从MSFT Project中同步数据到PSA

最近在做dynamics 365 PSA 模块的开发. 

其中的module功能的确是非常好用.  微软已经在project中有plugin可以直接使用. 这个plugin的好处是可以无缝和PSA关联,并且数据都可以导入进去.

缺点也非常明显, 就是现在只支持11个字段(WBS, category 和 description 在途中没有显示出来)

那如果project中有自定义的字段,则我们需要额外想办法导入. 

有两种方法:

1. 使用代码导入

2. 使用flow + project online

今天我们讲解用C#代码导入

因为微软没有提供官方的SDK, 我们只能用三方的SDK.

我们首先进nuget package中下载 net.sf.mpxj.

这是三方的插件, 通过Java实现的.

首先,我们在dynamics 中创建一个open web resource的 js绑定在ribbon button中, 当我们点upload按钮之后, 会激活下面的C# 代码.

这里只判断了三层关系. 超过三层关系则不做更多的判断.

        [HttpPost]
        [AllowAnonymous]
        [Route("api/project/uploadproject")]
        public JObject UploadProjects()
        {
            var back = new ProjectBack();
            try
            {
                LogHelper.WriteLog("Entered UploadProjects");
                HttpFileCollection filelist = HttpContext.Current.Request.Files;
                NameValueCollection form = HttpContext.Current.Request.Form;
                string rootFolder = form.Get("rootFolder");
                ProjectId = form.Get("projectId");
                LogHelper.WriteLog("Upload MPP" + rootFolder);
                if (filelist.Count < 1)
                {
                    back.status = "error";
                    back.errors.Add("cannot find file");
                    return JObject.Parse(JsonConvert.SerializeObject(back));
                }

                var file = filelist[0];
                byte[] fileData = null;
                using (var binaryReader = new BinaryReader(file.InputStream))
                {
                    fileData = binaryReader.ReadBytes(file.ContentLength);
                }

                // TODO: 
                // 删除当前project的所有projecttasks
                // 导入数据到project中 
                var result = DeleteRecord(ProjectId);
                if (result == false)
                {
                    var retryDeleteResult = false;
                    for (int i = 0; i < 5; i++)
                    {
                        retryDeleteResult = DeleteRecord(ProjectId);
                        if (retryDeleteResult)
                        {
                            break;
                        }
                    }
                    if (!retryDeleteResult)
                    {
                        back.status = "error";
                        back.errors.Add("delete error");
                        return JObject.Parse(JsonConvert.SerializeObject(back));
                    }

                }
                var reader = new net.sf.mpxj.reader.UniversalProjectReader();
                var project = reader.Read(new java.io.ByteArrayInputStream(fileData));
                var tasks = project.Tasks;
                var isRootItem = true;
                var previousOutlineLevel = 1;
                // initinal WbsLevel
                WbsLevel = new WbsLevel();
                foreach (net.sf.mpxj.Task task in tasks)
                {
                    // 判断不要第0行数据
                    //if (isRootItem)
                    //{
                    //    isRootItem = false;
                    //    continue;
                    //}

                    // 赋值guid
                    CurrentItemGuid = Guid.NewGuid();
                    // 判断父子层级关系
                    var wbs = task.WBS;
                    var wbsSplit = wbs.Split('.');
                    switch (wbsSplit.Count())
                    {
                        case 1:
                            WbsLevel.FirstLevelGuid = CurrentItemGuid;
                            HasParentLvl = false;
                            break;
                        case 2:
                            WbsLevel.SecondLevelGuid = CurrentItemGuid;
                            HasParentLvl = true;
                            ParentLvlGuid = WbsLevel.FirstLevelGuid;
                            break;
                        case 3:
                            WbsLevel.ThirdLevelGuid = CurrentItemGuid;
                            HasParentLvl = true;
                            ParentLvlGuid = WbsLevel.SecondLevelGuid;
                            break;
                        case 4:
                            HasParentLvl = true;
                            ParentLvlGuid = WbsLevel.ThirdLevelGuid;
                            break;
                    }


                    //int.TryParse(task.OutlineLevel.ToString(), out int currentOutlineLevel);
                    //if (currentOutlineLevel >= previousOutlineLevel)
                    //{
                    //    HasParentLvl = true;
                    //}
                    //else
                    //{
                    //    HasParentLvl = false;
                    //    // TODO: 不应该是上个 而是上级
                    //    ParentLvlGuid = CurrentItemGuid;
                    //}


                    var dependency = task.Predecessors.ToString();
                    var splitResult = dependency.Split('>');

                    if (splitResult.Count() >= 2)
                    {
                        var splitValue = splitResult[1];
                        dependency = splitValue.Substring(10, 2);
                    }
                    else
                    {
                        dependency = "";
                    }


                    var calStart = java.util.Calendar.getInstance();
                    calStart.setTime(task.Start);
                    // note that the Month component of java.util.Date  
                    // from 0-11 (i.e. Jan == 0)
                    var plannedStart = new DateTime(calStart.get(java.util.Calendar.YEAR),
                              calStart.get(java.util.Calendar.MONTH) + 1,
                              calStart.get(java.util.Calendar.DAY_OF_MONTH),
                              calStart.get(java.util.Calendar.HOUR_OF_DAY),
                              calStart.get(java.util.Calendar.MINUTE),
                              calStart.get(java.util.Calendar.SECOND));
                    var calEnd = java.util.Calendar.getInstance();
                    calEnd.setTime(task.Start);
                    // note that the Month component of java.util.Date  
                    // from 0-11 (i.e. Jan == 0)
                    var plannedEnd = new DateTime(calEnd.get(java.util.Calendar.YEAR),
                              calEnd.get(java.util.Calendar.MONTH) + 1,
                              calEnd.get(java.util.Calendar.DAY_OF_MONTH),
                              calEnd.get(java.util.Calendar.HOUR_OF_DAY),
                              calEnd.get(java.util.Calendar.MINUTE),
                              calEnd.get(java.util.Calendar.SECOND));

                    double.TryParse(task.PercentageComplete.toString(), out double complete);

                    MsProject = new MsProjectValues
                    {
                        task = task.Name,
                        responsible = task.ResourceNames,
                        totaldays = task.Duration.Duration,
                        plannedstart = plannedStart,
                        plannedend = plannedEnd,
                        complete = complete,
                        dependency = dependency,
                        wbsid = task.WBS
                    };
                    CreateProjectRecord(MsProject, ProjectId);
                    int.TryParse(task.OutlineLevel.toString(), out previousOutlineLevel);
                }

                back.status = "success";
                back.errors.Add("delete error");
                return JObject.Parse(JsonConvert.SerializeObject(back));
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("error=upload=====>" + ex.Message);
                back.status = "error";
                back.errors.Add("delete error");
                return JObject.Parse(JsonConvert.SerializeObject(back));
            }
        }