При разработке отчетов в Системе компоновки данных (СКД) для конфигураций на платформе 1С:Предприятие 8 (таких как «Управление торговлей 10.3» или «Управление производственным предприятием 1.3») разработчики часто сталкиваются с необходимостью вывода курса валюты и кратности взаиморасчетов из первичных документов оплаты. Типичный сценарий: строится отчет по остаткам и оборотам регистра ВзаиморасчетыСКонтрагентами, и в него требуется подтянуть курс, зафиксированный в табличной части конкретного платежного поручения. Однако при выполнении запроса поля курса и кратности выводятся со значением NULL.
В этой статье мы подробно разберем, почему возникает данная проблема, проанализируем ошибки в исходном запросе, рассмотрим опасности неверных архитектурных решений (которые могут приводить к задвоению сумм) и спроектируем правильное, производительное решение для СКД.
---
Рассмотрим условие соединения таблиц, которое использовал автор вопроса в своем исходном запросе:
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПлатежноеПоручениеВходящее.РасшифровкаПлатежа КАК ПлатежноеПоручениеВходящееРасшифровкаПлатежа
ПО ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Сделка.Ссылка = ПлатежноеПоручениеВходящееРасшифровкаПлатежа.Ссылка.Ссылка
Выделим ключевые методологические и технические ошибки в данном фрагменте кода:
Сделка в регистре взаиморасчетов — это документ-основание (например, Заказ покупателя или Счет на оплату). Поле Ссылка в табличной части документа (в конструкции Ссылка.Ссылка) — это ссылка на сам платежный документ (Платежное поручение входящее). Ссылка на заказ покупателя никогда не будет равна ссылке на платежное поручение. Из-за этого левое соединение не находит совпадений и возвращает значение NULL для всех колонок правой таблицы.
Сделка.Ссылка — это грубейшая ошибка проектирования запросов в 1С. Поле Сделка имеет составной тип данных. Обращение через точку к реквизитам составного поля заставляет СУБД генерировать тяжелые неявные соединения (LEFT JOIN) со всеми физическими таблицами документов, входящих в этот тип. Это приводит к колоссальному падению производительности на реальных базах данных. Ссылки необходимо сравнивать напрямую: Сделка = ДругаяСделка.
Ссылка.Ссылка является избыточным дублированием. Поле Ссылка табличной части уже хранит в себе прямую ссылку на документ-владелец. Достаточно писать просто Ссылка.
---
В обсуждении проблемы на форумах часто предлагают «простое» решение: связать таблицы по полю сделки:
ПО ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Сделка = ПлатежноеПоручениеВходящееРасшифровкаПлатежа.Сделка
Рассмотрим, почему такое решение является крайне опасным для отчетов по остаткам и оборотам:
Если в рамках одной сделки (например, одного заказа покупателя) было сделано несколько платежей (или платежный документ содержит несколько строк в табличной части по одной и той же сделке), то при левом соединении виртуальной таблицы регистра с табличной частью документа произойдет декартово произведение строк. Каждая строка остатков из регистра продублируется столько раз, сколько раз данная сделка встречается в платежных поручениях. В результате все начальные остатки, конечные остатки и общие обороты в отчете увеличатся кратно количеству оплат, что полностью исказит финансовую картину и приведет к недостоверности отчета.
---
Чтобы сопоставить движение по регистру с конкретным курсом валюты из платежного документа, необходимо связать запись регистра с документом, который это движение породил, то есть с Регистратором. Выясним, как сделать это по шагам:
Шаг 1. Настройка периодичности виртуальной таблицы.
В параметрах виртуальной таблицы ОстаткиИОбороты необходимо обязательно настроить периодичность. Если оставить периодичность по умолчанию (Период или Авто), поле Регистратор будет недоступно в запросе, либо данные не будут детализированы до конкретных регистраторов движения.
Для решения нашей задачи установим периодичность в значение Регистратор:
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , ) КАК Взаиморасчеты
Шаг 2. Наложение условий связи по аналитике.
Просто связать таблицы по условию Регистратор = Ссылка недостаточно. В одном платежном поручении в табличной части может быть указано несколько строк с разными договорами или сделками. Чтобы соединение отработало корректно и не вызвало дублирования строк, мы обязаны связать таблицы по регистратору и всей ключевой аналитике взаиморасчетов:
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПлатежноеПоручениеВходящее.РасшифровкаПлатежа КАК Расшифровка
ПО Взаиморасчеты.Регистратор = Расшифровка.Ссылка
И Взаиморасчеты.ДоговорКонтрагента = Расшифровка.ДоговорКонтрагента
И Взаиморасчеты.Сделка = Расшифровка.Сделка
Шаг 3. Обработка пустых сделок (Важно для УТ 10.3).
В конфигурации «Управление торговлей 10.3» взаиморасчеты часто ведутся в разрезе договоров («По договору в целом»), а не по заказам. В таком случае поле Сделка в регистре и в документе будет пустым (значение НЕОПРЕДЕЛЕНО или пустая ссылка). Прямое сравнение Взаиморасчеты.Сделка = Расшифровка.Сделка для пустых значений составного типа на уровне СУБД может отработать некорректно или привести к потере связи.
Для обеспечения надежности запроса условие соединения по сделке следует сделать гибким:
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПлатежноеПоручениеВходящее.РасшифровкаПлатежа КАК Расшифровка
ПО Взаиморасчеты.Регистратор = Расшифровка.Ссылка
И Взаиморасчеты.ДоговорКонтрагента = Расшифровка.ДоговорКонтрагента
И (Взаиморасчеты.Сделка = Расшифровка.Сделка
ИЛИ (Взаиморасчеты.Сделка ЕСТЬ NULL И Расшифровка.Сделка ЕСТЬ NULL))
---
В исходном запросе автора темы использовался пакет запросов со сверткой через СУММА и МАКСИМУМ во временной таблице, но при этом отсутствовала обязательная секция СГРУППИРОВАТЬ ПО. Без группировки использование агрегатных функций сворачивает весь результат в одну единственную строку, что возвращает некорректные данные.
Поскольку мы проектируем отчет на СКД, нам нет необходимости делать ручную группировку числовых полей на уровне SQL-запроса. СКД намного эффективнее справляется с расчетом итогов по группировкам на уровне собственных механизмов компоновки. Нам достаточно написать один простой, "плоский" и производительный запрос.
Рассмотрим пример правильного и оптимизированного запроса для схемы компоновки данных:
ВЫБРАТЬ
Взаиморасчеты.Организация КАК Организация,
Взаиморасчеты.Контрагент КАК Контрагент,
Взаиморасчеты.ДоговорКонтрагента КАК ДоговорКонтрагента,
Взаиморасчеты.Сделка КАК Сделка,
Взаиморасчеты.Регистратор КАК Регистратор,
Взаиморасчеты.СуммаВзаиморасчетовНачальныйОстаток КАК СуммаВзаиморасчетовНачальныйОстаток,
Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток КАК СуммаВзаиморасчетовКонечныйОстаток,
Взаиморасчеты.СуммаВзаиморасчетовПриход КАК СуммаВзаиморасчетовПриход,
Взаиморасчеты.СуммаВзаиморасчетовРасход КАК СуммаВзаиморасчетовРасход,
ВЫБОР
КОГДА РасшифровкаПлатежа.КурсВзаиморасчетов ЕСТЬ NULL ТОГДА 1
ИНАЧЕ РасшифровкаПлатежа.КурсВзаиморасчетов
КОНЕЦ КАК КурсВзаиморасчетов,
ВЫБОР
КОГДА РасшифровкаПлатежа.КратностьВзаиморасчетов ЕСТЬ NULL ТОГДА 1
ИНАЧЕ РасшифровкаПлатежа.КратностьВзаиморасчетов
КОНЕЦ КАК КратностьВзаиморасчетов
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , ) КАК Взаиморасчеты
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПлатежноеПоручениеВходящее.РасшифровкаПлатежа КАК РасшифровкаПлатежа
ПО Взаиморасчеты.Регистратор = РасшифровкаПлатежа.Ссылка
И Взаиморасчеты.ДоговорКонтрагента = РасшифровкаПлатежа.ДоговорКонтрагента
И Взаиморасчеты.Сделка = РасшифровкаПлатежа.Сделка
---
После написания правильного текста запроса необходимо корректно настроить схему компоновки данных в конфигураторе:
СуммаВзаиморасчетовНачальныйОстаток, СуммаВзаиморасчетовПриход и т.д.) укажем выражение расчета ресурса — СУММА(ИмяПоля).КурсВзаиморасчетов, КратностьВзаиморасчетов) в качестве ресурса укажем выражение МАКСИМУМ(КурсВзаиморасчетов) или СРЕДНЕЕ(КурсВзаиморасчетов). Это позволит избежать суммирования курсов валют при выводе итогов по группировкам контрагентов и договоров.Такой подход гарантирует высокую скорость выполнения отчета за счет отсутствия лишней вложенности запросов, исключает дублирование финансовых сумм при наличии нескольких документов оплаты по одной сделке и корректно выводит курс и кратность документов без возвращения значений NULL.