<?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.SjBzxxMapper">
    
    <resultMap type="SjBzxx" id="SjBzxxResult">
        <result property="id"    column="id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="bzlx"    column="bzlx"    />
        <result property="bzmc"    column="bzmc"    />
        <result property="rs"    column="rs"    />
        <result property="zt"    column="zt"    />
        <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="deptName"    column="dept_name"    />
        <result property="bzzs"    column="bzzs"    />
        <result property="zrs"    column="zrs"    />
    </resultMap>

    <sql id="selectSjBzxxVo">
        select a.id, a.dept_id, a.bzlx, a.bzmc, a.rs, a.zt, a.create_by, a.create_time, a.update_by, a.update_time,
               a.remark,
               CONCAT(p.dept_name,'/',d.dept_name) dept_name
          from sj_bzxx 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="selectSjBzxxList" parameterType="SjBzxx" resultMap="SjBzxxResult">
        <include refid="selectSjBzxxVo"/>
        <where>
            <if test="deptId != null ">and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="bzlx != null  and bzlx != ''"> and bzlx = #{bzlx}</if>
            <if test="bzmc != null  and bzmc != ''"> and bzmc = #{bzmc}</if>
            <if test="rs != null  and rs != ''"> and rs = #{rs}</if>
            <if test="zt != null  and zt != ''"> and zt = #{zt}</if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
    </select>
    <select id="selectSjBzxxById" parameterType="Long" resultMap="SjBzxxResult">
        <include refid="selectSjBzxxVo"/>
        where a.id = #{id}
    </select>
        
    <insert id="insertSjBzxx" parameterType="SjBzxx" useGeneratedKeys="true" keyProperty="id">
        insert into sj_bzxx
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deptId != null">dept_id,</if>
            <if test="bzlx != null">bzlx,</if>
            <if test="bzmc != null">bzmc,</if>
            <if test="rs != null">rs,</if>
            <if test="zt != null">zt,</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>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deptId != null">#{deptId},</if>
            <if test="bzlx != null">#{bzlx},</if>
            <if test="bzmc != null">#{bzmc},</if>
            <if test="rs != null">#{rs},</if>
            <if test="zt != null">#{zt},</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>
         </trim>
    </insert>
    <update id="updateSjBzxx" parameterType="SjBzxx">
        update sj_bzxx
        <trim prefix="SET" suffixOverrides=",">
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="bzlx != null">bzlx = #{bzlx},</if>
            <if test="bzmc != null">bzmc = #{bzmc},</if>
            <if test="rs != null">rs = #{rs},</if>
            <if test="zt != null">zt = #{zt},</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>
        </trim>
        where id = #{id}
    </update>

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


    <!--查询统计-->
    <select id="selectBzxxTj" parameterType="SjBzxx" resultMap="SjBzxxResult">
        select count(a.id) bzzs, sum(a.rs) zrs
        from sj_bzxx a
         left join sys_dept d on a.dept_id=d.dept_id
        where 1=1
        <if test="deptId != null "> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
        <if test="bzlx != null  and bzlx != ''"> and bzlx = #{bzlx}</if>
        <if test="bzmc != null  and bzmc != ''"> and bzmc = #{bzmc}</if>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
         limit 1
    </select>
    <select id="jcdwtjList" resultType="com.qianhe.domain.Vo.JcdwxxVo">
        SELECT
        t.*,
        b.jcdwrs,
        s.zsdwrs
        FROM (
            -- 原查询结果作为基础表t
            select
            a.dept_id zsdwid,a.dept_name zsdwmc,a.ancestors zsdw_ancestors,a.order_num zsdwpx,a.dwlx zsdwlx,
            b.dept_id jcdwid,b.parent_id,b.dept_name jcdwmc,b.ancestors jcdw_ancestors,b.order_num jcdwpx,b.dwlx jcdwlx,
            b.bzid,b.bzmc,b.bzlx,case when b.bzrs is null then a.jcrysl else bzrs end bzrs,a.jcrysl zsdwrysl,b.jcrysl
            jcdwyrsl
            from (
                select a.dept_id,a.dept_name,a.ancestors,a.order_num,b.dwlx,b.jcrysl
                from sys_dept a
                left join sys_dept_cb b on a.dept_id=b.dept_id
                where dwjb='3'
                and status='0'
                and del_flag='0'

                <!-- 数据范围过滤 -->
                ${params.dataScope}

                order by order_num
            )a
            left join (
                select a.dept_id,a.parent_id,a.dept_name,a.ancestors,a.order_num,b.dwlx,c.id bzid,c.bzmc,c.bzlx,c.rs
                bzrs,b.jcrysl
                from sys_dept a
                left join sys_dept_cb b on a.dept_id=b.dept_id
                left join sj_bzxx c on a.dept_id=c.dept_id
                where dwjb='4'
                and status='0'
                and del_flag='0'
                order by order_num
            ) b on a.dept_id=b.parent_id
        ) t
        -- 关联计算jcdwrs（同一jcdwid的bzrs总和）
        LEFT JOIN (
            select
            dept_id jcdwid,
            SUM(bzrs) jcdwrs
            from (
                select a.dept_id,c.rs bzrs
                from sys_dept a
                left join sj_bzxx c on a.dept_id=c.dept_id
                where dwjb='4'
                and status='0'
                and del_flag='0'
            ) temp
            group by dept_id
        ) b ON t.jcdwid = b.jcdwid
        -- 关联计算zsdwrs（同一zsdwid的jcdwrs总和）
        LEFT JOIN (
            select
            parent_id zsdwid,
            SUM(jcdwrs) zsdwrs
            from (
                select
                a.parent_id,
                SUM(c.rs) jcdwrs
                from sys_dept a
                left join sj_bzxx c on a.dept_id=c.dept_id
                where dwjb='4'
                and status='0'
                and del_flag='0'
                group by a.dept_id,a.parent_id
            ) temp
            group by parent_id
        ) s ON t.zsdwid = s.zsdwid
        where 1=1
        <if test="zsdwlx != null  and zsdwlx != ''">and t.zsdwlx = #{zsdwlx}</if>
        <if test="jcdwlx != null  and jcdwlx != ''">and t.jcdwlx = #{jcdwlx}</if>
        <if test="bzlx != null  and bzlx != ''">and t.bzlx = #{bzlx}</if>
        <if test="zsdwid != null">and (t.zsdwid=#{zsdwid} or find_in_set(#{zsdwid},t.zsdw_ancestors))</if>
        <if test="jcdwid != null">and (t.jcdwid=#{jcdwid} or find_in_set(#{jcdwid},t.jcdw_ancestors))</if>
        order by t.zsdwpx,t.jcdwpx
    </select>

    <update id="plxg" parameterType="SjBzxx">
        update sj_bzxx
        <trim prefix="SET" suffixOverrides=",">
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="bzlx != null">bzlx = #{bzlx},</if>
            <if test="bzmc != null">bzmc = #{bzmc},</if>
            <if test="rs != null">rs = #{rs},</if>
            <if test="zt != null">zt = #{zt},</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>
        </trim>
        where find_in_set(id, #{ids})
    </update>
</mapper>