<?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.business.mapper.HwElectronicFenceMapper">
    
    <resultMap type="HwElectronicFence" id="HwElectronicFenceResult">
        <result property="electronicFenceId"    column="electronic_fence_id"    />
        <result property="electronicFenceName"    column="electronic_fence_name"    />
        <result property="tenantId"    column="tenant_id"    />
        <result property="sceneId"    column="scene_id"    />
        <result property="fenceType"    column="fence_type"    />
        <result property="effectiveTimeFlag"    column="effective_time_flag"    />
        <result property="timeZone"    column="time_zone"    />
        <result property="effectiveTime"    column="effective_time"    />
        <result property="triggerStatus"    column="trigger_status"    />
        <result property="fencePushFlag"    column="fence_push_flag"    />
        <result property="fencePushContent"    column="fence_push_content"    />
        <result property="fenceRecoverContent"    column="fence_recover_content"    />
        <result property="remark"    column="remark"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="fenceField"    column="fence_field"    />
        <result property="sceneName"    column="scene_name"    />
    </resultMap>

    <resultMap id="HwElectronicFenceHwFenceTargetResult" type="HwElectronicFence" extends="HwElectronicFenceResult">
        <collection property="hwFenceTargetList" notNullColumn="sub_target_id" javaType="java.util.List" resultMap="HwFenceTargetResult" />
    </resultMap>

    <resultMap type="HwFenceTarget" id="HwFenceTargetResult">
        <result property="electronicFenceId"    column="sub_electronic_fence_id"    />
        <result property="targetType"    column="sub_target_type"    />
        <result property="targetId"    column="sub_target_id"    />
    </resultMap>

    <sql id="selectHwElectronicFenceVo">
        select electronic_fence_id, electronic_fence_name, tenant_id, scene_id, fence_type, effective_time_flag, time_zone, effective_time, trigger_status, fence_push_flag, fence_push_content, fence_recover_content, remark, create_by, create_time, update_by, update_time, fence_field from hw_electronic_fence
    </sql>

    <select id="selectHwElectronicFenceList" parameterType="HwElectronicFence" resultMap="HwElectronicFenceResult">
        <include refid="selectHwElectronicFenceVo"/>
        <where>  
            <if test="electronicFenceName != null  and electronicFenceName != ''"> and electronic_fence_name like concat('%', #{electronicFenceName}, '%')</if>
            <if test="tenantId != null "> and tenant_id = #{tenantId}</if>
            <if test="sceneId != null "> and scene_id = #{sceneId}</if>
            <if test="fenceType != null  and fenceType != ''"> and fence_type = #{fenceType}</if>
            <if test="effectiveTimeFlag != null  and effectiveTimeFlag != ''"> and effective_time_flag = #{effectiveTimeFlag}</if>
            <if test="timeZone != null  and timeZone != ''"> and time_zone = #{timeZone}</if>
            <if test="effectiveTime != null  and effectiveTime != ''"> and effective_time = #{effectiveTime}</if>
            <if test="triggerStatus != null  and triggerStatus != ''"> and trigger_status = #{triggerStatus}</if>
            <if test="fencePushFlag != null  and fencePushFlag != ''"> and fence_push_flag = #{fencePushFlag}</if>
            <if test="fencePushContent != null  and fencePushContent != ''"> and fence_push_content = #{fencePushContent}</if>
            <if test="fenceRecoverContent != null  and fenceRecoverContent != ''"> and fence_recover_content = #{fenceRecoverContent}</if>
            <if test="fenceField != null  and fenceField != ''"> and fence_field = #{fenceField}</if>
        </where>
    </select>
    
    <select id="selectHwElectronicFenceByElectronicFenceId" parameterType="Long" resultMap="HwElectronicFenceHwFenceTargetResult">
        select a.electronic_fence_id, a.electronic_fence_name, a.tenant_id, a.scene_id, a.fence_type, a.effective_time_flag, a.time_zone, a.effective_time, a.trigger_status, a.fence_push_flag, a.fence_push_content, a.fence_recover_content, a.remark, a.create_by, a.create_time, a.update_by, a.update_time, a.fence_field,
 b.electronic_fence_id as sub_electronic_fence_id, b.target_type as sub_target_type, b.target_id as sub_target_id
        from hw_electronic_fence a
        left join hw_fence_target b on b.electronic_fence_id = a.electronic_fence_id
        where a.electronic_fence_id = #{electronicFenceId}
    </select>
        
    <insert id="insertHwElectronicFence" parameterType="HwElectronicFence" useGeneratedKeys="true" keyProperty="electronicFenceId">
        insert into hw_electronic_fence
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="electronicFenceName != null and electronicFenceName != ''">electronic_fence_name,</if>
            <if test="tenantId != null">tenant_id,</if>
            <if test="sceneId != null">scene_id,</if>
            <if test="fenceType != null and fenceType != ''">fence_type,</if>
            <if test="effectiveTimeFlag != null and effectiveTimeFlag != ''">effective_time_flag,</if>
            <if test="timeZone != null">time_zone,</if>
            <if test="effectiveTime != null and effectiveTime != ''">effective_time,</if>
            <if test="triggerStatus != null and triggerStatus != ''">trigger_status,</if>
            <if test="fencePushFlag != null and fencePushFlag != ''">fence_push_flag,</if>
            <if test="fencePushContent != null">fence_push_content,</if>
            <if test="fenceRecoverContent != null">fence_recover_content,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="fenceField != null">fence_field,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="electronicFenceName != null and electronicFenceName != ''">#{electronicFenceName},</if>
            <if test="tenantId != null">#{tenantId},</if>
            <if test="sceneId != null">#{sceneId},</if>
            <if test="fenceType != null and fenceType != ''">#{fenceType},</if>
            <if test="effectiveTimeFlag != null and effectiveTimeFlag != ''">#{effectiveTimeFlag},</if>
            <if test="timeZone != null">#{timeZone},</if>
            <if test="effectiveTime != null and effectiveTime != ''">#{effectiveTime},</if>
            <if test="triggerStatus != null and triggerStatus != ''">#{triggerStatus},</if>
            <if test="fencePushFlag != null and fencePushFlag != ''">#{fencePushFlag},</if>
            <if test="fencePushContent != null">#{fencePushContent},</if>
            <if test="fenceRecoverContent != null">#{fenceRecoverContent},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="fenceField != null">#{fenceField},</if>
         </trim>
    </insert>

    <update id="updateHwElectronicFence" parameterType="HwElectronicFence">
        update hw_electronic_fence
        <trim prefix="SET" suffixOverrides=",">
            <if test="electronicFenceName != null and electronicFenceName != ''">electronic_fence_name = #{electronicFenceName},</if>
            <if test="tenantId != null">tenant_id = #{tenantId},</if>
            <if test="sceneId != null">scene_id = #{sceneId},</if>
            <if test="fenceType != null and fenceType != ''">fence_type = #{fenceType},</if>
            <if test="effectiveTimeFlag != null and effectiveTimeFlag != ''">effective_time_flag = #{effectiveTimeFlag},</if>
            <if test="timeZone != null">time_zone = #{timeZone},</if>
            <if test="effectiveTime != null and effectiveTime != ''">effective_time = #{effectiveTime},</if>
            <if test="triggerStatus != null and triggerStatus != ''">trigger_status = #{triggerStatus},</if>
            <if test="fencePushFlag != null and fencePushFlag != ''">fence_push_flag = #{fencePushFlag},</if>
            <if test="fencePushContent != null">fence_push_content = #{fencePushContent},</if>
            <if test="fenceRecoverContent != null">fence_recover_content = #{fenceRecoverContent},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="fenceField != null">fence_field = #{fenceField},</if>
        </trim>
        where electronic_fence_id = #{electronicFenceId}
    </update>

    <delete id="deleteHwElectronicFenceByElectronicFenceId" parameterType="Long">
        delete from hw_electronic_fence where electronic_fence_id = #{electronicFenceId}
    </delete>

    <delete id="deleteHwElectronicFenceByElectronicFenceIds" parameterType="String">
        delete from hw_electronic_fence where electronic_fence_id in 
        <foreach item="electronicFenceId" collection="array" open="(" separator="," close=")">
            #{electronicFenceId}
        </foreach>
    </delete>
    
    <delete id="deleteHwFenceTargetByElectronicFenceIds" parameterType="String">
        delete from hw_fence_target where electronic_fence_id in 
        <foreach item="electronicFenceId" collection="array" open="(" separator="," close=")">
            #{electronicFenceId}
        </foreach>
    </delete>

    <select id="selectDeviceCount" parameterType="Long">
        select count(*) as count
        from hw_fence_target a where a.electronic_fence_id = #{electronicFenceId}
    </select>

    <select id="selectDeviceCountByMonitorUnit" parameterType="Long">
        select count(*) as count
        from hw_device hd where hd.device_status=1 and (hd.device_type=2 or hd.device_type=3) and exists (select 1 from hw_fence_target a
            where hd.monitor_unit_id = a.target_id
        and a.electronic_fence_id = #{electronicFenceId})
    </select>

    <select id="selectDeviceCountByScene" parameterType="Long">
        select count(*) as count
        from hw_device hd where hd.device_status=1 and (hd.device_type=2 or hd.device_type=3) and hd.scene_id = #{sceneId}
    </select>


    <delete id="deleteHwFenceTargetByElectronicFenceId" parameterType="Long">
        delete from hw_fence_target where electronic_fence_id = #{electronicFenceId}
    </delete>

    <insert id="batchHwFenceTarget">
        insert into hw_fence_target( electronic_fence_id, target_type, target_id) values
        <foreach item="item" index="index" collection="list" separator=",">
            ( #{item.electronicFenceId}, #{item.targetType}, #{item.targetId})
        </foreach>
    </insert>


    <select id="selectHwElectronicFenceJoinList" parameterType="HwElectronicFence" resultMap="HwElectronicFenceResult">
        select hef.electronic_fence_id,hef.electronic_fence_name,hef.fence_type,hef.trigger_status,
        hs.scene_name
        from hw_electronic_fence hef
        left join hw_scene hs on hef.scene_id = hs.scene_id
        <where>
            <if test="electronicFenceName != null  and electronicFenceName != ''"> and hef.electronic_fence_name like concat('%', #{electronicFenceName}, '%')</if>
            <if test="tenantId != null "> and hef.tenant_id = #{tenantId}</if>
            <if test="sceneId != null "> and hef.scene_id = #{sceneId}</if>
            <if test="fenceType != null  and fenceType != ''"> and hef.fence_type = #{fenceType}</if>
            <if test="effectiveTimeFlag != null  and effectiveTimeFlag != ''"> and hef.effective_time_flag = #{effectiveTimeFlag}</if>
            <if test="timeZone != null  and timeZone != ''"> and hef.time_zone = #{timeZone}</if>
            <if test="effectiveTime != null  and effectiveTime != ''"> and hef.effective_time = #{effectiveTime}</if>
            <if test="triggerStatus != null  and triggerStatus != ''"> and hef.trigger_status = #{triggerStatus}</if>
            <if test="fencePushFlag != null  and fencePushFlag != ''"> and hef.fence_push_flag = #{fencePushFlag}</if>
            <if test="fencePushContent != null  and fencePushContent != ''"> and hef.fence_push_content = #{fencePushContent}</if>
            <if test="fenceRecoverContent != null  and fenceRecoverContent != ''"> and hef.fence_recover_content = #{fenceRecoverContent}</if>
            <if test="fenceField != null  and fenceField != ''"> and hef.fence_field = #{fenceField}</if>
            <!-- 租户数据范围过滤 -->
            ${params.tenantDataScope}
        </where>
    </select>

</mapper>