<?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"/> -->	<typeAlias alias="Ywcountvideoappraise" type="cn.yw..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 1=1 
			<isNotEmpty prepend='AND' property='XGTJcountId'> countId = #XGTJcountId#</isNotEmpty>
			<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 1=1 
			<isNotEmpty prepend='AND' property='XGTJweightId'> weightId = #XGTJweightId#</isNotEmpty>
			<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 
		<include refid="condition_Ywcountvideoappraise"/>
	</delete>
	
	<!-- batch delete object -->
	<delete id="batch_delete_Ywcountvideoappraise" parameterClass="java.util.Map">
		delete from ywcountvideoappraise 
		where 1=1
			<isNotEmpty prepend='AND' property='keyword'>
			key  in (#keyword#)
			</isNotEmpty>
	</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">
		SELECT a.areanum as areanum,a.areaname as areaname,a.videoname,a.devicenum,a.channelnum,a.ipaddress,
		 max(a.countDate) as countdate,
		sum(a.offlineduration) as lxsc,min(a.offlinestarttime) as lxstarttime
		FROM ywcountvideoappraise a
		right JOIN ywvideosyncchannel d on (d.videonum=a.devicenum and d.channelnum=a.channelnum )
		<isNotEmpty prepend=' ' property='bmbh'>
			right JOIN sbtdjqqx b on (b.sbbh=a.devicenum and b.tdbh=a.channelnum )
		</isNotEmpty>
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='areanum'>
				(d.admincatalogue = #areanum#  or (d.admincatalogue in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
		</isNotEmpty>
			<isNotEmpty property='tableparam'>
				$tableparam$
			</isNotEmpty>
			GROUP BY
			a.videoname , a.devicenum , a.channelnum  , a.ipaddress,a.areanum ,a.areaname 
		<isNotEmpty property='order'>
			$order$
		</isNotEmpty>
	</select>
	<select id="regionAssessmentCountList" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		SELECT
			a.areanum,
			max(a.areaname) as areaname,
			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(*)  from ywvideosyncchannel  g
	        	<isNotEmpty prepend=' ' property='bmbh'>
					 	right JOIN sbtdjqqx b on (b.sbbh=g.videonum and b.tdbh=g.channelnum )
				</isNotEmpty>
				 where admincatalogue=a.areanum
	        ) as jksum  ,
			(SELECT count(*) as jobtotal FROM ywrepairjobinfo e
				<isNotEmpty prepend=' ' property='bmbh'>
					 	LEFT JOIN sbtdjqqx b on (b.sbbh=e.faultdeviceid and b.tdbh=e.channelnum )
				</isNotEmpty>
				LEFT JOIN ywvideosyncchannel g on (g.videonum=e.faultdeviceid and g.channelnum=e.channelnum )
				WHERE g.admincatalogue=a.areanum
					<isNotEmpty property='jobtableparam'>
						$jobtableparam$
					</isNotEmpty>
					<isNotEmpty prepend='and' property='bmbh'>
						 b.bmbh = #bmbh#
					</isNotEmpty>
					) as jobtotal,
			(SELECT count(*) as jobnormalnum FROM ywrepairjobinfo e
					<isNotEmpty prepend=' ' property='bmbh'>
						 	LEFT JOIN sbtdjqqx b on (b.sbbh=e.faultdeviceid and b.tdbh=e.channelnum )
					</isNotEmpty>
					LEFT JOIN ywvideosyncchannel g on (g.videonum=e.faultdeviceid and g.channelnum=e.channelnum )
				WHERE g.admincatalogue=a.areanum
					<isNotEmpty prepend='and' property='bmbh'>
							 b.bmbh = #bmbh#
						</isNotEmpty>
					and jobprocessstate in (903,907,911,912,913) 
						<isNotEmpty property='jobtableparam'>
							$jobtableparam$
						</isNotEmpty>
						
						) as jobnormalnum
		FROM ywcountvideoappraise a
		<isNotEmpty prepend=' ' property='bmbh'>
			 	LEFT JOIN sbtdjqqx b on (b.sbbh=a.devicenum and b.tdbh=a.channelnum )
		</isNotEmpty>
		LEFT JOIN ywvideosyncchannel d on (d.videonum=a.devicenum and d.channelnum=a.channelnum )
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		
		<isNotEmpty prepend='and' property='areanum'>
				(d.admincatalogue = #areanum#  or (d.admincatalogue in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
		</isNotEmpty>
		<isNotEmpty property='tableparam'>
			$tableparam$
		</isNotEmpty>
			
		GROUP BY
				a.areanum 
	<!-- ,a.areaname -->
		<!-- <isNotEmpty property='order'>
			$order$
		</isNotEmpty>
		<isNotEmpty property='start'>
			limit $start$ , $limit$
		</isNotEmpty> -->
	</select>
	
	<select id="regionAssessmentCountListByRegion" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		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,911,912,913) 
						<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>
			<!-- <isNotEmpty prepend='and' property='areanum'>
					(d.xzbh = #areanum#  or (d.xzbh in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
			</isNotEmpty> -->
			<isNotEmpty property='tableparam'>
				$tableparam$
			</isNotEmpty>
				
			GROUP BY
				a.devicenum,a.channelnum,a.videoname
			<isNotEmpty property='order'>
				$order$
			</isNotEmpty>
		) m
		<isNotEmpty prepend=' ' property='bmbh'>
			 	LEFT JOIN sbtdjqqx b on (b.sbbh=m.devicenum and b.tdbh=m.channelnum )
		</isNotEmpty>
		
		<!-- LEFT JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum ) -->
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		
		
		<!-- <isNotEmpty property='start'>
			limit $start$ , $limit$
		</isNotEmpty> -->
	</select>
	<select id="count_regionAssessmentCountListByRegion" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		SELECT count(*) 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,911,912,913) 
						<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>
			<!-- <isNotEmpty prepend='and' property='areanum'>
					(d.xzbh = #areanum#  or (d.xzbh in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
			</isNotEmpty> -->
			<isNotEmpty property='tableparam'>
				$tableparam$
			</isNotEmpty>
				
			GROUP BY
				a.devicenum,a.channelnum,a.videoname
		) m
		<isNotEmpty prepend=' ' property='bmbh'>
			 	LEFT JOIN sbtdjqqx b on (b.sbbh=m.devicenum and b.tdbh=m.channelnum )
		</isNotEmpty>
		
		<!-- LEFT JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum ) -->
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		
		
		<!-- <isNotEmpty property='start'>
			limit $start$ , $limit$
		</isNotEmpty> -->
	</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) m
	       
	</select>
	<select id="jobcount" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		SELECT
			count(*) as jobtotal,
		(select count(*) from ywrepairjobinfo e
			LEFT JOIN sbtdjqqx f on (f.sbbh=e.faultdeviceid and f.tdbh=e.channelnum )
			LEFT JOIN sbtdspsr g on (g.sbbh=e.faultdeviceid and g.tdbh=e.channelnum )
			 where jobprocessstate in (903,907,911,912,913)
				<!-- <isNotEmpty prepend='and' property='bmbh'>
					 b.bmbh = #bmbh#
				</isNotEmpty> -->
		
				<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 sbtdjqqx b on (b.sbbh=a.faultdeviceid and b.tdbh=a.channelnum )
		LEFT JOIN sbtdspsr d on (d.sbbh=a.faultdeviceid and d.tdbh=a.channelnum )
		WHERE 1=1
		<!-- <isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty> -->
		
		<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">
		SELECT count(*) from (
		SELECT a.videoname,a.devicenum,a.channelnum,a.ipaddress
		FROM ywcountvideoappraise a
		<isNotEmpty prepend=' ' property='bmbh'>
			LEFT JOIN sbtdjqqx b on (b.sbbh=a.devicenum and b.tdbh=a.channelnum )
		</isNotEmpty>
		LEFT JOIN sbtdspsr d on (d.sbbh=a.devicenum and d.tdbh=a.channelnum )
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		
		<isNotEmpty prepend='and' property='areanum'>
				(d.xzbh = #areanum#  or (d.xzbh in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
		</isNotEmpty>
			<isNotEmpty property='tableparam'>
				$tableparam$
			</isNotEmpty>
			
		GROUP BY
			a.videoname , a.devicenum , a.channelnum  , a.ipaddress,a.areanum ,a.areaname 	) c		
	</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 ywvideosyncchannel d on (d.videonum=a.devicenum and d.channelnum=a.channelnum )
		<isNotEmpty prepend=' ' property='bmbh'>
			 	right JOIN sbtdjqqx b on (b.sbbh=a.devicenum and b.tdbh=a.channelnum )
		</isNotEmpty>
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='areanum'>
				(d.admincatalogue = #areanum#  or (d.admincatalogue in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
		</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 ywvideosyncchannel d on (d.videonum=a.devicenum and d.channelnum=a.channelnum )
		<isNotEmpty prepend=' ' property='bmbh'>
			 	right JOIN sbtdjqqx b on (b.sbbh=a.devicenum and b.tdbh=a.channelnum )
		</isNotEmpty>
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='areanum'>
				(d.admincatalogue = #areanum#  or (d.admincatalogue in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
		</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 ywvideosyncchannel d on (d.videonum=a.devicenum and d.channelnum=a.channelnum )
		<isNotEmpty prepend=' ' property='bmbh'>
			 	right JOIN sbtdjqqx b on (b.sbbh=a.devicenum and b.tdbh=a.channelnum )
		</isNotEmpty>
		WHERE 1=1
		<isNotEmpty prepend='and' property='bmbh'>
			 b.bmbh = #bmbh#
		</isNotEmpty>
		<isNotEmpty prepend='and' property='areanum'>
				(d.admincatalogue = #areanum#  or (d.admincatalogue in (select xzbh from ptxzs where sjxzbh = #areanum#)) )
		</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>











