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

← На главную

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

Разберем типичную проблему, когда при формировании итогового дерева номенклатура разбивается на две группы — заданного контрагента и пустое значение NULL, и рассмотрим корректные способы построения такого запроса.

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

Рассмотрим, почему возникает разделение на контрагента и пустую группу. Если в качестве базовой таблицы запроса выбирается справочник номенклатуры или регистр сведений ЗначенияСвойствОбъектов, а затем к нему левым соединением подключается таблица с индивидуальными ценами контрагента, происходят следующие события:

Для позиций, по которым у контрагента задана индивидуальная цена, поле Контрагент заполняется значением из правого источника.
Для позиций, у которых индивидуальной цены нет, соединение возвращает значение NULL.
При расчете итогов по полю Контрагент платформа формирует две независимые ветки дерева: одна для выбранного контрагента, а вторая — для всех товаров со значением NULL.

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

Способ 1. Построение матрицы «Контрагент × Номенклатура» через пакетный запрос

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

Формируем временную таблицу контрагентов, по которым строится отчет.
Формируем временную таблицу номенклатуры, предварительно отобрав позиции по нужному свойству (например, производителю) и явно выразив типы.
Создаем сетку сочетаний с помощью декартова произведения (соединение по условию ПО ИСТИНА).
Присоединяем индивидуальные и базовые цены через левые соединения.
Вычисляем итоговую цену с помощью функции ЕСТЬNULL.

Посмотрим на пример реализации такого пакетного запроса:


ВЫБРАТЬ
    Контрагенты.Ссылка КАК Контрагент
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
    Справочник.Контрагенты КАК Контрагенты
ГДЕ
    Контрагенты.Ссылка В(&СписокКонтрагентов)
;

ВЫБРАТЬ
    ВЫРАЗИТЬ(ЗначенияСвойствОбъектов.Объект КАК Справочник.Номенклатура) КАК Номенклатура,
    ЗначенияСвойствОбъектов.Значение КАК Производитель
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
    РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
    ЗначенияСвойствОбъектов.Свойство = &Свойство
    И ЗначенияСвойствОбъектов.Значение В(&Значение)
;

ВЫБРАТЬ
    ВТ_Контрагенты.Контрагент КАК Контрагент,
    ВТ_Номенклатура.Номенклатура КАК Номенклатура,
    ВТ_Номенклатура.Производитель КАК Производитель
ПОМЕСТИТЬ ВТ_Сетка
ИЗ
    ВТ_Контрагенты КАК ВТ_Контрагенты
        СОЕДИНЕНИЕ ВТ_Номенклатура КАК ВТ_Номенклатура
        ПО ИСТИНА
;

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

Способ 2. Корректировка выбора полей и отсечение пустых значений

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

Замена конструкции ВЫБОР КОГДА ... ЕСТЬ NULL: вместо громоздких условий используем компактную функцию ЕСТЬNULL(ВложенныйЗапрос.Цена, ЦеныНоменклатурыСрезПоследних.Цена).
Корректное поле номенклатуры: в списке полей необходимо выбирать ЗначенияСвойствОбъектов.Объект КАК Номенклатура, а не поле из вложенного запроса, так как при отсутствии индивидуальной цены поле из вложенного запроса будет содержать NULL.
Подстановка контрагента: если контрагент выбирается из вложенного запроса, для строк с базовой ценой нужно явно подставлять параметр отбора &Контрагент с помощью функции ЕСТЬNULL(ВложенныйЗапрос.Контрагент, &Контрагент) КАК Контрагент.

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


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

Особенности работы с иерархией и оптимизация

При построении иерархических отчетов в 1С следует учитывать несколько важных правил:

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

Обход выборки в коде: при программной обработке дерева итогов обход выборки выполняется с указанием типа обхода:


ВыборкаКонтрагент = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией);
Пока ВыборкаКонтрагент.Следующий() Цикл
    // Обработка группировки контрагента
    ВыборкаНоменклатура = ВыборкаКонтрагент.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией);
    Пока ВыборкаНоменклатура.Следующий() Цикл
        // Обработка дерева папок и элементов номенклатуры
    КонецЦикла;
КонецЦикла;