interface/reports/inventory_list.php

   1 <?php
   2  // Copyright (C) 2008-2010 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 </style>
  76
  77 <script type="text/javascript" src="../../library/dialog.js"></script>
  78 <script type="text/javascript" src="<?php echo $GLOBALS['webroot'] ?>/library/js/jquery-1.9.1.min.js"></script>
  79
  80 <script language="JavaScript">
  81
  82  $(document).ready(function() {
  83   var win = top.printLogSetup ? top : opener.top;
  84   win.printLogSetup(document.getElementById('printbutton'));
  85  });
  86
  87  function mysubmit(action) {
  88   var f = document.forms[0];
  89   f.form_action.value = action;
  90   top.restoreSession();
  91   f.submit();
  92  }
  93
  94 </script>
  95
  96 </head>
  97
  98 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
  99
 100 <center>
 101
 102 <h2><?php echo htmlspecialchars(xl('Inventory List'))?></h2>
 103
 104 <form method='post' action='inventory_list.php' name='theform'>
 105
 106 <div id="report_parameters">
 107 <!-- form_action is set to "submit" at form submit time -->
 108 <input type='hidden' name='form_action' value='' />
 109 <table>
 110  <tr>
 111   <td width='50%'>
 112    <table class='text'>
 113     <tr>
 114      <td nowrap>
 115       <?php echo htmlspecialchars(xl('For the past')); ?>
 116       <input type="input" name="form_days" size='3' value="<?php echo $form_days; ?>" />
 117       <?php echo htmlspecialchars(xl('days')); ?>
 118      </td>
 119     </tr>
 120    </table>
 121   </td>
 122   <td align='left' valign='middle'>
 123    <table style='border-left:1px solid; width:100%; height:100%'>
 124     <tr>
 125      <td valign='middle'>
 126       <a href='#' class='css_button' onclick='mysubmit("submit")' style='margin-left:1em'>
 127        <span><?php echo htmlspecialchars(xl('Submit'), ENT_NOQUOTES); ?></span>
 128       </a>
 129 <?php if ($form_action) { ?>
 130       <a href='#' class='css_button' id='printbutton' style='margin-left:1em'>
 131        <span><?php echo htmlspecialchars(xl('Print'), ENT_NOQUOTES); ?></span>
 132       </a>
 133 <?php } ?>
 134      </td>
 135     </tr>
 136    </table>
 137   </td>
 138  </tr>
 139 </table>
 140 </div>
 141
 142 <?php if ($form_action) { // if submit ?>
 143
 144 <div id="report_results">
 145 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 146  <thead style='display:table-header-group'>
 147   <tr class='head'>
 148    <th><?php  xl('Name','e'); ?></th>
 149    <th><?php  xl('NDC','e'); ?></th>
 150    <th><?php  xl('Form','e'); ?></th>
 151    <th align='right'><?php echo htmlspecialchars(xl('QOH')); ?></th>
 152    <th align='right'><?php echo htmlspecialchars(xl('Reorder')); ?></th>
 153    <th align='right'><?php echo htmlspecialchars(xl('Avg Monthly')); ?></th>
 154    <th align='right'><?php echo htmlspecialchars(xl('Stock Months')); ?></th>
 155    <th><?php echo htmlspecialchars(xl('Warnings')); ?></th>
 156   </tr>
 157  </thead>
 158  <tbody>
 159 <?php
 160 $encount = 0;
 161 while ($row = sqlFetchArray($res)) {
 162   $on_hand = 0 + $row['on_hand'];
 163   $drug_id = 0 + $row['drug_id'];
 164   $warnings = '';
 165
 166   $srow = sqlQuery("SELECT " .
 167     "SUM(quantity) AS sale_quantity " .
 168     "FROM drug_sales WHERE " .
 169     "drug_id = '$drug_id' AND " .
 170     "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
 171     "AND pid != 0");
 172
 173   ++$encount;
 174   $bgcolor = "#" . (($encount & 1) ? "ddddff" : "ffdddd");
 175
 176   $sale_quantity = $srow['sale_quantity'];
 177   $months = $form_days / 30.5;
 178
 179   $monthly = ($months && $sale_quantity) ?
 180     sprintf('%0.1f', $sale_quantity / $months) : '&nbsp;';
 181
 182   $stock_months = '&nbsp;';
 183   if ($sale_quantity != 0) {
 184     $stock_months = sprintf('%0.1f', $on_hand * $months / $sale_quantity);
 185     if ($stock_months < 1.0) {
 186       addWarning(htmlspecialchars(xl('QOH is less than monthly usage')));
 187     }
 188   }
 189
 190   // Check for reorder point reached.
 191   if (!empty($row['reorder_point']) && $on_hand <= $row['reorder_point']) {
 192     addWarning(htmlspecialchars(xl('Reorder point has been reached')));
 193   }
 194
 195   // Compute the smallest quantity that might be taken from a lot based on the
 196   // past 30 days of sales.  If lot combining is allowed this is always 1.
 197   $min_sale = 1;
 198   if (!$row['allow_combining']) {
 199     $sminrow = sqlQuery("SELECT " .
 200       "MIN(quantity) AS min_sale " .
 201       "FROM drug_sales WHERE " .
 202       "drug_id = '$drug_id' AND " .
 203       "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
 204       "AND pid != 0 " .
 205       "AND quantity > 0");
 206     $min_sale = 0 + $sminrow['min_sale'];
 207   }
 208
 209   // Get all lots that we want to issue warnings about.  These are lots
 210   // expired, soon to expire, or with insufficient quantity for selling.
 211   $ires = sqlStatement("SELECT * " .
 212     "FROM drug_inventory WHERE " .
 213     "drug_id = '$drug_id' AND " .
 214     "on_hand > 0 AND " .
 215     "destroy_date IS NULL AND ( " .
 216     "on_hand < '$min_sale' OR " .
 217     "expiration IS NOT NULL AND expiration < DATE_ADD(NOW(), INTERVAL 30 DAY) " .
 218     ") ORDER BY lot_number");
 219
 220   // Generate warnings associated with individual lots.
 221   while ($irow = sqlFetchArray($ires)) {
 222     $lotno = $irow['lot_number'];
 223     if ($irow['on_hand'] < $min_sale) {
 224       addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('quantity seems unusable')));
 225     }
 226     if (!empty($irow['expiration'])) {
 227       $expdays = (int) ((strtotime($irow['expiration']) - time()) / (60 * 60 * 24));
 228       if ($expdays <= 0) {
 229         addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('has expired')));
 230       }
 231       else if ($expdays <= 30) {
 232         addWarning(htmlspecialchars(xl('Lot') . " '$lotno' " . xl('expires in') . " $expdays " . xl('days')));
 233       }
 234     }
 235   }
 236
 237   echo " <tr class='detail' bgcolor='$bgcolor'>\n";
 238   echo "  <td>" . htmlentities($row['name']) . "</td>\n";
 239   echo "  <td>" . htmlentities($row['ndc_number']) . "</td>\n";
 240   echo "  <td>" .
 241        generate_display_field(array('data_type'=>'1','list_id'=>'drug_form'), $row['form']) .
 242        "</td>\n";
 243   echo "  <td align='right'>" . $row['on_hand'] . "</td>\n";
 244   echo "  <td align='right'>" . $row['reorder_point'] . "</td>\n";
 245   echo "  <td align='right'>$monthly</td>\n";
 246   echo "  <td align='right'>$stock_months</td>\n";
 247   echo "  <td style='color:red'>$warnings</td>\n";
 248   echo " </tr>\n";
 249  }
 250 ?>
 251  </tbody>
 252 </table>
 253
 254 <?php } // end if submit ?>
 255
 256 </form>
 257 </center>
 258 </body>
 259 </html>