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