目录
一、SQL语句的编写
1.1 待开会议的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.state
when 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 a
inner join t_oa_user b on a.zhuchiren = b.id
left join t_oa_user c on a.auditor = c.id where 1=1
and state = 4 and FIND_IN_SET(1,CONCAT(
canyuze,',',liexize,',',zhuchiren))1.2 所有会议的SQL
然后我们分析所有会议的SQL语句和待开会议极为相似，就是多了一个审批者的数据库字段；所以我们就copy一份上面分析的来的SQL代码，修改一下就好啦。
就是要注意：要用IF NULL判断，不然就会因为有为空的审批人而丢失数据！
-- 所有会议
select 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.state
when 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 a
inner join t_oa_user b on a.zhuchiren = b.id
left join t_oa_user c on a.auditor = c.id where 1=1
and FIND_IN_SET(1,CONCAT(
a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))二、前端页面
2.1待开会议 meetingWaiting.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/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>2.2所有会议 meetingAll.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/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>三、需要封装的Js代码
3.1待开会议 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或者POST
loading: true, //是否显示加载条（默认 true）
page: true, //是否分页
where: { //设定异步数据接口的额外参数，任意设
'methodName':'queryMeetingInfoByState',
'title':$('#title').val(),
'zhuchiren':$('#userid').val(),
'state':4
},
request: { //自定义分页请求参数名
pageName: 'page', //页码的参数名称，默认：page
limitName: 'rows' //每页数据量的参数名，默认：limit
},
done: function (res, curr, count) {
//查询完成的回调函数
}
});
}3.2所有会议 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或者POST
loading: true, //是否显示加载条（默认 true）
page: true, //是否分页
where: { //设定异步数据接口的额外参数，任意设
'methodName':'allInfos',
'title':$('#title').val(),
'zhuchiren':$('#userid').val()
},
request: { //自定义分页请求参数名
pageName: 'page', //页码的参数名称，默认：page
limitName: 'rows' //每页数据量的参数名，默认：limit
},
done: function (res, curr, count) {
//查询完成的回调函数
}
});
}四、后台代码的编写
4.1 待开会议后台
我们编写Dao方法：
//待开会议
public List<Map<String, Object>> queryMeetingInfoByState(MeetingInfo mi, PageBean pageBean) throws Exception {
String sql = "select CONCAT(\r\n" +
" 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("+mi.getZhuchiren()+",CONCAT(\r\n" +
" canyuze,',',liexize,',',zhuchiren))";
return super.executeQuery(sql, pageBean);
}
再就是Action层：
//待开会议
public String queryMeetingInfoByState(HttpServletRequest req, HttpServletResponse resp) {
try {
PageBean pageBean = new PageBean();
//初始化
pageBean.setRequest(req);
List<Map<String, Object>> list = mid.queryMeetingInfoByState(mi, pageBean);
ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询成功" ,pageBean.getTotal() ,list));
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询失败"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}4.2 所有会议后台
然后就是所有会议查询的后台代码了，这个和上面的待开会议的后台代码是一样步骤的：
依然是先写Dao方法：
//所有会议
public List<Map<String, Object>> allInfos(MeetingInfo mi, PageBean pageBean) throws Exception {
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("+mi.getZhuchiren()+",CONCAT(\r\n" +
" a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))";
return super.executeQuery(sql, pageBean);
}然后就是Action层：
//所有会议
public String allInfos(HttpServletRequest req, HttpServletResponse resp) {
try {
PageBean pageBean = new PageBean();
//初始化
pageBean.setRequest(req);
List<Map<String, Object>> list = mid.allInfos(mi, pageBean);
ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询成功" ,pageBean.getTotal() ,list));
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询失败"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}五、测试查询
5.1 测试
上面的代码全部编写好了后，我们就可以来测试一下了！
待开会议：
所有会议：
所有会议也全部都查询出来啦~
OK，以上就是Leaf今天带来的关于会议OA项目的几个功能代码分享，喜欢的可以关注一起学习噢，有不理解的地方也可以私信来问我一起学习噢，我们下次见！！！
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
