package com.kingdee.shr.affair.web.handler.util;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.commons.lang3.StringUtils;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.base.syssetting.app.io.fileImport.ImportException;
import com.kingdee.shr.base.syssetting.context.SHRContext;

public class EmployeeUtil {

	/**
	 * 获取当前最大的员工编码
	 * @return
	 * @throws BOSException
	 * @throws SQLException
	 */
	public int getFnumber() throws BOSException, SQLException{
		int fnumber = 0;
		String fnumberSql = "SELECT max(a.number) as number FROM  ( \n" +
				" SELECT max(fnumber) as number FROM T_bd_person \n" +
				" union \n" +
				" SELECT max(CFEmpNumber)  as number FROM T_HR_PreEntry \n" +
				" union \n" +
				" select max(b.fempNumber) as number from T_HR_EmpEnrollBizBill as a ,\n" +
				" T_HR_EmpEnrollBizBillEntry as b where a.fid = b.FBILLID \n" +
				" )  a";
		 Context ctx = SHRContext.getInstance().getContext();
		 IRowSet rs = DbUtil.executeQuery(ctx, fnumberSql);
		 while(rs.next()){
			 fnumber =  rs.getInt("number");
			 fnumber = Integer.valueOf(fnumber) + 1;
		 }
		return fnumber;
		 
	}
	
	/**
	 * 用户名唯一校验
	 * @param userName
	 * @param fid 非本单据ID校验
	 * @return
	 * @throws BOSException
	 * @throws SQLException
	 */
	public Boolean checkUserName(String userName,String fid,String fnumber) throws BOSException, SQLException{
		StringBuffer userNameSql = new StringBuffer();
		userNameSql.append("select * from ( ");
		userNameSql.append("select fid,fnumber from t_bd_person where Upper(CFUserName) = Upper('" + userName + "')");
		userNameSql.append(" union ");
		userNameSql.append(" select fid,null from T_HR_PreEntry where Upper(CFUserName_l1) = Upper('" + userName + "') ");
		userNameSql.append(" ) a where 1 = 1 ");
		if(StringUtils.isNotBlank(fid)){
			userNameSql.append(" and fid != '" + fid + "'");
		}
		if(StringUtils.isNotBlank(fnumber)){
			userNameSql.append(" and fnumber != '" + fnumber + "'");
		}
		System.out.print("用户名唯一校验："+userNameSql.toString());
		Context ctx = SHRContext.getInstance().getContext();
		IRowSet rs = DbUtil.executeQuery(ctx, userNameSql.toString());
		if(rs.next()){
			return false;
		}
		return true;
	}
	
	/**
	 *  再入职判断ְ
	 * @param idCard
	 * @param name
	 * @param birthday
	 * @return
	 * @throws SQLException 
	 * @throws BOSException 
	 */
	public void checkIsReEntry(String idCard,String name ,Date birthday) throws SQLException, BOSException{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String personBirthday = sdf.format(birthday);
		String checkSql = "select FIDCardNO,FPASSPORTNO  from t_bd_person" +
				" where FIDCardNO = '" + idCard + "' " +
				" or (to_char(FBirthday,'yyyy-MM-dd') = '" + personBirthday + "' and UPPER(FName_L1) = UPPER('"+ name +"') ) ";
		Context ctx = SHRContext.getInstance().getContext();
		System.out.println("再入职校验sql" + checkSql);
		IRowSet rs = DbUtil.executeQuery(ctx, checkSql);
		if(rs.next()){
			if(StringUtils.isNotBlank(rs.getString("FIDCardNO"))){
				throw new ImportException("Employee is re-entry employee, ID number：" + rs.getString("FIDCardNO"));
			}
			if(StringUtils.isNotBlank(rs.getString("FPASSPORTNO"))){
				throw new ImportException("Employee is re-entry employee, passport number：" + rs.getString("FPASSPORTNO"));
			}
			 
		}
		
		
	}

	/**
	 * 工作邮箱唯一校验
	 * @param email
	 * @param fid
	 * @param fnumber
	 * @return
	 * @throws BOSException 
	 * @throws SQLException 
	 */
	public Boolean checkEmail(String email,String fid,String fnumber) throws BOSException, SQLException{
		StringBuffer emailSql = new StringBuffer();
		emailSql.append("select * from ( ");
		emailSql.append("select fid,fnumber from t_bd_person where Upper(FEMail) = Upper('" + email + "')");
		emailSql.append(" union ");
		emailSql.append(" select fid,null from T_HR_PreEntry where Upper(CFWorkemail) = Upper('" + email + "') ");
		emailSql.append(" ) a where 1 = 1 ");
		if(StringUtils.isNotBlank(fid)){
			emailSql.append(" and fid != '" + fid + "'");
		}
		if(StringUtils.isNotBlank(fnumber)){
			emailSql.append(" and fnumber != '" + fnumber + "'");
		}
		Context ctx = SHRContext.getInstance().getContext();
		System.out.println("工作邮箱唯一校验：" + emailSql);
		IRowSet rs = DbUtil.executeQuery(ctx, emailSql.toString());
		if(rs.next()){
			return false;
		}
		return true;
	}
	
	
	
}