public 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('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$include_sales = FormLib::get('includeSales', 0);
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "SELECT d.upc,\n p.brand,\n p.description,\n d.department,\n t.dept_name,\n SUM(total) AS total,\n SUM(d.cost) AS cost," . DTrans::sumQuantity('d') . " AS qty\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't');
// join only needed with specific buyer
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "WHERE tdate BETWEEN ? AND ?\n AND {$where}\n AND d.cost <> 0 ";
if ($include_sales != 1) {
$query .= "AND d.discounttype=0 ";
}
$query .= "GROUP BY d.upc,p.description,d.department,t.dept_name\n ORDER BY sum(total) DESC";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$data = array();
$sum_total = 0.0;
$sum_cost = 0.0;
while ($row = $dbc->fetch_row($result)) {
$margin = $row['total'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['total'] * 100;
$record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['cost']), sprintf('%.2f', $row['total']), sprintf('%.2f', $margin), sprintf('%.2f', $row['qty'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['qty']));
$sum_total += $row['total'];
$sum_cost += $row['cost'];
$data[] = $record;
}
// go through and add a contribution to margin value
for ($i = 0; $i < count($data); $i++) {
// (item_total - item_cost) / total sales
$contrib = ($data[$i][5] - $data[$i][4]) / $sum_total * 100;
$data[$i][] = sprintf('%.2f', $contrib);
}
return $data;
}
public 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('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$queryAll = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d \n WHERE tdate BETWEEN ? AND ?\n GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)\n ORDER BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
$argsAll = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$querySelected = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d ";
if ($buyer !== '' && $buyer > -1) {
$querySelected .= " LEFT JOIN superdepts AS s ON d.department=s.dept_ID ";
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$querySelected .= " WHERE tdate BETWEEN ? AND ? ";
$argsSel = $argsAll;
if ($buyer !== '') {
if ($buyer == -2) {
$querySelected .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$querySelected .= ' AND s.superID=? ';
$argsSel[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $argsSel) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $argsSel);
$querySelected .= $conditional;
}
$querySelected .= " GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
$dataset = array();
$prep = $dbc->prepare_statement($queryAll);
$result = $dbc->exec_statement($prep, $argsAll);
while ($row = $dbc->fetch_row($result)) {
$datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
$dataset[$datestr] = array('ttl' => $row['trans_count'], 'sub' => 0);
}
$prep = $dbc->prepare_statement($querySelected);
$result = $dbc->exec_statement($prep, $argsSel);
while ($row = $dbc->fetch_row($result)) {
$datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
if (isset($dataset[$datestr])) {
$dataset[$datestr]['sub'] = $row['trans_count'];
}
}
$data = array();
foreach ($dataset as $date => $count) {
$record = array($date, $count['sub'], $count['ttl']);
$record[] = sprintf('%.2f%%', $count['sub'] / $count['ttl'] * 100);
$data[] = $record;
}
return $data;
}
public 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('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "\n SELECT d.upc,\n p.brand,\n p.description," . DTrans::sumQuantity('d') . " AS qty,\n CASE WHEN memDiscount <> 0 AND memType <> 0 THEN unitPrice - memDiscount ELSE unitPrice END as price,\n d.department, \n t.dept_name, \n SUM(total) AS total\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't');
// join only needed with specific buyer
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "\n WHERE tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY d.upc,p.description,price,d.department,t.dept_name\n ORDER BY d.upc";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['price']), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
$data[] = $record;
}
// bold items that sold at multiple prices
for ($i = 0; $i < count($data); $i++) {
if (!isset($data[$i + 1])) {
continue;
}
if ($data[$i][0] == $data[$i + 1][0]) {
$data[$i]['meta'] = FannieReportPage::META_BOLD;
$data[$i + 1]['meta'] = FannieReportPage::META_BOLD;
}
}
return $data;
}
public 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('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array();
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 'p');
$where .= $conditional;
}
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
$query = "SELECT MIN(CASE WHEN a.modified IS NULL THEN p.modified ELSE a.modified END) AS entryDate, \n a.upc, p.description, p.department, d.dept_name\n FROM products AS p INNER JOIN prodUpdate AS a ON a.upc=p.upc\n LEFT JOIN departments AS d ON d.dept_no=p.department ";
// join only needed with specific buyer
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON p.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID ';
}
$query .= "WHERE {$where}\n GROUP BY p.upc,p.description,p.department, d.dept_name\n HAVING entryDate BETWEEN ? AND ?\n ORDER BY entryDate";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['entryDate'], $row['upc'], $row['description'], $row['department'], $row['dept_name']);
$data[] = $record;
}
return $data;
}
public 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('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer > -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
} elseif ($buyer == -2) {
$where .= ' AND s.superID <> 0 ';
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
$tempTables = array('connection' => $dbc, 'clauses' => array(array('sql' => 'trans_type IN (?, ?)', 'params' => array('I', 'D'))));
$dlog = DTransactionsModel::selectDlog($date1, $date2, $tempTables);
$query = "SELECT year(tdate),month(tdate),day(tdate),\n SUM(CASE WHEN trans_type='D' THEN total ELSE 0 END) as total,\n SUM(CASE WHEN trans_type='D' THEN abs(quantity) ELSE 0 END) as qty,\n SUM(CASE WHEN trans_type='D' THEN 1.0 ELSE 0.0 END) /\n SUM(CASE WHEN trans_type IN ('I','D') THEN 1.0 ELSE 0.0 END) as percentage\n FROM {$dlog} as d ";
// join only needed with specific buyer
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "WHERE trans_type IN ('I','D')\n AND tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY year(tdate),month(tdate),day(tdate)\n ORDER BY year(tdate),month(tdate),day(tdate)";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array(sprintf('%d/%d/%d', $row[1], $row[2], $row[0]), sprintf('%.2f', $row['total']), sprintf('%.2f', $row['qty']), sprintf('%.2f%%', $row['percentage'] * 100));
$data[] = $record;
}
return $data;
}
function fetch_report_data()
{
global $FANNIE_OP_DB, $FANNIE_ARCHIVE_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get_form_value('deptStart', 0);
$deptEnd = FormLib::get_form_value('deptEnd', 0);
$deptMulti = FormLib::get('departments', array());
$tempName = "TempNoMove";
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$tempQ = $dbc->prepare_statement("CREATE TABLE {$tempName} (upc varchar(13))");
$dbc->exec_statement($tempQ);
$insQ = $dbc->prepare("\n INSERT INTO {$tempName}\n SELECT d.upc FROM {$dlog} AS d\n WHERE \n d.tdate BETWEEN ? AND ?\n AND d.trans_type='I'\n GROUP BY d.upc");
$dbc->exec_statement($insQ, array($date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$where = ' 1=1 ';
$buyer = FormLib::get('super');
$args = array();
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 'p');
$where .= $conditional;
}
$query = "\n SELECT p.upc,\n p.brand,\n p.description,\n d.dept_no,\n d.dept_name \n FROM products AS p \n LEFT JOIN departments AS d ON p.department=d.dept_no ";
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON p.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID ';
}
$query .= " WHERE p.upc NOT IN (\n SELECT upc FROM {$tempName}\n )\n AND {$where}\n AND p.inUse=1\n ORDER BY p.upc";
$prep = $dbc->prepare($query);
$result = $dbc->exec_statement($prep, $args);
/**
Simple report
Issue a query, build array of results
*/
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
$record[] = $row[0];
$record[] = $row[1];
$record[] = $row[2];
$record[] = $row[3];
$record[] = $row[4];
if ($this->report_format == 'html') {
$record[] = sprintf('<a href="" id="del%s"
onclick="backgroundDeactivate(\'%s\');return false;">
Deactivate this item</a>', $row[0], $row[0]);
} else {
$record[] = '';
}
if ($this->report_format == 'html') {
$record[] = sprintf('<a href="" id="del%s"
onclick="backgroundDelete(\'%s\',\'%s\');return false;">
Delete this item</a>', $row[0], $row[0], $row[1]);
} else {
$record[] = '';
}
$ret[] = $record;
}
$drop = $dbc->prepare_statement("DROP TABLE {$tempName}");
$dbc->exec_statement($drop);
return $ret;
}
public 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('deptStart', 1);
$deptEnd = FormLib::get('deptEnd', 1);
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
/**
Report Query notes:
* Combining vendorName and prodExtra.distributor is a nod to
legacy data. Eventually data should be fully normalized on
products.default_vendor_id
* Excluding prodExtra.distributor empty string combines those
records with SQL NULL. Having two different "blank" rows
is confusing for users.
* Joins are only needed is a super department condition is
involved. WHERE clause changes similarly.
*/
$query = '
SELECT COALESCE(v.vendorName, x.distributor) AS vendor,
' . DTrans::sumQuantity('t') . ' AS qty,
SUM(t.total) AS ttl
FROM ' . $dlog . ' AS t
' . DTrans::joinProducts('t', 'p', 'LEFT') . '
LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
LEFT JOIN prodExtra AS x ON p.upc=x.upc
';
if ($buyer !== '' && $buyer > -1) {
$query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= ' LEFT JOIN MasterSuperDepts AS s ON t.department=s.dept_ID ';
}
$query .= '
WHERE t.tdate BETWEEN ? AND ?
AND t.trans_type IN (\'I\',\'D\') ';
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
if ($buyer !== '') {
if ($buyer == -2) {
$query .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$query .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 't');
$query .= $conditional;
}
$query .= '
GROUP BY COALESCE(v.vendorName, x.distributor)
ORDER BY SUM(total) DESC';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $args);
$data = array();
$total_sales = 0.0;
while ($w = $dbc->fetch_row($result)) {
$data[] = array($w['vendor'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['ttl']), 0.0);
$total_sales += $w['ttl'];
}
for ($i = 0; $i < count($data); $i++) {
$data[$i][3] = sprintf('%.2f%%', $data[$i][2] / $total_sales * 100);
$data[$i][2] = '$' . $data[$i][2];
}
return $data;
}
public 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('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$weekday = FormLib::get('weekday', 0);
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
$date_selector = 'year(tdate), month(tdate), day(tdate)';
$day_names = array();
if ($weekday == 1) {
$date_selector = $dbc->dayofweek('tdate');
$timestamp = strtotime('next Sunday');
for ($i = 1; $i <= 7; $i++) {
$day_names[$i] = strftime('%a', $timestamp);
$timestamp = strtotime('+1 day', $timestamp);
}
}
$hour = $dbc->hour('tdate');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "SELECT {$date_selector}, {$hour} as hour, \n sum(d.total) AS ttl, avg(d.total) as avg\n FROM {$dlog} AS d ";
// join only needed with specific buyer
// or all retail
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "WHERE d.trans_type IN ('I','D')\n AND d.tdate BETWEEN ? AND ?\n AND {$where} ";
if ($this->config->get('COOP_ID') == 'WFC_Duluth') {
$query .= ' AND d.department NOT IN (993, 998, 703) ';
}
$query .= " GROUP BY {$date_selector}, {$hour}\n ORDER BY {$date_selector}, {$hour}";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$dataset = array();
$minhour = 24;
$maxhour = 0;
while ($row = $dbc->fetch_row($result)) {
$hour = (int) $row['hour'];
$date = '';
if ($weekday == 1) {
$date = $day_names[$row[0]];
} else {
$date = sprintf('%d/%d/%d', $row[1], $row[2], $row[0]);
}
if (!isset($dataset[$date])) {
$dataset[$date] = array();
}
$dataset[$date][$hour] = $row['ttl'];
if ($hour < $minhour) {
$minhour = $hour;
}
if ($hour > $maxhour) {
$maxhour = $hour;
}
}
/**
# of columns is dynamic depending on the
date range selected
*/
$this->report_headers = array('Day');
foreach ($dataset as $day => $info) {
$this->report_headers[] = $day;
}
$this->report_headers[] = 'Total';
$data = array();
/**
# of rows is dynamic depending when
the store was open
*/
for ($i = $minhour; $i <= $maxhour; $i++) {
$record = array();
$sum = 0;
if ($i < 12) {
$record[] = str_pad($i, 2, '0', STR_PAD_LEFT) . ':00 AM';
} else {
if ($i == 12) {
$record[] = $i . ':00 PM';
} else {
$record[] = str_pad($i - 12, 2, '0', STR_PAD_LEFT) . ':00 PM';
}
}
// each day's sales for the given hour
foreach ($dataset as $day => $info) {
$sales = isset($info[$i]) ? $info[$i] : 0;
$record[] = sprintf('%.2f', $sales);
$sum += $sales;
}
$record[] = sprintf('%.2f', $sum);
$data[] = $record;
}
return $data;
}
