В практике разработки отчетов на платформе «1С:Предприятие 8» программисты часто сталкиваются с задачей вывода полного списка номенклатуры, включая позиции, которых в данный момент нет на складе (их остаток равен нулю). На первый взгляд задача кажется простой — достаточно использовать левое соединение справочника номенклатуры с виртуальной таблицей остатков регистра накопления. Однако здесь разработчиков подстерегает сразу несколько классических подводных камней: от неявного превращения левого соединения во внутреннее до некорректного расчета типов данных и неверной настройки ролей полей в Системе компоновки данных (СКД).
В этой статье мы подробно разберем, как правильно составить запрос для вывода нулевых остатков, избежим типичных ошибок с типами данных и рассмотрим эффективные методики расчета количества дней, в течение которых товара не было на складе (показатель Out of Stock). При необходимости автоматизировать формирование таких отчетов и их рассылку можно настроить программных роботов в One RPA.
Рассмотрим стандартную ситуацию. Нам необходимо вывести всю номенклатуру (кроме групп) и остатки по ней на определенном складе. Типичный ошибочный запрос выглядит следующим образом:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток,
ТоварыНаСкладахОстатки.Склад КАК Склад
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, ) КАК ТоварыНаСкладахОстатки
ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
СпрНоменклатура.ЭтоГруппа = ЛОЖЬ
И ТоварыНаСкладахОстатки.Склад = &Склад
Проанализируем этот запрос. Мы связали справочник Справочник.Номенклатура (левая таблица) с виртуальной таблицей остатков (правая таблица) левым соединением. Для товаров, которых нет на складе, поля правой таблицы (включая Склад и КоличествоОстаток) примут значение NULL.
Но обратите внимание на секцию ГДЕ. Условие ТоварыНаСкладахОстатки.Склад = &Склад накладывается на правую таблицу. Когда СУБД начнет обрабатывать строки с отсутствующим товаром, поле Склад будет содержать значение NULL. Сравнение NULL = &Склад вернет значение «Ложь» (или «Неизвестно»). В результате все строки с нулевыми остатками будут безвозвратно отсечены из итоговой выборки. Левое соединение фактически превратилось во внутреннее!
Как решить эту проблему правильно? Все условия отбора по полям правой (присоединяемой) таблицы необходимо переносить непосредственно в параметры виртуальной таблицы. В этом случае отсечение произойдет еще на этапе формирования таблицы остатков, а левое соединение со справочником номенклатуры отработает корректно, вернув нам все позиции с NULL (которые мы преобразуем в ноль).
Правильный вариант запроса:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток,
&Склад КАК Склад
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, Склад = &Склад) КАК ТоварыНаСкладахОстатки
ПО СпрНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
СпрНоменклатура.ЭтоГруппа = ЛОЖЬ
Если вы разрабатываете отчет на СКД, жестко прописывать параметры в схеме компоновки не всегда удобно. Для гибкой фильтрации используйте фигурные скобки, чтобы СКД сама подставляла пользовательские настройки отборов в параметры виртуальной таблицы:
РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, {Склад.* КАК Склад})
Разберем очень распространенную и коварную синтаксическую ошибку. Иногда разработчики по невнимательности пишут в запросе следующее выражение:
ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, "0")
Обратите внимание на кавычки вокруг нуля. Вместо числового значения 0 функция возвращает текстовую строку "0". К каким катастрофическим последствиям в СКД это приводит?
КоличествоОстаток Меньше или равно 0), на уровне СУБД произойдет критическая ошибка несовместимости типов данных при попытке сравнить строку с числом. Отчет просто откажется формироваться.Всегда внимательно следите за типами данных при использовании функций приведения к типу NULL. Писать нужно строго числовой ноль без кавычек:
ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток
Бизнес часто требует не просто показать нулевые остатки, но и выяснить, как давно товар закончился. Это важнейший показатель для оценки упущенной прибыли, которую можно контролировать автоматически при интеграции 1С с маркетплейсами Ozon, Wildberries, Яндекс.Маркет. Рассмотрим два основных способа решения этой задачи.
Посмотрим на пример реализации этой идеи, который часто используют начинающие разработчики. Запрос строится по виртуальной таблице ОстаткиИОбороты с периодичностью День:
ВЫБРАТЬ
ТоварыОрганизацийОстаткиИОбороты.Номенклатура КАК Номенклатура,
ЕСТЬNULL(ТоварыОрганизацийОстаткиИОбороты.КоличествоКонечныйОстаток, 0) КАК Остаток,
ЕСТЬNULL(ТоварыОрганизацийОстаткиИОбороты.КоличествоРасход, 0) КАК Расход,
ТоварыОрганизацийОстаткиИОбороты.Период КАК Период
ИЗ
РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты(&НачДата, &КонДата, День, , ) КАК ТоварыОрганизацийОстаткиИОбороты
ГДЕ
ТоварыОрганизацийОстаткиИОбороты.КоличествоКонечныйОстаток <= 0
В схеме компоновки данных добавляется вычисляемое поле ЧислоДнейОтсутствия с выражением: РАЗНОСТЬДАТ(Период, ТекущаяДата(), "День").
Почему этот подход имеет огромные минусы?
День заставляет СУБД генерировать плоскую таблицу остатков на каждый день выбранного периода по каждой номенклатуре. Если в базе данных 10 000 товаров, а период отчета составляет 1 год, СУБД придется обрабатывать миллионы строк. Это приведет к зависанию базы данных.Номенклатура и Склад обязательно должны иметь роль «Измерение», а поле Период — роль «Период» с указанием порядкового номера. Без этого СКД начнет некорректно складывать остатки за разные дни арифметически, получая ложные цифры.Для оптимизации скорости работы отчета откажемся от перебора всех дней периода. Вместо этого мы найдем точную дату последнего движения «Расход», которое обнулило остаток товара на складе. Выясним разницу между этой датой и концом периода отчета. Если производительность системы падает при обработке больших объемов исторических данных, в 1С:ERP можно выполнить многопоточную свёртку регистров.
Проанализируем оптимальную схему построения такого запроса:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(ТоварыОстатки.КоличествоОстаток, 0) КАК Остаток
ПОМЕСТИТЬ ВТ_ТоварыСНулевымОстатком
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&КонецПериода, {Склад.* КАК Склад}) КАК ТоварыОстатки
ПО СпрНоменклатура.Ссылка = ТоварыОстатки.Номенклатура
ГДЕ
СпрНоменклатура.ЭтоГруппа = ЛОЖЬ
И ЕСТЬNULL(ТоварыОстатки.КоличествоОстаток, 0) <= 0
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.Номенклатура КАК Номенклатура,
МАКСИМУМ(ТоварыНаСкладах.Период) КАК ДатаПоследнегоРахода
ПОМЕСТИТЬ ВТ_ПоследниеДвижения
ИЗ
ВТ_ТоварыСНулевымОстатком КАК ВТ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах
ПО ВТ.Номенклатура = ТоварыНаСкладах.Номенклатура
ГДЕ
ТоварыНаСкладах.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Расход)
И ТоварыНаСкладах.Период <= &КонецПериода
И (&СкладБезОтбора ИЛИ ТоварыНаСкладах.Склад = &Склад)
СГРУППИРОВАТЬ ПО
ВТ.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ТоварыСНулевымОстатком.Номенклатура КАК Номенклатура,
ВТ_ТоварыСНулевымОстатком.Остаток КАК Остаток,
ВТ_ПоследниеДвижения.ДатаПоследнегоРахода КАК ДатаПоследнегоРахода,
ВЫБОР
КОГДА ВТ_ПоследниеДвижения.ДатаПоследнегоРахода ЕСТЬ NULL Тогда 365 // Товар вообще не продавался в периоде
ИНАЧЕ РАЗНОСТЬДАТ(ВТ_ПоследниеДвижения.ДатаПоследнегоРахода, &КонецПериода, ДЕНЬ)
КОНЕЦ КАК ДнейОтсутствия
ИЗ
ВТ_ТоварыСНулевымОстатком КАК ВТ_ТоварыСНулевымОстатком
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПоследниеДвижения КАК ВТ_ПоследниеДвижения
ПО ВТ_ТоварыСНулевымОстатком.Номенклатура = ВТ_ПоследниеДвижения.Номенклатура
Почему этот алгоритм работает быстрее и правильнее?
ВТ_ТоварыСНулевымОстатком только теми товарами, остаток которых на текущий момент действительно равен нулю или отрицателен.МАКСИМУМ(Период) мы мгновенно вычисляем точное время последней расходной операции (когда товар физически покинул склад).