<?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.SjFybpMapper">
    
    <resultMap type="SjFybp" id="SjFybpResult">
        <result property="id"    column="id"    />
        <result property="zbId"    column="zb_id"    />
        <result property="fymc"    column="fymc"    />
        <result property="fyrq"    column="fyrq"    />
        <result property="deptId"    column="dept_id"    />
        <result property="fylx"    column="fylx"    />
        <result property="fyms"    column="fyms"    />
        <result property="fyje"    column="fyje"    />
        <result property="fjid"    column="fjid"    />
        <result property="zt"    column="zt"    />
        <result property="tjr"    column="tjr"    />
        <result property="tjrq"    column="tjrq"    />
        <result property="sprq"    column="sprq"    />
        <result property="spr"    column="spr"    />
        <result property="pfje"    column="pfje"    />
        <result property="ysyje"    column="ysyje"    />
        <result property="spyj"    column="spyj"    />
        <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="sl"    column="sl"    />
        <result property="pfjezh"    column="pfjezh"    />
        <result property="ysyjezh"    column="ysyjezh"    />
        <result property="sprName"    column="spr_name"    />
        <result property="spzt"    column="spzt"    />
        <result property="fyzje"    column="fyzje"    />

    </resultMap>

    <resultMap id="SjFybpSjFybpWhResult" type="SjFybp" extends="SjFybpResult">
        <collection property="sjFybpWhList" notNullColumn="sub_id" javaType="java.util.List" resultMap="SjFybpWhResult" />
    </resultMap>

    <resultMap type="SjFybpWh" id="SjFybpWhResult">
        <result property="id"    column="sub_id"    />
        <result property="fybpId"    column="sub_fybp_id"    />
        <result property="syrq"    column="sub_syrq"    />
        <result property="syje"    column="sub_syje"    />
        <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"    />
    </resultMap>

    <sql id="selectSjFybpVo">
        select id,
               zb_id
               fymc,
               fyrq,
               a.dept_id,
               fylx,
               fyms,
               fyje,
               fjid,
               zt,
               tjr,
               tjrq,
               sprq,
               spr,
               pfje,
               ysyje,
               spyj,
               a.create_by,
               a.create_time,
               a.update_by,
               a.update_time,
               a.remark,
               yl1,
               yl2,
               yl3,
               yl4,
               yl5,
               d.dept_name,
               u.user_name spr_name
        from sj_fybp a
                 left join  sys_dept d on a.dept_id=d.dept_id
                 left join sys_user u on a.spr=u.user_name
                 </sql>

    <select id="selectSjFybpList" parameterType="SjFybp" resultMap="SjFybpResult">
        <include refid="selectSjFybpVo"/>
        <where>  
            <if test="fymc != null  and fymc != ''"> and fymc like concat('%', #{fymc}, '%')</if>
            <if test="zbId != null "> and zb_id = #{zbId}</if>
            <if test="fyrq != null  and fyrq != ''"> and fyrq = #{fyrq}</if>
            <if test="deptId != null  and deptId != ''"> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
            <if test="fylx != null  and fylx != ''"> and fylx = #{fylx}</if>
            <if test="fyms != null  and fyms != ''"> and fyms = #{fyms}</if>
            <if test="fyje != null "> and fyje = #{fyje}</if>
            <if test="fjid != null  and fjid != ''"> and fjid = #{fjid}</if>
            <if test="zt != null  and zt != ''"> and zt = #{zt}</if>
            <if test="tjr != null  and tjr != ''"> and tjr = #{tjr}</if>
            <if test="tjrq != null  and tjrq != ''"> and tjrq = #{tjrq}</if>
            <if test="sprq != null  and sprq != ''"> and sprq = #{sprq}</if>
            <if test="spr != null  and spr != ''"> and spr = #{spr}</if>
            <if test="pfje != null "> and pfje = #{pfje}</if>
            <if test="ysyje != null "> and ysyje = #{ysyje}</if>
            <if test="spyj != null  and spyj != ''"> and spyj = #{spyj}</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="spcx != null  and spcx != '' and spcx=='1'.toString()"> and zt !='未提交'</if>
            <if test="spcx != null  and spcx != '' and spcx=='2'.toString()"> and zt ='已批复'</if>
            <if test="startTime != null  and startTime != ''"> and a.fyrq >=#{startTime}</if>
            <if test="endTime != null  and endTime != ''"> and a.fyrq &lt;= #{endTime}</if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>

    </select>
    
    <select id="selectSjFybpById" parameterType="Long" resultMap="SjFybpResult">
        select a.id, a.fymc, a.fyrq, a.dept_id,
               a.fylx, a.fyms, a.fyje, a.fjid, a.zt,
               a.tjr, a.tjrq, a.sprq, a.spr, a.pfje,
               a.ysyje, a.spyj, a.create_by,
               a.create_time, a.update_by, a.update_time,
               a.remark, a.yl1, a.yl2, a.yl3, a.yl4, a.yl5,d.dept_name, a.zb_id
        from sj_fybp a
                 left join  sys_dept d on a.dept_id=d.dept_id
        where a.id = #{id}
    </select>


    <select id="selectSjFybpByZbId" resultMap="SjFybpResult">
        select a.id, a.fymc, a.fyrq, a.dept_id,
               a.fylx, a.fyms, a.fyje, a.fjid, a.zt,
               a.tjr, a.tjrq, a.sprq, a.spr, a.pfje,
               a.ysyje, a.spyj, 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.zb_id
        from sj_fybp a

        where a.zb_id = #{zbid}
    </select>
    <select id="getFygz" resultMap="SjFybpResult">
        select  a.id zb_id,a.dept_id,d.dept_name,a.fyrq,a.fyje fyzje,b.id,b.fymc,b.fylx,b.fyje,b.fyms,IFNULL(sum(c.syje),0) ysyje from sj_fybp_zb a
        left join  sj_fybp b on a.id=b.zb_id
        left join  sj_fybp_wh c on b.id=c.fybp_id
        left join  sys_dept d on a.dept_id=d.dept_id
        where 1=1
        and a.zt = '已批复'
        <if test="deptId != null  and deptId != ''"> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
        <if test="fylx != null  and fylx != ''"> and b.fylx = #{fylx}</if>
        <if test="fymc != null  and fymc != ''"> and b.fymc like concat('%', #{fymc}, '%')</if>
        <if test="fyrq != null  and fyrq != ''"> and a.fyrq = #{fyrq}</if>
        ${params.dataScope}
        and b.fyje != 0
        group by a.id,a.dept_id,d.dept_name,a.fyrq,a.fyje,b.id,b.fymc,b.fylx,b.fyje,b.fyms
         order by a.sprq desc,a.dept_id,b.id
    </select>
    <select id="getFygzById" resultMap="SjFybpResult">
        select  a.id zb_id,a.dept_id,d.dept_name,a.fyrq,a.fyje fyzje,b.id,b.fymc,b.fylx,b.fyje,b.fyms,IFNULL(sum(c.syje),0) ysyje from sj_fybp_zb a
        left join  sj_fybp b on a.id=b.zb_id
        left join  sj_fybp_wh c on b.id=c.fybp_id
        left join  sys_dept d on a.dept_id=d.dept_id
        where 1=1
         and b.id=#{id}
        group by  a.id,a.dept_id,d.dept_name,a.fyrq,a.fyje,b.id,b.fymc,b.fylx,b.fyje,b.fyms
    </select>
    <select id="tjtbByfylx" resultType="com.qianhe.domain.Vo.SjfybpVo">
        select
        b.fylx,IFNULL(count(b.id),0) zsl,IFNULL(sum(b.fyje),0) xmje,  IFNULL(sum(b.ysyje ), 0) ysyje,e.dict_label fylxmc
        from sj_fybp_zb a
        left join sj_fybp b on a.id = b.zb_id

        left join sys_dept d on a.dept_id=d.dept_id
        left join sys_dict_data e on b.fylx=e.dict_value and e.dict_type='fy_lx'
        where  1=1
        and  a.zt = '已批复'
        <if test="deptId != null  and deptId != ''"> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
        <if test="fyrq != null  and fyrq != ''"> and a.fyrq =#{fyrq}</if>
        <if test="fylx != null  and fylx != ''"> and b.fylx = #{fylx}</if>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
        group by b.fylx,e.dict_label
    </select>
    <select id="tjtbByDept" resultType="com.qianhe.domain.Vo.SjfybpVo">
        select
        a.dept_id,d.dept_name,a.id,a.fyrq,IFNULL(count(b.id),0) zsl,IFNULL(sum(b.fyje),0) xmje,  IFNULL(sum(b.ysyje ), 0) ysyje
        from sj_fybp_zb a
        left join sj_fybp b on a.id = b.zb_id

        left join sys_dept d on a.dept_id=d.dept_id
                                    where 1=1
                                    and a.zt = '已批复'
        <if test="deptId != null  and deptId != ''"> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
        <if test="fyrq != null  and fyrq != ''"> and a.fyrq =#{fyrq}</if>
        <if test="fylx != null  and fylx != ''"> and b.fylx = #{fylx}</if>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
        group by a.dept_id,d.dept_name,a.id,a.fyrq
    </select>
    <select id="selectSjFybpCtList" resultMap="SjFybpResult">
        select
            a.dept_id,b.id,b.zb_id,b.fymc,b.fylx,a.fyrq,b.fyje,b.fyms,d.dept_name,b.ysyje
        from sj_fybp_zb a left join
             sj_fybp b on a.id=b.zb_id
                          left join sys_dept d on a.dept_id=d.dept_id
        where 1=1
        <if test="deptId != null  and deptId != ''"> and (a.dept_id = #{deptId} or  find_in_set(#{deptId},d.ancestors))</if>
        <if test="fyrq != null  and fyrq != ''"> and a.fyrq =#{fyrq}</if>
        <if test="fylx != null  and fylx != ''"> and b.fylx = #{fylx}</if>
        <!-- 数据范围过滤 -->
        ${params.dataScope}
    </select>

    <insert id="insertSjFybp" parameterType="SjFybp" useGeneratedKeys="true" keyProperty="id">
        insert into sj_fybp
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="fymc != null">fymc,</if>
            <if test="fyrq != null">fyrq,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="fylx != null">fylx,</if>
            <if test="fyms != null">fyms,</if>
            <if test="fyje != null">fyje,</if>
            <if test="fjid != null">fjid,</if>
            <if test="zt != null">zt,</if>
            <if test="tjr != null">tjr,</if>
            <if test="sprq != null">sprq,</if>
            <if test="spr != null">spr,</if>
            <if test="pfje != null">pfje,</if>
            <if test="ysyje != null">ysyje,</if>
            <if test="spyj != null">spyj,</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="tjrq != null">tjrq,</if>
            <if test="zbId != null">zb_id,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="fymc != null">#{fymc},</if>
            <if test="fyrq != null">#{fyrq},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="fylx != null">#{fylx},</if>
            <if test="fyms != null">#{fyms},</if>
            <if test="fyje != null">#{fyje},</if>
            <if test="fjid != null">#{fjid},</if>
            <if test="zt != null">#{zt},</if>
            <if test="tjr != null">#{tjr},</if>
            <if test="sprq != null">#{sprq},</if>
            <if test="spr != null">#{spr},</if>
            <if test="pfje != null">#{pfje},</if>
            <if test="ysyje != null">#{ysyje},</if>
            <if test="spyj != null">#{spyj},</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="tjrq != null">#{tjrq},</if>
            <if test="zbId != null">#{zbId},</if>
         </trim>
    </insert>

    <update id="updateSjFybp" parameterType="SjFybp">
        update sj_fybp
        <trim prefix="SET" suffixOverrides=",">
            <if test="fymc != null">fymc = #{fymc},</if>
            <if test="fyrq != null">fyrq = #{fyrq},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="fylx != null">fylx = #{fylx},</if>
            <if test="fyms != null">fyms = #{fyms},</if>
            <if test="fyje != null">fyje = #{fyje},</if>
            <if test="fjid != null">fjid = #{fjid},</if>
            <if test="zt != null">zt = #{zt},</if>
            <if test="tjr != null">tjr = #{tjr},</if>
            <if test="sprq != null">sprq = #{sprq},</if>
            <if test="spr != null">spr = #{spr},</if>
            <if test="pfje != null">pfje = #{pfje},</if>
            <if test="ysyje != null">ysyje = #{ysyje},</if>
            <if test="spyj != null">spyj = #{spyj},</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="tjrq != null">tjrq = #{tjrq},</if>
            <if test="zbId != null">zb_id = #{zbId},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSjFybpById" parameterType="Long">
        delete from sj_fybp where id = #{id}
    </delete>

    <delete id="deleteSjFybpByIds" parameterType="String">
        delete from sj_fybp where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    
    <delete id="deleteSjFybpWhByFybpIds" parameterType="String">
        delete from sj_fybp_wh where fybp_id in 
        <foreach item="fybpId" collection="array" open="(" separator="," close=")">
            #{fybpId}
        </foreach>
    </delete>

    <delete id="deleteSjFybpWhByFybpId" parameterType="Long">
        delete from sj_fybp_wh where fybp_id = #{fybpId}
    </delete>

    <insert id="batchSjFybpWh">
        insert into sj_fybp_wh( id, fybp_id, syrq, syje, remark, yl1, yl2, yl3, yl4, yl5) values
		<foreach item="item" index="index" collection="list" separator=",">
            ( #{item.id}, #{item.fybpId}, #{item.syrq}, #{item.syje}, #{item.remark}, #{item.yl1}, #{item.yl2}, #{item.yl3}, #{item.yl4}, #{item.yl5})
        </foreach>
    </insert>
</mapper>