Агрегация данных для формульных проверок

Агрегация данных для формульных проверок#

Механизм агрегации данных позволяет передавать в формульную проверку значения, рассчитанные по набору записей. Например, можно получить сумму проводок за период, количество записей, среднее значение или сумму отдельно по каждому товару или МВЗ.

Для таких расчетов используется источник агрегации Bts_FcAggregationSource. Он задает, из какого класса получать данные и какие значения можно использовать для расчета и группировки. На основании настроек источника и аргумента формульной проверки система формирует агрегирующий SQL-запрос и получает необходимые значения.

Агрегация источника данных используется как один из способов получения значений аргументов проверок на основе формул.

Настройка источников данных для агрегации#

Источник данных для агрегации задает класс, из которого система получает данные, и набор значений, доступных для расчета и группировки.

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

Для источника задаются:

  • Код — мнемокод источника;

  • Наименование — наименование источника;

  • Описание — описание источника;

  • Класс источника данных — класс, данные которого используются для расчета;

  • Программа по умолчанию — программа расчета, которая переносится в аргумент формульной проверки, если в самом аргументе программа еще не задана.

Также в карточке доступны служебные поля Не используется и Дата окончания использования.

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

Например, если указан класс Act_TransReg, агрегация выполняется по данным соответствующей ему таблицы.

Атрибуты агрегации#

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

Атрибуты источника описываются объектами Bts_FcAggregationSourceAttr и настраиваются на вкладке Атрибуты агрегации в карточке источника данных для агрегации.

Для атрибута задаются:

  • Код — код атрибута и алиас рассчитанного значения;

  • Наименование — наименование атрибута;

  • SQL выражение — колонка или SQL-выражение, используемое при формировании запроса;

  • Тип данных — тип значения атрибута;

  • Тип ссылочности — определяет, является ли значение ссылочным;

  • Класс источника данных — для ссылочного значения указывает класс объекта, на который оно ссылается;

  • Результат — разрешает использовать атрибут как значение для агрегации;

  • Группировка — разрешает использовать атрибут как поле группировки.

Чтобы добавить атрибут класса в источник агрегации:

  1. Откройте карточку источника данных для агрегации.

  2. Перейдите на вкладку Атрибуты агрегации.

  3. Выполните операцию Создать записи из источника.

  4. Выберите необходимые атрибуты класса.

  5. Для созданных атрибутов укажите способ использования:

    • Результат — атрибут доступен как значение для агрегации;

    • Группировка — атрибут доступен как поле группировки.

В формируемом SQL-запросе таблица источника используется с алиасом t.

Для атрибута, созданного с помощью операции Создать записи из источника, SQL-выражение формируется автоматически:

t.<системное имя атрибута>

Также можно задать собственное вычисляемое SQL-выражение. Например:

(1 - 2 * t.bCredit) * t.nSumBase

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

Зарегистрированные атрибуты используются при настройке аргумента формульной проверки: атрибуты с признаком Результат доступны для выбора рассчитываемого значения, а атрибуты с признаком Группировка — для группировки результатов.

Использование источника в формульной проверке#

Аргументы формульных проверок настраиваются через выборку Bts_FcArgument.

Для агрегации создается аргумент с типом объекта Агрегация источника данных. Для него задаются:

  • источник данных;

  • функция агрегации;

  • атрибут результата;

  • один или несколько атрибутов группировки;

  • программа расчета;

  • вариант параметров программы.

Доступны следующие функции агрегации:

  • SUM — сумма;

  • COUNT — количество;

  • MIN — минимальное значение;

  • MAX — максимальное значение;

  • AVG — среднее значение.

Атрибут результата выбирается среди атрибутов текущего источника с признаком Результат.

Атрибуты группировки выбираются среди атрибутов того же источника с признаком Группировка. Можно выбрать несколько атрибутов группировки.

После выбора источника становятся доступны поля функции агрегации, результата и группировки.

Если для источника указана программа по умолчанию, она переносится в аргумент, если программа аргумента еще не заполнена. После выбора программы становится доступен вариант ее параметров.

Выполнение расчета#

На основании настроек аргумента система формирует агрегирующий SQL-запрос. В него могут входить:

  • агрегирующее выражение в SELECT;

  • поля группировки;

  • таблица источника;

  • дополнительное условие WHERE;

  • секция GROUP BY.

Например:

SELECT
  SUM(t.nSum) AS nSum,
  t.idCostCenter AS grp_key_0,
  t.idGds AS grp_key_1
FROM Act_TransReg t
WHERE ...
GROUP BY t.idCostCenter, t.idGds

В этом примере рассчитывается сумма nSum отдельно для каждой комбинации центра затрат и товара.

Если группировка не задана, система рассчитывает одно агрегированное значение для всего набора данных и использует для результата ключ NO_GROUP.

При настроенной группировке отдельный результат формируется для каждой комбинации значений выбранных атрибутов. Значения включаются в ключ в порядке настройки атрибутов и соединяются дефисом.

Примеры:

Значения группировки

Ключ

Группировка отсутствует

NO_GROUP

123

123

280877, 456

280877-456

280877, NULL

280877-NULL

Результаты нескольких аргументов одной формульной проверки объединяются по одинаковым ключам группировки. Для каждого ключа формируется набор параметров:

код аргумента -> рассчитанное значение

Полученный набор передается в JEXL-формулу. Если формула хотя бы для одного обрабатываемого ключа возвращает отрицательный результат, общий результат проверки устанавливается в 0. Если все обрабатываемые группы успешно проходят проверку, результат устанавливается в 1.

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

Порядок атрибутов составной группировки имеет значение. Аргументы одной проверки, результаты которых должны объединяться, должны использовать одинаковый состав и порядок атрибутов группировки.

Программное использование#

Для выполнения агрегации из прикладного кода используется Bts_FcArgumentPkg.

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

Bts_FcArgumentPkg()
  .forArgument(idArgument)
  .withWhere("t.idDepOwner = 123")
  .withTable("Act_TransReg")
  .execute()

В примере:

  • forArgument(idArgument) задает аргумент формульной проверки;

  • withWhere(...) добавляет условие отбора без ключевого слова WHERE;

  • withTable(...) задает таблицу источника вместо таблицы, определенной по классу источника;

  • execute() формирует и выполняет SQL-запрос и возвращает Map[String, ArgumentResultRow].

ArgumentResultRow содержит:

  • argCode — код аргумента;

  • key — ключ группировки;

  • keyValues — исходные значения атрибутов группировки;

  • value — рассчитанное значение.

Без группировки результат формируется с ключом NO_GROUP. При использовании группировки отдельная запись формируется для каждого полученного ключа.

Для получения сформированных параметров расчета и SQL используется buildOnly():

Bts_FcArgumentPkg()
  .forArgument(idArgument)
  .withWhere("t.idDepOwner = 123")
  .withTable("Act_TransReg")
  .buildOnly()

buildOnly() возвращает FcArgument и SqlParts.

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

Bts_FcArgumentApi.calcAggregation(idArgument, where)

Метод использует тот же механизм, что и Bts_FcArgumentPkg.

Bts_FcArgumentPkg используется, когда требуется дополнительно:

  • задать другую таблицу источника с помощью withTable(...);

  • получить сформированные параметры расчета и SQL с помощью buildOnly().

Программная регистрация источников и атрибутов#

Источник агрегации можно зарегистрировать программно с помощью Bts_FcAggregationSourceApi.register.

Метод ищет источник по полю Код (sCode). Если запись с указанным кодом отсутствует, метод создает новый источник. После создания или поиска обновляются его наименование, описание, класс источника данных и программа по умолчанию.

Для регистрации атрибутов используется Bts_FcAggregationSourceAttrApi.register. Метод ищет атрибут по источнику и коду. После создания или поиска обновляются SQL-выражение, типы, ссылочный класс и признаки использования в качестве результата или группировки.

Пример регистрации источника Act_TransReg и результатного атрибута nSum:

val idFcSource = Bts_FcAggregationSourceApi().register(
  spCode = "Act_TransReg",
  spCaption = "Регистр проводок",
  idpRefClass = Act_TransRegApi().idClass
)

// Результатный атрибут
Bts_FcAggregationSourceAttrApi().register(
  idpFcAggregationSource = idFcSource.idJ,
  spCode = "nSum",
  spCaption = "Сумма",
  spColumnClause = "t.nSum",
  idpAttributeDbType = Btk_AttributeDbTypeApic().idNumber,
  idpAttributeRefType = Btk_AttributeRefTypeApic().idBasic,
  bpIsResult = 1.nr
)

В примере источник связан с классом Act_TransReg, а атрибут nSum зарегистрирован как результатный и может использоваться для агрегации.

Метод registerByAttr позволяет зарегистрировать атрибут агрегации на основании существующего Btk_Attribute. Метаданные атрибута копируются автоматически, а SQL-выражение формируется в виде t.<systemName>.

Использование с регистром проводок#

Механизм используется, например, для расчетов по регистру проводок Act_TransReg.

Для регистра настраиваются:

  • источник агрегации;

  • выражения для расчета сумм, сальдо и оборотов;

  • стандартные поля группировки;

  • дополнительные поля группировки на основании активных аналитик.

Аргумент формульной проверки определяет необходимое выражение результата и группировку. Прикладной код дополнительно задает условия отбора по организации, виду учета и периоду.

Период учитывается по-разному в зависимости от рассчитываемого значения:

  • обороты рассчитываются по записям внутри периода;

  • начальное сальдо учитывает записи до начала периода;

  • конечное сальдо учитывает записи до конца периода.

Ограничения#

SQL-выражения атрибутов и условия WHERE непосредственно включаются в формируемый SQL-запрос. Их следует задавать в серверном коде или настройках механизма и не формировать из непроверенных пользовательских данных.

Если для некоторого ключа группировки один из аргументов не сформировал результат, формула получает значения только фактически рассчитанных аргументов.

При расчете по пустому набору данных COUNT возвращает 0, а SUM, AVG, MIN и MAX могут возвращать NULL. Запрос с группировкой по пустому набору данных не возвращает строк результата.

Содержание