会议OA项目（六）--- （待开会议、历史会议、所有会议）

前言

        上一篇分享了会议通知以及会议反馈，根据需求来今天应该到了，历史会议、待开会议以及所有会议了。

---

一、需求分析

历史会议：登录人员，属于参与者列席者或者主人其中一个时，并且会议状态为已结束时，要将数据查询出来。

待开会议：登录人员，属于参与者列席者或者主人其中一个时，并且会议状态为待开时，要将数据查询出来。

所有会议：登录人员，属于参与者列席者或者主人其中一个时，要将数据查询出来。

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

​

历史会议

​
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 = 7 and FIND_IN_SET(6,CONCAT(canyuze,',',liexize,',',zhuchiren))

​

 二、编码

        后端：

        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 info) throws Exception {
		String sql = "insert into t_oa_meeting_info(title,content,canyuze,liexize,zhuchiren,\r\n"
				+ "location,startTime,endTime,remark) values(?,?,?,?,?,?,?,?,?)";
		return super.executeUpdate(sql, info, 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\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\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 SQLException, InstantiationException, IllegalAccessException {
		String sql = getSQL();
		//会议标题
		String title = info.getTitle();
		if(StringUtils.isNotBlank(title)) {
			sql+=" and title like '%"+title+"%'";
		}
		sql+=" and zhuchiren="+info.getZhuchiren();
//		排序按照降序展示
		sql+=" order by a.id desc ";
		return super.executeQuery(sql, pageBean);
	}

//	设置会议排座图片
	public int updateSeatPicById(MeetingInfo info) throws Exception {
		String sql =" update t