<?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="">
    <!-- add object -->
    <select id="query_videoStatusData" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select (select csmc from Xtcl where csdl='ywVideoCurrentState' and csdm=isonline) status,f.tdmc
        name,f.jd,f.wd,
        f.xzbh,( select xzmc from Ptxzs where xzbh=f.xzbh) xzmc,e.sbip ip
        from sbtdspsr f
        left join ywvideocurrentstate a on (a.videonum = f.sbbh and a.channelnum = f.tdbh)
        left join sbxx e on (f.sbbh =e.sbbh)
        where f.kz3 is not null  and  f.kz3 != '1'
        <isNotEmpty prepend="and " property='LIKEmc'>
            tdmc like '%$LIKEmc$%'
        </isNotEmpty>
        <isNotEmpty prepend="and " property='state'>
            isonline =#state#listYwvideohistorystateByPage
        </isNotEmpty>
        order by isonline
    </select>
    <select id="count_videoStatusData" parameterClass="java.util.Map" resultClass="java.lang.Integer">
        select count(*) from (select *
        from SBTDSPSR f left join ywvideocurrentstate a
        on (a.videonum = f.sbbh and a.channelnum = f.tdbh) where  f.kz3 is not null  and f.kz3 != '1'
        <isNotEmpty prepend="and" property='LIKEmc'>
            tdmc like '%$LIKEmc$%'
        </isNotEmpty>
        <isNotEmpty prepend="and " property='state'>
            isonline =#state#
        </isNotEmpty>
        )
    </select>

    <select id="query_videoStatusStatis" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select count(*) total,'total' status
        from SBTDSPSR  where kz3 is not null and kz3 != '1'
        <isNotEmpty prepend="and " property='LIKEmc'>
            tdmc like '%$LIKEmc$%'
        </isNotEmpty>
        union all
        select count(*) total,'repaired' status
        from ywvideocurrentstate
        where repairstate in('912','914','915')
        <isNotEmpty prepend="and " property='LIKEmc'>
            channelname like '%$LIKEmc$%'
        </isNotEmpty>
        union all
        select count(*) total,'repairing' status
        from ywvideocurrentstate
        where repairstate not in ('900','915','912','914' )
        <isNotEmpty prepend="and " property='LIKEmc'>
            channelname like '%$LIKEmc$%'
        </isNotEmpty>
        union all
        select count(*) total,'normal' status
        from sbtdspsr a   left join ywvideocurrentstate b
        on a.sbbh=videonum and tdbh=channelnum
        where isonline is not null and   <![CDATA[isonline <> 0]]>
        and  a.kz3 is not null and a.kz3 != '1'
        <isNotEmpty prepend="and " property='LIKEmc'>
            channelname like '%$LIKEmc$%'
        </isNotEmpty>
        union all
        select count(*) total,'abnormal' status
        from sbtdspsr a left join ywvideocurrentstate b
        on a.sbbh=videonum and tdbh=channelnum
        where (isonline is null or isonline=0)
        and  a.kz3 is not null and a.kz3 != '1'
        <isNotEmpty prepend="and " property='LIKEmc'>
            tdmc like '%$LIKEmc$%'
        </isNotEmpty>

    </select>

    <select id="query_machineStatusData" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select (select csmc from Xtcl where csdl='ywMachineCurrentState' and csdm=currentstate) status ,a.machinename name
      ,machineareanum xzbh,( select xzmc from Ptxzs where xzbh=machineareanum) xzmc,a.machineip ip   from
        ywmachineinfo a left join ywmachinecurrentstate b
        on a.machineid=b.machineid
        <isNotEmpty prepend="where " property='LIKEmc'>
            a.machinename like '%$LIKEmc$%'
        </isNotEmpty>
        <isNotEmpty prepend="and " property='state'>
            b.currentstate =#state#
        </isNotEmpty>
        order by b.currentstate
    </select>
    <select id="count_machineStatusData" parameterClass="java.util.Map" resultClass="java.lang.Integer">
        select count(*) from (
        select * from ywmachineinfo a left join ywmachinecurrentstate b
        on a.machineid=b.machineid        where 1=1
        <isNotEmpty prepend="and " property='LIKEmc'>
            a.machinename like '%$LIKEmc$%'
        </isNotEmpty>
        <isNotEmpty prepend="and " property='state'>
            b.currentstate =#state#
        </isNotEmpty>)
    </select>
    <select id="query_machineStatusStatis" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select count(*) total,'total' status
        from ywmachineinfo
        union all
        select count(*) total,'normal' status
        from ywmachinecurrentstate where (currentstate=1)
        union all
        select count(*) total,'abnormal' status
        from ywmachineinfo a left join  ywmachinecurrentstate t
        on  a.machineid=t.machineid
        where (currentstate is null or  currentstate!=1 )
        union all
        select count(*) total,'repaired' status
        from ywmachinecurrentstate where repairstate in('912','914','915')
        union all
        select count(*) total,'repairing' status
        from ywmachinecurrentstate where  repairstate  not in ('900','915','912','914' )
    </select>

    <select id="query_ydpStatusStatis" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select count(*) total,'total' status
        from nhywjtledydpgl
        union all
        select count(*) total,'normal' status
        from nhywjtledydpgl where (state=1)
        union all
        select count(*) total,'abnormal' status
        from nhywjtledydpgl t where (state=0 or state is null )
        union all
        select count(*) total,'repaired' status
        from nhywjtledydpgl
        where repairstate in('912','914','915')
        union all
        select count(*) total,'repairing' status
        from nhywjtledydpgl
        where   repairstate  not in ('900','915','912','914' )
    </select>

    <select id="query_xhdStatusStatis" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select count(*) total,'total' status
        from nhywjtxhdgl
        union all
        select count(*) total,'normal' status
        from nhywjtxhdgl
        where state!=20
        union all
        select count(*) total,'abnormal' status
        from nhywjtxhdgl
        where (state is null or state=20)
        union all
        select count(*) total,'repaired' status
        from nhywjtxhdgl
        where repairstate in('912','914','915')
        union all
        select count(*) total,'repairing' status
        from nhywjtxhdgl
        where   repairstate not in ('900','915','912','914' )


    </select>
    <select id="query_DetetorStatusStatis" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select count(*) total,'total' status from its.ksh_detector where 1=1
        <isNotEmpty prepend="and " property='detectortype'>
            (detectortype = #detectortype# )
        </isNotEmpty>
        union all
        select count(*) total,'normal' status
        from its.ksh_detector a where (a.state=1)
        <isNotEmpty prepend="and " property='detectortype'>
            (a.detectortype = #detectortype# )
        </isNotEmpty>
        union all
        select count(*) total,'abnormal' status
        from its.ksh_detector b where (b.state=0 or b.state is null )
        <isNotEmpty prepend="and " property='detectortype'>
            (b.detectortype = #detectortype# )
        </isNotEmpty>
        union all
        select count(*) total,'repaired' status
        from its.ksh_detector b
        where repairstate in('912','914','915')
        <isNotEmpty prepend="and " property='detectortype'>
            (b.detectortype = #detectortype# )
        </isNotEmpty>
        union all
        select count(*) total,'repairing' status
        from its.ksh_detector b
        where  repairstate not in ('900','915','912','914' )
        <isNotEmpty prepend="and " property='detectortype'>
            (b.detectortype = #detectortype# )
        </isNotEmpty>
    </select>
    <select id="query_ZxldStatusStatis" parameterClass="java.util.Map" resultClass="java.util.HashMap">
        select count(*) total,'total' status from its.ksh_zxld where 1=1
        union all
        select count(*) total,'normal' status
        from its.ksh_zxld a where (a.state=1)
        union all
        select count(*) total,'abnormal' status
        from its.ksh_zxld b where (b.state=0 or b.state is null )
        union all
        select count(*) total,'repaired' status
        from its.ksh_zxld
        where repairstate in('912','914','915')
        union all
        select count(*) total,'repairing' status
        from its.ksh_zxld
        where  repairstate not in ('900','915','912','914' )
    </select>

</sqlMap>











