<?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.diagnostic.mapper.DiaReportStandardsMapper">

    <resultMap type="DiaReportStandards" id="DiaReportStandardsResult">
        <result property="standardId"    column="standardId"    />
        <result property="relId"    column="relId"    />
        <result property="itemId"    column="itemId"    />
        <result property="level"    column="level"    />
        <result property="userName"    column="user_name"    />
        <result property="standardValue"    column="standard_value"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <sql id="selectDiaReportStandardsVo">
        select standardId, relId, itemId, level, user_name, standard_value, create_by, create_time, update_by, update_time from dia_report_standards
    </sql>

    <select id="selectDiaReportStandardsList" parameterType="DiaReportStandards" resultMap="DiaReportStandardsResult">
        <include refid="selectDiaReportStandardsVo"/>
        <where>
            <if test="relId != null "> and relId = #{relId}</if>
            <if test="itemId != null "> and itemId = #{itemId}</if>
            <if test="level != null "> and level = #{level}</if>
            <if test="userName != null "> and user_name like concat('%', #{userName}, '%')</if>
            <if test="standardValue != null  and standardValue != ''"> and standard_value = #{standardValue}</if>
        </where>
    </select>

    <select id="selectDiaReportStandardsByStandardId" parameterType="Long" resultMap="DiaReportStandardsResult">
        <include refid="selectDiaReportStandardsVo"/>
        where standardId = #{standardId}
    </select>

    <insert id="insertDiaReportStandards" parameterType="DiaReportStandards" useGeneratedKeys="true" keyProperty="standardId">
        insert into dia_report_standards
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="relId != null">relId,</if>
            <if test="itemId != null">itemId,</if>
            <if test="level != null">level,</if>
            <if test="userName != null">user_name,</if>
            <if test="standardValue != null">standard_value,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="relId != null">#{relId},</if>
            <if test="itemId != null">#{itemId},</if>
            <if test="level != null">#{level},</if>
            <if test="userName != null">#{userName},</if>
            <if test="standardValue != null">#{standardValue},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
         </trim>
    </insert>

    <update id="updateDiaReportStandards" parameterType="DiaReportStandards">
        update dia_report_standards
        <trim prefix="SET" suffixOverrides=",">
            <if test="relId != null">relId = #{relId},</if>
            <if test="itemId != null">itemId = #{itemId},</if>
            <if test="level != null">level = #{level},</if>
            <if test="userName != null">user_name = #{userName},</if>
            <if test="standardValue != null">standard_value = #{standardValue},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where standardId = #{standardId}
    </update>

    <delete id="deleteDiaReportStandardsByStandardId" parameterType="Long">
        delete from dia_report_standards where standardId = #{standardId}
    </delete>

    <delete id="deleteDiaReportStandardsByStandardIds" parameterType="String">
        delete from dia_report_standards where standardId in
        <foreach item="standardId" collection="array" open="(" separator="," close=")">
            #{standardId}
        </foreach>
    </delete>

    <select id="getItemByReportId" parameterType="Long" resultType="DiaReportItems">
        WITH RECURSIVE cte AS (
            SELECT * FROM dia_reports WHERE reportId = #{reportId}
            UNION ALL
            SELECT dr.*
            FROM dia_reports dr
                     INNER JOIN cte ON dr.parentId = cte.reportId
        )
        select * from
            dia_report_items as a
                left join cte on a.reportId = cte.reportId
        where a.reportId in (
            SELECT cte.reportId FROM cte
        )
    </select>

    <select id="selectStandardDept" parameterType="DiaReportStandards" resultType="DiaReportStandards">
        select a.*,b.dept_id
        from dia_report_standards as a
        left join sys_user as b on a.user_name COLLATE utf8mb4_general_ci = b.user_name COLLATE utf8mb4_general_ci
        <where>
            <if test="relId != null "> and a.relId = #{relId}</if>
            <if test="itemId != null "> and a.itemId = #{itemId}</if>
            <if test="level != null "> and a.level = #{level}</if>
            <if test="deptId != null "> and b.dept_id = #{deptId}</if>
        </where>
        limit 1
    </select>

    <select id="selectStandardDeptBatch" resultType="DiaReportStandards">
        select a.*,b.dept_id
        from dia_report_standards as a
        left join sys_user as b
        on a.user_name COLLATE utf8mb4_general_ci = b.user_name COLLATE utf8mb4_general_ci
        <where>
            <if test="relId != null"> and a.relId = #{relId} </if>
            <if test="level != null"> and a.level = #{level} </if>
            <if test="deptId != null"> and b.dept_id = #{deptId} </if>
            <if test="itemIds != null and itemIds.size() > 0">
                and a.itemId in
                <foreach collection="itemIds" item="itemId" open="(" separator="," close=")">
                    #{itemId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="countFinishNumByRelId" resultType="Map" parameterType="DiaReportStandards">
        SELECT
            COUNT(1) AS stu_num,
            SUM(is_finish) AS finish_num
        FROM (
                 SELECT
                     a.stu_no,
                     a.stu_name,
                     CASE
                         WHEN COUNT(dri.itemId) = SUM(CASE WHEN drsc.completed = 1 THEN 1 ELSE 0 END)
                             THEN 1 ELSE 0
                         END AS is_finish
                 FROM view_stu_info a
                          JOIN dia_report_items dri
                               ON dri.reportId = (SELECT reportId FROM dia_report_release WHERE relId =  #{relId})
                          LEFT JOIN dia_report_student_completion drsc
                                    ON drsc.itemId = dri.itemId
                                        AND drsc.relId = #{relId}
                                        AND drsc.stu_no = a.stu_no
        <where>
            a.`status` NOT IN ('02','03')
            <if test="deptId != null"> and a.dept_id = #{deptId} </if>
            <if test="majorId != null"> and a.major_id = #{majorId} </if>
            <if test="classId != null"> and a.class_id = #{classId} </if>
            <if test="gradeId != null"> and a.grade_id = #{gradeId} </if>
            <if test="fdyNo != null"> and a.t_no = #{fdyNo} </if>
        </where>
                 GROUP BY a.stu_no, a.stu_name
             ) t
    </select>

    <select id="countItemFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
            (stu_num * item_num) AS total_num,
            finish_num
        FROM
            (SELECT COUNT(1) AS stu_num FROM view_stu_info
        <where>
            `status` NOT IN ('02','03')
            <if test="deptId != null"> and dept_id = #{deptId} </if>
            <if test="majorId != null"> and major_id = #{majorId} </if>
            <if test="classId != null"> and class_id = #{classId} </if>
            <if test="gradeId != null"> and grade_id = #{gradeId} </if>
            <if test="fdyNo != null"> and t_no = #{fdyNo} </if>
        </where>
        ) t1
                CROSS JOIN (SELECT COUNT(1) AS item_num FROM dia_report_items WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId})) t2
                CROSS JOIN (
                SELECT
                    COUNT(1) AS finish_num
                FROM
                    dia_report_student_completion a
                WHERE
                    a.relId = #{relId}
                  AND a.completed = 1
                  AND a.stu_no IN (SELECT stu_no FROM view_stu_info
        <where>
            `status` NOT IN ('02','03')
            <if test="deptId != null"> and dept_id = #{deptId} </if>
            <if test="majorId != null"> and major_id = #{majorId} </if>
            <if test="classId != null"> and class_id = #{classId} </if>
            <if test="gradeId != null"> and grade_id = #{gradeId} </if>
            <if test="fdyNo != null"> and t_no = #{fdyNo} </if>
        </where>
         ) AND a.itemId IN (SELECT itemId FROM dia_report_items WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId}))) t3;
    </select>

    <select id="countDeptItemFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
            s.dept_name,
            COUNT(DISTINCT s.stu_no) AS stu_num,
            i.item_num,
            COUNT(c.completionId) AS finish_num,
            COUNT(DISTINCT s.stu_no) * i.item_num AS total_num
        FROM
            view_stu_info s
                JOIN
            (SELECT COUNT(1) AS item_num
             FROM dia_report_items
             WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId})
            ) i
                LEFT JOIN dia_report_student_completion c
                          ON c.stu_no = s.stu_no AND c.relId = #{relId} AND c.completed = 1
                              AND c.itemId IN (
                                  SELECT itemId FROM dia_report_items
                                  WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId})
                              )
        <where>
            s.status NOT IN ('02', '03')
          AND s.dept_name IS NOT NULL
            <if test="gradeId != null"> and s.grade_id = #{gradeId} </if>
        </where>
        GROUP BY s.dept_name, i.item_num
    </select>

    <select id="countDeptFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
            dept_name,
            COUNT(1) AS stu_num,
            SUM(is_finish) AS finish_num
        FROM
            (
                SELECT
                    a.stu_no,
                    a.dept_name,
                    CASE
                        WHEN COUNT(dri.itemId) = SUM(CASE WHEN drsc.completed = 1 THEN 1 ELSE 0 END)
                            THEN 1 ELSE 0
                        END AS is_finish
                FROM
                    view_stu_info a
                        JOIN dia_report_items dri ON dri.reportId = (
                        SELECT reportId FROM dia_report_release WHERE relId = #{relId}
                    )
                        LEFT JOIN dia_report_student_completion drsc
                                  ON drsc.itemId = dri.itemId
                                      AND drsc.relId = #{relId}
                                      AND drsc.stu_no = a.stu_no
        <where>
            a.`status` NOT IN ('02','03') and a.dept_name is not null
            <if test="gradeId != null"> and a.grade_id = #{gradeId} </if>
        </where>
                GROUP BY a.stu_no, a.dept_name
            ) t
        GROUP BY dept_name
        ORDER BY dept_name
        ;
    </select>

    <select id="countClassFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
        dept_name,
        major_name,
        class_name,
        COUNT(1) AS stu_num,
        SUM(is_finish) AS finish_num
        FROM
        (
        SELECT
        a.stu_no,
        a.dept_name,            -- 这里加上
        a.major_name,
        a.class_name,
        CASE
        WHEN COUNT(dri.itemId) = SUM(CASE WHEN drsc.completed = 1 THEN 1 ELSE 0 END)
        THEN 1 ELSE 0
        END AS is_finish
        FROM
        view_stu_info a
        JOIN dia_report_items dri ON dri.reportId = (
        SELECT reportId FROM dia_report_release WHERE relId = #{relId}
        )
        LEFT JOIN dia_report_student_completion drsc
        ON drsc.itemId = dri.itemId
        AND drsc.relId = #{relId}
        AND drsc.stu_no = a.stu_no
        <where>
            a.`status` NOT IN ('02','03') and a.dept_name is not null and a.major_name is not null
            <if test="gradeId != null"> and a.grade_id = #{gradeId} </if>
            <if test="deptId != null"> and a.dept_id = #{deptId} </if>
            <if test="majorId != null"> and a.major_id = #{majorId} </if>
            <if test="fdyNo != null"> and a.t_no = #{fdyNo} </if>
        </where>
        GROUP BY a.stu_no, a.dept_name, a.major_name,a.class_name
        ) t
        GROUP BY dept_name, major_name,class_name
        ORDER BY dept_name, major_name,class_name;
    </select>

    <select id="countMajorFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
            dept_name,
            major_name,
            COUNT(1) AS stu_num,
            SUM(is_finish) AS finish_num
        FROM
            (
                SELECT
                    a.stu_no,
                    a.dept_name,            -- 这里加上
                    a.major_name,
                    CASE
                        WHEN COUNT(dri.itemId) = SUM(CASE WHEN drsc.completed = 1 THEN 1 ELSE 0 END)
                            THEN 1 ELSE 0
                        END AS is_finish
                FROM
                    view_stu_info a
                        JOIN dia_report_items dri ON dri.reportId = (
                        SELECT reportId FROM dia_report_release WHERE relId = #{relId}
                    )
                        LEFT JOIN dia_report_student_completion drsc
                                  ON drsc.itemId = dri.itemId
                                      AND drsc.relId = #{relId}
                                      AND drsc.stu_no = a.stu_no
        <where>
            a.`status` NOT IN ('02','03') and a.dept_name is not null and a.major_name is not null
            <if test="gradeId != null"> and a.grade_id = #{gradeId} </if>
            <if test="deptId != null"> and a.dept_id = #{deptId} </if>
        </where>
                GROUP BY a.stu_no, a.dept_name, a.major_name
            ) t
        GROUP BY dept_name, major_name
        ORDER BY dept_name, major_name;
    </select>

    <select id="countClassItemFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
        s.dept_name,                -- 学院名称
        s.major_name,               -- 专业名称
        s.class_name,
        COUNT(DISTINCT s.stu_no) AS stu_num,
        i.item_num,
        COUNT(c.completionId) AS finish_num,
        COUNT(DISTINCT s.stu_no) * i.item_num AS total_num
        FROM
        view_stu_info s
        JOIN (
        SELECT COUNT(1) AS item_num
        FROM dia_report_items
        WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId =#{relId})
        ) i
        LEFT JOIN dia_report_student_completion c
        ON c.stu_no = s.stu_no
        AND c.relId = #{relId}
        AND c.completed = 1
        AND c.itemId IN (
        SELECT itemId FROM dia_report_items
        WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId})
        )
        <where>
            s.`status` NOT IN ('02','03') and s.dept_name is not null and s.major_name is not null
            <if test="gradeId != null"> and s.grade_id = #{gradeId} </if>
            <if test="deptId != null"> and s.dept_id = #{deptId} </if>
            <if test="majorId != null"> and s.major_id = #{majorId} </if>
            <if test="fdyNo != null"> and s.t_no = #{fdyNo} </if>
        </where>
        GROUP BY s.dept_name, s.major_name,s.class_name, i.item_num
        ORDER BY s.dept_name, s.major_name,s.class_name;
    </select>

    <select id="countMajorItemFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
            s.dept_name,                -- 学院名称
            s.major_name,               -- 专业名称
            COUNT(DISTINCT s.stu_no) AS stu_num,
            i.item_num,
            COUNT(c.completionId) AS finish_num,
            COUNT(DISTINCT s.stu_no) * i.item_num AS total_num
        FROM
            view_stu_info s
                JOIN (
                SELECT COUNT(1) AS item_num
                FROM dia_report_items
                WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId =#{relId})
            ) i
                LEFT JOIN dia_report_student_completion c
                          ON c.stu_no = s.stu_no
                              AND c.relId = #{relId}
                              AND c.completed = 1
                              AND c.itemId IN (
                                  SELECT itemId FROM dia_report_items
                                  WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId})
                              )
        <where>
            s.`status` NOT IN ('02','03') and s.dept_name is not null and s.major_name is not null
            <if test="gradeId != null"> and s.grade_id = #{gradeId} </if>
            <if test="deptId != null"> and s.dept_id = #{deptId} </if>
        </where>
        GROUP BY s.dept_name, s.major_name, i.item_num
        ORDER BY s.dept_name, s.major_name;
    </select>


    <select id="countGradeFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
        grade_name,
        COUNT(1) AS stu_num,
        SUM(is_finish) AS finish_num
        FROM
        (
        SELECT
        a.stu_no,
        a.grade_name,
        CASE
        WHEN COUNT(dri.itemId) = SUM(CASE WHEN drsc.completed = 1 THEN 1 ELSE 0 END)
        THEN 1 ELSE 0
        END AS is_finish
        FROM
        view_stu_info a
        JOIN dia_report_items dri ON dri.reportId = (
        SELECT reportId FROM dia_report_release WHERE relId = #{relId}
        )
        LEFT JOIN dia_report_student_completion drsc
        ON drsc.itemId = dri.itemId
        AND drsc.relId = #{relId}
        AND drsc.stu_no = a.stu_no
        <where>
            a.`status` NOT IN ('02','03') and a.dept_name is not null and a.major_name is not null
            and a.grade_name is not null
            <if test="majorId != null"> and a.major_id = #{majorId} </if>
            <if test="deptId != null"> and a.dept_id = #{deptId} </if>
        </where>
        GROUP BY a.stu_no, a.grade_name
        ) t
        GROUP BY grade_name
        ORDER BY grade_name;
    </select>

    <select id="countGradeItemFinishNumByRelId" parameterType="DiaReportStandards" resultType="Map">
        SELECT
        s.grade_name,                 -- 年级名称
        COUNT(DISTINCT s.stu_no) AS stu_num,
        i.item_num,
        COUNT(c.completionId) AS finish_num,
        COUNT(DISTINCT s.stu_no) * i.item_num AS total_num
        FROM
        view_stu_info s
        JOIN (
        SELECT COUNT(1) AS item_num
        FROM dia_report_items
        WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId})
        ) i
        LEFT JOIN dia_report_student_completion c
        ON c.stu_no = s.stu_no
        AND c.relId = #{relId}
        AND c.completed = 1
        AND c.itemId IN (
        SELECT itemId FROM dia_report_items
        WHERE reportId = (SELECT reportId FROM dia_report_release WHERE relId = #{relId})
        )
        WHERE
        s.`status` NOT IN ('02','03')
        AND s.grade_name IS NOT NULL
        <if test="deptId != null"> AND s.dept_id = #{deptId} </if>
        <if test="majorId != null"> AND s.major_id = #{majorId} </if>
        GROUP BY s.grade_name, i.item_num
        ORDER BY s.grade_name;
    </select>

</mapper>