package com.kingdee.shr.customer.gtiit.osf;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.bsf.service.app.IHRMsfService;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;

/**
 * 同步AD域信息接口
 * 
 * @author 何朗
 *
 */

public class SynADService implements IHRMsfService {

	private static Logger logger = Logger.getLogger("com.kingdee.shr.customer.gtiit.osf.SynADService");

	@Override
	public Object process(Context ctx, Map map) throws EASBizException, BOSException {
		logger.error("进入同步AD域信息接口");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		// 开始日期
		String startDate = (String) map.get("startDate");
		// 截止日期
		String endDate = (String) map.get("endDate");
		// 员工工号
		String personNumber = (String) map.get("personNumber");

		logger.error("获取员工信息接口参数-开始日期：" + startDate + ",截止日期：" + endDate + ",人员：" + personNumber);

		if (StringUtils.isBlank(startDate) || StringUtils.isBlank(endDate)) {
			Calendar calendar = Calendar.getInstance();
			startDate = sdf.format(calendar.getTime());
			calendar.add(Calendar.DAY_OF_MONTH, 1);
			endDate = sdf.format(calendar.getTime());
		}
		JSONArray jsonArray = new JSONArray();
		Set<String> personIdSet = new HashSet<String>();

		try {
			// 入职审核通过、当天入职的员工
			String enrollSql = "SELECT b.fpersonid FROM T_HR_EmpEnrollBizBill a left join T_HR_EmpEnrollBizBillentry b on a.fid=b.fbillid where a.fbillstate = '3' and b.fenrolldate='"
					+ startDate + "'";
			IRowSet enrollRow = DbUtil.executeQuery(ctx, enrollSql);
			while (enrollRow.next()) {
				personIdSet.add(enrollRow.getString("fpersonid"));
			}

			// 调动单审核通过的员工
			String fluctuaSql = "SELECT b.fpersonid FROM T_HR_FluctuationBizBill a left join T_HR_FluctuationBizBillentry b on a.fid=b.fbillid where FBILLSTATE ='3' and FLASTUPDATETIME >='"
					+ startDate + "' and FLASTUPDATETIME <='" + endDate + "'";
			IRowSet fluctuaRow = DbUtil.executeQuery(ctx, fluctuaSql);
			while (fluctuaRow.next()) {
				personIdSet.add(fluctuaRow.getString("fpersonid"));
			}

			// 离职单审核通过、前一天离职的员工
			Calendar resignCalendar = Calendar.getInstance();
			resignCalendar.add(Calendar.DAY_OF_MONTH, -1);
			String resignSql = "select b.fpersonid from T_HR_ResignBizBill a left join T_HR_ResignBizBillentry b on a.fid=b.fbillid where a.FBILLSTATE = '3' and b.FBIZDATE = '"
					+ sdf.format(resignCalendar.getTime()) + "'";
			IRowSet resignRow = DbUtil.executeQuery(ctx, resignSql);
			while (resignRow.next()) {
				personIdSet.add(resignRow.getString("fpersonid"));
			}

			// 预入职生效的员工
			String preSql = "select per.fid from T_HR_PreEntry pre left join T_BD_Person per on per.fnumber = pre.CFEMPNUMBER where FBILLSTATE = '3' and FCHECKINSTATE = '3' and CFEMPNUMBER <> '' and FBIZDATE >='"
					+ startDate + "' and FBIZDATE <='" + endDate + "'";
			IRowSet preRow = DbUtil.executeQuery(ctx, preSql);
			while (preRow.next()) {
				personIdSet.add(preRow.getString("fid"));
			}

			// 员工信息变动的员工
			String lastDateSql = "SELECT fid FROM T_BD_Person where flastupdatetime>='" + startDate
					+ "' and flastupdatetime<='" + endDate + "'";
			IRowSet lastDateRow = DbUtil.executeQuery(ctx, lastDateSql);
			while (lastDateRow.next()) {
				personIdSet.add(lastDateRow.getString("fid"));
			}

			if (StringUtils.isNotBlank(personNumber)) {
				personIdSet = new HashSet<String>();
				String personSql = "SELECT fid FROM T_BD_Person where fnumber = '" + personNumber + "'";
				IRowSet personRow = DbUtil.executeQuery(ctx, personSql);
				while (personRow.next()) {
					personIdSet.add(personRow.getString("fid"));
				}
			}

			logger.error("员工id集合：" + personIdSet.toString());

			StringBuilder sb = new StringBuilder();
			if (personIdSet.size() == 0) {
				sb.append("''");
			} else {
				for (String value : personIdSet) {
					sb.append("'").append(value).append("',");
				}
				// 移除最后一个逗号
				sb.deleteCharAt(sb.length() - 1);
			}

			StringBuffer sql = new StringBuffer();

			sql.append(
					" SELECT DISTINCT p.fname_l2 showname ,sj.cfusername as manager, fullp.fname_l1 AS extensionAttribute6 ,res.extensionAttribute4 ,cmpt.cfexpireddate as extensionAttribute10, emp.departmentname department,emp.positionname title ,p.fnumber personnumber,name personname,p.femail email,hpcc.cfprivateemail AS extensionAttribute9,p.cfusername sAMAccountName,p.cfgivenName givenName,p.cfsurname sn,  \r\n"
							+ "  case when p.fgender='1' then '男' when p.fgender='2' then '女' else '' end as gender,ify.fname_l2 extensionAttribute5,  \r\n"
							+ "			  sj.fnumber  personparent,zt.fname_l2 statusname,p.fcell as extensionAttribute11,p.fofficephone as telephoneNumber,la.fenterdate extensionAttribute3,nation.fname_l1 extensionAttribute8,\r\n"
							+ "			  us.fpassword password,emp.jobname,emp.jobgradename  FROM T_BD_Person p    \r\n"
							+ "			 left join (select e.fpersonid personid,a.fname_l2 departmentname,g.fname_l2 positionname,job.fname_l2 jobname,gra.fname_l2 jobgradename   \r\n"
							+ "			 from (SELECT FPERSONID,max(FEFFDT) as maxDate FROM T_HR_EmpPostExperienceHis where fassigntype='1' group by  FPERSONID) as h      \r\n"
							+ "			     left join T_HR_EmpPostExperienceHis e on e.fpersonid= h.FPERSONID and e.feffdt = h.maxDate     \r\n"
							+ "			     left join T_ORG_Admin a on a.fid=e.fadminorgid     \r\n"
							+ "			     left join T_ORG_Position g on g.fid=e.fpositionid     \r\n"
							+ "			     left join T_ORG_Job job on job.fid = g.fjobid     \r\n"
							+ "			     left join (SELECT * FROM T_HR_EmpPostRank where fislatest='1') r on r.fpersonid = e.fpersonid     \r\n"
							+ "			     left join T_HR_JobGrade gra on gra.fid=r.fjobgradeid where e.fassigntype='1') emp on p.fid=emp.personid      \r\n"
							+ "			 left join T_HR_EmployeeClassify ify on ify.fid = p.cfpersontypeid    \r\n"
							+ "			 LEFT JOIN T_BD_Nationality nation ON nation.fid = p.fnationalityid    \r\n"
							+ "			 left join CT_MP_Fullorpart fullp on fullp.fid=p.cfftorptid    \r\n"
							+ "			 	left join (select hpca.fpersonid,hpca.cfprivateemail from T_HR_PersonContactMethod hpca inner join (select fpersonid,max(flastupdatetime) maxDate from T_HR_PersonContactMethod group by fpersonid) hpcb on hpca.fpersonid=hpcb.fpersonid and hpca.flastupdatetime=hpcb.maxDate) hpcc on hpcc.fpersonid=p.fid    \r\n"
							+ "			 		left join (select ctpa.fpersonid,ctpa.cfexpireddate from CT_MP_Pcontractinfo ctpa inner join  (select fpersonid,max(cfexpireddate) maxDate from CT_MP_Pcontractinfo group by fpersonid ) ctpb on ctpa.fpersonid=ctpb.fpersonid and ctpa.cfexpireddate=ctpb.maxDate) cmpt on cmpt.fpersonid=p.fid    \r\n"
							+ "			left join (select  p.fname_l2 as parentName,   p.cfusername ,re.FPERSONID,p.cfsurname,p.fname_l2 name,p.fnumber from (SELECT FPERSONID,max(flastupdatetime) as maxDate FROM T_HR_EmpOrgRelation group by  FPERSONID) as re      \r\n"
							+ "			      left join T_HR_EmpOrgRelation rl on re.fpersonid = rl.fpersonid and re.maxDate = rl.flastupdatetime      \r\n"
							+ "			      left join T_BD_Person p on p.fid=rl.cflinemanagernameI   where p.fname_l1 is not null\r\n"
							+ "                                        ) sj on sj.fpersonid=p.fid   \r\n"
							+ "			 left join T_HR_BDEmployeeType zt on zt.fid=p.femployeetypeid     \r\n"
							+ "			 left join (select a.fpersonid,b.fenterdate,b.FEFFDT,b.fleffdt from (SELECT FPERSONID,max(fenterdate) as maxDate FROM T_HR_EmpLaborRelationHis group by fpersonid) a      \r\n"
							+ "			     left join T_HR_EmpLaborRelationHis b on a.fpersonid=b.fpersonid and a.maxdate=b.fenterdate) la on la.fpersonid=p.fid      \r\n"
							+ "			 left join T_PM_USER us on us.FPERSONID=p.fid    \r\n"
							+ "left join (select  a.fid,b.fbillid,b.fpersonid,b.FBIZDATE as extensionAttribute4  from T_HR_ResignBizBill a  \r\n"
							+ "			 left join T_HR_ResignBizBillentry b on a.fid=b.fbillid where a.FBILLSTATE = '3') as res  on res.fpersonid =p.fid  ");

			sql.append("where p.fid in (" + sb.toString() + ")");
			sql.append("and  ( p.CFGtiitemail is null  or   p.CFGtiitemail !='Yes' ) ");
			String str = sql.toString();
			str = str.replaceAll("(?i)fname_l2", "fname_l1");
			logger.info("非预入职流程SQL"+str);
			IRowSet iRowSet = DbUtil.executeQuery(ctx, str);
			while (iRowSet.next()) {
				JSONObject jsonObject = new JSONObject();
				// 用户名
				jsonObject.put("sAMAccountName", iRowSet.getString("sAMAccountName"));
				// 名
				jsonObject.put("givenName", iRowSet.getString("givenName"));
				// 性
				jsonObject.put("sn", iRowSet.getString("sn"));
				// 显示名称
				jsonObject.put("displayName", iRowSet.getString("showname"));
				// 用户名称
				jsonObject.put("userPrincipalName", iRowSet.getString("email"));
				// 邮箱地址
				jsonObject.put("mail", iRowSet.getString("email"));
				// 办公电话
				if(StringUtils.isNotBlank(iRowSet.getString("telephoneNumber"))) {
					jsonObject.put("telephoneNumber", iRowSet.getString("telephoneNumber"));
				}
				// 职位
				jsonObject.put("title", iRowSet.getString("title"));
				// 所属部门
				jsonObject.put("department", iRowSet.getString("department"));
				// 上级负责人
				jsonObject.put("manager", iRowSet.getString("manager"));
				// 员工工号
				jsonObject.put("employeeID", iRowSet.getString("personnumber"));
				// 员工类型
				jsonObject.put("employeeType", "STAFF");
				// 入职日期
				jsonObject.put("extensionAttribute3", stringDateFormat(iRowSet.getDate("extensionAttribute3")));
				// 离职日期
				Date extensionAttribute4 = iRowSet.getDate("extensionAttribute4");
				String AccountExpirationDate = "";
				if (extensionAttribute4 != null) {

					Calendar calendar = Calendar.getInstance();
					calendar.setTime(extensionAttribute4);
					calendar.add(Calendar.DAY_OF_MONTH, 1);
					Date nextDay = (Date) calendar.getTime();
					SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

					// 使用format方法将Date对象转换为指定格式的字符串
					AccountExpirationDate = formatter.format(nextDay);

				}
				jsonObject.put("extensionAttribute4", stringDateFormat(iRowSet.getDate("extensionAttribute4")));
				// 人员类型
				jsonObject.put("extensionAttribute5", iRowSet.getString("extensionAttribute5"));
				// 全职/兼职
				jsonObject.put("extensionAttribute6", iRowSet.getString("extensionAttribute6"));
				//国籍
				jsonObject.put("extensionAttribute8", iRowSet.getString("extensionAttribute8"));
				//私人邮箱
				jsonObject.put("extensionAttribute9", iRowSet.getString("extensionAttribute9"));
				//合同结束日期
				jsonObject.put("extensionAttribute10", stringDateFormat(iRowSet.getDate("extensionAttribute10")));
				//私人手机
				jsonObject.put("extensionAttribute11", iRowSet.getString("extensionAttribute11"));
				// 失效日期
				jsonObject.put("AccountExpirationDate", AccountExpirationDate);
				jsonArray.add(jsonObject);
			}

			// 处理新增预入职
			StringBuffer sql2 = new StringBuffer();
			sql2.append(
					"SELECT  pe.FName_l1  as  sAMAccountName ,pe.CFGivenName_l1 as givenName,pe.CFSurname_l1  as sn ,pe.FName_l1 as displayName ,pe.CFUserName_l1 as userPrincipalName ,\r\n"
							+ " pe.cfworkemail as mail ,pe.femail as extensionAttribute9,pe.FCellPhone  as telephoneNumber ,pe.FPositionID ,pe.FAdminOrgUnitID ,pe.CFLmanagerID ,pe.CFEmpNumber as employeeID ,pe.FPreEnterDate as extensionAttribute3 ,ec.fname_l1 AS extensionAttribute5 ,pe.CFFtorptID\r\n"
							+ " ,pep.CFGtiitemail  \r\n" + " , post.fname_l1   as title\r\n"
							+ " ,bu.fname_l1    as department\r\n" + " ,p.cfusername as   manager\r\n"
							+ ",ec.FNAME_l1 as  employeeType \r\n" + ",f.fname_l1 as  extensionAttribute6 \r\n"
							+ "  FROM  T_HR_PreEntry    pe\r\n"
							+ "  left join   T_HR_PreEntryPerson  pep  on pep.fid =  pe.FTalentID\r\n"
							+ "	left join T_ORG_Position post on pe.FPositionID = post.fid   \r\n"
							+ "        left join   T_ORG_BaseUnit bu on bu.fid=pe.FAdminOrgUnitID   \r\n"
							+ "        left join   T_BD_PERSON p on p.fid = pe.CFLmanagerID\r\n"
							+ "        left join T_HR_EmployeeClassify ec on ec.fid = pe.CFPersontypeID\r\n"
							+ "        left join CT_MP_Fullorpart f on f.fid = pe.CFFtorptID\r\n" + "where 1=1\r\n"
							+ "and  pe.FCheckInState='4'\r\n" + "and  pe.FBillState ='3'  ");
			sql2.append(" and  ( pep.CFGtiitemail is null  or  pep.CFGtiitemail !='Yes' ) ");

			if (StringUtils.isNotBlank(personNumber)) {
				sql2.append(" and  pe.CFEmpNumber  = '" + personNumber + "' ");
			}

			if (StringUtils.isNotBlank(startDate)) {
				sql2.append(" and  pe.FLastUpdateTime >= '" + startDate + "' ");
			}
			if (StringUtils.isNotBlank(endDate)) {
				sql2.append(" and  pe.FLastUpdateTime <= '" + endDate + "' ");
			}
			logger.info("预入职流程SQL"+sql2.toString());

			IRowSet iRowSet2 = DbUtil.executeQuery(ctx, sql2.toString());
			while (iRowSet2.next()) {
				JSONObject jsonObject = new JSONObject();
				// 用户名
				jsonObject.put("sAMAccountName", iRowSet2.getString("sAMAccountName"));
				// 名
				jsonObject.put("givenName", iRowSet2.getString("givenName"));
				// 性
				jsonObject.put("sn", iRowSet2.getString("sn"));
				// 显示名称
				jsonObject.put("displayName", iRowSet2.getString("displayName"));
				// 用户名称
				jsonObject.put("userPrincipalName", iRowSet2.getString("userPrincipalName"));
				// 邮箱地址
				jsonObject.put("mail", iRowSet2.getString("mail"));
				// 手机号码
				if(StringUtils.isNotBlank(iRowSet.getString("telephoneNumber"))) {
					jsonObject.put("telephoneNumber", iRowSet.getString("telephoneNumber"));
				}
				// 职位
				jsonObject.put("title", iRowSet2.getString("title"));
				// 所属部门
				jsonObject.put("department", iRowSet2.getString("department"));
				// 上级负责人
				jsonObject.put("manager", iRowSet2.getString("manager"));
				// 员工工号
				jsonObject.put("employeeID", iRowSet2.getString("employeeID"));
				// 员工类型
				jsonObject.put("employeeType", "STAFF");
				// 入职日期
				jsonObject.put("extensionAttribute3", stringDateFormat(iRowSet2.getDate("extensionAttribute3")));
				// 离职日期
		
				jsonObject.put("extensionAttribute4", null);
				// 人员类型
				jsonObject.put("extensionAttribute5", iRowSet2.getString("extensionAttribute5"));
				// 全职/兼职
				jsonObject.put("extensionAttribute6", iRowSet2.getString("extensionAttribute6"));
				jsonObject.put("extensionAttribute8", "");
				jsonObject.put("extensionAttribute9", "");
				jsonObject.put("extensionAttribute10", "");
				// 失效日期
				jsonObject.put("AccountExpirationDate", null);
				jsonArray.add(jsonObject);
			}
			

		} catch (Exception e) {
			e.printStackTrace();
			JSONObject resultJson = new JSONObject();
			resultJson.put("code", "0");
			resultJson.put("status", "false");
			resultJson.put("message", "获取人员信息失败：" + e.getMessage());
			resultJson.put("data", "");
			logger.error("获取人员信息失败：" + e.getMessage());
			return resultJson.toJSONString();
		}

		JSONObject resultJson = new JSONObject();
		resultJson.put("code", "1");
		resultJson.put("status", "true");
		resultJson.put("message", "获取人员信息成功");
		resultJson.put("data", jsonArray);
		jsonArray.forEach(item -> {
			System.out.println(item);
		});
		logger.error("返回员工信息结果集：" + resultJson);
		return resultJson;
	}

	private String stringDateFormat(Date date) {
		if (date == null) {
			return null;
		}

		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		// 使用format方法将Date对象转换为指定格式的字符串
		return formatter.format(date.getTime());

	}
}