設定好角色欄位後，接下來就是大量同步BPM角色名單的簽核主管
因此下方程式執行的步驟
1.讀取BPM連線字串(可自行修改方式)
2.讀取BPM主管(SQL)
3.解析職級&遞迴上階主管
4.修改儲存角色

--SQL讀取BPM每一個人的簽核主管
SELECT 
users_occu.userName as Occu
	,users_occu.id as EMP_ID
	,users_occu.ldapid as LDAP_ID
    ,level.levelValue
	,level.functionLevelName
	,funcDef.functionDefinitionName
	,org_unit.organizationUnitName as DeptName
	,org_unit_super.organizationUnitName as SuperDeptName
	,org_emp.organizationName as OrgName
    ,users.userName as Manager
    ,[isMain]
FROM [Functions] as func
left join [OrganizationUnit] as org_unit on org_unit.OID = func.organizationUnitOID
left join [OrganizationUnit] as org_unit_super on org_unit_super.OID = org_unit.superUnitOID
left join [FunctionDefinition] as funcDef on funcDef.OID = func.definitionOID
left join Users as users on users.OID = func.specifiedManagerOID
left join Users as users_occu on users_occu.OID = func.occupantOID
left join FunctionLevel as level on level.OID = func.approvalLevelOID
left join Organization as org_emp on org_emp.OID = org_unit.organizationOID
order by Occu,isMain

Method

inn = this.getInnovator();
return main(this);
}
//===================================================
private void Log(string msg)
{
    Item log = inn.newItem("JPC_Method_Log", "add");
    log.setProperty("jpc_run_method", "JPC_DBTrasfer_BPMToAras_Manager");
    log.setProperty("jpc_method_event", "schedule");
    log.setProperty("jpc_log", msg);
    log = log.apply();
}
//讀取資料庫連線字串
private Item GetDatabaseSetup(string name){
    Item itm = inn.newItem("JPC_TransferData","get");
         itm.setProperty("name",name);
         itm = itm.apply();
         return itm;
}

private void TransferToAras(DataTable dt)
{
    string error_msg = "";
    for(int i = 0; i < dt.Rows.Count; i++)
    {
        DataRow row = dt.Rows[i];
        string employee = row["Employee"].ToString();
        string nameEng = row["NameEng"].ToString();
        string emp_id = row["EMP_ID"].ToString();
        string ldap_id = row["LDAP_ID"].ToString();
        string org_id = row["ORG_ID"].ToString();
        string[] level = new string[10];
        for(int j = 0; j < 10; j++)
        {
            level[j] = row["cn_level" + j + "_manager"].ToString();
        }
        Item itmIdent = inn.newItem("Identity", "get");
            itmIdent.setProperty("name", "*"+employee);
            itmIdent.setPropertyCondition("name", "like");
            itmIdent.setProperty("cn_ldap_id", ldap_id);
            itmIdent = itmIdent.apply();

        if (itmIdent.isError())
        {
            error_msg += employee+":名稱不存在\r\n";
        }
        else
        {
            for(int r=0;r< itmIdent.getItemCount(); r++)
            {
                Item ident = itmIdent.getItemByIndex(r);
                Item updateItm = inn.newItem("Identity", "edit");
                    updateItm.setAttribute("id", ident.getID());
                for(int c=0;c< level.Length; c++)
                {
                    if (level[c] != "")
                    {
                        Item findIdent = inn.newItem("Identity", "get");
                            findIdent.setProperty("name", "*" + level[c]);
                            findIdent.setPropertyCondition("name", "like");
                            findIdent = findIdent.apply();
                            if (findIdent.isError() == false)
                            {
                                updateItm.setProperty("cn_level" + c + "_manager", findIdent.getItemByIndex(0).getID());
                            }
                    }
                    else
                    {
                        string sql = @"Update [innovator].[Identity] set cn_level"+c+"_manager=null where id='"+ident.getID()+"'";
                        inn.applySQL(sql);
                    }
                }
                updateItm.setProperty("cn_org_id",org_id);
                updateItm.setProperty("cn_ldap_id",ldap_id);
                updateItm.setProperty("cn_emp_id",emp_id);
                updateItm = updateItm.apply();
            }
        }
    }
    Log(error_msg);
}
public void ToCSV(DataTable dtDataTable, string strFilePath) {  
    StreamWriter sw = new StreamWriter(strFilePath, false,Encoding.UTF8);  
    //headers    
    for (int i = 0; i < dtDataTable.Columns.Count; i++) {  
        sw.Write(dtDataTable.Columns[i]);  
        if (i < dtDataTable.Columns.Count - 1) {  
            sw.Write(",");  
        }  
    }  
    sw.Write(sw.NewLine);  
    foreach(DataRow dr in dtDataTable.Rows) {  
        for (int i = 0; i < dtDataTable.Columns.Count; i++) {  
            if (!Convert.IsDBNull(dr[i])) {  
                string value = dr[i].ToString();  
                if (value.Contains(',')) {  
                    value = String.Format("\"{0}\"", value);  
                    sw.Write(value);  
                } else {  
                    sw.Write(dr[i].ToString());  
                }  
            }  
            if (i < dtDataTable.Columns.Count - 1) {  
                sw.Write(",");  
            }  
        }  
        sw.Write(sw.NewLine);  
    }  
    sw.Close();  
}
private bool RunInputAras(Item setup){
    try{
        string source_sql = setup.getProperty("jpc_source_sql","");
        
        DataTable dtS = RunMSSql(source_sql);
        ToCSV(dtS,@"D:\TransferArasToEPB\BPM\BPM_Employee.csv");
        //return true;
        DataTable dt = new DataTable();
            dt.Columns.Add("Employee");
            dt.Columns.Add("NameEng");
            dt.Columns.Add("EMP_ID");
            dt.Columns.Add("LDAP_ID");
            dt.Columns.Add("ORG_ID");
            dt.Columns.Add("cn_level0_manager");
            dt.Columns.Add("cn_level1_manager");
            dt.Columns.Add("cn_level2_manager");
            dt.Columns.Add("cn_level3_manager");
            dt.Columns.Add("cn_level4_manager");
            dt.Columns.Add("cn_level5_manager");
            dt.Columns.Add("cn_level6_manager");
            dt.Columns.Add("cn_level7_manager");
            dt.Columns.Add("cn_level8_manager");
            dt.Columns.Add("cn_level9_manager");
            dt.TableName = "Employee";
        if(dtS.Rows.Count>0){
            DataColumnCollection columns = dtS.Columns;
            foreach (DataRow row in dtS.Rows)
            {
                
                DataRow dtRow = dt.NewRow();
                string occu = row["Occu"].ToString();
                string emp_id = row["EMP_ID"].ToString();
                string ldap_id = row["LDAP_ID"].ToString();
                string level = row["functionLevelName"].ToString();
                string dept_name = row["DeptName"].ToString();
                string super_dept = row["SuperDeptName"].ToString();
                string manager = row["Manager"].ToString();
                string isMain = row["isMain"].ToString();
                string org_name = row["OrgName"].ToString();
                if (isMain == "0") continue;
                
                occu = occu.Replace("（", "(");
                occu = occu.Replace("）", ")");
                int splitIndex = occu.IndexOf('(');
                int splitEndIndex = occu.IndexOf(')');

                if (splitIndex > 0)
                {
                    try{
                        //error_log += occu.Substring(splitIndex+1,splitEndIndex-splitIndex-1)+"\r\n";
                        if(splitEndIndex>0){
                            dtRow["NameEng"] = occu.Substring(splitIndex+1,splitEndIndex-splitIndex);
                        }else{
                            dtRow["NameEng"] = occu.Substring(splitIndex+1,occu.Count()-splitIndex);
                        }
                        dtRow["NameEng"] = dtRow["NameEng"].ToString().Trim();
                        if(dtRow["NameEng"].ToString().IndexOf('_')>0){
                            int nameIndexOf = dtRow["NameEng"].ToString().IndexOf('_');
                            dtRow["NameEng"] = dtRow["NameEng"].ToString().Substring(0,nameIndexOf);
                        }
                        occu = occu.Substring(0, splitIndex);
                        JPCChineseConverter.SimpleConvert jpc_convert = new JPCChineseConverter.SimpleConvert();
                        occu = jpc_convert.SimpleToTraditional(occu);
                    }catch{
                        error_log += "Error Length="+occu+"\r\n";
                    }
                    
                }
                dtRow["Employee"] = occu;
                dtRow["EMP_ID"] = emp_id;
                dtRow["LDAP_ID"] = ldap_id;
                dtRow["ORG_ID"] ="";
                

                dtRow = ReturnManager(dtS, dtRow,occu, manager,org_name,dept_name);
                
                dt.Rows.Add(dtRow);
            }
            TransferToAras(dt);
        }
        return true;
    }catch(Exception ex){
        error_log+="RunInputAras:"+ex.ToString();
        return false;
    }
}
private DataRow ReturnManager(DataTable wsheet, DataRow dtRow,string occu, string manager,string org_name,string dept_name)
{
    if (manager == "NULL")
    {
        return dtRow;
    }
    else
    {
        DataRow[] isResultManager = wsheet.Select("Occu ='" + manager + "' and OrgName='"+org_name+"' and DeptName='"+dept_name+"'");
        if (isResultManager.Count() >  0)
        {
            //Log("Occu ='" + manager + "' and OrgName='"+org_name+"' and DeptName='"+dept_name+"'");
            string occu2 = isResultManager[0]["Occu"].ToString();
            string dept_name2 = isResultManager[0]["DeptName"].ToString();
            string super_dept2 = isResultManager[0]["SuperDeptName"].ToString();
            string level_name = isResultManager[0]["functionLevelName"].ToString();
            string manager_name = isResultManager[0]["Manager"].ToString();
            manager = manager.Replace("（", "(");
            int splitIndex = manager.IndexOf('(');
            if (splitIndex > 0)
            {
                manager = manager.Substring(0, splitIndex);
            }
            switch (level_name)
            {
                case "董事長級":
                    dtRow["cn_level0_manager"] = manager;
                    break;
                case "總經理級":
                    dtRow["cn_level1_manager"] = manager;
                    break;
                case "BU副總經理":
                    dtRow["cn_level2_manager"] = manager;
                    break;
                case "集團主管":
                    dtRow["cn_level3_manager"] = manager;
                    break;
                case "處長/協理/副總級":
                    dtRow["cn_level4_manager"] = manager;
                    break;
                case "資深經理級":
                    dtRow["cn_level5_manager"] = manager;
                    break;
                case "經理級":
                    dtRow["cn_level6_manager"] = manager;
                    break;
                case "副理級":
                    dtRow["cn_level7_manager"] = manager;
                    break;
                case "課長級":
                    dtRow["cn_level8_manager"] = manager;
                    break;
                case "組長級":
                    dtRow["cn_level9_manager"] = manager;
                    break;
            }
            dtRow = ReturnManager(wsheet, dtRow,occu2, manager_name,org_name,super_dept2);
        }
        else
        {
            //找不到部門中的長官，有可能是跨部門，找回主要部門
            DataRow[] isResultManager2 = wsheet.Select("Occu ='" + manager + "' and isMain='1'");
            if(isResultManager2.Count()>0){
                string occu2 = isResultManager2[0]["Occu"].ToString();
                string dept_name2 = isResultManager2[0]["DeptName"].ToString();
                string super_dept2 = isResultManager2[0]["SuperDeptName"].ToString();
                string level_name = isResultManager2[0]["functionLevelName"].ToString();
                string manager_name = isResultManager2[0]["Manager"].ToString();
                manager = manager.Replace("（", "(");
                int splitIndex = manager.IndexOf('(');
                if (splitIndex > 0)
                {
                    manager = manager.Substring(0, splitIndex);
                }
                switch (level_name)
                {
                    case "董事長級":
                        dtRow["cn_level0_manager"] = manager;
                        break;
                    case "總經理級":
                        dtRow["cn_level1_manager"] = manager;
                        break;
                    case "BU副總經理":
                        dtRow["cn_level2_manager"] = manager;
                        break;
                    case "集團主管":
                        dtRow["cn_level3_manager"] = manager;
                        break;
                    case "處長/協理/副總級":
                        dtRow["cn_level4_manager"] = manager;
                        break;
                    case "資深經理級":
                        dtRow["cn_level5_manager"] = manager;
                        break;
                    case "經理級":
                        dtRow["cn_level6_manager"] = manager;
                        break;
                    case "副理級":
                        dtRow["cn_level7_manager"] = manager;
                        break;
                    case "課長級":
                        dtRow["cn_level8_manager"] = manager;
                        break;
                    case "組長級":
                        dtRow["cn_level9_manager"] = manager;
                        break;
                }
                dtRow = ReturnManager(wsheet, dtRow,occu2, manager_name,org_name,super_dept2);
            }
        }
    }
    return dtRow;
}
public DataSet RunSql(string sql)
{
    try
    {
        using (Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionStr))
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            Oracle.ManagedDataAccess.Client.OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand(sql, conn);
            Oracle.ManagedDataAccess.Client.OracleDataAdapter DataAdapter = new Oracle.ManagedDataAccess.Client.OracleDataAdapter();
            DataAdapter.SelectCommand = cmd;

            DataSet ds = new DataSet();
            DataAdapter.Fill(ds);

            return ds;
        }
    }
    catch (Exception ex)
    {
        error_log = ex.ToString();
        return null;
    }
}
public DataTable RunMSSql(string sqlcmd)
{
    try
    {
        using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionStr))
        {
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sqlcmd, conn);
            System.Data.SqlClient.SqlDataAdapter DataAdapter = new System.Data.SqlClient.SqlDataAdapter();
            DataAdapter.SelectCommand = cmd;

            DataSet ds = new DataSet();
            DataAdapter.Fill(ds);

            return ds.Tables[0];
        }
    }
    catch (Exception ex)
    {
        error_log = ex.ToString();
        return null;
    }
}

//===================================================
private Innovator inn;
private string error_log="";
private string connectionStr="";
private string action = "";
private string log = "";
public Item main(Item thisItem){
//thisItem : name='Transfer Name'
string transfer_name = "BPMToAras_Manager";
//取得設定
Item setup = GetDatabaseSetup(transfer_name);
if(setup.isError()==false){
    connectionStr = setup.getProperty("jpc_connection_string","");
    action = setup.getProperty("jpc_action","");
    if(action=="Input"){
        //輸入至Aras
        RunInputAras(setup);
    }else{

    }
}else{
    error_log = "DB Transfer Name = "+transfer_name+" is null";
}
// if(error_log!=""){
//     return inn.newError(error_log);
// }
Log(error_log);
return thisItem;