看请假管理栏目的页面截图，很多人就放心了，因为页面还是以表格为主，所以前端代码比较简单，容易理解。其实请假管理栏目并不简单。虽说页面排版不复杂，但是归档请假单的时候，要把文件上传到腾讯云存储里面，这个过程还是挺复杂的。所以这一章跟以前其他章节有相似的地方，但是也有新技术等着我们挑战。

查询分页数据的时序图跟其他模块类似，这块业务我们非常的熟悉。

一、熟悉请假表

写后端代码之前，我们先要了解请假表的结构，然后才知道写SQL语句从这张表中提取哪些字段。tb_leave数据表的字段并不多，结构很简单。

二、编写持久层代码

在TbLeaveDao.xml文件中，定义SQL语句。因为普通用户只能看到自己的请假记录，而管理者能看到所有员工的请假记录，所以SQL语句中我设置了if条件判断。

<select id="searchLeaveByPage" parameterType="HashMap" resultType="HashMap">
    SELECT l.id,
           u.id AS userId,
           u.`name`,
           d.dept_name AS deptName,
           DATE_FORMAT(l.`start`,'%Y-%m-%d %H:%i') AS `start`,
           DATE_FORMAT(l.`end`,'%Y-%m-%d %H:%i') AS `end`,
           l.type,
           l.`status`,
           l.reason,
           l.instance_id AS instanceId,
           IF(u.id = #{myId},TRUE,FALSE) AS mine
    FROM tb_leave l
    JOIN tb_user u ON l.user_id = u.id
    LEFT JOIN tb_dept d ON u.dept_id = d.id
    WHERE 1=1
    <if test="userId != null">
        AND u.id=#{userId}
    </if>
    <if test="name != null">
        AND u.name LIKE '%${name}%'
    </if>
    <if test="deptId !=null">
        AND d.id = #{deptId}
    </if>
    <if test="date !=null">
        AND #{date} BETWEEN DATE_FORMAT(l.`start`,'%Y-%m-%d') AND DATE_FORMAT(l.`end`,'%Y-%m-%d')
    </if>
    <if test="status !=null">
        AND l.status = #{status}
    </if>
    <if test="type !=null">
        AND l.type = #{type}
    </if>
    ORDER BY l.id DESC
    LIMIT #{start}, #{length}
</select>

<select id="searchLeaveCount" parameterType="HashMap" resultType="long">
    SELECT COUNT(*)
    FROM
    tb_leave l
    JOIN tb_user u ON l.user_id = u.id
    LEFT JOIN tb_dept d ON u.dept_id = d.id
    WHERE 1=1
    <if test="userId != null">
        AND u.id=#{userId}
    </if>
    <if test="name != null">
        AND u.name LIKE '%${name}%'
    </if>
    <if test="deptId !=null">
        AND d.id = #{deptId}
    </if>
    <if test="date !=null">
        AND #{date} BETWEEN DATE_FORMAT(l.`start`,'%Y-%m-%d') AND DATE_FORMAT(l.`end`,'%Y-%m-%d')
    </if>
    <if test="status !=null">
        AND l.status = #{status}
    </if>
    <if test="type !=null">
        AND l.type = #{type}
    </if>
</select>

在TbLeaveDao.java接口中，定义DAO方法。

public interface TbLeaveDao {
    ……
    public ArrayList<HashMap> searchLeaveByPage(HashMap param);
    public long searchLeaveCount(HashMap param);
}

二、编写业务层代码

创建LeaveService.java接口，声明抽象方法。

public interface LeaveService {
    public PageUtils searchLeaveByPage(HashMap param);
}

创建LeaveServiceImpl.java类，实现抽象方法。

@Service
public class LeaveServiceImpl implements LeaveService {
    @Autowired
    private TbLeaveDao leaveDao;
    
    @Override
    public PageUtils searchLeaveByPage(HashMap param) {
        ArrayList<HashMap> list = leaveDao.searchLeaveByPage(param);
        long count = leaveDao.searchLeaveCount(param);
        int start = (Integer) param.get("start");
        int length = (Integer) param.get("length");
        PageUtils pageUtils = new PageUtils(list, count, start, length);
        return pageUtils;
    }
}

三、编写Web层代码

创建SearchLeaveByPageForm.java类，封装Ajax提交的数据。

@Data
@Schema(description = "查询请假分页表单")
public class SearchLeaveByPageForm {

    @NotNull(message = "page不能为空")
    @Min(value = 1, message = "page不能小于1")
    @Schema(description = "页数")
    private Integer page;

    @NotNull(message = "length不能为空")
    @Range(min = 10, max = 50, message = "length必须在10~50之间")
    @Schema(description = "每页记录数")
    private Integer length;

    @Pattern(regexp = "^[\\u4e00-\\u9fa5]{1,10}$", message = "name内容不正确")
    @Schema(description = "姓名")
    private String name;

    @Min(value = 1, message = "deptId不能小于1")
    @Schema(description = "部门编号")
    private String deptId;

    @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;

    @Range(min = 1, max = 2, message = "type只能是1或者2")
    @Schema(description = "类型")
    private Byte type;

    @Range(min = 1, max = 3, message = "状态只能是1~3范围")
    @Schema(description = "状态")
    private Byte status;
}

创建LeaveController.java类，定义Web方法。

@RestController
@RequestMapping("/leave")
@Tag(name = "LeaveController", description = "员工请假Web接口")
public class LeaveController {
    @Autowired
    private LeaveService leaveService;

    @PostMapping("/searchLeaveByPage")
    @Operation(summary = "查询请假分页数据")
    @SaCheckLogin
    public R searchLeaveByPage(@Valid @RequestBody SearchLeaveByPageForm form) {
        int page = form.getPage();
        int length = form.getLength();
        int start = (page - 1) * length;
        HashMap param = JSONUtil.parse(form).toBean(HashMap.class);
        param.put("start", start);
        param.put("myId", StpUtil.getLoginIdAsInt());
        if (!(StpUtil.hasPermission("LEAVE:SELECT")||StpUtil.hasPermission("ROOT"))) {
            param.put("userId", StpUtil.getLoginIdAsInt());
        }
        PageUtils pageUtils = leaveService.searchLeaveByPage(param);
        return R.ok().put("page", pageUtils);
    }
}