CREATE PROCEDURE dbo.CountriesConvert AS

/*
20150421 BUG/CLN. 
  Due to enumerated Delphi values the Translation table value of Country is 7 not 6
  and the Translation table value of currency is 8 not 7
  Fixed, but we actually don't need values for countries or currencies in the Translation table

  20110624/RN: Initial version
*/

-- This is a stored procedure based on the old CountriesConvert webupdate script
BEGIN

  DECLARE
    @UserIDNumber int,
    @HotelCountryNumber int,
    @PK_Country int,
    @CountryNumber int,
    @CountryName varchar(30),
    @CurrencyBank money,
    @CurrencyExchange money,
    @CurrenctSettlement money,
    @CurrencyProduct money,
    @SortNumber int,
    @LanguageCode varchar(5),
    @LanguageCode2 varchar(5),
    @LanguageCode3 varchar(5),
    @PhoneFormat varchar(30),
    @ISO_3166_A2 char(2),
    @ISO_3166_A3 char(3),
    @PK_Currency int,
    @CurrencyName varchar(30),
    @CurrencyCode varchar(3),
    @DateStamp smalldatetime,
    @FK_Log int


  SELECT @UserIDNumber=UserIDNumber FROM Personel WITH (NOLOCK) WHERE UserTypeNumber=2
  SELECT @HotelCountryNumber=KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='CountryCode'

  DECLARE #Country CURSOR LOCAL FAST_FORWARD FOR
    SELECT CountryNumber, CountryName, CurrencyBank, CurrencyExchange, CurrencySettlement, CurrencyProduct, SortNumber, LanguageCode, LanguageCode2, LanguageCode3, PhoneFormat, ISO_3166_A2, ISO_3166_A3, CurrencyName, CurrencyCode FROM Countries WITH (NOLOCK) WHERE MarkAsDeleted<>1
  OPEN #Country

  FETCH NEXT FROM #Country
  INTO @CountryNumber, @CountryName, @CurrencyBank, @CurrencyExchange, @CurrenctSettlement, @CurrencyProduct, @SortNumber, @LanguageCode, @LanguageCode2, @LanguageCode3, @PhoneFormat, @ISO_3166_A2, @ISO_3166_A3, @CurrencyName, @CurrencyCode

  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    -- Country
    IF NOT EXISTS(SELECT * FROM Country WITH (NOLOCK) WHERE FK_OldCountry = @CountryNumber)
    BEGIN
      INSERT INTO Country (ISO_3166_A2, ISO_3166_A3, SortNumber, LanguageCode, LanguageCode2, LanguageCode3, PhoneFormat, FK_OldCountry) 
      VALUES (@ISO_3166_A2, @ISO_3166_A3, @SortNumber, @LanguageCode, @LanguageCode2, @LanguageCode3, @PhoneFormat, @CountryNumber)
      --SELECT @PK_Country=@@identity
      --INSERT INTO Translation (ObjectNumber, CountryNumber, Type, Name) VALUES (@PK_Country, @HotelCountryNumber, 7, @CountryName)
    END   
    ELSE
    BEGIN
      UPDATE Country
      SET
        ISO_3166_A2 = @ISO_3166_A2, 
        ISO_3166_A3 = @ISO_3166_A3, 
        SortNumber = @SortNumber, 
        LanguageCode = @LanguageCode, 
        LanguageCode2 = @LanguageCode2, 
        LanguageCode3 = @LanguageCode3, 
        PhoneFormat = @PhoneFormat
      WHERE FK_OldCountry = @CountryNumber
      SELECT @PK_Country=PK_Country FROM Country WITH (NOLOCK) WHERE FK_OldCountry = @CountryNumber
    END

    -- Currency
    IF ISNULL(@CurrencyCode,'') <> ''
    BEGIN
      IF NOT EXISTS(SELECT * FROM Currency WITH (NOLOCK) WHERE CurrencyCode = @CurrencyCode)
      BEGIN
        INSERT INTO Currency (CurrencyCode) VALUES (@CurrencyCode)
        --SELECT @PK_Currency=@@identity
        --INSERT INTO Translation (ObjectNumber, CountryNumber, Type, Name) VALUES (@PK_Currency, @HotelCountryNumber, 8, @CurrencyName)
      END
      ELSE
      BEGIN
        SELECT @PK_Currency = PK_Currency FROM Currency WITH (NOLOCK) WHERE CurrencyCode = @CurrencyCode
      END

      IF NOT EXISTS(SELECT * FROM CountryCurrencyXTab WITH (NOLOCK) WHERE FK_Country = @PK_Country AND FK_Currency = @PK_Currency)
      BEGIN
        INSERT INTO CountryCurrencyXTab (FK_Country, FK_Currency) VALUES (@PK_Country, @PK_Currency)

        -- CurrencyRates
        SET @DateStamp=0
        INSERT INTO LogPicasso(DateStamp,UserIDNumber,Text) VALUES (@DateStamp, @UserIDNumber, 'Added via script')
        SELECT @FK_Log=@@identity
        INSERT INTO CurrencyRate (FK_Currency, FK_Log, DateStamp, Bank, Exchange_, Settlement, Product) VALUES (@PK_Currency, @FK_Log, @DateStamp, @CurrencyBank, @CurrencyExchange, @CurrenctSettlement, @CurrencyProduct)
      END
    END
    FETCH NEXT FROM #Country
    INTO @CountryNumber, @CountryName, @CurrencyBank, @CurrencyExchange, @CurrenctSettlement, @CurrencyProduct, @SortNumber, @LanguageCode, @LanguageCode2, @LanguageCode3, @PhoneFormat, @ISO_3166_A2, @ISO_3166_A3, @CurrencyName, @CurrencyCode
  END

  CLOSE #Country
  DEALLOCATE #Country

END
