В практике разработки на платформе 1С предприятие часто возникает задача получения остатков по регистру накопления с исключением движений, которые сделал определенный документ или группа документов. Классический пример из сферы расчета зарплаты: при заполнении документа ЗарплатаКВыплатеОрганизаций необходимо получить чистый остаток взаиморасчетов с сотрудниками, но исключить из него суммы будущих удержаний по займам, которые уже зарегистрированы в системе документом РасчетУдержаний.
В этой статье мы подробно разберем, почему стандартные попытки решить эту задачу «в лоб» через виртуальные таблицы оборотов приводят к ошибкам, и как реализовать технически правильный и производительный запрос с использованием временных таблиц.
Начинающие разработчики (или специалисты после длительного перерыва в работе) часто пытаются использовать виртуальную таблицу ОстаткиИОбороты с периодичностью Регистратор, чтобы наложить фильтр на поле Регистратор. Схема выглядит примерно так:
ВЫБРАТЬ
ВзаиморасчетыОстаткиИОбороты.Физлицо,
ВзаиморасчетыОстаткиИОбороты.СуммаВзаиморасчетовОборот КАК Сумма,
ВзаиморасчетыОстаткиИОбороты.Регистратор
ИЗ
РегистрНакопления.ВзаиморасчетыСРаботникамиОрганизаций.ОстаткиИОбороты(, , Регистратор, , Организация = &парамОрганизация) КАК ВзаиморасчетыОстаткиИОбороты
Почему этот подход в корне неверен и опасен для базы данных?
Регистратор, платформа 1С вынуждена динамически вычислять остаток на момент каждого проведенного документа. На реальной базе с тысячами сотрудников и документов этот запрос приведет к катастрофическому падению производительности и зависанию сессии.ГДЕ НЕ Регистратор ССЫЛКА Документ.РасчетУдержаний на таблицу оборотов или остатков-оборотов, мы отсечем только сами движения этого документа, но не скорректируем итоговый остаток, который сформировался с учетом этого документа ранее.Поэтому единственно верный технический путь получения остатка за минусом влияния конкретного документа — это математическая корректировка конечного остатка.
Проанализируем суть операции. Если документ РасчетУдержаний с операцией погашения займа уже провелся, он сделал движение «Расход» в регистре взаиморасчетов (уменьшил долг организации перед сотрудником). Чтобы «откатить» его влияние и вернуть удержанную сумму в доступный остаток для выплаты зарплаты, нам нужно математически прибавить эту сумму обратно к итоговому остатку.
Разберем по шагам, как построить такой запрос с использованием временных таблиц для оптимизации производительности СУБД.
Сначала мы выбираем актуальные остатки по регистру накопления на нужную дату без использования ресурсоемких соединений.
ВЫБРАТЬ
ВзаиморасчетыОстатки.Физлицо КАК Физлицо,
ВзаиморасчетыОстатки.СуммаВзаиморасчетовОстаток КАК СуммаОстатка
ПОМЕСТИТЬ ВТ_РеальныеОстатки
ИЗ
РегистрНакопления.ВзаиморасчетыСРаботникамиОрганизаций.Остатки(&парамГраница, Организация = &парамОрганизация) КАК ВзаиморасчетыОстатки
ИНДЕКСИРОВАТЬ ПО
Физлицо
;
Вторым запросом в пакет мы выбираем непосредственно те суммы, которые необходимо исключить. Мы обращаемся к табличной части ПогашениеЗаймов документа РасчетУдержаний. Обратите внимание на фильтрацию по проведению документа и периоду регистрации.
ВЫБРАТЬ
РасчетУдержанийЗаймы.Физлицо КАК Физлицо,
// Сумма идет с положительным знаком, так как мы будем прибавлять ее обратно к остатку
РасчетУдержанийЗаймы.СуммаВзаиморасчетов КАК СуммаИсключить
ПОМЕСТИТЬ ВТ_ИсключаемыеЗаймы
ИЗ
Документ.РасчетУдержаний.ПогашениеЗаймов КАК РасчетУдержанийЗаймы
ГДЕ
РасчетУдержанийЗаймы.Ссылка.Проведен = ИСТИНА
И РасчетУдержанийЗаймы.Ссылка.Организация = &парамОрганизация
И РасчетУдержанийЗаймы.Ссылка.ПериодРегистрации = &парамБудущийПериод
ИНДЕКСИРОВАТЬ ПО
Физлицо
;
Теперь мы объединяем полученные таблицы с помощью конструкции ОБЪЕДИНИТЬ ВСЕ и схлопываем результат по сотрудникам через СГРУППИРОВАТЬ ПО. Это позволяет избежать использования тяжелых конструкций ЛЕВОЕ СОЕДИНЕНИЕ, которые при наличии пустых значений (NULL) могут искажать арифметические расчеты.
ВЫБРАТЬ
ОбъединеннаяТаблица.Физлицо КАК Физлицо,
СУММА(ОбъединеннаяТаблица.Сумма * &парамПроцентВыплаты) КАК Сумма,
0 КАК ДепонированнаяСумма,
&парамВыплачено КАК ВыплаченностьЗарплаты
ИЗ
(ВЫБРАТЬ
ВТ_РеальныеОстатки.Физлицо КАК Физлицо,
ВТ_РеальныеОстатки.СуммаОстатка КАК Сумма
ИЗ
ВТ_РеальныеОстатки КАК ВТ_РеальныеОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ_ИсключаемыеЗаймы.Физлицо КАК Физлицо,
ВТ_ИсключаемыеЗаймы.СуммаИсключить КАК Сумма
ИЗ
ВТ_ИсключаемыеЗаймы КАК ВТ_ИсключаемыеЗаймы) КАК ОбъединеннаяТаблица
СГРУППИРОВАТЬ ПО
ОбъединеннаяТаблица.Физлицо
Рассмотренный выше технический прием полностью решает задачу, поставленную программисту. Однако с точки зрения методологии учета в решениях 1С (ЗУП 2.5, УПП 1.3), возникновение такой потребности часто сигнализирует об ошибках ведения учета бухгалтерами.
Рассмотрим, почему возникает эта проблема и как решить ее без кастомизации кода запросов:
Отпуска и указанием конкретных документов-оснований (начислений отпуска). В этом случае программа выплатит ровно сумму отпуска, не затрагивая основные начисления сотрудников, их плановые удержания и графики погашения займов.Если же убедить пользователей изменить бизнес-процесс невозможно, предложенное техническое решение с добавлением опционального флага (например, «Исключать займы будущих периодов») на форму выплаты и программным подключением нашего скорректированного запроса в модуле заполнения ТЧ станет наиболее стабильным и безопасным выходом из ситуации.