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

Перевод отчетов, написанных «ручным» кодом во внешних обработках, на Систему компоновки данных (СКД) — это стандартный шаг для улучшения интерфейса, повышения гибкости настроек и ускорения работы системы. Однако разработчики часто сталкиваются с классическими трудностями: при выводе номенклатуры из табличных частей документов начинают дублироваться итоговые суммы заказов (происходит «размножение» строк), а при неверно настроенных связях и отборах — пропадают контрагенты.

В этой статье мы подробно разберем, почему возникают эти проблемы, проанализируем архитектурные особенности работы запросов СУБД в среде 1С и рассмотрим два эффективных способа реализации такого отчета в СКД.

Анализ исходных ошибок в запросе

Давайте посмотрим на исходный запрос, который использовался в кодовой реализации, и выясним, почему он вызывает проблемы при прямолинейном переносе в СКД:

ВЫБРАТЬ
    ЗаказЗаказ.Контрагент КАК Контрагент,
    ЗаказЗаказ.Ссылка КАК Заказ,
    ЗаказЗаказ.СуммаДокумента КАК Сумма,
    ЕСТЬNULL(РасчетыСКлиентамиОстаткиИОбороты.КОплатеРасход, 0) КАК Оплачено,
    ЗаказЗаказ.Ссылка.СуммаДокумента - ЕСТЬNULL(РасчетыСКлиентамиОстаткиИОбороты.КОплатеРасход, 0) КАК Долг,
    ЗаказЗаказ.Товары.(
        Номенклатура КАК Номенклатура,
        Цена КАК Цена,
        Количество КАК Количество
    ) КАК Товары
ИЗ
    Документ.ЗаказКлиента КАК ЗаказЗаказ
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКлиентами.ОстаткиИОбороты КАК РасчетыСКлиентамиОстаткиИОбороты
    ПО ЗаказЗаказ.Ссылка = РасчетыСКлиентамиОстаткиИОбороты.ЗаказКлиента
ГДЕ
    ЗаказЗаказ.Товары.ВариантОбеспечения В (ЗНАЧЕНИЕ(Перечисление.ВариантыОбеспечения.Отгрузить)...)
    И НЕ ЗаказЗаказ.Товары.Отменено
    И ЗаказЗаказ.СуммаДокумента > ЕСТЬNULL(РасчетыСКлиентамиОстаткиИОбороты.КОплатеРасход, 0)

В этом запросе кроются две серьезные проблемы, которые «выстреливают» при попытке построить по нему отчет в СКД:

1. Неявное внутреннее соединение через секцию ГДЕ. Обращение к табличной части через точку в условиях отбора (ЗаказЗаказ.Товары.ВариантОбеспечения...) заставляет платформу неявно соединить шапку документа с его табличной частью Товары по внутреннему типу связи (INNER JOIN). Если у заказа вдруг нет строк, удовлетворяющих условию, или нарушается структура, весь заказ вместе с контрагентом просто исчезает из выборки.
2. Превращение левого соединения во внутреннее. Условие на данные из правой таблицы (РасчетыСКлиентамиОстаткиИОбороты) также наложено в секции ГДЕ. Хотя здесь используется ЕСТЬNULL, в сложных запросах СКД наложение фильтров на правые таблицы левого соединения в общих параметрах отбора часто приводит к отсечению строк, где правая часть равна NULL (то есть по которым вообще не было оплат). В результате «теряются» новые заказы, по которым оплат еще не поступало вовсе.
3. Дублирование показателей при разворачивании в плоскую таблицу. В коде автор обходил табличную часть во вложенном цикле (Для Каждого СтрокаТовары Из СтрокаКонтрагент.Товары). СКД же по умолчанию строит плоскую таблицу выборки. Если у заказа стоимостью 50 000 рублей в табличной части содержится 10 товаров, то при обычном соединении СУБД вернет 10 строк. Без специальных настроек ресурсов СКД просуммирует поле СуммаДокумента по всем строкам и выдаст общую сумму заказа в 500 000 рублей!

Выяснив причины некорректного поведения системы, перейдем к решению задач с использованием встроенных механизмов СКД.

Способ 1: Использование связи двух наборов данных в СКД (Рекомендуемый)

Этот способ является наиболее красивым и «родным» для СКД. Мы разделяем получение агрегированных данных по заказам (шапки документов и расчетов) и получение детальных строк (номенклатуры из табличных частей) на два независимых набора данных, а затем связываем их средствами самой компоновки.

Разберем этот процесс по шагам.

Шаг 1. Создание Набора данных №1 — «Заказы» (Набор данных - запрос)

В этот набор мы поместим запрос, который выбирает только уникальные данные по заказам и остаткам оплат. Здесь нет обращения к табличной части Товары, поэтому строки не будут дублироваться.

ВЫБРАТЬ
    ЗаказКлиента.Контрагент КАК Контрагент,
    ЗаказКлиента.Ссылка КАК Заказ,
    ЗаказКлиента.СуммаДокумента КАК СуммаДокумента,
    ЕСТЬNULL(РасчетыОстатки.КОплатеРасход, 0) КАК Оплачено
ИЗ
    Документ.ЗаказКлиента КАК ЗаказКлиента
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКлиентами.ОстаткиИОбороты(,,, ЗаказКлиента <> ЗНАЧЕНИЕ(Документ.ЗаказКлиента.ПустаяСсылка)) КАК РасчетыОстатки
    ПО ЗаказКлиента.Ссылка = РасчетыОстатки.ЗаказКлиента
ГДЕ
    ЗаказКлиента.Проведен
    И ЗаказКлиента.СуммаДокумента > ЕСТЬNULL(РасчетыОстатки.КОплатеРасход, 0)

Важный нюанс: Обратите внимание, что мы накладываем условия на проведение документа и проверяем наличие долга (СуммаДокумента > Оплачено) непосредственно в запросе первого набора данных. Контрагенты и заказы теперь не потеряются, так как нет паразитных связей с ТЧ товаров на этом уровне.

Шаг 2. Создание Набора данных №2 — «Товары» (Набор данных - запрос)

Этот набор данных будет выбирать строки табличной части. Здесь мы также применим фильтрацию по вариантам обеспечения и отмененным позициям, которая изначально требовалась автору.

ВЫБРАТЬ
    ЗаказТовары.Ссылка КАК Заказ,
    ЗаказТовары.Номенклатура КАК Номенклатура,
    ЗаказТовары.Цена КАК Цена,
    ЗаказТовары.Количество КАК Количество
ИЗ
    Документ.ЗаказКлиента.Товары КАК ЗаказТовары
ГДЕ
    ЗаказТовары.ВариантОбеспечения В (
        ЗНАЧЕНИЕ(Перечисление.ВариантыОбеспечения.Отгрузить), 
        ЗНАЧЕНИЕ(Перечисление.ВариантыОбеспечения.ОтгрузитьОбособленно), 
        ЗНАЧЕНИЕ(Перечисление.ВариантыОбеспечения.ИзЗаказов), 
        ЗНАЧЕНИЕ(Перечисление.ВариантыОбеспечения.Обособленно)
    )
    И НЕ ЗаказТовары.Отменено

Шаг 3. Настройка связи наборов данных

Перейдем на вкладку «Связи наборов данных» в конструкторе схемы компоновки и добавим новую строку:

• Источник связи: НаборДанных1 (Заказы)
• Приемник связи: НаборДанных2 (Товары)
• Условие связи: Заказ = Заказ
• Параметры: Оставляем пустыми (СКД выполнит левое соединение наборов в памяти).

Благодаря такому разделению, СКД сначала выберет уникальные заказы и их суммы, а затем для каждого заказа подтянет строки с товарами. При этом ресурсы СуммаДокумента и Оплачено будут суммироваться абсолютно корректно стандартной функцией СУММА(), без какого-либо размножения и искажения итогов!

Способ 2: Использование одного набора данных и агрегатных функций в ресурсах

Если по каким-то причинам вы хотите оставить только один запрос (например, для упрощения переноса существующего кода), вам потребуется правильно настроить расчет ресурсов на соответствующей вкладке СКД. Рассмотрим, как это сделать.

Запрос в данном случае будет объединять шапку и табличную часть через левое соединение:

ВЫБРАТЬ
    ЗаказКлиента.Контрагент КАК Контрагент,
    ЗаказКлиента.Ссылка КАК Заказ,
    ЗаказКлиента.СуммаДокумента КАК СуммаДокумента,
    ЕСТЬNULL(РасчетыОстатки.КОплатеРасход, 0) КАК Оплачено,
    ЗаказТовары.Номенклатура КАК Номенклатура,
    ЗаказТовары.Цена КАК Цена,
    ЗаказТовары.Количество КАК Количество
ИЗ
    Документ.ЗаказКлиента КАК ЗаказКлиента
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЗаказКлиента.Товары КАК ЗаказТовары
        ПО ЗаказКлиента.Ссылка = ЗаказТовары.Ссылка
        И ЗаказТовары.ВариантОбеспечения В (
            ЗНАЧЕНИЕ(Перечисление.ВариантыОбеспечения.Отгрузить), 
            ЗНАЧЕНИЕ(Перечисление.ВариантыОбеспечения.ОтгрузитьОбособленно)
        )
        И НЕ ЗаказТовары.Отменено
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКлиентами.ОстаткиИОбороты КАК РасчетыОстатки
        ПО ЗаказКлиента.Ссылка = РасчетыОстатки.ЗаказКлиента
ГДЕ
    ЗаказКлиента.СуммаДокумента > ЕСТЬNULL(РасчетыОстатки.КОплатеРасход, 0)

Обратите внимание: Мы перенесли условия по табличной части в секцию связи ПО при соединении с Документ.ЗаказКлиента.Товары. Это гарантирует, что соединение отработает корректно.

Теперь, если мы просто выведем этот запрос в отчет, суммы поползут вверх из-за дублирования строк номенклатуры. Чтобы решить эту проблему, настроим вкладку «Ресурсы»:

1. Добавим поле СуммаДокумента в список ресурсов.
2. В колонке «Выражение» напишем формулу:
   Максимум(СуммаДокумента)
   А в колонке «Рассчитывать по...» укажем группировку Заказ.
3. Добавим еще одну строку для ресурса СуммаДокумента, но уже для расчета по вышестоящим группировкам (Контрагент и Общий итог). Используем для этого специальную функцию СКД:
   Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаДокумента)", "Заказ"))

Проанализируем, как работает эта формула: СКД на уровне группировки Заказ берет ровно одно максимальное значение суммы (поскольку сумма документа внутри одного заказа всегда одинакова, Максимум вернет верную сумму заказа). Затем функция ВычислитьВыражениеСГруппировкойМассив собирает эти уникальные значения по всем заказам конкретного контрагента в массив, а внешняя функция Сумма складывает элементы этого массива. Итоги по контрагентам и общие итоги в отчете сходятся до копейки!

Аналогичным образом настраиваем ресурс Оплачено:

• Для группировки Заказ: Максимум(Оплачено)
• Для группировок Контрагент и общего итога: Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(Оплачено)", "Заказ"))

Настройка вычисляемых полей для расчета долга

Чтобы не перегружать запрос лишними вычислениями разностей, которые могут некорректно вести себя при наличии NULL значений, расчет долга (остатка к оплате) лучше вынести на вкладку «Вычисляемые поля» в СКД.

Добавим новое поле:

• Путь к данным: Долг
• Выражение: СуммаДокумента - Оплачено
• Заголовок: Остаток оплаты (Долг)

После добавления вычисляемого поля обязательно переходим на вкладку «Ресурсы» и настраиваем его расчет:

• Для группировки Заказ: Максимум(Долг)
• Для группировок Контрагент и общего итога: Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(Долг)", "Заказ")) (в случае использования одного набора данных) или просто Сумма(Долг) (в случае использования связи двух наборов данных).

Настройка структуры отчета на вкладке «Настройки»

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

Разберем структуру группировок сверху вниз:

1. Группировка по полю Контрагент.
   • В выводимых полях для этой группировки указываем: Контрагент, СуммаДокумента, Оплачено, Долг.
2. Подчиненная группировка по полю Заказ.
   • В выводимых полях указываем: Заказ, СуммаДокумента, Оплачено, Долг.
3. Подчиненная группировка «Детальные записи» (или группировка по полю Номенклатура).
   • В этой группировке нам не нужно дублировать финансовые показатели заказа. В выводимых полях оставляем только: Номенклатура, Количество, Цена.

При такой структуре детальные строки с товарами будут аккуратно схлопываться под своим заказом-родителем, финансовые показатели (сумма, оплата, долг) будут наглядно выведены напротив каждого заказа и просуммированы по контрагентам, а пользователи смогут сворачивать и разворачивать группировки стандартными кнопками 1С.