<?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.qianhe.mapper.SjGfjsYdjcjlMapper">

    <resultMap type="SjGfjsYdjcjl" id="SjGfjsYdjcjlResult">
        <result property="id"    column="id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="rq"    column="rq"    />
        <result property="jcjb"    column="jcjb"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="yl1"    column="yl1"    />
        <result property="yl2"    column="yl2"    />
        <result property="yl3"    column="yl3"    />
        <result property="yl4"    column="yl4"    />
        <result property="yl5"    column="yl5"    />
        <result property="sfczwt"    column="sfczwt"    />
        <result property="zt"    column="zt"    />

        <result property="deptName"    column="dept_name"    />
        <result property="deptLx"    column="dept_lx"    />
        <result property="wtzs"    column="wtzs"    />
        <result property="yzgsl"    column="yzgsl"    />
    </resultMap>
    <resultMap id="SjGfjsYdjcjlSjGfjsZsdwwtcjResult" type="SjGfjsYdjcjl" extends="SjGfjsYdjcjlResult">
        <collection property="sjGfjsZsdwwtcjList" notNullColumn="sub_id" javaType="java.util.List" resultMap="SjGfjsZsdwwtcjResult" />
    </resultMap>
    <resultMap type="SjGfjsZsdwwtcj" id="SjGfjsZsdwwtcjResult">
        <result property="id"    column="sub_id"    />
        <result property="deptId"    column="sub_dept_id"    />
        <result property="rq"    column="sub_rq"    />
        <result property="jcjb"    column="sub_jcjb"    />
        <result property="wtmc"    column="sub_wtmc"    />
        <result property="khnr"    column="sub_khnr"    />
        <result property="khxd"    column="sub_khxd"    />
        <result property="pjbzid"    column="sub_pjbzid"    />
        <result property="pjbzmc"    column="sub_pjbzmc"    />
        <result property="zgzrr"    column="sub_zgzrr"    />
        <result property="zgcs"    column="sub_zgcs"    />
        <result property="zgqx"    column="sub_zgqx"    />
        <result property="zgzt"    column="sub_zgzt"    />
        <result property="createBy"    column="sub_create_by"    />
        <result property="createTime"    column="sub_create_time"    />
        <result property="updateBy"    column="sub_update_by"    />
        <result property="updateTime"    column="sub_update_time"    />
        <result property="remark"    column="sub_remark"    />
        <result property="yl1"    column="sub_yl1"    />
        <result property="yl2"    column="sub_yl2"    />
        <result property="yl3"    column="sub_yl3"    />
        <result property="yl4"    column="sub_yl4"    />
        <result property="yl5"    column="sub_yl5"    />
        <result property="pid"    column="sub_pid"    />
        <result property="ldnr"    column="sub_ldnr"    />

        <result property="deptName"    column="dept_name"    />
        <result property="deptLx"    column="dept_lx"    />
    </resultMap>

    <sql id="selectSjGfjsYdjcjlVo">
        select id,
               a.dept_id,
               rq,
               jcjb,
               a.create_by,
               a.create_time,
               a.update_by,
               a.update_time,
               remark,
               yl1,
               yl2,
               yl3,
               yl4,
               yl5, sfczwt, a.zt,
                 CONCAT(p.dept_name,'/',d.dept_name) dept_name,
               d.dept_lx,
               (select count(id) from sj_gfjs_zsdwwtcj c where a.id = c.pid ) as wtzs,
               (select count(id) from sj_gfjs_zsdwwtcj c where a.id = c.pid and c.zgzt = '已整改' ) as yzgsl
        from sj_gfjs_ydjcjl a
        left join sys_dept d on a.dept_id=d.dept_id
         LEFT JOIN  sys_dept p on d.parent_id = p.dept_id
    </sql>
    <select id="selectSjGfjsYdjcjlList" parameterType="SjGfjsYdjcjl" resultMap="SjGfjsYdjcjlResult">
        <include refid="selectSjGfjsYdjcjlVo"/>
        <where>
            <if test="deptId != null ">and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="rq != null  and rq != ''"> and rq = #{rq}</if>
            <if test="jcjb != null  and jcjb != ''"> and jcjb = #{jcjb}</if>
            <if test="yl1 != null  and yl1 != ''"> and yl1 = #{yl1}</if>
            <if test="yl2 != null "> and yl2 = #{yl2}</if>
            <if test="yl3 != null "> and yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and yl5 = #{yl5}</if>
            <if test="sfczwt != null  and sfczwt != ''"> and sfczwt = #{sfczwt}</if>
            <if test="zt != null  and zt != ''"> and zt = #{zt}</if>
            <if test="startRq != null  and startRq != ''"> and rq >= #{startRq}</if>
            <if test="endRq != null  and endRq != ''"> and rq &lt;= #{endRq}</if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
        order by  a.rq desc
    </select>
    <select id="selectSjGfjsYdjcjlList_bfz" parameterType="SjGfjsYdjcjl" resultMap="SjGfjsYdjcjlResult">
        <include refid="selectSjGfjsYdjcjlVo"/>
        <where>
            <if test="deptId != null ">and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="rq != null  and rq != ''"> and rq = #{rq}</if>
            <if test="jcjb != null  and jcjb != ''"> and jcjb = #{jcjb}</if>
            <if test="yl1 != null  and yl1 != ''"> and yl1 = #{yl1}</if>
            <if test="yl2 != null "> and yl2 = #{yl2}</if>
            <if test="yl3 != null "> and yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and yl5 = #{yl5}</if>
            <if test="sfczwt != null  and sfczwt != ''"> and sfczwt = #{sfczwt}</if>
            <if test="zt != null  and zt != ''"> and zt = #{zt}</if>
            <if test="startRq != null  and startRq != ''"> and rq >= #{startRq}</if>
            <if test="endRq != null  and endRq != ''"> and rq &lt;= #{endRq}</if>
        </where>
        order by  a.rq desc
    </select>

    <select id="selectSjGfjsYdjcjlById" parameterType="Long" resultMap="SjGfjsYdjcjlSjGfjsZsdwwtcjResult">
        select a.id, a.dept_id, a.rq, a.jcjb, a.create_by, a.create_time, a.update_by, a.update_time, a.remark,
               a.yl1, a.yl2, a.yl3, a.yl4, a.yl5, a.sfczwt, a.zt,
               b.id as sub_id, b.dept_id as sub_dept_id, b.rq as sub_rq, b.jcjb as sub_jcjb, b.wtmc as sub_wtmc,
               b.khnr as sub_khnr, b.khxd as sub_khxd, b.pjbzid as sub_pjbzid, b.pjbzmc as sub_pjbzmc,
               b.zgzrr as sub_zgzrr, b.zgcs as sub_zgcs, b.zgqx as sub_zgqx, b.zgzt as sub_zgzt,
               b.create_by as sub_create_by, b.create_time as sub_create_time, b.update_by as sub_update_by,
               b.update_time as sub_update_time, b.remark as sub_remark, b.yl1 as sub_yl1, b.yl2 as sub_yl2,
               b.yl3 as sub_yl3, b.yl4 as sub_yl4, b.yl5 as sub_yl5, b.ldnr as sub_ldnr,
               b.pid as sub_pid, d.dept_name, d.dept_lx
        from sj_gfjs_ydjcjl a
        left join sj_gfjs_zsdwwtcj b on b.pid = a.id
        left join sys_dept d on a.dept_id=d.dept_id
        where a.id = #{id}
    </select>



    <insert id="insertSjGfjsYdjcjl" parameterType="SjGfjsYdjcjl" useGeneratedKeys="true" keyProperty="id">
        insert into sj_gfjs_ydjcjl
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deptId != null">dept_id,</if>
            <if test="rq != null">rq,</if>
            <if test="jcjb != null">jcjb,</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>
            <if test="remark != null">remark,</if>
            <if test="yl1 != null">yl1,</if>
            <if test="yl2 != null">yl2,</if>
            <if test="yl3 != null">yl3,</if>
            <if test="yl4 != null">yl4,</if>
            <if test="yl5 != null">yl5,</if>
            <if test="sfczwt != null">sfczwt,</if>
            <if test="zt != null">zt,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deptId != null">#{deptId},</if>
            <if test="rq != null">#{rq},</if>
            <if test="jcjb != null">#{jcjb},</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>
            <if test="remark != null">#{remark},</if>
            <if test="yl1 != null">#{yl1},</if>
            <if test="yl2 != null">#{yl2},</if>
            <if test="yl3 != null">#{yl3},</if>
            <if test="yl4 != null">#{yl4},</if>
            <if test="yl5 != null">#{yl5},</if>
            <if test="sfczwt != null">#{sfczwt},</if>
            <if test="zt != null">#{zt},</if>
        </trim>
    </insert>
    <update id="updateSjGfjsYdjcjl" parameterType="SjGfjsYdjcjl">
        update sj_gfjs_ydjcjl
        <trim prefix="SET" suffixOverrides=",">
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="rq != null">rq = #{rq},</if>
            <if test="jcjb != null">jcjb = #{jcjb},</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>
            <if test="remark != null">remark = #{remark},</if>
            <if test="yl1 != null">yl1 = #{yl1},</if>
            <if test="yl2 != null">yl2 = #{yl2},</if>
            <if test="yl3 != null">yl3 = #{yl3},</if>
            <if test="yl4 != null">yl4 = #{yl4},</if>
            <if test="yl5 != null">yl5 = #{yl5},</if>
            <if test="sfczwt != null">sfczwt = #{sfczwt},</if>
            <if test="zt != null">zt = #{zt},</if>
        </trim>
        where id = #{id}
    </update>

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

    <delete id="deleteSjGfjsZsdwwtcjByPids" parameterType="String">
        delete from sj_gfjs_zsdwwtcj where pid in
        <foreach item="pid" collection="array" open="(" separator="," close=")">
            #{pid}
        </foreach>
    </delete>
    <delete id="deleteSjGfjsZsdwwtcjByPid" parameterType="Long">
        delete from sj_gfjs_zsdwwtcj where pid = #{pid}
    </delete>
    <insert id="batchSjGfjsZsdwwtcj">
        insert into sj_gfjs_zsdwwtcj( dept_id, rq, jcjb, wtmc, khnr, khxd, pjbzid, pjbzmc, zgzrr, zgcs, zgqx, zgzt,
                                     create_by, create_time, update_by, update_time, remark, yl1, yl2, yl3, yl4, yl5,
                                     pid, ldnr) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.deptId}, #{item.rq}, #{item.jcjb}, #{item.wtmc}, #{item.khnr}, #{item.khxd}, #{item.pjbzid},
             #{item.pjbzmc}, #{item.zgzrr}, #{item.zgcs}, #{item.zgqx}, #{item.zgzt}, #{item.createBy},
             #{item.createTime}, #{item.updateBy}, #{item.updateTime}, #{item.remark}, #{item.yl1}, #{item.yl2},
             #{item.yl3}, #{item.yl4}, #{item.yl5}, #{item.pid}, #{item.ldnr})
        </foreach>
    </insert>



    <resultMap type="SjGfjsZsdwwtcj" id="SjGfjsZsdwwtcjResult2">
        <result property="id"    column="id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="rq"    column="rq"    />
        <result property="jcjb"    column="jcjb"    />
        <result property="wtmc"    column="wtmc"    />
        <result property="khnr"    column="khnr"    />
        <result property="khxd"    column="khxd"    />
        <result property="pjbzid"    column="pjbzid"    />
        <result property="pjbzmc"    column="pjbzmc"    />
        <result property="zgzrr"    column="zgzrr"    />
        <result property="zgcs"    column="zgcs"    />
        <result property="zgqx"    column="zgqx"    />
        <result property="zgzt"    column="zgzt"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <result property="yl1"    column="yl1"    />
        <result property="yl2"    column="yl2"    />
        <result property="yl3"    column="yl3"    />
        <result property="yl4"    column="yl4"    />
        <result property="yl5"    column="yl5"    />
        <result property="deptName"    column="dept_name"    />
        <result property="deptLx"    column="dept_lx"    />
        <result property="ldnr"    column="ldnr"    />
    </resultMap>
    <select id="selectSjGfjsZsdwwtcjList" parameterType="SjGfjsZsdwwtcj" resultMap="SjGfjsZsdwwtcjResult2">
        select id,
        a.dept_id,
        rq,
        jcjb,
        wtmc,
        khnr,
        khxd,
        pjbzid,
        pjbzmc,
        zgzrr,
        zgcs,
        zgqx,
        zgzt,
        a.create_by,
        a.create_time,
        a.update_by,
        a.update_time,
        remark,
        yl1,
        yl2,
        yl3,
        yl4,
        yl5, d.dept_name,d.dept_lx,ldnr
        from sj_gfjs_zsdwwtcj a
        left join sys_dept d on a.dept_id=d.dept_id
        <where>
            <if test="deptId != null ">and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="rq != null  and rq != ''"> and rq = #{rq}</if>
            <if test="jcjb != null  and jcjb != ''"> and jcjb = #{jcjb}</if>
            <if test="wtmc != null  and wtmc != ''"> and wtmc = #{wtmc}</if>
            <if test="khnr != null  and khnr != ''"> and khnr = #{khnr}</if>
            <if test="khxd != null  and khxd != ''"> and khxd = #{khxd}</if>
            <if test="pjbzid != null  and pjbzid != ''"> and pjbzid = #{pjbzid}</if>
            <if test="pjbzmc != null  and pjbzmc != ''"> and pjbzmc = #{pjbzmc}</if>
            <if test="zgzrr != null  and zgzrr != ''"> and zgzrr = #{zgzrr}</if>
            <if test="zgcs != null  and zgcs != ''"> and zgcs = #{zgcs}</if>
            <if test="zgqx != null  and zgqx != ''"> and zgqx = #{zgqx}</if>
            <if test="zgzt != null  and zgzt != ''"> and zgzt = #{zgzt}</if>
            <if test="yl1 != null  and yl1 != ''"> and yl1 = #{yl1}</if>
            <if test="yl2 != null  and yl2 != ''"> and yl2 = #{yl2}</if>
            <if test="yl3 != null  and yl3 != ''"> and yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and yl5 = #{yl5}</if>
            <if test="startRq != null  and startRq != ''"> and rq >= #{startRq}</if>
            <if test="endRq != null  and endRq != ''"> and rq &lt;= #{endRq}</if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
        order by  a.rq desc
    </select>


    <select id="selectSjGfjsYdjcjlList_main" parameterType="SjGfjsYdjcjl" resultMap="SjGfjsYdjcjlResult">
        <include refid="selectSjGfjsYdjcjlVo"/>
        <where>
            <if test="deptId != null ">and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="rq != null  and rq != ''"> and rq = #{rq}</if>
            <if test="jcjb != null  and jcjb != ''"> and jcjb = #{jcjb}</if>
            <if test="yl1 != null  and yl1 != ''"> and yl1 = #{yl1}</if>
            <if test="yl2 != null "> and yl2 = #{yl2}</if>
            <if test="yl3 != null "> and yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and yl5 = #{yl5}</if>
            <if test="sfczwt != null  and sfczwt != ''"> and sfczwt = #{sfczwt}</if>
            <if test="zt != null  and zt != ''"> and zt = #{zt}</if>
            <if test="startRq != null  and startRq != ''"> and rq >= #{startRq}</if>
            <if test="endRq != null  and endRq != ''"> and rq &lt;= #{endRq}</if>
        </where>
        order by  a.rq desc
    </select>


    
</mapper>
