CDRTool rating engine
---------------------

CDRTool provides on-the-fly rating of CDRs from multiple data-sources like
OpenSER, Asterisk or Cisco gateways based on an easy to build rating plan,
which can be imported from external data sources.

A Call Detail Record (or CDR) is one record from the radius radacct table.
The CDR record contains all information related to a session, like session
duration, calling, called party and media information. The rating engine
calculates the price of the session, which is applied in real-time to the
web output and saved to the database containing the CDRs.

Based on exceptions, different rates may be applied per caller party
identified by source IP, domain or subscriber.

Rating is applied only after the call has ended. CDRTool considers that a
call has ended when there is a stop time. In case of missing BYEs, CDRTool
relies on the fact that MediaProxy will update the CDR with the proper stop
time information based on the last moment the media stream passed through
the media proxy.

The rates are linked with profiles corresponding with different time of the
day, day of the week or holidays. For rating calls, which span multiple
profiles, the right rate is selected and applied for the call duration
within each profile. Each customer may be assigned its own dedicated rating
plans destination id and names. Chained profiles are possible to enable
exception based rating. Multiple customers may share a common rate list,
while some destinations may be rated differently, only the differences must
be provisioned. Multiple time zones are supported for multiple billing
parties hosted on the same platform.


Determination of application type
---------------------------------

The only supported application type is 'audio'.


Determination of the billing party
-----------------------------------

CDRTool identifies the rating plans associated with a session based on a
Billing Party entity, which is calculated on the fly. The calculus is based
on the first successful match of the BillingPartyId field from the CDR
database (radacct.UserName for radius data sources) in the following order:

1. SIP account user@domain 
2. SIP domain of the SIP account
3. Source IP of the session
4. Default (when none of the above matches)

The longest match is done against entries present in the billing_customers
table. The match yields a set of profiles to be used further depending on
the type of session and day of the week.

The billing_customers table field that matches the Source IP is gateway. Do
not use hostnames for this field.

"gateway" or "trusted peer" terms are used interchangebly in this
document. They bott relate to the source IP address that generated the
SIP session.


Determination of the destination
--------------------------------

CDRTool identifies the destination for which to perform the rating based on
one of the destination fields from the accounting table. The destination is
taken from the first non-empty field in this order:

1. CanonicalURI (the destination after all lookups inside the SIP Proxy) 
2. SipTranslatedRequestURI (the Request URI as presented by the SIP UA)
3. CalledStationId (the content of the To header)

The CanonicalURI is preferred because the destination field must contain the
real SIP destination after all possible lookups performed by the SIP Proxy
(except for the DNS lookups). You must configure OpenSER to send this Radius
attribute when doing accounting (instructions are available in INSTALL.txt).

A destination ID is then calculated based on the longest match of this
destination in the billing_destinations table and the billing party.


PSTN rating 
-----------

The following steps are taken to rate the calls terminated to PSTN based on
the destination ID and billing party found in previous steps. 

1. Lookup the billing Profile in cdrtool.billing_customers table in the
   following order: subscriber,domain,gateway (based on $this->dayofweek):
   (as explained in "Determination of the billing party" above)

   - profile_name1 matches week days [1-5]
   - profile_name1_alt matches week days [1-5] if no rates for profile_name1
     are found
   - profile_name2 matches week-ends [6-0]
   - profile_name2_alt matches week-ends [6-0] if no rates for profile_name2
     are found
   - profile_name2 matches also holidays from billing_holidays table
   
   The week starts with 0 (Sunday) and ends with 6 (Saturday)

2. Using the profile_name found, lookup the rate_name based
   on $this->hourofday in cdrtool.billing_profiles table

   If no rate_name is found for the given profiles a second set of profiles
   are used, profile_name1_alt and profile_name2_alt.

   - the day may be split in maximum 4 periods
   - the days starts with hour 0 and ends with hour 24
   - rate_name1 defines the first interval after hour 0
   - rate_name2 defines the first interval after rate_name1
   - rate_name3 defines the first interval after rate_name2
   - rate_name4 defines the first interval after rate_name3

   When the hour matches an interval use the rate_nameX found to lookup the
   rate in billing_rates, if no record is found use the rate called
   'default'

3. Lookup in the cdrtool.billing_rates table the record having same name
   found at point 2 having billing_rates.destination = destination Id
   and billing_rates.application = application type found in the steps above.

   - return an array with all the rating values and the duration rated

  No rate will be returned if no destination Id is found. Make sure each
  possible destination has a corresponding Id and name in destinations
  table.

4. If the duration rated at point 3 is less than total call duration, apply
   point 3 again for the remaining call duration in next profile.  A maximum
   of 10 spans (different rates depending of time of day, day of the week)
   can be calculated using this mechanism.

5. Lookup the call rate based on time (peak/of-peak, day of week, holiday) 

6. Calculate the call Price based on duration. In global.inc there are
   several variables that affect how the price is calculated.  These
   settings are global per CDRTool installation but some can be overwritten
   with per customer values in the billing_customers table.

   $PRICING=array( 
           "priceDenominator"        => 10000, // Rates units (global setting)
           "priceDecimalDigits"      => 4,     // Decimal information (global setting)
           "minimumDurationCharged"  => 0,     // Rate a minimum of X seconds (per customer)
           "minimumDuration"         => 0,     // Minimum duration to rate, if call duration is shorter the price is zero (per customer)
           "durationPeriodRated"     => 60     // Rate is per 60 seconds (global setting)
           "trafficSizeRated"        => 1024,  // Default we rate per 1 MB (global setting)
           "reportMissingRates"      => 0      // Send emails to administrator in case of missing rates
            );

   Pricing formula
   ---------------

   if min_duration then
	   minimumDurationCharged = min_duration
   else if minimumDurationCharged set in global inc
	   use minimumDurationCharged from global.inc
   else
   	   minimumDurationCharged = call duration

   if increment then
	   	durationForRating = round to the next increment
   else
        durationForRating = call duration

   if durationForRating >= minimumDurationCharged then
   		Price = connectCost/priceDenominator+
           durationRate*durationForRating/durationPeriodRated/priceDenominator+
           trafficRate/priceDenominator*(inputTraffic+outputTraffic)/8+
           applicationRate/priceDenominator
   else 
        Price = 0


   ENUM discounts
   --------------

   The rating engine can apply a discount associated with the ENUM top
   level domain that returned the final destination.

   Price = Price - Price * ENUM discount /100

   To apply ENUM based discounts, the ENUM TLD must be saved with each CDR
   and the TLDs with their corespondent discounts must be provisioned in the
   Rating tables section. See ENUM TLD discounts section for more
   information.


8. Save the calculated price, Billing Party and Destination Id for each call
   in the CDRs. Having the price stored in database, it is possible to build
   statistics to display consolidated revenues per country code, network or
   subscriber.
   	
   Update the normalized caller and destination for each CDR.
 

Importing and exporting of rating files
---------------------------------------

There are different data files needed for rating CDRs. The data files are
imported into corresponding MySQL tables. The files must be uploaded to
/var/www/CDRTool/csv directory.  To load the files into the database run
the following command:

/var/www/CDRTool/scripts/importRatingTables.php

The import script knows to import the files only once so you may dump
several files there and safely run the import script from cron. The import
script detects whether the files have been imported by building a unique key
out of the filename and the hash of the file content. So you may use the
same filenames as long as the content differs and viceversa. If the import
file has changed any records, the rating engine is automatically instructed
to reload the changes.

Sample csv files are found in the setup directory. The CSV field order is
described in setup/*.csv sample files. The first element on each line
specifies the operation will be performed with the current record. The
operation can be 2 (update/insert), 1 (insert) or 3 (delete).

The updates are performed based on a unique key present in each table:

billing_customers     - cust_idx      (gateway,domain,subscriber)
destinations          - cust_dest_idx (gateway,domain,subscriber,dest_id)
billing_profiles      - profile_idx   (gateway,domain,subscriber,name)
billing_rates         - rate_idx      (gateway,domain,subscriber,name,destination,application)
billing_rates_history - rate_idx      (gateway,domain,subscriber,name,destination,application,startDate,endDate)

The content of the rating tables can be exported in the Rating tables page.

The import script detects the type of file to import based on its filename.
The filename must comply with the following naming convention:

1. Must start with the name of the destination table and 
2. End with .csv extension

Examples:

- rates.csv or rates20061201.cvs will be loaded into the rates table
- profiles.csv or profiles20061201.cvs will be loaded into the profiles table
- destinations2006.csv will be loaded in the destinations table

It is advisable to name the files in a consistent manner like tableYYYYMMDD.csv

The results of the import operation is logged in the database and can be
viewed in the Log section of the web interface and syslog.


Rating tables
--------------

To see the tables structures use describe table_name after connecting to
cdrtool database. Run show tables and describe table_name:
 
billing_customers
+-------------------+
| Field             |
+-------------------+
| id                |
| gateway           |
| domain            |
| subscriber        |
| profile_name1     |
| profile_name1_alt |
| profile_name2     |
| profile_name2_alt |
| timezone          |
| increment         |
| min_duration      |
| country_code      |
+-------------------+

billing_profiles
+------------+
| Field      |
+------------+
| id         |
| gateway    |
| domain     |
| subscriber |
| name       |
| rate_name1 |
| hour1      |
| rate_name2 |
| hour2      |
| rate_name3 |
| hour3      |
| rate_name4 |
| hour4      |
+------------+

billing_rates
+-----------------+
| Field           |
+-----------------+
| id              |
| gateway         |
| domain          |
| subscriber      |
| name            |
| destination     |
| durationRate    |
| trafficRate     |
| application     |
| connectCost     |
+-----------------+

billing_rates_history
+-----------------+
| Field           |
+-----------------+
| id              |
| gateway         |
| domain          |
| subscriber      |
| name            |
| destination     |
| durationRate    |
| trafficRate     |
| application     |
| connectCost     |
| startDate       |
| endDate         |
+-----------------+

destinations
+------------+
| Field      |
+------------+
| id         |
| gateway    |
| domain     |
| subscriber |
| dest_id    |
| dest_name  |
| asr        |
+------------+

billing_holidays
+-------+
| Field |
+-------+
| day   |
+-------+

prepaid
+------------------+
| Field            |
+------------------+
| id               |
| account          |
| disabled         |
| balance          |
| balance_previous |
| change_date      |
| expires          |
| call_lock        |
| call_in_progress |
| last_call_price  |
+------------------+

billing_enum_tlds
+-------------+
| Field       |
+-------------+
| id          | 
| gateway     | 
| domain      | 
| subscriber  | 
| enum_tld    | 
| e164_regexp | 
| discount    | 
+-------------+


Rating tables management
------------------------

The rating tables can be edited from the web, click on Rating tables link.
One may insert/update/delete records or apply changes on selections. For
example it is possible to increase with XX units the rate for a specific
destination.

Numeric fields support mathematical operators [+-*/], one may update
using absolute or relative values the fields in the rating tables.

The rates may be copied in bulk and start quickly working with a fresh
new rating table. Select in the Rates PSTN table by filtering on rate
name. A new button appears which allows the copy of all selected rates
into a new set. The rates are copied under the old rate id with _N
suffix where N is the next available number for which same rate id does
not exist.

The content of the rating tables can be exported into comma separated
files. The CSV format has the same structure as the import file, is
fairly easy to modify an exported batch file into an external
application and load it back into CDRTool.


ENUM TLD discounts
------------------

To apply discounts based on ENUM certain conditions must be met.

1. The username part of the result of the ENUM lookup must be numeric and
   contain a fully qualified E164 number, optional with a numeric prefix.

   Example:

   The user dialed 020800001, the SIP Proxy has normalized the destination
   based on local policy by stripping 0 and adding country code 31 to obtain
   the fully qualified E164 number 3120800001, than it performed an ENUM
   lookup under top level domain e164.example.com for +3120800001. The ENUM
   server responsable for e164.example.com returned a response with the
   destination sip:01131208000011@gateway.example.com

   In the CDR, ENUMtld is stored as e164.example.com

   The rating logic checks if the TLD exists in the billing_enum_tlds table. 
   If it does, the rating engine tries to match the regexp field against the
   username part of the destination from the ENUM response, which has been
   saved in the Canonical URI. The match must return a fully qualified E164
   number otherwise the call is considered to have ended to a no E164
   destination and the call will be free of charge.

2. In the ENUM tld table you must provision (for the example above):

    - TLD: e164.example.com
    - Regexp: 011([1-9][0-9]{7,})
    - Discount: 25

   The paranthezis of the Regexp field indicate the E164 number returned by
   the match and discount is a percentage that will be substracted form the
   total price of the call. The formula is described in the PSTN rating
   section.


Network rating engine
---------------------

The network rating engine is daemon that listens for commands on a TCP
socket and returns responses by using a line protocol. It is used to cache
in memory the content of the rating tables stored in the MySQL database. The
CDR normalization process uses transparently this engine to achieve high
speed during the rating phase of the normalization process. It is not
necessary to directly interact with the network rating engine.


Reloading rating tables
-----------------------

From cdrtool version 3.2  the rating tables are no longer loaded from
database into memory during each normalization. The network rating engine is
contacted for rating all calls. The rating engine is a daemon, which loads
all rating tables and remains active in the server memory. Reload of rating
tables is possible without stopping the daemon by connecting to it and
issuing the reload command. The init.d script can also be used for reloading
the rating engine with the current values from the rating database.

There are 3 ways of reloading the rating tables:

a. Each change executed in the WEB interface for rate management may update
the rating tables. If there is a change made to the database the link
'Reload rating tables' appears in red color on web page. Click on the link to
execute the reload.

b. Telnet to the IP address and port number specified for the Rating engine
in global.inc. Type help to see the list of commands available. Locate the
reload rates command and execute it followed by \n. You may see the result
of the command in syslog. The results displayed by syslog will show how many
entries have been reloaded from the rating table.

c. Run /etc/init.d/cdrtool reload command


Troubleshooting
---------------

To examine the rate information for a rated call click on the Id field on
the leftmost column. (Java script support in browser is required). A blue
area will open under the CDR line containing more information about the SIP
session.

If you see no price in the CDR or no rating information appears in the call
details it means that either no destination was found in the destinations
table or no rate has been associated with that destination. Make sure that
for each entry in the destinations table there is a corespondent entry in
the rates table. CDRTool rating engine can send warning emails if it finds
missing entries in the rating tables if the system where CDRTool runs is
properly configured to send emails and the e-mail notification addresses are
set in global.inc:
 
$CDRTool['provider']['toEmail']  = "support@example.com";
$CDRTool['provider']['bccEmail'] = "cdrtool@example.com";

To log to syslog about missing rates or incorrect setup of the rating tables
enable 'reportMissingRates' in $CDRTool['rating'] section of global.inc


Renormalizing CDRs and historical rating
----------------------------------------

Sometime is useful to be able to change the rates for calls that have been
already normalized and rated, for example after changing the rating tables
you wish to apply the changes for the previous month for a customer.

To re-rate the CDRs do the following:

1. Change the current rates by using cvs files/WEB interface or add rates
   valid for specific dates/destinations in the rates_history table

2. Re-normalize the calls to be re-rated by either selecting ReNormalize
   check-box in the search screen or by changing the Normalized field in the
   CDR MySQL table (e.g. radacct):

   Examples: 

   a) Re-rate calls for this month (2004-12) SIP domain example.com:	
   UPDATE radacct set Normalized = '0' where Realm = 'example.com'
   and AcctStartTime >= '2004-12-01' 

   b) Re-rate calls for SIP subscriber sip01@example.com:	
   UPDATE radacct set Normalized = '0' where UserName = 'sip01@example.com'

3. Apply rating again using command: 

   /var/www/CDRTool/scripts/normalize.php

Notes

Renormalization process can take long time during which your database
(radacct table) will be intermitently locked. Perform this operation
only during low traffic periods. 

It is advisable to re-rate only the CDRs for destinations that have
different rates. To do this, select a filter in the CDR search screen, if
the selection is right re-run the query by selecting Re-normalize button.

After renormalization, the monthly usage information used by the quota
system will be out of date. At the next run of the quotaCheck script, a full
table scan will be performed. See QuoatSystem.txt for more information about
quota.

Holidays must be added as individual days YYYY-MM-DD in table
billing_holidays. The profile applied for holidays is the same as for
week-ends. Holidays are global and cannot be specified per customer.

Renormalization process does not affect the balance of prepaid users.
Prepaid is a real time un-reversible process, it goes in one direction. The
prepaid balance is changed only by placing a call or adding credit to it.
There are several reasons for this:

- The balance before and after each CDR is not known to be able to roll it
  back at a later time
- Re-rating correctly is mathematically not possible for prepaid users that
  have calls in progress
- If the prices are higher than previously debited and end up with a
  negative balance, the software cannot force the user to pay more
  retroactively

Re-normalization for the purpose of re-rating is useful only for postpaid
accounts where you send an invoice at the end of the month and your can
change things back and forth. If you need to perform manual credit/debit
operations to some prepaid users because of faulty pricing, you can edit in
CDRTool in the prepaid table the balance by using + or -.


Known limitations
-----------------

The rating engine does not calculate rates based on the outbound carriers or
outbound gateways, the rates are based per calling party and not per called
party.

Price discounts (except those on ENUM tld) must be applied outside CDRTool,
in the billing system that prints the actual invoices. CDRTool has no
possibility to rate only calls after X minutes per month for subscriber Y,
all calls are rated uniformly.

Performance
-----------

Rating is part of the normalization process that happens every time a query
is executed in the web interface or when the rating engine is contacted by
the SIP Proxy or by the User Agent that performs the prepaid application.

The following tests have been performed between two machines with 3 GHz CPU
and 1 GB memory located on the same LAN having a round trip time of 0.2 ms.

The rating tables have been populated durring the tests with:

Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 8135 destinations
Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 6 profilesPSTN
Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 4 holidays
Aug 11 11:25:44 sip03 CDRTool[4945]: Loaded 7273 prepaid accounts

Tests results for the postpaid application:

Clients		Rating command			Execution speed
----------------------------------------------------------------
1		ShowPrice			390/s per client 
5 		ShowPrice 			100/s per client
10		ShowPrice 			60/s per client


Tests results for the prepaid application

Clients		Rating command			Execution speed
----------------------------------------------------------------
1		MaxSessionTime/DebitBalance	250/s per client
5		MaxSessionTime/DebitBalance	80/s per client
10		MaxSessionTime/DebitBalance	40/s per client

Client means either a SIP Proxy entity or a CDRTool server, which performs
the normalization process.

The pre-calculated maximum duration of prepaid calls may be affected by
rating plans with custom time increments and minimum charged durations. Do
not use minimum durations or custom increments for customers using the
prepaid application.

The rates are read directly from MySQL from version 6.1 instead of
beeing cached as they are found based on a known index.