Как в запросе 1С вывести все виды начислений по сотрудникам, включая неиспользованные с нулевой суммой

← На главную

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

Попытки решить эту задачу «в лоб» через простое ЛЕВОЕ СОЕДИНЕНИЕ физической таблицы регистра с виртуальной таблицей оборотов обычно не приводят к успеху. В этой статье мы подробно разберем, почему возникает эта сложность, и рассмотрим три эффективных способа ее решения: с помощью декартова произведения в запросе, через механизмы Системы компоновки данных (СКД) и с использованием специализированного программного интерфейса (API) конфигурации ЗУП 3.1.

Выясняем причину: почему не работает обычное левое соединение

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

Такой подход некорректен по двум причинам:

Отсутствие данных в левой таблице для конкретного сотрудника. Левое соединение гарантирует вывод всех строк только из левой таблицы. Но если левая таблица — это просто плоский список уникальных начислений, у нее нет связи с конкретными физическими лицами. Пытаясь соединить ее с оборотами по сотруднику, мы не сможем «размножить» этот список начислений под каждого работающего человека. Если же левая таблица — это физическая таблица регистра движений, то в ней изначально отсутствуют записи по сотрудникам, у которых не было начислений в данном месяце. Нет записи — нет и строки в результате левого соединения.
Эффект «декартова произведения» на уровне записей движений. Физическая таблица регистра содержит множество детальных записей (регистраций расчетов) по дням и документам. Соединение физической таблицы с виртуальной таблицей оборотов приведет к лавинообразному дублированию строк (каждая детальная запись соединится с итоговым оборотом за месяц), что исказит результаты группировок и итогов.

Для правильного решения задачи нам необходимо сформировать полную координатную сетку (матрицу), где на пересечении каждого сотрудника и каждого вида начисления будет находиться соответствующая сумма (или 0, если данных в регистре нет).

Способ 1. Построение матрицы «Сотрудник × Начисление» в запросе (Декартово произведение)

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

В языке запросов 1С декартово произведение получается крайне просто: достаточно перечислить две или более таблицы в секции ИЗ через запятую, не указывая при этом никаких условий связи в секции ПО или ГДЕ. СУБД автоматически соединит каждую строку первой таблицы с каждой строкой второй таблицы.

Рассмотрим пример реализации такого запроса:


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

// ШАГ 2. Получаем уникальный список видов расчетов (начислений и удержаний)
ВЫБРАТЬ РАЗЛИЧНЫЕ
    НачисленияУдержания.НачислениеУдержание КАК НачислениеУдержание,
    НачисленияУдержания.ГруппаНачисленияУдержанияВыплаты КАК ГруппаНачисленияУдержанияВыплаты
ПОМЕСТИТЬ ВТ_Начисления
ИЗ
    РегистрНакопления.НачисленияУдержанияПоСотрудникам КАК НачисленияУдержания
ГДЕ
    НачисленияУдержания.Период МЕЖДУ НАЧАЛОПЕРИОДА(&Нач, ДЕНЬ) И КОНЕЦПЕРИОДА(&Кон, ДЕНЬ)
;

// ШАГ 3. Строим декартово произведение (Сетка: Сотрудник * Начисление)
ВЫБРАТЬ
    ВТ_Сотрудники.ФизическоеЛицо КАК ФизическоеЛицо,
    ВТ_Сотрудники.Подразделение КАК Подразделение,
    ВТ_Начисления.НачислениеУдержание КАК НачислениеУдержание,
    ВТ_Начисления.ГруппаНачисленияУдержанияВыплаты КАК ГруппаНачисленияУдержанияВыплаты
ПОМЕСТИТЬ ВТ_Сетка
ИЗ
    ВТ_Сотрудники КАК ВТ_Сотрудники,
    ВТ_Начисления КАК ВТ_Начисления
;

// ШАГ 4. Соединяем сетку с реальными оборотами регистра
ВЫБРАТЬ
    ВТ_Сетка.Подразделение КАК Подразделение,
    ВТ_Сетка.ФизическоеЛицо КАК ФизическоеЛицо,
    ВТ_Сетка.ГруппаНачисленияУдержанияВыплаты КАК ГруппаНачисленияУдержанияВыплаты,
    ВТ_Сетка.НачислениеУдержание КАК НачислениеУдержание,
    ЕСТЬNULL(ОборотыРегистра.СуммаОборот, 0) КАК СуммаНУ
ИЗ
    ВТ_Сетка КАК ВТ_Сетка
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.НачисленияУдержанияПоСотрудникам.Обороты(
        НАЧАЛОПЕРИОДА(&Нач, ДЕНЬ),
        КОНЕЦПЕРИОДА(&Кон, ДЕНЬ),
        Авто,
        (ФизическоеЛицо = &ФизическоеЛицо ИЛИ НЕ &ОтборПоФизЛицу)
        И (Подразделение В ИЕРАРХИИ (&Подразделение) ИЛИ НЕ &ОтборПоПодразделению)
    ) КАК ОборотыРегистра
    ПО ВТ_Сетка.ФизическоеЛицо = ОборотыРегистра.ФизическоеЛицо
    И ВТ_Сетка.НачислениеУдержание = ОборотыРегистра.НачислениеУдержание

УПОРЯДОЧИТЬ ПО
    Подразделение,
    ФизическоеЛицо,
    ГруппаНачисленияУдержанияВыплаты,
    НачислениеУдержание

Преимущества данного подхода: Мы гарантированно получим строки по каждому сотруднику и каждому виду расчета, существовавшему в базе за этот период. Если у сотрудника не было конкретного начисления, функция ЕСТЬNULL вернет аккуратный 0 в колонке суммы.

Недостатки: На больших базах данных (например, тысячи сотрудников и десятки видов начислений) размер временной таблицы декартова произведения ВТ_Сетка может составить сотни тысяч строк, что приведет к замедлению работы сервера СУБД. Поэтому использовать чистое декартово произведение в запросах нужно осмотрительно.

Способ 2. Решение задачи средствами Системы компоновки данных (СКД)

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

Проанализируем алгоритм настройки отчета в СКД:

В схеме компоновки данных пишется максимально простой и легкий запрос, выбирающий только реальные обороты из виртуальной таблицы регистра (без всяких соединений и декартовых произведений).
Переходим на вкладку «Настройки» компоновщика. Добавляем структуру типа «Таблица».
В строки таблицы добавляем группировки по Подразделение и ФизическоеЛицо.
В колонки таблицы добавляем группировку по полю НачислениеУдержание.
Чтобы вывести колонки по начислениям, которые не использовались у конкретного сотрудника в строке, необходимо для группировки колонок установить свойство «Выводить пустые группы». Платформа автоматически заполнит пустые пересечения строк и колонок значениями NULL.
Для того чтобы вместо пустых ячеек (или значения NULL) в отчете отображался красивый ноль, перейдите в свойства выбранного ресурса на вкладке «Ресурсы» и укажите выражение расчета как: ЕСТЬNULL(Сумма(СуммаОборот), 0).
Альтернативный вариант замены пустых ячеек на нули — использование механизма Условного форматирования. Добавляем элемент условного форматирования, в качестве оформляемого поля выбираем наш ресурс суммы, задаем условие СуммаОборот НЕ ЗАПОЛНЕНО, а в свойствах формата указываем текст: «0».

Этот способ является наиболее предпочтительным с точки зрения быстродействия системы, так как всю работу по сборке матрицы берет на себя процессор компоновки данных 1С, оптимизируя запросы к базе данных.

Способ 3. Использование встроенных механизмов ЗУП 3.1

В современных конфигурациях, работающих на платформе 1С:Предприятие 8.3 (таких как ЗУП 3.1, ERP или Комплексная автоматизация), прямое обращение к физическим таблицам регистров кадров и расчета крайне не рекомендуется. Это связано со сложной архитектурой учета периодов действия начислений, вытеснениями и частыми обновлениями метаданных со стороны фирмы «1С».

Вместо написания собственных сложных запросов к регистрам расчетов следует использовать готовый программный интерфейс общего назначения (API), предоставляемый разработчиками конфигурации. В ЗУП 3.1 для этих целей реализованы специальные функции в общих модулях.

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


// Создаем менеджер временных таблиц, который будет хранить результаты работы API
МенеджерВТ = Новый МенеджерВременныхТаблиц;

// 1. Получаем кадровые данные сотрудников (включая подразделения, должности, оклады)
КадровыеДанные = КадровыйУчет.СоздатьВТКадровыеДанныеСотрудников(
    МенеджерВТ, 
    Истина, 
    "ФизическоеЛицо, Подразделение, Должность", 
    &Кон
);

// 2. Получаем плановые начисления сотрудников и показатели тарифных ставок
ПоказателиРасчетаЗарплатыКадры.СоздатьВТНачисленияСПоказателямиТарифныхСтавок(
    МенеджерВТ,
    Истина,
    "Оклад, ТарифнаяСтавка",
    &Нач,
    &Кон
);

После вызова этих методов в вашем распоряжении (внутри объекта МенеджерВТ) появятся готовые, оптимизированные временные таблицы с кадровой историей сотрудников на нужные даты и их плановыми начислениями. Вам останется лишь написать результирующий запрос, который левым соединением свяжет эти готовые таблицы с оборотами расчетов. Такой подход полностью защищает вашу разработку от сбоев при последующих обновлениях конфигурации 1С.

Важный совет по оптимизации: избавляемся от «ИЛИ» в условиях «ГДЕ»

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


ГДЕ 
    (НЕ &ОтборПоПодразделению ИЛИ НачисленияУдержанияПоСотрудникам.Подразделение В ИЕРАРХИИ (&Подразделение))

Использование конструкции логического ИЛИ совместно с параметром-флагом (булево &ОтборПоПодразделению) в секции фильтрации ГДЕ крайне пагубно сказывается на скорости выполнения запроса СУБД.

Оптимизатор запросов СУБД (будь то Microsoft SQL Server или PostgreSQL) при наличии условия ИЛИ в предложении WHERE часто не может построить эффективный план выполнения. Система отказывается от использования индексов по полям ФизическоеЛицо или Подразделение и переходит к полному сканированию таблиц (Table Scan / Index Scan), что приводит к зависанию отчетов на реальных рабочих базах данных.

Как правильно оптимизировать фильтрацию данных:

Если вы используете компоновку данных (СКД), полностью удалите эти ручные условия из текста запроса. Настраивайте параметры и отборы на вкладке «Настройки» СКД. Система компоновки динамически и очень качественно встроит условия непосредственно в секцию ГДЕ или в параметры виртуальных таблиц на лету, не создавая лишних логических ветвлений.
Если запрос выполняется программно в коде модуля, используйте динамическое формирование текста запроса в зависимости от установленных пользователем флажков отбора. Посмотрим на пример кода:


БазовыйТекст = 
"ВЫБРАТЬ
|   Обороты.ФизическоеЛицо,
|   Обороты.СуммаОборот
|ИЗ
|   РегистрНакопления.НачисленияУдержанияПоСотрудникам.Обороты(&Нач, &Кон, , ) КАК Обороты
|ГДЕ
|   Истина";

Если ЗначениеЗаполнено(ВыбранноеПодразделение) Тогда
    БазовыйТекст = БазовыйТекст + " И Обороты.Подразделение В ИЕРАРХИИ(&Подразделение)";
КонецЕсли;

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

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