interface/reports/sales_by_item.php

   1 <?php
   2 // Copyright (C) 2006-2009 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("../../library/patient.inc");
  14 require_once("../../library/sql-ledger.inc");
  15 require_once("../../library/acl.inc");
  16
  17 function bucks($amount) {
  18   if ($amount)
  19     printf("%.2f", $amount);
  20 }
  21
  22 function display_desc($desc) {
  23   if (preg_match('/^\S*?:(.+)$/', $desc, $matches)) {
  24     $desc = $matches[1];
  25   }
  26   return $desc;
  27 }
  28
  29 function thisLineItem($patient_id, $encounter_id, $rowcat, $description, $transdate, $qty, $amount) {
  30   global $product, $category, $producttotal, $productqty, $cattotal, $catqty, $grandtotal, $grandqty;
  31   global $productleft, $catleft;
  32
  33   $invnumber = "$patient_id.$encounter_id";
  34   $rowamount = sprintf('%01.2f', $amount);
  35
  36   if (empty($rowcat)) $rowcat = 'None';
  37   $rowproduct = $description;
  38   if (! $rowproduct) $rowproduct = 'Unknown';
  39
  40   if ($product != $rowproduct || $category != $rowcat) {
  41     if ($product) {
  42       // Print product total.
  43       if ($_POST['form_csvexport']) {
  44         if (! $_POST['form_details']) {
  45           echo '"' . display_desc($category) . '",';
  46           echo '"' . display_desc($product)  . '",';
  47           echo '"' . $productqty             . '",';
  48           echo '"'; bucks($producttotal); echo '"' . "\n";
  49         }
  50       }
  51       else {
  52 ?>
  53  <tr bgcolor="#ddddff">
  54   <td class="detail">
  55    <?php echo display_desc($catleft); $catleft = "&nbsp;"; ?>
  56   </td>
  57   <td class="detail" colspan="3">
  58    <?php if ($_POST['form_details']) echo xl('Total for') . ' '; echo display_desc($product); ?>
  59   </td>
  60   <td class="dehead" align="right">
  61    <?php echo $productqty; ?>
  62   </td>
  63   <td class="dehead" align="right">
  64    <?php bucks($producttotal); ?>
  65   </td>
  66  </tr>
  67 <?php
  68       } // End not csv export
  69     }
  70     $producttotal = 0;
  71     $productqty = 0;
  72     $product = $rowproduct;
  73     $productleft = $product;
  74   }
  75
  76   if ($category != $rowcat) {
  77     if ($category) {
  78       // Print category total.
  79       if (!$_POST['form_csvexport']) {
  80 ?>
  81
  82  <tr bgcolor="#ffdddd">
  83   <td class="detail">
  84    &nbsp;
  85   </td>
  86   <td class="detail" colspan="3">
  87    <?php echo xl('Total for category') . ' '; echo display_desc($category); ?>
  88   </td>
  89   <td class="dehead" align="right">
  90    <?php echo $catqty; ?>
  91   </td>
  92   <td class="dehead" align="right">
  93    <?php bucks($cattotal); ?>
  94   </td>
  95  </tr>
  96 <?php
  97       } // End not csv export
  98     }
  99     $cattotal = 0;
 100     $catqty = 0;
 101     $category = $rowcat;
 102     $catleft = $category;
 103   }
 104
 105   if ($_POST['form_details']) {
 106     if ($_POST['form_csvexport']) {
 107       echo '"' . display_desc($category ) . '",';
 108       echo '"' . display_desc($product  ) . '",';
 109       echo '"' . display_desc($transdate) . '",';
 110       echo '"' . display_desc($invnumber) . '",';
 111       echo '"' . display_desc($qty      ) . '",';
 112       echo '"'; bucks($rowamount); echo '"' . "\n";
 113     }
 114     else {
 115 ?>
 116
 117  <tr>
 118   <td class="detail">
 119    <?php echo display_desc($catleft); $catleft = "&nbsp;"; ?>
 120   </td>
 121   <td class="detail">
 122    <?php echo display_desc($productleft); $productleft = "&nbsp;"; ?>
 123   </td>
 124   <td class="dehead">
 125    <?php echo $transdate; ?>
 126   </td>
 127   <td class="detail">
 128    <?php echo $invnumber; ?>
 129   </td>
 130   <td class="dehead" align="right">
 131    <?php echo $qty; ?>
 132   </td>
 133   <td class="dehead" align="right">
 134    <?php bucks($rowamount); ?>
 135   </td>
 136  </tr>
 137 <?
 138     } // End not csv export
 139   } // end details
 140   $producttotal += $rowamount;
 141   $cattotal     += $rowamount;
 142   $grandtotal   += $rowamount;
 143   $productqty   += $qty;
 144   $catqty       += $qty;
 145   $grandqty     += $qty;
 146 } // end function
 147
 148   if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
 149
 150   $INTEGRATED_AR = $GLOBALS['oer_config']['ws_accounting']['enabled'] === 2;
 151
 152   if (!$INTEGRATED_AR) SLConnect();
 153
 154   $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
 155   $form_to_date   = fixDate($_POST['form_to_date']  , date('Y-m-d'));
 156   $form_facility  = $_POST['form_facility'];
 157
 158   if ($_POST['form_csvexport']) {
 159     header("Pragma: public");
 160     header("Expires: 0");
 161     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
 162     header("Content-Type: application/force-download");
 163     header("Content-Disposition: attachment; filename=sales_by_item.csv");
 164     header("Content-Description: File Transfer");
 165     // CSV headers:
 166     if ($_POST['form_details']) {
 167       echo '"Category",';
 168       echo '"Item",';
 169       echo '"Date",';
 170       echo '"Invoice",';
 171       echo '"Qty",';
 172       echo '"Amount"' . "\n";
 173     }
 174     else {
 175       echo '"Category",';
 176       echo '"Item",';
 177       echo '"Qty",';
 178       echo '"Total"' . "\n";
 179     }
 180   } // end export
 181   else {
 182 ?>
 183 <html>
 184 <head>
 185 <?php html_header_show();?>
 186 <title><?php xl('Sales by Item','e') ?></title>
 187 </head>
 188
 189 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
 190 <center>
 191
 192 <h2><?php xl('Sales by Item','e')?></h2>
 193
 194 <form method='post' action='sales_by_item.php'>
 195
 196 <table border='0' cellpadding='3'>
 197
 198  <tr>
 199   <td>
 200 <?php
 201   // Build a drop-down list of facilities.
 202   //
 203   $query = "SELECT id, name FROM facility ORDER BY name";
 204   $fres = sqlStatement($query);
 205   echo "   <select name='form_facility'>\n";
 206   echo "    <option value=''>-- " . xl('All Facilities') . " --\n";
 207   while ($frow = sqlFetchArray($fres)) {
 208     $facid = $frow['id'];
 209     echo "    <option value='$facid'";
 210     if ($facid == $form_facility) echo " selected";
 211     echo ">" . $frow['name'] . "\n";
 212   }
 213   echo "   </select>\n";
 214 ?>
 215    &nbsp;<?php xl('From:','e'); ?>
 216    <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $form_from_date ?>'
 217     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 218    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 219     id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
 220     title='<?php xl('Click here to choose a date','e'); ?>'>
 221    &nbsp;<?php xl('To:','e'); ?>:
 222    <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_date ?>'
 223     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
 224    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
 225     id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
 226     title='<?php xl('Click here to choose a date','e'); ?>'>
 227    &nbsp;
 228    <input type='checkbox' name='form_details' value='1'<?php if ($_POST['form_details']) echo " checked"; ?>><?php xl('Details','e') ?>
 229    &nbsp;
 230    <input type='submit' name='form_refresh' value="<?php xl('Refresh','e') ?>">
 231    &nbsp;
 232    <input type='submit' name='form_csvexport' value="<?php xl('Export to CSV','e') ?>">
 233    &nbsp;
 234    <input type='button' value='<?php xl('Print','e'); ?>' onclick='window.print()' />
 235   </td>
 236  </tr>
 237
 238  <tr>
 239   <td height="1">
 240   </td>
 241  </tr>
 242
 243 </table>
 244
 245 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
 246
 247  <tr bgcolor="#dddddd">
 248   <td class="dehead">
 249    <?php xl('Category','e'); ?>
 250   </td>
 251   <td class="dehead">
 252    <?php xl('Item','e'); ?>
 253   </td>
 254   <td class="dehead">
 255    <?php xl('Date','e'); ?>
 256   </td>
 257   <td class="dehead">
 258    <?php xl('Invoice','e'); ?>
 259   </td>
 260   <td class="dehead" align="right">
 261    <?php xl('Qty','e'); ?>
 262   </td>
 263   <td class="dehead" align="right">
 264    <?php xl('Amount','e'); ?>
 265   </td>
 266  </tr>
 267 <?php
 268   } // end not export
 269
 270   if ($_POST['form_refresh'] || $_POST['form_csvexport']) {
 271     $from_date = $form_from_date;
 272     $to_date   = $form_to_date;
 273
 274     $category = "";
 275     $catleft = "";
 276     $cattotal = 0;
 277     $catqty = 0;
 278     $product = "";
 279     $productleft = "";
 280     $producttotal = 0;
 281     $productqty = 0;
 282     $grandtotal = 0;
 283     $grandqty = 0;
 284
 285     if ($INTEGRATED_AR) {
 286       $query = "SELECT b.fee, b.pid, b.encounter, b.code_type, b.code, b.units, " .
 287         "b.code_text, fe.date, fe.facility_id, lo.title " .
 288         "FROM billing AS b " .
 289         "JOIN form_encounter AS fe ON fe.pid = b.pid AND fe.encounter = b.encounter " .
 290         "LEFT JOIN codes AS c ON c.code = b.code AND c.modifier = b.modifier " .
 291         "LEFT JOIN list_options AS lo ON lo.list_id = 'superbill' AND lo.option_id = c.superbill " .
 292         "WHERE b.code_type != 'COPAY' AND b.activity = 1 AND b.fee != 0 AND " .
 293         "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59'";
 294       // If a facility was specified.
 295       if ($form_facility) {
 296         $query .= " AND fe.facility_id = '$form_facility'";
 297       }
 298       $query .= " ORDER BY lo.title, b.code, fe.date, fe.id";
 299       //
 300       $res = sqlStatement($query);
 301       while ($row = sqlFetchArray($res)) {
 302         thisLineItem($row['pid'], $row['encounter'],
 303           $row['title'], $row['code'] . ' ' . $row['code_text'],
 304           substr($row['date'], 0, 10), $row['units'], $row['fee']);
 305       }
 306       //
 307       $query = "SELECT s.sale_date, s.fee, s.quantity, s.pid, s.encounter, " .
 308         "d.name, fe.date, fe.facility_id " .
 309         "FROM drug_sales AS s " .
 310         "JOIN drugs AS d ON d.drug_id = s.drug_id " .
 311         "JOIN form_encounter AS fe ON " .
 312         "fe.pid = s.pid AND fe.encounter = s.encounter AND " .
 313         "fe.date >= '$from_date 00:00:00' AND fe.date <= '$to_date 23:59:59' " .
 314         "WHERE s.fee != 0";
 315       // If a facility was specified.
 316       if ($form_facility) {
 317         $query .= " AND fe.facility_id = '$form_facility'";
 318       }
 319       $query .= " ORDER BY d.name, fe.date, fe.id";
 320       //
 321       $res = sqlStatement($query);
 322       while ($row = sqlFetchArray($res)) {
 323         thisLineItem($row['pid'], $row['encounter'], xl('Products'), $row['name'],
 324           substr($row['date'], 0, 10), $row['quantity'], $row['fee']);
 325       }
 326     }
 327     else {
 328       $query = "SELECT ar.invnumber, ar.transdate, " .
 329         "invoice.description, invoice.qty, invoice.sellprice " .
 330         "FROM ar, invoice WHERE " .
 331         "ar.transdate >= '$from_date' AND ar.transdate <= '$to_date' " .
 332         "AND invoice.trans_id = ar.id " .
 333         "ORDER BY invoice.description, ar.transdate, ar.id";
 334       $t_res = SLQuery($query);
 335       if ($sl_err) die($sl_err);
 336       for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 337         $row = SLGetRow($t_res, $irow);
 338         list($patient_id, $encounter_id) = explode(".", $row['invnumber']);
 339         // If a facility was specified then skip invoices whose encounters
 340         // do not indicate that facility.
 341         if ($form_facility) {
 342           $tmp = sqlQuery("SELECT count(*) AS count FROM form_encounter WHERE " .
 343             "pid = '$patient_id' AND encounter = '$encounter_id' AND " .
 344             "facility_id = '$form_facility'");
 345           if (empty($tmp['count'])) continue;
 346         }
 347         thisLineItem($patient_id, $encounter_id, '', $row['description'],
 348           $row['transdate'], $row['qty'], $row['sellprice'] * $row['qty']);
 349       } // end for
 350     } // end not $INTEGRATED_AR
 351
 352     if ($_POST['form_csvexport']) {
 353       if (! $_POST['form_details']) {
 354         echo '"' . display_desc($product) . '",';
 355         echo '"' . $productqty            . '",';
 356         echo '"'; bucks($producttotal); echo '"' . "\n";
 357       }
 358     }
 359     else {
 360 ?>
 361
 362  <tr bgcolor="#ddddff">
 363   <td class="detail">
 364    <?php echo display_desc($catleft); $catleft = "&nbsp;"; ?>
 365   </td>
 366   <td class="detail" colspan="3">
 367    <?php if ($_POST['form_details']) echo xl('Total for') . ' '; echo display_desc($product); ?>
 368   </td>
 369   <td class="dehead" align="right">
 370    <?php echo $productqty; ?>
 371   </td>
 372   <td class="dehead" align="right">
 373    <?php bucks($producttotal); ?>
 374   </td>
 375  </tr>
 376
 377  <tr bgcolor="#ffdddd">
 378   <td class="detail">
 379    &nbsp;
 380   </td>
 381   <td class="detail" colspan="3">
 382    <?php echo xl('Total for category') . ' '; echo display_desc($category); ?>
 383   </td>
 384   <td class="dehead" align="right">
 385    <?php echo $catqty; ?>
 386   </td>
 387   <td class="dehead" align="right">
 388    <?php bucks($cattotal); ?>
 389   </td>
 390  </tr>
 391
 392  <tr bgcolor="#dddddd">
 393   <td class="detail" colspan="4">
 394    <?php xl('Grand Total','e'); ?>
 395   </td>
 396   <td class="dehead" align="right">
 397    <?php echo $grandqty; ?>
 398   </td>
 399   <td class="dehead" align="right">
 400    <?php bucks($grandtotal); ?>
 401   </td>
 402  </tr>
 403
 404 <?
 405     } // End not csv export
 406   }
 407   if (!$INTEGRATED_AR) SLClose();
 408
 409   if (! $_POST['form_csvexport']) {
 410 ?>
 411
 412 </table>
 413 </form>
 414 </center>
 415 </body>
 416
 417 <!-- stuff for the popup calendar -->
 418 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 419 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 420 <script type="text/javascript" src="../../library/dynarch_calendar_en.js"></script>
 421 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 422 <script language="Javascript">
 423  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 424  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 425 </script>
 426
 427 </html>
 428 <?php
 429   } // End not csv export
 430 ?>