会议OA项目（待开会议&所有会议）

目录

一、SQL语句

二、待开会议

 三、所有会议

一、SQL语句

待开会议

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

-- 待开会议
-- 与我的会议的区别在于，代开会议需要匹配3个数据库列段
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-%s')startTime,
DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%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 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-%s')startTime,
DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%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(canyuze,',',liexize,',',zhuchiren,',',IFNULL(a.auditor,-1)))

-- 只是新建状态，还没有对应审核人也应该查询出来，a.auditor为空，则该条数据查不出来，错误示范

SELECt CONCAt(a.canyuze,',',a.liexize,',',a.zhuchiren,',',a.auditor) allPerson
,a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,b.`name`,a.location
,DATE_FORMAT(a.startTime,'%Y-%m-%d %H:%i:%s') as startTime
,DATE_FORMAT(a.endTime,'%Y-%m-%d %H:%i:%s') as 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 '开启投票'
else '结束会' end
) as meetingState
,a.seatPic,a.remark,a.auditor,c.`name` as 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('2',CONCAt(a.canyuze,',',a.liexize,',',a.zhuchiren,',',a.auditor))  and title like '%%' order by a.id desc

二、待开会议

MeetingInfoDao

//待开会议
	public List> queryMeetingInfoByState(MeetingInfo meetingInfo, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {
		String sql = "SELECt a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchirenrn" + 
				" ,b.name zhuchirenname, rn" + 
				" a.location,rn" + 
				" DATE_FORMAT(a.startTime,'%Y-%m-%d %H-%s')startTime,rn" + 
				" DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%s')endTime,rn" + 
				" a.state,rn" + 
				" (rn" + 
				"	case a.statern" + 
				"	when 0 then '取消会议'rn" + 
				"	when 1 then '新建'rn" + 
				"	when 2 then '待审核'rn" + 
				"	when 3 then '驳回'rn" + 
				"	when 4 then '待开'rn" + 
				"	when 5 then '进行中'rn" + 
				"	when 6 then '开启投票'rn" + 
				"	when 7 then '结束会议'rn" + 
				"	else '其他' endrn" + 
				"	rn" + 
				" ) meetingstate,rn" + 
				" a.seatPic,a.remark,a.auditor,rn" + 
				" c.name auditornamern" + 
				" FROM t_oa_meeting_info arn" + 
				" inner join t_oa_user b on a.zhuchiren = b.idrn" + 
				" left join t_oa_user c on a.auditor = c.id where 1=1 rn" + 
				" and state = 4 and FIND_IN_SET("+meetingInfo.getZhuchiren()+",CONCAt(canyuze,',',liexize,',',zhuchiren))";
		return super.executeQuery(sql, pageBean);
	}

 MeetingInfoAction.java

//	待开会议
	public String queryMeetingInfoByState(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			pageBean.setRequest(req);
			List> lst = meetingInfoDao.queryMeetingInfoByState(meetingInfo, pageBean);
//		注意：layui中的数据表的格式
			ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询失败"));
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return null;
	}

meetingWaiting.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@include file="/common/header.jsp"%>








	
		
			会议标题：
			
				
				
			
		
		
			
				 查询
			
		
	
	

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

效果：

 三、所有会议

MeetingInfoDao.java

public List> allInfos(MeetingInfo meetingInfo, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {
		String sql = "SELECt CONCAt(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)), a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchirenrn" + 
				",b.name zhuchirenname, rn" + 
				"a.location,rn" + 
				"DATE_FORMAT(a.startTime,'%Y-%m-%d %H-%s')startTime,rn" + 
				"DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%s')endTime,rn" + 
				"a.state,rn" + 
				"(rn" + 
				"	case a.statern" + 
				"	when 0 then '取消会议'rn" + 
				"	when 1 then '新建'rn" + 
				"	when 2 then '待审核'rn" + 
				"	when 3 then '驳回'rn" + 
				"	when 4 then '待开'rn" + 
				"	when 5 then '进行中'rn" + 
				"	when 6 then '开启投票'rn" + 
				"	when 7 then '结束会议'rn" + 
				"	else '其他' endrn" + 
				"	rn" + 
				") meetingstate,rn" + 
				"a.seatPic,a.remark,a.auditor,rn" + 
				"c.name auditornamern" + 
				"FROM t_oa_meeting_info arn" + 
				"inner join t_oa_user b on a.zhuchiren = b.idrn" + 
				"left join t_oa_user c on a.auditor = c.id where 1=1 rn" + 
				"and FIND_IN_SET("+meetingInfo.getZhuchiren()+",CONCAt(canyuze,',',liexize,',',zhuchiren,',',IFNULL(a.auditor,-1)))";
		return super.executeQuery(sql, pageBean);
	}

MeetingInfoAction.java

//	所有会议
	public String allInfos(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			pageBean.setRequest(req);
			List> lst = meetingInfoDao.allInfos(meetingInfo, pageBean);
//		注意：layui中的数据表的格式
			ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询失败"));
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return null;
	}

meetingAll.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@include file="/common/header.jsp"%>








	
		
			会议标题：
			
				
				
			
		
		
			
				 查询
			
		
	
	

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

效果