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