目录
一、需要的SQL语句
1.1 会议通知的SQL
分析:不管会议是否得到反馈 都要反馈出来 所以选用外连接 会议信息表为主
-- 会议通知 (4即为待开的会议)
select
IFNULL(f.result,-1) result,t1.*
from(
select * from t_oa_meeting_info where FIND_IN_SET(2,CONCAT(
canyuze,',',liexize,',',zhuchiren)) and state = 4
) t1
left join t_oa_meeting_feedback f on t1.id = f.meetingId
and f.personId = 2
ORDER BY result;
1.2 反馈详情的SQL
根据需求我们可以一步步分析实现这个SQL:
-- 会议反馈详情
-- 1、会议id为12的会议,所有参与人员的姓名
-- 1.1 先拿到所有的参与人员的id
select CONCAT(canyuze,',',liexize,',',zhuchiren) from
t_oa_meeting_info where id = 12
-- 1.2 再拿到所有的参与人员的姓名
select * from t_oa_user where FIND_IN_SET(id,(select CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))
-- 2、链接反馈表,拿到对应的反馈情况(未读、参加、不参加)
-- 2、链接反馈表,拿到对应的反馈情况(未读、参加、不参加)
select
t1.`name`,IFNULL(f.result,-1) result
from(
select * from t_oa_user where FIND_IN_SET(id,(select CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))
) t1
left join t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = 12
-- 3、根据反馈情况进行分组 (最终反馈详情的SQL)
-- 3、根据反馈情况进行分组(最终反馈详情的SQL)
select
t.result,GROUP_CONCAT(t.name) names
from(
select
t1.`name`,IFNULL(f.result,-1) result
from(
select * from t_oa_user where FIND_IN_SET(id,(select CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))
) t1
left join t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = 12
) t
GROUP BY t.result
二、会议通知的前台代码
2.1 会议通知的jsp文件
会议通知的前台代码:meetingNotify.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="/common/header.jsp"%>
<!DOCTYPE html>
<!-- 会议通知 -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="static/js/meeting/meetingNotify.js"></script>
</head>
<style>
body{
margin:15px;
}
.layui-table-cell {height: inherit;}
.layui-layer-page .layui-layer-content { overflow: visible !important;}
</style>
<body>
<!-- 搜索栏 -->
<div class="layui-form-item" style="margin:15px 0px;">
<div class="layui-inline">
<label class="layui-form-label">会议标题</label>
<div class="layui-input-inline">
<input type="hidden" id="personId" value="${user.id }"/>
<input type="text" id="title" autocomplete="off" class="layui-input">
</div>
</div>
<div class="layui-inline">
<button id="btn_search" type="button" class="layui-btn"><i class="layui-icon layui-icon-search"></i> 查询</button>
</div>
</div>
<!-- 数据表格 -->
<table id="tb" lay-filter="tb" class="layui-table" style="margin-top:-15px"></table>
<script type="text/html" id="tbar">
{
{# if(d.result==-1){ }}
<a class="layui-btn layui-btn-xs" lay-event="edit">是否参会</a>
{
{# } }}
</script>
</body>
</html>
2.2 要封装的js文件
我们再封装好它对应的js文件:meetingNotify.js
let layer,table,$,form,test;
var row;
layui.use(['layer','table','jquery','form','test'],function(){
layer=layui.layer,
table=layui.table,
form=layui.form,
test=layui.test,
$=layui.jquery;
initTable();
//查询事件
$('#btn_search').click(function(){
query();
});
});
//初始化数据表格(我的审批)
function initTable(){
table.render({ //执行渲染
elem: '#tb', //指定原始表格元素选择器(推荐id选择器)
height: 400, //自定义高度
loading: false, //是否显示加载条(默认 true)
cols: [[ //设置表头
{field: 'id', title: '会议编号', width: 90},
{field: 'title', title: '会议标题', width: 120},
{field: 'location', title: '会议地点', width: 140},
{field: 'startTime', title: '开始时间', width: 120,
templet:function(d){
return test.toDate(new Date(d.startTime));
}
},
{field: 'endTime', title: '结束时间', width: 120,
templet:function(d){
return test.toDate(new Date(d.endTime));
}
},
//{field: 'meetingState', title: '会议状态', width: 120},
/*{field: 'seatPic', title: '会议排座', width: 120,
templet: function(d){
if(d.seatPic==null || d.seatPic=="")
return "尚未排座";
else
return "<img width='120px' src='"+d.seatPic+"'/>";
}
},*/
{field: 'result', title: '反馈状态', width: 120,
templet: function(d){
if(d.result==1)
return "参会";
else if(d.result==2)
return "缺席";
else
return "未读";
}
},
{field: '', title: '操作', width: 200,toolbar:'#tbar'},
]]
});
}
//点击查询
function query(){
table.reload('tb', {
url: $("#ctx").val()+'/feedBack.action', //请求地址
method: 'POST', //请求方式,GET或者PO