Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Thu Mar 28, 2024 3:54 pm
Auto Dialer Software


All times are UTC




Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: Importing rate-tables revisited.
PostPosted: Thu Nov 12, 2009 5:11 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
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.


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Thu Nov 12, 2009 7:24 pm 
Offline

Joined: Fri Jun 23, 2006 3:56 pm
Posts: 4065
Quote:
...regarding manipulating the tables with excel...


Or MS Access, the logic is the same.

Joe


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Thu Nov 12, 2009 7:26 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
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...


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Fri Nov 13, 2009 5:04 am 
Offline
User avatar

Joined: Mon Oct 26, 2009 8:20 am
Posts: 93
Dear Andy,

great from you.

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

Thanks
Michael


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Fri Nov 13, 2009 1:17 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
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";
?>


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Fri Nov 13, 2009 2:56 pm 
Offline
User avatar

Joined: Mon Oct 26, 2009 8:20 am
Posts: 93
Thats great my friend. And what about Voicetrading?
I try it out straight away.

Cheers
Skiller


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Fri Nov 13, 2009 3:01 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
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.


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Fri Nov 13, 2009 3:35 pm 
Offline
User avatar

Joined: Mon Oct 26, 2009 8:20 am
Posts: 93
I still did this. Very easy!

Thanks for sharing.

Cheers
Skiller


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Wed Dec 09, 2009 5:43 am 
Offline

Joined: Mon Nov 23, 2009 6:14 am
Posts: 36
HI,

Are you able to tweak it for gafachi?

Regards,

Al


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Wed Dec 09, 2009 12:49 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
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


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Sun Dec 13, 2009 4:46 am 
Offline

Joined: Mon Nov 23, 2009 6:14 am
Posts: 36
HI,

Would you please provide field order for a2billing.

Regards,

Al


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Sun Dec 13, 2009 9:14 am 
Offline

Joined: Fri Jun 23, 2006 3:56 pm
Posts: 4065
The order of fields is flexible and set by you, after dialcode, destination, sell rate.


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Sun Dec 13, 2009 3:00 pm 
Offline

Joined: Mon Mar 02, 2009 8:56 pm
Posts: 271
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


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Sun Dec 13, 2009 9:56 pm 
Offline

Joined: Mon Nov 23, 2009 6:14 am
Posts: 36
HI,

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

Best regards,

Al


Top
 Profile  
 
 Post subject: Re: Importing rate-tables revisited.
PostPosted: Tue Jun 05, 2012 7:16 pm 
Offline

Joined: Sat May 05, 2012 6:45 pm
Posts: 11
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 
VoIP Billing solution


All times are UTC


Who is online

Users browsing this forum: No registered users and 11 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group