interface/reports/inventory_activity.php

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