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 _toUpdateLabs) throws OptInCustomerException {
    boolean updateOK = false;

    // dB connection
    init();
    // update each customer
    for (int _inx = 0; _inx < _toUpdateLabs.size(); _inx++) {
      CustomerValue _customer = (CustomerValue) _toUpdateLabs.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 _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 LinkedHashMap getEducationContacts() {

    String _sql_edu_contact =
        " select distinct customer_number, concat(concat(ship_cont_fname, ' '),  nvl(ship_cont_lname, ''))"
            +
            // CHG10073 Lab Synchronizer cannot identify OCATP orders
            // Oracle 11i upgrade to 12i,  table name changed
            " from APPS.XXCAP_OCATP_ORDERS_V a"
            +
            // " from cap.cap_ocatp_orders_v a" +
            " where a.ocatp_order_id = ( "
            + "	select  max(b.ocatp_order_id) "
            + "	from APPS.XXCAP_OCATP_ORDERS_V b"
            +
            // "	from cap.cap_ocatp_orders_v b" +
            "	where a.customer_id = b.customer_id"
            + "	and a.cancelled_date is null"
            + "	group by b.customer_id) ";

    LinkedHashMap eduContacts = null;
    Connection _conOracle = null;
    ResultSet _rsEdu = null;
    try {
      _conOracle = OracleConnection.getOracleConnection().getConnection();
    } catch (Exception exe) {
      AppLog.writeAuditLog("Oracle  Driver Exception: " + exe.getMessage());
      System.out.println("Oracle Driver Exception : " + exe.getMessage());
      System.exit(1);
    }

    try {

      Statement _stmt = null;
      _stmt = _conOracle.createStatement();
      _rsEdu = _stmt.executeQuery(_sql_edu_contact);

      if (_rsEdu == null) {
        AppLog.writeAuditLog("LetterDriverDataBean - Education Contact Not found.");
        System.out.println("LetterDriverDataBean -  Education Contact Not found.");
        return null;
      }

      eduContacts = new LinkedHashMap();
      while (_rsEdu.next()) {
        eduContacts.put(_rsEdu.getString(1), _rsEdu.getString(2));
      }

      _rsEdu = null;
    } catch (SQLException _e) {
      AppLog.writeAuditLog(
          "SQL Exception: occurred reading oracle education orders view " + _e.getMessage());
      System.out.println(
          "SQL Exception occurred reading reading oracle education orders view " + _e.getMessage());
    } finally {
      try {
        if (_rsEdu != null) {
          _rsEdu = null;
        }

        OracleConnection.getOracleConnection().closeConnection();
      } catch (Exception _e) {
        AppLog.writeErrorLog(
            "Closing ORacle  dB Connection Failure.  Skip remaining job " + _e.getMessage());
        System.out.println(
            "Closing Oracle  dB Connection Failure.  Skip remaining job " + _e.getMessage());
      }
    }

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