<?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.FmsPeriodMapper">
    
    <resultMap type="FmsPeriod" id="FmsPeriodResult">
        <result property="periodId"    column="periodId"    />
        <result property="cropId"    column="cropId"    />
        <result property="periodName"    column="periodName"    />
        <result property="periodDays"    column="periodDays"    />
        <result property="periodRemark"    column="periodRemark"    />
    </resultMap>

    <resultMap type="FmsCrop" id="FmsCropResult">
        <result property="cropId"    column="cropId"    />
        <result property="cropName"    column="cropName"    />
        <result property="cropType"    column="cropType"    />
    </resultMap>


    <sql id="selectFmsPeriodVo">
        select periodId, cropId, periodName, periodDays, periodRemark, periodSeq,
        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = periodId) periodPreview,
        (select GROUP_CONCAT(resId) from SysRes r where r.resBusId = periodId) resIds
        from FmsPeriod
    </sql>

    <select id="selectFmsPeriodList" parameterType="FmsPeriod" resultMap="FmsPeriodResult">
        <include refid="selectFmsPeriodVo"/>
        <where>  
            <if test="cropId != null  and cropId != ''"> and cropId = #{cropId}</if>
            <if test="periodName != null  and periodName != ''"> and periodName like concat('%', #{periodName}, '%')</if>
            <if test="periodDays != null "> and periodDays = #{periodDays}</if>
            <if test="periodRemark != null  and periodRemark != ''"> and periodRemark = #{periodRemark}</if>
        </where>
        order by periodSeq
    </select>
    
    <select id="selectFmsPeriodByPeriodId" parameterType="String" resultMap="FmsPeriodResult">
        <include refid="selectFmsPeriodVo"/>
        where periodId = #{periodId}
    </select>

    <select id="selectFmsPeriodByCropId" parameterType="String" resultMap="FmsPeriodResult">
        <include refid="selectFmsPeriodVo"/>
        where cropId = #{cropId}
        order by periodSeq
    </select>

    <select id="selectFmsCropHasPeriod" parameterType="String" resultMap="FmsCropResult">
        SELECT
        c.*
        FROM
        FmsPeriod p
        LEFT JOIN FmsCrop c ON c.cropId = p.cropId
        where c.cropId is not null
        GROUP BY p.cropId;
    </select>


    <insert id="insertFmsPeriod" parameterType="FmsPeriod">
        insert into FmsPeriod
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="periodId != null">periodId,</if>
            <if test="cropId != null">cropId,</if>
            <if test="periodName != null">periodName,</if>
            <if test="periodDays != null">periodDays,</if>
            <if test="periodRemark != null">periodRemark,</if>
            <if test="periodSeq != null">periodSeq,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="periodId != null">#{periodId},</if>
            <if test="cropId != null">#{cropId},</if>
            <if test="periodName != null">#{periodName},</if>
            <if test="periodDays != null">#{periodDays},</if>
            <if test="periodRemark != null">#{periodRemark},</if>
            <if test="periodSeq != null">#{periodSeq},</if>
         </trim>
    </insert>

    <insert id="insertFmsPeriodBatch" parameterType="FmsPeriod">
        insert into FmsPeriod
        <trim prefix="(" suffix=")" suffixOverrides=",">
            periodId,
            cropId,
            periodName,
            periodDays,
            periodRemark,
            periodSeq,
        </trim>
        values
        <foreach item="data" collection="list" separator=",">
            (
                #{data.periodId},
                #{data.cropId},
                #{data.periodName},
                #{data.periodDays},
                #{data.periodRemark},
                #{data.periodSeq}
            )
        </foreach>
    </insert>


    <update id="updateFmsPeriod" parameterType="FmsPeriod">
        update FmsPeriod
        <trim prefix="SET" suffixOverrides=",">
            <if test="cropId != null">cropId = #{cropId},</if>
            <if test="periodName != null">periodName = #{periodName},</if>
            <if test="periodDays != null">periodDays = #{periodDays},</if>
            <if test="periodRemark != null">periodRemark = #{periodRemark},</if>
            <if test="periodSeq != null">periodSeq = #{periodSeq},</if>
        </trim>
        where periodId = #{periodId}
    </update>

    <delete id="deleteFmsPeriodByPeriodId" parameterType="String">
        delete from FmsPeriod where periodId = #{periodId}
    </delete>

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

    <delete id="deleteFmsPeriodByCropId" parameterType="String">
        delete from FmsPeriod where cropId = #{cropId}
    </delete>
</mapper>