Для работы с математическими вычислениями в программе Руководитель предусмотрено два типа полей: “MySql Формула” и “JS Формула”. По аналогии с ячейкой таблицы Excel, в этом поле будет отображаться результат заданных арифметических действий. При создании такого поля, вы указываете поля, значения которых необходимо использовать, и вычисления, которые нужно произвести.

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

Простые вычисления

([36]+[54])/2

где 36 и 54 — идентификаторы числовых полей.

Математические функции

В формуле мы можем применять математические функции MySql. К примеру, нам нужно округлить результат вычисления до целого числа:

FLOOR(([36]+[54])/2)

Расчет по формуле с условием

Для добавления условия в формулу нужно использовать оператор IF

if(expr1,expr2,expr3)

Если expr1 равно значению ИСТИНА (expr1 <> 0 и expr1 <> NULL), то функция IF() возвращает expr2, в противном случае — expr3. В зависимости от контекста, функция IF() может возвращать либо числовое, либо строковое значение:

if([36]>0,([36]+[54])/2,0)

В данном примере, если значение поля 36 больше 0, то мы производим расчеты, в противном случае, возвращаем ноль.

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

if([36]>0,([36]+[54])/2,'нет данных')

Проверка списка в операторе IF

Очень часто возникает необходимость производить расчеты только в том случае, если, к примеру, поле «Статус проекта» имеет тип поля «Раскрывающийся список» и значение «Закрыт». В таком случае, условие будет следующее:

if([11]=15,([36]+[54])/2,0)

где 11 — идентификатор поля «Статус», а 15 — это идентификатор значения «Закрыт».

Теперь давайте рассмотрим пример, когда нам нужно проверить несколько значений из списка:

if([11] in (15,16,17),([36]+[54])/2,0)

где 15,16,17 — это идентификаторы значений списка.

Для проверки значения из логического списка используйте ‘true’ или ‘false’, например:

if([20]='true',([36]+[54])/2,0)

где 20 — идентификатор поля «Логическое поле»

Проверка списка в операторе CASE

Оператор CASE — это аналог оператора switch в PHP. Его конструкция выглядит следующим образом:

case  
   when [11] =15 then ([36]+[54])/2) 
   when [11] =16 then ([36]+[54])/3)   
end

Расчет разницы времени

Очень часто возникает необходимость создать расчет разницы времени между полями с временем начала и окончания проекта/задачи. Так как даты хранятся в базе данных в секундах, то вычисление количества дней будет следующим:

([300]-[159])/86400

где 300 — идентификатор поля «Дата завершения», 159 — идентификатор поля «Дата начала», 86400 — количество секунд в сутках.

Но так как дата может быть введена некорректно или отсутствовать вовсе, мы должны выполнить проверку перед вычислением. Корректная формула будет следующая:

if([300]>0 and [159]>0 and [300]>[159],([300]-[159])/86400,0)

Примечание: вы можете использовать [TODAY] для текущей даты.

Вывести формат даты используя FROM_UNIXTIME

FROM_UNIXTIME([12],'%Y-%m-%d')

где 12 — это идентификатор для поля даты

Работа с функциями даты

В поле с типом MySQL формула можно выполнять расчеты и вывод данных, используя серверные функции для дат. Например, можно получить число дня месяца из указанной даты в поле с типом Дата. Если в поле с типом Дата [10] указано значение «21.12.2018», то выражение в поле с типом MySQL формула

DAYOFMONTH(FROM_UNIXTIME([10],'%Y-%m-%d'))

отразит результат функции в виде числа «21», т.е. укажет число месяца, которое можно использовать в дальнейших вычислениях. Также, используя данный формат, можно выводить любые другие значения функций даты. Более подробно с функциями даты в MySQL можно ознакомиться тут https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Объединение нескольких полей

С помощью функции CONCAT вы сможете объединить несколько полей в строку.

CONCAT([11],' - ',[36],' - ',id)

MySQL запрос

В поле MySQL формула можно встраивать собственный MySQL запрос. Например следующий запрос отобразит email пользователя, который создал запись.

SELECT app_entity_1.field_9 FROM app_entity_1 WHERE app_entity_1.id = e.created_by

Внутренние переменные

[TODAY] - текущая дата в UNIXTIME.
[id] - id текущей записи.
[date_added] - дата добавления записи UNIXTIME.
[created_by] - id пользователя, который создал запись.
[parent_item_id] - id родительской записи.
[current_user_id] - id текущего пользователя в системе.

get_value()

Для полей типа Список, вы можете устанавливать значения каждой опции, и с помощью встроенной функции get_value() использовать эти значения в полях формулы.

[36]+[54]+get_value([15])

где 15 — идентификатор поля типа Список. Если поле 15 не является обязательным и может быть пустым, в математических выражениях необходимо проверять проверку на NULL.

[36]+[54]+IFNULL(get_value([15]),0)

parent_entity_item_value()

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

parent_entity_item_value(22,173)

где 22 - идентификатор родительской сущности, 173 - идентификатор поля в родительской сущности.

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

Также эта функция создает несколько вложенных SQL-запросов, которые нагружают сервер. Если вам нужно получить несколько значений из родительского элемента, лучше сохранить их в поле «статический текстовый шаблон» и выбрать только одно поле, чем использовать функцию parent_entity_item_value () несколько раз.

entity_item_value()^{Новая функция в 3.0}

Используйте эту функцию для получения значения из связанной сущности по полю "Сущность".

entity_item_value(13,17)

13 - тип поля сущность, 17 - id поля, значение которого нужно использовать в формуле.

Функции

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

{12}+[5]

где 12 — это идентификатор функции, 5 идентификатор числового поля.

Следующий пример позволит вам выполнить вычисление с несколькими функциями:

{12}+{13}

Так же функции можно применять в условиях:

if([5]>0,{12},0)

if({1}={2},'<span color="green">на складе</span>','нет')

Работа с значениями NULL

При отсутствии записей в сущности функция будет возвращать NULL. При работе с NULL действуют особые правила, которые не дают возможность использовать NULL в математических выражения. В связи с этим необходимо выполнять проверку IFNULL

IFNULL({12},0)+[5]

MySQL Формула