3 * This is an inventory transactions list.
6 * @link http://www.open-emr.org
7 * @author Rod Roark <rod@sunsetsystems.com>
8 * @author Brady Miller <brady.g.miller@gmail.com>
9 * @copyright Copyright (c) 2010-2016 Rod Roark <rod@sunsetsystems.com>
10 * @copyright Copyright (c) 2017 Brady Miller <brady.g.miller@gmail.com>
11 * @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");
17 require_once("$srcdir/acl.inc");
19 function bucks($amount)
22 return oeFormatMoney($amount);
28 function esc4Export($str)
30 return str_replace('"', '\\"', $str);
33 function thisLineItem($row, $xfer = false)
35 global $grandtotal, $grandqty, $encount, $form_action;
40 if (!empty($row['pid'])) {
42 $dpname = $row['plname'];
43 if (!empty($row['pfname'])) {
44 $dpname .= ', ' . $row['pfname'];
45 if (!empty($row['pmname'])) {
46 $dpname .= ' ' . $row['pmname'];
50 $invnumber = empty($row['invoice_refno']) ?
51 "{$row['pid']}.{$row['encounter']}" : $row['invoice_refno'];
52 } else if (!empty($row['distributor_id'])) {
53 $ttype = xl('Distribution');
54 if (!empty($row['organization'])) {
55 $dpname = $row['organization'];
57 $dpname = $row['dlname'];
58 if (!empty($row['dfname'])) {
59 $dpname .= ', ' . $row['dfname'];
60 if (!empty($row['dmname'])) {
61 $dpname .= ' ' . $row['dmname'];
65 } else if (!empty($row['xfer_inventory_id']) ||
$xfer) {
66 $ttype = xl('Transfer');
67 } else if ($row['fee'] != 0) {
68 $ttype = xl('Purchase');
70 $ttype = xl('Adjustment');
73 if ($form_action == 'export') {
74 echo '"' . oeFormatShortDate($row['sale_date']) . '",';
75 echo '"' . $ttype . '",';
76 echo '"' . esc4Export($row['name']) . '",';
77 echo '"' . esc4Export($row['lot_number']) . '",';
78 echo '"' . esc4Export($row['warehouse']) . '",';
79 echo '"' . esc4Export($dpname) . '",';
80 echo '"' . (0 - $row['quantity']) . '",';
81 echo '"' . bucks($row['fee']) . '",';
82 echo '"' . $row['billed'] . '",';
83 echo '"' . esc4Export($row['notes']) . '"' . "\n";
85 $bgcolor = (++
$encount & 1) ?
"#ddddff" : "#ffdddd";
88 <tr bgcolor
="<?php echo $bgcolor; ?>">
90 <?php
echo htmlspecialchars(oeFormatShortDate($row['sale_date'])); ?
>
93 <?php
echo htmlspecialchars($ttype); ?
>
96 <?php
echo htmlspecialchars($row['name']); ?
>
99 <?php
echo htmlspecialchars($row['lot_number']); ?
>
102 <?php
echo htmlspecialchars($row['warehouse']); ?
>
105 <?php
echo htmlspecialchars($dpname); ?
>
107 <td
class="detail" align
="right">
108 <?php
echo htmlspecialchars(0 - $row['quantity']); ?
>
110 <td
class="detail" align
="right">
111 <?php
echo htmlspecialchars(bucks($row['fee'])); ?
>
113 <td
class="detail" align
="center">
114 <?php
echo empty($row['billed']) ?
' ' : '*'; ?
>
117 <?php
echo htmlspecialchars($row['notes']); ?
>
121 } // End not csv export
123 $grandtotal +
= $row['fee'];
124 $grandqty -= $row['quantity'];
126 // In the special case of a transfer, generate a second line item for
128 if (!empty($row['xfer_inventory_id'])) {
129 $row['xfer_inventory_id'] = 0;
130 $row['lot_number'] = $row['lot_number_2'];
131 $row['warehouse'] = $row['warehouse_2'];
132 $row['quantity'] = 0 - $row['quantity'];
133 $row['fee'] = 0 - $row['fee'];
134 thisLineItem($row, true);
138 if (! acl_check('acct', 'rep')) {
139 die(htmlspecialchars(xl("Unauthorized access."), ENT_NOQUOTES
));
142 // this is "" or "submit" or "export".
143 $form_action = $_POST['form_action'];
145 $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
146 $form_to_date = fixDate($_POST['form_to_date'], date('Y-m-d'));
147 $form_trans_type = isset($_POST['form_trans_type']) ?
$_POST['form_trans_type'] : '0';
151 if ($form_action == 'export') {
152 header("Pragma: public");
153 header("Expires: 0");
154 header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
155 header("Content-Type: application/force-download");
156 header("Content-Disposition: attachment; filename=inventory_transactions.csv");
157 header("Content-Description: File Transfer");
159 echo '"' . xl('Date') . '",';
160 echo '"' . xl('Transaction') . '",';
161 echo '"' . xl('Product') . '",';
162 echo '"' . xl('Lot') . '",';
163 echo '"' . xl('Warehouse') . '",';
164 echo '"' . xl('Who') . '",';
165 echo '"' . xl('Qty') . '",';
166 echo '"' . xl('Amount') . '",';
167 echo '"' . xl('Billed') . '",';
168 echo '"' . xl('Notes') . '"' . "\n";
174 <?php
html_header_show(); ?
>
175 <title
><?php
echo htmlspecialchars(xl('Inventory Transactions'), ENT_NOQUOTES
) ?
></title
>
176 <link rel
='stylesheet' href
='<?php echo $css_header ?>' type
='text/css'>
177 <link rel
="stylesheet" href
="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-datetimepicker-2-5-4/build/jquery.datetimepicker.min.css">
179 <style type
="text/css">
180 /* specifically include & exclude from printing */
182 #report_parameters {visibility: hidden; display: none;}
183 #report_parameters_daterange {visibility: visible; display: inline;}
184 #report_results {margin-top: 30px;}
187 /* specifically exclude some from the screen */
189 #report_parameters_daterange {visibility: hidden; display: none;}
192 body
{ font
-family
:sans
-serif
; font
-size
:10pt
; font
-weight
:normal
}
193 .dehead
{ color
:#000000; font-family:sans-serif; font-size:10pt; font-weight:bold }
194 .detail
{ color
:#000000; font-family:sans-serif; font-size:10pt; font-weight:normal }
196 #report_results table thead {
201 <script type
="text/javascript" src
="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-min-1-9-1/index.js"></script
>
202 <script type
="text/javascript" src
="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-datetimepicker-2-5-4/build/jquery.datetimepicker.full.min.js"></script
>
203 <script type
="text/javascript" src
="../../library/js/report_helper.js?v=<?php echo $v_js_includes; ?>"></script
>
205 <script language
='JavaScript'>
207 $
(document
).ready(function() {
208 oeFixedHeaderSetup(document
.getElementById('mymaintable'));
209 var win
= top
.printLogSetup ? top
: opener
.top
;
210 win
.printLogSetup(document
.getElementById('printbutton'));
212 $
('.datepicker').datetimepicker({
213 <?php
$datetimepicker_timepicker = false; ?
>
214 <?php
$datetimepicker_showseconds = false; ?
>
215 <?php
$datetimepicker_formatInput = false; ?
>
216 <?php
require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?
>
217 <?php
// can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
221 function mysubmit(action
) {
222 var f
= document
.forms
[0];
223 f
.form_action
.value
= action
;
224 top
.restoreSession();
232 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class='body_top'>
235 <h2
><?php
echo htmlspecialchars(xl('Inventory Transactions'), ENT_NOQUOTES
) ?
></h2
>
237 <form method
='post' action
='inventory_transactions.php'>
239 <div id
="report_parameters">
240 <!-- form_action is set to
"submit" or "export" at form submit time
-->
241 <input type
='hidden' name
='form_action' value
='' />
247 <td
class='label_custom'>
248 <?php
echo htmlspecialchars(xl('Type'), ENT_NOQUOTES
); ?
>:
251 <select name
='form_trans_type' onchange
='trans_type_changed()'>
255 '2' => xl('Purchase/Return'),
257 '6' => xl('Distribution'),
258 '4' => xl('Transfer'),
259 '5' => xl('Adjustment'),
260 ) as $key => $value) {
261 echo " <option value='$key'";
262 if ($key == $form_trans_type) {
266 echo ">" . htmlspecialchars($value, ENT_NOQUOTES
) . "</option>\n";
271 <td
class='label_custom'>
272 <?php
echo htmlspecialchars(xl('From'), ENT_NOQUOTES
); ?
>:
275 <input type
='text' class='datepicker' name
='form_from_date' id
="form_from_date" size
='10'
276 value
='<?php echo htmlspecialchars($form_from_date, ENT_QUOTES) ?>'
277 title
='<?php echo htmlspecialchars(xl('yyyy
-mm
-dd
'), ENT_QUOTES) ?>'>
279 <td
class='label_custom'>
280 <?php
xl('To', 'e'); ?
>:
283 <input type
='text' class='datepicker' name
='form_to_date' id
="form_to_date" size
='10'
284 value
='<?php echo htmlspecialchars($form_to_date, ENT_QUOTES) ?>'
285 title
='<?php echo htmlspecialchars(xl('yyyy
-mm
-dd
'), ENT_QUOTES) ?>'>
290 <td align
='left' valign
='middle'>
291 <table style
='border-left:1px solid; width:100%; height:100%'>
294 <a href
='#' class='css_button' onclick
='mysubmit("submit")' style
='margin-left:1em'>
295 <span
><?php
echo htmlspecialchars(xl('Submit'), ENT_NOQUOTES
); ?
></span
>
297 <?php
if ($form_action) { ?
>
298 <a href
='#' class='css_button' id
='printbutton' style
='margin-left:1em'>
299 <span
><?php
echo htmlspecialchars(xl('Print'), ENT_NOQUOTES
); ?
></span
>
301 <a href
='#' class='css_button' onclick
='mysubmit("export")' style
='margin-left:1em'>
302 <span
><?php
echo htmlspecialchars(xl('CSV Export'), ENT_NOQUOTES
); ?
></span
>
313 <?php
if ($form_action) { // if submit (already not export here) ?>
315 <div id
="report_results">
316 <table border
='0' cellpadding
='1' cellspacing
='2' width
='98%' id
='mymaintable' class='mymaintable'>
318 <tr bgcolor
="#dddddd">
320 <?php
echo htmlspecialchars(xl('Date'), ENT_NOQUOTES
); ?
>
323 <?php
echo htmlspecialchars(xl('Transaction'), ENT_NOQUOTES
); ?
>
326 <?php
echo htmlspecialchars(xl('Product'), ENT_NOQUOTES
); ?
>
329 <?php
echo htmlspecialchars(xl('Lot'), ENT_NOQUOTES
); ?
>
332 <?php
echo htmlspecialchars(xl('Warehouse'), ENT_NOQUOTES
); ?
>
335 <?php
echo htmlspecialchars(xl('Who'), ENT_NOQUOTES
); ?
>
337 <td
class="dehead" align
="right">
338 <?php
echo htmlspecialchars(xl('Qty'), ENT_NOQUOTES
); ?
>
340 <td
class="dehead" align
="right">
341 <?php
echo htmlspecialchars(xl('Amount'), ENT_NOQUOTES
); ?
>
343 <td
class="dehead" align
="Center">
344 <?php
echo htmlspecialchars(xl('Billed'), ENT_NOQUOTES
); ?
>
347 <?php
echo htmlspecialchars(xl('Notes'), ENT_NOQUOTES
); ?
>
356 if ($form_action) { // if submit or export
357 $from_date = $form_from_date;
358 $to_date = $form_to_date;
363 $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
364 "s.billed, s.notes, s.distributor_id, s.xfer_inventory_id, " .
365 "p.fname AS pfname, p.mname AS pmname, p.lname AS plname, " .
366 "u.fname AS dfname, u.mname AS dmname, u.lname AS dlname, u.organization, " .
367 "d.name, fe.date, fe.invoice_refno, " .
368 "i1.lot_number, i2.lot_number AS lot_number_2, " .
369 "lo1.title AS warehouse, lo2.title AS warehouse_2 " .
370 "FROM drug_sales AS s " .
371 "JOIN drugs AS d ON d.drug_id = s.drug_id " .
372 "LEFT JOIN drug_inventory AS i1 ON i1.inventory_id = s.inventory_id " .
373 "LEFT JOIN drug_inventory AS i2 ON i2.inventory_id = s.xfer_inventory_id " .
374 "LEFT JOIN patient_data AS p ON p.pid = s.pid " .
375 "LEFT JOIN users AS u ON u.id = s.distributor_id " .
376 "LEFT JOIN list_options AS lo1 ON lo1.list_id = 'warehouse' AND " .
377 "lo1.option_id = i1.warehouse_id AND lo1.activity = 1 " .
378 "LEFT JOIN list_options AS lo2 ON lo2.list_id = 'warehouse' AND " .
379 "lo2.option_id = i2.warehouse_id AND lo2.activity = 1 " .
380 "LEFT JOIN form_encounter AS fe ON fe.pid = s.pid AND fe.encounter = s.encounter " .
381 "WHERE s.sale_date >= ? AND s.sale_date <= ? ";
382 if ($form_trans_type == 2) { // purchase/return
383 $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee != 0 ";
384 } else if ($form_trans_type == 4) { // transfer
385 $query .= "AND s.xfer_inventory_id != 0 ";
386 } else if ($form_trans_type == 5) { // adjustment
387 $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee = 0 ";
388 } else if ($form_trans_type == 6) { // distribution
389 $query .= "AND s.distributor_id != 0 ";
390 } else if ($form_trans_type == 1) { // sale
391 $query .= "AND s.pid != 0 ";
394 $query .= "ORDER BY s.sale_date, s.sale_id";
396 $res = sqlStatement($query, array($from_date, $to_date));
397 while ($row = sqlFetchArray($res)) {
401 // Grand totals line.
402 if ($form_action != 'export') { // if submit
405 <tr bgcolor
="#dddddd">
406 <td
class="dehead" colspan
="6">
407 <?php
echo htmlspecialchars(xl('Grand Total'), ENT_NOQUOTES
); ?
>
409 <td
class="dehead" align
="right">
410 <?php
echo htmlspecialchars($grandqty, ENT_NOQUOTES
); ?
>
412 <td
class="dehead" align
="right">
413 <?php
echo htmlspecialchars(bucks($grandtotal), ENT_NOQUOTES
); ?
>
415 <td
class="dehead" colspan
="2">
422 } // end if submit or export
424 if ($form_action != 'export') {
431 } // end if ($form_action)