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);
$date1 .= ' 00:00:00';
$date2 .= ' 23:59:59';
$sales = "SELECT year(tdate) as year, month(tdate) as month,\n day(tdate) as day,max(memType) as memType,trans_num\n FROM {$dlog} as t\n WHERE \n tdate BETWEEN ? AND ?\n and trans_type = 'T'\n AND upc <> 'RRR'\n group by year(tdate),month(tdate),day(tdate),trans_num\n order by year(tdate),month(tdate),day(tdate),max(memType)";
$salesP = $dbc->prepare_statement($sales);
$result = $dbc->exec_statement($salesP, array($date1, $date2));
/**
Create result records based on date and increment them
when the same type is encountered again
*/
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$stamp = date("M j, Y", mktime(0, 0, 0, $row['month'], $row['day'], $row['year']));
if (!isset($ret[$stamp])) {
$ret[$stamp] = array("date" => $stamp);
foreach ($this->memtypes as $id => $desc) {
$ret[$stamp][$id] = 0;
}
$ret[$stamp]['ttl'] = 0;
}
$ret[$stamp]["ttl"]++;
if (!isset($ret[$stamp][$row['memType']])) {
$ret[$stamp][$row['memType']] = 0;
}
$ret[$stamp][$row['memType']]++;
}
$ret = $this->dekey_array($ret);
return $ret;
}
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;
}
function fetch_report_data()
{
$dbc = $this->connection;
$FANNIE_OP_DB = $this->config->get('OP_DB');
$dbc->selectDB($FANNIE_OP_DB);
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$card_no = FormLib::get_form_value('card_no', '0');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "select month(t.tdate),day(t.tdate),year(t.tdate),\n t.upc,p.description,\n t.department,d.dept_name,m.super_name,\n sum(t.quantity) as qty,\n sum(t.total) as ttl from\n {$dlog} as t left join {$FANNIE_OP_DB}.products as p on t.upc = p.upc \n left join {$FANNIE_OP_DB}.departments AS d ON t.department=d.dept_no\n left join {$FANNIE_OP_DB}.MasterSuperDepts AS m ON t.department=m.dept_ID\n where t.card_no = ? AND\n trans_type IN ('I','D') AND\n tdate BETWEEN ? AND ?\n group by year(t.tdate),month(t.tdate),day(t.tdate),\n t.upc,p.description\n order by year(t.tdate),month(t.tdate),day(t.tdate)";
$args = array($card_no, $date1 . ' 00:00:00', $date2 . ' 23:59:59');
$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['description'];
$record[] = $row['department'] . ' ' . $row['dept_name'];
$record[] = $row['super_name'];
$record[] = $row['qty'];
$record[] = $row['ttl'];
$ret[] = $record;
}
return $ret;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc = BarcodeLib::padUPC(FormLib::get('upc'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$q = $dbc->prepare("\n SELECT d.card_no,\n sum(quantity) as qty,\n sum(total) as amt\n FROM {$dlog} AS d \n WHERE d.upc=? AND \n tdate BETWEEN ? AND ?\n GROUP BY d.card_no\n ORDER BY d.card_no");
$r = $dbc->exec_statement($q, array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$data = array();
while ($w = $dbc->fetch_row($r)) {
$account = \COREPOS\Fannie\API\member\MemberREST::get($w['card_no']);
if ($account == false) {
continue;
}
$customer = array();
foreach ($account['customers'] as $c) {
if ($c['accountHolder']) {
$customer = $c;
break;
}
}
$record = array($w['card_no'], $customer['lastName'] . ', ' . $customer['firstName'], $account['addressFirstLine'] . ' ' . $account['addressSecondLine'], $account['city'], $account['state'], $account['zip'], $customer['phone'], $customer['altPhone'], $customer['email'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['amt']));
$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', '');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$queryAll = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d \n WHERE tdate BETWEEN ? AND ?\n GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)\n ORDER BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
$argsAll = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$querySelected = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d ";
if ($buyer !== '' && $buyer > -1) {
$querySelected .= " LEFT JOIN superdepts AS s ON d.department=s.dept_ID ";
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$querySelected .= " WHERE tdate BETWEEN ? AND ? ";
$argsSel = $argsAll;
if ($buyer !== '') {
if ($buyer == -2) {
$querySelected .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$querySelected .= ' AND s.superID=? ';
$argsSel[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $argsSel) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $argsSel);
$querySelected .= $conditional;
}
$querySelected .= " GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
$dataset = array();
$prep = $dbc->prepare_statement($queryAll);
$result = $dbc->exec_statement($prep, $argsAll);
while ($row = $dbc->fetch_row($result)) {
$datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
$dataset[$datestr] = array('ttl' => $row['trans_count'], 'sub' => 0);
}
$prep = $dbc->prepare_statement($querySelected);
$result = $dbc->exec_statement($prep, $argsSel);
while ($row = $dbc->fetch_row($result)) {
$datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
if (isset($dataset[$datestr])) {
$dataset[$datestr]['sub'] = $row['trans_count'];
}
}
$data = array();
foreach ($dataset as $date => $count) {
$record = array($date, $count['sub'], $count['ttl']);
$record[] = sprintf('%.2f%%', $count['sub'] / $count['ttl'] * 100);
$data[] = $record;
}
return $data;
}
public function get_date1_date2_handler()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$dlog = DTransactionsModel::selectDlog($this->date1, $this->date2);
$dtrans = DTransactionsModel::selectDtrans($this->date1, $this->date2);
$openQ = '
SELECT YEAR(tdate) AS year,
MONTH(tdate) AS month,
DAY(tdate) AS day,
emp_no,
register_no,
trans_no
FROM ' . $dlog . ' AS d
INNER JOIN MasterSuperDepts AS m ON m.dept_ID=d.department
WHERE tdate BETWEEN ? AND ?
AND trans_type=\'D\'
AND m.superID <> 0
GROUP BY YEAR(tdate),
MONTH(tdate),
DAY(tdate),
emp_no,
register_no,
trans_no
HAVING SUM(total) <> 0';
$badQ = '
SELECT upc
FROM ' . $dtrans . ' AS d
WHERE datetime BETWEEN ? AND ?
AND emp_no=?
AND register_no=?
AND trans_no=?
AND trans_type=\'L\'
AND description=\'BADSCAN\'
AND d.upc LIKE \'0%\'
AND d.upc NOT LIKE \'00000000000%\'';
$openP = $dbc->prepare($openQ);
$badP = $dbc->prepare($badQ);
$filter = FormLib::get('badscans', false);
$this->receipts = array();
$openR = $dbc->execute($openP, array($this->date1 . ' 00:00:00', $this->date2 . ' 23:59:59'));
while ($openW = $dbc->fetchRow($openR)) {
$ts = mktime(0, 0, 0, $openW['month'], $openW['day'], $openW['year']);
if ($filter) {
$args = array(date('Y-m-d 00:00:00', $ts), date('Y-m-d 23:59:59', $ts), $openW['emp_no'], $openW['register_no'], $openW['trans_no']);
if (FormLib::get('upc') != '') {
$args[] = FormLib::get('upc');
}
$badR = $dbc->execute($badP, $args);
if (!$badR || $dbc->num_rows($badR) == 0) {
continue;
}
}
$this->receipts[] = array('date' => date('Y-m-d', $ts), 'trans_num' => $openW['emp_no'] . '-' . $openW['register_no'] . '-' . $openW['trans_no']);
}
return true;
}
public function fetch_report_data()
{
$items = explode("\n", $this->form->items);
$ts1 = strtotime('last tuesday');
// -13 => previous wednesday and then back another full week
$ts2 = mktime(0, 0, 0, date('n', $ts1), date('j', $ts1) - 13, date('Y', $ts1));
$dlog = DTransactionsModel::selectDlog(date('Y-m-d', $ts2), date('Y-m-d', $ts1));
switch ($this->form->type) {
case 'lc':
list($query, $args) = $this->lcQuery($dlog, $items);
break;
case 'scale':
list($query, $args) = $this->scaleQuery($dlog, $items);
break;
default:
case 'upc':
list($query, $args) = $this->upcQuery($dlog, $items);
break;
}
$dbc = $this->connection;
$prep = $dbc->prepare($query);
// force output order to match input order
$order = array();
for ($i = 0; $i < count($args); $i++) {
$order[$args[$i]] = $i + 1;
}
$args[] = date('Y-m-d 00:00:00', $ts2);
$args[] = date('Y-m-d 23:59:59', $ts1);
$res = $dbc->execute($prep, $args);
$data = array();
$cpt = array();
$cpi = array();
while ($row = $dbc->fetchRow($res)) {
$data[] = array($order[$row['upc']], $row['upc'], $row['brand'], $row['description'], sprintf('%.2f', $row['qty'] / 2));
$cpt[$order[$row['upc']]] = $row['qty'] / 2;
$cpi[$order[$row['upc']]] = array($row['brand'], $row['description'], $row['upc'], $row['size'], $row['cost'], $row['normal_price']);
}
$table = '<table class="table small table-bordered">
<tr><th>Copy/Paste Items</th>';
ksort($cpi);
foreach ($cpi as $id => $row) {
$table .= sprintf('<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%.2f</td><td>%.2f</td></tr>', $row[0], $row[1], $row[2], $row[3], $row[4], $row[5]);
}
$table .= '</table>';
$table .= '<table class="table small table-bordered">
<tr><th>Copy/Paste Movement</th>';
ksort($cpt);
foreach ($cpt as $id => $qty) {
$table .= sprintf('<tr><td>%.2f</td></tr>', $qty);
}
$table .= '</table>';
if ($this->report_format == 'html') {
echo $table;
}
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;
}
protected function get_date1_date2_mtype_stype_handler()
{
global $FANNIE_TRANS_DB, $FANNIE_ROOT, $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
include $FANNIE_ROOT . 'install/db.php';
$mtype = "(";
$mArgs = array();
foreach ($this->mtype as $m) {
$mtype .= '?,';
$mArgs[] = (int) $m;
}
$mtype = rtrim($mtype, ",") . ")";
$stype = '(';
$sArgs = array();
foreach ($this->stype as $s) {
$stype .= '?,';
$sArgs[] = (int) $s;
}
$stype = rtrim($stype, ',') . ')';
if (count($sArgs) == 0) {
$stype = '(?)';
$sArgs = array('-9999');
}
$dlog = DTransactionsModel::selectDlog($this->date1, $this->date2);
if ($dbc->table_exists("dlog_patronage")) {
$drop = $dbc->prepare_statement("DROP TABLE dlog_patronage");
$dbc->exec_statement($drop);
}
$create = $dbc->prepare_statement('CREATE TABLE dlog_patronage (card_no INT, trans_type VARCHAR(2),
trans_subtype VARCHAR(2), total DECIMAL(10,2), min_year INT, max_year INT,
primary key (card_no, trans_type, trans_subtype))');
$dbc->exec_statement($create);
$insQ = sprintf("\n INSERT INTO dlog_patronage\n SELECT d.card_no,\n trans_type,\n trans_subtype,\n sum(total),\n YEAR(MIN(tdate)) AS firstDate, \n YEAR(MAX(tdate)) AS lastDate\n FROM %s AS d\n LEFT JOIN %s%scustdata AS c ON c.CardNo=d.card_no AND c.personNum=1 \n LEFT JOIN %s%ssuspensions AS s ON d.card_no=s.cardno\n LEFT JOIN %s%sMasterSuperDepts AS m ON d.department=m.dept_ID\n WHERE d.trans_type IN ('I','D','S','T')\n AND d.total <> 0 \n AND (s.memtype1 IN %s OR c.memType IN %s)\n AND (m.superID IS NULL OR m.superID NOT IN %s)\n AND d.tdate BETWEEN ? AND ?\n GROUP BY d.card_no, trans_type, trans_subtype", $dlog, $FANNIE_OP_DB, $dbc->sep(), $FANNIE_OP_DB, $dbc->sep(), $FANNIE_OP_DB, $dbc->sep(), $mtype, $mtype, $stype);
$args = $mArgs;
foreach ($mArgs as $m) {
$args[] = $m;
}
// need them twice
foreach ($sArgs as $s) {
$args[] = $s;
}
$args[] = $this->date1 . ' 00:00:00';
$args[] = $this->date2 . ' 23:59:59';
$prep = $dbc->prepare_statement($insQ);
$worked = $dbc->exec_statement($prep, $args);
if ($worked) {
$this->add_onload_command("showBootstrapAlert('#alert-area', 'success', 'Patronage Snapshot Created');\n");
} else {
$this->add_onload_command("showBootstrapAlert('#alert-area', 'danger', 'Error creating snapshot');\n");
}
return true;
}
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;
}
public function run()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$p_def = $dbc->tableDefinition('products');
if (!isset($p_def['last_sold'])) {
$this->logger->warning('products table does not have a last_sold column');
return;
}
$update = $dbc->prepare('
UPDATE products
SET last_sold=?
WHERE upc=?');
// really old date to ensure we get the whole history
$dlog = DTransactionsModel::selectDlog('1950-01-01', date('Y-m-d'));
$missingR = $dbc->query('
SELECT upc
FROM products
WHERE last_sold IS NULL');
/**
Lookup each transaction containing the UPC instead of
just MAX(tdate) over the time period. This is to adjust
for voids. We want the lastest transaction where the item
had a non-zero total.
*/
$lastSoldP = $dbc->prepare('
SELECT upc,
YEAR(tdate),
MONTH(tdate),
DAY(tdate),
trans_num,
MAX(tdate) AS last_sold
FROM ' . $dlog . '
WHERE trans_type=\'I\'
AND upc=?
GROUP BY YEAR(tdate),
MONTH(tdate),
DAY(tdate),
trans_num,
upc
HAVING SUM(total) <> 0
ORDER BY tdate
');
while ($missingW = $dbc->fetchRow($missingR)) {
echo "Scanning sales for {$missingW['upc']}\n";
$res = $dbc->execute($lastSoldP, $missingW['upc']);
while ($w = $dbc->fetchRow($res)) {
$dbc->execute($update, array($w['last_sold'], $w['upc']));
}
}
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$d1 = $this->form->date1;
$d2 = $this->form->date2;
$dlog = DTransactionsModel::selectDlog($d1, $d2);
$query = $dbc->prepare_statement("SELECT \n CASE WHEN upc='0' THEN 'NOT SCANNED' ELSE upc END as upc, \n sum(CASE WHEN upc='0' THEN 1 ELSE quantity END) as qty,\n sum(-total) as ttl FROM {$dlog}\n WHERE trans_subtype='CP'\n AND tdate BETWEEN ? AND ?\n GROUP BY upc\n ORDER BY upc");
$result = $dbc->exec_statement($query, array($d1 . ' 00:00:00', $d2 . ' 23:59:59'));
$data = array();
while ($row = $dbc->fetch_row($result)) {
$data[] = array($row['upc'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['ttl']));
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$d1 = FormLib::get('date1', date('Y-m-d'));
$d2 = FormLib::get('date2', date('Y-m-d'));
$dlog = DTransactionsModel::selectDlog($d1, $d2);
$query = $dbc->prepare_statement("\n SELECT m.memDesc,\n SUM(total) AS total \n FROM {$dlog} AS d\n LEFT JOIN memtype AS m ON d.memType=m.memtype\n WHERE d.upc='DISCOUNT'\n AND tdate BETWEEN ? AND ?\n GROUP BY m.memDesc\n ORDER BY m.memDesc");
$result = $dbc->exec_statement($query, array($d1 . ' 00:00:00', $d2 . ' 23:59:59'));
$data = array();
while ($row = $dbc->fetch_row($result)) {
$data[] = array($row['memDesc'], sprintf('%.2f', $row['total']));
}
return $data;
}
public function refresh_data($trans_db, $month, $year, $day = False)
{
$start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year));
$end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year));
$end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year));
if ($day !== False) {
$start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year));
$end_id = $start_id;
$end_date = $start_date;
}
$target_table = DTransactionsModel::selectDlog($start_date, $end_date);
/* clear old entries */
$sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?';
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_id, $end_id));
/* reload table from transarction archives
The process for this controller is iterative because of
an old bug that assigns incorrect values to the transaction's
memType column on records with trans_status 'M'. Using
aggregates directly on the table over-counts memType zero
so instead we count transactions one at a time.
*/
$sql = "SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n MAX(memType) as memType,\n CONVERT(SUM(total),DECIMAL(10,2)) as total,\n CONVERT(SUM(CASE WHEN trans_status='M' THEN itemQtty \n WHEN unitPrice=0.01 THEN 1 ELSE quantity END),DECIMAL(10,2)) as quantity\n FROM {$target_table} WHERE\n tdate BETWEEN ? AND ? AND\n trans_type IN ('I','D') AND upc <> 'RRR'\n AND card_no <> 0 AND memType IS NOT NULL\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), trans_num\n ORDER BY DATE_FORMAT(tdate,'%Y%m%d'), MAX(memType)";
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59'));
$this->reset();
while ($row = $this->connection->fetch_row($result)) {
if ($this->date_id() != $row['date_id'] || $this->memType() != $row['memType']) {
if ($this->date_id() !== 0) {
$this->save();
}
$this->reset();
$this->date_id($row['date_id']);
$this->memType($row['memType']);
$this->total(0.0);
$this->quantity(0.0);
$this->transCount(0);
}
$this->total($this->total() + $row['total']);
$this->quantity($this->quantity() + $row['quantity']);
$this->transCount($this->transCount() + 1);
}
if ($this->date_id() !== '') {
$this->save();
}
}
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);
$q = $dbc->prepare_statement("select emp_no,sum(-total),count(DISTINCT trans_num),\n year(tdate),month(tdate),day(tdate)\n from {$dlog} as d where\n tdate BETWEEN ? AND ?\n AND trans_type='T'\n GROUP BY year(tdate),month(tdate),day(tdate),emp_no\n ORDER BY sum(-total) DESC");
$r = $dbc->exec_statement($q, array($date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$data = array();
while ($row = $dbc->fetch_row($r)) {
$record = array($row['emp_no'], sprintf('%d/%d/%d', $row[4], $row[5], $row[3]), sprintf('%.2f', $row[1]), $row[2]);
$data[] = $record;
}
return $data;
}
protected function get_id_view()
{
$URL = $this->config->get('URL');
$table = DTransactionsModel::selectDlog($this->__models['start'], $this->__models['end']);
$monthYear = date('Ym', strtotime($this->__models['start']));
$dbc = $this->connection;
$dbc->selectDB($this->config->get('TRANS_DB'));
$query = "SELECT month(tdate),day(tdate),year(tdate),trans_num,\n sum(case when trans_type='T' then -total else 0 end) as tenderTotal\n FROM {$table} as t\n WHERE card_no=?\n AND tdate BETWEEN ? AND ?\n GROUP BY year(tdate),month(tdate),day(tdate),trans_num\n ORDER BY year(tdate) DESC, month(tdate) DESC,\n day(tdate) DESC";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, array($this->id, $this->__models['start'] . ' 00:00:00', $this->__models['end'] . ' 23:59:59'));
ob_start();
echo "<form action=\"MemPurchasesPage.php\" id=myform method=get>";
echo "<input type=hidden name=id value=\"" . $this->id . "\" />";
$tstamp = time();
echo '<div class="form-group">';
echo "<select class=\"form-control\" name=my onchange=\"\$('#myform').submit();\">";
$count = 0;
while (true) {
$val = date("Ym", $tstamp);
printf("<option value=\"%d\" %s>%s %d</option>", $val, $val == $monthYear ? "selected" : "", date("F", $tstamp), date("Y", $tstamp));
$tstamp = mktime(0, 0, 0, date("n", $tstamp) - 1, 1, date("Y", $tstamp));
// cuts off at 5 years
if ($count++ > 60) {
break;
}
}
echo "</select>";
echo '</div>';
$visits = 0;
$spending = 0.0;
echo "<table class=\"table table-bordered\">";
while ($row = $dbc->fetch_row($result)) {
echo "<tr>";
printf("<td>%d/%d/%d</td>", $row[0], $row[1], $row[2]);
printf("<td><a href=\"{$URL}admin/LookupReceipt/RenderReceiptPage.php?receipt=%s&month=%d&day=%d&year=%d\">%s</a></td>", $row[3], $row[0], $row[1], $row[2], $row[3]);
printf("<td>\$%.2f</td>", $row[4]);
echo "</tr>";
$spending += $row[4];
$visits += 1;
}
echo "</table>";
echo '<p>';
printf("<b>Visits</b>: %d<br /><b>Spending</b>: \$%.2f\n <br /><b>Avg</b>: \$%.2f", $visits, $spending, $visits > 0 ? $spending / $visits : 0);
echo '</p>';
return ob_get_clean();
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$code = FormLib::get('tendercode');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = $dbc->prepare_statement("select tdate,trans_num,-total as total,emp_no, register_no\n FROM {$dlog} as t \n where t.trans_subtype = ? AND\n trans_type='T' AND\n tdate BETWEEN ? AND ?\n AND total <> 0\n order by tdate");
$result = $dbc->exec_statement($query, array($code, $date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$data = array();
while ($row = $dbc->fetch_array($result)) {
$record = array(date('Y-m-d', strtotime($row['tdate'])), $row['trans_num'], $row['emp_no'], $row['register_no'], $row['total']);
$data[] = $record;
}
return $data;
}
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($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 refresh_data($trans_db, $month, $year, $day = False)
{
$start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year));
$end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year));
$end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year));
if ($day !== False) {
$start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year));
$end_id = $start_id;
$end_date = $start_date;
}
$target_table = DTransactionsModel::selectDlog($start_date, $end_date);
/* clear old entries */
$sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?';
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_id, $end_id));
/* reload table from transarction archives */
$sql = "INSERT INTO " . $this->name . "\n SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n memType,\n CONVERT(SUM(total),DECIMAL(10,2)) as total,\n COUNT(DISTINCT trans_num) as transCount\n FROM {$target_table} WHERE\n tdate BETWEEN ? AND ? AND\n trans_type IN ('S') AND total <> 0\n AND upc='DISCOUNT' AND card_no <> 0\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), memType";
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59'));
}
public function refresh_data($trans_db, $month, $year, $day = False)
{
$start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year));
$end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year));
$end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year));
if ($day !== False) {
$start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year));
$end_id = $start_id;
$end_date = $start_date;
}
$target_table = DTransactionsModel::selectDlog($start_date, $end_date);
/* clear old entries */
$sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?';
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_id, $end_id));
/* reload table from transarction archives */
$sql = "INSERT INTO " . $this->name . "\n SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n upc,department,\n CONVERT(SUM(total),DECIMAL(10,2)) as total,\n CONVERT(SUM(CASE WHEN trans_status='M' THEN itemQtty \n WHEN unitPrice=0.01 THEN 1 ELSE quantity END),DECIMAL(10,2)) as quantity\n FROM {$target_table} WHERE\n tdate BETWEEN ? AND ? AND\n trans_type IN ('I','D') AND upc <> '0'\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), upc, department";
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59'));
}
public function fetch_report_data()
{
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$m = new MasterSuperDeptsModel($dbc);
$m->superID(0);
$dept_list = '?,';
$args = array(0);
foreach ($m->find() as $obj) {
$dept_list .= '?,';
$args[] = $obj->dept_ID();
}
$dept_list = substr($dept_list, 0, strlen($dept_list) - 1);
$prep = $dbc->prepare('
SELECT SUM(d.total) AS ttl
FROM ' . $dlog . ' AS d
WHERE d.department NOT IN (' . $dept_list . ')
AND d.trans_type IN (\'I\',\'D\')
AND d.tdate BETWEEN ? AND ?');
$start = strtotime($date1);
$end = strtotime($date2);
$data = array();
$i = 0;
while ($start <= $end) {
$d1 = date('Y-m-d 00:00:00', $start);
$d2 = date('Y-m-d 23:59:59', mktime(0, 0, 0, date('n', $start), date('j', $start) + 6, date('Y', $start)));
$record = array(date('Y-m-d', strtotime($d1)) . ' to ' . date('Y-m-d', strtotime($d2)));
$week_args = array_merge($args, array($d1, $d2));
$result = $dbc->execute($prep, $week_args);
$row = $dbc->fetch_row($result);
$record[] = sprintf('%.2f', $row['ttl']);
$data[] = $record;
$start = mktime(0, 0, 0, date('n', $start), date('j', $start) + 7, date('Y', $start));
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date = $this->form->date;
$dlog = DTransactionsModel::selectDlog($date);
$hour = $dbc->hour('tdate');
$q = $dbc->prepare_statement("select {$hour} as hour,\n count(distinct trans_num)\n from {$dlog} where\n tdate BETWEEN ? AND ?\n group by {$hour}\n order by {$hour}");
$r = $dbc->exec_statement($q, array($date . ' 00:00:00', $date . ' 23:59:59'));
$data = array();
while ($row = $dbc->fetch_array($r)) {
$hour = $row[0];
if ($hour > 12) {
$hour -= 12;
}
$record = array();
$record[] = $hour . ($row[0] < 12 ? ':00 am' : ':00 pm');
$record[] = $row[1];
$data[] = $record;
}
return $data;
}
public function refresh_data($trans_db, $month, $year, $day = False)
{
global $FANNIE_OP_DB;
$start_id = date('Ymd', mktime(0, 0, 0, $month, 1, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, 1, $year));
$end_id = date('Ymt', mktime(0, 0, 0, $month, 1, $year));
$end_date = date('Y-m-t', mktime(0, 0, 0, $month, 1, $year));
if ($day !== False) {
$start_id = date('Ymd', mktime(0, 0, 0, $month, $day, $year));
$start_date = date('Y-m-d', mktime(0, 0, 0, $month, $day, $year));
$end_id = $start_id;
$end_date = $start_date;
}
$target_table = DTransactionsModel::selectDlog($start_date, $end_date);
/* clear old entries */
$sql = 'DELETE FROM ' . $this->name . ' WHERE date_id BETWEEN ? AND ?';
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_id, $end_id));
// 5Jul2013 - percentDiscount not currently exposed via dlog
$sql = "INSERT INTO " . $this->name . " \n SELECT DATE_FORMAT(tdate, '%Y%m%d') as date_id,\n trans_num,\n register_no,\n emp_no,\n SUM(CASE WHEN trans_type='T' THEN total ELSE 0 END) as tenderTotal,\n SUM(CASE WHEN upc='TAX' THEN total ELSE 0 END) as taxTotal,\n SUM(CASE WHEN upc='DISCOUNT' THEN total ELSE 0 END) as discountTotal,\n 0 as percentDiscount,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID <> 0 THEN total else 0 END) as retailTotal,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID <> 0 AND trans_status='M' THEN itemQtty \n WHEN trans_type IN ('I','D') AND m.superID <> 0 AND unitPrice=0.01 THEN 1 \n WHEN trans_type IN ('I','D') AND m.superID <> 0 AND trans_status<>'M'\n AND unitPrice<>0.01 THEN quantity ELSE 0 END) as retailQty,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID = 0 THEN total else 0 END) as retailTotal,\n SUM(CASE WHEN trans_type IN ('I','D') AND m.superID = 0 AND trans_status='M' THEN itemQtty \n WHEN trans_type IN ('I','D') AND m.superID = 0 AND unitPrice=0.01 THEN 1 \n WHEN trans_type IN ('I','D') AND m.superID = 0 AND trans_status<>'M'\n AND unitPrice<>0.01 THEN quantity ELSE 0 END) as retailQty,\n SUM(CASE WHEN trans_type in ('I','D') THEN 1 ELSE 0 END) as ringCount,\n MIN(tdate) as start_time,\n MAX(tdate) as end_time, " . $this->connection->seconddiff('MIN(tdate)', 'MAX(tdate)') . " as duration,\n MAX(card_no) as card_no,\n MAX(memType) as memType\n FROM {$target_table} as t LEFT JOIN " . $FANNIE_OP_DB . $this->connection->sep() . "MasterSuperDepts as m\n ON t.department=m.dept_ID\n WHERE tdate BETWEEN ? AND ? AND upc <> 'RRR'\n GROUP BY DATE_FORMAT(tdate,'%Y%m%d'), trans_num";
$prep = $this->connection->prepare_statement($sql);
$result = $this->connection->exec_statement($prep, array($start_date . ' 00:00:00', $end_date . ' 23:59:59'));
}
function mainDisplay($date, $checkForSave = True)
{
global $sql;
$ret = "";
$FROM_SAVED = False;
if ($checkForSave) {
$checkQ = $sql->prepare("select * from dailyJournal where datediff(dd,tdate,?) = 0");
$checkR = $sql->execute($checkQ, array($date));
if ($sql->num_rows($checkR) > 0) {
$FROM_SAVED = True;
}
}
$ret .= "<a href=\"\" onclick=\"save(); return false;\">Save</a> | ";
$ret .= "<a href=\"\" onclick=\"reInit(); return false;\">Reload from POS</a> | ";
$ret .= "<a href=\"\" onclick=\"csv(); return false;\">Export to CSV</a><br />";
$ret .= "<table id=thetable cellpadding=3 cellspacing=0 border=1>";
$ret .= "<tr><td> </td><th>Input</th><th colspan=3>Journal Entries</th></tr>";
$ret .= "<tr><td> </td><td> </td>";
$ret .= "<td style=\"width:7em; text-align: center;\"><i>Debit</i></td>";
$ret .= "<td style=\"width:7em; text-align: center;\"><i>Credit</i></td>";
$ret .= "<td style=\"width:7em; text-align: center;\"><i>Account</i></td></tr>";
$dlog = DTransactionsModel::selectDlog($date);
$tenderQ = "select t.tenderName,-sum(d.total) as total \n from {$dlog} as d, Tenders as t\n where datediff(dd,?,d.tDate) = 0\n and d.trans_status <> 'X'\n and d.trans_subtype = t.tenderCode\n group by t.tenderName";
if ($FROM_SAVED) {
$tenderQ = "select sub_type,value from dailyJournal\n where datediff(dd,tdate,?) = 0 and\n type = 'T'";
}
$tenderP = $sql->prepare($tenderQ);
$tenderR = $sql->execute($tenderP, array($date));
$cash = 0;
$check = 0;
$MAD = 0;
$RRR = 0;
$coupons = 0;
$GC = 0;
$TC = 0;
$storecharge = 0;
$EBT = 0;
$MC = 0;
$Visa = 0;
$Disc1 = 0;
$Disc2 = 0;
$instoreCoup = 0;
while ($w = $sql->fetch_row($tenderR)) {
if ($w[0] == "Cash") {
$cash = $w[1];
} elseif ($w[0] == "Check") {
$check = $w[1];
} elseif ($w[0] == "MAD Coupon") {
$MAD = $w[1];
} elseif ($w[0] == "RRR Coupon") {
$RRR = $w[1];
} elseif ($w[0] == "Coupons") {
$coupons = $w[1];
} elseif ($w[0] == "Gift Card") {
$GC = $w[1];
} elseif ($w[0] == "GIFT CERT") {
$TC = $w[1];
} elseif ($w[0] == "InStore Charges") {
$storecharge = $w[1];
} elseif ($w[0] == "EBT") {
$EBT = $w[1];
} elseif ($w[0] == "MC") {
$MC = $w[1];
} elseif ($w[0] == "Visa") {
$Visa = $w[1];
} elseif ($w[0] == "Discover1") {
$Disc1 = $w[1];
} elseif ($w[0] == "Discover2") {
$Disc2 = $w[1];
} elseif ($w[0] == "InStoreCoupon") {
$instoreCoup = $w[1];
}
}
$ret .= "<tr><td>Deposit</td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Cash</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCash type=text value=\"{$cash}\" size=8 /></td>";
$ret .= "<td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Check</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCheck type=text value=\"{$check}\" size=8 /></td>";
$ret .= "<td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>TOTAL Deposit</td><td id=depositTotal> </td>";
$ret .= "<td name=jDebit id=jDepositTotal align=right> </td><td> </td><td align=right>10120</td></tr>";
$ret .= "<tr><td>Credit Cards/EBT</td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>EBT/Debit Approved</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputEBT type=text size=8 value=\"{$EBT}\" /></td>";
$ret .= "<td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>MasterCard</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCMC type=text size=8 value=\"{$MC}\" /></td>";
$ret .= "<td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>VISA</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCVisa type=text size=8 value=\"{$Visa}\" /></td>";
$ret .= "<td name=jDebit id=jCCMain align=right> </td><td> </td><td align=right> </td></tr>";
$style = "";
if ($Disc1 == 0) {
$style = "display:none;";
}
$ret .= "<tr style=\"{$style}\"><td>Discover Discount</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCDisc1 type=text size=8 value=\"{$Disc1}\" /></td>";
$ret .= "<td name=jDebit id=jCCDisc1 align=right> </td><td> </td><td align=right> </td></tr>";
$ret .= "<tr><td>Discover Outlet Total</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCCDisc2 type=text size=8 value=\"{$Disc2}\" /></td>";
$ret .= "<td name=jDebit id=jCCDisc2 align=right> </td><td> </td><td align=right> </td></tr>";
$ret .= "<tr><td>Total FAPS</td><td id=totalFAPs> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Total All Credit Cards/EBT</td><td id=totalCCEBT> </td><td name=jDebit id=jTotalCCEBT align=right> </td>";
$ret .= "<td> </td><td align=right>10120</td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>RRR Coupon</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputRRR type=text value=\"{$RRR}\" size=8 /></td>";
$ret .= "<td name=jDebit id=jRRR align=right> </td><td> </td><td align=right>63380</td></tr>";
$ret .= "<tr><td>Coupons</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputCoupons type=text value=\"{$coupons}\" size=8 /></td>";
$ret .= "<td name=jDebit id=jCoupons align=right> </td><td> </td><td align=right>10740</td></tr>";
$ret .= "<tr><td>Gift Card as Tender</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputGC type=text value=\"{$GC}\" size=8 /></td>";
$ret .= "<td name=jDebit id=jGC align=right> </td><td> </td><td align=right>21205</td></tr>";
$ret .= "<tr><td>Gift Cert as Tender</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputTC type=text value=\"{$TC}\" size=8 /></td>";
$ret .= "<td name=jDebit id=jTC align=right> </td><td> </td><td align=right>21200</td></tr>";
$ret .= "<tr><td>InStore Charges</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputStoreCharge type=text value=\"{$storecharge}\" size=8 /></td>";
$ret .= "<td name=jDebit id=jStoreCharge align=right> </td><td> </td><td align=right>10710</td></tr>";
$ret .= "<tr><td>InStore Coupons</td><td>";
$ret .= "<input onchange=\"resumTenders();resumTotals();\" name=input id=inputInStoreCoup type=text value=\"{$instoreCoup}\" size=8 /></td>";
$ret .= "<td name=jDebit id=jInStoreCoup align=right> </td><td> </td><td align=right>67710</td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Tenders Total</td><td id=tenderTotal> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>SALES</td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>pCode</td><td> </td><td> </td><td> </td><td> </td></tr>";
$salesQ = "select d.pCode,sum(l.total) as total from\n {$dlog} as l join departments as d on l.department = d.dept_no\n where datediff(dd,?,tDate) = 0\n and l.department < 600 and l.department <> 0\n and l.trans_type <> 'T'\n group by d.pCode\n order by d.pCode";
if ($FROM_SAVED) {
$salesQ = "select sub_type,value from dailyJournal where\n datediff(dd,tdate,?) = 0 and type='P' order by sub_type";
}
$pCodes = array(41201, 41205, 41300, 41305, 41310, 41315, 41400, 41405, 41407, 41410, 41415, 41420, 41425, 41430, 41435, 41440, 41500, 41505, 41510, 41515, 41520, 41525, 41530, 41600, 41605, 41610, 41640, 41645, 41700, 41705);
$i = 0;
$slaesP = $sql->prepare($salesQ);
$salesR = $sql->execute($salesP, array($date));
while ($w = $sql->fetch_row($salesR)) {
if ($i >= count($pCodes)) {
break;
}
while ($w[0] > $pCodes[$i]) {
$ret .= "<tr><td>{$pCodes[$i]}</td>";
$ret .= "<td><input onchange=\"resumSales();resumTotals();\" name=input id=inputPcode{$pCodes[$i]} type=text size=8 value=0 /></td>";
$ret .= "<td name=jDebit id=jDebit{$pCodes[$i]} align=right> </td>";
$ret .= "<td name=jCredit id=jCredit{$pCodes[$i]} align=right> </td>";
$ret .= "<td align=right>{$pCodes[$i]}</td></tr>";
$i++;
}
if ($w[0] == $pCodes[$i]) {
$ret .= "<tr><td>{$pCodes[$i]}</td>";
$ret .= "<td><input onchange=\"resumSales();resumTotals();\" name=input id=inputPcode{$pCodes[$i]} type=text size=8 value=\"{$w['1']}\" /></td>";
$ret .= "<td name=jDebit id=jDebit{$pCodes[$i]} align=right> </td>";
$ret .= "<td name=jCredit id=jCredit{$pCodes[$i]} align=right> </td>";
$ret .= "<td align=right>{$pCodes[$i]}</td></tr>";
$i++;
}
}
while ($i < count($pCodes)) {
$ret .= "<tr><td>{$pCodes[$i]}</td>";
$ret .= "<td><input onchange=\"resumSales();resumTotals();\" name=input id=inputPcode{$pCodes[$i]} type=text size=8 value=0 /></td>";
$ret .= "<td name=jDebit id=jDebit{$pCodes[$i]} align=right> </td>";
$ret .= "<td name=jCredit id=jCredit{$pCodes[$i]} align=right> </td>";
$ret .= "<td align=right>{$pCodes[$i]}</td></tr>";
$i++;
}
$ret .= "<tr><td>column TOTAL</td><td id=totalPcode> </td><td> </td><td> </td><td> </td></tr>";
$totalQ = $sql->prepare("select sum(l.total) as totalSales from {$dlog} as l\n where datediff(dd,?,tDate) = 0\n and l.department < 600 and l.department <> 0\n and l.trans_type <> 'T'");
$totalR = $sql->execute($totalQ, array($date));
$totalW = $sql->fetch_row($totalR);
$ret .= "<tr><td>Total Sales POS report</td><td id=totalPOS>{$totalW['0']}</td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Difference</td><td id=salesDiff> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$otherQ = "select d.department,sum(total) as total from {$dlog} as d\n where datediff(dd,?,tDate) = 0\n and d.department > 300 and d.department <> 0 and d.register_no <> 20\n group by d.department order by d.department";
if ($FROM_SAVED) {
$otherQ = "select sub_type,value from dailyJournal where\n datediff(dd,tdate,?) = 0 and type = 'O'";
}
$otherP = $sql->prepare($otherQ);
$otherR = $sql->execute($otherP, array($date));
$gcSales = 0;
$tcSales = 0;
$miscPO = 0;
$classA = 0;
$classB = 0;
$ar = 0;
$ITCorrections = 0;
$misc1 = 0;
$misc2 = 0;
$supplies = 0;
$class = 0;
$foundMoney = 0;
$totes = 0;
while ($w = $sql->fetch_row($otherR)) {
if ($w[0] == 902) {
$gcSales = $w[1];
} elseif ($w[0] == 900) {
$tcSales = $w[1];
} elseif ($w[0] == 604) {
$miscPO = $w[1];
} elseif ($w[0] == 992) {
$classA = $w[1];
} elseif ($w[0] == 991) {
$classB = $w[1];
} elseif ($w[0] == 990) {
$ar = $w[1];
} elseif ($w[0] == 800) {
$ITCorrections = $w[1];
} elseif ($w[0] == 801) {
$misc1 = $w[1];
} elseif ($w[0] == 802) {
$misc2 = $w[1];
} elseif ($w[0] == 600) {
$supplies = $w[1];
} elseif ($w[0] == 708) {
$class = $w[1];
} elseif ($w[0] == 700) {
$totes = $w[1];
} elseif ($w[0] == "FOUND") {
$foundMoney = $w[1];
}
}
$ret .= "<tr><td>Gift Card Sales</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputGCSales type=text size=8 value=\"{$gcSales}\" /></td>";
$ret .= "<td name=jDebit id=jDebitGCSales align=right> </td>";
$ret .= "<td name=jCredit id=jCreditGCSales align=right> </td><td align=right>21205</td></tr>";
$ret .= "<tr><td>Gift Certificate Sales</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputTCSales type=text size=8 value=\"{$tcSales}\" /></td>";
$ret .= "<td name=jDebit id=jDebitTCSales align=right> </td>";
$ret .= "<td name=jCredit id=jCreditTCSales align=right> </td><td align=right>21200</td></tr>";
$ret .= "<tr><td>Misc PO</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMiscPO type=text size=8 value=\"{$miscPO}\" /></td>";
$ret .= "<td name=jDebit id=jDebitMiscPO align=right> </td>";
$ret .= "<td name=jCredit id=jCreditMiscPO align=right> </td><td> </td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>EQUITY</td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Class A Equity</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputClassA type=text size=8 value=\"{$classA}\" /></td>";
$ret .= "<td name=jDebit id=jDebitClassA align=right> </td>";
$ret .= "<td name=jCredit id=jCreditClassA align=right> </td><td align=right>31100</td></tr>";
$ret .= "<tr><td>Class B Equity</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputClassB type=text size=8 value=\"{$classB}\" /></td>";
$ret .= "<td name=jDebit id=jDebitClassB align=right> </td>";
$ret .= "<td name=jCredit id=jCreditClassB align=right> </td><td align=right>31110</td></tr>";
$ret .= "<tr><td>TOTAL Equity</td><td id=totalEquity> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>AR Payments</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputAR type=text size=8 value=\"{$ar}\" /></td>";
$ret .= "<td name=jDebit id=jDebitAR align=right> </td>";
$ret .= "<td name=jCredit id=jCreditAR align=right> </td><td align=right>10710</td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Discounts</td><td> </td><td> </td><td> </td><td> </td></tr>";
$discountQ = "select m.memDesc, sum(d.total)*-1 as discount from\n {$dlog} as d inner join custdata as c on c.cardno = d.card_no\n inner join memtypeID as m on c.memType = m.memTypeID\n where datediff(dd,?,d.tdate) = 0\n and d.upc = 'DISCOUNT' and c.personnum = 1\n group by m.memDesc,d.upc";
if ($FROM_SAVED) {
$discountQ = "select sub_type,value from dailyJournal where\n datediff(dd,tdate,?) = 0 and type = 'D'";
}
$discountP = $sql->prepare($discountQ);
$discountR = $sql->execute($discountP, array($date));
$discMem = 0;
$discStaffMem = 0;
$discStaffNonMem = 0;
while ($w = $sql->fetch_row($discountR)) {
if ($w[0] == "Member") {
$discMem = $w[1];
} elseif ($w[0] == "Staff Member") {
$discStaffMem = $w[1];
} elseif ($w[0] == "Staff NonMem") {
$discStaffNonMem = $w[1];
}
}
$ret .= "<tr><td>Member</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMemDisc type=text size=8 value=\"{$discMem}\" /></td>";
$ret .= "<td name=jDebit id=jDebitDiscMem align=right> </td>";
$ret .= "<td name=jCredit id=jCreditDiscMem align=right> </td><td align=right>66600</td></tr>";
$ret .= "<tr><td>Staff Member</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputStaffMemDisc type=text size=8 value=\"{$discStaffMem}\" /></td>";
$ret .= "<td name=jDebit id=jDebitDiscStaffMem align=right> </td>";
$ret .= "<td name=jCredit id=jCreditDiscStaffMem align=right> </td><td align=right>61170</td></tr>";
$ret .= "<tr><td>Staff NonMem</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputStaffNonMemDisc type=text size=8 value=\"{$discStaffNonMem}\" /></td>";
$ret .= "<td name=jDebit id=jDebitDiscStaffNonMem align=right> </td>";
$ret .= "<td name=jCredit id=jCreditDiscStaffNonMem align=right> </td><td align=right>61170</td></tr>";
$ret .= "<tr><td>MAD Coupon</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMAD type=text value=\"{$MAD}\" size=8 /></td>";
$ret .= "<td name=jDebit id=jDebitMAD align=right> </td>";
$ret .= "<td name=jCredit id=jCreditMAD align=right> </td><td align=right>66600</td></tr>";
$ret .= "<tr><td>TOTAL Discounts</td><td id=totalDisc> </td><td> </td><td> </td><td> </td></tr>";
$taxQ = "select sum(total) as tax_collected from {$dlog} as d\n where datediff(dd,?,tdate) = 0\n and d.upc = 'TAX' group by d.upc";
if ($FROM_SAVED) {
$taxQ = "select value from dailyJournal where type = 'X'\n and datediff(dd,tdate,?) = 0";
}
$taxP = $sql->prepare($taxQ);
$taxR = $sql->execute($taxP, array($date));
$taxW = $sql->fetch_row($taxR);
$ret .= "<tr><td>Sales Tax Collected</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputTax type=text size=8 value=\"{$taxW['0']}\" /></td>";
$ret .= "<td name=jDebit id=jDebitTax align=right> </td>";
$ret .= "<td name=jCredit id=jCreditTax align=right> </td><td align=right>21180</td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>Other Income</td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td>IT Corrections</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputITCorrections type=text size=8 value=\"{$ITCorrections}\" /></td>";
$ret .= "<td name=jDebit id=jDebitITCorrections align=right> </td>";
$ret .= "<td name=jCredit id=jCreditITCorrections align=right> </td><td> </td></tr>";
$ret .= "<tr><td>Misc. #1</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMisc1 type=text size=8 value=\"{$misc1}\" /></td>";
$ret .= "<td name=jDebit id=jDebitMisc1 align=right> </td>";
$ret .= "<td name=jCredit id=jCreditMisc1 align=right> </td><td align=right>42231</td></tr>";
$ret .= "<tr><td>Misc. #2</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputMisc2 type=text size=8 value=\"{$misc2}\" /></td>";
$ret .= "<td name=jDebit id=jDebitMisc2 align=right> </td>";
$ret .= "<td name=jCredit id=jCreditMisc2 align=right> </td><td align=right>42232</td></tr>";
$ret .= "<tr><td>Supplies (Stamps sold)</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputSupplies type=text size=8 value=\"{$supplies}\" /></td>";
$ret .= "<td name=jDebit id=jDebitSupplies align=right> </td>";
$ret .= "<td name=jCredit id=jCreditSupplies align=right> </td><td align=right>64410</td></tr>";
$ret .= "<tr><td>Class (public not staff)</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputClass type=text size=8 value=\"{$class}\" /></td>";
$ret .= "<td name=jDebit id=jDebitClass align=right> </td>";
$ret .= "<td name=jCredit id=jCreditClass align=right> </td><td align=right>42225</td></tr>";
$ret .= "<tr><td>Found Money</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputFound type=text size=8 value=\"{$foundMoney}\" /></td>";
$ret .= "<td name=jDebit id=jDebitFound align=right> </td>";
$ret .= "<td name=jCredit id=jCreditFound align=right> </td><td align=right>63350</td></tr>";
$ret .= "<tr><td>Totes</td><td>";
$ret .= "<input onchange=\"resumOtherIncome();resumTotals();\" name=input id=inputTotes type=text size=8 value=\"{$totes}\" /></td>";
$ret .= "<td name=jDebit id=jDebitTotes align=right> </td>";
$ret .= "<td name=jCredit id=jCreditTotes align=right> </td><td align=right>63320</td></tr>";
$miscCount = 0;
if ($FROM_SAVED) {
$miscQ = $sql->prepare("select sub_type,value from dailyJournal where\n datediff(dd,?,tdate) = 0 and type='M'");
$miscR = $sql->execute($miscQ, array($date));
while ($row = $sql->fetch_row($miscR)) {
$ret .= "<tr><td>MiscReceipt</td>";
$ret .= "<td><input onchange=\"resumMisc();resumTotals();\" type=text size=8 value={$row['1']} id=inputMisc{$miscCount} /></td>";
$ret .= "<td name=jDebit id=jDebitMisc{$miscCount} align=right> </td>";
$ret .= "<td name=jCredit id=jCreditMisc{$miscCount} align=right> </td>";
$ret .= "<td align=right><input type=text size=8 value=\"{$row['0']}\" id=accountMisc{$miscCount} /></td>";
$ret .= "</tr>";
$miscCount++;
}
}
$ret .= "<tr><td><a href=\"\" onclick=\"addMisc(); return false;\">Add MiscReceipt</a></td>";
$ret .= "<td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<input type=hidden id=miscCount value={$miscCount} />";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><td><i>SHEET SUBTOTAL</i></td><td> </td>";
$ret .= "<td id=sheetSubDebit align=right> </td><td id=sheetSubCredit align=right> </td><td> </td></tr>";
$ret .= "<tr><td>Over/Short</td><td id=overshort> </td>";
$ret .= "<td id=debitOvershort align=right> </td><td id=creditOvershort align=right> </td><td align=right>63350</td></tr>";
$ret .= "<tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr>";
$ret .= "<tr><th>SHEET TOTAL</th><td> </td>";
$ret .= "<td id=sheetDebit align=right> </td><td id=sheetCredit align=right> </td><td> </td></tr>";
$ret .= "<tr><td><i>SHEET IMBALANCE</i></td><td> </td><td> </td><td id=sheetDiff align=right> </td><td> </td></tr>";
$ret .= "</table>";
return $ret;
}
protected function updateSalesCache($week, $num_cached, $dateInfo)
{
$class_lib = $this->class_lib;
$dbc = $class_lib::getDB();
$sales = $class_lib::getCache($dbc);
$sales->obfWeekID($week->obfWeekID());
/**
Lookup total sales for each category
in a given date range
*/
$salesQ = 'SELECT
m.obfCategoryID as id,
m.superID,
SUM(t.total) AS sales
FROM __table__ AS t
INNER JOIN ' . $this->config->get('OP_DB') . $dbc->sep() . 'superdepts AS s
ON t.department=s.dept_ID
INNER JOIN ObfCategorySuperDeptMap AS m
ON s.superID=m.superID
LEFT JOIN ObfCategories AS c
ON m.obfCategoryID=c.obfCategoryID
WHERE c.hasSales=1
AND t.tdate BETWEEN ? AND ?
AND t.trans_type IN (\'I\', \'D\')
GROUP BY m.obfCategoryID, m.superID';
/**
Lookup number of transactions
in a given date range
*/
$transQ = 'SELECT
YEAR(t.tdate) AS year,
MONTH(t.tdate) AS month,
DAY(t.tdate) AS day,
t.trans_num
FROM __table__ AS t
INNER JOIN ' . $this->config->get('OP_DB') . $dbc->sep() . 'superdepts AS s
ON t.department=s.dept_ID
INNER JOIN ObfCategorySuperDeptMap AS m
ON s.superID=m.superID
WHERE
t.tdate BETWEEN ? AND ?
AND t.trans_type IN (\'I\', \'D\')
AND t.upc <> \'RRR\'
GROUP BY
YEAR(t.tdate),
MONTH(t.tdate),
DAY(t.tdate),
t.trans_num';
/**
Lookup tables for current week and
year-over-year comparison
*/
$dlog1 = DTransactionsModel::selectDlog(date('Y-m-d', $dateInfo['start_ts']), date('Y-m-d', $dateInfo['end_ts']));
$dlog2 = DTransactionsModel::selectDlog(date('Y-m-d', $dateInfo['start_ly']), date('Y-m-d', $dateInfo['end_ly']));
$args = array(date('Y-m-d 00:00:00', $dateInfo['start_ts']), date('Y-m-d 23:59:59', $dateInfo['end_ts']));
$future = $dateInfo['end_ts'] >= strtotime(date('Y-m-d')) ? true : false;
/**
Lookup number of transactions for the current
week and save that information if the week
is complete
*/
$trans1Q = str_replace('__table__', $dlog1, $transQ);
$transP = $dbc->prepare($trans1Q);
$transR = $dbc->execute($transP, $args);
if (!$future && $transR) {
$sales->transactions($dbc->num_rows($transR));
} else {
$sales->transactions(0);
}
/**
Lookup sales for the current week. Actual sales
is zeroed out until the week is complete, but
the records are saved as placeholders for later
*/
$oneQ = str_replace('__table__', $dlog1, $salesQ);
$oneP = $dbc->prepare($oneQ);
$oneR = $dbc->execute($oneP, $args);
while ($row = $dbc->fetch_row($oneR)) {
$sales->obfCategoryID($row['id']);
$sales->superID($row['superID']);
$sales->actualSales($row['sales']);
if ($future) {
$sales->actualSales(0);
}
$labor = $class_lib::getLabor($dbc);
$labor->obfWeekID($week->obfWeekID());
$labor->obfCategoryID($row['id']);
foreach ($labor->find() as $l) {
$sales->growthTarget($l->growthTarget());
}
$sales->save();
}
if (count($num_cached) == 0) {
/**
Now lookup year-over-year info
Since it examines a whole month rather than a single
week, we'll take the average and then extend
that out to seven days
*/
$sales->reset();
$sales->obfWeekID($week->obfWeekID());
$args = array(date('Y-m-d 00:00:00', $dateInfo['start_ly']), date('Y-m-d 23:59:59', $dateInfo['end_ly']));
$num_days = (double) date('t', $dateInfo['start_ly']);
/**
Transactions last year, pro-rated
*/
$trans2Q = str_replace('__table__', $dlog2, $transQ);
$transP = $dbc->prepare($trans2Q);
$transR = $dbc->execute($transP, $args);
if ($transR) {
$month_trans = $dbc->num_rows($transR);
$avg_trans = $month_trans / $num_days * 7;
$sales->lastYearTransactions($avg_trans);
} else {
$sales->lastYearTransactions(0);
}
/**
Sales last year, pro-rated
*/
$twoQ = str_replace('__table__', $dlog2, $salesQ);
$twoP = $dbc->prepare($twoQ);
$twoR = $dbc->execute($twoP, $args);
while ($row = $dbc->fetch_row($twoR)) {
$sales->obfCategoryID($row['id']);
$sales->superID($row['superID']);
$avg_sales = $row['sales'] / $num_days * 7;
$sales->lastYearSales($avg_sales);
if ($future) {
$sales->actualSales(0);
$labor = $class_lib::getLabor($dbc);
$labor->obfWeekID($week->obfWeekID());
$labor->obfCategoryID($row['id']);
foreach ($labor->find() as $l) {
$sales->growthTarget($l->growthTarget());
}
}
$sales->save();
}
}
}