4 * patient.inc.php includes functions for manipulating patient information.
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
19 use OpenEMR\Common\Uuid\UuidRegistry
;
20 use OpenEMR\Services\FacilityService
;
21 use OpenEMR\Services\PatientService
;
22 use OpenEMR\Services\SocialHistoryService
;
24 require_once(dirname(__FILE__
) . "/dupscore.inc.php");
26 global $facilityService;
27 $facilityService = new FacilityService();
29 // These are for sports team use:
30 $PLAYER_FITNESSES = array(
33 xl('Restricted Training'),
37 xl('International Duty')
39 $PLAYER_FITCOLORS = array('#6677ff', '#00cc00', '#ffff00', '#ff3333', '#ff8800', '#ffeecc', '#ffccaa');
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.
44 $policy_types = array(
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'),
58 * Get a patient's demographic data.
60 * @param int $pid The PID of the patient
61 * @param string $given an optional subsection of the patient's demographic
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.
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")
71 $sql = "select $given from patient_data where pid=? order by date DESC limit 0,1";
72 return sqlQuery($sql, array($pid));
75 function getInsuranceProvider($ins_id)
78 $sql = "select name from insurance_companies where id=?";
79 $row = sqlQuery($sql, array($ins_id));
80 return $row['name'] ??
'';
83 function getInsuranceProviders()
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'];
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";
102 $rez = sqlStatement($sql);
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] . ")";
114 function getInsuranceProvidersExtra()
116 $returnval = array();
117 // add a global and if for where to allow inactive inscompanies
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";
126 $rez = sqlStatement($sql);
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'];
133 case $GLOBALS['insurance_information'] = '1':
134 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ")";
136 case $GLOBALS['insurance_information'] = '2':
137 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['zip'] . ")";
139 case $GLOBALS['insurance_information'] = '3':
140 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['state'] . ")";
142 case $GLOBALS['insurance_information'] = '4':
143 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['state'] .
144 ", " . $row['zip'] . ")";
146 case $GLOBALS['insurance_information'] = '5':
147 $returnval[$row['id']] = $row['name'] . " (" . $row['line1'] . ", " . $row['line2'] . ", " . $row['city'] .
148 ", " . $row['state'] . ", " . $row['zip'] . ")";
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'] . ")";
154 case $GLOBALS['insurance_information'] = '7':
155 preg_match("/\d+/", $row['line1'], $matches);
156 $returnval[$row['id']] = $row['name'] . " (" . $row['zip'] .
157 "," . $matches[0] . ")";
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.
171 function getFacility($facid = 0)
173 global $facilityService;
178 return $facilityService->getById($facid);
181 if ($GLOBALS['login_into_facility']) {
182 //facility is saved in sessions
183 $facility = $facilityService->getById($_SESSION['facilityId']);
186 $facility = $facilityService->getPrimaryBillingLocation();
188 $facility = $facilityService->getFacilityForUser($_SESSION['authUserID']);
195 // Generate a report title including report name and facility name, address
198 function genFacilityTitle($repname = '', $facid = 0, $logo = "")
201 $s .= "<table class='ftitletable' width='100%'>\n";
204 $s .= " <td align='left' class='ftitlecell1'>" . text($repname) . "</td>\n";
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";
209 $s .= " <td align='right' class='ftitlecell2'>\n";
210 $r = getFacility($facid);
212 $s .= "<b>" . text($r['name'] ??
'') . "</b>\n";
213 if (!empty($r['street'])) {
214 $s .= "<br />" . text($r['street']) . "\n";
217 if (!empty($r['city']) ||
!empty($r['state']) ||
!empty($r['postal_code'])) {
220 $s .= text($r['city']);
228 $s .= text($r['state']);
231 if ($r['postal_code']) {
232 $s .= " " . text($r['postal_code']);
238 if (!empty($r['country_code'])) {
239 $s .= "<br />" . text($r['country_code']) . "\n";
242 if (preg_match('/[1-9]/', ($r['phone'] ??
''))) {
243 $s .= "<br />" . text($r['phone']) . "\n";
256 returns all facilities or just the id for the first one
257 (FACILITY FILTERING (lemonsoftware))
259 @param string - if 'first' return first facility ordered by id
260 @return array | int for 'first' case
262 function getFacilities($first = '')
264 global $facilityService;
266 $fres = $facilityService->getAllFacility();
268 if ($first == 'first') {
269 return $fres[0]['id'];
275 //(CHEMED) facility filter
276 function getProviderInfo($providerID = "%", $providers_only = true, $facility = '')
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 ";
285 //--------------------------------
286 //(CHEMED) facility filter
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))";
292 $param2 = " AND facility_id = '" . add_escape_custom($facility) . "' ";
296 //--------------------------------
299 if ($providerID == "%") {
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;
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']
318 $akeys = array_keys($returnval[0]);
319 foreach ($akeys as $key) {
320 $returnval[0][$key] = $returnval[0][$key];
324 return ($returnval ??
null);
327 function getProviderName($providerID, $provider_only = 'any')
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"];
335 if (!empty($pi[0]["suffix"]) && (strlen($pi[0]["suffix"]) > 0)) {
336 $pi[0]["lname"] .= ", " . $pi[0]["suffix"];
339 if (!empty($pi[0]["valedictory"]) && (strlen($pi[0]["valedictory"]) > 0)) {
340 $pi[0]["lname"] .= ", " . $pi[0]["valedictory"];
343 return $pi[0]['fname'] . " " . $pi[0]['lname'];
349 function getProviderId($providerName)
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;
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 = '')
366 if ($given == 'tobacco') {
367 $where = 'tobacco is not null and';
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));
377 $res = sqlQuery("select $given from history_data where $where pid = ? order by date DESC, id DESC limit 0,1", array($pid));
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")
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));
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(
400 $given = "insd.*, DATE_FORMAT(subscriber_DOB,'%m/%d/%Y') as subscriber_DOB, ic.name as provider_name"
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.
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));
416 function get_unallocated_patient_balance($pid)
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);
430 return sprintf('%01.2f', $unallocated);
433 function getInsuranceNameByDate(
437 $given = "ic.name as provider_name"
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";
449 $row = sqlQuery($sql, array($pid, $date, $date, $type));
450 return $row['provider_name'];
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 = "*")
457 $sql = "select $given from employer_data where pid = ? order by date DESC limit 0,1";
458 return sqlQuery($sql, array($pid));
461 // Generate a consistent header and footer, used for printed patient reports
462 function genPatientHeaderFooter($pid, $DOS = null)
464 $patient_dob = getPatientData($pid, "DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS");
465 $patient_name = getPatientName($pid);
468 $s = '<htmlpageheader name="PageHeader1"><div style="text-align: right; font-weight: bold;">';
469 $s .= text($patient_name) . ' DOB: ' . text($patient_dob['DOB_TS']);
471 $s .= ' DOS: ' . text($DOS);
473 $s .= '</div></htmlpageheader>';
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>';
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" />';
489 function _set_patient_inc_count($limit, $count, $where, $whereBindArray = array())
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'];
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
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")
523 $names = getPatientNameSplit($term);
525 foreach ($names as $key => $val) {
527 if ((strlen($val) > 1) && ($names[$key][0] != strtoupper($names[$key][0]))) {
528 $names[$key] = '%' . $val . '%';
530 $names[$key] = $val . '%';
535 // Debugging section below
536 //if(array_key_exists('first',$names)) {
537 // error_log("first name search term :".$names['first']);
539 //if(array_key_exists('middle',$names)) {
540 // error_log("middle name search term :".$names['middle']);
542 //if(array_key_exists('last',$names)) {
543 // error_log("last name search term :".$names['last']);
545 // Debugging section above
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']);
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']);
569 $where = "lname LIKE ? AND fname LIKE ? ";
570 array_push($sqlBindArray, $names['last'], $names['first']);
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"]);
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);
587 $rez = sqlStatement($sql, $sqlBindArray);
589 $returnval = array();
590 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
591 $returnval[$iter] = $row;
594 if (is_countable($returnval)) {
595 _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
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
608 function getPatientNameSplit($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]));
617 $n['first'] = $names[1];
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];
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);
635 if (empty($n['last'])) {
640 // Trim whitespace off the names before returning
641 foreach ($n as $key => $val) {
642 $n[$key] = trim($val);
645 return $n; // associative array containing names
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")
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"]);
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);
670 $rez = sqlStatement($sql, $sqlBindArray);
671 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
672 $returnval[$iter] = $row;
675 if (is_countable($returnval)) {
676 _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
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")
685 $layoutCols = sqlStatement(
686 "SELECT field_id FROM layout_options WHERE form_id = 'DEM' AND field_id not like ? AND uor != 0",
690 $sqlBindArray = array();
692 for ($iter = 0; $row = sqlFetchArray($layoutCols); $iter++
) {
697 $where .= " " . add_escape_custom($row["field_id"]) . " like ? ";
698 array_push($sqlBindArray, "%" . $searchTerm . "%");
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);
706 $rez = sqlStatement($sql, $sqlBindArray);
707 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
708 $returnval[$iter] = $row;
711 if (is_countable($returnval)) {
712 _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
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(
722 $given = "pid, id, lname, fname, mname, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS",
723 $orderby = "lname ASC, fname ASC",
726 $search_service_code = ''
729 $layoutCols = explode('~', $searchFields);
730 $sqlBindArray = array();
733 foreach ($layoutCols as $val) {
743 $where .= " " . escape_sql_column_name($val, ['patient_data']) . " = ? ";
744 array_push($sqlBindArray, $searchTerm);
746 $where .= " " . escape_sql_column_name($val, ['patient_data']) . " like ? ";
747 array_push($sqlBindArray, $searchTerm . "%");
753 // If no search terms, ensure valid syntax.
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 " .
769 array_push($sqlBindArray, $search_service_code);
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);
777 $rez = sqlStatement($sql, $sqlBindArray);
778 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
779 $returnval[$iter] = $row;
782 if (is_countable($returnval)) {
783 _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
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)
796 $given = "pid, id, lname, fname, mname, suffix, providerID, DATE_FORMAT(DOB,'%m/%d/%Y') as DOB_TS";
797 $orderby = "lname ASC, fname ASC";
801 // alter default values if defined in the passed in args
802 if (isset($args['pid'])) {
806 if (isset($args['given'])) {
807 $given = $args['given'];
810 if (isset($args['orderby'])) {
811 $orderby = $args['orderby'];
814 if (isset($args['limit'])) {
815 $limit = $args['limit'];
818 if (isset($args['start'])) {
819 $start = $args['start'];
825 } elseif (empty($pid)) {
829 if (strstr($pid, "%")) {
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);
838 $rez = sqlStatement($sql);
839 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
840 $returnval[$iter] = $row;
846 /* return a patient's name in the format LAST [SUFFIX], FIRST [MIDDLE] */
847 function getPatientName($pid)
853 $patientData = getPatientPID(array("pid" => $pid));
854 if (empty($patientData[0]['lname'])) {
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'];
866 * Get a patient's first name, middle name, last name and suffix if applicable.
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.
872 * @var $pid int The Patient ID
873 * @returns string The Full Name
875 function getPatientFullNameAsString($pid): string
880 $ptData = getPatientPID(["pid" => $pid]);
883 if (empty($pt['lname'])) {
887 $name = $pt['fname'];
890 $name .= " {$pt['mname']}";
893 $name .= " {$pt['lname']}";
896 $name .= " {$pt['suffix']}";
902 /* return a patient's name in the format FIRST LAST */
903 function getPatientNameFirstLast($pid)
909 $patientData = getPatientPID(array("pid" => $pid));
910 if (empty($patientData[0]['lname'])) {
914 $patientName = $patientData[0]['fname'] . " " . $patientData[0]['lname'];
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")
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"]);
935 $sql = "SELECT $given FROM patient_data WHERE $where ORDER BY $orderby";
937 if ($limit != "all") {
938 $sql .= " limit " . escape_limit($start) . ", " . escape_limit($limit);
941 $rez = sqlStatement($sql, $sqlBindArray);
942 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
943 $returnval[$iter] = $row;
946 if (is_countable($returnval)) {
947 _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
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")
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);
965 $rez = sqlStatement($sql, $sqlBindArray);
966 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
967 $returnval[$iter] = $row;
970 if (is_countable($returnval)) {
971 _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
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")
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);
990 $rez = sqlStatement($sql, $sqlBindArray);
991 for ($iter = 0; $row = sqlFetchArray($rez); $iter++
) {
992 $returnval[$iter] = $row;
995 if (is_countable($returnval)) {
996 _set_patient_inc_count($limit, count($returnval), $where, $sqlBindArray);
1001 //----------------------input functions
1002 function newPatientData(
1019 $phone_contact = "",
1021 $contact_relationship = "",
1028 $migrantseasonal = "",
1030 $monthly_income = "",
1032 $financial_review = "",
1034 $pid = "MAX(pid)+1",
1046 $drivers_license = "",
1048 $hipaa_message = "",
1053 $referral_source = '';
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) . "'";
1063 $fitness = $rez['fitness'];
1064 $referral_source = $rez['referral_source'];
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'];
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) . "',
1125 $id = sqlInsert($query);
1128 // find the last inserted id for new patient case
1132 $foo = sqlQuery("select `pid`, `uuid` from `patient_data` where `id` = ? order by `date` limit 0,1", array($id));
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]);
1143 // Supported input date formats are:
1145 // mm/dd/yy (assumes 20yy for yy < 10, else 19yy)
1147 // also mm-dd-yyyy, etc. and mm.dd.yyyy, etc.
1149 function fixDate($date, $default = "0000-00-00")
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);
1156 $fixed_date = sprintf("%04u-%02u-%02u", $dmy[0], $dmy[1], $dmy[2]);
1158 if ($dmy[0] != 0 ||
$dmy[1] != 0 ||
$dmy[2] != 0) {
1159 if ($dmy[2] < 1000) {
1163 if ($dmy[2] < 1910) {
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);
1172 $fixed_date = sprintf("%04u-%02u-%02u", $dmy[2], $dmy[0], $dmy[1]);
1174 $fixed_date = sprintf("%04u-%02u-%02u", $dmy[2], $dmy[1], $dmy[0]);
1182 function pdValueOrNull($key, $value)
1185 ($key == 'DOB' ||
$key == 'regdate' ||
$key == 'contrastart' ||
1186 substr($key, 0, 8) == 'userdate' ||
$key == 'deceased_date') &&
1187 (empty($value) ||
$value == '0000-00-00')
1191 return "'" . add_escape_custom($value) . "'";
1196 * Create or update patient data from an array.
1198 * This is a wrapper function for the PatientService which is now the single point
1199 * of patient creation and update.
1201 * If successful, returns the pid of the patient
1205 * @param false $create
1208 function updatePatientData($pid, $new, $create = false)
1210 // Create instance of patient service
1211 $patientService = new PatientService();
1216 $result = $patientService->databaseInsert($new);
1217 updateDupScore($result['pid']);
1220 $result = $patientService->databaseUpdate($new);
1223 // From the returned patient data array
1224 // retrieve the data and return the pid
1225 $pid = $result['pid'];
1230 function newEmployerData(
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) . "',
1252 // Create or update employer data from an array.
1254 function updateEmployerData($pid, $new, $create = false)
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) {
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) . "'";
1270 return sqlInsert("INSERT INTO employer_data SET $set");
1273 $old = getEmployerData($pid);
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];
1282 $set .= "`$key` = '" . add_escape_custom($value) . "', ";
1286 $set .= "pid = '" . add_escape_custom($pid) . "', date = NOW()";
1287 return sqlInsert("INSERT INTO employer_data SET $set");
1290 return ($old['id'] ??
'');
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.
1298 function newInsuranceData(
1302 $policy_number = "",
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 = "",
1324 $subscriber_sex = "",
1325 $effective_date = null,
1326 $accept_assignment = "TRUE",
1328 $effective_date_end = null
1331 if (strlen($type) <= 0) {
1335 if (is_null($accept_assignment)) {
1336 $accept_assignment = "TRUE";
1338 if (is_null($policy_type)) {
1342 // If empty dates were passed, then null.
1343 if (empty($effective_date)) {
1344 $effective_date = null;
1346 if (empty($subscriber_DOB)) {
1347 $subscriber_DOB = null;
1349 if (empty($effective_date_end)) {
1350 $effective_date_end = null;
1353 $idres = sqlStatement("SELECT * FROM insurance_data WHERE " .
1354 "pid = ? AND type = ? ORDER BY date DESC", array($pid,$type));
1355 $idrow = sqlFetchArray($idres);
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.
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) {
1372 if (!$idrow['provider'] && !sqlFetchArray($idres)) {
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) {
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']));
1394 sqlStatement("DELETE FROM insurance_data WHERE " .
1395 "pid = ? AND type = ? AND date >= ? AND " .
1396 "id != ?", array($pid, $type, $effective_date, $idrow['id']));
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'];
1434 "INSERT INTO `insurance_data` SET `type` = ?,
1436 `policy_number` = ?,
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` = ?,
1458 `subscriber_sex` = ?,
1461 `accept_assignment` = ?,
1473 $subscriber_relationship,
1477 $subscriber_postal_code,
1480 $subscriber_country,
1482 $subscriber_employer,
1483 $subscriber_employer_city,
1484 $subscriber_employer_street,
1485 $subscriber_employer_postal_code,
1486 $subscriber_employer_state,
1487 $subscriber_employer_country,
1500 // This is used internally only.
1501 function updateInsuranceData($id, $new)
1503 $fields = sqlListFields("insurance_data");
1506 foreach ($new as $key => $value) {
1507 if (in_array($key, $fields)) {
1508 $use[$key] = $value;
1513 $sql = "UPDATE insurance_data SET ";
1514 foreach ($use as $key => $value) {
1515 $sql .= "`" . $key . "` = ?, ";
1516 array_push($sqlBindArray, $value);
1519 $sql = substr($sql, 0, -2) . " WHERE id = ?";
1520 array_push($sqlBindArray, $id);
1522 sqlStatement($sql, $sqlBindArray);
1525 function newHistoryData($pid, $new = false)
1527 $socialHistoryService = new SocialHistoryService();
1529 $insertionRecord = $new;
1530 if (!is_array(($insertionRecord))) {
1531 $insertionRecord = [
1535 $socialHistoryService->create($insertionRecord);
1538 function updateHistoryData($pid, $new)
1540 $socialHistoryService = new SocialHistoryService();
1541 return $socialHistoryService->updateHistoryDataForPatientPid($pid, $new);
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)
1551 $patientService = new PatientService();
1552 return $patientService->getPatientAge($dobYMD, $nowYMD);
1556 * Wrapper to make sure the clinical rules dates formats corresponds to the
1557 * format expected by getPatientAgeYMD
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)
1567 // Prepare dob (expected in order Y M D, remove whatever delimiters might be there
1568 $dateDOB = preg_replace("/[-\s\/]/", "", $dob);
1570 // Prepare target (Y-M-D H:M:S)
1571 $dateTarget = preg_replace("/[-\s\/]/", "", $target);
1573 return getPatientAgeYMD($dateDOB, $dateTarget);
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
1585 function getPatientAgeYMD($dob, $date = null)
1587 $service = new PatientService();
1588 return $service->getPatientAgeYMD($dob, $date);
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)
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);
1606 // set the 'now' date values
1607 if ($nowYMD == null) {
1608 $nowDay = date("d");
1609 $nowMonth = date("m");
1610 $nowYear = date("Y");
1612 $nowDay = substr($nowYMD, 6, 2);
1613 $nowMonth = substr($nowYMD, 4, 2);
1614 $nowYear = substr($nowYMD, 0, 4);
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
1626 * Returns a string to be used to display a patient's age
1628 * @param type $dobYMD
1629 * @param type $asOfYMD
1630 * @return string suitable for displaying patient's age based on preferences
1632 function getPatientAgeDisplay($dobYMD, $asOfYMD = null)
1634 $service = new PatientService();
1635 return $service->getPatientAgeDisplay($dobYMD, $asOfYMD);
1637 function dateToDB($date)
1639 $date = substr($date, 6, 4) . "-" . substr($date, 3, 2) . "-" . substr($date, 0, 2);
1644 * Get up to 3 insurances (primary, secondary, tertiary) that are effective
1645 * for the given patient on the given date.
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.
1651 function getEffectiveInsurances($patient_id, $encdate)
1654 foreach (array('primary','secondary','tertiary') as $instype) {
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)
1661 if (empty($tmp['provider'])) {
1672 * Get all requisition insurance companies
1677 function getAllinsurances($pid)
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
1689 $inco = sqlStatement($sql, array($pid));
1691 while ($icl = sqlFetchArray($inco)) {
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.
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.
1707 function get_patient_balance($pid, $with_insurance = false, $eid = false)
1710 $bindarray = array($pid);
1711 $sqlstatement = "SELECT date, encounter, last_level_billed, " .
1712 "last_level_closed, stmt_count " .
1713 "FROM form_encounter WHERE pid = ?";
1715 $sqlstatement .= " AND encounter = ?";
1716 array_push($bindarray, $eid);
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.
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)
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)
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;
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.
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'];
1764 return sprintf('%01.2f', $balance);
1767 function get_patient_balance_excluding($pid, $excluded = -1)
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.
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)
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)
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)
1796 $brow['amount'] +
$srow['amount'] - $drow['payments'] - $drow['adjustments']
1800 // Function to check if patient is deceased.
1802 // $pid - patient id
1803 // $date - date checking if deceased (will default to current date if blank)
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 = '')
1811 // Set date to current if not set
1812 $date = (!empty($date)) ?
$date : date('Y-m-d H:i:s');
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));
1821 if (empty($results)) {
1822 // Patient is alive, so return false
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;
1835 // This computes, sets and returns the dup score for the given patient.
1837 function updateDupScore($pid)
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",
1845 $dupscore = empty($row['dupscore']) ?
0 : $row['dupscore'];
1847 "UPDATE patient_data SET dupscore = ? WHERE pid = ?",
1848 array($dupscore, $pid)
1853 function get_unallocated_payment_id($pid)
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'];