<?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.FmsBlockMapper">
    
    <resultMap type="FmsBlock" id="FmsBlockResult">
        <result property="blockId"    column="blockId"    />
        <result property="landId"    column="landId"    />
        <result property="blockName"    column="blockName"    />
        <result property="blockType"    column="blockType"    />
        <result property="blockManager"    column="blockManager"    />
        <result property="blockManagername"    column="blockManagername"    />
        <result property="blockArea"    column="blockArea"    />
        <result property="blockAreaplant"    column="blockAreaplant"    />
        <result property="blockLngrange"    column="blockLngrange"    />
        <result property="blockColor"    column="blockColor"    />
        <result property="blockRemark"    column="blockRemark"    />
        <result property="blockPreview"    column="blockPreview"    />
        <result property="blockSeq" column="blockSeq"/>
    </resultMap>


    <sql id="selectFmsBlockVo">
        select blockId, landId, blockName, blockType, blockManager, blockManagername, blockArea, blockAreaplant,blockSeq,
        blockLngrange, blockColor, blockRemark from FmsBlock
    </sql>

    <sql id="selectFmsBlockHasResVo">
        select b.blockId, b.landId, b.blockName, b.blockType, b.blockManager, b.blockManagername, b.blockArea, b.blockSeq, b.blockAreaplant, b.blockLngrange, b.blockColor, b.blockRemark,
        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = b.blockId) blockPreview,
        (select GROUP_CONCAT(resId) from SysRes r where r.resBusId = b.blockId) resIds
        from FmsBlock b
    </sql>

    <sql id="selectFmsBlockHasResVo2">
        select b.blockId, b.landId, b.blockName, b.blockType, b.blockManager, b.blockManagername, b.blockArea,
        b.blockSeq,
        b.blockAreaplant, b.blockLngrange, b.blockColor, b.blockRemark, l.landName,
        (select GROUP_CONCAT(c.cropName) from FmsCrop c
        left join FmsPlan p on p.cropId = c.cropId
        where p.blockId = b.blockId) cropName,
        (select GROUP_CONCAT(r.resUrl) from FmsCrop c
        left join FmsPlan p on p.cropId = c.cropId
        left join SysRes r on r.resBusId = c.cropId
        where p.blockId = b.blockId) cropPreview,
        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = b.blockId) blockPreview,
        (select GROUP_CONCAT(resId) from SysRes r where r.resBusId = b.blockId) resIds
        from FmsBlock b
        left join FmsLand l on b.landId = l.landId

    </sql>



    <select id="selectFmsBlockList" parameterType="FmsBlock" resultMap="FmsBlockResult">
        <include refid="selectFmsBlockHasResVo"/>
        <where>  
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="blockName != null  and blockName != ''"> and blockName like concat('%', #{blockName}, '%')</if>
            <if test="blockNameEq != null  and blockNameEq != ''"> and blockName = #{blockNameEq}</if>
            <if test="blockType != null  and blockType != ''"> and blockType = #{blockType}</if>
            <if test="blockManager != null  and blockManager != ''"> and blockManager = #{blockManager}</if>
            <if test="blockManagername != null  and blockManagername != ''"> and blockManagername like concat('%', #{blockManagername}, '%')</if>
            <if test="blockArea != null "> and blockArea = #{blockArea}</if>
            <if test="blockAreaplant != null "> and blockAreaplant = #{blockAreaplant}</if>
            <if test="blockLngrange != null  and blockLngrange != ''"> and blockLngrange = #{blockLngrange}</if>
            <if test="blockColor != null  and blockColor != ''"> and blockColor = #{blockColor}</if>
            <if test="blockRemark != null  and blockRemark != ''"> and blockRemark = #{blockRemark}</if>
            <if test="dataFilter == true and blockIds !=null and blockIds.size()>0">
                and b.blockId in
                <foreach collection="blockIds" item="blockId" index="index" open="(" close=")" separator=",">
                    #{blockId}
                </foreach>
            </if>
        </where>
    </select>
    
    <select id="selectFmsBlockByBlockId" parameterType="String" resultMap="FmsBlockResult">
        <include refid="selectFmsBlockHasResVo"/>
        where blockId = #{blockId}
    </select>

    <select id="selectFmsBlockInfoByBlockId" parameterType="String" resultType="FmsBlockResVo">
        SELECT *, (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = b.blockId) blockPreview
        from FmsBlock b
        LEFT JOIN FmsLand l ON b.landId = l.landId
        where b.blockId = #{blockId}
    </select>

    <select id="selectCountByLandId" parameterType="String" resultType="Integer">
        select count(1) from FmsBlock where landId = #{landId}
    </select>

    <select id="selectSumAreaByLandId" parameterType="FmsLand" resultType="BigDecimal">
        select sum(blockArea) from FmsBlock
        <where>
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="dataFilter == true and blockIds !=null and blockIds.size()>0">
                and blockId in
                <foreach collection="blockIds" item="blockId" index="index" open="(" close=")" separator=",">
                    #{blockId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectSumArea" parameterType="FmsBlockReqVo" resultType="BigDecimal">
        select sum(b.blockArea) from FmsBlock b LEFT JOIN FmsLand l ON b.landId = l.landId WHERE l.landId is not null
        <if test="landId != null  and landId != ''"> and l.landId = #{landId}</if>
        <if test="dataFilter == true and blockIds !=null and blockIds.size()>0">
            and b.blockId in
            <foreach collection="blockIds" item="blockId" index="index" open="(" close=")" separator=",">
                #{blockId}
            </foreach>
        </if>
    </select>

    <select id="selectFmsBlockHasPlan" parameterType="FmsBlockReqVo" resultType="FmsBlockResVo">
        select b.* , l.landName,
        tb1.blockPreview,
        tb1.resIds,
        (SELECT GROUP_CONCAT(c.cropName) FROM FmsPlan p LEFT JOIN FmsCrop c ON p.cropId = c.cropId WHERE p.blockId = b.blockId and (p.planStatus = 0 or p.planStatus = 1) limit 1) cropNames
        from FmsBlock b
        left join FmsLand l on b.landId = l.landId
        left join (select GROUP_CONCAT(resUrl) blockPreview, GROUP_CONCAT(resId) resIds, resBusId  from SysRes
                    WHERE resBiztype = 'BLOCK' group by resBusId  ) tb1 on b.blockId = tb1.resBusId
        having 1=1
        <if test="hasPlan != null  and hasPlan == '0'.toString() "> and cropNames is null</if>
        <if test="hasPlan != null  and hasPlan == '1'.toString() "> and cropNames is not null</if>
        <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
        <if test="blockId != null  and blockId != ''"> and blockId = #{blockId}</if>
        <if test="cropName != null  and cropName != ''"> and cropNames like concat('%', #{cropName}, '%')</if>
        <if test="blockName != null  and blockName != ''"> and b.blockName like concat('%', #{blockName}, '%')</if>
        <if test="dataFilter == true and blockIds !=null and blockIds.size()>0">
            and b.blockId in
            <foreach collection="blockIds" item="blockId" index="index" open="(" close=")" separator=",">
                #{blockId}
            </foreach>
        </if>
        order by b.blockSeq
    </select>

    <insert id="insertFmsBlock" parameterType="FmsBlock">
        insert into FmsBlock
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="blockId != null">blockId,</if>
            <if test="landId != null">landId,</if>
            <if test="blockName != null">blockName,</if>
            <if test="blockType != null">blockType,</if>
            <if test="blockManager != null">blockManager,</if>
            <if test="blockManagername != null">blockManagername,</if>
            <if test="blockArea != null">blockArea,</if>
            <if test="blockAreaplant != null">blockAreaplant,</if>
            <if test="blockLngrange != null">blockLngrange,</if>
            <if test="blockColor != null">blockColor,</if>
            <if test="blockRemark != null">blockRemark,</if>
            <if test="blockSeq != null">blockSeq,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="blockId != null">#{blockId},</if>
            <if test="landId != null">#{landId},</if>
            <if test="blockName != null">#{blockName},</if>
            <if test="blockType != null">#{blockType},</if>
            <if test="blockManager != null">#{blockManager},</if>
            <if test="blockManagername != null">#{blockManagername},</if>
            <if test="blockArea != null">#{blockArea},</if>
            <if test="blockAreaplant != null">#{blockAreaplant},</if>
            <if test="blockLngrange != null">#{blockLngrange},</if>
            <if test="blockColor != null">#{blockColor},</if>
            <if test="blockRemark != null">#{blockRemark},</if>
            <if test="blockSeq != null">#{blockSeq}</if>
         </trim>
    </insert>

    <update id="updateFmsBlock" parameterType="FmsBlock">
        update FmsBlock
        <trim prefix="SET" suffixOverrides=",">
            <if test="landId != null">landId = #{landId},</if>
            <if test="blockName != null">blockName = #{blockName},</if>
            <if test="blockType != null">blockType = #{blockType},</if>
            <if test="blockManager != null">blockManager = #{blockManager},</if>
            <if test="blockManagername != null">blockManagername = #{blockManagername},</if>
            <if test="blockArea != null">blockArea = #{blockArea},</if>
            <if test="blockAreaplant != null">blockAreaplant = #{blockAreaplant},</if>
            <if test="blockLngrange != null">blockLngrange = #{blockLngrange},</if>
            <if test="blockColor != null">blockColor = #{blockColor},</if>
            <if test="blockRemark != null">blockRemark = #{blockRemark},</if>
            <if test="blockSeq != null">blockSeq =#{blockSeq},</if>
        </trim>
        where blockId = #{blockId}
    </update>

    <update id="updateFmsBlockBatch" parameterType="FmsBlock">
        <foreach collection="list" item="block" separator=";">
            update FmsBlock
            <trim prefix="SET" suffixOverrides=",">
                <if test="block.landId != null">landId = #{block.landId},</if>
                <if test="block.blockName != null">blockName = #{block.blockName},</if>
                <if test="block.blockType != null">blockType = #{block.blockType},</if>
                <if test="block.blockManager != null">blockManager = #{block.blockManager},</if>
                <if test="block.blockManagername != null">blockManagername = #{block.blockManagername},</if>
                <if test="block.blockArea != null">blockArea = #{block.blockArea},</if>
                <if test="block.blockAreaplant != null">blockAreaplant = #{block.blockAreaplant},</if>
                <if test="block.blockLngrange != null">blockLngrange = #{block.blockLngrange},</if>
                <if test="block.blockColor != null">blockColor = #{block.blockColor},</if>
                <if test="block.blockRemark != null">blockRemark = #{block.blockRemark},</if>
                <if test="block.blockSeq != null">blockSeq =#{block.blockSeq},</if>
            </trim>
            where blockId = #{block.blockId}
        </foreach>
    </update>

    <delete id="deleteFmsBlockByBlockId" parameterType="String">
        delete from FmsBlock where blockId = #{blockId}
    </delete>

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

    <select id="selectFmsLandByBlockIds" parameterType="String" resultType="FmsLand">
        select l.* from FmsBlock b LEFT JOIN FmsLand l ON b.landId = l.landId
        where b.blockId in
        <foreach item="blockId" collection="array" open="(" separator="," close=")">
            #{blockId}
        </foreach>
        group by b.landId
    </select>

    <select id="selectValidDataNumber" parameterType="String" resultType="java.lang.Integer">
        select sum(num) from
        <trim prefix="(" suffix=")">
            <include refid="selectFmsPlanValidData"/>
            UNION ALL
            <include refid="selectIcsIrrgrcdValidData"/>
        </trim>
        AS validDataNum
    </select>

    <sql id="selectFmsPlanValidData">
        SELECT
        count( 1 ) AS num
        FROM
        FmsPlan
        WHERE
        blockId = #{blockId}
    </sql>
    <sql id="selectIcsIrrgrcdValidData">
        SELECT
        count( 1 ) AS num
        FROM
        IcsIrrgrcd
        WHERE
        blockId = #{blockId}
    </sql>


</mapper>