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_billcode = trim($_POST['form_billcode']);
  37 ?>
  38 <html>
  39 <head>
  40 <title><?xl('Receipts for Medical Services','e')?></title>
  41 </head>
  42
  43 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
  44 <center>
  45
  46 <h2><?xl('Cash Receipts','e')?></h2>
  47
  48 <form method='post' action='sl_receipts_report.php'>
  49
  50 <table border='0' cellpadding='3'>
  51
  52  <tr>
  53   <td>
  54 <?
  55         if (acl_check('acct', 'rep_a')) {
  56                 // Build a drop-down list of providers.
  57                 //
  58                 $query = "select id, lname, fname from users where " .
  59                         "authorized = 1 order by lname, fname";
  60                 $res = sqlStatement($query);
  61                 echo "   <select name='form_doctor'>\n";
  62                 echo "    <option value=''>All Providers\n";
  63                 while ($row = sqlFetchArray($res)) {
  64                         $provid = $row['id'];
  65                         echo "    <option value='$provid'";
  66                         if ($provid == $_POST['form_doctor']) echo " selected";
  67                         echo ">" . $row['lname'] . ", " . $row['fname'] . "\n";
  68                 }
  69                 echo "   </select>\n";
  70         } else {
  71                 echo "<input type='hidden' name='form_doctor' value='" . $_SESSION['authUserID'] . "'>";
  72         }
  73 ?>
  74    &nbsp;<select name='form_use_edate'>
  75     <option value='0'>Payment Date</option>
  76     <option value='1'<?php if ($form_use_edate) echo ' selected' ?>>Invoice Date</option>
  77    </select>
  78    &nbsp;<?xl('From:','e')?>
  79    <input type='text' name='form_from_date' size='10' value='<? echo $_POST['form_from_date']; ?>' title='MM/DD/YYYY'>
  80    &nbsp;To:
  81    <input type='text' name='form_to_date' size='10' value='<? echo $_POST['form_to_date']; ?>' title='MM/DD/YYYY'>
  82    &nbsp;Code:
  83    <input type='text' name='form_billcode' size='5' value='<? echo $form_billcode; ?>' title='Optional billing code'>
  84    &nbsp;
  85    <input type='checkbox' name='form_details' value='1'<? if ($_POST['form_details']) echo " checked"; ?>><?xl('Details','e')?>
  86    &nbsp;
  87    <input type='submit' name='form_refresh' value="<?xl('Refresh','e')?>">
  88   </td>
  89  </tr>
  90
  91  <tr>
  92   <td height="1">
  93   </td>
  94  </tr>
  95
  96 </table>
  97
  98 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
  99
 100  <tr bgcolor="#dddddd">
 101   <td class="dehead">
 102    <?xl('Practitioner','e')?>
 103   </td>
 104   <td class="dehead">
 105    <?xl('Date','e')?>
 106   </td>
 107   <td class="dehead">
 108    <?xl('Invoice','e')?>
 109   </td>
 110 <?php if ($form_billcode) { ?>
 111   <td class="dehead" align='right'>
 112    <?xl('InvAmt','e')?>
 113   </td>
 114 <?php } ?>
 115 <?php if ($form_billcode) { ?>
 116   <td class="dehead">
 117    <?xl('Insurance','e')?>
 118   </td>
 119 <?php } ?>
 120   <td class="dehead">
 121    <?xl('Procedure','e')?>
 122   </td>
 123   <td class="dehead" align="right">
 124    <?xl('Prof.','e')?>
 125   </td>
 126   <td class="dehead" align="right">
 127    <?xl('Clinic','e')?>
 128   </td>
 129  </tr>
 130 <?
 131   $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
 132   if ($sl_err) die($sl_err);
 133
 134   if ($_POST['form_refresh']) {
 135     $form_doctor = $_POST['form_doctor'];
 136     $from_date = fixDate($_POST['form_from_date']);
 137     $to_date   = fixDate($_POST['form_to_date']);
 138
 139     if ($form_billcode) {
 140       $query = "SELECT acc_trans.amount, acc_trans.transdate, " .
 141         "acc_trans.memo, acc_trans.project_id, " .
 142         "ar.invnumber, ar.employee_id, invoice.fxsellprice " .
 143         "FROM acc_trans, ar, invoice WHERE " .
 144         "acc_trans.chart_id = $chart_id_cash AND " .
 145         "acc_trans.memo ILIKE '$form_billcode' AND " .
 146         "ar.id = acc_trans.trans_id AND " .
 147         "invoice.trans_id = acc_trans.trans_id AND " .
 148         "invoice.serialnumber ILIKE acc_trans.memo AND " .
 149         "invoice.fxsellprice >= 0.00 AND ";
 150     }
 151     else {
 152       $query = "select acc_trans.amount, acc_trans.transdate, acc_trans.memo, " .
 153         "ar.invnumber, ar.employee_id from acc_trans, ar where " .
 154         "acc_trans.chart_id = $chart_id_cash and " .
 155         "ar.id = acc_trans.trans_id and ";
 156     }
 157
 158     if ($form_use_edate) {
 159       $query .= "ar.transdate >= '$from_date' and " .
 160       "ar.transdate <= '$to_date'";
 161     } else {
 162       $query .= "acc_trans.transdate >= '$from_date' and " .
 163       "acc_trans.transdate <= '$to_date'";
 164     }
 165
 166     if ($form_doctor) {
 167       $tmp = sqlQuery("select foreign_id from integration_mapping where " .
 168         "foreign_table = 'salesman' and local_id = $form_doctor");
 169       // $emplid = SLQueryValue("select id from employee where employeenumber = " .
 170       //   $tmp['foreign_id']);
 171       $emplid = $tmp['foreign_id'];
 172       $query .= " and ar.employee_id = $emplid";
 173     }
 174
 175     $query .= " order by ar.employee_id, acc_trans.transdate, ar.invnumber, acc_trans.memo";
 176
 177     echo "<!-- $query -->\n";
 178
 179     $t_res = SLQuery($query);
 180     if ($sl_err) die($sl_err);
 181
 182     $docname     = "";
 183     $docnameleft = "";
 184     $docid       = 0;
 185     $doctotal1   = 0;
 186     $grandtotal1 = 0;
 187     $doctotal2   = 0;
 188     $grandtotal2 = 0;
 189
 190     for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 191       $row = SLGetRow($t_res, $irow);
 192
 193       // Get insurance company name
 194       $insconame = '';
 195       if ($form_billcode && $row['project_id']) {
 196         $tmp = sqlQuery("SELECT name FROM insurance_companies WHERE " .
 197           "id = '" . $row['project_id'] . "'");
 198         $insconame = $tmp['name'];
 199       }
 200
 201       $amount1 = 0;
 202       $amount2 = 0;
 203       if (is_clinic($row['memo']))
 204         $amount2 -= $row['amount'];
 205       else
 206         $amount1 -= $row['amount'];
 207
 208       if ($docid != $row['employee_id']) {
 209         if ($docid) {
 210           // Print doc totals.
 211 ?>
 212
 213  <tr bgcolor="#ddddff">
 214   <td class="detail" colspan="<?php echo $form_billcode ? '6' : '4'; ?>">
 215    <? echo xl('Totals for ') . $docname ?>
 216   </td>
 217   <td class="dehead" align="right">
 218    <? bucks($doctotal1) ?>
 219   </td>
 220   <td class="dehead" align="right">
 221    <? bucks($doctotal2) ?>
 222   </td>
 223  </tr>
 224 <?
 225         }
 226         $doctotal1 = 0;
 227         $doctotal2 = 0;
 228         $docid = $row['employee_id'];
 229         $docname = SLQueryValue("select name from employee where id = $docid");
 230         $docnameleft = $docname;
 231       }
 232
 233       if ($_POST['form_details']) {
 234 ?>
 235
 236  <tr>
 237   <td class="detail">
 238    <? echo $docnameleft; $docnameleft = "&nbsp;" ?>
 239   </td>
 240   <td class="detail">
 241    <? echo $row['transdate'] ?>
 242   </td>
 243   <td class="detail">
 244    <? echo $row['invnumber'] ?>
 245   </td>
 246 <?php if ($form_billcode) { ?>
 247   <td class="detail" align='right'>
 248    <?php bucks($row['fxsellprice']) ?>
 249   </td>
 250 <?php } ?>
 251 <?php if ($form_billcode) { ?>
 252   <td class="detail">
 253    <?php echo $insconame ?>
 254   </td>
 255 <?php } ?>
 256   <td class="detail">
 257    <? echo $row['memo'] ?>
 258   </td>
 259   <td class="detail" align="right">
 260    <? bucks($amount1) ?>
 261   </td>
 262   <td class="detail" align="right">
 263    <? bucks($amount2) ?>
 264   </td>
 265  </tr>
 266 <?
 267       }
 268       $doctotal1   += $amount1;
 269       $doctotal2   += $amount2;
 270       $grandtotal1 += $amount1;
 271       $grandtotal2 += $amount2;
 272     }
 273 ?>
 274
 275  <tr bgcolor="#ddddff">
 276   <td class="detail" colspan="<?php echo $form_billcode ? '6' : '4'; ?>">
 277    <?echo xl('Totals for ') . $docname ?>
 278   </td>
 279   <td class="dehead" align="right">
 280    <? bucks($doctotal1) ?>
 281   </td>
 282   <td class="dehead" align="right">
 283    <? bucks($doctotal2) ?>
 284   </td>
 285  </tr>
 286
 287  <tr bgcolor="#ffdddd">
 288   <td class="detail" colspan="<?php echo $form_billcode ? '6' : '4'; ?>">
 289    <?xl('Grand Totals','e')?>
 290   </td>
 291   <td class="dehead" align="right">
 292    <? bucks($grandtotal1) ?>
 293   </td>
 294   <td class="dehead" align="right">
 295    <? bucks($grandtotal2) ?>
 296   </td>
 297  </tr>
 298
 299 <?
 300   }
 301   SLClose();
 302 ?>
 303
 304 </table>
 305 </form>
 306 </center>
 307 </body>
 308 </html>