CREATE PROCEDURE [dbo].[CurrencyRateSync] @DateStamp datetime, @UserIDNumber integer AS
SET NOCOUNT ON
/*
    Description: Temp stored proc to sync rates in CurrencyRate with old table Countries. This SP will be deleted when the old tables and code is eliminated.

--  AV 20070509 THIS PROCEDURE IS CURRENTLY DISABLED, BECAUSE THE NEW CURRENCY TABLES ARE NOT USED YET BY PICASSO (IT SHOULD HAVE BEEN USED FOR GRAND HOTEL REJKJVAVIK)

    Change log:
    4.1+
    BA/081209: This SP has been re-enabled because Grand Hotel Reykjavik needs it!

    3.39:
    JP 20070313: EXT: use server datetime instead of systemdate + server time when writing to CurrencyRate. NB! systemdate + server time (@DateStamp) is still used for LogPicasso
    JP 20070228: BUG: smalldatetime has no seconds which gives problems if you change rate and within the same minute refreshes in booking. You'll end up with the old rate when done within last 30 seconds of a minute
    3.38:
    JP 20061107: Created
  
*/
--RETURN
DECLARE
@CountryNumber int,
@CurrencyBank money, @Bank smallmoney,
@CurrencyExchange money, @Exchange smallmoney,
@CurrencySettlement money, @Settlement smallmoney,
@CurrencyProduct money, @Product smallmoney,
@FK_Currency int,
@FK_Log int

DECLARE #Country CURSOR LOCAL FAST_FORWARD FOR

  SELECT CountryNumber, CurrencyBank, CurrencyExchange, CurrencySettlement, CurrencyProduct FROM Countries WITH (NOLOCK) WHERE MarkAsDeleted<>1

OPEN #Country

FETCH NEXT FROM #Country
INTO @CountryNumber, @CurrencyBank, @CurrencyExchange, @CurrencySettlement, @CurrencyProduct

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  -- find newest rates to compare with
  SELECT TOP 1 @FK_Currency=CountryCurrencyXTab.FK_Currency, @Bank=Bank, @Exchange=Exchange_, @Settlement=Settlement, @Product=Product FROM CountryCurrencyXTab WITH (NOLOCK)
  INNER JOIN Country WITH (NOLOCK)  ON PK_Country=FK_Country AND FK_OldCountry=@CountryNumber
-- BA/081209: We need the top record as this one! It is ordered so we will get the latest record. The date criterial does not work in all cases.
--            Management has been changed so you can not enter currencies unless the Picasso date is the same as the computer date.
--  LEFT OUTER JOIN CurrencyRate(NOLOCK) ON CountryCurrencyXTab.FK_Currency=CurrencyRate.FK_Currency AND DateStamp<=@DateStamp -- not certain that there is a row here!
  LEFT OUTER JOIN CurrencyRate WITH (NOLOCK) ON CountryCurrencyXTab.FK_Currency=CurrencyRate.FK_Currency
  ORDER BY DateStamp DESC

  IF @@rowcount = 1
  BEGIN
    IF (@Bank<>@CurrencyBank) OR (@Exchange<>@CurrencyExchange) OR (@Settlement<>@CurrencySettlement) OR (@Product<>@CurrencyProduct) -- were the rates changed?
    BEGIN

      INSERT INTO LogPicasso(DateStamp,UserIDNumber,Text) VALUES (@DateStamp, @UserIDNumber, 'Rate Changed')
      SELECT @FK_Log=@@identity
      INSERT INTO CurrencyRate (FK_Currency, FK_Log, DateStamp, Bank, Exchange_, Settlement, Product) VALUES (@FK_Currency, @FK_Log, @DateStamp, @CurrencyBank, @CurrencyExchange, @CurrencySettlement, @CurrencyProduct)
--      INSERT INTO CurrencyRate (FK_Currency, FK_Log, DateStamp, Bank, Exchange_, Settlement, Product) VALUES (@FK_Currency, @FK_Log, GetDate(), @CurrencyBank, @CurrencyExchange, @CurrencySettlement, @CurrencyProduct)

    END
  END
  ELSE -- no rates found in new table. insert
  BEGIN

      INSERT INTO LogPicasso(DateStamp,UserIDNumber,Text) VALUES (@DateStamp, @UserIDNumber, 'Rate Added')
      SELECT @FK_Log=@@identity
      INSERT INTO CurrencyRate (FK_Currency, FK_Log, DateStamp, Bank, Exchange_, Settlement, Product) VALUES (@FK_Currency, @FK_Log, @DateStamp, @CurrencyBank, @CurrencyExchange, @CurrencySettlement, @CurrencyProduct)
--      INSERT INTO CurrencyRate (FK_Currency, FK_Log, DateStamp, Bank, Exchange_, Settlement, Product) VALUES (@FK_Currency, @FK_Log, GetDate(), @CurrencyBank, @CurrencyExchange, @CurrencySettlement, @CurrencyProduct)

  END

  FETCH NEXT FROM #Country
  INTO @CountryNumber, @CurrencyBank, @CurrencyExchange, @CurrencySettlement, @CurrencyProduct
END

CLOSE #Country
DEALLOCATE #Country

