public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $include_sales = FormLib::get('includeSales', 0); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer == -2) { $where .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args); $where .= $conditional; } $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "SELECT d.upc,\n p.brand,\n p.description,\n d.department,\n t.dept_name,\n SUM(total) AS total,\n SUM(d.cost) AS cost," . DTrans::sumQuantity('d') . " AS qty\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't'); // join only needed with specific buyer if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $query .= "WHERE tdate BETWEEN ? AND ?\n AND {$where}\n AND d.cost <> 0 "; if ($include_sales != 1) { $query .= "AND d.discounttype=0 "; } $query .= "GROUP BY d.upc,p.description,d.department,t.dept_name\n ORDER BY sum(total) DESC"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $data = array(); $sum_total = 0.0; $sum_cost = 0.0; while ($row = $dbc->fetch_row($result)) { $margin = $row['total'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['total'] * 100; $record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['cost']), sprintf('%.2f', $row['total']), sprintf('%.2f', $margin), sprintf('%.2f', $row['qty'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['qty'])); $sum_total += $row['total']; $sum_cost += $row['cost']; $data[] = $record; } // go through and add a contribution to margin value for ($i = 0; $i < count($data); $i++) { // (item_total - item_cost) / total sales $contrib = ($data[$i][5] - $data[$i][4]) / $sum_total * 100; $data[$i][] = sprintf('%.2f', $contrib); } 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; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); $dlog = DTransactionsModel::selectDlog($date1, $date2); $queryAll = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d \n WHERE tdate BETWEEN ? AND ?\n GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)\n ORDER BY YEAR(tdate), MONTH(tdate), DAY(tdate)"; $argsAll = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $querySelected = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d "; if ($buyer !== '' && $buyer > -1) { $querySelected .= " LEFT JOIN superdepts AS s ON d.department=s.dept_ID "; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $querySelected .= " WHERE tdate BETWEEN ? AND ? "; $argsSel = $argsAll; if ($buyer !== '') { if ($buyer == -2) { $querySelected .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $querySelected .= ' AND s.superID=? '; $argsSel[] = $buyer; } } if ($buyer != -1) { list($conditional, $argsSel) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $argsSel); $querySelected .= $conditional; } $querySelected .= " GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)"; $dataset = array(); $prep = $dbc->prepare_statement($queryAll); $result = $dbc->exec_statement($prep, $argsAll); while ($row = $dbc->fetch_row($result)) { $datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']); $dataset[$datestr] = array('ttl' => $row['trans_count'], 'sub' => 0); } $prep = $dbc->prepare_statement($querySelected); $result = $dbc->exec_statement($prep, $argsSel); while ($row = $dbc->fetch_row($result)) { $datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']); if (isset($dataset[$datestr])) { $dataset[$datestr]['sub'] = $row['trans_count']; } } $data = array(); foreach ($dataset as $date => $count) { $record = array($date, $count['sub'], $count['ttl']); $record[] = sprintf('%.2f%%', $count['sub'] / $count['ttl'] * 100); $data[] = $record; } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer == -2) { $where .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args); $where .= $conditional; } $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "\n SELECT d.upc,\n p.brand,\n p.description," . DTrans::sumQuantity('d') . " AS qty,\n CASE WHEN memDiscount <> 0 AND memType <> 0 THEN unitPrice - memDiscount ELSE unitPrice END as price,\n d.department, \n t.dept_name, \n SUM(total) AS total\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't'); // join only needed with specific buyer if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $query .= "\n WHERE tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY d.upc,p.description,price,d.department,t.dept_name\n ORDER BY d.upc"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $data = array(); while ($row = $dbc->fetch_row($result)) { $record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['price']), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total'])); $data[] = $record; } // bold items that sold at multiple prices for ($i = 0; $i < count($data); $i++) { if (!isset($data[$i + 1])) { continue; } if ($data[$i][0] == $data[$i + 1][0]) { $data[$i]['meta'] = FannieReportPage::META_BOLD; $data[$i + 1]['meta'] = FannieReportPage::META_BOLD; } } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array(); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer == -2) { $where .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 'p'); $where .= $conditional; } $args[] = $date1 . ' 00:00:00'; $args[] = $date2 . ' 23:59:59'; $query = "SELECT MIN(CASE WHEN a.modified IS NULL THEN p.modified ELSE a.modified END) AS entryDate, \n a.upc, p.description, p.department, d.dept_name\n FROM products AS p INNER JOIN prodUpdate AS a ON a.upc=p.upc\n LEFT JOIN departments AS d ON d.dept_no=p.department "; // join only needed with specific buyer if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON p.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID '; } $query .= "WHERE {$where}\n GROUP BY p.upc,p.description,p.department, d.dept_name\n HAVING entryDate BETWEEN ? AND ?\n ORDER BY entryDate"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $data = array(); while ($row = $dbc->fetch_row($result)) { $record = array($row['entryDate'], $row['upc'], $row['description'], $row['department'], $row['dept_name']); $data[] = $record; } return $data; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer > -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } elseif ($buyer == -2) { $where .= ' AND s.superID <> 0 '; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args); $where .= $conditional; } $tempTables = array('connection' => $dbc, 'clauses' => array(array('sql' => 'trans_type IN (?, ?)', 'params' => array('I', 'D')))); $dlog = DTransactionsModel::selectDlog($date1, $date2, $tempTables); $query = "SELECT year(tdate),month(tdate),day(tdate),\n SUM(CASE WHEN trans_type='D' THEN total ELSE 0 END) as total,\n SUM(CASE WHEN trans_type='D' THEN abs(quantity) ELSE 0 END) as qty,\n SUM(CASE WHEN trans_type='D' THEN 1.0 ELSE 0.0 END) /\n SUM(CASE WHEN trans_type IN ('I','D') THEN 1.0 ELSE 0.0 END) as percentage\n FROM {$dlog} as d "; // join only needed with specific buyer if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID '; } elseif ($buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $query .= "WHERE trans_type IN ('I','D')\n AND tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY year(tdate),month(tdate),day(tdate)\n ORDER BY year(tdate),month(tdate),day(tdate)"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $data = array(); while ($row = $dbc->fetch_row($result)) { $record = array(sprintf('%d/%d/%d', $row[1], $row[2], $row[0]), sprintf('%.2f', $row['total']), sprintf('%.2f', $row['qty']), sprintf('%.2f%%', $row['percentage'] * 100)); $data[] = $record; } return $data; }
function fetch_report_data() { global $FANNIE_OP_DB, $FANNIE_ARCHIVE_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get_form_value('deptStart', 0); $deptEnd = FormLib::get_form_value('deptEnd', 0); $deptMulti = FormLib::get('departments', array()); $tempName = "TempNoMove"; $dlog = DTransactionsModel::selectDlog($date1, $date2); $tempQ = $dbc->prepare_statement("CREATE TABLE {$tempName} (upc varchar(13))"); $dbc->exec_statement($tempQ); $insQ = $dbc->prepare("\n INSERT INTO {$tempName}\n SELECT d.upc FROM {$dlog} AS d\n WHERE \n d.tdate BETWEEN ? AND ?\n AND d.trans_type='I'\n GROUP BY d.upc"); $dbc->exec_statement($insQ, array($date1 . ' 00:00:00', $date2 . ' 23:59:59')); $where = ' 1=1 '; $buyer = FormLib::get('super'); $args = array(); if ($buyer !== '') { if ($buyer == -2) { $where .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 'p'); $where .= $conditional; } $query = "\n SELECT p.upc,\n p.brand,\n p.description,\n d.dept_no,\n d.dept_name \n FROM products AS p \n LEFT JOIN departments AS d ON p.department=d.dept_no "; if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON p.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID '; } $query .= " WHERE p.upc NOT IN (\n SELECT upc FROM {$tempName}\n )\n AND {$where}\n AND p.inUse=1\n ORDER BY p.upc"; $prep = $dbc->prepare($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]; $record[] = $row[1]; $record[] = $row[2]; $record[] = $row[3]; $record[] = $row[4]; if ($this->report_format == 'html') { $record[] = sprintf('<a href="" id="del%s" onclick="backgroundDeactivate(\'%s\');return false;"> Deactivate this item</a>', $row[0], $row[0]); } else { $record[] = ''; } if ($this->report_format == 'html') { $record[] = sprintf('<a href="" id="del%s" onclick="backgroundDelete(\'%s\',\'%s\');return false;"> Delete this item</a>', $row[0], $row[0], $row[1]); } else { $record[] = ''; } $ret[] = $record; } $drop = $dbc->prepare_statement("DROP TABLE {$tempName}"); $dbc->exec_statement($drop); return $ret; }
public function fetch_report_data() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $date1 = $this->form->date1; $date2 = $this->form->date2; $deptStart = FormLib::get('deptStart', 1); $deptEnd = FormLib::get('deptEnd', 1); $deptMulti = FormLib::get('departments', array()); $buyer = FormLib::get('buyer', ''); $dlog = DTransactionsModel::selectDlog($date1, $date2); /** Report Query notes: * Combining vendorName and prodExtra.distributor is a nod to legacy data. Eventually data should be fully normalized on products.default_vendor_id * Excluding prodExtra.distributor empty string combines those records with SQL NULL. Having two different "blank" rows is confusing for users. * Joins are only needed is a super department condition is involved. WHERE clause changes similarly. */ $query = ' SELECT COALESCE(v.vendorName, x.distributor) AS vendor, ' . DTrans::sumQuantity('t') . ' AS qty, SUM(t.total) AS ttl FROM ' . $dlog . ' AS t ' . DTrans::joinProducts('t', 'p', 'LEFT') . ' LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID LEFT JOIN prodExtra AS x ON p.upc=x.upc '; if ($buyer !== '' && $buyer > -1) { $query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= ' LEFT JOIN MasterSuperDepts AS s ON t.department=s.dept_ID '; } $query .= ' WHERE t.tdate BETWEEN ? AND ? AND t.trans_type IN (\'I\',\'D\') '; $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); if ($buyer !== '') { if ($buyer == -2) { $query .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $query .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 't'); $query .= $conditional; } $query .= ' GROUP BY COALESCE(v.vendorName, x.distributor) ORDER BY SUM(total) DESC'; $prep = $dbc->prepare($query); $result = $dbc->execute($prep, $args); $data = array(); $total_sales = 0.0; while ($w = $dbc->fetch_row($result)) { $data[] = array($w['vendor'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['ttl']), 0.0); $total_sales += $w['ttl']; } for ($i = 0; $i < count($data); $i++) { $data[$i][3] = sprintf('%.2f%%', $data[$i][2] / $total_sales * 100); $data[$i][2] = '$' . $data[$i][2]; } 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; $deptStart = FormLib::get('deptStart'); $deptEnd = FormLib::get('deptEnd'); $deptMulti = FormLib::get('departments', array()); $weekday = FormLib::get('weekday', 0); $buyer = FormLib::get('buyer', ''); // args/parameters differ with super // vs regular department $args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59'); $where = ' 1=1 '; if ($buyer !== '') { if ($buyer == -2) { $where .= ' AND s.superID != 0 '; } elseif ($buyer != -1) { $where .= ' AND s.superID=? '; $args[] = $buyer; } } if ($buyer != -1) { list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args); $where .= $conditional; } $date_selector = 'year(tdate), month(tdate), day(tdate)'; $day_names = array(); if ($weekday == 1) { $date_selector = $dbc->dayofweek('tdate'); $timestamp = strtotime('next Sunday'); for ($i = 1; $i <= 7; $i++) { $day_names[$i] = strftime('%a', $timestamp); $timestamp = strtotime('+1 day', $timestamp); } } $hour = $dbc->hour('tdate'); $dlog = DTransactionsModel::selectDlog($date1, $date2); $query = "SELECT {$date_selector}, {$hour} as hour, \n sum(d.total) AS ttl, avg(d.total) as avg\n FROM {$dlog} AS d "; // join only needed with specific buyer // or all retail if ($buyer !== '' && $buyer > -1) { $query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID '; } elseif ($buyer !== '' && $buyer == -2) { $query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID '; } $query .= "WHERE d.trans_type IN ('I','D')\n AND d.tdate BETWEEN ? AND ?\n AND {$where} "; if ($this->config->get('COOP_ID') == 'WFC_Duluth') { $query .= ' AND d.department NOT IN (993, 998, 703) '; } $query .= " GROUP BY {$date_selector}, {$hour}\n ORDER BY {$date_selector}, {$hour}"; $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($query, $args); $dataset = array(); $minhour = 24; $maxhour = 0; while ($row = $dbc->fetch_row($result)) { $hour = (int) $row['hour']; $date = ''; if ($weekday == 1) { $date = $day_names[$row[0]]; } else { $date = sprintf('%d/%d/%d', $row[1], $row[2], $row[0]); } if (!isset($dataset[$date])) { $dataset[$date] = array(); } $dataset[$date][$hour] = $row['ttl']; if ($hour < $minhour) { $minhour = $hour; } if ($hour > $maxhour) { $maxhour = $hour; } } /** # of columns is dynamic depending on the date range selected */ $this->report_headers = array('Day'); foreach ($dataset as $day => $info) { $this->report_headers[] = $day; } $this->report_headers[] = 'Total'; $data = array(); /** # of rows is dynamic depending when the store was open */ for ($i = $minhour; $i <= $maxhour; $i++) { $record = array(); $sum = 0; if ($i < 12) { $record[] = str_pad($i, 2, '0', STR_PAD_LEFT) . ':00 AM'; } else { if ($i == 12) { $record[] = $i . ':00 PM'; } else { $record[] = str_pad($i - 12, 2, '0', STR_PAD_LEFT) . ':00 PM'; } } // each day's sales for the given hour foreach ($dataset as $day => $info) { $sales = isset($info[$i]) ? $info[$i] : 0; $record[] = sprintf('%.2f', $sales); $sum += $sales; } $record[] = sprintf('%.2f', $sum); $data[] = $record; } return $data; }