<%@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" %>
<%@ page import="weaver.general.BaseBean" %>
<%@ page import="weaver.formmode.setup.ModeRightInfo" %>
<%@ page import="java.text.SimpleDateFormat" %>
<jsp:useBean id="bb" class="weaver.general.BaseBean" scope="page" />
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%
    String dataResource = "E7TEST";
    RecordSetDataSource rsd = new RecordSetDataSource(dataResource);
    RecordSet rs = new RecordSet();

    int totle_count = 0;
    int update_success_count = 0;
    int update_fail_count = 0;
    int update_exp_count = 0;
    int insert_success_count = 0;
    int insert_fail_count = 0;
    int insert_exp_count = 0;

    int unchanged_count = 0;
    int data_error_count = 0;

    List<String> dataErrorList = new ArrayList<String>();
    List<Map<String,String>> sqlList = new ArrayList<Map<String,String>>();

    List<Map<String,Object>> insertTitleList = new ArrayList<Map<String,Object>>();
    List<Map<String,Object>> updateTitleList = new ArrayList<Map<String,Object>>();

    Map<String,String> E9JobActivitiesMap = getE9HrmJobActivitiesList();
    Map<String,String> E9DepartmentMap = getE9DepartmentOutkey();
    List<String> E7HrmactivitiesList = getE7HrmActivitiesList(dataResource);

    out.println("=====E9JobActivitiesMap:"+E9JobActivitiesMap.size()+"</br>");
    out.println("=====E9DepartmentMap:"+E9DepartmentMap.size()+""+"</br>");
    out.println("=====E7HrmactivitiesList:"+E7HrmactivitiesList.size()+""+"</br>");

    Map<String,Map<String,String>> E7JobTitlesMap = getE7HrmTitlesList2(dataResource);
    out.println("=====E7JobTitlesMap:"+E7JobTitlesMap.size()+""+"</br>");
    String sql =" select t.id,t.jobtitlemark,t.jobtitlename,t.jobactivityid,t.jobdepartmentid,t.outkey,t.jobtitlecode \n" +
                " from hrmjobtitles t\n" +
                " where t.jobdepartmentid is not null\n" ;

    out.println("e9--sql:"+sql+"</br>");
    rs.execute(sql);
    while(rs.next()) {
        totle_count++;

        String e9id = Util.null2String(rs.getString("id"));
        String jobtitlename = Util.null2String(rs.getString("jobtitlename")).trim();
        String jobtitlemark = Util.null2String(rs.getString("jobtitlemark")).trim();
        String jobactivityid = Util.null2String(rs.getString("jobactivityid"));
        String jobdepartmentid = Util.null2String(rs.getString("jobdepartmentid"));
        String outkey = Util.null2String(rs.getString("outkey"));
        String jobtitlecode = Util.null2String(rs.getString("jobtitlecode"));
        String departmentid = Util.null2String(rs.getString("jobtitlecode"));

        Map<String,Object> E9Map = new HashMap<String,Object>();
        E9Map.put("e9id", e9id);
        E9Map.put("jobtitlename", jobtitlename);
        E9Map.put("jobtitlemark", jobtitlemark);
        E9Map.put("jobtitlecode", jobtitlecode);

        String jobactivityOutkey = "";
        if(StringUtils.isNotBlank(jobactivityid)){
            if(E9JobActivitiesMap.containsKey(jobactivityid)){
                jobactivityOutkey = E9JobActivitiesMap.get(jobactivityid);
            }
        }

        String departmentOutkey = "";
        if(StringUtils.isNotBlank(jobdepartmentid)){
            if(E9DepartmentMap.containsKey(jobdepartmentid)){
                departmentOutkey = E9DepartmentMap.get(jobdepartmentid);
            }
        }

        if(StringUtils.isNotBlank(outkey)){
            if(E7JobTitlesMap.containsKey(outkey)){
                if(StringUtils.isNotBlank(jobactivityOutkey) && E7HrmactivitiesList.contains(jobactivityOutkey)){
                    Map<String,String> jobTitlesMap = E7JobTitlesMap.get(outkey);
                    String e7jobtitlemark = Util.null2String(jobTitlesMap.get("e7jobtitlemark")).trim();
                    String e7jobtitlename = Util.null2String(jobTitlesMap.get("e7jobtitlename")).trim();
                    String e7jobactivityid = Util.null2String(jobTitlesMap.get("e7jobactivityid")).trim();
                    String e7jobdepartmentid = Util.null2String(jobTitlesMap.get("e7jobdepartmentid")).trim();
                    String e7jobtitlecode = Util.null2String(jobTitlesMap.get("e7jobtitlecode")).trim();

                    out.println("e7jobtitlemark:"+e7jobtitlemark+" e7jobtitlename:"+e7jobtitlename+" e7jobactivityid:"+e7jobactivityid+" e7jobdepartmentid:"+e7jobdepartmentid+" e7jobtitlecode:"+e7jobtitlecode+"</br>" );
                    out.println("jobtitlemark:"+jobtitlemark+" jobtitlename:"+jobtitlename+" jobactivityid:"+jobactivityOutkey+" jobdepartmentid:"+departmentOutkey +" jobtitlecode:"+jobtitlecode+"</br>");

                    if(!e7jobtitlemark.equals(jobtitlemark)
                        || !e7jobtitlename.equals(jobtitlename)
                        || !e7jobactivityid.equals(jobactivityOutkey)
                        || !e7jobdepartmentid.equals(departmentOutkey)
                        || !e7jobtitlecode.equals(jobtitlecode)
                    ){
                        E9Map.put("e7id", outkey);
                        E9Map.put("jobdepartmentid", departmentOutkey);
                        E9Map.put("jobactivityid", jobactivityOutkey);
                        updateTitleList.add(E9Map);
                    }else{
                        unchanged_count++;
                    }
                }else{
                    data_error_count++;
                    dataErrorList.add(e9id);
                }
            }else{
                if(StringUtils.isNotBlank(jobactivityOutkey) && E7HrmactivitiesList.contains(jobactivityOutkey)){
                    E9Map.put("jobdepartmentid", departmentOutkey);
                    E9Map.put("jobactivityid", jobactivityOutkey);
                    insertTitleList.add(E9Map);
                }else{
                    data_error_count++;
                    dataErrorList.add(e9id);
                }
            }
        }else{
            if(StringUtils.isNotBlank(jobactivityOutkey) && E7HrmactivitiesList.contains(jobactivityOutkey)){
                E9Map.put("jobdepartmentid", departmentOutkey);
                E9Map.put("jobactivityid", jobactivityOutkey);
                insertTitleList.add(E9Map);
            }else{
                data_error_count++;
                dataErrorList.add(e9id);
            }
        }
    }

    out.println("updateTitleList:"+updateTitleList.size());
    out.println("insertTitleList:"+insertTitleList.size());

    for(Map<String,Object> item : insertTitleList)
    {
        String e9id = Util.null2String(item.get("e9id"));
        String keyString = "";
        String valueString = "";
        Iterator<Map.Entry<String, Object>> it = item.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = Util.null2String(entry.getKey());
            String value = Util.null2String(entry.getValue());
            if(!"e7id".equals(key) && !"e9id".equals(key)){
                keyString += keyString == "" ? key : ","+key ;
                valueString += valueString == "" ? "'"+value+"'" : ",'"+value+"' ";
            }
        }

        boolean isTrue2 = false;
        String insert_sql = " insert into hrmjobtitles("+keyString+") values("+valueString+")";
        out.println("insert_sql:"+insert_sql+"</br>");
        String newE7Id = insertE7Data(rsd,insert_sql,insert_fail_count);
        out.println("newE7Id:"+newE7Id+"</br>");
        if(StringUtils.isNotBlank(newE7Id)){
            sql = "update hrmjobtitles set outkey= '"+newE7Id+"' where id = "+e9id;
            isTrue2 = rs.executeUpdate(sql);
            out.println("sql:"+sql+"  "+isTrue2+"</br>");
            if(isTrue2){
                insert_success_count++;
            }else{
                insert_exp_count++;
            }
        }else{
            insert_exp_count++;
        }

        Map<String,String> sqlMap = new HashMap<String,String>();
        sqlMap.put("zxyj",insert_sql);
        sqlMap.put("dataid",e9id);
        sqlMap.put("zxjg",isTrue2 ? "true" : "false");
        sqlList.add(sqlMap);
    }

    for(Map<String,Object> item : updateTitleList) {
        String e7id = Util.null2String(item.get("e7id"));
        String e9id = Util.null2String(item.get("e9id"));
        String valuesStr = "";
        Iterator<Map.Entry<String, Object>> it = item.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry<String, Object> entry = it.next();
            String key = Util.null2String(entry.getKey());
            if(!"e7id".equals(key) && !"e9id".equals(key)){
                String value = Util.null2String(entry.getValue());
                valuesStr += valuesStr == "" ? key + "='" + value + "'" : "," + key + "='" + value + "'";
            }
        }

        String update_sql = " update hrmjobtitles set "+valuesStr+" where id = '"+e7id+"' ";
        boolean isTrue = updateE7SubcompanyData(rsd,update_sql);
        out.println("update_sql:"+update_sql+" "+isTrue+"</br>");
        if(isTrue){
            update_success_count++;
        }else{
            update_fail_count++;
        }

        Map<String,String> sqlMap = new HashMap<String,String>();
        sqlMap.put("zxyj",sql);
        sqlMap.put("dataid",update_sql);
        sqlMap.put("zxjg",isTrue ? "true" : "false");
        sqlList.add(sqlMap);
    }


    String gwtbjg = "分部共:"+totle_count+"行数据,数据一致工:"+unchanged_count+"。新增成功:"+insert_success_count+",新增失败:"+insert_fail_count+",新增异常:"+insert_exp_count+"。"+
                    "更新成功:"+update_success_count+",更新失败:"+update_fail_count+",更新异常:"+update_exp_count+"。"+
                    "数据异常(ID在E7中不存在):"+data_error_count ;

    Map<String,String> mainMap = new HashMap<String,String>();
    mainMap.put("gwtbjg",gwtbjg);
    if(dataErrorList.size() > 0){
        String gwycsj = String.join(",", dataErrorList);
        mainMap.put("gwycsj",gwycsj);
    }
    String mainid = recordSynMainlog("",mainMap);
    out.println("mainid:"+mainid);
    if(StringUtils.isNotBlank(mainid)){
        insertLogDetail5(mainid,sqlList);
    }

%>

<%!

    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;
    }


    /***
     *
     * @return
     */
    public List<String> getE7HrmActivitiesList(String dataResource){

        RecordSetDataSource rsd = new RecordSetDataSource(dataResource);
        List<String> E7HrmactivitiesList = new ArrayList<String>();
        String sql = " select id from hrmjobactivities " ;
        rsd.execute(sql);
        while (rsd.next()){
            String id = Util.null2String(rsd.getString("id"));
            E7HrmactivitiesList.add(id);
        }
        return E7HrmactivitiesList;
    }


    public List<String> getE7HrmTitlesList(String dataResource){

        RecordSetDataSource rsd = new RecordSetDataSource(dataResource);
        List<String> E7JobtitlesList = new ArrayList<String>();
        String sql = " select id from hrmjobactivities " ;
        rsd.execute(sql);
        while (rsd.next()){
            String id = Util.null2String(rsd.getString("id"));
            E7JobtitlesList.add(id);
        }
        return E7JobtitlesList;
    }


    public Map<String,String> getE9HrmJobActivitiesList(){

        RecordSet rs = new RecordSet();
        Map<String,String> E7JobtitlesMap = new HashMap<String,String>();
        String sql =" select id,outkey from hrmjobactivities where id in(select jobactivityid from hrmjobtitles) ";
        rs.execute(sql);
        while (rs.next()){
            String id = Util.null2String(rs.getString("id"));
            String outkey = Util.null2String(rs.getString("outkey"));
            E7JobtitlesMap.put(id,outkey);
        }
        return E7JobtitlesMap;
    }


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

        RecordSetDataSource rsd = new RecordSetDataSource(dataResource);
        Map<String,Map<String,String>> E7JobtitlesMap = new HashMap<String,Map<String,String>>();
        String sql = " select id,jobtitlemark,jobtitlename,jobactivityid,jobdepartmentid,jobtitlecode from hrmjobtitles " ;
        rsd.execute(sql);
        while (rsd.next()){
            String id = Util.null2String(rsd.getString("id"));
            String e7jobtitlemark = Util.null2String(rsd.getString("jobtitlemark"));
            String e7jobtitlename = Util.null2String(rsd.getString("jobtitlename"));
            String e7jobactivityid = Util.null2String(rsd.getString("jobactivityid"));
            String e7jobdepartmentid = Util.null2String(rsd.getString("jobdepartmentid"));
            String e7jobtitlecode = Util.null2String(rsd.getString("jobtitlecode"));

            Map<String,String> jobtitlesMap = new HashMap<String,String>();
            jobtitlesMap.put("e7jobtitlemark",e7jobtitlemark);
            jobtitlesMap.put("e7jobtitlename",e7jobtitlename);
            jobtitlesMap.put("e7jobactivityid",e7jobactivityid);
            jobtitlesMap.put("e7jobdepartmentid",e7jobdepartmentid);
            jobtitlesMap.put("e7jobtitlecode",e7jobtitlecode);
            E7JobtitlesMap.put(id,jobtitlesMap);
        }
        return E7JobtitlesMap;
    }


    public String insertE7Data(RecordSetDataSource rsd,String insert_sql,int insert_fail_count){
        String newE7Id = "";
        BaseBean bb = new BaseBean();
        bb.writeLog("insert-sql:"+insert_sql);
        boolean isTrue = rsd.execute(insert_sql);
        if(isTrue){
            String sql = " select max(id) as id from hrmjobtitles ";
            rsd.executeSql(sql);
            if(rsd.next()){
                newE7Id = Util.null2String(rsd.getString("id"));
            }
        }else{
            insert_fail_count++;
        }
        return newE7Id;
    }


    public boolean updateE7SubcompanyData(RecordSetDataSource rsd,String update_sql){
        BaseBean bb = new BaseBean();
        bb.writeLog("update-sql:"+update_sql);
        boolean isTrue = rsd.execute(update_sql);
        return isTrue;
    }


    private String recordSynMainlog(String id,Map<String,String> mainMap){
        RecordSet rs = new RecordSet();
        BaseBean bb = new BaseBean();
        String dataid = "" ;
        String uf_table = "uf_zzjgtbrz";
        String modedatacreater = "1";
        SimpleDateFormat sdfDate = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
        SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");//设置日期格式
        String modedatacreatedate = sdfDate.format(new Date());
        String modedatacreatetime = sdfTime.format(new Date());
        String modedatacreatertype = "0";

        String formmodeid = "" ;
        String sql =" select k.id from modeinfo k  \n" +
                " inner join workflow_bill l on formid = l.id\n" +
                " where l.tablename = '"+uf_table+"' " ;
        rs.execute(sql);
        if(rs.next()){
            formmodeid = Util.null2String(rs.getString("id"));
        }

        if(StringUtils.isBlank(id)){

            ModeRightInfo modeRightInfo = new ModeRightInfo();
            modeRightInfo.setNewRight(true);

            String uuid = UUID.randomUUID().toString();
            mainMap.put("uuid",uuid);
            mainMap.put("formmodeid",formmodeid);
            mainMap.put("modedatacreater",modedatacreater);
            mainMap.put("modedatacreatertype",modedatacreatertype);
            mainMap.put("modedatacreatedate",modedatacreatedate);
            mainMap.put("modedatacreatetime",modedatacreatetime);

            String tablename = "" ;
            String tablenvalue = "" ;
            for (Map.Entry<String, String> entry : mainMap.entrySet())
            {
                String fieldname = Util.null2String(entry.getKey()).trim();
                String fieldvalue = Util.null2String(entry.getValue()).replace("'", "''").trim();
                tablename += tablename==""? fieldname :","+fieldname ;
                tablenvalue += tablenvalue=="" ? "'"+fieldvalue+"'" :",'"+fieldvalue+"'" ;
            }
            if(tablename !="" && tablenvalue !="")
            {
                sql = " insert into "+uf_table+"("+tablename+") values ("+ tablenvalue +")";
                boolean boo = rs.execute(sql);
                bb.writeLog("insert-istrue:"+boo);
                if(boo)
                {

                    sql = " select * from "+uf_table+" where uuid='"+uuid+"' " ;
                    bb.writeLog("sql:"+sql);
                    rs.execute(sql);
                    if(rs.next()){
                        dataid = Util.null2String(rs.getString("id"));
                    }
                    bb.writeLog("dataid:"+dataid);
                    if(!"".equals(dataid))
                    {
                        modeRightInfo.editModeDataShare(Integer.valueOf(modedatacreater), Integer.parseInt(formmodeid), Integer.parseInt(dataid));
                    }
                }
            }
        }else{
            dataid = id ;
            String valuesStr = "";
            Iterator<Map.Entry<String, String>> it = mainMap.entrySet().iterator();
            while (it.hasNext()) {
                Map.Entry<String, String> entry = it.next();
                String key = Util.null2String(entry.getKey());
                String value = Util.null2String(entry.getValue());
                valuesStr += valuesStr == "" ? key+"='"+value+"'" : ","+key+"='"+value+"'" ;
            }

            if(StringUtils.isNotBlank(valuesStr)){
                sql = "update "+uf_table+" set " + valuesStr + " where id = "+id+"";
                boolean isTrue = rs.execute(sql);
                bb.writeLog("update-istrue:"+isTrue);
            }
        }
        return dataid;
    }


    /***
     *
     * @param dt1List
     * @param mainid
     */
    public void insertLogDetail5(String mainid,List<Map<String,String>> dt1List){
        BaseBean bb = new BaseBean();
        RecordSet rs = new RecordSet();
        for(int i=0;i<dt1List.size();i++){
            Map<String,String> dt1Map = dt1List.get(i);
            String dataid = dt1Map.get("dataid");
            String zxyj = dt1Map.get("zxyj");
            if(StringUtils.isNotBlank(zxyj) && zxyj.contains("'")){
                zxyj = zxyj.replace("'", "''");
            }
            String zxjg = dt1Map.get("zxjg");

            String sql = " insert into uf_zzjgtbrz_dt5(mainid,gwid,zxyj,zxjg) values("+mainid+",'"+dataid+"','"+zxyj+"','"+zxjg+"')" ;
            bb.writeLog("sql:"+sql);
            boolean istrue = rs.executeUpdate(sql);
            bb.writeLog("istrue:"+istrue);
        }
    }

%>




