4 * This is a report of sales by item description.
7 * @link https://www.open-emr.org
8 * @author Rod Roark <rod@sunsetsystems.com>
9 * @author Terry Hill <terry@lillysystems.com>
10 * @author Brady Miller <brady.g.miller@gmail.com>
11 * @copyright Copyright (c) 2006-2016 Rod Roark <rod@sunsetsystems.com>
12 * @copyright Copyright (c) 2015-2016 Terry Hill <terry@lillysystems.com>
13 * @copyright Copyright (c) 2017-2018 Brady Miller <brady.g.miller@gmail.com>
14 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
17 require_once("../globals.php");
18 require_once("$srcdir/patient.inc");
19 require_once "$srcdir/options.inc.php";
21 use OpenEMR\Common\Acl\AclMain
;
22 use OpenEMR\Common\Csrf\CsrfUtils
;
23 use OpenEMR\Core\Header
;
26 if (!CsrfUtils
::verifyCsrfToken($_POST["csrf_token_form"])) {
27 CsrfUtils
::csrfNotVerified();
31 $form_provider = $_POST['form_provider'] ??
null;
32 if (!empty($_POST['form_refresh']) ||
!empty($_POST['form_csvexport'])) {
33 $form_details = (!empty($_POST['form_details'])) ?
true : false;
35 $form_details = false;
38 function bucks($amount)
41 return oeFormatMoney($amount);
45 function display_desc($desc)
47 if (preg_match('/^\S*?:(.+)$/', $desc, $matches)) {
54 function thisLineItem($patient_id, $encounter_id, $rowcat, $description, $transdate, $qty, $amount, $irnumber = '')
56 global $product, $category, $producttotal, $productqty, $cattotal, $catqty, $grandtotal, $grandqty;
57 global $productleft, $catleft;
59 $invnumber = $irnumber ?
$irnumber : "$patient_id.$encounter_id";
60 $rowamount = sprintf('%01.2f', $amount);
62 $patdata = sqlQuery("SELECT " .
63 "p.fname, p.mname, p.lname, p.pubpid, p.DOB, " .
64 "p.street, p.city, p.state, p.postal_code, " .
65 "p.ss, p.sex, p.status, p.phone_home, " .
66 "p.phone_biz, p.phone_cell, p.hipaa_notice " .
67 "FROM patient_data AS p " .
68 "WHERE p.pid = ? LIMIT 1", array($patient_id));
70 $pat_name = $patdata['fname'] . ' ' . $patdata['mname'] . ' ' . $patdata['lname'];
73 $rowcat = xl('None{{Sales}}');
76 $rowproduct = $description;
78 $rowproduct = xl('Unknown');
81 if ($product != $rowproduct ||
$category != $rowcat) {
83 // Print product total.
84 if ($_POST['form_csvexport']) {
85 if (! $_POST['form_details']) {
86 echo csvEscape(display_desc($category)) . ',';
87 echo csvEscape(display_desc($product)) . ',';
88 echo csvEscape($productqty) . ',';
89 echo csvEscape(bucks($producttotal));
94 <tr bgcolor
="#ddddff">
96 <?php
echo text(display_desc($catleft));
99 <td
class="detail" colspan
="3">
101 if ($_POST['form_details']) {
102 echo xlt('Total for') . ' ';
105 echo text(display_desc($product)); ?
>
107 <?php
if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {?
>
116 <?php
echo text($productqty); ?
>
119 <?php
echo text(bucks($producttotal)); ?
>
123 } // End not csv export
128 $product = $rowproduct;
129 $productleft = $product;
132 if ($category != $rowcat) {
134 // Print category total.
135 if (!$_POST['form_csvexport']) {
138 <tr bgcolor
="#ffdddd">
142 <td
class="detail" colspan
="3">
143 <?php
echo xlt('Total for category') . ' ';
144 echo text(display_desc($category)); ?
>
146 <?php
if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {?
>
155 <?php
echo text($catqty); ?
>
158 <?php
echo text(bucks($cattotal)); ?
>
162 } // End not csv export
168 $catleft = $category;
171 if (!empty($_POST['form_details'])) {
172 if ($_POST['form_csvexport']) {
173 echo csvEscape(display_desc($category)) . ',';
174 echo csvEscape(display_desc($product)) . ',';
175 echo csvEscape(oeFormatShortDate(display_desc($transdate))) . ',';
176 if ($GLOBALS['sales_report_invoice'] == 1 ||
$GLOBALS['sales_report_invoice'] == 2) {
177 echo csvEscape($pat_name) . ',';
180 if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {
181 echo csvEscape(display_desc($invnumber)) . ',';
184 if ($GLOBALS['sales_report_invoice'] == 1) {
185 echo csvEscape($patient_id) . ',';
188 // echo '"' . display_desc($invnumber) . '",';
189 echo csvEscape(display_desc($qty)) . ',';
190 echo csvEscape(bucks($rowamount));
197 <?php
echo text(display_desc($catleft));
201 <?php
echo text(display_desc($productleft));
202 $productleft = " "; ?
>
205 <?php
echo text(oeFormatShortDate($transdate)); ?
>
207 <?php
if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {?
>
212 <?php
if ($GLOBALS['sales_report_invoice'] == 1 ||
$GLOBALS['sales_report_invoice'] == 2) { ?
>
214 <?php
echo text($pat_name); ?
>
218 <?php
if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) { ?
>
219 <a href
='../patient_file/pos_checkout.php?ptid=<?php echo attr_url($patient_id); ?>&enc=<?php echo attr_url($encounter_id); ?>'>
220 <?php
echo text($invnumber); ?
></a
>
223 if ($GLOBALS['sales_report_invoice'] == 1) {
224 echo text($patient_id);
228 <?php
if ($GLOBALS['sales_report_invoice'] == 0) {?
>
234 <?php
echo text($qty); ?
>
237 <?php
echo text(bucks($rowamount)); ?
>
241 } // End not csv export
243 $producttotal +
= $rowamount;
244 $cattotal +
= $rowamount;
245 $grandtotal +
= $rowamount;
251 if (! AclMain
::aclCheckCore('acct', 'rep')) {
252 die(xlt("Unauthorized access."));
255 $form_from_date = (isset($_POST['form_from_date'])) ?
DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
256 $form_to_date = (isset($_POST['form_to_date'])) ?
DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
257 $form_facility = $_POST['form_facility'] ??
null;
259 if (!empty($_POST['form_csvexport'])) {
260 header("Pragma: public");
261 header("Expires: 0");
262 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
263 header("Content-Type: application/force-download");
264 header("Content-Disposition: attachment; filename=sales_by_item.csv");
265 header("Content-Description: File Transfer");
267 if ($_POST['form_details']) {
271 if ($GLOBALS['sales_report_invoice'] == 1 ||
$GLOBALS['sales_report_invoice'] == 2) {
275 if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {
279 if ($GLOBALS['sales_report_invoice'] == 1) {
284 echo '"Amount"' . "\n";
289 echo '"Total"' . "\n";
291 } else { // end export
296 <title
><?php
echo xlt('Sales by Item'); ?
></title
>
298 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
301 /* specifically include & exclude from printing */
307 #report_parameters_daterange {
316 /* specifically exclude some from the screen */
318 #report_parameters_daterange {
324 table
.mymaintable
, table
.mymaintable td
{
325 border
-collapse
: collapse
;
327 table
.mymaintable td
{
328 padding
: 1px
5px
1px
5px
;
334 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
335 var win
= top
.printLogSetup ? top
: opener
.top
;
336 win
.printLogSetup(document
.getElementById('printbutton'));
338 $
('.datepicker').datetimepicker({
339 <?php
$datetimepicker_timepicker = false; ?
>
340 <?php
$datetimepicker_showseconds = false; ?
>
341 <?php
$datetimepicker_formatInput = true; ?
>
342 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
343 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
349 <title
><?php
echo xlt('Sales by Item') ?
></title
>
351 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class="body_top">
353 <span
class='title'><?php
echo xlt('Report'); ?
> - <?php
echo xlt('Sales by Item'); ?
></span
>
355 <form method
='post' action
='sales_by_item.php' id
='theform' onsubmit
='return top.restoreSession()'>
356 <input type
="hidden" name
="csrf_token_form" value
="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
358 <div id
="report_parameters">
359 <input type
='hidden' name
='form_refresh' id
='form_refresh' value
=''/>
360 <input type
='hidden' name
='form_csvexport' id
='form_csvexport' value
=''/>
364 <div style
='float:left'>
367 <td
class='col-form-label'>
368 <?php
echo xlt('Facility'); ?
>:
371 <?php
dropdown_facility($form_facility, 'form_facility', true); ?
>
373 <td
class='col-form-label'>
374 <?php
echo xlt('From'); ?
>:
377 <input type
='text' class='datepicker form-control' name
='form_from_date' id
="form_from_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_from_date)); ?>'>
379 <td
class='col-form-label'>
380 <?php
echo xlt('To{{Range}}'); ?
>:
383 <input type
='text' class='datepicker form-control' name
='form_to_date' id
="form_to_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_to_date)); ?>'>
389 <td
class='col-form-label'>
390 <?php
echo xlt('Provider'); ?
>:
394 if (AclMain
::aclCheckCore('acct', 'rep_a')) {
395 // Build a drop-down list of providers.
396 $query = "select id, lname, fname from users where " .
397 "authorized = 1 order by lname, fname";
398 $res = sqlStatement($query);
399 echo " <select name='form_provider' class='form-control'>\n";
400 echo " <option value=''>-- " . xlt('All Providers') . " --\n";
401 while ($row = sqlFetchArray($res)) {
402 $provid = $row['id'];
403 echo " <option value='" . attr($provid) . "'";
404 if (!empty($_REQUEST['form_provider']) && ($provid == $_REQUEST['form_provider'])) {
408 echo ">" . text($row['lname']) . ", " . text($row['fname']) . "\n";
413 echo "<input type='hidden' name='form_provider' value='" . attr($_SESSION['authUserID']) . "'>";
419 <div
class='checkbox'>
420 <label
><input type
='checkbox' name
='form_details'<?php
echo ($form_details) ?
' checked' : ''; ?
>>
421 <?php
echo xlt('Details'); ?
></label
>
429 <td
class='h-100' align
='left' valign
='middle'>
430 <table
class='w-100 h-100' style
='border-left:1px solid;'>
433 <div
class="text-center">
434 <div
class="btn-group" role
="group">
435 <a href
='#' class='btn btn-secondary btn-save' onclick
='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
436 <?php
echo xlt('Submit'); ?
>
438 <?php
if (!empty($_POST['form_refresh']) ||
!empty($_POST['form_csvexport'])) { ?
>
439 <a href
='#' class='btn btn-secondary btn-print' id
='printbutton'>
440 <?php
echo xlt('Print'); ?
>
442 <a href
='#' class='btn btn-secondary btn-transmit' onclick
='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
443 <?php
echo xlt('CSV Export'); ?
>
455 </div
> <!-- end of parameters
-->
458 if (!empty($_POST['form_refresh']) ||
!empty($_POST['form_csvexport'])) {
461 <div id
="report_results">
462 <table width
='98%' id
='mymaintable' class='table table-striped mymaintable'>
463 <thead
class='thead-light'>
465 <?php
echo xlt('Category'); ?
>
468 <?php
echo xlt('Item'); ?
>
476 <?php
if ($GLOBALS['sales_report_invoice'] == 2) {?
>
483 if ($GLOBALS['sales_report_invoice'] == 0) {
496 if ($GLOBALS['sales_report_invoice'] == 1 ||
$GLOBALS['sales_report_invoice'] == 2) {
504 if ($GLOBALS['sales_report_invoice'] == 2) {
510 if ($GLOBALS['sales_report_invoice'] == 1) {
518 <?php
echo xlt('Qty'); ?
>
521 <?php
echo xlt('Amount'); ?
>
529 if (!empty($_POST['form_refresh']) ||
!empty($_POST['form_csvexport'])) {
530 $from_date = $form_from_date . ' 00:00:00';
531 $to_date = $form_to_date . ' 23:59:59';
543 $sqlBindArray = array();
544 $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.units, " .
545 "b.code_text, fe.date, fe.facility_id, fe.provider_id, fe.invoice_refno, lo.title " .
546 "FROM billing AS b " .
547 "JOIN code_types AS ct ON ct.ct_key = b.code_type " .
548 "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
549 "LEFT JOIN codes AS c ON c.code_type = ct.ct_id AND c.code = b.code AND c.modifier = b.modifier " .
550 "LEFT JOIN list_options AS lo ON lo.list_id = 'superbill' AND lo.option_id = c.superbill AND lo.activity = 1 " .
551 "WHERE b.code_type != 'COPAY' AND b.activity = 1 AND b.fee != 0 AND " .
552 "fe.date >= ? AND fe.date <= ?";
553 array_push($sqlBindArray, $from_date, $to_date);
554 // If a facility was specified.
555 if ($form_facility) {
556 $query .= " AND fe.facility_id = ?";
557 array_push($sqlBindArray, $form_facility);
560 if ($form_provider) {
561 $query .= " AND fe.provider_id = ?";
562 array_push($sqlBindArray, $form_provider);
565 $query .= " ORDER BY lo.title, b.code, fe.date, fe.id";
567 $res = sqlStatement($query, $sqlBindArray);
568 while ($row = sqlFetchArray($res)) {
573 $row['code'] . ' ' . $row['code_text'],
574 substr($row['date'], 0, 10),
577 $row['invoice_refno']
582 $sqlBindArray = array();
583 $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
584 "d.name, fe.date, fe.facility_id, fe.provider_id, fe.invoice_refno " .
585 "FROM drug_sales AS s " .
586 "JOIN drugs AS d ON d.drug_id = s.drug_id " .
587 "JOIN form_encounter AS fe ON " .
588 "fe.pid = s.pid AND fe.encounter = s.encounter AND " .
589 "fe.date >= ? AND fe.date <= ? " .
591 array_push($sqlBindArray, $from_date, $to_date);
592 // If a facility was specified.
593 if ($form_facility) {
594 $query .= " AND fe.facility_id = ?";
595 array_push($sqlBindArray, $form_facility);
598 if ($form_provider) {
599 $query .= " AND fe.provider_id = ?";
600 array_push($sqlBindArray, $form_provider);
603 $query .= " ORDER BY d.name, fe.date, fe.id";
605 $res = sqlStatement($query, $sqlBindArray);
606 while ($row = sqlFetchArray($res)) {
612 substr($row['date'], 0, 10),
615 $row['invoice_refno']
619 if ($_POST['form_csvexport']) {
620 if (! $_POST['form_details']) {
621 echo csvEscape(display_desc($product)) . ',';
622 echo csvEscape($productqty) . ',';
623 echo csvEscape(bucks($producttotal));
629 <tr bgcolor
="#ddddff">
631 <?php
echo text(display_desc($catleft));
634 <td
class="detail" colspan
="3">
636 if (!empty($_POST['form_details'])) {
637 echo xlt('Total for') . ' ';
640 echo text(display_desc($product)); ?
>
642 <?php
if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {?
>
651 <?php
echo text($productqty); ?
>
654 <?php
echo text(bucks($producttotal)); ?
>
658 <tr bgcolor
="#ffdddd">
662 <td
class="detail" colspan
="3"><strong
>
663 <?php
echo xlt('Total for category') . ' ';
664 echo text(display_desc($category)); ?
>
666 <?php
if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {?
>
674 <td align
="right"><strong
>
675 <?php
echo text($catqty); ?
>
677 <td align
="right"><strong
>
678 <?php
echo text(bucks($cattotal)); ?
>
683 <td
class="detail" colspan
="4"><strong
>
684 <?php
echo xlt('Grand Total'); ?
>
686 <?php
if ($GLOBALS['sales_report_invoice'] == 0 ||
$GLOBALS['sales_report_invoice'] == 2) {?
>
694 <td align
="right"><strong
>
695 <?php
echo text($grandqty); ?
>
697 <td align
="right"><strong
>
698 <?php
echo text(bucks($grandtotal)); ?
>
701 <?php
$report_from_date = oeFormatShortDate($form_from_date) ;
702 $report_to_date = oeFormatShortDate($form_to_date) ;
704 <div align
='right'><span
class='title' ><?php
echo xlt('Report Date') . ' '; ?
><?php
echo text($report_from_date);?
> - <?php
echo text($report_to_date);?
></span
></div
>
706 } // End not csv export
709 if (empty($_POST['form_csvexport'])) {
710 if (!empty($_POST['form_refresh'])) {
715 </div
> <!-- report results
-->
719 <?php
echo xlt('Please input search criteria above, and click Submit to view results.'); ?
>
730 } // End not csv export