При построении отчетов по продажам за длительный интервал времени (квартал, полугодие или год) разработчики часто сталкиваются с задачей: необходимо вывести количество проданного товара, фактическую выручку и плановую стоимость (например, количество, умноженное на нормативную цену или себестоимость, актуальную именно на момент совершения продажи). Если за один месяц или на конкретную фиксированную дату срез получить просто, то при выборке за год срез цен должен динамически определяться под каждую отдельную дату строки продажи.
Рассмотрим природу этой проблемы и разберем практические способы реализации такого отчета на платформе 1С:Предприятие 8.
Многие начинающие разработчики пытаются вызвать виртуальную таблицу РегистрСведений.ЦеныНоменклатуры.СрезПоследних и передать в ее параметры дату документа продажи через условие соединения ПО. Однако в языке запросов 1С виртуальная таблица СрезПоследних рассчитывается ровно один раз для всего запроса целиком. Параметры виртуальной таблицы не могут принимать динамические значения из полей других таблиц пакета.
По этой причине передать дату регистратора внутрь среза последних невозможно. Проанализируем возможные варианты решения этой задачи в зависимости от условий изменения цен и инструмента построения отчета.
Разберем ситуацию, когда бизнес-логика строго регламентирует: цена или плановая себестоимость утверждается ровно один раз в месяц (например, первого числа) и не меняется внутри календарного месяца. В этом случае задача существенно упрощается, так как отпадает необходимость вычислять срез на каждую секунду.
Для реализации такого механизма мы можем привести дату продажи и дату установки цены к началу месяца с помощью функции НАЧАЛОПЕРИОДА, после чего связать таблицы простым левым соединением по точному равенству.
Посмотрим на пример построения такого запроса:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
НАЧАЛОПЕРИОДА(ПродажиОбороты.Период, МЕСЯЦ) КАК МесяцПродажи,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.СтоимостьОборот КАК СуммаВыручки
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ПродажиОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
НАЧАЛОПЕРИОДА(ЦеныНоменклатуры.Период, МЕСЯЦ) КАК МесяцЦены,
ЦеныНоменклатуры.Цена КАК Цена
ПОМЕСТИТЬ ВТ_Цены
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
ЦеныНоменклатуры.ТипЦен = &ТипЦены
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.МесяцПродажи КАК Период,
Продажи.Количество КАК Количество,
Продажи.СуммаВыручки КАК СуммаВыручки,
ЕСТЬNULL(Цены.Цена, 0) КАК Цена,
Продажи.Количество * ЕСТЬNULL(Цены.Цена, 0) КАК ПлановаяСумма
ИЗ
ВТ_Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК Цены
ПО Продажи.Номенклатура = Цены.Номенклатура
И Продажи.МесяцПродажи = Цены.МесяцЦены
Обратим внимание на вызов ЕСТЬNULL: если для номенклатуры в каком-либо месяце цена не была зафиксирована, левое соединение вернет значение NULL. Без проверки на NULL результат умножения также станет неопределенным.
Если цены могут изменяться в произвольный день и произвольное количество раз в течение месяца, простого соединения по началу месяца недостаточно. В таком случае мы должны сымитировать поведение среза последних вручную, обращаясь напрямую к физической таблице регистра сведений.
Разберем алгоритм решения по шагам:
Цены.Период <= Продажи.Период. В этой же выборке группируем строки по продаже и находим МАКСИМУМ(Цены.Период). Это и будет дата актуальной цены на момент конкретной продажи.Номенклатура, ТипЦен и Период = МаксимальныйПериод получить само числовое значение цены.Посмотрим, как выглядит универсальный текст запроса:
ВЫБРАТЬ
Продажи.Период КАК ПериодПродажи,
Продажи.Регистратор КАК Регистратор,
Продажи.Номенклатура КАК Номенклатура,
Продажи.Количество КАК Количество,
Продажи.Сумма КАК Сумма
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Продажи.ПериодПродажи КАК ПериодПродажи,
Продажи.Регистратор КАК Регистратор,
Продажи.Номенклатура КАК Номенклатура,
МАКСИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
ПО Продажи.Номенклатура = Цены.Номенклатура
И (Цены.Период <= Продажи.ПериодПродажи)
И (Цены.ТипЦен = &ТипЦены)
СГРУППИРОВАТЬ ПО
Продажи.ПериодПродажи,
Продажи.Регистратор,
Продажи.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Продажи.ПериодПродажи КАК Период,
Продажи.Регистратор КАК Регистратор,
Продажи.Номенклатура КАК Номенклатура,
Продажи.Количество КАК Количество,
Продажи.Сумма КАК Сумма,
ЕСТЬNULL(Цены.Цена, 0) КАК Цена,
Продажи.Количество * ЕСТЬNULL(Цены.Цена, 0) КАК РасчетнаяСтоимость
ИЗ
ВТ_Продажи КАК Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_МаксимальныеДаты КАК ДатыЦен
ПО Продажи.ПериодПродажи = ДатыЦен.ПериодПродажи
И Продажи.Регистратор = ДатыЦен.Регистратор
И Продажи.Номенклатура = ДатыЦен.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
ПО ДатыЦен.Номенклатура = Цены.Номенклатура
И ДатыЦен.ПериодЦены = Цены.Период
И (Цены.ТипЦен = &ТипЦены)
Важное замечание по производительности: соединение по нестрогому неравенству Цены.Период <= Продажи.ПериодПродажи может приводить к формированию декартова произведения на стороне СУБД. При больших объемах данных (десятки и сотни тысяч строк) такой запрос может выполняться медленно. Чтобы ускорить расчет, обязательно ограничивайте выборку жестким отбором по конкретному типу цен.
Если отчет строится в Системе компоновки данных (СКД), разработчику не требуется писать громоздкие запросы к реальным таблицам регистров сведений. В СКД заложен штатный механизм передачи параметров из одной строки набора данных в виртуальную таблицу другого набора данных.
Разберем, как настроить такую схему:
НаборДанныхПродажи, где выбираем номенклатуру, период строки документа, количество и сумму.НаборДанныхЦены. В нем обращаемся к виртуальной таблице:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодЦены, ТипЦен = &ТипЦены) КАК ЦеныНоменклатурыСрезПоследних
НаборДанныхПродажи.НаборДанныхЦены.Номенклатура = Номенклатура.ПериодЦены = Период (где Период — это поле даты конкретной строки продажи из первого набора).При выполнении отчета СКД автоматически выполнит оптимизированный срез последних на каждую переданную дату продажи без необходимости строить ресурсоемкие промежуточные объединения вручную.