会议OA之查询是否参会以及反馈情况

目录

一：SQL查询

分析（涉及到的表）：

二：是否参会

分析（涉及到的表）：

1，再拿到相对应的人 

,2，拿到对应的反馈情况（未读、参加、不参加）

3、根据 会议信息反馈情况进行分组 

三：反馈情况

实体类：MeetingFeedBack  对应会议反馈表

实现反馈:

效果图： 

一：SQL查询

分析（涉及到的表）：

        会议信息表：t_oa_meeting_info

        会议反馈表：t_oa_meeting_feedback

SELECt * from t_oa_meeting_info where FIND_IN_SET(2,CONCAt(canyuze,',',liexize,',',zhuchiren))
 and state=4

SELECt
    IFNULL(f.result,-1) result,t1.*
FROM
(SELECt * from t_oa_meeting_info where FIND_IN_SET(2,CONCAt(canyuze,',',liexize,',',zhuchiren)) and state=4) t1
LEFT JOIN t_oa_meeting_feedback f on t1.id=f.meetingId
and f.personId=2
 ORDER BY result;

二：是否参会 分析（涉及到的表）：

        用户表：t_oa_user

        会议信息表：t_oa_meeting_info

        会议反馈表：t_oa_meeting_feedback

SELECt CONCAt(canyuze,',',liexize,',',zhuchiren) from
	t_oa_meeting_info where id=12

1，再拿到相对应的人 

SELECt * from t_oa_user 
where 
FIND_IN_SET(id,	(SELECt  CONCAt(canyuze,',',liexize,',',zhuchiren)
from
t_oa_meeting_info where id=12))

,2，拿到对应的反馈情况（未读、参加、不参加）

SELECt * from t_oa_user where FIND_IN_SET(id,	(SELECt CONCAt(canyuze,',',liexize,',',zhuchiren) from
	t_oa_meeting_info where id=12))

3、根据 会议信息反馈情况进行分组 

select 
t.result,GROUP_CONCAt(t.name) names
from
(select 
t1.name,IFNULL(f.result,-1) result
from
(SELECt * from t_oa_user where FIND_IN_SET(id,	(SELECt CONCAt(canyuze,',',liexize,',',zhuchiren) from
	t_oa_meeting_info 
	where id=12))) t1
left join t_oa_meeting_feedback f on t1.id=f.personId and f.meetingId=12) t
GROUP BY t.result

三：反馈情况

实体类：MeetingFeedBack  对应会议反馈表

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>








 

package com.zking.entity;
 
import java.io.Serializable;

public class MeetingFeedBack implements Serializable {
 
	private String id;
	private Long meetingId;
	private Integer personType;
	private Long personId;
	private Integer result;
	private String reason;
 
	// 会议标题
	private String title;
 
	public String getTitle() {
		return title;
	}
 
	public void setTitle(String title) {
		this.title = title;
	}
 
	public String getId() {
		return id;
	}
 
	public void setId(String id) {
		this.id = id;
	}
 
	public Long getMeetingId() {
		return meetingId;
	}
 
	public void setMeetingId(Long meetingId) {
		this.meetingId = meetingId;
	}
 
	public Integer getPersonType() {
		return personType;
	}
 
	public void setPersonType(Integer personType) {
		this.personType = personType;
	}
 
	public Long getPersonId() {
		return personId;
	}
 
	public void setPersonId(Long personId) {
		this.personId = personId;
	}
 
	public Integer getResult() {
		return result;
	}
 
	public void setResult(Integer result) {
		this.result = result;
	}
 
	public String getReason() {
		return reason;
	}
 
	public void setReason(String reason) {
		this.reason = reason;
	}
 
	public MeetingFeedBack() {
		super();
		// TODO Auto-generated constructor stub
	}
 
	@Override
	public String toString() {
		return "MeetingFeedBack [id=" + id + ", meetingId=" + meetingId + ", personType=" + personType + ", personId="
				+ personId + ", result=" + result + ", reason=" + reason + "]";
	}
 
}

 

package com.zking.dao;
 
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
 
import com.zking.entity.MeetingFeedBack;
import com.zking.util.BaseDao;
import com.zking.util.PageBean;
 
public class MeetingFeedBackDao extends BaseDao{
 
	//会议通知查询
	public List> queryMeetingFeedBackByUserId(MeetingFeedBack back, PageBean pageBean)
			throws SQLException, InstantiationException, IllegalAccessException {
		String sql="SELECtrn" + 
				"	IFNULL(f.result,-1) result,t1.*rn" + 
				" FROMrn" + 
				" (SELECt * from t_oa_meeting_info where FIND_IN_SET("+back.getPersonId()+",CONCAt(canyuze,',',liexize,',',zhuchiren)) and state=4) t1rn" + 
				" LEFT JOIN t_oa_meeting_feedback f on t1.id=f.meetingIdrn" + 
				" and f.personId="+back.getPersonId()+"rn" + 
				" ORDER BY result";
		
	return super.executeQuery(sql, pageBean);
	}
//	back.getPersonId()
}

package com.zking.web;
 
import java.util.List;
import java.util.Map;
 
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import com.zking.dao.MeetingFeedBackDao;
import com.zking.entity.MeetingFeedBack;
import com.zking.framework.ActionSupport;
import com.zking.framework.ModelDriver;
import com.zking.util.PageBean;
import com.zking.util.R;
import com.zking.util.ResponseUtil;
 
public class MeetingFeedBackAction extends ActionSupport implements ModelDriver{
	private MeetingFeedBack back=new MeetingFeedBack();
	private MeetingFeedBackDao backDao=new MeetingFeedBackDao();
	@Override
	public MeetingFeedBack getModel() {
		return back;
	}
 
//	会议通知查询
	public String queryMeetingFeedBackByUserId(HttpServletRequest req, HttpServletResponse resp) {
		try {
			PageBean pageBean=new PageBean();
			pageBean.setRequest(req);
			List> infos = backDao.queryMeetingFeedBackByUserId(back, pageBean);
//		注意：layui中的数据表格的格式
			ResponseUtil.writeJson(resp, R.ok(0, "会议通知数据查询成功",pageBean.getTotal(),infos));
		} catch (Exception e) {
			e.printStackTrace();
			try {
				ResponseUtil.writeJson(resp, R.error(0, "会议通知数据查询失败"));
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return null;
	}
	
}

实现反馈:

//	会议反馈详情
	public List> queryMeetingBackByMeetingId(MeetingFeedBack back, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {
		String sql="select rn" + 
				" t.result,GROUP_CONCAt(t.name) namesrn" + 
				" fromrn" + 
				" (select rn" + 
				" t1.name,IFNULL(f.result,-1) resultrn" + 
				" fromrn" + 
				" (SELECt * from t_oa_user where FIND_IN_SET(id,	(SELECt CONCAt(canyuze,',',liexize,',',zhuchiren) fromrn" + 
				"	t_oa_meeting_info rn" + 
				"	where id="+back.getMeetingId()+"))) t1rn" + 
				" left join t_oa_meeting_feedback f on t1.id=f.personId and f.meetingId="+back.getMeetingId()+") trn" + 
				" GROUP BY t.result";
				return super.executeQuery(sql, pageBean);
	}

效果图： 

 填写原因：