// 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(); }
// Print <Cluster>-prototype-ini.dat file // File format: // [Prototype] CombatOrganization|CivilanOrganization // [UniqueId] "UTC/CombatOrg" // [UIC] "UIC/<OrganizationName> // [Relationship] // Superior <Superior> "" // Support <Supported> <Role> // [TypeIdentificationPG] // TypeIdentification String "UTC/RTOrg" // Nomenclature String <Nomenclature> // AlternateTypeIdentification String "SRC/<SRC>" // [ClusterPG] // MessageAddress String <OrganizationName> // [OrganizationPG] // Roles Collection<Role> <Role> // [MilitaryOrgPG] // UIC String <UIC> // Echelon String <Echelon> // UTC String <UTC> // SRC String <SRC> // private void dumpOrganizationInfo(Hashtable all_organizations, String path) throws IOException { Hashtable supportedOrgRoles = null; for (Enumeration e = all_organizations.keys(); e.hasMoreElements(); ) { supportedOrgRoles = new Hashtable(); String org_name = (String) e.nextElement(); OrganizationData org_data = (OrganizationData) all_organizations.get(org_name); PrintWriter org_file; try { if (path != null) { org_file = createPrintWriter(path + File.separator + org_name + "-prototype-ini.dat"); } else { org_file = createPrintWriter(org_name + "-prototype-ini.dat"); } org_file.println( "[Prototype] " + (org_data.isCivilan() ? "CivilianOrganization" : "MilitaryOrganization")); org_file.println("\n[UniqueId] " + '"' + "UTC/CombatOrg" + '"'); org_file.println("\n[UIC] " + '"' + "UIC/" + org_name + '"'); // Write out Superior/Support Relationships org_file.println("\n[Relationship]"); if (org_data.mySuperior != null) { org_file.println("Superior " + '"' + org_data.mySuperior + '"' + " " + '"' + '"'); } for (Enumeration rels = org_data.mySupportRelations.elements(); rels.hasMoreElements(); ) { SupportRelation suprel = (SupportRelation) rels.nextElement(); if (!supportedOrgRoles.containsKey(suprel.mySupportedOrganization)) { supportedOrgRoles.put(suprel.mySupportedOrganization, suprel.myRole); } else { String role = (String) supportedOrgRoles.get(suprel.mySupportedOrganization); role = role + ", " + suprel.myRole; supportedOrgRoles.put(suprel.mySupportedOrganization, role); } } for (Enumeration roles = supportedOrgRoles.keys(); roles.hasMoreElements(); ) { String supportedOrg = (String) roles.nextElement(); String role = (String) supportedOrgRoles.get(supportedOrg); org_file.println("Supporting " + '"' + supportedOrg + '"' + " " + '"' + role + '"'); } // Print TypeIdentificationPG fields org_file.println("\n[TypeIdentificationPG]"); org_file.println("TypeIdentification String " + '"' + "UTC/RTOrg" + '"'); org_file.println("Nomenclature String " + '"' + org_data.myNomenclature + '"'); org_file.println( "AlternateTypeIdentification String " + '"' + "SRC/" + org_data.mySRC + '"'); // Print ClusterPG info org_file.println("\n[ClusterPG]"); org_file.println("MessageAddress String " + '"' + org_name + '"'); // Print OrganizationPG (Roles) info org_file.println("\n[OrganizationPG]"); org_file.print("Roles Collection<Role> " + '"'); boolean is_first = true; for (Enumeration roles = org_data.myRoles.elements(); roles.hasMoreElements(); ) { String role = (String) roles.nextElement(); if (!is_first) { org_file.print(", "); } org_file.print(role); is_first = false; } org_file.println('"'); // Print MilitaryOrgPG info org_file.println("\n[MilitaryOrgPG]"); org_file.println("UIC String " + '"' + org_data.myUIC + '"'); if (org_data.myEchelon.intValue() != -1) { org_file.println("Echelon String " + '"' + org_data.myEchelon.intValue() + '"'); } else { org_file.println("Echelon String " + '"' + '"'); } org_file.println("UTC String " + '"' + org_data.myUTC + '"'); org_file.println("SRC String " + '"' + org_data.mySRC + '"'); if (org_data.myIsReserve == true) { org_file.println("IsReserve boolean true"); } else { org_file.println("IsReserve boolean false"); } // Print HomeLocationPG info under Military Org PG org_file.println( "HomeLocation GeolocLocation " + "\"GeolocCode=" + org_data.myHomeGeoLoc + ", InstallationTypeCode=" + org_data.myHomeInstallCode + ", CountryStateCode=" + org_data.myHomeCSCode + ", CountryStateName=" + org_data.myHomeCSName + ", IcaoCode=" + org_data.myHomeICAOCode + ", Name=" + org_data.myHomeLocation + ", Latitude=Latitude " + org_data.myHomeLatitude + "degrees, Longitude=Longitude " + org_data.myHomeLongitude + "degrees\""); /* // Print AssignmentPG info org_file.println("\n[AssignmentPG]"); org_file.println("GeolocCode String "+'"'+org_data.myAssignedGeoLoc+'"'); org_file.println("InstallationTypeCode String "+'"'+org_data.myAssignedInstallCode+'"'); org_file.println("CountryStateCode String "+'"'+org_data.myAssignedCSCode+'"'); org_file.println("CountryStateName String "+'"'+org_data.myAssignedCSName+'"'); org_file.println("IcaoCode String "+'"'+org_data.myAssignedICAOCode+'"'); */ // Print CSSCapabilities info if (org_data.myCSSCapabilities != null) { is_first = true; org_file.println("\n[CSSCapabilityPG]"); org_file.print("Capabilities Collection<CSSCapability> " + '"'); for (Enumeration eCap = org_data.myCSSCapabilities.elements(); eCap.hasMoreElements(); ) { CSSCapabilities cssCap = (CSSCapabilities) eCap.nextElement(); if (!is_first) { org_file.print(", "); } org_file.print(cssCap.capability); org_file.print(" " + cssCap.qty); if (!cssCap.period.equals("")) { org_file.print(" Duration=" + cssCap.period); } is_first = false; } org_file.println('"'); } org_file.close(); } catch (IOException exc) { System.out.println("IOException: " + exc); System.exit(-1); } } }
/** Select Organization */ public static OrganizationData[] select( ConnectionProvider connectionProvider, String paramLanguage, String adUserClient, String adOrgClient, int firstRegister, int numberRegisters) throws ServletException { String strSql = ""; strSql = strSql + " SELECT " + " AD_OrgInfo.AD_Client_ID, " + " (CASE WHEN AD_OrgInfo.AD_Client_ID IS NULL THEN '' ELSE (TO_CHAR(client.Name) ) END) AS AD_Client_IDR, " + " AD_OrgInfo.AD_Org_ID, " + " (CASE WHEN AD_OrgInfo.AD_Org_ID IS NULL THEN '' ELSE (TO_CHAR(org.Name) ) END) AS AD_Org_IDR, " + " COALESCE(AD_OrgInfo.IsActive, 'N') AS IsActive, " + " AD_OrgInfo.C_Location_ID, " + " (CASE WHEN AD_OrgInfo.C_Location_ID IS NULL THEN '' ELSE (TO_CHAR(loc.Address1) || ' - ' || TO_CHAR(loc.Address2) || ' - ' || TO_CHAR(loc.Postal) || ' - ' || TO_CHAR(loc.City) || ' - ' || TO_CHAR(reg.Name) || ' - ' || TO_CHAR((CASE WHEN countryTrl.Name IS NULL THEN TO_CHAR(country.Name) ELSE TO_CHAR(countryTrl.Name) END)) ) END) AS C_Location_IDR, " + " AD_OrgInfo.DUNS, " + " AD_OrgInfo.TaxID, " + " AD_OrgInfo.C_BPartner_ID, " + " (CASE WHEN AD_OrgInfo.C_BPartner_ID IS NULL THEN '' ELSE (TO_CHAR(bpart.Name) ) END) AS C_BPartner_IDR, " + " AD_OrgInfo.Logo, " + " COALESCE(AD_OrgInfo.Reference_Order, 'N') AS Reference_Order, " + " '' AS TR_BGCOLOR , " + " AD_OrgInfo.Created, " + " AD_OrgInfo.CreatedBy, " + " AD_OrgInfo.Updated, " + " AD_OrgInfo.UpdatedBy, ? AS LANGUAGE " + " FROM " + " AD_OrgInfo " + " left join (select AD_Client_ID, Name from AD_Client) client on (AD_OrgInfo.AD_Client_ID = client.AD_Client_ID) " + " left join (select AD_Org_ID, Name from AD_Org) org on (AD_OrgInfo.AD_Org_ID = org.AD_Org_ID) " + " left join (select C_Location_ID, Address1, Address2, Postal, City, C_Region_ID, C_Country_ID from C_Location) loc on (AD_OrgInfo.C_Location_ID = loc.C_Location_ID) " + " left join (select C_Region_ID, Name from C_Region) reg on (loc.C_Region_ID = reg.C_Region_ID) " + " left join (select C_Country_ID, Name from C_Country) country on (loc.C_Country_ID = country.C_Country_ID) " + " left join (select C_Country_ID,AD_Language, Name from C_Country_TRL) countryTrl on (country.C_Country_ID = countryTrl.C_Country_ID and countryTrl.AD_Language = ?) " + " left join (select C_BPartner_ID, Name from C_BPartner) bpart on (AD_OrgInfo.C_BPartner_ID = bpart.C_BPartner_ID)" + " WHERE " + " AD_OrgInfo.AD_Client_ID IN ("; strSql = strSql + ((adUserClient == null || adUserClient.equals("")) ? "" : adUserClient); strSql = strSql + ") " + " AND AD_OrgInfo.AD_Org_ID IN ("; strSql = strSql + ((adOrgClient == null || adOrgClient.equals("")) ? "" : adOrgClient); strSql = strSql + ")"; ResultSet result; Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0); PreparedStatement st = null; int iParameter = 0; try { st = connectionProvider.getPreparedStatement(strSql); QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile()); iParameter++; UtilSql.setValue(st, iParameter, 12, null, paramLanguage); iParameter++; UtilSql.setValue(st, iParameter, 12, null, paramLanguage); if (adUserClient != null && !(adUserClient.equals(""))) {} if (adOrgClient != null && !(adOrgClient.equals(""))) {} result = st.executeQuery(); long countRecord = 0; long countRecordSkip = 1; boolean continueResult = true; while (countRecordSkip < firstRegister && continueResult) { continueResult = result.next(); countRecordSkip++; } while (continueResult && result.next()) { countRecord++; OrganizationData objectOrganizationData = new OrganizationData(); objectOrganizationData.adClientId = UtilSql.getValue(result, "AD_CLIENT_ID"); objectOrganizationData.adClientIdr = UtilSql.getValue(result, "AD_CLIENT_IDR"); objectOrganizationData.adOrgId = UtilSql.getValue(result, "AD_ORG_ID"); objectOrganizationData.adOrgIdr = UtilSql.getValue(result, "AD_ORG_IDR"); objectOrganizationData.isactive = UtilSql.getValue(result, "ISACTIVE"); objectOrganizationData.cLocationId = UtilSql.getValue(result, "C_LOCATION_ID"); objectOrganizationData.cLocationIdr = UtilSql.getValue(result, "C_LOCATION_IDR"); objectOrganizationData.duns = UtilSql.getValue(result, "DUNS"); objectOrganizationData.taxid = UtilSql.getValue(result, "TAXID"); objectOrganizationData.cBpartnerId = UtilSql.getValue(result, "C_BPARTNER_ID"); objectOrganizationData.cBpartnerIdr = UtilSql.getValue(result, "C_BPARTNER_IDR"); objectOrganizationData.logo = UtilSql.getValue(result, "LOGO"); objectOrganizationData.referenceOrder = UtilSql.getValue(result, "REFERENCE_ORDER"); objectOrganizationData.trBgcolor = UtilSql.getValue(result, "TR_BGCOLOR"); objectOrganizationData.created = UtilSql.getDateValue(result, "CREATED", "dd-MM-yyyy"); objectOrganizationData.createdby = UtilSql.getValue(result, "CREATEDBY"); objectOrganizationData.updated = UtilSql.getDateValue(result, "UPDATED", "dd-MM-yyyy"); objectOrganizationData.updatedby = UtilSql.getValue(result, "UPDATEDBY"); objectOrganizationData.language = UtilSql.getValue(result, "LANGUAGE"); objectOrganizationData.InitRecordNumber = Integer.toString(firstRegister); vector.addElement(objectOrganizationData); if (countRecord >= numberRegisters && numberRegisters != 0) { continueResult = false; } } result.close(); } catch (SQLException e) { log4j.error("SQL error in query: " + strSql + "Exception:" + e); throw new ServletException( "@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage()); } catch (Exception ex) { log4j.error("Exception in query: " + strSql + "Exception:" + ex); throw new ServletException("@CODE=@" + ex.getMessage()); } finally { try { connectionProvider.releasePreparedStatement(st); } catch (Exception ignore) { ignore.printStackTrace(); } } OrganizationData objectOrganizationData[] = new OrganizationData[vector.size()]; vector.copyInto(objectOrganizationData); return (objectOrganizationData); }