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.StdLog;
import cn.hmsoft.skill.data.model.std.StdLogin;
import cn.hmsoft.skill.data.model.std.StdReg;

@Repository
public class StdRegDao extends PlatformDaoSupport<StdReg> {

	public Pager pageLog(Integer school_id, int std_id, String query, Integer start, Integer limit, QueryOrder order) {
		String sql = "select * from std_log where std_id=? " + (school_id == null ? "" : " and school_id=" + school_id);
		if (StringHelper.isEmpty(query))
			return this.pageBySql(StdLog.class, order, start, limit, sql, std_id);
		else
			return this.pageBySql(StdLog.class, order, start, limit, sql + " and log_type=?", std_id, query);

	}

	public Pager pageLogin(Integer school_id, int std_id, Integer start, Integer limit) {
		String sql = "select * from std_login where std_id=? "
				+ (school_id == null ? "" : " and school_id=" + school_id);
		return this.pageBySql(StdLogin.class, start, limit, sql + " order by login_time desc", std_id);

	}

	public Pager pageStdReg(Integer school_id, Integer start, Integer limit, String query, QueryOrder queryOrder) {
		StringBuilder sql = new StringBuilder("select e.pay_status as pay_status, ");
		sql.append(" CASE ");
		sql.append("when  ISNULL(sm.VERIFY_STATUS)  THEN '未审核'    ");
		sql.append("when sm.VERIFY_STATUS = '' THEN '未审核'         ");
		sql.append("when sm.VERIFY_STATUS = 'Active' then '审核通过'    ");
		sql.append(" when sm.VERIFY_STATUS = 'InActive' then '审核未通过'   ");
		sql.append("END as verify_status,   ");
		sql.append("s.* from std_reg s left join std_enrol e on s.std_id = e.std_id ");
		sql.append(" LEFT JOIN std_material sm on  s.std_id = sm.std_id and sm.material_type= 'NoCrownPhoto' ");
		sql.append(" where  from_school=").append(school_id);
		if (StringHelper.isEmpty(query)) {
			return this.pageMapBySql(queryOrder, start, limit, sql.toString());
		}
		sql.append(" and (s.cert_id=? or s.exam_id=? or s.std_mobile1=? or s.std_mobile2=? or s.std_name like ?)");
		String vQuery = this.generateLikeParamter(query);
		return this.pageMapBySql(queryOrder, start, limit, sql.toString(), query, query, query, query, vQuery);
	}

	public StdReg findByCertAndPasswd(String cert_id, String password, Integer school_id) {
		return this.findBySql("select * from std_reg where cert_id=? and std_pass=? and from_school=? ", cert_id,
				password, school_id);
	}

	public StdReg findByCert(String cert_id, Integer school_id) {
		return this.findBySql("select * from std_reg where cert_id=? and from_school=?", cert_id, school_id);
	}

	public void stdUpdatePassword(int std_id, int school_id) {
		String sql = "update std_reg e set std_pass='123456' where std_id=? and from_school =?";
		this.updateBySql(sql, std_id, school_id);
	}

	public void stdUpdatePassword(int std_id, int school_id, String password) {
		String sql = "update std_reg e set std_pass=? where std_id=? and from_school =?";
		this.updateBySql(sql, password, std_id, school_id);
	}

	/**************************************
	 * 根据条件查询考生
	 */
	public StdReg findStdByQuery(String query) {
		String sql = " select * from std_reg where std_name = ? or cert_id = ? ";
		return this.findBySql(sql, query.trim(), query.trim());
	}

	private final static String FIND_BY_STDID = "select * from std_reg where std_id = ?";

	/***********************
	 * 通过id查找考生
	 */
	public StdReg findByStdId(int std_id) {
		return this.findBySql(FIND_BY_STDID, std_id);
	}

	private final static String REFUSE_STD_IDENTITY = "update std_reg set std_verify_flag = ? where std_id=?";

	// /*******************
	// * 验证考生身份
	// *
	// * @param std
	// */
	// public void refuseIdentity(StdReg std) {
	// std.setStd_verify_flag(FrameStatus.InActive.toString());
	// this.updateBySql(REFUSE_STD_IDENTITY, FrameStatus.InActive.toString(),
	// std.getStd_id());
	// }

	/**
	 * 添加身份证照片信息
	 * 
	 * @param std_id
	 * @param std_param5
	 */
	public void stdUpdateIdcard(int std_id, String std_param5) {
		String sql = "update std_reg e set std_param5=? where std_id=? ";
		this.updateBySql(sql, std_param5, std_id);
	}

	/**
	 * 添加免冠照片信息
	 * 
	 * @param std_id
	 * @param std_param5
	 */
	public void stdUpdateNoCrownPhoto(int std_id, String std_image) {
		String sql = "update std_reg e set std_image=? where std_id=? ";
		this.updateBySql(sql, std_image, std_id);
	}

	/***********************
	 * 通过id查找考生
	 */
	public StdReg findById(int std_id) {
		String sql = "select sr.STD_ID,sr.CERT_ID,sr.CERT_ID,sr.EXAM_ID,sr.REG_TIME,sr.STD_NAME,sr.STD_SEX, "
				+ " sr.STD_MOBILE1,sr.STD_MOBILE2,sr.STD_SCHOOL,sr.LINK_ADDR,sr.STD_PARAM3,sr.STD_PARAM5,sr.STD_IMAGE,sr.FROM_SCHOOL,sr.STD_PASS , "
				+ " 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_reg sr LEFT JOIN std_material sm on  sr.std_id = sm.std_id and sm.material_type= 'NoCrownPhoto' where sr.std_id= ?";
		return this.findBySql(sql, std_id);
	}

}
