public virtual void TestAddRows()
{
server.setResponseBody("../../../TestSDK/resources/addRows.json");
// Create a set of cells
IList <Cell> cells = new List <Cell>();
Cell cell = new Cell.AddCellBuilder(123, "lala").SetStrict(true).Build();
cells.Add(cell);
// Create a row and add the cells to it.
IList <Row> rows = new List <Row>
{
new Row.AddRowBuilder(true, null, null, null, null).SetCells(cells).Build(),
new Row.AddRowBuilder(null, null, 123, null, null).SetFormat("A").Build()
};
IList <Row> newRows = sheetRowResource.AddRows(2331373580117892L, rows);
Assert.NotNull(newRows);
Assert.AreEqual(2, newRows.Count, "The number of rows created & inserted is not correct.");
Assert.AreEqual(2331373580117892, newRows[1].SheetId);
Column col = new Column();
col.Id = 8764071660021636L;
Assert.Null(rows[0].GetColumnByIndex(0));
Assert.Null(rows[0].GetColumnById(8764071660021636L));
}
private static void AddValuesToSheet(SmartsheetClient smartsheet, long sheetId, string query)
{
Sheet sheet = smartsheet.SheetResources.GetSheet(sheetId, null, null, null, null, null, null, null);
long columnId = sheet.Columns[0].Id.Value;
Cell cell = new Cell.AddCellBuilder(columnId, query).SetStrict(false).Build();
Row[] rows = new Row[] { new Row.AddRowBuilder(true, null, null, null, false).SetCells(new Cell[] { cell }).Build() };
smartsheet.SheetResources.RowResources.AddRows(sheetId, rows);
}
public Sheet CreateSheet()
{
Sheet sheet = smartsheet.SheetResources.CreateSheet(CreateSheetObject());
Cell cellA = new Cell.AddCellBuilder(sheet.Columns[1].Id.Value, null).SetValue("A").SetStrict(false).Build();
Cell cellB = new Cell.AddCellBuilder(sheet.Columns[1].Id.Value, null).SetValue("B").SetStrict(false).Build();
Cell cellC = new Cell.AddCellBuilder(sheet.Columns[1].Id.Value, null).SetValue("C").SetStrict(false).Build();
Row rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellA }).Build();
Row rowB = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellB }).Build();
Row rowC = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellC }).Build();
sheet.Rows = smartsheet.SheetResources.RowResources.AddRows(sheet.Id.Value, new Row[] { rowA, rowB, rowC });
return(sheet);
}
/// <summary>
/// Adds subtasks to the Smartsheet project
/// </summary>
/// <param name="smartsheetClient"></param>
/// <param name="columnMap"></param>
/// <param name="sheet"></param>
/// <param name="taskRow"></param>
/// <param name="pmTemplateTaskSSExtRow"></param>
/// <param name="subTaskRow"></param>
/// <param name="columnID"></param>
/// <param name="dependencyStartDateOffset"></param>
/// <param name="dependencySibling"></param>
/// <returns></returns>
public long AddSubTasks(SmartsheetClient smartsheetClient,
Dictionary <string, long> columnMap,
Sheet sheet, PMTask taskRow,
PMTaskSSExt pmTemplateTaskSSExtRow,
PMSubTask subTaskRow,
long?columnID,
int dependencyStartDateOffset,
long dependencySibling)
{
List <Cell> newCells = new List <Cell>();
Cell currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.TASK_NAME], subTaskRow.SubTaskCD).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
newCells.Add(currentCell);
if (pmTemplateTaskSSExtRow.UsrEnableSubtaskDependency == true)
{
DateTime adjustedStartDate = taskRow.StartDate.Value.AddDays((double)dependencyStartDateOffset);
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.START], adjustedStartDate).Build();
newCells.Add(currentCell);
}
else
{
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.START], taskRow.StartDate).Build();
newCells.Add(currentCell);
}
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.DURATION], subTaskRow.Duration.ToString()).Build();
newCells.Add(currentCell);
currentCell = new Cell.AddCellBuilder(columnMap[SmartsheetConstants.GanttTemplateMapping.COMMENTS], subTaskRow.Description).Build();
newCells.Add(currentCell);
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.ParentId = (long)columnID;
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
List <Row> newSSRows = new List <Row>();
newSSRows.Add(currentRow);
IList <Row> ssRows = smartsheetClient.SheetResources.RowResources.AddRows((long)sheet.Id, newSSRows);
return((long)ssRows[0].Id);
}
private static long CreateSheet(SmartsheetClient smartsheet)
{
Column[] columnsToCreate = new Column[] {
new Column.CreateSheetColumnBuilder("col 1", true, ColumnType.TEXT_NUMBER).Build(),
new Column.CreateSheetColumnBuilder("col 2", false, ColumnType.DATE).Build(),
new Column.CreateSheetColumnBuilder("col 3", false, ColumnType.TEXT_NUMBER).Build(),
};
Sheet createdSheet = smartsheet.SheetResources.CreateSheet(new Sheet.CreateSheetBuilder("new sheet", columnsToCreate).Build());
Assert.IsTrue(createdSheet.Columns.Count == 3);
Assert.IsTrue(createdSheet.Columns[1].Title == "col 2");
Cell cellA = new Cell.AddCellBuilder((long)createdSheet.Columns[0].Id, true).SetValue("A").SetStrict(false).Build();
Cell cellB = new Cell.AddCellBuilder((long)createdSheet.Columns[0].Id, true).SetValue("B").SetStrict(false).Build();
Cell cellC = new Cell.AddCellBuilder((long)createdSheet.Columns[0].Id, true).SetValue("C").SetStrict(false).Build();
Row rowA = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellA }).Build();
Row rowB = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellB }).Build();
Row rowC = new Row.AddRowBuilder(true, null, null, null, null).SetCells(new Cell[] { cellC }).Build();
smartsheet.SheetResources.RowResources.AddRows((long)createdSheet.Id, new Row[] { rowA, rowB, rowC });
return(createdSheet.Id.Value);
}
/// <summary>
/// Adds subtasks to the Smartsheet project
/// </summary>
/// <param name="smartsheetClient"></param>
/// <param name="columnMap"></param>
/// <param name="sheet"></param>
/// <param name="taskRow"></param>
/// <param name="pmTemplateTaskSSExtRow"></param>
/// <param name="subTaskRow"></param>
/// <param name="columnID"></param>
/// <param name="dependencyStartDateOffset"></param>
/// <param name="dependencySibling"></param>
/// <returns></returns>
public long AddSubTasks(SmartsheetClient smartsheetClient,
ProjectEntry projectEntryGraph,
Dictionary <string, long> columnMap,
Sheet sheet, PMTask taskRow,
PMTaskSSExt pmTemplateTaskSSExtRow,
PMSubTask subTaskRow,
long?columnID,
int dependencyStartDateOffset,
long dependencySibling,
PXResultset <PMSSMapping> templateMappingSSRow)
{
List <Cell> newCells = new List <Cell>();
Cell currentCell;
ProjectEntry copyProjectEntryGraph = projectEntryGraph;
ProjectEntryExt graphExtended = copyProjectEntryGraph.GetExtension <ProjectEntryExt>();
if (taskRow != null)
{
taskRow.TaskCD = subTaskRow.SubTaskCD;
taskRow.Description = subTaskRow.Description;
if (pmTemplateTaskSSExtRow != null)
{
if (pmTemplateTaskSSExtRow.UsrEnableSubtaskDependency == true)
{
taskRow.StartDate = taskRow.EndDate;
}
}
foreach (PMSSMapping row in templateMappingSSRow)
{
if (!String.IsNullOrEmpty(row.NameAcu))
{
if (copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu) is DateTime)
{
currentCell = new Cell.AddCellBuilder(columnMap[row.NameSS], (DateTime)copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
else
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.PCT_COMPLETE)
{
decimal completePercent = Convert.ToDecimal(copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)) / 100;
currentCell = new Cell.AddCellBuilder(columnMap[row.NameSS], completePercent).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND_PERCENTAGE;
}
else
{
currentCell = new Cell.AddCellBuilder(columnMap[row.NameSS], copyProjectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu).ToString()).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
}
newCells.Add(currentCell);
}
}
}
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.ParentId = (long)columnID;
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
List <Row> newSSRows = new List <Row>();
newSSRows.Add(currentRow);
IList <Row> ssRows = smartsheetClient.SheetResources.RowResources.AddRows((long)sheet.Id, newSSRows);
return((long)ssRows[0].Id);
}
/// <summary>
/// Inserts Acumatica Tasks in Smartsheet
/// </summary>
/// <param name="projectEntryGraph"></param>
/// <param name="originalColumnMap"></param>
/// <param name="modifiedColumnMap"></param>
/// <param name="firstSync"></param>
/// <returns></returns>
public List <Row> InsertAcumaticaTasksInSS(ProjectEntry projectEntryGraph,
Dictionary <string, long> originalColumnMap,
Dictionary <string, long> modifiedColumnMap,
bool firstSync, PXResultset <PMSSMapping> templateMappingSet)
{
List <Row> newSSRows = new List <Row>();
Row blankRow = new Row();
if (firstSync)
{
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
foreach (PMTask taskRow in projectEntryGraph.Tasks.Select())
{
PMTaskSSExt pmTaskSSExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(taskRow);
if (pmTaskSSExtRow != null &&
pmTaskSSExtRow.UsrSmartsheetTaskID != null)
{
continue;
}
List <Cell> newCells = new List <Cell>();
Cell currentCell = new Cell();
foreach (PMSSMapping row in templateMappingSet)
{
if (!String.IsNullOrEmpty(row.NameAcu))
{
if (!String.IsNullOrEmpty(row.NameSS))
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.DURATION)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[row.NameSS], projectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu).ToString()).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
else
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.PCT_COMPLETE)
{
decimal completePercent = Convert.ToDecimal(projectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)) / 100;
currentCell = new Cell.AddCellBuilder(originalColumnMap[row.NameSS], completePercent).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND_PERCENTAGE;
}
else
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.TASKS_CD)
{
taskRow.TaskCD = taskRow.TaskCD.Trim();
}
currentCell = new Cell.AddCellBuilder(originalColumnMap[row.NameSS], projectEntryGraph.GetValue(SmartsheetConstants.ViewName.TASK, taskRow, row.NameAcu)).Build();
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
}
}
newCells.Add(currentCell);
}
}
}
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
newSSRows.Add(currentRow);
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
return(newSSRows);
}
/// <summary>
/// Inserts Acumatica Tasks in Smartsheet
/// </summary>
/// <param name="projectEntryGraph"></param>
/// <param name="originalColumnMap"></param>
/// <param name="modifiedColumnMap"></param>
/// <param name="firstSync"></param>
/// <returns></returns>
public List <Row> InsertAcumaticaTasksInSS(ProjectEntry projectEntryGraph,
Dictionary <string, long> originalColumnMap,
Dictionary <string, long> modifiedColumnMap,
bool firstSync)
{
List <Row> newSSRows = new List <Row>();
Row blankRow = new Row();
if (firstSync)
{
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
foreach (PMTask taskRow in projectEntryGraph.Tasks.Select())
{
PMTaskSSExt pmTaskSSExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(taskRow);
if (pmTaskSSExtRow != null &&
pmTaskSSExtRow.UsrSmartsheetTaskID != null)
{
continue;
}
List <Cell> newCells = new List <Cell>();
Cell currentCell = new Cell();
//Task
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.TASK_NAME], taskRow.TaskCD).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.TASK_ID], taskRow.TaskCD).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_BOLD_GRAY_BACKGROUND;
newCells.Add(currentCell);
//Dates
if (taskRow.StartDate != null && taskRow.EndDate != null)
{
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.START], taskRow.StartDate).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.START_DATE], taskRow.StartDate).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND;
newCells.Add(currentCell);
TimeSpan dateDifference = (TimeSpan)(taskRow.EndDate - taskRow.StartDate);
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.DURATION], (dateDifference.Days + 1).ToString()).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.DURATION], (dateDifference.Days + 1).ToString()).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND;
newCells.Add(currentCell);
}
//Completed %
if (taskRow.CompletedPercent != null)
{
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.PCT_COMPLETE], (double)taskRow.CompletedPercent / 100).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE], (double)taskRow.CompletedPercent / 100).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGER_GRAY_BACKGROUND_PERCENTAGE;
newCells.Add(currentCell);
}
if (taskRow.Description != null)
{
if (firstSync)
{
currentCell = new Cell.AddCellBuilder(originalColumnMap[SmartsheetConstants.GanttTemplateMapping.COMMENTS], taskRow.Description).Build();
}
else
{
currentCell = new Cell.AddCellBuilder(modifiedColumnMap[SmartsheetConstants.ColumnMapping.DESCRIPTION], taskRow.Description).Build();
}
currentCell.Format = SmartsheetConstants.CellFormat.LARGE_GRAY_BACKGROUND;
newCells.Add(currentCell);
}
Row currentRow = new Row.AddRowBuilder(null, true, null, null, null).SetCells(newCells).Build();
currentRow.Format = SmartsheetConstants.CellFormat.GRAY_BACKGROUND;
newSSRows.Add(currentRow);
blankRow = new Row.AddRowBuilder(null, true, null, null, null).Build();
newSSRows.Add(blankRow);
}
return(newSSRows);
}
