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);
/**
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);
}
$inArgs = array();
$inClause = '(';
$upcs = array();
foreach ($batchID as $bID) {
$inClause .= '?,';
$inArgs[] = $bID;
$upcs = array_merge($upcs, $model->getUPCs($bID));
}
$upcs = array_unique($upcs);
$inClause = rtrim($inClause, ',') . ')';
$batchInfoQ = '
SELECT batchName,
year(startDate) as sy,
month(startDate) as sm,
day(startDate) as sd,
year(endDate) as ey,
month(endDate) as em,
day(endDate) as ed
FROM batches
WHERE batchID IN ' . $inClause;
$batchInfoP = $dbc->prepare($batchInfoQ);
$batchInfoR = $dbc->execute($batchInfoP, $inArgs);
$bName = "";
while ($batchInfoW = $dbc->fetchRow($batchInfoR)) {
$bName .= $batchInfoW['batchName'] . " ";
if (empty($bStart)) {
$bStart = sprintf("%d-%02d-%02d", $batchInfoW['sy'], $batchInfoW['sm'], $batchInfoW['sd']);
}
if (empty($bEnd)) {
$bEnd = sprintf("%d-%02d-%02d", $batchInfoW['ey'], $batchInfoW['em'], $batchInfoW['ed']);
}
}
$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);
$salesBatchQ = "\n SELECT d.upc, \n p.description, \n l.floorSectionID,\n f.name AS location,\n SUM(d.total) AS sales, " . DTrans::sumQuantity('d') . " AS quantity, \n SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'INNER') . "\n LEFT JOIN prodPhysicalLocation AS l ON l.upc=p.upc\n LEFT JOIN FloorSections as f ON f.floorSectionID=l.floorSectionID\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc IN ({$in_sql})\n GROUP BY d.upc, \n p.description\n 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['sales']);
$record[] = sprintf('%.2f', $row['quantity']);
$record[] = $row['rings'];
$record[] = $row['location'] === null ? '' : $row['location'];
$ret[] = $record;
}
return $ret;
}
