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