CREATE OR ALTER PROCEDURE [Crm].[Pr_MessageCreateTemplatedEmails]
(
	@MessageTemplateID INT
)
-- EXEC [Crm].[Pr_MessageCreateTemplatedEmails] 1000
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @MessageSubject VARCHAR(MAX),@MessageTemplate VARCHAR(MAX),@RecipientSql VARCHAR(MAX),@DataSql VARCHAR(MAX)

	SELECT @RecipientSql = RecipientSqlScript,@DataSql = DatasetSqlScript,@MessageSubject = Subject,@MessageTemplate = Message
	FROM Crm.MessageTemplates
	WHERE MessageTemplateID = @MessageTemplateID

	DECLARE @MessageTemplateStart VARCHAR(MAX), @MessageTemplateData VARCHAR(MAX),@MessageTemplateEnd VARCHAR(MAX)

	SELECT @MessageTemplateStart = MessageTemplateBeforeRowTemplate,@MessageTemplateData = DetailsTemplate,@MessageTemplateEnd = MessageTemplateAfterRowTemplate
	FROM Crm.Fn_GetMessageTemplateParts(@MessageTemplate)

	DECLARE @SenderID BIGINT,@SenderEmail VARCHAR(50)

	SELECT @SenderID = CAST([System].Fn_GetSettingValue('SystemUserID') AS BIGINT),@SenderEmail = [System].Fn_GetSettingValue('SystemEmail')

	IF @RecipientSql IS NOT NULL AND @DataSql IS NOT NULL
	BEGIN
		INSERT INTO Crm.Messages( MessageTypeID, Sender, Recipients, Subject, Message, IsHtml, CreatedByID, CreatedDate, UpdatedByID, UpdatedDate )
		SELECT 1, @SenderEmail, Recipients, Subject, Message, 1, @SenderID, GETDATE(), @SenderID, GETDATE()
		FROM Crm.Fn_GetFormattedMessages(@RecipientSql,@DataSql,@MessageSubject,@MessageTemplateStart,@MessageTemplateData,@MessageTemplateEnd)
	END
END
GO


