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

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

    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('Ymd', strtotime($data['start']));
        $end = date('Ymd', 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['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 ctk.telstaffname in ('.implode(',',$salesStaff).')';
            $where['ctk.telstaffname'] = $salesStaff;
        }

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

        $cKey = 'ctk.telstaffname';
        $group = 'ctk.telstaffname';
        $channel_key = 'telstaffname';

        //追踪人【催拍人】  c_customerteltracking.telstaffname
        $staffname = db('c_customerteltracking')->where('telstaffname is not null')->field('telstaffname')->group('telstaffname')->select();
        if (count($staffname) == 0) {
            return array('code'=>100,'msg'=>'没有查到追踪人【催拍人】 。');
        }

        // 录入客资量 √
        $allCustomer = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'o.customerid=c.customerid','left')
            ->join('c_customerteltracking ctk', 'ctk.telphone=c.wphone','left')
            ->join('crm_c_customerinfo cc', 'cc.id= o.crm_customer_id','left')
            ->join('crm_s_channel s', 's.id=cc.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            ->where('o.fenpeiyaoyueday', '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('c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'o.customerid=c.customerid','left')
            ->join('c_customerteltracking ctk', 'ctk.telphone=c.wphone','left')
            ->join('crm_c_customerinfo cc', 'cc.id= o.crm_customer_id','left')
            ->join('crm_s_channel s', 's.id=cc.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('o.fenpeiyaoyueday', 'between', [$start, $end])
            ->where('cc.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('c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'o.customerid=c.customerid','left')
            ->join('c_customerteltracking ctk', 'ctk.telphone=c.wphone','left')
            ->join('crm_c_customerinfo cc', 'cc.id= o.crm_customer_id','left')
            ->join('crm_s_channel s', 's.id=cc.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('o.fenpeiyaoyueday', 'between', [$start, $end])
            ->where('cc.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('c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'o.customerid=c.customerid','left')
            ->join('c_customerteltracking ctk', 'ctk.telphone=c.wphone','left')
            ->join('crm_c_customerinfo cc', 'cc.id= o.crm_customer_id','left')
            ->join('crm_s_channel s', 's.id=cc.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('o.fenpeiyaoyueday', 'between', [$start, $end])
            ->where('cc.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('c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'o.customerid=c.customerid','left')
            ->join('c_customerteltracking ctk', 'ctk.telphone=c.wphone','left')
            ->join('crm_c_customerinfo cc', 'cc.id= o.crm_customer_id','left')
            ->join('crm_s_channel s', 's.id=cc.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('o.fenpeiyaoyueday', 'between', [$start, $end])
            ->where('cc.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('c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'o.customerid=c.customerid','left')
            ->join('c_customerteltracking ctk', 'ctk.telphone=c.wphone','left')
            ->join('crm_c_customerinfo cc', 'cc.id= o.crm_customer_id','left')
            ->join('crm_s_channel s', 's.id=cc.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('o.fenpeiyaoyueday', 'between', [$start, $end])
            ->where('cc.sign_name','停机')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name', $cKey);
        $return['sql_tingji'] = db()->getLastSql();
        
        //取消拍摄
        $quxiaops = db('c_customerinfo')->alias('c')
            ->join('c_customerorder o', 'o.customerid=c.customerid','left')
            ->join('c_customerteltracking ctk', 'ctk.telphone=c.wphone','left')
            ->join('crm_c_customerinfo cc', 'cc.id= o.crm_customer_id','left')
            ->join('crm_s_channel s', 's.id=cc.customer_channel_id','left')
            ->where($where)
            ->where($sql)
            // ->where('c.yjd_day is not null')
            //->where('c.yjd_day', 'between time', [$start, $end])
            ->where('o.fenpeiyaoyueday', 'between', [$start, $end])
            ->where('cc.loss_cause','取消拍摄')
            ->group($group)
            ->column('count(c.id) as num,s.channel_name,s.channel_class_name', $cKey);
        $return['sql_quxiaops'] = db()->getLastSql();


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

            $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]['quxiaops'] = $quxiaops[$v[$channel_key]]['num'] ? $quxiaops[$v[$channel_key]]['num'] : 0;
            

            //转化率 = 预约成功/数据总量
            $returnData[$k]['zhl'] = (sprintf('%.2f',$returnData[$k]['yyNum']/ $returnData[$k]['lrCustomer'])*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['quxiaops']    += $returnData[$k]['quxiaops'];
        }

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

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

}