<?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="Trafficplanmanagebean" type="cn.vp.XXX.model.TrafficplanmanageBean"/> -->	<typeAlias alias="Trafficplanmanage" type="cn.im.trafficplan.TrafficplanmanageBean"/>
	
	<sql id="condition_Trafficplanmanage">
		where 1=1
		<isNotEmpty prepend='AND' property='LIKEplanID'>
			(planID like concat('%',#LIKEplanID# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planID'>
			(planID = #planID#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEplanName'>
			(planName like concat('%',#LIKEplanName# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planName'>
			(planName = #planName# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEplanGrade'>
			(planGrade like concat('%',#LIKEplanGrade# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planGrade'>
			(planGrade = #planGrade# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEenableState'>
			(enableState like concat('%',#LIKEenableState# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='enableState'>
			(enableState = #enableState# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEplanExecMode'>
			(planExecMode like concat('%',#LIKEplanExecMode# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planExecMode'>
			(planExecMode = #planExecMode# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEplanOperateMode'>
			(planOperateMode like concat('%',#LIKEplanOperateMode# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planOperateMode'>
			(planOperateMode = #planOperateMode# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEdelayTime'>
			(delayTime like concat('%',#LIKEdelayTime# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='delayTime'>
			(delayTime = #delayTime# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEplanStartTime'>
			(planStartTime like concat('%',#LIKEplanStartTime# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planStartTime'>
			(planStartTime = #planStartTime# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEplanStopTime'>
			(planStopTime like concat('%',#LIKEplanStopTime# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planStopTime'>
			(planStopTime = #planStopTime# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEalarmType'>
			(alarmType like concat('%',#LIKEalarmType# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='alarmType'>
			(alarmType = #alarmType# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='LIKEalarmDeviceId'>
			(alarmDeviceId like concat('%',#LIKEalarmDeviceId# ,'%')   )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='alarmDeviceId'>
			(alarmDeviceId = #alarmDeviceId# )
		</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_Trafficplanmanage" parameterClass="java.util.Map">
		insert into 
		trafficplanmanage(
		<dynamic prepend=" ">  
			<isNotEmpty prepend=',' property='planID'>planID</isNotEmpty>
			<isNotEmpty prepend=',' property='planName'>planName</isNotEmpty>
			<isNotEmpty prepend=',' property='planGrade'>planGrade</isNotEmpty>
			<isNotEmpty prepend=',' property='enableState'>enableState</isNotEmpty>
			<isNotEmpty prepend=',' property='planExecMode'>planExecMode</isNotEmpty>
			<isNotEmpty prepend=',' property='planOperateMode'>planOperateMode</isNotEmpty>
			<isNotEmpty prepend=',' property='delayTime'>delayTime</isNotEmpty>
			<isNotEmpty prepend=',' property='planStartTime'>planStartTime</isNotEmpty>
			<isNotEmpty prepend=',' property='planStopTime'>planStopTime</isNotEmpty>
			<isNotEmpty prepend=',' property='alarmType'>alarmType</isNotEmpty>
			<isNotEmpty prepend=',' property='alarmDeviceId'>alarmDeviceId</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='planID'>#planID#</isNotEmpty>
			<isNotEmpty prepend=',' property='planName'>#planName#</isNotEmpty>
			<isNotEmpty prepend=',' property='planGrade'>#planGrade#</isNotEmpty>
			<isNotEmpty prepend=',' property='enableState'>#enableState#</isNotEmpty>
			<isNotEmpty prepend=',' property='planExecMode'>#planExecMode#</isNotEmpty>
			<isNotEmpty prepend=',' property='planOperateMode'>#planOperateMode#</isNotEmpty>
			<isNotEmpty prepend=',' property='delayTime'>#delayTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='planStartTime'>#planStartTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='planStopTime'>#planStopTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='alarmType'>#alarmType#</isNotEmpty>
			<!-- <isNotEmpty prepend=',' property='alarmDeviceId'>#alarmDeviceId#</isNotEmpty> -->
			<isNotEmpty prepend=',' property='alarmDeviceId'>#roadId#</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_Trafficplanmanage" parameterClass="java.util.Map">
		update trafficplanmanage 
		<dynamic prepend="set ">   
			
			<isNotEmpty prepend=',' property='planName'>planName=#planName#</isNotEmpty>
			<isNotEmpty prepend=',' property='planGrade'>planGrade=#planGrade#</isNotEmpty>
			<isNotEmpty prepend=',' property='enableState'>enableState=#enableState#</isNotEmpty>
			<isNotEmpty prepend=',' property='planExecMode'>planExecMode=#planExecMode#</isNotEmpty>
			<isNotEmpty prepend=',' property='planOperateMode'>planOperateMode=#planOperateMode#</isNotEmpty>
			<isNotEmpty prepend=',' property='delayTime'>delayTime=#delayTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='planStartTime'>planStartTime=#planStartTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='planStopTime'>planStopTime=#planStopTime#</isNotEmpty>
			<isNotEmpty prepend=',' property='alarmType'>alarmType=#alarmType#</isNotEmpty>
			<isNotEmpty prepend=',' property='alarmDeviceId'>alarmDeviceId=#alarmDeviceId#</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>

		</dynamic>
		where 1=1 
			<isNotEmpty prepend='AND' property='XGTJplanID'> planID = #XGTJplanID#</isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJplanName'> planName = #XGTJplanName# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJplanGrade'> planGrade = #XGTJplanGrade# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJenableState'> enableState = #XGTJenableState# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJplanExecMode'> planExecMode = #XGTJplanExecMode# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJplanOperateMode'> planOperateMode = #XGTJplanOperateMode# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJdelayTime'> delayTime = #XGTJdelayTime# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJplanStartTime'> planStartTime = #XGTJplanStartTime# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJplanStopTime'> planStopTime = #XGTJplanStopTime# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJalarmType'> alarmType = #XGTJalarmType# </isNotEmpty>
			<isNotEmpty prepend='AND' property='XGTJalarmDeviceId'> alarmDeviceId = #XGTJalarmDeviceId# </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_Trafficplanmanage" parameterClass="java.util.Map">
		delete from trafficplanmanage 
		<include refid="condition_Trafficplanmanage"/>
	</delete>
	
	<!-- delete by planID -->
	<delete id="delete_TrafficplanmanageByplanID" parameterClass="java.lang.String">
		delete from trafficplanmanage where 
			 planID = #planID# 
	</delete>
	
	<!-- query objects by page-->
	<select id="query_Trafficplanmanage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select 
			planID,planName,planGrade,enableState,planExecMode,planOperateMode,delayTime,planStartTime,planStopTime,alarmType,alarmDeviceId,createAccount,createTime,modifyAccount,modifyTime
		from trafficplanmanage where 1=1
		
		<isNotEmpty prepend='AND' property='planName'>
			(planName like '%$planName$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planGrade'>
			(planGrade = '$planGrade$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='alarmType'>
			(alarmType = '$alarmType$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='enableState'>
			(enableState = '$enableState$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planStartTime'>
			(createTime >= '$planStartTime$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planStopTime'>
			(createTime &lt;= '$planStopTime$')
		</isNotEmpty>
		<isNotEmpty prepend=' ' property='order'>
			order by $order$ desc
		</isNotEmpty>
		
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="count_Trafficplanmanage" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from trafficplanmanage where 1=1
				<isNotEmpty prepend='AND' property='planName'>
			(planName like '%$planName$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planGrade'>
			(planGrade = '$planGrade$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='alarmType'>
			(alarmType = '$alarmType$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='enableState'>
			(enableState = '$enableState$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planStartTime'>
			(createTime >= '$planStartTime$'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='planStopTime'>
			(createTime &lt;= '$planStopTime$')
		</isNotEmpty>	
	</select>
	
	<!--根据设备，告警类型，级别-->
	<select id="query_Trafficplanmanage_sjgl" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select 
       		distinct a.planID,a.planName,a.planExecMode,a.delayTime,a.alarmType,a.alarmDeviceId
		from trafficplanmanage a left join trafficplanalarmdevice b on a.planID = b.planID
		where 1=1 and enableState = '1'
		<isNotEmpty prepend='AND' property='planGrade'>
			(a.planGrade = #planGrade# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='alarmType'>
			(a.alarmType = #alarmType# )
		</isNotEmpty>
		<isNotEmpty prepend='AND' property='channelId'>
			(b.channelId = #channelId# )
		</isNotEmpty>
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	
	<!--选择预案-->
	<select id="query_Trafficplanmanage_choose" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select 
       		distinct a.planID,a.planName,a.planExecMode,a.delayTime,a.planGrade,
       		(case a.planGrade when '1' then '一般' when '2' then '重大' when '3' then '特别重大' end)as gradeName,
       		(case a.alarmType when '1' then '交通事故'   when '5' then '路面损毁' 
				when '6' then '交通拥堵' 
				when '30' then '车辆逆行' when '31' then '非机动车闯入' 
				when '32' then '非法停车' when '33' then '车辆异常缓驶' 
			
				when '34' then '车辆横穿路面' when '35' then '车辆驶离路面' 
				when '36' then '隧道内火灾' when '37' then '能见度过低' 
				when '38' then '行人横穿路面' when '39' then '道路障碍物' 
				when '99' then '其他事件' else '其他事件' END ) as alarmName,
       		a.alarmType,b.deviceName
		from trafficplanmanage a left join trafficplanalarmdevice b on a.planID = b.planID
		where 1=1 and enableState = '1'
		
		<isNotEmpty prepend='AND' property='channelId'>
			(b.channelId = #channelId# )
		</isNotEmpty>
		<isNotEmpty prepend='and' property='planID'>
			a.planID != #planID#  
		</isNotEmpty>
		<isNotEmpty prepend='and' property='condition'>
			(a.planName like concat('%',#condition# ,'%') )
		</isNotEmpty>
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty>
	</select>
	<select id="count_Trafficplanmanage_choose" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select 
       		count(*)
		from trafficplanmanage a left join trafficplanalarmdevice b on a.planID = b.planID
		where 1=1 and enableState = '1'
		
		<isNotEmpty prepend='AND' property='channelId'>
			(b.channelId = #channelId# )
		</isNotEmpty>
		<isNotEmpty prepend='and' property='planID'>
			a.planID != #planID#  
		</isNotEmpty>
		<isNotEmpty prepend='and' property='condition'>
			(a.planName like concat('%',#condition# ,'%') )
		</isNotEmpty>
	</select>
	<!--AMQ查询默认预案信息
		and planName = '设备初始化') 
		-->
	<select id="query_AmqTrafficplanmanage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select * from (
		select a.linkageID,a.planID,a.linkageOrder,b.planType,b.typeName,b.linkageDeviceName,
		b.linkageDeviceId,b.messageTitle,b.messageContent from trafficplanlinkagedevice a 
		left join trafficplanexecmode b 
		on a.linkageID = b.linkageID ) c where 
		c.planId=(select planId from trafficplanmanage 
		where 1=1 
		<isNotEmpty prepend='AND' property='planName'>
			planName = #planName# )
		</isNotEmpty>
	</select>
	<!--AMQ查询结束预案关联设备的默认预案执行内容-->
	<select id="query_AmqTrafficplanEnd" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select a.*,b.messageContent from (
		(select planID,linkageId,execOrder,planType,typeName,linkageDeviceName,linkageDeviceId 
		from trafficplanexecmode where planID=#planID#) a 
		LEFT JOIN 
		(select linkageDeviceId,messageContent from trafficplanexecmode 
		where planId=(select planId from trafficplanmanage 
		where 1=1 and planName = #planName# )) b 
		on a.linkageDeviceId = b.linkageDeviceId
		) 
	</select>

	<!-- 报警源设备信息查询 -->
		<!-- <select id="query_alarmDevice"  resultClass="java.util.HashMap" parameterClass="java.util.Map">
			select distinct a.xh,a.tdmc,a.sbbh,a.tdbh,b.xzmc from sbtdspsr a,ptxzs b  where   a.xzbh = b.xzbh  
			<isNotEmpty prepend='and' property='condition'>(a.tdmc like concat('%',#condition#,'%') or b.xzmc like concat('%',#condition#,'%'))</isNotEmpty>
		</select> -->
		<select id="query_alarmDevice"  resultClass="java.util.HashMap" parameterClass="java.util.Map">
			select distinct roadId,roadName,deviceId,deviceName,deviceNumber,deviceInfo from trafficdevicerelate  where   deviceType='10'
			<isNotEmpty prepend='and' property='roadId'></isNotEmpty>
				roadId = #roadId#  
			<isNotEmpty prepend='and' property='condition'></isNotEmpty>
		</select>
		
		<!-- 报警源设备信息查询 -->
		<select id="query_alarmDevice_Origin"  resultClass="java.util.HashMap" parameterClass="java.util.Map">
			select 
			alarmID,planID,b.deviceId,deviceName,channelId,createAccount,createTime,modifyAccount,modifyTime,
			(select roadName from trafficdevicerelate a where a.deviceId=b.deviceId limit 1) roadName
		from trafficplanalarmdevice b where 1=1
			<!-- <isEqual compareValue="1" property="state1" prepend="and">
				c.planID = #planID#
			</isEqual>
			<isEqual compareValue="2" property="state1" prepend="and">
				c.planID  <![CDATA[!= ]]> #planID#
			</isEqual> -->
			<isNotEmpty prepend='and' property='planID'>
				planID=#planID#
			</isNotEmpty>
			<isNotEmpty prepend='and' property='condition'>
				(a.deviceNmae like concat('%',#condition#,'%'))
			</isNotEmpty>
			<!-- <isNotEmpty prepend='and' property='state'>
				$state$
			</isNotEmpty> -->
		</select>	
		<!-- <select id="query_alarmDevice_Origin"  resultClass="java.util.HashMap" parameterClass="java.util.Map">
			select distinct a.xh,a.tdmc,a.sbbh,a.tdbh,b.xzmc from sbtdspsr a,ptxzs b,trafficplanalarmdevice c where  a.xzbh = b.xzbh  
			<isEqual compareValue="1" property="state1" prepend="and">
				c.planID = #planID#
			</isEqual>
			<isEqual compareValue="2" property="state1" prepend="and">
				c.planID  <![CDATA[!= ]]> #planID#
			</isEqual>
			
			<isNotEmpty prepend='and' property='condition'>
				(a.tdmc like concat('%',#condition#,'%') or b.xzmc like concat('%',#condition#,'%'))
			</isNotEmpty>
			<isNotEmpty prepend='and' property='state'>
				$state$
			</isNotEmpty>
		</select>	 -->													 
	<select id="query_TrafficLightDevice" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select 
			lightId,lightName,lightBrand,lightType
		from trafficlightdevice 
		
	</select>
	<select id="query_TrafficLedDevice" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select 
			ledId,ledName,ledBrand,ledType
		from trafficleddevice 
		
	</select>
</sqlMap>











