会议OA项目(待开会议历史会议所有会议)

文章目录

一、会议OA项目名词介绍

二、SQL编写

待开会议SQL

所有会议SQL

三、代码阶段

待开会议

所有会议

一、会议OA项目名词介绍

我的会议：当前登录账号，是某会议主持人，则查询出来
我的审批：当前登录账号，是某会议的指定审批人，并且会议状态是待审核，则查询出来
会议通知：当前登录账号，只要是某会议的参与者、列席者、主持人中的一员，并且该会议未反馈，则查询出来

待开会议：当前登录账号，只要是某会议的参与者、列席者、主持人中的一员，并且会议状态是待开，则查询出来

历史会议：当前登录账号，只要是某会议的参与者、列席者、主持人中的一员，并且会议状态是结束，则查询出来

所有会议：当前登录账号，只要是某会议的参与者、列席者、主持人、审批人中的一员，那么必须查询出来

二、SQL编写

待开会议SQL

待开会议与我的会议的区别：待开会议需要匹配3个数据库列段某会议的参与者、列席者、主持人

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 auditorname 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=1and state=4

所有会议SQL

这条SQL语句提供错误演示，不要运用在项目中👀

所有会议与待开会议的区别：所有会议不需要限制状态，并且需要新增一个审批人列段

select CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',a.auditor),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 auditorname 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

假设：id=6的用户，是会议OA的列席人员，会议OA又处于新建状态，意味着没有审批人，请问这条数据要不要查询出来？

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 auditorname 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=1and FIND_IN_SET(6,CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))

如果当前会议还没有送审也就是没有审批人的情况下，我们需要用ifnull函数设它编号为-1，否则会发生数据丢失

三、代码阶段

待开会议

meetingWaiting.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%@include file="/common/head.jsp"%><!DOCTYPE html><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><script type="text/javascript" src="${pageContext.request.contextPath }/static/js/meeting/meetingWaiting.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"><div class="layui-inline"><label class="layui-form-label">会议标题：</label><div class="layui-input-inline"><input type="hidden" id="userid" value="${sessionScope.user.id }"/><input type="text" id="title" autocomplete="off"class="layui-input"></div></div><div class="layui-inline"><button id="btn_meeting_search" class="layui-btn layui-btn-normal"><i class="layui-icon"></i> 查询</button></div></div><table style="margin-top: -15px;" id="tb_meeting" lay-filter="tb_meeting"></table></body></html>

meetingWaiting.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层

//待开会议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,\r\n" + " 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,\r\n" + " c.name auditorname 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\r\n" + " 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();//初始化pageBeanpageBean.setRequest(req);//调用方法List<Map<String, Object>> lst =infoDao.queryMeetingInfoByState(info, pageBean) ;//layui 的code 返回一定要是 0，不能是200，否者返回不了数据//传输到界面//pageBean.getTotal()总记录数ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询成功",pageBean.getTotal(),lst));} catch (Exception e) {e.printStackTrace();try {ResponseUtil.writeJson(resp, R.error(0, "待开会议数据查询失败"));} catch (Exception e1) {e1.printStackTrace();}}return null;}

页面运行效果

所有会议

meetingAll.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%><%@include file="/common/head.jsp"%><!DOCTYPE html><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><script type="text/javascript" src="${pageContext.request.contextPath }/static/js/meeting/meetingAll.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"><div class="layui-inline"><label class="layui-form-label">会议标题：</label><div class="layui-input-inline"><input type="hidden" id="userid" value="${sessionScope.user.id }"/><input type="text" id="title" autocomplete="off"class="layui-input"></div></div><div class="layui-inline"><button id="btn_meeting_search" class="layui-btn layui-btn-normal"><i class="layui-icon"></i> 查询</button></div></div><table style="margin-top: -15px;" id="tb_meeting" lay-filter="tb_meeting"></table></body></html>

meetingAll.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层

//所有会议public List<Map<String, Object>> allInfos(MeetingInfo info, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {String sql = "select a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren\r\n" + ",b.name zhuchirenname,\r\n" + "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,\r\n" + "c.name auditorname 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\r\n" + "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();//初始化pageBeanpageBean.setRequest(req);//调用方法List<Map<String, Object>> lst =infoDao.allInfos(info, pageBean) ;//layui 的code 返回一定要是 0，不能是200，否者返回不了数据//传输到界面//pageBean.getTotal()总记录数ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询成功",pageBean.getTotal(),lst));} catch (Exception e) {e.printStackTrace();try {ResponseUtil.writeJson(resp, R.error(0, "所有会议数据查询失败"));} catch (Exception e1) {e1.printStackTrace();}}return null;}

页面运行效果

拜了个拜~~

历史会议思路与以上会议思路相差无几，待我实现后即补全