USP_DATAFORMTEMPLATE_EDIT_EVENT_INFO
Used for editing Event Information
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @NAME | nvarchar(100) | IN | |
| @DESCRIPTION | nvarchar(255) | IN | |
| @STARTDATE | datetime | IN | |
| @STARTTIME | UDT_HOURMINUTE | IN | |
| @ENDDATE | datetime | IN | |
| @ENDTIME | UDT_HOURMINUTE | IN | |
| @EVENTLOCATIONID | uniqueidentifier | IN | |
| @EVENTLOCATIONCONTACTID | uniqueidentifier | IN | |
| @MAINEVENTID | uniqueidentifier | IN | |
| @EVENTCATEGORYCODEID | uniqueidentifier | IN | |
| @SITES | xml | IN | |
| @EVENTYEAR | UDT_YEAR | IN | |
| @LOCATIONDESCRIPTION | nvarchar(1000) | IN | |
| @LOOKUPEVENTID | nvarchar(100) | IN | |
| @SUPPORTPHONE | nvarchar(100) | IN | |
| @SUPPORTURL | UDT_WEBADDRESS | IN | |
| @PREVIOUSYEAREVENTID | uniqueidentifier | IN | |
| @EVENTTYPECODE | tinyint | IN | |
| @EVENTSUPPORTEMAIL | UDT_EMAILADDRESS | IN | |
| @FAFPROGRAMID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EVENT_INFO
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier= null,
@CURRENTAPPUSERID uniqueidentifier,
--EVENT FIELDS
@NAME nvarchar(100)= '',
@DESCRIPTION nvarchar(255)= '',
@STARTDATE datetime,
@STARTTIME UDT_HOURMINUTE= '',
@ENDDATE datetime= null,
@ENDTIME UDT_HOURMINUTE= '',
@EVENTLOCATIONID uniqueidentifier= null,
@EVENTLOCATIONCONTACTID uniqueidentifier= null,
@MAINEVENTID uniqueidentifier,
@EVENTCATEGORYCODEID uniqueidentifier,
@SITES xml,
--EVENTEXTENSION FIELDS
@EVENTYEAR UDT_YEAR= '',
@LOCATIONDESCRIPTION nvarchar(1000)= '',
@LOOKUPEVENTID nvarchar(100)= '',
@SUPPORTPHONE nvarchar(100)= '',
@SUPPORTURL UDT_WEBADDRESS,
@PREVIOUSYEAREVENTID uniqueidentifier= null,
@EVENTTYPECODE tinyint = 1,
@EVENTSUPPORTEMAIL UDT_EMAILADDRESS = '',
@FAFPROGRAMID uniqueidentifier=null
)
as
set nocount on
--EVENT FIELDS
declare @r int
declare --@APPEALID uniqueidentifier,
@CLIENTSITESID int,
@CurrentUserID int,
@ClientsID int,
@Guid uniqueidentifier,
@SiteTrackingScript nvarchar(max),
@ParentSiteID int,
@EventName_Old nvarchar(100)
--,@DESIGNATIONLEVELID uniqueidentifier
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
select @EventName_Old=EVENT.NAME from dbo.EVENT where EVENT.ID = @ID
--select @APPEALID = EVENT.APPEALID,
-- @EventName_Old=EVENT.NAME,
-- @DESIGNATIONLEVELID = DESIGNATION.DESIGNATIONLEVEL1ID
--from dbo.EVENT
--left join dbo.APPEALDESIGNATION on EVENT.APPEALID = APPEALDESIGNATION.APPEALID
--left join dbo.DESIGNATION on APPEALDESIGNATION.DESIGNATIONID = DESIGNATION.ID
--where EVENT.ID = @ID
select @CLIENTSITESID = C.ID, @ClientsID = ClientsID, @Guid = Guid, @CurrentUserID = OwnerID, @SiteTrackingScript = SiteTrackingScript, @ParentSiteID = ParentSiteID
from dbo.EVENTEXTENSION EE
join dbo.CLIENTSITES C on EE.CLIENTSITESID = C.ID
where EE.EVENTID = @ID
exec @r= dbo.USP_DATAFORMTEMPLATE_EDIT_EVENT_3 @ID=@ID,
@CURRENTAPPUSERID= @CURRENTAPPUSERID,
@CHANGEAGENTID= @CHANGEAGENTID ,
@NAME= @NAME,
@DESCRIPTION= @DESCRIPTION,
@STARTDATE= @STARTDATE,
@STARTTIME= @STARTTIME,
@ENDDATE= @ENDDATE,
@ENDTIME= @ENDTIME,
@CAPACITY= 0,
@EVENTLOCATIONID= @EVENTLOCATIONID,
@EVENTLOCATIONCONTACTID= @EVENTLOCATIONCONTACTID,
@MAINEVENTID= @MAINEVENTID,
@SITES= @SITES,
@EVENTCATEGORYCODEID= @EVENTCATEGORYCODEID,
@QUICKCOMPAREEVENTID= null
if @@error<>0
begin
if @r <> 0 return @r
return 1
end
------------------------------------------------------------------------
--EVENTEXTENSION FIELDS
begin try
-- handle updating the data
update dbo.EVENTEXTENSION set
EVENTYEAR= @EVENTYEAR,
SUPPORTPHONE= @SUPPORTPHONE,
SUPPORTURL= @SUPPORTURL,
LOOKUPEVENTID= @LOOKUPEVENTID,
PRIORYEAREVENTID= @PREVIOUSYEAREVENTID,
EVENTTYPECODE = @EVENTTYPECODE,
EVENTSUPPORTEMAIL = @EVENTSUPPORTEMAIL,
FAFPROGRAMID = @FAFPROGRAMID,
CHANGEDBYID= @CHANGEAGENTID,
DATECHANGED= getdate()
from dbo.EVENTEXTENSION
where EVENTID= @ID
exec spAddUpdate_ClientSites @PKID = @CLIENTSITESID,
@CurrentUsersID = @CurrentUserID,
@Name = @NAME,
@Deleted = 0,
@ClientsID = @ClientsID,
@Guid = @Guid,
@SiteTrackingScript = @SiteTrackingScript,
@ParentSiteID = @ParentSiteID
--exec dbo.USP_FAFEVENTDEFAULTDESIGNATION_ADD_EDIT @DESIGNATIONLEVELID=@DESIGNATIONLEVELID, @EVENTID=@ID, @APPEALID=@APPEALID, @NAME=@NAME, @DESCRIPTION=@NAME, @LOOKUPID=@LOOKUPEVENTID, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @CHANGEAGENTID=@CHANGEAGENTID
update dbo.IDSETREGISTER
set NAME = REPLACE(NAME, @EventName_Old, @NAME)
where ID in (select ADHOCQUERYID from dbo.FAFEVENTADHOCQUERY where EVENTID = @ID)
update dbo.ADHOCQUERY
set NAME = REPLACE(NAME, @EventName_Old, @NAME)
where ID in (select ADHOCQUERYID from dbo.FAFEVENTADHOCQUERY where EVENTID = @ID)
--Check the see if the goal is set. If it is, then look at the previous years' event to fetch the retention values
DECLARE @GOALISSET bit
SELECT @GOALISSET= GOALISSET FROM EVENTFAFCONFIG (NOLOCK) WHERE EVENTID= @ID
--If goalisset = 1 and previous year is null, retention fields should be zero
IF (@GOALISSET= 1 AND @PREVIOUSYEAREVENTID IS NULL)
BEGIN
UPDATE EVENTGOAL
SET
REVENUERETENTION= 0,
REVENUESPONSORRETENTION= 0,
REVENUECOMPANYRETENTION= 0,
REVENUETEAMRETENTION= 0,
REVENUEINDIVIDUALRETENTION= 0,
RECRUITMENTFUNDRAISINGCOACHRETENTION= 0,
RECRUITMENTFITNESSCOACHRETENTION= 0,
RECRUITMENTSPONSORRETENTION= 0,
RECRUITMENTCOMPANYRETENTION= 0,
RECRUITMENTDONORRETENTION= 0,
RECRUITMENTTEAMRETENTION= 0,
RECRUITMENTINDIVIDUALRETENTION= 0,
REVENUEHOUSEHOLDRETENTION = 0,
RECRUITMENTHOUSEHOLDRETENTION = 0,
REVENUEREGISTRATIONRETENTION = 0
FROM dbo.EVENTGOAL EG (NOLOCK)
JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID= E.ID
WHERE E.ID= @ID
END
--If goalisset = 0 and previous year is null, clear out retentions/goal values of current event
IF (@GOALISSET= 0 AND @PREVIOUSYEAREVENTID IS NULL)
BEGIN
UPDATE EVENTGOAL
SET
REVENUETOTAL = 0,
REVENUEMONTHLY = 0,
REVENUEWEEKLY = 0,
REVENUEDAILY = 0,
REVENUESPONSOR = 0,
REVENUECOMPANY = 0,
REVENUETEAM = 0,
REVENUEINDIVIDUAL = 0,
RECRUITMENTSPONSOR = 0,
RECRUITMENTCOMPANY = 0,
RECRUITMENTTEAM = 0,
RECRUITMENTINDIVIDUAL = 0,
COMMUNICATIONTOTAL = 0,
COMMUNICATIONMONTHLY = 0,
COMMUNICATIONWEEKLY = 0,
COMMUNICATIONDAILY = 0,
COMMUNICATIONSPONSOR = 0,
COMMUNICATIONCOMPANY = 0,
COMMUNICATIONTEAM = 0,
COMMUNICATIONINDIVIDUAL = 0,
RECRUITMENTFUNDRAISINGCOACHES = 0,
RECRUITMENTFITNESSCOACHES = 0,
RECRUITMENTDONOR= 0,
REVENUERETENTION= 0,
REVENUESPONSORRETENTION= 0,
REVENUECOMPANYRETENTION= 0,
REVENUETEAMRETENTION= 0,
REVENUEINDIVIDUALRETENTION= 0,
RECRUITMENTFUNDRAISINGCOACHRETENTION= 0,
RECRUITMENTFITNESSCOACHRETENTION= 0,
RECRUITMENTSPONSORRETENTION= 0,
RECRUITMENTCOMPANYRETENTION= 0,
RECRUITMENTDONORRETENTION= 0,
RECRUITMENTTEAMRETENTION= 0,
RECRUITMENTINDIVIDUALRETENTION= 0,
REVENUEHOUSEHOLD = 0,
RECRUITMENTHOUSEHOLD = 0,
COMMUNICATIONHOUSEHOLD = 0,
REVENUEHOUSEHOLDRETENTION = 0,
RECRUITMENTHOUSEHOLDRETENTION = 0,
REVENUEREGISTRATION = 0,
REVENUEREGISTRATIONRETENTION = 0
FROM dbo.EVENTGOAL EG (NOLOCK)
JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID= E.ID
WHERE E.ID= @ID
END
--If goalisset = 0 and previous year is not null, copy all retention fields and goals field of previous year to current year.
IF (@GOALISSET= 0 AND @PREVIOUSYEAREVENTID IS NOT NULL)
BEGIN
DECLARE @REVENUETOTAL money, @REVENUEMONTHLY money, @REVENUEWEEKLY money, @REVENUEDAILY money, @REVENUESPONSOR money, @REVENUECOMPANY money,
@REVENUETEAM money, @REVENUEINDIVIDUAL money, @RECRUITMENTSPONSOR int, @RECRUITMENTCOMPANY int, @RECRUITMENTTEAM int, @RECRUITMENTINDIVIDUAL int,
@COMMUNICATIONTOTAL int, @COMMUNICATIONMONTHLY int, @COMMUNICATIONWEEKLY int, @COMMUNICATIONDAILY int, @COMMUNICATIONSPONSOR int,
@COMMUNICATIONCOMPANY int, @COMMUNICATIONTEAM int, @COMMUNICATIONINDIVIDUAL int, @RECRUITMENTFUNDRAISINGCOACHES int,
@RECRUITMENTFITNESSCOACHES int, @RECRUITMENTDONOR int, @REVENUERETENTION decimal(6,3), @REVENUESPONSORRETENTION decimal(6,3),
@REVENUECOMPANYRETENTION decimal(6,3), @REVENUETEAMRETENTION decimal(6,3), @REVENUEINDIVIDUALRETENTION decimal(6,3),
@RECRUITMENTFUNDRAISINGCOACHRETENTION decimal(6,3), @RECRUITMENTFITNESSCOACHRETENTION decimal(6,3), @RECRUITMENTSPONSORRETENTION decimal(6,3),
@RECRUITMENTCOMPANYRETENTION decimal(6,3), @RECRUITMENTDONORRETENTION decimal(6,3), @RECRUITMENTTEAMRETENTION decimal(6,3), @RECRUITMENTINDIVIDUALRETENTION decimal(6,3),
@REVENUEHOUSEHOLD money,@RECRUITMENTHOUSEHOLD int,@COMMUNICATIONHOUSEHOLD int,@REVENUEHOUSEHOLDRETENTION decimal(6,3), @RECRUITMENTHOUSEHOLDRETENTION decimal(6,3),
@REVENUEREGISTRATION money, @REVENUEREGISTRATIONRETENTION decimal(6,3)
SELECT @REVENUETOTAL= REVENUETOTAL, @REVENUEMONTHLY= REVENUEMONTHLY, @REVENUEWEEKLY= REVENUEWEEKLY, @REVENUEDAILY= REVENUEDAILY,
@REVENUESPONSOR= REVENUESPONSOR, @REVENUECOMPANY= REVENUECOMPANY, @REVENUETEAM= REVENUETEAM, @REVENUEINDIVIDUAL= REVENUEINDIVIDUAL,
@RECRUITMENTSPONSOR= RECRUITMENTSPONSOR, @RECRUITMENTCOMPANY= RECRUITMENTCOMPANY, @RECRUITMENTTEAM= RECRUITMENTTEAM, @RECRUITMENTINDIVIDUAL= RECRUITMENTINDIVIDUAL,
@COMMUNICATIONTOTAL= COMMUNICATIONTOTAL, @COMMUNICATIONMONTHLY= COMMUNICATIONMONTHLY, @COMMUNICATIONWEEKLY= COMMUNICATIONWEEKLY,
@COMMUNICATIONDAILY= COMMUNICATIONDAILY, @COMMUNICATIONSPONSOR= COMMUNICATIONSPONSOR, @COMMUNICATIONCOMPANY= COMMUNICATIONCOMPANY,
@COMMUNICATIONTEAM= COMMUNICATIONTEAM,@COMMUNICATIONINDIVIDUAL= COMMUNICATIONINDIVIDUAL, @RECRUITMENTFUNDRAISINGCOACHES= RECRUITMENTFUNDRAISINGCOACHES,
@RECRUITMENTFITNESSCOACHES= RECRUITMENTFITNESSCOACHES, @RECRUITMENTDONOR= RECRUITMENTDONOR, @REVENUERETENTION= REVENUERETENTION,
@REVENUESPONSORRETENTION= REVENUESPONSORRETENTION,@REVENUECOMPANYRETENTION= REVENUECOMPANYRETENTION, @REVENUETEAMRETENTION= REVENUETEAMRETENTION,
@REVENUEINDIVIDUALRETENTION= REVENUEINDIVIDUALRETENTION, @RECRUITMENTFUNDRAISINGCOACHRETENTION= RECRUITMENTFUNDRAISINGCOACHRETENTION,
@RECRUITMENTFITNESSCOACHRETENTION= RECRUITMENTFITNESSCOACHRETENTION,@RECRUITMENTSPONSORRETENTION= RECRUITMENTSPONSORRETENTION, @RECRUITMENTCOMPANYRETENTION= RECRUITMENTCOMPANYRETENTION,
@RECRUITMENTDONORRETENTION= RECRUITMENTDONORRETENTION,@RECRUITMENTTEAMRETENTION= RECRUITMENTTEAMRETENTION, @RECRUITMENTINDIVIDUALRETENTION= RECRUITMENTINDIVIDUALRETENTION,
@REVENUEHOUSEHOLD =RECRUITMENTDONORRETENTION,@RECRUITMENTHOUSEHOLD =RECRUITMENTHOUSEHOLD,@COMMUNICATIONHOUSEHOLD =COMMUNICATIONHOUSEHOLD,@REVENUEHOUSEHOLDRETENTION =REVENUEHOUSEHOLDRETENTION,
@RECRUITMENTHOUSEHOLDRETENTION = RECRUITMENTHOUSEHOLDRETENTION, @REVENUEREGISTRATION = REVENUEREGISTRATION, @REVENUEREGISTRATIONRETENTION = REVENUEREGISTRATIONRETENTION
FROM dbo.EVENTGOAL EG (NOLOCK)
JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID= E.ID
WHERE E.ID= @PREVIOUSYEAREVENTID
UPDATE EVENTGOAL
SET
REVENUETOTAL = @REVENUETOTAL,
REVENUEMONTHLY = @REVENUEMONTHLY,
REVENUEWEEKLY = @REVENUEWEEKLY,
REVENUEDAILY = @REVENUEDAILY,
REVENUESPONSOR = @REVENUESPONSOR,
REVENUECOMPANY = @REVENUECOMPANY,
REVENUETEAM = @REVENUETEAM,
REVENUEINDIVIDUAL = @REVENUEINDIVIDUAL,
RECRUITMENTSPONSOR = @RECRUITMENTSPONSOR,
RECRUITMENTCOMPANY = @RECRUITMENTCOMPANY,
RECRUITMENTTEAM = @RECRUITMENTTEAM,
RECRUITMENTINDIVIDUAL = @RECRUITMENTINDIVIDUAL,
COMMUNICATIONTOTAL = @COMMUNICATIONTOTAL,
COMMUNICATIONMONTHLY = @COMMUNICATIONMONTHLY,
COMMUNICATIONWEEKLY = @COMMUNICATIONWEEKLY,
COMMUNICATIONDAILY = @COMMUNICATIONDAILY,
COMMUNICATIONSPONSOR = @COMMUNICATIONSPONSOR,
COMMUNICATIONCOMPANY = @COMMUNICATIONCOMPANY,
COMMUNICATIONTEAM = @COMMUNICATIONTEAM,
COMMUNICATIONINDIVIDUAL = @COMMUNICATIONINDIVIDUAL,
RECRUITMENTFUNDRAISINGCOACHES = @RECRUITMENTFUNDRAISINGCOACHES,
RECRUITMENTFITNESSCOACHES = @RECRUITMENTFITNESSCOACHES,
RECRUITMENTDONOR= @RECRUITMENTDONOR,
REVENUERETENTION= @REVENUERETENTION,
REVENUESPONSORRETENTION= @REVENUESPONSORRETENTION,
REVENUECOMPANYRETENTION= @REVENUECOMPANYRETENTION,
REVENUETEAMRETENTION= @REVENUETEAMRETENTION,
REVENUEINDIVIDUALRETENTION= @REVENUEINDIVIDUALRETENTION,
RECRUITMENTFUNDRAISINGCOACHRETENTION= @RECRUITMENTFUNDRAISINGCOACHRETENTION,
RECRUITMENTFITNESSCOACHRETENTION= @RECRUITMENTFITNESSCOACHRETENTION,
RECRUITMENTSPONSORRETENTION= @RECRUITMENTSPONSORRETENTION,
RECRUITMENTCOMPANYRETENTION= @RECRUITMENTCOMPANYRETENTION,
RECRUITMENTDONORRETENTION= @RECRUITMENTDONORRETENTION,
RECRUITMENTTEAMRETENTION= @RECRUITMENTTEAMRETENTION,
RECRUITMENTINDIVIDUALRETENTION= @RECRUITMENTINDIVIDUALRETENTION,
REVENUEHOUSEHOLD =@REVENUEHOUSEHOLD,
RECRUITMENTHOUSEHOLD =@RECRUITMENTHOUSEHOLD,
COMMUNICATIONHOUSEHOLD =@COMMUNICATIONHOUSEHOLD,
REVENUEHOUSEHOLDRETENTION =@REVENUEHOUSEHOLDRETENTION,
RECRUITMENTHOUSEHOLDRETENTION = @RECRUITMENTHOUSEHOLDRETENTION,
REVENUEREGISTRATION = @REVENUEREGISTRATION,
REVENUEREGISTRATIONRETENTION = @REVENUEREGISTRATIONRETENTION
FROM dbo.EVENTGOAL EG (NOLOCK)
JOIN dbo.EVENT E (NOLOCK)ON EG.EVENTID= E.ID
WHERE E.ID= @ID
END
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;
------------------------------------------------------------------------