interface/reports/inventory_transactions.php

   1 <?php
   2 /**
   3  * This is an inventory transactions list.
   4  *
   5  * @package   OpenEMR
   6  * @link      http://www.open-emr.org
   7  * @author    Rod Roark <rod@sunsetsystems.com>
   8  * @author    Brady Miller <brady.g.miller@gmail.com>
   9  * @copyright Copyright (c) 2010-2016 Rod Roark <rod@sunsetsystems.com>
  10  * @copyright Copyright (c) 2017 Brady Miller <brady.g.miller@gmail.com>
  11  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  12  */
  13
  14
  15 require_once("../globals.php");
  16 require_once("$srcdir/patient.inc");
  17 require_once("$srcdir/acl.inc");
  18
  19 function bucks($amount)
  20 {
  21     if ($amount != 0) {
  22         return oeFormatMoney($amount);
  23     }
  24
  25     return '';
  26 }
  27
  28 function esc4Export($str)
  29 {
  30     return str_replace('"', '\\"', $str);
  31 }
  32
  33 function thisLineItem($row, $xfer = false)
  34 {
  35     global $grandtotal, $grandqty, $encount, $form_action;
  36
  37     $invnumber = '';
  38     $dpname = '';
  39
  40     if (!empty($row['pid'])) {
  41         $ttype = xl('Sale');
  42         $dpname = $row['plname'];
  43         if (!empty($row['pfname'])) {
  44             $dpname .= ', ' . $row['pfname'];
  45             if (!empty($row['pmname'])) {
  46                 $dpname .= ' ' . $row['pmname'];
  47             }
  48         }
  49
  50         $invnumber = empty($row['invoice_refno']) ?
  51         "{$row['pid']}.{$row['encounter']}" : $row['invoice_refno'];
  52     } else if (!empty($row['distributor_id'])) {
  53         $ttype = xl('Distribution');
  54         if (!empty($row['organization'])) {
  55             $dpname = $row['organization'];
  56         } else {
  57             $dpname = $row['dlname'];
  58             if (!empty($row['dfname'])) {
  59                 $dpname .= ', ' . $row['dfname'];
  60                 if (!empty($row['dmname'])) {
  61                     $dpname .= ' ' . $row['dmname'];
  62                 }
  63             }
  64         }
  65     } else if (!empty($row['xfer_inventory_id']) || $xfer) {
  66         $ttype = xl('Transfer');
  67     } else if ($row['fee'] != 0) {
  68         $ttype = xl('Purchase');
  69     } else {
  70         $ttype = xl('Adjustment');
  71     }
  72
  73     if ($form_action == 'export') {
  74         echo '"' . oeFormatShortDate($row['sale_date']) . '",';
  75         echo '"' . $ttype                               . '",';
  76         echo '"' . esc4Export($row['name'])             . '",';
  77         echo '"' . esc4Export($row['lot_number'])       . '",';
  78         echo '"' . esc4Export($row['warehouse'])        . '",';
  79         echo '"' . esc4Export($dpname)                  . '",';
  80         echo '"' . (0 - $row['quantity'])               . '",';
  81         echo '"' . bucks($row['fee'])                   . '",';
  82         echo '"' . $row['billed']                       . '",';
  83         echo '"' . esc4Export($row['notes'])            . '"' . "\n";
  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 } // end function
 137
 138 if (! acl_check('acct', 'rep')) {
 139     die(htmlspecialchars(xl("Unauthorized access."), ENT_NOQUOTES));
 140 }
 141
 142 // this is "" or "submit" or "export".
 143 $form_action = $_POST['form_action'];
 144
 145 $form_from_date  = fixDate($_POST['form_from_date'], date('Y-m-d'));
 146 $form_to_date    = fixDate($_POST['form_to_date'], date('Y-m-d'));
 147 $form_trans_type = isset($_POST['form_trans_type']) ? $_POST['form_trans_type'] : '0';
 148
 149 $encount = 0;
 150
 151 if ($form_action == 'export') {
 152     header("Pragma: public");
 153     header("Expires: 0");
 154     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 155     header("Content-Type: application/force-download");
 156     header("Content-Disposition: attachment; filename=inventory_transactions.csv");
 157     header("Content-Description: File Transfer");
 158   // CSV headers:
 159     echo '"' . xl('Date') . '",';
 160     echo '"' . xl('Transaction') . '",';
 161     echo '"' . xl('Product') . '",';
 162     echo '"' . xl('Lot') . '",';
 163     echo '"' . xl('Warehouse') . '",';
 164     echo '"' . xl('Who') . '",';
 165     echo '"' . xl('Qty') . '",';
 166     echo '"' . xl('Amount') . '",';
 167     echo '"' . xl('Billed') . '",';
 168     echo '"' . xl('Notes') . '"' . "\n";
 169 } // end export
 170 else {
 171 ?>
 172 <html>
 173 <head>
 174 <?php html_header_show(); ?>
 175 <title><?php echo htmlspecialchars(xl('Inventory Transactions'), ENT_NOQUOTES) ?></title>
 176 <link rel='stylesheet' href='<?php echo $css_header ?>' type='text/css'>
 177 <link rel="stylesheet" href="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-datetimepicker-2-5-4/build/jquery.datetimepicker.min.css">
 178
 179 <style type="text/css">
 180  /* specifically include & exclude from printing */
 181  @media print {
 182   #report_parameters {visibility: hidden; display: none;}
 183   #report_parameters_daterange {visibility: visible; display: inline;}
 184   #report_results {margin-top: 30px;}
 185  }
 186
 187  /* specifically exclude some from the screen */
 188  @media screen {
 189   #report_parameters_daterange {visibility: hidden; display: none;}
 190  }
 191
 192  body       { font-family:sans-serif; font-size:10pt; font-weight:normal }
 193  .dehead    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:bold }
 194  .detail    { color:#000000; font-family:sans-serif; font-size:10pt; font-weight:normal }
 195
 196  #report_results table thead {
 197   font-size:10pt;
 198  }
 199 </style>
 200
 201 <script type="text/javascript" src="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-min-1-9-1/index.js"></script>
 202     <script type="text/javascript" src="<?php echo $GLOBALS['assets_static_relative']; ?>/jquery-datetimepicker-2-5-4/build/jquery.datetimepicker.full.min.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         $('.datepicker').datetimepicker({
 213             <?php $datetimepicker_timepicker = false; ?>
 214             <?php $datetimepicker_showseconds = false; ?>
 215             <?php $datetimepicker_formatInput = false; ?>
 216             <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
 217             <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
 218         });
 219     });
 220
 221     function mysubmit(action) {
 222         var f = document.forms[0];
 223         f.form_action.value = action;
 224         top.restoreSession();
 225         f.submit();
 226     }
 227
 228 </script>
 229
 230 </head>
 231
 232 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
 233 <center>
 234
 235 <h2><?php echo htmlspecialchars(xl('Inventory Transactions'), ENT_NOQUOTES) ?></h2>
 236
 237 <form method='post' action='inventory_transactions.php'>
 238
 239 <div id="report_parameters">
 240 <!-- form_action is set to "submit" or "export" at form submit time -->
 241 <input type='hidden' name='form_action' value='' />
 242 <table>
 243  <tr>
 244   <td width='50%'>
 245    <table class='text'>
 246     <tr>
 247      <td class='label_custom'>
 248         <?php echo htmlspecialchars(xl('Type'), ENT_NOQUOTES); ?>:
 249      </td>
 250      <td nowrap>
 251       <select name='form_trans_type' onchange='trans_type_changed()'>
 252 <?php
 253 foreach (array(
 254   '0' => xl('All'),
 255   '2' => xl('Purchase/Return'),
 256   '1' => xl('Sale'),
 257   '6' => xl('Distribution'),
 258   '4' => xl('Transfer'),
 259   '5' => xl('Adjustment'),
 260 ) as $key => $value) {
 261     echo "       <option value='$key'";
 262     if ($key == $form_trans_type) {
 263         echo " selected";
 264     }
 265
 266     echo ">" . htmlspecialchars($value, ENT_NOQUOTES) . "</option>\n";
 267 }
 268 ?>
 269       </select>
 270      </td>
 271      <td class='label_custom'>
 272         <?php echo htmlspecialchars(xl('From'), ENT_NOQUOTES); ?>:
 273      </td>
 274      <td nowrap>
 275       <input type='text' class='datepicker' name='form_from_date' id="form_from_date" size='10'
 276        value='<?php echo htmlspecialchars($form_from_date, ENT_QUOTES) ?>'
 277        title='<?php echo htmlspecialchars(xl('yyyy-mm-dd'), ENT_QUOTES) ?>'>
 278      </td>
 279      <td class='label_custom'>
 280         <?php xl('To', 'e'); ?>:
 281      </td>
 282      <td nowrap>
 283       <input type='text' class='datepicker' name='form_to_date' id="form_to_date" size='10'
 284        value='<?php echo htmlspecialchars($form_to_date, ENT_QUOTES) ?>'
 285        title='<?php echo htmlspecialchars(xl('yyyy-mm-dd'), ENT_QUOTES) ?>'>
 286      </td>
 287     </tr>
 288    </table>
 289   </td>
 290   <td align='left' valign='middle'>
 291    <table style='border-left:1px solid; width:100%; height:100%'>
 292     <tr>
 293      <td valign='middle'>
 294       <a href='#' class='css_button' onclick='mysubmit("submit")' style='margin-left:1em'>
 295        <span><?php echo htmlspecialchars(xl('Submit'), ENT_NOQUOTES); ?></span>
 296       </a>
 297 <?php if ($form_action) { ?>
 298       <a href='#' class='css_button' id='printbutton' style='margin-left:1em'>
 299        <span><?php echo htmlspecialchars(xl('Print'), ENT_NOQUOTES); ?></span>
 300       </a>
 301       <a href='#' class='css_button' onclick='mysubmit("export")' style='margin-left:1em'>
 302        <span><?php echo htmlspecialchars(xl('CSV Export'), ENT_NOQUOTES); ?></span>
 303       </a>
 304 <?php } ?>
 305      </td>
 306     </tr>
 307    </table>
 308   </td>
 309  </tr>
 310 </table>
 311 </div>
 312
 313 <?php if ($form_action) { // if submit (already not export here) ?>
 314
 315 <div id="report_results">
 316 <table border='0' cellpadding='1' cellspacing='2' width='98%' id='mymaintable' class='mymaintable'>
 317  <thead>
 318  <tr bgcolor="#dddddd">
 319   <td class="dehead">
 320     <?php echo htmlspecialchars(xl('Date'), ENT_NOQUOTES); ?>
 321   </td>
 322   <td class="dehead">
 323     <?php echo htmlspecialchars(xl('Transaction'), ENT_NOQUOTES); ?>
 324   </td>
 325   <td class="dehead">
 326     <?php echo htmlspecialchars(xl('Product'), ENT_NOQUOTES); ?>
 327   </td>
 328   <td class="dehead">
 329     <?php echo htmlspecialchars(xl('Lot'), ENT_NOQUOTES); ?>
 330   </td>
 331   <td class="dehead">
 332     <?php echo htmlspecialchars(xl('Warehouse'), ENT_NOQUOTES); ?>
 333   </td>
 334   <td class="dehead">
 335     <?php echo htmlspecialchars(xl('Who'), ENT_NOQUOTES); ?>
 336   </td>
 337   <td class="dehead" align="right">
 338     <?php echo htmlspecialchars(xl('Qty'), ENT_NOQUOTES); ?>
 339   </td>
 340   <td class="dehead" align="right">
 341     <?php echo htmlspecialchars(xl('Amount'), ENT_NOQUOTES); ?>
 342   </td>
 343   <td class="dehead" align="Center">
 344     <?php echo htmlspecialchars(xl('Billed'), ENT_NOQUOTES); ?>
 345   </td>
 346   <td class="dehead">
 347     <?php echo htmlspecialchars(xl('Notes'), ENT_NOQUOTES); ?>
 348   </td>
 349  </tr>
 350  </thead>
 351  <tbody>
 352 <?php
 353 } // end if submit
 354 } // end not export
 355
 356 if ($form_action) { // if submit or export
 357     $from_date = $form_from_date;
 358     $to_date   = $form_to_date;
 359
 360     $grandtotal = 0;
 361     $grandqty = 0;
 362
 363     $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
 364     "s.billed, s.notes, s.distributor_id, s.xfer_inventory_id, " .
 365     "p.fname AS pfname, p.mname AS pmname, p.lname AS plname, " .
 366     "u.fname AS dfname, u.mname AS dmname, u.lname AS dlname, u.organization, " .
 367     "d.name, fe.date, fe.invoice_refno, " .
 368     "i1.lot_number, i2.lot_number AS lot_number_2, " .
 369     "lo1.title AS warehouse, lo2.title AS warehouse_2 " .
 370     "FROM drug_sales AS s " .
 371     "JOIN drugs AS d ON d.drug_id = s.drug_id " .
 372     "LEFT JOIN drug_inventory AS i1 ON i1.inventory_id = s.inventory_id " .
 373     "LEFT JOIN drug_inventory AS i2 ON i2.inventory_id = s.xfer_inventory_id " .
 374     "LEFT JOIN patient_data AS p ON p.pid = s.pid " .
 375     "LEFT JOIN users AS u ON u.id = s.distributor_id " .
 376     "LEFT JOIN list_options AS lo1 ON lo1.list_id = 'warehouse' AND " .
 377     "lo1.option_id = i1.warehouse_id AND lo1.activity = 1 " .
 378     "LEFT JOIN list_options AS lo2 ON lo2.list_id = 'warehouse' AND " .
 379     "lo2.option_id = i2.warehouse_id AND lo2.activity = 1 " .
 380     "LEFT JOIN form_encounter AS fe ON fe.pid = s.pid AND fe.encounter = s.encounter " .
 381     "WHERE s.sale_date >= ? AND s.sale_date <= ? ";
 382     if ($form_trans_type == 2) { // purchase/return
 383         $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee != 0 ";
 384     } else if ($form_trans_type == 4) { // transfer
 385         $query .= "AND s.xfer_inventory_id != 0 ";
 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     } else if ($form_trans_type == 6) { // distribution
 389         $query .= "AND s.distributor_id != 0 ";
 390     } else if ($form_trans_type == 1) { // sale
 391         $query .= "AND s.pid != 0 ";
 392     }
 393
 394     $query .= "ORDER BY s.sale_date, s.sale_id";
 395   //
 396     $res = sqlStatement($query, array($from_date, $to_date));
 397     while ($row = sqlFetchArray($res)) {
 398         thisLineItem($row);
 399     }
 400
 401   // Grand totals line.
 402     if ($form_action != 'export') { // if submit
 403     ?>
 404
 405    <tr bgcolor="#dddddd">
 406     <td class="dehead" colspan="6">
 407         <?php echo htmlspecialchars(xl('Grand Total'), ENT_NOQUOTES); ?>
 408   </td>
 409   <td class="dehead" align="right">
 410         <?php echo htmlspecialchars($grandqty, ENT_NOQUOTES); ?>
 411   </td>
 412   <td class="dehead" align="right">
 413         <?php echo htmlspecialchars(bucks($grandtotal), ENT_NOQUOTES); ?>
 414   </td>
 415   <td class="dehead" colspan="2">
 416
 417   </td>
 418  </tr>
 419
 420 <?php
 421     } // End if submit
 422 } // end if submit or export
 423
 424 if ($form_action != 'export') {
 425     if ($form_action) {
 426     ?>
 427    </tbody>
 428   </table>
 429   </div>
 430     <?php
 431     } // end if ($form_action)
 432 ?>
 433
 434 </form>
 435 </center>
 436 </body>
 437 </html>
 438 <?php
 439 } // End not export
 440 ?>