飞讯管理员端群组部分完整版实现

群组概括信息页面：

/GroupBrief

需要展示有关群组的概括信息，包括群组的组成占比，群组数量，不同群组的数量，均可视化展示

群组有六个种类

Public
Work
Meeting
AVChatRoom
Community
Private

使用一个一维数组存储其每一个的数量

数据库中有关表为创建群组表create与注销群组表destroyed

每个表都包含以下信息

CallbackCommand	String	回调命令
GroupId	String	操作的群 ID
Operator_Account	String	发起创建群组请求的操作者 UserID
Owner_Account	String	请求创建的群的群主 UserID
Type	String	请求创建的 群组类型介绍，例如 Public
Name	String	请求创建的群组的名称
MemberList	Array	请求创建的群组的初始化成员列表

所以需要查询在create表中而不在destroy表中的记录

select a.type,count(*) count from
(select distinct callbackaftercreategroup.groupId,callbackaftercreategroup.type from
callbackaftercreategroup left join callbackaftergroupdestroyed
on callbackaftercreategroup.groupId=callbackaftergroupdestroyed.groupId
where callbackaftergroupdestroyed.groupId is null)a
group by a.type

执行后使用数组存储

int[] type= new int[6];//0-Public,1-Work,2-Meeting,3-AVChatRoom,4-Community,5-Private
            while (rs.next()){
                switch (rs.getString("type")){
                    case "Public" :type[0]=rs.getInt("count");break;
                    case "Work" :type[1]=rs.getInt("count");break;
                    case "Meeting" :type[2]=rs.getInt("count");break;
                    case "AVChatRoom" :type[3]=rs.getInt("count");break;
                    case "Community" :type[4]=rs.getInt("count");break;
                    case "Private" :type[5]=rs.getInt("count");break;

                }
            }

前端通过Highlights渲染图表

html与js

 <div class="layui-col-md4">

                    <div id="container2" style="height: 400px"></div>
                </div>
                <div class="layui-col-md4">
                    <div id="container" style="height: 400px">
                        
                    </div>
                    <script>
                        var chart = Highcharts.chart('container',{
                            chart: {
                                type: 'column'
                            },
                            title: {
                                text: '群组数量'
                            },
                            subtitle: {
                                text: ''
                            },
                            xAxis: {
                                categories: [
                                    '陌生人社交群','好友工作群','临时会议群','直播群','社群','私有群'
                                ],
                                crosshair: true
                            },
                            yAxis: {
                                min: 0,
                                title: {
                                    text: '数量'
                                }
                            },

                            plotOptions: {
                                column: {
                                    borderWidth: 0
                                }
                            },
                            series: [{
                                name:"群组数量",
                                data: [${publicNum},${workNum},${meetingNum},${AVChatRoomNum},${communityNum},${privateNum}]
                            }]
                        });

                        var chart = Highcharts.chart('container2', {
                            chart: {
                                type: 'pie',
                                plotBackgroundColor: null,
                                plotBorderWidth: null,
                                plotShadow: false,
                            },
                            title: {
                                text: '群组占比数量'
                            },
                            subtitle: {
                                text: ''
                            },
                            tooltip: {
                                pointFormat: '{series.name}: <b>{point.percentage:.1f}%</b>'
                            },
                            plotOptions: {
                                pie: {
                                    allowPointSelect: true,
                                    cursor: 'pointer',
                                    dataLabels: {
                                        enabled: false
                                    },
                                    showInLegend: true
                                }
                            },
                            series: [{
                                name: '占比',
                                colorByPoint: true,
                                data: [{
                                    name: '陌生人社交群',
                                    y: ${publicNum*100/total},
                                    sliced: true,
                                    selected: true
                                }, {
                                    name: '好友工作群',
                                    y: ${workNum*100/total}
                                }, {
                                    name: '临时会议群',
                                    y: ${meetingNum*100/total}
                                }, {
                                    name: '直播群',
                                    y: ${AVChatRoomNum*100/total}
                                }, {
                                    name: '社群',
                                    y: ${communityNum*100/total}
                                }, {
                                    name: '私有群',
                                    y: ${privateNum*100/total}
                                }]
                            }]
                        });

                    </script>
                </div>

呈现结果

 页面整体效果

 群组表格页面

获取每个群组与上述数据库相同，获取在create表而不在destroy表中的数据

而搜索功能需要根据对应的列进行搜索，通过获取find数据确定搜索类型，搜索和data有关的数据

var url="${group.groupId}";
var res=encodeURIComponent(url);
document.getElementById("${group.groupId}").innerHTML="<a href=/GroupDetail?groupId="+res+">查看</a>"

switch (find){
            case "groupId":glist= dao.GroupListById(data);break;
            case "operator_Account":glist=dao.GroupListByOperator(data);break;
            case "owner_Account":glist=dao.GroupListByOwner(data);break;
            case "name":glist=dao.GroupListByName(data);break;
            default:break;
        }

以groupId为例进行搜索，支持模糊搜索，sql为

"select * from callbackaftercreategroup\n" +
                    "where callbackaftercreategroup.groupId\n" +
                    "          not in (select groupId from callbackaftergroupdestroyed)\n" +
                    "    and callbackaftercreategroup.groupId like \'%"+Id+"%\'\n" +
                    "group by callbackaftercreategroup.groupId"

页面展示

 群组详细信息页面

需要获取群组内的成员，群组内成员活跃度，群组内最近发送的消息

对于成员，需要包含creat表中包含的成员、join表的成员与不在exit表中的成员

以@TGS#1INCWHDIF为例

select * from user
where user.userid in
      (select a.member_Account from callbackaftercreategroup a where groupId='@TGS#1INCWHDIF'
       union
       select b.member_Account from callbackafternewmemberjoin b where groupId='@TGS#1INCWHDIF')
  and user.userid not in
      (
          select callbackaftermemberexit.member_Account from callbackaftermemberexit where groupId='@TGS#1INCWHDIF'
      )

获取最近的消息，包括发言人，时间，文本信息

需要在text表中查询，用一维数组msg[4]存储

 String sql = "select * from(select * from callbackaftersendmsggrouptext where\n" +
                    "        groupId=\'"+groupId+"\'\n" +
                    "order by msgTime desc limit 9999\n" +
                    ") a\n" +
                    "group by a.from_Account";

String[] type= new String[4];//0-recent speaker nums,1-last msg time,2-las meg text,3-last speaker
            int nums = 0;
            if(rs.next()) {
                nums++;
                Timestamp time = rs.getTimestamp("msgTime");
                type[1] = time.toString();
                type[2] = rs.getString("text");
                type[3] = rs.getString("from_Account");
            }
            while (rs.next()){
                nums++;
            }
            type[0]=""+nums;
            return type;

活跃度为群内最近发言人数与总人数比，总人数用获取到userList.length()

前端展示

 <div class="layui-col-md4">
                    <ul class="layui-timeline">
                        <li class="layui-timeline-item">
                            <i class="layui-icon layui-timeline-axis">&#xe63f;</i>
                            <div class="layui-timeline-content layui-text">
                                <h1 style="color: #009688" class="layui-timeline-title">最近发言时间</h1>
                                <p>
                                    ${lastMsgTime}
                                </p>
                            </div>
                        </li>
                        <li class="layui-timeline-item">
                            <i class="layui-icon layui-timeline-axis">&#xe63f;</i>
                            <div class="layui-timeline-content layui-text">
                                <h1 style="color: #009688" class="layui-timeline-title">最近发言人</h1>
                                <p>
                                    ${lastSpeaker}

                                </p>

                            </div>
                        </li>
                        <li class="layui-timeline-item">
                            <i class="layui-icon layui-timeline-axis">&#xe63f;</i>
                            <div class="layui-timeline-content layui-text">
                                <h1 style="color: #009688" class="layui-timeline-title">最近发言内容</h1>
                                <p>
                                    ${lastMsgText}
                                </p>
                            </div>
                        </li>

                    </ul>

                </div>

页面展示