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
15 namespace OpenEMR\Services
;
17 use MongoDB\Driver\Query
;
18 use OpenEMR\Common\Database\QueryUtils
;
19 use OpenEMR\Common\Uuid\UuidRegistry
;
20 use OpenEMR\Events\Services\ServiceDeleteEvent
;
21 use OpenEMR\Services\Search\DateSearchField
;
22 use OpenEMR\Services\Search\FhirSearchWhereClauseBuilder
;
23 use OpenEMR\Services\Search\TokenSearchField
;
24 use OpenEMR\Services\Search\TokenSearchValue
;
25 use OpenEMR\Validators\ProcessingResult
;
26 use Particle\Validator\Exception\InvalidValueException
;
27 use Particle\Validator\Validator
;
29 class AppointmentService
extends BaseService
31 const TABLE_NAME
= "openemr_postcalendar_events";
32 const PATIENT_TABLE
= "patient_data";
33 const PRACTITIONER_TABLE
= "users";
34 const FACILITY_TABLE
= "facility";
37 * @var EncounterService
39 private $encounterService;
44 private $patientService;
47 * Default constructor.
49 public function __construct()
51 parent
::__construct(self
::TABLE_NAME
);
52 UuidRegistry
::createMissingUuidsForTables([self
::TABLE_NAME
, self
::PATIENT_TABLE
, self
::PRACTITIONER_TABLE
,
53 self
::FACILITY_TABLE
]);
56 public function setEncounterService(EncounterService
$service)
58 $this->encounterService
= $service;
61 public function getEncounterService()
63 if (empty($this->encounterService
)) {
64 $this->encounterService
= new EncounterService();
66 return $this->encounterService
;
69 public function setPatientService(PatientService
$patientService)
71 $this->patientService
= $patientService;
74 public function getPatientService()
76 if (empty($this->patientService
)) {
77 $this->patientService
= new PatientService();
79 return $this->patientService
;
82 public function getUuidFields(): array
84 return ['puuid', 'pce_aid_uuid', 'pc_uuid', 'facility_uuid', 'billing_location_uuid' ];
87 public function validate($appointment)
89 $validator = new Validator();
91 $validator->required('pc_catid')->numeric();
92 $validator->required('pc_title')->lengthBetween(2, 150);
93 $validator->required('pc_duration')->numeric();
94 $validator->required('pc_hometext')->string();
95 $validator->required('pc_apptstatus')->string();
96 $validator->required('pc_eventDate')->datetime('Y-m-d');
97 $validator->required('pc_startTime')->length(5); // HH:MM is 5 chars
98 $validator->required('pc_facility')->numeric();
99 $validator->required('pc_billing_location')->numeric();
100 $validator->optional('pc_aid')->numeric()
101 ->callback(function ($value, $data) {
102 $id = QueryUtils
::fetchSingleValue('Select id FROM users WHERE id = ? ', 'id', [$value]);
104 throw new InvalidValueException('pc_aid must be for a valid user', 'pc_aid');
108 $validator->optional('pid')->callback(function ($value, $data) {
109 $id = QueryUtils
::fetchSingleValue('Select id FROM patient_data WHERE pid = ? ', 'id', [$value]);
111 throw new InvalidValueException('pid must be for a valid patient', 'pid');
116 return $validator->validate($appointment);
119 public function search($search, $isAndCondition = true)
121 $sql = "SELECT pce.pc_eid,
128 providers.uuid AS pce_aid_uuid,
129 providers.npi AS pce_aid_npi,
137 pce.pc_billing_location,
141 f1.name as facility_name,
142 f1_map.uuid as facility_uuid,
143 f2.name as billing_location_name,
144 f2_map.uuid as billing_location_uuid
148 uuid AS pc_uuid, -- we do this because our uuid registry requires the field to be named this way
161 openemr_postcalendar_events
163 LEFT JOIN facility as f1 ON pce.pc_facility = f1.id
164 LEFT JOIN uuid_mapping as f1_map ON f1_map.target_uuid=f1.uuid AND f1_map.resource='Location'
165 LEFT JOIN facility as f2 ON pce.pc_billing_location = f2.id
166 LEFT JOIN uuid_mapping as f2_map ON f2_map.target_uuid=f2.uuid AND f2_map.resource='Location'
175 ) pd ON pd.pid = pce.pc_pid
176 LEFT JOIN users as providers ON pce.pc_aid = providers.id";
178 $whereClause = FhirSearchWhereClauseBuilder
::build($search, $isAndCondition);
180 $sql .= $whereClause->getFragment();
181 $sqlBindArray = $whereClause->getBoundValues();
182 $statementResults = QueryUtils
::sqlStatementThrowException($sql, $sqlBindArray);
184 $processingResult = new ProcessingResult();
185 while ($row = sqlFetchArray($statementResults)) {
186 $processingResult->addData($this->createResultRecordFromDatabaseResult($row));
189 return $processingResult;
192 public function getAppointmentsForPatient($pid)
194 $sqlBindArray = array();
196 $sql = "SELECT pce.pc_eid,
203 providers.uuid AS pce_aid_uuid,
204 providers.npi AS pce_aid_npi,
212 pce.pc_billing_location,
215 f1.name as facility_name,
216 f1_map.uuid as facility_uuid,
217 f2.name as billing_location_name,
218 f2_map.uuid as billing_location_uuid
219 FROM openemr_postcalendar_events as pce
220 LEFT JOIN facility as f1 ON pce.pc_facility = f1.id
221 LEFT JOIN uuid_mapping as f1_map ON f1_map.target_uuid=f1.uuid AND f1_map.resource='Location'
222 LEFT JOIN facility as f2 ON pce.pc_billing_location = f2.id
223 LEFT JOIN uuid_mapping as f2_map ON f2_map.target_uuid=f2.uuid AND f2_map.resource='Location'
224 LEFT JOIN patient_data as pd ON pd.pid = pce.pc_pid
225 LEFT JOIN users as providers ON pce.pc_aid = providers.id";
228 $sql .= " WHERE pd.pid = ?";
229 array_push($sqlBindArray, $pid);
232 $records = QueryUtils
::fetchRecords($sql, $sqlBindArray);
234 if (!empty($records)) {
235 foreach ($records as $record) {
236 $finalRecords[] = $this->createResultRecordFromDatabaseResult($record);
239 return $finalRecords;
242 public function getAppointment($eid)
244 $sql = "SELECT pce.pc_eid,
251 providers.uuid AS pce_aid_uuid,
252 providers.npi AS pce_aid_npi,
261 pce.pc_billing_location,
266 f1.name as facility_name,
267 f1_map.uuid as facility_uuid,
268 f2.name as billing_location_name,
269 f2_map.uuid as billing_location_uuid
270 FROM openemr_postcalendar_events as pce
271 LEFT JOIN facility as f1 ON pce.pc_facility = f1.id
272 LEFT JOIN uuid_mapping as f1_map ON f1_map.target_uuid=f1.uuid AND f1_map.resource='Location'
273 LEFT JOIN facility as f2 ON pce.pc_billing_location = f2.id
274 LEFT JOIN uuid_mapping as f2_map ON f2_map.target_uuid=f2.uuid AND f2_map.resource='Location'
275 LEFT JOIN patient_data as pd ON pd.pid = pce.pc_pid
276 LEFT JOIN users as providers ON pce.pc_aid = providers.id
277 WHERE pce.pc_eid = ?";
279 $records = QueryUtils
::fetchRecords($sql, [$eid]);
281 if (!empty($records)) {
282 foreach ($records as $record) {
283 $finalRecords[] = $this->createResultRecordFromDatabaseResult($record);
286 return $finalRecords;
289 public function insert($pid, $data)
291 $startTime = date("H:i:s", strtotime($data['pc_startTime']));
292 // TODO: Why are we adding strings with numbers? How is this even working
293 $endTime = $startTime . $data['pc_duration'];
294 $uuid = (new UuidRegistry())->createUuid();
296 $sql = " INSERT INTO openemr_postcalendar_events SET";
298 $sql .= " pc_pid=?,";
299 $sql .= " pc_catid=?,";
300 $sql .= " pc_title=?,";
301 $sql .= " pc_duration=?,";
302 $sql .= " pc_hometext=?,";
303 $sql .= " pc_eventDate=?,";
304 $sql .= " pc_apptstatus=?,";
305 $sql .= " pc_startTime=?,";
306 $sql .= " pc_endTime=?,";
307 $sql .= " pc_facility=?,";
308 $sql .= " pc_billing_location=?,";
309 $sql .= " pc_informant=1,";
310 $sql .= " pc_eventstatus=1,";
311 $sql .= " pc_sharing=1,";
314 $results = sqlInsert(
321 $data["pc_duration"],
322 $data["pc_hometext"],
323 $data["pc_eventDate"],
324 $data['pc_apptstatus'],
327 $data["pc_facility"],
328 $data["pc_billing_location"],
329 $data["pc_aid"] ??
null
338 * @param $recurr_affect
339 * @param $event_selected_date
342 public function deleteAppointment($eid, $recurr_affect, $event_selected_date)
344 // =======================================
345 // multi providers event
346 // =======================================
347 if ($GLOBALS['select_multi_providers']) {
348 // what is multiple key around this $eid?
349 $row = sqlQuery("SELECT pc_multiple FROM openemr_postcalendar_events WHERE pc_eid = ?", array($eid));
351 // obtain current list of providers regarding the multiple key
352 $providers_current = array();
353 $up = sqlStatement("SELECT pc_aid FROM openemr_postcalendar_events WHERE pc_multiple=?", array($row['pc_multiple']));
354 while ($current = sqlFetchArray($up)) {
355 $providers_current[] = $current['pc_aid'];
358 // establish a WHERE clause
359 if ($row['pc_multiple']) {
360 $whereClause = "pc_multiple = ?";
361 $whereBind = $row['pc_multiple'];
363 $whereClause = "pc_eid = ?";
367 if ($recurr_affect == 'current') {
368 // update all existing event records to exclude the current date
369 foreach ($providers_current as $provider) {
370 // update the provider's original event
371 // get the original event's repeat specs
372 $origEvent = sqlQuery("SELECT pc_recurrspec FROM openemr_postcalendar_events " .
373 " WHERE pc_aid <=> ? AND pc_multiple=?", array($provider,$row['pc_multiple']));
374 $oldRecurrspec = unserialize($origEvent['pc_recurrspec'], ['allowed_classes' => false]);
375 $selected_date = date("Y-m-d", strtotime($event_selected_date));
376 if ($oldRecurrspec['exdate'] != "") {
377 $oldRecurrspec['exdate'] .= "," . $selected_date;
379 $oldRecurrspec['exdate'] .= $selected_date;
382 // mod original event recur specs to exclude this date
383 sqlStatement("UPDATE openemr_postcalendar_events SET " .
384 " pc_recurrspec = ? " .
385 " WHERE " . $whereClause, array(serialize($oldRecurrspec), $whereBind));
387 } elseif ($recurr_affect == 'future') {
388 // update all existing event records to stop recurring on this date-1
389 $selected_date = date("Y-m-d", (strtotime($event_selected_date) - 24 * 60 * 60));
390 foreach ($providers_current as $provider) {
391 // In case of a change in the middle of the event
392 if (strcmp($_POST['event_start_date'], $event_selected_date) != 0) {
393 // update the provider's original event
394 sqlStatement("UPDATE openemr_postcalendar_events SET " .
396 " WHERE " . $whereClause, array($selected_date), $whereBind);
397 } else { // In case of a change in the event head
398 // as we need to notify events that we are deleting this record we need to grab all of the pc_eid
399 // so we can process the events
400 $pc_eids = QueryUtils
::fetchTableColumn(
401 "SELECT pc_eid FROM openemr_postcalendar_events WHERE " . $whereClause,
405 foreach ($pc_eids as $pc_eid) {
406 $this->deleteAppointmentRecord($pc_eid);
411 // really delete the event from the database
412 // as we need to notify events that we are deleting this record we need to grab all of the pc_eid
413 // so we can process the events
414 $pc_eids = QueryUtils
::fetchTableColumn(
415 "SELECT pc_eid FROM openemr_postcalendar_events WHERE " . $whereClause,
419 foreach ($pc_eids as $pc_eid) {
420 $this->deleteAppointmentRecord($pc_eid);
423 } else { // single provider event
424 if ($recurr_affect == 'current') {
425 // mod original event recur specs to exclude this date
426 // get the original event's repeat specs
427 $origEvent = sqlQuery("SELECT pc_recurrspec FROM openemr_postcalendar_events WHERE pc_eid = ?", array($eid));
428 $oldRecurrspec = unserialize($origEvent['pc_recurrspec'], ['allowed_classes' => false]);
429 $selected_date = date("Ymd", strtotime($_POST['selected_date']));
430 if ($oldRecurrspec['exdate'] != "") {
431 $oldRecurrspec['exdate'] .= "," . $selected_date;
433 $oldRecurrspec['exdate'] .= $selected_date;
436 sqlStatement("UPDATE openemr_postcalendar_events SET " .
437 " pc_recurrspec = ? " .
438 " WHERE pc_eid = ?", array(serialize($oldRecurrspec),$eid));
439 } elseif ($recurr_affect == 'future') {
440 // mod original event to stop recurring on this date-1
441 $selected_date = date("Ymd", (strtotime($_POST['selected_date']) - 24 * 60 * 60));
442 sqlStatement("UPDATE openemr_postcalendar_events SET " .
444 " WHERE pc_eid = ?", array($selected_date,$eid));
446 // fully delete the event from the database
447 $this->deleteAppointmentRecord($eid);
452 public function deleteAppointmentRecord($eid)
454 $servicePreDeleteEvent = new ServiceDeleteEvent($this, $eid);
455 $this->getEventDispatcher()->dispatch($servicePreDeleteEvent, ServiceDeleteEvent
::EVENT_PRE_DELETE
);
456 QueryUtils
::sqlStatementThrowException("DELETE FROM openemr_postcalendar_events WHERE pc_eid = ?", $eid);
457 $servicePostDeleteEvent = new ServiceDeleteEvent($this, $eid);
458 $this->getEventDispatcher()->dispatch($servicePostDeleteEvent, ServiceDeleteEvent
::EVENT_POST_DELETE
);
462 * Returns a list of categories
465 public function getCalendarCategories()
467 $sql = "SELECT pc_catid, pc_constant_id, pc_catname, pc_cattype,aco_spec FROM openemr_postcalendar_categories "
468 . " WHERE pc_active = 1 ORDER BY pc_seq";
469 return QueryUtils
::fetchRecords($sql);
473 * check to see if a status code exist as a check in
477 public function isCheckInStatus($option)
479 $row = sqlQuery("SELECT toggle_setting_1 FROM list_options WHERE " .
480 "list_id = 'apptstat' AND option_id = ? AND activity = 1", array($option));
481 if (empty($row['toggle_setting_1'])) {
489 * check to see if a status code exist as a check out
493 public function isCheckOutStatus($option)
495 $row = sqlQuery("SELECT toggle_setting_2 FROM list_options WHERE " .
496 "list_id = 'apptstat' AND option_id = ? AND activity = 1", array($option));
497 if (empty($row['toggle_setting_2'])) {
504 public function isPendingStatus($option)
506 // TODO: @adunsulag is there ANY way to track this in the database of what statii are pending?
507 if ($option == '^') {
514 * Returns a list of appointment statuses (also used with encounters).
517 public function getAppointmentStatuses()
519 $listService = new ListService();
520 $options = $listService->getOptionsByListName('apptstat', ['activity' => 1]);
525 * Checks to see if the passed in status is a valid appointment status for calendar appointments.
526 * @param $status_option_id The status to check if its a valid appointment status
527 * @return bool True if its valid, false otherwise
529 public function isValidAppointmentStatus($status_option_id)
531 $listService = new ListService();
532 $option = $listService->getListOption('apptstat', $status_option_id);
533 if (!empty($option)) {
540 * Updates the status for an appointment. TODO: should be refactored at some point to update the entire record
541 * @param $eid number The id of the appointment event
542 * @param $status string The status the appointment event should be set to.
543 * @param $user number The user performing the update
544 * @param $encounter number The encounter of the appointment
546 public function updateAppointmentStatus($eid, $status, $user, $encounter = '')
548 $appt = $this->getAppointment($eid);
550 throw new \
InvalidArgumentException("Appointment does not exist for eid " . $eid);
552 // TODO: Not sure why getAppointment returns an array of records instead of a single record
556 $sql = "UPDATE " . self
::TABLE_NAME
. " SET pc_apptstatus = ? WHERE pc_eid = ? ";
557 $binds = [$status, $eid];
559 if (!empty($appt['pid'])) {
560 $trackerService = new PatientTrackerService();
561 $trackerService->manage_tracker_status($appt['pc_eventDate'], $appt['pc_startTime'], $eid, $appt['pid'], $user, $status, $appt['pc_room'], $encounter);
563 $this->getLogger()->error("AppointmentService->updateAppointmentStatus() failed to update manage_tracker_status"
564 . " as patient pid was empty", ['pc_eid' => $eid, 'status' => $status, 'user' => $user, 'encounter' => $encounter]);
566 return QueryUtils
::sqlStatementThrowException($sql, $binds);
572 * @return array The most recent encounter for a given appointment
574 public function getEncounterForAppointment($pc_eid, $pid)
576 $appointment = $this->getAppointment($pc_eid)[0];
577 $date = $appointment['pc_eventDate'];
578 // we grab the most recent encounter for today's date for the given patient
579 $encounterService = $this->getEncounterService();
580 $dateField = new DateSearchField('date', ['eq' . $date], DateSearchField
::DATE_TYPE_DATE
);
581 $pidField = new TokenSearchField('pid', [new TokenSearchValue($pid)]);
582 // returns the most recent encounter for the given appointment..
583 // TODO: @adunsulag we should look at in the future of making an actual join table between encounters and appointments...
584 // this fuzzy match by date seems like it will have major problems for both inpatient settings as well as any kind
585 // of emergency care (patient sees doctor, patient does telehealth visit during the night due to crisis situation).
586 $encounterResult = $encounterService->search(['date' => $dateField, 'pid' => $pidField], true, null, ['limit' => 1]);
587 if ($encounterResult->hasData()) {
588 $result = $encounterResult->getData();
589 return array_pop($result);
594 public function createEncounterForAppointment($eid)
596 $appointment = $this->getAppointment($eid)[0];
597 $patientService = $this->getPatientService();
598 $patientUuid = UuidRegistry
::uuidToString($patientService->getUuid($appointment['pid']));
600 $userService = new UserService();
601 $user = $userService->getUser($appointment['pc_aid']);
602 $authGroup = UserService
::getAuthGroupForUser($user['username']);
604 $pos_code = QueryUtils
::fetchSingleValue(
605 "SELECT pos_code FROM facility WHERE id = ?",
607 [$appointment['pc_facility']]
611 'pc_catid' => $appointment['pc_catid']
612 // TODO: where would we get this information if it wasn't defaulted to ambulatory? Should this be a globals setting?
613 // this is imitating the work from encounter_events.inc.php::todaysEncounterCheck
614 ,'class_code' => EncounterService
::DEFAULT_CLASS_CODE
615 ,'puuid' => $patientUuid
616 ,'pid' => $appointment['pid']
617 ,'provider_id' => $user['id']
618 ,'reason' => $appointment['pc_hometext'] ??
xl('Please indicate visit reason')
619 ,'facility_id' => $appointment['pc_facility']
620 ,'billing_facility' => $appointment['pc_billing_location']
621 ,'pos_code' => $pos_code
622 ,'user' => $user['username']
623 ,'group' => $authGroup
626 $encounterService = $this->getEncounterService();
627 $result = $encounterService->insertEncounter($patientUuid, $data);
628 if ($result->hasData()) {
629 $result = $result->getData();
630 return $result[0]['encounter'];
636 * Returns the calendar category record from a supplied category id
639 public function getOneCalendarCategory($cat_id)
641 $sql = "SELECT * FROM openemr_postcalendar_categories WHERE pc_catid = ?";
642 return QueryUtils
::fetchRecords($sql, [$cat_id]);