PV Usage Quarterly Report

Description

The purpose of the process is to download details about patients who use PatientView such as when they last logged on and what their latest blood test was. There are potentially many uses for this information but the main one is to assist sites in producing Indicator returns for Fiona.

The script (https://github.com/renalreg/pv-scripts/blob/master/scripts/pv_usage.py ) is scheduled to be run via cron on the Live PV DB server (db.patientview.org) as a user called pv2db. It is currently scheduled to do this on 1st of each months (although January, April, July & October are the significant "as-at" months for the Indicators).

[root@RR-RPV-DB ~]# su - pv2db
[pv2db@RR-RPV-DB ~]$ crontab -l
0 12 1 * * . pv-scripts/venv/bin/activate && pv-scripts/scripts/pv_usage.py
[pv2db@RR-RPV-DB ~]$

Technical Details

The script assumes that several Materialized Views have been created in the PatientView database.

mve_pv_usage_obs
mve_pv_usage_transplant_status
mve_pv_usage_modality_status
mve_pv_usage_diagnosis
mve_pv_usage_diagnosis_edta

These can be recreated using https://github.com/renalreg/pv-scripts/blob/master/sql/pv2/create_materialised_views.sql

This is to improve performance when querying the FHIR database. ~~Unfortunately the DDL to create them afresh is missing from the current repo version - see~~

Error rendering macro 'jira' : Unable to locate Jira server for this macro. It may be due to Application Link configuration.

.

The script refreshes these views (taking approximately 15 minutes) then downloads the data which, at present, it uses to create 1 CSV file per Unit.

Process - PV Indicator Returns

At present only BHLY (Bradford, Hull, Leeds, York) are interested in using the report to improve their return.

Look in /home/pv2db/outgoing and download the files for just the units above.

Their data should be sent by NHS.Net e-mail to Elizabeth Lindley as soon as possible after its been run.

PKB email lists for Fiona

The reports requested by Fiona (in tickets similar to RPV-80 ) merge data from PV, UKRDC tracing response, RADAR membership, and PKB. The merge is done with the script https://github.com/renalreg/pv-scripts/blob/master/Fiona_scripts/generate_PKB_report.py. You will need to set the locations for the PV data, output, UKRDC tracing response and PKB data file in PV_FILES_DIRECTORY, OUTPUT_FILES_DIRECTORY, TRACING_RESPONSE, and PKB_DATA respectively, and set the appropriate information to get a tunnel in https://github.com/renalreg/pv-scripts/blob/master/Fiona_scripts/generate_PKB_report.py#L196.

1. PV_FILES_DIRECTORY is the path where the PV data can be found. You can get these files from the RR-RPV-DB Live server as user pv2db from the directory /home/pv2db/outgoing. If this data is older than 2 weeks, you can generate the unit files using (venv) [pv2db@RR-RPV-DB ~]$ python pv-scripts/scripts/pv_usage.py.

2. OUTPUT_FILES_DIRECTORY is the path where the generated reports will be stored.

3. TRACING_RESPONSE is the path where the UKRDC traced data can be found. The traced data is in \\nbsvr325\DBS2_DATA\OUTBOX\DBS\DBS002 in a file with a name similar to UKRDCLive_TRACE_REQUEST_202212101520221201003004467_response.csv. If the traced data is old, the traced file can be requested with:

[rrbatch@systems-live ~]$ /home/rrbatch/ukrdc_tracing/venv/bin/python3.6 /home/rrbatch/ukrdc_tracing/ukrdc_tracing/trace_request_creator.py live

in the systems-live server as rrbatch user.

4. PKB_DATA is the path where the data provided by PKB can be found. This is a file similar to Q:\PKB\Patient List\fd112989.csv

5. Once you have all these constants set, you can run the script https://github.com/renalreg/pv-scripts/blob/master/Fiona_scripts/generate_PKB_report.py. The result will be in OUTPUT_FILES_DIRECTORY. This is the folder that you need to copy for Fiona usually in Q:\PV\PV Exports for PKB\.