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