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

@Repository
public class DepartmentMajorDao extends PlatformDaoSupport<DepartmentMajor> {


	/***********************
	 * 分页查询（分系分专业分性别录取情况统计）
	 */
	public Pager pageDepartmentMajor(String year,String department,String major,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.department AS department ,  "); 
		sql.append(" B1.major AS major,  "); 
		sql.append(" B1.PLAN_NUM AS PLAN_NUM, "); 
		sql.append(" B1.admission_num AS ADMISSION_NUM, "); 
		sql.append(" B2.ADMISSION_MALE_NUM as admission_male_num, "); 
		sql.append(" B2.ADMISSION_FEMALE_NUM as admission_female_num, "); 
		sql.append(" B3.reported_num as reported_num, "); 
		sql.append(" B4.report_male_num as report_male_num, "); 
		sql.append(" b4.report_female_num 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(" st.department, "); 
		sql.append(" st.major, "); 
		sql.append(" count(jt.plan_num) as plan_num,  "); 
		sql.append(" count(st.department) as admission_num  "); 
		sql.append(" from std_admission st  "); 
		sql.append(" LEFT JOIN jt_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department   "); 
		sql.append(" group by st.department,st.major ,jt.`year`,st.school_id  "); 
		sql.append(" ) B1 "); 
		sql.append(" LEFT JOIN "); 
		sql.append(" ( "); 
		sql.append(" select  "); 
		sql.append(" A1.department AS department,A1.major AS major,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.department as department, "); 
		sql.append(" st1.major as major,   "); 
		sql.append(" count(st1.sex) as admission_male_num "); 
		sql.append(" from std_admission st1  "); 
		sql.append(" where st1.sex = '男' "); 
		sql.append(" group by st1.department,st1.major ,st1.sex  "); 
		sql.append(" ) A1 "); 
		sql.append(" LEFT JOIN "); 
		sql.append(" (select  "); 
		sql.append(" st2.department as department, "); 
		sql.append(" st2.major as major,   "); 
		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.department,st2.major ,st2.sex  "); 
		sql.append(" ) A2 ON A1.major = A2.major AND A1.department = A2.department "); 
		sql.append(" ) B2 "); 
		sql.append(" ON  B2.major = B1.major AND B2.department = B1.department "); 
		sql.append(" LEFT JOIN "); 
		sql.append(" ( "); 
		sql.append(" select  "); 
		sql.append(" st5.department as department, "); 
		sql.append(" st5.major as major,   "); 
		sql.append(" count(st5.department) as reported_num "); 
		sql.append(" from std_admission st5  "); 
		sql.append(" where  st5.isreported = '是' "); 
		sql.append(" group by st5.department,st5.major  "); 
		sql.append(" ) B3  "); 
		sql.append(" ON B3.major = B1.major AND B3.department = B1.department "); 
		sql.append(" LEFT JOIN "); 
		sql.append(" ( "); 
		sql.append(" select  "); 
		sql.append(" A3.department AS department,A3.major AS major,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.department as department, "); 
		sql.append(" st3.major as major,   "); 
		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.department,st3.major ,st3.sex  "); 
		sql.append(" ) A3 "); 
		sql.append(" LEFT JOIN "); 
		sql.append(" (select  "); 
		sql.append(" st4.department as department, "); 
		sql.append(" st4.major as major,   "); 
		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.department,st4.major ,st4.sex  "); 
		sql.append(" ) A4 ON A3.major = A4.major AND A3.department = A4.department "); 
		sql.append(" ) B4  "); 
		sql.append(" ON B4.major = B1.major AND B4.department = B1.department "); 
		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 (department != null && !"".equals(department)) {
			sql.append(" and B1.department='").append(department).append("'");
		}
		if (major != null && !"".equals(major)) {
			sql.append(" and B1.major='").append(major).append("'");
		}
		if (StringHelper.isEmpty(query)) {
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}else {
			sql.append(" and (B1.year like ? or B1.department like ? or B1.major like ? )");
		}
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery, vQuery, vQuery);
	}

	/**
	 * 查看对应年份数据是否导入数据库
	 * @param year
	 * @return
	 */
	public DepartmentMajor findByYear(String year) {
		return this.findBySql("select * from jt_department_major where year=?", year);
	}
	
	/***********************
	 * 分页查询（生源分专业分科类生源质量情况统计表）
	 */
	public Pager pageDepartmentMajorQuality(String year,String department,String major,Integer school_id, Integer start, Integer limit, String query,
			QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder("select   "); 
		sql.append(" C.year as year,"); 
		sql.append(" C.school_id AS school_id,"); 
		sql.append(" C.department as department,"); 
		sql.append(" C.major as major,"); 
		sql.append(" C.families as families,"); 
		sql.append(" C.admission_max_score as admission_max_score,"); 
		sql.append(" C.admission_min_score as admission_min_score,"); 
		sql.append(" ROUND(C.admission_average_score) as admission_average_score, "); 
		sql.append(" C.reported_max_score as reported_max_score,"); 
		sql.append(" C.reported_min_score as reported_min_score,"); 
		sql.append(" ROUND(C.reported_average_score) as reported_average_score"); 
		sql.append(" FROM "); 
		sql.append(" ("); 
		sql.append(" select "); 
		sql.append(" B1.year as year, "); 
		sql.append(" B1.school_id AS school_id, "); 
		sql.append(" B1.department as department, "); 
		sql.append(" B1.major as major, "); 
		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.department as department, "); 
		sql.append(" A1.major as major, "); 
		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.department as department, "); 
		sql.append(" st.major as major, "); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families ='文史类'"); 
		sql.append(" group by st.department,st.major ,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.department, "); 
		sql.append(" st.major,"); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families ='文史类'"); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.department,st.major ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A2 "); 
		sql.append(" on A1.department = A2.department "); 
		sql.append(" and A1.major = A2.major"); 
		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.department as department, "); 
		sql.append(" A3.major as major, "); 
		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.department as department, "); 
		sql.append(" st.major as major, "); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families ='理工类'"); 
		sql.append(" group by st.department,st.major ,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.department, "); 
		sql.append(" st.major,"); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families ='理工类'"); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.department,st.major ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A4 "); 
		sql.append(" on A3.department = A4.department "); 
		sql.append(" and A3.major = A4.major"); 
		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.department as department, "); 
		sql.append(" A5.major as major, "); 
		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.department as department, "); 
		sql.append(" st.major as major, "); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families = '文理综合类' "); 
		sql.append(" group by st.department,st.major ,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.department, "); 
		sql.append(" st.major,"); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families = '文理综合类' "); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.department,st.major ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A6 "); 
		sql.append(" on A5.department = A6.department "); 
		sql.append(" and A5.major = A6.major"); 
		sql.append(" and A5.year = A6.year"); 
		sql.append(" ) B1 "); 
		sql.append(" UNION"); 
		sql.append(" select "); 
		sql.append(" B2.year as year, "); 
		sql.append(" B2.school_id AS school_id, "); 
		sql.append(" B2.department as department, "); 
		sql.append(" '合计' as major, "); 
		sql.append(" '' as families,"); 
		sql.append(" MAX(B2.admission_max_score) as admission_max_score,"); 
		sql.append(" MIN(B2.admission_min_score) as admission_min_score,"); 
		sql.append(" MAX(ROUND(B2.admission_average_score)) as admission_average_score, "); 
		sql.append(" MAX(B2.reported_max_score) as reported_max_score,"); 
		sql.append(" MIN(B2.reported_min_score) as reported_min_score,"); 
		sql.append(" MAX(ROUND(B2.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.department as department, "); 
		sql.append(" A1.major as major, "); 
		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.department as department, "); 
		sql.append(" st.major as major, "); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families in ('文史类')"); 
		sql.append(" group by st.department,st.major ,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.department, "); 
		sql.append(" st.major,"); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families in ('文史类')"); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.department,st.major ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A2 "); 
		sql.append(" on A1.department = A2.department "); 
		sql.append(" and A1.major = A2.major"); 
		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.department as department, "); 
		sql.append(" A3.major as major, "); 
		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.department as department, "); 
		sql.append(" st.major as major, "); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families in ('理工类')"); 
		sql.append(" group by st.department,st.major ,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.department, "); 
		sql.append(" st.major,"); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families in ('理工类')"); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.department,st.major ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A4 "); 
		sql.append(" on A3.department = A4.department "); 
		sql.append(" and A3.major = A4.major"); 
		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.department as department, "); 
		sql.append(" A5.major as major, "); 
		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.department as department, "); 
		sql.append(" st.major as major, "); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families ='文理综合类'"); 
		sql.append(" group by st.department,st.major ,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.department, "); 
		sql.append(" st.major,"); 
		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_department_major jt "); 
		sql.append(" on st.major = jt.major and st.department = jt.department "); 
		sql.append(" where st.families = '文理综合类' "); 
		sql.append(" and st.isReported = '是' "); 
		sql.append(" group by st.department,st.major ,st.families,jt.`year`,st.school_id"); 
		sql.append(" ) A6 "); 
		sql.append(" on A5.department = A6.department "); 
		sql.append(" and A5.major = A6.major"); 
		sql.append(" and A5.year = A6.year"); 
		sql.append(" ) B2 "); 
		sql.append(" GROUP BY B2.year,"); 
		sql.append(" B2.department,B2.school_id "); 
		sql.append(" ) C"); 
		sql.append(" WHERE 1=1   and  C.school_id='").append(school_id).append("'");
		if (year != null && !"".equals(year)) {
			sql.append(" and C.year='").append(year).append("'");
		}
		if (department != null && !"".equals(department)) {
			sql.append(" and C.department='").append(department).append("'");
		}
		if (major != null && !"".equals(major)) {
			sql.append(" and C.major='").append(major).append("'");
		}
		if (StringHelper.isEmpty(query)) {
			sql.append(" ORDER BY C.department,C.major,C.families DESC"); 
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}else {
			sql.append(" and (C.year like ? or C.department like ? or C.major like ? )");
			sql.append(" ORDER BY C.department,C.major,C.families DESC"); 
		}
		
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery, vQuery, vQuery);
	}
}
