RayFlow users can create email profiles for themselves; however, RayFlow Admins can create email profiles for any user and any group. The in-phase task “Select package for mail tracking” option can also create an email profile for a user.
How can one find out how many email profiles exist and how each are configured without accessing each profile in turn via the user interface?
The following queries can be used to achieve this requirement, and example data is also provided so as to explain their result sets.
Profiles in Projects
SELECT ep.EmailProfileName AS [Profile],
ISNULL(ep.EmailProfileDescription,'No Description') AS [Description],
ep.EmailProfilePublic AS [Public],p.ProjectName AS Project
FROM EmailProfiles ep
JOIN Projects p ON p.ProjectID = ep.ProjectID
ORDER BY ep.EmailProfileName ASC
This RayFlow system only has one project which currently has six email profiles. The two PackTrack_<username> profiles were created when that user enabled the in-phase task “Select package for mail tracking” option. The Global_PackTrack public profile was created by the RayFlow admin and assigned to the “Configuration > Basic > Message Settings > PackTrack Profile” dropdown field, causing all further in-phase task “Select package for mail tracking” option actions to use this profile instead of creating a new or using an existing PackTrack_<username> profile.
Profile Associations
SELECT ep.EmailProfileName AS [Profile],
ISNULL(ep.EmailProfileDescription,'No Description') AS [Description],
ISNULL(au.UserName,'Group Association') AS UserName,
ISNULL(ar.RoleName,'User Association') AS GroupName,
ep.EmailProfilePublic AS [Public],p.ProjectName AS Project
FROM EmailProfiles ep
JOIN EmailProfilesInRolle epir ON epir.EmailProfileID = ep.EmailProfileID
LEFT JOIN aspnet_Users au ON au.UserId = epir.UserId
LEFT JOIN aspnet_Roles ar ON ar.RoleId = epir.RoleId
JOIN Projects p ON p.ProjectID = ep.ProjectID
ORDER BY ep.EmailProfileName ASC
The Global_PackTrack public profile was originally assigned by the RayFlow admin to four project groups. The Evaluator user became associated with this profile when they enabled the in-phase task “Select package for mail tracking” option.
Profile Rules
SELECT ep.EmailProfileName AS [Profile],
ISNULL(ep.EmailProfileDescription,'No Description') AS [Description],
tl.TaskName AS Phase,
ISNULL(sm.StatusMainName,'All Statuses') AS [Status],
ISNULL(ev.EmailEventDescription,'All Events') AS [Event],
ISNULL(ec.EmailConditionDescription,'All Conditions') AS Condition,
ISNULL(c.CatName,'All Categories') AS Category,
ISNULL(pl.ParameterName,'All Parameters') AS DataField,
ISNULL(er.ParameterValue,'Not Specified') AS DataFieldValue,
ep.EmailProfilePublic AS [Public],p.ProjectName AS Project
FROM Projects p
JOIN EmailProfiles ep ON ep.ProjectID = p.ProjectID
JOIN EmailRules er ON er.EmailProfileID = ep.EmailProfileID
JOIN TaskList tl ON tl.TaskListID = er.TaskListID
LEFT JOIN StatusMain sm ON sm.StatusMainID = er.StatusMainID
LEFT JOIN EmailEvents ev ON ev.EmailEventID = er.EmailEventID
LEFT JOIN EmailConditions ec ON ec.EmailConditionID = er.EmailConditionID
LEFT JOIN Category c ON c.CategoryID = er.CategoryID
LEFT JOIN ParameterList pl ON pl.ParameterListID = er.ParameterListID
Profiles created by the in-phase task “Select package for mail tracking” option target all phases with all statuses, all events, all categories and all data fields (All users should be made aware of this fact so that they know to configure their PackTrack_<username> profile once it has been created so as to prevent unwanted emails). Their condition will always be “My Selected Packages”. The Global_PackTrack public profile has kept the RayFlow admins configuration; however, its condition setup will be changed to “My Selected Packages” once in use.
Profile Rule Associations
SELECT ep.EmailProfileName AS [Profile],
ISNULL(ep.EmailProfileDescription,'No Description') AS [Description],
tl.TaskName AS Phase,
ISNULL(au.UserName,'Group Association') AS UserName,
ISNULL(ar.RoleName,'User Association') AS GroupName,
ISNULL(sm.StatusMainName,'All Statuses') AS [Status],
ISNULL(ev.EmailEventDescription,'All Events') AS [Event],
ISNULL(ec.EmailConditionDescription,'All Conditions') AS Condition,
ISNULL(c.CatName,'All Categories') AS Category,
ISNULL(pl.ParameterName,'All Parameters') AS DataField,
ISNULL(er.ParameterValue,'Not Specified') AS DataFieldValue,
ep.EmailProfilePublic AS [Public],proj.ProjectName AS Project
FROM Projects proj
JOIN EmailProfiles ep ON ep.ProjectID = proj.ProjectID
JOIN EmailProfilesInRolle epir ON epir.EmailProfileID = ep.EmailProfileID
LEFT JOIN aspnet_Users au ON au.UserId = epir.UserId
LEFT JOIN aspnet_Roles ar ON ar.RoleId = epir.RoleId
JOIN EmailRules er ON er.EmailProfileID = ep.EmailProfileID
JOIN TaskList tl ON tl.TaskListID = er.TaskListID
LEFT JOIN StatusMain sm ON sm.StatusMainID = er.StatusMainID
LEFT JOIN EmailEvents ev ON ev.EmailEventID = er.EmailEventID
LEFT JOIN EmailConditions ec ON ec.EmailConditionID = er.EmailConditionID
LEFT JOIN Category c ON c.CategoryID = er.CategoryID
LEFT JOIN ParameterList pl ON pl.ParameterListID = er.ParameterListID
ORDER BY ep.EmailProfileName,Phase ASC
This result set merges the last two queries into a clear email profile configuration.
In-phase task “Select package for mail tracking” option
SELECT au.UserName,p.ParameterValue AS Package
FROM aspnet_Users au
JOIN EmailSelectedPackages esp ON esp.UserId = au.UserId
JOIN Parameter p ON p.PackID = esp.PackID
JOIN ReportStandardFields rsf ON rsf.ParameterListID = p.ParameterListID
JOIN ReportFieldType rft ON rft.TypeID = rsf.TypeID
WHERE rft.TypeName = 'ApplicationName'
This query will only work if the “Configuration > Report Fieldmapp” has been setup. It’s result set displays those packages that have had the in-phase task “Select package for mail tracking” option enabled and which user performed that action.
The only thing missing in RayFlow 1.10 SP1 concerning email profiles is the ability to identify which email profile was in use when a user enabled the in-phase task “Select package for mail tracking” option. The following query attempts to do this, but fails to do so (7 rows instead of 3):
SELECT DISTINCT au.UserName,p.ParameterValue AS Package,
ep.EmailProfileName AS [Profile],
ISNULL(ep.EmailProfileDescription,'No Description') AS [Description],
ep.EmailProfilePublic AS [Public]
FROM aspnet_Users au
JOIN EmailSelectedPackages esp ON esp.UserId = au.UserId
JOIN Parameter p ON p.PackID = esp.PackID
JOIN EmailProfilesInRolle epir ON epir.UserId = au.UserId
JOIN EmailProfiles ep ON ep.EmailProfileID = epir.EmailProfileID
JOIN ReportStandardFields rsf ON rsf.ParameterListID = p.ParameterListID
JOIN ReportFieldType rft ON rft.TypeID = rsf.TypeID
WHERE rft.TypeName = 'ApplicationName'
RayFlow 2.0 should resolve this problem via its improved email profile functionality.
This KB has been attached in PDF format for your convenience.
Comments
0 comments
Article is closed for comments.