DECLARE @TheCountryCode VARCHAR(3)

SELECT @TheCountryCode = CountryCode 
FROM Countries WITH (NOLOCK) 
WHERE CountryNumber = (SELECT KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'COUNTRYCODE')

IF @TheCountryCode = 'IE' OR @TheCountryCode = 'IRL' OR @TheCountryCode = 'IR'
BEGIN
  DECLARE @GroupName VARCHAR(30)
  DECLARE @SortNumber INT
  DECLARE @GroupCode VARCHAR(3)

  DECLARE @CountryName VARCHAR(30)
  DECLARE @CountryCode VARCHAR(3)
  DECLARE @CurrencyCode VARCHAR(3)
  DECLARE @CurrencyName VARCHAR(30)

  DECLARE @CountryGroupNumber INT

  DECLARE @OtherEurope INT
  DECLARE @NorthAmerica INT
  DECLARE @Other INT

  CREATE TABLE #TempGroups 
  (
    CountryName VARCHAR(30) NOT NULL,
    GroupSortNumber INT NOT NULL,
    CountryCode VARCHAR(3) NOT NULL
  )

  SELECT @OtherEurope = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 34

  UPDATE Countries SET CountryGroupNumber = @OtherEurope
--  UPDATE Country SET FK_CountryGroup = @OtherEurope
  DELETE CountryGroups WHERE GroupNumber <> @OtherEurope

  INSERT INTO #TempGroups VALUES ('Rep Ireland',1,'IE')
  INSERT INTO #TempGroups VALUES ('Northern Ireland',2,'NI')
  INSERT INTO #TempGroups VALUES ('Britain',3,'GB')
  INSERT INTO #TempGroups VALUES ('North America',4,'NA')
  INSERT INTO #TempGroups VALUES ('France',5,'FR')
  INSERT INTO #TempGroups VALUES ('Germany',6,'DE')
  INSERT INTO #TempGroups VALUES ('Other Europe',7,'OE')
  INSERT INTO #TempGroups VALUES ('Other',8,'O')
  INSERT INTO #TempGroups VALUES ('Unknown',9,'U')

  DECLARE db_cur CURSOR FOR
    SELECT * FROM #TempGroups WITH (NOLOCK)
  OPEN db_cur 
	   
  FETCH NEXT FROM db_cur INTO @GroupName, @SortNumber, @GroupCode
	    
  WHILE @@FETCH_STATUS = 0 
  BEGIN
    IF @SortNumber <> 7
      INSERT INTO CountryGroups (GroupName, GroupSortNumber, GroupCode) VALUES (@GroupName, @SortNumber, @GroupCode)
    ELSE
      UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode WHERE GroupNumber = @OtherEurope
	  
    FETCH NEXT FROM db_cur INTO @GroupName, @SortNumber, @GroupCode
  END
  
  CLOSE db_cur
  DEALLOCATE db_cur
  
  --Hent Hvilket nr de enkelte grupper har fået.
  SELECT @OtherEurope = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 7
  SELECT @Other = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 8
  SELECT @NorthAmerica = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 4
	
  UPDATE Countries SET CountryGroupNumber = @Other

  -- Make sure that all countries exists
  CREATE TABLE #TempCountries 
  (
    CountryName VARCHAR(30) NOT NULL,
    CountryCode VARCHAR(3) NOT NULL,
    CurrencyCode VARCHAR(3) NOT NULL,
    CurrencyName VARCHAR(30) NOT NULL
  )

  INSERT INTO #TempCountries VALUES ('Rep Ireland','IE','EUR','Euro')
  INSERT INTO #TempCountries VALUES ('Northern Ireland','NI','GBP','British Pound')
  INSERT INTO #TempCountries VALUES ('Britain','GB','GBP','British Pound')
  INSERT INTO #TempCountries VALUES ('France','FR','EUR','Euro')
  INSERT INTO #TempCountries VALUES ('Germany','DE','EUR','Euro')

  DECLARE db_cur1 CURSOR FOR
    SELECT * FROM #TempCountries WITH (NOLOCK)
  OPEN db_cur1 
	 
  FETCH NEXT FROM db_cur1 INTO @CountryName, @CountryCode, @CurrencyCode, @CurrencyName
	  
  WHILE @@FETCH_STATUS = 0 
  BEGIN
    PRINT '@CountryName11111111111111111111111111111111111111111'
    PRINT @CountryName
    
    IF @CountryName = 'Rep Ireland'
    BEGIN
      PRINT 'Rep Ireland'
      IF EXISTS (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryName = 'Ireland')
      BEGIN
        SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupCode = @CountryCode
        UPDATE Countries SET CountryGroupNumber = @CountryGroupNumber, MarkAsDeleted = 0, CurrencyCode = @CurrencyCode, CurrencyName = @CurrencyName WHERE CountryName = 'Ireland'   
      END
      ELSE
      BEGIN
        -- Findes slet ikke i forvejen
        SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupCode = @CountryCode
        INSERT INTO Countries (CountryName, CountryCode, MarkAsDeleted, CountryGroupNumber, CurrencyCode, CurrencyName, SortNumber) VALUES (@CountryName,@CountryCode,0,@CountryGroupNumber,@CurrencyCode,@CurrencyName,999)
      END
    END
    ELSE IF @CountryName = 'Britain'
    BEGIN
      PRINT 'Britain'
      IF EXISTS (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryName = 'Great Britain')
      BEGIN
        SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupCode = @CountryCode
        UPDATE Countries SET CountryGroupNumber = @CountryGroupNumber, MarkAsDeleted = 0, CurrencyCode = @CurrencyCode, CurrencyName = @CurrencyName WHERE CountryName = 'Great Britain'   
      END
      ELSE
      BEGIN
        -- Findes slet ikke i forvejen
        SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupCode = @CountryCode
        INSERT INTO Countries (CountryName, CountryCode, MarkAsDeleted, CountryGroupNumber, CurrencyCode, CurrencyName, SortNumber) VALUES (@CountryName,@CountryCode,0,@CountryGroupNumber,@CurrencyCode,@CurrencyName,999)
      END
    END
    ELSE IF NOT EXISTS (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryName = @CountryName) 
    BEGIN
      -- landet findes ikke - indsæt det
      PRINT 'CREATE IT'
      SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupCode = @CountryCode
      INSERT INTO Countries (CountryName, CountryCode, MarkAsDeleted, CountryGroupNumber, CurrencyCode, CurrencyName, SortNumber) VALUES (@CountryName,@CountryCode,0,@CountryGroupNumber,@CurrencyCode,@CurrencyName,999)
    END
    ELSE
    BEGIN 
      PRINT 'UPDATE'
      -- Landet findes - kontroller at groupnumber er korrekt.
      SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupCode = @CountryCode
      UPDATE Countries SET CountryGroupNumber = @CountryGroupNumber, MarkAsDeleted = 0, CurrencyCode = @CurrencyCode, CurrencyName = @CurrencyName WHERE CountryName = @CountryName   
    END
    FETCH NEXT FROM db_cur1 INTO @CountryName, @CountryCode, @CurrencyCode, @CurrencyName
  END

  CLOSE db_cur1
  DEALLOCATE db_cur1

  --hent liste over alle andre lande og del op i korrekte grupper
  DECLARE db_cur2 CURSOR FOR
    SELECT CountryName, CountryGroupNumber FROM Countries WITH (nolock)
  OPEN db_cur2 
	 
  FETCH NEXT FROM db_cur2 INTO @CountryName, @CountryGroupNumber
	  
  WHILE @@FETCH_STATUS = 0 
  BEGIN
    IF @CountryName = 'Denmark' OR @CountryName = 'Norway' OR @CountryName = 'Sweden' OR @CountryName = 'Portugal' OR @CountryName = 'Belgium' OR @CountryName = 'Albania' OR
      @CountryName = 'Andorra' OR @CountryName = 'Bulgaria' OR @CountryName = 'Cyprus' OR @CountryName = 'Czech Republic' OR @CountryName = 'Estonia' OR 
      @CountryName = 'Faroe Islands' OR @CountryName = 'Finland' OR @CountryName = 'Greece' OR @CountryName = 'Hungary' OR @CountryName = 'Iceland' OR @CountryName = 'Italia' OR
      @CountryName = 'Liechtenstein' OR @CountryName = 'Luxembourg' OR @CountryName = 'Malta' OR @CountryName = 'Monaco' OR @CountryName = 'Netherlands' OR
      @CountryName = 'Poland' OR @CountryName = 'Romania' OR @CountryName = 'Spain' OR @CountryName = 'Switzerland' OR @CountryName = 'Turkey' OR
      @CountryName = 'Vatican' OR @CountryName = 'Yugoslavia' OR @CountryName = 'Greenland' OR @CountryName = 'Latvia' OR @CountryName = 'Lithuania' OR
      @CountryName = 'Slovakia' OR @CountryName = 'Slovenia' OR @CountryName = 'Ukraine' OR @CountryName = 'Serbia&Montenegro'
    BEGIN
      -- Europe 
      UPDATE Countries SET CountryGroupNumber = @OtherEurope WHERE CountryName = @CountryName 
    END

    IF @CountryName = 'USA' OR @CountryName = 'Canada' OR @CountryName = 'Costa Rica' OR @CountryName = 'Cuba' OR @CountryName = 'Guatemala' OR 
      @CountryName = 'Haiti' OR @CountryName = 'Mexico' OR @CountryName = 'Nicaragua' OR @CountryName = 'Panama'
    BEGIN
      -- North America
      UPDATE Countries SET CountryGroupNumber = @NorthAmerica WHERE CountryName = @CountryName 
    END

    FETCH NEXT FROM db_cur2 INTO @CountryName, @CountryGroupNumber
  END
  CLOSE db_cur2
  DEALLOCATE db_cur2
  
  DROP TABLE #TempCountries
  DROP TABLE #TempGroups
END