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
7 include_once("../globals.php");
8 include_once("../../library/patient.inc");
9 include_once("../../library/sql-ledger.inc");
10 include_once("../../library/acl.inc");
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.
18 include_once("../forms/fee_sheet/codes.php");
19 function is_clinic($code) {
21 return ($bcodes['CPT4'][xl('Lab')][$code] ||
22 $bcodes['CPT4'][xl('Immunizations')][$code] ||
23 $bcodes['HCPCS'][xl('Therapeutic Injections')][$code]);
26 function bucks($amount) {
28 printf("%.2f", $amount);
31 if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
35 $form_use_edate = $_POST['form_use_edate'];
36 $form_cptcode = trim($_POST['form_cptcode']);
37 $form_icdcode = trim($_POST['form_icdcode']);
41 <title
><?
xl('Receipts for Medical Services','e')?
></title
>
44 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0'>
47 <h2
><?
xl('Cash Receipts','e')?
></h2
>
49 <form method
='post' action
='sl_receipts_report.php'>
51 <table border
='0' cellpadding
='3'>
56 if (acl_check('acct', 'rep_a')) {
57 // Build a drop-down list of providers.
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)) {
66 echo " <option value='$provid'";
67 if ($provid == $_POST['form_doctor']) echo " selected";
68 echo ">" . $row['lname'] . ", " . $row['fname'] . "\n";
72 echo "<input type='hidden' name='form_doctor' value='" . $_SESSION['authUserID'] . "'>";
75  
;<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
>
79  
;<?
xl('From:','e')?
>
80 <input type
='text' name
='form_from_date' size
='10' value
='<? echo $_POST['form_from_date
']; ?>' title
='MM/DD/YYYY'>
82 <input type
='text' name
='form_to_date' size
='10' value
='<? echo $_POST['form_to_date
']; ?>' title
='MM/DD/YYYY'>
84 <input type
='text' name
='form_cptcode' size
='5' value
='<? echo $form_cptcode; ?>'
85 title
='<?php xl('Optional procedure code
','e
'); ?>'>
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
'); ?>'>
90 <input type
='checkbox' name
='form_details' value
='1'<?
if ($_POST['form_details']) echo " checked"; ?
>><?
xl('Details','e')?
>
92 <input type
='submit' name
='form_refresh' value
="<?xl('Refresh','e')?>">
103 <table border
='0' cellpadding
='1' cellspacing
='2' width
='98%'>
105 <tr bgcolor
="#dddddd">
107 <?
xl('Practitioner','e')?
>
113 <?
xl('Invoice','e')?
>
115 <?php
if ($form_cptcode) { ?
>
116 <td
class="dehead" align
='right'>
120 <?php
if ($form_cptcode) { ?
>
122 <?
xl('Insurance','e')?
>
126 <?
xl('Procedure','e')?
>
128 <td
class="dehead" align
="right">
131 <td
class="dehead" align
="right">
136 $chart_id_cash = SLQueryValue("select id from chart where accno = '$sl_cash_acc'");
137 if ($sl_err) die($sl_err);
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']);
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 ";
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 ";
166 if ($form_use_edate) {
167 $query .= "ar.transdate >= '$from_date' and " .
168 "ar.transdate <= '$to_date'";
170 $query .= "acc_trans.transdate >= '$from_date' and " .
171 "acc_trans.transdate <= '$to_date'";
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";
183 $query .= " order by ar.employee_id, acc_trans.transdate, ar.invnumber, acc_trans.memo";
185 echo "<!-- $query -->\n";
187 $t_res = SLQuery($query);
188 if ($sl_err) die($sl_err);
200 for ($irow = 0; $irow < SLRowCount($t_res); ++
$irow) {
201 $row = SLGetRow($t_res, $irow);
203 // If a diagnosis code was given then skip any invoices without
206 if ($row['trans_id'] == $last_trans_id) {
207 if ($skipping) continue;
208 // same invoice and not skipping, do nothing.
209 } else { // new invoice
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' )"))
222 // Get insurance company name
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'];
232 if (is_clinic($row['memo']))
233 $amount2 -= $row['amount'];
235 $amount1 -= $row['amount'];
237 if ($docid != $row['employee_id']) {
242 <tr bgcolor
="#ddddff">
243 <td
class="detail" colspan
="<?php echo $form_cptcode ? '6' : '4'; ?>">
244 <?
echo xl('Totals for ') . $docname ?
>
246 <td
class="dehead" align
="right">
247 <?
bucks($doctotal1) ?
>
249 <td
class="dehead" align
="right">
250 <?
bucks($doctotal2) ?
>
257 $docid = $row['employee_id'];
258 $docname = SLQueryValue("select name from employee where id = $docid");
259 $docnameleft = $docname;
262 if ($_POST['form_details']) {
267 <?
echo $docnameleft; $docnameleft = " " ?
>
270 <?
echo $row['transdate'] ?
>
273 <?
echo $row['invnumber'] ?
>
275 <?php
if ($form_cptcode) { ?
>
276 <td
class="detail" align
='right'>
277 <?php
bucks($row['fxsellprice']) ?
>
280 <?php
if ($form_cptcode) { ?
>
282 <?php
echo $insconame ?
>
286 <?
echo $row['memo'] ?
>
288 <td
class="detail" align
="right">
289 <?
bucks($amount1) ?
>
291 <td
class="detail" align
="right">
292 <?
bucks($amount2) ?
>
297 $doctotal1 +
= $amount1;
298 $doctotal2 +
= $amount2;
299 $grandtotal1 +
= $amount1;
300 $grandtotal2 +
= $amount2;
304 <tr bgcolor
="#ddddff">
305 <td
class="detail" colspan
="<?php echo $form_cptcode ? '6' : '4'; ?>">
306 <?
echo xl('Totals for ') . $docname ?
>
308 <td
class="dehead" align
="right">
309 <?
bucks($doctotal1) ?
>
311 <td
class="dehead" align
="right">
312 <?
bucks($doctotal2) ?
>
316 <tr bgcolor
="#ffdddd">
317 <td
class="detail" colspan
="<?php echo $form_cptcode ? '6' : '4'; ?>">
318 <?
xl('Grand Totals','e')?
>
320 <td
class="dehead" align
="right">
321 <?
bucks($grandtotal1) ?
>
323 <td
class="dehead" align
="right">
324 <?
bucks($grandtotal2) ?
>