<?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.yunfeiyun.agmp.fms.mapper.FmsWarnMapper">
    
    <resultMap type="FmsWarnResVo" id="FmsWarnResult">
        <result property="warnId"    column="warnId"    />
        <result property="periodId"    column="periodId"    />
        <result property="taskId"    column="taskId"    />
        <result property="warnTitle"    column="warnTitle"    />
        <result property="warnContent"    column="warnContent"    />
        <result property="warnCreatedate"    column="warnCreatedate"    />
        <result property="warnHandler"    column="warnHandler"    />
        <result property="warnHandletime"    column="warnHandletime"    />
        <result property="warnHandlestatus"    column="warnHandlestatus"    />
        <result property="planId"    column="planId"    />

        <result property="cropId"    column="cropId"    />
        <result property="cropName"    column="cropName"    />
        <result property="cropType"    column="cropType"    />
        <result property="cropMaintype"    column="cropMaintype"    />
        <result property="periodName"    column="periodName"    />
        <result property="planNo"    column="planNo"    />
        <result property="planArea"    column="planArea"    />
        <result property="landId"    column="landId"    />
        <result property="landName"    column="landName"    />
        <result property="blockId"    column="blockId"    />
        <result property="blockName"    column="blockName"    />
        <result property="blockName"    column="blockName"    />

    </resultMap>

    <sql id="selectFmsWarnReqVo">
        SELECT w.*, c.cropId, c.cropName, c.cropType, c.cropMaintype, p.periodName, p2.planNo, p2.planArea, la.landId, la.landName, b.blockId, b.blockName, r.resUrl cropPreview
        FROM FmsWarn AS w
        JOIN FmsPeriod AS p ON w.periodId = p.periodId
        JOIN FmsCrop AS c ON p.cropId = c.cropId
        JOIN FmsPlan AS p2 ON w.planId = p2.planId
        JOIN FmsLand AS la ON p2.landId = la.landId
        JOIN FmsBlock AS b ON p2.blockId = b.blockId
        LEFT JOIN SysRes r on r.resBusId = c.cropId
    </sql>

    <select id="selectFmsWarnList" parameterType="FmsWarnReqVo" resultMap="FmsWarnResult">
        <include refid="selectFmsWarnReqVo"/>
        <where>
            <if test="periodId != null  and periodId != ''"> and w.periodId = #{periodId}</if>
            <if test="taskId != null  and taskId != ''"> and taskId = #{taskId}</if>
            <if test="warnCreatedate != null  and warnCreatedate != ''"> and warnCreatedate = #{warnCreatedate}</if>
            <if test="warnHandler != null  and warnHandler != ''"> and warnHandler = #{warnHandler}</if>
            <if test="warnHandletime != null  and warnHandletime != ''"> and warnHandletime = #{warnHandletime}</if>
            <if test="warnHandlestatus != null  and warnHandlestatus != ''"> and warnHandlestatus = #{warnHandlestatus}</if>
            <if test="planId != null  and planId != ''"> and w.planId = #{planId}</if>
            <if test="landId != null  and landId != ''"> and la.landId = #{landId}</if>
            <if test="blockId != null  and blockId != ''"> and b.blockId = #{blockId}</if>
            <if test="cropName != null  and cropName != ''"> and c.cropName like concat('%', #{cropName}, '%')</if>
            <if test="planNo != null  and planNo != ''"> and p2.planNo like concat('%', #{planNo}, '%')</if>
            <if test="startDate != null  and startDate != ''">
                and w.warnCreatedate >=  #{startDate}
            </if>
            <if test="endDate != null  and endDate != ''">
                and w.warnCreatedate <![CDATA[ <= ]]>  #{endDate}
            </if>
            <if test="dataFilter == true and blockIds != null and blockIds.size() > 0">
                AND p2.blockId in
                <foreach collection="blockIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        ORDER BY w.warnCreatedate DESC
    </select>
    
    <select id="selectFmsWarnByWarnId" parameterType="String" resultMap="FmsWarnResult">
        <include refid="selectFmsWarnReqVo"/>
        where warnId = #{warnId}
    </select>

<!--    定时任务调度中使用-->
    <select id="selectFmsWarnListByScheduler" resultType="FmsWarnBySchedulerDto">
        SELECT planId, periodId
        FROM FmsWarn
    </select>

        
    <insert id="insertFmsWarn" parameterType="FmsWarn">
        insert into FmsWarn
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="warnId != null">warnId,</if>
            <if test="periodId != null">periodId,</if>
            <if test="taskId != null">taskId,</if>
            <if test="warnTitle != null">warnTitle,</if>
            <if test="warnContent != null">warnContent,</if>
            <if test="warnCreatedate != null">warnCreatedate,</if>
            <if test="warnHandler != null">warnHandler,</if>
            <if test="warnHandletime != null">warnHandletime,</if>
            <if test="warnHandlestatus != null">warnHandlestatus,</if>
            <if test="planId != null">planId,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="warnId != null">#{warnId},</if>
            <if test="periodId != null">#{periodId},</if>
            <if test="taskId != null">#{taskId},</if>
            <if test="warnTitle != null">#{warnTitle},</if>
            <if test="warnContent != null">#{warnContent},</if>
            <if test="warnCreatedate != null">#{warnCreatedate},</if>
            <if test="warnHandler != null">#{warnHandler},</if>
            <if test="warnHandletime != null">#{warnHandletime},</if>
            <if test="warnHandlestatus != null">#{warnHandlestatus},</if>
            <if test="planId != null">#{planId},</if>
         </trim>
    </insert>

    <insert id="insertFmsWarnBatch" parameterType="FmsPlanByWarnSchedulerDto">
        insert into FmsWarn
        <trim prefix="(" suffix=")" suffixOverrides=",">
            warnId,
            planId,
            periodId,
            warnTitle,
            warnContent,
            warnCreatedate,
            warnHandlestatus,
        </trim>
        values
        <foreach item="data" collection="list" separator=",">
            (
            #{data.warnId},
            #{data.planId},
            #{data.periodId},
            #{data.warnTitle},
            #{data.warnContent},
            #{data.warnCreatedate},
            #{data.warnHandlestatus}
            )
        </foreach>
    </insert>

    <update id="updateFmsWarn" parameterType="FmsWarn">
        update FmsWarn
        <trim prefix="SET" suffixOverrides=",">
            <if test="periodId != null">periodId = #{periodId},</if>
            <if test="taskId != null">taskId = #{taskId},</if>
            <if test="warnTitle != null">warnTitle = #{warnTitle},</if>
            <if test="warnContent != null">warnContent = #{warnContent},</if>
            <if test="warnCreatedate != null">warnCreatedate = #{warnCreatedate},</if>
            <if test="warnHandler != null">warnHandler = #{warnHandler},</if>
            <if test="warnHandletime != null">warnHandletime = #{warnHandletime},</if>
            <if test="warnHandlestatus != null">warnHandlestatus = #{warnHandlestatus},</if>
            <if test="planId != null">planId = #{planId},</if>
        </trim>
        where warnId = #{warnId}
    </update>

    <delete id="deleteFmsWarnByWarnId" parameterType="String">
        delete from FmsWarn where warnId = #{warnId}
    </delete>

    <delete id="deleteFmsWarnByWarnIds" parameterType="String">
        delete from FmsWarn where warnId in 
        <foreach item="warnId" collection="array" open="(" separator="," close=")">
            #{warnId}
        </foreach>
    </delete>
    <select id="selectFmsWarnResVoByBlockManager" resultType="FmsWarnResVo" parameterType="FmsWarnResVo">
        SELECT
        w.warnId,
        w.warnCreatedate,
        p.planPeriodname,
        b.blockName,
        l.landName,
        c.cropName
        FROM
        FmsWarn w
        LEFT JOIN FmsPlan p ON p.planId = w.planId
        LEFT JOIN FmsBlock b ON b.blockId = p.blockId
        LEFT JOIN FmsLand l ON l.landId = p.landId
        LEFT JOIN FmsCrop c ON c.cropId = p.cropId
        <where>
            warnHandlestatus = '1'
            <if test="dataFilter == true and blockIds != null and blockIds.size() > 0">
                AND b.blockId in
                <foreach collection="blockIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>


    </select>
</mapper>