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