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

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

    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();
        $staff = [];
        $sql = '1=1 ';

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

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

        //渠道
        if ($data['customer_channel']) {
            $where['s.channel_name'] = ['in',$data['customer_channel']];
        }elseif($data['yiji_channel_id']){
            $where['s.channel_class_name'] = ['in',$data['yiji_channel_id']];
        }

        $cKey = 'c.sales_staff';
        $group = 'c.sales_staff';
        $channel_key = 'staffname';

        //追踪人
        $staffname = controller('crm/Customer','lib')->get_staff_lists(['shop_code'=>$shop_code]);
        if (count($staffname) > 0) {
            // if (count($staff) > 0) {
            //     foreach ($staffname as $key => $value) {
            //         if (!in_array($value['staffname'], staff)) {
            //             unset($staffname[$key]);
            //         }
            //     }
            // }
            if($data['sales_staff']) {
                foreach ($staffname as $key => $value) {
                    if ($value['staffname'] != $data['sales_staff']) {
                        unset($staffname[$key]);
                    }
                }
            }
        }else{
            return array('code'=>100,'msg'=>'没有查到追踪人。');
        }

        // 录入客资量 √
        $allCustomer = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            ->where('c.dj_day', 'between', [$start, $end])
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $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($sql)
            // ->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', $cKey);
        $return['sql_qita'] = db()->getLastSql();

        //一级 渠道量
        $yiji_qudao = db('crm_c_customerinfo')->alias('c')
            ->join('crm_s_channel s', 's.id=c.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            ->where('c.dj_day', 'between', [$start, $end])
            ->count();
        $return['sql_yiji_qudao'] = db()->getLastSql();



        $returnData = [];
        $arr = [];
        $arr['channel_name'] = '总计';
        foreach ($staffname as $k => $v) {
            //$returnData[$k]['id'] = $v['id'];
            
            $returnData[$k]['staffname'] = $v['staffname'];

            $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']) / $yiji_qudao)*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']) / $yiji_qudao)*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();

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

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

        //追踪人
        $staff = controller('crm/Customer','lib')->get_staff_lists(['shop_code'=>$info['shop_code']]);
        $this->assign("staff",$staff);

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

}