<?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="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="areaShapeFlag"    column="area_shape_flag"    />
        <result property="areaRange"    column="area_range"    />
        <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"    />
    </resultMap>
    <resultMap id="EleMonitorDeviceSumVoResult" type="EleMonitorDeviceSumVo">
        <result property="sceneId" column="scene_id" />
        <result property="monitorUnitId" column="monitor_unit_id"/>
        <result property="electronicFenceId" column="electronic_fence_id"/>
        <result property="sum" column="sum"/>
    </resultMap>

    <sql id="selectHwElectronicFenceVo">
        select electronic_fence_id, electronic_fence_name, scene_id, fence_type, effective_time_flag, time_zone, effective_time, trigger_status, area_shape_flag, area_range, 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="selectElectronicByScene" resultMap="HwElectronicFenceResult">
        <include refid="selectHwElectronicFenceVo"/>
        <where>
            <if test="sceneId !=null and sceneId !=''">and scene_id = #{sceneId}</if>
        </where>
    </select>
<!--根据传入场景查询电子围栏的类型-->
    <select id="selectEleTypeBySceneId" parameterType="List" resultType="Integer">
        SELECT DISTINCT
	target_type
FROM
	hw_fence_target
WHERE
	electronic_fence_id = (
	SELECT
		electronic_fence_id
	FROM
		hw_electronic_fence
	WHERE
	scene_id = #{sceneId}
	)
    </select>
<!--    查询监控单元下拥有总的设备量 新增电子围栏id分层-->
    <select id="selectSumsFromMonitor" parameterType="Long" resultMap="EleMonitorDeviceSumVoResult">

        SELECT
	monitor_unit_id,b.electronic_fence_id,
	count(*) AS sum
FROM
	hw_device AS a
	LEFT JOIN (
	SELECT
		b.target_id,b.electronic_fence_id
	FROM
		hw_electronic_fence AS a
		LEFT JOIN hw_fence_target AS b ON a.electronic_fence_id = b.target_id
	WHERE
		a.scene_id = #{sceneId}
		AND a.fence_type = '2'
	) AS b ON a.monitor_unit_id = b.target_id
GROUP BY
	monitor_unit_id,b.electronic_fence_id
    </select>
<!--        //查询该场景下拥有电子围栏的设备的数量并按电子围栏分组 todo 获取target里的数量 已发布 -->
    <select id="selectSumsFromMonitorByElectronicFence" parameterType="Long" resultMap="EleMonitorDeviceSumVoResult">
select electronic_fence_id,count(*)as sum from hw_fence_target where electronic_fence_id = (	SELECT electronic_fence_id from hw_electronic_fence where scene_id = #{sceneId}
)GROUP BY electronic_fence_id
    </select>
    <select id="selectDevicesFromEleBySceneId" parameterType="Long" resultMap="EleMonitorDeviceSumVoResult">
        SELECT
	d.electronic_fence_id,
	count(*) AS sum
FROM
	hw_device AS c
	LEFT JOIN hw_fence_target AS d ON c.device_id = d.target_id
WHERE
	device_id = (
	SELECT
		target_id
	FROM
		hw_fence_target AS a
		LEFT JOIN ( SELECT * FROM hw_electronic_fence WHERE scene_id = #{sceneId} AND fence_type = '1' ) AS b ON a.electronic_fence_id = b.electronic_fence_id
	)
GROUP BY
	d.electronic_fence_id
    </select>
<!---->
    <select id="selectEleOnlyMonitorBySceneId" parameterType="Long" resultMap="EleMonitorDeviceSumVoResult">
		select b.electronic_fence_id,count(*) from hw_device as a
		left join hw_fence_target as b on a.device_id = b.target_id
		where a.scene_id = #{sceneId} and a.device_status ='1' group by b.electronic_fence_id
    </select>
    <select id="selectEleOnlyScene" parameterType="Long" resultType="Integer">
        select count(*) from hw_device where scene_id = #{sceneId}  where device_status = '1'
    </select>
    <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="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="areaShapeFlag != null  and areaShapeFlag != ''"> and area_shape_flag = #{areaShapeFlag}</if>
            <if test="areaRange != null  and areaRange != ''"> and area_range = #{areaRange}</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="HwElectronicFenceResult">
        <include refid="selectHwElectronicFenceVo"/>
        where 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="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="areaShapeFlag != null and areaShapeFlag != ''">area_shape_flag,</if>
            <if test="areaRange != null">area_range,</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="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="areaShapeFlag != null and areaShapeFlag != ''">#{areaShapeFlag},</if>
            <if test="areaRange != null">#{areaRange},</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="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="areaShapeFlag != null and areaShapeFlag != ''">area_shape_flag = #{areaShapeFlag},</if>
            <if test="areaRange != null">area_range = #{areaRange},</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>
</mapper>
