3 * This file contains functions for handling notes attached to patient files.
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * 2013-02-08 EMR Direct: changes to allow notes added by background-services with pid=0
15 * Retrieve a note, given its ID
17 * @param string $id the ID of the note to retrieve.
18 * @param string $cols A list of columns to retrieve. defaults to '*' for all.
20 function getPnoteById($id, $cols = "*")
22 return sqlQuery("SELECT $cols FROM pnotes WHERE id=? " .
23 ' AND deleted != 1 '. // exclude ALL deleted notes
24 'order by date DESC limit 0,1', array($id) );
28 * Get the patient notes for the given user.
30 * This function is used to retrieve notes assigned to the given user, or
31 * optionally notes assigned to any user.
33 * @param string $activity 0 for deleted notes, 1 (the default) for active
34 * notes, or 'All' for all.
35 * @param string $show_all whether to display only the selected user's
36 * messages, or all users' messages.
37 * @param string $user The user whom's notes you want to retrieve.
38 * @param bool $count Whether to return a count, or just return 0.
39 * @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)
40 * @param string $sortorder whether to sort ascending or descending.
41 * @param string $begin what row to start retrieving results from.
42 * @param string $listnumber number of rows to return.
43 * @return int The number of rows retrieved, or 0 if $count was true.
45 function getPnotesByUser($activity="1",$show_all="no",$user='',$count=false,$sortby='',$sortorder='',$begin='',$listnumber='')
48 // Set the activity part of query
50 $activity_query = " pnotes.message_status != 'Done' AND pnotes.activity = 1 AND ";
52 else if ($activity=='0') {
53 $activity_query = " (pnotes.message_status = 'Done' OR pnotes.activity = 0) AND ";
55 else { //$activity=='all'
56 $activity_query = " ";
59 // Set whether to show chosen user or all users
60 if ($show_all == 'yes' ) {
67 // 2013-02-08 EMR Direct: minor changes to query so notes with pid=0 don't disappear
68 $sql = "SELECT pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status,
69 IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
70 IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
71 patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
72 FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
73 LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE $activity_query
74 pnotes.deleted != '1' AND pnotes.assigned_to LIKE ?";
75 if (!empty($sortby) || !empty($sortorder) || !empty($begin) || !empty($listnumber)) {
76 $sql .= " order by ".escape_sql_column_name($sortby,array('users','patient_data','pnotes'),true).
77 " ".escape_sort_order($sortorder).
78 " limit ".escape_limit($begin).", ".escape_limit($listnumber);
80 $result = sqlStatement($sql, array($usrvar));
84 if(sqlNumRows($result) != 0) {
85 $total = sqlNumRows($result);
97 function getPnotesByDate(
110 $sqlParameterArray = array();
112 $sql = "SELECT $cols FROM pnotes AS p, gprelations AS r " .
113 "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
114 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid != p.user";
115 array_push($sqlParameterArray, '%'.$date.'%', $docid);
118 $sql = "SELECT $cols FROM pnotes AS p, gprelations AS r " .
119 "WHERE p.date LIKE ? AND r.type1 = 2 AND " .
120 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid != p.user";
121 array_push($sqlParameterArray, '%'.$date.'%', $orderid);
124 $sql = "SELECT $cols FROM pnotes AS p " .
125 "WHERE date LIKE ? AND pid LIKE ? AND p.pid != p.user";
126 array_push($sqlParameterArray, '%'.$date.'%', $pid);
128 $sql .= " AND deleted != 1"; // exclude ALL deleted notes
129 if ($activity != "all") {
130 if ($activity == '0') {
131 // only return inactive
132 $sql .= " AND (activity = '0' OR message_status = 'Done') ";
134 else { // $activity == '1'
135 // only return active
136 $sql .= " AND activity = '1' AND message_status != 'Done' ";
140 $sql .= " AND assigned_to LIKE ?";
141 array_push($sqlParameterArray, $username);
144 $sql .= " AND message_status IN ('".str_replace(",", "','", add_escape_custom($status) )."')";
145 $sql .= " ORDER BY date DESC";
147 $sql .= " LIMIT ".escape_limit($start).", ".escape_limit($limit);
149 $res = sqlStatement($sql, $sqlParameterArray);
152 for ($iter = 0;$row = sqlFetchArray($res);$iter++)
157 // activity can only be 0, 1, or 'all'
158 function getSentPnotesByDate(
171 $sqlParameterArray = array();
173 $sql = "SELECT $cols FROM pnotes AS p, gprelations AS r " .
174 "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
175 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid = p.user";
176 array_push($sqlParameterArray, '%'.$date.'%', $docid);
179 $sql = "SELECT $cols FROM pnotes AS p, gprelations AS r " .
180 "WHERE p.date LIKE ? AND r.type1 = 2 AND " .
181 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid = p.user";
182 array_push($sqlParameterArray, '%'.$date.'%', $orderid);
185 $sql = "SELECT $cols FROM pnotes AS p " .
186 "WHERE date LIKE ? AND pid LIKE ? AND p.pid = p.user";
187 array_push($sqlParameterArray, '%'.$date.'%', $pid);
189 $sql .= " AND deleted != 1"; // exclude ALL deleted notes
190 if ($activity != "all") {
191 if ($activity == '0') {
192 // only return inactive
193 $sql .= " AND (activity = '0' OR message_status = 'Done') ";
195 else { // $activity == '1'
196 // only return active
197 $sql .= " AND activity = '1' AND message_status != 'Done' ";
201 $sql .= " AND assigned_to LIKE ?";
202 array_push($sqlParameterArray, $username);
205 $sql .= " AND message_status IN ('".str_replace(",", "','", add_escape_custom($status) )."')";
206 $sql .= " ORDER BY date DESC";
208 $sql .= " LIMIT ".escape_limit($start).", ".escape_limit($limit);
210 $res = sqlStatement($sql, $sqlParameterArray);
213 for ($iter = 0;$row = sqlFetchArray($res);$iter++)
218 function getPatientNotes($pid = '', $limit = '', $offset = 0, $search = '')
221 $limit = "LIMIT ".escape_limit($offset).", ".escape_limit($limit);
232 CONCAT(pd.fname, ' ', pd.lname)
238 LEFT JOIN patient_data AS pd
240 WHERE assigned_to = '-patient-'
247 $res = sqlStatement($sql, array($pid));
248 for($iter = 0;$row = sqlFetchArray($res);$iter++){
254 function getPatientNotifications($pid = '', $limit = '', $offset = 0, $search = '')
257 $limit = "LIMIT ".escape_limit($offset).", ".escape_limit($limit);
262 date_created AS `date`,
263 'Patient Reminders' AS `user`,
265 CONCAT(lo.title, ':', lo2.title) AS body,
266 '' as message_status,
267 'Notification' as `type`
269 patient_reminders AS pr
270 LEFT JOIN list_options AS lo
271 ON lo.option_id = pr.category
272 AND lo.list_id = 'rule_action_category' AND lo.activity = 1
273 LEFT JOIN list_options AS lo2
274 ON lo2.option_id = pr.item
275 AND lo2.list_id = 'rule_action' AND lo2.activity = 1
278 AND date_created > DATE_SUB(NOW(), INTERVAL 1 MONTH)
283 $res = sqlStatement($sql, array($pid));
284 for($iter = 0;$row = sqlFetchArray($res);$iter++){
290 function getPatientSentNotes($pid = '', $limit = '', $offset = 0, $search = '')
293 $limit = "LIMIT ".escape_limit($offset).", ".escape_limit($limit);
304 CONCAT(pd.lname, ' ', pd.fname)
311 LEFT JOIN patient_data AS pd
316 AND p.message_status != 'Done'
321 $res = sqlStatement($sql, array($pid,$pid));
322 for($iter = 0;$row = sqlFetchArray($res);$iter++){
328 // activity can be 0, 1, or 'all'
329 function getPnotesByPid($pid, $activity = "1", $cols = "*", $limit=10, $start=0)
331 if ($activity == '1') {
332 // return only active
333 $res = sqlStatement("SELECT $cols FROM pnotes WHERE pid LIKE ? " .
334 "AND activity = '1' ".
335 " AND message_status != 'Done' ".
336 " AND deleted != 1 ".
337 " ORDER BY date DESC LIMIT ".escape_limit($start).",".escape_limit($limit), array($pid) );
339 else if ($activity == '0') {
340 // return only inactive
341 $res = sqlStatement("SELECT $cols FROM pnotes WHERE pid LIKE ? " .
342 "AND (activity = '0' ".
343 " OR message_status = 'Done') ".
344 " AND deleted != 1 ".
345 " ORDER BY date DESC LIMIT ".escape_limit($start).",".escape_limit($limit), array($pid) );
347 else { // $activity == "all"
348 // return both active and inactive
349 $res = sqlStatement("SELECT $cols FROM pnotes WHERE pid LIKE ? " .
350 " AND deleted != 1 ".
351 " ORDER BY date DESC LIMIT ".escape_limit($start).",".escape_limit($limit), array($pid) );
353 for ($iter = 0; $row = sqlFetchArray($res); $iter++)
358 /** Add a note to a patient's medical record.
360 * @param int $pid the ID of the patient whos medical record this note is going to be attached to.
361 * @param string $newtext the note contents.
362 * @param int $authorized
363 * @param int $activity
364 * @param string $title
365 * @param string $assigned_to
366 * @param string $datetime
367 * @param string $message_status
368 * @param string $background_user if set then the pnote is created by a background-service rather than a user
369 * @return int the ID of the added note.
376 $title= 'Unassigned',
379 $message_status = 'New',
383 if (empty($datetime)) $datetime = date('Y-m-d H:i:s');
385 // make inactive if set as Done
386 if ($message_status == 'Done') $activity = 0;
388 $user = ($background_user!="" ? $background_user : $_SESSION['authUser']);
389 $body = date('Y-m-d H:i') . ' (' . $user;
390 if ($assigned_to) $body .= " to $assigned_to";
391 $body = $body . ') ' . $newtext;
393 return sqlInsert('INSERT INTO pnotes (date, body, pid, user, groupname, ' .
394 'authorized, activity, title, assigned_to, message_status) VALUES ' .
395 '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
396 array($datetime, $body, $pid, $user, $_SESSION['authProvider'], $authorized, $activity, $title, $assigned_to, $message_status) );
399 function addMailboxPnote(
407 $message_status = "New"
410 if (empty($datetime)) $datetime = date('Y-m-d H:i:s');
412 // make inactive if set as Done
413 if ($message_status == "Done") $activity = 0;
415 $body = date('Y-m-d H:i') . ' (' . $pid;
416 if ($assigned_to) $body .= " to $assigned_to";
417 $body = $body . ') ' . $newtext;
419 return sqlInsert("INSERT INTO pnotes (date, body, pid, user, groupname, " .
420 "authorized, activity, title, assigned_to, message_status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
421 array($datetime, $body, $pid, $pid, 'Default', $authorized, $activity, $title, $assigned_to, $message_status) );
424 function updatePnote($id, $newtext, $title, $assigned_to, $message_status = "")
426 $row = getPnoteById($id);
427 if (! $row) die("updatePnote() did not find id '".text($id)."'");
428 $activity = $assigned_to ? '1' : '0';
430 // make inactive if set as Done
431 if ($message_status == "Done") $activity = 0;
433 $body = $row['body'] . "\n" . date('Y-m-d H:i') .
434 ' (' . $_SESSION['authUser'];
435 if ($assigned_to) $body .= " to $assigned_to";
436 $body = $body . ') ' . $newtext;
438 if ($message_status) {
439 sqlStatement("UPDATE pnotes SET " .
440 "body = ?, activity = ?, title= ?, " .
441 "assigned_to = ?, message_status = ? WHERE id = ?",
442 array($body, $activity, $title, $assigned_to, $message_status, $id) );
445 sqlStatement("UPDATE pnotes SET " .
446 "body = ?, activity = ?, title= ?, " .
447 "assigned_to = ? WHERE id = ?",
448 array($body, $activity, $title, $assigned_to, $id) );
452 function updatePnoteMessageStatus($id, $message_status)
454 if ($message_status == "Done") {
455 sqlStatement("update pnotes set message_status = ?, activity = '0' where id = ?", array($message_status, $id) );
458 sqlStatement("update pnotes set message_status = ?, activity = '1' where id = ?", array($message_status, $id) );
463 * Set the patient id in an existing message where pid=0
464 * @param $id the id of the existing note
465 * @param $patient_id the patient id to associate with the note
466 * @author EMR Direct <http://www.emrdirect.com/>
468 function updatePnotePatient($id, $patient_id)
470 $row = getPnoteById($id);
471 if (! $row) die("updatePnotePatient() did not find id '".text($id)."'");
472 $activity = $assigned_to ? '1' : '0';
475 if($pid != 0 || (int)$patient_id < 1) die("updatePnotePatient invalid operation");
477 $pid = (int) $patient_id;
478 $newtext = "\n" . date('Y-m-d H:i') . " (patient set by " . $_SESSION['authUser'] .")";
479 $body = $row['body'] . $newtext;
481 sqlStatement("UPDATE pnotes SET pid = ?, body = ? WHERE id = ?", array($pid, $body, $id) );
484 function authorizePnote($id, $authorized = "1")
486 sqlQuery("UPDATE pnotes SET authorized = ? WHERE id = ?", array ($authorized,$id) );
489 function disappearPnote($id)
491 sqlStatement("UPDATE pnotes SET activity = '0', message_status = 'Done' WHERE id=?", array($id) );
495 function reappearPnote($id)
497 sqlStatement("UPDATE pnotes SET activity = '1', message_status = IF(message_status='Done','New',message_status) WHERE id=?", array($id) );
501 function deletePnote($id)
503 sqlStatement("UPDATE pnotes SET deleted = '1' WHERE id=?", array($id) );