function fetch_report_data()
{
global $FANNIE_OP_DB, $FANNIE_COOP_ID;
$d1 = $this->form->date1;
$d2 = $this->form->date2;
$dept = FormLib::get_form_value('dept', 0);
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
// Can dlog views if they include cost.
$dtrans = DTransactionsModel::select_dtrans($d1, $d2);
$datestamp = $dbc->identifier_escape('datetime');
if (isset($FANNIE_COOP_ID) && $FANNIE_COOP_ID == 'WEFC_Toronto') {
$shrinkageUsers = " AND (t.card_no not between 99900 and 99998)";
} else {
$shrinkageUsers = "";
}
// The eventual return value.
$data = array();
$taxNames = array(0 => '');
$tQ = $dbc->prepare_statement("SELECT id, rate, description FROM taxrates WHERE id > 0 ORDER BY id");
$tR = $dbc->exec_statement($tQ);
// Try generating code in this loop for use in SELECT and reporting.
// See SalesAndTaxTodayReport.php
while ($trow = $dbc->fetch_array($tR)) {
$taxNames[$trow['id']] = $trow['description'];
}
/**
Margin column was added to departments but if
deptMargin is present data may not have been migrated
*/
$margin = 'd.margin';
$departments_table = $dbc->tableDefinition('departments');
if ($dbc->tableExists('deptMargin')) {
$margin = 'm.margin';
} elseif (!isset($departments_table['margin'])) {
$margin = '0.00';
}
/* Using department settings at the time of sale.
* I.e. The department# from the transaction.
* If that department# no longer exists or is different then the report will be wrong.
* This does not use a departments table contemporary with the transactions.
* [0]Dept_name [1]Cost, [2]HST, [3]GST, [4]Sales, [x]Qty, [x]superID, [x]super_name
*/
if ($dept == 0) {
// Change varname to sales or totals
$costs = "SELECT\n d.dept_name dname,\n sum(CASE WHEN t.trans_type = 'I' THEN t.cost \n WHEN t.trans_type = 'D' AND {$margin} > 0.00 \n THEN t.total - (t.total * {$margin}) END) AS costs,\n sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,\n sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,\n sum(t.total) AS sales,\n sum(t.quantity) AS qty,\n s.superID AS sid,\n s.super_name AS sname\n FROM\n {$dtrans} AS t LEFT JOIN\n departments AS d ON d.dept_no=t.department LEFT JOIN\n MasterSuperDepts AS s ON t.department=s.dept_ID LEFT JOIN\n taxrates AS x ON t.tax=x.id ";
if ($margin == 'm.margin') {
$costs .= " LEFT JOIN deptMargin AS m ON t.department=m.dept_id ";
}
$costs .= "\n WHERE \n ({$datestamp} BETWEEN ? AND ?)\n AND (s.superID > 0 OR s.superID IS NULL) \n AND t.trans_type in ('I','D')\n AND t.trans_status not in ('D','X','Z')\n AND t.emp_no not in (9999){$shrinkageUsers}\n AND t.register_no != 99\n AND t.upc != 'DISCOUNT'\n AND t.trans_subtype not in ('CP','IC')\n GROUP BY\n s.superID, s.super_name, d.dept_name, t.department\n ORDER BY\n s.superID, t.department";
} elseif ($dept == 1) {
$costs = "SELECT\n CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name END AS dname,\n sum(CASE WHEN t.trans_type = 'I' THEN t.cost \n WHEN t.trans_type = 'D' AND {$margin} > 0.00 \n THEN t.total - (t.total * {$margin}) END) AS costs,\n sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,\n sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,\n sum(t.total) AS sales,\n sum(t.quantity) AS qty,\n CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID END AS sid,\n CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END AS sname\n FROM\n {$dtrans} AS t LEFT JOIN\n products AS p ON t.upc=p.upc LEFT JOIN\n departments AS d ON d.dept_no=t.department LEFT JOIN\n departments AS e ON p.department=e.dept_no LEFT JOIN\n MasterSuperDepts AS s ON s.dept_ID=p.department LEFT JOIN\n MasterSuperDepts AS r ON r.dept_ID=t.department LEFT JOIN\n taxrates AS x ON t.tax=x.id ";
if ($margin == 'm.margin') {
$costs .= " LEFT JOIN deptMargin AS m ON t.department=m.dept_id ";
}
$costs .= "\n WHERE\n ({$datestamp} BETWEEN ? AND ?)\n AND (s.superID > 0 OR (s.superID IS NULL AND r.superID > 0)\n OR (s.superID IS NULL AND r.superID IS NULL))\n AND t.trans_type in ('I','D')\n AND t.trans_status not in ('D','X','Z')\n AND t.emp_no not in (9999){$shrinkageUsers}\n AND t.register_no != 99\n AND t.upc != 'DISCOUNT'\n AND t.trans_subtype not in ('CP','IC')\n GROUP BY\n CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,\n CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END,\n CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end,\n CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end\n ORDER BY\n CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,\n CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end";
}
$costsP = $dbc->prepare_statement($costs);
$costArgs = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
$costsR = $dbc->exec_statement($costsP, $costArgs);
// Array in which totals used in the report are accumulated.
$supers = array();
$curSuper = 0;
$grandTotal = 0;
$this->grandCostsTotal = 0;
$this->grandSalesTotal = 0;
$this->grandTax1Total = 0;
$this->grandTax2Total = 0;
while ($row = $dbc->fetch_array($costsR)) {
if ($curSuper != $row['sid']) {
$curSuper = $row['sid'];
}
if (!isset($supers[$curSuper])) {
$supers[$curSuper] = array('name' => $row['sname'], 'qty' => 0.0, 'costs' => 0.0, 'sales' => 0.0, 'taxes1' => 0.0, 'taxes2' => 0.0, 'depts' => array());
}
$supers[$curSuper]['qty'] += $row['qty'];
$supers[$curSuper]['costs'] += $row['costs'];
$supers[$curSuper]['sales'] += $row['sales'];
$supers[$curSuper]['taxes1'] += $row['taxes1'];
$supers[$curSuper]['taxes2'] += $row['taxes2'];
$this->grandCostsTotal += $row['costs'];
$this->grandSalesTotal += $row['sales'];
$this->grandTax1Total += $row['taxes1'];
$this->grandTax2Total += $row['taxes2'];
// GROUP BY produces 1 row per dept. Values are sums.
$supers[$curSuper]['depts'][] = array('name' => $row['dname'], 'qty' => $row['qty'], 'costs' => $row['costs'], 'sales' => $row['sales'], 'taxes1' => $row['taxes1'], 'taxes2' => $row['taxes2']);
}
$superCount = 1;
foreach ($supers as $s) {
if ($s['sales'] == 0 && !$this->show_zero) {
$superCount++;
continue;
}
$this->report_headers[] = array("{$s['name']}", 'Qty', 'Costs', '% Costs', 'DeptC%', 'Sales', '% Sales', 'DeptS %', 'Margin %', 'GST', 'HST');
// add department records
$superCostsSum = $s['costs'];
$superSalesSum = $s['sales'];
foreach ($s['depts'] as $d) {
$record = array($d['name'], sprintf('%.2f', $d['qty']), sprintf('$%.2f', $d['costs']));
$costPercent = 'n/a';
if ($this->grandCostsTotal > 0) {
$costPercent = sprintf('%.2f%%', $d['costs'] / $this->grandCostsTotal * 100);
}
$record[] = $costPercent;
$costPercent = 'n/a';
if ($superCostsSum > 0) {
$costPercent = sprintf('%.2f%%', $d['costs'] / $superCostsSum * 100);
}
$record[] = $costPercent;
$record[] = sprintf('$%.2f', $d['sales']);
$salePercent = 'n/a';
if ($this->grandSalesTotal > 0) {
$salePercent = sprintf('%.2f%%', $d['sales'] / $this->grandSalesTotal * 100);
}
$record[] = $salePercent;
$salePercent = 'n/a';
if ($superSalesSum > 0) {
$salePercent = sprintf('%.2f%%', $d['sales'] / $superSalesSum * 100);
}
$record[] = $salePercent;
$margin = 'n/a';
if ($d['sales'] > 0 && $d['costs'] > 0) {
$margin = sprintf('%.2f%%', 100 * ($d['sales'] - $d['costs']) / $d['sales']);
}
$record[] = $margin;
$record[] = sprintf('%.2f', $d['taxes2']);
$record[] = sprintf('%.2f', $d['taxes1']);
$data[] = $record;
}
/* "super record" is a row of totals for the superdept,
* instead of using calculate_footers().
*/
$record = array($s['name'], sprintf('%.2f', $s['qty']), sprintf('$%s', number_format($s['costs'], 2)));
$costPercent = 'n/a';
if ($this->grandCostsTotal > 0) {
$costPercent = sprintf('%.2f%%', $s['costs'] / $this->grandCostsTotal * 100);
}
$record[] = $costPercent;
$record[] = '';
$record[] = sprintf('$%s', number_format($s['sales'], 2));
//sprintf('%.2f',$s['sales']);
$salePercent = 'n/a';
if ($this->grandSalesTotal > 0) {
$salePercent = sprintf('%.2f%%', $s['sales'] / $this->grandSalesTotal * 100);
}
$record[] = $salePercent;
$record[] = '';
$margin = 'n/a';
if ($s['sales'] > 0 && $s['costs'] > 0) {
$margin = sprintf('%.2f%%', 100 * ($s['sales'] - $s['costs']) / $s['sales']);
}
$record[] = $margin;
$record[] = sprintf('%.2f', $s['taxes2']);
$record[] = sprintf('%.2f', $s['taxes1']);
$record['meta'] = FannieReportPage::META_BOLD;
$data[] = $record;
// Rather than start a new report, insert a blank line between superdepts.
$data[] = array('meta' => FannieReportPage::META_BLANK);
if ($superCount < count($supers)) {
$data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
}
$superCount++;
}
/** Discounts applied at the member type level.
*/
$report = array();
/* Headings
*/
$this->report_headers[] = array('MEMBER TYPE', 'Qty', '', '', '', 'Amount', '', '', '', '', '');
$data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
// A row for each type of member.
$dDiscountTotal = 0;
$dQtyTotal = 0;
$discQ = $dbc->prepare("\n SELECT m.memDesc, \n SUM(t.total) AS Discount,\n count(*) AS ct\n FROM {$dtrans} t\n INNER JOIN {$FANNIE_OP_DB}.memtype m ON t.memType = m.memtype\n WHERE ({$datestamp} BETWEEN ? AND ?)\n AND t.upc = 'DISCOUNT'\n AND t.total <> 0\n AND t.trans_status not in ('D','X','Z')\n AND t.emp_no not in (9999){$shrinkageUsers}\n AND t.register_no != 99\n AND t.trans_subtype not in ('CP','IC')\n GROUP BY m.memDesc\n ORDER BY m.memDesc");
$discR = $dbc->exec_statement($discQ, $costArgs);
if ($discR === False) {
$data[] = array("SQL exec on {$dtrans} failed");
} else {
$record = array('', '', '', '', '', '', '', '', '', '', '');
while ($discW = $dbc->fetch_row($discR)) {
$record[0] = $discW['memDesc'];
$record[1] = $discW['ct'];
$dQtyTotal += $discW['ct'];
$record[5] = sprintf('$%.2f', 1 * $discW['Discount']);
$dDiscountTotal += 1 * $discW['Discount'];
$data[] = $record;
}
// Total Footer
$record = array("DISCOUNTS", number_format($dQtyTotal, 0), '', '', '', number_format($dDiscountTotal, 2), '', '', '', '', '');
$record['meta'] = FannieReportPage::META_BOLD;
$data[] = $record;
$data[] = array('meta' => FannieReportPage::META_BLANK);
}
// The discount total is negative.
$this->grandSalesTotal += $dDiscountTotal;
$this->summary_data[] = $report;
// End of Discounts
/** The summary of grand totals proportions for the whole store.
*/
// Headings
$record = array('', '', 'Costs', '', '', 'Sales', 'Profit', '', 'Margin %', isset($taxNames['2']) ? $taxNames['2'] : 'n/a', isset($taxNames['1']) ? $taxNames['1'] : 'n/a');
$record['meta'] = FannieReportPage::META_BOLD;
$data[] = $record;
// Grand totals
$record = array('WHOLE STORE', '', '$ ' . number_format($this->grandCostsTotal, 2), '', '', '$ ' . number_format($this->grandSalesTotal, 2), '$ ' . number_format($this->grandSalesTotal - $this->grandCostsTotal, 2), '');
$margin = 'n/a';
if ($this->grandSalesTotal > 0) {
$margin = number_format(($this->grandSalesTotal - $this->grandCostsTotal) / $this->grandSalesTotal * 100, 2) . ' %';
}
$record[] = $margin;
$record[] = '$ ' . number_format($this->grandTax2Total, 2);
$record[] = '$ ' . number_format($this->grandTax1Total, 2);
$record['meta'] = FannieReportPage::META_BOLD;
$data[] = $record;
$this->grandTTL = $grandTotal;
return $data;
// fetch_report_data()
}