fannie/reports/BatchBeforeAfter/BatchBeforeAfterReport.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 BatchBeforeAfterReport extends FannieReportPage
{
protected $header = "Select batch";
protected $title = "Fannie :: Batch Report";
protected $report_cache = 'none';
protected $report_headers = array('UPC','Description','Pre $','Pre Qty', 'Promo $', 'Promo Qty', 'Post $', 'Post Qty', 'Location');
protected $required_fields = array('batchID');
public $description = '[Batch Before After Report] lists sales for items in a sales batch before, during, and after the promotional period.';
public $themed = true;
public $report_set = 'Batches';
protected $new_tablesorter = true;
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','');
$model = new BatchesModel($dbc);
$batchID = $this->form->batchID;
$upcs = $model->getUPCs($batchID);
$upcs = array_unique($upcs);
$batchInfoP = $dbc->prepare("SELECT startDate, endDate FROM batches WHERE batchID=?");
$batchInfo = $dbc->getRow($batchInfoP, array($batchID));
$bStart = new DateTimeImmutable($batchInfo['startDate']);
$bEnd = new DateTimeImmutable($batchInfo['endDate']);
$diff = $bEnd->diff($bStart, true);
$rStart = $bStart->sub($diff);
$rEnd = $bEnd->add($diff);
$dlog = DTransactionsModel::selectDlog($rStart->format('Y-m-d'), $rEnd->format('Y-m-d'));
$reportArgs = array(
$bStart->format('Y-m-d 00:00:00'), // pre sum
$bStart->format('Y-m-d 00:00:00'),
$bStart->format('Y-m-d 00:00:00'), // promo sum
$bEnd->format('Y-m-d 23:59:59'),
$bStart->format('Y-m-d 00:00:00'),
$bEnd->format('Y-m-d 23:59:59'),
$bEnd->format('Y-m-d 23:59:59'), // post sum
$bEnd->format('Y-m-d 23:59:59'),
$rStart->format('Y-m-d 00:00:00'), // report dates
$rEnd->format('Y-m-d 23:59:59'),
);
$in_sql = '';
list($in_sql, $reportArgs) = $dbc->safeInClause($upcs, $reportArgs);
$salesBatchQ ="
SELECT d.upc,
p.description,
f.sections AS location,
SUM(CASE WHEN d.tdate < ? THEN total ELSE 0 END) AS preTTL,
SUM(CASE WHEN d.tdate < ? THEN d.quantity ELSE 0 END) AS preQty,
SUM(CASE WHEN d.tdate BETWEEN ? AND ? THEN total ELSE 0 END) AS promoTTL,
SUM(CASE WHEN d.tdate BETWEEN ? AND ? THEN d.quantity ELSE 0 END) AS promoQty,
SUM(CASE WHEN d.tdate > ? THEN total ELSE 0 END) AS postTTL,
SUM(CASE WHEN d.tdate > ? THEN d.quantity ELSE 0 END) AS postQty
FROM $dlog AS d "
. DTrans::joinProducts('d', 'p', 'INNER') . "
LEFT JOIN FloorSectionsListView AS f ON d.upc=f.upc AND d.store_id=f.storeID
WHERE d.tdate BETWEEN ? AND ?
AND d.upc IN ($in_sql)
GROUP BY d.upc,
p.description,
f.sections
ORDER BY d.upc";
$salesBatchP = $dbc->prepare($salesBatchQ);
$inArgs[] = $bStart;
$inArgs[] = $bEnd;
$salesBatchR = $dbc->execute($salesBatchP, $reportArgs);
/**
Simple report
Issue a query, build array of results
*/
$ret = array();
while ($row = $dbc->fetchRow($salesBatchR)) {
$record = array();
$record[] = $row['upc'];
$record[] = $row['description'];
$record[] = sprintf('%.2f', $row['preTTL']);
$record[] = sprintf('%.2f', $row['preQty']);
$record[] = sprintf('%.2f', $row['promoTTL']);
$record[] = sprintf('%.2f', $row['promoQty']);
$record[] = sprintf('%.2f', $row['postTTL']);
$record[] = sprintf('%.2f', $row['postQty']);
$record[] = $row['location'] === null ? '' : $row['location'];
$ret[] = $record;
}
return $ret;
}
/**
Sum the quantity and total columns
*/
function calculate_footers($data)
{
$sums = array(0, 0, 0, 0, 0, 0);
foreach ($data as $row) {
for ($i=0; $i<6; $i++) {
$sums[$i] += $row[$i+2];
}
}
$sums[] = '';
array_unshift($sums, null);
array_unshift($sums, 'Total');
return $sums;
}
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]);
}
$typeQ = $dbc->prepare("
SELECT batchTypeID,
typeDesc
FROM batchType
ORDER BY batchTypeID");
$typeR = $dbc->execute($typeQ);
$t_opts = "<option value=\"\">Select type</option>";
while ($typeW = $dbc->fetchRow($typeR)) {
$t_opts .= sprintf("<option %s value=%d>%s</option>",
(($filter1==$typeW[0])?'selected':''),
$typeW[0],$typeW[1]);
}
ob_start();
echo '<div class="form-inline">';
echo "<label>Filters</label> ";
echo '<select id="typef" class="form-control"
onchange="location=\'BatchBeforeAfterReport.php?btype=\'+$(\'#typef\').val()+\'&owner=\'+escape($(\'#ownerf\').val());">';
echo $t_opts;
echo '</select>';
echo ' ';
echo '<select id="ownerf" class="form-control"
onchange="location=\'BatchBeforeAfterReport.php?btype=\'+$(\'#typef\').val()+\'&owner=\'+escape($(\'#ownerf\').val());">';
echo $o_opts;
echo '</select>';
echo '</div>';
echo '<hr />';
$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);
echo '<form action="BatchBeforeAfterReport.php" method="get">';
echo '<div class="row">';
echo '<div class="col-sm-5">';
echo '<select size="15" name=batchID class="form-control" required>';
while ($batchW = $dbc->fetchRow($batchR)) {
printf('<option value="%d">%s</option>',
$batchW['batchID'],$batchW['batchName']);
}
echo '</select>';
echo '</div>';
echo '<div class="col-sm-7">';
echo '<p><label>Excel ';
echo '<input type="checkbox" name="excel" value="xls" /></label></p>';
echo '<p><button type="submit" class="btn btn-default">Run Report</button></p>';
echo '</div>';
echo '</div>';
echo '</form>';
return ob_get_clean();
}
}
FannieDispatch::conditionalExec();