CREATE FUNCTION dbo.UDF_EmailReplaceText(@Text NVARCHAR (4000), @UserID INT, @CourseID INT, @TheDate DATETIME)
/*************************************************************************************************

*************************************************************************************************/
returns NVARCHAR (4000)
AS
BEGIN
DECLARE	@ReturnText NVARCHAR (4000)
SELECT	@ReturnText = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IsNull(@Text, N'NULL Source'),
		N'[CR]', NCHAR(10)+NCHAR(13)) ,
		N'[Email]', U.Email) ,
		N'[UserID]', CAST(U.UserID AS NVARCHAR)) ,
		N'[CourseID]', CAST(CO.CourseID AS NVARCHAR)) ,
		N'[Time]', RIGHT(CONVERT(NVARCHAR, @TheDate, 100), 7)) ,
		N'[Date]', CONVERT(NVARCHAR, @TheDate, 107) ) ,
		N'[Clientnamefull]', CL.Clientnamefull) ,
		N'[Clientname]', CL.Clientname) ,
		N'[Organization]', IsNull(C.Centername + ' - ' + B.Branchname, '')) ,
		N'[Orgname]', IsNull(C.Centername + ' - ' + B.Branchname, N'')) ,
		N'[Branchname]', IsNull(B.Branchname, '')),
		N'[Centername]', C.Centername),
		N'[Fullname]', U.Fullname),
		N'[CRecordID]', CAST(CR.CRecordID AS NVARCHAR)),
		N'[Acknowledge]', CASE WHEN CR.CRAcknowledge = 1 THEN N'Acknowledged' ELSE N'Denied' END),
		N'[Coursename]', CO.Coursename)
	FROM	dbo.Users U
	LEFT OUTER JOIN Centers C ON C.CenterID = U.CenterID
	LEFT OUTER JOIN Branches B ON B.BranchID = U.BranchID
	INNER JOIN Clients CL ON CL.ClientID = U.ClientID
	LEFT OUTER JOIN Courses CO ON CO.CourseID = @CourseID
	LEFT OUTER JOIN (SELECT TOP 1 * FROM dbo.CourseRecords WHERE CourseID = @CourseID AND UserID = @UserID ORDER BY CRecordStart DESC) CR ON CR.UserID = U.UserID
	WHERE	U.UserID = @UserID
	
RETURN (CASE WHEN @ReturnText IS NULL THEN IsNull(@Text, N'Null @Text') ELSE @ReturnText END)
END
GO