目录
②、不管会议是否得到反馈,都要查询出来,所以选用外连接,会议信息表为主
3.根据反馈情况进行分组 GROUP_CONCAT(expr)
一、详情SQL语句
目标:
1.会议通知查询
我们最终目标查询出数据,就是说登录一个账号,只要这条会议有你的名字就要查询出来,如果说我没有反馈,然后不管我反馈和没有反馈这条数据都要查询出来,三种状态,一个是未读、就是不反馈,二是反馈了,不参加,三是反馈了,会参加,然后反馈了什么就要在反馈状态上显示你是未读或者参加与不参加
2.某参会人的反馈
3.某会议的反馈详情
我们最终查询出来的结果是这样的
难点:
1.会议通知查询SQL
登录我自己的账户,就要查出凡是我是参与者、列席者、主持人中的其中一员,
那么都需要查询出来
查询条件: 登录用户id 16
如图所示:
分析:
会议信息表:t_oa_meeting_info
会议反馈表:t_oa_meeting_feedback
2.某会议的反馈详情SQL
分析:
用到的表:
用户表:t_oa_user
反馈表:t_oa_meeting_feedback
会议信息表:t_oa_meeting_info
查询条件:
会议id 10
1.会议id为12的会议,所有参与人员的姓名
①:先拿到所有的参与人员id
②:在拿到对应参与人员的姓名
2.链接反馈表,拿到对应的反馈情况(未读、参加、不参加)
3.根据反馈情况进行分组
二、会议通知查询SQL
①、查询出蒋文娟 id=16 的会议信息
select * from t_oa_meeting_info where FIND_IN_SET(16,CONCAT(canyuze,',',liexize,',',zhuchiren))
然而上面那条数据应该还要带上条件,是什么状态,肯定是待开,因为审核通过了,所以是待开状态 状态:0取消会议 1新建 2待审核 3驳回 4待开 5进行中 6开启投票 7结束会议,默认值为1
select * from t_oa_meeting_info where FIND_IN_SET(16,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4
②、不管会议是否得到反馈,都要查询出来,所以选用外连接,会议信息表为主
select
t2.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 t2 on t1.id=t2.meetingId
改好了的SQL语句:
select
IFNULL(t2.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 t2 on t1.id=t2.meetingId
order by result;
带上我们的条件SQL:这就是我们的会议通知查询的SQL语句
select
IFNULL(t2.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 t2 on t1.id=t2.meetingId
and t2.personId=2
order by result;
三、 会议反馈详情SQL
1、会议id为12的会议,所有参与人员的姓名
①:先拿到所有的参与人员id
select CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id=12
②:在拿到对应参与人员的姓名
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,f.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
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
像这样如图所示:
注意这里: 上面的表还不够全面为了更好的展示我该为了10
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=10))) t1
left join t_oa_meeting_feedback f on t1.id=f.personId and f.meetingId=10
3.根据反馈情况进行分组 GROUP_CONCAT(expr)
select
t.result
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=10))) t1
left join t_oa_meeting_feedback f on t1.id=f.personId and f.meetingId=10) t
GROUP BY t.result
这就是我们最终的反馈详情SQL语句
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=10))) t1
left join t_oa_meeting_feedback f on t1.id=f.personId and f.meetingId=10) t
GROUP BY t.result
四、会议通知后台代码实现
先把我们之前的写好的项目运行起来如图所示:
-------------
缺少这个界面之后我们就要去写新建一个meetingNotify.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">
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/static/js/plugins/sign/css/www.jsdaima.com.css">
<link rel="stylesheet" type="text/css" href="${pageContext.request.contextPath }/static/js/plugins/sign/font/iconfont.css">
<script type="text/javascript" src="${pageContext.request.contextPath }/static/js/meeting/addMeetingAudit.js"></script>
<%-- <script type="text/javascript" src="${pageContext.request.contextPath }/static/js/jquery-3.3.1.min.js"></script> --%>
<%-- <script type="text/javascript" src="${pageContext.request.contextPath }/static/js/plugins/sign/index.js"></script> --%>
<title>发布会议</title>
</head>
<style>
body{
margin:5px;
}
</style>
<body>
<div style="padding:10px 20px 10px 10px;">
<form class="layui-form layui-form-pane" lay-filter="audit">
<input type="hidden" id="id" name="id"/>
<input type="hidden" id="auditor" value="${sessionScope.user.id }"/>
<div class="layui-form-item">
<label class="layui-form-label">会议标题</label>
<div class="layui-input-block">
<input type="text" name="title" autocomplete="off" class="layui-input" readonly="readonly">
</div>
</div>
<div class="layui-form-item layui-form-text">
<label class="layui-form-label">会议内容</label>
<div class="layui-input-block">
<textarea placeholder="请输入内容" name="content" class="layui-textarea" readonly="readonly"></textarea>
</div>
</div>
<!-- <div class="layui-form-item layui-form-text"> -->
<!-- <label class="layui-form-label">会议内容</label> -->
<!-- <div class="layui-input-block"> -->
<div class="canvasBox">
<div class="contro">
<ul class="drawType">
<li data-name="pen" data-nameNum="0">
<span class="icon iconfont icon-qianbi"></span>
<span class="iconAlert">铅笔</span>
</li>
<!-- <li class="downLoad">
<span class="icon iconfont icon-baocun"></span>
<span class="iconAlert">保存</span>
<img src="" alt="" class="downImg">
</li>
<li data-name="eraser" >
<span class="icon iconfont icon-xiangpi"></span>
<span class="iconAlert">橡皮</span>
</li> -->
<li class="remote">
<span class="icon iconfont icon-delete"></span>
<span class="iconAlert">清空</span>
</li>
</ul>
</div>
<div class="canvasDraw">
<div class="drawFont" data-type="hide">
<span class="intoFont"></span>
<input type="text" class="intoFontInput">
</div>
<canvas id="canvas" width="550" height="150"></canvas>
</div>
</div>
<!-- </div>
</div> -->
</form>
</div>
</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');
}
运行一下如图所示:
我们就会去自定义模块 ,专门去这个时间上的转换
复制到这里面去 test.js代码块:
//提示:模块也可以依赖其它模块,如:layui.define('layer', callback);
layui.define(function(exports){
var obj = {
hello: function(str){
alert('Hello '+ (str||'test'));
},
toDate:function(date,pattern){
return fmtDate(date,pattern);
}
};
//输出test接口
//test.hello('zs');
exports('test', obj);
});
//给Date类添加了一个新的实例方法format
Date.prototype.format = function (fmt) {
//debugger;
var o = {
"M+": this.getMonth() + 1, //月份
"d+": this.getDate(), //日
"h+": this.getHours(), //小时
"m+": this.getMinutes(), //分
"s+": this.getSeconds(), //秒
"q+": Math.floor((this.getMonth() + 3) / 3), //季度
"S": this.getMilliseconds() //毫秒
};
if (/(y+)/.test(fmt))
fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
for (var k in o)
if (new RegExp("(" + k + ")").test(fmt))
fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
return fmt;
};
function fmtDate(date, pattern) {
var ts = date.getTime();
var d = new Date(ts).format("yyyy-MM-dd hh:mm:ss");
if (pattern) {
d = new Date(ts).format(pattern);
}
return d.toLocaleString();
};
我们可以看官网如图所示:
所我们少了一个配置文件config.js代码块:
layui.config({
base: 'static/js/layui/modules/' //假设这是test.js所在的目录
}).extend({ //设定模块别名
test: 'test' //如果test.js是在根目录,也可以不用设定别名
});
我们的meetingNotify.js里面的test我们要去使用这个模块,所以说我们要引入test.js文件还要config.js文件,我们的meetingNotify.jsp是不是要引入,为了方便后续所以的模块都能用到test.js所以我们要把这个加到公共页面去,为了每一个界面都能用到。 header.jsp代码块:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!-- 引入 layui.css -->
<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>
就解决了,在运行一下如图所示:
没有数据的原因是因为我们还没有写后台
1.实体类
MeetingFeedBack.java代码块:
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 + "]";
}
}
2.dao层
3.Action层
4.配置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>
5.结果
运行一下是不是就对应了我们上面会议通知查询SQL语句查询出来的数据一样的七条记录如图所示:
五、 会议反馈功能实现
当我们点击是否参会的时候要弹出一个界面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');
}
运行一下这个界面就可以弹出来了如图所示:
当我们点击会议反馈的时候是不是要反馈给 t_oa_meeting_feedback
1.后台代码dao层
MeetingFeedBackDao.java代码块:
package com.zking.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import com.zking.entity.MeetingFeedBack;
import com.zking.util.BaseDao;
import com.zking.util.PageBean;
public class MeetingFeedBackDao extends BaseDao<MeetingFeedBack>{
// 链表查询返回的是list
// 会议通知查询
public List<Map<String, Object>> queryMeetingFeedBackByUserId(MeetingFeedBack back,PageBean pageBean)
throws InstantiationException, IllegalAccessException, SQLException {
String sql="select\r\n" +
" IFNULL(t2.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 t2 on t1.id=t2.meetingId\r\n" +
" and t2.personId="+back.getPersonId()+"\r\n" +
" order by result ";
return super.executeQuery(sql, pageBean);
}
// 会议反馈
public int add(MeetingFeedBack back) throws Exception {
String sql="insert into t_oa_meeting_feedback values(?,?,?,?,?,?)";
// 前面没有id,所以我们随机生成一个id
// 前台没有传递id到后台
back.setId(UUID.randomUUID().toString().replaceAll("-", ""));
return super.executeUpdate(sql, back, new String[] {"id","meetingId","personType","personId","result","reason"});
}
}
2.action层
MeetingFeedBackAction.java代码块:
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 e1) {
e1.printStackTrace();
}
}
return null;
}
// 会议反馈
public String add(HttpServletRequest req, HttpServletResponse resp) {
try {
// n是sql语句执行的影响行数
int n=backDao.add(back);
// 如果大于0说明可以新增
if(n>0) {
ResponseUtil.writeJson(resp, R.ok(200, "会议反馈数据新增成功"));
}
else {
ResponseUtil.writeJson(resp, R.ok(0, "会议反馈数据新增失败"));
}
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "会议反馈数据新增失败"));
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
return null;
}
}
3.运行结果
我们在去数据库里面去看看反馈表里面是不是加了一条数据如图所示:
六、反馈详情
我们对于7号会议进行了反馈,那么我们要先找到这个7的主持人是谁,谁发布的会议,谁才能看到这个详情
当我们点击反馈详情的时候要弹出一个框出来代码块在这: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代码块:
let layer,$,table,form;
var row;
layui.use(['jquery', 'layer', 'table'], function(){
layer = layui.layer
,$ = layui.jquery
,form = layui.form
,table = layui.table;
//初始化数据表格
initTable();
//绑定查询按钮的点击事件
$('#btn_search').click(function(){
query();
});
//初始化审批人
initFormSelects();
//送审
$('#btn_auditor').click(function(){
$.post($("#ctx").val()+'/info.action',{
'methodName':'updateAuditorById',
'id':$('#meetingId').val(),
'auditor':$('#auditor').val()
},function(rs){
if(rs.success){
//关闭对话框
layer.closeAll();
//刷新列表
query();
}else{
layer.msg(rs.msg,{icon:5},function(){});
}
},'json');
return false;
});
});
//1.初始化数据表格
function initTable(){
table.render({ //执行渲染
elem: '#tb', //指定原始表格元素选择器(推荐id选择器)
// url: 'user.action?methodName=list', //请求地址
height: 340, //自定义高度
loading: false, //是否显示加载条(默认 true)
cols: [[ //设置表头
{field: 'id', title: '会议编号', width: 120},
{field: 'title', title: '会议标题', width: 120},
{field: 'location', title: '会议地点', width: 140},
{field: 'startTime', title: '开始时间', width: 220},
{field: 'endTime', title: '结束时间', width: 120},
{field: 'meetingstate', title: '会议状态', width: 120},
{field: 'seatPic', title: '会议排座', width: 140,templet: function(d){
console.log(d);
//得到当前行数据,并拼接成自定义模板
return '<img src="'+d.seatPic+'">'}
},
{field: 'auditorname', title: '审批人', width: 140},
{field: '', title: '操作', width: 220,toolbar:'#tbar'},
]]
});
//在页面中的<table>中必须配置lay-filter="tb_goods"属性才能触发属性!!!
table.on('tool(tb)', function (obj) {
row = obj.data;
if (obj.event == "seat") {
open(row.id);
}else if(obj.event == "send"){
// layer.msg("送审");
// 判断是否已经排座
if(row.seatPic==null || row.seatPic==""){
layer.msg('先请完成会议排座,再进行送审操作!',function(){});
return false;
}
// 弹出层中的会议送审人员必须查询出来,后台已经完成,在多功能下拉框中已经完成
//在打开送审页面之前,先请完成会议ID的赋值操作
$('#meetingId').val(row.id);
// 打开会议送审HTML 页面层
openLayerAudit();
}else if(obj.event == "del"){
layer.msg("取消会议");
// 把我们的会议状态改为0
}else if(obj.event == "back"){
// layer.msg("反馈详细");
openLayerFeedBack(row.id);
}else{
}
});
}
//2.点击查询
function query(){
// console.log($("#ctx").val());
table.reload('tb', {
url: 'info.action', //请求地址
method: 'POST', //请求方式,GET或者POST
loading: true, //是否显示加载条(默认 true)
page: true, //是否分页
where: { //设定异步数据接口的额外参数,任意设
'methodName':'myInfos',
'title':$('#title').val(),
'zhuchiren':$('#zhuchiren').val()
},
request: { //自定义分页请求参数名
pageName: 'page', //页码的参数名称,默认:page
limitName: 'rows' //每页数据量的参数名,默认:limit
}
});
}
//打开会议排座对话框
function open(id){
layer.open({
type: 2, //layer提供了5种层类型。可传入的值有:0(信息框,默认)1(页面层)2(iframe层)3(加载层)4(tips层)
title: '会议排座', //对话框标题
area: ['460px', '340px'], //宽高
skin: 'layui-layer-rim', //样式类名
content: $("#ctx").val()+'/jsp/meeting/seatPic.jsp?id='+id, //弹出内容。可以传入普通的html内容,还可以指定DOM,更可以随着type的不同而不同
});
}
//初始化审批人
function initFormSelects(){
$.getJSON($("#ctx").val()+'/user.action',{
'methodName':'queryUserAll'
},function(rs){
console.log(rs);
let data=rs.data;
$.each(data,function(i,e){
$('#auditor').append(new Option(e.name,e.value));
});
//重新渲染
form.render('select');
});
}
//会议送审
function openLayerAudit(){
//每次打开都对送审人进行初始化默认值设置
$('#auditor').val("");
//必须重新渲染
form.render('select');
//弹出对话框
layer.open({
type: 1, //layer提供了5种层类型。可传入的值有:0(信息框,默认)1(页面层)2(iframe层)3(加载层)4(tips层)
title:'会议送审',
area: ['426px', '140px'], //宽高
skin: 'layui-layer-rim', //样式类名
content: $('#audit'), //弹出内容。可以传入普通的html内容,还可以指定DOM,更可以随着type的不同而不同
});
}
//打开查看本会议的反馈详情
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();
}
});
}
});
}
我们就开始写方法了:
1.dao层
MeetingFeedBackDao.java代码块:
package com.zking.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import com.zking.entity.MeetingFeedBack;
import com.zking.util.BaseDao;
import com.zking.util.PageBean;
public class MeetingFeedBackDao extends BaseDao<MeetingFeedBack>{
// 链表查询返回的是list
// 会议通知查询
public List<Map<String, Object>> queryMeetingFeedBackByUserId(MeetingFeedBack back,PageBean pageBean)
throws InstantiationException, IllegalAccessException, SQLException {
String sql="select\r\n" +
" IFNULL(t2.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 t2 on t1.id=t2.meetingId\r\n" +
" and t2.personId="+back.getPersonId()+"\r\n" +
" order by result ";
return super.executeQuery(sql, pageBean);
}
// 会议反馈
public int add(MeetingFeedBack back) throws Exception {
String sql="insert into t_oa_meeting_feedback values(?,?,?,?,?,?)";
// 前面没有id,所以我们随机生成一个id
// 前台没有传递id到后台
back.setId(UUID.randomUUID().toString().replaceAll("-", ""));
return super.executeUpdate(sql, back, new String[] {"id","meetingId","personType","personId","result","reason"});
}
// 反馈详情
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 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";
return super.executeQuery(sql, pageBean);
}
}
2.action层
MeetingFeedBackAction.java代码块:
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 e1) {
e1.printStackTrace();
}
}
return null;
}
// 会议反馈
public String add(HttpServletRequest req, HttpServletResponse resp) {
try {
// n是sql语句执行的影响行数
int n=backDao.add(back);
// 如果大于0说明可以新增
if(n>0) {
ResponseUtil.writeJson(resp, R.ok(200, "会议反馈数据新增成功"));
}
else {
ResponseUtil.writeJson(resp, R.ok(0, "会议反馈数据新增失败"));
}
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "会议反馈数据新增失败"));
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
return null;
}
// 反馈详情
public String queryMeetingBackByMeetingId(HttpServletRequest req, HttpServletResponse resp) {
try {
PageBean pageBean = new PageBean();
pageBean.setRequest(req);
List<Map<String, Object>> infos = backDao.queryMeetingBackByMeetingId(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 e1) {
e1.printStackTrace();
}
}
return null;
}
}
3.运行结果
我们在测试一下:登录一个左鸿伟的账户参会
在登陆回张三看看如图所示: