Как правильно установить условие по подразделению и физическому лицу в запросе остатков по счету МЦ?

← На главную

При разработке и кастомизации механизмов заполнения документов в среде 1С:Предприятие разработчики часто сталкиваются с необходимостью накладывать дополнительные фильтры на существующие (типовые) запросы остатков. Типичный пример — заполнение табличной части документа списания материалов из эксплуатации с отбором по конкретному сотруднику (физическому лицу) и подразделению. На первый взгляд задача кажется простой, однако при её реализации могут возникнуть неочевидные проблемы: неэффективная работа СУБД из-за неоптимальных условий, пустые результаты запроса при попытке сравнения с пустой ссылкой измерения, а также трудности с динамическим изменением текста запроса.

В этой статье мы подробно разберем, как правильно спроектировать условия в запросе по субконто и измерениям регистра бухгалтерии, выясним причины типичных ошибок и рассмотрим наиболее изящные методы динамического управления параметрами запроса.

Разбор архитектуры хранения данных и первая задача: Отбор по Физическому лицу

Для начала проанализируем структуру хранения данных в регистре бухгалтерии Хозрасчетный для счетов учета материалов в эксплуатации (например, забалансовый счет МЦ.04 "Инвентарь и хозяйственные принадлежности в эксплуатации"). На таких счетах аналитика обычно ведется в разрезе субконто:

Субконто1 — Номенклатура (тип СправочникСсылка.Номенклатура).
Субконто2 — Партия материалов в эксплуатации (документ-регистратор, например, ПередачаМатериаловВЭксплуатацию или ПартияМатериаловВЭксплуатацию).
Субконто3 — Физическое лицо (тип СправочникСсылка.ФизическиеЛица).

Поскольку аналитика по сотруднику хранится в третьем субконто, для наложения отбора по конкретному физическому лицу нам необходимо обратиться к полю Субконто3 виртуальной таблицы остатков. Самым правильным решением является передача этого условия в параметры самой виртуальной таблицы, так как это позволяет СУБД отсечь лишние записи на этапе сборки остатков, а не фильтровать уже полученный огромный массив данных в секции ГДЕ.

Рассмотрим пример установки отбора по физическому лицу в параметрах виртуальной таблицы остатков:


ВЫБРАТЬ
    ХозрасчетныйОстаткиМЦ.Субконто1 КАК Номенклатура,
    ХозрасчетныйОстаткиМЦ.Субконто2 КАК ПартияМатериаловВЭксплуатации,
    ХозрасчетныйОстаткиМЦ.Субконто3 КАК Физлицо,
    ХозрасчетныйОстаткиМЦ.КоличествоОстатокДт КАК Количество
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(
        &Период,
        Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.ИнвентарьИХозяйственныеПринадлежностиВЭксплуатации),
        ,
        Организация = &Организация 
        И Субконто3 = &ФизЛицо
    ) КАК ХозрасчетныйОстаткиМЦ

В коде модуля при этом обязательно нужно передать соответствующий параметр:


Запрос.УстановитьПараметр("Период", ПериодГраница);
Запрос.УстановитьПараметр("Организация", Объект.Организация);
Запрос.УстановитьПараметр("ФизЛицо", Объект.ФизЛицо);

Проблема отбора по подразделению: Почему сравнение с пустой ссылкой возвращает пустой результат?

При попытке наложить отбор по измерению Подразделение разработчики часто сталкиваются с ситуацией, когда замена условия Подразделение ЕСТЬ NULL на сравнение с пустой ссылкой вида Подразделение = ЗНАЧЕНИЕ(Справочник.ПодразделенияОрганизаций.ПустаяСсылка) приводит к тому, что запрос перестает возвращать какие-либо данные вообще. Проанализируем причину этого поведения.

Измерения регистра и особенности хранения в БД: В отличие от субконто, Подразделение в регистре бухгалтерии является балансовым измерением. Однако учет по подразделениям может быть отключен в целом по системе в настройках учетной политики или функциональности программы, либо этот учет может быть выключен для конкретного счета (в данном случае для счета учета материалов в эксплуатации).

Если учет по подразделениям для выбранного счета не ведется, платформа 1С на физическом уровне СУБД записывает в колонку Подразделение значение NULL (отсутствие значения). Пустой ссылки справочника подразделений в базе данных физически нет в этих строках. С точки зрения реляционных баз данных, любое сравнение значения NULL с конкретным значением (даже с пустой ссылкой) дает результат "Ложь" (FALSE).

Именно поэтому проверка на равенство пустой ссылке отсекает все строки. Правильное и безопасное условие, учитывающее особенности настройки учета по подразделениям на конкретном предприятии, должно выглядеть следующим образом:

(Подразделение = &Подразделение ИЛИ Подразделение ЕСТЬ NULL)

Это условие гарантирует, что если учет по подразделениям на данном счете ведется, система выберет данные по указанному подразделению. Если же учет по подразделениям отключен (и в базе лежит системный NULL), запрос все равно вернет корректные остатки.

Оптимизация условий по типу документа (UNION ALL против оператора ИЛИ)

В типовых конфигурациях 1С часто можно встретить объединение (ОБЪЕДИНИТЬ ВСЕ) двух практически идентичных запросов, различающихся только типом документа в поле Субконто2. Возникает закономерный вопрос: почему бы не упростить запрос и не написать одно условие через оператор ИЛИ, например:


И (Субконто2 ССЫЛКА Документ.ПередачаМатериаловВЭксплуатацию 
    ИЛИ Субконто2 ССЫЛКА Документ.ПартияМатериаловВЭксплуатацию)

С точки зрения лаконичности кода это выглядит привлекательно, однако с точки зрения СУБД и оптимизатора запросов это может стать критической ошибкой. Рассмотрим причины подробнее:

Поскольку поле Субконто имеет составной тип данных, физически в СУБД значения субконто хранятся в отдельной таблице значений субконто, которая соединяется с основной таблицей итогов. Когда мы используем оператор ССЫЛКА, платформа вынуждена генерировать левые соединения (LEFT JOIN) с таблицами соответствующих документов.

При использовании оператора ИЛИ между проверками типов оптимизатор СУБД (будь то MS SQL Server или PostgreSQL) зачастую не может построить эффективный план выполнения запроса. Вместо быстрого поиска по индексу (Index Seek) СУБД переходит к полному сканированию таблиц (Table/Index Scan). На базах с большим объемом данных это приводит к колоссальному падению производительности и зависанию системы.

Разделение запроса через ОБЪЕДИНИТЬ ВСЕ позволяет СУБД построить два отдельных простых плана выполнения, каждый из которых будет использовать точечные индексы по конкретному типу документа, после чего результаты будут мгновенно объединены в оперативной памяти. Именно поэтому в типовых решениях разработчики фирмы "1С" сознательно используют конструкцию ОБЪЕДИНИТЬ ВСЕ для разных типов документов субконто.

Построение динамических условий в запросе: уходим от устаревших подходов

Допустим, нам необходимо сделать так, чтобы отборы по подразделению и физическому лицу действовали только тогда, когда эти реквизиты заполнены в шапке документа. Если они пустые — отбор производиться не должен.

Довольно часто разработчики пытаются решить эту задачу путем текстовой подстановки с помощью функции СтрЗаменить(), заменяя условные маркеры (например, "{ПроизвольноеУсловие}") на строки кода. Этот подход крайне не рекомендуется:

Код становится нечитаемым и подвержен синтаксическим ошибкам при переименовании реквизитов.
Теряется возможность использовать встроенный Конструктор запросов платформы 1С для редактирования текста.
Значительно усложняется отладка кода.

Рассмотрим два профессиональных альтернативных подхода, которые полностью соответствуют стандартам разработки 1С.

Способ 1: Использование универсальных логических флагов в параметрах

Этот способ позволяет написать неизменяемый, полностью совместимый с Конструктором запросов текст, логика которого управляется исключительно значениями передаваемых параметров. В тексте запроса мы прописываем условия следующим образом:


ВЫБРАТЬ
    ХозрасчетныйОстаткиМЦ.Субконто1 КАК Номенклатура,
    ХозрасчетныйОстаткиМЦ.Субконто2 КАК ПартияМатериаловВЭксплуатации,
    ХозрасчетныйОстаткиМЦ.Субконто3 КАК Физлицо,
    ХозрасчетныйОстаткиМЦ.КоличествоОстатокДт КАК Количество
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(
        &Период,
        Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.ИнвентарьИХозяйственныеПринадлежностиВЭксплуатации),
        ,
        Организация = &Организация 
        И (НЕ &ОтбиратьПоФизЛицу ИЛИ Субконто3 = &ФизЛицо)
        И (НЕ &ОтбиратьПоПодразделению ИЛИ Подразделение = &Подразделение ИЛИ Подразделение ЕСТЬ NULL)
    ) КАК ХозрасчетныйОстаткиМЦ

Теперь в модуле заполнения мы просто анализируем заполненность реквизитов шапки документа и передаем булевы флаги:


ПроводитьОтборПоФизЛицу = ЗначениеЗаполнено(Объект.ФизЛицо);
ПроводитьОтборПоПодразделению = ЗначениеЗаполнено(Объект.Подразделение);

Запрос.УстановитьПараметр("Период", ПериодГраница);
Запрос.УстановитьПараметр("Организация", Объект.Организация);

Запрос.УстановитьПараметр("ОтбиратьПоФизЛицу", ПроводитьОтборПоФизЛицу);
Запрос.УстановитьПараметр("ФизЛицо", Объект.ФизЛицо);

Запрос.УстановитьПараметр("ОтбиратьПоПодразделению", ПроводитьОтборПоПодразделению);
Запрос.УстановитьПараметр("Подразделение", Объект.Подразделение);

Если, например, Объект.ФизЛицо не заполнено, параметр &ОтбиратьПоФизЛицу примет значение Ложь. Часть условия (НЕ &ОтбиратьПоФизЛицу ИЛИ Субконто3 = &ФизЛицо) превратится в (Истина ИЛИ Субконто3 = ...), что всегда дает результат Истина. Таким образом, отбор по физическому лицу просто не будет влиять на итоговую выборку. Это элегантный, быстрый и абсолютно безопасный вариант.

Способ 2: Чистая программная сборка условий

Если по какой-то причине вы не хотите перегружать СУБД лишними логическими вычислениями в параметрах виртуальной таблицы (хотя современные оптимизаторы СУБД прекрасно справляются с условиями из Способа 1), можно собрать текст условий программно, но делать это нужно аккуратно, используя конкатенацию строк только для необязательных блоков:


// Базовый текст запроса с маркером для вставки условий
ТекстЗапроса = 
"ВЫБРАТЬ
|   ХозрасчетныйОстаткиМЦ.Субконто1 КАК Номенклатура,
|   ХозрасчетныйОстаткиМЦ.Субконто2 КАК ПартияМатериаловВЭксплуатации,
|   ХозрасчетныйОстаткиМЦ.Субконто3 КАК Физлицо,
|   ХозрасчетныйОстаткиМЦ.КоличествоОстатокДт КАК Количество
|ИЗ
|   РегистрБухгалтерии.Хозрасчетный.Остатки(
|       &Период,
|       Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.ИнвентарьИХозяйственныеПринадлежностиВЭксплуатации),
|       ,
|       Организация = &Организация %ДополнительныеУсловия%
|   ) КАК ХозрасчетныйОстаткиМЦ";

СтрокаУсловий = "";

Если ЗначениеЗаполнено(Объект.ФизЛицо) Тогда
    СтрокаУсловий = СтрокаУсловий + " И Субконто3 = &ФизЛицо";
КонецЕсли;

Если ЗначениеЗаполнено(Объект.Подразделение) Тогда
    СтрокаУсловий = СтрокаУсловий + " И (Подразделение = &Подразделение ИЛИ Подразделение ЕСТЬ NULL)";
КонецЕсли;

// Производим замену маркера в тексте запроса
ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "%ДополнительныеУсловия%", СтрокаУсловий);

Запрос = Новый Запрос(ТекстЗапроса);
Запрос.УстановитьПараметр("Период", ПериодГраница);
Запрос.УстановитьПараметр("Организация", Объект.Организация);

Если ЗначениеЗаполнено(Объект.ФизЛицо) Тогда
    Запрос.УстановитьПараметр("ФизЛицо", Объект.ФизЛицо);
КонецЕсли;

Если ЗначениеЗаполнено(Объект.Подразделение) Тогда
    Запрос.УстановитьПараметр("Подразделение", Объект.Подразделение);
КонецЕсли;

Этот способ гарантирует формирование максимально чистого и быстрого SQL-запроса на уровне СУБД, при этом сохраняя строгую типизацию параметров и исключая логические ошибки при построении условий.

Подведем итоги: При работе с виртуальными таблицами остатков регистра бухгалтерии всегда учитывайте особенности ведения учета по измерениям и субконто. Помните, что отсутствие учета по измерению на конкретном счете физически оборачивается значением NULL, а не пустой ссылкой. Используя предложенные методики настройки условий и динамического формирования параметров, вы сможете создавать производительные, масштабируемые и легко поддерживаемые решения в среде 1С:Предприятие.