<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');


class Statistics_model extends CI_Model {
        
    function __construct() 
    {
        parent::__construct();
        $this->DEST = $this->load->database('DEST', TRUE);
    }
    
    // 总订单数
    public function total_order_count($startdate,$enddate)
    {
        $sql="SELECT count(0) as total_order_count FROM orders WHERE o_date BETWEEN '$startdate' AND '$enddate' AND o_delete!=1";
        $query=$this->DEST->query($sql);
        $result=$query->result();
        return $result[0]->total_order_count;
    }

    //总成行
    public function total_success_count($startdate,$enddate)
    {
        $sql="SELECT count(0) as total_success_count 
                FROM orders LEFT JOIN payments on o_sn=pm_o_sn
               WHERE pm_sn in (select min(pm_sn) from payments group by pm_o_sn) 
                     AND o_date BETWEEN '$startdate' AND '$enddate'";
        $query=$this->DEST->query($sql);
        $result=$query->result();
        return $result[0]->total_success_count;
    }

    //总人数
    public function total_guest_count($startdate,$enddate)
    {
        $sql="SELECT (SUM(occ_adultcount)+SUM(occ_childrencount)+SUM(occ_babycount)) as total_guest_count 
                FROM orders 
                     left join order_circuit on o_sn=occ_o_sn 
               where o_date BETWEEN '$startdate' AND '$enddate'";
        $query=$this->DEST->query($sql);
        $result=$query->result();
        empty($result[0]->total_guest_count)?$result[0]->total_guest_count=0:'';
        return $result[0]->total_guest_count;
    }

    //总成行人数
    public function guest_success_count($startdate,$enddate)
    {
        $sql="SELECT (SUM(occ_adultcount)+SUM(occ_childrencount)+SUM(occ_babycount)) as guest_success_count 
                FROM orders 
                     left join order_circuit on o_sn=occ_o_sn 
                     LEFT JOIN payments on o_sn=pm_o_sn 
               where pm_sn in (select min(pm_sn) from payments group by pm_o_sn) 
                     and o_date BETWEEN '$startdate' AND '$enddate'";
        $query=$this->DEST->query($sql);
        $result=$query->result();
        empty($result[0]->guest_success_count)?$result[0]->guest_success_count=0:'';
        return $result[0]->guest_success_count;
    }

    //总销售额
    public function total_money($startdate,$enddate)
    {
        $sql="SELECT SUM(occ_price) as total_money 
                FROM orders 
                     left join order_circuit on o_sn=occ_o_sn 
               WHERE o_date BETWEEN '$startdate' AND '$enddate'";
        $query=$this->DEST->query($sql);
        $result=$query->result();
        empty($result[0]->total_money)?$result[0]->total_money=0:'';
        return $result[0]->total_money;
    }

    //总销售额
    public function total_success_money($startdate,$enddate)
    {
        $sql="SELECT SUM(occ_price) as total_success_money 
                FROM orders 
                     left join order_circuit on o_sn=occ_o_sn 
                     LEFT JOIN payments on o_sn=pm_o_sn 
               where pm_sn in (select min(pm_sn) from payments group by pm_o_sn) 
                     and o_date BETWEEN '$startdate' AND '$enddate'";
        $query=$this->DEST->query($sql);
        $result=$query->result();
        empty($result[0]->total_success_money)?$result[0]->total_success_money=0:'';
        return $result[0]->total_success_money;
    }

    //产品订单数
    public function product_order_count($startdate,$enddate,$filed='occ_circuitcode',$success=false)
    {
        $joinsql=$filed=='occ_circuitcode'?' LEFT JOIN order_circuit on o_sn=occ_o_sn ':'';
        $joinsql.=$success?" LEFT JOIN payments on o_sn=pm_o_sn ":'';
        $mapsql=$success?" AND pm_sn in (select min(pm_sn) from payments group by pm_o_sn) ":'';
        
        $sql="SELECT isnull($filed,'') as $filed,count(0) as total_order_count 
                FROM orders
                     $joinsql
               WHERE o_date BETWEEN '$startdate' AND '$enddate' $mapsql
               GROUP by $filed";
        $query=$this->DEST->query($sql);
        $result=$query->result();

        $product_count=array();
        foreach ($result as $value) {
            if (!isset($product_count[$value->$filed])) {
                $product_count[$value->$filed]=0;
            }
            $product_count[$value->$filed]+=$value->total_order_count;
        }
        return $product_count;
    }

    //产品订单人数
    public function product_order_guests($startdate,$enddate,$filed='occ_circuitcode',$success=false)
    {
        $joinsql=$success?" LEFT JOIN payments on o_sn=pm_o_sn ":'';
        $mapsql=$success?" AND pm_sn in (select min(pm_sn) from payments group by pm_o_sn) ":'';

        $sql="SELECT isnull($filed,'') as $filed,(SUM(occ_adultcount)+SUM(occ_childrencount)+SUM(occ_babycount)) as guest_count 
                FROM orders 
                     left join order_circuit on o_sn=occ_o_sn $joinsql
               where o_date BETWEEN '$startdate' AND '$enddate' $mapsql
               GROUP by $filed ";
        $query=$this->DEST->query($sql);
        $result=$query->result();

        $product_guests=array();
        foreach ($result as $value) {
            if (!isset($product_guests[$value->$filed])) {
                $product_guests[$value->$filed]=0;
            }
            $product_guests[$value->$filed]+=$value->guest_count;
        }

        return $product_guests;
    }

    //总销售额
    public function product_order_money($startdate,$enddate,$filed='occ_circuitcode',$success=false)
    {
        $joinsql=$success?" LEFT JOIN payments on o_sn=pm_o_sn ":'';
        $mapsql=$success?" AND pm_sn in (select min(pm_sn) from payments group by pm_o_sn) ":'';

        $sql="SELECT isnull($filed,'') as $filed,SUM(occ_price) as total_money 
                FROM orders 
                     left join order_circuit on o_sn=occ_o_sn $joinsql
               WHERE o_date BETWEEN '$startdate' AND '$enddate' $mapsql
               GROUP by $filed";
        $query=$this->DEST->query($sql);
        $result=$query->result();

        $product_money=array();
        foreach ($result as $value) {
            if (!isset($product_money[$value->$filed])) {
                $product_money[$value->$filed]=0;
            }
            $product_money[$value->$filed]+=$value->total_money;
        }

        return $product_money;
    }

    //支付方式数据统计
    public function payment_type($startdate,$enddate)
    {
        $sql="SELECT isnull(pm_money_type,'未知支付方式') as pm_money_type,count(0) as ordercount 
                FROM orders 
                     LEFT JOIN payments on o_sn=pm_o_sn 
               WHERE o_date BETWEEN '$startdate' AND '$enddate' 
               GROUP by pm_money_type";
        $query=$this->DEST->query($sql);
        $result=$query->result();

        $ordercount=array();
        foreach ($result as $value) {
            if (!isset($ordercount[$value->pm_money_type])) {
                $ordercount[$value->pm_money_type]=0;
            }
            $ordercount[$value->pm_money_type]+=$value->ordercount;
        }

        return $ordercount;
    }


}