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

Проблема: Отсутствие данных при пустой табличной части

Когда мы пытаемся получить данные из документа, который имеет табличную часть (например, "ОплатаПлатежнымиКартами" в документе "ОтчетОРозничныхПродажах"), и эта табличная часть может быть пустой, стандартный запрос, напрямую выбирающий данные из этой табличной части, может привести к "пропускам". Это означает, что документы, у которых табличная часть пуста, просто не попадут в результат запроса.

Рассмотрим пример начального запроса, который приводит к этой проблеме (Сообщение 1):


ВЫБРАТЬ
    НачалоПериода(ОтчетОРозничныхПродажах.Ссылка.Дата, День) КАК День,
    ОтчетОРозничныхПродажах.Ссылка КАК Ссылка,
    Максимум(ОтчетОРозничныхПродажах.Ссылка.СуммаДокумента) КАК СуммаДокумента,
    СУММА(ОтчетОРозничныхПродажах.Сумма) КАК СуммаБН
ИЗ
    Документ.ОтчетОРозничныхПродажах.ОплатаПлатежнымиКартами КАК ОтчетОРозничныхПродажах
ГДЕ
    ОтчетОРозничныхПродажах.Ссылка.Дата МЕЖДУ &Дата1 И &Дата2
    И ОтчетОРозничныхПродажах.Ссылка.Проведен
    И ОтчетОРозничныхПродажах.Ссылка.Проведен"+ФКасса+"
СГРУППИРОВАТЬ ПО
    НачалоПериода(ОтчетОРозничныхПродажах.Ссылка.Дата, День),
    Ссылка
УПОРЯДОЧИТЬ ПО
    День,
    Ссылка

В этом запросе мы выбираем данные напрямую из табличной части Документ.ОтчетОРозничныхПродажах.ОплатаПлатежнымиКартами. По своей сути это действует как внутреннее соединение (INNER JOIN), при котором в результат запроса попадают только те записи, для которых есть соответствие в обеих "таблицах" (в данном случае, в самом документе и его табличной части). Если в табличной части "ОплатаПлатежнымиКартами" нет ни одной записи для конкретного документа, то этот документ полностью исключается из результата.

Нашей задачей является получение всех документов "ОтчетОРозничныхПродажах" в заданном периоде, а также корректное отображение сумм безналичной и наличной оплаты. При этом, если оплаты картой не было, сумма безналичных оплат должна быть равна нулю, а не приводить к исключению документа.

Автор запроса также спрашивает: "а как применить функцию ВСЕ как это было в 7.7 ?". В 1С:Предприятии 7.7 для получения всех записей из одной таблицы и соответствующих из другой использовалось ключевое слово ВСЕ в операторе соединения. В 1С:Предприятии 8.3 аналогом этого функционала является ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN).

Решение проблемы с помощью ЛЕВОГО СОЕДИНЕНИЯ

Для того чтобы включить в результат запроса все документы "ОтчетОРозничныхПродажах", даже те, у которых табличная часть "ОплатаПлатежнымиКартами" пуста, мы должны использовать ЛЕВОЕ СОЕДИНЕНИЕ. Это позволит нам получить все записи из "левой" таблицы (основной документ) и соответствующие записи из "правой" таблицы (табличная часть). Если для записи из левой таблицы нет соответствия в правой, поля из правой таблицы будут заполнены значением NULL.

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

Выбираем основной документ: Начинаем запрос с основной таблицы Документ.ОтчетОРозничныхПродажах, присвоив ей псевдоним, например, ОтчетОРозничныхПродажах.
Используем ЛЕВОЕ СОЕДИНЕНИЕ: Присоединяем к ней табличную часть Документ.ОтчетОРозничныхПродажах.ОплатаПлатежнымиКартами с помощью ЛЕВОЕ СОЕДИНЕНИЕ, присвоив ей свой псевдоним, например, ОплатаПлатежнымиКартами. Условие соединения будет по ссылке на документ: ОтчетОРозничныхПродажах.Ссылка = ОплатаПлатежнымиКартами.Ссылка.
Обрабатываем NULL-значения функцией ЕСТЬNULL(): После ЛЕВОГО СОЕДИНЕНИЯ, если для документа не было оплат картой, поле ОплатаПлатежнымиКартами.Сумма будет содержать значение NULL. При суммировании NULL-значений они могут быть проигнорированы или привести к некорректным результатам. Чтобы этого избежать, мы используем функцию ЕСТЬNULL(<ПроверяемоеВыражение>, <ВыражениеЗамены>). Она заменяет NULL на указанное значение, например, 0 для числовых полей.
Корректная группировка: При использовании агрегатных функций (таких как СУММА) все неагрегированные поля, которые мы выбираем в запросе, должны быть указаны в секции СГРУППИРОВАТЬ ПО. Иначе возникнет ошибка "группировка верхнего уровня".
Расчет наличной оплаты: Сумма наличной оплаты может быть получена как разница между общей суммой документа (СуммаДокумента) и суммой безналичной оплаты (СуммаБН).

Пример запроса с ЛЕВЫМ СОЕДИНЕНИЕМ и ЕСТЬNULL()

Представим запрос, который решает нашу задачу, объединяя все вышеуказанные принципы (на основе Сообщения 9 и дополнительной информации):


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

Разберем этот запрос подробнее:

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

Расчет наличной оплаты

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

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

Вот как мы можем это сделать (на основе Сообщения 8):

Сначала мы формируем временную таблицу со всеми необходимыми данными: днём, ссылкой на документ, общей суммой документа и суммой безналичной оплаты (используя ЛЕВОЕ СОЕДИНЕНИЕ и ЕСТЬNULL(), как в предыдущем примере).
Затем, во втором запросе, мы выбираем данные из этой временной таблицы и выполняем расчет наличной оплаты.


// Шаг 1: Формируем временную таблицу с суммами безналичных оплат
ВЫБРАТЬ
    НАЧАЛОПЕРИОДА(ОтчетОРозничныхПродажах.Дата, ДЕНЬ) КАК День,
    ОтчетОРозничныхПродажах.Ссылка КАК Ссылка,
    МАКСИМУМ(ОтчетОРозничныхПродажах.СуммаДокумента) КАК СуммаДокумента,
    СУММА(ЕСТЬNULL(ОплатаПлатежнымиКартами.Сумма, 0)) КАК СуммаБН
ПОМЕСТИТЬ ВТ_ДанныеОплат
ИЗ
    Документ.ОтчетОРозничныхПродажах КАК ОтчетОРозничныхПродажах
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ОтчетОРозничныхПродажах.ОплатаПлатежнымиКартами КАК ОплатаПлатежнымиКартами
        ПО ОтчетОРозничныхПродажах.Ссылка = ОплатаПлатежнымиКартами.Ссылка
ГДЕ
    ОтчетОРозничныхПродажах.Дата МЕЖДУ &Дата1 И &Дата2
    И ОтчетОРозничныхПродажах.Проведен
СГРУППИРОВАТЬ ПО
    НАЧАЛОПЕРИОДА(ОтчетОРозничныхПродажах.Дата, ДЕНЬ),
    ОтчетОРозничныхПродажах.Ссылка
;

// Шаг 2: Выбираем данные из временной таблицы и рассчитываем сумму наличной оплаты
ВЫБРАТЬ
    ВТ_ДанныеОплат.День КАК День,
    ВТ_ДанныеОплат.Ссылка КАК Ссылка,
    ВТ_ДанныеОплат.СуммаДокумента КАК ОбщаяСуммаДокумента,
    ВТ_ДанныеОплат.СуммаБН КАК СуммаБезнал,
    ВТ_ДанныеОплат.СуммаДокумента - ВТ_ДанныеОплат.СуммаБН КАК СуммаНал
ИЗ
    ВТ_ДанныеОплат КАК ВТ_ДанныеОплат
УПОРЯДОЧИТЬ ПО
    День,
    Ссылка

В этом пакетном запросе:

Первый запрос создает временную таблицу ВТ_ДанныеОплат, содержащую все необходимые промежуточные расчеты.
Второй запрос выбирает данные из ВТ_ДанныеОплат и вычисляет СуммаНал как разницу между ОбщаяСуммаДокумента и СуммаБезнал.

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

Особенности использования агрегатных функций и группировки

Ошибка "группировка верхнего уровня", упомянутая в Сообщении 7, является одной из самых частых при работе с агрегатными функциями. Она возникает, когда в секции ВЫБРАТЬ присутствуют поля, которые не являются результатом агрегатной функции (например, СУММА, МАКСИМУМ, КОЛИЧЕСТВО) и при этом не включены в секцию СГРУППИРОВАТЬ ПО.

Правило: Все поля, которые вы выбираете и которые не являются результатом агрегатной функции, должны быть перечислены в списке полей для группировки после СГРУППИРОВАТЬ ПО.

В наших примерах, поля День (полученное через НАЧАЛОПЕРИОДА(ОтчетОРозничныхПродажах.Дата, ДЕНЬ)) и ОтчетОРозничныхПродажах.Ссылка являются неагрегированными полями. Поэтому они обязательно должны быть указаны в СГРУППИРОВАТЬ ПО.

Использование МАКСИМУМ(ОтчетОРозничныхПродажах.СуммаДокумента) вместо просто ОтчетОРозничныхПродажах.СуммаДокумента с добавлением в СГРУППИРОВАТЬ ПО также является валидным подходом. Поскольку СуммаДокумента — это уникальный реквизит для каждой Ссылки документа, МАКСИМУМ гарантирует, что мы получим именно его значение для каждой группы, не добавляя его явно в список полей группировки, если Ссылка уже там присутствует.

Резюме и рекомендации

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

Всегда использовать ЛЕВОЕ СОЕДИНЕНИЕ при присоединении табличных частей или регистров, если вы хотите сохранить все записи из основной таблицы.
Для полей, которые могут быть NULL после ЛЕВОГО СОЕДИНЕНИЯ, использовать функцию ЕСТЬNULL(<Поле>, <ЗначениеЗамены>), чтобы корректно обрабатывать отсутствующие данные (например, заменять NULL на 0 для числовых полей).
Тщательно проверять секцию СГРУППИРОВАТЬ ПО, убедившись, что все неагрегированные поля из секции ВЫБРАТЬ там присутствуют.
Располагать условия фильтрации (ГДЕ) так, чтобы они не изменяли тип соединения. Условия для "левой" таблицы должны быть в ГДЕ, а условия, специфичные для "правой" таблицы, которые не должны исключать строки из "левой", следует перемещать в условие ПО для ЛЕВОГО СОЕДИНЕНИЯ.

Применяя эти принципы, мы можем создавать надежные и полные запросы, которые корректно обрабатывают все сценарии данных в 1С.

← На главную