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