interface/reports/inventory_list.php

   1 <?php
   2  // Copyright (C) 2008-2016 Rod Roark <rod@sunsetsystems.com>
   3  //
   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.
   8
   9 //SANITIZE ALL ESCAPES
  10 $sanitize_all_escapes=true;
  11 //
  12
  13 //STOP FAKE REGISTER GLOBALS
  14 $fake_register_globals=false;
  15 //
  16
  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");
  21
  22  // Check authorization.
  23  $thisauth = acl_check('admin', 'drugs');
  24  if (!$thisauth) die(xl('Not authorized'));
  25
  26 function addWarning($msg) {
  27   global $warnings;
  28   if ($warnings) $warnings .= '<br />';
  29   $warnings .= $msg;
  30 }
  31
  32 // this is "" or "submit".
  33 $form_action = $_POST['form_action'];
  34
  35 if (!empty($_POST['form_days'])) {
  36   $form_days = $_POST['form_days'] + 0;
  37 }
  38 else {
  39   $form_days = sprintf('%d', (strtotime(date('Y-m-d')) - strtotime(date('Y-01-01'))) / (60 * 60 * 24) + 1);
  40 }
  41
  42 // get drugs
  43 $res = sqlStatement("SELECT d.*, SUM(di.on_hand) AS on_hand " .
  44   "FROM drugs AS d " .
  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");
  49 ?>
  50 <html>
  51
  52 <head>
  53 <?php html_header_show(); ?>
  54
  55 <link rel="stylesheet" href='<?php  echo $css_header ?>' type='text/css'>
  56 <title><?php  xl('Inventory List','e'); ?></title>
  57
  58 <style>
  59 /* specifically include & exclude from printing */
  60 @media print {
  61  #report_parameters {visibility: hidden; display: none;}
  62  #report_parameters_daterange {visibility: visible; display: inline;}
  63  #report_results {margin-top: 30px;}
  64 }
  65 /* specifically exclude some from the screen */
  66 @media screen {
  67  #report_parameters_daterange {visibility: hidden; display: none;}
  68 }
  69
  70 body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
  71
  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; }
  75
  76 table.mymaintable, table.mymaintable td, table.mymaintable th {
  77  border: 1px solid #aaaaaa;
  78  border-collapse: collapse;
  79 }
  80 table.mymaintable td, table.mymaintable th {
  81  padding: 1pt 4pt 1pt 4pt;
  82 }
  83 </style>
  84
  85 <script type="text/javascript" src="../../library/dialog.js?v=<?php echo $v_js_includes; ?>"></script>
  86 <script type="text/javascript" src="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-min-1-9-1/index.js"></script>
  87 <script type="text/javascript" src="../../library/js/report_helper.js?v=<?php echo $v_js_includes; ?>"></script>
  88
  89 <script language="JavaScript">
  90
  91  $(document).ready(function() {
  92   oeFixedHeaderSetup(document.getElementById('mymaintable'));
  93   var win = top.printLogSetup ? top : opener.top;
  94   win.printLogSetup(document.getElementById('printbutton'));
  95  });
  96
  97  function mysubmit(action) {
  98   var f = document.forms[0];
  99   f.form_action.value = action;
 100   top.restoreSession();
 101   f.submit();
 102  }
 103
 104 </script>
 105
 106 </head>
 107
 108 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
 109
 110 <center>
 111
 112 <h2><?php echo htmlspecialchars(xl('Inventory List'))?></h2>
 113
 114 <form method='post' action='inventory_list.php' name='theform'>
 115
 116 <div id="report_parameters">
 117 <!-- form_action is set to "submit" at form submit time -->
 118 <input type='hidden' name='form_action' value='' />
 119 <table>
 120  <tr>
 121   <td width='50%'>
 122    <table class='text'>
 123     <tr>
 124      <td nowrap>
 125       <?php echo htmlspecialchars(xl('For the past')); ?>
 126       <input type="input" name="form_days" size='3' value="<?php echo $form_days; ?>" />
 127       <?php echo htmlspecialchars(xl('days')); ?>
 128      </td>
 129     </tr>
 130    </table>
 131   </td>
 132   <td align='left' valign='middle'>
 133    <table style='border-left:1px solid; width:100%; height:100%'>
 134     <tr>
 135      <td valign='middle'>
 136       <a href='#' class='css_button' onclick='mysubmit("submit")' style='margin-left:1em'>
 137        <span><?php echo htmlspecialchars(xl('Submit'), ENT_NOQUOTES); ?></span>
 138       </a>
 139 <?php if ($form_action) { ?>
 140       <a href='#' class='css_button' id='printbutton' style='margin-left:1em'>
 141        <span><?php echo htmlspecialchars(xl('Print'), ENT_NOQUOTES); ?></span>
 142       </a>
 143 <?php } ?>
 144      </td>
 145     </tr>
 146    </table>
 147   </td>
 148  </tr>
 149 </table>
 150 </div>
 151
 152 <?php if ($form_action) { // if submit ?>
 153
 154 <div id="report_results">
 155 <table width='98%' id='mymaintable' class='mymaintable'>
 156  <thead style='display:table-header-group'>
 157   <tr class='head'>
 158    <th><?php  xl('Name','e'); ?></th>
 159    <th><?php  xl('NDC','e'); ?></th>
 160    <th><?php  xl('Form','e'); ?></th>
 161    <th align='right'><?php echo htmlspecialchars(xl('QOH')); ?></th>
 162    <th align='right'><?php echo htmlspecialchars(xl('Reorder')); ?></th>
 163    <th align='right'><?php echo htmlspecialchars(xl('Avg Monthly')); ?></th>
 164    <th align='right'><?php echo htmlspecialchars(xl('Stock Months')); ?></th>
 165    <th><?php echo htmlspecialchars(xl('Warnings')); ?></th>
 166   </tr>
 167  </thead>
 168  <tbody>
 169 <?php
 170 $encount = 0;
 171 while ($row = sqlFetchArray($res)) {
 172   $on_hand = 0 + $row['on_hand'];
 173   $drug_id = 0 + $row['drug_id'];
 174   $warnings = '';
 175
 176   $srow = sqlQuery("SELECT " .
 177     "SUM(quantity) AS sale_quantity " .
 178     "FROM drug_sales WHERE " .
 179     "drug_id = '$drug_id' AND " .
 180     "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
 181     "AND pid != 0");
 182
 183   ++$encount;
 184   $bgcolor = "#" . (($encount & 1) ? "ddddff" : "ffdddd");
 185
 186   $sale_quantity = $srow['sale_quantity'];
 187   $months = $form_days / 30.5;
 188
 189   $monthly = ($months && $sale_quantity) ?
 190     sprintf('%0.1f', $sale_quantity / $months) : '&nbsp;';
 191
 192   $stock_months = '&nbsp;';
 193   if ($sale_quantity != 0) {
 194     $stock_months = sprintf('%0.1f', $on_hand * $months / $sale_quantity);
 195     if ($stock_months < 1.0) {
 196       addWarning(htmlspecialchars(xl('QOH is less than monthly usage')));
 197     }
 198   }
 199
 200   // Check for reorder point reached.
 201   if (!empty($row['reorder_point']) && $on_hand <= $row['reorder_point']) {
 202     addWarning(htmlspecialchars(xl('Reorder point has been reached')));
 203   }
 204
 205   // Compute the smallest quantity that might be taken from a lot based on the
 206   // past 30 days of sales.  If lot combining is allowed this is always 1.
 207   $min_sale = 1;
 208   if (!$row['allow_combining']) {
 209     $sminrow = sqlQuery("SELECT " .
 210       "MIN(quantity) AS min_sale " .
 211       "FROM drug_sales WHERE " .
 212       "drug_id = '$drug_id' AND " .
 213       "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
 214       "AND pid != 0 " .
 215       "AND quantity > 0");
 216     $min_sale = 0 + $sminrow['min_sale'];
 217   }
 218
 219   // Get all lots that we want to issue warnings about.  These are lots
 220   // expired, soon to expire, or with insufficient quantity for selling.
 221   $ires = sqlStatement("SELECT * " .
 222     "FROM drug_inventory WHERE " .
 223     "drug_id = '$drug_id' AND " .
 224     "on_hand > 0 AND " .
 225     "destroy_date IS NULL AND ( " .
 226     "on_hand < '$min_sale' OR " .
 227     "expiration IS NOT NULL AND expiration < DATE_ADD(NOW(), INTERVAL 30 DAY) " .
 228     ") ORDER BY lot_number");
 229
 230   // Generate warnings associated with individual lots.
 231   while ($irow = sqlFetchArray($ires)) {
 232     $lotno = $irow['lot_number'];
 233     if ($irow['on_hand'] < $min_sale) {
 234       addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('quantity seems unusable')));
 235     }
 236     if (!empty($irow['expiration'])) {
 237       $expdays = (int) ((strtotime($irow['expiration']) - time()) / (60 * 60 * 24));
 238       if ($expdays <= 0) {
 239         addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('has expired')));
 240       }
 241       else if ($expdays <= 30) {
 242         addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('expires in') . " $expdays " . xl('days')));
 243       }
 244     }
 245   }
 246
 247   echo " <tr class='detail' bgcolor='$bgcolor'>\n";
 248   echo "  <td>" . htmlentities($row['name']) . "</td>\n";
 249   echo "  <td>" . htmlentities($row['ndc_number']) . "</td>\n";
 250   echo "  <td>" .
 251        generate_display_field(array('data_type'=>'1','list_id'=>'drug_form'), $row['form']) .
 252        "</td>\n";
 253   echo "  <td align='right'>" . $row['on_hand'] . "</td>\n";
 254   echo "  <td align='right'>" . $row['reorder_point'] . "</td>\n";
 255   echo "  <td align='right'>$monthly</td>\n";
 256   echo "  <td align='right'>$stock_months</td>\n";
 257   echo "  <td style='color:red'>$warnings</td>\n";
 258   echo " </tr>\n";
 259  }
 260 ?>
 261  </tbody>
 262 </table>
 263
 264 <?php } // end if submit ?>
 265
 266 </form>
 267 </center>
 268 </body>
 269 </html>