iVend Database Database : InvGetAllSelectedAttributeItems Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pHardwareId

In

VarWChar

200

@pProductKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvAttributeDetail

Table

Stores the details of attrbutes defined for a matrix product

1

InvAttributeMaster

Table

Stores the attrbutes information for a matrix product

1

InvProduct

Table

Stores the product related details.

1

InvProductAttribute

Table

Defines the attribute information assigned to a product

1

InvTempSelectedAttributeList

Table

Specifies the selected Temp attribute list for products

1

--EXEC InvGetAllSelectedAttributeItems '3893121030',10000000000024

CREATE PROCEDURE [dbo].[InvGetAllSelectedAttributeItems]

(

@pHardwareId NVARCHAR(200),

@pProductKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lRowNumber INT,

  @lAttributeKey nvarchar(50),

  @lAttributeDetailKey nvarchar(50),

  @lCounter INT,

  @lSelectedAttributeDetailKey nvarchar(50),

  @lIsTwoDimensionalMatrixItem BIT

SELECT @lIsTwoDimensionalMatrixItem = IsTwoDimensionalMatrixItem

FROM InvProduct Where ProductKey = @pProductKey

--PRINT @lIsTwoDimensionalMatrixItem

CREATE TABLE #TempMatrix

(

 ProductKey nvarchar(50),

 RowNumber INT

)

CREATE TABLE #TempMatrixAttributes

(

[MatrixAttributeKey] [bigint] IDENTITY(1,1) NOT NULL,

[ProductKey] nvarchar(50) NOT NULL,

[AttributeDetailKey] nvarchar(50) NOT NULL,

[Id] nvarchar(20) NOT NULL,

[FinalString] nvarchar(4000) NULL,

[Description] nvarchar(4000) NULL,

[CombinationKey] nvarchar(4000) NULL,

[RowNumber] INT NOT NULL

)

DECLARE RowCursor CURSOR FOR

SELECT distinct RowNumber

FROM InvTempSelectedAttributeList

WHERE HardwareId = @pHardwareId

ORDER BY RowNumber

OPEN RowCursor

FETCH NEXT FROM RowCursor INTO @lRowNumber

WHILE @@FETCH_STATUS = 0

BEGIN

  SET @lCounter = 0

  DECLARE AttributeCursor CURSOR FOR

  SELECT distinct C.AttributeKey

  FROM InvProductAttribute A, InvAttributeDetail B, InvAttributeMaster C

  WHERE ProductKey = @pProductKey

  AND A.AttributeDetailKey = B.AttributeDetailKey

  AND C.AttributeKey = B.AttributeKey

  AND C.IsTwoDimensional = CASE WHEN @lIsTwoDimensionalMatrixItem = 'FALSE' THEN C.IsTwoDimensional ELSE 'FALSE' END

  AND B.IsActive = 1

  OPEN AttributeCursor

  FETCH NEXT FROM AttributeCursor INTO @lAttributeKey

  WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @lCounter = @lCounter + 1

    IF @lCounter = 1

    BEGIN

      INSERT INTO #TempMatrix

      SELECT DISTINCT A.ProductKey, @lRowNumber

      FROM InvProduct A, InvProductAttribute B, InvAttributeDetail C, InvTempSelectedAttributeList D

      Where A.ParentMartixProductKey = @pProductKey

      AND A.ProductKey = B.ProductKey

      AND B.AttributeDetailKey = C.AttributeDetailKey

      AND C.AttributeKey = @lAttributeKey

      AND B.AttributeDetailKey = CASE WHEN D.AttributeDetailKey = '0' THEN B.AttributeDetailKey ELSE D.AttributeDetailKey END

      AND D.AttributeKey = @lAttributeKey

      AND D.RowNumber = @lRowNumber

      AND D.HardwareId = @pHardwareId

      AND C.IsActive = 1

    END

    ELSE

    BEGIN

      SELECT @lSelectedAttributeDetailKey = AttributeDetailKey

      FROM InvTempSelectedAttributeList A

      WHERE A.RowNumber = @lRowNumber

      AND A.AttributeKey = @lAttributeKey

      AND A.HardwareId = @pHardwareId

      --SELECT  @lSelectedAttributeDetailKey , @lAttributeKey

      IF @lSelectedAttributeDetailKey != '0'

      BEGIN

        DELETE FROM #TempMatrix

        WHERE ProductKey IN

        (

        SELECT DISTINCT A.ProductKey FROM InvProduct A, InvProductAttribute B, InvAttributeDetail C

        Where A.ParentMartixProductKey = @pProductKey

        AND B.AttributeDetailKey = C.AttributeDetailKey

        AND C.AttributeKey = @lAttributeKey

        AND A.ProductKey = B.ProductKey

        AND C.IsActive = 1

        AND B.AttributeDetailKey NOT IN(@lSelectedAttributeDetailKey)

        )

        AND RowNumber = @lRowNumber

      END

    END

    FETCH NEXT FROM AttributeCursor INTO @lAttributeKey

  END

  CLOSE AttributeCursor

  DEALLOCATE AttributeCursor

FETCH NEXT FROM RowCursor INTO @lRowNumber

END

CLOSE RowCursor

DEALLOCATE RowCursor

--SELECT * FROM #TempMatrix

IF @lIsTwoDimensionalMatrixItem = 'TRUE'

  BEGIN

  --SELECT * FROM #TempMatrix

  INSERT INTO #TempMatrixAttributes(ProductKey, AttributeDetailKey,Id, Description, CombinationKey, RowNumber)

  SELECT A.ProductKey, C.AttributeDetailKey, C.Id, C.Description, C.AttributeDetailKey, RowNumber

  FROM #TempMatrix A, InvProductAttribute B, InvAttributeDetail C, InvAttributeMaster D

  Where A.ProductKey = B.ProductKey

  AND B.AttributeDetailKey = C.AttributeDetailKey

  AND C.AttributeKey = D.AttributeKey

  AND D.IsTwoDimensional = 'FALSE'

  AND C.IsActive = 1

  ORDER BY A.ProductKey, A.RowNumber, C.AttributeDetailKey

  --SELECT * FROM #TempMatrixAttributes

  declare @ID NVARCHAR(4000),

      @LastProductKey nvarchar(50),

      @Description NVARCHAR(4000),

      @CombinationKey NVARCHAR(4000),

      @LastRowNumber INT

  SELECT @ID = '', @LastProductKey = '0', @LastRowNumber = 0

  UPDATE  #TempMatrixAttributes

  SET @ID = (CASE WHEN (@LastProductKey = ProductKey AND @LastRowNumber = RowNumber) THEN @ID + '~' + Id ELSE Id END),

    @Description = (CASE WHEN (@LastProductKey = ProductKey AND @LastRowNumber = RowNumber) THEN @Description + '~' + Description ELSE Description END),

    @CombinationKey = (CASE WHEN (@LastProductKey = ProductKey AND @LastRowNumber = RowNumber) THEN @CombinationKey + '~' + CombinationKey ELSE CombinationKey END),

    @LastProductKey = ProductKey,

    @LastRowNumber = RowNumber,

    Id = @ID,

    Description = @Description,

    CombinationKey = @CombinationKey

  SELECT DISTINCT  Id, Description, CombinationKey

  FROM #TempMatrixAttributes

  WHERE MatrixAttributeKey IN

   (

    SELECT MAX(MatrixAttributeKey)

    FROM #TempMatrixAttributes

    GROUP BY ProductKey, RowNumber

   )

  END

ELSE

  BEGIN

  SELECT DISTINCT A.ProductKey, B.Id, B.Description, CONVERT(decimal(20,5), 0) Quantity

  FROM #TempMatrix A, InvProduct B

  WHERE A.ProductKey = B.ProductKey

  END

END