Transplant Status - current PV-UKT process

Patientview Export

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

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

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

1. UKTBT downloads the file daily using its ukt account

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

3. This gpg file is decrypted and a .txt extension added and stored in /data/pv/feeds/pv2/outgoing/  on the sftpp server.
4. 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 
5. PV then imports this and updates the patient transplant status.