<?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="Ywcountuserrepairjobbean" type="cn.vp.XXX.model.YwcountuserrepairjobBean"/> -->	<typeAlias alias="Ywcountuserrepairjob" type="cn.im.ywgl.YwcountuserrepairjobBean"/>
	
	<sql id="condition_Ywcountuserrepairjob">
		where 1=1
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			countDate >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			countDate <= #timeEnd#
		 ]]>
		</isNotEmpty>
		<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='LIKEuseraccount'>
			(useraccount like '%$LIKEuseraccount$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='useraccount'>
			(useraccount = #useraccount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='LIKEusername'>
			(username like '%$LIKEusername$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='username'>
			(username = #username# )
		</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='LIKEreportjobcount'>
			(reportjobcount like '%$LIKEreportjobcount$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='reportjobcount'>
			(reportjobcount = #reportjobcount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='LIKErejectjobcount'>
			(rejectjobcount like '%$LIKErejectjobcount$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='rejectjobcount'>
			(rejectjobcount = #rejectjobcount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='LIKEacceptjobcount'>
			(acceptjobcount like '%$LIKEacceptjobcount$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='acceptjobcount'>
			(acceptjobcount = #acceptjobcount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='LIKEfeedbackjobcount'>
			(feedbackjobcount like '%$LIKEfeedbackjobcount$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='feedbackjobcount'>
			(feedbackjobcount = #feedbackjobcount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='LIKEhangupjobcount'>
			(hangupjobcount like '%$LIKEhangupjobcount$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='hangupjobcount'>
			(hangupjobcount = #hangupjobcount# )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='LIKEconfirmjobcount'>
			(confirmjobcount like '%$LIKEconfirmjobcount$%'  )
		</isNotEmpty>
		<isNotEmpty prepend='AND ' property='confirmjobcount'>
			(confirmjobcount = #confirmjobcount# )
		</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>
	
	
	<!-- query objects by page-->
	<select id="query_Ywcountuserrepairjob" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		<!--select areanum,areaname,useraccount,username,-->
		<!--trim(cast(sum(reportjobcount) as char(8))) as reportjobcount,-->
		<!--trim(cast(sum(rejectjobcount) as char(8))) as rejectjobcount,-->
		<!--trim(cast(sum(acceptjobcount) as char(8))) as acceptjobcount,-->
		<!--trim(cast(sum(feedbackjobcount) as char(8))) as feedbackjobcount,-->
		<!--trim(cast(sum(hangupjobcount) as char(8))) as hangupjobcount,-->
		<!--trim(cast(sum(confirmjobcount) as char(8))) as confirmjobcount-->
		<!--from ywcountuserrepairjob-->
		<!--<include refid="condition_Ywcountuserrepairjob"/>-->
		<!--group by areanum,areaname,useraccount,username-->

		select  t.* ,(select yhmc from ptyh where yhzh=useraccount)username ,(select b.bmmc from ptyh a ,ptyhbm b  where yhzh=useraccount and a.bmbh=b.bmbh)areaname from  (
		select  sum(total) total,name ,useraccount from (
		select  count(*) total ,'reportjobcount' name  , jobreportpeople useraccount
		from ywrepairjobinfo
		where 1=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobreportpeople
		union all
		select  count(*)total,'rejectjobcount' name , jobacceptpeople useraccount from ywrepairjobinfo
		where isreject=1 and  jobacceptpeople is not null and jobacceptpeople!=''
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobacceptpeople
		union all
		select  count(*)total,'rejectjobcount' name , receiverpeople useraccount from ywrepairjobinfo
		where isreject=1 and  receiverpeople is not null and receiverpeople!=''
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  receiverpeople
		union all
		select  count(*)total,'acceptjobcount' name , jobacceptpeople useraccount  from ywrepairjobinfo
		where  (isreject=0 or isreject is null )
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobacceptpeople
		union all
		select  count(*)total, 'feedbackjobcount'name , feedbackpeople useraccount from ywrepairjobinfo
		where 1=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  feedbackpeople
		union all
		select  count(*)total, 'hangupjobcount'  , jobacceptpeople useraccount from ywrepairjobinfo where  (ishangup=1)
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobacceptpeople
		union all
		select  count(*) total, 'confirmjobcount' , confirmpeople useraccount  from ywrepairjobinfo
		where 1=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  confirmpeople
		)group by useraccount ,name
		) pivot (sum(total) for name in ('feedbackjobcount' feedbackjobcount,'hangupjobcount' hangupjobcount ,'confirmjobcount' confirmjobcount,'rejectjobcount' rejectjobcount ,
		'reportjobcount' reportjobcount,'acceptjobcount' acceptjobcount))t
		<!-- <isNotEmpty property='start'>
			limit $start$ , $limit$
		</isNotEmpty> -->		
	</select>


	<!-- query objects totalcount -->
	<select id="count_Ywcountuserrepairjob" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		<!--select count(*) from (-->
			<!--select areanum,areaname,useraccount,username-->
			<!--from ywcountuserrepairjob -->
			<!--<include refid="condition_Ywcountuserrepairjob"/>-->
			<!--group by areanum,areaname,useraccount,username-->
		<!--) x-->
		select  count(*) from  (
		select  sum(total) total,name ,useraccount from (
		select  count(*) total ,'reportjobcount' name  , jobreportpeople useraccount
		from ywrepairjobinfo
		where 1=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobreportpeople
		union all
		select  count(*)total,'rejectjobcount' name , jobacceptpeople useraccount from ywrepairjobinfo
		where isreject=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobacceptpeople
		union all
		select  count(*)total,'rejectjobcount' name , receiverpeople useraccount from ywrepairjobinfo
		where isreject=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  receiverpeople
		union all
		select  count(*)total,'acceptjobcount' name , jobacceptpeople useraccount  from ywrepairjobinfo
		where  (isreject=0 or isreject is null )
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobacceptpeople
		union all
		select  count(*)total, 'feedbackjobcount'name , feedbackpeople useraccount from ywrepairjobinfo
		where 1=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  feedbackpeople
		union all
		select  count(*)total, 'hangupjobcount'  , jobacceptpeople useraccount from ywrepairjobinfo where  (ishangup=1)
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  jobacceptpeople
		union all
		select  count(*) total, 'confirmjobcount' , confirmpeople useraccount  from ywrepairjobinfo
		where 1=1
		<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[	jobreporttime >= #timeStart#	 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[  jobreporttime <=#timeEnd#  ]]>
		</isNotEmpty>
		group by  confirmpeople
		)group by useraccount ,name
		) pivot (sum(total) for name in ('feedbackjobcount' feedbackjobcount,'hangupjobcount' hangupjobcount ,'confirmjobcount' confirmjobcount，'rejectjobcount' rejectjobcount ,
		'reportjobcount' reportjobcount,'acceptjobcount' acceptjobcount))t
	</select>
	
	
	 <!-- 柱状图统计 -->
	<select id="count_userrepairjobshowBar" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select areanum, areaname, sum(reportjobcount) as sbs, sum(rejectjobcount) as bhs, sum(acceptjobcount) as sls,
		sum(feedbackjobcount) as fks, sum(hangupjobcount) as gqs, sum(confirmjobcount) as qrfks from ywcountuserrepairjob
		<include refid="condition_Ywcountuserrepairjob"/>
		group by areanum, areaname
	</select>
	
	<!-- 首页统计 -->
	<select id="count_OrderHomepage" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select a.bh,b.wxwc,c.gq,d.wxz from (
			select count(*) as bh from ywrepairjobinfo where jobprocessstate = 903
		) a left join 
		(
			select count(*) as wxwc from ywrepairjobinfo where jobprocessstate = 911
		) b on 1=1 left join
		(
			select count(*) as gq from ywrepairjobinfo where jobprocessstate = 904
		) c on 1=1 left join 
		(
			select count(*) as wxz from ywrepairjobinfo where jobprocessstate in (900,901,902,905,906,907,908,909,910,913)
		) d on 1=1
	</select>
</sqlMap>











