Patientview Export

Patientview runs an extract job against the PV database each night at 22.00
This produces a csv file in /opt/ukt/export/ukt_rpv_export.txt

This file contains a list of the NHSNo, Lastname, Firstname, DoB, Postcode for each patient to be queried.

SQL query used in PV

SELECT i.identifier, u.forename, u.surname, u.date_of_birth,  
(SELECT string_agg(cast(resource_id AS varchar), ', ')  
FROM pv_fhir_link WHERE user_id = u.id) AS links 
FROM pv_user u 
LEFT JOIN pv_identifier i ON i.user_id = u.id 
JOIN pv_user_group_role ugr ON ugr.user_id = u.id 
JOIN pv_role r ON r.id = ugr.role_id 
WHERE r.role_name = 'PATIENT' AND u.deleted = false
GROUP BY u.id, i.identifier 

-- Postcode is derived using a separate search
-- NHS number is validated and any invalid NHS numbers or records with out an NHS number are not sent but written to a error file

A cron job running under the account pv2 then sftp the file to the internet sftp server renalpatientview.org to /data/pv/uploads/renal/ukt/incoming at 22:45

UKTBT Return file

UKTBT downloads the file daily using its ukt account
NHSBT return a file uktstatus.gpg encrypted using gpg with a underlying format of:
NHSNo | A/T/O/R/S | N/T/U |
Each column contains a single letter. PV translate the first column from the letter to the following text on the patient record
Letter Meaning in PV
R,N,O Not on List
U No Status uploaded
A Kidney - Active
S Kidney - suspended
T Kidney - Transplanted
Only the first column appears to be processed and used the second is ignored
This gpg file is decrypted and a .txt extension added and stored in /data/pv/feeds/pv2/outgoing/ on the sftpp server.
This is then picked up by a cron job daily at 19:30 by the PV webserver using the pv2 account and stored in /opt/ukt/import/uktstatus.gpg.txt
PV then imports this and updates the patient transplant status.

Browser not supported