interface/reports/sales_by_item.php

   1 <?php
   2 // Copyright (C) 2006-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 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 </head>
 217
 218 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0' class="body_top">
 219
 220 <span class='title'><?php xl('Report','e'); ?> - <?php xl('Sales by Item','e'); ?></span>
 221
 222 <form method='post' action='sales_by_item.php' id='theform'>
 223
 224 <div id="report_parameters">
 225 <input type='hidden' name='form_refresh' id='form_refresh' value=''/>
 226 <input type='hidden' name='form_csvexport' id='form_csvexport' value=''/>
 227 <table>
 228  <tr>
 229   <td width='630px'>
 230         <div style='float:left'>
 231
 232         <table class='text'>
 233                 <tr>
 234                         <td class='label'>
 235                                 <?php xl('Facility','e'); ?>:
 236                         </td>
 237                         <td>
 238                         <?php dropdown_facility(strip_escape_custom($form_facility), 'form_facility', true); ?>
 239                         </td>
 240                         <td class='label'>
 241                            <?php xl('From','e'); ?>:
 242                         </td>
 243                         <td>
 244                            <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $form_from_date ?>'
 245                                 onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 246                            <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 247                                 id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 248                                 title='<?php xl('Click here to choose a date','e'); ?>'>
 249                         </td>
 250                         <td class='label'>
 251                            <?php xl('To','e'); ?>:
 252                         </td>
 253                         <td>
 254                            <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_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_to_date' border='0' alt='[?]' style='cursor:pointer'
 258                                 title='<?php xl('Click here to choose a date','e'); ?>'>
 259                         </td>
 260                 </tr>
 261                 <tr>
 262                         <td>&nbsp;</td>
 263                         <td>
 264                            <input type='checkbox' name='form_details'<?php  if ($form_details) echo ' checked'; ?>>
 265                            <?php  xl('Details','e'); ?>
 266                         </td>
 267                 </tr>
 268         </table>
 269
 270         </div>
 271
 272   </td>
 273   <td align='left' valign='middle' height="100%">
 274         <table style='border-left:1px solid; width:100%; height:100%' >
 275                 <tr>
 276                         <td>
 277                                 <div style='margin-left:15px'>
 278                                         <a href='#' class='css_button' onclick='$("#form_refresh").attr("value","true"); $("#form_csvexport").attr("value",""); $("#theform").submit();'>
 279                                         <span>
 280                                                 <?php xl('Submit','e'); ?>
 281                                         </span>
 282                                         </a>
 283
 284                                         <?php if ($_POST['form_refresh'] || $_POST['form_csvexport']) { ?>
 285                                         <a href='#' class='css_button' onclick='window.print()'>
 286                                                 <span>
 287                                                         <?php xl('Print','e'); ?>
 288                                                 </span>
 289                                         </a>
 290                                         <a href='#' class='css_button' onclick='$("#form_refresh").attr("value",""); $("#form_csvexport").attr("value","true"); $("#theform").submit();'>
 291                                                 <span>
 292                                                         <?php xl('CSV Export','e'); ?>
 293                                                 </span>
 294                                         </a>
 295                                         <?php } ?>
 296                                 </div>
 297                         </td>
 298                 </tr>
 299         </table>
 300   </td>
 301  </tr>
 302 </table>
 303
 304 </div> <!-- end of parameters -->
 305
 306 <?php
 307  if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 308 ?>
 309 <div id="report_results">
 310 <table >
 311  <thead>
 312   <th>
 313    <?php xl('Category','e'); ?>
 314   </th>
 315   <th>
 316    <?php xl('Item','e'); ?>
 317   </th>
 318   <th>
 319    <?php xl('Date','e'); ?>
 320   </th>
 321   <th>
 322    <?php xl('Invoice','e'); ?>
 323   </th>
 324   <th align="right">
 325    <?php xl('Qty','e'); ?>
 326   </th>
 327   <th align="right">
 328    <?php xl('Amount','e'); ?>
 329   </th>
 330  </thead>
 331 <?php
 332   } // end not export
 333
 334   if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 335     $from_date = $form_from_date;
 336     $to_date   = $form_to_date;
 337
 338     $category = "";
 339     $catleft = "";
 340     $cattotal = 0;
 341     $catqty = 0;
 342     $product = "";
 343     $productleft = "";
 344     $producttotal = 0;
 345     $productqty = 0;
 346     $grandtotal = 0;
 347     $grandqty = 0;
 348
 349     if ($INTEGRATED_AR) {
 350       $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.units, " .
 351         "b.code_text, fe.date, fe.facility_id, fe.invoice_refno, lo.title " .
 352         "FROM billing AS b " .
 353         "JOIN code_types AS ct ON ct.ct_key = b.code_type " .
 354         "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 355         "LEFT JOIN codes AS c ON c.code_type = ct.ct_id AND c.code = b.code AND c.modifier = b.modifier " .
 356         "LEFT JOIN list_options AS lo ON lo.list_id = 'superbill' AND lo.option_id = c.superbill " .
 357         "WHERE b.code_type != 'COPAY' AND b.activity = 1 AND b.fee != 0 AND " .
 358         "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59'";
 359       // If a facility was specified.
 360       if ($form_facility) {
 361         $query .= " AND fe.facility_id = '$form_facility'";
 362       }
 363       $query .= " ORDER BY lo.title, b.code, fe.date, fe.id";
 364       //
 365       $res = sqlStatement($query);
 366       while ($row = sqlFetchArray($res)) {
 367         thisLineItem($row['pid'], $row['encounter'],
 368           $row['title'], $row['code'] . ' ' . $row['code_text'],
 369           substr($row['date'], 0, 10), $row['units'], $row['fee'], $row['invoice_refno']);
 370       }
 371       //
 372       $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
 373         "d.name, fe.date, fe.facility_id, fe.invoice_refno " .
 374         "FROM drug_sales AS s " .
 375         "JOIN drugs AS d ON d.drug_id = s.drug_id " .
 376         "JOIN form_encounter AS fe ON " .
 377         "fe.pid = s.pid AND fe.encounter = s.encounter AND " .
 378         "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59' " .
 379         "WHERE s.fee != 0";
 380       // If a facility was specified.
 381       if ($form_facility) {
 382         $query .= " AND fe.facility_id = '$form_facility'";
 383       }
 384       $query .= " ORDER BY d.name, fe.date, fe.id";
 385       //
 386       $res = sqlStatement($query);
 387       while ($row = sqlFetchArray($res)) {
 388         thisLineItem($row['pid'], $row['encounter'], xl('Products'), $row['name'],
 389           substr($row['date'], 0, 10), $row['quantity'], $row['fee'], $row['invoice_refno']);
 390       }
 391     }
 392     else {
 393       $query = "SELECT ar.invnumber, ar.transdate, " .
 394         "invoice.description, invoice.qty, invoice.sellprice " .
 395         "FROM ar, invoice WHERE " .
 396         "ar.transdate >= '$from_date' AND ar.transdate <= '$to_date' " .
 397         "AND invoice.trans_id = ar.id " .
 398         "ORDER BY invoice.description, ar.transdate, ar.id";
 399       $t_res = SLQuery($query);
 400       if ($sl_err) die($sl_err);
 401       for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 402         $row = SLGetRow($t_res, $irow);
 403         list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 404         // If a facility was specified then skip invoices whose encounters
 405         // do not indicate that facility.
 406         if ($form_facility) {
 407           $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
 408             "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 409             "facility_id = '$form_facility'");
 410           if (empty($tmp['count'])) continue;
 411         }
 412         thisLineItem($patient_id, $encounter_id, '', $row['description'],
 413           $row['transdate'], $row['qty'], $row['sellprice'] * $row['qty']);
 414       } // end for
 415     } // end not $INTEGRATED_AR
 416
 417     if ($_POST['form_csvexport']) {
 418       if (! $_POST['form_details']) {
 419         echo '"' . display_desc($product) . '",';
 420         echo '"' . $productqty            . '",';
 421         echo '"'; bucks($producttotal); echo '"' . "\n";
 422       }
 423     }
 424     else {
 425 ?>
 426
 427  <tr bgcolor="#ddddff">
 428   <td class="detail">
 429    <?php echo display_desc($catleft); $catleft = "&nbsp;"; ?>
 430   </td>
 431   <td class="detail" colspan="3">
 432    <?php if ($_POST['form_details']) echo xl('Total for') . ' '; echo display_desc($product); ?>
 433   </td>
 434   <td align="right">
 435    <?php echo $productqty; ?>
 436   </td>
 437   <td align="right">
 438    <?php bucks($producttotal); ?>
 439   </td>
 440  </tr>
 441
 442  <tr bgcolor="#ffdddd">
 443   <td class="detail">
 444    &nbsp;
 445   </td>
 446   <td class="detail" colspan="3">
 447    <?php echo xl('Total for category') . ' '; echo display_desc($category); ?>
 448   </td>
 449   <td align="right">
 450    <?php echo $catqty; ?>
 451   </td>
 452   <td align="right">
 453    <?php bucks($cattotal); ?>
 454   </td>
 455  </tr>
 456
 457  <tr>
 458   <td class="detail" colspan="4">
 459    <?php xl('Grand Total','e'); ?>
 460   </td>
 461   <td align="right">
 462    <?php echo $grandqty; ?>
 463   </td>
 464   <td align="right">
 465    <?php bucks($grandtotal); ?>
 466   </td>
 467  </tr>
 468
 469 <?php
 470
 471     } // End not csv export
 472   }
 473   if (!$INTEGRATED_AR) SLClose();
 474
 475   if (! $_POST['form_csvexport']) {
 476 ?>
 477
 478 </table>
 479 </div> <!-- report results -->
 480 <?php } else { ?>
 481 <div class='text'>
 482         <?php echo xl('Please input search criteria above, and click Submit to view results.', 'e' ); ?>
 483 </div>
 484 <?php } ?>
 485
 486 </form>
 487
 488 </body>
 489
 490 <!-- stuff for the popup calendar -->
 491 <link rel='stylesheet' href='<?php echo $css_header ?>' type='text/css'>
 492 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 493 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 494 <?php include_once("{$GLOBALS['srcdir']}/dynarch_calendar_en.inc.php"); ?>
 495 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 496 <script type="text/javascript" src="../../library/js/jquery.1.3.2.js"></script>
 497
 498 <script language="Javascript">
 499  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 500  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 501 </script>
 502
 503 </html>
 504 <?php
 505   } // End not csv export
 506 ?>