<?php
/**
 * 报表实现类 聚焦活动报表
 *
 * ============================================================================
 * 版权所有 2017北京素玄科技，并保留所有权利。
 *
 * 网站地址: http://www.suxuantech.com
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！未经允许的情况下，您不能对本系统代码做任何修改 .
 * 不允许对程序代码以任何形式任何目的的再发布。
 * 如有修改需求，请联系素玄科技有限公司：contact@suxuantech.cn
 * ============================================================================
 * $Author: lilong/wangzhenya 2018-05-29 $
 */
namespace app\crm\lib\report;
use app\crm\lib\Report;
use think\Controller;
use think\image\Exception;

class Jj_hd_data extends Report {

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

    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();


        $det = db('s_report_form')->alias('f')->join('s_report_detail d','d.form_id=f.id')->where('d.id',$data['did'])->field('d.id,d.shop_code,d.brandclass_id,f.report_class_name')->find();
        $shop_code = $det['shop_code'];
        if(empty($shop_code)){
            return array('code'=>100,'msg'=>'报表配置有误');
        }

        $where['c.is_status'] = 1;
        $where['c.c_from'] = 1;
        if($data['hd_name']){
            $where['f.hd_name'] = trim($data['hd_name']);
        }else{
            return array('code'=>105,'msg'=>'请选择活动');
        }
        if($data['introducer_name']){
            $where['c.introducer_name'] = trim($data['introducer_name']);
        }
        if($data['introducer_tel']){
            $where['c.introducer_tel'] = trim($data['introducer_tel']);
        }
        if($data['dj']){
            $where['c.dj_staff'] = trim($data['dj']);
        }
        $start = $data['start'] ? date('Y-m-d', strtotime($data['start'])) : date('Y-m-d 00:00:00');
        $end =  $data['end'] ? date('Y-m-d 23:59:59', strtotime($data['end'])) : date('Y-m-d 23:59:59');

        $mkz = db('c_customerinfo')->alias('c')->join('c_customer_from f','f.cid=c.id','left')
            ->where($where)
            ->where('c.dj_day','between',[$start,$end])
            ->group('c.dj_staff,c.introducer_name')
            ->order('c.dj_staff,c.introducer_name')
            ->field('count(c.id)as num,c.dj_staff,c.dj_staff_id,c.introducer_name,f.hd_name')
            ->select();
        $valid = db('c_customerinfo')->alias('c')->join('c_customer_from f','f.cid=c.id','left')
            ->where($where)
            ->where('c.is_valid',1)
            ->where('c.dj_day','between',[$start,$end])
            ->group('c.dj_staff,c.introducer_name')
            ->order('c.dj_staff,c.introducer_name')
            ->field('count(c.id)as num,c.dj_staff,c.dj_staff_id,c.introducer_name,f.hd_name')
            ->select();
        $jd = db('c_customerinfo')->alias('c')->join('c_customer_from f','f.cid=c.id','left')
            ->where($where)
            ->where('c.is_intostore',2)
            ->where('c.syjd_day','between',[$start,$end])
            ->group('c.dj_staff,c.introducer_name')
            ->order('c.dj_staff,c.introducer_name')
            ->field('count(c.id)as num,c.dj_staff,c.dj_staff_id,c.introducer_name,f.hd_name')
            ->select();
        $order = db('c_customerinfo')->alias('c')->join('c_customer_from f','f.cid=c.id','left')
            ->where($where)
            ->where('c.is_success','in',[1,3])
            ->where('c.order_start_time','between',[$start,$end])
            ->group('c.dj_staff,c.introducer_name')
            ->order('c.dj_staff,c.introducer_name')
            ->field('count(c.id)as num,c.dj_staff,c.dj_staff_id,c.introducer_name,f.hd_name')
            ->select();
        $wj = db('c_customerinfo')->alias('c')->join('c_customer_from f','f.cid=c.id','left')
            ->where($where)
            ->where('c.is_intostore','<>',1)
            ->where('c.yjd_day','between',[$start,$end])
            ->group('c.dj_staff,c.introducer_name')
            ->order('c.dj_staff,c.introducer_name')
            ->field('count(c.id)as num,c.dj_staff,c.dj_staff_id,c.introducer_name,f.hd_name')
            ->select();
        $zd = db('c_customerinfo')->alias('c')->join('c_customer_from f','f.cid=c.id','left')
            ->where($where)
            ->where('c.is_success',2)
            ->where('c.is_intostore','in',[1,3])
            ->where('c.syjd_day','between',[$start,$end])
            ->group('c.dj_staff,c.introducer_name')
            ->order('c.dj_staff,c.introducer_name')
            ->field('count(c.id)as num,c.dj_staff,c.dj_staff_id,c.introducer_name,f.hd_name')
            ->select();
        $returnData = [];
        $arr = [];
        $px = [];
        $px['hd_name'] = '';
        $px['dj_staff'] = '总计';
        foreach($mkz as $k=>$v){
            $arr['hd_name'] = $v['hd_name'] ? $v['hd_name'] : '';
            $arr['dj_staff'] = $v['dj_staff'] ? $v['dj_staff'] : '';
            $arr['introducer_name'] = ($v['dj_staff']==$v['introducer_name'])&&$v['dj_staff_id']>0 ? '' : ($v['introducer_name'] ? $v['introducer_name'] : '');
            $arr['mkz'] = $v['num'] ? $v['num'] : '';
            foreach($valid as $vv){
                if($v['introducer_name'] == $vv['introducer_name'] && $v['dj_staff']  == $vv['dj_staff']){
                    $arr['valid'] = $vv['num'] ? $vv['num'] : '';
                }
            }
            foreach($jd as $vvv){
                if($v['introducer_name'] == $vvv['introducer_name'] && $v['dj_staff']  == $vvv['dj_staff']){
                    $arr['jd'] = $vvv['num'] ? $vvv['num'] : '';
                }
            }
            foreach($order as $vvvv){
                if($v['introducer_name'] == $vvvv['introducer_name'] && $v['dj_staff']  == $vvvv['dj_staff']){
                    $arr['order'] = $vvvv['num'] ? $vvvv['num'] : '';
                }
            }
            foreach($wj as $vvvvv){
                if($v['introducer_name'] == $vvvvv['introducer_name'] && $v['dj_staff']  == $vvvvv['dj_staff']){
                    $arr['wj'] = $vvvvv['num'] ? $vvvvv['num'] : '';
                }
            }
            foreach($zd as $vvvvvv){
                if($v['introducer_name'] == $vvvvvv['introducer_name'] && $v['dj_staff']  == $vvvvvv['dj_staff']){
                    $arr['zd'] = $vvvvvv['num'] ? $vvvvvv['num'] : '';
                }
            }

           /* $arr['jd'] = $jd[$v['dj_staff']]['num'] ? $jd[$v['dj_staff']]['num'] : '';
            $arr['order'] = $order[$v['dj_staff']]['num'] ? $order[$v['dj_staff']]['num'] : '';
            $arr['wj'] = $wj[$v['dj_staff']]['num'] ? $wj[$v['dj_staff']]['num'] : '';
            $arr['zd'] = $zd[$v['dj_staff']]['num'] ? $zd[$v['dj_staff']]['num'] : '';*/

            $arr['validRate'] = $arr['valid']/$arr['mkz']>0 ? sprintf('%.2f',$arr['valid']/$arr['mkz']*100).'%' : '';
            $arr['jdRate'] = $arr['jd']/$arr['valid']>0 ? sprintf('%.2f',$arr['jd']/$arr['valid']*100).'%' : '';
            $arr['orderRate'] = $arr['order']/$arr['jd']>0 ? sprintf('%.2f',$arr['order']/$arr['jd']*100).'%' : '';
            array_push($returnData,$arr);
            $px['introducer_name'] = '';
            $px['mkz'] += $arr['mkz'];
            $px['valid'] += $arr['valid'];
            $px['jd'] += $arr['jd'];
            $px['order'] += $arr['order'];
            $px['wj'] += $arr['wj'];
            $px['zd'] += $arr['zd'];
            $px['validRate'] = '';
            $px['jdRate'] ='';
            $px['orderRate'] = '';
            unset($arr);
        }
        $px['validRate'] = $px['valid']/$px['mkz']>0 ? sprintf('%.2f',$px['valid']/$px['mkz']*100).'%' : '';
        $px['jdRate'] =$px['jd']/$px['valid']>0 ? sprintf('%.2f',$px['jd']/$px['valid']*100).'%' : '';
        $px['orderRate'] = $px['order']/$px['jd']>0 ? sprintf('%.2f',$px['order']/$px['jd']*100).'%' : '';
        if(!empty($returnData)){
            array_push($returnData,$px);
        }

        // 导出
        if ($data['need'] == 1) {
            $title = ['活动名称','推荐人','提报人渠道', '客资量','有效客资','到店','成交','预约未进店','进店未成交','客资有效率','进店率','成交率'];
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $shop = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
            $file = $shop[0]['shop_name'].'-'.$ff.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $returnData, $file);
        }elseif($data['need'] == 2){
            $title = ['ID','客资姓名','客资手机', '是否分配','是否有效','是否进店','是否成交','分配人','登记人','追踪人','推荐人','意向店面','提报店面','归属店面'];
            $where = [];
            if($data['dj']) $where['c.dj_staff'] = trim($data['dj']);
            if($data['hd_name']) $where['f.hd_name'] = trim($data['hd_name']);
            if($data['introducer_name']) $where['c.introducer_name'] = trim($data['introducer_name']);
            $res = db('c_customerinfo')->alias('c')->join('c_customer_from f','f.cid=c.id','left')
                ->where($where)
                ->where('c.dj_day','between',[$start,$end])
                ->order('c.dj_staff,c.introducer_name')
                ->field('c.id,c.customer_name,c.customer_tel,is_fp,is_valid,is_intostore,is_success,c.fp_staff,c.dj_staff,c.sales_staff,c.introducer_name,c.consulation_shop,c.add_shop_name,c.shop_name')
                ->select();
            foreach($res as $k=>$v){
                $res[$k]['is_valid'] = getIsValidAttr($v['is_valid']);
                $res[$k]['is_fp'] = getIsFP($v['is_fp']);
                $res[$k]['is_success'] = getIsSuccess($v['is_success']);
                $res[$k]['is_intostore'] = getIsIntostore($v['is_intostore']);
            }
            $code = $this->getCode();
            $ff = db('s_report_form')->where('code', $code)->value('report_name');
            $shop = controller('crm/Customer','lib')->get_shop(['shop_code'=>$shop_code]);
            $file = $shop[0]['shop_name'].'-'.$ff.'.xlsx';
            controller("crm/Customer", "lib")->export($title, $res, $file);
        }else {
            $return['code'] = 0;
            $return['msg'] = '';
            $return['data'] = $returnData;
            $return['count'] = count($returnData);
            return json($return);
        }
    }

    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();
        $hd_name = db('c_customer_from')->where('hd_name','not null')->field('hd_name')->group('hd_name')->select();
        $this->assign('hd_name', $hd_name);
        if(!empty($hd_name)){
            //提报人
            $dj = db('c_customerinfo')->where('channel_plan_number',$hd_name[0]['hd_name'])->group('dj_staff')->select();
            $this->assign('dj', $dj);
            //介绍人
//            $js = db('c_customerinfo')->where('channel_plan_number',$hd_name[0]['hd_name'])->group('introducer_name')->select();
//            $this->assign('js', $js);
        }


        $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', $title);
        $this->assign('did', $info['id']);
        return $this->fetch($display);
    }

}