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; }