interface/billing/sl_receipts_report.php

   1 <?php
   2 /**
   3  * Report - Cash receipts by Provider
   4  *
   5  * This module was written for one of my clients to report on cash
   6  * receipts by practitioner.  It is not as complete as it should be
   7  * but I wanted to make the code available to the project because
   8  * many other practices have this same need. - rod@sunsetsystems.com
   9  *
  10  * Copyright (C) 2006-2010 Rod Roark <rod@sunsetsystems.com>
  11  *
  12  * This program is free software; you can redistribute it and/or
  13  * modify it under the terms of the GNU General Public License
  14  * as published by the Free Software Foundation; either version 2
  15  * of the License, or (at your option) any later version.
  16  *
  17  * @package OpenEMR
  18  * @author  Rod Roark <rod@sunsetsystems.com>
  19  * @link    http://open-emr.org
  20  */
  21
  22 require_once('../globals.php');
  23 require_once($GLOBALS['srcdir'].'/patient.inc');
  24 require_once($GLOBALS['srcdir'].'/sql-ledger.inc');
  25 require_once($GLOBALS['srcdir'].'/acl.inc');
  26 require_once($GLOBALS['srcdir'].'/formatting.inc.php');
  27 require_once($GLOBALS['srcdir'].'/options.inc.php');
  28 require_once($GLOBALS['srcdir'].'/formdata.inc.php');
  29 require_once($GLOBALS['fileroot'].'/custom/code_types.inc.php');
  30
  31   // This determines if a particular procedure code corresponds to receipts
  32   // for the "Clinic" column as opposed to receipts for the practitioner.  Each
  33   // practice will have its own policies in this regard, so you'll probably
  34   // have to customize this function.  If you use the "fee sheet" encounter
  35   // form then the code below may work for you.
  36   //
  37   require_once('../forms/fee_sheet/codes.php');
  38   function is_clinic($code) {
  39     global $bcodes;
  40     $i = strpos($code, ':');
  41     if ($i) $code = substr($code, 0, $i);
  42     return ($bcodes['CPT4'][xl('Lab')][$code]     ||
  43       $bcodes['CPT4'][xl('Immunizations')][$code] ||
  44       $bcodes['HCPCS'][xl('Therapeutic Injections')][$code]);
  45   }
  46
  47   function bucks($amount) {
  48     if ($amount) echo oeFormatMoney($amount);
  49   }
  50
  51   if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
  52
  53   $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
  54
  55   if (!$INTEGRATED_AR) {
  56     SLConnect();
  57     $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
  58     if ($sl_err) die($sl_err);
  59   }
  60
  61   $form_use_edate  = $_POST['form_use_edate'];
  62
  63   $form_proc_codefull = trim($_POST['form_proc_codefull']);
  64   // Parse the code type and the code from <code_type>:<code>
  65   $tmp_code_array = explode(':',$form_proc_codefull);
  66   $form_proc_codetype = $tmp_code_array[0];
  67   $form_proc_code = $tmp_code_array[1];
  68
  69   $form_dx_codefull  = trim($_POST['form_dx_codefull']);
  70   // Parse the code type and the code from <code_type>:<code>
  71   $tmp_code_array = explode(':',$form_dx_codefull);
  72   $form_dx_codetype = $tmp_code_array[0];
  73   $form_dx_code = $tmp_code_array[1];
  74
  75   $form_procedures = empty($_POST['form_procedures']) ? 0 : 1;
  76   $form_from_date  = fixDate($_POST['form_from_date'], date('Y-m-01'));
  77   $form_to_date    = fixDate($_POST['form_to_date'], date('Y-m-d'));
  78   $form_facility   = $_POST['form_facility'];
  79 ?>
  80 <html>
  81 <head>
  82 <?php if (function_exists('html_header_show')) html_header_show(); ?>
  83 <style type="text/css">
  84 /* specifically include & exclude from printing */
  85 @media print {
  86     #report_parameters {
  87         visibility: hidden;
  88         display: none;
  89     }
  90     #report_parameters_daterange {
  91         visibility: visible;
  92         display: inline;
  93     }
  94     #report_results {
  95        margin-top: 30px;
  96     }
  97 }
  98
  99 /* specifically exclude some from the screen */
 100 @media screen {      N
 101     #report_parameters_daterange {
 102         visibility: hidden;
 103         display: none;
 104     }
 105 }
 106 </style>
 107
 108 <script type="text/javascript" src="<?php echo $GLOBALS['webroot']; ?>/library/dialog.js"></script>
 109 <script language="JavaScript">
 110 // This is for callback by the find-code popup.
 111 // Erases the current entry
 112 // The target element is set by the find-code popup
 113 //  (this allows use of this in multiple form elements on the same page)
 114 function set_related_target(codetype, code, selector, codedesc, target_element) {
 115  var f = document.forms[0];
 116  var s = f[target_element].value;
 117  if (code) {
 118   s = codetype + ':' + code;
 119  } else {
 120   s = '';
 121  }
 122  f[target_element].value = s;
 123 }
 124
 125 // This invokes the find-code (procedure/service codes) popup.
 126 function sel_procedure() {
 127  dlgopen('../patient_file/encounter/find_code_popup.php?target_element=form_proc_codefull&codetype=<?php echo attr(collect_codetypes("procedure","csv")) ?>', '_blank', 500, 400);
 128 }
 129
 130 // This invokes the find-code (diagnosis codes) popup.
 131 function sel_diagnosis() {
 132  dlgopen('../patient_file/encounter/find_code_popup.php?target_element=form_dx_codefull&codetype=<?php echo attr(collect_codetypes("diagnosis","csv")) ?>', '_blank', 500, 400);
 133 }
 134
 135 </script>
 136
 137 <title><?xl('Cash Receipts by Provider','e')?></title>
 138 </head>
 139
 140 <body class="body_top">
 141
 142 <span class='title'><?php xl('Report','e'); ?> - <?php xl('Cash Receipts by Provider','e'); ?></span>
 143
 144 <form method='post' action='sl_receipts_report.php' id='theform'>
 145
 146 <div id="report_parameters">
 147
 148 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 149
 150 <table>
 151  <tr>
 152   <td width='660px'>
 153         <div style='float:left'>
 154
 155         <table class='text'>
 156                 <tr>
 157                         <td class='label'>
 158                                 <?php xl('Facility','e'); ?>:
 159                         </td>
 160                         <td>
 161                         <?php dropdown_facility(strip_escape_custom($form_facility), 'form_facility'); ?>
 162                         </td>
 163                         <td class='label'>
 164                            <?php xl('Provider','e'); ?>:
 165                         </td>
 166                         <td>
 167                                 <?php
 168                                 if (acl_check('acct', 'rep_a')) {
 169                                         // Build a drop-down list of providers.
 170                                         //
 171                                         $query = "select id, lname, fname from users where " .
 172                                                 "authorized = 1 order by lname, fname";
 173                                         $res = sqlStatement($query);
 174                                         echo "   &nbsp;<select name='form_doctor'>\n";
 175                                         echo "    <option value=''>-- " . xl('All Providers', 'e') . " --\n";
 176                                         while ($row = sqlFetchArray($res)) {
 177                                                 $provid = $row['id'];
 178                                                 echo "    <option value='$provid'";
 179                                                 if ($provid == $_POST['form_doctor']) echo " selected";
 180                                                 echo ">" . $row['lname'] . ", " . $row['fname'] . "\n";
 181                                         }
 182                                         echo "   </select>\n";
 183                                 } else {
 184                                         echo "<input type='hidden' name='form_doctor' value='" . $_SESSION['authUserID'] . "'>";
 185                                 }
 186                         ?>
 187                         </td>
 188                         <td>
 189                            <select name='form_use_edate'>
 190                                 <option value='0'><?php xl('Payment Date','e'); ?></option>
 191                                 <option value='1'<?php if ($form_use_edate) echo ' selected' ?>><?php xl('Invoice Date','e'); ?></option>
 192                            </select>
 193                         </td>
 194                 </tr>
 195                 <tr>
 196                         <td class='label'>
 197                            <?php xl('From','e'); ?>:
 198                         </td>
 199                         <td>
 200                            <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php  echo $form_from_date; ?>'
 201                                 title='Date of appointments mm/dd/yyyy' >
 202                            <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 203                                 id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 204                                 title='<?php xl('Click here to choose a date','e'); ?>'>
 205                         </td>
 206                         <td class='label'>
 207                            <?php xl('To','e'); ?>:
 208                         </td>
 209                         <td>
 210                            <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php  echo $form_to_date; ?>'
 211                                 title='Optional end date mm/dd/yyyy' >
 212                            <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 213                                 id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 214                                 title='<?php xl('Click here to choose a date','e'); ?>'>
 215                         </td>
 216                         <td>&nbsp;</td>
 217                 </tr>
 218                 <tr>
 219                         <td>
 220                                 <?php if (!$GLOBALS['simplified_demographics']) echo '&nbsp;' . xl('Procedure/Service', 'e') . ':'; ?>
 221                         </td>
 222                         <td>
 223                            <input type='text' name='form_proc_codefull' size='11' value='<?php echo $form_proc_codefull; ?>' onclick='sel_procedure()'
 224                                 title='<?php xl('Optional procedure/service code','e'); ?>'
 225                                 <?php if ($GLOBALS['simplified_demographics']) echo "style='display:none'"; ?>>
 226                         </td>
 227
 228                         <td>
 229                            <?php if (!$GLOBALS['simplified_demographics']) echo '&nbsp;' . xl('Diagnosis', 'e') . ':'; ?>
 230                         </td>
 231                         <td>
 232                            <input type='text' name='form_dx_codefull' size='11' value='<?php echo $form_dx_codefull; ?>' onclick='sel_diagnosis()'
 233                                 title='<?php xl('Enter a diagnosis code to exclude all invoices not containing it','e'); ?>'
 234                                 <?php if ($GLOBALS['simplified_demographics']) echo "style='display:none'"; ?>>
 235                         </td>
 236
 237                         <td>
 238                            <input type='checkbox' name='form_details' value='1'<?php if ($_POST['form_details']) echo " checked"; ?>><?xl('Details','e')?>
 239                            <input type='checkbox' name='form_procedures' value='1'<?php if ($form_procedures) echo " checked"; ?>><?xl('Procedures','e')?>
 240                         </td>
 241                 </tr>
 242         </table>
 243
 244         </div>
 245
 246   </td>
 247   <td align='left' valign='middle' height="100%">
 248         <table style='border-left:1px solid; width:100%; height:100%' >
 249                 <tr>
 250                         <td>
 251                                 <div style='margin-left:15px'>
 252                                         <a href='#' class='css_button' onclick='$("#form_refresh").attr("value","true"); $("#theform").submit();'>
 253                                         <span>
 254                                                 <?php xl('Submit','e'); ?>
 255                                         </span>
 256                                         </a>
 257
 258                                         <?php if ($_POST['form_refresh']) { ?>
 259                                         <a href='#' class='css_button' onclick='window.print()'>
 260                                                 <span>
 261                                                         <?php xl('Print','e'); ?>
 262                                                 </span>
 263                                         </a>
 264                                         <?php } ?>
 265                                 </div>
 266                         </td>
 267                 </tr>
 268         </table>
 269   </td>
 270  </tr>
 271 </table>
 272 </div>
 273
 274 <?php
 275  if ($_POST['form_refresh']) {
 276 ?>
 277 <div id="report_results">
 278 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 279  <thead>
 280   <th>
 281    <?php xl('Practitioner','e') ?>
 282   </th>
 283   <th>
 284    <?php xl('Date','e') ?>
 285   </th>
 286 <?php if ($form_procedures) { ?>
 287   <th>
 288    <?php xl('Invoice','e') ?>
 289   </th>
 290 <?php } ?>
 291 <?php if ($form_proc_codefull) { ?>
 292   <th align='right'>
 293    <?php xl('InvAmt','e') ?>
 294   </th>
 295 <?php } ?>
 296 <?php if ($form_proc_codefull) { ?>
 297   <th>
 298    <?php xl('Insurance','e') ?>
 299   </th>
 300 <?php } ?>
 301 <?php if ($form_procedures) { ?>
 302   <th>
 303    <?php xl('Procedure','e') ?>
 304   </th>
 305   <th align="right">
 306    <?php xl('Prof.','e') ?>
 307   </th>
 308   <th align="right">
 309    <?php xl('Clinic','e') ?>
 310   </th>
 311 <?php } else { ?>
 312   <th align="right">
 313    <?php xl('Received','e') ?>
 314   </th>
 315 <?php } ?>
 316  </thead>
 317 <?php
 318   if ($_POST['form_refresh']) {
 319     $form_doctor = $_POST['form_doctor'];
 320     $arows = array();
 321
 322     if ($INTEGRATED_AR) {
 323       $ids_to_skip = array();
 324       $irow = 0;
 325
 326       // Get copays.  These will be ignored if a CPT code was specified.
 327       //
 328       if (!$form_proc_code || !$form_proc_codetype) {
 329         /*************************************************************
 330         $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.modifier, " .
 331           "fe.date, fe.id AS trans_id, u.id AS docid " .
 332           "FROM billing AS b " .
 333           "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 334           "JOIN forms AS f ON f.pid = b.pid AND f.encounter = b.encounter AND f.formdir = 'newpatient' " .
 335           "LEFT OUTER JOIN users AS u ON u.username = f.user " .
 336           "WHERE b.code_type = 'COPAY' AND b.activity = 1 AND " .
 337           "fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_to_date 23:59:59'";
 338         // If a facility was specified.
 339         if ($form_facility) {
 340           $query .= " AND fe.facility_id = '$form_facility'";
 341         }
 342         // If a doctor was specified.
 343         if ($form_doctor) {
 344           $query .= " AND u.id = '$form_doctor'";
 345         }
 346         *************************************************************/
 347         $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.modifier, " .
 348           "fe.date, fe.id AS trans_id, fe.provider_id AS docid, fe.invoice_refno " .
 349           "FROM billing AS b " .
 350           "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 351           "WHERE b.code_type = 'COPAY' AND b.activity = 1 AND " .
 352           "fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_to_date 23:59:59'";
 353         // If a facility was specified.
 354         if ($form_facility) {
 355           $query .= " AND fe.facility_id = '$form_facility'";
 356         }
 357         // If a doctor was specified.
 358         if ($form_doctor) {
 359           $query .= " AND fe.provider_id = '$form_doctor'";
 360         }
 361         /************************************************************/
 362         //
 363         $res = sqlStatement($query);
 364         while ($row = sqlFetchArray($res)) {
 365           $trans_id = $row['trans_id'];
 366           $thedate = substr($row['date'], 0, 10);
 367           $patient_id = $row['pid'];
 368           $encounter_id = $row['encounter'];
 369           //
 370           if (!empty($ids_to_skip[$trans_id])) continue;
 371           //
 372           // If a diagnosis code was given then skip any invoices without
 373           // that diagnosis.
 374           if ($form_dx_code && $form_dx_codetype) {
 375             $tmp = sqlQuery("SELECT count(*) AS count FROM billing WHERE " .
 376               "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 377               "code_type = '$form_dx_codetype' AND code LIKE '$form_dx_code' AND " .
 378               "activity = 1");
 379             if (empty($tmp['count'])) {
 380               $ids_to_skip[$trans_id] = 1;
 381               continue;
 382             }
 383           }
 384           //
 385           $key = sprintf("%08u%s%08u%08u%06u", $row['docid'], $thedate,
 386             $patient_id, $encounter_id, ++$irow);
 387           $arows[$key] = array();
 388           $arows[$key]['transdate'] = $thedate;
 389           $arows[$key]['amount'] = $row['fee'];
 390           $arows[$key]['docid'] = $row['docid'];
 391           $arows[$key]['project_id'] = 0;
 392           $arows[$key]['memo'] = '';
 393           $arows[$key]['invnumber'] = "$patient_id.$encounter_id";
 394           $arows[$key]['irnumber'] = $row['invoice_refno'];
 395         } // end while
 396       } // end copays (not $form_proc_code)
 397
 398       // Get ar_activity (having payments), form_encounter, forms, users, optional ar_session
 399       /***************************************************************
 400       $query = "SELECT a.pid, a.encounter, a.post_time, a.code, a.modifier, a.pay_amount, " .
 401         "fe.date, fe.id AS trans_id, u.id AS docid, s.deposit_date, s.payer_id " .
 402         "FROM ar_activity AS a " .
 403         "JOIN form_encounter AS fe ON fe.pid = a.pid AND fe.encounter = a.encounter " .
 404         "JOIN forms AS f ON f.pid = a.pid AND f.encounter = a.encounter AND f.formdir = 'newpatient' " .
 405         "LEFT OUTER JOIN users AS u ON u.username = f.user " .
 406         "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 407         "WHERE a.pay_amount != 0 AND ( " .
 408         "a.post_time >= '$form_from_date 00:00:00' AND a.post_time <= '$form_to_date 23:59:59' " .
 409         "OR fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_to_date 23:59:59' " .
 410         "OR s.deposit_date >= '$form_from_date' AND s.deposit_date <= '$form_to_date' )";
 411       // If a procedure code was specified.
 412       if ($form_proc_code) $query .= " AND a.code = '$form_proc_code'";
 413       // If a facility was specified.
 414       if ($form_facility) $query .= " AND fe.facility_id = '$form_facility'";
 415       // If a doctor was specified.
 416       if ($form_doctor) $query .= " AND u.id = '$form_doctor'";
 417       ***************************************************************/
 418       $query = "SELECT a.pid, a.encounter, a.post_time, a.code, a.modifier, a.pay_amount, " .
 419         "fe.date, fe.id AS trans_id, fe.provider_id AS docid, fe.invoice_refno, s.deposit_date, s.payer_id, " .
 420         "b.provider_id " .
 421         "FROM ar_activity AS a " .
 422         "JOIN form_encounter AS fe ON fe.pid = a.pid AND fe.encounter = a.encounter " .
 423         "LEFT OUTER JOIN ar_session AS s ON s.session_id = a.session_id " .
 424         "LEFT OUTER JOIN billing AS b ON b.pid = a.pid AND b.encounter = a.encounter AND " .
 425         "b.code = a.code AND b.modifier = a.modifier AND b.activity = 1 AND " .
 426         "b.code_type != 'COPAY' AND b.code_type != 'TAX' " .
 427         "WHERE a.pay_amount != 0 AND ( " .
 428         "a.post_time >= '$form_from_date 00:00:00' AND a.post_time <= '$form_to_date 23:59:59' " .
 429         "OR fe.date >= '$form_from_date 00:00:00' AND fe.date <= '$form_to_date 23:59:59' " .
 430         "OR s.deposit_date >= '$form_from_date' AND s.deposit_date <= '$form_to_date' )";
 431       // If a procedure code was specified.
 432       // Support code type if it is in the ar_activity table. Note it is not always included, so
 433       // also support a blank code type in ar_activity table.
 434       if ($form_proc_codetype && $form_proc_code) $query .= " AND (a.code_type = '$form_proc_codetype' OR a.code_type = '') AND a.code = '$form_proc_code'";
 435       // If a facility was specified.
 436       if ($form_facility) $query .= " AND fe.facility_id = '$form_facility'";
 437       // If a doctor was specified.
 438       if ($form_doctor) {
 439         $query .= " AND ( b.provider_id = '$form_doctor' OR " .
 440           "( ( b.provider_id IS NULL OR b.provider_id = 0 ) AND " .
 441           "fe.provider_id = '$form_doctor' ) )";
 442       }
 443       /**************************************************************/
 444       //
 445       $res = sqlStatement($query);
 446       while ($row = sqlFetchArray($res)) {
 447         $trans_id = $row['trans_id'];
 448         $patient_id = $row['pid'];
 449         $encounter_id = $row['encounter'];
 450         //
 451         if (!empty($ids_to_skip[$trans_id])) continue;
 452         //
 453         if ($form_use_edate) {
 454           $thedate = substr($row['date'], 0, 10);
 455         } else {
 456           if (!empty($row['deposit_date']))
 457             $thedate = $row['deposit_date'];
 458           else
 459             $thedate = substr($row['post_time'], 0, 10);
 460         }
 461         if (strcmp($thedate, $form_from_date) < 0 || strcmp($thedate, $form_to_date) > 0) continue;
 462         //
 463         // If a diagnosis code was given then skip any invoices without
 464         // that diagnosis.
 465         if ($form_dx_code && $form_dx_codetype) {
 466           $tmp = sqlQuery("SELECT count(*) AS count FROM billing WHERE " .
 467             "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 468             "code_type = '$form_dx_codetype' AND code LIKE '$form_dx_code' AND " .
 469             "activity = 1");
 470           if (empty($tmp['count'])) {
 471             $ids_to_skip[$trans_id] = 1;
 472             continue;
 473           }
 474         }
 475         //
 476         $docid = empty($row['encounter_id']) ? $row['docid'] : $row['encounter_id'];
 477         $key = sprintf("%08u%s%08u%08u%06u", $docid, $thedate,
 478           $patient_id, $encounter_id, ++$irow);
 479         $arows[$key] = array();
 480         $arows[$key]['transdate'] = $thedate;
 481         $arows[$key]['amount'] = 0 - $row['pay_amount'];
 482         $arows[$key]['docid'] = $docid;
 483         $arows[$key]['project_id'] = empty($row['payer_id']) ? 0 : $row['payer_id'];
 484         $arows[$key]['memo'] = $row['code'];
 485         $arows[$key]['invnumber'] = "$patient_id.$encounter_id";
 486         $arows[$key]['irnumber'] = $row['invoice_refno'];
 487       } // end while
 488     } // end $INTEGRATED_AR
 489
 490     else {
 491       if ($form_proc_code) {
 492         $query = "SELECT acc_trans.amount, acc_trans.transdate, " .
 493           "acc_trans.memo, acc_trans.project_id, acc_trans.trans_id, " .
 494           "ar.invnumber, ar.employee_id, invoice.sellprice, invoice.qty " .
 495           "FROM acc_trans, ar, invoice WHERE " .
 496           "acc_trans.chart_id = $chart_id_cash AND " .
 497           "acc_trans.memo ILIKE '$form_proc_code' AND " .
 498           "ar.id = acc_trans.trans_id AND " .
 499           "invoice.trans_id = acc_trans.trans_id AND " .
 500           "invoice.serialnumber ILIKE acc_trans.memo AND " .
 501           "invoice.sellprice >= 0.00 AND " .
 502           "( invoice.description ILIKE 'CPT%' OR invoice.description ILIKE 'Proc%' ) AND ";
 503       }
 504       else {
 505         $query = "select acc_trans.amount, acc_trans.transdate, " .
 506           "acc_trans.memo, acc_trans.trans_id, " .
 507           "ar.invnumber, ar.employee_id from acc_trans, ar where " .
 508           "acc_trans.chart_id = $chart_id_cash and " .
 509           "ar.id = acc_trans.trans_id and ";
 510       }
 511
 512       if ($form_use_edate) {
 513         $query .= "ar.transdate >= '$form_from_date' and " .
 514         "ar.transdate <= '$form_to_date'";
 515       } else {
 516         $query .= "acc_trans.transdate >= '$form_from_date' and " .
 517         "acc_trans.transdate <= '$form_to_date'";
 518       }
 519
 520       $query .= " order by ar.invnumber";
 521
 522       // echo "<!-- $query -->\n"; // debugging
 523
 524       $t_res = SLQuery($query);
 525       if ($sl_err) die($sl_err);
 526
 527       $docname     = "";
 528       $docnameleft = "";
 529       $main_docid  = 0;
 530       $doctotal1   = 0;
 531       $grandtotal1 = 0;
 532       $doctotal2   = 0;
 533       $grandtotal2 = 0;
 534       $last_trans_id = 0;
 535       $skipping      = false;
 536
 537       for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 538         $row = SLGetRow($t_res, $irow);
 539
 540         list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 541
 542         // Under some conditions we may skip invoices that matched the SQL query.
 543         //
 544         if ($row['trans_id'] == $last_trans_id) {
 545           if ($skipping) continue;
 546           // same invoice and not skipping, do nothing.
 547         } else { // new invoice
 548           $skipping = false;
 549           // If a diagnosis code was given then skip any invoices without
 550           // that diagnosis.
 551           if ($form_dx_code) {
 552             if (!SLQueryValue("SELECT count(*) FROM invoice WHERE " .
 553               "invoice.trans_id = '" . $row['trans_id'] . "' AND " .
 554               "( invoice.description ILIKE 'ICD9:$form_dx_code %' OR " .
 555               "invoice.serialnumber ILIKE 'ICD9:$form_dx_code' )"))
 556             {
 557               $skipping = true;
 558               continue;
 559             }
 560           }
 561           // If a facility was specified then skip invoices whose encounters
 562           // do not indicate that facility.
 563           if ($form_facility) {
 564             $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
 565               "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 566               "facility_id = '$form_facility'");
 567             if (empty($tmp['count'])) {
 568               $skipping = true;
 569               continue;
 570             }
 571           }
 572           // Find out who the practitioner is.
 573           /***********************************************************
 574           $tmp = sqlQuery("SELECT users.id, users.authorized FROM forms, users WHERE " .
 575             "forms.pid = '$patient_id' AND forms.encounter = '$encounter_id' AND " .
 576             "forms.formdir = 'newpatient' AND users.username = forms.user");
 577           $main_docid = empty($tmp['id']) ? 0 : $tmp['id'];
 578           if (empty($tmp['authorized'])) {
 579             $tmp = sqlQuery("SELECT users.id FROM billing, users WHERE " .
 580               "billing.pid = '$patient_id' AND billing.encounter = '$encounter_id' AND " .
 581               "billing.activity = 1 AND billing.fee > 0 AND " .
 582               "users.id = billing.provider_id AND users.authorized = 1 " .
 583               "ORDER BY billing.fee DESC, billing.id ASC LIMIT 1");
 584             if (!empty($tmp['id'])) $main_docid = $tmp['id'];
 585           }
 586           ***********************************************************/
 587           $tmp = sqlQuery("SELECT provider_id FROM form_encounter WHERE " .
 588             "pid = '$patient_id' AND encounter = '$encounter_id' " .
 589             "ORDER BY id DESC LIMIT 1");
 590           $main_docid = $tmp['provider_id'] + 0;
 591
 592           // If a practitioner was specified then skip other practitioners.
 593           if ($form_doctor) {
 594             if ($form_doctor != $main_docid) {
 595               $skipping = true;
 596               continue;
 597             }
 598           }
 599         } // end new invoice
 600
 601         $row['docid'] = $main_docid;
 602         $key = sprintf("%08u%s%08u%08u%06u", $main_docid, $row['transdate'],
 603           $patient_id, $encounter_id, $irow);
 604         $arows[$key] = $row;
 605       }
 606
 607     } // end not $INTEGRATED_AR
 608
 609     ksort($arows);
 610     $docid = 0;
 611
 612     foreach ($arows as $row) {
 613
 614       // Get insurance company name
 615       $insconame = '';
 616       if ($form_proc_codefull  && $row['project_id']) {
 617         $tmp = sqlQuery("SELECT name FROM insurance_companies WHERE " .
 618           "id = '" . $row['project_id'] . "'");
 619         $insconame = $tmp['name'];
 620       }
 621
 622       $amount1 = 0;
 623       $amount2 = 0;
 624       if ($form_procedures && is_clinic($row['memo']))
 625         $amount2 -= $row['amount'];
 626       else
 627         $amount1 -= $row['amount'];
 628
 629       // if ($docid != $row['employee_id']) {
 630       if ($docid != $row['docid']) {
 631         if ($docid) {
 632           // Print doc totals.
 633 ?>
 634
 635  <tr bgcolor="#ddddff">
 636   <td class="detail" colspan="<?php echo ($form_proc_codefull ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 637    <?php echo xl('Totals for ') . $docname ?>
 638   </td>
 639   <td align="right">
 640    <?php bucks($doctotal1) ?>
 641   </td>
 642 <?php if ($form_procedures) { ?>
 643   <td align="right">
 644    <?php bucks($doctotal2) ?>
 645   </td>
 646 <?php } ?>
 647  </tr>
 648 <?php
 649         }
 650         $doctotal1 = 0;
 651         $doctotal2 = 0;
 652
 653         $docid = $row['docid'];
 654         $tmp = sqlQuery("SELECT lname, fname FROM users WHERE id = '$docid'");
 655         $docname = empty($tmp) ? 'Unknown' : $tmp['fname'] . ' ' . $tmp['lname'];
 656
 657         $docnameleft = $docname;
 658       }
 659
 660       if ($_POST['form_details']) {
 661 ?>
 662
 663  <tr>
 664   <td class="detail">
 665    <?php echo $docnameleft; $docnameleft = "&nbsp;" ?>
 666   </td>
 667   <td class="detail">
 668    <?php echo oeFormatShortDate($row['transdate']) ?>
 669   </td>
 670 <?php if ($form_procedures) { ?>
 671   <td class="detail">
 672    <?php echo empty($row['irnumber']) ? $row['invnumber'] : $row['irnumber']; ?>
 673   </td>
 674 <?php } ?>
 675 <?php
 676         if ($form_proc_code && $form_proc_codetype) {
 677           echo "  <td class='detail' align='right'>";
 678           if ($INTEGRATED_AR) {
 679             list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 680             $tmp = sqlQuery("SELECT SUM(fee) AS sum FROM billing WHERE " .
 681               "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 682               "code_type = '$form_proc_codetype' AND code = '$form_proc_code' AND activity = 1");
 683             bucks($tmp['sum']);
 684           }
 685           else {
 686             bucks($row['sellprice'] * $row['qty']);
 687           }
 688           echo "  </td>\n";
 689         }
 690 ?>
 691 <?php if ($form_proc_codefull) { ?>
 692   <td class="detail">
 693    <?php echo $insconame ?>
 694   </td>
 695 <?php } ?>
 696 <?php if ($form_procedures) { ?>
 697   <td class="detail">
 698    <?php echo $row['memo'] ?>
 699   </td>
 700 <?php } ?>
 701   <td class="detail" align="right">
 702    <?php bucks($amount1) ?>
 703   </td>
 704 <?php if ($form_procedures) { ?>
 705   <td class="detail" align="right">
 706    <?php bucks($amount2) ?>
 707   </td>
 708 <?php } ?>
 709  </tr>
 710 <?php
 711       } // end details
 712       $doctotal1   += $amount1;
 713       $doctotal2   += $amount2;
 714       $grandtotal1 += $amount1;
 715       $grandtotal2 += $amount2;
 716     }
 717 ?>
 718
 719  <tr bgcolor="#ddddff">
 720   <td class="detail" colspan="<?php echo ($form_proc_codefull ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 721    <?php echo xl('Totals for ') . $docname ?>
 722   </td>
 723   <td align="right">
 724    <?php bucks($doctotal1) ?>
 725   </td>
 726 <?php if ($form_procedures) { ?>
 727   <td align="right">
 728    <?php bucks($doctotal2) ?>
 729   </td>
 730 <?php } ?>
 731  </tr>
 732
 733  <tr bgcolor="#ffdddd">
 734   <td class="detail" colspan="<?php echo ($form_proc_codefull ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 735    <?php xl('Grand Totals','e') ?>
 736   </td>
 737   <td align="right">
 738    <?php bucks($grandtotal1) ?>
 739   </td>
 740 <?php if ($form_procedures) { ?>
 741   <td align="right">
 742    <?php bucks($grandtotal2) ?>
 743   </td>
 744 <?php } ?>
 745  </tr>
 746
 747 <?php
 748   }
 749   if (!$INTEGRATED_AR) SLClose();
 750 ?>
 751
 752 </table>
 753 </div>
 754 <?php } else { ?>
 755 <div class='text'>
 756         <?php echo xl('Please input search criteria above, and click Submit to view results.', 'e' ); ?>
 757 </div>
 758 <?php } ?>
 759
 760 </form>
 761 </body>
 762
 763 <!-- stuff for the popup calendar -->
 764 <link rel='stylesheet' href='<?php echo $css_header ?>' type='text/css'>
 765 <style type="text/css">@import url(<?php echo $GLOBALS['webroot']; ?>/library/dynarch_calendar.css);</style>
 766 <script type="text/javascript" src="<?php echo $GLOBALS['webroot']; ?>/library/dynarch_calendar.js"></script>
 767 <?php require_once($GLOBALS['srcdir'].'/dynarch_calendar_en.inc.php'); ?>
 768 <script type="text/javascript" src="<?php echo $GLOBALS['webroot']; ?>/library/dynarch_calendar_setup.js"></script>
 769 <script type="text/javascript" src="<?php echo $GLOBALS['webroot']; ?>/library/js/jquery.1.3.2.js"></script>
 770
 771 <script language="Javascript">
 772  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 773  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 774 </script>
 775
 776 </html>