Importing Callwithus rates to A2billing

There different ways of importing rates to a2billing, some are straight forward but others you will be required to do a bit of editing to make it work.
This HOWto's is going to show step by step on how import callwithus rate card to a2billing.

Step 1- Download rate card from provider.

This will depend on which provider you're using, on this post am going to use callwithus as my provider, download the rates from their website found here, then extract the file and save it anyway on your computer.

Step 2- Create new rate card
Now I go to RATECARD, Create new Rate Card
Give your ratecard a name and a description click CONFIRM DATA

Step 3- Import rate card
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”

File size is greater than allowed limit error.
If you get error that the file limit is greater than allowed limit means the file you're trying to upload is greater than 1Mb which is default value. To change it Go to SYSTEM SETTINGS, Global list and on lower left side of the page change the DISPLAY TO all, then click GO to display all Global list settings. Now search for Max File Upload Size parameter and change it to any big value, or simply add zero to make the maximum file you can upload to be 10Mb, then click COMFIRM DATA to save it.

Now try importing the rates again by following the procedures in step 3.

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.

A2billing should now tell you how many rates have been imported.

Step 4- Verifying the Imported rates
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.

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

Step 5 - Editing the rates via database.
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 with the password set during installation. The default database name is mya2billing – yours may differ.

mysql -u a2billinguser -p

Output:
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3497
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

List existing databases;

mysql> show databases;

Output:

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mya2billing        |

| test               |

+--------------------+

3 rows in set (0.00 sec)

Choose to use mya2billing database

mysql> use mya2billing;

Output:

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> 

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

mysql>  SELECT id,tariffname FROM cc_tariffplan;

Output:

+----+-----------------------+

| id | tariffname            |

+----+-----------------------+

|  1 | Ratecard1             |

|  2 | Callwithus 29-03-2016 |

+----+-----------------------+

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;

Output:

Query OK, 37109 rows affected (1.14 sec)

Rows matched: 37117  Changed: 37109  Warnings: 0

Now I’m going to add 40% on to my sell rate.

mysql> UPDATE cc_ratecard SET rateinitial = (rateinitial*1.4) WHERE idtariffplan = 2;

Output:

Query OK, 37109 rows affected, 17139 warnings (1.23 sec)

Rows matched: 37117  Changed: 37109  Warnings: 17139

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;

Output:

Query OK, 37117 rows affected (0.76 sec)

Rows matched: 37117  Changed: 37117  Warnings: 0

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;

Output:
Query OK, 37117 rows affected (0.75 sec)
Rows matched: 37117  Changed: 37117  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 40% more than my buy rate!

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.

Step-6 Testing the results

The importation is successfully now and you can test it using A2billing simulator by going to rates then simulator.

Seems everything is fine now, much credits should go to owner of this post, i documented this because i face a bit difficult when importing rates using the manual excel method and when i come across to this post i thought it is worth sharing.