portal/lib/appsql.class.php

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