OA项目之待开会议&历史会议&所有会议

一、待开会议&所有会议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 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)) )

运行：

二、待开会议及所有会议功能开发

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{

	//会议信息新增
	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"});
	}
	
//	通用的会议查询SQL语句，包含会议信息表数据，主持人姓名、审批人姓名、会议状态
	private String getSQL() {
		return "SELECT a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,b.`name`,a.locationrn" + 
				",DATE_FORMAT(a.startTime,'%Y-%m-%d %H:%i:%s') as startTimern" + 
				",DATE_FORMAT(a.endTime,'%Y-%m-%d %H:%i:%s') as endTimern" + 
				",a.statern" + 
				",(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" + 
				"else '结束会' endrn" + 
				") as meetingStatern" + 
				",a.seatPic,a.remark,a.auditor,c.`name` as 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 ";
	}
	
//	我的会议
	public List> myInfos(MeetingInfo info, PageBean pageBean) throws Exception {
		String sql = getSQL();
		String title = info.getTitle();
		if(StringUtils.isNotBlank(title)) {
			sql += " and title like '%"+title+"%'";
		}
		//根据当前登陆用户ID作为主持人字段的条件
		sql+=" and zhuchiren="+info.getZhuchiren();
		//按照会议ID降序排序
		
		//System.out.println(sql);
		return super.executeQuery(sql, pageBean);
	}
	
	
//	状态：0取消会议 1新建 2待审核 3驳回 4待开 5进行中 6开启投票 7结束会议，默认值为1
	public int updatezt(MeetingInfo m) throws Exception {
		String sql = "update t_oa_meeting_info set state=? where id = ?";
		return super.executeUpdate(sql, m, new String[] {"state","id"});
	}

	//设置会议排座图片
	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> myAudit(MeetingInfo info, PageBean pageBean) throws Exception{
		String sql = this.getSQL();
		String title = info.getTitle();
		if(StringUtils.isNotBlank(title)) {
			sql += " and title like '%"+title+"%' ";
		}
		//当前登录账号是会议信息表中 审批人字段值
		sql += " and a.auditor = " + info.getAuditor();
		//只查询会议状态为2  即待审核状态的会议
		sql += " and a.state = 2 ";
		//排序按照降序展示
		//sql += " order by a.id desc ";
		return super.executeQuery(sql, pageBean);
	}

	//待开会议
	public List> queryMeetingInfoByState(MeetingInfo info, PageBean pageBean) throws Exception{
		String sql = " select CONCAt(canyuze,',',liexize,',',zhuchiren) rn" + 
				" , 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.state rn" + 
				"	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=1 and state=4 and FIND_IN_SET("+info.getZhuchiren()+", CONCAt(canyuze,',',liexize,',',zhuchiren) )";
		return super.executeQuery(sql, pageBean);
	}

	public List> allInfos(MeetingInfo info, PageBean pageBean) throws Exception{
		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.state rn" + 
				"	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=1 and FIND_IN_SET("+info.getZhuchiren()+", CONCAt(a.canyuze,',',a.liexize,',',a.zhuchiren,',',IFNULL(a.auditor,-1)) )";
		return super.executeQuery(sql, pageBean);
	}
	
	
	
	
}

MeetingInfoAction

package com.zking.web;

import java.io.IOException;
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.BaseDao;
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;

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) throws Exception {
		
		try {
			// E:/T280/images/123.png
			//获取到图片的存放地址
			String dirPath = PropertiesUtil.getValue("dirPath");
			//获取到浏览器请求路径，为了后续保存到数据库
			String serverPath = PropertiesUtil.getValue("serverPath"); // serverPath=/test_layui/upload/paizuo/
			//随机生成一个图片名称
			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);
			
			//修改会议排座 数据库图片对应的数据库列段
			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.error(0, "会议排座失败"));
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		}
		return null;
	}
	
	//会议送审
	public String updateAuditorById(HttpServletRequest req, HttpServletResponse resp) {
		try {
			//rs是影响行数
			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 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.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> lst = infoDao.myAudit(info, pageBean);
//			注意：layui中的数据表格的格式
			ResponseUtil.writeJson(resp, R.ok(0, "我的会议数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.error(0, "我的会议数据查询失败"));
			} catch (Exception e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
		}
		return null;
	}
	
	//待开会议
	public String queryMeetingInfoByState(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			pageBean.setRequest(req);
			List> lst = infoDao.queryMeetingInfoByState(info, pageBean);
//			注意：layui中的数据表格的格式
			ResponseUtil.writeJson(resp, R.ok(0, "待开会议数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.error(0, "待开会议数据查询失败"));
			} catch (Exception e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
		}
		return null;
	}
	
	
	//所有会议 
	public String allInfos(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			pageBean.setRequest(req);
			List> lst = infoDao.allInfos(info, pageBean);
//			注意：layui中的数据表格的格式
			ResponseUtil.writeJson(resp, R.ok(0, "所有会议 数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.error(0, "所有会议数据查询失败"));
			} catch (Exception e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
		}
		return null;
	}
	
	
	
	//我的会议
	public String myInfos(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean = new PageBean();
			pageBean.setRequest(req);
			List> lst = infoDao.myInfos(info, pageBean);
//			注意：layui中的数据表格的格式
			ResponseUtil.writeJson(resp, R.ok(0, "我的会议数据查询成功",pageBean.getTotal(),lst));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.error(0, "我的会议数据查询失败"));
			} catch (Exception e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
		}
		return null;
	}
	
	
	//我的会议：删除（取消会议）
		public String updatezt(HttpServletRequest req, HttpServletResponse resp) {
			try {
				int rs = infoDao.updatezt(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;
		}

	
	
	

}

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

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

OA项目之待开会议&历史会议&所有会议

Java相关栏目本月热门文章