USP_SCHEDULE_USAGE_TRACKING
Definition
Copy
CREATE procedure dbo.USP_SCHEDULE_USAGE_TRACKING
as
begin
DECLARE @TIME TIME(0)
DECLARE @DIFF INT
DECLARE @CLARIFYSITEID NVARCHAR(100)
DECLARE @WEEKLYSSCHEDULEID UNIQUEIDENTIFIER
DECLARE @MONTHLYSCHEDULEID UNIQUEIDENTIFIER
DECLARE @DATE DATE
DECLARE @CHANGEAGENTID UNIQUEIDENTIFIER;
--Get the Clarify SiteId
SELECT @CLARIFYSITEID = clarifysiteId
FROM dbo.INSTALLATIONINFO
--Get a random number between 0 and the Clarify SiteId
SELECT @DIFF = Round(( ( @CLARIFYSITEID ) * Rand() ), 0)
--Set the default time to midnight
SET @TIME = '00:00:00';
--Get a random time to schedule the business process.
SELECT @TIME = Dateadd(second, @DIFF, @TIME);
--If the time exceeds 5:59 AM, then try to get a random time between midnight and 6:00 AM.
IF Datepart(HOUR, @TIME) > 5
BEGIN
DECLARE @TEMPTIME TIME(0)
SET @TEMPTIME = '00:00:00';
--Get a random number between 0 and 5
SELECT @DIFF = Round(( ( 5 ) * Rand() ), 0)
SET @TEMPTIME = Dateadd(hour, @DIFF, @TEMPTIME);
--Get a a new time between midnight and 5:59 AM by using the hour part from @TEMPTIME and the minute and seconds part from the original time.
--This will not work in SQL Server 2008 SET @TIME = Timefromparts(Datepart(HOUR, @TEMPTIME), Datepart(MINUTE, @TIME), Datepart(SECOND, @TIME), 0, 0)
Declare @Hour as int=Datepart(HOUR, @TEMPTIME)
Declare @Minute as int=Datepart(MINUTE, @TIME)
Declare @Second as int=Datepart(SECOND, @TIME)
Declare @Fraction as int=0
Select @TIME = Convert(time,Convert(varchar(4), @Hour)
+ ':' +
Convert(varchar(2), @Minute)
+ ':' +
Convert(varchar(2), @Second)
+ ':' +
Convert(varchar(2), @Fraction))
END
--Get the change agent.
EXEC dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT
@CHANGEAGENTID out;
SET @DATE = GETDATE()
-- Create the schedule for the weekly business process.
-- It will run every one week on Sunday at the random time calculated above.
-- By default it will be enabled and it's start time will be the day the below query is executed.
SET @WEEKLYSSCHEDULEID = '945F24DB-2A53-4BC8-B471-78BC6AEFA380'
IF NOT EXISTS (SELECT ID
FROM dbo.SCHEDULE
WHERE ID = @WEEKLYSSCHEDULEID)
BEGIN
INSERT INTO dbo.SCHEDULE
(ID,
TYPECODE,
FREQUENCY,
DAYSOFWEEK,
STARTDATE,
TIME)
VALUES (@WEEKLYSSCHEDULEID,
8,--here 8 Means Weekly
1,--here 1 means run every 1 week.
1,--here 1 means run on Sunday
@DATE,
@TIME);
INSERT INTO dbo.BUSINESSPROCESSSCHEDULE
(ID,
BUSINESSPROCESSID,
SCHEDULEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
VALUES ('E8278DBE-E6D7-4558-922E-84EEB9A35528',
'C7E36DA0-75B4-46A4-BC4B-06E0F69FE5B7',
@WEEKLYSSCHEDULEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@DATE,
@DATE);
END
--Create the schedule for the monthly business process.
--It will run every month on the last sunday of the month.
--By default it will be enabled and it's start time will be the day the below query is executed.
--Set the default time to midnight
SET @TIME = '00:00:00';
--Get a random time to schedule the business process.
SELECT @TIME = Dateadd(second, @DIFF, @TIME);
--If the time exceeds 5:59 AM, then try to get a random time between midnight and 6:00 AM.
IF Datepart(HOUR, @TIME) > 5
BEGIN
SET @TEMPTIME = '00:00:00';
--Get a random number between 0 and 5
SELECT @DIFF = Round(( ( 5 ) * Rand() ), 0)
SET @TEMPTIME = Dateadd(hour, @DIFF, @TEMPTIME);
--Get a a new time between midnight and 5:59 AM by using the hour part from @TEMPTIME and the minute and seconds part from the original time.
--This will not work in SQL Server 2008 SET @TIME = Timefromparts(Datepart(HOUR, @TEMPTIME), Datepart(MINUTE, @TIME), Datepart(SECOND, @TIME), 0, 0)
Select @TIME = Convert(time,Convert(varchar(4), @Hour)
+ ':' +
Convert(varchar(2), @Minute)
+ ':' +
Convert(varchar(2), @Second)
+ ':' +
Convert(varchar(2), @Fraction))
END
SET @MONTHLYSCHEDULEID = '5C9F0B4E-9897-46B9-ABFD-1E270330758A'
IF NOT EXISTS (SELECT ID
FROM dbo.SCHEDULE
WHERE ID = @MONTHLYSCHEDULEID)
BEGIN
INSERT INTO dbo.SCHEDULE
(ID,
TYPECODE,
FREQUENCY,
WEEKCODE,
DAYOFWEEKCODE,
STARTDATE,
TIME)
VALUES (@MONTHLYSCHEDULEID,
32,--here 32 means "Monthly Relative"
1,--here 1 means run every 1 month.
5,--here 5 means run in the last week of the month
1,--here 1 means runs on Sunday
@DATE,
@TIME);
INSERT INTO dbo.BUSINESSPROCESSSCHEDULE
(ID,
BUSINESSPROCESSID,
SCHEDULEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
VALUES ('FD829749-8BAB-49B9-98B9-28ECF3E064A6',
'F063111B-4D21-4735-92CD-49401CB32E8B',
@MONTHLYSCHEDULEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@DATE,
@DATE);
END
DECLARE @SYSTEMROLEID UNIQUEIDENTIFIER = '83E0479E-71FD-4645-8A62-B7288290A436',-- Blackbaud Built-In AppFx System User Role
@SPECID UNIQUEIDENTIFIER = 'c7e36da0-75b4-46a4-bc4b-06e0f69fe5b7'; -- Usage Tracking Weekly Business Process
IF NOT EXISTS (SELECT ID
FROM dbo.SYSTEMROLEPERM_BUSINESSPROCESS
WHERE SYSTEMROLEID = @SYSTEMROLEID
AND BUSINESSPROCESSCATALOGID = @SPECID)
BEGIN
INSERT INTO dbo.SYSTEMROLEPERM_BUSINESSPROCESS
(SYSTEMROLEID,
BUSINESSPROCESSCATALOGID,
GRANTORDENY,
ADDEDBYID,
CHANGEDBYID)
VALUES (@SYSTEMROLEID,
@SPECID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID);
END
SET @SPECID = 'f063111b-4d21-4735-92cd-49401cb32e8b'; -- Usage Tracking monthly Business Process
IF NOT EXISTS (SELECT ID
FROM dbo.SYSTEMROLEPERM_BUSINESSPROCESS
WHERE SYSTEMROLEID = @SYSTEMROLEID
AND BUSINESSPROCESSCATALOGID = @SPECID)
BEGIN
INSERT INTO dbo.SYSTEMROLEPERM_BUSINESSPROCESS
(SYSTEMROLEID,
BUSINESSPROCESSCATALOGID,
GRANTORDENY,
ADDEDBYID,
CHANGEDBYID)
VALUES (@SYSTEMROLEID,
@SPECID,
1,
@CHANGEAGENTID,
@CHANGEAGENTID);
END
END