<?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.WdWdwhMapper">
    
    <resultMap type="WdWdwh" id="WdWdwhResult">
        <result property="id"    column="id"    />
        <result property="year"    column="year"    />
        <result property="typeId"    column="type_id"    />
        <result property="typeName"    column="type_name"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />
        <result property="fjId"    column="fj_id"    />
        <result property="lrr"    column="lrr"    />
        <result property="lrsj"    column="lrsj"    />
        <result property="xgr"    column="xgr"    />
        <result property="xgsj"    column="xgsj"    />
        <result property="bz"    column="bz"    />
        <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="fjmc"    column="fjmc"    />
        <result property="scsj"    column="scsj"    />
        <result property="scr"    column="scr"    />
        <result property="fjdz"    column="fjdz"    />
        <result property="ejdeptId"    column="ejdept_id"    />
        <result property="ejdeptName"    column="ejdept_name"    />
        <result property="num"    column="num"    />

    </resultMap>

    <sql id="selectWdWdwhVo">
        select a.id, year,
            type_id, type_name,
            a.dept_id, a.dept_name,
            fj_id, a.lrr, a.lrsj,
            a.xgr, a.xgsj, a.bz, a.yl1, a.yl2, a.yl3,
            a.yl4, a.yl5, ejdept_id, ejdept_name
        from wd_wdwh a
            left join sys_dept d on a.dept_id = d.dept_id
    </sql>

    <select id="selectWdWdwhList" parameterType="WdWdwh" resultMap="WdWdwhResult">
        <include refid="selectWdWdwhVo"/>
        <where>  
            <if test="year != null  and year != ''"> and year = #{year}</if>
            <if test="typeId != null  and typeId != ''"> and type_id = #{typeId}</if>
            <if test="typeName != null  and typeName != ''"> and type_name like concat('%', #{typeName}, '%')</if>
            <if test="deptId != null  and deptId != ''"> and (d.dept_id = #{deptId}  or find_in_set(#{deptId},ancestors)) </if>
            <if test="deptName != null  and deptName != ''"> and dept_name like concat('%', #{deptName}, '%')</if>
            <if test="fjId != null  and fjId != ''"> and fj_id = #{fjId}</if>
            <if test="lrr != null  and lrr != ''"> and a.lrr = #{lrr}</if>
            <if test="lrsj != null "> and a.lrsj = #{lrsj}</if>
            <if test="xgr != null  and xgr != ''"> and a.xgr = #{xgr}</if>
            <if test="xgsj != null "> and a.xgsj = #{xgsj}</if>
            <if test="bz != null  and bz != ''"> and a.bz = #{bz}</if>
            <if test="yl1 != null  and yl1 != ''"> and a.yl1 = #{yl1}</if>
            <if test="yl2 != null  and yl2 != ''"> and a.yl2 = #{yl2}</if>
            <if test="yl3 != null  and yl3 != ''"> and a.yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and a.yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and a.yl5 = #{yl5}</if>
            <if test="ejdeptId != null  and ejdeptId != ''"> and ejdept_id = #{ejdeptId}</if>
            <if test="ejdeptName != null  and ejdeptName != ''"> and ejdept_name like concat('%', #{ejdeptName}, '%')</if>
            ${params.dataScope}
        </where>

    </select>
    
    <select id="selectWdWdwhById" parameterType="Long" resultMap="WdWdwhResult">
       select * from wd_wdwh
        where id = #{id}
    </select>
    <select id="getListBySjDw" resultMap="WdWdwhResult">
        select
            a.id,
            a.dept_id,
            a.dept_name,
            a.year
        from wd_wdwh  a
                 left join wd_lxwh b on a.type_id=b.id
        left join sys_dept d on a.dept_id = d.dept_id
        where 1=1
        <if test="year != null  and year != ''"> and year = #{year}</if>
        <if test="deptId != null  and deptId != ''"> and (d.dept_id = #{deptId}  or find_in_set(#{deptId},ancestors)) </if>
        group by a.dept_id,year order by a.dept_id,year
    </select>
    <select id="getListBySjDwByType" resultType="java.util.Map">
        select
            a.id,
            a.year year,
            a.dept_id deptId,
            a.type_id typeId,
            a.type_name typeName,
            count(xx.id) num
        from wd_wdwh  a
                 left join wd_lxwh b on a.type_id=b.id
        left join sys_dept d on a.dept_id = d.dept_id
        left join wd_wdxx xx on a.id=xx.gl_id
        <if test="year != null  and year != ''"> and year = #{year}</if>
        <if test="deptId != null  and deptId != ''"> and (d.dept_id = #{deptId}  or find_in_set(#{deptId},ancestors)) </if>
        group by a.dept_id,year,type_id
    </select>
    <select id="getListByEjDw" resultMap="WdWdwhResult">
        select
        a.id,
        a.ejdept_id ,
        a.ejdept_name,
        a.year
        from wd_wdwh  a
        left join wd_lxwh b on a.type_id=b.id
        left join sys_dept d on a.ejdept_id = d.dept_id
        where 1=1
        <if test="year != null  and year != ''"> and year = #{year}</if>
        <if test="deptId != null  and deptId != ''"> and (d.dept_id = #{deptId}  or find_in_set(#{deptId},ancestors)) </if>
        group by a.ejdept_id,year order by a.ejdept_id,year
    </select>
    <select id="getListByEjDwByType" resultType="java.util.Map">
        select
        a.id,
        a.year year,
        a.ejdept_id ejdeptId,
        a.type_id typeId,
        a.type_name typeName,
        count(xx.id) num
        from wd_wdwh  a
        left join wd_lxwh b on a.type_id=b.id
        left join sys_dept d on a.ejdept_id = d.dept_id
        left join wd_wdxx xx on a.id=xx.gl_id
        <if test="year != null  and year != ''"> and year = #{year}</if>
        <if test="deptId != null  and deptId != ''"> and (d.dept_id = #{deptId}  or find_in_set(#{deptId},ancestors)) </if>
        group by a.ejdept_id,year,type_id
    </select>
    <select id="getFileList" resultType="com.qianhe.domain.WdWdxx">
        select
            a.*
        from wd_wdxx a
                 left join wd_wdwh b on a.gl_id=b.id
        where 1=1
        <if test="typeName != null  and typeName != ''"> and type_name =#{typeName}</if>
        <if test="year != null  and year != ''"> and year = #{year}</if>
        <if test="deptId != null  and deptId != ''"> and dept_id = #{deptId} </if>
        <if test="ejdeptId != null  and ejdeptId != ''"> and ejdept_id = #{ejdeptId}</if>
    </select>


    <insert id="insertWdWdwh" parameterType="WdWdwh" useGeneratedKeys="true" keyProperty="id">
        insert into wd_wdwh
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="year != null">year,</if>
            <if test="typeId != null">type_id,</if>
            <if test="typeName != null">type_name,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="deptName != null">dept_name,</if>
            <if test="fjId != null">fj_id,</if>
            <if test="lrr != null">lrr,</if>
            <if test="lrsj != null">lrsj,</if>
            <if test="xgr != null">xgr,</if>
            <if test="xgsj != null">xgsj,</if>
            <if test="bz != null">bz,</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="ejdeptId != null">ejdept_id,</if>
            <if test="ejdeptName != null">ejdept_name,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="year != null">#{year},</if>
            <if test="typeId != null">#{typeId},</if>
            <if test="typeName != null">#{typeName},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="deptName != null">#{deptName},</if>
            <if test="fjId != null">#{fjId},</if>
            <if test="lrr != null">#{lrr},</if>
            <if test="lrsj != null">#{lrsj},</if>
            <if test="xgr != null">#{xgr},</if>
            <if test="xgsj != null">#{xgsj},</if>
            <if test="bz != null">#{bz},</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="ejdeptId != null">#{ejdeptId},</if>
            <if test="ejdeptName != null">#{ejdeptName},</if>
         </trim>
    </insert>

    <update id="updateWdWdwh" parameterType="WdWdwh">
        update wd_wdwh
        <trim prefix="SET" suffixOverrides=",">
            <if test="year != null">year = #{year},</if>
            <if test="typeId != null">type_id = #{typeId},</if>
            <if test="typeName != null">type_name = #{typeName},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="deptName != null">dept_name = #{deptName},</if>
            <if test="fjId != null">fj_id = #{fjId},</if>
            <if test="lrr != null">lrr = #{lrr},</if>
            <if test="lrsj != null">lrsj = #{lrsj},</if>
            <if test="xgr != null">xgr = #{xgr},</if>
            <if test="xgsj != null">xgsj = #{xgsj},</if>
            <if test="bz != null">bz = #{bz},</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="ejdeptId != null">ejdept_id = #{ejdeptId},</if>
            <if test="ejdeptName != null">ejdept_name = #{ejdeptName},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteWdWdwhById" parameterType="Long">
        delete from wd_wdwh where id = #{id}
    </delete>

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