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

    <resultMap type="EmsDevicesSetting" id="EmsDevicesSettingResult">
        <result property="id"    column="id"    />
        <result property="deviceName"    column="device_name"    />
        <result property="deviceType"    column="device_type"    />
        <result property="slaveId"    column="slave_id"    />
        <result property="timeoutMs"    column="timeout_ms"    />
        <result property="retries"    column="retries"    />
        <result property="ipAddress"    column="ip_address"    />
        <result property="ipPort"    column="ip_port"    />
        <result property="serialPort"    column="serial_port"    />
        <result property="baudRate"    column="baud_rate"    />
        <result property="dataBits"    column="data_bits"    />
        <result property="stopBits"    column="stop_bits"    />
        <result property="parity"    column="parity"    />
        <result property="description"    column="description"    />
        <result property="createdAt"    column="created_at"    />
        <result property="updatedAt"    column="updated_at"    />
        <result property="siteId"    column="site_id"    />
        <result property="communicationStatus"    column="communication_status"    />
        <result property="deviceId"    column="device_id"    />
        <result property="parentId"    column="parent_id"    />
        <result property="deviceCategory"    column="device_category"    />
        <result property="deviceStatus"    column="device_status"    />
        <result property="workStatus"    column="work_status"    />
        <result property="pictureUrl"    column="picture_url"    />
    </resultMap>

    <sql id="selectEmsDevicesSettingVo">
        select id, device_name, device_type, slave_id, timeout_ms, retries, ip_address, ip_port, serial_port, baud_rate, data_bits, stop_bits, parity, description, created_at, updated_at, site_id, communication_status, device_id, parent_id, device_category,device_status, picture_url,work_status from ems_devices_setting
    </sql>

    <select id="selectEmsDevicesSettingList" parameterType="EmsDevicesSetting" resultMap="EmsDevicesSettingResult">
        <include refid="selectEmsDevicesSettingVo"/>
        <where>
            <if test="deviceName != null  and deviceName != ''"> and device_name like concat('%', #{deviceName}, '%')</if>
            <if test="deviceType != null  and deviceType != ''"> and device_type = #{deviceType}</if>
            <if test="slaveId != null "> and slave_id = #{slaveId}</if>
            <if test="timeoutMs != null "> and timeout_ms = #{timeoutMs}</if>
            <if test="retries != null "> and retries = #{retries}</if>
            <if test="ipAddress != null  and ipAddress != ''"> and ip_address = #{ipAddress}</if>
            <if test="ipPort != null "> and ip_port = #{ipPort}</if>
            <if test="serialPort != null  and serialPort != ''"> and serial_port = #{serialPort}</if>
            <if test="baudRate != null "> and baud_rate = #{baudRate}</if>
            <if test="dataBits != null "> and data_bits = #{dataBits}</if>
            <if test="stopBits != null "> and stop_bits = #{stopBits}</if>
            <if test="parity != null  and parity != ''"> and parity = #{parity}</if>
            <if test="description != null  and description != ''"> and description = #{description}</if>
            <if test="createdAt != null "> and created_at = #{createdAt}</if>
            <if test="updatedAt != null "> and updated_at = #{updatedAt}</if>
            <if test="siteId != null  and siteId != ''"> and site_id = #{siteId}</if>
            <if test="communicationStatus != null  and communicationStatus != ''"> and communication_status = #{communicationStatus}</if>
            <if test="deviceId != null  and deviceId != ''"> and device_id = #{deviceId}</if>
            <if test="parentId != null  and parentId != ''"> and parent_id = #{parentId}</if>
            <if test="deviceCategory != null  and deviceCategory != ''"> and device_category = #{deviceCategory}</if>
            <if test="deviceStatus != null  and deviceStatus != ''"> and device_status = #{deviceStatus}</if>
            <if test="workStatus != null  and workStatus != ''"> and work_status = #{workStatus}</if>
            <if test="pictureUrl != null  and pictureUrl != ''"> and picture_url = #{pictureUrl}</if>
        </where>
    </select>

    <select id="selectEmsDevicesSettingById" parameterType="Long" resultMap="EmsDevicesSettingResult">
        <include refid="selectEmsDevicesSettingVo"/>
        where id = #{id}
    </select>

    <insert id="insertEmsDevicesSetting" parameterType="EmsDevicesSetting" useGeneratedKeys="true" keyProperty="id">
        insert into ems_devices_setting
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deviceName != null and deviceName != ''">device_name,</if>
            <if test="deviceType != null and deviceType != ''">device_type,</if>
            <if test="slaveId != null">slave_id,</if>
            <if test="timeoutMs != null">timeout_ms,</if>
            <if test="retries != null">retries,</if>
            <if test="ipAddress != null">ip_address,</if>
            <if test="ipPort != null">ip_port,</if>
            <if test="serialPort != null">serial_port,</if>
            <if test="baudRate != null">baud_rate,</if>
            <if test="dataBits != null">data_bits,</if>
            <if test="stopBits != null">stop_bits,</if>
            <if test="parity != null">parity,</if>
            <if test="description != null">description,</if>
            <if test="createdAt != null">created_at,</if>
            <if test="updatedAt != null">updated_at,</if>
            <if test="siteId != null">site_id,</if>
            <if test="communicationStatus != null">communication_status,</if>
            <if test="deviceId != null and deviceId != ''">device_id,</if>
            <if test="parentId != null">parent_id,</if>
            <if test="deviceCategory != null">device_category,</if>
            <if test="deviceStatus != null">device_status,</if>
            <if test="workStatus != null">work_status,</if>
            <if test="pictureUrl != null">picture_url,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deviceName != null and deviceName != ''">#{deviceName},</if>
            <if test="deviceType != null and deviceType != ''">#{deviceType},</if>
            <if test="slaveId != null">#{slaveId},</if>
            <if test="timeoutMs != null">#{timeoutMs},</if>
            <if test="retries != null">#{retries},</if>
            <if test="ipAddress != null">#{ipAddress},</if>
            <if test="ipPort != null">#{ipPort},</if>
            <if test="serialPort != null">#{serialPort},</if>
            <if test="baudRate != null">#{baudRate},</if>
            <if test="dataBits != null">#{dataBits},</if>
            <if test="stopBits != null">#{stopBits},</if>
            <if test="parity != null">#{parity},</if>
            <if test="description != null">#{description},</if>
            <if test="createdAt != null">#{createdAt},</if>
            <if test="updatedAt != null">#{updatedAt},</if>
            <if test="siteId != null">#{siteId},</if>
            <if test="communicationStatus != null">#{communicationStatus},</if>
            <if test="deviceId != null and deviceId != ''">#{deviceId},</if>
            <if test="parentId != null">#{parentId},</if>
            <if test="deviceCategory != null">#{deviceCategory},</if>
            <if test="deviceStatus != null">#{deviceStatus},</if>
            <if test="workStatus != null">#{workStatus},</if>
            <if test="pictureUrl != null">#{pictureUrl},</if>
        </trim>
    </insert>

    <update id="updateEmsDevicesSetting" parameterType="EmsDevicesSetting">
        update ems_devices_setting
        <trim prefix="SET" suffixOverrides=",">
            <if test="deviceName != null and deviceName != ''">device_name = #{deviceName},</if>
            <if test="deviceType != null and deviceType != ''">device_type = #{deviceType},</if>
            <if test="slaveId != null">slave_id = #{slaveId},</if>
            <if test="timeoutMs != null">timeout_ms = #{timeoutMs},</if>
            <if test="retries != null">retries = #{retries},</if>
            <if test="ipAddress != null">ip_address = #{ipAddress},</if>
            <if test="ipPort != null">ip_port = #{ipPort},</if>
            <if test="serialPort != null">serial_port = #{serialPort},</if>
            <if test="baudRate != null">baud_rate = #{baudRate},</if>
            <if test="dataBits != null">data_bits = #{dataBits},</if>
            <if test="stopBits != null">stop_bits = #{stopBits},</if>
            <if test="parity != null">parity = #{parity},</if>
            <if test="description != null">description = #{description},</if>
            <if test="createdAt != null">created_at = #{createdAt},</if>
            <if test="updatedAt != null">updated_at = #{updatedAt},</if>
            <if test="siteId != null">site_id = #{siteId},</if>
            <if test="communicationStatus != null">communication_status = #{communicationStatus},</if>
            <if test="deviceId != null and deviceId != ''">device_id = #{deviceId},</if>
            <if test="parentId != null">parent_id = #{parentId},</if>
            <if test="deviceCategory != null">device_category = #{deviceCategory},</if>
            <if test="deviceStatus != null">device_status = #{deviceStatus},</if>
            <if test="workStatus != null">work_status = #{workStatus},</if>
            <if test="pictureUrl != null">picture_url = #{pictureUrl},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteEmsDevicesSettingById" parameterType="Long">
        delete from ems_devices_setting where id = #{id}
    </delete>

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

    <select id="getAllStackInfoBySiteId" parameterType="String" resultType="java.util.Map">
        select device_id as id,device_name as deviceName from ems_devices_setting
        where device_id in (
            select distinct eb.device_id from ems_battery_stack eb where eb.site_id = #{siteId})
    </select>

    <select id="getDeviceInfoByParentId" parameterType="String" resultType="java.util.Map">
        select distinct t.device_id as id,t.device_name as deviceName
        from ems_devices_setting t
        where t.site_id = #{siteId}
              and t.parent_id = #{parentId}
        order by t.device_id
    </select>

    <select id="getAllBatteryDeviceBySiteId" parameterType="String" resultType="com.xzzn.ems.domain.EmsDevicesSetting">
        select distinct e.device_id as deviceId,
                        t.device_name as deviceName,
                        t.communication_status as communicationStatus
        from ems_devices_setting t INNER JOIN ems_ammeter_data e on t.site_id = e.site_id and t.device_id = e.device_id
        where t.site_id = #{siteId}
    </select>

    <select id="getAllClusterInfoBySiteId" parameterType="String" resultType="java.util.Map">
        select device_id as id,device_name as deviceName from ems_devices_setting
        where device_id in (
            select distinct eb.device_id from ems_battery_cluster eb where eb.site_id = #{siteId})
    </select>

    <select id="getDeviceInfosBySiteIdAndCategory" resultType="java.util.Map">
        select DISTINCT t1.device_id as id,
                        t1.device_name as deviceName,
                        t1.communication_status as communicationStatus,
                        t1.device_status as deviceStatus,
						t1.parent_id as parentDeviceId,
						t2.device_name as parentDeviceName
        from ems_devices_setting t1
		LEFT JOIN   ems_devices_setting t2
		ON t1.parent_id = t2.device_id  and t1.site_id = t2.site_id
        where t1.site_id = #{siteId} and t1.device_category = #{deviceCategory}
    </select>

    <select id="getDeviceBySiteAndDeviceId" parameterType="String" resultMap="EmsDevicesSettingResult">
        <include refid="selectEmsDevicesSettingVo"/>
        where site_id = #{siteId}
        <if test="deviceId != null and deviceId != ''">
            AND device_id = #{deviceId}
        </if>
    </select>

    <select id="getAmmeterNameList" parameterType="String" resultType="java.util.Map">
        select distinct device_id as id,device_name as deviceName from ems_devices_setting where site_id = #{siteId} and device_category = 'AMMETER'
    </select>

    <select id="getClusterIdsByFuzzyQuery" resultType="java.util.Map">
        select DISTINCT device_id as id,
                        device_name as deviceName,
                        communication_status as communicationStatus
        from ems_devices_setting
        where site_id = #{siteId}
            <if test="parentId !=null and parentId !=''">
            and parent_id = #{parentId}
            </if>
          and device_category = #{deviceCategory}
    </select>

    <select id="getAllDeviceCategoryBySiteId" resultType="java.lang.String">
        select DISTINCT device_category
        from ems_devices_setting
        where 1=1
        <if test="siteId !=null and siteId !=''">
            and site_id = #{siteId}
        </if>
    </select>
</mapper>
