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