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