We continue our story about the modification of the engine for a VoIP service provider.

In the first part, we talked about the initial database structure and the configuration of Asterisk for call service, with call status monitoring. In this part we will touch on such things as caller, LCR, billing and geolocation.

Tariff

It is no secret to anyone that different telecom operators, depending on their geographic location, have quite a different cost of calls to the same places. In order to make cheap calls in a certain direction, it is necessary to select an operator with a minimum price in the desired region. Depending on the area in which the service provider provides its services, the call tariffing may differ quite strongly. This difference is in how the operator considers the time spent on the conversation. Most Russian telecom operators charge calls per minute, but the vast majority of VoIP operators use other methods to determine the cost of a call. Separate conversation deserve premium services, but more on that later.

Here is an excerpt from the carrier’s tariff plan:

"Minimal Duration- 1 second" "Billing increment- 1 second" "USA -6/6" "Mexico 60/60" "Gambia 60/1" "Tonga All (Prefix 676) - 60/60" "Vanuatu All (Prefix 678)- 60/60 " "Samoa (Prefix 685) - 60/60" "Papua New Guinea (Prefix 675)- 60/60 " "Nauru All (Prefix 674)- 60/60 " 

As we can see, the minimum talk time is one second, and the cost of the call is calculated from the number of seconds spent (1/1), the call to the United States of America is charged in 6 seconds steps (6/6), the call to Mexico is charged in 60 steps seconds, i.e. per minute (60/60). The formula 60/1 can also be used, which means the per-second billing of a call, but with full payment of the first minute of the call. For example, if a minute of conversation costs 0.37USD and you talk for 28 seconds, then the cost of your call will be 0.37USD, and if you talk for 76 seconds, the cost of your call will be (0.37 / 60) * 76USD.

The operator of fixed, mobile or VoIP communications, giving you per-minute billing in the regions with a per second call charge, earns this bread by this difference. The margin is of course small, but together with the increase in tariff for the end user, it turns out as in the joke "twenty old women - the ruble." And very few operators allow themselves to sell per-second billing to the end user.

Tariff grids of operators are based on prefix addressing of a call. The prefix is ​​usually the first few digits of the phone in international standard. This means that for each specific operator the call in a certain direction may differ by several times. This happens both because of the method of delivery of the call to the called subscriber, and because of the introduction of local telecom operators into the numbering capacity, the so-called "premium" or "special" services. "Premium" services are usually called reference (where to buy, how to drive, etc.), or entertainment services (horoscopes, shop on the couch, phone sex, etc.), access to which is provided on a fee basis at elevated rates. . If the telecom operator does not control outgoing calls from its systems or doesn’t timely update the tariff plans of its partners, then there are cases of gigantic debts due to accidental resolution of calls to premium services without monitoring the client’s personal account.

How does this happen in real life?

Tinkering with the operator's “engine”, we faced the fact that the old “engine” did not track the direction of calls and charged the client “in fact”, with a call time limit of 3,600 seconds. Those. the client could call the phone "premium" service and "hang up" the session for 1 hour. With the cost of a call at $ 5 per minute, the operator fell at $ 300 per hour. And since the number of simultaneous calls was not limited, the losses could be large amounts. Due to the fact that telecom operators only entering the market are trying to attract new customers, they give "test" access to new users for a certain virtual amount. The user makes a call to the right place for him, assesses the quality of communication, and if everything is good, he begins to replenish his "personal" wallet with this telecom operator. This type of access is used by froders who are in collusion with the owners of the "premium" service. Froder, using the gaps in the tariffing system of operators, makes several calls to paid services, thereby exposing the operator to money. At the same time, the cost of a Froder’s call to a paid service is almost zero, and profits can reach huge amounts. The carrier detects the problem either in the morning or after receiving a notification about the exhaustion of money in his account. It’s almost impossible to dispute the perfect call that went through the chain of operators and reached the “premium” service. payment for the session goes through the entire chain of operators.

Consider the examples of "premium" services and how they look in the tariff scale.

If you look closely at the table, we will see that the number 37122705678 falls under three tariffs 37122.371227.3712270. If it is incorrect to process the beginning of the number or do not update the tariff plan in time, instead of the expected call for a ruble or 84 kopecks, we will receive a call with a price of more than 30 rubles per minute. This is the loophole and attackers enjoy. Therefore, to exclude such scenarios, we have divided all the prefixes into 14 categories.

And when they launched a bundle into work, they simply limited the test calls to some of the controversial categories. If the user contributed his money to the account, then the blocked categories were automatically opened. This reduced fraud levels by almost 99%. The remaining percentage was suspended in the air due to the time lag in updating the tariffs of the partners. When the tariff grid was updated, those prefixes that were not defined in the table “dropped out” from the general list and after analysis by the operator were tied to the desired category.

LCR (Least Cost Routing) - “Least Cost Routing”

Due to the fact that the system we designed meant connecting to a large number of operators, the question arose of choosing the routes with the lowest cost per minute.

The tariff grid we already had and it was about 700.000 records.

It is clear that the search for 700.000 records is expensive, and with an increase in the number of operators, the number of records would have noticeably grown. Therefore, we searched for many ways to speed up the search for a route in the tariff scale.

Attention! The number 79031210011 is taken as an example and is not mine. The prices for calls in the tables are from April 2016 (the last dump of the base)

The main problem with the search was that we had to find a longer number among the shorter prefixes. And after that, find the longest prefix for each operator from the search results. And for this there are two ways:

 # select * from rates r where '79031210011' like CONCAT(r.prefix,'%') # select * from rates r where INSTR('79031210011',r.prefix) = 1 #  ,    select prefix,pricerub,note,tarif_id from rates r where '79031210011' like CONCAT(r.prefix,'%'); /*  : 0  : 15 : 0  1 : 0,421 sec. */ select prefix,pricerub,note,tarif_id from rates r where INSTR('79031210011',r.prefix) = 1; /*  : 0  : 15 : 0  1 : 0,453 sec. */ 

The answer was as follows:

The search speed of half a second (test environment) is not that completely critical, but it looks weird. Therefore, to optimize the search, we have carried the prefix field to a separate table, making it key for the rates table.

 select * from rates_prefix r where '79031210011' like CONCAT(r.prefix,'%'); /*  : 0  : 4 : 0  1 : 0,172 sec. */ 

As we can see, the search speed increased about three times. That, in principle, is not so bad. Next, the resulting prefix table is "docked" to the tariff table of the operators, and the extra "short" prefixes are removed from the resulting table. In the same place, a table of operators is docked and at the exit we have a full list of routes with prices and prefixes, which is tied to a telecom operator.

 call usp_asteriskfastpathtest('79031210011','test_user',0); /*  : 0  : 17 : 0  1 : 0,140 sec. */ 

Or, for example, a list of routes to the Hotel Crystal Hotel in America with the number 13606632262

Naturally, in the formation of the dial-up list, along with the sorting of prefixes in the tariff scale, quite a lot of additional conditions are used. This is the date of the beginning / end of the tariff, CLI support, manual locks and others. The final table, before entering into the database, is additionally processed for the set limit on the maximum cost of the call. It makes no sense to allow calls for 300 rubles for a user who has only 100 rubles on the account.

All this imposes its cost on performance. On the combat system, we received approximately 50ms per request.

Billing

As we all understand, there are no systems for providing services without calculating the cost of the services provided. We have been thinking for a long time how to optimally control the state of the user's wallet and, in our opinion, have found a compromise solution.

At the end of the first part, I described the configuration of the Asterisk server in terms of adding service functions and channel state monitoring functions. The same functions allow you to clearly track the status of the call for the client.

As can be seen in the table below.

we always have information about the current tariff of the operator, the currency of the operator, the tariff of the client, the direction and other. Which in turn turns into the following table.

And it is dial_guid that is returned by the make_a_route.py application to the context [make_a_call] , and the step field is used to refine the current iteration.

When Asterisk begins to prepare a communication channel for connecting subscribers, it calls the create_channel_record.py application, which forms an empty record in the CDR table that contains no information other than the channel creation time, the user ID, and dial_guid + step. When the callee picks up the handset, the predial.py application is called , which fills the table fields responsible for picking up the phone and charging the call. If the conversation ends correctly or an error occurs, the application hangup.py is called , which “closes” the CDR record in the database, filling in the field with the end time of the conversation. Thus, it turns out that erroneous calls that for some reason were not made do not fall into the user's billing, but remain in the system logs. A trigger is set on the table with the temporary CDR information, which expects changes in the rows where the pickup time and the end time of the call are not zero. As soon as the given trigger sees the condition described above, it transfers the data from the temporary CDR of the table to the main one and writes the amount spent to the client to the account.

By the way, there is another table in which the names of the channels serving the current calls and associated with dial_guid are stored. Due to the fact that in realtime we see all the current conversations in the database, a trigger was triggered in the database every 5 seconds and correlating the current ongoing conversations with their tariffs and the balance in the client’s account.

As soon as the money in the client’s account remains for less than 1 minute of conversation, the trigger places in the special table the number of the channel that initiated the call. The service appication server, located on each of the Asterisk servers, monitors this table. As soon as he sees there a line addressed to his address, the Originate application is called with a redirection to the context with the phrase "not enough money" and the conversation ends. After the end of the conversation, the regular procedures change the balance of the client. This scheme is beautiful because it keeps track of all conversations carried out from the client’s account on all servers.

Conversation monitoring looks like this.

Multicurrency Recalculation

In essence, the technology described above allows building multi-currency billing within one system. Speaking of currency. Since the cost of calls through foreign operators is calculated in dollars or euros, it is necessary to determine in what currency the client works. And due to the fact that the currency rate changes every day, it is necessary to ensure the conversion of currencies.

This makes the simplest Python script.

 # -*- coding: utf-8 -*- import urllib from sqlconfig import * import mysql.connector import json # Init mysql connection cnx = mysql.connector.connect(**config) cursor = cnx.cursor() cursor.execute('SET AUTOCOMMIT=1;') cursor.execute('SET collation_connection=\'utf8mb4_unicode_ci\';') currency_pairs = [ ('EUR','USD'),('EUR','RUB'),('EUR','UAH'),('EUR','KZT'),('RUB','USD'),('RUB','UAH'),('RUB','KZT'),('USD','UAH'),('USD','KZT') ] for base in currency_pairs: (fromcur,tocur) = base root_url = 'http://www.bloomberg.com/markets/api/security/currency/cross-rates/%s,%s'%(fromcur,tocur) f = urllib.urlopen(root_url) myjson = json.loads(f.read()) pair = myjson[u'data'] for fromcur in pair: for topair in pair[fromcur].items(): (tocur,crossrate) = topair cursor.execute('INSERT INTO currency_cross (getcrosstime,from_currency_id,to_currency_id,crossrate) VALUES (now(),(select id from currency where iso="%s"),(select id from currency where iso="%s"),"%s")'%(fromcur,tocur,crossrate)) print fromcur,tocur,crossrate cnx.commit() cursor.close() cnx.close() 

He forms a table of cross-courses for each day.

Thus, depending on the main currency in which the calculation is made, we can recalculate the client's funds and bring the tariffs to the desired currency.

Call geolocation

Geolocation is quite an important part of various services that communicate with customers around the world. It is always nice on the phone to see the number of the caller coinciding with your location. How it works ?

The domestic regulatory authority in each country, in addition to the international numbering plan, as well as the prefix of a particular country, divides its territories into counties, regions, districts, cities and towns, assigning each of them an additional number in the dialing prefix.

Major cities (million-plus cities) such as Moscow, St. Petersburg and others have a seven-digit numbering plan, cities of regional significance - six figures, cities of regional significance - five-digit, etc.

Inside the numbering plan of the Russian Federation is divided into:

• Federal District
• City of Federal Value
• City
• Region
• Area
• settlement, village, settlement, etc.

Inside the numbering plan of the United States of America is divided into:

• Area
• State
• District
• County
• City

Thus, knowing the phone code, you can most likely find out the location of the object, with the exception of cellular phone numbers that have been migrated from one operator to another, as well as services providing geolabeized number services.
For Russian numbers on the Rossvyaz website , in free access, there are several tables describing the number binding by regions and federal districts. The affiliation of a cell phone number with an operator can be clarified on the website Tsniis .

For other countries, you can also find information on the website of local regulators.
Collecting and clarifying information about the prefixes used is a very time consuming business. Therefore, there are practically no complete bases in the public domain. And those that do not always contain the necessary information. But as in most situations, there is a small livehack.

IP telephony operators providing their tariffs, usually include in the form of a structured description, the location that serves the prefix.

We will not go far and take the first lines of the operator's tariff plan:

 "prefix","comment","price","connect_cost","increment","custom","created_at", "9375","AFGHANISTAN - CDMA","0.388125","0.0000","60","0","2015-10-11 23:21:51", "9340","AFGHANISTAN - HERAT","0.388125","0.0000","60","0","2015-10-11 23:21:51", "9360","AFGHANISTAN - JALALABAD","0.388125","0.0000","60","0","2015-10-11 23:21:51", "9320","AFGHANISTAN - KABUL","0.388125","0.0000","60","0","2015-10-11 23:21:51", "9330","AFGHANISTAN - KANDAHAR","0.388125","0.0000","60","0","2015-10-11 23:21:51", "9350","AFGHANISTAN - MAZAR-E-SHARIF","0.388125","0.0000","60","0","2015-10-11 23:21:51", "937","AFGHANISTAN - MOBILE","0.388125","0.0000","60","0","2015-10-11 23:21:51", "9377","AFGHANISTAN - MOBILE - AREEBA","0.32751","0.0000","60","0","2015-10-11 23:21:51", "9370","AFGHANISTAN - MOBILE - AWCC","0.32751","0.0000","60","0","2015-10-11 23:21:51", "9378","AFGHANISTAN - MOBILE - OTHER CARRIERS","0.32751","0.0000","60","0","2015-10-11 23:21:51", "9379","AFGHANISTAN - MOBILE - ROSHAN","0.310365","0.0000","60","0","2015-10-11 23:21:51", "93","AFGHANISTAN - PROPER","0.388125","0.0000","60","0","2015-10-11 23:21:51", "35568","ALBANIA - MOBILE - AMC","0.456165","0.0000","60","0","2015-10-11 23:21:51", "35567","ALBANIA - MOBILE - EAGLE","0.452925","0.0000","60","0","2015-10-11 23:21:51", "35569","ALBANIA - MOBILE - VODAFONE","0.47007","0.0000","60","0","2015-10-11 23:21:51", "355","ALBANIA - PROPER","0.08046","0.0000","60","0","2015-10-11 23:21:51", "3554","ALBANIA - TIRANE","0.08046","0.0000","60","0","2015-10-11 23:21:51", "35541","ALBANIA - TIRANE","0.08046","0.0000","60","0","2015-10-11 23:21:51", "35542","ALBANIA - TIRANE","0.03861","0.0000","60","0","2015-10-11 23:21:51", "35543","ALBANIA - TIRANE","0.08046","0.0000","60","0","2015-10-11 23:21:51", "35544","ALBANIA - TIRANE","0.08046","0.0000","60","0","2015-10-11 23:21:51", "35545","ALBANIA - TIRANE","0.08046","0.0000","60","0","2015-10-11 23:21:51", "35546","ALBANIA - TIRANE","0.08046","0.0000","60","0","2015-10-11 23:21:51", "3554249","ALBANIA 0 OLO GROUP","0.04401","0.0000","60","0","2015-10-11 23:21:51", "7997","RUSSIA (MOBILE)","0.07938","0.0000","60","0","2015-10-11 23:21:51", "78182","RUSSIA - ARKHANGELSK","0.09558","0.0000","60","0","2015-10-11 23:21:51", "73512","RUSSIA - CHELYABINSK","0.055755","0.0000","60","0","2015-10-11 23:21:51", "74932","RUSSIA - IVANOVO","0.055755","0.0000","60","0","2015-10-11 23:21:51", "73412","RUSSIA - IZHEVSK","0.055755","0.0000","60","0","2015-10-11 23:21:51", "78432","RUSSIA - KAZAN","0.052515","0.0000","60","0","2015-10-11 23:21:51", "73912","RUSSIA - KRASNOYARSK","0.052515","0.0000","60","0","2015-10-11 23:21:51", "7813","RUSSIA - LENINGRAD REGION","0.055755","0.0000","60","0","2015-10-11 23:21:51", "79","RUSSIA - MOBILE","0.07938","0.0000","60","0","2015-10-11 23:21:51", "7964","RUSSIA - MOBILE - BEELINE","0.13311","0.0000","60","0","2015-10-11 23:21:51", "7965","RUSSIA - MOBILE - BEELINE","0.13311","0.0000","60","0","2015-10-11 23:21:51", "792","RUSSIA - MOBILE - MEGAFON","0.07938","0.0000","60","0","2015-10-11 23:21:51", "791","RUSSIA - MOBILE - OTHER CARRIERS","0.07938","0.0000","60","0","2015-10-11 23:21:51", 1531,US,"United States - OnNet - NE - 531",0.008100,6,6,0.008100,Unchanged,USD,11-Apr-2016,22:00:00,18-Apr-2016,22:00:00 1603,US,"United States - OnNet - NH - 603",0.008100,6,6,0.008100,Unchanged,USD,11-Apr-2016,22:00:00,18-Apr-2016,22:00:00 1201,US,"United States - OnNet - NJ - 201",0.008100,6,6,0.008100,Unchanged,USD,11-Apr-2016,22:00:00,18-Apr-2016,22:00:00 

As you can see from this data, there is a binding code to the country and type of communication.

• PROPER - Main country code
• FIXED - fixed connection
• MOBILE is a mobile telecom operator. usually if his name is known, it follows
• CITY - if the code refers entirely to the city, then its name is written
• OLO - an abbreviation used to refer to local operators (OTHER LOCAL OPERATOR)
• OTHER - other types of communication
• IPRS —
• PREMIUM —
  etc.

, , . . , , .

, , . 3 . . https://sanstv.ru/codes/ , , , . . , 2 . . . , . .

.

© Aborche 2017