<?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_status_customer extends Report {

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

    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['add_shop_code'] = $shop_code;
        $where['dj_day'] = ['between time',[$start, $end]];

        $sql = $null_sql = 'is_status = 1 and c_from = 1 and add_shop_code = "'.$shop_code.'" and (dj_day between "'.$start.'" and "'.$end.'") ';

        // 消费类型
        if((int)$data['consultation_type_id']){
            $where['consultation_type_id'] = (int)$data['consultation_type_id'];
            $sql .= ' and consultation_type_id = '.(int)$data['consultation_type_id'];
            $null_sql .= ' and consultation_type_id = '.(int)$data['consultation_type_id'];
        }

        // 渠道
        if($data['customer_channel']) {
            $channelNames = explode(',',$data['customer_channel']);

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

            $channelIds = array_column($channels,'id');
            $where['customer_channel_id'] = ['in',$channelIds];

            $sql .= ' and customer_channel_id in ('.implode(',',$channelIds).')';
            $null_sql .= ' and customer_channel_id in ('.implode(',',$channelIds).')';
        }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")->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{
            // 来源渠道
            $channels = controller('crm/Customer', 'lib')->getChannel($shop_code);
            $channel_datas = array_column($channels, 'channel_info_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');
        }


        if($data['dj_staff']) {
            $staffs = explode(',',$data['dj_staff']);
            $djStaff = [];
            foreach($staffs as $v){
                $djStaff[] = '"'.$v.'"';
            }
            $sql .= ' and dj_staff in ('.implode(',',$djStaff).')';
        }

        if($data['sales_staff']) {
            $staff = explode(',',$data['sales_staff']);
            $salesStaff = [];
            foreach($staff as $v){
                $salesStaff[] = '"'.$v.'"';
            }
            $sql .= ' and c.sales_staff in ('.implode(',',$salesStaff).')';
        }

        if($data['customer_channel']){
            $group = 'c.customer_channel';
            $cKey = 'c.customer_channel';

            $allCus = 'select
                customer_channel,
                SUM(is_fp = 2) AS not_fp_num,
                SUM(c_star_day is not null) AS not_sales_num,
                SUM(is_valid in (\'1\',\'5\')) AS valid_num,
                SUM(is_valid in (\'3\',\'4\')) AS invalid_num,
                SUM(is_valid = 2) AS dd_num,
                SUM(is_valid = 5) AS fbh_num,
                SUM(is_valid = 4) AS bh_num,
                SUM(is_fp = 1) AS fp_num,
                SUM(c.sign_name = "已预约") AS yyy_num,
                SUM(c.sign_name = "未到店") AS wdd_num,
                SUM(c.sign_name = "进店未定") AS jdwd_num,
                SUM(c.sign_name = "已进店") AS yjd_num,
                SUM(c.sign_name = "无效到店") AS wxdd_num,
                SUM(c.sign_name = "进店保留") AS jdbl_num,
                SUM(c.sign_name = "邀约流失") AS yyls_num
                from crm_c_customerinfo c
                LEFT JOIN crm_l_pc_log pc
                ON c.id = pc.cid
                WHERE '.$sql.'
                GROUP BY '.$group;

            $allNum = db()->query($allCus);
            $num = [];
            foreach($allNum as $k => $v){
                $num[$v['customer_channel']] = $v;
            }

            // 网付订单量
            $wfOrderNum = db('crm_c_customerinfo')
                ->alias('c')
                ->join('crm_l_pc_log pc','c.id = pc.cid')
                ->join('c_customerorder erp_order', 'erp_order.crm_customer_number=c.customer_number')
                ->where($where)
                ->where('erp_order.orderId is not null')
                ->where('erp_order.crm_order_type',1)
                ->group($group)
                ->column('count(c.id) as num', $cKey);
        }else{
            $group = 's.channel_info_name';
            $cKey = 's.channel_info_name';

            $allCus = 'select
                customer_channel,
                channel_info_name,
                SUM(is_fp = 2) AS not_fp_num,
                SUM(c_star_day is not null) AS not_sales_num,
                SUM(is_valid in (\'1\',\'5\')) AS valid_num,
                SUM(is_valid in (\'3\',\'4\')) AS invalid_num,
                SUM(is_valid = 2) AS dd_num,
                SUM(is_valid = 5) AS fbh_num,
                SUM(is_valid = 4) AS bh_num,
                SUM(is_fp = 1) AS fp_num,
                SUM(c.sign_name = "已预约") AS yyy_num,
                SUM(c.sign_name = "未到店") AS wdd_num,
                SUM(c.sign_name = "进店未定") AS jdwd_num,
                SUM(c.sign_name = "已进店") AS yjd_num,
                SUM(c.sign_name = "无效到店") AS wxdd_num,
                SUM(c.sign_name = "进店保留") AS jdbl_num,
                SUM(c.sign_name = "邀约流失") AS yyls_num
                from crm_c_customerinfo c
                LEFT JOIN crm_l_pc_log pc
                ON c.id = pc.cid
                LEFT JOIN crm_s_channel s
                ON c.customer_channel=s.channel_name
                WHERE '.$sql.'
                GROUP BY '.$group;

            $allNum = db()->query($allCus);
            $num = [];
            foreach($allNum as $k => $v){
                $num[$v['channel_info_name']] = $v;
            }

            // 网付订单量
            $wfOrderNum = db('crm_c_customerinfo')
                ->alias('c')
                ->join('crm_l_pc_log pc','c.id = pc.cid')
                ->join('c_customerorder erp_order', 'erp_order.crm_customer_number=c.customer_number')
                ->join('crm_s_channel s','c.customer_channel=s.channel_info_name')
                ->where($where)
                ->where('erp_order.orderId is not null')
                ->where('erp_order.crm_order_type',1)
                ->group($group)
                ->column('count(c.id) as num', $cKey);
        }


        $returnData = [];

        $all = [];
        $all['channel_name'] = '总计';

        foreach ($channelData as $k => $v) {
            $num_key =$v['channel_info_name'];
            $returnData[$k]['channel_info_name'] = $v['channel_info_name'];
            if($data['customer_channel']){
                $num_key = $v['channel_name'];    
                $returnData[$k]['channel_name'] = $v['channel_name'];    
            }
            $returnData[$k]['not_fp_num'] = $num[$num_key]['not_fp_num'] ? $num[$num_key]['not_fp_num'] : 0;
            $returnData[$k]['not_sales_num'] = $num[$num_key]['not_sales_num'] ? $num[$num_key]['not_sales_num'] : 0;
            $returnData[$k]['valid_num'] = $num[$num_key]['valid_num'] ? $num[$num_key]['valid_num'] : 0;
            $returnData[$k]['invalid_num'] = $num[$num_key]['invalid_num'] ? $num[$num_key]['invalid_num'] : 0;
            $returnData[$k]['dd_num'] = $num[$num_key]['dd_num'] ? $num[$num_key]['dd_num'] : 0;
            $returnData[$k]['fbh_num'] = $num[$num_key]['fbh_num'] ? $num[$num_key]['fbh_num'] : 0;//返驳回
            $returnData[$k]['bh_num'] = $num[$num_key]['bh_num'] ? $num[$num_key]['bh_num'] : 0;//驳回
            $returnData[$k]['fp_num'] = $num[$num_key]['fp_num'] ? $num[$num_key]['fp_num'] : 0;//已分配
            $returnData[$k]['yyy_num'] = $num[$num_key]['yyy_num'] ? $num[$num_key]['yyy_num'] : 0;//已预约
            $returnData[$k]['wdd_num'] = $num[$num_key]['wdd_num'] ? $num[$num_key]['wdd_num'] : 0;//未到店
            $returnData[$k]['jdwd_num'] = $num[$num_key]['jdwd_num'] ? $num[$num_key]['jdwd_num'] : 0;//到店未定
            $returnData[$k]['yjd_num'] = $num[$num_key]['yjd_num'] ? $num[$num_key]['yjd_num'] : 0;//已进店
            $returnData[$k]['wxdd_num'] = $num[$num_key]['wxdd_num'] ? $num[$num_key]['wxdd_num'] : 0;//无效到店
            $returnData[$k]['jdbl_num'] = $num[$num_key]['jdbl_num'] ? $num[$num_key]['jdbl_num'] : 0;//进店保留
            $returnData[$k]['yyls_num'] = $num[$num_key]['yyls_num'] ? $num[$num_key]['yyls_num'] : 0;//邀约流失
            $returnData[$k]['wf_order_num'] = $wfOrderNum[$num_key] ? $wfOrderNum[$num_key] : 0;//网付订单量

            $all['not_fp_num']  += $returnData[$k]['not_fp_num'];
            $all['not_sales_num'] += $returnData[$k]['not_sales_num'];
            $all['valid_num'] += $returnData[$k]['valid_num'];
            $all['invalid_num'] += $returnData[$k]['invalid_num'];
            $all['dd_num']   += $returnData[$k]['dd_num'];
            $all['fbh_num']  += $returnData[$k]['fbh_num'];
            $all['bh_num']   += $returnData[$k]['bh_num'];
            $all['fp_num']   += $returnData[$k]['fp_num'];
            $all['yyy_num']  += $returnData[$k]['yyy_num'];
            $all['wdd_num']  += $returnData[$k]['wdd_num'];
            $all['jdwd_num'] += $returnData[$k]['jdwd_num'];
            $all['yjd_num']  += $returnData[$k]['yjd_num'];
            $all['wxdd_num'] += $returnData[$k]['wxdd_num'];
            $all['jdbl_num'] += $returnData[$k]['jdbl_num'];
            $all['yyls_num'] += $returnData[$k]['yyls_num'];
            $all['wf_order_num'] += $returnData[$k]['wf_order_num'];

        }

        /*   //没有选择渠道的时候 查没填渠道的数据数
        if(empty($data['customer_channel']) && empty($data['erji_channel_id'])){

            $null_sql .= ' and (s.channel_info_name is null or s.channel_info_name = "")';

            $null = [];
            $nullCus = 'select
            customer_channel,
            SUM(is_fp = 2) AS not_fp_num,
            SUM(c_star_day is not null) AS not_sales_num,
            SUM(is_valid in (\'1\',\'5\')) AS valid_num,
            SUM(is_valid in (\'3\',\'4\')) AS invalid_num,
            SUM(is_valid = 2) AS dd_num,
            SUM(is_valid = 5) AS fbh_num,
            SUM(is_valid = 4) AS bh_num,
            SUM(is_fp = 1) AS fp_num,
            SUM(c.sign_name = "已预约") AS yyy_num,
            SUM(c.sign_name = "未到店") AS wdd_num,
            SUM(c.sign_name = "进店未定") AS jdwd_num,
            SUM(c.sign_name = "已进店") AS yjd_num,
            SUM(c.sign_name = "无效到店") AS wxdd_num,
            SUM(c.sign_name = "进店保留") AS jdbl_num,
            SUM(c.sign_name = "邀约流失") AS yyls_num
            from crm_c_customerinfo c
            LEFT JOIN crm_l_pc_log pc
            ON c.id = pc.cid
            LEFT JOIN crm_s_channel s
            ON c.customer_channel = s.channel_name
            WHERE '.$null_sql;

            $nullNum = db()->query($nullCus);

            // 网付订单量
            $nullWfOrder = db('crm_c_customerinfo')
                ->alias('c')
                ->join('crm_l_pc_log pc','c.id = pc.cid')
                ->join('c_customerorder erp_order', 'erp_order.crm_customer_number=c.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.crm_order_type',1)
                ->where('s.channel_info_name is null or s.channel_info_name = ""')
                ->group($group)
                ->count('c.id');

            $null['channel_name'] = '未填写渠道';
            $null['not_fp_num']  = $nullNum[0]['not_fp_num'] ? $nullNum[0]['not_fp_num'] : 0;
            $null['not_sales_num'] = $nullNum[0]['not_sales_num'] ? $nullNum[0]['not_sales_num'] : 0;
            $null['valid_num'] = $nullNum[0]['valid_num'] ? $nullNum[0]['valid_num'] : 0;
            $null['invalid_num'] = $nullNum[0]['invalid_num'] ? $nullNum[0]['invalid_num'] : 0;
            $null['dd_num']   = $nullNum[0]['dd_num'] ? $nullNum[0]['dd_num'] : 0;
            $null['fbh_num']  = $nullNum[0]['fbh_num'] ? $nullNum[0]['fbh_num'] : 0;
            $null['bh_num']   = $nullNum[0]['bh_num'] ? $nullNum[0]['bh_num'] : 0;
            $null['fp_num']   = $nullNum[0]['fp_num'] ? $nullNum[0]['fp_num'] : 0;
            $null['yyy_num']  = $nullNum[0]['yyy_num'] ? $nullNum[0]['yyy_num'] : 0;
            $null['wdd_num']  = $nullNum[0]['wdd_num'] ? $nullNum[0]['wdd_num'] : 0;
            $null['jdwd_num'] = $nullNum[0]['jdwd_num'] ?$nullNum[0]['jdwd_num'] : 0;
            $null['yjd_num']  = $nullNum[0]['yjd_num'] ? $nullNum[0]['yjd_num'] : 0;
            $null['wxdd_num'] = $nullNum[0]['wxdd_num'] ? $nullNum[0]['wxdd_num'] : 0;
            $null['jdbl_num'] = $nullNum[0]['jdbl_num'] ? $nullNum[0]['jdbl_num'] : 0;
            $null['yyls_num'] = $nullNum[0]['yyls_num'] ?$nullNum[0]['yyls_num'] : 0;
            $null['wf_order_num'] = $nullWfOrder ? $nullWfOrder : 0;

            array_push($returnData,$null);

            $all['not_fp_num']  += $null['not_fp_num'];
            $all['not_sales_num'] += $null['not_sales_num'];
            $all['valid_num'] += $null['valid_num'];
            $all['invalid_num'] += $null['invalid_num'];
            $all['dd_num']   += $null['dd_num'];
            $all['fbh_num']  += $null['fbh_num'];
            $all['bh_num']   += $null['bh_num'];
            $all['fp_num']   += $null['fp_num'];
            $all['yyy_num']  += $null['yyy_num'];
            $all['wdd_num']  += $null['wdd_num'];
            $all['jdwd_num'] += $null['jdwd_num'];
            $all['yjd_num']  += $null['yjd_num'];
            $all['wxdd_num'] += $null['wxdd_num'];
            $all['jdbl_num'] += $null['jdbl_num'];
            $all['yyls_num'] += $null['yyls_num'];
            $all['wf_order_num'] += $null['wf_order_num'];

        }*/

        array_push($returnData,$all);

        if($data['need'] == 1){
            $title = ['渠道','可领取','未追踪','有效','无效','待确认','无效驳回','已分配','已预约','未到店','已进店','无效进店','进店保留','进店流失','网付订单','无效待审批','邀约流失'];

            $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['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();

        $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);

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

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

        //店
        $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);

        //小组
        $groupstaff = getGroupStaff(['shop_code'=>$shop_code]);
        $this->assign("groupstaff", json_encode($groupstaff));

        //报表名称
        $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_status_columns_hidden')));


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

}