System.exit(1);
}
}
public static void work() throws SQLException, IOException, ClassNotFoundException {
CSVWriter csvWriter = null;
Connection con = null;
Connection shadowCon = null;
Statement stmt = null;
Statement shadowStmt = null;
HeaderData header = new HeaderData();
VisitData visits[] = new VisitData[MAX_VISIT_CNT];
for (int i = 0; i < MAX_VISIT_CNT; i++) {
visits[i] = new VisitData();
}
try {
Class.forName(Mediator.getProperty("source.driver"));
con = DriverManager.getConnection(Mediator.getProperty("source.url"));
stmt = con.createStatement();
// Query shadow database to determine which patients to pull records for
Class.forName(Mediator.getProperty("shadow.driver"));
shadowCon = DriverManager.getConnection(Mediator.getProperty("shadow.url"),
Mediator.getProperty("shadow.username"),
Mediator.getProperty("shadow.password"));
shadowStmt = shadowCon.createStatement();
// See if any transactions have happened in the time frame we're interested in for the tables we care about
// First, get the list of tables that we're interested in
String tableList = "('" + Mediator.getProperty("source.tableList").replace(",", "','") + "')";
if ("".equals(tableList) || tableList == null) {
log(Level.SEVERE, "No tables listed in properties file.", 1);
}
// Next, get the date we want to use when checking for recent transactions
java.util.Date now = Calendar.getInstance().getTime();
String transSince = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
.format(now.getTime() - new Long(Mediator.getProperty("scheduler.lookback")));
if ("".equals(transSince) || transSince == null) {
log(Level.SEVERE, "Could not calculate date to use: " + transSince + ".", 1);
} else {
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO,
"About to start mining transactions since {0}", transSince);
}
// Finally, query the transaction_data table to get a list of patient_ids associated with the transaction(s)
String sql1 = "SELECT DISTINCT td.data AS data FROM transaction_data td, "
+ "transaction tr "
+ "WHERE td.column_id IN "
+ "(SELECT id FROM `column` "
+ "WHERE name = 'patient_id' AND table_id IN "
+ "(SELECT id FROM `table` WHERE name IN " + tableList + " " + ")) "
+ "AND td.data IS NOT NULL "
+ "AND LTRIM(RTRIM(td.data)) != '' "
+ "AND td.transaction_id = tr.id "
+ "AND tr.created_datetime >= '" + transSince + "'";
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.FINE, sql1);
ResultSet rs = shadowStmt.executeQuery(sql1);
ArrayList<String> shadowPids = new ArrayList<String>();
while (rs.next()) {
shadowPids.add(rs.getString("data").replace(".0", ""));
}
// Need to make sure the patient_ids found in shadow still exist in C-PAD
String sql2 = "SELECT DISTINCT patient_id FROM tblpatient_information "
+ "WHERE patient_id IS NOT NULL";
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.FINE, sql2);
rs = stmt.executeQuery(sql2);
ArrayList<String> cpadPids = new ArrayList<String>();
while (rs.next()) {
cpadPids.add(rs.getString("patient_id").replace(".0", ""));
}
ArrayList<String> cpadPidsToRemove = new ArrayList<String>();
for (int i = 0; i < cpadPids.size(); i++) {
if (!shadowPids.contains(cpadPids.get(i))) {
cpadPidsToRemove.add(cpadPids.get(i));
}
}
cpadPids.removeAll(cpadPidsToRemove);
int recCnt = cpadPids.size();
if (recCnt == 0) {
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "No updated patient records found in the shadow database since {0}.", transSince);
} else {
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "{0} updated patient records found since {1}", new Object[]{recCnt, transSince});
}
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "Extracting data for {0} patient{1}", new Object[]{recCnt, recCnt == 1 ? "." : "s."});
PreparedStatement headerStmts[] = new PreparedStatement[6];
headerStmts[0] = con.prepareStatement("select pi.patient_id, pi.first_name, pi.last_name, pi.dob, "
+ "pi.age, pi.agemnth, pi.date_entered, s.sexname, m.maritalname "
+ "from (tlkSex s INNER JOIN (tblpatient_information pi LEFT OUTER JOIN "
+ "tlkmarital m ON pi.marital_status = m.maritalcode) ON s.sexcode = pi.sex) "
+ "where pi.patient_id = ?");
headerStmts[1] = con.prepareStatement("select postal_address, telephone, district, "
+ "location, sub_location "
+ "from tbladdress "
+ "where patient_id = ?");
headerStmts[2] = con.prepareStatement("select ts.first_name, ts.last_name, ts.postal_address, ts.telephone, "
+ "ts.relationship as rel1, ts.relationship_other, sr.relationship as rel2 "
+ "from tbltreatment_supporter ts "
+ "left join tlkSupporter_relationships sr on ts.relationship = sr.relationid "
+ "where ts.patient_id = ?");
headerStmts[3] = con.prepareStatement("select fm.FmailyMemAge as age, fm.FmailyMemRel as rel1, "
+ "sr.relationship as rel2, fm.FmailyMemHIV as hiv_status, fm.FmailyMemCare as in_care, "
+ "fm.FmailyMemCCCN as pid "
+ "from tblFamilyMembers fm "
+ "left join tlkSupporter_relationships sr on fm.FmailyMemRel = sr.relationid "
+ "where fm.patient_id = ?");
headerStmts[4] = con.prepareStatement("select label "
+ "from Tbl_Values tv "
+ "where tv.category = ? "
+ "and tv.[value] = ?");
headerStmts[5] = con.prepareStatement("select Organization, SiteCode, District, Province "
+ "from tblOrganization");
PreparedStatement visitStmts[] = new PreparedStatement[9];
visitStmts[0] = con.prepareStatement("select count(visit_id) as visits from tblvisit_information where patient_id = ?");
visitStmts[1] = con.prepareStatement("select top " + MAX_VISIT_CNT + " vi.visit_id, vi.visit_date, vi.weight, vi.height, "
+ "p.yesno as pregnancy, vi.delivery_date, t.tbstatus as tbstatus, vi.other_medication, vi.cd4_result, "
+ "cs.yesno as cotrim, ca.adherence as cotrim_adherence, fs.yesno as fp_status, "
+ "vi.cd4_results_percent, vi.hb_result, vi.RPR_result, vi.TBSputum_result, "
+ "vi.art_regimen, ar.firstregimen, vi.art_other, aa.adherence, vi.ARTDose, "
+ "vi.other_testType, vi.other_test_result, vi.other_testType2, vi.other_test_result2, "
+ "vi.referred_to, vi.next_visit_date, vi.clinician_initial, vi.WHOstage, "
+ "vi.BMI, vi.TBStDate, vi.VisitType, vi.DuraSART, vi.DuraCReg, vi.tb_Tx, vi.INH, vi.RiskPopu, "
+ "vi.PwPDis, vi.PwPPaT, vi.PwPCon, vi.PwPSTI, pi.artstart_date "
+ "from (tblpatient_information pi INNER JOIN "
+ "(((((((tblvisit_information vi LEFT OUTER JOIN "
+ "tlkyesno p ON vi.pregnancy = p.yesnocode) LEFT OUTER JOIN "
+ "tlktbstatus t ON vi.tb_status = t.tbcode) LEFT OUTER JOIN "
+ "tlkadherencestatus aa ON vi.art_adherence = aa.adherecode) LEFT OUTER JOIN "
+ "tlkregimenfirst ar ON vi.art_regimen = ar.regnum) LEFT OUTER JOIN "
+ "tlkyesno cs ON vi.cotrim = cs.yesnocode) LEFT OUTER JOIN "
+ "tlkyesno fs ON vi.fp_status = fs.yesnocode) LEFT OUTER JOIN "
+ "tlkadherencestatus ca ON vi.cotrim_adherence = ca.adherecode) ON pi.patient_id = vi.patient_id) "
+ "where vi.patient_id = ? "
+ "and vi.visit_date <= now() "
+ "order by vi.visit_date desc");
visitStmts[2] = con.prepareStatement("select vi.visit_date, vi.art_regimen, vi.art_other, ar.firstregimen "
+ "from tblvisit_information vi "
+ "left join tlkregimenfirst ar on vi.art_regimen = ar.regnum "
+ "where vi.patient_id = ? "
+ "and vi.visit_id <> ? "
+ "and vi.visit_date <= ? "
+ "order by vi.visit_date desc");
visitStmts[3] = con.prepareStatement("select au.unsatisfactoryadherence, uc.UnsatCotriReaon, uc.UnsatCotriother "
+ "from tblUnsatisfactorycotrimoxazole uc, tlkadherenceunsatisfactory au "
+ "where uc.patient_id = ? "
+ "and uc.visit_id = ? "
+ "and uc.UnsatCotriReaon = au.adherencecode");
visitStmts[4] = con.prepareStatement("select au.unsatisfactoryadherence, ua.UnsatARTReason, ua.UnsatARTOth "
+ "from tblUnsatisfactoryart ua, tlkadherenceunsatisfactory au "
+ "where ua.patient_id = ? "
+ "and ua.visit_id = ? "
+ "and ua.UnsatARTReason = au.adherencecode");
visitStmts[5] = con.prepareStatement("select fp.fpmethod as method, fp.fpother, fl.fpmethod as method2 "
+ "from tblfpmethod fp "
+ "left join tlkfpmethod fl on fp.fpmethod = fl.fpmethodcode "
+ "where fp.patient_id = ? "
+ "and fp.visit_id = ?");
visitStmts[6] = con.prepareStatement("select se.artsideeffects, se.othersideeffects, sl.artsideeffects as effects2 "
+ "from tblARTSideEffects se "
+ "left join tlkartsideeffects sl on se.artsideeffects = sl.sideeffectscode "
+ "where se.patient_id = ? "
+ "and se.visit_id = ?");
visitStmts[7] = con.prepareStatement("select oi.newoi, oi.newoiother, il.oi_name "
+ "from tblNewOI oi "
+ "left join tlkoi_code il on oi.newoi = il.oi_id "
+ "where oi.patient_id = ? "
+ "and oi.visit_id = ?");
visitStmts[8] = con.prepareStatement("select label "
+ "from Tbl_Values tv "
+ "where tv.category = 'VisitType' "
+ "and tv.[value] = ?");
int cnt = 0;
List<String[]> recordList = new ArrayList<String[]>();
for (int a = 0; a < cpadPids.size(); a++) {
int pid = Integer.parseInt(cpadPids.get(a));
header.reset();
ExtractHeaderData(headerStmts, pid, header);
for (int i = 0; i < MAX_VISIT_CNT; i++) {
visits[i].reset();
}
ExtractVisitData(visitStmts, pid, visits);
String finalCsv = "";
finalCsv += header.printHeaderDelim("\t");
finalCsv += "\t";
// Fill in currently unused fields
for (int i = 0; i < FILLER_CNT; i++) {
finalCsv += "\t";
}
for (int i = 0; i < visits.length; i++) {
finalCsv += visits[i].printHeaderDelim("\t");
if (i < visits.length - 1) {
finalCsv += "\t";
}
}
String[] record = finalCsv.split("\t");
recordList.add(record);
if (++cnt % outputRecordLimit == 0 || (a == cpadPids.size() - 1)) {
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "({0})", cnt);
int recordCount = outputRecordLimit;
if (cnt % outputRecordLimit != 0) {
recordCount = cnt % outputRecordLimit;
}
String filePath = createFileName(recordCount);
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.FINE, "About to write {0} record(s) to {1}",
new Object[]{recordCount, filePath});
csvWriter = new CSVWriter(new FileWriter(new File(filePath)), '\t');
csvWriter.writeAll(recordList);
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.FINE, "Finished writing {0} record(s) to {1}",
new Object[]{recordCount, filePath});
recordList.clear();
}
}
// Send file to remote Mirth instance if configured to do so
if ("remote".equalsIgnoreCase(Mediator.getProperty("mirth.location"))) {
if (!"".equals(Mediator.getProperty("mirth.url"))
&& Mediator.getProperty("mirth.url") != null) {
if (sendMessage(Mediator.getProperty("mirth.url"), Mediator.getProperty("outputfilename"))) {
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "File sent!");
} else {
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "File not sent!");
}
} else {
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "No URL provided for remote Mirth instance. The file was not sent!");
}
}
Mediator.getLogger(CpadDataExtract.class.getName()).log(Level.INFO, "Done!");
} finally {
try {
if (csvWriter != null) {
csvWriter.close();
}
if (con != null) {
con.close();
}
if (stmt != null) {