package cn.hmsoft.skill.data.dao.school;

import org.springframework.stereotype.Repository;
import cn.hmsoft.helper.StringHelper;
import cn.hmsoft.jdbc.core.PlatformDaoSupport;
import cn.hmsoft.jdbc.entity.Pager;
import cn.hmsoft.jdbc.entity.QueryOrder;
import cn.hmsoft.skill.data.model.school.AreaRecruit;

@Repository
public class AreaRecruitDao extends PlatformDaoSupport<AreaRecruit> {

	/***********************
	 * 分页查询（分片区生源建设人数统计表）
	 */
	public Pager pageAreaRecruit(String year,String area,Integer school_id, Integer start, Integer limit, String query,
			QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder("select ");
		sql.append(" B1.YEAR AS YEAR,  "); 
		sql.append(" B1.school_id AS SCHOOL_ID,  "); 
		sql.append(" recr.area as area,"); 
		sql.append(" recr.recruit_num as recruit_num, "); 
		sql.append(" B1.admission_routine_num as admission_routine_num,   "); 
		sql.append(" B2.admission_separate_num AS admission_separate_num, "); 
		sql.append(" B3.reported_routine_num AS reported_routine_num,"); 
		sql.append(" B4.reported_separate_num AS reported_separate_num    "); 
		sql.append(" FROM    "); 
		sql.append(" jt_area_recruit recr   "); 
		sql.append(" LEFT JOIN    "); 
		sql.append(" (  "); 
		sql.append(" select A1.area_name as area,"); 
		sql.append(" count(1) AS admission_routine_num,    "); 
		sql.append(" st.school_id AS school_id,  "); 
		sql.append(" substring(st.notification_number,1,4) AS YEAR   "); 
		sql.append(" from std_admission st LEFT JOIN  "); 
		sql.append(" (  "); 
		sql.append(" select are.area_name as area_name,    "); 
		sql.append(" city.city_name as city_name "); 
		sql.append("  from frame_region_city city,frame_region_area are   "); 
		sql.append(" where city.PROVINCE_ID = '42' and city.AREA_ID = are.AREA_ID   "); 
		sql.append(" ) A1    "); 
		sql.append(" on A1.city_name = st.stu_area_residece"); 
		sql.append(" where st.region = '湖北'  AND st.families_type != '单独招生'   "); 
		sql.append(" group by area,school_id,YEAR"); 
		sql.append(" UNION   "); 
		sql.append(" select '外省' as area, "); 
		sql.append(" count(1) AS admission_routine_num,    "); 
		sql.append(" st.school_id AS school_id,  "); 
		sql.append(" substring(st.notification_number,1,4) AS YEAR   "); 
		sql.append(" from std_admission st  "); 
		sql.append(" where st.region != '湖北'  AND st.families_type != '单独招生'  "); 
		sql.append(" GROUP BY school_id,YEAR"); 
		sql.append(" ) B1    "); 
		sql.append(" ON B1.area = recr.area "); 
		sql.append(" LEFT JOIN    "); 
		sql.append(" (  "); 
		sql.append(" select A1.area_name as area,"); 
		sql.append(" count(1) AS admission_separate_num    "); 
		sql.append(" from std_admission st LEFT JOIN  "); 
		sql.append(" (  "); 
		sql.append(" select are.area_name as area_name,    "); 
		sql.append(" city.city_name as city_name "); 
		sql.append("  from frame_region_city city,frame_region_area are   "); 
		sql.append(" where city.PROVINCE_ID = '42' and city.AREA_ID = are.AREA_ID   "); 
		sql.append(" ) A1    "); 
		sql.append(" on A1.city_name = st.stu_area_residece"); 
		sql.append(" where st.region = '湖北'  AND st.families_type = '单独招生'    "); 
		sql.append(" group by area"); 
		sql.append(" UNION   "); 
		sql.append(" select '外省' as area, "); 
		sql.append(" count(1) AS admission_separate_num    "); 
		sql.append(" from std_admission st  "); 
		sql.append(" where st.region != '湖北'  AND st.families_type = '单独招生'   "); 
		sql.append(" ) B2    "); 
		sql.append(" on  B2.AREA= recr.area "); 
		sql.append(" LEFT JOIN    "); 
		sql.append(" (  "); 
		sql.append(" select A1.area_name as area,"); 
		sql.append(" count(1) AS reported_routine_num,"); 
		sql.append(" st.school_id AS school_id,  "); 
		sql.append(" substring(st.notification_number,1,4) AS YEAR   "); 
		sql.append(" from std_admission st LEFT JOIN  "); 
		sql.append(" (  "); 
		sql.append(" select are.area_name as area_name,    "); 
		sql.append(" city.city_name as city_name "); 
		sql.append("  from frame_region_city city,frame_region_area are   "); 
		sql.append(" where city.PROVINCE_ID = '42' and city.AREA_ID = are.AREA_ID   "); 
		sql.append(" ) A1    "); 
		sql.append(" on A1.city_name = st.stu_area_residece"); 
		sql.append(" where st.region = '湖北'  AND st.families_type != '单独招生'   "); 
		sql.append(" AND st.isReported = '是'    "); 
		sql.append(" group by area,school_id,YEAR"); 
		sql.append(" UNION   "); 
		sql.append(" select '外省' as area, "); 
		sql.append(" count(1) AS reported_routine_num,"); 
		sql.append(" st.school_id AS school_id,  "); 
		sql.append(" substring(st.notification_number,1,4) AS YEAR   "); 
		sql.append(" from std_admission st  "); 
		sql.append(" where st.region != '湖北'  AND st.families_type != '单独招生'  "); 
		sql.append(" AND st.isReported = '是'    "); 
		sql.append(" GROUP BY school_id,YEAR"); 
		sql.append(" ) B3    "); 
		sql.append(" ON B3.area = recr.area "); 
		sql.append(" LEFT JOIN    "); 
		sql.append(" (  "); 
		sql.append(" select A1.area_name as area,"); 
		sql.append(" count(1) AS reported_separate_num"); 
		sql.append(" from std_admission st LEFT JOIN  "); 
		sql.append(" (  "); 
		sql.append(" select are.area_name as area_name,    "); 
		sql.append(" city.city_name as city_name "); 
		sql.append("  from frame_region_city city,frame_region_area are   "); 
		sql.append(" where city.PROVINCE_ID = '42' and city.AREA_ID = are.AREA_ID   "); 
		sql.append(" ) A1    "); 
		sql.append(" on A1.city_name = st.stu_area_residece"); 
		sql.append(" where st.region = '湖北'  AND st.families_type = '单独招生'    "); 
		sql.append(" AND st.isReported = '是'    "); 
		sql.append(" group by area"); 
		sql.append(" UNION   "); 
		sql.append(" select '外省' as area, "); 
		sql.append(" count(1) AS reported_separate_num"); 
		sql.append(" from std_admission st  "); 
		sql.append(" where st.region != '湖北'  AND st.families_type = '单独招生'   "); 
		sql.append(" AND st.isReported = '是'    "); 
		sql.append(" ) B4    "); 
		sql.append(" on  B4.AREA= recr.area ");
		sql.append(" where 1=1 and  B1.school_id='").append(school_id).append("'");
		if (year != null && !"".equals(year)) {
			sql.append(" and B1.year='").append(year).append("'");
		}
		if (area != null && !"".equals(area)) {
			sql.append(" and recr.area='").append(area).append("'");
		}
		if (StringHelper.isEmpty(query)) {
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}else {
			sql.append(" and (B1.year=? or recr.area=?)");
		}
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery, vQuery);
	}
	
	/**
	 * 查看对应年份数据是否导入数据库
	 * @param year
	 * @return
	 */
	public AreaRecruit findByYear(String year) {
		return this.findBySql("select * from jt_area_recruit where year=?", year);
	}
	
	/***********************
	 * 分页查询（  湖北省近三年分地区招生情况统计表）
	 */
	public Pager pageThreeYear(Integer school_id, Integer start, Integer limit, String query,
			QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder("select ");
		sql.append("  A1.school_id as school_id, B.area_name as area,      ");  
		sql.append("   sum(A1.year1_admission_num) as year1_admission_num,");  
		sql.append("   SUM(A2.year1_reported_num) AS year1_reported_num,  ");  
		sql.append("   sum(A3.year2_admission_num) as year2_admission_num,");  
		sql.append("   SUM(A4.year2_reported_num) AS year2_reported_num,  ");  
		sql.append("   sum(A5.year3_admission_num) as year3_admission_num,");  
		sql.append("   SUM(A6.year3_reported_num) AS year3_reported_num   ");  
		sql.append("   FROM "); 
		sql.append("   (  ");  
		sql.append("   select st.stu_area_residece as city_name,  ");  
		sql.append("   COUNT(1) as year1_admission_num ,  ");  
		sql.append("    substring(st.notification_number,1,4) AS YEAR,     "); 
		sql.append("   st.school_id as school_id");
		sql.append("   from std_admission st where st.region = '湖北'     ");  
		sql.append("    AND substring(st.notification_number,1,4) = YEAR(NOW())-3 ");  
		sql.append("   GROUP BY st.stu_area_residece,YEAR ,school_id");  
		sql.append("   ) A1 "); 
		sql.append("   LEFT JOIN  ");  
		sql.append("   (  ");  
		sql.append("   select st.stu_area_residece as city_name,  ");  
		sql.append("   COUNT(1) as year1_reported_num ,   ");  
		sql.append("    substring(st.notification_number,1,4) AS YEAR     ");  
		sql.append("   from std_admission st where st.region = '湖北' AND st.isReported = '是'    ");  
		sql.append("    AND substring(st.notification_number,1,4) = YEAR(NOW())-3 ");  
		sql.append("   GROUP BY st.stu_area_residece ,YEAR ");  
		sql.append("   ) A2 "); 
		sql.append("   on A2.city_name = A1.city_name     ");  
		sql.append("   LEFT JOIN  ");  
		sql.append("   (  ");  
		sql.append("   select are.area_name as area_name, ");  
		sql.append("   		 city.city_name as city_name    ");  
		sql.append("   		 from frame_region_city city,frame_region_area are      ");  
		sql.append("   		 where city.PROVINCE_ID = '42' and city.AREA_ID = are.AREA_ID   ");  
		sql.append("   ) B");  
		sql.append("   on B.city_name = A1.city_name      ");  
		sql.append("   LEFT JOIN  ");  
		sql.append("   (  ");  
		sql.append("   select st.stu_area_residece as city_name,  ");  
		sql.append("   COUNT(1) as year2_admission_num ,  ");  
		sql.append("    substring(st.notification_number,1,4) AS YEAR     ");  
		sql.append("   from std_admission st where st.region = '湖北'     ");  
		sql.append("    AND substring(st.notification_number,1,4) = YEAR(NOW()) -4");  
		sql.append("   GROUP BY st.stu_area_residece,YEAR ");  
		sql.append("   ) A3 "); 
		sql.append("   ON A3.city_name = A1.city_name     ");  
		sql.append("   LEFT JOIN  ");  
		sql.append("   (  ");  
		sql.append("   select st.stu_area_residece as city_name,  ");  
		sql.append("   COUNT(1) as year2_reported_num ,   ");  
		sql.append("    substring(st.notification_number,1,4) AS YEAR     ");  
		sql.append("   from std_admission st where st.region = '湖北' AND st.isReported = '是'    ");  
		sql.append("    AND substring(st.notification_number,1,4) = YEAR(NOW()) -4");  
		sql.append("   GROUP BY st.stu_area_residece ,YEAR");  
		sql.append("   ) A4  "); 
		sql.append("   on A4.city_name = A1.city_name     ");  
		sql.append("   LEFT JOIN  ");  
		sql.append("   (  ");  
		sql.append("   select st.stu_area_residece as city_name,  ");  
		sql.append("   COUNT(1) as year3_admission_num ,  ");  
		sql.append("    substring(st.notification_number,1,4) AS YEAR     ");  
		sql.append("   from std_admission st where st.region = '湖北'     ");  
		sql.append("     AND substring(st.notification_number,1,4) = YEAR(NOW())-5");  
		sql.append("   GROUP BY st.stu_area_residece,YEAR ");  
		sql.append("   ) A5 "); 
		sql.append("   ON A5.city_name = A1.city_name     ");  
		sql.append("   LEFT JOIN  ");  
		sql.append("   (  ");  
		sql.append("   select st.stu_area_residece as city_name,  ");  
		sql.append("   COUNT(1) as year3_reported_num ,   ");  
		sql.append("    substring(st.notification_number,1,4) AS YEAR     ");  
		sql.append("   from std_admission st where st.region = '湖北' AND st.isReported = '是'    ");  
		sql.append("   AND substring(st.notification_number,1,4) = YEAR(NOW())-5  ");  
		sql.append("   GROUP BY st.stu_area_residece ,YEAR");  
		sql.append("   ) A6  "); 
		sql.append("   on A6.city_name = A1.city_name     ");  
		sql.append(" where 1=1 and  A1.school_id='").append(school_id).append("'");
		if (StringHelper.isEmpty(query)) {
			sql.append("    group by area  ,school_id    "); 
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}else {
			//sql.append(" and (A1.year=?)");
			sql.append("    group by area ,school_id     "); 
		}
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery);
	}
}
