<?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.ruoyi.system.mapper.fgw.FgwDbdMapper">
    
    <resultMap type="FgwDbd" id="FgwDbdResult">
        <result property="id"    column="id"    />
        <result property="xmId"    column="xm_id"    />
        <result property="xmmc"    column="xmmc"    />
        <result property="dbnrName"    column="dbnr_name"    />
        <result property="deptId"    column="dept_id"    />
        <result property="deptName"    column="dept_name"    />
        <result property="dbdj"    column="dbdj"    />
        <result property="yqwcsj"    column="yqwcsj"    />
        <result property="cqts"    column="cqts"    />
        <result property="isWc"    column="is_wc"    />
        <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="type"    column="type"    />

    </resultMap>

    <sql id="selectFgwDbdVo">
        select id,  xm_id, xmmc, dbnr_name, dept_id, dept_name, dbdj, yqwcsj, cqts, is_wc, create_by, create_time, update_by, update_time, remark from fgw_dbd
    </sql>

    <select id="selectFgwDbdList" parameterType="FgwDbd" resultMap="FgwDbdResult">
        SELECT
        id,
        xm_id,
        xmmc,
        dbnr_name,
        dept_id,
        dept_name,
        dbdj,
        yqwcsj,
        (CASE
        when  DATEDIFF(NOW(),yqwcsj)&gt;0 THEN DATEDIFF(NOW(),yqwcsj)
        when  DATEDIFF(NOW(),yqwcsj)&lt;=0 THEN 0
        END)cqts,

        (CASE
        when  DATEDIFF(NOW(),yqwcsj)&lt;=0  THEN 0
        when  DATEDIFF(NOW(),yqwcsj)&gt;0 and DATEDIFF(NOW(),yqwcsj)&lt;=15 THEN 1
        when  DATEDIFF(NOW(),yqwcsj)&gt;15 THEN 2
        END)type,
        is_wc,
        create_by,
        create_time,
        update_by,
        update_time,
        remark
        FROM
        fgw_dbd
        <where>
            <if test="xmId != null "> and xm_id = #{xmId}</if>
            <if test="xmmc != null  and xmmc != ''"> and xmmc = #{xmmc}</if>
            <if test="dbnrName != null  and dbnrName != ''"> and dbnr_name like concat('%', #{dbnrName}, '%')</if>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="deptName != null  and deptName != ''"> and dept_name like concat('%', #{deptName}, '%')</if>
            <if test="dbdj != null  and dbdj != ''"> and dbdj = #{dbdj}</if>
            <if test="yqwcsj != null "> and yqwcsj = #{yqwcsj}</if>
            <if test="cqts != null "> and cqts = #{cqts}</if>
            <if test="isWc != null  and isWc != ''"> and is_wc = #{isWc}</if>
        </where>
    </select>
    
    <select id="selectFgwDbdById" parameterType="Long" resultMap="FgwDbdResult">
        SELECT
            id,
            xm_id,
            xmmc,
            dbnr_name,
            dept_id,
            dept_name,
            dbdj,
            yqwcsj,
            (CASE
                 when  DATEDIFF(NOW(),yqwcsj)&gt;0 THEN DATEDIFF(NOW(),yqwcsj)
                 when  DATEDIFF(NOW(),yqwcsj)&lt;=0 THEN 0
                END)cqts,

            (CASE
                 when  DATEDIFF(NOW(),yqwcsj)&lt;=0  THEN 0
                 when  DATEDIFF(NOW(),yqwcsj)&gt;0 and DATEDIFF(NOW(),yqwcsj)&lt;=15 THEN 1
                 when  DATEDIFF(NOW(),yqwcsj)&gt;15 THEN 2
                END)type,
            is_wc,
            create_by,
            create_time,
            update_by,
            update_time,
            remark
        FROM
            fgw_dbd
        where id = #{id}
    </select>
    <select id="selectFgwDbdYjxmNum" resultMap="FgwDbdResult">
        SELECT
        xm_id
        FROM
        fgw_dbd
        WHERE
        DATEDIFF(NOW(), yqwcsj) &gt;= - 3
        AND DATEDIFF(NOW(), yqwcsj) &lt;=0
        and is_wc = 'N'
        GROUP BY
        xm_id
    </select>
    <select id="selectFgwDbdCqxmNum" resultMap="FgwDbdResult">
        SELECT
            xm_id
        FROM
            fgw_dbd
        WHERE
            DATEDIFF(NOW(), yqwcsj) &gt;0
          and is_wc = 'N'
        GROUP BY
            xm_id
    </select>

    <insert id="insertFgwDbd" parameterType="FgwDbd" useGeneratedKeys="true" keyProperty="id">
        insert into fgw_dbd
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="xmId != null">xm_id,</if>
            <if test="xmmc != null and xmmc != ''">xmmc,</if>
            <if test="dbnrName != null and dbnrName != ''">dbnr_name,</if>
            <if test="deptId != null">dept_id,</if>
            <if test="deptName != null and deptName != ''">dept_name,</if>
            <if test="dbdj != null and dbdj != ''">dbdj,</if>
            <if test="yqwcsj != null">yqwcsj,</if>
            <if test="cqts != null">cqts,</if>
            <if test="isWc != null and isWc != ''">is_wc,</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="xmId != null">#{xmId},</if>
            <if test="xmmc != null and xmmc != ''">#{xmmc},</if>
            <if test="dbnrName != null and dbnrName != ''">#{dbnrName},</if>
            <if test="deptId != null">#{deptId},</if>
            <if test="deptName != null and deptName != ''">#{deptName},</if>
            <if test="dbdj != null and dbdj != ''">#{dbdj},</if>
            <if test="yqwcsj != null">#{yqwcsj},</if>
            <if test="cqts != null">#{cqts},</if>
            <if test="isWc != null and isWc != ''">#{isWc},</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="updateFgwDbd" parameterType="FgwDbd">
        update fgw_dbd
        <trim prefix="SET" suffixOverrides=",">
            <if test="xmId != null">xm_id = #{xmId},</if>
            <if test="xmmc != null and xmmc != ''">xmmc = #{xmmc},</if>
            <if test="dbnrName != null and dbnrName != ''">dbnr_name = #{dbnrName},</if>
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="deptName != null and deptName != ''">dept_name = #{deptName},</if>
            <if test="dbdj != null and dbdj != ''">dbdj = #{dbdj},</if>
            <if test="yqwcsj != null">yqwcsj = #{yqwcsj},</if>
            <if test="cqts != null">cqts = #{cqts},</if>
            <if test="isWc != null and isWc != ''">is_wc = #{isWc},</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>
    <update id="updateFgwDbdByDbnrId">
        update fgw_dbd set is_wc = 'Y' ,update_time = sysdate() where xm_id = #{xmId} and dbnr_id = #{type}
    </update>

    <delete id="deleteFgwDbdById" parameterType="Long">
        delete from fgw_dbd where id = #{id}
    </delete>

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