• OA项目之会议通知(查询&是否参会&反馈详情)


    目录

    一、SQL语句编写

    1、会议通知查询SQL

    2、会议反馈详情SQL

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

    前端

    后端

    三、会议反馈功能实现

    前端

    后端 

    四、反馈详情


    一、SQL语句编写

    1、会议通知查询SQL

    界面原型:

    当登录某某的账号时,我们就要查出凡是某某是参与者、列席者、主任人的其中一员,那么都需要查询出来

    分析:

    所需要的表:     

                      会议信息表: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语句:

    1. SELECT
    2. IFNULL(f.result,-1) result,t1.*
    3. FROM
    4. (select * from t_oa_meeting_info where FIND_IN_SET(2,CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4)t1
    5. 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:在拿到对应参与人员的姓名

                    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语句:

    1. select
    2. t1.name,IFNULL(f.result,-1) result
    3. FROM
    4. (select * from t_oa_user where FIND_IN_SET(id,(SELECT CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))) t1
    5. left join t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = 12

           效果:

            3.根据反馈情况进行分组

            SQL语句:

    1. SELECT
    2. t.result,GROUP_CONCAT(t.name) names
    3. FROM
    4. (select
    5. t1.name,IFNULL(f.result,-1) result
    6. FROM
    7. (select * from t_oa_user where FIND_IN_SET(id,(SELECT CONCAT(canyuze,',',liexize,',',zhuchiren) from t_oa_meeting_info where id = 12))) t1
    8. left join t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = 12) t
    9. GROUP BY t.result

    GROUP_CONCAT:为分组链接查询

            效果:

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

    前端

    meetingNotify.jsp:

    1. <%@ page language="java" contentType="text/html; charset=UTF-8"
    2. pageEncoding="UTF-8"%>
    3. <%@include file="/common/header.jsp"%>
    4. DOCTYPE html>
  •  meetingNotify.js

    1. let layer,table,$,form,test;
    2. var row;
    3. layui.use(['layer','table','jquery','form','test'],function(){
    4. layer=layui.layer,
    5. table=layui.table,
    6. form=layui.form,
    7. test=layui.test,
    8. $=layui.jquery;
    9. initTable();
    10. //查询事件
    11. $('#btn_search').click(function(){
    12. query();
    13. });
    14. });
    15. //初始化数据表格(我的审批)
    16. function initTable(){
    17. table.render({ //执行渲染
    18. elem: '#tb', //指定原始表格元素选择器(推荐id选择器)
    19. height: 400, //自定义高度
    20. loading: false, //是否显示加载条(默认 true)
    21. cols: [[ //设置表头
    22. {field: 'id', title: '会议编号', width: 90},
    23. {field: 'title', title: '会议标题', width: 120},
    24. {field: 'location', title: '会议地点', width: 140},
    25. {field: 'startTime', title: '开始时间', width: 120,
    26. templet:function(d){
    27. return test.toDate(new Date(d.startTime));
    28. }
    29. },
    30. {field: 'endTime', title: '结束时间', width: 120,
    31. templet:function(d){
    32. return test.toDate(new Date(d.endTime));
    33. }
    34. },
    35. //{field: 'meetingState', title: '会议状态', width: 120},
    36. /*{field: 'seatPic', title: '会议排座', width: 120,
    37. templet: function(d){
    38. if(d.seatPic==null || d.seatPic=="")
    39. return "尚未排座";
    40. else
    41. return "";
    42. }
    43. },*/
    44. {field: 'result', title: '反馈状态', width: 120,
    45. templet: function(d){
    46. if(d.result==1)
    47. return "参会";
    48. else if(d.result==2)
    49. return "缺席";
    50. else
    51. return "未读";
    52. }
    53. },
    54. {field: '', title: '操作', width: 200,toolbar:'#tbar'},
    55. ]]
    56. });
    57. }
    58. //点击查询
    59. function query(){
    60. table.reload('tb', {
    61. url: $("#ctx").val()+'/feedBack.action', //请求地址
    62. method: 'POST', //请求方式,GET或者POST
    63. loading: true, //是否显示加载条(默认 true)
    64. page: true, //是否分页
    65. where: { //设定异步数据接口的额外参数,任意设
    66. 'methodName':'queryMeetingFeedBackByUserId',
    67. 'personId':$('#personId').val(),
    68. 'title':$('#title').val(),
    69. },
    70. request: { //自定义分页请求参数名
    71. pageName: 'page', //页码的参数名称,默认:page
    72. limitName: 'rows' //每页数据量的参数名,默认:limit
    73. },
    74. done: function (res, curr, count) {
    75. console.log(res);
    76. }
    77. });
    78. //工具条事件
    79. table.on('tool(tb)', function(obj){ //注:tool 是工具条事件名,test 是 table 原始容器的属性 lay-filter="对应的值"
    80. row = obj.data; //获得当前行数据
    81. var layEvent = obj.event; //获得 lay-event 对应的值(也可以是表头的 event 参数对应的值)
    82. var tr = obj.tr; //获得当前行 tr 的 DOM 对象(如果有的话)
    83. console.log(row);
    84. if(layEvent === 'edit'){ //是否参会
    85. openLayer(row.id);
    86. } else {
    87. }
    88. });
    89. }
    90. function openLayer(id){
    91. layer.open({
    92. type: 2, //layer提供了5种层类型。可传入的值有:0(信息框,默认)1(页面层)2(iframe层)3(加载层)4(tips层)
    93. title: '会议反馈', //对话框标题
    94. area: ['660px', '400px'], //宽高
    95. skin: 'layui-layer-rim', //样式类名
    96. content: 'jsp/meeting/addFeedBack.jsp?id='+id, //弹出内容。可以传入普通的html内容,还可以指定DOM,更可以随着type的不同而不同
    97. btn:['会议反馈','关闭'],
    98. yes:function(index,layero){
    99. //layer.msg('保存');
    100. //调用子页面中提供的getData方法,快速获取子页面的form表单数据
    101. let data= $(layero).find("iframe")[0].contentWindow.getData();
    102. addMeetingFeedBack(data);
    103. },
    104. btn2:function(){
    105. layer.closeAll();
    106. }
    107. });
    108. }
    109. // 对会议通知进行 参会/不参会的反馈
    110. function addMeetingFeedBack(params){
    111. params['methodName']="add";
    112. console.log(params);
    113. $.post($("#ctx").val()+'/feedBack.action',params,function(rs){
    114. if(rs.success){
    115. layer.closeAll();
    116. query();
    117. }else{
    118. layer.msg(rs.msg,{icon:5},function(){});
    119. }
    120. },'json');
    121. }

     由于该js文件中我们加入一个自定义模块,该自定义模块是为了转换时间。所以我们要将该文件夹放到外面的项目中

    test.js

    1. //提示:模块也可以依赖其它模块,如:layui.define('layer', callback);
    2. layui.define(function(exports){
    3. var obj = {
    4. hello: function(str){
    5. alert('Hello '+ (str||'test'));
    6. },
    7. toDate:function(date,pattern){
    8. return fmtDate(date,pattern);
    9. }
    10. };
    11. //输出test接口
    12. //test.hello('zs');
    13. exports('test', obj);
    14. });
    15. //给Date类添加了一个新的实例方法format
    16. Date.prototype.format = function (fmt) {
    17. //debugger;
    18. var o = {
    19. "M+": this.getMonth() + 1, //月份
    20. "d+": this.getDate(), //日
    21. "h+": this.getHours(), //小时
    22. "m+": this.getMinutes(), //分
    23. "s+": this.getSeconds(), //秒
    24. "q+": Math.floor((this.getMonth() + 3) / 3), //季度
    25. "S": this.getMilliseconds() //毫秒
    26. };
    27. if (/(y+)/.test(fmt))
    28. fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    29. for (var k in o)
    30. if (new RegExp("(" + k + ")").test(fmt))
    31. fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    32. return fmt;
    33. };
    34. function fmtDate(date, pattern) {
    35. var ts = date.getTime();
    36. var d = new Date(ts).format("yyyy-MM-dd hh:mm:ss");
    37. if (pattern) {
    38. d = new Date(ts).format(pattern);
    39. }
    40. return d.toLocaleString();
    41. };

    config.js

    1. layui.config({
    2. base: 'static/js/layui/modules/' //假设这是test.js所在的目录
    3. }).extend({ //设定模块别名
    4. test: 'test' //如果test.js是在根目录,也可以不用设定别名
    5. });

    为了每一个页面都加载出我们刚刚自定义的模块的js,所以我们公共页面的jsp需要进行更改

    header.jsp:

    1. <%@ page language="java" contentType="text/html; charset=UTF-8"
    2. pageEncoding="UTF-8"%>
    3. <link rel="stylesheet" href="${pageContext.request.contextPath }/static/js/layui/css/layui.css">
    4. <script src="${pageContext.request.contextPath }/static/js/layui/layui.js">script>
    5. <base href="${pageContext.request.contextPath }/" />
    6. <script src="${pageContext.request.contextPath }/static/js/layui/config.js">script>
    7. <input id="ctx" value="${pageContext.request.contextPath }" type="hidden" />
    8. <title>玉渊工作室title>

    前端效果:

    后端

    会议反馈表实体类(MeetingFeedBack):

    1. package com.zking.entity;
    2. import java.io.Serializable;
    3. //t_oa_meeting_feedback 会议反馈表
    4. public class MeetingFeedBack implements Serializable {
    5. private String id;
    6. private Long meetingId;
    7. private Integer personType;
    8. private Long personId;
    9. private Integer result;
    10. private String reason;
    11. // 会议标题
    12. private String title;
    13. public String getTitle() {
    14. return title;
    15. }
    16. public void setTitle(String title) {
    17. this.title = title;
    18. }
    19. public String getId() {
    20. return id;
    21. }
    22. public void setId(String id) {
    23. this.id = id;
    24. }
    25. public Long getMeetingId() {
    26. return meetingId;
    27. }
    28. public void setMeetingId(Long meetingId) {
    29. this.meetingId = meetingId;
    30. }
    31. public Integer getPersonType() {
    32. return personType;
    33. }
    34. public void setPersonType(Integer personType) {
    35. this.personType = personType;
    36. }
    37. public Long getPersonId() {
    38. return personId;
    39. }
    40. public void setPersonId(Long personId) {
    41. this.personId = personId;
    42. }
    43. public Integer getResult() {
    44. return result;
    45. }
    46. public void setResult(Integer result) {
    47. this.result = result;
    48. }
    49. public String getReason() {
    50. return reason;
    51. }
    52. public void setReason(String reason) {
    53. this.reason = reason;
    54. }
    55. public MeetingFeedBack() {
    56. super();
    57. // TODO Auto-generated constructor stub
    58. }
    59. @Override
    60. public String toString() {
    61. return "MeetingFeedBack [id=" + id + ", meetingId=" + meetingId + ", personType=" + personType + ", personId="
    62. + personId + ", result=" + result + ", reason=" + reason + "]";
    63. }
    64. }

     MeetingFeedBackDao:

    1. package com.zking.dao;
    2. import java.sql.SQLException;
    3. import java.util.List;
    4. import java.util.Map;
    5. import com.zking.entity.MeetingFeedBack;
    6. import com.zking.util.BaseDao;
    7. import com.zking.util.PageBean;
    8. public class MeetingFeedBackDao extends BaseDao{
    9. // 会议通知查询
    10. public List> queryMeetingFeedBackByUserId(MeetingFeedBack back, PageBean pageBean)
    11. throws SQLException, InstantiationException, IllegalAccessException {
    12. String sql = "SELECT \r\n" +
    13. " IFNULL(f.result,-1) result,t1.*\r\n" +
    14. " FROM\r\n" +
    15. " (select * from t_oa_meeting_info where FIND_IN_SET("+back.getPersonId()+",CONCAT(canyuze,',',liexize,',',zhuchiren)) and state=4)t1 \r\n" +
    16. " LEFT JOIN t_oa_meeting_feedback f on t1.id=f.meetingId and f.personId = "+back.getPersonId()+" ORDER BY result";
    17. return super.executeQuery(sql, pageBean);
    18. }
    19. }

     MeetingFeedBackAction:

    1. package com.zking.web;
    2. import java.util.List;
    3. import java.util.Map;
    4. import javax.servlet.http.HttpServletRequest;
    5. import javax.servlet.http.HttpServletResponse;
    6. import com.zking.dao.MeetingFeedBackDao;
    7. import com.zking.entity.MeetingFeedBack;
    8. import com.zking.framework.ActionSupport;
    9. import com.zking.framework.ModelDriver;
    10. import com.zking.util.PageBean;
    11. import com.zking.util.R;
    12. import com.zking.util.ResponseUtil;
    13. public class MeetingFeedBackAction extends ActionSupport implements ModelDriver{
    14. private MeetingFeedBack back=new MeetingFeedBack();
    15. private MeetingFeedBackDao backDao=new MeetingFeedBackDao();
    16. @Override
    17. public MeetingFeedBack getModel() {
    18. return back;
    19. }
    20. // 会议通知查询
    21. public String queryMeetingFeedBackByUserId(HttpServletRequest req, HttpServletResponse resp) {
    22. try {
    23. PageBean pageBean = new PageBean();
    24. pageBean.setRequest(req);
    25. List> lst = backDao.queryMeetingFeedBackByUserId(back, pageBean);
    26. // layui的code返回一定是0,不能是200,否者返回不了数据
    27. ResponseUtil.writeJson(resp, R.ok(0, "会议通知数据查询成功",pageBean.getTotal(),lst));
    28. } catch (Exception e) {
    29. e.printStackTrace();
    30. try {
    31. ResponseUtil.writeJson(resp, R.ok(0, "会议通知数据查询失败"));
    32. } catch (Exception e1) {
    33. e1.printStackTrace();
    34. }
    35. }
    36. return null;
    37. }
    38. }

    效果:

    三、会议反馈功能实现

    前端

    addFeedBack.jsp:

    1. <%@ page language="java" contentType="text/html; charset=UTF-8"
    2. pageEncoding="UTF-8"%>
    3. <%@include file="/common/header.jsp"%>
    4. DOCTYPE html>
    5. <html>
    6. <head>
    7. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    8. <script type="text/javascript" src="${pageContext.request.contextPath }/static/js/meeting/addFeedBack.js">script>
    9. head>
    10. <style>
    11. body{
    12. margin:5px;
    13. }
    14. style>
    15. <body>
    16. <div style="padding:10px;">
    17. <form class="layui-form layui-form-pane" lay-filter="back">
    18. <input type="hidden" name="meetingId" value="${param.id }"/>
    19. <input type="hidden" name="personId" value="${sessionScope.user.id }"/>
    20. <div class="layui-form-item">
    21. <label class="layui-form-label">人员类型label>
    22. <div class="layui-input-block">
    23. <select id="personType" name="personType">
    24. <option value="">请选择人员类型option>
    25. <option value="1">参会option>
    26. <option value="2">列席option>
    27. select>
    28. div>
    29. div>
    30. <div class="layui-form-item">
    31. <label class="layui-form-label">反馈结果label>
    32. <div class="layui-input-block">
    33. <select id="result" name="result">
    34. <option value="">请选择反馈结果option>
    35. <option value="1">参加option>
    36. <option value="2">不参加option>
    37. select>
    38. div>
    39. div>
    40. <div class="layui-form-item layui-form-text">
    41. <label class="layui-form-label">不参与会议的原因label>
    42. <div class="layui-input-block">
    43. <textarea placeholder="请输入内容" name="reason" class="layui-textarea">textarea>
    44. div>
    45. div>
    46. form>
    47. div>
    48. body>
    49. html>

    addFeedBack.js:

    1. let form,$;
    2. layui.use(['form','jquery'],function(){
    3. form=layui.form,
    4. $=layui.jquery;
    5. });
    6. function getData(){
    7. return form.val('back');
    8. }

    效果:

    后端 

    将以下方法加入到MeetingFeedBackDao:

    1. // 会议反馈
    2. public int add(MeetingFeedBack back) throws Exception {
    3. String sql="insert into t_oa_meeting_feedback values(?,?,?,?,?,?)";
    4. // 全体没有传递id到后台
    5. back.setId(UUID.randomUUID().toString().replaceAll("-", ""));
    6. return super.executeUpdate(sql, back, new String[] {"id","meetingId","personType","personId","result","reason"});
    7. }

     将以下方法加入到MeetingFeedBackAction:

    1. //会议反馈
    2. public String add(HttpServletRequest req, HttpServletResponse resp) {
    3. try {
    4. // rs是sql语句执行的影响行数
    5. int rs = backDao.add(back);
    6. if(rs>0) {
    7. ResponseUtil.writeJson(resp, R.ok(200, "会议信息数据新增成功"));
    8. }
    9. } catch (Exception e) {
    10. e.printStackTrace();
    11. try {
    12. ResponseUtil.writeJson(resp, R.ok(0, "数会议信息据新增失败"));
    13. } catch (Exception e1) {
    14. // TODO Auto-generated catch block
    15. e1.printStackTrace();
    16. }
    17. }
    18. return null;
    19. }

    效果:

    选择会议编号为1 

    四、反馈详情

    将以下方法加入到MeetingFeedBackAction

    1. // 反馈详情
    2. public String queryMeetingBackByMeetingId(HttpServletRequest req, HttpServletResponse resp) {
    3. try {
    4. PageBean pageBean = new PageBean();
    5. pageBean.setRequest(req);
    6. List> lst = backDao.queryMeetingBackByMeetingId(back, pageBean);
    7. // layui的code返回一定是0,不能是200,否者返回不了数据
    8. ResponseUtil.writeJson(resp, R.ok(0, "反馈详情数据查询成功",pageBean.getTotal(),lst));
    9. } catch (Exception e) {
    10. e.printStackTrace();
    11. try {
    12. ResponseUtil.writeJson(resp, R.ok(0, "反馈详情数据查询失败"));
    13. } catch (Exception e1) {
    14. e1.printStackTrace();
    15. }
    16. }
    17. return null;
    18. }

    将以下方法加入到MeetingFeedBackDao

    1. // 反馈详情
    2. public List> queryMeetingBackByMeetingId(MeetingFeedBack back, PageBean pageBean) throws InstantiationException, IllegalAccessException, SQLException {
    3. String sql="SELECT\r\n" +
    4. " t.result,GROUP_CONCAT(t.name) names\r\n" +
    5. " FROM\r\n" +
    6. " (select\r\n" +
    7. " t1.name,IFNULL(f.result,-1) result\r\n" +
    8. " FROM\r\n" +
    9. " (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" +
    10. " left join t_oa_meeting_feedback f on t1.id = f.personId and f.meetingId = "+back.getMeetingId()+") t\r\n" +
    11. " GROUP BY t.result";
    12. return super.executeQuery(sql, pageBean);
    13. }

    将以下方法加入到 myMeeting.js

    1. //打开查看本会议的反馈详情
    2. function openLayerFeedBack(id){
    3. $.getJSON('feedBack.action',{
    4. methodName:'queryMeetingBackByMeetingId',
    5. meetingId:id
    6. },function(data){
    7. $('#meeting_ok').html("");
    8. $('#meeting_no').html("");
    9. $('#meeting_noread').html("");
    10. if(data.success){
    11. console.log(data.data);
    12. $.each(data.data,function(i,e){
    13. if(e.result==1)
    14. $('#meeting_ok').html(e.names);
    15. else if(e.result==2)
    16. $('#meeting_no').html(e.names);
    17. else
    18. $('#meeting_noread').html(e.names);
    19. });
    20. //弹出对话框
    21. layer.open({
    22. type: 1, //layer提供了5种层类型。可传入的值有:0(信息框,默认)1(页面层)2(iframe层)3(加载层)4(tips层)
    23. title:'反馈详情',
    24. area: ['426px', '420px'], //宽高
    25. skin: 'layui-layer-rim', //样式类名
    26. content: $('#feedback'), //弹出内容。可以传入普通的html内容,还可以指定DOM,更可以随着type的不同而不同
    27. btn:['关闭'],
    28. yes:function(index,layero){
    29. layer.closeAll();
    30. }
    31. });
    32. }
    33. });
    34. }

    将原来的layer.msg("反馈详情") 

     

    效果:

     

  • 相关阅读:
    【第95题】JAVA高级技术-网络编程14(简易聊天室9:使用Socket传递音频)
    【MySQL】MySQL中对数据库及表的相关操作
    LQR 控制器
    uniapp分包 解决分多个包的问题
    【MindSpore】【图片加载】加载RGB-D图片失败
    八股文之springcloud
    为什么是LangChain?
    节省50%成本!京东云重磅发布新一代混合CDN产品
    【问题解决】Debian更新源提示InRelease已过期
    【前端甜点】某视频网站的m4s视频/音频下载方案(20240420)
  • 原文地址:https://blog.csdn.net/m0_62604616/article/details/126034502