При интеграции 1С с внешними системами управления задачами (например, Битрикс24) разработчики часто сталкиваются с необходимостью выгрузки иерархических структур в табличные части документов. В Битрикс24 задачи могут иметь неограниченную вложенность (подзадачи), в то время как в печатной форме документа (например, в «Заказе покупателя» или «Акте выполненных работ») клиенту чаще всего нужно показать только итоговую «родительскую» задачу, в которую уже заложена общая стоимость и общее количество затраченных часов всех ее подзадач.
В этой статье мы подробно разберем, как правильно реализовать такое объединение на уровне запроса 1С, проанализируем типичные ошибки, из-за которых двоятся или троятся суммы, и рассмотрим альтернативный программный способ решения через дерево значений.
Казалось бы, решение лежит на поверхности — нужно просто соединить табличную часть документа саму с собой по условию ID = IDРодителя. Однако здесь неопытных разработчиков подстерегает классическая логическая ловушка СУБД — декартово произведение строк.
Давайте разберем на конкретном примере, почему простая группировка и соединение «в лоб» приводят к неверным расчетам сумм и времени.
Предположим, у нас есть основная задача и две подзадачи к ней:
Если мы напишем простой запрос с левым соединением таблицы самой к себе по условию Заказ.ID = СоединенныйЗаказ.IDРодителя, СУБД вернет нам две строки для одной и той же родительской задачи:
Когда вы попытаетесь сложить эти данные функцией СУММА() в группировке по родительской задаче, СУБД честно просуммирует значения во всех получившихся строках: (5 + 2) + (5 + 3) = 15 часов. В итоге время родительской задачи продублировалось столько раз, сколько у нее было подзадач! Вместо корректных 10 часов (5 родительских + 2 + 3 подчиненных) мы получили завышенные показатели. Именно с этой проблемой столкнулся автор темы на форуме, когда вместо 6 часов у него выводилось 8.
Вторая частая ошибка — некорректная обработка пустых родительских связей. В СУБД пустые поля могут принимать значения NULL, но в реальных табличных частях документов 1С числовые поля по умолчанию инициализируются нулем (0), а строковые — пустой строкой ("").
Если при отборе корневых (родительских) задач вы напишете условие ГДЕ IDРодителя IS NULL, запрос может ничего не вернуть, так как в базе данных вместо физического NULL хранится пустая строка или ноль. Проверку на незаполненного родителя всегда нужно делать с учетом типа данных этого поля.
Рассмотрим правильный алгоритм решения этой задачи с помощью пакета запросов и временных таблиц.
Чтобы избежать дублирования родительских сумм, мы должны применить простое правило: сначала агрегируем (сворачиваем) подзадачи отдельно, и только потом соединяем полученные итоги с родительской задачей. Таким образом, к каждой родительской задаче присоединится ровно одна строка с уже готовыми суммами всех ее подчиненных задач.
Давайте спроектируем правильный запрос 1С по шагам:
// Шаг 1: Собираем и суммируем показатели только по подзадачам во временную таблицу.
// Корневые задачи на этом этапе мы полностью игнорируем.
ВЫБРАТЬ
ЗаказПокупателяРасшифровка.IDРодителя КАК IDРодителя,
СУММА(ЗаказПокупателяРасшифровка.Цена) КАК СуммаПодзадач,
СУММА(ЗаказПокупателяРасшифровка.КоличествоЧасов) КАК КоличествоЧасовПодзадач
ПОМЕСТИТЬ ВТ_СвернутыеПодзадачи
ИЗ
Документ.ЗаказПокупателя.Расшифровка КАК ЗаказПокупателяРасшифровка
ГДЕ
ЗаказПокупателяРасшифровка.Ссылка = &ТекущийДокумент
// Отсекаем корневые задачи.
// Если IDРодителя числовой - пишем: <> 0
// Если строковый UID - пишем: <> ""
И ЗаказПокупателяРасшифровка.IDРодителя <> ""
СГРУППИРОВАТЬ ПО
ЗаказПокупателяРасшифровка.IDРодителя
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выбираем основные (корневые) задачи и левым соединением добавляем
// к ним предварительно рассчитанные суммы их подчиненных ветвей.
ВЫБРАТЬ
ЗаказПокупателяРасшифровка.НомерСтроки КАК НомерСтрокиТЧ,
ЗаказПокупателяРасшифровка.Задание КАК Задание,
ЗаказПокупателяРасшифровка.Исполнитель КАК Исполнитель,
// Складываем показатели родительской задачи и агрегированные показатели подзадач.
// Обязательно используем функцию ЕСТЬNULL на случай, если у задачи нет подзадач.
ЗаказПокупателяРасшифровка.Цена + ЕСТЬNULL(ВТ_СвернутыеПодзадачи.СуммаПодзадач, 0) КАК Цена,
ЗаказПокупателяРасшифровка.КоличествоЧасов + ЕСТЬNULL(ВТ_СвернутыеПодзадачи.КоличествоЧасовПодзадач, 0) КАК КоличествоЧасов
ИЗ
Документ.ЗаказПокупателя.Расшифровка КАК ЗаказПокупателяРасшифровка
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СвернутыеПодзадачи КАК ВТ_СвернутыеПодзадачи
ПО ЗаказПокупателяРасшифровка.ID = ВТ_СвернутыеПодзадачи.IDРодителя
ГДЕ
ЗаказПокупателяРасшифровка.Ссылка = &ТекущийДокумент
// Выбираем только родительские задачи
И ЗаказПокупателяРасшифровка.IDРодителя = ""
УПОРЯДОЧИТЬ ПО
НомерСтрокиТЧ
Этот подход гарантирует абсолютно точный расчет итогов, исключает дублирование и работает максимально быстро, так как СУБД использует индексы табличной части документа.
Что делать, если иерархия задач из Битрикс24 может иметь более двух уровней (например, задача -> подзадача -> подподзадача)? Попытка решить такую задачу одним или даже несколькими запросами приведет к громоздкому коду, который сложно поддерживать, так как язык запросов 1С не поддерживает рекурсивные соединения.
В этом случае эффективнее выгрузить данные в оперативную память, построить из них ДеревоЗначений, программно рассчитать итоги снизу вверх и вывести на печать только корневые строки.
Рассмотрим алгоритм реализации этого способа:
ТаблицаЗначений.ДеревоЗначений с аналогичными колонками.Строки их родительских строк.Проанализируем пример рекурсивной функции для расчета итогов в дереве значений:
Процедура РассчитатьИтогиДерева(ТекущаяСтрокаДерева)
// Если у строки есть подчиненные строки (подзадачи)
Если ТекущаяСтрокаДерева.Строки.Количество() > 0 Тогда
// Сначала рекурсивно рассчитываем итоги для всех подчиненных строк
Для Каждого ПодчиненнаяСтрока Из ТекущаяСтрокаДерева.Строки Цикл
РассчитатьИтогиДерева(ПодчиненнаяСтрока);
КонецЦикла;
// Теперь, когда подчиненные строки гарантированно рассчитаны,
// собираем их показатели и прибавляем к текущему родителю
СуммаПодзадач = ТекущаяСтрокаДерева.Строки.Итог("Цена");
ЧасыПодзадач = ТекущаяСтрокаДерева.Строки.Итог("КоличествоЧасов");
ТекущаяСтрокаДерева.Цена = ТекущаяСтрокаДерева.Цена + СуммаПодзадач;
ТекущаяСтрокаДерева.КоличествоЧасов = ТекущаяСтрокаДерева.КоличествоЧасов + ЧасыПодзадач;
КонецЕсли;
КонецПроцедуры
Чтобы запустить этот расчет по всему дереву, вам достаточно будет в цикле обойти только строки верхнего уровня созданного дерева:
Для Каждого СтрокаВерхнегоУровня Из ДеревоЗадач.Строки Цикл
РассчитатьИтогиДерева(СтрокаВерхнегоУровня);
КонецЦикла;
После выполнения этой процедуры ваше дерево будет полностью подготовлено к выводу на печать. При формировании табличного документа вам останется лишь обойти коллекцию ДеревоЗадач.Строки (только первый уровень) и вывести их параметры в макет. Все расчеты по вложенным подзадачам любой глубины уже будут корректно просуммированы внутри родительских строк.
При выборе метода интеграции и подготовки печатных форм руководствуйтесь простым правилом: если вы точно знаете, что уровень вложенности задач не превышает одного (есть только родитель и его прямые подзадачи), смело используйте вариант с пакетом запросов и временными таблицами. Он работает быстрее всего и не нагружает сервер приложений 1С.
Если же интеграция с Битрикс24 предполагает сложную структуру проектов с глубокой вложенностью подзадач, используйте программный обход через рекурсию в Дереве Значений. Это убережет вас от построения громоздких и нечитаемых SQL-запросов и обеспечит стабильную работу системы при любых изменениях в структуре задач на стороне Битрикс24.