Accounting guide
Version 5 (Tijmen de Mes, 11/26/2012 11:36 am)
1 | 1 | Tijmen de Mes | h1. Accounting guide |
---|---|---|---|
2 | 1 | Tijmen de Mes | |
3 | 1 | Tijmen de Mes | |
4 | 1 | Tijmen de Mes | "!http://cdrtool.ag-projects.com/raw-attachment/wiki/WikiStart/OpenSIPS-accounting.png(http://cdrtool.ag-projects.com/raw-attachment/wiki/WikiStart/OpenSIPS-accounting.png)!":http://cdrtool.ag-projects.com/raw-attachment/wiki/WikiStart/OpenSIPS-accounting.png |
5 | 1 | Tijmen de Mes | |
6 | 1 | Tijmen de Mes | The platform generates usage information in the form of rated Call Detail Records (CDR). It is the responsibility of the Operator to import the rated CDRs into its own billing system and to generate printable invoices out of them. |
7 | 1 | Tijmen de Mes | |
8 | 1 | Tijmen de Mes | The CDRs can be easily searched and grouped by various criteria and exported into comma separated value files. Accounting can be done postpaid (default option) or prepaid (enabled per SIP account). Postpaid accounts can have a monthly quota assigned to prevent fraud. |
9 | 1 | Tijmen de Mes | |
10 | 1 | Tijmen de Mes | h2. CDR Generation |
11 | 1 | Tijmen de Mes | |
12 | 1 | Tijmen de Mes | Call detail records (CDRs) are generated from the SIP sessions established through the platform. A CDR contains relevant information about a SIP session from both signaling and data planes. The CDR is generated by combining several Radius requests from OpenSIPS and MediaProxy as follows: |
13 | 1 | Tijmen de Mes | |
14 | 1 | Tijmen de Mes | |
15 | 1 | Tijmen de Mes | |*Radius request*|*When*|*Component*|*Description*| |
16 | 1 | Tijmen de Mes | |FAILED|First INVITE with negative code|OpenSIPS|Zero second (failed) session| |
17 | 1 | Tijmen de Mes | |START|First INVITE with positive code|OpenSIPS|Start of new session| |
18 | 1 | Tijmen de Mes | |STOP|BYE|OpenSIPS|Stop of existing session| |
19 | 1 | Tijmen de Mes | |UPDATE|RTP stop|MediaProxy|Stop or timeout of existing RTP stream| |
20 | 1 | Tijmen de Mes | |
21 | 1 | Tijmen de Mes | |
22 | 1 | Tijmen de Mes | h3. Radius logs |
23 | 1 | Tijmen de Mes | |
24 | 1 | Tijmen de Mes | Radius requests generated by the platform are logged by the Freeradius server in the following places: |
25 | 1 | Tijmen de Mes | |
26 | 1 | Tijmen de Mes | |
27 | 1 | Tijmen de Mes | |*Directory*|*Log type*| |
28 | 1 | Tijmen de Mes | |/var/log/freeradius/radacct/IP/detail-YYYYMM|Radius tickets contents| |
29 | 1 | Tijmen de Mes | |/var/log/freeradius/radacct/radius.log|Radius errors| |
30 | 1 | Tijmen de Mes | |
31 | 1 | Tijmen de Mes | |
32 | 1 | Tijmen de Mes | The most relevant attribute of a CDR is the session duration. The cost for the session depends on this session duration. The sessions duration is determined by the Stop time and Start time of the SIP session. The Start time is considered the moment when the answer for the first INVITE has entered the SIP Proxy. Stop time is determined either by the presence of BYE message or in case BYE is missing from the last moment RTP has ben relayed though MediaProxy. |
33 | 1 | Tijmen de Mes | |
34 | 1 | Tijmen de Mes | bq. It is not possible to determine the stop time of a session in the following situation: |
35 | 1 | Tijmen de Mes | |
36 | 1 | Tijmen de Mes | |
37 | 1 | Tijmen de Mes | |
38 | 1 | Tijmen de Mes | # First INVITE is answered with a positive code |
39 | 1 | Tijmen de Mes | # No BYE is received |
40 | 1 | Tijmen de Mes | # No RTP data has been relayed |
41 | 1 | Tijmen de Mes | |
42 | 1 | Tijmen de Mes | h3. Media timeout |
43 | 1 | Tijmen de Mes | |
44 | 1 | Tijmen de Mes | If the RTP media has timed out (both parties stopped sending RTP) while no BYE has been received by the SIP Proxy: |
45 | 1 | Tijmen de Mes | |
46 | 1 | Tijmen de Mes | # MediaProxy will set in the Radius UPDATE the MediaInfo Radius attribute to *timeout* |
47 | 1 | Tijmen de Mes | # MediaProxy will trigger the sending of a a BYE message via the dialog module of OpenSIPS |
48 | 1 | Tijmen de Mes | |
49 | 1 | Tijmen de Mes | In this situation, the actual duration of the SIP session contains the period of the RTP stream plus the value of the timeout. The value of the timeout is determined by the connection tracking logic in the Linux kernel. At this moment there is no mechanism to store into the Radius database the value of this timeout, which is typically 180 seconds. |
50 | 1 | Tijmen de Mes | |
51 | 1 | Tijmen de Mes | h3. CDR tables |
52 | 1 | Tijmen de Mes | |
53 | 1 | Tijmen de Mes | CDRs are stored in monthly tables in radius database. The tables are named radacctYYYYMM. Tables are automatically created by the Radius server at the beginning of each month. |
54 | 1 | Tijmen de Mes | |
55 | 1 | Tijmen de Mes | h3. CDR fields |
56 | 1 | Tijmen de Mes | |
57 | 1 | Tijmen de Mes | |
58 | 1 | Tijmen de Mes | |*Field*|*Type*|*Description*|Notes| |
59 | 2 | Tijmen de Mes | |RadAcctId|bigint(21)|MySQL unque index auto-incremented|| |
60 | 2 | Tijmen de Mes | |AcctSessionId|varchar(255)|SIP call-id header|| |
61 | 2 | Tijmen de Mes | |AcctUniqueId|varchar(255)|Radius internal session id|| |
62 | 1 | Tijmen de Mes | |UserName|varchar(64)|SIP Username|Matched against billing_customers.subscriber| |
63 | 1 | Tijmen de Mes | |Realm|varchar(64)|SIP domain|Matched against billing_customers.domain| |
64 | 2 | Tijmen de Mes | |NASIPAddress|varchar(15)|SIP Proxy IP|| |
65 | 2 | Tijmen de Mes | |NASPortId|varchar(50)|SIP Proxy port|| |
66 | 2 | Tijmen de Mes | |NASPortType|varchar(255)|not used|| |
67 | 2 | Tijmen de Mes | |AcctStartTime|datetime|Session start time|| |
68 | 2 | Tijmen de Mes | |AcctStopTime|datetime|Session stop time|| |
69 | 3 | Tijmen de Mes | |AcctSessionTime|int(12)|Session duration used for rating|| |
70 | 3 | Tijmen de Mes | |AcctAuthentic|varchar(32)|not used|| |
71 | 3 | Tijmen de Mes | |ConnectInfo_start|varchar(32)|not used|| |
72 | 3 | Tijmen de Mes | |ConnectInfo_stop|varchar(32)|not used|| |
73 | 3 | Tijmen de Mes | |AcctInputOctets|bigint(12)|Relayed RTP through MediaProxy|| |
74 | 3 | Tijmen de Mes | |AcctOutputOctets|bigint(12)|Relayed RTP through MediaProxy|| |
75 | 3 | Tijmen de Mes | |CalledStationId|varchar(50)|SIP To header, the original dialed number|| |
76 | 3 | Tijmen de Mes | |CallingStationId|varchar(50)|SIP From header|| |
77 | 3 | Tijmen de Mes | |AcctTerminateCause|varchar(32)|SIP response code|| |
78 | 3 | Tijmen de Mes | |ServiceType|varchar(32)|Sip-Session|| |
79 | 3 | Tijmen de Mes | |ENUMtld|varchar(64)|ENUM top level domain used for lookup|| |
80 | 3 | Tijmen de Mes | |FramedIPAddress|varchar(15)|not used|| |
81 | 3 | Tijmen de Mes | |AcctStartDelay|int(12)|not used|| |
82 | 3 | Tijmen de Mes | |AcctStopDelay|int(12)|not used|| |
83 | 3 | Tijmen de Mes | |SipMethod|varchar(50)|SIP method|| |
84 | 3 | Tijmen de Mes | |SipResponseCode|smallint(5) unsigned|Response code for INVITE|| |
85 | 3 | Tijmen de Mes | |SipToTag|varchar(128)|SIP to tag|| |
86 | 3 | Tijmen de Mes | |SipFromTag|varchar(128)|SIP from tag|| |
87 | 3 | Tijmen de Mes | |SipTranslatedRequestURI|varchar(255)|Final network address after DNS lookup|| |
88 | 3 | Tijmen de Mes | |SipUserAgents|varchar(255)|SIP user agents/servers reported by MediaProxy|| |
89 | 3 | Tijmen de Mes | |SipApplicationType|varchar(255)|Media type (e.g. audio)|| |
90 | 3 | Tijmen de Mes | |SipCodecs|varchar(255)|RTP codecs report by MediaProxy|| |
91 | 4 | Tijmen de Mes | |SipRPID|varchar(255)|PSTN caller id|| |
92 | 3 | Tijmen de Mes | |SipRPIDHeader|varchar(255)|not used|| |
93 | 4 | Tijmen de Mes | |SourceIP|varchar(255)|Source IP of the session|Matched against billing_customers.gateway| |
94 | 3 | Tijmen de Mes | |SourcePort|varchar(255)|Source port of the session|| |
95 | 4 | Tijmen de Mes | |CanonicalURI|varchar(255)|Final logical destination used for rating|Matched against destinations table| |
96 | 3 | Tijmen de Mes | |DelayTime|varchar(5)|not used|| |
97 | 3 | Tijmen de Mes | |Timestamp|bigint(20)|Packet timestamp|| |
98 | 1 | Tijmen de Mes | |DestinationId|varchar(15)|Normalized E.164 prefix|Calculated during normalization| |
99 | 1 | Tijmen de Mes | |Rate|text|Description of rate calculation|Calculated during normalization| |
100 | 1 | Tijmen de Mes | |Price|double(20,4)|Cost of the session|Calculated during normalization| |
101 | 2 | Tijmen de Mes | |Normalized|enum('0','1')|If the CDR has been normalized|| |
102 | 3 | Tijmen de Mes | |BillingId|varchar(255)|Reseller id used by trusted peers and SIP domains|Calculated during normalization| |
103 | 2 | Tijmen de Mes | |MediaInfo|varchar(32)|Mediaproxy information|| |
104 | 2 | Tijmen de Mes | |RTPStatistics|text|RTP statistics reported by some devices|| |
105 | 2 | Tijmen de Mes | |FromHeader|varchar(128)|not used|| |
106 | 2 | Tijmen de Mes | |UserAgent|varchar(128)|SIP User Agent and Server headers|| |
107 | 2 | Tijmen de Mes | |Contact|varchar(128)|not used|| |
108 | 1 | Tijmen de Mes | |
109 | 1 | Tijmen de Mes | h2. CDR Normalization |
110 | 1 | Tijmen de Mes | |
111 | 1 | Tijmen de Mes | A CDR is considered closed when the stop time has been determined. Once the CDR is closed, a normalization process cleans up the CDR fields and calculates the price of the session. The price and the way its was calculated is saved together with the CDR in the same table. It is possible to re-normalize the sessions at a later time, for instance when the tariffs need to be changed for the past period. |
112 | 1 | Tijmen de Mes | |
113 | 1 | Tijmen de Mes | Normalization is performed periodically: |
114 | 1 | Tijmen de Mes | |
115 | 1 | Tijmen de Mes | # Whenever CDRTool web search is used |
116 | 1 | Tijmen de Mes | # By the cron job /var/www/CDRTool/script/normalize.php |
117 | 1 | Tijmen de Mes | |
118 | 1 | Tijmen de Mes | Once normalized, the CDRs are no longed modified, the normalization process marks each CDR that has been processed. |
119 | 1 | Tijmen de Mes | |
120 | 1 | Tijmen de Mes | |
121 | 1 | Tijmen de Mes | |*Operation*|*When*|*Platform component*|*Description*| |
122 | 1 | Tijmen de Mes | |NORMALIZE|After CDR was closed|CDRTool|Rating is applied| |
123 | 1 | Tijmen de Mes | |
124 | 1 | Tijmen de Mes | |
125 | 1 | Tijmen de Mes | h3. Un-normalized sessions |
126 | 1 | Tijmen de Mes | |
127 | 1 | Tijmen de Mes | Some SIP sessions can remain in the CDR database in an un-normalized state. They show in CDRTool as "in progress". Such sessions have a start date but no end date. Such sessions cannot be normalized automatically by the CDR normalization process because there is no indication when they have actually stopped. A SIP session is considered stopped when either a BYE message has been received by the SIP Proxy or when the RTP media has stopped flowing between the end-points, which is detected by the MediaProxy. If the RTP media has not started yet and no BYE has arrived after the initial INVITE/200 OK/ACK, there is no physical indication for when the session has started or when it ended. Due to the nature of the SIP protocol and in particular the fact that the RTP media is carried separately from the SIP signaling, it is always possible to have SIP sessions that from a call completion perspective have started but never ended. Such situations occur outside the control of the operator responsible for the SIP service. You may chose to apply your own policy for such sessions like deleting or archiving them. |
128 | 1 | Tijmen de Mes | |
129 | 1 | Tijmen de Mes | h2. CDR Search |
130 | 1 | Tijmen de Mes | |
131 | 1 | Tijmen de Mes | CDRs can be searched and displayed in CDRTool. Access to CDRTool can be provided to resellers or end-users of the platform. |
132 | 1 | Tijmen de Mes | |
133 | 1 | Tijmen de Mes | "!http://cdrtool.ag-projects.com/raw-attachment/wiki/WikiStart/CDRTool-CallSearch.jpg(http://cdrtool.ag-projects.com/raw-attachment/wiki/WikiStart/CDRTool-CallSearch.jpg)!":http://cdrtool.ag-projects.com/raw-attachment/wiki/WikiStart/CDRTool-CallSearch.jpg |
134 | 1 | Tijmen de Mes | |
135 | 1 | Tijmen de Mes | Last placed and received calls are also available for every SIP account using SOAP/XML functions: |
136 | 1 | Tijmen de Mes | |
137 | 1 | Tijmen de Mes | * SipPort->getCalls() |
138 | 1 | Tijmen de Mes | |
139 | 1 | Tijmen de Mes | h2. CDR Export |
140 | 1 | Tijmen de Mes | |
141 | 1 | Tijmen de Mes | CDRs can be exported from CDRTool in comma separated values (CSV) format or by selecting the raw content of the MySQL radacctYYYYMM tables. Such operation can take time to complete and is advisable to use always a MySQL slave server for these queries. |
142 | 1 | Tijmen de Mes | |
143 | 1 | Tijmen de Mes | h2. Rating Logic |
144 | 1 | Tijmen de Mes | |
145 | 1 | Tijmen de Mes | CDRTool provides on-the-fly rating of CDRs generated by the platform. |
146 | 1 | Tijmen de Mes | |
147 | 1 | Tijmen de Mes | A Call Detail Record (CDR) is one record from the radius radacct table. The CDR contains all information related to a session, its duration, the calling and called party and media information. The rating engine calculates the price of the session. The calculation is done once and its results are saved in the CDR table for later use. It is possible to re-calculated the prices at a later time for example when having to change the tariffs. |
148 | 1 | Tijmen de Mes | |
149 | 1 | Tijmen de Mes | Based on exceptions, different rates may be applied per: |
150 | 1 | Tijmen de Mes | |
151 | 1 | Tijmen de Mes | # Caller party (SIP account) |
152 | 1 | Tijmen de Mes | # Caller domain |
153 | 1 | Tijmen de Mes | # Source IP address |
154 | 1 | Tijmen de Mes | |
155 | 1 | Tijmen de Mes | 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. |
156 | 1 | Tijmen de Mes | |
157 | 1 | Tijmen de Mes | 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. |
158 | 1 | Tijmen de Mes | |
159 | 1 | Tijmen de Mes | Different customers can have different rating plans. A rating plan is a unique combination of holidays, day of week, time of day, destination ids, and associated costs. |
160 | 1 | Tijmen de Mes | |
161 | 1 | Tijmen de Mes | The following steps are performed to rate a CDR: |
162 | 1 | Tijmen de Mes | |
163 | 1 | Tijmen de Mes | # Determination of the billing party |
164 | 1 | Tijmen de Mes | # Determination of the destination id |
165 | 1 | Tijmen de Mes | # Determination of the costs |
166 | 1 | Tijmen de Mes | |
167 | 1 | Tijmen de Mes | The steps are described in detail below. |
168 | 1 | Tijmen de Mes | |
169 | 1 | Tijmen de Mes | h3. Determination of the billing party |
170 | 1 | Tijmen de Mes | |
171 | 1 | Tijmen de Mes | To be able to calculate the Price for a call the rating engine must determine whose rating plan to use. |
172 | 1 | Tijmen de Mes | |
173 | 1 | Tijmen de Mes | The rating engine does this by performing a match against entries in the billing_customers table for the the BillingPartyId field of the the CDR (radacct.UserName for radius based datasources) in the following order: |
174 | 1 | Tijmen de Mes | |
175 | 1 | Tijmen de Mes | # SIP account user@domain |
176 | 1 | Tijmen de Mes | # SIP domain of the SIP account |
177 | 1 | Tijmen de Mes | # Source IP of the session |
178 | 1 | Tijmen de Mes | # Default (when none of the above matches) |
179 | 1 | Tijmen de Mes | |
180 | 1 | Tijmen de Mes | The first match is considered to be the billing party for which the rating plan is determined. |
181 | 1 | Tijmen de Mes | |
182 | 1 | Tijmen de Mes | The rating plan is further derived from the profiles associated with the entry found in the billing_customers. |
183 | 1 | Tijmen de Mes | |
184 | 1 | Tijmen de Mes | bq. The billing_customers table field that matches the Source IP is called gateway. "gateway" or "trusted peer" terms are used interchangeably in this document. They both relate to the source IP address that generated the SIP session. |
185 | 1 | Tijmen de Mes | |
186 | 1 | Tijmen de Mes | |
187 | 1 | Tijmen de Mes | |
188 | 1 | Tijmen de Mes | h3. Determination of the destination id |
189 | 1 | Tijmen de Mes | |
190 | 1 | Tijmen de Mes | The rating engine identifies the 'destination id', which has associated rates depending on day of week and time of day. |
191 | 1 | Tijmen de Mes | |
192 | 1 | Tijmen de Mes | The 'destination id' is for example a country prefix like '31' for the Netherlands. This 'destination id' has prices associated with it so all calls to the Netherlands will have prices associated with prefix '31'. |
193 | 1 | Tijmen de Mes | |
194 | 1 | Tijmen de Mes | The 'destination id' is derived from the logical destination the SIP session has been routed to. In SIP headers and Radius records there are multiple places that contain information related to the destination. Some of them are generated by the SIP User-Agents (hence cannot be trusted and must not be used for accounting purposes) and others are generated by the SIP Proxy configured by the operator and are suitable for accounting purposes. |
195 | 1 | Tijmen de Mes | |
196 | 1 | Tijmen de Mes | The rating engine considers the destination to be the first non-empty CDR field in this order: |
197 | 1 | Tijmen de Mes | |
198 | 1 | Tijmen de Mes | # CanonicalURI (the destination after all lookups inside the SIP Proxy) |
199 | 1 | Tijmen de Mes | # SipTranslatedRequestURI (the Request URI as presented by the SIP UA) |
200 | 1 | Tijmen de Mes | # CalledStationId (the content of the To header, used as a last resort) |
201 | 1 | Tijmen de Mes | |
202 | 1 | Tijmen de Mes | The CanonicalURI is the preferred because is reliable information generated by the operator based on the logic configured in the SIP Proxy and the subscriber cannot control it. OpenSIPS must be configured to send the CanonicalURI Radius attribute when creating the radius accounting START record. Instructions for this are available in INSTALL.txt file. |
203 | 1 | Tijmen de Mes | |
204 | 1 | Tijmen de Mes | The 'destination id' is then calculated based on the longest match of this destination field in the billing_destinations table. |
205 | 1 | Tijmen de Mes | |
206 | 1 | Tijmen de Mes | The longest match for the chosen destination field is performed by the E164_class, which by default uses E164_Europe that defines an European numbering plan. It assumes the destinations start with a zero for a national call and with double zero for an international call. See cdr_generic.php for the actual logic. |
207 | 1 | Tijmen de Mes | |
208 | 1 | Tijmen de Mes | If your dialing plan is different, you must use other provided class like E164_US or create a custom class and point to it in global.inc for each datasource as follows: |
209 | 1 | Tijmen de Mes | |
210 | 1 | Tijmen de Mes | bc(wiki). 'E164_class'=>'MyE164Class', |
211 | 1 | Tijmen de Mes | |
212 | 1 | Tijmen de Mes | |
213 | 1 | Tijmen de Mes | For example the pre-defined E164_US class from cdr_generic.php matches the American dialing plan. |
214 | 1 | Tijmen de Mes | |
215 | 1 | Tijmen de Mes | h3. Determination of the costs |
216 | 1 | Tijmen de Mes | |
217 | 1 | Tijmen de Mes | The following steps are taken to determine the cost for the calls based on the 'destination id' and the billing party determined at the previous steps. |
218 | 1 | Tijmen de Mes | |
219 | 1 | Tijmen de Mes | # Lookup the billing profiles 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: |
220 | 1 | Tijmen de Mes | |
221 | 1 | Tijmen de Mes | * profile_name1 matches week days [1-5] |
222 | 1 | Tijmen de Mes | * profile_name1_alt matches week days [1-5] if no rates for profile_name1 are found |
223 | 1 | Tijmen de Mes | * profile_name2 matches week-ends [6-0] |
224 | 1 | Tijmen de Mes | * profile_name2_alt matches week-ends [6-0] if no rates for profile_name2 are found |
225 | 1 | Tijmen de Mes | * profile_name2 matches also holidays from billing_holidays table |
226 | 1 | Tijmen de Mes | |
227 | 1 | Tijmen de Mes | The week starts with 0 (Sunday) and ends with 6 (Saturday). This step determines which rates should be applied based on the day of the week when the call started. |
228 | 1 | Tijmen de Mes | |
229 | 1 | Tijmen de Mes | <ol start="2"> |
230 | 1 | Tijmen de Mes | <li>Using the profile_name found, lookup the rate_name based on $this->hourofday in cdrtool.billing_profiles table</li> |
231 | 1 | Tijmen de Mes | </ol> |
232 | 1 | Tijmen de Mes | |
233 | 1 | Tijmen de Mes | If no rate_name is found for the given profiles a second set of profiles are used, profile_name1_alt and profile_name2_alt. |
234 | 1 | Tijmen de Mes | |
235 | 1 | Tijmen de Mes | * the day may be split in maximum 4 periods |
236 | 1 | Tijmen de Mes | * the days starts with hour 0 and ends with hour 24 |
237 | 1 | Tijmen de Mes | * rate_name1 defines the first interval after hour 0 |
238 | 1 | Tijmen de Mes | * rate_name2 defines the first interval after rate_name1 |
239 | 1 | Tijmen de Mes | * rate_name3 defines the first interval after rate_name2 |
240 | 1 | Tijmen de Mes | * rate_name4 defines the first interval after rate_name3 |
241 | 1 | Tijmen de Mes | |
242 | 1 | Tijmen de Mes | 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'. This step determines which rate should be applied for the time of day when the call started. |
243 | 1 | Tijmen de Mes | |
244 | 1 | Tijmen de Mes | <ol start="3"> |
245 | 1 | Tijmen de Mes | <li>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.</li> |
246 | 1 | Tijmen de Mes | </ol> |
247 | 1 | Tijmen de Mes | |
248 | 1 | Tijmen de Mes | * return an array with all the rating values and the duration rated |
249 | 1 | Tijmen de Mes | |
250 | 1 | Tijmen de Mes | No rate will be returned if no 'destination id' is found. Make sure each possible destination has a 'corresponding id' and name in the destinations table. |
251 | 1 | Tijmen de Mes | |
252 | 1 | Tijmen de Mes | This step determines the costs within the current time span associated with the time of day and destination id. If the call duration exceeds this time span (that is a new interval for which another rate applies is reached), step 4 is performed. |
253 | 1 | Tijmen de Mes | |
254 | 1 | Tijmen de Mes | <ol start="4"> |
255 | 1 | Tijmen de Mes | <li>If the duration rated at point 3 is less than total call duration, apply point 3 again for the remaining call duration in the next profile. A maximum of 10 spans (different rates depending of time of day, day of the week) can be calculated using this mechanism. After 10 spans, the engine bails out to avoid loops caused by invalid tables provisioning.</li> |
256 | 1 | Tijmen de Mes | </ol> |
257 | 1 | Tijmen de Mes | |
258 | 1 | Tijmen de Mes | <ol start="5"> |
259 | 1 | Tijmen de Mes | <li>Calculate the total call Price based on its duration and connection fees.</li> |
260 | 1 | Tijmen de Mes | </ol> |
261 | 1 | Tijmen de Mes | |
262 | 1 | Tijmen de Mes | 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. |
263 | 1 | Tijmen de Mes | |
264 | 1 | Tijmen de Mes | bc(wiki). $RatingEngine=array( |
265 | 1 | Tijmen de Mes | "priceDenominator" => 10000, // Rates units (global setting) |
266 | 1 | Tijmen de Mes | "priceDecimalDigits" => 4, // Decimal information (global setting) |
267 | 1 | Tijmen de Mes | "minimumDurationCharged" => 0, // Rate a minimum of X seconds (per customer) |
268 | 1 | Tijmen de Mes | "minimumDuration" => 0, // Minimum duration to rate, if call duration is shorter the price is zero (per customer) |
269 | 1 | Tijmen de Mes | "durationPeriodRated" => 60 // Rate is per 60 seconds (global setting) |
270 | 1 | Tijmen de Mes | "trafficSizeRated" => 1024, // Default we rate per 1 MB (global setting) |
271 | 1 | Tijmen de Mes | "reportMissingRates" => 0 // Send emails to administrator in case of missing rates |
272 | 1 | Tijmen de Mes | ); |
273 | 1 | Tijmen de Mes | |
274 | 1 | Tijmen de Mes | |
275 | 1 | Tijmen de Mes | Pricing formula: |
276 | 1 | Tijmen de Mes | |
277 | 1 | Tijmen de Mes | <pre class="wiki"> |
278 | 1 | Tijmen de Mes | if min_duration then |
279 | 1 | Tijmen de Mes | minimumDurationCharged = min_duration |
280 | 1 | Tijmen de Mes | else if minimumDurationCharged set in global inc |
281 | 1 | Tijmen de Mes | use minimumDurationCharged from global.inc |
282 | 1 | Tijmen de Mes | else |
283 | 1 | Tijmen de Mes | minimumDurationCharged = call duration |
284 | 1 | Tijmen de Mes | |
285 | 1 | Tijmen de Mes | if increment then |
286 | 1 | Tijmen de Mes | durationForRating = round to the next increment |
287 | 1 | Tijmen de Mes | else |
288 | 1 | Tijmen de Mes | durationForRating = call duration |
289 | 1 | Tijmen de Mes | |
290 | 1 | Tijmen de Mes | if durationForRating >= minimumDurationCharged then |
291 | 1 | Tijmen de Mes | Price = connectCost/priceDenominator+ |
292 | 1 | Tijmen de Mes | durationRate*durationForRating/durationPeriodRated/priceDenominator |
293 | 1 | Tijmen de Mes | else |
294 | 1 | Tijmen de Mes | Price = 0 |
295 | 1 | Tijmen de Mes | </pre> |
296 | 1 | Tijmen de Mes | |
297 | 1 | Tijmen de Mes | ENUM discounts |
298 | 1 | Tijmen de Mes | |
299 | 1 | Tijmen de Mes | The rating engine can apply a discount associated with the ENUM top level domain that returned the final destination. |
300 | 1 | Tijmen de Mes | |
301 | 1 | Tijmen de Mes | bc(wiki). Price = Price - Price * ENUM discount / 100 |
302 | 1 | Tijmen de Mes | |
303 | 1 | Tijmen de Mes | |
304 | 1 | Tijmen de Mes | 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. |
305 | 1 | Tijmen de Mes | |
306 | 1 | Tijmen de Mes | Purchasing price |
307 | 1 | Tijmen de Mes | |
308 | 1 | Tijmen de Mes | A second price called 'Price in' is calculated using the same formula but based on connectCostIn and durationRateIn values. It can be used to match the purchasing price and calculate the margin between purchasing and selling prices. The information about both prices is stored in the RateInfo field of the CDR. The values for connectCostIn and durationRateIn must be provisioned in the billing_rates and billing_rates_history tables using the web interface or by importing csv files. |
309 | 1 | Tijmen de Mes | |
310 | 1 | Tijmen de Mes | <ol start="7"> |
311 | 1 | Tijmen de Mes | <li>Save the calculated Price, billing party and 'destination id' for each call in the CDR table. Having the price stored in the database, it is possible to build statistics to display consolidated revenues per country code, network or subscriber.</li> |
312 | 1 | Tijmen de Mes | </ol> |
313 | 1 | Tijmen de Mes | |
314 | 1 | Tijmen de Mes | h3. Rating files |
315 | 1 | Tijmen de Mes | |
316 | 1 | Tijmen de Mes | To generate billable CDRs from the SIP traffic that goes through the platform you must provision the rating plans in CDRTool software that is responsible for CDR mediation and rating. |
317 | 1 | Tijmen de Mes | |
318 | 1 | Tijmen de Mes | Before provisioning CDRTool, is important that you understand how the rating engine works. The modus operandi of the rating engine is described at: |
319 | 1 | Tijmen de Mes | |
320 | 1 | Tijmen de Mes | " https://mdns.sipthor.net/CDRTool/doc/RATING.txt":https://mdns.sipthor.net/CDRTool/doc/RATING.txt |
321 | 1 | Tijmen de Mes | |
322 | 1 | Tijmen de Mes | After you read and understand the document, you will need to generate the following files: |
323 | 1 | Tijmen de Mes | |
324 | 1 | Tijmen de Mes | # destinations.csv |
325 | 1 | Tijmen de Mes | # customers.csv |
326 | 1 | Tijmen de Mes | # billing_profiles.csv |
327 | 1 | Tijmen de Mes | # billing_rates.csv |
328 | 1 | Tijmen de Mes | |
329 | 1 | Tijmen de Mes | The file formats are described in 'Importing and exporting of rating files' section of RATING.txt document. |
330 | 1 | Tijmen de Mes | |
331 | 1 | Tijmen de Mes | Samples files can be found in the CDRTool software archive available at: |
332 | 1 | Tijmen de Mes | |
333 | 1 | Tijmen de Mes | " http://download.ag-projects.com/CDRTool/sample_rating_files/":http://download.ag-projects.com/CDRTool/sample_rating_files/ |
334 | 1 | Tijmen de Mes | |
335 | 1 | Tijmen de Mes | The files must then be uploaded in CDRTool rating engine, once the software is operational on your platform. |
336 | 1 | Tijmen de Mes | |
337 | 1 | Tijmen de Mes | The operator is responsable for creating the files. The initial provisioning is performed by AG Projects. |
338 | 1 | Tijmen de Mes | |
339 | 1 | Tijmen de Mes | The content of the rating tables can be edited in the web interface provided by CDRTool application. |
340 | 1 | Tijmen de Mes | |
341 | 1 | Tijmen de Mes | h3. Importing and exporting of rating files |
342 | 1 | Tijmen de Mes | |
343 | 1 | Tijmen de Mes | There are different data files needed for rating. The data files are imported into their corresponding MySQL tables. The files must be uploaded to /var/spool/cdrtool directory. To load the files into the database run the following command: |
344 | 1 | Tijmen de Mes | |
345 | 1 | Tijmen de Mes | bc(wiki). /var/www/CDRTool/scripts/importRatingTables.php |
346 | 1 | Tijmen de Mes | |
347 | 1 | Tijmen de Mes | |
348 | 1 | Tijmen de Mes | 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 each file 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. |
349 | 1 | Tijmen de Mes | |
350 | 1 | Tijmen de Mes | 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). |
351 | 1 | Tijmen de Mes | |
352 | 1 | Tijmen de Mes | The updates are performed based on a unique key present in each table: |
353 | 1 | Tijmen de Mes | |
354 | 1 | Tijmen de Mes | bc(wiki). billing_customers - cust_idx (gateway,domain,subscriber) |
355 | 1 | Tijmen de Mes | destinations - cust_dest_idx (gateway,domain,subscriber,dest_id) |
356 | 1 | Tijmen de Mes | billing_profiles - profile_idx (name) |
357 | 1 | Tijmen de Mes | billing_rates - rate_idx (name,destination,application) |
358 | 1 | Tijmen de Mes | billing_rates_history - rate_idx (name,destination,application,startDate,endDate) |
359 | 1 | Tijmen de Mes | |
360 | 1 | Tijmen de Mes | |
361 | 1 | Tijmen de Mes | The content of the rating tables can be exported in the Rating tables page. |
362 | 1 | Tijmen de Mes | |
363 | 1 | Tijmen de Mes | The import script detects the type of file to import based on its filename. |
364 | 1 | Tijmen de Mes | |
365 | 1 | Tijmen de Mes | The filename must comply with the following naming convention: |
366 | 1 | Tijmen de Mes | |
367 | 1 | Tijmen de Mes | # Must start with the name of the table without the billing_ |
368 | 1 | Tijmen de Mes | # May optionally contain extra characters after the name |
369 | 1 | Tijmen de Mes | # Must end with .csv extension |
370 | 1 | Tijmen de Mes | |
371 | 1 | Tijmen de Mes | Examples: |
372 | 1 | Tijmen de Mes | |
373 | 1 | Tijmen de Mes | * rates.csv or rates20061201.cvs will be loaded into the rates table |
374 | 1 | Tijmen de Mes | * profiles.csv or profiles20061201.cvs will be loaded into the profiles table |
375 | 1 | Tijmen de Mes | * destinations200601.csv will be loaded in the destinations table |
376 | 1 | Tijmen de Mes | * ratesHistory200801.csv will be loaded in the rates_history table |
377 | 1 | Tijmen de Mes | |
378 | 1 | Tijmen de Mes | Do not use 'billing_' prefix in front of the file name. |
379 | 1 | Tijmen de Mes | |
380 | 1 | Tijmen de Mes | It is advisable to name the files in a consistent manner like tableYYYYMMDD.csv |
381 | 1 | Tijmen de Mes | |
382 | 1 | Tijmen de Mes | The results of the import operation is logged in the database and can be viewed in the Log section of the web interface and the syslog. |
383 | 1 | Tijmen de Mes | |
384 | 1 | Tijmen de Mes | h3. Renormalizing CDRs |
385 | 1 | Tijmen de Mes | |
386 | 1 | Tijmen de Mes | 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. |
387 | 1 | Tijmen de Mes | |
388 | 1 | Tijmen de Mes | To re-rate the CDRs do the following: |
389 | 1 | Tijmen de Mes | |
390 | 1 | Tijmen de Mes | # Change the current rates by using cvs files/WEB interface or add rates valid for specific dates/destinations in the rates_history table |
391 | 1 | Tijmen de Mes | |
392 | 1 | Tijmen de Mes | <ol start="2"> |
393 | 1 | Tijmen de Mes | <li>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):</li> |
394 | 1 | Tijmen de Mes | </ol> |
395 | 1 | Tijmen de Mes | |
396 | 1 | Tijmen de Mes | Examples: |
397 | 1 | Tijmen de Mes | |
398 | 1 | Tijmen de Mes | # Re-rate calls for this month (2004-12) SIP domain example.com: |
399 | 1 | Tijmen de Mes | |
400 | 1 | Tijmen de Mes | bc(wiki). UPDATE radacct set Normalized = '0' where Realm = 'example.com' |
401 | 1 | Tijmen de Mes | and AcctStartTime >= '2004-12-01' |
402 | 1 | Tijmen de Mes | |
403 | 1 | Tijmen de Mes | |
404 | 1 | Tijmen de Mes | <ol start="2"> |
405 | 1 | Tijmen de Mes | <li>Re-rate calls for SIP subscriber " sip01@example.com":mailto:sip01@example.com:</li> |
406 | 1 | Tijmen de Mes | </ol> |
407 | 1 | Tijmen de Mes | |
408 | 1 | Tijmen de Mes | bc(wiki). UPDATE radacct set Normalized = '0' where UserName = 'sip01@example.com' |
409 | 1 | Tijmen de Mes | |
410 | 1 | Tijmen de Mes | |
411 | 1 | Tijmen de Mes | <ol start="3"> |
412 | 1 | Tijmen de Mes | <li>Apply rating again using command:</li> |
413 | 1 | Tijmen de Mes | </ol> |
414 | 1 | Tijmen de Mes | |
415 | 1 | Tijmen de Mes | bc(wiki). /var/www/CDRTool/scripts/normalize.php |
416 | 1 | Tijmen de Mes | |
417 | 1 | Tijmen de Mes | |
418 | 1 | Tijmen de Mes | Notes |
419 | 1 | Tijmen de Mes | |
420 | 1 | Tijmen de Mes | Renormalization process can take long time during which your database (radacct table) will be intermitently locked. Perform this operation only during low traffic periods. |
421 | 1 | Tijmen de Mes | |
422 | 1 | Tijmen de Mes | 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. |
423 | 1 | Tijmen de Mes | |
424 | 1 | Tijmen de Mes | 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 QuotaSystem.txt for more information about quota. |
425 | 1 | Tijmen de Mes | |
426 | 1 | Tijmen de Mes | 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. |
427 | 1 | Tijmen de Mes | |
428 | 1 | Tijmen de Mes | 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: |
429 | 1 | Tijmen de Mes | |
430 | 1 | Tijmen de Mes | * The balance before and after each CDR is not known to be able to roll it back at a later time |
431 | 1 | Tijmen de Mes | * Re-rating correctly is mathematically not possible for prepaid users that have calls in progress |
432 | 1 | Tijmen de Mes | * 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 |
433 | 1 | Tijmen de Mes | |
434 | 1 | Tijmen de Mes | 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 -. |
435 | 1 | Tijmen de Mes | |
436 | 1 | Tijmen de Mes | h2. Rating Assignment |
437 | 1 | Tijmen de Mes | |
438 | 1 | Tijmen de Mes | To asign a particular rating plan to a SIP account, SIP domain of Trusted Peer: |
439 | 1 | Tijmen de Mes | |
440 | 1 | Tijmen de Mes | h3. SOAP/XML functions |
441 | 1 | Tijmen de Mes | |
442 | 1 | Tijmen de Mes | * RatingPort->setEntityProfiles() |
443 | 1 | Tijmen de Mes | * RatingPort->deleteEntityProfiles() |
444 | 1 | Tijmen de Mes | * RatingPort->getEntityProfiles() |
445 | 1 | Tijmen de Mes | |
446 | 1 | Tijmen de Mes | h3. Graphical clients |
447 | 1 | Tijmen de Mes | |
448 | 1 | Tijmen de Mes | bc(wiki). CDRTool->Rating->Customers |
449 | 1 | Tijmen de Mes | |
450 | 1 | Tijmen de Mes | |
451 | 1 | Tijmen de Mes | bc(wiki). SIP settings page->Settings tab->Billing Profiles |
452 | 1 | Tijmen de Mes | |
453 | 1 | Tijmen de Mes | |
454 | 1 | Tijmen de Mes | h2. Prepaid Accounts |
455 | 1 | Tijmen de Mes | |
456 | 1 | Tijmen de Mes | "!http://callcontrol.ag-projects.com/raw-attachment/wiki/WikiStart/CallControl.png(http://callcontrol.ag-projects.com/raw-attachment/wiki/WikiStart/CallControl.png)!":http://callcontrol.ag-projects.com/raw-attachment/wiki/WikiStart/CallControl.png |
457 | 1 | Tijmen de Mes | |
458 | 1 | Tijmen de Mes | Prepaid functionality can be enabled by setting the SIP account prepaid attribute to 'true'. As a result, a new record is created in CDRTool application, in the prepaid table. This record is used to maintain the balance of the prepaid account. A SIP account marked as prepaid can make PSTN calls within the limits of the balance associated with the CDRTool prepaid account. |
459 | 1 | Tijmen de Mes | |
460 | 1 | Tijmen de Mes | Every credit and debit operation is logged into the prepaid_history table, which can be queried using SOAP functions or can be accessed using CDRTool web pages. |
461 | 1 | Tijmen de Mes | |
462 | 1 | Tijmen de Mes | h3. SOAP/XML functions |
463 | 1 | Tijmen de Mes | |
464 | 1 | Tijmen de Mes | * SipPort->addBalance() |
465 | 1 | Tijmen de Mes | * SipPort->getPrepaidStatus() |
466 | 1 | Tijmen de Mes | * SipPort->getCreditHistory() |
467 | 1 | Tijmen de Mes | |
468 | 1 | Tijmen de Mes | h3. Graphical client |
469 | 1 | Tijmen de Mes | |
470 | 1 | Tijmen de Mes | bc(wiki). CDRTool->Rating->Prepaid |
471 | 1 | Tijmen de Mes | |
472 | 1 | Tijmen de Mes | |
473 | 5 | Tijmen de Mes | "!cdrtool-prepaid-accounts.png!" |
474 | 1 | Tijmen de Mes | |
475 | 1 | Tijmen de Mes | bc(wiki). CDRTool->Rating->Prepaid history |
476 | 1 | Tijmen de Mes | |
477 | 1 | Tijmen de Mes | |
478 | 1 | Tijmen de Mes | "!/raw-attachment/wiki/AccountingGuide/cdrtool-prepaid-history.png!":/attachment/wiki/AccountingGuide/cdrtool-prepaid-history.png |
479 | 1 | Tijmen de Mes | |
480 | 1 | Tijmen de Mes | h3. Voice prompts |
481 | 1 | Tijmen de Mes | |
482 | 1 | Tijmen de Mes | Voice prompts can be played when the balance is not sufficient to make a new session. This feature must be enabled in the " SIP Proxy configuration":http://msp-documentation.ag-projects.com/wiki/ConfigurationGuide#SIPProxy: |
483 | 1 | Tijmen de Mes | |
484 | 1 | Tijmen de Mes | <pre class="wiki"> |
485 | 1 | Tijmen de Mes | define(`USE_FAILURE_PROMPTS', `1') |
486 | 1 | Tijmen de Mes | |
487 | 1 | Tijmen de Mes | # Voice message prompts for prepaid failure conditions. |
488 | 1 | Tijmen de Mes | define(`PREPAID_NO_CREDIT_PROMPT', `sip:800301@MEDIA_SERVER') |
489 | 1 | Tijmen de Mes | define(`PREPAID_ERROR_PROMPT', `sip:800399@MEDIA_SERVER') |
490 | 1 | Tijmen de Mes | </pre> |
491 | 1 | Tijmen de Mes | |
492 | 1 | Tijmen de Mes | h2. Prepaid Cards |
493 | 1 | Tijmen de Mes | |
494 | 1 | Tijmen de Mes | You may generate prepaid cards with designated values. The cards are randomly generated, identified by a combination of ID and NUMBER combination that can be safely sold to end-users or resellers in the form of scratch cards. |
495 | 1 | Tijmen de Mes | |
496 | 1 | Tijmen de Mes | h3. SOAP/XML functions |
497 | 1 | Tijmen de Mes | |
498 | 1 | Tijmen de Mes | Each prepaid card has a value, the value is transfered to the SIP account when the correct ID and NUMBER are supplied to the function: |
499 | 1 | Tijmen de Mes | |
500 | 1 | Tijmen de Mes | * SipPort->addBalanceFromVoucher() |
501 | 1 | Tijmen de Mes | |
502 | 1 | Tijmen de Mes | h3. Graphical client |
503 | 1 | Tijmen de Mes | |
504 | 1 | Tijmen de Mes | The Batches with prepaid cards can be exported in comma separated values format to external systems, for example to a printer that makes scratch cards. |
505 | 1 | Tijmen de Mes | |
506 | 1 | Tijmen de Mes | @CDRTool->Rating->Prepaid cards@ |
507 | 1 | Tijmen de Mes | |
508 | 1 | Tijmen de Mes | "!/raw-attachment/wiki/AccountingGuide/cdrtool-prepaid-cards.png!":/attachment/wiki/AccountingGuide/cdrtool-prepaid-cards.png |