示例#1
0
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;
}
示例#2
0
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;
}