------------------------------------------------------------------
--
-- QESTMix Mix Materials View
--
--
-- Author: Lief Martin
-- Version: 1.1
-- Date Modified: 17/08/2007
-- Products: QESTLab
--
-- Requirements:
--   1. This view is dependent on the view 'QESTMix_Mixes', which is created
--      using the script 'QESTMix_Mixes_View.sql'.  Version 2.0 is the minimum
--      version of the script that includes the required fields.
--   2. This view requires SQL Server 2005 - in addition, both the QESTLab and
--      QESTMix databases should have their compatibility levels set to 90.
--      - THIS NEEDS TESTING - Although it should work fine with QESTLab/Mix, 
--        it has the potential to break other things if they need a lower 
--        compatibility level.
--
-- Description:
--   This script creates a view in QESTLab similar to the QESTMix product master
--   report, which can be used with QESTLab's management reporting engine.
--
-- IMPORTANT: You need to replace the text "[QESTMix].[dbo]" with
--   the correct database name.  the name "QESTMix" is usually
--   correct, however, if it is different, you need to edit this
--   script before running it.
--
-- Change Log:
--  1.0 Initial Version (10/8/07)
--  1.1 Implemented complex material sorting (17/08/07)
--
------------------------------------------------------------------

--Generate the function to return the material weights for a mix on a single row (ouch)
print 'Creating function: [dbo].getmaterialdata';
IF OBJECT_ID ( '[dbo].getmaterialdata', 'TF' ) IS NOT NULL 
  DROP FUNCTION [dbo].getmaterialdata;
GO

CREATE FUNCTION [dbo].getmaterialdata(@GroupCode nvarchar(50), @GroupVersion int, @ProductCode nvarchar(50), @ProductVersion int, @DPRuid int)
RETURNS @material_data TABLE 
(
    MaterialCode1  nvarchar(20), MaterialType1  int, MaterialSSDWeight1  real, MaterialBatchWeight1  real
  , MaterialCode2  nvarchar(20), MaterialType2  int, MaterialSSDWeight2  real, MaterialBatchWeight2  real
  , MaterialCode3  nvarchar(20), MaterialType3  int, MaterialSSDWeight3  real, MaterialBatchWeight3  real
  , MaterialCode4  nvarchar(20), MaterialType4  int, MaterialSSDWeight4  real, MaterialBatchWeight4  real
  , MaterialCode5  nvarchar(20), MaterialType5  int, MaterialSSDWeight5  real, MaterialBatchWeight5  real
  , MaterialCode6  nvarchar(20), MaterialType6  int, MaterialSSDWeight6  real, MaterialBatchWeight6  real
  , MaterialCode7  nvarchar(20), MaterialType7  int, MaterialSSDWeight7  real, MaterialBatchWeight7  real
  , MaterialCode8  nvarchar(20), MaterialType8  int, MaterialSSDWeight8  real, MaterialBatchWeight8  real
  , MaterialCode9  nvarchar(20), MaterialType9  int, MaterialSSDWeight9  real, MaterialBatchWeight9  real
  , MaterialCode10 nvarchar(20), MaterialType10 int, MaterialSSDWeight10 real, MaterialBatchWeight10 real
  , MaterialCode11 nvarchar(20), MaterialType11 int, MaterialSSDWeight11 real, MaterialBatchWeight11 real
  , MaterialCode12 nvarchar(20), MaterialType12 int, MaterialSSDWeight12 real, MaterialBatchWeight12 real
  , MaterialCode13 nvarchar(20), MaterialType13 int, MaterialSSDWeight13 real, MaterialBatchWeight13 real
  , MaterialCode14 nvarchar(20), MaterialType14 int, MaterialSSDWeight14 real, MaterialBatchWeight14 real
  , MaterialCode15 nvarchar(20), MaterialType15 int, MaterialSSDWeight15 real, MaterialBatchWeight15 real
  , MaterialOverflow bit
)
AS
BEGIN

  DECLARE @mat1  nvarchar(20), @type1  int, @ssd1  real, @batch1  real;
  DECLARE @mat2  nvarchar(20), @type2  int, @ssd2  real, @batch2  real;
  DECLARE @mat3  nvarchar(20), @type3  int, @ssd3  real, @batch3  real;
  DECLARE @mat4  nvarchar(20), @type4  int, @ssd4  real, @batch4  real;
  DECLARE @mat5  nvarchar(20), @type5  int, @ssd5  real, @batch5  real;
  DECLARE @mat6  nvarchar(20), @type6  int, @ssd6  real, @batch6  real;
  DECLARE @mat7  nvarchar(20), @type7  int, @ssd7  real, @batch7  real;
  DECLARE @mat8  nvarchar(20), @type8  int, @ssd8  real, @batch8  real;
  DECLARE @mat9  nvarchar(20), @type9  int, @ssd9  real, @batch9  real;
  DECLARE @mat10 nvarchar(20), @type10 int, @ssd10 real, @batch10 real;
  DECLARE @mat11 nvarchar(20), @type11 int, @ssd11 real, @batch11 real;
  DECLARE @mat12 nvarchar(20), @type12 int, @ssd12 real, @batch12 real;
  DECLARE @mat13 nvarchar(20), @type13 int, @ssd13 real, @batch13 real;
  DECLARE @mat14 nvarchar(20), @type14 int, @ssd14 real, @batch14 real;
  DECLARE @mat15 nvarchar(20), @type15 int, @ssd15 real, @batch15 real;
  DECLARE @err bit;
  SET @err = 0

  DECLARE curMaterials CURSOR LOCAL FAST_FORWARD FOR
    SELECT ProductMaterialWeights.MaterialCode, Materials.[Type], ProductMaterialWeights.SSDWeight, ProductMaterialWeights.BatchWeight 
    FROM [QESTMix].[dbo].ProductMaterialWeights AS [ProductMaterialWeights]
    INNER JOIN [QESTMix].[dbo].Materials AS [Materials] ON [ProductMaterialWeights].MaterialCode = [Materials].Code
    WHERE ProductMaterialWeights.GroupCode = @GroupCode AND ProductMaterialWeights.GroupVersion = @GroupVersion AND ProductMaterialWeights.ProductCode = @ProductCode AND ProductMaterialWeights.MixVersion = @ProductVersion AND ProductMaterialWeights.DesignProductRelationUniqueID = @DPRuid
    ORDER BY [Materials].[Type], [Materials].AggSize ASC, [Materials].Code ASC
  
    OPEN curMaterials;
  
  DECLARE @mat nvarchar(20), @type int, @ssd real, @batch real;
  DECLARE @base int, @extra int, @col int;
  
  SET @base = 1;
  SET @extra = 10;
  SET @col = 0;
  FETCH NEXT FROM curMaterials INTO @mat,  @type,  @ssd,  @batch
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF (@type = 4 AND @base < 3) SET @base = 3
    ELSE IF (@type = 5 AND @base < 5) SET @base = 5
    ELSE IF (@type IN (8,9,10) AND @base < 7) SET @base = 7
    
    IF (    ((@base IN (1,2)) AND (@type IN (1,2,3)))
         OR ((@base IN (3,4)) AND (@type = 4))
         OR ((@base IN (5,6)) AND (@type = 5))
         OR ((@base IN (7,8,9)) AND (@type IN (8,9,10)))
       )
      BEGIN
        SET @col = @base
        SET @base = @base + 1;
      END
    ELSE
      BEGIN
        SET @col = @extra;
        SET @extra = @extra + 1;
      END

    IF @col = 1
      BEGIN
        SET   @mat1   = @mat;
        SET  @type1   = @type;
        SET   @ssd1   = @ssd;
        SET @batch1   = @batch;
      END
    ELSE IF @col = 2
      BEGIN
        SET   @mat2   = @mat;
        SET  @type2   = @type;
        SET   @ssd2   = @ssd;
        SET @batch2   = @batch;
      END
    ELSE IF @col = 3
      BEGIN
        SET   @mat3   = @mat;
        SET  @type3   = @type;
        SET   @ssd3   = @ssd;
        SET @batch3   = @batch;
      END
    ELSE IF @col = 4
      BEGIN
        SET   @mat4   = @mat;
        SET  @type4   = @type;
        SET   @ssd4   = @ssd;
        SET @batch4   = @batch;
      END
    ELSE IF @col = 5
      BEGIN
        SET   @mat5   = @mat;
        SET  @type5   = @type;
        SET   @ssd5   = @ssd;
        SET @batch5   = @batch;
      END
    ELSE IF @col = 6
      BEGIN
        SET   @mat6   = @mat;
        SET  @type6   = @type;
        SET   @ssd6   = @ssd;
        SET @batch6   = @batch;
      END
    ELSE IF @col = 7
      BEGIN
        SET   @mat7   = @mat;
        SET  @type7   = @type;
        SET   @ssd7   = @ssd;
        SET @batch7   = @batch;
      END
    ELSE IF @col = 8
      BEGIN
        SET   @mat8   = @mat;
        SET  @type8   = @type;
        SET   @ssd8   = @ssd;
        SET @batch8   = @batch;
      END
    ELSE IF @col = 9
      BEGIN
        SET   @mat9   = @mat;
        SET  @type9   = @type;
        SET   @ssd9   = @ssd;
        SET @batch9   = @batch;
      END
    ELSE IF @col = 10
      BEGIN
        SET   @mat10   = @mat;
        SET  @type10   = @type;
        SET   @ssd10   = @ssd;
        SET @batch10   = @batch;
      END
    ELSE IF @col = 11
      BEGIN
        SET   @mat11   = @mat;
        SET  @type11   = @type;
        SET   @ssd11   = @ssd;
        SET @batch11   = @batch;
      END
    ELSE IF @col = 12
      BEGIN
        SET   @mat12   = @mat;
        SET  @type12   = @type;
        SET   @ssd12   = @ssd;
        SET @batch12   = @batch;
      END
    ELSE IF @col = 13
      BEGIN
        SET   @mat13   = @mat;
        SET  @type13   = @type;
        SET   @ssd13   = @ssd;
        SET @batch13   = @batch;
      END
    ELSE IF @col = 14
      BEGIN
        SET   @mat14   = @mat;
        SET  @type14   = @type;
        SET   @ssd14   = @ssd;
        SET @batch14   = @batch;
      END
    ELSE IF @col = 15
      BEGIN
        SET   @mat15   = @mat;
        SET  @type15   = @type;
        SET   @ssd15   = @ssd;
        SET @batch15   = @batch;
      END
    ELSE
      BEGIN
        SET @err = 1;
      END
    
    FETCH NEXT FROM curMaterials INTO @mat,  @type,  @ssd,  @batch
  END  
  
  CLOSE curMaterials;
  DEALLOCATE curMaterials;

  INSERT INTO @material_data VALUES (
      @mat1,  @type1,  @ssd1,  @batch1 
    , @mat2,  @type2,  @ssd2,  @batch2 
    , @mat3,  @type3,  @ssd3,  @batch3 
    , @mat4,  @type4,  @ssd4,  @batch4 
    , @mat5,  @type5,  @ssd5,  @batch5 
    , @mat6,  @type6,  @ssd6,  @batch6 
    , @mat7,  @type7,  @ssd7,  @batch7 
    , @mat8,  @type8,  @ssd8,  @batch8 
    , @mat9,  @type9,  @ssd9,  @batch9 
    , @mat10, @type10, @ssd10, @batch10
    , @mat11, @type11, @ssd11, @batch11
    , @mat12, @type12, @ssd12, @batch12
    , @mat13, @type13, @ssd13, @batch13
    , @mat14, @type14, @ssd14, @batch14
    , @mat15, @type15, @ssd15, @batch15
    , @err
  )

  RETURN 
END

GO
print '----';
GO

--Ensure DB compatibility level set to 90 (to allow the CROSS APPLY in the view)
print 'Setting compatibility to SQL 2005 (90)';
GO
sp_dbcmptlevel [QESTLab], 90
GO
sp_dbcmptlevel [QESTMix], 90
GO
print '----';
GO

--Generate the view (simple now that we have that fancy function)
print 'Creating view: QESTMix_Mixes_Materials';
IF OBJECT_ID ('QESTMix_Mixes_Materials', 'view') IS NOT NULL
  DROP VIEW QESTMix_Mixes_Materials;
GO

CREATE VIEW QESTMix_Mixes_Materials
AS
SELECT * FROM [QESTMix_Mixes] AS [ProductMixes]
  CROSS APPLY [dbo].getmaterialdata(ProductMixes.ProductGroupCode, ProductMixes.ProductGroupVersion, ProductMixes.ProductCode, ProductMixes.ProductVersion, ProductMixes.DesignProductRelationUniqueID) AS MaterialData

GO
print '----';
GO