fannie/modules/plugins2.0/OverShortTools/OsVarianceReport.php
<?php
/*******************************************************************************
Copyright 2016 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_once(__DIR__ . '/../../../classlib2.0/FannieAPI.php');
}
class OsVarianceReport extends FannieReportPage
{
// 10Nov13 EL Added title and header
protected $title = 'Over/Short Variance Report';
protected $header = 'Over/Short Variance Report';
protected $auth_classes = array('overshorts');
public $page_set = 'Plugin :: Over/Shorts';
public $description = '[Variance Report] shows over/short variance info over time';
public $report_set = 'Finance';
protected $report_headers = array('Date', 'Lane', 'POS Total', 'Count Total', 'Variance', 'Emp#', 'Name', 'Share');
protected $report_cache = 'day';
protected $required_fields = array('date1', 'date2');
public function fetch_report_data()
{
try {
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$store = $this->form->store;
$mode = $this->form->mode;
} catch (Exception $ex) {
return array();
}
return $mode == 'Details' ? $this->getDetails($date1, $date2, $store) : $this->getTotals($date1, $date2, $store);
}
private function getTotals($date1, $date2, $store)
{
$settings = $this->config->get('PLUGIN_SETTINGS');
$dbc = $this->connection;
$dbc->selectDB($settings['OverShortDatabase']);
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$osP = $dbc->prepare("
SELECT SUM(amt) AS ttl
FROM dailyCounts
WHERE date BETWEEN ? AND ?
AND tender_type IN ('CA', 'SCA')
AND emp_no=?
AND storeID=?");
$transP = $dbc->prepare("
SELECT YEAR(tdate) AS year,
MONTH(tdate) AS month,
DAY(tdate) AS day,
d.register_no,
d.emp_no,
e.FirstName,
e.LastName,
SUM(-1*total) AS ttl
FROM {$dlog} AS d
LEFT JOIN " . $this->config->get('OP_DB') . $dbc->sep() . "employees AS e
ON d.emp_no=e.emp_no
WHERE d.tdate BETWEEN ? AND ?
AND trans_type='T'
AND trans_subtype='CA'
AND store_id=?
AND total <> 0
GROUP BY YEAR(tdate),
MONTH(tdate),
DAY(tdate),
d.register_no,
d.emp_no,
e.FirstName,
e.LastName");
$raw = array();
$totals = array();
$transR = $dbc->execute($transP, array($date1 . ' 00:00:00', $date2 . ' 23:59:59', $store));
// get date-register-employee data
while ($transW = $dbc->fetchRow($transR)) {
$date = date('Y-m-d', mktime(0,0,0, $transW['month'], $transW['day'], $transW['year']));
$key = $date . '-' . $transW['register_no'];
$raw[] = array(
'date' => $date,
'reg' => $transW['register_no'],
'emp' => $transW['emp_no'],
'name' => $transW['FirstName'] . ' ' . $transW['LastName'],
'key' => $key,
);
if (!isset($totals[$key])) {
$totals[$key] = 0;
}
$totals[$key] += $transW['ttl'];
}
// count how many employees were in a drawer each day
for ($i=0; $i<count($raw); $i++) {
$count = 0;
for ($j=0; $j<count($raw); $j++) {
if ($raw[$i]['key'] == $raw[$j]['key']) {
$count++;
}
}
$raw[$i]['count'] = $count;
}
// find counted amounts for each drawer
// avoid re-querying when possible
for ($i=0; $i<count($raw); $i++) {
$date = $raw[$i]['date'];
$key = $raw[$i]['key'];
if (!isset($raw[$i]['actual'])) {
$actual = $dbc->getValue($osP, array($date, $date, $raw[$i]['reg'], $store));
$raw[$i]['actual'] = $actual;
for ($j=$i+1; $j<count($raw); $j++) {
if ($raw[$i]['key'] == $raw[$j]['key']) {
$raw[$j]['actual'] = $actual;
}
}
}
}
$data = array();
foreach ($raw as $row) {
$emp = $row['emp'];
if (!isset($data[$emp])) {
$data[$emp] = array($emp, $row['name'], 0);
}
$key = $row['key'];
$variance = $totals[$key] - $row['actual'];
if ($row['actual'] == 0) {
$variance = 0;
}
$avg = $variance / $row['count'];
$data[$emp][2] += $avg;
if ($emp == 1) print_r($row);
}
$ret = array();
foreach ($data as $emp => $row) {
$row[2] = sprintf('%.2f', $row[2]);
$ret[] = $row;
}
return $ret;
}
public function calculate_footers($data)
{
if (count($data[0]) == 3) {
$this->report_headers = array('Emp#', 'Name', 'Total Variance Share');
$sum = array_reduce($data, function($c, $i) { return $c + $i[2]; });
return array('Total', '', round($sum, 2));
}
return array();
}
private function getDetails($date1, $date2, $store)
{
$settings = $this->config->get('PLUGIN_SETTINGS');
$dbc = $this->connection;
$dbc->selectDB($settings['OverShortDatabase']);
$osP = $dbc->prepare('
SELECT emp_no,
date,
storeID,
SUM(amt) AS ttl
FROM dailyCounts
WHERE date BETWEEN ? AND ?
AND tender_type IN (\'CA\', \'SCA\')
AND storeID=?
GROUP BY emp_no,
date,
storeID');
$osR = $dbc->execute($osP, array($date1, $date2, $store));
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$transP = $dbc->prepare("
SELECT SUM(-1*d.total) AS ttl,
d.emp_no,
e.LastName,
e.FirstName
FROM {$dlog} AS d
LEFT JOIN " . $this->config->get('OP_DB') . $dbc->sep() . "employees AS e
ON d.emp_no=e.emp_no
WHERE d.tdate BETWEEN ? AND ?
AND d.trans_type='T'
AND d.trans_subtype='CA'
AND d.store_id=?
AND d.register_no=?
AND d.upc='0'
AND total <> 0
GROUP BY d.emp_no,
e.LastName,
e.FirstName");
$data = array();
while ($osW = $dbc->fetchRow($osR)) {
$args = array(
$osW['date'] . ' 00:00:00',
$osW['date'] . ' 23:59:59',
$osW['storeID'],
$osW['emp_no'],
);
$ttl = 0;
$emps = array();
$transR = $dbc->execute($transP, $args);
while ($transW = $dbc->fetchRow($transR)) {
$ttl += $transW['ttl'];
$emps[] = array(
$transW['emp_no'],
$transW['FirstName'] . ' ' . $transW['LastName'],
);
}
$var = $osW['ttl'] - $ttl;
foreach ($emps as $e) {
$data[] = array(
$osW['date'],
$osW['emp_no'],
sprintf('%.2f', $ttl),
sprintf('%.2f', $osW['ttl']),
sprintf('%.2f', $var),
$e[0],
$e[1],
sprintf('%.2f', $var/count($emps)),
);
}
}
return $data;
}
public function form_content()
{
$dates = FormLib::standardDateFields();
$stores = FormLib::storePicker();
return <<<HTML
<form method="get">
<div class="col-sm-5">
<div class="form-group">
<label>Show</label>
<select name="mode" class="form-control">
<option>Cashier Totals</option>
<option>Details</option>
</select>
</div>
<div class="form-group">
<label>Store</label>
{$stores['html']}
</div>
<div class="form-group">
<button class="btn btn-default btn-core">Submit</button>
</div>
</div>
{$dates}
</form>
HTML;
}
}
FannieDispatch::conditionalExec();