fannie/reports/MonthOverMonth/MonthOverMonthReport.php
<?php
/*******************************************************************************
Copyright 2012 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
*********************************************************************************/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class MonthOverMonthReport extends FannieReportPage {
private $months = array();
protected $title = "Fannie : Month Over Month Movement";
protected $header = "Month Over Month Movement";
protected $required_fields = array('month1', 'month2');
public $description = '[Monthly Movement] shows monthly sales totals for items or departments.';
public $report_set = 'Movement Reports';
public $themed = true;
function preprocess()
{
parent::preprocess();
if ($this->content_function == 'report_content') {
$this->report_headers = array('#','Description');
// build headers and keys off span of months
$this->months = array();
$stamp1 = mktime(0,0,0,FormLib::get_form_value('month1',1),1,FormLib::get_form_value('year1',1));
$stamp2 = mktime(0,0,0,FormLib::get_form_value('month2',1),1,FormLib::get_form_value('year2',1));
while($stamp1 <= $stamp2){
$this->report_headers[] = date('F Y',$stamp1);
$this->months[] = date('Y-n',$stamp1);
$stamp1 = mktime(0,0,0,date('n',$stamp1)+1,1,date('Y',$stamp1));
}
}
return true;
}
function fetch_report_data(){
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$month1 = $this->form->month1;
$month2 = $this->form->month2;
$year1 = FormLib::get_form_value('year1',date('Y'));
$year2 = FormLib::get_form_value('year2',date('Y'));
$date1 = date('Y-m-d',mktime(0,0,0,$month1,1,$year1));
$date2 = date('Y-m-t',mktime(0,0,0,$month2,1,$year2));
$dlog = DTransactionsModel::selectDlog($date1,$date2);
$date1 .= ' 00:00:00';
$date2 .= ' 00:00:00';
$qArgs = array($date1,$date2);
$query = "";
$type = FormLib::get_form_value('mtype','upc');
if ($type == 'upc'){
$inClause = "(";
$vals = preg_split("/\D+/",FormLib::get_form_value('upcs',''));
foreach($vals as $v){
$qArgs[] = BarcodeLib::padUPC($v);
$inClause .= "?,";
}
$inClause = rtrim($inClause,",").")";
$query = "SELECT t.upc,
p.description, "
. DTrans::sumQuantity('t') . " AS qty,
SUM(total) AS sales,
MONTH(tdate) AS month,
YEAR(tdate) AS year
FROM $dlog AS t "
. DTrans::joinProducts('t', 'p') . "
WHERE t.trans_status <> 'M'
AND tdate BETWEEN ? AND ?
AND t.upc IN $inClause
GROUP BY YEAR(tdate),
MONTH(tdate),
t.upc,
p.description
ORDER BY YEAR(tdate),
MONTH(tdate),
t.upc,
p.description";
} else {
$dept1 = FormLib::get_form_value('dept1',1);
$dept2 = FormLib::get_form_value('dept2',1);
$qArgs[] = $dept1;
$qArgs[] = $dept2;
$query = "SELECT t.department,d.dept_name,SUM(t.quantity) as qty,
SUM(total) as sales, MONTH(tdate) as month, YEAR(tdate) as year
FROM $dlog AS t
LEFT JOIN departments AS d ON t.department=d.dept_no
WHERE t.trans_status <> 'M'
AND tdate BETWEEN ? AND ?
AND t.department BETWEEN ? AND ?
GROUP BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name
ORDER BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name";
}
$queryP = $dbc->prepare($query);
$result = $dbc->execute($queryP, $qArgs);
$ret = array();
while ($row = $dbc->fetchRow($result)){
if (!isset($ret[$row[0]])){
$ret[$row[0]] = array('num'=>$row[0],'desc'=>$row[1]);
foreach($this->months as $mkey)
$ret[$row[0]][$mkey] = 0;
}
if (FormLib::get_form_value('results','Sales') == 'Sales')
$ret[$row[0]][$row['year'].'-'.$row['month']] = $row['sales'];
else
$ret[$row[0]][$row['year'].'-'.$row['month']] = $row['qty'];
}
return $this->dekey_array($ret);
}
function form_content(){
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$depts = array();
$q = $dbc->prepare("SELECT dept_no,dept_name FROM departments ORDER BY dept_no");
$r = $dbc->execute($q);
while($w = $dbc->fetch_row($r))
$depts[$w[0]] = $w[1];
ob_start();
?>
<form action="MonthOverMonthReport.php" method="get" class="form-inline">
<p>
<select name="month1" class="form-control">
<?php
for($i=1;$i<13;$i++)
printf("<option value=%d>%s</option>",$i,date("F",mktime(0,0,0,$i,1,2000)));
?>
</select>
<input type="number" name="year1" value="<?php echo date("Y"); ?>" class="form-control" />
through
<select name="month2" class="form-control">
<?php
for($i=1;$i<13;$i++)
printf("<option value=%d>%s</option>",$i,date("F",mktime(0,0,0,$i,1,2000)));
?>
</select>
<input type="number" name="year2" value="<?php echo date("Y"); ?>" class="form-control" />
</p>
<p>
<input type="hidden" name="mtype" id="type-field" value="upc" />
<ul class="nav nav-tabs">
<li class="active"><a href="#upc-tab" role="tab"
onclick="$(this).tab('show'); $('#type-field').val('upc'); return false;">By UPC</a></li>
<li><a href="#dept-tab" role="tab"
onclick="$(this).tab('show'); $('#type-field').val('dept'); return false;">By Department</a></li>
</ul>
<div class="tab-content">
<div class="tab-pane active" id="upc-tab">
<label class="control-label">UPC(s)</label>
<input type="text" name="upcs" class="form-control">
</div>
<div class="tab-pane" id="dept-tab">
<label class="control-label">Start</label>
<select name="dept1" class="form-control">
<?php
foreach($depts as $k=>$v)
printf("<option value=%d>%d %s</option>",$k,$k,$v);
?>
</select>
<label class="control-label">End</label>
<select name="dept2" class="form-control">
<?php
foreach($depts as $k=>$v)
printf("<option value=%d>%d %s</option>",$k,$k,$v);
?>
</select>
</div>
</div>
</p>
<p>
<label class="control-label">Results in</label>
<select class="form-control" name=results><option>Sales</option><option>Quantity</option></select>
</p>
<p>
<button type="submit" value="Run Report" class="btn btn-default">Run Report</button>
<label>
<input type="checkbox" name="excel" /> Excel
</label>
</p>
</form>
<?php
return ob_get_clean();
}
public function helpContent()
{
return '<p>This report shows monthly sales totals for a
particular item or for all items in a department range.</p>';
}
}
FannieDispatch::conditionalExec();