Blog de Ubuntu en Español Blog [no oficial] de Ubuntu en Español

Asterisk, migracion de Master CSV a Mysql

Luego de aprender como guardar todos nuestros CDRs dentro de mysql, me ha surgido la duda de como migrar los ya existentes en el master.csv a la base de datos nueva, bueno primero explico algo de como y porque.

Por que:
Esto corriendo asterisk 1.4 y manejandolo con Vicidial (contact center app) pero este utiliza el custom_cdr.conf en donde el guardado en CVS esta definido de la siguiente forma:

Master.csv => "${CDR(clid)}","${CDR(src)}","${CDR(dst)}","${CDR(dcontext)}",
"${CDR(channel)}","${CDR(dstchannel)}","${CDR(lastapp)}",
"${CDR(lastdata)}","${CDR(start)}","${CDR(answer)}","${CDR(end)}",
"${CDR(duration)}","${CDR(billsec)}","${CDR(disposition)}",
"${CDR(amaflags)}",
"${CDR(accountcode)}","${CDR(uniqueid)}","${CDR(userfield)}"

Que significa esto ?? http://www.voip-info.org/wiki/view/Asterisk+billing

ahora bien he encontrado algunos scripts que dicen ser la herramienta mas util para este tipo de migracion lo cual luego de probarlos ha resultado falso!! jejeje

aqui les dejo el que yo modifique y utilize para que migrara de forma transparente los datos del Master.csv con la configuracion cdr_custom de vicidial.

<?php
/*** process asterisk cdr file (Master.csv) insert usage
* values into a mysql database which is created for use
* with the Asterisk_addons cdr_addon_mysql.so
* The script will only insert NEW records so it is safe
* to run on the same log over-and-over.
*
* Author: John Lange (john@johnlange.ca)
* Date: Version 2 Released July 8, 2008
*
*
* Here is what the script does:
*
* Parse each row from the text log and insert it into the database after testing for a
* matching "calldate, src, duration" record in the database. Note that not all fields are
* tested.
*
* If you have a large existing database it is recomended that you add an index to the calldate
* field which will greatly speed up this import.
*
*/
/*
 * Modified by Leif Madsen, July 29, 2009 to add additional columns.
 * Original post and code by John Lange: http://www.johnlange.ca/tech-tips/asterisk/asterisk-cdr-csv-mysql-import-v20/
 */
/*
 * Modified by Paulo Alvarado, Nov. 09 2009 to run with the custom master.cvs from vicidial now
 * Original post and code by John Lange: http://www.johnlange.ca/tech-tips/asterisk/asterisk-cdr-csv-mysql-import-v20/
 */
$locale_db_host = 'localhost';
$locale_db_name = 'asterisk';
$locale_db_login = 'root';
$locale_db_pass = 'vicidialnow';
if($argc == 2) {
$logfile = $argv[1];
} else {
print("Usage ".$argv[0]." <filename>\n");
print("Where filename is the path to the Asterisk csv file to import (Master.csv)\n");
print("This script is safe to run multiple times on a growing log file as it only imports records that are newer than the database\n");
exit(0);
}
// connect to db
$linkmb = mysql_connect($locale_db_host, $locale_db_login, $locale_db_pass) or die("Could not connect : " . mysql_error());
mysql_select_db($locale_db_name, $linkmb) or die("Could not select database $locale_db_name");
//** 1) Find records in the asterisk log file. **
$rows = 0;
$handle = fopen($logfile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
// NOTE: the fields in Master.csv can vary. This should work by default on all installations but you may have to edit the next line to match your configuration

list($clid, $src, $dst, $dcontext, $channel, $dstchannel, $lastapp, $lastdata, $start, $answer, $end, $duration, $billsec, $disposition, $amaflag, $accountcode,$uniqueid, $userfield ) = $data;

/** 2) Test to see if the entry is unique **/
$sql="SELECT calldate, src, duration".
" FROM cdr".
" WHERE calldate='$end'".
" AND src='$src'".
" AND duration='$duration'".
" LIMIT 1";
if(!($result = mysql_query($sql, $linkmb))) {
print("Invalid query: " . mysql_error()."\n");
print("SQL: $sql\n");
die();
}
if(mysql_num_rows($result) == 0) { // we found a new record so add it to the DB
// 3) insert each row in the database

$sql = "INSERT INTO cdr (calldate, answerdate, hangupdate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflag, accountcode, uniqueid, userfield)
VALUES('$start', '$answer', '$end', '$clid', '$src', '$dst', '$dcontext', '$channel', '$dstchannel','$lastapp', '$lastdata', '$duration', '$billsec', '$disposition', '$amaflag', '$accountcode', '$uniqueid', '$userfield')";

if(!($result2 = mysql_query($sql, $linkmb))) {
print("Invalid query: " . mysql_error()."\n");
print("SQL: $sql\n");
die();
}
print("Inserted: $end $src $duration\n");
$rows++;
} else {
print("Not unique: $end $src $duration\n");
}
}
fclose($handle);
print("$rows imported\n");
?>

ahora la parte mas dificil, como ejecutarlo:

php import.php Master.csv

Nota: si por algun motivo no funciona el comando php, eso significa que no tienen el paquete php-cli para ejecutar sentencias php en linea de comando.