Cacher is the code snippet organizer for pro developers

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      YJ
-- Create date: 2018/10/31
-- Description: 領好禮點數轉履保舊資料修改
-- =============================================
CREATE PROCEDURE sp_GiftPointToEscrow
    @giftActivitySerialno uniqueidentifier,
    @escrowBegin DateTime,
	@escrowEnd DateTime,
	@accountJsonArray nvarchar(max)
AS
BEGIN
    SET XACT_ABORT ON;
    
	BEGIN TRY
    BEGIN TRANSACTION
		-- Delcare
		DECLARE @creator uniqueidentifier;
		DECLARE @targetAccount TABLE(accountid nvarchar(100) collate Chinese_Taiwan_Stroke_CI_AS, nonescrowTotal int)
		DECLARE @oldPointImportSerialno uniqueidentifier;
		DECLARE @newPointImportSerialno uniqueidentifier = NEWID();
		DECLARE @transferedUserPointTable TABLE(pointserialno uniqueidentifier)
		
		-- Initial
		PRINT(N'【初始化...】')
		SET @creator = (SELECT serialno FROM Account WHERE accountid = 'MAYO-admin')
		SET @oldPointImportSerialno = (SELECT pointImportserialno FROM [dbo].[GiftActivity] WHERE serialno = @giftActivitySerialno)
		INSERT @targetAccount  SELECT * FROM OPENJSON(@accountJsonArray) WITH(accountid nvarchar(100) '$.accountid',nonescrowTotal int '$.nonescrowTotal');
		DROP TABLE IF EXISTS #escrowUserPointTable;
		DROP TABLE IF EXISTS #transferedUserPointTable;

		-- 0. 檢查
		PRINT(N'【資料檢查】')
		IF EXISTS (SELECT * FROM @targetAccount AS ta
		JOIN Account AS a ON a.accountid = ta.accountid
		JOIN UserPoint AS up ON up.accountserialno = a.serialno
		WHERE up.importserialno = @oldPointImportSerialno AND (up.escrowgroup is not null OR up.totalpoint < ta.nonescrowTotal)) 
		BEGIN 
			SELECT ta.accountid, 
			CASE 
				WHEN up.totalpoint < ta.nonescrowTotal THEN N'點數錯誤'
				WHEN up.escrowgroup is not null THEN N'該對象已做過餘額轉履保'
				ELSE N'未知的錯誤'
			END AS [message] 
			FROM @targetAccount AS ta
			JOIN Account AS a ON a.accountid = ta.accountid
			JOIN UserPoint AS up ON up.accountserialno = a.serialno
			WHERE up.importserialno = @oldPointImportSerialno AND (up.escrowgroup is not null OR up.totalpoint < ta.nonescrowTotal)
			RAISERROR (N'資料錯誤，請查看結果', 16, 1)
		END
		
		-- 1. 建立匯入主檔
		PRINT(N'【建立匯入主檔】escrowPointImportId = ' + CAST(@newPointImportSerialno AS nvarchar(50)) + ' ,PointImportId = ' + CAST(@oldPointImportSerialno AS nvarchar(50)) )
		INSERT INTO [dbo].[PointImport]
           ([serialno]
           ,[groupserialno]
           ,[typeserialno]
           ,[description]
           ,[importdate]
           ,[effectStartDate]
           ,[effectEndDate]
           ,[headcount]
           ,[pointPerHead]
           ,[totalpoint]
           ,[status]
           ,[creator]
           ,[createdatetime]
           ,[latestupdater]
           ,[latestupdatedatetime]
           ,[escrow]
           ,[escrowBegin]
           ,[escrowEnd]
           ,[Memo]
           ,[isUpdateEscrow]
           ,[escrowgroup])
		SELECT 
			@newPointImportSerialno
           ,oldPointImport.groupserialno
           ,oldPointImport.typeserialno
           ,oldPointImport.[description]
           ,@escrowBegin -- [importdate]
           ,oldPointImport.effectStartDate
           ,oldPointImport.effectEndDate
           ,0
           ,oldPointImport.pointPerHead
           ,totalpoint
           ,1
           ,@creator
           ,@escrowBegin
           ,@creator
           ,@escrowBegin
           ,1
           ,@escrowBegin
           ,@escrowEnd
           ,oldPointImport.Memo
           ,1
           ,@giftActivitySerialno
		FROM [PointImport] AS oldPointImport WHERE serialno = @oldPointImportSerialno

		-- 2. 新增履保點數
		PRINT(N'【新增履保點數】')
		SET @escrowBegin = DATEADD(ms, 100, @escrowBegin)
		SELECT 
           NEWID() AS serialno
           ,a.serialno AS [accountserialno]
           ,oldPointImport.typeserialno AS [typeserialno]
           ,newPointImport.serialno AS [importserialno]
           ,oldPointImport.description AS [description]
           ,@escrowBegin AS [effectStartDate]
           ,cast('9999-12-31' as datetime) AS [effectEndDate]
           ,ta.nonescrowTotal AS [totalPoint]
           ,ta.nonescrowTotal AS [availablePoint]
           ,0 AS [usedPoint]
           ,0 AS [blockPoint]
           ,1 AS [status]
           ,@creator AS [creator]
           ,@escrowBegin AS [createdatetime]
           ,@creator AS [latestupdater]
           ,@escrowBegin AS [latestupdatedatetime]
           ,@giftActivitySerialno AS [escrowgroup]
		INTO #escrowUserPointTable
		FROM @targetAccount AS ta
		INNER JOIN Account AS a ON a.accountid = ta.accountid
		CROSS APPLY (SELECT * FROM PointImport WHERE serialno = @oldPointImportSerialno) AS oldPointImport
		CROSS APPLY (SELECT * FROM PointImport WHERE serialno = @newPointImportSerialno) AS newPointImport

		INSERT INTO [dbo].[UserPoint]
           ([serialno]
           ,[accountserialno]
           ,[typeserialno]
           ,[importserialno]
           ,[description]
           ,[effectStartDate]
           ,[effectEndDate]
           ,[totalPoint]
           ,[availablePoint]
           ,[usedPoint]
           ,[blockPoint]
           ,[status]
           ,[creator]
           ,[createdatetime]
           ,[latestupdater]
           ,[latestupdatedatetime]
           ,[escrowgroup])
		SELECT * FROM #escrowUserPointTable

		INSERT INTO [dbo].[PointTransaction]
			([serialno]
			,[typeserialno]
			,[pointserialno]
			,[clientserialno]
			,[orderNo]
			,[usedPoint]
			,[Balance]
			,[sequence]
			,[status]
			,[descript]
			,[transactionNo]
			,[merchantReturnNo]
			,[authorizedClientKey]
			,[creator]
			,[createdatetime]
			,[latestupdater]
			,[latestupdatedatetime])
		SELECT 
			NEWID()
			,eupt.typeserialno
			,eupt.serialno
			,@creator
			,'1'
			,eupt.availablePoint AS [usedPoint]
			-- 選擇離點數轉履保最近的時間之前的[PointTransaction]的Balance
			,(SELECT TOP(1) [Balance] 
				FROM [PointTransaction] AS pt
				JOIN UserPoint AS up2 ON up2.serialno = pt.pointserialno
				WHERE pt.[latestupdatedatetime] < @escrowBegin 
				AND	up2.accountserialno = eupt.accountserialno
				 ORDER BY pt.[latestupdatedatetime] DESC)
			,1 AS [sequence]
			,5 AS [status]
			,pt.name
			,null
			,null
			,null
            ,@creator AS [creator]
            ,@escrowBegin AS [createdatetime]
            ,@creator AS [latestupdater]
            ,@escrowBegin AS [latestupdatedatetime]
		FROM #escrowUserPointTable AS eupt
		CROSS APPLY (SELECT * FROM [dbo].[PointType] WHERE serialno = eupt.typeserialno) AS pt

		-- 3. 移轉非履保時所花費的點數應算在履保點數
		PRINT(N'【移轉非履保時所花費的點數應算在履保點數】')

		UPDATE [PointTransaction]
		SET pointserialno = updateTable.newUserPointSerialno
		OUTPUT inserted.pointserialno AS pointserialno
		INTO @transferedUserPointTable
		FROM (SELECT pt.serialno, eupt.serialno AS newUserPointSerialno
				FROM [dbo].[PointTransaction] AS pt
					JOIN UserPoint AS up ON pt.pointserialno = up.serialno
					JOIN #escrowUserPointTable AS eupt ON eupt.accountserialno = up.accountserialno
					WHERE up.importserialno = @oldPointImportSerialno 
					AND up.accountserialno IN (SELECT serialno FROM @targetAccount AS ta INNER JOIN Account a ON a.accountid = ta.accountid)
					AND pt.latestupdatedatetime > @escrowBegin) AS updateTable
		WHERE [PointTransaction].serialno = updateTable.serialno

		-- 重新計算escrowUserPoint
		UPDATE [UserPoint]
		SET [availablePoint] = targetPointTransaction.[sum],
			[usedPoint] = [totalPoint] - targetPointTransaction.[sum]
		FROM (SELECT [pointserialno], SUM([usedPoint]) AS [sum]
			FROM [PointTransaction] 
			WHERE pointserialno IN (SELECT distinct pointserialno FROM @transferedUserPointTable)
			GROUP BY [pointserialno]) AS targetPointTransaction
		WHERE [serialno] = targetPointTransaction.[pointserialno]

		-- 4. 移轉非履保點數
		PRINT(N'【移轉非履保點數】')
		SET @escrowBegin = DATEADD(ms, -100, @escrowBegin)
		INSERT INTO [dbo].[PointTransaction]
           ([serialno]
           ,[typeserialno]
           ,[pointserialno]
           ,[clientserialno]
           ,[orderNo]
           ,[usedPoint]
           ,[Balance]
           ,[sequence]
           ,[status]
           ,[descript]
           ,[transactionNo]
           ,[merchantReturnNo]
           ,[authorizedClientKey]
           ,[creator]
           ,[createdatetime]
           ,[latestupdater]
           ,[latestupdatedatetime])
		SELECT
            NEWID()
            ,up.typeserialno
            ,up.serialno
            ,a.serialno
            ,''
            ,0 - ta.nonescrowTotal AS [usedPoint]
			-- 選擇離點數轉履保最近的時間之前的[PointTransaction]的Balance + [usedPoint]
            ,(SELECT TOP(1) [Balance] 
				FROM [PointTransaction] AS pt
				JOIN UserPoint AS up2 ON up2.serialno = pt.pointserialno
				WHERE pt.[latestupdatedatetime] < @escrowBegin 
				AND	up2.accountserialno = a.serialno
				 ORDER BY pt.[latestupdatedatetime] DESC) + (0 - ta.nonescrowTotal)
            ,1
            ,4
            ,N'非履保轉出'
            ,null AS [transactionNo]
            ,null AS [merchantReturnNo]
            ,null AS [authorizedClientKey]
            ,@creator AS [creator]
            ,@escrowBegin AS [createdatetime]
            ,@creator AS [latestupdater]
            ,@escrowBegin AS [latestupdatedatetime]
        FROM [dbo].[UserPoint] AS up
		INNER JOIN Account AS a ON a.serialno = up.accountserialno
		INNER JOIN @targetAccount AS ta ON a.accountid = ta.accountid
		WHERE up.importserialno = @oldPointImportSerialno 

		UPDATE [UserPoint]
		SET availablePoint = 0,
            usedPoint = totalPoint,
            escrowgroup = @giftActivitySerialno,
            latestupdater = @creator,
            latestupdatedatetime = @escrowBegin
		WHERE importserialno = @oldPointImportSerialno 
		AND accountserialno IN (SELECT serialno FROM @targetAccount AS ta INNER JOIN Account a ON a.accountid = ta.accountid)
		
		-- 5. 儲存轉移紀錄
		PRINT(N'【儲存轉移紀錄】')
		INSERT INTO [dbo].[GiftPointEscrow]
           ([serialno]
           ,[accountserialno]
           ,[activityserialno]
           ,[importserialno]
           ,[pointserialno]
           ,[escrowimportserialno]
           ,[escrowpointserialno]
           ,[escrowPoint]
           ,[creator]
           ,[createdatetime]
           ,[latestupdater]
           ,[latestupdatedatetime])
		SELECT 
			NEWID() AS [serialno]
			,eupt.accountserialno AS [accountserialno]
			,@giftActivitySerialno AS [activityserialno]
			,oldUserPoint.importserialno AS [importserialno]
			,oldUserPoint.serialno AS [pointserialno]
			,eupt.importserialno
			,eupt.serialno
			,eupt.[totalPoint]
			,eupt.creator
			,eupt.createdatetime
			,eupt.latestupdater
			,eupt.latestupdatedatetime
		FROM #escrowUserPointTable AS eupt
		CROSS APPLY(SELECT * FROM [UserPoint] AS up WHERE eupt.accountserialno = up.accountserialno AND up.importserialno = @oldPointImportSerialno ) AS oldUserPoint

		PRINT(N'【點數餘額轉履保完成】')
	COMMIT TRANSACTION
	END TRY

	BEGIN CATCH
		PRINT
			'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
			  ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
			  ', State ' + CONVERT(varchar(5), ERROR_STATE()) +
			  ', Line ' + CONVERT(varchar(5), ERROR_LINE())

		PRINT ERROR_MESSAGE();

		  IF XACT_STATE() <> 0 BEGIN
			ROLLBACK TRANSACTION
		  END
	END CATCH;
END
GO