-- vd20170328: New table for multiple EANs on customer
-- WIKI definition: International Article Number (EAN)


DECLARE @SQL VARCHAR(2000)

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WITH (NOLOCK) 
               WHERE 
                 id = object_id(N'[dbo].[CustomerEANS]') AND 
                 OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

  CREATE TABLE dbo.CustomerEANS(
    CustomerNumber INTEGER NOT NULL,
    EANType TINYINT NOT NULL,
    EAN VARCHAR(20) NOT NULL,
	EANDescription varchar(200) NULL 
	)

  ALTER TABLE CustomerEANS ADD CONSTRAINT DF_CustomerEANS_EANType
  DEFAULT 0 FOR EANType

  ALTER TABLE CustomerEANS ADD CONSTRAINT PK_CustomerEANS
  PRIMARY KEY (CustomerNumber, EANType)

  ALTER TABLE CustomerEANS ADD CONSTRAINT FK_CustomerEANS_CustomerFKey
  FOREIGN KEY (CustomerNumber) REFERENCES Customer (CustomerNumber)
  
  SET @SQL = 'DECLARE @CustomerNumber INTEGER
  DECLARE @EAN VARCHAR(20)
  
  DECLARE #ConversionCursor Cursor FOR
  SELECT CustomerNumber, CompanyForm FROM Customer WITH (NOLOCK)
  
  OPEN #ConversionCursor
  FETCH NEXT FROM #ConversionCursor INTO @CustomerNumber, @EAN
  
  WHILE @@Fetch_status = 0
  BEGIN
    IF RTRIM(LTRIM(ISNULL (@EAN, ''''))) <> ''''
	BEGIN
      INSERT INTO CustomerEANS (CustomerNumber, EAN) VALUES (@CustomerNumber, @EAN)
	END
    FETCH NEXT FROM #ConversionCursor INTO @CustomerNumber, @EAN
  END

  CLOSE #ConversionCursor
  DEALLOCATE #ConversionCursor'
  EXEC (@SQL)

  --EXEC sp_RENAME 'Customer.CompanyForm' , 'zzzCompanyForm', 'COLUMN'

END
GO
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'EANDescription'
    WHERE SysObjects.Name = 'CustomerEANS'
)
BEGIN
	ALTER TABLE dbo.CustomerEANS ADD EANDescription varchar(200) NULL 
END
GO
