USP_FAFEVENT_GETEVENTEMAILTEMPLATE_ADDFROMXML

CREATE procedure dbo.USP_FAFEVENT_GETEVENTEMAILTEMPLATE_ADDFROMXML
(
@EVENTID uniqueidentifier,
@XML xml,
@USERID int,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as

set nocount on;
declare @ClientsID int,
        @CLIENTSITESID int

if @CHANGEAGENTID is null
    exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

if @CHANGEDATE is null
    set @CHANGEDATE = getdate()

  Select @ClientsID=CS.ClientsID, @CLIENTSITESID=CS.ID from dbo.EVENTEXTENSION EX 
  join dbo.ClientSites CS on EX.ClientSitesID = CS.ID
  where EX.EVENTID = @EVENTID

  select * into #FAFEmailTemplate
  from dbo.UFN_FAFEVENT_GETEVENTEMAILTEMPLATE_FROMITEMLISTXML(@XML)

  update #FAFEmailTemplate set Guid = NEWID() where (Guid is null or Guid = '00000000-0000-0000-0000-000000000000')

  declare @EVENTNAME as nvarchar(max)
  SELECT @EVENTNAME = name from dbo.EVENT where ID=@EVENTID

  insert into dbo.EmailTemplate
      (ClientsID, Name, Description, OwnerID, Deleted, Guid, ContentHTML,
      FromAddress, FromDisplayName, Subject, Priority, ReplyAddress,
      Type,ClientSitesID, CopySourceID, CopyBatchGUID, DataSourceID)
    select @ClientsID, LEFT(@EVENTNAME,100) + ': participant communication template - ' + CAST(newid() AS nvarchar(max)), Description, @USERID, Deleted, Guid, dbo.UFN_UPDATECOPYCONTENTHTML(ContentHTML, CopyBatchGuid),
      FromAddress,FromDisplayName, Subject,Priority,ReplyAddress,
      Type,@CLIENTSITESID,CopySourceID,CopyBatchGUID,DataSourceID
    from #FAFEmailTemplate

  insert into dbo.EMAILTEMPLATEEXTENSION
      (ID, EMAILTEMPLATEID, CCEMAILADDRESSES,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select ET.Guid, ET.ID, ETX.CCEMAILADDRESSES,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE
    from dbo.#FAFEmailTemplate ETX
    join dbo.EmailTemplate ET on ET.CopySourceID = ETX.CopySourceID and ET.ClientSitesID = @CLIENTSITESID
    where CCEMAILADDRESSES is not null and CCEMAILADDRESSES <> ''

  insert into dbo.NC_Notification
    (NotificationTypeID, DisplayName, Description, EmailTemplateID, Enabled)
  select 
    NOTIFICATIONTYPEID, NOTICATIONNAME, NOTICATIONDESCRIPTION, COPYSOURCEID, ENABLED 
  from dbo.#FAFEmailTemplate 
  where NCNOTIFICATIONID is not null

  insert into dbo.EVENTEMAILTEMPLATE    
          ( ID, EVENTID, EMAILTEMPLATEID, CONFIRMATIONTYPECODE, ISAVAILABLETOINDIVIDUAL, ISAVAILABLETOHOUSEHOLD, ISAVAILABLETOTEAMMEMBER, ISAVAILABLETOTEAMLEADER, 
          ISAVAILABLETOCOMPANYLEADER,ISAVAILABLETOFUNDRAISINGCOACH,ISAVAILABLETOSPONSOR, ISACTIVE,
          RECIPIENTDONOR, RECIPIENTPROSPECT, RECIPIENTINDIVIDUAL, RECIPIENTHOUSEHOLDMEMBER, RECIPIENTHOUSEHOLDLEADER, RECIPIENTTEAMMEMBER, 
          RECIPIENTTEAMLEADER, RECIPIENTCOMPANYLEADER, RECIPIENTSTATUSPREVIOUS, RECIPIENTSTATUSCURRENT, NCNOTIFICATIONID,
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISAVAILABLETOHOUSEHOLDMEMBER,FAFTASKID,ISDEFAULTFORTASK)    
    select ET.Guid, @EVENTID, ET.ID, ETYPECODE, FORINDIVIDUAL, FORHH, FORTEAMMEMBER, FORTEAMLEADER, 
          FORCOMPLEADER,FORFUNDCOACH,FORSPONSOR, ISACTIVE,
          RECIPIENTDONOR, RECIPIENTPROSPECT, RECIPIENTINDIVIDUAL, RECIPIENTHHMEMBER, RECIPIENTHHLEADER, RECIPIENTTEAMMEMBER, 
          RECIPIENTTEAMLEADER, RECIPIENTCOMPLEADER, PREVIOUSSTATUS, CURRENTSTATUS, NCN.ID, 
          @CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE,FORHHMEMBER,FAFTASKID,ISDEFAULTFORTASK
    from dbo.#FAFEmailTemplate EET
    join dbo.EmailTemplate ET on ET.CopySourceID = EET.CopySourceID and ET.ClientSitesID = @CLIENTSITESID
    left join dbo.NC_Notification NCN on NCN.EmailTemplateID = EET.COPYSOURCEID and NCN.NotificationTypeID = 12

    /*set EmailTemplateID null here since it was a holding place*/
    update NC_Notification
    set EmailTemplateID = null
    where NotificationTypeID = 12 and EmailTemplateID in (select COPYSOURCEID from #FAFEmailTemplate where NCNOTIFICATIONID is not null)

  drop table #FAFEmailTemplate

 if @@Error <> 0
    return 2;

return 0;