При разработке отчетов в среде 1С:Предприятие программисты часто сталкиваются с задачей создания гибких систем фильтрации. Классический сценарий: пользователю необходимо предоставить возможность отбора данных по нескольким разрезам (например, по Складу и Номенклатуре). При этом параметры отбора должны быть необязательными. Если пользователь указывает конкретный склад, отчет должен формироваться только по нему. Если оставляет поле пустым — система должна проигнорировать этот фильтр и вывести данные по всем складам.
В современных конфигурациях такие задачи по умолчанию решаются с помощью Системы компоновки данных (СКД), которая автоматически управляет доступными полями и условиями запроса. Однако бывают ситуации, когда отчет необходимо построить вручную — через прямой запрос и программный вывод в табличный документ (например, при создании специализированных печатных форм, выгрузок данных или при работе в жестко ограниченных кастомных интерфейсах). В этой статье мы подробно разберем, как правильно реализовать такие отборы без использования СКД.
Мы проанализируем как базовый (простой) вариант решения «в лоб», так и профессиональные, более производительные подходы, которые уберегут вашу базу данных от зависаний на больших объемах информации.
Рассмотрим самый простой способ реализации необязательного отбора, который часто используется начинающими разработчиками. Он заключается в написании универсального логического условия непосредственно внутри текста запроса.
Для начала подготовим интерфейсную часть. Нам понадобятся два реквизита на форме отчета:
СправочникСсылка.Склады.СправочникСсылка.Номенклатура.Теперь обратимся к логике работы запроса. Чтобы сделать параметры необязательными, мы должны применить проверку на заполненность (сравнение с пустой ссылкой соответствующего справочника). Наш запрос к виртуальной таблице остатков и оборотов регистра бухгалтерии примет следующий вид:
ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Субконто3 КАК Склад,
ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Номенклатура,
СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоНачальныйОстаток) КАК НачальныйОстаток,
СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоОборотДт) КАК Приход,
СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоОборотКт) КАК Расход,
СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КонечныйОстаток
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(
,
,
,
,
Счет = &Счет4101,
,
(Субконто1 = &Номенклатура ИЛИ &Номенклатура = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка))
И (Субконто3 = &Склад ИЛИ &Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка))
) КАК ХозрасчетныйОстаткиИОбороты
СГРУППИРОВАТЬ ПО
ХозрасчетныйОстаткиИОбороты.Субконто3,
ХозрасчетныйОстаткиИОбороты.Субконто1
ИТОГИ
СУММА(НачальныйОстаток),
СУММА(Приход),
СУММА(Расход),
СУММА(КонечныйОстаток)
ПО
ОБЩИЕ
Проанализируем, как работает это условие. Конструкция состоит из двух независимых блоков, объединенных логическим оператором И. Рассмотрим один из них:
(Субконто1 = &Номенклатура ИЛИ &Номенклатура = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка))
Если пользователь оставил поле номенклатуры на форме пустым, то в параметр &Номенклатура будет передана пустая ссылка. В этом случае правая часть выражения (&Номенклатура = ЗНАЧЕНИЕ(...)) вернет значение ИСТИНА. Благодаря оператору ИЛИ, все выражение целиком также вернет ИСТИНА для абсолютно любой строки базы данных, независимо от того, что хранится в поле Субконто1. Отбор по номенклатуре фактически отключается.
Если же пользователь выбрал конкретную позицию номенклатуры, правая часть выражения станет ложной. В этом случае СУБД будет вынуждена оценивать левую часть выражения: Субконто1 = &Номенклатура. Таким образом, отбор сработает в штатном режиме.
Альтернативным вариантом написания этого же условия является использование конструкции ВЫБОР. Проанализируем следующий пример записи условий в параметрах виртуальной таблицы:
ВЫБОР
КОГДА &Номенклатура = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка) ТОГДА ИСТИНА
ИНАЧЕ Субконто1 = &Номенклатура
КОНЕЦ
И
ВЫБОР
КОГДА &Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка) ТОГДА ИСТИНА
ИНАЧЕ Субконто3 = &Склад
КОНЕЦ
Этот вариант по своей логике полностью идентичен первому, однако он более нагляден для чтения кода некоторыми разработчиками.
Хотя первый вариант прост в реализации и прекрасно работает на небольших тестовых базах данных, использовать его в реальных "живых" конфигурациях с большим объемом данных крайне не рекомендуется. Выясним причину этой проблемы.
Проблема производительности СУБД: При наличии логического оператора ИЛИ (или конструкции ВЫБОР) в условиях запроса оптимизатор СУБД (будь то MS SQL Server или PostgreSQL) не может заранее построить эффективный план выполнения запроса. Он не знает, какой именно параметр придет (пустой или заполненный), и в большинстве случаев отказывается от использования индексов. Вместо быстрого поиска по индексу (Index Seek) СУБД выполняет полное сканирование таблиц или индексов (Table Scan / Index Scan). Это приводит к колоссальным нагрузкам на сервер, замедлению работы отчета и избыточным блокировкам данных.
Чтобы избежать проблем с производительностью, мы должны применить динамическое формирование текста запроса. Суть метода проста: мы анализируем заполненность реквизитов на форме программно. Если реквизит заполнен — мы добавляем жесткое и быстрое условие (равенство) в запрос. Если реквизит пуст — мы вообще не пишем никакого условия, избегая лишних проверок в СУБД.
Рассмотрим пример кода на встроенном языке 1С, реализующий этот подход:
// Создаем массив для накопления активных условий
МассивУсловий = Новый Массив;
ПараметрыЗапроса = Новый Структура;
// Проверяем заполненность Склада на форме
Если ЗначениеЗаполнено(Склад) Тогда
МассивУсловий.Добавить("Субконто3 = &Склад");
ПараметрыЗапроса.Вставить("Склад", Склад);
КонецЕсли;
// Проверяем заполненность Номенклатуры на форме
Если ЗначениеЗаполнено(Номенклатура) Тогда
МассивУсловий.Добавить("Субконто1 = &Номенклатура");
ПараметрыЗапроса.Вставить("Номенклатура", Номенклатура);
КонецЕсли;
// Собираем условия в единую строку для подстановки
СтрокаУсловий = "";
Если МассивУсловий.Количество() > 0 Тогда
СтрокаУсловий = СтрСоединить(МассивУсловий, " И ");
КонецЕсли;
// Создаем шаблон запроса с текстовым маркером %УсловиеОтбора%
ТекстШаблона =
"ВЫБРАТЬ
| ХозрасчетныйОстаткиИОбороты.Субконто3 КАК Склад,
| ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Номенклатура,
| СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоНачальныйОстаток) КАК НачальныйОстаток,
| СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоОборотДт) КАК Приход,
| СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоОборотКт) КАК Расход,
| СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КонечныйОстаток
|ИЗ
| РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(,,, Счет = &Счет4101,, %УсловиеОтбора%) КАК ХозрасчетныйОстаткиИОбороты
|
|СГРУППИРОВАТЬ ПО
| ХозрасчетныйОстаткиИОбороты.Субконто3,
| ХозрасчетныйОстаткиИОбороты.Субконто1";
// Модифицируем текст запроса
ТекстЗапроса = СтрЗаменить(ТекстШаблона, "%УсловиеОтбора%", СтрокаУсловий);
// Инициализируем запрос
Запрос = Новый Запрос(ТекстЗапроса);
Запрос.УстановитьПараметр("Счет4101", ПланыСчетов.Хозрасчетный.ТоварыНаСкладах);
// Программно устанавливаем только те параметры, которые реально используются
Для Каждого КлючИЗначение Из ПараметрыЗапроса Цикл
Запрос.УстановитьПараметр(КлючИЗначение.Ключ, КлючИЗначение.Значение);
КонецЦикла;
Выборка = Запрос.Выполнить().Выбрать();
Этот код генерирует абсолютно «чистый» SQL-код без логического мусора. Если пользователь выберет только Склад, СУБД выполнит моментальный поиск по индексу склада, не отвлекаясь на проверку пустых ссылок номенклатуры.
Обратим внимание на важную архитектурную проблему, которая присутствует во всех предыдущих примерах. Мы жестко прописали обращение к Субконто1 и Субконто3. В реальной работе это крайне опасно.
В чем опасность: Порядок видов субконто (аналитики) на счетах учета задается пользователями или разработчиками в режиме конфигуратора/предприятия. Сегодня на счете 41.01 первым субконто идет Номенклатура, а третьим — Склад. Завтра бухгалтер решит поменять их местами или добавить новое субконто в начало списка. В этот же момент ваш ручной отчет либо перестанет работать вовсе, либо начнет выводить некорректные данные (например, пытаться записать ссылку Склада в поле Номенклатуры).
Чтобы избежать этой проблемы, мы должны явно передать массив видов субконто в параметры виртуальной таблицы запроса. В этом случае платформа сама переназначит их индексы в результирующей выборке строго в том порядке, в котором мы их передали.
Посмотрим на пример реализации такого подхода:
// Формируем массив видов субконто в строго определенном нами порядке
МассивВидовСубконто = Новый Массив;
МассивВидовСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Номенклатура); // Будет Субконто1
МассивВидовСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Склады); // Будет Субконто2
Запрос = Новый Запрос;
// Теперь в тексте запроса мы можем быть на 100% уверены в номерах субконто!
Запрос.Текст =
"ВЫБРАТЬ
| ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Номенклатура,
| ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Склад
|ИЗ
| РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(,,, Счет = &Счет, &ВидыСубконто, %Условие%) КАК ХозрасчетныйОстаткиИОбороты";
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.ТоварыНаСкладах);
Запрос.УстановитьПараметр("ВидыСубконто", МассивВидовСубконто);
Передавая параметр &ВидыСубконто на пятую позицию виртуальной таблицы, мы гарантируем стабильность отчета. Вне зависимости от настроек порядка субконто на самом счете в плане счетов, в запросе Номенклатура всегда будет доступна через Субконто1, а Склад — через Субконто2.
Если мы хотим разрабатывать производительные отчеты с динамическими отборами, но при этом не желаем заниматься ручной сборкой строк и склейкой условий, отличным решением станет использование встроенного класса ПостроительЗапроса. Это мощный инструмент (предшественник СКД), который до сих пор активно применяется для динамического формирования текстов запросов.
При использовании построителя мы описываем параметры отборов в тексте запроса в специальных фигурных скобках {}. Посмотрим, как это выглядит на практике:
Построитель = Новый ПостроительЗапроса;
Построитель.Текст =
"ВЫБРАТЬ
| ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Номенклатура,
| ХозрасчетныйОстаткиИОбороты.Субконто2 КАК Склад,
| ХозрасчетныйОстаткиИОбороты.КоличествоКонечныйОстаток КАК Остаток
|ИЗ
| РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(,,, Счет = &Счет, &ВидыСубконто, ) КАК ХозрасчетныйОстаткиИОбороты
|{ГДЕ
| ХозрасчетныйОстаткиИОбороты.Субконто1.* КАК Номенклатура,
| ХозрасчетныйОстаткиИОбороты.Субконто2.* КАК Склад}";
// Задаем базовые параметры
Построитель.Параметры.Вставить("Счет", ПланыСчетов.Хозрасчетный.ТоварыНаСкладах);
Построитель.Параметры.Вставить("ВидыСубконто", МассивВидовСубконто); // Предварительно заполненный массив
// Построитель автоматически создаст доступные отборы на основе секции {ГДЕ...}
// Мы можем управлять ими программно в зависимости от того, что ввел пользователь на форме:
Если ЗначениеЗаполнено(Склад) Тогда
ОтборСклад = Построитель.Отбор.Добавить("Склад");
ОтборСклад.Значение = Склад;
ОтборСклад.Использование = ИСТИНА;
КонецЕсли;
Если ЗначениеЗаполнено(Номенклатура) Тогда
ОтборНоменклатура = Построитель.Отбор.Добавить("Номенклатура");
ОтборНоменклатура.Значение = Номенклатура;
ОтборНоменклатура.Использование = ИСТИНА;
КонецЕсли;
// Построитель сам сформирует оптимальный SQL-текст запроса, добавив только активные отборы!
Построитель.Выполнить();
Выборка = Построитель.Результат.Выбрать();
Использование ПостроительЗапроса избавляет разработчика от написания рутинного кода по анализу заполненности значений и модификации строк, беря всю оптимизацию построения SQL-кода на себя.
В заключение разберем продвинутый сценарий. Что делать, если вам очень нравится, как СКД автоматически формирует быстрые запросы с любыми сложными отборами, но вы категорически не хотите выводить отчет стандартными средствами компоновки (например, вам нужно записать результат в кастомную таблицу значений или передать данные во внешнюю систему)?
Мы можем использовать СКД исключительно как программный «движок» генерации и получения данных. Разберем этот алгоритм:
// Получаем схему из макета нашего отчета
СхемаКомпоновкиДанных = ПолучитьМакет("ОсновнаяСхемаКомпоновкиДанных");
// Инициализируем настройки
Настройки = СхемаКомпоновкиДанных.НастройкиПоУмолчанию;
// Программно устанавливаем отборы в СКД
Если ЗначениеЗаполнено(Склад) Тогда
ЭлементОтбора = Настройки.Отбор.Элементы.Добавить(Тип("ЭлементОтбораКомпоновкиДанных"));
ЭлементОтбора.ЛевоеЗначение = Новый ПолеКомпоновкиДанных("Склад");
ЭлементОтбора.ВидСравнения = ВидСравненияКомпоновкиДанных.Равно;
ЭлементОтбора.ПравоеЗначение = Склад;
ЭлементОтбора.Использование = ИСТИНА;
КонецЕсли;
// Компонуем макет с помощью процессора компоновки
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки,,, Тип("ГенераторМакетаКомпоновкиДанныхДляКоллекцииЗначений"));
// Запускаем процессор выполнения
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки);
// Выгружаем результат в обычную Таблицу Значений
ТаблицаРезультата = Новый ТаблицаЗначений;
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВКоллекциюЗначений;
ПроцессорВывода.УстановитьОбъект(ТаблицаРезультата);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
// Теперь у нас есть чистая ТаблицаЗначений со всеми примененными быстрыми отборами!
Используя этот метод, мы полностью перекладываем задачу динамического построения запросов на мощное ядро платформы 1С, сохраняя при этом абсолютную свободу в выводе и обработке данных на встроенном языке.