Пример #1
1
  private String getMonth(String month) {
    String query;
    String i_month = "";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";

    USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, month);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        i_month = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return i_month;
  }
Пример #2
1
  public Vector getUsacFiles(String invoice) {
    Vector fileList = new Vector();
    String query = "";

    query =
        query
            + " SELECT distinct filename, '  Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat ";
    query = query + " FROM stage_usac_form ";
    query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?";
    query = query + " ORDER BY usac_prcs_dat ";

    USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null);

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = cConn.prepareStatement(query);
      pstmt.setString(1, invoice);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        fileList.addElement(rs.getString(1));
        fileList.addElement(rs.getString(2));
      }

      // rs.close();
      // stmt.close();
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();

    } catch (SQLException ex) {
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (Exception e) {
        USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e);
      }
      USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex);
    }

    /*	try
    {
    	if( rs != null)
    		rs.close();
    }
    catch(Exception e)
    {
    	USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
    }
    try
    {
    	if( pstmt != null)
    		pstmt.close();
    }
    catch(Exception e)
    {
    USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
    }*/
    return fileList;
  }
Пример #3
0
  /**
   * This method queries the database to get the list of the Billing Systems.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getYears(String year) {
    String query;
    Vector years = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";

    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, year);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        years.addElement(rs.getString(1));
        years.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return years;
  }
Пример #4
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());
  }
Пример #5
0
  /**
   * This method queries the database to validate the passed in FRN for the selected Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean validateFRN(String frn, String year) {
    String query;
    boolean validfrn = false;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        validfrn = true;
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return false;
  }
Пример #6
0
  /**
   * This method queries the database to get the details related to the bp_id passed.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getBpdet(String bpid) {
    String query;
    Vector bpdet = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";

    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, bpid);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        bpdet.addElement(rs.getString(1));
        bpdet.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return bpdet;
  }
Пример #7
0
  /**
   * This method queries the database to get the name of the Billing System.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getBlgsysnm(String blgsys) {
    String query;
    String blgsysnm = "";
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select bs_nm from blg_sys where bs_id = ?";

    USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, blgsys);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        blgsysnm = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
      }
    }

    return blgsysnm;
  }
Пример #8
0
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getInvno(String invid) {
    String query;
    String invno = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invno = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return invno;
  }
Пример #9
0
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public long getInvID(String inv_no) {
    String query;
    long invID = 0;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_id from inv where inv_no=" + inv_no;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invID = rs.getLong(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return (invID);
  }
Пример #10
0
  /**
   * This method queries the database to get the sequence for the BP_ID.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getBpid() {
    String query;
    String bpid = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select bp_id_seq.nextval from dual ";

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        bpid = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return bpid;
  }
Пример #11
0
  /**
   * This method queries the database to get the SPIN associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getSPINname(String invid) {
    String query;
    String spinnm = "";
    Statement stmt = null;
    ResultSet rs = null;

    //		query =  "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid;
    query =
        "select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id="
            + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        spinnm = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return spinnm;
  }
Пример #12
0
  /**
   * This method queries the database to check if the passed start date for the Journal Month starts
   * exactly after the Previous end date.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
    if (month.length() > ((new Integer("2")).intValue())) {
      month = getMonth(month);
    }

    String query;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
    query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";

    if ((rgn != null) && !(rgn.equals(""))) {
      query = query + " and bp_rgn = ?";
    } else {
      query = query + " and bp_rgn is null";
    }

    query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";

    query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";

    USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, month);
      pstmt.setString(2, month);
      pstmt.setString(3, blgsys);

      int m = 4;
      if ((rgn != null) && !(rgn.equals(""))) {
        pstmt.setString(m, rgn);
        m = m + 1;
      }

      pstmt.setString(m, month);
      pstmt.setString(m + 1, year);
      pstmt.setString(m + 2, year);
      pstmt.setString(m + 3, strtdat);

      rs = pstmt.executeQuery();

      if (rs.next()) {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return false;
  }
Пример #13
0
  /**
   * This method queries the database to get the Journal Dates for the passed in Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getJrnldts(String year) {
    String query;
    Vector jrnllst = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query =
        "select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
    query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
    query =
        query
            + "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
    query =
        query
            + "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
    query = query + "from blg_prd,blg_sys ";
    query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
    query = query + "(select strt_dat from fung_yr where yr = ?) and ";
    query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
    query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
    query = query + "order by ord,nm,bp_strt_dat ";

    USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, year);
      pstmt.setString(2, year);
      pstmt.setString(3, year);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        Dinvjrnl jrnldts = new Dinvjrnl(null);

        jrnldts.strBpid = rs.getString(1);
        jrnldts.strBlgsys = rs.getString(3);
        jrnldts.strBlgmnth = rs.getString(5);
        jrnldts.strBlgstrtdt = rs.getString(6);
        jrnldts.strBlgenddt = rs.getString(7);

        jrnllst.addElement(jrnldts);
        jrnldts = null;
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    USFEnv.getLog()
        .writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
    return jrnllst;
  }
Пример #14
0
  /**
   * This servlet init method sets up business logic EJBs. Servlet init method is only executed once
   *
   * @param config - default parameter
   * @exception ServletException
   */
  public void init(ServletConfig config) throws ServletException {
    super.init(config);
    USFEnv.getLog().writeDebug("initializing buslogic EJB home interfaces", this, null);

    try {
      workorderdetHome = (WorkOrderDetailHome) USFEnv.ic.lookup(WORKORDERDETS_INFORMATION_EJBEAN);
    } catch (NamingException e) {
      USFEnv.getLog().writeDebug("Failed to ic.lookup Naming Exception", this, null);
    }
  }
Пример #15
0
 /** Required Method Implementation */
 public void ejbCreate() throws CreateException, RemoteException {
   try {
     // Data validation EJB
     rhcccustValidateHome = (RhccCustomerHome) USFEnv.ic.lookup(RHCC_VALIDATION_EJBEAN);
     rhcccustValidate = rhcccustValidateHome.create();
   } catch (NamingException e) {
     USFEnv.getLog().writeCrit("Inside Naming exception", this, e);
   } catch (CreateException e) {
     USFEnv.getLog().writeCrit("Inside Create exception", this, e);
   } catch (RemoteException e) {
     USFEnv.getLog().writeCrit("Inside Remote exception", this, e);
   }
   USFEnv.getLog().writeDebug("EJB Created, ref:" + this.toString(), this, null);
 }
Пример #16
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;
  }
Пример #17
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();
  }
Пример #18
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();
  }
Пример #19
0
 public List retrieveAttachmentsByCustomerId(long customerId) throws RemoteException {
   List attachments = new ArrayList();
   AttachmentDAO attachmentDAO = new AttachmentDAO(conn);
   attachments = attachmentDAO.retrieveAttachmentsByCustomerId(customerId);
   USFEnv.getLog().writeDebug("attachments size is......" + attachments.size(), this, null);
   return attachments;
 }
Пример #20
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);
  }
Пример #21
0
  /**
   * This calls the itemselection.jsp and list all items available for a WorkOrder and a WorkOrder's
   * item list
   *
   * @param workorderid - the WO_ID of the parent WorkOrder
   * @param request - servlet request
   * @param response - servlet response
   */
  private void listProducts(
      long workorderId,
      WorkOrderDetailRemote workorderdetEJBean,
      HttpSession session,
      HttpServletRequest req,
      HttpServletResponse resp) {

    try {
      // If any product object is left over in session remove it
      session.removeValue("itemObj");

      // Create db connection for EJB
      workorderdetEJBean.connect();

      // Get the 2D Array which has the List of Items for the WorkOrder
      // grouped by the Billing System.
      Object[][] productList = workorderdetEJBean.getWorkOrderItems(workorderId);

      // Get the WorkOrder Object
      WorkOrder workorderObj = workorderdetEJBean.getWorkOrderInfo(workorderId);

      // Get the List of all Product Names for the WorkOrder
      String[] productNameList = workorderdetEJBean.getProdList(workorderId);

      for (int w = 0; w < productNameList.length; w++)
        USFEnv.getLog()
            .writeDebug("VALUES INSIDE productNameList is" + productNameList[w], this, null);

      // Set the attributes to the itemselection JSP
      req.setAttribute("productNameList", productNameList);
      req.setAttribute("productList", productList);
      req.setAttribute("workorderObj", workorderObj);

      // Release db connection for EJB
      workorderdetEJBean.release();

      // Include the JSP
      includeJSP(req, resp, ITEM_JSP_PATH, "itemselection");
      return;
    } catch (Exception e) {
      String errorMsg = "Fail to list products for a WORKORDER " + workorderId;
      USFEnv.getLog().writeCrit(errorMsg, this, e);
      errorJSP(req, resp, errorMsg);
    }
  }
Пример #22
0
  /**
   * Business unit of work This method is used to update an existing customer after editing
   *
   * @param customer details
   * @return true or false whether the customer is updated or not
   */
  public boolean updateCustomer(RhccCustInfos updateCustomer) throws RemoteException {
    USFEnv.getLog().writeWarn(" Inside updateCustomer", this, null);
    boolean bTmp = false;

    // Get the connection
    RhccCustInfos rhcc_obj = new RhccCustInfos(conn);

    try {
      if (conn != null) {
        bTmp = rhcc_obj.updateCustomerInfo(updateCustomer);
        USFEnv.getLog()
            .writeDebug("newCustomer DB connection, ref: " + conn.toString(), this, null);
      }
    } catch (Exception e) {
      USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
    }
    return bTmp;
  }
Пример #23
0
  /**
   * Business unit of work This method is used to delete an existing customer
   *
   * @param customer Id
   * @return true or false whether the customer is deleted or not
   */
  public boolean delCustomer(String customerId) throws RemoteException {
    USFEnv.getLog().writeWarn(" Inside delCustomer", this, null);
    boolean bTmp = false;

    // Get the connection
    RhccCustInfos rhcc_obj = new RhccCustInfos(conn);

    try {
      if (conn != null) {
        bTmp = rhcc_obj.deleteCustomer(Long.parseLong(customerId));
        USFEnv.getLog()
            .writeDebug("delCustomer DB connection, ref: " + conn.toString(), this, null);
      }
    } catch (Exception e) {
      USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
    }
    return bTmp;
  }
Пример #24
0
 /** This method used to connect to the Conection pool */
 public void connect() throws ConnectException, RemoteException {
   try {
     conn = null;
     conn = dbconn.open();
     USFEnv.getLog().writeDebug("created db connection(), ref: " + conn.toString(), this, null);
   } catch (Exception e) {
     throw new ConnectException("Database connection failure");
   }
 }
Пример #25
0
  /**
   * Business unit of work This method is used to view a particular selected Customer
   *
   * @param customer Id
   * @return customer details
   */
  public RhccCustInfos viewCustomer(String customerId) throws RemoteException {
    RhccCustInfos cTmp = null;

    USFEnv.getLog().writeWarn(" Inside viewCustomer", this, null);

    // Get the connection
    RhccCustInfos rhcc_obj = new RhccCustInfos(conn);
    try {
      if (conn != null) {
        cTmp = rhcc_obj.searchCustomerInfo(customerId);
        USFEnv.getLog()
            .writeDebug("viewCustomer DB connection, ref: " + conn.toString(), this, null);
      }
    } catch (Exception e) {
      USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
    }
    return cTmp;
  }
Пример #26
0
  /**
   * Business unit of work This method show all the RHCC customers for a funding year
   *
   * @param year - funding year
   * @return vCustnmid - Customers for a Funding Year
   */
  public Vector getAllRHCCCustomers(short year) throws RemoteException, NullPointerException {
    Vector vCustnmid = null;

    USFEnv.getLog().writeWarn("Inside getAllRHCCCustomers", this, null);

    // Get the connection

    RhccCustInfos rhcc_obj = new RhccCustInfos();
    try {

      vCustnmid = rhcc_obj.getAllRHCCCustomers(year);
      USFEnv.getLog()
          .writeDebug("getAllRHCCCustomers DB connection, ref: " + conn.toString(), this, null);

    } catch (Exception e) {
      USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
    }
    return vCustnmid;
  }
Пример #27
0
  /**
   * Business unit of work This method is used to save a new Customer or an existing customer after
   * editing
   *
   * @param new customer details
   * @return true or false whether the customer is saved or not
   */
  public boolean saveCustomer(RhccCustInfos saveCustomer, Hashtable custErrors)
      throws RemoteException {
    USFEnv.getLog().writeWarn(" Inside saveCustomer", this, null);

    long customerId;
    boolean bCustErr = false;
    boolean bTmp = false;

    // Get the connection
    RhccCustInfos rhcc_obj = new RhccCustInfos(conn);

    try {
      customerId = saveCustomer.getCustomerId();

      if (conn != null) {
        USFEnv.getLog()
            .writeDebug("saveCustomer DB connection, ref: " + conn.toString(), this, null);
      }
      // Check business logic field value validation
      bCustErr = rhcccustValidate.customerBusVal(saveCustomer, custErrors);

      // Check if critial error occured
      if (!bCustErr) {
        // Saving a new customer
        if (customerId == 0) {
          // One time funding year set for new customer record
          bTmp = newCustomer(saveCustomer);

          USFEnv.getLog()
              .writeWarn(
                  "new RhccCustInfo created, RCI_ID: " + saveCustomer.getCustomerId(), this, null);
        } else {
          // Saving an exiting customer
          bTmp = updateCustomer(saveCustomer);
        }
      }

    } catch (Exception e) {
      USFEnv.getLog().writeCrit("Error in business EJB: ", this, e);
    }
    return bTmp;
  }
Пример #28
0
  /**
   * This method queries the database to get the sequence for the BP_ID.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean checkDuplicate(String blgsys, String year, String month, String rgn) {
    String query;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select 'true' from blg_prd where bs_id_fk=" + blgsys + " and bp_year=" + year;
    query = query + " and bp_month=" + month;

    if ((rgn != null) && !(rgn.equals(""))) {
      query = query + " and bp_rgn='" + rgn + "'";
    } else {
      query = query + " and bp_rgn is null";
    }

    USFEnv.getLog().writeDebug("Dinvjrnl:check Duplicate- Query" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, blgsys);
      pstmt.setString(2, year);
      pstmt.setString(3, month);
      if ((rgn != null) && !(rgn.equals(""))) {
        pstmt.setString(4, rgn);
      }

      rs = pstmt.executeQuery();
      if (rs.next()) {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Date Comparison Failed ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return false;
  }
Пример #29
0
  /**
   * This method queries the database to get the Invoice Numbers list for a particular FRN in a
   * Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getFrninvnos(String frn, String year) {
    String query;
    Vector invnos = new Vector();
    Statement stmt = null;
    ResultSet rs = null;

    USFEnv.getLog().writeWarn("Inside the block-->>RhccDinview Inview", this, null);

    query =
        " select distinct rhcc_inv_id_fk,rhcc_inv.inv_no||' - '||to_char(rhcc_inv.inv_dat,'Mon/DD/YYYY') ";
    query = query + " from wrk_ordr,wrk_ordr_dets,wo_det_hsties,rhcc_inv,fung_yr ";
    query = query + " where wo_id=wo_id_fk and wod_id=wod_id_fk and ";
    query = query + " wdh_stat='P' and ";
    query = query + " inv_stat is not null and ";
    query = query + " rhcc_inv_id_fk=rhcc_inv_id and ";
    query = query + " wrk_ordr_no='" + frn + "' and ";
    query = query + " wrk_ordr.yr_fk=fung_yr.yr and ";
    query = query + " wrk_ordr.yr_fk=" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number List Query:" + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      while (rs.next()) {
        invnos.addElement(rs.getString(1));
        invnos.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog()
          .writeCrit("RhccDinvview: The Invoice Numbers List for FRN not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return invnos;
  }
Пример #30
0
  /**
   * This method Inserts Journal Dates in BLG_PRD table.
   *
   * @exception SQLException, if Insertion fails
   * @author
   */
  public boolean insertJrnldts(
      String bpid,
      String blgsys,
      String rgn,
      String strtdt,
      String enddt,
      String month,
      String year) {
    String query;
    boolean insert_flag = false;
    PreparedStatement pstmt = null;

    query =
        "Insert into blg_prd (bp_id,bs_id_fk,bp_year,bp_month,bp_strt_dat,bp_end_dat,bp_rgn) values ";
    query = query + "(?,?,?,?,to_date(?,'MM/DD/YYYY') ,to_date(?,'MM/DD/YYYY') ,?) ";

    USFEnv.getLog().writeDebug("Dinvjrnl:Insertion - Query" + query, this, null);

    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, bpid);
      pstmt.setString(2, blgsys);
      pstmt.setString(3, year);
      pstmt.setString(4, month);
      pstmt.setString(5, strtdt);
      pstmt.setString(6, enddt);
      pstmt.setString(7, rgn);

      insert_flag = (pstmt.executeUpdate() != 0) ? true : false;
      if (pstmt != null) pstmt.close();
      return insert_flag;
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: The Insertion of Journal Dates failed", this, ex);
      try {
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }
    return false;
  }