package nckd.jimin.jyyy.hr.hrmp.hbpm.task;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import kd.bos.algo.DataSet;
import kd.bos.algo.Row;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.dataentity.entity.DynamicObjectCollection;
import kd.bos.db.DB;
import kd.bos.db.DBRoute;
import kd.bos.logging.Log;
import kd.bos.logging.LogFactory;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import kd.bos.servicehelper.QueryServiceHelper;
import kd.bos.servicehelper.operation.SaveServiceHelper;
import kd.bos.util.HttpClientUtils;
import kd.hr.hbp.business.servicehelper.HRBaseServiceHelper;
import kd.hrmp.hrpi.business.infrastructure.utils.QFilterUtil;
import nckd.jimin.jyyy.hr.tsrsc.plugin.util.MokaApiUtil;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class SyncUtil {
    private static Log logger = LogFactory.getLog(SyncUtil.class);
    private static String KEY_ENTITY_LOG = "nckd_syncmdmlog";
    public static String v_success = "A";
    public static String v_partsuccess = "B";
    public static String v_error = "C";
    public static String SyncOrg = "组织同步";
    public static String SyncOrg_Person = "组织+人员同步";
    public static String SyncOrg_Position = "组织+岗位同步";
    public static String SyncPerson = "人员同步";
    public static String SyncPosition = "岗位同步";
    public static String SyncPersonMoka = "Moka面试官同步";


    /**
     * 获取调用MDM的地址，取nckd_commonparams中编码=MDM的参数
     * @param type
     * @return
     */
    public static String getUrl(String type) {
        QFilter filter = new QFilter("number", QCP.equals, "MDM");
        filter.and("nckd_entryentity.nckd_key",QCP.equals, type);
        DynamicObject bill = QueryServiceHelper.queryOne("nckd_commonparams", "nckd_entryentity.nckd_value", new QFilter[]{filter});
        logger.info("-------- 获取MDM url：" + bill.getString("nckd_entryentity.nckd_value") + " -------- ");
        return bill.getString("nckd_entryentity.nckd_value");
    }

    /**
     * 初始化组织Id和长编码的关系
     *
     * @return
     */
    public static Map<Long, DynamicObject> initOrgInfo() {
        String selectFields = "id,structure.vieworg.id,structure.longnumber,structure.fullname,createtime";
        QFilter filter = new QFilter("structure.view.id",QCP.equals,1L);
        DynamicObjectCollection orgs = QueryServiceHelper.query("bos_org", selectFields, new QFilter[]{filter});

        Map<Long, DynamicObject> orgMap = (Map)orgs.stream().collect(Collectors.toMap((org) -> {
            return org.getLong("id");
        }, (org) -> {
            return org;
        }, (key1, key2) -> {
            return key2;
        }));

        return orgMap;
    }

    public static Map<Long, DynamicObject> initOrgInfo(Map orgMap) {
        String selectFields = "id,structure.vieworg.id,structure.longnumber,structure.fullname,createtime";
        QFilter filter = new QFilter("structure.view.id",QCP.equals,1L);
        filter.and("id",QCP.in,orgMap.keySet());
        DynamicObjectCollection orgs = QueryServiceHelper.query("bos_org", selectFields, new QFilter[]{filter});

        Map<Long, DynamicObject> orgDyxMap = (Map)orgs.stream().collect(Collectors.toMap((org) -> {
            return org.getLong("id");
        }, (org) -> {
            return org;
        }, (key1, key2) -> {
            return key2;
        }));

        return orgDyxMap;
    }

    /**
     * 岗位树形结构，为了解决level字段问题
     *
     * @return
     */
    public static Map<Long, Integer> initPositionTree() {
        String selectProperties = "id,parent.id,name,iscurrentversion";
        QFilter filter = new QFilter("iscurrentversion", QCP.equals, "1");
        DynamicObjectCollection posDyArr = new HRBaseServiceHelper("homs_positionhis").queryOriginalCollection(selectProperties, new QFilter[]{filter});
        List<PositionTree> list = new ArrayList<PositionTree>();
        for (DynamicObject pos : posDyArr) {
            PositionTree pTree = new PositionTree(pos.getLong("id"), pos.getString("name"), pos.getLong("parent.id"));
            list.add(pTree);
        }
        //构造树形结构，带level
        List<PositionTree> tree = buildTree(list);
        //处理实现结构，返回一个id-level的Map
        Map<Long, Integer> levelMap = convertToIdLevelMap(tree);
        return levelMap;
    }

    public static Map<Long, Integer> convertToIdLevelMap(List<PositionTree> roots) {
        Map<Long, Integer> idLevelMap = new HashMap<>();
        for(PositionTree root : roots) {
            traverseTree(root, idLevelMap);
        }
        return idLevelMap;
    }

    private static void traverseTree(PositionTree node, Map<Long, Integer> map) {
        map.put(node.getId(), node.getLevel());
        for (PositionTree child : node.getChildren()) {
            traverseTree(child, map); // 递归处理子节点‌:ml-citation{ref="1,3" data="citationList"}
        }
    }

    // 递归打印树
    private static void printTree(PositionTree node, int indent) {
        String spaces = String.join("", Collections.nCopies(indent, "  "));
        System.out.println(spaces + "└─ " + node.getLabel());
        for (PositionTree child : node.getChildren()) {
            printTree(child, indent + 1);
        }
    }

    /**
     * 构建完整树形结构
     * @param nodes 所有平铺节点
     * @return 根节点列表（支持多根）
     */
    public static List<PositionTree> buildTree(List<PositionTree> nodes) {
        // 1. 创建快速查找的哈希表
        Map<Long, PositionTree> nodeMap = nodes.stream()
                .collect(Collectors.toMap(PositionTree::getId, node -> node));

        // 2. 构建父子关系
        List<PositionTree> roots = new ArrayList<>();
        for (PositionTree node : nodes) {
            Long parentId = node.getParentId();
            if (parentId == null || parentId == 0L) {
                roots.add(node); // 根节点
            } else {
                PositionTree parent = nodeMap.get(parentId);
                if (parent != null) {
                    parent.getChildren().add(node);
                } else {
                    // 处理孤儿节点（可选）
                    roots.add(node); // 将无父节点的视为根节点
                }
            }
        }

        // 3. 计算层级（可选扩展）
        calculateLevels(roots, 0);

        return roots;
    }

    /** 递归计算层级深度 */
    private static void calculateLevels(List<PositionTree> nodes, int level) {
        for (PositionTree node : nodes) {
            node.setLevel(level);
            calculateLevels(node.getChildren(), level + 1);
        }
    }

    /**
     * JSONArray排序，按照级次排序，避免下级数据新增的时候上级还没有
     * @param arr
     * @param fieldName
     */
    public static void setJSONArraySorted(JSONArray arr, String fieldName) {
        arr.sort((a, b) -> {
            JSONObject objA = (JSONObject) a;
            JSONObject objB = (JSONObject) b;
            return objA.getIntValue(fieldName) - objB.getIntValue(fieldName);
        });
    }

    /**
     * 分批调用MDM接口
     * @param url 接口地址
     * @param bodyData 入参
     * @param type 同步类型
     * @param startTime 变动开始时间
     * @param endTime 变动结束时间
     * @throws IOException
     */
    public static void batchPost(String url, JSONObject bodyData, String type, String startTime, String endTime) throws IOException {
        int size = getBatchSize();
        JSONArray oldArr = bodyData.getJSONArray("obj");
        int totalSize = bodyData.getJSONArray("obj").size();    //总条数
        JSONObject newOb = new JSONObject();
        int totalPage = totalSize % size == 0 ? totalSize / size : totalSize / size + 1;    //总页数
        for (int i = 1; i <= totalPage; i++) {
            int startIndex = (i - 1) * size;
            int endIndex = Math.min(startIndex + size, totalSize);
            JSONArray newArr = new JSONArray();
            for (int m = startIndex; m < endIndex; m++) {
                newArr.add(oldArr.get(m));
            }
            newOb.put("obj", newArr);
            //调用接口
            JSONObject response = SyncUtil.doPostByHttpClient(url, newOb);
            //处理返回结果
            String status = SyncUtil.dealResponseStatus(response);
            //记录日志
            SyncUtil.createLog(status, startTime, endTime, newOb.toJSONString(), response.toJSONString(), type);
        }
    }

    /**
     * 分批调用MDM接口
     * @param url 接口地址
     * @param bodyData 入参
     * @param type 同步类型
     * @param startTime 变动开始时间
     * @param endTime 变动结束时间
     * @throws IOException
     */
    public static void batchPost(int size, String url, JSONObject bodyData, String type, String startTime, String endTime) throws IOException {
        JSONArray oldArr = bodyData.getJSONArray("usersInfo");
        int totalSize = oldArr.size();    //总条数
        JSONObject newOb = new JSONObject();
        int totalPage = totalSize % size == 0 ? totalSize / size : totalSize / size + 1;    //总页数
        for (int i = 1; i <= totalPage; i++) {
            int startIndex = (i - 1) * size;
            int endIndex = Math.min(startIndex + size, totalSize);
            JSONArray newArr = new JSONArray();
            for (int m = startIndex; m < endIndex; m++) {
                newArr.add(oldArr.get(m));
            }
            newOb.put("usersInfo", newArr);
            //调用接口
            JSONObject response = MokaApiUtil.syncUserInfo(url, newOb);
            //处理返回结果
            if(response.getInteger("code") != 200) {
                SyncUtil.createLog("C", startTime, endTime, newOb.toJSONString(), response.toJSONString(), type);
            }
            else {
                //当前调用条数
                int curPageCount = newArr.size();
                int successCount = response.getJSONObject("data").getInteger("successCount");
                String status = "";
                //A-成功 B-部分成功 C-失败
                if (curPageCount == successCount)
                    status = "A";
                if (successCount < curPageCount)
                    status = "B";
                if (curPageCount == 0)
                    status = "C";
                //记录日志
                SyncUtil.createLog(status, startTime, endTime, newOb.toJSONString(), response.toJSONString(), type);
            }
        }
    }

    /**
     * 获取分页条数
     * @return
     */
    public static int getBatchSize () {
        QFilter filter = new QFilter("number", QCP.equals, "MDM");
        filter.and("nckd_entryentity.nckd_key",QCP.equals, "size");
        DynamicObject bill = QueryServiceHelper.queryOne("nckd_commonparams", "nckd_entryentity.nckd_value", new QFilter[]{filter});
        return Integer.valueOf(bill.getString("nckd_entryentity.nckd_value"));
    }



    /**
     *
     * @param url
     * @param bodyData
     * @return
     */
    public static JSONObject doPostByHttpClient(String url, JSONObject bodyData) throws IOException {
        Map<String, String> headers = new HashMap();
        headers.put("Content-Type", "application/json");
        headers.put("Accept", "*/*");
        logger.info(String.format("url[%s],data[%s]", url, bodyData.toJSONString()));
        System.out.println(String.format("url[%s],data[%s]", url, bodyData.toJSONString()));
        String responseEntify = HttpClientUtils.postjson(url, headers, bodyData.toJSONString(), 10000, 10000);

        logger.info(responseEntify);
        JSONObject result = (JSONObject)JSONObject.parse(responseEntify);
        return result;
    }

    /**
     * 处理MDM返回结果 A-成功 B-部分成功 C-失败
     * @param response
     * @return
     */
    public static String dealResponseStatus (JSONObject response) {
        String status = "";
        int successCount = 0;
        int errorCount = 0;
        JSONArray responseData = response.getJSONArray("responseData");
        //根据明细数据status分组
        Map<String, JSONArray> responseDataMap = responseData.stream().map(obj -> (JSONObject) obj)
                .collect(Collectors.groupingBy(
                        json -> json.getString("status"),
                        () -> new HashMap<>(),
                        Collectors.mapping(JSONObject::toJSON, Collectors.toCollection(JSONArray::new))
                ));
        JSONArray sucArr = responseDataMap.get("S");
        JSONArray errArr = responseDataMap.get("E");
        if(sucArr != null)
            successCount = sucArr.size();
        if(errArr != null)
            errorCount = errArr.size();

        if(errorCount > 0 && successCount > 0) {
            status = "B";
        }
        else if(successCount == 0  && errorCount > 0) {
            status = "C";
        }
        else if(successCount > 0 && errorCount == 0) {
            status = "A";
        }

        return status;
    }

    /**
     * 查询成本中心信息
     * 基础资料-财务数据-成本中心映射配置
     * @return
     */
    public static Map<Long, DynamicObject> getCostCenter() {
        String selectFields = "costcenter.isleaf,costcenter.enable,entryentity.sourcetype,entryentity.sourcedata.id,entryentity.sourcedata.number";
        QFilter filter1 = new QFilter("entryentity.sourcetype", QCP.equals, "bos_adminorg");
        DynamicObjectCollection cols = QueryServiceHelper.query("bos_costcentersourcemap", selectFields, new QFilter[]{filter1});
        Map<Long, DynamicObject> costCenterMap = (Map)cols.stream().collect(Collectors.toMap((dyx) -> {
            return dyx.getLong("entryentity.sourcedata.id");
        }, (dyx) -> {
            return dyx;
        }, (key1, key2) -> {
            return key2;
        }));
        return costCenterMap;
    }




    /**
     * 获取上一次同步时间
     * @param type
     */
    public static Date getLastSyncEndTime(String type) throws ParseException {
        QFilter filter = new QFilter("nckd_synctype", QCP.equals, type);
        filter.and("nckd_status", QCP.equals, "A");
        filter.and("enable", QCP.equals, "1");
        DynamicObjectCollection logInfos = QueryServiceHelper.query(KEY_ENTITY_LOG, "nckd_endtime", new QFilter[]{filter}, "nckd_endtime desc", 1);
        if(logInfos.size() > 0) {
            return logInfos.get(0).getDate("nckd_endtime");
        }
        else {
            String dateStr = "1900-01-01";
            //7.1时手工导入过一次数据，这里从7.2开始
            if(type.equals(SyncPersonMoka))
                dateStr = "2025-07-02";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            return sdf.parse(dateStr);
        }
    }

    /**
     * 创建同步日志
     * @param status 同步状态 A-成功 B-部分成功 C-失败
     * @param startTime 变动开始时间
     * @param endTime   变动结束时间
     * @param request   请求报文
     * @param response  返回报文
     * @param syncType  同步类型
     */
    public static void createLog (String status, String startTime, String endTime, String request, String response, String syncType) {
        DynamicObject dynamicObject = BusinessDataServiceHelper.newDynamicObject(KEY_ENTITY_LOG);
        String uuid = UUID.randomUUID().toString().replace("-", "");
        dynamicObject.set("enable", "1");
        dynamicObject.set("status", "C");
        dynamicObject.set("number", uuid.substring(0,29));
        dynamicObject.set("nckd_starttime", startTime);
        dynamicObject.set("nckd_endtime", endTime);
        dynamicObject.set("nckd_status", status);
        dynamicObject.set("nckd_synctype", syncType);
        if (request.length() < 200) {
            dynamicObject.set("nckd_request", request);
        } else {
            dynamicObject.set("nckd_request", request.substring(0, 200) + "...");
        }
        dynamicObject.set("nckd_request_tag", request);

        if (response.length() < 200) {
            dynamicObject.set("nckd_response", response);
        } else {
            dynamicObject.set("nckd_response", response.substring(0, 200) + "...");
        }
        dynamicObject.set("nckd_response_tag", response);
        SaveServiceHelper.save(new DynamicObject[]{dynamicObject});
        logger.info("-------- 保存日志 --------");
    }

    public static StringBuilder getPersonQuerySql() {
        StringBuilder sb = new StringBuilder();
        // 人员ID，主体标签，人员姓名，人员工号，部门ID，部门编码，部门名称
        sb.append("/*dialect*/ select a.fid PERSONID,'JY' MAIN_BODY_ID, a.fname MD_DESCRIPTION ,a.fnumber MD_CODE,org.fid ORGID,case when org.fk_nckd_easid = ' ' then org.fid::VARCHAR else org.fk_nckd_easid end EMP_W_DEPCODE ,org.fname EMP_W_DEPNAME,\n");
        // 年龄，职位名称，职位编码，上级职位名称，上级职位编码
        sb.append("             b.fage EMP_B_AGE,pos.fname EMP_W_JOBNAME,pos.fnumber EMP_W_JOBCODE,pos1.fname EMP_W_PARJOBNAME,pos1.fnumber EMP_W_PARJOBCODE,\n");
        // TODO sb.append("             -- 长名称、户籍地址\n");
        sb.append("             r.fnumber EMP_B_PERS_PROPERTY, \n");
        //性别
        sb.append("             case when sex.fid = '1010' then 1 when sex.fid = '1020' then 2 else 0 end EMP_B_GENDER,\n");
        //所属公司
        sb.append("             '上海济煜' EMP_W_COM,\n");
        // 手机号码，实际转正日期
        sb.append("             substring(e.fphone,5,15) EMP_B_MOBI_PHNUM,to_char(h.frealregulardate,'yyyy-MM-dd') EMP_W_OBTA_DATE,\n");
        // 用人单位编码，职级
        sb.append("             case when w.fnumber is null then q.fnumber else w.fnumber end EMP_B_BELONG_LEGAL, p.fnumber EMP_B_RANK, \n");
        sb.append("             to_char(person.fmodifytime,'yyyy-MM-dd hh24:mi:ss') EMP_B_LAST_UPTIME, \n");
        // 邮箱，生日，办公地点
        sb.append("             e.fbusemail EMP_B_EMAIL,to_char(b.fbirthday,'yyyy-MM-dd') EMP_B_DATE_BIRTH,wp.fname EMP_B_WORKSPACE,\n");
        // 身份证，用工关系状态，国籍 2025-05-25 身份证不传
        // sb.append("             g.fnumber EMP_B_IDNUMBER, n.fk_nckd_mdmkey EMP_B_RELA_STATUS, nation.fk_nckd_mdmkey EMP_B_NATIONALITY, \n");
        sb.append("             ' ' EMP_B_IDNUMBER, n.fk_nckd_mdmkey EMP_B_RELA_STATUS, nation.fk_nckd_mdmkey EMP_B_NATIONALITY, \n");
        // 在职状态
        sb.append("             s.fk_nckd_mdmkey EMP_W_WORKSTATE, \n");
        // 退休日期
        sb.append("             to_char(i1.fcontractenddate,'yyyy-MM-dd') EMP_W_RETIRDATE, \n");
        // 离职单相关信息
        sb.append("             j.fbillno EMP_W_RESIGNNUMBER,\n");
        sb.append("             to_char(i.fcontractenddate,'yyyy-MM-dd') EMP_W_RESIGNBIZDATE,\n");
        sb.append("             to_char(i.fcontractenddate,'yyyy-MM-dd') EMP_W_TERMDATE,\n");
        sb.append("             to_char(j.fcreatetime,'yyyy-MM-dd') EMP_W_RESIGNCREATEDATE,\n");
        sb.append("             case when k.fjoincomdate is null then '2025-04-01' else to_char(k.fjoincomdate,'yyyy-MM-dd') end EMP_W_ENTRY_DATE,\n");
        // TODO 现居住地
        sb.append("             case when b.fk_nckd_isgbqs = '1' then '是' else '否' end EMP_B_RELAT_CADRES, \n");
        sb.append("             d.fnativeplace EMP_B_NATIVE_PLACE ,g1.fnumber EMP_B_PASSPORT,l.fk_nckd_mdmkey EMP_B_NATIONAL, \n");
        sb.append("             case when a.fdatastatus = '-2' then '是' else '否' end EMP_B_DELE_LOGO, \n");
        sb.append("             u.fbillno EMP_W_EMPLOYNUMBER \n");
        sb.append(" from t_hrpi_ermanfile person\n");
        sb.append(" left join t_hrpi_person a on a.fid = person.fpersonid and a.fiscurrentversion = '1' and a.fdatastatus = '1'    -- 人员主表\n");
        sb.append(" left join t_hrpi_pernontsprop b on a.fid = b.fpersonid and b.fiscurrentversion = '1' and b.fdatastatus = '1'   -- 非时序\n");
        sb.append(" left join t_hrpi_pertsprop c on a.fid = c.fpersonid and c.fiscurrentversion = '1' and c.fdatastatus = '1'           -- 时序\n");
        sb.append(" left join t_hrpi_perregion d on a.fid = d.fpersonid and d.fiscurrentversion = '1' and d.fdatastatus = '1'        -- 区域信息\n");
        sb.append(" left join t_hrpi_percontact e on a.fid = e.fpersonid and e.fiscurrentversion = '1' and e.fdatastatus = '1'      -- 联系方式\n");
        sb.append(" inner join t_hrpi_empposorgrel f on f.fid = person.fempposrelid and f.fiscurrentversion = '1' and f.fisprimary = '1' and f.fdatastatus = '1'  -- 任职经历\n");
        sb.append(" left join t_hrpi_percre g on a.fid = g.fpersonid and g.fiscurrentversion = '1' and g.fcredentialstypeid = '1010' and g.fdatastatus = '1' -- 证件信息 身份证\n");
        sb.append(" left join ( select fnumber,fpersonid,fmodifytime,row_number() over (partition by fpersonid order by fissuedate desc) r \n");
        sb.append("             from t_hrpi_percre where fiscurrentversion = '1' and fcredentialstypeid = '1020' and fdatastatus = '1' ) g1 ON a.fid = g1.fpersonid AND g1.r = 1 \n");
        //sb.append(" left join t_hrpi_percre g1 on a.fid = g1.fpersonid and g1.fiscurrentversion = '1' and g1.fcredentialstypeid = '1020' and g1.fdatastatus = '1' -- 证件信息 护照\n");
        sb.append(" left join t_hrpi_trialperiod h on a.fid = h.fpersonid and h.fiscurrentversion = '1' and h.fdatastatus = '1'      -- 试用期\n");
        sb.append(" left join t_haos_adminorg org on org.fid = f.fadminorgid                                             -- 所属部门\n");
        sb.append(" left join t_hbpm_position pos on pos.fid = f.fpositionid                                               -- 岗位\n");
        sb.append(" left join t_hbpm_position pos1 on pos1.fid = pos.fparentid                                          -- 上级岗位\n");
        sb.append(" left join t_hbss_sex sex on sex.fid = b.fgenderid                                                            -- 性别\n");
        sb.append(" left join t_hrpi_baselocation t on t.femployeeid = person.femployeeid and t.fiscurrentversion = '1' and t.fbusinessstatus = '1' and t.fdatastatus = '1'  -- 常驻工作地\n ");
        sb.append(" left join t_hbss_workplace wp on wp.fid = t.flocationId                                           -- 工作地点\n");
        sb.append(" left join t_hbss_nationality nation on nation.fid = b.fnationalityid                              -- 国籍\n");
        sb.append(" left join t_htm_quitfileinfo i on i.femployeeid = f.femployeeid and i.fquittypeid <> '1245492024325465088'  -- 离职档案 非退休 \n");
        sb.append(" left join t_htm_quitfileinfo i1 on i1.femployeeid = f.femployeeid and i1.fquittypeid = '1245492024325465088'  -- 离职档案 退休 \n");
        sb.append(" left join t_htm_quitapplybill j on j.fid = i.fquitapplyid      -- 离职办理单 非退休 \n");
        sb.append(" left join t_htm_quitapplybill j1 on j1.fid = i1.fquitapplyid   -- 离职办理单 退休 \n");
        sb.append(" left join t_hrpi_perserlen k on k.femployeeid = person.femployeeid and k.fiscurrentversion = '1' and k.fdatastatus = '1' -- 服务年限\n");
        sb.append(" left join t_hbss_flok l on l.fid = b.ffolkid                                                                          -- 民族\n");
        sb.append(" left join t_hrpi_empentrel m on m.fid = person.fempentrelid and m.fdatastatus = '1' -- 职业信息\n");
        sb.append(" left join t_hbss_laborrelstatus n on n.fid = m.flaborrelstatusid                                  -- 用工关系状态\n");
        sb.append(" left join t_hrpi_empjobrel o on person.femployeeid = o.femployeeid and o.fiscurrentversion = '1' and o.fbusinessstatus = '1' and o.fdatastatus = '1'   -- 职级职等 \n");
        sb.append(" left join t_hbjm_joblevel p on p.fjoblevelid = o.fjoblevelid  -- 职级 \n");
        sb.append(" left join t_hbss_enterprise q on q.fid = m.fenterpriseid   -- 用人单位 \n");
        sb.append(" left join tk_nckd_personproperty r on r.fid = b.fk_nckd_personproperty -- 个人性质 \n");
        sb.append(" left join t_hbss_labrelstatuscls s on s.fid = n.flabrelstatusclsid -- 用工状态分类 \n");
        sb.append(" left join ( select row_number() over (partition by u1.femployeeno order by u2.feffectdate desc) r, u2.fbillno, u1.femployeeNo \n");
        sb.append("             from t_hom_onbrdbill u1 left join t_hom_onbrdbill_a u2 on u1.fid = u2.fid) u on u.femployeeno = a.fnumber and u.r = 1 -- 入职信息 \n");
        sb.append(" left join ( select row_number() over (partition by fermanfileid order by fmodifytime desc) r, fermanfileid, fsigncompanyid, fnumber \n");
        sb.append("             from t_hlcm_contract where fiscurrentversion = '1' ) v on v.fermanfileid = person.fid and v.r = 1  -- 劳动合同档案\n");
        sb.append(" left join t_hbss_signcompany w on w.fid = v.fsigncompanyid  -- 聘用单位 \n");
        sb.append(" where person.fiscurrentversion = '1' \n");
        sb.append("   and person.fbusinessstatus = '1' \n");
        return sb;
    }

    /**
     * 兼职查询sql
     * @return
     */
    public static StringBuilder getPersonPartTimeSql () {
        StringBuilder sb = new StringBuilder();
        sb.append("/*dialect*/ select a.fpersonId personId, \n");
        sb.append("        case when a.fk_nckd_easid = ' ' then a.fid::varchar else a.fk_nckd_easid end EMP_P_PRIMARY_KEY,  \n");
        sb.append("        to_char(a.fstartdate,'yyyy-MM-dd') EMP_P_STARTDATE, \n");
        sb.append("        to_char(a.fenddate,'yyyy-MM-dd') EMP_P_ENDDATE, \n");
        sb.append("        '上海济煜' EMP_P_SUBCOMPANY, \n");
        sb.append("        org.fid ORGID, \n");
        sb.append("        case when org.fk_nckd_easid = ' ' then org.fid::VARCHAR else org.fk_nckd_easid end EMP_P_DEPCODE, \n");
        sb.append("        org.fname EMP_P_DEPNAME, \n");
        sb.append("        pos.fnumber EMP_P_POSICODE, \n");
        sb.append("        pos.fname EMP_P_POSINAME, \n");
        sb.append("        pos1.fnumber EMP_P_PARPOSICODE, \n");
        sb.append("        pos1.fname EMP_P_PARPOSINAME, \n");
        sb.append("        '2' EMP_P_FORTYPE, \n");
        sb.append("        '否' EMP_P_DELETLOGO \n");
        sb.append(" from t_hrpi_ermanfile a \n");
        sb.append(" left join t_hrpi_erfiletype b on a.ffiletypeid = b.fid \n");
        sb.append(" left join t_hbss_postype c on b.fpostypeid = c.fid \n");
        sb.append(" left join t_hrpi_empposorgrel d on d.fid = a.fempposrelid \n");
        sb.append(" left join t_hbpm_position pos on pos.fid = d.fpositionid \n");
        sb.append(" left join t_hbpm_position pos1 on pos1.fid = pos.fparentid  \n");
        sb.append(" left join t_haos_adminorg org on org.fid = d.fadminorgid \n");
        sb.append(" where c.fnumber = '1020_S' \n");
        sb.append("    and a.fiscurrentversion = '1' \n");
        sb.append("    and a.fdatastatus = '1' \n");
        return sb;
    }

    /**
     * 获取兼职变动sql
     * 主要处理兼职岗位或者上级岗位有更新的情况
     * @return
     */
    public static StringBuilder getPersonPartTimeChangeSql (String startTime, String endTime) {
        StringBuilder sb = new StringBuilder();
        sb.append("/*dialect*/ select a.fpersonId \n");
        sb.append(" from t_hrpi_ermanfile a \n");
        sb.append(" left join t_hrpi_erfiletype b on a.ffiletypeid = b.fid \n");
        sb.append(" left join t_hbss_postype c on b.fpostypeid = c.fid \n");
        sb.append(" left join t_hrpi_empposorgrel d on d.fid = a.fempposrelid \n");
        sb.append(" left join t_hbpm_position pos on pos.fid = d.fpositionid \n");
        sb.append(" left join t_hbpm_position pos1 on pos1.fid = pos.fparentid  \n");
        sb.append(" where c.fnumber = '1020_S' \n");
        sb.append("   and a.fiscurrentversion = '1' \n");
        sb.append("   and a.fdatastatus = '1' \n");
        sb.append("   and (( to_char(pos.fmodifytime,'yyyy-MM-dd hh24:mi:ss') <= '"+endTime+"' and to_char(pos.fmodifytime,'yyyy-MM-dd hh24:mi:ss') >=  '"+startTime+"' ) \n");
        sb.append("   or (to_char(pos1.fmodifytime,'yyyy-MM-dd hh24:mi:ss') <= '"+endTime+"' and to_char(pos1.fmodifytime,'yyyy-MM-dd hh24:mi:ss') >=  '"+startTime+"' )) ");
        return sb;
    }

    /**
     * 获取兼职档案， 人员ID - JSONArray数据
     *
     * @return
     */
    public static Map<Long, JSONArray> getPartTimeInfo(Map<Long, DynamicObject> orgDyxMap) {
        StringBuilder sb = getPersonPartTimeSql();
        logger.info("-------- 查询人员兼职sql :" + sb.toString() + " -------- ");
        DataSet dataSet = DB.queryDataSet("SyncUtilGetPartTime", DBRoute.of("hr"), sb.toString());

        Map<Long, JSONArray> partTimeMap = new HashMap<Long, JSONArray>();
        String[] fieldNames = dataSet.getRowMeta().getFieldNames();
        while(dataSet.hasNext()) {
            JSONObject data = new JSONObject(true);
            Row rowData = dataSet.next();
            //循环put数据
            for (String field : fieldNames) {
                data.put(field.toUpperCase(), rowData.get(field));
            }
            data.put("EMP_P_DEPLNAME",orgDyxMap.get(rowData.getLong("ORGID")).getString("structure.fullname"));
            JSONArray partTimeArr = partTimeMap.get(rowData.getLong("PERSONID"));
            if(partTimeArr != null) {
                partTimeArr.add(data);
            }
            else {
                partTimeArr = new JSONArray();
                partTimeArr.add(data);
            }
            partTimeMap.put(rowData.getLong("PERSONID"), partTimeArr);
        }
        return partTimeMap;
    }

    /**
     * 自助用获取人员信息sql
     * @param personId
     * @return
     */
    public static StringBuilder getPersonSqlForHrobs (Long personId) {
        StringBuilder sb = new StringBuilder();
        sb.append("/*dialect*/ select a.fid personId, \n");
        sb.append("        to_char(b.fjoincomdate,'yyyy-MM-dd') joinDate, \n");
        sb.append("        to_char(c.fstartdate,'yyyy-MM-dd') startDate, \n");
        sb.append("        to_char(c.fenddate,'yyyy-MM-dd') endDate, \n");
        sb.append("        e.fname jobLevel, \n");
        sb.append("        wp.fname placeName \n");
        sb.append(" from t_hrpi_person a \n");
        sb.append(" left join t_hrpi_perserlen b on a.fid = b.fpersonid and b.fiscurrentversion = '1' and b.fbusinessstatus = '1' and b.fdatastatus = '1' \n");
        sb.append(" left join ( select row_number() over (partition by fpersonid order by fmodifytime desc) r, fpersonid, fstartdate, fenddate \n");
        sb.append("             from t_hlcm_contract where fiscurrentversion = '1' and fcontractstatus = '1' and fprotocoltype = '1' ) c on c.fpersonid = a.fid and c.r = 1 \n");
        sb.append(" left join t_hrpi_empjobrel d on a.fid = d.fpersonid and d.fiscurrentversion = '1' and d.fbusinessstatus = '1' and d.fdatastatus = '1' \n");
        sb.append(" left join t_hbjm_joblevel e on e.fjoblevelid = d.fjoblevelid \n");
        sb.append(" left join t_hrpi_baselocation f on f.fpersonid = a.fid and f.fiscurrentversion = '1' and f.fbusinessstatus = '1' and f.fdatastatus = '1' \n");
        sb.append(" left join t_hbss_workplace wp on wp.fid = f.flocationId \n");
        sb.append(" where a.fiscurrentversion = '1'  \n");
        sb.append("   and a.fdatastatus = '1' \n");
        sb.append("   and a.fid = '"+personId+"' \n");
        return sb;
    }

    /**
     * Moka同步面试官用，获取变动档案sql
     * @return
     */
    public static StringBuilder getChangePersonForMoka() {
        StringBuilder sb = new StringBuilder();
        sb.append(" /*dialect*/ SELECT distinct t1.fname personName, \n");
        sb.append("                    t1.fnumber personNumber, \n");
        sb.append("                    t5.fnumber orgMokaNumber, \n");
        sb.append("                    case when t7.fname = '已离职' then 1 else 0 end deactivated, \n");
        sb.append("                    t8.fbusemail, \n");
        sb.append("                    substring(t8.fphone,5,15) fphone \n");
        sb.append(" from t_hrpi_ermanfile t1\n");
        sb.append(" left join t_hrpi_person t2 on t1.fpersonid = t2.fid \n");
        sb.append(" left join t_hrpi_empposorgrel t3 on t1.fempposrelid = t3.fid \n");
        sb.append(" left join t_haos_adminorg org on org.fid = t3.fadminorgid \n");
        sb.append(" inner join t_tsrbs_adminorgentry t4 on t4.frealadminorgid = org.fid \n");
        sb.append(" inner join t_tsrbs_foreignadminorg t5 on t5.fid = t4.fid \n");
        sb.append(" left join t_hrpi_empentrel t6 on t6.fid = t1.fempentrelid \n");
        sb.append(" left join t_hbss_laborrelstatus t7 ON t7.fid = t6.flaborrelstatusid \n");
        sb.append(" left join t_hrpi_percontact t8 ON t8.fpersonid = t1.fpersonid and t8.fiscurrentversion = '1' and t8.fdatastatus = '1' \n");
        sb.append(" where 1 = 1 \n");
        sb.append("   AND t1.fiscurrentversion = '1' \n");
        sb.append("   AND t1.fbusinessstatus = '1' \n");
        sb.append("   AND t1.fdatastatus = '1' \n");
        //sb.append("   AND t1.fmodifytime >= '2025-08-10 00:00:00'\n");
        //sb.append("   AND t1.fmodifytime <= '2025-08-21 23:59:59'\n");
        sb.append("   AND t3.fpostypeid = '1010' \n");
        sb.append("   AND t1.fnumber not like 'w%' \n");
        return sb;
    }

    /**
     * 获取人员主任职的岗位和部门
     * @param employeeId
     * @return
     */
    public static DynamicObject getAdminOrgAndPositionByEmployeeId(Long employeeId) {
        QFilter filter = QFilterUtil.getCurrentQf();
        filter.and("employee", QCP.equals, employeeId);
        filter.and("isprimary", QCP.equals, "1");
        String selectFields = "adminorg.id,position.id";
        return QueryServiceHelper.queryOne("hrpi_empposorgrel", selectFields, new QFilter[]{filter});
    }



}
