目录
本章目标
1.会议通知查询
2.某参会人的反馈
3.某会议的反馈详情
一、会议通知查询SQL(难点)
介绍：登录某人的账号，就要查出来凡是此人是参与者、列席者、主持人中的其中一员，那么都需要查询出来
查询条件：登录账户的id
分析：
会议信息表：t_oa_meeting——info
会议反馈表：t_oa_meeting_feedback
不管会议是否得到反馈，都要查询出来，所以选用外连接，会议信息表为主
select IFNULL(y.result,-1),t1.* from (select *from t_oa_meeting_info where FIND_IN_SET(13,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4)t1 left join t_oa_meeting_feedback y on t1.id=y.meetingId and y.personId=13 ORDER BY result;
二、某会议的反馈详情SQL
1.会议id为13的会议，所有参与人员的姓名
1.1：先拿到所有的参与人员id
1.2：再拿到对应参与人员的姓名
2.连接反馈表，拿到对应的反馈情况（未读、参加、不参加）
3.根据反馈情况进行分组查询条件：会议id
分析：
用到的表：
用户表：t_oa_user
会议反馈表：t_oa_meeting_feedback
1.会议id为13的会议，所有参与人员的姓名
1.1：先拿到所有的参与人员id
select CONCAT(canyuze,',',liexize,',',zhuchiren) FROM t_oa_meeting_info where id=13
1.2：再拿到对应参与人员的姓名
select * from t_oa_user where FIND_IN_SET(id,(select CONCAT(canyuze,',',liexize,',',zhuchiren) FROM t_oa_meeting_info where id=13))
2.连接反馈表，拿到对应的反馈情况（未读、参加、不参加）
select t1.name,IFNULL(f.result,-1) from (select * from t_oa_user where FIND_IN_SET(id,(select CONCAT(canyuze,',',liexize,',',zhuchiren) FROM t_oa_meeting_info where id=13))) t1 left JOIN t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = 13;
3.根据反馈情况进行分组 GROUP_CONCAT(expr)//分组连接
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
三、会议通知
界面文件层次结构
博主把js代码与jsp代码都分离了，直接带jsp界面调用就行
页面效果
界面代码
meetingNotify.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="/common/head.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/meetingNotify.js"></script>
</head>
<style>
body{
margin:15px;
}
.layui-table-cell {height: inherit;}
.layui-layer-page .layui-layer-content { overflow: visible !important;}
</style>
<body>
<!-- 搜索栏 -->
<div class="layui-form-item" style="margin:15px 0px;">
<div class="layui-inline">
<label class="layui-form-label">会议标题</label>
<div class="layui-input-inline">
<input type="hidden" id="personId" value="${user.id }"/>
<input type="text" id="title" autocomplete="off" class="layui-input">
</div>
</div>
<div class="layui-inline">
<button id="btn_search" type="button" class="layui-btn"><i class="layui-icon layui-icon-search"></i> 查询</button>
</div>
</div>
<!-- 数据表格 -->
<table id="tb" lay-filter="tb" class="layui-table" style="margin-top:-15px"></table>
<script type="text/html" id="tbar">
{{# if(d.result==-1){ }}
<a class="layui-btn layui-btn-xs" lay-event="edit">是否参会</a>
{{# } }}
</script>
</body>
</html>
meetingNotify.js
let layer,table,$,form,test;
var row;
layui.use(['layer','table','jquery','form','test'],function(){
layer=layui.layer,
table=layui.table,
form=layui.form,
test=layui.test,
$=layui.jquery;
initTable();
//查询事件
$('#btn_search').click(function(){
query();
});
});
//初始化数据表格（我的审批）
function initTable(){
table.render({ //执行渲染
elem: '#tb', //指定原始表格元素选择器（推荐id选择器）
height: 400, //自定义高度
loading: false, //是否显示加载条（默认 true）
cols: [[ //设置表头
{field: 'id', title: '会议编号', width: 90},
{field: 'title', title: '会议标题', width: 120},
{field: 'location', title: '会议地点', width: 140},
{field: 'startTime', title: '开始时间', width: 120,
templet:function(d){
return test.toDate(new Date(d.startTime));
}
},
{field: 'endTime', title: '结束时间', width: 120,
templet:function(d){
return test.toDate(new Date(d.endTime));
}
},
//{field: 'meetingState', title: '会议状态', width: 120},
/*{field: 'seatPic', title: '会议排座', width: 120,
templet: function(d){
if(d.seatPic==null || d.seatPic=="")
return "尚未排座";
else
return "<img width='120px' src='"+d.seatPic+"'/>";
}
},*/
{field: 'result', title: '反馈状态', width: 120,
templet: function(d){
if(d.result==1)
return "参会";
else if(d.result==2)
return "缺席";
else
return "未读";
}
},
{field: '', title: '操作', width: 200,toolbar:'#tbar'},
]]
});
}
//点击查询
function query(){
table.reload('tb', {
url: $("#ctx").val()+'/feedBack.action', //请求地址
method: 'POST', //请求方式，GET或者POST
loading: true, //是否显示加载条（默认 true）
page: true, //是否分页
where: { //设定异步数据接口的额外参数，任意设
'methodName':'queryMeetingFeedBackByUserId',
'personId':$('#personId').val(),
'title':$('#title').val(),
},
request: { //自定义分页请求参数名
pageName: 'page', //页码的参数名称，默认：page
limitName: 'rows' //每页数据量的参数名，默认：limit
},
done: function (res, curr, count) {
console.log(res);
}
});
//工具条事件
table.on('tool(tb)', function(obj){ //注：tool 是工具条事件名，test 是 table 原始容器的属性 lay-filter="对应的值"
row = obj.data; //获得当前行数据
var layEvent = obj.event; //获得 lay-event 对应的值（也可以是表头的 event 参数对应的值）
var tr = obj.tr; //获得当前行 tr 的 DOM 对象（如果有的话）
console.log(row);
if(layEvent === 'edit'){ //是否参会
openLayer(row.id);
} else {
}
});
}
function openLayer(id){
layer.open({
type: 2, //layer提供了5种层类型。可传入的值有：0（信息框，默认）1（页面层）2（iframe层）3（加载层）4（tips层）
title: '会议反馈', //对话框标题
area: ['660px', '400px'], //宽高
skin: 'layui-layer-rim', //样式类名
content: 'jsp/meeting/addFeedBack.jsp?id='+id, //弹出内容。可以传入普通的html内容，还可以指定DOM，更可以随着type的不同而不同
btn:['会议反馈','关闭'],
yes:function(index,layero){
//layer.msg('保存');
//调用子页面中提供的getData方法，快速获取子页面的form表单数据
let data= $(layero).find("iframe")[0].contentWindow.getData();
addMeetingFeedBack(data);
},
btn2:function(){
layer.closeAll();
}
});
}
// 对会议通知进行 参会/不参会的反馈
function addMeetingFeedBack(params){
params['methodName']="add";
console.log(params);
$.post($("#ctx").val()+'/feedBack.action',params,function(rs){
if(rs.success){
layer.closeAll();
query();
}else{
layer.msg(rs.msg,{icon:5},function(){});
}
},'json');
}后端代码实现功能
MeetingFeedBack 实体类
package com.oyang.entity;
import java.io.Serializable;
//对应t_oa_metting_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 + "]";
}
}MeetingFeedBackDao
package com.oyang.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.oyang.entity.MeetingFeedBack;
import com.oyang.util.BaseDao;
import com.oyang.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(y.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 y on t1.id=y.meetingId \r\n" +
" and y.personId="+back.getPersonId()+" \r\n" +
" ORDER BY result ";
return super.executeQuery(sql, pageBean);
}
}
MeetingFeedBackAction
package com.oyang.web;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.oyang.dao.MeetingFeedBackDao;
import com.oyang.entity.MeetingFeedBack;
import com.oyang.framework.ActionSupport;
import com.oyang.framework.ModelDriver;
import com.oyang.util.PageBean;
import com.oyang.util.R;
import com.oyang.util.ResponseUtil;
public class MeetingFeedBackAction extends ActionSupport implements ModelDriver<MeetingFeedBack>{
private MeetingFeedBack back=new MeetingFeedBack();
private MeetingFeedBackDao bdao=new MeetingFeedBackDao();
@Override
public MeetingFeedBack getModel() {
// TODO Auto-generated method stub
return back;
}
//会议通知查询
public String queryMeetingFeedBackByUserId(HttpServletRequest req, HttpServletResponse resp) {
try {
PageBean pageBean = new PageBean();
pageBean.setRequest(req);
List<Map<String, Object>> lst = bdao.queryMeetingFeedBackByUserId(back, pageBean);
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;
}
}
mvc.xml
<?xml version="1.0" encoding="UTF-8"?>
<config>
<action path="/user" type="com.oyang.web.UserAction"></action>
<action path="/permission" type="com.oyang.web.PermissionAction"></action>
<action path="/info" type="com.oyang.web.MeetingInfoAction"></action>
<action path="/audit" type="com.oyang.web.MeetingAuditAction"></action>
<action path="/feedBack" type="com.oyang.web.MeetingFeedBackAction"></action>
</config>四、会议反馈功能实现
界面效果
界面代码
addFeedBack.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="/common/head.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');
}
后端代码实现功能
MeetingFeedBackAction
public String add(HttpServletRequest req, HttpServletResponse resp) {
try {
// rs是sql语句执行的影响行数
int rs = bdao.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;
}MeetingFeedBackDao
public int add(MeetingFeedBack back) throws Exception {
String sql="insert into t_oa_meeting_feedback values(?,?,?,?,?,?)";
//前台没有传递id到后台，UUID自己生成
back.setId(UUID.randomUUID().toString().replaceAll("-", ""));
return super.executeUpdate(sql, back, new String[] {"id","meetingId","personType","personId","result","reason"});
}运行效果
点击是否参会
填上信息-点击会议反馈 
结果
可以见到，此会议我们的状态以及改成了缺席
五、反馈详情
我们看看哪些人参加了，哪些是缺席人员，以及哪些人未读
界面效果
界面代码
在上篇文章的myMeeting.js中
后端代码实现功能
MeetingFeedBackDao
//反馈详情
public List<Map<String, Object>> queryMeetingBackByMeetingId(MeetingFeedBack back, PageBean pageBean) throws Exception {
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 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\r\n";
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 = bdao.queryMeetingBackByMeetingId(back, pageBean);
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;
}OK，今日的分享就到此结束啦，如果对个位看官有帮助的话可以留下免费的赞哦(收藏或关注也行),如果文章中有什么问题或不足以及需要改正的地方可以私信博主，博主会做出改正的。个位看官，小陽在此跟大家说拜拜啦！
581
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
