interface/billing/sl_receipts_report.php

   1 <?php
   2   // Copyright (C) 2006-2008 Rod Roark <rod@sunsetsystems.com>
   3   //
   4   // This program is free software; you can redistribute it and/or
   5   // modify it under the terms of the GNU General Public License
   6   // as published by the Free Software Foundation; either version 2
   7   // of the License, or (at your option) any later version.
   8
   9   // This module was written for one of my clients to report on cash
  10   // receipts by practitioner.  It is not as complete as it should be
  11   // but I wanted to make the code available to the project because
  12   // many other practices have this same need. - rod@sunsetsystems.com
  13
  14   include_once("../globals.php");
  15   include_once("../../library/patient.inc");
  16   include_once("../../library/sql-ledger.inc");
  17   include_once("../../library/acl.inc");
  18
  19   // This determines if a particular procedure code corresponds to receipts
  20   // for the "Clinic" column as opposed to receipts for the practitioner.  Each
  21   // practice will have its own policies in this regard, so you'll probably
  22   // have to customize this function.  If you use the "fee sheet" encounter
  23   // form then the code below may work for you.
  24   //
  25   include_once("../forms/fee_sheet/codes.php");
  26   function is_clinic($code) {
  27     global $bcodes;
  28     return ($bcodes['CPT4'][xl('Lab')][$code]     ||
  29       $bcodes['CPT4'][xl('Immunizations')][$code] ||
  30       $bcodes['HCPCS'][xl('Therapeutic Injections')][$code]);
  31   }
  32
  33   function bucks($amount) {
  34     if ($amount)
  35       printf("%.2f", $amount);
  36   }
  37
  38   if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
  39
  40   SLConnect();
  41
  42   $form_use_edate  = $_POST['form_use_edate'];
  43   $form_cptcode    = trim($_POST['form_cptcode']);
  44   $form_icdcode    = trim($_POST['form_icdcode']);
  45   $form_procedures = empty($_POST['form_procedures']) ? 0 : 1;
  46   $form_from_date  = fixDate($_POST['form_from_date'], date('Y-m-01'));
  47   $form_to_date    = fixDate($_POST['form_to_date'], date('Y-m-d'));
  48   $form_facility   = $_POST['form_facility'];
  49 ?>
  50 <html>
  51 <head>
  52 <?php html_header_show();?>
  53 <title><?xl('Cash Receipts by Provider','e')?></title>
  54 </head>
  55
  56 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
  57 <center>
  58
  59 <h2><?xl('Cash Receipts by Provider','e')?></h2>
  60
  61 <form method='post' action='sl_receipts_report.php'>
  62
  63 <table border='0' cellpadding='3'>
  64
  65  <tr>
  66   <td>
  67 <?php
  68  // Build a drop-down list of facilities.
  69  //
  70  $query = "SELECT id, name FROM facility ORDER BY name";
  71  $fres = sqlStatement($query);
  72  echo "   <select name='form_facility'>\n";
  73  echo "    <option value=''>-- All Facilities --\n";
  74  while ($frow = sqlFetchArray($fres)) {
  75   $facid = $frow['id'];
  76   echo "    <option value='$facid'";
  77   if ($facid == $form_facility) echo " selected";
  78   echo ">" . $frow['name'] . "\n";
  79  }
  80  echo "   </select>\n";
  81 ?>
  82 <?php
  83         if (acl_check('acct', 'rep_a')) {
  84                 // Build a drop-down list of providers.
  85                 //
  86                 $query = "select id, lname, fname from users where " .
  87                         "authorized = 1 order by lname, fname";
  88                 $res = sqlStatement($query);
  89                 echo "   &nbsp;<select name='form_doctor'>\n";
  90                 echo "    <option value=''>-- All Providers --\n";
  91                 while ($row = sqlFetchArray($res)) {
  92                         $provid = $row['id'];
  93                         echo "    <option value='$provid'";
  94                         if ($provid == $_POST['form_doctor']) echo " selected";
  95                         echo ">" . $row['lname'] . ", " . $row['fname'] . "\n";
  96                 }
  97                 echo "   </select>\n";
  98         } else {
  99                 echo "<input type='hidden' name='form_doctor' value='" . $_SESSION['authUserID'] . "'>";
 100         }
 101 ?>
 102    &nbsp;<select name='form_use_edate'>
 103     <option value='0'><?php xl('Payment Date','e'); ?></option>
 104     <option value='1'<?php if ($form_use_edate) echo ' selected' ?>><?php xl('Invoice Date','e'); ?></option>
 105    </select>
 106    &nbsp;<?xl('From:','e')?>
 107
 108    <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $form_from_date ?>'
 109     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 110    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 111     id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 112     title='<?php xl('Click here to choose a date','e'); ?>'>
 113    &nbsp;To:
 114    <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_date ?>'
 115     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 116    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 117     id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 118     title='<?php xl('Click here to choose a date','e'); ?>'>
 119    <?php if (!$GLOBALS['simplified_demographics']) echo '&nbsp;' . xl('CPT') . ':'; ?>
 120    <input type='text' name='form_cptcode' size='5' value='<? echo $form_cptcode; ?>'
 121     title='<?php xl('Optional procedure code','e'); ?>'
 122     <?php if ($GLOBALS['simplified_demographics']) echo "style='display:none'"; ?>>
 123    <?php if (!$GLOBALS['simplified_demographics']) echo '&nbsp;' . xl('ICD') . ':'; ?>
 124    <input type='text' name='form_icdcode' size='5' value='<? echo $form_icdcode; ?>'
 125     title='<?php xl('Enter a diagnosis code to exclude all invoices not containing it','e'); ?>'
 126     <?php if ($GLOBALS['simplified_demographics']) echo "style='display:none'"; ?>>
 127    &nbsp;
 128    <input type='checkbox' name='form_details' value='1'<? if ($_POST['form_details']) echo " checked"; ?>><?xl('Details','e')?>
 129    &nbsp;
 130    <input type='checkbox' name='form_procedures' value='1'<? if ($form_procedures) echo " checked"; ?>><?xl('Procedures','e')?>
 131    &nbsp;
 132    <input type='submit' name='form_refresh' value="<?xl('Refresh','e')?>">
 133    &nbsp;
 134    <input type='button' value='<?php xl('Print','e'); ?>' onclick='window.print()' />
 135   </td>
 136  </tr>
 137
 138  <tr>
 139   <td height="1">
 140   </td>
 141  </tr>
 142
 143 </table>
 144
 145 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 146
 147  <tr bgcolor="#dddddd">
 148   <td class="dehead">
 149    <?php xl('Practitioner','e') ?>
 150   </td>
 151   <td class="dehead">
 152    <?php xl('Date','e') ?>
 153   </td>
 154 <?php if ($form_procedures) { ?>
 155   <td class="dehead">
 156    <?php xl('Invoice','e') ?>
 157   </td>
 158 <?php } ?>
 159 <?php if ($form_cptcode) { ?>
 160   <td class="dehead" align='right'>
 161    <?php xl('InvAmt','e') ?>
 162   </td>
 163 <?php } ?>
 164 <?php if ($form_cptcode) { ?>
 165   <td class="dehead">
 166    <?php xl('Insurance','e') ?>
 167   </td>
 168 <?php } ?>
 169 <?php if ($form_procedures) { ?>
 170   <td class="dehead">
 171    <?php xl('Procedure','e') ?>
 172   </td>
 173   <td class="dehead" align="right">
 174    <?php xl('Prof.','e') ?>
 175   </td>
 176   <td class="dehead" align="right">
 177    <?php xl('Clinic','e') ?>
 178   </td>
 179 <?php } else { ?>
 180   <td class="dehead" align="right">
 181    <?php xl('Received','e') ?>
 182   </td>
 183 <?php } ?>
 184  </tr>
 185 <?
 186   $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
 187   if ($sl_err) die($sl_err);
 188
 189   if ($_POST['form_refresh']) {
 190     $form_doctor = $_POST['form_doctor'];
 191
 192     if ($form_cptcode) {
 193       $query = "SELECT acc_trans.amount, acc_trans.transdate, " .
 194         "acc_trans.memo, acc_trans.project_id, acc_trans.trans_id, " .
 195         "ar.invnumber, ar.employee_id, invoice.sellprice, invoice.qty " .
 196         "FROM acc_trans, ar, invoice WHERE " .
 197         "acc_trans.chart_id = $chart_id_cash AND " .
 198         "acc_trans.memo ILIKE '$form_cptcode' AND " .
 199         "ar.id = acc_trans.trans_id AND " .
 200         "invoice.trans_id = acc_trans.trans_id AND " .
 201         "invoice.serialnumber ILIKE acc_trans.memo AND " .
 202         "invoice.sellprice >= 0.00 AND " .
 203         "( invoice.description ILIKE 'CPT%' OR invoice.description ILIKE 'Proc%' ) AND ";
 204     }
 205     else {
 206       $query = "select acc_trans.amount, acc_trans.transdate, " .
 207         "acc_trans.memo, acc_trans.trans_id, " .
 208         "ar.invnumber, ar.employee_id from acc_trans, ar where " .
 209         "acc_trans.chart_id = $chart_id_cash and " .
 210         "ar.id = acc_trans.trans_id and ";
 211     }
 212
 213     if ($form_use_edate) {
 214       $query .= "ar.transdate >= '$form_from_date' and " .
 215       "ar.transdate <= '$form_to_date'";
 216     } else {
 217       $query .= "acc_trans.transdate >= '$form_from_date' and " .
 218       "acc_trans.transdate <= '$form_to_date'";
 219     }
 220
 221     if ($form_doctor) {
 222       $tmp = sqlQuery("select foreign_id from integration_mapping where " .
 223         "foreign_table = 'salesman' and local_id = $form_doctor");
 224       // $emplid = SLQueryValue("select id from employee where employeenumber = " .
 225       //   $tmp['foreign_id']);
 226       $emplid = $tmp['foreign_id'];
 227       $query .= " and ar.employee_id = $emplid";
 228     }
 229
 230     $query .= " order by ar.employee_id, acc_trans.transdate, ar.invnumber, acc_trans.memo";
 231
 232     echo "<!-- $query -->\n";
 233
 234     $t_res = SLQuery($query);
 235     if ($sl_err) die($sl_err);
 236
 237     $docname     = "";
 238     $docnameleft = "";
 239     $docid       = 0;
 240     $doctotal1   = 0;
 241     $grandtotal1 = 0;
 242     $doctotal2   = 0;
 243     $grandtotal2 = 0;
 244     $last_trans_id = 0;
 245     $skipping      = false;
 246
 247     for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 248       $row = SLGetRow($t_res, $irow);
 249
 250       // Under some conditions we may skip invoices that matched the SQL query.
 251       //
 252       if ($row['trans_id'] == $last_trans_id) {
 253         if ($skipping) continue;
 254         // same invoice and not skipping, do nothing.
 255       } else { // new invoice
 256         $skipping = false;
 257         // If a diagnosis code was given then skip any invoices without
 258         // that diagnosis.
 259         if ($form_icdcode) {
 260           if (!SLQueryValue("SELECT count(*) FROM invoice WHERE " .
 261             "invoice.trans_id = '" . $row['trans_id'] . "' AND " .
 262             "( invoice.description ILIKE 'ICD9:$form_icdcode %' OR " .
 263             "invoice.serialnumber ILIKE 'ICD9:$form_icdcode' )"))
 264           {
 265             $skipping = true;
 266             continue;
 267           }
 268         }
 269         // If a facility was specified then skip invoices whose encounters
 270         // do not indicate that facility.
 271         if ($form_facility) {
 272           list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 273           $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
 274             "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 275             "facility_id = '$form_facility'");
 276           if (empty($tmp['count'])) {
 277             $skipping = true;
 278             continue;
 279           }
 280         }
 281       }
 282
 283       // Get insurance company name
 284       $insconame = '';
 285       if ($form_cptcode && $row['project_id']) {
 286         $tmp = sqlQuery("SELECT name FROM insurance_companies WHERE " .
 287           "id = '" . $row['project_id'] . "'");
 288         $insconame = $tmp['name'];
 289       }
 290
 291       $amount1 = 0;
 292       $amount2 = 0;
 293       if ($form_procedures && is_clinic($row['memo']))
 294         $amount2 -= $row['amount'];
 295       else
 296         $amount1 -= $row['amount'];
 297
 298       if ($docid != $row['employee_id']) {
 299         if ($docid) {
 300           // Print doc totals.
 301 ?>
 302
 303  <tr bgcolor="#ddddff">
 304   <td class="detail" colspan="<?php echo ($form_cptcode ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 305    <? echo xl('Totals for ') . $docname ?>
 306   </td>
 307   <td class="dehead" align="right">
 308    <?php bucks($doctotal1) ?>
 309   </td>
 310 <?php if ($form_procedures) { ?>
 311   <td class="dehead" align="right">
 312    <?php bucks($doctotal2) ?>
 313   </td>
 314 <?php } ?>
 315  </tr>
 316 <?
 317         }
 318         $doctotal1 = 0;
 319         $doctotal2 = 0;
 320         $docid = $row['employee_id'];
 321         $docname = SLQueryValue("select name from employee where id = $docid");
 322         $docnameleft = $docname;
 323       }
 324
 325       if ($_POST['form_details']) {
 326 ?>
 327
 328  <tr>
 329   <td class="detail">
 330    <?php echo $docnameleft; $docnameleft = "&nbsp;" ?>
 331   </td>
 332   <td class="detail">
 333    <?php echo $row['transdate'] ?>
 334   </td>
 335 <?php if ($form_procedures) { ?>
 336   <td class="detail">
 337    <?php echo $row['invnumber'] ?>
 338   </td>
 339 <?php } ?>
 340 <?php if ($form_cptcode) { ?>
 341   <td class="detail" align='right'>
 342    <?php bucks($row['sellprice'] * $row['qty']) ?>
 343   </td>
 344 <?php } ?>
 345 <?php if ($form_cptcode) { ?>
 346   <td class="detail">
 347    <?php echo $insconame ?>
 348   </td>
 349 <?php } ?>
 350 <?php if ($form_procedures) { ?>
 351   <td class="detail">
 352    <?php echo $row['memo'] ?>
 353   </td>
 354 <?php } ?>
 355   <td class="detail" align="right">
 356    <?php bucks($amount1) ?>
 357   </td>
 358 <?php if ($form_procedures) { ?>
 359   <td class="detail" align="right">
 360    <?php bucks($amount2) ?>
 361   </td>
 362 <?php } ?>
 363  </tr>
 364 <?
 365       }
 366       $doctotal1   += $amount1;
 367       $doctotal2   += $amount2;
 368       $grandtotal1 += $amount1;
 369       $grandtotal2 += $amount2;
 370     }
 371 ?>
 372
 373  <tr bgcolor="#ddddff">
 374   <td class="detail" colspan="<?php echo ($form_cptcode ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 375    <?echo xl('Totals for ') . $docname ?>
 376   </td>
 377   <td class="dehead" align="right">
 378    <?php bucks($doctotal1) ?>
 379   </td>
 380 <?php if ($form_procedures) { ?>
 381   <td class="dehead" align="right">
 382    <?php bucks($doctotal2) ?>
 383   </td>
 384 <?php } ?>
 385  </tr>
 386
 387  <tr bgcolor="#ffdddd">
 388   <td class="detail" colspan="<?php echo ($form_cptcode ? 4 : 2) + ($form_procedures ? 2 : 0); ?>">
 389    <?php xl('Grand Totals','e') ?>
 390   </td>
 391   <td class="dehead" align="right">
 392    <?php bucks($grandtotal1) ?>
 393   </td>
 394 <?php if ($form_procedures) { ?>
 395   <td class="dehead" align="right">
 396    <?php bucks($grandtotal2) ?>
 397   </td>
 398 <?php } ?>
 399  </tr>
 400
 401 <?
 402   }
 403   SLClose();
 404 ?>
 405
 406 </table>
 407 </form>
 408 </center>
 409 </body>
 410
 411 <!-- stuff for the popup calendar -->
 412 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 413 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 414 <script type="text/javascript" src="../../library/dynarch_calendar_en.js"></script>
 415 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 416 <script language="Javascript">
 417  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 418  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 419 </script>
 420
 421 </html>