Пример #1
0
  /**
   * This clause update Status and Process Date
   *
   * @return String format of update statement
   */
  protected String buildUpdateStatus() throws Exception {
    // the String to return
    StringBuffer sbUpdate = null;

    // init the buffer
    sbUpdate = new StringBuffer("update stage_usac_form set ");

    // build the query
    sbUpdate.append("STATUS = " + this.STATUS);
    sbUpdate.append(", PROCESS_DATE = SYSDATE ");

    return sbUpdate.toString();
  }
Пример #2
0
  /**
   * Method to build a select clause for distinct filenames for a particular usac refrence number in
   * stage_usac_form
   *
   * @return String, the built select clause
   */
  protected String buildSelectFileName() {
    USFEnv.getLog().writeDebug("INSIDE buildSelectFileName()", this, null);
    // the String to return
    StringBuffer sbSelect = null;

    // init the buffer
    sbSelect = new StringBuffer("select ");

    // build the query
    sbSelect.append("distinct(FILENAME) ");
    sbSelect.append("from  stage_usac_form ");

    return sbSelect.toString();
  }
Пример #3
0
  /**
   * This method searches the table stage_usac_form for the USAC File Name. It returns all the
   * columns in the table.
   *
   * @param <code>String file</code> The USAC File Name
   * @return Vector containing the Result Set
   */
  public Vector searchStageUsacFormByFile(String file) {
    // the buffer for the query
    StringBuffer sbQuery = null;

    try {
      // init the buffer
      sbQuery = new StringBuffer("");

      // build the query
      sbQuery.append(buildSelectClause());
      sbQuery.append(" where upper(rtrim(ltrim(FILENAME)))=upper('" + file + "')");
      sbQuery.append(" order by FRN , SDC_INV_NO ");

      // write to log if in debug mode
      USFEnv.getLog()
          .writeDebug(
              "searchStageUsacFormByFile() " + "sbQuery is " + sbQuery.toString() + "\n",
              this,
              null);

    } catch (Exception e) {
      USFEnv.getLog().writeCrit("searchStageUsacFormByFile(): " + e.getMessage(), this, e);
    }
    return super.selectSQLCall(sbQuery.toString());
  }
Пример #4
0
  /**
   * This method searches the table stage_usac_form for the SDC Invoice Number. It returns all the
   * columns in the table.
   *
   * @param <code>String invoice</code> The SDC Invoice Number
   * @return Vector containing the Result Set
   */
  public Vector searchStageUsacFormByInv(String invoice) {
    // the buffer for the query
    StringBuffer sbQuery = null;

    try {
      // init the buffer
      sbQuery = new StringBuffer("");

      // build the query
      sbQuery.append(buildSelectClause());
      sbQuery.append(" where rtrim(ltrim(SDC_INV_NO))='" + invoice + "'");

      // write to log if in debug mode
      USFEnv.getLog()
          .writeDebug(
              "searchStageUsacFormByInv() " + "sbQuery is " + sbQuery.toString() + "\n",
              this,
              null);

    } catch (Exception e) {
      USFEnv.getLog().writeCrit("searchStageUsacFormByInv(): " + e.getMessage(), this, e);
    }
    return super.selectSQLCall(sbQuery.toString());
  }
Пример #5
0
  // NEW METHOD TO UPDATE PROCESS DATE AND STATUS IN STAGE_USAC_FORM TABLE
  public boolean updateStatus() {
    // the buffer for the query
    StringBuffer sbQuery = null;

    try {
      // init the buffer
      sbQuery = new StringBuffer();

      // build the query
      sbQuery.append(buildUpdateStatus());
      sbQuery.append(" WHERE ");
      sbQuery.append(" ROWID = '" + this.ROWID + "' ");

      if (super.doSQLOneRowCall(sbQuery.toString())) {
        USFEnv.getLog()
            .writeDebug(
                "updateStatus(): SUCCESS. sbQuery is " + sbQuery.toString() + "\n", this, null);
        return true;
      } else {
        USFEnv.getLog()
            .writeDebug(
                "updateStatus(): Failure!  sbQuery is " + sbQuery.toString() + "\n", this, null);
        return false;
      }

    } catch (Exception e) {
      USFEnv.getLog()
          .writeCrit(
              "exception in updateStatus ) "
                  + "Unable to update a stage_usac_form status and process date: "
                  + e.getMessage()
                  + " with Query: "
                  + sbQuery.toString(),
              this,
              e);
      return false;
    }
  }
Пример #6
0
  /**
   * Method to build a select clause for all the columns in stage_usac_form table.
   *
   * @return String, the built select clause
   */
  protected String buildSelectClauseRaw() {

    USFEnv.getLog().writeDebug("INSIDE buildSelectClauseRaw()", this, null);
    // the String to return
    StringBuffer sbSelect = null;

    // init the buffer
    sbSelect = new StringBuffer("select ");

    // build the query
    // IMPORTANT STEP*****ROWID IS ALSO INCLUDED IN THE SELECT STATEMENT
    sbSelect.append("ROWID, ");
    sbSelect.append("nvl(HDR_SPIN,0) HDR_SPIN, ");
    sbSelect.append("nvl(SPIN_NM,'') SPIN_NM, ");
    sbSelect.append("nvl(RCPNT_EMAIL,'') RCPNT_EMAIL, ");
    sbSelect.append("nvl(USAC_EMAIL,'') USAC_EMAIL, ");
    sbSelect.append("nvl(RFRNC_NMBR,'') RFRNC_NMBR, ");
    sbSelect.append("nvl(RCRD_CNT,0) RCRD_CNT, ");
    sbSelect.append("nvl(TOT_PAYMENT,0) TOT_PAYMENT, ");
    sbSelect.append("USAC_PRCS_DAT, ");
    sbSelect.append("nvl(RTRCT_FLAG,'') RTRCT_FLAG, ");
    sbSelect.append("nvl(DTL_SPIN,0) DTL_SPIN, ");
    sbSelect.append("nvl(FRN,0) FRN, ");
    sbSelect.append("nvl(SDC_INV_NO,'') SDC_INV_NO, ");
    sbSelect.append("nvl(AMT_PAID,0) AMT_PAID, ");
    sbSelect.append("nvl(DSBRSMNT_TXT,'') DSBRSMNT_TXT, ");
    sbSelect.append("EMAIL_DATE, ");
    sbSelect.append("nvl(FILENAME,'') FILENAME, ");
    sbSelect.append("PROCESS_DATE, ");
    sbSelect.append("nvl(STATUS,0) STATUS ");
    sbSelect.append(" from stage_usac_form ");

    return sbSelect.toString();
  }
Пример #7
0
  /**
   * This method searches the tables FRNS, FRN_DETS, FRN_DET_HSTIES for the presence of a record in
   * a specific query. It returns the invoice line number.
   *
   * @param <code>StageUsacForm</code> The STAGE_USAC object.
   * @return long containing the invoice line number.
   */
  public long searchForAmount(java.sql.Connection conn) {
    // the buffer for the query
    StringBuffer sbQuery = null;
    long returnAmt = 0;
    // Vector invline=null;

    PreparedStatement psStmt = null;
    ResultSet rsResult = null;
    try {
      // init the buffer
      sbQuery = new StringBuffer("");

      // build the query
      sbQuery.append("SELECT max(INV_LN_NO) AS INV_LN_NO_MAX ");
      sbQuery.append("FROM (SELECT sum(CRDT_AMT) AMT, INV_LN_NO ");
      sbQuery.append("FROM FRNS, FRN_DETS, FRN_DET_HSTIES, INV ");
      sbQuery.append("WHERE ");
      sbQuery.append("FRN_ID=FRN_ID_FK AND FD_ID=FD_ID_FK AND INV_STAT='I' AND ");
      sbQuery.append("INV_ID_FK=INV_ID");
      // sbQuery.append(" AND FRN = ?'");
      sbQuery.append(" AND FRN = ? ");
      // sbQuery.append(this.getFrn());
      // sbQuery.append("' GROUP BY INV_LN_NO) " );
      sbQuery.append(" GROUP BY INV_LN_NO) ");
      sbQuery.append(" WHERE AMT = ?");
      // sbQuery.append(this.getAmtPaid());

      // write to log if in debug mode
      USFEnv.getLog()
          .writeDebug(
              "searchForAmount(StageUsacForm) " + "sbQuery is " + sbQuery.toString() + "\n",
              this,
              null);

      psStmt = conn.prepareStatement(sbQuery.toString());

      // psStmt.setLong(1,this.getFrn());
      psStmt.setString(1, String.valueOf(this.getFrn()));
      psStmt.setDouble(2, this.getAmtPaid());

      rsResult = psStmt.executeQuery();

      if (rsResult.next()) {
        returnAmt = rsResult.getLong(1);
      }
      if (rsResult != null) rsResult.close();
      if (psStmt != null) psStmt.close();

    } catch (Exception e) {
      try {
        if (rsResult != null) rsResult.close();
        if (psStmt != null) psStmt.close();
      } catch (Exception ex) {
        USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, ex);
      }
      USFEnv.getLog()
          .writeCrit("searchForAmount(StageUsacForm) Failed Query:" + sbQuery.toString(), this, e);
    }

    /*	try
    {
    	if( rsResult != null)
    		rsResult.close();
    }
    catch(Exception e)
    {
    	USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
    }
    try
    {
    	if( psStmt != null)
    		psStmt.close();
    }
    catch(Exception e)
    {
    	 USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
    }*/

    return (returnAmt);
  } // searchForAmount
Пример #8
0
  /**
   * This method updates this object into the stage_usac_form table.
   *
   * @return boolean, true if update successful else false.
   */
  public boolean updateStageUsacForm() {
    // the buffer for the query
    StringBuffer sbQuery = null;

    try {
      // init the buffer
      sbQuery = new StringBuffer();

      // build the query
      sbQuery.append(buildUpdateClause());
      sbQuery.append(" WHERE ");
      sbQuery.append(" FRN = " + this.getFrn());
      sbQuery.append(" AND SDC_INV_NO = '" + this.getSdcInvno() + "' ");
      sbQuery.append(" AND HDR_SPIN = " + this.getHdrSpin());
      sbQuery.append(" AND DSBRSMNT_TXT = '" + this.getDsbrsmntTxt() + "' ");

      // update row - return true if update successful
      if (super.doSQLOneRowCall(sbQuery.toString())) {
        USFEnv.getLog()
            .writeDebug(
                "updateStageUsacForm(): SUCCESS. sbQuery is " + sbQuery.toString() + "\n",
                this,
                null);
        return true;
      } else {
        USFEnv.getLog()
            .writeDebug(
                "searchStageUsacFormByInv(): Failure!  sbQuery is " + sbQuery.toString() + "\n",
                this,
                null);
        return false;
      }

    } catch (Exception e) {
      USFEnv.getLog()
          .writeCrit(
              "updateStageUsacForm(long lQL>, long lStageUsacForm) "
                  + "Unable to update a stage_usac_form record: "
                  + e.getMessage()
                  + " with Query: "
                  + sbQuery.toString(),
              this,
              e);
      return false;
    }

    // update was not successful, return false
    // return false;
  }
Пример #9
0
  /**
   * Method to build a update clause for all the columns in stage_usac_form table.
   *
   * @return String, the built update clause
   * @exception Throws Exception on error
   */
  protected String buildUpdateClause() throws Exception {
    // the String to return
    StringBuffer sbUpdate = null;

    // init the buffer
    sbUpdate = new StringBuffer("update stage_usac_form set ");

    // build the query
    sbUpdate.append("HDR_SPIN = " + this.HDR_SPIN);
    sbUpdate.append(", SPIN_NM = '" + this.SPIN_NM + "'");
    sbUpdate.append(", RCPNT_EMAIL = '" + this.RCPNT_EMAIL + "'");
    sbUpdate.append(", USAC_EMAIL = '" + this.USAC_EMAIL + "'");
    sbUpdate.append(", RFRNC_NMBR = '" + this.RFRNC_NMBR + "'");
    sbUpdate.append(", RCRD_CNT = " + this.RCRD_CNT);
    sbUpdate.append(", TOT_PAYMENT = " + this.TOT_PAYMENT);

    if (this.USAC_PRCS_DAT == null) {
      sbUpdate.append(", USAC_PRCS_DAT = null");
    } else {
      sbUpdate.append(", USAC_PRCS_DAT = '" + this.USAC_PRCS_DAT + "'");
    }

    sbUpdate.append(", RTRCT_FLAG = '" + this.RTRCT_FLAG + "'");
    sbUpdate.append(", DTL_SPIN = " + this.DTL_SPIN);
    sbUpdate.append(", FRN = " + this.FRN);
    sbUpdate.append(", SDC_INV_NO = '" + this.SDC_INV_NO + "'");
    sbUpdate.append(", AMT_PAID = " + this.AMT_PAID);
    sbUpdate.append(", DSBRSMNT_TXT = '" + this.DSBRSMNT_TXT + "'");

    if (this.EMAIL_DATE == null) {
      sbUpdate.append(", EMAIL_DATE = null");
    } else {
      sbUpdate.append(", EMAIL_DATE = '" + this.EMAIL_DATE + "'");
    }

    sbUpdate.append(", FILENAME = '" + this.FILENAME + "'");

    if (this.PROCESS_DATE == null) {
      sbUpdate.append(", PROCESS_DATE = null");
    } else {
      sbUpdate.append(", PROCESS_DATE = '" + this.PROCESS_DATE + "'");
    }

    sbUpdate.append(", STATUS = " + this.STATUS);

    return sbUpdate.toString();
  }
Пример #10
0
  /**
   * Method to build a select clause for all the columns in stage_usac_form table.
   *
   * @return String, the built select clause
   */
  protected String buildSelectClause() {
    // the String to return
    StringBuffer sbSelect = null;

    // init the buffer
    sbSelect = new StringBuffer("select ");

    // build the query
    sbSelect.append("ROWID, ");
    sbSelect.append("nvl(HDR_SPIN,0) HDR_SPIN, ");
    sbSelect.append("nvl(SPIN_NM,'') SPIN_NM, ");
    sbSelect.append("nvl(RCPNT_EMAIL,'') RCPNT_EMAIL, ");
    sbSelect.append("nvl(USAC_EMAIL,'') USAC_EMAIL, ");
    sbSelect.append("nvl(RFRNC_NMBR,'') RFRNC_NMBR, ");
    sbSelect.append("nvl(RCRD_CNT,0) RCRD_CNT, ");
    sbSelect.append("nvl(TOT_PAYMENT,0) TOT_PAYMENT, ");
    sbSelect.append("USAC_PRCS_DAT, ");
    sbSelect.append("nvl(RTRCT_FLAG,'') RTRCT_FLAG, ");
    sbSelect.append("nvl(DTL_SPIN,0) DTL_SPIN, ");
    sbSelect.append("nvl(FRN,0) FRN, ");
    sbSelect.append("nvl(SDC_INV_NO,'') SDC_INV_NO, ");
    sbSelect.append("nvl(AMT_PAID,0) AMT_PAID, ");
    sbSelect.append("nvl(replace(DSBRSMNT_TXT,';','<BR>'),'') DSBRSMNT_TXT, ");
    sbSelect.append("EMAIL_DATE, ");
    sbSelect.append("nvl(FILENAME,'') FILENAME, ");
    sbSelect.append("PROCESS_DATE, ");
    sbSelect.append("nvl(STATUS,0) STATUS ");
    sbSelect.append(" from stage_usac_form ");

    return sbSelect.toString();
  }
Пример #11
0
  /**
   * This method pretty print the Class Object StageUsacForm
   *
   * @return String sbStr - Class Object StageUsacForm
   */
  public String toString() {
    // init the buffer
    StringBuffer sbStr = new StringBuffer();

    try {
      sbStr.append("\n HDR_SPIN: " + HDR_SPIN);
      sbStr.append("\n SPIN_NM: " + SPIN_NM);
      sbStr.append("\n RCPNT_EMAIL: " + RCPNT_EMAIL);
      sbStr.append("\n USAC_EMAIL: " + USAC_EMAIL);
      sbStr.append("\n RFRNC_NMBR: " + RFRNC_NMBR);
      sbStr.append("\n RCRD_CNT: " + RCRD_CNT);
      sbStr.append("\n TOT_PAYMENT: " + TOT_PAYMENT);
      sbStr.append("\n USAC_PRCS_DAT: " + USAC_PRCS_DAT);
      sbStr.append("\n RTRCT_FLAG: " + RTRCT_FLAG);
      sbStr.append("\n DTL_SPIN: " + DTL_SPIN);
      sbStr.append("\n FRN: " + FRN);
      sbStr.append("\n SDC_INV_NO: " + SDC_INV_NO);
      sbStr.append("\n AMT_PAID: " + AMT_PAID);
      sbStr.append("\n DSBRSMNT_TXT: " + DSBRSMNT_TXT);
      sbStr.append("\n EMAIL_DATE: " + EMAIL_DATE);
      sbStr.append("\n FILENAME: " + FILENAME);
      sbStr.append("\n PROCESS_DATE: " + PROCESS_DATE);
      sbStr.append("\n STATUS: " + STATUS);
    } catch (Exception e) {
      USFEnv.getLog()
          .writeCrit(
              "toString(): Unable to convert stage_usac_form to a string. " + e.getMessage(),
              this,
              e);
    }
    return sbStr.toString();
  }
Пример #12
0
  /**
   * This method searches the table STAGE_USAC_FORM for the distinctfilename provided for the usac
   * refrence number given
   *
   * @param Stringusac refrence number
   * @return Vector containing the Result Set
   */
  public Vector selectFileName(String rfrnc_nmbr) {
    // the buffer for the query
    StringBuffer sbQuery = null;
    Vector rsVector = new Vector();
    PreparedStatement psStmt = null;
    ResultSet rsResult = null;

    try {
      // init the buffer
      sbQuery = new StringBuffer("");
      // build the query
      sbQuery.append(buildSelectFileName());
      sbQuery.append(" WHERE ");
      sbQuery.append(" rfrnc_nmbr = ?");
      // sbQuery.append( rfrnc_nmbr );
      // sbQuery.append("'");

      USFEnv.getLog()
          .writeDebug(
              "Get FileName by USAC Ref Number  is: " + sbQuery.toString() + "\n", this, null);

      // execute the query
      psStmt = cConn.prepareStatement(sbQuery.toString());
      psStmt.setString(1, rfrnc_nmbr);

      rsResult = psStmt.executeQuery();
      if (rsResult != null) {
        while (rsResult.next()) {
          rsVector.addElement(rsResult.getString("FILENAME"));
          USFEnv.getLog()
              .writeDebug(" THE FILENAME IS : " + rsResult.getString("FILENAME"), this, null);
        }
      }

      if (rsResult != null) rsResult.close();
      if (psStmt != null) psStmt.close();

    } catch (SQLException e) {
      try {
        // close the statment and the ResultSet
        if (rsResult != null) rsResult.close();
        if (psStmt != null) psStmt.close();
      } catch (SQLException ex) {
        USFEnv.getLog()
            .writeCrit("selectSQLCall(): Fail to close result set or prepare statement", this, ex);
      }
      USFEnv.getLog()
          .writeCrit(
              " SQL Exception : SQL Error message "
                  + e.getMessage()
                  + " with Query: \n"
                  + sbQuery.toString(),
              this,
              e);
    } catch (Exception e) {
      try {
        // close the statment and the ResultSet
        if (rsResult != null) rsResult.close();
        if (psStmt != null) psStmt.close();
      } catch (SQLException ex) {
        USFEnv.getLog()
            .writeCrit("selectSQLCall(): Fail to close result set or prepare statement", this, ex);
      }
      USFEnv.getLog()
          .writeCrit(
              "Error Executing the Query(): Error executing query " + e.getMessage(), this, e);
    }

    /* try
      {
             // close the statment and the ResultSet
             if (rsResult != null)
             rsResult.close();
             if (psStmt != null)
             psStmt.close();
      }
      catch (SQLException e)
      {
    USFEnv.getLog().writeCrit("selectSQLCall(): Fail to close result set or prepare statement",this, e);
      }*/

    return rsVector;
  }
Пример #13
0
  /**
   * This method searches the table STAGE_USAC_FORM for the data provided for the status and
   * startDate given
   *
   * @param <code>status</code> USAC record processing status
   * @param <code>startDate</code> selection start Date
   * @return Vector containing the Result Set
   */
  public Vector getUsacErr(int status, java.sql.Date startDate) {
    // the buffer for the query
    StringBuffer sbQuery = null;
    Vector rsVector = new Vector();

    try {
      // init the buffer
      sbQuery = new StringBuffer("");

      // build the query
      sbQuery.append(buildSelectClauseRaw());
      sbQuery.append(" WHERE ");
      sbQuery.append(" status ");

      if (status == 0) {
        USFEnv.getLog()
            .writeDebug("inside the if loop for checking status is null or not", this, null);
        sbQuery.append(" is null");
      } else {
        sbQuery.append("= ");
        sbQuery.append(status);
      }

      if (startDate != null) {
        USFEnv.getLog()
            .writeDebug("inside the if loop for checking startDate is null or not", this, null);
        sbQuery.append(" AND ");
        sbQuery.append(" usac_prcs_dat >= '");
        sbQuery.append(startDate + "' ");
      }

      // order the result by filename, email date applic. number and frn
      // CHANGE MADE - ORDERT BY USAC REFRENCE NUMBER, TOTAL PAYMENT AND FRN IN ADDITION
      sbQuery.append(" ORDER BY rfrnc_nmbr, tot_payment, filename, frn, email_date, frn ASC ");

      // write to log if in debug mode
      USFEnv.getLog().writeDebug("Get USAC by error is: " + sbQuery.toString() + "\n", this, null);

      rsVector = super.selectSQLCall(sbQuery.toString());
    } catch (Exception e) {
      USFEnv.getLog()
          .writeCrit("Caught Exception in get USAC by status: " + e.getMessage(), this, e);
    }

    return (rsVector);
  }