<?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="Sbtdspsr">
		
	<select id="getVideoInfos" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select xh,sbbh,tdbh,tdmc,jd,wd,xzbh,
 				cast((select count(*) from trafficaccidentinfo b where a.xh=b.deviceId) as char) as bjl 
 				from sbtdspsr a  where  a.kz3 is not null and a.kz3 != '1' and
	</select>

	<select id="getLedInfos" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select ledId as xh, ledName as name, adminCatalogue, longitude as jd, latitude as wd from trafficleddevice
	
	</select>
	
	<select id="getLightInfos" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select lightId as xh, lightName as name, adminCatalogue, longitude as jd, latitude as wd from trafficlightdevice
	
	</select>
	
	
	<!-- 首页搜索事件详情    点击圈圈查询事件详情 -->
	<!--首页搜索功能-->
	<select id="getEventInfo" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select * from (
		select b.*,(case b.eventType when '1' then '交通事故'  when '2' then '自然灾害' when '3' then '恶劣天气' 
		when '4' then '严重违法行为' when '5' then '路面损毁' when '6' then '交通拥堵' when '7' then '货物散落'
		when '20' then '特殊事件' when '30' then '非法逆行' when '31' then '非机动车闯入' 
		when '33' then '非法停车' when '99' then '其他事件' else '其他事件' END ) as eventName,
		(case b.accidentState when '0' then '未处理'  when '1' then '未处理' when '2' then '处理中' 
		when '3' then '已忽略' when '4' then '已处理' else '未处理' END ) as accidentName,
		(case b.alarmLevel when '1' then '一般'  when '2' then '重大' when '3' then '特别重大' 
		 else '一般' END ) as alarmLevelName,
		(select deviceName from trafficdevicerelate where deviceType='1' and relationId=b.relationId limit 0,1) tdmc, 
		(select deviceName from trafficdevicerelate where deviceType='2' and relationId=b.relationId limit 0,1) roadName, 
		(select deviceInfo from trafficdevicerelate where deviceType='2' and relationId=b.relationId limit 0,1) startPile,
		(select deviceName from trafficdevicerelate where deviceType='3' and relationId=b.relationId limit 0,1) tunnelName  
		 from (
		select a.*,
		(select relationId from trafficdevicerelate where deviceType='1' and deviceId=a.deviceId limit 0,1) relationId
		from trafficaccidentinfo a
		) b ) c where 1=1
		<isNotEmpty prepend='AND ' property='videoSourceId'>
			(deviceId = #videoSourceId#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='occurTimeStart'>
			(occurTime >= #occurTimeStart#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='occurTimeEnd'>
			(occurTime &lt;= #occurTimeEnd#  )
		</isNotEmpty>
		order by occurTime desc
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty>
	</select>

	<select id="getSbtdspsr" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select * from sbtdspsr where  kz3 is not null and kz3 != '1' and xh = #xh#
	</select>
	
	<select id="getEventAlarm" parameterClass="java.util.Map" resultClass="java.util.HashMap">
		select a.*,b.tdmc,b.jd,b.wd from 
		(select accidentId,accidentState,createAccount,LONGITUDE as locationX,LATITUDE as  locationY,locationWidth,locationHeight,
		(case accidentState when '0' then '未处理'  when '1' then '未处理' when '2' then '处理中' 
				when '3' then '已忽略' when '4' then '已处理' when '5' then '未处理' when '6' then '已处理'
				when '7' then '已处理' 
				else '处理中' END ) as accidentName,
		deviceId,eventId,eventSource,
		eventType,
		(case eventType 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 eventName,occurTime,alarmTime,alarmLevel,
				(case alarmLevel when '1' then '一般' when '2' then '重大' when '3' then '特别重大' end) as alarmLevelName,
				eventUrl,vdId,channelId,planID ,rownum
		from trafficaccidentinfo ) a left join sbtdspsr b  on a.deviceId = b.xh
		where  b.kz3 is not null and b.kz3 != '1'
		<isNotEmpty prepend='AND ' property='tdmc'>
			b.tdmc like concat('%',#tdmc# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='startTime'>
			(occurTime >= #startTime#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='endTime'>
			(occurTime &lt;= #endTime#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='createAccount'>
			(createAccount != #createAccount#  )
		</isNotEmpty>
		<isNotEmpty prepend="AND" property='accidentState'>
		     accidentState in
		     <iterate open="(" close=")" conjunction="," property="accidentState" >
		        #accidentState[]#
		    </iterate>
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='start'>
			rownum  &gt; #start#  and  rownum &lt;= #limit#
			order by occurTime desc

		</isNotEmpty>
	</select>
	
	<select id="getEventAlarmCount" parameterClass="java.util.Map" resultClass="java.lang.Integer">
		select count(*) from 
		(select deviceId,createAccount, occurTime, accidentState from trafficaccidentinfo ) a 
		left join sbtdspsr b  on a.deviceId = b.xh
		where  b.kz3 is not null and b.kz3 != '1'
		<isNotEmpty prepend='AND ' property='tdmc'>
			b.tdmc like concat('%',#tdmc# ,'%')
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='startTime'>
			(occurTime >= #startTime#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='endTime'>
			(occurTime &lt;= #endTime#  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='createAccount'>
			(createAccount != #createAccount#  )
		</isNotEmpty>
		<isNotEmpty prepend="AND" property='accidentState'>
		     accidentState in
		     <iterate open="(" close=")" conjunction="," property="accidentState" >  
		        #accidentState[]#  
		    </iterate> 
		</isNotEmpty>
	</select>
	
</sqlMap>











