При разработке сложных отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с необходимостью интеграции локальных данных информационной базы с внешними источниками. Это могут быть данные о расходе топлива из систем мониторинга, пробег транспортных средств из сторонних баз или данные о посещениях из CRM-систем. Типичный сценарий — связь локального документа (например, Маршрутное задание) с таблицей внешнего источника (например, пройденный путь из путевых листов другой базы) по полям Водитель и Дата.
Проблема заключается в том, что при использовании стандартной вкладки «Связи наборов данных» в СКД записи из внешнего источника могут бесследно исчезать, если за этот день в текущей базе не было создано маршрутного листа. В этой статье мы подробно разберем причины такого поведения платформы 1С и изучим три надежных способа обхода этой проблемы, которые позволят вывести полную информацию без потери строк.
Давайте проанализируем внутренние механизмы СКД. Когда мы связываем два набора данных (например, НаборДанных1 — локальный запрос и НаборДанных2 — внешние данные) через вкладку «Связи наборов данных», платформа всегда выполняет операцию левого соединения (LEFT JOIN). В этой связке основным (левым) всегда выступает родительский набор данных, а подчиненным (правым) — связываемый набор.
Если на определенную дату (например, 27.05.2019) у водителя не было маршрутного листа в нашей базе, то в левой таблице строка для этой даты отсутствует. Так как соединение левое, СКД даже не попытается запросить или отобразить данные из правой таблицы за этот день, несмотря на то, что во внешней базе за 27.05.2019 зафиксирован реальный пробег. В итоге отчет показывает некорректные итоговые данные, занижая общий пробег водителя за период.
Для решения этой классической задачи нам необходимо реализовать аналог полного соединения (FULL JOIN). Рассмотрим три практических способа реализации этой логики в 1С.
Это наиболее простой, изящный и рекомендуемый способ в СКД, который не требует написания сложного программного кода и полностью реализуется в конструкторе схемы компоновки данных. Вместо связывания наборов данных мы объединим их в один общий виртуальный набор.
Разберем этот процесс по шагам:
ОбъединенныеДанные.Водитель и Дата), в обоих наборах данных мы обязаны указать абсолютно одинаковые имена в колонке «Путь к данным». Например, для водителя укажем путь Водитель, для даты — ПериодДень.ПройденноеРасстояние из внешней базы и МаршрутныйЛист из локальной), оставляем со своими собственными уникальными путями к данным.Как это работает при выводе отчета? СКД автоматически выполнит объединение строк. При выводе группировок по полям Водитель и ПериодДень платформа сгруппирует записи. Если на 27.05.2019 локального документа не было, поле МаршрутныйЛист останется пустым (будет содержать значение Null), но строка с датой выведется, и в колонке ПройденноеРасстояние отобразится корректное значение из внешнего источника. Данные больше не будут пропадать!
Бывают ситуации, когда объединение наборов данных не подходит из-за специфических требований к компоновке (например, при сложной иерархии группировок или жестких требованиях к структуре запроса). В таких случаях мы можем применить архитектурный паттерн с использованием «опорного» набора данных, который будет содержать непрерывную сетку всех возможных комбинаций ключевых полей.
Рассмотрим алгоритм реализации этого метода:
Водитель и Дата.Поскольку теперь оба набора соединяются левым соединением к полной опорной таблице, в которой гарантированно есть абсолютно все дни периода для каждого водителя, ни одна запись о пробеге или маршрутном листе не будет отброшена платформой. Пустые значения в неопределенные дни корректно отобразятся как пустые поля, сохранив целостность итоговых сумм.
Если внешние данные мы получаем динамически с помощью программных механизмов (например, через COM-соединение, ADO-подключение к внешней СУБД или интеграционные Web/REST-сервисы), выполнение связей на уровне встроенных механизмов СКД может привести к катастрофическому падению производительности. Дело в том, что СКД при связи наборов будет делать запросы к внешнему источнику в цикле для каждой строки основного набора.
Чтобы избежать этой проблемы, правильнее всего перенести логику объединения данных непосредственно в код модуля объекта отчета, переопределив обработчик события ПриКомпоновкеРезультата. Мы программно сформируем две таблицы значений, поместим их во временные таблицы и свяжем полноценным запросом с оператором ПОЛНОЕ СОЕДИНЕНИЕ.
Проанализируем пример кода, реализующий этот подход:
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
// Отменяем стандартный вывод отчета
СтандартнаяОбработка = Ложь;
// 1. Извлекаем локальные данные с приведением даты к началу дня
ЗапросЛокальный = Новый Запрос;
ЗапросЛокальный.Текст =
"ВЫБРАТЬ
| НАЧАЛОПЕРИОДА(МаршрутноеЗадание.Дата, ДЕНЬ) КАК ПериодДень,
| МаршрутноеЗадание.Водитель КАК Водитель,
| МаршрутноеЗадание.Ссылка КАК МаршрутныйЛист,
| МаршрутноеЗадание.СуммаДокумента КАК Сумма
|ИЗ
| Документ.МаршрутноеЗадание КАК МаршрутноеЗадание
|ГДЕ
| МаршрутноеЗадание.Проведен = ИСТИНА";
ТаблицаЛокальная = ЗапросЛокальный.Выполнить().Выгрузить();
// 2. Получаем внешние данные (в реальном проекте здесь будет обращение к внешней базе)
// Для примера создадим структуру таблицы внешних данных программно
ТаблицаВнешняя = Новый ТаблицаЗначений;
ТаблицаВнешняя.Колонки.Добавить("ПериодДень", Новый ОписаниеТипов("Дата"));
TaблицаВнешняя.Колонки.Добавить("Водитель", Новый ОписаниеТипов("СправочникСсылка.ФизическиеЛица"));
ТаблицаВнешняя.Колонки.Добавить("ПройденноеРасстояние", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
// (Здесь должен быть код заполнения ТаблицыВнешней данными из внешней ИБ)
// 3. Объединяем две таблицы в один результирующий набор средствами SQL-движка 1С
ЗапросОбъединения = Новый Запрос;
ЗапросОбъединения.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Помещаем локальную таблицу во временную
ЗапросОбъединения.Текст =
"ВЫБРАТЬ * ПОМЕСТИТЬ ВТ_Локальная ИЗ &ТаблицаЛокальная КАК ТаблицаЛокальная";
ЗапросОбъединения.УстановитьПараметр("ТаблицаЛокальная", ТаблицаЛокальная);
ЗапросОбъединения.Выполнить();
// Помещаем внешнюю таблицу во временную
ЗапросОбъединения.Текст =
"ВЫБРАТЬ * ПОМЕСТИТЬ ВТ_Внешняя ИЗ &ТаблицаВнешняя КАК ТаблицаВнешняя";
ЗапросОбъединения.УстановитьПараметр("ТаблицаВнешняя", ТаблицаВнешняя);
ЗапросОбъединения.Выполнить();
// Выполняем ПОЛНОЕ СОЕДИНЕНИЕ и корректно обрабатываем поля связи через ЕСТЬNULL
ЗапросОбъединения.Текст =
"ВЫБРАТЬ
| ЕСТЬNULL(ВТ_Локальная.ПериодДень, ВТ_Внешняя.ПериодДень) КАК ПериодДень,
| ЕСТЬNULL(ВТ_Локальная.Водитель, ВТ_Внешняя.Водитель) КАК Водитель,
| ВТ_Локальная.МаршрутныйЛист КАК МаршрутныйЛист,
| ВТ_Локальная.Сумма КАК Сумма,
| ЕСТЬNULL(ВТ_Внешняя.ПройденноеРасстояние, 0) КАК ПройденноеРасстояние
|ИЗ
| ВТ_Локальная КАК ВТ_Локальная
| ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Внешняя КАК ВТ_Внешняя
| ПО ВТ_Локальная.ПериодДень = ВТ_Внешняя.ПериодДень
| И ВТ_Локальная.Водитель = ВТ_Внешняя.Водитель";
ТаблицаИтоговая = ЗапросОбъединения.Выполнить().Выгрузить();
// 4. Передаем готовую таблицу значений в СКД как Набор Данных - Объект
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ИтоговаяТаблица", ТаблицаИтоговая);
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.ПолучитьНастройки(), ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки, ИСТИНА);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
КонецПроцедуры
Обратите внимание на конструкцию ЕСТЬNULL в итоговом запросе. Она критически важна! Если мы выберем просто ВТ_Локальная.Водитель, то при отсутствии маршрутного листа в конкретный день поле водителя в отчете окажется незаполненным, хотя данные о пробеге выведутся. Функция ЕСТЬNULL гарантирует, что ключевое поле заполнится значением из той таблицы, в которой эта запись реально существует.
Обсуждая тему связи данных по дате, нельзя не упомянуть важнейший нюанс, о котором шла речь во втором и третьем сообщениях форума. Это точность сопоставления дат.
В системе 1С тип данных Дата содержит в себе информацию о времени с точностью до секунды. Если документ в локальной базе записан временем 27.05.2019 14:35:12, а запись во внешней базе данных сохранена с временем 27.05.2019 00:00:00 (или любым другим), прямое сравнение этих дат вернет ложь (они не равны). Соединение по таким полям не сработает, и данные не свяжутся.
Поэтому при использовании любого из вышеописанных методов (будь то связь наборов в СКД, объединение наборов или запрос на языке 1С) необходимо предварительно нормализовать поле даты в обоих источниках, приведя его к началу дня. В запросах это делается с помощью функции НАЧАЛОПЕРИОДА(ИмяПоляДаты, ДЕНЬ). Только при совпадении дат с точностью до дня вы гарантированно получите корректно сопоставленные строки в результирующем отчете.
Подводя итог, выбор конкретного метода зависит от архитектуры вашего решения. Для простых отчетов без написания кода идеально подойдет Способ 1 с объединением наборов данных. Если требуется жестко выводить календарные периоды независимо от наличия данных — выбирайте Способ 2. А для сложных интеграций с внешними базами данных по COM или API лучшим выбором с точки зрения оптимизации скорости работы станет Способ 3.