SELECT *
FROM (
SELECT CONCAT( "", efd.demographic_no, "" ) AS "Master",
CONCAT( "",d.last_name,", ",SUBSTRING_INDEX(d.first_name," ",1), "" ) AS "eChart",
CONCAT( "", CONCAT(LEFT(efd.form_name,5),efd.fdid),"") AS "eForm",
efd.form_date AS "form date",
CONCAT( "", CONCAT(pre.prevention_type, pre.id),"" ) as prevention,
LEFT(pre.prevention_date,10) AS "prevention date",
CONCAT (pre.deleted,"/",pre.refused) AS "del/refuse",
prx.val,
CONCAT( "",CONCAT(hl7.discipline,hl7.accessionNum),"" ) as test,
LEFT(hl7.obr_date,10) AS "test date",
d.provider_no AS num,
p.last_name AS "Provider Surname",
d.family_doctor AS FP
FROM eform_data efd
LEFT JOIN preventions pre ON ( efd.demographic_no = pre.demographic_no AND pre.creation_date > efd.form_date
AND pre.prevention_type = SUBSTRING_INDEX(SUBSTRING_INDEX('{testcode}', '~', 2), '~', -1))
LEFT JOIN preventionsExt prx ON ( prx.prevention_id = pre.id AND prx.keyval = "result")
LEFT JOIN demographic d ON ( efd.demographic_no = d.demographic_no )
LEFT JOIN provider p ON ( d.provider_no = p.provider_no )
LEFT JOIN patientLabRouting plr ON ( efd.demographic_no = plr.demographic_no AND plr.created > efd.form_date )
LEFT JOIN hl7TextInfo hl7 ON ( plr.lab_no = hl7.lab_no AND hl7.obr_date >= efd.form_date AND hl7.discipline REGEXP SUBSTRING_INDEX(SUBSTRING_INDEX('{testcode}', '~', 3), '~', -1))
WHERE efd.fid IN(SUBSTRING_INDEX('{testcode}', '~', 1))
AND efd.form_date > ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-{months})*100)+1)
AND (d.provider_no LIKE {provider_no})
ORDER BY efd.form_date DESC, accessionNum DESC, pre.id DESC
LIMIT 10000
) AS sub
GROUP BY eForm
ORDER BY sub.`form date` DESC
;
SELECT "'%'" as provider_no, 'all providers' as provider
UNION
SELECT provider_no, concat(last_name,',',first_name,'(',provider_no,')') from provider
WHERE status='1'
AND provider_type='doctor'
AND ohip_no>1000
ORDER BY provider_no;
SELECT CONCAT(fid,"~PAP~AP\/C*|SRC\/") AS testcode, CONCAT("PAP|",IF(efm.subject<>"",efm.subject,efm.form_name)) AS DisplayName FROM eform efm WHERE efm.status=1 AND (efm.form_name LIKE "%PAP%" OR efm.form_name LIKE "%cytolo%" )
UNION
SELECT CONCAT(fid,"~FOBT~COLON%") AS testcode, CONCAT("FIT|",IF(efm.subject<>"",efm.subject,efm.form_name)) AS DisplayName FROM eform efm WHERE efm.form_name LIKE "FIT%" AND efm.status=1
UNION
SELECT CONCAT(fid,"~MAM~mamm%") AS testcode, CONCAT("MAM|",IF(efm.subject<>"",efm.subject,efm.form_name)) AS DisplayName FROM eform efm WHERE efm.form_name LIKE "mammo%" AND efm.status=1
;