go
CREATE OR ALTER PROCEDURE [dbo].[_as_Fix_PostGLCreatedBranch]
AS
BEGIN
SET NOCOUNT ON;
---------------------------------------------------------------------------------------
-- 1. Create the log table if it does not exist
---------------------------------------------------------------------------------------
--select * from _as_PostGL_FixLog
IF NOT EXISTS (
SELECT 1
FROM sys.tables t
WHERE t.name = '_as_PostGL_FixLog'
AND t.schema_id = SCHEMA_ID('dbo')
)
BEGIN
CREATE TABLE dbo._as_PostGL_FixLog
(
LogID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PostGL_AutoIdx BIGINT NOT NULL,
OldAuditNumber VARCHAR(255) NOT NULL,
NewAuditNumber VARCHAR(255) NOT NULL,
Old_iTxBranchId BIGINT NOT NULL,
New_iTxBranchId BIGINT NOT NULL,
Old_PostGL_iBranchID BIGINT NOT NULL,
New_PostGL_iBranchID BIGINT NOT NULL,
FixDate DATETIME NOT NULL CONSTRAINT DF__as_PostGLFixLog_FixDate DEFAULT GETDATE(),
Old_Reference VARCHAR(255) NULL,
New_Reference VARCHAR(255) NULL
);
END;
---------------------------------------------------------------------------------------
-- 2. Update PostGL with OUTPUT clause to log changes into _as_PostGL_FixLog
---------------------------------------------------------------------------------------
UPDATE p
SET cAuditNumber = CASE
WHEN (LEN(cAuditNumber) - LEN(REPLACE(cAuditNumber, '.', ''))) >= 2 THEN
CAST(PostGL_iCreatedBranchID AS VARCHAR(10))
+ '.'
+ SUBSTRING(
cAuditNumber,
CHARINDEX('.', cAuditNumber) + 1,
LEN(cAuditNumber)
)
ELSE
CAST(PostGL_iCreatedBranchID AS VARCHAR(10))
+ '.'
+ cAuditNumber
END,
iTxBranchID = PostGL_iCreatedBranchID,
PostGL_iBranchID = PostGL_iCreatedBranchID
OUTPUT
deleted.AutoIdx AS PostGL_AutoIdx,
deleted.cAuditNumber AS OldAuditNumber,
inserted.cAuditNumber AS NewAuditNumber,
deleted.iTxBranchID AS Old_iTxBranchId,
inserted.iTxBranchID AS New_iTxBranchId,
deleted.PostGL_iBranchID AS Old_PostGL_iBranchID,
inserted.PostGL_iBranchID AS New_PostGL_iBranchID,
deleted.Reference AS Old_Reference,
inserted.Reference AS New_Reference
INTO dbo._as_PostGL_FixLog
(PostGL_AutoIdx,
OldAuditNumber,
NewAuditNumber,
Old_iTxBranchId,
New_iTxBranchId,
Old_PostGL_iBranchID,
New_PostGL_iBranchID,
Old_Reference,
New_Reference)
--select *
FROM PostGL p
WHERE p.iTxBranchID <> p.PostGL_iCreatedBranchID
AND p.PostGL_iCreatedBranchID > 0
AND p.UserName = 'SDK'
AND p.TxDate >= DATEADD(MONTH,-2,GETDATE())
;
---------------------------------------------------------------------------------------
-- 3. Build the HTML email with changes from the last 2 minutes
---------------------------------------------------------------------------------------
DECLARE @EmailBody NVARCHAR(MAX) = N'';
DECLARE @IntroText NVARCHAR(MAX) = N'<p>Hello,<br/>Below is a summary of the PostGL records updated by _as_Fix_PostGLCreatedBranch procedure.</p>';
SET @EmailBody = N'<html><head><meta charset="UTF-8"></head><body>'
+ @IntroText
+ N'<table style="border-collapse:collapse; font-family:Arial, sans-serif;">'
+ N'<tr style="background-color:#003366; color:#D3D3D3;">'
+ N'<th style="border:1px solid #000; padding:5px;">AutoIdx</th>'
+ N'<th style="border:1px solid #000; padding:5px;">Reference</th>'
+ N'<th style="border:1px solid #000; padding:5px;">Old AuditNumber</th>'
+ N'<th style="border:1px solid #000; padding:5px;">New AuditNumber</th>'
+ N'<th style="border:1px solid #000; padding:5px;">iTxBranchID</th>'
+ N'</tr>';
/*Early out, if not changes.*/
IF NOT EXISTS (SELECT * FROM dbo._as_PostGL_FixLog WHERE FixDate >= DATEADD(MINUTE, -2, GETDATE())) RETURN -1;
WITH LogData AS
(
SELECT
LogID,
PostGL_AutoIdx,
OldAuditNumber,
NewAuditNumber,
Old_iTxBranchId,
New_iTxBranchId,
Old_Reference,
New_Reference,
ROW_NUMBER() OVER (ORDER BY LogID DESC) AS RowNum
FROM dbo._as_PostGL_FixLog
WHERE FixDate >= DATEADD(MINUTE, -2, GETDATE())
)
SELECT @EmailBody +=
N'<tr style="background-color:'
+ CASE WHEN (RowNum % 2) = 1 THEN '#FFFFFF' ELSE '#F0F0F0' END
+ N';">'
+ N'<td style="border:1px solid #000; padding:5px;">' + CAST(PostGL_AutoIdx AS NVARCHAR(20)) + N'</td>'
+ N'<td style="border:1px solid #000; padding:5px;">' + ISNULL(New_Reference,'') + N'</td>'
+ N'<td style="border:1px solid #000; padding:5px;">' + ISNULL(OldAuditNumber,'') + N'</td>'
+ N'<td style="border:1px solid #000; padding:5px;">' + ISNULL(NewAuditNumber,'') + N'</td>'
+ N'<td style="border:1px solid #000; padding:5px;">' + CAST(New_iTxBranchId AS NVARCHAR(20)) + N'</td>'
+ N'</tr>'
FROM LogData;
SET @EmailBody += N'</table></body></html>';
declare @subject nvarchar(255)
SET @subject = CONCAT(DB_Name(),' - PostGL Fix Updates - RUN GL RELINK!')
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AsamcoMailProfile',
@recipients = 'alex+ite@asamco.com;admin@swansalt.co.za;shaun@iteproducts.co.za;frik@iteproducts.co.za',
@subject = @subject,
@body = @EmailBody,
@body_format = 'HTML';
END;
GO
|