public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $query_parts = FormLib::standardItemFromWhere(); $query = ' SELECT t.upc, t.description, t.department, d.dept_name, ' . DTrans::sumQuantity('t') . ' AS qty, SUM(t.total) AS total ' . $query_parts['query'] . ' AND t.memType=? GROUP BY t.upc, t.description, t.department, d.dept_name ORDER BY t.upc'; $args = $query_parts['args']; $args[] = FormLib::get('memtype'); $data = array(); $prep = $dbc->prepare($query); $res = $dbc->execute($prep, $args); while ($w = $dbc->fetchRow($res)) { $data[] = array($w['upc'], $w['description'], $w['department'] . ' ' . $w['dept_name'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['total'])); } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $query = ''; $from_where = FormLib::standardItemFromWhere(); switch ($this->mode) { case 'PLU': $query = "\n 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') 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 t.department,\n d.dept_name,\n m.super_name,\n COALESCE(v.vendorName,x.distributor) AS distributor\n " . $from_where['query'] . "\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 t.department,\n d.dept_name,\n m.super_name,\n COALESCE(v.vendorName,x.distributor)\n ORDER BY SUM(t.total) DESC"; break; case 'Department': $query = "\n SELECT t.department,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS total \n " . $from_where['query'] . "\n GROUP BY t.department,\n d.dept_name\n ORDER BY SUM(t.total) DESC"; break; case 'Date': $query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS total \n " . $from_where['query'] . "\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)"; break; case 'Weekday': $cols = $dbc->dayofweek("tdate") . " AS dayNumber,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 = "\n SELECT " . $cols . " AS dayName, " . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as total \n " . $from_where['query'] . "\n GROUP BY " . str_replace(' AS dayNumber', '', $cols) . "\n ORDER BY " . $dbc->dayofweek('t.tdate'); break; } $prep = $dbc->prepare($query); $result = $dbc->execute($prep, $from_where['args']); $data = array(); while ($row = $dbc->fetch_row($result)) { switch ($this->mode) { case 'PLU': $data[] = array($row['upc'], $row['description'], $row['rings'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']), $row['department'], $row['dept_name'], $row['super_name'], $row['distributor']); break; case 'Department': $data[] = array($row['department'], $row['dept_name'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total'])); break; case 'Date': $tstamp = mktime(0, 0, 0, $row['month'], $row['day'], $row['year']); $data[] = array(date('m/d/Y', $tstamp), date('l', $tstamp), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total'])); break; case 'Weekday': $data[] = array($row['dayNumber'], $row['dayName'], sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total'])); break; } } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $dlog = DTransactionsModel::selectDlog($date1, $date2); $from_where = FormLib::standardItemFromWhere(); $select_cols = ' t.upc AS prodID, CASE WHEN p.brand IS NULL THEN \'\' ELSE p.brand END AS brand, CASE WHEN p.description IS NULL THEN t.description ELSE p.description END AS description'; $group_cols = ' t.upc, p.brand, CASE WHEN p.description IS NULL THEN t.description ELSE p.description END'; if (FormLib::get('lookup-type') == 'likecode') { $select_cols = ' u.likeCode AS prodID, \'\' AS brand, u.likeCodeDesc AS description'; $group_cols = ' u.likeCode, u.likeCodeDesc'; } $query = "\n SELECT \n YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day,\n {$select_cols}, " . DTrans::sumQuantity('t') . " AS total\n " . $from_where['query'] . "\n AND trans_status <> 'M'\n AND trans_type = 'I'\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate),\n {$group_cols}\n ORDER BY prodID,\n YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $from_where['args']); // variable columns. one per dates $dates = array(); while ($date1 != $date2) { $dates[] = $date1; $parts = explode("-", $date1); if (count($parts) != 3) { break; } $date1 = date("Y-m-d", mktime(0, 0, 0, $parts[1], $parts[2] + 1, $parts[0])); } $dates[] = $date2; $this->report_headers = array('UPC', 'Brand', 'Description'); foreach ($dates as $i) { $this->report_headers[] = $i; } $this->report_headers[] = 'Total'; $current = array('upc' => '', 'brand' => '', 'description' => ''); $data = array(); // track upc while going through the rows, storing // all data about a given upc before printing while ($row = $dbc->fetch_array($result)) { if ($current['upc'] != $row['prodID']) { if ($current['upc'] != "") { $record = array($current['upc'], $current['brand'], $current['description']); $sum = 0.0; foreach ($dates as $i) { if (isset($current[$i])) { $record[] = sprintf('%.2f', $current[$i]); $sum += $current[$i]; } else { $record[] = 0.0; } } $record[] = sprintf('%.2f', $sum); $data[] = $record; } // update 'current' values and clear data // brand may be missing in the case of like codes $current = array('upc' => $row['prodID'], 'brand' => $row['brand'], 'description' => $row['description']); } // get a yyyy-mm-dd format date from sql results $year = $row['year']; $month = str_pad($row['month'], 2, '0', STR_PAD_LEFT); $day = str_pad($row['day'], 2, '0', STR_PAD_LEFT); $datestr = $year . "-" . $month . "-" . $day; // index result into data based on date string // this is to properly place data in the output table // even when there are 'missing' days for a given upc $current[$datestr] = $row['total']; } // add the last data set $record = array($current['upc'], $current['brand'], $current['description']); $sum = 0.0; foreach ($dates as $i) { if (isset($current[$i])) { $record[] = sprintf('%.2f', $current[$i]); $sum += $current[$i]; } else { $record[] = 0.0; } } $record[] = sprintf('%.2f', $sum); $data[] = $record; return $data; }