-- vd20141217: New table linking customers and allotments

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

  CREATE TABLE CustomerAllotmentsXTab(
    CustomerNumber INTEGER NOT NULL,
    HotelNumber INTEGER NOT NULL,
    AllotmentNumber INTEGER NOT NULL)
	 
  ALTER TABLE CustomerAllotmentsXTab
  ADD CONSTRAINT PK_CustomerAllotmentsXTab PRIMARY KEY 
 (CustomerNumber, HotelNumber, AllotmentNumber)

  ALTER TABLE CustomerAllotmentsXTab ADD CONSTRAINT FK_CustomerAllotmentsXTab_CustomerFieldsPerHotelFK 
  FOREIGN KEY (CustomerNumber, HotelNumber)
  REFERENCES CustomerFieldsPerHotel(CustomerNumber, HotelNumber)

  /*
  vd20150702 To do: Replace FK_CustomerAllotmentsXTab_CustomerFieldsPerHotelFK with something like this:

 ALTER TABLE CustomerAllotmentsXTab ADD CONSTRAINT FK_CustomerAllotmentsXTab_Customer11 
 FOREIGN KEY (CustomerNumber)
 REFERENCES Customer(CustomerNumber)

 ALTER TABLE CustomerAllotmentsXTab ADD CONSTRAINT FK_CustomerAllotmentsXTab_Customer22
 FOREIGN KEY (HotelNumber)
 REFERENCES Customer(HotelNumber)

 Cannot be done now. No index on Customer(HotelNumber) so far
  */

  ALTER TABLE CustomerAllotmentsXTab ADD CONSTRAINT FK_CustomerAllotmentsXTab_AllotmentsFK 
  FOREIGN KEY (AllotmentNumber)
  REFERENCES Allotments(AllotmentNumber)
 
  DECLARE @CustomerNumber INTEGER
  DECLARE @HotelNumber INTEGER
  DECLARE @AllotmentNumber1 INTEGER
  DECLARE @AllotmentNumber2 INTEGER
  DECLARE @AllotmentNumber3 INTEGER
  DECLARE @AllotmentNumber4 INTEGER
  
  DECLARE ConversionCursor Cursor FOR
    SELECT 
      CustomerNumber, 
      HotelNumber,
      AllotmentNumber1, 
      AllotmentNumber2, 
      AllotmentNumber3, 
      AllotmentNumber4
    FROM CustomerFieldsPerHotel WITH (NOLOCK)
    WHERE 
      ISNULL(AllotmentNumber1, 0) +
      ISNULL(AllotmentNumber2, 0) +
      ISNULL(AllotmentNumber3, 0) +
      ISNULL(AllotmentNumber4, 0) > 0
  
  OPEN ConversionCursor
  FETCH NEXT FROM ConversionCursor 
  INTO 
    @CustomerNumber,
    @HotelNumber,
    @AllotmentNumber1,
    @AllotmentNumber2,
    @AllotmentNumber3,
    @AllotmentNumber4
  
  WHILE @@Fetch_status = 0
  BEGIN
    IF ISNULL(@AllotmentNumber1, 0) > 0 
    BEGIN
      INSERT INTO CustomerAllotmentsXTab (CustomerNumber, HotelNumber, AllotmentNumber)
        VALUES (@CustomerNumber, @HotelNumber, @AllotmentNumber1)
    END	 
    IF ISNULL(@AllotmentNumber2, 0) > 0 AND 
       ISNULL(@AllotmentNumber2, 0) <> ISNULL(@AllotmentNumber1, 0)
    BEGIN
      INSERT INTO CustomerAllotmentsXTab (CustomerNumber, HotelNumber, AllotmentNumber)
        VALUES (@CustomerNumber, @HotelNumber, @AllotmentNumber2)
    END
    IF ISNULL(@AllotmentNumber3, 0) > 0 AND
       ISNULL(@AllotmentNumber3, 0) <> ISNULL(@AllotmentNumber1, 0) AND 
       ISNULL(@AllotmentNumber3, 0) <> ISNULL(@AllotmentNumber2, 0)
    BEGIN
      INSERT INTO CustomerAllotmentsXTab (CustomerNumber, HotelNumber, AllotmentNumber)
        VALUES (@CustomerNumber, @HotelNumber, @AllotmentNumber3)
    END
    IF ISNULL(@AllotmentNumber4, 0) > 0 AND
       ISNULL(@AllotmentNumber4, 0) <> ISNULL(@AllotmentNumber1, 0) AND 
       ISNULL(@AllotmentNumber4, 0) <> ISNULL(@AllotmentNumber2, 0) AND 
       ISNULL(@AllotmentNumber4, 0) <> ISNULL(@AllotmentNumber3, 0) 
    BEGIN
      INSERT INTO CustomerAllotmentsXTab (CustomerNumber, HotelNumber, AllotmentNumber)
        VALUES (@CustomerNumber, @HotelNumber, @AllotmentNumber4)
    END
	
  FETCH NEXT FROM ConversionCursor 
  INTO 
    @CustomerNumber,
    @HotelNumber,
    @AllotmentNumber1,
    @AllotmentNumber2,
    @AllotmentNumber3,
    @AllotmentNumber4
  END

  CLOSE ConversionCursor
  DEALLOCATE ConversionCursor  

  DELETE FROM CustomerAllotmentsXTab
  WHERE AllotmentNumber NOT IN (SELECT AllotmentNumber
                                FROM Allotments WITH (NOLOCK)
                                WHERE ISNULL(MarkAsDeleted, 0) = 0) 
								
         OR AllotmentNumber IN (SELECT AllotmentNumber
                                FROM Allotments WITH (NOLOCK)
                                WHERE ISNULL(ENDDATE, GETDATE()) < GETDATE())									
								
  IF EXISTS (SELECT * FROM sys.foreign_keys 
             WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerFieldsPerHotel_ALLOTMENTS')
               AND parent_object_id = OBJECT_ID(N'dbo.CustomerFieldsPerHotel'))
    ALTER TABLE CustomerFieldsPerHotel DROP CONSTRAINT FK_CustomerFieldsPerHotel_ALLOTMENTS

  IF EXISTS (SELECT * FROM sys.foreign_keys 
             WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerFieldsPerHotel_ALLOTMENTS1')
               AND parent_object_id = OBJECT_ID(N'dbo.CustomerFieldsPerHotel'))
    ALTER TABLE CustomerFieldsPerHotel DROP CONSTRAINT FK_CustomerFieldsPerHotel_ALLOTMENTS1

  IF EXISTS (SELECT * FROM sys.foreign_keys 
             WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerFieldsPerHotel_ALLOTMENTS2')
               AND parent_object_id = OBJECT_ID(N'dbo.CustomerFieldsPerHotel'))
    ALTER TABLE CustomerFieldsPerHotel DROP CONSTRAINT FK_CustomerFieldsPerHotel_ALLOTMENTS2

   IF EXISTS (SELECT * FROM sys.foreign_keys 
              WHERE object_id = OBJECT_ID(N'dbo.FK_CustomerFieldsPerHotel_ALLOTMENTS3')
               AND parent_object_id = OBJECT_ID(N'dbo.CustomerFieldsPerHotel'))
    ALTER TABLE CustomerFieldsPerHotel DROP CONSTRAINT FK_CustomerFieldsPerHotel_ALLOTMENTS3   
	                 
  EXEC sp_RENAME 'CustomerFieldsPerHotel.AllotmentNumber1' , 'zzzAllotmentNumber1', 'COLUMN'
  EXEC sp_RENAME 'CustomerFieldsPerHotel.AllotmentNumber2' , 'zzzAllotmentNumber2', 'COLUMN'
  EXEC sp_RENAME 'CustomerFieldsPerHotel.AllotmentNumber3' , 'zzzAllotmentNumber3', 'COLUMN'
  EXEC sp_RENAME 'CustomerFieldsPerHotel.AllotmentNumber4' , 'zzzAllotmentNumber4', 'COLUMN'

END
