/**
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;
}
/**
Do whatever the service is supposed to do.
Should override this.
@param $args array of data
@return an array of data
*/
public function run($args = array())
{
$ret = array();
if (!property_exists($args, 'upc')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs type');
return $ret;
}
if (!is_array($args->upc)) {
$args->upc = array($args->upc);
}
$dbc = \FannieDB::get(\FannieConfig::config('OP_DB'));
$storeID = \FannieConfig::get('STORE_ID');
/**
In "fast" mode, look up the items and run UPDATE queries
on each lane. This reduces overhead substantially but will
overlook brand-new items since there's no check whether the
item exists on the lane.
If "fast" is not specified, each UPC record is copied to the
lane exactly using models. This mode is preferrable unless
performance becomes an issue.
*/
if (property_exists($args, 'fast')) {
$upc_data = array();
$query = '
SELECT normal_price,
pricemethod,
quantity,
groupprice,
special_price,
specialpricemethod,
specialquantity,
specialgroupprice,
discounttype,
mixmatchcode,
department,
tax,
foodstamp,
discount,
qttyEnforced,
idEnforced,
inUse,
upc
FROM products
WHERE store_id=?
AND upc IN (';
$params = array($storeID);
foreach ($args->upc as $upc) {
$query .= '?,';
$params[] = \BarcodeLib::padUPC($upc);
}
$query = substr($query, 0, strlen($query) - 1);
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $params);
while ($w = $dbc->fetchRow($result)) {
$upc_data[$w['upc']] = $w;
}
$updateQ = '
UPDATE products AS p SET
p.normal_price = ?,
p.pricemethod = ?,
p.quantity = ?,
p.groupprice = ?,
p.special_price = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.department = ?,
p.tax = ?,
p.foodstamp = ?,
p.discount=?,
p.qttyEnforced=?,
p.idEnforced=?,
p.inUse=?
WHERE p.upc = ?';
$FANNIE_LANES = \FannieConfig::config('LANES');
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$lane_sql = new \SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upc_data as $upc => $data) {
$lane_args = array($data['normal_price'], $data['pricemethod'], $data['quantity'], $data['groupprice'], $data['special_price'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['department'], $data['tax'], $data['foodstamp'], $data['discount'], $data['qttyEnforced'], $data['idEnforced'], $data['inUse'], $upc);
$lane_sql->execute($updateP, $lane_args);
}
}
} else {
$product = new \ProductsModel($dbc);
$ret['synced'] = array();
foreach ($args->upc as $upc) {
$upc = \BarcodeLib::padUPC($upc);
$product->upc($upc);
$product->store_id($storeID);
if ($product->load()) {
$product->pushToLanes();
$ret['synced'][] = $upc;
}
}
}
return $ret;
}
/**
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);
}
