fannie/modules/plugins2.0/OpenBookFinancingV2/ObfTrendReport.php
<?php
/*******************************************************************************
Copyright 2014 Whole Foods Co-op
This file is part of IT CORE.
IT CORE 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.
IT CORE 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 ObfTrendReport extends FannieReportPage
{
protected $sortable = false;
protected $no_sort_but_style = true;
protected $required_fields = array('from', 'to');
protected $title = 'OBF Trends';
protected $header = 'OBF Trends';
public $discoverable = false;
public function fetch_report_data()
{
$dbc = ObfLibV2::getDB();
$start = FormLib::get('from');
$end = FormLib::get('to');
$store = FormLib::get('store');
$categoryP = $dbc->prepare('
SELECT
c.name,
c.obfCategoryID AS catID
FROM ObfSalesCache AS s
INNER JOIN ObfCategories AS c ON s.obfCategoryID=c.obfCategoryID
WHERE s.obfWeekID BETWEEN ? AND ?
AND c.storeID=?
GROUP BY c.obfCategoryID, c.name');
$salesP = $dbc->prepare('
SELECT SUM(actualSales) AS actual,
SUM(lastYearSales) AS ly,
c.name,
c.obfCategoryID AS catID,
s.obfWeekID,
MAX(w.startDate) AS start
FROM ObfSalesCache AS s
INNER JOIN ObfCategories AS c ON s.obfCategoryID=c.obfCategoryID
INNER JOIN ObfWeeks AS w ON s.obfWeekID=w.obfWeekID
WHERE s.obfWeekID BETWEEN ? AND ?
AND s.obfCategoryID=?
GROUP BY s.obfWeekID, c.obfCategoryID, c.name');
$subP = $dbc->prepare('
SELECT actualSales,
lastYearSales,
m.super_name
FROM ObfSalesCache AS s
LEFT JOIN is4c_op.superDeptNames AS m ON s.superID=m.superID
WHERE s.obfWeekID BETWEEN ? AND ?
AND s.obfCategoryID=?
ORDER BY m.super_name, s.obfWeekID');
$laborP = $dbc->prepare('
SELECT hours,
wages
FROM ObfLabor
WHERE obfWeekID BETWEEN ? AND ?
AND obfCategoryID=?
ORDER BY obfWeekID');
$sales=$growth=$labor=$wages=$slph=array();
$headers = array();
$res = $dbc->execute($categoryP, array($start, $end, $store));
$first = true;
while ($row = $dbc->fetchRow($res)) {
$catID = $row['catID'];
$catName = $row['name'];
$salesR = $dbc->execute($salesP, array($start, $end, $catID));
$s_record = array($row['name'] . ' Sales');
$g_record = array($row['name'] . ' Growth');
if ($first) {
$headers[] = '';
}
$ttl = array();
while ($catW = $dbc->fetchRow($salesR)) {
$ttl[] = $catW['actual'];
$s_record[] = $catW['actual'];
$g_record[] = sprintf('%.2f%%', 100*($catW['actual']-$catW['ly']) / $catW['actual']);
if ($first) {
$headers[] = date('Y-m-d', strtotime($catW['start']));
}
}
$first = false;
$s_record['meta'] = FannieReportPage::META_BOLD;
$g_record['meta'] = FannieReportPage::META_BOLD;
$sales[] = $s_record;
$growth[] = $g_record;
$laborR = $dbc->execute($laborP, array($start, $end, $catID));
$h_record = array($catName . ' Labor Hours');
$w_record = array($catName . ' Labor Wages');
$p_record = array($catName . ' SPLH');
while ($labW = $dbc->fetchRow($laborR)) {
$wsales = array_shift($ttl);
$h_record[] = $labW['hours'];
$w_record[] = $labW['wages'];
$p_record[] = round($wsales / $labW['hours'], 2);
}
$labor[] = $h_record;
$wages[] = $w_record;
$splh[] = $p_record;
$super = null;
$subSales = $dbc->execute($subP, array($start,$end,$catID));
$s_record = $g_record = array();
while ($subW = $dbc->fetchRow($subSales)) {
if ($super === null) {
$s_record = array($subW['super_name'] . ' Sales');
$g_record = array($subW['super_name'] . ' Growth');
$super = $subW['super_name'];
} elseif ($super != $subW['super_name']) {
$sales[] = $s_record;
$growth[] = $g_record;
$s_record = array($subW['super_name'] . ' Sales');
$g_record = array($subW['super_name'] . ' Growth');
$super = $subW['super_name'];
}
$s_record[] = $subW['actualSales'];
$g_record[] = sprintf('%.2f%%', 100*($subW['actualSales'] - $subW['lastYearSales']) / $subW['actualSales']);
}
$sales[] = $s_record;
$growth[] = $g_record;
}
$this->report_headers = $headers;
$sttl = $this->sumSales($sales);
$sales[] = $sttl;
$noSalesP = $dbc->prepare('
SELECT obfCategoryID, name
FROM ObfCategories
WHERE hasSales=0
AND storeID=?');
$nsR = $dbc->execute($noSalesP, array($store));
while ($nsW = $dbc->fetchRow($nsR)) {
$l_record = array($nsW['name'] . ' Labor Hours');
$w_record = array($nsW['name'] . ' Labor Wages');
$s_record = array($nsW['name'] . ' SPLH');
$laborR = $dbc->execute($laborP, array($start, $end, $nsW['obfCategoryID']));
$week = 1;
while ($labW = $dbc->fetchRow($laborR)) {
$l_record[] = $labW['hours'];
$w_record[] = $labW['wages'];
$s_record[] = sprintf('%.2f', $sttl[$week]/$labW['hours']);
$week++;
}
$labor[] = $l_record;
$wages[] = $w_record;
$splh[] = $s_record;
}
$lttl = $this->sumLabor($labor);
array_unshift($labor, $lttl);
$wttl = $this->sumWages($wages);
array_unshift($wages, $wttl);
$pttl = $this->sumSPLH($sttl, $lttl);
array_unshift($splh, $pttl);
return array_merge($sales, $growth, $labor, $wages, $splh);
}
private function sumSPLH($sttl, $lttl)
{
$pttl = array('SPLH Total');
foreach ($sttl as $id => $val) {
if ($id !== 'meta' && $id !== 0) {
$pttl[$id] = round($sttl[$id] / $lttl[$id], 2);
}
}
$pttl['meta'] = FannieReportPage::META_BOLD;
return $pttl;
}
private function sumSales($sales)
{
$sttl = array('Total Sales');
// -1 because meta
for ($i=1; $i<count($sales[0])-1; $i++) {
$ssum = 0;
for ($j=0; $j<count($sales); $j++) {
if (!isset($sales[$j]['meta'])) {
continue;
}
$ssum += $sales[$j][$i];
}
$sttl[] = $ssum;
}
$sttl['meta'] = FannieReportPage::META_BOLD;
return $sttl;
}
private function sumLabor($labor)
{
$lttl = array('Total Labor');
for ($i = 1; $i<count($labor[0]); $i++) {
$lsum = 0;
for ($j=0;$j<count($labor);$j++) {
$lsum += $labor[$j][$i];
}
$lttl[] = $lsum;
}
$lttl['meta'] = FannieReportPage::META_BOLD;
return $lttl;
}
private function sumWages($labor)
{
$lttl = array('Total Wages');
for ($i = 1; $i<count($labor[0]); $i++) {
$lsum = 0;
for ($j=0;$j<count($labor);$j++) {
$lsum += $labor[$j][$i];
}
$lttl[] = $lsum;
}
$lttl['meta'] = FannieReportPage::META_BOLD;
return $lttl;
}
public function form_content()
{
$dbc = ObfLibV2::getDB();
$res = $dbc->query('
SELECT c.obfWeekID, w.startDate
FROM ObfSalesCache AS c
INNER JOIN ObfWeeks AS w ON c.obfWeekID=w.obfWeekID
WHERE c.actualSales > 0 AND c.actualSales IS NOT NULL
GROUP BY c.obfWeekID, w.startDate
ORDER BY w.startDate DESC');
$weeks = array();
while ($row = $dbc->fetchRow($res)) {
$weeks[$row['obfWeekID']] = 'Week of: ' . date('Y-m-d', strtotime($row['startDate']));
}
$ret = '<form action="' . filter_input(INPUT_SERVER, 'PHP_SELF') . '" method="get">';
$ret .= '<div class="form-group">
<label>From</label>
<select name="from" class="form-control">';
foreach ($weeks as $id => $name) {
$ret .= '<option value="' . $id . '">' . $name . '</option>';
}
$ret .= '</select>
</div>';
$ret .= '<div class="form-group">
<label>To</label>
<select name="to" class="form-control">';
foreach ($weeks as $id => $name) {
$ret .= '<option value="' . $id . '">' . $name . '</option>';
}
$ret .= '</select>
</div>';
$stores = FormLib::storePicker();
$ret .= '<div class="form-group">
<label>Store</label>
' . $stores['html'] . '
</div>
<p>
<button type="submit" class="btn btn-default">Submit</button>
</p>
</form>';
return $ret;
}
public function report_content()
{
$default = parent::report_content();
if ($this->report_format == 'html') {
$default .= '<div id="chartArea" style="border: 1px solid black;padding: 2em;">';
$default .= 'Graph: <select id="grapher" onchange="showGraph(this.value);"></select>';
$default .= '<div><canvas id="chartCanvas"></canvas></div>';
$default .= '</div>';
$this->addScript('trend.js');
$this->addOnloadCommand('addOptions();');
$this->addOnloadCommand("showGraph(\$('tbody td.reportColumn0:first').text().trim());\n");
}
return $default;
}
public function preprocess()
{
parent::preprocess();
// custom: needs graphing JS/CSS
if ($this->content_function == 'report_content' && $this->report_format == 'html') {
$this->addScript('../../../src/javascript/Chart.min.js');
$this->addScript('../../../src/javascript/CoreChart.js');
}
return true;
}
}
FannieDispatch::conditionalExec();