SET NOCOUNT ON
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 @OtherAfrica int
DECLARE @OtherMiddleAmerica int
DECLARE @OtherSouthAmerica int
DECLARE @OtherNorthAmerica int
DECLARE @OtherAsia int
DECLARE @OtherOceanic int

DECLARE @TheCountryCode varchar(3)

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

IF @TheCountryCode = 'D'
BEGIN
  UPDATE CountryGroups SET GroupName = 'Belgium' WHERE GroupName = 'Northern Ireland'
  UPDATE CountryGroups SET GroupName = 'Taiwan' WHERE GroupName = 'Unknown'

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

  INSERT INTO #TempGroups VALUES ('Germany',1,'DE')
  INSERT INTO #TempGroups VALUES ('Belgium',2,'BE')
  INSERT INTO #TempGroups VALUES ('Bulgaria',3,'BG')
  INSERT INTO #TempGroups VALUES ('Denmark',4,'DK')
  INSERT INTO #TempGroups VALUES ('Estonia',5,'EE')
  INSERT INTO #TempGroups VALUES ('Finland',6,'FI')
  INSERT INTO #TempGroups VALUES ('France',7,'FR')
  INSERT INTO #TempGroups VALUES ('Greece',8,'GR')
  INSERT INTO #TempGroups VALUES ('United Kingdom',9,'GB')
  INSERT INTO #TempGroups VALUES ('Ireland',10,'IE')
  INSERT INTO #TempGroups VALUES ('Iceland',11,'IS')
  INSERT INTO #TempGroups VALUES ('Italy',12,'IT')
  INSERT INTO #TempGroups VALUES ('Croatia',13,'HR')
  INSERT INTO #TempGroups VALUES ('Latvia',14,'LV')
  INSERT INTO #TempGroups VALUES ('Lithuania',15,'LT')
  INSERT INTO #TempGroups VALUES ('Luxembourg',16,'LU')
  INSERT INTO #TempGroups VALUES ('Malta',17,'MT')
  INSERT INTO #TempGroups VALUES ('Netherlands',18,'NL')
  INSERT INTO #TempGroups VALUES ('Norway',19,'NO')
  INSERT INTO #TempGroups VALUES ('Austria',20,'AT')
  INSERT INTO #TempGroups VALUES ('Poland',21,'PL')
  INSERT INTO #TempGroups VALUES ('Portugal',22,'PT')
  INSERT INTO #TempGroups VALUES ('Romania',23,'RO')
  INSERT INTO #TempGroups VALUES ('Russia',24,'RU')
  INSERT INTO #TempGroups VALUES ('Sweden',25,'SE')
  INSERT INTO #TempGroups VALUES ('Switzerland',26,'CH')
  INSERT INTO #TempGroups VALUES ('Slovakia',27,'SK')
  INSERT INTO #TempGroups VALUES ('Slovenia',28,'SI')
  INSERT INTO #TempGroups VALUES ('Spain',29,'ES')
  INSERT INTO #TempGroups VALUES ('Czech Republic',30,'CZ')
  INSERT INTO #TempGroups VALUES ('Turkey',31,'TR')
  INSERT INTO #TempGroups VALUES ('Ukraine',32,'UA')
  INSERT INTO #TempGroups VALUES ('Hungary',33,'HU')
  INSERT INTO #TempGroups VALUES ('Cyprus',34,'CY')
  INSERT INTO #TempGroups VALUES ('Other Europe',35,'OE')

  INSERT INTO #TempGroups VALUES ('South Africa',36,'ZA')
  INSERT INTO #TempGroups VALUES ('Other Africa',37,'OA')

  INSERT INTO #TempGroups VALUES ('Canada',38,'CA')
  INSERT INTO #TempGroups VALUES ('USA',39,'US')
  INSERT INTO #TempGroups VALUES ('Middle America',40,'MA')
  INSERT INTO #TempGroups VALUES ('Brazil',41,'BR')
  INSERT INTO #TempGroups VALUES ('Other South america',42,'OS')
  INSERT INTO #TempGroups VALUES ('Other North America',43,'ON')
  
  INSERT INTO #TempGroups VALUES ('Arabian golf states',44,'AG')
  INSERT INTO #TempGroups VALUES ('China',45,'CN')
  INSERT INTO #TempGroups VALUES ('India',46,'IN')
  INSERT INTO #TempGroups VALUES ('Isreal',47,'IL')
  INSERT INTO #TempGroups VALUES ('Japan',48,'JP')
  INSERT INTO #TempGroups VALUES ('Korea',49,'KR')
  INSERT INTO #TempGroups VALUES ('Taiwan',50,'TW')
  INSERT INTO #TempGroups VALUES ('Other Asia',51,'OB')

  INSERT INTO #TempGroups VALUES ('Australia',52,'AU')
  INSERT INTO #TempGroups VALUES ('New Zealand',53,'NZ')

  INSERT INTO #TempGroups VALUES ('Other',54,'OT')

	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 = 9   -- United Kingdom
	    UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode WHERE GroupName = 'Britain'
    IF @SortNumber = 10   -- Ireland
 	    UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode WHERE GroupName = 'Rep Ireland' 
    IF @SortNumber = 43   -- North America
 	    UPDATE CountryGroups SET GroupName = @GroupName, GroupSortNumber = @SortNumber, GroupCode = @GroupCode WHERE GroupName = 'North America' 

    IF @@RowCount > 0
    BEGIN
	    FETCH NEXT FROM db_cur INTO @GroupName, @SortNumber, @GroupCode
	    CONTINUE
    END

	  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
	  
    FETCH NEXT FROM db_cur INTO @GroupName, @SortNumber, @GroupCode
  END

  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 ('Germany','DE','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Belgium','BE','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Bulgaria','BG','BGN','Lev')
  INSERT INTO #TempCountries VALUES ('Denmark','DK','DKK','Krone')
  INSERT INTO #TempCountries VALUES ('Estonia','EE','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Finland','FI','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('France','FR','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Greece','GR','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('United Kingdom','GB','GBP','Pund')
  INSERT INTO #TempCountries VALUES ('Ireland','IE','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Iceland','IS','ISK','Krona')
  INSERT INTO #TempCountries VALUES ('Italy','IT','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Croatia','HR','HRK','Kuna')
  INSERT INTO #TempCountries VALUES ('Latvia','LV','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Lithuania','LT','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Luxembourg','LU','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Malta','MT','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Netherlands','NL','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Norway','NO','NOK','Krone')
  INSERT INTO #TempCountries VALUES ('Austria','AT','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Poland','PL','PLN','Zloty')
  INSERT INTO #TempCountries VALUES ('Portugal','PT','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Romania','RO','RON','Leu')
  INSERT INTO #TempCountries VALUES ('Russia','RU','RUB','Ruble')
  INSERT INTO #TempCountries VALUES ('Sweden','SE','SEK','Krona')
  INSERT INTO #TempCountries VALUES ('Switzerland','CH','CHF','Franc')
  INSERT INTO #TempCountries VALUES ('Slovakia','SK','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Slovenia','SI','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Spain','ES','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Czech Republic','CZ','CZK','Koruna')
  INSERT INTO #TempCountries VALUES ('Turkey','TR','TRY','Lira')
  INSERT INTO #TempCountries VALUES ('Ukraine','UA','UAH','Hryvnia')
  INSERT INTO #TempCountries VALUES ('Hungary','HU','HUF','Forint')
  INSERT INTO #TempCountries VALUES ('Cyprus','CY','EUR','EURO')
  INSERT INTO #TempCountries VALUES ('Other Europe','OE','EUR','EURO')

  INSERT INTO #TempCountries VALUES ('South Africa','ZA','ZAR','Rand')
  INSERT INTO #TempCountries VALUES ('Other Africa','OA','','')

  INSERT INTO #TempCountries VALUES ('Canada','CA','CAD','Dollar')
  INSERT INTO #TempCountries VALUES ('USA','US','USD','Dollar')
  INSERT INTO #TempCountries VALUES ('Middle America','MA','','')
  INSERT INTO #TempCountries VALUES ('Brazil','BR','BRL','Real')
  INSERT INTO #TempCountries VALUES ('Other South america','OS','','')
  INSERT INTO #TempCountries VALUES ('Other North america','ON','','')
  
  INSERT INTO #TempCountries VALUES ('Arabian golf states','AG','','')
  INSERT INTO #TempCountries VALUES ('China','CN','CNY','Renminbi')
  INSERT INTO #TempCountries VALUES ('India','IN','INR','Rupee')
  INSERT INTO #TempCountries VALUES ('Israel','IL','ILS','New Israeli Sheqel')
  INSERT INTO #TempCountries VALUES ('Japan','JP','JPY','Yen')
  INSERT INTO #TempCountries VALUES ('South Korea','KR','KRW','Won')
  INSERT INTO #TempCountries VALUES ('Taiwan','TW','TWD','Dollar')
  INSERT INTO #TempCountries VALUES ('Other Asia','OB','','')

  INSERT INTO #TempCountries VALUES ('Australia','AU','AUD','Dollar')
  INSERT INTO #TempCountries VALUES ('New Zealand','NZ','NZD','Dollar')
  INSERT INTO #TempCountries VALUES ('Other','OT','','')

	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
	  IF NOT EXISTS (SELECT CountryName FROM Countries WITH (NOLOCK) WHERE CountryName = @CountryName) 
	  BEGIN
	    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 
	    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

	SELECT @OtherEurope = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 35
	SELECT @OtherAfrica = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 37
	SELECT @OtherMiddleAmerica = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 40
	SELECT @OtherSouthAmerica = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 42
	SELECT @OtherNorthAmerica = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 43
	SELECT @OtherAsia = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 51
	SELECT @OtherOceanic = GroupNumber FROM CountryGroups WITH (NOLOCK) WHERE GroupSortNumber = 54

  --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 = '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' 
    BEGIN
      -- Africa
      UPDATE Countries SET CountryGroupNumber = @OtherAfrica WHERE CountryName = @CountryName 
    END

    IF @CountryName = 'Armenia' OR @CountryName = 'Azerbaijan' OR @CountryName = 'Bangladesh' OR @CountryName = 'Brunei' OR @CountryName = 'Cambodia' OR
      @CountryName = 'Georgia' OR @CountryName = 'Hong Kong' OR @CountryName = 'Iran' OR
      @CountryName = 'Iraq' 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 = 'Saudi Arabia' OR @CountryName = 'Sri Lanka' OR
      @CountryName = 'Syria' OR @CountryName = 'Tajikistan' OR @CountryName = 'Oman' OR @CountryName = 'Vietnam' OR @CountryName = 'Yemen' OR @CountryName = 'Sri Lanca'
    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 = '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 = 'Yugoslavia' OR @CountryName = 'Euro' OR @CountryName = 'Northern Ireland' OR @CountryName = 'Netherland'
    BEGIN
      -- Europe 
      UPDATE Countries SET CountryGroupNumber = @OtherEurope WHERE CountryName = @CountryName 
    END

    IF @CountryName = 'Belize' OR @CountryName = 'Costa Rica' OR @CountryName = 'El Salvador' OR @CountryName = 'Guatemala' OR @CountryName = 'Honduras' OR 
      @CountryName = 'Nicaragua' OR @CountryName = 'Panama' 
    BEGIN
      --America
      UPDATE Countries SET CountryGroupNumber = @OtherMiddleAmerica WHERE CountryName = @CountryName 
    END

    IF @CountryName = 'Colombia' OR @CountryName = 'Argentina' OR @CountryName = 'Peru' OR @CountryName = 'Venezuela' OR @CountryName = 'Chile' OR
      @CountryName = 'Ecuador' OR @CountryName = 'Bolivia' OR @CountryName = 'Paraguay' OR @CountryName = 'Uruguay' OR @CountryName = 'Equador' OR
      @CountryName = 'Guyana' OR @CountryName = 'Suriname' OR @CountryName = 'French Guiana' OR @CountryName = 'Falkland Islands'
    BEGIN
      --America
      UPDATE Countries SET CountryGroupNumber = @OtherSouthAmerica WHERE CountryName = @CountryName 
    END

    IF @CountryName = 'Mexico' OR @CountryName = 'Guatemala' OR @CountryName = 'Cuba' OR @CountryName = 'Dominican Republic' OR @CountryName = 'Haiti' OR
      @CountryName = 'Honduras' OR @CountryName = 'Nicaragua' OR @CountryName = 'El Salvador' OR @CountryName = 'Costa Rica' OR
      @CountryName = 'Jamaica' OR @CountryName = 'Trinidad and Tobago' OR @CountryName = 'Guadeloupe' OR @CountryName = 'Martinique' OR @CountryName = 'Bahamas' OR
      @CountryName = 'Belize' OR @CountryName = 'Barbados' OR @CountryName = 'Saint Lucia' OR @CountryName = 'Curaçao' OR @CountryName = 'Aruba' OR
      @CountryName = 'Saint Vincent and the Grenadines' OR @CountryName = 'United States Virgin Islands' OR @CountryName = 'Grenada' OR @CountryName = 'Antigua and Barbuda' OR @CountryName = 'Dominica' OR
      @CountryName = 'Bermuda' OR @CountryName = 'Cayman Islands' OR @CountryName = 'Saint Kitts and Nevis' OR @CountryName = 'Sint Maarten' OR @CountryName = 'Turks and Caicos Islands' OR
      @CountryName = 'Saint Martin' OR @CountryName = 'British Virgin Islands' OR @CountryName = 'Caribbean Netherlands' OR @CountryName = 'Anguilla' OR @CountryName = 'Saint Barthélemy' OR
      @CountryName = 'Saint Pierre and Miquelon' OR @CountryName = 'Montserrat' OR @CountryName = 'Montserrat'
    BEGIN
      --America
      UPDATE Countries SET CountryGroupNumber = @OtherNorthAmerica WHERE CountryName = @CountryName 
    END

    FETCH NEXT FROM db_cur2 INTO @CountryName, @CountryGroupNumber
  END

  DEALLOCATE db_cur
  DEALLOCATE db_cur1
  DEALLOCATE db_cur2

  DROP TABLE #TempGroups
  DROP TABLE #TempCountries
END