Интеграция API Курсы валют Центрального Банка РФ

Смотреть демостенд по получению котировок ЦБ РФ API

Описание обмена по API между БД FALCON и сайтами, предоставляющими информацию о курсах валют.

Полные тексты процедур приведены в приложении 1.

1 Центральный Банк РФ

Описание - http://www.cbr.ru/development/SXML/

для получения котировок на заданный день

нужно отправить запрос вида:

http://www.cbr.ru/scripts/XML_daily.asp?date_req=02/03/2002

где date_req= Date of query (dd/mm/yyyy)

* если параметр(date_req) отсутствует, то Вы получите документ на последнюю зарегистрированную дату.

PROCEDURE [dbo].[api_currencyRatesCBR_request]

Параметры запроса

Адрес сервиса (URL): http://www.cbr.ru/scripts/XML_daily.asp?
Метод запроса: GET
Формат на выходе: XML

Входные данные запроса –нет.

Дата не передаётся, при необходимости получить исторические данные запрос будет иметь вид.

http://www.cbr.ru/scripts/XML_daily.asp?date_req=04/06/2020

где date_req= Date of query (dd/mm/yyyy)

Примечание 1. В некоторых ситуациях, например, когда запрос отправлен вечером – актуальные данные на завтра (Дата в будущем).

Недостатки. Ответ запроса приходит в XML, кодировка windows-1251.

В некоторых браузерах кириллические символы (Доллар США) выводятся нечитаемыми символами.

Пример текста ответа - response.

Приведен с сокращениями.

<!--?xml version="1.0" encoding="windows-1251"?-->
<valcurs date="06.06.2020" name="Foreign Currency Market">
…
<valute id="R01235">
        <numcode>840</numcode>
        <charcode>USD</charcode>
        <nominal>1</nominal>
        <name>Доллар США</name>
        <value>68,6319</value>
    </valute>
    <valute id="R01239">
        <numcode>978</numcode>
        <charcode>EUR</charcode>
        <nominal>1</nominal>
        <name>Евро</name>
        <value>77,9658</value>
    </valute>
…
</valcurs>

CREATE PROCEDURE [dbo].[api_currencyRatesCBR_request]
	@parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2)
	@username nvarchar(32)  -- текущий пользователь.
    --
    ,@date_req  nvarchar(10) = ''
AS
BEGIN
--  date_req= Date of query (dd/mm/yyyy)
-- * если параметр(date_req) отсутствует, то Вы получите документ на последнюю зарегистрированную дату.
-- 103 = дд/мм/гггг = 04/06/2020
  IF  LEN(  ISNULL ( @date_req   , '' ) )=0
  -- SET @date_req  =  CONVERT ( nvarchar(10) , GETDATE() , 103 )
  SET @date_req  =  CONVERT ( nvarchar(10) , GETDATE() +1 , 103 )  	-- ПО ФАКТУ ВСЕГДА ЕСТЬ КУРС НА ЗАВТРА !!!
--  exec as_print @str = @date_req


DECLARE @apiUrl 		nvarchar(max) = '' 		-- Финальная строка для отправки -- @apiStr -- ФАКТИЧЕСКИ ЭТО Url
SET     @apiUrl = 'http://www.cbr.ru/scripts/XML_daily.asp?'
-- ОТЛАДКА
-- SET     @apiUrl = 'http://www.cbr.ru/scripts/XML_daily.asp?date_req=04/06/2020'
-- SET     @apiUrl = 'http://www.cbr.ru/scripts/XML_daily.asp?date_req=' + ISNULL ( @date_req  , '' )

    -- ОТЛАДКА
	insert into as_trace ( header, text, username, code , created )   values( '@apiUrl', @apiUrl , @username, 'api_currencyRatesCBR_request' , GETDATE() )


	-- SELECT 1  Msg, Result, Url (адрес, куда будет идти запрос)
	select '' Msg, 1 Result, @apiUrl Url

	-- SELECT 2 PARAMETERS - параметры, которые будут передаваться во внешний источник
	-- select 'id' name, '5' value, '' [type] -- form (в форме передается), header (в http headers), get запросы передавайте прямо в URL
END

PROCEDURE [dbo].[api_currencyRatesCBR_response]

Результат запроса может быть как сообщение с информацией, так и сообщение с ошибкой.

Список возможных сообщение об ошибках.

1 - Ошибка! В результате запроса @response нет корневого элемента ValCurs!

Если ошибок в ответе не обнаружено, из ответа выбираться такие данные:

Курс Доллар США и Евро.

Пример текста – результат: Курс на 06.06.2020 USD - Доллар США: 68,6319. EUR - Евро: 77,9658.

CREATE PROCEDURE [dbo].[api_currencyRatesCBR_response]
	@response nvarchar(max),
	@parameters ExtendedDictionaryParameter READONLY,  -- входящие параметры для внутренней обработки (используйте Key, Value2 - те же что и на request)

	@username nvarchar(32)
AS
BEGIN

    -- ОТЛАДКА
	insert into as_trace ( header, text, username, code , created )  values( '@response - 1    , 1024 ', SUBSTRING ( @response , 1    , 1024 ) , @username, 'api_currencyRatesCBR_response' , GETDATE() )



DECLARE  @xml_STR Nvarchar(max)
SET      @xml_STR = @response

DECLARE  @xml xml -- переменная в которую преобразуем полученную строку

-- Условия работы / Конвертация строка - xml
-- 1. Тип данных Nvarchar(max)
-- 2. Unicode string with N == > Строки начинаются с N'== >N' "UTF-16"

-- ПРИХОДИТ КОДИРОВКА encoding="windows-1251"
-- set @xml_STR = REPLACE( @xml_STR ,'UTF-8','UTF-16')
set @xml_STR = REPLACE( @xml_STR ,'windows-1251','UTF-16')
set @xml =convert (xml, @xml_STR)
-- select @xml

DECLARE @exist_ValCurs bit = 0
select  @exist_ValCurs = @xml.exist('/ValCurs')

      IF @exist_ValCurs = 0
      BEGIN
          SELECT 'Ошибка! В результате запроса @response нет корневого элемента ValCurs! ' Msg, 0 Result, @response Response
          RETURN
      END

-- OpenXML --
DECLARE @hdoc   int -- Дескриптор - указатель (handle) на создаваемую структуру дерева XML (потом будет использоваться в OPENXML и sp_xml_removedocument);

-- подготовка документа для работы - присвоение Дескриптор @hdoc
-- теперь к данным @xml можно обратиться FROM OPENXML (@hdoc ...
EXEC sp_xml_preparedocument @hdoc OUTPUT , @xml



		-- Во врем. переменную
DECLARE @tmp_currencyRates Table ( id int identity not null
                                  -- , ValDate Nvarchar(100) null
								  , NumCode Nvarchar(100) null, CharCode  Nvarchar(100) null
								  , Nominal Nvarchar(100) null, Name  Nvarchar(100) null, Value Nvarchar(100) null
								  )

-- Дата
DECLARE @valuteDate Nvarchar(32)
SELECT  @valuteDate = X.ValCurDate  FROM OPENXML (@hdoc, '/ValCurs',2) WITH ( ValCurDate Nvarchar(100) '@Date') AS X
-- SELECT @ValCurDate


-- все строки дублируются по 5 раза - по числу полей - ПОСТАВИТЬ DISTINCT
-- SELECT DISTINCT X. *
INSERT INTO  @tmp_currencyRates
SELECT DISTINCT X.NumCode , X.CharCode ,   X.Nominal  , X.Name ,   X.Value
		FROM OPENXML (@hdoc, '/ValCurs/Valute/*',2)      -- * - В Конце строки - работает
				WITH (  --  '.' -- ТОЧКА - ВЫВОД ТЕКУЩЕЙ СТРОКИ -- ТУТ НЕ РАБОТАЕТ
						NumCode  Nvarchar(100) '../NumCode' -- РАБОТАЕТ -- '../CharCode'
					  , CharCode Nvarchar(100) '../CharCode'
					  , Nominal  Nvarchar(100) '../Nominal'
					  , Name	 Nvarchar(100) '../Name'
					  , Value    Nvarchar(100) '../Value'
					   )
					   AS X

 -- Удалить дескриптор
EXEC sp_xml_removedocument @hdoc
-- SELECT * FROM @tmp_currencyRates

    DECLARE  @USDCharCode NVARCHAR(MAX) ,@USDName NVARCHAR(MAX) ,@USDValue NVARCHAR(MAX) , @USDNominal NVARCHAR(MAX)
	        ,@EURCharCode NVARCHAR(MAX) ,@EURName NVARCHAR(MAX) ,@EURValue NVARCHAR(MAX) , @EURNominal NVARCHAR(MAX)

SELECT @USDCharCode = CharCode ,@USDName = Name ,@USDValue = Value ,@USDNominal = Nominal
FROM @tmp_currencyRates WHERE CharCode = 'USD'

SELECT @EURCharCode = CharCode  ,@EURName = Name ,@EURValue = Value , @EURNominal  = Nominal
FROM @tmp_currencyRates WHERE CharCode = 'EUR'
-- -- Курс 69,0151 за 1(Nominal) = Доллар США (Name)

	-- SELECT 1
	select 	 'Курс на ' + ISNULL( @valuteDate, '' )
           + ' ' + ISNULL( @USDCharCode, '' ) +' - '+  ISNULL( @USDName, '' ) +': '+ ISNULL( @USDValue  , '' ) +'.'
           + ' ' + ISNULL( @EURCharCode, '' ) +' - '+  ISNULL( @EURName, '' ) +': '+ ISNULL( @EURValue  , '' ) +'.'

            AS Msg
          , 1 Result, @response Response

	-- SELECT 2 Внешние действия

END

2 Альтернатива – сайт www.cbr-xml-daily.ru

Сайт - https://www.cbr-xml-daily.ru/ предоставляет те же данные в разных форматах. В нашем случае будет получен ответ в формате JSON.

PROCEDURE [dbo].[api_currencyRatesCBRF_JSON_request]