interface/reports/inventory_transactions.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 // This is an inventory transactions list.
  10
  11 //SANITIZE ALL ESCAPES
  12 $sanitize_all_escapes=true;
  13 //
  14
  15 //STOP FAKE REGISTER GLOBALS
  16 $fake_register_globals=false;
  17 //
  18
  19 require_once("../globals.php");
  20 require_once("$srcdir/patient.inc");
  21 require_once("$srcdir/acl.inc");
  22
  23 function bucks($amount) {
  24   if ($amount != 0) return oeFormatMoney($amount);
  25   return '';
  26 }
  27
  28 function esc4Export($str) {
  29   return str_replace('"', '\\"', $str);
  30 }
  31
  32 function thisLineItem($row, $xfer=false) {
  33   global $grandtotal, $grandqty, $encount, $form_action;
  34
  35   $invnumber = '';
  36   $dpname = '';
  37
  38   if (!empty($row['pid'])) {
  39     $ttype = xl('Sale');
  40     $dpname = $row['plname'];
  41     if (!empty($row['pfname'])) {
  42       $dpname .= ', ' . $row['pfname'];
  43       if (!empty($row['pmname'])) $dpname .= ' ' . $row['pmname'];
  44     }
  45     $invnumber = empty($row['invoice_refno']) ?
  46       "{$row['pid']}.{$row['encounter']}" : $row['invoice_refno'];
  47   }
  48   else if (!empty($row['distributor_id'])) {
  49     $ttype = xl('Distribution');
  50     if (!empty($row['organization'])) {
  51       $dpname = $row['organization'];
  52     }
  53     else {
  54       $dpname = $row['dlname'];
  55       if (!empty($row['dfname'])) {
  56         $dpname .= ', ' . $row['dfname'];
  57         if (!empty($row['dmname'])) $dpname .= ' ' . $row['dmname'];
  58       }
  59     }
  60   }
  61   else if (!empty($row['xfer_inventory_id']) || $xfer) {
  62     $ttype = xl('Transfer');
  63   }
  64   else if ($row['fee'] != 0) {
  65     $ttype = xl('Purchase');
  66   }
  67   else {
  68     $ttype = xl('Adjustment');
  69   }
  70
  71   if ($form_action == 'export') {
  72     echo '"' . oeFormatShortDate($row['sale_date']) . '",';
  73     echo '"' . $ttype                               . '",';
  74     echo '"' . esc4Export($row['name'])             . '",';
  75     echo '"' . esc4Export($row['lot_number'])       . '",';
  76     echo '"' . esc4Export($row['warehouse'])        . '",';
  77     echo '"' . esc4Export($dpname)                  . '",';
  78     echo '"' . (0 - $row['quantity'])               . '",';
  79     echo '"' . bucks($row['fee'])                   . '",';
  80     echo '"' . $row['billed']                       . '",';
  81     echo '"' . esc4Export($row['notes'])            . '"' . "\n";
  82   }
  83   else {
  84     $bgcolor = (++$encount & 1) ? "#ddddff" : "#ffdddd";
  85 ?>
  86
  87  <tr bgcolor="<?php echo $bgcolor; ?>">
  88   <td class="detail">
  89    <?php echo htmlspecialchars(oeFormatShortDate($row['sale_date'])); ?>
  90   </td>
  91   <td class="detail">
  92    <?php echo htmlspecialchars($ttype); ?>
  93   </td>
  94   <td class="detail">
  95    <?php echo htmlspecialchars($row['name']); ?>
  96   </td>
  97   <td class="detail">
  98    <?php echo htmlspecialchars($row['lot_number']); ?>
  99   </td>
 100   <td class="detail">
 101    <?php echo htmlspecialchars($row['warehouse']); ?>
 102   </td>
 103   <td class="detail">
 104    <?php echo htmlspecialchars($dpname); ?>
 105   </td>
 106   <td class="detail" align="right">
 107    <?php echo htmlspecialchars(0 - $row['quantity']); ?>
 108   </td>
 109   <td class="detail" align="right">
 110    <?php echo htmlspecialchars(bucks($row['fee'])); ?>
 111   </td>
 112   <td class="detail" align="center">
 113    <?php echo empty($row['billed']) ? '&nbsp;' : '*'; ?>
 114   </td>
 115   <td class="detail">
 116    <?php echo htmlspecialchars($row['notes']); ?>
 117   </td>
 118  </tr>
 119 <?php
 120   } // End not csv export
 121
 122   $grandtotal   += $row['fee'];
 123   $grandqty     -= $row['quantity'];
 124
 125   // In the special case of a transfer, generate a second line item for
 126   // the source lot.
 127   if (!empty($row['xfer_inventory_id'])) {
 128     $row['xfer_inventory_id'] = 0;
 129     $row['lot_number'] = $row['lot_number_2'];
 130     $row['warehouse'] = $row['warehouse_2'];
 131     $row['quantity'] = 0 - $row['quantity'];
 132     $row['fee'] = 0 - $row['fee'];
 133     thisLineItem($row, true);
 134   }
 135
 136 } // end function
 137
 138 if (! acl_check('acct', 'rep')) die(htmlspecialchars(xl("Unauthorized access."), ENT_NOQUOTES));
 139
 140 // this is "" or "submit" or "export".
 141 $form_action = $_POST['form_action'];
 142
 143 $form_from_date  = fixDate($_POST['form_from_date'], date('Y-m-d'));
 144 $form_to_date    = fixDate($_POST['form_to_date']  , date('Y-m-d'));
 145 $form_trans_type = isset($_POST['form_trans_type']) ? $_POST['form_trans_type'] : '0';
 146
 147 $encount = 0;
 148
 149 if ($form_action == 'export') {
 150   header("Pragma: public");
 151   header("Expires: 0");
 152   header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 153   header("Content-Type: application/force-download");
 154   header("Content-Disposition: attachment; filename=inventory_transactions.csv");
 155   header("Content-Description: File Transfer");
 156   // CSV headers:
 157   echo '"' . xl('Date'       ) . '",';
 158   echo '"' . xl('Transaction') . '",';
 159   echo '"' . xl('Product'    ) . '",';
 160   echo '"' . xl('Lot'        ) . '",';
 161   echo '"' . xl('Warehouse'  ) . '",';
 162   echo '"' . xl('Who'        ) . '",';
 163   echo '"' . xl('Qty'        ) . '",';
 164   echo '"' . xl('Amount'     ) . '",';
 165   echo '"' . xl('Billed'     ) . '",';
 166   echo '"' . xl('Notes'      ) . '"' . "\n";
 167 } // end export
 168 else {
 169 ?>
 170 <html>
 171 <head>
 172 <?php html_header_show(); ?>
 173 <title><?php echo htmlspecialchars(xl('Inventory Transactions'), ENT_NOQUOTES) ?></title>
 174 <link rel='stylesheet' href='<?php echo $css_header ?>' type='text/css'>
 175
 176 <style type="text/css">
 177  /* specifically include & exclude from printing */
 178  @media print {
 179   #report_parameters {visibility: hidden; display: none;}
 180   #report_parameters_daterange {visibility: visible; display: inline;}
 181   #report_results {margin-top: 30px;}
 182  }
 183
 184  /* specifically exclude some from the screen */
 185  @media screen {
 186   #report_parameters_daterange {visibility: hidden; display: none;}
 187  }
 188
 189  body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
 190  .dehead    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:bold }
 191  .detail    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:normal }
 192
 193  #report_results table thead {
 194   font-size:10pt;
 195  }
 196 </style>
 197
 198 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 199 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 200 <?php include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?>
 201 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 202 <script type="text/javascript" src="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-min-1-9-1/index.js"></script>
 203 <script type="text/javascript" src="../../library/js/report_helper.js?v=<?php echo $v_js_includes; ?>"></script>
 204
 205 <script language='JavaScript'>
 206
 207  $(document).ready(function() {
 208   oeFixedHeaderSetup(document.getElementById('mymaintable'));
 209   var win = top.printLogSetup ? top : opener.top;
 210   win.printLogSetup(document.getElementById('printbutton'));
 211  });
 212
 213  function mysubmit(action) {
 214   var f = document.forms[0];
 215   f.form_action.value = action;
 216   top.restoreSession();
 217   f.submit();
 218  }
 219
 220 </script>
 221
 222 </head>
 223
 224 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
 225 <center>
 226
 227 <h2><?php echo htmlspecialchars(xl('Inventory Transactions'), ENT_NOQUOTES) ?></h2>
 228
 229 <form method='post' action='inventory_transactions.php'>
 230
 231 <div id="report_parameters">
 232 <!-- form_action is set to "submit" or "export" at form submit time -->
 233 <input type='hidden' name='form_action' value='' />
 234 <table>
 235  <tr>
 236   <td width='50%'>
 237    <table class='text'>
 238     <tr>
 239      <td class='label'>
 240       <?php echo htmlspecialchars(xl('Type'), ENT_NOQUOTES); ?>:
 241      </td>
 242      <td nowrap>
 243       <select name='form_trans_type' onchange='trans_type_changed()'>
 244 <?php
 245 foreach (array(
 246   '0' => xl('All'),
 247   '2' => xl('Purchase/Return'),
 248   '1' => xl('Sale'),
 249   '6' => xl('Distribution'),
 250   '4' => xl('Transfer'),
 251   '5' => xl('Adjustment'),
 252 ) as $key => $value)
 253 {
 254   echo "       <option value='$key'";
 255   if ($key == $form_trans_type) echo " selected";
 256   echo ">" . htmlspecialchars($value, ENT_NOQUOTES) . "</option>\n";
 257 }
 258 ?>
 259       </select>
 260      </td>
 261      <td class='label'>
 262       <?php echo htmlspecialchars(xl('From'), ENT_NOQUOTES); ?>:
 263      </td>
 264      <td nowrap>
 265       <input type='text' name='form_from_date' id="form_from_date" size='10'
 266        value='<?php echo htmlspecialchars($form_from_date, ENT_QUOTES) ?>'
 267        title='<?php echo htmlspecialchars(xl('yyyy-mm-dd'), ENT_QUOTES) ?>'
 268        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)'>
 269       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 270        id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 271        title='<?php echo htmlspecialchars(xl('Click here to choose a date'), ENT_QUOTES); ?>'>
 272      </td>
 273      <td class='label'>
 274       <?php xl('To','e'); ?>:
 275      </td>
 276      <td nowrap>
 277       <input type='text' name='form_to_date' id="form_to_date" size='10'
 278        value='<?php echo htmlspecialchars($form_to_date, ENT_QUOTES) ?>'
 279        title='<?php echo htmlspecialchars(xl('yyyy-mm-dd'), ENT_QUOTES) ?>'
 280        onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)'>
 281       <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 282        id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 283        title='<?php echo htmlspecialchars(xl('Click here to choose a date'), ENT_QUOTES); ?>'>
 284      </td>
 285     </tr>
 286    </table>
 287   </td>
 288   <td align='left' valign='middle'>
 289    <table style='border-left:1px solid; width:100%; height:100%'>
 290     <tr>
 291      <td valign='middle'>
 292       <a href='#' class='css_button' onclick='mysubmit("submit")' style='margin-left:1em'>
 293        <span><?php echo htmlspecialchars(xl('Submit'), ENT_NOQUOTES); ?></span>
 294       </a>
 295 <?php if ($form_action) { ?>
 296       <a href='#' class='css_button' id='printbutton' style='margin-left:1em'>
 297        <span><?php echo htmlspecialchars(xl('Print'), ENT_NOQUOTES); ?></span>
 298       </a>
 299       <a href='#' class='css_button' onclick='mysubmit("export")' style='margin-left:1em'>
 300        <span><?php echo htmlspecialchars(xl('CSV Export'), ENT_NOQUOTES); ?></span>
 301       </a>
 302 <?php } ?>
 303      </td>
 304     </tr>
 305    </table>
 306   </td>
 307  </tr>
 308 </table>
 309 </div>
 310
 311 <?php if ($form_action) { // if submit (already not export here) ?>
 312
 313 <div id="report_results">
 314 <table border='0' cellpadding='1' cellspacing='2' width='98%' id='mymaintable' class='mymaintable'>
 315  <thead>
 316  <tr bgcolor="#dddddd">
 317   <td class="dehead">
 318    <?php echo htmlspecialchars(xl('Date'), ENT_NOQUOTES); ?>
 319   </td>
 320   <td class="dehead">
 321    <?php echo htmlspecialchars(xl('Transaction'), ENT_NOQUOTES); ?>
 322   </td>
 323   <td class="dehead">
 324    <?php echo htmlspecialchars(xl('Product'), ENT_NOQUOTES); ?>
 325   </td>
 326   <td class="dehead">
 327    <?php echo htmlspecialchars(xl('Lot'), ENT_NOQUOTES); ?>
 328   </td>
 329   <td class="dehead">
 330    <?php echo htmlspecialchars(xl('Warehouse'), ENT_NOQUOTES); ?>
 331   </td>
 332   <td class="dehead">
 333    <?php echo htmlspecialchars(xl('Who'), ENT_NOQUOTES); ?>
 334   </td>
 335   <td class="dehead" align="right">
 336    <?php echo htmlspecialchars(xl('Qty'), ENT_NOQUOTES); ?>
 337   </td>
 338   <td class="dehead" align="right">
 339    <?php echo htmlspecialchars(xl('Amount'), ENT_NOQUOTES); ?>
 340   </td>
 341   <td class="dehead" align="Center">
 342    <?php echo htmlspecialchars(xl('Billed'), ENT_NOQUOTES); ?>
 343   </td>
 344   <td class="dehead">
 345    <?php echo htmlspecialchars(xl('Notes'), ENT_NOQUOTES); ?>
 346   </td>
 347  </tr>
 348  </thead>
 349  <tbody>
 350 <?php
 351 } // end if submit
 352 } // end not export
 353
 354 if ($form_action) { // if submit or export
 355   $from_date = $form_from_date;
 356   $to_date   = $form_to_date;
 357
 358   $grandtotal = 0;
 359   $grandqty = 0;
 360
 361   $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
 362     "s.billed, s.notes, s.distributor_id, s.xfer_inventory_id, " .
 363     "p.fname AS pfname, p.mname AS pmname, p.lname AS plname, " .
 364     "u.fname AS dfname, u.mname AS dmname, u.lname AS dlname, u.organization, " .
 365     "d.name, fe.date, fe.invoice_refno, " .
 366     "i1.lot_number, i2.lot_number AS lot_number_2, " .
 367     "lo1.title AS warehouse, lo2.title AS warehouse_2 " .
 368     "FROM drug_sales AS s " .
 369     "JOIN drugs AS d ON d.drug_id = s.drug_id " .
 370     "LEFT JOIN drug_inventory AS i1 ON i1.inventory_id = s.inventory_id " .
 371     "LEFT JOIN drug_inventory AS i2 ON i2.inventory_id = s.xfer_inventory_id " .
 372     "LEFT JOIN patient_data AS p ON p.pid = s.pid " .
 373     "LEFT JOIN users AS u ON u.id = s.distributor_id " .
 374     "LEFT JOIN list_options AS lo1 ON lo1.list_id = 'warehouse' AND " .
 375     "lo1.option_id = i1.warehouse_id AND lo1.activity = 1 " .
 376     "LEFT JOIN list_options AS lo2 ON lo2.list_id = 'warehouse' AND " .
 377     "lo2.option_id = i2.warehouse_id AND lo2.activity = 1 " .
 378     "LEFT JOIN form_encounter AS fe ON fe.pid = s.pid AND fe.encounter = s.encounter " .
 379     "WHERE s.sale_date >= ? AND s.sale_date <= ? ";
 380   if ($form_trans_type == 2) { // purchase/return
 381     $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee != 0 ";
 382   }
 383   else if ($form_trans_type == 4) { // transfer
 384     $query .= "AND s.xfer_inventory_id != 0 ";
 385   }
 386   else if ($form_trans_type == 5) { // adjustment
 387     $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee = 0 ";
 388   }
 389   else if ($form_trans_type == 6) { // distribution
 390     $query .= "AND s.distributor_id != 0 ";
 391   }
 392   else if ($form_trans_type == 1) { // sale
 393     $query .= "AND s.pid != 0 ";
 394   }
 395   $query .= "ORDER BY s.sale_date, s.sale_id";
 396   //
 397   $res = sqlStatement($query, array($from_date, $to_date));
 398   while ($row = sqlFetchArray($res)) {
 399     thisLineItem($row);
 400   }
 401
 402   // Grand totals line.
 403   if ($form_action != 'export') { // if submit
 404 ?>
 405
 406  <tr bgcolor="#dddddd">
 407   <td class="dehead" colspan="6">
 408    <?php echo htmlspecialchars(xl('Grand Total'), ENT_NOQUOTES); ?>
 409   </td>
 410   <td class="dehead" align="right">
 411    <?php echo htmlspecialchars($grandqty, ENT_NOQUOTES); ?>
 412   </td>
 413   <td class="dehead" align="right">
 414    <?php echo htmlspecialchars(bucks($grandtotal), ENT_NOQUOTES); ?>
 415   </td>
 416   <td class="dehead" colspan="2">
 417
 418   </td>
 419  </tr>
 420
 421 <?php
 422   } // End if submit
 423 } // end if submit or export
 424
 425 if ($form_action != 'export') {
 426   if ($form_action) {
 427 ?>
 428  </tbody>
 429 </table>
 430 </div>
 431 <?php
 432   } // end if ($form_action)
 433 ?>
 434
 435 </form>
 436 </center>
 437 </body>
 438
 439 <!-- stuff for the popup calendar -->
 440 <script language="Javascript">
 441  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 442  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 443 </script>
 444
 445 </html>
 446 <?php
 447 } // End not export
 448 ?>