4 * This is an inventory transactions list.
7 * @link https://www.open-emr.org
8 * @author Rod Roark <rod@sunsetsystems.com>
9 * @author Brady Miller <brady.g.miller@gmail.com>
10 * @copyright Copyright (c) 2010-2016 Rod Roark <rod@sunsetsystems.com>
11 * @copyright Copyright (c) 2017-2019 Brady Miller <brady.g.miller@gmail.com>
12 * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
15 require_once("../globals.php");
16 require_once("$srcdir/patient.inc");
18 use OpenEMR\Common\Acl\AclMain
;
19 use OpenEMR\Common\Csrf\CsrfUtils
;
20 use OpenEMR\Core\Header
;
23 if (!CsrfUtils
::verifyCsrfToken($_POST["csrf_token_form"])) {
24 CsrfUtils
::csrfNotVerified();
28 function bucks($amount)
31 return oeFormatMoney($amount);
37 function thisLineItem($row, $xfer = false)
39 global $grandtotal, $grandqty, $encount, $form_action;
44 if (!empty($row['pid'])) {
46 $dpname = $row['plname'];
47 if (!empty($row['pfname'])) {
48 $dpname .= ', ' . $row['pfname'];
49 if (!empty($row['pmname'])) {
50 $dpname .= ' ' . $row['pmname'];
54 $invnumber = empty($row['invoice_refno']) ?
55 "{$row['pid']}.{$row['encounter']}" : $row['invoice_refno'];
56 } elseif (!empty($row['distributor_id'])) {
57 $ttype = xl('Distribution');
58 if (!empty($row['organization'])) {
59 $dpname = $row['organization'];
61 $dpname = $row['dlname'];
62 if (!empty($row['dfname'])) {
63 $dpname .= ', ' . $row['dfname'];
64 if (!empty($row['dmname'])) {
65 $dpname .= ' ' . $row['dmname'];
69 } elseif (!empty($row['xfer_inventory_id']) ||
$xfer) {
70 $ttype = xl('Transfer');
71 } elseif ($row['fee'] != 0) {
72 $ttype = xl('Purchase');
74 $ttype = xl('Adjustment');
77 if ($form_action == 'export') {
78 echo csvEscape(oeFormatShortDate($row['sale_date'])) . ',';
79 echo csvEscape($ttype) . ',';
80 echo csvEscape($row['name']) . ',';
81 echo csvEscape($row['lot_number']) . ',';
82 echo csvEscape($row['warehouse']) . ',';
83 echo csvEscape($dpname) . ',';
84 echo csvEscape(0 - $row['quantity']) . ',';
85 echo csvEscape(bucks($row['fee'])) . ',';
86 echo csvEscape($row['billed']) . ',';
87 echo csvEscape($row['notes']) . "\n";
89 $bgcolor = (++
$encount & 1) ?
"#ddddff" : "#ffdddd";
92 <tr bgcolor
="<?php echo $bgcolor; ?>">
94 <?php
echo text(oeFormatShortDate($row['sale_date'])); ?
>
97 <?php
echo text($ttype); ?
>
100 <?php
echo text($row['name']); ?
>
103 <?php
echo text($row['lot_number']); ?
>
106 <?php
echo text($row['warehouse']); ?
>
109 <?php
echo text($dpname); ?
>
111 <td
class="detail" align
="right">
112 <?php
echo text(0 - $row['quantity']); ?
>
114 <td
class="detail" align
="right">
115 <?php
echo text(bucks($row['fee'])); ?
>
117 <td
class="detail" align
="center">
118 <?php
echo empty($row['billed']) ?
' ' : '*'; ?
>
121 <?php
echo text($row['notes']); ?
>
125 } // End not csv export
127 $grandtotal +
= $row['fee'];
128 $grandqty -= $row['quantity'];
130 // In the special case of a transfer, generate a second line item for
132 if (!empty($row['xfer_inventory_id'])) {
133 $row['xfer_inventory_id'] = 0;
134 $row['lot_number'] = $row['lot_number_2'];
135 $row['warehouse'] = $row['warehouse_2'];
136 $row['quantity'] = 0 - $row['quantity'];
137 $row['fee'] = 0 - $row['fee'];
138 thisLineItem($row, true);
142 if (! AclMain
::aclCheckCore('acct', 'rep')) {
143 die(xlt("Unauthorized access."));
146 // this is "" or "submit" or "export".
147 $form_action = $_POST['form_action'];
149 $form_from_date = (isset($_POST['form_from_date'])) ?
DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
150 $form_to_date = (isset($_POST['form_to_date'])) ?
DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
151 $form_trans_type = isset($_POST['form_trans_type']) ?
$_POST['form_trans_type'] : '0';
155 if ($form_action == 'export') {
156 header("Pragma: public");
157 header("Expires: 0");
158 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
159 header("Content-Type: application/force-download");
160 header("Content-Disposition: attachment; filename=inventory_transactions.csv");
161 header("Content-Description: File Transfer");
163 echo csvEscape(xl('Date')) . ',';
164 echo csvEscape(xl('Transaction')) . ',';
165 echo csvEscape(xl('Product')) . ',';
166 echo csvEscape(xl('Lot')) . ',';
167 echo csvEscape(xl('Warehouse')) . ',';
168 echo csvEscape(xl('Who')) . ',';
169 echo csvEscape(xl('Qty')) . ',';
170 echo csvEscape(xl('Amount')) . ',';
171 echo csvEscape(xl('Billed')) . ',';
172 echo csvEscape(xl('Notes')) . "\n";
173 } else { // end export
177 <title
><?php
echo xlt('Inventory Transactions'); ?
></title
>
179 <?php Header
::setupHeader(['datetime-picker', 'report-helper']); ?
>
182 /* specifically include & exclude from printing */
184 #report_parameters {visibility: hidden; display: none;}
185 #report_parameters_daterange {visibility: visible; display: inline;}
186 #report_results {margin-top: 30px;}
189 /* specifically exclude some from the screen */
191 #report_parameters_daterange {
198 font
-family
:sans
-serif
;
204 font
-family
:sans
-serif
;
208 .detail
{ color
:var(--black
);
209 font
-family
:sans
-serif
;
214 #report_results table thead {
222 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
223 var win
= top
.printLogSetup ? top
: opener
.top
;
224 win
.printLogSetup(document
.getElementById('printbutton'));
226 $
('.datepicker').datetimepicker({
227 <?php
$datetimepicker_timepicker = false; ?
>
228 <?php
$datetimepicker_showseconds = false; ?
>
229 <?php
$datetimepicker_formatInput = true; ?
>
230 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
231 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
235 function mysubmit(action
) {
236 var f
= document
.forms
[0];
237 f
.form_action
.value
= action
;
238 top
.restoreSession();
246 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class='body_top'>
249 <h2
><?php
echo xlt('Inventory Transactions'); ?
></h2
>
251 <form method
='post' action
='inventory_transactions.php' onsubmit
='return top.restoreSession()'>
252 <input type
="hidden" name
="csrf_token_form" value
="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
254 <div id
="report_parameters">
255 <!-- form_action is set to
"submit" or "export" at form submit time
-->
256 <input type
='hidden' name
='form_action' value
='' />
262 <td
class='label_custom'>
263 <?php
echo xlt('Type'); ?
>:
266 <select name
='form_trans_type' onchange
='trans_type_changed()'>
271 '2' => xl('Purchase/Return'),
273 '6' => xl('Distribution'),
274 '4' => xl('Transfer'),
275 '5' => xl('Adjustment'),
278 echo " <option value='" . attr($key) . "'";
279 if ($key == $form_trans_type) {
283 echo ">" . text($value) . "</option>\n";
288 <td
class='label_custom'>
289 <?php
echo xlt('From'); ?
>:
292 <input type
='text' class='datepicker' name
='form_from_date' id
="form_from_date" size
='10'
293 value
='<?php echo attr(oeFormatShortDate($form_from_date)); ?>'>
295 <td
class='label_custom'>
296 <?php
xl('To{{Range}}', 'e'); ?
>:
299 <input type
='text' class='datepicker' name
='form_to_date' id
="form_to_date" size
='10' value
='<?php echo attr(oeFormatShortDate($form_to_date)); ?>' />
304 <td align
='left' valign
='middle'>
305 <table style
='border-left:1px solid; width:100%; height:100%'>
308 <a href
='#' class='btn btn-primary' onclick
='mysubmit("submit")' style
='margin-left:1em'>
309 <span
><?php
echo xlt('Submit'); ?
></span
>
311 <?php
if ($form_action) { ?
>
312 <a href
='#' class='btn btn-primary' id
='printbutton' style
='margin-left:1em'>
313 <span
><?php
echo xlt('Print'); ?
></span
>
315 <a href
='#' class='btn btn-primary' onclick
='mysubmit("export")' style
='margin-left:1em'>
316 <span
><?php
echo xlt('CSV Export'); ?
></span
>
327 <?php
if ($form_action) { // if submit (already not export here) ?>
328 <div id
="report_results">
329 <table border
='0' cellpadding
='1' cellspacing
='2' width
='98%' id
='mymaintable' class='mymaintable'>
331 <tr bgcolor
="#dddddd">
333 <?php
echo xlt('Date'); ?
>
336 <?php
echo xlt('Transaction'); ?
>
339 <?php
echo xlt('Product'); ?
>
342 <?php
echo xlt('Lot'); ?
>
345 <?php
echo xlt('Warehouse'); ?
>
348 <?php
echo xlt('Who'); ?
>
350 <td
class="dehead" align
="right">
351 <?php
echo xlt('Qty'); ?
>
353 <td
class="dehead" align
="right">
354 <?php
echo xlt('Amount'); ?
>
356 <td
class="dehead" align
="Center">
357 <?php
echo xlt('Billed'); ?
>
360 <?php
echo xlt('Notes'); ?
>
369 if ($form_action) { // if submit or export
370 $from_date = $form_from_date;
371 $to_date = $form_to_date;
376 $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
377 "s.billed, s.notes, s.distributor_id, s.xfer_inventory_id, " .
378 "p.fname AS pfname, p.mname AS pmname, p.lname AS plname, " .
379 "u.fname AS dfname, u.mname AS dmname, u.lname AS dlname, u.organization, " .
380 "d.name, fe.date, fe.invoice_refno, " .
381 "i1.lot_number, i2.lot_number AS lot_number_2, " .
382 "lo1.title AS warehouse, lo2.title AS warehouse_2 " .
383 "FROM drug_sales AS s " .
384 "JOIN drugs AS d ON d.drug_id = s.drug_id " .
385 "LEFT JOIN drug_inventory AS i1 ON i1.inventory_id = s.inventory_id " .
386 "LEFT JOIN drug_inventory AS i2 ON i2.inventory_id = s.xfer_inventory_id " .
387 "LEFT JOIN patient_data AS p ON p.pid = s.pid " .
388 "LEFT JOIN users AS u ON u.id = s.distributor_id " .
389 "LEFT JOIN list_options AS lo1 ON lo1.list_id = 'warehouse' AND " .
390 "lo1.option_id = i1.warehouse_id AND lo1.activity = 1 " .
391 "LEFT JOIN list_options AS lo2 ON lo2.list_id = 'warehouse' AND " .
392 "lo2.option_id = i2.warehouse_id AND lo2.activity = 1 " .
393 "LEFT JOIN form_encounter AS fe ON fe.pid = s.pid AND fe.encounter = s.encounter " .
394 "WHERE s.sale_date >= ? AND s.sale_date <= ? ";
395 if ($form_trans_type == 2) { // purchase/return
396 $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee != 0 ";
397 } elseif ($form_trans_type == 4) { // transfer
398 $query .= "AND s.xfer_inventory_id != 0 ";
399 } elseif ($form_trans_type == 5) { // adjustment
400 $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee = 0 ";
401 } elseif ($form_trans_type == 6) { // distribution
402 $query .= "AND s.distributor_id != 0 ";
403 } elseif ($form_trans_type == 1) { // sale
404 $query .= "AND s.pid != 0 ";
407 $query .= "ORDER BY s.sale_date, s.sale_id";
409 $res = sqlStatement($query, array($from_date, $to_date));
410 while ($row = sqlFetchArray($res)) {
414 // Grand totals line.
415 if ($form_action != 'export') { // if submit
418 <tr bgcolor
="#dddddd">
419 <td
class="dehead" colspan
="6">
420 <?php
echo xlt('Grand Total'); ?
>
422 <td
class="dehead" align
="right">
423 <?php
echo text($grandqty); ?
>
425 <td
class="dehead" align
="right">
426 <?php
echo text(bucks($grandtotal)); ?
>
428 <td
class="dehead" colspan
="2">
435 } // end if submit or export
437 if ($form_action != 'export') {
444 } // end if ($form_action)