interface/reports/sales_by_item.php

   1 <?php
   2 // Copyright (C) 2006-2015 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 a report of sales by item description.  It's driven from
  10 // SQL-Ledger so as to include all types of invoice items.
  11
  12 require_once("../globals.php");
  13 require_once("$srcdir/patient.inc");
  14 require_once("$srcdir/sql-ledger.inc");
  15 require_once("$srcdir/acl.inc");
  16 require_once("$srcdir/formatting.inc.php");
  17 require_once "$srcdir/options.inc.php";
  18 require_once "$srcdir/formdata.inc.php";
  19
  20 function bucks($amount) {
  21   if ($amount) echo oeFormatMoney($amount);
  22 }
  23
  24 function display_desc($desc) {
  25   if (preg_match('/^\S*?:(.+)$/', $desc, $matches)) {
  26     $desc = $matches[1];
  27   }
  28   return $desc;
  29 }
  30
  31 function thisLineItem($patient_id, $encounter_id, $rowcat, $description, $transdate, $qty, $amount, $irnumber='') {
  32   global $product, $category, $producttotal, $productqty, $cattotal, $catqty, $grandtotal, $grandqty;
  33   global $productleft, $catleft;
  34
  35   $invnumber = $irnumber ? $irnumber : "$patient_id.$encounter_id";
  36   $rowamount = sprintf('%01.2f', $amount);
  37
  38   if (empty($rowcat)) $rowcat = 'None';
  39   $rowproduct = $description;
  40   if (! $rowproduct) $rowproduct = 'Unknown';
  41
  42   if ($product != $rowproduct || $category != $rowcat) {
  43     if ($product) {
  44       // Print product total.
  45       if ($_POST['form_csvexport']) {
  46         if (! $_POST['form_details']) {
  47           echo '"' . display_desc($category) . '",';
  48           echo '"' . display_desc($product)  . '",';
  49           echo '"' . $productqty             . '",';
  50           echo '"'; bucks($producttotal); echo '"' . "\n";
  51         }
  52       }
  53       else {
  54 ?>
  55  <tr bgcolor="#ddddff">
  56   <td class="detail">
  57    <?php echo display_desc($catleft); $catleft = "&nbsp;"; ?>
  58   </td>
  59   <td class="detail" colspan="3">
  60    <?php if ($_POST['form_details']) echo xl('Total for') . ' '; echo display_desc($product); ?>
  61   </td>
  62   <td align="right">
  63    <?php echo $productqty; ?>
  64   </td>
  65   <td align="right">
  66    <?php bucks($producttotal); ?>
  67   </td>
  68  </tr>
  69 <?php
  70       } // End not csv export
  71     }
  72     $producttotal = 0;
  73     $productqty = 0;
  74     $product = $rowproduct;
  75     $productleft = $product;
  76   }
  77
  78   if ($category != $rowcat) {
  79     if ($category) {
  80       // Print category total.
  81       if (!$_POST['form_csvexport']) {
  82 ?>
  83
  84  <tr bgcolor="#ffdddd">
  85   <td class="detail">
  86    &nbsp;
  87   </td>
  88   <td class="detail" colspan="3">
  89    <?php echo xl('Total for category') . ' '; echo display_desc($category); ?>
  90   </td>
  91   <td align="right">
  92    <?php echo $catqty; ?>
  93   </td>
  94   <td align="right">
  95    <?php bucks($cattotal); ?>
  96   </td>
  97  </tr>
  98 <?php
  99       } // End not csv export
 100     }
 101     $cattotal = 0;
 102     $catqty = 0;
 103     $category = $rowcat;
 104     $catleft = $category;
 105   }
 106
 107   if ($_POST['form_details']) {
 108     if ($_POST['form_csvexport']) {
 109       echo '"' . display_desc($category ) . '",';
 110       echo '"' . display_desc($product  ) . '",';
 111       echo '"' . oeFormatShortDate(display_desc($transdate)) . '",';
 112       echo '"' . display_desc($invnumber) . '",';
 113       echo '"' . display_desc($qty      ) . '",';
 114       echo '"'; bucks($rowamount); echo '"' . "\n";
 115     }
 116     else {
 117 ?>
 118
 119  <tr>
 120   <td class="detail">
 121    <?php echo display_desc($catleft); $catleft = "&nbsp;"; ?>
 122   </td>
 123   <td class="detail">
 124    <?php echo display_desc($productleft); $productleft = "&nbsp;"; ?>
 125   </td>
 126   <td>
 127    <?php echo oeFormatShortDate($transdate); ?>
 128   </td>
 129   <td class="detail">
 130    <a href='../patient_file/pos_checkout.php?ptid=<?php echo $patient_id; ?>&enc=<?php echo $encounter_id; ?>'>
 131    <?php echo $invnumber; ?></a>
 132   </td>
 133   <td align="right">
 134    <?php echo $qty; ?>
 135   </td>
 136   <td align="right">
 137    <?php bucks($rowamount); ?>
 138   </td>
 139  </tr>
 140 <?php
 141
 142     } // End not csv export
 143   } // end details
 144   $producttotal += $rowamount;
 145   $cattotal     += $rowamount;
 146   $grandtotal   += $rowamount;
 147   $productqty   += $qty;
 148   $catqty       += $qty;
 149   $grandqty     += $qty;
 150 } // end function
 151
 152   if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
 153
 154   $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
 155
 156   if (!$INTEGRATED_AR) SLConnect();
 157
 158   $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
 159   $form_to_date   = fixDate($_POST['form_to_date']  , date('Y-m-d'));
 160   $form_facility  = $_POST['form_facility'];
 161
 162   if ($_POST['form_csvexport']) {
 163     header("Pragma: public");
 164     header("Expires: 0");
 165     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 166     header("Content-Type: application/force-download");
 167     header("Content-Disposition: attachment; filename=sales_by_item.csv");
 168     header("Content-Description: File Transfer");
 169     // CSV headers:
 170     if ($_POST['form_details']) {
 171       echo '"Category",';
 172       echo '"Item",';
 173       echo '"Date",';
 174       echo '"Invoice",';
 175       echo '"Qty",';
 176       echo '"Amount"' . "\n";
 177     }
 178     else {
 179       echo '"Category",';
 180       echo '"Item",';
 181       echo '"Qty",';
 182       echo '"Total"' . "\n";
 183     }
 184   } // end export
 185   else {
 186 ?>
 187 <html>
 188 <head>
 189 <?php html_header_show();?>
 190 <style type="text/css">
 191 /* specifically include & exclude from printing */
 192 @media print {
 193     #report_parameters {
 194         visibility: hidden;
 195         display: none;
 196     }
 197     #report_parameters_daterange {
 198         visibility: visible;
 199         display: inline;
 200     }
 201     #report_results {
 202        margin-top: 30px;
 203     }
 204 }
 205
 206 /* specifically exclude some from the screen */
 207 @media screen {
 208     #report_parameters_daterange {
 209         visibility: hidden;
 210         display: none;
 211     }
 212 }
 213 </style>
 214
 215 <title><?php xl('Sales by Item','e') ?></title>
 216
 217 <script type="text/javascript" src="../../library/js/jquery.1.3.2.js"></script>
 218
 219 <script language="JavaScript">
 220  $(document).ready(function() {
 221   var win = top.printLogSetup ? top : opener.top;
 222   win.printLogSetup(document.getElementById('printbutton'));
 223  });
 224 </script>
 225
 226 </head>
 227
 228 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class="body_top">
 229
 230 <span class='title'><?php xl('Report','e'); ?> - <?php xl('Sales by Item','e'); ?></span>
 231
 232 <form method='post' action='sales_by_item.php' id='theform'>
 233
 234 <div id="report_parameters">
 235 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 236 <input type='hidden' name='form_csvexport' id='form_csvexport' value=''/>
 237 <table>
 238  <tr>
 239   <td width='630px'>
 240         <div style='float:left'>
 241
 242         <table class='text'>
 243                 <tr>
 244                         <td class='label'>
 245                                 <?php xl('Facility','e'); ?>:
 246                         </td>
 247                         <td>
 248                         <?php dropdown_facility(strip_escape_custom($form_facility), 'form_facility', true); ?>
 249                         </td>
 250                         <td class='label'>
 251                            <?php xl('From','e'); ?>:
 252                         </td>
 253                         <td>
 254                            <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $form_from_date ?>'
 255                                 onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 256                            <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 257                                 id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 258                                 title='<?php xl('Click here to choose a date','e'); ?>'>
 259                         </td>
 260                         <td class='label'>
 261                            <?php xl('To','e'); ?>:
 262                         </td>
 263                         <td>
 264                            <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_date ?>'
 265                                 onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 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 xl('Click here to choose a date','e'); ?>'>
 269                         </td>
 270                 </tr>
 271                 <tr>
 272                         <td>&nbsp;</td>
 273                         <td>
 274                            <input type='checkbox' name='form_details'<?php  if ($form_details) echo ' checked'; ?>>
 275                            <?php  xl('Details','e'); ?>
 276                         </td>
 277                 </tr>
 278         </table>
 279
 280         </div>
 281
 282   </td>
 283   <td align='left' valign='middle' height="100%">
 284         <table style='border-left:1px solid; width:100%; height:100%' >
 285                 <tr>
 286                         <td>
 287                                 <div style='margin-left:15px'>
 288                                         <a href='#' class='css_button' onclick='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
 289                                         <span>
 290                                                 <?php xl('Submit','e'); ?>
 291                                         </span>
 292                                         </a>
 293
 294                                         <?php if ($_POST['form_refresh'] || $_POST['form_csvexport']) { ?>
 295                                         <a href='#' class='css_button' id='printbutton'>
 296                                                 <span>
 297                                                         <?php xl('Print','e'); ?>
 298                                                 </span>
 299                                         </a>
 300                                         <a href='#' class='css_button' onclick='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
 301                                                 <span>
 302                                                         <?php xl('CSV Export','e'); ?>
 303                                                 </span>
 304                                         </a>
 305                                         <?php } ?>
 306                                 </div>
 307                         </td>
 308                 </tr>
 309         </table>
 310   </td>
 311  </tr>
 312 </table>
 313
 314 </div> <!-- end of parameters -->
 315
 316 <?php
 317  if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 318 ?>
 319 <div id="report_results">
 320 <table >
 321  <thead>
 322   <th>
 323    <?php xl('Category','e'); ?>
 324   </th>
 325   <th>
 326    <?php xl('Item','e'); ?>
 327   </th>
 328   <th>
 329    <?php xl('Date','e'); ?>
 330   </th>
 331   <th>
 332    <?php xl('Invoice','e'); ?>
 333   </th>
 334   <th align="right">
 335    <?php xl('Qty','e'); ?>
 336   </th>
 337   <th align="right">
 338    <?php xl('Amount','e'); ?>
 339   </th>
 340  </thead>
 341 <?php
 342   } // end not export
 343 }
 344
 345   if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 346     $from_date = $form_from_date;
 347     $to_date   = $form_to_date;
 348
 349     $category = "";
 350     $catleft = "";
 351     $cattotal = 0;
 352     $catqty = 0;
 353     $product = "";
 354     $productleft = "";
 355     $producttotal = 0;
 356     $productqty = 0;
 357     $grandtotal = 0;
 358     $grandqty = 0;
 359
 360     if ($INTEGRATED_AR) {
 361       $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.units, " .
 362         "b.code_text, fe.date, fe.facility_id, fe.invoice_refno, lo.title " .
 363         "FROM billing AS b " .
 364         "JOIN code_types AS ct ON ct.ct_key = b.code_type " .
 365         "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 366         "LEFT JOIN codes AS c ON c.code_type = ct.ct_id AND c.code = b.code AND c.modifier = b.modifier " .
 367         "LEFT JOIN list_options AS lo ON lo.list_id = 'superbill' AND lo.option_id = c.superbill " .
 368         "WHERE b.code_type != 'COPAY' AND b.activity = 1 AND b.fee != 0 AND " .
 369         "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59'";
 370       // If a facility was specified.
 371       if ($form_facility) {
 372         $query .= " AND fe.facility_id = '$form_facility'";
 373       }
 374       $query .= " ORDER BY lo.title, b.code, fe.date, fe.id";
 375       //
 376       $res = sqlStatement($query);
 377       while ($row = sqlFetchArray($res)) {
 378         thisLineItem($row['pid'], $row['encounter'],
 379           $row['title'], $row['code'] . ' ' . $row['code_text'],
 380           substr($row['date'], 0, 10), $row['units'], $row['fee'], $row['invoice_refno']);
 381       }
 382       //
 383       $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
 384         "d.name, fe.date, fe.facility_id, fe.invoice_refno " .
 385         "FROM drug_sales AS s " .
 386         "JOIN drugs AS d ON d.drug_id = s.drug_id " .
 387         "JOIN form_encounter AS fe ON " .
 388         "fe.pid = s.pid AND fe.encounter = s.encounter AND " .
 389         "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59' " .
 390         "WHERE s.fee != 0";
 391       // If a facility was specified.
 392       if ($form_facility) {
 393         $query .= " AND fe.facility_id = '$form_facility'";
 394       }
 395       $query .= " ORDER BY d.name, fe.date, fe.id";
 396       //
 397       $res = sqlStatement($query);
 398       while ($row = sqlFetchArray($res)) {
 399         thisLineItem($row['pid'], $row['encounter'], xl('Products'), $row['name'],
 400           substr($row['date'], 0, 10), $row['quantity'], $row['fee'], $row['invoice_refno']);
 401       }
 402     }
 403     else {
 404       $query = "SELECT ar.invnumber, ar.transdate, " .
 405         "invoice.description, invoice.qty, invoice.sellprice " .
 406         "FROM ar, invoice WHERE " .
 407         "ar.transdate >= '$from_date' AND ar.transdate <= '$to_date' " .
 408         "AND invoice.trans_id = ar.id " .
 409         "ORDER BY invoice.description, ar.transdate, ar.id";
 410       $t_res = SLQuery($query);
 411       if ($sl_err) die($sl_err);
 412       for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 413         $row = SLGetRow($t_res, $irow);
 414         list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 415         // If a facility was specified then skip invoices whose encounters
 416         // do not indicate that facility.
 417         if ($form_facility) {
 418           $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
 419             "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 420             "facility_id = '$form_facility'");
 421           if (empty($tmp['count'])) continue;
 422         }
 423         thisLineItem($patient_id, $encounter_id, '', $row['description'],
 424           $row['transdate'], $row['qty'], $row['sellprice'] * $row['qty']);
 425       } // end for
 426     } // end not $INTEGRATED_AR
 427
 428     if ($_POST['form_csvexport']) {
 429       if (! $_POST['form_details']) {
 430         echo '"' . display_desc($product) . '",';
 431         echo '"' . $productqty            . '",';
 432         echo '"'; bucks($producttotal); echo '"' . "\n";
 433       }
 434     }
 435     else {
 436 ?>
 437
 438  <tr bgcolor="#ddddff">
 439   <td class="detail">
 440    <?php echo display_desc($catleft); $catleft = "&nbsp;"; ?>
 441   </td>
 442   <td class="detail" colspan="3">
 443    <?php if ($_POST['form_details']) echo xl('Total for') . ' '; echo display_desc($product); ?>
 444   </td>
 445   <td align="right">
 446    <?php echo $productqty; ?>
 447   </td>
 448   <td align="right">
 449    <?php bucks($producttotal); ?>
 450   </td>
 451  </tr>
 452
 453  <tr bgcolor="#ffdddd">
 454   <td class="detail">
 455    &nbsp;
 456   </td>
 457   <td class="detail" colspan="3">
 458    <?php echo xl('Total for category') . ' '; echo display_desc($category); ?>
 459   </td>
 460   <td align="right">
 461    <?php echo $catqty; ?>
 462   </td>
 463   <td align="right">
 464    <?php bucks($cattotal); ?>
 465   </td>
 466  </tr>
 467
 468  <tr>
 469   <td class="detail" colspan="4">
 470    <?php xl('Grand Total','e'); ?>
 471   </td>
 472   <td align="right">
 473    <?php echo $grandqty; ?>
 474   </td>
 475   <td align="right">
 476    <?php bucks($grandtotal); ?>
 477   </td>
 478  </tr>
 479
 480 <?php
 481
 482     } // End not csv export
 483   }
 484   if (!$INTEGRATED_AR) SLClose();
 485
 486   if (! $_POST['form_csvexport']) {
 487       if($_POST['form_refresh']){
 488 ?>
 489
 490 </table>
 491 </div> <!-- report results -->
 492 <?php } else { ?>
 493 <div class='text'>
 494         <?php echo xl('Please input search criteria above, and click Submit to view results.', 'e' ); ?>
 495 </div>
 496 <?php } ?>
 497
 498 </form>
 499
 500 </body>
 501
 502 <!-- stuff for the popup calendar -->
 503 <link rel='stylesheet' href='<?php echo $css_header ?>' type='text/css'>
 504 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 505 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 506 <?php include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?>
 507 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 508
 509 <script language="Javascript">
 510  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 511  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 512 </script>
 513
 514 </html>
 515 <?php
 516   } // End not csv export
 517 ?>