interface/reports/inventory_activity.php

   1 <?php
   2 // Copyright (C) 2010 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 language='JavaScript'>
 388  var mypcc = '<? echo $GLOBALS['phone_country_code'] ?>';
 389  function mysubmit(action) {
 390   var f = document.forms[0];
 391   f.form_action.value = action;
 392   top.restoreSession();
 393   f.submit();
 394  }
 395 </script>
 396
 397 </head>
 398
 399 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
 400
 401 <center>
 402
 403 <h2><?php echo htmlspecialchars(xl('Inventory Activity'))?></h2>
 404
 405 <form method='post' action='inventory_activity.php?product=<?php echo htmlspecialchars($product_first, ENT_QUOTES); ?>'>
 406
 407 <div id="report_parameters">
 408 <!-- form_action is set to "submit" or "export" at form submit time -->
 409 <input type='hidden' name='form_action' value='' />
 410 <table>
 411  <tr>
 412   <td width='50%'>
 413    <table class='text'>
 414     <tr>
 415      <td class='label'>
 416       <?php echo htmlspecialchars(xl('By')); ?>:
 417      </td>
 418      <td nowrap>
 419       <select name='form_by'>
 420        <option value='p'><?php echo htmlspecialchars(xl('Product')); ?></option>
 421        <option value='w'<?php if (!$product_first) echo ' selected'; ?>><?php echo htmlspecialchars(xl('Warehouse')); ?></option>
 422       </select>
 423      </td>
 424      <td class='label'>
 425       <?php echo htmlspecialchars(xl('From')); ?>:
 426      </td>
 427      <td nowrap>
 428       <input type='text' name='form_from_date' id="form_from_date" size='10'
 429        value='<?php echo htmlspecialchars($form_from_date, ENT_QUOTES) ?>'
 430        title='<?php echo htmlspecialchars(xl('yyyy-mm-dd'), ENT_QUOTES) ?>'
 431        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)'>
 432       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 433        id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 434        title='<?php echo htmlspecialchars(xl('Click here to choose a date'), ENT_QUOTES); ?>'>
 435      </td>
 436      <td class='label'>
 437       <?php echo htmlspecialchars(xl('To')); ?>:
 438      </td>
 439      <td nowrap>
 440       <input type='text' name='form_to_date' id="form_to_date" size='10'
 441        value='<?php echo htmlspecialchars($form_to_date, ENT_QUOTES) ?>'
 442        title='<?php echo htmlspecialchars(xl('yyyy-mm-dd'), ENT_QUOTES) ?>'
 443        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)'>
 444       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 445        id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 446        title='<?php echo htmlspecialchars(xl('Click here to choose a date'), ENT_QUOTES); ?>'>
 447      </td>
 448     </tr>
 449     <tr>
 450      <td class='label'>
 451       <?php echo htmlspecialchars(xl('For'), ENT_NOQUOTES); ?>:
 452      </td>
 453      <td nowrap>
 454 <?php
 455 // Build a drop-down list of products.
 456 //
 457 $query = "SELECT drug_id, name FROM drugs ORDER BY name, drug_id";
 458 $pres = sqlStatement($query);
 459 echo "      <select name='form_product'>\n";
 460 echo "       <option value=''>-- " . htmlspecialchars(xl('All Products')) . " --\n";
 461 while ($prow = sqlFetchArray($pres)) {
 462   $drug_id = $prow['drug_id'];
 463   echo "       <option value='$drug_id'";
 464   if ($drug_id == $form_product) echo " selected";
 465   echo ">" . htmlspecialchars($prow['name']) . "\n";
 466 }
 467 echo "      </select>\n";
 468 ?>
 469      </td>
 470      <td class='label'>
 471       <?php echo htmlspecialchars(xl('Details')); ?>:
 472      </td>
 473      <td colspan='3' nowrap>
 474       <input type='checkbox' name='form_details' value='1'<?php if ($_POST['form_details']) echo " checked"; ?> />
 475      </td>
 476     </tr>
 477    </table>
 478   </td>
 479   <td align='left' valign='middle'>
 480    <table style='border-left:1px solid; width:100%; height:100%'>
 481     <tr>
 482      <td valign='middle'>
 483       <a href='#' class='css_button' onclick='mysubmit("submit")' style='margin-left:1em'>
 484        <span><?php echo htmlspecialchars(xl('Submit')); ?></span>
 485       </a>
 486 <?php if ($form_action) { ?>
 487       <a href='#' class='css_button' onclick='window.print()' style='margin-left:1em'>
 488        <span><?php echo htmlspecialchars(xl('Print')); ?></span>
 489       </a>
 490       <a href='#' class='css_button' onclick='mysubmit("export")' style='margin-left:1em'>
 491        <span><?php echo htmlspecialchars(xl('CSV Export')); ?></span>
 492       </a>
 493 <?php } ?>
 494      </td>
 495     </tr>
 496    </table>
 497   </td>
 498  </tr>
 499 </table>
 500 </div>
 501
 502 <?php if ($form_action) { // if submit (already not export here) ?>
 503
 504 <div id="report_results">
 505 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 506
 507  <tr bgcolor="#dddddd">
 508   <td class="dehead">
 509    <?php echo htmlspecialchars($product_first ? xl('Product') : xl('Warehouse')); ?>
 510   </td>
 511 <?php if ($_POST['form_details']) { ?>
 512   <td class="dehead">
 513    <?php echo htmlspecialchars($product_first ? xl('Warehouse') : xl('Product')); ?>
 514   </td>
 515   <td class="dehead">
 516    <?php echo htmlspecialchars(xl('Date')); ?>
 517   </td>
 518   <td class="dehead">
 519    <?php echo htmlspecialchars(xl('Invoice')); ?>
 520   </td>
 521 <?php } else { ?>
 522   <td class="dehead" colspan="3">
 523    <?php echo htmlspecialchars($product_first ? xl('Warehouse') : xl('Product')); ?>
 524   </td>
 525 <?php } ?>
 526   <td class="dehead" align="right" width="8%">
 527    <?php echo htmlspecialchars(xl('Start')); ?>
 528   </td>
 529   <td class="dehead" align="right" width="8%">
 530    <?php echo htmlspecialchars(xl('Sales')); ?>
 531   </td>
 532   <td class="dehead" align="right" width="8%">
 533    <?php echo htmlspecialchars(xl('Distributions')); ?>
 534   </td>
 535   <td class="dehead" align="right" width="8%">
 536    <?php echo htmlspecialchars(xl('Purchases')); ?>
 537   </td>
 538   <td class="dehead" align="right" width="8%">
 539    <?php echo htmlspecialchars(xl('Transfers')); ?>
 540   </td>
 541   <td class="dehead" align="right" width="8%">
 542    <?php echo htmlspecialchars(xl('Adjustments')); ?>
 543   </td>
 544   <td class="dehead" align="right" width="8%">
 545    <?php echo htmlspecialchars(xl('End')); ?>
 546   </td>
 547  </tr>
 548 <?php
 549 } // end if submit
 550 } // end not export
 551
 552 if ($form_action) { // if submit or export
 553   $from_date = $form_from_date;
 554   $to_date   = $form_to_date;
 555
 556   $product   = "";
 557   $prodleft  = "";
 558   $warehouse = "";
 559   $whleft    = "";
 560   $grandqtys = array(0, 0, 0, 0, 0);
 561   $priqtys   = array(0, 0, 0, 0, 0);
 562   $secqtys   = array(0, 0, 0, 0, 0);
 563   $last_inventory_id = 0;
 564
 565   $query = "SELECT s.sale_id, s.sale_date, s.quantity, s.fee, s.pid, s.encounter, " .
 566     "s.xfer_inventory_id, s.distributor_id, d.name, lo.title, " .
 567     "di.drug_id, di.warehouse_id, di.inventory_id, di.destroy_date, di.on_hand, " .
 568     "fe.invoice_refno " .
 569     "FROM drug_inventory AS di " .
 570     "JOIN drugs AS d ON d.drug_id = di.drug_id " .
 571     "LEFT JOIN drug_sales AS s ON " .
 572     "s.sale_date >= '$from_date' AND s.sale_date <= '$to_date' AND " .
 573     "s.drug_id = di.drug_id AND " .
 574     "( s.inventory_id = di.inventory_id OR s.xfer_inventory_id = di.inventory_id ) " .
 575     "LEFT JOIN list_options AS lo ON lo.list_id = 'warehouse' AND " .
 576     "lo.option_id = di.warehouse_id " .
 577     "LEFT JOIN form_encounter AS fe ON fe.pid = s.pid AND fe.encounter = s.encounter " .
 578     "WHERE ( di.destroy_date IS NULL OR di.destroy_date >= '$form_from_date' )";
 579
 580   // If a product was specified.
 581   if ($form_product) {
 582     $query .= " AND di.drug_id = '$form_product'";
 583   }
 584
 585   if ($product_first) {
 586     $query .= " ORDER BY d.name, d.drug_id, lo.title, di.warehouse_id, " .
 587       "di.inventory_id, s.sale_date, s.sale_id";
 588   } else {
 589     $query .= " ORDER BY lo.title, di.warehouse_id, d.name, d.drug_id, " .
 590       "di.inventory_id, s.sale_date, s.sale_id";
 591   }
 592
 593   $res = sqlStatement($query);
 594   while ($row = sqlFetchArray($res)) {
 595
 596     // If new lot and it was destroyed during the reporting period,
 597     // generate a pseudo-adjustment for that.
 598     if ($row['inventory_id'] != $last_inventory_id) {
 599       $last_inventory_id = $row['inventory_id'];
 600       if (!empty($row['destroy_date']) && $row['on_hand'] != 0
 601         && $row['destroy_date'] <= $form_to_date)
 602       {
 603         thisLineItem($row['drug_id'], $row['warehouse_id'], 0,
 604           0, $row['name'], $row['title'], $row['destroy_date'],
 605           array(0, 0, 0, 0, 0 - $row['on_hand']),
 606           xl('Destroyed'));
 607       }
 608     }
 609
 610     $qtys = array(0, 0, 0, 0, 0);
 611     if ($row['sale_id']) {
 612       if ($row['xfer_inventory_id']) {
 613         // A transfer sale item will appear twice, once with each lot.
 614         if ($row['inventory_id'] == $row['xfer_inventory_id'])
 615           $qtys[3] = $row['quantity'];
 616         else
 617           $qtys[3] = 0 - $row['quantity'];
 618       }
 619       else if ($row['pid'])
 620         $qtys[0] = 0 - $row['quantity'];
 621       else if ($row['distributor_id'])
 622         $qtys[1] = 0 - $row['quantity'];
 623       else if ($row['fee'] != 0)
 624         $qtys[2] = 0 - $row['quantity'];
 625       else // no pid, distributor, source lot or fee: must be an adjustment
 626         $qtys[4] = 0 - $row['quantity'];
 627     }
 628     thisLineItem($row['drug_id'], $row['warehouse_id'], $row['pid'] + 0,
 629       $row['encounter'] + 0, $row['name'], $row['title'], $row['sale_date'],
 630       $qtys, $row['invoice_refno']);
 631   }
 632
 633   // Generate totals for last product and warehouse.
 634   thisLineItem(0, '~', 0, 0, '', '', '0000-00-00', array(0, 0, 0, 0, 0));
 635
 636   // Grand totals line.
 637   if ($form_action != 'export') { // if submit
 638     $grei = getEndInventory();
 639 ?>
 640  <tr bgcolor="#dddddd">
 641   <td class="detail" colspan="4">
 642    <?php echo htmlspecialchars(xl('Grand Total')); ?>
 643   </td>
 644   <td class="dehead" align="right">
 645    <?php echo $grei - $grandqtys[0] - $grandqtys[1] - $grandqtys[2] - $grandqtys[3] - $grandqtys[4]; ?>
 646   </td>
 647   <td class="dehead" align="right">
 648    <?php echo $grandqtys[0]; ?>
 649   </td>
 650   <td class="dehead" align="right">
 651    <?php echo $grandqtys[1]; ?>
 652   </td>
 653   <td class="dehead" align="right">
 654    <?php echo $grandqtys[2]; ?>
 655   </td>
 656   <td class="dehead" align="right">
 657    <?php echo $grandqtys[3]; ?>
 658   </td>
 659   <td class="dehead" align="right">
 660    <?php echo $grandqtys[4]; ?>
 661   </td>
 662   <td class="dehead" align="right">
 663    <?php echo $grei; ?>
 664   </td>
 665  </tr>
 666 <?php
 667   } // End if submit
 668 } // end if submit or export
 669
 670 if ($form_action != 'export') {
 671   if ($form_action) {
 672 ?>
 673 </table>
 674 </div>
 675 <?php
 676   } // end if ($form_action)
 677 ?>
 678
 679 </form>
 680 </center>
 681 </body>
 682
 683 <!-- stuff for the popup calendar -->
 684 <script language="Javascript">
 685  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 686  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 687 </script>
 688
 689 </html>
 690 <?php
 691 } // End not export
 692 ?>