<?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="Ywcountvideoappraisebean" type="cn.vp.XXX.model.YwcountvideoappraiseBean"/> -->

    <sql id="condition_Ywcountvideoappraise">
        where 1=1
        <isNotEmpty prepend='AND ' property='LIKEcountId'>
            (countId like '%$LIKEcountId$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='countId'>
            (countId = #countId# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEareanum'>
            (areanum like '%$LIKEareanum$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='areanum'>
            (areanum = #areanum# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEareaname'>
            (areaname like '%$LIKEareaname$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='areaname'>
            (areaname = #areaname# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEvideoname'>
            (videoname like '%$LIKEvideoname$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='videoname'>
            (videoname = #videoname# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEdevicenum'>
            (devicenum like '%$LIKEdevicenum$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='devicenum'>
            (devicenum = #devicenum# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEchannelnum'>
            (channelnum like '%$LIKEchannelnum$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='channelnum'>
            (channelnum = #channelnum# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEipaddress'>
            (ipaddress like '%$LIKEipaddress$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='ipaddress'>
            (ipaddress = #ipaddress# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEcountMonth'>
            (countMonth like '%$LIKEcountMonth$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='countMonth'>
            (countMonth = #countMonth# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEcountDate'>
            (countDate like '%$LIKEcountDate$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='countDate'>
            (countDate = #countDate# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEcountHour'>
            (countHour like '%$LIKEcountHour$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='countHour'>
            (countHour = #countHour# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEonlineduration'>
            (onlineduration like '%$LIKEonlineduration$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='onlineduration'>
            (onlineduration = #onlineduration# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEonlinestarttime'>
            (onlinestarttime like '%$LIKEonlinestarttime$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='onlinestarttime'>
            (onlinestarttime = #onlinestarttime# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEofflineduration'>
            (offlineduration like '%$LIKEofflineduration$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='offlineduration'>
            (offlineduration = #offlineduration# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEofflinestarttime'>
            (offlinestarttime like '%$LIKEofflinestarttime$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='offlinestarttime'>
            (offlinestarttime = #offlinestarttime# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKErecordstate'>
            (recordstate like '%$LIKErecordstate$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='recordstate'>
            (recordstate = #recordstate# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKErecordduration'>
            (recordduration like '%$LIKErecordduration$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='recordduration'>
            (recordduration = #recordduration# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEnorecordduration'>
            (norecordduration like '%$LIKEnorecordduration$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='norecordduration'>
            (norecordduration = #norecordduration# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEvqdstate'>
            (vqdstate like '%$LIKEvqdstate$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='vqdstate'>
            (vqdstate = #vqdstate# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKElalstate'>
            (lalstate like '%$LIKElalstate$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='lalstate'>
            (lalstate = #lalstate# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEcreatejobcount'>
            (createjobcount like '%$LIKEcreatejobcount$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='createjobcount'>
            (createjobcount = #createjobcount# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKErepairjobcount'>
            (repairjobcount like '%$LIKErepairjobcount$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='repairjobcount'>
            (repairjobcount = #repairjobcount# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEcreateTime'>
            (createTime like '%$LIKEcreateTime$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='createTime'>
            (createTime = #createTime# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEupdateTime'>
            (updateTime like '%$LIKEupdateTime$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='updateTime'>
            (updateTime = #updateTime# )
        </isNotEmpty>
        <isNotEmpty property='tableparam'>
            $tableparam$
        </isNotEmpty>
        <isNotEmpty property='order'>
            $order$
        </isNotEmpty>
    </sql>

    <!-- add object -->
    <insert id="insert_Ywcountvideoappraise" parameterClass="java.util.Map">
        insert into
        ywcountvideoappraise(
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='countId'>countId</isNotEmpty>
            <isNotEmpty prepend=',' property='areanum'>areanum</isNotEmpty>
            <isNotEmpty prepend=',' property='areaname'>areaname</isNotEmpty>
            <isNotEmpty prepend=',' property='videoname'>videoname</isNotEmpty>
            <isNotEmpty prepend=',' property='devicenum'>devicenum</isNotEmpty>
            <isNotEmpty prepend=',' property='channelnum'>channelnum</isNotEmpty>
            <isNotEmpty prepend=',' property='ipaddress'>ipaddress</isNotEmpty>
            <isNotEmpty prepend=',' property='countMonth'>countMonth</isNotEmpty>
            <isNotEmpty prepend=',' property='countDate'>countDate</isNotEmpty>
            <isNotEmpty prepend=',' property='countHour'>countHour</isNotEmpty>
            <isNotEmpty prepend=',' property='onlineduration'>onlineduration</isNotEmpty>
            <isNotEmpty prepend=',' property='onlinestarttime'>onlinestarttime</isNotEmpty>
            <isNotEmpty prepend=',' property='offlineduration'>offlineduration</isNotEmpty>
            <isNotEmpty prepend=',' property='offlinestarttime'>offlinestarttime</isNotEmpty>
            <isNotEmpty prepend=',' property='recordstate'>recordstate</isNotEmpty>
            <isNotEmpty prepend=',' property='recordduration'>recordduration</isNotEmpty>
            <isNotEmpty prepend=',' property='norecordduration'>norecordduration</isNotEmpty>
            <isNotEmpty prepend=',' property='vqdstate'>vqdstate</isNotEmpty>
            <isNotEmpty prepend=',' property='lalstate'>lalstate</isNotEmpty>
            <isNotEmpty prepend=',' property='createjobcount'>createjobcount</isNotEmpty>
            <isNotEmpty prepend=',' property='repairjobcount'>repairjobcount</isNotEmpty>
            <isNotEmpty prepend=',' property='createTime'>createTime</isNotEmpty>
            <isNotEmpty prepend=',' property='updateTime'>updateTime</isNotEmpty>

        </dynamic>
        <![CDATA[
	        ) values ( 
	    ]]>
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='countId'>#countId#</isNotEmpty>
            <isNotEmpty prepend=',' property='areanum'>#areanum#</isNotEmpty>
            <isNotEmpty prepend=',' property='areaname'>#areaname#</isNotEmpty>
            <isNotEmpty prepend=',' property='videoname'>#videoname#</isNotEmpty>
            <isNotEmpty prepend=',' property='devicenum'>#devicenum#</isNotEmpty>
            <isNotEmpty prepend=',' property='channelnum'>#channelnum#</isNotEmpty>
            <isNotEmpty prepend=',' property='ipaddress'>#ipaddress#</isNotEmpty>
            <isNotEmpty prepend=',' property='countMonth'>#countMonth#</isNotEmpty>
            <isNotEmpty prepend=',' property='countDate'>#countDate#</isNotEmpty>
            <isNotEmpty prepend=',' property='countHour'>#countHour#</isNotEmpty>
            <isNotEmpty prepend=',' property='onlineduration'>#onlineduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='onlinestarttime'>#onlinestarttime#</isNotEmpty>
            <isNotEmpty prepend=',' property='offlineduration'>#offlineduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='offlinestarttime'>#offlinestarttime#</isNotEmpty>
            <isNotEmpty prepend=',' property='recordstate'>#recordstate#</isNotEmpty>
            <isNotEmpty prepend=',' property='recordduration'>#recordduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='norecordduration'>#norecordduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='vqdstate'>#vqdstate#</isNotEmpty>
            <isNotEmpty prepend=',' property='lalstate'>#lalstate#</isNotEmpty>
            <isNotEmpty prepend=',' property='createjobcount'>#createjobcount#</isNotEmpty>
            <isNotEmpty prepend=',' property='repairjobcount'>#repairjobcount#</isNotEmpty>
            <isNotEmpty prepend=',' property='createTime'>#createTime#</isNotEmpty>
            <isNotEmpty prepend=',' property='updateTime'>#updateTime#</isNotEmpty>

        </dynamic>
        <![CDATA[
	        ) 
	    ]]>
    </insert>

    <!-- edit object  -->
    <update id="update_Ywcountvideoappraise" parameterClass="java.util.Map">
        update ywcountvideoappraise
        <dynamic prepend="set ">

            <isNotEmpty prepend=',' property='areanum'>areanum=#areanum#</isNotEmpty>
            <isNotEmpty prepend=',' property='areaname'>areaname=#areaname#</isNotEmpty>
            <isNotEmpty prepend=',' property='videoname'>videoname=#videoname#</isNotEmpty>
            <isNotEmpty prepend=',' property='devicenum'>devicenum=#devicenum#</isNotEmpty>
            <isNotEmpty prepend=',' property='channelnum'>channelnum=#channelnum#</isNotEmpty>
            <isNotEmpty prepend=',' property='ipaddress'>ipaddress=#ipaddress#</isNotEmpty>
            <isNotEmpty prepend=',' property='countMonth'>countMonth=#countMonth#</isNotEmpty>
            <isNotEmpty prepend=',' property='countDate'>countDate=#countDate#</isNotEmpty>
            <isNotEmpty prepend=',' property='countHour'>countHour=#countHour#</isNotEmpty>
            <isNotEmpty prepend=',' property='onlineduration'>onlineduration=#onlineduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='onlinestarttime'>onlinestarttime=#onlinestarttime#</isNotEmpty>
            <isNotEmpty prepend=',' property='offlineduration'>offlineduration=#offlineduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='offlinestarttime'>offlinestarttime=#offlinestarttime#</isNotEmpty>
            <isNotEmpty prepend=',' property='recordstate'>recordstate=#recordstate#</isNotEmpty>
            <isNotEmpty prepend=',' property='recordduration'>recordduration=#recordduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='norecordduration'>norecordduration=#norecordduration#</isNotEmpty>
            <isNotEmpty prepend=',' property='vqdstate'>vqdstate=#vqdstate#</isNotEmpty>
            <isNotEmpty prepend=',' property='lalstate'>lalstate=#lalstate#</isNotEmpty>
            <isNotEmpty prepend=',' property='createjobcount'>createjobcount=#createjobcount#</isNotEmpty>
            <isNotEmpty prepend=',' property='repairjobcount'>repairjobcount=#repairjobcount#</isNotEmpty>
            <isNotEmpty prepend=',' property='createTime'>createTime=#createTime#</isNotEmpty>
            <isNotEmpty prepend=',' property='updateTime'>updateTime=#updateTime#</isNotEmpty>

        </dynamic>
        where countId = #XGTJcountId#
        <isNotEmpty prepend='AND ' property='XGTJareanum'>areanum = #XGTJareanum#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJareaname'>areaname = #XGTJareaname#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJvideoname'>videoname = #XGTJvideoname#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJdevicenum'>devicenum = #XGTJdevicenum#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJchannelnum'>channelnum = #XGTJchannelnum#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJipaddress'>ipaddress = #XGTJipaddress#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJcountMonth'>countMonth = #XGTJcountMonth#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJcountDate'>countDate = #XGTJcountDate#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJcountHour'>countHour = #XGTJcountHour#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJonlineduration'>onlineduration = #XGTJonlineduration#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJonlinestarttime'>onlinestarttime = #XGTJonlinestarttime#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJofflineduration'>offlineduration = #XGTJofflineduration#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJofflinestarttime'>offlinestarttime = #XGTJofflinestarttime#
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJrecordstate'>recordstate = #XGTJrecordstate#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJrecordduration'>recordduration = #XGTJrecordduration#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJnorecordduration'>norecordduration = #XGTJnorecordduration#
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJvqdstate'>vqdstate = #XGTJvqdstate#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJlalstate'>lalstate = #XGTJlalstate#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJcreatejobcount'>createjobcount = #XGTJcreatejobcount#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJrepairjobcount'>repairjobcount = #XGTJrepairjobcount#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJcreateTime'>createTime = #XGTJcreateTime#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJupdateTime'>updateTime = #XGTJupdateTime#</isNotEmpty>

    </update>
    <insert id="insertWeight" parameterClass="java.util.Map">
        insert into
        ywcountvideoweight(
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='weightId'>weightId</isNotEmpty>
            <isNotEmpty prepend=',' property='onlineweight'>onlineweight</isNotEmpty>
            <isNotEmpty prepend=',' property='videoweight'>videoweight</isNotEmpty>
            <isNotEmpty prepend=',' property='recordweight'>recordweight</isNotEmpty>
            <isNotEmpty prepend=',' property='lalweight'>lalweight</isNotEmpty>
            <isNotEmpty prepend=',' property='repairweight'>repairweight</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>
            <isNotEmpty prepend=',' property='remarks'>remarks</isNotEmpty>

        </dynamic>
        <![CDATA[
	        ) values ( 
	    ]]>
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='weightId'>#weightId#</isNotEmpty>
            <isNotEmpty prepend=',' property='onlineweight'>#onlineweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='videoweight'>#videoweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='recordweight'>#recordweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='lalweight'>#lalweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='repairweight'>#repairweight#</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>
            <isNotEmpty prepend=',' property='remarks'>#remarks#</isNotEmpty>

        </dynamic>
        <![CDATA[
	        ) 
	    ]]>
    </insert>
    <update id="updateWeight" parameterClass="java.util.Map">
        update ywcountvideoweight
        <dynamic prepend="set ">
            <isNotEmpty prepend=',' property='onlineweight'>onlineweight=#onlineweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='videoweight'>videoweight=#videoweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='recordweight'>recordweight=#recordweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='lalweight'>lalweight=#lalweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='repairweight'>repairweight=#repairweight#</isNotEmpty>
            <isNotEmpty prepend=',' property='modifyaccount'>modifyaccount=#modifyaccount#</isNotEmpty>
            <isNotEmpty prepend=',' property='modifytime'>modifytime=#modifytime#</isNotEmpty>
            <isNotEmpty prepend=',' property='remarks'>remarks=#remarks#</isNotEmpty>

        </dynamic>
        where weightId = #XGTJweightId#
        <isNotEmpty prepend='AND ' property='XGTJonlineweight'>onlineweight = #XGTJonlineweight#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJvideoweight'>videoweight = #XGTJvideoweight#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJrecordweight'>recordweight = #XGTJrecordweight#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJlalweight'>lalweight = #XGTJlalweight#</isNotEmpty>
        <isNotEmpty prepend='AND ' property='XGTJrepairweight'>repairweight = #XGTJrepairweight#</isNotEmpty>

    </update>

    <!-- delete object -->
    <delete id="delete_Ywcountvideoappraise" parameterClass="java.util.Map">
        delete from ywcountvideoappraise where (countId = #countId# )
    </delete>

    <!-- batch delete object -->
    <delete id="batch_delete_Ywcountvideoappraise" parameterClass="java.util.Map">
        delete from ywcountvideoappraise where  key in (#keyword#)
    </delete>

    <!-- query objects by page-->
    <!-- <select id="query_Ywcountvideoappraise" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select
            countId,areanum,areaname,videoname,devicenum,channelnum,ipaddress,countMonth,countDate,countHour,onlineduration,onlinestarttime,offlineduration,offlinestarttime,recordstate,recordduration,norecordduration,vqdstate,lalstate,createjobcount,repairjobcount,createTime,updateTime
        from ywcountvideoappraise
        <include refid="condition_Ywcountvideoappraise"/>
    </select> -->
    <select id="query_Ywcountvideoappraise" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            select tdmc videoname,a.xzbh as areanum,(select xzmc from ptxzs b where b.xzbh=a.xzbh ) as areaname,sbip
            ipaddress,
            round(to_number(to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss') -to_date( m.currentstatetime ,'yyyy-MM-dd
            hh24:mi:ss')))*24 as lxsc,
            m.currentstatetime as lxstarttime
            from sbtdspsr a left join sbxx b on a.sbbh=b.sbbh
            right join (
            select a.videonum , a.channelnum ,a.currentstatetime,a.isonline from
            ywvideohistorystate a,(
            select videonum , channelnum ,max(currentstatetime) currentstatetime from
            ywvideohistorystate where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ currentstatetime>=#startTime# ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ currentstatetime<=#endTime#]]>
            </isNotEmpty>
            group by videonum , channelnum ) b where a.videonum=b.videonum and a.channelnum=b.channelnum and
            a.currentstatetime=b.currentstatetime
            group by a.videonum ,a.channelnum,a.currentstatetime,a.isonline
            order by a.videonum ,a.channelnum,a.currentstatetime desc
            )m on a.sbbh=m.videonum and a.tdbh=channelnum where (m.isonline=0 or m.isonline is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ a.xzbh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            select machinename videoname,machineareanum as areanum,(select xzmc from ptxzs b where b.xzbh=machineareanum
            ) as areaname,machineip
            ipaddress,
            round(to_number(to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss') -to_date( m.currentstatetime ,'yyyy-MM-dd
            hh24:mi:ss')))*24 as lxsc,m.currentstatetime as
            lxstarttime
            from ywmachineinfo a right join (
            select a.machineid ,a.currentstatetime,a.currentstate from
            ywmachinehistorystate a,(
            select machineid ,max(currentstatetime) currentstatetime from
            ywmachinehistorystate where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ currentstatetime>=#startTime# ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ currentstatetime<=#endTime#]]>
            </isNotEmpty>
            group by machineid ) b where a.machineid=b.machineid and a.currentstatetime=b.currentstatetime
            group by a.machineid ,a.currentstatetime,a.currentstate
            order by a.machineid ,a.currentstatetime desc
            )m on a.machineid=m.machineid where (m.currentstate=0 or m.currentstate is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ machineareanum  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            select xhdmc videoname,qybh as areanum,(select xzmc from ptxzs b where b.xzbh=qybh ) as areaname,ip
            ipaddress,
            round(to_number(to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss') -to_date( m.create_time ,'yyyy-MM-dd
            hh24:mi:ss')))*24 as lxsc,m.create_time as
            lxstarttime
            from nhywjtxhdgl a right join (
            select a.noarea ,a.nojunc ,a.create_time,a.state from
            nhywjtxhdstate_history a,(
            select noarea ,nojunc,max(create_time) create_time from
            nhywjtxhdstate_history where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=#startTime# ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=#endTime#]]>
            </isNotEmpty>
            group by noarea ,nojunc ) b where a.noarea=b.noarea and a.nojunc=b.nojunc and a.create_time=b.create_time
            group by a.noarea ,a.nojunc ,a.create_time,a.state
            order by a.noarea ,a.nojunc,a.create_time desc
            )m on a.qybh=m.noarea and a.lkh=m.nojunc where (m.state=0 or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            select ledmc videoname,qybh as areanum,(select xzmc from ptxzs b where b.xzbh=qybh ) as areaname,ip
            ipaddress,
            round(to_number(to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss') -to_date( m.create_time ,'yyyy-MM-dd
            hh24:mi:ss')))*24 as lxsc,m.create_time as
            lxstarttime
            from nhywjtledydpgl a right join (
            select a.sbid ,a.xjsj create_time,a.sbzt from
            nhywjtledydpzt a,(
            select sbid ,max(xjsj) create_time from
            nhywjtledydpzt where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ xjsj>=#startTime# ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ xjsj<=#endTime#]]>
            </isNotEmpty>
            group by sbid ) b where a.sbid=b.sbid and a.xjsj=b.create_time
            group by a.sbid ,a.xjsj,a.sbzt
            order by a.sbid,a.xjsj desc
            )m on a.xh=m.sbid where (m.sbzt=0 or m.sbzt is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="10">
            select name videoname,regionid as areanum,(select xzmc from ptxzs b where b.xzbh=regionid ) as areaname,ip
            ipaddress,
            round(to_number(to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss') - m.create_time))*24 as lxsc,
            to_char( m.create_time ,'yyyy-MM-dd hh24:mi:ss') lxstarttime
            from its.ksh_detector a right join (
            select a.id ,a.create_time,a.state from
            its.ksh_detector_history a,(
            select id ,max(create_time) create_time from
            its.ksh_detector_history where detectortype='GVD'
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=to_date(#startTime# ,'yyyy-MM-dd hh24:mi:ss') ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss')  ]]>
            </isNotEmpty>
            group by id ) b where a.id=b.id and a.create_time=b.create_time
            group by a.id ,a.create_time,a.state
            order by a.id,a.create_time desc
            )m on a.id=m.id where (m.state=0 or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="11">
            select name videoname,regionid as areanum,(select xzmc from ptxzs b where b.xzbh=regionid ) as areaname,ip
            ipaddress,
            round(to_number(to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss') - m.create_time))*24 as lxsc,
            to_char( m.create_time ,'yyyy-MM-dd hh24:mi:ss') lxstarttime
            from its.ksh_detector a right join (
            select a.id ,a.create_time,a.state from
            its.ksh_detector_history a,(
            select id ,max(create_time) create_time from
            its.ksh_detector_history where detectortype='SMARTSENSOR'
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=to_date(#startTime# ,'yyyy-MM-dd hh24:mi:ss') ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss')]]>
            </isNotEmpty>
            group by id ) b where a.id=b.id and a.create_time=b.create_time
            group by a.id ,a.create_time,a.state
            order by a.id,a.create_time desc
            )m on a.id=m.id where (m.state=0 or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="12">
            select ldmc videoname,gxdwbh as areanum,(select xzmc from ptxzs b where b.xzbh=gxdwbh ) as areaname,ip
            ipaddress,
            round(to_number(to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss') - m.create_time))*24 as lxsc,
            to_char( m.create_time ,'yyyy-MM-dd hh24:mi:ss') as lxstarttime
            from its.ksh_zxld a right join (
            select a.xh ,a.create_time,a.state from
            its.ksh_zxldstate_history a,( select xh ,max(create_time) create_time from its.ksh_zxldstate_history where
            1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=to_date(#startTime# ,'yyyy-MM-dd hh24:mi:ss') ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=to_date(#endTime#,'yyyy-MM-dd hh24:mi:ss')]]>
            </isNotEmpty>
            group by xh ) b where a.xh=b.xh and a.create_time=b.create_time
            group by a.xh ,a.create_time,a.state
            order by a.xh,a.create_time desc
            )m
            on a.xh=m.xh where (m.state='0' or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ gxdwbh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>

        </isEqual>
    </select>

    <select id="get_Ywcountvideoappraise" resultClass="java.util.HashMap" parameterClass="java.util.Map"
            remapResults="true">
        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            SELECT a.videoname,a.areanum as areanum,a.areaname as areaname,a.ipaddress,
            sum(a.offlineduration) as lxsc,min(a.offlinestarttime) as lxstarttime
            FROM ywcountvideoappraise a
            right JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum )
            WHERE 1=1
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ d.xzbh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ countDate>=#startTime#  ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ countDate<=#endTime#  ]]>
            </isNotEmpty>
            GROUP BY
            a.videoname , a.devicenum , a.channelnum , a.ipaddress,a.areanum ,a.areaname
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            select c.machinename videoname ,c.machineareanum as areanum,(select xzmc from ptxzs b where
            b.xzbh=machineareanum ) as areaname,c.machineip ipaddress,
            sum(round(to_number(SYSDATE - to_date(a.currentstatetime, 'yyyy-mm-dd hh24:mi:ss')))*24) as
            lxsc,min(a.currentstatetime) as lxstarttime
            FROM ywmachinecurrentstate a right join ywmachineinfo c on a.machineid=c.machineid
            WHERE (currentstate!=1 or currentstate is null )
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ c.MACHINEAREANUM  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ currentstatetime>=#startTime#  ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ currentstatetime<=#endTime#  ]]>
            </isNotEmpty>
            GROUP BY c.machinename , c.MACHINEAREANUM , c.MACHINEIP
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            SELECT xhdmc videoname,qybh as areanum,(select xzmc from ptxzs b where b.xzbh=qybh ) as areaname,''
            ipaddress,
            sum(round(to_number(SYSDATE - to_date(statetime, 'yyyy-mm-dd hh24:mi:ss')))*24) as lxsc,min(statetime) as
            lxstarttime
            FROM nhywjtxhdgl
            WHERE (state=0 or state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ statetime>=#startTime#  ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ statetime<=#endTime#  ]]>
            </isNotEmpty>
            GROUP BY xhdmc , qybh
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            select ledmc videoname,qybh as areanum,(select xzmc from ptxzs b where b.xzbh=qybh ) as areaname,''
            ipaddress,
            sum(round(to_number(SYSDATE - to_date(statetime, 'yyyy-mm-dd hh24:mi:ss')))*24) as lxsc,min(statetime) as
            lxstarttime
            FROM nhywjtledydpgl
            WHERE (state=0 or state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ statetime>=#startTime#  ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ statetime<=#endTime#  ]]>
            </isNotEmpty>
            <isNotEmpty property='tableparam'>
                $tableparam$
            </isNotEmpty>
            GROUP BY ledmc , qybh
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="10">
            select name videoname,regionid as areanum,(select xzmc from ptxzs b where b.xzbh=regionid ) as areaname,ip
            ipaddress,
            sum(round(to_number(SYSDATE - to_date(statetime, 'yyyy-mm-dd hh24:mi:ss')))*24) as lxsc,min(statetime) as
            lxstarttime
            from its.ksh_detector
            WHERE (state=0 or state is null) and detectortype='GVD'
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ statetime>=#startTime#  ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ statetime<=#endTime#  ]]>
            </isNotEmpty>
            GROUP BY NAME , regionid,ip
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="11">
            select name videoname,regionid as areanum,(select xzmc from ptxzs b where b.xzbh=regionid ) as areaname,ip
            ipaddress,
            sum(round(to_number(SYSDATE - to_date(statetime, 'yyyy-mm-dd hh24:mi:ss')))*24) as lxsc,min(statetime) as
            lxstarttime
            from its.ksh_detector
            WHERE (state=0 or state is null) and detectortype='SMARTSENSOR'
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ statetime>=#startTime#  ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ statetime<=#endTime#  ]]>
            </isNotEmpty>
            GROUP BY NAME ,REGIONID,ip
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="12">
            select ldmc videoname,gxdwbh as areanum,(select xzmc from ptxzs b where b.xzbh=gxdwbh ) as areaname,''
            ipaddress,
            sum(round(to_number(SYSDATE - to_date(statetime, 'yyyy-mm-dd hh24:mi:ss')))*24) as lxsc,min(statetime) as
            lxstarttime
            from its.ksh_zxld
            WHERE (state=0 or state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ gxdwbh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ statetime>=#startTime#  ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ statetime<=#endTime#  ]]>
            </isNotEmpty>
            GROUP BY ldmc , gxdwbh
        </isEqual>
    </select>

    <select id="regionAssessmentCountList" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            select c.xzbh as areanum, xzmc as areaname,
            count(*)total,(select count(*) as jobtotal from ywrepairjobinfo e ,sbtdspsr f   where e.faultdeviceid=f.sbbh and
            e.channelnum=f.tdbh and c.xzbh=f.xzbh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e,sbtdspsr c where jobprocessstate in
            (903,907,915,912,913,916) and e.faultdeviceid=c.sbbh and e.channelnum=c.tdbh and c.xzbh=xzbh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum, sum(case when jd is not null and wd is not null then 1 else 0 end)
            lalnormalnum,
            (select count(*) as jobtotal from ywvideocurrentstate e ,sbtdspsr f where e.videonum=f.sbbh and
            e.channelnum=f.tdbh and e.isonline is  not null and  e.isonline!=0 and c.xzbh=f.xzbh) as zxsc
            from sbtdspsr c left join ptxzs b on b.xzbh=c.xzbh where sbbh is not null
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ c.xzbh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by c.xzbh,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            select machineareanum as areanum, xzmc as areaname,
            count(*)total,
            (select count(*) as jobtotal from ywrepairjobinfo e,ywmachineinfo f where e.faultdeviceid=f.machineid and
            c.machineareanum= f.machineareanum
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e,ywmachineinfo f where jobprocessstate in
            (903,907,915,912,913,916)
            and e.faultdeviceid=f.machineid and c.machineareanum= f.machineareanum
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum, 0 lalnormalnum,
            (select count(*) as jobtotal from ywmachinecurrentstate e ,ywmachineinfo f where e.machineid=f.machineid and
            c.machineareanum= f.machineareanum and currentstate=1) as zxsc
            from ywmachineinfo c left join ptxzs b on b.xzbh=c.machineareanum where machineid is not null
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ c.machineareanum  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by c.machineareanum,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            select qybh as areanum, xzmc as areaname,
            (select count(*) from nhywjtxhdgl where qybh=c.qybh)total,
            (select count(*) as jobtotal from ywrepairjobinfo e ,nhywjtxhdgl f where e.faultdeviceid=f.xh and
            f.qybh=c.qybh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal, sum(case when jd is not null and wd is not null then 1 else 0 end) lalnormalnum,
            (select count(*) as jobnormalnum from ywrepairjobinfo e,nhywjtxhdgl f where e.faultdeviceid=f.xh and
            jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=f.xh and f.qybh=c.qybh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,sum(decode(state,1,1,0))zxsc
            from nhywjtxhdgl c left join ptxzs b on c.qybh=b.xzbh where xh is not null
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ c.qybh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by qybh,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            select qybh as areanum,(select xzmc from ptxzs b where b.xzbh=qybh ) as areaname,
            count(*)total,
            (select count(*) as jobtotal from ywrepairjobinfo e ,nhywjtledydpgl f where e.faultdeviceid=f.xh and
            a.qybh=f.qybh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal, sum(case when jd is not null and wd is not null then 1 else 0 end) lalnormalnum,
            (select count(*) as jobnormalnum from ywrepairjobinfo e,nhywjtledydpgl f where e.faultdeviceid=f.xh and
            jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=f.xh and a.qybh=f.qybh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,
            sum(decode(state,1,1,0))zxsc
            from nhywjtledydpgl a left join ptxzs b on a.qybh=b.xzbh
            where 1=1
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by qybh,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="10">
            select regionid as areanum,xzmc as areaname,
            (select count(*) from its.ksh_detector where regionid=a.regionid and detectortype='smartsensor')total,
            sum(decode(state,1,1,0))zxsc,
            (select count(*) as jobtotal from ywrepairjobinfo e ,its.ksh_detector f where e.faultdeviceid=f.id and
            a.regionid=f.regionid
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal, sum(case when longitude is not null and latitude is not null then 1 else 0 end)
            lalnormalnum,
            (select count(*) as jobnormalnum from ywrepairjobinfo e,its.ksh_detector f where e.faultdeviceid=f.id and
            jobprocessstate in (903,907,915,912,913,916) and a.regionid=f.regionid
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum
            from its.ksh_detector a left join ptxzs b on a.regionid=b.xzbh
            where a.detectortype='SMARTSENSOR'
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>
            group by regionid,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="11">
            SELECT regionid as areanum,(select xzmc from ptxzs b where b.xzbh=regionid ) as areaname,
            count(*) total ,
            sum(decode(state,1,1,0))zxsc,
            (SELECT count(*) as jobtotal FROM ywrepairjobinfo e ,its.ksh_detector c where e.faultdeviceid=c.id and
            c.regionid=a.regionid
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal, sum(case when longitude is not null and latitude is not null then 1 else 0 end)
            lalnormalnum,
            (SELECT count(*) as jobnormalnum FROM ywrepairjobinfo e,its.ksh_detector c where e.faultdeviceid=c.id and
            jobprocessstate in (903,907,915,912,913,916) and c.regionid=a.regionid
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum
            FROM its.ksh_detector a left join ptxzs e on a.regionid=e.xzbh
            where a.detectortype='GVD'
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>
            GROUP BY regionid,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="12">
            select gxdwbh as areanum,xzmc as areaname,
            (select count(*) from its.ksh_zxld where gxdwbh=a.gxdwbh)total ,0 lxsc,0 vqdnormalnum,0 recordnormalnum,0
            recorddurationnormalnum,0
            norecorddurationnormalnum,0 jksum,
            sum(decode(state,1,1,0))zxsc,
            (select count(*) as jobtotal from ywrepairjobinfo e ,its.ksh_zxld c where e.faultdeviceid=c.xh and
            gxdwbh=a.gxdwbh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal, sum(case when jd is not null and wd is not null then 1 else 0 end) lalnormalnum,
            (select count(*) as jobnormalnum from ywrepairjobinfo e,its.ksh_zxld c where e.faultdeviceid=c.xh and
            jobprocessstate in (903,907,915,912,913,916) and gxdwbh=a.gxdwbh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum
            from its.ksh_zxld a left join ptxzs e on a.gxdwbh=e.xzbh
            where 1=1
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ gxdwbh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>
            group by gxdwbh,xzmc
        </isEqual>
        <!-- ,a.areaname -->
        <!-- <isNotEmpty property='order'>
            $order$
        </isNotEmpty>
        <isNotEmpty property='start'>
            limit $start$ , $limit$
        </isNotEmpty> -->
    </select>

    <select id="CountregionAssessmentCountList" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            select  count(count(*))
            from sbtdspsr c left join ptxzs b on b.xzbh=c.xzbh where sbbh is not null
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ c.xzbh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by c.xzbh,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            select count(count(*))
            from ywmachineinfo c left join ptxzs b on b.xzbh=c.machineareanum where machineid is not null
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ c.machineareanum  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by c.machineareanum,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            select count(count(*))
            from nhywjtxhdgl c left join ptxzs b on c.qybh=b.xzbh where xh is not null
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ c.qybh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by qybh,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            select count(count(*))
            from nhywjtledydpgl a left join ptxzs b on a.qybh=b.xzbh
            where 1=1
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            group by qybh,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="10">
            select count(count(*))
            from its.ksh_detector a left join ptxzs b on a.regionid=b.xzbh
            where a.detectortype='SMARTSENSOR'
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>
            group by regionid,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="11">
            SELECT count(count(*))
            FROM its.ksh_detector a left join ptxzs e on a.regionid=e.xzbh
            where a.detectortype='GVD'
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>
            GROUP BY regionid,xzmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="12">
            select count(count(*))
            from its.ksh_zxld a left join ptxzs e on a.gxdwbh=e.xzbh
            where 1=1
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ gxdwbh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>
            group by gxdwbh,xzmc
        </isEqual>
    </select>



    <select id="regionAssessmentCountListByRegion" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            select c.sbbh devicenum ,tdbh  as channelnum, tdmc videoname,
            count(*)total,(SELECT count(*) as jobtotal FROM ywrepairjobinfo e WHERE e.faultdeviceid=c.tdbh
            and e.channelnum=c.tdbh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e where jobprocessstate in
            (903,907,915,912,913,916) and e.faultdeviceid=c.sbbh and e.channelnum=c.tdbh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum, sum(case when jd is not null and wd is not null then 1 else 0 end)
            lalnormalnum,
            (select count(*) as jobtotal from ywvideocurrentstate e  where e.videonum=c.sbbh and
            e.channelnum=c.tdbh and  e.isonline is not null and  e.isonline!=0) as zxsc
            from sbtdspsr c
            <isNotEmpty prepend='where ' property='areanum'>
                (c.xzbh = #areanum# or (c.xzbh in (select xzbh from Ptxzs start with sjxzbh=
                #areanum# connect by sjxzbh =prior
                xzbh)))
            </isNotEmpty>
            group by c.sbbh,tdbh,tdmc
            <!---->
            <!--SELECT m.* from (select a.devicenum,a.channelnum ,a.videoname,-->
            <!--sum(a.offlineduration) AS lxsc,-->
            <!--sum(a.onlineduration) AS zxsc,-->
            <!--sum(a.vqdstate) AS vqdnormalnum,-->
            <!--sum(a.lalstate) AS lalnormalnum,-->
            <!--sum(a.recordstate) AS recordnormalnum,-->
            <!--sum(a.recordduration) AS recorddurationnormalnum,-->
            <!--sum(a.norecordduration) AS norecorddurationnormalnum,-->
            <!--count(*) as total,-->
            <!--(SELECT count(*) as jobtotal FROM ywrepairjobinfo e-->
            <!--WHERE 1=1-->
            <!--<isNotEmpty property='jobtableparam'>-->
            <!--$jobtableparam$-->
            <!--</isNotEmpty>-->
            <!--and e.faultdeviceid=a.devicenum and e.channelnum=a.channelnum ) as jobtotal,-->
            <!--(SELECT count(*) as jobnormalnum FROM ywrepairjobinfo e-->
            <!--WHERE jobprocessstate in (903,907,915,912,913,916)-->
            <!--<isNotEmpty property='jobtableparam'>-->
            <!--$jobtableparam$-->
            <!--</isNotEmpty>-->
            <!--and e.faultdeviceid=a.devicenum and e.channelnum=a.channelnum-->
            <!--) as jobnormalnum-->
            <!--FROM ywcountvideoappraise a-->
            <!--where 1=1-->
            <!--<isNotEmpty prepend='AND ' property='areanum'>-->
            <!--(a.areanum = #areanum# )-->
            <!--</isNotEmpty>-->
            <!--&lt;!&ndash; <isNotEmpty prepend='AND ' property='areanum'>-->
            <!--(d.xzbh = #areanum#  or (d.xzbh in (select xzbh from ptxzs where sjxzbh = #areanum#)) )-->
            <!--</isNotEmpty> &ndash;&gt;-->
            <!--<isNotEmpty property='tableparam'>-->
            <!--$tableparam$-->
            <!--</isNotEmpty>-->
            <!--GROUP BY-->
            <!--a.devicenum,a.channelnum,a.videoname-->
            <!--<isNotEmpty property='order'>-->
            <!--$order$-->
            <!--</isNotEmpty>-->
            <!--) m-->

            <!--&lt;!&ndash; LEFT JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum ) &ndash;&gt;-->
            <!--WHERE 1=1-->

        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            select c.machineid devicenum,'' channelnum ,c.machinename videoname,
            count(*) total,
            ,(select count(*) as jobtotal from ywrepairjobinfo e where e.faultdeviceid=c.machineid
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e where jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=c.machineid
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,0 lalnormalnum,
            (select count(*) as jobtotal from ywmachinecurrentstate e where e.machineid=c.machineid and currentstate=1)
            as zxsc
            from ywmachineinfo c where machineid is not null
            <isNotEmpty prepend='AND ' property='areanum'>
                (c.machineareanum = #areanum# or (c.machineareanum in (select xzbh from ptxzs start with sjxzbh=
                #areanum# connect by sjxzbh =prior
                xzbh)))
            </isNotEmpty>
            group by  c.machineid,c.machinename
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            select xh devicenum,'' channelnum ,xhdmc videoname,
            count(*) total,
            ,(select count(*) as jobtotal from ywrepairjobinfo e where e.faultdeviceid=a.xh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e where jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=a.xh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,
            sum(case when jd is not null and wd is not null then 1 else 0 end) lalnormalnum,
            sum(decode(state,1,1,0))zxsc,
            from nhywjtxhdgl a
            <isNotEmpty prepend='where ' property='areanum'>
                (qybh = #areanum# or (qybh in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by sjxzbh
                =prior xzbh)))
            </isNotEmpty>
            group by xh,xhdmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            select xh devicenum,'' channelnum ,ledmc videoname,
            count(*) total,
            (SELECT count(*) as jobtotal FROM ywrepairjobinfo e WHERE e.faultdeviceid=a.xh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e where jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=a.xh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,
            sum(case when jd is not null and wd is not null then 1 else 0 end) lalnormalnum,
            sum(decode(state,1,1,0))zxsc
            from nhywjtledydpgl a
            <isNotEmpty prepend='where' property='areanum'>
                (qybh = #areanum# or (qybh in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by sjxzbh
                =prior xzbh)))
            </isNotEmpty>
            group by xh,ledmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="10">
            select id devicenum,'' channelnum ,name videoname,
            count(*) total,
            (select count(*) as jobtotal from ywrepairjobinfo e where e.faultdeviceid=a.id
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e where jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=a.id
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,
            sum(case when longitude is not null and latitude is not null then 1 else 0 end) lalnormalnum,
            sum(decode(state,1,1,0))zxsc
            from its.ksh_detector a
            where a.detectortype='SMARTSENSOR'
            <isNotEmpty prepend='where' property='areanum'>
                (regionid = #areanum# or (regionid in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by
                sjxzbh =prior xzbh)))
            </isNotEmpty>
            group by id,name
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="11">
            select id devicenum,'' channelnum ,name videoname,
            count(*) total,
            (select count(*) as jobtotal from ywrepairjobinfo e where e.faultdeviceid=a.id
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e where jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=a.id
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,
            sum(case when longitude is not null and latitude is not null then 1 else 0 end) lalnormalnum,
            sum(decode(state,1,1,0))zxsc
            from its.ksh_detector a      where a.detectortype='GVD'
            <isNotEmpty prepend='AND' property='areanum'>
                (regionid = #areanum# or (regionid in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by
                sjxzbh =prior xzbh)))
            </isNotEmpty>
            group by id,name
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="12">
            select xh devicenum,'' channelnum ,ldmc videoname,
            count(*) total,
            (select count(*) as jobtotal from ywrepairjobinfo e where e.faultdeviceid=a.xh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobtotal,
            (select count(*) as jobnormalnum from ywrepairjobinfo e where jobprocessstate in (903,907,915,912,913,916)
            and e.faultdeviceid=a.xh
            <isNotEmpty property='jobtableparam'>
                $jobtableparam$
            </isNotEmpty>) as jobnormalnum,
            sum(decode(state,1,1,0))zxsc,
            sum(case when jd is not null and wd is not null then 1 else 0 end) lalnormalnum,
            max( decode(state,1, round(to_number(sysdate - to_date(statetime, 'yyyy-mm-dd hh24:mi:ss')))*24,0)) as zx
            from its.ksh_zxld a
            <isNotEmpty prepend='where' property='areanum'>
                (gxdwbh = #areanum# or (gxdwbh in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by sjxzbh
                =prior xzbh)))
            </isNotEmpty>
            group by xh,ldmc
        </isEqual>
    </select>
    <select id="count_regionAssessmentCountListByRegion" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            <!--select count(*) from (select-->
            <!--a.devicenum,a.channelnum ,a.videoname-->
            <!--from ywcountvideoappraise a-->
            <!--where 1=1-->
            <!--<isNotEmpty prepend='AND ' property='areanum'>-->
                <!--(a.areanum = #areanum# )-->
            <!--</isNotEmpty>-->
            <!--<isNotEmpty property='tableparam'>-->
                <!--$tableparam$-->
            <!--</isNotEmpty>-->
            <!--group by     a.devicenum,a.channelnum,a.videoname-->
            <!--) m-->


            SELECT count(count(*)) FROM sbtdspsr a
            <isNotEmpty prepend='where' property='areanum'>
                (xzbh = #areanum# or (xzbh in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by sjxzbh
                =prior xzbh)))
            </isNotEmpty>
            GROUP BY sbbh,tdbh
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            SELECT count(count(*))
            FROM ywmachineinfo c
            <isNotEmpty prepend='where ' property='areanum'>
                (c.MACHINEAREANUM = #areanum# or (c.MACHINEAREANUM in (select xzbh from Ptxzs start with sjxzbh=
                #areanum# connect by sjxzbh =prior xzbh)))
            </isNotEmpty>
            group by c.machineid,c.machinename
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            SELECT count(count(*)) FROM nhywjtxhdgl a
            <isNotEmpty prepend='where' property='areanum'>
                (qybh = #areanum# or (qybh in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by sjxzbh
                =prior xzbh)))
            </isNotEmpty>
            GROUP BY xh,xhdmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            SELECT count(count(*))
            FROM nhywjtledydpgl a
            <isNotEmpty prepend='where' property='areanum'>
                (qybh = #areanum# or (qybh in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by sjxzbh
                =prior xzbh)))
            </isNotEmpty>
            GROUP BY xh,ledmc
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="10">
            SELECT count(count(*))
            FROM its.ksh_detector a
            where a.detectortype='SMARTSENSOR'
            <isNotEmpty prepend='AND ' property='areanum'>
                (regionid = #areanum# or (regionid in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by
                sjxzbh =prior xzbh)))
            </isNotEmpty>
            GROUP BY id,name
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="11">
            select count(count(*))
            from its.ksh_detector a left join ywrepairjobinfo e on a.id=e.faultdeviceid
            where a.detectortype='GVD'
            <isNotEmpty prepend='AND ' property='xzbh'>
                (regionid = #areanum# or (regionid in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by
                sjxzbh =prior xzbh)))
            </isNotEmpty>
            group by id,name
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="12">
            select count(count(*))
            from its.ksh_zxld a
            where xh is not null
            <isNotEmpty prepend='AND ' property='areanum'>
                (gxdwbh = #areanum# or (gxdwbh in (select xzbh from Ptxzs start with sjxzbh= #areanum# connect by sjxzbh
                =prior xzbh)))
            </isNotEmpty>
            group by xh,ldmc
        </isEqual>

    </select>
    <select id="getVideoCount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(*) from
        (select devicenum,channelnum
        from ywcountvideoappraise where 1=1
        <isNotEmpty prepend="and" property='areanum'>
            areanum = #areanum#
        </isNotEmpty>
        <isNotEmpty property='tableparam'>
            $tableparam$
        </isNotEmpty>
        group by devicenum,channelnum)

    </select>
    <select id="jobcount" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        SELECT
        count(*) as jobtotal,
        (select count(*) from ywrepairjobinfo e
        LEFT JOIN sbtdspsr g on (g.sbbh=e.faultdeviceid and g.tdbh=e.channelnum )
        where jobprocessstate in (903,907,915,912,913,916)
        <isNotEmpty prepend='AND ' property='areanum'>
            (g.xzbh = #areanum# or (g.xzbh in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
        </isNotEmpty>
        <isNotEmpty property='jobtableparam'>
            $jobtableparam$
        </isNotEmpty>
        ) as jobnormalnum
        from
        ywrepairjobinfo a
        left join sbtdspsr d on (d.sbbh=a.faultdeviceid and d.tdbh=a.channelnum )
        where 1=1
        <isNotEmpty prepend='AND ' property='areanum'>
            (d.xzbh = #areanum# or (d.xzbh in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
        </isNotEmpty>
        <isNotEmpty property='jobtableparam'>
            $jobtableparam$
        </isNotEmpty>

    </select>
    <select id="getWeight" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		SELECT weightId,onlineweight,videoweight,recordweight,lalweight,repairweight,remarks FROM ywcountvideoweight ORDER BY modifytime desc
	</select>

    <!-- query objects totalcount -->
    <select id="count_Ywcountvideoappraise" resultClass="java.lang.Integer" parameterClass="java.util.Map">

        <isEqual prepend="" property="alarmdevicetype" compareValue="1">
            select count(*)
            from sbtdspsr a left join sbxx b on a.sbbh=b.sbbh
            right join (
            select a.videonum , a.channelnum ,a.currentstatetime,a.isonline from
            ywvideohistorystate a,(
            select videonum , channelnum ,max(currentstatetime) currentstatetime from
            ywvideohistorystate where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ currentstatetime>=#startTime# ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ currentstatetime<=#endTime#]]>
            </isNotEmpty>
            group by videonum , channelnum ) b where a.videonum=b.videonum and a.channelnum=b.channelnum and
            a.currentstatetime=b.currentstatetime
            group by a.videonum ,a.channelnum,a.currentstatetime,a.isonline
            order by a.videonum ,a.channelnum,a.currentstatetime desc
            )m on a.sbbh=m.videonum and a.tdbh=channelnum where (m.isonline=0 or m.isonline is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ a.xzbh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="2">
            select count(*)
            from ywmachineinfo a right join (
            select a.machineid ,a.currentstatetime,a.currentstate from
            ywmachinehistorystate a,(
            select machineid ,max(currentstatetime) currentstatetime from
            ywmachinehistorystate where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ currentstatetime>=#startTime#]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ currentstatetime<=#endTime#]]>
            </isNotEmpty>
            group by machineid ) b where a.machineid=b.machineid and a.currentstatetime=b.currentstatetime
            group by a.machineid ,a.currentstatetime,a.currentstate
            order by a.machineid ,a.currentstatetime desc
            )m on a.machineid=m.machineid where (m.currentstate=0 or m.currentstate is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ machineareanum  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="3">
            select count(*)
            from nhywjtxhdgl a right join (
            select a.noarea ,a.nojunc ,a.create_time,a.state from
            nhywjtxhdstate_history a,(
            select noarea ,nojunc,max(create_time) create_time from
            nhywjtxhdstate_history where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=#startTime# ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=#endTime#]]>
            </isNotEmpty>
            group by noarea ,nojunc ) b where a.noarea=b.noarea and a.nojunc=b.nojunc and a.create_time=b.create_time
            group by a.noarea ,a.nojunc ,a.create_time,a.state
            order by a.noarea ,a.nojunc,a.create_time desc
            )m on a.qybh=m.noarea and a.lkh=m.nojunc where (m.state=0 or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="4">
            select count(*)
            from nhywjtledydpgl a right join (
            select a.sbid ,a.xjsj create_time,a.sbzt from
            nhywjtledydpzt a,(
            select sbid ,max(xjsj) create_time from
            nhywjtledydpzt where 1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ xjsj>=#startTime# ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ xjsj<=#endTime#]]>
            </isNotEmpty>
            group by sbid ) b where a.sbid=b.sbid and a.xjsj=b.create_time
            group by a.sbid ,a.xjsj,a.sbzt
            order by a.sbid,a.xjsj desc
            )m on a.xh=m.sbid where (m.sbzt=0 or m.sbzt is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ qybh  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="10">
            select count(*)
            from its.ksh_detector a right join (
            select a.id ,a.create_time,a.state from
            its.ksh_detector_history a,(
            select id ,max(create_time) create_time from
            its.ksh_detector_history where detectortype='GVD'
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=to_date(#startTime# ,'yyyy-MM-dd hh24:mi:ss')   ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=to_date(#endTime# ,'yyyy-MM-dd hh24:mi:ss')]]>
            </isNotEmpty>
            group by id ) b where a.id=b.id and a.create_time=b.create_time
            group by a.id ,a.create_time,a.state
            order by a.id,a.create_time desc
            )m on a.id=m.id where (m.state=0 or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="11">
            select count(*)
            from its.ksh_detector a right join (
            select a.id ,a.create_time,a.state from
            its.ksh_detector_history a,(
            select id ,max(create_time) create_time from
            its.ksh_detector_history where detectortype='SMARTSENSOR'
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=to_date(#startTime# ,'yyyy-MM-dd hh24:mi:ss') ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=to_date(#endTime# ,'yyyy-MM-dd hh24:mi:ss')]]>
            </isNotEmpty>
            group by id ) b where a.id=b.id and a.create_time=b.create_time
            group by a.id ,a.create_time,a.state
            order by a.id,a.create_time desc
            )m on a.id=m.id where (m.state=0 or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ regionid  in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
        <isEqual prepend="" property="alarmdevicetype" compareValue="12">
            select count(*)
            from its.ksh_zxld a right join (
            select a.xh ,a.create_time,a.state from
            its.ksh_zxldstate_history a,( select xh ,max(create_time) create_time from its.ksh_zxldstate_history where
            1=1
            <isNotEmpty prepend='AND ' property='startTime'>
                <![CDATA[ create_time>=to_date(#startTime# ,'yyyy-MM-dd hh24:mi:ss') ]]>
            </isNotEmpty>
            <isNotEmpty prepend='AND ' property='endTime'>
                <![CDATA[ create_time<=to_date(#endTime# ,'yyyy-MM-dd hh24:mi:ss')]]>
            </isNotEmpty>
            group by xh ) b where a.xh=b.xh and a.create_time=b.create_time
            group by a.xh ,a.create_time,a.state
            order by a.xh,a.create_time desc
            )m
            on a.xh=m.xh where (m.state='0' or m.state is null)
            <isNotEmpty prepend='AND ' property='xzbh'>
                <![CDATA[ gxdwbh in ]]>
                <iterate property="xzbh" conjunction="," open="(" close=")">
                    #xzbh[]#
                </iterate>
            </isNotEmpty>
        </isEqual>
    </select>

    <!-- batch add objects -->
    <insert id="batch_insert_Ywcountvideoappraise" parameterClass="java.util.List">
        <![CDATA[
          INSERT
      	]]>
        <iterate conjunction=" ">
            into ywcountvideoappraise(
            <![CDATA[
				countId,areanum,areaname,videoname,devicenum,channelnum,ipaddress,countMonth,countDate,countHour,onlineduration,onlinestarttime,offlineduration,offlinestarttime,recordstate,recordduration,norecordduration,vqdstate,lalstate,createjobcount,repairjobcount,createTime,updateTime
			)values
			 ]]>
            <![CDATA[
				(
				#list[].countId#,#list[].areanum#,#list[].areaname#,#list[].videoname#,#list[].devicenum#,#list[].channelnum#,#list[].ipaddress#,#list[].countMonth#,#list[].countDate#,#list[].countHour#,#list[].onlineduration#,#list[].onlinestarttime#,#list[].offlineduration#,#list[].offlinestarttime#,#list[].recordstate#,#list[].recordduration#,#list[].norecordduration#,#list[].vqdstate#,#list[].lalstate#,#list[].createjobcount#,#list[].repairjobcount#,#list[].createTime#,#list[].updateTime#
				)
			]]>
        </iterate>
    </insert>

    <!-- µãÎ»Â¼ÏñÇé¿öÍ³¼Æ                           /start   -->
    <select id="query_dwlx" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select a.areanum, a.areaname, a.videoname, a.ipaddress,
        trim(cast(sum(a.recordduration) as char(8))) as recordduration,
        trim(cast(sum(a.norecordduration) as char(8))) as norecordduration from ywcountvideoappraise a
        right JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum )
        WHERE 1=1
        <isNotEmpty prepend='AND ' property='xzbh'>
            <![CDATA[ d.xzbh in ]]>
            <iterate property="xzbh" conjunction="," open="(" close=")">
                #xzbh[]#
            </iterate>
        </isNotEmpty>
        <!-- <isNotEmpty prepend="and" property="xzqh">
            areanum = #xzqh#
        </isNotEmpty> -->
        <isNotEmpty prepend="and" property="videoname">
            a.videoname like '%$videoname$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="ipaddress">
            a.ipaddress like '%$ipaddress$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="timeStart">
            a.countDate >= #timeStart#
        </isNotEmpty>
        <isNotEmpty prepend="and" property="timeEnd">
            <![CDATA[	a.countDate <= #timeEnd#  ]]>
        </isNotEmpty>
        group by a.areanum, a.areaname, a.videoname, a.ipaddress,a.devicenum
    </select>

    <select id="count_dwlx" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(*) from (
        select a.areanum, a.areaname, a.videoname, a.ipaddress
        from ywcountvideoappraise a
        right JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum )
        WHERE 1=1
        <isNotEmpty prepend='AND ' property='xzbh'>
            <![CDATA[ d.xzbh in ]]>
            <iterate property="xzbh" conjunction="," open="(" close=")">
                #xzbh[]#
            </iterate>
        </isNotEmpty>
        <!-- <isNotEmpty prepend="and" property="xzqh">
            areanum = #xzqh#
        </isNotEmpty> -->
        <isNotEmpty prepend="and" property="videoname">
            a.videoname like '%$videoname$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="ipaddress">
            a.ipaddress like '%$ipaddress$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="timeStart">
            a.countDate >= #timeStart#
        </isNotEmpty>
        <isNotEmpty prepend="and" property="timeEnd">
            <![CDATA[
				a.countDate <= #timeEnd#  
			]]>
        </isNotEmpty>
        group by a.areanum, a.areaname, a.videoname, a.ipaddress,a.devicenum
        ) x
    </select>
    <!-- Í³¼ÆÍ¼  Öù×´Í¼-->
    <select id="show_bar" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select a.areanum, a.areaname,
        trim(cast(sum(a.recordduration) as char(8))) as recordduration,
        trim(cast(sum(a.norecordduration) as char(8))) as norecordduration from ywcountvideoappraise a
        right JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum )
        WHERE 1=1
        <isNotEmpty prepend='AND ' property='xzbh'>
            <![CDATA[ d.xzbh in ]]>
            <iterate property="xzbh" conjunction="," open="(" close=")">
                #xzbh[]#
            </iterate>
        </isNotEmpty>
        <!-- <isNotEmpty prepend="and" property="xzqh">
            areanum = #xzqh#
        </isNotEmpty> -->
        <isNotEmpty prepend="and" property="videoname">
            a.videoname like '%$videoname$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="ipaddress">
            a.ipaddress like '%$ipaddress$%'
        </isNotEmpty>
        <isNotEmpty prepend="and" property="timeStart">
            a.countDate >= #timeStart#
        </isNotEmpty>
        <isNotEmpty prepend="and" property="timeEnd">
            <![CDATA[ a.countDate <= #timeEnd#  	]]>
        </isNotEmpty>
        group by areanum, areaname
    </select>
    <!-- µãÎ»Â¼ÏñÇé¿öÍ³¼Æ                           /end   -->
</sqlMap>











