package com.kingdee.shr.customer.gtiit.osf; 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; import java.text.SimpleDateFormat; import java.util.*; import org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger; /** * 同步AD域信息接口 * @author 何朗 * */ public class SynADService implements IHRMsfService { private static Logger logger = Logger.getLogger("com.kingdee.shr.customer.gtiit.osf.SynADService"); 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 personIdSet = new HashSet<>(); try { // 查询调动单审核通过的员工 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 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()); // 构建SQL IN条件 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); } // 构建主查询SQL // 查询非预入职员工信息 StringBuilder sql = new StringBuilder(); sql.append("SELECT DISTINCT ") .append("p.fname_l1 displayName, ") .append("sj.manager AS manager, ") .append("fullp.fname_l1 AS extensionAttribute6, ") .append("res.fleftdate extensionAttribute4, ") .append("cmpt.cfexpireddate AS extensionAttribute10, ") .append("sj.department department, ") .append("sj.title title, ") .append("p.fnumber personnumber, ") .append("p.femail mail, ") .append("p.femail userPrincipalName, ") .append("hpcc.cfprivateemail AS extensionAttribute9, ") .append("p.cfusername sAMAccountName, ") .append("p.cfgivenName givenName, ") .append("p.cfsurname sn, ") .append("CASE WHEN p.fgender = '1' THEN 'Male' ") .append("WHEN p.fgender = '2' THEN 'Female' ") .append("ELSE '' END AS gender, ") .append("CASE WHEN sj.biznumber = 'SHR02' THEN 'Re-employed' ELSE '' END extensionAttribute12, ") .append("ify.fname_l1 extensionAttribute5, ") .append("zt.fname_l1 statusname, ") .append("p.fcell AS extensionAttribute11, ") .append("p.fofficephone AS telephoneNumber, ") .append("la.fenterdate extensionAttribute3, ") .append("nation.fname_l1 extensionAttribute8 ") .append("FROM T_BD_Person p ") .append("LEFT JOIN T_HR_EmployeeClassify ify ON ify.fid = p.cfpersontypeid ") .append("LEFT JOIN T_BD_Nationality nation ON nation.fid = p.fnationalityid ") .append("LEFT JOIN CT_MP_Fullorpart fullp ON fullp.fid = p.cfftorptid ") .append("LEFT JOIN (") .append(" SELECT hpca.fpersonid, hpca.cfprivateemail ") .append(" FROM T_HR_PersonContactMethod hpca ") .append(" INNER JOIN (") .append(" SELECT fpersonid, MAX(flastupdatetime) maxDate ") .append(" FROM T_HR_PersonContactMethod ") .append(" GROUP BY fpersonid") .append(" ) hpcb ON hpca.fpersonid = hpcb.fpersonid AND hpca.flastupdatetime = hpcb.maxDate") .append(") hpcc ON hpcc.fpersonid = p.fid ") .append("LEFT JOIN (") .append(" SELECT ctpa.fpersonid, ctpa.cfexpireddate ") .append(" FROM CT_MP_Pcontractinfo ctpa ") .append(" INNER JOIN (") .append(" SELECT fpersonid, MAX(cfexpireddate) maxDate ") .append(" FROM CT_MP_Pcontractinfo ") .append(" GROUP BY fpersonid") .append(" ) ctpb ON ctpa.fpersonid = ctpb.fpersonid AND ctpa.cfexpireddate = ctpb.maxDate") .append(") cmpt ON cmpt.fpersonid = p.fid ") .append("LEFT JOIN (") .append(" SELECT c.fpersonid fpersonid, d.cfusername manager, ") .append(" post.fname_l1 title, org.fname_l1 department, biz.fnumber bizNumber ") .append(" FROM (") .append(" SELECT a.fpersonid fpersonid, a.cflinemanagernamei cflinemanagernamei, ") .append(" a.fpositionid fpositionid, a.fadminorgid fadminorgid, a.factionid factionid ") .append(" FROM T_HR_EmpOrgRelation a ") .append(" INNER JOIN (") .append(" SELECT MAX(fleffdt) maxDate, fpersonid fpersonid ") .append(" FROM T_HR_EmpOrgRelation ") .append(" WHERE FIsLatestInAday = '1' AND FAssignType = '1' ") .append(" GROUP BY fpersonid") .append(" ) b ON a.fpersonid = b.fpersonid AND a.fleffdt = b.maxDate ") .append(" WHERE a.FIsLatestInAday = '1' AND a.FAssignType = '1'") .append(" ) c ") .append(" LEFT JOIN T_BD_PERSON d ON c.cflinemanagernamei = d.fid ") .append(" LEFT JOIN T_ORG_Position post ON post.fid = c.fpositionid ") .append(" LEFT JOIN T_ORG_Admin org ON org.fid = c.fadminorgid ") .append(" LEFT JOIN T_HR_HRBizDefine biz ON biz.fid = c.factionid") .append(") sj ON sj.fpersonid = p.fid ") .append("LEFT JOIN T_HR_BDEmployeeType zt ON zt.fid = p.femployeetypeid ") .append("LEFT JOIN (") .append(" SELECT a.fpersonid, b.fenterdate, b.FEFFDT, b.fleffdt ") .append(" FROM (") .append(" SELECT FPERSONID, MAX(fenterdate) AS maxDate ") .append(" FROM T_HR_EmpLaborRelationHis ") .append(" GROUP BY fpersonid") .append(" ) a ") .append(" LEFT JOIN T_HR_EmpLaborRelationHis b ON a.fpersonid = b.fpersonid AND a.maxdate = b.fenterdate") .append(") la ON la.fpersonid = p.fid ") .append("LEFT JOIN (") .append(" SELECT hpipa.fleftdate fleftdate, hpipa.fpersonid fpersonid ") .append(" FROM T_HR_PersonPositionHis hpipa ") .append(" INNER JOIN (") .append(" SELECT MAX(fleffdt) maxDate, fpersonid fpersonid ") .append(" FROM T_HR_PersonPositionHis ") .append(" GROUP BY fpersonid") .append(" ) hpipb ON hpipa.fleffdt = hpipb.maxDate AND hpipa.fpersonid = hpipb.fpersonid") .append(") AS res ON res.fpersonid = p.fid ") .append("WHERE (") .append(" p.cfgtiitemail = 'Yes' ") .append(" OR ((p.cfgtiitemail = 'No' OR p.cfgtiitemail IS NULL) AND fullp.fnumber = 'FULL') ") .append(" OR (") .append(" (p.cfgtiitemail = 'No' OR p.cfgtiitemail IS NULL) ") .append(" AND fullp.fnumber = 'PART' ") .append(" AND (sj.title NOT LIKE '%Student Assistant%' OR sj.title LIKE '%ERP%')") .append(" )") //202505 增加.“是否GT邮箱"选项为"No"或为空，且为兼职员工，并且 work mal不包含 @stu.edu.cn 时，该员工信息需同步至AD .append(" OR ( ") .append(" (p.cfgtiitemail = 'No' OR p.cfgtiitemail IS NULL ) ") .append(" AND fullp.fnumber = 'PART' ") .append(" AND ( p.femail NOT LIKE '%@stu.edu.cn%' )") .append(" )") .append(") ") .append("AND p.fid IN (").append(sb.toString()).append(")"); String query = sql.toString().replaceAll("(?i)fname_l2", "fname_l1"); logger.info(String.format("非预入职流程SQL: %s", query)); IRowSet iRowSet = DbUtil.executeQuery(ctx, query); // 处理查询结果 while (iRowSet.next()) { JSONObject jsonObject = new JSONObject(); // 用户名 jsonObject.put("sAMAccountName", iRowSet.getString("sAMAccountName")); // 名 String givenName = iRowSet.getString("givenName"); jsonObject.put("givenName", givenName); // 姓 String sn = iRowSet.getString("sn"); jsonObject.put("sn", sn); // 显示名称 String displayName = iRowSet.getString("displayName"); jsonObject.put("displayName", convert(displayName, givenName, sn)); // 用户名称 jsonObject.put("userPrincipalName", iRowSet.getString("userPrincipalName")); // 邮箱地址 jsonObject.put("mail", iRowSet.getString("mail")); // 办公电话 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 = calendar.getTime(); SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); 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); // 再就业标识 if (StringUtils.isNotBlank(iRowSet.getString("extensionAttribute12"))) jsonObject.put("extensionAttribute12", iRowSet.getString("extensionAttribute12")); jsonArray.add(jsonObject); } // 查询预入职员工信息 StringBuilder sql2 = new StringBuilder(); sql2.append("SELECT ") .append("pe.CFUSERNAME_l1 AS sAMAccountName, ") .append("pe.CFGivenName_l1 AS givenName, ") .append("pe.CFSurname_l1 AS sn, ") .append("pe.FName_l1 AS displayName, ") .append("pe.CFUserName_l1 AS userPrincipalName, ") .append("pe.cfworkemail AS mail, ") .append("pe.femail AS extensionAttribute9, ") .append("pe.FCellPhone AS telephoneNumber, ") .append("pe.FPositionID, ") .append("pe.FAdminOrgUnitID, ") .append("pe.CFLmanagerID, ") .append("pe.CFEmpNumber AS employeeID, ") .append("pe.FPreEnterDate AS extensionAttribute3, ") .append("ec.fname_l1 AS extensionAttribute5, ") .append("pe.CFFtorptID, ") .append("pep.CFGtiitemail, ") .append("post.fname_l1 AS title, ") .append("bu.fname_l1 AS department, ") .append("p.cfusername AS manager, ") .append("ec.FNAME_l1 AS employeeType, ") .append("f.fname_l1 AS extensionAttribute6, ") .append("nation.fname_l1 AS extensionattribute8 ") .append("FROM T_HR_PreEntry pe ") .append("LEFT JOIN T_HR_PreEntryPerson pep ON pep.fid = pe.FTalentID ") .append("LEFT JOIN T_BD_Nationality nation ON pep.fnationalityid = nation.fid ") .append("LEFT JOIN T_ORG_Position post ON pe.FPositionID = post.fid ") .append("LEFT JOIN T_ORG_BaseUnit bu ON bu.fid = pe.FAdminOrgUnitID ") .append("LEFT JOIN T_BD_PERSON p ON p.fid = pe.CFLmanagerID ") .append("LEFT JOIN T_HR_EmployeeClassify ec ON ec.fid = pe.CFPersontypeID ") .append("LEFT JOIN CT_MP_Fullorpart f ON f.fid = pe.CFFtorptID ") .append("WHERE 1=1 ") .append("AND pe.FCheckInState = '4' ") .append("AND pe.FBillState = '3' ") .append("AND (pep.CFGtiitemail = 'Yes' ") .append("OR ((pep.CFGtiitemail = 'No' OR pep.CFGtiitemail IS NULL) AND f.fnumber = 'FULL') ") //202505 增加.“是否GT邮箱"选项为"No"或为空，且为兼职员工，并且 work mal不包含 @stu.edu.cn 时，该员工信息需同步至AD .append("OR ((pep.CFGtiitemail = 'No' OR pep.CFGtiitemail IS NULL) AND f.fnumber = 'PART' AND pe.femail NOT LIKE '%@stu.edu.cn%' ) ") .append("OR ((pep.CFGtiitemail = 'No' OR pep.CFGtiitemail IS NULL) ") .append("AND f.fnumber = 'PART' ") .append("AND (post.fname_l1 NOT LIKE '%Student Assistant%' OR post.fname_l1 LIKE '%ERP%')))"); // 添加查询条件（使用参数化查询防止SQL注入） if (StringUtils.isNotBlank(personNumber)) { sql2.append(" AND pe.CFEmpNumber = ?"); } if (StringUtils.isNotBlank(startDate)) { sql2.append(" AND pe.FLastUpdateTime >= ?"); } if (StringUtils.isNotBlank(endDate)) { sql2.append(" AND pe.FLastUpdateTime <= ?"); } logger.error(String.format("预入职流程SQL: %s", sql2.toString())); // 执行查询（假设DbUtil支持参数化查询） List