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