[user_details]
SELECT
p.last_name,
p.first_name,
p.dob,
p.nhs_number,
pd.address,
pd.post_code,
pd.contact_number,
r.name as 'referrer',
rs.display_name as 'location'
FROM outreach.patient_access pa
JOIN hilis4.patients p on pa.patient_id = p.id
JOIN hilis4.patient_demographics pd on pd.patient_id = p.id
JOIN hilis4.referral_sources rs on pd.practice_id = rs.id
JOIN hilis4.referrers r on pd.gp_id = r.id
WHERE pa.email = ?
[patient_details]
SELECT
p.last_name,
p.first_name,
p.dob,
p.nhs_number,
r.name AS 'GP',
rs.display_name AS 'practice',
pd.address,
pd.post_code
FROM outreach.patient_dispatch_detail pdd
JOIN hilis4.patients p on pdd.patient_id = p.id
JOIN hilis4.patient_demographics pd on pd.patient_id = p.id
JOIN hilis4.referral_sources rs on pd.practice_id = rs.id
JOIN hilis4.referrers r on pd.gp_id = r.id
WHERE p.nhs_number = ?
[pack_dispatches]
/* all requests & pack dispatches */
SELECT
r.id,
r.request_number,
r.year,
fo.`option` as 'outcome',
rpd.pack_due,
rpd.pack_sent,
rpd.return_due
FROM hilis4.requests r
JOIN hilis4.patient_case pc on r.patient_case_id = pc.id
JOIN hilis4.patients p on pc.patient_id = p.id
JOIN outreach.request_pack_dispatch rpd on rpd.request_id = r.id
JOIN outreach.request_followup rfu on rfu.request_id = r.id
JOIN outreach.followup_options fo on rfu.followup_option_id = fo.id
WHERE p.nhs_number = ?
ORDER BY r.created_at
[results]
SELECT
r.id,
lp.field_label,
rr.result
FROM outreach.request_results rr
JOIN outreach.lab_params lp on rr.param_id = lp.id
JOIN hilis4.requests r on rr.request_id = r.id
JOIN hilis4.patient_case pc on r.patient_case_id = pc.id
JOIN hilis4.patients p on pc.patient_id = p.id
WHERE p.nhs_number = ?
ORDER BY r.id
