iVend Database Database : InvGetAllSelectedAttributeItemsList 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

@pWarehouseKey

In

VarWChar

50

@pXAttributeKey

In

VarWChar

50

@pYAttributeKey

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

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

2

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

3

InvLocation

Table

Save the location infomation.

3

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

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

2

vw_InvInventoryItem

View

2

vw_InvProductInventoryInfo

View

1

  --EXEC InvGetAllSelectedAttributeItems '3893121030',10000000000024

CREATE PROCEDURE [dbo].[InvGetAllSelectedAttributeItemsList]

(

@pHardwareId NVARCHAR(200),

@pProductKey nvarchar(50),

@pWarehouseKey nvarchar(50),

@pXAttributeKey nvarchar(50),

@pYAttributeKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @lRowNumber INT,

  @lAttributeKey nvarchar(50),

  @lAttributeDetailKey nvarchar(50),

  @lCounter INT,

  @lProductKey nvarchar(50),

  @lSelectedAttributeDetailKey nvarchar(50)--,

  --@lIsTwoDimensionalMatrixItem BIT

--SELECT @lIsTwoDimensionalMatrixItem = IsTwoDimensionalMatrixItem

--FROM InvProduct Where ProductKey = @pProductKey

--PRINT @lIsTwoDimensionalMatrixItem

CREATE TABLE #TempMatrix

(

 ProductKey nvarchar(50),

 RowNumber INT,

 ParentProductKey nvarchar(50)

)

CREATE TABLE #TempMatrixAttributes

(

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

[ProductKey] nvarchar(50) NOT NULL,

[AttributeDetailKey] nvarchar(50) NOT NULL,

[Id] nvarchar(Max) NOT NULL,

[FinalString] nvarchar(Max) NULL,

[Description] nvarchar(Max) NULL,

[CombinationKey] nvarchar(Max) NULL,

[RowNumber] INT NOT NULL,

[ParentProductKey] nvarchar(50) NOT NULL,

)

CREATE TABLE #TempMatrixAttributesA

(

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

[ProductKey] nvarchar(50) NOT NULL,

[AttributeDetailKey] nvarchar(50) NOT NULL,

[Id] nvarchar(Max) NOT NULL,

[FinalString] nvarchar(Max) NULL,

[Description] nvarchar(Max) NULL,

[CombinationKey] nvarchar(Max) NULL,

[RowNumber] INT NOT NULL,

[ParentProductKey] nvarchar(50) NOT NULL

)

CREATE TABLE #TempMatrixAttributesB

(

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

[ProductKey] nvarchar(50) NOT NULL,

[AttributeDetailKey] nvarchar(50) NOT NULL,

[Id] nvarchar(Max) NOT NULL,

[FinalString] nvarchar(Max) NULL,

[Description] nvarchar(Max) NULL,

[CombinationKey] nvarchar(Max) NULL,

[RowNumber] INT NOT NULL,

[ParentProductKey] nvarchar(50) NOT NULL

)

DECLARE RowCursor CURSOR FOR

SELECT distinct ProductKey

FROM InvProduct

WHERE InvProduct.IsMatrixItem = 1

And InvProduct.IsMatrixChildItem = 0

And InvProduct.ParentMartixProductKey = '0'

AND InvProduct.ProductKey = CASE WHEN @pProductKey = '0' THEN InvProduct.ProductKey ELSE @pProductKey END

--ORDER BY RowNumber

OPEN RowCursor

FETCH NEXT FROM RowCursor INTO @lProductKey

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 = @lProductKey

  AND A.AttributeDetailKey = B.AttributeDetailKey

  AND C.AttributeKey = B.AttributeKey

  --AND C.IsTwoDimensional =  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, 0, A.ParentMartixProductKey

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

      Where A.ParentMartixProductKey = @lProductKey

      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 C.AttributeKey = @lAttributeKey

      --AND D.RowNumber = @lRowNumber

      --AND D.HardwareId  = @pHardwareId

      AND C.IsActive = 1

      --SELECT * FROM #TempMatrix

    END

    ELSE

    BEGIN

      SELECT @lSelectedAttributeDetailKey = AttributeDetailKey

      FROM InvTempSelectedAttributeList A

      WHERE A.RowNumber = 0

      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 = @lProductKey

        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 @lProductKey

END

CLOSE RowCursor

DEALLOCATE RowCursor

--SELECT * FROM #TempMatrix

--IF @lIsTwoDimensionalMatrixItem = 'TRUE'

  BEGIN

  --SELECT * FROM #TempMatrix

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

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

  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 D.AttributeKey != CASE WHEN @pXAttributeKey != @pYAttributeKey then @pXAttributeKey ELSE '-1' END

  AND C.IsActive = 1

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

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

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

  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 D.AttributeKey != CASE WHEN @pXAttributeKey != @pYAttributeKey then @pXAttributeKey ELSE '-1' END

  AND C.IsActive = 1

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

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

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

  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 D.AttributeKey = @pXAttributeKey

  AND C.IsActive = 1

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

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

  SELECT ProductKey, AttributeDetailKey,Id, Description, CombinationKey, RowNumber, ParentProductKey

  FROM #TempMatrixAttributesA

  ORDER BY ProductKey, MatrixAttributeKey

  --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  A.Id, A.Description, A.CombinationKey, A.ParentProductKey, B.Id 'ParentProductId', B.Description 'ParentDescription', F.Description As YAttribute

  FROM #TempMatrixAttributes A, InvProduct B, InvProductAttribute E, InvAttributeDetail F

  WHERE MatrixAttributeKey IN

   (

    SELECT MAX(MatrixAttributeKey)

    FROM #TempMatrixAttributes

    GROUP BY ProductKey, RowNumber

   )

  AND A.ParentProductKey = B.ProductKey

  AND A.ProductKey = E.ProductKey

  And E.AttributeDetailKey = F.AttributeDetailKey

  And f.AttributeKey = @pYAttributeKey

  ORDER BY YAttribute

  declare @aID NVARCHAR(4000),

      @aLastProductKey nvarchar(50),

      @aDescription NVARCHAR(4000),

      @aCombinationKey NVARCHAR(4000),

      @aLastRowNumber INT

  SELECT @aID = '', @aLastProductKey = '0', @aLastRowNumber = 0

  UPDATE  #TempMatrixAttributesB

  SET @aID = (CASE WHEN (@aLastProductKey = ProductKey AND @aLastRowNumber = RowNumber) THEN @aID + '~' + Id ELSE Id END),

    @aDescription = (CASE WHEN (@aLastProductKey = ProductKey AND @aLastRowNumber = RowNumber) THEN @aDescription + '~' + Description ELSE Description END),

    @aCombinationKey = (CASE WHEN (@aLastProductKey = ProductKey AND @aLastRowNumber = RowNumber) THEN @aCombinationKey + '~' + CombinationKey ELSE CombinationKey END),

    @aLastProductKey = ProductKey,

    @aLastRowNumber = RowNumber,

    Id = @aID,

    Description = @aDescription,

    CombinationKey = @aCombinationKey

  --DELETE FROM #TempMatrixAttributesB

  SELECT DISTINCT  A.Id, A.Description, CombinationKey, A.ProductKey, B.Id ProductId, H.AvailableQuantity,

    C.Price, A.ParentProductKey, D.Id 'ParentProductId',

    B.Description ProductDescription , D.Description 'ParentDescription', F.Description As YAttribute, C.InventoryItemKey, G.ProductCost

  FROM #TempMatrixAttributesB A, InvProduct B, InvInventoryItem C, InvProduct D, InvProductAttribute E, InvAttributeDetail F , vw_ProductCost G , vw_InvInventoryItem H

  WHERE MatrixAttributeKey IN

   (

    SELECT MAX(MatrixAttributeKey)

    FROM #TempMatrixAttributesB

    GROUP BY ProductKey, RowNumber

   )

  And A.ProductKey = B.ProductKey

  And B.ProductKey = C.ProductKey

  And C.WarehouseKey = @pWarehouseKey

  And A.ParentProductKey = D.ProductKey

  And B.ProductKey = E.ProductKey

  And E.AttributeDetailKey = F.AttributeDetailKey

  And F.AttributeKey = @pYAttributeKey

  AND G.ProductKey = A.ProductKey

  AND H.InventoryItemKey = C.InventoryItemKey

  END

  Select * from vw_InvProductInventoryInfo vw

  Where  vw.ParentMartixProductKey = @pProductKey

  AND vw.WarehouseKey = @pWarehouseKey

--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