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