<?php
/**
 * 渠道向集团汇报报表
 * User: sx01
 * Date: 2018/6/4
 * Time: 10:07
 */
namespace app\crm\lib\report;
use app\crm\lib\Report;
use think\Controller;
use think\Session;

class  Brandclass_report_to_company extends Report
{

    private $level = 3;
    private $type = 4;
    private $code = 'code1528164320';

    public function getLevel()
    {
        return $this->level;
    }

    public function getType()
    {
        return $this->type;
    }

    public function getCode()
    {
        return $this->code;
    }

    public function data()
    {
        $param = input();
        $det = db('s_report_form')->alias('f')->join('s_report_detail d','d.form_id=f.id')->where('d.id',$param['did'])->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();
        $bid = $det['brandclass_id'];
        if(empty($bid)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }
//        $bid = session('brandclass_id');
        $day = $param['day'] ? date('Y-m-d', strtotime($param['day'])) : date('Y-m-d');
        $start = $day.' 00:00:00';
        $end = $day.' 23:59:59';
        $startM = substr($day,0,7).'-01';
        $days = date('t',strtotime($day));
        $endM = substr($day,0,7).'-'.$days.' 23:59:59';
        //年 月
        $year = date('Y',strtotime($day));
        $month = date('m',strtotime($day));
        //店面
        //获取事业部下的渠道店面
        $shop = controller('crm/Customer','lib')->get_shop(['brandclass_id'=>$bid,'is_marketing'=>4]);
        $title = ['项目','月目标','单日','当月累计','单日指名','单日非营业'];
        $returnData = [];
        array_push($returnData,$title);
        $shops = array_column($shop,'shop_code');
        $where['is_status'] = 1;
        $where['customer_type'] = 2;
        //挖潜(A客)
        $waqian[0] = '挖潜(A客)';
        //月目标
        $waqian[1] =  db('s_kpi_staffgoal')->where('shop_code','in',$shops)->where('year',$year)->where('month',$month)->where('kpi_id','aCustomerNum')->sum('kpi_value');
        //单日
        $waqian[2] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','A客')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        //当月累计
        $waqian[3] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','A客')->where('shop_code','in',$shops)->where('sign_time','between',[$startM,$endM])->value('count(id) as num');
        //单日指名
        $waqian[4] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('customer_channel','门市部')->where('sign_name','A客')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        //单日非营业
        $waqian[5] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','A客')->where('customer_channel','<>','门市部')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$waqian);

        //邀约
        $yy[0] = '邀约';
        //月目标
        $yy[1] = db('s_kpi_staffgoal')->where('shop_code','in',$shops)->where('year',$year)->where('month',$month)->where('kpi_id','inviteIntoCustomerNumber')->sum('kpi_value');
        //单日
        $yy[2] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('shop_code','in',$shops)->where('yjd_action_day','between',[$start,$end])->value('count(id) as num');
        //当月累计
        $yy[3] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('shop_code','in',$shops)->where('yjd_action_day','between',[$startM,$endM])->value('count(id) as num');
        //单日指名
        $yy[4] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('shop_code','in',$shops)->where('customer_channel','门市部')->where('yjd_action_day','between',[$start,$end])->value('count(id) as num');
        //单日非营业
        $yy[5] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('shop_code','in',$shops)->where('customer_channel','<>','门市部')->where('yjd_action_day','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$yy);
        //进店
        $jd[0] = '进店';
        //月目标
        $jd[1] = db('s_kpi_staffgoal')->where('shop_code','in',$shops)->where('year',$year)->where('month',$month)->where('kpi_id','actualIntoCustomerNumber')->sum('kpi_value');
        //单日
        $jd[2] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_intostore','in','2,3')->where('shop_code','in',$shops)->where('syjd_day','between',[$start,$end])->value('count(id) as num');
        //当月累计
        $jd[3] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_intostore','in','2,3')->where('shop_code','in',$shops)->where('syjd_day','between',[$startM,$endM])->value('count(id) as num');
        //单日指名
        $jd[4] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_intostore','in','2,3')->where('shop_code','in',$shops)->where('customer_channel','门市部')->where('syjd_day','between',[$start,$end])->value('count(id) as num');
        //单日非营业
        $jd[5] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_intostore','in','2,3')->where('shop_code','in',$shops)->where('customer_channel','<>','门市部')->where('syjd_day','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$jd);
        //成交
        $succ[0] = '新单';
        //月目标
        $succ[1] = db('s_kpi_staffgoal')->where('shop_code','in',$shops)->where('year',$year)->where('month',$month)->where('kpi_id','orderNumber')->sum('kpi_value');
        //单日
        $succ[2] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_success',1)->where('shop_code','in',$shops)->where('order_day','between',[$start,$end])->value('count(id) as num');
        //当月累计
        $succ[3] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_success',1)->where('shop_code','in',$shops)->where('order_day','between',[$startM,$endM])->value('count(id) as num');
        //单日指名
        $succ[4] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_success',1)->where('shop_code','in',$shops)->where('customer_channel','门市部')->where('order_day','between',[$start,$end])->value('count(id) as num');
        //单日非营业
        $succ[5] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('is_success',1)->where('shop_code','in',$shops)->where('customer_channel','<>','门市部')->where('order_day','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$succ);

        //老顾客
        $old[0] = '老顾客';
        //月目标
        $old[1] = '';
        //单日
        $old[2] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('introducer_name','not null')->where('shop_code','in',$shops)->where('dj_day','between',[$start,$end])->value('count(id) as num');
        //当月累计
        $old[3] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('introducer_name','not null')->where('shop_code','in',$shops)->where('dj_day','between',[$startM,$endM])->value('count(id) as num');
        //单日指名
        $old[4] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('introducer_name','not null')->where('customer_channel','门市部')->where('shop_code','in',$shops)->where('dj_day','between',[$start,$end])->value('count(id) as num');
        //单日非营业
        $old[5] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('introducer_name','not null')->where('customer_channel','<>','门市部')->where('shop_code','in',$shops)->where('dj_day','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$old);

        //B客
        $bk[0] = 'B客';
        //月目标
        $bk[1] = db('s_kpi_staffgoal')->where('shop_code','in',$shops)->where('year',$year)->where('month',$month)->where('kpi_id','bCustomerNum')->sum('kpi_value');
        //单日
        $bk[2] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','B客')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        //当月累计
        $bk[3] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','B客')->where('shop_code','in',$shops)->where('sign_time','between',[$startM,$endM])->value('count(id) as num');
        //单日指名
        $bk[4] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('customer_channel','门市部')->where('sign_name','B客')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        //单日非营业
        $bk[5] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','B客')->where('customer_channel','<>','门市部')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$bk);

        //C客
        $ck[0] = 'C客';
        //月目标
        $ck[1] = db('s_kpi_staffgoal')->where('shop_code','in',$shops)->where('year',$year)->where('month',$month)->where('kpi_id','bCustomerNum')->sum('kpi_value');
        //单日
        $ck[2] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','C客')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        //当月累计
        $ck[3] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','C客')->where('shop_code','in',$shops)->where('sign_time','between',[$startM,$endM])->value('count(id) as num');
        //单日指名
        $ck[4] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('customer_channel','门市部')->where('sign_name','C客')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        //单日非营业
        $ck[5] = db('c_customerinfo')->where('brandclass_id',$bid)->where($where)->where('sign_name','C客')->where('customer_channel','<>','门市部')->where('shop_code','in',$shops)->where('sign_time','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$ck);
        array_push($returnData,['&nbsp;']);
        //成交率
        $cjl[0] = '日成交率';
        $cjl[1] = sprintf('%.2f',$succ[2]/$jd[2]*100).'%';
         array_push($returnData,$cjl);
        //进店率
        $cjl[0] = '日进店率';
        $cjl[1] = sprintf('%.2f',$jd[2]/$yy[2]*100).'%';
         array_push($returnData,$cjl);
        //邀约率
        $cjl[0] = '日邀约率';
        $cjl[1] = sprintf('%.2f',$yy[2]/$waqian[2]*100).'%';
         array_push($returnData,$cjl);
        //月渠道占新单比 渠道新单/事业部总新单
        $allorder = db('c_customerinfo')->where('brandclass_id',$bid)->where('is_status',1)->where('is_success',1)->where('order_day','between',[$startM,$endM])->value('count(id) as num');
        $zb[0] = '月渠道占新单比(渠道新单/事业部总新单)';
        $zb[1] = sprintf('%.2f',$succ[3]/$allorder*100).'%';
         array_push($returnData,$zb);
        //成交率
        $ycjl[0] = '月成交率';
        $ycjl[1] = sprintf('%.2f',$succ[3]/$jd[3]*100).'%';
         array_push($returnData,$ycjl);
        //进店率
        $yjdl[0] = '月进店率';
        $yjdl[1] = sprintf('%.2f',$jd[3]/$yy[3]*100).'%';
         array_push($returnData,$yjdl);
        //邀约率
        $yyyl[0] = '月邀约率';
        $yyyl[1] = sprintf('%.2f',$yy[3]/$waqian[3]*100).'%';
        array_push($returnData,$yyyl);
        //事业部数据
        $brand[0] = '当日事业部数据';
        array_push($returnData,$brand);
        //邀约
        $ayy[0] = '邀约';
        $ayy[1] = db('c_customerinfo')->where('brandclass_id',$bid)->where('is_status',1)->where('yjd_action_day','between',[$start,$end])->value('count(id) as num');
         array_push($returnData,$ayy);
        //进店
        $ajd[0] = '进店';
        $ajd[1] = db('c_customerinfo')->where('brandclass_id',$bid)->where('is_status',1)->where('is_intostore','in','2,3')->where('syjd_day','between',[$start,$end])->value('count(id) as num');
         array_push($returnData,$ajd);
        //A客
        $aa[0] = 'A客';
        $aa[1] = db('c_customerinfo')->where('brandclass_id',$bid)->where('is_status',1)->where('sign_name','A客')->where('sign_time','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$aa);
        //新单
        $asucc[0] = '新单';
        $asucc[1] = db('c_customerinfo')->where('brandclass_id',$bid)->where('is_status',1)->where('is_success',1)->where('order_day','between',[$start,$end])->value('count(id) as num');
        array_push($returnData,$asucc);
        // 导出
        if ($param['need'] == 1) {
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $brandclass = controller('crm/Customer','lib')->getBrands(['brandclass_id'=>$bid]);
            $file = $brandclass[0]['brandclass'].'-'.$ff.'.xlsx';
            unset($returnData[0]);
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        } else {
            return array('code'=>200,'data'=>$returnData,'msg'=>'');
        }

    }
    public function show()
    {
        $code = $this->getCode();

        $did = strtolower(input('param.did'));
        $info = db('s_report_form')->alias('f')->join('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();
        $shop= controller('crm/Customer','lib')->get_shop(['shop_code'=>$info['shop_code']]);
        $display = APP_PATH.'crm'.DS.'view'.DS.'report'.DS.strtolower($info['report_class_name']).'.html';
        $title = db('s_report_form')->where('code', $code)->value('report_name');
        $this->assign('title', $shop[0]['brandclass'].'-'.$title);
        $this->assign('time',date('Y-m-d',strtotime('-1 days')));
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }
}