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.ProvinceBatch;


@Repository
public class ProvinceBatchDao extends PlatformDaoSupport<ProvinceBatch> {

	/***********************
	 * 分页查询（分省分批次录取报到情况统计）
	 */
	public Pager pageProvinceBatch(String year,String province,String batch,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(" B1.province AS province ,  "); 
				sql.append(" B1.BATCH AS BATCH,  "); 
				sql.append(" IFNULL(B1.PLAN_NUM,0) AS PLAN_NUM, "); 
				sql.append(" IFNULL(B1.admission_num,0) AS ADMISSION_NUM, "); 
				sql.append(" IFNULL(B2.ADMISSION_MALE_NUM,0) as admission_male_num, "); 
				sql.append(" IFNULL(B2.ADMISSION_FEMALE_NUM,0) as admission_female_num, "); 
				sql.append(" IFNULL(B3.reported_num,0) as reported_num, "); 
				sql.append(" IFNULL(B4.report_male_num,0) as report_male_num, "); 
				sql.append(" IFNULL(b4.report_female_num,0) as report_female_num, "); 
				sql.append(" IFNULL(concat(cast(coalesce(round(B3.reported_num/B1.admission_num,2),0)*100 as char),'%'),0) as registration_rate "); 
				sql.append(" FROM "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" jt.year as year, "); 
				sql.append(" st.school_id as school_id, "); 
				sql.append(" jt.province, "); 
				sql.append(" st.batch, "); 
				sql.append(" IFNULL(count(jt.plan_num),0) as plan_num,  "); 
				sql.append(" IFNULL(count(st.region),0) as admission_num  "); 
				sql.append(" from std_admission st  "); 
				sql.append(" LEFT JOIN jt_province_batch jt "); 
				sql.append(" on st.batch = jt.batch and st.region = jt.province   "); 
				sql.append(" group by  jt.province,st.batch ,jt.`year`,st.school_id  "); 
				sql.append(" ) B1 "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" A1.region AS REGION,A1.batch AS BATCH,IFNULL(A1.admission_male_num,0) AS ADMISSION_MALE_NUM,IFNULL(A2.admission_female_num,0) AS ADMISSION_FEMALE_NUM "); 
				sql.append(" from  "); 
				sql.append(" (select  "); 
				sql.append(" st1.region as region, "); 
				sql.append(" st1.batch as batch,   "); 
				sql.append(" IFNULL(count(st1.sex),0) as admission_male_num "); 
				sql.append(" from std_admission st1  "); 
				sql.append(" where st1.sex = '男' "); 
				sql.append(" group by st1.region,st1.batch ,st1.sex  "); 
				sql.append(" ) A1 "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" (select  "); 
				sql.append(" st2.region as region, "); 
				sql.append(" st2.batch as batch,   "); 
				sql.append(" count(st2.sex) as admission_female_num "); 
				sql.append(" from std_admission st2  "); 
				sql.append(" where st2.sex = '女' "); 
				sql.append(" group by st2.region,st2.batch ,st2.sex  "); 
				sql.append(" ) A2 ON A1.BATCH = A2.BATCH AND A1.REGION = A2.REGION "); 
				sql.append(" ) B2 "); 
				sql.append(" ON  B2.BATCH = B1.BATCH AND B2.REGION = B1.province "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" st5.region as region, "); 
				sql.append(" st5.batch as batch,   "); 
				sql.append(" count(st5.region) as reported_num "); 
				sql.append(" from std_admission st5  "); 
				sql.append(" where  st5.isreported = '是' "); 
				sql.append(" group by st5.region,st5.batch  "); 
				sql.append(" ) B3  "); 
				sql.append(" ON B3.BATCH = B1.BATCH AND B3.REGION = B1.province "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" A3.region AS REGION,A3.batch AS BATCH,IFNULL(A3.report_male_num,0) AS report_male_num,IFNULL(A4.report_female_num,0) AS report_female_num "); 
				sql.append(" from  "); 
				sql.append(" (select  "); 
				sql.append(" st3.region as region, "); 
				sql.append(" st3.batch as batch,   "); 
				sql.append(" count(st3.sex) as report_male_num "); 
				sql.append(" from std_admission st3  "); 
				sql.append(" where st3.sex = '男' AND st3.isreported = '是' "); 
				sql.append(" group by st3.region,st3.batch ,st3.sex  "); 
				sql.append(" ) A3 "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" (select  "); 
				sql.append(" st4.region as region, "); 
				sql.append(" st4.batch as batch,   "); 
				sql.append(" count(st4.sex) as report_female_num "); 
				sql.append(" from std_admission st4  "); 
				sql.append(" where st4.sex = '女' AND st4.isreported = '是' "); 
				sql.append(" group by st4.region,st4.batch ,st4.sex  "); 
				sql.append(" ) A4 ON A3.BATCH = A4.BATCH AND A3.REGION = A4.REGION "); 
				sql.append(" ) B4  "); 
				sql.append(" ON B4.BATCH = B1.BATCH AND B4.REGION = B1.province "); 
				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 (province != null && !"".equals(province)) {
					sql.append(" and B1.province='").append(province).append("'");
				}
				if (batch != null && !"".equals(batch)) {
					sql.append(" and B1.batch='").append(batch).append("'");
				}
				if (StringHelper.isEmpty(query)) {
					return this.pageMapBySql(queryOrder, start, limit, sql.toString());
				}else {
					sql.append(" and (B1.year like ? or B1.province like ? or B1.batch like ? )");
				}
		
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery, vQuery, vQuery);
	}
	
	/**
	 * 查看对应年份数据是否导入数据库
	 * @param year
	 * @return
	 */
	public ProvinceBatch findByYear(String year) {
		return this.findBySql("select * from jt_province_batch where year=?", year);
	}
	
	/***********************
	 * 分页查询（分省分批次录取统计表）
	 */
	public Pager provinceBatchSex(String year,String province,String batch,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(" B1.province AS province ,  "); 
				sql.append(" B1.BATCH AS BATCH,  "); 
				sql.append(" IFNULL(B1.PLAN_NUM,0) AS PLAN_NUM, "); 
				sql.append(" IFNULL(B1.admission_num,0) AS ADMISSION_NUM, "); 
				sql.append(" IFNULL(B2.ADMISSION_MALE_NUM,0) as admission_male_num, "); 
				sql.append(" IFNULL(B2.ADMISSION_FEMALE_NUM,0) as admission_female_num, "); 
				sql.append(" IFNULL(B4.main_campus_num,0) as main_campus_num, "); 
				sql.append(" IFNULL(b4.west_campus_num,0) as west_campus_num "); 
				sql.append(" FROM "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" jt.year as year, "); 
				sql.append(" st.school_id as school_id, "); 
				sql.append(" jt.province, "); 
				sql.append(" st.batch, "); 
				sql.append(" IFNULL(count(jt.plan_num),0) as plan_num,  "); 
				sql.append(" IFNULL(count(st.region),0) as admission_num  "); 
				sql.append(" from std_admission st  "); 
				sql.append(" LEFT JOIN jt_province_batch jt "); 
				sql.append(" on st.batch = jt.batch and st.region = jt.province   "); 
				sql.append(" group by  jt.province,st.batch ,jt.`year`,st.school_id  "); 
				sql.append(" ) B1 "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" A1.region AS REGION,A1.batch AS BATCH,IFNULL(A1.admission_male_num,0) AS ADMISSION_MALE_NUM,IFNULL(A2.admission_female_num,0) AS ADMISSION_FEMALE_NUM "); 
				sql.append(" from  "); 
				sql.append(" (select  "); 
				sql.append(" st1.region as region, "); 
				sql.append(" st1.batch as batch,   "); 
				sql.append(" IFNULL(count(st1.sex),0) as admission_male_num "); 
				sql.append(" from std_admission st1  "); 
				sql.append(" where st1.sex = '男' "); 
				sql.append(" group by st1.region,st1.batch ,st1.sex  "); 
				sql.append(" ) A1 "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" (select  "); 
				sql.append(" st2.region as region, "); 
				sql.append(" st2.batch as batch,   "); 
				sql.append(" count(st2.sex) as admission_female_num "); 
				sql.append(" from std_admission st2  "); 
				sql.append(" where st2.sex = '女' "); 
				sql.append(" group by st2.region,st2.batch ,st2.sex  "); 
				sql.append(" ) A2 ON A1.BATCH = A2.BATCH AND A1.REGION = A2.REGION "); 
				sql.append(" ) B2 "); 
				sql.append(" ON  B2.BATCH = B1.BATCH AND B2.REGION = B1.province "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" st5.region as region, "); 
				sql.append(" st5.batch as batch,   "); 
				sql.append(" count(st5.region) as reported_num "); 
				sql.append(" from std_admission st5  "); 
				sql.append(" where  st5.isreported = '是' "); 
				sql.append(" group by st5.region,st5.batch  "); 
				sql.append(" ) B3  "); 
				sql.append(" ON B3.BATCH = B1.BATCH AND B3.REGION = B1.province "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" ( "); 
				sql.append(" select  "); 
				sql.append(" A3.region AS REGION,A3.batch AS BATCH,IFNULL(A3.main_campus_num,0) AS main_campus_num,IFNULL(A4.west_campus_num,0) AS west_campus_num "); 
				sql.append(" from  "); 
				sql.append(" (select  "); 
				sql.append(" st3.region as region, "); 
				sql.append(" st3.batch as batch,   "); 
				sql.append(" count(st3.campus) as main_campus_num "); 
				sql.append(" from std_admission st3  "); 
				sql.append(" where st3.campus = '主校区'  "); 
				sql.append(" group by st3.region,st3.batch ,st3.campus  "); 
				sql.append(" ) A3 "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" (select  "); 
				sql.append(" st4.region as region, "); 
				sql.append(" st4.batch as batch,   "); 
				sql.append(" count(st4.campus) as west_campus_num "); 
				sql.append(" from std_admission st4  "); 
				sql.append(" where st4.campus = '西校区' "); 
				sql.append(" group by st4.region,st4.batch ,st4.campus  "); 
				sql.append(" ) A4 ON A3.BATCH = A4.BATCH AND A3.REGION = A4.REGION "); 
				sql.append(" ) B4  "); 
				sql.append(" ON B4.BATCH = B1.BATCH AND B4.REGION = B1.province "); 
				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 (province != null && !"".equals(province)) {
					sql.append(" and B1.province='").append(province).append("'");
				}
				if (batch != null && !"".equals(batch)) {
					sql.append(" and B1.batch='").append(batch).append("'");
				}
				if (StringHelper.isEmpty(query)) {
					return this.pageMapBySql(queryOrder, start, limit, sql.toString());
				}else {
					sql.append(" and (B1.year like ? or B1.province like ? or B1.batch like ? )");
				}
		
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery, vQuery, vQuery);
	}
	
	
	/***********************
	 * 分页查询（分省分批次分科类生源质量情况统计表）
	 */
	public Pager pageProvinceBatchQuality(String year,String province,String batch,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(" B1.province as province, "); 
		sql.append(" B1.batch as batch, "); 
		sql.append(" B1.families as families, "); 
		sql.append(" B1.admission_max_score as admission_max_score, "); 
		sql.append(" B1.admission_min_score as admission_min_score, "); 
		sql.append(" ROUND(B1.admission_average_score) as admission_average_score,"); 
		sql.append(" B1.reported_max_score as reported_max_score, "); 
		sql.append(" B1.reported_min_score as reported_min_score, "); 
		sql.append(" ROUND(B1.reported_average_score) as reported_average_score "); 
		sql.append(" FROM "); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" A1.year as year, "); 
		sql.append(" A1.school_id as school_id, "); 
		sql.append(" A1.province as province, "); 
		sql.append(" A1.batch as batch, "); 
		sql.append(" A1.families as families, "); 
		sql.append(" A1.admission_max_score as admission_max_score, "); 
		sql.append(" A1.admission_min_score as admission_min_score, "); 
		sql.append(" ROUND(A1.admission_average_score) as admission_average_score,"); 
		sql.append(" A2.reported_max_score as reported_max_score, "); 
		sql.append(" A2.reported_min_score as reported_min_score, "); 
		sql.append(" ROUND(A2.reported_average_score) as reported_average_score "); 
		sql.append(" FROM "); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" jt.year as year, "); 
		sql.append(" st.school_id as school_id, "); 
		sql.append(" st.region as province, "); 
		sql.append(" st.batch as batch, "); 
		sql.append(" st.families as families, "); 
		sql.append(" max(st.stu_score) as admission_max_score,"); 
		sql.append(" min(st.stu_score) as admission_min_score,"); 
		sql.append(" AVG(st.stu_score) as admission_average_score "); 
		sql.append(" from std_admission st"); 
		sql.append(" LEFT JOIN jt_province_batch jt "); 
		sql.append(" on st.batch = jt.batch and st.region = jt.province "); 
		sql.append(" where st.families ='文史类'"); 
		sql.append(" group by st.region,st.batch ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A1 "); 
		sql.append(" LEFT JOIN"); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" jt.year as year, "); 
		sql.append(" st.school_id as school_id, "); 
		sql.append(" st.region, "); 
		sql.append(" st.batch,"); 
		sql.append(" st.families, "); 
		sql.append(" max(st.stu_score) as reported_max_score, "); 
		sql.append(" min(st.stu_score) as reported_min_score, "); 
		sql.append(" AVG(st.stu_score) as reported_average_score"); 
		sql.append(" from std_admission st"); 
		sql.append(" LEFT JOIN jt_province_batch jt "); 
		sql.append(" on st.batch = jt.batch and st.region = jt.province "); 
		sql.append(" where st.families ='文史类'"); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.region,st.batch ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A2 "); 
		sql.append(" on A1.province = A2.region "); 
		sql.append(" and A1.batch = A2.batch"); 
		sql.append(" and A1.year = A2.year"); 
		sql.append(" UNION"); 
		sql.append(" select "); 
		sql.append(" A3.year as year, "); 
		sql.append(" A3.school_id as school_id, "); 
		sql.append(" A3.province as province, "); 
		sql.append(" A3.batch as batch, "); 
		sql.append(" A3.families as families, "); 
		sql.append(" A3.admission_max_score as admission_max_score, "); 
		sql.append(" A3.admission_min_score as admission_min_score, "); 
		sql.append(" ROUND(A3.admission_average_score) as admission_average_score,"); 
		sql.append(" A4.reported_max_score as reported_max_score, "); 
		sql.append(" A4.reported_min_score as reported_min_score, "); 
		sql.append(" ROUND(A4.reported_average_score) as reported_average_score "); 
		sql.append(" FROM "); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" jt.year as year, "); 
		sql.append(" st.school_id as school_id, "); 
		sql.append(" st.region as province, "); 
		sql.append(" st.batch as batch, "); 
		sql.append(" st.families as families, "); 
		sql.append(" max(st.stu_score) as admission_max_score,"); 
		sql.append(" min(st.stu_score) as admission_min_score,"); 
		sql.append(" AVG(st.stu_score) as admission_average_score "); 
		sql.append(" from std_admission st"); 
		sql.append(" LEFT JOIN jt_province_batch jt "); 
		sql.append(" on st.batch = jt.batch and st.region = jt.province "); 
		sql.append(" where st.families ='理工类'"); 
		sql.append(" group by st.region,st.batch ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A3 "); 
		sql.append(" LEFT JOIN"); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" jt.year as year, "); 
		sql.append(" st.school_id as school_id, "); 
		sql.append(" st.region, "); 
		sql.append(" st.batch,"); 
		sql.append(" st.families, "); 
		sql.append(" max(st.stu_score) as reported_max_score, "); 
		sql.append(" min(st.stu_score) as reported_min_score, "); 
		sql.append(" AVG(st.stu_score) as reported_average_score"); 
		sql.append(" from std_admission st"); 
		sql.append(" LEFT JOIN jt_province_batch jt "); 
		sql.append(" on st.batch = jt.batch and st.region = jt.province "); 
		sql.append(" where st.families ='理工类'"); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.region,st.batch ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A4 "); 
		sql.append(" on A3.province = A4.region "); 
		sql.append(" and A3.batch = A4.batch"); 
		sql.append(" and A3.year = A4.year"); 
		sql.append(" UNION"); 
		sql.append(" select "); 
		sql.append(" A5.year as year, "); 
		sql.append(" A5.school_id as school_id, "); 
		sql.append(" A5.province as province, "); 
		sql.append(" A5.batch as batch, "); 
		sql.append(" A5.families as families, "); 
		sql.append(" A5.admission_max_score as admission_max_score, "); 
		sql.append(" A5.admission_min_score as admission_min_score, "); 
		sql.append(" ROUND(A5.admission_average_score) as admission_average_score,"); 
		sql.append(" A6.reported_max_score as reported_max_score, "); 
		sql.append(" A6.reported_min_score as reported_min_score, "); 
		sql.append(" ROUND(A6.reported_average_score) as reported_average_score "); 
		sql.append(" FROM "); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" jt.year as year, "); 
		sql.append(" st.school_id as school_id, "); 
		sql.append(" st.region as province, "); 
		sql.append(" st.batch as batch, "); 
		sql.append(" st.families as families, "); 
		sql.append(" max(st.stu_score) as admission_max_score,"); 
		sql.append(" min(st.stu_score) as admission_min_score,"); 
		sql.append(" AVG(st.stu_score) as admission_average_score "); 
		sql.append(" from std_admission st"); 
		sql.append(" LEFT JOIN jt_province_batch jt "); 
		sql.append(" on st.batch = jt.batch and st.region = jt.province "); 
		sql.append(" where st.families = '文理综合类' "); 
		sql.append(" group by st.region,st.batch ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A5 "); 
		sql.append(" LEFT JOIN"); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" jt.year as year, "); 
		sql.append(" st.school_id as school_id, "); 
		sql.append(" st.region, "); 
		sql.append(" st.batch,"); 
		sql.append(" st.families, "); 
		sql.append(" max(st.stu_score) as reported_max_score, "); 
		sql.append(" min(st.stu_score) as reported_min_score, "); 
		sql.append(" AVG(st.stu_score) as reported_average_score"); 
		sql.append(" from std_admission st"); 
		sql.append(" LEFT JOIN jt_province_batch jt "); 
		sql.append(" on st.batch = jt.batch and st.region = jt.province "); 
		sql.append(" where st.families = '文理综合类' "); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.region,st.batch ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A6 "); 
		sql.append(" on A5.province = A6.region "); 
		sql.append(" and A5.batch = A6.batch"); 
		sql.append(" and A5.year = A6.year"); 
		sql.append(" ) B1 "); 
		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 (province != null && !"".equals(province)) {
			sql.append(" and B1.province='").append(province).append("'");
		}
		if (batch != null && !"".equals(batch)) {
			sql.append(" and B1.batch='").append(batch).append("'");
		}
		if (StringHelper.isEmpty(query)) {
			sql.append(" ORDER BY B1.province,B1.batch,B1.families DESC"); 
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}else {
			sql.append(" and (B1.year like ? or B1.province like ? or B1.batch like ? )");
			sql.append(" ORDER BY B1.province,B1.batch,B1.families DESC"); 
		}
		
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery, vQuery, vQuery);
	
	}



}
