interface/reports/inventory_activity.php

   1 <?php
   2
   3 /**
   4  * Report columns:
   5  * Product Name (blank where repeated)
   6  * Warehouse Name (blank where repeated) or Total for Product
   7  * Starting Inventory (detail lines: date)
   8  * Ending Inventory   (detail lines: invoice ID)
   9  * Sales
  10  * Distributions
  11  * Purchases
  12  * Transfers
  13  *
  14  * @package   OpenEMR
  15  * @link      http://www.open-emr.org
  16  * @author    Rod Roark <rod@sunsetsystems.com>
  17  * @author    Brady Miller <brady.g.miller@gmail.com>
  18  * @copyright Copyright (c) 2010-2016 Rod Roark <rod@sunsetsystems.com>
  19  * @copyright Copyright (c) 2017-2019 Brady Miller <brady.g.miller@gmail.com>
  20  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  21  */
  22
  23 require_once("../globals.php");
  24 require_once("$srcdir/patient.inc.php");
  25
  26 use OpenEMR\Common\Acl\AclMain;
  27 use OpenEMR\Common\Csrf\CsrfUtils;
  28 use OpenEMR\Common\Twig\TwigContainer;
  29 use OpenEMR\Core\Header;
  30
  31 if (!empty($_POST)) {
  32     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  33         CsrfUtils::csrfNotVerified();
  34     }
  35 }
  36
  37 // Specify if product or warehouse is the first column.
  38 $product_first = (!empty($_POST['form_by']) && $_POST['form_by'] == 'w') ? 0 : 1;
  39
  40 $last_warehouse_id = '~';
  41 $last_product_id = 0;
  42
  43 // Get ending inventory for the report's end date.
  44 // Optionally restricts by product ID and/or warehouse ID.
  45 function getEndInventory($product_id = 0, $warehouse_id = '~')
  46 {
  47     global $form_from_date, $form_to_date, $form_product;
  48
  49     $whidcond = '';
  50     if ($warehouse_id !== '~') {
  51         $whidcond = $warehouse_id === '' ?
  52         "AND ( di.warehouse_id IS NULL OR di.warehouse_id = '' )" :
  53         "AND di.warehouse_id = '" . add_escape_custom($warehouse_id) . "'";
  54     }
  55
  56     $prodcond = '';
  57     if ($form_product) {
  58         $product_id = $form_product;
  59     }
  60
  61     if ($product_id) {
  62         $prodcond = "AND di.drug_id = '" . add_escape_custom($product_id) . "'";
  63     }
  64
  65   // Get sum of current inventory quantities + destructions done after the
  66   // report end date (which is effectively a type of transaction).
  67     $eirow = sqlQuery("SELECT sum(di.on_hand) AS on_hand " .
  68     "FROM drug_inventory AS di WHERE " .
  69     "( di.destroy_date IS NULL OR di.destroy_date > ? ) " .
  70     "$prodcond $whidcond", array($form_to_date));
  71
  72   // Get sum of sales/adjustments/purchases after the report end date.
  73     $sarow = sqlQuery("SELECT sum(ds.quantity) AS quantity " .
  74     "FROM drug_sales AS ds, drug_inventory AS di WHERE " .
  75     "ds.sale_date > ? AND " .
  76     "di.inventory_id = ds.inventory_id " .
  77     "$prodcond $whidcond", array($form_to_date));
  78
  79   // Get sum of transfers out after the report end date.
  80     $xfrow = sqlQuery("SELECT sum(ds.quantity) AS quantity " .
  81     "FROM drug_sales AS ds, drug_inventory AS di WHERE " .
  82     "ds.sale_date > ? AND " .
  83     "di.inventory_id = ds.xfer_inventory_id " .
  84     "$prodcond $whidcond", array($form_to_date));
  85
  86     return $eirow['on_hand'] + $sarow['quantity'] - $xfrow['quantity'];
  87 }
  88
  89 function thisLineItem(
  90     $product_id,
  91     $warehouse_id,
  92     $patient_id,
  93     $encounter_id,
  94     $rowprod,
  95     $rowwh,
  96     $transdate,
  97     $qtys,
  98     $irnumber = ''
  99 ) {
 100
 101     global $warehouse, $product, $secqtys, $priqtys, $grandqtys;
 102     global $whleft, $prodleft; // left 2 columns, blank where repeated
 103     global $last_warehouse_id, $last_product_id, $product_first;
 104     global $form_action;
 105
 106     $invnumber = empty($irnumber) ? ($patient_id ? "$patient_id.$encounter_id" : "") : $irnumber;
 107
 108   // Product name for this detail line item.
 109     if (empty($rowprod)) {
 110         $rowprod = 'Unnamed Product';
 111     }
 112
 113   // Warehouse name for this line item.
 114     if (empty($rowwh)) {
 115         $rowwh = 'None';
 116     }
 117
 118   // If new warehouse or product...
 119     if ($warehouse_id != $last_warehouse_id || $product_id != $last_product_id) {
 120         // If there was anything to total...
 121         if (($product_first && $last_warehouse_id != '~') || (!$product_first && $last_product_id)) {
 122             $secei = getEndInventory($last_product_id, $last_warehouse_id);
 123
 124             // Print second-column totals.
 125             if ($form_action == 'export') {
 126                 // Export:
 127                 if (! $_POST['form_details']) {
 128                     if ($product_first) {
 129                         echo csvEscape($product);
 130                         echo ',' . csvEscape($warehouse);
 131                     } else {
 132                         echo csvEscape($warehouse);
 133                         echo ',' . csvEscape($product);
 134                     }
 135
 136                     echo ',' . csvEscape($secei - $secqtys[0] - $secqtys[1] - $secqtys[2] - $secqtys[3] - $secqtys[4]); // start inventory
 137                     echo ',' . csvEscape($secqtys[0]); // sales
 138                     echo ',' . csvEscape($secqtys[1]); // distributions
 139                     echo ',' . csvEscape($secqtys[2]); // purchases
 140                     echo ',' . csvEscape($secqtys[3]); // transfers
 141                     echo ',' . csvEscape($secqtys[4]); // adjustments
 142                     echo ',' . csvEscape($secei); // end inventory
 143                     echo "\n";
 144                 }
 145             } else {
 146                 // Not export:
 147                 ?>
 148                 <tr bgcolor="#ddddff">
 149                 <?php if ($product_first) { ?>
 150                     <td class="detail">
 151                         <?php echo text($prodleft);
 152                         $prodleft = " "; ?>
 153                     </td>
 154                     <td class="detail" colspan='3'>
 155                         <?php
 156                         if ($_POST['form_details']) {
 157                             echo xlt('Total for') . ' ';
 158                         }
 159                         echo text($warehouse); ?>
 160                     </td>
 161                 <?php } else { ?>
 162                     <td class="detail">
 163                         <?php echo text($whleft);
 164                         $whleft = " "; ?>
 165                     </td>
 166                     <td class="detail" colspan='3'>
 167                         <?php
 168                         if ($_POST['form_details']) {
 169                             echo xlt('Total for') . ' ';
 170                         }
 171                         echo text($product); ?>
 172                     </td>
 173                 <?php } ?>
 174                 <td class="dehead" align="right">
 175                     <?php echo text($secei - $secqtys[0] - $secqtys[1] - $secqtys[2] - $secqtys[3] - $secqtys[4]); ?>
 176                 </td>
 177                 <td class="dehead" align="right">
 178                     <?php echo text($secqtys[0]); ?>
 179                 </td>
 180                 <td class="dehead" align="right">
 181                     <?php echo text($secqtys[1]); ?>
 182                 </td>
 183                 <td class="dehead" align="right">
 184                     <?php echo text($secqtys[2]); ?>
 185                 </td>
 186                 <td class="dehead" align="right">
 187                     <?php echo text($secqtys[3]); ?>
 188                 </td>
 189                 <td class="dehead" align="right">
 190                     <?php echo text($secqtys[4]); ?>
 191                 </td>
 192                 <td class="dehead" align="right">
 193                     <?php echo text($secei); ?>
 194                 </td>
 195                 </tr>
 196                 <?php
 197             } // End not csv export
 198         }
 199
 200         $secqtys = array(0, 0, 0, 0, 0);
 201         if ($product_first) {
 202             $whleft = $warehouse = $rowwh;
 203             $last_warehouse_id = $warehouse_id;
 204         } else {
 205             $prodleft = $product = $rowprod;
 206             $last_product_id = $product_id;
 207         }
 208     }
 209
 210     // If first column is changing, time for its totals.
 211     if (
 212         ($product_first && $product_id != $last_product_id) ||
 213         (!$product_first && $warehouse_id != $last_warehouse_id)
 214     ) {
 215         if (
 216             ($product_first && $last_product_id) ||
 217             (!$product_first && $last_warehouse_id != '~')
 218         ) {
 219             $priei = $product_first ? getEndInventory($last_product_id) :
 220               getEndInventory(0, $last_warehouse_id);
 221             // Print first column total.
 222             if ($form_action != 'export') {
 223                 ?>
 224
 225                 <tr bgcolor="#ffdddd">
 226                 <td class="detail">
 227                 &nbsp;
 228                 </td>
 229                 <td class="detail" colspan="3">
 230                 <?php echo xlt('Total for') . ' ';
 231                 echo text($product_first ? $product : $warehouse); ?>
 232                 </td>
 233                 <td class="dehead" align="right">
 234                 <?php echo text($priei - $priqtys[0] - $priqtys[1] - $priqtys[2] - $priqtys[3] - $priqtys[4]); ?>
 235                 </td>
 236                 <td class="dehead" align="right">
 237                 <?php echo text($priqtys[0]); ?>
 238                 </td>
 239                 <td class="dehead" align="right">
 240                 <?php echo text($priqtys[1]); ?>
 241                 </td>
 242                 <td class="dehead" align="right">
 243                 <?php echo text($priqtys[2]); ?>
 244                 </td>
 245                 <td class="dehead" align="right">
 246                 <?php echo text($priqtys[3]); ?>
 247                 </td>
 248                 <td class="dehead" align="right">
 249                 <?php echo text($priqtys[4]); ?>
 250                 </td>
 251                 <td class="dehead" align="right">
 252                 <?php echo text($priei); ?>
 253                 </td>
 254                 </tr>
 255                 <?php
 256             } // End not csv export
 257         }
 258
 259         $priqtys = array(0, 0, 0, 0, 0);
 260         if ($product_first) {
 261             $prodleft = $product = $rowprod;
 262             $last_product_id = $product_id;
 263         } else {
 264             $whleft = $warehouse = $rowwh;
 265             $last_warehouse_id = $warehouse_id;
 266         }
 267     }
 268
 269     // Detail line.
 270     if ($_POST['form_details'] && $product_id && ($qtys[0] + $qtys[1] + $qtys[2] + $qtys[3] + $qtys[4])) {
 271         if ($form_action == 'export') {
 272             if ($product_first) {
 273                 echo csvEscape($product);
 274                 echo ',' . csvEscape($warehouse);
 275             } else {
 276                 echo csvEscape($warehouse);
 277                 echo ',' . csvEscape($product);
 278             }
 279
 280             echo ',' . csvEscape(oeFormatShortDate($transdate));
 281             echo ',' . csvEscape($invnumber);
 282             echo ',' . csvEscape($qtys[0]); // sales
 283             echo ',' . csvEscape($qtys[1]); // distributions
 284             echo ',' . csvEscape($qtys[2]); // purchases
 285             echo ',' . csvEscape($qtys[3]); // transfers
 286             echo ',' . csvEscape($qtys[4]); // adjustments
 287             echo "\n";
 288         } else {
 289             ?>
 290             <tr>
 291             <?php if ($product_first) { ?>
 292                 <td class="detail">
 293                     <?php echo text($prodleft);
 294                     $prodleft = " "; ?>
 295                 </td>
 296                 <td class="detail">
 297                     <?php echo text($whleft);
 298                     $whleft = " "; ?>
 299                 </td>
 300             <?php } else { ?>
 301                 <td class="detail">
 302                     <?php echo text($whleft);
 303                     $whleft = " "; ?>
 304                 </td>
 305                 <td class="detail">
 306                     <?php echo text($prodleft);
 307                     $prodleft = " "; ?>
 308                 </td>
 309             <?php } ?>
 310             <td class="dehead">
 311                 <?php echo text(oeFormatShortDate($transdate)); ?>
 312             </td>
 313             <td class="detail">
 314                 <?php echo text($invnumber); ?>
 315             </td>
 316             <td class="detail">
 317                 &nbsp;
 318             </td>
 319             <td class="dehead" align="right">
 320                 <?php echo text($qtys[0]); ?>
 321             </td>
 322             <td class="dehead" align="right">
 323                 <?php echo text($qtys[1]); ?>
 324             </td>
 325             <td class="dehead" align="right">
 326                 <?php echo text($qtys[2]); ?>
 327             </td>
 328             <td class="dehead" align="right">
 329                 <?php echo text($qtys[3]); ?>
 330             </td>
 331             <td class="dehead" align="right">
 332                 <?php echo text($qtys[4]); ?>
 333             </td>
 334             <td class="detail">
 335                 &nbsp;
 336             </td>
 337             </tr>
 338             <?php
 339         } // End not csv export
 340     } // end details
 341     for ($i = 0; $i < 5; ++$i) {
 342         $secqtys[$i]   += $qtys[$i];
 343         $priqtys[$i]   += $qtys[$i];
 344         $grandqtys[$i] += $qtys[$i];
 345     }
 346 } // end function
 347
 348 if (! AclMain::aclCheckCore('acct', 'rep')) {
 349     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Inventory Activity")]);
 350     exit;
 351 }
 352
 353 // this is "" or "submit" or "export".
 354 $form_action = $_POST['form_action'];
 355
 356 $form_from_date = (isset($_POST['form_from_date'])) ? DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
 357 $form_to_date   = (isset($_POST['form_to_date'])) ? DateToYYYYMMDD($_POST['form_to_date']) : date('Y-m-d');
 358 $form_product  = $_POST['form_product'];
 359
 360 if ($form_action == 'export') {
 361     header("Pragma: public");
 362     header("Expires: 0");
 363     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 364     header("Content-Type: application/force-download");
 365     header("Content-Disposition: attachment; filename=inventory_activity.csv");
 366     header("Content-Description: File Transfer");
 367   // CSV headers:
 368     if ($product_first) {
 369         echo csvEscape(xl('Product')) . ',';
 370         echo csvEscape(xl('Warehouse')) . ',';
 371     } else {
 372         echo csvEscape(xl('Warehouse')) . ',';
 373         echo csvEscape(xl('Product')) . ',';
 374     }
 375
 376     if ($_POST['form_details']) {
 377         echo csvEscape(xl('Date')) . ',';
 378         echo csvEscape(xl('Invoice')) . ',';
 379         echo csvEscape(xl('Sales')) . ',';
 380         echo csvEscape(xl('Distributions')) . ',';
 381         echo csvEscape(xl('Purchases')) . ',';
 382         echo csvEscape(xl('Transfers')) . ',';
 383         echo csvEscape(xl('Adjustments')) . "\n";
 384     } else {
 385         echo csvEscape(xl('Start')) . ',';
 386         echo csvEscape(xl('Sales')) . ',';
 387         echo csvEscape(xl('Distributions')) . ',';
 388         echo csvEscape(xl('Purchases')) . ',';
 389         echo csvEscape(xl('Transfers')) . ',';
 390         echo csvEscape(xl('Adjustments')) . ',';
 391         echo csvEscape(xl('End')) . "\n";
 392     }
 393 } else { // end export
 394     ?>
 395 <html>
 396 <head>
 397 <title><?php echo xlt('Inventory Activity'); ?></title>
 398
 399     <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
 400
 401 <style>
 402  /* specifically include & exclude from printing */
 403  @media print {
 404   #report_parameters {visibility: hidden; display: none;}
 405   #report_parameters_daterange {visibility: visible; display: inline;}
 406   #report_results {margin-top: 30px;}
 407  }
 408  /* specifically exclude some from the screen */
 409  @media screen {
 410   #report_parameters_daterange {visibility: hidden; display: none;}
 411  }
 412  body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
 413  .dehead    { color:var(--black); font-family:sans-serif; font-size:10pt; font-weight:bold }
 414  .detail    { color:var(--black); font-family:sans-serif; font-size:10pt; font-weight:normal }
 415
 416 table.mymaintable, table.mymaintable td, table.mymaintable th {
 417  border: 1px solid #aaaaaa;
 418  border-collapse: collapse;
 419 }
 420 table.mymaintable td, table.mymaintable th {
 421  padding: 1pt 4pt 1pt 4pt;
 422 }
 423 </style>
 424
 425 <script>
 426
 427     $(function () {
 428         oeFixedHeaderSetup(document.getElementById('mymaintable'));
 429         var win = top.printLogSetup ? top : opener.top;
 430         win.printLogSetup(document.getElementById('printbutton'));
 431
 432         $('.datepicker').datetimepicker({
 433             <?php $datetimepicker_timepicker = false; ?>
 434             <?php $datetimepicker_showseconds = false; ?>
 435             <?php $datetimepicker_formatInput = true; ?>
 436             <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
 437             <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
 438         });
 439     });
 440
 441     function mysubmit(action) {
 442         var f = document.forms[0];
 443         f.form_action.value = action;
 444         top.restoreSession();
 445         f.submit();
 446     }
 447
 448 </script>
 449
 450 </head>
 451
 452 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
 453
 454 <center>
 455
 456 <h2><?php echo xlt('Inventory Activity'); ?></h2>
 457
 458 <form method='post' action='inventory_activity.php?product=<?php echo attr_url($product_first); ?>' onsubmit='return top.restoreSession()'>
 459 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 460
 461 <div id="report_parameters">
 462 <!-- form_action is set to "submit" or "export" at form submit time -->
 463 <input type='hidden' name='form_action' value='' />
 464 <table>
 465  <tr>
 466   <td width='50%'>
 467    <table class='text'>
 468     <tr>
 469      <td class='label_custom'>
 470         <?php echo xlt('By'); ?>:
 471      </td>
 472      <td nowrap>
 473       <select name='form_by'>
 474        <option value='p'><?php echo xlt('Product'); ?></option>
 475        <option value='w'<?php echo (!$product_first) ? ' selected' : ''; ?>><?php echo xlt('Warehouse'); ?></option>
 476       </select>
 477      </td>
 478      <td class='label_custom'>
 479         <?php echo xlt('From'); ?>:
 480      </td>
 481      <td nowrap>
 482       <input type='text' class='datepicker' name='form_from_date' id="form_from_date" size='10'
 483        value='<?php echo attr(oeFormatShortDate($form_from_date)); ?>'>
 484      </td>
 485      <td class='label_custom'>
 486         <?php echo xlt('To{{Range}}'); ?>:
 487      </td>
 488      <td nowrap>
 489       <input type='text' class='datepicker' name='form_to_date' id="form_to_date" size='10'
 490        value='<?php echo attr(oeFormatShortDate($form_to_date)); ?>'>
 491      </td>
 492     </tr>
 493     <tr>
 494      <td class='label_custom'>
 495         <?php echo xlt('For'); ?>:
 496      </td>
 497      <td nowrap>
 498     <?php
 499 // Build a drop-down list of products.
 500 //
 501     $query = "SELECT drug_id, name FROM drugs ORDER BY name, drug_id";
 502     $pres = sqlStatement($query);
 503     echo "      <select name='form_product'>\n";
 504     echo "       <option value=''>-- " . xlt('All Products') . " --\n";
 505     while ($prow = sqlFetchArray($pres)) {
 506         $drug_id = $prow['drug_id'];
 507         echo "       <option value='" . attr($drug_id) . "'";
 508         if ($drug_id == $form_product) {
 509             echo " selected";
 510         }
 511
 512         echo ">" . text($prow['name']) . "\n";
 513     }
 514
 515     echo "      </select>\n";
 516     ?>
 517      </td>
 518      <td class='label_custom'>
 519         <?php echo xlt('Details'); ?>:
 520      </td>
 521      <td colspan='3' nowrap>
 522       <input type='checkbox' name='form_details' value='1'<?php echo ($_POST['form_details']) ? " checked" : "";?> />
 523      </td>
 524     </tr>
 525    </table>
 526   </td>
 527   <td align='left' valign='middle'>
 528    <table class='w-100 h-100' style='border-left:1px solid;'>
 529     <tr>
 530      <td valign='middle'>
 531       <a href='#' class='btn btn-primary' onclick='mysubmit("submit")' style='margin-left:1em'>
 532        <span><?php echo xlt('Submit'); ?></span>
 533       </a>
 534     <?php if ($form_action) { ?>
 535       <a href='#' class='btn btn-primary' id='printbutton' style='margin-left:1em'>
 536        <span><?php echo xlt('Print'); ?></span>
 537       </a>
 538       <a href='#' class='btn btn-primary' onclick='mysubmit("export")' style='margin-left:1em'>
 539        <span><?php echo xlt('CSV Export'); ?></span>
 540       </a>
 541 <?php } ?>
 542      </td>
 543     </tr>
 544    </table>
 545   </td>
 546  </tr>
 547 </table>
 548 </div>
 549
 550     <?php if ($form_action) { // if submit (already not export here) ?>
 551 <div id="report_results">
 552 <table width='98%' id='mymaintable' class='mymaintable'>
 553  <thead>
 554  <tr bgcolor="#dddddd">
 555   <td class="dehead">
 556         <?php echo text($product_first ? xl('Product') : xl('Warehouse')); ?>
 557   </td>
 558         <?php if ($_POST['form_details']) { ?>
 559   <td class="dehead">
 560             <?php echo text($product_first ? xl('Warehouse') : xl('Product')); ?>
 561   </td>
 562   <td class="dehead">
 563             <?php echo xlt('Date'); ?>
 564   </td>
 565   <td class="dehead">
 566             <?php echo xlt('Invoice'); ?>
 567   </td>
 568 <?php } else { ?>
 569   <td class="dehead" colspan="3">
 570             <?php echo text($product_first ? xl('Warehouse') : xl('Product')); ?>
 571   </td>
 572 <?php } ?>
 573   <td class="dehead" align="right" width="8%">
 574         <?php echo xlt('Start'); ?>
 575   </td>
 576   <td class="dehead" align="right" width="8%">
 577         <?php echo xlt('Sales'); ?>
 578   </td>
 579   <td class="dehead" align="right" width="8%">
 580         <?php echo xlt('Distributions'); ?>
 581   </td>
 582   <td class="dehead" align="right" width="8%">
 583         <?php echo xlt('Purchases'); ?>
 584   </td>
 585   <td class="dehead" align="right" width="8%">
 586         <?php echo xlt('Transfers'); ?>
 587   </td>
 588   <td class="dehead" align="right" width="8%">
 589         <?php echo xlt('Adjustments'); ?>
 590   </td>
 591   <td class="dehead" align="right" width="8%">
 592         <?php echo xlt('End'); ?>
 593   </td>
 594  </tr>
 595  </thead>
 596  <tbody>
 597         <?php
 598     } // end if submit
 599 } // end not export
 600
 601 if ($form_action) { // if submit or export
 602     $from_date = $form_from_date;
 603     $to_date   = $form_to_date;
 604
 605     $product   = "";
 606     $prodleft  = "";
 607     $warehouse = "";
 608     $whleft    = "";
 609     $grandqtys = array(0, 0, 0, 0, 0);
 610     $priqtys   = array(0, 0, 0, 0, 0);
 611     $secqtys   = array(0, 0, 0, 0, 0);
 612     $last_inventory_id = 0;
 613
 614     $sqlBindArray = array();
 615
 616     $query = "SELECT s.sale_id, s.sale_date, s.quantity, s.fee, s.pid, s.encounter, " .
 617     "s.xfer_inventory_id, s.distributor_id, d.name, lo.title, " .
 618     "di.drug_id, di.warehouse_id, di.inventory_id, di.destroy_date, di.on_hand, " .
 619     "fe.invoice_refno " .
 620     "FROM drug_inventory AS di " .
 621     "JOIN drugs AS d ON d.drug_id = di.drug_id " .
 622     "LEFT JOIN drug_sales AS s ON " .
 623     "s.sale_date >= ? AND s.sale_date <= ? AND " .
 624     "s.drug_id = di.drug_id AND " .
 625     "( s.inventory_id = di.inventory_id OR s.xfer_inventory_id = di.inventory_id ) " .
 626     "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 627     "lo.option_id = di.warehouse_id AND lo.activity = 1 " .
 628     "LEFT JOIN form_encounter AS fe ON fe.pid = s.pid AND fe.encounter = s.encounter " .
 629     "WHERE ( di.destroy_date IS NULL OR di.destroy_date >= ? ) AND " .
 630     "( di.on_hand != 0 OR s.sale_id IS NOT NULL )";
 631
 632     array_push($sqlBindArray, $from_date, $to_date, $form_from_date);
 633
 634     // If a product was specified.
 635     if ($form_product) {
 636         $query .= " AND di.drug_id = ?";
 637         array_push($sqlBindArray, $form_product);
 638     }
 639
 640     if ($product_first) {
 641         $query .= " ORDER BY d.name, d.drug_id, lo.title, di.warehouse_id, " .
 642         "di.inventory_id, s.sale_date, s.sale_id";
 643     } else {
 644         $query .= " ORDER BY lo.title, di.warehouse_id, d.name, d.drug_id, " .
 645         "di.inventory_id, s.sale_date, s.sale_id";
 646     }
 647
 648     $res = sqlStatement($query, $sqlBindArray);
 649     while ($row = sqlFetchArray($res)) {
 650         // If new lot and it was destroyed during the reporting period,
 651         // generate a pseudo-adjustment for that.
 652         if ($row['inventory_id'] != $last_inventory_id) {
 653             $last_inventory_id = $row['inventory_id'];
 654             if (
 655                 !empty($row['destroy_date']) && $row['on_hand'] != 0
 656                 && $row['destroy_date'] <= $form_to_date
 657             ) {
 658                 thisLineItem(
 659                     $row['drug_id'],
 660                     $row['warehouse_id'],
 661                     0,
 662                     0,
 663                     $row['name'],
 664                     $row['title'],
 665                     $row['destroy_date'],
 666                     array(0, 0, 0, 0, 0 - $row['on_hand']),
 667                     xl('Destroyed')
 668                 );
 669             }
 670         }
 671
 672         $qtys = array(0, 0, 0, 0, 0);
 673         if ($row['sale_id']) {
 674             if ($row['xfer_inventory_id']) {
 675                 // A transfer sale item will appear twice, once with each lot.
 676                 if ($row['inventory_id'] == $row['xfer_inventory_id']) {
 677                     $qtys[3] = $row['quantity'];
 678                 } else {
 679                     $qtys[3] = 0 - $row['quantity'];
 680                 }
 681             } elseif ($row['pid']) {
 682                 $qtys[0] = 0 - $row['quantity'];
 683             } elseif ($row['distributor_id']) {
 684                 $qtys[1] = 0 - $row['quantity'];
 685             } elseif ($row['fee'] != 0) {
 686                 $qtys[2] = 0 - $row['quantity'];
 687             } else { // no pid, distributor, source lot or fee: must be an adjustment
 688                 $qtys[4] = 0 - $row['quantity'];
 689             }
 690         }
 691
 692         thisLineItem(
 693             $row['drug_id'],
 694             $row['warehouse_id'],
 695             $row['pid'] + 0,
 696             $row['encounter'] + 0,
 697             $row['name'],
 698             $row['title'],
 699             $row['sale_date'],
 700             $qtys,
 701             $row['invoice_refno']
 702         );
 703     }
 704
 705     // Generate totals for last product and warehouse.
 706     thisLineItem(0, '~', 0, 0, '', '', '0000-00-00', array(0, 0, 0, 0, 0));
 707
 708     // Grand totals line.
 709     if ($form_action != 'export') { // if submit
 710         $grei = getEndInventory();
 711         ?>
 712         <tr bgcolor="#dddddd">
 713         <td class="detail" colspan="4">
 714             <?php echo xlt('Grand Total'); ?>
 715         </td>
 716         <td class="dehead" align="right">
 717             <?php echo text($grei - $grandqtys[0] - $grandqtys[1] - $grandqtys[2] - $grandqtys[3] - $grandqtys[4]); ?>
 718         </td>
 719         <td class="dehead" align="right">
 720             <?php echo text($grandqtys[0]); ?>
 721         </td>
 722         <td class="dehead" align="right">
 723             <?php echo text($grandqtys[1]); ?>
 724         </td>
 725         <td class="dehead" align="right">
 726             <?php echo text($grandqtys[2]); ?>
 727         </td>
 728         <td class="dehead" align="right">
 729             <?php echo text($grandqtys[3]); ?>
 730         </td>
 731         <td class="dehead" align="right">
 732             <?php echo text($grandqtys[4]); ?>
 733         </td>
 734         <td class="dehead" align="right">
 735             <?php echo text($grei); ?>
 736         </td>
 737         </tr>
 738         <?php
 739     } // End if submit
 740 } // end if submit or export
 741
 742 if ($form_action != 'export') {
 743     if ($form_action) {
 744         ?>
 745         </tbody>
 746         </table>
 747         </div>
 748         <?php
 749     } // end if ($form_action)
 750     ?>
 751     </form>
 752     </center>
 753     </body>
 754     </html>
 755     <?php
 756 } // End not export
 757 ?>