portal/lib/appsql.class.php

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