iVend Database Database : RecalculateSlabForLoyaltyCustomer Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgLoyaltyConfig

Table

Stores the configuration information for the loyalty

1

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

LoyLoyaltyCardInformation

Table

Store the Details of Loyalty Customer in the system.

1

LoyMembershipGroup

Table

Stores the Header detail of Loyalty Membership Group in the system.

1

LoyPointsSummary

Table

Stores the Summary of Loyalty Points awarded in the system.

1

usp_RethrowError

Stored Procedure

Rethrow an error to the calling function/application

1

CREATE PROCEDURE [dbo].[RecalculateSlabForLoyaltyCustomer]

AS

BEGIN

SET NOCOUNT ON

DECLARE @lMinimumSlabValueCalculatedOn INT,

  @lCount   nvarchar(50),

  @lMinimumValue NUMERIC(18,2),

  @pSiteId BIGINT,

  @lMembershipGroupCount nvarchar(50),

  @pPOSSiteId INT,

  @LoyaltyConfigKey nvarchar(50),

  @BatchKey nvarchar(50)

BEGIN TRY

  Select @pSiteId = SiteId, @pPOSSiteId = ISNULL(POSSiteId, 0) From CFGSiteInformation

  CREATE TABLE #LoyaltyCustomer

  (

    LoyaltyCardInformationKey nvarchar(50),

    SlabKey nvarchar(50),

    NewSlabKey nvarchar(50),

    MembershipGroupKey nvarchar(50),

    HasMembershipGroup BIT

  )

  INSERT INTO #LoyaltyCustomer(LoyaltyCardInformationKey, SlabKey, MembershipGroupKey, HasMembershipGroup)

  SELECT LoyaltyCardInformationKey, SlabKey, MembershipGroupKey, HasMembershipGroup

  FROM LoyLoyaltyCardInformation

  WHERE IsActive = 1

  AND OnHold = 0

  AND IsDeleted = 0

SELECT @lCount = MIN(LoyaltyCardInformationKey)

FROM #LoyaltyCustomer

Where IsNull(MembershipGroupKey, '0') = '0'

AND HasMembershipGroup=0

SELECT @lMembershipGroupCount = MIN(MembershipGroupKey)

FROM #LoyaltyCustomer

Where IsNull(MembershipGroupKey,'0') <>'0'

AND HasMembershipGroup=1

      ---------------------------------Start Update Slab For Non LoyaltyMembership Group------------------

WHILE(@lCount IS NOT NULL)

BEGIN

  SET @lMinimumValue = 0

    SELECT @LoyaltyConfigKey=LoyaltyConfigKey FROM LOYLOYALTYCARDINFORMATION WHERE LOYALTYCARDINFORMATIONKEY = @lCount

SELECT @lMinimumSlabValueCalculatedOn = MinimumSlabValueCalculatedOn FROM CfgLoyaltyConfig where LoyaltyConfigKey =@LoyaltyConfigKey

IF(@lMinimumSlabValueCalculatedOn = 1)

SELECT @lMinimumValue = AvailablePoints FROM LoyPointsSummary WHERE CustomerKey = @lCount

  ELSE IF(@lMinimumSlabValueCalculatedOn = 2)

SELECT @lMinimumValue = AwardedPoints FROM LoyPointsSummary WHERE CustomerKey = @lCount

  ELSE IF(@lMinimumSlabValueCalculatedOn = 3)

SELECT @lMinimumValue = AvailableCurrency FROM LoyPointsSummary WHERE CustomerKey = @lCount

  ELSE IF(@lMinimumSlabValueCalculatedOn = 4)

SELECT @lMinimumValue = AwardedCurrency FROM LoyPointsSummary WHERE CustomerKey = @lCount

  /*Get Slab Based on Minimum Value*/

  UPDATE #LoyaltyCustomer

SET NewSlabKey = (SELECT Top 1 SlabKey FROM LoySlab

    WHERE MinimumValue <= ISNULL(@lMinimumValue,0) AND IsDeleted = 0   AND LoyaltyConfigKey=@LoyaltyConfigKey

    ORDER BY MinimumValue DESC

       )

WHERE LoyaltyCardInformationKey = @lCount

  SELECT @lCount = MIN(LoyaltyCardInformationKey) FROM #LoyaltyCustomer WHERE LoyaltyCardInformationKey > @lCount

END

UPDATE A

  SET A.SlabKey = B.NewSlabKey

  FROM LoyLoyaltyCardInformation A

  INNER JOIN #LoyaltyCustomer B ON B.LoyaltyCardInformationKey = A.LoyaltyCardInformationKey

  WHERE ISNULL(B.NewSlabKey,'0') <>'0' AND B.NewSlabKey <> B.SlabKey

SELECT @BatchKey = NEWID()

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 338, LoyaltyCardInformationKey, @BatchKey, 1, 'FALSE'

FROM #LoyaltyCustomer

WHERE ISNULL(NewSlabKey,'0') <> '0' AND NewSlabKey <> SlabKey

---------------------------------End Update Slab For Non LoyaltyMembership Group------------------

  ---------------------------------Start Update Slab For LoyaltyMembership Group------------------

WHILE(@lMembershipGroupCount IS NOT NULL)

BEGIN

        select @LoyaltyConfigKey=LoyaltyConfigKey from loyMembershipGroup WHERE MembershipGroupKey = @lMembershipGroupCount

      SELECT @lMinimumSlabValueCalculatedOn = MinimumSlabValueCalculatedOn FROM CfgLoyaltyConfig where LoyaltyConfigKey =@LoyaltyConfigKey

  SET @lMinimumValue = 0

  IF(@lMinimumSlabValueCalculatedOn = 1)

  SELECT @lMinimumValue = AvailablePoints FROM LoyMembershipGroup WHERE MembershipGroupKey = @lMembershipGroupCount

  ELSE IF(@lMinimumSlabValueCalculatedOn = 2)

  SELECT @lMinimumValue = AwardedPoints FROM LoyMembershipGroup WHERE MembershipGroupKey = @lMembershipGroupCount

  ELSE IF(@lMinimumSlabValueCalculatedOn = 3)

  SELECT @lMinimumValue = AvailableCurrency FROM LoyMembershipGroup WHERE MembershipGroupKey = @lMembershipGroupCount

  ELSE IF(@lMinimumSlabValueCalculatedOn = 4)

  SELECT @lMinimumValue = AwardedCurrency FROM LoyMembershipGroup WHERE MembershipGroupKey = @lMembershipGroupCount

  /*Get Slab Based on Minimum Value*/

  UPDATE #LoyaltyCustomer SET NewSlabKey = (SELECT Top 1 SlabKey FROM LoySlab

            WHERE MinimumValue <= ISNULL(@lMinimumValue,0) AND IsDeleted = 0 AND LoyaltyConfigKey=@LoyaltyConfigKey

            ORDER BY MinimumValue DESC)

              WHERE MembershipGroupKey = @lMembershipGroupCount

  SELECT @lMembershipGroupCount = MIN(MembershipGroupKey) FROM #LoyaltyCustomer WHERE MembershipGroupKey > @lMembershipGroupCount

END

UPDATE A

  SET A.SlabKey = B.NewSlabKey

  FROM LoyMembershipGroup A

  INNER JOIN #LoyaltyCustomer B ON B.MembershipGroupKey = A.MembershipGroupKey

  WHERE ISNULL(B.NewSlabKey,'0') <> '0' AND B.NewSlabKey <> A.SlabKey

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 356, MembershipGroupKey, @BatchKey,1, 'FALSE'

FROM #LoyaltyCustomer

WHERE ISNULL(NewSlabKey,'0') <> '0' AND NewSlabKey <> SlabKey

---------------------------------End Update Slab For  LoyaltyMembership Group------------------

END TRY

BEGIN CATCH

-- Raise the Error that caused the Error

Exec usp_RethrowError

RETURN (1)

END CATCH

END