function fetch_report_data()
{
$dbc = $this->connection;
$dbc->setDefaultDB($this->config->get('OP_DB'));
$d1 = $this->form->date1;
$d2 = $this->form->date2;
$dates = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
$today = date('Ymd');
$data = array();
$dlog = DTransactionsModel::selectDlog($d1);
$tenderQ = $dbc->prepare("\n SELECT TenderName,\n COUNT(d.total) AS num,\n SUM(d.total) as total,\n t.SalesCode\n FROM {$dlog} AS d\n LEFT JOIN tenders as t ON d.trans_subtype=t.TenderCode\n WHERE d.tdate BETWEEN ? AND ?\n AND d.trans_type = 'T'\n AND d.total <> 0\n GROUP BY t.TenderName,\n t.SalesCode \n ORDER BY TenderName");
$tenderR = $dbc->execute($tenderQ, $dates);
while ($tenderW = $dbc->fetch_row($tenderR)) {
$credit = $tenderW['total'] <= 0 ? abs($tenderW['total']) : 0.0;
$debit = $tenderW['total'] > 0 ? $tenderW['total'] : 0.0;
$data[] = array($today, $tenderW['SalesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $tenderW['TenderName']);
}
$salesQ = '
SELECT t.salesCode AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN departments AS t ON d.department=t.dept_no
WHERE d.department <> 0
AND d.trans_type <> \'T\'
AND d.tdate BETWEEN ? AND ?
GROUP BY t.salesCode
ORDER BY t.salesCode';
$salesP = $dbc->prepare($salesQ);
$salesR = $dbc->execute($salesP, $dates);
$report = array();
while ($salesW = $dbc->fetch_row($salesR)) {
$credit = $salesW['total'] < 0 ? abs($salesW['total']) : 0.0;
$debit = $salesW['total'] >= 0 ? $salesW['total'] : 0.0;
$data[] = array($today, $salesW['category'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), 'Sales ' . $salesW['category']);
}
$discQ = $dbc->prepare("\n SELECT m.memDesc, \n SUM(d.total) AS total,\n count(*) AS num,\n m.salesCode\n FROM {$dlog} d \n LEFT JOIN memtype m ON d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc = 'DISCOUNT'\n AND total <> 0\n GROUP BY m.memDesc,\n m.salesCode \n ORDER BY m.memDesc");
$discR = $dbc->execute($discQ, $dates);
while ($discW = $dbc->fetch_row($discR)) {
$credit = $discW['total'] <= 0 ? abs($discW['total']) : 0.0;
$debit = $discW['total'] > 0 ? $discW['total'] : 0.0;
$data[] = array($today, $discW['salesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $discW['memDesc'] . ' Discount');
}
$report = array();
$trans = DTransactionsModel::selectDTrans($d1);
$lineItemQ = $dbc->prepare("\n SELECT d.description,\n SUM(regPrice) AS ttl,\n t.salesCode\n FROM {$trans} AS d\n LEFT JOIN taxrates AS t ON d.numflag=t.id\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . DTrans::isNotTesting('d') . "\n GROUP BY d.description\n ");
$lineItemR = $dbc->execute($lineItemQ, $dates);
while ($lineItemW = $dbc->fetch_row($lineItemR)) {
$credit = $lineItemW['ttl'] < 0 ? abs($lineItemW['ttl']) : 0.0;
$debit = $lineItemW['ttl'] >= 0 ? $lineItemW['ttl'] : 0.0;
$data[] = array($today, $lineItemW['salesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $lineItemW['description'] . ' tax');
}
return $data;
}
/**
Lots of options on this report.
*/
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get_form_value('deptStart', '');
$deptEnd = FormLib::get_form_value('deptEnd', '');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get_form_value('buyer', '');
$groupby = FormLib::get_form_value('sort', 'PLU');
$store = FormLib::get('store', 0);
$superP = $dbc->prepare('SELECT dept_ID FROM superdepts WHERE superID=?');
/**
Build a WHERE condition for later.
Superdepartment (buyer) takes precedence over
department and negative values have special
meaning
Extra lookup to write condition in terms of
transaction.department seems to result in
better index utilization and faster queries
*/
$filter_condition = 't.department BETWEEN ? AND ?';
$args = array($deptStart, $deptEnd);
if (count($deptMulti) > 0) {
$filter_condition = 't.department IN (';
$args = array();
foreach ($deptMulti as $d) {
$filter_condition .= '?,';
$args[] = $d;
}
$filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')';
}
if ($buyer !== "" && $buyer > 0) {
$filter_condition .= ' AND s.superID=? ';
$args[] = $buyer;
/*
$superR = $dbc->execute($superP, array($buyer));
$filter_condition = 't.department IN (';
$args = array();
while ($superW = $dbc->fetch_row($superR)) {
$filter_condition .= '?,';
$args[] = $superW['dept_ID'];
}
$filter_condition = substr($filter_condition, 0, strlen($filter_condition)-1) . ')';
$filter_condition .= ' AND s.superID=?';
$args[] = $buyer;
*/
} else {
if ($buyer !== "" && $buyer == -1) {
$filter_condition = "1=1";
$args = array();
} else {
if ($buyer !== "" && $buyer == -2) {
$superR = $dbc->execute($superP, array(0));
$filter_condition = 't.department NOT IN (0,';
$args = array();
while ($superW = $dbc->fetch_row($superR)) {
$filter_condition .= '?,';
$args[] = $superW['dept_ID'];
}
$filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')';
$filter_condition .= ' AND s.superID <> 0';
}
}
}
/**
* Provide more WHERE conditions to filter irrelevant
* transaction records, as a stop-gap until this is
* handled more uniformly across the application.
*/
$filter_transactions = "t.trans_status NOT IN ('D','X','Z')\n AND t.emp_no <> 9999\n AND t.register_no <> 99";
$filter_transactions = DTrans::isValid() . ' AND ' . DTrans::isNotTesting();
/**
Select a summary table. For UPC results, per-unique-ring
summary is needed. For date/dept/weekday results the
per-department summary is fine (and a smaller table)
*/
$dlog = DTransactionsModel::selectDlog($date1, $date2);
/**
Build an appropriate query depending on the grouping option
*/
$query = "";
$superTable = $buyer !== "" && $buyer > 0 ? 'superdepts' : 'MasterSuperDepts';
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
$args[] = $store;
switch ($groupby) {
case 'PLU':
$query = "SELECT t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END as description, \n SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings," . DTrans::sumQuantity('t') . " as qty,\n SUM(t.total) AS total,\n d.dept_no,d.dept_name,s.superID,\n COALESCE(v.vendorName,x.distributor) AS distributor\n FROM {$dlog} as t " . DTrans::joinProducts() . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON t.department = s.dept_ID\n LEFT JOIN prodExtra as x on t.upc = x.upc\n LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID\n WHERE {$filter_condition}\n AND t.trans_type IN ('I', 'D')\n AND tdate BETWEEN ? AND ?\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END,\n CASE WHEN t.trans_status = 'R' THEN 'Refund' ELSE 'Sale' END,\n d.dept_no,d.dept_name,s.superID,distributor ORDER BY SUM(t.total) DESC";
break;
case 'Department':
$query = "SELECT t.department,d.dept_name," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.department,d.dept_name ORDER BY SUM(total) DESC";
break;
case 'Date':
$query = "SELECT year(tdate),month(tdate),day(tdate)," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales ,\n MAX(" . $dbc->dayofweek('tdate') . ") AS dow\n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department\n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY year(tdate),month(tdate),day(tdate) \n ORDER BY year(tdate),month(tdate),day(tdate)";
break;
case 'Weekday':
$cols = $dbc->dayofweek("tdate") . ",CASE \n WHEN " . $dbc->dayofweek("tdate") . "=1 THEN 'Sun'\n WHEN " . $dbc->dayofweek("tdate") . "=2 THEN 'Mon'\n WHEN " . $dbc->dayofweek("tdate") . "=3 THEN 'Tue'\n WHEN " . $dbc->dayofweek("tdate") . "=4 THEN 'Wed'\n WHEN " . $dbc->dayofweek("tdate") . "=5 THEN 'Thu'\n WHEN " . $dbc->dayofweek("tdate") . "=6 THEN 'Fri'\n WHEN " . $dbc->dayofweek("tdate") . "=7 THEN 'Sat'\n ELSE 'Err' END";
$query = "SELECT {$cols}," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY {$cols}\n ORDER BY " . $dbc->dayofweek('tdate');
break;
}
/**
Copy the results into an array. Date requires a
special case to combine year, month, and day into
a single field
*/
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
if ($groupby == "Date") {
$record[] = $row[1] . "/" . $row[2] . "/" . $row[0];
$record[] = date('l', strtotime($record[0]));
$record[] = sprintf('%.2f', $row[3]);
$record[] = sprintf('%.2f', $row[4]);
} else {
for ($i = 0; $i < $dbc->num_fields($result); $i++) {
if (preg_match('/^\\d+\\.\\d+$/', $row[$i])) {
$row[$i] = sprintf('%.2f', $row[$i]);
}
$record[] .= $row[$i];
}
}
$ret[] = $record;
}
return $ret;
}
echo '<b>RRR Coupons Redeemed</b>';
select_to_table($queryRRR, $args, 0, 'ffffff');
echo '</font>';
echo "</font>\n </body>\n </html>";
$output = ob_get_contents();
\COREPOS\Fannie\API\data\DataCache::putFile("monthly", $output);
ob_end_clean();
}
echo $output;
$newTaxQ = 'SELECT description,
SUM(regPrice) AS ttl,
numflag AS taxID
FROM is4c_trans.transarchive
WHERE datetime BETWEEN ? AND ?
AND upc=\'TAXLINEITEM\'
AND ' . DTrans::isNotTesting() . '
GROUP BY taxID, description';
$sql = FannieDB::get($FANNIE_OP_DB);
$prep = $sql->prepare($newTaxQ);
$res = $sql->execute($prep, $args);
$collected = array(1 => 0.0, 2 => 0.0);
while ($row = $sql->fetch_row($res)) {
$collected[$row['taxID']] = $row['ttl'];
}
$state = 0.06875000000000001;
$city = 0.01;
$deli = 0.0225;
$county = 0.005;
echo '<table border="1" cellspacing="0" cellpadding="4">';
echo '<tr><th>Tax Collected on Regular rate items</th>
<th>' . sprintf('%.2f', $collected[1]) . '</th>
function fetch_report_data()
{
global $FANNIE_OP_DB, $FANNIE_ARCHIVE_DB, $FANNIE_EQUITY_DEPARTMENTS, $FANNIE_COOP_ID;
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$d1 = $this->form->date1;
$dates = array($d1 . ' 00:00:00', $d1 . ' 23:59:59');
$data = array();
if (isset($FANNIE_COOP_ID) && $FANNIE_COOP_ID == 'WEFC_Toronto') {
$shrinkageUsers = " AND d.card_no not between 99900 and 99998";
} else {
$shrinkageUsers = "";
}
$reconciliation = array('Tenders' => 0.0, 'Sales' => 0.0, 'Discounts' => 0.0, 'Tax' => 0.0);
$dlog = DTransactionsModel::selectDlog($d1);
$tenderQ = $dbc->prepare_statement("SELECT \n TenderName,count(d.total),sum(d.total) as total\n FROM {$dlog} as d,\n {$FANNIE_OP_DB}.tenders as t \n WHERE d.tdate BETWEEN ? AND ?\n AND d.trans_subtype = t.TenderCode\n AND d.total <> 0{$shrinkageUsers}\n GROUP BY t.TenderName ORDER BY TenderName");
$tenderR = $dbc->exec_statement($tenderQ, $dates);
$report = array();
while ($tenderW = $dbc->fetch_row($tenderR)) {
$record = array($tenderW['TenderName'], $tenderW[1], sprintf('%.2f', $tenderW['total']));
$report[] = $record;
$reconciliation['Tenders'] += $tenderW['total'];
}
$data[] = $report;
$salesQ = $dbc->prepare_statement("SELECT m.super_name,sum(d.quantity) as qty,\n sum(d.total) as total\n FROM {$dlog} AS d LEFT JOIN\n {$FANNIE_OP_DB}.MasterSuperDepts AS m ON d.department=m.dept_ID\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department <> 0 AND d.trans_type <> 'T'{$shrinkageUsers}\n GROUP BY m.super_name ORDER BY m.super_name");
$salesQ = '';
switch (FormLib::get('sales-by')) {
case 'Department':
$salesQ = '
SELECT t.dept_name AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN departments AS t ON d.department=t.dept_no
WHERE d.department <> 0
AND d.trans_type <> \'T\' ' . $shrinkageUsers . '
AND d.tdate BETWEEN ? AND ?
GROUP BY t.dept_name
ORDER BY t.dept_name';
break;
case 'Sales Code':
$salesQ = '
SELECT t.salesCode AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN departments AS t ON d.department=t.dept_no
WHERE d.department <> 0
AND d.trans_type <> \'T\' ' . $shrinkageUsers . '
AND d.tdate BETWEEN ? AND ?
GROUP BY t.salesCode
ORDER BY t.salesCode';
break;
case 'Super Department':
default:
$salesQ = '
SELECT m.super_name AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN MasterSuperDepts AS m ON d.department=m.dept_ID
WHERE d.department <> 0
AND d.trans_type <> \'T\' ' . $shrinkageUsers . '
AND d.tdate BETWEEN ? AND ?
GROUP BY m.super_name
ORDER BY m.super_name';
break;
}
$salesP = $dbc->prepare($salesQ);
$salesR = $dbc->exec_statement($salesP, $dates);
$report = array();
while ($salesW = $dbc->fetch_row($salesR)) {
$record = array($salesW['category'], sprintf('%.2f', $salesW['qty']), sprintf('%.2f', $salesW['total']));
$report[] = $record;
$reconciliation['Sales'] += $salesW['total'];
}
$data[] = $report;
$discQ = $dbc->prepare_statement("SELECT m.memDesc, SUM(d.total) AS Discount,count(*)\n FROM {$dlog} d \n INNER JOIN memtype m ON d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc = 'DISCOUNT'{$shrinkageUsers}\n AND total <> 0\n GROUP BY m.memDesc ORDER BY m.memDesc");
$discR = $dbc->exec_statement($discQ, $dates);
$report = array();
while ($discW = $dbc->fetch_row($discR)) {
$record = array($discW['memDesc'], $discW[2], $discW[1]);
$report[] = $record;
$reconciliation['Discounts'] += $discW['Discount'];
}
$data[] = $report;
$report = array();
$trans = DTransactionsModel::selectDTrans($d1);
$lineItemQ = $dbc->prepare("\n SELECT description,\n SUM(regPrice) AS ttl\n FROM {$trans} AS d\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . DTrans::isNotTesting('d') . "\n GROUP BY d.description\n ");
$lineItemR = $dbc->execute($lineItemQ, $dates);
while ($lineItemW = $dbc->fetch_row($lineItemR)) {
$record = array($lineItemW['description'] . ' (est. owed)', sprintf('%.2f', $lineItemW['ttl']));
$report[] = $record;
}
$taxSumQ = $dbc->prepare_statement("SELECT sum(total) as tax_collected\n FROM {$dlog} as d \n WHERE d.tdate BETWEEN ? AND ?\n AND (d.upc = 'tax'){$shrinkageUsers}\n GROUP BY d.upc");
$taxR = $dbc->exec_statement($taxSumQ, $dates);
while ($taxW = $dbc->fetch_row($taxR)) {
$record = array('Total Tax Collected', round($taxW['tax_collected'], 2));
$report[] = $record;
$reconciliation['Tax'] = $taxW['tax_collected'];
}
$data[] = $report;
$report = array();
foreach ($reconciliation as $type => $amt) {
$report[] = array($type, sprintf('%.2f', $amt));
}
$data[] = $report;
$transQ = $dbc->prepare_statement("select q.trans_num,sum(q.quantity) as items,transaction_type, sum(q.total) from\n (\n select trans_num,card_no,quantity,total,\n m.memDesc as transaction_type\n from {$dlog} as d\n left join memtype as m on d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND trans_type in ('I','D')\n AND upc <> 'RRR'{$shrinkageUsers}\n ) as q \n group by q.trans_num,q.transaction_type");
$transR = $dbc->exec_statement($transQ, $dates);
$transinfo = array();
while ($row = $dbc->fetch_array($transR)) {
if (!isset($transinfo[$row[2]])) {
$transinfo[$row[2]] = array(0, 0.0, 0.0, 0.0, 0.0);
}
$transinfo[$row[2]][0] += 1;
$transinfo[$row[2]][1] += $row[1];
$transinfo[$row[2]][3] += $row[3];
}
$tSum = 0;
$tItems = 0;
$tDollars = 0;
foreach (array_keys($transinfo) as $k) {
$transinfo[$k][2] = round($transinfo[$k][1] / $transinfo[$k][0], 2);
$transinfo[$k][4] = round($transinfo[$k][3] / $transinfo[$k][0], 2);
$tSum += $transinfo[$k][0];
$tItems += $transinfo[$k][1];
$tDollars += $transinfo[$k][3];
}
$report = array();
foreach ($transinfo as $title => $info) {
array_unshift($info, $title);
$report[] = $info;
}
$data[] = $report;
$ret = preg_match_all("/[0-9]+/", $FANNIE_EQUITY_DEPARTMENTS, $depts);
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 = $dbc->prepare_statement("SELECT d.card_no,t.dept_name, sum(total) as total \n FROM {$dlog} as d\n LEFT JOIN {$FANNIE_OP_DB}.departments as t ON d.department = t.dept_no\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department IN {$dlist}{$shrinkageUsers}\n GROUP BY d.card_no, t.dept_name ORDER BY d.card_no, t.dept_name");
$equityR = $dbc->exec_statement($equityQ, $dates);
$report = array();
while ($equityW = $dbc->fetch_row($equityR)) {
$record = array($equityW['card_no'], $equityW['dept_name'], sprintf('%.2f', $equityW['total']));
$report[] = $record;
}
$data[] = $report;
}
return $data;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->setDefaultDB($this->config->get('OP_DB'));
$d1 = $this->form->date1;
$d2 = $this->form->date2;
$dates = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
$data = array();
$reconciliation = array('Tenders' => 0.0, 'Sales' => 0.0, 'Discounts' => 0.0, 'Tax' => 0.0);
$dlog = DTransactionsModel::selectDlog($d1);
$tenderQ = $dbc->prepare("\n SELECT TenderName,\n COUNT(d.total) AS num,\n SUM(d.total) as total\n FROM {$dlog} AS d\n INNER JOIN tenders as t ON d.trans_subtype=t.TenderCode\n WHERE d.tdate BETWEEN ? AND ?\n AND d.trans_type = 'T'\n AND d.total <> 0\n GROUP BY t.TenderName ORDER BY TenderName");
$tenderR = $dbc->execute($tenderQ, $dates);
$report = array();
while ($tenderW = $dbc->fetch_row($tenderR)) {
$record = array($tenderW['TenderName'], $tenderW[1], sprintf('%.2f', $tenderW['total']));
$report[] = $record;
$reconciliation['Tenders'] += $tenderW['total'];
}
$data[] = $report;
$salesQ = '';
switch (FormLib::get('sales-by')) {
case 'Department':
$salesQ = '
SELECT t.dept_name AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN departments AS t ON d.department=t.dept_no
WHERE d.department <> 0
AND d.trans_type <> \'T\'
AND d.tdate BETWEEN ? AND ?
GROUP BY t.dept_name
ORDER BY t.dept_name';
break;
case 'Sales Code':
$salesQ = '
SELECT t.salesCode AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN departments AS t ON d.department=t.dept_no
WHERE d.department <> 0
AND d.trans_type <> \'T\'
AND d.tdate BETWEEN ? AND ?
GROUP BY t.salesCode
ORDER BY t.salesCode';
break;
case 'Super Department':
default:
$salesQ = '
SELECT m.super_name AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN MasterSuperDepts AS m ON d.department=m.dept_ID
WHERE d.department <> 0
AND d.trans_type <> \'T\'
AND d.tdate BETWEEN ? AND ?
GROUP BY m.super_name
ORDER BY m.super_name';
break;
}
$salesP = $dbc->prepare($salesQ);
$salesR = $dbc->execute($salesP, $dates);
$report = array();
while ($salesW = $dbc->fetch_row($salesR)) {
$record = array($salesW['category'], sprintf('%.2f', $salesW['qty']), sprintf('%.2f', $salesW['total']));
$report[] = $record;
$reconciliation['Sales'] += $salesW['total'];
}
$data[] = $report;
$discQ = $dbc->prepare("\n SELECT m.memDesc, \n SUM(d.total) AS Discount,\n count(*) AS num\n FROM {$dlog} d \n INNER JOIN memtype m ON d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc = 'DISCOUNT'\n AND total <> 0\n GROUP BY m.memDesc \n ORDER BY m.memDesc");
$discR = $dbc->execute($discQ, $dates);
$report = array();
while ($discW = $dbc->fetch_row($discR)) {
$record = array($discW['memDesc'], $discW[2], $discW[1]);
$report[] = $record;
$reconciliation['Discounts'] += $discW['Discount'];
}
$data[] = $report;
$report = array();
$trans = DTransactionsModel::selectDTrans($d1);
$lineItemQ = $dbc->prepare("\n SELECT description,\n SUM(regPrice) AS ttl\n FROM {$trans} AS d\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . DTrans::isNotTesting('d') . "\n GROUP BY d.description\n ");
$lineItemR = $dbc->execute($lineItemQ, $dates);
while ($lineItemW = $dbc->fetch_row($lineItemR)) {
$record = array($lineItemW['description'] . ' (est. owed)', sprintf('%.2f', $lineItemW['ttl']));
$report[] = $record;
}
$taxSumQ = $dbc->prepare("\n 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 = $dbc->execute($taxSumQ, $dates);
while ($taxW = $dbc->fetch_row($taxR)) {
$record = array('Total Tax Collected', round($taxW['tax_collected'], 2));
$report[] = $record;
$reconciliation['Tax'] = $taxW['tax_collected'];
}
$data[] = $report;
$report = array();
foreach ($reconciliation as $type => $amt) {
$report[] = array($type, sprintf('%.2f', $amt));
}
$data[] = $report;
$ret = preg_match_all("/[0-9]+/", $this->config->get('EQUITY_DEPARTMENTS'), $depts);
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 = $dbc->prepare("\n SELECT d.card_no,\n t.dept_name, \n SUM(total) AS total \n FROM {$dlog} as d\n LEFT JOIN 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, t.dept_name ORDER BY d.card_no, t.dept_name");
$equityR = $dbc->execute($equityQ, $dates);
$report = array();
while ($equityW = $dbc->fetch_row($equityR)) {
$record = array($equityW['card_no'], $equityW['dept_name'], sprintf('%.2f', $equityW['total']));
$report[] = $record;
}
$data[] = $report;
}
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);
}
