top sql

<!-- *********************************jglist1 全部********************************************* -->
	
	<select id="searchSystemApplyListByOrg" parameterType="com.tanovo.model.SystemApply" resultMap="mapSystemApply">
		select a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
		from dj_system_apply a, dj_system_level l ,zk_test_org o 
		where a.sid=l.system_sid and a.org_sid=o.sid and a.`status`="通过"
		<if test="orgName != null and orgName != '' ">and o.name LIKE "%${orgName}%"</if>
		order by create_date
	</select>
	<select id="searchCountSystemApplyListByOrg" parameterType="com.tanovo.model.SystemApply" resultType="java.lang.Integer">
		select count(*)  
		from dj_system_apply a, dj_system_level l ,zk_test_org o 
		where a.sid=l.system_sid and a.org_sid=o.sid and a.`status`="通过"
		<if test="orgName != null and orgName != '' ">and o.name LIKE "%${orgName}%"</if> 
	</select>
	
	<!-- ********************************* jglist1 正常备案********************************************* -->
	
	<select id="searchSystemApplyListInDate" parameterType="com.tanovo.model.SystemApply" resultMap="mapSystemApply">
		
		SELECT w.* from 
			(select  ss.sid ,MAX(pp.report_date)  as report_date from  zk_system ss LEFT JOIN zk_project pp ON ss.sid=pp.system_sid  GROUP BY ss.sid) u   ,
 			(select s.sid as ssid, p.sag_level,p.report_date,a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
			from dj_system_apply a, dj_system_level l ,zk_test_org o ,zk_system s LEFT JOIN zk_project p ON s.sid=p.system_sid
			where a.sid=l.system_sid and a.org_sid=o.sid and a.record_no=s.record_no) w 
		WHERE u.sid=w.ssid AND u.report_date=w.report_date AND
			((w.sag_level="d02" AND YEAR(u.report_date)=YEAR(NOW()))OR 
			(w.sag_level="d03" AND u.report_date between date_sub(now(),interval 2 year) and now())) 
			<if test="orgName != null and orgName != '' ">AND w.orgName LIKE "%${orgName}%"</if>
			and w.`status`="通过"
		order by w.create_date DESC
	</select>
	<select id="searchCountSystemApplyListInDate" parameterType="com.tanovo.model.SystemApply" resultType="java.lang.Integer">
		SELECT count(*) from 
			(select  ss.sid ,MAX(pp.report_date)  as report_date from  zk_system ss LEFT JOIN zk_project pp ON ss.sid=pp.system_sid  GROUP BY ss.sid) u   ,
 			(select s.sid as ssid, p.sag_level,p.report_date,a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
			from dj_system_apply a, dj_system_level l ,zk_test_org o ,zk_system s LEFT JOIN zk_project p ON s.sid=p.system_sid
			where a.sid=l.system_sid and a.org_sid=o.sid and a.record_no=s.record_no) w 
		WHERE u.sid=w.ssid AND u.report_date=w.report_date AND
			((w.sag_level="d02" AND YEAR(u.report_date)=YEAR(NOW()))OR 
			(w.sag_level="d03" AND u.report_date between date_sub(now(),interval 2 year) and now())) 
			<if test="orgName != null and orgName != '' ">AND w.orgName LIKE "%${orgName}%"</if>
			and w.`status`="通过"
	</select>
	
	<!-- *********************************jglist1 备案已过期********************************************* -->
	
	<select id="searchSystemApplyListOverDate" parameterType="com.tanovo.model.SystemApply" resultMap="mapSystemApply">
		
		SELECT w.* from 
			(select  ss.sid ,MAX(pp.report_date)  as report_date from  zk_system ss LEFT JOIN zk_project pp ON ss.sid=pp.system_sid  GROUP BY ss.sid) u   ,
 			(select s.sid as ssid, p.sag_level,p.report_date,a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
			from dj_system_apply a, dj_system_level l ,zk_test_org o ,zk_system s LEFT JOIN zk_project p ON s.sid=p.system_sid
			where a.sid=l.system_sid and a.org_sid=o.sid and a.record_no=s.record_no) w 
		WHERE u.sid=w.ssid AND u.report_date=w.report_date AND
			((w.sag_level="d02" AND DATE_SUB(CURDATE(), INTERVAL 1 year) > date(u.report_date))OR 
			(w.sag_level="d03" AND DATE_SUB(CURDATE(), INTERVAL 2 year) > date(u.report_date)) ) 
			<if test="orgName != null and orgName != '' ">AND w.orgName LIKE "%${orgName}%"</if>
			and w.`status`="通过"
		order by w.create_date DESC
	</select>
	<select id="searchCountSystemApplyListOverDate" parameterType="com.tanovo.model.SystemApply" resultType="java.lang.Integer">
		SELECT count(*) from 
			(select  ss.sid ,MAX(pp.report_date)  as report_date from  zk_system ss LEFT JOIN zk_project pp ON ss.sid=pp.system_sid  GROUP BY ss.sid) u   ,
 			(select s.sid as ssid, p.sag_level,p.report_date,a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
			from dj_system_apply a, dj_system_level l ,zk_test_org o ,zk_system s LEFT JOIN zk_project p ON s.sid=p.system_sid
			where a.sid=l.system_sid and a.org_sid=o.sid and a.record_no=s.record_no) w 
		WHERE u.sid=w.ssid AND u.report_date=w.report_date AND
			((w.sag_level="d02" AND DATE_SUB(CURDATE(), INTERVAL 1 year) > date(u.report_date))OR 
			(w.sag_level="d03" AND DATE_SUB(CURDATE(), INTERVAL 2 year) > date(u.report_date)) ) 
			<if test="orgName != null and orgName != '' ">AND w.orgName LIKE "%${orgName}%"</if>
			and w.`status`="通过"
	</select>
	<!-- *********************************jglist1 已定级未备案********************************************* -->
	<select id="searchSystemApplyListNoRecord" parameterType="com.tanovo.model.SystemApply" resultMap="mapSystemApply">
		
		select a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
		from dj_system_apply a, dj_system_level l ,zk_test_org o 
		where a.sid=l.system_sid and a.org_sid=o.sid and( a.record_no='' or a.record_no is NULL or 0=(select count(*) from zk_system s where  a.record_no=s.record_no))
			<if test="orgName != null and orgName != '' ">AND o.name LIKE "%${orgName}%"</if>
			and a.`status`="通过"
		order by a.create_date DESC
	</select>
	<select id="searchCountSystemApplyListNoRecord" parameterType="com.tanovo.model.SystemApply" resultType="java.lang.Integer">
		SELECT count(*) from dj_system_apply a, dj_system_level l ,zk_test_org o 
		where a.sid=l.system_sid and a.org_sid=o.sid and( a.record_no='' or a.record_no is NULL or 0=(select count(*) from zk_system s where  a.record_no=s.record_no))
		and a.`status`="通过"
			<if test="orgName != null and orgName != '' ">AND o.name LIKE "%${orgName}%"</if>
	</select>
	<!-- *********************************jglist2 取每月申请数********************************************* -->
	<resultMap type="com.tanovo.model.SystemCount" id="mapSystemCount">
		<result property="year" column="year" />
		<result property="month" column="month" />
		<result property="num" column="num" />
		<result property="dateKey" column="date_key" />
	</resultMap>
	<select id="searchCountSystemEveryMonth" resultMap="mapSystemCount">
		select DATE_FORMAT(create_date,'%Y-%m') AS date_key,count(*) as num from dj_system_apply where `status`="通过" 
		GROUP BY DATE_FORMAT(create_date,'%Y-%m') ORDER BY  DATE_FORMAT(create_date,'%Y-%m') DESC  LIMIT 6
	</select>
	<!-- *********************************jglist2 取每年申请数********************************************* -->
	<select id="searchCountSystemEveryYear"  resultMap="mapSystemCount">
		select year(create_date) year,count(*) as num from dj_system_apply where `status`="通过"  GROUP BY year(create_date) ORDER BY year(create_date) desc 
	</select>
	<!-- ********************************* jglist2 正常备案********************************************* -->
	
	<select id="searchCountSystemInDateByCity" parameterType="java.lang.String"  resultType="java.lang.Integer">
		SELECT count(*) from 
			(select  ss.sid ,MAX(pp.report_date)  as report_date from  zk_system ss LEFT JOIN zk_project pp ON ss.sid=pp.system_sid  GROUP BY ss.sid) u   ,
 			(select s.sid as ssid, p.sag_level,p.report_date,a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
			from dj_system_apply a, dj_system_level l ,zk_test_org o ,zk_system s LEFT JOIN zk_project p ON s.sid=p.system_sid
			where a.sid=l.system_sid and a.org_sid=o.sid and a.record_no=s.record_no and o.city =#{city}) w 
		WHERE u.sid=w.ssid AND u.report_date=w.report_date AND
			((w.sag_level="d02" AND YEAR(u.report_date)=YEAR(NOW()))OR 
			(w.sag_level="d03" AND u.report_date between date_sub(now(),interval 2 year) and now())) 
			and w.`status`="通过"
	</select>
	
	<!-- *********************************jglist1 备案已过期********************************************* -->
	
	<select id="searchCountSystemOverDateByCity" parameterType="java.lang.String" resultType="java.lang.Integer">
		SELECT count(*) from 
			(select  ss.sid ,MAX(pp.report_date)  as report_date from  zk_system ss LEFT JOIN zk_project pp ON ss.sid=pp.system_sid  GROUP BY ss.sid) u   ,
 			(select s.sid as ssid, p.sag_level,p.report_date,a.sid,o.name as orgName,a.name, IFNULL(a.`status`,"") as status ,l.g as level ,o.linkman,o.phone,a.create_date,a.record_no  
			from dj_system_apply a, dj_system_level l ,zk_test_org o ,zk_system s LEFT JOIN zk_project p ON s.sid=p.system_sid
			where a.sid=l.system_sid and a.org_sid=o.sid and a.record_no=s.record_no and o.city =#{city}) w 
		WHERE u.sid=w.ssid AND u.report_date=w.report_date AND
			((w.sag_level="d02" AND DATE_SUB(CURDATE(), INTERVAL 1 year) > date(u.report_date))OR 
			(w.sag_level="d03" AND DATE_SUB(CURDATE(), INTERVAL 2 year) > date(u.report_date)) ) 
			and w.`status`="通过"
	</select>
	<!-- *********************************jglist1 已定级未备案********************************************* -->
	<select id="searchCountSystemNoRecordByCity" parameterType="java.lang.String" resultType="java.lang.Integer">
		SELECT count(*) from dj_system_apply a, dj_system_level l ,zk_test_org o 
		where a.sid=l.system_sid and a.org_sid=o.sid and( a.record_no='' or a.record_no is NULL or 0=(select count(*) from zk_system s where  a.record_no=s.record_no))
		and a.`status`="通过" and o.city =#{city}
	</select>