<?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.srs.survey.mapper.SurItineraryMapper">

    <resultMap type="SurItinerary" id="SurItineraryResult">
        <result property="returnSchoolId"    column="return_school_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="stuNo"    column="stu_no"    />
        <result property="classId"    column="class_id"    />
        <result property="name"    column="name"    />
        <result property="phone"    column="phone"    />
        <result property="emergencyContact"    column="emergency_contact"    />
        <result property="emergencyContactPhone"    column="emergency_contact_phone"    />
        <result property="schoolDistrict"    column="school_district"    />
        <result property="apartment"    column="apartment"    />
        <result property="room"    column="room"    />
        <result property="scheduledReturnTime"    column="scheduled_return_time"    />
        <result property="reachSchoolStatus"    column="reach_school_status"    />
        <result property="parentName"    column="parent_name"    />
        <result property="parentPhone"    column="parent_phone"    />
        <result property="know"    column="know"    />
        <result property="absentSchoolType"    column="absent_school_type"    />
        <result property="absentSchoolRemark"    column="absent_school_remark"    />
        <result property="attendSchoolGps"    column="attend_school_gps"    />
        <result property="attendSchoolTime"    column="attend_school_time"    />
        <result property="status"    column="status"    />
        <result property="surveyId"    column="survey_id"    />
        <result property="stuFdyName"    column="stu_fdy_name"    />
    </resultMap>

    <sql id="selectSurItineraryVo">
        select return_school_id, b.dept_id, stu_no, a.class_id, name, a.phone, emergency_contact, emergency_contact_phone, school_district, apartment, room, scheduled_return_time, reach_school_status, a.parent_name, a.parent_phone, know, absent_school_type, absent_school_remark, attend_school_gps, attend_school_time, a.status, survey_id,b.dept_name,c.class_name from sur_itinerary a left join sys_dept b on a.dept_id=b.dept_id left join srs_class c on a.class_id=c.class_id
    </sql>

    <select id="selectSurItineraryList" parameterType="SurItinerary" resultMap="SurItineraryResult">
        <include refid="selectSurItineraryVo"/>
        <where>
            <if test="deptId != null "> and a.dept_id = #{deptId}</if>
            <if test="stuNo != null  and stuNo != ''"> and stu_no = #{stuNo}</if>
            <if test="classId != null "> and a.class_id = #{classId}</if>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="phone != null  and phone != ''"> and phone = #{phone}</if>
            <if test="emergencyContact != null  and emergencyContact != ''"> and emergency_contact = #{emergencyContact}</if>
            <if test="emergencyContactPhone != null  and emergencyContactPhone != ''"> and emergency_contact_phone = #{emergencyContactPhone}</if>
            <if test="schoolDistrict != null  and schoolDistrict != ''"> and school_district = #{schoolDistrict}</if>
            <if test="apartment != null  and apartment != ''"> and apartment = #{apartment}</if>
            <if test="room != null  and room != ''"> and room = #{room}</if>
            <if test="scheduledReturnTime != null "> and scheduled_return_time = #{scheduledReturnTime}</if>
            <if test="reachSchoolStatus != null  and reachSchoolStatus != ''"> and reach_school_status = #{reachSchoolStatus}</if>
            <if test="parentName != null  and parentName != ''"> and parent_name like concat('%', #{parentName}, '%')</if>
            <if test="parentPhone != null  and parentPhone != ''"> and parent_phone = #{parentPhone}</if>
            <if test="know != null  and know != ''"> and know = #{know}</if>
            <if test="absentSchoolType != null  and absentSchoolType != ''"> and absent_school_type = #{absentSchoolType}</if>
            <if test="absentSchoolRemark != null  and absentSchoolRemark != ''"> and absent_school_remark = #{absentSchoolRemark}</if>
            <if test="attendSchoolGps != null  and attendSchoolGps != ''"> and attend_school_gps = #{attendSchoolGps}</if>
            <if test="attendSchoolTime != null "> and attend_school_time = #{attendSchoolTime}</if>
            <if test="status != null  and status != ''"> and a.status = #{status}</if>
            <if test="surveyId != null "> and survey_id = #{surveyId}</if>
        </where>
    </select>

    <select id="selectSurItineraryByReturnSchoolId" parameterType="Long" resultMap="SurItineraryResult">
        <include refid="selectSurItineraryVo"/>
        where return_school_id = #{returnSchoolId}
    </select>

    <insert id="insertSurItinerary" parameterType="SurItinerary" useGeneratedKeys="true" keyProperty="returnSchoolId">
        insert into sur_itinerary
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deptId != null">dept_id,</if>
            <if test="stuNo != null">stu_no,</if>
            <if test="classId != null">class_id,</if>
            <if test="name != null">name,</if>
            <if test="phone != null">phone,</if>
            <if test="emergencyContact != null">emergency_contact,</if>
            <if test="emergencyContactPhone != null">emergency_contact_phone,</if>
            <if test="schoolDistrict != null">school_district,</if>
            <if test="apartment != null">apartment,</if>
            <if test="room != null">room,</if>
            <if test="scheduledReturnTime != null">scheduled_return_time,</if>
            <if test="reachSchoolStatus != null">reach_school_status,</if>
            <if test="parentName != null">parent_name,</if>
            <if test="parentPhone != null">parent_phone,</if>
            <if test="know != null">know,</if>
            <if test="absentSchoolType != null">absent_school_type,</if>
            <if test="absentSchoolRemark != null">absent_school_remark,</if>
            <if test="attendSchoolGps != null">attend_school_gps,</if>
            <if test="attendSchoolTime != null">attend_school_time,</if>
            <if test="status != null">status,</if>
            <if test="surveyId != null">survey_id,</if>
            <if test="stuFdyName != null">stu_fdy_name,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deptId != null">#{deptId},</if>
            <if test="stuNo != null">#{stuNo},</if>
            <if test="classId != null">#{classId},</if>
            <if test="name != null">#{name},</if>
            <if test="phone != null">#{phone},</if>
            <if test="emergencyContact != null">#{emergencyContact},</if>
            <if test="emergencyContactPhone != null">#{emergencyContactPhone},</if>
            <if test="schoolDistrict != null">#{schoolDistrict},</if>
            <if test="apartment != null">#{apartment},</if>
            <if test="room != null">#{room},</if>
            <if test="scheduledReturnTime != null">#{scheduledReturnTime},</if>
            <if test="reachSchoolStatus != null">#{reachSchoolStatus},</if>
            <if test="parentName != null">#{parentName},</if>
            <if test="parentPhone != null">#{parentPhone},</if>
            <if test="know != null">#{know},</if>
            <if test="absentSchoolType != null">#{absentSchoolType},</if>
            <if test="absentSchoolRemark != null">#{absentSchoolRemark},</if>
            <if test="attendSchoolGps != null">#{attendSchoolGps},</if>
            <if test="attendSchoolTime != null">#{attendSchoolTime},</if>
            <if test="status != null">#{status},</if>
            <if test="surveyId != null">#{surveyId},</if>
            <if test="stuFdyName != null">#{stuFdyName},</if>
         </trim>
    </insert>

    <update id="updateSurItinerary" parameterType="SurItinerary">
        update sur_itinerary
        <trim prefix="SET" suffixOverrides=",">
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="stuNo != null">stu_no = #{stuNo},</if>
            <if test="classId != null">class_id = #{classId},</if>
            <if test="name != null">name = #{name},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="emergencyContact != null">emergency_contact = #{emergencyContact},</if>
            <if test="emergencyContactPhone != null">emergency_contact_phone = #{emergencyContactPhone},</if>
            <if test="schoolDistrict != null">school_district = #{schoolDistrict},</if>
            <if test="apartment != null">apartment = #{apartment},</if>
            <if test="room != null">room = #{room},</if>
            <if test="scheduledReturnTime != null">scheduled_return_time = #{scheduledReturnTime},</if>
            <if test="reachSchoolStatus != null">reach_school_status = #{reachSchoolStatus},</if>
            <if test="parentName != null">parent_name = #{parentName},</if>
            <if test="parentPhone != null">parent_phone = #{parentPhone},</if>
            <if test="know != null">know = #{know},</if>
            <if test="absentSchoolType != null">absent_school_type = #{absentSchoolType},</if>
            <if test="absentSchoolRemark != null">absent_school_remark = #{absentSchoolRemark},</if>
            <if test="attendSchoolGps != null">attend_school_gps = #{attendSchoolGps},</if>
            <if test="attendSchoolTime != null">attend_school_time = #{attendSchoolTime},</if>
            <if test="status != null">status = #{status},</if>
            <if test="surveyId != null">survey_id = #{surveyId},</if>
            <if test="stuFdyName != null">stu_fdy_name = #{stuFdyName},</if>
        </trim>
        where return_school_id = #{returnSchoolId}
    </update>

    <delete id="deleteSurItineraryByReturnSchoolId" parameterType="Long">
        delete from sur_itinerary where return_school_id = #{returnSchoolId}
    </delete>
    <update id="auditItinerary" parameterType="long">
        update sur_itinerary set status=2 where return_school_id = #{returnSchoolId}
    </update>
    <update id="batchAuditItinerary" parameterType="string">
        update sur_itinerary set status=2 where return_school_id in
        <foreach item="returnSchoolId" collection="array" open="(" separator="," close=")">
            #{returnSchoolId}
        </foreach>
    </update>
    <delete id="deleteSurItineraryByReturnSchoolIds" parameterType="String">
        delete from sur_itinerary where return_school_id in
        <foreach item="returnSchoolId" collection="array" open="(" separator="," close=")">
            #{returnSchoolId}
        </foreach>
    </delete>


    <select id="listView" parameterType="SurItinerary" resultType="SurItinerary">
        select d.survey_name, a.*,b.class_name,b.teacher_name,c.employee_id,f.campus_name,f.park_name,f.building_name,f.room_no
        from sur_itinerary as a
        left join view_stu_info as b on a.stu_no = b.stu_no
        left join cph_teacher as c on b.teacher_id = c.teacher_id
        left join sur_survey as d on a.survey_id = d.survey_id
        left join srs_dormitory_student as e on a.stu_no = e.stu_no
        left join view_dorm_info as f on e.dormitory_id = f.id
        <where>
            <if test="deptId != null "> and b.dept_id = #{deptId}</if>
            <if test="stuNo != null  and stuNo != ''"> and a.stu_no = #{stuNo}</if>
            <if test="classId != null "> and b.class_id = #{classId}</if>
            <if test="name != null  and name != ''"> and a.name like concat('%', #{name}, '%')</if>
            <if test="phone != null  and phone != ''"> and a.phone = #{phone}</if>
            <if test="emergencyContact != null  and emergencyContact != ''"> and a.emergency_contact = #{emergencyContact}</if>
            <if test="emergencyContactPhone != null  and emergencyContactPhone != ''"> and a.emergency_contact_phone = #{emergencyContactPhone}</if>
            <if test="scheduledReturnTime != null "> and a.scheduled_return_time = #{scheduledReturnTime}</if>
            <if test="reachSchoolStatus != null  and reachSchoolStatus != ''"> and a.reach_school_status = #{reachSchoolStatus}</if>
            <if test="parentName != null  and parentName != ''"> and a.parent_name like concat('%', #{parentName}, '%')</if>
            <if test="parentPhone != null  and parentPhone != ''"> and a.parent_phone = #{parentPhone}</if>
            <if test="know != null  and know != ''"> and a.know = #{know}</if>
            <if test="absentSchoolType != null  and absentSchoolType != ''"> and a.absent_school_type = #{absentSchoolType}</if>
            <if test="absentSchoolRemark != null  and absentSchoolRemark != ''"> and a.absent_school_remark = #{absentSchoolRemark}</if>
            <if test="attendSchoolGps != null  and attendSchoolGps != ''"> and a.attend_school_gps = #{attendSchoolGps}</if>
            <if test="attendSchoolTime != null "> and a.attend_school_time = #{attendSchoolTime}</if>
            <if test="status != null  and status != ''"> and a.status = #{status}</if>
            <if test="surveyId != null "> and a.survey_id = #{surveyId}</if>
            <if test="tNo != null "> and c.employee_id = #{tNo}</if>
            <if test="leaveStatus != null "> and a.leave_status = #{leaveStatus}</if>
            <if test="surveyName != null  and surveyName != ''"> and d.survey_name like concat('%', #{surveyName}, '%')</if>
            <if test="className != null  and className != ''"> and b.class_name like concat('%', #{className}, '%')</if>
            <if test="moreDeptName != null  and moreDeptName != ''"> and a.more_dept_name like concat('%', #{moreDeptName}, '%')</if>
            <if test="willLeaveTime != null">
                and date(a.will_leave_time) = date(#{willLeaveTime})
            </if>
        </where>
        order by a.return_school_id desc
    </select>

    <select id="listReturnRes" parameterType="Long" resultType="ReturnRes">
        select res1.*,( res1.yingfanxiao - res1.yifanxiao ) as weifanxiao,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "路上" and a.survey_id = #{surveyId} and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as lushang,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "病假" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as bingjia,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "无票" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as wupiao,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "无课请事假延长" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as wuke,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "事假" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as shijia,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "红白事" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as hongbaishi,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "考试" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as kaoshi,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "参赛" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as cansai,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "兵检或役前训练" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as bingjian,
               (
                   select count(1) from sur_itinerary as a
                                            left join view_stu_info as b on a.stu_no = b.stu_no
                   where a.absent_school_type = "退学或休学" and a.survey_id = #{surveyId}  and b.dept_name = res1.dept_name and b.grade_name = res1.grade_name
               ) as tuixue

        from
            (
                select r1.*,
                       (select count(1) from sur_itinerary as tt1
                                                 left join view_stu_info as tt2 on tt1.stu_no = tt2.stu_no
                        where tt1.`status` = "2" and tt1.reach_school_status = "是" and tt1.survey_id = #{surveyId}  and tt2.dept_name = r1.dept_name and tt2.grade_name = r1.grade_name
                       ) as yifanxiao
                from
                    (
                        select a.dept_name,a.grade_name,count(1) as stu_num,
                               (select count(1) from view_stu_info as tt1 where tt1.`status` = "05" and tt1.dept_name = a.dept_name and tt1.grade_name = a.grade_name ) as shixi,
                               (select count(1) from srs_dormitory_student as tt1
                                                         left join view_stu_info as tt2 on tt1.stu_no = tt2.stu_no
                                where tt2.`status` = "01" and tt2.dept_name = a.dept_name and tt2.grade_name = a.grade_name ) as yingfanxiao
                        from view_stu_info as a
                                 left join srs_grade as b on a.grade_id = b.grade_id
                        where b.grade_status in (1,3) and a.dept_name is not null  and a.`status` = "01"
                        GROUP BY a.dept_name,a.grade_name
                        order by a.dept_name,a.grade_name
                    ) as r1
            ) as res1
    </select>

    <select id="listLeaveRes" parameterType="Long" resultType="LeaveRes">
        select a.dept_name,count(1) as stu_num,
               (select count(1)
                from sur_itinerary as t1
                         left join view_stu_info as t2 on t1.stu_no = t2.stu_no
                where t1.is_home = "是" and t2.dept_name = a.dept_name and t1.survey_id = #{surveyId}
               ) as home_num,
               (select count(1)
                from sur_itinerary as t1
                         left join view_stu_info as t2 on t1.stu_no = t2.stu_no
                where t1.is_leave = "0" and t2.dept_name = a.dept_name and t1.survey_id = #{surveyId}
               ) as stay_num,
               (select count(1)
                from sur_itinerary as t1
                         left join view_stu_info as t2 on t1.stu_no = t2.stu_no
                where t1.`status` = "2" and t1.reach_school_status = "是" and t2.dept_name = a.dept_name and t1.survey_id = #{surveyId}
               ) as school_num
        from view_stu_info as a
                 left join srs_grade as b on a.grade_id = b.grade_id
        where b.grade_status in (1,3) and a.`status` = "01" and a.dept_name is not null
        group by a.dept_name
    </select>

    <select id="listSubmitRes" parameterType="Long" resultType="map">
        WITH base_dept AS (
            SELECT
                f1.dept_name
            FROM view_stu_info AS f1
                     LEFT JOIN srs_grade AS f2 ON f1.grade_id = f2.grade_id
            WHERE f1.status IN ('01','05','06') AND f2.grade_status IN (1,3)
            GROUP BY f1.dept_name
        ),
             stu_stat AS (
                 SELECT
                     a.dept_name,
                     COUNT(*) AS stu_num
                 FROM view_stu_info a
                          LEFT JOIN srs_grade b ON a.grade_id = b.grade_id
                 WHERE b.grade_status IN (1,3) AND a.status IN ('01','05','06')
                 GROUP BY a.dept_name
             ),
             itinerary_stat AS (
                 SELECT
                     SUBSTRING_INDEX(a.more_dept_name, '（', 1) AS dept_name,
                     a.more_dept_name,
                     COUNT(*) AS submit_num,
                     SUM(a.is_home = '是') AS home_num,
                     SUM(a.is_leave = 0) AS stay_num
                 FROM sur_itinerary a
                 WHERE a.survey_id = #{surveyId}
                 GROUP BY dept_name, a.more_dept_name
             )
        SELECT
            bd.dept_name,
            COALESCE(its.submit_num, 0) AS submit_num,
            COALESCE(its.more_dept_name, bd.dept_name) AS more_dept_name,
            COALESCE(its.home_num, 0) AS home_num,
            COALESCE(its.stay_num, 0) AS stay_num,
            COALESCE(ss.stu_num, 0) AS stu_num
        FROM base_dept bd
                 LEFT JOIN itinerary_stat its ON bd.dept_name = its.dept_name
                 LEFT JOIN stu_stat ss ON bd.dept_name = ss.dept_name;
    </select>
    <select id="getItineraryStatistics" resultType="SurItineraryStatisticsVo">
        SELECT
            more_dept_name AS moreDeptName,
            SUM(CASE WHEN is_leave = '1' AND leave_status = '2' THEN 1 ELSE 0 END) AS expectedLeaveCount,
            SUM(CASE WHEN is_leave = '0' AND leave_status = '2' THEN 1 ELSE 0 END) AS stayCount,
            SUM(CASE WHEN is_home = '是' THEN 1 ELSE 0 END) AS homeCount,
            SUM(CASE WHEN status = '2' AND reach_school_status = '是' THEN 1 ELSE 0 END) AS returnCount
        FROM sur_itinerary
        WHERE survey_id = #{surveyId}
        GROUP BY more_dept_name
    </select>


    <select id="listReturnRes1" parameterType="Long" resultType="map">
        WITH base_dept AS (
            SELECT
                f1.dept_name
            FROM view_stu_info AS f1
                     LEFT JOIN srs_grade AS f2 ON f1.grade_id = f2.grade_id
            WHERE f1.status IN ('01','05','06') AND f2.grade_status IN (1,3)
            GROUP BY f1.dept_name
        ),
             stu_stat AS (
                 SELECT
                     a.dept_name,
                     COUNT(*) AS stu_num,
                     SUM(a.status = '05') AS shixi,
                     SUM(a.status in ('01','06')) AS yingfanxiao
                 FROM view_stu_info a
                          LEFT JOIN srs_grade b ON a.grade_id = b.grade_id
                 WHERE b.grade_status IN (1,3) AND a.status IN ('01','05','06')
                 GROUP BY a.dept_name
             ),
             itinerary_stat AS (
                 SELECT
                     SUBSTRING_INDEX(a.more_dept_name, '（', 1) AS dept_name,
                     a.more_dept_name,
                     COUNT(*) AS submit_num,
                     SUM(a.status = '2' AND a.reach_school_status = '是') AS yifanxiao,
                     SUM(a.absent_school_type = '路上') AS lushang,
                     SUM(a.absent_school_type = '病假') AS bingjia,
                     SUM(a.absent_school_type = '无票') AS wupiao,
                     SUM(a.absent_school_type = '无课请事假延长') AS wuke,
                     SUM(a.absent_school_type = '事假') AS shijia,
                     SUM(a.absent_school_type = '红白事') AS hongbaishi,
                     SUM(a.absent_school_type = '考试') AS kaoshi,
                     SUM(a.absent_school_type = '参赛') AS cansai,
                     SUM(a.absent_school_type = '兵检或役前训练') AS bingjian,
                     SUM(a.absent_school_type = '退学或休学') AS tuixue
                 FROM sur_itinerary a
                 WHERE a.survey_id = #{surveyId}
                 GROUP BY dept_name, a.more_dept_name
             )
        SELECT
            bd.dept_name,
            COALESCE(its.more_dept_name, bd.dept_name) AS more_dept_name,
            COALESCE(its.submit_num, 0) AS submit_num,
            COALESCE(ss.stu_num, 0) AS stu_num,
            COALESCE(ss.shixi, 0) AS shixi,
            COALESCE(ss.yingfanxiao, 0) AS yingfanxiao,
            COALESCE(its.yifanxiao, 0) AS yifanxiao,
            COALESCE(ss.yingfanxiao - its.yifanxiao, 0) AS weifanxiao,
            COALESCE(its.lushang, 0) AS lushang,
            COALESCE(its.bingjia, 0) AS bingjia,
            COALESCE(its.wupiao, 0) AS wupiao,
            COALESCE(its.wuke, 0) AS wuke,
            COALESCE(its.shijia, 0) AS shijia,
            COALESCE(its.hongbaishi, 0) AS hongbaishi,
            COALESCE(its.kaoshi, 0) AS kaoshi,
            COALESCE(its.cansai, 0) AS cansai,
            COALESCE(its.bingjian, 0) AS bingjian,
            COALESCE(its.tuixue, 0) AS tuixue
        FROM base_dept bd
                 LEFT JOIN stu_stat ss ON bd.dept_name = ss.dept_name
                 LEFT JOIN itinerary_stat its ON bd.dept_name = its.dept_name;
    </select>

    <select id="countWillLeave" parameterType="Long" resultType="map">
        CALL test_survey_dept_leave_statistics(#{surveyId});
    </select>

    <select id="countFdyClassWillLeave" statementType="CALLABLE" resultType="map">
        CALL test_survey_class_leave_statistics(#{surveyId}, #{fdyNo});
    </select>

</mapper>
