Personal tools
Navigation
 

Prevention Reconciliation

Now has a link directly to the prevention. Some items should be tracked all the way to the prevention listing. That you still have to enter manually, but if it is missing this report by template will let you know to look for it.

Extensible Markup Language (XML) icon tracking4g.xml — Extensible Markup Language (XML), 3 kB (3731 bytes)

File contents

<report title="Prevention Reconciliation 2g" description="Dates of selected Tests and their results" active="1">
<query>
 SELECT * 
	FROM (
	SELECT CONCAT( "<a  target=new href=../../demographic/demographiccontrol.jsp?demographic_no=", efd.demographic_no, "&displaymode=edit&dboperation=search_detail/>", efd.demographic_no, "<a/>" ) AS "Master", 
		CONCAT( "<a  target='_blank' href=../../oscarEncounter/IncomingEncounter.do?demographicNo=", d.demographic_no, "&providerNo=",d.provider_no,">",d.last_name,", ",SUBSTRING_INDEX(d.first_name," ",1), "<a/>" ) AS "eChart",
		CONCAT( "<a  target='_blank' href=../../eform/efmshowform_data.jsp?fdid=",efd.fdid,"&appointment=0&parentAjaxId=eforms>", CONCAT(LEFT(efd.form_name,5),efd.fdid),"<a/>") AS "eForm", 
		efd.form_date AS "form date", 
		CONCAT( "<a  target=new href=../../oscarPrevention/AddPreventionData.jsp?id=",pre.id,"&demographic_no=",efd.demographic_no,">", CONCAT(pre.prevention_type, pre.id),"<a/>" ) as prevention,
		LEFT(pre.prevention_date,10) AS "prevention date", 
		CONCAT (pre.deleted,"/",pre.refused) AS "del/refuse", 
		prx.val,
		CONCAT( "<a  target=new href=../..//lab/CA/ALL/labDisplay.jsp?demographicId=",efd.demographic_no,"&providerNo=",d.provider_no,"&segmentID=",hl7.lab_no,">",CONCAT(hl7.discipline,hl7.accessionNum),"<a/>" ) 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
 ;
</query>
<param id="provider_no" type="list" description="Provider Number">
<param-query> 
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;
</param-query>
</param>
<param id="testcode" type="list" description="Prevention type">
<param-query> 
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
;
</param-query></param>

<param id="months" type="text" description="Number of Months to Search eg 1"> </param>
</report>

Document Actions

 

Download button

DOWNLOAD OSCAR FOR TESTING

 

eForms button

DOWNLOAD SHARED E-FORMS


 Customize button

FIND PLUG-INS AND TWEAKS
FOR YOUR OSCAR EMR

 

Subscribe Button

SUBSCRIBE TO DISCUSSION LIST 
(SEE ALL LISTS)

  

Help button

ACCESS THE ONLINE MANUALS
(PAID SUPPORT)