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).
