interface/billing/sl_receipts_report.php

   1 <?
   2   // This module was written for one of my clients to report on cash
   3   // receipts by practitioner.  It is not as complete as it should be
   4   // but I wanted to make the code available to the project because
   5   // many other practices have this same need. - rod@sunsetsystems.com
   6
   7   include_once("../globals.php");
   8   include_once("../../library/patient.inc");
   9   include_once("../../library/sql-ledger.inc");
  10   include_once("../../library/acl.inc");
  11
  12   // This determines if a particular procedure code corresponds to receipts
  13   // for the "Clinic" column as opposed to receipts for the practitioner.  Each
  14   // practice will have its own policies in this regard, so you'll probably
  15   // have to customize this function.  If you use the "fee sheet" encounter
  16   // form then the code below may work for you.
  17   //
  18   include_once("../forms/fee_sheet/codes.php");
  19   function is_clinic($code) {
  20     global $bcodes;
  21     return ($bcodes['CPT4'][xl('Lab')][$code]     ||
  22       $bcodes['CPT4'][xl('Immunizations')][$code] ||
  23       $bcodes['HCPCS'][xl('Therapeutic Injections')][$code]);
  24   }
  25
  26   function bucks($amount) {
  27     if ($amount)
  28       printf("%.2f", $amount);
  29   }
  30
  31   if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
  32
  33   SLConnect();
  34
  35   $form_use_edate = $_POST['form_use_edate'];
  36   $form_cptcode = trim($_POST['form_cptcode']);
  37   $form_icdcode = trim($_POST['form_icdcode']);
  38 ?>
  39 <html>
  40 <head>
  41 <title><?xl('Receipts for Medical Services','e')?></title>
  42 </head>
  43
  44 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
  45 <center>
  46
  47 <h2><?xl('Cash Receipts','e')?></h2>
  48
  49 <form method='post' action='sl_receipts_report.php'>
  50
  51 <table border='0' cellpadding='3'>
  52
  53  <tr>
  54   <td>
  55 <?
  56         if (acl_check('acct', 'rep_a')) {
  57                 // Build a drop-down list of providers.
  58                 //
  59                 $query = "select id, lname, fname from users where " .
  60                         "authorized = 1 order by lname, fname";
  61                 $res = sqlStatement($query);
  62                 echo "   <select name='form_doctor'>\n";
  63                 echo "    <option value=''>All Providers\n";
  64                 while ($row = sqlFetchArray($res)) {
  65                         $provid = $row['id'];
  66                         echo "    <option value='$provid'";
  67                         if ($provid == $_POST['form_doctor']) echo " selected";
  68                         echo ">" . $row['lname'] . ", " . $row['fname'] . "\n";
  69                 }
  70                 echo "   </select>\n";
  71         } else {
  72                 echo "<input type='hidden' name='form_doctor' value='" . $_SESSION['authUserID'] . "'>";
  73         }
  74 ?>
  75    &nbsp;<select name='form_use_edate'>
  76     <option value='0'><?php xl('Payment Date','e'); ?></option>
  77     <option value='1'<?php if ($form_use_edate) echo ' selected' ?>><?php xl('Invoice Date','e'); ?></option>
  78    </select>
  79    &nbsp;<?xl('From:','e')?>
  80    <input type='text' name='form_from_date' size='10' value='<? echo $_POST['form_from_date']; ?>' title='MM/DD/YYYY'>
  81    &nbsp;To:
  82    <input type='text' name='form_to_date' size='10' value='<? echo $_POST['form_to_date']; ?>' title='MM/DD/YYYY'>
  83    &nbsp;CPT:
  84    <input type='text' name='form_cptcode' size='5' value='<? echo $form_cptcode; ?>'
  85     title='<?php xl('Optional procedure code','e'); ?>'>
  86    &nbsp;ICD:
  87    <input type='text' name='form_icdcode' size='5' value='<? echo $form_icdcode; ?>'
  88     title='<?php xl('Enter a diagnosis code to exclude all invoices not containing it','e'); ?>'>
  89    &nbsp;
  90    <input type='checkbox' name='form_details' value='1'<? if ($_POST['form_details']) echo " checked"; ?>><?xl('Details','e')?>
  91    &nbsp;
  92    <input type='submit' name='form_refresh' value="<?xl('Refresh','e')?>">
  93   </td>
  94  </tr>
  95
  96  <tr>
  97   <td height="1">
  98   </td>
  99  </tr>
 100
 101 </table>
 102
 103 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 104
 105  <tr bgcolor="#dddddd">
 106   <td class="dehead">
 107    <?xl('Practitioner','e')?>
 108   </td>
 109   <td class="dehead">
 110    <?xl('Date','e')?>
 111   </td>
 112   <td class="dehead">
 113    <?xl('Invoice','e')?>
 114   </td>
 115 <?php if ($form_cptcode) { ?>
 116   <td class="dehead" align='right'>
 117    <?xl('InvAmt','e')?>
 118   </td>
 119 <?php } ?>
 120 <?php if ($form_cptcode) { ?>
 121   <td class="dehead">
 122    <?xl('Insurance','e')?>
 123   </td>
 124 <?php } ?>
 125   <td class="dehead">
 126    <?xl('Procedure','e')?>
 127   </td>
 128   <td class="dehead" align="right">
 129    <?xl('Prof.','e')?>
 130   </td>
 131   <td class="dehead" align="right">
 132    <?xl('Clinic','e')?>
 133   </td>
 134  </tr>
 135 <?
 136   $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
 137   if ($sl_err) die($sl_err);
 138
 139   if ($_POST['form_refresh']) {
 140     $form_doctor = $_POST['form_doctor'];
 141     $from_date = fixDate($_POST['form_from_date']);
 142     $to_date   = fixDate($_POST['form_to_date']);
 143
 144     if ($form_cptcode) {
 145       $query = "SELECT acc_trans.amount, acc_trans.transdate, " .
 146         "acc_trans.memo, acc_trans.project_id, acc_trans.trans_id, " .
 147         "ar.invnumber, ar.employee_id, invoice.fxsellprice " .
 148         "FROM acc_trans, ar, invoice WHERE " .
 149         "acc_trans.chart_id = $chart_id_cash AND " .
 150         "acc_trans.memo ILIKE '$form_cptcode' AND " .
 151         "ar.id = acc_trans.trans_id AND " .
 152         "invoice.trans_id = acc_trans.trans_id AND " .
 153         "invoice.serialnumber ILIKE acc_trans.memo AND " .
 154         "invoice.fxsellprice >= 0.00 AND " .
 155         "invoice.fxsellprice >= 0.00 AND " .
 156         "( invoice.description ILIKE 'CPT%' OR invoice.description ILIKE 'Proc%' ) AND ";
 157     }
 158     else {
 159       $query = "select acc_trans.amount, acc_trans.transdate, " .
 160         "acc_trans.memo, acc_trans.trans_id, " .
 161         "ar.invnumber, ar.employee_id from acc_trans, ar where " .
 162         "acc_trans.chart_id = $chart_id_cash and " .
 163         "ar.id = acc_trans.trans_id and ";
 164     }
 165
 166     if ($form_use_edate) {
 167       $query .= "ar.transdate >= '$from_date' and " .
 168       "ar.transdate <= '$to_date'";
 169     } else {
 170       $query .= "acc_trans.transdate >= '$from_date' and " .
 171       "acc_trans.transdate <= '$to_date'";
 172     }
 173
 174     if ($form_doctor) {
 175       $tmp = sqlQuery("select foreign_id from integration_mapping where " .
 176         "foreign_table = 'salesman' and local_id = $form_doctor");
 177       // $emplid = SLQueryValue("select id from employee where employeenumber = " .
 178       //   $tmp['foreign_id']);
 179       $emplid = $tmp['foreign_id'];
 180       $query .= " and ar.employee_id = $emplid";
 181     }
 182
 183     $query .= " order by ar.employee_id, acc_trans.transdate, ar.invnumber, acc_trans.memo";
 184
 185     echo "<!-- $query -->\n";
 186
 187     $t_res = SLQuery($query);
 188     if ($sl_err) die($sl_err);
 189
 190     $docname     = "";
 191     $docnameleft = "";
 192     $docid       = 0;
 193     $doctotal1   = 0;
 194     $grandtotal1 = 0;
 195     $doctotal2   = 0;
 196     $grandtotal2 = 0;
 197     $last_trans_id = 0;
 198     $skipping      = false;
 199
 200     for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 201       $row = SLGetRow($t_res, $irow);
 202
 203       // If a diagnosis code was given then skip any invoices without
 204       // that diagnosis.
 205       if ($form_icdcode) {
 206         if ($row['trans_id'] == $last_trans_id) {
 207           if ($skipping) continue;
 208           // same invoice and not skipping, do nothing.
 209         } else { // new invoice
 210           $skipping = false;
 211           if (!SLQueryValue("SELECT count(*) FROM invoice WHERE " .
 212             "invoice.trans_id = '" . $row['trans_id'] . "' AND " .
 213             "( invoice.description ILIKE 'ICD9:$form_icdcode %' OR " .
 214             "invoice.serialnumber ILIKE 'ICD9:$form_icdcode' )"))
 215           {
 216             $skipping = true;
 217             continue;
 218           }
 219         }
 220       }
 221
 222       // Get insurance company name
 223       $insconame = '';
 224       if ($form_cptcode && $row['project_id']) {
 225         $tmp = sqlQuery("SELECT name FROM insurance_companies WHERE " .
 226           "id = '" . $row['project_id'] . "'");
 227         $insconame = $tmp['name'];
 228       }
 229
 230       $amount1 = 0;
 231       $amount2 = 0;
 232       if (is_clinic($row['memo']))
 233         $amount2 -= $row['amount'];
 234       else
 235         $amount1 -= $row['amount'];
 236
 237       if ($docid != $row['employee_id']) {
 238         if ($docid) {
 239           // Print doc totals.
 240 ?>
 241
 242  <tr bgcolor="#ddddff">
 243   <td class="detail" colspan="<?php echo $form_cptcode ? '6' : '4'; ?>">
 244    <? echo xl('Totals for ') . $docname ?>
 245   </td>
 246   <td class="dehead" align="right">
 247    <? bucks($doctotal1) ?>
 248   </td>
 249   <td class="dehead" align="right">
 250    <? bucks($doctotal2) ?>
 251   </td>
 252  </tr>
 253 <?
 254         }
 255         $doctotal1 = 0;
 256         $doctotal2 = 0;
 257         $docid = $row['employee_id'];
 258         $docname = SLQueryValue("select name from employee where id = $docid");
 259         $docnameleft = $docname;
 260       }
 261
 262       if ($_POST['form_details']) {
 263 ?>
 264
 265  <tr>
 266   <td class="detail">
 267    <? echo $docnameleft; $docnameleft = "&nbsp;" ?>
 268   </td>
 269   <td class="detail">
 270    <? echo $row['transdate'] ?>
 271   </td>
 272   <td class="detail">
 273    <? echo $row['invnumber'] ?>
 274   </td>
 275 <?php if ($form_cptcode) { ?>
 276   <td class="detail" align='right'>
 277    <?php bucks($row['fxsellprice']) ?>
 278   </td>
 279 <?php } ?>
 280 <?php if ($form_cptcode) { ?>
 281   <td class="detail">
 282    <?php echo $insconame ?>
 283   </td>
 284 <?php } ?>
 285   <td class="detail">
 286    <? echo $row['memo'] ?>
 287   </td>
 288   <td class="detail" align="right">
 289    <? bucks($amount1) ?>
 290   </td>
 291   <td class="detail" align="right">
 292    <? bucks($amount2) ?>
 293   </td>
 294  </tr>
 295 <?
 296       }
 297       $doctotal1   += $amount1;
 298       $doctotal2   += $amount2;
 299       $grandtotal1 += $amount1;
 300       $grandtotal2 += $amount2;
 301     }
 302 ?>
 303
 304  <tr bgcolor="#ddddff">
 305   <td class="detail" colspan="<?php echo $form_cptcode ? '6' : '4'; ?>">
 306    <?echo xl('Totals for ') . $docname ?>
 307   </td>
 308   <td class="dehead" align="right">
 309    <? bucks($doctotal1) ?>
 310   </td>
 311   <td class="dehead" align="right">
 312    <? bucks($doctotal2) ?>
 313   </td>
 314  </tr>
 315
 316  <tr bgcolor="#ffdddd">
 317   <td class="detail" colspan="<?php echo $form_cptcode ? '6' : '4'; ?>">
 318    <?xl('Grand Totals','e')?>
 319   </td>
 320   <td class="dehead" align="right">
 321    <? bucks($grandtotal1) ?>
 322   </td>
 323   <td class="dehead" align="right">
 324    <? bucks($grandtotal2) ?>
 325   </td>
 326  </tr>
 327
 328 <?
 329   }
 330   SLClose();
 331 ?>
 332
 333 </table>
 334 </form>
 335 </center>
 336 </body>
 337 </html>