USP_GLOBALCHANGE_DELETECOMMUNICATIONTEMPLATE

create procedure dbo.USP_GLOBALCHANGE_DELETECOMMUNICATIONTEMPLATE
(
    @SELECTEDPARTICIPANTCOMMUNICATIONTEMPLATES xml = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as            
    set nocount off;

    declare @ONEPERRECORD bit

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

    declare @Count int, @Index int
    declare @EmailTemplateID int = 0,
            @EmailTemplateGuID uniqueidentifier,
            @EventEmailTemplateID uniqueidentifier

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

    begin try
        -- do the global change operation here, and set @NUMBERADDED, @NUMBEREDITED, and/or @NUMBERDELETED as appropriate

        IF @SELECTEDPARTICIPANTCOMMUNICATIONTEMPLATES IS NOT NULL
        BEGIN

                  DECLARE @RemoveTable table(
                      ID int identity(1,1), 
                      EmailTemplateGUID uniqueidentifier
                )

            INSERT INTO @RemoveTable(EmailTemplateGUID)
            SELECT EMAILTEMPLATEGUID from (SELECT T.c.value('(RECORDID)[1]','uniqueidentifier') AS 'EMAILTEMPLATEGUID'
            FROM @SELECTEDPARTICIPANTCOMMUNICATIONTEMPLATES.nodes('/SELECTEDPARTICIPANTCOMMUNICATIONTEMPLATES/ITEM') T(c)) AS XMLTABLE
      INNER JOIN EMAILTEMPLATE ET ON XMLTABLE.EMAILTEMPLATEGUID = ET.GUID
      INNER JOIN EVENTEMAILTEMPLATE ETT on ET.ID = ETT.EMAILTEMPLATEID
      WHERE exists(
          select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(ETT.EVENTID) EVENTSITE
          where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
            or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'800093A6-B727-490B-8CC4-C0C0CF2148F0',20) 
            where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)) )
          )      



            SET @NUMBERDELETED = 0
            SELECT @Count = COUNT(*),@Index = 1 FROM @RemoveTable

            WHILE @Index <= @Count
            BEGIN
                SELECT @EmailTemplateId = b.ID 
                FROM @RemoveTable a
                inner join Emailtemplate b on b.Guid = a.EmailTemplateGUID 
                WHERE a.ID = @Index

                SET @EventEmailTemplateID = (SELECT top 1 A.ID from EVENTEMAILTEMPLATE a JOIN EMAILTEMPLATE B ON a.EMAILTEMPLATEID = B.ID and b.ID = @EmailTemplateId and b.Deleted = 0)
                IF @EmailTemplateID IS NOT NULL and @EventEmailTemplateID is not null
                BEGIN
                    -- use the system generated delete routine to allow proper recording of the deleting agent

                    exec USP_EVENTEMAILTEMPLATE_DELETEBYID_WITHCHANGEAGENTID @EventEmailTemplateId, @CHANGEAGENTID

                    exec spDelete_EmailTemplate @PKID = @EmailTemplateID, @CurrentUsersID=0
                    SET @NUMBERDELETED = @NUMBERDELETED + 1
                END
                SET @Index = @Index + 1
            END
            -- SELECT @NUMBERDELETED = COUNT(*) from @RemoveTable


            /*
              declare @EMAILTEMPLATEID int

            select @EMAILTEMPLATEID = EMAILTEMPLATEID from EVENTEMAILTEMPLATE where ID = @ID

            -- use the system generated delete routine to allow proper recording of the deleting agent
            exec USP_EVENTEMAILTEMPLATE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

            exec spDelete_EmailTemplate @PKID = @EMAILTEMPLATEID, @CurrentUsersID=0
            */
        END
    end try

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