interface/reports/inventory_activity.php

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