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'));
$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;
$manu = FormLib::get_form_value('manu', '');
$type = FormLib::get_form_value('type', '');
$groupby = FormLib::get_form_value('groupby', 'upc');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$type_condition = "p.brand LIKE ?";
$args = array('%' . $manu . '%');
if ($type == 'prefix') {
$type_condition = 't.upc LIKE ?';
}
$query = "";
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
switch ($groupby) {
case 'upc':
$query = "\n SELECT t.upc,\n p.brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n p.description,\n d.dept_no,\n d.dept_name,\n s.superID\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(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . "\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\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 'dept':
$query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC";
break;
}
$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['month'] . '/' . $row['day'] . '/' . $row['year'];
$record[] = number_format($row['qty'], 2);
$record[] = number_format($row['ttl'], 2);
} else {
for ($i = 0; $i < $dbc->num_fields($result); $i++) {
if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') {
$row[$i] = sprintf('%.2f', $row[$i]);
}
$record[] .= $row[$i];
}
}
$ret[] = $record;
}
return $ret;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$month1 = $this->form->month1;
$month2 = $this->form->month2;
$year1 = FormLib::get_form_value('year1', date('Y'));
$year2 = FormLib::get_form_value('year2', date('Y'));
$date1 = date('Y-m-d', mktime(0, 0, 0, $month1, 1, $year1));
$date2 = date('Y-m-t', mktime(0, 0, 0, $month2, 1, $year2));
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$date1 .= ' 00:00:00';
$date2 .= ' 00:00:00';
$qArgs = array($date1, $date2);
$query = "";
$type = FormLib::get_form_value('mtype', 'upc');
if ($type == 'upc') {
$inClause = "(";
$vals = preg_split("/\\D+/", FormLib::get_form_value('upcs', ''));
foreach ($vals as $v) {
$qArgs[] = BarcodeLib::padUPC($v);
$inClause .= "?,";
}
$inClause = rtrim($inClause, ",") . ")";
$query = "SELECT t.upc,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS sales, \n MONTH(tdate) AS month, \n YEAR(tdate) AS year\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . " \n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.upc IN {$inClause}\n GROUP BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description\n ORDER BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description";
} else {
$dept1 = FormLib::get_form_value('dept1', 1);
$dept2 = FormLib::get_form_value('dept2', 1);
$qArgs[] = $dept1;
$qArgs[] = $dept2;
$query = "SELECT t.department,d.dept_name,SUM(t.quantity) as qty,\n SUM(total) as sales, MONTH(tdate) as month, YEAR(tdate) as year\n FROM {$dlog} AS t\n LEFT JOIN departments AS d ON t.department=d.dept_no\n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.department BETWEEN ? AND ?\n GROUP BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name\n ORDER BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name";
}
$queryP = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($queryP, $qArgs);
$ret = array();
while ($row = $dbc->fetch_array($result)) {
if (!isset($ret[$row[0]])) {
$ret[$row[0]] = array('num' => $row[0], 'desc' => $row[1]);
foreach ($this->months as $mkey) {
$ret[$row[0]][$mkey] = 0;
}
}
if (FormLib::get_form_value('results', 'Sales') == 'Sales') {
$ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['sales'];
} else {
$ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['qty'];
}
}
return $this->dekey_array($ret);
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$upc = $this->form->upc;
if (is_numeric($upc)) {
$upc = BarcodeLib::padUPC($upc);
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "SELECT \n MONTH(t.tdate),\n DAY(t.tdate),\n YEAR(t.tdate),\n t.upc,\n p.brand,\n p.description,\n " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS total\n FROM {$dlog} AS t \n " . DTrans::joinProducts('t', 'p', 'LEFT') . "\n WHERE t.upc = ? AND\n t.tdate BETWEEN ? AND ?\n GROUP BY \n YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate),\n t.upc,\n p.description\n ORDER BY year(t.tdate),month(t.tdate),day(t.tdate)";
$args = array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59');
if (strtolower($upc) == "rrr" || $upc == "0000000000052") {
if ($dlog == "dlog_90_view" || $dlog == "dlog_15") {
$dlog = "transarchive";
} else {
$dlog = "trans_archive.bigArchive";
}
$query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand,'RRR' AS description,\n sum(case when upc <> 'rrr' then quantity when volSpecial is null or volSpecial > 9999 then 0 else volSpecial end) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and trans_status <> 'X'\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)\n ORDER BY YEAR(datetime),MONTH(datetime),DAY(datetime)";
} else {
if (!is_numeric($upc)) {
$dlog = DTransactionsModel::selectDTrans($date1, $date2);
$query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand, description,\n sum(CASE WHEN quantity=0 THEN 1 ELSE quantity END) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and (trans_status <> 'X' || trans_type='L')\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)";
}
}
$prep = $dbc->prepare_statement($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] . "/" . $row[1] . "/" . $row[2];
$record[] = $row['upc'];
$record[] = $row['brand'] === null ? '' : $row['brand'];
$record[] = $row['description'] === null ? '' : $row['description'];
$record[] = sprintf('%.2f', $row['qty']);
$record[] = sprintf('%.2f', $row['total']);
$ret[] = $record;
}
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;
$vendor = FormLib::get_form_value('vendor', '');
$groupby = FormLib::get_form_value('groupby', 'upc');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "";
switch ($groupby) {
case 'upc':
$query = "\n SELECT t.upc,\n COALESCE(p.brand, x.manufacturer) AS brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n COALESCE(p.brand, x.manufacturer),\n p.description,\n d.dept_no,\n d.dept_name,\n s.super_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(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\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 'dept':
$query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.super_name\n ORDER BY SUM(t.total) DESC";
break;
}
$args = array('%' . $vendor . '%', '%' . $vendor . '%', $date1 . ' 00:00:00', $date2 . ' 23:59:59');
$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['month'] . '/' . $row['day'] . '/' . $row['year'];
$record[] = number_format($row['qty'], 2);
$record[] = number_format($row['ttl'], 2);
} else {
for ($i = 0; $i < $dbc->num_fields($result); $i++) {
if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') {
$row[$i] = number_format($row[$i], 2);
}
$record[] .= $row[$i];
}
}
$ret[] = $record;
}
return $ret;
}
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;
}
/**
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;
}
public function run()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
// look up item daily movement for last quarter
$salesQ = 'SELECT ' . DTrans::sumQuantity('d') . ' AS qty, ' . $dbc->datediff($dbc->now(), 'MIN(tdate)') . ' AS diff,
MAX(discounttype) AS onSale
FROM ' . $FANNIE_TRANS_DB . $dbc->sep() . 'dlog_90_view AS d
WHERE d.upc=?
AND charflag <> \'SO\'
AND trans_status <> \'R\'
GROUP BY year(tdate), month(tdate), day(tdate)
ORDER BY year(tdate), month(tdate), day(tdate) DESC';
$salesP = $dbc->prepare($salesQ);
$prodP = $dbc->prepare('UPDATE products SET auto_par=? WHERE upc=?');
$product = new ProductsModel($dbc);
$product->inUse(1);
$prodR = $dbc->query('
SELECT upc
FROM products
WHERE inUse=1
');
$lambda = 0.25;
// average daily sales for items at retail price
// sale days are discarded from both quantity sold
// and number of days
while ($prodW = $dbc->fetchRow($prodR)) {
$upc = $prodW['upc'];
$salesR = $dbc->execute($salesP, array($upc));
if ($dbc->numRows($salesR) == 0) {
$dbc->execute($prodP, array(0, $upc));
continue;
}
$max = 0;
$days = array();
$last_nonsale_qty = 0.1;
$nonsale_qty = 0.1;
$nonsale_count = 0;
while ($w = $dbc->fetchRow($salesR)) {
$index = $w['diff'];
if ($index > $max) {
$max = $index;
}
$days[$index] = $w['qty'];
if ($w['onSale']) {
$days[$index] = $nonsale_count == 0 ? $nonsale_qty : $nonsale_qty / $nonsale_count;
} else {
$nonsale_qty += $w['qty'];
$nonsale_count++;
}
}
$sum = 0;
$count = 0;
for ($i = 1; $i <= $max; $i++) {
if (isset($days[$i]) && $days[$i] == 'skip') {
continue;
}
$sum += isset($days[$i]) ? $days[$i] : 0;
$count++;
}
$avg = $count == 0 ? 0 : $sum / $count;
$dbc->execute($prodP, array($avg, $upc));
}
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$bStart = FormLib::get_form_value('date1', '');
$bEnd = FormLib::get_form_value('date2', '');
$model = new BatchesModel($dbc);
/**
Assemble argument array and appropriate string
for an IN clause in a prepared statement
*/
$batchID = $this->form->batchID;
if (!is_array($batchID)) {
$batchID = array($batchID);
}
$inArgs = array();
$inClause = '(';
$upcs = array();
foreach ($batchID as $bID) {
$inClause .= '?,';
$inArgs[] = $bID;
$upcs = array_merge($upcs, $model->getUPCs($bID));
}
$upcs = array_unique($upcs);
$inClause = rtrim($inClause, ',') . ')';
$batchInfoQ = '
SELECT batchName,
year(startDate) as sy,
month(startDate) as sm,
day(startDate) as sd,
year(endDate) as ey,
month(endDate) as em,
day(endDate) as ed
FROM batches
WHERE batchID IN ' . $inClause;
$batchInfoP = $dbc->prepare($batchInfoQ);
$batchInfoR = $dbc->execute($batchInfoP, $inArgs);
$bName = "";
while ($batchInfoW = $dbc->fetchRow($batchInfoR)) {
$bName .= $batchInfoW['batchName'] . " ";
if (empty($bStart)) {
$bStart = sprintf("%d-%02d-%02d", $batchInfoW['sy'], $batchInfoW['sm'], $batchInfoW['sd']);
}
if (empty($bEnd)) {
$bEnd = sprintf("%d-%02d-%02d", $batchInfoW['ey'], $batchInfoW['em'], $batchInfoW['ed']);
}
}
$dlog = DTransactionsModel::selectDlog($bStart, $bEnd);
$bStart .= ' 00:00:00';
$bEnd .= ' 23:59:59';
$reportArgs = array($bStart, $bEnd);
list($in_sql, $reportArgs) = $dbc->safeInClause($upcs, $reportArgs);
$salesBatchQ = "\n SELECT d.upc, \n p.description, \n l.floorSectionID,\n f.name AS location,\n SUM(d.total) AS sales, " . DTrans::sumQuantity('d') . " AS quantity, \n SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'INNER') . "\n LEFT JOIN prodPhysicalLocation AS l ON l.upc=p.upc\n LEFT JOIN FloorSections as f ON f.floorSectionID=l.floorSectionID\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc IN ({$in_sql})\n GROUP BY d.upc, \n p.description\n ORDER BY d.upc";
$salesBatchP = $dbc->prepare($salesBatchQ);
$inArgs[] = $bStart;
$inArgs[] = $bEnd;
$salesBatchR = $dbc->execute($salesBatchP, $reportArgs);
/**
Simple report
Issue a query, build array of results
*/
$ret = array();
while ($row = $dbc->fetchRow($salesBatchR)) {
$record = array();
$record[] = $row['upc'];
$record[] = $row['description'];
$record[] = sprintf('%.2f', $row['sales']);
$record[] = sprintf('%.2f', $row['quantity']);
$record[] = $row['rings'];
$record[] = $row['location'] === null ? '' : $row['location'];
$ret[] = $record;
}
return $ret;
}
public function preprocess()
{
global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB, $FANNIE_URL, $FANNIE_TRANS_DB;
//$this->add_script($FANNIE_URL.'src/javascript/jquery.js');
//$this->add_script($FANNIE_URL.'src/javascript/jquery-ui.js');
if (FormLib::get('upc_in') !== '') {
$upc = BarcodeLib::padUPC(FormLib::get('upc_in'));
$this->current_item_data['upc'] = $upc;
$dbc = FannieDB::get($FANNIE_OP_DB);
$model = new ProductsModel($dbc);
$model->upc($upc);
$vendorID = 0;
if ($model->load()) {
$this->current_item_data['desc'] = $model->brand() . ' ' . $model->description();
$this->current_item_data['par'] = $model->auto_par();
$vendorID = $model->default_vendor_id();
}
$model = new VendorsModel($dbc);
$model->vendorID($vendorID);
if ($model->load()) {
$this->current_item_data['vendor'] = $model->vendorName();
$schedule = new VendorDeliveriesModel($dbc);
$schedule->vendorID($vendorID);
if ($schedule->load() && $schedule->regular() == 1) {
$this->current_item_data['nextDelivery'] = date('D, M jS', strtotime($schedule->nextDelivery())) . ' & ' . date('D, M jS', strtotime($schedule->nextNextDelivery()));
$nd = new DateTime(date('Y-m-d', strtotime($schedule->nextDelivery())));
$nnd = new DateTime(date('Y-m-d', strtotime($schedule->nextNextDelivery())));
$this->current_item_data['deliverySpan'] = $nnd->diff($nd)->format('%a');
}
$items = new VendorItemsModel($dbc);
$items->vendorID($vendorID);
$items->upc($upc);
$this->current_item_data['cases'] = array();
foreach ($items->find('units') as $item) {
$this->current_item_data['cases'][] = $item->units();
}
}
$saleNow = 'SELECT b.batchName, b.startDate, b.endDate
FROM batchList AS l
INNER JOIN batches AS b ON b.batchID=l.batchID
WHERE l.upc=?
AND b.discounttype <> 0
AND b.startDate <= ' . $dbc->now() . '
AND b.endDate >= ' . $dbc->now();
$saleNow = $dbc->prepare($saleNow);
$saleNow = $dbc->execute($saleNow, array($upc));
if ($dbc->num_rows($saleNow) > 0) {
$row = $dbc->fetch_row($saleNow);
$this->current_item_data['onSale'] = $row['batchName'] . ' thru ' . date('D, M jS', strtotime($row['endDate']));
}
$saleNext = 'SELECT b.batchName, b.startDate, b.endDate
FROM batchList AS l
INNER JOIN batches AS b ON b.batchID=l.batchID
WHERE l.upc=?
AND b.discounttype <> 0
AND b.startDate >= ' . $dbc->now() . '
AND b.endDate >= ' . $dbc->now();
$saleNext = $dbc->prepare($saleNext);
$saleNext = $dbc->execute($saleNext, array($upc));
if ($dbc->num_rows($saleNext) > 0) {
$row = $dbc->fetch_row($saleNext);
$this->current_item_data['soonSale'] = $row['batchName'] . ' on ' . date('D, M jS', strtotime($row['startDate']));
}
$ordersQ = 'SELECT v.vendorName,
o.placedDate,
i.quantity,
i.caseSize
FROM PurchaseOrderItems AS i
INNER JOIN PurchaseOrder AS o ON i.orderID=o.orderID
INNER JOIN vendors AS v ON o.vendorID=v.vendorID
WHERE i.internalUPC = ?
ORDER BY o.placedDate DESC';
$ordersQ = $dbc->add_select_limit($ordersQ, 10);
$ordersP = $dbc->prepare($ordersQ);
$ordersR = $dbc->execute($ordersP, array($upc));
$orders = array();
while ($w = $dbc->fetch_row($ordersR)) {
$orders[] = $w;
}
$this->current_item_data['orders'] = $orders;
$salesQ = 'SELECT ' . DTrans::sumQuantity('d') . ' AS qty,
MIN(tdate) as day
FROM ' . $FANNIE_TRANS_DB . $dbc->sep() . 'dlog_15 AS d
WHERE upc = ?
GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)
ORDER BY YEAR(tdate) DESC, MONTH(tdate) DESC, DAY(tdate) DESC';
$salesP = $dbc->prepare($salesQ);
$salesR = $dbc->execute($salesP, array($upc));
$sales = array();
while ($w = $dbc->fetch_row($salesR)) {
$sales[] = $w;
}
$this->current_item_data['sales'] = $sales;
}
$this->linea_ios_mode = $this->linea_support_available();
if ($this->linea_ios_mode) {
$this->add_script($FANNIE_URL . 'src/javascript/linea/cordova-2.2.0.js');
$this->add_script($FANNIE_URL . 'src/javascript/linea/ScannerLib-Linea-2.0.0.js');
}
$this->add_script($FANNIE_URL . 'src/javascript/tablesorter/jquery.tablesorter.js');
$this->add_css_file($FANNIE_URL . 'src/javascript/tablesorter/themes/blue/style.css');
//$this->add_css_file($FANNIE_URL.'src/javascript/jquery-ui.css');
return true;
}
private function upcQuery($dlog, $items)
{
$args = array_filter($items, function ($i) {
return trim($i) !== '' ? true : false;
});
$args = array_map(function ($i) {
$item = trim($i);
// trim check digit based on dashes or spacing
if (strlen($item) > 2 && ($item[strlen($item) - 2] == ' ' || $item[strlen($item) - 2] == '-') && is_numeric($item[strlen($item) - 1])) {
$item = substr($item, 0, strlen($item) - 2);
}
$item = str_replace(' ', '', $item);
$item = str_replace('-', '', $item);
return BarcodeLib::padUPC($item);
}, $items);
$inStr = str_repeat('?,', count($args));
$inStr = substr($inStr, 0, strlen($inStr) - 1);
$query = '
SELECT t.upc,
p.brand,
p.description,
' . DTrans::sumQuantity('t') . ' as qty,
CASE WHEN p.scale=1 THEN \'LB\' ELSE p.size END AS size,
p.cost,
p.normal_price
FROM ' . $dlog . ' AS t
' . DTrans::joinProducts() . '
WHERE t.upc IN (' . $inStr . ')
AND t.tdate BETWEEN ? AND ?
GROUP BY t.upc,
p.brand,
p.description';
return array($query, $args);
}
public function avgSales($upc)
{
$config = FannieConfig::factory();
$dbc = FannieDB::get($config->get('OP_DB'));
$prodP = $dbc->prepare('SELECT auto_par FROM products WHERE upc=?');
$avg = $dbc->getValue($prodP, array($upc));
if ($avg) {
return $avg;
}
$dbc = FannieDB::get($config->get('ARCHIVE_DB'));
$avg = 0.0;
if ($dbc->tableExists('productWeeklyLastQuarter')) {
$maxP = $dbc->prepare('SELECT MAX(weekLastQuarterID) FROM productWeeklyLastQuarter WHERE upc=?');
$maxR = $dbc->execute($maxP, $upc);
if ($maxR && $dbc->numRows($maxR)) {
$maxW = $dbc->fetchRow($maxR);
$avgP = $dbc->prepare('
SELECT SUM((?-weekLastQuarterID)*quantity) / SUM(weekLastQuarterID)
FROM productWeeklyLastQuarter
WHERE upc=?');
$avgR = $dbc->execute($avgP, array($maxW[0], $upc));
$avgW = $dbc->fetchRow($avgR);
$avg = $avgW[0] / 7.0;
}
} else {
$dbc = FannieDB::get($config->get('TRANS_DB'));
$avgP = $dbc->prepare('
SELECT MIN(tdate) AS min,
MAX(tdate) AS max,
' . DTrans::sumQuantity() . ' AS qty
FROM dlog_90_view
WHERE upc=?');
$avgR = $dbc->execute($avgP, array($upc));
if ($avgR && $dbc->numRows($avgR)) {
$avgW = $dbc->fetchRow($avgR);
$d1 = new DateTime($avgW['max']);
$d2 = new DateTime($avgW['min']);
$num_days = $d1->diff($d2)->format('%a') + 1;
$avg = $avgW['qty'] / $num_days;
}
}
// put the database back where we found it (probably)
$dbc = FannieDB::get($config->get('OP_DB'));
return $avg;
}
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;
}
public function run()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_ARCHIVE_DB;
$today = strtotime('today');
$this_monday = $today;
while (date('N', $this_monday) != 1) {
$this_monday = mktime(0, 0, 0, date('n', $this_monday), date('j', $this_monday) - 1, date('Y', $this_monday));
}
$last_monday = mktime(0, 0, 0, date('n', $this_monday), date('j', $this_monday) - 7, date('Y', $this_monday));
$dbc = FannieDB::get($FANNIE_ARCHIVE_DB);
$this->cronMsg('Determining applicable weeks', FannieLogger::INFO);
$dbc->query('TRUNCATE TABLE weeksLastQuarter');
$ins = $dbc->prepare('INSERT INTO weeksLastQuarter (weekLastQuarterID, weekStart, weekEnd) VALUES (?, ?, ?)');
$weeks = array();
// week zero
$this_sunday = mktime(0, 0, 0, date('n', $this_monday), date('j', $this_monday) + 6, date('Y', $this_monday));
$args = array(0, date('Y-m-d 00:00:00', $this_monday), date('Y-m-d 23:59:59', $this_sunday));
$weeks[] = array($this_monday, $this_sunday);
$dbc->execute($ins, $args);
// database may be uncooperative about a zero value in the increment column
// so make sure its assigned correctly
$dbc->query('UPDATE weeksLastQuarter SET weekLastQuarterID=0');
// other weeks
for ($i = 0; $i < 13; $i++) {
$monday = mktime(0, 0, 0, date('n', $last_monday), date('j', $last_monday) - $i * 7, date('Y', $last_monday));
$sunday = mktime(0, 0, 0, date('n', $monday), date('j', $monday) + 6, date('Y', $monday));
$args = array($i + 1, date('Y-m-d 00:00:00', $monday), date('Y-m-d 23:59:59', $sunday));
$dbc->execute($ins, $args);
$weeks[] = array($monday, $sunday);
}
$addP = $dbc->prepare('INSERT INTO productWeeklyLastQuarter
(upc, weekLastQuarterID, quantity, total,
percentageStoreSales, percentageSuperDeptSales,
percentageDeptSales)
VALUES
(?, ?, ?, ?,
?, ?,
?)');
$products = $FANNIE_OP_DB . $dbc->sep() . 'products';
$supers = $FANNIE_OP_DB . $dbc->sep() . 'MasterSuperDepts';
$store_sales = 0.0;
$super_sales = array();
$dept_sales = array();
$dbc->query('TRUNCATE TABLE productWeeklyLastQuarter');
foreach ($weeks as $weekID => $limits) {
$upcs = array();
$this->cronMsg('Processing week #' . $weekID, FannieLogger::INFO);
$dlog = DTransactionsModel::selectDlog(date('Y-m-d', $limits[0]), date('Y-m-d', $limits[1]));
$dataP = $dbc->prepare("SELECT d.upc, SUM(total) as ttl, " . DTrans::sumQuantity('d') . " as qty,\n MAX(p.department) as dept, MAX(s.superID) as superDept\n FROM {$dlog} AS d INNER JOIN {$products} AS p\n ON d.upc = p.upc\n LEFT JOIN {$supers} AS s\n ON p.department = s.dept_ID\n WHERE tdate BETWEEN ? AND ?\n GROUP BY d.upc");
$args = array(date('Y-m-d 00:00:00', $limits[0]), date('Y-m-d 23:59:59', $limits[1]));
$result = $dbc->execute($dataP, $args);
// accumulate all info for the week
// in order to calculate percentages
while ($row = $dbc->fetch_row($result)) {
// normally miskeys that were voided
// and not useful information
if ($row['ttl'] == 0) {
continue;
}
$upcs[$row['upc']] = array('ttl' => $row['ttl'], 'qty' => $row['qty'], 'dept' => $row['dept'], 'super' => $row['superDept']);
$store_sales += $row['ttl'];
if (!isset($super_sales[$row['superDept']])) {
$super_sales[$row['superDept']] = 0.0;
}
$super_sales[$row['superDept']] += $row['ttl'];
if (!isset($dept_sales[$row['dept']])) {
$dept_sales[$row['dept']] = 0.0;
}
$dept_sales[$row['dept']] += $row['ttl'];
}
// add entries for this week's items
foreach ($upcs as $upc => $info) {
$d_ttl = $dept_sales[$info['dept']];
$s_ttl = $super_sales[$info['super']];
$args = array($upc, $weekID, $info['qty'], $info['ttl'], $store_sales == 0 ? 0.0 : $info['ttl'] / $store_sales, $s_ttl == 0 ? 0.0 : $info['ttl'] / $s_ttl, $d_ttl == 0 ? 0.0 : $info['ttl'] / $d_ttl);
$dbc->execute($addP, $args);
}
}
// end loop on weeks
// now do weighted averages
$this->cronMsg('Calculating weighted averages', FannieLogger::INFO);
$dbc->query('TRUNCATE TABLE productSummaryLastQuarter');
$dbc->query('INSERT INTO productSummaryLastQuarter
(upc, qtyThisWeek, totalThisWeek, qtyLastQuarter,
totalLastQuarter, percentageStoreSales,
percentageSuperDeptSales, percentageDeptSales)
SELECT upc,
SUM(CASE WHEN weekLastQuarterID=0 THEN quantity ELSE 0 END) as qtyThisWeek,
SUM(CASE WHEN weekLastQuarterID=0 THEN total ELSE 0 END) as totalThisWeek,
SUM(CASE WHEN weekLastQuarterID<>0 THEN quantity ELSE 0 END) as qtyLastQuarter,
SUM(CASE WHEN weekLastQuarterID<>0 THEN total ELSE 0 END) as totalLastQuarter,
SUM((14-weekLastQuarterID) * percentageStoreSales) / SUM(14-weekLastQuarterID),
SUM((14-weekLastQuarterID) * percentageSuperDeptSales) / SUM(14-weekLastQuarterID),
SUM((14-weekLastQuarterID) * percentageDeptSales) / SUM(14-weekLastQuarterID)
FROM productWeeklyLastQuarter
GROUP BY upc');
}
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 static function aggregateStruct(SQLManager $connection, $dlog, $start_date, $end_date, stdclass $where, $groupby = array())
{
$base_table = self::selectStruct($dlog, $start_date, $end_date);
$dt_col = $dlog ? 'tdate' : 'datetime';
$clone_table = $dlog ? 'dlog_15' : 'transarchive';
/**
Grouping is required
*/
if (!is_array($groupby) || count($groupby) == 0) {
return $base_table;
}
/**
Validate group by columns
*/
$model = new DTransactionsModel(null);
$columns = $model->getColumns();
$insert_cols = array();
$select_cols = array();
for ($i = 0; $i < count($groupby); $i++) {
$group = $groupby[$i];
if (isset($columns[$group])) {
$insert_cols[] = $group;
$select_cols[] = $group;
} elseif (preg_match('/(.+)\\s+AS\\s+(\\w+)$/', $group, $matches)) {
$col_definition = $matches[1];
$col_alias = $matches[2];
if (isset($columns[$col_alias])) {
$insert_cols[] = $col_alias;
$select_cols[] = $group;
$groupby[$i] = $col_definition;
} else {
return $base_table;
}
} else {
return $base_table;
}
}
/**
Always include a datetime column
*/
if (!in_array($dt_col, $insert_cols)) {
$insert_cols[] = $dt_col;
$select_cols[] = 'MAX(' . $dt_col . ') AS ' . $dt_col;
}
/**
Create randomly named temporary table based
on the structure of dlog_15 or transachive
*/
$config = FannieConfig::factory();
$sep = $connection->sep();
$random_name = uniqid('temp' . rand(1000, 9999));
$temp_table = $config->get('ARCHIVE_DB') . $sep . $random_name;
$clone_table = $config->get('TRANS_DB') . $sep . $clone_table;
$temp_name = $connection->temporaryTable($temp_table, $clone_table);
if ($temp_name === false) {
return $base_table;
}
/**
Build a query to insert aggregated rows into
the temporary table
*/
$query = 'INSERT INTO ' . $temp_name . '(';
foreach ($insert_cols as $c) {
$query .= $c . ',';
}
$query .= 'total, quantity) ';
$query .= ' SELECT ';
foreach ($select_cols as $c) {
$query .= $c . ',';
}
/**
Always aggregate by total & quantity
*/
$query .= ' SUM(total) AS total, ' . DTrans::sumQuantity() . ' AS quantity
FROM __TRANSACTION_TABLE__
WHERE ' . $dt_col . ' BETWEEN ? AND ? ';
$params = array($start_date . ' 00:00:00', $end_date . ' 23:59:59');
/**
Add a where clause if one has been specified
*/
if (property_exists($where, 'sql') && is_array($where->sql)) {
foreach ($where->sql as $sql) {
$query .= ' AND ' . $sql;
}
}
if (property_exists($where, 'params') && is_array($where->params)) {
foreach ($where->params as $p) {
$params[] = $p;
}
}
/**
Add the group by clause
*/
$query .= ' GROUP BY ';
foreach ($groupby as $group) {
$query .= $group . ',';
}
$query = substr($query, 0, strlen($query) - 1);
/**
Split monthly archive union if needed
*/
$source_tables = array();
if (strstr($base_table, ' UNION ')) {
preg_match_all('/\\s+FROM\\s+(\\w+)\\s+/', $base_table, $matches);
foreach ($matches[1] as $m) {
$source_tables[] = $m;
}
} else {
$source_tables = array($base_table);
}
/**
Load data into temporary table from source table(s)
using built query
*/
foreach ($source_tables as $source_table) {
$insertQ = str_replace('__TRANSACTION_TABLE__', $source_table, $query);
$prep = $connection->prepare($insertQ);
if (!$connection->execute($prep, $params)) {
return $base_table;
}
}
return $temp_name;
}
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;
}
