library/patient.inc.php

   1 <?php
   2
   3 /**
   4  * patient.inc.php includes functions for manipulating patient information.
   5  *
   6  * @package   OpenEMR
   7  * @link      http://www.open-emr.org
   8  * @author    Brady Miller <brady.g.miller@gmail.com>
   9  * @author    Sherwin Gaddis <sherwingaddis@gmail.com>
  10  * @author    Stephen Waite <stephen.waite@cmsvt.com>
  11  * @author    Rod Roark <rod@sunsetsystems.com>
  12  * @copyright Copyright (c) 2018-2019 Brady Miller <brady.g.miller@gmail.com>
  13  * @copyright Copyright (c) 2019 Sherwin Gaddis <sherwingaddis@gmail.com>
  14  * @copyright Copyright (c) 2018-2021 Stephen Waite <stephen.waite@cmsvt.com>
  15  * @copyright Copyright (c) 2021-2022 Rod Roark <rod@sunsetsystems.com>
  16  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  17  */
  18
  19 use OpenEMR\Common\Uuid\UuidRegistry;
  20 use OpenEMR\Services\FacilityService;
  21 use OpenEMR\Services\PatientService;
  22 use OpenEMR\Services\SocialHistoryService;
  23
  24 require_once(dirname(__FILE__) . "/dupscore.inc.php");
  25
  26 global $facilityService;
  27 $facilityService = new FacilityService();
  28
  29 // These are for sports team use:
  30 $PLAYER_FITNESSES = array(
  31   xl('Full Play'),
  32   xl('Full Training'),
  33   xl('Restricted Training'),
  34   xl('Injured Out'),
  35   xl('Rehabilitation'),
  36   xl('Illness'),
  37   xl('International Duty')
  38 );
  39 $PLAYER_FITCOLORS = array('#6677ff', '#00cc00', '#ffff00', '#ff3333', '#ff8800', '#ffeecc', '#ffccaa');
  40
  41 // Hard-coding this array because its values and meanings are fixed by the 837p
  42 // standard and we don't want people messing with them.
  43 global $policy_types;
  44 $policy_types = array(
  45   ''   => xl('N/A'),
  46   '12' => xl('Working Aged Beneficiary or Spouse with Employer Group Health Plan'),
  47   '13' => xl('End-Stage Renal Disease Beneficiary in MCP with Employer`s Group Plan'),
  48   '14' => xl('No-fault Insurance including Auto is Primary'),
  49   '15' => xl('Worker`s Compensation'),
  50   '16' => xl('Public Health Service (PHS) or Other Federal Agency'),
  51   '41' => xl('Black Lung'),
  52   '42' => xl('Veteran`s Administration'),
  53   '43' => xl('Disabled Beneficiary Under Age 65 with Large Group Health Plan (LGHP)'),
  54   '47' => xl('Other Liability Insurance is Primary'),
  55 );
  56
  57 /**
  58  * Get a patient's demographic data.
  59  *
  60  * @param int    $pid   The PID of the patient
  61  * @param string $given an optional subsection of the patient's demographic
  62  *                      data to retrieve.
  63  * @return array The requested subsection of a patient's demographic data.
  64  *               If no subsection was given, returns everything, with the
  65  *               date of birth as the last field.
  66  */
  67 // To prevent sql injection on this function, if a variable is used for $given parameter, then
  68 // it needs to be escaped via whitelisting prior to using this function.
  69 function getPatientData($pid, $given = "*, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS")
  70 {
  71     $sql = "select $given from patient_data where pid=? order by date DESC limit 0,1";
  72     return sqlQuery($sql, array($pid));
  73 }
  74
  75 function getInsuranceProvider($ins_id)
  76 {
  77
  78     $sql = "select name from insurance_companies where id=?";
  79     $row = sqlQuery($sql, array($ins_id));
  80     return $row['name'] ?? '';
  81 }
  82
  83 function getInsuranceProviders()
  84 {
  85     $returnval = array();
  86
  87     if (true) {
  88         $sql = "select name, id from insurance_companies where inactive != 1 order by name, id";
  89         $rez = sqlStatement($sql);
  90         for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
  91             $returnval[$row['id']] = $row['name'];
  92         }
  93     } else { // Please leave this here. I have a user who wants to see zip codes and PO
  94         // box numbers listed along with the insurance company names, as many companies
  95         // have different billing addresses for different plans.  -- Rod Roark
  96         $sql = "select insurance_companies.name, insurance_companies.id, " .
  97           "addresses.zip, addresses.line1 " .
  98           "from insurance_companies, addresses " .
  99           "where addresses.foreign_id = insurance_companies.id " .
 100           "order by insurance_companies.name, addresses.zip";
 101
 102         $rez = sqlStatement($sql);
 103
 104         for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 105             preg_match("/\d+/", $row['line1'], $matches);
 106             $returnval[$row['id']] = $row['name'] . " (" . $row['zip'] .
 107               "," . $matches[0] . ")";
 108         }
 109     }
 110
 111     return $returnval;
 112 }
 113
 114 function getInsuranceProvidersExtra()
 115 {
 116     $returnval = array();
 117     // add a global and if for where to allow inactive inscompanies
 118
 119     $sql = "SELECT insurance_companies.name, insurance_companies.id, insurance_companies.cms_id,
 120             addresses.line1, addresses.line2, addresses.city, addresses.state, addresses.zip
 121             FROM insurance_companies, addresses
 122             WHERE addresses.foreign_id = insurance_companies.id
 123             AND insurance_companies.inactive != 1
 124             ORDER BY insurance_companies.name, addresses.zip";
 125
 126     $rez = sqlStatement($sql);
 127
 128     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 129         switch ($GLOBALS['insurance_information']) {
 130             case $GLOBALS['insurance_information'] = '0':
 131                 $returnval[$row['id']] = $row['name'];
 132                 break;
 133             case $GLOBALS['insurance_information'] = '1':
 134                 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ")";
 135                 break;
 136             case $GLOBALS['insurance_information'] = '2':
 137                 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['zip'] . ")";
 138                 break;
 139             case $GLOBALS['insurance_information'] = '3':
 140                 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['state'] . ")";
 141                 break;
 142             case $GLOBALS['insurance_information'] = '4':
 143                 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['state'] .
 144                     ", " . $row['zip'] . ")";
 145                 break;
 146             case $GLOBALS['insurance_information'] = '5':
 147                 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['city'] .
 148                     ", " . $row['state'] . ", " . $row['zip'] . ")";
 149                 break;
 150             case $GLOBALS['insurance_information'] = '6':
 151                 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['city'] .
 152                     ", " . $row['state'] . ", " . $row['zip'] . ", " . $row['cms_id'] . ")";
 153                 break;
 154             case $GLOBALS['insurance_information'] = '7':
 155                 preg_match("/\d+/", $row['line1'], $matches);
 156                 $returnval[$row['id']] = $row['name'] . " (" . $row['zip'] .
 157                     "," . $matches[0] . ")";
 158                 break;
 159         }
 160     }
 161
 162     return $returnval;
 163 }
 164
 165 // ----------------------------------------------------------------------------
 166 // Get one facility row.  If the ID is not specified, then get either the
 167 // "main" (billing) facility, or the default facility of the currently
 168 // logged-in user.  This was created to support genFacilityTitle() but
 169 // may find additional uses.
 170 //
 171 function getFacility($facid = 0)
 172 {
 173     global $facilityService;
 174
 175     $facility = null;
 176
 177     if ($facid > 0) {
 178         return $facilityService->getById($facid);
 179     }
 180
 181     if ($GLOBALS['login_into_facility']) {
 182         //facility is saved in sessions
 183         $facility  = $facilityService->getById($_SESSION['facilityId']);
 184     } else {
 185         if ($facid == 0) {
 186             $facility = $facilityService->getPrimaryBillingLocation();
 187         } else {
 188             $facility = $facilityService->getFacilityForUser($_SESSION['authUserID']);
 189         }
 190     }
 191
 192     return $facility;
 193 }
 194
 195 // Generate a report title including report name and facility name, address
 196 // and phone.
 197 //
 198 function genFacilityTitle($repname = '', $facid = 0, $logo = "")
 199 {
 200     $s = '';
 201     $s .= "<table class='ftitletable' width='100%'>\n";
 202     $s .= " <tr>\n";
 203     if (empty($logo)) {
 204         $s .= "  <td align='left' class='ftitlecell1'>" . text($repname) . "</td>\n";
 205     } else {
 206         $s .= "  <td align='left' class='ftitlecell1'><img class='h-auto' style='max-height:8%;' src='" . attr($logo) . "' /></td>\n";
 207         $s .= "  <td align='left' class='ftitlecellm'><h2>" . text($repname) . "</h2></td>\n";
 208     }
 209     $s .= "  <td align='right' class='ftitlecell2'>\n";
 210     $r = getFacility($facid);
 211     if (!empty($r)) {
 212         $s .= "<b>" . text($r['name'] ?? '') . "</b>\n";
 213         if (!empty($r['street'])) {
 214             $s .= "<br />" . text($r['street']) . "\n";
 215         }
 216
 217         if (!empty($r['city']) || !empty($r['state']) || !empty($r['postal_code'])) {
 218             $s .= "<br />";
 219             if ($r['city']) {
 220                 $s .= text($r['city']);
 221             }
 222
 223             if ($r['state']) {
 224                 if ($r['city']) {
 225                     $s .= ", \n";
 226                 }
 227
 228                 $s .= text($r['state']);
 229             }
 230
 231             if ($r['postal_code']) {
 232                 $s .= " " . text($r['postal_code']);
 233             }
 234
 235             $s .= "\n";
 236         }
 237
 238         if (!empty($r['country_code'])) {
 239             $s .= "<br />" . text($r['country_code']) . "\n";
 240         }
 241
 242         if (preg_match('/[1-9]/', ($r['phone'] ?? ''))) {
 243             $s .= "<br />" . text($r['phone']) . "\n";
 244         }
 245     }
 246
 247     $s .= "  </td>\n";
 248     $s .= " </tr>\n";
 249     $s .= "</table>\n";
 250     return $s;
 251 }
 252
 253 /**
 254 GET FACILITIES
 255
 256 returns all facilities or just the id for the first one
 257 (FACILITY FILTERING (lemonsoftware))
 258
 259 @param string - if 'first' return first facility ordered by id
 260 @return array | int for 'first' case
 261 */
 262 function getFacilities($first = '')
 263 {
 264     global $facilityService;
 265
 266     $fres = $facilityService->getAllFacility();
 267
 268     if ($first == 'first') {
 269         return $fres[0]['id'];
 270     } else {
 271         return $fres;
 272     }
 273 }
 274
 275 //(CHEMED) facility filter
 276 function getProviderInfo($providerID = "%", $providers_only = true, $facility = '')
 277 {
 278     $param1 = "";
 279     if ($providers_only === 'any') {
 280         $param1 = " AND authorized = 1 AND active = 1 ";
 281     } elseif ($providers_only) {
 282         $param1 = " AND authorized = 1 AND calendar = 1 ";
 283     }
 284
 285     //--------------------------------
 286     //(CHEMED) facility filter
 287     $param2 = "";
 288     if ($facility) {
 289         if ($GLOBALS['restrict_user_facility']) {
 290             $param2 = " AND (facility_id = '" . add_escape_custom($facility) . "' OR  '" . add_escape_custom($facility) . "' IN (select facility_id from users_facility where tablename = 'users' and table_id = id))";
 291         } else {
 292             $param2 = " AND facility_id = '" . add_escape_custom($facility) . "' ";
 293         }
 294     }
 295
 296     //--------------------------------
 297
 298     $command = "=";
 299     if ($providerID == "%") {
 300         $command = "like";
 301     }
 302
 303 // removing active from query since is checked above with $providers_only argument
 304     $query = "select distinct id, username, lname, fname, authorized, info, facility, suffix, valedictory " .
 305         "from users where username != '' and id $command '" .
 306         add_escape_custom($providerID) . "' " . $param1 . $param2;
 307     // sort by last name -- JRM June 2008
 308     $query .= " ORDER BY lname, fname ";
 309     $rez = sqlStatement($query);
 310     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 311         $returnval[$iter] = $row;
 312     }
 313
 314     //if only one result returned take the key/value pairs in array [0] and merge them down into
 315     // the base array so that $resultval[0]['key'] is also accessible from $resultval['key']
 316
 317     if ($iter == 1) {
 318         $akeys = array_keys($returnval[0]);
 319         foreach ($akeys as $key) {
 320             $returnval[0][$key] = $returnval[0][$key];
 321         }
 322     }
 323
 324     return ($returnval ?? null);
 325 }
 326
 327 function getProviderName($providerID, $provider_only = 'any')
 328 {
 329     $pi = getProviderInfo($providerID, $provider_only);
 330     if (!empty($pi[0]["lname"]) && (strlen($pi[0]["lname"]) > 0)) {
 331         if (!empty($pi[0]["suffix"]) && (strlen($pi[0]["suffix"]) > 0)) {
 332             $pi[0]["lname"] .= ", " . $pi[0]["suffix"];
 333         }
 334
 335         if (!empty($pi[0]["valedictory"]) && (strlen($pi[0]["valedictory"]) > 0)) {
 336             $pi[0]["lname"] .= ", " . $pi[0]["valedictory"];
 337         }
 338
 339         return $pi[0]['fname'] . " " . $pi[0]['lname'];
 340     }
 341
 342     return "";
 343 }
 344
 345 function getProviderId($providerName)
 346 {
 347     $query = "select id from users where username = ?";
 348     $rez = sqlStatement($query, array($providerName));
 349     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 350         $returnval[$iter] = $row;
 351     }
 352
 353     return $returnval;
 354 }
 355
 356 // To prevent sql injection on this function, if a variable is used for $given parameter, then
 357 // it needs to be escaped via whitelisting prior to using this function; see lines 2020-2121 of
 358 // library/clinical_rules.php script for example of this.
 359 function getHistoryData($pid, $given = "*", $dateStart = '', $dateEnd = '')
 360 {
 361     $where = '';
 362     if ($given == 'tobacco') {
 363         $where = 'tobacco is not null and';
 364     }
 365
 366     if ($dateStart && $dateEnd) {
 367         $res = sqlQuery("select $given from history_data where $where pid = ? and date >= ? and date <= ? order by date DESC, id DESC limit 0,1", array($pid,$dateStart,$dateEnd));
 368     } elseif ($dateStart && !$dateEnd) {
 369         $res = sqlQuery("select $given from history_data where $where pid = ? and date >= ? order by date DESC, id DESC limit 0,1", array($pid,$dateStart));
 370     } elseif (!$dateStart && $dateEnd) {
 371         $res = sqlQuery("select $given from history_data where $where pid = ? and date <= ? order by date DESC, id DESC limit 0,1", array($pid,$dateEnd));
 372     } else {
 373         $res = sqlQuery("select $given from history_data where $where pid = ? order by date DESC, id DESC limit 0,1", array($pid));
 374     }
 375
 376     return $res;
 377 }
 378
 379 // function getInsuranceData($pid, $type = "primary", $given = "insd.*, DATE_FORMAT(subscriber_DOB,'%m/%d/%Y') as subscriber_DOB, ic.name as provider_name")
 380 // To prevent sql injection on this function, if a variable is used for $given parameter, then
 381 // it needs to be escaped via whitelisting prior to using this function.
 382 function getInsuranceData($pid, $type = "primary", $given = "insd.*, ic.name as provider_name")
 383 {
 384     $sql = "select $given from insurance_data as insd " .
 385     "left join insurance_companies as ic on ic.id = insd.provider " .
 386     "where pid = ? and type = ? order by date DESC limit 1";
 387     return sqlQuery($sql, array($pid, $type));
 388 }
 389
 390 // To prevent sql injection on this function, if a variable is used for $given parameter, then
 391 // it needs to be escaped via whitelisting prior to using this function.
 392 function getInsuranceDataByDate(
 393     $pid,
 394     $date,
 395     $type,
 396     $given = "insd.*, DATE_FORMAT(subscriber_DOB,'%m/%d/%Y') as subscriber_DOB, ic.name as provider_name"
 397 ) {
 398   /*
 399    This must take the date in the following manner: YYYY-MM-DD.
 400    This function recalls the insurance value that was most recently entered from the
 401    given date and before the insurance end date. It will call up most recent records up to and on the date given,
 402    but not records entered after the given date.
 403    */
 404     $sql = "select $given from insurance_data as insd " .
 405     "left join insurance_companies as ic on ic.id = provider " .
 406     "where pid = ? and (date_format(date,'%Y-%m-%d') <= ? OR date IS NULL) and " .
 407     "(date_format(date_end,'%Y-%m-%d') >= ? OR date_end IS NULL) and " .
 408     "type = ? order by date DESC limit 1";
 409     return sqlQuery($sql, array($pid, $date, $date, $type));
 410 }
 411
 412 function get_unallocated_patient_balance($pid)
 413 {
 414     $unallocated = 0.0;
 415     $query = "SELECT a.session_id, a.pay_total, a.global_amount " .
 416         "FROM ar_session AS a " .
 417         "WHERE a.patient_id = ? AND " .
 418         "a.adjustment_code = 'pre_payment' AND a.closed = 0";
 419     $res = sqlStatement($query, array($pid));
 420     while ($row = sqlFetchArray($res)) {
 421         $total_amt = $row['pay_total'] - $row['global_amount'];
 422         $rs = sqlQuery("SELECT sum(pay_amount) AS total_pay_amt FROM ar_activity WHERE session_id = ? AND pid = ? AND deleted IS NULL", array($row['session_id'], $pid));
 423         $pay_amount = $rs['total_pay_amt'];
 424         $unallocated += ($total_amt - $pay_amount);
 425     }
 426     return sprintf('%01.2f', $unallocated);
 427 }
 428
 429 function getInsuranceNameByDate(
 430     $pid,
 431     $date,
 432     $type,
 433     $given = "ic.name as provider_name"
 434 ) {
 435  // this must take the date in the following manner: YYYY-MM-DD
 436   // this function recalls the insurance value that was most recently enterred from the
 437   // given date. it will call up most recent records up to and on the date given,
 438   // but not records enterred after the given date
 439     $sql = "select $given from insurance_data as insd " .
 440     "left join insurance_companies as ic on ic.id = provider " .
 441     "where pid = ? and (date_format(date,'%Y-%m-%d') <= ? OR date IS NULL) and " .
 442     "(date_format(date_end,'%Y-%m-%d') >= ? OR date_end IS NULL) and " .
 443     "type = ? order by date DESC limit 1";
 444
 445     $row = sqlQuery($sql, array($pid, $date, $date, $type));
 446     return $row['provider_name'];
 447 }
 448
 449 // To prevent sql injection on this function, if a variable is used for $given parameter, then
 450 // it needs to be escaped via whitelisting prior to using this function.
 451 function getEmployerData($pid, $given = "*")
 452 {
 453     $sql = "select $given from employer_data where pid = ? order by date DESC limit 0,1";
 454     return sqlQuery($sql, array($pid));
 455 }
 456
 457 // Generate a consistent header and footer, used for printed patient reports
 458 function genPatientHeaderFooter($pid, $DOS = null)
 459 {
 460     $patient_dob = getPatientData($pid, "DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS");
 461     $patient_name = getPatientName($pid);
 462
 463     // Header
 464     $s = '<htmlpageheader name="PageHeader1"><div style="text-align: right; font-weight: bold;">';
 465     $s .= text($patient_name) . '&emsp;DOB: ' . text($patient_dob['DOB_TS']);
 466     if ($DOS) {
 467         $s .= '&emsp;DOS: ' . text($DOS);
 468     }
 469     $s .= '</div></htmlpageheader>';
 470
 471     // Footer
 472     $s .= '<htmlpagefooter name="PageFooter1"><div style="text-align: right; font-weight: bold;">';
 473     $s .= '<div style="float: right; width:33%; text-align: left;">' . oeFormatDateTime(date("Y-m-d H:i:s")) . '</div>';
 474     $s .= '<div style="float: right; width:33%; text-align: center;">{PAGENO}/{nbpg}</div>';
 475     $s .= '<div style="float: right; width:33%; text-align: right;">' . text($patient_name) . '</div>';
 476     $s .= '</div></htmlpagefooter>';
 477
 478     // Set the header and footer in the current document
 479     $s .= '<sethtmlpageheader name="PageHeader1" page="ALL" value="ON" show-this-page="1" />';
 480     $s .= '<sethtmlpagefooter name="PageFooter1" page="ALL" value="ON" />';
 481
 482     return $s;
 483 }
 484
 485 function _set_patient_inc_count($limit, $count, $where, $whereBindArray = array())
 486 {
 487   // When the limit is exceeded, find out what the unlimited count would be.
 488     $GLOBALS['PATIENT_INC_COUNT'] = $count;
 489   // if ($limit != "all" && $GLOBALS['PATIENT_INC_COUNT'] >= $limit) {
 490     if ($limit != "all") {
 491         $tmp = sqlQuery("SELECT count(*) AS count FROM patient_data WHERE $where", $whereBindArray);
 492         $GLOBALS['PATIENT_INC_COUNT'] = $tmp['count'];
 493     }
 494 }
 495
 496 /**
 497  * Allow the last name to be followed by a comma and some part of a first name(can
 498  *   also place middle name after the first name with a space separating them)
 499  * Allows comma alone followed by some part of a first name(can also place middle name
 500  *   after the first name with a space separating them).
 501  * Allows comma alone preceded by some part of a last name.
 502  * If no comma or space, then will search both last name and first name.
 503  * If the first letter of either name is capital, searches for name starting
 504  *   with given substring (the expected behavior). If it is lower case, it
 505  *   searches for the substring anywhere in the name. This applies to either
 506  *   last name, first name, and middle name.
 507  * Also allows first name followed by middle and/or last name when separated by spaces.
 508  * @param string $term
 509  * @param string $given
 510  * @param string $orderby
 511  * @param string $limit
 512  * @param string $start
 513  * @return array
 514  */
 515 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 516 // it needs to be escaped via whitelisting prior to using this function.
 517 function getPatientLnames($term = "%", $given = "pid, id, lname, fname, mname, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS", $orderby = "lname ASC, fname ASC", $limit = "all", $start = "0")
 518 {
 519     $names = getPatientNameSplit($term);
 520
 521     foreach ($names as $key => $val) {
 522         if (!empty($val)) {
 523             if ((strlen($val) > 1) && ($names[$key][0] != strtoupper($names[$key][0]))) {
 524                 $names[$key] = '%' . $val . '%';
 525             } else {
 526                 $names[$key] = $val . '%';
 527             }
 528         }
 529     }
 530
 531     // Debugging section below
 532     //if(array_key_exists('first',$names)) {
 533     //    error_log("first name search term :".$names['first']);
 534     //}
 535     //if(array_key_exists('middle',$names)) {
 536     //    error_log("middle name search term :".$names['middle']);
 537     //}
 538     //if(array_key_exists('last',$names)) {
 539     //    error_log("last name search term :".$names['last']);
 540     //}
 541     // Debugging section above
 542
 543     $sqlBindArray = array();
 544     if (array_key_exists('last', $names) && $names['last'] == '') {
 545         // Do not search last name
 546         $where = "fname LIKE ? ";
 547         array_push($sqlBindArray, $names['first']);
 548         if ($names['middle'] != '') {
 549             $where .= "AND mname LIKE ? ";
 550             array_push($sqlBindArray, $names['middle']);
 551         }
 552     } elseif (array_key_exists('first', $names) && $names['first'] == '') {
 553         // Do not search first name or middle name
 554         $where = "lname LIKE ? ";
 555         array_push($sqlBindArray, $names['last']);
 556     } elseif (empty($names['first']) && !empty($names['last'])) {
 557         // Search both first name and last name with same term
 558         $names['first'] = $names['last'];
 559         $where = "lname LIKE ? OR fname LIKE ? ";
 560         array_push($sqlBindArray, $names['last'], $names['first']);
 561     } elseif ($names['middle'] != '') {
 562         $where = "lname LIKE ? AND fname LIKE ? AND mname LIKE ? ";
 563         array_push($sqlBindArray, $names['last'], $names['first'], $names['middle']);
 564     } else {
 565         $where = "lname LIKE ? AND fname LIKE ? ";
 566         array_push($sqlBindArray, $names['last'], $names['first']);
 567     }
 568
 569     if (!empty($GLOBALS['pt_restrict_field'])) {
 570         if ($_SESSION["authUser"] != 'admin' || $GLOBALS['pt_restrict_admin']) {
 571             $where .= " AND ( patient_data." . add_escape_custom($GLOBALS['pt_restrict_field']) .
 572                 " = ( SELECT facility_id FROM users WHERE username = ?) OR patient_data." .
 573                 add_escape_custom($GLOBALS['pt_restrict_field']) . " = '' ) ";
 574             array_push($sqlBindArray, $_SESSION["authUser"]);
 575         }
 576     }
 577
 578     $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
 579     if ($limit != "all") {
 580         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 581     }
 582
 583     $rez = sqlStatement($sql, $sqlBindArray);
 584
 585     $returnval = array();
 586     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 587         $returnval[$iter] = $row;
 588     }
 589
 590     if (is_countable($returnval)) {
 591         _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
 592     }
 593
 594     return $returnval;
 595 }
 596 /**
 597  * Accept a string used by a search function expected to find a patient name,
 598  * then split up the string if a comma or space exists. Return an array having
 599  * from 1 to 3 elements, named first, middle, and last.
 600  * See above getPatientLnames() function for details on how the splitting occurs.
 601  * @param string $term
 602  * @return array
 603  */
 604 function getPatientNameSplit($term)
 605 {
 606     $term = trim($term);
 607     if (strpos($term, ',') !== false) {
 608         $names = explode(',', $term);
 609         $n['last'] = $names[0];
 610         if (strpos(trim($names[1]), ' ') !== false) {
 611             list($n['first'], $n['middle']) = explode(' ', trim($names[1]));
 612         } else {
 613             $n['first'] = $names[1];
 614         }
 615     } elseif (strpos($term, ' ') !== false) {
 616         $names = explode(' ', $term);
 617         if (count($names) == 1) {
 618             $n['last'] = $names[0];
 619         } elseif (count($names) == 3) {
 620             $n['first'] = $names[0];
 621             $n['middle'] = $names[1];
 622             $n['last'] = $names[2];
 623         } else {
 624             // This will handle first and last name or first followed by
 625             // multiple names only using just the last of the names in the list.
 626             $n['first'] = $names[0];
 627             $n['last'] = end($names);
 628         }
 629     } else {
 630         $n['last'] = $term;
 631         if (empty($n['last'])) {
 632             $n['last'] = '%';
 633         }
 634     }
 635
 636     // Trim whitespace off the names before returning
 637     foreach ($n as $key => $val) {
 638         $n[$key] = trim($val);
 639     }
 640
 641     return $n; // associative array containing names
 642 }
 643
 644 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 645 // it needs to be escaped via whitelisting prior to using this function.
 646 function getPatientId($pid = "%", $given = "pid, id, lname, fname, mname, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS", $orderby = "lname ASC, fname ASC", $limit = "all", $start = "0")
 647 {
 648
 649     $sqlBindArray = array();
 650     $where = "pubpid LIKE ? ";
 651     array_push($sqlBindArray, $pid . "%");
 652     if (!empty($GLOBALS['pt_restrict_field']) && $GLOBALS['pt_restrict_by_id']) {
 653         if ($_SESSION["authUser"] != 'admin' || $GLOBALS['pt_restrict_admin']) {
 654             $where .= "AND ( patient_data." . add_escape_custom($GLOBALS['pt_restrict_field']) .
 655                     " = ( SELECT facility_id FROM users WHERE username = ?) OR patient_data." .
 656                     add_escape_custom($GLOBALS['pt_restrict_field']) . " = '' ) ";
 657             array_push($sqlBindArray, $_SESSION["authUser"]);
 658         }
 659     }
 660
 661     $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
 662     if ($limit != "all") {
 663         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 664     }
 665
 666     $rez = sqlStatement($sql, $sqlBindArray);
 667     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 668         $returnval[$iter] = $row;
 669     }
 670
 671     if (is_countable($returnval)) {
 672         _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
 673     }
 674     return $returnval;
 675 }
 676
 677 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 678 // it needs to be escaped via whitelisting prior to using this function.
 679 function getByPatientDemographics($searchTerm = "%", $given = "pid, id, lname, fname, mname, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS", $orderby = "lname ASC, fname ASC", $limit = "all", $start = "0")
 680 {
 681     $layoutCols = sqlStatement(
 682         "SELECT field_id FROM layout_options WHERE form_id = 'DEM' AND field_id not like ? AND uor != 0",
 683         array('em\_%')
 684     );
 685
 686     $sqlBindArray = array();
 687     $where = "";
 688     for ($iter = 0; $row = sqlFetchArray($layoutCols); $iter++) {
 689         if ($iter > 0) {
 690             $where .= " or ";
 691         }
 692
 693         $where .= " " . add_escape_custom($row["field_id"]) . " like ? ";
 694         array_push($sqlBindArray, "%" . $searchTerm . "%");
 695     }
 696
 697     $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
 698     if ($limit != "all") {
 699         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 700     }
 701
 702     $rez = sqlStatement($sql, $sqlBindArray);
 703     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 704         $returnval[$iter] = $row;
 705     }
 706
 707     if (is_countable($returnval)) {
 708         _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
 709     }
 710     return $returnval;
 711 }
 712
 713 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 714 // it needs to be escaped via whitelisting prior to using this function.
 715 function getByPatientDemographicsFilter(
 716     $searchFields,
 717     $searchTerm = "%",
 718     $given = "pid, id, lname, fname, mname, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS",
 719     $orderby = "lname ASC, fname ASC",
 720     $limit = "all",
 721     $start = "0",
 722     $search_service_code = ''
 723 ) {
 724
 725     $layoutCols = explode('~', $searchFields);
 726     $sqlBindArray = array();
 727     $where = "";
 728     $i = 0;
 729     foreach ($layoutCols as $val) {
 730         if (empty($val)) {
 731             continue;
 732         }
 733
 734         if ($i > 0) {
 735             $where .= " or ";
 736         }
 737
 738         if ($val == 'pid') {
 739             $where .= " " . escape_sql_column_name($val, ['patient_data']) . " = ? ";
 740                 array_push($sqlBindArray, $searchTerm);
 741         } else {
 742             $where .= " " . escape_sql_column_name($val, ['patient_data']) . " like ? ";
 743                 array_push($sqlBindArray, $searchTerm . "%");
 744         }
 745
 746         $i++;
 747     }
 748
 749   // If no search terms, ensure valid syntax.
 750     if ($i == 0) {
 751         $where = "1 = 1";
 752     }
 753
 754   // If a non-empty service code was given, then restrict to patients who
 755   // have been provided that service.  Since the code is used in a LIKE
 756   // clause, % and _ wildcards are supported.
 757     if ($search_service_code) {
 758         $where = "( $where ) AND " .
 759         "( SELECT COUNT(*) FROM billing AS b WHERE " .
 760         "b.pid = patient_data.pid AND " .
 761         "b.activity = 1 AND " .
 762         "b.code_type != 'COPAY' AND " .
 763         "b.code LIKE ? " .
 764         ") > 0";
 765         array_push($sqlBindArray, $search_service_code);
 766     }
 767
 768     $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
 769     if ($limit != "all") {
 770         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 771     }
 772
 773     $rez = sqlStatement($sql, $sqlBindArray);
 774     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 775         $returnval[$iter] = $row;
 776     }
 777
 778     if (is_countable($returnval)) {
 779         _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
 780     }
 781     return $returnval;
 782 }
 783
 784 // return a collection of Patient PIDs
 785 // new arg style by JRM March 2008
 786 // orig function getPatientPID($pid = "%", $given = "pid, id, lname, fname, mname, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS", $orderby = "lname ASC, fname ASC", $limit="all", $start="0")
 787 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 788 // it needs to be escaped via whitelisting prior to using this function.
 789 function getPatientPID($args)
 790 {
 791     $pid = "%";
 792     $given = "pid, id, lname, fname, mname, suffix, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS";
 793     $orderby = "lname ASC, fname ASC";
 794     $limit = "all";
 795     $start = "0";
 796
 797     // alter default values if defined in the passed in args
 798     if (isset($args['pid'])) {
 799         $pid = $args['pid'];
 800     }
 801
 802     if (isset($args['given'])) {
 803         $given = $args['given'];
 804     }
 805
 806     if (isset($args['orderby'])) {
 807         $orderby = $args['orderby'];
 808     }
 809
 810     if (isset($args['limit'])) {
 811         $limit = $args['limit'];
 812     }
 813
 814     if (isset($args['start'])) {
 815         $start = $args['start'];
 816     }
 817
 818     $command = "=";
 819     if ($pid == -1) {
 820         $pid = "%";
 821     } elseif (empty($pid)) {
 822         $pid = "NULL";
 823     }
 824
 825     if (strstr($pid, "%")) {
 826         $command = "like";
 827     }
 828
 829     $sql = "select $given from patient_data where pid $command '" . add_escape_custom($pid) . "' order by $orderby";
 830     if ($limit != "all") {
 831         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 832     }
 833
 834     $rez = sqlStatement($sql);
 835     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 836         $returnval[$iter] = $row;
 837     }
 838
 839     return $returnval;
 840 }
 841
 842 /* return a patient's name in the format LAST [SUFFIX], FIRST [MIDDLE] */
 843 function getPatientName($pid)
 844 {
 845     if (empty($pid)) {
 846         return "";
 847     }
 848
 849     $patientData = getPatientPID(array("pid" => $pid));
 850     if (empty($patientData[0]['lname'])) {
 851         return "";
 852     }
 853
 854     $patientName = $patientData[0]['lname'];
 855     $patientName .= $patientData[0]['suffix'] ? " " . $patientData[0]['suffix'] . ", " : ", ";
 856     $patientName .= $patientData[0]['fname'];
 857     $patientName .= empty($patientData[0]['mname']) ? "" : " " . $patientData[0]['mname'];
 858     return $patientName;
 859 }
 860
 861 /**
 862  * Get a patient's first name, middle name, last name and suffix if applicable.
 863  *
 864  * Returns a properly formatted, complete name when applicable. Example name
 865  * would be "John B Doe Jr". No additional punctuation is added. Spaces are
 866  * correctly omitted if the middle name of suffix does not apply.
 867  *
 868  * @var $pid int The Patient ID
 869  * @returns string The Full Name
 870  */
 871 function getPatientFullNameAsString($pid): string
 872 {
 873     if (empty($pid)) {
 874         return '';
 875     }
 876     $ptData = getPatientPID(["pid" => $pid]);
 877     $pt = $ptData[0];
 878
 879     if (empty($pt['lname'])) {
 880         return "";
 881     }
 882
 883     $name = $pt['fname'];
 884
 885     if ($pt['mname']) {
 886         $name .= " {$pt['mname']}";
 887     }
 888
 889     $name .= " {$pt['lname']}";
 890
 891     if ($pt['suffix']) {
 892         $name .= " {$pt['suffix']}";
 893     }
 894
 895     return $name;
 896 }
 897
 898 /* return a patient's name in the format FIRST LAST */
 899 function getPatientNameFirstLast($pid)
 900 {
 901     if (empty($pid)) {
 902         return "";
 903     }
 904
 905     $patientData = getPatientPID(array("pid" => $pid));
 906     if (empty($patientData[0]['lname'])) {
 907         return "";
 908     }
 909
 910     $patientName =  $patientData[0]['fname'] . " " . $patientData[0]['lname'];
 911     return $patientName;
 912 }
 913
 914 /* find patient data by DOB */
 915 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 916 // it needs to be escaped via whitelisting prior to using this function.
 917 function getPatientDOB($DOB = "%", $given = "pid, id, lname, fname, mname", $orderby = "lname ASC, fname ASC", $limit = "all", $start = "0")
 918 {
 919     $sqlBindArray = array();
 920     $where = "DOB like ? ";
 921     array_push($sqlBindArray, $DOB . "%");
 922     if (!empty($GLOBALS['pt_restrict_field'])) {
 923         if ($_SESSION["authUser"] != 'admin' || $GLOBALS['pt_restrict_admin']) {
 924             $where .= "AND ( patient_data." . add_escape_custom($GLOBALS['pt_restrict_field']) .
 925                     " = ( SELECT facility_id FROM users WHERE username = ?) OR patient_data." .
 926                     add_escape_custom($GLOBALS['pt_restrict_field']) . " = '' ) ";
 927             array_push($sqlBindArray, $_SESSION["authUser"]);
 928         }
 929     }
 930
 931     $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
 932
 933     if ($limit != "all") {
 934         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 935     }
 936
 937     $rez = sqlStatement($sql, $sqlBindArray);
 938     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 939         $returnval[$iter] = $row;
 940     }
 941
 942     if (is_countable($returnval)) {
 943         _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
 944     }
 945     return $returnval;
 946 }
 947
 948 /* find patient data by SSN */
 949 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 950 // it needs to be escaped via whitelisting prior to using this function.
 951 function getPatientSSN($ss = "%", $given = "pid, id, lname, fname, mname, providerID", $orderby = "lname ASC, fname ASC", $limit = "all", $start = "0")
 952 {
 953     $sqlBindArray = array();
 954     $where = "ss LIKE ?";
 955     array_push($sqlBindArray, $ss . "%");
 956     $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
 957     if ($limit != "all") {
 958         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 959     }
 960
 961     $rez = sqlStatement($sql, $sqlBindArray);
 962     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 963         $returnval[$iter] = $row;
 964     }
 965
 966     if (is_countable($returnval)) {
 967         _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
 968     }
 969     return $returnval;
 970 }
 971
 972 //(CHEMED) Search by phone number
 973 // To prevent sql injection on this function, if a variable is used for $given OR $orderby parameter, then
 974 // it needs to be escaped via whitelisting prior to using this function.
 975 function getPatientPhone($phone = "%", $given = "pid, id, lname, fname, mname, providerID", $orderby = "lname ASC, fname ASC", $limit = "all", $start = "0")
 976 {
 977     $phone = preg_replace("/[[:punct:]]/", "", $phone);
 978     $sqlBindArray = array();
 979     $where = "REPLACE(REPLACE(phone_home, '-', ''), ' ', '') REGEXP ?";
 980     array_push($sqlBindArray, $phone);
 981     $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
 982     if ($limit != "all") {
 983         $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
 984     }
 985
 986     $rez = sqlStatement($sql, $sqlBindArray);
 987     for ($iter = 0; $row = sqlFetchArray($rez); $iter++) {
 988         $returnval[$iter] = $row;
 989     }
 990
 991     if (is_countable($returnval)) {
 992         _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
 993     }
 994     return $returnval;
 995 }
 996
 997 //----------------------input functions
 998 function newPatientData(
 999     $db_id = "",
1000     $title = "",
1001     $fname = "",
1002     $lname = "",
1003     $mname = "",
1004     $sex = "",
1005     $DOB = "",
1006     $street = "",
1007     $postal_code = "",
1008     $city = "",
1009     $state = "",
1010     $country_code = "",
1011     $ss = "",
1012     $occupation = "",
1013     $phone_home = "",
1014     $phone_biz = "",
1015     $phone_contact = "",
1016     $status = "",
1017     $contact_relationship = "",
1018     $referrer = "",
1019     $referrerID = "",
1020     $email = "",
1021     $language = "",
1022     $ethnoracial = "",
1023     $interpretter = "",
1024     $migrantseasonal = "",
1025     $family_size = "",
1026     $monthly_income = "",
1027     $homeless = "",
1028     $financial_review = "",
1029     $pubpid = "",
1030     $pid = "MAX(pid)+1",
1031     $providerID = "",
1032     $genericname1 = "",
1033     $genericval1 = "",
1034     $genericname2 = "",
1035     $genericval2 = "",
1036     $billing_note = "",
1037     $phone_cell = "",
1038     $hipaa_mail = "",
1039     $hipaa_voice = "",
1040     $squad = 0,
1041     $pharmacy_id = 0,
1042     $drivers_license = "",
1043     $hipaa_notice = "",
1044     $hipaa_message = "",
1045     $regdate = ""
1046 ) {
1047
1048     $fitness = 0;
1049     $referral_source = '';
1050     if ($pid) {
1051         $rez = sqlQuery("select id, fitness, referral_source from patient_data where pid = ?", array($pid));
1052         // Check for brain damage:
1053         if ($db_id != $rez['id']) {
1054             $errmsg = "Internal error: Attempt to change patient_data.id from '" .
1055               text($rez['id']) . "' to '" . text($db_id) . "' for pid '" . text($pid) . "'";
1056             die($errmsg);
1057         }
1058
1059         $fitness = $rez['fitness'];
1060         $referral_source = $rez['referral_source'];
1061     }
1062
1063     // Get the default price level.
1064     $lrow = sqlQuery("SELECT option_id FROM list_options WHERE " .
1065       "list_id = 'pricelevel' AND activity = 1 ORDER BY is_default DESC, seq ASC LIMIT 1");
1066     $pricelevel = empty($lrow['option_id']) ? '' : $lrow['option_id'];
1067
1068     $query = ("replace into patient_data set
1069         id='" . add_escape_custom($db_id) . "',
1070         title='" . add_escape_custom($title) . "',
1071         fname='" . add_escape_custom($fname) . "',
1072         lname='" . add_escape_custom($lname) . "',
1073         mname='" . add_escape_custom($mname) . "',
1074         sex='" . add_escape_custom($sex) . "',
1075         DOB='" . add_escape_custom($DOB) . "',
1076         street='" . add_escape_custom($street) . "',
1077         postal_code='" . add_escape_custom($postal_code) . "',
1078         city='" . add_escape_custom($city) . "',
1079         state='" . add_escape_custom($state) . "',
1080         country_code='" . add_escape_custom($country_code) . "',
1081         drivers_license='" . add_escape_custom($drivers_license) . "',
1082         ss='" . add_escape_custom($ss) . "',
1083         occupation='" . add_escape_custom($occupation) . "',
1084         phone_home='" . add_escape_custom($phone_home) . "',
1085         phone_biz='" . add_escape_custom($phone_biz) . "',
1086         phone_contact='" . add_escape_custom($phone_contact) . "',
1087         status='" . add_escape_custom($status) . "',
1088         contact_relationship='" . add_escape_custom($contact_relationship) . "',
1089         referrer='" . add_escape_custom($referrer) . "',
1090         referrerID='" . add_escape_custom($referrerID) . "',
1091         email='" . add_escape_custom($email) . "',
1092         language='" . add_escape_custom($language) . "',
1093         ethnoracial='" . add_escape_custom($ethnoracial) . "',
1094         interpretter='" . add_escape_custom($interpretter) . "',
1095         migrantseasonal='" . add_escape_custom($migrantseasonal) . "',
1096         family_size='" . add_escape_custom($family_size) . "',
1097         monthly_income='" . add_escape_custom($monthly_income) . "',
1098         homeless='" . add_escape_custom($homeless) . "',
1099         financial_review='" . add_escape_custom($financial_review) . "',
1100         pubpid='" . add_escape_custom($pubpid) . "',
1101         pid= '" . add_escape_custom($pid) . "',
1102         providerID = '" . add_escape_custom($providerID) . "',
1103         genericname1 = '" . add_escape_custom($genericname1) . "',
1104         genericval1 = '" . add_escape_custom($genericval1) . "',
1105         genericname2 = '" . add_escape_custom($genericname2) . "',
1106         genericval2 = '" . add_escape_custom($genericval2) . "',
1107         billing_note= '" . add_escape_custom($billing_note) . "',
1108         phone_cell = '" . add_escape_custom($phone_cell) . "',
1109         pharmacy_id = '" . add_escape_custom($pharmacy_id) . "',
1110         hipaa_mail = '" . add_escape_custom($hipaa_mail) . "',
1111         hipaa_voice = '" . add_escape_custom($hipaa_voice) . "',
1112         hipaa_notice = '" . add_escape_custom($hipaa_notice) . "',
1113         hipaa_message = '" . add_escape_custom($hipaa_message) . "',
1114         squad = '" . add_escape_custom($squad) . "',
1115         fitness='" . add_escape_custom($fitness) . "',
1116         referral_source='" . add_escape_custom($referral_source) . "',
1117         regdate='" . add_escape_custom($regdate) . "',
1118         pricelevel='" . add_escape_custom($pricelevel) . "',
1119         date=NOW()");
1120
1121     $id = sqlInsert($query);
1122
1123     if (!$db_id) {
1124       // find the last inserted id for new patient case
1125         $db_id = $id;
1126     }
1127
1128     $foo = sqlQuery("select `pid`, `uuid` from `patient_data` where `id` = ? order by `date` limit 0,1", array($id));
1129
1130     // set uuid if not set yet (if this was an insert and not an update)
1131     if (empty($foo['uuid'])) {
1132         $uuid = (new UuidRegistry(['table_name' => 'patient_data']))->createUuid();
1133         sqlStatementNoLog("UPDATE `patient_data` SET `uuid` = ? WHERE `id` = ?", [$uuid, $id]);
1134     }
1135
1136     return $foo['pid'];
1137 }
1138
1139 // Supported input date formats are:
1140 //   mm/dd/yyyy
1141 //   mm/dd/yy   (assumes 20yy for yy < 10, else 19yy)
1142 //   yyyy/mm/dd
1143 //   also mm-dd-yyyy, etc. and mm.dd.yyyy, etc.
1144 //
1145 function fixDate($date, $default = "0000-00-00")
1146 {
1147     $fixed_date = $default;
1148     $date = trim($date);
1149     if (preg_match("'^[0-9]{1,4}[/.-][0-9]{1,2}[/.-][0-9]{1,4}$'", $date)) {
1150         $dmy = preg_split("'[/.-]'", $date);
1151         if ($dmy[0] > 99) {
1152             $fixed_date = sprintf("%04u-%02u-%02u", $dmy[0], $dmy[1], $dmy[2]);
1153         } else {
1154             if ($dmy[0] != 0 || $dmy[1] != 0 || $dmy[2] != 0) {
1155                 if ($dmy[2] < 1000) {
1156                     $dmy[2] += 1900;
1157                 }
1158
1159                 if ($dmy[2] < 1910) {
1160                     $dmy[2] += 100;
1161                 }
1162             }
1163             // Determine if MDY date format is used, preferring Date Display Format from
1164             // global settings if it's not YMD, otherwise guessing from country code.
1165             $using_mdy = empty($GLOBALS['date_display_format']) ?
1166                 ($GLOBALS['phone_country_code'] == 1) : ($GLOBALS['date_display_format'] == 1);
1167             if ($using_mdy) {
1168                 $fixed_date = sprintf("%04u-%02u-%02u", $dmy[2], $dmy[0], $dmy[1]);
1169             } else {
1170                 $fixed_date = sprintf("%04u-%02u-%02u", $dmy[2], $dmy[1], $dmy[0]);
1171             }
1172         }
1173     }
1174
1175     return $fixed_date;
1176 }
1177
1178 function pdValueOrNull($key, $value)
1179 {
1180     if (
1181         ($key == 'DOB' || $key == 'regdate' || $key == 'contrastart' ||
1182         substr($key, 0, 8) == 'userdate' || $key == 'deceased_date') &&
1183         (empty($value) || $value == '0000-00-00')
1184     ) {
1185         return "NULL";
1186     } else {
1187         return "'" . add_escape_custom($value) . "'";
1188     }
1189 }
1190
1191 /**
1192  * Create or update patient data from an array.
1193  *
1194  * This is a wrapper function for the PatientService which is now the single point
1195  * of patient creation and update.
1196  *
1197  * If successful, returns the pid of the patient
1198  *
1199  * @param $pid
1200  * @param $new
1201  * @param false $create
1202  * @return mixed
1203  */
1204 function updatePatientData($pid, $new, $create = false)
1205 {
1206     // Create instance of patient service
1207     $patientService = new PatientService();
1208     if (
1209         $create === true ||
1210         $pid === null
1211     ) {
1212         $result = $patientService->databaseInsert($new);
1213         updateDupScore($result['pid']);
1214     } else {
1215         $new['pid'] = $pid;
1216         $result = $patientService->databaseUpdate($new);
1217     }
1218
1219     // From the returned patient data array
1220     // retrieve the data and return the pid
1221     $pid = $result['pid'];
1222
1223     return $pid;
1224 }
1225
1226 function newEmployerData(
1227     $pid,
1228     $name = "",
1229     $street = "",
1230     $postal_code = "",
1231     $city = "",
1232     $state = "",
1233     $country = ""
1234 ) {
1235
1236     return sqlInsert("insert into employer_data set
1237         name='" . add_escape_custom($name) . "',
1238         street='" . add_escape_custom($street) . "',
1239         postal_code='" . add_escape_custom($postal_code) . "',
1240         city='" . add_escape_custom($city) . "',
1241         state='" . add_escape_custom($state) . "',
1242         country='" . add_escape_custom($country) . "',
1243         pid='" . add_escape_custom($pid) . "',
1244         date=NOW()
1245         ");
1246 }
1247
1248 // Create or update employer data from an array.
1249 //
1250 function updateEmployerData($pid, $new, $create = false)
1251 {
1252     // used to hard code colnames array('name','street','city','state','postal_code','country');
1253     // but now adapted for layout based
1254     $colnames = array();
1255     foreach ($new as $key => $value) {
1256         $colnames[] = $key;
1257     }
1258
1259     if ($create) {
1260         $set = "pid = '" . add_escape_custom($pid) . "', date = NOW()";
1261         foreach ($colnames as $key) {
1262             $value = isset($new[$key]) ? $new[$key] : '';
1263             $set .= ", `$key` = '" . add_escape_custom($value) . "'";
1264         }
1265
1266         return sqlInsert("INSERT INTO employer_data SET $set");
1267     } else {
1268         $set = '';
1269         $old = getEmployerData($pid);
1270         $modified = false;
1271         foreach ($colnames as $key) {
1272             $value = empty($old[$key]) ? '' : $old[$key];
1273             if (isset($new[$key]) && strcmp($new[$key], $value) != 0) {
1274                 $value = $new[$key];
1275                 $modified = true;
1276             }
1277
1278             $set .= "`$key` = '" . add_escape_custom($value) . "', ";
1279         }
1280
1281         if ($modified) {
1282             $set .= "pid = '" . add_escape_custom($pid) . "', date = NOW()";
1283             return sqlInsert("INSERT INTO employer_data SET $set");
1284         }
1285
1286         return ($old['id'] ?? '');
1287     }
1288 }
1289
1290 // This updates or adds the given insurance data info, while retaining any
1291 // previously added insurance_data rows that should be preserved.
1292 // This does not directly support the maintenance of non-current insurance.
1293 //
1294 function newInsuranceData(
1295     $pid,
1296     $type = "",
1297     $provider = "",
1298     $policy_number = "",
1299     $group_number = "",
1300     $plan_name = "",
1301     $subscriber_lname = "",
1302     $subscriber_mname = "",
1303     $subscriber_fname = "",
1304     $subscriber_relationship = "",
1305     $subscriber_ss = "",
1306     $subscriber_DOB = null,
1307     $subscriber_street = "",
1308     $subscriber_postal_code = "",
1309     $subscriber_city = "",
1310     $subscriber_state = "",
1311     $subscriber_country = "",
1312     $subscriber_phone = "",
1313     $subscriber_employer = "",
1314     $subscriber_employer_street = "",
1315     $subscriber_employer_city = "",
1316     $subscriber_employer_postal_code = "",
1317     $subscriber_employer_state = "",
1318     $subscriber_employer_country = "",
1319     $copay = "",
1320     $subscriber_sex = "",
1321     $effective_date = null,
1322     $accept_assignment = "TRUE",
1323     $policy_type = "",
1324     $effective_date_end = null
1325 ) {
1326
1327     if (strlen($type) <= 0) {
1328         return false;
1329     }
1330
1331     if (is_null($accept_assignment)) {
1332         $accept_assignment = "TRUE";
1333     }
1334     if (is_null($policy_type)) {
1335         $policy_type = "";
1336     }
1337
1338     // If empty dates were passed, then null.
1339     if (empty($effective_date)) {
1340         $effective_date = null;
1341     }
1342     if (empty($subscriber_DOB)) {
1343         $subscriber_DOB = null;
1344     }
1345     if (empty($effective_date_end)) {
1346         $effective_date_end = null;
1347     }
1348
1349     $idres = sqlStatement("SELECT * FROM insurance_data WHERE " .
1350     "pid = ? AND type = ? ORDER BY date DESC", array($pid,$type));
1351     $idrow = sqlFetchArray($idres);
1352
1353     // Replace the most recent entry in any of the following cases:
1354     // * Its effective date is >= this effective date.
1355     // * It is the first entry and it has no (insurance) provider.
1356     // * There is no encounter that is earlier than the new effective date but
1357     //   on or after the old effective date.
1358     // Otherwise insert a new entry.
1359
1360     $replace = false;
1361     if ($idrow) {
1362         // convert date from null to "0000-00-00" for below strcmp and query
1363         $temp_idrow_date = (!empty($idrow['date'])) ? $idrow['date'] : "0000-00-00";
1364         $temp_effective_date = (!empty($effective_date)) ? $effective_date : "0000-00-00";
1365         if (strcmp($temp_idrow_date, $temp_effective_date) > 0) {
1366             $replace = true;
1367         } else {
1368             if (!$idrow['provider'] && !sqlFetchArray($idres)) {
1369                 $replace = true;
1370             } else {
1371                 $ferow = sqlQuery("SELECT count(*) AS count FROM form_encounter " .
1372                 "WHERE pid = ? AND date < ? AND " .
1373                 "date >= ?", array($pid, $temp_effective_date . " 00:00:00", $temp_idrow_date . " 00:00:00"));
1374                 if ($ferow['count'] == 0) {
1375                     $replace = true;
1376                 }
1377             }
1378         }
1379     }
1380
1381     if ($replace) {
1382         // TBD: This is a bit dangerous in that a typo in entering the effective
1383         // date can wipe out previous insurance history.  So we want some data
1384         // entry validation somewhere.
1385         if ($effective_date === null) {
1386             sqlStatement("DELETE FROM insurance_data WHERE " .
1387                 "pid = ? AND type = ? AND " .
1388                 "id != ?", array($pid, $type, $idrow['id']));
1389         } else {
1390             sqlStatement("DELETE FROM insurance_data WHERE " .
1391                 "pid = ? AND type = ? AND date >= ? AND " .
1392                 "id != ?", array($pid, $type, $effective_date, $idrow['id']));
1393         }
1394
1395         $data = array();
1396         $data['type'] = $type;
1397         $data['provider'] = $provider;
1398         $data['policy_number'] = $policy_number;
1399         $data['group_number'] = $group_number;
1400         $data['plan_name'] = $plan_name;
1401         $data['subscriber_lname'] = $subscriber_lname;
1402         $data['subscriber_mname'] = $subscriber_mname;
1403         $data['subscriber_fname'] = $subscriber_fname;
1404         $data['subscriber_relationship'] = $subscriber_relationship;
1405         $data['subscriber_ss'] = $subscriber_ss;
1406         $data['subscriber_DOB'] = $subscriber_DOB;
1407         $data['subscriber_street'] = $subscriber_street;
1408         $data['subscriber_postal_code'] = $subscriber_postal_code;
1409         $data['subscriber_city'] = $subscriber_city;
1410         $data['subscriber_state'] = $subscriber_state;
1411         $data['subscriber_country'] = $subscriber_country;
1412         $data['subscriber_phone'] = $subscriber_phone;
1413         $data['subscriber_employer'] = $subscriber_employer;
1414         $data['subscriber_employer_city'] = $subscriber_employer_city;
1415         $data['subscriber_employer_street'] = $subscriber_employer_street;
1416         $data['subscriber_employer_postal_code'] = $subscriber_employer_postal_code;
1417         $data['subscriber_employer_state'] = $subscriber_employer_state;
1418         $data['subscriber_employer_country'] = $subscriber_employer_country;
1419         $data['copay'] = $copay;
1420         $data['subscriber_sex'] = $subscriber_sex;
1421         $data['pid'] = $pid;
1422         $data['date'] = $effective_date;
1423         $data['accept_assignment'] = $accept_assignment;
1424         $data['policy_type'] = $policy_type;
1425         $data['date_end'] = $effective_date_end;
1426         updateInsuranceData($idrow['id'], $data);
1427         return $idrow['id'];
1428     } else {
1429         return sqlInsert(
1430             "INSERT INTO `insurance_data` SET `type` = ?,
1431             `provider` = ?,
1432             `policy_number` = ?,
1433             `group_number` = ?,
1434             `plan_name` = ?,
1435             `subscriber_lname` = ?,
1436             `subscriber_mname` = ?,
1437             `subscriber_fname` = ?,
1438             `subscriber_relationship` = ?,
1439             `subscriber_ss` = ?,
1440             `subscriber_DOB` = ?,
1441             `subscriber_street` = ?,
1442             `subscriber_postal_code` = ?,
1443             `subscriber_city` = ?,
1444             `subscriber_state` = ?,
1445             `subscriber_country` = ?,
1446             `subscriber_phone` = ?,
1447             `subscriber_employer` = ?,
1448             `subscriber_employer_city` = ?,
1449             `subscriber_employer_street` = ?,
1450             `subscriber_employer_postal_code` = ?,
1451             `subscriber_employer_state` = ?,
1452             `subscriber_employer_country` = ?,
1453             `copay` = ?,
1454             `subscriber_sex` = ?,
1455             `pid` = ?,
1456             `date` = ?,
1457             `accept_assignment` = ?,
1458             `policy_type` = ?,
1459             `date_end` = ?",
1460             [
1461                 $type,
1462                 $provider,
1463                 $policy_number,
1464                 $group_number,
1465                 $plan_name,
1466                 $subscriber_lname,
1467                 $subscriber_mname,
1468                 $subscriber_fname,
1469                 $subscriber_relationship,
1470                 $subscriber_ss,
1471                 $subscriber_DOB,
1472                 $subscriber_street,
1473                 $subscriber_postal_code,
1474                 $subscriber_city,
1475                 $subscriber_state,
1476                 $subscriber_country,
1477                 $subscriber_phone,
1478                 $subscriber_employer,
1479                 $subscriber_employer_city,
1480                 $subscriber_employer_street,
1481                 $subscriber_employer_postal_code,
1482                 $subscriber_employer_state,
1483                 $subscriber_employer_country,
1484                 $copay,
1485                 $subscriber_sex,
1486                 $pid,
1487                 $effective_date,
1488                 $accept_assignment,
1489                 $policy_type,
1490                 $effective_date_end
1491             ]
1492         );
1493     }
1494 }
1495
1496 // This is used internally only.
1497 function updateInsuranceData($id, $new)
1498 {
1499     $fields = sqlListFields("insurance_data");
1500     $use = array();
1501
1502     foreach ($new as $key => $value) {
1503         if (in_array($key, $fields)) {
1504             $use[$key] = $value;
1505         }
1506     }
1507
1508     $sqlBindArray = [];
1509     $sql = "UPDATE insurance_data SET ";
1510     foreach ($use as $key => $value) {
1511         $sql .= "`" . $key . "` = ?, ";
1512         array_push($sqlBindArray, $value);
1513     }
1514
1515     $sql = substr($sql, 0, -2) . " WHERE id = ?";
1516     array_push($sqlBindArray, $id);
1517
1518     sqlStatement($sql, $sqlBindArray);
1519 }
1520
1521 function newHistoryData($pid, $new = false)
1522 {
1523     $socialHistoryService = new SocialHistoryService();
1524
1525     $insertionRecord = $new;
1526     if (!is_array(($insertionRecord))) {
1527         $insertionRecord = [
1528             'pid' => $pid
1529         ];
1530     }
1531     $socialHistoryService->create($insertionRecord);
1532 }
1533
1534 function updateHistoryData($pid, $new)
1535 {
1536     $socialHistoryService = new SocialHistoryService();
1537     return $socialHistoryService->updateHistoryDataForPatientPid($pid, $new);
1538 }
1539
1540 // Returns Age
1541 //   in months if < 2 years old
1542 //   in years  if > 2 years old
1543 // given YYYYMMDD from MySQL DATE_FORMAT(DOB,'%Y%m%d')
1544 // (optional) nowYMD is a date in YYYYMMDD format
1545 function getPatientAge($dobYMD, $nowYMD = null)
1546 {
1547     $patientService = new PatientService();
1548     return $patientService->getPatientAge($dobYMD, $nowYMD);
1549 }
1550
1551 /**
1552  * Wrapper to make sure the clinical rules dates formats corresponds to the
1553  * format expected by getPatientAgeYMD
1554  *
1555  * @param  string  $dob     date of birth
1556  * @param  string  $target  date to calculate age on
1557  * @return array containing
1558  *      age - decimal age in years
1559  *      age_in_months - decimal age in months
1560  *      ageinYMD - formatted string #y #m #d */
1561 function parseAgeInfo($dob, $target)
1562 {
1563     // Prepare dob (expected in order Y M D, remove whatever delimiters might be there
1564     $dateDOB = preg_replace("/[-\s\/]/", "", $dob);
1565     ;
1566     // Prepare target (Y-M-D H:M:S)
1567     $dateTarget = preg_replace("/[-\s\/]/", "", $target);
1568
1569     return getPatientAgeYMD($dateDOB, $dateTarget);
1570 }
1571
1572 /**
1573  *
1574  * @param type $dob
1575  * @param type $date
1576  * @return array containing
1577  *      age - decimal age in years
1578  *      age_in_months - decimal age in months
1579  *      ageinYMD - formatted string #y #m #d
1580  */
1581 function getPatientAgeYMD($dob, $date = null)
1582 {
1583     $service = new PatientService();
1584     return $service->getPatientAgeYMD($dob, $date);
1585 }
1586
1587 // Returns Age in days
1588 //   in months if < 2 years old
1589 //   in years  if > 2 years old
1590 // given YYYYMMDD from MySQL DATE_FORMAT(DOB,'%Y%m%d')
1591 // (optional) nowYMD is a date in YYYYMMDD format
1592 function getPatientAgeInDays($dobYMD, $nowYMD = null)
1593 {
1594     $age = -1;
1595
1596     // strip any dashes from the DOB
1597     $dobYMD = preg_replace("/-/", "", $dobYMD);
1598     $dobDay = substr($dobYMD, 6, 2);
1599     $dobMonth = substr($dobYMD, 4, 2);
1600     $dobYear = substr($dobYMD, 0, 4);
1601
1602     // set the 'now' date values
1603     if ($nowYMD == null) {
1604         $nowDay = date("d");
1605         $nowMonth = date("m");
1606         $nowYear = date("Y");
1607     } else {
1608         $nowDay = substr($nowYMD, 6, 2);
1609         $nowMonth = substr($nowYMD, 4, 2);
1610         $nowYear = substr($nowYMD, 0, 4);
1611     }
1612
1613     // do the date math
1614     $dobtime = strtotime($dobYear . "-" . $dobMonth . "-" . $dobDay);
1615     $nowtime = strtotime($nowYear . "-" . $nowMonth . "-" . $nowDay);
1616     $timediff = $nowtime - $dobtime;
1617     $age = $timediff / 86400; // 24 hours * 3600 seconds/hour  = 86400 seconds
1618
1619     return $age;
1620 }
1621 /**
1622  * Returns a string to be used to display a patient's age
1623  *
1624  * @param type $dobYMD
1625  * @param type $asOfYMD
1626  * @return string suitable for displaying patient's age based on preferences
1627  */
1628 function getPatientAgeDisplay($dobYMD, $asOfYMD = null)
1629 {
1630     $service = new PatientService();
1631     return $service->getPatientAgeDisplay($dobYMD, $asOfYMD);
1632 }
1633 function dateToDB($date)
1634 {
1635     $date = substr($date, 6, 4) . "-" . substr($date, 3, 2) . "-" . substr($date, 0, 2);
1636     return $date;
1637 }
1638
1639 /**
1640  * Get up to 3 insurances (primary, secondary, tertiary) that are effective
1641  * for the given patient on the given date.
1642  *
1643  * @param int     The PID of the patient.
1644  * @param string  Date in yyyy-mm-dd format.
1645  * @return array  Array of 0-3 insurance_data rows.
1646  */
1647 function getEffectiveInsurances($patient_id, $encdate)
1648 {
1649     $insarr = array();
1650     foreach (array('primary','secondary','tertiary') as $instype) {
1651         $tmp = sqlQuery(
1652             "SELECT * FROM insurance_data " .
1653             "WHERE pid = ? AND type = ? " .
1654             "AND (date <= ? OR date IS NULL) ORDER BY date DESC LIMIT 1",
1655             array($patient_id, $instype, $encdate)
1656         );
1657         if (empty($tmp['provider'])) {
1658             break;
1659         }
1660
1661         $insarr[] = $tmp;
1662     }
1663
1664     return $insarr;
1665 }
1666
1667 /**
1668  * Get all requisition insurance companies
1669  *
1670  *
1671  */
1672
1673 function getAllinsurances($pid)
1674 {
1675     $insarr = array();
1676     $sql = "SELECT a.type, a.provider, a.plan_name, a.policy_number, a.group_number,
1677            a.subscriber_lname, a.subscriber_fname, a.subscriber_relationship, a.subscriber_employer,
1678                    b.name, c.line1, c.line2, c.city, c.state, c.zip
1679            FROM `insurance_data` AS a
1680            RIGHT JOIN insurance_companies AS b
1681            ON a.provider = b.id
1682            RIGHT JOIN addresses AS c
1683            ON a.provider = c.foreign_id
1684            WHERE a.pid = ? ";
1685     $inco = sqlStatement($sql, array($pid));
1686
1687     while ($icl = sqlFetchArray($inco)) {
1688         $insarr[] = $icl;
1689     }
1690     return $insarr;
1691 }
1692
1693 /**
1694  * Get the patient's balance due. Normally this excludes amounts that are out
1695  * to insurance.  If you want to include what insurance owes, set the second
1696  * parameter to true.
1697  *
1698  * @param int     The PID of the patient.
1699  * @param boolean Indicates if amounts owed by insurance are to be included.
1700  * @param int     Optional encounter id. If value is passed, will fetch only bills from specified encounter.
1701  * @return number The balance.
1702  */
1703 function get_patient_balance($pid, $with_insurance = false, $eid = false)
1704 {
1705     $balance = 0;
1706     $bindarray = array($pid);
1707     $sqlstatement = "SELECT date, encounter, last_level_billed, " .
1708       "last_level_closed, stmt_count " .
1709       "FROM form_encounter WHERE pid = ?";
1710     if ($eid) {
1711         $sqlstatement .= " AND encounter = ?";
1712         array_push($bindarray, $eid);
1713     }
1714     $feres = sqlStatement($sqlstatement, $bindarray);
1715     while ($ferow = sqlFetchArray($feres)) {
1716         $encounter = $ferow['encounter'];
1717         $dos = substr($ferow['date'], 0, 10);
1718         $insarr = getEffectiveInsurances($pid, $dos);
1719         $inscount = count($insarr);
1720         if (!$with_insurance && $ferow['last_level_closed'] < $inscount && $ferow['stmt_count'] == 0) {
1721             // It's out to insurance so only the co-pay might be due.
1722             $brow = sqlQuery(
1723                 "SELECT SUM(fee) AS amount FROM billing WHERE " .
1724                 "pid = ? AND encounter = ? AND " .
1725                 "code_type = 'copay' AND activity = 1",
1726                 array($pid, $encounter)
1727             );
1728             $drow = sqlQuery(
1729                 "SELECT SUM(pay_amount) AS payments " .
1730                 "FROM ar_activity WHERE " .
1731                 "deleted IS NULL AND pid = ? AND encounter = ? AND payer_type = 0",
1732                 array($pid, $encounter)
1733             );
1734             // going to comment this out for now since computing future copays doesn't
1735             // equate to cash in hand, which shows in the Billing widget in dashboard 4-23-21
1736             // $copay = !empty($insarr[0]['copay']) ? $insarr[0]['copay'] * 1 : 0;
1737             $copay = 0;
1738
1739             $amt = !empty($brow['amount']) ? $brow['amount'] * 1 : 0;
1740             $pay = !empty($drow['payments']) ? $drow['payments'] * 1 : 0;
1741             $ptbal = $copay + $amt - $pay;
1742             if ($ptbal) { // @TODO check if we want to show patient payment credits.
1743                 $balance += $ptbal;
1744             }
1745         } else {
1746             // Including insurance or not out to insurance, everything is due.
1747             $brow = sqlQuery("SELECT SUM(fee) AS amount FROM billing WHERE " .
1748             "pid = ? AND encounter = ? AND " .
1749             "activity = 1", array($pid, $encounter));
1750             $drow = sqlQuery("SELECT SUM(pay_amount) AS payments, " .
1751               "SUM(adj_amount) AS adjustments FROM ar_activity WHERE " .
1752               "deleted IS NULL AND pid = ? AND encounter = ?", array($pid, $encounter));
1753             $srow = sqlQuery("SELECT SUM(fee) AS amount FROM drug_sales WHERE " .
1754               "pid = ? AND encounter = ?", array($pid, $encounter));
1755             $balance += $brow['amount'] + $srow['amount']
1756               - $drow['payments'] - $drow['adjustments'];
1757         }
1758     }
1759
1760     return sprintf('%01.2f', $balance);
1761 }
1762
1763 function get_patient_balance_excluding($pid, $excluded = -1)
1764 {
1765     // We join form_encounter here to make sure we only count amounts for
1766     // encounters that exist.  We've had some trouble before with encounters
1767     // that were deleted but leaving line items in the database.
1768     $brow = sqlQuery(
1769         "SELECT SUM(b.fee) AS amount " .
1770         "FROM billing AS b, form_encounter AS fe WHERE " .
1771         "b.pid = ? AND b.encounter != 0 AND b.encounter != ? AND b.activity = 1 AND " .
1772         "fe.pid = b.pid AND fe.encounter = b.encounter",
1773         array($pid, $excluded)
1774     );
1775     $srow = sqlQuery(
1776         "SELECT SUM(s.fee) AS amount " .
1777         "FROM drug_sales AS s, form_encounter AS fe WHERE " .
1778         "s.pid = ? AND s.encounter != 0 AND s.encounter != ? AND " .
1779         "fe.pid = s.pid AND fe.encounter = s.encounter",
1780         array($pid, $excluded)
1781     );
1782     $drow = sqlQuery(
1783         "SELECT SUM(a.pay_amount) AS payments, " .
1784         "SUM(a.adj_amount) AS adjustments " .
1785         "FROM ar_activity AS a, form_encounter AS fe WHERE " .
1786         "a.deleted IS NULL AND a.pid = ? AND a.encounter != 0 AND a.encounter != ? AND " .
1787         "fe.pid = a.pid AND fe.encounter = a.encounter",
1788         array($pid, $excluded)
1789     );
1790     return sprintf(
1791         '%01.2f',
1792         $brow['amount'] + $srow['amount'] - $drow['payments'] - $drow['adjustments']
1793     );
1794 }
1795
1796 // Function to check if patient is deceased.
1797 //  Param:
1798 //    $pid  - patient id
1799 //    $date - date checking if deceased (will default to current date if blank)
1800 //  Return:
1801 //    If deceased, then will return the number of
1802 //      days that patient has been deceased and the deceased date.
1803 //    If not deceased, then will return false.
1804 function is_patient_deceased($pid, $date = '')
1805 {
1806
1807   // Set date to current if not set
1808     $date = (!empty($date)) ? $date : date('Y-m-d H:i:s');
1809
1810   // Query for deceased status (if person is deceased gets days_deceased and date_deceased)
1811     $results = sqlQuery("SELECT DATEDIFF(?,`deceased_date`) AS `days_deceased`, `deceased_date` AS `date_deceased` " .
1812                       "FROM `patient_data` " .
1813                       "WHERE `pid` = ? AND " .
1814                       dateEmptySql('deceased_date', true, true) .
1815                       "AND `deceased_date` <= ?", array($date,$pid,$date));
1816
1817     if (empty($results)) {
1818         // Patient is alive, so return false
1819         return false;
1820     } else {
1821         // Patient is dead, so return the number of days patient has been deceased.
1822         //  Don't let it be zero days or else will confuse calls to this function.
1823         if ($results['days_deceased'] === 0) {
1824             $results['days_deceased'] = 1;
1825         }
1826
1827         return $results;
1828     }
1829 }
1830
1831 // This computes, sets and returns the dup score for the given patient.
1832 //
1833 function updateDupScore($pid)
1834 {
1835     $row = sqlQuery(
1836         "SELECT MAX(" . getDupScoreSQL() . ") AS dupscore " .
1837         "FROM patient_data AS p1, patient_data AS p2 WHERE " .
1838         "p1.pid = ? AND p2.pid < p1.pid",
1839         array($pid)
1840     );
1841     $dupscore = empty($row['dupscore']) ? 0 : $row['dupscore'];
1842     sqlStatement(
1843         "UPDATE patient_data SET dupscore = ? WHERE pid = ?",
1844         array($dupscore, $pid)
1845     );
1846     return $dupscore;
1847 }
1848
1849 function get_unallocated_payment_id($pid)
1850 {
1851     $query = "SELECT session_id " .
1852         "FROM ar_session " .
1853         "WHERE patient_id = ? AND " .
1854         "adjustment_code = 'pre_payment' AND closed = 0 ORDER BY check_date ASC LIMIT 1";
1855     $res = sqlQuery($query, array($pid));
1856     if ($res['session_id']) {
1857         return $res['session_id'];
1858     } else {
1859         return '';
1860     }
1861 }