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

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.std.StdScoreSegment;


@Repository
public class StdScoreSegmentDao extends PlatformDaoSupport<StdScoreSegment> {


	/***********************
	 * 分页查询（普高考生十分一段统计表）
	 */
	public Pager pageScoreSegment(String year,String province,String batch,Integer school_id, Integer start, Integer limit, String query,
			QueryOrder queryOrder) {
		if("".equals(province))
		{
			province = "湖北";
		}
		StringBuilder sql = new StringBuilder("select   ");  
		sql.append("  B1.fraction_segment AS fraction_segment,    "); 
		sql.append("  B1.segment_score AS admission_segment_score,"); 
		sql.append("  B2.cumulative_score as admission_cumulative_score,"); 
		sql.append("  B3.segment_score as report_segment_score,   "); 
		sql.append("  B4.cumulative_score as report_cumulative_score,   "); 
		sql.append("  B1.YEAR AS YEAR,    "); 
		sql.append("  B1.school_id AS SCHOOL_ID,"); 
		sql.append("  B1.province AS PROVINCE   "); 
		sql.append("  from    "); 
		sql.append("  ( "); 
		sql.append("  select sc.fraction_segment as fraction_segment,   "); 
		sql.append("  count(sc.fraction_segment) as segment_score ,     "); 
		sql.append("  substring(st.notification_number,1,4) AS YEAR,st.school_id AS school_id,st.region AS province     "); 
		sql.append("  from std_admission st,std_score_segment sc  "); 
		sql.append("  where st.stu_score>=sc.min_score and st.stu_score<=sc.max_score     "); 
		sql.append("  AND st.region = '").append(province).append("'"); 
		sql.append("   group by sc.fraction_segment,YEAR,school_id,province   "); 
		sql.append("  ) B1    "); 
		sql.append("  LEFT JOIN     "); 
		sql.append("  ( "); 
		sql.append("  select  "); 
		sql.append("  A2.fraction_segment as fraction_segment,    "); 
		sql.append("  sum(A1.segment_score) as cumulative_score,  "); 
		sql.append("  A2.`YEAR` AS YEAR,  "); 
		sql.append("  A2.school_id AS school_id,"); 
		sql.append("  A2.province AS province   "); 
		sql.append("  FROM    "); 
		sql.append("  ( "); 
		sql.append("  select sc.fraction_segment as fraction_segment, count(sc.fraction_segment) as segment_score,"); 
		sql.append("  min(st.stu_score) as stu_score, "); 
		sql.append("  sc.min_score as min_score,substring(st.notification_number,1,4) AS YEAR,st.school_id AS school_id,st.region AS province   "); 
		sql.append("   from std_admission st,std_score_segment sc "); 
		sql.append("  where st.stu_score>=sc.min_score ");
		sql.append("  and st.stu_score<=sc.max_score AND st.region = '").append(province).append("'");
		sql.append("   group by sc.fraction_segment,sc.min_score,YEAR,school_id,PROVINCE  "); 
		sql.append("  ) A1,   "); 
		sql.append("  ( "); 
		sql.append("  select sc.fraction_segment as fraction_segment, count(sc.fraction_segment) as segment_score,"); 
		sql.append("  min(st.stu_score) as stu_score, "); 
		sql.append("  sc.min_score as min_score,substring(st.notification_number,1,4) AS YEAR,st.school_id AS school_id,st.region AS province   "); 
		sql.append("   from std_admission st,std_score_segment sc "); 
		sql.append("  where st.stu_score>=sc.min_score and st.stu_score<=sc.max_score ");
		sql.append("  AND st.region = '").append(province).append("'");
		sql.append("   group by sc.fraction_segment,sc.min_score,YEAR,school_id,PROVINCE  "); 
		sql.append("  ) A2    "); 
		sql.append("  where A1.stu_score>=A2.stu_score"); 
		sql.append("  group by A2.fraction_segment,A2.`YEAR` ,A2.school_id,A2.province    "); 
		sql.append("  ) B2    "); 
		sql.append("  ON B2.fraction_segment = B1.fraction_segment"); 
		sql.append("  LEFT JOIN     "); 
		sql.append("  ( "); 
		sql.append("  select sc.fraction_segment as fraction_segment,   "); 
		sql.append("  count(sc.fraction_segment) as segment_score ,     "); 
		sql.append("  substring(st.notification_number,1,4) AS YEAR,st.school_id AS school_id,st.region AS province     "); 
		sql.append("  from std_admission st,std_score_segment sc  "); 
		sql.append("  where st.stu_score>=sc.min_score and st.stu_score<=sc.max_score ");
		sql.append("  and st.isReported='是' AND st.region = '").append(province).append("'"); 
		sql.append("   group by sc.fraction_segment,YEAR,school_id,PROVINCE   "); 
		sql.append("  ) B3    "); 
		sql.append("  ON B3.fraction_segment = B1.fraction_segment"); 
		sql.append("  LEFT JOIN     "); 
		sql.append("  ( "); 
		sql.append("  select  "); 
		sql.append("  A4.fraction_segment as fraction_segment,    "); 
		sql.append("  sum(A3.segment_score) as cumulative_score,  "); 
		sql.append("  A3.`YEAR` AS YEAR,  "); 
		sql.append("  A3.school_id AS school_id,"); 
		sql.append("  A3.province AS PROVINCE   "); 
		sql.append("  FROM    "); 
		sql.append("  ( "); 
		sql.append("  select sc.fraction_segment as fraction_segment, count(sc.fraction_segment) as segment_score,"); 
		sql.append("  min(st.stu_score) as stu_score, "); 
		sql.append("  sc.min_score as min_score,"); 
		sql.append("  substring(st.notification_number,1,4) AS YEAR,st.school_id AS school_id,st.region AS province     "); 
		sql.append("   from std_admission st,std_score_segment sc "); 
		sql.append("  where st.stu_score>=sc.min_score and st.stu_score<=sc.max_score     "); 
		sql.append("  and st.isReported='是' AND st.region = '").append(province).append("'"); 
		sql.append("   group by sc.fraction_segment,sc.min_score,YEAR,school_id,PROVINCE  "); 
		sql.append("  ) A3,   "); 
		sql.append("  ( "); 
		sql.append("  select sc.fraction_segment as fraction_segment, count(sc.fraction_segment) as segment_score,"); 
		sql.append("  min(st.stu_score) as stu_score, "); 
		sql.append("  sc.min_score as min_score,substring(st.notification_number,1,4) AS YEAR,st.school_id AS school_id,st.region AS province   "); 
		sql.append("   from std_admission st,std_score_segment sc "); 
		sql.append("  where st.stu_score>=sc.min_score and st.stu_score<=sc.max_score     "); 
		sql.append("  and st.isReported = '是' AND st.region ='").append(province).append("'"); 
		sql.append("   group by sc.fraction_segment,sc.min_score,YEAR,school_id,PROVINCE  "); 
		sql.append("  ) A4    "); 
		sql.append("  where A3.stu_score>=A4.stu_score"); 
		sql.append("  group by A4.fraction_segment,YEAR,school_id,PROVINCE    "); 
		sql.append("  ) B4    "); 
		sql.append("  ON B4.fraction_segment = B1.fraction_segment"); 
		sql.append(" WHERE 1=1   and  B1.school_id='").append(school_id).append("'");
		sql.append(" and B1.province='").append(province).append("'");
		if (year != null && !"".equals(year)) {
			sql.append(" and B1.year='").append(year).append("'");
		}
		if (StringHelper.isEmpty(query)) {
			sql.append("  order by B1.fraction_segment DESC "); 
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}else {
			sql.append(" and (B1.year like ? or B1.province like ? )");
			sql.append("  order by B1.fraction_segment DESC "); 
		}
		
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery, vQuery, vQuery);
	}
	
}
