<?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.JygxWzglMapper">

    <resultMap type="JygxWzgl" id="JygxWzglResult">
        <result property="wzid"    column="wzid"    />
        <result property="px"    column="px"    />
        <result property="xxly"    column="xxly"    />
        <result property="lxid"    column="lxid"    />
        <result property="lxmc"    column="lxmc"    />
        <result property="wznr"    column="wznr"    />
        <result property="wzbt"    column="wzbt"    />
        <result property="shzt"    column="shzt"    />
        <result property="fjmc"    column="fjmc"    />
        <result property="fjdz"    column="fjdz"    />
        <result property="fjlx"    column="fjlx"    />
        <result property="wzzz"    column="wzzz"    />
        <result property="wzsj"    column="wzsj"    />
        <result property="deptId"    column="dept_id"    />
        <result property="yl2"    column="yl2"    />
        <result property="yl3"    column="yl3"    />
        <result property="yl4"    column="yl4"    />
        <result property="yl5"    column="yl5"    />
        <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="ydsl"    column="ydsl"    />

        <result property="mkmc"    column="mkmc"    />
    </resultMap>
    <resultMap type="Ssfl" id="SsflResult">
        <result property="lxmc"    column="lxmc"    />
        <result property="ts"    column="ts"    />
    </resultMap>

    <sql id="selectJygxWzglVo">
            select d.wzid, d.px, d.xxly, d.lxid, d.lxmc, d.wznr, d.wzbt, d.shzt, d.fjmc, d.fjdz, d.fjlx, d.wzzz, d.wzsj, d.dept_id,
            d.yl2, d.yl3, d.yl4, d.yl5, d.create_by, d.create_time, d.update_by, d.update_time, d.remark, d.ydsl, l.mkmc
            from jygx_wzgl d
            left join jygx_lxwh l on d.lxmc = l.lxmc
         </sql>

    <select id="selectJygxWzglList" parameterType="JygxWzgl" resultMap="JygxWzglResult">
        <include refid="selectJygxWzglVo"/>
        <where>
            <if test="px != null "> and d.px = #{px}</if>
            <if test="xxly != null  and xxly != ''"> and d.xxly = #{xxly}</if>
            <if test="lxmc != null  and lxmc != ''"> and d.lxmc = #{lxmc}</if>
            <if test="wznr != null  and wznr != ''"> and d.wznr = #{wznr}</if>
            <if test="wzbt != null  and wzbt != ''"> and d.wzbt like concat('%', #{wzbt}, '%')</if>
            <if test="shzt != null  and shzt != ''"> and d.shzt = #{shzt}</if>
            <if test="fjmc != null  and fjmc != ''"> and d.fjmc = #{fjmc}</if>
            <if test="fjdz != null  and fjdz != ''"> and d.fjdz = #{fjdz}</if>
            <if test="fjlx != null  and fjlx != ''"> and d.fjlx = #{fjlx}</if>
            <if test="wzzz != null  and wzzz != ''"> and d.wzzz = #{wzzz}</if>
            <if test="wzsj != null "> and d.wzsj = #{wzsj}</if>
            <if test="deptId != null  and deptId != ''"> and d.dept_id = #{deptId}</if>
            <if test="yl2 != null  and yl2 != ''"> and d.yl2 = #{yl2}</if>
            <if test="yl3 != null  and yl3 != ''"> and d.yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and d.yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and d.yl5 = #{yl5}</if>
            <if test="ydsl != null "> and d.ydsl = #{ydsl}</if>
            and d.lxmc != "syxw"
        </where>

        <!-- 数据范围过滤 -->
        ${params.dataScope}
        ORDER BY ifnull (d.px,999999), d.wzsj desc
    </select>

    <select id="selectList" parameterType="JygxWzgl" resultMap="JygxWzglResult">
        <include refid="selectJygxWzglVo"/>
        <where>
            <if test="px != null "> and px = #{px}</if>
            <if test="xxly != null  and xxly != ''"> and d.xxly = #{xxly}</if>
            <if test="lxmc != null  and lxmc != ''"> and d.lxmc = #{lxmc}</if>
            <if test="wznr != null  and wznr != ''"> and d.wznr = #{wznr}</if>
            <if test="wzbt != null  and wzbt != ''"> and d.wzbt = #{wzbt}</if>
            <if test="shzt != null  and shzt != ''"> and d.shzt = #{shzt}</if>
            <if test="fjmc != null  and fjmc != ''"> and d.fjmc = #{fjmc}</if>
            <if test="fjdz != null  and fjdz != ''"> and d.fjdz = #{fjdz}</if>
            <if test="fjlx != null  and fjlx != ''"> and d.fjlx = #{fjlx}</if>
            <if test="wzzz != null  and wzzz != ''"> and d.wzzz = #{wzzz}</if>
            <if test="wzsj != null "> and d.wzsj = #{wzsj}</if>
            <if test="deptId != null  and deptId != ''"> and d.dept_id = #{deptId}</if>
            <if test="yl2 != null  and yl2 != ''"> and d.yl2 = #{yl2}</if>
            <if test="yl3 != null  and yl3 != ''"> and d.yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and d.yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and d.yl5 = #{yl5}</if>
            <if test="ydsl != null "> and d.ydsl = #{ydsl}</if>
            and d.lxmc != "syxw"
        </where>
        ORDER BY ifnull (d.px,999999), d.wzsj desc
    </select>


    <select id="selectSylbxwList" parameterType="JygxWzgl" resultMap="JygxWzglResult">
        <include refid="selectJygxWzglVo"/>
        <where>
            <if test="px != null "> and d.px = #{px}</if>
            <if test="xxly != null  and xxly != ''"> and d.xxly = #{xxly}</if>
            <if test="wznr != null  and wznr != ''"> and d.wznr = #{wznr}</if>
            <if test="wzbt != null  and wzbt != ''"> and d.wzbt = #{wzbt}</if>
            <if test="shzt != null  and shzt != ''"> and d.shzt = #{shzt}</if>
            <if test="fjmc != null  and fjmc != ''"> and d.fjmc = #{fjmc}</if>
            <if test="fjdz != null  and fjdz != ''"> and d.fjdz = #{fjdz}</if>
            <if test="fjlx != null  and fjlx != ''"> and d.fjlx = #{fjlx}</if>
            <if test="wzzz != null  and wzzz != ''"> and d.wzzz = #{wzzz}</if>
            <if test="wzsj != null "> and d.wzsj = #{wzsj}</if>
            <if test="deptId != null  and deptId != ''"> and d.dept_id = #{deptId}</if>
            <if test="yl2 != null  and yl2 != ''"> and d.yl2 = #{yl2}</if>
            <if test="yl3 != null  and yl3 != ''"> and d.yl3 = #{yl3}</if>
            <if test="yl4 != null  and yl4 != ''"> and d.yl4 = #{yl4}</if>
            <if test="yl5 != null  and yl5 != ''"> and d.yl5 = #{yl5}</if>
            <if test="ydsl != null "> and d.ydsl = #{ydsl}</if>
            and d.lxmc = "syxw"
        </where>
        ORDER BY ifnull (d.px,999999), d.wzsj desc
    </select>

    <select id="selectJygxWzglByWzid" parameterType="Long" resultMap="JygxWzglResult">
        <include refid="selectJygxWzglVo"/>
        where wzid = #{wzid}
    </select>

    <insert id="insertJygxWzgl" parameterType="JygxWzgl" useGeneratedKeys="true" >
        insert into jygx_wzgl
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="wzid != null">wzid,</if>
            <if test="px != null">px,</if>
            <if test="xxly != null">xxly,</if>
            <if test="lxid != null">lxid,</if>
            <if test="lxmc != null">lxmc,</if>
            <if test="wznr != null">wznr,</if>
            <if test="wzbt != null">wzbt,</if>
            <if test="shzt != null">shzt,</if>
            <if test="fjmc != null">fjmc,</if>
            <if test="fjdz != null">fjdz,</if>
            <if test="fjlx != null">fjlx,</if>
            <if test="wzzz != null">wzzz,</if>
            <if test="wzsj != null">wzsj,</if>
            <if test="deptId != null">dept_id,</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="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="ydsl != null">ydsl,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="wzid != null">#{wzid},</if>
            <if test="px != null">#{px},</if>
            <if test="xxly != null">#{xxly},</if>
            <if test="lxid != null">#{lxid},</if>
            <if test="lxmc != null">#{lxmc},</if>
            <if test="wznr != null">#{wznr},</if>
            <if test="wzbt != null">#{wzbt},</if>
            <if test="shzt != null">#{shzt},</if>
            <if test="fjmc != null">#{fjmc},</if>
            <if test="fjdz != null">#{fjdz},</if>
            <if test="fjlx != null">#{fjlx},</if>
            <if test="wzzz != null">#{wzzz},</if>
            <if test="wzsj != null">#{wzsj},</if>
            <if test="deptId != null">#{deptId},</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="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="ydsl != null">#{ydsl},</if>
         </trim>
    </insert>

    <update id="updateJygxWzgl" parameterType="JygxWzgl">
        update jygx_wzgl
        <trim prefix="SET" suffixOverrides=",">
            <if test="px != null">px = #{px},</if>
            <if test="xxly != null">xxly = #{xxly},</if>
            <if test="lxid != null">lxid = #{lxid},</if>
            <if test="lxmc != null">lxmc = #{lxmc},</if>
            <if test="wznr != null">wznr = #{wznr},</if>
            <if test="wzbt != null">wzbt = #{wzbt},</if>
            <if test="shzt != null">shzt = #{shzt},</if>
            <if test="fjmc != null">fjmc = #{fjmc},</if>
            <if test="fjdz != null">fjdz = #{fjdz},</if>
            <if test="fjlx != null">fjlx = #{fjlx},</if>
            <if test="wzzz != null">wzzz = #{wzzz},</if>
            <if test="wzsj != null">wzsj = #{wzsj},</if>
            <if test="deptId != null">dept_id = #{deptId},</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="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="ydsl != null">ydsl = #{ydsl},</if>
        </trim>
        where wzid = #{wzid}
    </update>

    <update id="updateqxzd" parameterType="JygxWzgl">
        update jygx_wzgl
        set px = NULL
        where wzid = #{wzid}
    </update>

    <update id="update" parameterType="JygxWzgl">
        update jygx_wzgl
        set ydsl = ydsl+1
        where wzid = #{wzid}
    </update>


    <update id="updatezd" parameterType="JygxWzgl">
        UPDATE jygx_wzgl
        SET px = px + 1
        WHERE lxmc = #{lxmc} and px is not null
    </update>

    <delete id="deleteJygxWzglByWzid" parameterType="Long">
        delete from jygx_wzgl where wzid = #{wzid}
    </delete>

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


    <select id="selectFile" resultType="com.qianhe.domain.JygxWzglfj">
        select *
        from jygx_wzglfj where wzid = #{wzid}
    </select>


    <insert id="insertFile" parameterType="com.qianhe.domain.JygxWzglfj">
        insert into jygx_wzglfj(fjid, wzid, file_name, accessory_url, accessory_type, create_by, create_time) values
        <foreach item="item" index="index" collection="list" separator=",">
            (0,#{item.wzid}, #{item.fileName}, #{item.accessoryUrl}, #{item.accessoryType},  #{item.createBy}, #{item.createTime})
        </foreach>
    </insert>

    <delete id="deleteFileByBusinessId">
        delete from jygx_wzglfj where wzid = #{wzid}
    </delete>

    <select id="selectcxflList" parameterType="JygxWzgl" resultMap="SsflResult">
        SELECT '全部' AS lxmc, COUNT(*) AS ts
        FROM jygx_wzgl
        <where>
            <if test="wznr != null  and wznr != ''">
                AND wznr LIKE concat('%', #{wznr}, '%') OR wzbt LIKE concat('%', #{wznr}, '%')</if>
        </where>
        UNION ALL
        SELECT
            l.lxmc,
            COALESCE(COUNT(w.lxmc), 0) AS ts
        FROM
            jygx_lxwh l
        LEFT JOIN
            jygx_wzgl w ON l.lxmc = w.lxmc
        <if test="wznr != null  and wznr != ''">
        AND w.wznr LIKE concat('%', #{wznr}, '%') OR w.wzbt LIKE concat('%', #{wznr}, '%')</if>

        GROUP BY l.lxid



    </select>

    <select id="selectsyssList" parameterType="JygxWzgl" resultMap="JygxWzglResult">
        select  * from jygx_wzgl
        <where>
            <if test="wznr != null  and wznr != ''">
                (wznr LIKE concat('%', #{wznr}, '%')  OR wzbt LIKE concat('%', #{wznr}, '%'))
            </if>
            <if test="lxmc != null  and lxmc != ''"> and (lxmc = #{lxmc})</if>
            AND (lxmc != 'syxw')
        </where>
        ORDER BY ifnull (px,999999), wzsj desc
    </select>


</mapper>
