/* 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 = ?

/* 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 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