При разработке отчетов и аналитических обработок в 1С мы часто сталкиваемся с классической задачей: необходимо вывести состояние склада (остатки) или взаиморасчетов на каждую календарную дату внутри выбранного периода. Казалось бы, задача тривиальная, но стандартные механизмы виртуальных таблиц могут вести себя не совсем так, как ожидает начинающий разработчик, создавая "дыры" в хронологии.
Рассмотрим подробнее проблему, с которой столкнулся автор темы, и разберем по шагам, почему она возникает и какие существуют проверенные способы её решения. Мы проанализируем как "быстрый" способ через настройки отчета, так и "фундаментальный" способ через написание кода запроса.
Когда мы обращаемся к виртуальной таблице регистра накопления, например, ОстаткиИОбороты, и устанавливаем параметр периодичности День, платформа 1С возвращает нам данные только за те периоды, в которых были физические движения (приход или расход) или где был остаток на начало/конец, если это предусмотрено логикой.
Однако, если товар лежал на складе без движения три дня подряд, виртуальная таблица в режиме Обороты или смешанном режиме может просто пропустить эти даты в выборке. В результате, при выводе отчета мы получаем разорванный график: есть данные за 1-е число, потом сразу за 5-е, а промежуток с 2 по 4 число отсутствует. Для пользователя это выглядит некорректно: ему нужен непрерывный временной ряд.
Разберем детально два основных подхода к решению этой задачи, которые обсуждались в теме и дополнены лучшими практиками.
Если ваша задача — создать отчет, и вы используете Систему Компоновки Данных (что является стандартом для современных конфигураций), решение проблемы кроется в настройках самой схемы компоновки, без необходимости усложнять запрос.
Это самый элегантный и простой способ, не требующий написания сложного кода. Посмотрим, как это настроить:
Период, Товар и КоличествоКонечныйОстаток.Период (или ПериодДень, в зависимости от того, как вы назвали поле в запросе).&НачалоПериода и &КонецПериода.Как это работает?
Компоновщик макета автоматически создаст "скелет" из всех дат внутри указанного периода. Затем он присоединит к этому скелету ваши данные. Там, где данных нет, СКД автоматически подставит значения (пустые или значения предыдущего периода, если правильно настроить роли полей).
Для корректного вывода остатков (чтобы в пустой день выводился остаток предыдущего дня, а не ноль) важно проверить настройки ролей полей в наборе данных:
КоличествоКонечныйОстаток должно быть определено как Ресурс.Если вы пишете не отчет на СКД, а обработку, выгрузку данных или сложную выборку для дальнейших расчетов в коде, вам необходимо получить готовую таблицу значений с полным списком дат прямо из результата запроса. Здесь настройки СКД нам не помогут, придется действовать методами языка запросов.
Общий алгоритм действий выглядит так:
В 1С нет встроенной функции SQL "GenerateDateSeries", поэтому мы должны проявить изобретательность. Рассмотрим два метода получения календаря.
Метод А: Использование Регистров сведений
Во многих типовых конфигурациях (ERP, УТ, ЗУП, Бухгалтерия) есть регистр сведений РегламентированныйПроизводственныйКалендарь или КалендарныеГрафики. Это готовый источник дат.
Пример кода запроса к календарю:
ВЫБРАТЬ
Календарь.ДатаКалендаря КАК Период
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
ГДЕ
Календарь.ДатаКалендаря МЕЖДУ &НачалоПериода И &КонецПериода
Этот метод хорош своей простотой, но он зависит от того, заполнен ли календарь в пользовательском режиме. Если пользователь не заполнил календарь на следующий год, ваш отчет перестанет работать.
Метод Б: Генерация числовой последовательности (Универсальный)
Этот метод работает в любой конфигурации, даже пустой. Мы генерируем последовательность чисел (0, 1, 2, 3...) и прибавляем это количество дней к дате начала. Так мы получаем все даты.
Сначала создадим таблицу цифр от 0 до 9:
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ ВТ_Цифры
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 2
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 5
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 6
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 7
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 8
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 9
;
Теперь, комбинируя эту таблицу саму с собой, мы можем получить последовательность от 0 до 999 (или больше, если нужно):
ВЫБРАТЬ
ДЕСЯТКИ.Цифра * 10 + ЕДИНИЦЫ.Цифра КАК Число
ПОМЕСТИТЬ ВТ_Числа
ИЗ
ВТ_Цифры КАК ДЕСЯТКИ,
ВТ_Цифры КАК ЕДИНИЦЫ
;
И наконец, формируем календарь, прибавляя дни к дате начала:
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Числа.Число) КАК Период
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
ВТ_Числа КАК Числа
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Числа.Число) <= &КонецПериода
Теперь у нас есть временная таблица ВТ_Календарь, в которой гарантированно есть все дни.
Теперь самое интересное. Просто соединить Календарь и таблицу ОстаткиИОбороты недостаточно для получения остатков на каждый день, так как таблица остатков содержит записи только на моменты изменений.
Важное предостережение: Получить "скользящий" остаток (Rolling Balance) чисто в запросе 1С — задача ресурсоемкая. Запрос, который рассчитывает остаток на каждый день путем суммирования всех оборотов от начала времен до текущей даты календаря, будет работать крайне медленно на больших базах.
Поэтому наиболее эффективный алгоритм для программиста выглядит так:
Рассмотрим пример логики обхода выборки, так как это быстрее и надежнее сложного запроса:
// 1. Получаем остаток на начало месяца
Запрос.Текст = "ВЫБРАТЬ ... ИЗ РегистрНакопления.ТоварыНаСкладах.Остатки(&НачалоПериода, ) ...";
ТЗ_Остатки = Запрос.Выполнить().Выгрузить();
// 2. Получаем обороты по дням
Запрос.Текст = "ВЫБРАТЬ Период, Товар, ВНаличииПриход, ВНаличииРасход ...";
ТЗ_Обороты = Запрос.Выполнить().Выгрузить();
ТЗ_Обороты.Индексы.Добавить("Период, Товар");
// 3. Проходим циклом по каждому дню
ТекущаяДата = НачалоПериода;
Пока ТекущаяДата <= КонецПериода Цикл
// Для каждого товара актуализируем остаток
Для Каждого СтрокаТовара Из ТЗ_Остатки Цикл
// Ищем, были ли движения в этот день
Отбор = Новый Структура("Период, Товар", ТекущаяДата, СтрокаТовара.Товар);
НайденныеОбороты = ТЗ_Обороты.НайтиСтроки(Отбор);
// Если движения были, корректируем текущий остаток
Для Каждого Движение Из НайденныеОбороты Цикл
СтрокаТовара.Количество = СтрокаТовара.Количество + Движение.ВНаличииПриход - Движение.ВНаличииРасход;
КонецЦикла;
// ВЫВОДИМ ИЛИ ЗАПИСЫВАЕМ РЕЗУЛЬТАТ НА ЭТУ ДАТУ
НоваяСтрока = ТЗ_Результат.Добавить();
НоваяСтрока.Период = ТекущаяДата;
НоваяСтрока.Товар = СтрокаТовара.Товар;
НоваяСтрока.Остаток = СтрокаТовара.Количество;
КонецЦикла;
ТекущаяДата = ТекущаяДата + 86400; // +1 день
КонецЦикла;
Такой подход называется "Пост-обработка результата запроса". Он работает на порядок быстрее, чем попытка заставить SQL-сервер рассчитывать нарастающие итоги для каждой строки через вложенные запросы.
Если вы всё же решили делать всё одним запросом (например, для простых отчетов), и используете Левое Соединение календаря с данными, помните о функции ЕСТЬNULL (ISNULL).
Когда в таблице остатков нет записи за определенный день (из календаря), результат выборки полей регистра будет NULL. Математические операции с NULL (например, сложение) дадут NULL.
Правильная конструкция в поле выборки:
ЕСТЬNULL(ТаблицаОстатков.КоличествоОстаток, 0)
Однако, помните: простое левое соединение с таблицей ОстаткиИОбороты не заполнит "пропуски" значением предыдущего дня. Оно просто покажет 0 (или NULL). Чтобы "протянуть" остаток с предыдущего дня в запросе, требуется использовать сложные коррелированные подзапросы, что не рекомендуется для производительности.
Подведем итоги. Выбор метода зависит от вашей цели:
РегламентированныйПроизводственныйКалендарь или генерацию чисел) и последующий расчет нарастающего итога в цикле на встроенном языке 1С.Используйте эти приемы, и ваши отчеты всегда будут полными и информативными, а код — оптимальным по быстродействию.