library/pnotes.inc.php

   1 <?php
   2
   3 /**
   4  * This file contains functions for handling notes attached to patient files.
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU General Public License
   8  * as published by the Free Software Foundation; either version 2
   9  * of the License, or (at your option) any later version.
  10  *
  11  * 2013-02-08 EMR Direct: changes to allow notes added by background-services with pid=0
  12  */
  13
  14 use OpenEMR\Common\Logging\SystemLogger;
  15
  16 /**
  17  * Retrieve a note, given its ID
  18  *
  19  * @param string $id the ID of the note to retrieve.
  20  * @param string $cols A list of columns to retrieve. defaults to '*' for all.
  21  */
  22 function getPnoteById($id, $cols = "*")
  23 {
  24     return sqlQuery("SELECT "  . escape_sql_column_name(process_cols_escape($cols), array('pnotes')) . " FROM pnotes WHERE id=? " .
  25     ' AND deleted != 1 ' . // exclude ALL deleted notes
  26     'order by date DESC limit 0,1', array($id));
  27 }
  28
  29 /**
  30  * Check a note, given its ID to see if it matches the user
  31  *
  32  * @param string $id the ID of the note to retrieve.
  33  * @param string $user the user seeking to view the note
  34  */
  35 function checkPnotesNoteId(int $id, string $user): bool
  36 {
  37     $check = sqlQuery("SELECT `id`, `user`, `assigned_to` FROM pnotes WHERE id = ? AND deleted != 1", array($id));
  38     if (
  39         !empty($check['id'])
  40         && ($check['id'] == $id)
  41         && (in_array($user, [$check['user'], $check['assigned_to']]))
  42     ) {
  43         return true;
  44     } elseif (
  45         checkPortalAuthUser($user)
  46         && !empty($check['id'])
  47         && ($check['id'] == $id)
  48         && ('portal-user' === $check['assigned_to'])
  49     ) {
  50         return true;
  51     } else {
  52         return false;
  53     }
  54 }
  55
  56 /**
  57  * Check if an auth portal user
  58  *
  59  * @param string $user the user seeking to view the note
  60  * @return bool
  61  */
  62 function checkPortalAuthUser(string $user): bool
  63 {
  64     $check = sqlQuery("SELECT `id` FROM users WHERE portal_user = 1 AND username = ? AND active = 1", array($user));
  65     if (!empty($check['id'])) {
  66         return true;
  67     } else {
  68         return false;
  69     }
  70 }
  71
  72 /**
  73  * Get the patient notes for the given user.
  74  *
  75  * This function is used to retrieve notes assigned to the given user, or
  76  * optionally notes assigned to any user.
  77  *
  78  * @param string $activity 0 for deleted notes, 1 (the default) for active
  79  *                         notes, or 'All' for all.
  80  * @param string $show_all whether to display only the selected user's
  81  *                         messages, or all users' messages.
  82  * @param string $user The user whom's notes you want to retrieve.
  83  * @param bool $count Whether to return a count, or just return 0.
  84  * @param string $sortby A field to sort results by. (options are users.lname,patient_data.lname,pnotes.title,pnotes.date,pnotes.message_status) (will default to users.lname)
  85  * @param string $sortorder whether to sort ascending or descending.
  86  * @param string $begin what row to start retrieving results from.
  87  * @param string $listnumber number of rows to return.
  88  * @return int The number of rows retrieved, or 0 if $count was true.
  89  */
  90 function getPnotesByUser($activity = "1", $show_all = "no", $user = '', $count = false, $sortby = '', $sortorder = '', $begin = '', $listnumber = '')
  91 {
  92
  93   // Set the activity part of query
  94     if ($activity == '1') {
  95         $activity_query = " pnotes.message_status != 'Done' AND pnotes.activity = 1 AND ";
  96     } elseif ($activity == '0') {
  97         $activity_query = " (pnotes.message_status = 'Done' OR pnotes.activity = 0) AND ";
  98     } else { //$activity=='all'
  99         $activity_query = " ";
 100     }
 101     $user_plug = '';
 102   // Set whether to show chosen user or all users
 103     if ($show_all == 'yes') {
 104         $usrvar = '_%';
 105     } else {
 106         if (checkPortalAuthUser($user)) {
 107             $user_plug = "|| pnotes.assigned_to = 'portal-user'";
 108         }
 109         $usrvar = $user;
 110     }
 111
 112   // run the query
 113   // 2013-02-08 EMR Direct: minor changes to query so notes with pid=0 don't disappear
 114     $sql = "SELECT pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, pnotes.activity,
 115           IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
 116           IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
 117           patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
 118           FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
 119           LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE $activity_query
 120           pnotes.deleted != '1' AND (pnotes.assigned_to LIKE ? $user_plug)";
 121     if (!empty($sortby) || !empty($sortorder)  || !empty($begin) || !empty($listnumber)) {
 122         $sql .= " order by " . escape_sql_column_name($sortby, array('users','patient_data','pnotes'), true) .
 123             " " . escape_sort_order($sortorder) .
 124             " limit " . escape_limit($begin) . ", " . escape_limit($listnumber);
 125     }
 126
 127     $result = sqlStatement($sql, array($usrvar));
 128
 129   // return the results
 130     if ($count) {
 131         if (sqlNumRows($result) != 0) {
 132             $total = sqlNumRows($result);
 133         } else {
 134             $total = 0;
 135         }
 136
 137         return $total;
 138     } else {
 139         return $result;
 140     }
 141 }
 142
 143 function getPnotesByDate(
 144     $date,
 145     $activity = "1",
 146     $cols = "*",
 147     $pid = "%",
 148     $limit = "all",
 149     $start = 0,
 150     $username = '',
 151     $docid = 0,
 152     $status = "",
 153     $orderid = 0
 154 ) {
 155
 156     $sqlParameterArray = array();
 157     if ($docid) {
 158         $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes', 'gprelations')) . " FROM pnotes AS p, gprelations AS r " .
 159         "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
 160         "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid != p.user";
 161         array_push($sqlParameterArray, '%' . $date . '%', $docid);
 162     } elseif ($orderid) {
 163         $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes', 'gprelations')) . " FROM pnotes AS p, gprelations AS r " .
 164         "WHERE p.date LIKE ? AND r.type1 = 2 AND " .
 165         "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid != p.user";
 166         array_push($sqlParameterArray, '%' . $date . '%', $orderid);
 167     } else {
 168         $sql = "SELECT "  . escape_sql_column_name(process_cols_escape($cols), array('pnotes')) . " FROM pnotes AS p " .
 169         "WHERE date LIKE ? AND pid LIKE ? AND p.pid != p.user";
 170         array_push($sqlParameterArray, '%' . $date . '%', $pid);
 171     }
 172
 173     $sql .= " AND deleted != 1"; // exclude ALL deleted notes
 174     if ($activity != "all") {
 175         if ($activity == '0') {
 176             // only return inactive
 177             $sql .= " AND (activity = '0' OR message_status = 'Done') ";
 178         } else { // $activity == '1'
 179             // only return active
 180             $sql .= " AND activity = '1' AND message_status != 'Done' ";
 181         }
 182     }
 183
 184     if ($username) {
 185         $sql .= " AND assigned_to LIKE ?";
 186         array_push($sqlParameterArray, $username);
 187     }
 188
 189     if ($status) {
 190         $sql .= " AND message_status IN ('" . str_replace(",", "','", add_escape_custom($status)) . "')";
 191     }
 192
 193     $sql .= " ORDER BY date DESC";
 194     if ($limit != "all") {
 195         $sql .= " LIMIT " . escape_limit($start) . ", " . escape_limit($limit);
 196     }
 197
 198     $res = sqlStatement($sql, $sqlParameterArray);
 199
 200     $all = array();
 201     for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 202         $all[$iter] = $row;
 203     }
 204
 205     return $all;
 206 }
 207
 208 // activity can only be 0, 1, or 'all'
 209 function getSentPnotesByDate(
 210     $date,
 211     $activity = "1",
 212     $cols = "*",
 213     $pid = "%",
 214     $limit = "all",
 215     $start = 0,
 216     $username = '',
 217     $docid = 0,
 218     $status = "",
 219     $orderid = 0
 220 ) {
 221
 222     $sqlParameterArray = array();
 223     if ($docid) {
 224         $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes', 'gprelations')) . " FROM pnotes AS p, gprelations AS r " .
 225         "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
 226         "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid = p.user";
 227         array_push($sqlParameterArray, '%' . $date . '%', $docid);
 228     } elseif ($orderid) {
 229         $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes','gprelations')) . " FROM pnotes AS p, gprelations AS r " .
 230         "WHERE p.date LIKE ? AND r.type1 = 2 AND " .
 231         "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid = p.user";
 232         array_push($sqlParameterArray, '%' . $date . '%', $orderid);
 233     } else {
 234         $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes')) . " FROM pnotes AS p " .
 235         "WHERE date LIKE ? AND pid LIKE ? AND p.pid = p.user";
 236         array_push($sqlParameterArray, '%' . $date . '%', $pid);
 237     }
 238
 239     $sql .= " AND deleted != 1"; // exclude ALL deleted notes
 240     if ($activity != "all") {
 241         if ($activity == '0') {
 242             // only return inactive
 243             $sql .= " AND (activity = '0' OR message_status = 'Done') ";
 244         } else { // $activity == '1'
 245             // only return active
 246             $sql .= " AND activity = '1' AND message_status != 'Done' ";
 247         }
 248     }
 249
 250     if ($username) {
 251         $sql .= " AND assigned_to LIKE ?";
 252         array_push($sqlParameterArray, $username);
 253     }
 254
 255     if ($status) {
 256         $sql .= " AND message_status IN ('" . str_replace(",", "','", add_escape_custom($status)) . "')";
 257     }
 258
 259     $sql .= " ORDER BY date DESC";
 260     if ($limit != "all") {
 261         $sql .= " LIMIT " . escape_limit($start) . ", " . escape_limit($limit);
 262     }
 263
 264     $res = sqlStatement($sql, $sqlParameterArray);
 265
 266     $all = array();
 267     for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 268         $all[$iter] = $row;
 269     }
 270
 271     return $all;
 272 }
 273
 274 function getPatientNotes($pid = '', $limit = '', $offset = 0, $search = '')
 275 {
 276     if ($limit) {
 277         $limit = "LIMIT " . escape_limit($offset) . ", " . escape_limit($limit);
 278     }
 279
 280     $sql = "
 281     SELECT
 282       p.id,
 283       p.date,
 284       p.user,
 285       p.title,
 286       REPLACE(
 287         p.body,
 288         '-patient-',
 289         CONCAT(pd.fname, ' ', pd.lname)
 290       ) AS body,
 291       p.message_status,
 292       'Message' as `type`,
 293       p.activity
 294     FROM
 295       pnotes AS p
 296       LEFT JOIN patient_data AS pd
 297         ON pd.id = p.pid
 298     WHERE assigned_to = '-patient-'
 299       AND p.deleted != 1
 300       AND p.pid = ?
 301       $search
 302     ORDER BY `date` desc
 303     $limit
 304   ";
 305     $res = sqlStatement($sql, array($pid));
 306     for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 307         $all[$iter] = $row;
 308     }
 309
 310     return $all;
 311 }
 312
 313 function getPatientNotifications($pid = '', $limit = '', $offset = 0, $search = '')
 314 {
 315     if ($limit) {
 316         $limit = "LIMIT " . escape_limit($offset) . ", " . escape_limit($limit);
 317     }
 318
 319     $sql = "
 320     SELECT
 321       pr.id,
 322       date_created AS `date`,
 323       'Patient Reminders' AS `user`,
 324       due_status AS title,
 325       CONCAT(lo.title, ':', lo2.title) AS body,
 326       '' as message_status,
 327       'Notification' as `type`
 328     FROM
 329       patient_reminders AS pr
 330       LEFT JOIN list_options AS lo
 331         ON lo.option_id = pr.category
 332         AND lo.list_id = 'rule_action_category' AND lo.activity = 1
 333       LEFT JOIN list_options AS lo2
 334         ON lo2.option_id = pr.item
 335         AND lo2.list_id = 'rule_action' AND lo2.activity = 1
 336     WHERE pid = ?
 337       AND active = 1
 338       AND date_created > DATE_SUB(NOW(), INTERVAL 1 MONTH)
 339       $search
 340     ORDER BY `date` desc
 341     $limit
 342   ";
 343     $res = sqlStatement($sql, array($pid));
 344     for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 345         $all[$iter] = $row;
 346     }
 347
 348     return $all;
 349 }
 350
 351 function getPatientSentNotes($pid = '', $limit = '', $offset = 0, $search = '')
 352 {
 353     if ($limit) {
 354         $limit = "LIMIT " . escape_limit($offset) . ", " . escape_limit($limit);
 355     }
 356
 357     $sql = "
 358     SELECT
 359       p.id,
 360       p.date,
 361       p.assigned_to,
 362       p.title,
 363       REPLACE(
 364         p.body,
 365         '-patient-',
 366         CONCAT(pd.lname, ' ', pd.fname)
 367       ) AS body,
 368       p.activity,
 369       p.message_status,
 370       'Message' as `type`
 371     FROM
 372       pnotes AS p
 373       LEFT JOIN patient_data AS pd
 374         ON pd.id = p.pid
 375     WHERE `user` = ?
 376       AND p.deleted != 1
 377       AND p.pid = ?
 378       AND p.message_status != 'Done'
 379       $search
 380     ORDER BY `date` desc
 381     $limit
 382   ";
 383     $res = sqlStatement($sql, array($pid,$pid));
 384     for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 385         $all[$iter] = $row;
 386     }
 387
 388     return $all;
 389 }
 390
 391
 392
 393 /** Add a note to a patient's medical record.
 394  *
 395  * @param int $pid the ID of the patient whos medical record this note is going to be attached to.
 396  * @param string $newtext the note contents.
 397  * @param int $authorized
 398  * @param int $activity
 399  * @param string $title
 400  * @param string $assigned_to
 401  * @param string $datetime
 402  * @param string $message_status
 403  * @param string $background_user if set then the pnote is created by a background-service rather than a user
 404  * @return int the ID of the added note.
 405  */
 406 function addPnote(
 407     $pid,
 408     $newtext,
 409     $authorized = '0',
 410     $activity = '1',
 411     $title = 'Unassigned',
 412     $assigned_to = '',
 413     $datetime = '',
 414     $message_status = 'New',
 415     $background_user = ""
 416 ) {
 417
 418     if (empty($datetime)) {
 419         $datetime = date('Y-m-d H:i:s');
 420     }
 421
 422   // make inactive if set as Done
 423     if ($message_status == 'Done') {
 424         $activity = 0;
 425     }
 426     $user = ($background_user != "" ? $background_user : $_SESSION['authUser']);
 427     $body = date('Y-m-d H:i') . ' (' . $user;
 428     if ($assigned_to) {
 429         $body .= " to $assigned_to";
 430     }
 431
 432     $body = $body . ') ' . $newtext;
 433
 434     return sqlInsert(
 435         'INSERT INTO pnotes (date, body, pid, user, groupname, ' .
 436         'authorized, activity, title, assigned_to, message_status, update_by, update_date) VALUES ' .
 437         '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())',
 438         array($datetime, $body, $pid, $user, ($_SESSION['authProvider'] ?? null), $authorized, $activity, $title, $assigned_to, $message_status, ($_SESSION['authUserID'] ?? null))
 439     );
 440 }
 441
 442 function addMailboxPnote(
 443     $pid,
 444     $newtext,
 445     $authorized = '0',
 446     $activity = '1',
 447     $title = 'Unassigned',
 448     $assigned_to = '',
 449     $datetime = '',
 450     $message_status = "New"
 451 ) {
 452
 453     if (empty($datetime)) {
 454         $datetime = date('Y-m-d H:i:s');
 455     }
 456
 457   // make inactive if set as Done
 458     if ($message_status == "Done") {
 459         $activity = 0;
 460     }
 461
 462     $body = date('Y-m-d H:i') . ' (' . $pid;
 463     if ($assigned_to) {
 464         $body .= " to $assigned_to";
 465     }
 466
 467     $body = $body . ') ' . $newtext;
 468
 469     return sqlInsert(
 470         "INSERT INTO pnotes (date, body, pid, user, groupname, " .
 471         "authorized, activity, title, assigned_to, message_status, update_by, update_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())",
 472         array($datetime, $body, $pid, $pid, 'Default', $authorized, $activity, $title, $assigned_to, $message_status, $_SESSION['authUserID'])
 473     );
 474 }
 475
 476 function updatePnote($id, $newtext, $title, $assigned_to, $message_status = "", $datetime = "")
 477 {
 478     $row = getPnoteById($id);
 479     if (! $row) {
 480         die("updatePnote() did not find id '" . text($id) . "'");
 481     }
 482
 483     if (empty($datetime)) {
 484         $datetime = date('Y-m-d H:i:s');
 485     }
 486
 487     $activity = $assigned_to ? '1' : '0';
 488
 489   // make inactive if set as Done
 490     if ($message_status == "Done") {
 491         $activity = 0;
 492     }
 493
 494     $body = $row['body'] . "\n" . date('Y-m-d H:i') .
 495     ' (' . $_SESSION['authUser'];
 496     if ($assigned_to) {
 497         $body .= " to $assigned_to";
 498     }
 499
 500     $body = $body . ') ' . $newtext;
 501
 502
 503     $sql = "UPDATE pnotes SET " .
 504         "body = ?, activity = ?, title= ?, " .
 505         "assigned_to = ?, update_by = ?, update_date = NOW()";
 506     $bindingParams =  array($body, $activity, $title, $assigned_to, $_SESSION['authUserID']);
 507     if ($message_status) {
 508         $sql .= " ,message_status = ?";
 509         $bindingParams[] = $message_status;
 510     }
 511     if ($GLOBALS['messages_due_date']) {
 512         $sql .= " ,date = ?";
 513         $bindingParams[] = $datetime;
 514     }
 515     $sql .= " WHERE id = ?";
 516     $bindingParams[] = $id;
 517     sqlStatement($sql, $bindingParams);
 518 }
 519
 520 function updatePnoteMessageStatus($id, $message_status)
 521 {
 522     if ($message_status == "Done") {
 523         sqlStatement("update pnotes set message_status = ?, activity = '0', update_by = ?, update_date = NOW() where id = ?", array($message_status, $_SESSION['authUserID'], $id));
 524     } else {
 525         sqlStatement("update pnotes set message_status = ?, activity = '1', update_by = ?, update_date = NOW() where id = ?", array($message_status, $_SESSION['authUserID'], $id));
 526     }
 527 }
 528
 529 /**
 530  * Set the patient id in an existing message where pid=0
 531  * @param $id the id of the existing note
 532  * @param $patient_id the patient id to associate with the note
 533  * @author EMR Direct <http://www.emrdirect.com/>
 534  */
 535 function updatePnotePatient($id, $patient_id)
 536 {
 537     $row = getPnoteById($id);
 538     if (! $row) {
 539         die("updatePnotePatient() did not find id '" . text($id) . "'");
 540     }
 541
 542     $activity = $assigned_to ? '1' : '0';
 543
 544     $pid = $row['pid'];
 545
 546     if ($pid != 0 || (int)$patient_id < 1) {
 547         (new SystemLogger())->errorLogCaller("invalid operation", ['id' => $id, 'patient_id' => $patient_id, 'pid' => $pid]);
 548         die("updatePnotePatient invalid operation");
 549     }
 550
 551     $pid = (int) $patient_id;
 552     $newtext = "\n" . date('Y-m-d H:i') . " (patient set by " . $_SESSION['authUser'] . ")";
 553     $body = $row['body'] . $newtext;
 554
 555     sqlStatement("UPDATE pnotes SET pid = ?, body = ?, update_by = ?, update_date = NOW() WHERE id = ?", array($pid, $body, $_SESSION['authUserID'], $id));
 556 }
 557
 558 function authorizePnote($id, $authorized = "1")
 559 {
 560     sqlQuery("UPDATE pnotes SET authorized = ? , update_by = ?, update_date = NOW() WHERE id = ?", array ($authorized, $_SESSION['authUserID'], $id));
 561 }
 562
 563 function disappearPnote($id)
 564 {
 565     sqlStatement("UPDATE pnotes SET activity = '0', message_status = 'Done', update_by = ?, update_date = NOW()  WHERE id=?", array($_SESSION['authUserID'], $id));
 566     return true;
 567 }
 568
 569 function reappearPnote($id)
 570 {
 571     sqlStatement("UPDATE pnotes SET activity = '1', message_status = IF(message_status='Done','New',message_status), update_by = ?, update_date = NOW() WHERE id=?", array($_SESSION['authUserID'], $id));
 572     return true;
 573 }
 574
 575 function deletePnote($id)
 576 {
 577     $assigned = getAssignedToById($id);
 578     if (!checkPortalAuthUser($_SESSION['authUser']) && $assigned == 'portal-user') {
 579         return false;
 580     }
 581     if (
 582         $assigned == $_SESSION['authUser']
 583         || $assigned == 'portal-user'
 584         || getMessageStatusById($id) == 'Done'
 585     ) {
 586         sqlStatement("UPDATE pnotes SET deleted = '1', update_by = ?, update_date = NOW() WHERE id=?", array($_SESSION['authUserID'], $id));
 587         return true;
 588     } else {
 589         return false;
 590     }
 591 }
 592
 593 // Note that it is assumed that html escaping has happened before this function is called
 594 function pnoteConvertLinks($note)
 595 {
 596     $noteActiveLink = preg_replace('!(https://[-a-zA-Z()0-9@:%_+.~#?&;//=]+)!i', '<a href="$1" target="_blank" rel="noopener">$1</a>', $note);
 597     if (empty($noteActiveLink)) {
 598         // something bad happened (preg_replace returned null) or the $note was empty
 599         return $note;
 600     } else {
 601         return $noteActiveLink;
 602     }
 603 }
 604
 605 /**
 606  * Retrieve assigned_to field given the note ID
 607  *
 608  * @param string $id the ID of the note to retrieve.
 609  */
 610 function getAssignedToById($id)
 611 {
 612     $result = sqlQuery("SELECT assigned_to FROM pnotes WHERE id=?", array($id));
 613     return $result['assigned_to'];
 614 }
 615
 616 /**
 617  * Retrieve message_status field given the note ID
 618  *
 619  * @param string $id the ID of the note to retrieve.
 620  */
 621 function getMessageStatusById($id)
 622 {
 623     $result = sqlQuery("SELECT message_status FROM pnotes WHERE id=?", array($id));
 624     return $result['message_status'];
 625 }