В практике управленческого учета часто возникает сложная задача расчета мотивации сотрудников. Например, когда менеджеру по продажам начисляется базовый процент при недовыполнении плана продаж и повышенный процент при его выполнении. Однако бизнес-логика может требовать перерасчета процентов за предыдущие месяцы «задним числом». Если в текущем месяце менеджер существенно перевыполнил план, и этого «излишка» хватает для закрытия дефицита прошлых периодов внутри квартала, система должна автоматически пересчитать прошлые периоды по повышенной ставке и доначислить разницу.
Многие разработчики при усложнении подобных алгоритмов отказываются от возможностей языка запросов и СКД, предпочитая выгружать данные в таблицу значений и обрабатывать их вручную в коде. В этой статье мы подробно разберем, почему это делать необязательно, и проанализируем три эффективных способа решения этой задачи штатными средствами 1С.
Рассмотрим бизнес-кейс на конкретном примере:
Ниже мы подробно разберем, как реализовать такую логику различными методами.
---
Этот способ был предложен участниками форума как самый очевидный вариант обхода ограничений простого запроса. Его суть заключается в том, что мы сначала собираем все первичные данные по планам и фактам из базы данных программно, выгружаем их в объект ТаблицаЗначений, обрабатываем строки в цикле по алгоритму распределения остатков и затем передаем готовую таблицу в СКД через механизм Внешних наборов данных.
Рассмотрим по шагам, как подготовить такую таблицу значений программным путем:
// Шаг 1. Получаем исходные данные запросом
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ПродажиОбороты.Менеджер КАК Менеджер,
| ПродажиОбороты.Период КАК Период,
| ЕСТЬNULL(Планы.План, 0) КАК План,
| ЕСТЬNULL(ПродажиОбороты.СуммаОборот, 0) КАК Факт
|ИЗ
| РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК ПродажиОбороты
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПланыПродаж КАК Планы
| ПО ПродажиОбороты.Менеджер = Планы.Менеджер
| И НАЧАЛОПЕРИОДА(ПродажиОбороты.Период, МЕСЯЦ) = НАЧАЛОПЕРИОДА(Планы.Период, МЕСЯЦ)
|УПОРЯДОЧИТЬ ПО
| Менеджер,
| Период";
ТЗ_Данные = Запрос.Выполнить().Выгрузить();
// Добавим колонки для расчета результатов
ТЗ_Данные.Колонки.Добавить("ПроцентМенеджеру", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(5, 2)));
ТЗ_Данные.Колонки.Добавить("СуммаМенеджеру", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
ТЗ_Данные.Колонки.Добавить("КорректировкаПрошлыхПериодов", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 2)));
// Шаг 2. Алгоритм распределения перевыполнения по периодам
// Сгруппируем данные по менеджерам для корректной обработки
ТаблицаМенеджеров = ТЗ_Данные.Скопировать();
ТаблицаМенеджеров.Свернуть("Менеджер");
Для Каждого СтрокаМенеджер Из ТаблицаМенеджеров Цикл
Отбор = Новый Структура("Менеджер", СтрокаМенеджер.Mенеджер);
СтрокиМенеджера = ТЗ_Данные.НайтиСтроки(Отбор);
// Хранилище дефицитов прошлых месяцев внутри квартала
МассивДефицитов = Новый Массив;
Для Каждого СтрокаПериода Из СтрокиМенеджера Цикл
БалансМесяца = СтрокаПериода.Факт - СтрокаПериода.План;
Если БалансМесяца < 0 Тогда
// Запоминаем дефицитный месяц для возможного покрытия в будущем
СтруктураДефицита = Новый Структура("СтрокаТаблицы, ВеличинаДефицита", СтрокаПериода, -БалансМесяца);
МассивДефицитов.Добавить(СтруктураДефицита);
// По умолчанию платим базовый процент 2%
СтрокаПериода.ПроцентМенеджеру = 2;
СтрокаПериода.СуммаМенеджеру = СтрокаПериода.Факт * 2 / 100;
Иначе
// План текущего месяца выполнен, предварительно ставим повышенный процент 3%
СтрокаПериода.ПроцентМенеджеру = 3;
СтрокаПериода.СуммаМенеджеру = СтрокаПериода.Факт * 3 / 100;
// Если есть свободный остаток (перевыполнение), пытаемся закрыть дефициты прошлых периодов
СвободныйОстаток = БалансМесяца;
Для Каждого Дефицит Из МассивДефицитов Цикл
Если СвободныйОстаток <= 0 Тогда
Прервать;
КонецЕсли;
Если Дефицит.ВеличинаДефицита > 0 Тогда
ПокрываемаяЧасть = Мин(СвободныйОстаток, Дефицит.ВеличинаДефицита);
Дефицит.ВеличинаДефицита = Дефицит.ВеличинаДефицита - ПокрываемаяЧасть;
СвободныйОстаток = СвободныйОстаток - ПокрываемаяЧасть;
// Если дефицит закрыт полностью, пересчитываем прошлый месяц по 3%
Если Дефицит.ВеличинаДефицита = 0 Тогда
СтрокаПрошлогоМесяца = Дефицит.СтрокаТаблицы;
СтараяСумма = СтрокаПрошлогоМесяца.СуммаМенеджеру;
НоваяСумма = СтрокаПрошлогоМесяца.Факт * 3 / 100;
СтрокаПрошлогоМесяца.ПроцентМенеджеру = 3;
СтрокаПрошлогоМесяца.СуммаМенеджеру = НоваяСумма;
// Записываем разницу перерасчета в текущий месяц как доплату
СтрокаПериода.КорректировкаПрошлыхПериодов = СтрокаПериода.КорректировкаПрошлыхПериодов + (НоваяСумма - СтараяСумма);
КонецЕсли;
КонецЕсли;
КонецЦикла;
КонецЕсли;
КонецЦикла;
КонецДляКаждого;
После обработки этой таблицы значений мы передаем ее в качестве внешнего набора данных в СКД. Это рабочее, надежное решение, но у него есть существенный недостаток: потеря интерактивности отчета. При использовании внешнего набора данных пользователи не смогут использовать произвольные отборы компоновки по регистрам, добавлять новые группировки "на лету" без изменения программного кода формирования ТЗ.
---
Начиная с версии платформы 8.3.20, разработчикам стал доступен мощный аналитический инструмент — оконные функции (ключевое слово OVER в языке запросов). С их помощью мы можем элегантно решить задачу в рамках одного единственного запроса, избегая написания сложного кода на встроенном языке.
Проанализируем логику решения с использованием оконных функций:
Ниже представлен пример реализации такого запроса:
ВЫБРАТЬ
Продажи.Менеджер КАК Менеджер,
Продажи.Период КАК Период,
Продажи.План КАК План,
Продажи.Факт КАК Факт,
// 1. Рассчитываем накопительный план с начала квартала по текущий месяц
СУММА(Продажи.План) OVER (
РАЗБИТЬ ПО Продажи.Менеджер, НАЧАЛОПЕРИОДА(Продажи.Период, КВАРТАЛ)
УПОРЯДОЧИТЬ ПО Продажи.Период
) КАК ПланНарастающимИтогом,
// 2. Рассчитываем накопительный факт с начала квартала по текущий месяц
СУММА(Продажи.Факт) OVER (
РАЗБИТЬ ПО Продажи.Менеджер, НАЧАЛОПЕРИОДА(Продажи.Период, КВАРТАЛ)
УПОРЯДОЧИТЬ ПО Продажи.Период
) КАК ФактНарастающимИтогом
ПОМЕСТИТЬ ВТ_НакопительныеИтоги
ИЗ
ВТ_ИсходныеДанные КАК Продажи
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Итоги.Менеджер КАК Менеджер,
Итоги.Период КАК Период,
Итоги.План КАК План,
Итоги.Факт КАК Факт,
// Находим максимальную разницу между накопительным фактом и накопительным планом
// среди всех ПОСЛЕДУЮЩИХ месяцев до конца квартала.
// Если эта разница хотя бы в одном из будущих месяцев квартала станет больше или равна 0,
// значит дефицит текущего месяца будет полностью перекрыт в будущем!
МАКСИМУМ(Итоги.ФактНарастающимИтогом - Итоги.ПланНарастающимИтогом) OVER (
РАЗБИТЬ ПО Итоги.Менеджер, НАЧАЛОПЕРИОДА(Итоги.Период, КВАРТАЛ)
УПОРЯДОЧИТЬ ПО Итоги.Период
СТРОКИ МЕЖДУ ТЕКУЩАЯ СТРОКА И БЕЗГРАНИЧНО ПОСЛЕДУЮЩИЕ
) КАК МаксБудущееСальдоКвартала
ПОМЕСТИТЬ ВТ_АнализПокрытия
ИЗ
ВТ_НакопительныеИтоги КАК Итоги
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Анализ.Менеджер КАК Менеджер,
Анализ.Период КАК Период,
Анализ.План КАК План,
Анализ.Факт КАК Факт,
// Если факт превышает план ИЛИ максимальное будущее сальдо положительно — платим 3%
ВЫБОР
КОГДА Анализ.Факт >= Анализ.План ИЛИ Анализ.МаксБудущееСальдоКвартала >= 0
ТОГДА 3
ИНАЧЕ 2
КОНЕЦ КАК ПроцентМенеджеру
ИЗ
ВТ_АнализПокрытия КАК Анализ
Преимущества данного метода: Высочайшая производительность на стороне СУБД, чистота программного кода и сохранение полной интерактивности отчета СКД (пользователи могут без проблем использовать любые стандартные механизмы фильтрации и детализации данных).
---
Если ваша информационная база работает на версии платформы ниже 8.3.20, использовать оконную инструкцию OVER не получится. В этом случае мы можем применить классический прием моделирования оконных функций — левое соединение временной таблицы самой с собой по неравенству периодов.
Рассмотрим схему реализации этой логики:
// Шаг 1: Помещаем исходные данные во временную таблицу ВТ_ИсходныеДанные
// Шаг 2: Расчет нарастающего итога "назад" (от начала квартала до текущего месяца)
ВЫБРАТЬ
Т1.Менеджер КАК Менеджер,
Т1.Период КАК Период,
Т1.План КАК План,
Т1.Факт КАК Факт,
СУММА(Т2.План) КАК ПланНарастающимИтогом,
СУММА(Т2.Факт) КАК ФактНарастающимИтогом
ПОМЕСТИТЬ ВТ_ИтогиСвязанные
ИЗ
ВТ_ИсходныеДанные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК Т2
ПО Т1.Менеджер = Т2.Менеджер
И НАЧАЛОПЕРИОДА(Т1.Период, КВАРТАЛ) = НАЧАЛОПЕРИОДА(Т2.Период, КВАРТАЛ)
И Т1.Период >= Т2.Период // Условие накопления нарастающего итога
ГРУППИРОВАТЬ ПО
Т1.Менеджер,
Т1.Период,
Т1.План,
Т1.Факт
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединение "вперед" для анализа будущих периодов текущего квартала
ВЫБРАТЬ
Анализ1.Менеджер КАК Менеджер,
Анализ1.Период КАК Период,
Анализ1.План КАК План,
Анализ1.Факт КАК Факт,
// Вычисляем максимальный баланс в последующих периодах
МАКСИМУМ(Анализ2.ФактНарастающимИтогом - Анализ2.ПланНарастающимИтогом) КАК МаксБудущееСальдо
ПОМЕСТИТЬ ВТ_РезультатАнализа
ИЗ
ВТ_ИтогиСвязанные КАК Анализ1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИтогиСвязанные КАК Анализ2
ПО Анализ1.Менеджер = Анализ2.Менеджер
И НАЧАЛОПЕРИОДА(Анализ1.Период, КВАРТАЛ) = НАЧАЛОПЕРИОДА(Анализ2.Период, КВАРТАЛ)
И Анализ1.Период <= Анализ2.Период // Анализируем текущую строку и все последующие
ГРУППИРОВАТЬ ПО
Анализ1.Менеджер,
Анализ1.Период,
Анализ1.План,
Анализ1.Факт
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Расчет процентов
ВЫБРАТЬ
Рез.Менеджер КАК Менеджер,
Рез.Период КАК Период,
Рез.План КАК План,
Рез.Факт КАК Факт,
ВЫБОР
КОГДА Рез.Факт >= Рез.План ИЛИ Рез.МаксБудущееСальдо >= 0
ТОГДА 3
ИНАЧЕ 2
КОНЕЦ КАК ПроцентМенеджеру
ИЗ
ВТ_РезультатАнализа КАК Рез
Этот способ несколько уступает первому по производительности на больших объемах данных из-за использования соединений по неравенству (операторы >= и <= нагружают СУБД сильнее, чем равенства). Тем не менее, для квартальных расчетов в разрезе месяцев объемы записей обычно крайне малы, и данный запрос будет отрабатывать практически мгновенно на любых конфигурациях.
---
В системе компоновки данных 1С встроен собственный мощный вычислительный процессор, умеющий рассчитывать сложные итоги по группировкам. Для решения нашей задачи можно использовать встроенную функцию СКД ВычислитьВыражение на вкладке «Вычисляемые поля» или в «Ресурсах».
Разберем, как применить этот механизм:
БалансНарастающимИтогом. В качестве выражения для этого ресурса пишем:
ВычислитьВыражение("Сумма(Факт) - Сумма(План)", "Период", , "Первая", "Текущая")
МаксимальноеБудущееСальдо с выражением:
ВычислитьВыражение("ВычислитьВыражение(""Сумма(Факт) - Сумма(План)"", ""Период"", , ""Первая"", ""Текущая"")", "Период", , "Текущая", "Последняя", "Максимум")
Преимущество этого метода заключается в том, что СУБД вообще не нагружается сложными соединениями — вся математическая логика по расчету скользящих окон выполняется непосредственно на клиенте или сервере приложений 1С средствами самого процессора вывода СКД.
---
Утверждение о том, что сложные алгоритмы перерасчета невозможно реализовать в запросах СКД, — ошибочно. Мы рассмотрели три способа обхода этого ограничения без жесткого кодирования бизнес-логики в модулях документов или отчетов: