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