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("");
}
protected virtual void Users_RowSelected(PXCache cache, PXRowSelectedEventArgs e)
{
if (e.Row == null)
{
return;
}
Users userRow = e.Row as Users;
UsersSSExt userExtRow = PXCache <Users> .GetExtension <UsersSSExt>(userRow);
refreshSSToken.SetEnabled(userExtRow != null && userExtRow.UsrSmartSheetStatus == SmartsheetConstants.Messages.SS_CONNECTED);
}
/// <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;
}
/// <summary>
/// Gets the SmartSheet templates
/// </summary>
/// <param name="refreshedToken">Smartsheet Token</param>
/// <returns></returns>
public Dictionary <string, string> GetTemplateSS(string refreshedToken = "")
{
Dictionary <string, string> templateSS = new Dictionary <string, string>();
Users userRecord = PXSelect <
Users,
Where <Users.pKID, Equal <Required <AccessInfo.userID> > > >
.Select(this.Base, this.Base.Accessinfo.UserID);
if (userRecord == null)
{
throw new PXException(SmartsheetConstants.Messages.ERROR_USER);
}
UsersSSExt userRecordSSExt = PXCache <Users> .GetExtension <UsersSSExt>(userRecord);
try
{
Token token = new Token();
token.AccessToken = (String.IsNullOrEmpty(refreshedToken)) ? userRecordSSExt.UsrSmartsheetToken : refreshedToken;
SmartsheetClient smartsheetClient = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
PaginatedResult <Template> templates = smartsheetClient.TemplateResources.ListPublicTemplates(null);
if (templates.TotalCount > 0)
{
foreach (Template dataTemplate in templates.Data)
{
templateSS.Add(dataTemplate.Id.ToString(), dataTemplate.Name);
}
}
return(templateSS);
}
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();
return(GetTemplateSS(updatedToken));
}
else
{
throw new PXException(e.Message);
}
}
}
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 or updates users obtained from SmartSheet in Acumatica
/// </summary>
/// <param name="refreshedToken"></param>
public void GetUsersSS(string refreshedToken = "")
{
// User connection with SmartSheet
Users userRecord = PXSelect <
Users,
Where <Users.pKID, Equal <Required <AccessInfo.userID> > > >
.Select(this.Base, this.Base.Accessinfo.UserID);
if (userRecord == null)
{
throw new PXException(SmartsheetConstants.Messages.ERROR_USER);
}
UsersSSExt userRecordSSExt = PXCache <Users> .GetExtension <UsersSSExt>(userRecord);
try
{
Token token = new Token();
token.AccessToken = (String.IsNullOrEmpty(refreshedToken)) ? userRecordSSExt.UsrSmartsheetToken : refreshedToken;
SmartsheetClient smartsheetClient = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
PaginatedResult <User> smartsheetUserSet = smartsheetClient.UserResources.ListUsers(null, null);
if (smartsheetUserSet.TotalCount > 0)
{
PXCache <EPUsersListSS> usersListCache = this.Base.Caches <EPUsersListSS>();
foreach (User dataUsers in smartsheetUserSet.Data)
{
EPUsersListSS epUsersListSSRow = UserList.Select().Where(x => ((EPUsersListSS)x).Ssuserid == dataUsers.Id).FirstOrDefault();
if (epUsersListSSRow == null)
{
EPUsersListSS ci = (EPUsersListSS)usersListCache.Insert(new EPUsersListSS
{
Ssuserid = dataUsers.Id,
FirstName = dataUsers.FirstName,
LastName = dataUsers.LastName,
Email = dataUsers.Email
});
}
else
{
epUsersListSSRow.FirstName = dataUsers.FirstName;
epUsersListSSRow.LastName = dataUsers.LastName;
epUsersListSSRow.Email = dataUsers.Email;
usersListCache.Update(epUsersListSSRow);
}
}
this.Base.Persist();
}
}
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();
GetUsersSS(updatedToken);
}
else
{
throw new PXException(e.Message);
}
}
}
/// <summary>
/// Verifies the connection Token and the existence of the Sheet
/// </summary>
/// <param name="projectEntryGraph">Project's Graph</param>
/// <param name="pmProjectRow">Current PMProject row</param>
/// <param name="refreshedToken">Existing token</param>
/// <param name="isMassProcess">Indicates if it's used in a processing page</param>
/// <returns></returns>
public SmartsheetClient CheckTokenSheetSS(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 <Required <AccessInfo.userID> > > >
.Select(projectEntryGraph, projectEntryGraph.Accessinfo.UserID);
UsersSSExt userRecordSSExt = PXCache <Users> .GetExtension <UsersSSExt>(userRecord);
smartSheetHelperObject.SetupValidation(userRecordSSExt, pmSetupSSExt);
smartSheetHelperObject.ProjectValidation(projectEntryGraph);
Token token = new Token();
token.AccessToken = (String.IsNullOrEmpty(refreshedToken)) ? userRecordSSExt.UsrSmartsheetToken : refreshedToken;
try
{
SmartsheetClient smartsheetClient = new SmartsheetBuilder()
.SetAccessToken(token.AccessToken)
.SetDateTimeFixOptOut(true)
.Build();
long?sheetSelected;
Dictionary <string, long> currentColumnMap = new Dictionary <string, long>();
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);
}
return(smartsheetClient);
}
catch (Exception ex)
{
if (ex.Message.Contains(SmartsheetConstants.SSConstants.EXPIRED_TOKEN_MESSAGE))
{
MyProfileMaint profileMaintGraph = PXGraph.CreateInstance <MyProfileMaint>();
MyProfileMaintExt graphExtended = profileMaintGraph.GetExtension <MyProfileMaintExt>();
string updatedToken = graphExtended.RefreshSmartsheetToken();
CheckTokenSheetSS(projectEntryGraph, pmProjectRow, updatedToken);
}
if (ex.Message.Contains(SmartsheetConstants.SSConstants.NOTFOUND_PROJECT_MESSAGE))
{
if (isMassProcess)
{
SmartsheetClient smartsheetClient = new SmartsheetBuilder()
.SetAccessToken(token.AccessToken)
.SetDateTimeFixOptOut(true)
.Build();
UnlinkSmartsheetProject(projectEntryGraph);
CreateUpdateGanttProject(projectEntryGraph, pmProjectRow, smartsheetClient, true);
}
else
{
UnlinkSmartsheetProject(projectEntryGraph);
throw new PXException(SmartsheetConstants.Messages.UNLINK_PROJECT);
}
}
else
{
throw new PXException(ex.Message);
}
return(null);
}
}
/// <summary>
/// Creates or updates the smartsheet users listed in "EPUsersListSS" master table
/// </summary>
/// <param name="refreshedToken"></param>
public void GetUsersSS(string refreshedToken = "")
{
//The smartsheet account should support the UserResources.ListUsers() functionality
//Otherwise the message "The operation you are attempting to perform is not supported by your plan." is received
// User connection with SmartSheet
Users userRecord = PXSelect <
Users,
Where <Users.pKID, Equal <Required <AccessInfo.userID> > > >
.Select(this.Base, this.Base.Accessinfo.UserID);
if (userRecord == null)
{
throw new PXException(SmartsheetConstants.Messages.ERROR_USER);
}
UsersSSExt userRecordSSExt = PXCache <Users> .GetExtension <UsersSSExt>(userRecord);
try
{
Token token = new Token();
token.AccessToken = (String.IsNullOrEmpty(refreshedToken)) ? userRecordSSExt.UsrSmartsheetToken : refreshedToken;
SmartsheetClient smartsheetClient = new SmartsheetBuilder()
.SetAccessToken(token.AccessToken)
.SetDateTimeFixOptOut(true) //See NOTE ON 2.93.0 RELEASE on https://github.com/smartsheet-platform/smartsheet-csharp-sdk
.Build();
PaginatedResult <User> smartsheetUserSet = smartsheetClient.UserResources.ListUsers(null, null);
if (smartsheetUserSet.TotalCount > 0)
{
PXCache <EPUsersListSS> usersListCache = this.Base.Caches <EPUsersListSS>();
foreach (User dataUsers in smartsheetUserSet.Data)
{
EPUsersListSS epUsersListSSRow = UserList.Select().Where(x => ((EPUsersListSS)x).Ssuserid == dataUsers.Id).FirstOrDefault();
if (epUsersListSSRow == null)
{
EPUsersListSS ci = (EPUsersListSS)usersListCache.Insert(new EPUsersListSS
{
Ssuserid = dataUsers.Id,
FirstName = dataUsers.FirstName,
LastName = dataUsers.LastName,
Email = dataUsers.Email
});
}
else
{
epUsersListSSRow.FirstName = dataUsers.FirstName;
epUsersListSSRow.LastName = dataUsers.LastName;
epUsersListSSRow.Email = dataUsers.Email;
usersListCache.Update(epUsersListSSRow);
}
}
this.Base.Persist();
}
}
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();
GetUsersSS(updatedToken);
}
else
{
throw new PXException(e.Message);
}
}
}
/// <summary>
/// Gets the columns of the Sheet in SmartSheet
/// </summary>
/// <param name="refreshedToken">Smartsheet Token</param>
/// <returns></returns>
public Dictionary <string, string> GetColumnSheet(string refreshedToken = "")
{
PMTemplateListSS pmTemplateListSSRecord = this.TemplateSetup.Current;
if (pmTemplateListSSRecord == null)
{
throw new PXException(SmartsheetConstants.Messages.ERROR_SETUP);
}
Users userRecord = PXSelect <
Users,
Where <Users.pKID, Equal <Required <AccessInfo.userID> > > >
.Select(this.Base, this.Base.Accessinfo.UserID);
if (userRecord == null)
{
throw new PXException(SmartsheetConstants.Messages.ERROR_USER);
}
UsersSSExt userRecordSSExt = PXCache <Users> .GetExtension <UsersSSExt>(userRecord);
if (userRecordSSExt == null)
{
throw new PXException(SmartsheetConstants.Messages.ERROR_USEREXT);
}
Dictionary <string, string> currentColumnMap = new Dictionary <string, string>();
try
{
Token token = new Token();
token.AccessToken = (String.IsNullOrEmpty(refreshedToken)) ? userRecordSSExt.UsrSmartsheetToken : refreshedToken;
SmartsheetClient smartsheetClient = new SmartsheetBuilder().SetAccessToken(token.AccessToken).Build();
if (!String.IsNullOrEmpty(pmTemplateListSSRecord.TemplateSS))
{
Sheet sheet = new Sheet.CreateSheetFromTemplateBuilder(SmartsheetConstants.Messages.NAME_PROJECT_TEMP_SMARTSHEET, Convert.ToInt64(pmTemplateListSSRecord.TemplateSS)).Build();
//A temporary project is created in order to obtain the columns content
sheet = smartsheetClient.SheetResources.CreateSheet(sheet);
Sheet newlyCreatedSheet = smartsheetClient.SheetResources.GetSheet((long)sheet.Id, null, null, null, null, null, null, null);
currentColumnMap = new Dictionary <string, string>();
foreach (Column currentColumn in newlyCreatedSheet.Columns)
{
currentColumnMap.Add(currentColumn.Title, currentColumn.Type.ToString());
}
smartsheetClient.SheetResources.DeleteSheet((long)sheet.Id);
}
return(currentColumnMap);
}
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();
return(GetColumnSheet(updatedToken));
}
else
{
throw new PXException(e.Message);
}
}
}
/// <summary>
/// Gets the SmartSheet templates
/// </summary>
/// <param name="refreshedToken">Smartsheet Token</param>
/// <returns></returns>
public Dictionary <string, string> GetTemplateSS(string refreshedToken = "")
{
Dictionary <string, string> templateSS = new Dictionary <string, string>();
Users userRecord = PXSelect <
Users,
Where <Users.pKID, Equal <Required <AccessInfo.userID> > > >
.Select(this.Base, this.Base.Accessinfo.UserID);
if (userRecord == null)
{
throw new PXException(SmartsheetConstants.Messages.ERROR_USER);
}
UsersSSExt userRecordSSExt = PXCache <Users> .GetExtension <UsersSSExt>(userRecord);
try
{
Token token = new Token();
token.AccessToken = (String.IsNullOrEmpty(refreshedToken)) ? userRecordSSExt.UsrSmartsheetToken : refreshedToken;
SmartsheetClient smartsheetClient = new SmartsheetBuilder()
.SetAccessToken(token.AccessToken)
.SetDateTimeFixOptOut(true) //See NOTE ON 2.93.0 RELEASE on https://github.com/smartsheet-platform/smartsheet-csharp-sdk
.Build();
PaginationParameters paginationParams = new PaginationParameters(true, null, null); //includeAll = true ==> Ignoring paging and size if defined
PaginatedResult <Template> templates = smartsheetClient.TemplateResources.ListPublicTemplates(paginationParams);
PaginatedResult <Template> userTemplates = smartsheetClient.TemplateResources.ListUserCreatedTemplates(paginationParams);
if (templates.TotalCount > 0)
{
foreach (Template dataTemplate in templates.Data)
{
templateSS.Add(dataTemplate.Id.ToString(), dataTemplate.Name);
}
}
if (userTemplates.TotalCount > 0)
{
foreach (Template userTemplate in userTemplates.Data)
{
templateSS.Add(userTemplate.Id.ToString(), userTemplate.Name);
}
}
return(templateSS);
}
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();
return(GetTemplateSS(updatedToken));
}
else
{
throw new PXException(e.Message);
}
}
}
