public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$query_parts = FormLib::standardItemFromWhere();
$query = '
SELECT t.upc,
t.description,
t.department,
d.dept_name,
' . DTrans::sumQuantity('t') . ' AS qty,
SUM(t.total) AS total ' . $query_parts['query'] . ' AND t.memType=?
GROUP BY t.upc,
t.description,
t.department,
d.dept_name
ORDER BY t.upc';
$args = $query_parts['args'];
$args[] = FormLib::get('memtype');
$data = array();
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
while ($w = $dbc->fetchRow($res)) {
$data[] = array($w['upc'], $w['description'], $w['department'] . ' ' . $w['dept_name'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['total']));
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$query = '';
$from_where = FormLib::standardItemFromWhere();
switch ($this->mode) {
case 'PLU':
$query = "\n 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') 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 t.department,\n d.dept_name,\n m.super_name,\n COALESCE(v.vendorName,x.distributor) AS distributor\n " . $from_where['query'] . "\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 t.department,\n d.dept_name,\n m.super_name,\n COALESCE(v.vendorName,x.distributor)\n ORDER BY SUM(t.total) DESC";
break;
case 'Department':
$query = "\n SELECT t.department,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS total \n " . $from_where['query'] . "\n GROUP BY t.department,\n d.dept_name\n ORDER BY SUM(t.total) DESC";
break;
case 'Date':
$query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS total \n " . $from_where['query'] . "\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)";
break;
case 'Weekday':
$cols = $dbc->dayofweek("tdate") . " AS dayNumber,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 = "\n SELECT " . $cols . " AS dayName, " . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as total \n " . $from_where['query'] . "\n GROUP BY " . str_replace(' AS dayNumber', '', $cols) . "\n ORDER BY " . $dbc->dayofweek('t.tdate');
break;
}
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $from_where['args']);
$data = array();
while ($row = $dbc->fetch_row($result)) {
switch ($this->mode) {
case 'PLU':
$data[] = array($row['upc'], $row['description'], $row['rings'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']), $row['department'], $row['dept_name'], $row['super_name'], $row['distributor']);
break;
case 'Department':
$data[] = array($row['department'], $row['dept_name'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
break;
case 'Date':
$tstamp = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']);
$data[] = array(date('m/d/Y', $tstamp), date('l', $tstamp), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
break;
case 'Weekday':
$data[] = array($row['dayNumber'], $row['dayName'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
break;
}
}
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;
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$from_where = FormLib::standardItemFromWhere();
$select_cols = '
t.upc AS prodID,
CASE WHEN p.brand IS NULL THEN \'\' ELSE p.brand END AS brand,
CASE WHEN p.description IS NULL THEN t.description ELSE p.description END AS description';
$group_cols = '
t.upc,
p.brand,
CASE WHEN p.description IS NULL THEN t.description ELSE p.description END';
if (FormLib::get('lookup-type') == 'likecode') {
$select_cols = '
u.likeCode AS prodID,
\'\' AS brand,
u.likeCodeDesc AS description';
$group_cols = '
u.likeCode,
u.likeCodeDesc';
}
$query = "\n SELECT \n YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day,\n {$select_cols}, " . DTrans::sumQuantity('t') . " AS total\n " . $from_where['query'] . "\n AND trans_status <> 'M'\n AND trans_type = 'I'\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate),\n {$group_cols}\n ORDER BY prodID,\n YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $from_where['args']);
// variable columns. one per dates
$dates = array();
while ($date1 != $date2) {
$dates[] = $date1;
$parts = explode("-", $date1);
if (count($parts) != 3) {
break;
}
$date1 = date("Y-m-d", mktime(0, 0, 0, $parts[1], $parts[2] + 1, $parts[0]));
}
$dates[] = $date2;
$this->report_headers = array('UPC', 'Brand', 'Description');
foreach ($dates as $i) {
$this->report_headers[] = $i;
}
$this->report_headers[] = 'Total';
$current = array('upc' => '', 'brand' => '', 'description' => '');
$data = array();
// track upc while going through the rows, storing
// all data about a given upc before printing
while ($row = $dbc->fetch_array($result)) {
if ($current['upc'] != $row['prodID']) {
if ($current['upc'] != "") {
$record = array($current['upc'], $current['brand'], $current['description']);
$sum = 0.0;
foreach ($dates as $i) {
if (isset($current[$i])) {
$record[] = sprintf('%.2f', $current[$i]);
$sum += $current[$i];
} else {
$record[] = 0.0;
}
}
$record[] = sprintf('%.2f', $sum);
$data[] = $record;
}
// update 'current' values and clear data
// brand may be missing in the case of like codes
$current = array('upc' => $row['prodID'], 'brand' => $row['brand'], 'description' => $row['description']);
}
// get a yyyy-mm-dd format date from sql results
$year = $row['year'];
$month = str_pad($row['month'], 2, '0', STR_PAD_LEFT);
$day = str_pad($row['day'], 2, '0', STR_PAD_LEFT);
$datestr = $year . "-" . $month . "-" . $day;
// index result into data based on date string
// this is to properly place data in the output table
// even when there are 'missing' days for a given upc
$current[$datestr] = $row['total'];
}
// add the last data set
$record = array($current['upc'], $current['brand'], $current['description']);
$sum = 0.0;
foreach ($dates as $i) {
if (isset($current[$i])) {
$record[] = sprintf('%.2f', $current[$i]);
$sum += $current[$i];
} else {
$record[] = 0.0;
}
}
$record[] = sprintf('%.2f', $sum);
$data[] = $record;
return $data;
}
