package com.seeyon.apps.bipsync.aop;

import com.seeyon.apps.cip.organization.util.DataBaseUtils;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.util.JDBCAgent;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;

import javax.script.ScriptException;
import java.sql.Connection;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

import static com.seeyon.ctp.common.formula.FormulaUtil.getVar;

/**
 * 功能描述:<br>
 * <pre>
 *
 * </pre>
 *
 * @Author: FanGaowei
 * @Date: 2021/10/9
 */
@Aspect
public class NcHrSynAspect {
    public static List<String> data = new ArrayList<String>();
    public SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    static {
        data.add("201");
        data.add("202");
        data.add("203");
        data.add("204");
        data.add("210");
    }

    private static final Log log = LogFactory.getLog(NcHrSynAspect.class);

    @Pointcut("execution(public * com.seeyon.apps.cip.organization.manager.impl.CipSynOperationManagerImpl.handOperation(..))))")
    private void handOperation() {
    }

    @Pointcut("execution(public * com.seeyon.apps.cip.organization.manager.impl.CipSynOperationManagerImpl.autoOperation(..))))")
    private void autoOperation() {
    }

    @Before(value = "autoOperation() || handOperation()")
    public void before(JoinPoint joinPoint) {

    }

    @AfterReturning(value = "autoOperation() || handOperation()",
            returning = "result")
    public void after(JoinPoint joinPoint, Object result) {
        // 同步部门HRG角色
        String driver = null;
        String url = null;
        String user = null;
        String pwd = null;
        try {
            driver = (String) getVar("orgSync.driver");
            url = (String) getVar("orgSync.url");
            user = (String) getVar("orgSync.user");
            pwd = (String) getVar("orgSync.pwd");
        } catch (ScriptException | BusinessException e) {
            log.error("获取参数失败！");
        }
        Connection srcCon = null;
        Connection desCon = null;
        try {
            srcCon = DataBaseUtils.getInstance().getConnection(StringUtils.isBlank(driver) ? "oracle.jdbc.driver.OracleDriver" : driver, StringUtils.isBlank(url) ? "jdbc:oracle:thin:@10.1.90.21:1521:orcl" : url, StringUtils.isBlank(user) ? "bip2oa" : user, StringUtils.isBlank(pwd) ? "sxlybip2022oa" : pwd);
            desCon = JDBCAgent.getDataSource().getConnection();
            srcCon.setAutoCommit(false);
            desCon.setAutoCommit(false);
            syncGroupToMidDB(srcCon, desCon);
            syncUnitToMidDb(srcCon, desCon);
            syncDeptToMidDb(srcCon, desCon);
            syncPostToMidDb(srcCon, desCon);
            syncLevelMidDB(srcCon, desCon);
            syncPersonToMidDb(srcCon, desCon);
            syncPersonBaseToMidDb(srcCon, desCon);
            syncPersonPartMidDB(srcCon, desCon);
            desCon.commit();
        } catch (Exception e) {
            log.error("数据同步异常", e);
        } finally {
            if (srcCon != null) {
                try {
                    srcCon.close();
                } catch (Exception e) {
                }
            }
            if (desCon != null) {
                try {
                    desCon.close();
                } catch (Exception e) {
                }
            }
        }
    }

    void syncGroupToMidDB(Connection srcCon, Connection desCon) throws Exception {
        QueryRunner queryRunner = new QueryRunner(true);
        List<Map<String, Object>> rows = queryRunner.query(srcCon, "SELECT * FROM MID_ORG_GROUP ", new MapListHandler());
        queryRunner.update(desCon, "delete from MID_ORG_GROUP");
        for (Map<String, Object> row : rows) {
            queryRunner.insert(desCon,
                    "INSERT INTO MID_ORG_GROUP (ID, CODE, NAME, SHORT_NAME, PARENTID, IS_ENABLE, IS_DELETED, UPDATE_TIME) VALUES  (?, ?, ?, ?, ?, ?, ?, ?)", new MapListHandler(),
                    row.get("ID"), row.get("CODE"), row.get("NAME"), row.get("SHORT_NAME"), row.get("PARENTID"), row.get("IS_ENABLE"), row.get("IS_DELETED"), new Timestamp(format.parse(String.valueOf(row.get("UPDATE_TIME"))).getTime()));
        }
    }

    void syncUnitToMidDb(Connection srcCon, Connection desCon) throws Exception {
        QueryRunner queryRunner = new QueryRunner(true);
        List<Map<String, Object>> rows = queryRunner.query(srcCon, "select * from mid_org_unit where code in (" + StringUtils.join(data.stream().map(p->"'"+p+"'").collect(Collectors.toList()), ",") + ")", new MapListHandler());
        queryRunner.update(desCon, "delete from MID_ORG_UNIT");
        for (Map<String, Object> row : rows) {
            //只取部分单位的人员
            queryRunner.insert(desCon,
                    "INSERT INTO MID_ORG_UNIT (ID, CODE, NAME, SHORT_NAME, GROUPID, PARENTID, IS_ENABLE, IS_DELETED, UPDATE_TIME) VALUES (?, ?, ?, ?,?, ?,?, ?, ?)", new MapListHandler(),
                    row.get("ID"), row.get("CODE"), row.get("NAME"), row.get("SHORT_NAME"), row.get("GROUPID"), row.get("PARENTID"), row.get("IS_ENABLE"), row.get("IS_DELETED"), new Timestamp(format.parse(String.valueOf(row.get("UPDATE_TIME"))).getTime()));
        }
    }

    void syncDeptToMidDb(Connection srcCon, Connection desCon) throws Exception {
        QueryRunner queryRunner = new QueryRunner(true);
        //换成ID
        List<Map<String, Object>> rows = queryRunner.query(srcCon, "SELECT * FROM MID_ORG_DEPARTMENT  where UNITID  in( select ID from mid_org_unit where code in (" + StringUtils.join(data.stream().map(p->"'"+p+"'").collect(Collectors.toList()), ",") + "))", new MapListHandler());
        queryRunner.update(desCon, "delete from MID_ORG_DEPARTMENT");
        for (Map<String, Object> row : rows) {
            //只取部分单位的人员
            queryRunner.insert(desCon,
                    "INSERT INTO MID_ORG_DEPARTMENT (ID, CODE, NAME, UNITID, GROUPID, PARENTID, DEPARTMANAGERS, IS_ENABLE, IS_DELETED, UPDATE_TIME,EXT_ATTR_1,EXT_ATTR_2) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new MapListHandler(),
                    row.get("ID"), row.get("CODE"), row.get("NAME"), row.get("UNITID"), row.get("GROUPID"), row.get("PARENTID"), row.get("DEPARTMANAGERS"), row.get("IS_ENABLE"), row.get("IS_DELETED"), new Timestamp(format.parse(String.valueOf(row.get("UPDATE_TIME"))).getTime()), "部门", row.get("CODE"));
        }

        for (String datum : data) {
            //需求 把202开头的单位同步成部门
            List<Map<String, Object>> unitRows = queryRunner.query(srcCon, "select * from MID_ORG_UNIT  where code = ? ", new MapListHandler(), datum);
            log.error("编码调试：" + unitRows.size());
            Map<String, Object> unitMap = unitRows.get(0);
            List<Map<String, Object>> rowUnits = queryRunner.query(srcCon, "select * from MID_ORG_UNIT  where code like ? and  code != ?", new MapListHandler(), datum + "%", datum);
            //当作部门
            for (Map<String, Object> rowUnit : rowUnits) {
                Object code = "A" + rowUnit.get("CODE");
                queryRunner.insert(desCon, "INSERT INTO MID_ORG_DEPARTMENT (ID, CODE, NAME, UNITID, GROUPID, PARENTID, DEPARTMANAGERS, IS_ENABLE, IS_DELETED, UPDATE_TIME,EXT_ATTR_1,EXT_ATTR_2) VALUES (?,?, ?, ?, ?, ?, ?, ?,?, ?,?,?)", new MapListHandler(),
                        rowUnit.get("ID"), code, rowUnit.get("NAME"), unitMap.get("ID"), rowUnit.get("GROUPID"), rowUnit.get("PARENTID"), null, rowUnit.get("IS_ENABLE"), rowUnit.get("IS_DELETED"), new Timestamp(format.parse(String.valueOf(rowUnit.get("UPDATE_TIME"))).getTime()), "单位", rowUnit.get("CODE"));
                //单位下的部门
                List<Map<String, Object>> unitDepts = queryRunner.query(srcCon, "select * from MID_ORG_DEPARTMENT where unitId = ? ", new MapListHandler(), rowUnit.get("ID"));
                for (Map<String, Object> row : unitDepts) {
                    //这里加上前缀是因为同步到部门的单位下可能编码回重复
                    Object deptCode = "D" + String.valueOf(row.get("CODE"));
                    Object parentid = row.get("PARENTID");
                    if (Objects.equals(parentid, "~")) {
                        parentid = rowUnit.get("ID");
                    }
                    queryRunner.insert(desCon,
                            "INSERT INTO MID_ORG_DEPARTMENT (ID, CODE, NAME, UNITID, GROUPID, PARENTID, DEPARTMANAGERS, IS_ENABLE, IS_DELETED, UPDATE_TIME,EXT_ATTR_1,EXT_ATTR_2) VALUES (?,?, ?, ?, ?, ?, ?, ?,?, ?,?,?)", new MapListHandler(),
                            row.get("ID"), deptCode, row.get("NAME"), unitMap.get("ID"), row.get("GROUPID"), parentid, row.get("DEPARTMANAGERS"), row.get("IS_ENABLE"), row.get("IS_DELETED"), new Timestamp(format.parse(String.valueOf(row.get("UPDATE_TIME"))).getTime()), "部门", row.get("CODE"));
                }
            }
        }
    }

    void syncLevelMidDB(Connection srcCon, Connection desCon) throws Exception {
//        QueryRunner queryRunner = new QueryRunner(true);
//        List<Map<String, Object>> rows = queryRunner.query(srcCon, "SELECT * FROM MID_ORG_JOB ", new MapListHandler());
//        queryRunner.update(desCon, "delete from MID_ORG_JOB");
//        for (Map<String, Object> row : rows) {
//            //只取部分单位的人员
//            queryRunner.insert(desCon,
//                    "INSERT INTO MID_ORG_JOB  (ID, NAME, CODE, SORT, UNITID, IS_ENABLE, IS_DELETED, UPDATE_TIME) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", new MapListHandler(),
//                    row.get("ID"), row.get("NAME"), row.get("CODE"), row.get("SORT"), row.get("UNITID"), row.get("IS_ENABLE"), row.get("IS_DELETED"), row.get("UPDATE_TIME"));
//        }
    }

    void syncPostToMidDb(Connection srcCon, Connection desCon) throws Exception {
//        QueryRunner queryRunner = new QueryRunner(true);
//        List<Map<String, Object>> rows = queryRunner.query(srcCon, "SELECT * FROM MID_ORG_POST ", new MapListHandler());
//        queryRunner.update(desCon, "delete from MID_ORG_POST");
//        for (Map<String, Object> row : rows) {
//            //只取部分单位的人员
//            queryRunner.insert(desCon, "INSERT INTO MID_ORG_POST (ID, NAME, CODE, ENTITYID, DEPARTMENTID, IS_RESPONPOST, IS_ENABLE, IS_DELETED, UPDATE_TIME, EXT_ATTR_1, EXT_ATTR_2, EXT_ATTR_3, EXT_ATTR_4, EXT_ATTR_5, EXT_ATTR_6, EXT_ATTR_7, EXT_ATTR_8, EXT_ATTR_9, EXT_ATTR_10) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
//                    new MapListHandler(), row.get("ID"), row.get("NAME"), row.get("CODE"), row.get("ENTITYID"), row.get("DEPARTMENTID"), row.get("IS_RESPONPOST"), row.get("IS_ENABLE"), row.get("IS_DELETED"), row.get("UPDATE_TIME"), row.get("EXT_ATTR_1"), row.get("EXT_ATTR_2"), row.get("EXT_ATTR_3"), row.get("EXT_ATTR_4"), row.get("EXT_ATTR_5"),
//                    row.get("EXT_ATTR_6"), row.get("EXT_ATTR_7"), row.get("EXT_ATTR_8"), row.get("EXT_ATTR_9"), row.get("EXT_ATTR_10"));
//        }
    }

    void syncPersonToMidDb(Connection srcCon, Connection desCon) throws Exception {
        QueryRunner queryRunner = new QueryRunner(true);
        queryRunner.update(desCon, "delete from MID_ORG_PERSON");
        for (String datum : data) {
            List<Map<String, Object>> rows = queryRunner.query(srcCon, "SELECT * FROM MID_ORG_PERSON ", new MapListHandler());
            List<Map<String, Object>> unitRows = queryRunner.query(srcCon, "select * from MID_ORG_UNIT  where code = ?", new MapListHandler(), datum);
            log.error("编码调试：" + unitRows.size());
            Map<String, Object> unitMap = unitRows.get(0);
            List<Map<String, Object>> rowUnits = queryRunner.query(srcCon, "select * from MID_ORG_UNIT  where code like ? ", new MapListHandler(), datum + "%");
            //单位下的ID集合
            List<String> unitIds = rowUnits.stream().map(p -> String.valueOf(p.get("ID"))).collect(Collectors.toList());
            for (Map<String, Object> row : rows) {
                //只取部分单位的人员
                String unitId = null;
                //当前人员在部门集合里面证明是这个单位下的人员
                if (unitIds.contains(String.valueOf(row.get("UNITID")))) {
                    unitId = String.valueOf(unitMap.get("ID"));
                }
                if (unitId == null){
                    continue;
                }
                queryRunner.insert(desCon, "INSERT INTO MID_ORG_PERSON (ID, PERSON_BASEID, STATE, NAME, CODE, LOGIN_NAME, DEPARTMENTID, UNITID, GROUPID, JOBID, POSTID, IS_ENABLE, IS_DELETED, IS_FINISH, UPDATE_TIME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new MapListHandler(),
                        row.get("ID"), row.get("PERSON_BASEID"), row.get("STATE"), row.get("NAME"), row.get("CODE"), row.get("LOGIN_NAME"), row.get("DEPARTMENTID"), unitId, row.get("GROUPID"), row.get("JOBID"), row.get("POSTID"), row.get("IS_ENABLE"), row.get("IS_DELETED"), row.get("IS_FINISH"), new Timestamp(format.parse(String.valueOf(row.get("UPDATE_TIME"))).getTime()));
            }
        }
    }

    void syncPersonBaseToMidDb(Connection srcCon, Connection desCon) throws Exception {
        QueryRunner queryRunner = new QueryRunner(true);
        List<Map<String, Object>> rows = queryRunner.query(srcCon, "SELECT * FROM MID_ORG_PERSON_BASE ", new MapListHandler());
        queryRunner.update(desCon, "delete from MID_ORG_PERSON_BASE");
        for (Map<String, Object> row : rows) {
            //只取部分单位的人员
            queryRunner.insert(desCon, "INSERT INTO MID_ORG_PERSON_BASE (ID,  UPDATE_TIME, USED_NAME, AGE, GENDER, BIRTHDAY, ID_CARD, NATION, BIRTHPLACE, MARRIAGE, WORKING_TIME, EMAIL, TELPHONE, OFFICENUMBER, ADDRESS, DEGREE, POLITICS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?, ?, ?, ?)", new MapListHandler(),
                    row.get("ID"), new Timestamp(format.parse(String.valueOf(row.get("UPDATE_TIME"))).getTime()), row.get("USEDNAME"), row.get("AGE"), row.get("GENDER"), null, row.get("ID_CARD"), row.get("NATION"), row.get("BIRTHPLACE"), row.get("MARRIAGE"), row.get("WORKING_TIME"), row.get("EMAIL"), row.get("TELPHONE"), row.get("OFFICENUMBER"), row.get("ADDRESS"), row.get("DEGREE"), row.get("POLITICS"));
        }
    }

    void syncPersonPartMidDB(Connection srcCon, Connection desCon) throws Exception {

    }
}