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