Migration Utility Project online csom

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.ProjectServer.Client;
using Microsoft.SharePoint.Client;
using System.Security;
using System.Data;

namespace MigrationUtility {
 class ReadExcel {
  public static KeyValuePair < string, ProjectContext > Auth(String uname, String pwd, string siteURL) {
   ProjectContext Projcontext;


   try {
    Projcontext = new ProjectContext(siteURL);
    Web web = Projcontext.Web;
    SecureString passWord = new SecureString();
    foreach(char c in pwd.ToCharArray()) passWord.AppendChar(c);
    Projcontext.Credentials = new SharePointOnlineCredentials(uname, passWord);

    return new KeyValuePair < string, ProjectContext > ("True", Projcontext);
   } catch (Exception e) {

    return new KeyValuePair < string, ProjectContext > ("False", null);
   }
  }


  public static void ReadExcelFile() {
   string fileName = "D:\\Toll\\aruna.agarwal\\1142 - GL Gov&Def Infrastructure Upgrade Murarrie.xlsx";
   // string fileName = "D:\\Toll\\Automation Data\\ScheduleData_15Jan19.xlsx";
   var obj = Auth("anil.kumar@tollgroup.com", "", "");
   ProjectContext context = obj.Value;


   Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
   Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileName);

   #
   region Project Creation
   //Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
   //Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;

   //int rowCount = xlRange.Rows.Count;
   //int colCount = xlRange.Columns.Count;

   //for (int i = 2; i <= rowCount; i++)
   //{               
   //    string sdfNumber = xlRange.Cells[i, 1].Value2.ToString();
   //    string projectIdentifier = xlRange.Cells[i, 2].Value2.ToString();
   //    string billingCode = xlRange.Cells[i, 3].Value2.ToString();
   //    string projectName = xlRange.Cells[i, 4].Value2.ToString();
   //    string projectDescription = xlRange.Cells[i, 5].Value2.ToString();
   //    string managedBy = xlRange.Cells[i, 8].Value2.ToString();
   //    string projectManager = xlRange.Cells[i, 9].Value2.ToString();
   //    string reportable = xlRange.Cells[i, 18].Value2.ToString();
   //    double dtdate = double.Parse(xlRange.Cells[i, 39].Value2.ToString());
   //    DateTime startDate = DateTime.FromOADate(dtdate);

   //    TaskCreation objTaskCreation = new TaskCreation();
   //    PublishedProject project = objTaskCreation.CreateProject(context, sdfNumber, projectIdentifier, billingCode, projectName, projectDescription, managedBy, projectManager, reportable, startDate);




   //    //-------------------------------------------Custom fields creation------------------------------------------------

   //    //context.Load(project.IncludeCustomFields.CustomFields);
   //    //context.ExecuteQuery();

   //    context.Load(project.CustomFields, cs => cs.IncludeWithDefaultProperties(pr => pr.EntityType, pr => pr.FieldType, pr => pr.LookupTable, pr => pr.LookupTable.Name, pr => pr.LookupEntries ));
   //    context.ExecuteQuery();


   //    //foreach(var cust in project.CustomFields)
   //    //{
   //    //    Console.WriteLine(cust.Name);
   //    //}

   //    DraftProject draft = project.Draft;
   //    JobState job1 = context.WaitForQueue(draft.CheckIn(true), 50);

   //    DraftProject projCheckedOut = project.CheckOut();
   //    context.ExecuteQuery();

   //    var lookupTables = context.LookupTables;
   //    context.Load(lookupTables, l => l.IncludeWithDefaultProperties(lo => lo.Entries));
   //    context.ExecuteQuery();

   //    //set project identifier and project manager
   //    projCheckedOut.ProjectIdentifier = projectIdentifier;
   //    projCheckedOut.Owner.Email = "vishal.kumar@tollgroup.com";

   //    ////set Division
   //    //var divisionLookup = lookupTables.Where(l => l.Name == "LT_Division").FirstOrDefault();
   //    //string divisionValue = divisionLookup.Entries.Where(e => e.FullValue == "Global Express").Select(e => e.InternalName).FirstOrDefault();
   //    //projCheckedOut.SetCustomFieldValue("Custom_3b2c784c1fe7e81180d100155dbc5419", divisionValue);

   //    //////set Project Department
   //    ////var departmentLookup = lookupTables.Where(l => l.Name == "Department").FirstOrDefault();
   //    ////string departmentValue = departmentLookup.Entries.Where(e => e.FullValue == "Group IT").Select(e => e.InternalName).FirstOrDefault();
   //    ////projCheckedOut.SetCustomFieldValue("Custom_9d77d62aa92e4d40adc8446c90eb7456", departmentValue);

   //    ////set Project Sponsor
   //    //projCheckedOut.SetCustomFieldValue("Custom_0fcbcb010de7e81180d300155deca918", "Sponsor");

   //    ////set Mananged By
   //    //var managedByLookup = lookupTables.Where(l => l.Name == "LT_ManagedBy").FirstOrDefault();
   //    //string managedByValue = managedByLookup.Entries.Where(e => e.FullValue == "BP").Select(e => e.InternalName).FirstOrDefault();
   //    //projCheckedOut.SetCustomFieldValue("Custom_6df9ad7f1fe7e81180d300155de8391e", managedByValue);

   //    //////set Project Status
   //    ////var projectStatusLookup = lookupTables.Where(l => l.Name == "LT_ProjectStatus").FirstOrDefault();
   //    ////string projectStatusValue = projectStatusLookup.Entries.Where(e => e.FullValue == "Completed").Select(e => e.InternalName).FirstOrDefault();
   //    ////projCheckedOut.SetCustomFieldValue("Custom_9393a7b11fe7e81180d300155de8391e", projectStatusValue);

   //    //////set Priority(Rank)
   //    ////var priorityLookup = lookupTables.Where(l => l.Name == "LT_Priority").FirstOrDefault();
   //    ////string priorityValue = priorityLookup.Entries.Where(e => e.FullValue == "Medium").Select(e => e.InternalName).FirstOrDefault();
   //    ////projCheckedOut.SetCustomFieldValue("Custom_5cdf2d3e23e7e81180d800155deccb15", priorityValue);

   //    ////set Project Criticality
   //    //var projectCriticalityLookup = lookupTables.Where(l => l.Name == "LT_ProjectCriticality").FirstOrDefault();
   //    //string projectCriticalityValue = projectCriticalityLookup.Entries.Where(e => e.FullValue == "Client Critical").Select(e => e.InternalName).FirstOrDefault();
   //    //projCheckedOut.SetCustomFieldValue("Custom_76c6311320e7e81180ca00155dec3b0e", projectCriticalityValue);

   //    ////set Functional Area
   //    //var functionalAreaLookup = lookupTables.Where(l => l.Name == "LT_FunctionalArea").FirstOrDefault();
   //    //string functionalAreaValue = functionalAreaLookup.Entries.Where(e => e.FullValue == "Customer Relationship Management").Select(e => e.InternalName).FirstOrDefault();
   //    //projCheckedOut.SetCustomFieldValue("Custom_8f15d53720e7e81180ca00155dec3b0e", functionalAreaValue);

   //    //////set Project Stage
   //    ////var projectStageLookup = lookupTables.Where(l => l.Name == "LT_ProjectStage").FirstOrDefault();
   //    ////string projectStageValue = projectStageLookup.Entries.Where(e => e.FullValue == "Evaluate").Select(e => e.InternalName).FirstOrDefault();
   //    ////projCheckedOut.SetCustomFieldValue("Custom_c4c3f15420e7e81180d300155de8391e", projectStageValue);

   //    ////set Total Investment Cost Capex
   //    //projCheckedOut.SetCustomFieldValue("Custom_f33917fee0ebe811afaa00155deccb1a", 61000);

   //    ////set Total Investment Cost Opex
   //    //projCheckedOut.SetCustomFieldValue("Custom_185d5a18e1ebe811afa900155de8921b", 0);

   //    ////set Finance Status Date
   //    //projCheckedOut.SetCustomFieldValue("Custom_798c809725e7e81180d100155de85117", DateTime.Now);

   //    ////set Report Date
   //    //projCheckedOut.SetCustomFieldValue("Custom_f7be19f426e7e81180d400155dbc7129", DateTime.Now);

   //    //var RAGStatusLookup = lookupTables.Where(l => l.Name == "LT_RAGStatus").FirstOrDefault();
   //    //string RAGStatusValue = RAGStatusLookup.Entries.Where(e => e.FullValue == "Not Applicable").Select(e => e.InternalName).FirstOrDefault();

   //    ////set Current Project Status
   //    //projCheckedOut.SetCustomFieldValue("Custom_1d39cf6401e8e811afa600155de88b1d", RAGStatusValue);

   //    //////set Current Schedule Indicator
   //    ////projCheckedOut.SetCustomFieldValue("Custom_87b3c21402e8e81180d200155de87008", RAGStatusValue);

   //    //////set Current Financials Indicator
   //    ////projCheckedOut.SetCustomFieldValue("Custom_b293146a02e8e81180d200155de87008", RAGStatusValue);

   //    //////set Current Scope Indicator
   //    ////projCheckedOut.SetCustomFieldValue("Custom_bb6ff79f02e8e81180d300155deca918", RAGStatusValue);

   //    //////set Current Risk Indicator
   //    ////projCheckedOut.SetCustomFieldValue("Custom_2285d87d03e8e81180d000155de85417", RAGStatusValue);

   //    //////set Current Issues Indicator
   //    ////projCheckedOut.SetCustomFieldValue("Custom_43e945b603e8e81180d300155deca918", RAGStatusValue);

   //    projCheckedOut.Update();
   //    projCheckedOut.Publish(true);
   //    context.ExecuteQuery();

   //    //projCheckedOut.Update();
   //    //context.Load(projCheckedOut);
   //    //projCheckedOut.Publish(true);
   //    //context.ExecuteQuery();
   //    //context.Projects.Update();
   //    //context.ExecuteQuery();
   //    //projCheckedOut.Publish(true);
   //    JobState jobState = context.WaitForQueue(context.Projects.Update(), 10);

   //    if(jobState == JobState.Success)
   //        Console.WriteLine("Success");
   //    else
   //        Console.WriteLine("Failure");

   //    //-------------------------------------------Custom fields creation------------------------------------------------

   //    //cleanup 
   //    GC.Collect();
   //    GC.WaitForPendingFinalizers();

   //    //rule of thumb for releasing com objects: 
   //    //  never use two dots, all COM objects must be referenced and released individually 
   //    //  ex: [somthing].[something].[something] is bad 

   //    //release com objects to fully kill excel process from running in the background 
   //    Marshal.ReleaseComObject(xlRange);
   //    Marshal.ReleaseComObject(xlWorksheet);

   //    //close and release 
   //    xlWorkbook.Close();
   //    Marshal.ReleaseComObject(xlWorkbook);

   //    //quit and release 
   //    xlApp.Quit();
   //    Marshal.ReleaseComObject(xlApp);
   //    //}
   //}
   # endregion

   # region read excel sheet and filter on the basics of Project Name.
   ////Microsoft.Office.Interop.Excel._Worksheet xlWorksheetProject = xlWorkbook.Sheets[1]; //TaskData sheet
   ////Microsoft.Office.Interop.Excel.Range xlRangeTask = xlWorksheetProject.UsedRange;

   ////int ijk = xlRangeTask.Areas.Count;
   ////xlWorksheetProject.EnableAutoFilter = true;

   ////System.Data.DataTable dtProjectNames = new System.Data.DataTable();

   //////for(int s=1;s<=xlRangeTask.Rows.Count;s++)
   //////{
   //////    DataColumn dtColumn = new DataColumn()
   //////    DataRow dtRow = new DataRow();


   //////}

   ////String strProjectName = "R3820c - GFT Architecture";

   ////xlRangeTask.AutoFilter(1, strProjectName, Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlAnd,Type.Missing, true);

   //////get the filtered range to have correct data.
   ////Microsoft.Office.Interop.Excel.XlSpecialCellsValue XlSpecialCellsValueTask = new Microsoft.Office.Interop.Excel.XlSpecialCellsValue();

   ////Microsoft.Office.Interop.Excel.Range filterRange = xlRangeTask.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeVisible);

   ////int intareacount = filterRange.Areas.Count;


   ////for (int areaId = 1; areaId <= filterRange.Areas.Count; areaId++)
   ////{
   ////   Microsoft.Office.Interop.Excel.Range areaRange = filterRange.Areas.get_Item(areaId);
   ////    object[,] areaValues = areaRange.Value2;
   ////    // Do something with the values here...
   ////}


   ////    // Microsoft.Office.Interop.Excel.Range filterRange = xlRangeTask.SpecialCells(xlsp)

   //////    string strFilterProjectName = filterRange.Cells[2, 1].Value2.ToString();
   //////string strFilterTaskName = filterRange.Cells[2, 3].Value2.ToString();
   //////string strFilterProjectName = xlRangeTask.Cells[1, 2].Value2.ToString();
   //////string strFilterProjectName = xlRangeTask.Cells[1, 2].Value2.ToString();
   //////string strFilterProjectName = xlRangeTask.Cells[1, 2].Value2.ToString();
   //////string strFilterProjectName = xlRangeTask.Cells[1, 2].Value2.ToString();


   //////xlRangeTask.AutoFilter()





   # endregion



   # region Task creation
   Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[2]; //TaskData sheet
   Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;

   int rowCount = xlRange.Rows.Count;
   int colCount = xlRange.Columns.Count;

   PublishedProject publishedProject = context.Projects.GetByGuid(new Guid("56fd1aa2-5c19-e911-afaa-00155dec2d02"));
   DraftProject draftProject = publishedProject.Draft;
   JobState jobState1 = context.WaitForQueue(draftProject.CheckIn(true), 200);

   DraftProject checkedOutProject = publishedProject.CheckOut();

   TaskCreationInformation newTask;
   for (int i = 2; i <= rowCount; i++) {
    newTask = new TaskCreationInformation();
    newTask.Id = Guid.NewGuid();
    newTask.Name = xlRange.Cells[i, 2].Value2.ToString();
    newTask.IsManual = true;
    double startdate = double.Parse(xlRange.Cells[i, 3].Value2.ToString());
    DateTime start = DateTime.FromOADate(startdate);
    start = start.AddHours(8);
    newTask.Start = start;
    double finishdate = double.Parse(xlRange.Cells[i, 8].Value2.ToString());
    DateTime finish = DateTime.FromOADate(finishdate);
    //finish = finish.AddHours(17);
    if (xlRange.Cells[i, 7].Value2.ToString() == "Phase" || xlRange.Cells[i, 7].Value2.ToString() == "Milestone")
     finish = finish.AddHours(17);
    else
     finish = finish.AddHours(17).AddDays(1);
    newTask.Finish = finish;

    checkedOutProject.Tasks.Add(newTask);

   }

   QueueJob queueJob1 = checkedOutProject.Update();
   jobState1 = context.WaitForQueue(queueJob1, 200);
   QueueJob queueJob2 = checkedOutProject.Publish(false);
   jobState1 = context.WaitForQueue(queueJob2, 200);
   //checkedOutProject.CheckIn(false);
   //QueueJob queueJob3 = context.Projects.Update();
   //jobState1 = context.WaitForQueue(queueJob3, 200);
   #
   endregion

   # region Build team
   for project

   var resources = context.LoadQuery(context.EnterpriseResources.IncludeWithDefaultProperties(r => r.Id, r => r.Name));
   context.ExecuteQuery();

   xlWorksheet = xlWorkbook.Sheets[3]; //ResourceData sheet
   xlRange = xlWorksheet.UsedRange;
   rowCount = xlRange.Rows.Count;
   colCount = xlRange.Columns.Count;

   publishedProject = context.Projects.GetByGuid(new Guid("56fd1aa2-5c19-e911-afaa-00155dec2d02"));
   draftProject = publishedProject.Draft;
   jobState1 = context.WaitForQueue(draftProject.CheckIn(true), 200);

   checkedOutProject = publishedProject.CheckOut();

   context.Load(checkedOutProject.ProjectResources);
   context.ExecuteQuery();

   for (int i = 2; i <= rowCount; i++) {
    string resourceName = xlRange.Cells[i, 1].Value2.ToString();
    var resource = resources.Where(r => r.Name == resourceName);
    if (resource == null || resource.Count() == 0) {
     Console.WriteLine(resourceName + " not found in PWA");
     continue;
    }

    ProjectResourceCreationInformation res = new ProjectResourceCreationInformation();
    res.Id = resource.First().Id;
    res.Name = resource.First().Name;

    checkedOutProject.ProjectResources.AddEnterpriseResource(resource.First());
   }
   queueJob1 = checkedOutProject.Update();
   jobState1 = context.WaitForQueue(queueJob1, 200);
   queueJob2 = checkedOutProject.Publish(false);
   jobState1 = context.WaitForQueue(queueJob2, 200);
   //checkedOutProject.CheckIn(false);
   //queueJob3 = context.Projects.Update();
   //jobState1 = context.WaitForQueue(queueJob3, 200);
   #
   endregion

   # region Set task outline level

   xlWorksheet = xlWorkbook.Sheets[2]; //TaskData sheet
   xlRange = xlWorksheet.UsedRange;
   rowCount = xlRange.Rows.Count;
   colCount = xlRange.Columns.Count;


   publishedProject = context.Projects.GetByGuid(new Guid("56fd1aa2-5c19-e911-afaa-00155dec2d02"));
   draftProject = publishedProject.Draft;
   jobState1 = context.WaitForQueue(draftProject.CheckIn(true), 200);

   checkedOutProject = publishedProject.CheckOut();

   context.Load(checkedOutProject.Tasks, t => t.IncludeWithDefaultProperties(ta => ta.ActualWork, ta => ta.Assignments, ta => ta.IsMilestone, ta => ta.Name, ta => ta.Id, ta => ta.OutlineLevel, ta => ta.OutlinePosition, ta => ta.Work, ta => ta.StatusManager, ta => ta.CustomFields, ta => ta.CustomFields.IncludeWithDefaultProperties(cf => cf.LookupTable, cf => cf.LookupEntries)));
   context.ExecuteQuery();

   //var TaskCustomFields = context.CustomFields;
   //context.Load(TaskCustomFields, t => t.IncludeWithDefaultProperties(to => to.LookupEntries));

   DraftTaskCollection taskCol = checkedOutProject.Tasks;
   //DraftTask task1 = taskCol[3];
   //CustomFieldCollection cfcTask = task1.CustomFields;
   //Dictionary<string, object> customFields = task1.FieldValues;


   var lookupTables = context.LookupTables;
   context.Load(lookupTables, l => l.IncludeWithDefaultProperties(lo => lo.Entries));
   context.ExecuteQuery();

   //set Oracle Task Number.
   var OracleTaskNumberlookup = lookupTables.Where(l => l.Name == "LT_OracleTaskNumber").FirstOrDefault();

   //projCheckedOut.SetCustomFieldValue("Custom_3b2c784c1fe7e81180d100155dbc5419", OracleTaskNumberValue);

   //PublishedTaskCollection taskCol1 = checkedOutProject.Tasks;
   //PublishedTask task2 = taskCol.First();
   //CustomFieldCollection cfcTask = task2.CustomFields;

   for (int i = 2; i <= rowCount; i++) {
    string taskName = xlRange.Cells[i, 2].Value2.ToString();
    double startdate = double.Parse(xlRange.Cells[i, 3].Value2.ToString());
    DateTime start = DateTime.FromOADate(startdate);

    var task = taskCol.Where(t => t.Name == taskName && t.Start.Day == start.Day && t.Start.Month == start.Month && t.Start.Year == start.Year);
    //DraftTask taskOracle = taskCol.Where(t => t.Name == taskName && t.Start.Day == start.Day && t.Start.Month == start.Month && t.Start.Year == start.Year);
    //CustomField OracleTaskNumber = new CustomField();


    if (task == null || task.Count() == 0) {
     Console.WriteLine("Task not found in Project tasks");
     continue;
    }

    string outlineLevel = xlRange.Cells[i, 5].Value2.ToString();
    task.First().OutlineLevel = Convert.ToInt32(outlineLevel);
    if (!(task.First().IsSummary)) {
     task.First().StatusManager = checkedOutProject.Owner;
    }
    //CustomFieldCollection cfcTask = task.First().CustomFields;

    //Read Oracle Task Number from Excel Sheet.
    string strOracleTaskNumber = xlRange.Cells[i, 6].Value2.ToString();
    string OracleTaskNumberValue = OracleTaskNumberlookup.Entries.Where(e => e.FullValue == strOracleTaskNumber).Select(e => e.InternalName).FirstOrDefault();
    task.First()["Custom_a2829a1524e7e81180d800155deccb15"] = new String[] {
     OracleTaskNumberValue
    };
    //task.First().FieldValues["Custom_a2829a15-24e7-e811-80d8-00155deccb15"] = OracleTaskNumberValue.ToString();


    //Read Task Type Values
    string strTaskType = xlRange.Cells[i, 7].Value2.ToString();
    if (strTaskType == "Milestone") {
     task.First().IsMilestone = true;
    }

   }
   queueJob1 = checkedOutProject.Update();
   jobState1 = context.WaitForQueue(queueJob1, 200);
   queueJob2 = checkedOutProject.Publish(false);
   jobState1 = context.WaitForQueue(queueJob2, 200);
   //checkedOutProject.CheckIn(false);
   QueueJob queueJob3 = context.Projects.Update();
   jobState1 = context.WaitForQueue(queueJob3, 200);
   context.ExecuteQuery();

   #
   endregion


   # region Update assignments

   xlWorksheet = xlWorkbook.Sheets[4]; //Assignments sheet
   xlRange = xlWorksheet.UsedRange;

   rowCount = xlRange.Rows.Count;
   colCount = xlRange.Columns.Count;

   publishedProject = context.Projects.GetByGuid(new Guid("56fd1aa2-5c19-e911-afaa-00155dec2d02"));
   draftProject = publishedProject.Draft;
   jobState1 = context.WaitForQueue(draftProject.CheckIn(true), 200);

   checkedOutProject = publishedProject.CheckOut();

   context.Load(checkedOutProject.Tasks, t => t.IncludeWithDefaultProperties(ta => ta.ActualWork, ta => ta.Assignments, ta => ta.IsMilestone, ta => ta.Name, ta => ta.Id, ta => ta.OutlineLevel, ta => ta.OutlinePosition, ta => ta.Work, ta => ta.StatusManager));
   context.ExecuteQuery();

   context.Load(checkedOutProject.ProjectResources, r => r.IncludeWithDefaultProperties(re => re.Id, re => re.Name));
   context.ExecuteQuery();


   DraftTaskCollection taskCollection = checkedOutProject.Tasks;

   AssignmentCreationInformation newAssignment;
   for (int i = 2; i <= rowCount; i++) {
    string resourceName = xlRange.Cells[i, 2].Value2.ToString();
    string taskName = xlRange.Cells[i, 3].Value2.ToString();

    double startdate = double.Parse(xlRange.Cells[i, 4].Value2.ToString());
    DateTime start = DateTime.FromOADate(startdate);

    var task = taskCollection.Where(t => t.Name == taskName && t.Start.Day == start.Day && t.Start.Month == start.Month && t.Start.Year == start.Year);
    if (task == null || task.Count() == 0) {
     Console.WriteLine("Task not found in Project tasks");
     continue;
    }
    string actualWork = xlRange.Cells[i, 6].Value2.ToString().Split()[0];
    string work = xlRange.Cells[i, 9].Value2.ToString().Split()[0];

    task.First().ActualWork = (Convert.ToDecimal(task.First().ActualWork.Replace("h", "")) + Convert.ToDecimal(actualWork)).ToString() + "h";
    task.First().Work = (Convert.ToDecimal(task.First().Work.Replace("h", "")) + Convert.ToDecimal(work)).ToString() + "h";

    //if (task.First().OutlineLevel != 1)
    //{
    //    if (task.First().Duration == "1d")
    //        task.First().Duration = (Convert.ToDecimal(task.First().Duration.Replace("d", "")) * Convert.ToDecimal(work) / 8) + "d";
    //    else
    //        task.First().Duration = (Convert.ToDecimal(task.First().Duration.Replace("d", "")) + Convert.ToDecimal(work) / 8) + "d";
    //}


    var res = checkedOutProject.ProjectResources.Where(r => r.Name == resourceName);
    if (res == null || res.Count() == 0) {
     Console.WriteLine("Task - " + taskName + " can not be assigned as " + resourceName + " does not exist in PWA");
     continue;
    }

    newAssignment = new AssignmentCreationInformation();
    newAssignment.Id = Guid.NewGuid();
    newAssignment.TaskId = task.First().Id;
    newAssignment.ResourceId = res.First().Id;
    newAssignment.Start = start;

    task.First().Assignments.Add(newAssignment);
    checkedOutProject.Assignments.Add(newAssignment);
   }
   queueJob1 = checkedOutProject.Update();
   jobState1 = context.WaitForQueue(queueJob1, 200);
   queueJob2 = checkedOutProject.Publish(false);
   jobState1 = context.WaitForQueue(queueJob2, 200);
   checkedOutProject.CheckIn(false);
   queueJob3 = context.Projects.Update();
   jobState1 = context.WaitForQueue(queueJob3, 200);
   context.ExecuteQuery();#
   endregion

   xlWorkbook.Close();

  }

 }
}