Добавление возможности выгрузки cdr_reports в формате *.xlsx (MS Office 2007+)

Описание: в данной статье будет рассмотрена работа с базовой отчётностью FreePBX – модулем CDR-Reports и, в частности модификация позволяющая производить выгрузку отчёта в современном формате.

Использованные ресурсы:

Необходимое оборудование:

• CentOS 6.9
• Asterisk 13
• FreePBX 13

Рано или поздно любой владелец задумывается о статистике своих звонков. Давайте обратимся к интерфейсу администрирования FreePBX и рассмотрим, что он может нам предложить. Для озвученной задачи используется отдельный модуль CDR Reports, имеющий следующий интерфейс:

Первый из возможных вариантов предоставляет возможность просмотреть информацию по каналам, прослушать\скачать запись, получить сведения о продолжительности разговора и статусе звонка.

При необходимости, можно использовать, обычно пустое, поле «userfield», записав в него свою информацию. Это весьма полезно, поскольку изменение штатных полей запрещено, а добавлять собственное в таблицу CDR – рискованно, и требует некоторого опыта. Для примера туда можно записать код канала первым завершившим вызов. Данная информация полезна для большинства систем статистики.

Вторая форма отчётности – экспорт в файл. Вот тут и начинаются проблемы. Файл выгружается корректно, но по-умолчанию представляет собой *.csv формат. При открытии через Open Office вы сразу увидите предупреждение о настройке разметки и разделителей.

И так будет каждый раз при открытии, что доставляет неудобства и тратит время. Попробуем открыть через самый распространённый офисный пакет приложений, и конкретно через MS Excel. Получим следующее:

Стало только хуже. Да, как продукт с обширным функционалом этот редактор может привести данные в порядок. На случай если этот вариант приемлем опишем.

1. Выполняем переход «данные» -> «текст по столбцам»

В появившемся окне указываем форматирование с разделителями.

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

После того, как в предпросмотре на последнем шаге мы получаем удовлетворительное отображение данных, можем нажать «готово» и пронаблюдать за результатом.

Всё отобразится, как и требовалось, но вновь было потрачено много времени, а неопытный пользователь и вовсе бы не справился с подобной задачей.

В связи со всем вышесказанным была разработана и внедрена выгрузка в формате *.xlsx. Опишем как произвести эту модификацию.

Получаем доступ в консоль сервера (SSH). Подключаемся и переходим в каталог модуля cdr_reports

# cd /var/www/html/admin/modules/cdr/

И выполняем бэкапы файлов, которые собираемся редактировать. Это необходимо для быстрого отката в случае необходимости.

Немного отвлечёмся. Сам по себе файл формата *.xlsx довольно сложен и отличается от своего предшественника уже тем, что является архивом с файлами и инструкциями. Сформировать такой файл php самостоятельно не способен, поэтому нам потребуется применение сторонней библиотеки, а именно «PhpSpreadsheet».

Установим её с помощью файлового менеджера php – «composer». Если ранее вы таким не пользовались сперва установим его, выполнив ряд несложных команд:

# cd /usr/src
# curl -sS https://getcomposer.org/installer | php
# mv composer.phar /usr/local/bin/composer
# chmod a+x /usr/local/bin/composer

С его помощью скачаем и установим библиотеку «PhpSpreadsheet». Для этого в целевой директории создадим файл инструкций для компоновщика.

# cd /var/www/html/admin/modules/cdr/
# nano composer.json

И добавим в него следующее содержимое:

{
        "require": {
                "phpoffice/phpspreadsheet": "*.*"
        }
}

После чего выполним скрипт с передачей единственного аргумента

# composer install

Отметим изменения в директории: добавился файл composer.lock и папка vendor.

Теперь начнём последовательное внедрение в модуль. Поскольку нужные фрагменты кода уже найдены, позиции будут указаны примерными номерами строк кода в базовом файле.

Для начала добавим новый пункт в интерфейс, для возможности выбора альтернативного отчёта.

Открываем файл

# nano …/cdr/page.cdr.php

И после 168 строки вставляем следующий фрагмент:

<!-- CHANGE BEGIN -->
	<input <?php if ( ! empty($_POST['xlsxrepo']) && $_POST['xlsxrepo'] == 'true' ) { echo 'checked="checked"'; } ?> type="checkbox" name="xlsxrepo" value="true" /> : <?php echo _("XLSX-Report")?><br/>
<!-- CHANGE END -->

Ориентироваться можно на уже существующие элементы. Например быстро найти место можно поиском по фразе «Call Graph».

Обратите внимание: парой строк выше выделен маленький фрагмент. Его присутствие необязательно, но желательно, чтобы система не формировала два отчёта (веб и файл) одновременно.

Далее в этом же файле нам нужно добавить обработчик нового элемента формы. Следующий фрагмент размещаем после 704 строки.

// CHANGE BEGIN
/* ---------------------- Обработчик события ---------------------- */	
if(isset($_POST['xlsxrepo']) && $_POST['xlsxrepo'] == 'true'){
	// Формируем запрос
	$where = "WHERE $date_range $cnum $outbound_cnum $cnam $dst_cnam $did $dst $userfield $accountcode $disposition $duration";
	$query = "SELECT calldate, recordingfile, uniqueid, clid, did, lastapp, dst, disposition, duration, userfield FROM $db_name.$db_table_name $where $order $sort LIMIT $result_limit";
	// Получаем данные
	$answer = $dbcdr->getAll($query, DB_FETCHMODE_ASSOC);
	// Записываем заголовки в массив
	$head_ind = count($answer)+1;
	$answer[$head_ind][1] = _("Call Date");
	$answer[$head_ind][2] = _("Recording");
	$answer[$head_ind][3] = _("System");
	$answer[$head_ind][4] = _("CallerID");
	$answer[$head_ind][6] = _("DID");
	$answer[$head_ind][7] = _("App");
	$answer[$head_ind][8] = _("Destination");
	$answer[$head_ind][9] = _("Disposition");
	$answer[$head_ind][10] = _("Duration");
	$answer[$head_ind][11] = _("Userfield");
	
	// Сортируем в порядке индексирования, а не добавления
	$answer = array_reverse($answer);
	// Передаём данные в функцию экспорта
	cdr_export_xlsx($answer);
}
// CHANGE END

На этом с файлом страницы мы закончили, перейдём к описанию функций которыми пользовались. Их принято размещать в отдельном файле:

# nano function.inc.php

Здесь просто добавляем код в конце файла. Первая функция служит для проверки существования файла и передачи его с сервера на клиент, т.е. пользователю в браузер.

function file_force_download($file) {
	if (file_exists($file)) {
		ob_end_clean();
		
		header('Pragma: public'); // required
		header('Expires: 0');
		header('Last-Modified: ' . gmdate('D,d M YH:i:s') . ' GMT');
		header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
		header('Cache-Control: private',false); // required for certain browsers
		header('Content-Transfer-Encoding: Binary');
		header('Content-Type: application/zip');
		header('Content-Disposition: attachment; filename="export-' . date('Y-d-m-H_i_s') . '.xlsx"');
		header('Content-Length:' . filesize($file));
		
		flush();
		readfile($file);
		die();
	}
}

Задача второй функции получить массив и сформировать файл *.xlsx по всем правилам и требованиям технологии.

function cdr_export_xlsx($xlsxdata){

	// Подготовка к созданию файла
	$spreadsheet = new Spreadsheet();
	$sheet = $spreadsheet->getActiveSheet();
	$sheet->setTitle('report');
	
	// оформляем границы
	$borderStyleArray = [
		'borders' => [
			'outline' => [
				'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
				'color' => ['rgb' => '000000'],
			],
			'horizontal' => [
				'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
				'color' => ['rgb' => '000000'],
			],
			'vertical' => [
				'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
				'color' => ['rgb' => '000000'],
			], 
		],
	];
	$sheet->getStyle('A1:J1')->applyFromArray($borderStyleArray);

	// Запись и передача файла клиенту
	$sheet->FromArray($xlsxdata,NULL,'A1', true);
	$writer = new Xlsx($spreadsheet);
	$writer->save('/var/tmp/report.xlsx');
	file_force_download('/var/tmp/report.xlsx');
}

Сохраняем и возвращаемся на страницу модуля во FreePBX. Отметим добавившийся пункт и сформируем интересующий нас отчёт.

И если всё было выполнено корректно, получим файл следующего содержания:

При этом не требуется какая-либо постобработка или форматирование, можно сразу переходить к оперированию данными в привычном ПО.

Вывод: с помощью показанного здесь примера можно выполнять выгрузку в современный и удобный формат любых данных. Как в интерфейсе FreePBX, так и в собственных разработках.