Tariff Search

How does MagnusBilling select the rate to be used?

When a customer makes an external call, to a landline or cell phone, a verification routine is triggered which consists of:

1. Check the username and password.
2. Checks whether the client is active.
3. Checks if the number dialed is a DID.
4. Check if the dialed number is a SIP Account.
5. Check the customer’s plan.
6. Search within the plan’s tariffs which fare best fits the number dialed.
7. Search for the trunks that belong to the trunk group of the tariff found.
8. Sends the call to the first trunk, and if it fails it sends to the next.
9. When the call is finished, the purchase and sale price is calculated.
10. Added the call in the CDR report.
11. Discounting the value of the call in the customer’s credit.

Rather, how does step 6 work?

It is carried out as follows. Let’s say customer XXXXX called 551140045678

In step 5, the system takes the customer’s plan, and then in step 6 a “SELECT” is made on the tariff table like this.

$ sql = "SELECT * FROM pkg_plan LEFT JOIN pkg_rate ON pkg_plan.id = pkg_rate.id_plan
LEFT JOIN pkg_prefix ON pkg_rate.id_prefix = pkg_prefix.id
LEFT JOIN pkg_trunk_group ON pkg_trunk_group.id = pkg_rate.id_trunk_group
WHERE pkg_plan.id = $ MAGNUS-> id_plan AND pkg_rate.status = 1
AND $ MAGNUS-> prefixclause ORDER BY LENGTH (prefix) DESC LIMIT 1 ";

The $ MAGNUS-> id_plan variable is the customer’s plan id, and the $ MAGNUS-> prefixclause variable is the filter used to find the tariff.

To facilitate understanding we will explain it in another way.

As the customer called 551140045678, the system will search as follows.

Is there a tariff for the prefix 551140045678?

• If yes, we use this rate
• If not, we will search again by removing the last number, in this case 55114004567 and so on until we reach only number 5. If we do not find any rate, the system will return an error and will not continue the call.

Going back to “SELECT” it would look like this.

$ sql = "SELECT * FROM pkg_plan LEFT JOIN pkg_rate ON pkg_plan.id = pkg_rate.id_plan
LEFT JOIN pkg_prefix ON pkg_rate.id_prefix = pkg_prefix.id
LEFT JOIN pkg_trunk_group ON pkg_trunk_group.id = pkg_rate.id_trunk_group
WHERE pkg_plan.id = 1 AND pkg_rate.status = 1 AND
(prefix = '551140045678' OR
prefix = '55114004567' OR
prefix = '5511400456' OR
prefix = '551140045' OR
prefix = '55114004' OR
prefix = '5511400' OR
prefix = '551140' OR
prefix = '55114' OR
prefix = '5511' OR
prefix = '551' OR
prefix = '55' OR
prefix = '5')
ORDER BY LENGTH (prefix) DESC LIMIT 1 ";

Let’s say you have the following 3 tariffs in your customer’s plan

55, Brasil Fixo Geral 55119, Brazil SP Celular 5511, Brazil SP Fixed

In this example, “SELECT” would return tariff 5511, Brasil SP Fixo.

But you may be wondering if the customer calls 5511988551234, the “SELECT” would find the prefix 55119 and 5511 and then which one would use? I would use 55119, because in “SELECT” there is the parameter ORDER BY LENGTH (prefix) DESC and also the parameter LIMIT 1. That is, it will order the results by placing first the prefix that has the largest number of digits.