// Parse database tables to create organization info, including
// organization details (from Organizations table)
// roles (from Roles table)
// support relationships (from Relationships table)
// Schema:
// create table Organizations (
// Name String,
// UIC String,
// UTC String,
// SRC String,
// Superior String,
// Echelon String,
// Agency String,
// Service String,
// Nomenclature String,
// Prototype String,
// );
// create table Roles (
// Organization String,
// Role String -- Capable Role for Organization
// );
// create table Relationships (
// Organization String,
// Supported String, -- Supported Organization
// Role String -- Support Role
// );
private void parseOrganizationInfo(
Hashtable all_organizations, Statement stmt, String community, String node)
throws SQLException {
Double echelon;
int number = 0;
/*
String sql = "select Name, UIC, UTC, SRC, Superior, Echelon, Agency, Service, Nomenclature, "
+"Prototype, Location, Longitude, Latitude, Organizations.GeolocCode, InstallationTypeCode, "
+"CountryStateCode, CountryStateName, IcaoCode, ReserveOrg from Organizations, "
+"LocationMaster where Organizations.GeolocCode=LocationMaster.GeolocCode" ;
*/
String sql =
"select Name, UIC, UTC, SRC, Superior, Echelon, Agency, Service, Nomenclature, "
+ "Prototype, ReserveOrg from Organizations";
if (community != null && node != null) {
sql =
sql
+ " where Organizations.Community='"
+ community
+ "' and Organizations.Node='"
+ node
+ "'";
} else if (community != null) {
sql = sql + " where Organizations.Community='" + community + "'";
} else if (node != null) {
sql = sql + " where Organizations.Node='" + node + "'";
}
System.out.println(sql);
ResultSet rset = stmt.executeQuery(sql);
while (rset.next()) {
number++;
String current_organization = rset.getString("Name");
String testEchelon = rset.getString("Echelon");
if (testEchelon == null) {
echelon = new Double(-1);
} else {
echelon = new Double(testEchelon);
}
boolean reserve = false;
int res = rset.getInt("ReserveOrg");
if (res == 0) {
reserve = false;
} else {
reserve = true;
}
OrganizationData org_data =
new OrganizationData(
current_organization,
rset.getString("UIC"), // UIC
rset.getString("UTC"), // UTC
rset.getString("SRC"), // SRC
rset.getString("Superior"), // Superior
echelon, // Echelon
rset.getString("Agency"), // Agency
rset.getString("Service"), // Service
rset.getString("Nomenclature"), // Nomenclature
rset.getString("Prototype"), // Prototype
reserve); // isReserve
all_organizations.put(current_organization, org_data);
}
System.out.println("Query returned " + number + " results");
rset = null;
System.gc();
// Query for the Assigned Location
sql =
"select Name, Location, Longitude, Latitude, AssignedLoc, InstallationTypeCode, "
+ "CountryStateCode, CountryStateName, IcaoCode, ReserveOrg from Organizations, "
+ "LocationMaster where Organizations.AssignedLoc=LocationMaster.GeolocCode";
if (community != null && node != null) {
sql =
sql
+ " and Organizations.Community='"
+ community
+ "' and Organizations.Node='"
+ node
+ "'";
} else if (community != null) {
sql = sql + " and Organizations.Community='" + community + "'";
} else if (node != null) {
sql = sql + " and Organizations.Node='" + node + "'";
}
System.out.println(sql);
rset = stmt.executeQuery(sql);
while (rset.next()) {
number++;
String current_organization = rset.getString("Name");
OrganizationData data = (OrganizationData) all_organizations.get(current_organization);
// initialize the assigned location
data.initAssignedLoc(
rset.getString("Location"),
rset.getString("Longitude"),
rset.getString("Latitude"),
rset.getString("AssignedLoc"),
rset.getString("InstallationTypeCode"),
rset.getString("CountryStateCode"),
rset.getString("CountryStateName"),
rset.getString("IcaoCode"));
}
System.out.println("Query returned " + number + " results");
rset = null;
System.gc();
// Query for the home location
sql =
"select Name, Location, Longitude, Latitude, HomeLoc, InstallationTypeCode, "
+ "CountryStateCode, CountryStateName, IcaoCode, ReserveOrg from Organizations, "
+ "LocationMaster where Organizations.HomeLoc=LocationMaster.GeolocCode";
if (community != null && node != null) {
sql =
sql
+ " and Organizations.Community='"
+ community
+ "' and Organizations.Node='"
+ node
+ "'";
} else if (community != null) {
sql = sql + " and Organizations.Community='" + community + "'";
} else if (node != null) {
sql = sql + " and Organizations.Node='" + node + "'";
}
System.out.println(sql);
rset = stmt.executeQuery(sql);
while (rset.next()) {
number++;
String current_organization = rset.getString("Name");
OrganizationData data = (OrganizationData) all_organizations.get(current_organization);
// initialize the Home location
data.initHomeLoc(
rset.getString("Location"),
rset.getString("Longitude"),
rset.getString("Latitude"),
rset.getString("HomeLoc"),
rset.getString("InstallationTypeCode"),
rset.getString("CountryStateCode"),
rset.getString("CountryStateName"),
rset.getString("IcaoCode"));
}
System.out.println("Query returned " + number + " results");
rset = null;
System.gc();
// Query for all Organization/Role info
sql =
"select Organization, Role from Roles, Organizations"
+ " where Organizations.Name=Roles.Organization ";
number = 0;
if (community != null && node != null) {
sql =
sql
+ " and Organizations.Community='"
+ community
+ "' and Organizations.Node='"
+ node
+ "'";
} else if (community != null) {
sql = sql + " and Organizations.Community='" + community + "'";
} else if (node != null) {
sql = sql + " and Organizations.Node='" + node + "'";
}
System.out.println(sql);
rset = stmt.executeQuery(sql);
while (rset.next()) {
number++;
String current_organization = (String) rset.getString("Organization");
OrganizationData org_data = (OrganizationData) all_organizations.get(current_organization);
if (org_data == null) {
System.out.println("No organization defined : " + current_organization);
System.exit(0);
}
org_data.addRole(rset.getString("Role")); // Role
}
System.out.println("Query returned " + number + " results");
rset = null;
System.gc();
sql =
"Select SupportingOrg, SupportedOrg, Role from Relationships, Organizations"
+ " where Relationships.SupportingOrg=Organizations.Name";
if (community != null && node != null) {
sql =
sql
+ " and Organizations.Community='"
+ community
+ "'"
+ " and Organizations.Node='"
+ node
+ "'";
} else if (community != null) {
sql = sql + " and Organizations.Community='" + community + "'";
} else if (node != null) {
sql = sql + " and Organizations.Node='" + node + "'";
}
System.out.println(sql);
rset = stmt.executeQuery(sql);
number = 0;
rset = stmt.executeQuery(sql);
while (rset.next()) {
number++;
String current_organization = (String) rset.getString("SupportingOrg");
OrganizationData org_data = (OrganizationData) all_organizations.get(current_organization);
if (org_data == null) {
System.out.println("No organization defined : " + current_organization);
System.exit(0);
}
SupportRelation support =
new SupportRelation(
current_organization,
rset.getString("SupportedOrg"), // Supported Org
rset.getString("Role")); // Role
org_data.addSupportRelation(support);
}
System.out.println("Query returned " + number + " results");
rset = null;
System.gc();
// get the CSSCapabilities
sql =
"select CSSCapability.Cluster, Capability, QTY, Period"
+ " from CSSCapability, Organizations"
+ " where CSSCapability.Cluster=Organizations.Name";
if (community != null && node != null) {
sql =
sql
+ " and Organizations.Community='"
+ community
+ "' and Organizations.Node='"
+ node
+ "'";
} else if (community != null) {
sql = sql + " and Organizations.Community='" + community + "'";
} else if (node != null) {
sql = sql + " and Organizations.Node='" + node + "'";
}
System.out.println(sql);
number = 0;
rset = stmt.executeQuery(sql);
while (rset.next()) {
number++;
String current_organization = (String) rset.getString("Cluster");
OrganizationData org_data = (OrganizationData) all_organizations.get(current_organization);
if (org_data == null) {
System.out.println("No organization defined : " + current_organization);
System.exit(0);
}
CSSCapabilities CSSCap =
new CSSCapabilities(
current_organization,
rset.getString("Capability"), // Capability
rset.getString("QTY"), // Count
rset.getString("Period"));
org_data.addCSSCapabilities(CSSCap);
}
System.out.println("Query returned " + number + " results");
rset = null;
System.gc();
}
