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

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

    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 00:00:00', strtotime($data['start']));
        $end = date('Y-m-d 23:59:59', strtotime($data['end']));
        $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'=>'请选择店面');
        }

        $where['is_status'] = 1;
        $where['c_from'] = 1;
        $where['c.add_shop_code'] = $shop_code;
        $where['dj_day'] = ['between time',[$start, $end]];

        $whereCost['shop_code'] = $shop_code;
        $whereCost['cost_date'] = ['between time',[$start, $end]];

        // 消费类型
        if((int)$data['consultation_type_id']){
            $where['consultation_type_id'] = (int)$data['consultation_type_id'];
        }
        $group = 'channel_info_name';
        // 渠道
        if($data['customer_channel']) {
            $channelNames = explode(',',$data['customer_channel']);

            $channel_data = db('crm_s_channel')->where('channel_name', 'in' ,$channelNames)->order("order_sort")->select();
            $channel_datas = array_column($channel_data, 'channel_info_name');
            // asort($channel_datas);
            $channels = [];
            foreach($channel_datas as $key=>$value){
                $channels[] = $channel_data[$key];
            }

            $group = 'channel_name';
        }elseif($data['erji_channel_id']){
            // 来源渠道
            $channelNames = explode(',',$data['erji_channel_id']);
            $channel_data = db('crm_s_channel')->where("channel_info_name","in",$channelNames)->order("order_sort")->select();
            $channel_datas = array_column($channel_data, 'channel_info_name');
            // asort($channel_datas);
            $channel_datas = array_unique($channel_datas);
            $channels = [];
            foreach($channel_datas as $key=>$value){
                $channels[] = $channel_data[$key];
            }
        }else{
            // 来源渠道
            $channelData = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channel_datas = array_column($channelData, 'channel_info_name');
            // asort($channel_datas);
            $channels = [];
            foreach($channel_datas as $key=>$value){
                $channels[] = $channelData[$key];
            }
            $channelNames = array_column($channelData,'channel_info_name');
        }
        if($data['customer_channel']){
            $where['customer_channel'] = ['in',$channelNames];
            $whereCost['channel_name'] = ['in',$channelNames];
            $channel_type = 'channel_name';
        }else{
            $where['channel_info_name'] = ['in',$channelNames];
            $whereCost['channel_info_name'] = ['in',$channelNames];
            $channel_type = 'channel_info_name';
        }
        // crm省 列表
        $province = controller("Customer", "lib")->address(['type'=>1,'shop_code'=>$shop_code]);
        $province = array_column($province,'name');
        //$province = db('crm_c_customerinfo')->where($where)->where('province <> "" and province is not null')->group('province')->column('province');

        $search_key = $data['search_key'];
        switch($search_key){
            case 'tel':
                $sql = 'customer_tel is not null';
                break;
            case 'wx':
                $sql = 'customer_wechat is not null';
                break;
            case 'qq':
                $sql = 'customer_qq is not null';
                break;
            case 'tel-wx':
                $sql = 'customer_tel is not null and customer_wechat is not null';
                break;
            case 'nottel':
                $sql = 'customer_tel is null';
                break;
            case 'notwx':
                $sql = 'customer_wechat is null';
                break;
            case 'onlytel':
                $sql = 'customer_tel is not null and customer_wechat is null and customer_qq is null';
                break;
            case 'onlywx':
                $sql = 'customer_wechat is not null and customer_tel is null and customer_qq is null';
                break;
            case 'onlyqq':
                $sql = 'customer_qq is not null and customer_wechat is null and customer_tel is null';
                break;
        }

        $data_type = $data['data_type'];
        switch($data_type){
            case '客资量':
                $list = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);
                break;
            case '待定量':
                $where['is_valid'] = 2;
                $list = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);
                break;
            case '有效量':
                $where['is_valid'] = ['in',[1,5]];
                $list = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);
                break;
            case '无效量':
                $where['is_valid'] = ['in',[3,4]];
                $list = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);
                break;
            case '入店量':
                $where['is_intostore'] = ['in',[2,3,5]];
                $list = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);
                break;
            case '成交量':
                $where['is_success'] = 1;
                $list = $this->getData($this->getCj($where,$sql,$group),$channels,$province,1,$channel_type);
                break;
            case '预约量':
                $list = $this->getData($this->getPc($where,$sql,$group),$channels,$province,1,$channel_type);
                break;
            case '花费':
                $list = $this->getData($this->getCost($whereCost,$sql),$channels,$province,2,$channel_type);
                break;
            case '毛客资入店率': //入店/毛客资
                $cus = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $where['is_intostore'] = ['in',[2,3,5]];
                $rd = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $list = $this->getRate($cus,$rd,$province);
                break;
            case '有效客资入店率': //入店/有效

                $where['is_valid'] = 1;
                $yx = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $where['is_intostore'] = ['in',[2,3,5]];
                $rd = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $list = $this->getRate($yx,$rd,$province);
                break;

            case '入店成交率'://成交数/入店数
                $where['is_intostore'] = ['in',[2,3,5]];
                $rd = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                unset($where['is_intostore']);
                $where['is_success'] = 1;
                $cj = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $list = $this->getRate($rd,$cj,$province);
                break;

            case '毛客资成交率'://成交数/毛客资数

                $cus = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $where['is_success'] = 1;
                $cj = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $list = $this->getRate($cus,$cj,$province);
                break;

            case '有效客资成交率': //成交数/有效客资数

                $where['is_valid'] = 1;
                $cus = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                unset($where['is_valid']);
                $where['is_success'] = 1;
                $cj = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $list = $this->getRate($cus,$cj,$province);
                break;

            case '毛客资成本': //花费/毛客资

                $cus = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $cost = $this->getData($this->getCost($whereCost),$channels,$province,2,$channel_type);

                $list = $this->getMoney($cus,$cost,$province);
                break;

            case '入店成本': // 花费/入店数
                $where['is_intostore'] = ['in',[2,3,5]];
                $cus = $this->getData($this->getCustomer($where,$sql,$group),$channels,$province,1,$channel_type);

                $cost = $this->getData($this->getCost($whereCost),$channels,$province,2,$channel_type);

                $list = $this->getMoney($cus,$cost,$province);
                break;

            case '成交成本':  //花费/成交数
                $where['is_success'] = 1;
                $cus = $this->getData($this->getCj($where,$sql,$group),$channels,$province,1,$channel_type);

                $cost = $this->getData($this->getCost($whereCost),$channels,$province,2,$channel_type);

                $list = $this->getMoney($cus,$cost,$province);
                break;

            case '成交均价':  //总钱数/成交数
                $money = $this->getData($this->getOrderMoney($where,$group),$channels,$province,2,$channel_type);

                $where['is_success'] = 1;
                $cus = $this->getData($this->getCj($where,$sql,$group),$channels,$province,1,$channel_type);

                $list = $this->getMoney($cus,$money,$province);
                break;

            case '营业额':

                $list = $this->getData($this->getOrderMoney($where,$group),$channels,$province,2,$channel_type);
                break;

            case 'ROI'://营业额/花费

                //营业额
                $orderMoney = $this->getData($this->getOrderMoney($where,$group),$channels,$province,2,$channel_type);

                //花费
                $cost = $this->getData($this->getCost($whereCost),$channels,$province,2,$channel_type);

                $list = $this->getMoney($cost,$orderMoney,$province);
                break;

        }


        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($list);
        $return['data'] = $list;

        return json($return);

    }

    //成交量
    public function getCj($where,$sql,$group){
        $where['erp_order.targetdate'] = $where['dj_day'];
        unset($where['dj_day']);
        $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')
            // ->join('c_customerorder erp_order', 'erp_order.crm_customer_number=c.customer_number')
            ->where($where)->where($sql)
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            // ->where('erp_order.yixiaoshishou','>',0)
            ->where('erp_order.payment_money','>',0)
            ->where('erp_order.crm_order_type',1)
            // ->where('c.dj_day', 'between time', [$start, $end])
            ->group($group)
            ->field('customer_channel_id as channel_id,customer_channel as channel_name,province_id,province,COUNT(c.id)AS num')
            ->select();
        return $all;  
    }

    /*
     * 求率
     * data1  分母
     * data2  分子
     */
    public function getRate($data1,$data2,$province,$channel_type){
        $list = [];

        foreach($data1 as $k => $v){
            foreach($data2 as $kk => $vv){
                if($v[$channel_type] == $vv[$channel_type]) {
                    foreach($province as $key => $val) {
                        $list[$k][$channel_type] = $v[$channel_type];
                        $list[$k][$val] = sprintf('%.2f', $vv[$val] / $v[$val] * 100) . '%';
                    }
                    $list[$k]['其他'] = sprintf('%.2f', $vv['其他'] / $v['其他'] * 100) . '%';
                    $list[$k]['sum'] = sprintf('%.2f', $vv['sum'] / $v['sum'] * 100) . '%';
                }
            }
        }

        return $list;
    }

    /*
     * 求成本、均价
     * data1  分母
     * data2  分子
     */
    public function getMoney($data1,$data2,$province){
        $list = [];

        foreach($data1 as $k => $v){
            foreach($data2 as $kk => $vv){
                if($v['channel_name'] == $vv['channel_name']) {
                    foreach($province as $key => $val) {
                        $list[$k]['channel_name'] = $v['channel_name'];
                        $list[$k][$val] = sprintf('%.2f', $vv[$val] / $v[$val]);
                    }

                    $list[$k]['其他'] = sprintf('%.2f', $vv['其他'] / $v['其他']);
                    $list[$k]['sum'] = sprintf('%.2f', $vv['sum'] / $v['sum']);
                }
            }
        }

        return $list;
    }

    //客资数据
    public function getCustomer($where,$sql,$group){
        $all = db('crm_c_customerinfo')->alias('c')
            ->where($where)
            ->where($sql)
            ->join('crm_s_channel s','c.customer_channel=s.channel_name','left')
            ->field('customer_channel_id as channel_id,customer_channel as channel_name,province_id,province,COUNT(1)AS num,channel_info_name')
            ->group($group.',province')
            ->select();
            // echo db()->getLastSql();die;
        return $all;
    }

    //预约数据
    public function getPc($where,$sql,$group){

        /*$all = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($where)
            ->where($sql)
            ->where('pc.id is not null')->fetchSql(true)
            ->field('customer_channel_id as channel_id,customer_channel as channel_name,province_id,province,COUNT(1)AS num')
            ->group($group.',province')
            ->select();*/

        $all = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)->where($sql)
            ->where('c.sign_name','已预约')
            ->field('customer_channel_id as channel_id,customer_channel as channel_name,province_id,province,COUNT(c.id)AS num')
            ->group($group)
            ->select();
        return $all;
    }

    //花费
    public function getCost($whereCost){

        $all = db('crm_s_channel_cost')
            ->where($whereCost)
            ->field('channel_id,channel_name,province_name as province,sum(cost)AS num')
            ->group('channel_name,province_name')
            ->select();

        return $all;
    }

    //营业额
    public function getOrderMoney($where,$group){
        $where['erp_order.targetdate'] = $where['dj_day'];
        unset($where['dj_day']);
        $all = db('crm_c_customerinfo')
            ->alias('c')
            ->join('c_customerorder erp_order','c.customer_number = erp_order.crm_customer_number')
            ->join('crm_s_channel s','s.channel_name=c.customer_channel','left')
            ->where($where)
            ->where('erp_order.orderId is not null')
            ->where('erp_order.blankoutannal','<>',1)
            // ->where('erp_order.yixiaoshishou','>',0)
            ->where('erp_order.payment_money','>',0)
            ->where('erp_order.crm_order_type',1)
            ->field('customer_channel_id as channel_id,customer_channel as channel_name,province_id,province,sum(erp_order.bargain_money)AS num,s.channel_info_name')
            ->group($group,',province')
            ->select();
        return $all;
    }

    //获取数据格式
    public function getData($all,$channels,$province,$null_type,$channel_type){

        //重置数据格式
        $list = [];
        $qt = [];//用于累计省为空的情况 总和
     
        foreach($all as $k => $v){
            foreach($channels as $key => $val){
                if($val[$channel_type] == $v[$channel_type]){
                    $list[$v[$channel_type]]['channel_info_name'] = $val['channel_info_name'];
                }
            }
            if($channel_type == 'channel_name'){
                $list[$v[$channel_type]]['channel_name'] = $v['channel_name'] ? $v['channel_name'] : '未填写渠道';    
            }
            
            $qt[$v[$channel_type].$v['province']] += $v['num'];

            if(!$v['province']){
                $list[$v[$channel_type]]['其他'] = $qt[$v[$channel_type].$v['province']];
            }else{
                $list[$v[$channel_type]][$v['province']] = $qt[$v[$channel_type].$v['province']];
            }
        }
    
        //所有省中 给每个渠道没有数据的省 展示0
        foreach($list as $k => $v){
            foreach($province as $kk => $vv){
                if($v[$vv] == 0){
                    $list[$k][$vv] = $null_type == 1 ? 0 : '0.00';
                }
            }
        }
        //把没数据的渠道 展示0
        foreach($channels as $key => $val){
            if(count($list[$val[$channel_type]]) == 0 && $province){
                foreach($province as $k => $v){
                    $list[$val[$channel_type]]['channel_info_name'] = $val['channel_info_name'];
                    if($channel_type == 'customer_channel'){
                        $list[$val[$channel_type]]['channel_name'] = $val['channel_name'];
                    }
                    $list[$val[$channel_type]][$v] = $null_type == 1 ? 0 : '0.00';
                }
                $list[$val[$channel_type]]['其他'] = $null_type == 1 ? 0 : '0.00';
            }
        }
        //合计行
        //每个省 每列的合计
        $all = [];
        $all['channel_name'] = '合计';
        foreach($province as $v){
            $arr = array_column($list,$v);
            $num = array_sum($arr);
            if($num == 0){
                $num = $null_type == 1 ? 0 : '0.00';
            }
            $all[$v] = $num;

        }
        //’其他‘合计
        $arr = array_column($list,'其他');
        $num = array_sum($arr);
        if($num == 0){
            $num = $null_type == 1 ? 0 : '0.00';
        }
        $all['其他'] = $num;

        array_push($list,$all);

        //合计列
        //每一行的合计  渠道一行多个省合计
        foreach($list as $key => $val){
            $array = $val;
            unset($array['channel_name']);

            $num = array_sum($array);
            if($num == 0){
                $num = $null_type == 1 ? 0 : '0.00';
            }
            $list[$key]['sum'] = $num ;

        }

        return $list;
    }

    public function show(){
        /*
        $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')->find();

        $brandclass_id = session('brandclass_id');
        $brandclass = session('brandclass');
        $shop_code = session('shop_code');

        //消费类型
        $consumptionType = controller("Customer", "lib")->consumptionType(['brandclass_id'=>$brandclass_id]);
        $this->assign("consumptionType", $consumptionType);

        //渠道一二级
        $channel2 = getChannelJson12($shop_code);
        $this->assign("channel2",json_encode($channel2));

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

        //店
        $mcInterface = new mcInterface();
        $shop = $mcInterface->getWorkShop(['staff_id'=>session('staff_id'),'brandclass_id'=>session('brandclass_id')]);
        $this->assign("shop", $shop);
        $this->assign("shop_code", $info['shop_code']?$info['shop_code']:$shop_code);

        //报表名称
        $code = $this->getCode();
        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $title = db('crm_s_report_form')->where('code', $code)->value('report_name');
        $this->assign('title', $title);

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

        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}