fannie/reports/Store-Specific/WEFC_Toronto/OntarioTaxes/index.php
<?php
/*******************************************************************************
Copyright 2009 Whole Foods Co-op
This file is part of CORE-POS.
CORE-POS is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
CORE-POS is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
/* --COMMENTS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* 27Nov13 Andy Theuninck revised to use FannieDB
* 27Jan13 Eric Lee Based on GeneralSales.
* To exclude Cancelled transactions (X). What are D and Z?
* AND t.trans_status not in ('D','X','Z')
* To exclude Dummy/Training transactions
* AND t.emp_no not in (7000, 9999)
* Don't exclude voided items (trans_status = 'V');
* let the voided and original item cancel each other out.
*/
include(dirname(__FILE__) . '/../../../../config.php');
include(__DIR__ . '/../../../../classlib2.0/FannieAPI.php');
$dbc = FannieDB::get($FANNIE_OP_DB);
if (isset($_REQUEST['submit'])){
$d1 = $_REQUEST['date1'];
$d2 = $_REQUEST['date2'];
$dept = $_REQUEST['dept'];
if ( isset($_REQUEST['other_dates']) ) {
switch ($_REQUEST['other_dates']) {
case 'today':
$d1 = date("Y-m-d");
$d2 = $d1;
break;
case 'yesterday':
$d1 = date("Y-m-d", strtotime('yesterday'));
$d2 = $d1;
break;
case 'this_week':
$d1 = date("Y-m-d", strtotime('last monday'));
$d2 = date("Y-m-d");
break;
case 'last_week':
$d1 = date("Y-m-d", strtotime('last monday - 7 days'));
$d2 = date("Y-m-d", strtotime('last sunday'));
break;
case 'this_month':
$d1 = date("Y-m-d", strtotime('first day of this month'));
$d2 = date("Y-m-d");
break;
case 'last_month':
$d1 = date("Y-m-d", strtotime('first day of last month'));
$d2 = date("Y-m-d", strtotime('last day of last month'));
break;
}
}
$dlog = DTransactionsModel::selectDlog($d1,$d2);
if (isset($_REQUEST['excel'])){
header("Content-Disposition: inline; filename=sales_{$d1}_{$d2}.xls");
header("Content-type: application/vnd.ms-excel; name='excel'");
}
else{
printf("<H3>Ontario Total Taxes: %s </H3>\n", ($d1 == $d2) ? "For $d1" : "From $d1 to $d2");
printf("<a href=index.php?date1=%s&date2=%s&dept=%s&submit=yes&excel=yes>Save to Excel</a>",
$d1,$d2,$dept);
}
$sales = "SELECT
d.Dept_name,
sum(CASE WHEN t.tax = 0 THEN 0 ELSE t.total * x.rate END),
sum(t.quantity),
s.superID,
s.super_name
FROM $dlog AS t LEFT JOIN
departments AS d ON d.dept_no=t.department LEFT JOIN
MasterSuperDepts AS s ON t.department=s.dept_ID LEFT JOIN
core_op.taxrates AS x ON t.tax=x.id
WHERE
(tDate BETWEEN ? AND ?)
AND (s.superID > 0 OR s.superID IS NULL)
AND t.trans_type in ('I','D')
AND t.trans_status not in ('D','X','Z')
AND t.emp_no not in (7000, 9999)
AND t.register_no != 99
GROUP BY s.superID,s.super_name,d.dept_name,t.department
ORDER BY s.superID,t.department";
// 1 = Current Dept. Settings. Default, 0 = Settings at time of sale.
if ($dept == 1){
$sales = "SELECT
CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end,
sum(CASE WHEN t.tax = 0 THEN 0 ELSE t.total * x.rate END),
sum(t.quantity),
CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,
CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END
FROM $dlog AS t LEFT JOIN
products AS p ON t.upc=p.upc LEFT JOIN
departments AS d ON d.dept_no=t.department LEFT JOIN
departments AS e ON p.department=e.dept_no LEFT JOIN
MasterSuperDepts AS s ON s.dept_ID=p.department LEFT JOIN
MasterSuperDepts AS r ON r.dept_ID=t.department LEFT JOIN
core_op.taxrates AS x ON t.tax=x.id
WHERE
(tDate BETWEEN ? AND ?)
AND t.trans_type in ('I','D')
AND t.trans_status not in ('D','X','Z')
AND t.emp_no not in (7000, 9999)
AND t.register_no != 99
AND (s.superID > 0 OR (s.superID IS NULL AND r.superID > 0)
OR (s.superID IS NULL AND r.superID IS NULL))
GROUP BY
CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,
CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END,
CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end,
CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end
ORDER BY
CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,
CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end";
}
$supers = array();
$salesP = $dbc->prepare($sales);
$salesR = $dbc->execute($salesP, array($d1.' 00:00:00',$d2.' 23:59:59'));
$curSuper = 0;
$grandTotal = 0;
while($row = $dbc->fetch_row($salesR)){
if ($curSuper != $row[3]){
$curSuper = $row[3];
}
if (!isset($supers[$curSuper]))
$supers[$curSuper] = array('sales'=>0.0,'qty'=>0.0,'name'=>$row[4],'depts'=>array());
$supers[$curSuper]['sales'] += $row[1];
$supers[$curSuper]['qty'] += $row[2];
$supers[$curSuper]['depts'][] = array('name'=>$row[0],'sales'=>$row[1],'qty'=>$row[2]);
$grandTotal += $row[1];
}
foreach($supers as $s){
if ($s['sales']==0) continue;
echo "<table border=1>\n";//create table
echo "<tr align=right bgcolor='FFFF99'><td> </td><td>Taxes</td><td>Qty</td><td>% Sales</td><td>Dept %</td></tr>\n";//create table header
$superSum = $s['sales'];
foreach($s['depts'] as $d){
printf("<tr align=right><td size=25>%s</td><td size=25>\$%.2f</td><td size=15>%.2f</td>
<td size=15>%.2f %%</td><td size=15>%.2f %%</td></tr>\n",
$d['name'],$d['sales'],$d['qty'],
$d['sales'] / $grandTotal * 100,
$d['sales'] / $superSum * 100);
}
printf("<tr border = 1 align=right bgcolor=#ffff99><th>%s</th><th>\$%.2f</th><th>%.2f</th>
<th>%.2f %%</th><td> </td></tr>\n",
$s['name'],$s['sales'],$s['qty'],$s['sales']/$grandTotal * 100);
echo "</table><br />";
}
printf("<b>Total Taxes: </b>\$%.2f",$grandTotal);
}
else {
$page_title = "Fannie : Ontario Total Taxes Report";
$header = "Ontario Total Taxes Report";
include(__DIR__ . '/../../../../src/header.html');
$lastMonday = "";
$lastSunday = "";
$ts = mktime(0,0,0,date("n"),date("j")-1,date("Y"));
while($lastMonday == "" || $lastSunday == ""){
if (date("w",$ts) == 1 && $lastSunday != "")
$lastMonday = date("Y-m-d",$ts);
elseif(date("w",$ts) == 0)
$lastSunday = date("Y-m-d",$ts);
$ts = mktime(0,0,0,date("n",$ts),date("j",$ts)-1,date("Y",$ts));
}
?>
<script type="text/javascript">
$(document).ready(function(){
$('#date1').datepicker();
$('#date2').datepicker();
});
</script>
<form action=index.php method=get>
<style type="text/css">
/* This makes the input and label look like they have the same baseline. */
input[type="radio"] ,
input[type="checkbox"] {
height: 8px;
}
</style>
<table cellspacing=4 cellpadding=4 border='0'>
<tr style='vertical-align:top;'>
<td>
<table cellspacing='4' cellpadding='4' border='0'>
<tr>
<th>Start Date</th>
<td><input type=text name='date1' id="date1" value="<?php echo $lastMonday; ?>" />
<tr>
<th>End Date</th>
<td><input type=text name='date2' id="date2" value="<?php echo $lastSunday; ?>" /></td>
<tr>
<th></th>
<td>
<input type='radio' name='other_dates' value='' checked='1' > Start - End
</td>
</table>
</td>
<td rowspan='1'>
<fieldset style='border:330066;'>
<legend>Other dates</legend>
<table style='margin: 0em 0em 0em 0em;'>
<tr style='vertical-align:top;'><td style='margin: 0em 1.0em 0em 0em;'>
<input type='radio' name='other_dates' value='today'> Today</br >
<input type='radio' name='other_dates' value='this_week'> This week</br >
<input type='radio' name='other_dates' value='this_month'> This month</br >
</td>
<td rowspan='1'>
<input type='radio' name='other_dates' value='yesterday'> Yesterday</br >
<input type='radio' name='other_dates' value='last_week'> Last week</br >
<input type='radio' name='other_dates' value='last_month'> Last month</br >
</td>
</tr>
</table>
</fieldset>
</td>
</tr>
<tr>
<td colspan=99><select name=dept>
<option value=0>Use department settings at time of sale</option>
<option value=1>Use current department settings</option>
</select></td>
</tr><tr>
<td>Excel <input type=checkbox name=excel /></td>
<td><input type=submit name=submit value="Submit" /></td>
</tr>
</table>
</form>
<?php
include(__DIR__ . '/../../../../src/footer.html');
}