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'));
$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'));
$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 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;
}
public function run()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$yesterday = date('Y-m-d', strtotime('yesterday'));
$url = $this->config->get('URL');
$host = php_uname('n');
$dtrans = DTransactionsModel::selectDTrans($yesterday);
$findP = $dbc->prepare('
SELECT d.upc,
d.description,
d.department,
p.inUse,
count(*) AS occurences
FROM ' . $dtrans . ' AS d ' . DTrans::joinProducts('d') . '
WHERE d.trans_type=\'L\'
AND d.trans_subtype=\'OG\'
AND d.charflag=\'IU\'
AND d.emp_no <> 9999
AND d.register_no <> 99
AND d.datetime BETWEEN ? AND ?
GROUP BY d.upc,
d.description,
d.department,
p.inUse
HAVING SUM(d.quantity) <> 0
');
$findR = $dbc->execute($findP, array($yesterday . ' 00:00:00', $yesterday . ' 23:59:59'));
while ($w = $dbc->fetchRow($findR)) {
$msg = sprintf('%s (%s) was sold %d times while not inUse', $w['description'], $w['upc'], $w['occurences']);
$email = \COREPOS\Fannie\API\lib\AuditLib::getAddresses($w['department']);
if ($email) {
$subject = 'Not In Use Report';
$from = "From: automail\r\n";
$msg .= "\n";
$msg .= "http://{$host}/{$url}item/ItemEditorPage.php?searchupc={$w['upc']}\n";
mail($email, $subject, $msg, $from);
} else {
$this->cronMsg($msg);
}
}
}
function preprocess()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
if (FormLib::get_form_value('submit', false) !== false) {
$date = FormLib::get_form_value('date', '');
$date2 = FormLib::get_form_value('date2', '');
if ($date === '' && $date2 !== '') {
// only one date is supplied and it's
// via the secondary field, still use it
$date = $date2;
}
$trans_num = FormLib::get_form_value('trans_num', '');
$card_no = FormLib::get_form_value('card_no', '');
$emp_no = FormLib::get_form_value('emp_no', '');
$register_no = FormLib::get_form_value('register_no', '');
$trans_subtype = FormLib::get_form_value('trans_subtype', '');
$tenderTotal = FormLib::get_form_value('tenderTotal', '');
$department = FormLib::get_form_value('department', '');
$trans_no = "";
if ($trans_num != "") {
$temp = explode("-", $trans_num);
if (count($temp) != 3) {
$emp_no = $reg_no = $trans_no = 0;
} else {
$emp_no = $temp[0];
$register_no = $temp[1];
$trans_no = $temp[2];
}
}
$dbc = FannieDB::get($FANNIE_OP_DB);
$dlog = $FANNIE_TRANS_DB . $dbc->sep() . "transarchive";
$query = "SELECT \n year(datetime) AS year,\n month(datetime) AS month,\n day(datetime) AS day,\n emp_no,\n register_no,\n trans_no,\n MAX(card_no) AS card_no,\n MAX(datetime) AS ts\n FROM {$dlog} WHERE 1=1 ";
$args = array();
if ($date != "") {
$date2 = $date2 != "" ? $date2 : $date;
$query .= ' AND datetime BETWEEN ? AND ? ';
$args[] = $date . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
$dlog = DTransactionsModel::selectDTrans($date, $date2);
// update the table we're searching
$query = str_replace($FANNIE_TRANS_DB . $dbc->sep() . 'transarchive', $dlog, $query);
} else {
$query .= ' AND datetime >= ? ';
$args[] = date('Y-m-d 00:00:00', strtotime('-15 days'));
}
if ($card_no != "") {
$query .= " AND card_no=? ";
$args[] = $card_no;
}
if ($emp_no != "") {
$query .= " AND emp_no=? ";
$args[] = $emp_no;
}
if ($register_no != "") {
$query .= " AND register_no=? ";
$args[] = $register_no;
}
if ($trans_no != "") {
$query .= " AND trans_no=? ";
$args[] = $trans_no;
}
if (FormLib::get('no-training') == '1') {
$query .= ' AND emp_no <> 9999 AND register_no <> 99 ';
}
if (FormLib::get('no-canceled') == '1') {
$query .= ' AND trans_status <> \'X\' ';
}
$tender_clause = "( 1=1";
if ($trans_subtype != "") {
$tender_clause .= " AND trans_subtype=? ";
$args[] = $trans_subtype;
}
if ($tenderTotal != "") {
$tender_clause .= " AND total=-1*? ";
$args[] = $tenderTotal;
} else {
$tender_clause .= ' AND total <> 0 ';
}
$tender_clause .= ")";
/**
There is no tender restriction
replace with a not-true statements
otherwise the OR will match everything
*/
if ($tender_clause == '( 1=1 AND total <> 0 )') {
$tender_clause = '1=0';
}
$or_clause = '(' . $tender_clause;
if ($department != "") {
$or_clause .= " OR (department=? AND trans_type IN ('I','D')) ";
$args[] = $department;
}
if (FormLib::get('is_refund', 0) == 1) {
$or_clause .= ' OR trans_status=\'R\' ';
}
if (FormLib::get('mem_discount', 0) == 1) {
$or_clause .= ' OR upc=\'DISCOUNT\' ';
}
$or_clause .= ")";
if ($or_clause == "(1=0)") {
$or_clause = "1=1";
}
$query .= ' AND ' . $or_clause;
$query .= " GROUP BY year(datetime),month(datetime),day(datetime),emp_no,register_no,trans_no ";
$query .= " ORDER BY year(datetime),month(datetime),day(datetime),emp_no,register_no,trans_no ";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
if (!empty($trans_num) && !empty($date)) {
header("Location: RenderReceiptPage.php?date={$date}&receipt={$trans_num}");
return false;
} else {
if ($dbc->num_rows($result) == 0) {
$this->results = "<b>No receipts match the given criteria</b>";
} else {
if ($dbc->num_rows($result) == 1) {
$row = $dbc->fetch_row($result);
$year = $row[0];
$month = $row[1];
$day = $row[2];
$trans_num = $row[3] . '-' . $row[4] . '-' . $row[5];
header("Location: RenderReceiptPage.php?year={$year}&month={$month}&day={$day}&receipt={$trans_num}");
return false;
} else {
$this->addScript('../../src/javascript/tablesorter/jquery.tablesorter.js');
$this->addOnloadCommand("\$('.tablesorter').tablesorter();\n");
$this->results = "<b>Matching receipts</b>:<br />";
$this->results .= '<table class="table tablesorter">
<thead>
<tr>
<th>Date</th>
<th>Receipt</th>
<th>Employee</th>
<th>Lane</th>
<th>Owner</th>
<th>Subtotal</th>
</tr>
</thead>
<tbody>';
$subTotalP = $dbc->prepare("\n SELECT SUM(-total) AS subtotal\n FROM {$dlog} AS d\n WHERE datetime BETWEEN ? AND ?\n AND trans_type='T'\n AND department = 0\n AND emp_no=?\n AND register_no=?\n AND trans_no=?\n ");
$num_results = $dbc->numRows($result);
while ($row = $dbc->fetch_row($result)) {
$this->results .= '<tr>';
$year = $row[0];
$month = $row[1];
$day = $row[2];
$this->results .= '<td>' . $row['ts'] . '</td>';
$trans_num = $row[3] . '-' . $row[4] . '-' . $row[5];
$this->results .= "<td><a href=RenderReceiptPage.php?year={$year}&month={$month}&day={$day}&receipt={$trans_num}>";
$this->results .= "{$trans_num}</a></td>";
$this->results .= '<td>' . $row['emp_no'] . '</td>';
$this->results .= '<td>' . $row['register_no'] . '</td>';
$this->results .= '<td>' . $row['card_no'] . '</td>';
if ($num_results < 50) {
$subTotalArgs = array(date('Y-m-d 00:00:00', strtotime($row['ts'])), date('Y-m-d 23:59:59', strtotime($row['ts'])), $row['emp_no'], $row['register_no'], $row['trans_no']);
$subTotalR = $dbc->execute($subTotalP, $subTotalArgs);
$subTotalW = $dbc->fetchRow($subTotalR);
$subTotal = is_array($subTotalW) ? $subTotalW['subtotal'] : 0;
$this->results .= sprintf('<td>%.2f</td>', $subTotal);
} else {
$this->results .= '<td>n/a</td>';
}
$this->results .= '</tr>';
}
$this->results .= '</tbody></table>';
}
}
}
}
return true;
}
function fetch_report_data()
{
global $FANNIE_OP_DB, $FANNIE_ARCHIVE_DB, $FANNIE_EQUITY_DEPARTMENTS, $FANNIE_COOP_ID;
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$d1 = $this->form->date1;
$dates = array($d1 . ' 00:00:00', $d1 . ' 23:59:59');
$data = array();
if (isset($FANNIE_COOP_ID) && $FANNIE_COOP_ID == 'WEFC_Toronto') {
$shrinkageUsers = " AND d.card_no not between 99900 and 99998";
} else {
$shrinkageUsers = "";
}
$reconciliation = array('Tenders' => 0.0, 'Sales' => 0.0, 'Discounts' => 0.0, 'Tax' => 0.0);
$dlog = DTransactionsModel::selectDlog($d1);
$tenderQ = $dbc->prepare_statement("SELECT \n TenderName,count(d.total),sum(d.total) as total\n FROM {$dlog} as d,\n {$FANNIE_OP_DB}.tenders as t \n WHERE d.tdate BETWEEN ? AND ?\n AND d.trans_subtype = t.TenderCode\n AND d.total <> 0{$shrinkageUsers}\n GROUP BY t.TenderName ORDER BY TenderName");
$tenderR = $dbc->exec_statement($tenderQ, $dates);
$report = array();
while ($tenderW = $dbc->fetch_row($tenderR)) {
$record = array($tenderW['TenderName'], $tenderW[1], sprintf('%.2f', $tenderW['total']));
$report[] = $record;
$reconciliation['Tenders'] += $tenderW['total'];
}
$data[] = $report;
$salesQ = $dbc->prepare_statement("SELECT m.super_name,sum(d.quantity) as qty,\n sum(d.total) as total\n FROM {$dlog} AS d LEFT JOIN\n {$FANNIE_OP_DB}.MasterSuperDepts AS m ON d.department=m.dept_ID\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department <> 0 AND d.trans_type <> 'T'{$shrinkageUsers}\n GROUP BY m.super_name ORDER BY m.super_name");
$salesQ = '';
switch (FormLib::get('sales-by')) {
case 'Department':
$salesQ = '
SELECT t.dept_name 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\' ' . $shrinkageUsers . '
AND d.tdate BETWEEN ? AND ?
GROUP BY t.dept_name
ORDER BY t.dept_name';
break;
case 'Sales Code':
$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\' ' . $shrinkageUsers . '
AND d.tdate BETWEEN ? AND ?
GROUP BY t.salesCode
ORDER BY t.salesCode';
break;
case 'Super Department':
default:
$salesQ = '
SELECT m.super_name AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN MasterSuperDepts AS m ON d.department=m.dept_ID
WHERE d.department <> 0
AND d.trans_type <> \'T\' ' . $shrinkageUsers . '
AND d.tdate BETWEEN ? AND ?
GROUP BY m.super_name
ORDER BY m.super_name';
break;
}
$salesP = $dbc->prepare($salesQ);
$salesR = $dbc->exec_statement($salesP, $dates);
$report = array();
while ($salesW = $dbc->fetch_row($salesR)) {
$record = array($salesW['category'], sprintf('%.2f', $salesW['qty']), sprintf('%.2f', $salesW['total']));
$report[] = $record;
$reconciliation['Sales'] += $salesW['total'];
}
$data[] = $report;
$discQ = $dbc->prepare_statement("SELECT m.memDesc, SUM(d.total) AS Discount,count(*)\n FROM {$dlog} d \n INNER JOIN memtype m ON d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc = 'DISCOUNT'{$shrinkageUsers}\n AND total <> 0\n GROUP BY m.memDesc ORDER BY m.memDesc");
$discR = $dbc->exec_statement($discQ, $dates);
$report = array();
while ($discW = $dbc->fetch_row($discR)) {
$record = array($discW['memDesc'], $discW[2], $discW[1]);
$report[] = $record;
$reconciliation['Discounts'] += $discW['Discount'];
}
$data[] = $report;
$report = array();
$trans = DTransactionsModel::selectDTrans($d1);
$lineItemQ = $dbc->prepare("\n SELECT description,\n SUM(regPrice) AS ttl\n FROM {$trans} AS d\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)) {
$record = array($lineItemW['description'] . ' (est. owed)', sprintf('%.2f', $lineItemW['ttl']));
$report[] = $record;
}
$taxSumQ = $dbc->prepare_statement("SELECT sum(total) as tax_collected\n FROM {$dlog} as d \n WHERE d.tdate BETWEEN ? AND ?\n AND (d.upc = 'tax'){$shrinkageUsers}\n GROUP BY d.upc");
$taxR = $dbc->exec_statement($taxSumQ, $dates);
while ($taxW = $dbc->fetch_row($taxR)) {
$record = array('Total Tax Collected', round($taxW['tax_collected'], 2));
$report[] = $record;
$reconciliation['Tax'] = $taxW['tax_collected'];
}
$data[] = $report;
$report = array();
foreach ($reconciliation as $type => $amt) {
$report[] = array($type, sprintf('%.2f', $amt));
}
$data[] = $report;
$transQ = $dbc->prepare_statement("select q.trans_num,sum(q.quantity) as items,transaction_type, sum(q.total) from\n (\n select trans_num,card_no,quantity,total,\n m.memDesc as transaction_type\n from {$dlog} as d\n left join memtype as m on d.memType = m.memtype\n WHERE d.tdate BETWEEN ? AND ?\n AND trans_type in ('I','D')\n AND upc <> 'RRR'{$shrinkageUsers}\n ) as q \n group by q.trans_num,q.transaction_type");
$transR = $dbc->exec_statement($transQ, $dates);
$transinfo = array();
while ($row = $dbc->fetch_array($transR)) {
if (!isset($transinfo[$row[2]])) {
$transinfo[$row[2]] = array(0, 0.0, 0.0, 0.0, 0.0);
}
$transinfo[$row[2]][0] += 1;
$transinfo[$row[2]][1] += $row[1];
$transinfo[$row[2]][3] += $row[3];
}
$tSum = 0;
$tItems = 0;
$tDollars = 0;
foreach (array_keys($transinfo) as $k) {
$transinfo[$k][2] = round($transinfo[$k][1] / $transinfo[$k][0], 2);
$transinfo[$k][4] = round($transinfo[$k][3] / $transinfo[$k][0], 2);
$tSum += $transinfo[$k][0];
$tItems += $transinfo[$k][1];
$tDollars += $transinfo[$k][3];
}
$report = array();
foreach ($transinfo as $title => $info) {
array_unshift($info, $title);
$report[] = $info;
}
$data[] = $report;
$ret = preg_match_all("/[0-9]+/", $FANNIE_EQUITY_DEPARTMENTS, $depts);
if ($ret != 0) {
/* equity departments exist */
$depts = array_pop($depts);
$dlist = "(";
foreach ($depts as $d) {
$dates[] = $d;
// add query param
$dlist .= '?,';
}
$dlist = substr($dlist, 0, strlen($dlist) - 1) . ")";
$equityQ = $dbc->prepare_statement("SELECT d.card_no,t.dept_name, sum(total) as total \n FROM {$dlog} as d\n LEFT JOIN {$FANNIE_OP_DB}.departments as t ON d.department = t.dept_no\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department IN {$dlist}{$shrinkageUsers}\n GROUP BY d.card_no, t.dept_name ORDER BY d.card_no, t.dept_name");
$equityR = $dbc->exec_statement($equityQ, $dates);
$report = array();
while ($equityW = $dbc->fetch_row($equityR)) {
$record = array($equityW['card_no'], $equityW['dept_name'], sprintf('%.2f', $equityW['total']));
$report[] = $record;
}
$data[] = $report;
}
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');
$data = array();
$reconciliation = array('Tenders' => 0.0, 'Sales' => 0.0, 'Discounts' => 0.0, 'Tax' => 0.0);
$dlog = DTransactionsModel::selectDlog($d1);
$tenderQ = $dbc->prepare("\n SELECT TenderName,\n COUNT(d.total) AS num,\n SUM(d.total) as total\n FROM {$dlog} AS d\n INNER 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 ORDER BY TenderName");
$tenderR = $dbc->execute($tenderQ, $dates);
$report = array();
while ($tenderW = $dbc->fetch_row($tenderR)) {
$record = array($tenderW['TenderName'], $tenderW[1], sprintf('%.2f', $tenderW['total']));
$report[] = $record;
$reconciliation['Tenders'] += $tenderW['total'];
}
$data[] = $report;
$salesQ = '';
switch (FormLib::get('sales-by')) {
case 'Department':
$salesQ = '
SELECT t.dept_name 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.dept_name
ORDER BY t.dept_name';
break;
case 'Sales Code':
$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';
break;
case 'Super Department':
default:
$salesQ = '
SELECT m.super_name AS category,
SUM(d.quantity) AS qty,
SUM(d.total) AS total
FROM ' . $dlog . ' AS d
LEFT JOIN MasterSuperDepts AS m ON d.department=m.dept_ID
WHERE d.department <> 0
AND d.trans_type <> \'T\'
AND d.tdate BETWEEN ? AND ?
GROUP BY m.super_name
ORDER BY m.super_name';
break;
}
$salesP = $dbc->prepare($salesQ);
$salesR = $dbc->execute($salesP, $dates);
$report = array();
while ($salesW = $dbc->fetch_row($salesR)) {
$record = array($salesW['category'], sprintf('%.2f', $salesW['qty']), sprintf('%.2f', $salesW['total']));
$report[] = $record;
$reconciliation['Sales'] += $salesW['total'];
}
$data[] = $report;
$discQ = $dbc->prepare("\n SELECT m.memDesc, \n SUM(d.total) AS Discount,\n count(*) AS num\n FROM {$dlog} d \n INNER 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 ORDER BY m.memDesc");
$discR = $dbc->execute($discQ, $dates);
$report = array();
while ($discW = $dbc->fetch_row($discR)) {
$record = array($discW['memDesc'], $discW[2], $discW[1]);
$report[] = $record;
$reconciliation['Discounts'] += $discW['Discount'];
}
$data[] = $report;
$report = array();
$trans = DTransactionsModel::selectDTrans($d1);
$lineItemQ = $dbc->prepare("\n SELECT description,\n SUM(regPrice) AS ttl\n FROM {$trans} AS d\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)) {
$record = array($lineItemW['description'] . ' (est. owed)', sprintf('%.2f', $lineItemW['ttl']));
$report[] = $record;
}
$taxSumQ = $dbc->prepare("\n SELECT SUM(total) AS tax_collected\n FROM {$dlog} as d \n WHERE d.tdate BETWEEN ? AND ?\n AND (d.upc = 'tax')\n GROUP BY d.upc");
$taxR = $dbc->execute($taxSumQ, $dates);
while ($taxW = $dbc->fetch_row($taxR)) {
$record = array('Total Tax Collected', round($taxW['tax_collected'], 2));
$report[] = $record;
$reconciliation['Tax'] = $taxW['tax_collected'];
}
$data[] = $report;
$report = array();
foreach ($reconciliation as $type => $amt) {
$report[] = array($type, sprintf('%.2f', $amt));
}
$data[] = $report;
$ret = preg_match_all("/[0-9]+/", $this->config->get('EQUITY_DEPARTMENTS'), $depts);
if ($ret != 0) {
/* equity departments exist */
$depts = array_pop($depts);
$dlist = "(";
foreach ($depts as $d) {
$dates[] = $d;
// add query param
$dlist .= '?,';
}
$dlist = substr($dlist, 0, strlen($dlist) - 1) . ")";
$equityQ = $dbc->prepare("\n SELECT d.card_no,\n t.dept_name, \n SUM(total) AS total \n FROM {$dlog} as d\n LEFT JOIN departments as t ON d.department = t.dept_no\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department IN {$dlist}\n GROUP BY d.card_no, t.dept_name ORDER BY d.card_no, t.dept_name");
$equityR = $dbc->execute($equityQ, $dates);
$report = array();
while ($equityW = $dbc->fetch_row($equityR)) {
$record = array($equityW['card_no'], $equityW['dept_name'], sprintf('%.2f', $equityW['total']));
$report[] = $record;
}
$data[] = $report;
}
return $data;
}
/**
Fetch data for the specified report
@param [string] $report_class_name name of report
@param [FannieConfig] $config current configuration
@param [SQLManager] $connection database connection
@return [array] report records or [boolean] false
if this source cannot handle the request
*/
public function fetchReportData($report_class_name, \FannieConfig $config, \SQLManager $connection)
{
$date1 = \FormLib::get_form_value('date1', date('Y-m-d'));
if ($date1 == date('Y-m-d')) {
// warehouse cannot handle current day requests
return false;
}
$originalDB = $connection->defaultDatabase();
$plugin_settings = $config->get('PLUGIN_SETTINGS');
$connection->selectDB($plugin_settings['WarehouseDatabase']);
$args = array($this->dateToID($date1));
$reconciliation = array(array('Tenders', 0.0), array('Sales', 0.0), array('Discounts', 0.0), array('Tax', 0.0));
$prep = $connection->prepare('
SELECT t.TenderName,
s.quantity,
s.total
FROM sumTendersByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'tenders AS t
ON s.trans_subtype=t.TenderCode
WHERE date_id=?
ORDER BY t.TenderName');
$res = $connection->execute($prep, $args);
$tenders = array();
while ($w = $connection->fetchRow($res)) {
$tenders[] = array($w['TenderName'], $w['quantity'], $w['total']);
$reconciliation[0][1] += $w['total'];
}
/**
Always join into department settings twice
but swap priority depening on user request
*/
$then_prefix = 'a';
$now_prefix = 'b';
if (\FormLib::get('report-departments') == 'Current') {
$then_prefix = 'b';
$now_prefix = 'a';
}
$prep = $connection->prepare('
SELECT COALESCE(a.super_name, b.super_name) AS super_name,
SUM(s.quantity) AS quantity,
SUM(s.total) AS total
FROM sumRingSalesByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'products AS p
ON s.upc=p.upc
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'MasterSuperDepts AS ' . $then_prefix . '
ON s.department=' . $then_prefix . '.dept_ID
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'MasterSuperDepts AS ' . $now_prefix . '
ON p.department=' . $now_prefix . '.dept_ID
WHERE date_id=?
GROUP BY COALESCE(a.super_name, b.super_name)
ORDER BY COALESCE(a.super_name, b.super_name)');
$res = $connection->execute($prep, $args);
$sales = array();
while ($w = $connection->fetchRow($res)) {
$sales[] = array($w['super_name'], $w['quantity'], $w['total']);
$reconciliation[1][1] += $w['total'];
}
$prep = $connection->prepare('
SELECT m.memDesc,
s.transCount AS quantity,
s.total AS total
FROM sumDiscountsByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'memtype AS m
ON s.memType=m.memtype
WHERE s.date_id=?
ORDER BY m.memDesc');
$res = $connection->execute($prep, $args);
$discounts = array();
while ($w = $connection->fetchRow($res)) {
$discounts[] = array($w['memDesc'], $w['quantity'], $w['total']);
$reconciliation[2][1] += $w['total'];
}
$dtrans = \DTransactionsModel::selectDTrans($date1);
$dlog = \DTransactionsModel::selectDlog($date1);
$dates = array($date1 . ' 00:00:00', $date1 . ' 23:59:59');
$lineItemQ = $connection->prepare("\n SELECT description,\n SUM(regPrice) AS ttl\n FROM {$dtrans} AS d\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . \DTrans::isNotTesting('d') . "\n GROUP BY d.description\n ");
$lineItemR = $connection->execute($lineItemQ, $dates);
$taxes = array();
while ($lineItemW = $connection->fetchRow($lineItemR)) {
$taxes[] = array($lineItemW['description'] . ' (est. owed)', sprintf('%.2f', $lineItemW['ttl']));
}
$taxSumQ = $connection->prepare("SELECT sum(total) as tax_collected\n FROM {$dlog} as d \n WHERE d.tdate BETWEEN ? AND ?\n AND (d.upc = 'tax')\n GROUP BY d.upc");
$taxR = $connection->execute($taxSumQ, $dates);
while ($taxW = $connection->fetch_row($taxR)) {
$taxes[] = array('Total Tax Collected', round($taxW['tax_collected'], 2));
$reconciliation[3][1] += $taxW['tax_collected'];
}
$prep = $connection->prepare('
SELECT m.memDesc,
COUNT(*) AS numTrans,
SUM(retailQty + nonRetailQty) AS totalItems,
AVG(retailQty + nonRetailQty) AS avgItems,
SUM(retailTotal + nonRetailTotal) AS total,
AVG(retailTotal + nonRetailTotal) AS avg
FROM transactionSummary AS t
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'memtype AS m
ON t.memType=m.memtype
WHERE date_id=?
GROUP BY m.memDesc
ORDER BY m.memDesc');
$res = $connection->execute($prep, $args);
$transactions = array();
while ($w = $connection->fetchRow($res)) {
$transactions[] = array($w['memDesc'], $w['numTrans'], sprintf('%.2f', $w['totalItems']), sprintf('%.2f', $w['avgItems']), sprintf('%.2f', $w['total']), sprintf('%.2f', $w['avg']));
}
$ret = preg_match_all("/[0-9]+/", $config->get('EQUITY_DEPARTMENTS'), $depts);
$equity = array();
if ($ret != 0) {
/* equity departments exist */
$depts = array_pop($depts);
$dlist = "(";
foreach ($depts as $d) {
$dates[] = $d;
// add query param
$dlist .= '?,';
}
$dlist = substr($dlist, 0, strlen($dlist) - 1) . ")";
$equityQ = $connection->prepare("\n SELECT d.card_no,\n t.dept_name, \n sum(total) as total \n FROM {$dlog} as d\n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "departments as t ON d.department = t.dept_no\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department IN {$dlist}\n GROUP BY d.card_no, \n t.dept_name \n ORDER BY d.card_no, \n t.dept_name");
$equityR = $connection->execute($equityQ, $dates);
while ($equityW = $connection->fetchRow($equityR)) {
$record = array($equityW['card_no'], $equityW['dept_name'], sprintf('%.2f', $equityW['total']));
$equity[] = $record;
}
}
$connection->setDefaultDB($originalDB);
return array($tenders, $sales, $discounts, $taxes, $reconciliation, $transactions, $equity);
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$emp_no = FormLib::get('emp_no', false);
$dtrans = DTransactionsModel::selectDTrans($date1, $date2);
$detailP = $dbc->prepare('
SELECT SUM(CASE WHEN transInterval > 600 THEN 600 ELSE transInterval END) AS seconds,
COUNT(*) AS numTrans
FROM ' . $this->config->get('TRANS_DB') . $dbc->sep() . 'CashPerformDay
WHERE proc_date BETWEEN ? AND ?
AND emp_no = ?
');
$basicQ = '
SELECT d.emp_no,
e.FirstName,
COUNT(*) AS rings,
SUM(CASE WHEN trans_status=\'R\' THEN 1 ELSE 0 END) as refundRings,
SUM(CASE WHEN trans_status=\'V\' THEN 1 ELSE 0 END) as voidRings,
SUM(CASE WHEN trans_status=\'R\' THEN total ELSE 0 END) as refundTotal,
SUM(CASE WHEN trans_status=\'V\' THEN total ELSE 0 END) as voidTotal,
SUM(CASE WHEN trans_type=\'D\' THEN 1 ELSE 0 END) as openRings,
SUM(CASE WHEN trans_type=\'D\' THEN total ELSE 0 END) as openRingTotal,
SUM(CASE WHEN trans_status=\'X\' AND charflag <> \'S\' THEN 1 ELSE 0 END) as cancelRings,
SUM(CASE WHEN trans_status=\'X\' AND charflag <> \'S\' THEN total ELSE 0 END) as cancelTotal
FROM ' . $dtrans . ' AS d
INNER JOIN employees AS e ON d.emp_no=e.emp_no
WHERE d.datetime BETWEEN ? AND ?
AND trans_type IN (\'I\', \'D\')
AND trans_status <> \'M\'
AND register_no <> 99
AND d.emp_no <> 9999
AND d.store_id <> 50
';
if ($emp_no) {
$basicQ .= ' AND d.emp_no = ? ';
}
$basicQ .= '
GROUP BY d.emp_no, e.FirstName
ORDER BY e.FirstName';
$basicP = $dbc->prepare($basicQ);
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
if ($emp_no) {
$args[] = $emp_no;
}
$basicR = $dbc->execute($basicP, $args);
$data = array();
while ($row = $dbc->fetch_row($basicR)) {
$record = array($row['FirstName'], $row['rings'], $row['refundRings'], sprintf('%.2f%%', $this->safeDivide($row['refundRings'], $row['rings']) * 100.0), sprintf('$%.2f', $this->safeDivide($row['refundTotal'], $row['refundRings'])), $row['voidRings'], sprintf('%.2f%%', $this->safeDivide($row['voidRings'], $row['rings']) * 100.0), sprintf('$%.2f', $this->safeDivide($row['voidTotal'], $row['voidRings'])), $row['openRings'], sprintf('%.2f%%', $this->safeDivide($row['openRings'], $row['rings']) * 100.0), sprintf('$%.2f', $this->safeDivide($row['openRingTotal'], $row['openRings'])), $row['cancelRings'], sprintf('%.2f%%', $this->safeDivide($row['cancelRings'], $row['rings']) * 100.0), sprintf('$%.2f', $this->safeDivide($row['cancelTotal'], $row['cancelRings'])));
$args[2] = $row['emp_no'];
$detailR = $dbc->execute($detailP, $args);
$detailW = $dbc->fetch_row($detailR);
$time = $detailW['seconds'];
$trans = $detailW['numTrans'];
$minutes = $time / 60.0;
$record[] = $trans;
$record[] = sprintf('%.2f', $time / 60.0);
$record[] = sprintf('%.2f', $this->safeDivide($row['rings'], $minutes));
$data[] = $record;
}
return $data;
}
