При разработке отчетов и обработок в среде 1С:Предприятие 8 программисты часто сталкиваются с необходимостью построения сложных многоуровневых деревьев данных. Типичный пример такой задачи — вывод остатков товаров с группировкой по видам номенклатуры, иерархической структуре самого справочника номенклатуры (папкам) и, наконец, по складам, на которых эти остатки хранятся.
В этой статье мы подробно разберем, как правильно спроектировать запрос для решения этой задачи, устранить распространенные логические ошибки в секции ИТОГИ ПО, а также рассмотрим механизмы выгрузки полученных результатов в коллекцию ДеревоЗначений и альтернативные, более современные методы реализации через Систему компоновки данных (СКД).
Разберем типичную ситуацию. Разработчик пытается построить отчет по свободным остаткам товаров на складах. Исходный запрос выбирает данные из виртуальной таблицы остатков и соединяет их со сведениями о товарных ограничениях. По умолчанию дерево строится по иерархии самого справочника номенклатуры. Однако бизнес-требования меняются, и теперь требуется добавить на самый верхний уровень группировку по Видам номенклатуры (например: Товары для дома, Декоративные изделия, Услуги и т.д.), внутри которых должна раскрываться привычная иерархия папок номенклатуры, а на самом нижнем уровне — остатки в разрезе складов.
Попытка просто добавить поле вида номенклатуры в конец конструкции ИТОГИ ПО приводит к некорректному результату. Выясним причину этой ошибки и найдем правильный путь решения задачи.
Посмотрим на ошибочный вариант секции итогов, который часто используют начинающие разработчики:
ИТОГИ СУММА(Остаток), МАКСИМУМ(ОстатокМаксимум)
ПО Номенклатура, Склад, СвободныеОстаткиОстатки.Номенклатура.ВидНоменклатуры ИЕРАРХИЯ
В чем заключается ошибка? В языке запросов 1С порядок полей в конструкции ИТОГИ ПО имеет решающее значение. Он строго определяет вложенность группировок сверху вниз (от родителя к потомку). В приведенном выше ошибочном коде платформа сначала сгруппирует данные по самой номенклатуре, внутри нее создаст ветки по складам, и только внутри конкретного склада попытается сгруппировать данные по виду номенклатуры. Это логически неверно, так как у одной позиции номенклатуры может быть только один вид, и эта группировка просто потеряет свой смысл, оказавшись на самом дне иерархии.
Для того чтобы вид номенклатуры стал главным (корневым) узлом нашего дерева, это поле обязано стоять на первом месте в списке группировок секции ИТОГИ.
Правильный вариант конструкции будет выглядеть следующим образом:
ИТОГИ СУММА(Остаток), МАКСИМУМ(ОстатокМаксимум)
ПО СвободныеОстаткиОстатки.Номенклатура.ВидНоменклатуры,
СвободныеОстаткиОстатки.Номенклатура ИЕРАРХИЯ,
Склад
При таком подходе платформа построит четкую четырехуровневую структуру:
Рассмотрим подробнее поведение платформы при использовании различных типов расчета итогов по иерархическим справочникам. В обсуждениях на форумах часто упоминается конструкция ТОЛЬКО ИЕРАРХИЯ. Проанализируем разницу:
Что касается самого поля ВидНоменклатуры: справочник «Виды номенклатуры» в типовых конфигурациях (таких как УТ 11, ERP, КА) может быть как плоским, так и иерархическим. Если в вашей базе виды номенклатуры не разделены по папкам, использовать для них ключевое слово ИЕРАРХИЯ не нужно. Достаточно просто указать имя поля. Это существенно снизит нагрузку на СУБД при выполнении запроса.
Объединим все исправления в один итоговый запрос. Мы перенесли поле вида номенклатуры на первое место в секции ИТОГИ, а также добавили его выборку в секцию ВЫБРАТЬ для корректной последующей обработки в коде.
ВЫБРАТЬ РАЗРЕШЕННЫЕ
СвободныеОстаткиОстатки.Номенклатура КАК Номенклатура,
СвободныеОстаткиОстатки.Номенклатура.ВидНоменклатуры КАК ВидНоменклатуры,
СвободныеОстаткиОстатки.Склад КАК Склад,
СвободныеОстаткиОстатки.ВНаличииОстаток КАК ВНаличииОстаток,
СвободныеОстаткиОстатки.ВРезервеСоСкладаОстаток + СвободныеОстаткиОстатки.ВРезервеПодЗаказОстаток КАК ВРезервеОстаток,
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) - ЕСТЬNULL(СвободныеОстаткиОстатки.ВРезервеСоСкладаОстаток, 0) - ЕСТЬNULL(СвободныеОстаткиОстатки.ВРезервеПодЗаказОстаток, 0) КАК Остаток,
ТоварныеОграничения.СтраховоеКоличествоЗапаса КАК СтраховоеКоличествоЗапаса,
ТоварныеОграничения.МинимальноеКоличествоЗапаса КАК МинимальноеКоличествоЗапаса,
ТоварныеОграничения.МаксимальноеКоличествоЗапаса КАК МаксимальноеКоличествоЗапаса,
ТоварныеОграничения.МетодОбеспеченияПотребностей КАК МетодОбеспеченияПотребностей,
ЕСТЬNULL(СвободныеОстаткиОстатки.ВНаличииОстаток, 0) - ЕСТЬNULL(СвободныеОстаткиОстатки.ВРезервеСоСкладаОстаток, 0) - ЕСТЬNULL(СвободныеОстаткиОстатки.ВРезервеПодЗаказОстаток, 0) КАК ОстатокМаксимум
ИЗ
РегистрНакопления.СвободныеОстатки.Остатки(
&Период,
Склад В (&Склады)
И (&ОтборПоГруппамВыкл
ИЛИ Номенклатура.ВидНоменклатуры В ИЕРАРХИИ (&ГруппыОтбора))) КАК СвободныеОстаткиОстатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ТоварныеОграничения КАК ТоварныеОграничения
ПО СвободныеОстаткиОстатки.Номенклатура = ТоварныеОграничения.Номенклатура
И СвободныеОстаткиОстатки.Характеристика = ТоварныеОграничения.Характеристика
И СвободныеОстаткиОстатки.Склад = ТоварныеОграничения.Склад
УПОРЯДОЧИТЬ ПО
Номенклатура
ИТОГИ
СУММА(Остаток),
МАКСИМУМ(ОстатокМаксимум)
ПО
ВидНоменклатуры,
Номенклатура ИЕРАРХИЯ,
Склад
После того как мы написали корректный запрос, нам необходимо правильно обработать его результаты во встроенном языке 1С. Для того чтобы построить визуальное дерево на форме, результат запроса выгружают в объект типа ДеревоЗначений.
Для корректного распределения строк по веткам (Вид номенклатуры -> Папки -> Товары -> Склады) крайне важно использовать специальный системный тип обхода — ОбходРезультатаЗапроса.ПоГруппировкамСИерархией. Разберем пример кода:
// Выполняем запрос
РезультатЗапроса = Запрос.Выполнить();
// Выгружаем результат в Дерево значений с сохранением иерархической структуры
ДеревоОстатков = РезультатЗапроса.Выгрузить(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией);
// Теперь полученное дерево можно напрямую связать с реквизитом управляемой формы
ЗначениеВРеквизитФормы(ДеревоОстатков, "ДеревоОстатковНаФорме");
Если же перед вами стоит задача программно обойти все ветки полученного дерева (например, для формирования печатной формы или выгрузки в файл), мы рекомендуем использовать вложенные циклы выборки. Посмотрим на шаблон такого обхода:
// Выборка верхнего уровня - Виды номенклатуры
ВыборкаВид = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаВид.Следующий() Цикл
// Выводим или обрабатываем Вид номенклатуры
Сообщить("Вид номенклатуры: " + ВыборкаВид.ВидНоменклатуры);
// Выборка второго уровня - Номенклатура с учетом иерархии папок
ВыборкаНоменклатура = ВыборкаВид.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкамСИерархией, "Номенклатура");
Пока ВыборкаНоменклатура.Следующий() Цикл
// Здесь будут как группы (папки), так и сами элементы
Если ВыборкаНоменклатура.Номенклатура.ЭтоГруппа Тогда
Сообщить("--- Папка: " + ВыборкаНоменклатура.Номенклатура);
Иначе
Сообщить("------ Товар: " + ВыборкаНоменклатура.Номенклатура + " (Остаток: " + ВыборкаНоменклатура.Остаток + ")");
// Выборка третьего уровня - Детализация остатков по складам для конкретного товара
ВыборкаСклад = ВыборкаНоменклатура.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Склад");
Пока ВыборкаСклад.Следующий() Цикл
Сообщить("--------- Склад: " + ВыборкаСклад.Склад + " [Количество: " + ВыборкаСклад.Остаток + "]");
КонецЦикла;
КонецЕсли;
КонецЦикла;
КонецЦикла;
Необходимо отметить, что в современных конфигурациях 1С (особенно построенных на управляемых формах, таких как УТ 11, ERP, КА) ручное написание запросов с тяжелыми итогами по иерархии для вывода отчетов на форму считается устаревшим и неоптимальным подходом.
Использование конструкции ИТОГИ ... ИЕРАРХИЯ по нескольким полям создает серьезную нагрузку на СУБД (MS SQL Server, PostgreSQL и др.), поскольку платформе приходится выполнять ресурсоемкие рекурсивные вычисления для определения родителей каждого элемента. На больших базах данных это может приводить к значительному замедлению работы системы.
Гораздо более эффективным, надежным и простым в реализации решением является использование Системы компоновки данных (СКД). Нам не потребуется писать сложные конструкции итогов в самом запросе. СКД берет всю работу по компоновке дерева на себя, оптимизируя запросы к СУБД на уровне платформы.
Для реализации нашей задачи в СКД достаточно выполнить следующие шаги:
ИТОГИ).СУММА() или МАКСИМУМ() соответственно.ВидНоменклатуры (тип группировки: Без иерархии или Иерархия, в зависимости от структуры справочника видов).Номенклатура (тип группировки обязательно установить в значение Иерархия).Склад (тип группировки: Элементы).Платформа автоматически сформирует оптимизированный SQL-запрос, быстро обработает результат и выведет на экран пользователя аккуратное, быстродействующее интерактивное дерево, которое можно легко расширять новыми группировками без изменения программного кода.