<?xml version="1.0" encoding="gbk" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="" >
	<!--  
	<select id="query_Yworderinfocount" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select a.xzbh, 
		trim(cast(case when a.gdzs is null then 0 else a.gdzs end as char(8))) as gdzs, 
		trim(cast(case when b.bhgd is null then 0 else b.bhgd end as char(8))) as bhgd,
		trim(cast(case when c.gqgd is null then 0 else c.gqgd end as char(8))) as gqgd, 
		trim(cast(case when d.wwcgd is null then 0 else d.wwcgd end as char(8))) as wwcgd,
		trim(cast(case when e.ywcgd is null then 0 else e.ywcgd end as char(8))) as ywcgd, 
		trim(cast(case when f.jswcgd is null then 0 else f.jswcgd end as char(8))) as jswcgd,
		g.bmmc from(
		select count(*) as gdzs, jobacceptcompany as xzbh from ywrepairjobinfo 
		where 1=1
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>				
		group by jobacceptcompany
		) a left join 
		(
		select count(*) as bhgd, jobacceptcompany as xzbh from ywrepairjobinfo where isreject=1
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		 group by jobacceptcompany
		) b on a.xzbh = b.xzbh left join 
		(
		select count(*) as gqgd, jobacceptcompany as xzbh from ywrepairjobinfo where ishangup=1 
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) c on a.xzbh = c.xzbh left join
		(
		select count(*) as wwcgd, jobacceptcompany as xzbh from ywrepairjobinfo 
		where jobprocessstate not in (911,912) 
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) d on a.xzbh = d.xzbh left join 
		(
		select count(*) as ywcgd, jobacceptcompany as xzbh from ywrepairjobinfo 
		where jobprocessstate in (911,912) 
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) e on a.xzbh = e.xzbh left join
		(
		select count(*) as jswcgd, jobacceptcompany as xzbh from ywrepairjobinfo 
		<![CDATA[
		where jobprocessstate in (911,912) and timestampdiff(hour,jobreporttime,confirmtime)<24 
		]]>
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) f on a.xzbh = f.xzbh left join
		(
			select bmbh,bmmc from ptyhbm 
		) g on a.xzbh = g.bmbh
	</select>
	 -->
	 <!-- 
	 工单效率统计                       start
	<select id="query_orderefficient" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select a.xzbh, 
		trim(cast(case when a.zs is null then 0 else a.zs end  as char(8))) as zs, 
		trim(cast(case when b.ytyn is null then 0 else b.ytyn end  as char(8))) as ytyn,
		trim(cast(case when c.ytys is null then 0 else c.ytys end  as char(8))) as ytys, 
		trim(cast(case when d.stysis null then 0 else d.stysis end  as char(8))) as stys,
		e.bmmc from(
		select count(*) as zs,jobacceptcompany AS xzbh from ywrepairjobinfo 
		where 1=1
		<isNotEmpty prepend="and" property="xzbh">
		jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) a left join
		(
		select count(*) as ytyn,jobacceptcompany AS xzbh from ywrepairjobinfo 
		<![CDATA[ 
		where timestampdiff( DAY, jobreporttime, confirmtime ) < 1 
		]]>
		<isNotEmpty prepend="and" property="xzbh">
		jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) b on a.xzbh = b.xzbh left join 
		(
		select count(*) as ytys,jobacceptcompany AS xzbh from ywrepairjobinfo 
		<![CDATA[ 
		where timestampdiff( DAY, jobreporttime, confirmtime ) < 3 and timestampdiff( DAY, jobreporttime, confirmtime ) > 1
		]]>
		<isNotEmpty prepend="and" property="xzbh">
		jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) c on a.xzbh = c.xzbh left join 
		(
		select count(*) as stys,jobacceptcompany AS xzbh from ywrepairjobinfo 
		where timestampdiff( DAY, jobreporttime, confirmtime ) > 3
		<isNotEmpty prepend="and" property="xzbh">
		jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) d on a.xzbh = d.xzbh left join 
		(
			select bmbh, bmmc FROM ptyhbm
		) e on a.xzbh = e.bmbh
	</select>
	 -->
	<!-- 工单情况统计 -->
	<select id="query_Yworderinfocount" resultClass="java.util.HashMap" parameterClass="java.util.Map" remapResults="true">
		select a.xzbh, 
		trim(cast(case when a.gdzs is null then 0 else a.gdzs end as char(8))) as gdzs, 
		trim(cast(case when b.bhgd is null then 0 else b.bhgd end as char(8))) as bhgd,
		trim(cast(case when c.gqgd is null then 0 else c.gqgd end as char(8))) as gqgd, 
		trim(cast(case when d.wwcgd is null then 0 else d.wwcgd end as char(8))) as wwcgd,
		trim(cast(case when e.ywcgd is null then 0 else e.ywcgd end as char(8))) as ywcgd, 
		g.bmmc from(
		select count(*) as gdzs, jobacceptcompany as xzbh from ywrepairjobinfo 
		where 1=1
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>				
		group by jobacceptcompany
		) a left join 
		(
		select count(*) as bhgd, jobacceptcompany as xzbh from ywrepairjobinfo where isreject=1
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		 group by jobacceptcompany
		) b on a.xzbh = b.xzbh left join 
		(
		select count(*) as gqgd, jobacceptcompany as xzbh from ywrepairjobinfo where ishangup=1 
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) c on a.xzbh = c.xzbh left join
		(
		select count(*) as wwcgd, jobacceptcompany as xzbh from ywrepairjobinfo 
		where jobprocessstate not in (911,912) 
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) d on a.xzbh = d.xzbh left join 
		(
		select count(*) as ywcgd, jobacceptcompany as xzbh from ywrepairjobinfo 
		where jobprocessstate in (911,912) 
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) e on a.xzbh = e.xzbh left join
		(
			select bmbh,bmmc from ptyhbm 
		) g on a.xzbh = g.bmbh
		<!-- <isNotEmpty prepend=' ' property='start'>
			limit $start$ ,$limit$
		</isNotEmpty> -->
	</select>
	
	<select id="query_jswcgdOrders" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select jobacceptcompany as xzbh,confirmtime as endtime,jobreporttime as starttime  
		from ywrepairjobinfo where jobprocessstate in (911,912) and confirmtime is not null and jobreporttime is not null
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
	</select>
	
	
	<!-- 工单情况统计    上面用的  left join 所以分页可以这么写-->
	<select id="count_Yworderinfocount" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from(
			select count(*) as gdzs, jobacceptcompany as xzbh from ywrepairjobinfo where 1=1
			<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
			</isNotEmpty>
			<isNotEmpty prepend="and" property="timeStart">
			<![CDATA[ 
				jobreporttime >= #timeStart#
			 ]]>
			</isNotEmpty>
			<isNotEmpty prepend="and" property="timeEnd">
			<![CDATA[ 
				jobreporttime <= #timeEnd#
			 ]]>
			</isNotEmpty>
			group by jobacceptcompany
		) x
	</select>
	
	
	
	<!-- 工单效率统计                       start-->
	<select id="query_orderefficient" resultClass="java.util.HashMap" parameterClass="java.util.Map" remapResults="true">
		select a.xzbh, 
		trim(cast(case when a.zs is null then 0 else a.zs end  as char(8))) as zs, 
		e.bmmc from(
		select count(*) as zs,jobacceptcompany AS xzbh from ywrepairjobinfo 
		where 1=1
		<isNotEmpty prepend="and" property="xzbh">
		jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) a left join
		(
			select bmbh, bmmc FROM ptyhbm
		) e on a.xzbh = e.bmbh
		<!-- <isNotEmpty prepend=' ' property='start'>
			limit $start$ ,$limit$
		</isNotEmpty> -->
	</select>
	
	<select id="query_effList" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select jobacceptcompany as xzbh,confirmtime as endtime,jobreporttime as starttime  
		from ywrepairjobinfo where confirmtime is not null and jobreporttime is not null
		<isNotEmpty prepend="and" property="xzbh">
			jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
	
	</select>
	
	<select id="count_orderefficient" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select count(*) from(
		select count(*) as zs,jobacceptcompany AS xzbh from ywrepairjobinfo 
		where 1=1
		<isNotEmpty prepend="and" property="xzbh">
		jobacceptcompany = #xzbh#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeStart">
		<![CDATA[ 
			jobreporttime >= #timeStart#
		 ]]>
		</isNotEmpty>
		<isNotEmpty prepend="and" property="timeEnd">
		<![CDATA[ 
			jobreporttime <= #timeEnd#
		 ]]>
		</isNotEmpty>
		group by jobacceptcompany
		) x
	</select>
	<!-- 工单效率统计                       end-->
</sqlMap>











