[granulomatous_inflammation_status_alert]
SELECT
    p.last_name,
    p.first_name,
    p.middle_name,
    p.dob,
    p.nhs_number,
    pc.unit_number,
    rs.display_name as 'location',
    ref.name as 'referrer',
    req.id as 'request_id',
    req.request_number,
    req.year,
    req.created_at,
    rr.status,
    d.name as 'diagnosis'
FROM
    requests req
    join patient_case pc on req.patient_case_id = pc.id
    join patients p on pc.patient_id = p.id
    join referral_sources rs on pc.referral_source_id = rs.id
    join referrer_department rd on req.referrer_department_id = rd.id
    join referrers ref on rd.referrer_id = ref.id
	join request_history h on h.request_id = req.id
    join request_report_view rr on rr.request_id = req.id
    join diagnoses d on rr.diagnosis_id = d.id
    join request_lab_test_status rlts on rlts.request_id = req.id
    join lab_tests lt on rlts.lab_test_id = lt.id
    join lab_test_status_options so on rlts.status_option_id = so.id
WHERE d.name = 'Granulomatous inflammation'
    and lt.field_label = 'TB culture'
    and so.description = 'complete'
	and rs.organisation_code like 'RR8%'
	and h.action = 'authorised'
	and DATE(h.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)

[paediatrics_status_alert]
/* combined into juvenile_diagnosis_status_alert
SELECT
    p.last_name,
    p.first_name,
    p.middle_name,
    p.dob,
    p.nhs_number,
    pc.unit_number,
    rs.display_name as 'location',
    ref.name as 'referrer',
    req.id as 'request_id',
    req.request_number,
    req.year,
    req.created_at,
    rr.status,
    d.name as 'diagnosis'
FROM
    requests req
    join patient_case pc on req.patient_case_id = pc.id
    join patients p on pc.patient_id = p.id
    join referral_sources rs on pc.referral_source_id = rs.id
    join referrer_department rd on req.referrer_department_id = rd.id
    join referrers ref on rd.referrer_id = ref.id
	join request_history h on h.request_id = req.id
    join request_report rr on rr.request_id = req.id
    join diagnoses d on rr.diagnosis_id = d.id
WHERE rr.status IN ('new','relapsed')
    and p.dob IS NOT NULL
    and YEAR(CURRENT_DATE()) - YEAR(dob) -
        CASE WHEN MONTH(CURRENT_DATE) > MONTH(dob) THEN 0
            WHEN MONTH(CURRENT_DATE) < MONTH(dob) THEN 1
            WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(dob) THEN 1
            ELSE 0 END < 18
    and rs.organisation_code like 'RR8%'
	and h.action = 'authorised'
	and date(h.time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
*/

[young_adult_status_alert]
/* combined into juvenile_diagnosis_status_alert
SELECT
    p.last_name,
    p.first_name,
    p.middle_name,
    p.dob,
    p.nhs_number,
    pc.unit_number,
    rs.display_name as 'location',
    ref.name as 'referrer',
    req.id as 'request_id',
    req.request_number,
    req.year,
    req.created_at,
    rr.status,
    d.name as 'diagnosis'
FROM
    requests req
    join patient_case pc on req.patient_case_id = pc.id
    join patients p on pc.patient_id = p.id
    join referral_sources rs on pc.referral_source_id = rs.id
    join referrer_department rd on req.referrer_department_id = rd.id
    join referrers ref on rd.referrer_id = ref.id
	join request_history h on h.request_id = req.id
    join request_report rr on rr.request_id = req.id
    join diagnoses d on rr.diagnosis_id = d.id
WHERE rr.status IN ('new','relapsed')
    and p.dob IS NOT NULL
    and YEAR(CURRENT_DATE()) - YEAR(dob) -
        CASE WHEN MONTH(CURRENT_DATE) > MONTH(dob) THEN 0
            WHEN MONTH(CURRENT_DATE) < MONTH(dob) THEN 1
            WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(dob) THEN 1
            ELSE 0 END BETWEEN 13 AND 25
    and rs.organisation_code like 'RR8%'
	and h.action = 'authorised'
	and date(h.time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
*/

[juvenile_diagnosis_status_alert]
SELECT
    p.last_name,
    p.first_name,
    p.middle_name,
    p.dob,
    YEAR(CURRENT_DATE()) - YEAR(p.dob) -
	   ( CASE WHEN MONTH(CURRENT_DATE) > MONTH(p.dob) THEN 0
	            WHEN MONTH(CURRENT_DATE) < MONTH(p.dob) THEN 1
	            WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(p.dob) THEN 1
	            ELSE 0 END ) as 'age',
    p.nhs_number,
    pc.unit_number,
    rs.display_name as 'location',
    ref.name as 'referrer',
    req.id as 'request_id',
    req.request_number,
    req.year,
    req.created_at,
    rr.status,
    d.name as 'diagnosis'
FROM
    requests req
    join patient_case pc on req.patient_case_id = pc.id
    join patients p on pc.patient_id = p.id
    join referral_sources rs on pc.referral_source_id = rs.id
    join local_network_locations lnl on rs.parent_organisation_id = lnl.parent_id
    join referrer_department rd on req.referrer_department_id = rd.id
    join referrers ref on rd.referrer_id = ref.id
	join request_history h on h.request_id = req.id
    join request_report_view rr on rr.request_id = req.id
    join diagnoses d on rr.diagnosis_id = d.id
WHERE rr.status IN ('new','relapse')
    and p.dob IS NOT NULL
    and ( YEAR(CURRENT_DATE()) - YEAR(p.dob) -
            ( CASE WHEN MONTH(CURRENT_DATE) > MONTH(p.dob) THEN 0
                WHEN MONTH(CURRENT_DATE) < MONTH(p.dob) THEN 1
                WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(p.dob) THEN 1
                ELSE 0 END ) ) <= 25
    /* and rs.organisation_code like 'RR8%' # using local_network_locations now */
	and h.action = 'authorised'
	and DATE(h.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)

[testicular_breast_cns_biopsies]
SELECT
    CONCAT_WS('/', r.request_number, r.year - 2000),
    rr.biopsy_site,
    GROUP_CONCAT(DISTINCT(s.sample_code)),
    r.diagnosis
FROM authorised_reports_view r
	JOIN request_report_view rr on rr.request_id = r.id
	JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
		on rs.request_id = r.id
WHERE (
		rr.biopsy_site rlike 'testicular|brain|breast' OR
		rr.morphology  rlike 'testic(ular|le)|brain|breast|cerebral|cns'
	)
	and DATE(r.auth_date) > DATE_SUB(CURDATE(), INTERVAL ? DAY)
GROUP BY r.id
ORDER BY r.id

[new_remodlb_cases_alert]
/* discontinued - replaced by generic new_trial_cases_alert
SELECT
    CONCAT( 'H', r.`request_number`, '/', r.year - 2000 ),
    p.`last_name`,
    p.`first_name`,
 	DATE_FORMAT(p.`dob`,'%d/%m/%Y'),
    pc.`unit_number`,
    p.`nhs_number`,
    rs.`display_name`
FROM requests r
    JOIN patient_case pc on (r.`patient_case_id` = pc.id)
    JOIN patients p on (pc.`patient_id` = p.id)
    JOIN referral_sources rs on (pc.`referral_source_id` = rs.id)
    JOIN ( request_trial rt JOIN clinical_trials ct on (rt.`trial_id` = ct.id) )
   		on (rt.`request_id` = r.id)
WHERE DATE(r.created_at) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
	and ct.trial_name = 'SCTU REMoDL-B'
ORDER BY r.`year`, r.`request_number`
*/

[new_trial_cases_alert]
SELECT
    CONCAT( 'H', r.`request_number`, '/', r.year - 2000 ),
    p.`last_name`,
    p.`first_name`,
 	DATE_FORMAT(p.`dob`,'%d/%m/%Y'),
    pc.`unit_number`,
    p.`nhs_number`,
    GROUP_CONCAT(s.sample_code),
    rs.`display_name`
FROM requests r
    JOIN patient_case pc on (r.`patient_case_id` = pc.id)
    JOIN patients p on (pc.`patient_id` = p.id)
    JOIN referral_sources rs on (pc.`referral_source_id` = rs.id)
    JOIN ( request_trial rt JOIN clinical_trials ct on (rt.`trial_id` = ct.id) )
   		on (rt.`request_id` = r.id)
    JOIN ( request_specimen rs2 JOIN specimens s on rs2.specimen_id = s.id)
        on rs2.request_id = r.id
WHERE DATE(r.created_at) = DATE_SUB(CURRENT_DATE, INTERVAL ? DAY)
	and ct.trial_name = ?
GROUP BY r.id
ORDER BY r.`year`, r.`request_number`

[histopathology_referrals]
SELECT r.request_number, r.year
FROM requests r
	JOIN request_report_view rr on rr.request_id = r.id
	JOIN diagnoses d on rr.diagnosis_id = d.id
	JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'authorised' )
WHERE d.name IN ('Referred to histopathology', 'Epithelial thymoma')
	and DATE(rh.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)

[new_pnh_alert]
SELECT
    r1.`id` as 'request_id',
    r1.`request_number`,
    r1.`year`,
    r1.`created_at`,
    p.`last_name`,
    p.`first_name`,
    p.`dob`,
    p.`nhs_number`,
    pc.`unit_number`,
    d.name as 'diagnosis',
    rs.display_name as 'location',
    rr.status,
    r2.name as 'referrer'
FROM requests r1
    JOIN patient_case pc on (r1.`patient_case_id` = pc.id)
    JOIN patients p on (pc.`patient_id` = p.id)
    JOIN referral_sources rs on (pc.`referral_source_id` = rs.id)
    JOIN request_report_view rr on (rr.request_id = r1.id)
    JOIN diagnoses d on (rr.diagnosis_id = d.id)
	JOIN request_history rh on (rh.request_id = r1.id)
	JOIN referrer_department rd on (r1.referrer_department_id = rd.id)
	JOIN referrers r2 on (rd.referrer_id = r2.id)
WHERE
	d.`name` = 'PNH clone present'
	and rr.status = 'new'
	and rh.action = 'authorised'
	and DATE(rh.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)

[myelomaX_data_requests]
SELECT
    r.id,
	r.`request_number`,
	r.year,
	DATE_FORMAT(r.created_at, '%d.%b.%Y') as 'registered',
	p.`last_name`,
	p.`first_name`,
	pt.`trial_number`,
	s.description as 'presentation',
	fish.results_summary as 'FISH result',
	DATE_FORMAT(fish.time, '%d.%b.%Y') as 'FISH date',
	flow.results_summary as 'Flow result',
	DATE_FORMAT(flow.time, '%d.%b.%Y') as 'Flow date'
FROM requests r
	JOIN `patient_case` pc on (r.`patient_case_id` = pc.id)
	JOIN patients p on (pc.`patient_id` = p.id)
	LEFT JOIN ( `patient_trial` pt
            JOIN `clinical_trials` ct on (pt.trial_id = ct.id) )
		on (pt.`patient_id` = p.id and ct.`trial_name` = 'NCRI Myeloma X')
	JOIN `request_initial_screen` ris on (ris.`request_id` = r.id)
	JOIN screens s on (ris.`screen_id` = s.id)
	LEFT JOIN ( `request_result_summaries` fish
            JOIN lab_sections ls1 on (fish.`lab_section_id` = ls1.id) )
		on (fish.`request_id` = r.id and ls1.`section_name` = 'FISH')
	LEFT JOIN ( `request_result_summaries` flow
            JOIN lab_sections ls2 on (flow.`lab_section_id` = ls2.id) )
		on (flow.`request_id` = r.id and ls2.`section_name` = 'Flow cytometry')
WHERE s.description IN (
	/* 'Myeloma X - presentation', # HILIS 3 term */
	/* 'Myeloma X - follow-up', # HILIS 3 term */
	'Myeloma trial presentation',
	'Myeloma trial follow-up'
)
 and (
 	(
		MONTH(fish.time) = MONTH( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) ) and
		 YEAR(fish.time) =  YEAR( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) )
	)
 	or (
		MONTH(flow.time) = MONTH( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) ) and
		 YEAR(flow.time) =  YEAR( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) )
	)
 )
ORDER BY r.year, r.request_number

[resultable_fish_probe_names]
SELECT t2.field_label, t2.id
FROM lab_tests t2
	JOIN lab_sections t3 on (t2.`lab_section_id` = t3.id)
WHERE t3.`section_name` = 'FISH'
 	and t2.`has_results` = 'yes'

[myelomaX_fish_results]
SELECT
	t2.`field_label`,
	t1.result
FROM request_lab_test_results t1
	JOIN lab_tests t2 on (t1.lab_test_id = t2.id)
	JOIN lab_sections t3 on (t2.`lab_section_id` = t3.id)
 WHERE t3.`section_name` = 'FISH'
	and t1.request_id = ?

[get_reporter_from_request_id]
SELECT
	t2.last_name,
	t2.first_name
FROM
    request_history t1
    join users t2 on (t1.user_id = t2.id)
WHERE action = 'reported'
	and request_id = ?

[request_specimens]
SELECT
  t2.description
FROM
  request_specimen t1
  join specimens t2 on (t1.specimen_id = t2.id)
WHERE
  t1.request_id = ?

[dlbcl_or_burkitt_diagnosis]
SELECT
	rr.request_id,
	r.request_number,
	r.year
FROM requests r
	join request_report_view rr on (rr.request_id = r.id)
    join diagnoses d on (rr.diagnosis_id = d.id)
WHERE rr.updated_at = DATE_SUB(CURDATE(), INTERVAL ? DAY)
    AND d.name = 'DLBCL or Burkitt lymphoma - further tests pending'

[expire_user_accounts]
UPDATE users SET active = 'no'
WHERE last_login < DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH)
AND active = 'yes'

[lab_param_monitoring_data]
/* converted to RDBO method to allow placeholders in s.description field
SELECT
	DATE(r.`created_at`) as 'date',
    rs.`results_summary` as 'result'
FROM `requests` r
	JOIN `patient_case` pc on r.`patient_case_id` = pc.id
	JOIN `patients` p on pc.`patient_id` = p.id
	JOIN `request_initial_screen` ris on ris.`request_id` = r.id
	JOIN `screens` s on ris.`screen_id` = s.id
	JOIN `request_result_summaries` rs on rs.`request_id` = r.id
	JOIN `lab_sections` ls on rs.`lab_section_id` = ls.id
WHERE p.`id` = ?
	AND ls.`section_name` = ?
	AND s.`description` = ? # need IN (??)
ORDER BY r.id
*/

[national_code_unknown]
/* replaced by SQL::Abstract::More method
SELECT
    r1.`request_number`,
    r1.`year`,
    r1.`created_at`,
    rs.`display_name`,
    rs.`organisation_code`
FROM
    `referrer_department` rd
    JOIN `requests` r1  ON r1.`referrer_department_id` = rd.`id`
    JOIN `referrers` r2 ON rd.`referrer_id` = r2.`id`
    JOIN `patient_case` pc on r1.`patient_case_id` = pc.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    LEFT JOIN `request_trial` rt ON rt.`request_id` = r1.`id`
WHERE (
        r2.`national_code` like '%999998' OR
		rs.`organisation_code` IN ('X99999','V81999')
    )
    and r1.`created_at` >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
    and rt.`request_id` IS NULL
ORDER BY r1.year, r1.request_number
*/

[teaching_cases]
SELECT
    r.id,
	r.request_number,
	r.year,
	d.name as 'diagnosis',
	rr.status,
	DATE(rh.`time`) as 'auth_date'
FROM requests r
	JOIN request_report_view rr on rr.request_id = r.id
	JOIN diagnoses d on rr.diagnosis_id = d.id
	JOIN request_history rh on ( rh.request_id = r.id and rh.action = ? )
	JOIN request_option ro on ro.request_id = r.id
	JOIN additional_options ao on ro.option_id = ao.id
WHERE DATE(rh.`time`) >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
	AND ao.option_name = 'teaching'

[activity_data]
SELECT
    req.`id`,
    req.`request_number`,
    req.`year`,
	req.`created_at`,
    IF(HOUR(req.`created_at`) BETWEEN 8 and 17, 'N', 'Y') as 'on_call',
    p.`last_name`,
    p.`first_name`,
    p.`middle_name`,
    p.`nhs_number`,
    p.`gender`,
    p.`dob`,
	YEAR(req.`created_at`) - YEAR(p.`dob`)
		- (DATE_FORMAT(req.`created_at`, '00-%m-%d') < DATE_FORMAT(p.`dob`, '00-%m-%d'))
        AS 'age',
    pc.`unit_number`,
    src.`organisation_code`,
    src.`parent_organisation_id`,
    ref.`national_code` as 'referrer_code',
    rd.`hospital_department_code`,
    src.`display_name`,
    s1.`sample_code`,
    s1.`description` as 'sample_description',
    IF(rt.`request_id`,'ST','01') as 'category',
    s2.`description` as 'presentation',
    ct.trial_name,
    pt.trial_number
FROM `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `referral_sources` src on pc.`referral_source_id` = src.`id`
    JOIN `referrer_department` rd on req.`referrer_department_id` = rd.`id`
    JOIN `referrers` ref on rd.`referrer_id` = ref.`id`
    JOIN `request_specimen` rs on rs.`request_id` = req.`id`
    JOIN `specimens` s1 on rs.`specimen_id` = s1.`id`
    LEFT JOIN `request_initial_screen` ris JOIN `screens` s2 on (ris.`screen_id` = s2.`id`)
        on (ris.`request_id` = req.`id`)
    LEFT JOIN `request_trial` rt JOIN `clinical_trials` ct on (rt.trial_id = ct.id)
	 	on (rt.`request_id` = req.`id`)
	LEFT JOIN patient_trial pt ON pt.trial_id = rt.trial_id AND pt.patient_id = p.id
WHERE MONTH(req.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and req.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
ORDER BY req.`year`, req.`request_number`

[activity_patient_is_private]
/* replaced with activity_private_patients
SELECT 1
FROM request_option ro
    JOIN additional_options ao on ro.option_id = ao.id
WHERE ro.request_id = ?
    and ao.option_name = 'private'
*/

[activity_private_patients]
SELECT r.id, 1
FROM requests r
    JOIN ( request_option ro JOIN additional_options ao on ro.option_id = ao.id )
        on ro.request_id = r.id
WHERE MONTH(r.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and r.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
    and ao.option_name = 'private'

[activity_request_has_marrow]
/* replaced with activity_bone_marrow_requests
SELECT 1
FROM requests r
	JOIN ( request_specimen rs join specimens s on rs.specimen_id = s.id )
		on rs.request_id = r.id
WHERE rs.request_id = ? and s.sample_code IN ('BMA', 'BMAT')
*/

[activity_request_lab_tests]
SELECT lt.test_name
FROM request_lab_test_status ts
    JOIN lab_tests lt on ts.lab_test_id = lt.id
WHERE ts.request_id = ?

[activity_bone_marrow_requests]
SELECT r.id, 1
FROM requests r
	JOIN ( request_specimen rs join specimens s on rs.specimen_id = s.id )
		on rs.request_id = r.id
WHERE MONTH(r.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and r.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
    and s.sample_code IN ('BMA', 'BMAT')

[activity_molecular_sequencing]
/* discontinued - replaced with molecular_sequencing_tests hashref map */
SELECT DISTINCT(r.id), 1
FROM requests r
	JOIN ( request_lab_test_status lts JOIN lab_tests lt on lts.lab_test_id = lt.id )
		on lts.request_id = r.id
	JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
		on rs.request_id = r.id
WHERE MONTH(r.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and r.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and s.sample_code = 'PB'
	and lt.test_name IN (??)

[trial_cases]
SELECT
	r.request_number,
	r.year,
	p.last_name,
	p.first_name,
	p.dob,
	ref_src.display_name as 'location',
	ct.trial_name,
	s1.description as 'presentation',
	DATE_FORMAT(r.created_at, '%d.%b.%Y') as 'registered',
	GROUP_CONCAT(s2.sample_code) as 'specimen'
FROM requests r
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN patients p on pc.patient_id = p.id
	JOIN referral_sources ref_src on pc.referral_source_id = ref_src.id
	JOIN request_initial_screen ris on (ris.request_id = r.id)
	JOIN screens s1 on (ris.screen_id = s1.id)
	JOIN request_trial rt on (rt.request_id = r.id)
	JOIN clinical_trials ct on (rt.trial_id = ct.id)
	JOIN ( request_specimen rs
		JOIN specimens s2 on (rs.specimen_id = s2.id) ) on (rs.request_id = r.id)
WHERE ct.trial_name NOT IN ('Roche Gallium')
    and ct.trial_name NOT LIKE 'HTG %'
	and DATE(r.created_at) BETWEEN DATE_SUB(CURDATE(), INTERVAL ? MONTH)
        and DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY r.id
ORDER BY trial_name,r.id

[ncg_pnh]
/*
 * // replaced by SQLA 03/2019
SELECT s2.sample_code, COUNT(s2.sample_code)
FROM requests r
	JOIN ( request_initial_screen ris JOIN screens s1 on ris.screen_id = s1.id )
		on ris.request_id = r.id
	JOIN ( request_specimen rs JOIN specimens s2 on rs.specimen_id = s2.id )
		on rs.request_id = r.id
	JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'screened' )
WHERE s1.description like 'NCG PNH %'
	and s2.sample_code IN ('PB', 'BMA', 'BMAT')
	and DATE(rh.time) BETWEEN DATE_SUB(CURDATE(), INTERVAL ? MONTH)
        and DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY s2.id
*/

[ncg_hts]
/*
 * // replaced by SQLA 03/2019
SELECT COUNT(*)
FROM requests r
	JOIN ( request_initial_screen ris JOIN screens s1 on ris.screen_id = s1.id )
		on ris.request_id = r.id
	JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'screened' )
	JOIN ( request_lab_test_status ts JOIN lab_tests lt on ts.lab_test_id = lt.id )
		on ts.request_id = r.id
WHERE s1.description like 'NCG PNH %'
	and lt.test_name = 'hts_myeloid'
	and DATE(rh.time) BETWEEN DATE_SUB(CURDATE(), INTERVAL ? MONTH)
        and DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY r.id /* don't care about sample type - only whether test is done */
*/

[outreach_patients]
/* replaced with SQL::Abstract::More query
SELECT
	p.last_name,
	p.first_name,
	p.dob,
	pc.unit_number,
	p.nhs_number,
	DATE(r.created_at),
	rs1.display_name,
	s.sample_code
FROM requests r
	JOIN ( patient_case pc JOIN patients p on (pc.patient_id = p.id) )
		on (r.patient_case_id = pc.id)
	JOIN referral_sources rs1 on (pc.referral_source_id = rs1.id)
	JOIN ( request_specimen rs2 JOIN specimens s on (rs2.specimen_id = s.id) )
		on (rs2.request_id = r.id)
	JOIN (request_initial_screen ris JOIN screens s2 on ris.screen_id = s2.id )
		on ris.request_id = r.id
WHERE s2.description RLIKE 'outreach'
	and s.sample_code <> 'CMP'
    and YEAR(r.created_at) = YEAR(DATE_SUB(CURDATE(), INTERVAL ? MONTH))
	and MONTH(r.created_at) = MONTH(DATE_SUB(CURDATE(), INTERVAL ? MONTH))
ORDER BY r.created_at
/*

[_new_referrers]
/* discontinued - replaced by recent_referrers, seen_referrers & new_referrers
SELECT
    rd.`id` as 'referrer_department_id',
    r.`name`,
    r.`national_code`,
    hd.`display_name` as 'department',
    po.`description` as 'organisation',
    po.`parent_code`
FROM `referrer_department` rd
    JOIN `referrers` r on rd.`referrer_id` = r.`id`
    JOIN `hospital_departments` hd on rd.`hospital_department_code` = hd.`id`
    JOIN `parent_organisations` po on rd.`parent_organisation_id` = po.`id`
WHERE rd.`id` IN (
    SELECT DISTINCT(rd.`id`)
    FROM `requests` req
        JOIN `referrer_department` rd on req.`referrer_department_id` = rd.`id`
    WHERE req.`created_at` >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
)
*/

[recent_referrers]
SELECT DISTINCT(r2.national_code)
FROM requests r1
    JOIN referrer_department rd on r1.referrer_department_id = rd.id
    JOIN referrers r2 on rd.referrer_id = r2.id
    LEFT JOIN request_history rh on ( rh.request_id = r1.id
        and rh.`action` REGEXP 'amended referrer' )
WHERE DATE(r1.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
    OR DATE(rh.time) >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)

[referrer_department_map]
/* discontinued - replaced by recent_referrers, seen_referrers & new_referrers
SELECT DISTINCT(rd.`id`), 1
FROM `requests` req
    JOIN `referrer_department` rd on req.`referrer_department_id` = rd.`id`
WHERE req.`created_at` < DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
*/

[seen_referrers]
SELECT 1 FROM requests r1
    JOIN referrer_department rd on r1.referrer_department_id = rd.id
    JOIN referrers r2 on rd.referrer_id = r2.id
WHERE r2.national_code = ?
    AND DATE(r1.created_at) < DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)

[new_referrers]
SELECT
    r.`name`,
    r.`national_code`,
    hd.`display_name` as 'department',
    po.`description` as 'organisation',
    po.`parent_code`
FROM `referrer_department` rd
    JOIN `referrers` r on rd.`referrer_id` = r.`id`
    JOIN `hospital_departments` hd on rd.`hospital_department_code` = hd.`id`
    JOIN `parent_organisations` po on rd.`parent_organisation_id` = po.`id`
WHERE r.national_code IN (??)

[_new_referrer]
/* replaced below */
SELECT
    ref.`name`,
    ref.`national_code`,
    hd.`display_name` as 'department',
	rs.`display_name` as 'location',
	rs.`organisation_code`
FROM requests r
    JOIN patient_case pc ON r.patient_case_id = pc.id
    JOIN referral_sources rs ON pc.referral_source_id = rs.id
    JOIN referrer_department rd ON r.referrer_department_id = rd.id
    JOIN referrers ref ON rd.referrer_id = ref.id
    JOIN hospital_departments hd on rd.hospital_department_code = hd.id
WHERE ref.national_code = ?

[new_referrer]
SELECT
    ref.`name`,
    ref.`national_code`,
    hd.`display_name` as 'department',
	rs.display_name as 'location',
	rs.organisation_code
  FROM requests r
  JOIN patient_case pc ON (r.patient_case_id = pc.id)
  JOIN referral_sources rs ON (pc.referral_source_id = rs.id)
  JOIN referrer_department rd ON (r.referrer_department_id = rd.id)
  JOIN referrers ref ON (rd.referrer_id = ref.id)
  JOIN hospital_departments hd on rd.`hospital_department_code` = hd.`id`
WHERE ref.national_code = ?

[new_location]
SELECT rs.display_name, rs.organisation_code, rt.description
FROM referral_sources rs
	JOIN parent_organisations po on rs.parent_organisation_id = po.id
	JOIN referral_types rt on rs.referral_type_id = rt.id
WHERE rs.organisation_code = ?

[new_national_code]
SELECT national_code, type
FROM new_national_code
WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL ? DAY)

[hodgkin]
SELECT
    p.`last_name`,
    p.`first_name`,
    p.`dob`,
    pc.`unit_number`,
    p.`nhs_number`,
    rs.`display_name` as 'location',
    d.`name` as 'diagnosis'
FROM `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    JOIN `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
    JOIN `request_report_view` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
    JOIN `icdo_sub_category` ic on ic.`icdo3` = d.`icdo3`
    JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
    JOIN `request_history` rh on rh.`request_id` = req.`id`
WHERE dc.`description` = 'hodgkin'
    and rh.`action` = 'authorised'
    and rr.`status` = 'new'
    and po.`parent_code` IN ('RAE','RR8')
    and DATE(rh.`time`) >= DATE_SUB(NOW(), INTERVAL ? DAY)

[ex-hodgkin]
SELECT
    p.`last_name`,
    p.`first_name`,
    p.`dob`,
    pc.`unit_number`,
    p.`nhs_number`,
    rs.`display_name` as 'location',
    d.`name` as 'diagnosis'
FROM `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `request_report_view` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
    JOIN `icdo_sub_category` ic on ic.`icdo3` = d.`icdo3`
    JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
WHERE req.`id` IN (
    SELECT req.`id`
    FROM `requests` req
        JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
        JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
        JOIN `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
        JOIN `request_report_view` rr on rr.`request_id` = req.`id`
        JOIN `request_diagnosis_history` rh on rh.`request_id` = req.`id`
        JOIN `diagnoses` d on rh.`diagnosis_id` = d.`id`
        JOIN `icdo_sub_category` ic on ic.`icdo3` = d.`icdo3`
    	JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
    WHERE dc.`description` = 'hodgkin'
          and rr.`status` = 'new'
          and po.`parent_code` IN ('RAE','RR8')
          and rh.`time` >= DATE_SUB(NOW(), INTERVAL ? DAY)
    )
    and dc.`description` <> 'hodgkin'

[mds_diagnoses]
SELECT
    CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
    UPPER(p.`last_name`),
    CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
    pc.`unit_number`,
    p.`nhs_number`,
    p.`dob`,
    rs.`display_name`,
    d.`name`,
    DATE(rh.`time`),
    IF(rr.`status` = 'default', 'follow-up', rr.`status`)
FROM `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `request_report_view` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
    JOIN `icdo_sub_category` ic on ic.`icdo3` = d.`icdo3`
    JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
    JOIN `request_history` rh on rh.`request_id` = req.`id`
WHERE dc.`description` = 'Myelodysplastic'
    and rh.`action` = 'authorised'
    and rh.`time` >= DATE_SUB(NOW(), INTERVAL ? DAY)
ORDER BY rh.`time`, req.`year`, req.`request_number`

[myeloid_diagnoses]
SELECT
    CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
    UPPER(p.`last_name`),
    CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
    pc.`unit_number`,
    p.`nhs_number`,
    p.`dob`,
    rs.`display_name`,
    d.`name`,
    DATE(rh.`time`),
    IF(rr.`status` = 'default', 'follow-up', rr.`status`)
FROM `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    JOIN `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `request_report_view` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
    JOIN `icdo_sub_category` ic on ic.`icdo3` = d.`icdo3`
    JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
    JOIN `request_history` rh on rh.`request_id` = req.`id`
WHERE dc.`description` IN ('Myelodysplastic','Myeloproliferative')
    and rh.`action` = 'authorised'
    and po.`parent_code` IN ('RR8','RWY')
    and MONTH(rh.`time`) = ? and YEAR(rh.`time`) = ?
ORDER BY rh.`time`, req.`year`, req.`request_number`

[register_log]
SELECT u.id, u.username, COUNT(*) as f
FROM request_history rh
	JOIN users u on (rh.user_id = u.id)
WHERE `action` = 'registered'
	AND YEAR(rh.time) = ? AND MONTH(rh.time) = ?
GROUP BY u.username
ORDER BY f DESC

[followup_requests_requiring]
SELECT r.id, r.request_number, r.year, p.last_name, p.first_name, p.dob,
    p.nhs_number, d.name as 'diagnosis', s2.description as 'presentation',
    pc.patient_id, GROUP_CONCAT(s.sample_code) as 'specimen', rs.display_name
    as 'location', date(rh.time) as 'authorised'
FROM requests r
    JOIN patient_case pc on r.patient_case_id = pc.id
    JOIN referral_sources rs on pc.referral_source_id = rs.id
    JOIN patients p on pc.patient_id = p.id
    JOIN request_history rh on rh.request_id = r.id
    JOIN ( request_specimen rs2 JOIN specimens s on rs2.specimen_id = s.id )
        on rs2.request_id = r.id
    JOIN ( request_initial_screen ris JOIN screens s2 on ris.screen_id = s2.id )
        on ris.request_id = r.id
    LEFT JOIN request_trial rt on rt.request_id = r.id
    JOIN ( request_report_detail rr join diagnoses d on rr.diagnosis_id = d.id )
        on rr.request_id = r.id
WHERE rt.request_id is null and
    rh.action = 'authorised' and
    year(rh.time) = ? and month(rh.time) = ? and
    d.name in (??)
GROUP BY r.id
ORDER BY d.name, r.id

[followup_requests_followups]
SELECT r.id, r.request_number, r.year, date(r.created_at) as 'registered',
    d.name as 'diagnosis', GROUP_CONCAT(s.sample_code) as 'specimen',
    s2.description as 'presentation', rs.display_name as 'location'
FROM requests r
    JOIN patient_case pc on r.patient_case_id = pc.id
    JOIN referral_sources rs on pc.referral_source_id = rs.id
    LEFT JOIN request_trial rt on rt.request_id = r.id
    JOIN ( request_specimen rs2 JOIN specimens s on rs2.specimen_id = s.id )
        on rs2.request_id = r.id
    JOIN ( request_initial_screen ris JOIN screens s2 on ris.screen_id = s2.id )
        on ris.request_id = r.id
    JOIN ( request_report_detail rr join diagnoses d on rr.diagnosis_id = d.id )
        on rr.request_id = r.id
WHERE rt.request_id is null and
    pc.patient_id = ? and
    date(r.created_at) > ?
GROUP BY r.id
ORDER BY r.id

[error_code_A_requests]
SELECT DISTINCT(request_id)
FROM request_history
WHERE YEAR(`time`) = ? and MONTH(`time`) = ?
    and (`action` LIKE 'deleted%' OR `action` = 'recorded error code A' )

[error_code_A_data]
/* register_and_error_log query - not used anymore
SELECT request_id, `action`, user_id
FROM request_history
WHERE YEAR(`time`) = ? and MONTH(`time`) = ?
    and `action` regexp '^(deleted|modified)'
    and request_id IN (%PARAMS%)
*/

[error_codes]
SELECT UCASE(t2.code), t2.description, count(*)
FROM request_error_code t1
	JOIN error_codes t2 on (t1.error_code_id = t2.id)
WHERE YEAR(t1.`time`) = ? and MONTH(t1.`time`) = ?
GROUP BY t2.id
ORDER BY t2.code

[error_log]
/* # discontinued after hilis3
SELECT
    r.id as 'request_id',
	r.request_number,
	r.year,
	UCASE(u.username) as 'modifier',
	rh1.action,
	rh2.action as 'err_code_action',
	rh2.id as 'err_history_id',
	rh1.time
FROM requests r
	JOIN request_history rh1 on (rh1.request_id = r.id)
	JOIN request_history rh2 on (
        rh1.time = rh2.time
        and rh1.request_id = rh2.request_id
        and rh2.`action` like 'recorded error code %')
	JOIN users u on (rh1.user_id = u.id)
WHERE DATE(rh1.`time`) >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
	and (
        rh1.action regexp 'modified (%REGEXP%)'
		or rh1.action = 'deleted' )
*/

[request_history_error_codes]
SELECT
    r.id as 'request_id',
	r.request_number,
	r.year,
	UCASE(u.username) as 'modifier',
    rh.id as 'request_history_id',
	rh.action,
	rh.time
FROM requests r
	JOIN request_history rh on (rh.request_id = r.id)
	JOIN users u on (rh.user_id = u.id)
WHERE DATE(rh.`time`) >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
    and rh.`action` like 'recorded error code %'

[error_log_patient_edits]
/* # not using
SELECT *
FROM patient_edits pe
    JOIN users u on (pe.user_id = u.id)
    JOIN error_codes ec on (pe.error_code_id = ec.id)
WHERE DATE(pe.`time`) >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
*/

[registration_user_map]
SELECT user_id, COUNT(*)
FROM request_history
WHERE `action` = 'registered'
    and request_id IN (??)
GROUP BY user_id

[null_nhs_number]
SELECT
    CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
    UPPER(p.`last_name`),
    CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
    p.`dob`,
    pc.`unit_number`,
    DATE(p.`created_at`),
    rs.`display_name`
FROM `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    JOIN `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
    LEFT JOIN ( `request_trial` rt
        JOIN `clinical_trials` ct on rt.`trial_id` = ct.`id` )
    on rt.`request_id` = req.`id`
WHERE YEARWEEK(p.`created_at`, 1) = YEARWEEK(CURRENT_DATE(), 1)
  and p.`nhs_number` IS NULL
  /* and po.`parent_code` = 'RR8' */
  and p.`first_name` NOT IN ('LGI','PIN')
  and ( ct.`trial_name` IS NULL OR ct.`trial_name` NOT IN (??) )

[all_previous_lab_tests_and_results]
SELECT
    t1.id,
    t1.year,
    t1.request_number,
    DATE(t3.time) as 'test_date',
    t3.lab_test_id,
    t4.field_label,
    t5.section_name,
    t7.result
FROM
    requests t1
    JOIN patient_case t2 ON t1.patient_case_id = t2.id
    JOIN request_lab_test_status t3 ON t3.request_id = t1.id
    JOIN lab_tests t4 ON t3.lab_test_id = t4.id
    JOIN lab_sections t5 ON t4.lab_section_id = t5.id
    JOIN lab_test_status_options t6 ON t3.status_option_id = t6.id
    LEFT JOIN ( request_lab_test_results t7 JOIN lab_tests t8
        ON t7.lab_test_id = t8.id ) ON t7.request_id = t1.id AND t8.id = t4.id
WHERE
  t2.patient_id = ? AND
  t6.description <> 'delete'
ORDER BY t1.year, t1.request_number

[cytogenetics_failed]
SELECT
    CONCAT_WS('/', r.request_number, r.year - 2000),
    d.name, DATE(rrs.time), rrs.results_summary
FROM requests r
	JOIN ( request_result_summaries rrs JOIN lab_sections ls
			on rrs.lab_section_id = ls.id and ls.section_name = 'cytogenetics')
		on rrs.request_id = r.id
	LEFT JOIN ( request_report_view rrv JOIN diagnoses d on rrv.diagnosis_id = d.id )
		on rrv.request_id = r.id
WHERE rrs.results_summary rlike 'failed'
	and DATE(rrs.`time`) > DATE_SUB(CURDATE(), INTERVAL ? DAY)
ORDER BY rrs.`time`

[inadequate_unfixed_specimen]
SELECT
    CONCAT_WS('/', t1.request_number, t1.year - 2000),
    GROUP_CONCAT(DISTINCT(s.sample_code)),
    t1.auth_date
FROM authorised_reports_view t1
	JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
		on rs.request_id = t1.id
WHERE t1.diagnosis = 'inadequate sample'
	and auth_date >= date_sub(current_date(), INTERVAL ? DAY)
	and s.sample_code like '%U'
GROUP BY t1.id

[cases_registered]
SELECT
    CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
    UPPER(p.`last_name`),
    CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
    p.`dob`,
    DATE(req.`created_at`),
    p.`nhs_number`
FROM `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
WHERE req.`created_at` > DATE_SUB(CURDATE(), INTERVAL ? DAY)
ORDER BY req.`year`, req.`request_number`

[time_to_authorisation_by_specimen]
SELECT
    DATE(req.`created_at`) as 'registered',
    DATE(rh.`time`) as 'authorised',
    s.`sample_code`
FROM `requests` req
    JOIN `request_history` rh on rh.`request_id` = req.`id`
    JOIN `request_specimen` rs on rs.`request_id` = req.`id`
    JOIN `specimens` s on rs.`specimen_id` = s.`id`
WHERE req.created_at >= date_sub(CURRENT_DATE(), INTERVAL ? MONTH)
    and rh.`action` = 'authorised'

[overdue_for_authorisation]
SELECT
    req.`id`,
    CONCAT('H', req.`request_number`, '/', req.`year` - 2000) as 'lab_number',
    DATE(req.`created_at`) as 'registered',
    CONCAT( UPPER(p.`last_name`), ', ',
        CONCAT( UPPER(SUBSTRING(p.`first_name`, 1, 1) ),
            LOWER( SUBSTRING(p.`first_name` FROM 2) ) ) ) as 'name',
    src.`display_name` as 'location',
    s1.`sample_code` as 'specimen',
    s2.`description`,
    IF(rt.`request_id`, 1, 0) AS 'trial_case'
FROM
  requests `req`
  JOIN `status_options` so ON (req.`status_option_id` = so.`id`)
  JOIN `patient_case` pc ON (req.`patient_case_id` = pc.`id`)
  JOIN `patients` p ON (pc.`patient_id` = p.`id`)
  JOIN `referral_sources` src ON (pc.`referral_source_id` = src.`id`)
  JOIN `request_specimen` rs ON (rs.`request_id` = req.`id`)
  JOIN `specimens` s1 ON (rs.`specimen_id` = s1.`id`)
  JOIN `request_initial_screen` ris ON (ris.`request_id` = req.`id`)
  JOIN `screens` s2 ON (ris.`screen_id` = s2.`id`)
  LEFT JOIN `request_trial` rt ON (rt.`request_id` = req.`id`)
WHERE
  so.`description` = 'reported'
ORDER BY req.`year`, req.`request_number`

[unreported_requests]
SELECT
    CONCAT(t1.request_number, '/', t1.year - 2000) as 'labno',
    DATE(t1.created_at) as 'registered',
    t5.display_name as 'location',
	t7.description as 'presentation',
    IF(t8.request_id, 1, NULL) AS 'trial_case',
    DATEDIFF(CURDATE(), DATE(t1.created_at)) as 'delta'
FROM
  requests t1
  JOIN status_options t2 ON (t1.status_option_id = t2.id)
  JOIN patient_case t3 ON (t1.patient_case_id = t3.id)
  JOIN referral_sources t5 ON (t3.referral_source_id = t5.id)
  JOIN request_initial_screen t6 ON (t1.id = t6.request_id)
  JOIN screens t7 ON (t6.screen_id = t7.id)
  LEFT JOIN request_trial t8 ON (t8.request_id = t1.id)
WHERE
  t2.description = 'screened'
  and t1.created_at <= date_sub(CURDATE(), INTERVAL 7 DAY)
GROUP BY t1.id
ORDER BY t1.created_at, request_number

[unauthorised_requests]
SELECT
    CONCAT(r.`request_number`, '/', r.`year` - 2000) as 'labno',
    rh.`time` as 'reported',
    d.`name` as 'diagnosis',
    s.`description` as 'screened_as',
    IF(rt.`request_id`, 1, 0) AS 'trial_case'
FROM `requests` r
    JOIN `request_history` rh ON rh.`request_id` = r.`id`
    JOIN `status_options` so on r.`status_option_id` = so.`id`
    JOIN `request_report_view` rr on rr.`request_id` = r.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
    JOIN `request_initial_screen` ris on ris.`request_id` = r.`id`
    JOIN `screens` s ON ris.`screen_id` = s.`id`
    LEFT JOIN `request_trial` rt ON rt.`request_id` = r.`id`
WHERE
    so.`description` = 'reported'
    and rh.`action` = 'reported'
ORDER BY rh.`time`

[unscreened_requests]
SELECT
    CONCAT(r.`request_number`, '/', r.`year` - 2000) as 'labno',
    DATE(r.`created_at`) as 'registered',
    GROUP_CONCAT(s2.sample_code) as 'specimen',
    s1.display_name as 'location'
FROM `requests` r
    JOIN patient_case pc on r.patient_case_id = pc.id
    JOIN referral_sources s1 on pc.referral_source_id = s1.id
    JOIN request_specimen rs on rs.request_id = r.id
    JOIN specimens s2 on rs.specimen_id = s2.id
    LEFT JOIN `request_initial_screen` ris on ris.`request_id` = r.`id`
WHERE ris.request_id IS NULL
GROUP BY r.id

[results_and_ppm]
SELECT
    req.`id` as 'request_id',
    rs.`organisation_code`, /* ppm only */
	p.`gender` as 'sex',
	p.`last_name`,
	p.`first_name`,
	p.`nhs_number`,
	pc.`unit_number`,
	DATE_FORMAT(p.`dob`, '%d.%m.%Y') as 'dob',
	p.`id` as 'patient_id', /* ppm only */
    ref.`national_code` as 'referrer_code',
    ref.`name` as 'referrer',
	CONCAT('H',req.`request_number`,'/', req.`year` - 2000) as 'labno',
	rr.`clinical_details`,
    rr.`gross_description`,
	rr.`specimen_quality`,
	rr.`morphology`,
	rr.`biopsy_site`,
	rr.`comment`,
    d1.`name` as 'diagnosis',
    d2.`name` as 'diagnosis2'
FROM
    `requests` req
    JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    JOIN `referrer_department` rd on req.`referrer_department_id` = rd.`id`
    JOIN `referrers` ref on rd.`referrer_id` = ref.`id`
    JOIN ( `request_report_view` rr JOIN `diagnoses` d1 on rr.`diagnosis_id`
        = d1.`id` ) on rr.`request_id` = req.`id`
    LEFT JOIN ( `request_secondary_diagnosis` rsd JOIN `diagnoses` d2 on
        rsd.`secondary_diagnosis_id` = d2.`id` ) on rsd.`request_id` = req.`id`
WHERE req.`id` IN (??)

[results_and_ppm_request_ids]
SELECT DISTINCT(r.`id`)
FROM requests r
    JOIN `patient_case` pc on r.`patient_case_id` = pc.`id`
    JOIN `patients` p on pc.`patient_id` = p.`id`
    JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
    JOIN `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
    JOIN `request_history` rh on rh.`request_id` = r.`id`
    JOIN `request_initial_screen` ris on ris.`request_id` = r.`id`
    JOIN `screens` s on ris.`screen_id` = s.`id`
    LEFT JOIN `request_diagnosis_history` rdh on rdh.`request_id` = r.`id`
    LEFT JOIN `request_result_summaries` rrs on rrs.`request_id` = r.`id`

[incomplete_requests_request_ids]
/* moved to SQLA
SELECT DISTINCT(r.`id`)
FROM `requests` r
    JOIN `status_options` so ON (r.`status_option_id` = so.`id`)
    JOIN ( `request_initial_screen` ris JOIN `screens` s ON ris.`screen_id` = s.`id`)
	 	ON ris.`request_id` = r.`id`
    JOIN ( `request_result_summaries` rrs JOIN `lab_sections` ls
        ON rrs.`lab_section_id` = ls.`id`) ON rrs.`request_id` = r.`id`
    JOIN ( request_report_detail rrd JOIN diagnoses d on rrd.diagnosis_id = d.id )
    	ON rrd.request_id = r.id
WHERE (
        ( so.`description` = 'authorised'
            and ls.`section_name` IN ('Molecular','Cytogenetics','FISH') )
        OR (
          so.`description` = 'complete' and d.name RLIKE 'awaiting final (diagnosis|review)'
        )
    ) AND s.`description` NOT IN (??)
ORDER BY r.`year`, r.`request_number`
*/

[incomplete_requests_request_report]
/* // moved to SQLA
SELECT r.request_number, r.year, d.name as 'diagnosis',
    max(case when rs.action = 'reported' then rs.username end) as 'report_by',
    max(case when rs.action = 'reported' then rs.time end) as 'report_date',
    max(case when rs.action = 'authorised' then rs.username end) as 'auth_by',
    max(case when rs.action = 'authorised' then rs.time end) as 'auth_date'
FROM requests r
    JOIN request_status_view rs on rs.request_id = r.id
    JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
        on rr.request_id = r.id
WHERE r.id = ?
*/

[incomplete_requests_clinical_trial]
/* // moved to SQLA
SELECT ct.trial_name
FROM request_trial rt
	JOIN clinical_trials ct on rt.trial_id = ct.id
WHERE rt.request_id = ?
*/

[incomplete_requests_initial_screen]
/* // moved to SQLA
SELECT s.description
FROM request_initial_screen ris
    JOIN screens s on ris.screen_id = s.id
WHERE ris.request_id = ?
*/

[incomplete_requests_results_summary]
/* // moved to SQLA

SELECT ls.section_name, rrs.time
FROM request_result_summaries rrs
    JOIN lab_sections ls on rrs.lab_section_id = ls.id
WHERE rrs.request_id = ?
*/

[incomplete_requests_specimen]
/* // moved to SQLA
SELECT sample_code
FROM request_specimen rs
    JOIN specimens s on	rs.specimen_id = s.id
WHERE rs.request_id = ?
*/

[incomplete_requests_lab_tests]
/* // moved to SQLA
SELECT lt.test_name, so.description as 'status'
FROM requests r
	JOIN request_lab_test_status rs on rs.request_id = r.id
	JOIN lab_tests lt on rs.lab_test_id = lt.id
	JOIN lab_test_status_options so on rs.status_option_id = so.id
WHERE r.id = ?
*/

[authorised_requests]
/* // replaced by SQLA
SELECT t1.id, t1.updated_at
FROM requests t1
    JOIN status_options t2 ON (t1.status_option_id = t2.id)
WHERE t2.description = 'authorised'
    and t1.id NOT IN (??)
*/

[unsent_diagnosis_status_alerts]
/* sending all diagnosis_status_alerts now
SELECT
	r.id as 'request_id',
	rs.id as 'ref_src_id',
	rs.parent_organisation_id
FROM requests r
	JOIN patient_case pc on (r.patient_case_id = pc.id)
	JOIN referral_sources rs on (pc.referral_source_id = rs.id)
	JOIN request_report rr on (rr.request_id = r.id)
	JOIN request_history rh1 on (rh1.request_id = r.id and rh1.`action` = 'authorised')
	LEFT JOIN request_history rh2
		on (rh2.request_id = r.id and rh2.action like 'e-mailed diagnosis status alert%' )
WHERE rr.`status` IN ('new','relapse')
	and DATE(rh1.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)
	and rh2.request_id IS NULL
*/

[diagnosis_status_alerts]
SELECT
	r.id as 'request_id',
	rs.id as 'ref_src_id',
	rs.parent_organisation_id
FROM requests r
	JOIN patient_case pc on (r.patient_case_id = pc.id)
	JOIN referral_sources rs on (pc.referral_source_id = rs.id)
	JOIN request_report_view rr on (rr.request_id = r.id)
	JOIN request_history rh on (rh.request_id = r.id and rh.`action` = 'authorised')
WHERE rr.`status` IN ('new','relapse')
	and DATE(rh.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)

[potential_new_diagnosis]
SELECT
	r.id as 'request_id',
    date(r.created_at) as 'registered',
    pc.patient_id,
	rs.id as 'ref_src_id',
	rs.parent_organisation_id
FROM requests r
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN referral_sources rs on pc.referral_source_id = rs.id
	JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
        on rr.request_id = r.id
    JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
        on ris.request_id = r.id
	JOIN request_history rh on (rh.request_id = r.id and rh.`action` = 'authorised')
WHERE d.icdo3 <> '9765/1' /* has icdo3 but not mgus */
    and s.description NOT RLIKE 'chimerism|pnh|trial|outreach|hiv|rituximab'
    and rr.status = 'default'
    and DATE(rh.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)

[nycris_data]
SELECT
    r.id as 'request_id',
    DATE(r.created_at) as 'request_date',
	p.`last_name`,
	p.`first_name`,
	p.`middle_name`,
	p.`nhs_number`,
	pc.`unit_number`,
	p.`dob`,
	p.`gender`,
    r2.`national_code` as 'referrer_code',
    r2.`name` as 'referrer',
	CONCAT('H',r.`request_number`,'/', r.`year` - 2000) as 'labno',
    rs2.`display_name` as 'location',
    rs2.`organisation_code`,
	rr.`clinical_details`,
    rr.`gross_description`,
	rr.`specimen_quality`,
	rr.`comment`,
    d.`name` as 'diagnosis',
    d.`icdo3`,
    DATE(rh.time) as 'authorisation_date'
FROM requests r
    JOIN request_report_view rr on (rr.request_id = r.id)
    JOIN diagnoses d on (rr.diagnosis_id = d.id)
    JOIN request_history rh on (rh.request_id = r.id and rh.action = 'reported')
	JOIN patient_case pc on (r.patient_case_id = pc.id)
	JOIN patients p on (pc.patient_id = p.id)
    JOIN referrer_department rd on (r.referrer_department_id = rd.id)
    JOIN referrers r2 on (rd.referrer_id = r2.id)
	JOIN referral_sources rs2 on (pc.referral_source_id = rs2.id)
WHERE r.id in (??)

[_nycris_new_diagnosis_request_ids]
/* replaced 20/11/2015
SELECT r.id
FROM requests r
    JOIN referrer_department rd on (r.referrer_department_id = rd.id)
    JOIN request_report_view rr on (rr.request_id = r.id)
    JOIN request_history rh on (rh.request_id = r.id)
WHERE rr.status = 'new'
	and rh.action = 'authorised'
    and MONTH(rh.`time`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
    and  YEAR(rh.`time`) =  YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
    and rd.parent_organisation_id in (
	 	SELECT parent_id FROM local_network_locations
	)
*/
[nycris_new_diagnosis_request_ids]
SELECT r.id
FROM requests r
	JOIN referrer_department rd on (r.referrer_department_id = rd.id)
	JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
		on rr.request_id = r.id
	JOIN request_history rh on (rh.request_id = r.id)
	JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
		on ris.request_id = r.id
WHERE ( d.icdo3 NOT IN ('9765/1') or ( d.name = 'see comments' and rr.status = 'new' ) )
	and rh.action = 'authorised'
	and MONTH(rh.`time`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and  YEAR(rh.`time`) =  YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and rd.parent_organisation_id in (
		SELECT parent_id FROM local_network_locations
	)
	and s.description NOT IN ( 'Follow-up CML (PB)', 'Outreach' )

[_nycris_revised_diagnosis_request_ids]
/* replaced 20/11/2015
SELECT DISTINCT(r.id)
FROM requests r
    JOIN referrer_department rd on (r.referrer_department_id = rd.id)
    JOIN request_report_view rr on (rr.request_id = r.id)
    JOIN request_history rh on (rh.request_id = r.id)
    JOIN request_diagnosis_history rdh on (rdh.request_id = r.id)
WHERE rr.status = 'new'
	and rh.action = 'authorised'
	and MONTH(rdh.`time`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
    and	 YEAR(rdh.`time`) =  YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
    and rd.parent_organisation_id in (
	 	SELECT parent_id FROM local_network_locations
	)
*/
[nycris_revised_diagnosis_request_ids]
SELECT DISTINCT(r.id)
FROM requests r
	JOIN referrer_department rd on (r.referrer_department_id = rd.id)
	JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
		on rr.request_id = r.id
	JOIN request_history rh on (rh.request_id = r.id)
	JOIN request_diagnosis_history rdh on (rdh.request_id = r.id)
	JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
		on ris.request_id = r.id
WHERE ( d.icdo3 NOT IN ('9765/1') or ( d.name = 'see comments' and rr.status = 'new' ) )
	and rh.action = 'authorised'
	and MONTH(rdh.`time`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and	 YEAR(rdh.`time`) =  YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
	and rd.parent_organisation_id in (
		SELECT parent_id FROM local_network_locations
	)
	and s.description NOT IN ( 'Follow-up CML (PB)', 'Outreach' )

[ncrs_data]
SELECT DISTINCT(t1.id) as 'request_id',
    DATE(t1.created_at) as 'request_date',
	p.`last_name`,
	p.`first_name`,
	p.`middle_name`,
	p.`nhs_number`,
	pc.`unit_number`,
	p.`dob`,
	p.`gender`,
    pd.address,
    pd.post_code,
    r.`national_code` as 'referrer_code',
	CONCAT('H',t1.`request_number`,'/', t1.`year` - 2000) as 'labno',
    rs.`organisation_code`,
	rr.`clinical_details`,
    rr.`biopsy_site`,
    rr.`gross_description`,
	rr.`specimen_quality`,
    DATE(rr.`specimen_date`) as 'sample_date',
    rr.`clinical_details`,
	rr.`morphology`,
	rr.`comment`,
    d.`name` as 'diagnosis',
    d.`icdo3`,
    DATE(rh.time) as 'authorisation_date'
FROM requests t1
    JOIN request_history rh on (rh.request_id = t1.id and action = 'authorised')
    JOIN ( patient_case pc JOIN patients p on pc.patient_id = p.id )
    	on t1.patient_case_id = pc.id
    JOIN ( referral_sources rs JOIN local_network_locations lnl on
            lnl.parent_id = rs.parent_organisation_id )
    	on pc.referral_source_id = rs.id
    JOIN ( referrer_department rd JOIN referrers r on rd.referrer_id = r.id )
    	on t1.referrer_department_id = rd.id
    JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
        on rr.request_id = t1.id
    JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
        on ris.request_id = t1.id
    LEFT JOIN request_diagnosis_history rdh on rdh.request_id = t1.id
    LEFT JOIN patient_demographics pd on pd.patient_id = p.id
    /* LEFT JOIN request_trial rt on rt.request_id = t1.id */
WHERE ( DATE(rh.`time`) BETWEEN ? and ? OR DATE(rdh.`time`) BETWEEN ? and ? )
    /* and rt.request_id IS NULL // handled in screen exclusions */
    and s.description NOT IN (
        'Inappropriate/unsuitable sample',
        'Myeloma trial follow-up',
        'Myeloma XI follow-up',
        'Follow-up CML (PB)',
        'Follow-up CML (BM)',
        'CLL trial follow-up',
        'Chimerism (CHIA/CHIB)',
        'Chimerism (CHIM)',
        'ACE-WM follow-up',
        'HIV monitoring',
        'NCG PNH (PB)',
        'PNH NCG (PB)',
        'NCG PNH (BM)',
        'Outreach'
    )
    and ( /* requires a (non-MGUS) ICDO3 code: */
        icdo3 <> '9765/1' or (
            d.name = 'see comments' and rr.comment IS NOT NULL
        )
    )
ORDER BY t1.id

[nycris_request_specimens]
SELECT
    rs.request_id,
    group_concat(s.description separator '; ') as 'description',
    group_concat(s.sample_code) as 'code'
FROM request_specimen rs
    JOIN specimens s on (rs.specimen_id = s.id)
WHERE rs.request_id in (??)
GROUP BY request_id

[nycris_result_summaries]
SELECT
    rs.request_id,
    rs.results_summary,
    ls.section_name
FROM request_result_summaries rs
    join lab_sections ls on rs.lab_section_id = ls.id
WHERE rs.request_id in (??)

[nycris_reporters]
SELECT
    rh.request_id,
    u.username,
    u.first_name,
    u.last_name
FROM request_history rh
    JOIN users u on ( rh.user_id = u.id and action = 'reported' )
WHERE request_id in (??)

[single_use_referral_sources]
/* replaced with unique_referral_sources & unique_referral_source_request_ids
SELECT rs.id
FROM requests r
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN referral_sources rs on pc.referral_source_id = rs.id
GROUP BY rs.id
HAVING COUNT(rs.id) = 1
*/

[unique_referral_source_request_ids]
SELECT t1.id
FROM requests t1
	LEFT JOIN request_history t2 on ( t2.request_id = t1.id
		and t2.`action` REGEXP 'amended referral source' )
WHERE DATE(t1.created_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
	OR DATE(t2.time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

[unique_referral_sources]
SELECT rs.*, rt.description,
    MAX(DATE(r.created_at)) as 'reg_date' /* need newest date if multiple */
FROM requests r
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN referral_sources rs on pc.referral_source_id = rs.id
	JOIN referral_types rt on rs.referral_type_id = rt.id
WHERE r.id IN (??)
GROUP BY rs.id

[new_referral_sources]
/* replaced with referral_source_seen
SELECT rs.*, rt.description
FROM requests r
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN referral_sources rs on pc.referral_source_id = rs.id
	JOIN referral_types rt on rs.referral_type_id = rt.id
WHERE DATE(r.created_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
	and rs.id in (??)
*/

[referral_source_seen]
SELECT COUNT(*)
FROM requests r
    JOIN patient_case pc on r.patient_case_id = pc.id
WHERE pc.referral_source_id = ?
    AND DATE(r.created_at) < ?

/* quicker, but constraint requires h1.time not arv.created_at
[turnaround_times_new]
SELECT
	s1.sample_code,
    s2.description,
	arv.reg_date as 'registered',
	arv.auth_date as 'authorised',
	DATE(rsv.time) as 'reported'
FROM
  authorised_reports_view arv
  JOIN ( request_specimen rs JOIN specimens s1 ON rs.specimen_id = s1.id )
    ON arv.id = rs.request_id
  JOIN ( request_initial_screen ris JOIN screens s2 ON ris.screen_id = s2.id )
    ON arv.id = ris.request_id
  JOIN ( request_status_view rsv JOIN status_options so on rsv.status_option_id = so.id )
	ON rsv.request_id = arv.id and rsv.action = 'reported'
  LEFT JOIN request_trial rt on rt.request_id = arv.id
WHERE rt.request_id IS NULL AND %CONSTRAINT%
 */

[turnaround_times]
SELECT
  s1.sample_code,
  s2.description,
  DATE(r.created_at) as 'registered',
  DATE(h1.time) as 'reported',
  DATE(h2.time) as 'authorised'
FROM
  requests r
  JOIN ( request_specimen rs JOIN specimens s1 ON rs.specimen_id = s1.id )
    ON (r.id = rs.request_id)
  JOIN ( request_initial_screen ris JOIN screens s2 ON ris.screen_id = s2.id)
    ON (r.id = ris.request_id)
  /* slow, but slightly faster allocating h1 = authorised, h2 = reported */
  JOIN request_history h1 ON (h1.request_id = r.id and h1.action = 'reported')
  /* LEFT JOIN in case not using authorisation */
  LEFT JOIN request_history h2 ON (h2.request_id = r.id and h2.action = 'authorised')
  LEFT JOIN request_trial rt on rt.request_id = r.id
WHERE rt.request_id IS NULL AND %CONSTRAINT%

[turnaround_times_to_authorisation]
SELECT
    r.request_number,
    r.year,
	s1.sample_code,
	s2.description,
	DATE(r.created_at) as 'registered',
	DATE(h1.time) as 'authorised'
FROM
  requests r
  JOIN ( request_specimen rs JOIN specimens s1 ON rs.specimen_id = s1.id )
    ON (r.id = rs.request_id)
  JOIN ( request_initial_screen ris JOIN screens s2 ON ris.screen_id = s2.id)
    ON (r.id = ris.request_id)
  JOIN request_history h1 ON (r.id = h1.request_id and h1.action = 'authorised')
  LEFT JOIN request_trial rt on rt.request_id = r.id
WHERE rt.request_id IS NULL AND %CONSTRAINT%

[turnaround_times_to_reporting]
SELECT
    r.request_number,
    r.year,
	s1.sample_code,
	s2.description,
	DATE(r.created_at) as 'registered',
	DATE(h1.time) as 'reported'
FROM
  requests r
  JOIN ( request_specimen rs JOIN specimens s1 ON rs.specimen_id = s1.id )
    ON (r.id = rs.request_id)
  JOIN ( request_initial_screen ris JOIN screens s2 ON ris.screen_id = s2.id)
    ON (r.id = ris.request_id)
  JOIN request_history h1 ON (r.id = h1.request_id and h1.action = 'reported')
  LEFT JOIN request_trial rt on rt.request_id = r.id
WHERE rt.request_id IS NULL AND %CONSTRAINT%

[turnaround_lab_test_times]
SELECT
    r.id as 'request_id',
	r.request_number,
	r.year,
	r.created_at as 'registered',
  	lt.field_label,
  	ls.section_name,
  	rh.time as 'screened',
	lts.time as 'completed'
FROM
    requests r
    JOIN request_history rh on ( rh.request_id = r.id AND rh.action = 'screened' )
    JOIN request_lab_test_status lts ON lts.request_id = r.id
    JOIN lab_tests lt ON lts.lab_test_id = lt.id
    JOIN lab_sections ls on lt.lab_section_id = ls.id
    JOIN lab_test_status_options so on lts.status_option_id = so.id
WHERE
    so.description = 'complete' AND %CONSTRAINT%
ORDER BY r.year, r.request_number

[turnaround_lab_test_post_screen_requests]
SELECT request_id, `action`, `time` as 'datetime'
FROM request_lab_test_history
WHERE `action` RLIKE '^(auto-)?requested' and request_id in (??)
ORDER BY `time` /* so any repeat tests in chronological order */

[lab_tests_sign_off]
SELECT lt.field_label, u.username, count(*) as f
FROM request_lab_test_status t1
	JOIN ( lab_tests lt JOIN lab_sections ls on lt.lab_section_id = ls.id )
        on t1.lab_test_id = lt.id
	JOIN lab_test_status_options so on t1.status_option_id = so.id
	JOIN users u on t1.user_id = u.id
WHERE so.description = 'complete' AND %CONSTRAINT%
GROUP BY lt.id, u.id
ORDER BY f desc

[nhs_number_compliance]
SELECT
    rs.display_name as 'location',
	COUNT(*) as 'total',
	COUNT(p.nhs_number) as 'has_nhs_number',
	ROUND(100 * COUNT(p.nhs_number) / COUNT(*), 0) as 'percent'
FROM requests r
	JOIN patient_case pc ON (pc.id = r.patient_case_id)
	JOIN referral_sources rs ON (pc.referral_source_id = rs.id)
	JOIN patients p ON (pc.patient_id = p.id)
	JOIN request_initial_screen ris ON (ris.request_id = r.id)
	JOIN screens s ON (ris.screen_id = s.id)
    LEFT JOIN ( request_trial rt JOIN clinical_trials ct on rt.trial_id = ct.id )
        on rt.request_id = r.id
WHERE %CONSTRAINT%
GROUP BY rs.id
HAVING total > 5
ORDER BY ROUND(100 * COUNT(p.nhs_number) / COUNT(*), 0) DESC, total DESC, rs.display_name

[user_workload_stats]
SELECT
    GROUP_CONCAT(DISTINCT(s.sample_code) ORDER BY s.sample_code SEPARATOR '-')
        as 'specimen',
    rh.action,
    u.username
FROM
  requests r
  JOIN (request_specimen rs JOIN specimens s ON (rs.specimen_id = s.id)) ON r.id = rs.request_id
  JOIN (request_history rh JOIN users u ON (rh.user_id = u.id)) ON r.id = rh.request_id
WHERE %CONSTRAINT% AND rh.action IN (??)
GROUP BY r.id, rh.action

[revised_diagnoses]
SELECT
    r.id,
	r.request_number,
	r.year,
	d1.name as 'current_diagnosis',
	d2.name as 'revised_diagnosis',
	u1.username as 'reporter',
	u2.username as 'reviser',
    co.option_name
FROM
    requests r
	JOIN request_report_view rr ON (rr.request_id = r.id)
	JOIN request_diagnosis_history dh ON (dh.request_id = r.id)
	JOIN diagnosis_change_options co ON (dh.option_id = co.id)
	JOIN diagnoses d1 ON (rr.diagnosis_id = d1.id)
	JOIN diagnoses d2 ON (dh.diagnosis_id = d2.id)
	JOIN request_history rh ON (rh.request_id = r.id and rh.action = 'reported')
	JOIN users u1 ON (rh.user_id = u1.id)
	JOIN users u2 ON (dh.user_id = u2.id)
WHERE co.option_name LIKE 'error%'
	and %CONSTRAINT%
ORDER BY r.year, r.request_number

[user_workload]
/* replaced by RDBO method
SELECT
    u.username,
    s.sample_code,
    rh.action
FROM requests r
    JOIN ( request_history rh JOIN users u on rh.user_id = u.id )
        on ( rh.request_id = r.id )
    JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
        on (rs.request_id = r.id)
WHERE
    date(rh.time) >= ?
	and rh.`action` = ?
*/

[unfixed_histology_specimens]
SELECT
    r.id,
    r.request_number,
    DATE(r.created_at),
    GROUP_CONCAT(s.sample_code),
    d.name,
    DATE(rh.time)
FROM
    requests r
	JOIN request_report_view rr ON (rr.request_id = r.id)
	JOIN diagnoses d ON (rr.diagnosis_id = d.id)
	JOIN request_history rh ON (rh.request_id = r.id and rh.action = 'authorised')
	JOIN request_specimen rs on (rs.request_id = r.id)
	JOIN specimens s on (rs.specimen_id = s.id)
WHERE rh.time BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
		and CURRENT_DATE() /* ie to end of yesterday) */
	AND s.description like '%unfixed'
GROUP BY r.id
ORDER BY r.year, r.request_number

[get_email_contacts]
/* replaced by SQLAM (requests_and_reports.pl) or discontinued (mail_reports.pl)
SELECT
    id,
    display_name,
    `scope`,
    referral_source_id,
    GROUP_CONCAT(contact_address) as 'contacts'
FROM email_contacts
WHERE type = ?
    and is_active = 'yes'
GROUP BY display_name, `scope`, referral_source_id
*/

[mail_reports_request_ids]
SELECT r.id
FROM requests r
    JOIN patient_case pc on (r.patient_case_id = pc.id)
	JOIN referrer_department rd on (r.referrer_department_id = rd.id)
	JOIN hospital_departments hd on (rd.hospital_department_code = hd.id)
    JOIN referral_sources rs on (pc.referral_source_id = rs.id)
    JOIN request_initial_screen ris on (ris.request_id = r.id)
    JOIN screens s on (ris.screen_id = s.id)
    JOIN request_report_view rr ON (r.id = rr.request_id)
    JOIN status_options so ON (r.status_option_id = so.id)
    LEFT JOIN ( request_trial rt JOIN clinical_trials ct on (rt.trial_id = ct.id) )
        on (rt.request_id = r.id )
WHERE /* constraints added in script */

[pathlinks_samples]
SELECT
	r.request_number,
	( r.year - 2000 ),
	rs.display_name,
	DATE(rh.time),
	GROUP_CONCAT(s.sample_code)
FROM requests r
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN referral_sources rs on pc.referral_source_id = rs.id
	JOIN parent_organisations po on rs.parent_organisation_id = po.id
	JOIN request_specimen rs2 on rs2.request_id = r.id
	JOIN specimens s on rs2.specimen_id = s.id
	JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'authorised' )
WHERE s.description REGEXP 'trephine|block|fixed'
	and YEARWEEK(rh.time, 1) = YEARWEEK(CURRENT_DATE(), 1)
    and po.parent_code IN (??)
GROUP BY r.id

[requests_received]
/* replaced by SLQAM
SELECT r.id
FROM requests r
	JOIN patient_case pc on (r.patient_case_id = pc.id)
    JOIN patients p on (pc.patient_id = p.id)
	JOIN referral_sources rs on (pc.referral_source_id = rs.id)
WHERE YEARWEEK(r.created_at, 1) = YEARWEEK(CURRENT_DATE(), 1)
    AND p.first_name NOT IN ('lgi', 'pin')
    AND rs.id IN (??)
*/
[requests_authorised]
/* replaced by SLQAM
SELECT r.id
FROM requests r
	JOIN patient_case pc on (r.patient_case_id = pc.id)
    JOIN patients p on (pc.patient_id = p.id)
	JOIN referral_sources rs on (pc.referral_source_id = rs.id)
    JOIN request_history rh on (rh.request_id = r.id and rh.action = 'authorised')
WHERE YEARWEEK(rh.time, 1) = YEARWEEK(CURRENT_DATE(), 1)
    AND p.first_name NOT IN ('lgi', 'pin')
    AND rs.id IN (??)
*/
[requests_and_reports]
/* replaced by SLQAM
SELECT
    UCASE(p.last_name) as last_name,
    CONCAT(UPPER(SUBSTRING(p.first_name, 1, 1)), LOWER(SUBSTRING(p.first_name FROM 2)))
        AS first_name,
    p.dob,
    p.nhs_number,
    pc.unit_number,
    rs.display_name as 'location',
    r2.name as 'referrer',
    hd.display_name as 'department',
	DATE(r1.created_at) as 'registered',
    DATE(rh.time) as 'authorised'
FROM requests r1
    JOIN patient_case pc on (r1.patient_case_id = pc.id)
    JOIN patients p on (pc.patient_id = p.id)
	JOIN referrer_department rd on (r1.referrer_department_id = rd.id)
	JOIN referrers r2 on (rd.referrer_id = r2.id)
	JOIN hospital_departments hd on (rd.hospital_department_code = hd.id)
    JOIN referral_sources rs on (pc.referral_source_id = rs.id)
    LEFT JOIN request_history rh on (rh.request_id = r1.id and rh.action = 'authorised')
WHERE r1.id IN (??)
ORDER BY p.last_name, p.first_name
*/

[awaiting_final_diagnosis]
SELECT id, CONCAT_WS('/', request_number, year - 2000) as hmds_ref, diagnosis,
    UPPER(rsv1.username) as 'reporter', UPPER(rsv2.username) as 'authoriser', auth_date
FROM authorised_reports_view r
    JOIN request_status_view rsv1 on ( rsv1.request_id = r.id and rsv1.`action` = 'reported' )
    JOIN request_status_view rsv2 on ( rsv2.request_id = r.id and rsv2.`action` = 'authorised' )
WHERE diagnosis RLIKE 'awaiting final (diagnosis|review)'
   AND auth_date < DATE_SUB(CURDATE(), INTERVAL ? DAY)
GROUP BY r.id /* to remove duplicate authorisations */
/* HAVING COUNT(*) > 1 // to view duplicates */
ORDER BY year, request_number

[awaiting_final_diagnosis_incomplete_tests]
select t1.request_id, 1
from request_lab_test_status t1
	join lab_test_status_options so on t1.status_option_id = so.id
where so.description <> 'complete'
    and t1.request_id in (??)

[all_and_mixed_phenotype]
SELECT r.request_number, r.year, r.last_name, r.first_name, r.dob, r.age, r.nhs_number, r.location,
	GROUP_CONCAT(DISTINCT(s.sample_code)) as 'specimen', rrv.clinical_details, rrv.morphology, rrv.`comment`,
	MAX(CASE WHEN ls.section_name = 'Flow cytometry' THEN rrs.results_summary END) as 'flow result',
	MAX(CASE WHEN ls.section_name = 'Cytogenetics'   THEN rrs.results_summary END) as 'cytogenetics result',
	MAX(CASE WHEN ls.section_name = 'Molecular'      THEN rrs.results_summary END) as 'molecular result',
	CASE WHEN rh.hb   = '*000' THEN 'fail' ELSE rh.hb   / 1   END as 'hb',
	CASE WHEN rh.wbc  = '*000' THEN 'fail' ELSE rh.wbc  / 10  END as 'wbc',
	CASE WHEN rh.plt  = '*000' THEN 'fail' ELSE rh.plt  / 1   END as 'plts',
	r.diagnosis, r.auth_date
FROM authorised_reports_view r
	JOIN request_report_view rrv on rrv.request_id = r.id
	JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
		on rs.request_id = r.id
	JOIN ( request_result_summaries rrs JOIN lab_sections ls on rrs.lab_section_id = ls.id )
		on rrs.request_id = r.id
	LEFT JOIN request_haematology rh on rh.lab_number =
		CONCAT(r.year - 2000, '_', LPAD(r.request_number, 5, '0'))
WHERE r.icdo3 in (
	'9801/3',
	'9806/3',
	'9807/3',
	'9808/3',
	'9809/3',
	'9811/3',
	'9812/3',
	'9813/3',
	'9814/3',
	'9815/3',
	'9816/3',
	'9818/3',
	'9837/3'
)
	and r.`status` = 'new'
	and MONTH(r.auth_date) = MONTH( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) )
	and  YEAR(r.auth_date) =  YEAR( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) )
GROUP BY r.id
ORDER BY r.year, r.request_number

[yhhn_potential_trial_cases]
/* TODO: very slow search */
SELECT rv.*, CONCAT_WS('/', rv.request_number, rv.year - 2000) as 'hmds_ref'
FROM authorised_reports_view rv
	JOIN parent_organisations po on rv.parent_code = po.parent_code
	JOIN local_network_locations nl on po.id = nl.parent_id
WHERE rv.auth_date >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
	AND (
		( rv.age >= 60 AND rv.diagnosis RLIKE 'Classical Hodgkin lymphoma' )
		OR ( rv.age >= 18
            AND (
                rv.icdo3 IN ('9673/3', '9690/3', '9680/3') /* FL, DLBCL, MCL */
                OR rv.diagnosis RLIKE 'marginal zone lymphoma'
                OR rv.diagnosis IN (
                    'Angioimmunoblastic T-cell lymphoma',
                    'Anaplastic large cell lymphoma ALK negative', /* not ALK+ */
                    'Anaplastic large cell lymphoma of T/null type',
                    'Peripheral T-cell lymphoma - common; unspecified'
                    'Primary cutaneous anaplastic large cell lymphoma',
                    'Mycosis fungoides'
                )
            )
        )
	)
GROUP BY rv.id /* in case duplicate authorisation entries in request_history */

[completed_requests]
SELECT
    r.request_number,
    r.year,
    UPPER(p.last_name) as lastname,
    ucFirst(p.first_name) as firstname, /* ucFirst = mysql function */
    ucFirst(p.middle_name) as middlename, /* ditto */
    p.dob,
    rs.display_name as 'location',
    d.name as 'diagnosis'
FROM requests r
	JOIN status_options so on r.status_option_id = so.id
	JOIN request_report_view rr on rr.request_id = r.id
	JOIN diagnoses d on rr.diagnosis_id = d.id
	JOIN patient_case pc on r.patient_case_id = pc.id
	JOIN referral_sources rs on rs.id = pc.referral_source_id
    JOIN patients p on pc.patient_id = p.id
WHERE so.description = 'complete'
    and DATE(r.updated_at) = DATE_SUB(CURDATE(), INTERVAL ? DAY)