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
13 /* for sqlQuery(), sqlStatement(), sqlNumRows(), sqlFetchArray(). */
14 require_once($GLOBALS['srcdir'].'/sql.inc');
17 * Retrieve a note, given its ID
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.
22 function getPnoteById($id, $cols = "*")
24 return sqlQuery("SELECT $cols FROM pnotes WHERE id=? " .
25 ' AND deleted != 1 '. // exclude ALL deleted notes
26 'order by date DESC limit 0,1', array($id) );
30 * Get the patient notes for the given user.
32 * This function is used to retrieve notes assigned to the given user, or
33 * optionally notes assigned to any user.
35 * @param string $activity 0 for deleted notes, 1 (the default) for active
36 * notes, or 'All' for all.
37 * @param string $show_all whether to display only the selected user's
38 * messages, or all users' messages.
39 * @param string $user The user whom's notes you want to retrieve.
40 * @param bool $count Whether to return a count, or just return 0.
41 * @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)
42 * @param string $sortorder whether to sort ascending or descending.
43 * @param string $begin what row to start retrieving results from.
44 * @param string $listnumber number of rows to return.
45 * @return int The number of rows retrieved, or 0 if $count was true.
47 function getPnotesByUser($activity="1",$show_all="no",$user='',$count=false,$sortby='',$sortorder='',$begin='',$listnumber='')
50 // Set the options for $sortby (for sql-injection hardening)
51 $sortbyoptions = array("users.lname","patient_data.lname","pnotes.title","pnotes.date","pnotes.message_status");
53 // Set the activity part of query
55 $activity_query = " pnotes.message_status != 'Done' AND pnotes.activity = 1 AND ";
57 else if ($activity=='0') {
58 $activity_query = " (pnotes.message_status = 'Done' OR pnotes.activity = 0) AND ";
60 else { //$activity=='all'
61 $activity_query = " ";
64 // Set whether to show chosen user or all users
65 if ($show_all == 'yes' ) {
72 // 2013-02-08 EMR Direct: minor changes to query so notes with pid=0 don't disappear
73 $sql = "SELECT pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status,
74 IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname,
75 IF(pnotes.pid = 0 OR pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname,
76 patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname
77 FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username)
78 LEFT JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE $activity_query
79 pnotes.deleted != '1' AND pnotes.assigned_to LIKE ?";
80 if (!empty($sortby) || !empty($sortorder) || !empty($begin) || !empty($listnumber)) {
81 $sql .= " order by ".escape_identifier($sortby,TRUE,$sortbyoptions).
82 " ".escape_sort_order($sortorder).
83 " limit ".escape_limit($begin).", ".escape_limit($listnumber);
85 $result = sqlStatement($sql, array($usrvar));
89 if(sqlNumRows($result) != 0) {
90 $total = sqlNumRows($result);
102 function getPnotesByDate($date, $activity = "1", $cols = "*", $pid = "%",
103 $limit = "all", $start = 0, $username = '', $docid = 0, $status = "")
105 $sqlParameterArray = array();
107 $sql = "SELECT $cols FROM pnotes AS p, gprelations AS r " .
108 "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
109 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid != p.user";
110 array_push($sqlParameterArray, '%'.$date.'%', $docid);
113 $sql = "SELECT $cols FROM pnotes AS p " .
114 "WHERE date LIKE ? AND pid LIKE ? AND p.pid != p.user";
115 array_push($sqlParameterArray, '%'.$date.'%', $pid);
117 $sql .= " AND deleted != 1"; // exclude ALL deleted notes
118 if ($activity != "all") {
119 if ($activity == '0') {
120 // only return inactive
121 $sql .= " AND (activity = '0' OR message_status = 'Done') ";
123 else { // $activity == '1'
124 // only return active
125 $sql .= " AND activity = '1' AND message_status != 'Done' ";
129 $sql .= " AND assigned_to LIKE ?";
130 array_push($sqlParameterArray, $username);
133 $sql .= " AND message_status IN ('".str_replace(",", "','", add_escape_custom($status) )."')";
134 $sql .= " ORDER BY date DESC";
136 $sql .= " LIMIT ".escape_limit($start).", ".escape_limit($limit);
138 $res = sqlStatement($sql, $sqlParameterArray);
141 for ($iter = 0;$row = sqlFetchArray($res);$iter++)
146 // activity can only be 0, 1, or 'all'
147 function getSentPnotesByDate($date, $activity = "1", $cols = "*", $pid = "%",
148 $limit = "all", $start = 0, $username = '', $docid = 0, $status = "")
150 $sqlParameterArray = array();
152 $sql = "SELECT $cols FROM pnotes AS p, gprelations AS r " .
153 "WHERE p.date LIKE ? AND r.type1 = 1 AND " .
154 "r.id1 = ? AND r.type2 = 6 AND p.id = r.id2 AND p.pid = p.user";
155 array_push($sqlParameterArray, '%'.$date.'%', $docid);
158 $sql = "SELECT $cols FROM pnotes AS p " .
159 "WHERE date LIKE ? AND pid LIKE ? AND p.pid = p.user";
160 array_push($sqlParameterArray, '%'.$date.'%', $pid);
162 $sql .= " AND deleted != 1"; // exclude ALL deleted notes
163 if ($activity != "all") {
164 if ($activity == '0') {
165 // only return inactive
166 $sql .= " AND (activity = '0' OR message_status = 'Done') ";
168 else { // $activity == '1'
169 // only return active
170 $sql .= " AND activity = '1' AND message_status != 'Done' ";
174 $sql .= " AND assigned_to LIKE ?";
175 array_push($sqlParameterArray, $username);
178 $sql .= " AND message_status IN ('".str_replace(",", "','", add_escape_custom($status) )."')";
179 $sql .= " ORDER BY date DESC";
181 $sql .= " LIMIT ".escape_limit($start).", ".escape_limit($limit);
183 $res = sqlStatement($sql, $sqlParameterArray);
186 for ($iter = 0;$row = sqlFetchArray($res);$iter++)
191 function getPatientNotes($pid = '', $limit = '', $offset = 0, $search = '')
194 $limit = "LIMIT ".escape_limit($offset).", ".escape_limit($limit);
205 CONCAT(pd.fname, ' ', pd.lname)
211 LEFT JOIN patient_data AS pd
213 WHERE assigned_to = '-patient-'
220 $res = sqlStatement($sql, array($pid));
221 for($iter = 0;$row = sqlFetchArray($res);$iter++){
227 function getPatientNotifications($pid = '', $limit = '', $offset = 0, $search = '')
230 $limit = "LIMIT ".escape_limit($offset).", ".escape_limit($limit);
235 date_created AS `date`,
236 'Patient Reminders' AS `user`,
238 CONCAT(lo.title, ':', lo2.title) AS body,
239 '' as message_status,
240 'Notification' as `type`
242 patient_reminders AS pr
243 LEFT JOIN list_options AS lo
244 ON lo.option_id = pr.category
245 AND lo.list_id = 'rule_action_category'
246 LEFT JOIN list_options AS lo2
247 ON lo2.option_id = pr.item
248 AND lo2.list_id = 'rule_action'
251 AND date_created > DATE_SUB(NOW(), INTERVAL 1 MONTH)
256 $res = sqlStatement($sql, array($pid));
257 for($iter = 0;$row = sqlFetchArray($res);$iter++){
263 function getPatientSentNotes($pid = '', $limit = '', $offset = 0, $search = '')
266 $limit = "LIMIT ".escape_limit($offset).", ".escape_limit($limit);
277 CONCAT(pd.lname, ' ', pd.fname)
284 LEFT JOIN patient_data AS pd
289 AND p.message_status != 'Done'
294 $res = sqlStatement($sql, array($pid,$pid));
295 for($iter = 0;$row = sqlFetchArray($res);$iter++){
301 // activity can be 0, 1, or 'all'
302 function getPnotesByPid ($pid, $activity = "1", $cols = "*", $limit=10, $start=0)
304 if ($activity == '1') {
305 // return only active
306 $res = sqlStatement("SELECT $cols FROM pnotes WHERE pid LIKE ? " .
307 "AND activity = '1' ".
308 " AND message_status != 'Done' ".
309 " AND deleted != 1 ".
310 " ORDER BY date DESC LIMIT ".escape_limit($start).",".escape_limit($limit), array($pid) );
312 else if ($activity == '0') {
313 // return only inactive
314 $res = sqlStatement("SELECT $cols FROM pnotes WHERE pid LIKE ? " .
315 "AND (activity = '0' ".
316 " OR message_status = 'Done') ".
317 " AND deleted != 1 ".
318 " ORDER BY date DESC LIMIT ".escape_limit($start).",".escape_limit($limit), array($pid) );
320 else { // $activity == "all"
321 // return both active and inactive
322 $res = sqlStatement("SELECT $cols FROM pnotes WHERE pid LIKE ? " .
323 " AND deleted != 1 ".
324 " ORDER BY date DESC LIMIT ".escape_limit($start).",".escape_limit($limit), array($pid) );
326 for ($iter = 0; $row = sqlFetchArray($res); $iter++)
331 /** Add a note to a patient's medical record.
333 * @param int $pid the ID of the patient whos medical record this note is going to be attached to.
334 * @param string $newtext the note contents.
335 * @param int $authorized
336 * @param int $activity
337 * @param string $title
338 * @param string $assigned_to
339 * @param string $datetime
340 * @param string $message_status
341 * @param string $background_user if set then the pnote is created by a background-service rather than a user
342 * @return int the ID of the added note.
344 function addPnote($pid, $newtext, $authorized = '0', $activity = '1',
345 $title= 'Unassigned', $assigned_to = '', $datetime = '',
346 $message_status = 'New', $background_user="")
348 if (empty($datetime)) $datetime = date('Y-m-d H:i:s');
350 // make inactive if set as Done
351 if ($message_status == 'Done') $activity = 0;
353 $user = ($background_user!="" ? $background_user : $_SESSION['authUser']);
354 $body = date('Y-m-d H:i') . ' (' . $user;
355 if ($assigned_to) $body .= " to $assigned_to";
356 $body = $body . ') ' . $newtext;
358 return sqlInsert('INSERT INTO pnotes (date, body, pid, user, groupname, ' .
359 'authorized, activity, title, assigned_to, message_status) VALUES ' .
360 '(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
361 array($datetime, $body, $pid, $user, $_SESSION['authProvider'], $authorized, $activity, $title, $assigned_to, $message_status) );
364 function addMailboxPnote($pid, $newtext, $authorized = '0', $activity = '1',
365 $title='Unassigned', $assigned_to = '', $datetime = '', $message_status = "New")
367 if (empty($datetime)) $datetime = date('Y-m-d H:i:s');
369 // make inactive if set as Done
370 if ($message_status == "Done") $activity = 0;
372 $body = date('Y-m-d H:i') . ' (' . $pid;
373 if ($assigned_to) $body .= " to $assigned_to";
374 $body = $body . ') ' . $newtext;
376 return sqlInsert("INSERT INTO pnotes (date, body, pid, user, groupname, " .
377 "authorized, activity, title, assigned_to, message_status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
378 array($datetime, $body, $pid, $pid, 'Default', $authorized, $activity, $title, $assigned_to, $message_status) );
381 function updatePnote($id, $newtext, $title, $assigned_to, $message_status = "")
383 $row = getPnoteById($id);
384 if (! $row) die("updatePnote() did not find id '".text($id)."'");
385 $activity = $assigned_to ? '1' : '0';
387 // make inactive if set as Done
388 if ($message_status == "Done") $activity = 0;
390 $body = $row['body'] . "\n" . date('Y-m-d H:i') .
391 ' (' . $_SESSION['authUser'];
392 if ($assigned_to) $body .= " to $assigned_to";
393 $body = $body . ') ' . $newtext;
395 if ($message_status) {
396 sqlStatement("UPDATE pnotes SET " .
397 "body = ?, activity = ?, title= ?, " .
398 "assigned_to = ?, message_status = ? WHERE id = ?",
399 array($body, $activity, $title, $assigned_to, $message_status, $id) );
402 sqlStatement("UPDATE pnotes SET " .
403 "body = ?, activity = ?, title= ?, " .
404 "assigned_to = ? WHERE id = ?",
405 array($body, $activity, $title, $assigned_to, $id) );
409 function updatePnoteMessageStatus($id, $message_status)
411 if ($message_status == "Done") {
412 sqlStatement("update pnotes set message_status = ?, activity = '0' where id = ?", array($message_status, $id) );
415 sqlStatement("update pnotes set message_status = ?, activity = '1' where id = ?", array($message_status, $id) );
420 * Set the patient id in an existing message where pid=0
421 * @param $id the id of the existing note
422 * @param $patient_id the patient id to associate with the note
423 * @author EMR Direct <http://www.emrdirect.com/>
425 function updatePnotePatient($id, $patient_id)
427 $row = getPnoteById($id);
428 if (! $row) die("updatePnotePatient() did not find id '".text($id)."'");
429 $activity = $assigned_to ? '1' : '0';
432 if($pid != 0 || (int)$patient_id < 1) die("updatePnotePatient invalid operation");
434 $pid = (int) $patient_id;
435 $newtext = "\n" . date('Y-m-d H:i') . " (patient set by " . $_SESSION['authUser'] .")";
436 $body = $row['body'] . $newtext;
438 sqlStatement("UPDATE pnotes SET pid = ?, body = ? WHERE id = ?", array($pid, $body, $id) );
441 function authorizePnote($id, $authorized = "1")
443 sqlQuery("UPDATE pnotes SET authorized = ? WHERE id = ?", array ($authorized,$id) );
446 function disappearPnote($id)
448 sqlStatement("UPDATE pnotes SET activity = '0', message_status = 'Done' WHERE id=?", array($id) );
452 function reappearPnote ($id)
454 sqlStatement("UPDATE pnotes SET activity = '1', message_status = IF(message_status='Done','New',message_status) WHERE id=?", array($id) );
458 function deletePnote($id)
460 sqlStatement("UPDATE pnotes SET deleted = '1' WHERE id=?", array($id) );