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