library/clinical_rules.php

   1 <?php
   2 // Copyright (C) 2011 by following authors:
   3 //   -Brady Miller <brady@sparmy.com>
   4 //   -Ensofttek, LLC
   5 //   -Medical Information Integration, LLC
   6 //
   7 // This program is free software; you can redistribute it and/or
   8 // modify it under the terms of the GNU General Public License
   9 // as published by the Free Software Foundation; either version 2
  10 // of the License, or (at your option) any later version.
  11
  12 // Functions are kept here that will support the clinical rules.
  13
  14 require_once(dirname(__FILE__) . "/patient.inc");
  15 require_once(dirname(__FILE__) . "/forms.inc");
  16 require_once(dirname(__FILE__) . "/formdata.inc.php");
  17 require_once(dirname(__FILE__) . "/options.inc.php");
  18
  19 // Display the clinical summary widget.
  20 // Parameters:
  21 //   $patient_id - pid of selected patient
  22 //   $mode       - choose either 'reminders-all' or 'reminders-due' (required)
  23 //   $dateTarget - target date. If blank then will test with current date as target.
  24 //   $organize_mode - Way to organize the results (default or plans)
  25 function clinical_summary_widget($patient_id,$mode,$dateTarget='',$organize_mode='default') {
  26
  27   // Set date to current if not set
  28   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
  29
  30   // Collect active actions
  31   $actions = test_rules_clinic('','passive_alert',$dateTarget,$mode,$patient_id,'',$organize_mode);
  32
  33   // Display the actions
  34   foreach ($actions as $action) {
  35
  36     // Deal with plan names first
  37     if ($action['is_plan']) {
  38       echo "<br><b>";
  39       echo htmlspecialchars( xl("Plan"), ENT_NOQUOTES) . ": ";
  40       echo generate_display_field(array('data_type'=>'1','list_id'=>'clinical_plans'),$action['id']);
  41       echo "</b><br>";
  42       continue;
  43     }
  44
  45     if ($action['custom_flag']) {
  46       // Start link for reminders that use the custom rules input screen
  47       echo "<a href='../rules/patient_data.php?category=" .
  48         htmlspecialchars( $action['category'], ENT_QUOTES) . "&item=" .
  49         htmlspecialchars( $action['item'], ENT_QUOTES) .
  50         "' class='iframe  medium_modal' onclick='top.restoreSession()'>";
  51     }
  52     else if ($action['clin_rem_link']) {
  53       // Start link for reminders that use the custom rules input screen
  54       echo "<a href='../../../" . $action['reminder_message'] .
  55         "' class='iframe  medium_modal' onclick='top.restoreSession()'>";
  56     }
  57     else {
  58       // continue, since no link will be created
  59     }
  60
  61     // Display Reminder Details
  62     echo generate_display_field(array('data_type'=>'1','list_id'=>'rule_action_category'),$action['category']) .
  63       ": " . generate_display_field(array('data_type'=>'1','list_id'=>'rule_action'),$action['item']);
  64
  65     if ($action['custom_flag'] || $action['clin_rem_link']) {
  66       // End link for reminders that use an html link
  67       echo "</a>";
  68     }
  69
  70     // Display due status
  71     if ($action['due_status']) {
  72       // Color code the status (red for past due, purple for due, green for not due and black for soon due)
  73       if ($action['due_status'] == "past_due") {
  74         echo "&nbsp;&nbsp;(<span style='color:red'>";
  75       }
  76       else if ($action['due_status'] == "due") {
  77         echo "&nbsp;&nbsp;(<span style='color:purple'>";
  78       }
  79       else if ($action['due_status'] == "not_due") {
  80         echo "&nbsp;&nbsp;(<span style='color:green'>";
  81       }
  82       else {
  83         echo "&nbsp;&nbsp;(<span>";
  84       }
  85       echo generate_display_field(array('data_type'=>'1','list_id'=>'rule_reminder_due_opt'),$action['due_status']) . "</span>)<br>";
  86     }
  87     else {
  88       echo "<br>";
  89     }
  90
  91   }
  92 }
  93
  94 // Test the clinic rules of entire clinic and create a report or patient reminders
  95 //  (can also test on one patient or patients of one provider)
  96 // Parameters:
  97 //   $provider   - id of a selected provider. If blank, then will test entire clinic. If 'collate_outer' or
  98 //                   'collate_inner', then will test each provider in entire clinic; outer will nest plans
  99 //                   inside collated providers, while inner will nest the providers inside the plans (note
 100 //                   inner and outer are only different if organize_mode is set to plans).
 101 //   $type       - rule filter (active_alert,passive_alert,cqm,amc,patient_reminder). If blank then will test all rules.
 102 //   $dateTarget - target date. If blank then will test with current date as target.
 103 //   $mode       - choose either 'report' or 'reminders-all' or 'reminders-due' (required)
 104 //   $patient_id - pid of patient. If blank then will check all patients.
 105 //   $plan       - test for specific plan only
 106 //   $organize_mode - Way to organize the results (default, plans)
 107 //     'default':
 108 //       Returns a two-dimensional array of results organized by rules:
 109 //         reminders-due mode - returns an array of reminders (action array elements plus a 'pid' and 'due_status')
 110 //         reminders-all mode - returns an array of reminders (action array elements plus a 'pid' and 'due_status')
 111 //         report mode    - returns an array of rows for the Clinical Quality Measures (CQM) report
 112 //     'plans':
 113 //       Returns similar to default, but organizes by the active plans
 114 //
 115 function test_rules_clinic($provider='',$type='',$dateTarget='',$mode='',$patient_id='',$plan='',$organize_mode='default') {
 116
 117   // Set date to current if not set
 118   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
 119
 120   // Prepare the results array
 121   $results = array();
 122
 123   // If set the $provider to collate_outer (or collate_inner without plans organize mode),
 124   // then run through this function recursively and return results.
 125   if (($provider == "collate_outer") || ($provider == "collate_inner" && $organize_mode != 'plans')) {
 126     // First, collect an array of all providers
 127     $query = "SELECT id, lname, fname, npi, federaltaxid FROM users WHERE authorized = 1 ORDER BY lname, fname";
 128     $ures = sqlStatement($query);
 129     // Second, run through each provider recursively
 130     while ($urow = sqlFetchArray($ures)) {
 131       $newResults = test_rules_clinic($urow['id'],$type,$dateTarget,$mode,$patient_id,$plan,$organize_mode);
 132       if (!empty($newResults)) {
 133         $provider_item['is_provider'] = TRUE;
 134         $provider_item['prov_lname'] = $urow['lname'];
 135         $provider_item['prov_fname'] = $urow['fname'];
 136         $provider_item['npi'] = $urow['npi'];
 137         $provider_item['federaltaxid'] = $urow['federaltaxid'];
 138         array_push($results,$provider_item);
 139         $results = array_merge($results,$newResults);
 140       }
 141     }
 142     // done, so now can return results
 143     return $results;
 144   }
 145
 146   // If set organize-mode to plans, then collects active plans and run through this
 147   // function recursively and return results.
 148   if ($organize_mode == "plans") {
 149     // First, collect active plans
 150     $plans_resolve = resolve_plans_sql($plan,$patient_id);
 151     // Second, run through function recursively
 152     foreach ($plans_resolve as $plan_item) {
 153       //  (if collate_inner, then nest a collation of providers within each plan)
 154       if ($provider == "collate_inner") {
 155         // First, collect an array of all providers
 156         $query = "SELECT id, lname, fname, npi, federaltaxid FROM users WHERE authorized = 1 ORDER BY lname, fname";
 157         $ures = sqlStatement($query);
 158         // Second, run through each provider recursively
 159         $provider_results = array();
 160         while ($urow = sqlFetchArray($ures)) {
 161           $newResults = test_rules_clinic($urow['id'],$type,$dateTarget,$mode,$patient_id,$plan_item['id']);
 162           if (!empty($newResults)) {
 163             $provider_item['is_provider'] = TRUE;
 164             $provider_item['prov_lname'] = $urow['lname'];
 165             $provider_item['prov_fname'] = $urow['fname'];
 166             $provider_item['npi'] = $urow['npi'];
 167             $provider_item['federaltaxid'] = $urow['federaltaxid'];
 168             array_push($provider_results,$provider_item);
 169             $provider_results = array_merge($provider_results,$newResults);
 170           }
 171         }
 172         if (!empty($provider_results)) {
 173           $plan_item['is_plan'] = TRUE;
 174           array_push($results,$plan_item);
 175           $results = array_merge($results,$provider_results);
 176         }
 177       }
 178       else {
 179         // (not collate_inner, so do not nest providers within each plan)
 180         $newResults = test_rules_clinic($provider,$type,$dateTarget,$mode,$patient_id,$plan_item['id']);
 181         if (!empty($newResults)) {
 182           $plan_item['is_plan'] = TRUE;
 183           array_push($results,$plan_item);
 184           $results = array_merge($results,$newResults);
 185         }
 186       }
 187     }
 188     // done, so now can return results
 189     return $results;
 190   }
 191
 192   // Collect all patient ids
 193   $patientData = array();
 194   if (!empty($patient_id)) {
 195     // only look at the selected patient
 196     array_push($patientData,$patient_id);
 197   }
 198   else {
 199     if (empty($provider)) {
 200       // Look at entire practice
 201       $rez = sqlStatement("SELECT `pid` FROM `patient_data`");
 202       for($iter=0; $row=sqlFetchArray($rez); $iter++) {
 203        $patientData[$iter]=$row;
 204       }
 205     }
 206     else {
 207       // Look at one provider
 208       $rez = sqlStatement("SELECT `pid` FROM `patient_data` " .
 209         "WHERE providerID=?", array($provider) );
 210       for($iter=0; $row=sqlFetchArray($rez); $iter++) {
 211        $patientData[$iter]=$row;
 212       }
 213     }
 214   }
 215   // Go through each patient(s)
 216   //
 217   //  If in report mode, then tabulate for each rule:
 218   //    Total Patients
 219   //    Patients that pass the filter
 220   //    Patients that pass the target
 221   //  If in reminders mode, then create reminders for each rule:
 222   //    Reminder that action is due soon
 223   //    Reminder that action is due
 224   //    Reminder that action is post-due
 225
 226   //Collect applicable rules
 227   if ($mode != "report") {
 228     // Use per patient custom rules (if exist)
 229     $rules = resolve_rules_sql($type,$patient_id,FALSE,$plan);
 230   }
 231   else { // $mode = "report"
 232     // Only use default rules (do not use patient custom rules)
 233     $rules = resolve_rules_sql($type,$patient_id,FALSE,$plan);
 234   }
 235
 236   foreach( $rules as $rowRule ) {
 237
 238     // If using cqm or amc type, then use the hard-coded rules set.
 239     // Note these rules are only used in report mode.
 240     if ($rowRule['cqm_flag'] || $rowRule['amc_flag']) {
 241
 242       // Ensure the ruleSet class file has been included
 243       // (will only require if needed, since it's gonna be large)
 244       require_once(dirname(__FILE__) . "/classes/rulesets/ruleSet.class.php");
 245
 246       // Run the class rule set
 247       $rule_results = new ruleSet($rowRule,$dateTarget,$patientData);
 248
 249       // Collect/add the results to the results array
 250       $tempResults = $rule_results->return_results();
 251       if (!empty($tempResults)) {
 252         foreach ($tempResults as $tempResult) {
 253           array_push($results,$tempResult);
 254         }
 255       }
 256
 257       // Go on to the next rule
 258       continue;
 259     }
 260
 261     // If in reminder mode then need to collect the measurement dates
 262     //  from rule_reminder table
 263     $target_dates = array();
 264     if ($mode != "report") {
 265       // Calculate the dates to check for
 266       if ($type == "patient_reminder") {
 267         $reminder_interval_type = "patient_reminder";
 268       }
 269       else { // $type == "passive_alert" or $type == "active_alert"
 270         $reminder_interval_type = "clinical_reminder";
 271       }
 272       $target_dates = calculate_reminder_dates($rowRule['id'], $dateTarget, $reminder_interval_type);
 273     }
 274     else { // $mode == "report"
 275       // Only use the target date in the report
 276       $target_dates[0] = $dateTarget;
 277     }
 278
 279     //Reset the counters
 280     $total_patients = 0;
 281     $pass_filter = 0;
 282     $exclude_filter = 0;
 283     $pass_target = 0;
 284
 285     foreach( $patientData as $rowPatient ) {
 286
 287       // Count the total patients
 288       $total_patients++;
 289
 290       $dateCounter = 1; // for reminder mode to keep track of which date checking
 291       foreach ( $target_dates as $dateFocus ) {
 292
 293         //Skip if date is set to SKIP
 294         if ($dateFocus == "SKIP") {
 295           $dateCounter++;
 296           continue;
 297         }
 298
 299         //Set date counter and reminder token (applicable for reminders only)
 300         if ($dateCounter == 1) {
 301           $reminder_due = "soon_due";
 302         }
 303         else if ($dateCounter == 2) {
 304           $reminder_due = "due";
 305         }
 306         else { // $dateCounter == 3
 307           $reminder_due = "past_due";
 308         }
 309
 310         // Check if pass filter
 311         $passFilter = test_filter($rowPatient['pid'],$rowRule['id'],$dateFocus);
 312         if ($passFilter === "EXCLUDED") {
 313           // increment EXCLUDED and pass_filter counters
 314           //  and set as FALSE for reminder functionality.
 315           $pass_filter++;
 316           $exclude_filter++;
 317           $passFilter = FALSE;
 318         }
 319         if ($passFilter) {
 320           // increment pass filter counter
 321           $pass_filter++;
 322         }
 323         else {
 324           $dateCounter++;
 325           continue;
 326         }
 327
 328         // Check if pass target
 329         $passTarget = test_targets($rowPatient['pid'],$rowRule['id'],'',$dateFocus);
 330         if ($passTarget) {
 331           // increment pass target counter
 332           $pass_target++;
 333           // send to reminder results
 334           if ($mode == "reminders-all") {
 335             // place the completed actions into the reminder return array
 336             $actionArray = resolve_action_sql($rowRule['id'],'1');
 337             foreach ($actionArray as $action) {
 338               $action_plus = $action;
 339               $action_plus['due_status'] = "not_due";
 340               $action_plus['pid'] = $rowPatient['pid'];
 341               $results = reminder_results_integrate($results, $action_plus);
 342             }
 343           }
 344           break;
 345         }
 346         else {
 347           // send to reminder results
 348           if ($mode != "report") {
 349             // place the uncompleted actions into the reminder return array
 350             $actionArray = resolve_action_sql($rowRule['id'],'1');
 351             foreach ($actionArray as $action) {
 352               $action_plus = $action;
 353               $action_plus['due_status'] = $reminder_due;
 354               $action_plus['pid'] = $rowPatient['pid'];
 355               $results = reminder_results_integrate($results, $action_plus);
 356             }
 357           }
 358         }
 359         $dateCounter++;
 360       }
 361     }
 362
 363     // Calculate and save the data for the rule
 364     $percentage = calculate_percentage($pass_filter,$exclude_filter,$pass_target);
 365     if ($mode == "report") {
 366       $newRow=array('is_main'=>TRUE,'total_patients'=>$total_patients,'excluded'=>$exclude_filter,'pass_filter'=>$pass_filter,'pass_target'=>$pass_target,'percentage'=>$percentage);
 367       $newRow=array_merge($newRow,$rowRule);
 368       array_push($results, $newRow);
 369     }
 370
 371     // Find the number of target groups, and go through each one if more than one
 372     $targetGroups = numberTargetGroups($rowRule['id']);
 373     if ($targetGroups > 1) {
 374       if ($mode != "report") {
 375         $start_id = 2;
 376       }
 377       else { // $mode == "report"
 378         $start_id = 1;
 379       }
 380       for ($i = $start_id; $i <= $targetGroups; $i++){
 381
 382         //Reset the target counter
 383         $pass_target = 0;
 384
 385         foreach( $patientData as $rowPatient ) {
 386
 387           $dateCounter = 1; // for reminder mode to keep track of which date checking
 388           foreach ( $target_dates as $dateFocus ) {
 389
 390             //Skip if date is set to SKIP
 391             if ($dateFocus == "SKIP") {
 392               $dateCounter++;
 393               continue;
 394             }
 395
 396             //Set date counter and reminder token (applicable for reminders only)
 397             if ($dateCounter == 1) {
 398               $reminder_due = "soon_due";
 399             }
 400             else if ($dateCounter == 2) {
 401               $reminder_due = "due";
 402             }
 403             else { // $dateCounter == 3
 404               $reminder_due = "past_due";
 405             }
 406
 407             //Check if pass target
 408             $passTarget = test_targets($rowPatient['pid'],$rowRule['id'],$i,$dateFocus);
 409             if ($passTarget) {
 410               // increment pass target counter
 411               $pass_target++;
 412               // send to reminder results
 413               if ($mode == "reminders-all") {
 414                 // place the completed actions into the reminder return array
 415                 $actionArray = resolve_action_sql($rowRule['id'],'1');
 416                 foreach ($actionArray as $action) {
 417                   $action_plus = $action;
 418                   $action_plus['due_status'] = "not_due";
 419                   $action_plus['pid'] = $rowPatient['pid'];
 420                   $results = reminder_results_integrate($results, $action_plus);
 421                 }
 422               }
 423               break;
 424             }
 425             else {
 426               // send to reminder results
 427               if ($mode != "report") {
 428                 // place the actions into the reminder return array
 429                 $actionArray = resolve_action_sql($rowRule['id'],$i);
 430                 foreach ($actionArray as $action) {
 431                   $action_plus = $action;
 432                   $action_plus['due_status'] = $reminder_due;
 433                   $action_plus['pid'] = $rowPatient['pid'];
 434                   $results = reminder_results_integrate($results, $action_plus);
 435                 }
 436               }
 437             }
 438             $dateCounter++;
 439           }
 440         }
 441
 442         // Calculate and save the data for the rule
 443           $percentage = calculate_percentage($pass_filter,$exclude_filter,$pass_target);
 444
 445         // Collect action for title (just use the first one, if more than one)
 446         $actionArray = resolve_action_sql($rowRule['id'],$i);
 447         $action = $actionArray[0];
 448         if ($mode == "report") {
 449           $newRow=array('is_sub'=>TRUE,'action_category'=>$action['category'],'action_item'=>$action['item'],'total_patients'=>'','excluded'=>'','pass_filter'=>'','pass_target'=>$pass_target,'percentage'=>$percentage);
 450           array_push($results, $newRow);
 451         }
 452       }
 453     }
 454   }
 455
 456   // Return the data
 457   return $results;
 458 }
 459
 460 // Test filter of a selected rule on a selected patient
 461 // Parameters:
 462 //   $patient_id - pid of selected patient.
 463 //   $rule       - id(string) of selected rule
 464 //   $dateTarget - target date.
 465 // Return:
 466 //   boolean (if pass filter then TRUE, if excluded then 'EXCLUDED', if not pass filter then FALSE)
 467 function test_filter($patient_id,$rule,$dateTarget) {
 468
 469   // Set date to current if not set
 470   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
 471
 472   // Collect patient information
 473   $patientData = getPatientData($patient_id, "sex, DATE_FORMAT(DOB,'%Y %m %d') as DOB_TS");
 474
 475   //
 476   // ----------------- INCLUSIONS -----------------
 477   //
 478
 479   // -------- Age Filter (inclusion) ------------
 480   // Calculate patient age in years and months
 481   $patientAgeYears = convertDobtoAgeYearDecimal($patientData['DOB_TS'],$dateTarget);
 482   $patientAgeMonths = convertDobtoAgeMonthDecimal($patientData['DOB_TS'],$dateTarget);
 483
 484   // Min age (year) Filter (assume that there in not more than one of each)
 485   $filter = resolve_filter_sql($rule,'filt_age_min');
 486   if (!empty($filter)) {
 487     $row = $filter[0];
 488     if ($row ['method_detail'] == "year") {
 489       if ( $row['value'] && ($row['value'] > $patientAgeYears) ) {
 490         return false;
 491       }
 492     }
 493     if ($row ['method_detail'] == "month") {
 494       if ( $row['value'] && ($row['value'] > $patientAgeMonths) ) {
 495         return false;
 496       }
 497     }
 498   }
 499   // Max age (year) Filter (assume that there in not more than one of each)
 500   $filter = resolve_filter_sql($rule,'filt_age_max');
 501   if (!empty($filter)) {
 502     $row = $filter[0];
 503     if ($row ['method_detail'] == "year") {
 504       if ( $row['value'] && ($row['value'] < $patientAgeYears) ) {
 505         return false;
 506       }
 507     }
 508     if ($row ['method_detail'] == "month") {
 509       if ( $row['value'] && ($row['value'] < $patientAgeMonths) ) {
 510         return false;
 511       }
 512     }
 513   }
 514
 515   // -------- Gender Filter (inclusion) ---------
 516   // Gender Filter (assume that there in not more than one of each)
 517   $filter = resolve_filter_sql($rule,'filt_sex');
 518   if (!empty($filter)) {
 519     $row = $filter[0];
 520     if ( $row['value'] && ($row['value'] != $patientData['sex']) ) {
 521       return false;
 522     }
 523   }
 524
 525   // -------- Database Filter (inclusion) ------
 526   // Database Filter
 527   $filter = resolve_filter_sql($rule,'filt_database');
 528   if ((!empty($filter)) && !database_check($patient_id,$filter,'',$dateTarget)) return false;
 529
 530   // -------- Lists Filter (inclusion) ----
 531   // Set up lists filter, which is fully customizable and currently includes diagnoses, meds,
 532   //   surgeries and allergies.
 533   $filter = resolve_filter_sql($rule,'filt_lists');
 534   if ((!empty($filter)) && !lists_check($patient_id,$filter,$dateTarget)) return false;
 535
 536   //
 537   // ----------------- EXCLUSIONS -----------------
 538   //
 539
 540   // -------- Lists Filter (EXCLUSION) ----
 541   // Set up lists EXCLUSION filter, which is fully customizable and currently includes diagnoses, meds,
 542   //   surgeries and allergies.
 543   $filter = resolve_filter_sql($rule,'filt_lists',0);
 544   if ((!empty($filter)) && lists_check($patient_id,$filter,$dateTarget)) return "EXCLUDED";
 545
 546   // Passed all filters, so return true.
 547   return true;
 548 }
 549
 550 // Return the number of target groups of a selected rule
 551 // Parameters:
 552 //   $rule - id(string) of rule
 553 // Return:
 554 //   integer, number of target groups associated with rule
 555 function numberTargetGroups($rule) {
 556   $numberGroups = 1;
 557
 558   $sql = sqlQuery("SELECT max(`group_id`) as numberGroups FROM `rule_target` " .
 559     "WHERE `id`=?", array($rule) );
 560
 561   if ($sql['numberGroups']) $numberGroups = $sql['numberGroups'];
 562
 563   return $numberGroups;
 564 }
 565
 566 // Test targets of a selected rule on a selected patient
 567 // Parameters:
 568 //   $patient_id - pid of selected patient.
 569 //   $rule       - id(string) of selected rule (if blank, then will ignore grouping)
 570 //   $group_id   - group id of target group
 571 //   $dateTarget - target date.
 572 // Return:
 573 //   boolean (if target passes then true, otherwise false)
 574 function test_targets($patient_id,$rule,$group_id='',$dateTarget) {
 575
 576   // -------- Interval Target ----
 577   $interval = resolve_target_sql($rule,$group_id,'target_interval');
 578
 579   // -------- Database Target ----
 580   // Database Target (includes)
 581   $target = resolve_target_sql($rule,$group_id,'target_database');
 582   if ((!empty($target)) && !database_check($patient_id,$target,$interval,$dateTarget)) return false;
 583
 584   // -------- Procedure (labs,imaging,test,procedures,etc) Target ----
 585   // Procedure Target (includes)
 586   $target = resolve_target_sql($rule,$group_id,'target_proc');
 587   if ((!empty($target)) && !procedure_check($patient_id,$target,$interval,$dateTarget)) return false;
 588
 589   // -------- Appointment Target ----
 590   // Appointment Target (includes) (Specialized functionality for appointment reminders)
 591   $target = resolve_target_sql($rule,$group_id,'target_appt');
 592   if ((!empty($target)) && appointment_check($patient_id,$dateTarget)) return false;
 593
 594   // Passed all target tests, so return true.
 595   return true;
 596 }
 597
 598 // Function to return active plans
 599 // Parameters:
 600 //   $type             - plan type filter (normal or cqm or blank)
 601 //   $patient_id       - pid of selected patient. (if custom plan does not exist then
 602 //                        will use the default plan)
 603 //   $configurableOnly - true if only want the configurable (per patient) plans
 604 //                       (ie. ignore cqm plans)
 605 // Return: array containing plans
 606 function resolve_plans_sql($type='',$patient_id='0',$configurableOnly=FALSE) {
 607
 608   if ($configurableOnly) {
 609     // Collect all default, configurable (per patient) plans into an array
 610     //   (ie. ignore the cqm rules)
 611     $sql = sqlStatement("SELECT * FROM `clinical_plans` WHERE `pid`=0 AND `cqm_flag` !=1 ORDER BY `id`");
 612   }
 613   else {
 614     // Collect all default plans into an array
 615     $sql = sqlStatement("SELECT * FROM `clinical_plans` WHERE `pid`=0 ORDER BY `id`");
 616   }
 617   $returnArray= array();
 618   for($iter=0; $row=sqlFetchArray($sql); $iter++) {
 619     array_push($returnArray,$row);
 620   }
 621
 622   // Now collect the pertinent plans
 623   $newReturnArray = array();
 624
 625   // Need to select rules (use custom if exist)
 626   foreach ($returnArray as $plan) {
 627     $customPlan = sqlQuery("SELECT * FROM `clinical_plans` WHERE `id`=? AND `pid`=?", array($plan['id'],$patient_id) );
 628
 629     // Decide if use default vs custom plan (preference given to custom plan)
 630     if (!empty($customPlan)) {
 631       if ($type == "cqm" ) {
 632         // For CQM , do not use custom plans (these are to create standard clinic wide reports)
 633         $goPlan = $plan;
 634       }
 635       else {
 636         // merge the custom plan with the default plan
 637         $mergedPlan = array();
 638         foreach ($customPlan as $key => $value) {
 639           if ($value == NULL && preg_match("/_flag$/",$key)) {
 640             // use default setting
 641             $mergedPlan[$key] = $plan[$key];
 642           }
 643           else {
 644             // use custom setting
 645             $mergedPlan[$key] = $value;
 646           }
 647         }
 648         $goPlan = $mergedPlan;
 649       }
 650     }
 651     else {
 652       $goPlan = $plan;
 653     }
 654
 655     // Use the chosen plan if set
 656     if (!empty($type)) {
 657       if ($goPlan["${type}_flag"] == 1) {
 658         // active, so use the plan
 659         array_push($newReturnArray,$goPlan);
 660       }
 661     }
 662     else {
 663       if ($goPlan['normal_flag'] == 1 ||
 664           $goPlan['cqm_flag'] == 1) {
 665         // active, so use the plan
 666         array_push($newReturnArray,$goPlan);
 667       }
 668     }
 669   }
 670   $returnArray = $newReturnArray;
 671
 672   return $returnArray;
 673 }
 674
 675 // Function to return a specific plan
 676 // Parameters:
 677 //   $plan       - id(string) of plan
 678 //   $patient_id - pid of selected patient. (if set to 0, then will return
 679 //                 the default rule).
 680 // Return: array containing a rule
 681 function collect_plan($plan,$patient_id='0') {
 682
 683   return sqlQuery("SELECT * FROM `clinical_plans` WHERE `id`=? AND `pid`=?", array($plan,$patient_id) );
 684
 685 }
 686
 687 // Function to set a specific plan activity for a specific patient
 688 // Parameters:
 689 //   $plan       - id(string) of plan
 690 //   $type       - plan filter (normal,cqm)
 691 //   $setting    - activity of plan (yes,no,default)
 692 //   $patient_id - pid of selected patient.
 693 // Return: nothing
 694 function set_plan_activity_patient($plan,$type,$setting,$patient_id) {
 695
 696   // Don't allow messing with the default plans here
 697   if ($patient_id == "0") {
 698     return;
 699   }
 700
 701   // Convert setting
 702   if ($setting == "on") {
 703     $setting = 1;
 704   }
 705   else if ($setting == "off") {
 706     $setting = 0;
 707   }
 708   else { // $setting == "default"
 709     $setting = NULL;
 710   }
 711
 712   // Collect patient specific plan, if already exists.
 713   $query = "SELECT * FROM `clinical_plans` WHERE `id` = ? AND `pid` = ?";
 714   $patient_plan = sqlQuery($query, array($plan,$patient_id) );
 715
 716   if (empty($patient_plan)) {
 717     // Create a new patient specific plan with flags all set to default
 718     $query = "INSERT into `clinical_plans` (`id`, `pid`) VALUES (?,?)";
 719     sqlStatement($query, array($plan, $patient_id) );
 720   }
 721
 722   // Update patient specific row
 723   $query = "UPDATE `clinical_plans` SET `" . add_escape_custom($type) . "_flag`= ? WHERE id = ? AND pid = ?";
 724   sqlStatement($query, array($setting,$plan,$patient_id) );
 725
 726 }
 727
 728 // Function to return active rules
 729 // Parameters:
 730 //   $type             - rule filter (active_alert,passive_alert,cqm,amc,patient_reminder)
 731 //   $patient_id       - pid of selected patient. (if custom rule does not exist then
 732 //                        will use the default rule)
 733 //   $configurableOnly - true if only want the configurable (per patient) rules
 734 //                       (ie. ignore cqm and amc rules)
 735 //   $plan             - collect rules for specific plan
 736 // Return: array containing rules
 737 function resolve_rules_sql($type='',$patient_id='0',$configurableOnly=FALSE,$plan='') {
 738
 739   if ($configurableOnly) {
 740     // Collect all default, configurable (per patient) rules into an array
 741     //   (ie. ignore the cqm and amc rules)
 742     $sql = sqlStatement("SELECT * FROM `clinical_rules` WHERE `pid`=0 AND `cqm_flag` !=1 AND `amc_flag` !=1 ORDER BY `id`");
 743   }
 744   else {
 745     // Collect all default rules into an array
 746     $sql = sqlStatement("SELECT * FROM `clinical_rules` WHERE `pid`=0 ORDER BY `id`");
 747   }
 748   $returnArray= array();
 749   for($iter=0; $row=sqlFetchArray($sql); $iter++) {
 750     array_push($returnArray,$row);
 751   }
 752
 753   // Now filter rules for plan (if applicable)
 754   if (!empty($plan)) {
 755     $planReturnArray = array();
 756     foreach ($returnArray as $rule) {
 757       $standardRule = sqlQuery("SELECT * FROM `clinical_plans_rules` " .
 758                                "WHERE `plan_id`=? AND `rule_id`=?", array($plan,$rule['id']) );
 759       if (!empty($standardRule)) {
 760         array_push($planReturnArray,$rule);
 761       }
 762     }
 763     $returnArray = $planReturnArray;
 764   }
 765
 766   // Now collect the pertinent rules
 767   $newReturnArray = array();
 768
 769   // Need to select rules (use custom if exist)
 770   foreach ($returnArray as $rule) {
 771     $customRule = sqlQuery("SELECT * FROM `clinical_rules` WHERE `id`=? AND `pid`=?", array($rule['id'],$patient_id) );
 772
 773     // Decide if use default vs custom rule (preference given to custom rule)
 774     if (!empty($customRule)) {
 775       if ($type == "cqm" || $type == "amc" ) {
 776         // For CQM and AMC, do not use custom rules (these are to create standard clinic wide reports)
 777         $goRule = $rule;
 778       }
 779       else {
 780         // merge the custom rule with the default rule
 781         $mergedRule = array();
 782         foreach ($customRule as $key => $value) {
 783           if ($value == NULL && preg_match("/_flag$/",$key)) {
 784             // use default setting
 785             $mergedRule[$key] = $rule[$key];
 786           }
 787           else {
 788             // use custom setting
 789             $mergedRule[$key] = $value;
 790           }
 791         }
 792         $goRule = $mergedRule;
 793       }
 794     }
 795     else {
 796       $goRule = $rule;
 797     }
 798
 799     // Use the chosen rule if set
 800     if (!empty($type)) {
 801       if ($goRule["${type}_flag"] == 1) {
 802         // active, so use the rule
 803         array_push($newReturnArray,$goRule);
 804       }
 805     }
 806     else {
 807       // no filter, so return the rule
 808       array_push($newReturnArray,$goRule);
 809     }
 810   }
 811   $returnArray = $newReturnArray;
 812
 813   return $returnArray;
 814 }
 815
 816 // Function to return a specific rule
 817 // Parameters:
 818 //   $rule       - id(string) of rule
 819 //   $patient_id - pid of selected patient. (if set to 0, then will return
 820 //                 the default rule).
 821 // Return: array containing a rule
 822 function collect_rule($rule,$patient_id='0') {
 823
 824   return sqlQuery("SELECT * FROM `clinical_rules` WHERE `id`=? AND `pid`=?", array($rule,$patient_id) );
 825
 826 }
 827
 828 // Function to set a specific rule activity for a specific patient
 829 // Parameters:
 830 //   $rule       - id(string) of rule
 831 //   $type       - rule filter (active_alert,passive_alert,cqm,amc,patient_reminder)
 832 //   $setting    - activity of rule (yes,no,default)
 833 //   $patient_id - pid of selected patient.
 834 // Return: nothing
 835 function set_rule_activity_patient($rule,$type,$setting,$patient_id) {
 836
 837   // Don't allow messing with the default rules here
 838   if ($patient_id == "0") {
 839     return;
 840   }
 841
 842   // Convert setting
 843   if ($setting == "on") {
 844     $setting = 1;
 845   }
 846   else if ($setting == "off") {
 847     $setting = 0;
 848   }
 849   else { // $setting == "default"
 850     $setting = NULL;
 851   }
 852
 853   // Collect patient specific rule, if already exists.
 854   $query = "SELECT * FROM `clinical_rules` WHERE `id` = ? AND `pid` = ?";
 855   $patient_rule = sqlQuery($query, array($rule,$patient_id) );
 856
 857   if (empty($patient_rule)) {
 858     // Create a new patient specific rule with flags all set to default
 859     $query = "INSERT into `clinical_rules` (`id`, `pid`) VALUES (?,?)";
 860     sqlStatement($query, array($rule, $patient_id) );
 861   }
 862
 863   // Update patient specific row
 864   $query = "UPDATE `clinical_rules` SET `" . add_escape_custom($type) . "_flag`= ? WHERE id = ? AND pid = ?";
 865   sqlStatement($query, array($setting,$rule,$patient_id) );
 866
 867 }
 868
 869 // Function to return applicable reminder dates (relative)
 870 // Parameters:
 871 //   $rule            - id(string) of selected rule
 872 //   $reminder_method - string label of filter type
 873 // Return: array containing reminder features
 874 function resolve_reminder_sql($rule,$reminder_method) {
 875   $sql = sqlStatement("SELECT `method_detail`, `value` FROM `rule_reminder` " .
 876     "WHERE `id`=? AND `method`=?", array($rule, $reminder_method) );
 877
 878   $returnArray= array();
 879   for($iter=0; $row=sqlFetchArray($sql); $iter++) {
 880     array_push($returnArray,$row);
 881   }
 882   return $returnArray;
 883 }
 884
 885 // Function to return applicable filters
 886 // Parameters:
 887 //   $rule          - id(string) of selected rule
 888 //   $filter_method - string label of filter type
 889 //   $include_flag  - to allow selection for included or excluded filters
 890 // Return: array containing filters
 891 function resolve_filter_sql($rule,$filter_method,$include_flag=1) {
 892   $sql = sqlStatement("SELECT `method_detail`, `value`, `required_flag` FROM `rule_filter` " .
 893     "WHERE `id`=? AND `method`=? AND `include_flag`=?", array($rule, $filter_method, $include_flag) );
 894
 895   $returnArray= array();
 896   for($iter=0; $row=sqlFetchArray($sql); $iter++) {
 897     array_push($returnArray,$row);
 898   }
 899   return $returnArray;
 900 }
 901
 902 // Function to return applicable targets
 903 // Parameters:
 904 //   $rule          - id(string) of selected rule
 905 //   $group_id      - group id of target group (if blank, then will ignore grouping)
 906 //   $target_method - string label of target type
 907 //   $include_flag  - to allow selection for included or excluded targets
 908 // Return: array containing targets
 909 function resolve_target_sql($rule,$group_id='',$target_method,$include_flag=1) {
 910
 911   if ($group_id) {
 912     $sql = sqlStatement("SELECT `value`, `required_flag`, `interval` FROM `rule_target` " .
 913       "WHERE `id`=? AND `group_id`=? AND `method`=? AND `include_flag`=?", array($rule, $group_id, $target_method, $include_flag) );
 914   }
 915   else {
 916     $sql = sqlStatement("SELECT `value`, `required_flag`, `interval` FROM `rule_target` " .
 917       "WHERE `id`=? AND `method`=? AND `include_flag`=?", array($rule, $target_method, $include_flag) );
 918   }
 919
 920   $returnArray= array();
 921   for($iter=0; $row=sqlFetchArray($sql); $iter++) {
 922     array_push($returnArray,$row);
 923   }
 924   return $returnArray;
 925 }
 926
 927 // Function to return applicable actions
 928 // Parameters:
 929 //   $rule          - id(string) of selected rule
 930 //   $group_id      - group id of target group (if blank, then will ignore grouping)
 931 // Return: array containing actions
 932 function resolve_action_sql($rule,$group_id='') {
 933
 934   if ($group_id) {
 935     $sql = sqlStatement("SELECT b.category, b.item, b.clin_rem_link, b.reminder_message, b.custom_flag " .
 936       "FROM `rule_action` as a " .
 937       "JOIN `rule_action_item` as b " .
 938       "ON a.category = b.category AND a.item = b.item " .
 939       "WHERE a.id=? AND a.group_id=?", array($rule,$group_id) );
 940   }
 941   else {
 942     $sql = sqlStatement("SELECT b.category, b.item, b.value, b.custom_flag " .
 943       "FROM `rule_action` as a " .
 944       "JOIN `rule_action_item` as b " .
 945       "ON a.category = b.category AND a.item = b.item " .
 946       "WHERE a.id=?", array($rule) );
 947   }
 948
 949   $returnArray= array();
 950   for($iter=0; $row=sqlFetchArray($sql); $iter++) {
 951     array_push($returnArray,$row);
 952   }
 953   return $returnArray;
 954 }
 955
 956 // Function to check database filters and targets
 957 // Parameters:
 958 //   $patient_id - pid of selected patient.
 959 //   $filter     - array containing filter/target elements
 960 //   $interval   - used for the interval elements
 961 //   $dateTarget - target date. blank is current date.
 962 // Return: boolean if check passed, otherwise false
 963 function database_check($patient_id,$filter,$interval='',$dateTarget='') {
 964   $isMatch = false; //matching flag
 965
 966   // Set date to current if not set
 967   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
 968
 969   // Unpackage interval information
 970   // (Assume only one for now and only pertinent for targets)
 971   $intervalType = '';
 972   $intervalValue = '';
 973   if (!empty($interval)) {
 974     $intervalType = $interval[0]['value'];
 975     $intervalValue = $interval[0]['interval'];
 976   }
 977
 978   foreach( $filter as $row ) {
 979     // Row description
 980     //   [0]=>special modes
 981     $temp_df = explode("::",$row['value']);
 982
 983     if ($temp_df[0] == "CUSTOM") {
 984       // Row description
 985       //   [0]=>special modes(CUSTOM) [1]=>category [2]=>item [3]=>complete? [4]=>number of hits comparison [5]=>number of hits
 986       if (exist_custom_item($patient_id, $temp_df[1], $temp_df[2], $temp_df[3], $temp_df[4], $temp_df[5], $intervalType, $intervalValue, $dateTarget)) {
 987         // Record the match
 988         $isMatch = true;
 989       }
 990       else {
 991        // If this is a required entry then return false
 992        if ($row['required_flag']) return false;
 993       }
 994     }
 995     else if ($temp_df[0] == "LIFESTYLE") {
 996       // Row description
 997       //   [0]=>special modes(LIFESTYLE) [1]=>column [2]=>status
 998       if (exist_lifestyle_item($patient_id, $temp_df[1], $temp_df[2], $dateTarget)) {
 999         // Record the match
1000         $isMatch = true;
1001       }
1002       else {
1003        // If this is a required entry then return false
1004        if ($row['required_flag']) return false;
1005       }
1006     }
1007     else {
1008       // Default mode
1009       // Row description
1010       //   [0]=>special modes(BLANK) [1]=>table [2]=>column [3]=>value comparison [4]=>value [5]=>number of hits comparison [6]=>number of hits
1011       if (exist_database_item($patient_id, $temp_df[1], $temp_df[2], $temp_df[3], $temp_df[4], $temp_df[5], $temp_df[6], $intervalType, $intervalValue, $dateTarget)) {
1012         // Record the match
1013         $isMatch = true;
1014       }
1015       else {
1016        // If this is a required entry then return false
1017        if ($row['required_flag']) return false;
1018       }
1019     }
1020   }
1021
1022   // return results of check
1023   return $isMatch;
1024 }
1025
1026 // Function to check procedure filters and targets
1027 // Parameters:
1028 //   $patient_id - pid of selected patient.
1029 //   $filter     - array containing filter/target elements
1030 //   $interval   - used for the interval elements
1031 //   $dateTarget - target date. blank is current date.
1032 // Return: boolean if check passed, otherwise false
1033 function procedure_check($patient_id,$filter,$interval='',$dateTarget='') {
1034   $isMatch = false; //matching flag
1035
1036   // Set date to current if not set
1037   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1038
1039   // Unpackage interval information
1040   // (Assume only one for now and only pertinent for targets)
1041   $intervalType = '';
1042   $intervalValue = '';
1043   if (!empty($interval)) {
1044     $intervalType = $interval[0]['value'];
1045     $intervalValue = $interval[0]['interval'];
1046   }
1047
1048   foreach( $filter as $row ) {
1049     // Row description
1050     // [0]=>title [1]=>code [2]=>value comparison [3]=>value [4]=>number of hits comparison [5]=>number of hits
1051     //   code description
1052     //     <type(ICD9,CPT)>:<identifier>; etc.
1053     $temp_df = explode("::",$row['value']);
1054     if (exist_procedure_item($patient_id, $temp_df[0], $temp_df[1], $temp_df[2], $temp_df[3], $temp_df[4], $temp_df[5], $intervalType, $intervalValue, $dateTarget)) {
1055       // Record the match
1056       $isMatch = true;
1057     }
1058     else {
1059       // If this is a required entry then return false
1060       if ($row['required_flag']) return false;
1061     }
1062   }
1063
1064   // return results of check
1065   return $isMatch;
1066 }
1067
1068 // Function to check for appointment
1069 // Parameters:
1070 //   $patient_id - pid of selected patient.
1071 //   $dateTarget - target date.
1072 // Return: boolean if appt exist, otherwise false
1073 function appointment_check($patient_id,$dateTarget='') {
1074   $isMatch = false; //matching flag
1075
1076   // Set date to current if not set (although should always be set)
1077   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1078   $dateTargetRound = date('Y-m-d',$dateTarget);
1079
1080   // Set current date
1081   $currentDate = date('Y-m-d H:i:s');
1082   $currentDateRound = date('Y-m-d',$dateCurrent);
1083
1084   // Basically, if the appointment is within the current date to the target date,
1085   //  then return true. (will not send reminders on same day as appointment)
1086   $sql = sqlStatement("SELECT openemr_postcalendar_events.pc_eid, " .
1087     "openemr_postcalendar_events.pc_title, " .
1088     "openemr_postcalendar_events.pc_eventDate, " .
1089     "openemr_postcalendar_events.pc_startTime, " .
1090     "openemr_postcalendar_events.pc_endTime " .
1091     "FROM openemr_postcalendar_events " .
1092     "WHERE openemr_postcalendar_events.pc_eventDate > ? " .
1093     "AND openemr_postcalendar_events.pc_eventDate <= ? " .
1094     "AND openemr_postcalendar_events.pc_pid = ?", array($currentDate,$dateTarget,$patient_id) );
1095
1096   // return results of check
1097   //
1098   // TODO: Figure out how to have multiple appointment and changing appointment reminders.
1099   //         Plan to send back array of appt info (eid, time, date, etc.)
1100   //         to do this.
1101   if (sqlNumRows($sql) > 0) {
1102     $isMatch = true;
1103   }
1104
1105   return $isMatch;
1106 }
1107
1108 // Function to check lists filters and targets
1109 //  Customizable and currently includes diagnoses, medications,
1110 //    allergies and surgeries.
1111 // Parameters:
1112 //   $patient_id - pid of selected patient.
1113 //   $filter     - array containing lists filter/target elements
1114 //   $dateTarget - target date. blank is current date.
1115 // Return: boolean if check passed, otherwise false
1116 function lists_check($patient_id,$filter,$dateTarget) {
1117   $isMatch = false; //matching flag
1118
1119   // Set date to current if not set
1120   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1121
1122   foreach ( $filter as $row ) {
1123     if (exist_lists_item($patient_id, $row['method_detail'], $row['value'], $dateTarget)) {
1124       // Record the match
1125       $isMatch = true;
1126     }
1127     else {
1128      // If this is a required entry then return false
1129      if ($row['required_flag']) return false;
1130     }
1131   }
1132
1133   // return results of check
1134   return $isMatch;
1135 }
1136
1137 // Function to check for existance of data in database for a patient
1138 // Parameters:
1139 //   $patient_id    - pid of selected patient.
1140 //   $table         - selected mysql table
1141 //   $column        - selected mysql column
1142 //   $data_comp       - data comparison (eq,ne,gt,ge,lt,le)
1143 //   $data            - selected data in the mysql database
1144 //   $num_items_comp  - number items comparison (eq,ne,gt,ge,lt,le)
1145 //   $num_items_thres - number of items threshold
1146 //   $intervalType  - type of interval (ie. year)
1147 //   $intervalValue - searched for within this many times of the interval type
1148 //   $dateTarget    - target date.
1149 // Return: boolean if check passed, otherwise false
1150 function exist_database_item($patient_id,$table,$column='',$data_comp,$data='',$num_items_comp,$num_items_thres,$intervalType='',$intervalValue='',$dateTarget='') {
1151
1152   // Set date to current if not set
1153   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1154
1155   // Collect the correct column label for patient id in the table
1156   $patient_id_label = collect_database_label('pid',$table);
1157
1158   // Get the interval sql query string
1159   $dateSql = sql_interval_string($table,$intervalType,$intervalValue,$dateTarget);
1160
1161   // If just checking for existence (ie. data is empty),
1162   //   then simply set the comparison operator to ne.
1163   if (empty($data)) {
1164     $data_comp = "ne";
1165   }
1166
1167   // get the appropriate sql comparison operator
1168   $compSql = convertCompSql($data_comp);
1169
1170   // check for items
1171   if (empty($column)) {
1172     // simple search for any table entries
1173     $sql = sqlStatement("SELECT * " .
1174       "FROM `" . add_escape_custom($table)  . "` " .
1175       "WHERE `" . add_escape_custom($patient_id_label)  . "`=?", array($patient_id) );
1176   }
1177   else {
1178     // search for number of specific items
1179     $sql = sqlStatement("SELECT `" . add_escape_custom($column) . "` " .
1180       "FROM `" . add_escape_custom($table)  . "` " .
1181       "WHERE `" . add_escape_custom($column) ."`" . $compSql . "? " .
1182       "AND `" . add_escape_custom($patient_id_label)  . "`=? " .
1183       $dateSql, array($data,$patient_id) );
1184   }
1185
1186   // See if number of returned items passes the comparison
1187   return itemsNumberCompare($num_items_comp, $num_items_thres, sqlNumRows($sql));
1188 }
1189
1190 // Function to check for existence of procedure(s) for a patient
1191 // Parameters:
1192 //   $patient_id      - pid of selected patient.
1193 //   $proc_title      - procedure title
1194 //   $proc_code       - procedure identifier code (array)
1195 //   $result_comp     - results comparison (eq,ne,gt,ge,lt,le)
1196 //   $result_data     - results data
1197 //   $num_items_comp  - number items comparison (eq,ne,gt,ge,lt,le)
1198 //   $num_items_thres - number of items threshold
1199 //   $intervalType    - type of interval (ie. year)
1200 //   $intervalValue   - searched for within this many times of the interval type
1201 //   $dateTarget      - target date.
1202 // Return: boolean if check passed, otherwise false
1203 function exist_procedure_item($patient_id,$proc_title,$proc_code,$result_comp,$result_data='',$num_items_comp,$num_items_thres,$intervalType='',$intervalValue='',$dateTarget='') {
1204
1205   // Set date to current if not set
1206   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1207
1208   // Set the table exception (for looking up pertinent date and pid sql columns)
1209   $table = "PROCEDURE-EXCEPTION";
1210
1211   // Collect the correct column label for patient id in the table
1212   $patient_id_label = collect_database_label('pid',$table);
1213
1214   // Get the interval sql query string
1215   $dateSql = sql_interval_string($table,$intervalType,$intervalValue,$dateTarget);
1216
1217   //
1218   // TODO
1219   // Figure out a way to use the identifiers codes
1220   // TODO
1221   //
1222
1223   // If just checking for existence (ie result_data is empty),
1224   //   then simply set the comparison operator to ne.
1225   if (empty($result_data)) {
1226     $result_comp = "ne";
1227   }
1228
1229   // get the appropriate sql comparison operator
1230   $compSql = convertCompSql($result_comp);
1231
1232   // collect specific items that fulfill request
1233   $sql = sqlStatement("SELECT procedure_result.result " .
1234          "FROM `procedure_type`, " .
1235          "`procedure_order`, " .
1236          "`procedure_report`, " .
1237          "`procedure_result` " .
1238          "WHERE procedure_type.procedure_type_id = procedure_order.procedure_type_id " .
1239          "AND procedure_order.procedure_order_id = procedure_report.procedure_order_id " .
1240          "AND procedure_report.procedure_report_id = procedure_result.procedure_report_id " .
1241          "AND procedure_type.name = ? " .
1242          "AND procedure_result.result " . $compSql . " ? " .
1243          "AND " . add_escape_custom($patient_id_label) . " = ? " .
1244          $dateSql, array($proc_title,$result_data,$patient_id) );
1245
1246   // See if number of returned items passes the comparison
1247   return itemsNumberCompare($num_items_comp, $num_items_thres, sqlNumRows($sql));
1248 }
1249
1250 // Function to check for existance of data for a patient in the rule_patient_data table
1251 // Parameters:
1252 //   $patient_id    - pid of selected patient.
1253 //   $category      - label in category column
1254 //   $item          - label in item column
1255 //   $complete       - label in complete column (YES,NO, or blank)
1256 //   $num_items_comp  - number items comparison (eq,ne,gt,ge,lt,le)
1257 //   $num_items_thres - number of items threshold
1258 //   $intervalType  - type of interval (ie. year)
1259 //   $intervalValue - searched for within this many times of the interval type
1260 //   $dateTarget    - target date.
1261 // Return: boolean if check passed, otherwise false
1262 function exist_custom_item($patient_id,$category,$item,$complete,$num_items_comp,$num_items_thres,$intervalType='',$intervalValue='',$dateTarget) {
1263
1264   // Set the table
1265   $table = 'rule_patient_data';
1266
1267   // Collect the correct column label for patient id in the table
1268   $patient_id_label = collect_database_label('pid',$table);
1269
1270   // Get the interval sql query string
1271   $dateSql = sql_interval_string($table,$intervalType,$intervalValue,$dateTarget);
1272
1273   // search for number of specific items
1274   $sql = sqlStatement("SELECT `result` " .
1275     "FROM `" . add_escape_custom($table)  . "` " .
1276     "WHERE `category`=? " .
1277     "AND `item`=? " .
1278     "AND `complete`=? " .
1279     "AND `" . add_escape_custom($patient_id_label)  . "`=? " .
1280     $dateSql, array($category,$item,$complete,$patient_id) );
1281
1282   // See if number of returned items passes the comparison
1283   return itemsNumberCompare($num_items_comp, $num_items_thres, sqlNumRows($sql));
1284 }
1285
1286 // Function to check for existance of data for a patient in lifestyle section
1287 // Parameters:
1288 //   $patient_id - pid of selected patient.
1289 //   $lifestyle  - selected label of mysql column of patient history
1290 //   $status     - specific status of selected lifestyle element
1291 //   $dateTarget - target date. blank is current date.
1292 // Return: boolean if check passed, otherwise false
1293 function exist_lifestyle_item($patient_id,$lifestyle,$status,$dateTarget) {
1294
1295   // Set date to current if not set
1296   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1297
1298   // Collect pertinent history data
1299   $history = getHistoryData($patient_id, $lifestyle,'',$dateTarget);
1300
1301   // See if match
1302   $stringFlag = strstr($history[$lifestyle], "|".$status);
1303   if (empty($status)) {
1304     // Only ensuring any data has been entered into the field
1305     $stringFlag = true;
1306   }
1307   if ( $history[$lifestyle] &&
1308        $history[$lifestyle] != '|0|' &&
1309        $stringFlag ) {
1310     return true;
1311   }
1312   else {
1313     return false;
1314   }
1315 }
1316
1317 // Function to check for lists item of a patient
1318 //  Fully customizable and includes diagnoses, medications,
1319 //    allergies, and surgeries.
1320 // Parameters:
1321 //   $patient_id - pid of selected patient.
1322 //   $type  - type (medical_problem, allergy, medication, etc)
1323 //   $value  - value searching for
1324 //   $dateTarget - target date. blank is current date.
1325 // Return: boolean if check passed, otherwise false
1326 function exist_lists_item($patient_id,$type,$value,$dateTarget) {
1327
1328   // Set date to current if not set
1329   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1330
1331   if ($type == "medical_problem") {
1332     // Specific search for diagnoses
1333     // Explode the value into diagnosis code type and code
1334     $temp_diag_array = explode("::",$value);
1335     $code_type = $temp_diag_array[0];
1336     $diagnosis = $temp_diag_array[1];
1337     if ($code_type=='CUSTOM') {
1338       // Deal with custom code first (title column in lists table)
1339       $response = sqlQuery("SELECT * FROM `lists` " .
1340         "WHERE `type`=? " .
1341         "AND `pid`=? " .
1342         "AND `title`=? " .
1343         "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " .
1344         "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,$diagnosis,$dateTarget,$dateTarget,$dateTarget) );
1345       if (!empty($response)) return true;
1346     }
1347     else {
1348       // Deal with the set code types (diagnosis column in lists table)
1349       $response = sqlQuery("SELECT * FROM `lists` " .
1350         "WHERE `type`=? " .
1351         "AND `pid`=? " .
1352         "AND `diagnosis` LIKE ? " .
1353         "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " .
1354         "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,"%".$code_type.":".$diagnosis."%",$dateTarget,$dateTarget,$dateTarget) );
1355       if (!empty($response)) return true;
1356     }
1357   }
1358   else { // generic lists item that requires no customization
1359     $response = sqlQuery("SELECT * FROM `lists` " .
1360       "WHERE `type`=? " .
1361       "AND `pid`=? " .
1362       "AND `title`=? ".
1363       "AND ( (`begdate` IS NULL AND `date`<=?) OR (`begdate` IS NOT NULL AND `begdate`<=?) ) " .
1364       "AND ( (`enddate` IS NULL) OR (`enddate` IS NOT NULL AND `enddate`>=?) )", array($type,$patient_id,$value,$dateTarget,$dateTarget,$dateTarget) );
1365     if (!empty($response)) return true;
1366   }
1367
1368   return false;
1369 }
1370
1371 // Function to return part of sql query to deal with interval
1372 // Parameters:
1373 //   $table         - selected mysql table (or EXCEPTION(s))
1374 //   $intervalType  - type of interval (ie. year)
1375 //   $intervalValue - searched for within this many times of the interval type
1376 //   $dateTarget    - target date.
1377 // Return: string containing pertinent date interval filter for mysql query
1378 function sql_interval_string($table,$intervalType,$intervalValue,$dateTarget) {
1379
1380   $dateSql="";
1381
1382   // Collect the correct column label for date in the table
1383   $date_label = collect_database_label('date',$table);
1384
1385   // Deal with interval
1386   if (!empty($intervalType)) {
1387     switch($intervalType) {
1388       case "year":
1389         $dateSql = "AND (" . add_escape_custom($date_label) .
1390           " BETWEEN DATE_SUB('" . add_escape_custom($dateTarget) .
1391           "', INTERVAL " . add_escape_custom($intervalValue) .
1392           " YEAR) AND '" . add_escape_custom($dateTarget) . "') ";
1393         break;
1394       case "month":
1395         $dateSql = "AND (" . add_escape_custom($date_label) .
1396           " BETWEEN DATE_SUB('" . add_escape_custom($dateTarget) .
1397           "', INTERVAL " . add_escape_custom($intervalValue) .
1398           " MONTH) AND '" . add_escape_custom($dateTarget) . "') ";
1399         break;
1400       case "week":
1401         $dateSql = "AND (" . add_escape_custom($date_label) .
1402           " BETWEEN DATE_SUB('" . add_escape_custom($dateTarget) .
1403           "', INTERVAL " . add_escape_custom($intervalValue) .
1404           " WEEK) AND '" . add_escape_custom($dateTarget) . "') ";
1405         break;
1406       case "day":
1407         $dateSql = "AND (" . add_escape_custom($date_label) .
1408           " BETWEEN DATE_SUB('" . add_escape_custom($dateTarget) .
1409           "', INTERVAL " . add_escape_custom($intervalValue) .
1410           " DAY) AND '" . add_escape_custom($dateTarget) . "') ";
1411         break;
1412       case "hour":
1413         $dateSql = "AND (" . add_escape_custom($date_label) .
1414           " BETWEEN DATE_SUB('" . add_escape_custom($dateTarget) .
1415           "', INTERVAL " . add_escape_custom($intervalValue) .
1416           " HOUR) AND '" . add_escape_custom($dateTarget) . "') ";
1417         break;
1418       case "minute":
1419         $dateSql = "AND (" . add_escape_custom($date_label) .
1420           " BETWEEN DATE_SUB('" . add_escape_custom($dateTarget) .
1421           "', INTERVAL " . add_escape_custom($intervalValue) .
1422           " MINUTE) AND '" . add_escape_custom($dateTarget) . "') ";
1423         break;
1424       case "second":
1425         $dateSql = "AND (" . add_escape_custom($date_label) .
1426           " BETWEEN DATE_SUB('" . add_escape_custom($dateTarget) .
1427           "', INTERVAL " . add_escape_custom($intervalValue) .
1428           " SECOND) AND '" . add_escape_custom($dateTarget) . "') ";
1429         break;
1430       case "flu_season":
1431         // Flu season to be hard-coded as September thru February
1432         //  (Should make this modifiable in the future)
1433         //  ($intervalValue is not used)
1434         $dateArray = explode("-",$dateTarget);
1435         $Year = $dateArray[0];
1436         $dateThisYear = $Year . "-09-01";
1437         $dateLastYear = ($Year-1) . "-09-01";
1438         $dateSql =" " .
1439           "AND ((" .
1440               "MONTH('" . add_escape_custom($dateTarget) . "') < 9 " .
1441               "AND " . add_escape_custom($date_label) . " >= '" . $dateLastYear . "' ) " .
1442             "OR (" .
1443               "MONTH('" . add_escape_custom($dateTarget) . "') >= 9 " .
1444               "AND " . add_escape_custom($date_label) . " >= '" . $dateThisYear . "' ))" .
1445           "AND " . add_escape_custom($date_label) . " <= '" . add_escape_custom($dateTarget) . "' ";
1446         break;
1447     }
1448   }
1449   else {
1450     $dateSql = "AND " . add_escape_custom($date_label) .
1451       " <= '" . add_escape_custom($dateTarget)  . "' ";
1452   }
1453
1454  // return the sql interval string
1455  return $dateSql;
1456 }
1457
1458 // Function to collect generic column labels from tables.
1459 //  It currently works for date and pid.
1460 //  Will need to expand this as algorithm grows.
1461 // Parameters:
1462 //   $label - element (pid or date)
1463 //   $table - selected mysql table (or EXCEPTION(s))
1464 // Return: string containing official label of selected element
1465 function collect_database_label($label,$table) {
1466
1467   if ($table == 'PROCEDURE-EXCEPTION') {
1468     // return cell to get procedure collection
1469     // special case since reuqires joing of multiple
1470     // tables to get this value
1471     if ($label == "pid") {
1472       $returnedLabel = "procedure_order.patient_id";
1473     }
1474     else if ($label == "date") {
1475       $returnedLabel = "procedure_result.date";
1476     }
1477     else {
1478       // unknown label, so return the original label
1479       $returnedLabel = $label;
1480     }
1481   }
1482   else if ($table == 'immunizations') {
1483     // return requested label for immunization table
1484     if ($label == "pid") {
1485       $returnedLabel = "patient_id";
1486     }
1487     else if ($label == "date") {
1488       $returnedLabel = "`administered_date`";
1489     }
1490     else {
1491       // unknown label, so return the original label
1492       $returnedLabel = $label;
1493     }
1494   }
1495   else {
1496     // return requested label for default tables
1497     if ($label == "pid") {
1498       $returnedLabel = "pid";
1499     }
1500     else if ($label == "date") {
1501       $returnedLabel = "`date`";
1502     }
1503     else {
1504       // unknown label, so return the original label
1505       $returnedLabel = $label;
1506     }
1507   }
1508
1509   return $returnedLabel;
1510 }
1511
1512 // Simple function to avoid processing of duplicate actions
1513 // Parameters:
1514 //   $actions - 2-dimensional array with all current active targets
1515 //   $action  - array of selected target to test for duplicate
1516 // Return: boolean, true if duplicate, false if not duplicate
1517 function is_duplicate_action($actions,$action) {
1518   foreach ($actions as $row) {
1519     if ($row['category'] == $action['category'] &&
1520         $row['item'] == $action['item'] &&
1521         $row['value'] == $action['value']) {
1522       // Is a duplicate
1523       return true;
1524     }
1525   }
1526
1527   // Not a duplicate
1528   return false;
1529 }
1530
1531 // Calculate the reminder dates.
1532 // Parameters:
1533 //   $rule       - id(string) of selected rule
1534 //   $dateTarget - target date. If blank then will test with current date as target.
1535 //   $type       - either 'patient_reminder' or 'clinical_reminder'
1536 // For now, will always return an array of 3 dates:
1537 //   first date is before the target date (past_due) (default of 1 month)
1538 //   second date is the target date (due)
1539 //   third date is after the target date (soon_due) (default of 2 weeks)
1540 function calculate_reminder_dates($rule, $dateTarget='',$type) {
1541
1542   // Set date to current if not set
1543   $dateTarget = ($dateTarget) ? $dateTarget : date('Y-m-d H:i:s');
1544
1545   // Collect the current date settings (to ensure not skip)
1546   $res = resolve_reminder_sql($rule, $type.'_current');
1547   if (!empty($res)) {
1548     $row = $res[0];
1549     if ($row ['method_detail'] == "SKIP") {
1550       $dateTarget = "SKIP";
1551     }
1552   }
1553
1554   // Collect the past_due date
1555   $past_due_date == "";
1556   $res = resolve_reminder_sql($rule, $type.'_post');
1557   if (!empty($res)) {
1558     $row = $res[0];
1559     if ($row ['method_detail'] == "week") {
1560       $past_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " -" . $row ['value'] . " week"));
1561     }
1562     if ($row ['method_detail'] == "month") {
1563       $past_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " -" . $row ['value'] . " month"));
1564     }
1565     if ($row ['method_detail'] == "hour") {
1566       $past_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " -" . $row ['value'] . " hour"));
1567     }
1568     if ($row ['method_detail'] == "SKIP") {
1569       $past_due_date = "SKIP";
1570     }
1571   }
1572   else {
1573     // empty settings, so use default of one month
1574     $past_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " -1 month"));
1575   }
1576
1577   // Collect the soon_due date
1578   $soon_due_date == "";
1579   $res = resolve_reminder_sql($rule, $type.'_pre');
1580   if (!empty($res)) {
1581     $row = $res[0];
1582     if ($row ['method_detail'] == "week") {
1583       $soon_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " +" . $row ['value'] . " week"));
1584     }
1585     if ($row ['method_detail'] == "month") {
1586       $soon_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " +" . $row ['value'] . " month"));
1587     }
1588     if ($row ['method_detail'] == "hour") {
1589       $soon_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " -" . $row ['value'] . " hour"));
1590     }
1591     if ($row ['method_detail'] == "SKIP") {
1592       $soon_due_date = "SKIP";
1593     }
1594   }
1595   else {
1596     // empty settings, so use default of one month
1597     $soon_due_date = date("Y-m-d H:i:s", strtotime($dateTarget . " +2 week"));
1598   }
1599
1600   // Return the array of three dates
1601   return array($soon_due_date,$dateTarget,$past_due_date);
1602 }
1603
1604 // Adds an action into the reminder array
1605 // Parameters:
1606 //   $reminderOldArray - Contains the current array of reminders
1607 //   $reminderNew      - Array of a new reminder
1608 // Return:
1609 //   An array of reminders
1610 function reminder_results_integrate($reminderOldArray, $reminderNew) {
1611
1612   $results = array();
1613
1614   // If reminderArray is empty, then insert new reminder
1615   if (empty($reminderOldArray)) {
1616     array_push($results, $reminderNew);
1617     return $results;
1618   }
1619
1620   // If duplicate reminder, then replace the old one
1621   $duplicate = false;
1622   foreach ($reminderOldArray as $reminderOld) {
1623     if (  $reminderOld['pid'] == $reminderNew['pid'] &&
1624           $reminderOld['category'] == $reminderNew['category'] &&
1625           $reminderOld['item'] == $reminderNew['item']) {
1626       array_push($results, $reminderNew);
1627       $duplicate = true;
1628     }
1629     else {
1630       array_push($results, $reminderOld);
1631     }
1632   }
1633
1634   // If a new reminder, then insert the new reminder
1635   if (!$duplicate) {
1636     array_push($results, $reminderNew);
1637   }
1638
1639   return $results;
1640 }
1641
1642 // Compares number of items with requested comparison operator
1643 // Parameters:
1644 //   $comp      - Comparison operator(eq,ne,gt,ge,lt,le)
1645 //   $thres     - Threshold used in comparison
1646 //   $num_items - Number of items
1647 // Return:
1648 //   Boolean of comparison results
1649 function itemsNumberCompare($comp, $thres, $num_items) {
1650
1651   if ( ($comp == "eq") && ($num_items == $thres) ) {
1652     return true;
1653   }
1654   else if ( ($comp == "ne") && ($num_items != $thres) && ($num_items > 0) ) {
1655     return true;
1656   }
1657   else if ( ($comp == "gt") && ($num_items > $thres) ) {
1658     return true;
1659   }
1660   else if ( ($comp == "ge") && ($num_items >= $thres) ) {
1661     return true;
1662   }
1663   else if ( ($comp == "lt") && ($num_items < $thres) && ($num_items > 0) ) {
1664     return true;
1665   }
1666   else if ( ($comp == "le") && ($num_items <= $thres) && ($num_items > 0) ) {
1667     return true;
1668   }
1669   else {
1670     return false;
1671   }
1672 }
1673
1674 // Converts a text comparison operator to sql equivalent
1675 // Parameters:
1676 //   $comp      - Comparison operator(eq,ne,gt,ge,lt,le)
1677 // Return:
1678 //   String containing sql compatible comparison operator
1679 function convertCompSql($comp) {
1680
1681   if ($comp == "eq") {
1682     return "=";
1683   }
1684   else if ($comp == "ne") {
1685     return "!=";
1686   }
1687   else if ($comp == "gt") {
1688     return ">";
1689   }
1690   else if ($comp == "ge") {
1691     return ">=";
1692   }
1693   else if ($comp == "lt") {
1694     return "<";
1695   }
1696   else { // ($comp == "le")
1697     return "<=";
1698   }
1699 }
1700
1701 // Function to find age in years (with decimal) on the target date
1702 // Parameters:
1703 //   $dob        - date of birth
1704 //   $target     - date to calculate age on
1705 // Return: decimal, years(decimal) from dob to target(date)
1706 function convertDobtoAgeYearDecimal($dob,$target) {
1707
1708     // Grab year, month, and day from dob and dateTarget
1709     $dateDOB = explode(" ",$dob);
1710     $dateTarget = explode(" ",$target);
1711
1712     // Collect differences
1713     $iDiffYear  = $dateTarget[0] - $dateDOB[0];
1714     $iDiffMonth = $dateTarget[1] - $dateDOB[1];
1715     $iDiffDay   = $dateTarget[2] - $dateDOB[2];
1716
1717     // If birthday has not happen yet for this year, subtract 1.
1718     if ($iDiffMonth < 0 || ($iDiffMonth == 0 && $iDiffDay < 0))
1719     {
1720         $iDiffYear--;
1721     }
1722
1723     return $iDiffYear;
1724 }
1725
1726 // Function to find age in months (with decimal) on the target date
1727 // Parameters:
1728 //   $dob        - date of birth
1729 //   $target     - date to calculate age on
1730 // Return: decimal, months(decimal) from dob to target(date)
1731 function convertDobtoAgeMonthDecimal($dob,$target) {
1732
1733     // Grab year, month, and day from dob and dateTarget
1734     $dateDOB = explode(" ",$dob);
1735     $dateTarget = explode(" ",$target);
1736
1737     // Collect differences
1738     $iDiffYear  = $dateTarget[0] - $dateDOB[0];
1739     $iDiffMonth = $dateTarget[1] - $dateDOB[1];
1740     $iDiffDay   = $dateTarget[2] - $dateDOB[2];
1741
1742     // If birthday has not happen yet for this year, subtract 1.
1743     if ($iDiffMonth < 0 || ($iDiffMonth == 0 && $iDiffDay < 0))
1744     {
1745         $iDiffYear--;
1746     }
1747
1748     return (12 * $iDiffYear) + $iDiffMonth;
1749 }
1750
1751 // Function to calculate the percentage
1752 // Parameters:
1753 //   $pass_filter    - number of patients that pass filter
1754 //   $exclude_filter - number of patients that are excluded
1755 //   $pass_target    - number of patients that pass target
1756 // Return: String of a number formatted into a percentage
1757 function calculate_percentage($pass_filt,$exclude_filt,$pass_targ) {
1758   if ($pass_filt > 0) {
1759     $perc = number_format(($pass_targ/($pass_filt-$exclude_filt))*100) . xl('%');
1760   }
1761   else {
1762     $perc = "0". xl('%');
1763   }
1764   return $perc;
1765 }
1766