目录
一、查询
思路:
1、根据要查询的数据建立实体类;
2、SQL语句:先把存在登录的用户的id的会议查出来,不管会议是否得到反馈,都要查询出来
1.1 实体类
package com.zking.entity;
import java.io.Serializable;
/**
* 会议通知实体类
* @author cxy
*
*/
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 + "]";
}
}
1.2 dao方法
/**
* 会议通知:查询出我(当前登陆用户)需要参与的会议及会议的反馈信息(参会、缺席以及未读)
* @param back
* @param pageBean
* @return
* @throws SQLException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public List<Map<String,Object>>
queryMeetingFeedBackByUserId(MeetingFeedBack back,PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException{
String sql="select t1.*,ifnull(f.result,-1) result from \r\n" +
"(select * from t_oa_meeting_info where "
+ "find_in_set('"+back.getPersonId()+"',concat(canyuze,',',liexize,',',zhuchiren))) t1 \r\n" +
"left join t_oa_meeting_feedback f on "
+ "t1.id=f.meetingId and personId="+back.getPersonId()+" where f.result is null ";
sql += " order by t1.id desc";
System.out.println(sql);
return super.executeQuery(sql, pageBean);
}
1.3 web层
/**
* 会议通知分页查询
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String queryMeetingFeedBackByUserId(HttpServletRequest req,
HttpServletResponse resp) throws ServletException,IOException{
try {
PageBean pageBean=new PageBean();
pageBean.setRequest(req);
List<Map<String, Object>> lst = backDao.queryMeetingFeedBackByUserId(back, pageBean);
ResponseUtil.writeJson(resp, R.ok(0, "分页查询会议通知信息成功!", pageBean.getTotal(), lst));
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "分页查询会议通知信息失败!"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}
1.4 js代码
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: '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'){ //是否参会
} else {
}
});
}
1.5 jsp界面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@include file="/common/hearder.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>
1.6 效果
二、参会
1.1 dao方法
/**
* 新增会议反馈记录
* @param back
* @throws Exception
*/
public int add(MeetingFeedBack back) throws Exception {
String sql="insert into t_oa_meeting_feedback values(?,?,?,?,?,?)";
back.setId(UUID.randomUUID().toString().replace("-", ""));
return super.executeUpdate(sql, back, new String[] {"id","meetingId","personType","personId","result","reason"});
}
1.2 web层
/**
* 新增会议反馈记录
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String add(HttpServletRequest req,
HttpServletResponse resp) throws ServletException,IOException{
try {
backDao.add(back);
ResponseUtil.writeJson(resp, R.ok(200, "反馈成功!"));
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "反馈失败!"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}
1.3 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: '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');
}
1.4 xml配置
<?xml version="1.0" encoding="UTF-8"?>
<config>
<action path="/user" type="com.zking.web.UserAction">
</action>
<action path="/audit" type="com.zking.web.MeetingAuditAction">
</action>
<action path="/feedBack" type="com.zking.web.MeetingFeedBackAction">
</action>
</config>
1.5 效果
三、反馈结果 (我的会议)
SQL语句编写思路:
1、根据会议id,先拿到所有参与人员id
2、在拿到对应参与人员的姓名
3、连接反馈表,拿到对应的反馈情况(未读,参加,不参加)
4、根据反馈情况进行分组 GROUP_CONCAT(expr)
1.1 dao方法
/**
* 根据会议ID查询会议参与人员的反馈信息
* @param back
* @param pageBean
* @return
* @throws SQLException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public List<Map<String,Object>>
queryMeetingBackByMeetingId(MeetingFeedBack back) throws InstantiationException, IllegalAccessException, SQLException{
String sql="select result,group_concat(name) names from(\r\n" +
"select t1.name,ifnull(f.result,-1) result from \r\n" +
"(select * from t_oa_user where find_in_set(id,(\r\n" +
"select concat(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info "
+ "where id="+back.getMeetingId()+"))) t1 left join t_oa_meeting_feedback f \r\n" +
"on t1.id=f.personId and meetingId="+back.getMeetingId()+") temp group by result";
System.out.println(sql);
return super.executeQuery(sql, null);
}
1.2 web层
/**
* 根据会议ID查询参会人员的反馈信息
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String queryMeetingBackByMeetingId(HttpServletRequest req,
HttpServletResponse resp) throws ServletException,IOException{
try {
List<Map<String, Object>> lst = backDao.queryMeetingBackByMeetingId(back);
ResponseUtil.writeJson(resp, R.ok(200, "反馈详情查询成功!", lst));
} catch (Exception e) {
e.printStackTrace();
try {
ResponseUtil.writeJson(resp, R.error(0, "反馈详情查询失败!"));
} catch (Exception e1) {
e1.printStackTrace();
}
}
return null;
}
1.3 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();
}
});
}
});
}