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; }
/** * 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 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 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; }
private Connection getPooledConnection() throws Exception { Connection conn = null; int size = pool.size(); if (size > 0) { conn = (Connection) (pool.elementAt(0)); pool.removeElementAt(0); } else if (users < maxCon || maxCon == 0) { conn = createConnection(); } return conn; }
/** * Method to build a vector from a ResultSet * * @param <code>ResultSet rsResult</code> the result from a query * @return Vector of objects from the ResultSet * @exception Throws Exception on error */ protected Vector buildObjectVector(ResultSet rsResult) throws Exception { // vector for return data Vector vReturn = null; try { // init the vector vReturn = new Vector(super.VECT_INIT_SIZE, super.VECT_GROW_SIZE); // loop through the entire result set while (rsResult.next()) { // create a new one StageUsacForm objTmp = new StageUsacForm(); // set the attributes // ADDING A NEW FIELD "ROWID" objTmp.ROWID = rsResult.getString("ROWID"); objTmp.HDR_SPIN = rsResult.getLong("HDR_SPIN"); objTmp.SPIN_NM = rsResult.getString("SPIN_NM"); objTmp.RCPNT_EMAIL = rsResult.getString("RCPNT_EMAIL"); objTmp.USAC_EMAIL = rsResult.getString("USAC_EMAIL"); objTmp.RFRNC_NMBR = rsResult.getString("RFRNC_NMBR"); objTmp.RCRD_CNT = rsResult.getLong("RCRD_CNT"); objTmp.TOT_PAYMENT = rsResult.getDouble("TOT_PAYMENT"); objTmp.USAC_PRCS_DAT = rsResult.getDate("USAC_PRCS_DAT"); objTmp.RTRCT_FLAG = rsResult.getString("RTRCT_FLAG"); objTmp.DTL_SPIN = rsResult.getLong("DTL_SPIN"); objTmp.FRN = rsResult.getLong("FRN"); objTmp.SDC_INV_NO = rsResult.getString("SDC_INV_NO"); objTmp.AMT_PAID = rsResult.getDouble("AMT_PAID"); objTmp.DSBRSMNT_TXT = rsResult.getString("DSBRSMNT_TXT"); objTmp.EMAIL_DATE = rsResult.getDate("EMAIL_DATE"); objTmp.FILENAME = rsResult.getString("FILENAME"); objTmp.PROCESS_DATE = rsResult.getDate("PROCESS_DATE"); objTmp.STATUS = rsResult.getLong("STATUS"); // add it to the vector vReturn.addElement(objTmp); } return vReturn; } catch (Exception e) { throw new Exception("BuildObjectVector()\n" + e.getMessage()); } }
public void insert(Object[] obj) { try { DefaultTableModel dtt = (DefaultTableModel) getModel(); dtt.addRow(obj); dataBaru.add(getModel().getRowCount() - 1); } catch (Exception e) { e.printStackTrace(); } }
private ConnectionPool(int initCon, int maxCon) throws Exception { this.initCon = initCon; this.maxCon = maxCon; pool = new Vector(); for (int i = 0; i < initCon; i++) { pool.add(createConnection()); } }
/** * This Method returns the 2D Array which is the list of the Items for the selected WorkOrder of * the Customer. * * @param workorderId - WorkOrder Id Foreign Key of WRK_ORDR_DETS table * @return Object[][] - 2D Array which is List of the Items for the WorkOrder */ public Object[][] getWorkOrderItems(long workorderId) throws RemoteException { WrkOrdrDets wrkordrdts_obj = new WrkOrdrDets(conn); RHCCBlgKeys blgkeys_obj = new RHCCBlgKeys(conn); Vector workorderdetList = wrkordrdts_obj.getProductList(workorderId); // Hashtable BSysList = (Hashtable) USFEnv.getBillSystems(); BlgSys blgsys = new BlgSys(); Hashtable BSysList = (Hashtable) blgsys.searchBlgSys(); Enumeration BSys = BSysList.keys(); // The 2-Dimensional array to hold the Items Billing System wise. Object[][] prodList = new Object[BSysList.size()][workorderdetList.size() + 1]; // The Number of Billing Systems are assumed to be equal to the // Static Load Billing Systems Hashtable size. The Billing Systems will // be in order starting from 1 and incrementing by one. for (int i = 0; i < BSysList.size(); i++) { // Set the 2D array to store the Billing System as the first element // of each row. prodList[i][0] = String.valueOf(i + 1); } // Loop throught the WorkOrder Items List and place them in the appropriate // positions in the 2D array. for (int j = 0; j < workorderdetList.size(); j++) { // Determine the Billing System of the Product Vector tmpVector = new Vector(); WrkOrdrDets workorderObj = (WrkOrdrDets) workorderdetList.elementAt(j); RHCCBlgKeys bkObj = blgkeys_obj.searchRHCCBlgKeys(((WrkOrdrDets) workorderdetList.elementAt(j)).getRBKID()); int bsid = (new Long(bkObj.getBsId())).intValue(); tmpVector.addElement(bkObj); tmpVector.addElement(workorderObj); // Based on the Billing System Id retreived place the Product Object // in the 2D array. int k = 1; while (prodList[bsid - 1][k] != null) { k = k + 1; } prodList[bsid - 1][k] = tmpVector; } // Return the 2D array return prodList; }
/** * 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; }
/** * 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 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; }
public void InsertDataTable() { for (int i = 0; i < dataBaru.size(); i++) { try { rowSet.moveToInsertRow(); for (int ii = 0; ii < insertKolom.size(); ii++) { String temp = insertKolom.get(ii + ""); String data[] = temp.split("#"); int no = 0; boolean constanta = false; if (data[1].trim().toLowerCase().equals("c")) { constanta = true; } else { try { no = Integer.parseInt(data[1].trim()); } catch (Exception e) { no = 0; } } if (data[0].trim().toLowerCase().contains("i")) { if (data[2].trim().toLowerCase().equals("boolean")) { String value = ""; if (data[4].trim().toLowerCase().equals("int")) { if (getValueAt( new Integer(dataBaru.get(i).toString()), Integer.parseInt(data[1].trim())) .toString() .equals("true")) { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "1"; } else { value = "Y"; } } else { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "0"; } else { value = "N"; } } try { rowSet.updateInt(data[3].trim().toString(), Integer.parseInt(value)); } catch (Exception e) { e.printStackTrace(); } } else { if (getValueAt( new Integer(dataBaru.get(i).toString()), Integer.parseInt(data[1].trim())) .toString() .equals("true")) { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "1"; } else { value = "Y"; } } else { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "0"; } else { value = "N"; } } try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } } else if (data[2].trim().toLowerCase().equals("date")) { String value = ""; java.util.Date dd = (java.util.Date) getValueAt(new Integer(dataBaru.get(i).toString()), no); if (data[4].equals("dd-MM-yyyy")) { value = Function.dateToString(dd); } else if (data[4].equals("MM-yyyy")) { value = Function.monthToString(dd).trim(); } else if (data[4].equals("yyyy")) { // value = Function.yearToDate(sql); JOptionPane.showMessageDialog(null, "Belom Support"); } // JOptionPane.showMessageDialog(null, value.length()); try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } else if (data[2].trim().toLowerCase().equals("string") || data[2].trim().toLowerCase().equals("numeric")) { String value = ""; if (constanta) { value = data[6].trim(); } else { try { value = getValueAt(new Integer(dataBaru.get(i).toString()), no).toString(); } catch (Exception e) { try { value = getValueAt(new Integer(dataBaru.get(i).toString()), no).toString(); } catch (Exception ee) { value = ""; } } } if (data[4].trim().toLowerCase().equals("int")) { try { if (value.trim().equals("")) { value = "0"; } rowSet.updateLong(data[3].trim().toString(), Long.parseLong(value.trim())); } catch (Exception e) { e.printStackTrace(); } } else { try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } } else if (data[2].trim().toLowerCase().equals("combo")) { String value = ""; String valueKolom = ""; try { valueKolom = getValueAt(new Integer(dataBaru.get(i).toString()), no).toString().trim(); } catch (Exception e) { valueKolom = ""; } try { value = Function.getValueFromCell(data[5].trim(), valueKolom); } catch (Exception e) { value = ""; } if (data[4].trim().toLowerCase().equals("int")) { try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } else { try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } } } } // JOptionPane.showMessageDialog(null, rowSet.getCommand()); rowSet.insertRow(); } catch (Exception e) { e.printStackTrace(); } } }
public void updateDataTable() { for (int i = 0; i < dataAsli.size(); i++) { try { rowSet.absolute(new Integer(dataAsli.elementAt(i).toString())); if (rowSet.next()) { for (int ii = 0; ii < updateKolom.size(); ii++) { String temp = updateKolom.get(ii + ""); String data[] = temp.split("#"); int no = 0; try { no = Integer.parseInt(data[1].trim()); } catch (Exception e) { no = 0; } if (data[0].trim().toLowerCase().contains("u")) { if (data[2].trim().toLowerCase().equals("boolean")) { String value = ""; if (data[4].trim().toLowerCase().equals("int")) { if (getValueAt( new Integer(dataAsli.get(i).toString()), Integer.parseInt(data[1].trim())) .toString() .equals("true")) { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "1"; } else { value = "Y"; } } else { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "0"; } else { value = "N"; } } try { rowSet.updateInt(data[3].trim().toString(), Integer.parseInt(value)); } catch (Exception e) { e.printStackTrace(); } } else { if (getValueAt( new Integer(dataAsli.get(i).toString()), Integer.parseInt(data[1].trim())) .toString() .equals("true")) { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "1"; } else { value = "Y"; } } else { if (data[5].trim().equals("1") || data[5].trim().equals("0")) { value = "0"; } else { value = "N"; } } try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } } else if (data[2].trim().toLowerCase().equals("date")) { String value = ""; java.util.Date dd = (java.util.Date) getValueAt(new Integer(dataAsli.get(i).toString()), no); if (data[4].equals("dd-MM-yyyy")) { value = Function.dateToString(dd); } else if (data[4].equals("MM-yyyy")) { value = Function.monthToString(dd); } else if (data[4].equals("yyyy")) { // value = Function.yearToDate(sql); JOptionPane.showMessageDialog(null, "Belom Support"); } try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } else if (data[2].trim().toLowerCase().equals("string") || data[2].trim().toLowerCase().equals("numeric")) { String value = ""; String data1 = data[1].trim(); if (data1 == null || data1.equals("")) { data1 = "0"; } try { value = getValueAt(new Integer(dataAsli.get(i).toString()), no).toString(); } catch (Exception e) { // e.printStackTrace(); try { value = getValueAt(new Integer(dataAsli.get(i).toString()), no).toString(); } catch (Exception ee) { value = ""; // e.printStackTrace(); } } if (data[4].trim().toLowerCase().equals("int")) { try { if (value.trim().equals("")) { value = "0"; } rowSet.updateLong(data[3].trim().toString(), Long.parseLong(value.trim())); } catch (Exception e) { e.printStackTrace(); } } else { try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } } else if (data[2].trim().toLowerCase().equals("combo")) { String value = ""; value = Function.getValueFromCell( data[5].trim(), getValueAt(new Integer(dataAsli.get(i).toString()), no).toString().trim()); if (data[4].trim().toLowerCase().equals("int")) { try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } else { try { rowSet.updateString(data[3].trim().toString(), value.trim()); } catch (Exception e) { e.printStackTrace(); } } } } } rowSet.updateRow(); } } catch (Exception e) { e.printStackTrace(); } } }
/** * 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; }
public Vector getFiledetails(String frn, String invid, String year) { String query; Vector inv = new Vector(); Statement stmt = null; ResultSet rs = null; query = " SELECT rci_st, wrk_ordr_no, s.bs_nm, b.rbk_keys, rci_nm, wod_id, "; query = query + " srv_sub_cat_nm, h.bill_dat, TO_CHAR (h.bill_dat, 'MM/DD/YYYY'), "; query = query + " TO_CHAR (h.CRDT_AMT, 'FM999999990.00'), "; query = query + " inv_stat invstat "; query = query + " from "; query = query + " rhcc_cust_infos, "; query = query + " wo_det_hsties h, "; query = query + " wrk_ordr_dets, "; query = query + " wrk_ordr, "; query = query + " rhcc_blg_keys b, "; query = query + " srv_sub_cat, "; query = query + " fung_yr, "; query = query + " blg_sys s "; query = query + " WHERE wo_id = wo_id_fk "; query = query + " AND wod_id = wod_id_fk "; query = query + " AND rci_id = rci_id_fk "; query = query + " AND wdh_stat = 'P' "; query = query + " AND b.bs_id_fk != 2 "; query = query + " AND inv_stat IS NOT NULL "; query = query + " AND ssc_id = ssc_id_fk "; query = query + " AND wrk_ordr_dets.rbk_id_fk = b.rbk_id "; query = query + " AND b.bs_id_fk = s.bs_id "; query = query + " AND fung_yr.yr = wrk_ordr.yr_fk and "; query = query + " fung_yr.yr=" + year + " and "; if ((frn != null) && (!(frn).equals(""))) { query = query + " wrk_ordr_no='" + frn + "' and "; } query = query + " rhcc_inv_id_fk=" + invid + " and "; query = query + " wrk_ordr.sc_id_fk=5 "; query = query + " UNION ALL "; query = query + " select rci_st,wrk_ordr_no, s.bs_nm||'-'||substr(GETCONDITION( rm_rgn "; query = query + " , "; query = query + " substr(rbk_keys,1,3) "; query = query + " , "; query = query + " substr(rbk_keys,4,3) "; query = query + " ) "; query = query + " ,1,1),b.rbk_keys,rci_nm,wod_id,srv_sub_cat_nm,h.bill_dat, "; query = query + " to_char(h.bill_dat,'MM/DD/YYYY'),to_char(h.CRDT_AMT,'FM999999990.00'),inv_stat invstat "; query = query + " from rhcc_cust_infos,wo_det_hsties h,wrk_ordr_dets,wrk_ordr, "; query = query + " rhcc_blg_keys b,srv_sub_cat,rgn_map,fung_yr,blg_sys s "; query = query + " where wo_id = wo_id_fk "; query = query + " AND wod_id = wod_id_fk "; query = query + " AND rci_id = rci_id_fk and "; query = query + " wdh_stat='P' and "; query = query + " b.bs_id_fk=2 and "; query = query + " inv_stat is not null and "; query = query + " ssc_id=ssc_id_fk and "; query = query + " wrk_ordr_dets.RBK_ID_FK = b.RBK_ID AND "; query = query + " b.bs_id_fk=s.bs_id and "; query = query + " FUNG_YR.YR = wrk_ordr.YR_FK and "; query = query + " fung_yr.yr=" + year + " and "; if ((frn != null) && (!(frn).equals(""))) { query = query + " wrk_ordr_no='" + frn + "' and "; } query = query + " rhcc_inv_id_fk=" + invid + " and "; query = query + " wrk_ordr.sc_id_fk='5' and "; query = query + " rm_npa = substr(b.rbk_keys,1,3) "; USFEnv.getLog().writeDebug("rhccDinvview:Query :" + query, this, null); try { stmt = conn.createStatement(); rs = stmt.executeQuery(query); while (rs.next()) { RhccDinvview linedet = new RhccDinvview(null); linedet.strCuststate = rs.getString(1); linedet.strFRN = rs.getString(2); linedet.strBlgsys = rs.getString(3); linedet.strBlgkey = rs.getString(4); linedet.strCustnm = rs.getString(5); linedet.strReconkey = rs.getString(6); linedet.strProdnm = rs.getString(7); linedet.strBdate = rs.getString(9); linedet.strProdcost = rs.getString(10); linedet.strInvstatus = rs.getString(11); inv.addElement(linedet); linedet = null; } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { USFEnv.getLog() .writeCrit("RhccDinvgen: The Details of the Line Item 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 inv; }
/** * This is the main controller logic for item selection servlet. This determines whether to show a * list of items for a WorkOrder, add a item, edit a item's detail information, or delete a item. * The product_action parameter is past to this servlet to determine what action to perform. The * product_action parameter is a button defined by JSPs related to product presentation screens. * * <p>The default action is to show all product related to a parent WorkOrder. * * @param req HttpServlet request * @param resp HttpServlet response */ public void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { WorkOrderDetailRemote workorderdetEJBean = null; securityChecks(req, resp); // Get the current session HttpSession session = req.getSession(false); if (session == null) return; String sTmp = ""; // Get a new business logic EJB (custInfoEJBean) USFEnv.getLog().writeDebug("getting buslogic EJB", this, null); try { workorderdetEJBean = workorderdetHome.create(); USFEnv.getLog().writeDebug("EJBean Created", this, null); } catch (CreateException e) { String errorMsg = "Critical Exception in ItemsServlet"; USFEnv.getLog().writeCrit(errorMsg + " failed EJB creation", this, e); errorJSP(req, resp, errorMsg); return; } catch (RemoteException e) { String errorMsg = "Critical Exception in ItemsServlet"; USFEnv.getLog().writeCrit(errorMsg + " failed EJB connect", this, e); errorJSP(req, resp, errorMsg); return; } catch (Exception e) { String errorMsg = "Critical Exception no EJB created"; USFEnv.getLog().writeCrit(errorMsg + " failed EJB creation", this, e); errorJSP(req, resp, errorMsg); return; } try { // Button user pressed in the itemselection.jsp String sJSPAction = req.getParameter("product_action"); USFEnv.getLog().writeWarn("product_action from JSP: " + sJSPAction, this, null); short year = Short.valueOf((String) session.getValue("Iyear")).shortValue(); long customerId = Long.valueOf((String) session.getValue("rcustId")).longValue(); long applicationId = Long.valueOf((String) session.getValue("rappid")).longValue(); long workorderId = 0; long bkId = 0; long billkeyId = 0; long bsId = 0; long wodId = 0; String editflag = ""; String bsNm = ""; java.sql.Date strtDate = null; java.sql.Date endDate = null; String prodError = ""; // Check to make sure session does have an WorkOrder ID if (session.getValue("WorkOrderId") != null) { // If Yes get the parent WorkOrder ID from session workorderId = Long.valueOf((String) session.getValue("WorkOrderId")).longValue(); } // Button Action from JSP is empty then show the Default page of the // Product Listing. if (USFUtil.isBlank(sJSPAction)) { USFEnv.getLog().writeDebug("Display product list for FRN.", this, null); // list items for parent Work Order Number Vector billingsystems = null; WrkOrdrDets workorderdets = new WrkOrdrDets(); billingsystems = workorderdets.getBillingSystems(); req.setAttribute("BillingSystems", billingsystems); for (int s = 0; s < billingsystems.size(); s++) USFEnv.getLog() .writeDebug("INSIDE BILLING SYSTEMS " + billingsystems.elementAt(s), this, null); listProducts(workorderId, workorderdetEJBean, session, req, resp); return; } // Button action from JSP is to Add a new Item else if (sJSPAction.equals("add")) { // Remove the Product Object from session session.removeValue("prodObj"); editflag = "addnew"; req.setAttribute("editflag", editflag); // Read the Item and the Billing System for adding the New // Item. String formProdBsId = (String) req.getParameter("formProdId"); String formProdId = formProdBsId.substring(0, formProdBsId.indexOf("|")); String formProdName = formProdBsId.substring(formProdBsId.indexOf("|") + 1); long rscId = (new Long(formProdId).longValue()); // bsId = (new Long( formBsId ).longValue()) ; String billSystem = formProdName.substring(0, formProdName.indexOf("-")); String itemName = formProdName.substring(formProdName.indexOf("-") + 1); BlgSys blgSys = new BlgSys(); bsId = blgSys.getBsId(billSystem); // Create db connection for EJB workorderdetEJBean.connect(); // Get the WorkOrder Object. WorkOrder woObj = workorderdetEJBean.getWorkOrderInfo(workorderId); // Get the list of Billing Keys for the Billing System. String[] blgKeys = workorderdetEJBean.getBillingKeys(customerId, bsId); // Release db connection for EJB workorderdetEJBean.release(); // If no Billing Keys for the Billing System selected then redirect // to the List Items screen and show the error Message. if (blgKeys == null) { BlgSys blgsys = new BlgSys(); Hashtable bSysList = (Hashtable) blgsys.searchBlgSys(); // Hashtable bSysList = (Hashtable) USFEnv.getBillSystems(); // Set the JSP error message req.setAttribute("errorMsg", "No BTNs for Billing System " + billSystem); // list products for parent WorkOrder Number listProducts(workorderId, workorderdetEJBean, session, req, resp); return; } req.setAttribute("prodcredit", "N"); req.setAttribute("bsystem", String.valueOf(bsId)); req.setAttribute("bkList", blgKeys); req.setAttribute("prodId", formProdId); String wid = String.valueOf(workorderId); session.putValue("workorderId", wid); req.setAttribute("billingsystem", billSystem); req.setAttribute("itemname", itemName); // Include the JSP to Edit Product includeJSP(req, resp, ITEM_JSP_PATH, "editItem"); return; } // Button action from JSP is to Edit a Product else if (sJSPAction.equals("edit")) { String bsysid = req.getParameter("bsId"); session.putValue("bysysidforedit", bsysid); wodId = (new Long((String) req.getParameter("wodId"))).longValue(); // bkId = (new Long( (String) req.getParameter("bkId"))).longValue() ; session.putValue("workorderdetid", String.valueOf(wodId)); bsId = (new Long((String) req.getParameter("bsId"))).longValue(); BlgSys blgSys = new BlgSys(); bsNm = blgSys.getBsName(bsId); // Create db connection for EJB workorderdetEJBean.connect(); // WorkOrder woObj = workorderdetEJBean.getWorkOrderInfo(workorderId); // Get the WorkOrder Number Object WrkOrdrDets wodets_obj = new WrkOrdrDets(); wodets_obj = workorderdetEJBean.getProductInfo(wodId); // Get the List of Billing Keys for the Billing System. String[] blgKeys = workorderdetEJBean.getBillingKeys(customerId, bsId); // Check if the Item has any Credits. If any credits then Billing // Key is not Editable else Editable. if (workorderdetEJBean.hasCredits(wodId)) { req.setAttribute("prodcredit", "Y"); } else { req.setAttribute("prodcredit", "N"); } // Release db connection for EJB workorderdetEJBean.release(); // If Item Object is not null (which generally is the case) then // set the Attributes for the JSP. if (wodets_obj != null) { // Put the Item Object in session editflag = "edit"; session.putValue("wodets", wodets_obj); req.setAttribute("editflag", editflag); // Set the attributes for the Billing System, Billing Key List, req.setAttribute("bkList", blgKeys); req.setAttribute("bsname", bsNm); // Include the JSP to Edit the Item. includeJSP(req, resp, ITEM_JSP_PATH, "editItem"); return; } // If Item Object is null (which generally should not Occur) show // the Default page of Item List for the WorkOrder Number else { // Set the JSP error message req.setAttribute( "errorMsg", "Product Key - " + wodId + " Information not available in the Data Base"); // list items for parent WorkOrder Number listProducts(workorderId, workorderdetEJBean, session, req, resp); return; } } // Button action from JSP is to Delete an Item else if (sJSPAction.equals("delete")) { String formWodId = req.getParameter("wodId"); wodId = (new Long((String) req.getParameter("wodId"))).longValue(); // Create db connection for EJB workorderdetEJBean.connect(); // Delete the Item if (workorderdetEJBean.deleteProduct(wodId)) { req.setAttribute("errorMsg", "Product Key - " + wodId + " Deleted"); } else { req.setAttribute( "errorMsg", "Deletion Failed. Product Key - " + wodId + " is associated with amounts."); } // Release db connection for EJB workorderdetEJBean.release(); // Show the Item List screen listProducts(workorderId, workorderdetEJBean, session, req, resp); return; } // Button action from JSP is to Save a Product. This includes Insertion // of New Product or Updation of an Existing Product. else if (sJSPAction.equals("save")) { boolean save = false; boolean newProd = false; // long qty=0; // Read the Billing System Id String formBsId = (String) req.getParameter("bs_id"); String bsysid = (String) req.getParameter("bsysid"); /* String trans_type = (String) req.getParameter("trans_type"); //String quantity = (String) req.getParameter("qty"); String quantity=""; if (!(req.getParameter("qty").equals(""))) { quantity=(String) req.getParameter("qty"); qty = (new Long(quantity).longValue()); } */ String prod_stat = (String) req.getParameter("prod_stat"); double nrcg_dscnt = (new Double((String) req.getParameter("NonRecurringDiscount"))).doubleValue(); double rcg_dscnt = (new Double((String) req.getParameter("RecurringDiscount"))).doubleValue(); String start_month = (String) req.getParameter("strt_month"); String start_day = (String) req.getParameter("strt_day"); String start_year = (String) req.getParameter("strt_year"); String end_month = (String) req.getParameter("end_month"); String end_day = (String) req.getParameter("end_day"); String end_year = (String) req.getParameter("end_year"); String start_date = start_month + "-" + start_day + "-" + start_year; String end_date = end_month + "-" + end_day + "-" + end_year; long wrkordrid = (new Long((String) session.getValue("WorkOrderId"))).longValue(); String for_editing = req.getParameter("for_editing"); String for_new = req.getParameter("for_new"); String formBkId = (String) req.getParameter("bk_id"); String formBKId = formBkId.substring(0, formBkId.indexOf("|")); String formBTN = formBkId.substring(formBkId.indexOf("|") + 1); billkeyId = (new Long(formBKId)).longValue(); try { bsId = (new Long((String) req.getParameter("bs_id"))).longValue(); } catch (Exception e) { USFEnv.getLog().writeDebug("Exception is " + e, this, null); } RHCCBlgKeys blgkeys = new RHCCBlgKeys(); if (for_editing.equals("editing")) { blgkeys.setRbkId(billkeyId); blgkeys.setRbkKeys(formBTN); blgkeys.setBsId(new Long(bsysid).longValue()); } if (for_new.equals("new")) { blgkeys.setRbkId(billkeyId); blgkeys.setRbkKeys(formBTN); blgkeys.setBsId(bsId); } int index = 0; WrkOrdrDets wod_obj = new WrkOrdrDets(); // wod_obj.setTxTyp(trans_type); // wod_obj.setQty(qty); wod_obj.setNonRcrgDscnt(nrcg_dscnt); wod_obj.setRcrgDscnt(rcg_dscnt); wod_obj.setRBKID(billkeyId); wod_obj.setWodStat(prod_stat); wod_obj.setWOID(wrkordrid); if (!(start_date.equals(""))) { strtDate = new java.sql.Date((new SimpleDateFormat("MM-dd-yyyy")).parse(start_date).getTime()); wod_obj.setStrtDat(strtDate); } // Else if the Start Date is null update the Item Object Date to // null else { wod_obj.setStrtDat(null); } // If Item Service End Date is not null read the date and update // the Item Object with the date if (!(end_date.equals(""))) { endDate = new java.sql.Date((new SimpleDateFormat("MM-dd-yyyy")).parse(end_date).getTime()); wod_obj.setEndDat(endDate); } // Else if the End Date is null update the Item Object Date to null else { wod_obj.setEndDat(null); } // Check if the Start Date is after the End Date or equals End Date if ((strtDate != null) && (endDate != null) && (strtDate.after(endDate))) { prodError = "Product Service Start Date is after Product Service End Date. \n"; index = 1; } else if ((strtDate != null) && (endDate != null) && (strtDate.equals(endDate))) { prodError = "Product Service Start Date equals Product Service End Date. \n"; } workorderdetEJBean.connect(); if (for_editing.equals("editing")) { long workorderdetID = (new Long((String) session.getValue("workorderdetid"))).longValue(); wod_obj.setWODID(workorderdetID); if (index == 0) { save = workorderdetEJBean.saveProduct(wod_obj); } if (save) { prodError = prodError + "<BR> Product Key - " + wod_obj.getWODID() + " Information updated"; req.setAttribute("error", prodError); } else { prodError = prodError + "<BR> Failed to update Product Information"; req.setAttribute("error", prodError); } } if (for_new.equals("new")) { if (index == 0) { int prodId = Integer.parseInt(req.getParameter("prod_Id")); wod_obj.setProd_id(prodId); save = workorderdetEJBean.saveProduct(wod_obj); } if (save) { prodError = prodError + "<BR> Product Key - " + wod_obj.getWODID() + " Information Saved"; req.setAttribute("error", prodError); } else { prodError = prodError + "<BR> Failed to Save Product Information"; req.setAttribute("error", prodError); } } workorderdetEJBean.release(); listProducts(wrkordrid, workorderdetEJBean, session, req, resp); } } // End of try block catch (Exception e) { if (workorderdetEJBean != null) { // calling bean release method try { workorderdetEJBean.release(); } catch (Exception ex) { USFEnv.getLog().writeCrit(" Exception in calling release() method ", this, e); } } String errorMsg = "Processing Exception in Items Servlet: "; USFEnv.getLog().writeCrit(errorMsg, this, e); errorJSP(req, resp, errorMsg); } // End of catch block } // end of doPost()
// </editor-fold> // <editor-fold defaultstate="collapsed" desc="Select Table"> public void selectTabel(DefaultTableModel tableM) { setModelTable(tableM); try { dataAsli = new Vector(); rowSet.setCommand(sql); rowSet.execute(); DefaultTableModel dtm = (DefaultTableModel) getModel(); int i = 0; // tipeKolom = // untukASIU#noKolomJtable#tipeKolomJTable#namaKolomDB#tipeKolom#atributKolom#nilaiKolom // tipeKolom = boolean, Date, String, combo, Int, Numeric while (rowSet.next()) { Object[] dede = new Object[selectKolom.size()]; for (int ii = 0; ii < selectKolom.size(); ii++) { String temp = selectKolom.get(ii + ""); String data[] = temp.split("#"); int no = 0; try { no = Integer.parseInt(data[1].trim()); } catch (Exception e) { no = 0; } if (data[0].trim().toLowerCase().contains("s")) { if (data[2].trim().toLowerCase().equals("boolean")) { boolean value = false; if (data[4].trim().toLowerCase().equals("int")) { if (rowSet.getInt(data[3].trim()) == Integer.parseInt(data[5].trim())) { value = true; } else { value = false; } } else { if (rowSet.getString(data[3].trim()).equals(data[5].trim())) { value = true; } else { value = false; } } dede[no] = value; } else if (data[2].trim().toLowerCase().equals("date")) { java.util.Date value = new java.util.Date(); if (data[4].equals("dd-MM-yyyy")) { value = Function.dateStringToDate(rowSet.getString(data[3].trim())); } else if (data[4].equals("MM-yyyy")) { value = Function.stringToMonth(rowSet.getString(data[3].trim())); } else if (data[4].equals("yyyy")) { value = Function.yearToDate(rowSet.getString(data[3].trim())); } dede[no] = value; } else if (data[2].trim().toLowerCase().equals("string") || data[2].trim().toLowerCase().equals("combo") || data[2].trim().toLowerCase().equals("numeric")) { String value = ""; if (data[4].trim().toLowerCase().equals("int")) { value = rowSet.getLong(data[3].trim()) + ""; } else { value = rowSet.getString(data[3].trim()); } dede[no] = value; } } } dtm.addRow(dede); dataAsli.add(i); i++; } } catch (Exception e) { e.printStackTrace(); } }
public synchronized void releaseConnection(Connection conn) { pool.add(conn); users--; notifyAll(); }