<?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.FmsPlanMapper">
    
    <resultMap type="FmsPlan" id="FmsPlanResult">
        <result property="planId"    column="planId"    />
        <result property="planNo"    column="planNo"    />
        <result property="landId"    column="landId"    />
        <result property="blockId"    column="blockId"    />
        <result property="cropId"    column="cropId"    />
        <result property="planArea"    column="planArea"    />
        <result property="planPlanstartdate"    column="planPlanstartdate"    />
        <result property="planPlanenddate"    column="planPlanenddate"    />
        <result property="planStartdate"    column="planStartdate"    />
        <result property="planEnddate"    column="planEnddate"    />
        <result property="planStatus"    column="planStatus"    />
        <result property="planRemark"    column="planRemark"    />
        <result property="planCreator"    column="planCreator"    />
        <result property="planCreateddate"    column="planCreateddate"    />
        <result property="planModifier"    column="planModifier"    />
        <result property="planModifieddate"    column="planModifieddate"    />
        <result property="planPeriodname"    column="planPeriodname"    />

    </resultMap>

    <sql id="selectFmsPlanVo">
        select planId, planNo, landId, blockId, cropId, planArea, planPlanstartdate, planPlanenddate, planStartdate, planEnddate, planStatus, planRemark, planCreator, planCreateddate, planModifier, planModifieddate, planPeriodname from FmsPlan
    </sql>

    <select id="selectFmsPlanList" parameterType="FmsPlan" resultMap="FmsPlanResult">
        <include refid="selectFmsPlanVo"/>
        <where>  
            <if test="planNo != null  and planNo != ''"> and planNo = #{planNo}</if>
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="blockId != null  and blockId != ''"> and blockId = #{blockId}</if>
            <if test="cropId != null  and cropId != ''"> and cropId = #{cropId}</if>
            <if test="planArea != null "> and planArea = #{planArea}</if>
            <if test="planPlanstartdate != null  and planPlanstartdate != ''"> and planPlanstartdate = #{planPlanstartdate}</if>
            <if test="planPlanenddate != null  and planPlanenddate != ''"> and planPlanenddate = #{planPlanenddate}</if>
            <if test="planStartdate != null  and planStartdate != ''"> and planStartdate = #{planStartdate}</if>
            <if test="planEnddate != null  and planEnddate != ''"> and planEnddate = #{planEnddate}</if>
            <if test="planStatus != null  and planStatus != ''"> and planStatus = #{planStatus}</if>
            <if test="planRemark != null  and planRemark != ''"> and planRemark = #{planRemark}</if>
            <if test="planCreator != null  and planCreator != ''"> and planCreator = #{planCreator}</if>
            <if test="planCreateddate != null  and planCreateddate != ''"> and planCreateddate = #{planCreateddate}</if>
            <if test="planModifier != null  and planModifier != ''"> and planModifier = #{planModifier}</if>
            <if test="planModifieddate != null  and planModifieddate != ''"> and planModifieddate = #{planModifieddate}</if>
            <if test="planPeriodname != null  and planPeriodname != ''"> and planPeriodname = #{planPeriodname}</if>
        </where>
    </select>

    <select id="selectCountFmsPlanByBlockId" parameterType="String" resultType="int">
        select count(1) from FmsPlan where blockId = #{blockId}
    </select>

    <select id="selectCountFmsPlan" parameterType="FmsPlanReqVo" resultType="int">
        select count(1) from FmsPlan
        <where>
            <if test="planNo != null  and planNo != ''"> and planNo = #{planNo}</if>
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="blockId != null  and blockId != ''"> and blockId = #{blockId}</if>
            <if test="cropId != null  and cropId != ''"> and cropId = #{cropId}</if>
            <if test="planStatus != null  and planStatus != ''">
                and
                <foreach collection="planStatus.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        planStatus = #{items}
                    </if>
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectPlanList" parameterType="FmsPlanReqVo" resultType="FmsPlanResVo">
        SELECT p.*,c.cropName, c.cropType, c.cropMaintype, l.landName, b.blockName,
        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = c.cropId) cropPreview,
        (select GROUP_CONCAT(resId) from SysRes r where r.resBusId = c.cropId) resIds,
        (select sum(tp.pickWeight)  from FmsTaskpick tp left join FmsTask f on tp.taskId = f.taskId where f.planId = p.planId) pickWeightSum
        from FmsPlan p
        LEFT JOIN FmsCrop c ON p.cropId = c.cropId
        LEFT JOIN FmsLand l ON p.landId = l.landId
        LEFT JOIN FmsBlock b ON p.blockId = b.blockId
        <where>
            <if test="planNo != null  and planNo != ''"> and p.planNo = #{planNo}</if>
            <if test="landId != null  and landId != ''"> and p.landId = #{landId}</if>
            <if test="blockId != null  and blockId != ''"> and p.blockId = #{blockId}</if>
            <if test="cropId != null  and cropId != ''"> and p.cropId = #{cropId}</if>
            <if test="cropName != null  and cropName != ''"> and c.cropName = #{cropName}</if>
            <if test="planStatus != null  and planStatus != ''">
                and
                <foreach collection="planStatus.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        p.planStatus = #{items}
                    </if>
                </foreach>
            </if>
            <if test="startDate != null  and startDate != ''">
                and p.planPlanenddate >=  #{startDate}
            </if>
            <if test=" endDate != null  and endDate != ''">
                and p.planPlanstartdate <![CDATA[ <= ]]>  #{endDate}
            </if>
            <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>
        order by p.planNo desc
    </select>

    <select id="selectPlanListScheduler" parameterType="FmsPlanReqVo" resultType="FmsPlan">
        SELECT p.* from FmsPlan p
        <where>
            <if test="planNo != null  and planNo != ''"> and p.planNo = #{planNo}</if>
            <if test="landId != null  and landId != ''"> and p.landId = #{landId}</if>
            <if test="blockId != null  and blockId != ''"> and p.blockId = #{blockId}</if>
            <if test="cropId != null  and cropId != ''"> and p.cropId = #{cropId}</if>
            <if test="planStatus != null  and planStatus != ''">
                and
                <foreach collection="planStatus.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        p.planStatus = #{items}
                    </if>
                </foreach>
            </if>
            <if test="startDate != null  and startDate != ''">
                and p.planPlanenddate >=  #{startDate}
            </if>
            <if test=" endDate != null  and endDate != ''">
                and p.planPlanstartdate <![CDATA[ <= ]]>  #{endDate}
            </if>
            <if test=" nowDate != null  and nowDate != ''">
                <if test="updateType != null  and updateType == '0'.toString()">
                    and p.planPlanstartdate <![CDATA[ <= ]]>  #{nowDate}
                </if>
                <if test="updateType != null  and updateType == '1'.toString()">
                    and p.planPlanenddate <![CDATA[ < ]]>  #{nowDate}
                </if>

            </if>
        </where>
    </select>


    <select id="selectFmsPlanByPlanId" parameterType="String" resultType="FmsPlanResVo">
        select p.*, c.cropName, c.cropType, c.cropMaintype
        from FmsPlan p
        LEFT JOIN FmsCrop c ON p.cropId = c.cropId
        where planId = #{planId}
    </select>

    <select id="selectFmsPlanRecordByPlanId" parameterType="String" resultType="FmsPlanResVo">
        select p.*, c.cropName, c.cropType, c.cropMaintype, l.landName, b.blockName,
        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = c.cropId) cropPreview,
        (select GROUP_CONCAT(resId) from SysRes r where r.resBusId = c.cropId) resIds
        from FmsPlan p
        LEFT JOIN FmsCrop c ON p.cropId = c.cropId
        LEFT JOIN FmsLand l ON l.landId = p.landId
        LEFT JOIN FmsBlock b ON b.blockId = p.blockId
        where planId = #{planId}
    </select>

    <select id="selectSumPlanArea" parameterType="FmsPlanReqVo" resultType="BigDecimal">
        SELECT sum(p.planArea) FROM FmsPlan p
        <where>
            <if test="planStatus != null  and planStatus != ''">
                and
                <foreach collection="planStatus.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        p.planStatus = #{items}
                    </if>
                </foreach>
            </if>
            <if test="landId != null  and landId != ''">
                and p.landId =  #{landId}
            </if>
            <if test="blockId != null  and blockId != ''">
                and p.blockId =  #{blockId}
            </if>
            <if test="dataFilter == true and blockIds != null and blockIds.size() > 0">
                AND p.blockId in
                <foreach collection="blockIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>

    </select>

    <select id="selectPlanByCropName" parameterType="FmsPlanReqVo" resultType="FmsPlanResVo">
        SELECT c.cropName,sum(p.planArea) planArea FROM FmsPlan p
        LEFT JOIN FmsLand l ON p.landId = l.landId
        LEFT JOIN FmsCrop c ON p.cropId = c.cropId
        LEFT JOIN FmsBlock b on b.blockId = p.blockId
        <where>
            <if test="planStatus != null  and planStatus != ''">
                and
                <foreach collection="planStatus.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        p.planStatus = #{items}
                    </if>
                </foreach>
            </if>
            <if test="landId != null  and landId != ''">
                and p.landId =  #{landId}
            </if>
            <if test="blockId != null  and blockId != ''">
                and p.blockId =  #{blockId}
            </if>
            <if test="startDate != null  and startDate != ''">
                and p.planPlanenddate >=  #{startDate}
            </if>
            <if test="endDate != null  and endDate != ''">
                and p.planPlanstartdate <![CDATA[ <= ]]>  #{endDate}
            </if>
            <if test="dataFilter == true and landIds != null and landIds.size() > 0">
                AND l.landId in
                <foreach collection="landIds" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        GROUP BY c.cropName
    </select>

    <select id="selectPlanByCropNameAndType" parameterType="FmsPlanReqVo" resultType="FmsPlanResVo">
        SELECT c.cropName, c.cropType,
        sum(p.planArea) planArea
        FROM FmsPlan p
        LEFT JOIN FmsLand l ON p.landId = l.landId
        LEFT JOIN FmsCrop c ON p.cropId = c.cropId
        <where>
            <if test="planStatus != null  and planStatus != ''">
                and
                <foreach collection="planStatus.split(',')" item="items" open="(" separator="or" close=")">
                    <if test="items != null  and items != ''">
                        p.planStatus = #{items}
                    </if>
                </foreach>
            </if>
            <if test="landId != null  and landId != ''">
                and p.landId =  #{landId}
            </if>
            <if test="blockId != null  and blockId != ''">
                and p.blockId =  #{blockId}
            </if>
            <if test="startDate != null  and startDate != ''">
                and p.planPlanenddate >=  #{startDate}
            </if>
            <if test="endDate != null  and endDate != ''">
                and p.planPlanstartdate <![CDATA[ <= ]]>  #{endDate}
            </if>
        </where>
        GROUP BY c.cropName, c.cropType
    </select>


    <select id="selectFmsPlanMaxWaterKey"  resultType="String">
        select max(planNo) as planNo from FmsPlan LIMIT  1
    </select>

    <insert id="insertFmsPlan" parameterType="FmsPlan">
        insert into FmsPlan
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="planId != null">planId,</if>
            <if test="planNo != null">planNo,</if>
            <if test="landId != null">landId,</if>
            <if test="blockId != null">blockId,</if>
            <if test="cropId != null">cropId,</if>
            <if test="planArea != null">planArea,</if>
            <if test="planPlanstartdate != null">planPlanstartdate,</if>
            <if test="planPlanenddate != null">planPlanenddate,</if>
            <if test="planStartdate != null">planStartdate,</if>
            <if test="planEnddate != null">planEnddate,</if>
            <if test="planStatus != null">planStatus,</if>
            <if test="planRemark != null">planRemark,</if>
            <if test="planCreator != null">planCreator,</if>
            <if test="planCreateddate != null">planCreateddate,</if>
            <if test="planModifier != null">planModifier,</if>
            <if test="planModifieddate != null">planModifieddate,</if>
            <if test="planPeriodname != null">planPeriodname,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="planId != null">#{planId},</if>
            <if test="planNo != null">#{planNo},</if>
            <if test="landId != null">#{landId},</if>
            <if test="blockId != null">#{blockId},</if>
            <if test="cropId != null">#{cropId},</if>
            <if test="planArea != null">#{planArea},</if>
            <if test="planPlanstartdate != null">#{planPlanstartdate},</if>
            <if test="planPlanenddate != null">#{planPlanenddate},</if>
            <if test="planStartdate != null">#{planStartdate},</if>
            <if test="planEnddate != null">#{planEnddate},</if>
            <if test="planStatus != null">#{planStatus},</if>
            <if test="planRemark != null">#{planRemark},</if>
            <if test="planCreator != null">#{planCreator},</if>
            <if test="planCreateddate != null">#{planCreateddate},</if>
            <if test="planModifier != null">#{planModifier},</if>
            <if test="planModifieddate != null">#{planModifieddate},</if>
            <if test="planPeriodname != null">#{planPeriodname},</if>
         </trim>
    </insert>

    <update id="updateFmsPlan" parameterType="FmsPlan">
        update FmsPlan
        <trim prefix="SET" suffixOverrides=",">
            <if test="planNo != null">planNo = #{planNo},</if>
            <if test="landId != null">landId = #{landId},</if>
            <if test="blockId != null">blockId = #{blockId},</if>
            <if test="cropId != null">cropId = #{cropId},</if>
            <if test="planArea != null">planArea = #{planArea},</if>
            <if test="planPlanstartdate != null">planPlanstartdate = #{planPlanstartdate},</if>
            <if test="planPlanenddate != null">planPlanenddate = #{planPlanenddate},</if>
            <if test="planStartdate != null">planStartdate = #{planStartdate},</if>
            <if test="planEnddate != null">planEnddate = #{planEnddate},</if>
            <if test="planStatus != null">planStatus = #{planStatus},</if>
            <if test="planRemark != null">planRemark = #{planRemark},</if>
            <if test="planCreator != null">planCreator = #{planCreator},</if>
            <if test="planCreateddate != null">planCreateddate = #{planCreateddate},</if>
            <if test="planModifier != null">planModifier = #{planModifier},</if>
            <if test="planModifieddate != null">planModifieddate = #{planModifieddate},</if>
            <if test="planPeriodname != null">planPeriodname = #{planPeriodname},</if>
        </trim>
        where planId = #{planId}
    </update>

    <update id="updateFmsPlanBatch" parameterType="FmsPlan">
        <foreach collection="list" item="fmsPlan" separator=";">
            update FmsPlan
            <trim prefix="SET" suffixOverrides=",">
                <if test="fmsPlan.planNo != null">planNo = #{fmsPlan.planNo},</if>
                <if test="fmsPlan.landId != null">landId = #{fmsPlan.landId},</if>
                <if test="fmsPlan.blockId != null">blockId = #{fmsPlan.blockId},</if>
                <if test="fmsPlan.cropId != null">cropId = #{fmsPlan.cropId},</if>
                <if test="fmsPlan.planArea != null">planArea = #{fmsPlan.planArea},</if>
                <if test="fmsPlan.planPlanstartdate != null">planPlanstartdate = #{fmsPlan.planPlanstartdate},</if>
                <if test="fmsPlan.planPlanenddate != null">planPlanenddate = #{fmsPlan.planPlanenddate},</if>
                <if test="fmsPlan.planStartdate != null">planStartdate = #{fmsPlan.planStartdate},</if>
                <if test="fmsPlan.planEnddate != null">planEnddate = #{fmsPlan.planEnddate},</if>
                <if test="fmsPlan.planStatus != null">planStatus = #{fmsPlan.planStatus},</if>
                <if test="fmsPlan.planRemark != null">planRemark = #{fmsPlan.planRemark},</if>
                <if test="fmsPlan.planCreator != null">planCreator = #{fmsPlan.planCreator},</if>
                <if test="fmsPlan.planCreateddate != null">planCreateddate = #{fmsPlan.planCreateddate},</if>
                <if test="fmsPlan.planModifier != null">planModifier = #{fmsPlan.planModifier},</if>
                <if test="fmsPlan.planModifieddate != null">planModifieddate = #{fmsPlan.planModifieddate},</if>
                <if test="fmsPlan.planPeriodname != null">planPeriodname = #{fmsPlan.planPeriodname},</if>
            </trim>
            where planId = #{fmsPlan.planId}
        </foreach>
    </update>

    <delete id="deleteFmsPlanByPlanId" parameterType="String">
        delete from FmsPlan where planId = #{planId}
    </delete>

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

    <select id="selectPlanListByWarnScheduler" resultType="FmsPlanByWarnSchedulerDto">
        SELECT p.planId, pe.periodId, pe.periodName, pe.periodRemark, p.planPlanstartdate, p.planPlanenddate,
        p.planEnddate, pe.periodDays, pe.periodSeq
        FROM FmsPlan AS p
        JOIN FmsCrop AS c ON p.cropId = c.cropId
        JOIN FmsPeriod AS pe ON p.cropId = pe.cropId
        <where>
            p.planStatus = 1

            <if test="planEnddate != null and planEnddate != ''">
                and p.planEnddate >= #{planEnddate} OR p.planEnddate IS NULL
            </if>
        </where>
        ORDER BY p.planId, pe.periodId, pe.periodSeq
    </select>

    <select id="selectPlanListOfTaskStatus" parameterType="String" resultType="FmsPlanListOfTaskStatusResVo">
        SELECT p.planId, p.blockId, p.cropId, p.landId, p.planNo, p.planPeriodname, c.cropName, c.cropType, b.blockName,p.planplanstartdate,p.planPlanenddate,
        (SELECT count(1) FROM FmsTask t WHERE t.taskStatus <![CDATA[ < ]]> 3 and t.planId = p.planId) taskCount,
        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = c.cropId) cropPreview
        FROM FmsPlan p
        LEFT JOIN FmsCrop c on c.cropId = p.cropId
        LEFT JOIN FmsBlock b on b.blockId = p.blockId
        <where>
            <if test="landId != null and landId != ''">
                and p.landId  = #{landId}
            </if>
            and p.planStatus != '2'
        </where>
    </select>
</mapper>