USE [EXP_V4_ASP]
GO
/** Object:  Trigger [dbo].[Loyalty_Transactions]    Script Date: 12/26/2024 12:30:38 PM **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Loyalty_Transactions]
ON [dbo].[points_stmt]
AFTER INSERT, UPDATE
AS
BEGIN
    BEGIN TRY
        DECLARE
            @LoyaltyID INT,
            @URL NVARCHAR(MAX) = 'http://172.105.61.92:3005/api/create-loyality_transaction',
            @Object INT,
            @Body NVARCHAR(MAX),
            @Points FLOAT,
            @CardNo NVARCHAR(MAX),
            @expiryDate DATE,
            @tranDate DATE,
            @entryNo BIGINT,
            @voucherAmt FLOAT,
            @recno BIGINT;

        -- Use a CURSOR to iterate through the inserted rows
        DECLARE cur CURSOR FOR
        SELECT cust_code, points, card_no, PointsExpire_date, tran_date, entry_no, voucher_amt, recno FROM inserted;

        OPEN cur;
        FETCH NEXT FROM cur INTO @LoyaltyID, @Points, @CardNo, @expiryDate, @tranDate, @entryNo, @voucherAmt, @recno;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Construct the JSON body safely
            SET @Body = N'{
                "loyality_id": "' + ISNULL(CAST(@LoyaltyID AS NVARCHAR), '') + '",
                "points": ' + ISNULL(CAST(@Points AS NVARCHAR), '0') + ',
                "card_no": "' + ISNULL(@CardNo, '') + '",
                "expiry_date": "' + ISNULL(CONVERT(NVARCHAR, @expiryDate, 23), '') + '",
                "tran_date": "' + ISNULL(CONVERT(NVARCHAR, @tranDate, 23), '') + '",
                "entry_no": ' + ISNULL(CAST(@entryNo AS NVARCHAR), '0') + ',
                "voucher_amount": ' + ISNULL(CAST(@voucherAmt AS NVARCHAR), '0') + ',
                "recno": ' + ISNULL(CAST(@recno AS NVARCHAR), '0') + '
            }';

            -- Make the HTTP request
            EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
            EXEC sp_OAMethod @Object, 'open', NULL, 'POST', @URL, 'false';
            EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type', 'application/json';
            EXEC sp_OAMethod @Object, 'send', NULL, @Body;
            EXEC sp_OADestroy @Object;

            FETCH NEXT FROM cur INTO @LoyaltyID, @Points, @CardNo, @expiryDate, @tranDate, @entryNo, @voucherAmt, @recno;
        END;

        CLOSE cur;
        DEALLOCATE cur;
    END TRY
    BEGIN CATCH
        -- Print error details
        PRINT ERROR_MESSAGE();
    END CATCH;
END