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

群组概括信息页面：

/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-Privatewhile (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'unionselect 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 speakerint 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>

页面展示