基于layUI动态数据表格。创建后台控制台数据统计。并添加分页以及查询

PS：这种写法就是典型的api接口，不会写接口的看完了就知道怎么去写接口，需要的可以收藏保存哦，制作不易需要的大大们稍微打赏一下，支持一下小弟，后面会放更多干货，您的支持就是我的动力，顺便推广一下我的火车头插件哈哈https://www.xunruicms.com/shop/app/529.html，需要的可以去插件商城下载，有问题随时找我，知无不言。

第一步：创建插件

创建插件必须的几个文件为

/dayrui/App/插件目录/Config/App.php

/dayrui/App/插件目录/Config/Version.php

可以先用开发者工具插件去创建一个应用控制器然后讲这两个文件加入进去，这样插件就创建完成

app.php代码
<?php

return [
    'type' => 'app',
    'name' => '学员信息统计',
    'icon' => 'fa fa-bar-chart-o (alias)',
    'author' => '琉惗', // 作者名字
];

Version.php代码
<?php

return [
    'id' => '399',
    'version' => '2.1.1',
    'license' => '',
    'updatetime' => '2020-02-17',
    'downtime' => '2020-02-17',
];

第二步，创建后台面板文件，具体操作方法请查看手册https://www.xunruicms.com/doc/719.html

第三步，在模版文件中使用layui创建表格，代码如下

<link rel="stylesheet" href="./static/layui/css/layui.css"  media="all">

<div class="col-md-6 column sortable" style="width: 100%;padding: 0 0 0 0" id="6-3">

    <div class="portlet portlet-sortable light bordered">
        <div style="border-bottom: 0px solid #eef1f5;" class="portlet-title tabbable-line">
            <div class="caption">
                <i class="fa fa-bar-chart-o (alias)"></i>
                <span class="caption-subject">学生数据统计</span>
            </div>
        </div>
        <div class="portlet-body">
            <div class="layui-tab layui-tab-brief" lay-filter="docDemoTabBrief">
                <ul class="layui-tab-title">
                    <li class="layui-this">总数据</li>
                    <li>客户统计</li>
                </ul>
                <div class="layui-tab-content">
                    <div class="layui-tab-item layui-show">
                        <table class="layui-hide" id="test"></table>
                    </div>
                    <div class="layui-tab-item">
                        <div class="demoTable">
                            客户名称：
                            <div class="layui-inline layui-form">
                                <select name="name" id="name">
                                    <option value=""></option>
                                    {linkage code=client}
                                        <option value="{$t.ii}">{$t.name}</option>
                                    {/linkage}
                                </select>
                            </div>
                            <button class="layui-btn" data-type="reload">搜索</button>
                        </div>
                        <table id="client"></table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>
<script src="./static/layui/layui.js" charset="utf-8"></script>

<script>
    layui.use(['element','table'], function(){
        var $ = layui.jquery
            ,element = layui.element
            ,table = layui.table; //Tab的切换功能，切换事件监听等，需要依赖element模块

        table.render({
            elem: '#test'
            ,url:'{dr_url("Statistics/home/statistic")}'
            ,cols: [[
                {field:'total', title: '总报名人数', align:'center', sort: true}
                ,{field:'day', title: '每日人数', align:'center', sort: true}
                ,{field:'enroll', title: '已录取人数', align:'center', sort: true}
                ,{field:'enroll_no', title: '未录取人数', align:'center', sort: true}
            ]]
        });

        table.render({
            elem: '#client'
            ,url: '{dr_url('Statistics/home/client')}' //数据接口
            ,page: true //开启分页
            ,cols: [
                [ //表头
                    {field: 'name', title: '客户姓名', rowspan: 2, align: 'center', fixed: 'center'}
                    ,{field: 'day', title: '每日人数', rowspan: 2, align: 'center', fixed: 'center'}
                    ,{field: 'enter', title: '总报名人数', rowspan: 2, align: 'center', fixed: 'center'}
                    ,{ title: '学校信息', colspan: 3, align: 'center', fixed: 'center'}
                    ,{ title: '录取状态', colspan: 2, align: 'center', fixed: 'center'}
                    ,{ title: '学籍状态', colspan: 3, align: 'center', fixed: 'center'}
                ]
                ,[

                    {field: 'school', title: '学校名称', align: 'center', fixed: 'center'}
                    ,{field: 'undergraduate', title: '专科', align: 'center', fixed: 'center'}
                    ,{field: 'specialist', title: '本科', align: 'center', fixed: 'center'}
                    ,{field: 'enroll', title: '已录取', align: 'center', fixed: 'center'}
                    ,{field: 'noenroll', title: '未录取', align: 'center', fixed: 'center'}
                    ,{field: 'inschool', title: '在校人数', align: 'center', fixed: 'center'}
                    ,{field: 'graduate', title: '毕业人数', align: 'center', fixed: 'center'}
                    ,{field: 'outschool', title: '退学人数', align: 'center', fixed: 'center'}
                ]
            ]
            ,id: 'clientReload'
        });

        var $ = layui.$, active = {
            reload: function(){
                var demoReload = $('#name');

                //执行重载
                table.reload('clientReload', {
                    page: {
                        curr: 1 //重新从第 1 页开始
                    }
                    ,where: {
                        key: {
                            name: demoReload.val()
                        }
                    }
                }, 'data');
            }
        };

        $('.demoTable .layui-btn').on('click', function(){
            var type = $(this).data('type');
            active[type] ? active[type].call(this) : '';
        });
    });
</script>

第四步，写后台逻辑代码，并将数据进行返回

public function statistic() {
        $data = \Phpcmf\Service::M()->db->table(SITE_ID.'_student')
            ->select('inputtime,enroll')
            ->get()->getResultArray();

        $total = count($data);

        $day = count(\Phpcmf\Service::M()->db->table(SITE_ID.'_student')
            ->select('inputtime,enroll')
            ->where('DATEDIFF(from_unixtime(inputtime),now())=0')
            ->get()->getResultArray());

        $enroll = count(\Phpcmf\Service::M()->db->table(SITE_ID.'_student')
            ->select('enroll')
            ->where('enroll', 1)
            ->get()->getResultArray());

        $enroll_no = count(\Phpcmf\Service::M()->db->table(SITE_ID.'_student')
            ->select('enroll')
            ->where('enroll', 0)
            ->get()->getResultArray());

        return json_encode([
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => [
                    [
                    'total' => $total,
                    'day' => $day,
                    'enroll' => $enroll,
                    'enroll_no' => $enroll_no
                ]
            ]
        ]);
    }

    public function client() {
	    $page = $this->request->getGet('page');
	    $size = $this->request->getGet('limit');
	    $keyword = $this->request->getGet('key');
	    if(empty($keyword['name'])) {
           $where = '';
            $total = count(\Phpcmf\Service::M()->db->query('SELECT a.id as clientid, a.name as clientname, b.id as schoolid, b.name as schoolname, b.childids as schoolchildids, b.class as schoolclass FROM info_linkage_data_5 a INNER JOIN info_linkage_data_3 b ON b.pid = 0')->getResultArray());
        }else{
            $where = ' AND a.id = '.$keyword['name'];
            $total = count(\Phpcmf\Service::M()->db->query('SELECT a.id as clientid, a.name as clientname, b.id as schoolid, b.name as schoolname, b.childids as schoolchildids, b.class as schoolclass FROM info_linkage_data_5 a INNER JOIN info_linkage_data_3 b ON b.pid = 0'.$where)->getResultArray());
        }

	    $up = ($page-1)*$size;

        $array_info = \Phpcmf\Service::M()->db->query('SELECT a.id as clientid, a.name as clientname, b.id as schoolid, b.name as schoolname, b.childids as schoolchildids, b.class as schoolclass FROM info_linkage_data_5 a INNER JOIN info_linkage_data_3 b ON b.pid = 0'.$where.' ORDER BY clientid asc,b.displayorder ASC limit '.$up.','.$size.'')->getResultArray();

        foreach($array_info as $key => $arr_in) {
            $data[$key]['school'] = $arr_in['schoolname'];
            $data[$key]['name'] = $arr_in['clientname'];

            $data[$key]['enter'] = count(\Phpcmf\Service::M()->db->table(SITE_ID . '_student')
                ->select('client')
                ->where('client', $arr_in['clientid'])
                ->where('school in ('.$arr_in['schoolchildids'].')')
                ->get()->getResultArray());

            $data[$key]['day'] = count(\Phpcmf\Service::M()->db->table(SITE_ID.'_student')
                ->select('inputtime')
                ->where('client', $arr_in['clientid'])
                ->where('school in ('.$arr_in['schoolchildids'].')')
                ->where('DATEDIFF(from_unixtime(inputtime),now())=0')
                ->get()->getResultArray());

            $data[$key]['inschool'] = count(\Phpcmf\Service::M()->db->table(SITE_ID . '_student')
                ->select('client')
                ->where('client', $arr_in['clientid'])
                ->where('school in ('.$arr_in['schoolchildids'].')')
                ->where('inschool', 0)
                ->get()->getResultArray());

            $data[$key]['outschool'] = count(\Phpcmf\Service::M()->db->table(SITE_ID . '_student')
                ->select('client')
                ->where('client', $arr_in['clientid'])
                ->where('school in ('.$arr_in['schoolchildids'].')')
                ->where('inschool', 2)
                ->get()->getResultArray());

            $data[$key]['graduate'] = count(\Phpcmf\Service::M()->db->table(SITE_ID . '_student')
                ->select('client')
                ->where('client', $arr_in['clientid'])
                ->where('school in ('.$arr_in['schoolchildids'].')')
                ->where('inschool', 1)
                ->get()->getResultArray());

            $data[$key]['specialist'] = count(\Phpcmf\Service::M()->db->query('SELECT a.id,b.name,b.class FROM info_'.SITE_ID.'_student a INNER JOIN info_linkage_data_3 b ON a.client = '.$arr_in['clientid'].' AND a.school = b.id AND a.school IN ('.$arr_in['schoolchildids'].') AND b.class = 2')->getResultArray());

            $data[$key]['undergraduate'] = count(\Phpcmf\Service::M()->db->query('SELECT a.id,b.name,b.class FROM info_'.SITE_ID.'_student a INNER JOIN info_linkage_data_3 b ON a.client = '.$arr_in['clientid'].' AND a.school = b.id AND a.school IN ('.$arr_in['schoolchildids'].') AND b.class = 1')->getResultArray());

            $data[$key]['enroll'] = count(\Phpcmf\Service::M()->db->table(SITE_ID . '_student')
                ->select('client')
                ->where('client', $arr_in['clientid'])
                ->where('school in ('.$arr_in['schoolchildids'].')')
                ->where('enroll', 1)
                ->get()->getResultArray());

            $data[$key]['noenroll'] = count(\Phpcmf\Service::M()->db->table(SITE_ID . '_student')
                ->select('client')
                ->where('client', $arr_in['clientid'])
                ->where('school in ('.$arr_in['schoolchildids'].')')
                ->where('enroll', 0)
                ->get()->getResultArray());
        }


        return json_encode([
            'code' => 0,
            'msg' => '',
            'count' => $total,
            'data' => $data
        ]);
    }

PS：具体接口数据格式如下图所示，是一个二维数组转化成的json数据