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; }
/** Lots of options on this report. */ 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_form_value('deptStart', ''); $deptEnd = FormLib::get_form_value('deptEnd', ''); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get_form_value('buyer', ''); $groupby = FormLib::get_form_value('sort', 'PLU'); $store = FormLib::get('store', 0); $superP = $dbc->prepare('SELECT dept_ID FROM superdepts WHERE superID=?'); /** Build a WHERE condition for later. Superdepartment (buyer) takes precedence over department and negative values have special meaning Extra lookup to write condition in terms of transaction.department seems to result in better index utilization and faster queries */ $filter_condition = 't.department BETWEEN ? AND ?'; $args = array($deptStart, $deptEnd); if (count($deptMulti) > 0) { $filter_condition = 't.department IN ('; $args = array(); foreach ($deptMulti as $d) { $filter_condition .= '?,'; $args[] = $d; } $filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')'; } if ($buyer !== "" && $buyer > 0) { $filter_condition .= ' AND s.superID=? '; $args[] = $buyer; /* $superR = $dbc->execute($superP, array($buyer)); $filter_condition = 't.department IN ('; $args = array(); while ($superW = $dbc->fetch_row($superR)) { $filter_condition .= '?,'; $args[] = $superW['dept_ID']; } $filter_condition = substr($filter_condition, 0, strlen($filter_condition)-1) . ')'; $filter_condition .= ' AND s.superID=?'; $args[] = $buyer; */ } else { if ($buyer !== "" && $buyer == -1) { $filter_condition = "1=1"; $args = array(); } else { if ($buyer !== "" && $buyer == -2) { $superR = $dbc->execute($superP, array(0)); $filter_condition = 't.department NOT IN (0,'; $args = array(); while ($superW = $dbc->fetch_row($superR)) { $filter_condition .= '?,'; $args[] = $superW['dept_ID']; } $filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')'; $filter_condition .= ' AND s.superID <> 0'; } } } /** * Provide more WHERE conditions to filter irrelevant * transaction records, as a stop-gap until this is * handled more uniformly across the application. */ $filter_transactions = "t.trans_status NOT IN ('D','X','Z')\n AND t.emp_no <> 9999\n AND t.register_no <> 99"; $filter_transactions = DTrans::isValid() . ' AND ' . DTrans::isNotTesting(); /** Select a summary table. For UPC results, per-unique-ring summary is needed. For date/dept/weekday results the per-department summary is fine (and a smaller table) */ $dlog = DTransactionsModel::selectDlog($date1, $date2); /** Build an appropriate query depending on the grouping option */ $query = ""; $superTable = $buyer !== "" && $buyer > 0 ? 'superdepts' : 'MasterSuperDepts'; $args[] = $date1 . ' 00:00:00'; $args[] = $date2 . ' 23:59:59'; $args[] = $store; switch ($groupby) { case 'PLU': $query = "SELECT t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END as description, \n SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings," . DTrans::sumQuantity('t') . " as qty,\n SUM(t.total) AS total,\n d.dept_no,d.dept_name,s.superID,\n COALESCE(v.vendorName,x.distributor) AS distributor\n FROM {$dlog} as t " . DTrans::joinProducts() . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON t.department = s.dept_ID\n LEFT JOIN prodExtra as x on t.upc = x.upc\n LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID\n WHERE {$filter_condition}\n AND t.trans_type IN ('I', 'D')\n AND tdate BETWEEN ? AND ?\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END,\n CASE WHEN t.trans_status = 'R' THEN 'Refund' ELSE 'Sale' END,\n d.dept_no,d.dept_name,s.superID,distributor ORDER BY SUM(t.total) DESC"; break; case 'Department': $query = "SELECT t.department,d.dept_name," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.department,d.dept_name ORDER BY SUM(total) DESC"; break; case 'Date': $query = "SELECT year(tdate),month(tdate),day(tdate)," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales ,\n MAX(" . $dbc->dayofweek('tdate') . ") AS dow\n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department\n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY year(tdate),month(tdate),day(tdate) \n ORDER BY year(tdate),month(tdate),day(tdate)"; break; case 'Weekday': $cols = $dbc->dayofweek("tdate") . ",CASE \n WHEN " . $dbc->dayofweek("tdate") . "=1 THEN 'Sun'\n WHEN " . $dbc->dayofweek("tdate") . "=2 THEN 'Mon'\n WHEN " . $dbc->dayofweek("tdate") . "=3 THEN 'Tue'\n WHEN " . $dbc->dayofweek("tdate") . "=4 THEN 'Wed'\n WHEN " . $dbc->dayofweek("tdate") . "=5 THEN 'Thu'\n WHEN " . $dbc->dayofweek("tdate") . "=6 THEN 'Fri'\n WHEN " . $dbc->dayofweek("tdate") . "=7 THEN 'Sat'\n ELSE 'Err' END"; $query = "SELECT {$cols}," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY {$cols}\n ORDER BY " . $dbc->dayofweek('tdate'); break; } /** Copy the results into an array. Date requires a special case to combine year, month, and day into a single field */ $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[1] . "/" . $row[2] . "/" . $row[0]; $record[] = date('l', strtotime($record[0])); $record[] = sprintf('%.2f', $row[3]); $record[] = sprintf('%.2f', $row[4]); } else { for ($i = 0; $i < $dbc->num_fields($result); $i++) { if (preg_match('/^\\d+\\.\\d+$/', $row[$i])) { $row[$i] = sprintf('%.2f', $row[$i]); } $record[] .= $row[$i]; } } $ret[] = $record; } return $ret; }
echo '<b>RRR Coupons Redeemed</b>'; select_to_table($queryRRR, $args, 0, 'ffffff'); echo '</font>'; echo "</font>\n </body>\n </html>"; $output = ob_get_contents(); \COREPOS\Fannie\API\data\DataCache::putFile("monthly", $output); ob_end_clean(); } echo $output; $newTaxQ = 'SELECT description, SUM(regPrice) AS ttl, numflag AS taxID FROM is4c_trans.transarchive WHERE datetime BETWEEN ? AND ? AND upc=\'TAXLINEITEM\' AND ' . DTrans::isNotTesting() . ' GROUP BY taxID, description'; $sql = FannieDB::get($FANNIE_OP_DB); $prep = $sql->prepare($newTaxQ); $res = $sql->execute($prep, $args); $collected = array(1 => 0.0, 2 => 0.0); while ($row = $sql->fetch_row($res)) { $collected[$row['taxID']] = $row['ttl']; } $state = 0.06875000000000001; $city = 0.01; $deli = 0.0225; $county = 0.005; echo '<table border="1" cellspacing="0" cellpadding="4">'; echo '<tr><th>Tax Collected on Regular rate items</th> <th>' . sprintf('%.2f', $collected[1]) . '</th>
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); }