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; }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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); }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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); } }
/** 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); }
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; }
/** * 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); } }
/** * 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; }
/** * 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; }
/** 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"); } }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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; }
/** * This method updates the BLG_PRD table. * * @exception SQLException, if query fails * @author */ public boolean updateJrnldts(String bpid, String strtdt, String enddt) { String query; boolean update_flag = false; PreparedStatement pstmt = null; ResultSet rs = null; query = "update blg_prd set bp_strt_dat=to_date(?,'MM/DD/YYYY') , bp_end_dat=to_date(?,'MM/DD/YYYY') where bp_id = ?"; USFEnv.getLog().writeDebug("Dinvjrnl:Updation- Query" + query, this, null); try { pstmt = conn.prepareStatement(query); pstmt.setString(1, strtdt); pstmt.setString(2, enddt); pstmt.setString(3, bpid); update_flag = (pstmt.executeUpdate() != 0) ? true : false; if (pstmt != null) pstmt.close(); return update_flag; } catch (SQLException ex) { try { if (pstmt != null) pstmt.close(); } catch (Exception e) { USFEnv.getLog().writeCrit("Unable to close statement", this, e); } USFEnv.getLog().writeCrit("Dinvjrnl: The Updation of Journal Dates failed", this, ex); } catch (Exception e) { try { if (pstmt != null) pstmt.close(); } catch (Exception ex) { USFEnv.getLog().writeCrit("Unable to close statement", this, ex); } USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e); } return false; }
/** * This Method is the Unit of Work related to a Item Deletion. This returns a boolean value based * on the Deletion Result. * * @param wodId - WorkOrder Detail Id(Product Key) Primary Key of WRK_ORDR_DETS table * @return boolean - TRUE ( If Deletion Success) FALSE (Deletion Failed) */ public boolean deleteProduct(long wodId) throws RemoteException { WrkOrdrDets wrkordrdts_obj = new WrkOrdrDets(conn); // Check if the Product credit is greater than zero if (wrkordrdts_obj.getProductDiscount(wodId) > (new Double("0.00")).doubleValue()) { // If Yes return False as product cant be deleted USFEnv.getLog().writeWarn("Cannot Delete Item. Item has Discounts", this, null); return false; } // Else if credit not greater than zero Delete the Product. return wrkordrdts_obj.deleteWrkOrdrDets(wodId); }
/** * This Method checks if the passed product key has any credits. returns a boolean value based on * the check Result. * * @param wodId - Product Key WOD_ID of the WRK_ORDR_DETS table * @return boolean - TRUE ( If product has credits) FALSE (If product has no credits) */ public boolean hasCredits(long wodId) throws RemoteException { WrkOrdrDets wrkordrdts_obj = new WrkOrdrDets(conn); // Check if the Product credit is greater than zero if (wrkordrdts_obj.getProductDiscount(wodId) > (new Double("0.00")).doubleValue()) { // If Yes Return True USFEnv.getLog().writeDebug("Item has Discounts", this, null); return true; } // Return False return false; }
/** * This method queries the database to get the list of the Future Years. * * @exception SQLException, if query fails * @author */ public Vector getFutureyears() { String query; Vector years = new Vector(); Statement stmt = null; ResultSet rs = null; query = " select yr,yr ||'-'|| (yr+1) from fung_yr "; query = query + "where yr >= to_number( to_char(sysdate,'YYYY') ) "; query = query + "and yr not in "; query = query + "( select yr from fung_yr where "; query = query + "((sysdate between strt_dat and end_dat) or "; query = query + "(sysdate-365 between strt_dat and end_dat) or "; query = query + "(sysdate-730 between strt_dat and end_dat)) ) "; USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Query :" + query, this, null); try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); while (rs.next()) { years.addElement(rs.getString(1)); years.addElement(rs.getString(2)); } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: Future Years List 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); } } USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Vector size :" + years.size(), this, null); return years; }
/** * This method queries the database to get the sequence for the BP_ID. * * @exception SQLException, if query fails * @author */ public boolean checkDates(String strtdt, String enddt, String year) { String query; PreparedStatement pstmt = null; ResultSet rs = null; query = "select 'true' from fung_yr where to_date(?,'MM/DD/YYYY') < to_date(?,'MM/DD/YYYY') "; query = query + "and yr = ? and strt_dat-10 <= to_date(?,'MM/DD/YYYY') "; query = query + "and end_dat >= to_date(?,'MM/DD/YYYY') "; USFEnv.getLog().writeDebug("Dinvjrnl: Check Dates Query :" + query, this, null); try { pstmt = conn.prepareStatement(query); pstmt.setString(1, strtdt); pstmt.setString(2, enddt); pstmt.setString(3, year); pstmt.setString(4, strtdt); pstmt.setString(5, enddt); 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; }
/** * This method queries the database to get the list of the Billing Systems. * * @exception SQLException, if query fails * @author */ public Vector getBlgsys() { String query; Vector blgsys = new Vector(); Statement stmt = null; ResultSet rs = null; query = "select distinct decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord,"; query = query + " rtrim(bs_nm||' '||bp_rgn),bs_id_fk||rtrim(bp_rgn) from blg_sys,blg_prd where bs_id=bs_id_fk order by ord"; USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Query :" + query, this, null); try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); while (rs.next()) { blgsys.addElement(rs.getString(3)); blgsys.addElement(rs.getString(2)); } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { USFEnv.getLog().writeCrit("Dinvjrnl: Billing System List 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); } USFEnv.getLog() .writeDebug("Dinvjrnl: Billing System Vector size :" + blgsys.size(), this, null); } return blgsys; }