USP_DATAFORMTEMPLATE_ADD_FAFEVENT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_FAFEVENT
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,    
  @NAME nvarchar(100) = '',
  @DESCRIPTION nvarchar(255) = '',    
  @STARTDATE datetime,
  @STARTTIME dbo.UDT_HOURMINUTE = null,
  @ENDDATE datetime = null,
  @ENDTIME dbo.UDT_HOURMINUTE = null,                        
  @CAPACITY int = 0,
  @EVENTLOCATIONID uniqueidentifier = null,
  @EVENTLOCATIONCONTACTID uniqueidentifier = null,
  @COPYFROMEVENTID uniqueidentifier = null,  
  @ATTRIBUTEDEFINED bit = 0,
  @SITES xml = null,
  @EVENTCATEGORYCODEID uniqueidentifier = null,
  @APPEALID uniqueidentifier = null,
  @PREVIOUSYEAREVENTID uniqueidentifier = null,
  @EVENTYEAR UDT_YEAR = null,
  @LOOKUPID nvarchar(100) = '',
  @SUPPORTPHONE nvarchar(20) = '',
  @SUPPORTURL UDT_WEBADDRESS = '',
  @EVENTTYPECODE tinyint = 0,
  @EVENTSUPPORTEMAIL UDT_EMAILADDRESS = '',
  @CLIENTSITESID int = 0,
  @ISFIRSTFAF bit = 0,
  @BATCHNUMBER nvarchar(200) = '',
  @FAFPROGRAMID uniqueidentifier = null
)
as                    
set nocount on;

declare @CURRENTDATE datetime,
        @idOfTargetSite int,
        @idOfSiteToClone int,
        @NewGuid uniqueidentifier,
        @CopyBatchGUID uniqueidentifier,
        @NewName varchar(50),
        @UserID int,
        @CopyDepth int,
        @CopySecurity bit,
        @DESIGNATIONUSERID nvarchar(200),
        @ClientsID int, 
        @MAINEVENTID uniqueidentifier = null,
        @DEFAULTSITEID uniqueidentifier = null,
        @dSite nvarchar(200),
        @importbyuser uniqueidentifier = @CURRENTAPPUSERID;

if @ID is null    
    set @ID = newid();    

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

set @CURRENTDATE = getdate();

select @DEFAULTSITEID = SITEID from dbo.UFN_SITE_MYSITE_GET(@CURRENTAPPUSERID)
if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 
begin
  if @SITES is null
    raiserror('Site is required.',13,1)  
  else
    begin      
      select @DEFAULTSITEID = SITEID from dbo.UFN_SITE_MYSITE_GET(@CURRENTAPPUSERID)
      set @dSite = '<SITEID>'+ cast(@DEFAULTSITEID as nvarchar(40)) +'</SITEID>';
      if CAST(@SITES AS NVARCHAR(MAX)) NOT LIKE '%'+@dSite+'%'
        raiserror('You did not assign your default site to the event.  To continue, edit site security and add your default site.', 13,1)

    end
end

if not exists(select top 1 EA.EMAILADDRESS from CONSTITUENT C join EMAILADDRESS EA on C.ID = EA.CONSTITUENTID where C.ID = @EVENTLOCATIONCONTACTID)
  raiserror('ContactNoEmail',13,1)

if @ENDDATE is null
    set @ENDDATE = @STARTDATE;

/* TMV 02/28/2007 CR268233-022607*/
if @CAPACITY is null
    set @CAPACITY = 0;

if @ISFIRSTFAF = 1
  exec dbo.USP_FAFEVENT_INITIALIZE_PARAMETER

begin try

    exec dbo.USP_EVENTSITE_VALIDATESITES @SITES

  /* FAF considers appeal same as event, so we create appeal behind scene to conform with current event model (team fundraising)*/

  declare @APPEALDESCRIPTION nvarchar(255)
  set @APPEALID = newID()
  if @DESCRIPTION = '' 
    set @APPEALDESCRIPTION = @NAME
  else
    set @APPEALDESCRIPTION = @DESCRIPTION                
  declare @appealindex int = 1
  declare @APPEALNAME nvarchar(100)
  set @APPEALNAME = @NAME
  while exists(select ID from dbo.APPEAL where NAME = @APPEALNAME)
  begin
       set @APPEALNAME = @APPEALNAME + cast(@appealindex as nvarchar(3))
       set @appealindex = @appealindex + 1
  end

  exec dbo.USP_DATAFORMTEMPLATE_ADD_APPEAL @ID=@APPEALID output, @CHANGEAGENTID=@CHANGEAGENTID, @NAME=@APPEALNAME, @DESCRIPTION=@APPEALDESCRIPTION, 
            @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @SITEID=@DEFAULTSITEID, @CURRENTAPPUSERID=@CURRENTAPPUSERID;

    insert into dbo.EVENT
        (ID, NAME, DESCRIPTION, STARTDATE, STARTTIME, ENDDATE, ENDTIME, CAPACITY, EVENTLOCATIONID, EVENTLOCATIONCONTACTID, MAINEVENTID, EVENTCATEGORYCODEID, APPEALID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
        (@ID, @NAME, @DESCRIPTION, @STARTDATE, @STARTTIME, @ENDDATE, @ENDTIME, @CAPACITY, @EVENTLOCATIONID, @EVENTLOCATIONCONTACTID, @MAINEVENTID, @EVENTCATEGORYCODEID, @APPEALID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    exec dbo.USP_EVENT_GETSITES_ADDFROMXML @ID, @SITES, @CHANGEAGENTID, @CURRENTDATE 

  select @DESIGNATIONUSERID = LOOKUPID from dbo.EVENT where ID = @ID

  set @DESIGNATIONUSERID = 'FAF-DES-' + @DESIGNATIONUSERID

  exec dbo.USP_FAFEVENTDEFAULTDESIGNATION_ADD_EDIT @EVENTID=@ID, @APPEALID=@APPEALID, @NAME=@NAME, @DESCRIPTION=@APPEALDESCRIPTION, @LOOKUPID=@DESIGNATIONUSERID, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @SITEID=@DEFAULTSITEID, @CHANGEAGENTID=@CHANGEAGENTID

   /* Site/FAF event creation starts */   

  select @UserID = [ClientUsers].ID from BBNCUSERMAP 
  join dbo.[ClientUsers] on [ClientUsers].[UserName] = BBNCUSERMAP.BBNCUSERNAME
  where BBNCUSERMAP.ID = @CURRENTAPPUSERID

  if @UserID is null
    set @UserID = 1

  set @NewGuid = NEWID()
  set @CopyBatchGUID = NEWID()
  set @NewName = @NAME

  set @CopyDepth = 3  /* ALL */
  set @CopySecurity = 1 /* true */

  select @idOfTargetSite = Value, @idOfSiteToClone = Value from dbo.Setting where ID = 1 
  select @ClientsID = value from dbo.Setting where ID = 2

  if @COPYFROMEVENTID is not null 
    select @idOfTargetSite=CLIENTSITESID, @idOfSiteToClone=CLIENTSITESID from dbo.EVENTEXTENSION where EVENTID = @COPYFROMEVENTID

  exec dbo.spClone_ClientSites @PKID = @idOfTargetSite output, @CurrentUsersID=@UserID,@NewName=@NewName, @NewGuid = @NewGuid, @CopyBatchGUID=@CopyBatchGUID

  select @ClientsID=ClientsID from dbo.ClientSites where ID=@idOfTargetSite

  set @CLIENTSITESID = @idOfTargetSite 

  if @COPYFROMEVENTID is not null 
    begin
      exec dbo.USP_FAFEVENTCOPY_WRAPPER @SOURCEID=@COPYFROMEVENTID,@TARGETEVENTID=@ID,@COPYBATCHGUID=@CopyBatchGUID,@CHANGEAGENTID = @CHANGEAGENTID,@EVENTYEAR = @EVENTYEAR,
        @SUPPORTURL = @SUPPORTURL, @SUPPORTPHONE = @SUPPORTPHONE, @LOOKUPEVENTID = @LOOKUPID, @EVENTTYPECODE = @EVENTTYPECODE, 
            @PRIORYEAREVENTID = @PREVIOUSYEAREVENTID, @EVENTSUPPORTEMAIL = @EVENTSUPPORTEMAIL, @CLIENTSITESID=@idOfTargetSite,@USERID=@UserID, @SITEID=@DEFAULTSITEID,
            @FAFPROGRAMID = @FAFPROGRAMID;            

       exec dbo.spCloneSite_DeepCopy @SourceClientSitesID=@idOfSiteToClone, @TargetClientSitesID=@idOfTargetSite, @CurrentUserID=@UserID, @CopyBatchGUID=@CopyBatchGUID, @CopyDepth=@CopyDepth,@CopySecurity=@CopySecurity

       IF NOT EXISTS(SELECT ET.ID FROM dbo.EmailTemplate ET (NOLOCK) INNER JOIN dbo.EVENTEMAILTEMPLATE EET (NOLOCK)
          ON ET.ID = EET.EMAILTEMPLATEID AND EET.CONFIRMATIONTYPECODE = 33 WHERE EET.EVENTID = @ID)
       EXEC USP_FAF_DEFAULT_MARKETING_TEMPLATE_ADD @EVENTID = @ID, @appuserID = @CURRENTAPPUSERID, @USERID = @USERID, @CLIENTSID = @CLIENTSID;

       EXEC USP_FAF_ADHOCQUERY_LIST_COPY @EVENTID = @ID, @OLDEVENTID = @COPYFROMEVENTID, @APPUSERID = @CURRENTAPPUSERID, @USERID = @USERID, @CLIENTSID = @CLIENTSID;

       EXEC USP_FAF_DEFAULT_MARKETING_MESSAGES_COPY @EVENTID = @ID, @OLDEVENTID = @COPYFROMEVENTID, @APPUSERID = @CURRENTAPPUSERID, @USERID = @USERID, @CLIENTSID = @CLIENTSID;


  end
  else
    begin
      exec dbo.USP_DATAFORMTEMPLATE_ADD_EVENT_EXTENSION @CHANGEAGENTID = @CHANGEAGENTID, @EVENTID =  @ID, @EVENTYEAR = @EVENTYEAR, 
          @SUPPORTURL = @SUPPORTURL, @SUPPORTPHONE = @SUPPORTPHONE, @LOOKUPEVENTID = @LOOKUPID, @EVENTTYPECODE = @EVENTTYPECODE, 
          @PRIORYEAREVENTID = @PREVIOUSYEAREVENTID, @EVENTSUPPORTEMAIL = @EVENTSUPPORTEMAIL, @CLIENTSITESID=@idOfTargetSite, @SITEID=@DEFAULTSITEID,
          @FAFPROGRAMID = @FAFPROGRAMID;  

      exec dbo.USP_FAFDEFAULTOPTIONS_CREATE @EVENTID=@ID, @CHANGEAGENTID = @CHANGEAGENTID
      exec dbo.USP_FAFDEFAULTACKNOWLEDGEMENTS @EVENTID=@ID, @CLIENTSITESID=@idOfTargetSite, @USERID=@UserID, @CLIENTSID=@ClientsID, @CHANGEAGENTID = @CHANGEAGENTID
      exec dbo.USP_FAFDEFAULTNOTIFICATIONS @EVENTID=@ID, @CLIENTSITESID=@idOfTargetSite, @USERID=@UserID, @CLIENTSID=@ClientsID, @CHANGEAGENTID = @CHANGEAGENTID
      exec dbo.USP_FAFDEFAULTCOMMUNICATIONTEMPLATES @EVENTID=@ID, @CLIENTSITESID=@idOfTargetSite, @USERID=@UserID, @CHANGEAGENTID = @CHANGEAGENTID
      exec dbo.USP_FAFDEFAULTONLINECONFIRMATIONS @EVENTID=@ID, @CLIENTSITESID=@idOfTargetSite, @USERID=@UserID, @CLIENTSID=@ClientsID, @CHANGEAGENTID = @CHANGEAGENTID

      -- Create Default Social Network transaction types of the new event
      exec dbo.USP_FAFDEFAULTSOCIALNETWORK @EVENTID=@ID, @CHANGEAGENTID = @CHANGEAGENTID

      -- Create Default Marketing Template

      exec dbo.USP_FAF_DEFAULT_MARKETING_TEMPLATE_ADD @EVENTID = @ID, @APPUSERID = @CURRENTAPPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @USERID = @USERID, @CLIENTSID = @CLIENTSID

      -- Create Default Marketing Messages

      exec dbo.USP_FAF_DEFAULT_MARKETING_MESSAGES @EVENTID = @ID, @APPUSERID = @CURRENTAPPUSERID, @CHANGEAGENTID = @CHANGEAGENTID, @USERID = @USERID, @CLIENTSID = @CLIENTSID

      if @BATCHNUMBER <> '' 
        set @importbyuser = null

      insert into dbo.EVENTIMPORT
        (ID, IMPORTATTEMPT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISIMPORTINGBYUSERID, BATCHNUMBER)
      values 
        (@ID, 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @importbyuser, @BATCHNUMBER);

    end  


  /* create fake URL */

  /*
  declare @protocol nvarchar(5), @domain nvarchar(250)

  select @protocol = item from dbo.UFN_STRING_PARSER('/', @url ) where id = 1
  select @domain= item from dbo.UFN_STRING_PARSER('/', @url ) where id = 2

  set @url = @protocol + '//' + @domain + '/' + replace(@NAME, ' ', '-') + '/'

  */    

  declare @url nvarchar(1024)
    select @url = value from dbo.CMSSITESETTING where ClientSitesID = 1 and ENUMID = 11

  declare @INTEGRATIONURL nvarchar(1024)          
  set @INTEGRATIONURL = dbo.UFN_BBNC_URL();

  declare @siteName nvarchar(200)
  set @siteName = replace(@NAME, ' ', '')
  set @siteName = dbo.UFN_FAFEVENT_VALIDEVENTURL(@siteName)

  if @INTEGRATIONURL = @url  
    set @url = @url + @siteName + '/'
  else
    set @url = @INTEGRATIONURL + @siteName + '/'

  declare @index int = 1
  while exists(select value from dbo.CMSSITESETTING where ENUMID = 11 and VALUE = @url)
  begin
      set @sitename = @siteName + cast(@index as nvarchar(3))
      set @index = @index + 1
      set @url = @integrationurl + @siteName + '/'
  end

  update dbo.CMSSITESETTING
  set VALUE = @url 
  where ENUMID = 11 and CLIENTSITESID = @idOfTargetSite

  update dbo.CMSSITESETTING
  set VALUE = @url + 'Page.aspx' 
  where ENUMID = 10 and CLIENTSITESID = @idOfTargetSite

  /*update dbo.ClientSites
  set ParentSiteID = @idOfSiteToClone
  where ID = @idOfTargetSite*/

  /* site creation end */ 

end try

begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;