2 // Copyright (C) 2008-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.
10 $sanitize_all_escapes=true;
13 //STOP FAKE REGISTER GLOBALS
14 $fake_register_globals=false;
17 require_once("../globals.php");
18 require_once("$srcdir/acl.inc");
19 require_once("$srcdir/options.inc.php");
20 require_once("$include_root/drugs/drugs.inc.php");
22 // Check authorization.
23 $thisauth = acl_check('admin', 'drugs');
24 if (!$thisauth) die(xl('Not authorized'));
26 function addWarning($msg) {
28 if ($warnings) $warnings .= '<br />';
32 // this is "" or "submit".
33 $form_action = $_POST['form_action'];
35 if (!empty($_POST['form_days'])) {
36 $form_days = $_POST['form_days'] +
0;
39 $form_days = sprintf('%d', (strtotime(date('Y-m-d')) - strtotime(date('Y-01-01'))) / (60 * 60 * 24) +
1);
43 $res = sqlStatement("SELECT d.*, SUM(di.on_hand) AS on_hand " .
45 "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " .
46 "AND di.on_hand != 0 AND di.destroy_date IS NULL " .
47 "WHERE d.active = 1 " .
48 "GROUP BY d.name, d.drug_id ORDER BY d.name, d.drug_id");
53 <?php
html_header_show(); ?
>
55 <link rel
="stylesheet" href
='<?php echo $css_header ?>' type
='text/css'>
56 <title
><?php
xl('Inventory List','e'); ?
></title
>
59 /* specifically include & exclude from printing */
61 #report_parameters {visibility: hidden; display: none;}
62 #report_parameters_daterange {visibility: visible; display: inline;}
63 #report_results {margin-top: 30px;}
65 /* specifically exclude some from the screen */
67 #report_parameters_daterange {visibility: hidden; display: none;}
70 body
{ font
-family
:sans
-serif
; font
-size
:10pt
; font
-weight
:normal
}
72 tr
.head
{ font
-size
:10pt
; background
-color
:#cccccc; text-align:center; }
73 tr
.detail
{ font
-size
:10pt
; }
74 a
, a
:visited
, a
:hover
{ color
:#0000cc; }
77 <script type
="text/javascript" src
="../../library/dialog.js"></script
>
79 <script language
="JavaScript">
80 function mysubmit(action
) {
81 var f
= document
.forms
[0];
82 f
.form_action
.value
= action
;
90 <body leftmargin
='0' topmargin
='0' marginwidth
='0' marginheight
='0' class='body_top'>
94 <h2
><?php
echo htmlspecialchars(xl('Inventory List'))?
></h2
>
96 <form method
='post' action
='inventory_list.php' name
='theform'>
98 <div id
="report_parameters">
99 <!-- form_action is set to
"submit" at form submit time
-->
100 <input type
='hidden' name
='form_action' value
='' />
107 <?php
echo htmlspecialchars(xl('For the past')); ?
>
108 <input type
="input" name
="form_days" size
='3' value
="<?php echo $form_days; ?>" />
109 <?php
echo htmlspecialchars(xl('days')); ?
>
114 <td align
='left' valign
='middle'>
115 <table style
='border-left:1px solid; width:100%; height:100%'>
118 <a href
='#' class='css_button' onclick
='mysubmit("submit")' style
='margin-left:1em'>
119 <span
><?php
echo htmlspecialchars(xl('Submit'), ENT_NOQUOTES
); ?
></span
>
121 <?php
if ($form_action) { ?
>
122 <a href
='#' class='css_button' onclick
='window.print()' style
='margin-left:1em'>
123 <span
><?php
echo htmlspecialchars(xl('Print'), ENT_NOQUOTES
); ?
></span
>
134 <?php
if ($form_action) { // if submit ?>
136 <div id
="report_results">
137 <table border
='0' cellpadding
='1' cellspacing
='2' width
='98%'>
138 <thead style
='display:table-header-group'>
140 <th
><?php
xl('Name','e'); ?
></th
>
141 <th
><?php
xl('NDC','e'); ?
></th
>
142 <th
><?php
xl('Form','e'); ?
></th
>
143 <th align
='right'><?php
echo htmlspecialchars(xl('QOH')); ?
></th
>
144 <th align
='right'><?php
echo htmlspecialchars(xl('Reorder')); ?
></th
>
145 <th align
='right'><?php
echo htmlspecialchars(xl('Avg Monthly')); ?
></th
>
146 <th align
='right'><?php
echo htmlspecialchars(xl('Stock Months')); ?
></th
>
147 <th
><?php
echo htmlspecialchars(xl('Warnings')); ?
></th
>
153 while ($row = sqlFetchArray($res)) {
154 $on_hand = 0 +
$row['on_hand'];
155 $drug_id = 0 +
$row['drug_id'];
158 $srow = sqlQuery("SELECT " .
159 "SUM(quantity) AS sale_quantity " .
160 "FROM drug_sales WHERE " .
161 "drug_id = '$drug_id' AND " .
162 "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
166 $bgcolor = "#" . (($encount & 1) ?
"ddddff" : "ffdddd");
168 $sale_quantity = $srow['sale_quantity'];
169 $months = $form_days / 30.5;
171 $monthly = ($months && $sale_quantity) ?
172 sprintf('%0.1f', $sale_quantity / $months) : ' ';
174 $stock_months = ' ';
175 if ($sale_quantity != 0) {
176 $stock_months = sprintf('%0.1f', $on_hand * $months / $sale_quantity);
177 if ($stock_months < 1.0) {
178 addWarning(htmlspecialchars(xl('QOH is less than monthly usage')));
182 // Check for reorder point reached.
183 if (!empty($row['reorder_point']) && $on_hand <= $row['reorder_point']) {
184 addWarning(htmlspecialchars(xl('Reorder point has been reached')));
187 // Compute the smallest quantity that might be taken from a lot based on the
188 // past 30 days of sales. If lot combining is allowed this is always 1.
190 if (!$row['allow_combining']) {
191 $sminrow = sqlQuery("SELECT " .
192 "MIN(quantity) AS min_sale " .
193 "FROM drug_sales WHERE " .
194 "drug_id = '$drug_id' AND " .
195 "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
198 $min_sale = 0 +
$sminrow['min_sale'];
201 // Get all lots that we want to issue warnings about. These are lots
202 // expired, soon to expire, or with insufficient quantity for selling.
203 $ires = sqlStatement("SELECT * " .
204 "FROM drug_inventory WHERE " .
205 "drug_id = '$drug_id' AND " .
207 "destroy_date IS NULL AND ( " .
208 "on_hand < '$min_sale' OR " .
209 "expiration IS NOT NULL AND expiration < DATE_ADD(NOW(), INTERVAL 30 DAY) " .
210 ") ORDER BY lot_number");
212 // Generate warnings associated with individual lots.
213 while ($irow = sqlFetchArray($ires)) {
214 $lotno = $irow['lot_number'];
215 if ($irow['on_hand'] < $min_sale) {
216 addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('quantity seems unusable')));
218 if (!empty($irow['expiration'])) {
219 $expdays = (int) ((strtotime($irow['expiration']) - time()) / (60 * 60 * 24));
221 addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('has expired')));
223 else if ($expdays <= 30) {
224 addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('expires in') . " $expdays " . xl('days')));
229 echo " <tr class='detail' bgcolor='$bgcolor'>\n";
230 echo " <td>" . htmlentities($row['name']) . "</td>\n";
231 echo " <td>" . htmlentities($row['ndc_number']) . "</td>\n";
233 generate_display_field(array('data_type'=>'1','list_id'=>'drug_form'), $row['form']) .
235 echo " <td align='right'>" . $row['on_hand'] . "</td>\n";
236 echo " <td align='right'>" . $row['reorder_point'] . "</td>\n";
237 echo " <td align='right'>$monthly</td>\n";
238 echo " <td align='right'>$stock_months</td>\n";
239 echo " <td style='color:red'>$warnings</td>\n";
246 <?php
} // end if submit ?>