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