protected virtual void RequestSSToken() { PMSetup pmSetupRow = PXSelect <PMSetup> .Select(Base); PMSetupSSExt pmSetupSSExtRow = PXCache <PMSetup> .GetExtension <PMSetupSSExt>(pmSetupRow); string loginScopeCompany = PXDatabase.Companies.Length > 0 ? PXAccess.GetCompanyName() : String.Empty; string currentScope = String.Format(",{0},{1}", Base.Accessinfo.UserName, loginScopeCompany); if (pmSetupSSExtRow != null && pmSetupSSExtRow.UsrSmartsheetClientID != null) { string smartsheetRedirect = SmartsheetConstants.SSCodeRequest.ENDPOINT; smartsheetRedirect += SmartsheetConstants.SSCodeRequest.RESPONSE_TYPE; smartsheetRedirect += SmartsheetConstants.SSCodeRequest.CLIENT_ID + pmSetupSSExtRow.UsrSmartsheetClientID; smartsheetRedirect += SmartsheetConstants.SSCodeRequest.SCOPE; smartsheetRedirect += SmartsheetConstants.SSCodeRequest.STATE + currentScope; throw new PXRedirectToUrlException(smartsheetRedirect, PXBaseRedirectException.WindowMode.InlineWindow, string.Empty, false); } else { throw new PXException(SmartsheetConstants.Messages.SMARTSHEET_ID_MISSING); } }
public string RefreshSmartsheetToken() { Guid loggedUser = PXAccess.GetUserID(); Users usersRow = PXSelect <Users, Where <Users.pKID, Equal <Required <Users.pKID> > > > .Select(this.Base, loggedUser); UsersSSExt usersSSExtRow = PXCache <Users> .GetExtension <UsersSSExt>(usersRow); PMSetup setupRow = PXSelect <PMSetup> .Select(this.Base); PMSetupSSExt setupSSExtRow = PXCache <PMSetup> .GetExtension <PMSetupSSExt>(setupRow); if (usersSSExtRow != null && usersSSExtRow.UsrSmartsheetRefreshToken != null) { List <string> ssToken = refreshToken(setupSSExtRow.UsrSmartsheetClientID, setupSSExtRow.UsrSmartsheetAppSecret, usersSSExtRow.UsrSmartsheetRefreshToken); if (ssToken.Count == 2) { usersSSExtRow.UsrSmartsheetToken = ssToken[0]; usersSSExtRow.UsrSmartsheetRefreshToken = ssToken[1]; this.Base.UserProfile.Update(usersRow); this.Base.Actions.PressSave(); return(usersSSExtRow.UsrSmartsheetToken); } } return(""); }
/// <summary> /// Verifies the definition of Smartsheet Setup parameters and the Token retrieval /// </summary> /// <param name="pmSetupSSExt"></param> public void SetupValidation(UsersSSExt usersSSExt, PMSetupSSExt pmSetupSSExt) { if (usersSSExt == null || String.IsNullOrEmpty(usersSSExt.UsrSmartsheetToken)) { throw new PXException(SmartsheetConstants.Messages.SMARTSHEET_TOKEN_MISSING); } if (pmSetupSSExt == null || String.IsNullOrEmpty(pmSetupSSExt.UsrDefaultRateTableID)) { throw new PXException(SmartsheetConstants.Messages.SMARTSHEET_RATE_TABLE_MISSING); } return; }
protected virtual void PMSetup_RowPersisting(PXCache sender, PXRowPersistingEventArgs e) { if (e.Row == null) { return; } PMSetup pmSetupRow = (PMSetup)e.Row; PMSetupSSExt pmSetupSSExtRow = PXCache <PMSetup> .GetExtension <PMSetupSSExt>(pmSetupRow); if (pmSetupSSExtRow.UsrSmartsheetClientID != null && String.IsNullOrEmpty(pmSetupSSExtRow.UsrDefaultRateTableID)) { throw new PXRowPersistingException(typeof(PMSetupSSExt.usrDefaultRateTableID).Name, null, ErrorMessages.FieldIsEmpty); } }
public void GetSmartsheetToken(string currentURL) { Guid loggedUser = Base.Accessinfo.UserID; Users usersRow = PXSelect <Users, Where <Users.pKID, Equal <Required <Users.pKID> > > > .Select(this.Base, loggedUser); UsersSSExt usersSSExtRow = PXCache <Users> .GetExtension <UsersSSExt>(usersRow); this.Base.UserProfile.Current = this.Base.UserProfile.Search <Users.pKID>(usersRow.PKID); PMSetup setupRow = PXSelect <PMSetup> .Select(this.Base); PMSetupSSExt setupSSExtRow = PXCache <PMSetup> .GetExtension <PMSetupSSExt>(setupRow); if (currentURL.IndexOf(SmartsheetConstants.SSCodeRequest.STATE, StringComparison.CurrentCultureIgnoreCase) > 0) { string ssCode = getSSCodeString(currentURL); if (ssCode.Trim().Length > 0) { List <string> ssToken = getSSToken(setupSSExtRow.UsrSmartsheetClientID, ssCode, setupSSExtRow.UsrSmartsheetAppSecret); if (ssToken.Count == 2) { usersSSExtRow.UsrSmartsheetToken = ssToken[0]; usersSSExtRow.UsrSmartsheetRefreshToken = ssToken[1]; this.Base.UserProfile.Update(usersRow); this.Base.Actions.PressSave(); } } } SendRefreshCall(); return; }
/// <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) { bool recordedInAcumatica = false; int primaryColumnPosition = 0; 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) { PMTask 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) { pmTaskNewEntry = projectEntryGraph.Tasks.Insert(pmTaskNewEntry); if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.DESCRIPTION]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.DESCRIPTION]].Value != null) { projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.description>(pmTaskNewEntry, updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.DESCRIPTION]].Value.ToString()); } else { projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.description>(pmTaskNewEntry, SmartsheetConstants.Messages.DEFAULT_TASK_DESCRIPTION); } projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.rateTableID>(pmTaskNewEntry, pmSetupSSExt.UsrDefaultRateTableID); projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.status>(pmTaskNewEntry, SmartsheetConstants.SSConstants.ACTIVE); if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]].Value != null) { if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]].Value is DateTime) { DateTime startDate = (DateTime)updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]].Value; projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.startDate>(pmTaskNewEntry, startDate); projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.plannedStartDate>(pmTaskNewEntry, startDate); } } if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]].Value != null) { if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]].Value is DateTime) { DateTime endDate = (DateTime)updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]].Value; projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.endDate>(pmTaskNewEntry, endDate); projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.plannedEndDate>(pmTaskNewEntry, endDate); } } if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE]].Value != null && pmTaskNewEntry.Status == SmartsheetConstants.SSConstants.ACTIVE) { projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.completedPercent>(pmTaskNewEntry, (decimal)((double)updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE]].Value * 100)); } PMTaskSSExt pmTaskExtRow = PXCache <PMTask> .GetExtension <PMTaskSSExt>(pmTaskNewEntry); pmTaskExtRow.UsrSmartsheetTaskID = updatedSSRow.Id; pmTaskNewEntry = projectEntryGraph.Tasks.Update(pmTaskNewEntry); } } } else //Previously existing row in SS { //Fields updated: Description, Start Date, End Date, % complete. if (updatedSSRow.Cells[primaryColumnPosition].Value != null) { if (currentTaskRow != null) { if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.DESCRIPTION]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.DESCRIPTION]].Value != null) { projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.description>(currentTaskRow, updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.DESCRIPTION]].Value.ToString()); } if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]].Value != null) { if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]].Value is DateTime) { DateTime startDate = (DateTime)updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.START_DATE]].Value; projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.startDate>(currentTaskRow, startDate); } } if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]].Value != null) { if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]].Value is DateTime) { DateTime endDate = (DateTime)updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.END_DATE]].Value; projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.endDate>(currentTaskRow, endDate); } } if (updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE]] != null && updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE]].Value != null && currentTaskRow.Status == SmartsheetConstants.SSConstants.ACTIVE) { projectEntryGraph.Tasks.Cache.SetValueExt <PMTask.completedPercent>(currentTaskRow, (decimal)((double)updatedSSRow.Cells[columnPositionMap[SmartsheetConstants.ColumnMapping.PCT_COMPLETE]].Value * 100)); } currentTaskRow = projectEntryGraph.Tasks.Update(currentTaskRow); } } } } break; } else { primaryColumnPosition += 1; } } return; }
/// <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="isMassProcess">Indicates if it's used in a processing page</param> public void CreateUpdateGanttProject(ProjectEntry projectEntryGraph, PMProject pmProjectRow, string refreshedToken = "", bool isMassProcess = false) { //Primary Data View is set projectEntryGraph.Project.Current = pmProjectRow; SmartsheetHelper smartSheetHelperObject = new SmartsheetHelper(); string sheetName = pmProjectRow.ContractCD.Trim() + " - " + pmProjectRow.Description.Trim(); sheetName = smartSheetHelperObject.Left(sheetName, SmartsheetConstants.SSConstants.SS_PROJECT_NAME_LENGTH); PMSetup setupRecord = projectEntryGraph.Setup.Select(); PMSetupSSExt pmSetupSSExt = PXCache <PMSetup> .GetExtension <PMSetupSSExt>(setupRecord); PMProjectSSExt pmProjectSSExt = PXCache <PMProject> .GetExtension <PMProjectSSExt>(pmProjectRow); Users userRecord = PXSelect <Users, Where <Users.pKID, Equal <Current <AccessInfo.userID> > > > .Select(projectEntryGraph); UsersSSExt userRecordSSExt = PXCache <Users> .GetExtension <UsersSSExt>(userRecord); smartSheetHelperObject.SetupValidation(userRecordSSExt, pmSetupSSExt); smartSheetHelperObject.ProjectValidation(projectEntryGraph); try { Token token = new Token(); token.AccessToken = (String.IsNullOrEmpty(refreshedToken)) ? userRecordSSExt.UsrSmartsheetToken : refreshedToken; SmartsheetClient smartsheetClient = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build(); long?sheetSelected; Dictionary <string, long> currentColumnMap = new Dictionary <string, long>(); ////////////////// //Information from Acumatica is updated in Smartsheet ////////////////// 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); } else //Acumatica Project has not been linked to Smartsheet { //Sheet is created from a Gantt Template available in SmartSheet Sheet sheet = new Sheet.CreateSheetFromTemplateBuilder(sheetName, SmartsheetConstants.SSConstants.BASIC_SS_PROJECT_WITH_GANTT).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); } smartSheetHelperObject.AdjustGanttSheet(smartsheetClient, sheet, currentColumnMap); //Acumatica Tasks are added as Smartsheet rows List <Row> newSSRows = smartSheetHelperObject.InsertAcumaticaTasksInSS(projectEntryGraph, currentColumnMap, null, true); IList <Row> ssRows = smartsheetClient.SheetResources.RowResources.AddRows((long)sheet.Id, newSSRows); int ssTaskIDPosition = 0; if (ssRows.Count > 0 && ssRows[0].Cells != null) { ssTaskIDPosition = smartSheetHelperObject.GetSSTaskPosition(ssRows[0].Cells, currentColumnMap[SmartsheetConstants.GanttTemplateMapping.TASK_NAME]); } smartSheetHelperObject.InsertAcumaticaSubTasks(projectEntryGraph, smartsheetClient, sheet, ssRows, smartSheetHelperObject, currentColumnMap, ssTaskIDPosition); 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); projectEntryGraph.Actions.PressSave(); if (isMassProcess) { PXProcessing.SetInfo(String.Format(SmartsheetConstants.Messages.SUCCESSFULLY_SYNCED, pmProjectRow.ContractCD)); } } catch (Exception e) { if (e.Message.Contains(SmartsheetConstants.SSConstants.EXPIRED_TOKEN_MESSAGE)) { MyProfileMaint profileMaintGraph = PXGraph.CreateInstance <MyProfileMaint>(); MyProfileMaintExt graphExtended = profileMaintGraph.GetExtension <MyProfileMaintExt>(); string updatedToken = graphExtended.RefreshSmartsheetToken(); CreateUpdateGanttProject(projectEntryGraph, pmProjectRow, updatedToken, isMassProcess = false); } else { throw new PXException(e.Message); } } }