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