· 5 years ago · Mar 31, 2020, 04:02 PM
1/*-------------------------------------------------------------------------------------------------
2Description: Unit 231035: Change P type folios linked to SILV-L1 or SILV-ML1 to SILV-L2 (5% Silver).
3Limitation:
4SQL Server: 2008+
5CRS Version: 6.14.01+
6---------------------------------------------------------------------------------------------------
7DONE:
81.3 Minor fix in @sScriptInfo1
9- remember to set @sScriptVersion!
10---------------------------------------------------------------------------------------------------*/
11---------------------------------------------------------------------------------------------------
12-- Fix script business logic
13---------------------------------------------------------------------------------------------------
14-- Create temp sp to store fix script business logic
15IF OBJECT_ID (N'temp_spFixScript', N'P') IS NOT NULL
16 DROP PROCEDURE temp_spFixScript
17GO
18CREATE PROCEDURE temp_spFixScript WITH RECOMPILE
19AS
20BEGIN
21 DECLARE @sCRSVersion NVARCHAR(10) -- Current CRS version
22 SET @sCRSVersion = (SELECT typId FROM typType WHERE typUId=(SELECT ISNULL(MAX(typUId),0) FROM typType WHERE typGroup='ver'))
23 ---------------------------------------------------------------------------------------------------
24 -- Begin fix script business logic
25 -- Mark all modified records with xUsrModifyId=2 and xModifyDate=GETUTCDATE()
26 ---------------------------------------------------------------------------------------------------
27 -- TODO: write your fix script business logic here - sample for version dependent logic
28 BEGIN
29 PRINT '231035: Change P type folios linked to SILV-L1 or SILV-ML1 to SILV-L2 (5% Silver).'
30 DECLARE @RPLSILVL1 AS INT, @RPLSILVML1 AS INT, @RPLSILVL2 AS INT, @PageEndUId INT;
31 SELECT @RPLSILVL1 = typUId FROM typType WHERE typGroup = 'rpl' AND typEnabled = 1 AND typId = 'SILV-L1' -- Silver 10% Repeater Level
32 SELECT @RPLSILVML1 = typUId FROM typType WHERE typGroup = 'rpl' AND typEnabled = 1 AND typId = 'SILV-ML1' -- Silver-10%-Manual
33 SELECT @RPLSILVL2 = typUId FROM typType WHERE typGroup = 'rpl' AND typEnabled = 1 AND typId = 'SILV-L2' -- Silver 5% Repeater Level
34 -- Check to see if the temporary table already exists.
35 IF OBJECT_ID('tempdb..#TmpFoliosRepeaterLvl') IS NOT NULL
36 DROP TABLE #TmpFoliosRepeaterLvl
37 CREATE TABLE #TmpFoliosRepeaterLvl (
38 tmpFolUId INT NOT NULL,
39 tmpFolTypRepeaterId INT NOT NULL
40 )
41 -- Identify folios that should have their repeater level recalculated
42 INSERT INTO #TmpFoliosRepeaterLvl
43 SELECT
44 folUId,
45 @RPLSILVL2
46 FROM folFolio
47 WHERE folUId > 0
48 AND folType = 'P'
49 AND folEnabled = 1
50 AND folDeleted = 0
51 AND folTypRepeaterId IN (@RPLSILVL1, @RPLSILVML1)
52 -- Remove records that would be updated to the same value they already have.
53 DELETE FROM #TmpFoliosRepeaterLvl
54 WHERE tmpFolTypRepeaterId = (SELECT folTypRepeaterId FROM folFolio WHERE folUId = tmpFolUId)
55 -- Update the booking table in batches.
56 WHILE EXISTS (SELECT * FROM #TmpFoliosRepeaterLvl)
57 BEGIN
58 SELECT
59 @PageEndUId = MAX(tmpFolUId)
60 FROM
61 (
62 SELECT TOP 5000
63 tmpFolUId
64 FROM #TmpFoliosRepeaterLvl
65 ORDER BY tmpFolUId
66 ) AS Page
67
68 UPDATE folFolio
69 SET
70 folTypRepeaterId = tmpFolTypRepeaterId,
71 folModifyDate = GETUTCDATE(),
72 folUsrModifyId = 2
73 FROM folFolio
74 INNER JOIN #TmpFoliosRepeaterLvl ON (folUId = tmpFolUId)
75 WHERE tmpFolUId <= @PageEndUId
76 DELETE FROM #TmpFoliosRepeaterLvl
77 WHERE tmpFolUId <= @PageEndUId
78
79 END
80 DROP TABLE #TmpFoliosRepeaterLvl
81 END
82 ---------------------------------------------------------------------------------------------------
83 -- End fix script business logic
84 ---------------------------------------------------------------------------------------------------
85END
86GO
87GRANT EXECUTE ON dbo.temp_spFixScript TO PUBLIC
88GO
89---------------------------------------------------------------------------------------------------
90-- Initialize
91---------------------------------------------------------------------------------------------------
92DECLARE @sCRSSiteId NVARCHAR(20) -- Current CRS site id
93DECLARE @sCRSVersion NVARCHAR(10) -- Current CRS version
94DECLARE @sCRSVersionMin NVARCHAR(10) -- Min supported CRS ver
95DECLARE @sCRSVersionMax NVARCHAR(10) -- Max supported CRS ver
96DECLARE @sScriptSiteId NVARCHAR(20) -- Site id this script supports
97DECLARE @sScriptInfo1 NVARCHAR(1000)
98DECLARE @sScriptInfo2 NVARCHAR(MAX)
99DECLARE @sScriptVersion NVARCHAR(10)
100DECLARE @sScriptFilename NVARCHAR(100)
101DECLARE @nScriptResult INT
102DECLARE @nUserId INT
103SET @nScriptResult = 0 -- [0:Fail|1:Success]
104SET @nUserId = 2 -- SystemAdmin. Easy to identify all records updated by any fix script on particular date
105SET @sCRSSiteId = (SELECT cmpCode FROM cmpCompany WHERE cmpUId=1)
106SET @sCRSVersion = (SELECT typId FROM typType WHERE typUId=(SELECT ISNULL(MAX(typUId),0) FROM typType WHERE typGroup='ver'))
107-- TODO: specify the following
108SET @sScriptFilename = 'CORChangeRepeaterLevel.sql' -- Fix script filename
109SET @sScriptInfo2 = '231035: Change P type folios linked to SILV-L1 or SILV-ML1 to SILV-L2 (5% Silver).' -- TFS ticket id (subunit), short description
110SET @sScriptVersion = '1.0'
111SET @sScriptSiteId = 'COR' -- Supported site id
112SET @sCRSVersionMin = '6.14.01' -- Supported min CRS ver
113SET @sCRSVersionMax = '6.14.99' -- Supported max CRS ver
114---------------------------------------------------------------------------------------------------
115-- Validate
116---------------------------------------------------------------------------------------------------
117BEGIN TRY
118-- Validate if CRS version is supported
119IF EXISTS (SELECT * FROM typType WHERE typGroup='ver' AND (@sCRSVersion > @sCRSVersionMax OR @sCRSVersion < @sCRSVersionMin))
120 RAISERROR('This script cannot be run on CRS version %s. Supported versions are %s to %s. Download latest version from FTP.',11,1,@sCRSVersion,@sCRSVersionMin,@sCRSVersionMax)
121-- Validate site owner
122IF NOT EXISTS (SELECT * FROM cmpCompany WHERE cmpUId=1 AND (cmpCode=@sScriptSiteId OR cmpCode='DMO' OR cmpName1='Demo'))
123 RAISERROR('This script is designed to be run only for %s company.',11,1,@sScriptSiteId)
124---------------------------------------------------------------------------------------------------
125-- EXEC fix script business logic
126---------------------------------------------------------------------------------------------------
127BEGIN TRANSACTION
128EXEC temp_spFixScript
129COMMIT TRANSACTION
130---------------------------------------------------------------------------------------------------
131-- Commit all, save script run info
132---------------------------------------------------------------------------------------------------
133SET @nScriptResult = 1
134SET @sScriptInfo1 = @sScriptFilename + ', ' + @sCRSVersion +', ' + @sScriptVersion + ', ' + @sCRSSiteId
135-- Save script info
136INSERT INTO typType (typGroup,typString1,typString2,typId,typValue1,typDate1,typDate2,typModifyDate,typEnabled,typDeleted,typUsrModifyId,typValue2,typTypLinkId)
137 SELECT 'ves',LEFT(@sScriptInfo1, 100),@sScriptInfo2,@sCRSVersion,@nScriptResult,GETDATE(),CONVERT(DATETIME,'1800-01-01',20),GETUTCDATE(),1,0,@nUserId,-100000000,0
138 PRINT 'SUCCESS - ' + @sScriptInfo1
139END TRY
140---------------------------------------------------------------------------------------------------
141-- Error handling. Save script error info
142---------------------------------------------------------------------------------------------------
143BEGIN CATCH
144-- Rollback anything that was done to this point.
145-- XACT_STATE() 0: no transactions, a commit or rollback operation would generate an error. 1: transaction is committable. 2: transaction is in an uncommittable state
146IF (XACT_STATE() <> 0 AND @@TRANCOUNT > 0)
147 ROLLBACK TRANSACTION
148-- Save script info
149SET @sScriptInfo1 = @sScriptFilename + ', ' + @sCRSVersion +', ' + @sScriptVersion + ', ' + @sCRSSiteId + ', Error:' + CONVERT(NVARCHAR,ERROR_NUMBER()) + ' ' + + ERROR_MESSAGE() + ' Line:' + CONVERT(NVARCHAR,ERROR_LINE())
150INSERT INTO typType (typGroup,typString1,typString2,typId,typValue1,typDate1,typDate2,typModifyDate,typEnabled,typDeleted,typUsrModifyId,typValue2,typTypLinkId)
151 SELECT 'ves',LEFT(@sScriptInfo1, 100),@sScriptInfo2,@sCRSVersion,@nScriptResult,GETDATE(),CONVERT(DATETIME,'1800-01-01',20),GETUTCDATE(),1,0,@nUserId,-100000000,0
152 PRINT 'FAIL - ' + @sScriptInfo1
153END CATCH
154GO
155---------------------------------------------------------------------------------------------------
156-- Cleanup
157---------------------------------------------------------------------------------------------------
158IF OBJECT_ID (N'temp_spFixScript', N'P') IS NOT NULL
159 DROP PROCEDURE temp_spFixScript
160GO
161SET NOEXEC OFF
162SET NOCOUNT OFF
163GO