src/Billing/BillingReport.php

   1 <?php
   2
   3 /**
   4  * This provides helper functions for the billing report.
   5  *
   6  * @package   OpenEMR
   7  * @link      http://www.open-emr.org
   8  * @author    Eldho Chacko <eldho@zhservices.com>
   9  * @author    Paul Simon K <paul@zhservices.com>
  10  * @author    Stephen Waite <stephen.waite@cmsvt.com>
  11  * @author    Brady Miller <brady.g.miller@gmail.com>
  12  * @author    Rod Roark <rod@sunsetsystems.com>
  13  * @copyright Copyright (c) 2010 Z&H Consultancy Services Private Limited <sam@zhservices.com>
  14  * @copyright Copyright (c) 2018-2019 Stephen Waite <stephen.waite@cmsvt.com>
  15  * @copyright Copyright (c) 2019 Brady Miller <brady.g.miller@gmail.com>
  16  * @copyright Copyright (c) 2020 Rod Roark <rod@sunsetsystems.com>
  17  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  18  */
  19
  20 namespace OpenEMR\Billing;
  21
  22 class BillingReport
  23 {
  24     public static function generateTheQueryPart()
  25     {
  26         global $query_part, $query_part2, $billstring, $auth;
  27         //Search Criteria section.
  28         $billstring = '';
  29         $auth = '';
  30         $query_part = '';
  31         $query_part2 = '';
  32         if (isset($_REQUEST['final_this_page_criteria'])) {
  33             foreach ($_REQUEST['final_this_page_criteria'] as $criteria_key => $criteria_value) {
  34                 $criteria_value = self::prepareSearchItem($criteria_value); // this escapes for sql
  35                 $SplitArray = array();
  36                 //---------------------------------------------------------
  37                 if (strpos($criteria_value, "billing.billed = '1'") !== false) {
  38                     $billstring .= ' AND ' . $criteria_value;
  39                 } elseif (strpos($criteria_value, "billing.billed = '0'") !== false) {
  40                     //3 is an error condition
  41                     $billstring .= ' AND ' . "(billing.billed is null or billing.billed = '0' or (billing.billed = '1' and billing.bill_process = '3'))";
  42                 } elseif (strpos($criteria_value, "billing.billed = '7'") !== false) {
  43                     $billstring .= ' AND ' . "billing.bill_process = '7'";
  44                 } elseif (strpos($criteria_value, "billing.id = 'null'") !== false) {
  45                     $billstring .= ' AND ' . "billing.id is null";
  46                 } elseif (strpos($criteria_value, "billing.id = 'not null'") !== false) {
  47                     $billstring .= ' AND ' . "billing.id is not null";
  48                 } elseif (strpos($criteria_value, "patient_data.fname") !== false) {
  49                     $SplitArray = explode(' like ', $criteria_value);
  50                     $query_part .= " AND ($criteria_value or patient_data.lname like " . $SplitArray[1] . ")";
  51                 } elseif (strpos($criteria_value, "billing.authorized") !== false) {
  52                     $auth = ' AND ' . $criteria_value;
  53                 } elseif (strpos($criteria_value, "form_encounter.pid") !== false) {//comes like '781,780'
  54                     $SplitArray = explode(" = '", $criteria_value);//comes like 781,780'
  55                     $SplitArray[1] = substr($SplitArray[1], 0, -1);//comes like 781,780
  56                     $query_part .= ' AND form_encounter.pid in (' . $SplitArray[1] . ')';
  57                     $query_part2 .= ' AND pid in (' . $SplitArray[1] . ')';
  58                 } elseif (strpos($criteria_value, "form_encounter.encounter") !== false) {//comes like '781,780'
  59                     $SplitArray = explode(" = '", $criteria_value);//comes like 781,780'
  60                     $SplitArray[1] = substr($SplitArray[1], 0, -1);//comes like 781,780
  61                     $query_part .= ' AND form_encounter.encounter in (' . $SplitArray[1] . ')';
  62                 } elseif (strpos($criteria_value, "insurance_data.provider = '1'") !== false) {
  63                     $query_part .= ' AND ' . "insurance_data.provider > '0' and (insurance_data.date <= form_encounter.date OR insurance_data.date IS NULL)";
  64                 } elseif (strpos($criteria_value, "insurance_data.provider = '0'") !== false) {
  65                     $query_part .= ' AND ' . "(insurance_data.provider = '0' or insurance_data.date > form_encounter.date)";
  66                 } else {
  67                     $query_part .= ' AND ' . $criteria_value;
  68                 }
  69             }
  70         }
  71     }
  72
  73     //date must be in nice format (e.g. 2002-07-11)
  74     public static function getBillsBetween(
  75         $code_type,
  76         $cols = "id,date,pid,code_type,code,user,authorized,x12_partner_id"
  77     ) {
  78         self::generateTheQueryPart();
  79         global $query_part, $billstring, $auth;
  80         // Selecting by the date in the billing table is wrong, because that is
  81         // just the data entry date; instead we want to go by the encounter date
  82         // which is the date in the form_encounter table.
  83         //
  84         $sql = "SELECT distinct form_encounter.date AS enc_date, form_encounter.pid AS enc_pid, " .
  85             "form_encounter.encounter AS enc_encounter, form_encounter.provider_id AS enc_provider_id, billing.* " .
  86             "FROM form_encounter " .
  87             "LEFT OUTER JOIN billing ON " .
  88             "billing.encounter = form_encounter.encounter AND " .
  89             "billing.pid = form_encounter.pid AND " .
  90             "billing.code_type LIKE ? AND " .
  91             "billing.activity = 1 " .
  92             "LEFT OUTER JOIN patient_data on patient_data.pid = form_encounter.pid " .
  93             "LEFT OUTER JOIN claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter " .
  94             "LEFT OUTER JOIN insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' " .
  95             "WHERE 1=1 $query_part  " . " $auth " . " $billstring " .
  96             "ORDER BY form_encounter.provider_id, form_encounter.encounter, form_encounter.pid, billing.code_type, billing.code ASC";
  97         //echo $sql;
  98         $res = sqlStatement($sql, array($code_type));
  99         $all = false;
 100         for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 101             $all[$iter] = $row;
 102         }
 103
 104         return $all;
 105     }
 106
 107     public static function getBillsBetweenReport(
 108         $code_type,
 109         $cols = "id,date,pid,code_type,code,user,authorized,x12_partner_id"
 110     ) {
 111         self::generateTheQueryPart();
 112         global $query_part, $query_part2, $billstring, $auth;
 113         // Selecting by the date in the billing table is wrong, because that is
 114         // just the data entry date; instead we want to go by the encounter date
 115         // which is the date in the form_encounter table.
 116         //
 117         $sql = "SELECT distinct form_encounter.date AS enc_date, form_encounter.pid AS enc_pid, " .
 118             "form_encounter.encounter AS enc_encounter, form_encounter.provider_id AS enc_provider_id, billing.* " .
 119             "FROM form_encounter " .
 120             "LEFT OUTER JOIN billing ON " .
 121             "billing.encounter = form_encounter.encounter AND " .
 122             "billing.pid = form_encounter.pid AND " .
 123             "billing.code_type LIKE ? AND " .
 124             "billing.activity = 1 " .
 125             "LEFT OUTER JOIN patient_data on patient_data.pid = form_encounter.pid " .
 126             "LEFT OUTER JOIN claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter " .
 127             "LEFT OUTER JOIN insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' " .
 128             "WHERE 1=1 $query_part  " . " $auth " . " $billstring " .
 129             "ORDER BY form_encounter.encounter, form_encounter.pid, billing.code_type, billing.code ASC";
 130         //echo $sql;
 131         $res = sqlStatement($sql, array($code_type));
 132         $all = false;
 133         for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 134             $all[$iter] = $row;
 135         }
 136
 137         $query = sqlStatement("SELECT pid, 'COPAY' AS code_type, pay_amount AS code, date(post_time) AS date " .
 138             "FROM ar_activity where deleted IS NULL $query_part2 and payer_type=0 and account_code='PCP'");
 139         //new fees screen copay gives account_code='PCP' openemr payment screen copay gives code='CO-PAY'
 140         for ($iter; $row = sqlFetchArray($query); $iter++) {
 141             $all[$iter] = $row;
 142         }
 143
 144         return $all;
 145     }
 146
 147     public static function getBillsListBetween(
 148         $code_type,
 149         $cols = "billing.id, form_encounter.date, billing.pid, billing.code_type, billing.code, billing.user"
 150     ) {
 151         self::generateTheQueryPart();
 152         global $query_part, $billstring, $auth;
 153         // See above comment in self::getBillsBetween().
 154         //
 155         $sql = "select distinct $cols " .
 156             "from form_encounter, billing, patient_data, claims, insurance_data where " .
 157             "billing.encounter = form_encounter.encounter and " .
 158             "billing.pid = form_encounter.pid and " .
 159             "patient_data.pid = form_encounter.pid and " .
 160             "claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter and " .
 161             "insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' " .
 162             $auth .
 163             $billstring . $query_part . " and " .
 164             "billing.code_type like ? and " .
 165             "billing.activity = 1 " .
 166             "order by billing.pid, billing.date ASC";
 167
 168         $res = sqlStatement($sql, array($code_type));
 169         $array = array();
 170         for ($iter = 0; $row = sqlFetchArray($res); $iter++) {
 171             array_push($array, $row["id"]);
 172         }
 173
 174         return $array;
 175     }
 176
 177     public static function billCodesList($list, $skip = [])
 178     {
 179         if (empty($list)) {
 180             return;
 181         }
 182
 183         $sqlBindArray = array_diff($list, $skip);
 184         if (empty($sqlBindArray)) {
 185             return;
 186         }
 187
 188         $in = str_repeat('?,', count($sqlBindArray) - 1) . '?';
 189         sqlStatement("update billing set billed=1 where id in ($in)", $sqlBindArray);
 190
 191         return;
 192     }
 193
 194     public static function returnOFXSql()
 195     {
 196         self::generateTheQueryPart();
 197         global $query_part, $billstring, $auth;
 198
 199         $sql = "SELECT distinct billing.*, concat(patient_data.fname, ' ', patient_data.lname) as name from billing "
 200             . "join patient_data on patient_data.pid = billing.pid "
 201             . "join form_encounter on "
 202             . "billing.encounter = form_encounter.encounter AND "
 203             . "billing.pid = form_encounter.pid "
 204             . "join claims on claims.patient_id = form_encounter.pid and claims.encounter_id = form_encounter.encounter "
 205             . "join insurance_data on insurance_data.pid = form_encounter.pid and insurance_data.type = 'primary' "
 206             . "where billed = '1' "
 207             . "$auth "
 208             . "$billstring  $query_part  "
 209             . "order by billing.pid,billing.encounter";
 210
 211         return $sql;
 212     }
 213
 214     public static function prepareSearchItem($SearchItem)
 215     {
 216         $SplitArray = explode(' like ', $SearchItem);
 217         if (isset($SplitArray[1])) {
 218             $SplitArray[1] = substr($SplitArray[1], 0, -1);
 219             $SplitArray[1] = substr($SplitArray[1], 1);
 220             $SearchItem = $SplitArray[0] . ' like ' . "'" . add_escape_custom($SplitArray[1]) . "'";
 221         } else {
 222             $SplitArray = explode(' = ', $SearchItem);
 223             if (isset($SplitArray[1])) {
 224                 $SplitArray[1] = substr($SplitArray[1], 0, -1);
 225                 $SplitArray[1] = substr($SplitArray[1], 1);
 226                 $SearchItem = $SplitArray[0] . ' = ' . "'" . add_escape_custom($SplitArray[1]) . "'";
 227             }
 228         }
 229
 230         return($SearchItem);
 231     }
 232
 233     //Parses the database value and prepares for display.
 234     public static function buildArrayForReport($Query)
 235     {
 236         $array_data = array();
 237         $res = sqlStatement($Query);
 238         while ($row = sqlFetchArray($res)) {
 239             $array_data[$row['id']] = attr($row['name']);
 240         }
 241
 242         return $array_data;
 243     }
 244
 245     //The criteria  "Insurance Company" is coded here.The ajax one
 246     public static function insuranceCompanyDisplay()
 247     {
 248
 249         // TPS = This Page Search
 250         global $TPSCriteriaDisplay, $TPSCriteriaKey, $TPSCriteriaIndex, $web_root;
 251
 252         echo '<table width="140" border="0" cellspacing="0" cellpadding="0">' .
 253             '<tr>' .
 254             '<td width="140" colspan="2">' .
 255             '<iframe id="frame_to_hide" style="position:absolute;display:none; width:240px; height:100px" frameborder=0' .
 256             'scrolling=no marginwidth=0 src="" marginheight=0>hello</iframe>' .
 257             '<input type="hidden" id="hidden_ajax_close_value" value="' . attr($_POST['type_code']) . '" /><input name="type_code"  id="type_code" class="text "' .
 258             'style=" width:140px;"  title="' . xla("Type Id or Name.3 characters minimum (including spaces).") . '"' .
 259             'onfocus="hide_frame_to_hide();appendOptionTextCriteria(' . attr_js($TPSCriteriaDisplay[$TPSCriteriaIndex]) . ',' .
 260             '' . attr_js($TPSCriteriaKey[$TPSCriteriaIndex]) . ',' .
 261             'document.getElementById(\'type_code\').value,document.getElementById(\'div_insurance_or_patient\').innerHTML,' .
 262             '\' = \',' .
 263             '\'text\')" onblur="show_frame_to_hide()" onKeyDown="PreventIt(event)" value="' . attr($_POST['type_code']) . '"  autocomplete="off"   /><br />' .
 264             '<!--onKeyUp="ajaxFunction(event,\'non\',\'search_payments.php\');"-->' .
 265             '<div id="ajax_div_insurance_section">' .
 266             '<div id="ajax_div_insurance_error">            </div>' .
 267             '<div id="ajax_div_insurance" style="display:none;"></div>' .
 268             '</div>' .
 269             '</div>        </td>' .
 270             '</tr>' .
 271             '<tr height="5"><td colspan="2"></td></tr>' .
 272             '<tr>' .
 273             '<td><div  name="div_insurance_or_patient" id="div_insurance_or_patient" class="text"  style="border:1px solid black; padding-left:5px; width:50px; height:17px;">' . attr($_POST['hidden_type_code']) . '</div><input type="hidden" name="description"  id="description" /></td>' .
 274             '<td><a href="#" onClick="CleanUpAjax(' . attr_js($TPSCriteriaDisplay[$TPSCriteriaIndex]) . ',' .
 275             attr_js($TPSCriteriaKey[$TPSCriteriaIndex]) . ',\' = \')"><img src="' . $web_root . '/interface/pic/Clear.gif" border="0" /></a></td>' .
 276             '</tr>' .
 277             '</table>' .
 278             '<input type="hidden" name="hidden_type_code" id="hidden_type_code" value="' . attr($_POST['hidden_type_code']) . '"/>';
 279     }
 280 }