×
Меню
Поиск
 
   
   
   
 
Битрикс24.Отчеты - AmoCRM.Отчеты
(c) Nemind, 2022
Описание работы в SQL
 
Для работы с Битрикс24 и amoCRM Nemind использует промежуточную базу SQLite для хранения данных на жёстком диске компьютера пользователя.
Вы можете напрямую открыть эту базу, например, в бесплатном приложении DB Browser for SQLite.
Благодаря языку SQL, можно настроить практически любую логику запросов к данным.
 
Где отлаживать SQL?
 
Для этого можно воспользоваться либо встроенным редактором SQL от Nemind (как подключить, смотрите в разделе "Включить SQL редактор" ), либо DB Browser for SQLite или иным средством работы с SQL.  При работе с любыми редакторами, кроме Nemind, следует обращать внимание на особенности хранения SQL в файлах .npt:
 
 
Особенности SQL от Nemind, встроенные функции и атрибуты
 
Использование спецсимволов больше > и меньше <
Так как данные символы являются зарезервированными для XML языка,  в котором хранятся .npt проекты, то в них данные символы заменяются по стандартам xml:
< меняется на &lt;
> меняется на &gt;
пример:
Tasks.closedDate &gt; Tasks.DEADLINE
Это в SQL будет Tasks.closedDate > Tasks.DEADLINE
В этом случае, при отладке SQL и замене его в npt файле, необходимо в файл писать спецсимволы &lt; или  &gt;  , а в SQL отладчиках - < или >.
 
Встроенные функции Nemind
Для ряда особых задач мы разработали внутренние функции, которые не поддерживаются напрямую внешними SQL редакторами, такими как DB Browser for SQLite, но вы можете их использовать внутри Nemind в .npt файлах.
DurationWorkTime
функция расчёта периода с учётом рабочих часов.
Важно:  если второй параметр пуст, то будет считаться время до момента вызова функции, то есть до “сейчас”.
Пример:
     CASE WHEN Tasks.DEADLINE is not null
             THEN
    DurationWorkTime(DateTime(Tasks.createdDate), DateTime(Tasks.DEADLINE))
END
      AS [Planned working hours #ftDurationHms],    
 
В вышеуказанном примере показано, как вернуть пустоту, если второй параметр пуст - через конструкцию CASE.
 
Translate
функция перевода данных через методы перевода Nemind.
пример:
 
       Translate(Statuses.Phase) AS [Stage group],
Здесь мы переводим группу стадий на русский язык, сами переводы описаны в Русский.txt в [SQL] сегменте:
[SQL]
Open=Открыто
Lost=Провалено
Won=Успешно
Outgoing=Исходящий
Incoming=Входящий
Deleted status=Удалённый статус
Deleted user=Удалённый пользователь
 
StripTags
Вырезает html коды, например
<div><b>текст</b></div>
превратит в
текст
 
Функция применяется обычно для описаний дел в Битрикс24, так как они храняться там с html-форматированием, например, из e-mail.
пример
      StripTags(Activities.DESCRIPTION) AS [Description],
 
 
Атрибуты в именах заголовков Nemind
Атрибуты подставляются в конец заголовка, через пробел, например:
[Duration #ftDuration]
заголовок будет Duration (точнее, “Продолжительность” если выбран русский язык), и применится тип Nemind #ftDuration. Перечислим атрибуты
 
#ftDuration     -  результат в секундах будет переведён в общий формат продолжительности: годы - месяцы - дни - часы - минуты - секунды. Часто используется вместе с встроенной функцией DurationWorkTime Примеры в SQL
       CAST(CASE WHEN Leads.DATE_CLOSED is not null
             THEN
               strftime('%s', Leads.DATE_CLOSED) - strftime('%s', Leads.DATE_CREATE)
             ELSE
               strftime('%s', 'now') - strftime('%s', Leads.DATE_CREATE)
          END AS integer) AS [Duration #ftDuration],
Здесь считается время лида  либо от начала до завершения, либо до момента расчёта.
       DurationWorkTime(DateTime(Activities.START_TIME), DateTime(Activities.END_TIME)) AS [Working hours #ftDuration]
Здесь считается время дела с учётом рабочих часов
 
#ftDurationHms
       DurationWorkTime(DateTime(Activities.START_TIME), DateTime(Activities.END_TIME)) AS [Working hours #ftDurationHms]
Атрибут #ftDurationHms переведёт секунды в формат часы - минуты - секунды. В отличие от #ftDuration, программа не покажет дни и месяцы, она завершит округление до часов. Это удобно, к примеру, если нужно рассчитать количество потраченных человеко-часов, где их могут быть тысячи.
 
#ftBoolean
Да-нет переменная, будет выведена в виде галочки.
пример: CAST(Tasks.parentId IS NOT NULL as boolean)  AS [Subtask #ftBoolean],
здесь мы проверяем наличие у задачи ссылки на родительскую задачу и выводим в виде булен-значения с галочками.
 
#ftStages
Статус или стадия  будут показаны с учётом их сортировки и завершённости (в amocrm и с учётом цвета). Пример:
       Statuses.NAME AS [Status #ftStages],
 
Как рассчитать продолжительность?
Это одна из наиболее частых задач, для которых применяется SQL.
Посмотрите на примерах:
strftime('%s', Leads.DATE_CLOSED) - strftime('%s', Leads.DATE_CREATE)
это расчёт времени между созданием и завершением лида (обратите внимание, на особенности наименования полей в Вашей CRM).
Создадим для него поле:
 
       CAST(CASE WHEN Leads.DATE_CLOSED is not null
             THEN
               strftime('%s', Leads.DATE_CLOSED) - strftime('%s', Leads.DATE_CREATE)
             ELSE
               strftime('%s', 'now') - strftime('%s', Leads.DATE_CREATE)
          END AS integer) AS [Duration #ftDuration],
Выше мы сделали проверки на заполненность поля, два вида расчётов и указали в конец атрибут #ftDuration
 
Время между созданием лида и текущим моментом:
 strftime('%s', 'now') - strftime('%s', Leads.DATE_CREATE)
 
 
Добавление данных из двух таблиц в одну, дублирование данных одной строки (изменение ответственного) в две (сдал / принял):
-
необходимо прописать в комментарии sql / EventsToActivities /
 
 
#ftDouble
 
Переводит в формат числа. Если Вы создаёте новое вычисляемое поле в SQL, то не всегда Nemind получает информацию о том, что это число и иногда воспринимает как строку, в этом случае добавление "ftDouble" помогает точно определить тип.
например
        case when strftime('%m', Invoices.DATE_PAY_BEFORE) = '06' then  Invoices.PRICE end as [Июнь #ftDouble],
 
В примере указывается в поле "Июнь" сумма запланированной оплаты счёта Битрикс24