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