Documentation
(→Selling tables) |
(→Selling tables) |
||
| Line 20: | Line 20: | ||
[[File:TableRelationship.png|700x700px|center]] | [[File:TableRelationship.png|700x700px|center]] | ||
| − | == | + | == Generation of client rate tables == |
| − | x164 allows to | + | x164 allows to generate rate tables for customers from outgoing carrier rate tables. x164 consolidates the carrier rates into one rate table following a strict longest-code-match logic. The rate table can be further simplified by filtering for quality or consolidating prices within tolerances to one code/price. |
| + | |||
| + | The operation takes the following parameters as inputs: | ||
{|border="1" cellpadding="2" | {|border="1" cellpadding="2" | ||
| − | |qACD || Minimum Average Call Duration | + | |qACD || Minimum Average Call Duration (seconds). |
|- | |- | ||
| − | |qASR || Minimum Success Rate | + | |qASR || Minimum Success Rate (seized calls / total calls) |
|- | |- | ||
| − | |tolerance || Margin to consider | + | |tolerance || Margin to consider prices as equal (%) |
|- | |- | ||
|max_code_lenght || Maximum length of the destination field to be kept in the final table | |max_code_lenght || Maximum length of the destination field to be kept in the final table | ||
| Line 47: | Line 49: | ||
[[File:secretKey.png|center|800x800px]] | [[File:secretKey.png|center|800x800px]] | ||
| − | After this initial filtering the system searches the best price for each destination. The parameters qACD and qASR are used as to filter out low quality routes. For a given destination, if there are routes that satisfy this criteria, the cheapest one will be picked. In case there are | + | After this initial filtering the system searches the best price for each destination dialing code. The parameters qACD and qASR are used as to filter out low quality routes. For a given destination, if there are routes that satisfy this criteria, the cheapest one will be picked. In case there are no routes with above minimum quality, the one with the best quality will be selected. At the end of this process there will be one record for each destination. Then all records with price lesser than rateMin are set to rateMin. |
In the next steps, rates will be simplified using the tolerance parameter. First redundant codes will be removed if shorter codes exist within their price tolerance. As an example, consider the following record: | In the next steps, rates will be simplified using the tolerance parameter. First redundant codes will be removed if shorter codes exist within their price tolerance. As an example, consider the following record: | ||
| Line 59: | Line 61: | ||
3716 26 | 3716 26 | ||
| − | In the second stage of the | + | In the second stage of the simplification process, the system looks for sets of ten consecutive codes. If they are within the same tolerance range, a new rate is created using the information of the most expensive record in the set and the shorter destination that matches all the consecutive destination. For example, for the following set: |
Destination Price | Destination Price | ||
Revision as of 17:13, 6 November 2012
Here are a few links you can start from. In addition, you might want to take a look at the list of all categories or all pages.
x164 Introduction
x164 consists of a central database which holds private and public rate tables, statistics and route configurations. Many x164 switches connect to the central x164 database to retrieve routing information and to update the statistics in realtime. Softswitches are preferably, but not limited to, yate-based switches. Call detail records are collected with radius. Call details, statistics and the configuration screens are accessible by the web access.
x164 Call flow
When receiving a call, the softswitch queries the database for user authentication. This can be made either by IP for fixed IP customers, or user/password for dynamic IP customers. After a positive authentication, a new query is issued to verify there is an incoming rate linked to the user for the dialed number. If there is no rate for the dialed number, the call is blocked. Otherwise the softswitch looks up the database for outgoing or DID routes. In case they don't exist, or if those available are not profitable, the call is blocked. When many valid routes exist, the system picks the most profitable, taking in account its rate and statistics. When the call finishes, the softswitch updates the CDR database and the route's statistics.
Rates database
The rates database uses the Profiles table to link customers, carriers an DID clients to their corresponding set of allowed routes in the Rates table. There are three types of profiles: incoming, carrier and DID. Incoming profiles link customers in the Customers table with a set of incoming routes. It is possible to create many records of the same incoming profile, allowing the user to dynamically choose between different rate sets by dialing a prefix (for more information look in the Profiles table documentation and in the HowTo page). Carrier profiles link Carriers with outgoing routes. DID profiles are used to define ranges of own numbers in the Rates table that are routed inside your network (you can find a guide about how to set DID clients here).
The Rates table defines the rate for each route and stores information about route's statistics. Statistics are used to measure route's profitability in terms of incoming versus outgoing rate and average call duration.
Generation of client rate tables
x164 allows to generate rate tables for customers from outgoing carrier rate tables. x164 consolidates the carrier rates into one rate table following a strict longest-code-match logic. The rate table can be further simplified by filtering for quality or consolidating prices within tolerances to one code/price.
The operation takes the following parameters as inputs:
| qACD | Minimum Average Call Duration (seconds). |
| qASR | Minimum Success Rate (seized calls / total calls) |
| tolerance | Margin to consider prices as equal (%) |
| max_code_lenght | Maximum length of the destination field to be kept in the final table |
| minRate | All rates where the price is lesser than minRate will be updated to minRate |
| profit | Profit margin to add to the price in % |
| secret_key | Used to select the reseller_id in the gateways table |
| rateDate | Date for which the rates will be retrieved. |
The rateDate input filters rates by date, and secret_key by reseller_id. The relationship with the rates table is as follows:
After this initial filtering the system searches the best price for each destination dialing code. The parameters qACD and qASR are used as to filter out low quality routes. For a given destination, if there are routes that satisfy this criteria, the cheapest one will be picked. In case there are no routes with above minimum quality, the one with the best quality will be selected. At the end of this process there will be one record for each destination. Then all records with price lesser than rateMin are set to rateMin.
In the next steps, rates will be simplified using the tolerance parameter. First redundant codes will be removed if shorter codes exist within their price tolerance. As an example, consider the following record:
Destination Price 371660 25
With a tolerance of 15%, this code would be deleted from the final selling table if a shorter match with price within the range [22.5 - 28.5] is found, such as:
Destination Price 3716 26
In the second stage of the simplification process, the system looks for sets of ten consecutive codes. If they are within the same tolerance range, a new rate is created using the information of the most expensive record in the set and the shorter destination that matches all the consecutive destination. For example, for the following set:
Destination Price 3710 50 3711 50 3712 45 3713 51 3714 47 3715 48 3716 49 3717 50 3718 47 3719 46
First the system makes sure the shorter code 371 doesn't already exist in the database. If it doesn't, it checks that all the prices are within the tolerance range. In the previous table case, for a tolerance of 15%, the cheapest price upper limit would be 45*(1+15/100) = 51.75, which is superior to the highest price in the set. Thus, this set is removed from the final table and a new record is created:
Destination Price 371 51
Once the simplification process is finished, the rates with destination longer than the max_code_length parameter are deleted. Finally, all the prices are increased according to the profit percentage specified in the input.
Web Access
x164 provides its members a web access to configure their settings, upload their rate tables and view call statistics. The interface and rating-engine is derived from CDRTool, which we modified heavily for speed and simplification.
Description
The call search offers access to call-detail-records (CDRs). CDRs can be summarized and filtered for various statistics.
Selection fields
| Data source | Database where are the call details |
| Start time | Beginning of the range of time in search |
| Stop time | End of the range of time in search |
| Sip call / Sip source | Filter by the user ID, received from the customer or proxy |
| User agents / Media code | Filter by the format of the call |
| Sip Billing Party (username) | Search the calls of a subscriber |
| Sip Caller Party (From URI) | Filter using the ID received from the caller |
| Sip destination (Canonical URI) | Filter by destination of the call |
| Duration / Application / status | Filter selecting different call duration, media or the status of the call |
| Order by / Group by | Complements the search ordering results, change the limit of records to show, grouping these and if ReNormalize is marked the system recalculate the call prices |
Access to all data influencing the rating and routing. The rating data is contained in several tables which can be selected on the top-right side. The cost calculation of the calls stored in the system occurs in parallel and periodically using the values that the user introduce in this tables.
Customers table
Description
The Customers table shows details of your authorized customers who may send calls through the x164 system. The user can be authorized by trusted peer or subscriber and password.
Data fields
| Id | Automatically generated id field |
| Reseller | Numeric ID of reseller |
| Trusted Peer | Authorized IP address, in this case the user is authorized by IP |
| Domain | Not used |
| Dynamic_ip | Use dynamic IP when value set to 1 |
| Subscriber | Subscriber name in the format "user@domain" used as billing party in the rate process |
| Password | Password for authorized users |
| Profile | Profile name which links the customer to a profile in the profile table |
| Subscriber_out | In case subscriber also provides outgoing routes put outgoing subscriber name here to avoid looping calls |
| Blocked | Block the subscriber when value set to 1 |
| Pre_location | Parameters that are added before the called number in the outgoing message (possible values: SIP/00 to identify a SIP carrier with prefix of 00 in front of the e164 number) |
| Post_location | URL and parameters added to the called number in the outgoing message, e.g. @carrier.com |
| The outgoing route is defined as: pre-location + e164 number + post-location, e.g. SIP/004930123456789@carrier.com | |
| Expires | Expiration time of the dynamic IP |
| Formats | Possible codec formats separated by comma (possible values: g729, ulaw, alaw, t38) |
| rtpProxy | 1 activates rtp proxying for this route |
Carriers table
Description
The Carriers table has the details of your carriers to send calls to them.
Data fields
| Id | Automatically generated id field |
| Reseller | Numeric ID of reseller |
| Domain | Not used |
| Carrier_name | Carrier name in the format "user@domain" used as billing party in the rate process |
| Profile | Profile name which links the customer to a profile in the profile table |
| Blocked | Block the subscriber when value set to 1 |
| Pre_location | Parameters that are added before the called number in the outgoing message (possible values: SIP/00 to identify a SIP carrier with prefix of 00 in front of the e164 number) |
| Post_location | URL and parameters added to the called number in the outgoing message, e.g. @carrier.com |
| The outgoing route is defined as: pre-location + e164 number + post-location, e.g. SIP/004930123456789@carrier.com | |
| Line | Section identifier from accfile.conf, this can be used instead of pre-location, post-location |
| Formats | Possible codec formats separated by comma (possible values: g729, ulaw, alaw, t38) |
| rtpProxy | 1 activates rtp proxying for this route |
Profiles table
Description
The profiles table shows the rate details assigned to customers which will be used to calculate the price of the call by x164 system,
Data fields
| Id | Automatically generated id field |
| Reseller | Numeric ID of reseller |
| Profile | Profile name which links the customer to the profile table |
| Rate | Rate name which identifies the rates used for this profile in the rates table |
| Starthourweek | Start hour in the week of this tariff (possible value range 0 - 167) 0 hour is Sunday 0:00 hour in local time |
| Endhourweek | End hour in the week of this tariff (possible value range 0 - 167) 0 hour is Sunday 0:00 hour in local time |
| Penalty | Constant price added to this rate. Used to penalize a tariff. |
| Timezone | Timezone must reflect the local time of the tariff in the rating profile table. The setting is used to correct the CDR timestamps coming from the softswitch which should be in GMT time. Use timezone identifier. |
| Incr | Used to consider the duration of the call in increments (default 1 second) |
| Min_Dur | Minimum duration time charged of the call, only activated when the call is successful |
| Curr_code | Currency used in iso format (EUR, USD ...), if you want use your own values the format should be iso and adding reseller number |
| Carrier | Possible profile types: IN : for incoming routes |
| Tech_Prefix | Technical prefix used to identify incoming rate table. Different tech prefixes allow to link several rate tables to one incoming customer, e.g. to let the client chose dynamically grey, standard, premium rate tables. Prefixes must have two digits and when dialing they are used as follows: (prefix)#(called number) |
Rates table
Description
The rate table shows details to calculate the price by x164 system according to valid tariff and destination
Data fields
| Id | Automatically generated id field |
| Reseller | ID number |
| Rate | ID of the rate |
| Destination | Destination number to this rate |
| Region | Region of this number |
| Description | Description of the destination to the number |
| App | Application Type: audio, sms |
| Conn | Price of the call connection |
| Price | Price per minute |
| Conn In | Cost of the call setup, to calculate the profit of the call |
| Price In | Cost per minute, to calculate the profit of the call |
| Start Date | The start time of the rate |
| End Date | The end time of the rate |
| Blocked | Lock indicator of the rate |
| Billtime_sum | Sum of billed time of this rate |
| Ringtime_sum | Sum of ring time of this rate |
| Setuptime_sum | Sum of setup time (until ringing) of this rate |
| Calls_count | Number of calls of this rate |
| Success_count | Number of connected calls |
| Acd_short | Short-term average call duration, updated after each call with acd_short = ((5 * acd_short) + _billtime)/6, |
| Acd_avr | Long-term average call duration, updated after each call with acd_avr = (acd_short + (billtime_sum / success_count)) / 2 |
Gateways table
Description
The gateways table serves to identify the IP address of the switches used by the reseller.
Data fields
| Id | Automatically generated id field |
| Reseller | ID number |
| Gateway | IP address of the switch |
| Secret | Switch's password |
DIDclients table
Description
The DIDclients table shows the DID clients with their numbers.
Data fields
| Id | Automatically generated id field |
| Reseller | ID number |
| DID_number | Number of the client |
| DID_subscriber | Subscriber who belongs this number |
Currencies table
Description
The currencies table shows the currencies used by x164 system. They are used in the routing process to look for the most appropriate route.
Data fields
| Id | Automatically generated id field |
| Reseller | ID number |
| Domain | Not used |
| Currency | Currency id in the system, use ISO format adding reseller number to be unique in the system |
| Curr_rate | Value of the currency, against the euro |
Quota table
Description
The quota table stores the billed sum of calls made by each subscriber. The table allows to set a monthly quota limit. When the limit is reached the subscriber is blocked. The usage is reset at the beginning of each month. Un-blocking of subscribers has to be done manually.
Data fields
| Id | automatically generated id field |
| Reseller | ID number |
| Subscriber | Subscriber name in the format "user@domain" |
| Domain | Subscriber domain |
| Quota | Quota limit per month |
| Notified | Date on which the system detected the exceeded limit |
| This Month | Monthly cumulative consumption |
| Today | Daily cumulative consumption |
CSV import and export
With the x164 web access you can add many records at a time to a table using CSV files. Often rates, customers and other information will be available in a spreadsheet software like Excel or Calc, which supports CSV export. In order to import this data into the database, you have to organize it to fit the x164 format. The first row of the spreadsheet must have the same field headers as the destination table. Since CSV files use line breaks to separate records and commas to delimit fields, you must ensure those are not present in your data. The name of the CSV file should always start with the name of the table in lowercase and an underscore "_", for example, a file imported to the Rates table can be named: rates_1.csv. To export a spreadsheet as a CSV file in OpenOffice, simply go to File -> Save As and in the opened dialog select "Text CSV (.csv)" as the file type. Once you have exported your spreadsheet to CSV it will look like this:
"Ops","Reseller","Rate","Destination","Region","Description","App","Conn","Price","Conn In","Price In",'Start Date","End Date","Blocked", "Billtime_sum","Ringtime_sum","Setuptime_sum","Calls_count","Success_count","Acd_short","Acd_avr" 2,3,PREMIUM,34,,Spain prefix premium,audio,,120,,,2008-01-01,2022-01-01,0,3412.43,326.693,305.535,13,12,254.665,269.517 2,3,PROFCARRIER,34,,Spain,audio,,65,,,2008-01-01,2022-01-01,0,3000,300,300,10,10,300,300
To import a file to the database first click in the 'Choose a file' button on the right of the involved table and select your file. Then click Import. When the file is correctly uploaded a confirming message will be prompted. The imported data will not be available immediately since the database is updated every X minutes.
You can also export data from the database in CSV format by clicking on the 'Export table.csv' button on the right side of a table. A new browser window will be opened showing the CSV text.
Softswitch
The x164 switch is based on yate, In its basic configuration the switch can handle SIP and H323 calls. IAX, ISDN, SS7 calls are theoretically possible. Users can activate further yate modules to extend the possibilities. x164 members are encouraged to post their suggestions and ideas on the [ http://forum.x164.com/ forum]. The yate configuration is located in /usr/local/etc/yate.
Server settings
Time zone
The server running yate should have its timezone set to GMT time if the timezone setting is used in the rating customers table. The timezone setting in the rating customer table must reflect the local time of the tariff in the rating profile table. The timezone setting is used to correct the CDR timestamps coming from the softswitch. The Starthourweek and Endhourweek time ranges in the rating profiles table are always in local time.
CDR module - cdrbuild.conf
Description
This module builds the CDR messages (Call Details Record) which are used later in the radius module. Here are set the necessary attributes to create the correct CDR messages.
h323 Channel module - h323chan.conf
Description
This module gives support to h323 protocol by using the open h323 library.
Mysql database module - mysqldb.conf
Description
This module is used to make the connection between yate and a MySQL database. Establishing the necessary parameters as host, port, database and password.
Radius module - yradius.conf
Description
This module give support to establish communication between Yate and FreeRADIUS, as well the ip address and port of the FreeRADIUS server. Are set the specific attributes to sent in the CDR message and are assigned the correct values.
Regexroute module - regexroute.conf
description
This module is used in addition to the register module, to assign values of different parameters in the call as the error messages or the formats to use in transcoding calls, used to route calls without using database.
Register module - register.conf
The register module defines the messsage handlers which are communicating with the x164 database. Message handlers "catch" internal yate messages, issue queries to the database and return the result back to yate. x164 is configured to use mysql stored-procedures as message handlers.
Authuser
Description
User_Auth is used to identify the subscriber.
Statement
PROCEDURE `proc_user_auth`( IN username VARCHAR(100), // username received in SIP INVITE IN domain VARCHAR(100), // domain name from caller IN address VARCHAR(25) // IP address and port of the received call IN password VARCHAR(25) // secret key for MySQL procedures in Yate )
Register
Description
This query handle register messages, add in database data of register users and the last time that the user was registered.
Statement
PROCEDURE `proc_user_register`( IN ip_host VARCHAR(30), // IP address received in SIP INVITE IN ip_port INT, // IP port received in SIP INVITE IN expires INT, // register expiration time IN authname VARCHAR(125) // authorized subscriber name corresponding in data base IN password VARCHAR(25) // secret key for MySQL procedures in Yate )
Unregister
Description
This query handle unregister messages initializing the data entered by register message.
Statement
PROCEDURE `proc_user_unregister`( IN authname VARCHAR(125) // authorized subscriber name corresponding in data base IN password VARCHAR(25) // secret key for MySQL procedures in Yate )
Engine timer
Description
The engine timer found that users have exceeded the time of registration and invalidate the dynamic_ip.
Statement
PROCEDURE `proc_user_enginer_timer`( IN password VARCHAR(25) // secret key for MySQL procedures in Yate )
Preroute
Description
Preroute checks whether an incoming route for the combination of IP address and called number or authname and called number exists. The caller is either identified by IP address or was identified by authname/password combination in authname procedure. If no incoming route exists the call is blocked.
Statement
PROCEDURE `proc_get_preroute`( IN authname VARCHAR(125), // authorized subscriber name corresponding in data base IN address VARCHAR(25), // IP address and port received in SIP INVITE IN called VARCHAR(100) // the called number, optionally with prefix IN password VARCHAR(25) // secret key for MySQL procedures in Yate )
Route
Description
This selects the possible outgoing routes according to the incoming parameters, looking for the best quality and profit.
Statement
PROCEDURE `proc_get_route`( IN usernameNobuy VARCHAR(125), // Subscriber_out found in incoming route to prevent call loops IN rtproxy_in TINYINT(4), // 0/1 flag to specify whether the rtp stream of the incoming call leg is proxied. IN realRateSell FLOAT, // (client) call rate found in preroute expressed in the base currency (EUR) IN called VARCHAR(100), // Called number (optionally with prefix). The prefix is not used. IN _format_in VARCHAR(255), // Codecs received in incoming call leg IN password VARCHAR(25) // secret key for MySQL procedures in Yate )
cdr_finalize
Description
This procedure computes the values of the call once finished to update the statistics in the system.
Statement
PROCEDURE `proc_cdr_finalize`( IN duration FLOAT, // the overall call duration IN billtime FLOAT, // the time the call was connected IN ringtime FLOAT, // duration of call ringing IN direction VARCHAR(10), // incoming or outgoing IN status VARCHAR(10), // if the call was connected or not IN rateidBuy BIGINT(20), // id of the rate for the outgoing call leg IN rateidSell BIGINT(20), // id of the rate for the incoming call leg IN password VARCHAR(25) // secret key for MySQL procedures in Yate )
RTP Channel module - yrtpchan.conf
Description
This module provides RTP and udptl transports for any technology that requieres it as SIP or H.323. The RTCP support is disabled.
SIP Channel module - ysipchan.conf
Description
This module give support to SIP protocol using the YASS library. Here also is posible select the value "expires" used in register.conf and the codecs enable to use.
Yate - yate.conf
Description
In this configuration file is selected the modules that are in use, and the parameter "timeout" limits the call duration as 2h.
