asterisk2billing.org
http://forum.asterisk2billing.org/

Importing rate-tables revisited.
http://forum.asterisk2billing.org/viewtopic.php?f=21&t=6570
Page 1 of 1

Author:  andyml [ Thu Nov 12, 2009 5:11 pm ]
Post subject:  Importing rate-tables revisited.

So - jroper and I have been talking about LCR and ratetables in another thread () and when the importing of rate-tables came up, I thought it might be best to start another thread - it being a topic that effects everyone.

A little background. Joe asked if I had seen http://trac.asterisk2billing.org/cgi-bin/trac.cgi/wiki/1-3-ImportRatecard regarding manipulating the tables with excel. I mentioned to Joe that with 7 digit rate tables from US providers, there are 1.4 million rates, and that Excel's 65536 max rows was insufficient for manipulating them. In fact, I've written some php to do the manipulation and thought I might share it here, as an example configured for a specific provider.

Unfortunately, it's newest iteration (and it changes every time I reconfigure it for a new provider I add something cool to it) is for a no-name large-scale carrier and I'll need to tweak it for gafachi, or voip.ms, or vitelity etc. Let me know what provider(s) you (the community) want to see it for?

Right now, it imports from a proper csv line by line, and transforms it to the format A2Billing is looking for (including the buyrate, buyrateinitblock, buyrateincrement, and initblock). Along the way it keeps some statistics. If you're using a fixed sell-rate (like $0.01/min for the US, but you have different costs along the way) it tells you what your overall margin is, how many rates are losers (cost more than sell), how many rates are biglosers (cost at least $0.005 more than sell) or really biglosers (rbiglosers) that cost at least $0.01 more than sell.

Its easy to add to and easily handles the +60mb ratedecks from the large providers with ease. That said, it requires some massaging up front, since every provider presents their rates differently, and everyone will want a different sellrate (either fixed, or a percentage above cost, etc.)

So - what provider do you all want as an example? I'd love to show it off.

I also think this is a good place to discuss any changes you'd like to see to the a2billing import tools.

Author:  jroper [ Thu Nov 12, 2009 7:24 pm ]
Post subject:  Re: Importing rate-tables revisited.

Quote:
...regarding manipulating the tables with excel...


Or MS Access, the logic is the same.

Joe

Author:  andyml [ Thu Nov 12, 2009 7:26 pm ]
Post subject:  Re: Importing rate-tables revisited.

man, if we could build a query-set in access for converting a specific provider's csv's into a2billing's scsv's (semi-colon seperated...) That'd be pretty cool...

Author:  skiller [ Fri Nov 13, 2009 5:04 am ]
Post subject:  Re: Importing rate-tables revisited.

Dear Andy,

great from you.

voicetrading.com (only Stdandard and Premium route) and voipjet.com

Thanks
Michael

Author:  andyml [ Fri Nov 13, 2009 1:17 pm ]
Post subject:  Re: Importing rate-tables revisited.

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";
?>

Author:  skiller [ Fri Nov 13, 2009 2:56 pm ]
Post subject:  Re: Importing rate-tables revisited.

Thats great my friend. And what about Voicetrading?
I try it out straight away.

Cheers
Skiller

Author:  andyml [ Fri Nov 13, 2009 3:01 pm ]
Post subject:  Re: Importing rate-tables revisited.

My personal theory is, if you can't figure out how to massage the rate tables to get them into a2billing, given the vast resources available in this forum and other blogs etc, you probably shouldn't be running an ITSP. IOW, I'm not just going to do it for you.

That said, I'm more than happy to help you understand my script so that you can do it yourself!

- create /root/voicetrading/ and copy/paste my script into voicetrading.php
- download the rate table from voicetrading and save it in /root/voicetrading/
- edit the script so that it's pointing at the right files ($infile and $outfile)
- the carefully try to read through the script. See if you can't find the definitions for the componants a2billing is looking for, and try to define them. let me know what you need help with. I know not everyone speaks php, but I tried to make it pretty plain-english.

Author:  skiller [ Fri Nov 13, 2009 3:35 pm ]
Post subject:  Re: Importing rate-tables revisited.

I still did this. Very easy!

Thanks for sharing.

Cheers
Skiller

Author:  aberrio [ Wed Dec 09, 2009 5:43 am ]
Post subject:  Re: Importing rate-tables revisited.

HI,

Are you able to tweak it for gafachi?

Regards,

Al

Author:  andyml [ Wed Dec 09, 2009 12:49 pm ]
Post subject:  Re: Importing rate-tables revisited.

Funny you should ask. I have done it for Gafachi once already.

There are a lot of variables with Gafachi though. They change your rate based on volume - even on volume to specific areas. Read the domestic "terms of service" - it isn't a non-sensical contract, it is actually very serious 'small print' giving them the right to change from flat domestic rates to NPANXX rates if your traffic pattern falls out of their defined "normal".

PM me some details and maybe the one I wrote earlier can serve for you?

Andy

Author:  aberrio [ Sun Dec 13, 2009 4:46 am ]
Post subject:  Re: Importing rate-tables revisited.

HI,

Would you please provide field order for a2billing.

Regards,

Al

Author:  jroper [ Sun Dec 13, 2009 9:14 am ]
Post subject:  Re: Importing rate-tables revisited.

The order of fields is flexible and set by you, after dialcode, destination, sell rate.

Author:  bucasia [ Sun Dec 13, 2009 3:00 pm ]
Post subject:  Re: Importing rate-tables revisited.

I wrote a PHP script a while back too for importing rate tables that might help someone.

It works with callwithus.com currently but could easily be changed for other providers if they supply a csv file. You need to set the correct database username/password.

By default it adds 50% to the buy rate to create the sell rate and automatically creates a ratecard in the a2billing database.

You can find it here - http://sysadminman.net/blog/2009/automatically-create-a2billing-rate-card-from-callwithus-rate-file-873

Author:  aberrio [ Sun Dec 13, 2009 9:56 pm ]
Post subject:  Re: Importing rate-tables revisited.

HI,

Thank you for your help. I was able to manipulated rates from my provider.

Best regards,

Al

Author:  jeffik [ Tue Jun 05, 2012 7:16 pm ]
Post subject:  Re: Importing rate-tables revisited.

Hello All,
I am setting up a2billing. I am using vitelity. They only provide three pieces of information, dial code, destination name and rate. What i see is they have some npa-nxx s for us but not all, mostly like this 1787944 and 1939218. they do not list any other US npa-nxx s. So do i need to set rate cards so they look at all these first and if they do not find a match then use a 'default' rate? It looks like the rates on the npa-nxx s they list are higher than the standard rates for the rest of US. Any thoughts on how to do this?
Thank you in advance.

Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/