Getting started with A2Billing – Part 5 Importing a ratecard

When I setup my ratecard here I only created 1 rate to Leicester in the UK. Ok for testing but not much use in the real world! So how are we going to enter all of the rates we need – the answer is to import them. Many ITSP (Internet Telephony Service Providers) publish a rate file that you can download. I’m going to use the callwithus (my provider) rate file that you can download from here.

screenshot-3_2_2009-2_19_40-pm

then I’ve extracted the zip file and saved it to my C drive –

I have also rename the file to .txt instead of .csv as there can be problems with it called .csv

screenshot-3_2_2009-2_37_13-pm

Now I go to RATECARD, Create new Rate Card

Give your ratecard a name and a description. I like to include the date I am creating the ratecard

and click CONFIRM DATA

screenshot-3_2_2009-2_24_16-pm

Now select the Import RateCard menu

Ensure the “Choose the ratecard to import” is set to the rate card you just created

Also the callwithus import file includes “BUYRATE MIN DURATION” and “BUYRATE BILLING BLOCK” information so I have moved those 2 fields over to the “Selected Fields” box

Click on Browse to select your import file then click “Import Ratecard”

screenshot-3_2_2009-2_40_03-pm

helpfully a2billing shows us an example of what it’s going to import. This is the first record in the file.

Click “Continue to Import the Ratecard” if the example looks good

screenshot-3_2_2009-2_40_46-pm

a2billing should now tell you how many rates have been imported

screenshot-3_2_2009-2_41_15-pm1

Unfortunately if we now go and look at one of these rates we’ll see that the callwithus file wasn’t particularly well formatted to be imported into a2billing as-is.

The buy rate is zero, the selling rate is our buying cost and “SELLRATE MIN DURATION / SELLRATE BILLING BLOCK” are both zero!

screenshot-3_2_2009-3_00_17-pm

So we need to sort this out. What we could have done was edit the file in something like Excel before we imported it to get everything in the correct layout. This is probably the easiest option, however I’m going to edit my ratecard using SQL in the database. Be sure you know what you’re doing if you choose this option!

First I need to log into mysql. The default database name is mya2billing – yours may differ.

# mysql -u asteriskuser -p mya2billing
Enter password:
Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
Your MySQL connection id IS 10748
Server version: 5.0.45 SOURCE distribution

TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the buffer.

mysql>

Next I need to find out the ID of the tarifplan I want to edit

mysql> SELECT id,tariffname FROM cc_tariffplan;
+----+---------------------+
| id | tariffname          |
+----+---------------------+
|  1 | test-rate-card      |
|  2 | callwithus 03-02-09 |
+----+---------------------+
2 ROWS IN SET (0.00 sec)

Now I’m going to set the buy rate to be the sell rate that was loaded from the import file –

mysql> UPDATE cc_ratecard SET buyrate = rateinitial WHERE idtariffplan = 2;
Query OK, 9344 ROWS affected (0.11 sec)
ROWS matched: 9345  Changed: 9344  Warnings: 0

Now I’m going to add 20% on to my sell rate –

mysql> UPDATE cc_ratecard SET rateinitial = (rateinitial*1.2) WHERE idtariffplan = 2;
Query OK, 9344 ROWS affected (0.12 sec)
ROWS matched: 9345  Changed: 9344  Warnings: 0

Now to set the “SELLRATE MIN DURATION” to the “BUYRATE MIN DURATION” that was loaded from the import file –

mysql> UPDATE cc_ratecard SET initblock = buyrateinitblock WHERE idtariffplan = 2;
Query OK, 9345 ROWS affected (0.11 sec)
ROWS matched: 9345  Changed: 9345  Warnings: 0

and finally to set “SELLRATE BILLING BLOCK” to the “BUYRATE BILLING BLOCK” that was loaded from the import file –

mysql> UPDATE cc_ratecard SET billingblock = buyrateincrement WHERE idtariffplan = 2;
Query OK, 9345 ROWS affected (0.12 sec)
ROWS matched: 9345  Changed: 9345  Warnings: 0

Now if we go and look at the rate we were looking at before it looks much better. The billing increments are all the same and my sell rate it 20% more than my buy rate!

screenshot-3_2_2009-3_37_57-pm

Now I just need to go to my call plan and remove the test rate card  created earlier and add my new callwithus rate card

screenshot-3_2_2009-3_41_37-pm

5 thoughts on “Getting started with A2Billing – Part 5 Importing a ratecard

  1. Abdelkader

    Hello,

    I have the following situation with a2billing:

    I have 3 DID for enabling my clients to call worldwide destinations. Each DID corresponds to a type of client: free number, local number and callback number. Also, I heve setup a call plan with 3 rate cards (each rate card must be matched to a specific DID).

    My problem is: How to match each DID with a specific rate card with a2billing?

    Please help.

    Thanks.

  2. matt Post author

    Hi, I’m not sure you can assign different callplans based on the access number dialled in the current version on a2billing. What you may be able to use are a couple of settings in the a2billing.conf file called extracharge_did and extracharge_fee. With these you can add a charge to the call plan rate based on the access number that has been dialled. This would also prevent you having to manage multiple rate cards. Matt.

  3. John

    I am trying to do this and I get message:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysql&gt’ at line 1

    I tried several combinations and none of them worked. Am I typing something wrong?

  4. marcin

    The error it self answers your question . You are typing or pasting ‘mysql>’ and you do not need it.
    Just start your mysq commands with “select” or “update”.

  5. hugo

    tengo un problema con a2billing cuando termino la instalacion y quiero entrar http://mi ip/a2billing/admin no muestra nada sa la pantalla en blanco

Comments are closed.