fannie/reports/Store-Specific/WEFC_Toronto/GeneralCosts/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
* 17Mar13 Andy Theuninck removed variable name for datestamp filed
* + (not necessary, not compatible with all databases)
* 25Jan13 EL Add today, yesterday, this week, last week, this month, last month options.
* 2Jan13 Eric Lee Report of Costs, based on GeneralSales/index.php
* + Base on a dtrans table
* + Use variable for name of datestamp field.
* + Exclude what the dlog view excludes
* + For trans_type D approximate cost as (total / dept markup).
* + Page heading.
* + Format report as a single table. Other HTML adjustments.
* I'm not sure the dept==1 flavour gets markup right when department has changed
* since the transaction.
*/
include(dirname(__FILE__) . '/../../../../config.php');
include(__DIR__ . '/../../../../classlib2.0/FannieAPI.php');
$dbc = FannieDB::get($FANNIE_OP_DB);
/* This program has two modes:
* 1. Display the form for specifying the report.
* Test: the submit button has not been clicked so $_REQUEST['submit'] is not set.
* 2. Prepare and display the report.
* Test: the submit button has been clicked so $_REQUEST['submit'] is set.
*/
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::selectDtrans($d1,$d2);
if (isset($_REQUEST['excel'])){
header("Content-Disposition: inline; filename=costs_{$d1}_{$d2}.xls");
header("Content-type: application/vnd.ms-excel; name='excel'");
}
else{
printf("<H3>General Costs: %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);
}
/* Using department settings at the time of sale.
* I.e. The department# from the transaction.
* If that department# no longer exists or is different then the report will be wrong.
* This does not use a departments table contemporary with the transactions.
*/
if ($dept == 0){
$costs = "SELECT
d.Dept_name,
sum(CASE WHEN t.trans_type = 'I' THEN t.cost WHEN t.trans_type = 'D' AND m.margin > 1.00 THEN t.total / m.margin ELSE 0.00 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
deptMargin AS m ON t.department=m.dept_id
WHERE
(datetime 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";
}
/* Using current department settings.
* I.e. The department for the upc from the current products table.
* This does not use a departments table contemporary with the transactions.
*/
elseif ($dept == 1){
$costs = "SELECT
CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end,
sum(CASE WHEN t.trans_type = 'I' THEN t.cost WHEN t.trans_type = 'D' AND m.margin > 1.00 THEN t.total / m.margin ELSE 0.00 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
deptMargin AS m ON p.department=m.dept_id
WHERE
(datetime BETWEEN ? AND ?)
AND (t.trans_type = 'I' or t.trans_type = 'D')
AND (s.superID > 0 OR (s.superID IS NULL AND r.superID > 0)
OR (s.superID IS NULL AND r.superID IS NULL))
AND ((t.trans_status not in ('D','X','Z')) and (t.emp_no not in (7000, 9999)) and (t.register_no <> 99))
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";
}
else {
print "<br />Form variable 'dept' value >{$dept}< is unknown.";
return;
}
// Array in which totals used in the report are accumulated.
$supers = array();
$prep = $dbc->prepare($costs);
$costsR = $dbc->execute($prep,array($d1.' 00:00:00',$d2.' 23:59:59'));
$curSuper = 0;
$grandTotal = 0;
while($row = $dbc->fetch_row($costsR)){
if ($curSuper != $row[3]){
$curSuper = $row[3];
}
if (!isset($supers[$curSuper]))
$supers[$curSuper] = array('costs'=>0.0,'qty'=>0.0,'name'=>$row[4],'depts'=>array());
$supers[$curSuper]['costs'] += $row[1];
$supers[$curSuper]['qty'] += $row[2];
$supers[$curSuper]['depts'][] = array('name'=>$row[0],'costs'=>$row[1],'qty'=>$row[2]);
$grandTotal += $row[1];
}
$superCount = 0;
foreach($supers as $s){
if ($s['costs']==0) continue;
$superCount++;
if ( $superCount == 1) {
echo "<table border=1>\n";
echo "<tr align=center bgcolor='FFFF99'><th> </th><th>Cost</th><th>Qty</th><th>% Cost</th><th>Dept %</th></tr>\n";
} else {
echo "<tr><th colspan='99' style='color:white;'>Foo</th></tr>\n";
echo "<tr align=center bgcolor='FFFF99'><th> </th><th>Cost</th><th>Qty</th><th>% Cost</th><th>Dept %</th></tr>\n";
}
$superSum = $s['costs'];
foreach($s['depts'] as $d){
printf("<tr align=right><td >%s</td><td >\$%.2f</td><td >%.2f</td>
<td >%.2f %%</td><td >%.2f %%</td></tr>\n",
$d['name'],$d['costs'],$d['qty'],
$d['costs'] / $grandTotal * 100,
$d['costs'] / $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['costs'],$s['qty'],$s['costs']/$grandTotal * 100);
}
echo "</table><br />";
printf("<b>Total Costs: </b>\$%.2f",$grandTotal);
}
// Form for specifying the report.
else {
$page_title = "Fannie : General Costs Report";
$header = "General Costs Report";
include(__DIR__ . '/../../../../src/header.html');
$lastMonday = "";
$lastSunday = "";
/* Default date range is the most recent complete Mon-Sun week,
* with calculation beginning yesterday.
* If today is Friday the 25th the range is 14th to 20th.
* If today is Monday the 28th the range is 21st to 27th.
*/
$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({dateFormat:'yy-mm-dd'});
$('#date2').datepicker({dateFormat:'yy-mm-dd'});
});
</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" /></td>
<tr>
<th>End Date</th>
<td>
<input type=text name='date2' id="date2" /></td>
</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 the department# the upc was assigned to at time of sale</option>
<option value='1'>Use the department# the upc is assigned to now</option>
</select></td>
</tr>
<tr><td>Excel <input type='checkbox' name='excel' /></td>
<td colspan='99'><input type='submit' name='submit' value="Submit" /></td>
</tr>
</table>
</form>
<?php
include(__DIR__ . '/../../../../src/footer.html');
}