voipjet is an okay example, but their rate-table is oriented to people in the US. (It has 011 in front of non-countrycode=1 countries.)
So, I strip off the 011 if it's there to make the table easier to LCR with other providers. Also - they did not include the US rate, so you'll have to add it to the bottom of from_voipjet.txt when you're done.
USAGE:
- Copy this code into a text editor on a linux machine, and save it somewhere in it's own directory. Like /root/voipjet/voipjet.php
- Download the voipjet csv, and rename it voipjet.csv
- run 'php ./voipjet.php' and you're done!
Now - notice there are some more files in /root/voipjet/
* from_voipjet.txt - this is the file ready to import into a2billing
* losers.txt, biglosers.txt, and rbiglosers.txt - for people charging a flat rate, vs a straight markup, this will tell you how your cost compares with your sellrate for specific destinations.
* stats.txt - this gives you some overall statistics, averages, etc. (i think my overall average markup logic is flawed but it gives you a good idea.)
Code:
<?php
// 0 1 2 3
$infile = "voipjet.csv"; //Country, Dialcode Prefix, Rate Per Minute, Initial Billing Increment
$outfile="from_voipjet.txt";
$handle = fopen($infile, "r");
$fp = fopen($outfile, "w");
$fp2 = fopen("losers.txt", "w");
$fp3 = fopen("biglosers.txt","w");
$fp4 = fopen("rbiglosers.txt","w");
echo "start\n";
$ratesnum = 0;
$sellsum = 0;
$buysum = 0;
$losers = 0;
$losersbsum = 0;
$losersssum = 0;
$biglosers = 0;
$biglosersbsum = 0;
$biglosersssum = 0;
$rbiglosers = 0;
$rbiglosersbsum = 0;
$rbiglosersssum = 0;
$markup = 0.3;
while (($data = fgetcsv($handle, 3000, ",")) !== FALSE) { //for every row
// where are these items? this is the output order for a2b,
// prefix, desc, sell, buy, postfix
$prefixprefix = ""; // add prefix (country code for domestic decks?)
if (substr($data[1],0,3) == '011') $prefix = substr($data[1],3); // dialprefix with 011 removed (like ${EXTEN:3})
else $prefix = $data[1];
$desc = $data[0]; // destination
$sell = $data[2]*(1+$markup); // rate initial (sell below)
$buy = substr($data[2],1); // buyrate
$postfix = $data[3].';6;6'; // buyinitblock;buyrateincr;sellinitblock;billingblock;connectcharge
$i = ""; // 6->7 digit loop related
// destination specific details
$country = $desc; //state (
if (substr($country,0,6)=='mexico') $postfix = $data[3].';60;60';
if ($prefix<>"" AND $desc<>"Country"){//while ($i<($seventh+1)){ // 6->7 digit loop.
// write ratetable and statistics
$sellsum = $sellsum + $sell;
$buysum = $buysum + $buy;
$ratesnum++;
fwrite($fp, $prefixprefix . $prefix . $i . ";" . $desc . ";" . $sell . ";" . $buy . ";" . $postfix . "\n");
if ($sell < $buy){
fwrite($fp2, $prefixprefix . $prefix . ";" . $desc . ";" . $sell . ";" . $buy . ";" . $postfix . "\n");
$losers++;
$losersbsum = $losersbsum + $buy;
$losersssum = $losersssum + $sell;
}
if (($buy-$sell)>.005){
fwrite($fp3, $prefixprefix . $prefix . ";" . $desc . ";" . $sell . ";" . $buy . ";" . $postfix . "\n");
$biglosers++;
$biglosersbsum = $biglosersbsum + $buy;
$biglosersssum = $biglosersssum + $sell;
}
if (($buy-$sell)>.01){
fwrite($fp4, $prefixprefix . $prefix . ";" . $desc . ";" . $sell . ";" . $buy . ";" . $postfix . "\n");
$rbiglosers++;
$rbiglosersbsum = $rbiglosersbsum + $buy;
$rbiglosersssum = $rbiglosersssum + $sell;
}
}
}
$fp9 = fopen("stats.txt", "w");
fwrite($fp9,"ALL:\n-Sell Total = ".$sellsum."\n-Buy Total = ".$buysum."\n-Avg markup = ".(1-($buysum/$sellsum))."\n\n");
if ($losersssum <> 0) fwrite($fp9,"LOSERS:\n-Sell Total = ".$losersssum."\n-Buy Total = ".$losersbsum."\n-Avg Markup = ".(1-($losersbsum/$losersssum))."\n\n");
if ($biglosersssum <> 0) fwrite($fp9,"BIGLOSERS:\n-Sell Total = ".$biglosersssum."\n-Buy Total = ".$biglosersbsum."\n-Avg Markup = ".(1-($biglosersbsum/$biglosersssum))."\n\n");
if ($rbiglosersssum <> 0) fwrite($fp9,"RBIGLOSERS:\n-Sell Total = ".$rbiglosersssum."\n-Buy Total = ".$rbiglosersbsum."\n-Avg Markup = ".(1-($rbiglosersbsum/$rbiglosersssum))."\n\n");
fwrite($fp9,"COUNTS:\n-total = ".$ratesnum." - losers = ".$losers." - big losers = ".$biglosers." - rbig losers = ".$rbiglosers."\n\n");
fwrite($fp9,"PERCENTS:\n-total = ".($ratesnum/$ratesnum*100)." - losers = ".($losers/$ratesnum*100)." - big losers = ".($biglosers/$ratesnum*100)." - rbig losers = ".($rbiglosers/$ratesnum*100)."\n\n");
echo "end\n";
?>