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