SET NOCOUNT ON
DECLARE @TheCountryCode varchar(3)

DECLARE @GroupName varchar(30)
DECLARE @SortNumber int
DECLARE @GroupCode varchar(3)
DECLARE @CurrencyCode varchar(3)
DECLARE @CurrencyName varchar(30)
DECLARE @CountryName1 VARCHAR(30)

DECLARE @CountryGroupNumber int

DECLARE @OtherEurope int
DECLARE @OtherAfrica int
DECLARE @OtherAmerica int
DECLARE @OtherAsia int
DECLARE @OtherOceanic int
DECLARE @OtherWorld int

DECLARE @CountryName VARCHAR(30)

CREATE TABLE #TempGroups 
(
  GroupName VARCHAR(30) NOT NULL,
  GroupSortNumber INT NOT NULL,
  CountryCode VARCHAR(3) NOT NULL,
  CurrencyCode VARCHAR(3) NOT NULL,
  CurrencyName VARCHAR(30) NOT NULL,
  CountryName1 VARCHAR(30) NOT NULL
)

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

IF @TheCountryCode = 'GL' 
BEGIN
  INSERT INTO #TempGroups VALUES ('Bosiddende i Grønland',1,'GL', 'DKK', 'Kroner', 'Greenland')
  INSERT INTO #TempGroups VALUES ('Danmark',2,'DK', 'DKK', 'Kroner', 'Denmark')
  INSERT INTO #TempGroups VALUES ('Frankrig',3,'F', 'EUR', 'EURO', 'France')
  INSERT INTO #TempGroups VALUES ('Holland',4,'NL', 'EUR', 'EURO', 'Netherlands')
  INSERT INTO #TempGroups VALUES ('Island',5,'IS', 'ISK', 'Kroner', 'Iceland')
  INSERT INTO #TempGroups VALUES ('Italien',6,'I', 'EUR', 'EURO', 'Italy')
  INSERT INTO #TempGroups VALUES ('Norge',7,'N', 'NOK', 'Kroner', 'Norway')
  INSERT INTO #TempGroups VALUES ('Polen',8,'PL', 'EUR', 'EURO', 'Poland')
  INSERT INTO #TempGroups VALUES ('Rusland',9,'RU', 'RUB', 'Rubler', 'Russia')
  INSERT INTO #TempGroups VALUES ('Schweiz',10,'SZ', 'EUR', 'EURO', 'Switzerland')
  INSERT INTO #TempGroups VALUES ('Spanien',11,'ES', 'EUR', 'EURO', 'Spain')
  INSERT INTO #TempGroups VALUES ('Storbritannien',12,'GB', 'GBP', 'Pund Sterling', 'Great Britain')
  INSERT INTO #TempGroups VALUES ('Sverige',13,'SE', 'SEK', 'Kroner', 'Sweden')
  INSERT INTO #TempGroups VALUES ('Tyskland',14,'DE', 'EUR', 'EURO', 'Germany')
  INSERT INTO #TempGroups VALUES ('Østrig',15,'AT', 'EUR', 'EURO', 'Austria')
  INSERT INTO #TempGroups VALUES ('Øvrige Europa',16,'OE', '', '', 'Other Europe')
  INSERT INTO #TempGroups VALUES ('Australien',17,'AU', 'AUD', 'Dollar', 'Australia')
  INSERT INTO #TempGroups VALUES ('Canada',18,'CA', 'CAD', 'Dollar', 'Canada')
  INSERT INTO #TempGroups VALUES ('USA',19,'US', 'USD', 'Dollar', 'USA')
  INSERT INTO #TempGroups VALUES ('Hong Kong',20,'HK', 'HKD', 'Dollar', 'Hong Kong')
  INSERT INTO #TempGroups VALUES ('Indonesien',21,'ID', 'IDR', 'Rupiah', 'Indonesia')
  INSERT INTO #TempGroups VALUES ('Japan',22,'JP', 'JPY', 'Yen', 'Japan')
  INSERT INTO #TempGroups VALUES ('Kina',23,'CN', 'CNY', 'Yuan', 'China')
  INSERT INTO #TempGroups VALUES ('Malaysia',24,'MY', 'MYR', 'Ringgits', 'Malaysia')
  INSERT INTO #TempGroups VALUES ('Singapore',25,'SG', 'SGD', 'Dollar', 'Singapore')
  INSERT INTO #TempGroups VALUES ('Sydkorea',26,'KR', 'KRW', 'Won', 'South Korea')
  INSERT INTO #TempGroups VALUES ('Taiwan',27,'TW', 'TWD', 'Dollar', 'Taiwan')
  INSERT INTO #TempGroups VALUES ('Øvrige Asien',28,'OAL', '', '', 'Other Asia')
  INSERT INTO #TempGroups VALUES ('Andre lande',29,'øvr', '', '', 'Other world')

  DECLARE db_curGL CURSOR FOR
    SELECT * FROM #TempGroups WITH (nolock)
  OPEN db_curGL 
	   
  FETCH NEXT FROM db_curGL INTO @GroupName, @SortNumber, @GroupCode, @CurrencyCode, @CurrencyName, @CountryName1
	    
  WHILE @@fetch_status = 0 
  BEGIN
    IF @SortNumber = 1   -- Bosiddende i Grønland
      UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode  WHERE GroupName LIKE 'Gr%nland' 
    ELSE IF @SortNumber = 12   -- England 
      UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode  WHERE GroupName LIKE '%/ Nord irland%' OR GroupName LIKE '%/ Nordirland%' OR GroupName = 'England'
    ELSE IF @SortNumber = 16   -- Øvrige europa 
      UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode  WHERE GroupName LIKE '%Europa i %vrigt%'
    ELSE IF @SortNumber = 29   -- Andre lande 
      UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode  WHERE GroupName LIKE '%vrige lande%'

    IF NOT EXISTS (SELECT GroupName FROM CountryGroups WITH (NOLOCK) WHERE GroupName = @GroupName) 
      INSERT INTO CountryGroups (GroupName, GroupSortNumber, GroupCode) VALUES (@GroupName, @SortNumber, @GroupCode)
    ELSE
      UPDATE CountryGroups SET GroupSortNumber = @SortNumber, GroupCode = @GroupCode WHERE GroupName = @GroupName

    SELECT @CountryGroupNumber = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupName = @GroupName

	  IF @GroupName = 'Italien'
	  BEGIN
	    IF EXISTS (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryName = 'Italia')
	      UPDATE Countries SET CountryGroupNumber = @CountryGroupNumber, MarkAsDeleted = 0, CurrencyCode = @CurrencyCode, CurrencyName = @CurrencyName WHERE CountryName = 'Italia'   
	    ELSE IF EXISTS (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryName = 'Italy')
	      UPDATE Countries SET CountryGroupNumber = @CountryGroupNumber, MarkAsDeleted = 0, CurrencyCode = @CurrencyCode, CurrencyName = @CurrencyName WHERE CountryName = 'Italy'   
	  END
    ELSE IF EXISTS (SELECT * FROM Countries WITH (NOLOCK) WHERE CountryName = @CountryName1)
    BEGIN
      UPDATE Countries SET CountryGroupNumber = @CountryGroupNumber, MarkAsDeleted = 0, CurrencyCode = @CurrencyCode, CurrencyName = @CurrencyName WHERE CountryName = @CountryName1   
    END
    ELSE
    BEGIN
      -- Findes slet ikke i forvejen
      INSERT INTO Countries (CountryName, CountryCode, MarkAsDeleted, CountryGroupNumber, CurrencyCode, CurrencyName, SortNumber, CurrencyActiveOnline) 
      VALUES (@CountryName1, @GroupCode, 0, @CountryGroupNumber, @CurrencyCode, @CurrencyName, 999, 0)
    END

    FETCH NEXT FROM db_curGL INTO @GroupName, @SortNumber, @GroupCode, @CurrencyCode, @CurrencyName, @CountryName1
  END

  CLOSE db_curGL
  DEALLOCATE db_curGL

  SELECT @OtherEurope = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 16
  SELECT @OtherAfrica = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 29
  SELECT @OtherAmerica = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 29
  SELECT @OtherAsia = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 28
  SELECT @OtherOceanic = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 28

  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 = 'Algeria' or @CountryName = 'Angola' or @CountryName = 'Benin' or @CountryName = 'Botswana' or @CountryName = 'Burkina Faso' or 
	     @CountryName = 'Burundi' or @CountryName = 'Cameroon' or @CountryName = 'Cape Verde' or @CountryName = 'Central African Republic' or @CountryName = 'Chad' or 
	     @CountryName = 'Comoros' or @CountryName = 'Cote dIvoire' or @CountryName = 'Egypt' or @CountryName = 'Equatorial Guinea' or @CountryName = 'Eritrea' or 
	     @CountryName = 'Ethiopia' or @CountryName = 'Gabon' or @CountryName = 'Gambia' or @CountryName = 'Ghana' or @CountryName = 'Guinea-Bissau' or 
	     @CountryName = 'Guinea' or @CountryName = 'Kenya' or @CountryName = 'Liberia' or @CountryName = 'Libya' or @CountryName = 'Madagascar' or @CountryName = 'Malawi' or 
	     @CountryName = 'Mali' or @CountryName = 'Mauritania' or @CountryName = 'Mauritius' or @CountryName = 'Morocco' or @CountryName = 'Mozambique' or 
	     @CountryName = 'Namibia' or @CountryName = 'Nigeria' or @CountryName = 'Niger' or @CountryName = 'Republic of the Congo' or @CountryName = 'Rwanda' or 
	     @CountryName = 'Sao Tome and Principe' or @CountryName = 'Senegal' or @CountryName = 'Seychelles' or @CountryName = 'Sierra Leone' or @CountryName = 'Somalia' or 
	     @CountryName = 'Sudan' or @CountryName = 'Swaziland' or @CountryName = 'Tanzania' or @CountryName = 'Togo' or @CountryName = 'Tunisia' or 
	     @CountryName = 'Uganda' or @CountryName = 'Zambia' or @CountryName = 'Zimbabwe' or @CountryName = 'Congo' or @CountryName = 'Tunesia' or @CountryName = 'South Africa' 
	  BEGIN
	    -- Africa
	    UPDATE Countries SET CountryGroupNumber = @OtherAfrica WHERE CountryName = @CountryName 
	  END
	
	  IF @CountryName = 'Armenia' OR @CountryName = 'ORAzerbaijan' OR @CountryName = 'Bangladesh' OR @CountryName = 'Brunei' OR @CountryName = 'Cambodia' OR
	     @CountryName = 'Georgia' OR @CountryName = 'India' OR @CountryName = 'Iran' OR
	     @CountryName = 'Iraq' OR @CountryName = 'Israel' OR @CountryName = 'Jordan' OR @CountryName = 'Kazakhstan' OR @CountryName = 'Kuwait' OR
	     @CountryName = 'Laos' OR @CountryName = 'Lebanon' OR @CountryName = 'Macao' OR @CountryName = 'Maldives' OR @CountryName = 'Mongolia' OR
	     @CountryName = 'Myanmar' OR @CountryName = 'Nepal' OR @CountryName = 'North Korea' OR @CountryName = 'Oman' OR @CountryName = 'Pakistan' OR
	     @CountryName = 'Philippines' OR @CountryName = 'Qatar' OR @CountryName = 'Saudi Arabia' OR @CountryName = 'Sri Lanka' OR
	     @CountryName = 'Syria' OR @CountryName = 'Tajikistan' OR @CountryName = 'Oman' OR @CountryName = 'Thailand' OR
	     @CountryName = 'United Arab Emirates' OR @CountryName = 'Vietnam' OR @CountryName = 'Yemen' OR @CountryName = 'Sri Lanca' or 
       @CountryName = 'Other Transatlantic' or @CountryName = 'Other Asien'
	  BEGIN
	    -- Asia
	    UPDATE Countries SET CountryGroupNumber = @OtherAsia WHERE CountryName = @CountryName 
	  END
	
	  IF @CountryName = 'Federated States of Micronesia' OR @CountryName = 'Fiji' OR @CountryName = 'Kiribati' OR @CountryName = 'Marshall Islands' OR
	     @CountryName = 'Nauru' OR @CountryName = 'New Zealand' OR @CountryName = 'Palau' OR @CountryName = 'Papua New Guinea' OR @CountryName = 'Samoa' OR
	     @CountryName = 'Solomon Islands' OR @CountryName = 'Tonga' OR @CountryName = 'Tuvalu' OR @CountryName = 'Vanuatu'
	  BEGIN
	    -- Oceanic
	    UPDATE Countries SET CountryGroupNumber = @OtherOceanic WHERE CountryName = @CountryName 
	  END
	
	  IF @CountryName = 'Albania' or @CountryName = 'Andorra' or @CountryName = 'Belarus' or @CountryName = 'Bosnia and Herzegovina' or @CountryName = 'Bulgaria' or
	     @CountryName = 'Croatia' or @CountryName = 'Liechtenstein' or @CountryName = 'Macedonia' or @CountryName = 'Moldova' or @CountryName = 'Romania' or
	     @CountryName = 'San Marino' or @CountryName = 'Vatican City' or @CountryName = 'Yugoslavia' OR @CountryName = 'Euro' or @CountryName = 'Vatican' OR
       @CountryName = 'Faroe Islands' or @CountryName = 'Belgium' or @CountryName = 'Cyprus' or @CountryName = 'Estonia' or @CountryName = 'Finland' or @CountryName = 'Greece' or
       @CountryName = 'Ireland' or @CountryName = 'Latvia' or @CountryName = 'Lithuania' or @CountryName = 'Luxembourg' or @CountryName = 'Malta' or 
       @CountryName = 'Portugal' or @CountryName = 'Slovakia' or 
       @CountryName = 'Slovenia' or @CountryName = 'Czech Republic' or @CountryName = 'Turkia' or @CountryName = 'Ukraine' or
       @CountryName = 'Hungary'
	  BEGIN
	    -- Europe 
	    UPDATE Countries SET CountryGroupNumber = @OtherEurope WHERE CountryName = @CountryName 
	  END
	
	  IF @CountryName = 'Belize' OR @CountryName = 'Costa Rica' OR @CountryName = 'Cuba' OR @CountryName = 'Dominican Republic' OR @CountryName = 'El Salvador' OR
	     @CountryName = 'Guatemala' OR @CountryName = 'Haiti' OR @CountryName = 'Honduras' OR @CountryName = 'Jamaica' OR @CountryName = 'Mexico' OR
	     @CountryName = 'Nicaragua' OR @CountryName = 'Panama' OR @CountryName = 'Argentina' OR @CountryName = 'Bolivia' OR @CountryName = 'Chile' OR
	     @CountryName = 'Colombia' OR @CountryName = 'Ecuador' OR @CountryName = 'French Guiana' OR @CountryName = 'Guyana' OR @CountryName = 'Paraguay' OR
	     @CountryName = 'Peru' OR @CountryName = 'Suriname' OR @CountryName = 'Uruguay' OR @CountryName = 'Venezuela' OR @CountryName = 'Equador' OR 
       @CountryName = 'Mexico' OR @CountryName = 'Brazil'
	  BEGIN
	    --America
	    UPDATE Countries SET CountryGroupNumber = @OtherAmerica WHERE CountryName = @CountryName 
	  END

    FETCH NEXT FROM db_cur2 INTO @CountryName, @CountryGroupNumber
  END
  CLOSE db_cur2
  DEALLOCATE db_cur2
END -- End of Grønland Groups

DROP TABLE #TempGroups 