/**
* @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;
}