<?php

/**
 * ECSHOP 订单统计
 * ============================================================================
 * 版权所有 (C) 2005-2007 北京亿商互动科技发展有限公司，并保留所有权利。
 * 网站地址: http://www.ecshop.com
 * ----------------------------------------------------------------------------
 * 这是一个免费开源的软件；这意味着您可以在不用于商业目的的前提下对程序代码
 * 进行修改、使用和再发布。
 * ============================================================================
 * @author:     liry <liry123@hotmail.com>
 * @version:    v2.1
 * ---------------------------------------------
 * $Author: paulgao $
 * $Date: 2007-04-19 18:11:35 +0800 (星期四, 19 四月 2007) $
 * $Id: order_stats.php 8272 2007-04-19 10:11:35Z paulgao $
*/

define('IN_ECS', true);

require('includes/init.php');
require_once('../languages/' .$_CFG['lang']. '/admin/statistic.php');

$smarty->assign('lang', $_LANG);

/* act操作项的初始化 */
if (empty($_REQUEST['act']))
{
    $_REQUEST['act'] = 'list';
}
else
{
    $_REQUEST['act'] = trim($_REQUEST['act']);
}

/*------------------------------------------------------ */
//--订单统计
/*------------------------------------------------------ */
if ($_REQUEST['act'] == 'list')
{
    admin_priv('sale_order_stats');

    /* 随机的颜色数组 */
    $color_array = array('33FF66', 'FF6600', '3399FF', '009966', 'CC3399', 'FFCC33', '6699CC', 'CC3366');

    /* 计算订单各种费用之和的语句 */
    $total_fee = " SUM(goods_amount + shipping_fee + insure_fee + pay_fee + pack_fee + card_fee) AS total_turnover ";

    /* 取得订单转化率数据 */
    $sql = "SELECT COUNT(*) AS total_order_num, " .$total_fee.
           " FROM " . $ecs->table('order_info').
           " WHERE order_status = '" .OS_CONFIRMED. "' AND pay_status > '" .PS_UNPAYED. "'" .
           " AND shipping_status > '" .SS_UNSHIPPED. "'";
    $order_general = $db->getRow($sql);
    $order_general['total_turnover'] = floatval($order_general['total_turnover']);

    /* 取得商品总点击数量 */
    $sql = 'SELECT SUM(click_count) FROM ' .$ecs->table('goods') .' WHERE is_delete = 0';
    $click_count = floatval($db->getOne($sql));

    /* 每千个点击的订单数 */
    $click_ordernum = $click_count > 0 ? round(($order_general['total_order_num'] * 1000)/$click_count,2) : 0;

    /* 每千个点击的购物额 */
    $click_turnover = $click_count > 0 ? round(($order_general['total_turnover'] * 1000)/$click_count,2) : 0;

    /* 时间参数 */
    if (isset($_POST) && !empty($_POST))
    {
        $start_date = trim($_POST['start_dateYear']. '-' .$_POST['start_dateMonth']. '-' .$_POST['start_dateDay']);
        $end_date   = trim($_POST['end_dateYear']. '-' .$_POST['end_dateMonth']. '-' .$_POST['end_dateDay']);
    }
    else
    {
        $start_date = date("Y-m-d", time() - 86400 * 30);
        $end_date   = date("Y-m-d", time() + 24 * 3600);
    }

    /* 查询订单概况 */
    $order_info = get_orderinfo($start_date, $end_date);

    /* 生成XML数据 */
    $order_general_xml = "<graph caption='".$_LANG['order_circs']."' decimalPrecision='2' showPercentageValues='0' showNames='1' showValues='1' showPercentageInLabel='0' pieYScale='45' pieBorderAlpha='40' pieFillAlpha='70' pieSliceDepth='15' pieRadius='100' outCnvBaseFontSize='13' baseFontSize='12'>";

    $order_general_xml .= "<set value='" .$order_info['confirmed_num']. "' name='" . $_LANG['confirmed'] . "' color='".$color_array[5]."' />";

    $order_general_xml .= "<set value='" .$order_info['succeed_num']."' name='" . $_LANG['succeed'] . "' color='".$color_array[0]."' />";

    $order_general_xml .= "<set value='" .$order_info['unconfirmed_num']. "' name='" . $_LANG['unconfirmed'] . "' color='".$color_array[1]."'  />";

    $order_general_xml .= "<set value='" .$order_info['invalid_num']. "' name='" . $_LANG['invalid'] . "' color='".$color_array[4]."' />";
    $order_general_xml .= "</graph>";

    /* 查询支付方式 */
    $pay_xml = "<graph caption='" . $_LANG['pay_method'] . "' decimalPrecision='2' showPercentageValues='0' showNames='1' numberPrefix='' showValues='1' showPercentageInLabel='0' pieYScale='45' pieBorderAlpha='40' pieFillAlpha='70' pieSliceDepth='15' pieRadius='100' outCnvBaseFontSize='13' baseFontSize='12'>";

    $sql = 'SELECT i.pay_id, p.pay_name, COUNT(i.order_id) AS order_num ' .
           'FROM ' .$ecs->table('payment'). ' AS p, ' .$ecs->table('order_info'). ' AS i '.
           "WHERE p.pay_id = i.pay_id AND i.order_status = '" .OS_CONFIRMED. "' ".
           "AND i.pay_status > '" .PS_UNPAYED. "' AND i.shipping_status > '" .SS_UNSHIPPED. "' ".
           "AND i.order_time >= '$start_date' AND i.order_time <= '$end_date' ".
           "GROUP BY i.pay_id ORDER BY order_num DESC";
    $pay_res= $db->query($sql);

    while ($pay_item = $db->FetchRow($pay_res))
    {
        $pay_xml .= "<set value='".$pay_item['order_num']."' name='".$pay_item['pay_name']."' color='".$color_array[rand(0,7)]."'/>";
    }

    $pay_xml .= "</graph>";

    /* 配送方式 */
    $ship_xml = "<graph caption='".$_LANG['shipping_method']."' decimalPrecision='2' showPercentageValues='0' showNames='1' numberPrefix='' showValues='1' showPercentageInLabel='0' pieYScale='45' pieBorderAlpha='40' pieFillAlpha='70' pieSliceDepth='15' pieRadius='100' outCnvBaseFontSize='13' baseFontSize='12'>";

    $sql = 'SELECT sp.shipping_id, sp.shipping_name AS ship_name, COUNT(i.order_id) AS order_num ' .
           'FROM ' .$ecs->table('shipping'). ' AS sp, ' .$ecs->table('order_info'). ' AS i ' .
           'WHERE sp.shipping_id = i.shipping_id ' .
           "AND i.order_status= '" .OS_CONFIRMED. "' AND i.pay_status > '" .PS_UNPAYED. "' " .
           "AND i.shipping_status > '" .SS_UNSHIPPED. "' " .
           "AND i.order_time >= '$start_date' AND i.order_time <= '$end_date' " .
           "GROUP BY i.shipping_id ORDER BY order_num DESC";
    $ship_res = $db->query($sql);

    while ($ship_item = $db->fetchRow($ship_res))
    {
        $ship_xml .= "<set value='".$ship_item['order_num']."' name='".$ship_item['ship_name']."' color='".$color_array[rand(0,7)]."' />";
    }

    $ship_xml .= "</graph>";

    /* 赋值到模板 */
    $smarty->assign('order_general',       $order_general);
    $smarty->assign('total_turnover',      price_format($order_general['total_turnover']));
    $smarty->assign('click_count',         $click_count);         //商品总点击数
    $smarty->assign('click_ordernum',      $click_ordernum);      //每千点订单数
    $smarty->assign('click_turnover',      price_format($click_turnover));  //每千点购物额

    $smarty->assign('order_general_xml',   $order_general_xml);
    $smarty->assign('ship_xml',            $ship_xml);
    $smarty->assign('pay_xml',             $pay_xml);

    $smarty->assign('ur_here',             $_LANG['report_order']);
    $smarty->assign('start_date',          $start_date);
    $smarty->assign('end_date',            $end_date);

    assign_query_info();
    $smarty->display('order_stats.htm');
}

/*------------------------------------------------------ */
//--订单统计需要的函数
/*------------------------------------------------------ */
 /**
  * 取得订单概况数据(包括订单的几种状态)
  * @param       $start_date    开始查询的日期
  * @param       $end_date      查询的结束日期
  * @return      $order_info    订单概况数据
  */
 function get_orderinfo($start_date, $end_date)
 {
    $order_info = array();

    /* 未确认订单数 */
    $sql = 'SELECT COUNT(*) AS unconfirmed_num FROM ' .$GLOBALS['ecs']->table('order_info').
           " WHERE order_status = '" .OS_UNCONFIRMED. "' AND order_time >= '$start_date'".
           " AND order_time <= '$end_date'";
    $order_info['unconfirmed_num'] = $GLOBALS['db']->getOne($sql);

    /* 已确认订单数 */
    $sql = 'SELECT COUNT(*) AS confirmed_num FROM ' .$GLOBALS['ecs']->table('order_info').
           " WHERE order_status = '" .OS_CONFIRMED. "' AND order_time >= '$start_date'".
           " AND order_time <= '$end_date'";
    $order_info['confirmed_num'] = $GLOBALS['db']->getOne($sql);

    /* 已成交订单数 */
    $sql = 'SELECT COUNT(*) AS succeed_num FROM ' .$GLOBALS['ecs']->table('order_info').
           " WHERE order_status = '" .OS_CONFIRMED. "' AND pay_status > '" .PS_UNPAYED. "'".
           " AND shipping_status > '".SS_UNSHIPPED."'".
           " AND order_time >= '$start_date' AND order_time <= '$end_date'";
    $order_info['succeed_num'] = $GLOBALS['db']->getOne($sql);

    /* 无效或已取消订单数 */
    $sql = "SELECT COUNT(*) AS invalid_num FROM " .$GLOBALS['ecs']->table('order_info').
           " WHERE order_status > '" .OS_CONFIRMED. "'".
           " AND order_time >= '$start_date' AND order_time <= '$end_date'";
    $order_info['invalid_num'] = $GLOBALS['db']->getOne($sql);

    return $order_info;
 }

?>