<?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="" >
	<!-- 告警总量
		select sum(gjCount) as mcount,xzbh,xzmc from(
		select count(*) as gjCount, a.alarmdeviceid,a.channelnum,b.xzbh,c.xzmc from ywalarmeventinfo a,Sbtdspsr b, ptxzs c
		where a.alarmdeviceid=b.sbbh and a.channelnum=b.tdbh and b.xzbh=c.xzbh and alarmdevicetype=1
		<isNotEmpty prepend="and" property="timeStart">
		 <![CDATA[ 
			a.alarmtime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			a.alarmtime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="areacode">
			b.xzbh = #areacode#
		</isNotEmpty>
		group by a.alarmdeviceid,a.channelnum,b.xzbh,c.xzmc
		) x group by xzbh,xzmc	
		<isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty> 
	 -->	
	<!-- 区域设备告警统计   告警总量  恢复量 -->
	<select id="query_Ywmalarmcount" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select trim(cast(case when g.zcount is null then 0 else g.zcount end as char(8))) as zcount,
		trim(cast(case when h.hfcount is null then 0 else h.hfcount end as char(8))) as hfcount,
		g.xzbh,g.xzmc from(
		select sum(gjCount) as zcount,xzbh,xzmc from(
		select count(*) as gjCount, a.alarmdeviceid,a.channelnum,b.admincatalogue as xzbh,c.xzmc from ywalarmeventinfo a,ywvideosyncchannel b, ptxzs c
		where a.alarmdeviceid=b.videonum and a.channelnum=b.channelnum and b.admincatalogue=c.xzbh and a.alarmdevicetype=1
		<isNotEmpty prepend="and" property="timeStart">
		 <![CDATA[ 
			a.alarmtime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			a.alarmtime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="areacode">
			b.admincatalogue = #areacode#
		</isNotEmpty>
		group by a.alarmdeviceid,a.channelnum,b.admincatalogue,c.xzmc
		) x group by xzbh,xzmc
		) g left join 
		(
		select sum(gjCount) as hfcount,xzbh,xzmc from(
		select count(*) as gjCount, d.alarmdeviceid,d.channelnum,e.admincatalogue as xzbh,f.xzmc from ywalarmeventinfo d,ywvideosyncchannel e, ptxzs f
		where d.alarmdeviceid=e.videonum and d.channelnum=e.channelnum and e.admincatalogue=f.xzbh and d.alarmdevicetype=1 
		and d.processstatus in (911,914,912)
		<isNotEmpty prepend="and" property="timeStart">
		 <![CDATA[ 
			d.processtime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			d.processtime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="areacode">
			e.admincatalogue = #areacode#
		</isNotEmpty>
		group by d.alarmdeviceid,d.channelnum,e.admincatalogue,f.xzmc
		) y group by xzbh,xzmc
		) h on g.xzbh = h.xzbh	
		<!-- <isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty> -->	
	</select>
	
	<!-- 区域设备告警统计    上面用的  left join 所以分页可以这么写-->
	<select id="count_Ywmalarmcount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from (
		select sum(gjCount) as mcount,xzbh,xzmc from(
		select count(*) as gjCount, a.alarmdeviceid,a.channelnum,b.admincatalogue as xzbh,c.xzmc from ywalarmeventinfo a,ywvideosyncchannel b, ptxzs c
		where a.alarmdeviceid=b.videonum and a.channelnum=b.channelnum and b.admincatalogue=c.xzbh and alarmdevicetype=1
		<isNotEmpty prepend="and" property="timeStart">
		 <![CDATA[ 
			a.alarmtime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			a.alarmtime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="areacode">
			b.xzbh = #areacode#
		</isNotEmpty>
		group by a.alarmdeviceid,a.channelnum,b.admincatalogue,c.xzmc
		) x group by xzbh,xzmc	
		) y		
	</select>
	
	<!-- 区域告警设备列表 -->
	<select id="query_alarmbyArea" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select trim(cast(case when g.mcount is null then 0 else g.mcount end as char(8))) as zcount,g.tdmc,g.sbbh,g.tdbh,g.sbip,
		trim(cast(case when h.mcount is null then 0 else h.mcount end as char(8))) as hfcount from(	
		select count(*) as mcount, tdmc,b.sbbh,tdbh,sbip from ywalarmeventinfo a, Sbtdspsr b left join sbxx c 
		on b.sbbh = c.sbbh where a.alarmdeviceid = b.sbbh and a.channelnum = b.tdbh and a.alarmdevicetype=1
		 <isNotEmpty prepend="and" property="timeStart">
		 <![CDATA[ 
			a.alarmtime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			a.alarmtime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="areacode">
			b.xzbh = #areacode#
		</isNotEmpty>
		group by tdmc,b.sbbh,tdbh,sbip
		) g left join(		
		select count(*) as mcount, tdmc,e.sbbh,tdbh,sbip from ywalarmeventinfo d, Sbtdspsr e left join sbxx f 
		on e.sbbh = f.sbbh where d.alarmdeviceid = e.sbbh and d.channelnum = e.tdbh 
		and d.alarmdevicetype=1 and d.processstatus in (911,914,912)
		<isNotEmpty prepend="and" property="timeStart">
		 <![CDATA[ 
			d.alarmtime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			d.alarmtime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="areacode">
			e.xzbh = #areacode#
		</isNotEmpty>
		group by tdmc,e.sbbh,tdbh,sbip
		) h on g.sbbh = h.sbbh and g.tdbh=h.tdbh
		<!-- <isNotEmpty prepend=' ' property='start'>
			limit #start# ,#limit#
		</isNotEmpty> -->
	</select>
	
	<!-- 区域告警设备列表    上面用的  left join 所以分页可以这么写-->
	<select id="count_alarmbyArea" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from ( 
			select count(*) as mcount, b.channelname as tdmc,b.videonum as sbbh,b.channelnum as tdbh,sbip from ywalarmeventinfo a, ywvideosyncchannel b left join sbxx c 
			on b.videonum = c.sbbh where a.alarmdeviceid = b.videonum and a.channelnum = b.channelnum 
			and a.alarmdevicetype=1 
		<isNotEmpty prepend="and" property="timeStart">
		 <![CDATA[ 
			a.alarmtime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			a.alarmtime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="areacode">
			b.admincatalogue = #areacode#
		</isNotEmpty>
			group by b.channelname,b.videonum,b.channelnum,sbip
		) x
	</select>
</sqlMap>











