<?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.pqs9900.mapper.DataInfo.VersionMapper">


    <select id="getDeviceList" resultType="com.pqs9900.pojo.DataInfo.TerminalPojo">
		SELECT
		pq.DEV_INDEX AS devIndex,
		pq.NAME AS name,
		dic.DIC_NAME AS devType,
		pq.STATUS as status,
		gd.NAME AS gdName,
		nvl(pu.name,'/') AS updateUser,
		ded.updateTime,
		sub.NAME AS bdName,
		ed.ed_index as edIndex,
		nvl(ed.ED_PROTOCOL,'/') as proTocol,
		nvl(ed.ED_NAME,'/') as edName,
		nvl(ed.ed_date,'/') as edDate,
		nvl(ed.devType,'/') as devSeries,
		nvl(dic2.DIC_NAME,'/') as devSeriesName
		FROM
		PQ_DEVICE pq
		left join PQ_GDINFORMATION gd on gd.GD_INDEX = pq.GD_INDEX
		left join PQ_SUBSTATION sub on sub.SUB_INDEX = PQ.SUB_INDEX
		left join PQS_DICDATA dic on pq.DEVTYPE = dic.DIC_INDEX
		left join PQS_DEVEDASS ded on pq.DEV_INDEX = ded.DEV_INDEX and ded.STATE = 1 and ded.UPDATERESULT = 1
		left join PQS_EDDATA ed on ded.ED_INDEX = ed.ED_INDEX
		left join PQS_USER pu on ded.UPDATEUSER=pu.USER_INDEX
		left join PQS_DICDATA dic2 on ed.devType = dic2.DIC_INDEX
		WHERE
		pq.DEVMODEL = 1
        <if test="devType!=null and devType!=''">
            and pq.DEVTYPE = #{devType}
        </if>
        <if test="status!=null">
            and pq.STATUS = #{status}
        </if>
		<if test="devSeries!=null and devSeries!=''">
			and ed.devType = #{devSeries}
		</if>
		<if test="devarr!=null">
			and pq.DEV_INDEX in
			<foreach collection="devarr" item="item" index="index" open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
        order by pq.updateTime desc
    </select>

	<!--判断字段是否重复-->
	<select id="getCount" resultType="int">
		select count(*) from PQS_EDDATA
		where 1=1
	    <if test="edProtocol!=null and edProtocol!=''">
			and ED_PROTOCOL=#{edProtocol}
		</if>
		<if test="edDate!=null and edDate!=''">
			and ED_DATE=#{edDate}
		</if>
		<if test="edIndex!=null and edIndex!=''">
			and ED_INDEX!=#{edIndex}
		</if>
	</select>

    <insert id="addVersion">
		insert into PQS_EDDATA
		(
		ED_INDEX,
		ED_NAME,
		ED_PROTOCOL,
		ED_DATE,
		ED_FILE,
		ED_DESCRIBE,
		UPDATETIME,
		UPDATEUSER,
		<if test="type!=null and type!=''">
			TYPE,
		</if>
		DEVTYPE
		)
		values
		 (
		 #{edIndex},
		 #{edName},
		 #{edProtocol},
		 #{edDate},
		 #{edFile},
		 #{edDescribe},
		 #{updateTime},
		 #{updateUser},
		<if test="type!=null and type!=''">
			#{type},
		</if>
		 #{devType}
		 )
	</insert>

    <update id="updateVersion">
		update PQS_EDDATA set
		ED_NAME=#{edName},
		ED_PROTOCOL=#{edProtocol},
		ED_DATE=#{edDate},
		<if test="edFile!=null and edFile!=''">
			ED_FILE=#{edFile},
		</if>
		ED_DESCRIBE=#{edDescribe},
		UPDATETIME=#{updateTime},
		UPDATEUSER=#{updateUser},
		<if test="type!=null and type!=''">
			TYPE = #{type},
		</if>
		DEVTYPE=#{devType}
		where
		ED_INDEX = #{edIndex}
		and state = 1
	</update>

    <update id="delVersion">
		update PQS_EDDATA set
		STATE = 0
		where
		ED_INDEX = #{edIndex}
		and state = 1
	</update>

    <select id="getVersionList" resultType="com.pqs9900.pojo.terminal.EdData">
		select
		a.ED_INDEX edIndex,
		a.ED_NAME edName,
		a.ED_PROTOCOL edProtocol,
		a.ED_DATE edDate,
		a.ED_DESCRIBE edDescribe,
		a.UPDATETIME updateTime,
		a.TYPE type,
		c.dic_name devType,
		a.DEVTYPE devTypeId,
		a.status status,
		nvl(b.NAME,'/') updateUser from PQS_EDDATA a
		left join PQS_USER b on a.UPDATEUSER= b.USER_INDEX
		left join PQS_DICDATA c on a.devType =c.dic_index
		where a.state = 1
		<if test="edIndex!=null and edIndex!=''">
			and a.ED_INDEX !=#{edIndex}
		</if>
		<if test="edName!=null and edName!=''">
		and a.ED_NAME = #{edName}
		</if>
		<if test="edProtocol!=null and edProtocol!=''">
			and a.ED_PROTOCOL = #{edProtocol}
		</if>
		<if test="edDate!=null and edDate!=''">
			and a.ED_DATE = #{edDate}
		</if>
		<if test="devType!=null and devType!=''">
			and a.DEVTYPE = #{devType}
		</if>
		<if test="blob!=null and blob!=''">
			and a.ED_FILE is not null
		</if>
		<if test="type!=null and type!=''">
			and a.TYPE =#{type}
		</if>
		order by a.UPDATETIME desc
	</select>

	<select id="getVersionListFilterEdIndex" resultType="com.pqs9900.pojo.terminal.EdData">
		select
		a.ED_INDEX edIndex,
		a.ED_NAME edName,
		a.ED_PROTOCOL edProtocol,
		a.ED_DATE edDate,
		a.ED_DESCRIBE edDescribe,
		a.UPDATETIME updateTime,
		a.TYPE type,
		c.dic_name devType,
		a.DEVTYPE devTypeId,
		a.status status,
		nvl(b.NAME,'/') updateUser from PQS_EDDATA a
		left join PQS_USER b on a.UPDATEUSER= b.USER_INDEX
		left join PQS_DICDATA c on a.devType =c.dic_index
		where a.state = 1
		and a.status = 1
		<if test="devType!=null and devType!=''">
			and a.DEVTYPE = #{devType}
		</if>
		<if test="blob!=null and blob!=''">
			and a.ED_FILE is not null
		</if>
		and a.ED_INDEX not in
		<foreach collection="edList" item="item" open="(" close=")" separator="," index="index">
			#{item}
		</foreach>
	</select>

    <select id="getVersion" resultType="com.pqs9900.pojo.terminal.EdData">
		select * from PQS_EDDATA
		where state = 1
		and ED_INDEX = #{edIndex}
	</select>

	<select id="getVersionTwo" resultType="com.pqs9900.pojo.terminal.EdData">
		select
		a.ED_NAME as edName,
		a.ED_PROTOCOL as edProtocol,
		a.ED_DATE as edDate,
		c.dic_name as devType,
		a.devtype as devTypeId,
		a.TYPE as type,
		a.ED_DESCRIBE as edDescribe,
		a.ED_INDEX as edIndex from PQS_EDDATA a
		left join PQS_DICDATA c on a.devType =c.dic_index
		where a.state = 1
		and a.status = 1
		and a.ED_INDEX = #{edIndex}
	</select>

	<select id="getDeviceIp" resultType="com.pqs9900.pojo.terminal.DeviceIpRVO">
	select
	a.dev_index as devIndex,
	a.devtype as devType,
	d.devtype as devSeries,
	a.status,
	a.name as devName,
	b.node_ip as nodeIp from pq_device a
	left join NODEINFORMATION b on a.NODE_INDEX = b.NODE_INDEX and b.NODE_STATUS = 1
	left join PQS_DEVEDASS c on a.dev_index = c.dev_index and c.state = 1
	left join PQS_EDDATA d on d.ed_index = c.ed_index
	where a.DEV_INDEX in
	<foreach collection="list" item="item" open="(" close=")" separator="," index="index">
		#{item}
	</foreach>
	</select>

	<select id="getDeviceIps" resultType="com.pqs9900.pojo.terminal.DeviceIpRVO">
		select
		a.dev_index as devIndex,
		a.devtype as devType,
		d.devtype as devSeries,
		a.status,
		a.name as devName,
		b.node_ip as nodeIp from pq_device a
		left join NODEINFORMATION b on a.NODE_INDEX = b.NODE_INDEX and b.NODE_STATUS = 1
		left join PQS_DEVEDASS c on a.dev_index = c.dev_index and c.state = 1
		left join PQS_EDDATA d on d.ed_index = c.ed_index
		where a.DEV_INDEX = #{devList}
	</select>

	<select id="isExitVersionForDev" resultType="int">
		select count(*) from  PQS_DEVEDASS
		where state = 1
		and ED_INDEX = #{edIndex}
		and DEV_INDEX in
		<foreach collection="list" index="index" separator="," open="(" close=")" item="item">
			#{item}
		</foreach>
	</select>

	<select id="getTypeByDicId" resultType="string">
		select dic_name from pqs_dicdata
		where dic_index=#{dicIndex}
	</select>


	<update id="enableDisable">
		update PQS_EDDATA set
		status = #{status}
		where
		ED_INDEX = #{edIndex}
		and state = 1
	</update>

	<select id="getUpdetail" resultType="com.pqs9900.pojo.DataInfo.TerminalPojo">
		select
		 a.name,
		 c.ed_name edName,
		 b.updateresult,
		 b.updatetime updateTime,
		 d.name updateUser
		from pq_device a
		inner join pqs_devedass b on a.dev_index=b.dev_index and b.updateuser is not null
		inner join pqs_eddata c on b.ed_index = c.ed_index
		inner join pqs_user d on b.updateuser = d.user_index and d.state = 1
		where a.dev_index = #{devIndex}
		order by b.updatetime desc
	</select>

	<select id="isBindDev" resultType="int">
		select count(*) from pqs_devedass
		where ed_index = #{edIndex}
		and updateresult = 1
		and state = 1
	</select>

	<select id="upFlag" resultType="com.pqs9900.pojo.DataInfo.TerminalPojo">
		SELECT ed_index as edIndex,dev_index as devIndex FROM PQS_DEVEDASS
		where dev_index in
		<foreach collection="devIndex" open="(" close=")" item="item" separator="," index="index">
			#{item}
		</foreach>
		and state = 1
		and UPDATERESULT = 1
	</select>
</mapper>
