public function post_upc_description_department_cost_price_qty_reason_handler()
{
global $FANNIE_TRANS_DB, $FANNIE_EMP_NO, $FANNIE_REGISTER_NO;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$record = DTrans::$DEFAULTS;
$record['emp_no'] = $FANNIE_EMP_NO;
$record['register_no'] = $FANNIE_REGISTER_NO;
$record['trans_no'] = DTrans::getTransNo($dbc, $FANNIE_EMP_NO, $FANNIE_REGISTER_NO);
$record['trans_id'] = 1;
$record['upc'] = $this->upc;
$record['description'] = $this->description;
$record['department'] = $this->department;
$record['trans_type'] = 'I';
$record['quantity'] = $this->qty;
$record['ItemQtty'] = $this->qty;
$record['unitPrice'] = $this->price;
$record['regPrice'] = $this->price;
$record['total'] = $this->qty * $this->price;
$record['cost'] = $this->qty * $this->cost;
$record['numflag'] = $this->reason;
$record['charflag'] = strlen(FormLib::get('type')) > 0 ? strtoupper(substr(FormLib::get('type'), 0, 1)) : '';
$record['trans_status'] = 'Z';
$info = DTrans::parameterize($record, 'datetime', $dbc->now());
$query = 'INSERT INTO dtransactions
(' . $info['columnString'] . ')
VALUES
(' . $info['valueString'] . ')';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $info['arguments']);
header('Location: ' . $_SERVER['PHP_SELF'] . '?msg=1');
return false;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$query = '
SELECT b.batchID,
b.batchName,
l.upc,
p.normal_price,
l.salePrice,
CASE WHEN b.startDate > ' . $dbc->curdate() . ' THEN 0 ELSE 1 END AS current
FROM batches AS b
INNER JOIN batchList AS l ON b.batchID=l.batchID
' . DTrans::joinProducts('l', 'p', 'INNER') . '
WHERE b.endDate >= ' . $dbc->curdate() . '
AND b.discounttype <> 0
AND l.salePrice >= p.normal_price';
$result = $dbc->query($query);
$data = array();
while ($w = $dbc->fetchRow($result)) {
$record = array('<a href="' . $this->config->get('URL') . 'batches/newbatch/EditBatchPage.php?id=' . $w['batchID'] . '">' . $w['batchName'] . '</a>', $w['upc'], $w['current'] == 1 ? 'Yes' : 'No', $w['normal_price'], $w['salePrice']);
$data[] = $record;
}
return $data;
}
public function fetch_report_data()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$qty = FormLib::get('qty', 1);
$create = $dbc->prepare_statement("CREATE TABLE groupingTempBS (upc VARCHAR(13), quantity double, total decimal(10,2), trans_num varchar(50))");
$dbc->exec_statement($create);
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$setupQ = $dbc->prepare_statement("INSERT INTO groupingTempBS\n SELECT upc, quantity, total, trans_num\n FROM {$dlog} AS d WHERE tdate BETWEEN ? AND ?\n AND trans_type IN ('I','D')\n GROUP BY year(tdate),month(tdate),day(tdate),trans_num \n HAVING COUNT(*) <= ?");
$dbc->exec_statement($setupQ, array($date1 . ' 00:00:00', $date2 . ' 23:59:59', $qty));
$reportQ = $dbc->prepare_statement('
SELECT g.upc,
p.description,
SUM(g.quantity) AS qty,
COUNT(DISTINCT trans_num) AS num,
SUM(total) AS ttl
FROM groupingTempBS as g ' . DTrans::joinProducts('g', 'p') . '
GROUP BY g.upc,
p.description
HAVING sum(total) <> 0
ORDER BY count(*) DESC
');
$reportR = $dbc->exec_statement($reportQ);
$data = array();
while ($w = $dbc->fetch_row($reportR)) {
$record = array($w['upc'], empty($w['description']) ? 'n/a' : $w['description'], $w[3], sprintf('%.2f', $w[2]), sprintf('%.2f', $w[4]));
$data[] = $record;
}
$drop = $dbc->prepare_statement("DROP TABLE groupingTempBS");
$dbc->exec_statement($drop);
return $data;
}
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', '');
$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;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->setDefaultDB($this->config->get('OP_DB'));
$d1 = $this->form->date1;
$d2 = $this->form->date2;
$dates = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
$today = date('Ymd');
$data = array();
$dlog = DTransactionsModel::selectDlog($d1);
$tenderQ = $dbc->prepare("\n SELECT TenderName,\n COUNT(d.total) AS num,\n SUM(d.total) as total,\n t.SalesCode\n FROM {$dlog} AS d\n LEFT JOIN tenders as t ON d.trans_subtype=t.TenderCode\n WHERE d.tdate BETWEEN ? AND ?\n AND d.trans_type = 'T'\n AND d.total <> 0\n GROUP BY t.TenderName,\n t.SalesCode \n ORDER BY TenderName");
$tenderR = $dbc->execute($tenderQ, $dates);
while ($tenderW = $dbc->fetch_row($tenderR)) {
$credit = $tenderW['total'] <= 0 ? abs($tenderW['total']) : 0.0;
$debit = $tenderW['total'] > 0 ? $tenderW['total'] : 0.0;
$data[] = array($today, $tenderW['SalesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $tenderW['TenderName']);
}
$salesQ = '
SELECT t.salesCode AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN departments AS t ON d.department=t.dept_no
WHERE d.department <> 0
AND d.trans_type <> \'T\'
AND d.tdate BETWEEN ? AND ?
GROUP BY t.salesCode
ORDER BY t.salesCode';
$salesP = $dbc->prepare($salesQ);
$salesR = $dbc->execute($salesP, $dates);
$report = array();
while ($salesW = $dbc->fetch_row($salesR)) {
$credit = $salesW['total'] < 0 ? abs($salesW['total']) : 0.0;
$debit = $salesW['total'] >= 0 ? $salesW['total'] : 0.0;
$data[] = array($today, $salesW['category'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), 'Sales ' . $salesW['category']);
}
$discQ = $dbc->prepare("\n SELECT m.memDesc, \n SUM(d.total) AS total,\n count(*) AS num,\n m.salesCode\n FROM {$dlog} d \n LEFT JOIN memtype m ON d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc = 'DISCOUNT'\n AND total <> 0\n GROUP BY m.memDesc,\n m.salesCode \n ORDER BY m.memDesc");
$discR = $dbc->execute($discQ, $dates);
while ($discW = $dbc->fetch_row($discR)) {
$credit = $discW['total'] <= 0 ? abs($discW['total']) : 0.0;
$debit = $discW['total'] > 0 ? $discW['total'] : 0.0;
$data[] = array($today, $discW['salesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $discW['memDesc'] . ' Discount');
}
$report = array();
$trans = DTransactionsModel::selectDTrans($d1);
$lineItemQ = $dbc->prepare("\n SELECT d.description,\n SUM(regPrice) AS ttl,\n t.salesCode\n FROM {$trans} AS d\n LEFT JOIN taxrates AS t ON d.numflag=t.id\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . DTrans::isNotTesting('d') . "\n GROUP BY d.description\n ");
$lineItemR = $dbc->execute($lineItemQ, $dates);
while ($lineItemW = $dbc->fetch_row($lineItemR)) {
$credit = $lineItemW['ttl'] < 0 ? abs($lineItemW['ttl']) : 0.0;
$debit = $lineItemW['ttl'] >= 0 ? $lineItemW['ttl'] : 0.0;
$data[] = array($today, $lineItemW['salesCode'], sprintf('%.2f', $credit), sprintf('%.2f', $debit), $lineItemW['description'] . ' tax');
}
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()
{
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;
}
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);
}
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 lookupItem($store, $sec, $subsec, $sh_set, $shelf, $loc)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$q = $dbc->prepare_statement("SELECT l.upc,p.description FROM prodPhysicalLocation AS l\n " . DTrans::joinProducts('l') . "\n WHERE l.store_id=? AND section=? AND subsection=?\n AND shelf_set=? AND shelf=? AND location=?");
$args = array($store, $sec, $subsec, $sh_set, $shelf, $loc);
$r = $dbc->exec_statement($q, $args);
$ret = array('upc' => '', 'description' => 'no item at this location');
if ($dbc->num_rows($r) > 0) {
$w = $dbc->fetch_row($r);
$ret['upc'] = $w['upc'];
$ret['description'] = $w['description'];
}
return $ret;
}
public function guessCode()
{
$dbc = $this->connection;
// case 1: item exists in products
$deptP = $dbc->prepare('
SELECT d.salesCode
FROM products AS p
INNER JOIN departments AS d ON p.department=d.dept_no
WHERE p.upc=?');
$deptR = $dbc->execute($deptP, array($this->internalUPC()));
if ($dbc->numRows($deptR)) {
$w = $dbc->fetchRow($deptR);
return $w['salesCode'];
}
$order = new PurchaseOrderModel($dbc);
$order->orderID($this->orderID());
$order->load();
// case 2: item is SKU-mapped but the order record
// does not reflect the internal PLU
$deptP = $dbc->prepare('
SELECT d.salesCode
FROM vendorSKUtoPLU AS v
' . DTrans::joinProducts('v', 'p', 'INNER') . '
INNER JOIN departments AS d ON p.department=d.dept_no
WHERE v.sku=?
AND v.vendorID=?');
$deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID()));
if ($dbc->numRows($deptR)) {
$w = $dbc->fetchRow($deptR);
return $w['salesCode'];
}
// case 3: item is not normally carried but is in a vendor catalog
// that has vendor => POS department mapping
$deptP = $dbc->prepare('
SELECT d.salesCode
FROM vendorItems AS v
INNER JOIN vendorDepartments AS z ON v.vendorDept=z.deptID AND v.vendorID=z.vendorID
INNER JOIN departments AS d ON z.posDeptID=d.dept_no
WHERE v.sku=?
AND v.vendorID=?');
$deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID()));
if ($dbc->numRows($deptR)) {
$w = $dbc->fetchRow($deptR);
return $w['salesCode'];
}
return false;
}
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;
$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;
}
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 run()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$yesterday = date('Y-m-d', strtotime('yesterday'));
$url = $this->config->get('URL');
$host = php_uname('n');
$dtrans = DTransactionsModel::selectDTrans($yesterday);
$findP = $dbc->prepare('
SELECT d.upc,
d.description,
d.department,
p.inUse,
count(*) AS occurences
FROM ' . $dtrans . ' AS d ' . DTrans::joinProducts('d') . '
WHERE d.trans_type=\'L\'
AND d.trans_subtype=\'OG\'
AND d.charflag=\'IU\'
AND d.emp_no <> 9999
AND d.register_no <> 99
AND d.datetime BETWEEN ? AND ?
GROUP BY d.upc,
d.description,
d.department,
p.inUse
HAVING SUM(d.quantity) <> 0
');
$findR = $dbc->execute($findP, array($yesterday . ' 00:00:00', $yesterday . ' 23:59:59'));
while ($w = $dbc->fetchRow($findR)) {
$msg = sprintf('%s (%s) was sold %d times while not inUse', $w['description'], $w['upc'], $w['occurences']);
$email = \COREPOS\Fannie\API\lib\AuditLib::getAddresses($w['department']);
if ($email) {
$subject = 'Not In Use Report';
$from = "From: automail\r\n";
$msg .= "\n";
$msg .= "http://{$host}/{$url}item/ItemEditorPage.php?searchupc={$w['upc']}\n";
mail($email, $subject, $msg, $from);
} else {
$this->cronMsg($msg);
}
}
}
/**
Create a POS transaction
@param $emp_no [int] employee ID
@param $register_no [int] lane ID
@param $lines [array] of records
Each record is a set of key/value pairs
with the following keys:
amount => purchase amount
department => department ID#
description => text description
card_no => member ID#
*/
public static function writeTransaction($emp_no, $register_no, $lines)
{
global $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$prep = $dbc->prepare('SELECT MAX(trans_no) FROM dtransactions
WHERE emp_no=? AND register_no=?');
$result = $dbc->execute($prep, array($emp_no, $register_no));
$trans_no = 1;
if ($dbc->num_rows($result) > 0) {
$row = $dbc->fetch_row($result);
if ($row[0] != '') {
$trans_no = $row[0] + 1;
}
}
$record = DTrans::$DEFAULTS;
$record['register_no'] = $register_no;
$record['emp_no'] = $emp_no;
$record['trans_no'] = $trans_no;
$record['trans_id'] = 1;
$record['trans_type'] = 'D';
$record['quantity'] = 1.0;
$record['ItemQtty'] = 1.0;
$record['memType'] = 1;
foreach ($lines as $line) {
$record['total'] = sprintf('%.2f', $line['amount']);
$record['unitPrice'] = sprintf('%.2f', $line['amount']);
$record['regPrice'] = sprintf('%.2f', $line['amount']);
$record['department'] = $line['department'];
$record['description'] = substr($line['description'], 0, 30);
$record['card_no'] = $line['card_no'];
$record['upc'] = sprintf('%.2fDP%d', $line['amount'], $line['department']);
$p = DTrans::parameterize($record, 'datetime', $dbc->now());
$query = "INSERT INTO dtransactions ({$p['columnString']}) VALUES ({$p['valueString']})";
$prep = $dbc->prepare($query);
$write = $dbc->execute($prep, $p['arguments']);
$record['trans_id']++;
}
return $emp_no . '-' . $register_no . '-' . $trans_no;
}
public function run()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_AR_DEPARTMENTS, $FANNIE_SERVER_DBMS;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$date = date('Y-m-t 23:59:59', mktime(0, 0, 0, date('n') - 1));
$cn = 'SELECT CardNo FROM ' . $FANNIE_OP_DB . $dbc->sep() . 'custdata
WHERE memType=4 and personNum=1';
$r = $dbc->query($cn);
$balQ = 'SELECT balance FROM ar_live_balance where card_no=?';
$balP = $dbc->prepare($balQ);
$tn = 1;
while ($w = $dbc->fetch_row($r)) {
$balR = $dbc->execute($balP, array($w['CardNo']));
if ($balW = $dbc->fetch_row($balR)) {
if ($balW[0] > 0) {
$record = DTrans::$DEFAULTS;
$datetime = date('\'Y-m-t 00:00:00\'', mktime(0, 0, 0, date('n') - 1));
$record['emp_no'] = 1001;
$record['register_no'] = 20;
$record['upc'] = $balW[0] . 'DP990';
$record['description'] = 'AR Payment';
$record['department'] = 990;
$record['quantity'] = 1;
$record['ItemQtty'] = 1;
$record['card_no'] = $w['CardNo'];
$record['regPrice'] = $balW[0];
$record['total'] = $balW[0];
$record['unitPrice'] = $balW[0];
$record['trans_no'] = $tn;
$record['trans_id'] = 1;
$info = DTrans::parameterize($record, 'datetime', $datetime);
$query = 'INSERT INTO dtransactions (' . $info['columnString'] . ') VALUES (' . $info['valueString'] . ')';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $info['arguments']);
$tn++;
}
}
}
}
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'));
$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;
}
if (strtotime($repDate) <= strtotime('2015-04-01')) {
$countyTax = 0;
}
$taxQ = $dbc->prepare_statement("\nSELECT \n (CASE WHEN d.tax = 1 THEN 'Non Deli Sales' ELSE 'Deli Sales' END) as type, \n sum(total) as taxable_sales,\n {$cityTax}*(sum(total)) as city_tax,\n {$deliTax}*(sum(CASE WHEN d.tax = 2 THEN total ELSE 0 END)) as deli_tax,\n {$stateTax}*(sum(total)) as state_tax,\n {$countyTax}*(SUM(total)) AS county_tax\nFROM {$dlog} as d \nWHERE d.tdate BETWEEN ? AND ?\nAND d.tax <> 0 \nAND " . DTrans::isStoreID($store, 'd') . "\nGROUP BY d.tax ORDER BY d.tax DESC");
$taxR = $dbc->exec_statement($taxQ, $store_dates);
$taxes = array();
while ($row = $dbc->fetch_row($taxR)) {
$taxes["{$row['0']}"] = array(-1 * $row['taxable_sales'], -1 * $row['city_tax'], -1 * $row['deli_tax'], -1 * $row['county_tax'], -1 * $row['state_tax'], -1 * ($row['city_tax'] + $row['county_tax'] + $row['state_tax'] + $row['deli_tax']));
}
echo "<br /><b>Sales Tax</b>";
echo WfcLib::tablify($taxes, array(0, 1, 2, 3, 4, 5, 6), array(" ", "Taxable Sales", sprintf("City Tax (%.2f%%)", $cityTax * 100), sprintf("Deli Tax (%.2f%%)", $deliTax * 100), sprintf("County Tax (%.2f%%)", $countyTax * 100), sprintf("State Tax (%.3f%%)", $stateTax * 100), "Total Tax"), array(WfcLib::ALIGN_LEFT, WfcLib::ALIGN_RIGHT | WfcLib::TYPE_MONEY, WfcLib::ALIGN_RIGHT | WfcLib::TYPE_MONEY, WfcLib::ALIGN_RIGHT | WfcLib::TYPE_MONEY, WfcLib::ALIGN_RIGHT | WfcLib::TYPE_MONEY, WfcLib::ALIGN_RIGHT | WfcLib::TYPE_MONEY, WfcLib::ALIGN_RIGHT | WfcLib::TYPE_MONEY));
$taxSumQ = $dbc->prepare_statement("SELECT -1*sum(total) as tax_collected\nFROM {$dlog} as d \nWHERE d.tdate BETWEEN ? AND ?\nAND (d.upc = 'tax')\nAND " . DTrans::isStoreID($store, 'd') . "\nGROUP BY d.upc");
$taxSumR = $dbc->exec_statement($taxSumQ, $store_dates);
echo "<br /><b><u>Actual Tax Collected</u></b><br />";
echo sprintf("%.2f<br />", array_pop($dbc->fetch_row($taxSumR)));
$transQ = $dbc->prepare_statement("select q.trans_num,sum(q.quantity) as items,transaction_type, sum(q.total) from\n (\n select trans_num,card_no,quantity,total,\n m.memdesc as transaction_type\n from {$dlog} as d\n left join custdata as c on d.card_no = c.cardno\n left join memTypeID as m on c.memtype = m.memTypeID\n WHERE d.tdate BETWEEN ? AND ?\n AND trans_type in ('I','D')\n and upc <> 'RRR'\n and c.personNum=1\n AND " . DTrans::isStoreID($store, 'd') . "\n ) as q \n group by q.trans_num,q.transaction_type");
$transR = $dbc->exec_statement($transQ, $store_dates);
$transinfo = array("Member" => array(0, 0.0, 0.0, 0.0, 0.0), "Non Member" => array(0, 0.0, 0.0, 0.0, 0.0), "Staff Member" => array(0, 0.0, 0.0, 0.0, 0.0), "Staff NonMem" => array(0, 0.0, 0.0, 0.0, 0.0));
while ($row = $dbc->fetch_array($transR)) {
if (!isset($transinfo[$row[2]])) {
continue;
}
$transinfo[$row[2]][0] += 1;
$transinfo[$row[2]][1] += $row[1];
$transinfo[$row[2]][3] += $row[3];
}
$tSum = 0;
$tItems = 0;
$tDollars = 0;
foreach (array_keys($transinfo) as $k) {
$transinfo[$k][2] = round($transinfo[$k][1] / $transinfo[$k][0], 2);
/**
Do whatever the service is supposed to do.
Should override this.
@param $args array of data
@return an array of data
*/
public function run($args = array())
{
$ret = array();
if (!property_exists($args, 'field') || !property_exists($args, 'search')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
} else {
if (strlen($args->search) < 1) {
// search term is too short
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
}
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->field)) {
case 'item':
$res = false;
if (!is_numeric($args->search)) {
$prep = $dbc->prepare('SELECT p.upc,
p.description
FROM products AS p
LEFT JOIN productUser AS u ON u.upc=p.upc
WHERE p.description LIKE ?
OR p.brand LIKE ?
OR u.description LIKE ?
OR u.brand LIKE ?
GROUP BY p.upc,
p.description
ORDER BY p.description');
$term = '%' . $args->search . '%';
$res = $dbc->execute($prep, array($term, $term, $term, $term));
} elseif (ltrim($args->search, '0') != '') {
$prep = $dbc->prepare('
SELECT p.upc,
p.upc AS description
FROM products AS p
WHERE p.upc LIKE ?
GROUP BY p.upc');
$res = $dbc->execute($prep, array('%' . $args->search . '%'));
}
while ($res && ($row = $dbc->fetch_row($res))) {
$ret[] = array('label' => $row['description'], 'value' => $row['upc']);
}
case 'brand':
$prep = $dbc->prepare('SELECT brand
FROM products
WHERE brand LIKE ?
GROUP BY brand
ORDER BY brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'long_brand':
$prep = $dbc->prepare('
SELECT u.brand
FROM productUser AS u
' . DTrans::joinProducts('u', 'p', 'INNER') . '
WHERE u.brand LIKE ?
GROUP BY u.brand
ORDER BY u.brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'vendor':
$prep = $dbc->prepare('SELECT vendorID,
vendorName
FROM vendors
WHERE vendorName LIKE ?
ORDER BY vendorName');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['vendorName'];
}
if ($dbc->tableExists('prodExtra')) {
$prep = $dbc->prepare('SELECT distributor
FROM prodExtra
WHERE distributor LIKE ?
GROUP BY distributor
ORDER BY distributor');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
if (!in_array($row['distributor'], $ret)) {
$ret[] = $row['distributor'];
}
}
}
return $ret;
case 'mfirstname':
case 'mlastname':
case 'maddress':
case 'mcity':
case 'memail':
return \COREPOS\Fannie\API\member\MemberREST::autoComplete($args->field, $args->search);
case 'sku':
$query = 'SELECT sku
FROM vendorItems
WHERE sku LIKE ? ';
$param = array($args->search . '%');
if (property_exists($args, 'vendor_id')) {
$query .= ' AND vendorID=? ';
$param[] = $args->vendor_id;
}
$query .= 'GROUP BY sku
ORDER BY sku';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['sku'];
if (count($ret) > 50) {
break;
}
}
return $ret;
case 'unit':
$query = '
SELECT unitofmeasure
FROM products
WHERE unitofmeasure LIKE ?
GROUP BY unitofmeasure
ORDER BY unitofmeasure';
$param = array($args->search . '%');
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetchRow($res)) {
$ret[] = $row['unitofmeasure'];
if (count($ret) > 50) {
break;
}
}
return $ret;
default:
return $ret;
}
}
public function get_id_view()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$prep = $dbc->prepare("\n SELECT m.sku,\n m.upc,\n v.description AS vendorDescript,\n p.description as storeDescript\n FROM VendorBreakdowns AS m\n " . DTrans::joinProducts('m') . "\n LEFT JOIN vendorItems AS v ON v.sku=m.sku AND v.vendorID=m.vendorID\n WHERE m.vendorID = ?\n ORDER BY m.upc\n ");
$ret = '';
$ret .= '<form action="' . $_SERVER['PHP_SELF'] . '" method="get">';
$ret .= '<div class="form-group form-inline">
<label>SKU</label>
<input type="text" class="form-control" name="sku" placeholder="Vendor SKU" />
<label>PLU/UPC</label>
<input type="text" class="form-control" name="plu" placeholder="Our PLU" />
<button type="submit" class="btn btn-default">Add Entry</button>
<input type="hidden" name="id" value="' . $this->id . '" />
<a href="?id=' . $this->id . '&break=1" class="btn btn-default">Run Breakdowns</a>
<a href="VendorIndexPage.php?vid=' . $this->id . '" class="btn btn-default">Home</a>
</div>
</form>';
$ret .= '<table class="table table-bordered">';
$ret .= '<thead><tr>
<th>Vendor SKU</th>
<th>Our PLU</th>
<th>Vendor Description</th>
<th>Our Description</th>
<th> </th>
</tr></thead><tbody>';
$res = $dbc->execute($prep, array($this->id));
while ($row = $dbc->fetchRow($res)) {
if (empty($row['vendorDescript'])) {
$row['vendorDescript'] = '<span class="alert-danger">Discontinued by vendor?</span>';
}
if (empty($row['storeDescript'])) {
$row['storeDescript'] = '<span class="alert-danger">Discontinued by us?</span>';
}
$ret .= sprintf('
<tr>
<td>%s</td>
<td><a href="../ItemEditorPage.php?searchupc=%s">%s</a></td>
<td>%s</td>
<td>%s</td>
<td>
<a href="?_method=delete&id=%d&sku=%s&plu=%s"
onclick="return confirm(\'Delete entry for PLU #%s?\');">%s</a>
</td>
</tr>', $row['sku'], $row['upc'], $row['upc'], $row['vendorDescript'], $row['storeDescript'], $this->id, $row['sku'], $row['upc'], $row['upc'], FannieUI::deleteIcon());
}
$ret .= '</tbody></table>';
$this->addScript('../../src/javascript/tablesorter/jquery.tablesorter.js');
$this->addOnloadCommand("\$('.table').tablesorter([[1,0]]);\n");
return $ret;
}
function get_view()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
/**
Excludes:
Values with spaces (fixed in lanecode going forward)
One and two digit PLUs (likely simply miskeys)
Values with no leading zeroes (EAN-13 and UPC-A should have
at least one. I do have some values with no leading zeroes
but not sure yet what they are. Do not appear to be GTIN-14).
*/
$dbc = FannieDB::get($FANNIE_OP_DB);
$query = "SELECT t.upc, COUNT(t.upc) AS instances,\n MIN(datetime) as oldest,\n MAX(datetime) as newest,\n p.description as prod,\n MAX(v.description) as vend, MAX(n.vendorName) as vendorName, MAX(v.srp) as srp\n FROM " . $FANNIE_TRANS_DB . $dbc->sep() . "transarchive AS t\n " . DTrans::joinProducts('t') . "\n LEFT JOIN vendorItems AS v ON t.upc=v.upc\n LEFT JOIN vendors AS n ON v.vendorID=n.vendorID\n WHERE t.trans_type='L' AND t.description='BADSCAN'\n AND t.upc NOT LIKE '% %'\n AND t.upc NOT LIKE '00000000000%'\n AND (t.upc NOT LIKE '00000000%' OR p.upc IS NOT NULL OR v.upc IS NOT NULL)";
if ($this->date_restrict) {
$query .= ' AND datetime >= ' . date('\'Y-m-d 00:00:00\'', strtotime('-8 days'));
}
$query .= "GROUP BY t.upc, p.description\n ORDER BY t.upc DESC";
if ($this->date_restrict == 2) {
$query = str_replace('transarchive', 'dtransactions', $query);
}
$result = $dbc->query($query);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$data[] = $row;
}
// stick a total in the cache along with SQL results
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$query = "SELECT COUNT(*) FROM transarchive WHERE trans_type='I' AND upc <> '0'";
$result = $dbc->query($query);
$row = $dbc->fetch_row($result);
$data['itemTTL'] = $row[0];
$ret = '';
$ret .= '<div class="nav">';
$ret .= '<a href="BadScanTool.php?lastquarter=1"
class="btn btn-default navbar-btn' . (!$this->date_restrict ? ' active' : '') . '">View Last Quarter</a>';
$ret .= ' ';
$ret .= '<a href="BadScanTool.php"
class="btn btn-default navbar-btn' . ($this->date_restrict == 1 ? ' active' : '') . '">View Last Week</a>';
$ret .= ' ';
$ret .= '<a href="BadScanTool.php?today=1"
class="btn btn-default navbar-btn' . ($this->date_restrict == 2 ? ' active' : '') . '">View Today</a>';
$ret .= '</div>';
$ret .= '<br /><b>Show</b>: ';
$ret .= '<input type="radio" name="rdo" id="rdoa" onclick="showAll();" />
<label for="rdoa">All</label>';
$ret .= ' ';
$ret .= '<input type="radio" name="rdo" id="rdom" onclick="showMultiple();" />
<label for="rdom">Repeats</label>';
$ret .= ' ';
$ret .= '<input type="radio" name="rdo" id="rdof" onclick="showFixable();" checked />
<label for="rdof">Fixable</label>';
$ret .= '<br />';
$ret .= '<div class="well">';
$ret .= '<span class="alert-success">Green items have been entered in POS</span>. ';
$ret .= '<span class="alert-danger">Red items can be added from vendor catalogs</span>. ';
$ret .= '<span class="alert-info">Blue items can also be added from vendor catalogs but
may not be needed. All scans are within a 5 minute window. May indicate a special
order case scanned by mistake or a bulk purchase in a barcoded container.</span> ';
$ret .= 'Other items are not identifiable with available information';
$ret .= '</div>';
$ret .= '<table id="scantable" class="table"><thead>';
$ret .= '<tr id="tableheader"><th>UPC</th><th># Scans</th><th>Oldest</th><th>Newest</th>
<th>In POS</th><th>In Vendor Catalog</th><th>SRP</th></tr>';
$ret .= '</thead><tbody>';
$scanCount = 0;
foreach ($data as $row) {
if (count($row) == 1) {
// cached item total
continue;
}
$css = '';
$fixButton = '';
$span = strtotime($row['newest']) - strtotime($row['oldest']);
if (!empty($row['prod'])) {
$css = 'class="fixed alert alert-success collapse"';
} else {
if (!empty($row['vend']) && !empty($row['srp'])) {
if ($span > 300) {
$css = 'class="fixable alert alert-danger"';
} else {
$css = 'class="semiFixable alert alert-info"';
}
$fixButton = ' <a href="ItemEditorPage.php?searchupc= ' . $row['upc'] . '" target="_new' . $row['upc'] . '">ADD</a>';
} else {
if ($row['instances'] == 1) {
$css = 'class="loner collapse"';
} else {
$css = 'class="collapse"';
}
}
}
$ret .= sprintf('<tr %s><td>%s</td><td>%d</td><td>%s</td><td>%s</td>
<td>%s</td><td>%s</td><td>%s</td>
<td><a href="OpenRingReceipts.php?upc=%s&date1=%s&date2=%s">View Receipts</a></td>
</tr>', $css, $row['upc'], $row['instances'], $row['oldest'], $row['newest'], !empty($row['prod']) ? "Yes ({$row['prod']})" : 'No', !empty($row['vend']) ? "Yes ({$row['vendorName']} {$row['vend']})" : 'No', !empty($row['srp']) ? $row['srp'] . $fixButton : 'n/a', $row['upc'], $row['oldest'], $row['newest']);
$scanCount += $row['instances'];
}
$ret .= '</tbody></table>';
$ret .= '<div id="ratio">';
$ret .= sprintf('Approx. bad scan rate: %.2f%%', $data['itemTTL'] == 0 ? 0 : (double) $scanCount / ((double) $data['itemTTL'] != 0) * 100);
$ret .= '</div>';
$this->addScript('../src/javascript/tablesorter/jquery.tablesorter.min.js');
$this->addOnloadCommand("\$('#scantable').tablesorter();\n");
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());
$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;
}
public function form_content()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$set = FormLib::get('deal-set');
$optsR = $dbc->query('
SELECT dealSet
FROM CoopDealsItems
GROUP BY dealSet
ORDER BY MAX(coopDealsItemID) DESC');
$opts = '';
while ($optsW = $dbc->fetchRow($optsR)) {
if ($set === '') {
$set = $optsW['dealSet'];
}
$opts .= sprintf('<option %s>%s</option>', $set == $optsW['dealSet'] ? 'selected' : '', $optsW['dealSet']);
}
$query = $dbc->prepare_statement("\n SELECT\n t.upc,\n p.brand,\n p.description,\n t.price,\n CASE WHEN s.super_name IS NULL THEN 'sale' ELSE s.super_name END as batch,\n t.abtpr as subbatch\n FROM CoopDealsItems as t\n " . DTrans::joinProducts('t', 'p', 'INNER') . "\n LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID\n WHERE t.dealSet=?\n AND p.inUse=1\n ORDER BY s.super_name,t.upc\n ");
$result = $dbc->exec_statement($query, array($set));
$ret = "<form action=CoopDealsReviewPage.php method=post>\n <div class=\"form-group\">\n <label>Month</label>\n <select name=\"deal-set\" class=\"form-control\" \n onchange=\"location='?deal-set='+this.value;\">\n " . $opts . "\n </select>\n </div>\n <table class=\"table table-bordered table-striped tablesorter tablesorter-core small\">\n <thead>\n <tr><th>UPC</th><th>Brand</th><th>Desc</th><th>Sale Price</th>\n <th>New Batch Name</th></tr>\n\n </thead><tbody>";
while ($row = $dbc->fetch_row($result)) {
$ret .= sprintf('<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%.2f</td>
<td><span class="superNameSpan">%s </span>Co-op Deals %s</td>
</tr>' . "\n", $row['upc'], $row['brand'], $row['description'], $row['price'], $row['batch'], $row['subbatch']);
}
$ret .= <<<html
</tbody>
</table><p />
<div class="row form-horizontal form-group">
<label class="col-sm-2 control-label">A Start</label>
<div class="col-sm-4">
<input type="text" name="start" id="start" class="form-control date-field" />
</div>
<label class="col-sm-2 control-label">B Start</label>
<div class="col-sm-4">
<input type="text" name="bstart" id="bstart" class="form-control date-field" />
</div>
</div>
<div class="row form-horizontal form-group">
<label class="col-sm-2 control-label">A End</label>
<div class="col-sm-4">
<input type="text" name="end" id="end" class="form-control date-field" />
</div>
<label class="col-sm-2 control-label">B End</label>
<div class="col-sm-4">
<input type="text" name="bend" id="bend" class="form-control date-field" />
</div>
</div>
<div class="row form-horizontal form-group">
<label class="col-sm-2 control-label">Month</label>
<div class="col-sm-4">
<input type="text" name="naming" class="form-control" value="{{set}}" />
</div>
<label class="col-sm-6">
<input type="checkbox" name="group_by_superdepts" checked="true"
onchange="\$('.superNameSpan').toggle(); " />
Group sale batches by Superdepartment
</label>
</div>
<p>
<button type=submit class="btn btn-default">Create Batch(es)</button>
<a href="CoopDealsMergePage.php" class="pull-right btn btn-default">Merge New Items into Existing Batch(es)</a>
</p>
</form>
html;
$ret = str_replace('{{set}}', $set, $ret);
return $ret;
}
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;
}
function finish_content()
{
if (!empty($this->errors)) {
return $this->errors;
}
$ret = '';
$trans_no = DTrans::getTransNo($this->connection, $this->CORRECTION_CASHIER, $this->CORRECTION_LANE);
$params = array('card_no' => $this->cn, 'register_no' => $this->CORRECTION_LANE, 'emp_no' => $this->CORRECTION_CASHIER);
DTrans::addOpenRing($this->connection, $this->dept1, -1 * $this->amount, $trans_no, $params);
DTrans::addOpenRing($this->connection, $this->dept2, $this->amount, $trans_no, $params);
$comment = FormLib::get_form_value('comment');
if (!empty($comment)) {
$params = array('description' => $comment, 'trans_type' => 'C', 'trans_subtype' => 'CM', 'card_no' => $this->cn, 'register_no' => $this->CORRECTION_LANE, 'emp_no' => $this->CORRECTION_CASHIER);
DTrans::addItem($this->connection, $trans_no, $params);
}
$ret .= sprintf("Receipt #1: %s", $this->CORRECTION_CASHIER . '-' . $this->CORRECTION_LANE . '-' . $trans_no);
return $ret;
}
