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;
}
/**
* 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;
}
/**
* Transforma os dados obtidos atraves de uma consulta a tabela de glosas do prestador em um
* objeto do tipo GlosaPrestador
*
* @param rset - um ResultSet contendo o resultado da consulta a tabela de glosas do prestador
* @return um objeto do tipo GlosaPrestador
*/
private static final GlosaPrestador montaGlosaPrestador(ResultSet rset) throws SQLException {
String numAtendimento = null;
String numDocOrigem = null;
String strLoteNota = "";
String strCodBeneficiario = "";
String strNomeBeneficiario = "";
Timestamp dtGlosa = null;
String strMotivo = "";
String strDescricao = "";
String strObservacoes = "";
numAtendimento = rset.getString("rej_ate_num_atendimento");
dtGlosa = rset.getTimestamp("rej_data_hora");
numDocOrigem = rset.getString("nat_numero_documento_origem");
strLoteNota = rset.getString("Lote_Nota");
strCodBeneficiario = rset.getString("Beneficiario");
strNomeBeneficiario = rset.getString("Nome");
strDescricao = rset.getString("Descricao");
strMotivo = (rset.getString("rej_motivo") == null) ? "" : rset.getString("rej_motivo");
strObservacoes =
(rset.getString("ate_observacoes") == null) ? "" : rset.getString("ate_observacoes");
return (new GlosaPrestador(
numAtendimento,
numDocOrigem,
strLoteNota,
strCodBeneficiario,
strNomeBeneficiario,
dtGlosa,
strMotivo,
strDescricao,
strObservacoes));
} // montaGlosaPrestador()
/**
* 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");
}
}
/**
* 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, "@");
}
/**
* 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;
}
/**
* 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;
}
/**
* 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 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;
}
/**
* 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 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;
}
// 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.
}
}
}
/**
* 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");
}
}
/**
* 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");
}
}
/**
* 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 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;
}
/**
* Private method to delete a visualization script file of a quiz taken by a student.
*
* @param uniqueId a unique number of that indicates the instance of the quiz taking in the
* database
* @param studentName student's user name
* @param testName quiz name
*/
private void deleteVisualization(String uniqueId, String studentName, String testName) {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
// get quiz visulization type
ResultSet rs =
stmt.executeQuery("select visual_type from test where name = '" + testName + "'");
// construct the quiz vizualisation file name
String visFileName =
new String("./StudentQuizzes/" + studentName + "/" + testName + uniqueId);
if (rs.next())
if (rs.getString(1).trim().equalsIgnoreCase("gaigs")) visFileName = visFileName + ".sho";
else if (rs.getString(1).trim().equalsIgnoreCase("samba"))
visFileName = visFileName + ".sam";
else if (rs.getString(1).trim().equalsIgnoreCase("animal"))
visFileName = visFileName + ".ani";
else {
System.err.println("visualization type unknown for quiz: " + rs.getString(3) + "\n");
}
else {
System.err.println("Visulization type not found for quiz: " + rs.getString(3) + "\n");
}
// delete the file
File visFile = new File(visFileName);
if (debug) System.out.println("deleting " + visFileName);
if (visFile.exists())
if (!visFile.delete()) System.err.println("can't delete the visualization file");
rs.close();
stmt.close();
db.close();
} catch (Exception e) {
System.err.println("Execption thrown from deleteVisualisation: " + e.getMessage());
}
}
/* 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;
}
/**
* This method queries the database to validate the passed in FRN for the selected Funding Year
* has been invoiced or not
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkFRNinv(String frn, String year) {
String query;
boolean validfrn = false;
Statement stmt = null;
ResultSet rs = null;
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + frn, this, null);
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + year, this, null);
query = " SELECT DISTINCT rhcc_inv_id_fk ";
query = query + " FROM wrk_ordr, wrk_ordr_dets, wo_det_hsties ";
query = query + " WHERE wo_id = wo_id_fk ";
query = query + " AND wod_id = wod_id_fk ";
query = query + " AND wdh_stat = 'P' ";
query = query + " AND inv_stat = 'I' ";
query = query + " AND wrk_ordr_no ='" + frn + "' ";
query = query + " AND wrk_ordr.yr_fk =" + year;
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
USFEnv.getLog().writeCrit("RhccDinvview: FRN Invoiced" + rs.getFetchSize(), this, null);
if (rs.next()) {
USFEnv.getLog()
.writeCrit("RhccDinvview: FRN Invoiced" + rs.getString("rhcc_inv_id_fk"), this, null);
validfrn = true;
if (rs != null) rs.close();
if (stmt != null) stmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: FRN not Invoiced", 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 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;
}
protected SDMSObject rowToObject(SystemEnvironment env, ResultSet r) throws SDMSException {
Long id;
String name;
Long deleteVersion;
Long creatorUId;
Long createTs;
Long changerUId;
Long changeTs;
long validFrom;
long validTo;
try {
id = new Long(r.getLong(1));
name = r.getString(2);
deleteVersion = new Long(r.getLong(3));
creatorUId = new Long(r.getLong(4));
createTs = new Long(r.getLong(5));
changerUId = new Long(r.getLong(6));
changeTs = new Long(r.getLong(7));
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",
"Group: $1 $2",
new Integer(sqle.getErrorCode()),
sqle.getMessage()));
}
if (validTo < env.lowestActiveVersion) return null;
return new SDMSGroupGeneric(
id, name, deleteVersion, creatorUId, createTs, changerUId, changeTs, validFrom, validTo);
}
/**
* Changes the password of a user. Throws InvalidNameException if user's not registered in the
* database. Throws InvalidPassException if user's password was invalid. Throws
* InvalidDBRequestException if error occured during insertion or other database connection
* problems.
*
* @param name user's user name
* @param pass user's password
* @param newPAss user's new password
* @throws InvalidNameException, InvalidPassException, InvalidDBRequestException
*/
public void changePassword(String name, String pass, String newPass)
throws InvalidNameException, InvalidPassException, InvalidDBRequestException {
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
int count;
Statement stmt = db.createStatement();
ResultSet rs;
rs = stmt.executeQuery("select password from student where login = '******'");
// check if it's not a student in database
if (!rs.next()) {
rs = stmt.executeQuery("select password from instructor where login = '******'");
// check if it's an instructor in database
if (!rs.next()) {
if (debug) System.out.println("User not found in the user table");
throw new InvalidNameException("User not registered");
}
// check for password
if (!rs.getString(1).equals(pass)) {
if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"update instructor set password = '******' where login = '******' and password ='******'");
} else {
// check for password
if (!rs.getString(1).equals(pass)) {
if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"update student set password = '******' where login = '******' and password ='******'");
}
// check if successful
if (count != 1) {
System.err.println("Error in updating password");
throw new InvalidDBRequestException("Error in updating password");
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) // catch sql exception
{
System.err.println("Invalid SQL in ChangePassword: "******"Server request error: " + e.getMessage());
} catch (ClassNotFoundException e) // catch driver not found exception
{
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Server Error");
}
}
/**
* Query a student list from the database. If quiz name is not specified, the list will contain
* all students who are registered in the instructor's courses. Otherwise, it will contain all
* students who has taken the quiz and are registered to the instructor's course(s) that use the
* quiz Throws InvalidDBRequestException if any error occured to the database connection.
*
* @param name instructor's user name
* @param quiz quiz name. Can be empty String to get the list of all students who are registered
* in the instructor's courses.
* @return a vector that contains the list of students
* @throws InvalidDBRequestException
*/
public Vector getStudentList(String name, String quiz) 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 (!quiz.equals("")) {
// get the list that contains all students who has taken the quiz and are registered to the
// instructor's course(s) that use the quiz
rs =
stmt.executeQuery(
"select login, last_name, first_name, scores.start_time from student, scores, courseRoster, course "
+ "where student.login = scores.user_login "
+ "and scores.user_login = courseRoster.user_login "
+ "and courseRoster.course_id = course.course_id "
+ "and scores.test_name = '"
+ quiz
+ "' "
+ "and instructor = '"
+ name
+ "' "
+ "order by last_name, first_name, login, scores.start_time");
while (rs.next()) {
list.add(
rs.getString(2)
+ ", "
+ rs.getString(3)
+ " ("
+ rs.getString(1)
+ ") <"
+ rs.getString(4)
+ ">");
}
} else {
// get the list that contains all students who are registered in the instructor's courses
rs =
stmt.executeQuery(
"select distinct login, last_name, first_name from student, courseRoster, course "
+ "where student.login = courseRoster.user_login "
+ "and courseRoster.course_id = course.course_id "
+ "and instructor = '"
+ name
+ "' "
+ "order by last_name, first_name, login");
while (rs.next()) {
list.add(rs.getString(2) + ", " + rs.getString(3) + " (" + rs.getString(1) + ")");
}
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in getStudentList: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
return list;
}
public void start() {
Connection connection = null; // manages connection
Connection connection2 = null;
Statement statement = null; // query statement
Statement statement2 = null;
wm = new double[20][Data.windows_size];
String QueryKinasesName = "%" + Data.kinease + "%";
// data.code = data.codenames[3];
String QueryCodeName = Data.code;
int windows_size = Data.windows_size;
// int windows_size = 9;
int shift = windows_size / 2;
try {
Class.forName(JDBC_DRIVER); // load database driver class
for (windows_size = Data.windows_size; windows_size <= Data.windows_size; windows_size += 2) {
shift = windows_size / 2;
// establish connection to database
connection = DriverManager.getConnection(DATABASE_URL, "", "");
connection2 = DriverManager.getConnection(DATABASE_URL, "", "");
// create Statement for querying database
statement = connection.createStatement();
statement2 = connection2.createStatement();
String ACC = null;
String SEQUENCE = null;
String KINASES = null;
String LIKE = "LIKE";
// int POSITION = 0;
// int index = 0;
String temp = null;
// int numtemp = 0;
// int LENGTH = (int) 0;
// int count = 0;
double[] totalAAcount = new double[windows_size];
double weightmatrix[][] = new double[windows_size][128]; // windowssize;aa;
String statementquery1 =
"SELECT Mid(sequence,(position-"
+ shift
+ "),"
+ windows_size
+ ") AS TARGET, index,code,length,position,sequence FROM Dataset_041106 WHERE ((position-"
+ shift
+ ")>1) AND ((position +"
+ shift
+ ")<length) AND kinases "
+ LIKE
+ " '"
+ QueryKinasesName
+ "' AND (code LIKE '"
+ QueryCodeName
+ "')";
System.out.println("#" + statementquery1);
/// fout.println("#"+statementquery1);
ResultSet resultSet1 = statement.executeQuery(statementquery1);
int seqsize = 0;
while ((resultSet1.next())) {
String posseq = resultSet1.getString("TARGET");
seqsize = posseq.length();
if (posseq.charAt(0) != 'X' && posseq.charAt(seqsize - 1) != 'X') { // �h����t
for (int i = 0; i < seqsize; i++) {
weightmatrix[i][posseq.charAt(i)]++;
}
// possequence.addElement(posseq);
}
} // end while
char[] aaMap = {
'A', 'R', 'N', 'D', 'C', 'Q', 'E', 'G', 'H', 'I', 'L', 'K', 'M', 'F', 'P', 'S', 'T', 'W',
'Y', 'V', 'X'
};
double[] expmatrix = {
0.0701313443873091,
0.0582393695201718,
0.0359362961736045,
0.0520743144385134,
0.0172010453343506,
0.0498574962335004,
0.0796465136978452,
0.0624720283551962,
0.0241405228512130,
0.0416989778376737,
0.0934441156861220,
0.0632334844952389,
0.0213293464067050,
0.0324554733241482,
0.0651181982370858,
0.0881672518230193,
0.0524630941595624,
0.0101093184162382,
0.0244701177088640,
0.0578116909136386
};
// double[] aaMapfreq = new double[windows_size];
double freq = 0;
for (int j = 0; j < weightmatrix.length; j++) {
for (int i = 0; i < aaMap.length - 1; i++) {
totalAAcount[j] += weightmatrix[j][aaMap[i]];
}
}
for (int i = 0; i < aaMap.length - 1; i++) {
// profilefout.print(aaMap[i]);
for (int j = 0; j < windows_size; j++) {
freq = ((weightmatrix[j][aaMap[i]]) / (totalAAcount[j])) + 1;
wm[i][j] = Math.log10((freq / expmatrix[i])) / Math.log10(2.0);
// profilefout.print(","+aaMapfreq[i]);
}
// profilefout.println();
}
// fout.close();
// profilefout.close();
resultSet1.close();
connection.close();
}
} // end try
catch (ClassNotFoundException classNotFound) {
classNotFound.printStackTrace();
System.exit(1);
} catch (NullPointerException nullpointerException) {
nullpointerException.printStackTrace();
System.exit(1);
} catch (SQLException ex) {
/** @todo Handle this exception */
/* } catch (IOException ex) {
*/
/** @todo Handle this exception */
} catch (NoClassDefFoundError ex) {
} finally { // ensure statement and connection are closed properly
try {
statement.close();
statement2.close();
connection.close();
connection2.close();
} catch (Exception exception) { // end try
exception.printStackTrace();
System.exit(1);
} // end catch
} // end finally
} // end main
/**
* Add a student to a course if not already so. If student not registered in the student table
* yet, the user are required to enter the student information so that the student can be added.
* Throws InvalidDBRequestException if user name belongs to an instructor, student already
* registered in the course, student information differs than the one in the database, student
* information not available when needed, error occured during insertion, or if other exception
* occured Throws FileFailureException if fail to create folder for the student.
*
* @param username student's user name
* @param courseID course id (course number + instructor name)
* @param lastname student's last name
* @param firstname student's first name
* @param initPass initial password for the student
* @throws InvalidDBRequestException
*/
public void addStudent(
String courseID, String username, String lastName, String firstName, String initPass)
throws InvalidDBRequestException, FileFailureException {
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 user name belongs to an instructor
rs = stmt.executeQuery("select login from instructor where login = '******'");
if (rs.next()) throw new InvalidDBRequestException("User name is reserved by an instructor");
// check if student in the database or not
rs =
stmt.executeQuery(
"select last_name, first_name from student where login = '******'");
if (rs.next()) {
// check if user name belongs to another student
if (!lastName.equals("")
&& (!lastName.equalsIgnoreCase(rs.getString(1).trim())
|| !firstName.equalsIgnoreCase(rs.getString(2).trim())))
throw new InvalidDBRequestException(
"User name is used by: " + rs.getString(2).trim() + " " + rs.getString(1).trim());
} else {
// check if student's information is given
if (lastName.equals(""))
throw new InvalidDBRequestException(
"Student is not registered in the database. Please enter the student's name");
// insert student into hte database
count =
stmt.executeUpdate(
"insert into student values ('"
+ username
+ "', '"
+ lastName
+ "', '"
+ firstName
+ "', '"
+ initPass
+ "', now())");
if (count != 1) throw new InvalidDBRequestException("Error occured during insertion!");
// create student's folder
File newStudentDir = new File("./StudentQuizzes/" + username);
if (!(newStudentDir.mkdir())) {
System.err.println("Error in creating new folder for student: " + username);
throw new FileFailureException("huh");
}
}
// check if student is registered in the course
rs =
stmt.executeQuery(
"select * from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (rs.next())
throw new InvalidDBRequestException("Student is already registered to the course");
// register student to the course
count =
stmt.executeUpdate(
"insert into courseRoster values ('" + courseID + "', '" + username + "')");
if (count != 1) throw new InvalidDBRequestException("Error occured during insertion!");
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");
}
}
/**
* ** Constructor ** @param rs SQL ResultSet ** @param fldName Field name in ResultSet containing
* the field template
*/
public DTTemplate(ResultSet rs, String fldName) throws SQLException {
super(rs, fldName);
// set to default value if 'rs' is null
this.templateProps = new RTProperties((rs != null) ? rs.getString(fldName) : "");
this.fieldCount = -1;
}
/**
* Deletes a course from the database. Also deletes all the quiz visualization files from the
* students' folder who is registered in the course for all quizzes related to the course.
* Caution: vizualisation file will be deleted eventhough it also relates to anther course if the
* student is also registered to that course. (FIX ME!) Throws InvalidDBRequestException if the
* course is not in the database, error occured during deletion, or other exception occured.
*
* @param courseNum course number
* @param instructor instructor's user name who owns the course
* @throws InvalidDBRequestException
*/
public void deleteCourse(String courseNum, String instructor) throws InvalidDBRequestException {
String courseId = new String(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 in the database
rs = stmt.executeQuery("select course_id from course where course_id = '" + courseId + "'");
if (!rs.next()) throw new InvalidDBRequestException("Course is not in the database");
else {
// delete course from course table
count = stmt.executeUpdate("delete from course where course_id = '" + courseId + "'");
if (count != 1) throw new InvalidDBRequestException("Something happen during deletion!");
else {
// delete quiz visualization files
rs =
stmt.executeQuery(
"select distinct unique_id, s.user_login, s.test_name from scores s, courseRoster r, courseTest t "
+ "where s.test_name = t.test_name "
+ "and r.user_login = s.user_login "
+ "and r.course_id = t.course_id "
+ "and t.course_id = '"
+ courseId
+ "'");
while (rs.next()) {
deleteVisualization(
rs.getString(1).trim(), rs.getString(2).trim(), rs.getString(3).trim());
count =
stmt.executeUpdate(
"delete from scores where unique_id = " + rs.getString(1).trim());
}
// delete course from other tables
count =
stmt.executeUpdate("delete from courseRoster where course_id = '" + courseId + "'");
count = stmt.executeUpdate("delete from courseTest where course_id = '" + courseId + "'");
// delete menu file
File menuFile = new File("./html_root/cat/" + courseId + ".list");
if (menuFile.exists())
if (!menuFile.delete()) System.err.println("Failed to delete the menu");
}
}
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");
}
}