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 name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
/**
* This method queries the database to get the 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;
}
/**
* This method queries the database to validate the passed in FRN for the selected Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public boolean validateFRN(String frn, String year) {
String query;
boolean validfrn = false;
Statement stmt = null;
ResultSet rs = null;
query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;
USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
validfrn = true;
if (rs != null) rs.close();
if (stmt != null) stmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the 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 list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* This method queries the database to check if the passed start date for the Journal Month starts
* exactly after the Previous end date.
*
* @exception SQLException, if query fails
* @author
*/
public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
if (month.length() > ((new Integer("2")).intValue())) {
month = getMonth(month);
}
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";
if ((rgn != null) && !(rgn.equals(""))) {
query = query + " and bp_rgn = ?";
} else {
query = query + " and bp_rgn is null";
}
query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";
query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";
USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
pstmt.setString(2, month);
pstmt.setString(3, blgsys);
int m = 4;
if ((rgn != null) && !(rgn.equals(""))) {
pstmt.setString(m, rgn);
m = m + 1;
}
pstmt.setString(m, month);
pstmt.setString(m + 1, year);
pstmt.setString(m + 2, year);
pstmt.setString(m + 3, strtdat);
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the Journal Dates for the passed in Funding Year
*
* @exception SQLException, if query fails
* @author
*/
public Vector getJrnldts(String year) {
String query;
Vector jrnllst = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query =
"select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
query =
query
+ "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
query =
query
+ "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
query = query + "from blg_prd,blg_sys ";
query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
query = query + "(select strt_dat from fung_yr where yr = ?) and ";
query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
query = query + "order by ord,nm,bp_strt_dat ";
USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
pstmt.setString(2, year);
pstmt.setString(3, year);
rs = pstmt.executeQuery();
while (rs.next()) {
Dinvjrnl jrnldts = new Dinvjrnl(null);
jrnldts.strBpid = rs.getString(1);
jrnldts.strBlgsys = rs.getString(3);
jrnldts.strBlgmnth = rs.getString(5);
jrnldts.strBlgstrtdt = rs.getString(6);
jrnldts.strBlgenddt = rs.getString(7);
jrnllst.addElement(jrnldts);
jrnldts = null;
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
return jrnllst;
}
/**
* 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");
}
}
/* 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;
}
/**
* 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;
}
/**
* 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");
}
}
/**
* 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 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 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;
}
/* 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 get the list of the Future Years.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getFutureyears() {
String query;
Vector years = new Vector();
Statement stmt = null;
ResultSet rs = null;
query = " select yr,yr ||'-'|| (yr+1) from fung_yr ";
query = query + "where yr >= to_number( to_char(sysdate,'YYYY') ) ";
query = query + "and yr not in ";
query = query + "( select yr from fung_yr where ";
query = query + "((sysdate between strt_dat and end_dat) or ";
query = query + "(sysdate-365 between strt_dat and end_dat) or ";
query = query + "(sysdate-730 between strt_dat and end_dat)) ) ";
USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Query :" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Future Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Vector size :" + years.size(), this, null);
return years;
}
/**
* This method queries the database to get the sequence for the BP_ID.
*
* @exception SQLException, if query fails
* @author
*/
public boolean checkDates(String strtdt, String enddt, String year) {
String query;
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select 'true' from fung_yr where to_date(?,'MM/DD/YYYY') < to_date(?,'MM/DD/YYYY') ";
query = query + "and yr = ? and strt_dat-10 <= to_date(?,'MM/DD/YYYY') ";
query = query + "and end_dat >= to_date(?,'MM/DD/YYYY') ";
USFEnv.getLog().writeDebug("Dinvjrnl: Check Dates Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, strtdt);
pstmt.setString(2, enddt);
pstmt.setString(3, year);
pstmt.setString(4, strtdt);
pstmt.setString(5, enddt);
rs = pstmt.executeQuery();
if (rs.next()) {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
return true;
}
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Date Comparison Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return false;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBlgsys() {
String query;
Vector blgsys = new Vector();
Statement stmt = null;
ResultSet rs = null;
query =
"select distinct decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord,";
query =
query
+ " rtrim(bs_nm||' '||bp_rgn),bs_id_fk||rtrim(bp_rgn) from blg_sys,blg_prd where bs_id=bs_id_fk order by ord";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Query :" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
blgsys.addElement(rs.getString(3));
blgsys.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Billing System Vector size :" + blgsys.size(), this, null);
}
return blgsys;
}
/**
* 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");
}
}
/**
* 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());
}
}
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
/**
* ** Returns true if the specified key attribute exists in the table ** @param altIndexName The
* alternate index name, or null to use the primary index ** @param whereKeyType The partial key
* match type ** @return True if the specified key attribute exists in the table, false otherwise
*/
protected boolean _exists(String altIndexName, int whereKeyType)
throws SQLException, DBException {
/* key fields */
boolean usePrimaryKey = StringTools.isBlank(altIndexName);
DBField kfld[] = usePrimaryKey ? this.getKeyFields() : this.getAltKeyFields(altIndexName);
if (ListTools.isEmpty(kfld)) {
throw new DBException("No keys found!");
}
/* check last key for "auto_increment" */
if (whereKeyType == DBWhere.KEY_FULL) {
DBField lastField = kfld[kfld.length - 1];
if (lastField.isAutoIncrement()
&& !this.getFieldValues().hasFieldValue(lastField.getName())) {
// full key requested and last key is auto_increment, which is missing
return false;
}
}
// DBSelect: SELECT <Keys> FROM <TableName> <KeyWhere>
String firstKey = kfld[0].getName();
DBSelect<gDBR> dsel = new DBSelect<gDBR>(this.getFactory());
dsel.setSelectedFields(firstKey);
dsel.setWhere(this._getWhereClause(altIndexName, whereKeyType));
/* get keyed record */
DBConnection dbc = null;
Statement stmt = null;
ResultSet rs = null;
boolean exists = false;
try {
dbc = DBConnection.getDefaultConnection();
stmt = dbc.execute(dsel.toString()); // may throw DBException
rs = stmt.getResultSet();
exists = rs.next();
} catch (SQLException sqe) {
if (sqe.getErrorCode() == DBFactory.SQLERR_TABLE_NOTLOCKED) {
// MySQL: This case has been seen on rare occasions. Not sure what causes it.
Print.logError("SQL Lock Error: " + sqe);
Print.logError("Hackery! Forcing lock on table: " + this.getTableName());
if (DBProvider.lockTableForRead(this.getTableName(), true)) { // may throw DBException
stmt = dbc.execute(dsel.toString()); // may throw SQLException, DBException
rs = stmt.getResultSet(); // SQLException
exists = rs.next(); // SQLException
DBProvider.unlockTables(); // DBException
}
} else {
throw sqe;
}
} finally {
if (rs != null) {
try {
rs.close();
} catch (Throwable t) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Throwable t) {
}
}
DBConnection.release(dbc);
}
return exists;
}
