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