2 // Copyright (C) 2006-2010 Rod Roark <rod@sunsetsystems.com>
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.
9 // This is a report of sales by item description. It's driven from
10 // SQL-Ledger so as to include all types of invoice items.
12 require_once("../globals.php");
13 require_once("$srcdir/patient.inc");
14 require_once("$srcdir/sql-ledger.inc");
15 require_once("$srcdir/acl.inc");
16 require_once("$srcdir/formatting.inc.php");
17 require_once "$srcdir/options.inc.php";
18 require_once "$srcdir/formdata.inc.php";
20 function bucks($amount) {
21 if ($amount) echo oeFormatMoney($amount);
24 function display_desc($desc) {
25 if (preg_match('/^\S*?:(.+)$/', $desc, $matches)) {
31 function thisLineItem($patient_id, $encounter_id, $rowcat, $description, $transdate, $qty, $amount, $irnumber='') {
32 global $product, $category, $producttotal, $productqty, $cattotal, $catqty, $grandtotal, $grandqty;
33 global $productleft, $catleft;
35 $invnumber = $irnumber ?
$irnumber : "$patient_id.$encounter_id";
36 $rowamount = sprintf('%01.2f', $amount);
38 if (empty($rowcat)) $rowcat = 'None';
39 $rowproduct = $description;
40 if (! $rowproduct) $rowproduct = 'Unknown';
42 if ($product != $rowproduct ||
$category != $rowcat) {
44 // Print product total.
45 if ($_POST['form_csvexport']) {
46 if (! $_POST['form_details']) {
47 echo '"' . display_desc($category) . '",';
48 echo '"' . display_desc($product) . '",';
49 echo '"' . $productqty . '",';
50 echo '"'; bucks($producttotal); echo '"' . "\n";
55 <tr bgcolor
="#ddddff">
57 <?php
echo display_desc($catleft); $catleft = " "; ?
>
59 <td
class="detail" colspan
="3">
60 <?php
if ($_POST['form_details']) echo xl('Total for') . ' '; echo display_desc($product); ?
>
63 <?php
echo $productqty; ?
>
66 <?php
bucks($producttotal); ?
>
70 } // End not csv export
74 $product = $rowproduct;
75 $productleft = $product;
78 if ($category != $rowcat) {
80 // Print category total.
81 if (!$_POST['form_csvexport']) {
84 <tr bgcolor
="#ffdddd">
88 <td
class="detail" colspan
="3">
89 <?php
echo xl('Total for category') . ' '; echo display_desc($category); ?
>
92 <?php
echo $catqty; ?
>
95 <?php
bucks($cattotal); ?
>
99 } // End not csv export
104 $catleft = $category;
107 if ($_POST['form_details']) {
108 if ($_POST['form_csvexport']) {
109 echo '"' . display_desc($category ) . '",';
110 echo '"' . display_desc($product ) . '",';
111 echo '"' . oeFormatShortDate(display_desc($transdate)) . '",';
112 echo '"' . display_desc($invnumber) . '",';
113 echo '"' . display_desc($qty ) . '",';
114 echo '"'; bucks($rowamount); echo '"' . "\n";
121 <?php
echo display_desc($catleft); $catleft = " "; ?
>
124 <?php
echo display_desc($productleft); $productleft = " "; ?
>
127 <?php
echo oeFormatShortDate($transdate); ?
>
130 <a href
='../patient_file/pos_checkout.php?ptid=<?php echo $patient_id; ?>&enc=<?php echo $encounter_id; ?>'>
131 <?php
echo $invnumber; ?
></a
>
137 <?php
bucks($rowamount); ?
>
142 } // End not csv export
144 $producttotal +
= $rowamount;
145 $cattotal +
= $rowamount;
146 $grandtotal +
= $rowamount;
152 if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
154 $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
156 if (!$INTEGRATED_AR) SLConnect();
158 $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
159 $form_to_date = fixDate($_POST['form_to_date'] , date('Y-m-d'));
160 $form_facility = $_POST['form_facility'];
162 if ($_POST['form_csvexport']) {
163 header("Pragma: public");
164 header("Expires: 0");
165 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
166 header("Content-Type: application/force-download");
167 header("Content-Disposition: attachment; filename=sales_by_item.csv");
168 header("Content-Description: File Transfer");
170 if ($_POST['form_details']) {
176 echo '"Amount"' . "\n";
182 echo '"Total"' . "\n";
189 <?php
html_header_show();?
>
190 <style type
="text/css">
191 /* specifically include & exclude from printing */
197 #report_parameters_daterange {
206 /* specifically exclude some from the screen */
208 #report_parameters_daterange {
215 <title
><?php
xl('Sales by Item','e') ?
></title
>
218 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class="body_top">
220 <span
class='title'><?php
xl('Report','e'); ?
> - <?php
xl('Sales by Item','e'); ?
></span
>
222 <form method
='post' action
='sales_by_item.php' id
='theform'>
224 <div id
="report_parameters">
225 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
226 <input type
='hidden' name
='form_csvexport' id
='form_csvexport' value
=''/>
230 <div style
='float:left'>
235 <?php
xl('Facility','e'); ?
>:
238 <?php
dropdown_facility(strip_escape_custom($form_facility), 'form_facility', true); ?
>
241 <?php
xl('From','e'); ?
>:
244 <input type
='text' name
='form_from_date' id
="form_from_date" size
='10' value
='<?php echo $form_from_date ?>'
245 onkeyup
='datekeyup(this,mypcc)' onblur
='dateblur(this,mypcc)' title
='yyyy-mm-dd'>
246 <img src
='../pic/show_calendar.gif' align
='absbottom' width
='24' height
='22'
247 id
='img_from_date' border
='0' alt
='[?]' style
='cursor:pointer'
248 title
='<?php xl('Click here to choose a date
','e
'); ?>'>
251 <?php
xl('To','e'); ?
>:
254 <input type
='text' name
='form_to_date' id
="form_to_date" size
='10' value
='<?php echo $form_to_date ?>'
255 onkeyup
='datekeyup(this,mypcc)' onblur
='dateblur(this,mypcc)' title
='yyyy-mm-dd'>
256 <img src
='../pic/show_calendar.gif' align
='absbottom' width
='24' height
='22'
257 id
='img_to_date' border
='0' alt
='[?]' style
='cursor:pointer'
258 title
='<?php xl('Click here to choose a date
','e
'); ?>'>
264 <input type
='checkbox' name
='form_details'<?php
if ($form_details) echo ' checked'; ?
>>
265 <?php
xl('Details','e'); ?
>
273 <td align
='left' valign
='middle' height
="100%">
274 <table style
='border-left:1px solid; width:100%; height:100%' >
277 <div style
='margin-left:15px'>
278 <a href
='#' class='css_button' onclick
='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
280 <?php
xl('Submit','e'); ?
>
284 <?php
if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) { ?
>
285 <a href
='#' class='css_button' onclick
='window.print()'>
287 <?php
xl('Print','e'); ?
>
290 <a href
='#' class='css_button' onclick
='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
292 <?php
xl('CSV Export','e'); ?
>
304 </div
> <!-- end of parameters
-->
307 if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) {
309 <div id
="report_results">
313 <?php
xl('Category','e'); ?
>
316 <?php
xl('Item','e'); ?
>
319 <?php
xl('Date','e'); ?
>
322 <?php
xl('Invoice','e'); ?
>
325 <?php
xl('Qty','e'); ?
>
328 <?php
xl('Amount','e'); ?
>
334 if ($_POST['form_refresh'] ||
$_POST['form_csvexport']) {
335 $from_date = $form_from_date;
336 $to_date = $form_to_date;
349 if ($INTEGRATED_AR) {
350 $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.units, " .
351 "b.code_text, fe.date, fe.facility_id, fe.invoice_refno, lo.title " .
352 "FROM billing AS b " .
353 "JOIN code_types AS ct ON ct.ct_key = b.code_type " .
354 "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
355 "LEFT JOIN codes AS c ON c.code_type = ct.ct_id AND c.code = b.code AND c.modifier = b.modifier " .
356 "LEFT JOIN list_options AS lo ON lo.list_id = 'superbill' AND lo.option_id = c.superbill " .
357 "WHERE b.code_type != 'COPAY' AND b.activity = 1 AND b.fee != 0 AND " .
358 "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59'";
359 // If a facility was specified.
360 if ($form_facility) {
361 $query .= " AND fe.facility_id = '$form_facility'";
363 $query .= " ORDER BY lo.title, b.code, fe.date, fe.id";
365 $res = sqlStatement($query);
366 while ($row = sqlFetchArray($res)) {
367 thisLineItem($row['pid'], $row['encounter'],
368 $row['title'], $row['code'] . ' ' . $row['code_text'],
369 substr($row['date'], 0, 10), $row['units'], $row['fee'], $row['invoice_refno']);
372 $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
373 "d.name, fe.date, fe.facility_id, fe.invoice_refno " .
374 "FROM drug_sales AS s " .
375 "JOIN drugs AS d ON d.drug_id = s.drug_id " .
376 "JOIN form_encounter AS fe ON " .
377 "fe.pid = s.pid AND fe.encounter = s.encounter AND " .
378 "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59' " .
380 // If a facility was specified.
381 if ($form_facility) {
382 $query .= " AND fe.facility_id = '$form_facility'";
384 $query .= " ORDER BY d.name, fe.date, fe.id";
386 $res = sqlStatement($query);
387 while ($row = sqlFetchArray($res)) {
388 thisLineItem($row['pid'], $row['encounter'], xl('Products'), $row['name'],
389 substr($row['date'], 0, 10), $row['quantity'], $row['fee'], $row['invoice_refno']);
393 $query = "SELECT ar.invnumber, ar.transdate, " .
394 "invoice.description, invoice.qty, invoice.sellprice " .
395 "FROM ar, invoice WHERE " .
396 "ar.transdate >= '$from_date' AND ar.transdate <= '$to_date' " .
397 "AND invoice.trans_id = ar.id " .
398 "ORDER BY invoice.description, ar.transdate, ar.id";
399 $t_res = SLQuery($query);
400 if ($sl_err) die($sl_err);
401 for ($irow = 0; $irow < SLRowCount($t_res); ++
$irow) {
402 $row = SLGetRow($t_res, $irow);
403 list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
404 // If a facility was specified then skip invoices whose encounters
405 // do not indicate that facility.
406 if ($form_facility) {
407 $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
408 "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
409 "facility_id = '$form_facility'");
410 if (empty($tmp['count'])) continue;
412 thisLineItem($patient_id, $encounter_id, '', $row['description'],
413 $row['transdate'], $row['qty'], $row['sellprice'] * $row['qty']);
415 } // end not $INTEGRATED_AR
417 if ($_POST['form_csvexport']) {
418 if (! $_POST['form_details']) {
419 echo '"' . display_desc($product) . '",';
420 echo '"' . $productqty . '",';
421 echo '"'; bucks($producttotal); echo '"' . "\n";
427 <tr bgcolor
="#ddddff">
429 <?php
echo display_desc($catleft); $catleft = " "; ?
>
431 <td
class="detail" colspan
="3">
432 <?php
if ($_POST['form_details']) echo xl('Total for') . ' '; echo display_desc($product); ?
>
435 <?php
echo $productqty; ?
>
438 <?php
bucks($producttotal); ?
>
442 <tr bgcolor
="#ffdddd">
446 <td
class="detail" colspan
="3">
447 <?php
echo xl('Total for category') . ' '; echo display_desc($category); ?
>
450 <?php
echo $catqty; ?
>
453 <?php
bucks($cattotal); ?
>
458 <td
class="detail" colspan
="4">
459 <?php
xl('Grand Total','e'); ?
>
462 <?php
echo $grandqty; ?
>
465 <?php
bucks($grandtotal); ?
>
471 } // End not csv export
473 if (!$INTEGRATED_AR) SLClose();
475 if (! $_POST['form_csvexport']) {
479 </div
> <!-- report results
-->
482 <?php
echo xl('Please input search criteria above, and click Submit to view results.', 'e' ); ?
>
490 <!-- stuff
for the popup calendar
-->
491 <link rel
='stylesheet' href
='<?php echo $css_header ?>' type
='text/css'>
492 <style type
="text/css">@import
url(../../library
/dynarch_calendar
.css
);</style
>
493 <script type
="text/javascript" src
="../../library/dynarch_calendar.js"></script
>
494 <?php
include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?
>
495 <script type
="text/javascript" src
="../../library/dynarch_calendar_setup.js"></script
>
496 <script type
="text/javascript" src
="../../library/js/jquery.1.3.2.js"></script
>
498 <script language
="Javascript">
499 Calendar
.setup({inputField
:"form_from_date", ifFormat
:"%Y-%m-%d", button
:"img_from_date"});
500 Calendar
.setup({inputField
:"form_to_date", ifFormat
:"%Y-%m-%d", button
:"img_to_date"});
505 } // End not csv export