线下会议页面的日程表我们已经做完了，能创建会议申请，也能在甘特图上显示会议。线下会议页面的周日历界面还没有做，咱们就把周日历功能的后端代码给写一下。
如果按照会议室名字查找，就会进入到周日历界面，默认显示成某个会议室在当前这周的会议安排。
用鼠标点击周日历上面的会议卡片，会出现弹窗，里面展示的是这个会议的详情信息。
如果我们按照日期和会议室名字查询，周日历界面显示成从某一天开始，为期7天的周日历。
这节课我们干脆把查询周日历和查询会议详情的两个业务流程的后端代码给写出来，下节课去写前端代码。
一、编写持久层代码
我们在TbMeetingDao.xml文件中定义三个SQL语句，分别查询周日历和会议详情。searchMeetingInfo和searchCurrentMeetingInfo两个SQL语句的区别在于是否能查询出来实际的出勤和缺勤人员。
<select id="searchOfflineMeetingInWeek" parameterType="HashMap" resultType="HashMap">
SELECT id,
title,
DATE_FORMAT(`date`,"%m/%d") AS `date`,
DATE_FORMAT(start,'%H:%i') AS `start`,
ROUND(timestampdiff(MINUTE,CONCAT(`date`," ",`start`),CONCAT(`date`," ",`end`))/30) AS time,
uuid,
instance_id AS instanceId,
IF(creator_id=#{userId},"true","false") AS isCreator,
`status`
FROM tb_meeting
WHERE type = 2
AND place = #{place}
AND `status` != 2
AND date BETWEEN #{startDate} AND #{endDate}
<if test="mold=='我的会议'">
AND JSON_CONTAINS(members, CAST(#{userId} AS CHAR))
</if>
</select>
<select id="searchMeetingInfo" parameterType="long" resultType="HashMap">
SELECT m.title,
m.date,
m.place,
DATE_FORMAT(m.`start`,'%H:%i') AS `start`,
DATE_FORMAT(m.`end`,'%H:%i') AS `end`,
m.status,
CONCAT( "[", GROUP_CONCAT( JSON_OBJECT ( "name", u.name, "photo", u.photo ) SEPARATOR "," ), "]" ) AS members
FROM tb_meeting m
LEFT JOIN tb_user u ON JSON_CONTAINS ( m.members, CONVERT ( u.id, CHAR ) )
WHERE m.id = #{id} AND u.status=1
</select>
<select id="searchCurrentMeetingInfo" parameterType="long" resultType="HashMap">
SELECT m.title,
m.date,
m.place,
DATE_FORMAT(m.`start`,'%H:%i') AS `start`,
DATE_FORMAT(m.`end`,'%H:%i') AS `end`,
m.status,
(
SELECT CONCAT( "[", GROUP_CONCAT( JSON_OBJECT ( "name", u.name, "photo", u.photo ) SEPARATOR "," ), "]" )
FROM tb_user u WHERE JSON_CONTAINS ( m.present, CONVERT ( u.id, CHAR ) )
) AS present,
(
SELECT CONCAT( "[", GROUP_CONCAT( JSON_OBJECT ( "name", u.name, "photo", u.photo ) SEPARATOR "," ), "]" )
FROM tb_user u WHERE JSON_CONTAINS ( m.unpresent, CONVERT ( u.id, CHAR ) )
) AS unpresent
FROM tb_meeting m
WHERE m.id = #{id}
</select>
在TbMeetingDao.java接口中，声明DAO方法。
public interface TbMeetingDao {
……
public ArrayList<HashMap> searchOfflineMeetingInWeek(HashMap param);
public HashMap searchMeetingInfo(long id);
public HashMap searchCurrentMeetingInfo(long id);
}
GROUP_CONCAT()函数有默认长度限制，超出长度限制的内容会被自动截断。因此我们要修改MySQL的配置文件（my.ini或者my.cnf），在里面加上一个参数，并且重启MySQL数据库。
[mysqld]
……
group_concat_max_len=102400
二、编写业务层代码
在MeetingService.java接口中，声明抽象方法。
public interface MeetingService {
……
public ArrayList<HashMap> searchOfflineMeetingInWeek(HashMap param);
public HashMap searchMeetingInfo(short status, long id);
}
在MeetingServiceImpl.java类中，实现抽象方法。
public class MeetingServiceImpl implements MeetingService {
……
@Override
public ArrayList<HashMap> searchOfflineMeetingInWeek(HashMap param) {
ArrayList<HashMap> list = meetingDao.searchOfflineMeetingInWeek(param);
return list;
}
@Override
public HashMap searchMeetingInfo(short status, long id) {
//判断正在进行中的会议
HashMap map;
//此处代码升级过，正在进行和已经结束的会议都可以查询present和unpresent字段
if (status == 4||status==5) {
map = meetingDao.searchCurrentMeetingInfo(id);
} else {
map = meetingDao.searchMeetingInfo(id);
}
return map;
}
}
三、编写Web层代码
创建SearchOfflineMeetingInWeekForm.java和SearchMeetingInfoForm.java这两个类，用于封装Ajax提交的数据。
@Data
@Schema(description = "查询某个会议室一周会议表单")
public class SearchOfflineMeetingInWeekForm {
@Pattern(regexp = "^((((1[6-9]|[2-9]\\d)\\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\\d|3[01]))|(((1[6-9]|[2-9]\\d)\\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\\d|30))|(((1[6-9]|[2-9]\\d)\\d{2})-0?2-(0?[1-9]|1\\d|2[0-8]))|(((1[6-9]|[2-9]\\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$", message = "date内容不正确")
@Schema(description = "日期")
private String date;
@NotBlank(message = "mold不能空")
@Pattern(regexp = "^全部会议$|^我的会议$", message = "mold内容不正确")
@Schema(description = "模式")
private String mold;
@NotBlank(message = "name不能为空")
@Pattern(regexp = "^[a-zA-Z0-9\\u4e00-\\u9fa5]{2,20}$", message = "name内容不正确")
@Schema(description = "会议室名称")
private String name;
}
@Data
@Schema(description = "查询会议信息表单")
public class SearchMeetingInfoForm {
@NotNull(message = "id不能为空")
@Min(value = 1,message = "id不能小于1")
@Schema(description = "会议ID")
private Long id;
@NotNull(message = "status不能为空")
@Range(min = 1, max = 5,message = "status必须在1~5之间")
@Schema(description = "状态")
private Short status;
}
在MeetingController.java类中，定义两个Web方法。
public class MeetingController {
……
@PostMapping("/searchOfflineMeetingInWeek")
@Operation(summary = "查询某个会议室的一周会议")
@SaCheckLogin
public R searchOfflineMeetingInWeek(@Valid @RequestBody SearchOfflineMeetingInWeekForm form) {
String date = form.getDate();
DateTime startDate, endDate;
if (date != null && date.length() > 0) {
//从date开始，生成七天日期
startDate = DateUtil.parseDate(date);
endDate = startDate.offsetNew(DateField.DAY_OF_WEEK, 6);
} else {
//查询当前日期，生成本周的日期
startDate = DateUtil.beginOfWeek(new Date());
endDate = DateUtil.endOfWeek(new Date());
}
HashMap param = new HashMap() {{
put("place", form.getName());
put("startDate", startDate.toDateStr());
put("endDate", endDate.toDateStr());
put("mold", form.getMold());
put("userId", StpUtil.getLoginIdAsLong());
}};
ArrayList list = meetingService.searchOfflineMeetingInWeek(param);
//生成周日历水平表头的文字标题
DateRange range = DateUtil.range(startDate, endDate, DateField.DAY_OF_WEEK);
ArrayList days = new ArrayList();
range.forEach(one -> {
JSONObject json = new JSONObject();
json.set("date", one.toString("MM/dd"));
json.set("day", one.dayOfWeekEnum().toChinese("周"));
days.add(json);
});
return R.ok().put("list", list).put("days", days);
}
@PostMapping("/searchMeetingInfo")
@Operation(summary = "查询会议信息")
@SaCheckLogin
public R searchMeetingInfo(@Valid @RequestBody SearchMeetingInfoForm form) {
HashMap map = meetingService.searchMeetingInfo(form.getStatus(), form.getId());
return R.ok(map);
}
}