SQL запросы (версия 3.xx)/Bitrix24: различия между версиями
Руслан (обсуждение | вклад) |
Руслан (обсуждение | вклад) |
||
Строка 191: | Строка 191: | ||
Возвращаемые данные: результат ответа от сервера в json-формате. | Возвращаемые данные: результат ответа от сервера в json-формате. | ||
== | == Примеры запросов при работе с пользовательскими полями == | ||
Запрос на порлукчение номеров телефонов всех лидов со статусом "В работе": | |||
select PHONE from crm.lead where STATUS_ID = 'IN_PROCESS' | select PHONE from crm.lead where STATUS_ID = 'IN_PROCESS' | ||
Запрос на получение полей Phone, Name, UF_CRM_1524132067 (пользовательское поле), для строк, содержимое поля STATUS_ID которых равно IN_PROCESS (Статус Лида "В работе"), а содержимое поля UF_CRM_1524132067 содержит текст "Нажата клавиша". | |||
Здесь учтены сразу две особенности Битрикс: | |||
1. При запросе на чтение к пользовательскому полю вы должны использовать не имя (UF_Статус обзвона), а его ID (UF_CRM_1524132067). Как узнать ID: https://www.youtube.com/watch?v=UokPByYMjNM | |||
2. Битрикс не возвращает строки по условию "пустое поле". Например, если вам надо получить все записи, в которых поле UF_CRM_1524132067 равно '2' или пустое (NULL). Придётся получить все поля, а затем отфильтровать их на своей стороне. | |||
/! | |||
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]; // Удаляем ненужные | |||
} | |||
} | |||
return this.MakeResult (Leads, 'crm.lead'); | |||
} | |||
!/ | |||
Версия 11:20, 26 апреля 2018
Описание 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 () { 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 - Встреча, 4 - E-Mail "OWNER_TYPE_ID": 1, // 0 - Лид, 1 - Сделка, 2 - Контакт, 3 - Компания, 4 - Предложение, 5 - Счет, 6 - Реквизиты "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; } !/ !/
Описание функций Call Office для работы с Bitrix24
- Функция CallMethod отправляет запрос на API Bitrix24 и получает результаты выполнения запроса (Не более 50 записей из-за особенности API Bitrix24).
Передаваемые параметры:
- Метод передачи запроса. Пример: 'crm.contact.list'.
- Параметры для передаваемого запроса. Параметры соответствуют допустимым для метода, которые можно найти в документации для каждого метода. Пример: {filter:{"PHONE": "555888"},select: ["*"]}.
- Флаг возврата полного ответа сервера. Если true - будет возвращен полный ответ от сервера. Если false - будет возвращен результат ответа от сервера.
Возвращаемые данные: полный ответ или результат ответа от сервера в json-формате.
- Функция MakeResult строит таблицу на основе полученных данных от сервера в json-формате.
Передаваемые параметры:
- Данные в json-формате.
- Таблица, из которой получены данные. Используется для преобразования пользовательских полей в привычный вид, то есть вместо UF_CRM_1234565 подставляет UF_[НазваниеСтолбца] (string, необязательно).
Возвращаемые данные: объект данных (Recordset).
- Функция Select аналогична функции CallMethod, но получает все или определенное количество записей.
Передаваемые параметры:
- Метод передачи запроса. Пример: 'crm.contact.list'.
- Параметры для передаваемого запроса. Параметры соответствуют допустимым для метода, которые можно найти в документации для каждого метода. Пример: {filter:{"PHONE": "555888"},select: ["*"]}.
- Результат запроса при помощи функции CallMethod с такими же параметрами и установленным флагом возврата полного результата ответа от сервера.
Возвращаемые данные: результат ответа от сервера в json-формате.
Примеры запросов при работе с пользовательскими полями
Запрос на порлукчение номеров телефонов всех лидов со статусом "В работе": select PHONE from crm.lead where STATUS_ID = 'IN_PROCESS'
Запрос на получение полей Phone, Name, UF_CRM_1524132067 (пользовательское поле), для строк, содержимое поля STATUS_ID которых равно IN_PROCESS (Статус Лида "В работе"), а содержимое поля UF_CRM_1524132067 содержит текст "Нажата клавиша".
Здесь учтены сразу две особенности Битрикс: 1. При запросе на чтение к пользовательскому полю вы должны использовать не имя (UF_Статус обзвона), а его ID (UF_CRM_1524132067). Как узнать ID: https://www.youtube.com/watch?v=UokPByYMjNM 2. Битрикс не возвращает строки по условию "пустое поле". Например, если вам надо получить все записи, в которых поле UF_CRM_1524132067 равно '2' или пустое (NULL). Придётся получить все поля, а затем отфильтровать их на своей стороне.
/!
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]; // Удаляем ненужные } }
return this.MakeResult (Leads, 'crm.lead');
}
!/
Назад к SQL-запросам.