目录
界面原型:

当登录某某的账号时,我们就要查出凡是某某是参与者、列席者、主任人的其中一员,那么都需要查询出来
分析:
所需要的表:
会议信息表:t_oa_meeting_info
会议反馈表:t_oa_meeting_feedback
查询条件:登录用户id 2为例
find_in_set(2,(1,2,3,4,5))
步骤:
1.查询出带某某及该用户的id 的会议信息
以id=2的举例
SQL语句:
select * from t_oa_meeting_info where FIND_IN_SET(2,CONCAT(canyuze,',',liexize,',',zhuchiren))根据以上SQL语句可以查询出参与者、列席者、主持人中包含id=2的所有会议
效果:
可以看出一共查询出了12条数据,但是要加上条件会议为待开状态
SQL语句:
select * from t_oa_meeting_info where FIND_IN_SET(2,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4效果:
2、不管会议是否得到反馈,都要查询出来,所以选用外连接,以会议信息表为主表
用外连接的话可以防止数据丢失,而且我们可以发现界面原型图上的数据大部分为信息表中的
SQL语句:
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_feedback
会议信息表:t_oa_meeting_info
查询条件:
会议的id 12为例
1.会议id为12的会议,所有参与人员的姓名
1.1:先拿到所有的参与人员id
SQL语句:
SELECT CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12效果:
1.2:在拿到对应参与人员的姓名
SQL语句:
select * from t_oa_user where FIND_IN_SET(id,(SELECT CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))效果:
![]()
2.外链接反馈表,拿到对应的反馈情况(未读、参加、不参加)
SQL语句:
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.根据反馈情况进行分组
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 = 12))) t1 left join t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = 12) t GROUP BY t.resultGROUP_CONCAT:为分组链接查询
效果:
meetingNotify.jsp:
- <%@ page language="java" contentType="text/html; charset=UTF-8"
- pageEncoding="UTF-8"%>
- <%@include file="/common/header.jsp"%>
- DOCTYPE html>
- body{
- margin:15px;
- }
- .layui-table-cell {height: inherit;}
- .layui-layer-page .layui-layer-content { overflow: visible !important;}
-
-
-
-
-
-
-
-
-
-
-
- {{# if(d.result==-1){ }}
- {{# } }}
-
-
-
-
-
-
-
-
-
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 "
"; - }
- },*/
- {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');
- }
由于该js文件中我们加入一个自定义模块,该自定义模块是为了转换时间。所以我们要将该文件夹放到外面的项目中

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是在根目录,也可以不用设定别名
- });
为了每一个页面都加载出我们刚刚自定义的模块的js,所以我们公共页面的jsp需要进行更改
header.jsp:
- <%@ page language="java" contentType="text/html; charset=UTF-8"
- pageEncoding="UTF-8"%>
- <link rel="stylesheet" href="${pageContext.request.contextPath }/static/js/layui/css/layui.css">
- <script src="${pageContext.request.contextPath }/static/js/layui/layui.js">script>
- <base href="${pageContext.request.contextPath }/" />
- <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 + "]";
- }
-
- }
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
{ -
- // 会议通知查询
- public List
- 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 and f.personId = "+back.getPersonId()+" ORDER BY result";
- return super.executeQuery(sql, pageBean);
- }
- }
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
{ - 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
- // layui的code返回一定是0,不能是200,否者返回不了数据
- ResponseUtil.writeJson(resp, R.ok(0, "会议通知数据查询成功",pageBean.getTotal(),lst));
- } catch (Exception e) {
- e.printStackTrace();
- try {
- ResponseUtil.writeJson(resp, R.ok(0, "会议通知数据查询失败"));
- } catch (Exception e1) {
- e1.printStackTrace();
- }
- }
- return null;
- }
- }
效果:

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">
-
- <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');
- }
效果:

将以下方法加入到MeetingFeedBackDao:
- // 会议反馈
- public int add(MeetingFeedBack back) throws Exception {
- String sql="insert into t_oa_meeting_feedback values(?,?,?,?,?,?)";
- // 全体没有传递id到后台
- back.setId(UUID.randomUUID().toString().replaceAll("-", ""));
- 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, "会议信息数据新增成功"));
- }
- } catch (Exception e) {
- e.printStackTrace();
- try {
- ResponseUtil.writeJson(resp, R.ok(0, "数会议信息据新增失败"));
- } catch (Exception e1) {
- // TODO Auto-generated catch block
- e1.printStackTrace();
- }
- }
- return null;
- }
效果:
选择会议编号为1


将以下方法加入到MeetingFeedBackAction
- // 反馈详情
- public String queryMeetingBackByMeetingId(HttpServletRequest req, HttpServletResponse resp) {
- try {
- PageBean pageBean = new PageBean();
- pageBean.setRequest(req);
- List
- // layui的code返回一定是0,不能是200,否者返回不了数据
- ResponseUtil.writeJson(resp, R.ok(0, "反馈详情数据查询成功",pageBean.getTotal(),lst));
- } catch (Exception e) {
- e.printStackTrace();
- try {
- ResponseUtil.writeJson(resp, R.ok(0, "反馈详情数据查询失败"));
- } catch (Exception e1) {
- e1.printStackTrace();
- }
- }
- return null;
- }
将以下方法加入到MeetingFeedBackDao
- // 反馈详情
- public List
- 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);
- }
将以下方法加入到 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();
- }
- });
- }
- });
- }
将原来的layer.msg("反馈详情")
效果:
