-- Check to see if the temporary table already exists.
IF OBJECT_ID('tempdb..#TmpFolios') IS NOT NULL
  DROP TABLE #TmpFolios

CREATE TABLE #TmpFolios (
  tmpFolUId INT NOT NULL,
  tmpFolTypRepeaterId INT NOT NULL,
  tmpFolTypFlagId INT NOT NULL,
  tmpFolDaysCount INT NOT NULL,
  tmpRebLastTripEndDate DATETIME
  )

DECLARE @PageEndUId INT;

DECLARE @FLAGBLACK AS INT
DECLARE @FLAGDEAD AS INT
DECLARE @FLAGSTAFF AS INT
DECLARE @RPLOPTOUT AS INT


SELECT @FLAGBLACK = typUId FROM typType WHERE typGroup = 'flg' AND typEnabled = 1 AND typId = 'BLACK'
SELECT @FLAGDEAD = typUId FROM typType WHERE typGroup = 'flg' AND typEnabled = 1 AND typId = 'DEAD'
SELECT @FLAGSTAFF = typUId FROM typType WHERE typGroup = 'flg' AND typEnabled = 1 AND typId = 'STAFF'
SELECT @RPLOPTOUT = typUId FROM typType WHERE typGroup = 'rpl' AND typEnabled = 1 AND typId = 'OPTOUT' -- No Longer an Explorer Club Member


-- Identify folios that should have their repeater level recalculated
INSERT INTO #TmpFolios
  SELECT
    folUId,
    MAX(folTypRepeaterId),
    MAX(folTypFlagId),
    MAX(folDaysCount),
    MIN(rebLastTripEndDate)
  FROM folFolio
    INNER JOIN refReservedFolio ON (refFolFolioId = folUId)
    INNER JOIN rebReservedBooking ON (rebUId = refRebBookingId)
  WHERE 1=1
    AND folUId > 0
    AND folType = 'P'
    AND folEnabled = 1
    AND folDeleted = 0
    AND refUId > 0
    AND refEnabled = 1
    AND rebUId > 0
    AND rebEnabled = 1
    AND rebBookingStatus BETWEEN 'R' AND 'V'
    AND rebLastTripEndDate < GETDATE()
  GROUP BY folUId;

-- Calculate the repeater level in memory/tempdb, without locking folio table.
UPDATE #TmpFolios
SET
  tmpFolTypRepeaterId = ISNULL(
      CASE
        WHEN tmpFolTypFlagId IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) THEN 21739 -- OPTOUT No Longer an Explorer Club Member
        WHEN (
               tmpFolDaysCount BETWEEN 0 AND 2 -- No Repeater Level
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 0  -- 'Loyalty Club Member'
        WHEN (
               tmpFolDaysCount BETWEEN 3 AND 13 -- Silver 5% Repeater Level
               AND tmpRebLastTripEndDate >= CONVERT(DATE,'2012-11-01',20)
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 21651  -- 'SILVER 5%'
        WHEN (
               tmpFolDaysCount BETWEEN 14 AND 50 -- Gold Repeater Level
               AND tmpRebLastTripEndDate < CONVERT(DATE,GETDATE(),20)
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 1715  -- 'GOLD'
        WHEN (
               tmpFolDaysCount BETWEEN 51 AND 101 -- Platinum Repeater Level
               AND tmpRebLastTripEndDate < CONVERT(DATE,GETDATE(),20)
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 1714  -- 'PLATINUM'
        WHEN (
               tmpFolDaysCount >= 102 -- Double Platinum Repeater Level
               AND tmpRebLastTripEndDate < CONVERT(DATE,GETDATE(),20)
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 205432  -- 'DOUBLE PLATINUM'
        ELSE tmpFolTypRepeaterId
      END
    , tmpFolTypRepeaterId),
  tmpFolTypFlagId = ISNULL(
      CASE
        WHEN tmpFolTypFlagId = @FLAGDEAD THEN 1969 -- Deceased
        WHEN tmpFolTypFlagId = @FLAGBLACK THEN 1721 -- Black Listed
        WHEN tmpFolTypFlagId = @FLAGSTAFF THEN 1719 -- Staff
        WHEN tmpFolTypRepeaterId = @RPLOPTOUT THEN -21 -- No Longer an Explorer Club Member
        WHEN (
               tmpFolDaysCount BETWEEN 0 AND 2
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN -21  --'Loyalty Club Member'
        WHEN (
               tmpFolDaysCount BETWEEN 3 AND 13
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 21523  --'SILVER'
        WHEN (
               tmpFolDaysCount BETWEEN 14 AND 50
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 21525  --'GOLD'
        WHEN (
               tmpFolDaysCount >= 51
               AND tmpFolTypFlagId NOT IN (@FLAGBLACK, @FLAGDEAD, @FLAGSTAFF) -- Deceased, Black Listed, Staff
               AND tmpFolTypRepeaterId <> @RPLOPTOUT -- No Longer an Explorer Club Member
             ) THEN 21526  --'PLATINUM'
        ELSE tmpFolTypFlagId
      END
    , tmpFolTypFlagId)


-- Remove records that would be updated to the same value they already have.
DELETE FROM #TmpFolios
WHERE tmpFolTypRepeaterId = (SELECT folTypRepeaterId FROM folFolio WHERE folUId = tmpFolUId)
  AND tmpFolTypFlagId = (SELECT folTypFlagId FROM folFolio WHERE folUId = tmpFolUId)


-- Update the booking table in batches.
WHILE EXISTS (SELECT * FROM #TmpFolios)
BEGIN

  SELECT
      @PageEndUId = MAX(tmpFolUId)
  FROM
    (
      SELECT TOP 5000
          tmpFolUId
      FROM #TmpFolios
      ORDER BY tmpFolUId
    ) AS Page

  UPDATE folFolio
  SET
    folTypFlagId = tmpFolTypFlagId,
    folTypRepeaterId = tmpFolTypRepeaterId,
    folModifyDate = GETUTCDATE(),
    folUsrModifyId = 1
  FROM folFolio
    INNER JOIN #TmpFolios ON (folUId = tmpFolUId)
  WHERE tmpFolUId <= @PageEndUId

  DELETE FROM #TmpFolios
  WHERE tmpFolUId <= @PageEndUId

END

DROP TABLE #TmpFolios
_]