If you are wanting to mass set all users alert preferences for the Portal Framework and module notifications, please follow the steps below for instructions.
--In all below sections, the 2 is for email only. Set for the type of alert wanted--
0 = None
2 = Email ONLY
4 = Popup ONLY
6 = Email + Popup
8 = Mobile Email ONLY
10 = Email + Mobile Email
12 = Popup + Mobile Email
14 = Email + Popup + Mobile Email
1. From the SQL server, take a backup of the Passageways Portal Framework database.
2. Copy the script below in to a new query window.
NOTE: If you do not see the Production_Portal_PortalFx database naming convention, do a Find & Replace for Production_Portal_PortalFx and replace with your Portal Framework database name.
3. This section deletes all existing alert preferences in order to set them all. Remove from the script if you do not want to remove existing alerts.
DELETE Production_Portal_PortalFx.dbo.PWUserPreferences WHERE (
PreferenceKey = 'ReportBypassed' OR PreferenceKey = 'ReportStatusChanged' OR PreferenceKey = 'ReportWaiting'
OR PreferenceKey = 'AnnouncementPosted'
OR PreferenceKey = 'DistributionCompleted' OR PreferenceKey = 'DistributionSent' OR PreferenceKey = 'FormActivated' OR PreferenceKey = 'FormDeactivated' OR
PreferenceKey = 'FormReadyForActivation' OR PreferenceKey = 'FormSubmitted' OR PreferenceKey = 'FormSubmittedForApproval' OR PreferenceKey = 'SubmissionApproved' OR
PreferenceKey = 'SubmissionApprovedForReview' OR PreferenceKey = 'SubmissionDeclined'
OR PreferenceKey = 'AddPost' OR PreferenceKey = 'AddThread' OR PreferenceKey = 'DeletePost' OR PreferenceKey = 'EditPost' OR
PreferenceKey = 'LockThread' OR PreferenceKey = 'MoveThread' OR PreferenceKey = 'ReportContentNotification' OR PreferenceKey = 'StickyThread'
OR PreferenceKey = 'MovedWorkOrder' OR PreferenceKey = 'NewWorkOrder' OR PreferenceKey = 'WorkOrderClosed' OR
PreferenceKey = 'WorkOrderCommentAdded' OR PreferenceKey = 'WorkOrderEdited' OR PreferenceKey = 'WorkOrderReopened'
OR PreferenceKey = 'ProcedureAddedOrRemoved' OR PreferenceKey = 'ProcedureChanged' OR PreferenceKey = 'ProcedurePublished' OR
PreferenceKey = 'ProductDescriptionChanged' OR PreferenceKey = 'ProductPromotionsChanged' OR PreferenceKey = 'ProductSalesTipsChanged'
OR PreferenceKey = 'ContractNotification' OR PreferenceKey = 'VendorNotification' OR PreferenceKey = 'VendorReview' OR PreferenceKey = 'ChecklistItem'
OR (PreferenceKey = 'Miscellaneous' and AppID = 'Passageways.Modules.VendorManagement') OR PreferenceKey = 'DocumentReview' OR PreferenceKey = 'ContractExpiration'
OR PreferenceKey = 'ChangedPasswordNotification' OR PreferenceKey = 'DeactivatedWorkgroupNotification' OR PreferenceKey = 'DeletedWorkgroupNotification' OR
PreferenceKey = 'DocumentSubscriptionNotification' OR PreferenceKey = 'MiscellaneousNotification' OR PreferenceKey = 'UserCommentsStatusNotification' OR
PreferenceKey = 'UserProfileChangedNotification'
)
4. Set the users' alert preferences
--If you wish to only do a subset of users, specify them in the Select query below. Existing query will set alert preferences for all activated users.
Declare @userID as varchar(50)
declare c1 cursor for
Select DISTINCT UserID from [Production_Portal_PortalFx].[dbo].[PWUser] where activated = 1
--DO NOT TOUCH THE BELOW SECTION
open c1
fetch next from c1 into @userID
while @@fetch_status=0
begin
--DO NOT TOUCH THE ABOVE SECTION
--In all below sections, the 2 is for email only. Set for the type of alert wanted 0 = None, 2 = Email, 4 = Popup, 6 = Email + Popup.
--Announcements
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Announcements','AnnouncementPosted','2')
--Expense Reports
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.ExpenseTracking','ReportBypassed','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.ExpenseTracking','ReportStatusChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.ExpenseTracking','ReportWaiting','2')
--Form Builder
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','DistributionCompleted','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','DistributionSent','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormActivated','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormDeactivated','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormReadyForActivation','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormSubmitted','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','FormSubmittedForApproval','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','SubmissionApproved','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','SubmissionApprovedForReview','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.FormBuilder','SubmissionDeclined','2')
--Conversations
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','AddPost','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','AddThread','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','DeletePost','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','EditPost','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','LockThread','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','MoveThread','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','ReportContentNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Forums','StickyThread','2')
--HelpDesk
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','MovedWorkOrder','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','NewWorkOrder','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderClosed','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderCommentAdded','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderEdited','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.HelpDesk','WorkOrderReopened','2')
--Products and Services
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProcedureAddedOrRemoved','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProcedureChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProcedurePublished','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProductDescriptionChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProductPromotionsChanged','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Products','ProductSalesTipsChanged','2')
--Vendor Management
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','ChecklistItem','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','ContractExpiration','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','DocumentReview','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','Miscellaneous','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.VendorManagement','VendorReview','2')
--Portal Framework
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','ChangedPasswordNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','DeactivatedWorkgroupNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','DeletedWorkgroupNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','DocumentSubscriptionNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','MiscellaneousNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','UserCommentsStatusNotification','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Portal','UserProfileChangedNotification','2')
--Calendar
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Calendar','CalendarSubscription','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Calendar','EventAttendence','2')
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Calendar','EventManagement','2')
--Reservations
INSERT INTO Production_Portal_PortalFx.dbo.PWUserPreferences (UserID,AppID,PreferenceKey,PreferenceValue) VALUES (@userID,'Passageways.Modules.Reservations','ReservationItemCreated','2')
--DO NOT TOUCH THE BELOW SECTION
fetch next from c1 into @userID
end
close c1
deallocate c1
--DO NOT TOUCH THE ABOVE SECTION
Comments
0 comments
Please sign in to leave a comment.