<%@page import="weaver.conn.RecordSet"%>
<%@ page import="org.apache.commons.lang3.StringUtils" %>
<%@ page import="weaver.general.Util" %>
<%@ page import="java.util.*" %>
<%@ page import="weaver.conn.RecordSetDataSource" %>
<jsp:useBean id="bb" class="weaver.general.BaseBean" scope="page" />
<%@ page language="java" contentType="text/html; charset=UTF-8" %>

<%

    String dataResource = "E7TEST";

    int modifier = 1;
    int creater = 1;

    RecordSet rs = new RecordSet();
    Map<String,String> subCompanyOutkeyMap = getE9SubcompanyOutkey();
    Map<String,String> departmentOutkeyMap = getE9DepartmentOutkey();
    Map<String,Map<String,String>> E7DepartmentMap = getE7DepartmentData(dataResource);
    List<String> E7SubcompanyList = getE7HrmSubcompanyList(dataResource);

    out.println("E7DepartmentMap:"+E7DepartmentMap.size()+"</br>");
    out.println("E7SubcompanyList:"+E7SubcompanyList.size()+"</br>");

    List<Map<String,Object>> topInsertList = new ArrayList<Map<String,Object>>();
    List<Map<String,Object>> topUpdateList = new ArrayList<Map<String,Object>>();



    List<String> topList = new ArrayList<>();
    String sql =" select id,departmentmark,departmentname,subcompanyid1,supdepid,allsupdepid,canceled,departmentcode,outkey,tlevel,showorderoftree,showorder\n" +
                " from hrmdepartment \n" +
                " where supdepid = 0 \n" ;
    out.println("e9-sql:"+sql+"</br>");
    rs.execute(sql);
    while(rs.next()) {
        String e9Id = Util.null2String(rs.getString("id"));
        topList.add(e9Id);

        String departmentmark = Util.null2String(rs.getString("departmentmark"));
        String departmentname = Util.null2String(rs.getString("departmentname"));
        String subcompanyid1 = Util.null2String(rs.getString("subcompanyid1"));
        String supdepid = Util.null2String(rs.getString("supdepid"));
        String allsupdepid = Util.null2String(rs.getString("allsupdepid"));
        String canceled = Util.null2String(rs.getString("canceled"));
        String departmentcode = Util.null2String(rs.getString("departmentcode"));
        String outkey = Util.null2String(rs.getString("outkey"));  //E7的hrmsubcompany的ID

        String tlevel = Util.null2String(rs.getString("tlevel"));
        String showorderoftree = Util.null2String(rs.getString("showorderoftree"));
        String showorder = Util.null2String(rs.getString("showorder"));

        out.println("e9Id:"+e9Id+""+"  departmentname:"+departmentname+"</br>");

        Map<String,Object> E9Map = new HashMap<String, Object>();
        E9Map.put("departmentname", departmentname);

        if (StringUtils.isNotBlank(departmentmark)) {
            E9Map.put("departmentmark", departmentmark);
        }
        if (StringUtils.isNotBlank(canceled)) {
            E9Map.put("canceled", canceled);
        }
        if (StringUtils.isNotBlank(departmentcode)) {
            E9Map.put("departmentcode", departmentcode);
        }
        if(StringUtils.isNotBlank(canceled)){
            E9Map.put("canceled", canceled);
        }

        if (StringUtils.isNotBlank(tlevel)) {
            E9Map.put("tlevel", tlevel);
        }
        if (StringUtils.isNotBlank(showorderoftree)) {
            E9Map.put("showorderoftree", showorderoftree);
        }
        if (StringUtils.isNotBlank(showorder)) {
            E9Map.put("showorder", showorder);
        }


        String subcompanyOutKey = "" ;
        if(subCompanyOutkeyMap.containsKey(subcompanyid1)){
            subcompanyOutKey = subCompanyOutkeyMap.get(subcompanyid1);
        }

        String supdepidOutKey = "";
        if(StringUtils.isNotBlank(supdepid)){
            if("0".equals(supdepid) || "-1".equals(supdepid) || StringUtils.isBlank(supdepid)){
                supdepidOutKey = "0";
            }else{
                if(departmentOutkeyMap.containsKey(supdepid)){
                    supdepidOutKey = departmentOutkeyMap.get(supdepid);
                }
            }
        }

        out.println("subcompanyid1:"+subcompanyid1+" subcompanyOutKey:"+subcompanyOutKey+" supdepid:"+supdepid+" supdepidOutKey:"+supdepidOutKey+"</br>");

        String allsupdepidOutKey = "";
        if(StringUtils.isNotBlank(allsupdepid)){
            String[] supdepids = allsupdepid.split(",");
            for(int i=0;i<supdepids.length;i++){
                if(StringUtils.isNotBlank(supdepids[i])){
                    if(departmentOutkeyMap.containsKey(supdepids[i])){
                        String supdepid_OutKey = departmentOutkeyMap.get(supdepids[i]);
                        allsupdepidOutKey += StringUtils.isBlank(allsupdepidOutKey) ? supdepid_OutKey : ","+supdepid_OutKey ;
                    }
                }
            }
        }

        if(StringUtils.isNotBlank(outkey)){
            if(E7DepartmentMap.containsKey(outkey)){
                if(StringUtils.isNotBlank(subcompanyOutKey) && E7SubcompanyList.contains(subcompanyOutKey) ) {
                    if (StringUtils.isNotBlank(supdepidOutKey)) {

                        Map<String,String> e7DeptMap = E7DepartmentMap.get(outkey);
                        String e7departmentmark = Util.null2String(e7DeptMap.get("e7departmentmark"));
                        String e7departmentname = Util.null2String(e7DeptMap.get("e7departmentname"));
                        String e7subcompanyid1 = Util.null2String(e7DeptMap.get("e7subcompanyid1"));
                        String e7supdepid = Util.null2String(e7DeptMap.get("e7supdepid"));
                        String e7allsupdepid = Util.null2String(e7DeptMap.get("e7allsupdepid"));
                        String e7canceled = Util.null2String(e7DeptMap.get("e7canceled"));
                        String e7departmentcode = Util.null2String(e7DeptMap.get("e7departmentcode"));
                        String e7tlevel = Util.null2String(e7DeptMap.get("e7tlevel"));
                        String e7showorder = Util.null2String(e7DeptMap.get("e7showorder"));

                        out.println("e7departmentmark:"+e7departmentmark+" e7departmentname:"+e7departmentname +" e7subcompanyid1:"+e7subcompanyid1+" e7supdepid:"+e7supdepid+" e7allsupdepid:"+e7allsupdepid+" e7canceled:"+e7canceled+" e7departmentcode:"+e7departmentcode+" e7tlevel:"+e7tlevel+" e7showorder:"+e7showorder+"</br>");
                        out.println("departmentmark:"+departmentmark+" departmentname:"+departmentname +" subcompanyid1:"+subcompanyOutKey+" supdepid:"+supdepidOutKey+" allsupdepid:"+allsupdepidOutKey+" canceled:"+canceled+" departmentcode:"+departmentcode+" tlevel:"+tlevel+" showorder:"+showorder+"</br>");

                        if(!e7departmentmark.equals(departmentmark)
                            || !e7departmentname.equals(departmentname)
                                || !e7subcompanyid1.equals(subcompanyOutKey)
                                || !e7supdepid.equals(supdepidOutKey)
                                || !e7canceled.equals(canceled)
                                || !e7departmentcode.equals(departmentcode)
                                || !e7allsupdepid.equals(allsupdepidOutKey)
                        ){
                            out.println("更新--outkey:"+outkey+"</br>");
                            E9Map.put("subcompanyid1", subcompanyOutKey);
                            E9Map.put("supdepid", supdepidOutKey);
                            E9Map.put("e7id", outkey);
                            topUpdateList.add(E9Map);
                        }else{
                            out.println("=====outkey:"+outkey+"没有变化的数据"+"</br>");
                        }

//                        if(!e7departmentmark.equals(departmentmark)
//                                || !e7departmentname.equals(departmentname)
//                                || !e7subcompanyid1.equals(subcompanyOutKey)
//                                || !e7supdepid.equals(supdepidOutKey)
//                                || !e7canceled.equals(canceled)
//                                || !e7departmentcode.equals(departmentcode)
//                                || !e7tlevel.equals(tlevel)
//                                || !e7showorder.equals(showorder)
//                                || !e7allsupdepid.equals(allsupdepidOutKey)
//                        ){
//                            out.println("更新--outkey:"+outkey+"</br>");
//                            E9Map.put("subcompanyid1", subcompanyOutKey);
//                            E9Map.put("supdepid", supdepidOutKey);
//                            E9Map.put("e7id", outkey);
//                            topUpdateList.add(E9Map);
//                        }else{
//                            out.println("=====outkey:"+outkey+"没有变化的数据"+"</br>");
//                        }

                    }else{
                        out.println("=====outkey:"+outkey+"的supdepid:"+supdepidOutKey+"在E7中不存在</br>");
                    }
                }else{
                    out.println("=====outkey:"+outkey+"subcompany:"+subcompanyOutKey+"在E7中不存在</br>");
                }
            }else{
                out.println("=====outkey:"+outkey+"在E7中不存在"+"</br>");
            }
        }else{

            if(StringUtils.isNotBlank(subcompanyOutKey) && E7SubcompanyList.contains(subcompanyOutKey) ) {
                if (StringUtils.isNotBlank(supdepidOutKey) && E7DepartmentMap.containsKey(supdepidOutKey)) {
                    E9Map.put("subcompanyid1", subcompanyOutKey);
                    E9Map.put("supdepid", supdepidOutKey);
                    topInsertList.add(E9Map);
                }
            }
        }
    }

    out.println("topUpdateList:"+topUpdateList.size()+"</br>");
    out.println("topInsertList:"+topInsertList.size()+"</br>");

    for(Map<String,Object> E9Map : topInsertList){
        String uuid = UUID.randomUUID().toString().replace("-","").toUpperCase();
        E9Map.put("modifier",modifier);
        E9Map.put("creater",creater);
        E9Map.put("UUID",uuid);

        String keyString = "";
        String valueString = "";
        Iterator<Map.Entry<String, Object>> it = E9Map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = Util.null2String(entry.getKey());
            String value = Util.null2String(entry.getValue());
            keyString += keyString == "" ? key : ","+key ;
            valueString += valueString == "" ? "'"+value+"'" : ",'"+value+"' ";
        }

        sql = " insert into hrmdepartment(modified,created,"+keyString+") values(systimestamp,systimestamp,"+valueString+")";
        out.println("sql:"+sql+"</br>");
    }


    for(Map<String,Object> E9Map : topUpdateList){
        String e7id = Util.null2String(E9Map.get("e7id"));
        String valuesStr = " modified=systimestamp,modifier=1";
        Iterator<Map.Entry<String, Object>> it = E9Map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = Util.null2String(entry.getKey());
            if(!"e7id".equals(key)){
                String value = Util.null2String(entry.getValue());
                valuesStr += valuesStr == "" ? key+"='"+value+"'" : ","+key+"='"+value+"'" ;
            }
        }
        sql = "update hrmdepartment set "+valuesStr+" where id = '"+e7id+"' ";
        out.println("sql:"+sql+"</br>");

    }

    out.println("top 执行结束 </br>");

    List<Map<String,Object>> insertList = new ArrayList<Map<String,Object>>();
    List<Map<String,Object>> updateList = new ArrayList<Map<String,Object>>();

    for(String departmentid : topList){
        if(StringUtils.isNotBlank(departmentid)){
            sql =   " select id,departmentmark,departmentname,subcompanyid1,supdepid,allsupdepid,canceled,departmentcode,outkey,tlevel,showorderoftree,showorder \n" +
                    " from hrmdepartment  \n" +
                    " connect by prior id = supdepid \n" +
                    " start with supdepid = "+departmentid;

            out.println("sql:"+sql+"</br>");
            rs.executeQuery(sql);
            while (rs.next()){
                String e9Id = Util.null2String(rs.getString("id"));
                String departmentmark = Util.null2String(rs.getString("departmentmark"));
                String departmentname = Util.null2String(rs.getString("departmentname"));
                String subcompanyid1 = Util.null2String(rs.getString("subcompanyid1"));
                String supdepid = Util.null2String(rs.getString("supdepid"));
                String allsupdepid = Util.null2String(rs.getString("allsupdepid"));
                String canceled = Util.null2String(rs.getString("canceled"));
                String departmentcode = Util.null2String(rs.getString("departmentcode"));
                String outkey = Util.null2String(rs.getString("outkey"));  //E7的hrmsubcompany的ID

                String tlevel = Util.null2String(rs.getString("tlevel"));
                String showorderoftree = Util.null2String(rs.getString("showorderoftree"));
                String showorder = Util.null2String(rs.getString("showorder"));

                out.println("e9Id:"+e9Id+""+"  departmentname:"+departmentname+"</br>");

                Map<String,Object> E9Map = new HashMap<String, Object>();
                E9Map.put("departmentname", departmentname);

                if (StringUtils.isNotBlank(departmentmark)) {
                    E9Map.put("departmentmark", departmentmark);
                }
                if (StringUtils.isNotBlank(canceled)) {
                    E9Map.put("canceled", canceled);
                }
                if (StringUtils.isNotBlank(departmentcode)) {
                    E9Map.put("departmentcode", departmentcode);
                }
                if(StringUtils.isNotBlank(canceled)){
                    E9Map.put("canceled", canceled);
                }

                if (StringUtils.isNotBlank(tlevel)) {
                    E9Map.put("tlevel", tlevel);
                }
                if (StringUtils.isNotBlank(showorderoftree)) {
                    E9Map.put("showorderoftree", showorderoftree);
                }
                if (StringUtils.isNotBlank(showorder)) {
                    E9Map.put("showorder", showorder);
                }

                out.println("subcompanyid1:"+subcompanyid1+"</br>");
                String subcompanyOutKey = "" ;
                if(subCompanyOutkeyMap.containsKey(subcompanyid1)){
                    subcompanyOutKey = subCompanyOutkeyMap.get(subcompanyid1);
                }

                String supdepidOutKey = "";
                if(StringUtils.isNotBlank(supdepid)){
                    if(departmentOutkeyMap.containsKey(supdepid)){
                        supdepidOutKey = departmentOutkeyMap.get(supdepid);
                    }
                }

                String allsupdepidOutKey = "";
                if(StringUtils.isNotBlank(allsupdepid)){
                    String[] supdepids = allsupdepid.split(",");
                    for(int i=0;i<supdepids.length;i++){
                        if(StringUtils.isNotBlank(supdepids[i])){
                            if(departmentOutkeyMap.containsKey(supdepids[i])){
                                String supdepid_OutKey = departmentOutkeyMap.get(supdepids[i]);
                                allsupdepidOutKey += StringUtils.isBlank(allsupdepidOutKey) ? supdepid_OutKey : ","+supdepid_OutKey ;
                            }
                        }
                    }
                }

                if(StringUtils.isNotBlank(outkey)){
                    if(E7DepartmentMap.containsKey(outkey)){
                        if(StringUtils.isNotBlank(subcompanyOutKey) && E7SubcompanyList.contains(subcompanyOutKey) ) {
                            if (StringUtils.isNotBlank(supdepidOutKey) && E7DepartmentMap.containsKey(supdepidOutKey)) {

                                Map<String,String> e7DeptMap = E7DepartmentMap.get(outkey);
                                String e7departmentmark = Util.null2String(e7DeptMap.get("e7departmentmark"));
                                String e7departmentname = Util.null2String(e7DeptMap.get("e7departmentname"));
                                String e7subcompanyid1 = Util.null2String(e7DeptMap.get("e7subcompanyid1"));
                                String e7supdepid = Util.null2String(e7DeptMap.get("e7supdepid"));
                                String e7allsupdepid = Util.null2String(e7DeptMap.get("e7allsupdepid"));
                                String e7canceled = Util.null2String(e7DeptMap.get("e7canceled"));
                                String e7departmentcode = Util.null2String(e7DeptMap.get("e7departmentcode"));
                                String e7tlevel = Util.null2String(e7DeptMap.get("e7tlevel"));
                                String e7showorder = Util.null2String(e7DeptMap.get("e7showorder"));

                                out.println("e7departmentmark:"+e7departmentmark+" e7departmentname:"+e7departmentname +" e7subcompanyid1:"+e7subcompanyid1+" e7supdepid:"+e7supdepid+" e7allsupdepid:"+e7allsupdepid+" e7canceled:"+e7canceled+" e7departmentcode:"+e7departmentcode+" e7tlevel:"+e7tlevel+" e7showorder:"+e7showorder);
                                out.println("departmentmark:"+departmentmark+" departmentname:"+departmentname +" subcompanyid1:"+subcompanyid1+" supdepid:"+supdepid+" allsupdepid:"+allsupdepid+" canceled:"+canceled+" departmentcode:"+departmentcode+" tlevel:"+tlevel+" showorderoftree:"+showorderoftree+" showorder:"+showorder);

//                                if(!e7departmentmark.equals(departmentmark)
//                                        || !e7departmentname.equals(departmentname)
//                                        || !e7subcompanyid1.equals(subcompanyOutKey)
//                                        || !e7supdepid.equals(supdepidOutKey)
//                                        || !e7canceled.equals(canceled)
//                                        || !e7departmentcode.equals(departmentcode)
//                                        || !e7tlevel.equals(tlevel)
//                                        || !e7showorder.equals(showorder)
//                                        || !e7allsupdepid.equals(allsupdepidOutKey)
//                                ){
//                                    out.println("更新--outkey:"+outkey+"</br>");
//                                    E9Map.put("subcompanyid1", subcompanyOutKey);
//                                    E9Map.put("supdepid", supdepidOutKey);
//                                    E9Map.put("e7id", outkey);
//                                    updateList.add(E9Map);
//                                }else{
//                                    out.println("=====outkey:"+outkey+"没有变化的数据"+"</br>");
//                                }

                                if(!e7departmentmark.equals(departmentmark)
                                        || !e7departmentname.equals(departmentname)
                                        || !e7subcompanyid1.equals(subcompanyOutKey)
                                        || !e7supdepid.equals(supdepidOutKey)
                                        || !e7canceled.equals(canceled)
                                        || !e7departmentcode.equals(departmentcode)
                                        || !e7allsupdepid.equals(allsupdepidOutKey)
                                ){
                                    out.println("更新--outkey:"+outkey+"</br>");
                                    E9Map.put("subcompanyid1", subcompanyOutKey);
                                    E9Map.put("supdepid", supdepidOutKey);
                                    E9Map.put("e7id", outkey);
                                    updateList.add(E9Map);
                                }else{
                                    out.println("=====outkey:"+outkey+"没有变化的数据"+"</br>");
                                }

                            }else{
                                out.println("=====outkey:"+outkey+"的supdepid:"+supdepidOutKey+"在E7中不存在</br>");
                            }
                        }else{
                            out.println("=====outkey:"+outkey+"subcompany:"+subcompanyOutKey+"在E7中不存在</br>");
                        }
                    }else{
                        out.println("=====outkey:"+outkey+"在E7中不存在"+"</br>");
                    }
                }else{

                    if(StringUtils.isNotBlank(subcompanyOutKey) && E7SubcompanyList.contains(subcompanyOutKey) ) {
                        if (StringUtils.isNotBlank(supdepidOutKey) && E7DepartmentMap.containsKey(supdepidOutKey)) {
                            E9Map.put("subcompanyid1", subcompanyOutKey);
                            E9Map.put("supdepid", supdepidOutKey);
                            insertList.add(E9Map);
                        }
                    }
                }
            }
        }
    }

    out.println("insertList:"+insertList.size()+"</br>");
    out.println("updateList:"+updateList.size()+"</br>");

    for(Map<String,Object> E9Map : insertList){
        String uuid = UUID.randomUUID().toString().replace("-","").toUpperCase();
        E9Map.put("modifier",modifier);
        E9Map.put("creater",creater);
        E9Map.put("UUID",uuid);

        String keyString = "";
        String valueString = "";
        Iterator<Map.Entry<String, Object>> it = E9Map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = Util.null2String(entry.getKey());
            String value = Util.null2String(entry.getValue());
            keyString += keyString == "" ? key : ","+key ;
            valueString += valueString == "" ? "'"+value+"'" : ",'"+value+"' ";
        }

        sql = " insert into hrmsubcompany(modified,created,"+keyString+") values(systimestamp,systimestamp,"+valueString+")";
        out.println("sql:"+sql+"</br>");
    }


    for(Map<String,Object> E9Map : updateList){
        String e7id = Util.null2String(E9Map.get("e7id"));
        String valuesStr = " modified=systimestamp,modifier=1";
        Iterator<Map.Entry<String, Object>> it = E9Map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = Util.null2String(entry.getKey());
            if(!"e7id".equals(key)){
                String value = Util.null2String(entry.getValue());
                valuesStr += valuesStr == "" ? key+"='"+value+"'" : ","+key+"='"+value+"'" ;
            }
        }
        sql = "update hrmsubcompany set "+valuesStr+" where id = '"+e7id+"' ";
        out.println("sql:"+sql+"</br>");
    }

%>

<%!
    public Map<String,String> getE9SubcompanyOutkey(){

        RecordSet rs = new RecordSet();
        Map<String,String> outkeyMap = new HashMap<String,String>();
        String sql = " select id,outkey from hrmsubcompany " ;
        rs.executeQuery(sql);
        while (rs.next()){
            String id = Util.null2String(rs.getString("id"));
            String outkey = Util.null2String(rs.getString("outkey"));
            outkeyMap.put(id,outkey);
        }
        return outkeyMap;
    }


    public Map<String,String> getE9DepartmentOutkey(){

        RecordSet rs = new RecordSet();
        Map<String,String> outkeyMap = new HashMap<String,String>();
        String sql = " select id,outkey from hrmdepartment " ;
        rs.executeQuery(sql);
        while (rs.next()){
            String id = Util.null2String(rs.getString("id"));
            String outkey = Util.null2String(rs.getString("outkey"));
            outkeyMap.put(id,outkey);
        }
        return outkeyMap;
    }



    public Map<String,Map<String,String>> getE7DepartmentData(String dataResource){

        RecordSetDataSource rsd = new RecordSetDataSource(dataResource);
        Map<String,Map<String,String>> E7DepartmentMap = new HashMap<String,Map<String,String>>();
        String sql = " select id,departmentmark,departmentname,subcompanyid1,supdepid,allsupdepid,canceled,departmentcode,tlevel,showorder from hrmdepartment " ;
        rsd.execute(sql);
        while (rsd.next()){
            String id = Util.null2String(rsd.getString("id"));
            String e7departmentmark = Util.null2String(rsd.getString("departmentmark"));
            String e7departmentname = Util.null2String(rsd.getString("departmentname"));
            String e7subcompanyid1 = Util.null2String(rsd.getString("subcompanyid1"));
            String e7supdepid = Util.null2String(rsd.getString("supdepid"));
            String e7allsupdepid = Util.null2String(rsd.getString("allsupdepid"));
            String e7canceled = Util.null2String(rsd.getString("canceled"));
            String e7departmentcode = Util.null2String(rsd.getString("departmentcode"));
            String e7tlevel = Util.null2String(rsd.getString("departmentcode"));
            String e7showorder = Util.null2String(rsd.getString("departmentcode"));

            Map<String,String> deptMap= new HashMap<String,String>();
            deptMap.put("e7departmentmark",e7departmentmark);
            deptMap.put("e7departmentname",e7departmentname);
            deptMap.put("e7subcompanyid1",e7subcompanyid1);
            deptMap.put("e7supdepid",e7supdepid);
            deptMap.put("e7allsupdepid",e7allsupdepid);
            deptMap.put("e7canceled",e7canceled);
            deptMap.put("e7departmentcode",e7departmentcode);
            deptMap.put("e7tlevel",e7tlevel);
            deptMap.put("e7showorder",e7showorder);

            E7DepartmentMap.put(id,deptMap);
        }
        return E7DepartmentMap;
    }

    /***
     *
     * @return
     */
    public List<String> getE7HrmSubcompanyList(String dataResource){
        RecordSetDataSource rsd = new RecordSetDataSource(dataResource);
        List<String> E7SubcompanyList = new ArrayList<String>();
        String sql = " select id from hrmsubcompany " ;
        rsd.execute(sql);
        while (rsd.next()){
            String id = Util.null2String(rsd.getString("id"));
            E7SubcompanyList.add(id);
        }
        return E7SubcompanyList;
    }


%>




