iVend Database Database : Loy_GetMonthlyPointSummary Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@LoyaltyCard

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

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

2

GetCompanyDateTime

User Defined Function

1

LoyLoyaltyCardInformation

Table

Store the Details of Loyalty Customer in the system.

1

LoyPoints

Table

Stores the Header detail of Loyalty Points awarded in the system.

1

/****** Object:  StoredProcedure [dbo].[Loy_GetMonthlyPointSummary]    Script Date: 02/18/2015 10:33:21 ******/

CREATE PROCEDURE [dbo].[Loy_GetMonthlyPointSummary](@LoyaltyCard NVARCHAR(50))

AS

BEGIN

DECLARE @months TABLE

(

  Month DATETIME,

   AwardPoint INT,

   RedeemPoint INT,

   AwardCurrency DECIMAL(38,2),

   RedeemCurency DECIMAL(38,2)

 )

DECLARE @tmpDate DATETIME

DECLARE @LoyaltyCardInformationKey nvarchar(50)

DECLARE @MembershipGroupKey nvarchar(50)

SET @tmpDate = DATEADD(MONTH ,-11,dbo.GetCompanyDateTime())

SET @tmpDate = DATEADD(DAY,(1-DATEPART(dd,@tmpDate)),@tmpDate)

IF(exists( SELECT LoyaltyCardInformationKey FROM LoyLoyaltyCardInformation WHERE LoyaltyId =@LoyaltyCard))

BEGIN

SELECT @MembershipGroupKey=ISNULL(MembershipGroupKey,0) FROM LoyLoyaltyCardInformation WHERE LoyaltyId=@LoyaltyCard

SELECT @LoyaltyCardInformationKey =LoyaltyCardInformationKey FROM LoyLoyaltyCardInformation WHERE LoyaltyId=@LoyaltyCard

WHILE(1 = 1)

BEGIN

IF @MembershipGroupKey!='0'

BEGIN

      IF(EXISTS(SELECT * from LoyPoints WHERE DATEPART(MM, @tmpDate) = DATEPART(MM, Created)

      AND DATEPART(YYYY, @tmpDate) = DATEPART(YYYY, Created) and CustomerKey=@LoyaltyCardInformationKey))

      BEGIN

        INSERT INTO @months

        SELECT @tmpDate,SUM(AwardedPoints), SUM(RedeemedPoints),SUM(AwardedCurrency),SUM(RedeemedCurrency)

        FROM LoyPoints

        WHERE DATEPART(MM, @tmpDate) = DATEPART(MM, Created)

        AND DATEPART(YYYY, @tmpDate) = DATEPART(YYYY, Created)AND  CustomerKey=@LoyaltyCardInformationKey

GROUP BY DATEPART(MM, Created),DATEPART(YYYY, Created)

  --  print  ((DATENAME(MM,@tmpDate)+ Datename(YYYY,@tmpDate)))

END

    ELSE

              INSERT INTO @months VALUES(@tmpDate, 0,0,0,0)

                --print  ((DATENAME(MM,@tmpDate)+ Datename(YYYY,@tmpDate)))

          IF(MONTH(@tmpDate)= MONTH(dbo.GetCompanyDateTime()) and year(@tmpDate)= year(dbo.GetCompanyDateTime()))

        -- if(MONTH(@tmpDate) + 1 > 12)

              BREAK;

      SET @tmpDate = DATEADD(MM, 1, @tmpDate);

  END

IF @MembershipGroupKey !='0'

BEGIN

      IF(EXISTS(SELECT * FROM LoyPoints WHERE DATEPART(MM, @tmpDate) = DATEPART(MM, Created)

      AND DATEPART(YYYY, @tmpDate) = DATEPART(YYYY, Created) and MembershipGroupKey=@MembershipGroupKey))

      BEGIN

        INSERT INTO @months

        SELECT @tmpDate,SUM(AwardedPoints), SUM(RedeemedPoints),SUM(AwardedCurrency),SUM(RedeemedCurrency)

        FROM LoyPoints

        WHERE DATEPART(MM, @tmpDate) = DATEPART(MM, Created)

        AND DATEPART(YYYY, @tmpDate) = DATEPART(YYYY, Created)AND  MembershipGroupKey=@MembershipGroupKey

GROUP BY DATEPART(MM, Created),DATEPART(YYYY, Created)

  --  print  ((DATENAME(MM,@tmpDate)+ Datename(YYYY,@tmpDate)))

END

    Else

              INSERT INTO @months VALUES(@tmpDate, 0,0,0,0)

                --print  ((DATENAME(MM,@tmpDate)+ Datename(YYYY,@tmpDate)))

          IF(MONTH(@tmpDate)= MONTH(dbo.GetCompanyDateTime()) and YEAR(@tmpDate)= YEAR(dbo.GetCompanyDateTime()))

        -- if(MONTH(@tmpDate) + 1 > 12)

              BREAK;

      SET @tmpDate = DATEADD(MM, 1, @tmpDate);

  END

END

SELECT * FROM @months

END

END