<?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.ruoyi.postCheck.mapper.PostListMapper">

    <resultMap type="PostList" id="PostListResult">
        <result property="id"    column="id"    />
        <result property="status"    column="status"    />
        <result property="statusByUserId"    column="status_by_user_id"    />
        <result property="statusByUserName"    column="status_by_user_name"    />
        <result property="unitId"    column="unit_id"    />
        <result property="postId"    column="post_id"    />
        <result property="postName"    column="post_name"    />
        <result property="positionLevel"    column="position_level"    />
        <result property="directSuperior"    column="direct_superior"    />
        <result property="subordinateCount"    column="subordinate_count"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />
        <result property="positionCode"    column="position_code"    />
        <result property="isConfidential"    column="is_confidential"    />
        <result property="directSubordinate"    column="direct_subordinate"    />
        <result property="creationDate"    column="creation_date"    />
        <result property="revisionDate"    column="revision_date"    />
        <result property="jobSummary"    column="job_summary"    />
        <result property="remark"    column="remark"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createTime"    column="create_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="createUserId"    column="create_user_id"    />
    </resultMap>

    <resultMap id="PostListPostListInfoResult" type="PostList" extends="PostListResult">
        <!--<collection property="postListInfoList" notNullColumn="sub_id" javaType="java.util.List" resultMap="PostListInfoResult" />-->
        <collection property="children" notNullColumn="sub_id" javaType="java.util.List" resultMap="PostListInfoResult" />
    </resultMap>

    <resultMap type="PostListInfo" id="PostListInfoResult">
        <result property="id"    column="sub_id"    />
        <result property="status"    column="sub_status"    />
        <result property="statusByUserId"    column="sub_status_by_user_id"    />
        <result property="statusByUserName"    column="sub_status_by_user_name"    />
        <result property="postListId"    column="sub_post_list_id"    />
        <result property="type"    column="sub_type"    />
        <result property="sequenceProject"    column="sub_sequence_project"    />
        <result property="levelOne"    column="sub_level_one"    />
        <result property="levelTwo"    column="sub_level_two"    />
        <result property="levelThree"    column="sub_level_three"    />
        <result property="levelFour"    column="sub_level_four"    />
        <result property="levelFive"    column="sub_level_five"    />
        <result property="workStandard"    column="sub_work_standard"    />
        <result property="requirement"    column="sub_requirement"    />
        <result property="remark"    column="sub_remark"    />
        <result property="delFlag"    column="sub_del_flag"    />
        <result property="createTime"    column="sub_create_time"    />
        <result property="createBy"    column="sub_create_by"    />
        <result property="updateTime"    column="sub_update_time"    />
        <result property="updateBy"    column="sub_update_by"    />
        <result property="createUserId"    column="sub_create_user_id"    />
    </resultMap>

    <sql id="selectPostListVo">
        select
            t1.id,
            t1.status,
            t1.status_by_user_id,
            t1.status_by_user_name,
            t1.unit_id,
            t1.post_id,
            t1.position_level,
            t1.direct_superior,
            t1.subordinate_count,
            t1.dept_id,
            t1.position_code,
            t1.is_confidential,
            t1.direct_subordinate,
            t1.creation_date,
            t1.revision_date,
            t1.job_summary,
            t1.remark,
            t1.del_flag,
            t1.create_time,
            t1.create_by,
            t1.update_time,
            t1.update_by,
            t1.create_user_id,
            t2."dept_name" ,
            t3."post_name"
        from post_list t1
        LEFT JOIN "sys_dept" t2 ON t2."dept_id" = t1."dept_id"
        LEFT JOIN "sys_post" t3 ON t3."post_id" = t1."post_id"
    </sql>

    <select id="selectPostListList" parameterType="PostList" resultMap="PostListResult">
        <include refid="selectPostListVo"/>
        <where>
            <if test="status != null "> and t1.status = #{status}</if>
            <if test="statusByUserId != null "> and t1.status_by_user_id = #{statusByUserId}</if>
            <if test="statusByUserName != null  and statusByUserName != ''"> and t1.status_by_user_name like concat('%', #{statusByUserName}, '%')</if>
            <if test="unitId != null  and unitId != ''"> and t1.unit_id = #{unitId}</if>
            <if test="positionLevel != null  and positionLevel != ''"> and t1.position_level = #{positionLevel}</if>
            <if test="directSuperior != null  and directSuperior != ''"> and t1.direct_superior = #{directSuperior}</if>
            <if test="subordinateCount != null "> and t1.subordinate_count = #{subordinateCount}</if>
            <if test="positionCode != null  and positionCode != ''"> and t1.position_code = #{positionCode}</if>
            <if test="isConfidential != null "> and t1.is_confidential = #{isConfidential}</if>
            <if test="directSubordinate != null  and directSubordinate != ''"> and t1.direct_subordinate = #{directSubordinate}</if>
            <if test="creationDate != null "> and t1.creation_date = #{creationDate}</if>
            <if test="revisionDate != null "> and t1.revision_date = #{revisionDate}</if>
            <if test="jobSummary != null  and jobSummary != ''"> and t1.job_summary = #{jobSummary}</if>
            <if test="createUserId != null "> and t1.create_user_id = #{createUserId}</if>

            <if test="deptId != null "> and t1.dept_id = #{deptId}</if>
            <if test="postId != null "> and t1.post_id = #{postId}</if>
            <if test="deptName != null  and deptName != ''"> and t2.dept_name like concat('%', #{deptName}, '%')</if>
            <if test="postName != null  and postName != ''"> and t3.post_name like concat('%', #{postName}, '%')</if>
        </where>
    </select>

    <select id="selectPostListById" parameterType="Long" resultMap="PostListPostListInfoResult">
        select
            a.id, a.status, a.status_by_user_id, a.status_by_user_name, a.unit_id,
            a.post_id,

            a.position_level,
            a.direct_superior, a.subordinate_count,
            a.dept_id,

            a.position_code, a.is_confidential,
            a.direct_subordinate, a.creation_date, a.revision_date,
            a.job_summary, a.remark, a.del_flag, a.create_time, a.create_by, a.update_time, a.update_by,
            a.create_user_id,

            b.id as sub_id, b.status as sub_status,
            b.status_by_user_id as sub_status_by_user_id,
            b.status_by_user_name as sub_status_by_user_name,
            b.post_list_id as sub_post_list_id, b.type as sub_type,
            b.sequence_project as sub_sequence_project, b.level_one as sub_level_one,
            b.level_two as sub_level_two, b.level_three as sub_level_three, b.level_four as sub_level_four,
            b.level_five as sub_level_five, b.work_standard as sub_work_standard, b.requirement as sub_requirement,
            b.remark as sub_remark, b.del_flag as sub_del_flag, b.create_time as sub_create_time, b.create_by as sub_create_by,
            b.update_time as sub_update_time, b.update_by as sub_update_by, b.create_user_id as sub_create_user_id,

            t2.dept_name,
            t3.post_name
        from post_list a
        left join post_list_info b on b.post_list_id = a.id
        LEFT JOIN "sys_dept" t2 ON t2."dept_id" = a."dept_id"
        LEFT JOIN "sys_post" t3 ON t3."post_id" = a."post_id"
        where a.id = #{id}
    </select>

    <insert id="insertPostList" parameterType="PostList">
        insert into post_list
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="status != null">status,</if>
            <if test="statusByUserId != null">status_by_user_id,</if>
            <if test="statusByUserName != null">status_by_user_name,</if>
            <if test="unitId != null">unit_id,</if>
            <if test="postId != null">post_id,</if>
            <if test="positionLevel != null">position_level,</if>
            <if test="directSuperior != null">direct_superior,</if>
            <if test="subordinateCount != null">subordinate_count,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="positionCode != null">position_code,</if>
            <if test="isConfidential != null">is_confidential,</if>
            <if test="directSubordinate != null">direct_subordinate,</if>
            <if test="creationDate != null">creation_date,</if>
            <if test="revisionDate != null">revision_date,</if>
            <if test="jobSummary != null">job_summary,</if>
            <if test="remark != null">remark,</if>
            <if test="delFlag != null and delFlag != ''">del_flag,</if>
            <if test="createTime != null">create_time,</if>
            <if test="createBy != null and createBy != ''">create_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null and updateBy != ''">update_by,</if>
            <if test="createUserId != null">create_user_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="status != null">#{status},</if>
            <if test="statusByUserId != null">#{statusByUserId},</if>
            <if test="statusByUserName != null">#{statusByUserName},</if>
            <if test="unitId != null">#{unitId},</if>
            <if test="postId != null">#{postId},</if>
            <if test="positionLevel != null">#{positionLevel},</if>
            <if test="directSuperior != null">#{directSuperior},</if>
            <if test="subordinateCount != null">#{subordinateCount},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="positionCode != null">#{positionCode},</if>
            <if test="isConfidential != null">#{isConfidential},</if>
            <if test="directSubordinate != null">#{directSubordinate},</if>
            <if test="creationDate != null">#{creationDate},</if>
            <if test="revisionDate != null">#{revisionDate},</if>
            <if test="jobSummary != null">#{jobSummary},</if>
            <if test="remark != null">#{remark},</if>
            <if test="delFlag != null and delFlag != ''">#{delFlag},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="createBy != null and createBy != ''">#{createBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null and updateBy != ''">#{updateBy},</if>
            <if test="createUserId != null">#{createUserId},</if>
         </trim>
    </insert>

    <update id="updatePostList" parameterType="PostList">
        update post_list
        <trim prefix="SET" suffixOverrides=",">
            <if test="status != null">status = #{status},</if>
            <if test="statusByUserId != null">status_by_user_id = #{statusByUserId},</if>
            <if test="statusByUserName != null">status_by_user_name = #{statusByUserName},</if>
            <if test="unitId != null">unit_id = #{unitId},</if>
            <if test="postId != null">post_id = #{postId},</if>
            <if test="positionLevel != null">position_level = #{positionLevel},</if>
            <if test="directSuperior != null">direct_superior = #{directSuperior},</if>
            <if test="subordinateCount != null">subordinate_count = #{subordinateCount},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="positionCode != null">position_code = #{positionCode},</if>
            <if test="isConfidential != null">is_confidential = #{isConfidential},</if>
            <if test="directSubordinate != null">direct_subordinate = #{directSubordinate},</if>
            <if test="creationDate != null">creation_date = #{creationDate},</if>
            <if test="revisionDate != null">revision_date = #{revisionDate},</if>
            <if test="jobSummary != null">job_summary = #{jobSummary},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="delFlag != null and delFlag != ''">del_flag = #{delFlag},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createBy != null and createBy != ''">create_by = #{createBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="createUserId != null">create_user_id = #{createUserId},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deletePostListById" parameterType="Long">
        delete from post_list where id = #{id}
    </delete>

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

    <delete id="deletePostListInfoByPostListIds" parameterType="String">
        delete from post_list_info where post_list_id in
        <foreach item="postListId" collection="array" open="(" separator="," close=")">
            #{postListId}
        </foreach>
    </delete>

    <delete id="deletePostListInfoByPostListId" parameterType="Long">
        delete from post_list_info where post_list_id = #{postListId}
    </delete>

    <insert id="batchPostListInfo">
        insert into post_list_info( id, status, status_by_user_id, status_by_user_name, post_list_id, type, sequence_project, level_one, level_two, level_three, level_four, level_five, work_standard, requirement, remark, del_flag, create_time, create_by, update_time, update_by, create_user_id) values
		<foreach item="item" index="index" collection="list" separator=",">
            ( #{item.id}, #{item.status}, #{item.statusByUserId}, #{item.statusByUserName}, #{item.postListId}, #{item.type}, #{item.sequenceProject}, #{item.levelOne}, #{item.levelTwo}, #{item.levelThree}, #{item.levelFour}, #{item.levelFive}, #{item.workStandard}, #{item.requirement}, #{item.remark}, #{item.delFlag}, #{item.createTime}, #{item.createBy}, #{item.updateTime}, #{item.updateBy}, #{item.createUserId})
        </foreach>
    </insert>

    <select id="contentListByType" resultType="PostCheckedItems">
        SELECT
            t2.id,
            t2.type,
            t2.level_one  as levelOne,
            t2.level_two as levelTwo,
            t2.level_three as levelThre,
            t2.level_four as levelFour,
            t2.level_five as levelFive,
            case
                when t2.level_five IS NOT NULL AND CHAR_LENGTH(TRIM(t2.level_five)) > 0 then TRIM(t2.level_five)
                when t2.level_four IS NOT NULL AND CHAR_LENGTH(TRIM(t2.level_four)) > 0  then TRIM(t2.level_four)
                when t2.level_three IS NOT NULL AND CHAR_LENGTH(TRIM(t2.level_three)) > 0  then TRIM(t2.level_three)
                when t2.level_two IS NOT NULL AND CHAR_LENGTH(TRIM(t2.level_two)) > 0  then TRIM(t2.level_two)
                when t2.level_one IS NOT NULL AND CHAR_LENGTH(TRIM(t2.level_one)) > 0  then TRIM(t2.level_one)
               <!--
                when t2.level_five IS NOT NULL then t2.level_five
                when t2.level_four IS NOT NULL then t2.level_four
                when t2.level_three IS NOT NULL then t2.level_three
                when t2.level_two IS NOT NULL then t2.level_two
                when t2.level_one IS NOT NULL then t2.level_one
               -->
                else 'Other'
            END AS postCheckedContent,
            t2.work_standard as postCheckedStandard
        FROM "post_list" t1
        INNER JOIN "post_list_info" t2 ON t2."post_list_id" = t1."id"
        WHERE t1."dept_id" = #{deptId}
        AND t1."post_id" = #{postId}
        AND t2."type" = #{type}
    </select>

    <select id="userTotal" resultType="java.lang.Integer">
        SELECT count(0) FROM "public"."sys_users"
    </select>

    <select id="postChecked" resultType="com.ruoyi.postCheck.domain.homePage.PostCheckedUnCheckedView">
        SELECT
            t2."checked_dept_id" deptId ,
            t2.checked_post_id postId,
            (select a."post_name" from "public"."sys_post" a WHERE a."post_id" = t2.checked_post_id) AS postName,
            (select aa."dept_name" from "public"."sys_dept" aa WHERE aa."dept_id" = t2.checked_dept_id) AS deptName,
            (select count(0) from "public"."sys_user_post" a LEFT JOIN "public"."sys_users" b ON a."user_id" = b."user_id" WHERE a."post_id" = t2."checked_post_id") AS "userCount"
        FROM "post_plan" t1
                 INNER JOIN "post_plan_info" t2 ON t2."post_plan_id" = t1."id"
        WHERE t1.status = 3 -- 状态：1-待审核；2-审核中；3-通过；4-驳回；
          AND t2."status" = #{status} -- 岗检状态：1-未检；2-已检；
        GROUP BY t2."checked_dept_id", t2."checked_post_id"
        ORDER BY t2."checked_dept_id"
    </select>

    <select id="getPostCountByDeptId" resultType="java.lang.Integer">
        SELECT COUNT(0) FROM "public"."sys_user_post" sup LEFT JOIN "public"."sys_user_role" sur ON sup."user_id" = sur."user_id"
                                                          LEFT JOIN "public"."sys_role_dept" srd ON sur."role_id" = srd."role_id"
        WHERE srd."dept_id" = #{deptId}
    </select>
</mapper>
