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