HylafaxSql

Интеграция HylaFax с базой данный

Первоисточник

В английском HOWTO HylaFax есть небольшой абзац 6.14 Database Integration - интеграция с базой данных:

HylaFAX обычно помещает всю свою статистическую информацию в отформатированный xferfaxlog файл. Многие пользователи считают, что лучше вставить ту же самую информацию в локальную базу данных, такую как MySQL, где это может использоваться с другой бухгалтерской информацией. Эту цель несложно достигнуть особенно используя etc/FaxAccounting, который описан на странице документации xferfaxlog . Типовой сценарий FaxAccounting можно посмотреть здесь: http://hylafax.sourceforge.net/howto/misc/FaxAccounting]. Помните, что etc/FaxAccounting должен быть отмечен как выполнимый большинством процессов HylaFax.

Структура параметров

Если мы обобщим информацию из документации xferfaxlog и небольшую практику использования можно сказать следующее:

в каждую строчку xferfaxlog всегда записывается ровно по 20 полей разделенных символами табуляции и соответственно ровно это же количество 20 полей всегда передается скрипту etc/FaxAccounting. часть полей может быть пустыми - но если поле присутствует то оно передаётся на строго определенной позиции: так например если определен параметр modem - он передается только четвертым параметром.

1	date	date	date	date	date	date	date
2	SEND	RECV	CALL	POLL	PAGE	UNSENT	SUBMIT
3	commid	commid	commid	commid	commid
4	modem	modem	modem	modem	modem
5	jobid	qfile		jobid	jobid	jobid	jobid
6	jobtag			jobtag	jobtag	.	.
7	sender	fax	fax	sender	sender	.	.
8	dest-number	local-number	local-number	dest-number	dest-number	.	.
9	CSI	TSI		TSI		.	.
10	param	param	0	param	0	.	.
11	#pages	#pages	0	#pages	0	.	.
12	jobtime	jobtime	jobtime	jobtime	jobtime	.	.
13	conntime	conntime	conntime	conntime	conntime	.	.
14	reason	reason		reason	reason	.	.
15		CIDName	CIDName			.	.
16		CIDNumber	CIDNumber			.	.
17		callid	callid			.	.
18	owner		.	.	owner	.	.
19	dcs	dcs	.	.		.	.
20	jobinfo		.	.	jobinfo	.	.

Точками в таблице отмечены те поля которые могут все рано присутствовать, то есть в документации они отмечены не как null или 0, а многоточием :))

Нельзя сказать что структура выбрана разработчиками оптимально. Так например непонятно почему надо было 23 параметра впихивать в 20 позиций, а не зарезервировать для каждой свою. Ведь 20 полей или 23 - большой разницы нет. А вот разбирать смешанные данные в одних позициях(например jobid и qfile в пятой позиции) это менее логично.

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

Скрипты интеграции

Скрипт инициализации раздельных таблиц MySQL

Из соображений как бы чего не потерять - таблицы сделаны несколько избыточными:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- База данных: `hylafax`
--
-- --------------------------------------------------------
--
-- Структура таблицы `CALL`
--
DROP TABLE IF EXISTS `CALL`;
CREATE TABLE IF NOT EXISTS `CALL` (
 `date` datetime NOT NULL,
 `etype` char(6) collate utf8_unicode_ci NOT NULL,
 `commid` char(20) collate utf8_unicode_ci NOT NULL,
 `modem` char(6) collate utf8_unicode_ci NOT NULL,
 `jobid` char(100) collate utf8_unicode_ci NOT NULL,
 `jobtag` char(100) collate utf8_unicode_ci NOT NULL,
 `fax` char(100) collate utf8_unicode_ci NOT NULL,
 `localnumber` char(50) collate utf8_unicode_ci NOT NULL,
 `CSI` char(100) collate utf8_unicode_ci NOT NULL,
 `params` char(100) collate utf8_unicode_ci NOT NULL,
 `pages` int(11) unsigned NOT NULL,
 `jobtime` time NOT NULL,
 `conntime` time NOT NULL,
 `reason` char(255) collate utf8_unicode_ci NOT NULL,
 `CIDName` char(100) collate utf8_unicode_ci NOT NULL,
 `CIDNumber` char(100) collate utf8_unicode_ci NOT NULL,
 `callid` char(100) collate utf8_unicode_ci NOT NULL,
 `owner` char(100) collate utf8_unicode_ci NOT NULL,
 `dcs` char(255) collate utf8_unicode_ci NOT NULL,
 `jobinfo` char(255) collate utf8_unicode_ci NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id_CALL` int(11) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`id_CALL`),
 KEY `commid` (`commid`),
 KEY `modem` (`modem`),
 KEY `jobid` (`jobid`),
 KEY `date` (`date`),
 KEY `CSI` (`CSI`),
 KEY `dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Структура таблицы `PAGE`
--
DROP TABLE IF EXISTS `PAGE`;
CREATE TABLE IF NOT EXISTS `PAGE` (
 `date` datetime NOT NULL,
 `etype` char(6) collate utf8_unicode_ci NOT NULL,
 `commid` char(20) collate utf8_unicode_ci NOT NULL,
 `modem` char(6) collate utf8_unicode_ci NOT NULL,
 `jobid` int(11) NOT NULL,
 `jobtag` char(100) collate utf8_unicode_ci NOT NULL,
 `sender` char(100) collate utf8_unicode_ci NOT NULL,
 `destnumber` char(50) collate utf8_unicode_ci NOT NULL,
 `TSI` char(100) collate utf8_unicode_ci NOT NULL,
 `params` char(100) collate utf8_unicode_ci NOT NULL,
 `pages` int(11) unsigned NOT NULL,
 `jobtime` time NOT NULL,
 `conntime` time NOT NULL,
 `reason` char(255) collate utf8_unicode_ci NOT NULL,
 `CIDName` char(100) collate utf8_unicode_ci NOT NULL,
 `CIDNumber` char(100) collate utf8_unicode_ci NOT NULL,
 `callid` char(100) collate utf8_unicode_ci NOT NULL,
 `owner` char(100) collate utf8_unicode_ci NOT NULL,
 `dcs` char(255) collate utf8_unicode_ci NOT NULL,
 `jobinfo` char(255) collate utf8_unicode_ci NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id_PAGE` int(11) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`id_PAGE`),
 KEY `commid` (`commid`),
 KEY `modem` (`modem`),
 KEY `jobid` (`jobid`),
 KEY `date` (`date`),
 KEY `TSI` (`TSI`),
 KEY `dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Структура таблицы `POLL`
--
DROP TABLE IF EXISTS `POLL`;
CREATE TABLE IF NOT EXISTS `POLL` (
 `date` datetime NOT NULL,
 `etype` char(6) collate utf8_unicode_ci NOT NULL,
 `commid` char(20) collate utf8_unicode_ci NOT NULL,
 `modem` char(6) collate utf8_unicode_ci NOT NULL,
 `jobid` int(11) NOT NULL,
 `jobtag` char(100) collate utf8_unicode_ci NOT NULL,
 `sender` char(100) collate utf8_unicode_ci NOT NULL,
 `destnumber` char(50) collate utf8_unicode_ci NOT NULL,
 `TSI` char(100) collate utf8_unicode_ci NOT NULL,
 `params` char(100) collate utf8_unicode_ci NOT NULL,
 `pages` int(11) unsigned NOT NULL,
 `jobtime` time NOT NULL,
 `conntime` time NOT NULL,
 `reason` char(255) collate utf8_unicode_ci NOT NULL,
 `CIDName` char(100) collate utf8_unicode_ci NOT NULL,
 `CIDNumber` char(100) collate utf8_unicode_ci NOT NULL,
 `callid` char(100) collate utf8_unicode_ci NOT NULL,
 `owner` char(100) collate utf8_unicode_ci NOT NULL,
 `dcs` char(255) collate utf8_unicode_ci NOT NULL,
 `jobinfo` char(255) collate utf8_unicode_ci NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id_POLL` int(11) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`id_POLL`),
 KEY `commid` (`commid`),
 KEY `modem` (`modem`),
 KEY `jobid` (`jobid`),
 KEY `TSI` (`TSI`),
 KEY `destnumber` (`destnumber`),
 KEY `date` (`date`),
 KEY `dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Структура таблицы `RECV`
--
DROP TABLE IF EXISTS `RECV`;
CREATE TABLE IF NOT EXISTS `RECV` (
 `date` datetime NOT NULL,
 `etype` char(6) collate utf8_unicode_ci NOT NULL,
 `commid` char(20) collate utf8_unicode_ci NOT NULL,
 `modem` char(6) collate utf8_unicode_ci NOT NULL,
 `qfile` char(255) collate utf8_unicode_ci NOT NULL,
 `jobtag` char(100) collate utf8_unicode_ci NOT NULL,
 `fax` char(100) collate utf8_unicode_ci NOT NULL,
 `localnumber` char(50) collate utf8_unicode_ci NOT NULL,
 `TSI` char(100) collate utf8_unicode_ci NOT NULL,
 `params` char(100) collate utf8_unicode_ci NOT NULL,
 `pages` int(11) unsigned NOT NULL,
 `jobtime` time NOT NULL,
 `conntime` time NOT NULL,
 `reason` char(255) collate utf8_unicode_ci NOT NULL,
 `CIDName` char(100) collate utf8_unicode_ci NOT NULL,
 `CIDNumber` char(100) collate utf8_unicode_ci NOT NULL,
 `callid` char(100) collate utf8_unicode_ci NOT NULL,
 `owner` char(100) collate utf8_unicode_ci NOT NULL,
 `dcs` char(255) collate utf8_unicode_ci NOT NULL,
 `jobinfo` char(255) collate utf8_unicode_ci NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id_RECV` int(11) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`id_RECV`),
 KEY `commid` (`commid`),
 KEY `modem` (`modem`),
 KEY `TSI` (`TSI`),
 KEY `date` (`date`),
 KEY `qfile` (`qfile`),
 KEY `dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Структура таблицы `SEND`
--
DROP TABLE IF EXISTS `SEND`;
CREATE TABLE IF NOT EXISTS `SEND` (
 `date` datetime NOT NULL,
 `etype` char(6) collate utf8_unicode_ci NOT NULL,
 `commid` char(20) collate utf8_unicode_ci NOT NULL,
 `modem` char(6) collate utf8_unicode_ci NOT NULL,
 `jobid` int(11) NOT NULL,
 `jobtag` char(100) collate utf8_unicode_ci NOT NULL,
 `sender` char(100) collate utf8_unicode_ci NOT NULL,
 `destnumber` char(50) collate utf8_unicode_ci NOT NULL,
 `CSI` char(100) collate utf8_unicode_ci NOT NULL,
 `params` char(100) collate utf8_unicode_ci NOT NULL,
 `pages` int(11) unsigned NOT NULL,
 `jobtime` time NOT NULL,
 `conntime` time NOT NULL,
 `reason` char(255) collate utf8_unicode_ci NOT NULL,
 `CIDName` char(100) collate utf8_unicode_ci NOT NULL,
 `CIDNumber` char(100) collate utf8_unicode_ci NOT NULL,
 `callid` char(100) collate utf8_unicode_ci NOT NULL,
 `owner` char(100) collate utf8_unicode_ci NOT NULL,
 `dcs` char(255) collate utf8_unicode_ci NOT NULL,
 `jobinfo` char(255) collate utf8_unicode_ci NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id_SEND` int(10) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`id_SEND`),
 KEY `commid` (`commid`),
 KEY `modem` (`modem`),
 KEY `jobid` (`jobid`),
 KEY `CSI` (`CSI`),
 KEY `date` (`date`),
 KEY `destnumber` (`destnumber`),
 KEY `dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Структура таблицы `SUBMIT`
--
DROP TABLE IF EXISTS `SUBMIT`;
CREATE TABLE IF NOT EXISTS `SUBMIT` (
 `date` datetime NOT NULL,
 `etype` char(6) collate utf8_unicode_ci NOT NULL,
 `commid` char(20) collate utf8_unicode_ci NOT NULL,
 `modem` char(6) collate utf8_unicode_ci NOT NULL,
 `jobid` int(11) NOT NULL,
 `jobtag` char(100) collate utf8_unicode_ci NOT NULL,
 `sender` char(100) collate utf8_unicode_ci NOT NULL,
 `destnumber` char(50) collate utf8_unicode_ci NOT NULL,
 `CSI` char(100) collate utf8_unicode_ci NOT NULL,
 `params` char(100) collate utf8_unicode_ci NOT NULL,
 `pages` int(11) unsigned NOT NULL,
 `jobtime` time NOT NULL,
 `conntime` time NOT NULL,
 `reason` char(255) collate utf8_unicode_ci NOT NULL,
 `CIDName` char(100) collate utf8_unicode_ci NOT NULL,
 `CIDNumber` char(100) collate utf8_unicode_ci NOT NULL,
 `callid` char(100) collate utf8_unicode_ci NOT NULL,
 `owner` char(100) collate utf8_unicode_ci NOT NULL,
 `dcs` char(255) collate utf8_unicode_ci NOT NULL,
 `jobinfo` char(255) collate utf8_unicode_ci NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id_SUBMIT` int(11) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`id_SUBMIT`),
 KEY `commid` (`commid`),
 KEY `modem` (`modem`),
 KEY `jobid` (`jobid`),
 KEY `destnumber` (`destnumber`),
 KEY `CSI` (`CSI`),
 KEY `date` (`date`),
 KEY `dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Структура таблицы `UNSENT`
--
DROP TABLE IF EXISTS `UNSENT`;
CREATE TABLE IF NOT EXISTS `UNSENT` (
 `date` datetime NOT NULL,
 `etype` char(6) collate utf8_unicode_ci NOT NULL,
 `commid` char(20) collate utf8_unicode_ci NOT NULL,
 `modem` char(6) collate utf8_unicode_ci NOT NULL,
 `jobid` int(11) NOT NULL,
 `jobtag` char(100) collate utf8_unicode_ci NOT NULL,
 `sender` char(100) collate utf8_unicode_ci NOT NULL,
 `destnumber` char(50) collate utf8_unicode_ci NOT NULL,
 `CSI` char(100) collate utf8_unicode_ci NOT NULL,
 `params` char(100) collate utf8_unicode_ci NOT NULL,
 `pages` int(11) unsigned NOT NULL,
 `jobtime` time NOT NULL,
 `conntime` time NOT NULL,
 `reason` char(255) collate utf8_unicode_ci NOT NULL,
 `CIDName` char(100) collate utf8_unicode_ci NOT NULL,
 `CIDNumber` char(100) collate utf8_unicode_ci NOT NULL,
 `callid` char(100) collate utf8_unicode_ci NOT NULL,
 `owner` char(100) collate utf8_unicode_ci NOT NULL,
 `dcs` char(255) collate utf8_unicode_ci NOT NULL,
 `jobinfo` char(255) collate utf8_unicode_ci NOT NULL,
 `dt` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `id_UNSENT` int(11) unsigned NOT NULL auto_increment,
 PRIMARY KEY  (`id_UNSENT`),
 KEY `commid` (`commid`),
 KEY `modem` (`modem`),
 KEY `jobid` (`jobid`),
 KEY `CSI` (`CSI`),
 KEY `date` (`date`),
 KEY `destnumber` (`destnumber`),
 KEY `dt` (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Скрипт транслирующий текущий xferfaxlog в базу

Файлы xxfs и xxaw.awk должны находиться в одном каталоге с xferfaxlog

файл xxfs :

#!/bin/sh
#
sed '1d'  xferfaxlog |
sed 's/\"//g'|
awk -f xxaw.awk |
sed 's/,$/;/' | mysql hylafax

файл xxaw.awk:

BEGIN{
 FS="\t"
 send=""
 recv=""
 call=""
 page=""
 poll=""
 unsent=""
 submit=""
}
{
 split($1,a,"[\/ ]")
 if($2 == "SEND"){ 
   send=send "('20" a[3] "-" a[1]"-"a[2]" "a[4]"','"$2"','"$3"','"$4"','"$5"','"$6"','"$7"','"$8"','"$9"','"$10"','"$11"','"$12"','"$13"','"$14"','"$15"','"$16"','"$17"','"$18"','"$19"','"$20"',NULL,NULL),"
 }else{
  if($2== "RECV"){ 
   recv = recv "('20" a[3] "-" a[1]"-"a[2]" "a[4]"','"$2"','"$3"','"$4"','"$5"','"$6"','"$7"','"$8"','"$9"','"$10"','"$11"','"$12"','"$13"','"$14"','"$15"','"$16"','"$17"','"$18"','"$19"','"$20"',NULL,NULL),"
 }else{
  if($2 == "CALL"){
   call = call "('20" a[3] "-" a[1]"-"a[2]" "a[4]"','"$2"','"$3"','"$4"','"$5"','"$6"','"$7"','"$8"','"$9"','"$10"','"$11"','"$12"','"$13"','"$14"','"$15"','"$16"','"$17"','"$18"','"$19"','"$20"',NULL,NULL),"
}else{
  if($2 == "PAGE"){
   page = page "('20" a[3] "-" a[1]"-"a[2]" "a[4]"','"$2"','"$3"','"$4"','"$5"','"$6"','"$7"','"$8"','"$9"','"$10"','"$11"','"$12"','"$13"','"$14"','"$15"','"$16"','"$17"','"$18"','"$19"','"$20"',NULL,NULL),"
 }else{
  if($2 == "POLL"){
   poll = poll "('20" a[3] "-" a[1]"-"a[2]" "a[4]"','"$2"','"$3"','"$4"','"$5"','"$6"','"$7"','"$8"','"$9"','"$10"','"$11"','"$12"','"$13"','"$14"','"$15"','"$16"','"$17"','"$18"','"$19"','"$20"',NULL,NULL),"
 }else{
  if($2 == "UNSENT"){
   unsent = unsent "('20" a[3] "-" a[1]"-"a[2]" "a[4]"','"$2"','"$3"','"$4"','"$5"','"$6"','"$7"','"$8"','"$9"','"$10"','"$11"','"$12"','"$13"','"$14"','"$15"','"$16"','"$17"','"$18"','"$19"','"$20"',NULL,NULL),"
 }else{
  if($2 == "SUBMIT"){
   submit = submit "('20" a[3] "-" a[1]"-"a[2]" "a[4]"','"$2"','"$3"','"$4"','"$5"','"$6"','"$7"','"$8"','"$9"','"$10"','"$11"','"$12"','"$13"','"$14"','"$15"','"$16"','"$17"','"$18"','"$19"','"$20"',NULL,NULL),"
}
}}}}}}
}
END{
if( send != "" ){print "INSERT INTO `SEND` VALUES " send }
if( recv != "" ){print "INSERT INTO `RECV` VALUES " recv }
if( call != "" ){print "INSERT INTO `CALL` VALUES " call }
if( page != "" ){print "INSERT INTO `PAGE` VALUES " page }
if( poll != "" ){print "INSERT INTO `POLL` VALUES " poll }
if( unsent != "" ){print "INSERT INTO `UNSENT` VALUES " unsent }
if( submit != "" ){print "INSERT INTO `SUBMIT` VALUES " submit }
}

etc/FaxAccounting для раздельных таблиц

#!/bin/bash
#
if [ $# -eq 20 ]
then
# переделываем дату для mysql
dad=`echo "$1" | cut  -d" " -f1`
tat=`echo "$1" | cut  -d" " -f2`
ddt=`echo "$dad" | cut  -d"/" -f2`
mdt=`echo "$dad" | cut  -d"/" -f1`
gdt=`echo "$dad" | cut  -d"/" -f3`
newdat=`echo "20$gdt-$mdt-$ddt $tat"`
# пишем событие в базу
echo "INSERT INTO \`$2\` VALUES('$newdat','$2','$3','$4','$5','$6','$7','$8','$9','${10}','${11}','${12}','${13}','${14}','${15}','${16}','${17}','${18}','${19}','${20}',NULL,NULL)" | mysql hylafax
else
# получна неправильная структура
echo "$@ - $1 - $2" >> log
fi

PS

Проблему представляет недостаток данных.

Так например в таблицу неплохо бы получать данные DIAL для исходящих сеансов связи -аналогичных данным CALL для входящих вызовов.

В записях структуры SEND и SUBMIT также не лишним было бы присутсвие поля qfile.

---

если вам статья поможет киньте сколько не жалко на кошелек 41001101259634 яндекс.денег :)))

что б понимать насколько не зря я трудился :)) - скрипты писал сам :)