interface/reports/sales_by_item.php

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