<?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">

        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            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,c.xzbh,c.xzmc from ywalarmeventinfo
            a,
            ywvideosyncchannel b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend="  " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isNull prepend=" " property="areacode">
                sjxzbh ='-1'
            </isNull>
            connect by prior xzbh = sjxzbh ) c
            where a.alarmdeviceid=b.videonum and a.channelnum=b.channelnum and b.admincatalogue=c.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                a.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
        a.alarmtime >= #timeStart#
     ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        a.alarmtime <= #timeEnd#
     ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,a.channelnum,c.xzbh,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,f.xzbh,f.xzmc from
            ywalarmeventinfo d,ywvideosyncchannel e,
            (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) f
            where d.alarmdeviceid=e.videonum and d.channelnum=e.channelnum and e.admincatalogue=f.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                d.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            and d.processstatus in (915,914,912)
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
        d.processtime >= #timeStart#
     ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        d.processtime <= #timeEnd#
     ]]>
            </isNotEmpty>

            group by d.alarmdeviceid,d.channelnum,f.xzbh,f.xzmc
            ) y group by xzbh,xzmc
            ) h on g.xzbh = h.xzbh
            <!-- <isNotEmpty prepend=' ' property='start'>
                limit #start# ,#limit#
            </isNotEmpty> -->
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            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,c.xzbh,c.xzmc from ywalarmeventinfo
            a,
            YWMACHINEINFO b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) c where a.alarmdeviceid=b.MACHINEID and b.MACHINEAREANUM=c.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                a.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
        a.alarmtime >= #timeStart#
     ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        a.alarmtime <= #timeEnd#
     ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,c.xzbh,c.xzmc ) x group by xzbh,xzmc ) g left join
            ( select sum(gjCount) as hfcount,xzbh,xzmc from( select count(*) as gjCount,
            d.alarmdeviceid,f.xzbh ,f.xzmc from ywalarmeventinfo d,YWMACHINEINFO e, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) f
            where d.alarmdeviceid=e.machineid and e.MACHINEAREANUM=f.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                d.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>

            and d.processstatus in (915,914,912)
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
        d.processtime >= #timeStart#
     ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        d.processtime <= #timeEnd#
     ]]>
            </isNotEmpty>
            group by d.alarmdeviceid,f.xzbh,f.xzmc ) y group by xzbh,xzmc ) h on g.xzbh = h.xzbh
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            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,c.xzbh,c.xzmc from ywalarmeventinfo
            a,
            NHYWJTXHDGL b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) c
            where a.alarmdeviceid=b.XHDBH and b.QYBH=c.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                a.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
             a.alarmtime >= #timeStart#
            ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        a.alarmtime <= #timeEnd#
     ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,c.xzbh,c.xzmc ) x group by xzbh,xzmc ) g left join
            ( select sum(gjCount) as hfcount,xzbh,xzmc from( select count(*) as gjCount,
            d.alarmdeviceid,f.xzbh ,f.xzmc from ywalarmeventinfo d,NHYWJTXHDGL e, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) f
            where d.alarmdeviceid=e.XHDBH and e.QYBH=f.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                d.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            and d.processstatus in (915,914,912)
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
        d.processtime >= #timeStart#
     ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        d.processtime <= #timeEnd#
     ]]>
            </isNotEmpty>
            group by d.alarmdeviceid,f.xzbh,f.xzmc ) y group by xzbh,xzmc ) h on g.xzbh = h.xzbh

        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            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,c.xzbh,c.xzmc from ywalarmeventinfo
            a,
            NHYWJTLEDYDPGL b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) c
            where a.alarmdeviceid=b.LEDBH and b.QYBH=c.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                a.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
        a.alarmtime >= #timeStart#
     ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        a.alarmtime <= #timeEnd#
     ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,c.xzbh,c.xzmc ) x group by xzbh,xzmc ) g left join
            ( select sum(gjCount) as hfcount,xzbh,xzmc from( select count(*) as gjCount,
            d.alarmdeviceid,f.xzbh ,f.xzmc from ywalarmeventinfo d,NHYWJTLEDYDPGL e, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) f
            where d.alarmdeviceid=e.xh and e.QYBH=f.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                d.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>

            and d.processstatus in (915,914,912)
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
        d.processtime >= #timeStart#
     ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
        d.processtime <= #timeEnd#
     ]]>
            </isNotEmpty>
            group by d.alarmdeviceid,f.xzbh,f.xzmc ) y group by xzbh,xzmc ) h on g.xzbh = h.xzbh
        </isEqual>
    </select>

    <!-- 区域设备告警统计    上面用的  left join 所以分页可以这么写-->
    <select id="count_Ywmalarmcount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            select count(*) from (
            select sum(gjCount) as mcount,xzbh,xzmc from(
            select count(*) as gjCount, a.alarmdeviceid,a.channelnum,c.xzbh,c.xzmc from ywalarmeventinfo a,
            ywvideosyncchannel b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) c
            where a.alarmdeviceid=b.videonum and a.channelnum=b.channelnum and b.admincatalogue=c.xzbh and
            alarmdevicetype=1
            <isNotEmpty prepend="and" property="alarmdevicetype">
                a.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
			a.alarmtime >= #timeStart#
		 ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
			a.alarmtime <= #timeEnd#
		 ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,a.channelnum,c.xzbh,c.xzmc
            ) x group by xzbh,xzmc
            ) y
        </isEqual>
        <isEqual prepend=" " property="alarmdevicetype" compareValue="2">
            select count(*) from (
            select sum(gjCount) as mcount,xzbh,xzmc from(
            select count(*) as gjCount, a.alarmdeviceid,c.xzbh,c.xzmc from ywalarmeventinfo a,
            YWMACHINEINFO b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) c
            where a.alarmdeviceid=b.MACHINEID and b.MACHINEAREANUM=c.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                a.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
			a.alarmtime >= #timeStart#
		 ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
			a.alarmtime <= #timeEnd#
		 ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,c.xzbh,c.xzmc
            ) x group by xzbh,xzmc
            ) y
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            select count(*) from (
            select sum(gjCount) as mcount,xzbh,xzmc from(
            select count(*) as gjCount, a.alarmdeviceid,c.xzbh,c.xzmc from ywalarmeventinfo a,
            NHYWJTXHDGL b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) c
            where a.alarmdeviceid=b.xh and b.QYBH=c.xzbh
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
			a.alarmtime >= #timeStart#
		 ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
			a.alarmtime <= #timeEnd#
		 ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,c.xzbh,c.xzmc
            ) x group by xzbh,xzmc
            ) y
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            select count(*) from (
            select sum(gjCount) as mcount,xzbh,xzmc from(
            select count(*) as gjCount, a.alarmdeviceid,c.xzbh,c.xzmc from ywalarmeventinfo a,
            NHYWJTLEDYDPGL b, (select *
            from PTXZS t
            start with
            <isNotEmpty prepend=" " property="areacode">
                sjxzbh = #areacode#
            </isNotEmpty>
            <isEmpty prepend=" " property="areacode">
                sjxzbh ='-1'
            </isEmpty>
            connect by prior xzbh = sjxzbh ) c
            where a.alarmdeviceid=b.xh and b.QYBH=c.xzbh
            <isNotEmpty prepend="and" property="alarmdevicetype">
                a.alarmdevicetype = #alarmdevicetype#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeStart">
                <![CDATA[
			a.alarmtime >= #timeStart#
		 ]]>
            </isNotEmpty>
            <isNotEmpty prepend="and" property="timeEnd">
                <![CDATA[
			a.alarmtime <= #timeEnd#
		 ]]>
            </isNotEmpty>
            group by a.alarmdeviceid,c.xzbh,c.xzmc
            ) x group by xzbh,xzmc
            ) y
        </isEqual>
    </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#     or (b.xzbh in (select xzbh from Ptxzs  start with sjxzbh= #areacode#  connect by  sjxzbh =prior  xzbh)) )
        </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#     or (e.xzbh in (select xzbh from Ptxzs  start with sjxzbh= #areacode#  connect by  sjxzbh =prior  xzbh)) )
        </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_videoalarmbyArea" 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>
    <select id="count_machinealarmbyArea" 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, YWMACHINEINFO b where a.alarmdeviceid = b.MACHINEID
        <isNotEmpty prepend="and" property="alarmdevicetype">
            a.alarmdevicetype = #alarmdevicetype#
        </isNotEmpty>
        <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>











