portal/lib/appsql.class.php

   1 <?php
   2
   3 /**
   4  * Patient Portal
   5  *
   6  * @package   OpenEMR
   7  * @link      http://www.open-emr.org
   8  * @author    Jerry Padgett <sjpadgett@gmail.com>
   9  * @copyright Copyright (c) 2016-2019 Jerry Padgett <sjpadgett@gmail.com>
  10  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  11  */
  12
  13 require_once(dirname(__FILE__) . '/../../interface/globals.php');
  14
  15 use OpenEMR\Common\Crypto\CryptoGen;
  16 use OpenEMR\Common\Logging\EventAuditLogger;
  17
  18 class ApplicationTable
  19 {
  20     public function __construct()
  21     {
  22     }
  23
  24     /**
  25      * Function zQuery
  26      * All DB Transactions take place
  27      *
  28      * @param String $sql
  29      *            SQL Query Statment
  30      * @param array $params
  31      *            SQL Parameters
  32      * @param boolean $log
  33      *            Logging True / False
  34      * @param boolean $error
  35      *            Error Display True / False
  36      * @return type
  37      */
  38     public function zQuery($sql, $params = '', $log = false, $error = true)
  39     {
  40         $return = false;
  41         $result = false;
  42
  43         try {
  44             $return = sqlStatement($sql, $params);
  45             $result = true;
  46         } catch (Exception $e) {
  47             if ($error) {
  48                 $this->errorHandler($e, $sql, $params);
  49             }
  50         }
  51
  52         if ($log) {
  53             EventAuditLogger::instance()->auditSQLEvent($sql, $result, $params);
  54         }
  55
  56         return $return;
  57     }
  58
  59     public function getPortalAuditRec($recid)
  60     {
  61         $return = false;
  62         $result = false;
  63         try {
  64             $sql = "Select * From onsite_portal_activity Where  id = ?";
  65             $return = sqlStatementNoLog($sql, $recid);
  66             $result = true;
  67         } catch (Exception $e) {
  68             $this->errorHandler($e, $sql);
  69         }
  70         if ($result === true) {
  71             return sqlFetchArray($return);
  72         } else {
  73             return false;
  74         }
  75     }
  76
  77     public function getPortalAudit($patientid, $action = 'review', $activity = 'profile', $status = 'waiting', $auditflg = 1, $rtn = 'last', $oelog = true, $error = true)
  78     {
  79         $return = false;
  80         $result = false;
  81         $audit = array (
  82                     $patientid,
  83                     $activity,
  84                     $auditflg,
  85                     $status,
  86                     $action
  87             );
  88         try {
  89             $sql = "Select * From onsite_portal_activity As pa Where  pa.patient_id = ? And  pa.activity = ? And  pa.require_audit = ? " .
  90                                     "And pa.status = ? And  pa.pending_action = ? ORDER BY pa.date ASC LIMIT 1";
  91             $return = sqlStatementNoLog($sql, $audit);
  92             $result = true;
  93         } catch (Exception $e) {
  94             if ($error) {
  95                 $this->errorHandler($e, $sql, $audit);
  96             }
  97         }
  98
  99         if ($oelog) {
 100             EventAuditLogger::instance()->auditSQLEvent($sql, $result, $audit);
 101         }
 102
 103         if ($rtn == 'last') {
 104             return sqlFetchArray($return);
 105         } else {
 106             return $return;
 107         }
 108     }
 109     /**
 110      * Function portalAudit
 111      * All Portal audit Transactions log
 112      * Hoping to work both ends, patient and user, from one or most two tables
 113      *
 114      * @param String $sql
 115      *            SQL Query Statment for actions will execute sql as normal for cases
 116      *            user auth is not required.
 117      * @param array $params
 118      *            Parameters for actions
 119      * @param array $auditvals
 120      *            Parameters of audit
 121      * @param boolean $log
 122      *            openemr Logging True / False
 123      * @param boolean $error
 124      *            Error Display True / False
 125      * @param type audit array params for portal audits
 126      *         $audit = Array();
 127      *         $audit['patient_id']="";
 128      *         $audit['activity']="";
 129      *         $audit['require_audit']="";
 130      *         $audit['pending_action']="";
 131      *         $audit['action_taken']="";
 132      *         $audit['status']="";
 133      *         $audit['narrative']="";
 134      *         $audit['table_action']=""; //auth user action sql to run after review
 135      *         $audit['table_args']=""; //auth user action data to run after review
 136      *         $audit['action_user']="";
 137      *         $audit['action_taken_time']="";
 138      *         $audit['checksum']="";
 139      */
 140     public function portalAudit(string $type = null, string $rec = null, array $auditvals, $oelog = true, $error = true)
 141     {
 142         $return = false;
 143         $result = false;
 144         $audit = array ();
 145         if (!$type) {
 146             $type = 'insert';
 147         }
 148         if ($type != 'insert') {
 149             $audit['date'] = $auditvals['date'] ? $auditvals['date'] : date("Y-m-d H:i:s");
 150         }
 151
 152         $audit['patient_id'] = $auditvals['patient_id'] ? $auditvals['patient_id'] : $_SESSION['pid'];
 153         $audit['activity'] = $auditvals['activity'] ? $auditvals['activity'] : "";
 154         $audit['require_audit'] = $auditvals['require_audit'] ? $auditvals['require_audit'] : "";
 155         $audit['pending_action'] = $auditvals['pending_action'] ? $auditvals['pending_action'] : "";
 156         $audit['action_taken'] = $auditvals['action_taken'] ? $auditvals['action_taken'] : "";
 157         $audit['status'] = $auditvals['status'] ? $auditvals['status'] : "new";
 158         $audit['narrative'] = $auditvals['narrative'] ? $auditvals['narrative'] : "";
 159         $audit['table_action'] = $auditvals['table_action'] ? $auditvals['table_action'] : "";
 160         if ($auditvals['activity'] == 'profile') {
 161             $audit['table_args'] = serialize($auditvals['table_args']);
 162         } else {
 163             $audit['table_args'] = $auditvals['table_args'];
 164         }
 165
 166         $audit['action_user'] = $auditvals['action_user'] ? $auditvals['action_user'] : "";
 167         $audit['action_taken_time'] = $auditvals['action_taken_time'] ? $auditvals['action_taken_time'] : "";
 168         $audit['checksum'] = $auditvals['checksum'] ? $auditvals['checksum'] : "";
 169
 170         try {
 171             if ($type != 'update') {
 172                 $logsql = "INSERT INTO onsite_portal_activity" .
 173                         "( date, patient_id, activity, require_audit, pending_action, action_taken, status, narrative," .
 174                             "table_action, table_args, action_user, action_taken_time, checksum) " .
 175                                 "VALUES (NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 176             } else {
 177                 $logsql = "update onsite_portal_activity set date=?, patient_id=?, activity=?, require_audit=?," .
 178                         "            pending_action=?, action_taken=?,status=?, narrative=?, table_action=?, table_args=?," .
 179                                         "action_user=?, action_taken_time=?, checksum=? ";
 180                 $logsql .= "where id='" . add_escape_custom($rec) . "' And patient_id='" . add_escape_custom($audit['patient_id']) . "'";
 181             }
 182
 183             $return = sqlStatementNoLog($logsql, $audit);
 184             $result = true;
 185         } catch (Exception $e) {
 186             if ($error) {
 187                 $this->errorHandler($e, $logsql, $audit);
 188             }
 189         }
 190
 191         if ($oelog) {
 192             $this->portalLog('profile audit transaction', $audit['patient_id'], $logsql, $audit, $result, 'See portal audit activity');
 193             //auditSQLEvent( $logsql, $result, $audit );
 194         }
 195
 196         return $return;
 197     }
 198
 199     public function portalLog($event = '', $patient_id = null, $comments = "", $binds = '', $success = '1', $user_notes = '', $ccda_doc_id = 0)
 200     {
 201         $groupname = $GLOBALS['groupname'] ?? 'none';
 202         $user = $_SESSION['portal_username'] ?? $_SESSION['authUser'] ?? null;
 203         $log_from = isset($_SESSION['portal_username']) ? 'onsite-portal' : 'portal-dashboard';
 204         if (!isset($_SESSION['portal_username']) && !isset($_SESSION['authUser'])) {
 205             $log_from = 'portal-login';
 206         }
 207
 208         $user_notes .= isset($_SESSION['whereto']) ? (' Module:' . $_SESSION['whereto']) : "";
 209
 210         $processed_binds = "";
 211         if (is_array($binds)) {
 212             $first_loop = true;
 213             foreach ($binds as $value_bind) {
 214                 if ($first_loop) {
 215                     $processed_binds .= "'" . add_escape_custom($value_bind) . "'";
 216                     $first_loop = false;
 217                 } else {
 218                     $processed_binds .= ",'" . add_escape_custom($value_bind) . "'";
 219                 }
 220             }
 221
 222             if (! empty($processed_binds)) {
 223                 $processed_binds = "(" . $processed_binds . ")";
 224                 $comments .= " " . $processed_binds;
 225             }
 226         }
 227
 228         $this->portalNewEvent($event, $user, $groupname, $success, $comments, $patient_id, $log_from, $user_notes, $ccda_doc_id);
 229     }
 230     /**
 231      * Function errorHandler
 232      * All error display and log
 233      * Display the Error, Line and File
 234      * Same behavior of HelpfulDie fuction in OpenEMR
 235      * Path /library/sql.inc.php
 236      *
 237      * @param type $e
 238      * @param string $sql
 239      * @param array $binds
 240      */
 241     public function errorHandler($e, $sql, $binds = '')
 242     {
 243         $trace = $e->getTraceAsString();
 244         $nLast = strpos($trace, '[internal function]');
 245         $trace = substr($trace, 0, ( $nLast - 3 ));
 246         $logMsg = '';
 247         do {
 248             $logMsg .= "\r Exception: " . self::escapeHtml($e->getMessage());
 249         } while ($e = $e->getPrevious());
 250         /**
 251          * List all Params
 252          */
 253         $processedBinds = "";
 254         if (is_array($binds)) {
 255             $firstLoop = true;
 256             foreach ($binds as $valueBind) {
 257                 if ($firstLoop) {
 258                     $processedBinds .= "'" . $valueBind . "'";
 259                     $firstLoop = false;
 260                 } else {
 261                     $processedBinds .= ",'" . $valueBind . "'";
 262                 }
 263             }
 264
 265             if (! empty($processedBinds)) {
 266                 $processedBinds = "(" . $processedBinds . ")";
 267             }
 268         }
 269
 270         echo '<pre><span style="color: red;">';
 271         echo 'ERROR : ' . $logMsg;
 272         echo "\r\n";
 273         echo 'SQL statement : ' . self::escapeHtml($sql);
 274         echo self::escapeHtml($processedBinds);
 275         echo '</span></pre>';
 276         echo '<pre>';
 277         echo $trace;
 278         echo '</pre>';
 279         /**
 280          * Error Logging
 281          */
 282         $logMsg .= "\n SQL statement : $sql" . $processedBinds;
 283         $logMsg .= "\n $trace";
 284         error_log("ERROR: " . htmlspecialchars($logMsg, ENT_QUOTES), 0);
 285     }
 286     public function escapeHtml($string)
 287     {
 288         return htmlspecialchars($string, ENT_QUOTES);
 289     }
 290     /*
 291      * Retrive the data format from GLOBALS
 292      *
 293      * @param Date format set in GLOBALS
 294      * @return Date format in PHP
 295      */
 296     public function dateFormat($format)
 297     {
 298         if ($format == "0") {
 299             $date_format = 'yyyy/mm/dd';
 300         } elseif ($format == 1) {
 301             $date_format = 'mm/dd/yyyy';
 302         } elseif ($format == 2) {
 303             $date_format = 'dd/mm/yyyy';
 304         } else {
 305             $date_format = $format;
 306         }
 307
 308         return $date_format;
 309     }
 310     /**
 311      * fixDate - Date Conversion Between Different Formats
 312      *
 313      * @param String $input_date
 314      *            Date to be converted
 315      * @param String $date_format
 316      *            Target Date Format
 317      */
 318     public function fixDate($input_date, $output_format = null, $input_format = null)
 319     {
 320         if (! $input_date) {
 321             return;
 322         }
 323
 324         $input_date = preg_replace('/T|Z/', ' ', $input_date);
 325
 326         $temp = explode(' ', $input_date); // split using space and consider the first portion, in case of date with time
 327         $input_date = $temp[0];
 328
 329         $output_format = ApplicationTable::dateFormat($output_format);
 330         $input_format = ApplicationTable::dateFormat($input_format);
 331
 332         preg_match("/[^ymd]/", $output_format, $date_seperator_output);
 333         $seperator_output = $date_seperator_output[0];
 334         $output_date_arr = explode($seperator_output, $output_format);
 335
 336         preg_match("/[^ymd]/", $input_format, $date_seperator_input);
 337         $seperator_input = $date_seperator_input[0];
 338         $input_date_array = explode($seperator_input, $input_format);
 339
 340         preg_match("/[^1234567890]/", $input_date, $date_seperator_input);
 341         $seperator_input = $date_seperator_input[0];
 342         $input_date_arr = explode($seperator_input, $input_date);
 343
 344         foreach ($output_date_arr as $key => $format) {
 345             $index = array_search($format, $input_date_array);
 346             $output_date_arr[$key] = $input_date_arr[$index];
 347         }
 348
 349         $output_date = implode($seperator_output, $output_date_arr);
 350
 351         $output_date = $temp[1] ? $output_date . " " . $temp[1] : $output_date; // append the time, if exists, with the new formatted date
 352         return $output_date;
 353     }
 354
 355     /*
 356      * Using generate id function from OpenEMR sql.inc.php library file
 357      * @param string $seqname table name containing sequence (default is adodbseq)
 358      * @param integer $startID id to start with for a new sequence (default is 1)
 359      * @return integer returns the sequence integer
 360      */
 361     public function generateSequenceID()
 362     {
 363         return generate_id();
 364     }
 365
 366     public function portalNewEvent($event, $user, $groupname, $success, $comments = "", $patient_id = null, $log_from = '', $user_notes = "", $ccda_doc_id = 0)
 367     {
 368         EventAuditLogger::instance()->recordLogItem($success, $event, $user, $groupname, $comments, $patient_id, null, $log_from, null, $ccda_doc_id, $user_notes);
 369     }
 370 }// app query class