A little bit of A2Billing magic

A2Billing is a great product to set yourself up as a small-time ITSP. It does however have some limitations and oddities.

Today’s thought process was to offer a package of free minutes to a customer. Mobile calls are expensive of course, and the customer is phoning an awful lot. The assumption that I am paying maybe 6p and generally selling to low-volume customers for 10p. So I might want to offer an incentive and offer a package of £20 per month to give 250 mobile minutes per month for a cost of £20, thus saving the customer 2p per minute on their 250 minutes and still earning me 2p per minute.

I may also add a landlines minutes package, giving them perhaps 1000 minutes for £10 per month, meaning 1p per minute to them (if they use them all – often they will like the idea of a package and then not use all the minutes 🙂

So jump into A2B, click Add Package, choose free seconds and OK. Then you can add rates, and you get a simple search box.

This is where the trouble starts. In the UK the calling rates are so complex you wouldn’t believe it.

Landlines all start 01, 02, or 03 (441, 442, and 443 in reality). That’s fine.

All mobiles start 07 (447) but not all 07 numbers are mobiles, and not all are charged at the same rate. Typically we’d pay 10p per minute for a mobile call, but up to 20p for some mobile networks, 25-35p for what are known as ‘personal’ numbers, and I certainly don’t want to be giving those away for 8p!

So I need a way to choose only those 07 rates where I pay less than 8p and add them to my package, without having to manually check and click each one.

You’ll need MySQL workbench for this  – I connect via an SSH tunnel to avoid exposing any MySQL ports to the outside world. The command you need is as follows:

insert into mya2billing.cc_package_rate (package_id,rate_id) SELECT '1', id FROM mya2billing.cc_ratecard where dialprefix regexp '^447.' and buyrate < 0.08;

At this stage we’ve already created call package number 1. After “select” you see the number ‘1’ which means that the number 1 is inserted into the first column every time; this is the package number so adjust this if you are adding for package 2, 3 etc. Then we use a regular expression criteria on the dialprefix columnt to say ‘match anything starting with 447’ and also a buyrate of less than 8p.

This command will do the dirty straight away, so you may wish to check your work FIRST with the following:

SELECT * from mya2billing.cc_ratecard where dialprefix regexp '^447.' and buyrate < 0.08;

This will show all details about that rates you will be adding. I had 450 or so items in my list.

And voila – check in A2B Package Offer > Add, then Add Rates to your package – you’ll see all the 447 rates you need.

Hope this helps!