fn_set_notification('N', __('notice'), __('text_conditions_cleared'));
return array(CONTROLLER_STATUS_REDIRECT, "sales_reports.update_table?report_id={$_REQUEST['report_id']}&table_id={$_REQUEST['table_id']}");
// View report
} elseif ($mode == 'view') {
$report_id = empty($_REQUEST['report_id']) ? db_get_field("SELECT report_id FROM ?:sales_reports WHERE status = 'A' ORDER BY position ASC LIMIT 1") : $_REQUEST['report_id'];
$table_id = empty($_REQUEST['table_id']) ? db_get_field("SELECT table_id FROM ?:sales_reports_tables WHERE report_id = ?i ORDER BY position ASC LIMIT 1", $report_id) : intval($_REQUEST['table_id']);
$reports = fn_get_order_reports(true, $report_id);
// If some reports defined calculate data for them
if (!empty($reports)) {
$report = $reports[$report_id];
// Get report data for each table;
if (!empty($report['tables'])) {
if (isset($report['tables'][$table_id])) {
$table = $report['tables'][$table_id];
if (!empty($table['elements']) && !empty($table['intervals']) && $table['type'] == 'T') {
$_table_cond = fn_get_table_condition($table['table_id']);
if (!empty($_table_cond)) {
$table_conditions[$table['table_id']] = fn_reports_get_conditions($_table_cond);
}
$_values = fn_get_report_statistics($table);
$report['tables'][$table_id]['values'] = $_values;
// Calculate totals
$report['tables'][$table_id]['totals'] = array();
foreach ($_values as $v) {
foreach ($v as $_k => $_v) {
$report['tables'][$table_id]['totals'][$_k] = empty($report['tables'][$table_id]['totals'][$_k]) ? $_v : $report['tables'][$table_id]['totals'][$_k] + $_v;
}
}
$_element_id = db_get_field("SELECT element_id FROM ?:sales_reports_table_elements WHERE table_id = ?i", $table['table_id']);
if (!empty($_element_id)) {
$report['tables'][$table_id]['parameter'] = __("reports_parameter_{$_element_id}");
function fn_get_report_statistics(&$table)
{
$table_condition = fn_get_table_condition($table['table_id'], true);
$order_ids = fn_proceed_table_conditions($table_condition);
foreach ($table['elements'] as $key => $element) {
foreach ($table['intervals'] as $interval) {
$a = $element['element_hash'];
$b = $interval['interval_id'];
if (empty($element['auto_generated'])) {
$element['request'] = fn_get_parameter_request($table['table_id'], $element['element_hash']);
}
$interval['request'] = db_quote(" timestamp BETWEEN ?i AND ?i", $interval['time_from'], $interval['time_to']);
if ($table['display'] == 'order_amount') {
$data[$a][$b] = db_get_field("SELECT SUM(total) FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids}");
} elseif ($table['display'] == 'order_number') {
$data[$a][$b] = db_get_field("SELECT COUNT(total) FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids}");
} elseif ($table['display'] == 'shipping') {
$data[$a][$b] = db_get_field("SELECT SUM(shipping_cost) FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids}");
} elseif ($table['display'] == 'discount') {
$data[$a][$b] = db_get_field("SELECT SUM(subtotal_discount) FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids}");
$_orders = db_get_fields("SELECT order_id FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids}");
$discounts = db_get_fields("SELECT b.extra FROM ?:orders as a LEFT JOIN ?:order_details as b ON a.order_id = b.order_id WHERE a.order_id IN (?n)", $_orders);
foreach ($discounts as $key => $value) {
$extra = @unserialize($value);
if (!empty($extra['discount'])) {
$data[$a][$b] += $extra['discount'];
}
}
$data[$a][$b] = fn_format_price($data[$a][$b]);
} elseif ($table['display'] == 'tax') {
$data[$a][$b] = 0;
$_orders = db_get_fields("SELECT order_id FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids}");
$all_taxes = db_get_fields("SELECT data FROM ?:order_data WHERE order_id IN (?n) AND type = 'T'", $_orders);
foreach ($all_taxes as $key => $value) {
$taxes = @unserialize($value);
if (is_array($taxes)) {
foreach ($taxes as $v) {
if (!empty($v['tax_subtotal'])) {
$data[$a][$b] += $v['tax_subtotal'];
}
}
}
$data[$a][$b] = fn_format_price($data[$a][$b]);
}
} elseif ($table['display'] == 'product_cost') {
$product_cost = empty($element['product_ids']) ? '' : db_quote(" AND product_id IN (?n)", $element['product_ids']);
$_orders = db_get_fields("SELECT order_id FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids}");
$data[$a][$b] = db_get_field("SELECT SUM(amount * price) FROM ?:order_details WHERE order_id IN (?n) ?p", $_orders, $product_cost);
} elseif ($table['display'] == 'product_number') {
$product_count = empty($element['product_ids']) ? '' : " AND product_id IN ('" . implode("', '", $element['product_ids']) . "')";
$_orders = db_get_fields("SELECT order_id FROM ?:orders WHERE {$element['request']} AND {$interval['request']} {$order_ids} ");
$data[$a][$b] = db_get_field("SELECT SUM(amount) FROM ?:order_details WHERE order_id IN (?n) ?p", $_orders, $product_count);
}
$data[$a][$b] = empty($data[$a][$b]) ? 0 : $data[$a][$b];
$data[$a][$b] = @$data[$a][$b] == '0.00' ? 0 : $data[$a][$b];
}
}
return @$data;
}
function fn_get_report_statistics(&$table)
{
$table_condition = fn_get_table_condition($table['table_id'], true);
$order_ids = fn_proceed_table_conditions($table_condition);
$last_elm = end($table['intervals']);
$first_elm = reset($table['intervals']);
$interval_code = $first_elm['interval_code'];
$time_start = $first_elm['time_from'];
$time_end = $last_elm['time_to'];
$new_data = array();
foreach ($table['elements'] as $element) {
$a = $element['element_hash'];
if (empty($element['auto_generated'])) {
$element['request'] = fn_get_parameter_request($table['table_id'], $element['element_hash']);
}
$time_condition = db_quote(" timestamp BETWEEN ?i AND ?i", $time_start, $time_end);
$group_condition = ' GROUP BY `interval`';
if ($interval_code == 'year') {
$add_field = db_quote(", DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y') as `interval`, timestamp");
} elseif ($interval_code == 'month') {
$add_field = db_quote(", DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m') as `interval`, timestamp");
} elseif ($interval_code == 'week') {
$add_field = db_quote(", DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%u') as `interval`, timestamp");
} elseif ($interval_code == 'day') {
$add_field = db_quote(", DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') as `interval`, timestamp");
} else {
$add_field = db_quote(", 1 as `interval`, timestamp");
$group_condition = '';
}
if ($table['display'] == 'order_amount') {
$fields = !empty($element['fields']) ? $element['fields'] : 'SUM(total)';
$tables = !empty($element['tables']) ? $element['tables'] : '?:orders';
$data[$a] = db_get_hash_array("SELECT {$fields} as total {$add_field} FROM {$tables} WHERE {$element['request']} AND {$time_condition} {$order_ids} {$group_condition}", 'interval');
} elseif ($table['display'] == 'order_number') {
$data[$a] = db_get_hash_array("SELECT COUNT(total) as total {$add_field} FROM ?:orders WHERE {$element['request']} AND {$time_condition} {$order_ids} {$group_condition}", 'interval');
} elseif ($table['display'] == 'shipping') {
$data[$a] = db_get_hash_array("SELECT SUM(shipping_cost) as total {$add_field} FROM ?:orders WHERE {$element['request']} AND {$time_condition} {$order_ids} {$group_condition}", 'interval');
} elseif ($table['display'] == 'discount') {
$data[$a] = db_get_hash_array("SELECT SUM(subtotal_discount) as total, ?:order_details.extra {$add_field} FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE {$element['request']} AND {$time_condition} {$order_ids} {$group_condition}", 'interval');
foreach ($data[$a] as $int => $interval_data) {
$extra = @unserialize($interval_data['extra']);
if (!empty($extra['discount'])) {
$data[$a][$int]['total'] += $extra['discount'];
}
unset($interval_data['extra']);
$data[$a][$int]['total'] = fn_format_price($data[$a][$int]['total']);
}
} elseif ($table['display'] == 'tax') {
$all_taxes = db_get_hash_array("SELECT ?:order_data.data {$add_field} FROM ?:order_data LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_data.order_id WHERE ?:order_data.type = 'T' AND {$element['request']} AND {$time_condition} {$order_ids} {$group_condition}", 'interval');
foreach ($all_taxes as $int => $interval_data) {
$data[$a][$int] = $interval_data;
$data[$a][$int]['total'] = 0;
$taxes = @unserialize($interval_data['data']);
if (is_array($taxes)) {
foreach ($taxes as $tax_data) {
if (!empty($tax_data['tax_subtotal'])) {
$data[$a][$int]['total'] += $tax_data['tax_subtotal'];
}
}
}
unset($data[$a][$int]['data']);
$data[$a][$int]['total'] = fn_format_price($data[$a][$int]['total']);
}
} elseif ($table['display'] == 'product_cost') {
$product_cost = empty($element['product_ids']) ? '' : db_quote(" AND product_id IN (?n)", $element['product_ids']);
$data[$a] = db_get_hash_array("SELECT SUM(amount * price) as total {$add_field} FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE {$element['request']} AND {$time_condition} {$order_ids} ?p {$group_condition}", 'interval', $product_cost);
} elseif ($table['display'] == 'product_number') {
$product_count = empty($element['product_ids']) ? '' : db_quote(" AND product_id IN (?n)", $element['product_ids']);
$data[$a] = db_get_hash_array("SELECT SUM(amount) as total {$add_field} FROM ?:order_details LEFT JOIN ?:orders ON ?:orders.order_id = ?:order_details.order_id WHERE {$element['request']} AND {$time_condition} {$order_ids} ?p {$group_condition}", 'interval', $product_count);
}
foreach ($table['intervals'] as $interval) {
$b = $interval['interval_id'];
if (isset($data[$a])) {
foreach ($data[$a] as $interval_data) {
if ($interval_data['timestamp'] >= $interval['time_from'] && $interval_data['timestamp'] <= $interval['time_to']) {
$new_data[$a][$b] = $interval_data['total'];
break;
}
}
}
if (!isset($new_data[$a][$b])) {
$new_data[$a][$b] = 0;
}
}
}
return $new_data;
}