Данная публикация является продолжением моей статьи про язык запросов DaJet QL. Пишу я её больше для себя и всех тех, кто заинтересовался технологией DaJet QL. Отвечаю таким образом на их вопросы.
Кроме этого, в этой статье я хочу рассказать о новых возможностях SQL Server 2016, которые позволяют эффективно работать с данными в формате JSON непосредственно в языке запросов. Совмещаю приятное с полезным =)
Дано:
1. Платформа 1С:Предприятие 8.3.15.1534
2. Microsoft SQL Server 2016 Express Edition
3. Управление торговлей, редакция 11.2 (11.2.3.159)
4. Бухгалтерия предприятия, редакция 3.0 (3.0.67.63)
Примеры:
1. Получение курсов валют из УТ в формате JSON.
2. Чтение курсов валют в формате JSON в БП.
3. Перенос курсов валют из УТ в БП одним запросом.
Обе задачи выполняются средствами языка запросов DaJet QL.
Ссылка где взять web сервер DaJet QL дана выше.
Синхронизация справочников "Валюты" выполняется по коду.
1. Получение курсов валют в формате JSON.
Запрос для DaJet QL:
DECLARE @period datetime = '4020-04-20';
SELECT
(
SELECT
DATEADD(YEAR, -2000, КурсыВалют.Период) AS [Period],
Валюты.Код AS [Currency],
КурсыВалют.Кратность AS [Factor],
КурсыВалют.Курс AS [Rate]
FROM
РегистрСведений.КурсыВалют AS КурсыВалют
INNER JOIN Справочник.Валюты AS Валюты
ON КурсыВалют.Валюта.uuid = Валюты.Ссылка.uuid
WHERE
КурсыВалют.Период = @period
FOR JSON PATH
) AS json
Результат в формате JSON:
[
{
"Period": "2020-04-20T00:00:00",
"Currency": "840",
"Factor": 1,
"Rate": 65.1234
},
{
"Period": "2020-04-20T00:00:00",
"Currency": "978",
"Factor": 1,
"Rate": 70.4321
}
]
2. Чтение курсов валют в формате JSON.
Запрос для DaJet QL:
DECLARE @json nvarchar(max) = N'
[
{
"Period" : "2020-04-20T00:00:00",
"Currency" : "840",
"Factor" : 1,
"Rate" : 65.1234
},
{
"Period" : "2020-04-20T00:00:00",
"Currency" : "978",
"Factor" : 1,
"Rate" : 70.4321
}
]';
SELECT
DATEADD(YEAR, 2000, JsonTable.[Период]) AS [Период],
JsonTable.[Валюта] AS [КодВалюты],
JsonTable.[Кратность] AS [Кратность],
JsonTable.[Курс] AS [Курс]
FROM
OPENJSON(@json)
WITH (
[Период] datetime '$.Period',
[Валюта] nvarchar(3) '$.Currency',
[Кратность] numeric(10) '$.Factor',
[Курс] numeric(10,4) '$.Rate'
) AS JsonTable
INNER JOIN Справочник.Валюты AS Валюты
ON JsonTable.Валюта = Валюты.Код;
Результат в табличном виде:
|
Период |
КодВалюты |
Кратность |
Курс |
|
4020-04-20T00:00:00 |
840 |
1 |
65,1234 |
|
4020-04-20T00:00:00 |
978 |
1 |
70,4321 |
3. Перенос курсов валют из УТ в БП одним запросом.
Запрос для DaJet QL:
DECLARE @period datetime = '4020-04-20';
INSERT [accounting_3_0_72_72_demo].РегистрСведений.КурсыВалют
(
Период,
Валюта.uuid,
Кратность,
Курс,
ОбластьДанныхОсновныеДанные
)
SELECT
КурсыВалют.Период AS [Период],
БПВалюты.Ссылка.uuid AS [Валюта],
КурсыВалют.Кратность AS [Кратность],
КурсыВалют.Курс AS [Курс],
0 AS [ОбластьДанныхОсновныеДанные]
FROM
[trade_11_2_3_159_demo].РегистрСведений.КурсыВалют AS КурсыВалют
INNER JOIN [trade_11_2_3_159_demo].Справочник.Валюты AS УТВалюты
ON КурсыВалют.Валюта.uuid = УТВалюты.Ссылка.uuid
INNER JOIN [accounting_3_0_72_72_demo].Справочник.Валюты AS БПВалюты
ON УТВалюты.Код = БПВалюты.Код
WHERE
КурсыВалют.Период = @period
Результат:
Курсы валют перенесены из УТ в регистр сведений "КурсыВалют" БП.
Примечания:
В этих примерах используется два способа передачи параметров в запросы DaJet QL. Для этого можно использовать возможности оператора DECLARE, а также функционал SQL Server 2016 и выше для передачи табличных данных в запрос в формате JSON.
При этом стоит отметить, что технику получения табличных данных из JSON во 2-ом примере можно успешно использовать в 3-ем примере для вставки этих данных в таблицу СУБД.
Для организации взаимодействия между удалёнными серверами SQL Server, которые не являются связанными (linked), можно использовать технику получения данных в формате JSON на одном сервере, с последующей передачей и обработкой этих данных на втором сервере. Для этого можно установить на этих серверах по одному web серверу DaJet QL, а управлять ими вообще при помощи третьего.
