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 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 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 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; }
// Get a list of images that have been added, but not yet added into // the database. protected void doFlush() { SpyDB pdb = getDB(); Vector v = null; try { Connection db = pdb.getConn(); Statement st = db.createStatement(); String query = "select * from upload_log where stored is null"; ResultSet rs = st.executeQuery(query); v = new Vector(); while (rs.next()) { v.addElement(rs.getString("photo_id")); } } catch (Exception e) { // Do nothing, we'll try again later. } finally { pdb.freeDBConn(); } // Got the vector, now store the actual images. This is done so // that we don't hold the database connection open whlie we're // making the list *and* getting another database connection to act // on it. if (v != null) { try { for (int i = 0; i < v.size(); i++) { String stmp = (String) v.elementAt(i); storeImage(Integer.valueOf(stmp).intValue()); } } catch (Exception e) { // Don't care, we'll try again soon. } } }
/* return status codes for account/device */ public static int[] getStatusCodes(String accountID, String deviceID) throws DBException { /* account-id specified? */ if (StringTools.isBlank(accountID)) { return new int[0]; } /* device-id specified? */ if (StringTools.isBlank(deviceID)) { deviceID = ALL_DEVICES; } /* select */ // DBSelect: SELECT statucCode FROM StatusCode WHERE (accountID='acct') AND (deviceID='*') ORDER // BY statucCode DBSelect<StatusCode> dsel = new DBSelect<StatusCode>(StatusCode.getFactory()); dsel.setSelectedFields(StatusCode.FLD_statusCode); DBWhere dwh = dsel.createDBWhere(); dsel.setWhere( dwh.WHERE_( dwh.AND( dwh.EQ(StatusCode.FLD_accountID, accountID), dwh.EQ(StatusCode.FLD_deviceID, deviceID)))); dsel.setOrderByFields(StatusCode.FLD_statusCode); /* get list */ java.util.List<Integer> codeList = new Vector<Integer>(); Statement stmt = null; ResultSet rs = null; try { stmt = DBConnection.getDefaultConnection().execute(dsel.toString()); rs = stmt.getResultSet(); while (rs.next()) { int code = rs.getInt(StatusCode.FLD_statusCode); codeList.add(new Integer(code)); } } catch (SQLException sqe) { throw new DBException("Getting StatusCode List", sqe); } finally { if (rs != null) { try { rs.close(); } catch (Throwable t) { } } if (stmt != null) { try { stmt.close(); } catch (Throwable t) { } } } /* return array of status codes */ int codeListInt[] = new int[codeList.size()]; for (int i = 0; i < codeListInt.length; i++) { codeListInt[i] = codeList.get(i).intValue(); } return codeListInt; }
/** * 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; }
/** * Deletes an instructor from the database. Deletes the instructor's courses by invoking the * deleteCourse method. Throws InvalidDBRequestException if instructor not in the database or * other database connection problems. * * @see deleteCourse * @param instructor instructor's user name * @throws InvalidDBRequestException */ public void deleteInstructor(String instructor) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); int count; // delete the instructor's courses ResultSet rs = stmt.executeQuery( "select course_num from course where instructor = '" + instructor + "'"); while (rs.next()) deleteCourse(rs.getString(1).trim(), instructor); // delete the instructor's record count = stmt.executeUpdate("delete from instructor where login ='******'"); rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addCourse: " + e.getMessage()); throw new InvalidDBRequestException("??? "); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
/** * Consulta as fatos do prestador passado em um determinado periodo * * @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as fatos * @param strNumPeriodo - o periodo de referencia do qual se deseja obter os fatos * @return um array de fatos do prestador fornecido como paramentro */ public static final ResumoFato[] buscaResumoFato(String strCdContrato, String strNumPeriodo) throws Exception { Connection con = ConnectionPool.getConnection(); ResumoFato[] fatos = null; PreparedStatement pst; ResultSet rset; int qtdeFatos = 0; try { pst = con.prepareStatement(CONSULTA_RESUMO_FATO); pst.setString(1, strCdContrato); pst.setString(2, strNumPeriodo); pst.setString(3, strCdContrato); pst.setString(4, strNumPeriodo); pst.setString(5, strCdContrato); pst.setString(6, strNumPeriodo); pst.setString(7, strCdContrato); pst.setString(8, strNumPeriodo); pst.setString(9, strCdContrato); pst.setString(10, strNumPeriodo); rset = pst.executeQuery(); if (!rset.next()) { return null; } // if ( ! rset.next() ) do { qtdeFatos += 1; } while (rset.next()); System.out.println("qtdeFatos -> " + qtdeFatos); fatos = new ResumoFato[qtdeFatos]; rset = pst.executeQuery(); qtdeFatos = 0; while (rset.next()) { fatos[qtdeFatos] = montaResumoFato(rset); qtdeFatos++; } // while } catch (SQLException sqle) { sqle.printStackTrace(); throw new Exception( "Não foi possivel estabelecer conexão com a base de " + "dados.Erro:\n" + sqle.getMessage()); } finally { // catch() if (con != null) { con.close(); System.out.println("Fechou a conexao"); } // if } return fatos; } // buscaResumoFato()
/** * 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; }
/** * Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any * error occured to the database connection. * * @param name the instructor's user name * @return a vector containing the list of courses * @throws InvalidDBRequestException */ public Vector getCourseList(String name) throws InvalidDBRequestException { Vector courseList = new Vector(); try { Connection db; Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; // get the course list rs = stmt.executeQuery( "select course_num, course_name from course where instructor = '" + name + "' order by course_num"); while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2)); rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in getCourseList: " + e.getMessage()); throw new InvalidDBRequestException("???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Server Error"); } return courseList; }
/** * 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; }
/** * Deletes students who are older than a certain number of years and not registered to any course. * * @param year students older than year are candidates to be deleted * @throws InvalidDBRequestException */ public void deleteOldStudent(int year) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); // query all student who have been in the database longer than a number of years and not // registered to any course ResultSet rs = stmt.executeQuery( "select login, count(course_id) " + "from student s left join courseRoster r on login = user_login " + "where date_entered < SUBDATE(now(), INTERVAL " + new String().valueOf(year).trim() + " YEAR) " + "group by login, date_entered"); // delete them while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim()); rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addCourse: " + e.getMessage()); throw new InvalidDBRequestException("??? "); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
/** * Transforma os dados obtidos atraves de uma consulta a tabela de fatos do prestador em um objeto * do tipo ResumoFato * * @param rset - um ResultSet contendo o resultado da consulta a tabela de fatos do prestador * @return um objeto do tipo ResumoFato */ private static final ResumoFato montaResumoFato(ResultSet rset) throws SQLException { /* double qtdeNotas; String strCodServico; String strDsServico; double qtdeServico; double valorTotal; strCodServico = rset.getString("pef_codigo"); strDsServico = rset.getString("Descricao"); qtdeNotas = rset.getDouble("QtdeNotas"); qtdeServico = rset.getDouble("QtdeServicos"); valorTotal = rset.getDouble("ValorTotal"); return ( new ResumoFato(qtdeNotas, strCodServico, strDsServico, qtdeServico, valorTotal) );*/ int tipoNota = 0; int qtdeNotas = 0; int tipoRegistro = 0; double valorTotal = 0; tipoNota = rset.getInt("tipo_nota"); tipoRegistro = rset.getInt("tipo_registro"); qtdeNotas = rset.getInt("qtde_notas"); valorTotal = rset.getDouble("valor_total"); return (new ResumoFato(tipoNota, tipoRegistro, qtdeNotas, valorTotal)); } // montaResumoFato()
/** * 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; }
/** * Check the user's name and password and verify that the user is an instructor. Throws * InvalidDBRequestException if user is not an instructor, wrong password, or if any error occured * to the database connection. * * @param name user's user name * @param pass user's password * @throws InvalidDBRequestException */ public void instructorLogin(String name, String pass) throws InvalidDBRequestException { try { Connection db; Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; // check if instructor rs = stmt.executeQuery("select password from instructor where login = '******'"); if (!rs.next()) { if (debug) System.out.println("User not found in the instructor table"); throw new InvalidDBRequestException("Instructor not registered"); } // check password if (!rs.getString(1).equals(pass)) { if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"Invalid SQL in instructor login: "******"???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Server Error"); } }
/** * 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 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; }
/** * Obtains information on a quiz that a student has taken, which includes the student's answer to * the quiz, a unique id of the instance, number of questions in the quiz, number of questions * answered correctly, and vizualisation type of the quiz. Throws InvalidDBRequestException if * cannot find the record of the stuent taking the quiz, quiz is not registered in the database, * or if any error occured to the database connection. * * @param student student's user name * @param quiz quiz name * @param startTime the time the student started the quiz * @return a string tokenizer containing: answer,uniqueID, numQuestions, numCorrect, and * visualType. It uses "@" as the delimiter. * @throws InvalidDBRequestException */ public StringTokenizer getAnswerAndVisualType(String student, String quiz, String startTime) throws InvalidDBRequestException { String answer; String uniqueID; String numQuestions; String numCorrect; String visualType; try { Connection db; Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; // get student's answer, unique id, number of questions, and number of questions answered // correctly rs = stmt.executeQuery( "select transcript, unique_id, num_questions, num_correct from scores " + "where test_name = '" + quiz + "' and user_login = '******' and start_time = '" + startTime + "'"); if (!rs.next()) throw new InvalidDBRequestException("Student has not taken the quiz"); else { answer = rs.getString(1).trim(); uniqueID = rs.getString(2).trim(); numQuestions = rs.getString(3).trim(); numCorrect = rs.getString(4).trim(); } // get quiz vizualisation type rs = stmt.executeQuery("select visual_type from test where name = '" + quiz + "' "); if (!rs.next()) throw new InvalidDBRequestException( "Quiz was not found! Can't retrieve visualization type."); else { visualType = rs.getString(1); } rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in getAnswerAndVisualType: " + e.getMessage()); throw new InvalidDBRequestException("???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } return new StringTokenizer( answer + "@" + uniqueID + "@" + numQuestions + "@" + numCorrect + "@" + visualType, "@"); }
/** * Consulta as glosas do prestador passado em um determinado periodo * * @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as glosas * @param strNumPeriodo - o periodo de referencia do qual se deseja obter os glosas * @return um array de glosas do prestador fornecido como paramentro */ public static final GlosaPrestador[] buscaGlosaPrest(String strCdContrato, String strNumPeriodo) throws Exception { Connection con = ConnectionPool.getConnection(); GlosaPrestador[] glosas = null; PreparedStatement pst; ResultSet rset; int qtdeGlosas = 0; try { pst = con.prepareStatement(CONSULTA_GLOSA); pst.setString(1, strCdContrato); pst.setString(2, strNumPeriodo); rset = pst.executeQuery(); if (!rset.next()) { return null; } // if ( ! rset.next() ) do { qtdeGlosas += 1; } while (rset.next()); System.out.println("qtdeGlosas -> " + qtdeGlosas); glosas = new GlosaPrestador[qtdeGlosas]; pst = con.prepareStatement(CONSULTA_GLOSA); pst.setString(1, strCdContrato); pst.setString(2, strNumPeriodo); rset = pst.executeQuery(); qtdeGlosas = 0; while (rset.next()) { glosas[qtdeGlosas] = montaGlosaPrestador(rset); qtdeGlosas++; } // while } catch (SQLException sqle) { sqle.printStackTrace(); throw new Exception( "Não foi possivel estabelecer conexão com a base de " + "dados.Erro:\n" + sqle.getMessage()); } finally { // catch() if (con != null) { con.close(); System.out.println("Fechou a conexao"); } // if } return glosas; } // consultaGlosaPrest()
/** * 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; }
protected SDMSObject rowToObject(SystemEnvironment env, ResultSet r) throws SDMSException { Long id; Long smeId; Long trId; Long nextTriggerTime; Integer timesChecked; Integer timesTriggered; Long creatorUId; Long createTs; Long changerUId; Long changeTs; long validFrom; long validTo; try { id = new Long(r.getLong(1)); smeId = new Long(r.getLong(2)); trId = new Long(r.getLong(3)); nextTriggerTime = new Long(r.getLong(4)); timesChecked = new Integer(r.getInt(5)); timesTriggered = new Integer(r.getInt(6)); creatorUId = new Long(r.getLong(7)); createTs = new Long(r.getLong(8)); changerUId = new Long(r.getLong(9)); changeTs = new Long(r.getLong(10)); validFrom = 0; validTo = Long.MAX_VALUE; } catch (SQLException sqle) { SDMSThread.doTrace(null, "SQL Error : " + sqle.getMessage(), SDMSThread.SEVERITY_ERROR); throw new FatalException( new SDMSMessage( env, "01110182045", "TriggerQueue: $1 $2", new Integer(sqle.getErrorCode()), sqle.getMessage())); } if (validTo < env.lowestActiveVersion) return null; return new SDMSTriggerQueueGeneric( id, smeId, trId, nextTriggerTime, timesChecked, timesTriggered, creatorUId, createTs, changerUId, changeTs, validFrom, validTo); }
protected void loadTable(SystemEnvironment env) throws SQLException, SDMSException { int read = 0; int loaded = 0; final boolean postgres = SystemEnvironment.isPostgreSQL; String squote = SystemEnvironment.SQUOTE; String equote = SystemEnvironment.EQUOTE; Statement stmt = env.dbConnection.createStatement(); ResultSet rset = stmt.executeQuery( "SELECT " + tableName() + ".ID" + ", " + squote + "NAME" + equote + ", " + squote + "DELETE_VERSION" + equote + ", " + squote + "CREATOR_U_ID" + equote + ", " + squote + "CREATE_TS" + equote + ", " + squote + "CHANGER_U_ID" + equote + ", " + squote + "CHANGE_TS" + equote + " FROM " + tableName() + ""); while (rset.next()) { if (loadObject(env, rset)) ++loaded; ++read; } stmt.close(); SDMSThread.doTrace( null, "Read " + read + ", Loaded " + loaded + " rows for " + tableName(), SDMSThread.SEVERITY_INFO); }
/* return list of all Devices within the specified DeviceGroup (NOT SCALABLE BEYOND A FEW HUNDRED GROUPS) */ public static java.util.List<String> getUsersForGroup(String acctId, String groupId) throws DBException { /* valid account/groupId? */ if (StringTools.isBlank(acctId)) { return null; } else if (StringTools.isBlank(groupId)) { return null; } /* get db selector */ DBSelect dsel = GroupList._getUserListSelect(acctId, groupId); if (dsel == null) { return null; } /* read users for group */ java.util.List<String> usrList = new Vector<String>(); DBConnection dbc = null; Statement stmt = null; ResultSet rs = null; try { dbc = DBConnection.getDefaultConnection(); stmt = dbc.execute(dsel.toString()); rs = stmt.getResultSet(); while (rs.next()) { String usrId = rs.getString(GroupList.FLD_userID); usrList.add(usrId); } } catch (SQLException sqe) { throw new DBException("Get Group GroupeList", sqe); } finally { if (rs != null) { try { rs.close(); } catch (Throwable t) { } } if (stmt != null) { try { stmt.close(); } catch (Throwable t) { } } DBConnection.release(dbc); } /* return list */ return usrList; }
/** * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the * student's directory which relates to the course. Caution: vizualisation file will be deleted * eventhough it also relates to another course if the student is also registered to that course. * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course, * error occured during deletion, or other exception occured. * * @param username student's user name * @param courseID course id (course number + instructor name) * @throws InvalidDBRequestException */ public void deleteStudent(String username, String courseID) throws InvalidDBRequestException { try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; int count = 0; // check if student registered to the course rs = stmt.executeQuery( "select * from courseRoster where course_id = '" + courseID + "' and user_login = '******'"); if (!rs.next()) throw new InvalidDBRequestException("Student is not registered to the course"); // remove student from the course count = stmt.executeUpdate( "delete from courseRoster where course_id = '" + courseID + "' and user_login = '******'"); if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!"); // delete the quiz visualization files rs = stmt.executeQuery( "select distinct unique_id, s.test_name from scores s, courseTest t " + "where s.test_name = t.test_name " + "and course_id = '" + courseID + "' " + "and user_login = '******'"); while (rs.next()) { deleteVisualization(rs.getString(1), username, rs.getString(2)); count = stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim()); } rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addstudent: " + e.getMessage()); throw new InvalidDBRequestException("???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
/** * Query a quiz list from the database. If there is no student user name specified, the list will * contain all quizzes that are used in the instructor's courses. Otherwise, the list will contain * all quizzes that the student has taken and from the instructor's courses which the student is * registered. Throws InvalidDBRequestException if any error occured to the database connection. * * @param instructor instructor's user name * @param student student's user name. Can be empty to get a list of all quizzes in the * instructor's courses. * @return a vector containing the list of quizzes * @throws InvalidDBRequestException */ public Vector getQuizList(String instructor, String student) throws InvalidDBRequestException { Vector list = new Vector(); try { Connection db; Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; if (!student.equals("")) { // get the list that contains all quizzes that the student has taken and from the // instructor's courses which the student is registered rs = stmt.executeQuery( "select courseTest.test_name, scores.start_time from scores, courseTest, course " + "where courseTest.test_name = scores.test_name " + "and courseTest.course_id = course.course_id " + "and instructor = '" + instructor + "' and user_login = '******' " + "order by scores.start_time"); while (rs.next()) { list.add(rs.getString(1) + " <" + rs.getString(2) + ">"); } } else { // get the list that contains all quizzes that are used in the instructor's courses rs = stmt.executeQuery( "select test_name from courseTest, course " + "where courseTest.course_id = course.course_id " + "and instructor = '" + instructor + "' "); while (rs.next()) { list.add(rs.getString(1)); } } rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in getQuizList: " + e.getMessage()); throw new InvalidDBRequestException("???"); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } return list; }
/** * Add a course into the database if not already so. Throws InvalidDBRequestException if course * already in the DB, error occured during insertion, or other exception occured. * * @param courseNum course number * @param courseName name of the course * @param instructor name of the instructor who owns the course * @throws InvalidDBRequestException */ public void addCourse(String courseNum, String courseName, String instructor) throws InvalidDBRequestException { String courseId = courseNum + instructor; try { Class.forName(GaigsServer.DBDRIVER); db = DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD); Statement stmt = db.createStatement(); ResultSet rs; int count = 0; // check if course already in the database rs = stmt.executeQuery("select course_id from course where course_id = '" + courseId + "'"); if (rs.next()) throw new InvalidDBRequestException("Course is already in the database"); // insert course into database count = stmt.executeUpdate( "insert into course values ('" + courseId + "', '" + courseName + "', '" + courseNum + "', '" + instructor + "')"); if (count != 1) throw new InvalidDBRequestException("Something happen during insertion!"); rs.close(); stmt.close(); db.close(); } catch (SQLException e) { System.err.println("Invalid SQL in addCourse: " + e.getMessage()); throw new InvalidDBRequestException("??? "); } catch (ClassNotFoundException e) { System.err.println("Driver Not Loaded"); throw new InvalidDBRequestException("Internal Server Error"); } }
/** * 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 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 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; }