4 * Report tracking, storing and viewing functions using the report_results sql table.
6 * Supports generic tracking, storing and viewing of reports by utilizing a vertical
7 * table entitled report_results. This allows flexible placement of tokens for report
8 * setting etc. Also supports itemization of results (per patient tracking).
9 * <pre>Tokens that are reserved include:
10 * 'bookmark' - Allows bookmarking of a new report id (used to allow tracking
11 * progress via ajax calls). If exist, value is always set to '1'.
12 * 'progress' - Either set to 'pending' or 'complete'.
13 * 'type' - Set to type of report
14 * 'total_items' - Set to total number of items that will be processed (ie. such as patients)
15 * 'progress_items' - Set to number of items (ie. such as patients)
16 * 'data' - Contains the data of the report
17 * 'date_report' - Set to date of the report (date and time)
18 * 'date_report_complete' - Set to date of the report completion (date and time)
21 * Copyright (C) 2012 Brady Miller <brady.g.miller@gmail.com>
23 * LICENSE: This program is free software; you can redistribute it and/or
24 * modify it under the terms of the GNU General Public License
25 * as published by the Free Software Foundation; either version 2
26 * of the License, or (at your option) any later version.
27 * This program is distributed in the hope that it will be useful,
28 * but WITHOUT ANY WARRANTY; without even the implied warranty of
29 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
30 * GNU General Public License for more details.
31 * You should have received a copy of the GNU General Public License
32 * along with this program. If not, see <http://opensource.org/licenses/gpl-license.php>;.
35 * @author Brady Miller <brady.g.miller@gmail.com>
36 * @link http://www.open-emr.org
40 * Return listing of report results.
42 * @param timestamp $start Start of date range
43 * @param timestamp $end End of date range
44 * @return sql-query Listing of report results
46 function listingReportDatabase($start_date = '', $end_date = '')
49 // set $end_date to today's date if empty
50 $end_date = ($end_date) ? $end_date : date('Y-m-d H:i:s');
52 // Collect pertinent information as a pivot table (ie. converting vertical to horizontal row)
53 if (empty($start_date)) {
54 $res = sqlStatement("SELECT *, TIMESTAMPDIFF(MINUTE,pt.date_report,pt.date_report_complete) as `report_time_processing`
57 MAX(if( `field_id` = 'date_report', `field_value`, 0 )) as `date_report`,
58 MAX(if( `field_id` = 'date_report_complete', `field_value`, 0 )) as `date_report_complete`,
59 MAX(if( `field_id` = 'progress', `field_value`, 0 )) as `progress`,
60 MAX(if( `field_id` = 'total_items', `field_value`, 0 )) as `total_items`,
61 MAX(if( `field_id` = 'progress_items', `field_value`, 0 )) as `progress_items`,
62 MAX(if( `field_id` = 'type', `field_value`, 0 )) as `type`
66 WHERE pt.date_report < ?
67 ORDER BY pt.report_id", array($end_date));
69 $res = sqlStatement("SELECT *, TIMESTAMPDIFF(MINUTE,pt.date_report,pt.date_report_complete) as `report_time_processing`
72 MAX(if( `field_id` = 'date_report', `field_value`, 0 )) as `date_report`,
73 MAX(if( `field_id` = 'date_report_complete', `field_value`, 0 )) as `date_report_complete`,
74 MAX(if( `field_id` = 'progress', `field_value`, 0 )) as `progress`,
75 MAX(if( `field_id` = 'total_items', `field_value`, 0 )) as `total_items`,
76 MAX(if( `field_id` = 'progress_items', `field_value`, 0 )) as `progress_items`,
77 MAX(if( `field_id` = 'type', `field_value`, 0 )) as `type`
81 WHERE pt.date_report > ? AND pt.date_report < ?
82 ORDER BY pt.report_id", array($start_date,$end_date));
89 * Simply reserves a report id for use in the report results tracking/storing/viewing item in database..
91 * @return integer Report id that was assigned in database
93 function bookmarkReportDatabase()
96 // Retrieve a new report id
97 $query = sqlQuery("SELECT max(`report_id`) as max_report_id FROM `report_results`");
101 $new_report_id = $query['max_report_id'] + 1;
104 // Set the bookmark token
105 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($new_report_id,"bookmark",1));
107 return $new_report_id;
111 * Initiate a report results tracking/storing/viewing item in database.
113 * @param string $type Report type identifier
114 * @param array $fields Array containing pertinent report details (Do NOT use 'bookmark', 'progress','type','progress_patients', 'data', 'date_report' or 'no_json_support' as keys in array; they will be ignored)
115 * @param integer $report_id Report id (if have already bookmarked a report id)
116 * @return integer Report id that is assigned to the report
118 function beginReportDatabase($type, $fields, $report_id = null)
121 // Retrieve a new report id, if needed.
122 if (empty($report_id)) {
123 $query = sqlQuery("SELECT max(`report_id`) as max_report_id FROM `report_results`");
127 $new_report_id = $query['max_report_id'] + 1;
130 $new_report_id = $report_id;
133 // Set the required tokens
134 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($new_report_id,"progress","pending"));
135 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($new_report_id,"type",$type));
136 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($new_report_id,"progress_items","0"));
137 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($new_report_id,"data",""));
138 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($new_report_id,"date_report",date("Y-m-d H:i:s")));
140 // Set the fields tokens
141 if (!empty($fields)) {
142 foreach ($fields as $key => $value) {
143 // skip the special tokens
147 ($key == "progress") ||
148 ($key == "progress_items") ||
149 ($key == "total_items") ||
150 ($key == "date_report") ||
151 ($key == "date_report_complete") ||
158 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($new_report_id,$key,$value));
162 // Return the report id
163 return $new_report_id;
167 * Insert total items to process in database.
168 * For performance reasons, it is assumed that the total_items does not already exists in current database entry.
170 * @param integer $report_id Report id
171 * @param integer $total_items Total number of items that will be processed
173 function setTotalItemsReportDatabase($report_id, $total_items)
175 // Insert the total items that are to be processed
176 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($report_id,"total_items",$total_items));
180 * Update report results in database(basically update number of items (patients) that has been processed in pending reports).
181 * For performance reasons, it is assumed that the progress_items token already exists in current database entry.
183 * @param integer $report_id Report id
184 * @param integer $items_processed Number of items that have been processed
186 function updateReportDatabase($report_id, $items_processed)
188 // Update the items that have been processed
189 sqlStatement("UPDATE `report_results` SET `field_value`=? WHERE `report_id`=? AND `field_id`='progress_items'", array($items_processed,$report_id));
193 * Store (finished) report results (in json format) in database.
194 * For performance reasons, it is assumed that the data and progress tokens already exists in current database entry.
195 * For performance reasons, it is assumed that the date_report_complete does not already exists in current database entry.
197 * @param integer $report_id Report id
198 * @param string $data Report results/data
200 function finishReportDatabase($report_id, $data)
204 sqlStatement("UPDATE `report_results` SET `field_value`=? WHERE `report_id`=? AND `field_id`='data'", array($data,$report_id));
206 // Record the finish date/time
207 sqlStatement("INSERT INTO `report_results` (`report_id`,`field_id`,`field_value`) VALUES (?,?,?)", array ($report_id,"date_report_complete",date("Y-m-d H:i:s")));
209 // Set progress to complete
210 sqlStatement("UPDATE `report_results` SET `field_value`='complete' WHERE `report_id`=? AND `field_id`='progress'", array($report_id));
214 * Collect report results from database.
216 * @param integer $report_id Report id
217 * @return array Array of id/values for a report
219 function collectReportDatabase($report_id)
222 // Collect the rows of data
223 $res = sqlStatement("SELECT * FROM `report_results` WHERE `report_id`=?", array($report_id));
225 // Convert data into an array
226 $final_array = array();
227 while ($row = sqlFetchArray($res)) {
228 $final_array = array_merge($final_array, array($row['field_id'] => $row['field_value']));
235 * Get status of report from database.
237 * @param integer $report_id Report id
238 * @return string Status report (PENDING, COMPLETE, or return a string with progress)
240 function getStatusReportDatabase($report_id)
243 // Collect the pertinent rows of data
244 $res = sqlStatement("SELECT `field_id`, `field_value` FROM `report_results` WHERE `report_id`=? AND (`field_id`='progress' OR `field_id`='total_items' OR `field_id`='progress_items')", array($report_id));
246 // If empty, then just return Pending, since stil haven't likely created the entries yet
247 if (sqlNumRows($res) < 1) {
251 // Place into an array for quick processing
252 $final_array = array();
253 while ($row = sqlFetchArray($res)) {
254 $final_array = array_merge($final_array, array($row['field_id'] => $row['field_value']));
257 if ($final_array['progress'] == "complete") {
261 $final_array['progress_items'] = ($final_array['progress_items']) ? $final_array['progress_items'] : 0;
262 return $final_array['progress_items'] . " / " . $final_array['total_items'] . " " . xl("Patients");
267 * Insert itemization item into database.
269 * @param integer $report_id Report id
270 * @param integer $itemized_test_id Itemized test id
271 * @param integer $pass 0 is fail, 1 is pass, 2 is exclude
272 * @param integer $patient_id Patient pid
273 * @param integer $$numerator_label Numerator label (if applicable)
275 function insertItemReportTracker($report_id, $itemized_test_id, $pass, $patient_id, $numerator_label = '')
277 $sqlParameters = array($report_id,$itemized_test_id,$numerator_label,$pass,$patient_id);
278 sqlStatementCdrEngine("INSERT INTO `report_itemized` (`report_id`,`itemized_test_id`,`numerator_label`,`pass`,`pid`) VALUES (?,?,?,?,?)", $sqlParameters);
282 * Collect a rules display title for itemized report.
284 * @param integer $report_id Report id
285 * @param integer $itemized_test_id Itemized test id
286 * @param integer $numerator_label Numerator label (if applicable)
287 * @return string/boolean Rule title for itemization display (false if nothing found)
289 function collectItemizedRuleDisplayTitle($report_id, $itemized_test_id, $numerator_label = '')
292 $report_view = collectReportDatabase($report_id);
293 $type_report = $report_view['type'];
294 $dataSheet = json_decode($report_view['data'], true);
295 foreach ($dataSheet as $row) {
296 if (isset($row['is_main']) || isset($row['is_sub'])) {
297 if (isset($row['is_main'])) {
299 $dispTitle = generate_display_field(array('data_type' => '1','list_id' => 'clinical_rules'), $row['id']);
302 if (($row['itemized_test_id'] == $itemized_test_id) && (($row['numerator_label'] ?? '') == $numerator_label)) {
303 // We have a hit, build on the $dispTitle created above
304 if (isset($row['is_main'])) {
305 $tempCqmAmcString = "";
306 if (($type_report == "cqm") || ($type_report == "cqm_2011") || ($type_report == "cqm_2014")) {
307 if (!empty($row['cqm_pqri_code'])) {
308 $tempCqmAmcString .= " " . xlt('PQRI') . ":" . text($row['cqm_pqri_code']) . " ";
311 if (!empty($row['cqm_nqf_code'])) {
312 $tempCqmAmcString .= " " . xlt('NQF') . ":" . text($row['cqm_nqf_code']) . " ";
316 if ($type_report == "amc") {
317 if (!empty($row['amc_code'])) {
318 $tempCqmAmcString .= " " . xlt('AMC-2011') . ":" . text($row['amc_code']) . " ";
321 if (!empty($row['amc_code_2014'])) {
322 $tempCqmAmcString .= " " . xlt('AMC-2014') . ":" . text($row['amc_code_2014']) . " ";
326 if ($type_report == "amc_2011") {
327 if (!empty($row['amc_code'])) {
328 $tempCqmAmcString .= " " . xlt('AMC-2011') . ":" . text($row['amc_code']) . " ";
332 if ($type_report == "amc_2014_stage1") {
333 if (!empty($row['amc_code_2014'])) {
334 $tempCqmAmcString .= " " . xlt('AMC-2014 Stage I') . ":" . text($row['amc_code_2014']) . " ";
338 if ($type_report == "amc_2014_stage2") {
339 if (!empty($row['amc_code_2014'])) {
340 $tempCqmAmcString .= " " . xlt('AMC-2014 Stage II') . ":" . text($row['amc_code_2014']) . " ";
344 if (!empty($tempCqmAmcString)) {
345 $dispTitle .= "(" . $tempCqmAmcString . ")";
348 if (!(empty($row['concatenated_label']))) {
349 $dispTitle .= ", " . xlt($row['concatenated_label']) . " ";
351 } else { // isset($row['is_sub']
352 $dispTitle .= " - " . generate_display_field(array('data_type' => '1','list_id' => 'rule_action_category'), $row['action_category']);
353 $dispTitle .= ": " . generate_display_field(array('data_type' => '1','list_id' => 'rule_action'), $row['action_item']);
365 * Collect patient listing from CDR reports itemization.
367 * @param integer $report_id Report id
368 * @param integer $itemized_test_id Itemized test id
369 * @param string $pass options are 'fail', 'pass', 'exclude', 'init_patients', 'exception' and 'all'
370 * @param integer $numerator_label Numerator label (if applicable)
371 * @param integer $sqllimit Sql query pagination info
372 * @param integer $fstart Sql query pagination info
373 * @return array/integer Array list or a count
375 function collectItemizedPatientsCdrReport($report_id, $itemized_test_id, $pass = 'all', $numerator_label = '', $count = false, $sqllimit = 'all', $fstart = 0)
379 $given = " COUNT(DISTINCT `patient_data`.`pid`) AS total_listings ";
381 $given = " DISTINCT `patient_data`.*, DATE_FORMAT(`patient_data`.`DOB`,'%m/%d/%Y') as DOB_TS ";
384 $orderby = " `patient_data`.`lname` ASC, `patient_data`.`fname` ASC ";
397 case "init_patients":
405 $sqlParameters = array($report_id,$itemized_test_id,$numerator_label);
407 if ($pass == "all") {
408 $sql_where = " WHERE `report_itemized`.`pass` != 3 AND `report_itemized`.`report_id` = ? AND `report_itemized`.`itemized_test_id` = ? AND `report_itemized`.`numerator_label` = ? ";
409 } elseif ($pass == "fail") {
410 $exlPidArr = array();
411 $exludeResult = collectItemizedPatientsCdrReport($report_id, $itemized_test_id, 'exclude', $numerator_label, false, $sqllimit, $fstart);
412 foreach ($exludeResult as $exlResArr) {
413 $exlPidArr[] = $exlResArr['pid'];
416 $sql_where = " WHERE `report_itemized`.`report_id` = ? AND `report_itemized`.`itemized_test_id` = ? AND `report_itemized`.`numerator_label` = ? AND `report_itemized`.`pass` = ? ";
418 if (count($exlPidArr) > 0) {
419 $exlPids = implode(",", $exlPidArr);
420 $sql_where .= " AND patient_data.pid NOT IN(" . add_escape_custom($exlPids) . ") ";
423 array_push($sqlParameters, $pass_sql);
425 $sql_where = " WHERE `report_itemized`.`report_id` = ? AND `report_itemized`.`itemized_test_id` = ? AND `report_itemized`.`numerator_label` = ? AND `report_itemized`.`pass` = ? ";
426 array_push($sqlParameters, $pass_sql);
429 $sql_query = "SELECT " . $given . " FROM `patient_data` JOIN `report_itemized` ON `patient_data`.`pid` = `report_itemized`.`pid` " . $sql_where . " ORDER BY " . $orderby;
431 if ($sqllimit != "all") {
432 $sql_query .= " limit " . escape_limit($fstart) . ", " . escape_limit($sqllimit);
436 $rez = sqlQueryCdrEngine($sql_query, $sqlParameters);
437 return $rez['total_listings'];
439 $rez = sqlStatementCdrEngine($sql_query, $sqlParameters);
440 // create array of listing for return
441 $returnval = array();
442 for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
443 $returnval[$iter] = $row;