<?xml version="1.0" encoding="gbk" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="" >
<!-- 	<typeAlias alias="Trafficdevicerelatebean" type="cn.vp.XXX.model.TrafficdevicerelateBean"/> -->	<typeAlias alias="Trafficdevicerelate" type="cn.im.traffic.TrafficdevicerelateBean"/>
	
	<sql id="condition_Trafficdevicerelate">
		where 1=1
		<isNotEmpty prepend='AND' property='LIKErelationId'>
			(relationId like concat('%',#LIKErelationId# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='relationId'>
			(relationId = #relationId#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKErelationName'>
			(relationName like concat('%',#LIKErelationName# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='relationName'>
			(relationName = #relationName# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEroadId'>
			(roadId like concat('%',#LIKEroadId# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='roadId'>
			(roadId = #roadId# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEroadName'>
			(roadName like concat('%',#LIKEroadName# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='roadName'>
			(roadName = #roadName# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEdeviceType'>
			(deviceType like concat('%',#LIKEdeviceType# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='deviceType'>
			(deviceType = #deviceType# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEdeviceId'>
			(deviceId like concat('%',#LIKEdeviceId# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='deviceId'>
			(deviceId = #deviceId# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEdeviceName'>
			(deviceName like concat('%',#LIKEdeviceName# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='deviceName'>
			(deviceName = #deviceName# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEdeviceNumber'>
			(deviceNumber like concat('%',#LIKEdeviceNumber# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='deviceNumber'>
			(deviceNumber = #deviceNumber# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEdeviceInfo'>
			(deviceInfo like concat('%',#LIKEdeviceInfo# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='deviceInfo'>
			(deviceInfo = #deviceInfo# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEremarks'>
			(remarks like concat('%',#LIKEremarks# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='remarks'>
			(remarks = #remarks# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEcreateAccount'>
			(createAccount like concat('%',#LIKEcreateAccount# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='createAccount'>
			(createAccount = #createAccount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEcreateTime'>
			(createTime like concat('%',#LIKEcreateTime# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='createTime'>
			(createTime = #createTime# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEmodifyAccount'>
			(modifyAccount like concat('%',#LIKEmodifyAccount# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='modifyAccount'>
			(modifyAccount = #modifyAccount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEmodifyTime'>
			(modifyTime like concat('%',#LIKEmodifyTime# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='modifyTime'>
			(modifyTime = #modifyTime# )
		</isNotEmpty>
		<isNotEmpty property='tableparam'>
			$tableparam$
		</isNotEmpty>
		<isNotEmpty property='order'>
			$order$
		</isNotEmpty>
	</sql>
	
	<!-- add object -->
	<insert id="insert_Trafficdevicerelate" parameterClass="java.util.Map">
		insert into 
		trafficdevicerelate(
		<dynamic prepend=" ">  
			<isNotEmpty prepend=',' property='relationId'>relationId</isNotEmpty>
			<isNotEmpty prepend=',' property='relationName'>relationName</isNotEmpty>
			<isNotEmpty prepend=',' property='roadId'>roadId</isNotEmpty>
			<isNotEmpty prepend=',' property='roadName'>roadName</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceType'>deviceType</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceId'>deviceId</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceName'>deviceName</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceNumber'>deviceNumber</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceInfo'>deviceInfo</isNotEmpty>
			<isNotEmpty prepend=',' property='remarks'>remarks</isNotEmpty>
			<isNotEmpty prepend=',' property='createAccount'>createAccount</isNotEmpty>
			<isNotEmpty prepend=',' property='createTime'>createTime</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyAccount'>modifyAccount</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyTime'>modifyTime</isNotEmpty>

		</dynamic>  
	    <![CDATA[ 
	        ) values ( 
	    ]]>  
	     <dynamic prepend=" "> 
			<isNotEmpty prepend=',' property='relationId'>#relationId#</isNotEmpty>
			<isNotEmpty prepend=',' property='relationName'>#relationName#</isNotEmpty>
			<isNotEmpty prepend=',' property='roadId'>#roadId#</isNotEmpty>
			<isNotEmpty prepend=',' property='roadName'>#roadName#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceType'>#deviceType#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceId'>#deviceId#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceName'>#deviceName#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceNumber'>#deviceNumber#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceInfo'>#deviceInfo#</isNotEmpty>
			<isNotEmpty prepend=',' property='remarks'>#remarks#</isNotEmpty>
			<isNotEmpty prepend=',' property='createAccount'>#createAccount#</isNotEmpty>
			<isNotEmpty prepend=',' property='createTime'>#createTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyAccount'>#modifyAccount#</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyTime'>#modifyTime#</isNotEmpty>

		 </dynamic>  
	    <![CDATA[ 
	        ) 
	    ]]> 
	</insert>
	
	<!-- edit object  -->
	<update id="update_TrafficdevicePositionRelate" parameterClass="java.util.Map">
		update trafficdevicerelate 
		<dynamic prepend="set ">   
			
			<isNotEmpty prepend=',' property='pictureX'>pictureX=#pictureX#</isNotEmpty>
			<isNotEmpty prepend=',' property='pictureY'>pictureY=#pictureY#</isNotEmpty>
		</dynamic>
		where 1=1 
			<isNotEmpty prepend='AND' property='XGTJrelationId'> relationId = #XGTJrelationId#</isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJroadId'> roadId = #XGTJroadId# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceType'> deviceType = #XGTJdeviceType# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceId'> deviceId = #XGTJdeviceId# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceNumber'> deviceNumber = #XGTJdeviceNumber# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceInfo'> deviceInfo = #XGTJdeviceInfo# </isNotEmpty>
	</update>
	
	<!-- edit object  -->
	<update id="update_Trafficdevicerelate" parameterClass="java.util.Map">
		update trafficdevicerelate 
		<dynamic prepend="set ">   
			
			<isNotEmpty prepend=',' property='relationName'>relationName=#relationName#</isNotEmpty>
			<isNotEmpty prepend=',' property='roadId'>roadId=#roadId#</isNotEmpty>
			<isNotEmpty prepend=',' property='roadName'>roadName=#roadName#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceType'>deviceType=#deviceType#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceId'>deviceId=#deviceId#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceName'>deviceName=#deviceName#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceNumber'>deviceNumber=#deviceNumber#</isNotEmpty>
			<isNotEmpty prepend=',' property='deviceInfo'>deviceInfo=#deviceInfo#</isNotEmpty>
			<isNotEmpty prepend=',' property='remarks'>remarks=#remarks#</isNotEmpty>
			<isNotEmpty prepend=',' property='createAccount'>createAccount=#createAccount#</isNotEmpty>
			<isNotEmpty prepend=',' property='createTime'>createTime=#createTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyAccount'>modifyAccount=#modifyAccount#</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyTime'>modifyTime=#modifyTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='pictureX'>pictureX=#pictureX#</isNotEmpty>
			<isNotEmpty prepend=',' property='pictureY'>pictureY=#pictureY#</isNotEmpty>
		</dynamic>
		where 1=1 
			<isNotEmpty prepend='AND' property='XGTJrelationId'> relationId = #XGTJrelationId#</isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJrelationName'> relationName = #XGTJrelationName# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJroadId'> roadId = #XGTJroadId# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJroadName'> roadName = #XGTJroadName# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceType'> deviceType = #XGTJdeviceType# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceId'> deviceId = #XGTJdeviceId# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceName'> deviceName = #XGTJdeviceName# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceNumber'> deviceNumber = #XGTJdeviceNumber# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdeviceInfo'> deviceInfo = #XGTJdeviceInfo# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJremarks'> remarks = #XGTJremarks# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJcreateAccount'> createAccount = #XGTJcreateAccount# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJcreateTime'> createTime = #XGTJcreateTime# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJmodifyAccount'> modifyAccount = #XGTJmodifyAccount# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJmodifyTime'> modifyTime = #XGTJmodifyTime# </isNotEmpty>

	</update>
	
	<!-- delete object -->
	<delete id="delete_Trafficdevicerelate" parameterClass="java.util.Map">
		delete from trafficdevicerelate 
		<include refid="condition_Trafficdevicerelate"/>
	</delete>
	
	<!-- batch delete object -->
	<delete id="batch_delete_Trafficdevicerelate" parameterClass="java.util.Map">
		delete from trafficdevicerelate 
		where 1=1
		<isNotEmpty prepend='and' property='keyword'>
			relationId IN 
			<iterate open="(" close=")" conjunction="," property="keyword">
			 	#keyword[]# 
			</iterate>  
		</isNotEmpty>
	</delete>
	
	<!-- query objects by page-->
	<select id="query_Trafficdevicerelate" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select 
			relationId,relationName,roadId,roadName,deviceId,deviceName,deviceNumber,
			deviceInfo,remarks,createAccount,createTime,modifyAccount,modifyTime,
			(CASE deviceType WHEN '1' THEN '摄像头' WHEN '2' THEN '路段' WHEN '3' THEN '隧道'
			 WHEN '4' THEN '情报板' WHEN '5' THEN '诱导灯' END) as deviceType, deviceType as roadDeviceType
		from trafficdevicerelate 
		<include refid="condition_Trafficdevicerelate"/>
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	
	<!-- query objects totalcount -->
	<select id="count_Trafficdevicerelate" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from trafficdevicerelate 
		<include refid="condition_Trafficdevicerelate"/>			
	</select>
	
	<!-- query objects by roadIds-->
	<select id="getRoadAlarmSourceByRoadIds" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select relationId,relationName,deviceId,deviceName,
		deviceNumber,deviceInfo,deviceType,roadId,roadName
		from trafficdevicerelate
		
		where deviceType='10'
		<isNotEmpty prepend='and' property='roadIds'>
			roadId IN 
			<iterate open="(" close=")" conjunction="," property="roadIds">
			 	#roadIds[]# 
			</iterate>  
		</isNotEmpty>
		<isNotEmpty prepend='and' property='selected'>
			deviceId not IN 
			<iterate open="(" close=")" conjunction="," property="selected">
			 	#selected[]# 
			</iterate>  
		</isNotEmpty>
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	
	
	<!-- query objects by roadIds  执行预案-->
	<select id="getRoadDeviceByRoadIds" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select relationId,relationName,deviceId,deviceName,
		deviceNumber,deviceInfo,deviceType,roadId,roadName
		from trafficdevicerelate
		
		where 1=1
		<isNotEmpty prepend='AND' property='type'> 
			deviceType = #type#
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='condition'> 
			(deviceName like concat('%',#condition# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='and' property='roadIds'>
			roadId IN 
			<iterate open="(" close=")" conjunction="," property="roadIds">
			 	#roadIds[]# 
			</iterate>  
		</isNotEmpty>
		<isNotEmpty prepend='and' property='selected'>
			deviceId not IN 
			<iterate open="(" close=")" conjunction="," property="selected">
			 	#selected[]# 
			</iterate>  
		</isNotEmpty>
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	
	
	
	<!-- 查询路段对应的监控排序信息-->
	<select id="query_VideoOrderByRoadId" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select relationId,roadId,roadName,deviceType,deviceId,deviceName,deviceInfo,remarks
		from trafficdevicerelate
		where deviceType='10' 
		<isNotEmpty prepend='AND' property='roadId'> 
			roadId = #roadId#
		</isNotEmpty>
	</select>
	
	<!-- batch add objects -->
	<insert id="batch_insert_Trafficdevicerelate" parameterClass="java.util.List">
	 	<![CDATA[
          INSERT
      	]]>
			into trafficdevicerelate(
			 <![CDATA[ 
				relationId,relationName,roadId,roadName,deviceType,deviceId,deviceName,deviceNumber,deviceInfo,remarks,createAccount,createTime,modifyAccount,modifyTime
			)values
			 ]]>  		 
		<iterate conjunction=" ," > 
			 <![CDATA[ 
				(
				#list[].relationId#,#list[].relationName#,#list[].roadId#,#list[].roadName#,#list[].deviceType#,#list[].deviceId#,#list[].deviceName#,#list[].deviceNumber#,#list[].deviceInfo#,#list[].remarks#,#list[].createAccount#,#list[].createTime#,#list[].modifyAccount#,#list[].modifyTime#
				)
			]]>    
    	</iterate> 
	</insert>
	<select id="count_RoadAlarmSourceByRoadIds" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from trafficdevicerelate where deviceType='10'
		<isNotEmpty prepend='and' property='roadIds'>
			roadId IN 
			<iterate open="(" close=")" conjunction="," property="roadIds">
			 	#roadIds[]# 
			</iterate> 
		</isNotEmpty>
		<isNotEmpty prepend='and' property='selected'>
			deviceId not IN 
			<iterate open="(" close=")" conjunction="," property="selected">
			 	#selected[]# 
			</iterate>  
		</isNotEmpty>
	</select>
	<select id="count_getRoadDeviceByRoadIds" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from trafficdevicerelate 
		where 1=1
		<isNotEmpty prepend='AND' property='condition'> 
			(deviceName like concat('%',#condition# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='type'> 
			deviceType = #type#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='roadIds'>
			roadId IN 
			<iterate open="(" close=")" conjunction="," property="roadIds">
			 	#roadIds[]# 
			</iterate>  
		</isNotEmpty>
		<isNotEmpty prepend='and' property='selected'>
			deviceId not IN 
			<iterate open="(" close=")" conjunction="," property="selected">
			 	#selected[]# 
			</iterate>  
		</isNotEmpty>
	</select>
	<!-- 查询行政目录信息并分页 -->
	<select id="getPtxzsPage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select xzbh,xzmc,xzmcpy,sjxzbh,qlj,wbbh,sm,jdpx,khbh,cjry,cjrq,xgry,xgrq,kz1,kz2,kz3,kz4
		from Ptxzs where 1=1
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getPtxzsPageCount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from Ptxzs 
	</select>
	
	<!-- 查询摄像头信息并分页 -->
	<select id="getVideoListPage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select * from sbtdspsr where 1=1
		<isNotEmpty prepend='and' property='xzbh' >
			(xzbh like concat('%',#xzbh# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getVideoListPageCount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from sbtdspsr 
	</select>
	
	<!-- 查询情报板信息并分页 -->
	<select id="getLedListPage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select * from trafficleddevice where 1=1
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getLedListPageCount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from trafficleddevice 
	</select>
	
	<!-- 查询诱导灯信息并分页 -->
	<select id="getLightListPage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select * from trafficlightdevice where 1=1
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getLightListPageCount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from trafficlightdevice 
	</select>
	
	
	
	<!-- 设备关联页面 -->
	<select id="getDeviceRelatePage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select relationName, roadName, case deviceType when '1' then '情报板' when '2' then '交通信号灯' 
		when '3' then '通风控制' when '4' then '车道指示器' when '5' then '大屏上墙' when '6' then '声光报警' 
		when '7' then '语音播报' when '8' then '照明控制' when '9' then '隧道' when '10' then '监控点' 
		when '20' then '隧道实景图' end as type,deviceInfo, deviceName, deviceNumber, relationId, roadId, deviceId,remarks,
		createTime, createAccount from trafficdevicerelate where 1=1
		<isNotEmpty prepend='and' property='roadId' >
			roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceType' >
			deviceType = #deviceType#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
		  order by createTime desc limit #start# ,#limit#
		</isNotEmpty>	
	</select>
	
	<select id="getDeviceRelatePageCount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from trafficdevicerelate where 1=1
		<isNotEmpty prepend='and' property='roadId' >
			roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceType' >
			deviceType = #deviceType#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		</isNotEmpty>		
	</select>
	
<!-- ===========   路段管理页面    增加信息 左侧   /start   =================== -->
		<!-- 1. 摄像头查询 -->
	<select id="getRoadVideo" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select xh as deviceId, sbbh as deviceNumber, tdmc as deviceName, 
		'10' as deviceType, concat(sbbh,'-',tdbh) as deviceInfo from sbtdspsr a 
		where not exists (select relationId from trafficdevicerelate b where a.xh=b.deviceId 			
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		) 
		 <isNotEmpty prepend='and' property='deviceName' >
			tdmc like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>	
	</select>
	<select id="getRoadVideoCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from sbtdspsr a  
		where not exists (select relationId from trafficdevicerelate b where a.xh=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		)  
		 <isNotEmpty prepend='and' property='deviceName' >
			tdmc like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>	
	</select>
	
	<!-- 2. 隧道查询 -->
	<select id="getRoadTunnel" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select tunnelId as deviceId, tunnelNumber as deviceNumber, tunnelName as deviceName,
		'9' as deviceType, concat(upDirection,'-',downDirection) as deviceInfo from traffictunnelinfo a 
		 where not exists (select relationId from trafficdevicerelate b where a.tunnelId=b.deviceId 
		 <isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		)  
		 <isNotEmpty prepend='and' property='deviceName' >
			tunnelName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>			
	</select>
	<select id="getRoadTunnelCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from traffictunnelinfo a 
	 where not exists (select relationId from trafficdevicerelate b where a.tunnelId=b.deviceId 
		 <isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 ) 
		 <isNotEmpty prepend='and' property='deviceName' >
			tunnelName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>	
	</select>
	
	<!-- 3. 情报板查询 -->
	<select id="getRoadLed" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select ledId as deviceId, '' as deviceNumber, ledName as deviceName,
		'1' as deviceType, ledBrand as deviceInfo from trafficleddevice a 
 		where not exists (select relationId from trafficdevicerelate b where a.ledId=b.deviceId 
 		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		 <isNotEmpty prepend='and' property='deviceName' >
			ledName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>			
	</select>
	<select id="getRoadLedCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficleddevice a 
		 where not exists (select relationId from trafficdevicerelate b where a.ledId=b.deviceId 
		 <isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		 <isNotEmpty prepend='and' property='deviceName' >
			ledName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>	
	</select>
		<!-- 4. 查询交通信号灯 -->
	<select id="getRoadLight" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select lightId as deviceId, lightName as deviceName, '' as deviceNumber,
		'2' as deviceType, lightBrand as deviceInfo from trafficlightdevice a 
		 where not exists (select relationId from trafficdevicerelate b where a.lightId=b.deviceId 
		 <isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			lightName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getRoadLightCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficlightdevice a 
		 where not exists (select relationId from trafficdevicerelate b where a.lightId=b.deviceId 
		 <isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			lightName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
	</select>
	
	<!-- 5. 查询车道指示器 -->
	<select id="getRoadLane" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select deviceId, deviceName, '' as deviceNumber, '4' as deviceType, deviceBrand as deviceInfo 
		from trafficlaneindicatedevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getRoadLaneCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficlaneindicatedevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
	</select>
	
	<!-- 6. 查询通风控制 -->
	<select id="getRoadArea" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select deviceId, deviceName, '' as deviceNumber, '3' as deviceType, deviceBrand as deviceInfo 
		from trafficaerationdevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getRoadAreaCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficaerationdevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
	</select>
	
	<!-- 7. 查询声光报警 -->
	<select id="getRoadVoiLig" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select deviceId, deviceName, '' as deviceNumber, '6' as deviceType, deviceBrand as deviceInfo 
		from trafficvoicelightdevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getRoadVoiLigCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficvoicelightdevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
	</select>
	
	<!-- 8. 查询语音播报 -->
	<select id="getRoadVoice" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select deviceId, deviceName, '' as deviceNumber, '7' as deviceType, deviceBrand as deviceInfo 
		from trafficvoicedevice a
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getRoadVoiceCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficvoicedevice a
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
	</select>
	
	<!-- 9. 查询照明控制 -->
	<select id="getRoadLigCon" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select deviceId, deviceName, '' as deviceNumber, '8' as deviceType, deviceBrand as deviceInfo 
		from trafficlightcontroldevice a
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getRoadLigConCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficlightcontroldevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
	</select>
	
	<!-- 10. 查询大屏上墙 -->
	<select id="getBigScreen" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select deviceId, deviceName, '' as deviceNumber, '5' as deviceType, deviceBrand as deviceInfo 
		from trafficbigscreendevice a
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
		 <isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="getgetBigScreenCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficbigscreendevice a 
		where not exists (select relationId from trafficdevicerelate b where a.deviceId=b.deviceId 
		<isNotEmpty prepend='and' property='roadId' >
			b.roadId = #roadId#
		 </isNotEmpty>
		 )  
		<isNotEmpty prepend='and' property='deviceName' >
			deviceName like concat('%',#deviceName# ,'%') 
		 </isNotEmpty>
	</select>
	<!-- 路段管理页面    增加信息 左侧   /end -->
	
	<!-- 更新关联设备顺序   by relationId -->
	<update id="updateRemarks" parameterClass="java.util.Map" >
		update trafficdevicerelate 
		<dynamic prepend="set ">   
			<isNotEmpty prepend=',' property='remarks'>remarks=#remarks#</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyAccount'>modifyAccount=#modifyAccount#</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyTime'>modifyTime=#modifyTime#</isNotEmpty>
		</dynamic>
		where 1=1 
			<isNotEmpty prepend='AND' property='relationId'> relationId = #relationId#</isNotEmpty>
	</update>
	
	<!-- 查询预案播放视频设备信息 -->
	<select id="getPlanVideoInfo" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select deviceType, deviceId, deviceName, deviceNumber, deviceInfo, remarks,
		(select remarks from trafficdevicerelate where deviceType = '10' 
		<isNotEmpty prepend='AND' property='deviceId'> deviceId = #deviceId#</isNotEmpty>
		limit 1) AS relationId
		 from trafficdevicerelate 
		where deviceType = '10' and roadId=
		(
		select roadId from trafficdevicerelate where deviceType = '10' 
		<isNotEmpty prepend='AND' property='deviceId'> deviceId = #deviceId#</isNotEmpty>
		limit 1
		) 		ORDER BY
		(remarks+0) ASC
	</select>
	<!-- 隧道内监控设备 -->
	<select id="getTunnelDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.xh,a.sbbh,a.tdbh,a.tdmc,a.xzbh,a.tdzt,a.jd,a.wd,a.sfzx,a.sfzlx,a.lxzt,a.tdlx,
			(select roadId from trafficdevicerelate where deviceType='10' and deviceId=a.xh  ORDER BY createTime limit 1) roadId
			from sbtdspsr  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内情报板 -->
	<select id="getTunnelLedDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.ledId,a.ledName,a.ledBrand,a.ledType,a.adminCatalogue,a.screeNum,a.longitude,a.latitude,a.screenWide,a.screenHeight,a.startX,a.startY,
			a.color,a.brightValue,a.entryMode,a.runSpeed,a.residenceTime,a.autoStartTime,a.autoCloseTime,a.wordType,a.wordSize,
			(select roadId from trafficdevicerelate where deviceType='1' and deviceId=a.ledId  ORDER BY createTime limit 1) roadId
			from trafficleddevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内交通信号灯 -->
	<select id="getTunnelLightDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.lightId,a.lightName,a.lightBrand,a.lightType,a.adminCatalogue,a.longitude,a.latitude,
			(select roadId from trafficdevicerelate where deviceType='2' and deviceId=a.lightId  ORDER BY createTime limit 1) roadId
			from trafficlightdevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内通风控制 -->
	<select id="getTunnelAerationDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.deviceId,a.deviceName,a.deviceBrand,a.deviceType,a.adminCatalogue,a.longitude,a.latitude,
			a.channelNum,a.windSpeed,a.windDirection,
			(select roadId from trafficdevicerelate where deviceType='3' and deviceId=a.deviceId  ORDER BY createTime limit 1) roadId
			from trafficaerationdevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内车道指示器 -->
	<select id="getTunnelLaneindicateDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.deviceId,a.deviceName,a.deviceBrand,a.deviceType,a.adminCatalogue,a.longitude,a.latitude,
			a.channelNum,a.indicatorColor,a.
			(select roadId from trafficdevicerelate where deviceType='4' and deviceId=a.deviceId  ORDER BY createTime limit 1) roadId
			from trafficlaneindicatedevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内大屏上墙 -->
	<select id="getTunnelBigscreenDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.deviceId,a.deviceName,a.deviceBrand,a.deviceType,a.adminCatalogue,a.longitude,a.latitude,
			a.screeNum,a.color,
			(select roadId from trafficdevicerelate where deviceType='5' and deviceId=a.deviceId  ORDER BY createTime limit 1) roadId
			from trafficbigscreendevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内声光报警 -->
	<select id="getTunnelVoicelightDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.deviceId,a.deviceName,a.deviceBrand,a.deviceType,a.adminCatalogue,a.longitude,a.latitude,
			a.screeNum,a.color,a.brightValue,a.voiceType,a.voiceSound,
			(select roadId from trafficdevicerelate where deviceType='6' and deviceId=a.deviceId  ORDER BY createTime limit 1) roadId
			from trafficvoicelightdevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内语音播报 -->
	<select id="getTunnelVoiceDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.deviceId,a.deviceName,a.deviceBrand,a.deviceType,a.adminCatalogue,a.longitude,a.latitude,
			a.screeNum,a.voiceToWords,a.voiceSpeen,a.voiceType,a.voiceSound,
			(select roadId from trafficdevicerelate where deviceType='7' and deviceId=a.deviceId  ORDER BY createTime limit 1) roadId
			from trafficvoicedevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	<!-- 隧道内照明控制 -->
	<select id="getTunnelLightcontrolDeviceList" resultClass="java.util.HashMap">
		select b.*,
				(select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelId,
				(select deviceName from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) as tunnelName
 		from 
		(
			select a.deviceId,a.deviceName,a.deviceBrand,a.deviceType,a.adminCatalogue,a.longitude,a.latitude,
			a.lightColor,a.lightBright,a.lightLength,
			(select roadId from trafficdevicerelate where deviceType='8' and deviceId=a.deviceId  ORDER BY createTime limit 1) roadId
			from trafficlightcontroldevice  a
			) b 
		where  (select deviceId from trafficdevicerelate where roadId=b.roadId and deviceType='9' ORDER BY createTime limit 1) is NOT null;
	</select>
	
	
	
	
	
 <!--  ======================= 查询路段与设备关联信息 ============================================= -->	
 <!-- 路段关联交通信号灯 -->
	<select id="get_roadLight" parameterClass="java.util.Map" resultClass="java.util.HashMap">		
		select a.roadName, a.deviceName,a.deviceId,b.pictureX,b.pictureY 
		from trafficdevicerelate a , trafficlightdevice b where a.deviceId = b.lightId and a.deviceType="2"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadLightCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">		
		select count(*) from trafficdevicerelate a , trafficlightdevice b where a.deviceId = b.lightId and a.deviceType="2"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>
	
<!-- 路段关联声光报警设备 -->	
	<select id="get_roadVoicelight" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.roadName, a.deviceName,a.deviceId,b.pictureX,b.pictureY 
		from trafficdevicerelate a , trafficvoicelightdevice b where a.deviceId = b.deviceId and a.deviceType="6"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadVoicelightCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate a , trafficvoicelightdevice b where a.deviceId = b.deviceId and a.deviceType="6"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>

<!-- 路段关联情报板设备 -->	
	<select id="get_roadLed" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.roadName, a.deviceName,a.deviceId,b.pictureX,b.pictureY 
		from trafficdevicerelate a , trafficleddevice b where a.deviceId = b.ledId and a.deviceType="1"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadLedCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate a , trafficleddevice b where a.deviceId = b.deviceId and a.deviceType="1"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>

<!-- 路段关联照明控制设备 -->	
	<select id="get_roadLightcontrol" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.roadName, a.deviceName,a.deviceId,b.pictureX,b.pictureY 
		from trafficdevicerelate a , trafficlightcontroldevice b where a.deviceId = b.deviceId and a.deviceType="8"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadLightcontrolCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate a , trafficlightcontroldevice b where a.deviceId = b.deviceId and a.deviceType="8"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>
	
	<!-- 路段关联外挂语音播报设备 -->	
	<select id="get_roadVoice" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.roadName, a.deviceName,a.deviceId,b.pictureX,b.pictureY 
		from trafficdevicerelate a , trafficvoicedevice b where a.deviceId = b.deviceId and a.deviceType="7"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadVoiceCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate a , trafficvoicedevice b where a.deviceId = b.deviceId and a.deviceType="7"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>
	
<!-- 路段关联车道指示器设备 -->	
	<select id="get_roadlaneind" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.roadName, a.deviceName,a.deviceId,b.pictureX,b.pictureY 
		from trafficdevicerelate a , trafficlaneindicatedevice b where a.deviceId = b.deviceId and a.deviceType="4"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadlaneindCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate a , trafficlaneindicatedevice b where a.deviceId = b.deviceId and a.deviceType="4"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>
	
<!-- 路段关联通风控制设备 -->	
	<select id="get_roadArea" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.roadName, a.deviceName,a.deviceId,b.pictureX,b.pictureY 
		from trafficdevicerelate a , trafficaerationdevice b where a.deviceId = b.deviceId and a.deviceType="3"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadAreaCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate a , trafficaerationdevice b where a.deviceId = b.deviceId and a.deviceType="3"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>
	
<!-- 路段关联监控设备 -->	
	<select id="get_roadVideo" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.roadName, a.deviceName,a.deviceId,b.tpwzx as pictureX,b.tpwzy as pictureY 
		from trafficdevicerelate a , sbtdspsr b where a.deviceId = b.xh and a.deviceType="10"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="get_roadVideoCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate a , sbtdspsr b where a.deviceId = b.xh and a.deviceType="10"
		<isNotEmpty prepend='and' property='roadId'>
			a.roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			a.deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
	</select>
		
	
	<!-- 修改查询 -->							
	<select id="get_roadRelate" parameterClass='java.util.Map' resultClass='java.util.HashMap'>
		select roadId, roadName, deviceInfo ,deviceType, deviceName, deviceId, pictureX, pictureY 
		from trafficdevicerelate where 1=1 
		<isNotEmpty prepend='and' property='roadId'>
			roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceType'>
			deviceType = #deviceType#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='' property='start' >
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	
	<select id="get_roadRelateCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from trafficdevicerelate where 1=1 
		<isNotEmpty prepend='and' property='roadId'>
			roadId = #roadId#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceType'>
			deviceType = #deviceType#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='deviceName'>
			deviceName like concat('%',#deviceName# ,'%')
		</isNotEmpty>
		 
	</select>
	
 <!-- ======================= 查询路段与设备关联信息 =============================================	 -->
	
	
	<update id="update_TrafficdevicePositionNull" parameterClass="java.util.Map">
		update trafficdevicerelate set pictureX='',pictureY=''		
			<isNotEmpty prepend=',' property='modifyAccount'>modifyAccount=#modifyAccount#</isNotEmpty>
			<isNotEmpty prepend=',' property='modifyTime'>modifyTime=#modifyTime#</isNotEmpty>
		where 1=1 
			<isNotEmpty prepend='AND' property='relationId'> relationId = #relationId#</isNotEmpty>
			<isNotEmpty prepend='AND' property='roadId'> roadId = #roadId# </isNotEmpty>
			<isNotEmpty prepend='AND' property='deviceType'> deviceType = #deviceType# </isNotEmpty>
			<isNotEmpty prepend='AND' property='deviceId'> deviceId = #deviceId# </isNotEmpty>
			
	</update>
	
</sqlMap>











