<?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.njcn.mapper.configuration.DeviceMapper'>


    <select id="getDevicesByDevIndexs" resultType="device">
        SELECT
        T.DEV_INDEX,
        T.DEVFLAG
        FROM
        PQ_DEVICE t
        where
        t.DEV_INDEX in
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <!-- 	to_Char(T1.LOGONTIME,'yyyy-MM') between #{startTime} and  #{endTime} -->

    <select id="getDeviceIndexs" resultType="int">
        select
        t.dev_index
        from
        pq_device t
        where
        t.gd_index in
        <foreach collection="list" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
        AND
        t.DEVModel= #{devModel}
        AND
        t.DataType in
        <foreach collection="type" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </select>


    <update id="updateDevice">
    update pq_device set DEVFLAG=#{devflag} where dev_index=#{name}
    </update>

    <select id="selectMaxIndex" parameterType="String" resultType="Long">
		select max(dev_index) from PQ_DEVICE
	</select>

    <select id="testName" resultType="Device">
		select * from pq_device where name=#{name} and sub_index = #{subIndex} and dev_index != #{devIndex}
	</select>

    <select id="testIP" resultType="Device">
		select * from pq_device where IP=#{ip} and PORTID=#{portid} and gd_index = #{gdIndex} and dev_index != #{devIndex}
	</select>

    <select id="getDeviceIndexsByCompanyName" resultType="Integer">
        SELECT T2.DEV_INDEX from PQ_DEVICE t2
        INNER JOIN(
        SELECT
        *
        FROM
        PQ_DEVICEDETAIL
        WHERE
        MANUFACTURER = #{comIndex}
        ) t1 ON
        T1.dev_index=T2.DEV_INDEX
        AND
        T2.DEVMODEL =#{devModel}
        AND
        T2.DataType in
        <foreach collection="type" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
        AND
        T2.Dev_Index IN
        <foreach collection="list" open="(" close=")" separator="," item="item1">
            #{item1}
        </foreach>
    </select>

    <select id="getOnlineDevice" resultType="Integer">
        SELECT
        DEV_INDEX
        FROM
        PQ_DEVICE
        WHERE
        DEV_INDEX IN
        <foreach collection="list" item="item" separator="," close=")" open="(">
            #{item}
        </foreach>
        AND
        DEVFLAG =0
    </select>
    <select id="devNameType" resultType="device">
		select * from PQ_DEVICE where NAME=#{devName}
	</select>

    <select id="devIndexType" resultType="device">
		select * from PQ_DEVICE where dev_index=#{devIndex}
	</select>

    <select id="devIndexsType" resultType="device">
		select * from PQ_DEVICE where dev_index=(select dev_index from pq_line where line_index = #{lineIndex})
	</select>

    <select id="selectAllExcelData" resultType="DeviceExcelBody">
		select * from PQ_DEVICE
	</select>

    <select id="getIpByLineIndex" resultType="String">
		SELECT
             T2.IP
        FROM
            PQ_LINE t1,
            PQ_DEVICE t2
        WHERE
            T1.DEV_INDEX = T2.DEV_INDEX
        AND T1.LINE_INDEX = #{index}
	</select>

    <select id="getPushNodes" resultType="String">
		select distinct a.node_ip from pqs_pushinfo a where a.state = 1
	</select>

    <select id="getRunDeviceIndex" resultType="Integer">
        SELECT
        DEV_INDEX
        FROM
        PQ_DEVICE
        WHERE
        DEV_INDEX IN
        <foreach collection="list" open="(" close=")" separator="," item="item1">
            #{item1}
        </foreach>
        AND DEVFLAG = 0
    </select>

    <select id="getDevIndex" resultType="int">
        SELECT
        dev_index
        FROM
        PQ_DEVICE
        WHERE
        DATATYPE IN
        <foreach collection="list" open="(" close=")" separator="," item="item1">
            #{item1}
        </foreach>
        AND DEVMODEL = 1
    </select>

    <select id="deviceDescribe" resultType="String">
		SELECT
				'将'|| gd.name || sub.name || '下的' || device.name || '(' || device.ip || ')'
		FROM
			PQ_DEVICE device
				LEFT JOIN PQ_GDINFORMATION gd ON device.GD_INDEX = gd.GD_INDEX
				LEFT JOIN PQ_SUBSTATION sub ON device.SUB_INDEX = sub.SUB_INDEX
		WHERE
			device.DEV_INDEX = #{devIndex}
	</select>

    <select id="getDevAndParent" resultType="com.njcn.pojo.commons.device.DeviceDetail">
		   SELECT a.name,b.name subName,c.name gdNa
FROM
	pq_device a
	LEFT JOIN pq_substation b ON a.sub_index = b.sub_index
	LEFT JOIN pq_gdinformation c ON a.gd_index = c.gd_index
WHERE
	a.dev_index = #{devIndex}
	</select>
</mapper>
