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