При разработке отчетов на схеме компоновки данных (СКД) в конфигурации «1С:Управление производственным предприятием 1.3» (1С:УПП) программисты часто сталкиваются со сложной задачей: необходимо вывести остатки и обороты материалов на складах, но ограничить вывод только теми позициями, которые входят в спецификацию конкретного изготавливаемого прибора или изделия.
На первый взгляд задача кажется простой — достаточно соединить виртуальную таблицу остатков и оборотов со спецификациями. Однако при реализации «в лоб» разработчик неизбежно сталкивается с двумя серьезными техническими проблемами:
LEFT JOIN) СУБД дублирует строки с остатками, а СКД суммирует их в ресурсах.В этой статье мы подробно разберем природу обеих проблем и рассмотрим надежные практические способы их решения.
Выясним причину искажения данных. Когда мы выполняем левое соединение таблицы остатков регистра ТоварыНаСкладах.ОстаткиИОбороты со справочником спецификаций, связь происходит по полю Номенклатура:
ВЫБРАТЬ
ТоварыНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток КАК Остаток,
Спецификации.Ссылка КАК Спецификация
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ТоварыНаСкладахОстаткиИОбороты
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК Спецификации
ПО ТоварыНаСкладахОстаткиИОбороты.Номенклатура = Спецификации.Номенклатура
Если материал «Винт М4» имеет остаток на складе в количестве 10 штук, но при этом фигурирует в трех разных спецификациях изделий (или в трех разных узлах одной спецификации), результатом этого запроса станут три строки. В каждой строке будет указан остаток 10 штук. Когда СКД начнет рассчитывать общие итоги по ресурсу КоличествоКонечныйОстаток, она сложит эти строки и выведет общую сумму 30 штук вместо реальных 10. Это классическая ошибка связей «один-ко-многим» в реляционных базах данных.
Для исправления этой ситуации применим наиболее правильный и архитектурный метод — использование двух независимых наборов данных в СКД и их связывание на уровне схемы компоновки.
Рассмотрим пошагово, как это настроить:
ВЫБРАТЬ
ТоварыНаСкладах.Склад КАК Склад,
ТоварыНаСкладах.Номенклатура КАК Номенклатура,
ТоварыНаСкладах.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
ТоварыНаСкладах.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
ТоварыНаСкладах.КоличествоПриход КАК КоличествоПриход,
ТоварыНаСкладах.КоличествоРасход КАК КоличествоРасход
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Авто, , ) КАК ТоварыНаСкладах
ВЫБРАТЬ DISTINCT
СпецификацииИсходныеКомплектующие.Номенклатура КАК Номенклатура,
СпецификацииИсходныеКомплектующие.Ссылка КАК Спецификация
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК СпецификацииИсходныеКомплектующие
НаборДанных1НаборДанных2Номенклатура = НоменклатураПочему это решает проблему? При такой настройке СКД сначала получит уникальные записи из НаборДанных1, рассчитает по ним корректные итоги (сумма остатков будет ровно 10 штук), а затем для каждой строки номенклатуры просто сопоставит и выведет соответствующие спецификации из подчиненного НаборДанных2. Дублирования числовых показателей при этом не произойдет.
Перейдем ко второй проблеме. Как правило, готовая продукция состоит из полуфабрикатов, те — из других полуфабрикатов, и только на самом нижнем уровне находятся чистые материалы. Простым соединением таблиц мы получим доступ только к первому уровню вложенности.
Поскольку язык запросов 1С не поддерживает рекурсивные конструкции (такие как рекурсивные CTE в СУБД MS SQL), стандартными запросами обойти дерево на неограниченную глубину невозможно. Проанализируем три альтернативных метода решения этой задачи.
В типовой конфигурации 1С:УПП 1.3 уже заложен мощный программный механизм разузлования, оптимизированный под производственную специфику предприятия. Он учитывает не только дерево вложенности, но и активные спецификации на дату, аналоги, формулы расчета и единицы измерения. Этот механизм находится в общем модуле РазузлованиеНоменклатуры в функции РазузловатьНоменклатуру().
Для интеграции этого алгоритма в наш отчет СКД выполним следующие шаги:
ТаблицаФильтраНоменклатуры. Определим в нем одно поле — Номенклатура.Номенклатура) на вкладке «Связи наборов данных».
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь;
// Получаем настройки пользователя
Настройки = КомпоновщикНастроек.ПолучитьНастройки();
// Ищем, установлена ли в отборах конкретная спецификация
ПараметрСпецификация = Неопределено;
Для Каждого ЭлементОтбора Из Настройки.Отбор.Элементы Цикл
Если ТипЗнч(ЭлементОтбора) = Тип("ЭлементОтбораКомпоновкиДанных")
И ЭлементОтбора.ЛевоеЗначение = Новый ПолеКомпоновкиДанных("Спецификация") Тогда
Если ЭлементОтбора.Использование И ЗначениеЗаполнено(ЭлементОтбора.ПравоеЗначение) Тогда
ПараметрСпецификация = ЭлементОтбора.ПравоеЗначение;
КонецЕсли;
КонецЕсли;
КонецЦикла;
// Создаем таблицу значений, которую передадим в СКД как внешний набор данных
ТЗНоменклатуры = Новый ТаблицаЗначений;
ТЗНоменклатуры.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
Если ЗначениеЗаполнено(ПараметрСпецификация) Тогда
// Подготавливаем параметры для типовой функции разузлования УПП
ТаблицаНоменклатуры = Новый ТаблицаЗначений;
ТаблицаНоменклатуры.Колонки.Добавить("Номенклатура");
ТаблицаНоменклатуры.Колонки.Добавить("Спецификация");
ТаблицаНоменклатуры.Колонки.Добавить("Количество");
// Получаем головную номенклатуру этой спецификации
НоменклатураВладелец = ПараметрСпецификация.Владелец;
НоваяСтрока = ТаблицаНоменклатуры.Добавить();
НоваяСтрока.Номенклатура = НоменклатураВладелец;
НоваяСтрока.Спецификация = ПараметрСпецификация;
НоваяСтрока.Количество = 1;
// Вызываем штатный механизм разузлования УПП
ПараметрыРазузлования = РазузловатьНоменклатуры.ПолучитьСтруктуруПараметровРазузкования();
ПараметрыРазузлования.ВыполнятьРазузлование = Истина;
ДеревоКомплектующих = РазузловатьНоменклатуры.РазузловатьНоменклатуру(ТаблицаНоменклатуры, РабочаяДата, ПараметрыРазузлования);
// Заполняем ТЗ всеми найденными на любых уровнях комплектующими
Для Каждого СтрокаДерева Из ДеревоКомплектующих.Строки Цикл
ЗаполнитьКонечныеКомпоненты(СтрокаДерева, ТЗНоменклатуры);
КонецЦикла;
КонецЕсли;
// Передаем сформированную ТЗ в процессор компоновки
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ТаблицаФильтраНоменклатуры", ТЗНоменклатуры);
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки, ДанныеРасшифровки);
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки, Истина);
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.Выводить(ПроцессорКомпоновки);
КонецПроцедуры
Процедура ЗаполнитьКонечныеКомпоненты(СтрокаУровня, ТЗРезультат)
// Добавляем текущую позицию
НоваяСтрока = ТЗРезультат.Добавить();
НоваяСтрока.Номенклатура = СтрокаУровня.Номенклатура;
// Рекурсивно обходим подчиненные элементы дерева
Для Каждого ПодчиненнаяСтрока Из СтрокаУровня.Строки Цикл
ЗаполнитьКонечныеКомпоненты(ПодчиненнаяСтрока, ТЗРезультат);
КонецЦикла;
КонецПроцедуры
Данный подход является максимально надежным, так как исключает написание сложных рекурсивных запросов и использует проверенные системные функции конфигурации 1С:УПП 1.3.
Если перед нами стоит задача сделать отчет полностью в визуальном конструкторе СКД, без написания программного кода в модуле объекта, мы можем задействовать внутренний рекурсивный механизм связей СКД.
Рассмотрим, как его настроить:
ВЫБРАТЬ
Спецификации.Ссылка КАК СпецификацияРодитель,
Спецификации.Номенклатура КАК Компонент,
Спецификации.Спецификация КАК СпецификацияКомпонента
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК Спецификации
НашНаборНашНаборСпецификацияКомпонентаСпецификацияРодитель&КорневаяСпецификацияПри таких настройках СКД самостоятельно начнет выполнять рекурсивные итерации, подставляя значение поля СпецификацияКомпонента на место СпецификацияРодитель следующего уровня, пока дерево спецификаций не будет развернуто до самого низа. На выходе мы получим готовую плоскую иерархию компонентов.
Иногда требуется решить задачу исключительно на уровне одного SQL-запроса (например, при жестком ограничении среды разработки или для быстрого создания консольного запроса). В таком случае можно применить технику последовательного разложения во временных таблицах на заранее определенную фиксированную глубину (например, до 3 уровней вложенности).
Проанализируем пример такого запроса:
// Шаг 1: Получаем 1-й уровень комплектующих по корневой спецификации
ВЫБРАТЬ
ИсходныеКомплектующие.Номенклатура КАК Номенклатура,
ИсходныеКомплектующие.Спецификация КАК Спецификация
ПОМЕСТИТЬ ВТ_Уровень1
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК ИсходныеКомплектующие
ГДЕ
ИсходныеКомплектующие.Ссылка = &КорневаяСпецификация
;
// Шаг 2: Получаем 2-й уровень комплектующих для тех позиций 1-го уровня, у которых есть свои спецификации
ВЫБРАТЬ
ИсходныеКомплектующие.Номенклатура КАК Номенклатура,
ИсходныеКомплектующие.Спецификация КАК Спецификация
ПОМЕСТИТЬ ВТ_Уровень2
ИЗ
ВТ_Уровень1 КАК Уровень1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК ИсходныеКомплектующие
ПО Уровень1.Спецификация = ИсходныеКомплектующие.Ссылка
;
// Шаг 3: Получаем 3-й уровень
ВЫБРАТЬ
ИсходныеКомплектующие.Номенклатура КАК Номенклатура,
ИсходныеКомплектующие.Спецификация КАК Спецификация
ПОМЕСТИТЬ ВТ_Уровень3
ИЗ
ВТ_Уровень2 КАК Уровень2
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК ИсходныеКомплектующие
ПО Уровень2.Спецификация = ИсходныеКомплектующие.Ссылка
;
// Шаг 4: Объединяем все уровни в единую таблицу фильтрации номенклатуры
ВЫБРАТЬ Номенклатура ПОМЕСТИТЬ ВТ_ИтоговыйФильтр ИЗ ВТ_Уровень1
ОБЪЕДИНИТЬ
ВЫБРАТЬ Номенклатура ИЗ ВТ_Уровень2
ОБЪЕДИНИТЬ
ВЫБРАТЬ Номенклатура ИЗ ВТ_Уровень3
;
// Шаг 5: Выбираем остатки на складах, соединяя их с таблицей итогового фильтра
ВЫБРАТЬ
ТоварыНаСкладах.Склад КАК Склад,
ТоварыНаСкладах.Номенклатура КАК Номенклатура,
ТоварыНаСкладах.КоличествоКонечныйОстаток КАК Остаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ТоварыНаСкладах
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИтоговыйФильтр КАК Фильтр
ПО ТоварыНаСкладах.Номенклатура = Фильтр.Номенклатура
Преимуществом данного метода является высокая скорость выполнения на уровне СУБД. Главный недостаток — ограничение глубины вложенности. Если на производстве появится изделие с 4-м или 5-м уровнем вложенности полуфабрикатов, компоненты этих уровней не попадут в отчет, и запрос придется расширять вручную.
Подводя итоги, мы рекомендуем использовать Способ А (с программным разузлованием средствами общего модуля УПП), так как он гарантирует 100% точность расчета состава изделия и автоматически подстраивается под любые изменения производственной логики предприятия.