При разработке отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда необходимо сопоставить данные из двух табличных частей разных документов. Типичный пример: Заказ клиента и Заказ поставщика. Если в одном документе товар указан одной строкой на 15 единиц, а в другом — тремя строками по 5 единиц, обычное левое соединение в запросе по номенклатуре приведет к дублированию строк. СКД не понимает логику распределения количеств и просто перемножает записи. Разберем, как реализовать алгоритм ФИФО (First In, First Out), чтобы строки «схлопывались» корректно.
Проанализируем ситуацию: у нас есть ЗаказКлиента (ТЧ1) и ЗаказПоставщика (ТЧ2). В ТЧ1 товар «Монитор» идет тремя строками по 10, 5 и 15 штук. В ТЧ2 тот же товар идет двумя строками по 15 и 15 штук. Общее количество совпадает (30 штук), но при связи в СКД по полю Номенклатура система выдаст 6 строк (каждая строка из ТЧ1 соединится с каждой строкой из ТЧ2). Наша задача — заставить отчет понять, что первая поставка на 15 штук должна закрыть первую строку клиента (10) и часть второй (5), а вторая поставка должна закрыть остаток.
Рассмотрим подробнее три основных способа решения этой задачи.
Это наиболее надежный и гибкий метод. Мы не пытаемся решить задачу сложным SQL-запросом, а подготавливаем данные в коде на языке 1С и передаем готовую таблицу в СКД. Выясним, как это реализовать по шагам.
ИтоговаяТаблица и опишем в нем все необходимые поля (Номенклатура, КоличествоКлиента, КоличествоПоставщика, СрокПоставки и т.д.).ПриКомпоновкеРезультата.ТаблицаЗначений.Посмотрим на пример кода, который выполняет такое распределение:
// Пример алгоритма распределения ФИФО в Таблице Значений
Процедура ПриКомпоновкеРезультата(ДанныеРасшифровки, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь;
// 1. Получаем таблицы ТЧ1 (Заказы) и ТЧ2 (Поставки)
ТаблицаЗаказов = ПолучитьДанныеЗаказов(); // Ваш запрос здесь
ТаблицаПоставок = ПолучитьДанныеПоставок(); // Ваш запрос здесь
// 2. Создаем итоговую таблицу для СКД
ИтоговаяТЗ = Новый ТаблицаЗначений;
ИтоговаяТЗ.Колонки.Добавить("Номенклатура");
ИтоговаяТЗ.Колонки.Добавить("Количество");
ИтоговаяТЗ.Колонки.Добавить("ЗаказПоставщика");
// 3. Распределяем по ФИФО
Для каждого СтрЗаказа Из ТаблицаЗаказов Цикл
ОстатокКРаспределению = СтрЗаказа.Количество;
Для каждого СтрПоставки Из ТаблицаПоставок Цикл
Если СтрПоставки.Номенклатура <> СтрЗаказа.Номенклатура Или СтрПоставки.Остаток = 0 Тогда
Продолжить;
КонецЕсли;
ВзятоИзПоставки = Мин(ОстатокКРаспределению, СтрПоставки.Остаток);
НоваяСтр = ИтоговаяТЗ.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтр, СтрЗаказа);
НоваяСтр.Количество = ВзятоИзПоставки;
НоваяСтр.ЗаказПоставщика = СтрПоставки.ЗаказПоставщика;
СтрПоставки.Остаток = СтрПоставки.Остаток - ВзятоИзПоставки;
ОстатокКРаспределению = ОстатокКРаспределению - ВзятоИзПоставки;
Если ОстатокКРаспределению = 0 Тогда
Прервать;
КонецЕсли;
КонецЦикла;
КонецЦикла;
// 4. Передаем таблицу в СКД
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ИтоговаяТаблица", ИтоговаяТЗ);
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.ПолучитьНастройки(), ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(Результат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
КонецПроцедуры
Этот подход гарантирует отсутствие дублей, так как мы сами контролируем создание каждой строки отчета. Однако, как отмечалось на форуме, при очень больших объемах данных (десятки тысяч строк) этот процесс может занять время, так как обработка идет в оперативной памяти.
Если стоит задача решить все исключительно средствами запроса (без программирования в модулях), можно применить метод нарастающих итогов. Разберем его по шагам, так как он считается «высшим пилотажем» в работе с запросами 1С.
Суть метода заключается в том, что каждая строка товара представляется как отрезок на числовой оси. Например, первая строка «Заказа» — это отрезок [0, 10], вторая — [10, 15], третья — [15, 30]. Аналогично для «Поставок»: первая — [0, 15], вторая — [15, 30].
Алгоритм реализации в запросе:
Т1.НомерСтроки >= Т2.НомерСтроки. В новых версиях (платформа 8.3.21+) можно использовать оконную функцию СУММА(Количество) ОБЪЕДИНИТЬ ПО....ГраницаОт (предыдущий итог) и ГраницаДо (текущий итог).ТЧ1.ГраницаОт < ТЧ2.ГраницаДо И ТЧ1.ГраницаДо > ТЧ2.ГраницаОт.Минимум(ТЧ1.ГраницаДо, ТЧ2.ГраницаДо) - Максимум(ТЧ1.ГраницаОт, ТЧ2.ГраницаОт).Проанализируем пример части такого запроса:
ВЫБРАТЬ
Т1.Номенклатура,
Т1.ГраницаОт КАК ГраницаОт_Клиент,
Т1.ГраницаДо КАК ГраницаДо_Клиент,
Т2.ЗаказПоставщика,
Т2.ГраницаОт КАК ГраницаОт_Пост,
Т2.ГраницаДо КАК ГраницаДо_Пост,
// Вычисляем пересечение интервалов
ВЫБОР
КОГДА Т1.ГраницаДо < Т2.ГраницаДо ТОГДА Т1.ГраницаДо
ИНАЧЕ Т2.ГраницаДо
КОНЕЦ -
ВЫБОР
КОГДА Т1.ГраницаОт > Т2.ГраницаОт ТОГДА Т1.ГраницаОт
ИНАЧЕ Т2.ГраницаОт
КОНЕЦ КАК КоличествоРаспределено
ИЗ
ВТ_ЗаказыКлиентов КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ЗаказыПоставщиков КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.ГраницаОт < Т2.ГраницаДо
И Т1.ГраницаДо > Т2.ГраницаОт
Преимущество: Все вычисления происходят на стороне СУБД, что потенциально быстрее для СКД и позволяет использовать стандартные механизмы группировок и отборов без потери производительности на передачу ТЗ.
В некоторых случаях, как советуют участники форума, проблему можно решить на уровне архитектуры данных. Если распределение ФИФО критически важно для бизнеса, возможно, его стоит фиксировать в момент проведения документов в специальном регистре сведений или накопления. В этом случае отчет СКД будет просто выводить готовую «связку» из регистра без сложных вычислений.
Если же архитектуру менять нельзя, можно в запросе СКД добавить искусственный ключ связи. Для каждой строки товара в ТЧ1 и ТЧ2 программно или запросом генерируется номер в очереди внутри одной номенклатуры. Однако это все равно сводится к вычислению нарастающего итога или порядкового номера, что возвращает нас к первому или второму способу.
Независимо от выбранного способа, при работе с распределенными данными в СКД обратите внимание на следующие моменты:
ВычислитьВыражение, которая позволяет считать итоги по группировкам. Она может помочь визуально отобразить остатки ФИФО, но она не поможет «раздробить» одну строку на две, если поставка покрывает два заказа. Дробление строк возможно только в источнике данных (запрос или ТЗ).Т1.Номер >= Т2.Номер) имеет квадратичную сложность. На таблицах в 10 000 строк это даст 100 000 000 операций сравнения. В таких случаях всегда используйте программное формирование ТЗ в ПриКомпоновкеРезультата или новые оконные функции платформы.Подводя итог, отметим: для разовых отчетов или сложных условий сопоставления (когда нужно учитывать еще и склады, серии, сроки годности) лучше всего подходит программный метод с внешним набором данных. Он прозрачен для отладки и позволяет реализовать любую логику распределения, которую сложно «уложить» в один запрос.