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

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

import org.springframework.stereotype.Repository;

import cn.hmsoft.jdbc.core.PlatformDaoSupport;
import cn.hmsoft.pay.data.model.PayBill;
import cn.hmsoft.skill.data.model.std.StdEnrol;
import cn.hmsoft.skill.data.model.std.StdEnrolSubject;

@Repository
public class StdEnrolDao extends PlatformDaoSupport<StdEnrol> {

	private final static String PAY_ENROL = "update std_enrol set bill_id=?,pay_time=?,pay_status='Active' where enrol_id=?";

	public boolean payEnrol(PayBill bill, int enrol_id) {
		int value = this.updateBySql(PAY_ENROL, bill == null ? null : bill.getBill_id(),
				bill == null ? LocalDateTime.now() : bill.getReply_time(),
				bill == null ? enrol_id : bill.getBusiness_id());
		if (value > 0)
			return true;
		return false;
	}

	private final static String FIND_ENROL = "select ap.*,e.std_id,e.std_score,e.std_level,e.ticket_num,e.ticket_file,s.enrol_start_time,s.enrol_end_time,e.enrol_id,e.pay_status,e.pay_time,e.enrol_time from std_enrol e,school_reg s,school_aspect ap where e.school_id=s.school_id and e.aspect_id=ap.aspect_id and e.std_id=? and e.aspect_id=?";

	public Map<String, Object> findEnrol(int std_id, int aspect_id) {
		List<Map<String, Object>> map = this.listMapBySql(FIND_ENROL, std_id, aspect_id);
		if (map.size() == 0)
			return null;
		return map.get(0);
	}
	
	private final static String FIND_ENROL_NAME = "select ap.*,e.std_id,e.std_score,e.std_level,e.ticket_num,e.ticket_file,s.enrol_start_time,s.enrol_end_time,e.enrol_id,e.pay_status,e.pay_time,e.enrol_time from std_enrol e,school_reg s,school_aspect ap where e.school_id=s.school_id and e.aspect_id=ap.aspect_id and e.std_id=? and ap.aspect_name=? and e.school_id=?";
	public Map<String, Object> findEnrol(int school_id,int std_id, String aspect_name) {
		List<Map<String, Object>> map = this.listMapBySql(FIND_ENROL_NAME, std_id, aspect_name,school_id);
		if (map.size() == 0)
			return null;
		return map.get(0);
	}
	
	private final static String LIST_ENROL = "select ap.*,e.std_id,e.std_score,e.std_level,e.ticket_num,e.ticket_file,s.enrol_start_time,s.enrol_end_time,e.enrol_id,e.pay_status,e.pay_time,e.enrol_time from std_enrol e,school_reg s,school_aspect ap where e.school_id=s.school_id and e.aspect_id=ap.aspect_id and e.std_id=? and e.school_id=?";
		
	public List<Map<String, Object>> listEnrol(int std_id, int school_id) {
		return this.listMapBySql(LIST_ENROL, std_id,school_id);
	}
	
	private final static String FIND_ENROL_BY_ID = "select ap.*,e.std_id,e.std_score,e.std_level,e.ticket_num,e.ticket_file,s.enrol_start_time,s.enrol_end_time,e.enrol_id,e.pay_status,e.pay_time,e.enrol_time from std_enrol e,school_reg s,school_aspect ap where e.school_id=s.school_id and e.aspect_id=ap.aspect_id and e.enrol_id=?";
	public Map<String, Object> findEnrol(int enrol_id) {
		List<Map<String, Object>> map = this.listMapBySql(FIND_ENROL_BY_ID, enrol_id);
		if (map.size() == 0)
			return null;
		return map.get(0);
	}
	
	
	public void unPay(int enrol_id) {
		String sql = "update std_enrol e set e.pay_time=null,e.pay_status=null where enrol_id=? and bill_id is null";
		this.updateBySql(sql, enrol_id);
	}
	
	public void clearTicket(int school_id) {
		String sql = "update std_enrol e set e.ticket_num=null,e.ticket_file=null where school_id=?";
		this.updateBySql(sql,school_id);
	}
	
	public List<StdEnrolSubject> listSubject(int std_id,int aspect_id) {
		String sql = "select * from std_enrol_subject where std_id=? and aspect_id=? order by subject_order";
		return this.listBySql(StdEnrolSubject.class,sql,std_id,aspect_id);
	}
	
	/**
	 * 武铁
	 * @param std_id
	 * @param aspect_id
	 * @return
	 */
	public List<StdEnrolSubject> listSubject8910(int std_id,int aspect_id) {
		String sql = "select * from std_enrol_subject where std_id=? and aspect_id=? order by EXAM_TIME ";
		return this.listBySql(StdEnrolSubject.class,sql,std_id,aspect_id);
	}


	/******************
	 * 查找考生的所有报考
	 */
	public List<Map<String, Object>> mapStdEnrol(int std_id) {
		StringBuilder sql = new StringBuilder();
		sql.append("select a.*,b.bill_no,b.channel_id,b.bill_status,b.channel_no                          ");
		sql.append("from (select                                                                          ");
		sql.append("		e.*,ag.school_name,ag.enrol_end_time,ag.ticket_note,ag.ticket_start_time,     ");
		sql.append("		ag.ticket_end_time,ap.aspect_code,ap.aspect_notice,ap.aspect_name,r.std_image ");
		sql.append("	 from std_enrol e,std_reg r,school_reg ag,school_aspect ap                        ");
		sql.append("where e.std_id=r.std_id and ap.aspect_id=e.aspect_id and ag.school_id=e.school_id  and e.std_id=?) a    ");
		sql.append("left join pay_bill b on a.bill_id=b.bill_id  order by aspect_id                       ");
		return this.listMapBySql(sql.toString(), std_id);
	}

	public void stdPaymentUpdate(int std_id,int school_id) {
		String sql = "update std_enrol e set pay_status='Active' where std_id=? and school_id =?";
		this.updateBySql(sql, std_id,school_id);
	}
	
	
	/******************
	 * 查找某个高中中学校的所有考生的打印准考证信息
	 */
	public List<Map<String, Object>> mapStdTicket(String std_school_code, String school_reg_id) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select sr.std_id,sr.CERT_ID,sr.EXAM_ID,sr.STD_NAME,                      ");
		sql.append(" sc.school_reg_id,sc.std_school_id,sc.std_school_code ,sc.std_school,     ");
		sql.append(" sr.STD_SEX,sr.STD_MOBILE1,sr.STD_PARAM1,sr.STD_PARAM4,                   ");
		sql.append(" sr.STD_IMAGE,sr.FROM_SCHOOL,st.TICKET_FILE,st.TICKET_NUM                 ");
		sql.append("  from std_school sc                                                      ");
		sql.append(" LEFT JOIN  std_reg sr                                                    ");
		sql.append(" on school_reg_id = sr.FROM_SCHOOL                                        ");
		sql.append(" LEFT JOIN std_enrol st                                                   ");
		sql.append(" on sr.STD_ID = st.STD_ID                                                 ");
		sql.append(" where sc.std_school_code = sr.STD_PARAM1                                 ");
		sql.append(" and sc.std_school_code = ? and sc.school_reg_id = ?       ");
		return this.listMapBySql(sql.toString(), std_school_code, school_reg_id);
	}

	/**
	 * 报考的信息是否照片审核
	 * @param enrol_id
	 * @return
	 */
	public StdEnrol findById(int enrol_id ) {
		String sql = " select e.enrol_id,	e.std_id,	e.aspect_id,	e.enrol_time,	e.enrol_status,	e.pay_time,	e.bill_id,	e.school_id,	e.fee_amount, " 
				+"	e.pay_status,	e.pay_end_time,	e.ticket_num,	e.ticket_file,	e.std_score,	e.std_level,	"
				+" CASE "  
				+"	 when  ISNULL(sm.VERIFY_STATUS)  THEN '未审核'   "  
				+"	when sm.VERIFY_STATUS = '' THEN '未审核'         "  
				+" 		 when sm.VERIFY_STATUS = 'Active' then '审核通过'    " 
				+" 	   when sm.VERIFY_STATUS = 'InActive' then '审核未通过'  "  
				+" END as verify_status  " 
				+" from STD_ENROL e LEFT JOIN std_material sm on  e.std_id = sm.std_id and sm.material_type= 'NoCrownPhoto' where e.ENROL_ID= ? ";
		return this.findBySql(sql, enrol_id);
	}


}
