1 / 39

Agenda

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

macon-park
Download Presentation

Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Agenda • Demo • Schema review • Code structure • Issues

  2. Schema review • Relationships between tables • Temporary tables • Views • The “tenMinuteJob”

  3. Relationships

  4. Drug Relationships

  5. 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”

  6. 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)

  7. 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

  8. Tempdb tables

  9. 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

  10. tenMinuteJob

  11. Code structure • XML representation • Tokens and Parameters • Invoking report window • RunReport • Backend functions • Jasper/Ireport

  12. XML Representation

  13. Tokens and Parameters • For substitution into queries • $<var> • For display • XML elements • for branching in code • XML elements • XML attributes

  14. Tokens and Parameters

  15. Invoking Report Window • Via menu • Report window launched via menu.php • $url = "runReport.php?rtype=" . $cat . "&amp;reportNumber=" . $report["reportNumber”]… • Via parameters page • Want tokens, but not parameters • $url = “kickPoint.php?rtype=…” • Using full parameter functionality • $url = "kickPoint.php?rtype=" . $cat . "&amp;reportNumber=" . $report["reportNumber"] . "&amp;lang=" . $lang . "&amp;site=" . $site . "&amp;patientStatus=" . $report["patientStatus"] . "&amp;treatmentStatus=" . $report["treatmentStatus"] . "&amp;testType=" . $report["testType"] . "&amp;groupLevel=" . $report["groupLevel"] . "&amp;otherLevel=" . $report["otherLevel"] . "&amp;menu=" . $report["menuSelection"];

  16. 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)

  17. Example with debugging

  18. Backend[Addon] Functions Used by runReport.php • genSemiAnnual(…) • applyCriteria(…) • buildSemiQuery(…) • BuildReportQuery(…) • drawPie/drawBar • generateQueryResult(…) • writeOutput(…)

  19. Example 2

  20. Example 2

  21. Example 2

  22. Example 2

  23. Haiti Jasper Reports

  24. Agenda • Introduction • Using Jasperreports • Q&A

  25. Introduction • An Open source Java reporting tool • http://jasperreports.sourceforge.net/

  26. Using Jasperreports • Design a report • Integrate with TOMCAT • Fill the report with data • Export the report

  27. Design a report • Using iReports (layout, parameters for input, fields for output)

  28. 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"/> …

  29. Design a report • You can edit the JRXML files directly.

  30. 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>

  31. Integrate with TOMCAT ReportViewerServlet serves to generate reports

  32. 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); …

  33. Fill a report with data • Get the database connection. For example, Connection conn = DBUtils.getConnection(JNDI_DATA_SOURCE_NAME);

  34. 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.

  35. 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);

  36. Export a report • Jasperreport offers several formats. • CSV (JRCsvExporter) • PDF (JRPdfExporter) • XLS (JRXlsExporter) • XML (JRXmlExporter) • HTML (JRHtmlExporter)

  37. 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();

  38. Export a report

  39. Issues • Visit (and other) dates • Current status • Correctness • Formatting • Consolidation

More Related