/**
* Returns a list of information related to prescription reports given all of the office visits
* and the patient ID. The patient ID is necessary in case the office visit IDs are for different
* patients (the disambiguation is for security reasons).
*
* @param ovIDs A java.util.List of Longs for the office visits.
* @param patientID A long representing the MID of the patient in question.
* @return A java.util.List of prescription reports.
* @throws DBException
*/
public List<PrescriptionReportBean> getPrescriptionReportsByDate(
long patientID, String startDate, String endDate) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"SELECT * FROM NDCodes, OVMedication, OfficeVisits "
+ "WHERE NDCodes.Code=OVMedication.NDCode AND OVMedication.VisitID=OfficeVisits.ID "
+ "AND PatientID=? AND ((DATE(?) < OVMedication.EndDate AND DATE(?) > OVMedication.StartDate)"
+ "OR (DATE(?) > OVMedication.StartDate AND DATE(?) < OVMedication.EndDate ) OR "
+ "(DATE(?) <= OVMedication.StartDate AND DATE(?) >= OVMedication.StartDate)) "
+ "ORDER BY VisitDate DESC");
ps.setLong(1, patientID);
ps.setString(2, startDate);
ps.setString(3, startDate);
ps.setString(4, endDate);
ps.setString(5, endDate);
ps.setString(6, startDate);
ps.setString(7, endDate);
ResultSet rs = ps.executeQuery();
return prescriptionReportBeanLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Tries to add a condition to the table of existing conditions
*
* @param mid The patient ID to add the condition for
* @param condition The string of the condition to add
* @return A message of whether it was successfully added or not
*/
public String putConditionByMID(long mid, String condition) {
if (condition == null || condition.length() < 1)
return "Cannot add empty string as a pre-existing condition";
if (condition.length() > CONDITION_MAX_STR_LEN)
return "Maximum length of " + CONDITION_MAX_STR_LEN + " exceeded";
if (!validateStr(condition))
return "Illegal condition. Only alphanumerics - , and _ are allowed.";
try {
Connection conn = factory.getConnection();
Statement stmt = conn.createStatement();
String request =
"INSERT INTO `obstetricsconditions` (`mid`, `condition`) VALUES ('"
+ mid
+ "', '"
+ condition
+ "');";
stmt.execute(request);
conn.close();
stmt.close();
return "Pre-existing condition added OK";
} catch (SQLException e) {
return "Database error while inserting";
}
}
/**
* Returns a list of information related to prescription reports given all of the office visits
* and the patient ID. The patient ID is necessary in case the office visit IDs are for different
* patients (the disambiguation is for security reasons).
*
* @param ovIDs A java.util.List of Longs for the office visits.
* @param patientID A long representing the MID of the patient in question.
* @return A java.util.List of prescription reports.
* @throws DBException
*/
public List<PrescriptionReportBean> getPrescriptionReports(List<Long> ovIDs, long patientID)
throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
String preps = buildPreps(ovIDs.size());
ps =
conn.prepareStatement(
"SELECT * FROM NDCodes, OVMedication, OfficeVisits "
+ "WHERE NDCodes.Code=OVMedication.NDCode AND OVMedication.VisitID=OfficeVisits.ID "
+ "AND PatientID=? AND VisitID IN("
+ preps
+ ") ORDER BY VisitDate DESC");
ps.setLong(1, patientID);
prepareOVIDs(ps, ovIDs);
ResultSet rs = ps.executeQuery();
return prescriptionReportBeanLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Inserts a lab procedure into the database.
*
* @param b The LabProcedureBean to be inserted.
* @return A long containing the ID of the newly inserted lab procedure bean.
* @throws DBException
*/
public long addLabProcedure(LabProcedureBean b) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
if (b.getPid() == 0L) throw new SQLException("PatientMID cannot be null");
conn = factory.getConnection();
ps =
conn.prepareStatement(
"INSERT INTO LabProcedure (PatientMID, LaboratoryProcedureCode, Status, Commentary, Results, OfficeVisitID, Rights) VALUES (?,?,?,?,?,?,?)");
ps.setLong(1, b.getPid());
ps.setString(2, b.getLoinc());
ps.setString(3, b.getStatus());
ps.setString(4, b.getCommentary());
ps.setString(5, b.getResults());
ps.setLong(6, b.getOvID());
ps.setString(7, b.getRights());
ps.executeUpdate();
return DBUtil.getLastInsert(conn);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Adds a LOINCCode to an office visit.
*
* @param LOINCCode A string of the code being added.
* @param visitID The ID of the office visit the code is being added to.
* @param pid The patient's MID associated with this transaction.
* @return The unique ID of the code that was added.
* @throws DBException
*/
public long addLabProcedureToOfficeVisit(String LOINCCode, long visitID, long pid)
throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"INSERT INTO LabProcedure (LaboratoryProcedureCode,OfficeVisitID,"
+ "Commentary, Results, PatientMID, Status, Rights) VALUES (?,?,?,?,?,?,?)");
ps.setString(1, LOINCCode);
ps.setLong(2, visitID);
ps.setString(3, "");
ps.setString(4, "");
ps.setLong(5, pid);
ps.setString(6, LabProcedureBean.Not_Received);
ps.setString(7, "ALLOWED");
ps.executeUpdate();
return DBUtil.getLastInsert(conn);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns a list of information related to prescription reports given all of the office visits
* and the patient ID. The patient ID is necessary in case the office visit IDs are for different
* patients (the disambiguation is for security reasons).
*
* @param ovIDs A java.util.List of Longs for the office visits.
* @param patientID A long representing the MID of the patient in question.
* @return A java.util.List of prescription reports.
* @throws DBException
*/
public List<PrescriptionReportBean> byDate(long patientID, String startDate, String endDate)
throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"SELECT * FROM ndcodes, ovmedication, officevisits "
+ "WHERE ndcodes.Code=ovmedication.NDCode AND ovmedication.VisitID=officevisits.ID "
+ "AND PatientID=? AND ((DATE(?) < ovmedication.EndDate AND DATE(?) > ovmedication.StartDate)"
+ "OR (DATE(?) > ovmedication.StartDate AND DATE(?) < ovmedication.EndDate ) OR "
+ "(DATE(?) <= ovmedication.StartDate AND DATE(?) >= ovmedication.StartDate)) "
+ "ORDER BY VisitDate DESC");
ps.setLong(1, patientID);
ps.setString(2, startDate);
ps.setString(3, startDate);
ps.setString(4, endDate);
ps.setString(5, endDate);
ps.setString(6, startDate);
ps.setString(7, endDate);
ResultSet rs = ps.executeQuery();
List<PrescriptionReportBean> loadlist = loader.loadList(rs);
rs.close();
return loadlist;
} catch (SQLException e) {
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns a list of information related to prescription reports given all of the office visits
* and the patient ID. The patient ID is necessary in case the office visit IDs are for different
* patients (the disambiguation is for security reasons).
*
* @param ovIDs A java.util.List of Longs for the office visits.
* @param patientID A long representing the MID of the patient in question.
* @return A java.util.List of prescription reports.
* @throws DBException
*/
public List<PrescriptionReportBean> byOfficeVisitAndPatient(List<Long> ovIDs, long patientID)
throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
String preps = buildPreps(ovIDs.size());
ps =
conn.prepareStatement(
"SELECT * FROM ndcodes, ovmedication, officevisits "
+ "WHERE ndcodes.Code=ovmedication.NDCode AND ovmedication.VisitID=officevisits.ID "
+ "AND PatientID=? AND VisitID IN("
+ preps
+ ") ORDER BY VisitDate DESC");
ps.setLong(1, patientID);
prepareOVIDs(ps, ovIDs);
ResultSet rs = ps.executeQuery();
List<PrescriptionReportBean> loadlist = loader.loadList(rs);
rs.close();
return loadlist;
} catch (SQLException e) {
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Insert survey data into database.
*
* @param surveyBean The Bean representing the user's responses which will be inserted.
* @param date The date the survey was completed.
*/
public void addCompletedSurvey(SurveyBean surveyBean, Date date) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
if (surveyBean == null) throw new SQLException("Null survey exception.");
conn = factory.getConnection();
ps = conn.prepareStatement("INSERT INTO ovsurvey (VisitID, SurveyDate) VALUES (?,?)");
ps.setLong(1, surveyBean.getVisitID());
ps.setTimestamp(2, new java.sql.Timestamp(date.getTime()));
ps.executeUpdate();
ps.close();
if (surveyBean.getWaitingRoomMinutes() > 0) {
ps = conn.prepareStatement("update ovsurvey set WaitingRoomMinutes = ? where VisitID = ?");
ps.setFloat(1, surveyBean.getWaitingRoomMinutes());
ps.setLong(2, surveyBean.getVisitID());
ps.executeUpdate();
ps.close();
}
if (surveyBean.getExamRoomMinutes() > 0) {
ps = conn.prepareStatement("update ovsurvey set ExamRoomMinutes = ? where VisitID = ?");
ps.setFloat(1, surveyBean.getExamRoomMinutes());
ps.setLong(2, surveyBean.getVisitID());
ps.executeUpdate();
ps.close();
}
if (surveyBean.getVisitSatisfaction() > 0) {
ps = conn.prepareStatement("update ovsurvey set VisitSatisfaction = ? where VisitID = ?");
ps.setFloat(1, surveyBean.getVisitSatisfaction());
ps.setLong(2, surveyBean.getVisitID());
ps.executeUpdate();
ps.close();
}
if (surveyBean.getTreatmentSatisfaction() > 0) {
ps =
conn.prepareStatement(
"update ovsurvey set TreatmentSatisfaction = ? where VisitID = ?");
ps.setFloat(1, surveyBean.getTreatmentSatisfaction());
ps.setLong(2, surveyBean.getVisitID());
ps.executeUpdate();
ps.close();
}
} catch (SQLException e) {
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Removes a particular diagnosis from its office visit
*
* @param ovDiagnosisID The ID for the office visit diagnosis to be removed.
* @throws DBException
*/
public void removeDiagnosisFromOfficeVisit(long ovDiagnosisID) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("DELETE FROM OVDiagnosis WHERE ID=? ");
ps.setLong(1, ovDiagnosisID);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Removes a given lab procedure.
*
* @param labProcedureID The unique ID of the lab procedure.
* @throws DBException
*/
public void removeLabProcedureFromOfficeVisit(long labProcedureID) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("DELETE FROM LabProcedure WHERE LaboratoryProcedureID=?");
ps.setLong(1, labProcedureID);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Gets all lab procedures, period.
*
* @return A java.util.List of LabProcedureBeans.
* @throws DBException
*/
public List<LabProcedureBean> getAllLabProcedures() throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("SELECT * FROM LabProcedure ORDER BY UpdatedDate ASC");
ResultSet rs = ps.executeQuery();
return labProcedureLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
public void testGetFluShotDelinquentsEmptyList() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
gen.patient1();
gen.patient2();
gen.patient3();
boolean thisYear = DateUtil.currentlyInMonthRange(9, 12);
java.sql.Date september = new java.sql.Date(0l), december = new java.sql.Date(0l);
DateUtil.setSQLMonthRange(september, 8, thisYear ? 0 : 1, december, 11, thisYear ? 0 : 1);
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"SELECT DISTINCT "
+ "? as hid, ov.patientid, p.lastname, p.firstname, "
+ "p.phone "
+ "FROM officevisits ov, patients p "
+ "WHERE ov.patientid=p.mid "
+ "AND p.dateofdeath IS NULL "
+ "AND p.dateofbirth < DATE_SUB(CURDATE(), INTERVAL 50 YEAR) "
+ "AND patientid NOT IN "
+ "(SELECT patientid FROM officevisits ov, ovmedication om "
+ "WHERE ov.id=om.visitid "
+ "AND NDCode IN (90656, 90658, 90660) "
+ "AND ((StartDate BETWEEN ? AND ?) "
+ "OR (EndDate BETWEEN ? AND ?))) "
+ "ORDER BY lastname, firstname, ov.patientid");
ps.setLong(1, this.LHCP);
ps.setDate(2, september);
ps.setDate(3, december);
ps.setDate(4, september);
ps.setDate(5, december);
rs = ps.executeQuery();
VisitReminderReturnFormLoader loader = new VisitReminderReturnFormLoader();
List<VisitReminderReturnForm> patients = loader.loadList(rs);
assertEquals(2, patients.size());
VisitReminderReturnForm patient1 = patients.get(0);
assertEquals(9000000003l, patient1.getHcpID());
assertEquals(3l, patient1.getPatientID());
assertEquals("Care", patient1.getFirstName());
assertEquals("919-971-0000", patient1.getPhoneNumber());
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns whether or not an office visit actually exists
*
* @param ovID The ID of the office visit to be checked.
* @param pid The MID of the patient associated with this transaction.
* @return A boolean indicating its existence.
* @throws DBException
*/
public boolean checkOfficeVisitExists(long ovID, long pid) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("SELECT * FROM OfficeVisits WHERE ID=? AND PatientID=?");
ps.setLong(1, ovID);
ps.setLong(2, pid);
ResultSet rs = ps.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Gets all office visits corresponding to a particular ICD diagnosis.
*
* @param icdcode A string represnting the ICD diagnosis to look up.
* @return A java.util.List of Office visits.
* @throws DBException
*/
public List<OfficeVisitBean> getAllOfficeVisitsForDiagnosis(String icdcode) throws DBException {
List<DiagnosisBean> diags = null;
List<OfficeVisitBean> ovs = new ArrayList<OfficeVisitBean>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
if (icdcode == null) throw new SQLException("icdcode cannot be null");
conn = factory.getConnection();
ps =
conn.prepareStatement(
"SELECT * FROM ovdiagnosis ovd, icdcodes icd WHERE ovd.ICDCode=? and icd.Code=?");
ps.setString(1, icdcode);
ps.setString(2, icdcode);
rs = ps.executeQuery();
diags = diagnosisLoader.loadList(rs);
rs.close();
ps.close();
ps = null;
rs = null;
for (DiagnosisBean bean : diags) {
ps = conn.prepareStatement("SELECT * FROM officevisits ov WHERE ov.ID=?");
ps.setInt(1, (int) bean.getVisitID());
rs = ps.executeQuery();
if (rs.next()) {
ovs.add(loadFullOfficeVist(rs, bean.getVisitID()));
}
rs.close();
ps.close();
}
return ovs;
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* This gets all the procedures for a particular patient on a particular office visit
*
* @param mid The MID of the patient.
* @param ovid The Office Visit ID.
* @return A java.util.List of LabProcedureBeans
* @throws DBException
*/
public List<LabProcedureBean> getAllLabProceduresForDocOV(long ovid) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE OfficeVisitID = ? ");
ps.setLong(1, ovid);
ResultSet rs = ps.executeQuery();
return labProcedureLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Adds the given CPT codes to the given office visit
*
* @param icd A double representing the ICD code to be added.
* @param visitID The ID of the office visit we are adding the code to.
* @return A long for the new ICD code's ID.
* @throws DBException
*/
public long addDiagnosisToOfficeVisit(double icd, long visitID) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("INSERT INTO OVDiagnosis (ICDCode,VisitID) VALUES (?,?)");
ps.setDouble(1, icd);
ps.setLong(2, visitID);
ps.executeUpdate();
return DBUtil.getLastInsert(conn);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns a list of all office visits at a given hospital
*
* @param hospitalID the id of the hospital
* @return a list of the OfficeVisitBeans that hold the office visits
* @throws DBException in the event of a database error
*/
public List<OfficeVisitBean> getOfficeVisitsFromHospital(String hospitalID) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement("SELECT * FROM officevisits WHERE hospitalID = ? ORDER BY ID DESC");
ps.setString(1, hospitalID);
ResultSet rs = ps.executeQuery();
return officeVisitLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Gets an individual lab procedure.
*
* @param id The ID of the lab procedure.
* @return A LabProcedureBean representing the procedure.
* @throws DBException
*/
public LabProcedureBean getLabProcedure(long id) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("SELECT * FROM LabProcedure WHERE LaboratoryProcedureID = ?");
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
rs.next();
return labProcedureLoader.loadSingle(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns a particular office visit given an ID
*
* @param visitID The unique ID of the office visit.
* @return An OfficeVisitBean with the specifics for that office visit.
* @throws DBException
*/
public OfficeVisitBean getOfficeVisit(long visitID) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("Select * From OfficeVisits Where ID = ?");
ps.setLong(1, visitID);
ResultSet rs = ps.executeQuery();
if (rs.next()) return loadFullOfficeVist(rs, visitID);
else return null;
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns a list of all office visits for a given patient
*
* @param pid The MID of the patient.
* @return A java.util.List of Office Visits.
* @throws DBException
*/
public List<OfficeVisitBean> getOfficeVisitsWithNoSurvey(long pid) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"SELECT * FROM OfficeVisits where id not in (select visitid from OVSurvey) and PatientID = ? ORDER BY VisitDate DESC");
ps.setLong(1, pid);
ResultSet rs = ps.executeQuery();
return officeVisitLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns a list of all office visits for a given patient
*
* @param mid The MID of the LHCP you are looking up.
* @return A java.util.List of Office Visits.
* @throws DBException
*/
public List<OfficeVisitBean> getAllOfficeVisitsForLHCP(long mid) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
if (mid == 0L) throw new SQLException("HCPID cannot be null");
conn = factory.getConnection();
ps =
conn.prepareStatement("SELECT * FROM OfficeVisits WHERE HCPID=? ORDER BY VisitDate DESC");
ps.setLong(1, mid);
ResultSet rs = ps.executeQuery();
return officeVisitLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Adds a prescription bean to the database.
*
* @param pres The prescription bean to be added.
* @return The unique ID of the newly added bean.
* @throws DBException
*/
public long addPrescription(PrescriptionBean pres) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"INSERT INTO OVMedication (VisitID,NDCode,StartDate,EndDate,Dosage,Instructions) VALUES (?,?,?,?,?,?)");
prescriptionLoader.loadParameters(ps, pres);
ps.executeUpdate();
return DBUtil.getLastInsert(conn);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Adds an visit and return its ID
*
* @param ov The OfficeVisitBean to be added.
* @return A long indicating the unique ID for the office visit.
* @throws DBException
*/
public long add(OfficeVisitBean ov) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"INSERT INTO OfficeVisits (VisitDate, Notes, HCPID, PatientID, HospitalID) VALUES (?,?,?,?,?)");
setValues(ps, ov);
ps.executeUpdate();
return DBUtil.getLastInsert(conn);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Returns all of the diagnoses associated with the given office visit
*
* @param visitID The unique ID of the office visit.
* @return A java.util.List of diagnoses.
* @throws DBException
*/
public List<DiagnosisBean> getDiagnoses(long visitID) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"Select * From OVDiagnosis,ICDCodes Where OVDiagnosis.VisitID = ? "
+ "AND ICDCodes.Code=OVDiagnosis.ICDCode");
ps.setLong(1, visitID);
ResultSet rs = ps.executeQuery();
return diagnosisLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Updates the information in a particular office visit.
*
* @param ov The Office Visit bean representing the changes.
* @throws DBException
*/
public void update(OfficeVisitBean ov) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps =
conn.prepareStatement(
"UPDATE OfficeVisits SET VisitDate=?, Notes=?, HCPID=?, "
+ "PatientID=?, HospitalID=? WHERE ID=?");
setValues(ps, ov);
ps.setLong(6, ov.getID());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Adds the given CPT codes to the given office visit
*
* @param cptCode
* @param visitID
* @return
* @throws DBException
*/
public long addProcedureToOfficeVisit(String cptCode, long visitID, String hcpid)
throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("INSERT INTO OVProcedure (CPTCode,VisitID,HCPID) VALUES (?,?,?)");
ps.setString(1, cptCode);
ps.setLong(2, visitID);
ps.setString(3, hcpid);
ps.executeUpdate();
return DBUtil.getLastInsert(conn);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Gets the lab procedures for a given LHCP that occur within the next month.
*
* @param ovid The Office Visit ID conducted by the LHCP in question.
* @return A java.util.List of LabProcedureBeans.
* @throws DBException
*/
public List<LabProcedureBean> getLabProceduresForLHCPForNextMonth(long ovid) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
if (ovid == 0L) throw new SQLException("OfficeVisitID cannot be null");
conn = factory.getConnection();
ps =
conn.prepareStatement(
"SELECT * FROM LabProcedure WHERE OfficeVisitID = ? AND Status = ? AND (DateDiff(SYSDATE(),UpdatedDate) <= 30) ORDER BY UpdatedDate DESC");
ps.setLong(1, ovid);
ps.setString(2, LabProcedureBean.Completed);
ResultSet rs = ps.executeQuery();
return labProcedureLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Updates the rights of a user on a given lab procedure.
*
* @param b The LabProcedureBean in question.
* @throws DBException
*/
public void updateRights(LabProcedureBean b) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
if (b.getPid() == 0L) throw new SQLException("PatientMID cannot be null");
conn = factory.getConnection();
ps =
conn.prepareStatement(
"UPDATE LabProcedure SET Rights = ?, UpdatedDate = ? WHERE LaboratoryProcedureID=?");
ps.setString(1, b.getRights());
ps.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis()));
ps.setLong(3, b.getProcedureID());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Gets all the lab procedures that correspond to a particular LOINC.
*
* @param id The LOINC in question.
* @return A java.util.List of LabProcedureBeans.
* @throws DBException
*/
public List<LabProcedureBean> getAllLabProceduresLOINC(long id) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
if (id == 0L) throw new SQLException("PatientMID cannot be null");
conn = factory.getConnection();
ps =
conn.prepareStatement(
"SELECT * FROM LabProcedure WHERE PatientMID = ? ORDER BY LaboratoryProcedureCode ASC");
ps.setLong(1, id);
ResultSet rs = ps.executeQuery();
return labProcedureLoader.loadList(rs);
} catch (SQLException e) {
e.printStackTrace();
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}
/**
* Has this survey been completed?
*
* @param visitID The unique ID of the office visit we are wondering about.
* @return boolean indicating whether this survey is completed.
* @throws DBException
*/
public boolean isSurveyCompleted(long visitID) throws DBException {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = factory.getConnection();
ps = conn.prepareStatement("SELECT count(*) FROM ovsurvey WHERE VisitID = ?");
ps.setLong(1, visitID);
ResultSet rs = ps.executeQuery();
rs.next();
boolean check = (rs.getInt(1) == 0) ? false : true;
rs.close();
return check;
} catch (SQLException e) {
throw new DBException(e);
} finally {
DBUtil.closeConnection(conn, ps);
}
}