文章目录
MeetingFeedBackAction--弹框的会议反馈功能
本期任务会议通知--查询并选择是否参会,我的会议--反馈不参加会议详情功能⬇⬇
一、SQL编写
1、会议通知查询SQL
假设登录t_oa_user表中的张q账号,就要查出凡是张q是参与者、列席者、主持人中的其中一员,那么都要查询出来
查询条件:登录用户的id
分析涉及表有:会议信息表:t_oa_meeting_info
会议信息反馈表: t_oa_meeting_feedback
1.查询出带张强 id为2的会议信息
select * from t_oa_meeting_info where FIND_IN_SET(2,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4
2.不管会议是否得到反馈,都要查询出来,所以选用外连接,以会议信息表为主
反馈:-1未读 1参加会议 2不参加
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;
2、某会议的反馈详情SQL
查询条件:会议的id
最终的查询结果,希望实现如图显示⬇⬇
分析涉及表有:用户表:t_oa_user
会议反馈表: t_oa_meeting_feedback
会议信息表:t_oa_meeting_info
1、先拿到会议id为12的会议及所有参与人员的姓名
1.1先拿到所有的参与人员id
1.2再拿到对应参与人员的姓名
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
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
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
二、会议通知后台代码实现
公共页面: header.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<link rel="stylesheet" href="${pageContext.request.contextPath }/static/js/layui/css/layui.css">
<!-- 引入 layui.js -->
<script src="${pageContext.request.contextPath }/static/js/layui/layui.js"></script>
<!-- 指定整个项目的根路径 -->
<base href="${pageContext.request.contextPath }/"/>
<!-- 存放layui扩展模块的配置文件 -->
<script src="${pageContext.request.contextPath }/static/js/layui/config.js"></script>
<input id="ctx" value="${pageContext.request.contextPath }" type="hidden"/>
<title>小坤工作室</title>
实体类MeetingFeedBack
package com.zking.entity;
import java.io.Serializable;
/**
* t_oa_meeting_feedback
* 对应会议反馈表
*
*/
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 + "]";
}
}
web层dao层
MeetingFeedBackDao
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<MeetingFeedBack>{
//会议通知查询
public List<Map<String, Object>> queryMeetingFeedBackByUserId(MeetingFeedBack back, PageBean pageBean)
throws SQLException, InstantiationException, IllegalAccessException {
String sql="SELECT\r\n" +
" IFNULL(f.result,-1) result,t1.*\r\n" +
" FROM\r\n" +
" (SELECT * from t_oa_meeting_info where FIND_IN_SET("+back.getPersonId()+",CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4) t1\r\n" +
" LEFT JOIN t_oa_meeting_feedback f on t1.id=f.meetingId\r\n" +
" and f.personId="+back.getPersonId()+"\r\n" +
" ORDER BY result";
return super.executeQuery(sql, pageBean);
}
//back.getPersonId()
}
MeetingFeedBackAction
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<MeetingFeedBack>{
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<Map<String, Object>> 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;
}
}
配置xml--mvc.xml
<?xml version="1.0" encoding="UTF-8"?>
<config>
<action path="/user" type="com.zking.web.UserAction">
</action>
<action path="/permission" type="com.zking.web.PermissionAction">
</action>
<action path="/info" type="com.zking.web.MeetingInfoAction">
</action>
<action path="/audit" type="com.zking.web.MeetingAuditAction">
</action>
<action path="/feedBack" type="com.zking.web.MeetingFeedBackAction">
</action>
</config>
页面运行效果
当我点击是否参会时,应该要弹出一个界面⬇⬇⬇
接下来我们完成这个弹框界面的会议反馈功能
addFeedBack.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="/common/header.jsp"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script type="text/javascript" src="${pageContext.request.contextPath }/static/js/meeting/addFeedBack.js"></script>
</head>
<style>
body{
margin:5px;
}
</style>
<body>
<div style="padding:10px;">
<form class="layui-form layui-form-pane" lay-filter="back">
<!-- <div class="layui-form-item">
<button type="submit" class="layui-btn" lay-submit="" lay-filter="meeting">立即提交</button>
<button id="reset" type="reset" class="layui-btn layui-btn-primary">重置</button>
</div> -->
<input type="hidden" name="meetingId" value="${param.id }"/>
<input type="hidden" name="personId" value="${sessionScope.user.id }"/>
<div class="layui-form-item">
<label class="layui-form-label">人员类型</label>
<div class="layui-input-block">
<select id="personType" name="personType">
<option value="">请选择人员类型</option>
<option value="1">参会</option>
<option value="2">列席</option>
</select>
</div>
</div>
<div class="layui-form-item">
<label class="layui-form-label">反馈结果</label>
<div class="layui-input-block">
<select id="result" name="result">
<option value="">请选择反馈结果</option>
<option value="1">参加</option>
<option value="2">不参加</option>
</select>
</div>
</div>
<div class="layui-form-item layui-form-text">
<label class="layui-form-label">不参与会议的原因</label>
<div class="layui-input-block">
<textarea placeholder="请输入内容" name="reason" class="layui-textarea"></textarea>
</div>
</div>
</form>
</div>
</body>
</html>
addFeedBack.js
let form,$;
layui.use(['form','jquery'],function(){
form=layui.form,
$=layui.jquery;
});
function getData(){
return form.val('back');
}
web层dao层
MeetingFeedBackDao--弹框的会议反馈功能
// 会议反馈
public int add(MeetingFeedBack back) throws Exception {
String sql="insert into t_oa_meeting_feedback values (?,?,?,?,?,?)";
//前台没有传递id到后台,所以自己生成id
back.setId(UUID.randomUUID().toString().replace("-", ""));
return super.executeUpdate(sql, back, new String [] {"id","meetingId","personType","personId","result","reason"});
}
MeetingFeedBackAction--弹框的会议反馈功能
//会议反馈
public String add(HttpServletRequest req, HttpServletResponse resp) {
try {
// rs是sql语句执行的影响行数
int rs = backDao.add(back);
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 e2) {
e2.printStackTrace();
}
}
return null;
}
页面运行测试一下 :
反馈表中reason数据已更改
三、我的会议查看反馈详情
myMeeting.jsp--我的会议界面反馈详情弹框
<!-- 对话框(反馈详情) -->
<div id="feedback" style="display:none;padding:15px;">
<fieldset class="layui-elem-field layui-field-title">
<legend>参会人员</legend>
</fieldset>
<blockquote class="layui-elem-quote" id="meeting_ok"></blockquote>
<fieldset class="layui-elem-field layui-field-title">
<legend>缺席人员</legend>
</fieldset>
<blockquote class="layui-elem-quote" id="meeting_no"></blockquote>
<fieldset class="layui-elem-field layui-field-title">
<legend>未读人员</legend>
</fieldset>
<blockquote class="layui-elem-quote" id="meeting_noread"></blockquote>
</div>
myMeeting.js
//打开查看本会议的反馈详情
function openLayerFeedBack(id){
$.getJSON('feedBack.action',{
methodName:'queryMeetingBackByMeetingId',
meetingId:id
},function(data){
$('#meeting_ok').html("");
$('#meeting_no').html("");
$('#meeting_noread').html("");
if(data.success){
console.log(data.data);
$.each(data.data,function(i,e){
if(e.result==1)
$('#meeting_ok').html(e.names);
else if(e.result==2)
$('#meeting_no').html(e.names);
else
$('#meeting_noread').html(e.names);
});
//弹出对话框
layer.open({
type: 1, //layer提供了5种层类型。可传入的值有:0(信息框,默认)1(页面层)2(iframe层)3(加载层)4(tips层)
title:'反馈详情',
area: ['426px', '420px'], //宽高
skin: 'layui-layer-rim', //样式类名
content: $('#feedback'), //弹出内容。可以传入普通的html内容,还可以指定DOM,更可以随着type的不同而不同
btn:['关闭'],
yes:function(index,layero){
layer.closeAll();
}
});
}
});
}
MeetingFeedBackDao
// 会议反馈详情
public List<Map<String, Object>> queryMeetingBackByMeetingId(MeetingFeedBack back, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {
String sql="select \r\n" +
" t.result,GROUP_CONCAT(t.name) names\r\n" +
" from\r\n" +
" (select \r\n" +
" t1.name,IFNULL(f.result,-1) result\r\n" +
" from\r\n" +
" (SELECT * from t_oa_user where FIND_IN_SET(id, (SELECT CONCAT(canyuze,',',liexize,',',zhuchiren) from\r\n" +
" t_oa_meeting_info \r\n" +
" where id="+back.getMeetingId()+"))) t1\r\n" +
" left join t_oa_meeting_feedback f on t1.id=f.personId and f.meetingId="+back.getMeetingId()+") t\r\n" +
" GROUP BY t.result";
return super.executeQuery(sql, pageBean);
}
MeetingFeedBackAction
//会议反馈详情
public String queryMeetingBackByMeetingId(HttpServletRequest req, HttpServletResponse resp) {
try {
PageBean pageBean=new PageBean();
pageBean.setRequest(req);
List<Map<String, Object>> lst = backDao.queryMeetingBackByMeetingId(back, pageBean);
// 注意:layui中的数据表格的格式
ResponseUtil.writeJson(resp, R.ok(0, "会议反馈详情数据查询成功",pageBean.getTotal(),lst));
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "会议反馈详情数据查询失败"));
} catch (Exception e2) {
e2.printStackTrace();
}
}
return null;
}
登录张三的在t_oa_user中的账号并查看
好了,差不蛮多,拜拜~~~
对了对了,还有一张图