/** 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); }