Как быстро рассчитать взаиморасчеты по ФИФО в 1С при учете по договору в целом?

← На главную

В процессе автоматизации учета в конфигурациях «1С:Управление производственным предприятием 1.3» (УПП) или «1С:Управление торговлей 10.3» (УТ) разработчики часто сталкиваются с дилеммой настройки взаиморасчетов. С одной стороны, ведение взаиморасчетов «по договору в целом» существенно упрощает жизнь пользователям: им не нужно вручную подбирать документы расчетов в платежных поручениях и накладных. С другой стороны, руководству и отделу продаж регулярно требуется аналитическая отчетность: необходимо видеть структуру задолженности в разрезе конкретных документов отгрузки с расчетом дней просрочки по методу ФИФО (First In, First Out — первый пришел, первый ушел).

Когда мы пытаемся решить эту задачу стандартными средствами СКД или простыми запросами с соединением таблиц «само на себя» по условию неравенства дат, мы неизбежно сталкиваемся с катастрофическим падением производительности. База данных начинает «зависать», а формирование отчета по крупным контрагентам с большой историей транзакций растягивается на десятки минут. В этой статье мы подробно разберем, почему классический подход работает медленно, и изучим революционный высокопроизводительный алгоритм на основе метода дихотомии (бинарного поиска) прямо в запросе 1С, который сокращает время формирования отчета до долей секунды.

Почему классический расчет ФИФО в запросах работает медленно?

Давайте проанализируем, как обычно разработчики пытаются сопоставить платежи и отгрузки по ФИФО, когда детальный учет по документам в регистрах не ведется. Для этого требуется рассчитать нарастающие итоги (кумулятивные суммы) отдельно по отгрузкам (увеличивающим долг) и отдельно по оплатам (уменьшающим долг), а затем сопоставить их по шкале времени.

В языке запросов 1С (и в SQL в целом, если не использовать специфические оконные функции, которые в старых версиях платформы или СУБД были недоступны) расчет нарастающего итога традиционно реализуется через левое соединение таблицы документов с самой собой по условию:


ВЫБРАТЬ
    Таблица1.Контрагент,
    Таблица1.Документ,
    Таблица1.Сумма,
    СУММА(Таблица2.Сумма) КАК НарастающийИтог
ИЗ
    ВременнаяТаблицаДокументов КАК Таблица1
    ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблицаДокументов КАК Таблица2
        ПО Таблица1.Контрагент = Таблица2.Контрагент
        И Таблица1.Дата >= Таблица2.Дата
ГРУППИРОВАТЬ ПО
    Таблица1.Контрагент,
    Таблица1.Документ,
    Таблица1.Сумма

С точки зрения СУБД, такое соединение (так называемое неэквисоединение или тэта-соединение) имеет квадратичную сложность O(N²), где N — количество документов по договору. Если у контрагента за несколько лет накопилось, например, 5000 документов отгрузок и оплат, то для расчета нарастающих итогов СУБД придется сопоставить и обработать в памяти около 25 миллионов строк! На практике это приводит к тому, что процессор сервера СУБД загружается на 100%, оперативная память переполняется, а пользователи получают сообщение об истечении времени ожидания запроса.

Революционное решение: метод дихотомии (бинарного поиска) в запросе

Решение проблемы медленной работы было найдено и опубликовано разработчиком под ником ildarovich (Сергей). Вместо того чтобы вычислять нарастающий итог для каждой строки на протяжении всей оси времени, алгоритм предлагает кардинально изменить логику поиска.

Если мы знаем текущую итоговую сумму долга контрагента (которую мы легко и мгновенно получаем из остатков регистра накопления ВзаиморасчетыСКонтрагентами), наша задача сводится к тому, чтобы найти точную секунду на оси времени (границу), начиная с которой сумма всех совершенных отгрузок будет в точности равна этой сумме долга. Всё, что было отгружено после этой границы, составляет наш текущий непогашенный долг. Всё, что было отгружено до нее — считается полностью оплаченным.

Для поиска этой временной точки используется математический метод дихотомии (деления отрезка пополам). Мы берем весь временной интервал ведения учета (например, с 2010 года по текущую дату) и делим его пополам. За один проход (линейный запрос) мы определяем сумму отгрузок во второй половине интервала:

Если сумма отгрузок во второй половине больше или равна сумме текущего долга, значит, искомая граница (точка начала формирования долга) находится внутри этой второй половины. Мы отбрасываем первую половину и продолжаем поиск во второй.
Если сумма отгрузок во второй половине меньше суммы долга, значит, часть долга сформирована еще в первой половине интервала. Мы фиксируем, что вся вторая половина точно входит в долг, вычитаем ее сумму из искомого долга и продолжаем поиск границы в первой половине интервала.

Повторяя эту операцию деления интервала пополам, мы чрезвычайно быстро сжимаем временной отрезок поиска. Поскольку в языке запросов 1С невозможно организовать классический цикл, этот бинарный поиск реализуется в виде жесткой последовательности временных таблиц в пакете запроса. Для достижения секундной точности на интервале в 34 года (около 1 миллиарда секунд) достаточно выполнить всего 30 последовательных шагов деления ($2^{30} \approx 1.073$ млрд).

Каждый такой шаг представляет собой простейший запрос к индексированным временным таблицам, который СУБД выполняет за доли миллисекунды. В результате весь пакет запросов, состоящий из 30 шагов, отрабатывает практически мгновенно даже по договорам с десятками тысяч документов.

Как реализовать алгоритм дихотомии на практике

Рассмотрим по шагам общую схему построения пакета запросов для реализации быстрого ФИФО:

Шаг 1. Получение текущего остатка долга и границ периода. Сначала мы получаем актуальный остаток долга по каждому договору из виртуальной таблицы остатков регистра ВзаиморасчетыСКонтрагентами.Остатки. Также определяем начальную и конечную точку оси времени (например, в секундах с начала эпохи 1С).

Шаг 2. Подготовка таблицы отгрузок. Помещаем все документы отгрузки (Реализации, Акты) во временную таблицу с индексацией по полям контрагента, договора и времени документа (выраженного в секундах).

Шаг 3. Реализация итераций бинарного поиска. Мы строим цепочку запросов. На каждой итерации мы вычисляем середину текущего расчетного интервала для каждого договора:


// Пример расчета середины интервала на шаге N
ВЫБРАТЬ
    Границы.Договор,
    Границы.НачалоИнтервала,
    Границы.КонецИнтервала,
    (Границы.НачалоИнтервала + Границы.КонецИнтервала) / 2 КАК СерединаИнтервала
ПОМЕСТИТЬ ВременнаяТаблица_Шаг_1_Центр
ИЗ
    ВременнаяТаблица_Границы КАК Границы
;

// Оценка суммы отгрузок во второй половине интервала
ВЫБРАТЬ
    Центр.Договор,
    СУММА(ЕСТЬNULL(Отгрузки.Сумма, 0)) КАК СуммаВторойПоловины
ПОМЕСТИТЬ ВременнаяТаблица_Шаг_1_Суммы
ИЗ
    ВременнаяТаблица_Шаг_1_Центр КАК Центр
    ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица_Отгрузок КАК Отгрузки
        ПО Центр.Договор = Отгрузки.Договор
        И Отгрузки.СекундаВремя >= Центр.СерединаИнтервала
        И Отгрузки.СекундаВремя <= Центр.КонецИнтервала
ГРУППИРОВАТЬ ПО
    Центр.Договор
;

// Корректировка границ для следующего шага
ВЫБРАТЬ
    Центр.Договор,
    ВЫБОР
        КОГДА Суммы.СуммаВторойПоловины >= Долги.СуммаДолга
            ТОГДА Центр.СерединаИнтервала // Граница во второй половине
        ИНАЧЕ Центр.НачалоИнтервала    // Граница в первой половине
    КОНЕЦ КАК НовоеНачало,
    ВЫБОР
        КОГДА Суммы.СуммаВторойПоловины >= Долги.СуммаДолга
            ТОГДА Центр.КонецИнтервала
        ИНАЧЕ Центр.СерединаИнтервала
    КОНЕЦ КАК НовоеПоложениеКонца
ПОМЕСТИТЬ ВременнаяТаблица_Границы_Шаг_2
ИЗ
    ВременнаяТаблица_Шаг_1_Центр КАК Центр
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица_Шаг_1_Суммы КАК Суммы
        ПО Центр.Договор = Суммы.Договор
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВременнаяТаблица_ТекущиеДолги КАК Долги
        ПО Центр.Договор = Долги.Договор

Этот цикл повторяется в пакете запросов необходимое количество раз. На выходе мы получаем временную таблицу, в которой для каждого договора определена точная секунда границы распределения ФИФО.

Шаг 4. Финальная выборка и сопоставление. Нам остается лишь выбрать все документы отгрузки, дата которых позже найденной границы. Для документа, на который непосредственно «легла» граница, мы рассчитываем частичную сумму остатка долга (так как он может быть оплачен частично). Все документы, созданные строго после этой секунды, попадают в отчет со своей полной суммой долга.

Полезные модификации и расширения алгоритма

Описанный математический аппарат открывает перед разработчиками 1С огромные возможности для создания быстрых и функциональных отчетов:

Детальная развертка «Документ долга — Документ оплаты». Как отмечают практикующие специалисты, базовый алгоритм можно расширить. Зная точную хронологическую последовательность, можно сопоставить не только неоплаченные накладные, но и показать пользователю, какими именно платежными поручениями или приходными кассовыми ордерами была закрыта каждая конкретная отгрузка. Это позволяет строить полноценные отсчеты по закрытию авансов.
Учет отсрочки платежа по рабочим дням. В реальных бизнес-процессах отсрочка клиентам часто предоставляется в банковских (рабочих) днях. Чтобы связать алгоритм быстрого ФИФО с производственным календарем и не потерять в скорости, разработчики предварительно нумеруют рабочие дни в отдельной индексированной временной таблице. После этого расчет даты просрочки сводится к быстрому математическому сложению индексов дней без сложных циклических проверок дат.
Анализ «возраста» складских остатков. Этот же метод дихотомии успешно применяется для решения задач складской логистики в базах, где отключен или не ведется партионный учет. Алгоритм позволяет сопоставить текущий остаток товара на складе с хронологией последних документов поступления от поставщиков, мгновенно определяя, сколько дней та или иная позиция лежит без движения.

Реализация механизмов ФИФО в современных конфигурациях 1С

Описанный подход с использованием внешних высокопроизводительных отчетов является незаменимым спасением для поддержки и развития исторических баз на обычных формах (таких как «Управление производственным предприятием, ред. 1.3» или «Управление торговлей, ред. 10.3»), где архитектура хранения данных не позволяет вносить радикальные изменения в регистры.

В современных конфигурациях нового поколения (таких как «1С:ERP Управление предприятием», «1С:Комплексная автоматизация 2» или «1С:Управление торговлей 11») фирма 1С кардинально переработала этот механизм на системном уровне:

Внедрен балансовый онлайн-режим работы взаиморасчетов.
Система больше не пытается рассчитывать ФИФО «на лету» в момент формирования отчетов. Распределение платежей по накладным выполняется автоматически при проведении документов или в рамках фонового регламентного задания распределения расчетов.
Результаты распределения сразу записываются в специализированные регистры (например, регистр накопления РасчетыСКлиентамиПоДокументам). Любые отчеты по задолженности просто считывают уже готовые, распределенные данные, что обеспечивает мгновенный вывод информации пользователю без сложных математических вычислений.