interface/reports/sales_by_item.php

   1 <?php
   2   // Copyright (C) 2006 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   include_once("../globals.php");
  13   include_once("../../library/patient.inc");
  14   include_once("../../library/sql-ledger.inc");
  15   include_once("../../library/acl.inc");
  16
  17   function bucks($amount) {
  18     if ($amount)
  19       printf("%.2f", $amount);
  20   }
  21
  22   if (! acl_check('acct', 'rep')) die(xl("Unauthorized access."));
  23
  24   SLConnect();
  25
  26   $form_from_date = fixDate($_POST['form_from_date'], date('Y-m-d'));
  27   $form_to_date   = fixDate($_POST['form_to_date']  , date('Y-m-d'));
  28 ?>
  29 <html>
  30 <head>
  31 <? html_header_show();?>
  32 <title><?xl('Sales by Item','e')?></title>
  33 </head>
  34
  35 <body leftmargin='0' topmargin='0' marginwidth='0' marginheight='0'>
  36 <center>
  37
  38 <h2><?xl('Sales by Item','e')?></h2>
  39
  40 <form method='post' action='sales_by_item.php'>
  41
  42 <table border='0' cellpadding='3'>
  43
  44  <tr>
  45   <td>
  46    &nbsp;<?xl('From:','e')?>
  47    <input type='text' name='form_from_date' id="form_from_date" size='10' value='<?php echo $form_from_date ?>'
  48     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
  49    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
  50     id='img_from_date' border='0' alt='[?]' style='cursor:pointer'
  51     title='<?php xl('Click here to choose a date','e'); ?>'>
  52    &nbsp;To:
  53    <input type='text' name='form_to_date' id="form_to_date" size='10' value='<?php echo $form_to_date ?>'
  54     onkeyup='datekeyup(this,mypcc)' onblur='dateblur(this,mypcc)' title='yyyy-mm-dd'>
  55    <img src='../pic/show_calendar.gif' align='absbottom' width='24' height='22'
  56     id='img_to_date' border='0' alt='[?]' style='cursor:pointer'
  57     title='<?php xl('Click here to choose a date','e'); ?>'>
  58    &nbsp;
  59    <input type='checkbox' name='form_details' value='1'<? if ($_POST['form_details']) echo " checked"; ?>><?xl('Details','e')?>
  60    &nbsp;
  61    <input type='submit' name='form_refresh' value="<?xl('Refresh','e')?>">
  62    &nbsp;
  63    <input type='button' value='<?php xl('Print','e'); ?>' onclick='window.print()' />
  64   </td>
  65  </tr>
  66
  67  <tr>
  68   <td height="1">
  69   </td>
  70  </tr>
  71
  72 </table>
  73
  74 <table border='0' cellpadding='1' cellspacing='2' width='98%'>
  75
  76  <tr bgcolor="#dddddd">
  77   <td class="dehead">
  78    <?xl('Item','e')?>
  79   </td>
  80   <td class="dehead">
  81    <?xl('Date','e')?>
  82   </td>
  83   <td class="dehead">
  84    <?xl('Invoice','e')?>
  85   </td>
  86   <td class="dehead" align="right">
  87    <?xl('Qty','e')?>
  88   </td>
  89   <td class="dehead" align="right">
  90    <?xl('Amount','e')?>
  91   </td>
  92  </tr>
  93 <?
  94   if ($_POST['form_refresh']) {
  95     $from_date = $form_from_date;
  96     $to_date   = $form_to_date;
  97
  98     $query = "SELECT ar.invnumber, ar.transdate, " .
  99       "invoice.description, invoice.qty, invoice.sellprice " .
 100       "FROM ar, invoice WHERE " .
 101       "ar.transdate >= '$from_date' AND ar.transdate <= '$to_date' " .
 102       "AND invoice.trans_id = ar.id " .
 103       "ORDER BY invoice.description, ar.transdate, ar.id";
 104
 105     // echo "<!-- $query -->\n"; // debugging
 106
 107     $t_res = SLQuery($query);
 108     if ($sl_err) die($sl_err);
 109
 110     $product = "";
 111     $productleft = "";
 112     $producttotal = 0;
 113     $grandtotal = 0;
 114     $productqty = 0;
 115     $grandqty = 0;
 116
 117     for ($irow = 0; $irow < SLRowCount($t_res); ++$irow) {
 118       $row = SLGetRow($t_res, $irow);
 119       // $rowamount = $row['fxsellprice'];
 120       $rowamount = sprintf('%01.2f', $row['sellprice'] * $row['qty']);
 121
 122       // Extract only the first word as the payment method because any following
 123       // text will be some petty detail like a check number.
 124       $rowproduct = $row['description'];
 125       if (! $rowproduct) $rowproduct = 'Unknown';
 126
 127       if ($product != $rowproduct) {
 128         if ($product) {
 129           // Print product total.
 130 ?>
 131
 132  <tr bgcolor="#ddddff">
 133   <td class="detail" colspan="3">
 134    <? echo xl('Total for ') . $product ?>
 135   </td>
 136   <td class="dehead" align="right">
 137    <?php echo $productqty; ?>
 138   </td>
 139   <td class="dehead" align="right">
 140    <?php bucks($producttotal); ?>
 141   </td>
 142  </tr>
 143 <?
 144         }
 145         $producttotal = 0;
 146         $productqty = 0;
 147         $product = $rowproduct;
 148         $productleft = $product;
 149       }
 150
 151       if ($_POST['form_details']) {
 152 ?>
 153
 154  <tr>
 155   <td class="detail">
 156    <?php echo $productleft; $productleft = "&nbsp;"; ?>
 157   </td>
 158   <td class="dehead">
 159    <?php echo $row['transdate']; ?>
 160   </td>
 161   <td class="detail">
 162    <?php echo $row['invnumber']; ?>
 163   </td>
 164   <td class="dehead" align="right">
 165    <?php echo $row['qty']; ?>
 166   </td>
 167   <td class="dehead" align="right">
 168    <?php bucks($rowamount); ?>
 169   </td>
 170  </tr>
 171 <?
 172       }
 173       $producttotal += $rowamount;
 174       $grandtotal   += $rowamount;
 175       $productqty   += $row['qty'];
 176       $grandqty     += $row['qty'];
 177     }
 178 ?>
 179
 180  <tr bgcolor="#ddddff">
 181   <td class="detail" colspan="3">
 182    <?echo xl('Total for ') . $product ?>
 183   </td>
 184   <td class="dehead" align="right">
 185    <?php echo $productqty; ?>
 186   </td>
 187   <td class="dehead" align="right">
 188    <?php bucks($producttotal); ?>
 189   </td>
 190  </tr>
 191
 192  <tr bgcolor="#ffdddd">
 193   <td class="detail" colspan="3">
 194    <?php xl('Grand Total','e'); ?>
 195   </td>
 196   <td class="dehead" align="right">
 197    <?php echo $grandqty; ?>
 198   </td>
 199   <td class="dehead" align="right">
 200    <?php bucks($grandtotal); ?>
 201   </td>
 202  </tr>
 203
 204 <?
 205   }
 206   SLClose();
 207 ?>
 208
 209 </table>
 210 </form>
 211 </center>
 212 </body>
 213
 214 <!-- stuff for the popup calendar -->
 215 <style type="text/css">@import url(../../library/dynarch_calendar.css);</style>
 216 <script type="text/javascript" src="../../library/dynarch_calendar.js"></script>
 217 <script type="text/javascript" src="../../library/dynarch_calendar_en.js"></script>
 218 <script type="text/javascript" src="../../library/dynarch_calendar_setup.js"></script>
 219 <script language="Javascript">
 220  Calendar.setup({inputField:"form_from_date", ifFormat:"%Y-%m-%d", button:"img_from_date"});
 221  Calendar.setup({inputField:"form_to_date", ifFormat:"%Y-%m-%d", button:"img_to_date"});
 222 </script>
 223
 224 </html>