package com.kingdee.shr.customer.gtiit.osf; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.apache.commons.lang3.StringUtils; 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; /** * 获取员工任职历史记录的接口 * @author cb * */ public class GtiitEmpOrgRelationService implements IHRMsfService { private static final Logger logger = LoggerFactory.getLogger(GtiitOrgPositionService.class); @Override public Object process(Context ctx, Map param) throws EASBizException, BOSException { // 开始日期 String startDate = (String) param.get("startDate"); // 截止日期 String endDate = (String) param.get("endDate"); String sql="SELECT\r\n" + " he.fid ID,\r\n" + " bp.fnumber PERSON_NUMBER,\r\n" + " bp.cfsurname LAST_NAME,\r\n" + " bp.cfmiddlenames MIDDLE_NAMES,\r\n" + " bp.CFGivenName FIRST_NAME,\r\n" + " bp.cflocalname LOCAL_NAME,\r\n" + " bp.fname_l2 DISPLAY_NAME,\r\n" + " he.fstartdatetime EFFECTIVE_START_DATE,\r\n" + " he.fenddatetime EFFECTIVE_END_DATE,\r\n" + " oa.FNUMBER DEPARTMENT_CODE,\r\n" + " oa.FNAME_l2 DEPARTMENT_NAME,\r\n" + " oa.FSIMPLENAME DEPARTMENT_SHORT_NAME,\r\n" + " he.fjobtxt JOB_NAME,\r\n" + " op.FNAME_l1 POSITION,\r\n" + " op.FNUMBER POSITION_CODE,\r\n" + " wc.fname_l1 WORK_CATE,\r\n" + " mf.fname_l1 FULL_PART_TIME,\r\n" + " ma.Fnumber MANAGER_PERSON_NUMBER,\r\n" + " ma.Fname_l2 MANAGER_NAME,\r\n" + " he.cfadmintitle ADMIN_TITLE,\r\n" + " mat.fname_l1 ACADEMIC_TITLE,\r\n" + " hj.fname_l1 JOB2,\r\n" + " xyz.name JOB_LEVEL,\r\n" + " he.fassigntype PRIMARY_FLAG,\r\n" + "CASE\r\n" + " \r\n" + " WHEN helr.FLABORRELATIONSTATEID IN (\r\n" + " SELECT\r\n" + " fid \r\n" + " FROM\r\n" + " T_HR_BDEmployeeType \r\n" + " WHERE\r\n" + " FNUMBER NOT IN ( 'S08', 'S09', '009', '010', '011' )) THEN\r\n" + " 1 ELSE 0 \r\n" + " END AS ASSIGNMENT_STATUS_TYPE,\r\n" + " he.FCREATETIME ERP_CREATION_DATE,\r\n" + " he.FLASTUPDATETIME ERP_LAST_UPDATE_DATE \r\n" + " FROM\r\n" + " T_HR_EmpOrgRelation he\r\n" + " LEFT JOIN t_bd_person bp ON bp.fid = he.fpersonid\r\n" + " LEFT JOIN T_ORG_Admin oa ON oa.fid = he.fadminorgid\r\n" + " LEFT JOIN T_ORG_Position op ON op.FID = he.fpositionid\r\n" + " LEFT JOIN CT_MP_WorkerCategory wc ON wc.fid = he.cfworkercategoryid\r\n" + " LEFT JOIN CT_MP_Fullorpart mf ON mf.fid = he.cfftorptid\r\n" + " LEFT JOIN T_BD_Person ma ON ma.fid = he.cflinemanagernamei\r\n" + " LEFT JOIN CT_MP_AcademicTitle mat ON mat.fid = he.cfacademictitleid\r\n" + " LEFT JOIN CT_HR_Job2 hj ON hj.fid = he.cfjobsid\r\n" + " LEFT JOIN T_HR_EmpLaborRelation helr ON helr.fpersonid = he.fpersonid\r\n" + " LEFT JOIN (\r\n" + " SELECT\r\n" + " hjg.fname_l2 name,\r\n" + " a.fid fpersonid \r\n" + " FROM\r\n" + " T_HR_JobGrade hjg\r\n" + " LEFT JOIN (\r\n" + " SELECT\r\n" + " * \r\n" + " FROM\r\n" + " T_HR_EmpPostRank \r\n" + " WHERE\r\n" + " FLEFFDT = ( SELECT MAX ( FLEFFDT ) FROM T_HR_EmpPostRank )) a ON hjg.FID = a.FJOBGRADEID \r\n" + " ) xyz ON xyz.fpersonid = he.fpersonid"; if (StringUtils.isNotBlank(startDate) && StringUtils.isNotBlank(endDate)) { startDate = DateUtils.formatDate(startDate, true); endDate = DateUtils.formatDate(endDate, false); sql += " where he.FLASTUPDATETIME >= '" + startDate + "' and he.FLASTUPDATETIME <= '" + endDate + "'"; } List fieldList = initMappingField(); logger.info(">>> GtiitEmpOrgRelationService 的执行 sql = " + sql); IRowSet rs = DbUtil.executeQuery(ctx, sql.toString()); List> dataList = this.getRsListData(fieldList, rs); return dataList; } private List initMappingField() { List list = new ArrayList<>(); list.add("ID"); list.add("PERSON_NUMBER"); list.add("LAST_NAME"); list.add("MIDDLE_NAMES"); list.add("FIRST_NAME"); list.add("LOCAL_NAME"); list.add("DISPLAY_NAME"); list.add("EFFECTIVE_START_DATE"); list.add("EFFECTIVE_END_DATE"); list.add("DEPARTMENT_CODE"); list.add("DEPARTMENT_NAME"); list.add("DEPARTMENT_SHORT_NAME"); list.add("JOB_NAME"); list.add("POSITION"); list.add("POSITION_CODE"); list.add("WORK_CATE"); list.add("FULL_PART_TIME"); list.add("MANAGER_PERSON_NUMBER"); list.add("MANAGER_NAME"); list.add("ADMIN_TITLE"); list.add("ACADEMIC_TITLE"); list.add("JOB2"); list.add("JOB_LEVEL"); list.add("PRIMARY_FLAG"); list.add("ASSIGNMENT_STATUS_TYPE"); list.add("ERP_CREATION_DATE"); list.add("ERP_LAST_UPDATE_DATE"); return list; } private List> getRsListData(List fieldList, IRowSet rs) throws BOSException { List> dataList = new ArrayList<>(); try { while (rs.next()) { HashMap dataMap = new HashMap<>(); int i = 0; for (int size = fieldList.size(); i < size; ++i) { String field = fieldList.get(i); if ("PRIMARY_FLAG".equals(field)) { dataMap.put(field, "1".equals(rs.getString(field)) ? "是" : "否"); }else if("ASSIGNMENT_STATUS_TYPE".equals(field)){ dataMap.put(field, "1".equals(rs.getString(field)) ? "ACTIVE" : "INACTIVE"); } else { dataMap.put(field, rs.getString(field)); } } dataList.add(dataMap); } return dataList; } catch (SQLException ex) { throw new BOSException(ex); } } }