// 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(); }