会议反馈功能实现

一、会议通知查询SQL

分析数据：

需要查询陈冬丽（ID=3）参与、列席或主持会议时的所有记录

使用表：t_oa_meeting_info（会议信息）、t_oa_meeting_feedback（会议反馈）

查询条件：FIND_IN_SET(3, CONCAT(canyuze,',',liexize,',',zhuchiren))，表示陈冬丽在参与者、列席或主持人中

会议状态：state = 4（1为未读，2为已读参加，4为已读不参加）

SQL实现：

SELECT IFNULL(f.result,-1) result, t1.* FROM   (SELECT * FROM t_oa_meeting_info    WHERE FIND_IN_SET(3, CONCAT(canyuze,',',liexize,',',zhuchiren))    AND state = 4) t1LEFT JOIN t_oa_meeting_feedback f ON t1.id = f.meetingId AND f.personId = 3ORDER BY result

二、会议反馈详情SQL

分析数据：

查询某会议（ID=12）所有参与人员的姓名

使用表：t_oa_user（用户）、t_oa_meeting_info、t_oa_meeting_feedback

查询条件：meetingId = 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(...)))

获取反馈详情：

SELECT t1.name, IFNULL(f.result,-1) result FROM   (SELECT * FROM t_oa_user WHERE ...) t1LEFT JOIN t_oa_meeting_feedback f ON t1.id = f.personId AND f.meetingId = 12

分组连接姓名：

SELECT t.result, GROUP_CONCAT(t.name) names FROM   (SELECT t1.name, IFNULL(f.result,-1) result FROM ...) tGROUP BY t.result

三、数据库实现

实体类：`MeetingFeedBack`

package com.zking.entity;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;    // setter方法省略}

DAO实现

package com.zking.dao;public class MeetingFeedBackDao extends BaseDao {    public List
       
        > queryMeetingFeedBackByUserId(MeetingFeedBack back, PageBean pageBean) throws Exception {        String sql = "SELECT IFNULL(f.result,-1) result, t1.*                      FROM (SELECT * FROM t_oa_meeting_info                           WHERE FIND_IN_SET(" + back.getPersonId() + ", CONCAT(canyuze,',',liexize,',',zhuchiren))                           AND state = 4) t1                     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);    }    public int add(MeetingFeedBack back) throws Exception {        back.setId(UUID.randomUUID().toString().replaceAll("-", ""));        String sql = "INSERT INTO t_oa_meeting_feedback VALUES (?, ?, ?, ?, ?, ?)";        return super.executeUpdate(sql, back, new String[]{"id", "meetingId", "personType", "personId", "result", "reason"});    }}

后台代码

package com.zking.web;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) throws Exception {        PageBean pageBean = new PageBean();        pageBean.setRequest(req);        List
       
        > lst = backDao.queryMeetingFeedBackByUserId(back, pageBean);        ResponseUtil.writeJson(resp, R.ok(0, "会议通知查询成功", pageBean.getTotal(), lst));        return null;    }    public String add(HttpServletRequest req, HttpServletResponse resp) throws Exception {        try {            int rs = backDao.add(back);            if (rs > 0) {                ResponseUtil.writeJson(resp, R.ok(200, "会议反馈成功"));            } else {                ResponseUtil.writeJson(resp, R.error(0, "会议反馈失败"));            }        } catch (Exception e) {            e.printStackTrace();            try {                ResponseUtil.writeJson(resp, R.error(0, "会议信息新增失败"));            } catch (Exception e1) {                e1.printStackTrace();            }        }        return null;    }}