SQL запросы (версия 3.xx)/Bitrix24

Описание SQL-запросов

Для работы с данными CRM Bitrix24 можно использовать стандартные SQL запросы, которые буду преобразованы в формат REST API Bitrix24. Поддерживаются SQL - операторы:

• select - выборка данных. В списке полей можно указать * - все поля, или список полей через запятую, например ID, Phone и т.д. При использовании * есть особенность, возвращаются не все поля выбранной таблицы, не возвращаются объектные поля, например, поле номера телефона Phone, его нужно указывать явно. Выражения не поддерживаются. Если поле объектное и содержит несколько значений, его значения объединяются в строку через запятую.
  • where - позволяет указать условие выборки. Первым операндом должно быть имя поля указанной таблицы, оператор сравнения может быть like, >=, <=, =, >, <, !=, <> вторым операндом должна быть константа. Выражения не поддерживаются. Операторы сравнения работают как указано здесь. Специфика работы операторов:
    • like/not like - выполняет регистронезависимый поиск подстроки (используется фильтр %/!%).
    • is null/is not null - выполняет пустых или не пустых значений (используется фильтр с значением false). Не работает для объектных полей, например, PHONE.
    • in - выполняет поиск с с использованием сложной логики (используется фильтр с значением ?). Например: `UF_Статус обзвона` in (номер занят || перезвонить).
  • order by - позволяет указать порядок сортировки результатов в формате <имя поля> [asc|desc]. Выражения не поддерживаются. Например: order by Id desc
  • limit - позволяет указать начальное значение выборки и количество строк. Например: limit 50 или limit 10, 50
• insert - вставка данных, позволяет указать список полей и константы значений, выражения не поддерживаются.
• update - изменение данных, позволяет указать список изменяемых полей и единственное условие отбора: where id =.
• delete - удаление данных, позволяет единственное условие отбора: where id =.

Помимо стандартного синтаксиса SQL, в запросах к источнику данных Bitrix24 можно использовать вставки родного синтаксиса REST API Bitrix24. Для этого используется конструкция /! !/. Такую возможность можно использовать в операторах:

• where - для указания условий выборки, например: where /! {"=ID": 4} !/. Полученное значение будет поставлено в параметр filter REST API Bitrix24. Подробное описание фильтров REST API Bitrix24 можно посмотреть здесь.
• order by - для указания порядка сортировки, например: order by /! {"DATE_CREATE": "ASC"} !/. Полученное значение будет поставлено в параметр order REST API Bitrix24.
• limit - для указания смещения выборки и количества строк, например: limit /! {limit: 5, offset: 10} !/. Полученные значения будет поставлено в параметры limit и offset REST API Bitrix24.

Помимо этого, SQL-запросы к этому источнику данных могут содержать комментарии в виде /* */ и содержать несколько запросов разделенных символами // на новой строке.

Если применить к пользовательскому полю типа "Дата/Время" now (без кавычек), то поставиться текущие дата - время. Пример: `UF_Время обзвона` = now

Важно: Получение данных из Bitrix

Обратите внимание, при получении данных из Bitrix, API не отдаёт данные пустых пользовательских полей, поэтому, если планируете использовать их в сценарии, при макроподстановке в звуковом сообщении или для последующей записи, ВСЕГДА объявляйте ВЕСЬ список ожидаймых полей. Значения полей, которые не были получены, не будут сохранены в переменные сценария [Выборка.Имя поля], а значит не сработает автозамена макроподстановок. Например:

var UF_Status = '';
if (Script.GetVariable ('Выборка.UF_Status') != undefined) UF_Status = Script.GetVariable ('Выборка.UF_Status');
Script.AddVariable ('UF_Status', UF_Status);

Непосредственный вызов методов REST API Bitrix24

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

/! {Method: <название метода>, Params: <параметры метода>} !/

Например:

/! {Method: 'crm.contact.list', 
    Params: { 
               order: { "DATE_CREATE": "ASC" },
               filter: { "TYPE_ID": "CLIENT" },
               select: [ "ID", "NAME", "LAST_NAME", "TYPE_ID", "SOURCE_ID" ]
            }
   } 
!/

Подробное описание формата вызова родных методов REST API Bitrix24 можно посмотреть здесь.

Помимо этого можно использовать функцию, которая вернет нужный объект, например, такого вида:

/!
function () {
   return {Method: 'crm.contact.list', 
           Params: { 
                      order: { "DATE_CREATE": "ASC" },
                      filter: { "TYPE_ID": "CLIENT" },
                      select: [ "ID", "NAME", "LAST_NAME", "TYPE_ID", "SOURCE_ID" ]
                   }
          };
}
!/

Использование функции для вызова методов REST API Bitrix24

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

/!
function () {
   return this.Query ('select * from crm.contact');
} 
!/

Более сложный вариант функции с вызовом метода REST API Bitrix24 и формированием результата:

/!
function () {
   var Res = this.CallMethod ('crm.contact.list', {}, true);
   return this.MakeResult (this.Select ('crm.contact.list', {}, Res), 'crm.contact');
}
!/

Если функция не возвращает записи, например в результате изменения или удаления данных, она должна вернуть значение null:

/!
function () {
   var Res = this.CallMethod ('crm.contact.add', {...});
   return null;
}
!/

Если необходимо выбрать только контакты, для которых есть сделка с именем "Для звонка через Call Office":

/!
function () {
 var ResultContact = this.Query ('select * from crm.contact');
 var ResultDeal = this.Query ('select * from crm.deal where TITLE = "Для звонка через Call Office"');
 return ResultContact.Join (ResultDeal, 'ID', 'CONTACT_ID');
}
!/

Регистрация звонков в Мои дела

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

• В таблице Мои дела нажать "Настроить список" -> "Колонки списка" -> "Описание"
• В разделе Чтение данных выбрать SQL-запрос и добавить следующий текст, который выбирает клиентов из таблицы Контакты, которым программа не дозвонилась или еще ни разу не звонила:

/!
 function () {

    var Activity = this.CallMethod ('crm.activity.list', {filter:{"=%DESCRIPTION": "OK%"},select: ["*"]}); // Выбор звонков, у которых в описании есть OK

    var Fields = this.CallMethod ('crm.contact.fields'); // Выбор списка полей из таблицы Контакты

    var FieldsList = [];
    for (var i in Fields) FieldsList.push (i); // Построение списка полей таблицы Контакты

    var Contacts =  this.CallMethod ('crm.contact.list', {select: FieldsList}, true); // Выбор всех контактов из таблицы Контакты

    Contacts = this.Select ('crm.contact.list', {}, Contacts); // Построение списка контактов

    for (var i in Activity) {
       for (var j in Contacts) {
          if (Activity[i].OWNER_ID == Contacts[j].ID) { // Если найден успешный звонок клиенту, который есть в таблице Контакты
             delete Contacts[j]; // то убираем его из списка
             break;
          }
       }
    }

    return this.MakeResult (Contacts, 'crm.contact'); // Возвращаем объект из контактов, которым не звонили или не дозвонились
 }
 !/

• В разделе Запись данных добавить следующий текст, который записывает звонок с названием "Звонок через Call Office" и его результат в таблицу Мои дела:

/!

 function () {

    function GetDate (Days)  // Получаем текущие время и дату и приводим их стандартному для Bitrix формату
	{
	   var paddatepart = function (part) {return part >= 10 ? part.toString() : '0' + part.toString();}

	   var d = new Date ();
	   d.setDate (d.getDate () + Days);
	   d.setSeconds (0); 

	   return d.getFullYear () + '-' + 
			  paddatepart (1 + d.getMonth ()) + '-' + 
			  paddatepart (d.getDate ()) + 'T' + 
			  paddatepart (d.getHours ()) + ':' + 
			  paddatepart (d.getMinutes ()) + ':' + 
			  paddatepart (d.getSeconds ()) + '+03:00';  

	}

    this.CallMethod ('crm.activity.add',{
             fields:{"TYPE_ID": 2, // 1 - Встреча, 2 - Звонок, 3 - Задача*
               "OWNER_TYPE_ID": 1, // 1 - Лид, 2 - Сделка, 3 - Контакт, 4 - Компания*
               "OWNER_ID": Script.GetVariable ('Выборка.ID') , // ID абонента
               "COMMUNICATIONS": [  { VALUE:  Script.GetVariable ('Выборка.PHONE')}  ], // Телефон абонента Script.GetVariable ('Выборка.PHONE')
               "SUBJECT": "Перезвонить", // Название звонка
	       "START_TIME": GetDate (0), // Дата назначения дела (текущее время + 0)
               "END_TIME": GetDate (7),  // Срок выполнения (текущая дата + 7 дней)
               "DIRECTION": 1, // 1 - Входящее, 2 - Исходящее
               "DESCRIPTION": "Нажата клавиша [Клавиши]", // Статус звонка (Его описание) + стандартные переменные Call Office, например, использующиеся при распознавании DTMF
               "COMPLETED": "N" // Y - выполнено, N - невыполнено
                 }
         });

    return null;
 }
 
 !/

\* получить актуальный набор параметров можно сделав запрос

/! {Method: 'crm.enum.activitytype', Params: { } } !/

Описание функций Call Office для работы с Bitrix24

• Функция CallMethod отправляет запрос на API Bitrix24 и получает результаты выполнения запроса (Не более 50 записей из-за особенности API Bitrix24).

Передаваемые параметры:

1. Метод передачи запроса. Пример: 'crm.contact.list'.
2. Параметры для передаваемого запроса. Параметры соответствуют допустимым для метода, которые можно найти в документации для каждого метода. Пример: {filter:{"PHONE": "555888"},select: ["*"]}.
3. Флаг возврата полного ответа сервера. Если true - будет возвращен полный ответ от сервера. Если false - будет возвращен результат ответа от сервера.

Возвращаемые данные: полный ответ или результат ответа от сервера в json-формате.

• Функция MakeResult строит таблицу на основе полученных данных от сервера в json-формате.

Передаваемые параметры:

1. Данные в json-формате.
2. Таблица, из которой получены данные. Используется для преобразования пользовательских полей в привычный вид, то есть вместо UF_CRM_1234565 подставляет UF_[НазваниеСтолбца] (string, необязательно).

Возвращаемые данные: объект данных (Recordset).

• Функция Select аналогична функции CallMethod, но получает все или определенное количество записей.

Передаваемые параметры:

1. Метод передачи запроса. Пример: 'crm.contact.list'.
2. Параметры для передаваемого запроса. Параметры соответствуют допустимым для метода, которые можно найти в документации для каждого метода. Пример: {filter:{"PHONE": "555888"},select: ["*"]}.
3. Результат запроса при помощи функции CallMethod с такими же параметрами и установленным флагом возврата полного результата ответа от сервера.

Возвращаемые данные: результат ответа от сервера в json-формате.

Примеры запросов при работе с пользовательскими полями

Работа с пользовательскими полями используя API Bitrix24 имеет несколько особенностей:

• Название полей имеет вид UF_CRM_<большое число>, например: UF_CRM_1524132067. Получить этот номер можно по инструкции здесь: https://www.youtube.com/watch?v=UokPByYMjNM
• В SQL запросах можно использовать реальные название полей в виде UF_<имя поля>, например: UF_Статус обзвона. В этом случае преобразование к нужному виду будет выполнено автоматически.
• Поля полученные в результате запроса на выборку данных автоматически преобразуются из представления Bitrix24 (UF_CRM_<большое число>) к удобному UF_<имя поля>.
• Если все записи пользовательского поля пустые, запрос на выборку это поле не вернет вообще. Нужно ввести хотя бы одно значение в это поле для получения его в выборке.
• Условия фильтрации данных в API Bitrix24 имеют свои особенности, поэтому сложные условия работать не будут. Для указания сложных условий можно использовать родной синтаксис API Bitrix24 или фильтровать при помощи js кода как указано ниже.

Простой запрос на получение номеров телефонов всех лидов со статусом "В работе" может выглядеть так:

select PHONE from crm.lead where STATUS_ID = 'IN_PROCESS'

Запрос на запись в пользовательское поле с использованием переменных Call Office

update crm.lead set `UF_Статус обзвона` = "Нажата клавиша [Клавиши]" where id = [Выборка.ID]

Более сложный запрос с фильтрацией данных на стороне клиента может выглядеть так:

/!
function () {

    var Data = {filter:{"STATUS_ID": 'IN_PROCESS'}, select:['Phone', 'Name', 'UF_CRM_1524132067']};   // Параметры выборки, которые может обработать Bitrix

    var Leads =  this.CallMethod ('crm.lead.list', Data, true);   
    Leads = this.Select ('crm.lead.list', Data, Leads);  // Формируем массив из выборки по "Статус Лида"
 
    for (var i in Leads) {  // Запускаем цикл фильтрации массива по "Статус обзвона"

       var Value = Leads[i].UF_CRM_1524132067;

       if (Value != null) {  // Игнорируем пустые поля
          if (Value.indexOf ('Нажата клавиша') !== -1) delete Leads[i]; // Удаляем строки если поле "UF_CRM_1524132067" содержит 'Нажата клавиша'
       }
    }

    return this.MakeResult (Leads, 'crm.lead');  // Возвращаем массив строк
}
 !/

Этот запрос получает поля Phone, Name, UF_CRM_1524132067 (пользовательское поле) всех лидов у которых содержимое поля STATUS_ID равно IN_PROCESS (Статус Лида "В работе"), а содержимое поля UF_CRM_1524132067 содержит текст "Нажата клавиша".

Здесь учтены сразу две особенности Битрикс:

• При запросе на чтение к пользовательскому полю вы должны использовать не имя (UF_Статус обзвона), а его ID (UF_CRM_1524132067).
• Битрикс не возвращает строки по условию "пустое поле". Например, если вам надо получить все записи, в которых поле UF_CRM_1524132067 равно '2' или пустое (NULL). Придётся получить все поля, а затем отфильтровать их на своей стороне.

Регистрация звонков в Битрикс24 (Коллтрекинг)

Регистрацию звонков в Битрикс24 можно производить с помощью метода 'telephony.externalcall.register'. Подробное описание параметров метода можно посмотреть здесь.

Пример регистрации звонка:

/!

function () { 

   function GetDate (Days)  // Получаем текущие время и дату и приводим их стандартному для Bitrix формату
   {
      var paddatepart = function (part) {return part >= 10 ? part.toString() : '0' + part.toString();}

      var d = new Date ();
      d.setDate (d.getDate () + Days);
      d.setSeconds (0); 

      return d.getFullYear () + '-' + 
             paddatepart (1 + d.getMonth ()) + '-' + 
             paddatepart (d.getDate ()) + 'T' + 
	     paddatepart (d.getHours ()) + ':' + 
	     paddatepart (d.getMinutes ()) + ':' + 
	     paddatepart (d.getSeconds ()) + '+03:00';     // Часовой пояс

   }

   return {Method: 'telephony.externalcall.register',
           Params:{"USER_PHONE_INNER": 1,                  // Внутренний номер пользователя.
                            "USER_ID": [Выборка.ID],       // Идентификатор пользователя.
                       "PHONE_NUMBER": [Выборка.PHONE]),   // Номер телефона.
                    "CALL_START_DATE": GetDate(),          // Дата/время звонка в формате iso8601.
                         "CRM_CREATE": 1,                  // [0/1] Создавать или нет новый лид (если номер не найден в CRM).
                         "CRM_SOURCE": "CallOffice",       // STATUS_ID источника из справочника источников. 
                    "CRM_ENTITY_TYPE": "LEAD",             // Тип объекта CRM, из карточки которого совершается звонок - CONTACT | COMPANY | LEAD
                               "SHOW": 1,                  // Показывать ли карточку звонка (по умолчанию 1).
                              "TYPE" : 1                   // Тип звонка: 1 - исходящий, 2 - входящий, 3 - входящий с перенаправлением, 4 - обратный                            
                  }
           };
}

!/

---

Назад к SQL-запросам.