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

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

    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'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }

        //爱婴摄影师
        if(!$data['staff_name']){
            $staff = getShopStaffs(['shop_code'=>$shop_code,'work_type'=>PZ_WORK_TYPE]);//爱婴总部 摄影师

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

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


        $where['pc.is_delete'] = 2;
        $where['pc.status'] = ['<>',99];
        $where['pc.pc_time'] = ['between time',[$start, $end]];
        $where['pc.cameraman_name'] = ['in',$staffNames];


        $pcWhere['is_delete'] = 2;
        $pcWhere['status'] = ['<>',99];
        $pcWhere['pc_time'] = ['between time',[$start, $end]];
        $pcWhere['cameraman_name'] = ['in',$staffNames];

        //邀约量
        $yy_num = db('crm_l_pc_log')
            ->where($pcWhere)
            ->where('status','in',[2,3])
            ->group('cameraman_name')
            ->column('count(id) as num','cameraman_name');

        //拍照数
        $pz_num = db('crm_l_pc_log')
            ->where($pcWhere)
            ->where('status','in',[2,3])
            ->where(['sm_type'=>['<>','理发']])
            ->group('cameraman_name')
            ->column('count(id) as num','cameraman_name');

        //理发数
        $lf_num = db('crm_l_pc_log')
            ->where($pcWhere)
            ->where('status','in',[2,3])
            ->where(['sm_type'=>'理发'])
            ->group('cameraman_name')
            ->column('count(id) as num','cameraman_name');

        //超距数
        $cj_num = db('crm_l_pc_log')
            ->where($pcWhere)
            ->where(['is_chaoju'=>1])
            ->group('cameraman_name')
            ->column('count(id) as num','cameraman_name');

        //摄影成交数  （订单金额≥899元）
        /*
        $sy_order_num = db('crm_l_pc_log')
            ->alias('pc')
            ->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')
            ->where($where)
            ->where('photo.shop_code',$shop_code)//爱婴
            ->where('erp_order.total_money','>=',899)//订单金额≥899元
            ->where(['erp_order.consumption_type'=>['not like','%三宝%']])
            ->group('cameraman_name')
            ->column('count(pc.id) as num', 'cameraman_name');
        */
        $sy_order_num = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payclass',0)
            ->where('p.payment_money','>=',899)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('count(o.orderId) as num', 'staffname');

        $sql['sy_order_num'] = db()->getLastSql();
        //三宝成交数  （订单金额≥199元）
        $sb_order_num = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payclass',0)
            ->where('p.payment_money','>=',199)
            ->where(['o.consumption_type'=>['like','%三宝%']])
            ->group('c.staffname')
            ->column('count(o.orderId) as num', 'staffname');
        /*
        $sb_order_num = db('crm_l_pc_log')
            ->alias('pc')
            ->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')
            ->where($where)
            ->where('photo.shop_code',$shop_code)//爱婴
            ->where('erp_order.total_money','>=',199)//订单金额≥199
            ->where(['erp_order.consumption_type'=>['like','%三宝%']])
            ->group('cameraman_name')
            ->column('count(pc.id) as num', 'cameraman_name');
            */
        //理发 收款 现金 刷卡 卡消
        $sbwhere = $where;
        unset($sbwhere['pc.pc_time']);
        $sbwhere['pay.paymentdate'] = ['between',[date('Ymd',strtotime($start)), date('Ymd',strtotime($end))]];
        $sbwhere['pay.payclass'] = ['<>',3];


        $lf_sb_order_money_xj = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%理发%']])
            ->group('c.staffname')
            ->column('sum(IF(p.paytype = "现金",p.payment_money,0)) as xj_money', 'staffname');

        $sql['lf_sb_order_money_xj'] = db()->getLastSql();
        $lf_sb_order_money_sk = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%理发%']])
            ->group('c.staffname')
            ->column('sum(IF(p.paytype = "刷卡",p.payment_money,0)) as sk_money', 'staffname');

        $sql['lf_sb_order_money_sk'] = db()->getLastSql();
        $lf_sb_order_money_vip = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%理发%']])
            ->group('c.staffname')
            ->column('sum(IF(p.paytype = "VIP卡",p.payment_money,0)) as vip_money', 'staffname');

        $sql['lf_sb_order_money_vip'] = db()->getLastSql();
        //其他付款方式
        $lf_sb_order_money_qt = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%理发%']])
            ->group('c.staffname')
            ->column('sum(IF(p.paytype <> "VIP卡" and p.paytype <> "现金" and p.paytype <> "刷卡",p.payment_money,0)) as qt_money', 'staffname');
        $sql['lf_sb_order_money_qt'] = db()->getLastSql();

        //三宝 现金 刷卡 卡消
        $sb_order_money_xj = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(IF(p.paytype = "现金",p.payment_money,0)) as xj_money', 'staffname');
        

        $sql['sb_order_money_xj'] = db()->getLastSql();

        $sb_order_money_sk = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(IF(p.paytype = "刷卡",p.payment_money,0)) as sk_money', 'staffname');

        $sql['sb_order_money_sk'] = db()->getLastSql();
        $sb_order_money_vip = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(IF(p.paytype = "VIP卡",p.payment_money,0)) as vip_money', 'staffname');

        $sql['sb_order_money_vip'] = db()->getLastSql();
        $sb_order_money_qt = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payment_money','>',0)
            ->where('p.payclass',0)
            ->where(['o.consumption_type'=>['like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype <> "VIP卡" and p.paytype <> "现金" and p.paytype <> "刷卡", p.payment_money, 0)) as vip_money', 'staffname');
        $sql['sb_order_money_qt'] = db()->getLastSql();
        //摄影底片（收款项目：后期实收，非三宝） 现金 刷卡 卡消
        

        $sy_order_money_xj = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payclass','<>',3)
            ->where('p.fundname','后期实收')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "现金", p.payment_money, 0)) as xj_money', 'staffname');

        $sql['sy_order_money_xj'] = db()->getLastSql();
        $sy_order_money_sk = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payclass','<>',3)
            ->where('p.fundname','后期实收')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "刷卡", p.payment_money, 0)) as sk_money', 'staffname');

        $sql['sy_order_money_sk'] = db()->getLastSql();
        $sy_order_money_vip = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payclass','<>',3)
            ->where('p.fundname','后期实收')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "VIP卡", p.payment_money, 0)) as vip_money', 'staffname');
        $sql['sy_order_money_vip'] = db()->getLastSql();
        $sy_order_money_qt = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.payclass','<>',3)
            ->where('p.fundname','后期实收')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype <> "VIP卡" and p.paytype <> "现金" and p.paytype <> "刷卡", p.payment_money, 0)) as vip_money', 'staffname');
        $sql['sy_order_money_qt'] = db()->getLastSql();

        //摄影续订单/套餐（收款项目：后期实收，非三宝） 现金 刷卡 卡消
        
        $sy_order_money_two_xj = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payclass','<>',3)
            ->where('p.fundname','续订收款')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "现金", p.payment_money, 0)) as xj_money', 'staffname');
        $sql['sy_order_money_two_xj'] = db()->getLastSql();
        $sy_order_money_two_sk = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payclass','<>',3)
            ->where('p.fundname','续订收款')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "刷卡", p.payment_money, 0)) as sk_money', 'staffname');
        $sql['sy_order_money_two_sk'] = db()->getLastSql();
        $sy_order_money_two_vip = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payclass','<>',3)
            ->where('p.fundname','续订收款')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "VIP卡", p.payment_money, 0)) as vip_money', 'staffname');
        $sql['sy_order_money_two_vip'] = db()->getLastSql();
        $sy_order_money_two_qt = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payclass','<>',3)
            ->where('p.fundname','续订收款')
            ->where('p.payment_money','>',0)
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->group('c.staffname')
            ->column('sum(if(p.paytype <> "VIP卡" and p.paytype <> "现金" and p.paytype <> "刷卡", p.payment_money, 0)) AS vip_money', 'staffname');
        $sql['sy_order_money_two_qt'] = db()->getLastSql();
        //抵用券

        $dyq_money_xj = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.tutor_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payclass','<>',3)
            ->where('p.fundname','爱婴抵用券')
            ->where('p.payment_money','>',0)
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "现金", p.payment_money, 0)) as sk_money', 'staffname');
        $dyq_money_sk = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.tutor_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payclass','<>',3)
            ->where('p.fundname','爱婴抵用券')
            ->where('p.payment_money','>',0)
            ->group('c.staffname')
            ->column('sum(if(p.paytype = "刷卡", p.payment_money, 0)) as sk_money', 'staffname');
        $dyq_money_qt = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.tutor_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payclass','<>',3)
            ->where('p.fundname','爱婴抵用券')
            ->where('p.payment_money','>',0)
            ->group('c.staffname')
            ->column('sum(if(p.paytype <> "现金" and p.paytype <> "刷卡", p.payment_money, 0)) as sk_money', 'staffname');
        //当日充值
        //充值单数
        $cz_order_num = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where(['p.payclass'=>3])//VIP卡充值
            ->where('p.payment_money','>',0)
            ->group('c.staffname')
            ->column('count(distinct o.orderId) as num', 'staffname');

        //储值总额
        $sql['cz_order_money'] = 'SELECT c.staffname,
            sum(if(p.paytype = "现金",`p`.`payment_money`,0)) AS xj_sum,
            sum(if(p.paytype = "刷卡",p.payment_money,0)) AS sk_sum,
            sum(if(p.paytype <> "现金" and p.paytype <> "刷卡",p.payment_money,0)) AS qt_sum 
            FROM c_jobscore c 
            LEFT JOIN `c_paymentcontent` `p` ON `p`.`id`=`c`.`yj_pyid` 
            LEFT JOIN `c_customerorder` `o` ON `o`.`orderId`=`p`.`orderId` 
            WHERE ( `c`.`staffname` IN (\''.implode("','",$where['pc.cameraman_name'][1] ).'\') ) 
            AND ( `p`.`paymentdate` BETWEEN '.date('Ymd',strtotime($data['start'])).' AND '.date('Ymd',strtotime($data['end'])).' ) 
            AND `c`.`position_name` = \'摄影\' AND `p`.`payclass` = 3 
            AND `p`.`payment_money` > 0 GROUP BY `c`.`staffname`';
        $cz_order_money = db()->query($sql['cz_order_money']);
        $cz_order_money_xj = array_column($cz_order_money, 'xj_sum','staffname');
        $cz_order_money_xk = array_column($cz_order_money, 'sk_sum','staffname');
        $cz_order_money_qt = array_column($cz_order_money, 'qt_sum','staffname');
        

        //卡余
        /*旧的
        $vip_money = db('crm_l_pc_log')
            ->alias('pc')
            ->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_customerinfo cus','erp_order.customerid = cus.customerid')
            ->join('c_vipcardinfo vip','cus.vipnum = vip.cardid')
            ->where($where)
            ->where('photo.shop_code',$shop_code)//爱婴
            ->group('cameraman_name')
            ->column('sum(vip.cardmoney) AS num', 'cameraman_name');
        */
        $sql['vip_money_list'] = 'select a.staffname,sum(a.cardmoney) as cardmoney from (SELECT c.staffname,v.cardmoney,v.cardid
            FROM c_jobscore c 
            LEFT JOIN `c_paymentcontent` `p` ON `p`.`id`=`c`.`yj_pyid` 
            LEFT JOIN c_vipcardinfo `v` ON `v`.`cardid`=`p`.`viprechargecard` 
            WHERE ( `c`.`staffname` IN (\''.implode("','",$where['pc.cameraman_name'][1] ).'\') ) 
            AND ( `p`.`paymentdate` BETWEEN '.date('Ymd',strtotime($data['start'])).' AND '.date('Ymd',strtotime($data['end'])).' ) 
            AND `c`.`position_name` = \'摄影\' AND `p`.`payclass` = 3 
            AND `p`.`payment_money` > 0 GROUP BY `c`.`staffname`,v.cardid) a group by a.staffname';
        $vip_money_list = db()->query($sql['vip_money_list']);
        foreach($vip_money_list as $k => $v){
            $vip_money[$v['staffname']] = $v['cardmoney'];
        }

        //成交退款数  
        $cj_refund = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where('p.payment_money','<',0)
            ->group('c.staffname')
            ->column('sum(if(p.paytype <> "VIP卡", p.payment_money, 0)) as vip_money', 'staffname');
        $sql['cj_refund'] = db()->getLastSql();
        //三宝退款
        $cj_refund_sb = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where(['o.consumption_type'=>['like','%三宝%']])
            ->where('p.payment_money','<',0)
            ->group('c.staffname')
            ->column('sum(p.payment_money) as vip_money', 'staffname');
        $sql['cj_refund_sb'] = db()->getLastSql();
        //摄影退款
        $cj_refund_sy = db('c_jobscore c')
            ->join('c_paymentcontent p','p.id = c.yj_pyid','left')
            ->join('c_customerorder o ','o.orderId = p.orderId','left')
            ->where('c.staffname',$where['pc.cameraman_name'])
            ->where('p.paymentdate',['between',[date('Ymd',strtotime($data['start'])),date('Ymd',strtotime($data['end']))]])
            ->where('c.position_name','摄影')
            ->where('p.currentfc','in',[1,2,3])
            ->where(['o.consumption_type'=>['not like','%三宝%']])
            ->where('p.payment_money','<',0)
            ->group('c.staffname')
            ->column('sum(p.payment_money) as vip_money', 'staffname');
        $sql['cj_refund_sy'] = db()->getLastSql();


        $returnData = [];
        $sum = [];
        $sum_yy_num = 0;
        $sum_pz_num = 0;
        $sum_lf_num = 0;
        $sum_cj_num = 0;
        $sum_sy_order_num = 0;
        $sum_sb_order_num = 0;
        $sum_lf_sb_order_money_xj = 0;
        $sum_lf_sb_order_money_sk = 0;
        $sum_lf_sb_order_money_vip = 0;
        $sum_lf_sb_order_money_qt = 0;
        $sum_sb_order_money_xj = 0;
        $sum_sb_order_money_sk = 0;
        $sum_sb_order_money_vip = 0;
        $sum_sb_order_money_qt = 0;
        $sum_sy_order_money_xj = 0;
        $sum_sy_order_money_sk = 0;
        $sum_sy_order_money_vip = 0;
        $sum_sy_order_money_qt = 0;
        $sum_sy_order_money_two_xj = 0;
        $sum_sy_order_money_two_sk = 0;
        $sum_sy_order_money_two_vip = 0;
        $sum_sy_order_money_two_qt = 0;
        $sum_dyq_money_xj = 0;
        $sum_dyq_money_sk = 0;
        $sum_dyq_money_qt = 0;
        $sum_cz_order_num = 0;
        $sum_cz_order_money_xj = 0;
        $sum_cz_order_money_sk = 0;
        $sum_cz_order_money_qt = 0;
        $sum_vip_money = 0;
        $sum_cj_order_money_xj = 0;
        $sum_cj_order_money_sk = 0;
        $sum_cj_order_money_vip = 0;
        $sum_cj_order_money_qt = 0;
        $sum_cj_refund = 0;
        $sum_cj_refund_sb = 0;
        $sum_cj_refund_sy = 0;

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

            $returnData[$k]['staff_name'] = $v;
            $returnData[$k]['yy_num'] = $yy_num[$v] ? $yy_num[$v] : 0;
            $returnData[$k]['pz_num'] = $pz_num[$v] ? $pz_num[$v] : 0;
            $returnData[$k]['lf_num'] = $lf_num[$v] ? $lf_num[$v] : 0;//理发数
            $returnData[$k]['cj_num'] = $cj_num[$v] ? $cj_num[$v] : 0;//超距数

            $returnData[$k]['sy_order_num'] = $sy_order_num[$v] ? $sy_order_num[$v] : 0;//摄影成交数
            $returnData[$k]['sb_order_num'] = $sb_order_num[$v] ? $sb_order_num[$v] : 0;//三宝成交数

            $returnData[$k]['lf_sb_order_money_xj'] = $lf_sb_order_money_xj[$v] ? $lf_sb_order_money_xj[$v] : '0.00';//理发  现金
            $returnData[$k]['lf_sb_order_money_sk'] = $lf_sb_order_money_sk[$v] ? $lf_sb_order_money_sk[$v] : '0.00';//理发  刷卡
            $returnData[$k]['lf_sb_order_money_vip'] = $lf_sb_order_money_vip[$v] ? $lf_sb_order_money_vip[$v] : '0.00';//理发  刷卡
            $returnData[$k]['lf_sb_order_money_qt'] = $lf_sb_order_money_qt[$v] ? $lf_sb_order_money_qt[$v] : '0.00';//理发  其他

            $returnData[$k]['sb_order_money_xj'] = $sb_order_money_xj[$v] ? $sb_order_money_xj[$v] : '0.00';//三宝收款 现金
            $returnData[$k]['sb_order_money_sk'] = $sb_order_money_sk[$v] ? $sb_order_money_sk[$v] : '0.00';//三宝收款 刷卡
            $returnData[$k]['sb_order_money_vip'] = $sb_order_money_vip[$v] ? $sb_order_money_vip[$v] : '0.00';//三宝收款 刷卡
            $returnData[$k]['sb_order_money_qt'] = $sb_order_money_qt[$v] ? $sb_order_money_qt[$v] : '0.00';//三宝收款 其他

            $returnData[$k]['sy_order_money_xj'] = $sy_order_money_xj[$v] ? $sy_order_money_xj[$v] : '0.00';//摄影底片 现金
            $returnData[$k]['sy_order_money_sk'] = $sy_order_money_sk[$v] ? $sy_order_money_sk[$v] : '0.00';//摄影底片 刷卡
            $returnData[$k]['sy_order_money_vip'] = $sy_order_money_vip[$v] ? $sy_order_money_vip[$v] : '0.00';//摄影底片 刷卡
            $returnData[$k]['sy_order_money_qt'] = $sy_order_money_qt[$v] ? $sy_order_money_qt[$v] : '0.00';//摄影底片 其他

            $returnData[$k]['sy_order_money_two_xj'] = $sy_order_money_two_xj[$v] ? $sy_order_money_two_xj[$v] : '0.00';//摄影续订单/套餐 现金
            $returnData[$k]['sy_order_money_two_sk'] = $sy_order_money_two_sk[$v] ? $sy_order_money_two_sk[$v] : '0.00';//摄影续订单/套餐 刷卡
            $returnData[$k]['sy_order_money_two_vip'] = $sy_order_money_two_vip[$v] ? $sy_order_money_two_vip[$v] : '0.00';//摄影续订单/套餐 刷卡
            $returnData[$k]['sy_order_money_two_qt'] = $sy_order_money_two_qt[$v] ? $sy_order_money_two_qt[$v] : '0.00';//摄影续订单/套餐 其他

            $returnData[$k]['dyq_money_xj'] = $dyq_money_xj[$v] ? $dyq_money_xj[$v] : '0.00';//抵用券 现金
            $returnData[$k]['dyq_money_sk'] =  $dyq_money_sk[$v] ? $dyq_money_sk[$v] : '0.00';//抵用券 刷卡
            $returnData[$k]['dyq_money_qt'] =  $dyq_money_qt[$v] ? $dyq_money_qt[$v] : '0.00';//抵用券 其他

            $returnData[$k]['cz_order_num'] = $cz_order_num[$v] ? $cz_order_num[$v] : '0.00';//当日充值单数

            $returnData[$k]['cz_order_money_xj'] = $cz_order_money_xj[$v] ? $cz_order_money_xj[$v] : '0.00';//储值总额 现金
            $returnData[$k]['cz_order_money_sk'] = $cz_order_money_sk[$v] ? $cz_order_money_sk[$v] : '0.00';//储值总额 刷卡
            $returnData[$k]['cz_order_money_qt'] = $cz_order_money_qt[$v] ? $cz_order_money_qt[$v] : '0.00';//储值总额 其它
            $returnData[$k]['vip_money'] = $vip_money[$v] ? $vip_money[$v] : '0.00';//卡余

            /* 改取合计相加不从库里重取。
            $returnData[$k]['cj_order_money_xj'] = $cj_order_money_xj[$v] ? $cj_order_money_xj[$v] : '0.00';//成交合计 现金
            $returnData[$k]['cj_order_money_sk'] = $cj_order_money_sk[$v] ? $cj_order_money_sk[$v] : '0.00';//成交合计 刷卡
            $returnData[$k]['cj_order_money_vip'] = $cj_order_money_vip[$v] ? $cj_order_money_vip[$v] : '0.00';//成交合计 卡消
            $returnData[$k]['cj_order_money_qt'] = $cj_order_money_qt[$v] ? $cj_order_money_qt[$v] : '0.00';//成交合计 其他
            
            //*/
            $returnData[$k]['cj_order_money_xj'] = 
                      $returnData[$k]['lf_sb_order_money_xj']
                    + $returnData[$k]['sb_order_money_xj']
                    + $returnData[$k]['sy_order_money_xj']
                    + $returnData[$k]['sy_order_money_two_xj']
                    + $returnData[$k]['cz_order_money_xj']
                    + $returnData[$k]['dyq_money_xj'];
            $returnData[$k]['cj_order_money_sk'] = 
                    + $returnData[$k]['lf_sb_order_money_sk']
                    + $returnData[$k]['sb_order_money_sk']
                    + $returnData[$k]['sy_order_money_sk']
                    + $returnData[$k]['sy_order_money_two_sk']
                    + $returnData[$k]['cz_order_money_sk']
                    + $returnData[$k]['dyq_money_sk'];
            $returnData[$k]['cj_order_money_vip'] = 
                    + $returnData[$k]['lf_sb_order_money_vip']
                    + $returnData[$k]['sb_order_money_vip']
                    + $returnData[$k]['sy_order_money_vip']
                    + $returnData[$k]['sy_order_money_two_vip'];
            $returnData[$k]['cj_order_money_qt'] = 
                    + $returnData[$k]['lf_sb_order_money_qt']
                    + $returnData[$k]['sb_order_money_qt']
                    + $returnData[$k]['sy_order_money_qt']
                    + $returnData[$k]['sy_order_money_two_qt']
                    + $returnData[$k]['cz_order_money_qt']
                    + $returnData[$k]['dyq_money_qt'];

            $returnData[$k]['cj_refund'] = $cj_refund[$v] ? $cj_refund[$v] : '0.00';//成交退款数
            $returnData[$k]['cj_refund_sb'] = $cj_refund_sb[$v] ? $cj_refund_sb[$v] : '0.00';//成交退款数-三宝
            $returnData[$k]['cj_refund_sy'] = $cj_refund_sy[$v] ? $cj_refund_sy[$v] : '0.00';//成交退款数-摄影

            $sum_yy_num += $returnData[$k]['yy_num'];
            $sum_pz_num += $returnData[$k]['pz_num'];
            $sum_lf_num += $returnData[$k]['lf_num'];
            $sum_cj_num += $returnData[$k]['cj_num'];

            $sum_sy_order_num += $returnData[$k]['sy_order_num'];
            $sum_sb_order_num += $returnData[$k]['sb_order_num'];

            $sum_lf_sb_order_money_xj += $returnData[$k]['lf_sb_order_money_xj'];
            $sum_lf_sb_order_money_sk += $returnData[$k]['lf_sb_order_money_sk'];
            $sum_lf_sb_order_money_vip += $returnData[$k]['lf_sb_order_money_vip'];
            $sum_lf_sb_order_money_qt += $returnData[$k]['lf_sb_order_money_qt'];

            $sum_sb_order_money_xj += $returnData[$k]['sb_order_money_xj'];
            $sum_sb_order_money_sk += $returnData[$k]['sb_order_money_sk'];
            $sum_sb_order_money_vip += $returnData[$k]['sb_order_money_vip'];
            $sum_sb_order_money_qt += $returnData[$k]['sb_order_money_qt'];

            $sum_sy_order_money_xj += $returnData[$k]['sy_order_money_xj'];
            $sum_sy_order_money_sk += $returnData[$k]['sy_order_money_sk'];
            $sum_sy_order_money_vip += $returnData[$k]['sy_order_money_vip'];
            $sum_sy_order_money_qt += $returnData[$k]['sy_order_money_qt'];

            $sum_sy_order_money_two_xj += $returnData[$k]['sy_order_money_two_xj'];
            $sum_sy_order_money_two_sk += $returnData[$k]['sy_order_money_two_sk'];
            $sum_sy_order_money_two_vip += $returnData[$k]['sy_order_money_two_vip'];
            $sum_sy_order_money_two_qt += $returnData[$k]['sy_order_money_two_qt'];

            $sum_dyq_money_xj += $returnData[$k]['dyq_money_xj'];
            $sum_dyq_money_sk += $returnData[$k]['dyq_money_sk'];
            $sum_dyq_money_qt += $returnData[$k]['dyq_money_qt'];

            $sum_cz_order_num += $returnData[$k]['cz_order_num'];
            $sum_cz_order_money_xj += $returnData[$k]['cz_order_money_xj'];
            $sum_cz_order_money_sk += $returnData[$k]['cz_order_money_sk'];
            $sum_cz_order_money_qt += $returnData[$k]['cz_order_money_qt'];
            $sum_vip_money += $returnData[$k]['vip_money'];

            $sum_cj_order_money_xj += $returnData[$k]['cj_order_money_xj'];
            $sum_cj_order_money_sk += $returnData[$k]['cj_order_money_sk'];
            $sum_cj_order_money_vip += $returnData[$k]['cj_order_money_vip'];
            $sum_cj_order_money_qt += $returnData[$k]['cj_order_money_qt'];
            $sum_cj_refund += $returnData[$k]['cj_refund'];
            $sum_cj_refund_sb += $returnData[$k]['cj_refund_sb'];
            $sum_cj_refund_sy += $returnData[$k]['cj_refund_sy'];
        }

        $sum['staff_name'] = '合计：';
        $sum['yy_num'] = $sum_yy_num;
        $sum['pz_num'] = $sum_pz_num;
        $sum['lf_num'] = $sum_lf_num;//理发数
        $sum['cj_num'] = $sum_cj_num;//超距数

        $sum['sy_order_num'] = $sum_sy_order_num;//摄影成交数
        $sum['sb_order_num'] = $sum_sb_order_num;//三宝成交数

        $sum['lf_sb_order_money_xj'] = $sum_lf_sb_order_money_xj;//理发 三宝收款 现金
        $sum['lf_sb_order_money_sk'] = $sum_lf_sb_order_money_sk;//理发 三宝收款 刷卡
        $sum['lf_sb_order_money_vip'] = $sum_lf_sb_order_money_vip;//理发 三宝收款 卡消
        $sum['lf_sb_order_money_qt'] = $sum_lf_sb_order_money_qt;//理发 三宝收款 其他

        $sum['sb_order_money_xj'] = $sum_sb_order_money_xj;//三宝收款 现金
        $sum['sb_order_money_sk'] = $sum_sb_order_money_sk;//三宝收款 刷卡
        $sum['sb_order_money_vip'] = $sum_sb_order_money_vip;//三宝收款 卡消
        $sum['sb_order_money_qt'] = $sum_sb_order_money_qt;//三宝收款 其他

        $sum['sy_order_money_xj'] = $sum_sy_order_money_xj;//摄影底片 现金
        $sum['sy_order_money_sk'] = $sum_sy_order_money_sk;//摄影底片 刷卡
        $sum['sy_order_money_vip'] = $sum_sy_order_money_vip;//摄影底片 卡消
        $sum['sy_order_money_qt'] = $sum_sy_order_money_qt;//摄影底片 其他

        $sum['sy_order_money_two_xj'] = $sum_sy_order_money_two_xj;//摄影续订单/套餐 现金
        $sum['sy_order_money_two_sk'] = $sum_sy_order_money_two_sk;//摄影续订单/套餐 刷卡
        $sum['sy_order_money_two_vip'] = $sum_sy_order_money_two_vip;//摄影续订单/套餐 卡消
        $sum['sy_order_money_two_qt'] = $sum_sy_order_money_two_qt;//摄影续订单/套餐 其他

        $sum['dyq_money_xj'] = $sum_dyq_money_xj;//抵用券 现金
        $sum['dyq_money_sk'] =  $sum_dyq_money_sk;//抵用券 刷卡
        $sum['dyq_money_qt'] =  $sum_dyq_money_qt;//抵用券 其他

        $sum['cz_order_num'] = $sum_cz_order_num;//当日充值单数
        $sum['cz_order_money_xj'] = $sum_cz_order_money_xj;//储值总额
        $sum['cz_order_money_sk'] = $sum_cz_order_money_sk;//储值总额
        $sum['cz_order_money_qt'] = $sum_cz_order_money_qt;//储值总额
        $sum['vip_money'] = $sum_vip_money;//卡余

        $sum['cj_order_money_xj'] = $sum_cj_order_money_xj;//成交合计 现金
        $sum['cj_order_money_sk'] = $sum_cj_order_money_sk;//成交合计 刷卡
        $sum['cj_order_money_vip'] = $sum_cj_order_money_vip;//成交合计 卡消
        $sum['cj_order_money_qt'] = $sum_cj_order_money_qt;//成交合计 其他
        $sum['cj_refund'] = $sum_cj_refund;//成交退款数
        $sum['cj_refund_sb'] = $sum_cj_refund_sb;//成交退款数-三宝
        $sum['cj_refund_sy'] = $sum_cj_refund_sy;//成交退款数-摄影

        array_push($returnData,$sum);
        //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'=>PZ_WORK_TYPE]);//爱婴总部
        $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', $shopInfo[0]['shop_name'].'-'. $title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}