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