gaoyixia/application/user/controller/Member.php

<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2019 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: http://demo.thinkadmin.top
// +----------------------------------------------------------------------
// | 开源协议 ( https://mit-license.org )
// +----------------------------------------------------------------------
// | gitee 代码仓库：https://gitee.com/zoujingli/ThinkAdmin
// | github 代码仓库：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace app\user\controller;
use app\common\model\User;
use app\common\model\UserFacility;
use app\common\model\UserGroup;
use app\common\model\UserLevel;
use app\common\model\UserLevelRank;
use app\common\model\UserTag;
use app\common\model\UserWallet;
use app\common\model\VideoIntro;
use library\Controller;
use library\tools\Data;
use think\cache\driver\Redis;
use think\Db;
use function AlibabaCloud\Client\value;

/**
 * 会员信息管理
 * Class Member
 * @package app\Member\controller
 */
class Member extends Controller
{
    /**
     * 绑定数据表
     * @var string
     */
    protected $table = 'StoreMember';

    /**
     * 会员信息管理
     * @auth true
     * @menu true
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function index()
    {
        $this->search_url = strtolower($this->request->controller()).'/index_search';
        $this->title = '会员信息管理';
        $this->tag_arr = UserTag::column('title','id');
        $this->level_arr = UserLevel::column('name','id');
        $this->group_arr = UserGroup::column('name','id');
        $where = [];
        $where[] = ['m.is_deleted','=',0];
        if($name = input('get.name')) $where[] = ['m.name','like','%'.$name.'%'];
        if($phone = input('get.phone')) $where[] = ['m.phone|m.email','like','%'.$phone.'%'];
        if($level_id = input('get.level_id')) $where[] = ['m.level_id','=',$level_id];
//        if($level_id = input('get.level_id')) $where[] = ['r.level_id','=',$level_id];
//        if($level_id = input('get.level_id')) $where[] = ['r.end_date','>',date('Y-m-d H:i:s')];
        if($label_id = input('get.label_id')) $where[] = ['m.label','like','%|'.$label_id."|%"];
        if($account_type = input('get.account_type')) {
            if($account_type == 3) {
                $where[] = ['m.is_first','=',1];
            }else{
                $where[] = ['m.account_type','=',$account_type];
            }
        }
        $field="m.id,m.openid,m.name,m.is_first,m.headimg,m.level_exp,m.phone,m.status,m.create_at,m.account_type,w.growth,w.integral,w.money,m.tag_id,label";
        $create_at = input('create_at');
        $query = $this->_query($this->table)
            ->field($field)
            ->alias('m')
            ->leftJoin('UserWallet w','m.id =w.user_id')
            ->where($where)
            ->when($create_at,function ($q)use ($create_at){
                if($create_at){
                    $time = explode(' - ',$_GET['create_at']);
                    $start_date_time = $time[0];
                    $end_date_time =  $time[1];
                    $q->whereBetweenTime('m.create_at',$start_date_time,$end_date_time);
                }
            })->order('id desc')->select();
        $query->page();


    }

    /**
     * 数据列表处理
     * @auth true
     * @menu true
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function _index_page_filter(&$data)
    {
        $level_id = input('get.level_id');
        $limit = input('get.limit');
        if(!$limit){
            $limit = 20;
        }
        $level_arr = UserLevel::column('name','id');
        $group_arr = UserGroup::column('name','id');
        if($level_id && $level_id != 0){
            $arr = [];
            foreach ($data as $k=>&$v){
                $level_rank= UserLevelRank::getUserVipInfo($v['id']);
                if($level_rank){
                    $v['level_id']      = $level_rank ? $level_rank['level_id'] : 0 ;
                    $v['level_name']    = $level_rank ? $level_arr[$level_rank['level_id']] : '--';
                    $v['end_date']      = $level_rank ?$level_rank['end_date'] :'--';
                    $label_ids = implode(',',explode("|",trim($v['label'],'|')));
                    $v['label_arr']     = $v['label'] ? UserTag::where('id','in',$label_ids)->column('title') : [];
                    $arr[] = $v;
                }
            }
            $data = $arr;
        }else{
            foreach ($data as $k=>&$v){
                $level_rank= UserLevelRank::getUserVipInfo($v['id']);
                $v['level_id']      = $level_rank ? $level_rank['level_id'] : 0 ;
                $v['level_name']    = $level_rank ? $level_arr[$level_rank['level_id']] : '--';
                $v['end_date']      = $level_rank ?$level_rank['end_date'] :'--';
                $label_ids = implode(',',explode("|",trim($v['label'],'|')));
                $v['label_arr']     = $v['label'] ? UserTag::where('id','in',$label_ids)->column('title') : [];
            }
        }
        if(count($data) < $limit && $level_id && $level_id != 0){
            $num = $limit - count($data);
            $field="m.id,m.openid,m.name,m.is_first,m.headimg,m.level_exp,m.phone,m.status,m.create_at,m.account_type,w.growth,w.integral,w.money,m.tag_id,label";
            $create_at = input('create_at');
            $where = [];
            $where[] = ['m.is_deleted','=',0];
            if($name = input('get.name')) $where[] = ['m.name','like','%'.$name.'%'];
            if($phone = input('get.phone')) $where[] = ['m.phone|m.email','like','%'.$phone.'%'];
            $query = User::field($field)
                ->alias('m')
                ->leftJoin('UserWallet w','m.id =w.user_id')
                ->where($where)
                ->when($create_at,function ($q)use ($create_at){
                    if($create_at){
                        $time = explode(' - ',$_GET['create_at']);
                        $start_date_time = $time[0];
                        $end_date_time =  $time[1];
                        $q->whereBetweenTime('m.create_at',$start_date_time,$end_date_time);
                    }
                })->order('id desc')->select();
            $arrs = [];
//            $data = $query;
//            $level_arr = UserLevel::column('name','id');
            foreach ($query as $k=> &$v){
                if(count($arrs) >= $num){
                    break;
                }
//                dump($v);die;
                $level_rank= UserLevelRank::getUserVipInfo($v['id']);
                if(!$level_rank){
                    unset($v);
                }else{
                    $v['level_id']      = $level_rank ? $level_rank['level_id'] : 0 ;
                    $v['level_name']    = $level_rank ? $level_arr[$level_rank['level_id']] : '--';
                    $v['end_date']      = $level_rank ?$level_rank['end_date'] :'--';
                    $label_ids = implode(',',explode("|",trim($v['label'],'|')));
                    $v['label_arr']     = $v['label'] ? UserTag::where('id','in',$label_ids)->column('title') : [];
                    $arrs[] = $v;
                }
            }
            $data = array_merge($arrs,$data);
        }

    }



    public function selectes($num = 0){
        if($num == 0){
            return ;
        }
        $field="m.id,m.openid,m.name,m.is_first,m.headimg,m.level_exp,m.phone,m.status,m.create_at,m.account_type,w.growth,w.integral,w.money,m.tag_id,label";
        $create_at = input('create_at');
        $where = [];
        $where[] = ['m.is_deleted','=',0];
        if($name = input('get.name')) $where[] = ['m.name','like','%'.$name.'%'];
        if($phone = input('get.phone')) $where[] = ['m.phone|m.email','like','%'.$phone.'%'];
        $query = $this->_query($this->table)
            ->field($field)
            ->alias('m')
            ->leftJoin('UserWallet w','m.id =w.user_id')
            ->where($where)
            ->when($create_at,function ($q)use ($create_at){
                if($create_at){
                    $time = explode(' - ',$_GET['create_at']);
                    $start_date_time = $time[0];
                    $end_date_time =  $time[1];
                    $q->whereBetweenTime('m.create_at',$start_date_time,$end_date_time);
                }
            })->order('id desc')->select();
        $arr = [];
        $data = $query;
        $level_arr = UserLevel::column('name','id');
        foreach ($data as $k=>&$v){
            if(count($arr) >= $num){
                break;
            }
            $level_rank= UserLevelRank::getUserVipInfo($v['id']);
            if(!$level_rank){
                unset($v);
            }else{
                $v['level_id']      = $level_rank ? $level_rank['level_id'] : 0 ;
                $v['level_name']    = $level_rank ? $level_arr[$level_rank['level_id']] : '--';
                $v['end_date']      = $level_rank ?$level_rank['end_date'] :'--';
                $label_ids = implode(',',explode("|",trim($v['label'],'|')));
                $v['label_arr']     = $v['label'] ? UserTag::where('id','in',$label_ids)->column('title') : [];
                $arr[] = $v;
            }
        }
        return $arr;
    }

    /**
     * 删除
     * @auth true
     * @menu true
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function remove()
    {
        $this->_save($this->table, ['is_deleted' => '1','phone'=>'','email'=>'','bind_id'=>0,'is_first'=>0]);
    }
    /**
     * 禁用
     * @auth true
     * @menu true
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function forbid()
    {
        $this->_save($this->table, ['status' => '0']);
    }
    /**
     * 启用
     * @auth true
     * @menu true
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function resume()
    {
        $this->_save($this->table, ['status' => '1']);
    }




    /**
     * 添加
     * @auth true
     * @menu true
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function add(){
        $this->title = '添加';
        $this->level_arr = UserLevel::column('name','id');
        $this->group_arr = UserGroup::column('name','id');
        if($this->request->isPost())
        {
            list($data) = [$this->request->post()];
            $check_where =[];
            $check_where[] = $data['account_type'] == 1 ? ['email','=',$data['account']] : ['phone','=',$data['account']];
            $user_info = User::where($check_where)->find();
            if($user_info) $this->error('用户已存在');
            $user_add= [];

            $data['account_type'] == 1 ? $user_add['email'] = $data['account'] :  $user_add['phone'] = $data['account'];
            $user_add['name'] = $data['name'];
            $user_add['account_type'] =  $data['account_type'];
            $select_label = [];
            if(isset($data['label']) && !empty($data['label'])){
                foreach ($data['label'] as $key=>$value){
                    if($value) $select_label[] = $key;
                }
            }
            $data['label'] = '|'.implode('|',$select_label).'|';
            $new_user  = User::create($user_add);
            if($data['level_id'] && intval($data['days']) > 0) {
                UserLevelRank::create(['user_id'=>$new_user->id,'level_id'=>$data['level_id'],'start_time'=>time(),'end_time'=>time()+86400* intval($data['days']),'end_date'=>date('Y-m-d H:i:s',time()+86400*intval($data['days']))]);
            }
            $this->success('添加成功');
        }
        $this->_form($this->table, 'add');
    }

    /**
     *
     * 编辑
     * @auth true
     * @menu true
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function edit()
    {
        $this->title = '编辑';
        $this->level_arr = UserLevel::column('name','id');
        $this->group_arr = UserGroup::column('name','id');
        $this->_form($this->table, 'form');
    }

    /**
     * 钱包管理
     * @auth true
     * @menu true
     * @param array $data
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function wallet()
    {
        $this->title = '钱包';

        $this->_form($this->table, 'wallet');
    }


    protected function _form_filter(&$data){
        //选中的服务标签
        $this->tag_arr = UserTag::column('title','id');

        if($this->request->isPost() && $this->request->action() == 'add')
        {
            if($data['account_type'] == 1) {
                $is_reg = User::where('email',$data['account'])->value('id');
                $data['email'] = $data['account'];
            }else{
                $is_reg = User::where('phone',$data['account'])->value('id');
                $data['phone'] = $data['account'];
            }
            if($is_reg)$this->error('账号已存在');
        }
        if($this->request->isPost() && $this->request->action() == 'edit') {
            if($data['level_id'] > 0 && $data['over_time']) {
                Data::save('UserLevelRank',[
                    'user_id'=>$data['id'],
                    'level_id'=>$data['level_id'],
                    'start_time'=>time(),
                    'end_time'=>strtotime($data['over_time']) ,
                    'end_date'=>$data['over_time']
                ],'user_id',['user_id'=>$data['id']]);
                $data['level_exp']  = $data['over_time'];
            } else if($data['level_id'] == 0){
                Data::save('UserLevelRank',[
                    'user_id'=>$data['id'],
                    'level_id'=>$data['level_id'],
                    'start_time'=>time(),
                    'end_time'=> time(),
                    'end_date'=>date('Y-m-d H:i:s')
                ],'user_id',['user_id'=>$data['id']]);
                $data['level_exp']  = date('Y-m-d H:i:s');
            }

            if($data['group_id'] > 0 && $data['group_id']) {
                $check_group = User::where(['group_id'=>$data['group_id'],'is_first'=>1])->value('id');//该公司是否有第一个注册的
                if(!$check_group) $data['is_first']  =1;
                if($check_group)$data['group_first']  = $check_group;
                $data['bind_id'] = $data['id'];
                $data['account_type'] = 1;
            }else{
                $data['is_first']  =1;
                $data['group_first'] = 0;
                $data['bind_id'] = 0;
                $data['account_type'] = 2;
            }
        }
        //
        if($this->request->isPost()) {
            $select_label = [];
            if(isset($data['label']) && !empty($data['label'])){
                foreach ($data['label'] as $key=>$value){
                    if($value) $select_label[] = $key;
                }
            }
            $data['label'] = '|'.implode('|',$select_label).'|';

            if(isset($data['bind_phone'])) {
                $bind_id = User::where('phone',$data['bind_phone'])->value('id');
                if(!$bind_id) $this->error('该用户未注册');
                $check_bind = User::where(['bind_id'=>$bind_id])->value('id');
                if($data['bind_phone'] && $check_bind) $this->error('该用户被绑定');
                $data['bind_id'] = $bind_id;
            }
        }
        if($this->request->isGet() && $this->request->action() =='edit') {
            $label_arr = isset($data['label']) ? explode('|',trim($data['label'],'|')) : [];
            $this->label_arr = $label_arr;
            $this->label_zn_arr = Db::name('user_label')->where('user_id',$data['id'])->order('num desc')->select();
            $data['rank_id'] = UserLevelRank::getUserVip($data['id']);
            $level_rank= UserLevelRank::where([['user_id','=',$data['id']]])->find();
            $data['end_date']      = $level_rank ? $level_rank->end_date:'--';
            $data['bind_phone'] = $data['bind_id'] ? User::where('id',$data['bind_id'])->value('phone'):'';
        }


    }


    public function facility()
    {
        if($this->request->isGet())
        {
            $user_id = input('get.user_id');
            $list = UserFacility::where('user_id',$user_id)->column('facility','type');
            $this->fetch('', ['list' =>$list,'user_id'=>$user_id]);
        }else{
            list($data) = [$this->request->post()];
            for ($i=1;$i<=4;$i++) {
               // $check_facility = UserFacility::where(['user_id'=>$data['user_id'],'type'=>$i,'facility'=>$data['facility_'.$i]])->value('id');
                $redis = new Redis();

                if(isset($data['facility_'.$i])){
                    Data::save(
                        'UserFacility',
                        ['user_id'=>$data['user_id'],'type'=>$i,'facility'=>$data['facility_'.$i],'set_time'=>date('Y-m-d H:i:s')],
                        'user_id',['user_id'=>$data['user_id'],'type'=>$i]);
                    if($data['facility_'.$i] == ''){
                        $redis->rm('TOKEN_'.$data['user_id'].'_'.$i);
//                        var_dump('TOKEN_'.$data['user_id'].'_'.$i);die;
                        $aliases = $this->aliases($data['user_id'],[],'GET','?new_format=true');
                        if(isset($aliases['data']) && $aliases['data'] != '' && $aliases['data'] != null){
                            if(count($aliases['data']) > 0){
                                //别名下有绑定的Registration ID
                                $registration_ids = [];
                                foreach ($aliases['data'] as $k => $v){
                                    if($i == 1 || $i == 3){
                                        //安卓
                                        if($v['platform'] == "android"){
                                            $registration_ids[] = $v['registration_id'];
                                        }
                                    }else if($i == 2 || $i == 4){
                                        //ios
                                        if($v['platform'] == "ios"){
                                            $registration_ids[] = $v['registration_id'];
                                        }
                                    }
                                }
                                if(count($registration_ids) > 0){
                                    $body = [
                                        "registration_ids"=>[
                                            "remove"=> $registration_ids
                                        ]
                                    ];
                                    //var_dump($body);die;
//                            var_dump($registration_ids);die;
                                    $aliases_del = $this->aliases($data['user_id'],$body,'POST');
//                                var_dump($aliases_del);die;
                                }
                            }
                        }
                    }
                }
            }
            $this->success('编辑成功');
        }


    }

    /**
     * @title 推送查询
     * @desc 推送查询
     */
    public function aliases($id = 66,$registration_ids = [],$type = 'GET',$new_format = ''){
        $data = input();
//        if(!$data['url'] || !$data['from'] || !$data['to'] || !$data['url']){
//
//        }
        $arr = [
            'url'=>'https://device.jpush.cn/v3/aliases/'.$id.$new_format,
            'AppKey'=>config('app.jiguang')['AppKey'],
            'MasterSecret'=>config('app.jiguang')['MasterSecret'],
        ];
//        $hx_voice = hx_voice(24);
//        $url= $arr['url'].$arr['org_name'].'/'.$arr['app_name'].'/'.'messages/users';
//        $body['from'] = $data['form']; //'member2200'
//        $body['to'] = $data['to']; //worker1000
//        $body['type'] = 'audio';
//        $body['body'] = [
//            'filename' => $data['filename'], //'testaudio.amr', //语音文件的名称
//            'url' => $data['url'] //'http://tmp/bQo7Udv1FETr01c4bb0ecee3a2edb86b00a393176fcc.durationTime=2790.aac', //语音文件的url地址
//        ];
        $body = $registration_ids;
        $options['type']="audio";
        $options['msg']='发送';

        $b=json_encode($body);
//        return $b;
        $str = $arr['AppKey'].':'.$arr['MasterSecret'];
        //$str = '7d431e42dfa6a6d693ac2d04:5e987ac6d2e04d95a9d8f0d1';

        $base64 = base64_encode($str);

        //$header=['Authorization' => ];
        $header = array("Authorization: Basic ".$base64);
//        return json($header);
        $result=$this->postCurl($arr['url'],$b,$header,$type);
//        $result=$this->postCurl($url,$body,$header);
        //dump($result);
        return $result;
//        return $hx_voice;
    }

    public function postCurl($url,$body,$header,$type="POST"){
        //1.创建一个curl资源
        $ch = curl_init();
        //2.设置URL和相应的选项
        curl_setopt($ch,CURLOPT_URL,$url);//设置url
        //1)设置请求头
//        array_push($header, 'Accept:application/json');
//        array_push($header,'Content-Type:application/json');
//        array_push($header, 'http:multipart/form-data');
        //设置为false,只会获得响应的正文(true的话会连响应头一并获取到)
        curl_setopt($ch,CURLOPT_HEADER,0);
//		curl_setopt ( $ch, CURLOPT_TIMEOUT,5); // 设置超时限制防止死循环
        //设置发起连接前的等待时间，如果设置为0，则无限等待。
        curl_setopt($ch,CURLOPT_CONNECTTIMEOUT,5);
        //将curl_exec()获取的信息以文件流的形式返回，而不是直接输出。
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        //2)设备请求体
        if (strlen($body)>0) {
            //$b=json_encode($body,true);
            curl_setopt($ch, CURLOPT_POSTFIELDS, $body);//全部数据使用HTTP协议中的"POST"操作来发送。
        }
        //设置请求头
//        if(count($header)>0){
//            curl_setopt($ch,CURLOPT_HTTPHEADER,$header);
//        }
        curl_setopt($ch,CURLOPT_HTTPHEADER,$header);
        //上传文件相关设置
        curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
        curl_setopt($ch, CURLOPT_MAXREDIRS, 3);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);// 对认证证书来源的检查
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);// 从证书中检查SSL加密算

        //3)设置提交方式
        switch($type){
            case "GET":
                curl_setopt($ch,CURLOPT_HTTPGET,true);
                break;
            case "POST":
                curl_setopt($ch,CURLOPT_POST,true);
                break;
            case "PUT"://使用一个自定义的请求信息来代替"GET"或"HEAD"作为HTTP请求。这对于执行"DELETE" 或者其他更隐蔽的HTT
                curl_setopt($ch,CURLOPT_CUSTOMREQUEST,"PUT");
                break;
            case "DELETE":
                curl_setopt($ch,CURLOPT_CUSTOMREQUEST,"DELETE");
                break;
        }


        //4)在HTTP请求中包含一个"User-Agent: "头的字符串。-----必设

//		curl_setopt($ch, CURLOPT_USERAGENT, 'SSTS Browser/1.0');
//		curl_setopt($ch, CURLOPT_ENCODING, 'gzip');

        //curl_setopt ( $ch, CURLOPT_USERAGENT, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0)' ); // 模拟用户使用的浏览器
        //5)


        //3.抓取URL并把它传递给浏览器
        $res=curl_exec($ch);

        $result=json_decode($res,true);
        //4.关闭curl资源，并且释放系统资源
        curl_close($ch);

        if(empty($result))

            return $res;
        else
            return $result;

    }



    public function export(){
        $tag_arr = UserTag::column('title','id');
        $get_data = $this->request->get();
        $time = explode(' - ',$get_data['create_at']);
        $phone = $get_data['phone'];
        $name  = $get_data['name'];
        $where = [];
        $where[] = ['status',1];
        $where[] = ['is_deleted',0];
        $where_str = ' status = 1 AND is_deleted = 0';
        if($name) $where_str .=' AND name like '."'%".$name."%'";
        if($phone) $where_str .=' AND phone like '."'%".$phone."%'";
        if($level_id = input('get.level_id')) $where_str .=' AND level_id = '.$level_id;
        if($label_id = input('get.label_id')) $where_str .=' AND label like '."'%|".$label_id."|%'";
        if($get_data['create_at']) $where_str.=" AND  create_at > '".$time[0]."'AND create_at <'".$time[1]."'";
        $data = Db::query("SELECT name,headimg,email,tag_id,phone,create_at,account_type,label FROM dd_store_member WHERE".$where_str.' ORDER BY id DESC');
        if(empty($data)) $this->error('暂无可以导出的数据');
        foreach ($data as  $k=>&$v) {
            if(!$v) $v = '--';
            $v['account_desc'] = $v['account_type'] == 1 ? '公司':'个人';
            $label_ids = implode(',',explode("|",trim($v['label'],'|')));
            $label_arr  = $label_ids ?  UserTag::where('id','in',$label_ids)->column('title') : [];
            $v['label_name'] = $label_arr ? implode('|',$label_arr) : "--";
        }
        $field=array(
            'A' => array('name', '昵称'),
            'B' => array('email', '邮箱'),
            'C' => array('phone', '电话'),
            'D' => array('account_desc','类型'),
            'E' => array('label_name','标签'),
            'F' => array('create_at', '注册时间'),
        );
        $this->phpExcelList($field,$data,'会员列表');
    }


    public function study_export(){
        $get_data = $this->request->get();
        $time = explode(' - ',$get_data['study_at']);
        $where_str = [];
        if(!$get_data['study_at']) $this->error('请选择时间');
        $date = $this->getDatesInRange($time[0],$time[1]);
        $title = '学习记录';
        if(count($date)>30)$this->error('导出记录不能超过30天');
        //生成表格部分
        $obj = new \PHPExcel();// 实例化PHPExcel类，等价于在桌面上新建一个excel表格
        $objSheet = $obj->getActiveSheet(); //获得当前活动sheet的操作对象
        foreach ($date as $k => $v){
            $study = Db::name('user_study_log')->where('create_at','> time',$v.' 00:00:00')
                ->where('create_at','< time',$v.' 23:59:59')
                ->select();
            $studyss = Db::name('user_study_log')->where('create_at','> time',$v.' 00:00:00')
                ->where('create_at','< time',$v.' 23:59:59')
                ->group('user_phone')
                ->select();
            $obj->createSheet();//创建新的内置表
            $obj->setActiveSheetIndex($k); //把新创建的sheet设定为当前活动sheet
            $objSheet = $obj->getActiveSheet(); //获取当前活动sheet
            $objSheet->setTitle($v); //给当前活动sheet设置名称
            //方法二
            $array = [
                [""],
            ];
            if ($study){
//                return json($study);

//                $array[] = [];
                foreach ($study as $sk => $sv){
                    $video = VideoIntro::where('id',$sv['video_intro_id'])->find();
                    $key = array_search($video['title'], $array[0]);
//                    $is_s = 1;
                    if ($key !== false) {
                        //return "找到值，对应的键是: " . $key;for(
                        //return $key;
                    } else {
                        //第一行

                        $array[0][] = $video['title'];
                        $key = array_search($video['title'], $array[0]);


//                        foreach ($array as $ak => $av){
//                            $is_s = 1;
//                            if($ak != 0){
//                                foreach ($array as $ak1 => $av1){
//                                    if(isset($array[$ak1][0]) && $array[$ak1][0] == $sv['user_phone']) {
////                                        return json($array);
//                                        var_dump($sv);
//                                        $is_s = 0;
//                                    }
//                                }
//                                if($is_s == 1 && !isset($array[$ak][0])){
//                                    $array[$ak][0] = $sv['user_phone'];
//                                }else if($is_s == 1){
//                                    $array[] = [$sv['user_phone']];
//                                }
//                                if($av == $video['title']){
//                                    $array[$ak][] = $sv['study_duration'];
////                                    $is_s = 0;
//                                }
//                            }else{
//
//                            }
//                        }
//以下
                    }

                }
                foreach ($studyss as $kss => $vss){
                    $array[] = [$vss['user_phone']];
                }
//                return json($array);
                foreach ($array as $k1 => $v1){
                    if($k1 != 0){
                        foreach ($study as $k3 => $v3) {
                            foreach ($array[0] as $k2 => $v2){
                                $videoIn = VideoIntro::where('id',$v3['video_intro_id'])->find();
                                if($k2 != 0){
//                                $key = array_search($videoIn['title'], $array[0]);
//                                $is_s = 1;
                                    if ($videoIn['title'] == $v2 && $v1[0] == $v3['user_phone']) {
                                        //return "找到值，对应的键是: " . $key;for(
                                        $array[$k1][$k2] = $v3['study_duration'];
                                    }else if($videoIn['title'] != $v2 && $v1[0] == $v3['user_phone'] && !isset($array[$k1][$k2])){
                                        $array[$k1][$k2] = '0';
                                    }
                                }
                            }
                        }
                    }


//                    if($k !== 0){
//                        return json($v2);
//                    }
                }
            }
            $objSheet->fromArray($array); //直接加载数据块来填充数据
        }

        $objWriter = \PHPExcel_IOFactory::createWriter($obj,"Excel2007");
        header('Content-Type: application/vnd.ms-excel'); // 告诉浏览器生成一个excel05版的表格
        header("Content-Disposition: attachment;filename={$title}.xls"); //告诉浏览器输出文件的名称
        header('Cache-Control: max-age=0'); //禁止缓存
        $objWriter->save("php://output"); //输出到浏览器
        $this->success('ok',$date);
    }


    public function phpExcelList($field=[],$list=[],$title='文件'){
        $PHPExcel=new \PHPExcel();
        $PHPSheet=$PHPExcel->getActiveSheet();
        $PHPSheet->setTitle('demo'); //给当前活动sheet设置名称
        foreach($list as $key=>$value)
        {
            foreach($field as $k=>$v){
                if($key == 0){
                    $PHPSheet= $PHPExcel->getActiveSheet()->setCellValue($k.'1',$v[1]);
                }
                $i=$key+2;
                $PHPExcel->getActiveSheet()->setCellValue($k . $i, $value[$v[0]]);
            }
        }
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,'Excel2007'); //按照指定格式生成Excel文件，
        header('Content-Type: application/vnd.ms-excel'); // 告诉浏览器生成一个excel05版的表格
        header("Content-Disposition: attachment;filename={$title}.xls"); //告诉浏览器输出文件的名称
        header('Cache-Control: max-age=0'); //禁止缓存
        $PHPWriter->save("php://output"); //输出到浏览器
    }


    function getDatesInRange($start, $end) {
        $start = new \DateTime($start);
        $end = new \DateTime($end);
        $end->add(new \DateInterval('P1D')); // 添加一天来包含结束日期
        $interval = new \DateInterval('P1D');
        $period = new \DatePeriod($start, $interval, $end);

        $dates = array();
        foreach ($period as $date) {
            $dates[] = $date->format('Y-m-d'); // 按年-月-日格式化日期
        }
        return $dates;
    }

    /**
     * 导入
     * @auth true
     * @menu true
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     * @throws \think\exception\PDOException
     */
    public function import()
    {
        $file = request()->file('file');
        $file_size = $_FILES['file']['size'];
        if ($file_size > 5 * 1024 * 1024) $this->error('文件大小不能超过5M');
        //限制上传表格类型
        $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);
        if ($fileExtendName != 'xls' && $fileExtendName != 'xlsx') $this->error('必须为excel表格，且必须为xls/xlsx格式！');
        $dir = dirname(realpath(dirname($_SERVER['SCRIPT_FILENAME']))) . '/public/upload';
        if (!file_exists($dir)) mkdir($dir, 0777, true);
        $info = $file->move($dir);
        $fileName = $info->getSaveName();
        $filePath = dirname(realpath(dirname($_SERVER['SCRIPT_FILENAME']))) . "/public/upload/{$fileName}";
        /* $reader = \PHPExcel_IOFactory::createReader('Excel2007');
         if(!$reader->canRead($filePath)) $reader = \PHPExcel_IOFactory::createReader('Excel2015');
         */
        $objPHPExcelReader = \PHPExcel_IOFactory::load($filePath);
        $sheet = $objPHPExcelReader->getSheet(0);        // 读取第一个工作表(编号从 0 开始)
        $highestRow = $sheet->getHighestRow();           // 取得总行数
        $arr = array('A','B','C','D','E','F');
        // 一次读取一列
        $res_arr = [];
        for ($row = 2; $row <= $highestRow; $row++) {
            $row_arr = array();
            for ($column = 0 ;$column < count($arr) ; $column++) {
                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                $row_arr[] = $val;
            }
            $res_arr[] = $row_arr;
        }
        $success_num = 0;
        // name account account_type level_id days label
        foreach ($res_arr  as $new_user) {
            if(!trim($new_user['1']) || empty($new_user['1']))continue;
            $check_where=[];
            $check_where[] = $new_user['2'] == 1 ? ['email','=',trim($new_user['1'])]: ['phone','=',trim($new_user['1'])];
            $ck_res = User::where($check_where)->value('id');
            if($ck_res){
                Data::save('UserLevelRank',[
                    'user_id'=>$ck_res,
                    'level_id'=>intval($new_user['3']),
                    'start_time'=>time(),
                    'end_time'=>time()+86400* intval($new_user['4']),
                    'end_date'=>date('Y-m-d H:i:s',time()+86400*intval($new_user['4']))],'user_id',['user_id'=>$ck_res]);
                $success_num++;
                User::where('id',$ck_res)->update(['level_id'=>intval($new_user['3']),'level_exp'=>date('Y-m-d H:i:s',time()+86400*intval($new_user['4'])),'label'=>'|'.$new_user['5'].'|']);
            }else{
                $user_add= [];
                $user_add['account_type'] =  $new_user['2'];
                if($new_user['5']) $user_add['label'] =  '|'.$new_user['5'].'|';
                if( $user_add['account_type']  == 1) {// 公司账号
                    $group_title  = explode('@', $new_user['1'])[1];
                    $group_info = UserGroup::where(['title'=>'@'.$group_title])->find();
                    if(empty($group_info)) continue;
                    $user_add['name'] =  'G企业用户_'.$group_info->name.'_'.$group_title;
                    $check_group = User::where(['group_id'=>$group_info->id,'is_first'=>1])->value('id');//该公司是否有第一个注册的
                    $user_add['group_id']  = $group_info->id;
                    if(!$check_group) $user_add['is_first']  =1;
                    if($check_group) $user_add['group_first']  = $check_group;
                    $add_res  = User::create($user_add);
                    if(isset_full_check($user_add,'is_first',1)){
                        User::where('id',$add_res->id)->update(['group_first'=>$add_res->id]);
                        if($new_user['3'] > 0 && intval($new_user[4]) > 0) {
                            UserLevelRank::create(['user_id'=>$add_res->id,'level_id'=>$new_user['3'],'start_time'=>time(),'end_time'=>time()+86400* intval($new_user['4']),'end_date'=>date('Y-m-d H:i:s',time()+86400*intval($new_user['4']))]);
                            User::where('id',$add_res->id)->update(['level_id'=>intval($new_user['3']),'level_exp'=>date('Y-m-d H:i:s',time()+86400*intval($new_user['4']))]);
                            $success_num++;
                        }
                    }
                }else{
                    $user_add['phone'] = trim($new_user['1']);
                    $user_add['name'] =  'G'. substr_replace( $new_user['1'],'****',3,4);
                    $add_res  = User::create($user_add);
                    if($new_user['3'] > 0 && intval($new_user[4]) > 0) {
                        UserLevelRank::create(['user_id'=>$add_res->id,'level_id'=>$new_user['3'],'start_time'=>time(),'end_time'=>time()+86400* intval($new_user['4']),'end_date'=>date('Y-m-d H:i:s',time()+86400*intval($new_user['4']))]);
                        User::where('id',$add_res->id)->update(['level_id'=>intval($new_user['3']),'level_exp'=>date('Y-m-d H:i:s',time()+86400*intval($new_user['4']))]);
                        $success_num++;
                    }
                }
            }

        }
        $this->success('成功导入会员：'.$success_num);
    }


}