protected virtual void PMTemplateListSS_TemplateDefault_FieldVerifying(PXCache sender, PXFieldVerifyingEventArgs e)
{
PMTemplateListSS row = e.Row as PMTemplateListSS;
if (row == null)
{
return;
}
object oldValue = sender.GetValue <PMTemplateListSS.templateDefault>(row);
if (row.TemplateDefault != null && (bool)e.NewValue == true)
{
PXResultset <PMSSMapping> pmSSMappingRows = this.MappingSetup.Select();
if (pmSSMappingRows.Count() > 0)
{
PMSSMapping pmSSMappingRecord = pmSSMappingRows.RowCast <PMSSMapping>().Where(_ => !String.IsNullOrEmpty(_.NameAcu)).FirstOrDefault();
if (pmSSMappingRecord != null)
{
return;
}
}
e.NewValue = oldValue;
sender.RaiseExceptionHandling <PMTemplateListSS.templateDefault>(e.Row, oldValue, new PXSetPropertyException(SmartsheetConstants.Messages.ERROR_TEMPLATE_DEFAULT, PXErrorLevel.RowError));
}
}
protected void PMSSMapping_RowInserted(PXCache cache, PXRowInsertedEventArgs e)
{
if (e.Row == null)
{
return;
}
PMSSMapping mappingRow = (PMSSMapping)e.Row;
PMTemplateListSS pmTemplateListSSRecord = this.TemplateSetup.Current;
mappingRow.TemplateSS = pmTemplateListSSRecord.TemplateSS;
}
/// <summary>
/// Updates SS Project with new Acumatica tasks not yet synced
/// </summary>
/// <param name="smartsheetClient"></param>
/// <param name="columnMap"></param>
/// <param name="projectEntryGraph"></param>
/// <param name="sheetSelected"></param>
/// <param name="smartSheetHelperObject"></param>
public void UpdateSSProject(SmartsheetClient smartsheetClient,
Dictionary <string, long> columnMap,
ProjectEntry projectEntryGraph,
long?sheetSelected,
SmartsheetHelper smartSheetHelperObject, PXResultset <PMSSMapping> templateMappingSet)
{
//Add newly created rows to Smartsheet
List <Row> newRows = smartSheetHelperObject.InsertAcumaticaTasksInSS(projectEntryGraph, columnMap, null, false, templateMappingSet);
IList <Row> ssRows = smartsheetClient.SheetResources.RowResources.AddRows((long)sheetSelected, newRows);
int ssTaskIDPosition = 0;
if (ssRows.Count > 0 && ssRows[0].Cells != null)
{
//"TaskCD" is the linking element between the Acumatica Project's Tasks and the cell being used as the "Task" equivalent in the mapping definition
PMSSMapping mappingSS = templateMappingSet.Where(t => ((PMSSMapping)t).NameAcu.Trim().ToUpper() == SmartsheetConstants.ColumnMapping.TASKS_CD.Trim().ToUpper()).FirstOrDefault();
ssTaskIDPosition = smartSheetHelperObject.GetSSTaskPosition(ssRows[0].Cells, columnMap[mappingSS.NameSS]);
}
foreach (Row currentRow in ssRows)
{
foreach (PMTask acumaticaTask in projectEntryGraph.Tasks.Select())
{
PMTaskSSExt pmTaskSSExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(acumaticaTask);
if (pmTaskSSExtRow != null &&
pmTaskSSExtRow.UsrSmartsheetTaskID != null)
{
continue;
}
if (currentRow.Cells[ssTaskIDPosition].Value != null &&
acumaticaTask.TaskCD != null &&
string.Equals(currentRow.Cells[ssTaskIDPosition].Value.ToString().Trim(), acumaticaTask.TaskCD.Trim(), StringComparison.OrdinalIgnoreCase))
{
pmTaskSSExtRow.UsrSmartsheetTaskID = currentRow.Id;
projectEntryGraph.Tasks.Update(acumaticaTask);
break;
}
}
}
return;
}
/// <summary>
/// Behavior of data obtained from SmartSheet to insert or update in Acumatica
/// </summary>
/// <param name="row"></param>
/// <param name="columnPositionMap"></param>
/// <param name="updatedSSRow"></param>
/// <param name="pmTaskNewEntry"></param>
/// <param name="projectEntryGraph"></param>
/// <param name="durationVar"></param>
public void SettingForSheets(PMSSMapping row, Dictionary <string, int> columnPositionMap, Row updatedSSRow, PMTask pmTaskNewEntry, ProjectEntry projectEntryGraph, string durationVar)
{
if (updatedSSRow.Cells[columnPositionMap[row.NameSS]].Value != null)
{
if (updatedSSRow.Cells[columnPositionMap[row.NameSS]].Value is DateTime)
{
DateTime rowDate = (DateTime)updatedSSRow.Cells[columnPositionMap[row.NameSS]].Value;
projectEntryGraph.Tasks.Cache.SetValueExt(pmTaskNewEntry, row.NameAcu, rowDate);
}
else
{
//Duration has to be assigned before StartDate
if (row.NameAcu == SmartsheetConstants.ColumnMapping.DURATION)
{
durationVar = updatedSSRow.Cells[columnPositionMap[row.NameSS]].Value.ToString().Replace("d", "");
projectEntryGraph.Tasks.Cache.SetValueExt(pmTaskNewEntry, row.NameAcu, durationVar);
}
else
{
if (row.NameAcu == SmartsheetConstants.ColumnMapping.PCT_COMPLETE)
{
decimal percent = Convert.ToDecimal(updatedSSRow.Cells[columnPositionMap[row.NameSS]].Value.ToString().Replace("%", "")) * 100;
projectEntryGraph.Tasks.Cache.SetValueExt(pmTaskNewEntry, row.NameAcu, percent);
}
else
{
projectEntryGraph.Tasks.Cache.SetValueExt(pmTaskNewEntry, row.NameAcu, updatedSSRow.Cells[columnPositionMap[row.NameSS]].Value.ToString());
}
}
}
if (row.NameAcu == SmartsheetConstants.ColumnMapping.START_DATE)
{
if (!String.IsNullOrEmpty(durationVar))
{
int durationRow = Convert.ToInt32(durationVar);
DateTime rowDate = (DateTime)updatedSSRow.Cells[columnPositionMap[row.NameSS]].Value;
projectEntryGraph.Tasks.Cache.SetValueExt <PMTaskSSExt.duration>(pmTaskNewEntry, durationRow);
}
}
}
}
/// <summary>
/// Inserts the columns that the SmartSheet Template has in Acumatica
/// </summary>
/// <param name="mappingCache"> </param>
public void InsertTemplate(PXCache <PMSSMapping> mappingCache)
{
Dictionary <string, string> columnSheetSS = GetColumnSheet();
this.Base.Caches <PMSSMapping>().Clear();
int cont = 0;
foreach (var sheetRow in columnSheetSS)
{
if (mappingCache.GetValue <PMSSMapping.cellSSID>(sheetRow.Value.ToString()) == null)
{
PMSSMapping mappingRow = (PMSSMapping)mappingCache.Insert(new PMSSMapping
{
CellSSID = cont++,
NameSS = sheetRow.Key,
NameAcu = null,
CellFormat = sheetRow.Value
});
}
}
}
/// <summary>
/// Creates or updates the Smartsheet project with information from Acumatica.
/// New Tasks created in Smartsheet will be updated back in Acumatica
/// </summary>
/// <param name="projectEntryGraph">Project Entry graph</param>
/// <param name="pmProjectRow">PMProject record</param>
/// <param name="smartsheetClient">Smartsheet's SDK Client</param>
/// <param name="isMassProcess">Indicates if it's used in a processing page</param>
public void CreateUpdateGanttProject(ProjectEntry projectEntryGraph, PMProject pmProjectRow, SmartsheetClient smartsheetClient, bool isMassProcess = false)
{
//Primary Data View is set
projectEntryGraph.Project.Current = pmProjectRow;
PMProjectSSExt pmProjectSSExt = PXCache <PMProject> .GetExtension <PMProjectSSExt>(pmProjectRow);
PMSetup setupRecord = projectEntryGraph.Setup.Select();
PMSetupSSExt pmSetupSSExt = PXCache <PMSetup> .GetExtension <PMSetupSSExt>(setupRecord);
SmartsheetHelper smartSheetHelperObject = new SmartsheetHelper();
if (String.IsNullOrEmpty(pmProjectSSExt.UsrTemplateSS))
{
throw new PXException(SmartsheetConstants.Messages.DEFAULT_TEMPLATE);
}
string sheetName = pmProjectRow.ContractCD.Trim() + " - " + pmProjectRow.Description.Trim();
sheetName = smartSheetHelperObject.Left(sheetName, SmartsheetConstants.SSConstants.SS_PROJECT_NAME_LENGTH);
try
{
long?sheetSelected;
Dictionary <string, long> currentColumnMap = new Dictionary <string, long>();
//////////////////
//Information from Acumatica is updated in Smartsheet
//////////////////
PXResultset <PMSSMapping> templateMappingSet = PXSelect <
PMSSMapping,
Where <PMSSMapping.templateSS, Equal <Required <PMSSMapping.templateSS> > > >
.Select(projectEntryGraph, pmProjectSSExt.UsrTemplateSS);
if (pmProjectSSExt != null &&
pmProjectSSExt.UsrSmartsheetContractID != null) //Acumatica Project is already linked to SS
{
sheetSelected = pmProjectSSExt.UsrSmartsheetContractID;
Sheet ssProjectSheet = smartsheetClient.SheetResources.GetSheet((long)sheetSelected, null, null, null, null, null, null, null);
//Columns ID Mapping
currentColumnMap = new Dictionary <string, long>();
foreach (Column currentColumn in ssProjectSheet.Columns)
{
currentColumnMap.Add(currentColumn.Title, (long)currentColumn.Id);
}
smartSheetHelperObject.UpdateSSProject(smartsheetClient, currentColumnMap, projectEntryGraph, sheetSelected, smartSheetHelperObject, templateMappingSet);
}
else //Acumatica Project has not been linked to Smartsheet
{
//Sheet is created from a the template selected in the Project
Sheet sheet = new Sheet.CreateSheetFromTemplateBuilder(sheetName, Convert.ToInt64(pmProjectSSExt.UsrTemplateSS)).Build();
sheet = smartsheetClient.SheetResources.CreateSheet(sheet);
Sheet newlyCreatedSheet = smartsheetClient.SheetResources.GetSheet((long)sheet.Id, null, null, null, null, null, null, null);
currentColumnMap = new Dictionary <string, long>();
foreach (Column currentColumn in newlyCreatedSheet.Columns)
{
currentColumnMap.Add(currentColumn.Title, (long)currentColumn.Id);
}
//Acumatica Tasks are added as Smartsheet rows
List <Row> newSSRows = smartSheetHelperObject.InsertAcumaticaTasksInSS(projectEntryGraph, currentColumnMap, null, true, templateMappingSet);
IList <Row> ssRows = smartsheetClient.SheetResources.RowResources.AddRows((long)sheet.Id, newSSRows);
int ssTaskIDPosition = 0;
if (ssRows.Count > 0 && ssRows[0].Cells != null)
{
PMSSMapping mappingSS = templateMappingSet.Where(t => ((PMSSMapping)t).NameAcu.Trim().ToUpper() == SmartsheetConstants.ColumnMapping.TASKS_CD.Trim().ToUpper()).FirstOrDefault();
ssTaskIDPosition = smartSheetHelperObject.GetSSTaskPosition(ssRows[0].Cells, currentColumnMap[mappingSS.NameSS]);
}
// Add SubTasks
smartSheetHelperObject.InsertAcumaticaSubTasks(projectEntryGraph, smartsheetClient, sheet, ssRows, smartSheetHelperObject, currentColumnMap, ssTaskIDPosition, templateMappingSet);
foreach (Row currentRow in ssRows)
{
foreach (PMTask rowTask in projectEntryGraph.Tasks.Select())
{
if (currentRow.Cells[ssTaskIDPosition].Value != null &&
rowTask.TaskCD != null &&
string.Equals(currentRow.Cells[ssTaskIDPosition].Value.ToString().Trim(), rowTask.TaskCD.Trim(), StringComparison.OrdinalIgnoreCase))
{
PMTaskSSExt pmTaskSSExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(rowTask);
pmTaskSSExtRow.UsrSmartsheetTaskID = currentRow.Id;
projectEntryGraph.Tasks.Update(rowTask);
break;
}
}
}
sheetSelected = (long)sheet.Id;
PMProjectSSExt pmProjectSSExtRow = PXCache <PMProject> .GetExtension <PMProjectSSExt>(pmProjectRow);
pmProjectSSExtRow.UsrSmartsheetContractID = sheetSelected;
projectEntryGraph.Project.Update(pmProjectRow);
}
//////////////////
//Information from Smartsheet is updated in Acumatica
//////////////////
Sheet updatedSheet = smartsheetClient.SheetResources.GetSheet((long)sheetSelected, null, null, null, null, null, null, null);
int columnPosition = 0;
Dictionary <string, int> columnPositionMap = new Dictionary <string, int>();
foreach (Column currentColumn in updatedSheet.Columns)
{
columnPositionMap.Add(currentColumn.Title, columnPosition);
columnPosition += 1;
}
smartSheetHelperObject.UpdateAcumaticaTasks(projectEntryGraph, pmProjectRow, pmSetupSSExt, updatedSheet, columnPositionMap, templateMappingSet);
projectEntryGraph.Actions.PressSave();
if (isMassProcess)
{
PXProcessing.SetInfo(String.Format(SmartsheetConstants.Messages.SUCCESSFULLY_SYNCED, pmProjectRow.ContractCD));
}
}
catch (Exception e)
{
throw new PXException(e.Message);
}
}
/// <summary>
/// Creates/Updates Acumatica Tasks with the Smartsheet modifications
/// </summary>
/// <param name="projectEntryGraph"></param>
/// <param name="pmProjectRow"></param>
/// <param name="pmSetupSSExt"></param>
/// <param name="updatedSheet"></param>
/// <param name="columnPositionMap"></param>
public void UpdateAcumaticaTasks(ProjectEntry projectEntryGraph,
PMProject pmProjectRow,
PMSetupSSExt pmSetupSSExt,
Sheet updatedSheet,
Dictionary <string, int> columnPositionMap,
PXResultset <PMSSMapping> templateMappingSet)
{
bool recordedInAcumatica = false;
int primaryColumnPosition = 0;
PMTask pmTaskNewEntry = new PMTask();
foreach (Column updatedColumn in updatedSheet.Columns)
{
if (updatedColumn != null &&
updatedColumn.Primary != null &&
updatedColumn.Primary == true)
{
foreach (Row updatedSSRow in updatedSheet.Rows)
{
if (updatedSSRow != null &&
updatedSSRow.ParentId != null) //Subtasks are not synced back to Acumatica
{
continue;
}
PMTask currentTaskRow = null;
PMTaskSSExt pmTaskSSExtRow = null;
foreach (PMTask taskRow in projectEntryGraph.Tasks.Select())
{
recordedInAcumatica = false;
pmTaskSSExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(taskRow);
if (pmTaskSSExtRow != null &&
pmTaskSSExtRow.UsrSmartsheetTaskID == updatedSSRow.Id)
{
recordedInAcumatica = true;
currentTaskRow = taskRow;
break;
}
}
if (recordedInAcumatica == false) //New Row in Smartsheet not yet added to Acumatica
{
//Fields retrieved: Task, Description, Start Date, End Date, % Complete,
if (updatedSSRow.Cells[primaryColumnPosition].Value != null)
{
pmTaskNewEntry = new PMTask();
pmTaskNewEntry.ProjectID = pmProjectRow.ContractID;
pmTaskNewEntry.TaskCD = updatedSSRow.Cells[primaryColumnPosition].Value.ToString();
PMTask taskCDValidation = (PMTask)projectEntryGraph.Tasks.Select()
.Where(t => ((PMTask)t).TaskCD.Trim().ToUpper() == updatedSSRow.Cells[primaryColumnPosition].Value.ToString().Trim().ToUpper()).FirstOrDefault();
if (taskCDValidation == null)
{
projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.rateTableID>(pmTaskNewEntry, pmSetupSSExt.UsrDefaultRateTableID);
projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.status>(pmTaskNewEntry, SmartsheetConstants.SSConstants.ACTIVE);
pmTaskNewEntry.Description = SmartsheetConstants.Messages.DEFAULT_TASK_DESCRIPTION;
string durationVar = "";
foreach (PMSSMapping row in templateMappingSet)
{
if (!String.IsNullOrEmpty(row.NameAcu))
{
SettingForSheets(row, columnPositionMap, updatedSSRow, pmTaskNewEntry, projectEntryGraph, durationVar);
}
}
PMTaskSSExt pmTaskExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(pmTaskNewEntry);
pmTaskExtRow.UsrSmartsheetTaskID = updatedSSRow.Id;
//Insert() has to be invoked at the end as the order in which the values are assigned to the object depend on the iteration
pmTaskNewEntry = projectEntryGraph.Tasks.Insert(pmTaskNewEntry);
}
}
}
else //Previously existing row in SS
{
pmTaskNewEntry = new PMTask();
//Fields updated: Description, Start Date, End Date, % complete.
if (updatedSSRow.Cells[primaryColumnPosition].Value != null)
{
if (currentTaskRow != null)
{
// Find the Task to update it
PMSSMapping mappingSS = templateMappingSet.Where(t => ((PMSSMapping)t).NameAcu.Trim().ToUpper() == SmartsheetConstants.ColumnMapping.TASKS_CD.Trim().ToUpper()).FirstOrDefault();
pmTaskNewEntry = (PMTask)projectEntryGraph.Tasks.Select()
.Where(t => ((PMTask)t).TaskCD.Trim().ToUpper() == updatedSSRow.Cells[columnPositionMap[mappingSS.NameSS]].Value.ToString().Trim().ToUpper()).FirstOrDefault();
string durationVar = "";
foreach (PMSSMapping row in templateMappingSet)
{
if (!String.IsNullOrEmpty(row.NameAcu))
{
SettingForSheets(row, columnPositionMap, updatedSSRow, pmTaskNewEntry, projectEntryGraph, durationVar);
}
}
currentTaskRow = projectEntryGraph.Tasks.Update(currentTaskRow);
}
}
}
}
break;
}
else
{
primaryColumnPosition += 1;
}
}
return;
}
