· 5 years ago · Mar 31, 2020, 04:26 PM
1-- Check to see if the temporary table already exists.
2IF OBJECT_ID('tempdb..#TmpFolios') IS NOT NULL
3 DROP TABLE #TmpFolios
4
5CREATE TABLE #TmpFolios (
6 tmpFolUId INT NOT NULL,
7 tmpFolTypRepeaterId INT NOT NULL,
8 tmpFolTypFlagId INT NOT NULL,
9 tmpFolDaysCount INT NOT NULL,
10 tmpRebLastTripEndDate DATETIME
11 )
12
13DECLARE @PageEndUId INT;
14
15DECLARE @FLAGBLACK AS INT
16DECLARE @FLAGDEAD AS INT
17DECLARE @FLAGSTAFF AS INT
18DECLARE @RPLOPTOUT AS INT
19
20
21SELECT @FLAGBLACK = typUId FROM typType WHERE typGroup = 'flg' AND typEnabled = 1 AND typId = 'BLACK'
22SELECT @FLAGDEAD = typUId FROM typType WHERE typGroup = 'flg' AND typEnabled = 1 AND typId = 'DEAD'
23SELECT @FLAGSTAFF = typUId FROM typType WHERE typGroup = 'flg' AND typEnabled = 1 AND typId = 'STAFF'
24SELECT @RPLOPTOUT = typUId FROM typType WHERE typGroup = 'rpl' AND typEnabled = 1 AND typId = 'OPTOUT' -- No Longer an Explorer Club Member
25
26
27-- Identify folios that should have their repeater level recalculated
28INSERT INTO #TmpFolios
29 SELECT
30 folUId,
31 MAX(folTypRepeaterId),
32 MAX(folTypFlagId),
33 MAX(folDaysCount),
34 MIN(rebLastTripEndDate)
35 FROM folFolio
36 INNER JOIN refReservedFolio ON (refFolFolioId = folUId)
37 INNER JOIN rebReservedBooking ON (rebUId = refRebBookingId)
38 WHERE 1=1
39 AND folUId > 0
40 AND folType = 'P'
41 AND folEnabled = 1
42 AND folDeleted = 0
43 AND refUId > 0
44 AND refEnabled = 1
45 AND rebUId > 0
46 AND rebEnabled = 1
47 AND rebBookingStatus BETWEEN 'R' AND 'V'
48 AND rebLastTripEndDate < GETDATE()
49 GROUP BY folUId;
50
51-- Calculate the repeater level in memory/tempdb, without locking folio table.
52UPDATE #TmpFolios
53SET
54 tmpFolTypRepeaterId = ISNULL(
55 CASE
56 WHEN tmpFolTypFlagId IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) THEN 21739 -- OPTOUT No Longer an Explorer Club Member
57 WHEN (
58 tmpFolDaysCount BETWEEN 0 AND 2 -- No Repeater Level
59 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
60 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
61 ) THEN 0 -- 'Loyalty Club Member'
62 WHEN (
63 tmpFolDaysCount BETWEEN 3 AND 13 -- Silver 5% Repeater Level
64 AND tmpRebLastTripEndDate >= CONVERT(DATE,'2012-11-01',20)
65 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
66 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
67 ) THEN 21651 -- 'SILVER 5%'
68 WHEN (
69 tmpFolDaysCount BETWEEN 14 AND 50 -- Gold Repeater Level
70 AND tmpRebLastTripEndDate < CONVERT(DATE,GETDATE(),20)
71 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
72 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
73 ) THEN 1715 -- 'GOLD'
74 WHEN (
75 tmpFolDaysCount BETWEEN 51 AND 101 -- Platinum Repeater Level
76 AND tmpRebLastTripEndDate < CONVERT(DATE,GETDATE(),20)
77 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
78 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
79 ) THEN 1714 -- 'PLATINUM'
80 WHEN (
81 tmpFolDaysCount >= 102 -- Double Platinum Repeater Level
82 AND tmpRebLastTripEndDate < CONVERT(DATE,GETDATE(),20)
83 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
84 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
85 ) THEN 205432 -- 'DOUBLE PLATINUM'
86 ELSE tmpFolTypRepeaterId
87 END
88 , tmpFolTypRepeaterId),
89 tmpFolTypFlagId = ISNULL(
90 CASE
91 WHEN tmpFolTypFlagId = @FLAGDEAD THEN 1969 -- Deceased
92 WHEN tmpFolTypFlagId = @FLAGBLACK THEN 1721 -- Black Listed
93 WHEN tmpFolTypFlagId = @FLAGSTAFF THEN 1719 -- Staff
94 WHEN tmpFolTypRepeaterId = @RPLOPTOUT THEN -21 -- No Longer an Explorer Club Member
95 WHEN (
96 tmpFolDaysCount BETWEEN 0 AND 2
97 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
98 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
99 ) THEN -21 --'Loyalty Club Member'
100 WHEN (
101 tmpFolDaysCount BETWEEN 3 AND 13
102 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
103 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
104 ) THEN 21523 --'SILVER'
105 WHEN (
106 tmpFolDaysCount BETWEEN 14 AND 50
107 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
108 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
109 ) THEN 21525 --'GOLD'
110 WHEN (
111 tmpFolDaysCount >= 51
112 AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
113 AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
114 ) THEN 21526 --'PLATINUM'
115 ELSE tmpFolTypFlagId
116 END
117 , tmpFolTypFlagId)
118
119
120-- Remove records that would be updated to the same value they already have.
121DELETE FROM #TmpFolios
122WHERE tmpFolTypRepeaterId = (SELECT folTypRepeaterId FROM folFolio WHERE folUId = tmpFolUId)
123 AND tmpFolTypFlagId = (SELECT folTypFlagId FROM folFolio WHERE folUId = tmpFolUId)
124
125
126-- Update the booking table in batches.
127WHILE EXISTS (SELECT * FROM #TmpFolios)
128BEGIN
129
130 SELECT
131 @PageEndUId = MAX(tmpFolUId)
132 FROM
133 (
134 SELECT TOP 5000
135 tmpFolUId
136 FROM #TmpFolios
137 ORDER BY tmpFolUId
138 ) AS Page
139
140 UPDATE folFolio
141 SET
142 folTypFlagId = tmpFolTypFlagId,
143 folTypRepeaterId = tmpFolTypRepeaterId,
144 folModifyDate = GETUTCDATE(),
145 folUsrModifyId = 1
146 FROM folFolio
147 INNER JOIN #TmpFolios ON (folUId = tmpFolUId)
148 WHERE tmpFolUId <= @PageEndUId
149
150 DELETE FROM #TmpFolios
151 WHERE tmpFolUId <= @PageEndUId
152
153END
154
155DROP TABLE #TmpFolios
156_]