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

class Nnqq_year_customer extends Report {

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

    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 = $data['start'].'-01-01 00:00:00';
        $end = $data['start'].'-12-31 23:59:59';

        $det = db('crm_s_report_form')->alias('f')->join('crm_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();

        //可操作店
        if($data['shop_code']){
            $shop_code = $data['shop_code'];
        }elseif($det['shop_code']){
            $shop_code = $det['shop_code'];
        }else{
            $shop_code = session('shop_code');
        }
        
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'请选择店面');
        }
        
        if($data['channel_id']){
            $channels = explode(',',$data['channel_id']);
            $channel_data = db('crm_s_channel')->where("channel_name","in",$data['channel_id'])->order("order_sort")->select();
            $channel_datas = array_column($channel_data, 'channel_info_name');
            // asort($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channel_data[$key];
            }
        }elseif($data['erji_channel_id']){
            // 来源渠道
            $channels = explode(',',$data['erji_channel_id']);
            $channel_data = db('crm_s_channel')->where("channel_info_name","in",$data['erji_channel_id'])->order("order_sort asc")->select();
            $channel_datas = array_column($channel_data, 'channel_info_name');
            // asort($channel_datas);
            $channel_datas = array_unique($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channel_data[$key];
            }
        }else{
            // 来源渠道
            $channelss = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channel_datas = array_column($channelss, 'channel_info_name');
            // asort($channel_datas);
            $channel_datas = array_unique($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channelss[$key];
            }
            $channels = array_column($channelss,'channel_info_name');
        }
        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['add_shop_code'] = $shop_code;
        if((int)$data['consultation_type_id']){
            $where['consultation_type_id'] = (int)$data['consultation_type_id'];
        }
        $field = '';
        $costField = '';
        $orderField = '';
        switch ($data['data_type']) {
            case 'mkz':
                $sql = 'dj_day between "'.$start.'" and "'.$end.'"';
                break;
             case 'valid':
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in (1,5)';
                break;
            case 'unValid':
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid in (3,4)';
                break;
            case 'dd':
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and is_valid=2';
                break;
            case 'yy':
                $sql = 'dj_day between "'.$start.'" and "'.$end.'" and yjd_day is not null';
                break;
            case 'jd':
                $sql = 'syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')';
                break;
            case 'order':
                $sql = 'order_start_time between "'.$start.'" and "'.$end.'" and is_success=1';
                break;
            case 'validRate':
                $field = 'sum(dj_day between "'.$start.'" and "'.$end.'") as mkz';
                $field.= ',sum(dj_day between "'.$start.'" and "'.$end.'" and is_valid in (1)) as yx,';
                break;
            case 'unValidRate':
                $field = 'sum(dj_day between "'.$start.'" and "'.$end.'") as mkz';
                $field.= ',sum(dj_day between "'.$start.'" and "'.$end.'" and is_valid = 3) as wx,';
                break;
            case 'ddRate':
                $field = 'sum(dj_day between "'.$start.'" and "'.$end.'") as mkz';
                $field.= ',sum(dj_day between "'.$start.'" and "'.$end.'" and is_valid = 2) as dd,';
                break;
            case 'mkzJdRate':
                $field = 'sum(dj_day between "'.$start.'" and "'.$end.'") as mkz';
                $field.= ',sum(syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')) as rd,';
                break;
            case 'validJdRate':
                $field.= 'sum(dj_day between "'.$start.'" and "'.$end.'" and is_valid in (1)) as yx';
                $field.= ',sum(syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')) as rd,';
                break;
            case 'jdOrderRate':
                $field.= 'sum(syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')) as rd';
                $field.= ',sum(order_start_time between "'.$start.'" and "'.$end.'" and is_success=1) as cj,';
                break;
            case 'mkzOrderRate':
                $field = 'sum(dj_day between "'.$start.'" and "'.$end.'") as mkz';
                $field.= ',sum(order_start_time between "'.$start.'" and "'.$end.'" and is_success=1) as cj,';
                break;
            case 'validOrderRate':
                $field.= 'sum(dj_day between "'.$start.'" and "'.$end.'" and is_valid in (1)) as yx';
                $field.= ',sum(order_start_time between "'.$start.'" and "'.$end.'" and is_success=1) as cj,';
                break;
            case 'validOrderRate':
                $field.= 'sum(dj_day between "'.$start.'" and "'.$end.'" and is_valid in (1)) as yx';
                $field.= ',sum(order_start_time between "'.$start.'" and "'.$end.'" and is_success=1) as cj,';
                break;
            case 'cost':       
                $costField.= 'sum(cost) as cost_num,';
                break;
            case 'mkzCost':
                $field = 'sum(dj_day between "'.$start.'" and "'.$end.'") as mkz,';
                $costField.= 'sum(cost) as cost_num,';
                break;
            case 'jdCost':
                $field.= 'sum(syjd_day between "'.$start.'" and "'.$end.'" and is_intostore in (\'2\',\'3\',\'5\')) as rd,';
                $costField.= 'sum(cost) as cost_num,';
                break;
            case 'orderCost':
                $field.= 'sum(order_start_time between "'.$start.'" and "'.$end.'" and is_success=1) as cj,';
                $costField.= 'sum(cost) as cost_num,';
                break;
            case 'orderJunjia':
                $orderField.= 'sum(order_start_time between "'.$start.'" and "'.$end.'" and is_success=1) as cj,';
                break;
            case 'total_money':
                $orderField.= 'sum(bargain_money) as total_money,';
                break;
            case 'ROI':
                $orderField.= 'sum(bargain_money) as total_money,';
                break;
            default:
                $sql = 'dj_day between "'.$start.'" and "'.$end.'"';
                break;
        }
        if($data['channel_id']){
            $field.= 'customer_channel,DATE_FORMAT(dj_day,"%m") as s_dj_day,COUNT(id) AS num';
            $group = 'c.customer_channel,s_targetdate';  
        }else{
            $field.= 'customer_channel,DATE_FORMAT(dj_day,"%m") as s_dj_day,COUNT(i.id) AS num';
            $group = 's.channel_info_name,s_targetdate';
        }
        $orderField.= 'c.customer_channel,DATE_FORMAT(c.dj_day,"%m") as s_dj_day,COUNT(c.id) AS num,o.bargain_money,o.total_money';
        // 渠道
       /* $channel = db('crm_c_customerinfo')
            ->where($where)
            ->where($sql)
            ->where('customer_channel','in',$channels)
            ->group('customer_channel')->column('customer_channel');*/
        if($data['data_type'] == 'order' || $data['data_type'] == 'orderCost' || $data['data_type'] == 'orderJunjia' || $data['data_type'] == 'jdOrderRate' || $data['data_type'] == 'validOrderRate' || $data['data_type'] == 'mkzOrderRate'){
            // 成交量 有效订单数 √
            $orderNum = db('crm_c_customerinfo')->alias('c')
                ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
                ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
                ->field('count(c.id) as num,DATE_FORMAT(erp_order.targetdate,"%d") as s_targetdate,s.channel_name,s.channel_info_name,s.id')
                ->where($where)
                ->where('erp_order.orderId is not null')
                ->where('erp_order.blankoutannal','<>',1)
                ->where('erp_order.payment_money','>',0)
                ->where('erp_order.crm_order_type',1)
                ->where('erp_order.targetdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
                ->group($group)
                ->select();
        }
        if($data['data_type'] == 'order'){
            
            $returnData = [];
            $arr = [];
            foreach ($channelData as $key => $value) {
                // var_dump($value);
                $returnData[$key]['channel_info_name'] = $value['channel_info_name'];
                $channel = 'channel_info_name';
                $channelinfo = 'channel_info_name';
                if($data['channel_id']){
                    $returnData[$key]['channel_name'] = $value['channel_name'];
                    $channel = 'channel_name';
                    $channelinfo = 'customer_channel';
                }
                
                // $returnData[$key]['总计'] = 0;
                foreach ($orderNum as $k => $v) {
                    if ($value[$channel] == $v[$channelinfo]) {
                        $returnData[$key][$v['s_targetdate']] = $v['num'] ? $v['num'] : 0;
                        $returnData[$key]['总计'] += $v['num'] ? $v['num'] : 0;
                    }
                }
                $all += $returnData[$key]['总计'];
            }

        }else if($data['data_type'] == 'cost' || $data['data_type'] == 'mkzCost' || $data['data_type'] == 'jdCost' || $data['data_type'] == 'orderCost'){
            $costField.= "cost,c.channel_name,DATE_FORMAT(cost_date,'%m') as s_cost_date,c.channel_info_name";
            
            if($data['channel_id']){
                $cost = db('crm_s_channel_cost')->alias('c')
                ->where('channel_name','in',$channels)
                ->group('channel_name,s_cost_date')
                ->field($costField)
                ->select();
            }else{
                $cost = db('crm_s_channel_cost')->alias('c')
                ->join('crm_s_channel s','c.channel_name=s.channel_name','left')
                ->where('s.channel_info_name','in',$channels)
                ->group('s.channel_info_name,s_cost_date')
                ->field($costField)
                ->select();
            }
            
            
            $costArr = $cost;
            /*foreach($cost as $key=>$value){
                $costArr[$value['channel_name'].''.$value['cost_date']]['channel_info_name'] = $value['channel_info_name'];
                $costArr[$value['channel_name'].''.$value['cost_date']]['channel_name'] = $value['channel_name'];
                $costArr[$value['channel_name'].''.$value['cost_date']]['cost'] = $value['cost']+$costArr[$value['channel_name'].''.$value['cost_date']]['cost'];
                $costArr[$value['channel_name'].''.$value['cost_date']]['cost_date'] = $value['cost_date'];
            }*/

            if($data['data_type'] == 'cost'){
                $returnData = [];
                // $arr = [];
                foreach ($channelData as $key => $value) {
                    $returnData[$key]['channel_info_name'] = $value['channel_info_name'];
                    $channel = $value['channel_info_name'];
                    $info_cost = 'channel_info_name';
                    if($data['channel_id']){
                        $returnData[$key]['channel_name'] = $value['channel_name'];
                        $channel = $value['channel_name'];
                        $info_cost = 'channel_name';
                    }
                    foreach ($costArr as $k => $v) {
                        if ($channel == $v[$info_cost]) {
                            $returnData[$key][$v['s_cost_date']] = $v['cost_num'] ? sprintf('%.2f',$v['cost_num']) : 0;
                            $returnData[$key]['总计'] += $v['cost_num'] ? sprintf('%.2f',$v['cost_num']) : 0;
                        }
                    }
                    //$all += $returnData[$key]['总计'];
                }

            }else{
                if($data['channel_id']){
                    $info = db('crm_c_customerinfo')
                        ->where($where)
                        ->where($sql)
                        ->where('customer_channel','in',$channels)
                        ->group('customer_channel,s_dj_day')
                        ->field($field)
                        ->select();    
                }else{
                    $info = db('crm_c_customerinfo')->alias('i')
                    ->join('crm_s_channel s','i.customer_channel=s.channel_name','left')
                    ->where($where)
                    ->where($sql)
                    ->where('s.channel_info_name','in',$channels)
                    ->group('s.channel_info_name,s_dj_day')
                    ->field($field.',s.channel_info_name')
                    ->select();
                }
                //echo db()->getLastSql();die;
                $infoArr = $info;
                /*foreach($info as $key=>$value){
                    $infoArr[$value['customer_channel'].''.$value['dj_day']]['channel_info_name'] = $value['channel_info_name'];
                    $infoArr[$value['customer_channel'].''.$value['dj_day']]['customer_channel'] = $value['customer_channel'];
                    $infoArr[$value['customer_channel'].''.$value['dj_day']]['dj_day'] = $value['dj_day'];
                    $infoArr[$value['customer_channel'].''.$value['dj_day']]['num'] = $value['num']+$infoArr[$value['customer_channel'].''.$value['dj_day']]['num'];
                    $infoArr[$value['customer_channel'].''.$value['dj_day']]['mkz'] = $value['mkz']+$infoArr[$value['customer_channel'].''.$value['dj_day']]['mkz'];
                    $infoArr[$value['customer_channel'].''.$value['dj_day']]['rd'] = $value['rd']+$infoArr[$value['customer_channel'].''.$value['dj_day']]['rd'];
                    $infoArr[$value['customer_channel'].''.$value['dj_day']]['cj'] = $value['cj']+$infoArr[$value['customer_channel'].''.$value['dj_day']]['cj'];
                }*/
                if($data['channel_id']){
                    $info_channel = 'customer_channel';
                    $cost_channel = 'channel_name';
                }else{
                    $info_channel = 'channel_info_name';
                    $cost_channel = 'channel_info_name';
                }
                foreach($infoArr as $key=>$value){
                    foreach($costArr as $k2=>$v2){
                        if($value[$info_channel] == $v2[$cost_channel] && $value['s_dj_day'] == $v2['s_cost_date']){
                            $infoArr[$key]['cost'] = $v2['cost_num'];
                        }
                    }
                }
                $returnData = [];
                // $arr = [];
                foreach ($channelData as $key => $value) {
                    $returnData[$key]['channel_info_name'] = $value['channel_info_name'];
                    $channel = $value['channel_info_name'];
                    $info_channel = 'channel_info_name';
                    if($data['channel_id']){
                        $returnData[$key]['channel_name'] = $value['channel_name'];    
                        $channel = $value['channel_name'];
                        $info_channel = 'channel_name';
                    }
                    foreach ($infoArr as $k => $v) {
                         if ($channel == $v[$info_channel]) {
                            switch ($data['data_type']) {
                                case 'mkzCost': //毛客资成本
                                    $returnData[$key][$v['s_dj_day']] = $v['cost'] ?  sprintf('%.2f',$v['cost']/$v['mkz']) : 0;
                                    $returnData[$key]['cost_num'] += $v['cost'] ? $v['cost'] : 0;
                                    $returnData[$key]['mkz_num'] += $v['mkz'] ? $v['mkz'] : 0;
                                    $returnData[$key]['总计'] = $returnData[$key]['mkz_num']? sprintf('%.2f',$returnData[$key]['cost_num']/$returnData[$key]['mkz_num']):'';  
                                    break;
                                case 'jdCost': //入店成本
                                    $returnData[$key][$v['s_dj_day']] = $v['cost'] ?  sprintf('%.2f',$v['cost']/$v['rd']) : 0;
                                    $returnData[$key]['cost_num'] += $v['cost'] ? $v['cost'] : 0;
                                    $returnData[$key]['rd_num'] += $v['rd'] ? $v['rd'] : 0;
                                    $returnData[$key]['总计'] = $returnData[$key]['cost_num']? sprintf('%.2f',$returnData[$key]['cost_num']/$returnData[$key]['rd_num']):'';  
                                    break;
                                case 'orderCost': //成交成本
                                    $returnData[$key][$v['s_dj_day']] = $v['cost'] ? round($v['cost']/$orderNum[$v['s_dj_day']]['num']) : 0;
                                    $returnData[$key]['cost_num'] += $v['cost'] ? $v['cost'] : 0;
                                    $returnData[$key]['cj_num'] += $orderNum[$v['s_dj_day']]['num'] ? $orderNum[$v['s_dj_day']]['num'] : 0;
                                    $returnData[$key]['总计'] = $returnData[$key]['cost_num']?round($returnData[$key]['cost_num']/$returnData[$key]['cj_num']):'';  
                                    break;
                                default:
                                    # code...
                                    break;
                            }
                            
                        }
                    }
                    //$all += $returnData[$key]['总计'];
                }

            }

        }else if($data['data_type'] == 'orderJunjia' || $data['data_type'] == 'total_money' || $data['data_type'] == 'ROI'){
            
            if($data['channel_id']){
                $order = db('crm_c_customerinfo')->alias('c')
                ->join('c_customerorder o','c.customer_number = o.crm_customer_number','left')
                ->where($where)
                ->where($sql)
                ->where('customer_channel','in',$channels)
                ->group('customer_channel,s_dj_day')
                ->field($orderField)
                ->select();
            }else{
                $order = db('crm_c_customerinfo')->alias('c')
                ->join('c_customerorder o','c.customer_number = o.crm_customer_number','left')
                ->join('crm_s_channel s','c.customer_channel=s.channel_name','left')
                ->where($where)
                ->where($sql)
                ->where('s.channel_info_name','in',$channels)
                ->group('s.channel_info_name,s_dj_day')
                ->field($orderField.',s.channel_info_name')
                ->select();
            }

            $orderinfo = $order;
            /*foreach ($order as $key => $value) {
                $orderinfo[$value['customer_channel'].''.$value['dj_day']]['channel_info_name'] = $value['channel_info_name'];
                $orderinfo[$value['customer_channel'].''.$value['dj_day']]['customer_channel'] = $value['customer_channel'];
                $orderinfo[$value['customer_channel'].''.$value['dj_day']]['total_money'] = $value['total_money']+$orderinfo[$value['customer_channel'].''.$value['dj_day']]['total_money'];
                $orderinfo[$value['customer_channel'].''.$value['dj_day']]['bargain_money'] = $value['bargain_money']+$orderinfo[$value['customer_channel'].''.$value['dj_day']]['bargain_money'];
                $orderinfo[$value['customer_channel'].''.$value['dj_day']]['cj'] = $value['cj']+$orderinfo[$value['customer_channel'].''.$value['dj_day']]['cj'];
            }*/
           
            //ROI
            if($data['data_type'] == 'ROI'){

                $totalMoney = db('crm_c_customerinfo')->alias('c')
                    ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
                    ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
                    ->where($where)
                    ->field('sum(erp_order.bargain_money) as s_total_money,DATE_FORMAT(erp_order.targetdate,"%d") as s_targetdate,s.channel_name,s.channel_info_name,s.id')
                    ->where('erp_order.orderId is not null')
                    ->where('erp_order.blankoutannal','<>',1)
                    ->where('erp_order.payment_money','>',0)
                    ->where('erp_order.crm_order_type',1)
                    ->where('erp_order.targetdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
                    ->group($group)
                    ->select();

                $costField.= "cost,DATE_FORMAT(cost_date,'%m') as s_cost_date";
                if($data['channel_id']){
                    $cost = db('crm_s_channel_cost')
                        ->where('channel_name','in',$channels)
                        ->group('channel_name,s_cost_date')
                        ->field($costField.',channel_name')
                        ->select();
                }else{
                    $cost = db('crm_s_channel_cost')->alias('c')
                        ->join('crm_s_channel s','c.channel_name=s.channel_name','left')
                        ->where('s.channel_info_name','in',$channels)
                        ->group('s.channel_info_name,s_cost_date')
                        ->field($costField.',s.channel_name,s.channel_info_name')
                        ->select();
                }

                $costArr = $cost;
                /*foreach($cost as $key=>$value){
                    $costArr[$value['channel_name'].''.$value['cost_date']]['channel_info_name'] = $value['channel_info_name'];
                    $costArr[$value['channel_name'].''.$value['cost_date']]['channel_name'] = $value['channel_name'];
                    $costArr[$value['channel_name'].''.$value['cost_date']]['cost'] = $value['cost']+$costArr[$value['channel_name'].''.$value['cost_date']]['cost'];
                    $costArr[$value['channel_name'].''.$value['cost_date']]['cost_date'] = $value['cost_date'];
                }*/

                foreach($totalMoney as $key=>$value){
                    foreach($costArr as $k2=>$v2){
                        if($value['customer_channel'] == $v2['channel_name'] && $value['s_dj_day'] == $v2['s_cost_date']){
                            $totalMoney[$key]['cost'] = $v2['cost'];
                        }
                    }
                }
                $returnData = [];
                foreach($channelData as $key=>$value){
                    $returnData[$key]['channel_info_name'] = $value['channel_info_name'];
                    $channel = $value['channel_info_name'];
                    $info_order = 'channel_info_name';
                    if($data['channel_id']){
                        $returnData[$key]['channel_name'] = $value['channel_name'];
                        $channel = $value['channel_name'];
                        $info_order = 'customer_channel';
                    }
                    foreach ($totalMoney as $k => $v) {
                        if ($channel == $v[$info_order]) {
                            $returnData[$key][$v['s_dj_day']] = $v['s_total_money'] ? round($v['s_total_money']/$v['cost']) : 0;
                            $returnData[$key]['total_money_num'] += $v['s_total_money'] ? $v['s_total_money'] : 0;
                            $returnData[$key]['cost_num'] += $v['cost'] ? $v['cost'] : 0;
                            $returnData[$key]['总计'] += $returnData[$key]['total_money_num'] ? round($returnData[$key]['total_money_num']/$returnData[$key]['cost_num']) : 0;
                        }
                    }
                }

            }elseif($data['data_type'] == 'orderJunjia'){ //成交均价
                $returnData = [];
                // $arr = [];
                foreach ($channelData as $key => $value) {
                    $returnData[$key]['channel_info_name'] = $value['channel_info_name'];
                     $channel = $value['channel_info_name'];
                    $cost_channel = 'channel_info_name';
                    if($data['channel_id']){
                        $returnData[$key]['channel_name'] = $value['channel_name'];
                        $channel = $value['channel_name'];
                        $cost_channel = 'channel_name';
                    }
                    foreach ($costArr as $k => $v) {
                        if ($channel == $v[$cost_channel]) {
                            $returnData[$key][$v['s_cost_date']] = $v['bargain_money'] ? round($v['bargain_money']/$v['cj']) : 0;
                            $returnData[$key]['bargain_money_num'] += $v['bargain_money'] ? $v['bargain_money'] : 0;
                            $returnData[$key]['cj_num'] += $v['cj'] ? $v['cj'] : 0;
                            $returnData[$key]['总计'] += $returnData[$key]['bargain_money_num'] ? round($returnData[$key]['bargain_money_num']/$returnData[$key]['cj_num']) : 0;
                        }
                    }
                    //$all += $returnData[$key]['总计'];
                }
            }else{  //营业额

                $totalMoney = db('crm_c_customerinfo')->alias('c')
                    ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
                    ->join('c_customerorder erp_order', 'erp_order.crm_customer_id=c.id','left')
                    ->where($where)
                    ->field('sum(erp_order.bargain_money) as s_total_money,DATE_FORMAT(erp_order.targetdate,"%d") as s_targetdate,s.channel_name,s.channel_info_name,s.id')
                    ->where('erp_order.orderId is not null')
                    ->where('erp_order.blankoutannal','<>',1)
                    ->where('erp_order.payment_money','>',0)
                    ->where('erp_order.crm_order_type',1)
                    ->where('erp_order.targetdate', 'between', [date('Ymd',strtotime($start)), date('Ymd',strtotime($end))])
                    ->group($group)
                    ->select();

                $returnData = [];
                // $arr = [];
                foreach ($channelData as $key => $value) {
                    $returnData[$key]['channel_info_name'] = $value['channel_info_name'];
                    $channel = $value['channel_info_name'];
                    $cost_channel = 'channel_info_name';
                    if($data['channel_id']){
                        $channel = $value['channel_name'];
                        $returnData[$key]['channel_name'] = $value['channel_name'];
                        $cost_channel = 'channel_name';
                    }
                    foreach ($totalMoney as $k => $v) {
                        if ($channel == $v[$cost_channel]) {
                            $returnData[$key][$v['s_cost_date']] = $v['s_total_money'] ? $v['s_total_money'] : 0;
                            $returnData[$key]['总计'] += $v['s_total_money']?$v['s_total_money']:0;
                        }
                    }
                    //$all += $returnData[$key]['总计'];
                }
            }

        }else{

            $returnData = [];
            // 数据
            if($data['channel_id']){
                $info = db('crm_c_customerinfo')
                ->where($where)
                ->where($sql)
                ->where('customer_channel','in',$channels)
                ->group('customer_channel,s_dj_day')
                ->field($field)
                ->select();
                //$returnData['infosql'] = db()->getLastSql();
            }else{
                $info = db('crm_c_customerinfo')->alias('i')
                ->join('crm_s_channel c','i.customer_channel=c.channel_name','left')
                ->where($where)
                ->where($sql)
                ->where('c.channel_info_name','in',$channels)
                ->group('c.channel_info_name,s_dj_day')
                ->field($field.',c.channel_info_name')
                ->select();
            }
            /*$infoArr = [];
            foreach($info as $key => $value){
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['channel_info_name'] = $value['channel_info_name'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['customer_channel'] = $value['customer_channel'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['dj_day'] = $value['s_dj_day'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['num'] = $value['num']+$infoArr[$value['customer_channel'].''.$value['s_dj_day']]['num'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['mkz'] = $value['mkz']+$infoArr[$value['customer_channel'].''.$value['s_dj_day']]['mkz'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['yx'] = $value['yx']+$infoArr[$value['customer_channel'].''.$value['s_dj_day']]['yx'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['wx'] = $value['wx']+$infoArr[$value['customer_channel'].''.$value['s_dj_day']]['wx'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['dd'] = $value['dd']+$infoArr[$value['customer_channel'].''.$value['s_dj_day']]['dd'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['rd'] = $value['rd']+$infoArr[$value['customer_channel'].''.$value['s_dj_day']]['rd'];
                $infoArr[$value['customer_channel'].''.$value['s_dj_day']]['cj'] = $value['cj']+$infoArr[$value['customer_channel'].''.$value['s_dj_day']]['cj'];
            }*/

            // var_dump($info);die;
    
            $arr = [];
            foreach ($channelData as $key => $value) {
                // var_dump($value);
                $returnData[$key]['channel_info_name'] = $value['channel_info_name'];
                $channel = 'channel_info_name';
                $channelinfo = 'channel_info_name';
                if($data['channel_id']){
                    $returnData[$key]['channel_name'] = $value['channel_name'];
                    $channel = 'channel_name';
                    $channelinfo = 'customer_channel';
                }
                // $returnData[$key]['总计'] = 0;
                foreach ($info as $k => $v) {
                    if ($value[$channel] == $v[$channelinfo]) {
                        switch ($data['data_type']) {
                            case 'mkz':
                            case 'valid':
                            case 'unValid':
                            case 'dd':
                            case 'yy':
                            case 'jd':
                            case 'order':
                                $returnData[$key][$v['s_dj_day']] = $v['num'] ? $v['num'] : 0;
                                $returnData[$key]['总计'] += $v['num'] ? $v['num'] : 0;
                                break;
                            case 'validRate':
                                $returnData[$key][$v['s_dj_day']] = $v['yx']?sprintf('%.2f',$v['yx']/$v['mkz']*100).'%':'';
                                $returnData[$key]['mkz_num'] += $v['mkz'] ? $v['mkz'] : 0;
                                $returnData[$key]['yx_num'] += $v['yx'] ? $v['yx'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['yx_num']?sprintf('%.2f',$returnData[$key]['yx_num']/$returnData[$key]['mkz_num']*100).'%':'';
                                break;
                            case 'unValidRate':
                                $returnData[$key][$v['s_dj_day']] = $v['wx']?sprintf('%.2f',$v['wx']/$v['mkz']*100).'%':'';
                                $returnData[$key]['mkz_num'] += $v['mkz'] ? $v['mkz'] : 0;
                                $returnData[$key]['wx_num'] += $v['wx'] ? $v['wx'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['wx_num']?sprintf('%.2f',$returnData[$key]['wx_num']/$returnData[$key]['mkz_num']*100).'%':'';
                                break;
                            case 'ddRate':
                                $returnData[$key][$v['s_dj_day']] = $v['dd']?sprintf('%.2f',$v['dd']/$v['mkz']*100).'%':'';
                                $returnData[$key]['mkz_num'] += $v['mkz'] ? $v['mkz'] : 0;
                                $returnData[$key]['dd_num'] += $v['dd'] ? $v['dd'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['dd_num']?sprintf('%.2f',$returnData[$key]['dd_num']/$returnData[$key]['mkz_num']*100).'%':'';
                                break;
                            case 'mkzJdRate':
                                $returnData[$key][$v['s_dj_day']] = $v['rd']?sprintf('%.2f',$v['rd']/$v['mkz']*100).'%':'';
                                $returnData[$key]['mkz_num'] += $v['mkz'] ? $v['mkz'] : 0;
                                $returnData[$key]['rd_num'] += $v['rd'] ? $v['rd'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['rd_num']?sprintf('%.2f',$returnData[$key]['rd_num']/$returnData[$key]['mkz_num']*100).'%':'';
                                break;
                            case 'validJdRate':
                                $returnData[$key][$v['s_dj_day']] = $v['rd']?sprintf('%.2f',$v['rd']/$v['yx']*100).'%':'';
                                $returnData[$key]['yx_num'] += $v['yx'] ? $v['yx'] : 0;
                                $returnData[$key]['rd_num'] += $v['rd'] ? $v['rd'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['rd_num']?sprintf('%.2f',$returnData[$key]['rd_num']/$returnData[$key]['yx_num']*100).'%':'';
                                break;
                            case 'jdOrderRate':
                                $returnData[$key][$v['s_dj_day']] = $orderNum[$v['s_dj_day']]['num']?sprintf('%.2f',$orderNum[$v['s_dj_day']]['num']/$v['rd']*100).'%':'';
                                $returnData[$key]['cj_num'] += $orderNum[$v['s_dj_day']]['num'] ? $orderNum[$v['s_dj_day']]['num'] : 0;
                                $returnData[$key]['rd_num'] += $v['rd'] ? $v['rd'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['cj_num']?sprintf('%.2f',$returnData[$key]['cj_num']/$returnData[$key]['rd_num']*100).'%':'';
                                break;
                            case 'mkzOrderRate':
                                $returnData[$key][$v['s_dj_day']] = $orderNum[$v['s_dj_day']]['num']?sprintf('%.2f',$orderNum[$v['s_dj_day']]['num']/$v['mkz']*100).'%':'';
                                $returnData[$key]['cj_num'] += $orderNum[$v['s_dj_day']]['num'] ? $orderNum[$v['s_dj_day']]['num']: 0;
                                $returnData[$key]['mkz_num'] += $v['mkz'] ? $v['mkz'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['cj_num']?sprintf('%.2f',$returnData[$key]['cj_num']/$returnData[$key]['mkz_num']*100).'%':'';
                                break;
                            case 'validOrderRate':
                                $returnData[$key][$v['s_dj_day']] = $orderNum[$v['s_dj_day']]['num']?sprintf('%.2f',$orderNum[$v['s_dj_day']]['num']/$v['yx']*100).'%':'';
                                $returnData[$key]['cj_num'] += $orderNum[$v['s_dj_day']]['num'] ? $orderNum[$v['s_dj_day']]['num'] : 0;
                                $returnData[$key]['yx_num'] += $v['yx'] ? $v['yx'] : 0;
                                $returnData[$key]['总计'] = $returnData[$key]['cj_num']?sprintf('%.2f',$returnData[$key]['cj_num']/$returnData[$key]['yx_num']*100).'%':'';
                                break;
                            default:
                                $sql = 'dj_day between "'.$start.'" and "'.$end.'"';
                                break;
                        }

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

        array_unshift($returnData, $arr);


        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($channel)+1;
        $return['data'] = $returnData;
        return json($return);
    }
    public function show(){
        $code = $this->getCode();
        /*
        $did = strtolower(input('param.did'));
        $info = db('crm_s_report_form')->alias('f')->join('crm_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();*/
        $menu_code = strtolower(input('param.menu_code'));
        $info = db('crm_s_report_form')->alias('f')->join('crm_s_report_detail d','d.form_id=f.id','left')->where('f.code',$menu_code)->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name,f.report_name')->find();
        $shop_code = session('shop_code');

        //来源渠道
        $channel = getChannelJson($shop_code);
        $this->assign("channel", json_encode($channel));

        //渠道一二级
        $channel2 = getChannelJson12($shop_code);
        $this->assign("channel2",json_encode($channel2));
        
        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $this->assign('title', session('shop_name').'-'.$info['report_name']);

        $this->assign('did', $info['id']);
        $this->assign('today',date('Y-m-d'));

        //表格隐藏列
        $this->assign('hiddenColumns',explode('|',cookie('report_cd_year_customer_hidden')));

        //可操作店
        $mc = new \app\common\lib\mcInterface;
        $shopList = $mc->getWorkShop(['staff_id'=>session('staff_id'),'brandclass_id'=>session('brandclass_id')]);
        $this->assign('shopList',$shopList);
        $this->assign("shop_code", $info['shop_code']?$info['shop_code']:$shop_code);

        $data['type'] = 1;
        $data['shop_code'] = $shop_code;
        $province = controller("Customer", "lib")->address($data);
        $this->assign('province', $province);
        
        $yestoday = date('Y',time());

        $this->assign('yestoday',$yestoday);
        return $this->fetch($display);
    }
}