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

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

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

• select - выборка данных. В списке полей можно указать * - все поля, или список полей через запятую, например ID, Phone и т.д. При использовании * есть особенность, возвращаются не все поля выбранной таблицы, не возвращаются обьектные поля, например, поле номера телефона Phone, его нужно указывать явно. Выражения не поддерживаются.
  • where - позволяет указать условие выборки. Первым операндом должно быть имя поля указанной таблицы, оператор сравнения может быть like, >=, <=, =, >, <, вторым операндом должна быть константа. Выражения не поддерживаются.
  • 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-запросы к этому источнику данных могут содержать комментарии в виде /* */ и содержать несколько запросов разделенных символами // на новой строке.

Непосредственный вызов методов 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 () {

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

    return null;
 }
 !/

Описание функций 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-формате.

Пример SQL запроса

Например, для получения номеров телефонов всех лидов со статусом "В работе" пишем: select PHONE from crm.lead where STATUS_ID = 'IN_PROCESS'

---

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