目录
sq语句:
1.待开会议:
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))
运行结果:

2.所有会议:
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 state
=4 and FIND_IN_SET(6, CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))
jsp界面:
- <%@ page language="java" contentType="text/html; charset=UTF-8"
- pageEncoding="UTF-8"%>
- <%@include file="/common/header.jsp"%>
- 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>
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) {
- //查询完成的回调函数
- }
- });
- }
- 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
{ -
- //会议信息的新增
- 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"});
- }
-
-
-
- private String getSQL() {
- return "SELECT\r\n" +
- " a.id,\r\n" +
- " a.title,\r\n" +
- " a.content,\r\n" +
- " a.liexize,\r\n" +
- " 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\r\n" +
- " a.state \r\n" +
- " WHEN 0 THEN\r\n" +
- " '取消会议' \r\n" +
- " WHEN 1 THEN\r\n" +
- " '新建' \r\n" +
- " WHEN 2 THEN\r\n" +
- " '待审核' \r\n" +
- " WHEN 3 THEN\r\n" +
- " '驳回' \r\n" +
- " WHEN 4 THEN\r\n" +
- " '待开' \r\n" +
- " WHEN 5 THEN\r\n" +
- " '进行中' \r\n" +
- " WHEN 6 THEN\r\n" +
- " '开启投票' \r\n" +
- " WHEN 7 THEN\r\n" +
- " '结束会议' ELSE '其他' \r\n" +
- "END \r\n" +
- " ) meetingstate,\r\n" +
- " a.seatPic,\r\n" +
- " a.remark,\r\n" +
- " a.auditor,\r\n" +
- " c.`name` auditorname \r\n" +
- "FROM\r\n" +
- " 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 and 1=1 ";
- }
-
- //我的会议SQL,后续其他的菜单也会使用
- public List
- throws SQLException, InstantiationException, IllegalAccessException {
- String sql=getSQL();
- //拿到会议标题
- String title = info.getTitle();
- if(StringUtils.isNotBlank(title)) {
- sql +=" and title like '%"+title+"%' ";
- }
- sql +=" and zhuchiren = "+info.getZhuchiren();
- return super.executeQuery(sql, pageBean);
- }
-
-
- //设置会议排座图片
- 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
- String sql=getSQL();
- //拿到会议标题
- String title = info.getTitle();
- if(StringUtils.isNotBlank(title)) {
- sql +=" and a.title like '%"+title+"%' ";
- }
- sql +=" and auditor = "+info.getAuditor();
- sql+=" and a.state = 2";
- //降序
- sql+=" order by a.id desc";
- return super.executeQuery(sql, pageBean);
- }
-
-
- //待开会议
- public List
- String sql = "select CONCAT(canyuze,',',liexize,',',zhuchiren)\r\n" +
- " , a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren, 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, \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\r\n" +
- " =4 and FIND_IN_SET("+info.getZhuchiren()+", CONCAT(canyuze,',',liexize,',',zhuchiren))";
- return super.executeQuery(sql, pageBean);
- }
-
- //所有会议
-
- public List
- String sql = "select\r\n" +
- " a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren, 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, \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)))\r\n" +
- " \r\n" +
- " \r\n" +
- "";
- return super.executeQuery(sql, pageBean);
- }
-
-
-
-
- }
- package com.zking.web;
-
- 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.MyDateConverter;
- import com.zking.util.PageBean;
- import com.zking.util.PropertiesUtil;
- import com.zking.util.R;
- import com.zking.util.ResponseUtil;
- import com.zking.util.StringUtils;
-
- public class MeetingInfoAction extends ActionSupport implements ModelDriver
{ - 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) {
- /**
- * 1.接收界面传递到后台的图片对应的字符串
- * 2.借助工具类将字符串生成一个图片,保存到配置文件所配置的路径下
- * 3.添加服务器硬盘与请求地址的映射,即可访问
- * 4.将请求地址保存到数据库中
- */
-
- // E:/temp/images/T280/
-
-
- try {
- //获取图片的存放地址
- String dirPath = PropertiesUtil.getValue("dirPath");
- //获取浏览器的请求路径,为了后续保存到数据库serverPath=/upload/paizuo/
- String serverPath = PropertiesUtil.getValue("serverPath");
- //随机生产一个图片名
- 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);
-
- //修改会议排座数据库图片对应的数据库列段
- //rs影响行数n
- 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.ok(0,"会议排座失败"));
- } catch (Exception e1) {
- // TODO Auto-generated catch block
- 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.ok(0,"会议信息数据新增失败"));
- } catch (Exception e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
-
- }
- return null;
- }
-
-
-
-
-
- //我的会议
-
- public String myInfos (HttpServletRequest req, HttpServletResponse resp) {
-
- try {
- PageBean pageBean = new PageBean();
- pageBean.setRequest(req);
- List
- //注意:layui中的数据表格的格式
- ResponseUtil.writeJson(resp, R.ok(0, "我的会议 数据查询成功",pageBean.getTotal(), list ));
-
- } catch (Exception e) {
- e.printStackTrace();
- try {
- ResponseUtil.writeJson(resp, R.ok(0,"我的会议数据查询失败"));
- } catch (Exception e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
-
- }
- return null;
- }
-
-
-
-
-
- // 根据会议ID更新会议的审批人(送审)
- public String updateAuditorById(HttpServletRequest req, HttpServletResponse resp) {
- try {
- 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 myAudit(HttpServletRequest req, HttpServletResponse resp) {
- try {
- PageBean pageBean = new PageBean();
- pageBean.setRequest(req);
- List
- ResponseUtil.writeJson(resp, R.ok(0, "我的审批查询成功!!!", pageBean.getTotal(), infos));
- } catch (Exception e) {
- e.printStackTrace();
- try {
- ResponseUtil.writeJson(resp, R.error(0, "我的审批查询失败"));
- } catch (Exception e1) {
- e1.printStackTrace();
- }
- }
- return null;
- }
-
-
-
- //待开会议queryMeetingInfoByState
-
- public String queryMeetingInfoByState (HttpServletRequest req, HttpServletResponse resp) {
-
- try {
- PageBean pageBean = new PageBean();
- pageBean.setRequest(req);
- List
- //注意:layui中的数据表格的格式
- ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询成功",pageBean.getTotal(), list ));
-
- } catch (Exception e) {
- e.printStackTrace();
- try {
- ResponseUtil.writeJson(resp, R.ok(0,"待开会议数据查询失败"));
- } catch (Exception e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
-
- }
- return null;
- }
-
-
- //allInfos所有会议
-
- public String allInfos (HttpServletRequest req, HttpServletResponse resp) {
-
- try {
- PageBean pageBean = new PageBean();
- pageBean.setRequest(req);
- List
- //注意:layui中的数据表格的格式
- ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询成功",pageBean.getTotal(), list ));
-
- } catch (Exception e) {
- e.printStackTrace();
- try {
- ResponseUtil.writeJson(resp, R.ok(0,"所有会议数据查询失败"));
- } catch (Exception e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
-
- }
- return null;
- }
-
-
-
-
-
- }
运行效果:
