/** * @author gdulski * @param _customer CustomerValue object * @return boolean true if update ok otherwise false * @throws SQLException * @throws OptInCustomerException * <p>Update the DAOptInCustomer table Process a single customer at a time. Get the update SQL * from the CAPBatch properties file * <p>Columns affected: OptInKey, OptInStatus, LastStatusUpdateDt, LastUpdateDt, * LastUpdateUserID */ private boolean update(CustomerValue _customer) throws SQLException, OptInCustomerException { boolean updateOK = false; String fieldName = null; // prepare the SQL if (_stmt == null) { _stmt = _con.prepareStatement(_updatesql); } // populate query parameters try { fieldName = "OptInKey"; _stmt.setString(1, _customer.getOptInKey()); fieldName = "OptInStatus"; if (_customer.getOptInStatus().equalsIgnoreCase(DEFAULTOPTINSTATUSCODE)) { _stmt.setString(2, DEFAULTOPTINSTATUSCODE); } else { _stmt.setString(2, refreshedOptInStatusCode); } fieldName = "LastUpdateUserId"; _stmt.setString(3, _customer.getLastUpdateUserid().substring(0, 19)); fieldName = "Orig OPtin Letter Date"; System.out.println("originletterdate " + _customer.getOrigOptinLetterSqlDate()); _stmt.setDate(4, _customer.getOrigOptinLetterSqlDate()); fieldName = "Last Optin Letter Date"; System.out.println("LastOptinLetterDate " + _customer.getLastOptinLetterSqlDate()); _stmt.setDate(5, _customer.getLastOptinLetterSqlDate()); fieldName = "Letter Count"; if (_customer.getOptInStatus().equalsIgnoreCase(NEWOPTINSTATUSCODE)) { _stmt.setInt(6, _customer.getOptinLetterCount() + 1); } else _stmt.setInt(6, _customer.getOptinLetterCount()); fieldName = "CAPnumber"; _stmt.setString(8, _customer.getCAPnumber()); fieldName = "AdditionalLetterSent Dt "; _stmt.setDate(7, _customer.getAdditionalLetterSqlDate()); fieldName = null; } catch (SQLException _s1) { _s1.printStackTrace(); System.out.println(fieldName + " exception " + _s1.getMessage()); } // execute the stmt try { _stmt.executeUpdate(); _stmt.close(); _stmt = null; updateOK = true; } catch (SQLException _e) { AppLog.writeErrorLog( "SQL EXCEPTION occurred update customer " + _customer.getCAPnumber() + " message\n" + _e.getMessage()); System.out.println( "SQL EXCEPTION occurred update customer " + _customer.getCAPnumber() + " message\n" + _e.getMessage()); _e.printStackTrace(); throw new OptInCustomerException( "SQL EXCEPTION occurred update customer " + _customer.getCAPnumber() + " message\n" + _e.getMessage()); } if (_customer.getOptInStatus().equalsIgnoreCase(DEFAULTOPTINSTATUSCODE)) { // Audit Trail the Warning letter. DAAuditDetails da = new DAAuditDetails(); da.setApplicationID("Batch Process"); System.out.println("Set Application ID : " + da.getApplicationID()); da.setLogAccess(null); da.setLogAction(_appProp.getProperty("batch.audittrail.optincustomerletter.Action")); da.setLogAdminName(0); da.setLogCAPNumber(_customer.getCAPnumber()); da.setLogProgram(""); da.setLogUserName(0); AuditTrail at = new AuditTrail(); at.writeAuditLog(da); } return updateOK; }
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; }