/** 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')); $date2 = \FormLib::get_form_value('date2', date('Y-m-d')); $type = \FormLib::get_form_value('report-basis', 'Purchases'); $exclude = \FormLib::get_form_value('excludes', ''); if ($type == 'Join Date') { return false; } $ex = preg_split('/\\D+/', $exclude, 0, PREG_SPLIT_NO_EMPTY); $exCondition = ''; $exArgs = array(); foreach ($ex as $num) { $exCondition .= '?,'; $exArgs[] = $num; } $exCondition = substr($exCondition, 0, strlen($exCondition) - 1); $originalDB = $connection->defaultDatabase(); $plugin_settings = $config->get('PLUGIN_SETTINGS'); $connection->selectDB($plugin_settings['WarehouseDatabase']); $query = "\n SELECT \n CASE WHEN m.zip='' THEN 'none' ELSE m.zip END as zipcode,\n COUNT(*) as num_trans, \n SUM(total) as spending,\n COUNT(DISTINCT s.card_no) as uniques\n FROM sumMemSalesByDay AS s \n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "meminfo AS m ON s.card_no=m.card_no \n WHERE "; if (!empty($exArgs)) { $query .= "s.card_no NOT IN ({$exCondition}) AND "; } $query .= "s.date_id BETWEEN ? AND ?\n GROUP BY zipcode\n ORDER BY SUM(total) DESC"; $exArgs[] = $this->dateToID($date1); $exArgs[] = $this->dateToID($date2); $prep = $connection->prepare($query); $result = $connection->execute($prep, $exArgs); while ($row = $connection->fetchRow($result)) { $record = array($row['zipcode'], $row['num_trans'], $row['uniques'], $row['spending']); $data[] = $record; } $connection->setDefaultDB($originalDB); return $data; }
public static function aggregateStruct(SQLManager $connection, $dlog, $start_date, $end_date, stdclass $where, $groupby = array()) { $base_table = self::selectStruct($dlog, $start_date, $end_date); $dt_col = $dlog ? 'tdate' : 'datetime'; $clone_table = $dlog ? 'dlog_15' : 'transarchive'; /** Grouping is required */ if (!is_array($groupby) || count($groupby) == 0) { return $base_table; } /** Validate group by columns */ $model = new DTransactionsModel(null); $columns = $model->getColumns(); $insert_cols = array(); $select_cols = array(); for ($i = 0; $i < count($groupby); $i++) { $group = $groupby[$i]; if (isset($columns[$group])) { $insert_cols[] = $group; $select_cols[] = $group; } elseif (preg_match('/(.+)\\s+AS\\s+(\\w+)$/', $group, $matches)) { $col_definition = $matches[1]; $col_alias = $matches[2]; if (isset($columns[$col_alias])) { $insert_cols[] = $col_alias; $select_cols[] = $group; $groupby[$i] = $col_definition; } else { return $base_table; } } else { return $base_table; } } /** Always include a datetime column */ if (!in_array($dt_col, $insert_cols)) { $insert_cols[] = $dt_col; $select_cols[] = 'MAX(' . $dt_col . ') AS ' . $dt_col; } /** Create randomly named temporary table based on the structure of dlog_15 or transachive */ $config = FannieConfig::factory(); $sep = $connection->sep(); $random_name = uniqid('temp' . rand(1000, 9999)); $temp_table = $config->get('ARCHIVE_DB') . $sep . $random_name; $clone_table = $config->get('TRANS_DB') . $sep . $clone_table; $temp_name = $connection->temporaryTable($temp_table, $clone_table); if ($temp_name === false) { return $base_table; } /** Build a query to insert aggregated rows into the temporary table */ $query = 'INSERT INTO ' . $temp_name . '('; foreach ($insert_cols as $c) { $query .= $c . ','; } $query .= 'total, quantity) '; $query .= ' SELECT '; foreach ($select_cols as $c) { $query .= $c . ','; } /** Always aggregate by total & quantity */ $query .= ' SUM(total) AS total, ' . DTrans::sumQuantity() . ' AS quantity FROM __TRANSACTION_TABLE__ WHERE ' . $dt_col . ' BETWEEN ? AND ? '; $params = array($start_date . ' 00:00:00', $end_date . ' 23:59:59'); /** Add a where clause if one has been specified */ if (property_exists($where, 'sql') && is_array($where->sql)) { foreach ($where->sql as $sql) { $query .= ' AND ' . $sql; } } if (property_exists($where, 'params') && is_array($where->params)) { foreach ($where->params as $p) { $params[] = $p; } } /** Add the group by clause */ $query .= ' GROUP BY '; foreach ($groupby as $group) { $query .= $group . ','; } $query = substr($query, 0, strlen($query) - 1); /** Split monthly archive union if needed */ $source_tables = array(); if (strstr($base_table, ' UNION ')) { preg_match_all('/\\s+FROM\\s+(\\w+)\\s+/', $base_table, $matches); foreach ($matches[1] as $m) { $source_tables[] = $m; } } else { $source_tables = array($base_table); } /** Load data into temporary table from source table(s) using built query */ foreach ($source_tables as $source_table) { $insertQ = str_replace('__TRANSACTION_TABLE__', $source_table, $query); $prep = $connection->prepare($insertQ); if (!$connection->execute($prep, $params)) { return $base_table; } } return $temp_name; }
$query = "INSERT INTO ar_history_sum\n SELECT card_no,SUM(charges),SUM(payments),SUM(charges)-SUM(payments)\n FROM ar_history GROUP BY card_no"; $rslt = $sql->query($query); if ($rslt === False) { echo cron_msg("Failed: {$query}"); } /* turnover view/cache base tables for WFC end-of-month reports */ if (date("j") == 1 && $sql->table_exists("ar_history_backup")) { $query = "TRUNCATE TABLE ar_history_backup"; $rslt = $sql->query($query); if ($rslt === False) { echo cron_msg("Failed: {$query}"); } $query = "INSERT INTO ar_history_backup SELECT * FROM ar_history"; $rslt = $sql->query($query); if ($rslt === False) { echo cron_msg("Failed: {$query}"); } $AR_EOM_Summary_Q = "\n INSERT INTO AR_EOM_Summary\n SELECT c.CardNo," . $sql->concat("c.FirstName", "' '", "c.LastName", '') . " AS memName,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -4\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -4\n THEN payments ELSE 0 END) AS priorBalance,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -3\n THEN a.charges ELSE 0 END) AS threeMonthCharges,\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -3\n THEN a.payments ELSE 0 END) AS threeMonthPayments,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -3\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -3\n THEN payments ELSE 0 END) AS threeMonthBalance,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -2\n THEN a.charges ELSE 0 END) AS twoMonthCharges,\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -2\n THEN a.payments ELSE 0 END) AS twoMonthPayments,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -2\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -2\n THEN payments ELSE 0 END) AS twoMonthBalance,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -1\n THEN a.charges ELSE 0 END) AS lastMonthCharges,\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -1\n THEN a.payments ELSE 0 END) AS lastMonthPayments,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -1\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -1\n THEN payments ELSE 0 END) AS lastMonthBalance\n\n FROM ar_history_backup AS a LEFT JOIN " . $FANNIE_OP_DB . $sql->sep() . "custdata AS c \n ON a.card_no=c.CardNo AND c.personNum=1\n GROUP BY c.CardNo,c.LastName,c.FirstName"; if ($sql->table_exists("AR_EOM_Summary")) { $query = "TRUNCATE TABLE AR_EOM_Summary"; $rslt = $sql->query($query); if ($rslt === False) { echo cron_msg("Failed: {$query}"); } $rslt = $sql->query($AR_EOM_Summary_Q); if ($rslt === False) { echo cron_msg("Failed: {$AR_EOM_Summary_Q}"); } } } echo cron_msg("Done.");
/** Get an available dtransactions.trans_no value @param $connection [SQLManager] database connection @param $emp_no [int] employee number @param $register_no [int] register number @return [int] trans_no */ public static function getTransNo(SQLManager $connection, $emp_no = false, $register_no = false) { $config = FannieConfig::factory(); if ($emp_no === false) { $emp_no = $config->get('EMP_NO'); } if ($register_no === false) { $register_no = $config->get('REGISTER_NO'); } $prep = $connection->prepare(' SELECT MAX(trans_no) AS trans FROM ' . $config->get('TRANS_DB') . $connection->sep() . 'dtransactions WHERE emp_no=? AND register_no=?'); $result = $connection->execute($prep, array($emp_no, $register_no)); if (!$result || $connection->num_rows($result) == 0) { return 1; } else { $row = $connection->fetch_row($result); if ($row['trans'] == '') { return 1; } else { return $row['trans'] + 1; } } }
/** 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); }