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

class Ynk_channel_data extends Report {

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

    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['customer_type'] = 1;
        $where['c.shop_code'] = $shop_code;
        // $whereN['add_shop_code'] = $shop_code;

        /*//不在追踪，2 否
        // $where['is_loss'] = 2;
        // 消费类型
        if ($data['consultation_type']) {
            $where['c.consultation_type'] = ['in',$data['consultation_type']];
        }

        // 地域id，拼接where条件
        if ($data['province']) {
            $where['province_id'] = (int)$data['province'];
            $whereC['province_id'] = (int)$data['province'];
            $whereN['province_id'] = (int)$data['province'];
        }

        if ($data['city']) {
            $where['city_id'] = (int)$data['city'];
            $whereC['city_id'] = (int)$data['city'];
            $whereN['city_id'] = (int)$data['city'];
        }*/

        /* if ($data['customer_channel']) {
            $channelNames = explode(',',$data['customer_channel']);
            $channels = db('crm_s_channel')->where('channel_name', 'in' ,$channelNames)->select();
        } else {
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
        }*/

        $cKey = 's.channel_name';
        $group = 's.channel_name';
        $costKey = 'c.channel_name';
        $channel_key = 'channel_name';

        if ($data['customer_channel']) {
            $channels = explode(',',$data['customer_channel']);
            $channel_data = db('crm_s_channel')->where("channel_name","in",$data['customer_channel'])->order("order_sort")->select();
            $channel_datas = array_column($channel_data, 'channel_name');
            // asort($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channel_data[$key];
            }
            $cKey = 's.channel_name';
            $group = 's.channel_name';
            $costKey = 'c.channel_name';
            $channel_key = 'channel_name';
        }elseif($data['yiji_channel_id']){
            // 来源渠道
            $channels = explode(',',$data['yiji_channel_id']);
            $channel_data = db('crm_s_channel')->where("channel_class_name","in",$data['yiji_channel_id'])->order("order_sort")->select();
            $channel_datas = array_column($channel_data, 'channel_name');
            // asort($channel_datas);
            $channel_datas = array_unique($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channel_data[$key];
            }
        }else{
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channel_datas = array_column($channels, 'channel_name');
            // asort($channel_datas);
            $channel_datas = array_unique($channel_datas);
            $channelData = [];
            foreach($channel_datas as $key=>$value){
                $channelData[] = $channels[$key];
            }
            // $channels = array_column($channels,'channel_info_name');
        }
        // print_r($channelData);exit;

        // 录入客资量 √
        $allCustomer = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            ->where('c.dj_day', 'between', [$start, $end])
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id,c.sign_id,c.sign_name', $cKey);
        $return['mkz'] = db()->getLastSql();
        //预约成功
        $yyNum = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.sign_name','预约成功')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_yyNum'] = db()->getLastSql();
        //待跟进 √
        $daigengjin = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.sign_name','待跟进')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_daigengjin'] = db()->getLastSql();

        //无人接听 √
        $wurenjt = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.sign_name','无人接听')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_wurenjt'] = db()->getLastSql();

        //忙音 √
        $mangyin = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.sign_name','忙音')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_mangyin'] = db()->getLastSql();

        //停机 √
        $tingji = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.sign_name','停机')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_tingji'] = db()->getLastSql();

        //未报名/未参加
        $weibm = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','未报名/未参加')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_weibm'] = db()->getLastSql();
        
        //取消拍摄
        $quxiaops = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','取消拍摄')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_quxiaops'] = db()->getLastSql();

        //跟进3天未预约
        $gengjin3 = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','跟进3天未预约')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_gengjin3'] = db()->getLastSql();

        //3天以上未联系上/拒接
        $t3no = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','3天以上未联系上/拒接')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_t3no'] = db()->getLastSql();

        //重复订单
        $cfdingdan = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','重复订单')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_cfdingdan'] = db()->getLastSql();

        //超龄
        $chaoling = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','超龄')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_chaoling'] = db()->getLastSql();

        //空号/错号
        $konghao = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','空号/错号')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_konghao'] = db()->getLastSql();

        //其他
        $qita = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('c.dj_day', 'between', [$start, $end])
            ->where('c.loss_cause','其他')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name,s.channel_name,s.id', $cKey);
        $return['sql_qita'] = db()->getLastSql();

        $returnData = [];
        $channelCostNum = 0;
        $totalMoneyNum = 0;
        $wechatCustomerNum = 0;
        $arr = [];
        $arr['channel_name'] = '总计';
        foreach ($channelData as $k => $v) {
            //$returnData[$k]['id'] = $v['id'];
            
            $returnData[$k]['channel_class_name'] = $v['channel_class_name'];
            $returnData[$k]['channel_name'] = $v['channel_name'];

            $returnData[$k]['lrCustomer'] = $allCustomer[$v[$channel_key]]['num'] ? $allCustomer[$v[$channel_key]]['num'] : 0;//数据总量

            $returnData[$k]['yyNum'] = $yyNum[$v[$channel_key]]['num'] ? $yyNum[$v[$channel_key]]['num'] : 0;//预约
            $returnData[$k]['daigengjin'] = $daigengjin[$v[$channel_key]]['num'] ? $daigengjin[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['wurenjt'] = $wurenjt[$v[$channel_key]]['num'] ? $wurenjt[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['mangyin'] = $mangyin[$v[$channel_key]]['num'] ? $mangyin[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['tingji'] = $tingji[$v[$channel_key]]['num'] ? $tingji[$v[$channel_key]]['num'] : 0;

            $returnData[$k]['weibm'] = $weibm[$v[$channel_key]]['num'] ? $weibm[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['quxiaops'] = $quxiaops[$v[$channel_key]]['num'] ? $quxiaops[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['gengjin3'] = $gengjin3[$v[$channel_key]]['num'] ? $gengjin3[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['t3no'] = $t3no[$v[$channel_key]]['num'] ? $t3no[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['cfdingdan'] = $cfdingdan[$v[$channel_key]]['num'] ? $cfdingdan[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['chaoling'] = $chaoling[$v[$channel_key]]['num'] ? $chaoling[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['konghao'] = $konghao[$v[$channel_key]]['num'] ? $konghao[$v[$channel_key]]['num'] : 0;
            $returnData[$k]['qita'] = $qita[$v[$channel_key]]['num'] ? $qita[$v[$channel_key]]['num'] : 0;

            //名单有效率 =（数据总量-重复订单-超龄-空号/错号）/三级渠道量数据总量 
            $returnData[$k]['md_yxl'] = (sprintf('%.2f',($returnData[$k]['lrCustomer'] - $returnData[$k]['cfdingdan'] - $returnData[$k]['chaoling'] - $returnData[$k]['konghao']) / $returnData[$k]['lrCustomer'])*100)."%";

            //转化率 = 预约成功/名单有效量【数据总量-重复订单-超龄-错号】
            $returnData[$k]['zhl'] = (sprintf('%.2f',$returnData[$k]['yyNum']/ ($returnData[$k]['lrCustomer'] - $returnData[$k]['cfdingdan'] - $returnData[$k]['chaoling'] - $returnData[$k]['konghao']))*100)."%";

            $arr['lrCustomer']  += $allCustomer[$v[$channel_key]]['num'];    //录入总计
            $arr['yyNum']    += $returnData[$k]['yyNum'];    //预约总计
            $arr['daigengjin']    += $returnData[$k]['daigengjin'];
            $arr['wurenjt']    += $returnData[$k]['wurenjt'];
            $arr['mangyin']    += $returnData[$k]['mangyin'];
            $arr['tingji']    += $returnData[$k]['tingji'];
            $arr['weibm']    += $returnData[$k]['weibm'];
            $arr['quxiaops']    += $returnData[$k]['quxiaops'];
            $arr['gengjin3']    += $returnData[$k]['gengjin3'];
            $arr['t3no']    += $returnData[$k]['t3no'];
            $arr['cfdingdan']    += $returnData[$k]['cfdingdan'];
            $arr['chaoling']    += $returnData[$k]['chaoling'];
            $arr['konghao']    += $returnData[$k]['konghao'];
            $arr['qita']    += $returnData[$k]['qita'];

        }

        $arr['md_yxl'] = (sprintf('%.2f',($arr['lrCustomer'] - $arr['cfdingdan'] - $arr['chaoling'] - $arr['konghao']) / $arr['lrCustomer'])*100)."%";

        $arr['zhl'] = (sprintf('%.2f',$arr['yyNum']/ ($arr['lrCustomer'] - $arr['cfdingdan'] - $arr['chaoling'] - $arr['konghao']))*100)."%";

        array_push($returnData,$arr);
        // print_r($returnData);die;
        // echo db()->getLastSql();die;

        if($data['need'] == 1){
            $title = ['一级渠道','三级渠道','数据总量','预约成功','待跟进','无人接听','忙音','停机','未报名/未参加','取消拍摄','跟进3天未预约','3天以上未联系上/拒接','重复订单','超龄','空号','其他','名单有效率','转化率'];
      
            $code = $this->getCode();
            $name = db('crm_s_report_form')->where('code', $code)->value('report_name');
            $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
            $file = $shopInfo[0]['shop_name'].'-'.$name.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        }
        $return['orderSql'] = $orderSql;
        $return['totalSql'] = $totalSql;
        $return['moneySql'] = $moneySql;

        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($returnData);
        $return['data'] = $returnData;
        return json($return);
    }

    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();
        // print_r($info);exit;
        //来源渠道
        $channel = getChannelJson($info['shop_code']);
        $this->assign("channel", json_encode($channel));

        //渠道一级
        $channel1 = getChannelJson1($info['shop_code']);
        $this->assign("channel1",json_encode($channel1));

        $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');
        // $shopInfo = controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
        $this->assign('title', $title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}