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; }