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