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  require_once("../globals.php");
  10  require_once("$srcdir/acl.inc");
  11  require_once("$srcdir/options.inc.php");
  12  require_once("$include_root/drugs/drugs.inc.php");
  13
  14  // Check authorization.
  15  $thisauth = acl_check('admin', 'drugs');
  16  if (!$thisauth) die(xl('Not authorized'));
  17
  18 function addWarning($msg) {
  19   global $warnings;
  20   if ($warnings) $warnings .= '<br />';
  21   $warnings .= $msg;
  22 }
  23
  24 if (!empty($_POST['form_days'])) {
  25   $form_days = $_POST['form_days'] + 0;
  26 }
  27 else {
  28   $form_days = sprintf('%d', (strtotime(date('Y-m-d')) - strtotime(date('Y-01-01'))) / (60 * 60 * 24) + 1);
  29 }
  30
  31 // get drugs
  32 $res = sqlStatement("SELECT d.*, SUM(di.on_hand) AS on_hand " .
  33   "FROM drugs AS d " .
  34   "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " .
  35   "AND di.on_hand != 0 AND di.destroy_date IS NULL " .
  36   "WHERE d.active = 1 " .
  37   "GROUP BY d.name, d.drug_id ORDER BY d.name, d.drug_id");
  38 ?>
  39 <html>
  40
  41 <head>
  42 <?php html_header_show(); ?>
  43
  44 <link rel="stylesheet" href='<?php  echo $css_header ?>' type='text/css'>
  45 <title><?php  xl('Inventory List','e'); ?></title>
  46
  47 <style>
  48 tr.head   { font-size:10pt; background-color:#cccccc; text-align:center; }
  49 tr.detail { font-size:10pt; }
  50 a, a:visited, a:hover { color:#0000cc; }
  51 </style>
  52
  53 <script type="text/javascript" src="../../library/dialog.js"></script>
  54
  55 <script language="JavaScript">
  56 </script>
  57
  58 </head>
  59
  60 <body>
  61 <center>
  62
  63 <form method='post' action='inventory_list.php' name='theform'>
  64 <table border='0' cellpadding='5' cellspacing='0' width='98%'>
  65  <tr>
  66   <td class='title'>
  67    <?php xl('Inventory List','e'); ?>
  68   </td>
  69   <td class='text' align='right'>
  70    <?php xl('For the past','e'); ?>
  71    <input type="input" name="form_days" size='3' value="<?php echo $form_days; ?>" />
  72    <?php xl('days','e'); ?>&nbsp;
  73    <input type="submit" value="<?php xl('Refresh','e'); ?>" />&nbsp;
  74    <input type="button" value="<?php xl('Print','e'); ?>" onclick="window.print()" />
  75   </td>
  76  </tr>
  77 </table>
  78 </form>
  79
  80 <table width='98%' cellpadding='2' cellspacing='2'>
  81  <thead style='display:table-header-group'>
  82   <tr class='head'>
  83    <th><?php  xl('Name','e'); ?></th>
  84    <th><?php  xl('NDC','e'); ?></th>
  85    <th><?php  xl('Form','e'); ?></th>
  86    <th align='right'><?php  xl('QOH','e'); ?></th>
  87    <th align='right'><?php  xl('Reorder','e'); ?></th>
  88    <th align='right'><?php  xl('Avg Monthly','e'); ?></th>
  89    <th align='right'><?php  xl('Stock Months','e'); ?></th>
  90    <th><?php xl('Warnings','e'); ?></th>
  91   </tr>
  92  </thead>
  93  <tbody>
  94 <?php
  95 $encount = 0;
  96 while ($row = sqlFetchArray($res)) {
  97   $on_hand = 0 + $row['on_hand'];
  98   $drug_id = 0 + $row['drug_id'];
  99   $warnings = '';
 100
 101   $srow = sqlQuery("SELECT " .
 102     "SUM(quantity) AS sale_quantity " .
 103     "FROM drug_sales WHERE " .
 104     "drug_id = '$drug_id' AND " .
 105     "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
 106     "AND pid != 0");
 107
 108   ++$encount;
 109   $bgcolor = "#" . (($encount & 1) ? "ddddff" : "ffdddd");
 110
 111   $sale_quantity = $srow['sale_quantity'];
 112   $months = $form_days / 30.5;
 113
 114   $monthly = ($months && $sale_quantity) ?
 115     sprintf('%0.1f', $sale_quantity / $months) : '&nbsp;';
 116
 117   $stock_months = '&nbsp;';
 118   if ($sale_quantity != 0) {
 119     $stock_months = sprintf('%0.1f', $on_hand * $months / $sale_quantity);
 120     if ($stock_months < 1.0) {
 121       addWarning(xl('QOH is less than monthly usage'));
 122     }
 123   }
 124
 125   // Check for reorder point reached.
 126   if (!empty($row['reorder_point']) && $on_hand <= $row['reorder_point']) {
 127     addWarning(xl('Reorder point has been reached'));
 128   }
 129
 130   // Compute the smallest quantity that might be taken from a lot based on the
 131   // past 30 days of sales.  If lot combining is allowed this is always 1.
 132   $min_sale = 1;
 133   if (!$row['allow_combining']) {
 134     $sminrow = sqlQuery("SELECT " .
 135       "MIN(quantity) AS min_sale " .
 136       "FROM drug_sales WHERE " .
 137       "drug_id = '$drug_id' AND " .
 138       "sale_date > DATE_SUB(NOW(), INTERVAL $form_days DAY) " .
 139       "AND pid != 0 " .
 140       "AND quantity > 0");
 141     $min_sale = 0 + $sminrow['min_sale'];
 142   }
 143
 144   // Get all lots that we want to issue warnings about.  These are lots
 145   // expired, soon to expire, or with insufficient quantity for selling.
 146   $ires = sqlStatement("SELECT * " .
 147     "FROM drug_inventory WHERE " .
 148     "drug_id = '$drug_id' AND " .
 149     "on_hand > 0 AND " .
 150     "destroy_date IS NULL AND ( " .
 151     "on_hand < '$min_sale' OR " .
 152     "expiration IS NOT NULL AND expiration < DATE_ADD(NOW(), INTERVAL 30 DAY) " .
 153     ") ORDER BY lot_number");
 154
 155   // Generate warnings associated with individual lots.
 156   while ($irow = sqlFetchArray($ires)) {
 157     $lotno = $irow['lot_number'];
 158     if ($irow['on_hand'] < $min_sale) {
 159       addWarning(xl('Lot') . " '$lotno' " . xl('quantity seems unusable'));
 160     }
 161     if (!empty($irow['expiration'])) {
 162       $expdays = (int) ((strtotime($irow['expiration']) - time()) / (60 * 60 * 24));
 163       if ($expdays <= 0) {
 164         addWarning(xl('Lot') . " '$lotno' " . xl('has expired'));
 165       }
 166       else if ($expdays <= 30) {
 167         addWarning(xl('Lot') . " '$lotno' " . xl('expires in') . " $expdays " . xl('days'));
 168       }
 169     }
 170   }
 171
 172   echo " <tr class='detail' bgcolor='$bgcolor'>\n";
 173   echo "  <td>" . htmlentities($row['name']) . "</td>\n";
 174   echo "  <td>" . htmlentities($row['ndc_number']) . "</td>\n";
 175   echo "  <td>" .
 176        generate_display_field(array('data_type'=>'1','list_id'=>'drug_form'), $row['form']) .
 177        "</td>\n";
 178   echo "  <td align='right'>" . $row['on_hand'] . "</td>\n";
 179   echo "  <td align='right'>" . $row['reorder_point'] . "</td>\n";
 180   echo "  <td align='right'>$monthly</td>\n";
 181   echo "  <td align='right'>$stock_months</td>\n";
 182   echo "  <td style='color:red'>$warnings</td>\n";
 183   echo " </tr>\n";
 184  }
 185 ?>
 186  </tbody>
 187 </table>
 188
 189 </center>
 190 </body>
 191 </html>