<?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.FmsCropMapper">
    
    <resultMap type="FmsCrop" id="FmsCropResult">
        <result property="cropId"    column="cropId"    />
        <result property="cropName"    column="cropName"    />
        <result property="cropType"    column="cropType"    />
        <result property="cropMaintype"    column="cropMaintype"    />
        <result property="cropRcmdstatus"    column="cropRcmdstatus"    />
        <result property="cropPreview"    column="cropPreview"    />
        <result property="cropRemark"    column="cropRemark"    />
    </resultMap>
    <resultMap type="FmsCropResVo" id="FmsCropResVoResult">
        <result property="cropId"    column="cropId"    />
        <result property="cropName"    column="cropName"    />
        <result property="cropType"    column="cropType"    />
        <result property="cropMaintype"    column="cropMaintype"    />
        <result property="cropRcmdstatus"    column="cropRcmdstatus"    />
        <result property="cropPreview"    column="cropPreview"    />
        <result property="cropRemark"    column="cropRemark"    />
        <result property="count"    column="count"    />
        <result property="periodDays"    column="periodDays"    />
    </resultMap>


    <sql id="selectFmsCropVo">
        select cropId, cropName, cropType, cropMaintype, cropRcmdstatus, cropRemark, cropCreator, cropCreateddate from FmsCrop
    </sql>
    <sql id="selectFmsCropHasResVo">
        select c.*,
        (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 FmsCrop c
    </sql>

    <select id="selectFmsCropList" parameterType="FmsCrop" resultMap="FmsCropResVoResult">
        SELECT
        cropId,
        cropName,
        cropType,
        cropMaintype,
        cropRcmdstatus,
<!--        cropPreview,-->
        cropRemark,
        cropCreator,
        cropCreateddate,
        ( SELECT count( 1 ) FROM FmsPeriod p WHERE p.cropId = c.cropId ) count,
        ( SELECT sum( periodDays ) FROM FmsPeriod p WHERE p.cropId = c.cropId ) periodDays,
        (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
        FmsCrop c
        <where>  
            <if test="cropName != null  and cropName != ''"> and c.cropName = #{cropName}</if>
<!--            <if test="cropName != null  and cropName != ''"> and cropName like concat('%', #{cropName}, '%')</if>-->
            <if test="cropType != null  and cropType != ''"> and c.cropType = #{cropType}</if>
            <if test="cropMaintype != null  and cropMaintype != ''"> and c.cropMaintype = #{cropMaintype}</if>
            <if test="cropRcmdstatus != null  and cropRcmdstatus != ''"> and c.cropRcmdstatus = #{cropRcmdstatus}</if>
            <if test="cropRemark != null  and cropRemark != ''"> and c.cropRemark = #{cropRemark}</if>
        </where>
        order by c.cropCreateddate desc
    </select>
    
    <select id="selectFmsCropByCropId" parameterType="String" resultMap="FmsCropResult">
        <include refid="selectFmsCropHasResVo"/>
        where cropId = #{cropId}
    </select>

    <select id="selectFmsCropNameByCropMaintype" parameterType="String" resultMap="FmsCropResult">
        select cropName from FmsCrop
        where cropRcmdstatus = '0'
        <if test="cropMaintype != null  and cropMaintype != ''"> and cropMaintype = #{cropMaintype}</if>
        group by cropName
    </select>

    <select id="selectFmsCropNameByBlockId" parameterType="String" resultType="FmsBlockResVo">
        SELECT GROUP_CONCAT(tb.cropName, tb.cropType) cropNames, tb.blockId, GROUP_CONCAT(tb.cropPreviews) cropPreviews
        FROM (SELECT c.cropName, c.cropType, p.blockId, GROUP_CONCAT(r.resUrl) cropPreviews
            FROM FmsPlan p LEFT JOIN FmsCrop c ON p.cropId = c.cropId left join SysRes r on r.resBusId = c.cropId
            where p.blockId in
            <foreach item="blockId" collection="array" open="(" separator="," close=")">
                #{blockId}
            </foreach>
            and p.planStatus != '2' group by p.blockId, c.cropId
            ) tb
        group by tb.blockId;

    </select>
        
    <insert id="insertFmsCrop" parameterType="FmsCrop">
        insert into FmsCrop
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="cropId != null">cropId,</if>
            <if test="cropName != null">cropName,</if>
            <if test="cropType != null">cropType,</if>
            <if test="cropMaintype != null">cropMaintype,</if>
            <if test="cropRcmdstatus != null">cropRcmdstatus,</if>
            <if test="cropRemark != null">cropRemark,</if>
            <if test="cropCreator != null">cropCreator,</if>
            <if test="cropCreateddate != null">cropCreateddate,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="cropId != null">#{cropId},</if>
            <if test="cropName != null">#{cropName},</if>
            <if test="cropType != null">#{cropType},</if>
            <if test="cropMaintype != null">#{cropMaintype},</if>
            <if test="cropRcmdstatus != null">#{cropRcmdstatus},</if>
            <if test="cropRemark != null">#{cropRemark},</if>
            <if test="cropCreator != null">#{cropCreator},</if>
            <if test="cropCreateddate != null">#{cropCreateddate},</if>
         </trim>
    </insert>

    <update id="updateFmsCrop" parameterType="FmsCrop">
        update FmsCrop
        <trim prefix="SET" suffixOverrides=",">
            <if test="cropName != null">cropName = #{cropName},</if>
            <if test="cropType != null">cropType = #{cropType},</if>
            <if test="cropMaintype != null">cropMaintype = #{cropMaintype},</if>
            <if test="cropRcmdstatus != null">cropRcmdstatus = #{cropRcmdstatus},</if>
            <if test="cropRemark != null">cropRemark = #{cropRemark},</if>
            <if test="cropCreator != null">cropCreator = #{cropCreator},</if>
            <if test="cropCreateddate != null">cropCreateddate = #{cropCreateddate},</if>
        </trim>
        where cropId = #{cropId}
    </update>

    <delete id="deleteFmsCropByCropId" parameterType="String">
        delete from FmsCrop where cropId = #{cropId}
    </delete>

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