<?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.FmsLandMapper">
    
    <resultMap type="FmsLand" id="FmsLandResult">
        <result property="landId"    column="landId"    />
        <result property="landName"    column="landName"    />
        <result property="landArea"    column="landArea"    />
        <result property="landBlockcount"    column="landBlockcount"    />
        <result property="landDevcount"    column="landDevcount"    />
        <result property="landManager"    column="landManager"    />
        <result property="landTel"    column="landTel"    />
        <result property="landLocation"    column="landLocation"    />
        <result property="landPreview"    column="landPreview"    />
        <result property="landRemark"    column="landRemark"    />
        <result property="resIds"    column="resIds"    />
        <result property="landSeq" column="landSeq"/>
    </resultMap>

    <resultMap type="FmsLandTotalResVo" id="FmsLandTotalResult">
        <result property="count"    column="count"    />
        <result property="landAreaSum"    column="landAreaSum"    />
        <result property="landBlockcountSum"    column="landBlockcountSum"    />
        <result property="landDevcountSum"    column="landDevcountSum"    />
    </resultMap>

    <sql id="selectFmsLandVo">
        select landId, landName, landArea, landBlockcount, landDevcount, landManager, landTel, landLocation, landRemark, landLatitude, landLongitude, landSeqfrom FmsLand
    </sql>

<!--    <sql id="selectFmsLandHasResVo">-->
<!--        select l.*,-->
<!--        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = l.landId) landPreview,-->
<!--        (select GROUP_CONCAT(resId) from SysRes r where r.resBusId = l.landId) resIds-->
<!--        from FmsLand l-->
<!--    </sql>-->

    <sql id="selectFmsLandHasResVo">
        SELECT l.landId, l.landName, l.landArea, l.landBlockcount, l.landManager, l.landTel, l.landLocation,
            l.landRemark, l.landLatitude, l.landLongitude,l.landSeq,
        (select GROUP_CONCAT(resUrl) from SysRes r where r.resBusId = l.landId) landPreview,
        (select GROUP_CONCAT(resId) from SysRes r where r.resBusId = l.landId) resIds,
        (SELECT COUNT(tmnlandId) FROM TmnLand tl WHERE tl.landId = l.landId) landDevcount,
        (SELECT u.userName FROM SysUser u WHERE u.userId = l.landManager) landManagerName
        from FmsLand l
    </sql>

    <select id="selectFmsLandList" parameterType="FmsLand" resultMap="FmsLandResult">
        <include refid="selectFmsLandHasResVo"/>
        <where>
            <if test="landId != null "> and landId = #{landId}</if>
            <if test="landName != null  and landName != ''"> and landName like concat('%', #{landName}, '%')</if>
            <if test="landNameEq != null  and landNameEq != ''"> and landName = #{landNameEq}</if>
            <if test="landArea != null "> and landArea = #{landArea}</if>
            <if test="landBlockcount != null "> and landBlockcount = #{landBlockcount}</if>
            <if test="landDevcount != null "> and landDevcount = #{landDevcount}</if>
            <if test="landManager != null  and landManager != ''"> and landManager = #{landManager}</if>
            <if test="landTel != null  and landTel != ''"> and landTel = #{landTel}</if>
            <if test="landLocation != null  and landLocation != ''"> and landLocation = #{landLocation}</if>
            <if test="landRemark != null  and landRemark != ''"> and landRemark = #{landRemark}</if>
            <if test="dataFilter == true and landIds !=null and landIds.size()>0">
                and l.landId in
                <foreach collection="landIds" item="landId" index="index" open="(" close=")" separator=",">
                    #{landId}
                </foreach>
            </if>
        </where>
        order by l.landSeq

    </select>
    
    <select id="selectFmsLandByLandId" parameterType="String" resultMap="FmsLandResult">
        <include refid="selectFmsLandHasResVo"/>
        where landId = #{landId}
    </select>

    <select id="selectFmsLandTotal" parameterType="FmsLand" resultMap="FmsLandTotalResult">
        SELECT count(1) count, sum(landArea) landAreaSum, sum(landBlockcount) landBlockcountSum,
        (SELECT COUNT(tmnlandId) FROM TmnLand
        <where>
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="dataFilter == true and landIds !=null and landIds.size()>0">
                and landId in
                <foreach collection="landIds" item="landId" index="index" open="(" close=")" separator=",">
                    #{landId}
                </foreach>
            </if>
        </where>
        ) landDevcountSum
        from FmsLand
        <where>
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="dataFilter == true and landIds !=null and landIds.size()>0">
                and landId in
                <foreach collection="landIds" item="landId" index="index" open="(" close=")" separator=",">
                    #{landId}
                </foreach>
            </if>
        </where>
    </select>

    <select id="selectDevCount" parameterType="FmsLand" resultType="int">
        SELECT COUNT(1) FROM (SELECT COUNT(tmnId) count FROM TmnLand
        <where>
            <if test="landId != null  and landId != ''"> and landId = #{landId}</if>
            <if test="dataFilter == true and landIds !=null and landIds.size()>0">
                and landId in
                <foreach collection="landIds" item="landId" index="index" open="(" close=")" separator=",">
                    #{landId}
                </foreach>
            </if>
        </where>
        GROUP BY tmnId
        ) tb
    </select>
        
    <insert id="insertFmsLand" parameterType="FmsLand">
        insert into FmsLand
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="landId != null">landId,</if>
            <if test="landName != null">landName,</if>
            <if test="landArea != null">landArea,</if>
            <if test="landBlockcount != null">landBlockcount,</if>
            <if test="landDevcount != null">landDevcount,</if>
            <if test="landManager != null">landManager,</if>
            <if test="landTel != null">landTel,</if>
            <if test="landLocation != null">landLocation,</if>
            <if test="landRemark != null">landRemark,</if>
            <if test="landLatitude != null">landLatitude,</if>
            <if test="landLongitude != null">landLongitude,</if>
            <if test="landSeq !=null" >landSeq,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="landId != null">#{landId},</if>
            <if test="landName != null">#{landName},</if>
            <if test="landArea != null">#{landArea},</if>
            <if test="landBlockcount != null">#{landBlockcount},</if>
            <if test="landDevcount != null">#{landDevcount},</if>
            <if test="landManager != null">#{landManager},</if>
            <if test="landTel != null">#{landTel},</if>
            <if test="landLocation != null">#{landLocation},</if>
            <if test="landRemark != null">#{landRemark},</if>
            <if test="landLatitude != null">#{landLatitude},</if>
            <if test="landLongitude != null">#{landLongitude},</if>
            <if test="landSeq != null"> #{landSeq},</if>
         </trim>
    </insert>

    <update id="updateFmsLand" parameterType="FmsLand">
        update FmsLand
        <trim prefix="SET" suffixOverrides=",">
            <if test="landName != null">landName = #{landName},</if>
            <if test="landArea != null">landArea = #{landArea},</if>
            <if test="landBlockcount != null">landBlockcount = #{landBlockcount},</if>
            <if test="landDevcount != null">landDevcount = #{landDevcount},</if>
            <if test="landManager != null">landManager = #{landManager},</if>
            <if test="landTel != null">landTel = #{landTel},</if>
            <if test="landLocation != null">landLocation = #{landLocation},</if>
            <if test="landRemark != null">landRemark = #{landRemark},</if>
            <if test="landLatitude != null">landLatitude = #{landLatitude},</if>
            <if test="landLongitude != null">landLongitude = #{landLongitude},</if>
            <if test="landSeq != null" >landSeq =#{landSeq},</if>
        </trim>
        where landId = #{landId}
    </update>

    <update id="updateFmsLandBatch" >
        <foreach collection="list" item="fmsLand" separator=";">
            update FmsLand
            <trim prefix="SET" suffixOverrides=",">
                <if test="fmsLand.landName != null">landName = #{fmsLand.landName},</if>
                <if test="fmsLand.landArea != null">landArea = #{fmsLand.landArea},</if>
                <if test="fmsLand.landBlockcount != null">landBlockcount = #{fmsLand.landBlockcount},</if>
                <if test="fmsLand.landDevcount != null">landDevcount = #{fmsLand.landDevcount},</if>
                <if test="fmsLand.landManager != null">landManager = #{fmsLand.landManager},</if>
                <if test="fmsLand.landTel != null">landTel = #{fmsLand.landTel},</if>
                <if test="fmsLand.landLocation != null">landLocation = #{fmsLand.landLocation},</if>
                <if test="fmsLand.landRemark != null">landRemark = #{fmsLand.landRemark},</if>
                <if test="fmsLand.landLatitude != null">landLatitude = #{fmsLand.landLatitude},</if>
                <if test="fmsLand.landLongitude != null">landLongitude = #{fmsLand.landLongitude},</if>
                <if test="fmsLand.landSeq != null">landSeq =#{fmsLand.landSeq},</if>
            </trim>
            where landId = #{fmsLand.landId}
        </foreach>
    </update>

    <delete id="deleteFmsLandByLandId" parameterType="String">
        delete from FmsLand where landId = #{landId}
    </delete>

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

    <select id="selectFmsLandByTarcecodeapplyno" parameterType="String" resultMap="FmsLandResult">
        SELECT
        l.*
        FROM
        FmsLand l
        LEFT JOIn FmsTask t on t.landId = l.landId
        LEFT JOIN FmsTaskpick tp on tp.taskId = t.taskId
        where tp.pickNo = #{tarcecodeapplyno}
    </select>
</mapper>