?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
.inc files migration to .inc.php (#5897)
[openemr.git] / interface / eRxStore.php
blob3c9cba69bf51d68f31ee3e01ad449d979cc98217
1 <?php
2
3 /**
4 * interface/eRxStore.php Functions for interacting with NewCrop database.
5 *
6 * @package OpenEMR
7 * @link http://www.open-emr.org
8 * @author Sam Likins <sam.likins@wsi-services.com>
9 * @author Ken Chapple <ken@mi-squared.com>
10 * @copyright Copyright (c) 2013-2015 Sam Likins <sam.likins@wsi-services.com>
11 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
12 */
13
14 require_once(__DIR__ . "/../library/api.inc.php");
15
16 class eRxStore
17 {
18 /**
19 * Strip away any non numerical characters
20 * @param string $value Value to sanitize
21 * @return string Value sanitized of all non numerical characters
22 */
23 public static function sanitizeNumber($value)
24 {
25 return preg_replace('/[^-0-9.]/', '', $value);
26 }
27
28 /**
29 * Return the primary business entity
30 * @return array Primary business entity
31 */
32 public function getFacilityPrimary()
33 {
34 $return = sqlQuery('SELECT `name`, `federal_ein`, `street`, `city`, `state`, `postal_code`, `country_code`, `phone`, `fax`
35 FROM `facility`
36 WHERE `primary_business_entity` = \'1\';');
37
38 return $return;
39 }
40
41 /**
42 * Return the Federal EIN established with the primary business entity
43 * @return string Federal EIN for the primary business entity
44 */
45 public function selectFederalEin()
46 {
47 $return = $this->getFacilityPrimary();
48
49 return $return['federal_ein'];
50 }
51
52 /**
53 * Return user information using user Id
54 * @param integer $id Id of user to return
55 * @return array Specified user information: index [id, username, lname, fname, mname, title, license, federaldrugid, upin, state_license_number, npi, newcrop_user_role]
56 */
57 public function getUserById($id)
58 {
59 return sqlQuery(
60 'SELECT id, username, lname, fname, mname, title, federaldrugid, upin, state_license_number, npi, newcrop_user_role
61 FROM users
62 WHERE id = ?;',
63 array($id)
64 );
65 }
66
67 /**
68 * Return user facility business entity
69 * @param integer $id Id of user to return
70 * @return array User facility business entity
71 */
72 public function getUserFacility($id)
73 {
74 return sqlQuery(
75 'SELECT facility.id, facility.name, facility.street, facility.city, facility.state, facility.postal_code, facility.country_code, facility.phone, facility.fax
76 FROM users
77 LEFT JOIN facility ON facility.id = users.facility_id
78 WHERE users.id = ?;',
79 array($id)
80 );
81 }
82
83 /**
84 * Return patient information using patient Id
85 * @param integer $patientId Id of patient
86 * @return array Specified patient information: index [pid, fname, mname, lname, street, city, state, postal_code, country_code, phone_home, date_of_birth, sex]
87 */
88 public function getPatientByPatientId($patientId)
89 {
90 return sqlQuery(
91 'SELECT pid, fname, mname, lname, street, city, state, postal_code, country_code, phone_home, DATE_FORMAT(DOB,\'%Y%m%d\') AS date_of_birth, sex
92 FROM patient_data
93 WHERE pid = ?;',
94 array($patientId)
95 );
96 }
97
98 public function getPatientVitalsByPatientId($patientId)
99 {
100 $result = sqlQuery(
101 "SELECT FORM_VITALS.date, FORM_VITALS.id
102 FROM form_vitals AS FORM_VITALS LEFT JOIN forms AS FORMS ON FORM_VITALS.id = FORMS.form_id
103 WHERE FORM_VITALS.pid=? AND FORMS.deleted != '1'
104 ORDER BY FORM_VITALS.date DESC",
105 array($patientId)
106 );
107
108 $data = formFetch("form_vitals", $result['id']);
109
110 $weight = number_format($data['weight'] * 0.45359237, 2);
111 $height = number_format(round($data['height'] * 2.54, 1), 2);
112
113 return [
114 'height' => $height,
115 'height_units' => 'cm',
116 'weight' => $weight,
117 'weight_units' => 'kg'
118 ];
119 }
120
121 public function getPatientHealthplansByPatientId($patientId)
122 {
123 return sqlStatement(
124 'SELECT `ins`.`name`
125 FROM (
126 SELECT
127 `id`.`type`,
128 `ic`.`name`
129 FROM `insurance_data` AS `id`
130 LEFT JOIN `insurance_companies` AS `ic` ON `ic`.`id` = `id`.`provider`
131 WHERE `id`.`pid` = ?
132 AND `id`.`subscriber_relationship` = \'self\'
133 AND `id`.`provider` > 0
134 ORDER BY `id`.`date` DESC
135 ) AS `ins`
136 GROUP BY `ins`.`type`;',
137 array($patientId)
138 );
139 }
140
141 public function getPatientAllergiesByPatientId($patientId)
142 {
143 return sqlStatement(
144 'SELECT id, lists.title as title1, list_options.title as title2, comments
145 FROM lists
146 LEFT JOIN list_options ON lists.outcome = list_options.option_id
147 AND list_options.list_id = \'outcome\'
148 WHERE `type` = \'allergy\'
149 AND pid = ?
150 AND erx_source = \'0\'
151 AND erx_uploaded = \'0\'
152 AND (
153 enddate is NULL
154 OR enddate = \'0000-00-00\'
155 );',
156 array($patientId)
157 );
158 }
159
160 public function getPatientDiagnosisByPatientId($patientId)
161 {
162 return sqlStatement(
163 'SELECT diagnosis, begdate, title, date
164 FROM lists
165 WHERE `type` = \'medical_problem\'
166 AND pid = ?
167 ;',
168 array($patientId)
169 );
170 }
171
172 /**
173 * Return TTL timestamp for provided patient Id and process
174 * @param string $process SOAP process to check
175 * @param integer $patientId Patient Id to check
176 * @return string|boolean TTL timestamp of last SOAP call for provided patient Id and process
177 */
178 public function getLastSOAP($process, $patientId)
179 {
180 $return = sqlQuery(
181 'SELECT updated
182 FROM erx_ttl_touch
183 WHERE patient_id = ?
184 AND process = ?;',
185 array(
186 $patientId,
187 $process
188 )
189 );
190 if ($return === false) {
191 return false;
192 }
193
194 return $return['updated'];
195 }
196
197 /**
198 * Set TTL timestamp for provided patient Id and process
199 * @param string $process SOAP process to update
200 * @param integer $patientId Patient Id to update
201 */
202 public function setLastSOAP($process, $patientId)
203 {
204 sqlQuery(
205 'REPLACE INTO erx_ttl_touch
206 SET patient_id = ?,
207 process = ?,
208 updated = NOW();',
209 array(
210 $patientId,
211 $process
212 )
213 );
214 }
215
216 /**
217 * Update external sourced prescripts active status for provided patient Id
218 * @param integer $patientId Patient Id to update
219 * @param integer $active Active status to set for provided patient
220 */
221 public function updatePrescriptionsActiveByPatientId($patientId, $active = 0)
222 {
223 sqlQuery(
224 'UPDATE prescriptions
225 SET active = ?
226 WHERE patient_id = ?
227 AND erx_source=\'1\'',
228 array(
229 ($active == 1 ? 1 : 0),
230 $patientId
231 )
232 );
233 }
234
235 public function updatePrescriptionsUploadActiveByPatientIdPrescriptionId($upload, $active, $patientId, $prescriptionId)
236 {
237 sqlQuery(
238 'UPDATE prescriptions
239 SET erx_uploaded = ?,
240 active = ?
241 WHERE patient_id = ?
242 AND id = ?;',
243 array(
244 $upload,
245 $active,
246 $patientId,
247 $prescriptionId
248 )
249 );
250 }
251
252 /**
253 * Return prescription specified
254 * @param integer $prescriptionId Id of the prescription to return
255 * @return array Prescription information specified
256 */
257 public function getPrescriptionById($prescriptionId)
258 {
259 return sqlQuery(
260 'SELECT p.note, p.dosage, p.substitute, p.per_refill, p.form, p.route, p.size, p.interval, p.drug, p.quantity,
261 p.id AS prescid, l1.title AS title1, l2.title AS title2, l3.title AS title3, l4.title AS title4,
262 DATE_FORMAT(date_added,\'%Y%m%d\') AS date_added, CONCAT_WS(fname, \' \', mname, \' \', lname) AS docname
263 FROM prescriptions AS p
264 LEFT JOIN users AS u ON p.provider_id = u.id
265 LEFT JOIN list_options AS l1 ON l1.list_id = \'drug_form\'
266 AND l1.option_id = p.form
267 LEFT JOIN list_options AS l2 ON l2.list_id = \'drug_route\'
268 AND l2.option_id = p.route
269 LEFT JOIN list_options AS l3 ON l3.list_id = \'drug_interval\'
270 AND l3.option_id = p.interval
271 LEFT JOIN list_options AS l4 ON l4.list_id = \'drug_units\'
272 AND l4.option_id = p.unit
273 WHERE p.drug <> \'\'
274 AND p.id = ?;',
275 array($prescriptionId)
276 );
277 }
278
279
280 public function selectMedicationsNotUploadedByPatientId($patientId, $uploadActive, $limit)
281 {
282 return sqlStatement(
283 'SELECT id, begdate, title
284 FROM lists
285 WHERE type = \'medication\'
286 AND pid = ?
287 AND title <> \'\'
288 AND erx_uploaded = \'0\'
289 AND (? = 0
290 OR (enddate IS NULL
291 OR enddate = \'0000-00-00\'
292 )
293 )
294 ORDER BY enddate
295 LIMIT 0, ?;',
296 array(
297 $patientId,
298 $uploadActive,
299 $limit
300 )
301 );
302 }
303
304 public function selectPrescriptionIdsNotUploadedByPatientId($patientId, $uploadActive, $limit)
305 {
306 return sqlStatement(
307 'SELECT id
308 FROM prescriptions
309 WHERE patient_id = ?
310 AND erx_source = \'0\'
311 AND erx_uploaded = \'0\'
312 AND (? = 0
313 OR active = 1
314 ) LIMIT 0, ?;',
315 array(
316 $patientId,
317 $uploadActive,
318 $limit,
319 )
320 );
321 }
322
323 /**
324 * Return option Id for title text of specified list
325 * @param string $listId Id of list to reference
326 * @param string $title Title text to find
327 * @return string Option Id of selected list item
328 */
329 public function selectOptionIdByTitle($listId, $title)
330 {
331 $return = sqlQuery(
332 'SELECT option_id
333 FROM list_options
334 WHERE list_id = ? AND activity = 1
335 AND title = ?;',
336 array(
337 $listId,
338 $title
339 )
340 );
341
342 if (is_array($return)) {
343 $return = $return['option_id'];
344 }
345
346 return $return;
347 }
348
349 /**
350 * Return highest option Id for provided list Id
351 * @param string $listId Id of list to reference
352 * @return integer Highest option Id for provided list Id
353 */
354 public function selectOptionIdsByListId($listId)
355 {
356 $return = sqlQuery(
357 'SELECT option_id
358 FROM list_options
359 WHERE list_id = ? AND activity = 1
360 ORDER BY ABS(option_id) DESC
361 LIMIT 1;',
362 array($listId)
363 );
364
365 if (is_array($return)) {
366 $return = $return['option_id'];
367 }
368
369 return $return;
370 }
371
372 /**
373 * Return user Id by user name
374 * @param string $name Name of user to reference
375 * @return integer Id of provided user name
376 */
377 public function selectUserIdByUserName($name)
378 {
379 $return = sqlQuery(
380 'SELECT id
381 FROM users
382 WHERE username = ?;',
383 array($name)
384 );
385
386 return $return['id'];
387 }
388
389 /**
390 * Insert new option to specified list
391 * @param string $listId Id of list to add option to
392 * @param string $optionId Option Id to add to referenced list
393 * @param string $title Title of option to add to new option
394 */
395 public function insertListOptions($listId, $optionId, $title)
396 {
397 sqlQuery(
398 'INSERT INTO list_options
399 (list_id, option_id, title, seq)
400 VALUES
401 (?, ?, ?, ?);',
402 array(
403 $listId,
404 $optionId,
405 $title,
406 $optionId
407 )
408 );
409 }
410
411 /**
412 * Return Id of prescription selected by GUID and patient Id
413 * @param string $prescriptionGuid GUID of prescription
414 * @param integer $patientId Id of patient
415 * @return resource Prescription Id of specified GUID for selected patient, this resource comes from a call to mysql_query()
416 */
417 public function selectPrescriptionIdByGuidPatientId($prescriptionGuid, $patientId)
418 {
419 return sqlStatement(
420 'SELECT id
421 FROM prescriptions
422 WHERE prescriptionguid = ?
423 AND prescriptionguid IS NOT NULL
424 AND patient_id = ?;',
425 array(
426 $prescriptionGuid,
427 $patientId
428 )
429 );
430 }
431
432 /**
433 * Insert new prescription as external sourced
434 * @param array $prescriptionData Information for creating prescription: [PrescriptionDate, DrugName, DrugID, DrugInfo, DosageNumberDescription, Strength, Refills, PrescriptionNotes, SiteID, rxcui, PrescriptionGuid, ExternalPatientID]
435 * @param integer $encounter Id of encounter for prescription
436 * @param integer $providerId Id of provider for prescription
437 * @param string $authUserId Id of user creating prescription
438 * @param integer $formOptionId Option Id for prescription form
439 * @param integer $routeOptionId Option Id for prescription route
440 * @param integer $unitsOptionId Option Id for prescription units
441 * @param integer $intervalOptionId Option Id for prescription interval
442 * @return integer Id of newly created prescription
443 */
444 public function insertPrescriptions($prescriptionData, $encounter, $providerId, $authUserId, $formOptionId, $routeOptionId, $unitsOptionId, $intervalOptionId)
445 {
446 return sqlInsert(
447 'INSERT INTO `prescriptions`
448 (
449 `datetime`,
450 `erx_source`,
451 `encounter`,
452 `date_added`,
453 `user`,
454 `provider_id`,
455 `form`,
456 `unit`,
457 `route`,
458 `interval`,
459 `drug`,
460 `drug_id`,
461 `drug_info_erx`,
462 `dosage`,
463 `size`,
464 `refills`,
465 `note`,
466 `site`,
467 `rxnorm_drugcode`,
468 `prescriptionguid`,
469 `patient_id`
470 )
471 VALUES
472 (
473 NOW(), \'1\', ?, ?, ?,
474 ?, ?, ?, ?, ?, ?, ?, ?,
475 ?, ?, ?, ?, ?, ?, ?, ?
476 );',
477 array(
478 $encounter,
479 substr($prescriptionData['PrescriptionDate'], 0, 10),
480 $authUserId,
481 $providerId,
482 $formOptionId,
483 $unitsOptionId,
484 $routeOptionId,
485 $intervalOptionId,
486 $prescriptionData['DrugName'],
487 $prescriptionData['DrugID'],
488 $prescriptionData['DrugInfo'],
489 $prescriptionData['DosageNumberDescription'],
490 self::sanitizeNumber($prescriptionData['Strength']),
491 $prescriptionData['Refills'],
492 $prescriptionData['PrescriptionNotes'],
493 $prescriptionData['SiteID'],
494 $prescriptionData['rxcui'],
495 $prescriptionData['PrescriptionGuid'],
496 $prescriptionData['ExternalPatientID']
497 )
498 );
499 }
500
501 /**
502 * Update prescription information as external sourced
503 * @param array $prescriptionData Information for creating prescription: [DrugName, DrugID, DrugInfo, DosageNumberDescription, Strength, Refills, PrescriptionNotes, SiteID, rxcui, PrescriptionGuid, ExternalPatientID]
504 * @param integer $providerId Id of provider for prescription
505 * @param string $authUserId Id of user creating prescription
506 * @param integer $formOptionId Option Id for prescription form
507 * @param integer $routeOptionId Option Id for prescription route
508 * @param integer $unitsOptionId Option Id for prescription units
509 * @param integer $intervalOptionId Option Id for prescription interval
510 */
511 public function updatePrescriptions($prescriptionData, $providerId, $authUserId, $formOptionId, $routeOptionId, $unitsOptionId, $intervalOptionId)
512 {
513 sqlQuery(
514 'UPDATE prescriptions SET
515 `datetime` = NOW(),
516 `erx_source` = \'1\',
517 `active` = \'1\',
518 `user` = ?,
519 `provider_id` = ?,
520 `form` = ?,
521 `unit` = ?,
522 `route` = ?,
523 `interval` = ?,
524 `drug` = ?,
525 `drug_id` = ?,
526 `drug_info_erx` = ?,
527 `dosage` = ?,
528 `size` = ?,
529 `refills` = ?,
530 `note` = ?,
531 `site` = ?,
532 `rxnorm_drugcode` = ?
533 WHERE prescriptionguid = ?
534 AND patient_id = ?;',
535 array(
536 $authUserId,
537 $providerId,
538 $formOptionId,
539 $unitsOptionId,
540 $routeOptionId,
541 $intervalOptionId,
542 $prescriptionData['DrugName'],
543 $prescriptionData['DrugID'],
544 $prescriptionData['DrugInfo'],
545 $prescriptionData['DosageNumberDescription'],
546 self::sanitizeNumber($prescriptionData['Strength']),
547 $prescriptionData['Refills'],
548 $prescriptionData['PrescriptionNotes'],
549 $prescriptionData['SiteID'],
550 $prescriptionData['rxcui'],
551 $prescriptionData['PrescriptionGuid'],
552 $prescriptionData['ExternalPatientID']
553 )
554 );
555 }
556
557 /**
558 * Return eRx source of specified active allergy for selected patient
559 * @param integer $patientId Id of patient to select
560 * @param string $name Name of active allergy to return
561 * @return integer eRx source flag of specified allergy for selected patient: [0 = OpenEMR, 1 = External]
562 */
563 public function selectAllergyErxSourceByPatientIdName($patientId, $name)
564 {
565 $return = sqlQuery(
566 'SELECT erx_source
567 FROM lists
568 WHERE pid = ?
569 AND type = \'allergy\'
570 AND title = ?
571 AND (
572 enddate IS NULL
573 OR enddate = \'0000-00-00\'
574 );',
575 array(
576 $patientId,
577 $name
578 )
579 );
580
581 if (is_array($return)) {
582 $return = $return['erx_source'];
583 }
584
585 return $return;
586 }
587
588 /**
589 * Insert new allergy as external sourced
590 * @param string $name Allergy name to insert
591 * @param integer $allergyId External allergy Id
592 * @param integer $patientId Patient Id
593 * @param integer $authUserId User Id
594 * @param integer $outcome Allergy option Id
595 */
596 public function insertAllergy($name, $allergyId, $patientId, $authUserId, $outcome)
597 {
598 sqlQuery(
599 'INSERT INTO lists
600 (
601 date, type, erx_source, begdate,
602 title, external_allergyid, pid, user, outcome
603 )
604 VALUES
605 (
606 NOW(), \'allergy\', \'1\', NOW(),
607 ?, ?, ?, ?, ?
608 );',
609 array(
610 $name,
611 $allergyId,
612 $patientId,
613 $authUserId,
614 $outcome
615 )
616 );
617
618 setListTouch($patientId, 'allergy');
619 }
620
621 /**
622 * Update allergy outcome and external Id as external sourced using patient Id and allergy name
623 * @param integer $outcome Allergy outcome Id to set
624 * @param integer $externalId External allergy Id to set
625 * @param integer $patientId Patient Id to select
626 * @param string $name Allergy name to select
627 */
628 public function updateAllergyOutcomeExternalIdByPatientIdName($outcome, $externalId, $patientId, $name)
629 {
630 sqlQuery(
631 'UPDATE lists
632 SET outcome = ?,
633 erx_source = \'1\',
634 external_allergyid = ?
635 WHERE pid = ?
636 AND title = ?;',
637 array(
638 $outcome,
639 $externalId,
640 $patientId,
641 $name
642 )
643 );
644 }
645
646 /**
647 * Update external sourced allergy outcome using patient Id, external Id, and allergy name
648 * @param integer $outcome Allergy outcome Id to set
649 * @param integer $patientId Patient Id to select
650 * @param integer $externalId External allergy Id to select
651 * @param string $name Allergy name to select
652 */
653 public function updateAllergyOutcomeByPatientIdExternalIdName($outcome, $patientId, $externalId, $name)
654 {
655 sqlQuery(
656 'UPDATE lists
657 SET outcome = ?
658 WHERE pid = ?
659 AND erx_source = \'1\'
660 AND external_allergyid = ?
661 AND title = ?;',
662 array(
663 $outcome,
664 $patientId,
665 $externalId,
666 $name
667 )
668 );
669 }
670
671 public function updateAllergyUploadedByPatientIdAllergyId($uploaded, $patientId, $allergyId)
672 {
673 sqlQuery(
674 'UPDATE lists
675 SET erx_uploaded = ?
676 WHERE type = \'allergy\'
677 AND pid = ?
678 AND id = ?;',
679 array(
680 $uploaded,
681 $patientId,
682 $allergyId
683 )
684 );
685 }
686
687 /**
688 * Return all external sourced active allergies for patient using patient Id
689 * @param integer $patientId Patient Id to select
690 * @return resource Patients active allergies, this resource comes from a call to mysql_query()
691 */
692 public function selectActiveAllergiesByPatientId($patientId)
693 {
694 return sqlStatement(
695 'SELECT id, title
696 FROM lists
697 WHERE pid = ?
698 AND type = \'allergy\'
699 AND erx_source = \'1\'
700 AND (
701 enddate IS NULL
702 OR enddate = \'0000-00-00\'
703 );',
704 array($patientId)
705 );
706 }
707
708 /**
709 * Update allergy end date for specified patient Id and list Id
710 * @param integer $patientId Id of patient to lookup
711 * @param integer $listId Id of allergy to update
712 */
713 public function updateAllergyEndDateByPatientIdListId($patientId, $listId)
714 {
715 sqlQuery(
716 'UPDATE lists
717 SET enddate = now()
718 WHERE pid = ?
719 AND id = ?
720 AND type = \'allergy\';',
721 array(
722 $patientId,
723 $listId
724 )
725 );
726 }
727
728 /**
729 * Update eRx uploaded status using list Id
730 * @param integer $listId Id of list item
731 * @param integer $erx [optional - defaults to 0] Upload status to set: [0 = Pending NewCrop upload, 1 = Uploaded TO NewCrop]
732 */
733 public function updateErxUploadedByListId($listId, $erx = 0)
734 {
735 sqlQuery(
736 'UPDATE lists
737 SET erx_uploaded = ?
738 WHERE id = ?;',
739 array(
740 $erx,
741 $listId
742 )
743 );
744 }
745
746 /**
747 * Return patient import status using patient Id
748 * @param integer $patientId Id of patient
749 * @return integer Import status for specified patient: [1 = Prescription Press, 2 = Prescription Import, 3 = Allergy Press, 4 = Allergy Import]
750 */
751 public function getPatientImportStatusByPatientId($patientId)
752 {
753 $return = sqlquery(
754 'SELECT soap_import_status
755 FROM patient_data
756 WHERE pid = ?;',
757 array($patientId)
758 );
759 return $return['soap_import_status'];
760 }
761
762 /**
763 * Update patient import status using patient Id
764 * @param integer $patientId Id of patient to update
765 * @param integer $status Import status to update specified patient: [1 = Prescription Press, 2 = Prescription Import, 3 = Allergy Press, 4 = Allergy Import]
766 */
767 public function updatePatientImportStatusByPatientId($patientId, $status)
768 {
769 sqlQuery(
770 'UPDATE patient_data
771 SET soap_import_status = ?
772 WHERE pid = ?;',
773 array(
774 $status,
775 $patientId
776 )
777 );
778 }
779 }
Mirror of official OpenEMR Sourceforge repository