USP_CREATE_FAFSCHEDULE
Definition
Copy
CREATE procedure dbo.USP_CREATE_FAFSCHEDULE
as
begin
declare @FAFPRODUCTID uniqueidentifier = 'a919502c-a2f6-4a56-9183-28e3f667916e', -- FAF
@FAFNFGPRODUCTID uniqueidentifier = 'd418d0f9-8c9c-4d52-8972-09c4b6b1e0a1', -- FAF NFG
@CHANGEAGENTID uniqueidentifier;
declare @PRODUCTFLAGID uniqueidentifier = @FAFPRODUCTID,
@CORRECTPRODUCTINSTALLED bit = 0;
declare @SCHEDULEID uniqueidentifier,
@BUSINESSPROCESSID uniqueidentifier,
@BUSINESSPROCESSINSTANCEID uniqueidentifier,
@SYSTEMROLEID uniqueidentifier = '83E0479E-71FD-4645-8A62-B7288290A436', --Blackbaud Built-In AppFx System User Role
@CURRENTDATE datetime = getdate(),
--Client timezone offset
@CLIENTDATETIMEWITHOFFSET datetimeoffset,
@SERVERTOCLIENTOFFSET integer,
@MINUTES integer,
@TIME time = '00:00:00',
@ENDTIME time = '23:59:59',
@TYPECODE tinyint = 4, --daily
@FREQUENCY tinyint = 1,-- every 1 day
@SUBDAYFREQUENCYTYPECODE tinyint = 4, -- 2 = second, 4 = minute, 8 = hourly
@SUBDAYFREQUENCY tinyint = 5; -- if minute, then every 30 minutes, if hour, then needs a smaller number
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CLIENTDATETIMEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(sysutcdatetime(), 1)
set @SERVERTOCLIENTOFFSET = datediff(hour, @CURRENTDATE, cast(@CLIENTDATETIMEWITHOFFSET as datetime))
set @MINUTES = round(rand() * 60, 0, 1)
------------------------------------------------------------------------------------------------------------
--Create FAF Group Business schedule with 5 minutes interval --------------------------
------------------------------------------------------------------------------------------------------------
set @SCHEDULEID = 'D52498DE-08F5-48CC-BB34-F26CBFFF250C'
set @BUSINESSPROCESSID = '3d458a59-9889-45bd-b885-133c66fc4727'
set @BUSINESSPROCESSINSTANCEID = '14DF1775-530B-48A3-896C-A08256E91EB8'
set @CURRENTDATE = getdate()
set @SUBDAYFREQUENCYTYPECODE = 4 -- 2 = second, 4 = minute, 8 = hourly
set @SUBDAYFREQUENCY = 5; -- if minute, then every 30 minutes, if hour, then needs a smaller number
if not exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID)
insert into dbo.SCHEDULE(ID, [ENABLED], TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE )
values ( @SCHEDULEID, 0, @TYPECODE, @FREQUENCY, @SUBDAYFREQUENCYTYPECODE, @SUBDAYFREQUENCY, @TIME, @ENDTIME,@CURRENTDATE );
if exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID) and not exists(select ID from dbo.BUSINESSPROCESSSCHEDULE where BUSINESSPROCESSID = @BUSINESSPROCESSID)
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, BUSINESSPROCESSPARAMETERSETID,SCHEDULEID,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
values(@BUSINESSPROCESSINSTANCEID,@BUSINESSPROCESSID,null,@SCHEDULEID,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID)
if not exists(select 1 from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID)
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
select ID, @BUSINESSPROCESSID,1,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
from dbo.SYSTEMROLE where ID = @SYSTEMROLEID
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID
------------------------------------------------------------------------------------------------------------
--Create FAF NFG Business schedule with 13 minutes interval --------------------------
------------------------------------------------------------------------------------------------------------
set @SCHEDULEID = '896F00B3-7F4B-4710-918A-0A3748929061'
set @BUSINESSPROCESSID = '264ef63b-2992-41b8-812f-315cf0f2e9c9'
set @BUSINESSPROCESSINSTANCEID = '83E52CDF-BB2F-4AD8-873E-24415244E9F4'
set @CURRENTDATE = getdate()
set @SUBDAYFREQUENCYTYPECODE = 4 -- 2 = second, 4 = minute, 8 = hourly
set @SUBDAYFREQUENCY = 13 -- if minute, then every 30 minutes, if hour, then needs a smaller number
if not exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID)
insert into dbo.SCHEDULE(ID, [ENABLED], TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE )
values ( @SCHEDULEID, 0, @TYPECODE, @FREQUENCY, @SUBDAYFREQUENCYTYPECODE, @SUBDAYFREQUENCY, @TIME, @ENDTIME,@CURRENTDATE );
if exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID) and not exists(select ID from dbo.BUSINESSPROCESSSCHEDULE where BUSINESSPROCESSID = @BUSINESSPROCESSID)
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, BUSINESSPROCESSPARAMETERSETID,SCHEDULEID,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
values(@BUSINESSPROCESSINSTANCEID,@BUSINESSPROCESSID,null,@SCHEDULEID,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID)
if not exists(select 1 from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID)
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
select ID, @BUSINESSPROCESSID,1,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
from dbo.SYSTEMROLE where ID = @SYSTEMROLEID
set @PRODUCTFLAGID = @FAFNFGPRODUCTID;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID
------------------------------------------------------------------------------------------------------------
--Friends Asking Friends Incentive Level Updates --------------------------
------------------------------------------------------------------------------------------------------------
set @SCHEDULEID = 'F13A9023-9450-4248-8A16-3D7115E17C71'
set @BUSINESSPROCESSID = '532268ce-d9bc-4b09-90df-3cd227d21274'
set @BUSINESSPROCESSINSTANCEID = '31415654-E172-45D8-85A8-93A78DE6B0D6'
set @CURRENTDATE = getdate()
set @TIME = '23:00'
set @ENDTIME = null
set @SUBDAYFREQUENCYTYPECODE = 0 -- 0 = None, 2 = second, 4 = minute, 8 = hourly
set @SUBDAYFREQUENCY = 0
if not exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID)
insert into dbo.SCHEDULE(ID, [ENABLED], TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, [TIME], SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE )
values ( @SCHEDULEID, 0, @TYPECODE, @FREQUENCY, @SUBDAYFREQUENCYTYPECODE, @TIME, @SUBDAYFREQUENCY, @ENDTIME, @ENDTIME,@CURRENTDATE );
if exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID) and not exists(select ID from dbo.BUSINESSPROCESSSCHEDULE where BUSINESSPROCESSID = @BUSINESSPROCESSID)
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, BUSINESSPROCESSPARAMETERSETID,SCHEDULEID,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
values(@BUSINESSPROCESSINSTANCEID,@BUSINESSPROCESSID,null,@SCHEDULEID,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID)
if not exists(select 1 from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID)
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
select ID, @BUSINESSPROCESSID,1,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
from dbo.SYSTEMROLE where ID = @SYSTEMROLEID
set @PRODUCTFLAGID = @FAFPRODUCTID;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID
------------------------------------------------------------------------------------------------------------
--Friends Asking Friends Email Schedule --------------------------
------------------------------------------------------------------------------------------------------------
set @SCHEDULEID = '1ECB355B-8369-4E9B-AEFB-CD4EA684BA13'
set @BUSINESSPROCESSID = '28aeab47-b7db-4cb9-9e6c-3c184cf19e5b'
set @BUSINESSPROCESSINSTANCEID = '33C5CB63-231D-41E0-9FDB-01C965915FFF'
set @CURRENTDATE = getdate()
set @TIME = '00:00:00'
set @ENDTIME = '23:59:59'
set @SUBDAYFREQUENCYTYPECODE = 4 -- 2 = second, 4 = minute, 8 = hourly
set @SUBDAYFREQUENCY = 10 -- if minute, then every 30 minutes, if hour, then needs a smaller number
if not exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID)
insert into dbo.SCHEDULE(ID, [ENABLED], TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE )
values ( @SCHEDULEID, 0, @TYPECODE, @FREQUENCY, @SUBDAYFREQUENCYTYPECODE, @SUBDAYFREQUENCY, @TIME, @ENDTIME,@CURRENTDATE );
if exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID) and not exists(select ID from dbo.BUSINESSPROCESSSCHEDULE where BUSINESSPROCESSID = @BUSINESSPROCESSID)
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, BUSINESSPROCESSPARAMETERSETID,SCHEDULEID,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
values(@BUSINESSPROCESSINSTANCEID,@BUSINESSPROCESSID,null,@SCHEDULEID,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID)
if not exists(select 1 from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID)
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
select ID, @BUSINESSPROCESSID,1,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
from dbo.SYSTEMROLE where ID = @SYSTEMROLEID
set @PRODUCTFLAGID = @FAFPRODUCTID;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID
------------------------------------------------------------------------------------------------------------
--Friends Asking Friends Donation Cache schedule --------------------------
------------------------------------------------------------------------------------------------------------
set @SCHEDULEID = '54D4F8F0-3800-4E43-8AED-9D16FF692706'
set @BUSINESSPROCESSID = '012D2720-6840-444E-B316-FB2C1EF8019B' -- business catalog id
set @BUSINESSPROCESSINSTANCEID = '9E8DC347-37A6-4B0D-BEBC-ED314B1D2605' -- generate a new ID
set @CURRENTDATE = getdate()
set @TIME = '23:30'
set @ENDTIME = null
set @SUBDAYFREQUENCYTYPECODE = 0 -- 2 = second, 4 = minute, 8 = hourly
set @SUBDAYFREQUENCY = 0 -- if minute, then every 30 minutes, if hour, then needs a smaller number
if not exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID)
insert into dbo.SCHEDULE(ID, [ENABLED], TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, [TIME], SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE )
values ( @SCHEDULEID, 0, @TYPECODE, @FREQUENCY, @SUBDAYFREQUENCYTYPECODE, @TIME, @SUBDAYFREQUENCY, @ENDTIME, @ENDTIME,@CURRENTDATE );
if exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID) and not exists(select ID from dbo.BUSINESSPROCESSSCHEDULE where BUSINESSPROCESSID = @BUSINESSPROCESSID)
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, BUSINESSPROCESSPARAMETERSETID,SCHEDULEID,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
values(@BUSINESSPROCESSINSTANCEID,@BUSINESSPROCESSID,null,@SCHEDULEID,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID)
if not exists(select 1 from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID)
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
select ID, @BUSINESSPROCESSID,1,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
from dbo.SYSTEMROLE where ID = @SYSTEMROLEID
set @PRODUCTFLAGID = @FAFPRODUCTID;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID
------------------------------------------------------------------------------------------------------------
--Friends Asking Friends Totals and Aggregates for Datamart schedule --------------------------
------------------------------------------------------------------------------------------------------------
set @SCHEDULEID = '22B605D5-E86C-4F09-9C2F-9EA7C4C7801A'
set @BUSINESSPROCESSID = 'd1af2411-b2fd-405e-b86a-e53b5bea324e' -- business catalog id
set @BUSINESSPROCESSINSTANCEID = 'FA6768DF-50F1-470A-948E-F34046546AEF' -- generate a new ID
set @CURRENTDATE = getdate()
--Client timezone offset
set @TIME = '23:30'
set @ENDTIME = null
set @SUBDAYFREQUENCYTYPECODE = 0 -- 2 = second, 4 = minute, 8 = hourly
set @SUBDAYFREQUENCY = 0 -- if minute, then every 30 minutes, if hour, then needs a smaller number
if not exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID)
insert into dbo.SCHEDULE(ID, [ENABLED], TYPECODE, FREQUENCY, SUBDAYFREQUENCYTYPECODE, [TIME], SUBDAYFREQUENCY, SUBDAYSTARTINGTIME, SUBDAYENDINGTIME, STARTDATE )
values ( @SCHEDULEID, 0, @TYPECODE, @FREQUENCY, @SUBDAYFREQUENCYTYPECODE, @TIME, @SUBDAYFREQUENCY, @ENDTIME, @ENDTIME,@CURRENTDATE );
if exists(select ID from dbo.SCHEDULE where ID = @SCHEDULEID) and not exists(select ID from dbo.BUSINESSPROCESSSCHEDULE where BUSINESSPROCESSID = @BUSINESSPROCESSID)
insert into dbo.BUSINESSPROCESSSCHEDULE (ID, BUSINESSPROCESSID, BUSINESSPROCESSPARAMETERSETID,SCHEDULEID,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
values(@BUSINESSPROCESSINSTANCEID,@BUSINESSPROCESSID,null,@SCHEDULEID,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID)
if not exists(select 1 from dbo.SYSTEMROLEPERM_BUSINESSPROCESS where SYSTEMROLEID = @SYSTEMROLEID and BUSINESSPROCESSCATALOGID = @BUSINESSPROCESSID)
insert into dbo.SYSTEMROLEPERM_BUSINESSPROCESS (SYSTEMROLEID,BUSINESSPROCESSCATALOGID,GRANTORDENY,DATEADDED, DATECHANGED,ADDEDBYID,CHANGEDBYID)
select ID, @BUSINESSPROCESSID,1,@CURRENTDATE,@CURRENTDATE,@CHANGEAGENTID,@CHANGEAGENTID
from dbo.SYSTEMROLE where ID = @SYSTEMROLEID
set @PRODUCTFLAGID = @FAFPRODUCTID;
exec dbo.USP_INSTALLEDPRODUCTS_PRODUCTIS
@PRODUCTCODE = @PRODUCTFLAGID,
@ISINSTALLED = @CORRECTPRODUCTINSTALLED OUTPUT
update dbo.SCHEDULE set ENABLED = @CORRECTPRODUCTINSTALLED where ID = @SCHEDULEID
end