<?php echo xlt('Inventory List'); ?>

* @author Brady Miller * @copyright Copyright (c) 2008-2021 Rod Roark * @copyright Copyright (c) 2018-2019 Brady Miller * @license https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3 */ require_once("../globals.php"); require_once("$srcdir/options.inc.php"); require_once("$include_root/drugs/drugs.inc.php"); use OpenEMR\Common\Acl\AclMain; use OpenEMR\Common\Csrf\CsrfUtils; use OpenEMR\Common\Twig\TwigContainer; use OpenEMR\Core\Header; if (!empty($_POST)) { if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) { CsrfUtils::csrfNotVerified(); } } // For each sorting option, specify the ORDER BY argument. // $ORDERHASH = array( 'name' => 'd.name, d.drug_id', 'act' => 'd.active, d.name, d.drug_id', 'con' => 'd.consumable, d.name, d.drug_id', ); // Check permission for this report. $auth_drug_reports = $GLOBALS['inhouse_pharmacy'] && ( AclMain::aclCheckCore('admin', 'drugs') || AclMain::aclCheckCore('inventory', 'reporting')); if (!$auth_drug_reports) { echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Inventory List")]); exit; } // Note if user is restricted to any facilities and/or warehouses. $is_user_restricted = isUserRestricted(); function addWarning($msg) { global $warnings, $form_action; $break = $form_action != 'export' ? '
' : '; '; if ($warnings) { $warnings .= $break; } $warnings .= text($msg); } // Check if a product needs to be re-ordered, optionally for a given warehouse. // function checkReorder($drug_id, $min, $warehouse = '') { global $form_days; if (!$min) { return false; } $query = "SELECT " . "SUM(s.quantity) AS sale_quantity " . "FROM drug_sales AS s " . "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " . "WHERE " . "s.drug_id = ? AND " . "s.sale_date > DATE_SUB(NOW(), INTERVAL ? DAY) " . "AND s.pid != 0"; $binds = array($drug_id, $form_days); if ($warehouse !== '') { $query .= " AND di.warehouse_id = ?"; $binds[] = $warehouse; } $srow = sqlQuery($query, $binds); $sales = 0 + $srow['sale_quantity']; $query = "SELECT SUM(on_hand) AS on_hand " . "FROM drug_inventory AS di WHERE " . "di.drug_id = ? AND " . "(di.expiration IS NULL OR di.expiration > NOW()) AND " . "di.destroy_date IS NULL"; $binds = array($drug_id); if ($warehouse !== '') { $query .= " AND di.warehouse_id = ?"; $binds[] = $warehouse; } $ohrow = sqlQuery($query, $binds); $onhand = intval($ohrow['on_hand']); if (empty($GLOBALS['gbl_min_max_months'])) { if ($onhand <= $min) { return true; } } else { if ($sales != 0) { $stock_months = sprintf('%0.1f', $onhand * ($form_days / 30.41) / $sales); if ($stock_months <= $min) { return true; } } } return false; } // Generate the list of warehouse IDs that the current user is allowed to access. // This is used to build SQL for $uwcond. // function genUserWarehouses($userid = 0) { $list = ''; $res = sqlStatement( "SELECT DISTINCT option_id, option_value FROM list_options WHERE " . "list_id = 'warehouse' AND activity = 1" ); while ($row = sqlFetchArray($res)) { if (isWarehouseAllowed($row['option_value'], $row['option_id'], $userid)) { if ($list != '') { $list .= ', '; } // Using add_escape_custom because this string is for a SQL query. $list .= "'" . add_escape_custom($row['option_id']) . "'"; } } return $list; } // This counts the number of days that have a starting zero inventory for a given product // since a given start date with given restrictions for warehouse or facility. // End date is assumed to be the current date. // // function zeroDays($product_id, $begdate, $warehouse_id = '~', $facility_id = 0) { function zeroDays($product_id, $begdate, $extracond, $extrabind, $min_sale = 1) { $today = date('Y-m-d'); $prodcond = ''; $prodbind = array(); if ($product_id) { $prodcond = "AND di.drug_id = ?"; $prodbind[] = $product_id; } // This will be an array where key is date and value is quantity. // For each date key the value represents net quantity changes for that day. $qtys = array(); // Force it to have entries for the begin and end dates. $qtys[$today] = 0; $qtys[$begdate] = 0; // Get sums of current inventory quantities. $query = "SELECT SUM(di.on_hand) AS on_hand " . "FROM drug_inventory AS di " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "di.destroy_date IS NULL $prodcond $extracond"; $row = sqlQuery($query, array_merge($prodbind, $extrabind)); $current_qoh = $row['on_hand']; // Add sums of destructions done for each date (effectively a type of transaction). $res = sqlStatement( "SELECT di.destroy_date, SUM(di.on_hand) AS on_hand " . "FROM drug_inventory AS di " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "di.destroy_date IS NOT NULL AND di.destroy_date >= ? " . "$prodcond $extracond" . "GROUP BY di.destroy_date ORDER BY di.destroy_date", array_merge(array($begdate), $prodbind, $extrabind) ); while ($row = sqlFetchArray($res)) { $thisdate = substr($row['destroy_date'], 0, 10); if (!isset($qtys[$thisdate])) { $qtys[$thisdate] = 0; } $qtys[$thisdate] += $row['on_hand']; } // Add sums of other transactions for each date. // Note sales are positive and purchases are negative. $res = sqlStatement( "SELECT ds.sale_date, SUM(ds.quantity) AS quantity " . "FROM drug_sales AS ds, drug_inventory AS di " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "ds.sale_date >= ? AND " . "di.inventory_id = ds.inventory_id " . "$prodcond $extracond" . "GROUP BY ds.sale_date ORDER BY ds.sale_date", array_merge(array($begdate), $prodbind, $extrabind) ); while ($row = sqlFetchArray($res)) { $thisdate = $row['sale_date']; if (!isset($qtys[$thisdate])) { $qtys[$thisdate] = 0; } $qtys[$thisdate] += $row['quantity']; } // Subtract sums of transfers out for each date. // Quantity for transfers, like purchases, is usually negative. $res = sqlStatement( "SELECT ds.sale_date, SUM(ds.quantity) AS quantity " . "FROM drug_sales AS ds, drug_inventory AS di " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "ds.sale_date >= ? AND " . "di.inventory_id = ds.xfer_inventory_id " . "$prodcond $extracond" . "GROUP BY ds.sale_date ORDER BY ds.sale_date", array_merge(array($begdate), $prodbind, $extrabind) ); while ($row = sqlFetchArray($res)) { $thisdate = $row['sale_date']; if (!isset($qtys[$thisdate])) { $qtys[$thisdate] = 0; } $qtys[$thisdate] -= $row['quantity']; } // Sort by reverse date. krsort($qtys); $lastdate = ''; $lastqty = $current_qoh; // This will be the count of days that have zero quantity at the start of the day. $zerodays = 0; // Now we traverse the array in descending date order, adding a date's quantity adjustment // to the running total to get the quantity at the beginning of that date. foreach ($qtys as $key => $val) { if ($lastdate && $lastqty < $min_sale) { // The span of days from $key to start of $lastdate has zero quantity. // Add that number of days to $zerodays. $diff = date_diff(date_create($key), date_create($lastdate)); $zerodays += $diff->days; } $lastdate = $key; $lastqty += $val; // giving qoh at the start of $lastdate } // The last array entry hasn't been accounted for yet, so do that. if ($lastqty < $min_sale) { ++$zerodays; } return $zerodays; } function write_report_line(&$row) { global $form_details, $wrl_last_drug_id, $warnings, $encount, $fwcond, $fwbind, $form_days; global $gbl_expired_lot_warning_days, $form_facility, $form_warehouse, $form_action; $emptyvalue = $form_action != 'export' ? ' ' : ''; $drug_id = (int) $row['drug_id']; $on_hand = (int) $row['on_hand']; $warehouse_id = isset($row['warehouse_id']) ? $row['warehouse_id'] : ''; $facility_id = empty($row['option_value']) ? '0' : $row['option_value']; $warnings = ''; // Get sales in the date range for this drug (and facility or warehouse if details). if ($form_details == 1) { // facility details $query = "SELECT " . "SUM(s.quantity) AS sale_quantity " . "FROM drug_sales AS s " . "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "s.drug_id = ? AND " . "lo.option_value IS NOT NULL AND lo.option_value = ? AND " . "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " . "AND s.pid != 0 $fwcond"; $srow = sqlQuery($query, array_merge(array($drug_id, $facility_id), $fwbind)); } else if ($form_details == 2) { // warehouse details $query = "SELECT " . "SUM(s.quantity) AS sale_quantity " . "FROM drug_sales AS s " . "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "s.drug_id = ? AND " . "di.warehouse_id IS NOT NULL AND di.warehouse_id = ? AND " . "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " . "AND s.pid != 0 $fwcond"; $srow = sqlQuery($query, array_merge(array($drug_id, $warehouse_id), $fwbind)); } else { $srow = sqlQuery( "SELECT " . "SUM(s.quantity) AS sale_quantity " . "FROM drug_sales AS s " . "LEFT JOIN drug_inventory AS di ON di.inventory_id = s.inventory_id " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "s.drug_id = ? AND " . "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " . "AND s.pid != 0 $fwcond", array_merge(array($drug_id), $fwbind) ); } $sale_quantity = $srow['sale_quantity']; // Compute the smallest quantity that might be taken from ANY lot for this product // (and facility or warehouse if details) based on the past $form_days days of sales. // If lot combining is allowed this is always 1. $extracond = $fwcond; $extrabind = $fwbind; if ($form_details == 1) { $extracond = "AND lo.option_value IS NOT NULL AND lo.option_value = ?"; $extrabind = array($facility_id); } if ($form_details == 2) { $extracond = "AND di.warehouse_id = ?"; $extrabind = array($warehouse_id); } $min_sale = 1; if (!$row['allow_combining']) { $sminrow = sqlQuery( "SELECT " . "MIN(s.quantity) AS min_sale " . "FROM drug_sales AS s " . "LEFT JOIN drug_inventory AS di ON di.drug_id = s.drug_id " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "s.drug_id = ? AND " . "s.sale_date > DATE_SUB(NOW(), INTERVAL " . escape_limit($form_days) . " DAY) " . "AND s.pid != 0 " . "AND s.quantity > 0 $extracond", array_merge(array($drug_id), $extrabind) ); $min_sale = 0 + $sminrow['min_sale']; } if (!$min_sale) { $min_sale = 1; } // Get number of days with no stock. $today = date('Y-m-d'); $tmp_days = max($form_days - 1, 0); $begdate = date('Y-m-d', strtotime("$today - $tmp_days days")); $zerodays = zeroDays($drug_id, $begdate, $extracond, $extrabind, $min_sale); $months = $form_days / 30.41; $monthly = ($months && $sale_quantity && $form_days > $zerodays) ? sprintf('%0.1f', $sale_quantity / $months * $form_days / ($form_days - $zerodays)) : 0; if ($monthly == 0.0 && $on_hand == 0) { // The row has no QOH and no recent sales, so is deemed uninteresting. // See CV email 2014-06-25. return; } if ($drug_id != $wrl_last_drug_id) { ++$encount; } $bgcolor = "#" . (($encount & 1) ? "ddddff" : "ffdddd"); $stock_months = 0; if ($monthly != 0) { $stock_months = sprintf('%0.1f', $on_hand / $monthly); if ($stock_months < 1.0) { addWarning(xl('QOH is less than monthly usage')); } } // Check for reorder point reached, once per product. if ($drug_id != $wrl_last_drug_id) { if (checkReorder($drug_id, $row['reorder_point'])) { addWarning(xl('Product-level reorder point has been reached')); } } // For warehouse details mode we want the message on the line for this warehouse. // If the warehouse is not shown because it has no QOH and no recent // activity, then this message doesn't matter any more either. if ($form_details == 2) { if (checkReorder($drug_id, $row['pw_min_level'], $warehouse_id)) { addWarning(xl("Reorder point has been reached for warehouse") . " '" . $row['title'] . "'"); } } // Get all lots that we want to issue warnings about. These are lots // expired, soon to expire, or with insufficient quantity for selling. $gbl_expired_lot_warning_days = empty($gbl_expired_lot_warning_days) ? 0 : intval($gbl_expired_lot_warning_days); if ($gbl_expired_lot_warning_days <= 0) { $gbl_expired_lot_warning_days = 30; } $ires = sqlStatement( "SELECT di.* " . "FROM drug_inventory AS di " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE " . "di.drug_id = ? AND " . "di.on_hand > 0 AND " . "di.destroy_date IS NULL AND ( " . "di.on_hand < ? OR " . "di.expiration IS NOT NULL AND di.expiration < DATE_ADD(NOW(), INTERVAL " . escape_limit($gbl_expired_lot_warning_days) . " DAY) " . ") $extracond ORDER BY di.lot_number", array_merge(array($drug_id, $min_sale), $extrabind) ); // Generate warnings associated with individual lots. while ($irow = sqlFetchArray($ires)) { $lotno = $irow['lot_number']; if ($irow['on_hand'] < $min_sale) { addWarning(xl('Lot') . " '$lotno' " . xl('quantity seems unusable')); } if (!empty($irow['expiration'])) { $expdays = (int) ((strtotime($irow['expiration']) - time()) / (60 * 60 * 24)); if ($expdays <= 0) { addWarning(xl('Lot') . " '$lotno' " . xl('has expired')); } else if ($expdays <= $gbl_expired_lot_warning_days) { addWarning(xl('Lot') . " '$lotno' " . xl('expires in') . " $expdays " . xl('days')); } } } // Per CV 2014-06-20: // Reorder Quantity should be calculated only if Stock Months is less than Months Min. // If Stock Months is [not] less than Months Min, Reorder Quantity should be zero. // The calculation should be: (Min Months minus Stock Months) times Avg Monthly. // Reorder Quantity should be rounded up to a whole number. $reorder_qty = 0; if ($monthly > 0.00) { // Note if facility details, this the sum of min levels for the facility's warehouses. $min_months = 0 + ($form_details ? $row['pw_min_level'] : $row['reorder_point']); // If min is not specified as months then compute it that way. if (empty($GLOBALS['gbl_min_max_months'])) { $min_months /= $monthly; } if ($stock_months < $min_months) { $reorder_qty = ceil(($min_months - $stock_months) * $monthly); } } if (empty($monthly)) { $monthly = $emptyvalue; } if (empty($stock_months)) { $stock_months = $emptyvalue; } $relcodes = ''; $tmp = explode(';', $row['related_code']); foreach ($tmp as $codestring) { if ($codestring === '') { continue; } list($codetype, $code) = explode(':', $codestring); // For IPPF just the IPPFCM codes are wanted. if ($GLOBALS['ippf_specific'] && $codetype !== 'IPPFCM') { continue; } if ($relcodes) { $relcodes .= ';'; } $relcodes .= $codestring; } $drug_form = empty($row['form']) ? '' : generate_display_field( array( 'data_type' => '1', 'list_id' => 'drug_form' ), $row['form'] ); if ($form_action == 'export') { echo csvEscape($row['name']) . ','; echo csvEscape($relcodes) . ','; echo csvEscape($row['ndc_number']) . ','; echo csvEscape($row['active'] ? xl('Yes') : xl('No')) . ','; echo csvEscape($row['consumable'] ? xl('Yes') : xl('No')) . ','; echo csvEscape($drug_form) . ','; if ($form_details) { echo csvEscape($row['facname']) . ','; if ($form_details == 2) { // warehouse details { echo csvEscape($row['title']) . ','; } echo csvEscape($row['pw_min_level']) . ','; echo csvEscape($row['pw_max_level']) . ','; } else { echo csvEscape($row['reorder_point']) . ','; echo csvEscape($row['max_level']) . ','; } echo csvEscape($row['on_hand']) . ','; echo csvEscape($zerodays) . ','; echo csvEscape($monthly) . ','; echo csvEscape($stock_months) . ','; echo csvEscape($reorder_qty) . ','; echo csvEscape($warnings) . ''; echo "\n"; } else { // end exporting echo " \n"; if ($drug_id == $wrl_last_drug_id) { echo " \n"; } else { echo " " . text($row['name']) . "\n"; echo " " . text($relcodes) . "\n"; echo " " . text($row['ndc_number']) . "\n"; echo " " . ($row['active'] ? xlt('Yes') : xlt('No')) . "\n"; echo " " . ($row['consumable'] ? xlt('Yes') : xlt('No')) . "\n"; echo " " . text($drug_form) . "\n"; } if ($form_details) { echo " " . text($row['facname']) . "\n"; if ($form_details == 2) { // warehouse details { echo " " . text($row['title']) . "\n"; } echo " " . text($row['pw_min_level']) . "\n"; echo " " . text($row['pw_max_level']) . "\n"; } else { echo " " . text($row['reorder_point']) . "\n"; echo " " . text($row['max_level']) . "\n"; } echo " " . text($row['on_hand']) . "\n"; echo " " . text($zerodays) . "\n"; // Do not text() these next 2 lines. echo " " . $monthly . "\n"; echo " " . $stock_months . "\n"; echo " " . text($reorder_qty) . "\n"; // Do not text() the following line. echo " " . $warnings . "\n"; echo " \n"; } // end not exporting $wrl_last_drug_id = $drug_id; } if (!empty($_POST['form_days'])) { $form_days = $_POST['form_days'] + 0; } else { $form_days = sprintf('%d', (strtotime(date('Y-m-d')) - strtotime(date('Y-01-01'))) / (60 * 60 * 24) + 1); } // this is "" or "submit". $form_action = $_POST['form_action'] ?? ''; if (!empty($_POST['form_days'])) { $form_days = $_POST['form_days'] + 0; } else { $form_days = sprintf('%d', (strtotime(date('Y-m-d')) - strtotime(date('Y-01-01'))) / (60 * 60 * 24) + 1); } // this is "" or "submit" or "export". $form_action = empty($_POST['form_action']) ? '' : $_POST['form_action']; $form_inactive = empty($_REQUEST['form_inactive']) ? 0 : 1; $form_details = empty($_REQUEST['form_details']) ? 0 : intval($_REQUEST['form_details']); $form_facility = 0 + empty($_REQUEST['form_facility']) ? 0 : $_REQUEST['form_facility']; $form_consumable = isset($_REQUEST['form_consumable']) ? intval($_REQUEST['form_consumable']) : 0; $form_orderby = $_REQUEST['form_orderby'] ?? 'name'; $orderby = $ORDERHASH[$form_orderby]; // Incoming form_warehouse, if not empty is in the form "warehouse/facility". // The facility part is an attribute used by JavaScript logic. $form_warehouse = $_REQUEST['form_warehouse'] ?? ''; $tmp = explode('/', $form_warehouse); $form_warehouse = $tmp[0]; $mmtype = empty($GLOBALS['gbl_min_max_months']) ? xl('Units') : xl('Months'); // Compute WHERE condition for filtering on facility/warehouse. $fwcond = ''; $fwbind = array(); if ($form_facility) { $fwcond .= " AND lo.option_value IS NOT NULL AND lo.option_value = ?"; $fwbind[] = $form_facility; } if ($form_warehouse) { $fwcond .= " AND di.warehouse_id IS NOT NULL AND di.warehouse_id = ?"; $fwbind[] = $form_warehouse; } if ($is_user_restricted) { $fwcond .= "AND di.warehouse_id IS NOT NULL AND di.warehouse_id IN (" . genUserWarehouses() . ")"; } // Compute WHERE condition for filtering on activity and consumability (drugs table). $actcond = ''; if (!$form_inactive) { $actcond .= " AND d.active = 1"; } if ($form_consumable) { if ($form_consumable == 1) { $actcond .= " AND d.consumable = '1'"; } else { $actcond .= " AND d.consumable != '1'"; } } if ($form_details == 1) { // Query for the main loop if facility details are wanted. $query = "SELECT d.*, SUM(di.on_hand) AS on_hand, lo.option_value, fac.name AS facname, " . "SUM(pw.pw_min_level) AS pw_min_level, SUM(pw.pw_max_level) AS pw_max_level " . "FROM drugs AS d " . "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " . "AND di.on_hand != 0 AND di.destroy_date IS NULL " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "LEFT JOIN facility AS fac ON fac.id = lo.option_value " . "LEFT JOIN product_warehouse AS pw ON pw.pw_drug_id = d.drug_id AND " . "pw.pw_warehouse = di.warehouse_id " . "WHERE 1 = 1 $fwcond $actcond " . "GROUP BY d.name, d.drug_id, lo.option_value ORDER BY $orderby, lo.option_value"; } else if ($form_details == 2) { // Query for the main loop if warehouse/lot details are wanted. $query = "SELECT d.*, di.on_hand, di.inventory_id, di.lot_number, " . "di.expiration, di.warehouse_id, lo.title, fac.name AS facname, " . "pw.pw_min_level, pw.pw_max_level " . "FROM drugs AS d " . "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " . "AND di.on_hand != 0 AND di.destroy_date IS NULL " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "LEFT JOIN facility AS fac ON fac.id = lo.option_value " . "LEFT JOIN product_warehouse AS pw ON pw.pw_drug_id = d.drug_id AND " . "pw.pw_warehouse = di.warehouse_id " . "WHERE 1 = 1 $fwcond $actcond " . "ORDER BY $orderby, lo.title, di.warehouse_id, di.lot_number, di.inventory_id"; } else { // Query for the main loop if summary report. $query = "SELECT d.*, SUM(di.on_hand) AS on_hand " . "FROM drugs AS d " . "LEFT JOIN drug_inventory AS di ON di.drug_id = d.drug_id " . "AND di.on_hand != 0 AND di.destroy_date IS NULL " . "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " . "lo.option_id = di.warehouse_id AND lo.activity = 1 " . "WHERE 1 = 1 $fwcond $actcond " . "GROUP BY $orderby ORDER BY $orderby"; } // $res = sqlStatement($query); $res = sqlStatement($query, $fwbind); if ($form_action == 'export') { header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Type: application/force-download; charset=utf-8"); header("Content-Disposition: attachment; filename=inventory_list.csv"); header("Content-Description: File Transfer"); // Prepend a BOM (Byte Order Mark) header to mark the data as UTF-8. This is // said to work for Excel 2007 pl3 and up and perhaps also Excel 2003 pl3. See: // http://stackoverflow.com/questions/155097/microsoft-excel-mangles-diacritics-in-csv-files // http://crashcoursing.blogspot.com/2011/05/exporting-csv-with-special-characters.html echo "\xEF\xBB\xBF"; // CSV headers: echo csvEscape(xl('Name')) . ','; echo csvEscape(xl('Relates To')) . ','; echo csvEscape(xl('NDC')) . ','; echo csvEscape(xl('Active')) . ','; echo csvEscape(xl('Consumable')) . ','; echo csvEscape(xl('Form')) . ','; if ($form_details) { echo csvEscape(xl('Facility')) . ','; if ($form_details == 2) { echo csvEscape(xl('Warehouse')) . ','; } echo csvEscape($mmtype . xl('Min')) . ','; echo csvEscape($mmtype . xl('Max')) . ','; } echo csvEscape(xl('QOH')) . ','; echo csvEscape(xl('Zero Stock Days')) . ','; echo csvEscape(xl('Avg Monthly')) . ','; echo csvEscape(xl('Stock Months')) . ','; echo csvEscape(xl('Reorder Qty')) . ','; echo csvEscape(xl('Warnings')) . ''; echo "\n"; } else { // not exporting ?> <?php echo xlt('Inventory List'); ?>

\n"; echo "

0, 'warehouse_id' => ''); while ($row = sqlFetchArray($res)) { $drug_id = (int) $row['drug_id']; if ($form_details == 2) { if ($drug_id != $last_drug_id || $row['warehouse_id'] != $warehouse_row['warehouse_id']) { if (!empty($warehouse_row['drug_id'])) { write_report_line($warehouse_row); } $warehouse_row = $row; $warehouse_row['on_hand'] = 0; } $warehouse_row['on_hand'] += $row['on_hand']; } else { write_report_line($row); } $last_drug_id = $drug_id; } if ($form_details == 2) { if (!empty($warehouse_row['drug_id'])) { write_report_line($warehouse_row); } } if ($form_action != 'export') { echo " \n"; echo "

>			>	>

\n"; echo "

\n"; } } // end if submit if ($form_action != 'export') { ?>