<?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.ruoyi.bst.areaSub.mapper.AreaSubMapper">

    <resultMap type="AreaSubVO" id="AreaSubResult" autoMapping="true">
        <result property="boundary" column="boundary" typeHandler="com.ruoyi.common.mybatis.typehandler.GeometryTypeHandler"/>
    </resultMap>

    <sql id="selectAreaSubVo">
        select
            bas.id,
            bas.name,
            bas.type,
            bas.area_id,
            bas.boundary,
            bas.boundary_str,
            bas.error,
            bas.longitude,
            bas.latitude,
            bas.create_time,
            bas.picture,
            bas.remark,
            bas.status,
            bas.deleted
        <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        from bst_area_sub bas
            left join bst_area ba on ba.id = bas.area_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and bas.id = #{query.id}</if>
        <if test="query.name != null  and query.name != ''"> and bas.name like concat('%', #{query.name}, '%')</if>
        <if test="query.type != null  and query.type != ''"> and bas.type = #{query.type}</if>
        <if test="query.areaId != null "> and bas.area_id = #{query.areaId}</if>
        <if test="query.status != null  and query.status != ''"> and bas.status = #{query.status}</if>
        <if test="query.deleted != null "> and bas.deleted = #{query.deleted}</if>
        <if test="query.deleted == null "> and bas.deleted = false</if>
        <if test="query.ids != null and query.ids.size() > 0">
            and bas.id in
            <foreach collection="query.ids" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@dataScope(
            null,
            "ba.user_id,ba.create_id",
            null,
            null,
            query.scope
        )}
        ${query.params.dataScope}
    </sql>

    <select id="selectAreaSubList" parameterType="AreaSubQuery" resultMap="AreaSubResult">
        <include refid="selectAreaSubVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectAreaSubById" parameterType="Long" resultMap="AreaSubResult">
        <include refid="selectAreaSubVo"/>
        where bas.id = #{id}
    </select>

    <insert id="insertAreaSub" parameterType="AreaSub" useGeneratedKeys="true" keyProperty="id">
        insert into bst_area_sub
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">name,</if>
            <if test="type != null">type,</if>
            <if test="areaId != null">area_id,</if>
            <if test="boundary != null">boundary,</if>
            <if test="boundaryStr != null">boundary_str,</if>
            <if test="error != null">error,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude,</if>
            <if test="createTime != null">create_time,</if>
            <if test="picture != null">picture,</if>
            <if test="remark != null">remark,</if>
            <if test="status != null and status != ''">status,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">#{name},</if>
            <if test="type != null">#{type},</if>
            <if test="areaId != null">#{areaId},</if>
            <if test="boundary != null">ST_GeomFromText(#{boundary}),</if>
            <if test="boundaryStr != null">#{boundaryStr},</if>
            <if test="error != null">#{error},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="picture != null">#{picture},</if>
            <if test="remark != null">#{remark},</if>
            <if test="status != null and status != ''">#{status},</if>
         </trim>
    </insert>

    <update id="updateAreaSub" parameterType="AreaSub">
        update bst_area_sub
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.name != null">name = #{data.name},</if>
        <if test="data.type != null">type = #{data.type},</if>
        <if test="data.areaId != null">area_id = #{data.areaId},</if>
        <if test="data.boundary != null">boundary = ST_GeomFromText(#{data.boundary}),</if>
        <if test="data.boundaryStr != null">boundary_str = #{data.boundaryStr},</if>
        <if test="data.error != null">error = #{data.error},</if>
        <if test="data.longitude != null">longitude = #{data.longitude},</if>
        <if test="data.latitude != null">latitude = #{data.latitude},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.picture != null">picture = #{data.picture},</if>
        <if test="data.remark != null">remark = #{data.remark},</if>
        <if test="data.status != null and data.status != ''">status = #{data.status},</if>
    </sql>

    <delete id="deleteAreaSubById" parameterType="Long">
        delete from bst_area_sub where id = #{id}
    </delete>

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

    <!-- selectTypeCountGroupByAreaId -->

    <select id="selectTypeCountGroupByAreaId" resultType="AreaSubCountTypeByAreaVO">
        select
            bas.area_id,
            bas.type,
            count(bas.id) as `count`
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
        group by bas.area_id, bas.type
    </select>

    <!-- selectIdByQuery --> 

    <select id="selectIdByQuery" resultType="Long">
        select bas.id
        <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- logicDel --> 

    <update id="logicDel">
        update bst_area_sub 
        set deleted = true 
        where id in
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
            and deleted = false
    </update>
</mapper>
