<?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.TmnBaseMapper">
    
    <resultMap type="TmnBase" id="TmnBaseResult">
        <result property="tmnId"    column="tmnId"    />
        <result property="tmnName"    column="tmnName"    />
        <result property="tmnBizcode"    column="tmnBizcode"    />
        <result property="tmnType"    column="tmnType"    />
        <result property="tmnCreateddate"    column="tmnCreateddate"    />
        <result property="tmnLatitude"    column="tmnLatitude"    />
        <result property="tmnLongitude"    column="tmnLongitude"    />
    </resultMap>

    <sql id="selectTmnBaseVo">
        select tmnId, tmnName, tmnBizcode, tmnType, tmnCreateddate from TmnBase
    </sql>

    <select id="selectTmnBaseList" parameterType="TmnBase" resultType="TmnBase">
        select tb.tmnId, tl.tmnlandId, fl.landId, fl.landName, tb.tmnName, tb.tmnBizcode, tb.tmnType, tb.tmnCreateddate, tl.tmnlandAssigndate
        from TmnBase as tb
            LEFT JOIN TmnLand AS tl on tb.tmnId = tl.tmnId
            LEFT JOIN FmsLand as fl ON tl.landId = fl.landId

        <where>  
            <if test="landId != null  and landId != ''"> and tl.landId = #{landId}</if>
            <if test="tmnBizcode != null  and tmnBizcode != ''"> and tb.tmnBizcode like concat('%', #{tmnBizcode}, '%')</if>
            <if test="tmnType != null  and tmnType != ''">
                and tb.tmnType in
                <foreach item="ty" collection="tmnTypeIds" open="(" separator="," close=")">
                    #{ty}
                </foreach>
            </if>
            <if test="tmnName != null  and tmnName != ''"> and tb.tmnName like concat('%', #{tmnName} '%')</if>
        </where>
        order by tb.tmnCreateddate desc, tb.tmnId desc
    </select>
    
    <select id="selectTmnBaseByTmnId" parameterType="String" resultMap="TmnBaseResult">
        <include refid="selectTmnBaseVo"/>
        where tmnId = #{tmnId}
    </select>

    <select id="selectTmnBaseByOne" resultMap="TmnBaseResult">
        <include refid="selectTmnBaseVo"/>
        order by tmnCreateddate desc
        limit 1
    </select>

    <select id="selectTmnBaseTypeList" resultType="TmnBaseTypeResVo">
        SELECT tmnType, COUNT(*) as tmnTypeNum
        FROM TmnBase
        GROUP BY tmnType
        ORDER BY CAST(tmnType AS signed)
    </select>

    <select id="selectTmnBaseByBizCodeList" parameterType="String" resultType="TmnBase">
        select tmnId, tmnName, tmnBizcode, tmnType, tmnCreateddate from TmnBase
        WHERE tmnBizcode IN
        <foreach item="tmnBizcode" collection="array" open="(" separator="," close=")">
            #{tmnBizcode}
        </foreach>
    </select>
        
    <insert id="insertTmnBase" parameterType="TmnBase">
        insert into TmnBase
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="tmnId != null">tmnId,</if>
            <if test="tmnName != null">tmnName,</if>
            <if test="tmnBizcode != null">tmnBizcode,</if>
            <if test="tmnType != null">tmnType,</if>
            <if test="tmnCreateddate != null">tmnCreateddate,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="tmnId != null">#{tmnId},</if>
            <if test="tmnName != null">#{tmnName},</if>
            <if test="tmnBizcode != null">#{tmnBizcode},</if>
            <if test="tmnType != null">#{tmnType},</if>
            <if test="tmnCreateddate != null">#{tmnCreateddate},</if>
         </trim>
    </insert>

    <insert id="insertTmnBaseBatch" parameterType="TmnBase">
        <foreach collection="list" item="tmnBase" separator=";">
            update TmnBase
            <trim prefix="SET" suffixOverrides=",">
                <if test="tmnBase.tmnName != null">tmnName=#{tmnBase.tmnName},</if>
                <if test="tmnBase.tmnType != null">tmnType=#{tmnBase.tmnType},</if>
                <if test="tmnBase.tmnLatitude != null">tmnLatitude=#{tmnBase.tmnLatitude},</if>
                <if test="tmnBase.tmnLongitude != null">tmnLongitude=#{tmnBase.tmnLongitude},</if>
            </trim>
            where tmnBizcode = #{tmnBase.tmnBizcode};
            insert into TmnBase
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="tmnBase.tmnId != null">tmnId,</if>
                <if test="tmnBase.tmnName != null">tmnName,</if>
                <if test="tmnBase.tmnBizcode != null">tmnBizcode,</if>
                <if test="tmnBase.tmnType != null">tmnType,</if>
                <if test="tmnBase.tmnCreateddate != null">tmnCreateddate,</if>
                <if test="tmnBase.tmnLatitude != null">tmnLatitude,</if>
                <if test="tmnBase.tmnLongitude != null">tmnLongitude,</if>
            </trim>
            <trim prefix="select " suffix=" " suffixOverrides=",">
                <if test="tmnBase.tmnId != null">#{tmnBase.tmnId},</if>
                <if test="tmnBase.tmnName != null">#{tmnBase.tmnName},</if>
                <if test="tmnBase.tmnBizcode != null">#{tmnBase.tmnBizcode},</if>
                <if test="tmnBase.tmnType != null">#{tmnBase.tmnType},</if>
                <if test="tmnBase.tmnCreateddate != null">#{tmnBase.tmnCreateddate},</if>
                <if test="tmnBase.tmnLatitude != null">#{tmnBase.tmnLatitude},</if>
                <if test="tmnBase.tmnLongitude != null">#{tmnBase.tmnLongitude},</if>
            </trim>
            FROM DUAL
                WHERE NOT EXISTS(SELECT tmnBizcode FROM TmnBase WHERE tmnBizcode = #{tmnBase.tmnBizcode})
        </foreach>

    </insert>

    <update id="updateTmnBase" parameterType="TmnBase">
        update TmnBase
        <trim prefix="SET" suffixOverrides=",">
            <if test="tmnName != null">tmnName = #{tmnName},</if>
            <if test="tmnBizcode != null">tmnBizcode = #{tmnBizcode},</if>
            <if test="tmnType != null">tmnType = #{tmnType},</if>
            <if test="tmnCreateddate != null">tmnCreateddate = #{tmnCreateddate},</if>
        </trim>
        where tmnId = #{tmnId}
    </update>

    <delete id="deleteTmnBaseByTmnId" parameterType="String">
        delete from TmnBase where tmnId = #{tmnId}
    </delete>

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