Vous utilisez les services de téléphonie professionnelle d’OVH ?
OVH vous permet, au travers de l’API mise à disposition, de récupérer des données complètes concernant vos appels (communément appelées CDR, pour Call Data Records). Typiquement, il s’agit de fichiers au format CSV, que vous pouvez ouvrir et exploiter dans un tableur, ou aisément intégrer dans une base de données.
Voici trois petits scripts que vous pouvez utiliser tels que, ou adapter, afin de récupérer vos CDR :
- le premier réalise un simple téléchargement de vos fichiers CSV,
- le second enregistre le contenu des CSV dans une table de votre base de données au cours de l’opération de téléchargement,
- le troisième est destiné à récupérer les données correspondants à des appels entrants vers des numéros en 08…
Les méthodes de l’API OVH mises en oeuvre dans ces scripts sont principalement :
- /telephony/{billingAccount}/historyConsumption/{date}/file
- /telephony/{billingAccount}/historyTollfreeConsumption/{date}/document
Ces scripts sont écrits en PHP et utilisent le wrapper PHP d’OVH. Je les publie en espérant que cela puisse vous être utile. Vous pouvez les adapter à un autre langage et/ou à vos propres besoins ; à cette fin, retrouvez les sur GitLab.
Pour un usage professionnel, et notamment si vous êtes revendeur des solutions de téléphonie OVH, je vous invite à regarder du côté de notre solution BlueRockTEL, et de notre eBook Facturation de la Téléphonie OVH avec BlueRockTEL.
1. getCSV_SimpleDownload
require_once '../_cgGlobal.php';
require_once '../mysql_connect.php';
require_once '../vendor/autoload.php';
use \Ovh\Api;
$ovh = new Api(APP_KEY, APP_SECRET, APP_ENDPOINT, APP_CONSKEY);
print "Welcome " . $ovh->get('/me')['firstname'] . "\n";
# creates the path where we are going to put the csv files:
$dir = getcwd();
$path = $dir . '/csv2';
exec("rm -rf {$path}");
exec("mkdir {$path}");
# sets what we want to get :
$period = date('Y-m-01'); # that's fine for the last available CSV
$services = $ovh -> get('/telephony');
$delay = 15;
# delay between doc request and download needs to be long if you download many files
# otherwise, the doc is not ready for download
foreach($services as $service)
{
print 'For service ' . $service . '';
try
{
$document = $ovh->get('/telephony/' . $service . '/historyConsumption/' . $period . '/file?extension=csv');
$document = (object) $document;
sleep($delay);
if(!file_exists($path . '/' . $document->filename))
{
if(!$data = file_get_contents($document->url))
{
print ', download of document '. $document->filename . ': error reading file' . "\n";
}
else
{
if(!file_put_contents($path . '/' . $document->filename, $data))
{
print ', download for '. $document->filename . ': error reading file' . "\n";
}
else
{
print ', download for '. $document->filename . ': success' . "\n";
}
}
}
}
catch (Exception $message)
{
print ", no document available for $service.\n";
}
}
print 'Job is done, thanks and see you soon' . "\n";
2. getCSV
require_once '../_cgGlobal.php';
require_once '../mysql_connect.php';
require_once'../vendor/autoload.php';
use \Ovh\Api;
$ovh = new Api(APP_KEY, APP_SECRET, APP_ENDPOINT, APP_CONSKEY);
print "Welcome " . $ovh->get('/me')['firstname'] . "\n";
# creates the path where we are going to put the csv files:
$dir = getcwd();
$path = $dir . '/csv2';
exec("rm -rf {$path}");
exec("mkdir {$path}");
# here set the table you want to use:
# fields are : phoneLine, datetime, duration, calledNumber, nature,
# type, destination, priceWithOutVAT, date, time, designation, idkey,
# callingNumber, dialedNumber
$tableForTelephonyCalls = 'telephonyCalls';
# sets what we want to get :
$period = date('Y-m-01'); # that's fine for the last available CSV
$services = $ovh -> get('/telephony');
$delay = 15;
# delay between doc request and download needs to be long if you download many files
# otherwise, the doc is not ready for download
foreach($services as $service)
{
print 'For service ' . $service . '';
try
{
$document = $ovh->get('/telephony/' . $service . '/historyConsumption/' . $period . '/file?extension=csv');
$document = (object) $document;
sleep($delay);
if(!file_exists($path . '/' . $document->filename))
{
if(!$data = file_get_contents($document->url))
{
print ', download of document '. $document->filename . ': error reading file' . "\n";
}
else
{
if(!file_put_contents($path . '/' . $document->filename, $data))
{
print ', download for '. $document->filename . ': error reading file' . "\n";
}
else
{
print ', download for '. $document->filename . ': success' . "\n";
# sends the content of the file to the database:
$insert = 0;
$sth = $dbh->prepare("INSERT INTO $tableForTelephonyCalls (phoneLine, datetime, duration, calledNumber, nature, type, destination, priceWithOutVAT, date, time, designation, idkey, callingNumber, dialedNumber) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
$sth1 = $dbh->prepare("SELECT idkey FROM $tableForTelephonyCalls WHERE idkey = :idkey"); # checks if the call is already in the table or not
$lines = explode("\n", $data); # I was using fgetcsv but I think this is much simplier:
foreach($lines as $line)
{
$line = str_replace('+', '00', $line); # because I like to have 00331... instead of +331...
$values = explode(',', $line);
if(is_numeric($values[11])) # filter titles
{
$sth1->bindParam(':idkey', $values[11]);
$sth1->execute();
if($sth1->rowCount() == 0) # call is not in the table
{
$sth->execute($values);
$insert++;
}
}
}
print $insert . ' records inserted in table from file ' . $document->filename . "\n";
}
}
}
}
catch (Exception $message)
{
print ", no document available for $service.\n";
}
}
print 'Job is done, thanks and see you soon' . "\n";
3. getCSVforReversed
require_once '../_cgGlobal.php';
require_once '../mysql_connect.php';
require_once '../vendor/autoload.php';
use \Ovh\Api;
$ovh = new Api(APP_KEY, APP_SECRET, APP_ENDPOINT, APP_CONSKEY);
print 'Welcome ' . $ovh->get('/me')['firstname'] . "\n";
# creates the path where we are going to put the csv files:
$dir = getcwd();
$path = $dir . '/csv';
exec("rm -rf {$path}");
exec("mkdir {$path}");
# here set the table you want to use:
# fields are : phoneLine, datetime, duration, calledNumber, nature,
# type, destination, priceWithOutVAT, date, time, designation, idkey,
# callingNumber, dialedNumber
$tableForTelephonyCallsReversed = 'telephonyCallsReversed';
# sets what we want to get :
$period = date('Y-m-01');
$services = $ovh -> get('/telephony');
foreach($services as $service)
{
print 'service: ' . $service . "\n";
try
{
$document = $ovh->get('/telephony/' . $service . '/historyTollfreeConsumption/' . $period . '/document');
$document = (object) $document;
sleep(2); # sometimes the document is not immediately ready for download immediately
if(!file_exists( $path . '/' . $document->filename))
{
$content = '';
$h = fopen($document->url, 'rb');
while(!feof($h))
{
$content .= fread($h, 8192);
}
fclose($h);
$h = fopen($path . '/' . $document->filename, 'wb');
if(!fwrite($h, $content))
{
print 'Download for '. $document->filename . ': error' . "\n";
}
else
{
print 'Download for '. $document->filename . ': success' . "\n";
$h = fopen($path . '/' . $document->filename, 'r');
# insert the data in table:
$insert=0;
$sql = "INSERT INTO $tableForTelephonyCallsReversed (idkey, phoneLine, datetime, date, time, duration, callingNumber, operator, priceReversed, type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
$sth = $dbh->prepare($sql);
# second query checks that the call is not is the table already:
$sql1 = "SELECT id FROM $tableForTelephonyCallsReversed WHERE idkey = :idkey";
$sth1 = $dbh->prepare($sql1);
while(($data = fgetcsv($h, 0, "\n")) !== FALSE)
{
for($i=0; $i<count($data); $i++)
{
$values = explode(',', $data[$i]);
if(is_numeric($values[0])) # filter titles
{
$sth1->bindParam(':idkey', $values[0]);
$sth1->execute();
if($sth1->rowCount() == 0) # call is not in the table
{
$sth->execute($values);
$insert++;
}
}
}
}
print $insert . ' records inserted in table' . "\n";
}
}
else
{
print $document->filename . ' was already downloaded' . "\n";
}
}
catch (Exception $message)
{
print "no doc available for $service\n";
}
}
Cyrille Georgel 