4 * This file contains functions for handling notes attached to patient files.
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.
11 * 2013-02-08 EMR Direct: changes to allow notes added by background-services with pid=0
16 * Retrieve a note, given its ID
18 * @param string $id the ID of the note to retrieve.
19 * @param string $cols A list of columns to retrieve. defaults to '*' for all.
21 function getPnoteById($id, $cols = "*")
23 return sqlQuery("SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes')) . " FROM pnotes WHERE id=? " .
24 ' AND deleted != 1 ' . // exclude ALL deleted notes
25 'order by date DESC limit 0,1', array($id));
29 * Get the patient notes for the given user.
31 * This function is used to retrieve notes assigned to the given user, or
32 * optionally notes assigned to any user.
34 * @param string $activity 0 for deleted notes, 1 (the default) for active
35 * notes, or 'All' for all.
36 * @param string $show_all whether to display only the selected user's
37 * messages, or all users' messages.
38 * @param string $user The user whom's notes you want to retrieve.
39 * @param bool $count Whether to return a count, or just return 0.
40 * @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)
41 * @param string $sortorder whether to sort ascending or descending.
42 * @param string $begin what row to start retrieving results from.
43 * @param string $listnumber number of rows to return.
44 * @return int The number of rows retrieved, or 0 if $count was true.
46 function getPnotesByUser($activity = "1", $show_all = "no", $user = '', $count = false, $sortby = '', $sortorder = '', $begin = '', $listnumber = '')
49 // Set the activity part of query
50 if ($activity == '1') {
51 $activity_query = " pnotes.message_status != 'Done' AND pnotes.activity = 1 AND ";
52 } elseif ($activity == '0') {
53 $activity_query = " (pnotes.message_status = 'Done' OR pnotes.activity = 0) AND ";
54 } else { //$activity=='all'
55 $activity_query = " ";
58 // Set whether to show chosen user or all users
59 if ($show_all == 'yes') {
66 // 2013-02-08 EMR Direct: minor changes to query so notes with pid=0 don't disappear
67 $sql = "SELECT pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, pnotes.activity,
68 IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
69 IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
70 patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
71 FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
72 LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE $activity_query
73 pnotes.deleted != '1' AND pnotes.assigned_to LIKE ?";
74 if (!empty($sortby) || !empty($sortorder) || !empty($begin) || !empty($listnumber)) {
75 $sql .= " order by " . escape_sql_column_name($sortby, array('users','patient_data','pnotes'), true) .
76 " " . escape_sort_order($sortorder) .
77 " limit " . escape_limit($begin) . ", " . escape_limit($listnumber);
80 $result = sqlStatement($sql, array($usrvar));
84 if (sqlNumRows($result) != 0) {
85 $total = sqlNumRows($result);
96 function getPnotesByDate(
109 $sqlParameterArray = array();
111 $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes', 'gprelations')) . " FROM pnotes AS p, gprelations AS r " .
112 "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
113 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid != p.user";
114 array_push($sqlParameterArray, '%' . $date . '%', $docid);
115 } elseif ($orderid) {
116 $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes', 'gprelations')) . " FROM pnotes AS p, gprelations AS r " .
117 "WHERE p.date LIKE ? AND r.type1 = 2 AND " .
118 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid != p.user";
119 array_push($sqlParameterArray, '%' . $date . '%', $orderid);
121 $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes')) . " FROM pnotes AS p " .
122 "WHERE date LIKE ? AND pid LIKE ? AND p.pid != p.user";
123 array_push($sqlParameterArray, '%' . $date . '%', $pid);
126 $sql .= " AND deleted != 1"; // exclude ALL deleted notes
127 if ($activity != "all") {
128 if ($activity == '0') {
129 // only return inactive
130 $sql .= " AND (activity = '0' OR message_status = 'Done') ";
131 } else { // $activity == '1'
132 // only return active
133 $sql .= " AND activity = '1' AND message_status != 'Done' ";
138 $sql .= " AND assigned_to LIKE ?";
139 array_push($sqlParameterArray, $username);
143 $sql .= " AND message_status IN ('" . str_replace(",", "','", add_escape_custom($status)) . "')";
146 $sql .= " ORDER BY date DESC";
147 if ($limit != "all") {
148 $sql .= " LIMIT " . escape_limit($start) . ", " . escape_limit($limit);
151 $res = sqlStatement($sql, $sqlParameterArray);
154 for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
161 // activity can only be 0, 1, or 'all'
162 function getSentPnotesByDate(
175 $sqlParameterArray = array();
177 $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes', 'gprelations')) . " FROM pnotes AS p, gprelations AS r " .
178 "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
179 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid = p.user";
180 array_push($sqlParameterArray, '%' . $date . '%', $docid);
181 } elseif ($orderid) {
182 $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes','gprelations')) . " FROM pnotes AS p, gprelations AS r " .
183 "WHERE p.date LIKE ? AND r.type1 = 2 AND " .
184 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid = p.user";
185 array_push($sqlParameterArray, '%' . $date . '%', $orderid);
187 $sql = "SELECT " . escape_sql_column_name(process_cols_escape($cols), array('pnotes')) . " FROM pnotes AS p " .
188 "WHERE date LIKE ? AND pid LIKE ? AND p.pid = p.user";
189 array_push($sqlParameterArray, '%' . $date . '%', $pid);
192 $sql .= " AND deleted != 1"; // exclude ALL deleted notes
193 if ($activity != "all") {
194 if ($activity == '0') {
195 // only return inactive
196 $sql .= " AND (activity = '0' OR message_status = 'Done') ";
197 } else { // $activity == '1'
198 // only return active
199 $sql .= " AND activity = '1' AND message_status != 'Done' ";
204 $sql .= " AND assigned_to LIKE ?";
205 array_push($sqlParameterArray, $username);
209 $sql .= " AND message_status IN ('" . str_replace(",", "','", add_escape_custom($status)) . "')";
212 $sql .= " ORDER BY date DESC";
213 if ($limit != "all") {
214 $sql .= " LIMIT " . escape_limit($start) . ", " . escape_limit($limit);
217 $res = sqlStatement($sql, $sqlParameterArray);
220 for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
227 function getPatientNotes($pid = '', $limit = '', $offset = 0, $search = '')
230 $limit = "LIMIT " . escape_limit($offset) . ", " . escape_limit($limit);
242 CONCAT(pd.fname, ' ', pd.lname)
249 LEFT JOIN patient_data AS pd
251 WHERE assigned_to = '-patient-'
258 $res = sqlStatement($sql, array($pid));
259 for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
266 function getPatientNotifications($pid = '', $limit = '', $offset = 0, $search = '')
269 $limit = "LIMIT " . escape_limit($offset) . ", " . escape_limit($limit);
275 date_created AS `date`,
276 'Patient Reminders' AS `user`,
278 CONCAT(lo.title, ':', lo2.title) AS body,
279 '' as message_status,
280 'Notification' as `type`
282 patient_reminders AS pr
283 LEFT JOIN list_options AS lo
284 ON lo.option_id = pr.category
285 AND lo.list_id = 'rule_action_category' AND lo.activity = 1
286 LEFT JOIN list_options AS lo2
287 ON lo2.option_id = pr.item
288 AND lo2.list_id = 'rule_action' AND lo2.activity = 1
291 AND date_created > DATE_SUB(NOW(), INTERVAL 1 MONTH)
296 $res = sqlStatement($sql, array($pid));
297 for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
304 function getPatientSentNotes($pid = '', $limit = '', $offset = 0, $search = '')
307 $limit = "LIMIT " . escape_limit($offset) . ", " . escape_limit($limit);
319 CONCAT(pd.lname, ' ', pd.fname)
326 LEFT JOIN patient_data AS pd
331 AND p.message_status != 'Done'
336 $res = sqlStatement($sql, array($pid,$pid));
337 for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
346 /** Add a note to a patient's medical record.
348 * @param int $pid the ID of the patient whos medical record this note is going to be attached to.
349 * @param string $newtext the note contents.
350 * @param int $authorized
351 * @param int $activity
352 * @param string $title
353 * @param string $assigned_to
354 * @param string $datetime
355 * @param string $message_status
356 * @param string $background_user if set then the pnote is created by a background-service rather than a user
357 * @return int the ID of the added note.
364 $title = 'Unassigned',
367 $message_status = 'New',
368 $background_user = ""
371 if (empty($datetime)) {
372 $datetime = date('Y-m-d H:i:s');
375 // make inactive if set as Done
376 if ($message_status == 'Done') {
379 $user = ($background_user != "" ? $background_user : $_SESSION['authUser']);
380 $body = date('Y-m-d H:i') . ' (' . $user;
382 $body .= " to $assigned_to";
385 $body = $body . ') ' . $newtext;
388 'INSERT INTO pnotes (date, body, pid, user, groupname, ' .
389 'authorized, activity, title, assigned_to, message_status, update_by, update_date) VALUES ' .
390 '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())',
391 array($datetime, $body, $pid, $user, $_SESSION['authProvider'], $authorized, $activity, $title, $assigned_to, $message_status, $_SESSION['authUserID'])
395 function addMailboxPnote(
400 $title = 'Unassigned',
403 $message_status = "New"
406 if (empty($datetime)) {
407 $datetime = date('Y-m-d H:i:s');
410 // make inactive if set as Done
411 if ($message_status == "Done") {
415 $body = date('Y-m-d H:i') . ' (' . $pid;
417 $body .= " to $assigned_to";
420 $body = $body . ') ' . $newtext;
423 "INSERT INTO pnotes (date, body, pid, user, groupname, " .
424 "authorized, activity, title, assigned_to, message_status, update_by, update_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())",
425 array($datetime, $body, $pid, $pid, 'Default', $authorized, $activity, $title, $assigned_to, $message_status, $_SESSION['authUserID'])
429 function updatePnote($id, $newtext, $title, $assigned_to, $message_status = "", $datetime = "")
431 $row = getPnoteById($id);
433 die("updatePnote() did not find id '" . text($id) . "'");
436 if (empty($datetime)) {
437 $datetime = date('Y-m-d H:i:s');
440 $activity = $assigned_to ? '1' : '0';
442 // make inactive if set as Done
443 if ($message_status == "Done") {
447 $body = $row['body'] . "\n" . date('Y-m-d H:i') .
448 ' (' . $_SESSION['authUser'];
450 $body .= " to $assigned_to";
453 $body = $body . ') ' . $newtext;
456 $sql = "UPDATE pnotes SET " .
457 "body = ?, activity = ?, title= ?, " .
458 "assigned_to = ?, update_by = ?, update_date = NOW()";
459 $bindingParams = array($body, $activity, $title, $assigned_to, $_SESSION['authUserID']);
460 if ($message_status) {
461 $sql .= " ,message_status = ?";
462 $bindingParams[] = $message_status;
464 if ($GLOBALS['messages_due_date']) {
465 $sql .= " ,date = ?";
466 $bindingParams[] = $datetime;
468 $sql .= " WHERE id = ?";
469 $bindingParams[] = $id;
470 sqlStatement($sql, $bindingParams);
473 function updatePnoteMessageStatus($id, $message_status)
475 if ($message_status == "Done") {
476 sqlStatement("update pnotes set message_status = ?, activity = '0', update_by = ?, update_date = NOW() where id = ?", array($message_status, $_SESSION['authUserID'], $id));
478 sqlStatement("update pnotes set message_status = ?, activity = '1', update_by = ?, update_date = NOW() where id = ?", array($message_status, $_SESSION['authUserID'], $id));
483 * Set the patient id in an existing message where pid=0
484 * @param $id the id of the existing note
485 * @param $patient_id the patient id to associate with the note
486 * @author EMR Direct <http://www.emrdirect.com/>
488 function updatePnotePatient($id, $patient_id)
490 $row = getPnoteById($id);
492 die("updatePnotePatient() did not find id '" . text($id) . "'");
495 $activity = $assigned_to ? '1' : '0';
498 if ($pid != 0 || (int)$patient_id < 1) {
499 die("updatePnotePatient invalid operation");
502 $pid = (int) $patient_id;
503 $newtext = "\n" . date('Y-m-d H:i') . " (patient set by " . $_SESSION['authUser'] . ")";
504 $body = $row['body'] . $newtext;
506 sqlStatement("UPDATE pnotes SET pid = ?, body = ?, update_by = ?, update_date = NOW() WHERE id = ?", array($pid, $body, $_SESSION['authUserID'], $id));
509 function authorizePnote($id, $authorized = "1")
511 sqlQuery("UPDATE pnotes SET authorized = ? , update_by = ?, update_date = NOW() WHERE id = ?", array ($authorized, $_SESSION['authUserID'], $id));
514 function disappearPnote($id)
516 sqlStatement("UPDATE pnotes SET activity = '0', message_status = 'Done', update_by = ?, update_date = NOW() WHERE id=?", array($_SESSION['authUserID'], $id));
520 function reappearPnote($id)
522 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));
526 function deletePnote($id)
528 sqlStatement("UPDATE pnotes SET deleted = '1', update_by = ?, update_date = NOW() WHERE id=?", array($_SESSION['authUserID'], $id));