From: Rod Roark Date: Sun, 27 Jan 2013 01:47:26 +0000 (-0800) Subject: Additional fixes, mostly due to changes in schema for procedure orders and results. X-Git-Tag: whats-been-changed~436 X-Git-Url: https://repo.or.cz/w/openemr.git/commitdiff_plain/f5af71ad54a356f62fb7016d80d489a3eed92dcb Additional fixes, mostly due to changes in schema for procedure orders and results. --- diff --git a/interface/orders/gen_hl7_order.inc.php b/interface/orders/gen_hl7_order.inc.php index ae87d2b09..808d88d91 100644 --- a/interface/orders/gen_hl7_order.inc.php +++ b/interface/orders/gen_hl7_order.inc.php @@ -36,6 +36,7 @@ require_once("$srcdir/classes/Address.class.php"); require_once("$srcdir/classes/InsuranceCompany.class.php"); +require_once("$webserver_root/custom/code_types.inc.php"); function hl7Text($s) { // See http://www.interfaceware.com/hl7_escape_protocol.html: @@ -334,7 +335,7 @@ function gen_hl7_order($orderid, &$out) { $d0; // Diagnoses. Currently hard-coded for ICD9 and we'll surely want to make - // this more flexible. + // this more flexible (probably when some lab needs another diagnosis type). $setid2 = 0; if (!empty($porow['diagnoses'])) { $relcodes = explode(';', $porow['diagnoses']); @@ -342,13 +343,7 @@ function gen_hl7_order($orderid, &$out) { if ($codestring === '') continue; list($codetype, $code) = explode(':', $codestring); if ($codetype !== 'ICD9') continue; - $dcrow = sqlQuery("SELECT c.code_text FROM " . - "code_types AS ct, codes AS c WHERE " . - "ct.ct_key = ? AND c.code_type = ct.ct_id AND " . - "c.code = ? " . - "ORDER BY c.id LIMIT 1", - array($codetype, $code)); - $desc = empty($dcrow['code_text']) ? '' : $dcrow['code_text']; + $desc = lookup_code_descriptions($codestring); $out .= "DG1" . $d1 . ++$setid2 . // Set ID $d1 . // Diagnosis Coding Method diff --git a/interface/reports/clinical_reports.php b/interface/reports/clinical_reports.php index abf3a721c..ae28826ba 100644 --- a/interface/reports/clinical_reports.php +++ b/interface/reports/clinical_reports.php @@ -353,10 +353,10 @@ $sqlstmt = "select $sqlstmt=$sqlstmt.",r.id as id, r.date_modified AS prescriptions_date_modified, r.dosage as dosage, r.route as route, r.interval as hinterval, r.refills as refills, r.drug as drug, r.form as hform, r.size as size, r.unit as hunit, d.name as name, d.ndc_number as ndc_number,r.quantity as quantity"; - } + } + if(strlen($form_lab_results) > 0) { - - $sqlstmt = $sqlstmt.",pr.date AS procedure_result_date, + $sqlstmt = $sqlstmt.",pr.date AS procedure_result_date, pr.facility AS procedure_result_facility, pr.units AS procedure_result_units, pr.result AS procedure_result_result, @@ -365,17 +365,19 @@ $sqlstmt = "select pr.comments AS procedure_result_comments, pr.document_id AS procedure_result_document_id"; } + if ( $type == 'Procedure') { - $sqlstmt = $sqlstmt.",po.date_ordered AS procedure_order_date_ordered, + $sqlstmt = $sqlstmt.",po.date_ordered AS procedure_order_date_ordered, pt.standard_code AS procedure_type_standard_code, - pt.name as procedure_name, + pc.procedure_name as procedure_name, po.order_priority AS procedure_order_order_priority, po.order_status AS procedure_order_order_status, - po.encounter_id AS procedure_order_encounter, + po.encounter_id AS procedure_order_encounter, po.patient_instructions AS procedure_order_patient_instructions, po.activity AS procedure_order_activity, po.control_id AS procedure_order_control_id "; - } + } + if ( $type == 'Medical History') { $sqlstmt = $sqlstmt.",hd.date AS history_data_date, hd.tobacco AS history_data_tobacco, @@ -396,15 +398,18 @@ $sqlstmt = "select if(strlen($form_diagnosis) > 0 ){ $sqlstmt = $sqlstmt." left outer join lists as li on li.pid = pd.pid "; } - if ( $type == 'Procedure' ||( strlen($form_lab_results)!=0) ) { - $sqlstmt = $sqlstmt." left outer join procedure_order as po on po.patient_id = pd.pid - left outer join procedure_report as pp on pp.procedure_order_id = po.procedure_order_id - left outer join procedure_type as pt on pt.procedure_type_id = po.procedure_type_id "; - } - if (strlen($form_lab_results)!=0 ) { - $sqlstmt = $sqlstmt." left outer join procedure_result as pr on pr.procedure_report_id = pp.procedure_report_id - and pr.procedure_type_id = po.procedure_type_id "; - } + + if ( $type == 'Procedure' ||( strlen($form_lab_results)!=0) ) { + $sqlstmt = $sqlstmt." left outer join procedure_order as po on po.patient_id = pd.pid + left outer join procedure_order_code as pc on pc.procedure_order_id = po.procedure_order_id + left outer join procedure_report as pp on pp.procedure_order_id = po.procedure_order_id + left outer join procedure_type as pt on pt.procedure_code = pc.procedure_code and pt.lab_id = po.lab_id "; + } + + if (strlen($form_lab_results)!=0 ) { + $sqlstmt = $sqlstmt." left outer join procedure_result as pr on pr.procedure_report_id = pp.procedure_report_id "; + } + if(strlen($form_drug_name)!=0) { $sqlstmt=$sqlstmt." left outer join prescriptions AS r on r.patient_id=pd.pid LEFT OUTER JOIN drugs AS d ON d.drug_id = r.drug_id"; diff --git a/library/classes/ClinicalTypes/LabResult.php b/library/classes/ClinicalTypes/LabResult.php index e57f1b837..b3f60c896 100644 --- a/library/classes/ClinicalTypes/LabResult.php +++ b/library/classes/ClinicalTypes/LabResult.php @@ -35,17 +35,25 @@ class LabResult extends ClinicalType // search through vitals to find the most recent lab result in the date range // if the result value is within range using Range->test(val), return true $sql = "SELECT procedure_result.result, procedure_result.date " . - "FROM `procedure_type`, " . - "`procedure_order`, " . - "`procedure_report`, " . - "`procedure_result` " . - "WHERE procedure_type.procedure_type_id = procedure_order.procedure_type_id " . - "AND procedure_order.procedure_order_id = procedure_report.procedure_order_id " . - "AND procedure_report.procedure_report_id = procedure_result.procedure_report_id " . - "AND ( procedure_type.standard_code = ? OR procedure_type.procedure_code = ? ) " . - "AND procedure_report.date_collected >= ? " . - "AND procedure_report.date_collected <= ? " . - "AND procedure_order.patient_id = ? "; + "FROM " . + "procedure_type, " . + "procedure_order, " . + "procedure_order_code, " . + "procedure_report, " . + "procedure_result " . + "WHERE " . + "procedure_type.lab_id = procedure_order.lab_id AND " . + "procedure_type.procedure_type = 'ord' AND " . + "procedure_type.procedure_code = procedure_order_code.procedure_code AND " . + "( procedure_type.standard_code = ? OR procedure_type.procedure_code = ? ) AND " . + "procedure_order_code.procedure_order_id = procedure_order.procedure_order_id AND " . + "procedure_order_code.procedure_order_seq = procedure_report.procedure_order_seq AND " . + "procedure_order.procedure_order_id = procedure_report.procedure_order_id AND " . + "procedure_report.procedure_report_id = procedure_result.procedure_report_id AND " . + "procedure_report.date_collected >= ? AND " . + "procedure_report.date_collected <= ? AND " . + "procedure_order.patient_id = ? "; + if ( $range->lowerBound != Range::NEG_INF ) { $sql .= "AND procedure_result.result >= ? "; } diff --git a/library/classes/rulesets/Amc/library/AbstractAmcReport.php b/library/classes/rulesets/Amc/library/AbstractAmcReport.php index 654e12066..d87dbc840 100644 --- a/library/classes/rulesets/Amc/library/AbstractAmcReport.php +++ b/library/classes/rulesets/Amc/library/AbstractAmcReport.php @@ -185,16 +185,15 @@ abstract class AbstractAmcReport implements RsReportIF array_push($sqlBindArray, $patient->id, $begin, $end); break; case "labs": - $sql = "SELECT procedure_result.result " . - "FROM `procedure_type`, " . - "`procedure_order`, " . - "`procedure_report`, " . - "`procedure_result` " . - "WHERE procedure_type.procedure_type_id = procedure_order.procedure_type_id " . - "AND procedure_order.procedure_order_id = procedure_report.procedure_order_id " . - "AND procedure_report.procedure_report_id = procedure_result.procedure_report_id " . - "AND procedure_order.patient_id = ? " . - "AND procedure_report.date_collected >= ? AND procedure_report.date_collected <= ?"; + $sql = "SELECT procedure_result.result FROM " . + "procedure_order, " . + "procedure_report, " . + "procedure_result " . + "WHERE " . + "procedure_order.patient_id = ? AND " . + "procedure_order.procedure_order_id = procedure_report.procedure_order_id AND " . + "procedure_report.procedure_report_id = procedure_result.procedure_report_id AND " . + "procedure_report.date_collected >= ? AND procedure_report.date_collected <= ?"; array_push($sqlBindArray, $patient->id, $begin, $end); break; } diff --git a/library/clinical_rules.php b/library/clinical_rules.php index a1781989a..e1fe09aaa 100644 --- a/library/clinical_rules.php +++ b/library/clinical_rules.php @@ -1593,15 +1593,20 @@ function exist_procedure_item($patient_id,$proc_title,$proc_code,$result_comp,$r // collect specific items (use both title and/or codes) that fulfill request $sqlBindArray=array(); - $sql_query = "SELECT procedure_result.result " . - "FROM `procedure_type`, " . - "`procedure_order`, " . - "`procedure_report`, " . - "`procedure_result` " . - "WHERE procedure_type.procedure_type_id = procedure_order.procedure_type_id " . - "AND procedure_order.procedure_order_id = procedure_report.procedure_order_id " . - "AND procedure_report.procedure_report_id = procedure_result.procedure_report_id " . - "AND "; + $sql_query = "SELECT procedure_result.result FROM " . + "procedure_order_code, " . + "procedure_order, " . + "procedure_type, " . + "procedure_report, " . + "procedure_result " . + "WHERE " . + "procedure_order_code.procedure_code = procedure_type.procedure_code AND " . + "procedure_order.procedure_order_id = procedure_order_code.procedure_order_id AND " . + "procedure_order.lab_id procedure_type.lab_id AND " . + "procedure_report.procedure_order_id = procedure_order.procedure_order_id AND " . + "procedure_report.procedure_order_seq = procedure_order_code.procedure_order_seq AND " . + "procedure_result.procedure_report_id = procedure_report.procedure_report_id AND " . + "procedure_type.procedure_type = 'ord' AND "; foreach ($codes as $tem) { $sql_query .= "( ( (procedure_type.standard_code = ? AND procedure_type.standard_code != '') " . "OR (procedure_type.procedure_code = ? AND procedure_type.procedure_code != '') ) OR "; @@ -1611,6 +1616,7 @@ function exist_procedure_item($patient_id,$proc_title,$proc_code,$result_comp,$r "AND procedure_result.result " . $compSql . " ? " . "AND " . add_escape_custom($patient_id_label) . " = ? " . $dateSql; array_push($sqlBindArray,$proc_title,$result_data,$patient_id); + $sql = sqlStatementCdrEngine($sql_query,$sqlBindArray); // See if number of returned items passes the comparison diff --git a/library/sql-ccr.inc b/library/sql-ccr.inc index de6aa344b..20677bd6d 100644 --- a/library/sql-ccr.inc +++ b/library/sql-ccr.inc @@ -217,8 +217,13 @@ function getProcedureData() { ON prs.procedure_report_id = prp.procedure_report_id LEFT JOIN procedure_order AS po ON prp.procedure_order_id = po.procedure_order_id + LEFT JOIN procedure_order_code AS poc + ON poc.procedure_order_id = po.procedure_order_id + AND poc.procedure_order_seq = prp.procedure_order_seq LEFT JOIN procedure_type AS pt - ON prs.procedure_type_id = pt.procedure_type_id + ON pt.lab_id = po.lab_id + AND pt.procedure_code = prs.result_code + AND pt.procedure_type = 'res' LEFT JOIN procedure_type AS ptt ON pt.parent = ptt.procedure_type_id AND ptt.procedure_type = 'ord' @@ -270,9 +275,14 @@ function getProcedureData() { LEFT JOIN procedure_report AS prp ON prs.procedure_report_id = prp.procedure_report_id LEFT JOIN procedure_order AS po - ON prp.procedure_order_id = po.procedure_order_id + ON prp.procedure_order_id = po.procedure_order_id + LEFT JOIN procedure_order_code AS poc + ON poc.procedure_order_id = po.procedure_order_id + AND poc.procedure_order_seq = prp.procedure_order_seq LEFT JOIN procedure_type AS pt - ON prs.procedure_type_id = pt.procedure_type_id + ON pt.lab_id = po.lab_id + AND pt.procedure_code = prs.result_code + AND pt.procedure_type = 'res' LEFT JOIN procedure_type AS ptt ON pt.parent = ptt.procedure_type_id AND ptt.procedure_type = 'ord' @@ -402,9 +412,14 @@ function getResultData() { ON prs.procedure_report_id = prp.procedure_report_id LEFT JOIN procedure_order AS po ON prp.procedure_order_id = po.procedure_order_id + LEFT JOIN procedure_order_code AS poc + ON poc.procedure_order_id = po.procedure_order_id + AND poc.procedure_order_seq = prp.procedure_order_seq LEFT JOIN procedure_type AS pt - ON prs.procedure_type_id = pt.procedure_type_id - LEFT JOIN procedure_type AS ptt + ON pt.lab_id = po.lab_id + AND pt.procedure_code = prs.result_code + AND pt.procedure_type = 'res' + LEFT JOIN procedure_type AS ptt ON pt.parent = ptt.procedure_type_id AND ptt.procedure_type = 'ord' LEFT JOIN list_options AS lo @@ -431,9 +446,14 @@ function getResultData() { ON prs.procedure_report_id = prp.procedure_report_id LEFT JOIN procedure_order AS po ON prp.procedure_order_id = po.procedure_order_id + LEFT JOIN procedure_order_code AS poc + ON poc.procedure_order_id = po.procedure_order_id + AND poc.procedure_order_seq = prp.procedure_order_seq LEFT JOIN procedure_type AS pt - ON prs.procedure_type_id = pt.procedure_type_id - LEFT JOIN procedure_type AS ptt + ON pt.lab_id = po.lab_id + AND pt.procedure_code = prs.result_code + AND pt.procedure_type = 'res' + LEFT JOIN procedure_type AS ptt ON pt.parent = ptt.procedure_type_id AND ptt.procedure_type = 'ord' LEFT JOIN list_options AS lo @@ -463,21 +483,20 @@ function getActorData() { $sql3 = " SELECT - u.* + u.ppid AS id, u.name AS lname, '' AS fname, '' AS city, '' AS state, '' AS zip, '' AS phone FROM - procedure_type AS pt - LEFT JOIN procedure_order AS po - ON po.procedure_type_id = pt.procedure_type_id - LEFT JOIN forms AS f + procedure_order AS po + LEFT JOIN forms AS f ON f.form_id = po.procedure_order_id + AND f.formdir = 'procedure_order' LEFT JOIN list_options AS lo ON lo.title = f.form_name - LEFT JOIN users AS u - ON pt.lab_id = u.id + LEFT JOIN procedure_providers AS u + ON po.lab_id = u.ppid WHERE f.pid = ? AND lo.list_id = 'proc_type' AND lo.option_id = 'ord' - GROUP BY u.id"; + GROUP BY u.ppid"; $result[2] = sqlStatement($sql3, array($pid) ); diff --git a/patients/get_lab_results.php b/patients/get_lab_results.php index e6afe49aa..c667064a4 100644 --- a/patients/get_lab_results.php +++ b/patients/get_lab_results.php @@ -35,41 +35,29 @@ require_once('../interface/globals.php'); require_once('../library/options.inc.php'); - $selects = "po.procedure_order_id, po.date_ordered, " . - "po.procedure_type_id AS order_type_id, pt1.name AS procedure_name, " . - "ptrc.name AS result_category_name, " . - "pt2.procedure_type AS result_type, " . - "pt2.procedure_type_id AS result_type_id, pt2.name AS result_name, " . - "pt2.units AS result_def_units, pt2.range AS result_def_range, " . - "pt2.description AS result_description, lo.title AS units_name, " . - "pr.procedure_report_id, pr.date_report, pr.date_collected, pr.specimen_num, pr.report_status, pr.review_status, " . - "ps.procedure_result_id, ps.abnormal, ps.result, ps.range, ps.result_status, " . - "ps.facility, ps.comments"; - - $joins = "LEFT JOIN procedure_type AS pt1 ON pt1.procedure_type_id = po.procedure_type_id "; - $joins .= "LEFT JOIN procedure_type AS ptrc ON ptrc.procedure_type_id = pt1.parent "; - $joins .= "AND ptrc.procedure_type LIKE 'grp%' " . - "LEFT JOIN procedure_type AS pt2 ON " . - "( ( ptrc.procedure_type_id IS NULL AND ( pt2.parent = po.procedure_type_id " . - "OR pt2.procedure_type_id = po.procedure_type_id ) ) OR "; - $joins .= "( pt2.procedure_type_id IS NOT NULL AND pt2.parent = pt1.procedure_type_id ) " . - ") AND ( pt2.procedure_type LIKE 'res%' OR pt2.procedure_type LIKE 'rec%' ) " . - "LEFT JOIN list_options AS lo ON list_id = 'proc_unit' AND option_id = pt2.units " . - "LEFT JOIN procedure_report AS pr ON pr.procedure_order_id = po.procedure_order_id " . - "LEFT JOIN procedure_result AS ps ON ps.procedure_report_id = pr.procedure_report_id " . - "AND ps.procedure_type_id = pt2.procedure_type_id"; - - $orderby ="po.date_ordered, po.procedure_order_id, pr.procedure_report_id, " . - "ptrc.seq, ptrc.name, ptrc.procedure_type_id, " . - "pt2.seq, pt2.name, pt2.procedure_type_id"; - - $where = "1 = 1"; + $selects = + "po.procedure_order_id, po.date_ordered, pc.procedure_order_seq, " . + "pt1.procedure_type_id AS order_type_id, pc.procedure_name, " . + "pr.procedure_report_id, pr.date_report, pr.date_collected, pr.specimen_num, " . + "pr.report_status, pr.review_status"; + + $joins = + "JOIN procedure_order_code AS pc ON pc.procedure_order_id = po.procedure_order_id " . + "LEFT JOIN procedure_type AS pt1 ON pt1.lab_id = po.lab_id AND pt1.procedure_code = pc.procedure_code " . + "LEFT JOIN procedure_report AS pr ON pr.procedure_order_id = po.procedure_order_id AND " . + "pr.procedure_order_seq = pc.procedure_order_seq"; + + $orderby = + "po.date_ordered, po.procedure_order_id, " . + "pc.procedure_order_seq, pr.procedure_report_id"; + + $where = "1 = 1"; - $res = sqlStatement("SELECT $selects " . - "FROM procedure_order AS po $joins " . - "WHERE po.patient_id = ? AND $where " . - "ORDER BY $orderby", array($pid)); - + $res = sqlStatement("SELECT $selects " . + "FROM procedure_order AS po $joins " . + "WHERE po.patient_id = ? AND $where " . + "ORDER BY $orderby", array($pid)); + if(sqlNumRows($res)>0) { ?> @@ -87,7 +75,41 @@ "; echo "".htmlspecialchars($date[1]."/".$date[2]."/".$date[0],ENT_NOQUOTES).""; echo "".htmlspecialchars($row['procedure_name'],ENT_NOQUOTES).""; - echo "".htmlspecialchars($row['result_name'],ENT_NOQUOTES).""; - echo "".generate_display_field(array('data_type'=>'1','list_id'=>'proc_res_abnormal'),$row['abnormal']).""; + echo "".htmlspecialchars($rrow['name'],ENT_NOQUOTES).""; + echo "".generate_display_field(array('data_type'=>'1','list_id'=>'proc_res_abnormal'),$rrow['abnormal']).""; echo "".htmlspecialchars($row['result'],ENT_NOQUOTES).""; - echo "".htmlspecialchars($row['result_def_range'],ENT_NOQUOTES).""; - echo "".generate_display_field(array('data_type'=>'1','list_id'=>'proc_unit'),$row['result_def_units']).""; - echo "".generate_display_field(array('data_type'=>'1','list_id'=>'proc_res_status'),$row['result_status']).""; + echo "".htmlspecialchars($rrow['pt2_range'],ENT_NOQUOTES).""; + echo "".generate_display_field(array('data_type'=>'1','list_id'=>'proc_unit'),$rrow['pt2_units']).""; + echo "".generate_display_field(array('data_type'=>'1','list_id'=>'proc_res_status'),$rrow['result_status']).""; echo "".generate_display_field(array('data_type'=>'1','list_id'=>'proc_rep_status'),$row['report_status']).""; echo ""; - } + + } + + } + echo ""; } else diff --git a/version.php b/version.php index d7cd68d5e..88cdb1948 100644 --- a/version.php +++ b/version.php @@ -17,7 +17,7 @@ $v_realpatch = '0'; // is a database change in the course of development. It is used // internally to determine when a database upgrade is needed. // -$v_database = 85; +$v_database = 86; // Access control version identifier, this is to be incremented whenever there // is a access control change in the course of development. It is used