interface/reports/receipts_by_method_report.php

   1 <?php
   2 // Copyright (C) 2006-2009 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 is a report of receipts by payer or payment method.
  10 //
  11 // The payer option means an insurance company name or "Patient".
  12 //
  13 // The payment method option is most useful for sites using
  14 // pos_checkout.php (e.g. weight loss clinics) because this plugs
  15 // a payment method like Cash, Check, VISA, etc. into the "source"
  16 // column of the SQL-Ledger acc_trans table or ar_session table.
  17
  18 require_once("../globals.php");
  19 require_once("../../library/patient.inc");
  20 require_once("../../library/sql-ledger.inc");
  21 require_once("../../library/acl.inc");
  22
  23 $insarray = array();
  24
  25 function bucks($amount) {
  26   if ($amount)
  27     printf("%.2f", $amount);
  28 }
  29
  30 function thisLineItem($patient_id, $encounter_id, $memo, $transdate,
  31   $rowmethod, $rowpayamount, $rowadjamount)
  32 {
  33   global $form_report_by, $insarray, $grandpaytotal, $grandadjtotal;
  34   if ($form_report_by != '1' || $_POST['form_details']) {
  35     showLineItem($patient_id, $encounter_id, $memo, $transdate,
  36       $rowmethod, $rowpayamount, $rowadjamount);
  37     return;
  38   }
  39   if (empty($insarray[$rowmethod])) $insarray[$rowmethod] = array(0, 0);
  40   $insarray[$rowmethod][0] += $rowpayamount;
  41   $insarray[$rowmethod][1] += $rowadjamount;
  42   $grandpaytotal  += $rowpayamount;
  43   $grandadjtotal  += $rowadjamount;
  44 }
  45
  46 function showLineItem($patient_id, $encounter_id, $memo, $transdate,
  47   $rowmethod, $rowpayamount, $rowadjamount)
  48 {
  49   global $paymethod, $paymethodleft, $methodpaytotal, $methodadjtotal,
  50     $grandpaytotal, $grandadjtotal;
  51
  52   if (! $rowmethod) $rowmethod = 'Unknown';
  53
  54   $invnumber = "$patient_id.$encounter_id";
  55
  56   if ($paymethod != $rowmethod) {
  57     if ($paymethod) {
  58       // Print method total.
  59 ?>
  60
  61  <tr bgcolor="#ddddff">
  62   <td class="detail" colspan="4">
  63    <?php echo xl('Total for ') . $paymethod ?>
  64   </td>
  65   <td class="dehead" align="right">
  66    <?php bucks($methodadjtotal) ?>
  67   </td>
  68   <td class="dehead" align="right">
  69    <?php bucks($methodpaytotal) ?>
  70   </td>
  71  </tr>
  72 <?php
  73     }
  74     $methodpaytotal = 0;
  75     $methodadjtotal  = 0;
  76     $paymethod = $rowmethod;
  77     $paymethodleft = $paymethod;
  78   }
  79
  80   if ($_POST['form_details']) {
  81 ?>
  82
  83  <tr>
  84   <td class="detail">
  85    <?php echo $paymethodleft; $paymethodleft = "&nbsp;" ?>
  86   </td>
  87   <td class="dehead">
  88    <?php echo $transdate ?>
  89   </td>
  90   <td class="detail">
  91    <?php echo $invnumber ?>
  92   </td>
  93   <td class="dehead">
  94    <?php echo $memo ?>
  95   </td>
  96   <td class="dehead" align="right">
  97    <?php bucks($rowadjamount) ?>
  98   </td>
  99   <td class="dehead" align="right">
 100    <?php bucks($rowpayamount) ?>
 101   </td>
 102  </tr>
 103 <?php
 104   }
 105   $methodpaytotal += $rowpayamount;
 106   $grandpaytotal  += $rowpayamount;
 107   $methodadjtotal += $rowadjamount;
 108   $grandadjtotal  += $rowadjamount;
 109 }
 110
 111 if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
 112
 113 $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
 114
 115 if (!$INTEGRATED_AR) SLConnect();
 116
 117 $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
 118 $form_to_date   = fixDate($_POST['form_to_date']  , date('Y-m-d'));
 119 $form_use_edate = $_POST['form_use_edate'];
 120 $form_facility  = $_POST['form_facility'];
 121 $form_report_by = $_POST['form_report_by'];
 122 ?>
 123 <html>
 124 <head>
 125 <?php if (function_exists('html_header_show')) html_header_show(); ?>
 126 <title><?xl('Receipts Summary','e')?></title>
 127 </head>
 128
 129 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
 130 <center>
 131
 132 <h2><?php xl('Receipts Summary','e') ?></h2>
 133
 134 <form method='post' action='receipts_by_method_report.php'>
 135
 136 <table border='0' cellpadding='3'>
 137
 138  <tr>
 139   <td>
 140    Report by
 141 <?php
 142 echo "   <select name='form_report_by'>\n";
 143 foreach (array(1 => 'Payer', 2 => 'Payment Method', 3 => 'Check Number') as $key => $value) {
 144   echo "    <option value='$key'";
 145   if ($key == $form_report_by) echo ' selected';
 146   echo ">" . xl($value) . "</option>\n";
 147 }
 148 echo "   </select>&nbsp;\n";
 149
 150 // Build a drop-down list of facilities.
 151 //
 152 $query = "SELECT id, name FROM facility ORDER BY name";
 153 $fres = sqlStatement($query);
 154 echo "   <select name='form_facility'>\n";
 155 echo "    <option value=''>-- All Facilities --\n";
 156 while ($frow = sqlFetchArray($fres)) {
 157   $facid = $frow['id'];
 158   echo "    <option value='$facid'";
 159   if ($facid == $form_facility) echo " selected";
 160   echo ">" . $frow['name'] . "\n";
 161 }
 162 echo "   </select>\n";
 163 ?>
 164    &nbsp;
 165    <input type='checkbox' name='form_details' value='1'<? if ($_POST['form_details']) echo " checked"; ?>><?xl('Details','e')?>
 166   </td>
 167  </tr>
 168
 169  <tr>
 170   <td>
 171    <select name='form_use_edate'>
 172     <option value='0'><?php xl('Payment Date','e'); ?></option>
 173     <option value='1'<?php if ($form_use_edate) echo ' selected' ?>><?php xl('Invoice Date','e'); ?></option>
 174    </select>
 175    &nbsp;<?xl('From:','e')?>
 176    <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $form_from_date ?>'
 177     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 178    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 179     id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 180     title='<?php xl('Click here to choose a date','e'); ?>'>
 181    &nbsp;<?php xl('To:','e'); ?>
 182    <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_date ?>'
 183     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 184    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 185     id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 186     title='<?php xl('Click here to choose a date','e'); ?>'>
 187    &nbsp;
 188    <input type='submit' name='form_refresh' value="<?xl('Refresh','e')?>">
 189    &nbsp;
 190    <input type='button' value='<?php xl('Print','e'); ?>' onclick='window.print()' />
 191   </td>
 192  </tr>
 193
 194  <tr>
 195   <td height="1">
 196   </td>
 197  </tr>
 198
 199 </table>
 200
 201 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 202
 203  <tr bgcolor="#dddddd">
 204   <td class="dehead">
 205    <?xl('Method','e')?>
 206   </td>
 207   <td class="dehead">
 208    <?xl('Date','e')?>
 209   </td>
 210   <td class="dehead">
 211    <?xl('Invoice','e')?>
 212   </td>
 213   <td class="dehead">
 214    <?xl('Procedure','e')?>
 215   </td>
 216   <td class="dehead" align="right">
 217    <?xl('Adjustments','e')?>
 218   </td>
 219   <td class="dehead" align="right">
 220    <?xl('Payments','e')?>
 221   </td>
 222  </tr>
 223 <?php
 224
 225 if (!$INTEGRATED_AR) {
 226   $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
 227   if ($sl_err) die($sl_err);
 228   $chart_id_income = SLQueryValue("select id from chart where accno = '$sl_income_acc'");
 229   if ($sl_err) die($sl_err);
 230 }
 231
 232 if ($_POST['form_refresh']) {
 233   $from_date = $form_from_date;
 234   $to_date   = $form_to_date;
 235
 236   $paymethod   = "";
 237   $paymethodleft = "";
 238   $methodpaytotal = 0;
 239   $grandpaytotal  = 0;
 240   $methodadjtotal  = 0;
 241   $grandadjtotal  = 0;
 242
 243   if ($INTEGRATED_AR) {
 244
 245     // Get co-pays using the encounter date as the pay date.  These will
 246     // always be considered patient payments.
 247     //
 248     $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, " .
 249       "fe.date, fe.facility_id " .
 250       "FROM billing AS b " .
 251       "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 252       "WHERE b.code_type = 'COPAY' AND b.activity = 1 AND b.fee != 0 AND " .
 253       "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59'";
 254     // If a facility was specified.
 255     if ($form_facility) $query .= " AND fe.facility_id = '$form_facility'";
 256     $query .= " ORDER BY fe.date, b.pid, b.encounter, fe.id";
 257     //
 258     $res = sqlStatement($query);
 259     while ($row = sqlFetchArray($res)) {
 260       $rowmethod = $form_report_by == 1 ? 'Patient' : 'Co-Pay';
 261       thisLineItem($row['pid'], $row['encounter'], $row['code_text'],
 262         substr($row['date'], 0, 10), $rowmethod, 0 - $row['fee'], 0);
 263     }
 264
 265     // Get all other payments and adjustments and their dates, corresponding
 266     // payers and check reference data, and the encounter dates separately.
 267     //
 268     $query = "SELECT a.pid, a.encounter, a.post_time, a.pay_amount, " .
 269       "a.adj_amount, a.memo, a.session_id, a.code, fe.id, fe.date, " .
 270       "s.deposit_date, s.payer_id, s.reference, i.name " .
 271       "FROM ar_activity AS a " .
 272       "JOIN form_encounter AS fe ON fe.pid = a.pid AND fe.encounter = a.encounter " .
 273       "JOIN forms AS f ON f.pid = a.pid AND f.encounter = a.encounter AND f.formdir = 'newpatient' " .
 274       "LEFT JOIN ar_session AS s ON s.session_id = a.session_id " .
 275       "LEFT JOIN insurance_companies AS i ON i.id = s.payer_id " .
 276       "WHERE ( a.pay_amount != 0 OR a.adj_amount != 0 )";
 277     //
 278     if ($form_use_edate) {
 279       $query .= " AND fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59'";
 280     } else {
 281       $query .= " AND ( ( s.deposit_date IS NOT NULL AND " .
 282         "s.deposit_date >= '$from_date' AND s.deposit_date <= '$to_date' ) OR " .
 283         "( s.deposit_date IS NULL AND a.post_time >= '$from_date 00:00:00' AND " .
 284         "a.post_time <= '$to_date 23:59:59' ) )";
 285     }
 286     // If a facility was specified.
 287     if ($form_facility) $query .= " AND fe.facility_id = '$form_facility'";
 288     //
 289     if ($form_use_edate) {
 290       $query .= " ORDER BY s.reference, fe.date, a.pid, a.encounter, fe.id";
 291     } else {
 292       $query .= " ORDER BY s.reference, s.deposit_date, a.post_time, a.pid, a.encounter, fe.id";
 293     }
 294     //
 295     $res = sqlStatement($query);
 296     while ($row = sqlFetchArray($res)) {
 297       if ($form_use_edate) {
 298         $thedate = substr($row['date'], 0, 10);
 299       } else if (!empty($row['deposit_date'])) {
 300         $thedate = $row['deposit_date'];
 301       } else {
 302         $thedate = substr($row['post_time'], 0, 10);
 303       }
 304       // Compute reporting key: insurance company name or payment method.
 305       if ($form_report_by == '1') {
 306         if (empty($row['payer_id'])) {
 307           $rowmethod = '';
 308         } else {
 309           if (empty($row['name'])) $rowmethod = xl('Unnamed insurance company');
 310           else $rowmethod = $row['name'];
 311         }
 312       }
 313       else {
 314         if (empty($row['session_id'])) {
 315           $rowmethod = trim($row['memo']);
 316         } else {
 317           $rowmethod = trim($row['reference']);
 318         }
 319         if ($form_report_by != '3') {
 320           // Extract only the first word as the payment method because any
 321           // following text will be some petty detail like a check number.
 322           $rowmethod = substr($rowmethod, 0, strcspn($rowmethod, ' /'));
 323         }
 324       }
 325       //
 326       thisLineItem($row['pid'], $row['encounter'], $row['code'],
 327         $thedate, $rowmethod, $row['pay_amount'], $row['adj_amount']);
 328     }
 329   } // end $INTEGRATED_AR
 330   else {
 331     $query = "SELECT acc_trans.amount, acc_trans.transdate, acc_trans.memo, " .
 332       "replace(acc_trans.source, 'InvAdj ', '') AS source, " .
 333       "acc_trans.chart_id, ar.invnumber, ar.employee_id, ar.notes " .
 334       "FROM acc_trans, ar WHERE " .
 335       "( acc_trans.chart_id = $chart_id_cash OR " .
 336       "( acc_trans.chart_id = $chart_id_income AND " .
 337       "acc_trans.source LIKE 'InvAdj %' ) ) AND " .
 338       "ar.id = acc_trans.trans_id AND ";
 339     if ($form_use_edate) {
 340       $query .= "ar.transdate >= '$from_date' AND " .
 341       "ar.transdate <= '$to_date'";
 342     } else {
 343       $query .= "acc_trans.transdate >= '$from_date' AND " .
 344       "acc_trans.transdate <= '$to_date'";
 345     }
 346     $query .= " ORDER BY source, acc_trans.transdate, ar.invnumber, acc_trans.memo";
 347
 348     // echo "<!-- $query -->\n";
 349
 350     $t_res = SLQuery($query);
 351     if ($sl_err) die($sl_err);
 352
 353     for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 354       $row = SLGetRow($t_res, $irow);
 355       list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 356
 357       // If a facility was specified then skip invoices whose encounters
 358       // do not indicate that facility.
 359       if ($form_facility) {
 360         $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
 361           "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 362           "facility_id = '$form_facility'");
 363         if (empty($tmp['count'])) continue;
 364       }
 365
 366       $rowpayamount = 0 - $row['amount'];
 367       $rowadjamount = 0;
 368       if ($row['chart_id'] == $chart_id_income) {
 369         $rowadjamount = $rowpayamount;
 370         $rowpayamount = 0;
 371       }
 372
 373       // Compute reporting key: insurance company name or payment method.
 374       if ($form_report_by == '1') {
 375         $rowmethod = '';
 376         $rowsrc = strtolower($row['source']);
 377         $insgot = strtolower($row['notes']);
 378         foreach (array('ins1', 'ins2', 'ins3') as $value) {
 379           if (strpos($rowsrc, $value) !== false) {
 380             $i = strpos($insgot, $value);
 381             if ($i !== false) {
 382               $j = strpos($insgot, "\n", $i);
 383               if (!$j) $j = strlen($insgot);
 384               $rowmethod = trim(substr($row['notes'], $i + 5, $j - $i - 5));
 385               break;
 386             }
 387           }
 388         } // end foreach
 389       } // end reporting by payer
 390       else {
 391         $rowmethod = trim($row['source']);
 392         if ($form_report_by != '3') {
 393           // Extract only the first word as the payment method because any
 394           // following text will be some petty detail like a check number.
 395           $rowmethod = substr($rowmethod, 0, strcspn($rowmethod, ' /'));
 396         }
 397       } // end reporting by method
 398
 399       thisLineItem($patient_id, $encounter_id, $row['memo'],
 400         $row['transdate'], $rowmethod, $rowpayamount, $rowadjamount);
 401     } // end for
 402   } // end not $INTEGRATED_AR
 403
 404   // Not payer summary: print last method total.
 405   if ($form_report_by != '1' || $_POST['form_details']) {
 406 ?>
 407  <tr bgcolor="#ddddff">
 408   <td class="detail" colspan="4">
 409    <?echo xl('Total for ') . $paymethod ?>
 410   </td>
 411   <td class="dehead" align="right">
 412    <?php bucks($methodadjtotal) ?>
 413   </td>
 414   <td class="dehead" align="right">
 415    <?php bucks($methodpaytotal) ?>
 416   </td>
 417  </tr>
 418 <?php
 419   }
 420
 421   // Payer summary: need to sort and then print it all.
 422   else {
 423     ksort($insarray);
 424     foreach ($insarray as $key => $value) {
 425       if (empty($key)) $key = xl('Patient');
 426 ?>
 427  <tr bgcolor="#ddddff">
 428   <td class="detail" colspan="4">
 429    <?php echo $key; ?>
 430   </td>
 431   <td class="dehead" align="right">
 432    <?php bucks($value[1]); ?>
 433   </td>
 434   <td class="dehead" align="right">
 435    <?php bucks($value[0]); ?>
 436   </td>
 437  </tr>
 438 <?php
 439     } // end foreach
 440   } // end payer summary
 441 ?>
 442  <tr bgcolor="#ffdddd">
 443   <td class="detail" colspan="4">
 444    <?php xl('Grand Total','e') ?>
 445   </td>
 446   <td class="dehead" align="right">
 447    <?php bucks($grandadjtotal) ?>
 448   </td>
 449   <td class="dehead" align="right">
 450    <?php bucks($grandpaytotal) ?>
 451   </td>
 452  </tr>
 453
 454 <?php
 455 } // end form refresh
 456 if (!$INTEGRATED_AR) SLClose();
 457 ?>
 458
 459 </table>
 460 </form>
 461 </center>
 462 </body>
 463
 464 <!-- stuff for the popup calendar -->
 465 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 466 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 467 <script type="text/javascript" src="../../library/dynarch_calendar_en.js"></script>
 468 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 469 <script language="Javascript">
 470  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 471  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 472 </script>
 473
 474 </html>