Как связать заказы с платежными поручениями в запросе 1С, если заказ может быть в шапке или в табличной части

← На главную

В процессе разработки отчетов в системе 1С программисты часто сталкиваются с нестандартными структурами метаданных. Типичный пример такой задачи — получение сведений об оплате заказов поставщикам из документов «Платежное поручение исходящее» (ППИ). Сложность заключается в том, что в зависимости от специфики заполнения документа (например, флага многострочности МнгСтр) ссылка на исходный заказ может находиться либо непосредственно в реквизите шапки платежного поручения, либо внутри его табличной части (например, ОснованиеПлатежа).

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

Анализ исходной проблемы и неэффективного подхода

Рассмотрим ситуацию подробнее. У нас есть документ ЗаказПоставщику и документ ПлатежноеПоручениеИсходящее. В платежном поручении связь с заказом может быть организована двумя путями:

Если платежка «однострочная» (флаг МнгСтр равен ЛОЖЬ), то ссылка на заказ хранится в реквизите шапки документа (например, ДокументЗаказПоставщику). При этом сумма оплаты берется из реквизита шапки СуммаДокумента.
Если платежка «многострочная» (флаг МнгСтр равен ИСТИНА), то оплата распределяется по нескольким заказам. В этом случае связь находится в табличной части ОснованиеПлатежа (реквизит Заказ), а сумма оплаты по конкретному заказу хранится в строке табличной части (реквизит СуммаЗаказа).

Попытка соединить эти таблицы «в лоб» через левое соединение с условием ВЫБОР КОГДА... в полях выборки является крайне неэффективной. Проанализируем исходный запрос, который пытался использовать автор темы:


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

СГРУППИРОВАТЬ ПО
    ЗаказПоставщику.Ссылка
;

Почему такой подход не будет работать корректно? Во-первых, левое соединение происходит исключительно по табличной части Документ.ПлатежноеПоручениеИсходящее.ОснованиеПлатежа. Если платежное поручение оформлено как однострочное, в его табличной части может вообще не быть строк, либо связь по полю Заказ в табличной части не сработает. В результате мы полностью потеряем оплаты по однострочным платежным поручениям. Во-вторых, обращение через точку к реквизиту шапки из табличной части (ПлатежноеПоручениеИсходящееОснованиеПлатежа.Ссылка.МнгСтр) вызывает неявные соединения в СУБД, что сильно снижает производительность запроса.

Решение через разделение на два запроса и объединение

Для корректного получения данных нам необходимо разделить логику на два независимых потока получения данных и затем объединить их результаты. Сделать это можно с помощью оператора ОБЪЕДИНИТЬ ВСЕ.

Выясним структуру двух частей нашего будущего объединения:

Первая часть: выбирает оплаты из многострочных платежных поручений. Мы соединяем заказы с табличной частью ОснованиеПлатежа и берем детальную сумму строки.
Вторая часть: выбирает оплаты из однострочных платежных поручений. Мы соединяем заказы напрямую с шапкой документа ПлатежноеПоручениеИсходящее по реквизиту ДокументЗаказПоставщику и берем общую сумму документа.

Посмотрим на пример реализации объединения, который обсуждался на форуме:


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

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    ЗаказПоставщику.Ссылка,
    СУММА(ЕСТЬNULL(ПлатежноеПоручениеИсходящее.СуммаДокумента, 0))
ИЗ
    Документ.ЗаказПоставщику КАК ЗаказПоставщику
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПлатежноеПоручениеИсходящее КАК  ПлатежноеПоручениеИсходящее
        ПО (ПлатежноеПоручениеИсходящее.ДокументЗаказПоставщику = ЗаказПоставщику.Ссылка)
ГДЕ
    ЗаказПоставщику.Утвержден = &Утвержден
    // ... другие условия ...
СГРУППИРОВАТЬ ПО
    ЗаказПоставщику.Ссылка
;

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

Внимательно проанализируем представленный выше код. В нем кроется очень опасная логическая ошибка, которая приведет к искажению финансовых данных в отчете (суммы оплат будут существенно завышены).

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

Если запустить данный запрос:

В первой ветке объединения система найдет это платежное поручение в табличной части и честно прибавит по 20 000 рублей к каждому из 5 заказов.
Во второй ветке объединения система снова найдет это же платежное поручение (так как связи по шапке не ограничены флагом) и прибавит ВСЮ сумму документа из шапки (100 000 рублей) к первому заказу.

В результате по первому заказу мы получим сумму оплаты 120 000 рублей вместо реальных 20 000 рублей. Произойдет классическое задвоение (завышение) показателей.

Как исправить эту ошибку? Нам необходимо жестко разграничить области действия двух ветвей запроса с помощью условий на признак МнгСтр:

В первой ветке (работа с табличной частью) мы должны обрабатывать только те документы, у которых МнгСтр = ИСТИНА.
Во второй ветке (работа с шапкой) мы должны обрабатывать только те документы, у которых МнгСтр = ЛОЖЬ.

Сворачивание и группировка результатов во временных таблицах

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

Для этого мы воспользуемся механизмом пакета запросов и временных таблиц. Сначала мы поместим объединенные детальные данные во временную таблицу ВТ_ОплатыДетально, а затем в следующем запросе пакета сгруппируем их по заказу:


ВЫБРАТЬ
    ВТ_ОплатыДетально.Документ КАК Документ,
    СУММА(ВТ_ОплатыДетально.СуммаПлатежки) КАК СуммаПлатежки
ПОМЕСТИТЬ ВТ_ИтоговыеОплаты
ИЗ
    ВТ_ОплатыДетально КАК ВТ_ОплатыДетально

СГРУППИРОВАТЬ ПО
    ВТ_ОплатыДетально.Документ
;

Оптимизация производительности: почему ОБЪЕДИНИТЬ ВСЕ лучше?

Обратим внимание на важный аспект оптимизации запросов в 1С. В коде на форуме используется оператор ОБЪЕДИНИТЬ. Напомним, что использование ОБЪЕДИНИТЬ (без ключевого слова ВСЕ) заставляет СУБД выполнять дополнительную операцию по поиску и удалению дублирующихся строк (аналог операции DISTINCT). Это крайне ресурсоемкая операция, которая на больших базах данных приводит к дисковым операциям и серьезному замедлению работы.

Поскольку на следующем шаге мы все равно группируем данные и суммируем их функцией СУММА, нам абсолютно неважно, есть ли в объединении одинаковые строки. Поэтому мы всегда должны использовать оператор ОБЪЕДИНИТЬ ВСЕ. Это позволит СУБД просто "склеить" результаты двух выборок моментально, без лишних проверок.

Кроме того, выполнение группировки внутри каждой ветки объединения, а затем повторной группировки при выборке из ВТ — это избыточная работа. Гораздо эффективнее выбрать «сырые» данные без группировок в первой и второй ветках, быстро объединить их через ОБЪЕДИНИТЬ ВСЕ во временную таблицу, проиндексировать ее по полю связи и сгруппировать один-единственный раз при итоговом соединении с заказами.

Архитектурное отступление: связь «Документ-Документ» как антипаттерн

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

Почему это плохо?

Табличные части документов в СУБД не оптимизированы для сложных выборок. Поля типа Заказ в табличных частях по умолчанию не индексируются. Соединение по неиндексированному полю на базе среднего или большого размера гарантированно приведет к сканированию всей таблицы (Table Scan), блокировкам и зависанию системы.
Документы могут быть распроведены или помечены на удаление, их структуры могут меняться.

Как правильно? Все данные о расчетах с поставщиками и оплатах должны фиксироваться в регистрах накопления (например, в типовых конфигурациях это регистры ВзаиморасчетыСКонтрагентами, РасчетыСПоставщиками или ОплатыЗаказовПоставщикам). При проведении платежного поручения система сама считывает признак многострочности и записывает суммы оплат в разрезе заказов в движения регистра.

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


ВЫБРАТЬ
    РасчетыОбороты.ЗаказПоставщику КАК Документ,
    РасчетыОбороты.СуммаРасход КАК СуммаПлатежки
ИЗ
    РегистрНакопления.ВзаиморасчетыСПоставщиками.Обороты(&НачПериода, &КонПериода, , ЗаказПоставщику В (&СписокЗаказов)) КАК РасчетыОбороты

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

Итоговый правильный и оптимизированный вариант запроса

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


// ШАГ 1: Собираем оплаты из платежных поручений во временную таблицу.
// Используем ОБЪЕДИНИТЬ ВСЕ для быстродействия.
// Обязательно разделяем логику по признаку МнгСтр для исключения задвоения.

ВЫБРАТЬ
    ПлатежкаТЧ.Заказ КАК Заказ,
    ПлатежкаТЧ.СуммаЗаказа КАК СуммаОплаты
ПОМЕСТИТЬ ВТ_Оплаты_Сырые
ИЗ
    Документ.ПлатежноеПоручениеИсходящее.ОснованиеПлатежа КАК ПлатежкаТЧ
ГДЕ
    ПлатежкаТЧ.Ссылка.Проведен = ИСТИНА
    И ПлатежкаТЧ.Ссылка.МнгСтр = ИСТИНА // Только многострочные платежки

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ПлатежкаШапка.ДокументЗаказПоставщику,
    ПлатежкаШапка.СуммаДокумента
ИЗ
    Документ.PlatezhnoePoruchenieIshodyaschee КАК ПлатежкаШапка
ГДЕ
    ПлатежкаШапка.Проведен = ИСТИНА
    И ПлатежкаШапка.МнгСтр = ЛОЖЬ // Только однострочные платежки
    И ПлатежкаШапка.ДокументЗаказПоставщику <> ЗНАЧЕНИЕ(Документ.ЗаказПоставщику.ПустаяСсылка)
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Сворачиваем (группируем) оплаты по Заказу и индексируем поле связи.
// Индексация временной таблицы критически важна для быстрого последующего соединения!

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

СГРУППИРОВАТЬ ПО
    ВТ_Оплаты_Сырые.Заказ

ИНДЕКСИРОВАТЬ ПО
    Заказ
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Выбираем Заказы Поставщикам и соединяем их с подготовленной таблицей оплат.

ВЫБРАТЬ
    ЗаказПоставщику.Ссылка КАК Документ,
    ЗаказПоставщику.Дата КАК Дата,
    ЕСТЬNULL(ВТ_Оплаты_Сгруппированные.СуммаОплаты, 0) КАК СуммаПлатежки
ИЗ
    Документ.ЗаказПоставщику КАК ЗаказПоставщику
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Оплаты_Сгруппированные КАК ВТ_Оплаты_Сгруппированные
        ПО (ВТ_Оплаты_Сгруппированные.Заказ = ЗаказПоставщику.Ссылка)
ГДЕ
    ЗаказПоставщику.Утвержден = &Утвержден
    И ЗаказПоставщику.Оплачено В(&Оплачено)
    И ЗаказПоставщику.Дата МЕЖДУ &НачПериода И &КонПериода
    И ЗаказПоставщику.ПометкаУдаления = ЛОЖЬ
    И ЗаказПоставщику.Организация В ИЕРАРХИИ(&Организация)
;

Разберем, почему данный финальный запрос работает правильно и быстро:

Безопасность данных: разделение по условиям МнгСтр = ИСТИНА и МнгСтр = ЛОЖЬ гарантирует, что ни одна сумма не будет посчитана дважды. Мы четко разделили логику получения сумм из шапки и табличной части.
Производительность СУБД: мы убрали избыточные группировки из первых подзапросов, применили производительный оператор ОБЪЕДИНИТЬ ВСЕ, а также добавили директиву ИНДЕКСИРОВАТЬ ПО Заказ для временной таблицы оплат. Теперь финальное левое соединение выполнится мгновенно за счет использования индекса СУБД.

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