interface/reports/inventory_transactions.php

   1 <?php
   2
   3 /**
   4  * This is an inventory transactions list.
   5  *
   6  * @package   OpenEMR
   7  * @link      https://www.open-emr.org
   8  * @author    Rod Roark <rod@sunsetsystems.com>
   9  * @author    Brady Miller <brady.g.miller@gmail.com>
  10  * @copyright Copyright (c) 2010-2016 Rod Roark <rod@sunsetsystems.com>
  11  * @copyright Copyright (c) 2017-2019 Brady Miller <brady.g.miller@gmail.com>
  12  * @license   https://github.com/openemr/openemr/blob/master/LICENSE GNU General Public License 3
  13  */
  14
  15 require_once("../globals.php");
  16 require_once("$srcdir/patient.inc.php");
  17
  18 use OpenEMR\Common\Acl\AclMain;
  19 use OpenEMR\Common\Csrf\CsrfUtils;
  20 use OpenEMR\Common\Twig\TwigContainer;
  21 use OpenEMR\Common\Utils\FormatMoney;
  22 use OpenEMR\Core\Header;
  23
  24 if (!empty($_POST)) {
  25     if (!CsrfUtils::verifyCsrfToken($_POST["csrf_token_form"])) {
  26         CsrfUtils::csrfNotVerified();
  27     }
  28 }
  29
  30 function thisLineItem($row, $xfer = false)
  31 {
  32     global $grandtotal, $grandqty, $encount, $form_action;
  33
  34     $invnumber = '';
  35     $dpname = '';
  36
  37     if (!empty($row['pid'])) {
  38         $ttype = xl('Sale');
  39         $dpname = $row['plname'];
  40         if (!empty($row['pfname'])) {
  41             $dpname .= ', ' . $row['pfname'];
  42             if (!empty($row['pmname'])) {
  43                 $dpname .= ' ' . $row['pmname'];
  44             }
  45         }
  46
  47         $invnumber = empty($row['invoice_refno']) ?
  48         "{$row['pid']}.{$row['encounter']}" : $row['invoice_refno'];
  49     } elseif (!empty($row['distributor_id'])) {
  50         $ttype = xl('Distribution');
  51         if (!empty($row['organization'])) {
  52             $dpname = $row['organization'];
  53         } else {
  54             $dpname = $row['dlname'];
  55             if (!empty($row['dfname'])) {
  56                 $dpname .= ', ' . $row['dfname'];
  57                 if (!empty($row['dmname'])) {
  58                     $dpname .= ' ' . $row['dmname'];
  59                 }
  60             }
  61         }
  62     } elseif (!empty($row['xfer_inventory_id']) || $xfer) {
  63         $ttype = xl('Transfer');
  64     } elseif ($row['fee'] != 0) {
  65         $ttype = xl('Purchase');
  66     } else {
  67         $ttype = xl('Adjustment');
  68     }
  69
  70     if ($form_action == 'export') {
  71         echo csvEscape(oeFormatShortDate($row['sale_date'])) . ',';
  72         echo csvEscape($ttype)                               . ',';
  73         echo csvEscape($row['name'])                         . ',';
  74         echo csvEscape($row['lot_number'])                   . ',';
  75         echo csvEscape($row['warehouse'])                    . ',';
  76         echo csvEscape($dpname)                              . ',';
  77         echo csvEscape(0 - $row['quantity'])                 . ',';
  78         echo csvEscape(FormatMoney::getBucks($row['fee']))   . ',';
  79         echo csvEscape($row['billed'])                       . ',';
  80         echo csvEscape($row['notes'])                        . "\n";
  81     } else {
  82         $bgcolor = (++$encount & 1) ? "#ddddff" : "#ffdddd";
  83         ?>
  84
  85      <tr bgcolor="<?php echo $bgcolor; ?>">
  86   <td class="detail">
  87         <?php echo text(oeFormatShortDate($row['sale_date'])); ?>
  88   </td>
  89   <td class="detail">
  90         <?php echo text($ttype); ?>
  91   </td>
  92   <td class="detail">
  93         <?php echo text($row['name']); ?>
  94   </td>
  95   <td class="detail">
  96         <?php echo text($row['lot_number']); ?>
  97   </td>
  98   <td class="detail">
  99         <?php echo text($row['warehouse']); ?>
 100   </td>
 101   <td class="detail">
 102         <?php echo text($dpname); ?>
 103   </td>
 104   <td class="detail" align="right">
 105         <?php echo text(0 - $row['quantity']); ?>
 106   </td>
 107   <td class="detail" align="right">
 108         <?php echo text(FormatMoney::getBucks($row['fee'])); ?>
 109   </td>
 110   <td class="detail" align="center">
 111         <?php echo empty($row['billed']) ? '&nbsp;' : '*'; ?>
 112   </td>
 113   <td class="detail">
 114         <?php echo text($row['notes']); ?>
 115   </td>
 116  </tr>
 117         <?php
 118     } // End not csv export
 119
 120     $grandtotal   += $row['fee'];
 121     $grandqty     -= $row['quantity'];
 122
 123   // In the special case of a transfer, generate a second line item for
 124   // the source lot.
 125     if (!empty($row['xfer_inventory_id'])) {
 126         $row['xfer_inventory_id'] = 0;
 127         $row['lot_number'] = $row['lot_number_2'];
 128         $row['warehouse'] = $row['warehouse_2'];
 129         $row['quantity'] = 0 - $row['quantity'];
 130         $row['fee'] = 0 - $row['fee'];
 131         thisLineItem($row, true);
 132     }
 133 } // end function
 134
 135 if (! AclMain::aclCheckCore('acct', 'rep')) {
 136     echo (new TwigContainer(null, $GLOBALS['kernel']))->getTwig()->render('core/unauthorized.html.twig', ['pageTitle' => xl("Inventory Transactions")]);
 137     exit;
 138 }
 139
 140 // this is "" or "submit" or "export".
 141 $form_action = $_POST['form_action'];
 142
 143 $form_from_date = (isset($_POST['form_from_date'])) ? DateToYYYYMMDD($_POST['form_from_date']) : date('Y-m-d');
 144 $form_to_date   = (isset($_POST['form_to_date'])) ? DateToYYYYMMDD($_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 csvEscape(xl('Date')) . ',';
 158     echo csvEscape(xl('Transaction')) . ',';
 159     echo csvEscape(xl('Product')) . ',';
 160     echo csvEscape(xl('Lot')) . ',';
 161     echo csvEscape(xl('Warehouse')) . ',';
 162     echo csvEscape(xl('Who')) . ',';
 163     echo csvEscape(xl('Qty')) . ',';
 164     echo csvEscape(xl('Amount')) . ',';
 165     echo csvEscape(xl('Billed')) . ',';
 166     echo csvEscape(xl('Notes')) . "\n";
 167 } else { // end export
 168     ?>
 169 <html>
 170 <head>
 171 <title><?php echo xlt('Inventory Transactions'); ?></title>
 172
 173     <?php Header::setupHeader(['datetime-picker', 'report-helper']); ?>
 174
 175 <style>
 176  /* specifically include & exclude from printing */
 177  @media print {
 178   #report_parameters {visibility: hidden; display: none;}
 179   #report_parameters_daterange {visibility: visible; display: inline;}
 180   #report_results {margin-top: 30px;}
 181  }
 182
 183  /* specifically exclude some from the screen */
 184  @media screen {
 185   #report_parameters_daterange {
 186       visibility: hidden;
 187       display: none;
 188 }
 189  }
 190
 191  body {
 192      font-family:sans-serif;
 193      font-size:10pt;
 194      font-weight:normal;
 195 }
 196  .dehead {
 197      color:var(--black);
 198      font-family:sans-serif;
 199      font-size:10pt;
 200      font-weight:bold;
 201 }
 202  .detail { color:var(--black);
 203      font-family:sans-serif;
 204      font-size:10pt;
 205      font-weight:normal;
 206 }
 207
 208  #report_results table thead {
 209   font-size:10pt;
 210  }
 211 </style>
 212
 213 <script>
 214
 215     $(function () {
 216         oeFixedHeaderSetup(document.getElementById('mymaintable'));
 217         var win = top.printLogSetup ? top : opener.top;
 218         win.printLogSetup(document.getElementById('printbutton'));
 219
 220         $('.datepicker').datetimepicker({
 221             <?php $datetimepicker_timepicker = false; ?>
 222             <?php $datetimepicker_showseconds = false; ?>
 223             <?php $datetimepicker_formatInput = true; ?>
 224             <?php require($GLOBALS['srcdir'] . '/js/xl/jquery-datetimepicker-2-5-4.js.php'); ?>
 225             <?php // can add any additional javascript settings to datetimepicker here; need to prepend first setting with a comma ?>
 226         });
 227     });
 228
 229     function mysubmit(action) {
 230         var f = document.forms[0];
 231         f.form_action.value = action;
 232         top.restoreSession();
 233         f.submit();
 234     }
 235
 236 </script>
 237
 238 </head>
 239
 240 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class='body_top'>
 241 <center>
 242
 243 <h2><?php echo xlt('Inventory Transactions'); ?></h2>
 244
 245 <form method='post' action='inventory_transactions.php' onsubmit='return top.restoreSession()'>
 246 <input type="hidden" name="csrf_token_form" value="<?php echo attr(CsrfUtils::collectCsrfToken()); ?>" />
 247
 248 <div id="report_parameters">
 249 <!-- form_action is set to "submit" or "export" at form submit time -->
 250 <input type='hidden' name='form_action' value='' />
 251 <table>
 252  <tr>
 253   <td width='50%'>
 254    <table class='text'>
 255     <tr>
 256      <td class='label_custom'>
 257         <?php echo xlt('Type'); ?>:
 258      </td>
 259      <td nowrap>
 260       <select name='form_trans_type' onchange='trans_type_changed()'>
 261     <?php
 262     foreach (
 263         array(
 264         '0' => xl('All'),
 265         '2' => xl('Purchase/Return'),
 266         '1' => xl('Sale'),
 267         '6' => xl('Distribution'),
 268         '4' => xl('Transfer'),
 269         '5' => xl('Adjustment'),
 270         ) as $key => $value
 271     ) {
 272         echo "       <option value='" . attr($key) . "'";
 273         if ($key == $form_trans_type) {
 274             echo " selected";
 275         }
 276
 277         echo ">" . text($value) . "</option>\n";
 278     }
 279     ?>
 280       </select>
 281      </td>
 282      <td class='label_custom'>
 283         <?php echo xlt('From'); ?>:
 284      </td>
 285      <td nowrap>
 286       <input type='text' class='datepicker' name='form_from_date' id="form_from_date" size='10'
 287        value='<?php echo attr(oeFormatShortDate($form_from_date)); ?>'>
 288      </td>
 289      <td class='label_custom'>
 290         <?php xl('To{{Range}}', 'e'); ?>:
 291      </td>
 292      <td nowrap>
 293       <input type='text' class='datepicker' name='form_to_date' id="form_to_date" size='10' value='<?php echo attr(oeFormatShortDate($form_to_date)); ?>' />
 294      </td>
 295     </tr>
 296    </table>
 297   </td>
 298   <td align='left' valign='middle'>
 299    <table style='border-left:1px solid; width:100%; height:100%'>
 300     <tr>
 301      <td valign='middle'>
 302       <a href='#' class='btn btn-primary' onclick='mysubmit("submit")' style='margin-left:1em'>
 303        <span><?php echo xlt('Submit'); ?></span>
 304       </a>
 305     <?php if ($form_action) { ?>
 306       <a href='#' class='btn btn-primary' id='printbutton' style='margin-left:1em'>
 307        <span><?php echo xlt('Print'); ?></span>
 308       </a>
 309       <a href='#' class='btn btn-primary' onclick='mysubmit("export")' style='margin-left:1em'>
 310        <span><?php echo xlt('CSV Export'); ?></span>
 311       </a>
 312 <?php } ?>
 313      </td>
 314     </tr>
 315    </table>
 316   </td>
 317  </tr>
 318 </table>
 319 </div>
 320
 321     <?php if ($form_action) { // if submit (already not export here) ?>
 322 <div id="report_results">
 323 <table border='0' cellpadding='1' cellspacing='2' width='98%' id='mymaintable' class='mymaintable'>
 324  <thead>
 325  <tr bgcolor="#dddddd">
 326   <td class="dehead">
 327         <?php echo xlt('Date'); ?>
 328   </td>
 329   <td class="dehead">
 330         <?php echo xlt('Transaction'); ?>
 331   </td>
 332   <td class="dehead">
 333         <?php echo xlt('Product'); ?>
 334   </td>
 335   <td class="dehead">
 336         <?php echo xlt('Lot'); ?>
 337   </td>
 338   <td class="dehead">
 339         <?php echo xlt('Warehouse'); ?>
 340   </td>
 341   <td class="dehead">
 342         <?php echo xlt('Who'); ?>
 343   </td>
 344   <td class="dehead" align="right">
 345         <?php echo xlt('Qty'); ?>
 346   </td>
 347   <td class="dehead" align="right">
 348         <?php echo xlt('Amount'); ?>
 349   </td>
 350   <td class="dehead" align="Center">
 351         <?php echo xlt('Billed'); ?>
 352   </td>
 353   <td class="dehead">
 354         <?php echo xlt('Notes'); ?>
 355   </td>
 356  </tr>
 357  </thead>
 358  <tbody>
 359         <?php
 360     } // end if submit
 361 } // end not export
 362
 363 if ($form_action) { // if submit or export
 364     $from_date = $form_from_date;
 365     $to_date   = $form_to_date;
 366
 367     $grandtotal = 0;
 368     $grandqty = 0;
 369
 370     $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
 371     "s.billed, s.notes, s.distributor_id, s.xfer_inventory_id, " .
 372     "p.fname AS pfname, p.mname AS pmname, p.lname AS plname, " .
 373     "u.fname AS dfname, u.mname AS dmname, u.lname AS dlname, u.organization, " .
 374     "d.name, fe.date, fe.invoice_refno, " .
 375     "i1.lot_number, i2.lot_number AS lot_number_2, " .
 376     "lo1.title AS warehouse, lo2.title AS warehouse_2 " .
 377     "FROM drug_sales AS s " .
 378     "JOIN drugs AS d ON d.drug_id = s.drug_id " .
 379     "LEFT JOIN drug_inventory AS i1 ON i1.inventory_id = s.inventory_id " .
 380     "LEFT JOIN drug_inventory AS i2 ON i2.inventory_id = s.xfer_inventory_id " .
 381     "LEFT JOIN patient_data AS p ON p.pid = s.pid " .
 382     "LEFT JOIN users AS u ON u.id = s.distributor_id " .
 383     "LEFT JOIN list_options AS lo1 ON lo1.list_id = 'warehouse' AND " .
 384     "lo1.option_id = i1.warehouse_id AND lo1.activity = 1 " .
 385     "LEFT JOIN list_options AS lo2 ON lo2.list_id = 'warehouse' AND " .
 386     "lo2.option_id = i2.warehouse_id AND lo2.activity = 1 " .
 387     "LEFT JOIN form_encounter AS fe ON fe.pid = s.pid AND fe.encounter = s.encounter " .
 388     "WHERE s.sale_date >= ? AND s.sale_date <= ? ";
 389     if ($form_trans_type == 2) { // purchase/return
 390         $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee != 0 ";
 391     } elseif ($form_trans_type == 4) { // transfer
 392         $query .= "AND s.xfer_inventory_id != 0 ";
 393     } elseif ($form_trans_type == 5) { // adjustment
 394         $query .= "AND s.pid = 0 AND s.distributor_id = 0 AND s.xfer_inventory_id = 0 AND s.fee = 0 ";
 395     } elseif ($form_trans_type == 6) { // distribution
 396         $query .= "AND s.distributor_id != 0 ";
 397     } elseif ($form_trans_type == 1) { // sale
 398         $query .= "AND s.pid != 0 ";
 399     }
 400
 401     $query .= "ORDER BY s.sale_date, s.sale_id";
 402   //
 403     $res = sqlStatement($query, array($from_date, $to_date));
 404     while ($row = sqlFetchArray($res)) {
 405         thisLineItem($row);
 406     }
 407
 408   // Grand totals line.
 409     if ($form_action != 'export') { // if submit
 410         ?>
 411
 412    <tr bgcolor="#dddddd">
 413     <td class="dehead" colspan="6">
 414         <?php echo xlt('Grand Total'); ?>
 415   </td>
 416   <td class="dehead" align="right">
 417         <?php echo text($grandqty); ?>
 418   </td>
 419   <td class="dehead" align="right">
 420         <?php echo text(FormatMoney::getBucks($grandtotal)); ?>
 421   </td>
 422   <td class="dehead" colspan="2">
 423
 424   </td>
 425  </tr>
 426
 427         <?php
 428     } // End if submit
 429 } // end if submit or export
 430
 431 if ($form_action != 'export') {
 432     if ($form_action) {
 433         ?>
 434    </tbody>
 435   </table>
 436   </div>
 437         <?php
 438     } // end if ($form_action)
 439     ?>
 440
 441 </form>
 442 </center>
 443 </body>
 444 </html>
 445     <?php
 446 } // End not export
 447 ?>