src/Services/AppointmentService.php

   1 <?php
   2
   3 /**
   4  * AppointmentService
   5  *
   6  * @package   OpenEMR
   7  * @link      http://www.open-emr.org
   8  * @author    Matthew Vita <matthewvita48@gmail.com>
   9  * @author    Brady Miller <brady.g.miller@gmail.com>
  10  * @copyright Copyright (c) 2018 Matthew Vita <matthewvita48@gmail.com>
  11  * @copyright Copyright (c) 2018 Brady Miller <brady.g.miller@gmail.com>
  12  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  13  */
  14
  15 namespace OpenEMR\Services;
  16
  17 use MongoDB\Driver\Query;
  18 use OpenEMR\Common\Database\QueryUtils;
  19 use OpenEMR\Common\Uuid\UuidRegistry;
  20 use OpenEMR\Services\Search\DateSearchField;
  21 use OpenEMR\Services\Search\FhirSearchWhereClauseBuilder;
  22 use OpenEMR\Services\Search\TokenSearchField;
  23 use OpenEMR\Services\Search\TokenSearchValue;
  24 use OpenEMR\Validators\ProcessingResult;
  25 use Particle\Validator\Exception\InvalidValueException;
  26 use Particle\Validator\Validator;
  27
  28 class AppointmentService extends BaseService
  29 {
  30     const TABLE_NAME = "openemr_postcalendar_events";
  31     const PATIENT_TABLE = "patient_data";
  32     const PRACTITIONER_TABLE = "users";
  33     const FACILITY_TABLE = "facility";
  34
  35     /**
  36      * @var EncounterService
  37      */
  38     private $encounterService;
  39
  40     /**
  41      * @var PatientService
  42      */
  43     private $patientService;
  44
  45   /**
  46    * Default constructor.
  47    */
  48     public function __construct()
  49     {
  50         parent::__construct(self::TABLE_NAME);
  51         UuidRegistry::createMissingUuidsForTables([self::TABLE_NAME, self::PATIENT_TABLE, self::PRACTITIONER_TABLE,
  52             self::FACILITY_TABLE]);
  53     }
  54
  55     public function setEncounterService(EncounterService $service)
  56     {
  57         $this->encounterService = $service;
  58     }
  59
  60     public function getEncounterService()
  61     {
  62         if (empty($this->encounterService)) {
  63             $this->encounterService = new EncounterService();
  64         }
  65         return $this->encounterService;
  66     }
  67
  68     public function setPatientService(PatientService $patientService)
  69     {
  70         $this->patientService = $patientService;
  71     }
  72
  73     public function getPatientService()
  74     {
  75         if (empty($this->patientService)) {
  76             $this->patientService = new PatientService();
  77         }
  78         return $this->patientService;
  79     }
  80
  81     public function getUuidFields(): array
  82     {
  83         return ['puuid', 'pce_aid_uuid', 'pc_uuid'];
  84     }
  85
  86     public function validate($appointment)
  87     {
  88         $validator = new Validator();
  89
  90         $validator->required('pc_catid')->numeric();
  91         $validator->required('pc_title')->lengthBetween(2, 150);
  92         $validator->required('pc_duration')->numeric();
  93         $validator->required('pc_hometext')->string();
  94         $validator->required('pc_apptstatus')->string();
  95         $validator->required('pc_eventDate')->datetime('Y-m-d');
  96         $validator->required('pc_startTime')->length(5); // HH:MM is 5 chars
  97         $validator->required('pc_facility')->numeric();
  98         $validator->required('pc_billing_location')->numeric();
  99         $validator->optional('pc_aid')->numeric()
 100             ->callback(function ($value, $data) {
 101                 $id = QueryUtils::fetchSingleValue('Select id FROM users WHERE id = ? ', 'id', [$value]);
 102                 if (empty($id)) {
 103                     throw new InvalidValueException('pc_aid must be for a valid user', 'pc_aid');
 104                 }
 105                 return true;
 106             });
 107         $validator->optional('pid')->callback(function ($value, $data) {
 108             $id = QueryUtils::fetchSingleValue('Select id FROM patient_data WHERE pid = ? ', 'id', [$value]);
 109             if (empty($id)) {
 110                 throw new InvalidValueException('pid must be for a valid patient', 'pid');
 111             }
 112             return true;
 113         });
 114
 115         return $validator->validate($appointment);
 116     }
 117
 118     public function search($search, $isAndCondition = true)
 119     {
 120         $sql = "SELECT pce.pc_eid,
 121                        pce.pc_uuid,
 122                        pd.puuid,
 123                        pd.fname,
 124                        pd.lname,
 125                        pd.DOB,
 126                        pd.pid,
 127                        providers.uuid AS pce_aid_uuid,
 128                        providers.npi AS pce_aid_npi,
 129                        pce.pc_aid,
 130                        pce.pc_apptstatus,
 131                        pce.pc_eventDate,
 132                        pce.pc_startTime,
 133                        pce.pc_endTime,
 134                        pce.pc_facility,
 135                        pce.pc_billing_location,
 136                        pce.pc_catid,
 137                        pce.pc_pid,
 138                        pce.pc_duration,
 139                        f1.name as facility_name,
 140                        f2.name as billing_location_name
 141                        FROM (
 142                              SELECT
 143                                pc_eid,
 144                                uuid AS pc_uuid, -- we do this because our uuid registry requires the field to be named this way
 145                                pc_aid,
 146                                pc_apptstatus,
 147                                pc_eventDate,
 148                                pc_startTime,
 149                                pc_duration,
 150                                pc_endTime,
 151                                pc_facility,
 152                                pc_billing_location,
 153                                pc_catid,
 154                                pc_pid
 155                             FROM
 156                                  openemr_postcalendar_events
 157                        ) pce
 158                        LEFT JOIN facility as f1 ON pce.pc_facility = f1.id
 159                        LEFT JOIN facility as f2 ON pce.pc_billing_location = f2.id
 160                        LEFT JOIN (
 161                            select uuid AS puuid
 162                            ,fname
 163                            ,lname
 164                            ,DOB
 165                            ,pid
 166                            FROM
 167                                 patient_data
 168                       ) pd ON pd.pid = pce.pc_pid
 169                        LEFT JOIN users as providers ON pce.pc_aid = providers.id";
 170
 171         $whereClause = FhirSearchWhereClauseBuilder::build($search, $isAndCondition);
 172
 173         $sql .= $whereClause->getFragment();
 174         $sqlBindArray = $whereClause->getBoundValues();
 175         $statementResults =  QueryUtils::sqlStatementThrowException($sql, $sqlBindArray);
 176
 177         $processingResult = new ProcessingResult();
 178         while ($row = sqlFetchArray($statementResults)) {
 179             $processingResult->addData($this->createResultRecordFromDatabaseResult($row));
 180         }
 181
 182         return $processingResult;
 183     }
 184
 185     public function getAppointmentsForPatient($pid)
 186     {
 187         $sqlBindArray = array();
 188
 189         $sql = "SELECT pce.pc_eid,
 190                        pce.uuid AS pc_uuid,
 191                        pd.fname,
 192                        pd.lname,
 193                        pd.DOB,
 194                        pd.pid,
 195                        pd.uuid AS puuid,
 196                        providers.uuid AS pce_aid_uuid,
 197                        providers.npi AS pce_aid_npi,
 198                        pce.pc_aid,
 199                        pce.pc_apptstatus,
 200                        pce.pc_eventDate,
 201                        pce.pc_startTime,
 202                        pce.pc_endTime,
 203                        pce.pc_facility,
 204                        pce.pc_billing_location,
 205                        pce.pc_catid,
 206                        pce.pc_pid,
 207                        f1.name as facility_name,
 208                        f2.name as billing_location_name
 209                        FROM openemr_postcalendar_events as pce
 210                        LEFT JOIN facility as f1 ON pce.pc_facility = f1.id
 211                        LEFT JOIN facility as f2 ON pce.pc_billing_location = f2.id
 212                        LEFT JOIN patient_data as pd ON pd.pid = pce.pc_pid
 213                        LEFT JOIN users as providers ON pce.pc_aid = providers.id";
 214
 215         if ($pid) {
 216             $sql .= " WHERE pd.pid = ?";
 217             array_push($sqlBindArray, $pid);
 218         }
 219
 220         $records = QueryUtils::fetchRecords($sql, $sqlBindArray);
 221         $finalRecords = [];
 222         if (!empty($records)) {
 223             foreach ($records as $record) {
 224                 $finalRecords[] = $this->createResultRecordFromDatabaseResult($record);
 225             }
 226         }
 227         return $finalRecords;
 228     }
 229
 230     public function getAppointment($eid)
 231     {
 232         $sql = "SELECT pce.pc_eid,
 233                        pce.uuid AS pc_uuid,
 234                        pd.fname,
 235                        pd.lname,
 236                        pd.DOB,
 237                        pd.pid,
 238                        pd.uuid AS puuid,
 239                        providers.uuid AS pce_aid_uuid,
 240                        providers.npi AS pce_aid_npi,
 241                        pce.pc_aid,
 242                        pce.pc_apptstatus,
 243                        pce.pc_eventDate,
 244                        pce.pc_startTime,
 245                        pce.pc_endTime,
 246                        pce.pc_duration,
 247                        pce.pc_facility,
 248                        pce.pc_billing_location,
 249                        pce.pc_catid,
 250                        pce.pc_room,
 251                        pce.pc_pid,
 252                        pce.pc_hometext,
 253                        f1.name as facility_name,
 254                        f2.name as billing_location_name
 255                        FROM openemr_postcalendar_events as pce
 256                        LEFT JOIN facility as f1 ON pce.pc_facility = f1.id
 257                        LEFT JOIN facility as f2 ON pce.pc_billing_location = f2.id
 258                        LEFT JOIN patient_data as pd ON pd.pid = pce.pc_pid
 259                        LEFT JOIN users as providers ON pce.pc_aid = providers.id
 260                        WHERE pce.pc_eid = ?";
 261
 262         $records = QueryUtils::fetchRecords($sql, [$eid]);
 263         $finalRecords = [];
 264         if (!empty($records)) {
 265             foreach ($records as $record) {
 266                 $finalRecords[] = $this->createResultRecordFromDatabaseResult($record);
 267             }
 268         }
 269         return $finalRecords;
 270     }
 271
 272     public function insert($pid, $data)
 273     {
 274         $startTime = date("H:i:s", strtotime($data['pc_startTime']));
 275         // TODO: Why are we adding strings with numbers?  How is this even working
 276         $endTime = $startTime . $data['pc_duration'];
 277         $uuid = (new UuidRegistry())->createUuid();
 278
 279         $sql  = " INSERT INTO openemr_postcalendar_events SET";
 280         $sql .= "     uuid=?,";
 281         $sql .= "     pc_pid=?,";
 282         $sql .= "     pc_catid=?,";
 283         $sql .= "     pc_title=?,";
 284         $sql .= "     pc_duration=?,";
 285         $sql .= "     pc_hometext=?,";
 286         $sql .= "     pc_eventDate=?,";
 287         $sql .= "     pc_apptstatus=?,";
 288         $sql .= "     pc_startTime=?,";
 289         $sql .= "     pc_endTime=?,";
 290         $sql .= "     pc_facility=?,";
 291         $sql .= "     pc_billing_location=?,";
 292         $sql .= "     pc_informant=1,";
 293         $sql .= "     pc_eventstatus=1,";
 294         $sql .= "     pc_sharing=1,";
 295         $sql .= "     pc_aid=?";
 296
 297         $results = sqlInsert(
 298             $sql,
 299             array(
 300                 $uuid,
 301                 $pid,
 302                 $data["pc_catid"],
 303                 $data["pc_title"],
 304                 $data["pc_duration"],
 305                 $data["pc_hometext"],
 306                 $data["pc_eventDate"],
 307                 $data['pc_apptstatus'],
 308                 $startTime,
 309                 $endTime,
 310                 $data["pc_facility"],
 311                 $data["pc_billing_location"],
 312                 $data["pc_aid"] ?? null
 313             )
 314         );
 315
 316         return $results;
 317     }
 318
 319     public function delete($eid)
 320     {
 321         QueryUtils::sqlStatementThrowException("DELETE FROM openemr_postcalendar_events WHERE pc_eid = ?", $eid);
 322         return ['message' => 'record deleted'];
 323     }
 324
 325     /**
 326      * Returns a list of categories
 327      * @return array
 328      */
 329     public function getCalendarCategories()
 330     {
 331         $sql = "SELECT pc_catid, pc_constant_id, pc_catname, pc_cattype,aco_spec FROM openemr_postcalendar_categories "
 332         . " WHERE pc_active = 1 ORDER BY pc_seq";
 333         return QueryUtils::fetchRecords($sql);
 334     }
 335
 336     /**
 337      * check to see if a status code exist as a check in
 338      * @param $option
 339      * @return bool
 340      */
 341     public function isCheckInStatus($option)
 342     {
 343         $row = sqlQuery("SELECT toggle_setting_1 FROM list_options WHERE " .
 344             "list_id = 'apptstat' AND option_id = ? AND activity = 1", array($option));
 345         if (empty($row['toggle_setting_1'])) {
 346             return(false);
 347         }
 348
 349         return(true);
 350     }
 351
 352     /**
 353      * check to see if a status code exist as a check out
 354      * @param $option
 355      * @return bool
 356      */
 357     public function isCheckOutStatus($option)
 358     {
 359         $row = sqlQuery("SELECT toggle_setting_2 FROM list_options WHERE " .
 360             "list_id = 'apptstat' AND option_id = ? AND activity = 1", array($option));
 361         if (empty($row['toggle_setting_2'])) {
 362             return(false);
 363         }
 364
 365         return(true);
 366     }
 367
 368     public function isPendingStatus($option)
 369     {
 370         // TODO: @adunsulag is there ANY way to track this in the database of what statii are pending?
 371         if ($option == '^') {
 372             return true;
 373         }
 374         return false;
 375     }
 376
 377     /**
 378      * Returns a list of appointment statuses (also used with encounters).
 379      * @return array
 380      */
 381     public function getAppointmentStatuses()
 382     {
 383         $listService = new ListService();
 384         $options = $listService->getOptionsByListName('apptstat', ['activity' => 1]);
 385         return $options;
 386     }
 387
 388     /**
 389      * Checks to see if the passed in status is a valid appointment status for calendar appointments.
 390      * @param $status_option_id The status to check if its a valid appointment status
 391      * @return bool True if its valid, false otherwise
 392      */
 393     public function isValidAppointmentStatus($status_option_id)
 394     {
 395         $listService = new ListService();
 396         $option = $listService->getListOption('apptstat', $status_option_id);
 397         if (!empty($option)) {
 398             return true;
 399         }
 400         return false;
 401     }
 402
 403     /**
 404      * Updates the status for an appointment.  TODO: should be refactored at some point to update the entire record
 405      * @param $eid number The id of the appointment event
 406      * @param $status string The status the appointment event should be set to.
 407      * @param $user number The user performing the update
 408      * @param $encounter number The encounter of the appointment
 409      */
 410     public function updateAppointmentStatus($eid, $status, $user, $encounter = '')
 411     {
 412         $appt = $this->getAppointment($eid);
 413         if (empty($appt)) {
 414             throw new \InvalidArgumentException("Appointment does not exist for eid " . $eid);
 415         } else {
 416             // TODO: Not sure why getAppointment returns an array of records instead of a single record
 417             $appt = $appt[0];
 418         }
 419
 420         $sql = "UPDATE " . self::TABLE_NAME . " SET pc_apptstatus = ? WHERE pc_eid = ? ";
 421         $binds = [$status, $eid];
 422
 423         if (!empty($appt['pid'])) {
 424             $trackerService = new PatientTrackerService();
 425             $trackerService->manage_tracker_status($appt['pc_eventDate'], $appt['pc_startTime'], $eid, $appt['pid'], $user, $status, $appt['pc_room'], $encounter);
 426         } else {
 427             $this->getLogger()->error("AppointmentService->updateAppointmentStatus() failed to update manage_tracker_status"
 428             . " as patient pid was empty", ['pc_eid' => $eid, 'status' => $status, 'user' => $user, 'encounter' => $encounter]);
 429         }
 430         return QueryUtils::sqlStatementThrowException($sql, $binds);
 431     }
 432
 433     /**
 434      * @param $eid
 435      * @param $pid
 436      * @return array The most recent encounter for a given appointment
 437      */
 438     public function getEncounterForAppointment($pc_eid, $pid)
 439     {
 440         $appointment = $this->getAppointment($pc_eid)[0];
 441         $date = $appointment['pc_eventDate'];
 442         // we grab the most recent encounter for today's date for the given patient
 443         $encounterService = $this->getEncounterService();
 444         $dateField = new DateSearchField('date', ['eq' . $date], DateSearchField::DATE_TYPE_DATE);
 445         $pidField = new TokenSearchField('pid', [new TokenSearchValue($pid)]);
 446         // returns the most recent encounter for the given appointment..
 447         // TODO: @adunsulag we should look at in the future of making an actual join table between encounters and appointments...
 448         // this fuzzy match by date seems like it will have major problems for both inpatient settings as well as any kind
 449         // of emergency care (patient sees doctor, patient does telehealth visit during the night due to crisis situation).
 450         $encounterResult = $encounterService->search(['date' => $dateField, 'pid' => $pidField], true, null, ['limit' => 1]);
 451         if ($encounterResult->hasData()) {
 452             $result = $encounterResult->getData();
 453             return array_pop($result);
 454         }
 455         return null;
 456     }
 457
 458     public function createEncounterForAppointment($eid)
 459     {
 460         $appointment = $this->getAppointment($eid)[0];
 461         $patientService = $this->getPatientService();
 462         $patientUuid = UuidRegistry::uuidToString($patientService->getUuid($appointment['pid']));
 463
 464         $userService = new UserService();
 465         $user = $userService->getUser($appointment['pc_aid']);
 466         $authGroup = UserService::getAuthGroupForUser($user['username']);
 467
 468         $pos_code = QueryUtils::fetchSingleValue(
 469             "SELECT pos_code FROM facility WHERE id = ?",
 470             'pos_code',
 471             [$appointment['pc_facility']]
 472         );
 473
 474         $data = [
 475             'pc_catid' => $appointment['pc_catid']
 476             // TODO: where would we get this information if it wasn't defaulted to ambulatory?  Should this be a globals setting?
 477             // this is imitating the work from encounter_events.inc.php::todaysEncounterCheck
 478             ,'class_code' => EncounterService::DEFAULT_CLASS_CODE
 479             ,'puuid' => $patientUuid
 480             ,'pid' => $appointment['pid']
 481             ,'provider_id' => $user['id']
 482             ,'reason' => $appointment['pc_hometext'] ?? xl('Please indicate visit reason')
 483             ,'facility_id' => $appointment['pc_facility']
 484             ,'billing_facility' => $appointment['pc_billing_location']
 485             ,'pos_code' => $pos_code
 486             ,'user' => $user['username']
 487             ,'group' => $authGroup
 488         ];
 489
 490         $encounterService = $this->getEncounterService();
 491         $result = $encounterService->insertEncounter($patientUuid, $data);
 492         if ($result->hasData()) {
 493             $result = $result->getData();
 494             return $result[0]['encounter'];
 495         }
 496         return null;
 497     }
 498
 499     /**
 500      * Returns the calendar category record from a supplied category id
 501      * @return array
 502      */
 503     public function getOneCalendarCategory($cat_id)
 504     {
 505         $sql = "SELECT * FROM openemr_postcalendar_categories WHERE pc_catid = ?";
 506         return QueryUtils::fetchRecords($sql, [$cat_id]);
 507     }
 508 }