fannie/reports/HourlySales/HourlySalesReport.php
<?php
/*******************************************************************************
Copyright 2013 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 HourlySalesReport extends FannieReportPage
{
public $description = '[Hourly Sales] lists sales per hour over a given date range.';
public $report_set = 'Sales Reports';
public $themed = true;
protected $title = "Fannie : Hourly Sales Report";
protected $header = "Hourly Sales";
protected $required_fields = array('date1', 'date2');
protected $sortable = false;
protected $no_sort_but_style = true;
protected $new_tablesorter = true;
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;
}
private function fetch_adjacent_period()
{
$date1 = FormLib::get('date1');
$date2 = FormLib::get('date2');
$temp1 = new DateTime($date1);
$temp2 = new DateTime($date2);
$diff = $temp1->diff($temp2, true);
$days = $diff->format('%a');
$formContents = array('buyer','deptStart','deptEnd','store','other_dates');
foreach ($formContents as $input) {
${$input} = FormLib::get($input);
}
$actions = array(
'increment' => array(
'action' => '+',
'glyph' => 'right',
),
'decrement' => array(
'action' => '-',
'glyph' => 'left',
),
);
foreach ($actions as $k => $row) {
$temp1 = new DateTime($date1);
$temp2 = new DateTime($date2);
if ($days == 0) {
$temp1->modify($row['action'].'1 Day');
$temp2->modify($row['action'].'1 Day');
$newDate1 = $temp1->format('Y-m-d');
$newDate2 = $temp2->format('Y-m-d');
} elseif ($days == 6) {
$temp1->modify($row['action'].'1 Week');
$temp2->modify($row['action'].'1 Week');
$newDate1 = $temp1->format('Y-m-d');
$newDate2 = $temp2->format('Y-m-d');
} else {
// assumes we're probably looking at a month
$temp1->modify($row['action'].'1 Month');
$temp2->modify($row['action'].'31 Days');
$newDate1 = $temp1->format('Y-m-d');
$newDate2 = $temp2->format('Y-m-t');
}
${'form'.$k} = '<form method="get" style="display: inline-block;">';
foreach ($formContents as $input) {
${'form'.$k} .= "<input type='hidden' name='$input' value='${$input}'>";
}
${'form'.$k} .= "<input type='hidden' name='date1' value='$newDate1'>";
${'form'.$k} .= "<input type='hidden' name='date2' value='$newDate2'>";
${'form'.$k} .= '<button class="btn btn-default btn-xs"><span class="fas fa-chevron-'.$row['glyph'].'"></span></button>';
${'form'.$k} .= '</form>';
}
return <<<HTML
<div><label>Change Period</label>: $formdecrement | $formincrement</div>
HTML;
}
public function report_description_content()
{
$deptStart = FormLib::get('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$weekday = FormLib::get('weekday', 0);
$buyer = FormLib::get('buyer', '');
$ret = array();
if ($buyer === '') {
$ret[] = 'Department '.$deptStart.' to '.$deptEnd;
} else if ($buyer == -1) {
$ret[] = 'All Super Departments';
} else if ($buyer == -2) {
$ret[] = 'All Retail Super Departments';
} else {
$ret[] = 'Super Department '.$buyer;
}
if ($weekday == 1) {
$ret[] = 'Grouped by weekday';
}
if ($this->report_format == 'html') {
$ret[] = sprintf(' <a href="../HourlyTrans/HourlyTransReport.php?%s">Transaction Counts for Same Period</a>',
filter_input(INPUT_SERVER, 'QUERY_STRING'));
}
$ret[] = $this->fetch_adjacent_period();
return $ret;
}
public function report_content() {
$default = parent::report_content();
if ($this->report_format == 'html') {
$default .= '<div class="row">
<div class="col-sm-10"><canvas id="dailyCanvas"></canvas></div>
</div><div class="row">
<div class="col-sm-10"><canvas id="totalCanvas"></canvas></div>
</div>';
$this->addOnloadCommand('chartAll('.(count($this->report_headers)-1).')');
}
return $default;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$weekday = FormLib::get('weekday', 0);
$store = FormLib::get('store', 0);
$buyer = FormLib::get('buyer', '');
$dow = FormLib::get('dow', '');
// args/parameters differ with super
// vs regular department
$args = array($date1.' 00:00:00', $date2.' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
if ($dow) {
$where .= ' AND ' . $dbc->dayofweek('tdate') . ' = ? ';
$args[] = $dow;
}
$args[] = $store;
$date_selector = 'year(tdate), month(tdate), day(tdate)';
$day_names = array();
if ($weekday == 1) {
$date_selector = $dbc->dayofweek('tdate');
$timestamp = strtotime('next Sunday');
for ($i = 1; $i <= 7; $i++) {
$day_names[$i] = strftime('%a', $timestamp);
$timestamp = strtotime('+1 day', $timestamp);
}
}
$hour = $dbc->hour('tdate');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$nabs = DTrans::memTypeIgnore($dbc);
$query = "SELECT $date_selector, $hour as hour,
sum(d.total) AS ttl, avg(d.total) as avg
FROM $dlog AS d ";
// join only needed with specific buyer
// or all retail
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "WHERE d.trans_type IN ('I','D')
AND d.tdate BETWEEN ? AND ?
AND d.memType NOT IN {$nabs}
AND $where ";
if ($this->config->get('COOP_ID') == 'WFC_Duluth') {
$query .= ' AND d.department NOT IN (993, 998, 703) ';
}
$query .= " AND " . DTrans::isStoreID($store, 'd') . "
GROUP BY $date_selector, $hour
ORDER BY $date_selector, $hour";
$prep = $dbc->prepare($query);
$result = $dbc->execute($query, $args);
$dataset = array();
$minhour = 24;
$maxhour = 0;
while($row = $dbc->fetch_row($result)) {
$hour = (int)$row['hour'];
$date = '';
if ($weekday == 1) {
$date = $day_names[$row[0]];
} else {
$date = sprintf('%d/%d/%d', $row[1], $row[2], $row[0]);
}
if (!isset($dataset[$date])) {
$dataset[$date] = array();
}
$dataset[$date][$hour] = $row['ttl'];
if ($hour < $minhour) {
$minhour = $hour;
}
if ($hour > $maxhour) {
$maxhour = $hour;
}
}
if (isset($dataset['Sun'])) {
$sunday = $dataset['Sun'];
unset($dataset['Sun']);
$dataset['Sun'] = $sunday;
}
/**
# of columns is dynamic depending on the
date range selected
*/
$this->report_headers = array('Day');
foreach($dataset as $day => $info) {
$this->report_headers[] = $day;
}
$this->report_headers[] = 'Total';
$data = array();
/**
# of rows is dynamic depending when
the store was open
*/
for($i=$minhour; $i<=$maxhour; $i++) {
$record = array();
$sum = 0;
if ($i < 12) {
$record[] = str_pad($i,2,'0',STR_PAD_LEFT).':00 AM';
} else if ($i == 12) {
$record[] = $i.':00 PM';
} else {
$record[] = str_pad(($i-12),2,'0',STR_PAD_LEFT).':00 PM';
}
// each day's sales for the given hour
foreach($dataset as $day => $info) {
$sales = isset($info[$i]) ? $info[$i] : 0;
$record[] = sprintf('%.2f', $sales);
$sum += $sales;
}
$record[] = sprintf('%.2f', $sum);
$data[] = $record;
}
return $data;
}
public function calculate_footers($data)
{
if (count($data) == 0) {
return array();
}
$ret = array('Totals');
for($i=1; $i<count($data[0]); $i++) {
$ret[] = 0.0;
}
foreach($data as $row) {
for($i=1; $i < count($row); $i++) {
$ret[$i] += $row[$i];
}
}
for($i=1; $i<count($ret); $i++) {
$ret[$i] = sprintf('%.2f', $ret[$i]);
}
return $ret;
}
public function javascriptContent()
{
if ($this->report_format != 'html') {
return;
}
return <<<JAVASCRIPT
function chartAll(totalCol) {
var xLabels = $('td.reportColumn0').toArray().map(x => x.innerHTML.trim());
var totals = $('td.reportColumn' + totalCol).toArray().map(x => Number(x.innerHTML.trim()));
var daily = [];
var dailyLabels = [];
for (var i=1; i<totalCol; i++) {
dailyLabels.push($('th.reportColumn'+i).first().text().trim());
var yData = $('td.reportColumn' + i).toArray().map(x => Number(x.innerHTML.trim()));
daily.push(yData);
}
CoreChart.lineChart('dailyCanvas', xLabels, daily, dailyLabels);
CoreChart.lineChart('totalCanvas', xLabels, [totals], ["Total"]);
}
JAVASCRIPT;
}
public function form_content()
{
ob_start();
?>
<form method="get" class="form-horizontal">
<div class="row">
<div class="col-sm-6">
<?php echo FormLib::standardDepartmentFields('buyer'); ?>
<div class="form-group">
<label class="col-sm-4 control-label">
Group by weekday?
<input type=checkbox name=weekday value=1>
</label>
<div class="col-sm-4">
<select name="dow" class="form-control">
<option value="">Filter by weekday</option>
<option value="2">Mondays</option>
<option value="3">Tuesdays</option>
<option value="4">Wendesdays</option>
<option value="5">Thursdays</option>
<option value="6">Fridays</option>
<option value="7">Saturdays</option>
<option value="1">Sundays</option>
</select>
</div>
</div>
<div class="form-group">
<label class="control-label col-sm-4">Save to Excel
<input type=checkbox name=excel id=excel value=1>
</label>
<label class="col-sm-4 control-label">Store</label>
<div class="col-sm-4">
<?php $ret=FormLib::storePicker();echo $ret['html']; ?>
</div>
</div>
</div>
<div class="col-sm-5">
<div class="form-group">
<label class="col-sm-4 control-label">Start Date</label>
<div class="col-sm-8">
<input type=text id=date1 name=date1 class="form-control date-field" required />
</div>
</div>
<div class="form-group">
<label class="col-sm-4 control-label">End Date</label>
<div class="col-sm-8">
<input type=text id=date2 name=date2 class="form-control date-field" required />
</div>
</div>
<div class="form-group">
<?php echo FormLib::date_range_picker(); ?>
</div>
</div>
</div>
<p>
<button type=submit name=submit value="Submit" class="btn btn-default btn-core">Submit</button>
<button type=reset name=reset class="btn btn-default btn-reset"
onclick="$('#super-id').val('').trigger('change');">Start Over</button>
</p>
</form>
<?php
$this->addOnloadCommand("\$('#subdepts').closest('.form-group').hide();");
return ob_get_clean();
}
public function helpContent()
{
return '<p>This report shows hourly sales over a range of dates.
The rows are always hours. The columns are either calendar
dates or named weekdays (e.g., Monday, Tuesday) if grouping
by week day.</p>
<p>If a <em>Buyer/Dept</em> option is used, the result will
be sales from that super department. Otherwise, the result
will be sales from the specified department range. Note there
are a couple special options in the <em>Buyer/Dept</em> list:
<em>All</em> is simply all sales and <em>All Retail</em> is
everything except for super department #0 (zero).</p>';
}
}
FannieDispatch::conditionalExec();