public function testTransactionArchiving()
{
$config = FannieConfig::factory();
$task = new TransArchiveTask();
$GLOBALS['FANNIE_ARCHIVE_METHOD'] = 'tables';
/**
Put a record in dtransactions that should trigger
a new monthly table & view
*/
$dtrans = new DTransactionsModel(FannieDB::get($config->get('TRANS_DB')));
$dtrans->datetime('1901-01-01 00:00:00');
$dtrans->save();
$task->run();
/**
Verify the task created new monthly table & view
*/
$archive_db = FannieDB::get($config->get('ARCHIVE_DB'));
$archive_table_exists = $archive_db->tableExists('transArchive190101');
$archive_dlog_exists = $archive_db->tableExists('dlog190101');
$this->assertEquals(true, $archive_table_exists, 'Monthly archive table not created');
$this->assertEquals(true, $archive_dlog_exists, 'Monthly dlog view not created');
/**
Verify dtransactions was cleared
*/
$trans_db = FannieDB::get($config->get('TRANS_DB'));
$records = $trans_db->query('SELECT * FROM dtransactions');
$this->assertEquals(0, $trans_db->num_rows($records), 'dtransactions not cleared');
}
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;
}
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 __construct($con)
{
$this->columns['store_row_id']['increment'] = false;
$this->columns['store_row_id']['primary_key'] = false;
$this->columns['store_row_id']['index'] = false;
$this->columns['pos_row_id']['index'] = false;
parent::__construct($con);
}
/**
Override BasicModel::create to ensure
date settings are present
*/
public function create()
{
if ($this->name == '__needs_initialization') {
return false;
} else {
return parent::create();
}
}
/**
Override BasicModel::create to add an
initial partition when the table is first
created
*/
public function create()
{
$exists = $this->connection->tableExists($this->name);
$created = parent::create();
if ($created && !$exists) {
$this->initPartitions(date('Y'), date('n'));
}
return $created;
}
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 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;
}
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()
{
$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;
}
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 __construct($con)
{
unset($this->columns['datetime']);
$tdate = array('tdate' => array('type' => 'datetime', 'index' => True));
$trans_num = array('trans_num' => array('type' => 'VARCHAR(25)'));
$this->columns = $tdate + $this->columns + $trans_num;
$this->columns['store_row_id']['increment'] = false;
$this->columns['store_row_id']['primary_key'] = false;
$this->columns['pos_row_id']['index'] = false;
parent::__construct($con);
}
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;
}
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'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$manu = FormLib::get_form_value('manu', '');
$type = FormLib::get_form_value('type', '');
$groupby = FormLib::get_form_value('groupby', 'upc');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$type_condition = "p.brand LIKE ?";
$args = array('%' . $manu . '%');
if ($type == 'prefix') {
$type_condition = 't.upc LIKE ?';
}
$query = "";
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
switch ($groupby) {
case 'upc':
$query = "\n SELECT t.upc,\n p.brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n p.description,\n d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC";
break;
case 'date':
$query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . "\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)";
break;
case 'dept':
$query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC";
break;
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
if ($groupby == "date") {
$record[] = $row['month'] . '/' . $row['day'] . '/' . $row['year'];
$record[] = number_format($row['qty'], 2);
$record[] = number_format($row['ttl'], 2);
} else {
for ($i = 0; $i < $dbc->num_fields($result); $i++) {
if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') {
$row[$i] = sprintf('%.2f', $row[$i]);
}
$record[] .= $row[$i];
}
}
$ret[] = $record;
}
return $ret;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$FANNIE_TRANS_DB = $this->config->get('TRANS_DB');
$dtrans = $FANNIE_TRANS_DB . $dbc->sep() . 'transarchive';
$union = true;
$args = array();
try {
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$dtrans = DTransactionsModel::selectDTrans($date1, $date2);
$union = false;
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
} catch (Exception $ex) {
$date1 = '';
$date2 = '';
}
/**
I'm using {{placeholders}}
to build the basic query, then replacing those
pieces depending on date range options
*/
$query = "SELECT\n YEAR(datetime) AS year,\n MONTH(datetime) AS month,\n DAY(datetime) AS day,\n d.upc,\n d.description,\n d.department,\n e.dept_name,\n SUM(d.quantity) AS quantity,\n SUM(d.total) AS total,\n s.description AS shrinkReason,\n m.super_name,\n e.salesCode,\n d.charflag\n FROM {{table}} AS d\n LEFT JOIN departments AS e ON d.department=e.dept_no\n LEFT JOIN ShrinkReasons AS s ON d.numflag=s.shrinkReasonID\n LEFT JOIN MasterSuperDepts AS m ON d.department=m.dept_ID\n WHERE trans_status = 'Z'\n AND trans_type IN ('D', 'I')\n AND trans_subtype IN ('','0')\n AND emp_no <> 9999\n AND register_no <> 99\n AND upc <> '0'\n {{date_clause}}\n GROUP BY\n YEAR(datetime),\n MONTH(datetime),\n DAY(datetime),\n d.upc,\n d.description,\n d.department,\n e.dept_name,\n s.description";
$fullQuery = '';
if (!$union) {
// user selected date range
$fullQuery = str_replace('{{table}}', $dtrans, $query);
$fullQuery = str_replace('{{date_clause}}', 'AND datetime BETWEEN ? AND ?', $fullQuery);
} else {
// union of today (dtransaction)
// plus last quarter (transarchive)
$today_table = $FANNIE_TRANS_DB . $dbc->sep() . 'dtransactions';
$today_clause = ' AND ' . $dbc->datediff($dbc->now(), 'datetime') . ' = 0';
$query1 = str_replace('{{table}}', $today_table, $query);
$query1 = str_replace('{{date_clause}}', $today_clause, $query1);
$query2 = str_replace('{{table}}', $dtrans, $query);
$query2 = str_replace('{{date_clause}}', '', $query2);
$fullQuery = $query1 . ' UNION ALL ' . $query2;
}
$data = array();
$prep = $dbc->prepare($fullQuery);
$result = $dbc->execute($prep, $args);
while ($row = $dbc->fetch_row($result)) {
$record = array(date('Y-m-d', mktime(0, 0, 0, $row['month'], $row['day'], $row['year'])), $row['upc'], $row['description'], $row['department'], $row['dept_name'], $row['salesCode'], $row['super_name'], sprintf('%.2f', $row['quantity']), sprintf('%.2f', $row['total']), empty($row['shrinkReason']) ? 'n/a' : $row['shrinkReason'], $row['charflag'] == 'C' ? 'No' : 'Yes');
$data[] = $record;
}
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('TRANS_DB'));
$dtrans = new DTransactionsModel($dbc);
$columns = $dtrans->getColumns();
foreach ($columns as $name => $info) {
$this->report_headers[] = $name;
}
list($emp, $reg, $trans) = explode('-', FormLib::get('trans'), 3);
$date = FormLib::get('date');
$table = DTransactionsModel::selectDtrans($date);
$query = $dbc->prepare('
SELECT *
FROM ' . $table . '
WHERE datetime BETWEEN ? AND ?
AND emp_no = ?
AND register_no = ?
AND trans_no = ?
ORDER BY trans_id
');
$result = $dbc->execute($query, array($date . ' 00:00:00', $date . ' 23:59:59', $emp, $reg, $trans));
$data = array();
while ($w = $dbc->fetchRow($result)) {
$record = array();
foreach ($columns as $c => $info) {
if (isset($w[$c])) {
$record[] = $w[$c];
} else {
$record[] = '';
}
}
$data[] = $record;
}
return $data;
}
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;
}
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;
}
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 get_date1_date2_dept_view()
{
$start = FormLib::get('date1');
$end = FormLib::get('date2');
$dept_limit = FormLib::get('dept');
$method = FormLib::get('sql-method');
$dlog = false;
$dbc = FannieDB::get($this->config->get('OP_DB'));
$timing_point_1 = microtime(true);
switch ($method) {
case 'Large Temporary Table':
$where = array('connection' => $dbc, 'clauses' => array(array('sql' => ' trans_type IN (\'I\',\'D\') ', 'params' => array()), array('sql' => ' department BETWEEN 0 AND ? ', 'params' => array($dept_limit))));
$dlog = DTransactionsModel::selectDTrans($start, $end, $where);
break;
case 'Aggregate Temporary Table':
$where = new stdclass();
$where->sql = array("trans_type IN ('I', 'D')", "department BETWEEN 0 AND ?");
$where->params = array($dept_limit);
$groupby = array('department');
$dlog = DTransactionsModel::aggregateDtrans($dbc, $start, $end, $where, $groupby);
break;
case 'Single Query':
default:
$dlog = DTransactionsModel::selectDTrans($start, $end);
break;
}
$timing_point_2 = microtime(true);
$ret = '<p>Using archive table(s): <em>' . $dlog . '</em></p>';
$query = '
SELECT d.dept_name,
SUM(t.total) AS ttl
FROM ' . $dlog . ' AS t
LEFT JOIN departments AS d ON t.department=d.dept_no
WHERE t.datetime BETWEEN ? AND ?
AND trans_type IN (\'I\', \'D\')
AND t.department BETWEEN 0 AND ?
GROUP BY d.dept_name';
$prep = $dbc->prepare($query);
$args = array($start . ' 00:00:00', $end . ' 23:59:59', $dept_limit);
$result = $dbc->execute($prep, $args);
$timing_point_3 = microtime(true);
$ret .= '<p>Query used:<pre>' . $query . '</pre></p>';
$ret .= '<p>Query succeeded: ' . ($result ? 'Yes' : 'No') . '</p>';
$ret .= '<p>Elapsed time: ' . ($timing_point_3 - $timing_point_1) . '</p>';
return $ret;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$upc = $this->form->upc;
if (is_numeric($upc)) {
$upc = BarcodeLib::padUPC($upc);
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "SELECT \n MONTH(t.tdate),\n DAY(t.tdate),\n YEAR(t.tdate),\n t.upc,\n p.brand,\n p.description,\n " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS total\n FROM {$dlog} AS t \n " . DTrans::joinProducts('t', 'p', 'LEFT') . "\n WHERE t.upc = ? AND\n t.tdate BETWEEN ? AND ?\n GROUP BY \n YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate),\n t.upc,\n p.description\n ORDER BY year(t.tdate),month(t.tdate),day(t.tdate)";
$args = array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59');
if (strtolower($upc) == "rrr" || $upc == "0000000000052") {
if ($dlog == "dlog_90_view" || $dlog == "dlog_15") {
$dlog = "transarchive";
} else {
$dlog = "trans_archive.bigArchive";
}
$query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand,'RRR' AS description,\n sum(case when upc <> 'rrr' then quantity when volSpecial is null or volSpecial > 9999 then 0 else volSpecial end) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and trans_status <> 'X'\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)\n ORDER BY YEAR(datetime),MONTH(datetime),DAY(datetime)";
} else {
if (!is_numeric($upc)) {
$dlog = DTransactionsModel::selectDTrans($date1, $date2);
$query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand, description,\n sum(CASE WHEN quantity=0 THEN 1 ELSE quantity END) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and (trans_status <> 'X' || trans_type='L')\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)";
}
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
/**
Simple report
Issue a query, build array of results
*/
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
$record[] = $row[0] . "/" . $row[1] . "/" . $row[2];
$record[] = $row['upc'];
$record[] = $row['brand'] === null ? '' : $row['brand'];
$record[] = $row['description'] === null ? '' : $row['description'];
$record[] = sprintf('%.2f', $row['qty']);
$record[] = sprintf('%.2f', $row['total']);
$ret[] = $record;
}
return $ret;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$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;
}
