群组概括信息页面:
/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"></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"></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"></i>
<div class="layui-timeline-content layui-text">
<h1 style="color: #009688" class="layui-timeline-title">最近发言内容</h1>
<p>
${lastMsgText}
</p>
</div>
</li>
</ul>
</div>
页面展示