Tag Archives: mysql

A2Billing useful MySQL queries

I am not condoning anyone who does not understand MySQL or the internal workings of A2Billing to be messing around with MySQL commands; however occassionally you do need to, to save yourself a lot of time and effort. I’m intending to update this page with MySQL commands for specific jobs as I work them out, for my future reference mostly.

I’ve posted before about various A2Billing MySQL queries, so this is a down-and-dirty page of commands, not a full explanation of what each does or why you might need them.

Here’s the first one:
You have 2 tarrifs setup, and you need to change the sellprice of all the rates in tariff 2 to match those of tariff 1:

use mya2billing;
update cc_ratecard as a, cc_ratecard as b
set a.rateinitial = b.rateinitial where a.dialprefix=b.dialprefix and a.idtariffplan=2

 

Next up it’s bills. How to find out the total bill for calls ever, for all customers?

use mya2billing;
select sum(sessionbill),card_id, stoptime from cc_call group by card_id

And similaly to get the unbilled usage (assuming a billing date of 1st of the month):

select sum(sessionbill),card_id, stoptime from cc_call where stoptime >
DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 0 MONTH)),'%Y-%m-01 00:00:00')  group by card_id

Next is useful sort of query – it gives you the outgoing calls made by a customer between 2 dates, and finds the name of the destination as well as naming the columns for a nicely exportable CSV from MySQL Workbench:

select cc_call.starttime as "Time",cc_call.sessiontime as "Seconds",cc_call.calledstation as "Called Number",cc_call.sessionbill as "Cost",cc_call.src as "Source",cc_prefix.destination as "Destination" from cc_call left join cc_prefix on cc_call.destination=cc_prefix.prefix where cc_call.card_id = "1" and cc_call.starttime between '2015-02-01 00:00:00' and '2015-03-01 23:59:00' order by cc_call.starttime DESC;

Note this query has been updated to account for your destinations not being complete (IE some may not have a name, and would not have shown up in your list).

Slow rate browsing in A2Billing

I recently looked at an A2Billing 1.34 install that was slow to browse the rates through the GUI. There were over 800,000 rates which was causing the slowdown. While probably not a good idea to have so many rates, it is possible to speed up this screen by creating an index in MySQL.

To do that –

Log in to MySQL –

(you should be able to get the username/password you need from the top of the /etc/asterisk/a2billing.conf file)

mysql -u a2billing-user -p mya2billing

Create an index on the destination field in the cc_ratecard table –

create index ind_cc_ratecard_destination using btree on cc_ratecard(destination);

To find out why queries are taking so long in MySQL you can turn on the slow-query log in MySQL.

See here for more info – http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html