Page 1 of 1
Read out data from a PABX log file
Posted: Thu Feb 19, 2004 12:50 am
by Khairul Alam
Hello Viewer
Would u pls someone tell me,
how could I import data from a PABX log file(txt file),
and insert the data to the database
for ur kind information I am giving the sample log file.
pls find the log file in the following address-
http://cp.bol-online.com/pabx/
its an assignment for me.
thats why this so urgent for me.
Pls don't mind for my poor english.
I will be so greatful if anyone give any regarding information.
Thanks.
Posted: Thu Feb 19, 2004 1:44 am
by markl999
You're going to have to create some custom parsing code. The below is what i've whipped up and it seems to work (for me anyway) you just need to tweak it to your particular set up etc..
Code: Select all
<?php
error_reporting(E_ALL);
/*
CREATE TABLE `pbax` (
`date` varchar(8) NOT NULL default '',
`time` varchar(7) NOT NULL default '',
`ext` char(3) NOT NULL default '',
`co` char(2) NOT NULL default '',
`dial` varchar(10) NOT NULL default '',
`ring` varchar(4) NOT NULL default '',
`duration` varchar(8) NOT NULL default '',
`acccode` varchar(10) NOT NULL default '',
`cd` char(2) NOT NULL default ''
)
*/
//Parse PABX log file
$logfile = 'pabx_log.txt'; //change this to the name of your log file
$content = file($logfile);
foreach($content as $line){
//find only pabx lines and not the lines with the titles
if(preg_match('/^[0-9]{2}\/[0-9]{2}\/[0-9]{2}\s/', $line)){
$validlines[] = $line;
}
}
if(isset($validlines)){
//change the below for your mysql user and password
$db = mysql_connect('localhost', 'user', 'password') or die(mysql_error());
//change the below to the name of your database
mysql_select_db('test') or die(mysql_error());
foreach($validlines as $valid){
$valid = preg_replace('/\s{2,}/', ' ', $valid);
$parts = explode(' ', $valid);
list($date, $time, $ext, $co, $dial) = array_slice($parts, 0, 5);
$ring = '';
$duration = '';
$acccode = '';
$cd = '';
foreach(array_slice($parts, 5) as $check){
switch(strlen($check)){
case 4:
$ring = $check;
break;
case 8:
$duration = $check;
break;
case 2:
$cd = $check;
break;
}
}
//build up the insert query
$sqladd = array(
"'".$date."'",
"'".$time."'",
"'".$ext."'",
"'".$co."'",
"'".$dial."'",
"'".str_replace("'", "''", $ring)."'",
"'".str_replace("'", "''", $duration)."'",
"'".$acccode."'",
"'".$cd."'");
//change the below if your table isn't named pabx
$sql = "INSERT INTO pbax VALUES (".join(',', $sqladd).")";
echo $sql.'<br />';
mysql_query($sql) or die(mysql_error());
}
} else {
//if it ever gets here, then something went wrong :o
echo 'Error: No valid lines found';
}
?>
Posted: Thu Feb 19, 2004 3:57 am
by Khairul Alam
Thanks lot markl999
I'v worked with ur given code. It works very nice.
If u dont mind I'v some more query to u-
Would u pls tell me any links or other info about this system by which I can learn more.
I want to generate some reports suppose-
call summery of particular date
call summery of how many Incomming and Outgoing call have occured by a perticular extension
etc.
I am eagerly waiting for ur valuable sugestions.
Thanks again
Posted: Thu Feb 19, 2004 10:10 am
by markl999
To produce reports you just need to work out a bunch of mysql queries. If you look at
http://php.net/mysql it will show you the commands you can use to run the queries.
http://www.sqlcourse.com has some tutorials on building queries. A simple query might look like :
$sql = "SELECT * FROM pabx WHERE date = '06/02/04'";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
//echo the $row values here
}
Posted: Fri Feb 20, 2004 1:57 am
by Khairul Alam
Thanks markl999 for ur valuable info.
I got some confusing about regular expressions of ur given code.
But I retrieve the meaning of the expressions by myself.
Here are some new query to u-
$ring = '';
$duration = '';
$acccode = '';
$cd = '';
foreach(array_slice($parts, 5) as $check){
switch(strlen($check)){
case 4:
$ring = $check;
break;
case 8:
$duration = $check;
break;
case 2:
$cd = $check;
break;
Would u pls tell me what u do in this section.
Why dont u assign values to other variable's($ring,$cd etc.) by the following line ? -
Code: Select all
list($date, $time, $ext, $co, $dial) = array_slice($parts, 0, 5);
If my query is confusing, pls let me know.
Thanks again
Posted: Fri Feb 20, 2004 6:46 am
by Khairul Alam
Another Query To markl999
$sql = "SELECT * FROM pabx WHERE date = '06/02/04'";
if the 'date' field is of type 'varchar' then how could I match with a variable '$date' that contains a date.
Would u pls tell me the necessary PHP syntax with sql query.
Thanks Lot
Posted: Fri Feb 20, 2004 7:39 am
by markl999
Would u pls tell me what u do in this section.
When looking at the log file you can see that the first 5 columns are always present (date, time, ext, co, dialnumber) so the list($date, $time, $ext, $co, $dial) = array_slice($parts, 0, 5); takes care of those. The remaining fields have to be treated differently as some are filled and some arn't, but luckily each of them are a unique length.
ring is always 4 chars, durations is always 8 and cd is always 2, so the code you queried above basically initialises the values to '' first, then checks the length of each of the remaining columns, this way you know which columns are filled and which are not.
With regards to your second question :
$date = '06/02/04';
$sql = "SELECT * FROM pabx WHERE date = '$date'";