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