// Takes and image_id, pulls in the image from cache, and goes about
// encoding it to put it into the database in a transaction. The last
// query in the transaction records the image having been stored.
protected void storeImage(int image_id) throws Exception {
PhotoImage p = new PhotoImage(image_id);
SpyDB pdb = getDB();
Connection db = null;
Statement st = null;
Vector v = p.getImage();
System.err.println(
"Storer: Got image for " + image_id + " " + v.size() + " lines of data to store.");
try {
int i = 0, n = 0;
db = pdb.getConn();
db.setAutoCommit(false);
st = db.createStatement();
BASE64Encoder base64 = new BASE64Encoder();
String data = "";
for (; i < v.size(); i++) {
String tmp = base64.encodeBuffer((byte[]) v.elementAt(i));
tmp = tmp.trim();
if (data.length() < 2048) {
data += tmp + "\n";
} else {
storeQuery(image_id, n, st, data);
data = tmp;
n++;
}
}
// OK, this is sick, but another one right now for the spare.
if (data.length() > 0) {
System.err.println("Storer: Storing spare.");
storeQuery(image_id, n, st, data);
n++;
}
System.err.println("Storer: Stored " + n + " lines of data for " + image_id + ".");
st.executeUpdate(
"update upload_log set stored=datetime(now())\n" + "\twhere photo_id = " + image_id);
db.commit();
// Go ahead and generate a thumbnail.
p.getThumbnail();
} catch (Exception e) {
// If anything happens, roll it back.
if (st != null) {
try {
db.rollback();
} catch (Exception e3) {
// Nothing
}
}
} finally {
if (db != null) {
try {
db.setAutoCommit(true);
} catch (Exception e) {
System.err.println("Error: " + e);
}
}
pdb.freeDBConn();
}
}
/**
* 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;
}
/**
* 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 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.
}
}
}
/**
* 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");
}
}
/**
* 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 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 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;
}
/**
* Delete a student from the database. Also deletes the student's folders. Throws
* InvalidDBRequestException if any error in database connection.
*
* @param username student's user name
* @throws InvalidDBRequestException
*/
private void purgeStudent(String username) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
int count;
// delete from scores
count = stmt.executeUpdate("delete from scores where user_login = '******'");
// delete from student
count = stmt.executeUpdate("delete from student where login = '******'");
// delete student's folder
File studentDir = new File("./StudentQuizzes/" + username);
if (!(studentDir.delete())) {
System.err.println("Error in deleting folder for student: " + username);
}
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");
}
}
/**
* 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;
}
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;
}
// Query to store an image
protected void storeQuery(int image_id, int line, Statement st, String data) throws Exception {
String query = "insert into image_store values(" + image_id + ", " + line + ", '" + data + "')";
// Print out the query for debug.
// System.err.println(query);
st.executeUpdate(query);
}
/**
* 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 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;
}
/**
* 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");
}
}
/**
* 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());
}
}
/**
* This method queries the database to get the list of the Future Years.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getFutureyears() {
String query;
Vector years = new Vector();
Statement stmt = null;
ResultSet rs = null;
query = " select yr,yr ||'-'|| (yr+1) from fung_yr ";
query = query + "where yr >= to_number( to_char(sysdate,'YYYY') ) ";
query = query + "and yr not in ";
query = query + "( select yr from fung_yr where ";
query = query + "((sysdate between strt_dat and end_dat) or ";
query = query + "(sysdate-365 between strt_dat and end_dat) or ";
query = query + "(sysdate-730 between strt_dat and end_dat)) ) ";
USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Query :" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Future Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
USFEnv.getLog().writeDebug("Dinvjrnl: Future Years Vector size :" + years.size(), this, null);
return years;
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBlgsys() {
String query;
Vector blgsys = new Vector();
Statement stmt = null;
ResultSet rs = null;
query =
"select distinct decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord,";
query =
query
+ " rtrim(bs_nm||' '||bp_rgn),bs_id_fk||rtrim(bp_rgn) from blg_sys,blg_prd where bs_id=bs_id_fk order by ord";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Query :" + query, this, null);
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
while (rs.next()) {
blgsys.addElement(rs.getString(3));
blgsys.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
USFEnv.getLog()
.writeDebug("Dinvjrnl: Billing System Vector size :" + blgsys.size(), this, null);
}
return blgsys;
}
public void 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");
}
}
/**
* 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;
}
/**
* ** 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;
}