[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 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 1 DAY)	

[paediatrics_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]
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)
	
[new_remodlb_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_FORMAT(r.created_at, '%Y-%m-%d') = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
	and ct.trial_name = 'SCTU REMoDL-B' # 'HMDS outreach' - for test
ORDER BY r.`year`, r.`request_number`

[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', 
	'Myeloma X - 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 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]
SELECT  
	DATE_FORMAT(r.`created_at`, '%Y-%m-%d') 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` = ?
ORDER BY r.id

[national_code_unknown]
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
 
[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`,
	DATE_FORMAT(req.`created_at`, '%Y') - DATE_FORMAT(p.`dob`, '%Y') 
		- (DATE_FORMAT(req.`created_at`, '00-%m-%d') < DATE_FORMAT(p.`dob`, '00-%m-%d'))
        AS 'age',
    pc.`unit_number`,
    src.`organisation_code`,    
    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
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`
    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 JOIN `clinical_trials` ct on (rt.trial_id = ct.id)
	 	on (rt.`request_id` = req.`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`

[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)        
)

[referrer_department_map]
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)

[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` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`    
    JOIN `icdo_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 req.`created_at` >= 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` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`    
    JOIN `icdo_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` 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_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_FORMAT(rh.`time`, '%Y-%m-%d'),
    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` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`    
    JOIN `icdo_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_FORMAT(rh.`time`, '%Y-%m-%d'),
    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` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`    
    JOIN `icdo_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`

[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_FORMAT(p.`created_at`, '%Y-%m-%d'),
    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` 
WHERE p.`created_at` BETWEEN DATE_SUB(CURDATE(), INTERVAL ? DAY) and CURDATE()
  and p.`nhs_number` IS NULL
  and p.`first_name` <> 'LGI'
  and po.`parent_code` = 'RR8'

[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_FORMAT(req.`created_at`, '%Y-%m-%d'),
    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_FORMAT(req.`created_at`, '%Y-%m-%d') as 'registered',
    DATE_FORMAT(rh.`time`, '%Y-%m-%d') 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_FORMAT(req.`created_at`, '%Y-%m-%d') 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`
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`)
WHERE
  so.`description` = 'reported'
ORDER BY req.`year`, req.`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'
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` 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`
WHERE
    so.`description` = 'reported'
    and rh.`action` = 'reported'
ORDER BY rh.`time`

[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`,
    gd.`detail` as 'gross_description',
	rr.`specimen_quality`,
	rr.`comment`,
    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_report` rr on rr.`request_id` = req.`id`
    JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
    LEFT JOIN `request_gross_description` gd on gd.`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`

[incomplete_request_ids]
SELECT DISTINCT(r.`id`)
FROM `requests` r
    JOIN `status_options` so ON (r.`status_option_id` = so.`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` rrs ON (rrs.`request_id` = r.`id`)    
    JOIN `lab_sections` ls ON (rrs.`lab_section_id` = ls.`id`)
WHERE so.`description` = 'authorised'    
    and ls.`section_name` IN ('Molecular','Cytogenetics','FISH')
    and s.`description` NOT IN (??)
ORDER BY r.`id`

[nycris_data]
SELECT
    r.id as 'request_id',
    DATE_FORMAT(r.created_at, '%Y-%m-%d') as 'request_date',
	p.`last_name`,
	p.`first_name`,
	p.`middle_name`,
	p.`nhs_number`,
	pc.`unit_number`,
	DATE_FORMAT(p.`dob`, '%Y-%m-%d') as '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`,
    rgd.`detail` as 'gross_description',
	rr.`specimen_quality`,
    rr.`clinical_details`,
	rr.`comment`,
    d.`name` as 'diagnosis',
    d.`icdo3`,
    DATE_FORMAT(rh.time, '%Y-%m-%d') as 'authorisation_date'
FROM requests r
    JOIN request_report 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)
    LEFT JOIN request_gross_description rgd on (rgd.request_id = r.id)
WHERE r.id in (??)    

[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 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_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 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
	)
    
[turnaround_times_specimen]
SELECT
	s1.sample_code,
	s2.description,
	DATE_FORMAT(r.created_at, '%Y-%m-%d') as 'registered', 
	DATE_FORMAT(h1.time, '%Y-%m-%d') as 'reported',
	DATE_FORMAT(h2.time, '%Y-%m-%d') as 'authorised'
FROM
  requests r 
  JOIN request_specimen rs ON (r.id = rs.request_id)
  JOIN specimens s1 ON (rs.specimen_id = s1.id)
  JOIN request_history h1 ON (r.id = h1.request_id and h1.action = 'reported')
  JOIN request_history h2 ON (r.id = h2.request_id and h2.action = 'authorised')
  JOIN request_initial_screen ris ON (r.id = ris.request_id)
  JOIN screens s2 ON (ris.screen_id = s2.id)
WHERE r.id IN (
	SELECT request_id FROM request_history WHERE %CONSTRAINT% AND action = 'reported'
)

[turnaround_times_specimen_no_authorisation]
SELECT
	s1.sample_code,
	s2.description,
	DATE_FORMAT(r.created_at, '%Y-%m-%d') as 'registered', 
	DATE_FORMAT(h1.time, '%Y-%m-%d') as 'reported'
FROM
  requests r 
  JOIN request_specimen rs ON (r.id = rs.request_id)
  JOIN specimens s1 ON (rs.specimen_id = s1.id)
  JOIN request_history h1 ON (r.id = h1.request_id and h1.action = 'reported')
  JOIN request_initial_screen ris ON (r.id = ris.request_id)
  JOIN screens s2 ON (ris.screen_id = s2.id)
WHERE r.id IN (
	SELECT request_id FROM request_history WHERE %CONSTRAINT% AND action = 'reported'
)

[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)
where %CONSTRAINT%
	and s.description NOT IN ('HIV')
GROUP BY rs.id
HAVING total > 5
ORDER BY ROUND(100 * COUNT(p.nhs_number) / COUNT(*), 0) DESC, total DESC, rs.display_name

[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'
FROM
    requests r
	JOIN request_report rr ON (rr.request_id = r.id)
	JOIN request_diagnosis_history dh ON (dh.request_id = r.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 dh.reason = 'error'
	and %CONSTRAINT%
ORDER BY r.year, r.request_number

[unfixed_histology_specimens]
SELECT
    r.id,
    r.request_number,
    DATE_FORMAT(r.created_at, '%d.%b.%Y'),
    GROUP_CONCAT(s.sample_code),
    d.name
FROM
    requests r
	JOIN request_report 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 DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
	AND s.description like '%unfixed'
GROUP BY r.id
ORDER BY r.year, r.request_number

[nycris_request_specimens]
SELECT
    rs.request_id,
    group_concat(s.sample_code) as 'code',
    group_concat(s.description separator '; ') as 'description'
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 (??)

[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_report rr ON (r.id = rr.request_id)  
    JOIN status_options so ON (r.status_option_id = so.id)
WHERE
  DATE_FORMAT(rr.updated_at, '%Y-%m-%d') = ?
  AND so.description IN ('authorised', 'complete')