[user_details]
SELECT
    /* p.last_name, p.first_name,  p.nhs_number, */
    p.initials,
    p.dob,
    p.nhs_number,
    pa.email,
    pa.patient_id,
    pa.last_login,
    pa.password,
    pd.address,
    pd.post_code,
    pd.contact_number,
    r.name as 'GP',
    rs.display_name as 'practice'
FROM patient_access pa
    JOIN patients p on pa.patient_id = p.id
    JOIN patient_demographics pd on pd.patient_id = p.id
    JOIN referral_sources rs on pd.practice_id = rs.id
    JOIN referrers r on pd.gp_id = r.id
WHERE p.nhs_number = ? OR p.id = ?

[pack_dispatches]
/* all requests & pack dispatches, reverse chronological order */
SELECT
    r.id,
    r.request_number,
    r.year,
    r.registered,
    IFNULL(d.count, 0) as 'section_data_count',
    so.description as 'request_status',
    fo.`option`,
    fo.`label` as 'outcome',
    /* p.`status` as 'patient_status', */
    rpd.pack_due,
    rpd.pack_sent,
    rpd.return_due
FROM requests r
	JOIN status_options so on r.status_option_id = so.id
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN patients p on pc.patient_id = p.id
    /* for deployment with HILIS4:
    JOIN patient_demographics pd on pd.patient_id = p.id
    JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id
        and s.sample_code = 'CMP' ) on rs.request_id = r.id */
	LEFT JOIN request_pack_dispatch rpd on rpd.request_id = r.id
	LEFT JOIN ( request_followup rfu JOIN followup_options fo
        on rfu.followup_option_id = fo.id )	on rfu.request_id = r.id
    LEFT JOIN lab_sections_data d on d.request_id = r.id
WHERE p.id = ?
ORDER BY r.id DESC
LIMIT 10

[results]
/* not using
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
*/

[how_to_find_patients_with_all_three_options]
create temporary table t1 /* any duration option (one_month, three_month, etc )*/
    select distinct(pc.patient_id) from request_followup rfu
        join followup_options fo on rfu.followup_option_id = fo.id
        join hilis4.requests r on rfu.request_id = r.id
        join hilis4.patient_case pc on r.patient_case_id = pc.id
    where fo.`option` rlike 'month|week';

create temporary table t2 /* any duration + clinic_return */
    select distinct(pc.patient_id) from request_followup rfu
        join followup_options fo on rfu.followup_option_id = fo.id
        join hilis4.requests r on rfu.request_id = r.id
        join hilis4.patient_case pc on r.patient_case_id = pc.id
        join t1 on pc.patient_id = t1.patient_id
    where fo.`option` = 'clinic_return';

/* any duration + clinic_return + pack_due in future */
select distinct(pc.patient_id) from request_pack_dispatch rpd
	join hilis4.requests r on rpd.request_id = r.id
	join hilis4.patient_case pc on r.patient_case_id = pc.id
	join t2 on pc.patient_id = t2.patient_id
where rpd.pack_due > curdate();