目录：
一，待开会议SQL编写
二，全部会议SQL编写
三，待开会议功能开发
四，所有会议功能开发
一，待开会议SQL编写
待开会议sql编写其实与我的会议sql大同小异，主要区别在于：待开会议需要匹配三个数据库列段
对比一下：
主要区别已用方框标记出来
待开会议SQL:
select CONCAT(canyuze,',',liexize,',',zhuchiren),a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,b.name zhuchirenname,a.location,DATE_FORMAT(a.startTime,'%Y-%m-%d %H-%m-%s') startTime,DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%m-%s') endTime,a.state,(case a.statewhen 0 then '取消会议'when 1 then '新建'when 2 then '待审核'when 3 then '驳回'when 4 then '待开'when 5 then '进行中'when 6 then '开启投票'when 7 then '结束会议'else '其他' end) meetingstate,a.seatPic,a.remark,a.auditor,c.name anditorname from t_oa_meeting_info ainner join t_oa_user b on a.zhuchiren = b.idleft join t_oa_user c on a.auditor = c.id where 1=1 and state =4 and FIND_IN_SET(6,CONCAT(canyuze,',',liexize,',',zhuchiren))
分析：
我们只需要查询出状态为4，即为待开的会议（state=4）
函数find_in_set（y，xxx）y里放用户编号，xxx中放的包括参与者以及列席人员和主持人的编号，如果用户的编号存在于后面三个字段中就查询出来，意思就是该用户存在于这个会议中，是参会人员中的一员
二，全部会议SQL编写
与待开会议的区别已用方框圈出
全部会议不需要限制状态，并且需要新增一个审批人列段
注意：如果当前会议还没有送审也就是没有审批人的情况下，我们需要用ifnull函数设它编号为-1，否则会发生数据丢失
全部会议SQL:
select CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)),a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,b.name zhuchirenname,a.location,DATE_FORMAT(a.startTime,'%Y-%m-%d %H-%m-%s') startTime,DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%m-%s') endTime,a.state,(case a.statewhen 0 then '取消会议'when 1 then '新建'when 2 then '待审核'when 3 then '驳回'when 4 then '待开'when 5 then '进行中'when 6 then '开启投票'when 7 then '结束会议'else '其他' end) meetingstate,a.seatPic,a.remark,a.auditor,c.name anditorname from t_oa_meeting_info ainner join t_oa_user b on a.zhuchiren = b.idleft join t_oa_user c on a.auditor = c.id where 1=1 and FIND_IN_SET(6,CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))
三，待开会议功能开发
第一步我们先导入待开会议的jsp页面与js文件
全部会议jsp页面
js文件
代码展示：
let layer,form,table,$;var row;layui.use(['layer','form','table'],function(){layer=layui.layer,form=layui.form,table=layui.table,$=layui.jquery;//初始化会议列表initMeeting();//绑定查询按钮的点击事件$('#btn_meeting_search').click(function(){query();});});//1.初始化会议列表function initMeeting(){table.render({ //执行渲染elem: '#tb_meeting', //指定原始表格元素选择器（推荐id选择器）height: 400, //自定义高度loading: false,//是否显示加载条（默认 true）cols: [[ //设置表头{field: 'title', title: '会议标题', width: 180},{field: 'location', title: '会议地点', width: 120},{field: 'startTime', title: '开始时间', width: 180},{field: 'endTime', title: '结束时间', width: 180},{field: 'meetingstate', title: '会议状态', width: 90},{field: 'zhuchirenname', title: '主持人', width: 120},//{field: '', title: '操作', width: 260, toolbar: '#tbMeeting'}]]});}//2.待开会议function query(){table.reload('tb_meeting', {url: 'info.action',//请求地址method: 'POST',//请求方式，GET或者POSTloading: true, //是否显示加载条（默认 true）page: true, //是否分页where: { //设定异步数据接口的额外参数，任意设'methodName':'queryMeetingInfoByState','title':$('#title').val(),'zhuchiren':$('#userid').val(),'state':4},request: {//自定义分页请求参数名pageName: 'page', //页码的参数名称，默认：pagelimitName: 'rows' //每页数据量的参数名，默认：limit},done: function (res, curr, count) {//查询完成的回调函数}});}
导入成功后我们开始编写dao方法，将我们的sql语句放入
//待开会议public List<Map<String, Object>> queryMeetingInfoByState(MeetingInfo info, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {String sql=" select CONCAT(canyuze,',',liexize,',',zhuchiren),a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,\r\n" + " b.name zhuchirenname,a.location,\r\n" + " DATE_FORMAT(a.startTime,'%Y-%m-%d %H-%m-%s') startTime,\r\n" + " DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%m-%s') endTime,\r\n" + " a.state,\r\n" + " (\r\n" + " case a.state\r\n" + " when 0 then '取消会议'\r\n" + " when 1 then '新建'\r\n" + " when 2 then '待审核'\r\n" + " when 3 then '驳回'\r\n" + " when 4 then '待开'\r\n" + " when 5 then '进行中'\r\n" + " when 6 then '开启投票'\r\n" + " when 7 then '结束会议'\r\n" + " else '其他' end\r\n" + " ) meetingstate,\r\n" + " a.seatPic,a.remark,a.auditor,c.name anditorname from t_oa_meeting_info a\r\n" + " inner join t_oa_user b on a.zhuchiren = b.id\r\n" + " left join t_oa_user c on a.auditor = c.id where 1=1 and state =4 and FIND_IN_SET("+info.getZhuchiren()+",CONCAT(canyuze,',',liexize,',',zhuchiren))\r\n" + " ";return super.executeQuery(sql, pageBean);}
在web层调用：
//待开会议public String queryMeetingInfoByState(HttpServletRequest req, HttpServletResponse resp) {try {PageBean pageBean = new PageBean();pageBean.setRequest(req);List<Map<String, Object>> lst = infoDao.queryMeetingInfoByState(info, pageBean);//注意：layui中的数据表的格式ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询成功",pageBean.getTotal(),lst));} catch (Exception e) {e.printStackTrace();try {ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询失败"));} catch (Exception e1) {e1.printStackTrace();}}return null;}
四，所有会议功能开发
导入全部会议jsp页面
js页面：
代码展示：
let layer,form,table,$;var row;layui.use(['layer','form','table'],function(){layer=layui.layer,form=layui.form,table=layui.table,$=layui.jquery;//初始化会议列表initMeeting();//绑定查询按钮的点击事件$('#btn_meeting_search').click(function(){query();});});//1.初始化会议列表function initMeeting(){table.render({ //执行渲染elem: '#tb_meeting', //指定原始表格元素选择器（推荐id选择器）height: 400, //自定义高度loading: false,//是否显示加载条（默认 true）cols: [[ //设置表头{field: 'title', title: '会议标题', width: 180},{field: 'location', title: '会议地点', width: 120},{field: 'startTime', title: '开始时间', width: 180},{field: 'endTime', title: '结束时间', width: 180},{field: 'meetingstate', title: '会议状态', width: 90},{field: 'zhuchirenname', title: '主持人', width: 120},//{field: '', title: '操作', width: 260, toolbar: '#tbMeeting'}]]});}//2.查询所有会议function query(){table.reload('tb_meeting', {url: 'info.action',//请求地址method: 'POST',//请求方式，GET或者POSTloading: true, //是否显示加载条（默认 true）page: true, //是否分页where: { //设定异步数据接口的额外参数，任意设'methodName':'allInfos','title':$('#title').val(),'zhuchiren':$('#userid').val()},request: {//自定义分页请求参数名pageName: 'page', //页码的参数名称，默认：pagelimitName: 'rows' //每页数据量的参数名，默认：limit},done: function (res, curr, count) {//查询完成的回调函数}});}
开始编写dao方法，将我们的sql语句放入
//所有会议public List<Map<String, Object>> allInfos(MeetingInfo info, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {String sql=" select CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)),a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,\r\n" + " b.name zhuchirenname,a.location,\r\n" + " DATE_FORMAT(a.startTime,'%Y-%m-%d %H-%m-%s') startTime,\r\n" + " DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%m-%s') endTime,\r\n" + " a.state,\r\n" + " (\r\n" + " case a.state\r\n" + " when 0 then '取消会议'\r\n" + " when 1 then '新建'\r\n" + " when 2 then '待审核'\r\n" + " when 3 then '驳回'\r\n" + " when 4 then '待开'\r\n" + " when 5 then '进行中'\r\n" + " when 6 then '开启投票'\r\n" + " when 7 then '结束会议'\r\n" + " else '其他' end\r\n" + " ) meetingstate,\r\n" + " a.seatPic,a.remark,a.auditor,c.name anditorname from t_oa_meeting_info a\r\n" + " inner join t_oa_user b on a.zhuchiren = b.id\r\n" + " left join t_oa_user c on a.auditor = c.id where 1=1 and FIND_IN_SET("+info.getZhuchiren()+",CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))";return super.executeQuery(sql, pageBean);}
在web层进行调用：
//所有会议public String allInfos(HttpServletRequest req, HttpServletResponse resp) {try {PageBean pageBean = new PageBean();pageBean.setRequest(req);List<Map<String, Object>> lst = infoDao.allInfos(info, pageBean);//注意：layui中的数据表的格式ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询成功",pageBean.getTotal(),lst));} catch (Exception e) {e.printStackTrace();try {ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询失败"));} catch (Exception e1) {e1.printStackTrace();}}return null;}
最终效果展示：
所有会议：
待开会议：
