Как использовать произвольный SQL-запрос к внешней базе данных в качестве источника для СКД?

В практике разработки на платформе 1С часто возникают задачи, когда стандартных механизмов интеграции, таких как "Внешние источники данных", становится недостаточно. Типичный пример — необходимость использовать специфические функции конкретной СУБД, например, полнотекстовый поиск (CONTAINS, FREETEXT), который 1С не всегда может эффективно транслировать через свои внутренние механизмы. В таких случаях оптимальным решением становится связка прямого SQL-запроса и Системы компоновки данных (СКД) для вывода результата пользователю.

Рассмотрим подробно, как реализовать механизм, который позволит динамически формировать текст SQL-запроса, получать данные и передавать их в СКД для дальнейшей фильтрации и оформления.

Метод 1: Использование «Набора данных — объект»

Это самый распространенный способ, когда структура данных заранее известна или может быть описана. Основная идея заключается в том, что мы сначала получаем данные из внешней СУБД (через ADO или COM-соединение) в обычную ТаблицаЗначений, а затем передаем эту таблицу в СКД.

Проанализируем алгоритм действий:

1. Создадим схему компоновки данных и добавим в неё Набор данных - объект.
2. В поле "Имя объекта содержащего данные" укажем, например, ВнешняяТаблицаДанных.
3. Вручную или программно опишем поля, которые мы ожидаем получить из SQL-запроса.

Разберем программный код для вызова такой схемы:

// 1. Получаем данные из внешней базы через ADO
ТекстЗапросаSQL = "SELECT Id, Name, Description FROM MyExternalTable WHERE CONTAINS(Description, 'Условие')";
ТаблицаРезультат = ПолучитьТаблицуИзSQL(ТекстЗапросаSQL); // Функция, возвращающая ТЗ

// 2. Подготавливаем СКД
СхемаКомпоновки = Отчеты.МойОтчет.ПолучитьМакет("ОсновнаяСхемаКомпоновкиДанных");
Настройки = СхемаКомпоновки.НастройкиПоУмолчанию;

// 3. Передаем таблицу во внешний набор данных
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ВнешняяТаблицаДанных", ТаблицаРезультат);

ДанныеРасшифровки = Новый ДанныеРасшифровкиКомпоновкиДанных;
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновки, Настройки, ДанныеРасшифровки);

ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки);

// 4. Выводим результат в табличный документ
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(Элементы.Результат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);

Метод 2: Программная генерация схемы СКД (Динамические колонки)

Если структура SQL-запроса заранее неизвестна (пользователь сам выбирает таблицы или колонки), нам необходимо программно сформировать объект СхемаКомпоновкиДанных. Выясним, как это сделать по шагам.

Для начала создадим пустую схему и добавим в нее набор данных:

Схема = Новый СхемаКомпоновкиДанных;
ИсточникДанных = Схема.ИсточникиДанных.Добавить();
ИсточникДанных.Имя = "Источник1";
ИсточникДанных.ТипИсточникаДанных = "Local";

НаборДанных = Схема.НаборыДанных.Добавить(Тип("НаборДанныхОбъектСхемыКомпоновкиДанных"));
НаборДанных.Имя = "ОсновнойНабор";
НаборДанных.ИмяОбъекта = "ТЗ_Данные";

Теперь проанализируем колонки полученной таблицы значений и добавим их в поля СКД. Это критически важно для того, чтобы пользователь мог накладывать отборы в интерфейсе:

Для Каждого Колонка Из ТаблицаРезультат.Колонки Цикл
    ПолеСКД = НаборДанных.Поля.Добавить(Тип("ПолеНабораДанныхСхемыКомпоновкиДанных"));
    ПолеСКД.Поле = Колонка.Имя;
    ПолеСКД.ПутьКДанным = Колонка.Имя;
    ПолеСКД.Заголовок = Колонка.Имя;
    ПолеСКД.ТипЗначения = Колонка.ТипЗначения;
КонецЦикла;

После этого не забудьте инициализировать настройки компоновщика, чтобы поля стали доступны для выбора:

КомпоновщикНастроек.Инициализировать(Новый ИсточникДоступныхНастроекКомпоновкиДанных(Схема));

Оптимизация производительности: Трансляция отборов

Посмотрим на ситуацию с точки зрения производительности. Если мы сначала выгрузим из SQL-базы миллион строк в ТаблицаЗначений, а потом СКД будет их фильтровать — система может зависнуть из-за нехватки оперативной памяти.

Правильный подход: проанализировать настройки отбора, которые пользователь установил в КомпоновщикНастроек, и динамически дописать их в секцию WHERE вашего SQL-запроса.

1. Обойдем коллекцию КомпоновщикНастроек.Настройки.Отбор.Элементы.
2. Для каждого элемента сформируем строковое представление условия (например, "AND Name LIKE '%" + Значение + "%'").
3. Вставим полученную строку в текст SQL-запроса перед его выполнением.

Работа с полнотекстовым поиском и большими строками

Проанализируем особенности работы с полями типа LOB (Large Objects) или длинными строками (NVARCHAR(MAX)). При получении таких данных через ADO следует учитывать:

• Обрезание данных: В некоторых драйверах ODBC/OLEDB длинные строки могут обрезаться до 255 или 1024 символов. Проверяйте настройки провайдера данных.
• Полнотекстовые индексы: Используйте специализированные операторы СУБД. Например, если пользователь ввел поисковую фразу в поле отбора СКД, вы можете подменить стандартное сравнение на SQL-конструкцию: WHERE CONTAINS(BigTextColumn, 'СтрокаПоиска').
• Группировки: В СКД для полей с неограниченной длиной рекомендуется отключать возможность группировки, так как СУБД (и сама 1С при обработке ТЗ) может тратить значительные ресурсы на сравнение таких строк.

Вывод в Excel и отправка по почте

Поскольку задача предполагает сохранение результата на диск, после формирования ТабличныйДокумент с помощью ПроцессорВывода, мы можем легко сохранить его:

ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);

Затем используем стандартный объект ИнтернетПочтовыйПрофиль и ИнтернетПочтовоеСообщение для отправки сформированного файла пользователю. Таким образом, мы получаем гибкий инструмент, который объединяет мощь SQL-запросов и удобство визуализации СКД.