xianglian_test_admin/application/user/controller/Invite.php

<?php


namespace app\user\controller;

use app\common\library\PHPExcelService;
use library\Controller;
use think\Db;

/**
 * 推荐管理
 * Class Invite
 * @package app\user\controller
 */
class Invite extends Controller
{
    protected  $table ="store_member";


    /**
     * 会员信息管理
     * @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->title = '邀请排行榜';
        $create_at = $this->request->get('create_at');
        /* if (isset($create_at) && $create_at){
             $time = explode(' - ',$_GET['create_at']);
             $start_date_time = $time[0].' 00:00:00';
             $end_date_time = $time[1].' 23:59:59';
             $sql = "(select count(b.id) from store_member as b where b.pid=a.id and (b.create_at between '".$start_date_time."' and '".$end_date_time."')) as count";
            // echo $sql;die;
         }else{
             $sql = '(select count(b.id) from store_member as b where b.pid=a.id) as count';
         }*/
        $this->off_set =  input('page',1) * input('limit',20)-  input('limit',20);
        /* $this->_query($this->table.' as a')
             ->field('a.id,a.name,a.phone,a.pid,a.headimg,'.$sql)
             ->group('a.id')
             //->having('count>0')
             ->order('count desc')
             ->like('name,phone')
             ->page();*/

        $name = input('name');
        $phone = input('phone');
        $sel_id = 0;
        $where = 'id > 0';
        if($name || $phone) {
            $se = [];
            if($name) $s[] = ['name','=',$name];
            if($phone) $s[] = ['phone','=',$phone];
            $sel_id = Db::name('store_member')->where($se)->value('id');
            $where = 'id = '.$sel_id;
        }

        $create_at = $this->request->get('create_at');
        if(isset($create_at) && $create_at) {
            $time = explode(' - ',$_GET['create_at']);
            $start_date_time = $time[0].' 00:00:00';
            $end_date_time =  $time[1].' 23:59:59';
            $this->_query($this->table)
                ->field('pid,count(*) count')
                ->where('pid','>',0)
                ->where($where)
                ->whereBetweenTime('create_at',$start_date_time,$end_date_time)
                ->group('pid')
                ->order('count desc')
                ->page();
        }else{
            $this->_query($this->table)
                ->field('pid,count(*) count')
                ->where('pid','>',0)
                ->where($where)
                ->group('pid')
                ->order('count desc')
                ->page();

        }


    }


    protected function _index_page_filter(&$data)
    {
        foreach ($data as &$value){
            $users_info = Db::name('store_member')->field('id,phone,headimg,name')->where('id',$value['pid'])->find();
            $value['id'] = $users_info['id'];
            $value['phone'] = $users_info['phone'];
            $value['name'] = $users_info['name'];
            $value['headimg'] = $users_info['headimg'];
            unset($value['pid']);
        }
    }

    /**
     * 导出EXCL
     * @remark 根据WHERE条件导出EXCL
     * @param array $post 查询条件所需值
     * @return array
     */
    public function get_excl()
    {
        set_time_limit(0);
        $name = $this->request->get('name');
        $phone = $this->request->get('phone');
        $create_at = $this->request->get('create_at');

        if (isset($create_at) && $create_at){
            $time = explode(' - ',$_GET['create_at']);
            $start_date_time = $time[0].' 00:00:00';
            $end_date_time = $time[1].' 23:59:59';
            $sql = "(select count(b.id) from store_member as b where b.pid=a.id and (b.create_at between '".$start_date_time."' and '".$end_date_time."')) as count";
            // echo $sql;die;
        }else{
            $sql = '(select count(b.id) from store_member as b where b.pid=a.id) as count';
        }
        $list = Db::name('store_member as a')
            ->field('a.id,a.name,a.phone,a.pid,a.headimg,'.$sql)
            ->when($name,function ($query) use ($name){
                $query->whereLike('a.name','%'.$name.'%');
            })
            ->when($phone,function ($query) use ($phone){
                $query->whereLike('a.phone','%'.$phone.'%');
            })
            ->group('a.id')
            //->having('count>0')
            ->order('count desc')
            ->select();

        $export = [];
        if (is_array($list)) {
            foreach ($list as $index => $item) {
                $export[] = [
                    $item['phone'],
                    $item['count']
                ];
            }
        }

        PHPExcelService::setExcelHeader(['手机号','邀请数量'])
            ->setExcelTile('手机号导出' . date('YmdHis', time()), '手机号信息' . time(), ' 生成时间：' . date('Y-m-d H:i:s', time()))
            ->setExcelContent($export)
            ->ExcelSave();

    }



    public function power(){
        $ids = input('id');
        var_dump($ids);
        $goods_list =  Db::name('store_collection')->where(['is_deleted'=>0,'status'=>1,'type'=>1])->order('id desc')->column('name','id');
        $this->fetch('',['ids'=>$ids,'goods_list'=>$goods_list]);
    }


}