Работа с данными с помощью SQL

Приложение загружает в хранилище данные “как есть” , т.е. без изменений. Далее, пользователь путем написания SQL запросов может получить необходимый результат. Рассмотрим ниже несколько примеров.

Как связать таблицу документов со справочником

Рассмотрим, как связать таблицу документов Заказы покупателя brv_customerorder со справочником Контрагентов brv_counterparty.

Если сделать SQL запрос к таблице Заказов покупателей и вывести в запросе поле agent, то увидим, что это поле содержит ссылку-идентификатор на справочник Контрагентов.

Пример кода:

select c.name as number_doc, -- номер документа
       c.moment as date_doc, -- дата документа
       c.agent as counterparty, -- ссылка на контрагента 
from `br01-309010.test.brv_customerorder` c -- таблица заказов покупателей

SQL

Пример выполнения запроса:

В справочнике Контрагентов данный ключ находится в поле href.
Пример кода:

select href, -- ссылка на контрагента
       name -- наименование контрагента
from `br01-309010.test.brv_counterparty` -- справочник контрагентов

SQL

Пример выполнения запроса:

Далее, сделаем запрос для объединения двух таблиц по соответствующим полям:

Пример кода:

select c.name as number_doc, -- номер документа
       c.moment as date_doc, -- дата документа
       a.name as counterparty, -- наименование контрагента 
from `br01-309010.test.brv_customerorder` c -- таблица заказов покупателей
join br01-309010.test.brv_counterparty a on a.href = c.agent -- связь со справочником контрагентов

SQL

Пример выполнения запроса:

Как связать документы с позициями товаров в них

Информация о позициях всех документах находится в таблицах с именами brv_сущность_postions. Вместо сущности может использоваться любой документ.

Рассмотрим, как связать таблицу документов Заказы покупателя brv_customerorder с соответствующей таблицей позиций brv_customerorder_postions.

В обеих таблицах есть поле entity_id (id документа), именно по этому полю и можно сделать связь. Пример:

select c.name as number_doc, -- номер документа
       c.moment as date_doc, -- дата документа
       cp.price, -- цена
       cp.quantity, -- количество
       cp.discount, -- скидка
       cp.vat -- НДС
from `br01-309010.test.brv_customerorder` c -- заказы покупателей
join `br01-309010.test.brv_customerorder_positions` cp -- позиции заказов
ON cp.entity_id = c.entity_id -- связь по полю entity_id

SQL

Пример выполнения запроса:

Таким же образом связываются и все остальные документы, например, Отгрузки brv_demand и Позиции отгрузок brv_demand_positions :

select d.name, -- номер документа
       d.moment, -- дата документа
       dp.price, -- цена
       dp.quantity, -- количество
from `br01-309010.test.brv_demand` d -- отгрузки
join `br01-309010.test.brv_demand_positions` dp -- позиции
ON dp.entity_id = d.entity_id

SQL

Пример выполнения запроса:

Поле цены и суммы в МойСклад:

Цены и суммы API МоегоСклада хранятся в копейках, поэтому если вам нужно перевести значение в рубли, то необходимо разделить соответствующее поле на 100. Пример:

select  cast(price as float64) /100 from `br01-309010.test.brv_demand_positions`

SQL

Для удобства и быстроты расчетов мы создали в хранилище функции BigQuery для расчетов суммы, скидок и т.д.: https://brsys.atlassian.net/wiki/spaces/EBQ/pages/425394218/BigQuery#%D0%9F%D0%B5%D1%80%D0%B5%D1%87%D0%B5%D0%BD%D1%8C-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D0%B9

Дополнительные поля из МоегоСклада

Дополнительные поля из любого документа или сущности находятся в поле attributes соответствующей таблицы. Например, если вы создали в МоемСкладе дополнительные поля для Заказов покупателю, то в таблице brv_customerorder вы сможете найти данное поле.

В поле attributes находится массив с перечнем значений дополнительных полей относящихся к данному документу или сущности. Получить название определенного поля можно с помощью функции brf_get_atr_value, подробнее об ее использовании написано здесь - https://brsys.atlassian.net/wiki/spaces/EBQ/pages/425394218/BigQuery#%D0%9F%D0%B5%D1%80%D0%B5%D1%87%D0%B5%D0%BD%D1%8C-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D0%B9

Если дополнительное поле представляет собой пользовательский справочник значений, то функция brf_get_atr_value вернет ссылку на элемент справочника. Само значение можно получить через связь с таблицей brv_customentity.