protected void init() {
try {
_con = WebAppConnection.getWebAppConnection().getConnection();
} catch (OptInCustomerException ex) {
AppLog.writeAuditLog(
"OptIN Driver OptInCustomerException: no connection to dB " + ex.getMessage());
System.out.println(
"OptIN Driver OptInCustomerException: no connection to dB " + ex.getMessage());
System.exit(1);
} catch (Exception exe) {
AppLog.writeAuditLog("OptIN Driver Exception: " + exe.getMessage());
System.out.println("OptIn Driver Exception : " + exe.getMessage());
System.exit(1);
}
}
protected LinkedHashMap getEducationContacts() {
String _sql_edu_contact =
" select distinct customer_number, concat(concat(ship_cont_fname, ' '), nvl(ship_cont_lname, ''))"
+
// CHG10073 Lab Synchronizer cannot identify OCATP orders
// Oracle 11i upgrade to 12i, table name changed
" from APPS.XXCAP_OCATP_ORDERS_V a"
+
// " from cap.cap_ocatp_orders_v a" +
" where a.ocatp_order_id = ( "
+ " select max(b.ocatp_order_id) "
+ " from APPS.XXCAP_OCATP_ORDERS_V b"
+
// " from cap.cap_ocatp_orders_v b" +
" where a.customer_id = b.customer_id"
+ " and a.cancelled_date is null"
+ " group by b.customer_id) ";
LinkedHashMap eduContacts = null;
Connection _conOracle = null;
ResultSet _rsEdu = null;
try {
_conOracle = OracleConnection.getOracleConnection().getConnection();
} catch (Exception exe) {
AppLog.writeAuditLog("Oracle Driver Exception: " + exe.getMessage());
System.out.println("Oracle Driver Exception : " + exe.getMessage());
System.exit(1);
}
try {
Statement _stmt = null;
_stmt = _conOracle.createStatement();
_rsEdu = _stmt.executeQuery(_sql_edu_contact);
if (_rsEdu == null) {
AppLog.writeAuditLog("LetterDriverDataBean - Education Contact Not found.");
System.out.println("LetterDriverDataBean - Education Contact Not found.");
return null;
}
eduContacts = new LinkedHashMap();
while (_rsEdu.next()) {
eduContacts.put(_rsEdu.getString(1), _rsEdu.getString(2));
}
_rsEdu = null;
} catch (SQLException _e) {
AppLog.writeAuditLog(
"SQL Exception: occurred reading oracle education orders view " + _e.getMessage());
System.out.println(
"SQL Exception occurred reading reading oracle education orders view " + _e.getMessage());
} finally {
try {
if (_rsEdu != null) {
_rsEdu = null;
}
OracleConnection.getOracleConnection().closeConnection();
} catch (Exception _e) {
AppLog.writeErrorLog(
"Closing ORacle dB Connection Failure. Skip remaining job " + _e.getMessage());
System.out.println(
"Closing Oracle dB Connection Failure. Skip remaining job " + _e.getMessage());
}
}
return eduContacts;
}
protected ArrayList getOptInCustomers() {
int counter = 0;
ArrayList _optinLabs = new ArrayList();
// get CAPBatch properties:
// _limit = number of letters to print
// _sql = the Select query
int _limit = Integer.parseInt(_appProp.getProperty("optinletter.segment.limit"));
String _sql =
"SELECT distinct CAPNumber, OptInKey, BusinessName1 as Company, Address1, "
+ "Address2, Address3, Address4, City, State, PostalCode, Country, "
+ "Contact, EmailAddress, OptInStatus, LastStatusUpdateDt, "
+ "SiteAdminCount, LastUpdateUserId, LastUpdateDt, "
+ "(case "
+ " when Country = 'United States' then ('Y'+Country) "
+ " when Country = 'USA' then ('Y'+Country) "
+ " else ('N'+Country) end) as ISUSA, "
+ "OrgType, BusinessName2 as Company2, OrigOptinLetterDt, "
+ "LastOptinLetterDt, OptinLetterCt, OptinLetterType "
+ ",AdditonalLetterSentDt "
+ "FROM DAOptinCustomer (nolock) "
+
// byri - allow letter to also be sent to LAP-only labs
// "WHERE ((AbeType<>'L' and (OptInStatus = 'N' or (OptInStatus = 'A' and
// SiteAdminCount = 0 and (OptInKey is null) ) ) )" +
"WHERE ((OptInStatus = 'N' or (OptInStatus = 'A' and SiteAdminCount = 0 and (OptInKey is null) ) ) "
+ " or (optinlettertype IN ('EDU', 'PNE') and AdditonalLetterSentDt is null))";
try {
Statement _stmt = null;
_stmt = _con.createStatement();
ResultSet _rs = _stmt.executeQuery(_sql);
if (_rs == null) {
AppLog.writeAuditLog("LetterDriverDataBean - no customer letters to process.");
System.out.println("LetterDriverDataBean - no customer letters to process.");
return null;
}
// Get education contacts
System.out.println("\nGetting the Education Contacts \n");
LinkedHashMap eduContacts = this.getEducationContacts();
for (; _rs != null && _rs.next() && counter < _limit; counter++) {
try {
CustomerValue _customer = new CustomerValue(AppLog);
_customer.setCustomerData(_rs, eduContacts);
_optinLabs.add(counter, _customer);
_customer = null;
} catch (OptInCustomerException e) {
System.out.println("Populating CustomerValue object failed " + e.getMessage());
AppLog.writeAuditLog("Populating CustomerValue object failed " + e.getMessage());
}
}
} catch (SQLException _e) {
AppLog.writeAuditLog("SQL Exception: occurred reading DAOptinCustomer " + _e.getMessage());
System.out.println("SQL Exception occurred reading DAOptInCustomer " + _e.getMessage());
} finally {
// close the connection
try {
closeConnection();
} catch (OptInCustomerException _io) {
System.out.println("DB close exception occurred. terminate job.");
}
}
System.out.println("There were " + counter + " customers read from DAOptInCustomer table");
return _optinLabs;
}
