agenda demo schema review code structure issues. schema review relationships between tables...

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• 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 cd4Tabledelete cd4Tempinsert 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) = 1insert 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

Tempdb tables

Viewscreate 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

tenMinuteJob

Step Name DB Procedure Result

genCD4table sp_genCD4Table cd4Table

genPepfar sp_whoInit drugTable,

pepfarTable [regimens], cohortTable

genBloodEval sp_bloodEval bloodeval1, bloodeval2 (for v_bloodEval)

discontinuedDates sp_genDiscDates discTable

genActivePatients sp_genActivePatients activePatients [no longer used]

Code structure

• XML representation

• Tokens and Parameters

• Invoking report window

• RunReport

• Backend functions

• Jasper/Ireport

XML Representation

Tokens and Parameters

• For substitution into queries

– $<var>

• For display

– XML elements

• for branching in code

– XML elements

– XML attributes

Tokens and Parameters

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"];

runReport.php

DEBUG_FLAG = true displays much of the processing and queries

1. Reads parameters2. Special processing (Nastad, PEPFAR)3. Generates temp tables4. Generates graph5. Formats and displays report in separate

window6. Optionally generates Excel or PDF (Jasper)

Example with debugging

Backend[Addon] Functions

Used by runReport.php• genSemiAnnual(…)• applyCriteria(…)• buildSemiQuery(…)• BuildReportQuery(…)• drawPie/drawBar• generateQueryResult(…)• writeOutput(…)

Example 2

Example 2

Example 2

Example 2

HaitiHaitiHaitiHaiti

Jasper ReportsJasper 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.javapublic 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 exporterexporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint); exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, response.getWriter());

• Export the report exporter.exportReport();

Export a report

Issues

• Visit (and other) dates

• Current status

• Correctness

• Formatting

• Consolidation