当前位置：网站首页>OA project meeting notice (query & whether attending & feedback for details)

OA project meeting notice (query & whether attending & feedback for details)

2022-08-11 07:09:00 【Yu Musheng】

目录

一、SQL语句编写

1、会议通知查询SQL

2、会议反馈详情SQL

二、会议通知后台代码实现

前端

后端

三、会议反馈功能实现

前端

后端 

四、反馈详情

一、SQL语句编写

1、会议通知查询SQL

界面原型：

When logging in to someone's account,We're going to find out who so-and-so is a participant、列席者、one of the directors,那么都需要查询出来

分析：

所需要的表：     

                  会议信息表：t_oa_meeting_info

                  会议反馈表：t_oa_meeting_feedback

查询条件：登录用户id 2为例

find_in_set(2,(1,2,3,4,5))

步骤：

1.Query out the one with so-and-so and the userid 的会议信息

以id=2的举例

SQL语句：

select * from t_oa_meeting_info where FIND_IN_SET(2,CONCAT(canyuze,',',liexize,',',zhuchiren))

根据以上SQLThe statement can query the participants、列席者、Included in the hostid=2all meetings of

效果：

 It can be seen that a total of queries have been made12条数据,But add the conditional meeting as pending status

SQL语句：

select * from t_oa_meeting_info where FIND_IN_SET(2,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4

效果：

 2、不管会议是否得到反馈,都要查询出来,所以选用外连接,Take the meeting information table as the main table

If you use an outer connection, you can prevent data loss,And we can find that most of the data on the interface prototype diagram is in the information table

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;

效果：

2、会议反馈详情SQL

界面原型图：

分析： 

 用到的表：

        用户表：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：Get the names of the corresponding participants

                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.External link feedback form,拿到对应的反馈情况（未读、参加、不参加）

       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.result

GROUP_CONCAT：Query for grouped links

        效果：

二、会议通知后台代码实现

前端

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

 由于该jsWe add a custom module to the file,This custom module is for converting time.So we're going to put that folder into an outside project

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是在根目录,也可以不用设定别名
});

For each page, the module we just customized is loadedjs,So our public pagejsp需要进行更改

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">
<!-- 引入 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>

前端效果：

后端

Meeting feedback form entity class(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<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 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<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>> lst = backDao.queryMeetingFeedBackByUserId(back, pageBean);
//		layui的code返回一定是0,不能是200,Otherwise, no data will be returned
			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">
    	<!-- <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');
}

效果：

后端 

将以下方法加入到MeetingFeedBackDao：

//	会议反馈
	public int add(MeetingFeedBack back) throws Exception {
		String sql="insert into t_oa_meeting_feedback values(?,?,?,?,?,?)";
//		The whole is not deliveredid到后台
		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, "Failed to add meeting information"));
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return null;
	}

效果：

Select the meeting number as1 

四、反馈详情

将以下方法加入到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的code返回一定是0,不能是200,Otherwise, no data will be returned
			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<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);
	}

将以下方法加入到 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("反馈详情") 

 

效果：

 

原网站

版权声明
本文为[Yu Musheng]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/223/202208110517296811.html

边栏推荐

猜你喜欢

随机推荐