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
* @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;
}
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
* @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());
}
}
public void addNewLAPAUInfo() throws OptInCustomerException {
PreparedStatement psNewLAPAUContact = null;
ResultSet rsNewLAPAUContact = null;
try {
_appLog.writeAuditLog("Part -- II: Adding new LAP-AU address and Contact information..");
String newLAPAUs =
"Select au.au_abe_u, p.reference_u, a.long_busn_name1, r.abe_relation_u, "
+ " trim(a2.first_name_n), trim(a2.middle_init_n), trim(a2.abe_name_n), "
+ " c.email_t, a.long_busn_name2, a2.credentials_t "
+ " from lpt_au_abe au, ptt_abe_reference p, ptt_abe a, ptt_abe_relation r, ptt_abe a2, outer cpt_poc_email c "
+ " where au.au_abe_u = p.abe_u and "
+ " au.au_abe_u = a.abe_u and "
+ " au.au_abe_u = r.abe_u and "
+ " r.abe_relation_u = a2.abe_u and "
+ " r.abe_relation_u = c.abe_u and "
+ " au.accred_s = 'A' and "
+ " p.assigning_abe_u = 1000061 and "
+ " p.reference_type_c = 'I' and "
+ " r.relation_type_c = 'I' and "
+ " c.email_type_c = 'PRIM' and "
+ " p.reference_u NOT IN ( SELECT CAPNumber FROM tmp_CAPNumber ) and "
+ " current between p.effective_dt and p.termination_dt and "
+ " current between r.effective_dt and r.termination_dt and "
+ " current between c.effective_dt and c.termination_dt ";
System.out.println("***** Started New LAP-AU refresh process..");
psNewLAPAUContact = _ifxConn.prepareStatement(newLAPAUs);
rsNewLAPAUContact = psNewLAPAUContact.executeQuery();
this.updateCustomerInfo(rsNewLAPAUContact, "New");
System.out.println("***** Finished New LAP-AU refresh process..");
} catch (SQLException e) {
throw new OptInCustomerException(e);
} finally {
// Close database Connections
try {
InfmxConnection.getInfmxConnection().closeConnection();
WebAppConnection.getWebAppConnection().closeConnection();
} catch (Exception ex) {
throw new OptInCustomerException(ex);
}
}
}
public class OptInLetterBatch {
/** @SBGen Variable (,newSupplier,,0) */
private LetterDriver newSupplier;
static MaintainLog _appLog = MaintainLog.getLogger("OptInLetterBatchProcess");
public OptInLetterBatch() {}
public static void main(String args[]) {
System.out.println(
"The Opt In Letter process is beginning at "
+ (new SimpleDateFormat("yyyy.MM.dd 'at' HH:mm")).format(new Date()));
LetterDriver driver = new LetterDriver(_appLog);
System.out.println("The Opt In LetterDriver is processing letters");
driver.processLetters();
System.out.println(
"The Opt In Letter process completed successfully at "
+ (new SimpleDateFormat("yyyy.MM.dd 'at' HH:mm")).format(new Date()));
System.exit(0);
}
}
/**
* @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;
}
public void updateLAPAUInfo() throws OptInCustomerException {
String customerCAPIDSQL = null;
String LAPAUIDSQL = null;
String tempSQL = null;
Statement sCustomerCAPID = null;
ResultSet rsCustomerCAPID = null;
Statement sLAPAUContact = null;
ResultSet rsLAPAUContact = null;
PreparedStatement psTemp = null;
try {
System.out.println("\n\n\n\nupdateLAPAUInfo\n\n\n\n");
_appLog.writeAuditLog(
"Part -- I: Refreshing LAP-AU address "
+ "and Contact information for existing CAPIDs..");
customerCAPIDSQL =
"SELECT CAPNumber "
+ " FROM DAOptInCustomer "
+ " WHERE AbeType = 'A'";
sCustomerCAPID = _webAppConn.createStatement();
rsCustomerCAPID = sCustomerCAPID.executeQuery(customerCAPIDSQL);
tempSQL = "CREATE temp table tmp_CAPNumber (CAPNumber Char(30)) " + "with no log";
psTemp = _ifxConn.prepareStatement(tempSQL);
psTemp.executeUpdate();
tempSQL = "INSERT INTO tmp_CAPNumber (CAPNumber) VALUES (?)";
psTemp = _ifxConn.prepareStatement(tempSQL);
while (rsCustomerCAPID.next()) {
if (rsCustomerCAPID.getString(1) != null) {
psTemp.setString(1, rsCustomerCAPID.getString(1).trim());
psTemp.executeUpdate();
}
}
LAPAUIDSQL =
"SELECT au.au_abe_u, p.reference_u, a.long_busn_name1,"
+ " r.abe_relation_u, a2.first_name_n, a2.middle_init_n,"
+ " a2.abe_name_n, c.email_t, a.long_busn_name2,"
+ " a2.credentials_t "
+ " FROM lpt_au_abe au, ptt_abe_reference p, ptt_abe a,"
+ " ptt_abe_relation r, ptt_abe a2, outer cpt_poc_email c"
+ " WHERE au.au_abe_u = p.abe_u and "
+ " au.au_abe_u = a.abe_u and "
+ " au.au_abe_u = r.abe_u and "
+ " r.abe_relation_u = a2.abe_u and "
+ " r.abe_relation_u = c.abe_u and "
+ " au.accred_s = 'A' and "
+ " p.assigning_abe_u = 1000061 and "
+ " p.reference_type_c = 'I' and "
+ " r.relation_type_c = 'I' and "
+ " c.email_type_c = 'PRIM' and "
+ " p.reference_u IN ( SELECT CAPNumber FROM tmp_CAPNumber ) and "
+ " current between p.effective_dt and p.termination_dt and "
+ " current between r.effective_dt and r.termination_dt and "
+ " current between c.effective_dt and c.termination_dt ";
System.out.println("***** Started LAP-AU Update refresh..");
sLAPAUContact = _ifxConn.createStatement();
rsLAPAUContact = sLAPAUContact.executeQuery(LAPAUIDSQL);
this.updateCustomerInfo(rsLAPAUContact, "Refresh");
System.out.println("***** Finished LAP-AU Update refresh..");
} catch (Exception ex) {
throw new OptInCustomerException(ex);
}
}
private void updateCustomerInfo(ResultSet rs, String mode) throws OptInCustomerException {
String insertSQL =
"INSERT INTO DAOptInCustomer ("
+ " CAPNumber, BusinessName1, Address1, Address2, Address3, "
+ " Address4, City, State, PostalCode, Country, "
+ " Contact, EMailAddress, "
+ " OptInStatus, LastStatusUpdateDt, SiteAdminCount, "
+ " AbeType, LastUpdateUserId, LastUpdateDt, BusinessName2, Optinattempt )"
+ " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'N', getDate(), 0, 'A', 'javaint', getDate(), ?, 0 )";
String updateSQL =
"UPDATE DAOptInCustomer "
+ "SET BusinessName1 = ?, Address1=?, Address2=?, Address3=?, Address4=?, "
+ " City=?, State=?, PostalCode=?, Country=?, Contact=?, EmailAddress=?, "
+ " LastUpdateUserId='javaint', LastUpdateDt=getDate(), BusinessName2=? "
+ " WHERE CAPNumber=? ";
String addressID = null;
String auAddressSQL = null;
String addr1 = null;
String addr2 = null;
String addr3 = null;
String addr4 = null;
String city = null;
String state = null;
String zip = null;
String country = null;
String name = null;
int totalCount = 0;
PreparedStatement pstmt = null;
PreparedStatement pstmtAddr = null;
/* Get Address Details using SP lpp_get_address */
PreparedStatement psLAPAUAddressDetails = null;
ResultSet rsAddress = null;
ResultSet rsSP = null;
CallableStatement cstmt = null;
try {
_appLog.writeAuditLog(
"Retrieving Address details for each AU and Updating DAOptInCustomer table ..");
cstmt = _ifxConn.prepareCall("{call lpp_get_address(?,?,?)}");
if (mode.equalsIgnoreCase("new")) {
pstmt = _webAppConn.prepareStatement(insertSQL);
} else {
pstmt = _webAppConn.prepareStatement(updateSQL);
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
while (rs.next()) {
try {
totalCount++;
// Set the au and CAPID
cstmt.setInt(1, rs.getInt(1));
cstmt.setTimestamp(2, new Timestamp(new java.util.Date().getTime()));
cstmt.setString(3, "M");
cstmt.execute();
rsSP = cstmt.getResultSet();
if (totalCount % 2000 == 0) {
System.out.println("Processed " + totalCount + " Rows..");
}
if (rsSP.next()) {
addressID = rsSP.getString(3);
/* Using addressID get address information from ptt_Address table */
auAddressSQL =
"SELECT a.addr_1_t, a.addr_2_t, a.addr_3_t, "
+ " a.addr_4_t, a.city_t, a.state_c, a.zip_t, p.value_t "
+ " FROM ptt_address a, outer ptt_standard_codes p "
+ " WHERE a.country_c = p.key_u and p.table_u = 10 and "
+ " current between p.effective_dt and p.termination_dt and "
+ " a.address_u = "
+ addressID;
pstmtAddr = _ifxConn.prepareStatement(auAddressSQL);
rsAddress = pstmtAddr.executeQuery();
if (rsAddress.next()) {
if (rsAddress.getString(1) != null) {
addr1 = rsAddress.getString(1).trim();
} else {
System.out.println("ADDRESS1 VALUE WAS NULL for address ID: " + addressID);
addr1 = "";
}
if (rsAddress.getString(2) != null) {
addr2 = rsAddress.getString(2).trim();
} else {
System.out.println("ADDRESS2 VALUE WAS NULL for address ID: " + addressID);
addr2 = "";
}
if (rsAddress.getString(3) != null) {
addr3 = rsAddress.getString(3).trim();
} else {
System.out.println("ADDRESS3 VALUE WAS NULL for address ID: " + addressID);
addr3 = "";
}
if (rsAddress.getString(4) != null) {
addr4 = rsAddress.getString(4).trim();
} else {
System.out.println("ADDRESS4 VALUE WAS NULL for address ID: " + addressID);
addr4 = "";
}
if (rsAddress.getString(5) != null) {
city = rsAddress.getString(5).trim();
} else {
System.out.println("CITY VALUE WAS NULL for address ID: " + addressID);
city = "";
}
if (rsAddress.getString(6) != null) {
state = rsAddress.getString(6).trim();
} else {
System.out.println("STATE VALUE WAS NULL for address ID: " + addressID);
state = "";
}
if (rsAddress.getString(7) != null) {
zip = rsAddress.getString(7).trim();
} else {
System.out.println("ZIP VALUE WAS NULL for address ID: " + addressID);
zip = "";
}
if (rsAddress.getString(8) != null) {
country = rsAddress.getString(8).trim();
} else {
country = "United States";
}
// If Country is United States, insert an empty space
if (country.equalsIgnoreCase("united states") || country.equalsIgnoreCase("usa"))
country = "";
// Name Formatting
if (rs.getString(6) != null && rs.getString(5) != null && rs.getString(7) != null) {
if (rs.getString(6).trim().length() > 0)
name =
rs.getString(5).trim()
+ " "
+ rs.getString(6).trim()
+ ". "
+ rs.getString(7).trim();
else name = rs.getString(5).trim() + " " + rs.getString(7).trim();
} else name = rs.getString(5).trim() + " " + rs.getString(7).trim();
// Adding Credentials
if (rs.getString(10) != null) {
if (rs.getString(10).trim().length() > 0)
name = name + ", " + rs.getString(10).trim();
}
updateData.setCAPID(rs.getString(2));
updateData.setAddress1(addr1);
updateData.setAddress2(addr2);
updateData.setAddress3(addr3);
updateData.setAddress4(addr4);
updateData.setCity(city);
updateData.setState(state);
updateData.setZip(zip);
updateData.setCountry(country);
updateData.setCompanyName(rs.getString(3));
updateData.setContactName(name);
updateData.setEmail(rs.getString(8));
updateData.setBusinessName2(rs.getString(9));
updateData.setAbeType("A"); // Not used here
// Insert mode (add new AUs)
if (mode.equalsIgnoreCase("new")) {
System.out.println("Inserting the row for : " + updateData.getCAPID());
pstmt.setString(1, updateData.getCAPID()); // -- CAPID
pstmt.setString(2, updateData.getCompanyName()); // -- CompanyName
pstmt.setString(3, updateData.getAddress1()); // -- Address1
pstmt.setString(4, updateData.getAddress2()); // -- Address2
pstmt.setString(5, updateData.getAddress3()); // -- Address3
pstmt.setString(6, updateData.getAddress4()); // -- Address4
pstmt.setString(7, updateData.getCity()); // -- City
pstmt.setString(8, updateData.getState()); // -- State
pstmt.setString(9, updateData.getZip()); // -- Zip
pstmt.setString(10, updateData.getCountry()); // -- Country
pstmt.setString(11, updateData.getContactName()); // -- Contact Name
pstmt.setString(12, updateData.getEmail()); // -- Email
pstmt.setString(13, updateData.getBusinessName2()); // -- BusinessName2
pstmt.executeUpdate();
}
// Update mode (refresh data for existing AUs)
else {
pstmt.setString(1, updateData.getCompanyName()); // -- Company name
pstmt.setString(2, updateData.getAddress1()); // -- Address1
pstmt.setString(3, updateData.getAddress2()); // -- Address2
pstmt.setString(4, updateData.getAddress3()); // -- Address3
pstmt.setString(5, updateData.getAddress4()); // -- Address4
pstmt.setString(6, updateData.getCity()); // -- City
pstmt.setString(7, updateData.getState()); // -- State
pstmt.setString(8, updateData.getZip()); // -- Zip
pstmt.setString(9, updateData.getCountry()); // -- Country
pstmt.setString(10, updateData.getContactName()); // -- Contact Name
pstmt.setString(11, updateData.getEmail()); // -- Email
pstmt.setString(12, updateData.getBusinessName2()); // -- BusinessName2
pstmt.setString(13, updateData.getCAPID()); // -- CAPID
pstmt.executeUpdate();
}
}
}
} catch (SQLException e) {
if (e.getErrorCode() == 2627) {
// user Exists in the table. Update the record
try {
_appLog.writeAuditLog(
"LAP Customer:Updated the status:" + "CAPID=" + updateData.getCAPID());
pstmt = _webAppConn.prepareStatement(updateSQL);
pstmt.setString(1, updateData.getCompanyName()); // -- Company name
pstmt.setString(2, updateData.getAddress1()); // -- Address1
pstmt.setString(3, updateData.getAddress2()); // -- Address2
pstmt.setString(4, updateData.getAddress3()); // -- Address3
pstmt.setString(5, updateData.getAddress4()); // -- Address4
pstmt.setString(6, updateData.getCity()); // -- City
pstmt.setString(7, updateData.getState()); // -- State
pstmt.setString(8, updateData.getZip()); // -- Zip
pstmt.setString(9, updateData.getCountry()); // -- Country
pstmt.setString(10, updateData.getContactName()); // -- Contact Name
pstmt.setString(11, updateData.getEmail()); // -- Email
pstmt.setString(12, updateData.getBusinessName2()); // -- BusinessName2
pstmt.setString(13, updateData.getCAPID()); // -- CAPID
pstmt.executeUpdate();
// Set the prepared statement back to the new mode
pstmt = _webAppConn.prepareStatement(insertSQL);
// Now Update the AbeType
String sqlString =
"Update dbo.DAOptInCustomer set AbeType='A' where CAPNumber = '"
+ updateData.getCAPID()
+ "'";
PreparedStatement p = _webAppConn.prepareStatement(sqlString);
p.executeUpdate();
p.close();
} catch (SQLException f) {
System.out.println("Error: Updating DAOptInCustomer table....");
f.printStackTrace();
throw new OptInCustomerException(f);
}
} else {
e.printStackTrace();
throw new OptInCustomerException(e);
}
}
}
if (mode.equalsIgnoreCase("refresh"))
_appLog.writeAuditLog(
"Part -- I Complete : Updated address and contact information for "
+ totalCount
+ " AUs in DAOptInCustomer table..");
else
_appLog.writeAuditLog(
"Part -- II Complete : Inserted " + totalCount + " new AUs in DAOptInCustomer table..");
} catch (SQLException e) {
e.printStackTrace();
}
}