OA项目之左侧菜单&动态选项卡（二）

package com.zking.oa.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.lisen.mvc.util.DbTemplate;
import org.lisen.mvc.util.PageBean;
import com.mysql.cj.util.StringUtils;
import com.zking.oa.model.MeetingMember;
import com.zking.oa.model.Meetinginfo;
import com.zking.oa.model.User;
import com.zking.oa.util.BaseDao;
import com.zking.oa.util.CommonUtils;
@SuppressWarnings("unchecked")
public class MeetinginfoDao extends BaseDao implements IMeetinginfoDao {
  @Override
  public List<MeetingMember> listMeetingMember(){
    String sql="select name,id from t_oa_user";
    return DbTemplate.query(sql, MeetingMember.class);
  }
  @Override
  public void addMeetinginfo(Meetinginfo meetinginfo) {
    DbTemplate.save(meetinginfo);
  }
  /**
   * 封装sql：作为我的会议、我的审批、会议通知、代开会议、
   * 历史会议级所有会议的基础SQL语句
   * @return
   */
  private String getSql() {
    return "SELECT \r\n" + 
        "m.id,m.title,m.content,m.canyuze,m.zhuchiren,m.liexize,u.name\r\n" + 
        ",m.location,\r\n" + 
        "DATE_FORMAT(m.startTime,'%Y-%m-%d %H:%i:%s') as startTime,\r\n" + 
        "DATE_FORMAT(m.endTime,'%Y-%m-%d %H:%i:%s') as endTime,\r\n" + 
        "(case m.state\r\n" + 
        " when 0 then '取消会议'\r\n" + 
        " when 1 then '新建'\r\n" + 
        " when 2 then '待审核'\r\n" + 
        " when 3 then '驳回'\r\n" + 
        " when 4 then '待开'\r\n" + 
        " when 5 then '进行中'\r\n" + 
        " when 6 then '开启投票'\r\n" + 
        " ELSE '结束会议' end\r\n" + 
        ") as meetingState,m.state,m.seatPic,m.remark,m.auditor,\r\n" + 
        "u2.name as auditName\r\n" + 
        "from \r\n" + 
        "t_oa_meeting_info m inner join t_oa_user u\r\n" + 
        "on m.zhuchiren=u.id\r\n" + 
        "LEFT JOIN t_oa_user u2\r\n" + 
        "on u2.id=m.auditor where 1=1 ";
  }
  @Override
  public List<Map<String,Object>> listMeetingInfo(Meetinginfo meetingInfo, PageBean pageBean) {
    String sql=this.getSql();
    //按照会议标题模糊查询
    if(!StringUtils.isNullOrEmpty(meetingInfo.getTitle())) 
      sql+=" and title like '%"+meetingInfo.getTitle()+"%'";
    //按照 当前登录ID作为主持人字段条件
    sql+=" and zhuchiren="+meetingInfo.getZhuchiren();
    //sql +=" and state="+meetingInfo.getState();
    //按照会议ID降序排序
    sql+=" order by m.id desc";
    return super.executeQuery(sql, pageBean, new convert<Map<String, Object>>() {
      @Override
      public List<Map<String, Object>> forEach(ResultSet rs) throws SQLException, Exception {
        return CommonUtils.toList(rs);
      }
    });
  }
  @Override
  public void updateMeetingState(Meetinginfo meetingInfo) {
    String sql = "update t_oa_meeting_info set state = ? where id = ?";
    DbTemplate.update(sql, new Object[] {meetingInfo.getState(), meetingInfo.getId()});
  }
  @Override
public List<Meetinginfo> listRelatedMeetingInfoByUserId(Integer uid, String title,Integer state,PageBean pageBean) {
    String sql = "SELECT t1.id,t1.auditor,t1.canyuze,t1.liexize,t1.content,DATE_FORMAT(t1.endTime,'%Y-%m-%d %H:%i:%s') as endTime,t1.fujian,\r\n" + 
        "       t1.location,t1.seatPic,DATE_FORMAT(t1.startTime,'%Y-%m-%d %H:%i:%s') as startTime,"
        + "(case t1.state "
        + " when 0 then '取消会议' "
        + " when 1 then '新建' "
        + " when 2 then '待审核'"
        + " when 3 then '驳回'"
        + " when 4 then '待开'"
        + " when 5 then '进行中' "
        + " when 6 then '开启投票' "
        + " ELSE '结束会议' end) as meetingState,"
        + "t1.title,t1.zhuchiren\r\n" + 
        "   FROM t_oa_meeting_info t1 INNER JOIN \r\n" + 
        " (SELECT a.id,\r\n" + 
        "         SUBSTRING_INDEX(SUBSTRING_INDEX( a.canyuze, ',', b.help_topic_id + 1 ), ',',- 1) AS userid \r\n" + 
        "    FROM t_oa_meeting_info AS a\r\n" + 
        "   JOIN mysql.help_topic AS b ON b.help_topic_id < ( LENGTH( a.canyuze ) - LENGTH( REPLACE ( a.canyuze, ',', '' ) ) + 1 )\r\n" + 
        "          UNION ALL\r\n" + 
        "   SELECT a.id,\r\n" + 
        "    SUBSTRING_INDEX(SUBSTRING_INDEX( a.liexize, ',', b.help_topic_id + 1 ), ',',- 1) AS userid \r\n" + 
        "      FROM t_oa_meeting_info AS a\r\n" + 
        "     JOIN mysql.help_topic AS b ON b.help_topic_id < ( LENGTH( a.liexize ) - LENGTH( REPLACE ( a.liexize, ',', '' ) ) + 1 )\r\n" + 
        "         ) tmp ON t1.id = tmp.id\r\n" + 
        "WHERE tmp.userid = "+uid+" and t1.state ="+state;
    if(title!=null&&!"".equals(title)) {
      sql+=" and  t1.title like '"+title+"%'";
    }
    return super.executeQuery(sql, pageBean, new convert<Map<String, Object>>() {
      @Override
      public List<Map<String, Object>> forEach(ResultSet rs) throws SQLException, Exception {
        return CommonUtils.toList(rs);
      }
    });
  }
  @Override
  public List<Meetinginfo> listRelatedMeetingInfoByUserId2(Integer uid, String title,Integer state,PageBean pageBean) {
    String sql = "SELECT t1.id,t1.auditor,t1.canyuze,t1.liexize,t1.content,DATE_FORMAT(t1.endTime,'%Y-%m-%d %H:%i:%s') as endTime,t1.fujian,\r\n" + 
        "       t1.location,t1.seatPic,DATE_FORMAT(t1.startTime,'%Y-%m-%d %H:%i:%s') as startTime,(case t1.state  when 0 then '取消会议'  when 1 then '新建'  when 2 then '待审核' when 3 then '驳回' when 4 then '待开' when 5 then '进行中'  when 6 then '开启投票'  ELSE '结束会议' end) as meetingState,t1.title,t1.zhuchiren\r\n" + 
        "   FROM t_oa_meeting_info t1\r\n" + 
        "WHERE t1.state=7 and FIND_IN_SET("+uid+",concat(t1.zhuchiren,',',t1.canyuze,',',t1.liexize))";
    if(title!=null&&!"".equals(title)) {
      sql+=" and  t1.title like '"+title+"%'";
    }
    return super.executeQuery(sql, pageBean, new convert<Map<String, Object>>() {
      @Override
      public List<Map<String, Object>> forEach(ResultSet rs) throws SQLException, Exception {
        return CommonUtils.toList(rs);
      }
    });
  }
  @Override
  public List<User> listMeetingMembersById(Integer id) {
    String sql = "SELECT id,name,loginName,pwd,rid "
        + "FROM t_oa_user id "
        + "WHERE FIND_IN_SET(id, (SELECT CONCAT(t.canyuze,',',t.liexize,',',t.zhuchiren) FROM t_oa_meeting_info t WHERE t.id = ?))";
    return DbTemplate.query(sql, new Object[] {id}, User.class);
  }
  @Override
  public void updateSeatPicById(Meetinginfo meetingInfo) {
    String sql = "UPDATE t_oa_meeting_info SET seatPic = ? WHERE id = ? ";
    DbTemplate.update(sql, new Object[] {meetingInfo.getSeatPic(), meetingInfo.getId()});
  }
  @Override
  public void sendAudit(Meetinginfo meetingInfo) {
    String sql = "update t_oa_meeting_info set state = 2, auditor=? where id = ?";
    DbTemplate.update(sql, new Object[] {meetingInfo.getAuditor(), meetingInfo.getId()});
  }
  public static void main(String[] args) {
    MeetinginfoDao md = new MeetinginfoDao();
    Meetinginfo info = new Meetinginfo();
//    info.setZhuchiren("1");
//    List<Map<String,Object>> listMeetingInfo = md.listMeetingInfo(info, new PageBean());
//    System.out.println(listMeetingInfo);
    //List<Meetinginfo> listRelatedMeetingInfoByUserId = md.listRelatedMeetingInfoByUserId(1, 4, new PageBean());
    //System.out.println(listRelatedMeetingInfoByUserId);
}
}

package com.zking.oa.action;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;
import org.lisen.mvc.framework.AbstractDispatchAction;
import org.lisen.mvc.framework.ModelDrive;
import org.lisen.mvc.util.PageBean;
import com.zking.oa.dao.MeetinginfoDao;
import com.zking.oa.model.MeetingMember;
import com.zking.oa.model.Meetinginfo;
import com.zking.oa.model.User;
import com.zking.oa.service.IMeetinginfoService;
import com.zking.oa.service.MeetinginfoService;
import com.zking.oa.util.Base64ImageUtils;
import com.zking.oa.util.CommonUtil;
public class MeetinginfoAction extends AbstractDispatchAction implements ModelDrive{
  private Meetinginfo meetinginfo = new Meetinginfo();
  private IMeetinginfoService service = new MeetinginfoService();
  @Override
  public Object getModel() {
    //日期转换器
    DateConverter converter = new DateConverter();
    converter.setPattern("yyyy-MM-dd HH:mm:ss");
    ConvertUtils.register(converter, Date.class);
    return meetinginfo;
  }
  public void listMeetingMember(HttpServletRequest req,HttpServletResponse resp) {
    try {
      List<MeetingMember> list = service.listMeetingMember();
      CommonUtil.sendResponse(0, "成功", list,resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "获取会议参与人员失败", resp);
    }
  }
  public void addMeetinginfo(HttpServletRequest req,HttpServletResponse resp) {
    try {
       service.addMeetinginfo(meetinginfo);
      CommonUtil.sendResponse(0, "会议发布成功", resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "会议发布失败", resp);
    }
  }
  /**
   * 获取以当前用户为主持人的会议信息，即我的会议功能
   * @param req
   * @param resp
   */
  public void listMeetingInfo(HttpServletRequest req, HttpServletResponse resp) {
    try {
      PageBean pageBean = new PageBean();
      pageBean.setRequest(req);
      //从session中获取当前用户放入条件
      User user = (User)req.getSession().getAttribute("user");
      meetinginfo.setZhuchiren(user.getId().toString());
      List<Map<String,Object>> list = service.listMeetingInfo(meetinginfo, pageBean);
      CommonUtil.sendResponse(0, "会议信息查询成功", pageBean.getTotal(), list, resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "会议信息查询失败",resp);
    }
  }
  /**
   * 查询与当前登录用户相关的状态为待开的会议信息
   * @return
   */
  public void listRelatedMeetingInfoByUserId(HttpServletRequest req, HttpServletResponse resp) {
    MeetinginfoDao ma=new MeetinginfoDao();
    try {
      PageBean pageBean = new PageBean();
      pageBean.setRequest(req);
      //从session中获取当前用户，并且只查询待审记录
      User user = (User)req.getSession().getAttribute("user");
      List<Meetinginfo> list = ma.listRelatedMeetingInfoByUserId(user.getId(),meetinginfo.getTitle(),4, pageBean);
      CommonUtil.sendResponse(0, "会议信息查询成功", pageBean.getTotal(), list, resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "会议信息查询失败",resp);
    }
  }
  /**
   * 历史会议
   * @return
   */
  public void listRelatedMeetingInfoNotify(HttpServletRequest req, HttpServletResponse resp) {
    MeetinginfoDao ma=new MeetinginfoDao();
    try {
      PageBean pageBean = new PageBean();
      pageBean.setRequest(req);
      //从session中获取当前用户，并且只查询待审记录
      User user = (User)req.getSession().getAttribute("user");
      List<Meetinginfo> list = ma.listRelatedMeetingInfoByUserId2(user.getId(),meetinginfo.getTitle(),7, pageBean);
      CommonUtil.sendResponse(0, "历史会议查询成功", pageBean.getTotal(), list, resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "历史会议查询失败",resp);
    }
  }
  /**
   * 通过会议的Id获取与本次会议相关的参与人员信息
   * @param req
   * @param resp
   */
  public void listMeetingMemberById(HttpServletRequest req, HttpServletResponse resp) {
    try {
      PageBean pageBean = new PageBean();
      pageBean.setRequest(req);
      List<User> list = service.listMeetingMembersById(meetinginfo.getId().intValue());
      CommonUtil.sendResponse(0, "会议信息查询成功", pageBean.getTotal(), list, resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "会议信息查询失败",resp);
    }
  }
  /**
   * 保存排座图片到服务器的指定目录，并更新相关会议信息的排座图片字段
   * @param req
   * @param resp
   */
  public void addArrangeSeat(HttpServletRequest req, HttpServletResponse resp) {
    try {
      String fName = UUID.randomUUID().toString().replace("-", "").concat(".jpg");
      String path = "D:\\temp\\images\\seatPic\\uploads\\" + fName;
      String base64Image = meetinginfo.getSeatPic().replace("data:image/png;base64,", "");
      //生成图片
      Base64ImageUtils.GenerateImage(base64Image, path);
      //更新会议信息表中的排座信息，需要设置tomcat的设置增加一个扩展web模块
      meetinginfo.setSeatPic("/uploads/"+fName);
      service.updateSeatPicById(meetinginfo);
      CommonUtil.sendResponse(0, "排座成功", resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "排座失败", resp);
    }
  }
  /**
   * 我的审核，会议的审核人员是当前登录的用户，且会议的状态为待审
   * @param req
   * @param resp
   */
  public void listMyAuditMeetingInfo(HttpServletRequest req, HttpServletResponse resp) {
    try {
      PageBean pageBean = new PageBean();
      pageBean.setRequest(req);
      //从session中获取当前用户，并且只查询待审记录
      User user = (User)req.getSession().getAttribute("user");
      meetinginfo.setAuditor(user.getId().toString());
      meetinginfo.setState(2);
      List<Map<String,Object>> list = service.listMeetingInfo(meetinginfo, pageBean);
      CommonUtil.sendResponse(0, "会议信息查询成功", pageBean.getTotal(), list, resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "会议信息查询失败",resp);
    }
  }
  /**
   * 会议送审，能够送审的前提是该会议已经排座，否则不能送审，此外
   * 对于审核通过，结束，取消，进行中等状态的会议不能执行送审
   * @param rep
   * @param resp
   */
  public void sendAudit(HttpServletRequest rep, HttpServletResponse resp) {
    try {
      service.sendAudit(meetinginfo);
      CommonUtil.sendResponse(0, "送审成功", resp);
    } catch (Exception e) {
      e.printStackTrace();
      CommonUtil.sendResponse(-1, "送审失败", resp);
    }
  }
}