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

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;
import com.kingdee.shr.customer.gtiit.osf.util.DateUtils;
import com.kingdee.shr.integration.oa.util.IntegrateOAUtil;
import com.kingdee.shr.integration.oa.util.MappingFieldInfo;
/**
 * 获取人员基本信息
 * @author zkwuh
 *
 */
public class GtiitLocalPersonOsfService implements IHRMsfService{
	
	private static final Logger logger = LoggerFactory.getLogger(GtiitLocalPersonOsfService.class);

	public Object process(Context ctx, Map map) throws EASBizException, BOSException {
		logger.error("GtiitOAGetAdminOrgDataService enter ");
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		// 开始日期
		String startDate = (String) map.get("startDate");
		// 截止日期
		String endDate = (String) map.get("endDate");
		if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
			startDate=DateUtils.formatDate(startDate,true);
			endDate=DateUtils.formatDate(endDate,false);
		}
		logger.error("GtiitOAGetAdminOrgDataService接口参数-开始日期：" + startDate + ",截止日期：" + endDate + ",人员：" );
		List<MappingFieldInfo> fieldList = this.initMappingField();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT\r\n" + 
				"	p.fid SHRID,\r\n" + 
				"	p.fnumber PERSON_NUMBER,\r\n" + 
				"	p.cfsurname LAST_NAME,\r\n" + 
				"	p.cfmiddlenames MIDDLE_NAMES,\r\n" + 
				"	p.cfgivenname FIRST_NAME,\r\n" + 
				"	p.cflocalname LOCAL_NAME,\r\n" + 
				"	p.fname_l1 DISPLAY_NAME,\r\n" + 
				"	w.fname_l1 USER_PERSON_TYPE,\r\n" + 
				"	fu.fname_l1 FULL_PART_TIME,\r\n" + 
				"	p.fhiredate HIRE_DATE,\r\n" + 
				"	e.ftryoutmonth PROBATION_PERIOD,\r\n" + 
				"	'Months' PROBATION_UNIT,\r\n" + 
				"	e.fplanformaldate DATE_PROBATION_END,\r\n" + 
				"	hp.fofficephone WORK_PHONE,\r\n" + 
				"	hp.fmobile CELL_PHONE,\r\n" + 
				"	hp.femail EMAIL_ADDRESS,\r\n" + 
				"	hp.cfprivateemail S_EMAIL_ADDRESS,\r\n" + 
				"	n.fname_l1 CITIZENSHIP,\r\n" + 
				"CASE\r\n" + 
				"		\r\n" + 
				"		WHEN p.fgender= '1' THEN\r\n" + 
				"		'男' \r\n" + 
				"		WHEN p.fgender= '2' THEN\r\n" + 
				"		'女' ELSE '' \r\n" + 
				"	END AS GENDER,\r\n" + 
				"	p.fbirthday DATE_OF_BIRTH,\r\n" + 
				"	hf.fname_l1 ETHNICITY,\r\n" + 
				"	he.fname_l1 HUKOU_TYPE,\r\n" + 
				"	hl.fname_l1 POLITICAL_STATUS,\r\n" + 
				"	hs.fleftdate TER_DATE,\r\n" + 
				"	hre.fname_l1 REASON,\r\n" + 
				"	htc.fname_l1 PROFESSIONAL_TITLE,\r\n" + 
				"	hj.fname_l1 JOB_LEVEL,\r\n" + 
				"	p.cfusername USER_NAME,\r\n" + 
				"	hpc.cfcommencingdate CONTRACT_START_DATE,\r\n" + 
				"	hpc.cfexpireddate CONTRACT_END_DATE,\r\n" + 
				"	p.fcreatetime ERP_CREATION_DATE,\r\n" + 
				"	p.flastupdatetime ERP_LAST_UPDATE_DATE \r\n" + 
				"FROM\r\n" + 
				"	T_BD_Person p\r\n" + 
				"	LEFT JOIN CT_MP_WorkerCategory w ON w.fid= p.cfworkercategoryid\r\n" + 
				"	LEFT JOIN CT_MP_FULLORPART fu ON fu.fid= p.cfftorptid\r\n" + 
				"	LEFT JOIN (\r\n" + 
				"	SELECT\r\n" + 
				"		ea.fpersonid ,\r\n" + 
				"		ea.ftryoutmonth ,\r\n" + 
				"		ea.fplanformaldate \r\n" + 
				"	FROM\r\n" + 
				"		T_HR_EmpLaborRelationHis ea\r\n" + 
				"		INNER JOIN ( SELECT fpersonid, MAX ( fenterdate ) maxDate FROM T_HR_EmpLaborRelationHis GROUP BY fpersonid ) eb ON ea.fpersonid= eb.fpersonid \r\n" + 
				"		AND ea.fenterdate= eb.maxDate \r\n" + 
				"	) e ON p.fid= e.fpersonid\r\n" + 
				"	LEFT JOIN (\r\n" + 
				"	SELECT\r\n" + 
				"		hpa.fpersonid,\r\n" + 
				"		hpa.fofficephone,\r\n" + 
				"		hpa.fmobile,\r\n" + 
				"		hpa.femail,\r\n" + 
				"		hpa.cfprivateemail \r\n" + 
				"	FROM\r\n" + 
				"		T_HR_PersonContactMethod hpa\r\n" + 
				"		INNER JOIN ( SELECT fpersonid, MAX ( flastupdatetime ) maxDate FROM T_HR_PersonContactMethod GROUP BY fpersonid ) hpb ON hpa.fpersonid= hpb.fpersonid \r\n" + 
				"		AND hpb.maxDate= hpa.flastupdatetime \r\n" + 
				"	) hp ON hp.fpersonid= p.fid\r\n" + 
				"	LEFT JOIN T_BD_Nationality n ON n.fid= p.fnationalityid\r\n" + 
				"	LEFT JOIN T_BD_HRFolk hf ON hf.fid= p.ffolkid\r\n" + 
				"	LEFT JOIN T_BD_Regpermresidence he ON he.fid= p.fregresidenceid\r\n" + 
				"	LEFT JOIN T_BD_HRPolitical hl ON hl.fid= p.fpoliticalfaceid\r\n" + 
				"	LEFT JOIN (\r\n" + 
				"	SELECT\r\n" + 
				"		hsa.fleftdate,\r\n" + 
				"		hsa.fdimissionreasonid,\r\n" + 
				"		hsa.fpersonid \r\n" + 
				"	FROM\r\n" + 
				"		T_HR_PersonPositionHis hsa\r\n" + 
				"		INNER JOIN ( SELECT fpersonid, MAX ( fjoingroupdatecur ) maxDate FROM T_HR_PersonPositionHis GROUP BY fpersonid ) hsb ON hsa.fpersonid= hsb.fpersonid \r\n" + 
				"		AND hsa.fjoingroupdatecur= hsb.maxDate \r\n" + 
				"	) hs ON hs.fpersonid= p.fid\r\n" + 
				"	LEFT JOIN T_HR_BDDimissionReason hre ON hre.fid= hs.fdimissionreasonid\r\n" + 
				"	LEFT JOIN (\r\n" + 
				"	SELECT\r\n" + 
				"		hta.ftechnicalpostid,\r\n" + 
				"		hta.fpersonid \r\n" + 
				"	FROM\r\n" + 
				"		T_HR_PersonTechPost hta\r\n" + 
				"		INNER JOIN ( SELECT fpersonid, MAX ( fconferdate ) maxDate FROM T_HR_PersonTechPost GROUP BY fpersonid ) htb ON hta.fpersonid= htb.fpersonid \r\n" + 
				"		AND hta.fconferdate= htb.maxDate \r\n" + 
				"	) ht ON ht.fpersonid= p.fid\r\n" + 
				"	LEFT JOIN T_HR_BDTechnicalPost htc ON htc.fid= ht.ftechnicalpostid\r\n" + 
				"	LEFT JOIN (\r\n" + 
				"	SELECT\r\n" + 
				"		hera.fpersonid,\r\n" + 
				"		hera.fjobgradeid \r\n" + 
				"	FROM\r\n" + 
				"		T_HR_EmpPostRank hera\r\n" + 
				"		INNER JOIN ( SELECT fpersonid, MAX ( fleffdt ) maxDate FROM T_HR_EmpPostRank GROUP BY fpersonid ) herb ON hera.fpersonid= herb.fpersonid \r\n" + 
				"		AND hera.fleffdt= herb.maxDate \r\n" + 
				"	) her ON her.fpersonid= p.fid\r\n" + 
				"	LEFT JOIN T_HR_JobGrade hj ON hj.fid= her.fjobgradeid\r\n" + 
				"	LEFT JOIN (\r\n" + 
				"	SELECT\r\n" + 
				"		hpca.fpersonid,\r\n" + 
				"		hpca.cfcommencingdate,\r\n" + 
				"		hpca.cfexpireddate \r\n" + 
				"	FROM\r\n" + 
				"		CT_MP_Pcontractinfo hpca\r\n" + 
				"		INNER JOIN ( SELECT fpersonid, MAX ( cfexpireddate ) maxDate,max(flastupdatetime) lastDate,max(cfcommencingdate) startDate FROM CT_MP_Pcontractinfo GROUP BY fpersonid ) hpcb ON hpca.fpersonid= hpcb.fpersonid \r\n" + 
				"	AND hpca.cfexpireddate= hpcb.maxDate and hpca.flastupdatetime=hpcb.lastDate and hpca.cfcommencingdate=hpcb.startDate\r\n" + 
				"	) hpc ON hpc.fpersonid= p.fid ");
		
		if(StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) {
			sql.append("  where p.flastupdatetime >= '"+startDate+"' and p.flastupdatetime <= '"+endDate+"'");
		}
		sql.append(" order by PERSON_NUMBER desc");
		logger.info("GtiitLocalPersonOsfService人员查询SQL"+sql.toString());
		IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
		List<HashMap<String, String>> dataList = IntegrateOAUtil.getRsListData(fieldList, rs);
		return dataList;
	}

	private List<MappingFieldInfo> initMappingField() {
		List<MappingFieldInfo> list = new ArrayList();
		MappingFieldInfo fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("PERSON_NUMBER");
		fieldInfo.setShrField("PERSON_NUMBER");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("SHRID");
		fieldInfo.setShrField("SHRID");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("LAST_NAME");
		fieldInfo.setShrField("LAST_NAME");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("MIDDLE_NAMES");
		fieldInfo.setShrField("MIDDLE_NAMES");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("FIRST_NAME");
		fieldInfo.setShrField("FIRST_NAME");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("LOCAL_NAME");
		fieldInfo.setShrField("LOCAL_NAME");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("DISPLAY_NAME");
		fieldInfo.setShrField("DISPLAY_NAME");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("USER_PERSON_TYPE");
		fieldInfo.setShrField("USER_PERSON_TYPE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("FULL_PART_TIME");
		fieldInfo.setShrField("FULL_PART_TIME");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("HIRE_DATE");
		fieldInfo.setShrField("HIRE_DATE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("PROBATION_PERIOD");
		fieldInfo.setShrField("PROBATION_PERIOD");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("PROBATION_UNIT");
		fieldInfo.setShrField("PROBATION_UNIT");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("DATE_PROBATION_END");
		fieldInfo.setShrField("DATE_PROBATION_END");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("WORK_PHONE");
		fieldInfo.setShrField("WORK_PHONE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("CELL_PHONE");
		fieldInfo.setShrField("CELL_PHONE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("EMAIL_ADDRESS");
		fieldInfo.setShrField("EMAIL_ADDRESS");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("S_EMAIL_ADDRESS");
		fieldInfo.setShrField("S_EMAIL_ADDRESS");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("CITIZENSHIP");
		fieldInfo.setShrField("CITIZENSHIP");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("GENDER");
		fieldInfo.setShrField("GENDER");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("DATE_OF_BIRTH");
		fieldInfo.setShrField("DATE_OF_BIRTH");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("ETHNICITY");
		fieldInfo.setShrField("ETHNICITY");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("HUKOU_TYPE");
		fieldInfo.setShrField("HUKOU_TYPE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("POLITICAL_STATUS");
		fieldInfo.setShrField("POLITICAL_STATUS");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("TER_DATE");
		fieldInfo.setShrField("TER_DATE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("REASON");
		fieldInfo.setShrField("REASON");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("PROFESSIONAL_TITLE");
		fieldInfo.setShrField("PROFESSIONAL_TITLE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("JOB_LEVEL");
		fieldInfo.setShrField("JOB_LEVEL");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("USER_NAME");
		fieldInfo.setShrField("USER_NAME");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("CONTRACT_START_DATE");
		fieldInfo.setShrField("CONTRACT_START_DATE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("CONTRACT_END_DATE");
		fieldInfo.setShrField("CONTRACT_END_DATE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("ERP_CREATION_DATE");
		fieldInfo.setShrField("ERP_CREATION_DATE");
		list.add(fieldInfo);
		fieldInfo = new MappingFieldInfo();
		fieldInfo.setOaField("ERP_LAST_UPDATE_DATE");
		fieldInfo.setShrField("ERP_LAST_UPDATE_DATE");
		list.add(fieldInfo);
		return list;
	}
	
	
}