GO
CREATE TABLE [sAlert].[tEventTemplateTemp](
	[OrgId] [smallint] NOT NULL,
	[EventId] [smallint] NOT NULL,
	[Template] [nvarchar](max) NULL,
	[AlertModeId] [tinyint] NOT NULL,
	[IsAutoSend] [bit] NULL,
	[MailSubject] [nvarchar](100) NULL,
	[DefaultAlertTo] [nvarchar](500) NULL,
	[SMSTemplateID] [nvarchar](100) NULL,
	[EMailCC] [nvarchar](500) NULL,
	[EMailBCC] [nvarchar](500) NULL,
	[WhatsAppTemplate] [nvarchar](100) NULL,
	[WhatsAppTemplateLanguage] [nvarchar](10) NULL,
	[DefaultAlertToEUId] [nvarchar](max) NULL
)
GO

INSERT INTO [sAlert].[tEventTemplateTemp]
SELECT * FROM [sAlert].[tEventTemplate]

GO
DROP Table #tmptbl
GO
CREATE Table #tmptbl
(
	Id int IDENTITY(1,1),
	OrgId int,
	EventId int,
	Title nvarchar(200),
	Field nvarchar(1000),
	Template nvarchar (max),
	AlertModeId smallint
)

GO

Insert Into #tmptbl
select ET.OrgId, ET.EventId, EM.TItle, EM.Fields, ET.Template, ET.AlertModeID 
from sAlert.tEventTemplate ET
INNER JOIN sAlert.tEventMaster EM ON EM.Id = ET.EventId
Where (EM.ModuleId In (153,214) OR EM.Id IN (152) OR (ET.EventId IN (106,151,163) AND EM.Fields LIKE '%[AbsentDates]%'))
AND ET.IsAutoSend = 1 
AND ET.EventId NOT IN (133,151) 
AND EM.Fields LIKE '%Date%'
order by ET.EventId

declare @lCount int, @lEventId int, @lField Nvarchar(1000), @lTemplate nvarchar(max),@lAlertModeId int,@lOrgId  int

select @lCount = Count(Id) from #tmptbl

while @lCount  > 0 
BEGIN
	Select @lField = Field, @lTemplate = Template, @lEventId = EventId, @lAlertModeId = AlertModeId, @lOrgId = OrgId
	from #tmptbl where Id = @lCount 

	Select @lField,@lTemplate,@lEventId

	Update ET
	SET ET.Template  = ET.Template + '[PresentDates]'
	FROM sAlert.tEventTemplate ET
	INNER JOIN #tmptbl T ON T.EventId = ET.EventId AND T.AlertModeId = ET.AlertModeId AND T.OrgId = ET.OrgId
	where ET.EventId = @lEventId AND ET.AlertModeId =@lAlertModeId  AND ET.OrgId = @lOrgId
	AND T.Id = @lCount AND (CHARINDEX('[PresentDates]', T.Field) > 0 AND CHARINDEX('[PresentDates]', T.Template) = 0) 

	Update ET
	SET ET.Template  = ET.Template + '[PresentDate]'
	FROM sAlert.tEventTemplate ET
	INNER JOIN #tmptbl T ON T.EventId = ET.EventId AND T.AlertModeId = ET.AlertModeId AND T.OrgId = ET.OrgId
	where ET.EventId = @lEventId AND ET.AlertModeId =@lAlertModeId  AND ET.OrgId = @lOrgId
	AND T.Id = @lCount AND (CHARINDEX('[PresentDate]', T.Field) > 0 AND CHARINDEX('[PresentDate]', T.Template) = 0) 

	Update ET
	SET ET.Template  = ET.Template + '[AbsentDate]'
	FROM sAlert.tEventTemplate ET
	INNER JOIN #tmptbl T ON T.EventId = ET.EventId AND T.AlertModeId = ET.AlertModeId AND T.OrgId = ET.OrgId
	where ET.EventId = @lEventId AND ET.AlertModeId =@lAlertModeId  AND ET.OrgId = @lOrgId
	AND T.Id = @lCount AND (CHARINDEX('[AbsentDate]', T.Field) > 0 AND CHARINDEX('[AbsentDate]', T.Template) = 0) 

	Update ET
	SET ET.Template  = ET.Template + '[AbsentDates]'
	FROM sAlert.tEventTemplate ET
	INNER JOIN #tmptbl T ON T.EventId = ET.EventId AND T.AlertModeId = ET.AlertModeId AND T.OrgId = ET.OrgId
	where ET.EventId = @lEventId AND ET.AlertModeId =@lAlertModeId  AND ET.OrgId = @lOrgId
	AND T.Id = @lCount AND (CHARINDEX('[AbsentDates]', T.Field) > 0 AND CHARINDEX('[AbsentDates]', T.Template) = 0) 

	Update ET
	SET ET.Template  = ET.Template + '[LateDate]'
	FROM sAlert.tEventTemplate ET
	INNER JOIN #tmptbl T ON T.EventId = ET.EventId AND T.AlertModeId = ET.AlertModeId AND T.OrgId = ET.OrgId
	where ET.EventId = @lEventId AND ET.AlertModeId =@lAlertModeId  AND ET.OrgId = @lOrgId
	AND T.Id = @lCount AND (CHARINDEX('[LateDate]', T.Field) > 0 AND CHARINDEX('[LateDate]', T.Template) = 0) 

	SET @lCount = @lCount - 1

END

DROP Table #tmptbl