/**
Fetch data for the specified report
@param [string] $report_class_name name of report
@param [FannieConfig] $config current configuration
@param [SQLManager] $connection database connection
@return [array] report records or [boolean] false
if this source cannot handle the request
*/
public function fetchReportData($report_class_name, \FannieConfig $config, \SQLManager $connection)
{
$date1 = \FormLib::get_form_value('date1', date('Y-m-d'));
$date2 = \FormLib::get_form_value('date2', date('Y-m-d'));
$type = \FormLib::get_form_value('report-basis', 'Purchases');
$exclude = \FormLib::get_form_value('excludes', '');
if ($type == 'Join Date') {
return false;
}
$ex = preg_split('/\\D+/', $exclude, 0, PREG_SPLIT_NO_EMPTY);
$exCondition = '';
$exArgs = array();
foreach ($ex as $num) {
$exCondition .= '?,';
$exArgs[] = $num;
}
$exCondition = substr($exCondition, 0, strlen($exCondition) - 1);
$originalDB = $connection->defaultDatabase();
$plugin_settings = $config->get('PLUGIN_SETTINGS');
$connection->selectDB($plugin_settings['WarehouseDatabase']);
$query = "\n SELECT \n CASE WHEN m.zip='' THEN 'none' ELSE m.zip END as zipcode,\n COUNT(*) as num_trans, \n SUM(total) as spending,\n COUNT(DISTINCT s.card_no) as uniques\n FROM sumMemSalesByDay AS s \n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "meminfo AS m ON s.card_no=m.card_no \n WHERE ";
if (!empty($exArgs)) {
$query .= "s.card_no NOT IN ({$exCondition}) AND ";
}
$query .= "s.date_id BETWEEN ? AND ?\n GROUP BY zipcode\n ORDER BY SUM(total) DESC";
$exArgs[] = $this->dateToID($date1);
$exArgs[] = $this->dateToID($date2);
$prep = $connection->prepare($query);
$result = $connection->execute($prep, $exArgs);
while ($row = $connection->fetchRow($result)) {
$record = array($row['zipcode'], $row['num_trans'], $row['uniques'], $row['spending']);
$data[] = $record;
}
$connection->setDefaultDB($originalDB);
return $data;
}
/**
Fetch data for the specified report
@param [string] $report_class_name name of report
@param [FannieConfig] $config current configuration
@param [SQLManager] $connection database connection
@return [array] report records or [boolean] false
if this source cannot handle the request
*/
public function fetchReportData($report_class_name, \FannieConfig $config, \SQLManager $connection)
{
$date1 = \FormLib::get_form_value('date1', date('Y-m-d'));
if ($date1 == date('Y-m-d')) {
// warehouse cannot handle current day requests
return false;
}
$originalDB = $connection->defaultDatabase();
$plugin_settings = $config->get('PLUGIN_SETTINGS');
$connection->selectDB($plugin_settings['WarehouseDatabase']);
$args = array($this->dateToID($date1));
$reconciliation = array(array('Tenders', 0.0), array('Sales', 0.0), array('Discounts', 0.0), array('Tax', 0.0));
$prep = $connection->prepare('
SELECT t.TenderName,
s.quantity,
s.total
FROM sumTendersByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'tenders AS t
ON s.trans_subtype=t.TenderCode
WHERE date_id=?
ORDER BY t.TenderName');
$res = $connection->execute($prep, $args);
$tenders = array();
while ($w = $connection->fetchRow($res)) {
$tenders[] = array($w['TenderName'], $w['quantity'], $w['total']);
$reconciliation[0][1] += $w['total'];
}
/**
Always join into department settings twice
but swap priority depening on user request
*/
$then_prefix = 'a';
$now_prefix = 'b';
if (\FormLib::get('report-departments') == 'Current') {
$then_prefix = 'b';
$now_prefix = 'a';
}
$prep = $connection->prepare('
SELECT COALESCE(a.super_name, b.super_name) AS super_name,
SUM(s.quantity) AS quantity,
SUM(s.total) AS total
FROM sumRingSalesByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'products AS p
ON s.upc=p.upc
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'MasterSuperDepts AS ' . $then_prefix . '
ON s.department=' . $then_prefix . '.dept_ID
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'MasterSuperDepts AS ' . $now_prefix . '
ON p.department=' . $now_prefix . '.dept_ID
WHERE date_id=?
GROUP BY COALESCE(a.super_name, b.super_name)
ORDER BY COALESCE(a.super_name, b.super_name)');
$res = $connection->execute($prep, $args);
$sales = array();
while ($w = $connection->fetchRow($res)) {
$sales[] = array($w['super_name'], $w['quantity'], $w['total']);
$reconciliation[1][1] += $w['total'];
}
$prep = $connection->prepare('
SELECT m.memDesc,
s.transCount AS quantity,
s.total AS total
FROM sumDiscountsByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'memtype AS m
ON s.memType=m.memtype
WHERE s.date_id=?
ORDER BY m.memDesc');
$res = $connection->execute($prep, $args);
$discounts = array();
while ($w = $connection->fetchRow($res)) {
$discounts[] = array($w['memDesc'], $w['quantity'], $w['total']);
$reconciliation[2][1] += $w['total'];
}
$dtrans = \DTransactionsModel::selectDTrans($date1);
$dlog = \DTransactionsModel::selectDlog($date1);
$dates = array($date1 . ' 00:00:00', $date1 . ' 23:59:59');
$lineItemQ = $connection->prepare("\n SELECT description,\n SUM(regPrice) AS ttl\n FROM {$dtrans} AS d\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . \DTrans::isNotTesting('d') . "\n GROUP BY d.description\n ");
$lineItemR = $connection->execute($lineItemQ, $dates);
$taxes = array();
while ($lineItemW = $connection->fetchRow($lineItemR)) {
$taxes[] = array($lineItemW['description'] . ' (est. owed)', sprintf('%.2f', $lineItemW['ttl']));
}
$taxSumQ = $connection->prepare("SELECT sum(total) as tax_collected\n FROM {$dlog} as d \n WHERE d.tdate BETWEEN ? AND ?\n AND (d.upc = 'tax')\n GROUP BY d.upc");
$taxR = $connection->execute($taxSumQ, $dates);
while ($taxW = $connection->fetch_row($taxR)) {
$taxes[] = array('Total Tax Collected', round($taxW['tax_collected'], 2));
$reconciliation[3][1] += $taxW['tax_collected'];
}
$prep = $connection->prepare('
SELECT m.memDesc,
COUNT(*) AS numTrans,
SUM(retailQty + nonRetailQty) AS totalItems,
AVG(retailQty + nonRetailQty) AS avgItems,
SUM(retailTotal + nonRetailTotal) AS total,
AVG(retailTotal + nonRetailTotal) AS avg
FROM transactionSummary AS t
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'memtype AS m
ON t.memType=m.memtype
WHERE date_id=?
GROUP BY m.memDesc
ORDER BY m.memDesc');
$res = $connection->execute($prep, $args);
$transactions = array();
while ($w = $connection->fetchRow($res)) {
$transactions[] = array($w['memDesc'], $w['numTrans'], sprintf('%.2f', $w['totalItems']), sprintf('%.2f', $w['avgItems']), sprintf('%.2f', $w['total']), sprintf('%.2f', $w['avg']));
}
$ret = preg_match_all("/[0-9]+/", $config->get('EQUITY_DEPARTMENTS'), $depts);
$equity = array();
if ($ret != 0) {
/* equity departments exist */
$depts = array_pop($depts);
$dlist = "(";
foreach ($depts as $d) {
$dates[] = $d;
// add query param
$dlist .= '?,';
}
$dlist = substr($dlist, 0, strlen($dlist) - 1) . ")";
$equityQ = $connection->prepare("\n SELECT d.card_no,\n t.dept_name, \n sum(total) as total \n FROM {$dlog} as d\n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "departments as t ON d.department = t.dept_no\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department IN {$dlist}\n GROUP BY d.card_no, \n t.dept_name \n ORDER BY d.card_no, \n t.dept_name");
$equityR = $connection->execute($equityQ, $dates);
while ($equityW = $connection->fetchRow($equityR)) {
$record = array($equityW['card_no'], $equityW['dept_name'], sprintf('%.2f', $equityW['total']));
$equity[] = $record;
}
}
$connection->setDefaultDB($originalDB);
return array($tenders, $sales, $discounts, $taxes, $reconciliation, $transactions, $equity);
}
