Несмотря на кажущуюся простоту отчета, построение запроса для выгрузки данных имеет ряд подводных камней.
Рассмотрим пошагово, где какие проблемы могут возникнуть и как их решать.
Для начала суть отчета – нужно вывести остатки стеллажей (пересчитав из остатка деталей) и сгруппировать по складам. Количество стеллажей определяется по минимальной кратности из всех деталей. Т.е. если стеллаж состоит из 3х деталей – А(5 шт), Б (10 шт) и В (20 шт), а остаток на складе по деталям – А (21 шт), Б (23 шт), В (25 шт), то целый стеллаж только один.
Для получения результатов запросов используется консоль запросов.
Начнем с простого подхода – к остаткам по складам соединяем (чуть не написал ВПРим) левым соединением данные с составом стеллажей из регистра сведений, чтобы получить количество стеллажей. Делим остаток деталей на количество деталей в стеллаже, группируем по складу, стеллажу и определяем минимум по «количеству стеллажей».
Для отладки – следующие исходные данные:
Два стеллажа:
Стеллаж 1 состоит из 4-х стоек, 5-ти полок, 20-ти болтов,
Стеллаж 2 – из 4-х стенок и 17-ти гаек.
Остатки на складах (Деталь |Количество | Склад): Стойка – 50 шт (Склад 1), Полка – 45 шт (склад 1), Болт – 30 шт (Склад 1), Стенка – 15 шт (Склад 2), Гайка – 64 шт (Склад 2)
Текст запроса:
ВЫБРАТЬ
ОстаткиНоменклатурыОстатки.Склад,
ПринадлежностьДеталей.Стеллаж,
МИНИМУМ(ОстаткиНоменклатурыОстатки.КоличествоОстаток / ПринадлежностьДеталей.КоличествоВСтеллаже) КАК КоличествоСтеллажей
ИЗ
РегистрНакопления.ОстаткиНоменклатуры.Остатки КАК ОстаткиНоменклатурыОстатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПринадлежностьДеталей КАК ПринадлежностьДеталей
ПО ОстаткиНоменклатурыОстатки.Номенклатура = ПринадлежностьДеталей.Деталь
СГРУППИРОВАТЬ ПО
ОстаткиНоменклатурыОстатки.Склад,
ПринадлежностьДеталей.Стеллаж
Результат – в скриншоте ниже.
Для начала, как видим, необходимо избавиться от дробной части.
Не вдаваясь в подробности, используем следующую конструкцию: ВЫРАЗИТЬ(Значение - 0.5 КАК число(15,0)) , где Значение – число, которое необходимо округлить.
Редактируем поле «КоличествоСтеллажей» на
МИНИМУМ(ВЫРАЗИТЬ(ОстаткиНоменклатурыОстатки.КоличествоОстаток / ПринадлежностьДеталей.КоличествоВСтеллаже - 0.5 КАК ЧИСЛО(15, 0))) КАК КоличествоСтеллажей
Выгружаем результат
Вроде все ОК.
НО!..
Удалим из остатков позицию Болт (все 30 шт). И выполним запрос.
Как же так – позиции Стеллаж1 вообще не должно быть в отчете (болтов вообще нет), а отражается 10 шт?
Посмотрим результат запроса с номенклатурой (для более очевидного результата изменим соединение с левого на правое).
Как видим – по математике – все верно. Из имеющихся (остаток) на складе деталей минимальная кратность – 10 (т.е. на 10 стеллажей). А должно быть 0.
Вывод – математика верная, логика неточная – необходимо доработать логику запроса.
Вариант 1.
В запрос добавляем дополнительную логику: если «ингредиентов» (видов деталей) для стеллажа на складе меньше, чем должно быть в составе стеллажа, значит остаток стеллажей на складе – 0. Например, если стеллаж состоит из трех видов деталей, а на складе есть только два – значит остаток стеллажей на складе – ноль.
Для этого в исходном запросе
А) добавляем в начале временную таблицу с количеством видов деталей для каждого стеллажа
Б) из результата исключаем строки где видов деталей для стеллажа на складе меньше, чем должно быть (т.е. оставляем, где равно)
В) добавляем исключение из итогового результата строк с нулевым результатом (т.е. для тех ситуаций, когда на складе есть все виды деталей, но хотя бы по одной детали нет количества даже на один стеллаж)
Для наглядности – результат после пункта А (по Стеллажу1 должно быть три элемента, а на складе есть только два элемента)
Итоговый текст запроса:
ВЫБРАТЬ
ПринадлежностьДеталей.Стеллаж КАК Стеллаж,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ПринадлежностьДеталей.КоличествоВСтеллаже) КАК КоличествоВСтеллаже
ПОМЕСТИТЬ ЭлементоВСтеллаже
ИЗ
РегистрСведений.ПринадлежностьДеталей КАК ПринадлежностьДеталей
СГРУППИРОВАТЬ ПО
ПринадлежностьДеталей.Стеллаж
ИНДЕКСИРОВАТЬ ПО
Стеллаж
;
//
ВЫБРАТЬ
ОстаткиНоменклатурыОстатки.Склад,
ПринадлежностьДеталей.Стеллаж,
МИНИМУМ(ВЫРАЗИТЬ(ОстаткиНоменклатурыОстатки.КоличествоОстаток / ПринадлежностьДеталей.КоличествоВСтеллаже - 0.5 КАК ЧИСЛО(15, 0))) КАК КоличествоСтеллажей,
МАКСИМУМ(ЭлементоВСтеллаже.КоличествоВСтеллаже) КАК ТребуетсяЭлементов,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ОстаткиНоменклатурыОстатки.Номенклатура) КАК ФактическиЭлементовНаСкладе
ИЗ
РегистрНакопления.ОстаткиНоменклатуры.Остатки КАК ОстаткиНоменклатурыОстатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПринадлежностьДеталей КАК ПринадлежностьДеталей
ЛЕВОЕ СОЕДИНЕНИЕ ЭлементоВСтеллаже КАК ЭлементоВСтеллаже
ПО ПринадлежностьДеталей.Стеллаж = ЭлементоВСтеллаже.Стеллаж
ПО ОстаткиНоменклатурыОстатки.Номенклатура = ПринадлежностьДеталей.Деталь
СГРУППИРОВАТЬ ПО
ОстаткиНоменклатурыОстатки.Склад,
ПринадлежностьДеталей.Стеллаж
ИМЕЮЩИЕ
МАКСИМУМ(ЭлементоВСтеллаже.КоличествоВСтеллаже) = КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ОстаткиНоменклатурыОстатки.Номенклатура)
И
(МИНИМУМ(ВЫРАЗИТЬ(ОстаткиНоменклатурыОстатки.КоличествоОстаток / ПринадлежностьДеталей.КоличествоВСтеллаже - 0.5 КАК ЧИСЛО(15, 0))) > 0)
Вариант 2. (уточнение логики исходного запроса)
Строим логику на том, что для каждого склада (на котором есть остатки) формируем список и состав всех стеллажей (т.е. максимально возможные варианты), а потом уже к ним левым соединение подгружаем фактические данные. Если на таком-то складе для такого-то стеллажа нет такой-то делали – то через ЕСТЬNULL формируем ноль.
Для реализации данной логики сначала к регистру сведений с составами стеллажей из виртуальной таблицы остаток добавляем склады (без установки связей между таблицами). В итоге для каждой номенклатуры каждого стеллажа будут добавлены все склады, на которых есть хоть какой-то остаток.
Выгружаем результат во временную таблицу и на следующем этапе присоединяем к ней остатки по складам по Номенклатуре и Стеллажу (по остаткам необходимо обнулить пустые).
Делаем поле с округлением….
МИНИМУМ(ВЫРАЗИТЬ(ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) / МаксимальныеВарианты.КоличествоВСтеллаже - 0.5 КАК ЧИСЛО(15, 0)))
и … получаем отрицательные результаты, если точнее – то минус 1 стеллаж.
Что в принципе логично, т.к. когда в конструкции ВЫРАЗИТЬ(Значение - 0.5 КАК число(15,0)) Значение равно нулю, то при округлении -0,5 до целого получается -1.
Дорабатываем конструкцию через ВЫБОР
ВЫБОР
КОГДА ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0)=0
ТОГДА 0
ИНАЧЕ
ВЫРАЗИТЬ(ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) / МаксимальныеВарианты.КоличествоВСтеллаже - 0.5 КАК ЧИСЛО(15, 0))
КОНЕЦ
Теперь все ОК
Добавляем фильтр ненулевых результатов и запрос для отчета готов.
Итоговый текст запроса:
ВЫБРАТЬ
ПринадлежностьДеталей.Стеллаж КАК Стеллаж,
ПринадлежностьДеталей.Деталь,
ПринадлежностьДеталей.КоличествоВСтеллаже,
ОстаткиНоменклатурыОстатки.Склад КАК Склад
ПОМЕСТИТЬ МаксимальныеВарианты
ИЗ
РегистрСведений.ПринадлежностьДеталей КАК ПринадлежностьДеталей,
РегистрНакопления.ОстаткиНоменклатуры.Остатки КАК ОстаткиНоменклатурыОстатки
;
//
ВЫБРАТЬ
МаксимальныеВарианты.Стеллаж,
МаксимальныеВарианты.Склад,
МИНИМУМ(ВЫБОР
КОГДА ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) = 0
ТОГДА 0
ИНАЧЕ ВЫРАЗИТЬ(ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) / МаксимальныеВарианты.КоличествоВСтеллаже - 0.5 КАК ЧИСЛО(15, 0))
КОНЕЦ) КАК КоличествоСтеллажей
ИЗ
МаксимальныеВарианты КАК МаксимальныеВарианты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиНоменклатуры.Остатки КАК ОстаткиНоменклатурыОстатки
ПО МаксимальныеВарианты.Деталь = ОстаткиНоменклатурыОстатки.Номенклатура
И МаксимальныеВарианты.Склад = ОстаткиНоменклатурыОстатки.Склад
СГРУППИРОВАТЬ ПО
МаксимальныеВарианты.Стеллаж,
МаксимальныеВарианты.Склад
ИМЕЮЩИЕ
МИНИМУМ(ВЫБОР
КОГДА ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) = 0
ТОГДА 0
ИНАЧЕ ВЫРАЗИТЬ(ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) / МаксимальныеВарианты.КоличествоВСтеллаже - 0.5 КАК ЧИСЛО(15, 0))
КОНЕЦ)
> 0
Подводя итоги – вариантов реализации запроса для отчета два:
- Для каждого стеллажа определяем максимальное число элементов и сравниваем с числом элементов на складе. Если на складе меньше – значит стеллажей ноль
- Определяем все возможные комбинации составов стеллажей/деталей со складами (склады где есть остатки) и затем присоединяем фактические остатки.
Возможно имеются и другие варианты, но я пока их не нашел.
Спасибо за внимание!
