При разработке отчетов по взаиморасчетам, таких как «Анализ дебиторской задолженности по срокам долга», «Классификация задолженности по интервалам просрочки» или распределение платежей по методу ФИФО, разработчики часто сталкиваются с серьезным ограничением платформы «1С:Предприятие». Нам необходимо получить остаток долга в разрезе конкретных документов-регистраторов (например, накладных) на определенную дату (например, на сегодня или на выбранную дату параметра).
Если мы попытаемся использовать стандартную виртуальную таблицу ОстаткиИОбороты регистра накопления взаиморасчетов и выберем поле Регистратор в качестве измерения (или добавим его в детальные записи), платформа вернет нам остаток на момент проведения каждого конкретного регистратора, а вовсе не на конечную дату нашего параметра. Это особенность работы СУБД и виртуальных таблиц 1С: остаток рассчитывается динамически на «момент времени» самой записи.
В этой статье мы подробно разберем, почему возникает эта проблема, и проанализируем пять практических методов ее решения — от простых алгоритмов постобработки в коде до сложных математических запросов ФИФО и современных архитектурных подходов, используемых в типовых конфигурациях 1С.
Рассмотрим классический запрос, который часто пытаются написать начинающие разработчики:
ВЫБРАТЬ
Взаиморасчеты.Контрагент КАК Контрагент,
Взаиморасчеты.Договор КАК Договор,
Взаиморасчеты.Регистратор КАК Регистратор,
Взаиморасчеты.СуммаВзаиморасчетовКонечныйОстаток КАК ОстатокДолга
ИЗ
РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Запись, , Контрагент = &ВыбКонтрагент) КАК Взаиморасчеты
При таком подходе платформа выполняет расчет остатка последовательно. Для каждой строки-документа (регистратора) выводится остаток, актуальный только на момент проведения этого документа. Если после накладной от 1-го числа последовала оплата 5-го числа, то в строке накладной от 1-го числа остаток все равно будет равен полной сумме накладной, так как на тот момент оплата еще не совершилась. Нам же требуется увидеть актуальный остаток по этой накладной на текущую дату (с учетом всех последующих оплат).
Для решения этой задачи необходимо использовать специализированные алгоритмы сопоставления долгов и оплат.
---
Это один из самых надежных и простых для понимания методов, который отлично подходит для средних объемов данных. Его суть заключается в разделении задачи: СУБД отдает плоские данные, а логика распределения (погашения) долгов выполняется в оперативной памяти сервера приложений 1С.
Разберем этот алгоритм по шагам:
Посмотрим на пример программной реализации этого метода:
// Шаг 1. Получаем общий остаток и детальные продажи
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВзаиморасчетыОстатки.Контрагент КАК Контрагент,
| ВзаиморасчетыОстатки.СуммаВзаиморасчетовОстаток КАК ОбщийОстаток
|ПОМЕСТИТЬ ВТ_Остатки
|ИЗ
| РегистрНакопления.ВзаиморасчетыСКонтрагентами.Остатки(&ДатаАнализа, Контрагент = &ВыбКонтрагент) КАК ВзаиморасчетыОстатки
|;
|
|ВЫБРАТЬ
| ВзаиморасчетыОбороты.Регистратор КАК Регистратор,
| ВзаиморасчетыОбороты.Период КАК Период,
| ВзаиморасчетыОбороты.СуммаВзаиморасчетовОборот КАК СуммаПродажи
|ИЗ
| РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты(, &ДатаАнализа, Регистратор, Контрагент = &ВыбКонтрагент) КАК ВзаиморасчетыОбороты
|ГДЕ
| ВзаиморасчетыОбороты.СуммаВзаиморасчетовОборот > 0
|
|УПОРЯДОЧИТЬ ПО
| Период УБЫВ"; // Сортируем от новых к старым
Запрос.УстановитьПараметр("ДатаАнализа", Новый Граница(ДатаАнализа, ГраницаТипа.Включая));
Запрос.УстановитьПараметр("ВыбКонтрагент", Контрагент);
РезультатПакет = Запрос.ВыполнитьПакет();
ТаблицаОстатков = РезультатПакет[0].Выгрузить();
ТаблицаПродаж = РезультатПакет[1].Выгрузить();
// Проверяем, есть ли вообще долг
Если ТаблицаОстатков.Количество() > 0 Тогда
ОстатокРаспределения = ТаблицаОстатков[0].ОбщийОстаток;
Иначе
ОстатокРаспределения = 0;
КонецЕсли;
// Шаг 2. Распределяем остаток по документам
ТаблицаРезультат = Новый ТаблицаЗначений;
ТаблицаРезультат.Колонки.Добавить("Регистратор");
ТаблицаРезультат.Колонки.Добавить("ОстатокДолга");
Для Каждого СтрокаПродажи Из ТаблицаПродаж Цикл
Если ОстатокРаспределения <= 0 Тогда
Прервать; // Весь долг распределен
КонецЕсли;
НоваяСтрока = ТаблицаРезультат.Добавить();
НоваяСтрока.Регистратор = СтрокаПродажи.Регистратор;
Если СтрокаПродажи.СуммаПродажи <= ОстатокРаспределения Тогда
НоваяСтрока.ОстатокДолга = СтрокаПродажи.СуммаПродажи;
ОстатокРаспределения = ОстатокРаспределения - СтрокаПродажи.СуммаПродажи;
Иначе
НоваяСтрока.ОстатокДолга = ОстатокРаспределения;
ОстатокРаспределения = 0;
КонецЕсли;
КонецЦикла;
// Полученную таблицу можно передать в СКД как внешний набор данных
Преимущества метода: Высокая скорость работы на небольших и средних выборках, простота отладки и модификации логики распределения.
Недостатки метода: Если отчет строится по тысячам контрагентов одновременно, выгрузка данных в оперативную память и обработка в цикле на сервере приложений вызовет просадку производительности.
---
Если требуется получить данные исключительно в СУБД (например, для сложного отчета СКД, выполняющегося строго на сервере базы данных), нам придется рассчитывать кумулятивную (нарастающую) сумму долгов и оплат прямо в запросе.
Классическое соединение таблицы самой с собой для расчета нарастающего итога по условию Т1.МоментВремени >= Т2.МоментВремени имеет квадратичную сложность O(N²). Если у вас по одному контрагенту накоплено 10 000 документов, такой запрос может привести к зависанию СУБД.
Для обхода этого ограничения используется метод «Баттерфляй» (бинарное дерево). Мы разбиваем множество документов на пары, затем на четверки, восьмерки и так далее с помощью временных таблиц и побитовых сдвигов (или целочисленного деления номеров строк). Это позволяет рассчитать нарастающий итог с вычислительной сложностью O(N log N).
Рассмотрим базовую концепцию построения интервалов нарастающего итога в одном запросе:
// 1. Формируем временную таблицу продаж с порядковыми номерами строк
// 2. Рассчитываем нарастающий итог продаж (кумулятивный долг)
ВЫБРАТЬ
Т1.Регистратор КАК Регистратор,
Т1.СуммаПродажи КАК СуммаПродажи,
СУММА(Т2.СуммаПродажи) КАК НарастающийДогКонец
ПОМЕСТИТЬ ВТ_НарастающийДолг
ИЗ
ВТ_Продажи КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Продажи КАК Т2
ПО Т1.ПорядковыйНомер >= Т2.ПорядковыйНомер
ГРУППИРОВАТЬ ПО
Т1.Регистратор,
Т1.СуммаПродажи
После получения нарастающего итога по продажам мы можем сопоставить его с общей суммой оплат на выбранную дату. Каждая накладная получает свой диапазон задолженности: от (НарастающийДолгКонец - СуммаПродажи) до НарастающийДолгКонец. Если общая сумма оплат меньше начала этого диапазона — документ вообще не оплачен. Если больше конца — оплачен полностью.
---
Система компоновки данных (СКД) предоставляет разработчику альтернативный способ расчета нарастающих итогов без усложнения SQL-запроса к базе данных. СУБД возвращает серверу приложений плоскую таблицу оборотов, а математический подсчет кумулятивной суммы выполняется внутренними оптимизированными алгоритмами СКД.
Для этого в СКД на вкладке «Вычисляемые поля» или прямо в выражении ресурсов используется функция ВычислитьВыражение(). Рассмотрим подробнее ее синтаксис:
ВычислитьВыражение("Сумма(СуммаОборот)", , , "Первая", "Текущая")
Этот параметр указывает системе рассчитывать сумму с первой строки группировки по текущую строку включительно, реализуя классический нарастающий итог. Сопоставив этот итог с общим остатком в настройках схемы компоновки, можно вывести корректные остатки по документам на любую дату без написания сложных запросов.
---
Этот математический метод позволяет рассчитать остатки неоплаченных накладных полностью на уровне СУБД. Он базируется на сопоставлении интервалов чисел.
Проанализируем алгоритм:
ВЫБРАТЬ
Д.Регистратор КАК Регистратор,
ВЫБОР
// Случай 1: Сумма оплат не дошла до начала интервала накладной (накладная вообще не оплачена)
КОГДА &ОбщаяСуммаОплат <= Д.ИнтервалНачало
ТОГДА Д.СуммаДокумента
// Случай 2: Сумма оплат полностью покрыла интервал накладной (накладная закрыта)
КОГДА &ОбщаяСуммаОплат >= Д.ИнтервалКонец
ТОГДА 0
// Случай 3: Сумма оплат попала внутрь интервала (накладная оплачена частично)
ИНАЧЕ Д.ИнтервалКонец - &ОбщаяСуммаОплат
КОНЕЦ КАК ОстатокДолгаПоДокументу
ИЗ
ВТ_Долги КАК Д
Этот метод работает чрезвычайно быстро на современных СУБД (MS SQL Server, PostgreSQL), поскольку логика ветвления ВЫБОР КОГДА оптимизируется на уровне планов запросов базы данных.
---
Фирма «1С» в своих флагманских решениях отказалась от динамического расчета остатков по регистраторам «на лету» ввиду критических требований к производительности крупных ERP-систем. Вместо этого была внедрена архитектура двухфазных взаиморасчетов.
Разберем, как устроен этот механизм:
РасчетыСКлиентамиПоСрокам, РасчетыСПоставщикамиПоСрокам). Эти регистры хранят информацию о плановых и фактических датах погашения долгов.Благодаря этому для получения остатка по регистратору на любую дату программисту не нужно писать сложные распределительные алгоритмы. Достаточно сделать простейший запрос к виртуальной таблице .Остатки этого специализированного регистра:
ВЫБРАТЬ
РасчетыПоСрокам.АналитикаУчетаПоПартнерам КАК Партнер,
РасчетыПоСрокам.Заказ КАК ОбъектРасчетов,
РасчетыПоСрокам.РасчетныйДокумент КАК Регистратор,
РасчетыПоСрокам.ДолгОстаток КАК ОстатокДолга
ИЗ
РегистрНакопления.РасчетыСКлиентамиПоСрокам.Остатки(&ДатаПараметр, ) КАК РасчетыПоСрокам
Если вы проектируете крупную корпоративную систему или дорабатываете самописную конфигурацию с высокой нагрузкой по взаиморасчетам, настоятельно рекомендуется пойти именно по этому пути: создать физический регистр остатков в разрезе расчетных документов и производить распределение оплат при проведении документов или в фоновом режиме.
Выбор конкретного метода зависит от архитектуры вашей базы данных и объема операций: