--BEGIN TRAN
IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'FkAccountVatGroup'
    WHERE SysObjects.Name = 'Accounts'
)
BEGIN 
  CREATE TABLE dbo.[AccountVatGroup] (
    [PkAccountVatGroup] int IDENTITY (1, 1) NOT NULL,
    [OrderNumber] int NOT NULL,
    [Profile] tinyint NOT NULL DEFAULT ((0)),
    [Name] varchar (50) NOT NULL,
    [Description] varchar (50) NULL) ON [Primary]
END
GO
IF NOT EXISTS 
(
  SELECT name FROM SysIndexes  WITH (NOLOCK) 
  WHERE name = 'PK_AccountVatGroup'
)
BEGIN
  ALTER TABLE Accounts ADD [FkAccountVatGroup] INT NOT NULL DEFAULT ((0))
  ALTER TABLE AccountVatGroup WITH NOCHECK ADD CONSTRAINT PK_AccountVatGroup PRIMARY KEY  CLUSTERED (PkAccountVatGroup)  
  ALTER TABLE Accounts WITH NOCHECK ADD CONSTRAINT FK_Accounts_AccountVatGroup FOREIGN KEY (FkAccountVatGroup) REFERENCES AccountVatGroup (PkAccountVatGroup)
END
GO
IF  
(
  SELECT COUNT(*) FROM AccountVatGroup WITH (NOLOCK) 
) = 0
BEGIN
  DECLARE @CustomerNumber INT, @VatGroup INT, @BeingHereOnce BIT
  INSERT INTO AccountVatGroup (OrderNumber, Profile, Name, Description) 
  VALUES (1, 0, 'Default', '')
  SELECT @VatGroup = PkAccountVatGroup FROM AccountVatGroup WITH (NOLOCK) WHERE Profile = 0
  UPDATE Accounts SET FkAccountVatGroup = @VatGroup 
  SET @BeingHereOnce = 0 
  DECLARE #HotelCur CURSOR FAST_FORWARD READ_ONLY FOR
  SELECT CustomerNumber FROM Customer WITH (NOLOCK) WHERE (HotelNumber>0 OR LocalHotel=1) AND ISNULL(Customer.MarkAsDeleted, 0) = 0
  OPEN #HotelCur
  FETCH NEXT FROM #HotelCur
  INTO @CustomerNumber
  WHILE @BeingHereOnce = 0 AND @@FETCH_STATUS = 0 -- safe
  BEGIN
    IF (SELECT dbo.GetHotelSetupKeyValue('BillVatSplitLogi', @CustomerNumber) ) = 1
    BEGIN
      SET @BeingHereOnce = 1
      INSERT INTO AccountVatGroup (OrderNumber, Profile, Name, Description) 
      VALUES (2, 2, 'Overnatning', 'Værelse/Logi')
      SELECT @VatGroup = PkAccountVatGroup FROM AccountVatGroup WITH (NOLOCK) WHERE Profile = 2
      PRINT @VatGroup
      UPDATE Accounts SET FkAccountVatGroup = @VatGroup 
      WHERE AccountNumber IN (
        SELECT ResultAccountNumber FROM Ware WITH (NOLOCK)
          INNER JOIN WareGroups WG WITH (NOLOCK) ON WG.WareGroupNumber = Ware.WareGroupNumber AND WG.Payment IN (2, 7)
        WHERE ISNULL(Ware.MarkAsDeleted, 0) = 0
      ) AND ISNULL(Accounts.MarkAsDeleted, '0') = '0'
    END    
    FETCH NEXT FROM #HotelCur
    INTO @CustomerNumber    
  END
  CLOSE #HotelCur
  DEALLOCATE #HotelCur
END

--SELECT * FROM AccountVatGroup WITH (NOLOCK)
--SELECT * FROM Accounts WITH (NOLOCK) WHERE ISNULL(FkAccountVatGroup, 0) > 0  
--ROLLBACK TRAN
