= DAPS (Demographics Authentication Processing System)

DAPS (Digital Audit of Patients Submission) takes a sub-set of patient details and submits them to the UHL and Spine systems for validation and to provide the additional data.

To submit a file for processing with DAPS (Data Analysis and Partitioning with Spine), copy a CSV file with the correct columns into the folder !\\uhldwh\BRICCS_DBS_IN.

When processing is complete DAPS (Death date, Address and Phone number Supplementation) will place 3 files for each submitted CSV in the folder V:\Study specific recruitment projects\IT returned files\

== Processing

The processing occurs in 2 stages:

1. Send
2. Reeceive

=== Send Process

Main database is the DWPATMATCH database on the data warehouse server.

1. Copy values from the CSV file in the !\\uhldwh\BRICCS_DBS_IN directory to the {{{DBS_TRACING}}} table with a batch ID

'''The two following steps take place ''concurrently''.  I think this is an error, but it may only be the case in my (RaB) copy and not the live copy'''

2. Patient matching
3. Export patient details to the file to be sent to the Spine

==== Patient Matching

1. Copy the values from the {{{DBS_TRACING}}} table to the {{{PMI_DEMOGRAPHICS}}} table.
2. Run the script {{{USP_UPDATE_MATCH_FIELDS}}}.  This script runs various matching scripts if successful they update the S Number on the {{{PMI_DEMOGRAPHICS}}} table and assign the record a matching code (the code from the start of the script name).  If all these fail, the record is given a code of '6 - NO MATCH'
 a. {{{USP_UPDATE_MATCH_1A_NHS_NO_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1A_S_NUMBER_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1A_IPN_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1B_CASENOTE_NUMBER_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1C_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1C_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1D_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1E_NHS_NO_MATCH_DOB}}}
 a. {{{USP_UPDATE_MATCH_2A_S_NUMBER_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2A_IPN_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2B_S_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_2B_S_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_2C_CASENOTE_NUMBER_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2D_CASENOTE_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_2E_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2E_IPN_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2F_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_3A_S_NUMBER_MATCH_WITH_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3A_IPN_MATCH_WITH_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3B_S_NUMBER_MATCH_WITH_DOB}}}
 a. {{{USP_UPDATE_MATCH_3B_IPN_MATCH_WITH_DOB}}}
 a. {{{USP_UPDATE_MATCH_3C_CASENOTE_NUMBER_MATCH_WITH_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3D_CASENOTE_NUMBER_MATCH_WITH_DOB}}}
 a. {{{USP_UPDATE_MATCH_3E_S_NUMBER_MATCH_WITH_PREV_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3E_IPN_MATCH_WITH_PREV_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3F_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_4A_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4B_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_DOB}}}
 a. {{{USP_UPDATE_MATCH_4C_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4D_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4E_DEMOGRAPHIC_SURNAME_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4F_DEMOGRAPHIC_FORENAME_3_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_5A_S_NUMBER_MATCH}}}
 a. {{{USP_UPDATE_MATCH_5A_NHS_NO_MATCH}}}
 a. {{{USP_UPDATE_MATCH_5B_CASENOTE_NUMBER_MATCH}}}

==== Export Patient

1. Run script {{{USP_UPDATE_S_NUMBER_AND_INTERNAL_NUMBER_IN_DBS_TRACING_BY_BATCH_ID}}} that updates the S number on the {{{DBS_TRACING}}} table from the {{{PMI_DEMOGRAPHICS}}} table where the match level is not like {{{'%5'}}} (which might never happen anyway).
2. Write the output of the stored procedure {{{USP_OUTPUT_NNSTS_BY_BATCH_ID}}} to the batch file to be sent to the Spine.  The stored procedure selects data only from the {{{Patient}}} table in the {{{DWREPO}}} that have a matching S Number from the {{{DBS_TRACING}}} table.  That is, all data comes from the {{{Patient}}} table and none from the {{{DBS_TRACING}}} table.
3. Execute the batch file {{{P:\DataForImport\Demographic Batch Service\RUN_Process_NNITS_auto.bat}}} which presumably export file somewhere to be processed.


== Specific Runs

=== Graphic 2 Extract of S Numbers

{{{#!sql
WITH demo AS (
SELECT 
	[SYSTEM_NUMBER_CURRENT]
    ,[FORENAMES]
    ,[SURNAME]
    ,[POSTCODE]
    ,[GENDER]
    ,[DATE OF BIRTH]
    ,[BATCH_ID]
    ,[MATCH_DEMOGRAPHIC_LEVEL]
  FROM [DWPATMATCH].[dbo].[DBS_TRACING]
)

SELECT
	male.FORENAMES
	,male.SURNAME
	,male.[DATE OF BIRTH]
	,ISNULL(postcode.POSTCODE, '') [PostCode]
	,ISNULL(male.SYSTEM_NUMBER_CURRENT, '') [Male_S_Number]
	,ISNULL(female.SYSTEM_NUMBER_CURRENT, '') [Female_S_Number]
	,ISNULL(postcode.SYSTEM_NUMBER_CURRENT, '') [Postcode_S_Number]
    ,COALESCE(male.SYSTEM_NUMBER_CURRENT, female.SYSTEM_NUMBER_CURRENT, postcode.SYSTEM_NUMBER_CURRENT, '') [S Number]
    ,CASE WHEN (male.SYSTEM_NUMBER_CURRENT <> female.SYSTEM_NUMBER_CURRENT)
			OR (male.SYSTEM_NUMBER_CURRENT <> postcode.SYSTEM_NUMBER_CURRENT)
			OR (female.SYSTEM_NUMBER_CURRENT <> postcode.SYSTEM_NUMBER_CURRENT)
			THEN 'Yes' ELSE '' END Mismatch
FROM	demo male
LEFT JOIN	demo female ON female.SURNAME = male.SURNAME
						and female.FORENAMES = male.FORENAMES
						and female.[DATE OF BIRTH] = male.[DATE OF BIRTH]
						and female.BATCH_ID = 10930 and female.GENDER = 'f'
LEFT JOIN	demo postcode ON postcode.SURNAME = male.SURNAME
						and postcode.FORENAMES = male.FORENAMES
						and postcode.[DATE OF BIRTH] = male.[DATE OF BIRTH]
						and postcode.BATCH_ID = 10929
WHERE	male.BATCH_ID = 10930 and male.GENDER = 'm'

}}}