<?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.area.mapper.AreaMapper">

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

    <sql id="selectAreaVo">
        select
            ba.id,
            ba.user_id,
            ba.name,
            ba.boundary,
            ba.boundary_str,
            ba.longitude,
            ba.latitude,
            ba.contact,
            ba.phone,
            ba.status,
            ba.area_out_outage,
            ba.parking_out_dispatch,
            ba.area_out_dispatch,
            ba.no_riding_outage,
            ba.authentication,
            ba.msg_switch,
            ba.undercharge,
            ba.error,
            ba.agreement,
            ba.deposit,
            ba.dispatch_fee,
            ba.vehicle_management_fee,
            ba.area_time_start,
            ba.area_time_end,
            ba.parking_return,
            ba.area_out_return,
            ba.return_verify,
            ba.upper_left,
            ba.lower_right,
            ba.guide_map,
            ba.guide_switch,
            ba.create_id,
            ba.create_time,
            ba.deleted,
            ba.boundary_distance,
            ba.outage_distance,
            su.nick_name as user_name,
            su.agent_id as agent_id,
            su.user_name as user_phone,
            suc.nick_name as create_name
        from <include refid="searchTables"/>
    </sql>

    <sql id="searchTables">
        bst_area ba
            left join sys_user su on su.user_id = ba.user_id
            left join sys_user suc on suc.user_id = ba.create_id
    </sql>

    <sql id="searchCondition">
        <if test="query.id != null "> and ba.id = #{query.id}</if>
        <if test="query.userId != null "> and ba.user_id = #{query.userId}</if>
        <if test="query.name != null  and query.name != ''"> and ba.name like concat('%', #{query.name}, '%')</if>
        <if test="query.contact != null  and query.contact != ''"> and ba.contact like concat('%', #{query.contact}, '%')</if>
        <if test="query.phone != null  and query.phone != ''"> and ba.phone like concat('%', #{query.phone}, '%')</if>
        <if test="query.status != null  and query.status != ''"> and ba.status = #{query.status}</if>
        <if test="query.areaOutOutage != null "> and ba.area_out_outage = #{query.areaOutOutage}</if>
        <if test="query.parkingOutDispatch != null "> and ba.parking_out_dispatch = #{query.parkingOutDispatch}</if>
        <if test="query.areaOutDispatch != null "> and ba.area_out_dispatch = #{query.areaOutDispatch}</if>
        <if test="query.noRidingOutage != null "> and ba.no_riding_outage = #{query.noRidingOutage}</if>
        <if test="query.authentication != null "> and ba.authentication = #{query.authentication}</if>
        <if test="query.msgSwitch != null "> and ba.msg_switch = #{query.msgSwitch}</if>
        <if test="query.parkingReturn != null "> and ba.parking_return = #{query.parkingReturn}</if>
        <if test="query.areaOutReturn != null "> and ba.area_out_return = #{query.areaOutReturn}</if>
        <if test="query.returnVerify != null "> and ba.return_verify = #{query.returnVerify}</if>
        <if test="query.guideSwitch != null "> and ba.guide_switch = #{query.guideSwitch}</if>
        <if test="query.createId != null "> and ba.create_id = #{query.createId}</if>
        <if test="query.deleted != null "> and ba.deleted = #{query.deleted}</if>
        <if test="query.deleted == null "> and ba.deleted = false</if>
        <if test="query.userName != null  and query.userName != ''"> and su.nick_name like concat('%', #{query.userName}, '%')</if>
        <if test="query.createName != null  and query.createName != ''"> and suc.nick_name like concat('%', #{query.createName}, '%')</if>
        <if test="query.userPhone != null  and query.userPhone != ''"> and su.user_name like concat('%', #{query.userPhone}, '%')</if>
        <if test="query.ids != null and query.ids.size() > 0">
            and ba.id in
            <foreach collection="query.ids" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="query.keyword != null  and query.keyword != ''">
            and (
                ba.name like concat('%', #{query.keyword}, '%') 
            )
        </if>
        ${@com.ruoyi.framework.util.DataScopeUtil@create(query.scope)
            .userSetAlias("ba.user_id,ba.create_id,su.agent_id")
            .areaAlias("ba.id", query.areaPermissions)
            .build()
        }
        ${query.params.dataScope}
    </sql>

    <select id="selectAreaList" parameterType="AreaQuery" resultMap="AreaResult">
        <include refid="selectAreaVo"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <select id="selectAreaById" parameterType="Long" resultMap="AreaResult">
        <include refid="selectAreaVo"/>
        where ba.id = #{id}
    </select>

    <insert id="insertArea" parameterType="Area" useGeneratedKeys="true" keyProperty="id">
        insert into bst_area
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="boundary != null">boundary,</if>
            <if test="boundaryStr != null">boundary_str,</if>
            <if test="longitude != null">longitude,</if>
            <if test="latitude != null">latitude,</if>
            <if test="contact != null">contact,</if>
            <if test="phone != null">phone,</if>
            <if test="status != null and status != ''">status,</if>
            <if test="areaOutOutage != null">area_out_outage,</if>
            <if test="parkingOutDispatch != null">parking_out_dispatch,</if>
            <if test="areaOutDispatch != null">area_out_dispatch,</if>
            <if test="noRidingOutage != null">no_riding_outage,</if>
            <if test="authentication != null">authentication,</if>
            <if test="msgSwitch != null">msg_switch,</if>
            <if test="undercharge != null">undercharge,</if>
            <if test="error != null">error,</if>
            <if test="agreement != null">agreement,</if>
            <if test="deposit != null">deposit,</if>
            <if test="dispatchFee != null">dispatch_fee,</if>
            <if test="vehicleManagementFee != null">vehicle_management_fee,</if>
            <if test="areaTimeStart != null">area_time_start,</if>
            <if test="areaTimeEnd != null">area_time_end,</if>
            <if test="parkingReturn != null">parking_return,</if>
            <if test="areaOutReturn != null">area_out_return,</if>
            <if test="returnVerify != null">return_verify,</if>
            <if test="upperLeft != null">upper_left,</if>
            <if test="lowerRight != null">lower_right,</if>
            <if test="guideMap != null">guide_map,</if>
            <if test="guideSwitch != null">guide_switch,</if>
            <if test="createId != null">create_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="boundaryDistance != null">boundary_distance,</if>
            <if test="outageDistance != null">outage_distance,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="boundary != null">ST_GeomFromText(#{boundary}),</if>
            <if test="boundaryStr != null">#{boundaryStr},</if>
            <if test="longitude != null">#{longitude},</if>
            <if test="latitude != null">#{latitude},</if>
            <if test="contact != null">#{contact},</if>
            <if test="phone != null">#{phone},</if>
            <if test="status != null and status != ''">#{status},</if>
            <if test="areaOutOutage != null">#{areaOutOutage},</if>
            <if test="parkingOutDispatch != null">#{parkingOutDispatch},</if>
            <if test="areaOutDispatch != null">#{areaOutDispatch},</if>
            <if test="noRidingOutage != null">#{noRidingOutage},</if>
            <if test="authentication != null">#{authentication},</if>
            <if test="msgSwitch != null">#{msgSwitch},</if>
            <if test="undercharge != null">#{undercharge},</if>
            <if test="error != null">#{error},</if>
            <if test="agreement != null">#{agreement},</if>
            <if test="deposit != null">#{deposit},</if>
            <if test="dispatchFee != null">#{dispatchFee},</if>
            <if test="vehicleManagementFee != null">#{vehicleManagementFee},</if>
            <if test="areaTimeStart != null">#{areaTimeStart},</if>
            <if test="areaTimeEnd != null">#{areaTimeEnd},</if>
            <if test="parkingReturn != null">#{parkingReturn},</if>
            <if test="areaOutReturn != null">#{areaOutReturn},</if>
            <if test="returnVerify != null">#{returnVerify},</if>
            <if test="upperLeft != null">#{upperLeft},</if>
            <if test="lowerRight != null">#{lowerRight},</if>
            <if test="guideMap != null">#{guideMap},</if>
            <if test="guideSwitch != null">#{guideSwitch},</if>
            <if test="createId != null">#{createId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="boundaryDistance != null">#{boundaryDistance},</if>
            <if test="outageDistance != null">#{outageDistance},</if>
         </trim>
    </insert>

    <update id="updateArea" parameterType="Area">
        update bst_area
        <trim prefix="SET" suffixOverrides=",">
            <include refid="updateColumns"/>
        </trim>
        where id = #{data.id}
    </update>

    <sql id="updateColumns">
        <if test="data.userId != null">user_id = #{data.userId},</if>
        <if test="data.name != null and data.name != ''">name = #{data.name},</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.longitude != null">longitude = #{data.longitude},</if>
        <if test="data.latitude != null">latitude = #{data.latitude},</if>
        <if test="data.contact != null">contact = #{data.contact},</if>
        <if test="data.phone != null">phone = #{data.phone},</if>
        <if test="data.status != null and data.status != ''">status = #{data.status},</if>
        <if test="data.areaOutOutage != null">area_out_outage = #{data.areaOutOutage},</if>
        <if test="data.parkingOutDispatch != null">parking_out_dispatch = #{data.parkingOutDispatch},</if>
        <if test="data.areaOutDispatch != null">area_out_dispatch = #{data.areaOutDispatch},</if>
        <if test="data.noRidingOutage != null">no_riding_outage = #{data.noRidingOutage},</if>
        <if test="data.authentication != null">authentication = #{data.authentication},</if>
        <if test="data.msgSwitch != null">msg_switch = #{data.msgSwitch},</if>
        <if test="data.undercharge != null">undercharge = #{data.undercharge},</if>
        <if test="data.error != null">error = #{data.error},</if>
        <if test="data.agreement != null">agreement = #{data.agreement},</if>
        <if test="data.deposit != null">deposit = #{data.deposit},</if>
        <if test="data.dispatchFee != null">dispatch_fee = #{data.dispatchFee},</if>
        <if test="data.vehicleManagementFee != null">vehicle_management_fee = #{data.vehicleManagementFee},</if>
        <if test="data.areaTimeStart != null">area_time_start = #{data.areaTimeStart},</if>
        <if test="data.areaTimeEnd != null">area_time_end = #{data.areaTimeEnd},</if>
        <if test="data.parkingReturn != null">parking_return = #{data.parkingReturn},</if>
        <if test="data.areaOutReturn != null">area_out_return = #{data.areaOutReturn},</if>
        <if test="data.returnVerify != null">return_verify = #{data.returnVerify},</if>
        <if test="data.upperLeft != null">upper_left = #{data.upperLeft},</if>
        <if test="data.lowerRight != null">lower_right = #{data.lowerRight},</if>
        <if test="data.guideMap != null">guide_map = #{data.guideMap},</if>
        <if test="data.guideSwitch != null">guide_switch = #{data.guideSwitch},</if>
        <if test="data.createId != null">create_id = #{data.createId},</if>
        <if test="data.createTime != null">create_time = #{data.createTime},</if>
        <if test="data.boundaryDistance != null">boundary_distance = #{data.boundaryDistance},</if>
        <if test="data.outageDistance != null">outage_distance = #{data.outageDistance},</if>
    </sql>

    <delete id="deleteAreaById" parameterType="Long">
        delete from bst_area where id = #{id}
    </delete>

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

    <!-- selectIdByQuery --> 

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

    <select id="selectAreaByStoreId" resultType="java.lang.Long">
        select id from bst_area where user_id = #{storeId}
    </select>

    <!-- logicDel --> 

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

    <!-- selectCount --> 

    <select id="selectCount" resultType="Integer">
        select count(ba.id)
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>

    <!-- selectSimpleList --> 

    <select id="selectSimpleList">
        select 
            ba.id,
            ba.name
        from <include refid="searchTables"/>
        <where>
            <include refid="searchCondition"/>
        </where>
    </select>
    
</mapper>
