目录
一、待开会议&所有会议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.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(6, CONCAT(canyuze,',',liexize,',',zhuchiren) )
运行：
-- 所有会议
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(6, CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)) )
运行：
二、待开会议及所有会议功能开发
MeetingInfoDao
package com.zking.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.zking.entity.MeetingInfo;
import com.zking.util.BaseDao;
import com.zking.util.PageBean;
import com.zking.util.StringUtils;
public class MeetingInfoDao extends BaseDao<MeetingInfo>{
//会议信息新增
public int add (MeetingInfo t) throws Exception {
String sql = "insert into t_oa_meeting_info(title,content,canyuze,liexize,zhuchiren,location,startTime,endTime,remark) values(?,?,?,?,?,?,?,?,?)";
return super.executeUpdate(sql, t, new String[] {"title","content","canyuze","liexize","zhuchiren","location","startTime","endTime","remark"});
}
// 通用的会议查询SQL语句，包含会议信息表数据，主持人姓名、审批人姓名、会议状态
private String getSQL() {
return "SELECT a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,b.`name`,a.location\r\n" +
",DATE_FORMAT(a.startTime,'%Y-%m-%d %H:%i:%s') as startTime\r\n" +
",DATE_FORMAT(a.endTime,'%Y-%m-%d %H:%i:%s') as endTime\r\n" +
",a.state\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" +
"else '结束会' end\r\n" +
") as meetingState\r\n" +
",a.seatPic,a.remark,a.auditor,c.`name` as auditorName\r\n" +
"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 ";
}
// 我的会议
public List<Map<String, Object>> myInfos(MeetingInfo info, PageBean pageBean) throws Exception {
String sql = getSQL();
String title = info.getTitle();
if(StringUtils.isNotBlank(title)) {
sql += " and title like '%"+title+"%'";
}
//根据当前登陆用户ID作为主持人字段的条件
sql+=" and zhuchiren="+info.getZhuchiren();
//按照会议ID降序排序
/*sql+=" order by a.id desc";*/
//System.out.println(sql);
return super.executeQuery(sql, pageBean);
}
// 状态：0取消会议 1新建 2待审核 3驳回 4待开 5进行中 6开启投票 7结束会议，默认值为1
public int updatezt(MeetingInfo m) throws Exception {
String sql = "update t_oa_meeting_info set state=? where id = ?";
return super.executeUpdate(sql, m, new String[] {"state","id"});
}
//设置会议排座图片
public int updateSeatPicById(MeetingInfo info) throws Exception {
String sql = " update t_oa_meeting_info set seatPic = ? where id=?";
return super.executeUpdate(sql, info, new String[] {"seatPic","id"});
}
//会议送审
public int updateAuditorById(MeetingInfo info) throws Exception {
String sql = " update t_oa_meeting_info set auditor = ? , state =2 where id=?";
return super.executeUpdate(sql, info, new String[] {"auditor","id"});
}
//我的审批中的会议查询
public List<Map<String, Object>> myAudit(MeetingInfo info, PageBean pageBean) throws Exception{
String sql = this.getSQL();
String title = info.getTitle();
if(StringUtils.isNotBlank(title)) {
sql += " and title like '%"+title+"%' ";
}
//当前登录账号是会议信息表中 审批人字段值
sql += " and a.auditor = " + info.getAuditor();
//只查询会议状态为2 即待审核状态的会议
sql += " and a.state = 2 ";
//排序按照降序展示
//sql += " order by a.id desc ";
return super.executeQuery(sql, pageBean);
}
//待开会议
public List<Map<String, Object>> queryMeetingInfoByState(MeetingInfo info, PageBean pageBean) throws Exception{
String sql = " select CONCAT(canyuze,',',liexize,',',zhuchiren) \r\n" +
" , 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 and state=4 and FIND_IN_SET("+info.getZhuchiren()+", CONCAT(canyuze,',',liexize,',',zhuchiren) )";
return super.executeQuery(sql, pageBean);
}
public List<Map<String, Object>> allInfos(MeetingInfo info, 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 and FIND_IN_SET("+info.getZhuchiren()+", CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)) )";
return super.executeQuery(sql, pageBean);
}
}
MeetingInfoAction
package com.zking.web;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.ConvertUtils;
import com.zking.dao.MeetingInfoDao;
import com.zking.entity.MeetingInfo;
import com.zking.framework.ActionSupport;
import com.zking.framework.ModelDriver;
import com.zking.util.Base64ImageUtils;
import com.zking.util.BaseDao;
import com.zking.util.MyDateConverter;
import com.zking.util.PageBean;
import com.zking.util.PropertiesUtil;
import com.zking.util.R;
import com.zking.util.ResponseUtil;
public class MeetingInfoAction extends ActionSupport implements ModelDriver<MeetingInfo>{
private MeetingInfo info = new MeetingInfo();
private MeetingInfoDao infoDao = new MeetingInfoDao();
@Override
public MeetingInfo getModel() {
ConvertUtils.register(new MyDateConverter(), Date.class);
return info;
}
//会议排座图片生成
public String updateSeatPicById(HttpServletRequest req, HttpServletResponse resp) throws Exception {
/**
* 1.接受前端页面传递到后台的图片对应的字符串
* 2.借助工具类将字符串生成一个图片，保存到配置文件所配置的路径下
* 3.添加服务器硬盘与 请求地址的映射，即可访问
* 4.将请求地址 保存到数据库中
*/
try {
// E:/T280/images/123.png
//获取到图片的存放地址
String dirPath = PropertiesUtil.getValue("dirPath");
//获取到浏览器请求路径，为了后续保存到数据库
String serverPath = PropertiesUtil.getValue("serverPath"); // serverPath=/test_layui/upload/paizuo/
//随机生成一个图片名称
String fileName = UUID.randomUUID().toString().replaceAll("-", "") + ".png";
//info.getSeatPic();//图片字符串
Base64ImageUtils.GenerateImage(info.getSeatPic().replaceAll("data:image/png;base64,", ""), dirPath+fileName);
//将seatPic里面的内容修改为请求地址
info.setSeatPic(serverPath + fileName);
//修改会议排座 数据库图片对应的数据库列段
int rs = infoDao.updateSeatPicById(info);
if(rs > 0) {
ResponseUtil.writeJson(resp, R.ok(200, "会议排座成功"));
}
else {
ResponseUtil.writeJson(resp, R.error(0, "会议排座失败"));
}
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "会议排座失败"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}
//会议送审
public String updateAuditorById(HttpServletRequest req, HttpServletResponse resp) {
try {
//rs是影响行数
int rs = infoDao.updateAuditorById(info);
if(rs > 0) {
ResponseUtil.writeJson(resp, R.ok(200, "会议送审成功"));
}
else {
ResponseUtil.writeJson(resp, R.error(0, "会议送审失败"));
}
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "会议送审失败"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}
//用户新增
public String add(HttpServletRequest req, HttpServletResponse resp) {
try {
//rs是影响行数
int rs = infoDao.add(info);
if(rs > 0) {
ResponseUtil.writeJson(resp, R.ok(200, "会议信息新增成功"));
}
else {
ResponseUtil.writeJson(resp, R.error(0, "会议信息新增失败"));
}
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "会议信息新增失败"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}
//我的审批中的会议查询
public String myAudit(HttpServletRequest req, HttpServletResponse resp) {
try {
PageBean pageBean = new PageBean();
pageBean.setRequest(req);
List<Map<String, Object>> lst = infoDao.myAudit(info, pageBean);
// 注意：layui中的数据表格的格式
ResponseUtil.writeJson(resp, R.ok(0, "我的会议数据查询成功",pageBean.getTotal(),lst));
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "我的会议数据查询失败"));
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
return null;
}
//待开会议
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) {
// TODO: handle exception
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "待开会议数据查询失败"));
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
return null;
}
//所有会议
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) {
// TODO: handle exception
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "所有会议数据查询失败"));
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
return null;
}
//我的会议
public String myInfos(HttpServletRequest req, HttpServletResponse resp) {
try {
PageBean pageBean = new PageBean();
pageBean.setRequest(req);
List<Map<String, Object>> lst = infoDao.myInfos(info, pageBean);
// 注意：layui中的数据表格的格式
ResponseUtil.writeJson(resp, R.ok(0, "我的会议数据查询成功",pageBean.getTotal(),lst));
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "我的会议数据查询失败"));
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
return null;
}
//我的会议：删除（取消会议）
public String updatezt(HttpServletRequest req, HttpServletResponse resp) {
try {
int rs = infoDao.updatezt(info);
if (rs > 0) {
ResponseUtil.writeJson(resp, R.ok(200, "会议取消成功"));
} else {
ResponseUtil.writeJson(resp, R.error(0, "会议取消失败"));
}
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "会议取消失败"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}
}
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="${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或者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) {
//查询完成的回调函数
}
});
}
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="${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或者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) {
//查询完成的回调函数
}
});
}
306
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
