interface/reports/inventory_list.php

   1 <?php
   2
   3 /**
   4  * inventory_list.php
   5  *
   6  * @package   OpenEMR
   7  * @link      http://www.open-emr.org
   8  * @author    Rod Roark <rod@sunsetsystems.com>
   9  * @author    Brady Miller <brady.g.miller@gmail.com>
  10  * @copyright Copyright (c) 2008-2021 Rod Roark <rod@sunsetsystems.com>
  11  * @copyright Copyright (c) 2018-2019 Brady Miller <brady.g.miller@gmail.com>
  12  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  13  */
  14
  15 require_once("../globals.php");
  16 require_once("$srcdir/options.inc.php");
  17 require_once("$include_root/drugs/drugs.inc.php");
  18
  19 use OpenEMR\Common\Acl\AclMain;
  20 use OpenEMR\Common\Csrf\CsrfUtils;
  21 use OpenEMR\Common\Twig\TwigContainer;
  22 use OpenEMR\Core\Header;
  23
  24 if (!empty($_POST)) {
  25     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  26         CsrfUtils::csrfNotVerified();
  27     }
  28 }
  29
  30 // For each sorting option, specify the ORDER BY argument.
  31 //
  32 $ORDERHASH = array(
  33     'name' => 'd.name, d.drug_id',
  34     'act'  => 'd.active, d.name, d.drug_id',
  35     'con'  => 'd.consumable, d.name, d.drug_id',
  36 );
  37
  38 // Check permission for this report.
  39 $auth_drug_reports = $GLOBALS['inhouse_pharmacy'] && (
  40     AclMain::aclCheckCore('admin', 'drugs') ||
  41     AclMain::aclCheckCore('inventory', 'reporting'));
  42 if (!$auth_drug_reports) {
  43     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Inventory List")]);
  44     exit;
  45 }
  46
  47 // Note if user is restricted to any facilities and/or warehouses.
  48 $is_user_restricted = isUserRestricted();
  49
  50 function addWarning($msg)
  51 {
  52     global $warnings, $form_action;
  53     $break = $form_action != 'export' ? '<br />' : '; ';
  54     if ($warnings) {
  55         $warnings .= $break;
  56     }
  57     $warnings .= text($msg);
  58 }
  59
  60 // Check if a product needs to be re-ordered, optionally for a given warehouse.
  61 //
  62 function checkReorder($drug_id, $min, $warehouse = '')
  63 {
  64     global $form_days;
  65
  66     if (!$min) {
  67         return false;
  68     }
  69
  70     $query = "SELECT " .
  71         "SUM(s.quantity) AS sale_quantity " .
  72         "FROM drug_sales AS s " .
  73         "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " .
  74         "WHERE " .
  75         "s.drug_id = ? AND " .
  76         "s.sale_date > DATE_SUB(NOW(), INTERVAL ? DAY) " .
  77         "AND s.pid != 0";
  78     $binds = array($drug_id, $form_days);
  79     if ($warehouse !== '') {
  80         $query .= " AND di.warehouse_id = ?";
  81         $binds[] = $warehouse;
  82     }
  83     $srow = sqlQuery($query, $binds);
  84     $sales = 0 + $srow['sale_quantity'];
  85
  86     $query = "SELECT SUM(on_hand) AS on_hand " .
  87       "FROM drug_inventory AS di WHERE " .
  88       "di.drug_id = ? AND " .
  89       "(di.expiration IS NULL OR di.expiration > NOW()) AND " .
  90       "di.destroy_date IS NULL";
  91     $binds = array($drug_id);
  92     if ($warehouse !== '') {
  93         $query .= " AND di.warehouse_id = ?";
  94         $binds[] = $warehouse;
  95     }
  96     $ohrow = sqlQuery($query, $binds);
  97     $onhand = intval($ohrow['on_hand']);
  98
  99     if (empty($GLOBALS['gbl_min_max_months'])) {
 100         if ($onhand <= $min) {
 101             return true;
 102         }
 103     } else {
 104         if ($sales != 0) {
 105             $stock_months = sprintf('%0.1f', $onhand * ($form_days / 30.41) / $sales);
 106             if ($stock_months <= $min) {
 107                 return true;
 108             }
 109         }
 110     }
 111
 112     return false;
 113 }
 114
 115 // Generate the list of warehouse IDs that the current user is allowed to access.
 116 // This is used to build SQL for $uwcond.
 117 //
 118 function genUserWarehouses($userid = 0)
 119 {
 120     $list = '';
 121     $res = sqlStatement(
 122         "SELECT DISTINCT option_id, option_value FROM list_options WHERE " .
 123         "list_id = 'warehouse' AND activity = 1"
 124     );
 125     while ($row = sqlFetchArray($res)) {
 126         if (isWarehouseAllowed($row['option_value'], $row['option_id'], $userid)) {
 127             if ($list != '') {
 128                 $list .= ', ';
 129             }
 130             // Using add_escape_custom because this string is for a SQL query.
 131             $list .= "'" . add_escape_custom($row['option_id']) . "'";
 132         }
 133     }
 134     return $list;
 135 }
 136
 137 // This counts the number of days that have a starting zero inventory for a given product
 138 // since a given start date with given restrictions for warehouse or facility.
 139 // End date is assumed to be the current date.
 140 //
 141 // function zeroDays($product_id, $begdate, $warehouse_id = '~', $facility_id = 0) {
 142 function zeroDays($product_id, $begdate, $extracond, $extrabind, $min_sale = 1)
 143 {
 144     $today = date('Y-m-d');
 145
 146     $prodcond = '';
 147     $prodbind = array();
 148     if ($product_id) {
 149         $prodcond = "AND di.drug_id = ?";
 150         $prodbind[] = $product_id;
 151     }
 152
 153     // This will be an array where key is date and value is quantity.
 154     // For each date key the value represents net quantity changes for that day.
 155     $qtys = array();
 156
 157     // Force it to have entries for the begin and end dates.
 158     $qtys[$today] = 0;
 159     $qtys[$begdate] = 0;
 160
 161     // Get sums of current inventory quantities.
 162     $query = "SELECT SUM(di.on_hand) AS on_hand " .
 163         "FROM drug_inventory AS di " .
 164         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 165         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 166         "WHERE " .
 167         "di.destroy_date IS NULL $prodcond $extracond";
 168     $row = sqlQuery($query, array_merge($prodbind, $extrabind));
 169     $current_qoh = $row['on_hand'];
 170
 171     // Add sums of destructions done for each date (effectively a type of transaction).
 172     $res = sqlStatement(
 173         "SELECT di.destroy_date, SUM(di.on_hand) AS on_hand " .
 174         "FROM drug_inventory AS di " .
 175         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 176         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 177         "WHERE " .
 178         "di.destroy_date IS NOT NULL AND di.destroy_date >= ? " .
 179         "$prodcond $extracond" .
 180         "GROUP BY di.destroy_date ORDER BY di.destroy_date",
 181         array_merge(array($begdate), $prodbind, $extrabind)
 182     );
 183     while ($row = sqlFetchArray($res)) {
 184         $thisdate = substr($row['destroy_date'], 0, 10);
 185         if (!isset($qtys[$thisdate])) {
 186             $qtys[$thisdate] = 0;
 187         }
 188         $qtys[$thisdate] += $row['on_hand'];
 189     }
 190
 191     // Add sums of other transactions for each date.
 192     // Note sales are positive and purchases are negative.
 193     $res = sqlStatement(
 194         "SELECT ds.sale_date, SUM(ds.quantity) AS quantity " .
 195         "FROM drug_sales AS ds, drug_inventory AS di " .
 196         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 197         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 198         "WHERE " .
 199         "ds.sale_date >= ? AND " .
 200         "di.inventory_id = ds.inventory_id " .
 201         "$prodcond $extracond" .
 202         "GROUP BY ds.sale_date ORDER BY ds.sale_date",
 203         array_merge(array($begdate), $prodbind, $extrabind)
 204     );
 205     while ($row = sqlFetchArray($res)) {
 206         $thisdate = $row['sale_date'];
 207         if (!isset($qtys[$thisdate])) {
 208             $qtys[$thisdate] = 0;
 209         }
 210         $qtys[$thisdate] += $row['quantity'];
 211     }
 212
 213     // Subtract sums of transfers out for each date.
 214     // Quantity for transfers, like purchases, is usually negative.
 215     $res = sqlStatement(
 216         "SELECT ds.sale_date, SUM(ds.quantity) AS quantity " .
 217         "FROM drug_sales AS ds, drug_inventory AS di " .
 218         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 219         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 220         "WHERE " .
 221         "ds.sale_date >= ? AND " .
 222         "di.inventory_id = ds.xfer_inventory_id " .
 223         "$prodcond $extracond" .
 224         "GROUP BY ds.sale_date ORDER BY ds.sale_date",
 225         array_merge(array($begdate), $prodbind, $extrabind)
 226     );
 227     while ($row = sqlFetchArray($res)) {
 228         $thisdate = $row['sale_date'];
 229         if (!isset($qtys[$thisdate])) {
 230             $qtys[$thisdate] = 0;
 231         }
 232         $qtys[$thisdate] -= $row['quantity'];
 233     }
 234
 235     // Sort by reverse date.
 236     krsort($qtys);
 237
 238     $lastdate = '';
 239     $lastqty = $current_qoh;
 240
 241     // This will be the count of days that have zero quantity at the start of the day.
 242     $zerodays = 0;
 243
 244     // Now we traverse the array in descending date order, adding a date's quantity adjustment
 245     // to the running total to get the quantity at the beginning of that date.
 246     foreach ($qtys as $key => $val) {
 247         if ($lastdate && $lastqty < $min_sale) {
 248             // The span of days from $key to start of $lastdate has zero quantity.
 249             // Add that number of days to $zerodays.
 250             $diff = date_diff(date_create($key), date_create($lastdate));
 251             $zerodays += $diff->days;
 252         }
 253         $lastdate = $key;
 254         $lastqty += $val; // giving qoh at the start of $lastdate
 255     }
 256     // The last array entry hasn't been accounted for yet, so do that.
 257     if ($lastqty < $min_sale) {
 258         ++$zerodays;
 259     }
 260     return $zerodays;
 261 }
 262
 263 function write_report_line(&$row)
 264 {
 265     global $form_details, $wrl_last_drug_id, $warnings, $encount, $fwcond, $fwbind, $form_days;
 266     global $gbl_expired_lot_warning_days, $form_facility, $form_warehouse, $form_action;
 267
 268     $emptyvalue = $form_action != 'export' ? '&nbsp;' : '';
 269     $drug_id = (int) $row['drug_id'];
 270     $on_hand = (int) $row['on_hand'];
 271     $warehouse_id = isset($row['warehouse_id']) ? $row['warehouse_id'] : '';
 272     $facility_id = empty($row['option_value']) ? '0' : $row['option_value'];
 273     $warnings = '';
 274
 275     // Get sales in the date range for this drug (and facility or warehouse if details).
 276     if ($form_details == 1) { // facility details
 277         $query = "SELECT " .
 278             "SUM(s.quantity) AS sale_quantity " .
 279             "FROM drug_sales AS s " .
 280             "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " .
 281             "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 282             "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 283             "WHERE " .
 284             "s.drug_id = ? AND " .
 285             "lo.option_value IS NOT NULL AND lo.option_value = ? AND " .
 286             "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " .
 287             "AND s.pid != 0 $fwcond";
 288         $srow = sqlQuery($query, array_merge(array($drug_id, $facility_id), $fwbind));
 289     } elseif ($form_details == 2) { // warehouse details
 290         $query = "SELECT " .
 291             "SUM(s.quantity) AS sale_quantity " .
 292             "FROM drug_sales AS s " .
 293             "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " .
 294             "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 295             "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 296             "WHERE " .
 297             "s.drug_id = ? AND " .
 298             "di.warehouse_id IS NOT NULL AND di.warehouse_id = ? AND " .
 299             "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " .
 300             "AND s.pid != 0 $fwcond";
 301         $srow = sqlQuery($query, array_merge(array($drug_id, $warehouse_id), $fwbind));
 302     } else {
 303         $srow = sqlQuery(
 304             "SELECT " .
 305             "SUM(s.quantity) AS sale_quantity " .
 306             "FROM drug_sales AS s " .
 307             "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " .
 308             "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 309             "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 310             "WHERE " .
 311             "s.drug_id = ? AND " .
 312             "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " .
 313             "AND s.pid != 0 $fwcond",
 314             array_merge(array($drug_id), $fwbind)
 315         );
 316     }
 317     $sale_quantity = $srow['sale_quantity'];
 318
 319     // Compute the smallest quantity that might be taken from ANY lot for this product
 320     // (and facility or warehouse if details) based on the past $form_days days of sales.
 321     // If lot combining is allowed this is always 1.
 322     $extracond = $fwcond;
 323     $extrabind = $fwbind;
 324     if ($form_details == 1) {
 325         $extracond = "AND lo.option_value IS NOT NULL AND lo.option_value = ?";
 326         $extrabind = array($facility_id);
 327     }
 328     if ($form_details == 2) {
 329         $extracond = "AND di.warehouse_id = ?";
 330         $extrabind = array($warehouse_id);
 331     }
 332     $min_sale = 1;
 333     if (!$row['allow_combining']) {
 334         $sminrow = sqlQuery(
 335             "SELECT " .
 336             "MIN(s.quantity) AS min_sale " .
 337             "FROM drug_sales AS s " .
 338             "LEFT JOIN drug_inventory AS di ON di.drug_id = s.drug_id " .
 339             "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 340             "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 341             "WHERE " .
 342             "s.drug_id = ? AND " .
 343             "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " .
 344             "AND s.pid != 0 " .
 345             "AND s.quantity > 0 $extracond",
 346             array_merge(array($drug_id), $extrabind)
 347         );
 348         $min_sale = 0 + $sminrow['min_sale'];
 349     }
 350     if (!$min_sale) {
 351         $min_sale = 1;
 352     }
 353
 354     // Get number of days with no stock.
 355     $today = date('Y-m-d');
 356     $tmp_days = max($form_days - 1, 0);
 357     $begdate = date('Y-m-d', strtotime("$today - $tmp_days days"));
 358     $zerodays = zeroDays($drug_id, $begdate, $extracond, $extrabind, $min_sale);
 359
 360     $months = $form_days / 30.41;
 361
 362     $monthly = ($months && $sale_quantity && $form_days > $zerodays) ?
 363         sprintf('%0.1f', $sale_quantity / $months * $form_days / ($form_days - $zerodays))
 364         : 0;
 365
 366     if ($monthly == 0.0 && $on_hand == 0) {
 367         // The row has no QOH and no recent sales, so is deemed uninteresting.
 368         // See CV email 2014-06-25.
 369         return;
 370     }
 371
 372     if ($drug_id != $wrl_last_drug_id) {
 373         ++$encount;
 374     }
 375     $bgcolor = "#" . (($encount & 1) ? "ddddff" : "ffdddd");
 376
 377     $stock_months = 0;
 378     if ($monthly != 0) {
 379         $stock_months = sprintf('%0.1f', $on_hand / $monthly);
 380         if ($stock_months < 1.0) {
 381             addWarning(xl('QOH is less than monthly usage'));
 382         }
 383     }
 384
 385     // Check for reorder point reached, once per product.
 386     if ($drug_id != $wrl_last_drug_id) {
 387         if (checkReorder($drug_id, $row['reorder_point'])) {
 388             addWarning(xl('Product-level reorder point has been reached'));
 389         }
 390     }
 391     // For warehouse details mode we want the message on the line for this warehouse.
 392     // If the warehouse is not shown because it has no QOH and no recent
 393     // activity, then this message doesn't matter any more either.
 394     if ($form_details == 2) {
 395         if (checkReorder($drug_id, $row['pw_min_level'], $warehouse_id)) {
 396             addWarning(xl("Reorder point has been reached for warehouse") .
 397                 " '" . $row['title'] . "'");
 398         }
 399     }
 400
 401     // Get all lots that we want to issue warnings about.  These are lots
 402     // expired, soon to expire, or with insufficient quantity for selling.
 403     $gbl_expired_lot_warning_days = empty($gbl_expired_lot_warning_days) ? 0 : intval($gbl_expired_lot_warning_days);
 404     if ($gbl_expired_lot_warning_days <= 0) {
 405         $gbl_expired_lot_warning_days = 30;
 406     }
 407     $ires = sqlStatement(
 408         "SELECT di.* " .
 409         "FROM drug_inventory AS di " .
 410         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 411         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 412         "WHERE " .
 413         "di.drug_id = ? AND " .
 414         "di.on_hand > 0 AND " .
 415         "di.destroy_date IS NULL AND ( " .
 416         "di.on_hand < ? OR " .
 417         "di.expiration IS NOT NULL AND di.expiration < DATE_ADD(NOW(), INTERVAL " . escape_limit($gbl_expired_lot_warning_days) . " DAY) " .
 418         ") $extracond ORDER BY di.lot_number",
 419         array_merge(array($drug_id, $min_sale), $extrabind)
 420     );
 421     // Generate warnings associated with individual lots.
 422     while ($irow = sqlFetchArray($ires)) {
 423         $lotno = $irow['lot_number'];
 424         if ($irow['on_hand'] < $min_sale) {
 425             addWarning(xl('Lot') . " '$lotno' " . xl('quantity seems unusable'));
 426         }
 427         if (!empty($irow['expiration'])) {
 428             $expdays = (int) ((strtotime($irow['expiration']) - time()) / (60 * 60 * 24));
 429             if ($expdays <= 0) {
 430                 addWarning(xl('Lot') . " '$lotno' " . xl('has expired'));
 431             } elseif ($expdays <= $gbl_expired_lot_warning_days) {
 432                 addWarning(xl('Lot') . " '$lotno' " . xl('expires in') . " $expdays " . xl('days'));
 433             }
 434         }
 435     }
 436
 437     // Per CV 2014-06-20:
 438     // Reorder Quantity should be calculated only if Stock Months is less than Months Min.
 439     // If Stock Months is [not] less than Months Min, Reorder Quantity should be zero.
 440     // The calculation should be: (Min Months minus Stock Months) times Avg Monthly.
 441     // Reorder Quantity should be rounded up to a whole number.
 442     $reorder_qty = 0;
 443     if ($monthly > 0.00) {
 444         // Note if facility details, this the sum of min levels for the facility's warehouses.
 445         $min_months = 0 + ($form_details ? $row['pw_min_level'] : $row['reorder_point']);
 446         // If min is not specified as months then compute it that way.
 447         if (empty($GLOBALS['gbl_min_max_months'])) {
 448             $min_months /= $monthly;
 449         }
 450         if ($stock_months < $min_months) {
 451             $reorder_qty = ceil(($min_months - $stock_months) * $monthly);
 452         }
 453     }
 454
 455     if (empty($monthly)) {
 456         $monthly = $emptyvalue;
 457     }
 458     if (empty($stock_months)) {
 459         $stock_months = $emptyvalue;
 460     }
 461
 462     $relcodes = '';
 463     $tmp = explode(';', $row['related_code']);
 464     foreach ($tmp as $codestring) {
 465         if ($codestring === '') {
 466             continue;
 467         }
 468         list($codetype, $code) = explode(':', $codestring);
 469         // For IPPF just the IPPFCM codes are wanted.
 470         if ($GLOBALS['ippf_specific'] && $codetype !== 'IPPFCM') {
 471             continue;
 472         }
 473         if ($relcodes) {
 474             $relcodes .= ';';
 475         }
 476         $relcodes .= $codestring;
 477     }
 478
 479     $drug_form = empty($row['form']) ? '' : generate_display_field(
 480         array(
 481             'data_type' => '1',
 482             'list_id' => 'drug_form'
 483         ),
 484         $row['form']
 485     );
 486
 487     if ($form_action == 'export') {
 488         echo csvEscape($row['name'])                          . ',';
 489         echo csvEscape($relcodes)                             . ',';
 490         echo csvEscape($row['ndc_number'])                    . ',';
 491         echo csvEscape($row['active'] ? xl('Yes') : xl('No')) . ',';
 492         echo csvEscape($row['consumable'] ? xl('Yes') : xl('No')) . ',';
 493         echo csvEscape($drug_form) . ',';
 494         if ($form_details) {
 495             echo csvEscape($row['facname'])                   . ',';
 496             if ($form_details == 2) { // warehouse details {
 497                 echo csvEscape($row['title'])                 . ',';
 498             }
 499             echo csvEscape($row['pw_min_level'])              . ',';
 500             echo csvEscape($row['pw_max_level'])              . ',';
 501         } else {
 502             echo csvEscape($row['reorder_point'])             . ',';
 503             echo csvEscape($row['max_level'])                 . ',';
 504         }
 505         echo csvEscape($row['on_hand'])                       . ',';
 506         echo csvEscape($zerodays)                             . ',';
 507         echo csvEscape($monthly)                              . ',';
 508         echo csvEscape($stock_months)                         . ',';
 509         echo csvEscape($reorder_qty)                          . ',';
 510         echo csvEscape($warnings)                             . '';
 511         echo "\n";
 512     } else { // end exporting
 513         echo " <tr class='detail' bgcolor='$bgcolor'>\n";
 514         if ($drug_id == $wrl_last_drug_id) {
 515             echo "  <td colspan='6'>&nbsp;</td>\n";
 516         } else {
 517             echo "  <td>" . text($row['name'])                            . "</td>\n";
 518             echo "  <td>" . text($relcodes)                               . "</td>\n";
 519             echo "  <td>" . text($row['ndc_number'])                      . "</td>\n";
 520             echo "  <td>" . ($row['active'] ? xlt('Yes') : xlt('No'))     . "</td>\n";
 521             echo "  <td>" . ($row['consumable'] ? xlt('Yes') : xlt('No')) . "</td>\n";
 522             echo "  <td>" . text($drug_form)                              . "</td>\n";
 523         }
 524         if ($form_details) {
 525             echo "  <td>" . text($row['facname'])                         . "</td>\n";
 526             if ($form_details == 2) { // warehouse details {
 527                 echo "  <td>" . text($row['title'])                         . "</td>\n";
 528             }
 529             echo "  <td align='right'>" . text($row['pw_min_level'])      . "</td>\n";
 530             echo "  <td align='right'>" . text($row['pw_max_level'])      . "</td>\n";
 531         } else {
 532             echo "  <td align='right'>" . text($row['reorder_point'])     . "</td>\n";
 533             echo "  <td align='right'>" . text($row['max_level'])         . "</td>\n";
 534         }
 535         echo "  <td align='right'>" . text($row['on_hand'])               . "</td>\n";
 536         echo "  <td align='right'>" . text($zerodays)                     . "</td>\n";
 537         // Do not text() these next 2 lines.
 538         echo "  <td align='right'>" . $monthly                            . "</td>\n";
 539         echo "  <td align='right'>" . $stock_months                       . "</td>\n";
 540         echo "  <td align='right'>" . text($reorder_qty)                  . "</td>\n";
 541         // Do not text() the following line.
 542         echo "  <td style='color:red'>" . $warnings                       . "</td>\n";
 543         echo " </tr>\n";
 544     } // end not exporting
 545
 546     $wrl_last_drug_id = $drug_id;
 547 }
 548
 549 if (!empty($_POST['form_days'])) {
 550     $form_days = $_POST['form_days'] + 0;
 551 } else {
 552     $form_days = sprintf('%d', (strtotime(date('Y-m-d')) - strtotime(date('Y-01-01'))) / (60 * 60 * 24) + 1);
 553 }
 554
 555 // this is "" or "submit".
 556 $form_action = $_POST['form_action'] ?? '';
 557
 558 if (!empty($_POST['form_days'])) {
 559     $form_days = $_POST['form_days'] + 0;
 560 } else {
 561     $form_days = sprintf('%d', (strtotime(date('Y-m-d')) - strtotime(date('Y-01-01'))) / (60 * 60 * 24) + 1);
 562 }
 563
 564 // this is "" or "submit" or "export".
 565 $form_action = empty($_POST['form_action']) ? '' : $_POST['form_action'];
 566
 567 $form_inactive = empty($_REQUEST['form_inactive']) ? 0 : 1;
 568 $form_details = empty($_REQUEST['form_details']) ? 0 : intval($_REQUEST['form_details']);
 569 $form_facility = 0 + empty($_REQUEST['form_facility']) ? 0 : $_REQUEST['form_facility'];
 570 $form_consumable = isset($_REQUEST['form_consumable']) ? intval($_REQUEST['form_consumable']) : 0;
 571 $form_orderby = $_REQUEST['form_orderby'] ?? 'name';
 572 $orderby = $ORDERHASH[$form_orderby];
 573
 574 // Incoming form_warehouse, if not empty is in the form "warehouse/facility".
 575 // The facility part is an attribute used by JavaScript logic.
 576 $form_warehouse = $_REQUEST['form_warehouse'] ?? '';
 577 $tmp = explode('/', $form_warehouse);
 578 $form_warehouse = $tmp[0];
 579
 580 $mmtype = empty($GLOBALS['gbl_min_max_months']) ? xl('Units') : xl('Months');
 581
 582 // Compute WHERE condition for filtering on facility/warehouse.
 583 $fwcond = '';
 584 $fwbind = array();
 585 if ($form_facility) {
 586     $fwcond .= " AND lo.option_value IS NOT NULL AND lo.option_value = ?";
 587     $fwbind[] = $form_facility;
 588 }
 589 if ($form_warehouse) {
 590     $fwcond .= " AND di.warehouse_id IS NOT NULL AND di.warehouse_id = ?";
 591     $fwbind[] = $form_warehouse;
 592 }
 593 if ($is_user_restricted) {
 594     $fwcond .= "AND di.warehouse_id IS NOT NULL AND di.warehouse_id IN (" . genUserWarehouses() . ")";
 595 }
 596
 597 // Compute WHERE condition for filtering on activity and consumability (drugs table).
 598 $actcond = '';
 599 if (!$form_inactive) {
 600     $actcond .= " AND d.active = 1";
 601 }
 602 if ($form_consumable) {
 603     if ($form_consumable == 1) {
 604         $actcond .= " AND d.consumable = '1'";
 605     } else {
 606         $actcond .= " AND d.consumable != '1'";
 607     }
 608 }
 609
 610 if ($form_details == 1) {
 611     // Query for the main loop if facility details are wanted.
 612     $query = "SELECT d.*, SUM(di.on_hand) AS on_hand, lo.option_value, fac.name AS facname, " .
 613         "SUM(pw.pw_min_level) AS pw_min_level, SUM(pw.pw_max_level) AS pw_max_level " .
 614         "FROM drugs AS d " .
 615         "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " .
 616         "AND di.on_hand != 0 AND di.destroy_date IS NULL " .
 617         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 618         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 619         "LEFT JOIN facility AS fac ON fac.id = lo.option_value " .
 620         "LEFT JOIN product_warehouse AS pw ON pw.pw_drug_id = d.drug_id AND " .
 621         "pw.pw_warehouse = di.warehouse_id " .
 622         "WHERE 1 = 1 $fwcond $actcond " .
 623         "GROUP BY d.name, d.drug_id, lo.option_value ORDER BY $orderby, lo.option_value";
 624 } elseif ($form_details == 2) {
 625     // Query for the main loop if warehouse/lot details are wanted.
 626     $query = "SELECT d.*, di.on_hand, di.inventory_id, di.lot_number, " .
 627         "di.expiration, di.warehouse_id, lo.title, fac.name AS facname, " .
 628         "pw.pw_min_level, pw.pw_max_level " .
 629         "FROM drugs AS d " .
 630         "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " .
 631         "AND di.on_hand != 0 AND di.destroy_date IS NULL " .
 632         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 633         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 634         "LEFT JOIN facility AS fac ON fac.id = lo.option_value " .
 635         "LEFT JOIN product_warehouse AS pw ON pw.pw_drug_id = d.drug_id AND " .
 636         "pw.pw_warehouse = di.warehouse_id " .
 637         "WHERE 1 = 1 $fwcond $actcond " .
 638         "ORDER BY $orderby, lo.title, di.warehouse_id, di.lot_number, di.inventory_id";
 639 } else {
 640     // Query for the main loop if summary report.
 641     $query = "SELECT d.*, SUM(di.on_hand) AS on_hand " .
 642         "FROM drugs AS d " .
 643         "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " .
 644         "AND di.on_hand != 0 AND di.destroy_date IS NULL " .
 645         "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 646         "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 647         "WHERE 1 = 1 $fwcond $actcond " .
 648         "GROUP BY $orderby ORDER BY $orderby";
 649 }
 650
 651 // $res = sqlStatement($query);
 652 $res = sqlStatement($query, $fwbind);
 653
 654 if ($form_action == 'export') {
 655     header("Pragma: public");
 656     header("Expires: 0");
 657     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 658     header("Content-Type: application/force-download; charset=utf-8");
 659     header("Content-Disposition: attachment; filename=inventory_list.csv");
 660     header("Content-Description: File Transfer");
 661     // Prepend a BOM (Byte Order Mark) header to mark the data as UTF-8.  This is
 662     // said to work for Excel 2007 pl3 and up and perhaps also Excel 2003 pl3.  See:
 663     // http://stackoverflow.com/questions/155097/microsoft-excel-mangles-diacritics-in-csv-files
 664     // http://crashcoursing.blogspot.com/2011/05/exporting-csv-with-special-characters.html
 665     echo "\xEF\xBB\xBF";
 666
 667     // CSV headers:
 668     echo csvEscape(xl('Name'))              . ',';
 669     echo csvEscape(xl('Relates To'))        . ',';
 670     echo csvEscape(xl('NDC'))               . ',';
 671     echo csvEscape(xl('Active'))            . ',';
 672     echo csvEscape(xl('Consumable'))        . ',';
 673     echo csvEscape(xl('Form'))              . ',';
 674     if ($form_details) {
 675         echo csvEscape(xl('Facility'))      . ',';
 676         if ($form_details == 2) {
 677             echo csvEscape(xl('Warehouse')) . ',';
 678         }
 679         echo csvEscape($mmtype . xl('Min')) . ',';
 680         echo csvEscape($mmtype . xl('Max')) . ',';
 681     }
 682     echo csvEscape(xl('QOH'))               . ',';
 683     echo csvEscape(xl('Zero Stock Days'))   . ',';
 684     echo csvEscape(xl('Avg Monthly'))       . ',';
 685     echo csvEscape(xl('Stock Months'))      . ',';
 686     echo csvEscape(xl('Reorder Qty'))       . ',';
 687     echo csvEscape(xl('Warnings'))          . '';
 688     echo "\n";
 689 } else { // not exporting
 690     ?>
 691 <html>
 692
 693 <head>
 694
 695 <title><?php echo xlt('Inventory List'); ?></title>
 696
 697     <?php Header::setupHeader(['report-helper']); ?>
 698
 699 <style>
 700 /* specifically include & exclude from printing */
 701 @media print {
 702  #report_parameters {visibility: hidden; display: none;}
 703  #report_parameters_daterange {visibility: visible; display: inline;}
 704  #report_results {margin-top: 30px;}
 705 }
 706 /* specifically exclude some from the screen */
 707 @media screen {
 708  #report_parameters_daterange {visibility: hidden; display: none;}
 709 }
 710
 711 body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
 712
 713 tr.head   { font-size:10pt; background-color:#cccccc; text-align:center; }
 714 tr.detail { font-size:10pt; }
 715 a, a:visited, a:hover { color:#0000cc; }
 716
 717 table.mymaintable, table.mymaintable td, table.mymaintable th {
 718  border: 1px solid #aaaaaa;
 719  border-collapse: collapse;
 720 }
 721 table.mymaintable td, table.mymaintable th {
 722  padding: 1pt 4pt 1pt 4pt;
 723 }
 724 </style>
 725
 726 <script>
 727
 728  $(function () {
 729   oeFixedHeaderSetup(document.getElementById('mymaintable'));
 730   var win = top.printLogSetup ? top : opener.top;
 731   win.printLogSetup(document.getElementById('printbutton'));
 732  });
 733
 734  function mysubmit(action) {
 735   var f = document.forms[0];
 736   f.form_action.value = action;
 737   top.restoreSession();
 738   f.submit();
 739  }
 740
 741  function dosort(orderby) {
 742   var f = document.forms[0];
 743   f.form_orderby.value = orderby;
 744   f.form_action.value = 'submit';
 745   top.restoreSession();
 746   f.submit();
 747   return false;
 748  }
 749
 750  // Enable/disable warehouse options depending on current facility.
 751  function facchanged() {
 752   var f = document.forms[0];
 753   var facid = f.form_facility.value;
 754   var theopts = f.form_warehouse.options;
 755   for (var i = 1; i < theopts.length; ++i) {
 756    var tmp = theopts[i].value.split('/');
 757    var dis = facid && (tmp.length < 2 || tmp[1] != facid);
 758    theopts[i].disabled = dis;
 759    if (dis) theopts[i].selected = false;
 760   }
 761  }
 762
 763 </script>
 764
 765 </head>
 766
 767 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
 768
 769 <center>
 770
 771 <form method='post' action='inventory_list.php' name='theform' onsubmit='return top.restoreSession()'>
 772 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 773
 774 <!-- form_action is set to "submit" or "export" at form submit time -->
 775 <input type='hidden' name='form_action' value='' />
 776
 777 <!-- Sorting key -->
 778 <input type="hidden" name="form_orderby" value="<?php echo attr($form_orderby) ?>" />
 779
 780 <div id="report_parameters">
 781 <table>
 782  <tr>
 783   <td class='title'>
 784     <?php echo xlt('Inventory List'); ?>
 785   </td>
 786   <td class='text' align='right'>
 787     <?php
 788     // Build a drop-down list of facilities.
 789     //
 790     $query = "SELECT id, name FROM facility ORDER BY name";
 791     $fres = sqlStatement($query);
 792     echo "   <select name='form_facility' onchange='facchanged()'>\n";
 793     echo "    <option value=''>-- " . xlt('All Facilities') . " --\n";
 794     while ($frow = sqlFetchArray($fres)) {
 795         $facid = $frow['id'];
 796         if ($is_user_restricted && !isFacilityAllowed($facid)) {
 797             continue;
 798         }
 799         echo "    <option value='" . attr($facid) . "'";
 800         if ($facid == $form_facility) {
 801             echo " selected";
 802         }
 803         echo ">" . text($frow['name']) . "\n";
 804     }
 805     echo "   </select>&nbsp;\n";
 806     echo "   <select name='form_warehouse'>\n";
 807     echo "    <option value=''>" . xlt('All Warehouses') . "</option>\n";
 808     $lres = sqlStatement(
 809         "SELECT * FROM list_options " .
 810         "WHERE list_id = 'warehouse' AND activity = 1 ORDER BY seq, title"
 811     );
 812     while ($lrow = sqlFetchArray($lres)) {
 813         $whid = $lrow['option_id'];
 814         $facid = $lrow['option_value'];
 815         if ($is_user_restricted && !isWarehouseAllowed($facid, $whid)) {
 816             continue;
 817         }
 818         echo "    <option value='" . attr($whid . "/" . $facid) . "'";
 819         echo " id='fac" . attr($facid) . "'";
 820         if (strlen($form_warehouse)  > 0 && $whid == $form_warehouse) {
 821             echo " selected";
 822         }
 823         echo ">" . text(xl_list_label($lrow['title'])) . "</option>\n";
 824     }
 825     echo "   </select>&nbsp;\n";
 826     ?>
 827
 828     <?php echo xlt('For the past'); ?>
 829    <input type="input" name="form_days" size='3' value="<?php echo attr($form_days); ?>" />
 830     <?php echo xlt('days'); ?>&nbsp;
 831
 832    <select name='form_consumable'><?php
 833     foreach (
 834         array(
 835         '0' => xl('All Product Types'),
 836         '1' => xl('Consumable Only'),
 837         '2' => xl('Non-Consumable Only'),
 838         ) as $key => $value
 839     ) {
 840         echo "<option value='" . attr($key) . "'";
 841         if ($key == $form_consumable) {
 842             echo " selected";
 843         }
 844         echo ">" . text($value) . "</option>";
 845     }
 846     ?></select>&nbsp;
 847
 848    <input type='checkbox' name='form_inactive' value='1'<?php if ($form_inactive) {
 849         echo " checked";} ?>
 850    /><?php echo xlt('Include Inactive'); ?>&nbsp;
 851
 852     <?php
 853     echo "   <select name='form_details'>\n";
 854     $tmparr = array(0 => xl('Summary'), 1 => xl('Facility Details'), 2 => xl('Warehouse Details'));
 855     foreach ($tmparr as $key => $value) {
 856         echo "    <option value='" . attr($key) . "'";
 857         if ($key == $form_details) {
 858             echo " selected";
 859         }
 860         echo ">" . text($value) . "\n";
 861     }
 862     echo "   </select>&nbsp;\n";
 863     ?>
 864    <a href='#' class='btn btn-primary' onclick='mysubmit("submit")' style='margin-left:1em'>
 865     <span><?php echo xlt('Refresh'); ?></span>
 866    </a>
 867    <a href='#' class='btn btn-primary' onclick='mysubmit("export")' style='margin-left:1em'>
 868     <span><?php echo xlt('Export to CSV'); ?></span>
 869    </a>
 870    <a href='#' class='btn btn-primary' onclick='window.print()' style='margin-left:1em' id='printbutton'>
 871     <span><?php echo xlt('Print'); ?></span>
 872    </a>
 873   </td>
 874  </tr>
 875 </table>
 876 </div>
 877
 878     <?php
 879     if ($form_action) {
 880         // We are not exporting and have submitted the form.
 881         ?>
 882 <div id="report_results">
 883 <table width='98%' id='mymaintable' class='mymaintable'>
 884  <thead style='display:table-header-group'>
 885   <tr class='head'>
 886    <th>
 887     <a href="#" onclick="return dosort('name')"
 888         <?php if ($form_orderby == "name") {
 889             echo " style=\"color:#00cc00\"";} ?>>
 890         <?php echo xlt('Name'); ?> </a>
 891    </th>
 892    <th><?php echo xlt('Relates To'); ?></th>
 893    <th><?php echo xlt('NDC'); ?></th>
 894    <th>
 895     <a href="#" onclick="return dosort('act')"
 896         <?php echo $form_orderby == "act" ? " style=\"color:#00cc00\"" : ""; ?>>
 897         <?php echo xlt('Active'); ?> </a>
 898    </th>
 899    <th>
 900     <a href="#" onclick="return dosort('con')"
 901         <?php echo $form_orderby == "con" ? " style=\"color:#00cc00\"" : ""; ?>>
 902         <?php echo xlt('Consumable'); ?> </a>
 903    </th>
 904    <th><?php echo xlt('Form'); ?></th>
 905         <?php if ($form_details) { ?>
 906    <th><?php echo xlt('Facility'); ?></th>
 907             <?php if ($form_details == 2) { ?>
 908    <th><?php echo xlt('Warehouse'); ?></th>
 909 <?php } ?>
 910 <?php } ?>
 911    <th align='right'><?php echo text("$mmtype " . xl('Min')); ?></th>
 912    <th align='right'><?php echo text("$mmtype " . xl('Max')); ?></th>
 913    <th align='right'><?php echo xlt('QOH'); ?></th>
 914    <th align='right'><?php echo xlt('Zero Stock Days'); ?></th>
 915    <th align='right'><?php echo xlt('Avg Monthly'); ?></th>
 916    <th align='right'><?php echo xlt('Stock Months'); ?></th>
 917    <th align='right'><?php echo xlt('Reorder Qty'); ?></th>
 918    <th><?php echo xlt('Warnings'); ?></th>
 919   </tr>
 920  </thead>
 921  <tbody>
 922         <?php
 923     } // end if submit
 924 } // end not exporting
 925
 926 if ($form_action) { // if submit
 927     $encount = 0;
 928     $last_drug_id = '';
 929     $wrl_last_drug_id = '';
 930     $warehouse_row = array('drug_id' => 0, 'warehouse_id' => '');
 931
 932     while ($row = sqlFetchArray($res)) {
 933         $drug_id = (int) $row['drug_id'];
 934         if ($form_details == 2) {
 935             if ($drug_id != $last_drug_id || $row['warehouse_id'] != $warehouse_row['warehouse_id']) {
 936                 if (!empty($warehouse_row['drug_id'])) {
 937                     write_report_line($warehouse_row);
 938                 }
 939                 $warehouse_row = $row;
 940                 $warehouse_row['on_hand'] = 0;
 941             }
 942             $warehouse_row['on_hand'] += $row['on_hand'];
 943         } else {
 944             write_report_line($row);
 945         }
 946         $last_drug_id = $drug_id;
 947     }
 948
 949     if ($form_details == 2) {
 950         if (!empty($warehouse_row['drug_id'])) {
 951             write_report_line($warehouse_row);
 952         }
 953     }
 954
 955     if ($form_action != 'export') {
 956         echo " </tbody>\n";
 957         echo "</table>\n";
 958         echo "</div>\n";
 959     }
 960 } // end if submit
 961
 962 if ($form_action != 'export') {
 963     ?>
 964 </form>
 965 </center>
 966 </body>
 967 </html>
 968     <?php
 969 } // end not export