<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xjrsoft.module.student.mapper.StudentLeaveMapper">
    <select id="getLeaveCount" resultType="java.lang.Long">
        SELECT COUNT(*) as count FROM student_leave t1
        INNER JOIN xjr_user t2 ON t1.student_user_id = t2.id
        INNER JOIN base_student_school_roll t3 ON t3.user_id = t2.id
        WHERE t1.status = 1 AND t2.delete_mark = 0
        AND (
                (start_date BETWEEN DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
                OR (end_date BETWEEN DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
                OR (start_date > DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d') > end_date)
                OR (DATE_FORMAT(#{startTime}, '%Y-%m-%d') > start_date and end_date > DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
        )
        <if test="dto.gradeId != null">
            and t3.grade_id = #{dto.gradeId}
        </if>
        <if test="dto.classId != null">
            and t3.class_id = #{dto.classId}
        </if>
    </select>
    <select id="getClassLeaveCount" resultType="com.xjrsoft.module.outint.vo.IdCountVo">
        SELECT t1.class_id as id, COUNT(*) as count FROM student_leave t1
        INNER JOIN xjr_user t2 ON t1.student_user_id = t2.id
        WHERE t1.status = 1 AND t2.delete_mark = 0
        AND (
        (start_date BETWEEN DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
        OR (end_date BETWEEN DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
        OR (start_date > DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d') > end_date)
        OR (DATE_FORMAT(#{startTime}, '%Y-%m-%d') > start_date and end_date > DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
        ) group by t1.class_id
    </select>
    <select id="getLeaveList" resultType="com.xjrsoft.module.student.entity.StudentLeave">
        SELECT t1.* FROM student_leave t1
        INNER JOIN xjr_user t2 ON t1.student_user_id = t2.id
        WHERE t1.status = 1 AND t2.delete_mark = 0
        AND (
                (start_date BETWEEN DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
                OR (end_date BETWEEN DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
                OR (start_date > DATE_FORMAT(#{startTime}, '%Y-%m-%d') and DATE_FORMAT(#{endTime}, '%Y-%m-%d') > end_date)
                OR (DATE_FORMAT(#{startTime}, '%Y-%m-%d') > start_date and end_date > DATE_FORMAT(#{endTime}, '%Y-%m-%d'))
        )
    </select>
</mapper>