IF NOT EXISTS(SELECT * From Color (NOLOCK))
begin
	INSERT INTO ColorRGB ( Blue, Green, Red )
	VALUES(0, 128, 0)
	INSERT INTO Color (ColorName, AutoDayColor, YieldFilterColor, SortOrder, SetupSortorder, FK_ColorRGBID)
	VALUES ('Green', 1, 1, 1, 3, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(0, 255, 255)
	INSERT INTO Color (ColorName, AutoDayColor, YieldFilterColor, SortOrder, SetupSortorder, FK_ColorRGBID)
	VALUES ('Yellow',  1, 1, 2, 2, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(0, 0, 255)	
	INSERT INTO Color (ColorName, AutoDayColor, YieldFilterColor, SortOrder, SetupSortorder, FK_ColorRGBID)
	VALUES ('Red',  1, 1, 3, 1, @@IDENTITY)
	
	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(255, 0, 0)
	INSERT INTO Color (ColorName, AutoDayColor, YieldFilterColor, SortOrder, SetupSortorder, FK_ColorRGBID)
	VALUES ('Blue',1,  1, 4, 4, @@IDENTITY)
	
	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(107, 5, 165)
	INSERT INTO Color (ColorName,AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Cashier',  0, 0, 5, @@IDENTITY)
		
	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(128, 0, 0)
	INSERT INTO Color (ColorName, AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Navy',  0, 1, 6, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(0, 255, 0)
	INSERT INTO Color (ColorName, AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Lime',  0, 1, 7, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(255, 0, 255)
	INSERT INTO Color (ColorName,  AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Fuchsia',  0, 1, 8, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(168, 111, 168)
	INSERT INTO Color (ColorName,  AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Marketing',  0, 0, 9, @@IDENTITY)
	
	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(0, 0, 128)
	INSERT INTO Color (ColorName, AutoDayColor,  YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Maroon',  0, 1, 10, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(0, 128, 128)
	INSERT INTO Color (ColorName,  AutoDayColor,  YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Olive', 0,  1, 11, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(128, 0, 128)
	INSERT INTO Color (ColorName,  AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Purple',  0, 1, 12, @@IDENTITY)
	
	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(128, 128, 0)
	INSERT INTO Color (ColorName, AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Teal',  0, 1, 13, @@IDENTITY)

	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(255, 255, 0)
	INSERT INTO Color (ColorName,  AutoDayColor, YieldFilterColor, SortOrder, FK_ColorRGBID)
	VALUES ('Aqua',  0, 1, 14, @@IDENTITY)
	
	SET IDENTITY_INSERT Color ON
	INSERT INTO ColorRGB (Blue, Green, Red )
	VALUES(0, 0, 0)
	INSERT INTO Color (ColorID, ColorName, SortOrder, FK_ColorRGBID)
	VALUES (0, 'None', 999, @@IDENTITY)
	
	SET IDENTITY_INSERT Color OFF
	
	DECLARE @MatchDayColor INT
	DECLARE @ProductNumber INT
	DECLARE @YieldNumber INT
	DECLARE @ColorID INT
	DECLARE @Count int
	SET @Count = 0
	
	
	DECLARE #Cur CURSOR LOCAL FAST_FORWARD FOR
		SELECT ProductNumber, MatchDayColor From Products 
		WHERE ISNULL(MatchDayColor, 0) > 0
		
	OPEN #CUR
	FETCH NEXT FROM #CUR INTO @ProductNumber,  @MatchDayColor
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		WHILE @Count < 9
		BEGIN
			IF (@MatchDayColor & POWER(2, @count)) = POWER(2,@Count)
				INSERT INTO ProductColorXTAB (ProductNumber, ColorID) VALUES (@ProductNumber, @Count + 1)
			SET @Count = @Count + 1
		END	
		Set @Count = 0
		FETCH NEXT FROM #CUR INTO @ProductNumber,  @MatchDayColor
	END
	CLOSE #CUR
	DEALLOCATE #CUR
	
	SET @Count = 0
	
	DECLARE #Cur CURSOR LOCAL FAST_FORWARD FOR
		SELECT YieldNumber, MatchDayColor From Yield 
		WHERE ISNULL(MatchDayColor, 0) > 0
		
	OPEN #CUR
	FETCH NEXT FROM #CUR INTO @YieldNumber,  @MatchDayColor
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		WHILE @Count < 9
		BEGIN
			IF (@MatchDayColor & POWER(2, @count)) = POWER(2,@Count)
				INSERT INTO YieldColorXTAB (YieldNumber, ColorID) VALUES (@YieldNumber, @Count + 1)
			SET @Count = @Count + 1
		END	
		Set @Count = 0
		FETCH NEXT FROM #CUR INTO @YieldNumber,  @MatchDayColor
	END
	CLOSE #CUR
	DEALLOCATE #CUR
	
	UPDATE Yield
	SET Yield.FilterColorID = Color.ColorID
	FROM Yield
		INNER JOIN Color
		ON colorname = SUBSTRING(FilterColor,3,LEN(FilterColor))
		
	UPDATE RoomBookingStatusYield 
	SET DayColor = 0
	WHERE DayColor < -1

	DECLARE
	@CustomerNumber INT, 
	@AutoDayProfileColorsNONE INT,
	@AutoDayProfileColorsBLUE INT, 
	@AutoDayProfileColorsGREEN INT, 
	@AutoDayProfileColorsYELLOW INT, 
	@AutoDayProfileColorsRED INT

	IF NOT EXISTS(SELECT * FROM SalesChannelExtraFields WHERE FK_ColorID > 0)
	BEGIN
		DELETE FROM SalesChannelExtraFields
	
		DECLARE db_cur CURSOR FOR
			SELECT CustomerNumber,AutoDayProfileColorsNONE, AutoDayProfileColorsBLUE, AutoDayProfileColorsGREEN, AutoDayProfileColorsYELLOW, AutoDayProfileColorsRED
			FROM SalesChannelExtraFields_Old
		OPEN db_cur
		FETCH NEXT FROM db_cur INTO @CustomerNumber, @AutoDayProfileColorsNONE, @AutoDayProfileColorsBLUE, @AutoDayProfileColorsGREEN, @AutoDayProfileColorsYELLOW, @AutoDayProfileColorsRED
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF ISNULL(@AutoDayProfileColorsNONE, 0) + ISNULL(@AutoDayProfileColorsBLUE, 0) + ISNULL(@AutoDayProfileColorsGREEN, 0) + ISNULL(@AutoDayProfileColorsYELLOW,0) + ISNULL(@AutoDayProfileColorsRED, 0) > 0
			BEGIN
				INSERT INTO SalesChannelExtraFields (CustomerNumber, FK_ColorID, Availability)
				Values (@CustomerNumber, -1,@AutoDayProfileColorsNONE)
			
				INSERT INTO SalesChannelExtraFields (CustomerNumber, FK_ColorID, Availability)
				Values (@CustomerNumber, 3 ,@AutoDayProfileColorsRED)
			
				IF @AutoDayProfileColorsYELLOW > @AutoDayProfileColorsRED
					INSERT INTO SalesChannelExtraFields (CustomerNumber, FK_ColorID, Availability)
					Values (@CustomerNumber, 2 ,@AutoDayProfileColorsYELLOW)
				
				IF @AutoDayProfileColorsGREEN > @AutoDayProfileColorsYELLOW
					INSERT INTO SalesChannelExtraFields (CustomerNumber, FK_ColorID, Availability)
					Values (@CustomerNumber, 1,@AutoDayProfileColorsGREEN)
				
				IF @AutoDayProfileColorsBLUE > @AutoDayProfileColorsGREEN
					INSERT INTO SalesChannelExtraFields (CustomerNumber, FK_ColorID, Availability)
					Values (@CustomerNumber, 4,@AutoDayProfileColorsBLUE)
			END	
			FETCH NEXT FROM db_cur INTO @CustomerNumber, @AutoDayProfileColorsNONE, @AutoDayProfileColorsBLUE, @AutoDayProfileColorsGREEN, @AutoDayProfileColorsYELLOW, @AutoDayProfileColorsRED
		END

		CLOSE db_cur
		DEALLOCATE db_cur
	END
	
	
end