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