PageRenderTime 151ms CodeModel.GetById 117ms app.highlight 27ms RepoModel.GetById 2ms app.codeStats 0ms

/library/sql-ccr.inc

https://bitbucket.org/DenizYldrm/openemr
PHP | 505 lines | 433 code | 40 blank | 32 comment | 15 complexity | 9a437b9ac29d2c34c8592439bf7b238b MD5 | raw file
  1<?php
  2//  ------------------------------------------------------------------------ //
  3//                     Garden State Health Systems                           //
  4//                    Copyright (c) 2010 gshsys.com                          //
  5//                      <http://www.gshsys.com/>                             //
  6//  ------------------------------------------------------------------------ //
  7//  This program is free software; you can redistribute it and/or modify     //
  8//  it under the terms of the GNU General Public License as published by     //
  9//  the Free Software Foundation; either version 2 of the License, or        //
 10//  (at your option) any later version.                                      //
 11//                                                                           //
 12//  You may not change or alter any portion of this comment or credits       //
 13//  of supporting developers from this source code or any supporting         //
 14//  source code which is considered copyrighted (c) material of the          //
 15//  original comment or credit authors.                                      //
 16//                                                                           //
 17//  This program is distributed in the hope that it will be useful,          //
 18//  but WITHOUT ANY WARRANTY; without even the implied warranty of           //
 19//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the            //
 20//  GNU General Public License for more details.                             //
 21//                                                                           //
 22//  You should have received a copy of the GNU General Public License        //
 23//  along with this program; if not, write to the Free Software              //
 24//  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA //
 25//  ------------------------------------------------------------------------ //
 26
 27if($_POST['ccrAction'] == 'generate'){
 28  if(isset($_POST['show_date'])){
 29    $set = "on";
 30    $start = $_POST['Start'];
 31    $start = $start." 00:00:00";
 32    $end = $_POST['End'];
 33    $end = $end." 23:59:59";
 34  }
 35}
 36
 37function getHeaderData() {
 38
 39// Reserved for future use
 40	
 41}
 42
 43function getMedicationData() {
 44	global $pid,$set,$start,$end;
 45  if($set == "on"){
 46    $sql = " 
 47      SELECT prescriptions.date_added ,
 48        prescriptions.patient_id,
 49        prescriptions.start_date,
 50        prescriptions.quantity,
 51        prescriptions.interval,
 52        prescriptions.note,
 53        prescriptions.drug,
 54        prescriptions.medication,
 55        IF(prescriptions.active=1,'Active','Prior History No Longer Active') AS active,
 56        prescriptions.provider_id,
 57        prescriptions.size,
 58	prescriptions.rxnorm_drugcode,
 59        IFNULL(prescriptions.refills,0) AS refills,
 60        lo2.title AS form,
 61        lo.title
 62      FROM prescriptions 
 63      LEFT JOIN list_options AS lo
 64      ON lo.list_id = 'drug_units' AND prescriptions.unit = lo.option_id
 65      LEFT JOIN list_options AS lo2
 66      ON lo2.list_id = 'drug_form' AND prescriptions.form = lo2.option_id
 67      WHERE prescriptions.patient_id = ?
 68      AND prescriptions.date_added BETWEEN ? AND ?
 69      UNION
 70      SELECT 
 71        DATE(DATE) AS date_added,
 72        pid AS patient_id,
 73        begdate AS start_date,
 74        '' AS quantity,
 75        '' AS `interval`,
 76        comments AS note,
 77        title AS drug,
 78        '' AS medication,
 79        IF((isnull(enddate) OR enddate = '0000-00-00' OR enddate >= CURDATE()),'Active','Prior History No Longer Active') AS active,
 80        '' AS provider_id,
 81        '' AS size,
 82'' AS rxnorm_drugcode,
 83        0 AS refills,
 84        '' AS form,
 85        '' AS title 
 86      FROM
 87        lists 
 88      WHERE `type` = 'medication' 
 89        AND pid = ?
 90        AND `date` BETWEEN ? AND ?";
 91    $result = sqlStatement($sql, array($pid,$start,$end,$pid,$start,$end) );
 92  }else{
 93    $sql = " 
 94      SELECT prescriptions.date_added ,
 95        prescriptions.patient_id,
 96        prescriptions.start_date,
 97        prescriptions.quantity,
 98        prescriptions.interval,
 99        prescriptions.note,
100        prescriptions.drug,
101        prescriptions.medication,
102        IF(prescriptions.active=1,'Active','Prior History No Longer Active') AS active,
103        prescriptions.provider_id,
104        prescriptions.size,
105	prescriptions.rxnorm_drugcode,
106        IFNULL(prescriptions.refills,0) AS refills,
107        lo2.title AS form,
108        lo.title
109      FROM prescriptions 
110      LEFT JOIN list_options AS lo
111      ON lo.list_id = 'drug_units' AND prescriptions.unit = lo.option_id
112      LEFT JOIN list_options AS lo2
113      ON lo2.list_id = 'drug_form' AND prescriptions.form = lo2.option_id
114      WHERE prescriptions.patient_id = ?
115      UNION
116      SELECT 
117        DATE(DATE) AS date_added,
118        pid AS patient_id,
119        begdate AS start_date,
120        '' AS quantity,
121        '' AS `interval`,
122        comments AS note,
123        title AS drug,
124        '' AS medication,
125        IF((isnull(enddate) OR enddate = '0000-00-00' OR enddate >= CURDATE()),'Active','Prior History No Longer Active') AS active,
126        '' AS provider_id,
127        '' AS size,
128	'' AS rxnorm_drugcode,
129        0 AS refills,
130        '' AS form,
131        '' AS title 
132      FROM
133        lists 
134      WHERE `type` = 'medication' 
135        AND pid = ?";
136    $result = sqlStatement($sql, array($pid,$pid) );
137  }
138	return $result;
139}
140
141function getImmunizationData() {
142	global $pid,$set,$start,$end;
143  if($set == "on"){
144    $sql = "SELECT
145      immunizations.administered_date,
146      immunizations.patient_id,
147      immunizations.vis_date,
148      immunizations.note,
149      immunizations.immunization_id,
150      immunizations.manufacturer,
151      codes.code_text AS title
152    FROM immunizations 
153    LEFT JOIN codes ON immunizations.cvx_code = codes.code
154    LEFT JOIN code_types ON codes.code_type = code_types.ct_id
155    WHERE immunizations.patient_id = ? AND code_types.ct_key = 'CVX'
156    AND create_date BETWEEN ? AND ?" ;
157    $result = sqlStatement($sql, array($pid,$start,$end) );
158  }else{
159    $sql = "SELECT
160      immunizations.administered_date,
161      immunizations.patient_id,
162      immunizations.vis_date,
163      immunizations.note,
164      immunizations.immunization_id,
165      immunizations.manufacturer,
166      codes.code_text AS title
167    FROM immunizations 
168    LEFT JOIN codes ON immunizations.cvx_code = codes.code
169    LEFT JOIN code_types ON codes.code_type = code_types.ct_id
170    WHERE immunizations.patient_id = ? AND code_types.ct_key = 'CVX'";
171    $result = sqlStatement($sql, array($pid) );
172  }
173	return $result;
174}
175
176
177function getProcedureData() {
178
179	global $pid,$set,$start,$end;
180  if($set == "on"){
181    $sql = " 
182    SELECT 
183      lists.title as proc_title,
184      lists.date as `date`,
185      list_options.title as outcome,
186      '' as laterality,
187      '' as body_site,
188      lists.type as `type`,
189      lists.diagnosis as `code`,
190      IF(SUBSTRING(lists.diagnosis,1,LOCATE(':',lists.diagnosis)-1) = 'ICD9','ICD9-CM',SUBSTRING(lists.diagnosis,1,LOCATE(':',lists.diagnosis)-1)) AS coding
191    FROM
192      lists 
193      LEFT JOIN issue_encounter 
194        ON issue_encounter.list_id = lists.id 
195      LEFT JOIN form_encounter 
196        ON form_encounter.encounter = issue_encounter.encounter 
197      LEFT JOIN facility 
198        ON form_encounter.facility_id = facility.id 
199      LEFT JOIN users 
200        ON form_encounter.provider_id = users.id 
201      LEFT JOIN list_options
202        ON lists.outcome = list_options.option_id
203        AND list_options.list_id = 'outcome'
204    WHERE lists.type = 'surgery' 
205      AND lists.pid = ?
206      AND lists.date BETWEEN ? AND ?
207    UNION
208    SELECT 
209      pt.name as proc_title,
210      prs.date as `date`,
211      '' as outcome,
212      ptt.laterality as laterality,
213      ptt.body_site as body_site,
214      'Lab Order' as `type`,
215      ptt.standard_code as `code`,
216      IF(SUBSTRING(ptt.standard_code,1,LOCATE(':',ptt.standard_code)-1) = 'ICD9','ICD9-CM',SUBSTRING(ptt.standard_code,1,LOCATE(':',ptt.standard_code)-1)) AS coding
217    FROM
218      procedure_result AS prs 
219      LEFT JOIN procedure_report AS prp 
220        ON prs.procedure_report_id = prp.procedure_report_id 
221      LEFT JOIN procedure_order AS po 
222        ON prp.procedure_order_id = po.procedure_order_id 
223      LEFT JOIN procedure_type AS pt 
224        ON prs.procedure_type_id = pt.procedure_type_id 
225      LEFT JOIN procedure_type AS ptt 
226        ON pt.parent = ptt.procedure_type_id 
227        AND ptt.procedure_type = 'ord' 
228      LEFT JOIN list_options AS lo 
229        ON lo.list_id = 'proc_unit' 
230        AND pt.units = lo.option_id 
231    WHERE po.patient_id = ?
232    AND prs.date BETWEEN ? AND ?";
233  
234    $result = sqlStatement($sql, array($pid,$start,$end,$pid,$start,$end) );
235  }else{
236    $sql = " 
237    SELECT 
238      lists.title as proc_title,
239      lists.date as `date`,
240      list_options.title as outcome,
241      '' as laterality,
242      '' as body_site,
243      lists.type as `type`,
244      lists.diagnosis as `code`,
245      IF(SUBSTRING(lists.diagnosis,1,LOCATE(':',lists.diagnosis)-1) = 'ICD9','ICD9-CM',SUBSTRING(lists.diagnosis,1,LOCATE(':',lists.diagnosis)-1)) AS coding
246    FROM
247      lists 
248      LEFT JOIN issue_encounter 
249        ON issue_encounter.list_id = lists.id 
250      LEFT JOIN form_encounter 
251        ON form_encounter.encounter = issue_encounter.encounter 
252      LEFT JOIN facility 
253        ON form_encounter.facility_id = facility.id 
254      LEFT JOIN users 
255        ON form_encounter.provider_id = users.id 
256      LEFT JOIN list_options
257        ON lists.outcome = list_options.option_id
258        AND list_options.list_id = 'outcome'
259    WHERE lists.type = 'surgery' 
260      AND lists.pid = ? 
261    UNION
262    SELECT 
263      pt.name as proc_title,
264      prs.date as `date`,
265      '' as outcome,
266      ptt.laterality as laterality,
267      ptt.body_site as body_site,
268      'Lab Order' as `type`,
269      ptt.standard_code as `code`,
270      IF(SUBSTRING(ptt.standard_code,1,LOCATE(':',ptt.standard_code)-1) = 'ICD9','ICD9-CM',SUBSTRING(ptt.standard_code,1,LOCATE(':',ptt.standard_code)-1)) AS coding
271    FROM
272      procedure_result AS prs 
273      LEFT JOIN procedure_report AS prp 
274        ON prs.procedure_report_id = prp.procedure_report_id 
275      LEFT JOIN procedure_order AS po 
276        ON prp.procedure_order_id = po.procedure_order_id 
277      LEFT JOIN procedure_type AS pt 
278        ON prs.procedure_type_id = pt.procedure_type_id 
279      LEFT JOIN procedure_type AS ptt 
280        ON pt.parent = ptt.procedure_type_id 
281        AND ptt.procedure_type = 'ord' 
282      LEFT JOIN list_options AS lo 
283        ON lo.list_id = 'proc_unit' 
284        AND pt.units = lo.option_id 
285    WHERE po.patient_id = ? ";
286  
287    $result = sqlStatement($sql, array($pid,$pid) );
288  }
289	return $result;
290}
291
292
293function getProblemData() {
294
295  # Note we are hard-coding (only allowing) problems that have been coded to ICD9. Would
296  #  be easy to upgrade this to other codesets in future (ICD10,SNOMED) by using already
297  #  existant flags in the code_types table.
298  # Additionally, only using problems that have one diagnosis code set in diagnosis field.
299  #  Note OpenEMR allows multiple codes set per problem, but will limit to showing only
300  #  problems with one diagnostic code set in order to maintain previous behavior
301  #  (this will likely need to be dealt with at some point; ie. support multiple dx codes per problem).
302
303	global $pid,$set,$start,$end;
304  if($set == "on"){
305    $sql = " 
306    SELECT fe.encounter, fe.reason, fe.provider_id, u.title, u.fname, u.lname, 
307      fe.facility_id, f.street, f.city, f.state, ie.list_id, l.pid, l.title AS prob_title, l.diagnosis, 
308      l.outcome, l.groupname, l.begdate, l.enddate, l.type, l.comments , l.date
309    FROM lists AS l 
310    LEFT JOIN issue_encounter AS ie ON ie.list_id = l.id
311    LEFT JOIN form_encounter AS fe ON fe.encounter = ie.encounter
312    LEFT JOIN facility AS f ON fe.facility_id = f.id
313    LEFT JOIN users AS u ON fe.provider_id = u.id
314    WHERE l.type = 'medical_problem' AND l.pid=? AND l.diagnosis LIKE 'ICD9:%'
315    AND l.diagnosis NOT LIKE '%;%'
316    AND l.date BETWEEN ? AND ?";
317    $result = sqlStatement($sql, array($pid,$start,$end) );
318  }else{
319    $sql = " 
320    SELECT fe.encounter, fe.reason, fe.provider_id, u.title, u.fname, u.lname, 
321      fe.facility_id, f.street, f.city, f.state, ie.list_id, l.pid, l.title AS prob_title, l.diagnosis, 
322      l.outcome, l.groupname, l.begdate, l.enddate, l.type, l.comments , l.date
323    FROM lists AS l 
324    LEFT JOIN issue_encounter AS ie ON ie.list_id = l.id
325    LEFT JOIN form_encounter AS fe ON fe.encounter = ie.encounter
326    LEFT JOIN facility AS f ON fe.facility_id = f.id
327    LEFT JOIN users AS u ON fe.provider_id = u.id
328    WHERE l.type = 'medical_problem' AND l.pid=? AND l.diagnosis LIKE 'ICD9:%'
329    AND l.diagnosis NOT LIKE '%;%'";
330    $result = sqlStatement($sql, array($pid) );
331  }
332	return $result;
333}
334
335
336function getAlertData() {
337
338	global $pid,$set,$start,$end;
339  if($set == "on"){
340	$sql = " 
341    select fe.reason, fe.provider_id, fe.facility_id, fe.encounter,
342      ie.list_id, l.pid, l.title as alert_title, l.outcome, 
343      l.groupname, l.begdate, l.enddate, l.type, l.diagnosis, l.date ,
344      l.reaction , l.comments ,
345        f.street, f.city, f.state, u.title, u.fname, u.lname, cd.code_text
346    from lists as l 
347    left join issue_encounter as ie
348    on ie.list_id = l.id
349    left join form_encounter as fe
350    on fe.encounter = ie.encounter
351    left join facility as f
352    on fe.facility_id = f.id
353    left join users as u
354    on fe.provider_id = u.id
355    left join codes as cd
356    on cd.code = SUBSTRING(l.diagnosis, LOCATE(':',l.diagnosis)+1)
357    where l.type = 'allergy' and l.pid=?
358    AND l.date BETWEEN ? AND ?";
359    
360    $result = sqlStatement($sql, array($pid,$start,$end) );
361  }else{
362    $sql = " 
363    select fe.reason, fe.provider_id, fe.facility_id, fe.encounter,
364      ie.list_id, l.pid, l.title as alert_title, l.outcome, 
365      l.groupname, l.begdate, l.enddate, l.type, l.diagnosis, l.date ,
366      l.reaction , l.comments ,
367        f.street, f.city, f.state, u.title, u.fname, u.lname, cd.code_text
368    from lists as l 
369    left join issue_encounter as ie
370    on ie.list_id = l.id
371    left join form_encounter as fe
372    on fe.encounter = ie.encounter
373    left join facility as f
374    on fe.facility_id = f.id
375    left join users as u
376    on fe.provider_id = u.id
377    left join codes as cd
378    on cd.code = SUBSTRING(l.diagnosis, LOCATE(':',l.diagnosis)+1)
379    where l.type = 'allergy' and l.pid=?";
380      
381    $result = sqlStatement($sql, array($pid) );
382  }
383	return $result;
384}
385
386
387function getResultData() {
388
389	global $pid,$set,$start,$end;
390  if($set == "on"){
391    $sql = "
392      SELECT 
393        prs.procedure_result_id as `pid`,
394        pt.name as `name`,
395        pt.procedure_type_id as `type`,
396        prs.date as `date`,
397        concat_ws(' ',prs.result,lo.title) as `result`,
398        prs.range as `range`,
399        prs.abnormal as `abnormal`,
400        prs.comments as `comments`,
401        ptt.lab_id AS `lab`
402      FROM
403        procedure_result AS prs 
404        LEFT JOIN procedure_report AS prp 
405          ON prs.procedure_report_id = prp.procedure_report_id 
406        LEFT JOIN procedure_order AS po 
407          ON prp.procedure_order_id = po.procedure_order_id
408        LEFT JOIN procedure_type AS pt 
409          ON prs.procedure_type_id = pt.procedure_type_id
410          LEFT JOIN procedure_type AS ptt 
411          ON pt.parent = ptt.procedure_type_id
412          AND ptt.procedure_type = 'ord'
413        LEFT JOIN list_options AS lo
414          ON lo.list_id = 'proc_unit' AND pt.units = lo.option_id
415      WHERE po.patient_id=?
416      AND prs.date BETWEEN ? AND ?";
417		
418    $result = sqlStatement($sql, array($pid,$start,$end) );
419  }else{
420    $sql = "
421      SELECT 
422        prs.procedure_result_id as `pid`,
423        pt.name as `name`,
424        pt.procedure_type_id as `type`,
425        prs.date as `date`,
426        concat_ws(' ',prs.result,lo.title) as `result`,
427        prs.range as `range`,
428        prs.abnormal as `abnormal`,
429        prs.comments as `comments`,
430        ptt.lab_id AS `lab`
431      FROM
432        procedure_result AS prs 
433        LEFT JOIN procedure_report AS prp 
434          ON prs.procedure_report_id = prp.procedure_report_id 
435        LEFT JOIN procedure_order AS po 
436          ON prp.procedure_order_id = po.procedure_order_id
437        LEFT JOIN procedure_type AS pt 
438          ON prs.procedure_type_id = pt.procedure_type_id
439          LEFT JOIN procedure_type AS ptt 
440          ON pt.parent = ptt.procedure_type_id
441          AND ptt.procedure_type = 'ord'
442        LEFT JOIN list_options AS lo
443          ON lo.list_id = 'proc_unit' AND pt.units = lo.option_id
444      WHERE po.patient_id=?";
445		
446    $result = sqlStatement($sql, array($pid) );
447  }
448	return $result;
449}
450
451
452function getActorData() {
453	global $pid;
454
455	$sql = " 
456	select fname, lname, DOB, sex, pid, street, city, state, postal_code, phone_contact
457	from patient_data
458	where pid=?";
459		
460	$result[0] = sqlStatement($sql, array($pid) );
461  
462  $sql2 = " 
463	SELECT * FROM users AS u LEFT JOIN facility AS f ON u.facility_id = f.id WHERE u.id=?";
464		
465	$result[1] = sqlStatement($sql2, array($_SESSION['authUserID']) );
466  
467  $sql3 = "
468  SELECT 
469    u.*
470  FROM
471    procedure_type AS pt 
472    LEFT JOIN procedure_order AS po 
473      ON po.procedure_type_id = pt.procedure_type_id 
474    LEFT JOIN forms AS f 
475      ON f.form_id = po.procedure_order_id 
476    LEFT JOIN list_options AS lo 
477      ON lo.title = f.form_name
478    LEFT JOIN users AS u
479    ON pt.lab_id = u.id
480  WHERE f.pid = ? 
481    AND lo.list_id = 'proc_type' 
482    AND lo.option_id = 'ord'
483    GROUP BY u.id";
484		
485	$result[2] = sqlStatement($sql3, array($pid) );
486  
487	return $result;
488}
489
490
491function getReportFilename() {
492  global $pid;
493
494  $sql = "
495    select fname, lname, pid
496    from patient_data
497    where pid=?";
498
499  $result = sqlQuery($sql, array($pid) );
500	$result_filename = $result['lname']."-".$result['fname']."-".$result['pid']."-".date("mdY",time());
501
502  return $result_filename;
503}
504
505?>