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()
{
global $FANNIE_OP_DB, $FANNIE_SERVER_DBMS;
// creates a temporary table so requesting a writable connection
// does make sense here
$dbc = FannieDB::get($FANNIE_OP_DB);
$depts = FormLib::get('depts', array());
$upc = FormLib::get('upc');
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$filters = FormLib::get('filters', array());
list($dClause, $dArgs) = $dbc->safeInClause($depts);
$where = "d.department IN ({$dClause})";
$inv = "d.department NOT IN ({$dClause})";
if ($upc != "") {
$upc = BarcodeLib::padUPC($upc);
$where = "d.upc = ?";
$inv = "d.upc <> ?";
$dArgs = array($upc);
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$filter = "";
$fArgs = array();
if (is_array($filters) && count($filters) > 0) {
$fClause = "";
foreach ($filters as $f) {
$fClause .= "?,";
$fArgs[] = $f;
}
$fClause = "(" . rtrim($fClause, ",") . ")";
$filter = "AND d.department IN {$fClause}";
}
$query = $dbc->prepare_statement("CREATE TABLE groupingTemp (tdate varchar(11), emp_no int, register_no int, trans_no int)");
$dbc->exec_statement($query);
$dateConvertStr = $FANNIE_SERVER_DBMS == 'MSSQL' ? 'convert(char(11),d.tdate,110)' : 'convert(date(d.tdate),char)';
$loadQ = $dbc->prepare_statement("INSERT INTO groupingTemp\n SELECT {$dateConvertStr} as tdate,\n emp_no,register_no,trans_no FROM {$dlog} AS d\n WHERE {$where} AND tdate BETWEEN ? AND ?\n GROUP BY {$dateConvertStr}, emp_no,register_no,trans_no");
$dArgs[] = $date1 . ' 00:00:00';
$dArgs[] = $date2 . ' 23:59:59';
$dbc->exec_statement($loadQ, $dArgs);
$dataQ = $dbc->prepare_statement("\n SELECT d.upc,\n p.description,\n t.dept_no,\n t.dept_name,\n SUM(d.quantity) AS quantity\n FROM {$dlog} AS d \n INNER JOIN groupingTemp AS g ON \n {$dateConvertStr} = g.tdate\n AND g.emp_no = d.emp_no\n AND g.register_no = d.register_no\n AND g.trans_no = d.trans_no " . DTrans::joinProducts('d', 'p') . DTrans::joinDepartments('d', 't') . "\n WHERE {$inv} \n AND trans_type IN ('I','D')\n AND d.tdate BETWEEN ? AND ?\n AND d.trans_status=''\n {$filter}\n GROUP BY d.upc,\n p.description,\n t.dept_no,\n t.dept_name\n ORDER BY SUM(d.quantity) DESC");
foreach ($fArgs as $f) {
$dArgs[] = $f;
}
$dataR = $dbc->exec_statement($dataQ, $dArgs);
$data = array();
while ($dataW = $dbc->fetch_row($dataR)) {
$record = array($dataW['upc'], $dataW['description'], $dataW['dept_no'] . ' ' . $dataW['dept_name'], sprintf('%.2f', $dataW['quantity']));
$data[] = $record;
}
$drop = $dbc->prepare_statement("DROP TABLE groupingTemp");
$dbc->exec_statement($drop);
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;
}
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;
}
/**
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 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;
}
