<?php
/**
 * 报表实现类 西安绝对人气 定制 月度客资数据统计
 * *
 * ============================================================================
 * 版权所有 2017北京素玄科技，并保留所有权利。
 *
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 * $Author: lilong 2020-06 $
 */
namespace app\crm\lib\report;
use app\crm\lib\Report;
use think\Controller;
use think\image\Exception;

class Xa_month_cus_data extends Report {

    private $level= 1;
    private $type = 1;
    private $code = 'code1593061674';

    public function getLevel(){
        return $this->level;
    }
    public function getType(){
        return $this->type;
    }
    public function getCode(){
        return $this->code;
    }
    public function data(){
        set_time_limit(0);
        $data = input();
        // $start = date('Y-m-d', strtotime($data['start']));
        // $end = date('Y-m-d 23:59:59', strtotime($data['end']));
        // $det = db('s_report_form')->alias('f')->join('s_report_detail d','d.form_id=f.id')->where('d.id',$data['did'])->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();
        // $shop_code = $det['shop_code'];
        $shop_code = $data['shop_code']?$data['shop_code']:session('shop_code');
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'请选择门店');
        }
        // 获取月的每一天
        if ($data['getMonthDay']) {
            $param['month'] = date('Y-m',strtotime($data['month']));
            return $this->getMonthDay($param);
        }
        $start = date('Y-m-01 00:00:00', strtotime($data['month']));
        $end = date('Y-m-d 23:59:59', strtotime('+1 month -1day'.$start));
        // 列
        $param['month'] = date('Y-m',strtotime($data['month']));
        $column = $this->getMonthDay($param);
        if($data['channel_id']){
            $channels = explode(',',$data['channel_id']);
        }else{
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channels = array_column($channels,'channel_name');
        }
        $where['c.is_status'] = 1;
        $where['c.c_from'] = 1;
        $where['c.add_shop_code'] = $shop_code;
        if((int)$data['consultation_type_id']){
            $whereT['c.consultation_type_id'] = (int)$data['consultation_type_id'];
        }

        $field = '';
        $costField = '';
        $orderField = '';
        switch ($data['data_type']) {
            //------------------------ 计算各种量 cusNum ------------------------//
            case 'mkz':
                $sql = 'dj_day between "'.$start.'" and "'.$end.'"';
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['sql']   = $sql;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusNum($param);
                break;
            case 'valid':
            	$yxconfig = db_config('yx_config') ? db_config('yx_config') : "'1','5'";
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$yxconfig.')';
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['sql']   = $sql;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusNum($param);
                break;
            case 'unValid':
            	$wxconfig = db_config('wx_config') ? db_config('wx_config') : "'3','4'";
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$wxconfig.')';
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['sql']   = $sql;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusNum($param);
                break;
            case 'dd':
            	$ddconfig = db_config('dd_config') ? db_config('dd_config') : "'2'";
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$ddconfig.')';
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['sql']   = $sql;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusNum($param);
                break;
            case 'yy':
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and yjd_day IS NOT NULL';
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['sql']   = $sql;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusNum($param);
                break;
            case 'jd':
                $sql = 'syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')';
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['sql']   = $sql;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusNum($param);
                break;
            case 'order':
                $sql = 'order_day between "'.$start.'" and "'.$end.'" and is_success=1';
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['sql']   = $sql;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusNum($param);
                break;
            //------------------------ 计算各种率 cusRate ------------------------//
            case 'validRate':
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'"';
                $group['1'] = 'dj_day';
                $yxconfig = db_config('yx_config') ? db_config('yx_config') : "'1','5'";
                $sql['2'] = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$yxconfig.')';
                $group['2'] = 'dj_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            case 'unValidRate':
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'"';
                $group['1'] = 'dj_day';
                $wxconfig = db_config('wx_config') ? db_config('wx_config') : "'3','4'";
                $sql['2'] = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$wxconfig.')';
                $group['2'] = 'dj_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            case 'ddRate':
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'"';
                $group['1'] = 'dj_day';
                $ddconfig = db_config('dd_config') ? db_config('dd_config') : "'2'";
                $sql['2'] = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$ddconfig.')';
                $group['2'] = 'dj_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            case 'mkzJdRate':
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'"';
                $group['1'] = 'dj_day';
                $sql['2'] = 'syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')';
                $group['2'] = 'syjd_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            case 'validJdRate':
            	$yxconfig = db_config('yx_config') ? db_config('yx_config') : "'1','5'";
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$yxconfig.') ';
                $group['1'] = 'dj_day';
                $sql['2'] = 'syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')';
                $group['2'] = 'syjd_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            case 'jdOrderRate':
                $sql['1'] = 'syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')';
                $group['1'] = 'syjd_day';
                $sql['2'] = 'order_day between "'.$start.'" and "'.$end.'" and is_success=1';
                $group['2'] = 'order_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            case 'mkzOrderRate':
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'"';
                $group['1'] = 'dj_day';
                $sql['2'] = 'order_day between "'.$start.'" and "'.$end.'" and is_success=1';
                $group['2'] = 'order_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            case 'validOrderRate':
            	$yxconfig = db_config('yx_config') ? db_config('yx_config') : "'1','5'";
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$yxconfig.') ';
                $group['1'] = 'dj_day';
                $sql['2'] = 'order_day between "'.$start.'" and "'.$end.'" and is_success=1';
                $group['2'] = 'order_day';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRate($param);
                break;
            //------------------------ 计算花费 ------------------------//
            case 'cost':
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'"';
                $sql['2'] = 'cost_date between "'.$start.'" and "'.$end.'" ';
                $group = 'cost_date';
                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusCost($param);
                break;
            //------------------------ 计算各种营收 实收 ------------------------//
            case 'total_money':
                $sql['1'] = 'c.order_day between "'.$start.'" and "'.$end.'"';
                $sql['2'] = 'c.order_day between "'.$start.'" and "'.$end.'" ';
                $group = 'c.order_day';
                $field = 'o.order_amount';
                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['field'] = $field;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusMoney($param);
                break;
            case 'fact_money':
                $sql['1'] = 'c.order_day between "'.$start.'" and "'.$end.'"';
                $sql['2'] = 'c.order_day between "'.$start.'" and "'.$end.'" ';
                $group = 'c.order_day';
                $field = 'o.order_fact';
                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['field'] = $field;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusMoney($param);
                break;
            //------------------------ 计算各种成本 花费 除以 毛客资，有效客资等 ------------------------//
            case 'mkzCost':
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'"';
                $group['1'] = 'dj_day';
                $sql['2'] = 'cost_date between "'.$start.'" and "'.$end.'" ';
                $group['2'] = 'cost_date';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusCb($param);
                break;
            case 'yxCost':
            	$yxconfig = db_config('yx_config') ? db_config('yx_config') : "'1','5'";
                $sql['1'] = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in ('.$yxconfig.') ';
                $group['1'] = 'dj_day';
                $sql['2'] = 'cost_date between "'.$start.'" and "'.$end.'" ';
                $group['2'] = 'cost_date';
                
                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusCb($param);
                break;
            case 'jdCost':
                $sql['1'] = 'syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')';
                $group['1'] = 'syjd_day';
                $sql['2'] = 'cost_date between "'.$start.'" and "'.$end.'" ';
                $group['2'] = 'cost_date';
                
                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusCb($param);
                break;
            case 'orderCost':
                $sql['1'] = 'order_day between "'.$start.'" and "'.$end.'" and is_success=1';
                $group['1'] = 'order_day';
                $sql['2'] = 'cost_date between "'.$start.'" and "'.$end.'" ';
                $group['2'] = 'cost_date';
                
                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusCb($param);
                break;
            //------------------------ 计算均价 roi ------------------------//
            case 'orderJunjia':
                $sql['1'] = 'c.order_day between "'.$start.'" and "'.$end.'" and c.is_success=1';
                $group['1'] = 'c.order_day';
                $sql['2'] = 'c.order_day between "'.$start.'" and "'.$end.'" and c.is_success=1';
                $group['2'] = 'c.order_day';

                $field = 'o.order_amount';

                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['field'] = $field;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusJj($param);
                break;
            case 'ROI':
                $sql['1'] = 'cost_date between "'.$start.'" and "'.$end.'" ';
                $group['1'] = 'cost_date';
                $sql['2'] = 'c.order_day between "'.$start.'" and "'.$end.'" and c.is_success=1';
                $group['2'] = 'c.order_day';
                $field = 'o.order_amount';
                
                $param['sql'] = $sql;
                $param['group'] = $group;
                $param['field'] = $field;
                $param['where'] = $where;
                $param['whereT']= $whereT;
                $param['column']= $column;
                $param['channels']=$channels;
                return $this->cusRoi($param);
                break;
            default:
                $return['code'] = 0;
                $return['msg']  = '';
                $return['count']= 0;
                $return['data'] = [];
                return json($return);
                break;
        }
    }
    public function show(){
        $code = $this->getCode();
        $did = strtolower(input('param.did'));
        $shop_code = session('shop_code');
        // $info = db('s_report_form')->alias('f')->join('s_report_detail d','d.form_id=f.id')->where('d.id',$did)->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();
        //来源渠道
        $channel = getChannelJson($shop_code);
        $this->assign("channel", json_encode($channel));
        //消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>session('brandclass_id')]);
        $this->assign("consumptionType", $consumptionType);
        
        $info = db('s_report_form')->where('code', $code)->field('report_class_name,report_name')->find();
        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $this->assign('title', session('shop_name').'-'.$info['report_name']);

        $mc = new \app\common\lib\mcInterface();
        $shopList = $mc->cacheData('5a8fd8296f3d3',['staff_id'=>session('staff_id')]);
        $this->assign('shop_list',$shopList);

        $this->assign('did', $info['id']);
        $this->assign('month',date('Y-m'));
        
        $param['month'] = date('Y-m');
        $header = $this->getMonthDay($param);
        $this->assign('header',$header);

        return $this->fetch($display);
    }
    // 获取表头
    public function getMonthDay($param){
        $month = date('Y-m', strtotime($param['month']) );
        $start = date('Y-m-01', strtotime($month));
        $end = date('Y-m-d', strtotime('+1 month -1day'.$start));
        
        $month = getDateFromRange($start,$end);
        foreach ($month as $key => $value) {
            $monthDay[$key] = date('d',strtotime($value));
        }
        return $monthDay;
    }
    // 数据类型 （量）
    public function cusNum($param){
        $where = $param['where'];
        $whereT= $param['whereT'];
        $sql = $param['sql'];
        $column = $param['column'];
        $channels = $param['channels'];

        $channel = db('c_customerinfo')->alias('c')
            ->where($where)
            ->where($sql)
            ->where('customer_channel','in',$channels)
            ->group('customer_channel')->column('customer_channel');

        // 数据
        $info = db('c_customerinfo')->alias('c')
            ->where($where)
            ->where($sql)
            ->where('customer_channel','in',$channels)
            ->group('customer_channel,new_dj_day')
            ->field('customer_channel,DATE_FORMAT(dj_day,"%d") as new_dj_day,COUNT(id) AS num')
            // ->fetchSql(true)
            ->select();
        $sql = db()->getLastSql();
        // var_dump($info);die;
        $returnData = [];
        $arr = [];
        foreach ($channel as $key => $value) {
            // var_dump($value);
            $returnData[$key]['channel_name'] = $value;
            foreach ($info as $k => $v) {
                if ($value == $v['customer_channel']) {
                    $returnData[$key][$v['new_dj_day']] = $v['num'] ? $v['num'] : 0;
                    $returnData[$key]['总计'] += $v['num'] ? $v['num'] : 0;
                }
            }
            $all += $returnData[$key]['总计'];
        }
        $arr = [];
        $arr['channel_name'] = '总计';
        $arr['总计'] = $all;
        foreach ($column as $key => $value) {
            $arr[$value] = '';
            foreach ($returnData as $k => $v) {
                $arr[$value] += $v[$value];
            }
        }
        // die;
        array_unshift($returnData, $arr);
        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channel)+1;
        $return['data'] = $returnData;
        $return['sql'] = $sql;
        return json($return);
    }
    // 数据类型 （率）
    public function cusRate($param){
        $where = $param['where'];
        $whereT= $param['whereT'];
        $column = $param['column'];
        $channels = $param['channels'];
        $sql = $param['sql'];
        $group = $param['group'];
        $time = microtime(true).'<br />';
        $channel = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['1'])
            ->where('customer_channel','in',$channels)
            ->group('customer_channel')->column('customer_channel');
        $time .= microtime(true).'<br />';
        // 数据 被除数(大)
        $info1 = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['1'])
            ->where('customer_channel','in',$channels)
            ->group('customer_channel,new_day')
            ->field('customer_channel,DATE_FORMAT('.$group['1'].',"%d") as new_day,COUNT(id) AS num1')
            ->select();
        $sql1 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        // 数据 除数（小）
        $info2 = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['2'])
            ->where('customer_channel','in',$channels)
            ->group('customer_channel,new_day')
            ->field('customer_channel,DATE_FORMAT('.$group['2'].',"%d") as new_day,COUNT(id) AS num2')
            ->select();
        $sql2 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        // var_dump($info);die;
        $temp = array_merge($info1,$info2);
        $info = [];
        foreach ($temp as $key => $v) {
            $info[$v['customer_channel'].$v['new_day']]['customer_channel'] = $v['customer_channel'];
            $info[$v['customer_channel'].$v['new_day']]['new_day'] = $v['new_day'];
            $info[$v['customer_channel'].$v['new_day']]['num1'] += $v['num1'];
            $info[$v['customer_channel'].$v['new_day']]['num2'] += $v['num2'];
        }
        $returnData = [];
        $arr = [];
        foreach ($channel as $key => $value) {
            // var_dump($value);
            $returnData[$key]['channel_name'] = $value;
            foreach ($info as $k => $v) {
                if ($value == $v['customer_channel']) {
                    $returnData[$key][$v['new_day'].'_1'] = $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key][$v['new_day'].'_2'] = $v['num2'] ? $v['num2'] : 0;
                    $returnData[$key]['总计_1'] += $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key]['总计_2'] += $v['num2'] ? $v['num2'] : 0;

                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']*100).'%' : '0%';
                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']*100).'%' : '0%';
                }
            }
            $all1 += $returnData[$key]['总计_1'];
            $all2 += $returnData[$key]['总计_2'];
        }
        $time .= microtime(true).'<br />';
        foreach ($returnData as $key => $value) {
            $returnData[$key]['总计'] = $value['总计_2'] ? sprintf('%.2f',$value['总计_2']/$value['总计_1']*100).'%' : '0%';
        }
        $arr = [];
        $arr['channel_name'] = '总计';
        $arr['总计'] = $all2 ? sprintf('%.2f',$all2/$all1*100).'%' : '0%';
        foreach ($column as $key => $value) {
            $arr[$value] = '';
            foreach ($returnData as $k => $v) {
                $arr[$value.'_1'] += $v[$value.'_1'];
                $arr[$value.'_2'] += $v[$value.'_2'];
            }
        }
        foreach ($column as $key => $value) {            
            $arr[$value] = $arr[$value.'_2'] ? sprintf('%.2f',$arr[$value.'_2']/$arr[$value.'_1']*100).'%' : '0%';
        }
        $time .= microtime(true).'<br />';
        array_unshift($returnData, $arr);
        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channel)+1;
        $return['data'] = $returnData;
        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['time'] = $time;
        return json($return);
    }
    // 数据类型 花费
    public function cusCost($param){
        $where = $param['where'];
        $whereT= $param['whereT'];
        $column = $param['column'];
        $channels = $param['channels'];
        $sql = $param['sql'];
        $group = $param['group'];
        $time = microtime(true).'<br />';
        // $channel = db('c_customerinfo')->alias('c')
        //     ->where($where)
        //     ->where($sql['1'])
        //     ->where('customer_channel','in',$channels)
        //     ->group('customer_channel')->column('customer_channel');
        // $time .= microtime(true).'<br />';
        // 数据
        $info = db('s_channel_cost')
            ->where('channel_name','in',$channels)
            ->where($sql['2'])
            ->group('channel_name,new_day')
            ->field('channel_name,DATE_FORMAT('.$group.',"%d") as new_day,sum(cost) AS num')
            ->select();
        $sql = db()->getLastSql();
        $time .= microtime(true).'<br />';
        $returnData = [];
        $arr = [];
        foreach ($channels as $key => $value) {
            $returnData[$key]['channel_name'] = $value;
            foreach ($info as $k => $v) {
                if ($value == $v['channel_name']) {
                    $returnData[$key][$v['new_day']] = $v['num'] ? sprintf('%.2f',$v['num']) : '0.00';
                    $returnData[$key]['总计'] += $v['num'] ? sprintf('%.2f',$v['num']) : '0.00';
                }
            }
            $all += $returnData[$key]['总计'];
        }
        $time .= microtime(true).'<br />';
        $arr = [];
        $arr['channel_name'] = '总计';
        $arr['总计'] = $all;
        foreach ($column as $key => $value) {
            $arr[$value] = '';
            foreach ($returnData as $k => $v) {
                $arr[$value] += $v[$value];
            }
        }
        $time .= microtime(true).'<br />';
        // die;
        array_unshift($returnData, $arr);
        // var_dump($returnData);
        // die;
        $return['code'] = 0;
        $return['msg']  = '';
        $return['count']= count($channel)+1;
        $return['data'] = $returnData;
        $return['sql']  = $sql;
        $return['time'] = $time;
        return json($return);
    }
    // 数据类型 收入
    public function cusMoney($param){
        $where = $param['where'];
        $whereT= $param['whereT'];
        $column = $param['column'];
        $channels = $param['channels'];
        $sql = $param['sql'];
        $group = $param['group'];
        $field = $param['field'];
        $time = microtime(true).'<br />';
        $channel = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['1'])
            ->where('c.customer_channel','in',$channels)
            ->group('c.customer_channel')->column('customer_channel');
        $sql1 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        $info = db('c_customerinfo')->alias('c')
            ->join('c_customer_order o','o.order_sn = c.order_number and o.customer_id = c.id','left')
            ->where('o.refund',1)->where('o.is_delete',2)
            ->where($where)->where($sql['2'])
            ->where('c.customer_channel','in',$channels)
            ->group('c.customer_channel,new_day')
            ->field('c.customer_channel,DATE_FORMAT('.$group.',"%d") as new_day,SUM('.$field.') AS num')
            ->select();
        $sql2 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        $returnData = [];
        $arr = [];
        foreach ($channel as $key => $value) {
            // var_dump($value);
            $returnData[$key]['channel_name'] = $value;
            foreach ($info as $k => $v) {
                if ($value == $v['customer_channel']) {
                    $returnData[$key][$v['new_day']] = $v['num'] ? $v['num'] : 0;
                    $returnData[$key]['总计'] += $v['num'] ? $v['num'] : 0;
                }
            }
            $all += $returnData[$key]['总计'];
        }
        $arr = [];
        $arr['channel_name'] = '总计';
        $arr['总计'] = $all;
        foreach ($column as $key => $value) {
            $arr[$value] = '';
            foreach ($returnData as $k => $v) {
                $arr[$value] += $v[$value];
            }
        }
        // die;
        $time .= microtime(true).'<br />';
        array_unshift($returnData, $arr);
        $return['code'] = 0;
        $return['msg']  = '';
        $return['count']= count($channel)+1;
        $return['data'] = $returnData;
        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['time']= $time;
        return json($return);
    }
    // 数据类型 成本
    public function cusCb($param){
        $where = $param['where'];
        $whereT= $param['whereT'];
        $column = $param['column'];
        $channels = $param['channels'];
        $sql = $param['sql'];
        $group = $param['group'];
        $time = microtime(true).'<br />';
        $channel = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['1'])
            ->where('customer_channel','in',$channels)
            ->group('customer_channel')->column('customer_channel');
        $time .= microtime(true).'<br />';
        // 数据 毛客资，有效客资，进店，订单等等
        $info1 = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['1'])
            ->where('customer_channel','in',$channels)
            ->group('customer_channel,new_day')
            ->field('customer_channel,DATE_FORMAT('.$group['1'].',"%d") as new_day,COUNT(id) AS num1')
            ->select();
        $sql1 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        // 数据 花费
        $info2 = db('s_channel_cost')
            ->where('channel_name','in',$channels)
            ->where($sql['2'])
            ->group('channel_name,new_day')
            ->field('channel_name as customer_channel,DATE_FORMAT('.$group['2'].',"%d") as new_day,sum(cost) AS num2')
            ->select();
        $sql2 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        $temp = array_merge($info1,$info2);
        $info = [];
        foreach ($temp as $key => $v) {
            $info[$v['customer_channel'].$v['new_day']]['customer_channel'] = $v['customer_channel'];
            $info[$v['customer_channel'].$v['new_day']]['new_day'] = $v['new_day'];
            $info[$v['customer_channel'].$v['new_day']]['num1'] += $v['num1'];
            $info[$v['customer_channel'].$v['new_day']]['num2'] += $v['num2'] ? sprintf('%.2f',$v['num2']) : '0.00';
            $all1 += $v['num1'];
            $all2 += $v['num2'];
        }
        $returnData = [];
        $arr = [];
        foreach ($channel as $key => $value) {
            // var_dump($value);
            $returnData[$key]['channel_name'] = $value;
            foreach ($info as $k => $v) {
                if ($value == $v['customer_channel']) {
                    $returnData[$key][$v['new_day'].'_1'] = $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key][$v['new_day'].'_2'] = $v['num2'] ? $v['num2'] : 0;
                    $returnData[$key]['总计_1'] += $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key]['总计_2'] += $v['num2'] ? $v['num2'] : 0;

                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']) : '0.00';
                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']) : '0.00';
                }
            }
        }
        $time .= microtime(true).'<br />';
        foreach ($returnData as $key => $value) {
            $returnData[$key]['总计'] = $value['总计_2'] ? sprintf('%.2f',$value['总计_2']/$value['总计_1']) : '0';
        }
        $arr = [];
        $arr['channel_name'] = '总计';
        $arr['总计'] = $all2 ? sprintf('%.2f',$all2/$all1) : '0';
        foreach ($column as $key => $value) {
            $arr[$value] = '';
            foreach ($returnData as $k => $v) {
                $arr[$value.'_1'] += $v[$value.'_1'];
                $arr[$value.'_2'] += $v[$value.'_2'];
            }
        }
        foreach ($column as $key => $value) {            
            $arr[$value] = $arr[$value.'_2'] ? sprintf('%.2f',$arr[$value.'_2']/$arr[$value.'_1']) : '0';
        }
        $time .= microtime(true).'<br />';
        array_unshift($returnData, $arr);
        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channel)+1;
        $return['data'] = $returnData;
        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['time'] = $time;
        return json($return);
    }
    // 数据类型 均价
    public function cusJj($param){
        $where = $param['where'];
        $whereT= $param['whereT'];
        $column = $param['column'];
        $channels = $param['channels'];
        $sql = $param['sql'];
        $group = $param['group'];
        $field = $param['field'];
        $time = microtime(true).'<br />';
        $channel = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['1'])
            ->where('customer_channel','in',$channels)
            ->group('customer_channel')->column('customer_channel');
        $time .= microtime(true).'<br />';
        // 数据 成交单量
        $info1 = db('c_customerinfo')->alias('c')
            ->where($where)->where($sql['1'])
            ->where('customer_channel','in',$channels)
            ->group('customer_channel,new_day')
            ->field('customer_channel,DATE_FORMAT('.$group['1'].',"%d") as new_day,COUNT(id) AS num1')
            ->select();
        $sql1 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        // 数据 营业额
        $info2 = db('c_customerinfo')->alias('c')
            ->join('c_customer_order o','o.order_sn = c.order_number and o.customer_id = c.id','left')
            ->where('o.refund',1)->where('o.is_delete',2)
            ->where($where)->where($sql['2'])
            ->where('c.customer_channel','in',$channels)
            ->group('c.customer_channel,new_day')
            ->field('c.customer_channel,DATE_FORMAT('.$group['2'].',"%d") as new_day,SUM('.$field.') AS num2')
            ->select();
        $sql2 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        // var_dump($info);die;
        $temp = array_merge($info1,$info2);
        $info = [];
        foreach ($temp as $key => $v) {
            $info[$v['customer_channel'].$v['new_day']]['customer_channel'] = $v['customer_channel'];
            $info[$v['customer_channel'].$v['new_day']]['new_day'] = $v['new_day'];
            $info[$v['customer_channel'].$v['new_day']]['num1'] += $v['num1'];
            $info[$v['customer_channel'].$v['new_day']]['num2'] += $v['num2'];
        }
        $returnData = [];
        $arr = [];
        foreach ($channel as $key => $value) {
            // var_dump($value);
            $returnData[$key]['channel_name'] = $value;
            foreach ($info as $k => $v) {
                if ($value == $v['customer_channel']) {
                    $returnData[$key][$v['new_day'].'_1'] = $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key][$v['new_day'].'_2'] = $v['num2'] ? $v['num2'] : 0;
                    $returnData[$key]['总计_1'] += $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key]['总计_2'] += $v['num2'] ? $v['num2'] : 0;

                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']) : '0';
                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']) : '0';
                }
            }
            $all1 += $returnData[$key]['总计_1'];
            $all2 += $returnData[$key]['总计_2'];
        }
        $time .= microtime(true).'<br />';
        foreach ($returnData as $key => $value) {
            $returnData[$key]['总计'] = $value['总计_2'] ? sprintf('%.2f',$value['总计_2']/$value['总计_1']) : '0';
        }
        $arr = [];
        $arr['channel_name'] = '总计';
        $arr['总计'] = $all2 ? sprintf('%.2f',$all2/$all1) : '0';
        foreach ($column as $key => $value) {
            $arr[$value] = '';
            foreach ($returnData as $k => $v) {
                $arr[$value.'_1'] += $v[$value.'_1'];
                $arr[$value.'_2'] += $v[$value.'_2'];
            }
        }
        foreach ($column as $key => $value) {            
            $arr[$value] = $arr[$value.'_2'] ? sprintf('%.2f',$arr[$value.'_2']/$arr[$value.'_1']) : '0';
        }
        $time .= microtime(true).'<br />';
        array_unshift($returnData, $arr);
        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channel)+1;
        $return['data'] = $returnData;
        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['time'] = $time;
        return json($return);
    }
    // 数据类型 ROI
    public function cusRoi($param){
        $where = $param['where'];
        $whereT= $param['whereT'];
        $column = $param['column'];
        $channels = $param['channels'];
        $sql = $param['sql'];
        $group = $param['group'];
        $field = $param['field'];
        $time = microtime(true).'<br />';
        // $channel = db('c_customerinfo')->alias('c')
        //     ->where($where)->where($sql['1'])
        //     ->where('customer_channel','in',$channels)
        //     ->group('customer_channel')->column('customer_channel');
        // $time .= microtime(true).'<br />';
        // 数据 花费
        $info1 = db('s_channel_cost')
            ->where('channel_name','in',$channels)
            ->where($sql['1'])
            ->group('channel_name,new_day')
            ->field('channel_name as customer_channel,DATE_FORMAT('.$group['1'].',"%d") as new_day,sum(cost) AS num1')
            ->select();
        $sql1 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        // 数据 营业额
        $info2 = db('c_customerinfo')->alias('c')
            ->join('c_customer_order o','o.order_sn = c.order_number and o.customer_id = c.id','left')
            ->where('o.refund',1)->where('o.is_delete',2)
            ->where($where)->where($sql['2'])
            ->where('c.customer_channel','in',$channels)
            ->group('c.customer_channel,new_day')
            ->field('c.customer_channel,DATE_FORMAT('.$group['2'].',"%d") as new_day,SUM('.$field.') AS num2')
            ->select();
        $sql2 = db()->getLastSql();
        $time .= microtime(true).'<br />';
        // var_dump($info);die;
        $temp = array_merge($info1,$info2);
        $info = [];
        foreach ($temp as $key => $v) {
            $info[$v['customer_channel'].$v['new_day']]['customer_channel'] = $v['customer_channel'];
            $info[$v['customer_channel'].$v['new_day']]['new_day'] = $v['new_day'];
            $info[$v['customer_channel'].$v['new_day']]['num1'] += $v['num1'];
            $info[$v['customer_channel'].$v['new_day']]['num2'] += $v['num2'];
        }
        $returnData = [];
        $arr = [];
        foreach ($channels as $key => $value) {
            // var_dump($value);
            $returnData[$key]['channel_name'] = $value;
            foreach ($info as $k => $v) {
                if ($value == $v['customer_channel']) {
                    $returnData[$key][$v['new_day'].'_1'] = $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key][$v['new_day'].'_2'] = $v['num2'] ? $v['num2'] : 0;
                    $returnData[$key]['总计_1'] += $v['num1'] ? $v['num1'] : 0;
                    $returnData[$key]['总计_2'] += $v['num2'] ? $v['num2'] : 0;

                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']) : '0';
                    $returnData[$key][$v['new_day']] = $returnData[$key][$v['new_day'].'_2'] ? sprintf('%.2f',$returnData[$key][$v['new_day'].'_2']/$returnData[$key][$v['new_day'].'_1']) : '0';
                }
            }
            $all1 += $returnData[$key]['总计_1'];
            $all2 += $returnData[$key]['总计_2'];
        }
        $time .= microtime(true).'<br />';
        foreach ($returnData as $key => $value) {
            $returnData[$key]['总计'] = $value['总计_2'] ? sprintf('%.2f',$value['总计_2']/$value['总计_1']) : '0';
        }
        $arr = [];
        $arr['channel_name'] = '总计';
        $arr['总计'] = $all2 ? sprintf('%.2f',$all2/$all1) : '0';
        foreach ($column as $key => $value) {
            $arr[$value] = '';
            foreach ($returnData as $k => $v) {
                $arr[$value.'_1'] += $v[$value.'_1'];
                $arr[$value.'_2'] += $v[$value.'_2'];
            }
        }
        foreach ($column as $key => $value) {            
            $arr[$value] = $arr[$value.'_2'] ? sprintf('%.2f',$arr[$value.'_2']/$arr[$value.'_1']) : '0';
        }
        $time .= microtime(true).'<br />';
        array_unshift($returnData, $arr);
        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channel)+1;
        $return['data'] = $returnData;
        $return['sql1'] = $sql1;
        $return['sql2'] = $sql2;
        $return['time'] = $time;
        return json($return);
    }
}