<?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.ems.mapper.EmsDeviceMapper">

    <resultMap type="com.ruoyi.ems.domain.EmsDevice" id="EmsDeviceResult">
        <result property="id"    column="id"    />
        <result property="deviceCode"          column="device_code"    />
        <result property="deviceName"          column="device_name"    />
        <result property="deviceCategory"      column="device_category"    />
        <result property="deviceCategoryName"  column="device_category_name"    />
        <result property="deviceBrand"         column="device_brand"    />
        <result property="deviceSpec"          column="device_spec"    />
        <result property="deviceStatus"        column="device_status"    />
        <result property="location"            column="location"    />
        <result property="locationRef"         column="location_ref"    />
        <result property="areaCode"            column="area_code"    />
        <result property="deviceModel"         column="device_model"    />
        <result property="psCode"              column="ps_code"    />
        <result property="psName"              column="ps_name"    />
        <result property="refFacs"             column="ref_facs"    />
        <result property="refFacsName"         column="ref_facs_name"    />
        <result property="subsystemCode"       column="subsystem_code"    />
        <result property="subsystemName"       column="subsystem_name"    />
        <result property="createTime"          column="create_time"    />
        <result property="updateTime"          column="update_time"    />
    </resultMap>

    <sql id="selectDeviceVo">
        select
            d.`id`, d.`device_code`, d.`device_name`, d.`device_category`, d.`device_brand`, d.`device_spec`, d.`device_status`, d.`location`, d.`location_ref`, d.`area_code`, d.`device_model`, d.`ref_facs`, d.`subsystem_code`, d.`ps_code`, d.`create_time`, d.`update_time`
        from adm_ems_device d
    </sql>

    <sql id="selectDetailDeviceVo">
        select
            d.`id`, d.`device_code`, d.`device_name`, d.`device_category`, d.`device_brand`, d.`device_spec`, d.`device_status`, d.`location`, d.`location_ref`, d.`area_code`, d.`device_model`, d.`ref_facs`, d.`subsystem_code`, d.`ps_code`, d.`create_time`, d.`update_time`,
            sc.`name` as device_category_name,
            dp.`ps_name`,
            s.`system_name` as subsystem_name,
            f.`facs_name` as ref_facs_name
        from adm_ems_device d
            LEFT JOIN dim_ems_facs_subcategory sc ON d.`device_category` = sc.`code`
            LEFT JOIN dim_ems_dev_process dp ON d.`ps_code` = dp.`ps_code`
            LEFT JOIN adm_ems_subsystem s ON d.`subsystem_code` = s.`system_code`
            LEFT JOIN adm_ems_facs f ON d.`ref_facs` = f.`facs_code`
    </sql>

    <select id="selectDeviceList" parameterType="com.ruoyi.ems.model.QueryDevice" resultMap="EmsDeviceResult">
        <include refid="selectDeviceVo"/>
        <where>
            <if test="deviceCode != null  and deviceCode != ''"> and d.`device_code` = #{deviceCode}</if>
            <if test="areaCode != null  and areaCode != ''"> and d.`area_code` = #{areaCode}</if>
            <if test="deviceName != null  and deviceName != ''"> and d.`device_name` like concat('%', #{deviceName}, '%')</if>
            <if test="deviceSubCategory != null and deviceSubCategory !=''"> and d.`device_category` = #{deviceSubCategory}</if>
            <if test="deviceStatus != null "> and d.`device_status` = #{deviceStatus}</if>
            <if test="locationRef != null  and locationRef != ''"> and d.`location_ref` = #{locationRef}</if>
            <if test="refFacs != null  and refFacs != ''"> and d.`ref_facs` = #{refFacs}</if>
            <if test="psCode != null  and psCode != ''"> and d.`ps_code` = #{psCode}</if>
            <if test="deviceModel != null  and deviceModel != ''"> and d.`device_model` = #{device_model}</if>
            <if test="subsystemCode != null  and subsystemCode != ''"> and d.`subsystem_code` = #{subsystemCode}</if>
        </where>
    </select>

    <select id="selectByFlowRel" parameterType="com.ruoyi.ems.model.QueryDevice" resultMap="EmsDeviceResult">
        <include refid="selectDeviceVo"/>
        LEFT JOIN adm_ems_flow_rel rel ON d.`device_code` = rel.`input_obj`
        <where>
            <if test="areaCode != null  and areaCode != ''"> and d.`area_code` = #{areaCode}</if>
            <if test="locationRef != null  and locationRef != ''"> and d.`location_ref` = #{locationRef}</if>
            <if test="deviceSubCategory != null and deviceSubCategory !=''"> and d.`device_category` = #{deviceSubCategory}</if>
            <if test="psCode != null  and psCode != ''"> and d.`ps_code` = #{psCode}</if>
            <if test="psCodes != null"> and d.`ps_code` in
                (
                  <foreach collection="psCodes" item="item" index="index" separator=",">
                      #{item}
                  </foreach>
                )
            </if>
            <if test="subsystemCode != null  and subsystemCode != ''"> and d.`subsystem_code` = #{subsystemCode}</if>
            <if test="refFacs != null  and refFacs != ''"> and d.`ref_facs` = #{refFacs}</if>
            <if test="upstreamObjType != null"> and rel.`export_obj_type` = #{upstreamObjType}</if>
            <if test="upstreamObjCode != null  and upstreamObjCode != ''"> and rel.`export_obj` = #{upstreamObjCode}</if>
        </where>
    </select>

    <select id="selectDetailList" parameterType="com.ruoyi.ems.model.QueryDevice" resultMap="EmsDeviceResult">
        <include refid="selectDetailDeviceVo"/>
        <where>
            <if test="deviceCode != null  and deviceCode != ''"> and d.`device_code` = #{deviceCode}</if>
            <if test="areaCode != null  and areaCode != ''"> and d.`area_code` = #{areaCode}</if>
            <if test="deviceName != null  and deviceName != ''"> and d.`device_name` like concat('%', #{deviceName}, '%')</if>
            <if test="deviceCategory != null and deviceCategory !=''"> and sc.`parent_code` = #{deviceCategory}</if>
            <if test="deviceSubCategory != null and deviceSubCategory !=''"> and d.`device_category` = #{deviceSubCategory}</if>
            <if test="deviceStatus != null "> and d.`device_status` = #{deviceStatus}</if>
            <if test="locationRef != null and locationRef != ''"> and d.`location_ref` = #{locationRef}</if>
            <if test="refFacs != null  and refFacs != ''"> and d.`ref_facs` = #{refFacs}</if>
            <if test="psCode != null  and psCode != ''"> and d.`ps_code` = #{psCode}</if>
            <if test="deviceModel != null  and deviceModel != ''"> and d.`device_model` = #{device_model}</if>
            <if test="subsystemCode != null  and subsystemCode != ''"> and d.`subsystem_code` = #{subsystemCode}</if>
        </where>
    </select>

    <!-- 递归查询 区域/建筑/区块 下的设备 -->
    <select id="selectByAreaTree" parameterType="com.ruoyi.ems.model.QueryDevice" resultMap="EmsDeviceResult">
        <include refid="selectDetailDeviceVo"/>
        <where>
            <if test="deviceCode != null  and deviceCode != ''"> and d.`device_code` = #{deviceCode}</if>
            <if test="deviceName != null  and deviceName != ''"> and d.`device_name` like concat('%', #{deviceName}, '%')</if>
            <if test="deviceCategory != null and deviceCategory !=''"> and sc.`parent_code` = #{deviceCategory}</if>
            <if test="deviceSubCategory != null and deviceSubCategory !=''"> and d.`device_category` = #{deviceSubCategory}</if>
            <if test="refFacs != null  and refFacs != ''"> and d.`ref_facs` = #{refFacs}</if>
            <if test="psCode != null  and psCode != ''"> and d.`ps_code` = #{psCode}</if>
            <if test="deviceModel != null  and deviceModel != ''"> and d.`device_model` = #{device_model}</if>
            <if test="subsystemCode != null  and subsystemCode != ''"> and d.`subsystem_code` = #{subsystemCode}</if>
            <if test="locationRef != null and locationRef != '' and areaCodes != null">
                and d.`location_ref` in
                <foreach item="areaCode" collection="areaCodes" open="(" separator="," close=")">
                    #{areaCode}
                </foreach>
            </if>
        </where>
        ORDER BY d.`location_ref`
    </select>

    <select id="selectEmsDeviceById" parameterType="Long" resultMap="EmsDeviceResult">
        <include refid="selectDeviceVo"/>
        where d.`id` = #{id}
    </select>

    <select id="selectDeviceByCode" parameterType="java.lang.String" resultMap="EmsDeviceResult">
        <include refid="selectDeviceVo"/>
        where d.`device_code` = #{code}
    </select>

    <select id="selectDetailByCode" parameterType="java.lang.String" resultMap="EmsDeviceResult">
        <include refid="selectDetailDeviceVo"/>
        where d.`device_code` = #{code}
    </select>

    <insert id="insertEmsDevice" parameterType="com.ruoyi.ems.domain.EmsDevice" useGeneratedKeys="true" keyProperty="id">
        insert into adm_ems_device
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deviceCode != null and deviceCode != ''">device_code,</if>
            <if test="deviceName != null and deviceName != ''">device_name,</if>
            <if test="deviceCategory != null and deviceCategory !=''">device_category,</if>
            <if test="deviceBrand != null">device_brand,</if>
            <if test="deviceSpec != null">device_spec,</if>
            <if test="deviceStatus != null">device_status,</if>
            <if test="location != null">location,</if>
            <if test="locationType != null">location_type,</if>
            <if test="locationRef != null">location_ref,</if>
            <if test="areaCode != null">area_code,</if>
            <if test="deviceModel !=null">device_model,</if>
            <if test="refFacs != null">ref_facs,</if>
            <if test="psCode != null">ps_code,</if>
            <if test="subsystemCode != null">subsystem_code,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deviceCode != null and deviceCode != ''">#{deviceCode},</if>
            <if test="deviceName != null and deviceName != ''">#{deviceName},</if>
            <if test="deviceCategory != null and deviceCategory !=''">#{deviceCategory},</if>
            <if test="deviceBrand != null">#{deviceBrand},</if>
            <if test="deviceSpec != null">#{deviceSpec},</if>
            <if test="deviceStatus != null">#{deviceStatus},</if>
            <if test="location != null">#{location},</if>
            <if test="locationType != null">#{locationType},</if>
            <if test="locationRef != null">#{locationRef},</if>
            <if test="areaCode != null">#{areaCode},</if>
            <if test="areaAncestors !=null">#{areaAncestors},</if>
            <if test="deviceModel !=null">#{deviceModel},</if>
            <if test="refFacs != null">#{refFacs},</if>
            <if test="psCode != null">#{psCode},</if>
            <if test="subsystemCode != null">#{subsystemCode},</if>
         </trim>
    </insert>

    <update id="updateEmsDevice" parameterType="com.ruoyi.ems.domain.EmsDevice">
        update adm_ems_device
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceCode != null and deviceCode != ''">device_code = #{deviceCode},</if>
            <if test="deviceName != null and deviceName != ''">device_name = #{deviceName},</if>
            <if test="deviceCategory != null and deviceCategory !=''">device_category = #{deviceCategory},</if>
            <if test="deviceBrand != null">device_brand = #{deviceBrand},</if>
            <if test="deviceSpec != null">device_spec = #{deviceSpec},</if>
            <if test="deviceStatus != null">device_status = #{deviceStatus},</if>
            <if test="location != null">location = #{location},</if>
            <if test="locationType != null">location_type = #{locationType},</if>
            <if test="locationRef != null">location_ref = #{locationRef},</if>
            <if test="areaCode != null">area_code = #{areaCode},</if>
            <if test="deviceModel !=null">device_model = #{deviceModel},</if>
            <if test="refFacs != null">ref_facs = #{refFacs},</if>
            <if test="psCode != null">ps_code = #{psCode},</if>
            <if test="subsystemCode != null">subsystem_code = #{subsystemCode},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteEmsDeviceById" parameterType="Long">
        delete from adm_ems_device where id = #{id}
    </delete>

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

    <select id="cntDeviceStatus">
        SELECT
        device_status deviceStatus,
        count( id ) cnt
        FROM
        adm_ems_device
        <where>
            <if test="areaCode !=null and areaCode!=''">
                area_code = #{areaCode}
            </if>
        </where>
        GROUP BY
        device_status
    </select>

    <select id="calcDeviceOnlineSummary">
        SELECT
            device_status  deviceStatus,
            sc.`name` typeName,
            count( devc.id ) total,
            SUM( CASE WHEN device_status = '0' THEN 1 ELSE 0 END ) AS onlineCount
        FROM
            adm_ems_device devc
            INNER JOIN dim_ems_facs_subcategory sc ON devc.device_category = sc.`code`
            <if test="areaCode !=null and areaCode!=''">
                LEFT JOIN adm_area area ON devc.location_ref = area.area_code
            </if>
        <where>
            <if test="areaCode !=null and areaCode!=''">
                area.ancestors like CONCAT('%/',#{areaCode},'/%')
            </if>
        </where>
        GROUP BY
           device_category
    </select>

</mapper>
