В этой статье будет приведен пример загрузки данных из Mango Office в Qlik Sense с помощью PHP. Это не идеальный скрипт, сделана рабочая связка, которая ежедневно выгружает данные в QVD для сквозной аналитики по интернет-проектам.
Выгрузка данных / получение данных из Манго Телеком API
Интеграция Mango Office и аналитической системы Qlik Sense для получения статистики звонков. Схема настройки ETL процесса может быть изменена.
_settings.php
<?php //Берется из личного кабинета Mango Office $api_key = '7wlk24q8754654654654654654564woat'; $api_salt = 'bm4309d685198198198yHKJh87ggudrde'; ?>
_functions.php
<?php
//=====================================================================================
// ОПИСАНИЕ: Файл с функциями для php скриптов обработки данных
// Версия: v1
// Дата создания: 2019.04.16
// Автор: Шамаев Иван
//=====================================================================================
function send_request_to_api($url,$data,$api_key,$api_salt){
//=====================================================================================
// Дата: 2019.04.16
// Тек.Версия: v1
// Автор: Иван Шамаев
// Описание:
// Изменения: №1 <Дата> <Автор> - <Что изменено>
//=====================================================================================
$json = json_encode($data);
$sign = hash('sha256', $api_key . $json . $api_salt);
$postdata = array(
'vpbx_api_key' => $api_key,
'sign' => $sign,
'json' => $json
);
$post = http_build_query($postdata);
$opts = array(
'http' => array(
'method' => 'POST',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $post
)
);
$context = stream_context_create($opts);
$response = file_get_contents($url, false, $context);
$result = json_decode($response,TRUE);
$key = $result['key'];
return $key;
}
function get_result_to_api($url,$key,$api_key,$api_salt){
//===============================================================================================
// Дата: 2019.04.16
// Тек.Версия: v2
// Автор: Иван Шамаев
// Описание: Функция получения результатов расчета по запросу к Mango API
// Изменения: №1 2019.04.17 Иван Шамаев - При запросе данных за 1 месяц данные
// Данные формируются какое-то время. Соответственно нужно делать
// 1 попытку в 5 секунд по запросу результата расчета. Количество попыток 20
//===============================================================================================
$data = array(
"key" => $key
);
$json = json_encode($data);
$sign = hash('sha256', $api_key . $json . $api_salt);
$postdata = array(
'vpbx_api_key' => $api_key,
'sign' => $sign,
'json' => $json
);
$post = http_build_query($postdata);
$opts = array(
'http' => array(
'method' => 'POST',
'header' => 'Content-type: application/x-www-form-urlencoded',
'content' => $post
)
);
$context = stream_context_create($opts);
//=========================================================
// Получаем данные в цикле, т.к. API работает не мгновенно
//=========================================================
//Пустая переменная
$stats_result = NULL;
//Ограничиваем количество попыток 20 - максимум
$i_try_max = 20;
$i_try = 1;
//Пока переменная пуста делаем попытки повторного обращения к серверу
while (empty($stats_result) and $i_try <= $i_try_max) {
//Забираем данные в формате CSV с разделителем ; и переносом на новую строку \n
$stats_result = file_get_contents($url, false, $context);
$i_try++;
sleep(5);
}
if($i_try>$i_try_max){
echo '<span style="color:red;">ПРЕВЫШЕНО КОЛИЧЕСТВО ПОПЫТОК (>'.$i_try_max.' раз) ПОДКЛЮЧЕНИЙ ДЛЯ ПОЛУЧЕНИЯ РЕЗУЛЬТАТА</span>';
exit();
}
return $stats_result;
}
function put_csv_to_array($result_data,$header_dates,$header_row){
//=====================================================================================
// Дата: 2019.04.16
// Тек.Версия: v1
// Автор: Иван Шамаев
// Описание:
// Изменения: №1 <Дата> <Автор> - <Что изменено>
//=====================================================================================
//Парсим строку $result_data
$result_array = array();
$temp = array();
$i_flag = 0;
//Дробим ответ от сервера на отдельные строки с разделителем \n
$big_parts = explode("\n", $result_data);
foreach ($big_parts as $big_part){
//Дробим каждую CSV-строку по разделителю ";"
$temp = explode(';', $big_part);
//Пишем данные в результирующий массив
foreach ($temp as $key_temp=>$value_temp){
//Если название столбца - дата, то изменяем формат
if (in_array($header_row[$key_temp], $header_dates)) {
$header_name = $header_row[$key_temp];
$header_name = preg_replace('/[^a-zA-Zа-яА-Я0-9 \:]/ui', '',$header_name );
$result_array[$i_flag][$header_name] = date("Y-m-d H:i:s", $value_temp);
}
else {
$result_array[$i_flag][$header_row[$key_temp]] = $value_temp;
}
}
$i_flag++;
}
//Возвращаем массив
return $result_array;
}
function format_timestamp($date,$start_end_property){
//=====================================================================================
// Дата: 2019.04.16
// Тек.Версия: v1
// Автор: Иван Шамаев
// Описание:
// Изменения: №1 <Дата> <Автор> - <Что изменено>
//=====================================================================================
if($start_end_property==="start") {
$calc_date = new DateTime($date.' 00:00:00', new DateTimeZone('Europe/Moscow'));
$return_date = $calc_date->getTimestamp();
}
elseif($start_end_property==="end") {
$calc_date = new DateTime($date.' 23:59:59', new DateTimeZone('Europe/Moscow'));
$return_date = $calc_date->getTimestamp();
}
//Возвращаем массив
return $return_date;
}
function set_dates_from_url(){
//=====================================================================================
// Дата: 2019.04.17
// Тек.Версия: v1
// Автор: Иван Шамаев
// Описание: Функция для установки переменных $start_date, $end_date из URL запроса
// Изменения: №1 <Дата> <Автор> - <Что изменено>
//=====================================================================================
//Задаем глобальные переменные
global $start_date, $end_date;
//Получаем переменные из URL запроса
if (isset($_GET['start_date']) and isset($_GET['end_date'])) {
$start_date = htmlspecialchars($_GET["start_date"]);
$end_date = htmlspecialchars($_GET["end_date"]);
}
else {
err();
}
}
?>
_print_csv.php
<?php
//-- 2019.04.12 Закомментировал динамические имена, т.к. при изменениях может ломаться Qlik
//Получаем название файла php
//$path_parts = $_SERVER['PHP_SELF'];
//Вычленяем название php
//$phpScriptName = str_replace('.php','',substr($path_parts, strrpos($path_parts, '/') + 1));
//-- Единое название файлов csv для всех
$phpScriptName = 'DataSet';
// параметр вывода, сделаем так, чтобы файл загружался, а не отображался
header('Content-Type:text/csv;charset=utf-8');
header('Content-Disposition: attachment; filename='.$phpScriptName.'.csv');
// создаем указатель файла, подключенный к выходному потоку
$output = fopen('php://output', 'w');
// Первая строк необходима, чтобы Excel понял, что формат CSV на UTF-8 кодировке
//fwrite($output,b"\xEF\xBB\xBF" ) ;
// Название колонок (заголовки)
fputcsv($output, array_keys($result_array[0]), ";");
// Перебираем строки и печатаем в файл csv
foreach($result_array as $array2){
fputcsv($output, $array2, ";");
}
?>
get_call_statistics.php
<?php
//=============================================================================================================================
// ОПИСАНИЕ: Файл с функциями для php скриптов обработки данных. Файл полностью построен на функциях, но как выяснилось
// для API Манго офис это не очень подходящий вариант, т.к. все очень разнообразно для каждой функции
// Версия: v1
// Дата создания: 2019.04.17
// Автор: Шамаев Иван
// Пример запроса: http://your-domain-iis/mango/json/v2/get_call_statistics.php?start_date=2019-03-01&end_date=2019-03-31
//=============================================================================================================================
// ======= БУФЕР =========
//Включение буферизации вывода
ob_start();
//========================================================
// НАСТРОЙКИ: ПОЛУЧАЕМ ОБЩИЕ ПЕРЕМЕННЫЕ
//========================================================
include '_settings.php';
$url_request = 'https://app.mango-office.ru/vpbx/stats/request';
$url_result = 'https://app.mango-office.ru/vpbx/stats/result';
//========================================================
// ФУНКЦИИ
//========================================================
include '_functions.php';
//Создаем переменные даты начала и даты окончания из URL: $start_date, $end_date
set_dates_from_url();
//Начало месяца
$start_date_timestamp = format_timestamp($start_date,"start");
//Конец месяца
$end_date_timestamp = format_timestamp($end_date,"end");
//========================================================
// НАСТРОЙКА ПАРАМЕТРОВ ЗАПРОСА (можно править)
//========================================================
//Перечень полей, которые будет выводить API Mango Office
$header_row = [
'records','start','finish',
'answer','from_extension','from_number',
'to_extension','to_number','disconnect_reason',
'line_number','location'
];
//Задаем поля, которые содержат дату (по другому пока не знаю как это дело пометить)
$header_dates = ['start','finish','answer'];
$fields_request = implode(",",$header_row);
$column_name_csv = implode(";",$header_row);
//Параметры запроса
$request_data = array(
"date_from" => $start_date_timestamp,
"date_to" => $end_date_timestamp,
"fields" => $fields_request,
"from" => array(
"extension"=>"",
"number" => ""
),
"to"=> array(
"extension"=>"",
"number"=>""
)
);
//========================================================
// ФОРМИРОВАНИЕ ЗАПРОСА: POST /stats/request
//========================================================
//Отправляем запрос и получаем ключ для дальнейшего получения ответа
$key = send_request_to_api($url_request,$request_data,$api_key,$api_salt);
//=====================================================
// ЗАБИРАЕМ ДАННЫЕ: POST /stats/result
//=====================================================
//Забираем данные в формате CSV с разделителем ; и переносом на новую строку \n
$result_data = get_result_to_api($url_result,$key,$api_key,$api_salt);
//=====================================================
// ПАРСИМ ДАННЫЕ: из-за дат приходится парсить строку
// и в PHP делать перекодировку
// из timestamp в ДатаВремя
//=====================================================
//Парсим строку
$result_array = array();
$result_array = put_csv_to_array($result_data,$header_dates,$header_row);
// ======= БУФЕР =========
//Очищаем все выходные буферы (удаляем весь мусор из страницы перед печатью CSV)
ob_end_clean();
//========================================================
// ПЕЧАТАЕМ МАССИВ ДАННЫХ В CSV
//========================================================
include '_print_csv.php';
// ======= БУФЕР =========
//Возвращаем содержимое буфера вывода (наш сформированный csv)
ob_get_contents();
?>
Запрос в Qlik Sense:
Let varMinDate = Num(MakeDate(2018,2,1));
Let varMaxDate = Num(Today());
TempCalendar:
LOAD TempDate As StartDate,
MonthEnd(TempDate) As EndDate
Where TempDate=MonthStart(TempDate);
LOAD
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1
While $(varMinDate) + IterNo() -1 <= $(varMaxDate)
;
For j=1 to NoOfRows('TempCalendar')
LET vStartDate = Peek('StartDate',$(j)-1,'TempCalendar');
LET vEndDate = Peek('EndDate',$(j)-1,'TempCalendar');
[get_call_statistics]:
LOAD records,
start,
finish,
answer,
from_extension,
from_number,
to_extension,
to_number,
disconnect_reason,
line_number,
location
FROM [lib://test]
(URL is [http://your-domain-iis/mango/json/v2/get_call_statistics.php?start_date=$(vStartDate)&end_date=$(vEndDate)],txt, utf8, embedded labels, delimiter is ';', msq)
Where not IsNull(records) and records<>''
;
NEXT j
DROP Table TempCalendar;
Store get_call_statistics into [lib://QVDData/get_call_statistics.qvd] (qvd);
