文章目录

一、会议OA项目名词介绍

二、SQL编写

待开会议SQL

所有会议SQL

 三、代码阶段

待开会议

所有会议

我的会议：当前登录账号，是 某会议 主持人，则查询出来
我的审批：当前登录账号，是 某会议 的指定审批人，并且会议状态是待审核，则查询出来
会议通知：当前登录账号，只要是 某会议 的参与者、列席者、主持人中的一员，并且该会议未反馈，则查询出来

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

 历史会议：当前登录账号，只要是 某会议 的参与者、列席者、主持人中的一员，并且会议状态是结束，则查询出来

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

 二、SQL编写

待开会议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

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

 

 所有会议SQL

这条SQL语句提供错误演示，不要运用在项目中

所有会议与待开会议的区别：所有会议不需要限制状态，并且需要新增一个审批人列段

select CONCAt(a.canyuze,',',a.liexize,',',a.zhuchiren,',',a.auditor),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

 假设：id=6的用户，是会议OA的列席人员，会议OA又处于新建状态，意味着没有审批人，请问这条数据要不要查询出来？

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

如果当前会议还没有送审也就是没有审批人的情况下，我们需要用ifnull函数设它编号为-1，否则会发生数据丢失 

 三、代码阶段

待开会议

meetingWaiting.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@include file="/common/head.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) {
            //查询完成的回调函数
        }
    });
}

dao层

//待开会议
	public List> queryMeetingInfoByState(MeetingInfo info, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {
		String sql = "select CONCAt(canyuze,',',liexize,',',zhuchiren),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-%m-%s') startTime,rn" + 
				" DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%m-%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" + 
				" ) meetingstate,rn" + 
				" a.seatPic,a.remark,a.auditor,rn" + 
				" c.name auditorname 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=1rn" + 
				" and state = 4 and FIND_IN_SET("+info.getZhuchiren()+",CONCAt(canyuze,',',liexize,',',zhuchiren))rn" + 
				"";
		return super.executeQuery(sql, pageBean);
	}

web层

//待开会议
	public String queryMeetingInfoByState(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			//初始化pageBean
			pageBean.setRequest(req);
			//调用方法
			List> lst =infoDao.queryMeetingInfoByState(info, pageBean) ;
//			layui 的code 返回一定要是 0，不能是200，否者返回不了数据
			//传输到界面
			//pageBean.getTotal()总记录数
			ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.error(0, "待开会议数据查询失败"));
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		return null;
	}

页面运行效果

 所有会议

meetingAll.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@include file="/common/head.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) {
            //查询完成的回调函数
        }
    });
}

 dao层

//所有会议
	public List> allInfos(MeetingInfo info, 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-%m-%s') startTime,rn" + 
				"DATE_FORMAT(a.endTime,'%Y-%m-%d %H-%m-%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" + 
				") meetingstate,rn" + 
				"a.seatPic,a.remark,a.auditor,rn" + 
				"c.name auditorname 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=1rn" + 
				"and FIND_IN_SET("+info.getZhuchiren()+",CONCAt(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)))";
		return super.executeQuery(sql, pageBean);
	}

web层 

//所有会议
	public String allInfos(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			//初始化pageBean
			pageBean.setRequest(req);
			//调用方法
			List> lst =infoDao.allInfos(info, pageBean) ;
//			layui 的code 返回一定要是 0，不能是200，否者返回不了数据
			//传输到界面
			//pageBean.getTotal()总记录数
			ResponseUtil.writeJson(resp, R.ok(0, "所有会议数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.error(0, "所有会议数据查询失败"));
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		return null;
	}

页面运行效果 

拜了个拜~~ 

历史会议思路与以上会议思路相差无几，待我实现后即补全