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

@Repository
public class SchoolAreaDao extends PlatformDaoSupport<SchoolArea> {

	/***********************
	 * 分页查询（各学校录取报道人数）
	 */
	public Pager pageSchoolAdmission(String year,String province,String area,String region, String school_name,Integer school_id, Integer start, Integer limit, String query,
			QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder("select A.year as year, A.school_id, A.province AS province,A.area AS area ,");
				sql.append("A.region AS region,A.school_name AS school_name, ");
				sql.append("SUM(IFNULL(A.admission_num,0) + IFNULL(B.admission_num,0)) AS admission_num, ");
				sql.append("SUM(IFNULL(A.reported_num,0) + IFNULL(B.reported_num,0)) AS reported_num from  "); 
				sql.append("(select substring(st1.notification_number,1,4) AS year, ");
				sql.append(" st1.region as province, ");
			    sql.append("sc.area as area, ");
			    sql.append("case when sc.region = st1.stu_area_residece then sc.region  "); 
			    sql.append("when sc.region=null then st1.stu_area_residece  "); 
			    sql.append("when sc.region != st1.stu_area_residece then  CONCAT_WS('-',st1.stu_area_residece,sc.region)  ");
			    sql.append("else st1.stu_area_residece  "); 
			    sql.append("end as region , ");
			    sql.append("st1.graduate_middle_school as school_name, "); 
			    sql.append("count(st1.graduate_middle_school) as admission_num, ");
			    sql.append("0 as reported_num, ");
			    sql.append("st1.school_id AS school_id  ");
			    sql.append("from std_admission st1 LEFT JOIN school_area sc ON  st1.graduate_middle_school = sc.school_name  ");
				sql.append("group by  ");
				sql.append("st1.graduate_middle_school,year,region, sc.area ,st1.region ,st1.school_id "); 
				sql.append(" ) A "); 
				sql.append(" LEFT JOIN "); 
				sql.append(" ( "); 
				sql.append(" select substring(st2.notification_number,1,4) AS year, st2.region as province, "); 
				sql.append(" sc.area as area, "); 
				sql.append(" case when sc.region = st2.stu_area_residece then sc.region "); 
				sql.append(" when sc.region=null then st2.stu_area_residece "); 
				sql.append(" when sc.region != st2.stu_area_residece then  CONCAT_WS('-',st2.stu_area_residece,sc.region) "); 
				sql.append(" else st2.stu_area_residece  "); 
				sql.append(" end as region , "); 
				sql.append(" st2.graduate_middle_school as school_name, "); 
				sql.append(" 0 as admission_num, "); 
				sql.append(" count(st2.graduate_middle_school) as reported_num "); 
				sql.append(" from std_admission st2 LEFT JOIN school_area sc ON  st2.graduate_middle_school = sc.school_name   "); 
				sql.append(" where  st2.isReported = '是' "); 
				sql.append(" group by st2.graduate_middle_school,year,region,sc.area ,st2.region  "); 
				sql.append(" ) B "); 
				sql.append(" ON A.school_name = B.school_name "); 
				sql.append(" where 1=1 and  A.school_id='").append(school_id).append("'");
				
				if (province != null && !"".equals(province)) {
					sql.append(" and A.province='").append(province).append("'");
				}
				if (area != null && !"".equals(area)) {
					sql.append(" and A.area='").append(area).append("'");
				}
				if (region != null && !"".equals(region)) {
					sql.append(" and A.region='").append(region).append("'");
				}
				if (school_name != null && !"".equals(school_name)) {
					sql.append(" and A.school_name='").append(school_name).append("'");
				}
				if (year != null && !"".equals(year)) {
					sql.append(" and A.year='").append(year).append("'");
				}
				
				if (StringHelper.isEmpty(query)) {
					sql.append(" GROUP BY A.year, A.province,A.area,A.region,A.school_name,A.school_id");
					return this.pageMapBySql(queryOrder, start, limit, sql.toString());
				}else {
					sql.append(" and (A.year=? or A.province=? or A.area=? or A.region=? or A.school_name=?)");
					
					sql.append(" GROUP BY A.year, A.province,A.area,A.region,A.school_name,A.school_id");
				}
		
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(),vQuery, vQuery, vQuery, vQuery,vQuery);
	}
	
}
