Как правильно получить остатки на дату в 1С и избежать типичных ошибок в запросах

← На главную

Каждый разработчик на платформе 1С:Предприятие рано или поздно сталкивается с задачей получения остатков на определенную дату. На первый взгляд, задача кажется тривиальной: достаточно передать дату в параметры виртуальной таблицы остатков. Однако на практике возникает множество нюансов, связанных с границей времени, особенностями работы виртуальных таблиц и правилами левого соединения в запросах СУБД. Не учет этих нюансов ведет к тому, что остатки либо рассчитываются неверно (без учета документов последней секунды), либо запрос начинает безбожно зависать, либо из результатов выборки пропадают нужные данные.

В этой статье мы подробно разберем, почему возникает проблема "неработающих остатков", как правильно использовать объект Граница, и как оптимизировать левое соединение виртуальных таблиц, чтобы ваш запрос работал корректно и быстро.

Анализ исходной проблемы

Давайте проанализируем ситуацию, с которой столкнулся автор темы. Ему необходимо получить остатки по регистру накопления "Расчеты" на конец определенного дня (31.12.2018), а также связать эти остатки с оборотами за месяц по регистру "Потолки" с помощью левого соединения. В результате выполнения его кода система выдает некорректные данные или проверяет остатки "на текущий момент".

Посмотрим на исходный запрос, который вызвал затруднения:


ВЫБРАТЬ
    ПотолкиОбороты.Период,
    ПотолкиОбороты.Заказчик КАК Дилер,
    СУММА(ПотолкиОбороты.Площадь) КАК Площадь,
    РасчетыОстатки.Контрагент,
    РасчетыОстатки.БалансОстаток
ИЗ
    РегистрНакопления.Расчеты.Остатки(&ПарДата, ) КАК РасчетыОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Потолки.Обороты(, , Месяц, ) КАК ПотолкиОбороты
    ПО РасчетыОстатки.Contragent = ПотолкиОбороты.Заказчик
ГДЕ
    ПотолкиОбороты.Период МЕЖДУ &НачалоПериода И &КонецПериода
    И РасчетыОстатки.БалансОстаток >= 0

В этом коде кроется сразу три серьезные ошибки, которые мы разберем по шагам.

Ошибка 1. Граница времени и потеря документов последней секунды

В 1С:Предприятии дата хранится с точностью до секунды. Когда мы передаем обычную дату в параметры виртуальной таблицы остатков (даже если мы предварительно обработали ее функцией КонецДня, получив значение 31.12.2018 23:59:59), платформа рассчитывает остатки исключая эту секунду.

Что это означает на практике? Все документы, которые были проведены ровно в 23:59:59 (а это стандартное время для регламентных операций, закрытия месяца или просто документов, проведенных в самый конец дня оператором), не будут учтены при расчете остатков. Платформа вернет состояние регистра "накануне" этой секунды.

Для решения этой проблемы в платформе существует специальный объект — Граница. Он позволяет явно указать системе, нужно ли включать движения граничной секунды в расчет или нет.

Конструктор объекта выглядит следующим образом:

Новый Граница(Дата, ВидГраницы)

Где параметр ВидГраницы может принимать два значения:

ВидГраницы.Включая — указывает, что движения на эту дату/время должны быть включены в результат.
ВидГраницы.Исключая — движения на эту секунду в результат не попадают (поведение по умолчанию для обычной даты).

Таким образом, чтобы получить честные остатки на самый конец дня, включая все документы последней секунды, нам необходимо написать в коде инициализации параметров:

ПарДата = Новый Граница(КонецДня(Дата(2018, 12, 31)), ВидГраницы.Включая);

Альтернативный вариант ("Плюс одна секунда"):
Иногда в старых конфигурациях или внешних отчетах неудобно использовать объект Граница (например, если параметры передаются в СКД в неизменяемом виде или отправляются во внешние системы). В этом случае разработчики используют проверенный временем прием — сдвигают расчетную дату на одну секунду вперед, то есть на начало следующего дня:

ПарДата = КонецДня(КонецПериода) + 1; // Получим 01.01.2019 00:00:00

Так как остатки на эту дату рассчитываются "исключая" ее (то есть на 01.01.2019 00:00:00 исключая документы нового года), мы получим математически точный остаток на конец дня 31.12.2018 со всеми его документами.

Ошибка 2. Превращение ЛЕВОГО соединения во ВНУТРЕННЕЕ

Вторая и самая коварная логическая ошибка в запросе автора кроется в секции ГДЕ. Обратим внимание на строки:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Потолки.Обороты(, , Месяц, ) КАК ПотолкиОбороты
...
ГДЕ
    ПотолкиОбороты.Period МЕЖДУ &НачалоПериода И &КонецПериода

Давайте вспомним, как работает ЛЕВОЕ СОЕДИНЕНИЕ. Оно берет все записи из левой таблицы (в нашем случае это остатки по расчетам контрагентов) и пытается сопоставить им записи из правой таблицы (обороты по потолкам). Если для какого-то контрагента из левой таблицы не нашлось ни одного оборота в правой таблице, то во всех полях правой таблицы для этого контрагента будет возвращено значение NULL.

После того как СУБД выполнила соединение таблиц, она начинает применять фильтрацию из секции ГДЕ. И когда система проверяет условие ПотолкиОбороты.Период МЕЖДУ &НачалоПериода И &КонецПериода для тех контрагентов, у которых не было оборотов, сравнение NULL МЕЖДУ ... возвращает ложь (точнее, UNKNOWN). В результате все такие строки безжалостно отсекаются из результирующей выборки.

Таким образом, наше ЛЕВОЕ СОЕДИНЕНИЕ легким движением руки превратилось во ВНУТРЕННЕЕ (Inner Join), полностью уничтожив смысл левого соединения. Контрагенты, у которых есть остаток, но не было оборотов в выбранном периоде, просто пропали из отчета.

Как это исправить?
Все условия, накладываемые на правую (присоединяемую) таблицу, должны накладываться либо непосредственно в параметрах этой виртуальной таблицы, либо в условии связи ПО. Секция ГДЕ должна содержать фильтры исключительно по левой (основной) таблице.

Ошибка 3. Отсутствие параметров виртуальной таблицы оборотов

В исходном коде автора таблица оборотов вызывается без параметров:

РегистрНакопления.Потолки.Обороты(, , Месяц, ) КАК ПотолкиОбороты

Это классический антипаттерн разработки в 1С. Если вы не передаете параметры начала и конца периода непосредственно в виртуальную таблицу, СУБД выполнит тяжелейшую работу: она прочитает абсолютно все обороты из базы данных за весь период ведения учета (возможно, за несколько лет), построит временную таблицу в памяти и только потом начнет фильтровать ее по вашему условию ГДЕ.

Правильный подход — всегда ограничивать выборку виртуальных таблиц "на входе", передавая параметры непосредственно в их скобки:


РегистрНакопления.Потолки.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ПотолкиОбороты

В этом случае СУБД выберет из базы данных только те записи, которые укладываются в указанный интервал времени, что ускорит работу запроса в десятки и сотни раз на реальных базах данных.

Пошаговое решение и правильный вариант кода

Давайте соберем все наши исправления воедино и напишем правильный, оптимизированный и логически верный код для получения остатков на дату.

Шаг 1. Подготовка параметров на встроенном языке 1С:


НачалоПериода = Дата(2018, 12, 1);
КонецПериода = Дата(2018, 12, 31);

// Создаем Границу, чтобы учесть документы последней секунды дня
ПарДата = Новый Граница(КонецДня(КонецПериода), ВидГраницы.Включая);

Шаг 2. Написание правильного текста запроса:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    ПотолкиОбороты.Период КАК Период,
    |    РасчетыОстатки.Контрагент КАК Дилер,
    |    ЕСТЬNULL(ПотолкиОбороты.Площадь, 0) КАК Площадь,
    |    РасчетыОстатки.БалансОстаток КАК БалансОстаток
    |ИЗ
    |    РегистрНакопления.Расчеты.Остатки(&ПарДата, ) КАК РасчетыОстатки
    |        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Потолки.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ПотолкиОбороты
    |        ПО РасчетыОстатки.Контрагент = ПотолкиОбороты.Заказчик
    |ГДЕ
    |    РасчетыОстатки.БалансОстаток >= 0
    |
    |УПОРЯДОЧИТЬ ПО
    |    Дилер";

Разберем изменения в запросе подробнее:

Мы перенесли условия &НачалоПериода и &КонецПериода непосредственно в скобки виртуальной таблицы Обороты. Теперь СУБД считывает только данные за декабрь 2018 года.
Мы убрали условие по периоду оборотов из секции ГДЕ. Благодаря этому левое соединение теперь работает корректно. Если у контрагента есть положительный остаток на балансе, но не было оборотов по потолкам в декабре, он все равно попадет в наш отчет. При этом поле Площадь для него вернет 0 (благодаря функции ЕСТЬNULL).
Поскольку мы убрали группировку (в виртуальных таблицах остатков и оборотов данные уже сгруппированы разработчиками платформы), запрос стал намного проще и быстрее. Мы также добавили вывод поля Контрагент как основного связующего звена.

Шаг 3. Передача параметров и выполнение запроса:


Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("ПарДата", ПарДата);    

РезультатЗапроса = Запрос.Выполнить();

Резюме

Для корректного и быстрого получения остатков на дату в 1С всегда помните о трех золотых правилах:

Используйте объект Граница с параметром ВидГраницы.Включая для дат, соответствующих концу периода, чтобы не потерять движения документов, проведенных в последнюю секунду.
Никогда не накладывайте фильтры на поля правой (присоединяемой) таблицы левого соединения в секции ГДЕ. Переносите эти условия в параметры виртуальной таблицы или в условия связи ПО.
Всегда ограничивайте выборку виртуальных таблиц остатков и оборотов входными параметрами (внутри скобок), минимизируя объем считываемых данных из СУБД.