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