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; }