390 likes | 497 Vues
Agenda. Demo Schema review Code structure Issues. Schema review. Relationships between tables Temporary tables Views The “tenMinuteJob”. Relationships. Drug Relationships. Why Temporary Tables?. To eliminate bad data When aggregations or computations are required
E N D
Agenda • Demo • Schema review • Code structure • Issues
Schema review • Relationships between tables • Temporary tables • Views • The “tenMinuteJob”
Why Temporary Tables? • To eliminate bad data • When aggregations or computations are required • To do snapshots in time • To simplify processing • To “divide and conquer”
encValid = encounter + visitDate create proc sp_genEncValid as delete encValid insert into encValid select encounter_id, siteCode, patientID, visitDateDd, visitDateMm,visitDateYy, lastModified, encounterType, seqNum, clinicPatientID, encStatus, encComments, dbSite, convert(datetime, visitdateMm + '/' + visitdateDd + '/' + visitdateYy) as visitDate from encounter where encStatus < 255 and sitecode in (select sitecode from siteLookup) and sitecode != '00000' and isdate(visitdateMm + '/' + visitdateDd + '/' + visitdateYy) = 1 and patientid in (select patientid from patient where patStatus = 0)
cd4Table = all cd4 readings in DB create proc sp_genCD4table as delete cd4Table delete cd4Temp insert into dbo.cd4Temp select distinct siteCode, patientID,convert(smalldatetime,visitdatemm+'/'+visitdatedd+'/'+visitdateyy), lowestCd4Cnt from vitals where lowestCd4Cnt is not null and left(lowestcd4cnt,1) <> '-' and isnumeric(lowestcd4cnt) = 1 and isdate(visitdatemm+'/'+visitdatedd+'/'+visitdateyy) = 1 insert into dbo.cd4Temp select distinct siteCode, patientID,convert(smalldatetime,visitdatemm+'/'+visitdatedd+'/'+visitdateyy), Cd4 from medicalEligARVs where Cd4 is not null and left(cd4,1) <> '-' and isnumeric(cd4) = 1 and isdate(visitdatemm+'/'+visitdatedd+'/'+visitdateyy) = 1 insert into dbo.cd4Temp select distinct siteCode, patientID, convert(smalldatetime,visitdatemm+'/'+visitdatedd+'/'+visitdateyy), result from labs where result is not null and left(result,1) <> '-' and isnumeric(result) = 1 and isdate(visitdatemm+'/'+visitdatedd+'/'+visitdateyy) = 1 and labID in (18,102) insert into dbo.cd4Table select distinct siteCode, patientID, visitdate, cd4 from cd4Temp
Views create view v_prescriptions as select t.*, e.visitdate, e.encounterType, e.clinicPatientID, l.drugName from prescriptions t, drugLookup l, encValid e where t.siteCode = e.siteCode and t.patientID = e.patientID and t.visitdatedd = e.visitdatedd and t.visitdatemm = e.visitdatemm and t.visitdateyy = e.visitdateyy and t.seqNum = e.seqNum and t.drugID = l.drugID create view v_medsDispensed as select * from v_prescriptions where isdate(dispDateMm + '/01/' + dispDateYy) = 1
Code structure • XML representation • Tokens and Parameters • Invoking report window • RunReport • Backend functions • Jasper/Ireport
Tokens and Parameters • For substitution into queries • $<var> • For display • XML elements • for branching in code • XML elements • XML attributes
Invoking Report Window • Via menu • Report window launched via menu.php • $url = "runReport.php?rtype=" . $cat . "&reportNumber=" . $report["reportNumber”]… • Via parameters page • Want tokens, but not parameters • $url = “kickPoint.php?rtype=…” • Using full parameter functionality • $url = "kickPoint.php?rtype=" . $cat . "&reportNumber=" . $report["reportNumber"] . "&lang=" . $lang . "&site=" . $site . "&patientStatus=" . $report["patientStatus"] . "&treatmentStatus=" . $report["treatmentStatus"] . "&testType=" . $report["testType"] . "&groupLevel=" . $report["groupLevel"] . "&otherLevel=" . $report["otherLevel"] . "&menu=" . $report["menuSelection"];
runReport.php DEBUG_FLAG = true displays much of the processing and queries • Reads parameters • Special processing (Nastad, PEPFAR) • Generates temp tables • Generates graph • Formats and displays report in separate window • Optionally generates Excel or PDF (Jasper)
Backend[Addon] Functions Used by runReport.php • genSemiAnnual(…) • applyCriteria(…) • buildSemiQuery(…) • BuildReportQuery(…) • drawPie/drawBar • generateQueryResult(…) • writeOutput(…)
Haiti Jasper Reports
Agenda • Introduction • Using Jasperreports • Q&A
Introduction • An Open source Java reporting tool • http://jasperreports.sourceforge.net/
Using Jasperreports • Design a report • Integrate with TOMCAT • Fill the report with data • Export the report
Design a report • Using iReports (layout, parameters for input, fields for output)
Design a report • Generate a JRXML file. patientRpt.jrxml <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd"> <jasperReport name="patientRpt" …. <parameter name="siteName" isForPrompting="true" class="java.lang.String"/> … <field name=“total" class="java.lang.Integer"/> …
Design a report • You can edit the JRXML files directly.
Integrate with TOMCAT • reports.xml Add/Edit entries in reports.xml so we can get the right request of TOMCAT. For example, <report id="1591" patientStatus="62" treatmentStatus="1" testType="0"> <title lang="en">Active/Inactive Patients</title> <title lang="fr">Patients actifs/inactifs</title> … <reportFile groupLevel="1" otherLevel="1">patientRpt.jasper</reportFile>
Integrate with TOMCAT ReportViewerServlet serves to generate reports
Integrate with TOMCAT • Get the parameter values from the POST/GET method. ReportViewerServlet.java public void doPost(HttpServletRequest req, HttpServletResponse resp) { … String lang = req.getParameter(PARAM_REPORT_LANG); String site = req.getParameter(PARAM_REPORT_SITE); …
Fill a report with data • Get the database connection. For example, Connection conn = DBUtils.getConnection(JNDI_DATA_SOURCE_NAME);
Fill a report with data • Put values to parameters as we mentioned in the previous phases. For example, parameters.put(“siteName”,”Hopital Grace Children”); • Especially queries.
Fill a report with data • Using the connection and queries, we can get the data to fill the report. JasperPrint jasperPrint = JasperFillManager.fillReport(report, parameters, conn);
Export a report • Jasperreport offers several formats. • CSV (JRCsvExporter) • PDF (JRPdfExporter) • XLS (JRXlsExporter) • XML (JRXmlExporter) • HTML (JRHtmlExporter)
Export a report • Create an exporter, for example: JRHtmlExporter exporter = new JRHtmlExporter(); • Set parameters for the exporter exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint); exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, response.getWriter()); • Export the report exporter.exportReport();
Issues • Visit (and other) dates • Current status • Correctness • Formatting • Consolidation