protected ArrayList getOptInCustomers() {
int counter = 0;
ArrayList _rowset = new ArrayList();
// get CAPBatch properties:
// _limit = number of letters to print
// _sql = the Select query
int _limit = Integer.parseInt(_appProp.getProperty(SEGMENTPROPERTY));
String _sql = _appProp.getNoEnvironmentProperty(SQLREADPROPERTY);
try {
ResultSet _resultSet = null;
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;
}
for (; _rs != null && _rs.next() && counter < _limit; counter++) {
try {
CustomerValue _customer = new CustomerValue(AppLog);
_customer.setCustomerData(_rs);
_rowset.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 _rowset;
}
/**
* @author gdulski
* @since nov 26, 2003
* <p>Singleton class that performs all dB functions for the LetterDriver class. Open a
* connection to DAOptInCustomer table in Styx and query the cusotmer data. Updates the table
* with new PIN and OptStatusCode, dates and userId.
*/
public class LetterDriverDataBean {
private String SEGMENTPROPERTY = "optinletter.segment.limit";
private String SQLREADPROPERTY = "optinletter.read.sql";
private String SQLUPDATEPROPERTY = "optinletter.update.sql";
private String COMPLETEDLETTEROPTINSTATUS = "optinletter.completed.optin.status";
private String DEFAULTOPTINSTATUSCODE = "A";
static MaintainLog AppLog = null;
CAPProperties _appProp = new CAPProperties();
// get the update sql from CAPBatch
private String _updatesql = _appProp.getNoEnvironmentProperty(SQLUPDATEPROPERTY);
// optin status code for completed letters
private String refreshedOptInStatusCode =
_appProp.getNoEnvironmentProperty(COMPLETEDLETTEROPTINSTATUS).trim();
private Connection _con = null;
private PreparedStatement _stmt = null;
public static LetterDriverDataBean letterDataBean = null;
/** private constructor to ensure only one instance in the JVM */
public LetterDriverDataBean(MaintainLog appLog) {
AppLog = appLog;
init();
}
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);
}
}
/**
* @author gdulski
* @param _con
* @return nothing
* <p>call the closeConnection() of WebAppConnection class
*/
protected void closeConnection() throws OptInCustomerException {
try {
WebAppConnection.getWebAppConnection().closeConnection();
} catch (Exception _e) {
AppLog.writeErrorLog("Closing dB Connection Failure. Skip remaining job " + _e.getMessage());
System.out.println("Closing dB Connection Failure. Skip remaining job " + _e.getMessage());
throw new OptInCustomerException(
"Close dB Connection Failure. Skip remaining job " + _e.getMessage());
}
}
protected ArrayList getOptInCustomers() {
int counter = 0;
ArrayList _rowset = new ArrayList();
// get CAPBatch properties:
// _limit = number of letters to print
// _sql = the Select query
int _limit = Integer.parseInt(_appProp.getProperty(SEGMENTPROPERTY));
String _sql = _appProp.getNoEnvironmentProperty(SQLREADPROPERTY);
try {
ResultSet _resultSet = null;
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;
}
for (; _rs != null && _rs.next() && counter < _limit; counter++) {
try {
CustomerValue _customer = new CustomerValue(AppLog);
_customer.setCustomerData(_rs);
_rowset.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 _rowset;
}
/**
* @author gdulski
* @since nov 26, 2003
* @param ArrayList of updated CustomerValue objects containing new PIN, new OptInStatus, new date
* updated and new lastUpdateUserId.
* @return boolean true if update successful otherwise return false
* <p>Update the DAOptInCustomer table in Styx for each customer in the input ArrayList.
*/
protected boolean updateCustomers(ArrayList _rowset) throws OptInCustomerException {
boolean updateOK = false;
// dB connection
init();
// update each customer
for (int _inx = 0; _inx < _rowset.size(); _inx++) {
CustomerValue _customer = (CustomerValue) _rowset.get(_inx);
try {
updateOK = update(_customer);
if (!updateOK) break;
} catch (SQLException _e) {
AppLog.writeErrorLog(
"SQL Exception when preparing update SQL "
+ _customer.getCAPnumber()
+ " message : "
+ _e.getMessage());
System.out.println(
"SQL Exception when preparing update SQL "
+ _customer.getCAPnumber()
+ " msg "
+ _e.getMessage());
throw new OptInCustomerException(
"SQL Exception when preparing update SQL "
+ _customer.getCAPnumber()
+ " msg "
+ _e.getMessage());
}
_customer = null;
}
return true;
}
/**
* @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;
// prepare the SQL
if (_stmt == null) {
_stmt = _con.prepareStatement(_updatesql);
}
// populate query parameters
try {
_stmt.setString(1, _customer.getOptInKey());
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("OptInKey exception " + _s1.getMessage());
}
try {
if (_customer.getOptInStatus().equalsIgnoreCase(DEFAULTOPTINSTATUSCODE)) {
_stmt.setString(2, DEFAULTOPTINSTATUSCODE);
} else {
_stmt.setString(2, refreshedOptInStatusCode.trim());
}
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("OptInStatus exception " + _s1.getMessage());
}
try {
_stmt.setString(3, _customer.getLastUpdateUserid().substring(0, 19));
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("LastUpdateUserId exception " + _s1.getMessage());
}
try {
_stmt.setString(4, _customer.getCAPnumber());
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("CAPnumber 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());
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(
_appProp.getProperty("batch.audittrail.optincustomerletter.ApplicationID"));
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;
}
}
/**
* @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;
// prepare the SQL
if (_stmt == null) {
_stmt = _con.prepareStatement(_updatesql);
}
// populate query parameters
try {
_stmt.setString(1, _customer.getOptInKey());
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("OptInKey exception " + _s1.getMessage());
}
try {
if (_customer.getOptInStatus().equalsIgnoreCase(DEFAULTOPTINSTATUSCODE)) {
_stmt.setString(2, DEFAULTOPTINSTATUSCODE);
} else {
_stmt.setString(2, refreshedOptInStatusCode.trim());
}
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("OptInStatus exception " + _s1.getMessage());
}
try {
_stmt.setString(3, _customer.getLastUpdateUserid().substring(0, 19));
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("LastUpdateUserId exception " + _s1.getMessage());
}
try {
_stmt.setString(4, _customer.getCAPnumber());
} catch (SQLException _s1) {
_s1.printStackTrace();
System.out.println("CAPnumber 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());
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(
_appProp.getProperty("batch.audittrail.optincustomerletter.ApplicationID"));
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;
}