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