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

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import cn.hmsoft.helper.StringHelper;
import cn.hmsoft.jdbc.core.PlatformDaoSupport;
import cn.hmsoft.jdbc.entity.DataRecord;
import cn.hmsoft.jdbc.entity.Pager;
import cn.hmsoft.jdbc.entity.QueryOrder;
import cn.hmsoft.skill.data.model.school.SchoolAspect;
import cn.hmsoft.skill.data.model.school.SchoolAspectSubject;

@Repository
public class SchoolAspectDao extends PlatformDaoSupport<SchoolAspect> {

	private final static String LIST_ASPECT = "select a.*,e.enrol_time,e.PAY_TIME from (select * from school_aspect where school_id=? and (aspect_type=? or aspect_type is null)) a left join (select * from std_enrol e where school_id=? and std_id=?) e on a.ASPECT_ID=e.ASPECT_ID and a.SCHOOL_ID=e.SCHOOL_ID order by pay_time,aspect_code";

	public List<Map<String, Object>> listAspect(int std_id, int school_id,String std_type) {
		return this.listMapBySql(LIST_ASPECT, school_id, std_type,school_id, std_id);
	}

	private final static String TOTLA_ENROL_ASPECT = "select a.*,b.enrol_count,b.pay_count from"
			+ "(select t.*,r.school_short_img from school_aspect t,school_reg r  where r.school_id=t.school_id and t.school_id=?) a left join ("
			+ "select count(1) enrol_count,ASPECT_ID,sum(case when PAY_STATUS='Active' then 1 else 0 end) pay_count from std_enrol e where e.school_id=? group by ASPECT_ID ) b on a.aspect_id=b.aspect_id order by a.aspect_id";

	//学校下对应报考高中学校的账号信息
	private final static String LIST_SCHOOL_STD = "select SS.* from std_school ss LEFT JOIN (select  std_param1 from std_reg sr GROUP BY std_param1) s1 on ss.std_school_code = s1.std_param1 where  ss.school_reg_id=? ";

	
	/*********************
	 * 统计某个学校的专业报考情况
	 */
	public List<Map<String, Object>> totalAspect(int school_id) {
		return this.listMapBySql(TOTLA_ENROL_ASPECT, school_id, school_id);

	}

	private final static String TOTAL_ENROL_FEE = "select sum(BILL_AMOUNT) i01,sum(case when reply_time>=? and reply_time<? then bill_amount else 0 end) i02 from pay_bill p,std_enrol e where p.BUSINESS_ID=e.ENROL_ID and p.bill_status='Complete' and school_id=?";

	/***********************
	 * 统计每一个学校的缴费情况
	 */
	public DataRecord totalFee(int school_id) {
		LocalDate date = LocalDate.now();
		return this.findBySql(DataRecord.class, TOTAL_ENROL_FEE, date, date.plusDays(1), school_id);
	}

	private final static String 	LIST_ASPECT_STD = "select a.*,b.CHANNEL_ID,b.CHANNEL_NO,b.bill_no from ( "
			+ "select s.*,e.ticket_num,e.ticket_file,e.enrol_id,e.aspect_id,e.school_id,sa.aspect_name,e.enrol_time,e.pay_time,e.bill_id,e.pay_status from std_reg s,std_enrol e,school_aspect sa where s.std_id=e.std_id and sa.aspect_id=e.aspect_id and e.school_id=? ) a "
			+ "left join pay_bill b on a.bill_id=b.bill_id ";
	
	/**
	 * 二级学校对应的考生信息
	 */
	private final static String 	LIST_SCHOOL_STUDENT = "SELECT	a.*, b.CHANNEL_ID, b.CHANNEL_NO, b.bill_no FROM	( SELECT s.*, e.ticket_num,	 e.ticket_file,	 e.enrol_id, e.aspect_id," 
			+ "	e.school_id, sa.aspect_name,  e.enrol_time,	 e.pay_time, e.bill_id,	 e.pay_status 	FROM std_reg s,	 std_enrol e, school_aspect sa "
			+ "	WHERE  s.std_id = e.std_id  AND sa.aspect_id = e.aspect_id 	 AND e.school_id = ? ) a	LEFT JOIN pay_bill b ON a.bill_id = b.bill_id" 
			+ "	left JOIN std_school sc on a.STD_PARAM1 = sc.std_school_code where a.std_param1 = ? and school_reg_id = ?";
	
	/******************
	 * 查询考生
	 */
	public Pager pageAspectStd(int school_id, String query, Integer start, Integer limit, QueryOrder order) {

		if (StringHelper.isEmpty(query))
			return this.pageMapBySql(order, start, limit, LIST_ASPECT_STD, school_id);
		String sql = "select a.*,b.CHANNEL_ID,b.CHANNEL_NO,b.bill_no from ( "
				+ "select s.*,e.ticket_num,e.ticket_file,e.enrol_id,e.aspect_id,e.school_id,sa.aspect_name,e.enrol_time,e.pay_time,e.bill_id,e.pay_status from std_reg s,std_enrol e,school_aspect sa where s.std_id=e.std_id and sa.aspect_id=e.aspect_id and e.school_id=? "
				+ " and ( aspect_name=? or std_mobile1=? or std_name like ?  or cert_id like ? or exam_id like ? or std_school like ? )) a "
				+ "left join pay_bill b on a.bill_id=b.bill_id ";
		String like = generateLikeParamter(query);
		return this.pageMapBySql(order, start, limit, sql, school_id, query, query, like, like, like, like);
	}
	
	public List<Map<String,Object>> listAspectStd(int school_id){
		return this.listMapBySql(LIST_ASPECT_STD +" order by aspect_name,pay_time", school_id);
	}
	
	public List<SchoolAspectSubject> listSchoolSubject(int school_id) {
		String sql = "select sb.* from school_aspect_subject sb,school_aspect ap where sb.aspect_id=ap.aspect_id and ap.school_id=? order by ap.aspect_id,subject_name";
		return this.listBySql(SchoolAspectSubject.class, sql,school_id);
	}

	public Pager pageSchoolAspect(Integer school_id, Integer start, Integer limit, String query, QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from school_aspect ");
		sql.append(" where school_id=").append(school_id);
		if (StringHelper.isEmpty(query)) {
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}
		sql.append(" and (aspect_code=? or aspect_name like ?)");
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), query, "%" + query + "%");
	}

	public List<SchoolAspect> findAspectByName(Integer org_id, String aspect_name) {
		String sql = "select * from school_aspect where school_id=? and aspect_name=?";
		return this.listBySql(sql, org_id, aspect_name);
	}
	
	/**
	 * 通过学校查询考生报考信息
	 * @param org_id
	 * @return
	 */
	public List<SchoolAspect> findAspectBySchoolId(Integer org_id) {
		String sql = "select * from school_aspect where school_id=?";
		return this.listBySql(sql, org_id);
	}
	
	/***********************
	 * 分页查询（导出考生报考及缴费统计表）
	 */
	public Pager pageEnrol(Integer school_id, Integer start, Integer limit, String query,
			QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder(" select a.*, b.enrol_count, b.pay_count ");
		sql.append("  from (select t.ASPECT_NAME,t.aspect_id,t.SCHOOL_ID ");
		sql.append(" from school_aspect t, school_reg r ");
		sql.append(" where r.school_id = t.school_id ");
		sql.append(" and t.school_id = '").append(school_id).append("'");
		sql.append(" ) a  left join  ");
		sql.append(" (select count(1) enrol_count,  ");
		sql.append(" ASPECT_ID,  ");
		sql.append(" sum(case  ");
		sql.append(" when PAY_STATUS = 'Active' then  ");
		sql.append(" 1  ");
		sql.append("  else  ");
		sql.append("  0  ");
		sql.append(" end) pay_count  ");
		sql.append(" from std_enrol e  ");
		sql.append(" where e.school_id = '").append(school_id).append("'");
		sql.append(" group by ASPECT_ID) b  ");
		sql.append(" on a.aspect_id = b.aspect_id  ");
		sql.append("  UNION all ");
		sql.append(" select '合计' as	ASPECT_NAME, ");
		sql.append("	'' as	aspect_id, ");
		sql.append("	'' as	SCHOOL_ID ,  ");
		sql.append("	sum(b.enrol_count) as enrol_count,   ");
		sql.append("	sum(b.pay_count) as pay_count ");
		sql.append("  from (select t.ASPECT_NAME,t.aspect_id,t.SCHOOL_ID ");
		sql.append(" from school_aspect t, school_reg r ");
		sql.append(" where r.school_id = t.school_id ");
		sql.append(" and t.school_id = '").append(school_id).append("'");
		sql.append(" ) a  left join  ");
		sql.append(" (select count(1) enrol_count,  ");
		sql.append(" ASPECT_ID,  ");
		sql.append(" sum(case  ");
		sql.append(" when PAY_STATUS = 'Active' then  ");
		sql.append(" 1  ");
		sql.append("  else  ");
		sql.append("  0  ");
		sql.append(" end) pay_count  ");
		sql.append(" from std_enrol e  ");
		sql.append(" where e.school_id = '").append(school_id).append("'");
		sql.append(" group by ASPECT_ID) b  ");
		sql.append(" on a.aspect_id = b.aspect_id  ");
		if (StringHelper.isEmpty(query)) {
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), vQuery);
	
	}
	
	/**
	 * 学校下面的二级学校（高中）账号和密码
	 * @param school_id
	 * @return
	 */
	public List<Map<String,Object>> listTwoSchool(int school_id){
		return this.listMapBySql(LIST_SCHOOL_STD +" order by std_school_id", school_id);
	}
	
	/**
	 * 二级学校对应的考生信息
	 * @param school_id
	 * @return
	 */
	public List<Map<String,Object>> listAspectSchoolStudent(int school_id,String std_school_code){
		return this.listMapBySql(LIST_SCHOOL_STUDENT +" order by aspect_name,channel_id,pay_time", school_id,std_school_code,school_id);
	}
}
