guangzhouyiselie/GDYSL/websrc/com/kingdee/shr/customer/gtiit/osf/SynPersonService.java

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;

/**
 * 同步人员信息接口
 * 
 * @author xiaoxin
 *
 */
public class SynPersonService implements IHRMsfService {

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

	@Override
	public Object process(Context ctx, Map map) throws EASBizException, BOSException {
		logger.error("进入获取员工信息接口");
		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("\r\n" + 
					"SELECT p.FEFFDT,p.fnumber personnumber,p.fname_l2 personname,p.femail email,fu.fname_l2  fullorpartName,  p.fcell cell,p.fofficephone officephone, case when p.fgender='1' then '男' when p.fgender='2' then '女' else '' end as gender\r\n" + 
					",emp.departmentname ,emp.positionname ,emp.jobname ,emp.CFOalevel as seclevel , emp.jobgradename\r\n" + 
					",ify.fname_l2 persontypename\r\n" + 
					",emp.fnumber  personparent ,emp.workercategory  as Jobactivitydesc\r\n" + 
					",zt.statusname\r\n" + 
					",la.fenterdate enterdate,la.FPlanFormalDate   AS probationenddate \r\n" + 
					",us.fpassword password,us.fnumber usnumber\r\n" + 
					",ht.maxstartDate as startdate ,ht.maxenddate as enddate\r\n" + 
					"FROM T_BD_Person p \r\n" + 
					" left join CT_MP_FULLORPART fu on fu.fid=p.cfftorptid \r\n" + 
					"left join T_HR_EmployeeClassify ify on ify.fid = p.cfpersontypeid \r\n" + 
					"   left join (select re.FPERSONID,re.maxDate,p.fname_l2 name,p.fnumber  ,w.fname_l2 workercategory , rl.FAdminOrgID ,bu.fnumber  departmentname,\r\n" + 
					"       post.fnumber positionname  ,job.fname_l2 jobname ,job2.CFOalevel ,gra.fname_l2 jobgradename   from\r\n" + 
					"          (SELECT FPERSONID,max(FEFFDT) as maxDate FROM T_HR_EmpOrgRelation where  fisLatestInAday ='1' and  fassignType ='1' group by  FPERSONID  ) as re    \r\n" + 
					"           left join   T_HR_EmpOrgRelation   rl on re.fpersonid = rl.fpersonid and re.maxDate = rl.feffdt \r\n" + 
					"	  left join   CT_MP_WorkerCategory w    on w.fid =rl.CFWorkercategoryID\r\n" + 
					"           left join T_BD_Person p on p.fid=rl.cflinemanagernameI    \r\n" + 
					"           left join   T_ORG_BaseUnit bu on bu.fid=rl.FAdminOrgID\r\n" + 
					"           left join T_ORG_Position post on rl.fpositionid = post.fid\r\n" + 
					"   	  left join T_ORG_Job job on job.fid = post.fjobid    \r\n" + 
					"           left join   T_HR_HRJob  job2  on  job2.FJobID =job.fid\r\n" + 
					"	  left join (SELECT * FROM T_HR_EmpPostRank where fislatest='1') r on r.fpersonid = rl.fpersonid    \r\n" + 
					"	  left join T_HR_JobGrade gra on gra.fid=r.fjobgradeid \r\n" + 
					"           where  rl.fisLatestInAday ='1' and  rl.fassignType ='1' ) emp on emp.fpersonid=p.fid  \r\n" + 
					"   left join  ( SELECT fname_l2 statusname ,fid FROM  T_HR_BDEmployeeType ) zt on zt.fid=p.femployeetypeid\r\n" + 
					"   left join T_HR_EmpLaborRelation la on  la.fpersonid=p.fid \r\n" + 
					"   left join T_PM_USER us on us.FPERSONID=p.fid\r\n" + 
					"left join  (\r\n" + 
					"    SELECt pco.fpersonid  ,pco.maxstartDate,max(pco.CFExpiredDate)  as maxenddate FROM    (\r\n" + 
					"	SELECT pco.fpersonid ,pco2.maxstartDate,pco.CFExpiredDate FROM   CT_MP_Pcontractinfo  pco\r\n" + 
					"         inner join (SELECT  fpersonid , max(CFCommencingDate) maxstartDate  from  CT_MP_Pcontractinfo  group  by fpersonid ) as pco2 on pco.fpersonid = pco.fpersonid and  pco.CFCommencingDate = pco2.maxstartDate ) as pco\r\n" + 
					"    group by pco.fpersonid  ,pco.maxstartDate\r\n" + 
					") ht  on  ht.fpersonid =p.fid\r\n ");
			// sql.append(
			// "SELECT sj.workercategory, emp.departmentname,emp.positionname,p.fnumber
			// personnumber,p.fname_l2 personname,p.femail email,");
			// sql.append(
			// "case when p.fgender='1' then '男' when p.fgender='2' then '女' else '' end as
			// gender,ify.fname_l2 persontypename,");
			// sql.append(
			// "sj.fnumber personparent,zt.fname_l2 statusname,p.fcell cell,p.fofficephone
			// officephone,la.fenterdate enterdate,");
			// sql.append(
			// "la.factualFormalDate actualFormalDate,la.FEFFDT EFFDT,la.fleffdt
			// leffdt,us.fpassword password,emp.jobname,emp.jobgradename FROM T_BD_Person p
			// ");
			// sql.append(
			// "left join (select e.fpersonid personid,a.fname_l2 departmentname,g.fname_l2
			// positionname,job.fname_l2 jobname,gra.fname_l2 jobgradename ");
			// sql.append(
			// "from (SELECT FPERSONID,max(FEFFDT) as maxDate FROM T_HR_EmpPostExperienceHis
			// group by FPERSONID) as h ");
			// sql.append(
			// " left join T_HR_EmpPostExperienceHis e on e.fpersonid= h.FPERSONID and
			// e.feffdt = h.maxDate ");
			// sql.append(" left join T_ORG_Admin a on a.fid=e.fadminorgid ");
			// sql.append(" left join T_ORG_Position g on g.fid=e.fpositionid ");
			// sql.append(" left join T_ORG_Job job on job.fid = g.fjobid ");
			// sql.append(
			// " left join (SELECT * FROM T_HR_EmpPostRank where fislatest='1') r on
			// r.fpersonid = e.fpersonid ");
			// sql.append(" left join T_HR_JobGrade gra on gra.fid=r.fjobgradeid ) emp on
			// p.fid=emp.personid ");
			// sql.append("left join T_HR_EmployeeClassify ify on ify.fid = p.cfpersontypeid
			// ");
			// sql.append(
			// "left join (select re.FPERSONID,p.fname_l2 name,p.fnumber ,w.fname_l2
			// workercategory from (SELECT FPERSONID,max(FEFFDT) as maxDate FROM
			// T_HR_EmpOrgRelation "
			// + " where fisLatestInAday ='1' and fassignType ='1' "
			// + " group by FPERSONID) as re ");
			// sql.append(
			// " left join T_HR_EmpOrgRelation rl on re.fpersonid = rl.fpersonid and
			// re.maxDate = rl.feffdt ");
			// sql.append(
			// " left join CT_MP_WorkerCategory w on w.fid =rl.CFWorkercategoryID ");
			// sql.append(" left join T_BD_Person p on p.fid=rl.cflinemanagernameI) sj on
			// sj.fpersonid=p.fid ");
			// sql.append("left join T_HR_BDEmployeeType zt on zt.fid=p.femployeetypeid ");
			// sql.append(
			// "left join (select
			// a.fpersonid,b.fenterdate,b.factualFormalDate,b.FEFFDT,b.fleffdt from (SELECT
			// FPERSONID,max(FEFFDT) as maxDate FROM T_HR_EmpLaborRelationHis group by
			// fpersonid) a ");
			// sql.append(
			// " left join T_HR_EmpLaborRelationHis b on a.fpersonid=b.fpersonid and
			// a.maxdate=b.FEFFDT) la on la.fpersonid=p.fid ");
			// sql.append("left join T_PM_USER us on us.FPERSONID=p.fid ");
			sql.append("where p.fid in (" + sb.toString() + ")");
			String str = sql.toString();
			str = str.replaceAll("(?i)fname_l2", "fname_l1");
			logger.info("人员查询SQL"+str);
			IRowSet iRowSet = DbUtil.executeQuery(ctx, str);
			Calendar calendar = Calendar.getInstance();

			while (iRowSet.next()) {
				JSONObject jsonObject = new JSONObject();
				// 员工工号
				jsonObject.put("workcode", iRowSet.getString("personnumber"));
				// 姓名
				jsonObject.put("lastname", iRowSet.getString("personname"));
				// 登录名
				jsonObject.put("loginid", iRowSet.getString("usnumber"));
				// 密码
				jsonObject.put("password", iRowSet.getString("password"));
				// 性别
				jsonObject.put("sex", iRowSet.getString("gender"));
				// 安全级别
				jsonObject.put("seclevel", iRowSet.getString("seclevel"));
				// 部门
				 //String s="\\";
			    //格式 {JSON}{\"departmentcode\":\"code\"}
				jsonObject.put("department", "{JSON}{\"departmentcode\":\""+iRowSet.getString("departmentname")+"\"}");
				// 岗位
				jsonObject.put("jobtitle", "{JSON}{\"jobtitlecode\":\""+iRowSet.getString("positionname")+"\"}");
				// 职务
				jsonObject.put("jobactivityid", iRowSet.getString("jobname"));
				//兼职全职
				jsonObject.put("usekind", "Full Time".equals(iRowSet.getString("fullorpartName"))?"正式员工":"兼职员工");
				// 职级
				if (StringUtils.isNotBlank(iRowSet.getString("jobgradename"))) {
					jsonObject.put("joblevel", iRowSet.getString("jobgradename").replace("L", ""));
				} else {
					jsonObject.put("joblevel", iRowSet.getString("jobgradename"));
				}
				// 员工类别
				jsonObject.put("jobactivitydesc", iRowSet.getString("Jobactivitydesc"));
				// 直接上级                
				jsonObject.put("managerid", iRowSet.getString("personparent"));
				// 状态
				jsonObject.put("status", iRowSet.getString("statusname"));
				// 办公室
				jsonObject.put("workroom", "");
				// 办公室电话
				jsonObject.put("telephone", StringUtils.isBlank(iRowSet.getString("officephone"))?"":iRowSet.getString("officephone"));
				// 移动电话
				jsonObject.put("mobile", StringUtils.isBlank(iRowSet.getString("cell"))?"":iRowSet.getString("cell"));
				// 电子邮件
				jsonObject.put("email", iRowSet.getString("email"));
				// 入职日期
				jsonObject.put("companystartdate", stringDateFormat(iRowSet.getDate("enterdate")));

				// 试用期结束日期
				Date date = iRowSet.getDate("probationenddate");
				if (date == null) {
					// 无试用日期 去入职日期
					jsonObject.put("probationenddate", stringDateFormat(iRowSet.getDate("enterdate")));
				} else {
					calendar.setTime(date);
					calendar.add(Calendar.DATE, -1);
					date = calendar.getTime();
					// 试用期结束日期 = 预计转正日期 -1天
					jsonObject.put("probationenddate", stringDateFormat(date));
				}
				// 合同开始日期
				jsonObject.put("startdate", stringDateFormat(iRowSet.getDate("startdate")));
				// 合同结束日期
				jsonObject.put("enddate", stringDateFormat(iRowSet.getDate("enddate")));
				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);
		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());

	}
}