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

Manually adding customer payment in A2Billing

If you use Post Pay customers in A2Billing then you have 2 options for maintaining the customers credit limit and balance. You could invoice the customer directly from A2Billing but it can be complex to set up and many businesses already have an invoicing system in place.

The other option is to invoice the A2Billing customer on an external billing system, and then credit the A2Billing account manually on receipt of payment. You will want to credit the A2Billing account or once the customer reaches their credit limit they will be unable to make calls.

Setting up the customer as Post Pay and their credit limit is done when creating the A2Billing account –

Once you have invoiced the customer for their calls and received the payment you will want to credit the account  by going to BILLING / Refills –

and then clicking Add Refill on the right hand side –

Once there enter the customer ID, amount to refill and select No to creating an invoice –

A2Billing refill information

That payment should then get added to the customers balance.

Extension to extension calling between 2 FreePBX systems

Here I’m going to show how to setup extension to extension calling between 2 FreePBX systems using an IAX2 trunk.

There are 2 steps to this. First we need to create an IAX2 trunk on each system. Each trunk will configure the inbound and outbound user/connection. Then we will set up an outbound route on each system to tell FreePBX what calls to pass over the trunk. We will need extensions in different number ranges at each site for this to work.

Here are the details of the systems –

PBX1
IP Address : 1.1.1.1
Extension Range : 3000 – 3999

PBX2
IP Address : 2.2.2.2
Extension Range : 4000 – 4999

SETTING UP THE TRUNKS

First we create an IAX2 trunk on PBX1 with the following details –

Trunk setup PBX1

Continue reading

Using Android with FreePBX – CSipSimple extension

I’ve used a few different Android SIP clients as extensions on FreePBX and my current favourite is CSipSimple

Installation and setup is straight forward. There are several built in configuration profiles for call providers, or you can choose advanced and enter your FreePBX server details to use CSipSimple as a FreePBX extension.

CSipSimple account setupCSipSimple add accountCSipSimple Registered Account

Continue reading

What to consider when chosing an ITSP

Firstly what is an ITSP? As described in our glossary, “ITSP:  Acronym for Internet Telephony Service Provider. This is a company that allows calls to and from ‘normal’ telephone numbers (PSTN)”.  Here’s a few things to think about.

Call plans – Plans maybe a cost for each call, bundled minutes, or plans for domestic / international usage. Providers offer the ability to phone free between customers in their community, is this something you’ll need?

Continue reading

Inbound Number (DDI/DID) with FreePBX 2.10

Setting up an Inbound Number with FreePBX can be tricky. There are a few different things to consider and in the past I’ve written some posts for setting up specific call providers. This post will try to describe what the different options mean, and will hopefully help you set up a DDI number with any provider.

The first thing you need to know is that all SysAdminMan templates have “Allow Anonymous Inbound SIP Calls” set to No. This means that an unknown SIP server can not pass a call to your system to be processed. This is to help prevent hackers route calls through your system. It’s strongly recommended that you leave Anonymous SIP calls set to No, unless you really know what the implications are when setting it to Yes.

So, if we don’t let any SIP servers pass calls to us then we need a way to tell FreePBX to allow calls from our DDI provider. We do this by setting up a SIP trunk.

Setting up a SIP trunk

We are going to put all of the settings under “Outgoing settings”, and leave “Incoming settings” blank. This is a little confusing to say the least! but we are not creating a user on our system for the call provider to connect to, we are just allowing their IP. So we add a new SIP trunk and see these details –

Continue reading

Glossary

New or novice users my find this basic glossary useful when using their Asterisk VOIP, a few telephone industry acronyms

CDR: Call Data Records or Call Detail Records.  These are logs of calls that have passed through the phone system.

GUI: Usually pronounced “gooey”,  Acronym for Graphical User Interface, the graphical administration software used to manipulate the phone system

Inbound call: This is a call from a regular telephone number to your PBX. Also referred to as Origination

ITSP:  Acronym for Internet Telephony Service Provider. This is a company that allows calls to and from ‘normal’ telephone numbers (PSTN)

NGN: Acronym for Non Geographic Numbers, such as 0845 in the UK

Outbound call: This is a call from your VOIP PBX to a regular telephone number. Also referred to as Termination

PBX: This stands for Private Branch Exchange, it is an old fashioned term for a phone system

PSTN: Public Switched Telephone Network. This is a term used to described the ‘normal’ telephone network. Non VOIP landline and mobile/cell telephone numbers.

SIP: A communication protocol for phones, a language to make phones and phone systems, talk to each other

Trunk: A link to another phone system or call provider. For example you would have a ‘trunk’ to your call provider (ITSP). Your system would send VOIP calls down the trunk to your call provider, they would send the call to it’s destination (a landline or mobile)

FreePBX extension choices

When you use a FreePBX server there are various options you have when choosing a device for making calls.

Here are some of them …

Physical desk phone

Yealink T22

Yealink T22

A physical VOIP desk phone connects to your FreePBX server over a broadband connection. One of my current favourites is the Yealink T22P pictured here. It has softkeys for selecting things like do-not-disturb and call history. There are 3 lines so you can do things like conference in another caller to an ongoing call. The sound quality is great and there is also a built-in speaker phone.

There are other makes and models, ranging from phones with no display to those with full colour displays. Any phone that is ‘SIP compatible’ should work, but some phones will work better with FreePBX, especially things like the voicemail waiting light and the softkey menus.

If you are rolling out a lot of phones it’s worth checking the phone is compatible with the FreePBX module called End Point Manager. This allows you to configure and manage phones centrally (on the FreePBX server) rather than configuring each phone individually.

The phone pictured here costs around £80 GBP.

Benefits : Fairly straight forward to configure. Good sound quality. Lots of features.

Negatives : Can be expensive

ATA adapter

Linksys ATA

Linksys ATA

An ATA adapter is a device that turns a traditional analogue phone in to a VOIP phone. You plug your phone in to the ATA, and your ATA in to your broadband router.

Continue reading

Integrating Asterisk, FreePBX and vTiger

vTiger is a free, fully-featured open source CRM solution that can be used to manage your customers and sales leads. It can also integrate with Asterisk to provide click-to-call and call notification from within vTiger itself.

While the Elastix distribution comes with both Asterisk and vTiger installed it may not be desirable to have your CRM and VOIP system running on the same server for performance reasons. You don’t want vTiger interfering with the call quality. Also vTiger on Elastix is not normally at the latest version, which has resulted in security issues in the past.

The solution is to run vTiger and Asterisk on different servers. The instructions below were kindly provided by a SysAdminMan customer on the steps required to integrate Asterisk and vTiger. If you want assistance with this please open a support ticket.

This would work well with a SysAdminMan FreePBX server – sysadminman.net/sysadminman-freepbx-hosting.html

1 – Whitelist the vTiger server IP addresses in the Asterisk server firewall

Continue reading

OpenWRT review

I’ve been using OpenWRT for a couple of years now, so I thought it was time to talk a little about it.

First off what is OpenWRT? It’s a replacement firmware for many domestic router models. There are a few different replacement firmwares but if you’re looking for something comprehensive and geeky I would definitely check out OpenWRT. You can see a list of supported routers on the OpenWRT homepage. My current favourite router is the TP-Link 1043 as it’s inexpensive with plenty of onboard memory and a reasonable CPU.

You flash the OpenWRT firmware image just as you would an update form the router manufacturer (this varies depending on the router). Then you can log in to the web management interface.

From there you can configure your network connections, firewall, etc … There are useful information pages such as realtime graphs –

openwrt-graphs

Continue reading