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