interface/reports/inventory_activity.php

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