目录

一、待开会议

SQL语句

dao方法

 web层

js代码 

 jsp文件

运行效果

二、所有会议

SQL语句

dao方法

web层

js代码

jsp

运行效果

---

一、待开会议

SQL语句

待开会议
与我的会议的区别在于，待开会议需要匹配3个数据库列段

SELECT CONCAT(a.canyuze,',',a.liexize,',',a.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 a.state=4 and FIND_IN_SET
(6,CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren))

dao方法

 
//	通用的会议查询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> queryMeetingInfoByState(MeetingInfo info,PageBean pageBean) throws Exception{
		String sql=this.getSQL();
		String title = info.getTitle();
		//按照会议标题模糊查询
		if(StringUtils.isNotBlank(title))
			sql+=" and title like '%"+title+"%'";
		//根据当前登陆用户ID作为主持人字段的条件
		String zhuchiren = info.getZhuchiren();
		if(StringUtils.isNotBlank(zhuchiren)) {
			sql+="and FIND_IN_SET('"+zhuchiren+"',CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren))";
		}
		//根据会议状态，查询会议信息
		Integer state = info.getState();
		if(state != null) {
			sql+=" and state = " + state;
		}
		//按照会议ID降序排序
		sql+=" order by a.id desc";
		return super.executeQuery(sql, pageBean);
	}

 web层

//	待开会议
	public String queryMeetingInfoByState(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			pageBean.setRequest(req);
			List> infos = meetingInfoDao.queryMeetingInfoByState(info, pageBean);
			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;
	}

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: 'name', 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) {
            //查询完成的回调函数
        }
    });
}

 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>

 

运行效果

 

二、所有会议

SQL语句

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

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)))

dao方法

//	通用的会议查询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> allInfos(MeetingInfo info,PageBean pageBean) throws Exception{
		String sql=this.getSQL();
		String title = info.getTitle();
		//按照会议标题模糊查询
		if(StringUtils.isNotBlank(title))
			sql+=" and title like '%"+title+"%'";
		//根据当前登陆用户ID作为主持人字段的条件
		String zhuchiren = info.getZhuchiren();
		if(StringUtils.isNotBlank(zhuchiren)) {
			sql+="and FIND_IN_SET('"+zhuchiren+"',CONCAT(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))";
		}
		//按照会议ID降序排序
		sql+=" order by a.id desc";
		System.out.println(sql);
		return super.executeQuery(sql, pageBean);
	}
	

web层

//	所有会议
	public String allInfos(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			pageBean.setRequest(req);
			List> infos = meetingInfoDao.allInfos(info, pageBean);
			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;
	}
	

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: 'name', 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) {
            //查询完成的回调函数
        }
    });
}

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/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>

运行效果