Zeports - Aggregate patient data

The zeports database de-normalises data from the main zeprs database. It is used in two cases: for the Reflex report on the live ZEPRS server, and also to create a separate data warehouse.

Reflex report

The stored procedure "daily_reflex_report" runs nightly on the live ZEPRS db server. The cron job is /etc/cron.daily/run_hiv_report.sh. This cron job executes a sql script called run_hiv_report.sql. These files are attached to this page.

This script aggregates data that is stored in the hiv_report table, which is created by the stored procedure, sp_create_hiv_report_table(). This stored procedure loops through arvregimen and labType to aggregate arv, cd4, and hgb data. The value for regimen_visit_date is used for encounter_date for arvregimen; the value for dateLabRequest is used for cd4 and hgb. After that procedure has completed, sp_ega() calculates the current ega for each record and updates each record.. The SQL for both of these stored procedures is attached.

Data Warehouse

It is created by running the zeports_lusaka.bat file included in the reports-distro.zip archive. This batch file downloads the latest zeprs database and calls the sp_build_history stored procedure in zeports.

If you open up the zeports database in MyManager and click on Procedures, you can see where all of the data originates.

sp_copy_demographics takes data from patientregistration and patient tables to demographics table.

sp_build_history points to all of the procedures that build the other tables. sp_build_history loops through each patient in demographics table and builds the record for each patient. It executes the following stored procedures:

• sp_pregs_cn - data from prevpregnancies table - goes into history table
• sp_med_surg_hist - data from medSurgHist - goes into history table
• sp_antenatal - data from pregnancydating, routineante, initialvisit - goes into antenatal table
• sp_drugs_labs - data from safemotherhoodcare, rpr, labtest, smcounselingvisit, arvRegimen, currentmedicine - does into drugs_labs table