fannie/reports/BatchReport/BatchReport.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
*********************************************************************************/
use COREPOS\Fannie\API\lib\Store;
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class BatchReport extends FannieReportPage
{
protected $header = "Select batch(es)";
protected $title = "Fannie :: Batch Report";
protected $report_cache = 'none';
protected $report_headers = array('UPC','SKU','Brand','Description','$','Qty','Rings','Location', 'Lift%');
protected $required_fields = array('batchID');
public $description = '[Batch Report] lists sales for items in a sales batch (or group of sales batches).';
public $report_set = 'Batches';
protected $new_tablesorter = true;
/**
Ajax callback:
Get daily sales totals for a given item
*/
private function ajaxItemSales()
{
$upc = BarcodeLib::padUPC(FormLib::get('upc'));
$date1 = FormLib::get('date1');
$date2 = FormLib::get('date2');
$store = FormLib::get('store');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$dataP = $this->connection->prepare("
SELECT YEAR(tdate),
MONTH(tdate),
DAY(tdate),
SUM(total),
MAX(description) AS descript
FROM {$dlog} AS d
WHERE upc=?
AND " . DTrans::isStoreID($store, 'd') . "
AND tdate BETWEEN ? AND ?
GROUP BY YEAR(tdate),
MONTH(tdate),
DAY(tdate)
ORDER BY YEAR(tdate),
MONTH(tdate),
DAY(tdate)
");
$json = array('dates'=>array(), 'totals'=>array(), 'min'=>99999, 'max'=>0);
$dataR = $this->connection->execute($dataP, array($upc, $store, $date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$points = array();
while ($row = $this->connection->fetchRow($dataR)) {
$date = date('Y-m-d', mktime(0,0,0, $row[1], $row[2], $row[0]));
$total = sprintf('%.2f', $row[3]);
$points[$date] = $total;
if ($total < $json['min']) {
$json['min'] = $total;
}
if ($total > $json['max']) {
$json['max'] = $total;
}
$json['description'] = $row['descript'];
}
$json['min'] = 0.95 * $json['min'];
$json['max'] = 1.05 * $json['max'];
// fill in zeroes for any days without sales
$start = new DateTime($date1);
$end = new DateTime($date2);
$p1d = new DateInterval('P1D');
while ($start <= $end) {
$str = $start->format('Y-m-d');
$json['dates'][] = $str;
$json['totals'][] = isset($points[$str]) ? $points[$str] : 0.00;
$start->add($p1d);
if (!isset($points[$str]) && $json['min'] > 0) {
$json['min'] = 0;
}
}
return $json;
}
function preprocess()
{
$ret = parent::preprocess();
// ajax callback: get daily item sales
if (FormLib::get('upc', false) !== false) {
echo json_encode($this->ajaxItemSales());
return false;
}
$this->addScript('../../src/javascript/Chart.min.js');
$this->addScript('batchReport.js');
$this->addOnloadCommand('batchReport.init();');
return $ret;
}
private function getLifts($dbc, $batchIDs, $storeID)
{
$ret = array();
list($inStr, $args) = $dbc->safeInClause($batchIDs);
$query = "SELECT upc, SUM(saleQty) AS qty, SUM(compareQty) AS comp
FROM SalesLifts
WHERE batchID IN ({$inStr})";
if ($storeID) {
$query .= ' AND storeID=? ';
$args[] = $storeID;
}
$query .= ' GROUP BY upc';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
while ($row = $dbc->fetchRow($res)) {
$ret[$row['upc']] = $row['comp'] != 0 ? (($row['qty'] - $row['comp']) / $row['comp']) : 999.99;
}
return $ret;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$bStart = FormLib::get_form_value('date1','');
$bEnd = FormLib::get_form_value('date2','');
$store = FormLib::get('store', false);
$model = new BatchesModel($dbc);
if ($store === false) {
$store = Store::getIdByIp(0);
}
/**
Assemble argument array and appropriate string
for an IN clause in a prepared statement
*/
$batchID = $this->form->batchID;
if (!is_array($batchID)) {
$batchID = array($batchID);
}
$upcs = array();
foreach ($batchID as $bID) {
$upcs = array_merge($upcs, $model->getUPCs($bID));
}
$upcs = array_unique($upcs);
list($bName, $bStart, $bEnd) = $this->getNameAndDates($batchID, $bStart, $bEnd);
$lifts = $this->getLifts($dbc, $batchID, $store);
$dlog = DTransactionsModel::selectDlog($bStart,$bEnd);
$bStart .= ' 00:00:00';
$bEnd .= ' 23:59:59';
$reportArgs = array($bStart, $bEnd);
list($in_sql, $reportArgs) = $dbc->safeInClause($upcs, $reportArgs);
$reportArgs[] = $store;
$salesBatchQ ="
SELECT d.upc,
p.brand,
p.description,
p.default_vendor_id,
MAX(l.sections) AS location,
MAX(v.sku) AS sku,
SUM(d.total) AS sales, "
. DTrans::sumQuantity('d') . " AS quantity,
SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings
FROM $dlog AS d "
. DTrans::joinProducts('d', 'p', 'INNER') . "
LEFT JOIN FloorSectionsListTable as l on d.upc=l.upc AND l.storeID=d.store_id
LEFT JOIN vendorItems AS v ON (p.upc = v.upc AND p.default_vendor_id = v.vendorID)
WHERE d.tdate BETWEEN ? AND ?
AND d.upc IN ($in_sql)
AND " . DTrans::isStoreID($store, 'd') . "
AND d.charflag <> 'SO'
GROUP BY d.upc,
p.brand,
p.description,
p.default_vendor_id
ORDER BY d.upc";
$salesBatchP = $dbc->prepare($salesBatchQ);
$salesBatchR = $dbc->execute($salesBatchP, $reportArgs);
/**
Simple report
Issue a query, build array of results
*/
$ret = array();
while ($row = $dbc->fetchRow($salesBatchR)) {
$row['lift'] = isset($lifts[$row['upc']]) ? $lifts[$row['upc']] : 0;
$row['lift'] = sprintf('<a href="BatchLiftReport.php?upc=%s&id=%d&store=%d">%.2f</a>',
$row['upc'], $batchID[0], $store, 100*$row['lift']);
$ret[] = $this->rowToRecord($row);
}
return $ret;
}
private function rowToRecord($row)
{
$record = array();
$record[] = $row['upc'];
if ($row['upc'] == $row['sku'] || $row['sku'] == NULL) {
$record[] = '<div align="right"><i class="text-warning">
no sku on record</i></div>';
} else {
$record[] = $row['sku'];
}
$record[] = $row['brand'];
$record[] = $row['description'];
$record[] = sprintf('%.2f',$row['sales']);
$record[] = sprintf('%.2f',$row['quantity']);
$record[] = $row['rings'];
$record[] = $row['location'] === null ? '' : $row['location'];
$record[] = $row['lift'];
return $record;
}
/**
Sum the quantity and total columns
*/
function calculate_footers($data)
{
$sumQty = 0.0;
$sumSales = 0.0;
$sumRings = 0.0;
foreach ($data as $row) {
$sumQty += $row[5];
$sumSales += $row[4];
$sumRings += $row[6];
}
return array('Total',null,null,null,$sumSales,$sumQty, $sumRings, '', '');
}
private function getBatches($dbc, $filter1, $filter2)
{
$batchQ = "
SELECT b.batchID,
batchName
FROM batches AS b
WHERE 1=1 ";
$args = array();
if ($filter1 !== "") {
$batchQ .= " AND batchType=? ";
$args[] = $filter1;
}
if ($filter2 !== "") {
$batchQ .= " AND owner=? ";
$args[] = $filter2;
}
$batchQ .= "ORDER BY b.batchID desc";
$batchP = $dbc->prepare($batchQ);
$batchR = $dbc->execute($batchP, $args);
return $batchR;
}
function form_content()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$filter1 = FormLib::get('btype','');
$filter2 = FormLib::get('owner','');
$ownerQ = $dbc->prepare("
SELECT super_name
FROM superDeptNames
WHERE superID > 0
ORDER BY superID");
$ownerR = $dbc->execute($ownerQ);
$o_opts = "<option value=\"\">Select owner</option>";
while ($ownerW = $dbc->fetchRow($ownerR)) {
$o_opts .= sprintf("<option %s>%s</option>",
(($filter2==$ownerW[0])?'selected':''),
$ownerW[0]);
}
$types = new BatchTypeModel($dbc);
$t_opts = '<option value="">Select type</option>' . $types->toOptions($filter1);
ob_start();
echo '<div class="form-inline">';
echo "<label>Filters</label> ";
echo '<select id="typef" class="form-control"
onchange="location=\'BatchReport.php?btype=\'+$(\'#typef\').val()+\'&owner=\'+escape($(\'#ownerf\').val());">';
echo $t_opts;
echo '</select>';
echo ' ';
echo '<select id="ownerf" class="form-control"
onchange="location=\'BatchReport.php?btype=\'+$(\'#typef\').val()+\'&owner=\'+escape($(\'#ownerf\').val());">';
echo $o_opts;
echo '</select>';
echo '</div>';
echo '<hr />';
echo '<form action="BatchReport.php" method="get">';
echo '<div class="row">';
echo '<div class="col-sm-5">';
echo '<select size="15" multiple name=batchID[] class="form-control" required>';
$batchR = $this->getBatches($dbc, $filter1, $filter2);
while ($batchW = $dbc->fetchRow($batchR)) {
printf('<option value="%d">%s</option>',
$batchW['batchID'],$batchW['batchName']);
}
echo '</select>';
echo '</div>';
$rightCol = <<<HTML
<div class="col-sm-7">
<p>
<label>Start Date</label>
<input class="form-control date-field" name="date1" id="date1" />
</p>
<p>
<label>End Date</label>
<input class="form-control date-field" name="date2" id="date2" />
</p>
<p>
<label>Store(s)
{{STORES}}
</p>
<p>
<label>Excel
<input type="checkbox" name="excel" value="xls" />
</label>
</p>
<p>
<button type="submit" class="btn btn-default">Run Report</button>
</p>
</div>
</div>
HTML;
$stores = FormLib::storePicker();
echo str_replace('{{STORES}}', $stores['html'], $rightCol);
echo '</form>';
return ob_get_clean();
}
private function getNameAndDates($batchID, $bStart, $bEnd)
{
$dbc = $this->connection;
list($inClause, $inArgs) = $dbc->safeInClause($batchID);
$batchInfoQ = $dbc->prepare("
SELECT batchName,
startDate AS startDate,
endDate AS endDate
FROM batches
WHERE batchID IN ($inClause)");
$batchInfoR = $dbc->execute($batchInfoQ,$inArgs);
$bName = "";
while ($batchInfoW = $dbc->fetchRow($batchInfoR)) {
$bName .= $batchInfoW['batchName'] . ' ';
if (empty($bStart) && strtotime($batchInfoW['startDate'])) {
$bStart = date('Y-m-d', strtotime($batchInfoW['startDate']));
}
if (empty($bEnd) && strtotime($batchInfoW['endDate'])) {
$bEnd = date('Y-m-d', strtotime($batchInfoW['endDate']));
}
}
return array($bName, $bStart, $bEnd);
}
function report_description_content()
{
$FANNIE_URL = $this->config->get('URL');
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$ret = array();
$bStart = FormLib::get('date1','');
$bEnd = FormLib::get('date2','');
$batchID = $this->form->batchID;
if (!is_array($batchID)) {
$batchID = array($batchID);
}
list($bName, $bStart, $bEnd) = $this->getNameAndDates($batchID, $bStart, $bEnd);
$ret[] = '<br /><span style="font-size:150%;">'.$bName.'</span>';
if ($this->report_format == 'html') {
$store = FormLib::storePicker();
$ret[] = '<p><form action="BatchReport.php" method="get" class="form-inline">';
$ret[] = "<span style=\"color:black; display:inline;\">
Store: {$store['html']}
From:
<input type=\"text\" name=\"date1\" size=\"10\" value=\"$bStart\" id=\"date1\" />
to:
<input type=\"text\" name=\"date2\" size=\"10\" value=\"$bEnd\" id=\"date2\" />
</span><input type=\"submit\" value=\"Change Dates\" />
<style type=\"text/css\">
.ui-datepicker {
z-index: 999 !important;
}
</style>";
$this->add_onload_command("\$('#date1').datepicker({dateFormat:'yy-mm-dd'});");
$this->add_onload_command("\$('#date2').datepicker({dateFormat:'yy-mm-dd'});");
foreach($batchID as $bID) {
$ret[] = sprintf('<input type="hidden" name="batchID[]" value="%d" />', $bID);
}
$ret[] = '</form></p>';
} else {
$ret[] = "<span style=\"color:black\">From: $bStart to: $bEnd</span>";
}
return $ret;
}
public function helpContent()
{
return '<p>Show per-item sales data for items in a batch or set
of batches over the given date range. The filters just narrow
down the list of batches. You still have to make selections in
the list.</p>';
}
public function unitTest($phpunit)
{
$data = array('upc'=>'4011', 'brand'=>'test', 'description'=>'test',
'sales'=>1, 'quantity'=>1, 'rings'=>1, 'location'=>'test', 'sku'=>'123');
$phpunit->assertInternalType('array', $this->rowToRecord($data));
$phpunit->assertInternalType('array', $this->calculate_footers($this->dekey_array(array($data))));
}
}
FannieDispatch::conditionalExec();