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

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

    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,report_name')->find();

        // $shop_code = $det['shop_code'];
        //2021-04-25 改成当前店
        $shop_code = session('shop_code');
        
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }

        //爱婴话务
        if(!$data['staff_name']){
            $staff = getShopStaffs(['shop_code'=>$shop_code,'work_type'=>RECEIVE_WORK_TYPE]);

            $staffIds = array_column($staff,'id');
            $staffNames = array_column($staff,'staffname');

        }else{
            $staffNames = [$data['staff_name']];
        }

        $cus_where['is_status'] = 1;
        $cus_where['c_from'] = 1;
        // $cus_where['baby_birthday'] = ['between time',[$start, $end]];
        // $cus_where['dj_day'] = ['between time',[$start, $end]];
        $cus_where['yx_day'] = ['between time',[$start, $end]];
        $cus_where['sales_staff'] = ['in',$staffNames];

        $c_pc_where['pc.is_delete'] = 2;
        $c_pc_where['pc.status'] = ['<>',99];
        $c_pc_where['pc.pc_time'] = ['between time',[$start, $end]];
        $c_pc_where['c.sales_staff'] = ['in',$staffNames];
        // 会所类-A1（会所）  一类-A2（独家合作） 二类三类-A3（医拍） 出生名单-A4（出生名单） 预产期-D (预产期） E（拓展）
        //分配客资量
        /*$sql['fp_num'] = "
                select 
                    sales_staff,
                    sum(if(sign_name ='一类',1,0)) as yl_num,
                    sum(if(sign_name ='二类',1,0)) as el_num,
                    sum(if(sign_name ='三类',1,0)) as sl_num,
                    sum(if(sign_name ='会所类',1,0)) as hsl_num,
                    sum(if(sign_name ='出生名单',1,0)) as csmd_num 
                from crm_c_customerinfo 
                where 
                    is_status = 1 
                    and c_from = 1 
                    and sales_staff in ('".implode("','", $staffNames)."') 
                    and fp_day between '$start' and '$end'
                group by sales_staff;
        ";
        */
        $sql['fp_num'] = "
                select 
                    sales_staff,
                    sum(if(sign_name ='".YILEI."',1,0)) as yl_num,
                    sum(if(sign_name ='".ERLEI."',1,0)) as el_num,
                    sum(if(sign_name ='".SANLEI."',1,0)) as hsl_num,
                    sum(if(sign_name ='".MINGDAN."',1,0)) as csmd_num 
                from crm_c_customerinfo 
                where 
                    is_status = 1 
                    and c_from = 1 
                    and sales_staff in ('".implode("','", $staffNames)."') 
                    and fp_day between '$start' and '$end'
                group by sales_staff;
        ";
        
        $fp_num = db()->query($sql['fp_num']);
        $fp_num_yl = array_column($fp_num, 'yl_num','sales_staff');
        $fp_num_el = array_column($fp_num, 'el_num','sales_staff');
        // $fp_num_sl = array_column($fp_num, 'sl_num','sales_staff');
        $fp_num_hsl = array_column($fp_num, 'hsl_num','sales_staff');
        $fp_num_csmd = array_column($fp_num, 'csmd_num','sales_staff');
        //话务员接收的分配客资（按照宝宝出生日期）
        //一类 有效
        $yl_valid =db('crm_c_customerinfo')
            ->where($cus_where)
            ->where(['sign_name'=>YILEI,'is_valid'=>1])
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');
        //二类 有效
        $el_valid =db('crm_c_customerinfo')
            ->where($cus_where)
            ->where(['sign_name'=>ERLEI,'is_valid'=>1])
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');
        //三类 有效
        // $sl_valid =db('crm_c_customerinfo')
        //     ->where($cus_where)
        //     ->where(['sign_name'=>'A3（医拍）','is_valid'=>1])
        //     ->group('sales_staff')
        //     ->column('count(id) as num', 'sales_staff');
        //会所类有效  会所没有无效的
        $hs_valid =db('crm_c_customerinfo')
            ->where($cus_where)
            ->where(['sign_name'=>'A1（会所）','is_valid'=>1])
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');
        //无效
        $invalid =db('crm_c_customerinfo')
            ->where($cus_where)
            ->where(['is_valid'=>3])
            ->where('sign_name','in',[YILEI,ERLEI,'A1（会所）'])
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');

        //未拍出生照的客资（出生名单）
        //有效
        $csmd_valid =db('crm_c_customerinfo')
            ->where($cus_where)
            ->where('is_valid',1)
            ->where('sign_name',MINGDAN)
            // ->where('baby_birthday is not null')
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');

        //无效
        $csmd_invalid = db('crm_c_customerinfo')
            ->where($cus_where)
            ->where('is_valid',3)
            ->where('sign_name',MINGDAN)
            // ->where('baby_birthday is not null')
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');

        //预产期客资
        //有效
        $ycq_valid = db('crm_c_customerinfo')
            ->where($cus_where)
            ->where('is_valid',1)
            ->where('sign_name',YUCHANAQI)
            // ->where('ready_baby_day is not null')
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');

        //无效
        $ycq_invalid = db('crm_c_customerinfo')
            ->where($cus_where)
            ->where('is_valid',3)
            ->where('sign_name',YUCHANAQI)
            // ->where('ready_baby_day is not null')
            ->group('sales_staff')
            ->column('count(id) as num', 'sales_staff');



        //话务员客资邀约及摄影转化数(根据预约上门日期统计)
        //一类 预约量
        $yl_yy_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',YILEI)
            ->group('sales_staff')
            ->column('count(c.id) as num', 'sales_staff');


        //一类 拍照数
        $yl_pz_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',YILEI)
            ->where(['pc.status'=>2,'sm_type'=>['<>','理发']])
            ->group('sales_staff')
            ->column('count(c.id) as num','sales_staff');
        $sql['yl_pz_num'] = db()->getLastSql();
        //一类 摄影底片（后期实收）
        //$c_pc_where['pc.is_delete'] = 2;
        $c_pc_where['pc.status'] = ['<>',99];

        $yl_dp_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',YILEI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','后期实收')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');
        $sql['yl_dp_num'] = db()->getLastSql();

        //一类 续订单成交量（续订收款）
        $yl_xdd_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',YILEI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','续订收款')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');

        //一类 订单实收
        $yl_order_money = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',YILEI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('sum(pay.payment_money) AS num', 'sales_staff');
        $sql['yl_order_money'] = db()->getLastSql();
        //二类 预约量
        $el_yy_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',ERLEI)
            ->group('sales_staff')
            ->column('count(c.id) as num', 'sales_staff');
        $sql['el_yy_num'] = db()->getLastSql();


        //二类 拍照数
        $el_pz_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',ERLEI)
            ->where(['pc.status'=>2,'sm_type'=>['<>','理发']])
            ->group('sales_staff')
            ->column('count(c.id) as num','sales_staff');
        $sql['el_pz_num'] = db()->getLastSql();

        //二类 摄影底片（后期实收）
        $el_dp_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',ERLEI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','后期实收')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');


        //二类 续订单成交量（续订收款）
        $el_xdd_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',ERLEI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','续订收款')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');

        //二类 订单实收
        $el_order_money = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where('c.sign_name',ERLEI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('sum(pay.payment_money) AS num', 'sales_staff');

        // //三类 预约量
        // $sl_yy_num = db('crm_c_customerinfo')
        //     ->alias('c')
        //     ->join('crm_l_pc_log pc','c.id = pc.cid')
        //     ->where($c_pc_where)
        //     ->where('c.sign_name','A3（医拍）')
        //     ->group('sales_staff')
        //     ->column('count(c.id) as num', 'sales_staff');


        // //三类 拍照数
        // $sl_pz_num = db('crm_c_customerinfo')
        //     ->alias('c')
        //     ->join('crm_l_pc_log pc','c.id = pc.cid')
        //     ->where($c_pc_where)
        //     ->where('c.sign_name','A3（医拍）')
        //     ->where(['pc.status'=>2,'sm_type'=>['<>','理发']])
        //     ->group('sales_staff')
        //     ->column('count(c.id) as num','sales_staff');

        // //三类 摄影底片（后期实收）
        // $sl_dp_num = db('crm_c_customerinfo')
        //     ->alias('c')
        //     ->join('crm_l_pc_log pc','c.id = pc.cid')
        //     ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
        //     ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
        //     ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
        //     ->where('pc.status',['<>',99])
        //     ->where('pc.is_delete',2)
        //     ->where('c.sales_staff',['in',$staffNames])
        //     ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
        //     ->where('c.sign_name','A3（医拍）')
        //     ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
        //     ->where('pay.fundname','后期实收')
        //     ->where('pay.payment_money > 0')
        //     ->where('pay.payclass <> 3')
        //     ->where(['erp_order.consumption_type'=>['not like','%理发%']])
        //     ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
        //     ->group('sales_staff')
        //     ->column('count(c.id) AS num', 'sales_staff');


        // //三类 续订单成交量（续订收款）
        // $sl_xdd_num = db('crm_c_customerinfo')
        //     ->alias('c')
        //     ->join('crm_l_pc_log pc','c.id = pc.cid')
        //     ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
        //     ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
        //     ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
        //     ->where('pc.status',['<>',99])
        //     ->where('pc.is_delete',2)
        //     ->where('c.sales_staff',['in',$staffNames])
        //     ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
        //     ->where('c.sign_name','A3（医拍）')
        //     ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
        //     ->where('pay.fundname','续订收款')
        //     ->where('pay.payment_money > 0')
        //     ->where('pay.payclass <> 3')
        //     ->where(['erp_order.consumption_type'=>['not like','%理发%']])
        //     ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
        //     ->group('sales_staff')
        //     ->column('count(c.id) AS num', 'sales_staff');
        // $sql['sl_xdd_num'] = db()->getLastSql();
        // //三类 订单实收
        // $sl_order_money = db('crm_c_customerinfo')
        //     ->alias('c')
        //     ->join('crm_l_pc_log pc','c.id = pc.cid')
        //     ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
        //     ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
        //     ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
        //     ->where('pc.status',['<>',99])
        //     ->where('pc.is_delete',2)
        //     ->where('c.sales_staff',['in',$staffNames])
        //     ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
        //     ->where('pay.payclass <> 3')
        //     ->where(['erp_order.consumption_type'=>['not like','%理发%']])
        //     ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
        //     ->where('c.sign_name','A3（医拍）')
        //     ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
        //     ->group('sales_staff')
        //     ->column('sum(pay.payment_money) AS num', 'sales_staff');
        // $sql['sl_order_money'] = db()->getLastSql();
        //会所拍摄量
        $hs_pz_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name','A1(会所)')
            ->where(['pc.status'=>2,'sm_type'=>['<>','理发']])
            ->group('sales_staff')
            ->column('count(c.id) as num','sales_staff');
        $sql['hs_pz_num'] = db()->getLastSql();
        //会所底片成交量
        $hs_dp_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name','A1(会所)')
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','后期实收')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');
        $sql['hs_dp_num'] = db()->getLastSql();

        //会所续定成交量
        $hs_xdd_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name','A1(会所)')
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','续订收款')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');
        $sql['hs_xdd_num'] = db()->getLastSql();
        //会所订单实收
        $hs_order_money = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name','A1(会所)')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('sum(pay.payment_money) AS num', 'sales_staff');
        $sql['hs_order_money'] = db()->getLastSql();
        //出生名单 预约量
        $csmd_yy_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('sign_name',MINGDAN)
            // ->where('c.baby_birthday is not null')
            ->group('sales_staff')
            ->column('count(c.id) as num', 'sales_staff');


        //出生名单 拍照数
        $csmd_pz_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',MINGDAN)
            ->where(['pc.status'=>2,'sm_type'=>['<>','理发']])
            ->group('sales_staff')
            ->column('count(c.id) as num','sales_staff');
        $sql['csmd_pz_num'] = db()->getLastSql();
        //出生名单 摄影底片（后期实收）
        $csmd_dp_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',MINGDAN)
            // ->where('c.baby_birthday is not null')
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','后期实收')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');


        //出生名单 续订单成交量（续订收款）
        $csmd_xdd_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',MINGDAN)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->where('pay.fundname','续订收款')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');

        //出生名单 订单实收
        $csmd_order_money = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where('c.sign_name',MINGDAN)
            // ->where('c.ready_baby_day is not null')
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('sum(pay.payment_money) AS num', 'sales_staff');
        $sql['csmd_order_money'] = db()->getLastSql();

        //预产期 预约量
        $ycq_yy_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',YUCHANAQI)
            ->group('sales_staff')
            ->column('count(c.id) as num', 'sales_staff');


        //预产期 拍照数
        $ycq_pz_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',YUCHANAQI)
            ->where(['pc.status'=>2,'sm_type'=>['<>','理发']])
            ->group('sales_staff')
            ->column('count(c.id) as num','sales_staff');

        //预产期 摄影底片（后期实收）
        $ycq_dp_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',YUCHANAQI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            //->where('erp_order.total_money','<',899)//订单应收金额<899
            ->where('pay.fundname','后期实收')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');


        //预产期 续订单成交量（>=899元）
        $ycq_xdd_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.sign_name',YUCHANAQI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            // ->where('erp_order.total_money','>=',899)//订单应收金额>=899
            ->where('pay.fundname','续订收款')
            ->where('pay.payment_money > 0')
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');

        //预产期 订单实收
        $ycq_order_money = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('pay.payclass <> 3')
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where('c.sign_name',YUCHANAQI)
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('sum(pay.payment_money) AS num', 'sales_staff');

        //理发 (根据预约上门日期统计)
        //所有预约量
        $yy_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where(['sm_type'=>'理发'])
            ->group('sales_staff')
            ->column('count(c.id) as num', 'sales_staff');

        //理发量-状态是已成交或已核销的
        $lf_num =  db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where(['status'=>['in',[2,3]],'sm_type'=>'理发'])
            ->group('sales_staff')
            ->column('count(c.id) as num', 'sales_staff');
        $sql['lf_num'] =  db()->getLastSql();
        //理发成交
        $lf_order_money = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('pay.payclass <> 3')
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where(['erp_order.consumption_type'=>['like','%理发%']])
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('sum(pay.payment_money) AS num', 'sales_staff');
        $sql['lf_order_money'] = db()->getLastSql();

        //三宝成交
        $sb_order_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('pay.payclass <> 3')
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where(['erp_order.consumption_type'=>['like','%三宝%']])
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('count(distinct(c.id)) AS num', 'sales_staff');
        $sql['sb_order_num'] = db()->getLastSql();
        //三宝成交 订单实收
        $sb_order_money = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','pc.id=erp_order.crm_visit_id')
            ->join('c_customerphoto photo','photo.orderId = erp_order.orderId and photo.currentfc = 1')
            ->join('c_paymentcontent pay','pay.orderId = photo.orderId and pay.currentfc = 1')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('pay.payclass <> 3')
            ->where('c.sales_staff',['in',$staffNames])
            ->where('pay.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where(['erp_order.consumption_type'=>['like','%三宝%']])
            ->where('photo.shop_name','in',["爱婴总部","爱婴一部","爱婴二部","爱婴三部"])//爱婴
            ->group('sales_staff')
            ->column('sum(pay.payment_money) AS num', 'sales_staff');
        $sql['sb_order_money'] = db()->getLastSql();
        //分析数据
        //预约取消量（不含理发预约和理发取消）  预约量-预约成功
        $not_lf_yy_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('pc.sm_type',['<>','理发'])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');

        $not_lf_yy_success_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where(['pc.sm_type'=>['<>','理发'],'pc.status'=>['in',[2,3]]])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');

        //一类/二类/三类摄影入户
        $yes_rh_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where(['c.sign_name'=>['in',[YILEI,ERLEI]],'pc.status'=>['in',[2,3]]])
            ->where('sm_type','<>','理发')
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');
        $sql['yes_rh_num'] = db()->getLastSql();
        //会所入户数
        $hs_rh_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name','A1(会所)')
            ->where('sm_type','<>','理发')
            // ->where('c.baby_birthday is not null')
            ->where(['pc.status'=>['in',[2,3]]])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');
        $sql['hs_rh_num'] = db()->getLastSql();
        //出生名单摄影入户
        $csmd_rh_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',MINGDAN)
            ->where('sm_type','<>','理发')
            // ->where('c.baby_birthday is not null')
            ->where(['pc.status'=>['in',[2,3]]])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');
        $sql['csmd_rh_num'] = db()->getLastSql();
        //预产期摄影入户
        $ycq_rh_num = db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->where($c_pc_where)
            ->where('c.sign_name',YUCHANAQI)
            ->where('sm_type','<>','理发')
            ->where(['pc.status'=>['in',[2,3]]])
            ->group('sales_staff')
            ->column('count(c.id) AS num', 'sales_staff');

        //摄影订单实收总业绩
        $sy_order_money =  db('crm_c_customerinfo')
            ->alias('c')
            ->join('crm_l_pc_log pc','c.id = pc.cid')
            ->join('c_customerorder erp_order','erp_order.crm_visit_id = pc.id')
            ->join('c_paymentcontent p','p.orderId = erp_order.orderId and currentfc = 1')
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where(' p.currentfc = 1')
            ->where(' p.payclass <> 3')
            ->where('pc.status',['<>',99])
            ->where('pc.is_delete',2)
            ->where('c.sales_staff',['in',$staffNames])
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->where(['erp_order.consumption_type'=>['not like','%理发%']])
            ->group('c.sales_staff')
            ->column('sum(p.payment_money) AS num', 'sales_staff');
        $sql['sy_order_money'] = db()->getLastSql();

        $returnData = [];
        $sum = [];
        $all = [];

        foreach($staffNames as $k => $v){

            $returnData[$k]['staff_name'] = $v;
            $returnData[$k]['fp_num_yl'] = $fp_num_yl[$v] ? $fp_num_yl[$v] : 0;//一类分配
            $returnData[$k]['fp_num_el'] = $fp_num_el[$v] ? $fp_num_el[$v] : 0;//二类分配
            // $returnData[$k]['fp_num_sl'] = $fp_num_sl[$v] ? $fp_num_sl[$v] : 0;//三类分配
            $returnData[$k]['fp_num_hsl'] = $fp_num_hsl[$v] ? $fp_num_hsl[$v] : 0;//会所类分配
            $returnData[$k]['fp_num_csmd'] = $fp_num_csmd[$v] ? $fp_num_csmd[$v] : 0;//出生名单分配

            $returnData[$k]['yl_valid'] = $yl_valid[$v] ? $yl_valid[$v] : 0;//一类有效
            $returnData[$k]['el_valid'] = $el_valid[$v] ? $el_valid[$v] : 0;//二类有效
            // $returnData[$k]['sl_valid'] = $sl_valid[$v] ? $sl_valid[$v] : 0;//三类有效
            $returnData[$k]['hs_valid'] = $hs_valid[$v]?$hs_valid[$v]:0;//会所有效
            $returnData[$k]['invalid'] = $invalid[$v] ? $invalid[$v] : 0;//无效

            $returnData[$k]['csmd_valid'] = $csmd_valid[$v] ? $csmd_valid[$v] : 0;//出生名单有效
            $returnData[$k]['csmd_invalid'] = $csmd_invalid[$v] ? $csmd_invalid[$v] : 0;//出生名单有效

            $returnData[$k]['ycq_valid'] = $ycq_valid[$v] ? $ycq_valid[$v] : 0;//预产期有效
            $returnData[$k]['ycq_invalid'] = $ycq_invalid[$v] ? $ycq_invalid[$v] : 0;//预产期无效

            $returnData[$k]['yl_yy_num'] = $yl_yy_num[$v] ? $yl_yy_num[$v] : 0;//一类
            $returnData[$k]['yl_pz_num'] = $yl_pz_num[$v] ? $yl_pz_num[$v] : 0;//一类
            $returnData[$k]['yl_dp_num'] = $yl_dp_num[$v] ? $yl_dp_num[$v] : 0;//底片
            $returnData[$k]['yl_xdd_num'] = $yl_xdd_num[$v] ? $yl_xdd_num[$v] : 0;//续订单
            $returnData[$k]['yl_order_money'] = $yl_order_money[$v] ? $yl_order_money[$v] : '0.00';//订单实收

            $returnData[$k]['el_yy_num'] = $el_yy_num[$v] ? $el_yy_num[$v] : 0;//二类
            $returnData[$k]['el_pz_num'] = $el_pz_num[$v] ? $el_pz_num[$v] : 0;//二类
            $returnData[$k]['el_dp_num'] = $el_dp_num[$v] ? $el_dp_num[$v] : 0;//底片
            $returnData[$k]['el_xdd_num'] = $el_xdd_num[$v] ? $el_xdd_num[$v] : 0;//续订单
            $returnData[$k]['el_order_money'] = $el_order_money[$v] ? $el_order_money[$v] : '0.00';//二类订单实收

            // $returnData[$k]['sl_yy_num'] = $sl_yy_num[$v] ? $sl_yy_num[$v] : 0;//三类
            // $returnData[$k]['sl_pz_num'] = $sl_pz_num[$v] ? $sl_pz_num[$v] : 0;//三类
            // $returnData[$k]['sl_dp_num'] = $sl_dp_num[$v] ? $sl_dp_num[$v] : 0;//三类底片
            // $returnData[$k]['sl_xdd_num'] = $sl_xdd_num[$v] ? $sl_xdd_num[$v] : 0;//三类续订单
            // $returnData[$k]['sl_order_money'] = $sl_order_money[$v] ? $sl_order_money[$v] : '0.00';//三类订单实收

            $returnData[$k]['hs_pz_num'] = $hs_pz_num[$v] ? $hs_pz_num[$v] : 0;//会所类拍照
            $returnData[$k]['hs_dp_num'] = $hs_dp_num[$v] ? $hs_dp_num[$v] : 0;//会所类底片
            $returnData[$k]['hs_xdd_num'] = $hs_xdd_num[$v] ? $hs_xdd_num[$v] : 0;//会所类续订单
            $returnData[$k]['hs_order_money'] = $hs_order_money[$v] ? $hs_order_money[$v] : '0.00';//会所类订单实收

            $returnData[$k]['csmd_yy_num'] = $csmd_yy_num[$v] ? $csmd_yy_num[$v] : 0;//出生名单
            $returnData[$k]['csmd_pz_num'] = $csmd_pz_num[$v] ? $csmd_pz_num[$v] : 0;//出生名单
            $returnData[$k]['csmd_dp_num'] = $csmd_dp_num[$v] ? $csmd_dp_num[$v] : 0;//出生名单底片
            $returnData[$k]['csmd_xdd_num'] = $csmd_xdd_num[$v] ? $csmd_xdd_num[$v] : 0;//出生名单续订单
            $returnData[$k]['csmd_order_money'] = $csmd_order_money[$v] ? $csmd_order_money[$v] : '0.00';//出生名单订单实收

            $returnData[$k]['ycq_yy_num'] = $ycq_yy_num[$v] ? $ycq_yy_num[$v] : 0;//预产期
            $returnData[$k]['ycq_pz_num'] = $ycq_pz_num[$v] ? $ycq_pz_num[$v] : 0;//预产期
            $returnData[$k]['ycq_dp_num'] = $ycq_dp_num[$v] ? $ycq_dp_num[$v] : 0;//预产期底片
            $returnData[$k]['ycq_xdd_num'] = $ycq_xdd_num[$v] ? $ycq_xdd_num[$v] : 0;//预产期续订单
            $returnData[$k]['ycq_order_money'] = $ycq_order_money[$v] ? $ycq_order_money[$v] : '0.00';//预产期订单实收


            $returnData[$k]['yy_num'] = $yy_num[$v] ? $yy_num[$v] : 0;//预约数
            $returnData[$k]['lf_num'] = $lf_num[$v] ? $lf_num[$v] : 0;//理发数

            $returnData[$k]['sb_order_num'] = $sb_order_num[$v] ? $sb_order_num[$v] : 0;//三宝成交
            $returnData[$k]['sb_order_money'] = $sb_order_money[$v] ? $sb_order_money[$v] : '0.00';//三宝成交实收
            $returnData[$k]['lf_order_money'] = $lf_order_money[$v] ? $lf_order_money[$v] : '0.00';//理发成交实收

            //求预约取消量
            $returnData[$k]['not_lf_yy_num'] = $not_lf_yy_num[$v] ? $not_lf_yy_num[$v] : 0;//不是理发的所有预约量
            $returnData[$k]['not_lf_yy_success_num'] = $not_lf_yy_success_num[$v] ? $not_lf_yy_success_num[$v] : 0;//不是理发的所有预约成功量
            $returnData[$k]['yy_cancel'] =  $returnData[$k]['not_lf_yy_num'] - $returnData[$k]['not_lf_yy_success_num'];//预约取消量


            $returnData[$k]['yes_valid'] = $returnData[$k]['yl_valid'] + $returnData[$k]['el_valid'];// + $returnData[$k]['sl_valid'];//一类二类三类有效
            $returnData[$k]['yes_rh_num'] = $yes_rh_num[$v] ? $yes_rh_num[$v] : 0;//一类二类三类入户
            $returnData[$k]['yes_rh_lv'] = (sprintf('%.2f', $returnData[$k]['yes_rh_num'] / $returnData[$k]['yes_valid'] * 100)) . '%';//一类二类三类入户率

            //会所入户hs_rh_num
            $returnData[$k]['hs_rh_num'] = $hs_rh_num[$v] ? $hs_rh_num[$v] : 0;//出生名单入户
            $returnData[$k]['hs_rh_lv'] = (sprintf('%.2f', $returnData[$k]['hs_rh_num'] / $returnData[$k]['hs_valid'] * 100)) . '%';//出生名单入率

            $returnData[$k]['csmd_rh_num'] = $csmd_rh_num[$v] ? $csmd_rh_num[$v] : 0;//出生名单入户
            $returnData[$k]['csmd_rh_lv'] = (sprintf('%.2f', $returnData[$k]['csmd_rh_num'] / $returnData[$k]['csmd_valid'] * 100)) . '%';//出生名单入率

            $returnData[$k]['ycq_rh_num'] = $ycq_rh_num[$v] ? $ycq_rh_num[$v] : 0;//预产期入户
            $returnData[$k]['ycq_rh_lv'] = (sprintf('%.2f', $returnData[$k]['ycq_rh_num'] / $returnData[$k]['ycq_valid'] * 100)) . '%';//预产期入户率

            $returnData[$k]['sy_order_money'] = $sy_order_money[$v] ? $sy_order_money[$v] : '0.00';//摄影订单实收总业绩


            $all['fp_num_yl'] += $returnData[$k]['fp_num_yl'];
            $all['fp_num_el'] += $returnData[$k]['fp_num_el'];
            // $all['fp_num_sl'] += $returnData[$k]['fp_num_sl'];
            $all['fp_num_hsl'] += $returnData[$k]['fp_num_hsl'];
            $all['fp_num_csmd'] += $returnData[$k]['fp_num_csmd'];
            $all['yl_valid'] += $returnData[$k]['yl_valid'];
            $all['el_valid'] += $returnData[$k]['el_valid'];
            // $all['sl_valid'] += $returnData[$k]['sl_valid'];
            $all['hs_valid'] += $returnData[$k]['hs_valid'];

            $all['invalid'] += $returnData[$k]['invalid'];
            $all['csmd_valid'] += $returnData[$k]['csmd_valid'];
            $all['csmd_invalid'] += $returnData[$k]['csmd_invalid'];
            $all['ycq_valid'] += $returnData[$k]['ycq_valid'];
            $all['ycq_invalid'] += $returnData[$k]['ycq_invalid'];

            $all['yl_yy_num'] += $returnData[$k]['yl_yy_num'];
            $all['yl_pz_num'] += $returnData[$k]['yl_pz_num'];
            $all['yl_dp_num'] += $returnData[$k]['yl_dp_num'];
            $all['yl_xdd_num'] += $returnData[$k]['yl_xdd_num'];
            $all['yl_order_money'] += $returnData[$k]['yl_order_money'];

            $all['el_yy_num'] += $returnData[$k]['el_yy_num'];
            $all['el_pz_num'] += $returnData[$k]['el_pz_num'];
            $all['el_dp_num'] += $returnData[$k]['el_dp_num'];
            $all['el_xdd_num'] += $returnData[$k]['el_xdd_num'];
            $all['el_order_money'] += $returnData[$k]['el_order_money'];

            $all['sl_yy_num'] += $returnData[$k]['sl_yy_num'];
            $all['sl_pz_num'] += $returnData[$k]['sl_pz_num'];
            $all['sl_dp_num'] += $returnData[$k]['sl_dp_num'];
            $all['sl_xdd_num'] += $returnData[$k]['sl_xdd_num'];
            $all['sl_order_money'] += $returnData[$k]['sl_order_money'];

            $all['hs_pz_num'] += $returnData[$k]['hs_pz_num'];
            $all['hs_dp_num'] += $returnData[$k]['hs_dp_num'];
            $all['hs_xdd_num'] += $returnData[$k]['hs_xdd_num'];
            $all['hs_order_money'] += $returnData[$k]['hs_order_money'];



            $all['csmd_yy_num'] += $returnData[$k]['csmd_yy_num'];
            $all['csmd_pz_num'] += $returnData[$k]['csmd_pz_num'];
            $all['csmd_dp_num'] += $returnData[$k]['csmd_dp_num'];
            $all['csmd_xdd_num'] += $returnData[$k]['csmd_xdd_num'];
            $all['csmd_order_money'] += $returnData[$k]['csmd_order_money'];

            $all['ycq_yy_num'] += $returnData[$k]['ycq_yy_num'];
            $all['ycq_pz_num'] += $returnData[$k]['ycq_pz_num'];
            $all['ycq_dp_num'] += $returnData[$k]['ycq_dp_num'];
            $all['ycq_xdd_num'] += $returnData[$k]['ycq_xdd_num'];
            $all['ycq_order_money'] += $returnData[$k]['ycq_order_money'];

            $all['yy_num'] += $returnData[$k]['yy_num'];
            $all['lf_num'] += $returnData[$k]['lf_num'];

            $all['sb_order_num'] += $returnData[$k]['sb_order_num'];
            $all['sb_order_money'] += $returnData[$k]['sb_order_money'];
            $all['lf_order_money'] += $returnData[$k]['lf_order_money'];

            //求预约取消量
            $all['not_lf_yy_num'] += $returnData[$k]['not_lf_yy_num'];
            $all['not_lf_yy_success_num'] += $returnData[$k]['not_lf_yy_success_num'];
            $all['yy_cancel'] += $returnData[$k]['yy_cancel'];

            $all['yes_valid'] += $returnData[$k]['yes_valid'];
            $all['yes_rh_num'] += $returnData[$k]['yes_rh_num'];

            $all['hs_rh_num'] += $returnData[$k]['hs_rh_num'];

            $all['csmd_rh_num'] += $returnData[$k]['csmd_rh_num'];

            $all['ycq_rh_num'] += $returnData[$k]['ycq_rh_num'];

            $all['sy_order_money'] += $returnData[$k]['sy_order_money'];

        }

        $all['staff_name'] = '合计：';
        $all['yes_rh_lv'] = (sprintf('%.2f', $all['yes_rh_num'] / $all['yes_valid'] * 100)) . '%';//一类二类三类入户率
        $all['hs_rh_lv'] = (sprintf('%.2f', $all['hs_rh_num'] / $all['hs_valid'] * 100)) . '%';//出生名单入率
        $all['csmd_rh_lv'] = (sprintf('%.2f', $all['csmd_rh_num'] / $all['csmd_valid'] * 100)) . '%';//出生名单入率
        $all['ycq_rh_lv'] = (sprintf('%.2f', $all['ycq_rh_num'] / $all['ycq_valid'] * 100)) . '%';//预产期入户率

        $all['fp_num_yl'] = $all['fp_num_yl'] ? $all['fp_num_yl'] : 0;
        $all['fp_num_el'] = $all['fp_num_el'] ? $all['fp_num_el'] : 0;
        // $all['fp_num_sl'] = $all['fp_num_sl'] ? $all['fp_num_sl'] : 0;
        $all['fp_num_hsl'] = $all['fp_num_hsl'] ? $all['fp_num_hsl'] : 0;
        $all['fp_num_csmd'] = $all['fp_num_csmd'] ? $all['fp_num_csmd'] : 0;
        $all['yl_valid'] = $all['yl_valid'] ? $all['yl_valid'] : 0;
        $all['el_valid'] = $all['el_valid'] ? $all['el_valid'] : 0;
        // $all['sl_valid'] = $all['sl_valid'] ? $all['sl_valid'] : 0;
        $all['hs_valid'] = $all['hs_valid'] ? $all['hs_valid'] : 0;
        $all['invalid'] = $all['invalid'] ? $all['invalid'] : 0;
        $all['csmd_valid'] = $all['csmd_valid'] ? $all['csmd_valid'] : 0;
        $all['csmd_invalid'] = $all['csmd_invalid'] ? $all['csmd_invalid'] : 0;
        $all['ycq_valid'] = $all['ycq_valid'] ? $all['ycq_valid'] : 0;
        $all['ycq_invalid'] = $all['ycq_invalid'] ? $all['ycq_invalid'] : 0;

        $all['yl_yy_num'] = $all['yl_yy_num'] ? $all['yl_yy_num'] : 0;
        $all['yl_pz_num'] = $all['yl_pz_num'] ? $all['yl_pz_num'] : 0;
        $all['yl_dp_num'] = $all['yl_dp_num'] ? $all['yl_dp_num'] : 0;
        $all['yl_xdd_num'] = $all['yl_xdd_num'] ? $all['yl_xdd_num'] : 0;
        $all['yl_order_money'] = $all['yl_order_money'] ? $all['yl_order_money'] : '0.00';

        $all['el_yy_num'] = $all['el_yy_num'] ? $all['el_yy_num'] : 0;
        $all['el_pz_num'] = $all['el_pz_num'] ? $all['el_pz_num'] : 0;
        $all['el_dp_num'] = $all['el_dp_num'] ? $all['el_dp_num'] : 0;
        $all['el_xdd_num'] = $all['el_xdd_num'] ? $all['el_xdd_num'] : 0;
        $all['el_order_money'] = $all['el_order_money'] ? $all['el_order_money'] : '0.00';

        $all['sl_yy_num'] = $all['sl_yy_num'] ? $all['sl_yy_num'] : 0;
        $all['sl_pz_num'] = $all['sl_pz_num'] ? $all['sl_pz_num'] : 0;
        $all['sl_dp_num'] = $all['sl_dp_num'] ? $all['sl_dp_num'] : 0;
        $all['sl_xdd_num'] = $all['sl_xdd_num'] ? $all['sl_xdd_num'] : 0;
        $all['sl_order_money'] = $all['sl_order_money'] ? $all['sl_order_money'] : '0.00';

        $all['hs_pz_num'] = $all['hs_pz_num'] ? $all['hs_pz_num'] : 0;
        $all['hs_dp_num'] = $all['hs_dp_num'] ? $all['hs_dp_num'] : 0;
        $all['hs_xdd_num'] = $all['hs_xdd_num'] ? $all['hs_xdd_num'] : 0;
        $all['hs_order_money'] = $all['hs_order_money'] ? $all['hs_order_money'] : '0.00';

        $all['csmd_yy_num'] = $all['csmd_yy_num'] ? $all['csmd_yy_num'] : 0;
        $all['csmd_pz_num'] = $all['csmd_pz_num'] ? $all['csmd_pz_num'] : 0;
        $all['csmd_dp_num'] = $all['csmd_dp_num'] ? $all['csmd_dp_num'] : 0;
        $all['csmd_xdd_num'] = $all['csmd_xdd_num'] ? $all['csmd_xdd_num'] : 0;
        $all['csmd_order_money'] = $all['csmd_order_money'] ? $all['csmd_order_money'] : '0.00';

        $all['ycq_yy_num'] = $all['ycq_yy_num'] ? $all['ycq_yy_num'] : 0;
        $all['ycq_pz_num'] = $all['ycq_pz_num'] ? $all['ycq_pz_num'] : 0;
        $all['ycq_dp_num'] = $all['ycq_dp_num'] ? $all['ycq_dp_num'] : 0;
        $all['ycq_xdd_num'] = $all['ycq_xdd_num'] ? $all['ycq_xdd_num'] : 0;
        $all['ycq_order_money'] = $all['ycq_order_money'] ? $all['ycq_order_money'] : '0.00';

        $all['yy_num'] = $all['yy_num'] ? $all['yy_num'] : 0;
        $all['lf_num'] = $all['lf_num'] ? $all['lf_num'] : 0;

        $all['sb_order_num'] = $all['sb_order_num'] ? $all['sb_order_num'] : 0;
        $all['sb_order_money'] = $all['sb_order_money'] ? $all['sb_order_money'] : '0.00';
        $all['lf_order_money'] = $all['lf_order_money'] ? $all['lf_order_money'] : '0.00';

        //求预约取消量
        $all['not_lf_yy_num'] = $all['not_lf_yy_num'] ? $all['not_lf_yy_num'] : 0;
        $all['not_lf_yy_success_num'] = $all['not_lf_yy_success_num'] ? $all['not_lf_yy_success_num'] : 0;
        $all['yy_cancel'] = $all['yy_cancel'] ? $all['yy_cancel'] : 0;

        $all['yes_valid'] = $all['yes_valid'] ? $all['yes_valid'] : 0;
        $all['yes_rh_num'] = $all['yes_rh_num'] ? $all['yes_rh_num'] : 0;
        $all['hs_rh_num'] = $all['hs_rh_num'] ? $all['hs_rh_num'] : 0;

        $all['csmd_rh_num'] = $all['csmd_rh_num'] ? $all['csmd_rh_num'] : 0;

        $all['ycq_rh_num'] = $all['ycq_rh_num'] ? $all['ycq_rh_num'] : 0;

        $all['sy_order_money'] = $all['sy_order_money'] ? $all['sy_order_money'] : '0.00';


        array_push($returnData,$all);
        //print_r($returnData);die;

        $return['code'] = 0;
        $return['msg'] = '';
        $return['count'] = count($returnData);
        $return['data'] = $returnData;
        $return['sql'] = $sql;
        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();

        //爱婴话务
        // $staff = getShopStaffs(['shop_code'=>$info['shop_code'],'work_type'=>RECEIVE_WORK_TYPE]);

        //2021-04-25 改成当前店
        $staff = getShopStaffs(['shop_code'=>session('shop_code'),'work_type'=>RECEIVE_WORK_TYPE]);


        //$res = sx_call('5b75341456d18', ['shop_code'=>$shop_code,'all'=>2,'work_type'=>21]);//爱婴总部
        //$staff = $res['data'];
        $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'=>session('shop_code')]);
        $this->assign('title',$shopInfo[0]['shop_name'].'-'.$title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}