Разработчики в 1С часто сталкиваются с классической задачей: необходимо проанализировать складские запасы и определить, сколько дней конкретного товара не было на складе за определенный период. Кажется, что задача тривиальна — достаточно обратиться к виртуальной таблице остатков и оборотов с периодичностью День. Однако здесь нас подстерегает главная особенность работы СУБД в 1С.
Виртуальная таблица ОстаткиИОбороты возвращает данные только за те дни, в которых были совершены движения (обороты) по конкретной номенклатуре. Если товар лежал на складе без движения или его остаток стал равен нулю и больше не менялся, эти «пустые» дни просто не попадут в выборку. Нам же требуется получить непрерывную картину по абсолютно всем дням периода для каждой позиции номенклатуры.
В этой статье мы подробно разберем, почему так происходит, и рассмотрим несколько эффективных способов решения этой задачи: от классического дополнения периодов в запросе до возможностей Системы компоновки данных (СКД).
Рассмотрим запрос, который часто пытаются использовать начинающие разработчики:
ВЫБРАТЬ
ТоварыНаСкладахОстаткиИОбороты.Номенклатура,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(ТоварыНаСкладахОстаткиИОбороты.Период, ДЕНЬ)) КАК Период
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&Дат1, &Дат2, ДЕНЬ, , ) КАК ТоварыНаСкладахОстаткиИОбороты
ГДЕ
ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток <= 0
СГРУППИРОВАТЬ ПО
ТоварыНаСкладахОстаткиИОбороты.Номенклатура
Этот запрос вернет ошибочный результат. Он покажет нулевой остаток только для тех дней, когда был зафиксирован расход, списавший товар в ноль (или в минус). Если на следующий день движений по товару не было, то СУБД не вернет запись для этого дня, хотя фактически остаток оставался нулевым. Нам нужен механизм, который принудительно «развернет» временную шкалу и рассчитает остаток на каждый день.
Самый надежный способ получить непрерывную последовательность дат в рамках запроса — использовать физическую таблицу, в которой гарантированно есть записи на каждый день. В типовых конфигурациях 1С для этого идеально подходит регистр сведений РегламентированныйПроизводственныйКалендарь или календарь графиков работы.
Разберем по шагам логику построения такого запроса:
Посмотрим на пример практической реализации этого алгоритма:
ВЫБРАТЬ
Календарь.Дата КАК Период
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
ГДЕ
Календарь.Дата МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТоварыОбороты.Номенклатура КАК Номенклатура,
ТоварыОбороты.Период КАК Период,
ТоварыОбороты.КоличествоКонечныйОстаток КАК Остаток,
ТоварыОбороты.КоличествоОборот КАК Оборот
ПОМЕСТИТЬ ВТ_Движения
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, ДЕНЬ, , Номенклатура В ИЕРАРХИИ(&Номенклатура)) КАК ТоварыОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Календарь.Период КАК Период,
Движения.Номенклатура КАК Номенклатура,
СУММА(ВЫБОР
КОГДА Движения.Период = &НачалоПериода ТОГДА Движения.Остаток
ИНАЧЕ ВЫБОР
КОГДА Движения.Период <= ВТ_Календарь.Период ТОГДА Движения.Оборот
ИНАЧЕ 0
КОНЕЦ
КОНЕЦ) КАК ОстатокНаДень
ПОМЕСТИТЬ ВТ_ОстаткиПоДням
ИЗ
ВТ_Календарь КАК ВТ_Календарь
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Движения КАК Движения
ПО Движения.Период <= ВТ_Календарь.Период
СГРУППИРОВАТЬ ПО
ВТ_Календарь.Период,
Движения.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ОстаткиПоДням.Номенклатура КАК Номенклатура,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_ОстаткиПоДням.Период) КАК КолДнейБезТовара
ИЗ
ВТ_ОстаткиПоДням КАК ВТ_ОстаткиПоДням
ГДЕ
ВТ_ОстаткиПоДням.ОстатокНаДень <= 0
СГРУППИРОВАТЬ ПО
ВТ_ОстаткиПоДням.Номенклатура
Важное примечание: В этом запросе мы используем внутреннее соединение по условию Движения.Период <= ВТ_Календарь.Период для того, чтобы на каждую дату календаря рассчитать накопительный итог оборотов. Это позволяет получить точный остаток даже в те дни, когда движений не происходило.
Что делать, если в базе данных не ведется производственный календарь или он заполнен некорректно? Использовать другие регистры (например, КурсыВалют) — это крайне ненадежная практика (антипаттерн), так как курсы валют могут не вводиться по выходным дням.
Мы можем сгенерировать последовательность чисел и превратить их в даты прямо в запросе с помощью декартова произведения небольших виртуальных таблиц. Проанализируем пример генерации таблицы из 1000 дней (около 2.7 лет):
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, t100.v * 100 + t10.v * 10 + t1.v) КАК Период
ПОМЕСТИТЬ ВТ_ВсеДаты
ИЗ
(ВЫБРАТЬ 0 КАК v ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК t1
ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК v ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК t10 ПО ИСТИНА
ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК v ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК t100 ПО ИСТИНА
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, t100.v * 100 + t10.v * 10 + t1.v) <= &КонецПериода
;
Данный подход гарантирует получение абсолютно чистой и непрерывной последовательности дат независимо от заполненности каких-либо справочников или регистров в системе. Полученную таблицу ВТ_ВсеДаты мы можем использовать точно так же, как и таблицу календаря в Способе 1.
Если результатом вашей работы должен стать отчет, то оптимальным решением будет использование Системы компоновки данных (СКД). В платформе 1С реализован мощный механизм автоматического дополнения периодов, который работает на уровне построения отчета и не нагружает базу данных сложными соединениями.
Выполним настройку по шагам:
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты с периодичностью День.Период.День. Укажите дополнительное имя для связи (например, свяжите период с вашей номенклатурой).Период.День. Также задайте параметры начала и окончания периода дополнения (свяжите их с параметрами отчета &НачалоПериода и &КонецПериода).Выбор Когда КоличествоКонечныйОстаток <= 0 Тогда 1 Иначе 0 Конец.Сумма(ИмяВычисляемогоПоля).СКД самостоятельно сгенерирует все недостающие строки в отчете и рассчитает остатки на каждую дату. Это наиболее производительный и современный метод реализации.
Если данные необходимо обработать в коде модуля (без использования СКД), можно прибегнуть к конструкции ИТОГИ ПО... ПЕРИОДАМИ. Рассмотрим особенности этого подхода.
Конструкция позволяет платформе принудительно дополнить выборку результатами за «пустые» дни в момент обхода выборки. Посмотрим на пример кода:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТоварыОбороты.Номенклатура КАК Номенклатура,
| ТоварыОбороты.Период КАК Период,
| ТоварыОбороты.КоличествоКонечныйОстаток КАК КонечныйОстаток
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&Дат1, &Дат2, ДЕНЬ, , Номенклатура В ИЕРАРХИИ (&Ном)) КАК ТоварыОбороты
|ИТОГИ ПО
| Период ПЕРИОДАМИ(ДЕНЬ, &Дат1, &Дат2)";
Запрос.УстановитьПараметр("Дат1", НачалоПериода);
Запрос.УстановитьПараметр("Дат2", КонецПериода);
Запрос.УстановитьПараметр("Ном", ВыбраннаяНоменклатура);
РезультатЗапроса = Запрос.Выполнить();
// Важно: обход по группировкам с параметром "Все"
ВыборкаПериод = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Период", "Все");
Пока ВыборкаПериод.Следующий() Цикл
// Платформа зайдет сюда для каждого дня периода, даже если движений не было
ВыборкаНоменклатура = ВыборкаПериод.Выбрать();
Пока ВыборкаНоменклатура.Следующий() Цикл
Если ВыборкаНоменклатура.КонечныйОстаток <= 0 Тогда
// Фиксируем день отсутствия товара
КонецЕсли;
КонецЦикла;
КонецЦикла;
Предупреждение: Данный метод имеет технические ограничения. Его нельзя использовать во вложенных запросах или для создания временных таблиц. Конструкция ПЕРИОДАМИ работает исключительно на самом верхнем уровне запроса в секции ИТОГИ и предназначена только для последующего программного анализа в цикле.
При расчете дней отсутствия товара на складе разработчики часто упускают из виду важный бизнес-сценарий: внутридневные обороты.
Представим ситуацию: на начало дня остаток товара был равен 0. В 12:00 поступило 10 штук товара. В 16:00 все 10 штук были проданы. На конец дня остаток снова равен 0.
Если мы будем анализировать только КонечныйОстаток <= 0, то система посчитает этот день как день полного отсутствия товара на складе. Однако фактически в течение нескольких часов товар был в наличии и продавался!
Для корректного анализа ситуации рекомендуется проверять не только конечный остаток, но и факт прихода товара за день. В условиях проверки дней отсутствия используйте конструкцию:
ВТ_ОстаткиПоДням.ОстатокНаДень <= 0 И ВТ_ОстаткиПоДням.ОборотПриход = 0
Это позволит избежать искажения аналитических данных и предоставит бизнесу точную информацию об упущенных продажах.