Как построить запрос для акта сверки в разрезе документов и контрагентов в 1С?

← На главную

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

В этой статье мы подробно разберем, как правильно составить такой запрос, проанализируем варианты решения, предложенные разработчиками на форуме, выявим их недостатки и спроектируем максимально оптимизированный, быстрый и профессиональный запрос для получения данных взаиморасчетов.

Вариант 1. Использование виртуальной таблицы ОборотыДтКт (Решение с форума)

На форумах часто предлагают решать задачу «в лоб» — с помощью объединения двух отдельных запросов к виртуальной таблице РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт. Один запрос выбирает обороты по дебету, второй — по кредиту, а затем результаты объединяются через конструкцию ОБЪЕДИНИТЬ ВСЕ.

Рассмотрим пример такого запроса:


ВЫБРАТЬ
    ВложенныйЗапрос.Контрагент КАК Контрагент,
    ВложенныйЗапрос.Регистратор КАК Регистратор,
    ВложенныйЗапрос.ОборотДт КАК ОборотДт,
    ВложенныйЗапрос.ОборотКт КАК ОборотКт
ИЗ
    (ВЫБРАТЬ
        ВложенныйЗапрос.Контрагент КАК Контрагент,
        ВложенныйЗапрос.Регистратор КАК Регистратор,
        СУММА(ВложенныйЗапрос.ОборотДт) КАК ОборотДт,
        СУММА(ВложенныйЗапрос.ОборотКт) КАК ОборотКт
    ИЗ
        (ВЫБРАТЬ
            ХозрасчетныйОборотыДтКт.СубконтоДт1 КАК Контрагент,
            ХозрасчетныйОборотыДтКт.Регистратор КАК Регистратор,
            ХозрасчетныйОборотыДтКт.СуммаОборот КАК ОборотДт,
            0 КАК ОборотКт
        ИЗ
            РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(
                &НачПериода,
                &КонПериода,
                Регистратор,
                СчетДт В (&спСчетов),
                ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыСубконтоХозрасчетные.Контрагенты),
                ,
                ,
                Организация = &Организация
                    И СубконтоДт1 В (&спКонтрагентов)) КАК ХозрасчетныйОборотыДтКт
        
        ОБЪЕДИНИТЬ ВСЕ
        
        ВЫБРАТЬ
            ХозрасчетныйОборотыДтКт.СубконтоКт1,
            ХозрасчетныйОборотыДтКт.Регистратор,
            0,
            ХозрасчетныйОборотыДтКт.СуммаОборот
        ИЗ
            РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(
                &НачПериода,
                &КонПериода,
                Регистратор,
                ,
                ,
                СчетКт В (&спСчетов),
                ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыСубконтоХозрасчетные.Контрагенты),
                Организация = &Организация
                    И СубконтоКт1 В (&спКонтрагентов)) КАК ХозрасчетныйОборотыДтКт) КАК ВложенныйЗапрос
    
    СГРУППИРОВАТЬ ПО
        ВложенныйЗапрос.Contragent,
        ВложенныйЗапрос.Регистратор) КАК ВложенныйЗапрос

Проанализируем этот подход. Данный запрос полностью рабочий и позволяет получить обороты в разрезе документов-регистраторов. Однако у него есть несколько критических недостатков, из-за которых его не рекомендуется использовать в высоконагруженных системах или на базах с большим объемом данных:

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

Вариант 2. Профессиональное решение на виртуальной таблице «ОстаткиИОбороты»

Для того чтобы получить оптимальный по скорости и лаконичный по коду запрос, мы должны использовать виртуальную таблицу РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты с периодичностью Регистратор. Эта таблица за один проход к базе данных способна вернуть начальные остатки, обороты (дебет и кредит) и конечные остатки в разрезе регистраторов.

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

Посмотрим на пример правильного и оптимизированного запроса:


ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Контрагент,
    ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Договор,
    ХозрасчетныйОстаткиИОбороты.Регистратор КАК Регистратор,
    ХозрасчетныйОстаткиИОбороты.ПериодСекунда КАК ПериодСекунда,
    ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстаток КАК НачальныйОстаток,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотДт КАК ОборотДебет,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотКт КАК ОборотКредит,
    ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстаток КАК КонечныйОстаток
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(
        &НачалоПериода,
        &КонецПериода,
        Авто,
        ,
        Счет В (&СписокСчетов),
        &МассивВидовСубконто,
        Организация = &Организация
            И Субконто1 В (&СписокКонтрагентов)
    ) КАК ХозрасчетныйОстаткиИОбороты
УПОРЯДОЧИТЬ ПО
    Контрагент,
    Договор,
    ПериодСекунда,
    Регистратор

Ключевые правила оптимизации запроса к регистру бухгалтерии

Разберем по шагам важные нюансы оптимизации, которые мы применили в этом запросе:

Правильная передача видов субконто (Параметр №6): Это самая частая ошибка новичков. Если мы не укажем в шестом параметре виртуальной таблицы массив используемых субконто (в нашем случае это Контрагенты и Договоры), то платформа 1С построит SQL-запрос с левыми соединениями ко всем возможным таблицам субконто, которые привязаны к выбранным счетам. Передав параметр &МассивВидовСубконто, мы жестко ограничиваем соединения СУБД, повышая скорость выполнения в несколько раз.
Периодичность «Авто»: Использование значения Авто в качестве периодичности позволяет СКД (Системе компоновки данных) или платформе оптимально рассчитывать нарастающие итоги, используя дополнительные поля периодов (например, ПериодСекунда), что гарантирует правильную хронологическую последовательность документов.
Фильтрация на уровне параметров виртуальной таблицы: Обратите внимание, что условие Организация = &Организация И Субконто1 В (&СписокКонтрагентов) наложено непосредственно внутри параметров виртуальной таблицы (Параметр №7 «Условие»), а не в секции ГДЕ. Это позволяет СУБД отсечь ненужные записи еще на этапе сборки виртуальной таблицы, не перегружая оперативную память сервера лишней выборкой.

Как подготовить параметры запроса в коде 1С?

Проанализируем программную подготовку параметров перед запуском запроса на выполнение. Нам необходимо заполнить список счетов учета расчетов и массив видов субконто:


// Создаем массив счетов учета расчетов с контрагентами
СписокСчетов = Новый СписокЗначений;
СписокСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыСПоставщиками);          // 60.01
СписокСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыПоАвансамВыданным);     // 60.02
СписокСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыСПокупателями);         // 62.01
СписокСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыПоАвансамПолученным);   // 62.02
СписокСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыСРазнымиДебиторами);    // 76.05

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

// Передаем параметры в запрос
Запрос = Новый Запрос;
Запрос.Текст = "ВСТАВЬТЕ_СЮДА_ТЕКСТ_ЗАПРОСА_ИЗ_ВАРИАНТА_2";
Запрос.УстановитьПараметр("НачалоПериода", НачалоДня(ПериодРегистрации.ДатаНачала));
Запрос.УстановитьПараметр("КонецПериода", КонецДня(PeriodRegistracii.DataEnd));
Запрос.УстановитьПараметр("СписокСчетов", СписокСчетов);
Запрос.УстановитьПараметр("МассивВидовСубконто", МассивВидовСубконто);
Запрос.УстановитьПараметр("Организация", ВыбраннаяОрганизация);
Запрос.УстановитьПараметр("СписокКонтрагентов", ВыбранныйКонтрагент);

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

Особенности вывода остатков и расчета нарастающего итога

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

Если же вы формируете отчет программно, например, через макет табличного документа, то рассчитывать нарастающий итог по строкам документов рекомендуется непосредственно при обходе выборки в коде 1С. В самом запросе в таком случае достаточно получить начальный остаток на дату начала периода, а далее прибавлять дебетовый оборот и вычитать кредитовый оборот по каждой строке выборки. Такой подход убережет СУБД от тяжелых вычислений остатка на каждую секунду времени проведения документов.

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