interface/reports/collections_report.php

   1 <?php
   2  // Copyright (C) 2006 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  include_once("../globals.php");
  10  include_once("../../library/patient.inc");
  11  include_once("../../library/sql-ledger.inc");
  12  include_once("../../library/invoice_summary.inc.php");
  13  include_once("../../custom/statement.inc.php");
  14  include_once("../../library/sl_eob.inc.php");
  15
  16  $alertmsg = '';
  17  $bgcolor = "#aaaaaa";
  18  $export_patient_count = 0;
  19  $export_dollars = 0;
  20
  21  function bucks($amount) {
  22   if ($amount)
  23    printf("%.2f", $amount);
  24  }
  25
  26  $today = date("Y-m-d");
  27
  28  $form_date      = fixDate($_POST['form_date'], "");
  29  $form_to_date   = fixDate($_POST['form_to_date'], "");
  30  $is_due_ins     = $_POST['form_category'] == xl('Due Ins');
  31  $is_due_pt      = $_POST['form_category'] == xl('Due Pt');
  32
  33
  34  $grand_total_charges     = 0;
  35  $grand_total_adjustments = 0;
  36  $grand_total_paid        = 0;
  37
  38  SLConnect();
  39
  40 function endPatient($ptrow) {
  41   global $export_patient_count, $export_dollars, $bgcolor;
  42   global $grand_total_charges, $grand_total_adjustments, $grand_total_paid;
  43   global $is_due_ins;
  44
  45   if (!$ptrow['pid']) return;
  46
  47   $pt_balance = $ptrow['amount'] - $ptrow['paid'];
  48
  49   if ($_POST['form_export']) {
  50     // This is a fixed-length format used by Transworld Systems.  Your
  51     // needs will surely be different, so consider this just an example.
  52     //
  53     echo "1896H"; // client number goes here
  54     echo "000";   // filler
  55     echo sprintf("%-30s", substr($ptrow['ptname'], 0, 30));
  56     echo sprintf("%-30s", " ");
  57     echo sprintf("%-30s", substr($ptrow['address1'], 0, 30));
  58     echo sprintf("%-15s", substr($ptrow['city'], 0, 15));
  59     echo sprintf("%-2s", substr($ptrow['state'], 0, 2));
  60     echo sprintf("%-5s", $ptrow['zipcode'] ? substr($ptrow['zipcode'], 0, 5) : '00000');
  61     echo "1";                      // service code
  62     echo sprintf("%010.0f", $ptrow['pid']); // transmittal number = patient id
  63     echo " ";                      // filler
  64     echo sprintf("%-15s", substr($ptrow['ss'], 0, 15));
  65     echo substr($ptrow['dos'], 5, 2) . substr($ptrow['dos'], 8, 2) . substr($ptrow['dos'], 2, 2);
  66     echo sprintf("%08.0f", $pt_balance * 100);
  67     echo sprintf("%-9s\n", " ");
  68
  69     if (!$_POST['form_without']) {
  70       sqlStatement("UPDATE patient_data SET " .
  71         "genericname2 = 'Billing', " .
  72         "genericval2 = 'IN COLLECTIONS " . date("Y-m-d") . "' " .
  73         "WHERE pid = '" . $ptrow['pid'] . "'");
  74     }
  75     $export_patient_count += 1;
  76     $export_dollars += $pt_balance;
  77   }
  78   else {
  79     if ($ptrow['count'] > 1) {
  80       echo " <tr bgcolor='$bgcolor'>\n";
  81       echo "  <td class='detail' colspan='" . ($is_due_ins ? '5' : '4') . "'>\n";
  82       echo "   &nbsp;\n";
  83       echo "  </td>\n";
  84       echo "  <td class='detotal' colspan='5'>\n";
  85       echo "   &nbsp;Total Patient Balance:\n";
  86       echo "  </td>\n";
  87       echo "  <td class='detotal' align='right'>\n";
  88       echo "   &nbsp;" . sprintf("%.2f", $pt_balance) . "&nbsp;\n";
  89       echo "  </td>\n";
  90       echo "  <td class='detail' colspan='2'>\n";
  91       echo "   &nbsp;\n";
  92       echo "  </td>\n";
  93       echo " </tr>\n";
  94     }
  95   }
  96   $grand_total_charges     += $ptrow['charges'];
  97   $grand_total_adjustments += $ptrow['adjustments'];
  98   $grand_total_paid        += $ptrow['paid'];
  99 }
 100 ?>
 101 <html>
 102 <head>
 103 <link rel=stylesheet href="<?echo $css_header;?>" type="text/css">
 104 <title><?xl('Collections Report','e')?></title>
 105 <style type="text/css">
 106  body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
 107  .dehead    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:bold }
 108  .detail    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:normal }
 109  .detotal   { color:#996600; font-family:sans-serif; font-size:10pt; font-weight:normal }
 110 </style>
 111
 112 <script language="JavaScript">
 113
 114 function checkAll(checked) {
 115  var f = document.forms[0];
 116  for (var i = 0; i < f.elements.length; ++i) {
 117   var ename = f.elements[i].name;
 118   if (ename.indexOf('form_cb[') == 0)
 119    f.elements[i].checked = checked;
 120  }
 121 }
 122
 123 </script>
 124
 125 </head>
 126
 127 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
 128 <center>
 129
 130 <form method='post' action='collections_report.php' enctype='multipart/form-data'>
 131
 132 <table border='0' cellpadding='5' cellspacing='0'>
 133
 134  <tr>
 135   <td height="1" colspan="4">
 136   </td>
 137  </tr>
 138
 139  <tr bgcolor='#ddddff'>
 140   <td>
 141    <?xl('Svc Date:','e')?>
 142    <input type='text' name='form_date' size='10' value='<?php echo $_POST['form_date']; ?>'
 143     title='<?xl("Date of service mm/dd/yyyy","e")?>'>
 144   </td>
 145   <td>
 146    <?xl('To:','e')?>
 147    <input type='text' name='form_to_date' size='10' value='<?php echo $_POST['form_to_date']; ?>'
 148     title='<?xl("Ending DOS mm/dd/yyyy if you wish to enter a range","e")?>'>
 149   </td>
 150   <td>
 151    <select name='form_category'>
 152 <?php
 153  foreach (array(xl('Open'), xl('Due Pt'), xl('Due Ins'), xl('Credits')) as $value) {
 154   echo "    <option value='$value'";
 155   if ($_POST['form_category'] == $value) echo " selected";
 156   echo ">$value</option>\n";
 157  }
 158 ?>
 159    </select>
 160   </td>
 161   <td>
 162    <input type='submit' name='form_search' value='<?xl("Search","e")?>'>
 163   </td>
 164  </tr>
 165
 166  <tr>
 167   <td height="1" colspan="4">
 168   </td>
 169  </tr>
 170
 171 </table>
 172
 173 <?php
 174   if ($_POST['form_search'] || $_POST['form_export']) {
 175     $where = "";
 176
 177     if ($_POST['form_export']) {
 178       $where = "( 1 = 2";
 179       foreach ($_POST['form_cb'] as $key => $value) $where .= " OR ar.customer_id = $key";
 180       $where .= ' )';
 181     }
 182
 183     if ($form_date) {
 184       if ($where) $where .= " AND ";
 185       $date1 = substr($form_date, 0, 4) . substr($form_date, 5, 2) .
 186         substr($form_date, 8, 2);
 187       if ($form_to_date) {
 188         $date2 = substr($form_to_date, 0, 4) . substr($form_to_date, 5, 2) .
 189           substr($form_to_date, 8, 2);
 190         $where .= "((CAST (substring(ar.invnumber from position('.' in ar.invnumber) + 1 for 8) AS integer) " .
 191           "BETWEEN '$date1' AND '$date2')";
 192         $tmp = "date >= '$form_date' AND date <= '$form_to_date'";
 193       }
 194       else {
 195         // This catches old converted invoices where we have no encounters:
 196         $where .= "(ar.invnumber LIKE '%.$date1'";
 197         $tmp = "date = '$form_date'";
 198       }
 199       // Pick out the encounters from MySQL with the desired DOS:
 200       $rez = sqlStatement("SELECT pid, encounter FROM form_encounter WHERE $tmp");
 201       while ($row = sqlFetchArray($rez)) {
 202         $where .= " OR ar.invnumber = '" . $row['pid'] . "." . $row['encounter'] . "'";
 203       }
 204       $where .= ")";
 205     }
 206
 207     if (! $where) {
 208       $where = "1 = 1";
 209     }
 210
 211     $query = "SELECT ar.id, ar.invnumber, ar.duedate, ar.amount, ar.paid, " .
 212       "ar.intnotes, ar.notes, ar.shipvia, " .
 213       "customer.id AS custid, customer.name, customer.address1, " .
 214       "customer.city, customer.state, customer.zipcode, customer.phone, " .
 215       "(SELECT SUM(invoice.fxsellprice) FROM invoice WHERE " .
 216       "invoice.trans_id = ar.id AND invoice.fxsellprice > 0) AS charges, " .
 217       "(SELECT SUM(invoice.fxsellprice) FROM invoice WHERE " .
 218       "invoice.trans_id = ar.id AND invoice.fxsellprice < 0) AS adjustments " .
 219       "FROM ar JOIN customer ON customer.id = ar.customer_id " .
 220       "WHERE ( $where ) ";
 221     if ($_POST['form_search']) {
 222       $query .= "AND ar.amount != ar.paid ";
 223     }
 224     $query .= "ORDER BY ar.invnumber";
 225
 226     // echo "<!-- $query -->\n"; // debugging
 227
 228     $t_res = SLQuery($query);
 229     if ($sl_err) die($sl_err);
 230     $num_invoices = SLRowCount($t_res);
 231
 232     //////////////////////////////////////////////////////////////////
 233
 234     $rows = array();
 235     for ($irow = 0; $irow < $num_invoices; ++$irow) {
 236       $row = SLGetRow($t_res, $irow);
 237       $pt_balance = sprintf("%.2f",$row['amount']) - sprintf("%.2f",$row['paid']);
 238
 239       if ($_POST['form_category'] == 'Credits') {
 240         if ($pt_balance > 0) continue;
 241       }
 242       // else {
 243       //   if ($pt_balance < 0) continue;
 244       // }
 245
 246       // $duncount was originally supposed to be the number of times that
 247       // the patient was sent a statement for this invoice.
 248       //
 249       $duncount = substr_count(strtolower($row['intnotes']), "statement sent");
 250
 251       // But if we have not yet billed the patient, then compute $duncount as a
 252       // negative count of the number of insurance plans for which we have not
 253       // yet closed out insurance.  Here we also compute $insname as the name of
 254       // the insurance plan from which we are awaiting payment.
 255       //
 256       $insname = '';
 257       if (! $duncount) {
 258         $insgot = strtolower($row['notes']);
 259         $inseobs = strtolower($row['shipvia']);
 260         foreach (array('ins1', 'ins2', 'ins3') as $value) {
 261           $i = strpos($insgot, $value);
 262           if ($i !== false && strpos($inseobs, $value) === false) {
 263             --$duncount;
 264             if (!$insname && $is_due_ins) {
 265               $j = strpos($insgot, "\n", $i);
 266               if (!$j) $j = strlen($insgot);
 267               $insname = trim(substr($row['notes'], $i + 5, $j - $i - 5));
 268             }
 269           }
 270         }
 271       }
 272
 273       // An invoice is now due from the patient if money is owed and we are
 274       // not waiting for insurance to pay.  We no longer look at the due date
 275       // for this.
 276       //
 277       $isduept = ($duncount >= 0) ? " checked" : "";
 278
 279       // Skip invoices not in the desired "Due..." category.
 280       //
 281       if ($is_due_ins && $duncount >= 0) continue;
 282       if ($is_due_pt  && $duncount <  0) continue;
 283
 284       $row['duncount'] = $duncount;
 285
 286       // Determine the date of service.  An 8-digit encounter number is
 287       // presumed to be a date of service imported during conversion.
 288       // Otherwise look it up in the form_encounter table.
 289       //
 290       $svcdate = "";
 291       list($pid, $encounter) = explode(".", $row['invnumber']);
 292       if (strlen($encounter) == 8) {
 293         $svcdate = substr($encounter, 0, 4) . "-" . substr($encounter, 4, 2) .
 294           "-" . substr($encounter, 6, 2);
 295       }
 296       else if ($encounter) {
 297         $tmp = sqlQuery("SELECT date FROM form_encounter WHERE " .
 298           "encounter = $encounter");
 299         $svcdate = substr($tmp['date'], 0, 10);
 300       }
 301
 302       $row['dos'] = $svcdate;
 303
 304       $pdrow = sqlQuery("SELECT pd.fname, pd.lname, pd.mname, pd.ss, " .
 305         "pd.genericname2, pd.genericval2 FROM " .
 306         "integration_mapping AS im, patient_data AS pd WHERE " .
 307         "im.foreign_id = " . $row['custid'] . " AND " .
 308         "im.foreign_table = 'customer' AND " .
 309         "pd.id = im.local_id");
 310
 311       $row['ss'] = $pdrow['ss'];
 312       $row['billnote'] = ($pdrow['genericname2'] == 'Billing') ? $pdrow['genericval2'] : '';
 313
 314       $ptname = $pdrow['lname'] . ", " . $pdrow['fname'];
 315       if ($pdrow['mname']) $ptname .= " " . substr($pdrow['mname'], 0, 1);
 316
 317       // $rows[$ptname] = $row;
 318       $rows[$insname . '|' . $ptname . '|' . $encounter] = $row; // new
 319     }
 320
 321     ksort($rows);
 322
 323     if ($_POST['form_export']) {
 324       echo "<textarea rows='35' cols='100' readonly>";
 325     }
 326     else {
 327 ?>
 328
 329 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 330
 331  <tr bgcolor="#dddddd">
 332 <?php if ($is_due_ins) { ?>
 333   <td class="dehead">
 334    &nbsp;<?xl('Insurance','e')?>
 335   </td>
 336 <?php } ?>
 337   <td class="dehead">
 338    &nbsp;<?xl('Name','e')?>
 339   </td>
 340   <td class="dehead">
 341    &nbsp;<?xl('SSN','e')?>
 342   </td>
 343   <td class="dehead">
 344    &nbsp;<?xl('Phone','e')?>
 345   </td>
 346   <td class="dehead">
 347    &nbsp;<?xl('City','e')?>
 348   </td>
 349   <td class="dehead">
 350    &nbsp;<?php xl('Invoice','e') ?>
 351   </td>
 352   <td class="dehead">
 353    &nbsp;<?php xl('Svc Date','e') ?>
 354   </td>
 355   <td class="dehead" align="right">
 356    <?php xl('Charge','e') ?>&nbsp;
 357   </td>
 358   <td class="dehead" align="right">
 359    <?php xl('Adjust','e') ?>&nbsp;
 360   </td>
 361   <td class="dehead" align="right">
 362    <?php xl('Paid','e') ?>&nbsp;
 363   </td>
 364   <td class="dehead" align="right">
 365    <?php xl('Balance','e') ?>&nbsp;
 366   </td>
 367   <td class="dehead" align="center">
 368    <?php xl('Prv','e') ?>
 369   </td>
 370   <td class="dehead" align="center">
 371    <?php xl('Sel','e') ?>
 372   </td>
 373  </tr>
 374
 375 <?php
 376     }
 377
 378     $ptrow = array('insname' => '', 'pid' => 0);
 379     $orow = -1;
 380
 381     foreach ($rows as $key => $row) {
 382       list($insname, $ptname, $trash) = explode('|', $key);
 383       list($pid, $encounter) = explode(".", $row['invnumber']);
 384
 385       if ($insname != $ptrow['insname'] || $pid != $ptrow['pid']) {
 386         // For the report, this will write the patient totals.  For the
 387         // export this writes everything for the patient:
 388         endPatient($ptrow);
 389         $bgcolor = ((++$orow & 1) ? "#ffdddd" : "#ddddff");
 390         $ptrow = array('insname' => $insname, 'ptname' => $ptname, 'pid' => $pid, 'count' => 1);
 391         foreach ($row as $key => $value) $ptrow[$key] = $value;
 392       } else {
 393         $ptrow['amount']      += $row['amount'];
 394         $ptrow['paid']        += $row['paid'];
 395         $ptrow['charges']     += $row['charges'];
 396         $ptrow['adjustments'] += $row['adjustments'];
 397         ++$ptrow['count'];
 398       }
 399
 400       if (!$_POST['form_export']) {
 401
 402         $in_collections = stristr($row['billnote'], 'IN COLLECTIONS') !== false;
 403
 404 ?>
 405  <tr bgcolor='<?php echo $bgcolor ?>'>
 406 <?php
 407         if ($ptrow['count'] == 1) {
 408           if ($is_due_ins) {
 409             echo "  <td class='detail'>\n";
 410             echo "   &nbsp;$insname\n";
 411             echo "  </td>\n";
 412           }
 413           echo "  <td class='detail'>\n";
 414           echo "   &nbsp;$ptname\n";
 415           echo "  </td>\n";
 416           echo "  <td class='detail'>\n";
 417           echo "   &nbsp;" . $row['ss'] . "\n";
 418           echo "  </td>\n";
 419           echo "  <td class='detail'>\n";
 420           echo "   &nbsp;" . $row['phone'] . "\n";
 421           echo "  </td>\n";
 422           echo "  <td class='detail'>\n";
 423           echo "   &nbsp;" . $row['city'] . "\n";
 424           echo "  </td>\n";
 425         } else {
 426           echo "  <td class='detail' colspan='" . ($is_due_ins ? '5' : '4') . "'>\n";
 427           echo "   &nbsp;\n";
 428           echo "  </td>\n";
 429         }
 430 ?>
 431   <td class="detail">
 432    &nbsp;<a href="../billing/sl_eob_invoice.php?id=<?php echo $row['id'] ?>"
 433     target="_blank"><?php echo $row['invnumber'] ?></a>
 434   </td>
 435   <td class="detail">
 436    &nbsp;<?php echo $row['dos']; ?>
 437   </td>
 438   <td class="detail" align="right">
 439    <?php bucks($row['charges']) ?>&nbsp;
 440   </td>
 441   <td class="detail" align="right">
 442    <?php bucks($row['adjustments']) ?>&nbsp;
 443   </td>
 444   <td class="detail" align="right">
 445    <?php bucks($row['paid']) ?>&nbsp;
 446   </td>
 447   <td class="detail" align="right">
 448    <?php bucks($row['charges'] + $row['adjustments'] - $row['paid']) ?>&nbsp;
 449   </td>
 450   <td class="detail" align="center">
 451    <?php echo $row['duncount'] ? $row['duncount'] : "&nbsp;" ?>
 452   </td>
 453   <td class="detail" align="center">
 454 <?php
 455         if ($ptrow['count'] == 1) {
 456           if ($in_collections) {
 457             echo "   <b><font color='red'>IC</font></b>\n";
 458           } else {
 459             echo "   <input type='checkbox' name='form_cb[" . $row['custid'] . "]' />\n";
 460           }
 461         } else {
 462           echo "   &nbsp;\n";
 463         }
 464 ?>
 465   </td>
 466  </tr>
 467 <?
 468       } // end not $form_export
 469     } // end loop
 470
 471     endPatient($ptrow);
 472
 473     if ($_POST['form_export']) {
 474       echo "</textarea>\n";
 475       $alertmsg .= "$export_patient_count patients representing $" .
 476         sprintf("%.2f", $export_dollars) . " have been exported ";
 477       if ($_POST['form_without']) {
 478         $alertmsg .= "but NOT flagged as in collections.";
 479       } else {
 480         $alertmsg .= "AND flagged as in collections.";
 481       }
 482     }
 483     else {
 484       echo " <tr bgcolor='#ffffff'>\n";
 485       echo "  <td class='detail' colspan='" . ($is_due_ins ? '5' : '4') . "'>\n";
 486       echo "   &nbsp;\n";
 487       echo "  </td>\n";
 488       echo "  <td class='dehead' colspan='2'>\n";
 489       echo "   &nbsp;Report Totals:\n";
 490       echo "  </td>\n";
 491       echo "  <td class='dehead' align='right'>\n";
 492       echo "   &nbsp;" . sprintf("%.2f", $grand_total_charges) . "&nbsp;\n";
 493       echo "  </td>\n";
 494       echo "  <td class='dehead' align='right'>\n";
 495       echo "   &nbsp;" . sprintf("%.2f", $grand_total_adjustments) . "&nbsp;\n";
 496       echo "  </td>\n";
 497       echo "  <td class='dehead' align='right'>\n";
 498       echo "   &nbsp;" . sprintf("%.2f", $grand_total_paid) . "&nbsp;\n";
 499       echo "  </td>\n";
 500       echo "  <td class='dehead' align='right'>\n";
 501       echo "   " . sprintf("%.2f", $grand_total_charges +
 502            $grand_total_adjustments - $grand_total_paid) . "&nbsp;\n";
 503       echo "  </td>\n";
 504       echo "  <td class='detail' colspan='2'>\n";
 505       echo "   &nbsp;\n";
 506       echo "  </td>\n";
 507       echo " </tr>\n";
 508       echo "</table>\n";
 509     }
 510   } // end if form_search
 511   SLClose();
 512 ?>
 513
 514 <p>
 515 <?php if (!$_POST['form_export']) { ?>
 516 <input type='button' value='Select All' onclick='checkAll(true)' /> &nbsp;
 517 <input type='button' value='Clear All' onclick='checkAll(false)' /> &nbsp;
 518 <input type='submit' name='form_export' value='Export Selected to Collections' /> &nbsp;
 519 <input type='checkbox' name='form_without' value='1' /> <?php xl('Without Update','e') ?>
 520 <?php } ?>
 521 </p>
 522
 523 </form>
 524 </center>
 525 <script language="JavaScript">
 526 <?php
 527 if ($alertmsg) {
 528   echo "alert('" . htmlentities($alertmsg) . "');\n";
 529 }
 530 ?>
 531 </script>
 532 </body>
 533 </html>