diff --git a/doc/RATING.txt b/doc/RATING.txt
index 3a3226d..095e879 100644
--- a/doc/RATING.txt
+++ b/doc/RATING.txt
@@ -1,833 +1,834 @@
CDRTool rating engine
---------------------
CDRTool provides on-the-fly rating of CDRs from multiple data-sources like
OpenSIPS, Asterisk or Cisco gateways based on easy to build rating plan,
which can be provisioned in a web interface or imported from csv files.
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-calculate the prices
at a later time for example when having to change the tariffs.
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. This functionality
is not available when the end-points have negotiated using ICE another
candidate than MediaProxy.
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.
Rating logic
------------
Different subscribers 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.
The following steps are performed to rate a CDR:
1. Determination of the billing party
2. Determination of the destination id
3. Determination of the costs
The steps are described in detail below.
1. Determination of the billing party
-------------------------------------
To be able to calculate the Price for a call the rating engine must
determine whose rating plan to use.
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:
a. SIP account user@domain
-b. SIP domain of the SIP account
-c. Source IP of the session
-d. Default (when none of the above matches)
+b. Username@SourceIP (Trusted peer account)
+c. SIP domain of the SIP account
+d. SourceIP of the session (Trusted peer IP)
+e. Default (when none of the above matches)
You must have a maximum of one column (either subscriber, domain or gateway)
set in every row of billing_customers table for this to work.
The first match is considered to be the billing party for which the rating
plan is determined.
The rating plan is further derived from the profiles associated with the
entry found in the billing_customers table.
Note
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.
If you want to use the multi-tenant features for OpenSIPS accounting you
must create an integer reseller_id column in the trusted and domain tables.
Traffic generated by IP addresses from the trusted peers table and local
domains will be marked as belonging to their correspondent reseller_id. The
reseller_id is then later used to lookup destinations and rates belonging to
the reseller_id.
2. Determination of the destination id
--------------------------------------
The rating engine identifies the 'destination id', which has associated
rates depending on day of week and time of day.
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'.
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.
The rating engine considers the destination to be the first non-empty CDR
field in this order:
a. CanonicalURI (the destination after all lookups inside the SIP Proxy)
b. SipTranslatedRequestURI (the Request URI as presented by the SIP UA)
c. CalledStationId (the content of the To header, used as a last resort)
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 how to set this up are available in INSTALL.txt
file.
The 'destination id' is then calculated based on the longest match of this
destination field in the billing_destinations table.
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.
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:
'E164_class'=>'MyE164Class',
For example the pre-defined E164_US class from cdr_generic.php matches the
American dialing plan.
Destinations are uploaded into destination table. Each reseller_id can have
its own list of destinations. If the reseller_id of trusted peers and domain
tables have been set, then the rating engine will apply the rating
information belonging to them.
Each destination has also a set of properties that can be used to alter the
calculation of the price. These properties are:
- increment: the call duration is rounded to the next increment in seconds,
this is only used for rating, the actual CDR duration is not modified
- min_duration: set the duration to min_duration if the CDR duration is
smaller than min_duration, this is only used for rating, the actual CDR
duration is not modified
- max_duration: limit the duration to max_duration if CDR duration is
bigger than max_duration, this is only used for rating, the actual CDR
duration is not modified
- max_price: the price is set to max_price per call if the calculated price
is bigger than the max_price
The min_duration and increment values from billing_customers override the
values found in the destinations table.
3. Determination of the costs
-----------------------------
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.
1. 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)
- 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)
This step determines which rates should be applied based on the day of
the week when the call started.
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'
This step determines which rate should be applied for the time of day
when the call started.
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 the destinations
table.
The 'audio' application may optionally be specified with a subtype in order
to refine billing rates. If a subtype is specified, it is appended to the
application name, separated by a dot (i.e. 'audio.incoming').
For example, ('1', 'audio') can have a durationRate of 280, where
('1', 'audio.incoming') can have a durationRate of 0. If the application
isn't specified in the CDR, it defaults to 'audio'.
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.
4. 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.
5. Calculate the total call Price based on its duration and connection fees.
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.
$RatingEngine=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
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.
Purchasing price
----------------
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.
6. 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
destination id, gateway, domain or subscriber.
Testing the rating engine
-------------------------
You can test the rating engine by telneting to the IP and port configured in
global.inc. Type 'help' once connected to see the available commands. Use
'ShowPrice' command to simulate the rating of one session, for example:
adigeo@w1:/var/www/CDRTool/doc$ telnet ws1 9024
Trying 10.0.0.1 ...
Connected to 10.0.0.1.
Escape character is '^]'.
ShowPrice From=sip:123@example.com To=sip:0031650222333@example.com Gateway=10.0.0.1 Duration=59
0.2023
Duration: 59 s
App: audio
Destination: 31650
Customer: domain=example.com
Connect: 0.0450
StartTime: 2009-01-03 14:29:10
--
Span: 1
Duration: 59 s
ProfileId: 442 / weekend
RateId: 442 / 0-24h
Rate: 0.1600 / 60 s
Price: 0.1573
Below is a description of the fields that must be separated by one or more
spaces:
* From - must contain the caller party
* Gateway - must contain the source IP of the session
* To - must contain the full CanonicalURI destination, its format must be synced
with the E164 class logic used to determine the 'destination id'
* Duration - the duration of the session in seconds
Check the syslog for any errors, most of the configuration errors like
missing rates are logged to the syslog.
Importing and exporting of rating files
---------------------------------------
It is important to set the impersonate field correctly for the login
accounts. To access in the CDRTool Rating web page all the rates table for
all resellers in the system you must set the impersonate field of the login
account to 0.0
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:
/var/www/CDRTool/scripts/importRatingTables.php
Data partitioning for multiple resellers
The reseller_id column present in all rating tables is used to filter access
based on Login account impersonate field.
You may create numerical sub-directories under /var/spool/cdrtool directory.
When importing a file from such numerical directory, the reseller_id field
in all imported records is set to the directory name.
The import script knows to import the files only once so you may dump
several files over time with the same name and safely run the import script
from cron. The import script detects whether each file has already 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 (reseller,gateway,domain,subscriber)
destinations - cust_dest_idx (reseller,gateway,domain,subscriber,dest_id)
billing_profiles - profile_idx (reseller,name)
billing_rates - rate_idx (reseller,name,destination,application)
billing_rates_history - rate_idx (reseller,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 table without the billing_ prefix
2. May optionally contain extra characters after the name
3. Must 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
- destinations200601.csv will be loaded in the destinations table
- ratesHistory200801.csv will be loaded in the rates_history table
Do not use 'billing_' prefix in front of the file name.
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 the syslog.
MySQL schema
------------
To see the rating tables and their structures connect to the cdrtool
database using mysql client.
Run 'show tables' and 'describe table_name':
billing_customers
+-------------------+
| Field |
+-------------------+
| id |
| reseller_id |
| gateway |
| domain |
| subscriber |
| profile_name1 |
| profile_name1_alt |
| profile_name2 |
| profile_name2_alt |
| timezone |
| increment |
| min_duration |
+-------------------+
billing_profiles
+------------+
| Field |
+------------+
| id |
| reseller_id|
| name |
| rate_name1 |
| hour1 |
| rate_name2 |
| hour2 |
| rate_name3 |
| hour3 |
| rate_name4 |
| hour4 |
+------------+
billing_rates
+-----------------+
| Field |
+-----------------+
| id |
| reseller_id |
| name |
| destination |
| application |
| connectCost |
| durationRate |
| connectCostIn |
| durationRateIn |
+-----------------+
billing_rates_history
+-----------------+
| Field |
+-----------------+
| id |
| reseller_id |
| name |
| destination |
| application |
| connectCost |
| durationRate |
| connectCostIn |
| durationRateIn |
| startDate |
| endDate |
+-----------------+
destinations
+--------------+
| Field |
+--------------+
| id |
| reseller_id |
| gateway |
| domain |
| subscriber |
| dest_id |
| region |
| dest_name |
| increment |
| min_duration |
| max_duration |
| max_price |
+--------------+
billing_holidays
+-------+
| Field |
+-------+
| day |
+-------+
prepaid
+------------------+
| Field |
+------------------+
| id |
| reseller_id |
| account |
| domain |
| balance |
| change_date |
| active_sessions |
| session_counter |
+------------------+
prepaid_history
+-------------+
| Field |
+-------------+
| id |
| reseller_id |
| username |
| domain |
| action |
| number |
| value |
| balance |
| date |
+-------------+
billing_enum_tlds
+-------------+
| Field |
+-------------+
| id |
| reseller_id |
| gateway |
| domain |
| subscriber |
| enum_tld |
| e164_regexp |
| discount |
+-------------+
Web based 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 the 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.
Note
When $RatingEngine['split_rating_table'] is true, after changing the rates
in the web interface or by importing them, you must run the script
scripts/splitRatingTables.php to split the central billing_rates table into
individual tables for each rate id. You do not need this feature if you have
less than 100K rates in your system.
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 parenthesis of the Regexp field indicate the E164 number returned by
the match and discount is a percentage that will be subtracted form the
total price of the call. The formula is described in the PSTN rating
section.
Reloading rating tables
-----------------------
The rating engine loads some of the rating tables in the memory, when the
tables change a reload is needed. 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 that requires a
reload 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 QuotaSystem.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 prices based on the outbound carriers
or outbound gateways, the rating plan is is assigned by the calling party
and not by called party.
Price discounts (except those based 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 rates are read directly from MySQL from version 6.1 instead of
being cached as they are found based on a known index.
To monitor the connections to the rating engin server telnet to
the rating engine port and issue the ShowClients command. Example output:
ShowClients
Clients:
1. 91.20.228.143:32837
2. 85.1.86.71:57945
3. 91.20.228.146:35098
4. 91.20.228.150:34285
5. 91.20.228.129:55090
6. 91.20.228.164:34147
Requests:
12 requests from 91.20.228.129
11 requests from 91.20.228.150
8 requests from 85.11.86.71
2 requests from 91.20.228.164
Statistics:
Total requests: 33
Uptime: 169 seconds
Load: 0.20/s
diff --git a/library/rating.php b/library/rating.php
index dca8414..67c1adf 100644
--- a/library/rating.php
+++ b/library/rating.php
@@ -1,9574 +1,9584 @@
db = $db;
$this->settings = $settings;
if ($this->database_backend == "mysql") {
$this->db->Halt_On_Error="no";
}
if ($this->settings['priceDenominator']) {
$this->priceDenominator = $this->settings['priceDenominator'];
}
if ($this->settings['priceDecimalDigits']) {
$this->priceDecimalDigits = $this->settings['priceDecimalDigits'];
}
if ($this->settings['durationPeriodRated']) {
$this->durationPeriodRated = $this->settings['durationPeriodRated'];
}
if ($this->settings['trafficSizeRated']) {
$this->trafficSizeRated = $this->settings['trafficSizeRated'];
}
if ($this->settings['rate_longer_than']) {
// if call is shorter than this, it has zero cost
$this->rate_longer_than = $this->settings['rate_longer_than'];
}
if ($this->settings['min_duration']) {
// if call is shorter than this, it has zero cost
$this->min_duration = $this->settings['min_duration'];
}
if ($this->settings['increment']) {
$this->increment = $this->settings['increment'];
}
if ($this->settings['database_backend']) {
$this->database_backend = $this->settings['database_backend'];
}
}
public function calculateAudio($dictionary)
{
// used for calculate rate for audio application
$this->RatingTables = $dictionary['RatingTables'];
$this->callId = $dictionary['callId'];
$this->timestamp = $dictionary['timestamp'];
$this->duration = $dictionary['duration'];
$this->traffic = 2 * ($dictionary['inputTraffic'] + $dictionary['outputTraffic']);
$this->DestinationId = $dictionary['DestinationId'];
$this->BillingPartyId = $dictionary['BillingPartyId'];
$this->domain = $dictionary['domain'];
$this->gateway = $dictionary['gateway'];
$this->ResellerId = $dictionary['ResellerId'];
$this->aNumber = $dictionary['aNumber'];
$this->cNumber = $dictionary['cNumber'];
$this->ENUMtld = $dictionary['ENUMtld'];
if ($this->rate_longer_than && $this->duration < $this->rate_longer_than) {
//syslog(LOG_NOTICE, "Duration less than minimum $this->rate_longer_than");
$this->rateInfo .= " Duration < $this->rate_longer_than s\n";
return true;
}
if ($this->ENUMtld && $this->ENUMtld != 'n/a' && $this->ENUMtld != 'none' && $this->RatingTables->ENUMtlds[$this->ENUMtld]) {
$this->ENUMdiscount = $this->RatingTables->ENUMtlds[$this->ENUMtld]['discount'];
if (!is_numeric($this->ENUMdiscount) || $this->ENUMdiscount < 0 || $this->ENUMdiscount > 100) {
syslog(LOG_NOTICE, "Error: ENUM discount for tld $this->ENUMtld must be between 0 and 100");
}
}
if (!$this->gateway) {
$this->gateway = "0.0.0.0";
}
if (!$this->duration) {
$this->duration = 0;
}
if (!$this->traffic) {
$this->traffic = 0;
}
$this->application=$dictionary['application'];
if (!$this->application) {
$this->application = 'audio';
}
$durationRate = 0;
$foundRates = array();
if (!$this->DestinationId) {
syslog(LOG_NOTICE, "Error: Cannot calculate rate without destination id for callid=$this->callId");
return false;
}
if (!$this->lookupDestinationDetails()) {
// get region, increment and other per destination details
syslog(LOG_NOTICE, "Error: Cannot find destination details for call_id=$this->callId, dest_id=$this->DestinationId)");
return false;
}
if (!$this->lookupProfiles()) {
// get profiles for the billing party
syslog(LOG_NOTICE, "Error: Cannot find any profiles for call_id=$this->callId, dest_id=$this->DestinationId)");
return false;
}
// lookup discounts if any
$this->lookupDiscounts();
$this->startTimeBilling = getLocalTime($this->billingTimezone, $this->timestamp);
list($dateText,$timeText) = explode(" ", trim($this->startTimeBilling));
$Bdate = explode("-", $dateText);
$Btime = explode(":", $timeText);
$this->timestampBilling = mktime($Btime[0], $Btime[1], $Btime[2], $Bdate[1], $Bdate[2], $Bdate[0]);
$this->startTimeBilling = Date("Y-m-d H:i:s", $this->timestampBilling);
$this->trafficKB = number_format($this->traffic/1024, 0, "", "");
// check min_duration and increment per destination
if ($this->increment >= 1) {
// increase the billed duration to the next increment
$this->duration = $this->increment * ceil($this->duration / $this->increment);
}
if ($this->max_duration && $this->duration > $this->max_duration) {
// limit the maximum duration for rating
$this->duration = $this->max_duration;
}
$this->rateSyslog = "";
if ($this->duration) {
if ($this->increment >= 1) {
$this->rateInfo .=
" Increment: $this->increment s\n";
$this->rateSyslog .= sprintf("Increment=%s ", $this->increment);
}
if ($this->min_duration) {
$this->rateInfo .=
" Min duration: $this->min_duration s\n";
$this->rateSyslog .= sprintf("MinDuration=%s ", $this->min_duration);
}
if ($this->max_duration) {
$this->rateInfo .=
" Max duration: $this->max_duration s\n";
$this->rateSyslog .= sprintf("MaxDuration=%s ", $this->max_duration);
}
if ($this->max_price) {
$this->rateInfo .=
" Max price: $this->max_price\n";
$this->rateSyslog .= sprintf("MaxPrice=%s ", $this->max_price);
}
unset($IntervalsForPricing);
$this->rateInfo .=
" Duration: $this->duration s\n".
" App: $this->application\n".
" Destination: $this->DestinationId\n".
" Customer: $this->CustomerProfile\n";
if ($this->region) {
$this->rateInfo .=
" Region: $this->region\n";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .=
" Discount: ";
}
if ($this->discount_connect) {
$this->rateInfo .= " connect $this->discount_connect% ";
}
if ($this->discount_duration) {
$this->rateInfo .= " duration $this->discount_duration% ";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .= "\n";
}
if ($this->ENUMtld && $this->ENUMtld != 'none' && $this->ENUMtld != 'n/a') {
$this->rateInfo .=
" ENUM tld: $this->ENUMtld\n".
" ENUM discount: $this->ENUMdiscount%\n";
}
$i=0;
$durationRatedTotal=0;
// get recursively a set of arrays with rates
// until we billed the whole duration
while ($durationRatedTotal < $this->duration) {
if ($i == "0") {
$dayofweek = date("w", $this->timestampBilling);
$hourofday = date("G", $this->timestampBilling);
$dayofyear = date("Y-m-d", $this->timestampBilling);
} else {
$dayofweek = date("w", $this->timestampBilling+$durationRatedTotal);
$hourofday = $foundRate['nextHourOfDay'];
$dayofyear = date("Y-m-d", $this->timestampBilling+$durationRatedTotal);
}
$foundRate = $this->lookupRateAudio($dayofyear, $dayofweek, $hourofday, $durationRatedTotal);
$durationRatedTotal = $durationRatedTotal + $foundRate['duration'];
if (!$foundRate['rate']) {
$this->broken_rate=true;
return false;
}
$foundRates[] = $foundRate;
$i++;
if ($i > 10) {
// possible loop because of wrong coding make sure we end this loop somehow
$body="Rating of call $this->callId (DestId=$this->DestinationId) has more than 10 spans. It could be a serious bug.\n";
mail($this->toEmail, "CDRTool rating problem", $body, $this->extraHeaders);
syslog(LOG_NOTICE, "Error: Rating of call $this->callId (DestId=$this->DestinationId) has more than 10 spans.");
break;
}
}
}
$j = 0;
$span = 0;
foreach ($foundRates as $thisRate) {
$spanPrice = 0;
$span++;
if ($j > 0) {
$payConnect = 0;
$durationForRating = $thisRate['duration'];
} else {
$payConnect=1;
if ($this->min_duration && $this->duration < $this->min_duration) {
$durationForRating = $this->min_duration;
} else {
$durationForRating = $thisRate['duration'];
}
}
$connectCost = $thisRate['values']['connectCost'];
$durationRate = $thisRate['values']['durationRate'];
// apply discounts for connect
if ($this->discount_connect) {
$connectCost = $connectCost - $connectCost * $this->discount_connect / 100;
}
// apply discounts for duration
if ($this->discount_duration) {
$durationRate = $durationRate - $durationRate * $this->discount_duration / 100;
}
$connectCostIn = $thisRate['values']['connectCostIn'];
$durationRateIn = $thisRate['values']['durationRateIn'];
if ($span=="1") {
$connectCostSpan = $connectCost;
$this->connectCost = number_format($connectCost/$this->priceDenominator, $this->priceDecimalDigits);
$connectCostSpanIn = $connectCostIn;
$this->connectCostIn = number_format($connectCostIn/$this->priceDenominator, $this->priceDecimalDigits);
} else {
$connectCostSpan=0;
$connectCostSpanIn=0;
}
$connectCostPrint = number_format($connectCostSpan/$this->priceDenominator, $this->priceDecimalDigits);
$durationRatePrint = number_format($durationRate/$this->priceDenominator, $this->priceDecimalDigits);
$connectCostPrintIn = number_format($connectCostSpanIn/$this->priceDenominator, $this->priceDecimalDigits);
$durationRatePrintIn = number_format($durationRateIn/$this->priceDenominator, $this->priceDecimalDigits);
if (!$connectCostSpan) $connectCostSpan=0;
if (!$durationRate) $durationRate=0;
if (!$connectCostSpanIn) $connectCostSpanIn=0;
if (!$durationRateIn) $durationRateIn=0;
if (!$this->inputTraffic) $this->inputTraffic=0;
if (!$this->outputTraffic) $this->outputTraffic=0;
if ($span>1) $this->rateInfo .= "--\n";
/*
durationRate*durationForRating/durationPeriodRated/priceDenominator+
trafficRate/priceDenominator/trafficSizeRated*(inputTraffic+outputTraffic)/8");
$durationRate*$durationForRating/$this->durationPeriodRated/$this->priceDenominator+
$trafficRate/$this->priceDenominator/$this->trafficSizeRated*($this->inputTraffic+$this->outputTraffic)/8");
*/
$spanPrice = $durationRate * $durationForRating / $this->durationPeriodRated / $this->priceDenominator;
$this->price = $this->price+$spanPrice;
$spanPricePrint = number_format($spanPrice, $this->priceDecimalDigits);
$spanPriceIn = $durationRateIn * $durationForRating / $this->durationPeriodRated / $this->priceDenominator;
$this->priceIn = $this->priceIn+$spanPriceIn;
$spanPricePrintIn = number_format($spanPriceIn, $this->priceDecimalDigits);
if ($span=="1" && $thisRate['profile']) {
if ($connectCostIn) {
$this->rateInfo .=
" Connect in: $connectCostPrintIn\n";
}
$this->rateInfo .=
" Connect: $connectCostPrint\n".
" StartTime: $this->startTimeBilling\n".
"--\n";
$this->rateSyslog .= "ConnectFee=$connectCostPrint ";
$this->price = $this->price + $connectCostSpan / $this->priceDenominator * $payConnect;
$this->priceIn = $this->priceIn + $connectCostSpanIn / $this->priceDenominator * $payConnect;
}
$this->rateInfo .=
" Span: $span\n".
" Duration: $durationForRating s\n";
$this->rateSyslog .= sprintf(
"CallId=%s Span=%s Duration=%s DestId=%s %s",
$this->callId,
$span,
$durationForRating,
$this->DestinationId,
$thisRate['customer']
);
if ($thisRate['profile']) {
$this->rateInfo .=
" ProfileId: $thisRate[profile] / $thisRate[day]\n".
" RateId: $thisRate[rate] / $thisRate[interval]h\n".
" Rate: $durationRatePrint / $this->durationPeriodRated s\n".
" Price: $spanPricePrint\n";
if ($spanPriceIn) {
$this->rateInfo .=
" Price in: $spanPricePrintIn\n";
}
$this->rateSyslog .= sprintf(
" Profile=%s Period=%s Rate=%s Interval=%s Cost=%s/%s",
$thisRate['profile'],
$thisRate['day'],
$thisRate['rate'],
$thisRate['interval'],
$durationRatePrint,
$this->durationPeriodRated
);
} else {
$this->rateInfo .=
" ProfileId: none\n".
" RateId: none\n";
$this->rateSyslog .= " Profile=none, Rate=none";
}
$this->rateSyslog .= " Price=".sprintf("%.4f", $spanPrice);
$this->rateSyslog .= " PriceIn=".sprintf("%.4f", $spanPriceIn);
if ($this->discount_connect) {
$this->rateSyslog .= sprintf(" DisCon=%s", $this->discount_connect);
}
if ($this->discount_duration) {
$this->rateSyslog .= sprintf(" DisDur=%s", $this->discount_duration);
}
syslog(LOG_NOTICE, $this->rateSyslog);
$j++;
}
if ($this->priceIn) {
$this->rateInfo .= "--\n".
" Price out: ".sprintf("%.4f", $this->price)."\n".
" Price in: ".sprintf("%.4f", $this->priceIn)."\n".
" Margin: ".sprintf("%.4f", $this->price-$this->priceIn)."\n";
}
$this->rateInfo=trim($this->rateInfo);
if ($this->max_price && $this->price > $this->max_price) {
$this->price = $this->max_price;
}
if ($this->ENUMdiscount) {
$this->priceBeforeDiscount = sprintf("%.4f", $this->price);
$this->price = $this->price - $this->price * $this->ENUMdiscount / 100;
$this->price = sprintf("%.4f", $this->price);
$this->rateInfo .=
"\n--\n".
" Total: $this->priceBeforeDiscount\n".
" Total after discount: $this->price\n";
}
$this->price = sprintf("%.4f", $this->price);
$this->pricePrint = number_format($this->price, $this->priceDecimalDigits);
return true;
}
public function calculateMessage($dictionary)
{
// used for calculate rate for SMS application
$this->RatingTables = $dictionary['RatingTables'];
$this->callId = $dictionary['callId'];
$this->timestamp = $dictionary['timestamp'];
$this->DestinationId = $dictionary['DestinationId'];
$this->BillingPartyId = $dictionary['BillingPartyId'];
$this->domain = $dictionary['domain'];
$this->gateway = $dictionary['gateway'];
$this->ResellerId = $dictionary['ResellerId'];
$this->aNumber = $dictionary['aNumber'];
$this->cNumber = $dictionary['cNumber'];
if (!$this->gateway) {
$this->gateway = "0.0.0.0";
}
$this->application = 'sms';
$foundRates=array();
if (!$this->DestinationId) {
syslog(LOG_NOTICE, "Error calculateMessage(): Cannot calculate rate without destination id");
return false;
}
if (!$this->lookupProfiles()) {
// get profiles for the billing party
syslog(LOG_NOTICE, "Error: calculateMessage() Cannot find any profiles for call_id=$this->callId, dest_id=$this->DestinationId)");
return false;
}
// lookup discounts if any
$this->lookupDiscounts();
$this->startTimeBilling = getLocalTime($this->billingTimezone, $this->timestamp);
list($dateText,$timeText) = explode(" ", trim($this->startTimeBilling));
$Bdate = explode("-", $dateText);
$Btime = explode(":", $timeText);
$this->timestampBilling = mktime($Btime[0], $Btime[1], $Btime[2], $Bdate[1], $Bdate[2], $Bdate[0]);
$dayofweek = date("w", $this->timestampBilling);
$hourofday = date("G", $this->timestampBilling);
$dayofyear = date("Y-m-d", $this->timestampBilling);
$this->rateInfo .=
" App: sms\n".
" Destination: $this->DestinationId\n".
" Customer: $this->CustomerProfile\n";
if ($this->region) {
$this->rateInfo .=
" Region: $this->region\n";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .=
" Discount: ";
}
if ($this->discount_connect) {
$this->rateInfo .= " connect $this->discount_connect% ";
}
if ($this->discount_duration || $this->discount_connect) {
$this->rateInfo .= "\n";
}
$foundRate = $this->lookupRateMessage($dayofyear, $dayofweek, $hourofday);
if (is_array($foundRate)) {
$this->price = number_format($foundRate['values']['connectCost'] / $this->priceDenominator, $this->priceDecimalDigits);
$this->price = sprintf("%.4f", $this->price);
$this->pricePrint = $this->price;
$this->rateInfo .=
" ProfileId: $foundRate[profile] / $foundRate[day]\n".
" RateId: $foundRate[rate]\n".
" Price: $this->price\n";
return true;
} else {
return false;
}
}
private function lookupDiscounts()
{
// get discounts for customer per region if set otherwise per destination id
if (!$this->CustomerProfile) {
return false;
}
if ($this->region) {
$_field = 'region';
$_value = $this->region;
} else {
$_field = 'destination';
$_value = $this->DestinationId;
}
if ($this->CustomerProfile == 'default') {
$query = sprintf(
"select * from billing_discounts
where subscriber = ''
and domain = ''
and gateway = ''
and application = '%s'
and %s = '%s'
",
addslashes($this->application),
addslashes($_field),
addslashes($_value)
);
} else {
$els = explode("=", $this->CustomerProfile);
$query = sprintf(
"select * from billing_discounts
where %s = '%s'
and application = '%s'
and %s = '%s'
",
addslashes($els[0]),
addslashes($els[1]),
addslashes($this->application),
addslashes($_field),
addslashes($_value)
);
}
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$this->db->next_record();
if ($this->db->f('connect') > 0 && $this->db->f('connect') <=100) {
$this->discount_connect = $this->db->f('connect');
}
if ($this->db->f('duration') > 0 && $this->db->f('duration') <=100) {
$this->discount_duration = $this->db->f('duration');
}
}
return true;
}
private function lookupDestinationDetails()
{
// get rating related details for the destination id
if (!$this->DestinationId) {
syslog(LOG_NOTICE, "Error: Cannot lookup destination details without a destination id");
return false;
}
// mysql backend
$query = sprintf(
"select * from destinations
where dest_id = '%s'
and (reseller_id = %d or reseller_id = 0) order by reseller_id desc limit 1
",
addslashes($this->DestinationId),
addslashes($this->ResellerId)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$this->db->next_record();
$this->region = $this->db->Record['region'];
$this->max_duration = $this->db->Record['max_duration'];
$this->max_price = $this->db->Record['max_price'];
if ($this->db->Record['increment']) {
$this->increment = $this->db->Record['increment'];
}
if ($this->db->Record['min_duration']) {
$this->min_duration = $this->db->Record['min_duration'];
}
}
return true;
}
private function lookupProfiles()
{
unset($this->allProfiles);
/*
lookup the profile_name in billing_customers in the following order:
- subscriber, domain, gateway (based on $dayofweek):
+ subscriber, username@gateway, domain, gateway (based on $dayofweek):
- profile_workday matches days [1-5] (Work-day)
- profile_weekend matches days [6-0] (Week-end)
- week starts with 0 Sunday and ends with 6 Saturday
Alternatively look for profile_workday_alt and profile_weekend_alt
If no rates are found for destination in the profileX,
than lookup rates in profileX_alt
*/
// mysql backend
+ list($username, $domain) = explode("@", $this->BillingPartyId);
+ $trusted_peer_account = sprintf("%s@%s", $username, $this->gateway);
$query = sprintf(
"select * from billing_customers
where
(subscriber = '%s' and domain = '' and gateway = '' )
+ or (subscriber = '%s' and domain = '' and gateway = '' )
or (domain = '%s' and subscriber = '' and gateway = '' )
or (gateway = '%s' and subscriber = '' and domain = '' )
or (subscriber = '' and domain = '' and gateway = '' )
order by subscriber desc, domain desc, gateway desc limit 1
",
addslashes($this->BillingPartyId),
+ addslashes($trusted_peer_account),
addslashes($this->domain),
addslashes($this->gateway)
);
+ dprint_sql($query);
+
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$this->db->next_record();
if ($this->db->Record['subscriber']) {
- $this->CustomerProfile = sprintf("subscriber=%s", $this->db->Record['subscriber']);
+ if ($this->db->Record['subscriber'] == $trusted_peer_account) {
+ $this->CustomerProfile = sprintf("remote_account=%s", $this->db->Record['subscriber']);
+ } else {
+ $this->CustomerProfile = sprintf("local_account=%s", $this->db->Record['subscriber']);
+ }
} elseif ($this->db->Record['domain']) {
$this->CustomerProfile = sprintf("domain=%s", $this->db->Record['domain']);
} elseif ($this->db->Record['gateway']) {
- $this->CustomerProfile = sprintf("gateway=%s", $this->db->Record['gateway']);
+ $this->CustomerProfile = sprintf("trusted_peer=%s", $this->db->Record['gateway']);
} else {
$this->CustomerProfile = "default";
}
if (!$this->db->Record['profile_name1']) {
$log = sprintf(
"Error: customer %s (id=%d) has no weekday profile assigned in profiles table",
$this->CustomerProfile,
$this->db->Record['id']
);
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->db->Record['profile_name2']) {
$log = sprintf(
"Error: customer %s (id=%d) has no weekend profile assigned in profiles table",
$this->CustomerProfile,
$this->db->Record['id']
);
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->db->Record['timezone']) {
$log = sprintf(
"Error: missing timezone for customer %s",
$this->CustomerProfile
);
syslog(LOG_NOTICE, $log);
return false;
}
$this->billingTimezone = $this->db->Record['timezone'];
$this->allProfiles = array(
"profile_workday" => $this->db->Record['profile_name1'],
"profile_weekend" => $this->db->Record['profile_name2'],
"profile_workday_alt" => $this->db->Record['profile_name1_alt'],
"profile_weekend_alt" => $this->db->Record['profile_name2_alt'],
"timezone" => $this->db->Record['timezone']
);
if ($this->db->Record['increment']) {
$this->increment = $this->db->Record['increment'];
}
if ($this->db->Record['min_duration']) {
$this->min_duration = $this->db->Record['min_duration'];
}
return true;
} else {
$log = sprintf(
"Error: no customer found in billing_customers table for billing party=%s, domain=%s, gateway=%s",
$this->BillingPartyId,
$this->domain,
$this->gateway
);
syslog(LOG_NOTICE, $log);
return false;
}
}
private function lookupRateAudio($dayofyear, $dayofweek, $hourofday, $durationRatedAlready)
{
/*
// Required information from CDR structure
$this->BillingPartyId # calling subscriber
$this->domain # multiple callers may belong to same domain
$this->gateway # multiple callers may belong to the same gateway
$this->cNumber # E164 destination prefixed with 00 (e.g. 0041 CH)
$this->DestinationId # longest matched DestinationId
$this->region # region the destination belongs to
// pertinent to the curent rating SPAN (a span = same profile like evening hours)
$hourofday # which hour of teh day started for peak/ofpeak rates
$dayofweek # which day of the week for matching profiles
$dayofyear # which day of the year for matching holidays
$durationRatedAlready= the full duration for which a profile is defined (e.g. 0800-1800)
// the call is called recursively until the $durationRatedAlready = $CDR->duration
// when a call spans multiple profiles. If we span multiple profiles we must call
// the function again to lookup the corect rates
Rating logic
------------
1. using the profile_name found, lookup the rate_name based
on $hourofday in billing_profiles
- the day may be split in maximum 4 periods
- each day 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'
2. lookup in billing_rates the record having same name found above
and billing_rates.destination = $this->DestinationId
- return an array with all the values to
$this->calculateAudio() function that called us
*/
// get work-day or weekend profile
if ($this->RatingTables->holidays[$dayofyear]) {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
} else {
if ($dayofweek >=1 && $dayofweek <=5) {
$this->profileName = $this->allProfiles['profile_workday'];
$this->profileNameAlt = $this->allProfiles['profile_workday_alt'];
$this->PeriodOfProfile = "weekday";
} else {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
}
}
// get rate for the time of the day
$timestampNextProfile = $this->timestampBilling + $durationRatedAlready;
$profileValues = $this->RatingTables->profiles[$this->profileName];
if (is_array($profileValues)) {
$this->profileNameLog = $this->profileName;
if ($hourofday < $profileValues['hour1']) {
$this->rateName = $profileValues['rate_name1'];
$this->timeInterval = "0-".$profileValues['hour1'];
$foundProfile = $profileValues['hour1'];
$this->nextProfile = $profileValues['hour1'];
} elseif ($hourofday < $profileValues['hour2']) {
$this->rateName = $profileValues['rate_name2'];
$this->timeInterval = $profileValues['hour1']."-".$profileValues['hour2'];
$foundProfile = $profileValues['hour2'];
$this->nextProfile = $profileValues['hour2'];
} elseif ($hourofday < $profileValues['hour3']) {
$this->rateName = $profileValues['rate_name3'];
$this->timeInterval = $profileValues['hour2']."-".$profileValues['hour3'];
$foundProfile = $profileValues['hour3'];
$this->nextProfile = $profileValues['hour3'];
} elseif ($hourofday < $profileValues['hour4']) {
$this->rateName = $profileValues['rate_name4'];
$this->timeInterval = $profileValues['hour3']."-".$profileValues['hour4'];
$foundProfile = $profileValues['hour4'];
$this->nextProfile = 0;
}
if ($this->rateName) {
$found_history=false;
//get historical rating if exists
if (is_array($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application])) {
$h=0;
foreach (($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application]) as $_idx) {
$h++;
if ($_idx['startDate'] <= $this->timestamp) {
if ($_idx['endDate'] > $this->timestamp) {
// found historical rate
$found_history=true;
$this->rateValues=$_idx;
break;
} else {
$_log = sprintf("Interval missmatch %s < %s", $_idx['endDate'], $this->timestamp);
continue;
}
} else {
$_log = sprintf("Interval missmatch %s > %s", $_idx['startDate'], $this->timestamp);
continue;
}
}
}
if (!$found_history) {
if ($this->region) {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->region);
if (!$this->rateValues) {
// try the destination as last resort
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
}
}
}
$profileValuesAlt = $this->RatingTables->profiles[$this->profileNameAlt];
if (!$this->rateValues && is_array($profileValuesAlt)) {
$this->profileNameLog = $this->profileNameAlt;
if ($hourofday < $profileValuesAlt['hour1']) {
$this->rateName = $profileValuesAlt['rate_name1'];
$this->timeInterval = "0-".$profileValuesAlt['hour1'];
$foundProfile = $profileValuesAlt['hour1'];
$this->nextProfile = $profileValuesAlt['hour1'];
} elseif ($hourofday < $profileValuesAlt['hour2']) {
$this->rateName = $profileValuesAlt['rate_name2'];
$this->timeInterval = $profileValuesAlt['hour1']."-".$profileValuesAlt['hour2'];
$foundProfile = $profileValuesAlt['hour2'];
$this->nextProfile = $profileValuesAlt['hour2'];
} elseif ($hourofday < $profileValuesAlt['hour3']) {
$this->rateName = $profileValuesAlt['rate_name3'];
$this->timeInterval = $profileValuesAlt['hour2']."-".$profileValuesAlt['hour3'];
$foundProfile = $profileValuesAlt['hour3'];
$this->nextProfile = $profileValuesAlt['hour3'];
} elseif ($hourofday < $profileValuesAlt['hour4']) {
$this->rateName = $profileValuesAlt['rate_name4'];
$this->timeInterval = $profileValuesAlt['hour3']."-".$profileValuesAlt['hour4'];
$foundProfile = $profileValuesAlt['hour4'];
$this->nextProfile = 0;
}
if ($this->rateName) {
$found_history=false;
//get historical rating if exists
if (is_array($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application])) {
$h=0;
foreach (($this->RatingTables->ratesHistory[$this->rateName][$this->DestinationId][$this->application]) as $_idx) {
$h++;
if ($_idx['startDate'] <= $this->timestamp) {
if ($_idx['endDate'] > $this->timestamp) {
// found historical rate
$found_history=true;
$this->rateValues=$_idx;
break;
} else {
$_log = sprintf("Interval missmatch %s < %s", $_idx['endDate'], $this->timestamp);
continue;
}
} else {
$_log = sprintf("Interval missmatch %s > %s", $_idx['startDate'], $this->timestamp);
continue;
}
}
}
if (!$found_history) {
if ($this->region) {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->region);
// try destination as last resort
if (!$this->rateValues) {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues = $this->lookupRateValuesAudio($this->rateName, $this->DestinationId);
}
}
}
}
if (!$this->rateValues) {
$this->rateNotFound=true;
$log = sprintf(
"Error: Cannot find rates for callid=%s, billing party=%s, customer %s, gateway=%s, destination=%s, profile=%s, app=%s",
$this->callId,
$this->BillingPartyId,
$this->CustomerProfile,
$this->gateway,
$this->DestinationId,
$this->profileName,
$this->application
);
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->nextProfile == "24") $this->nextProfile = 0;
$DST = Date("I", $timestampNextProfile);
if (!$this->nextProfile) {
// check it we change daylight saving time tomorrow
// yes this cann happen and we must apply a different rate
$timestampNextProfile =$timestampNextProfile+24*3600;
$DSTNext = Date("I", $timestampNextProfile);
if ($DST != $DSTNext) {
if ($DSTNext==0) {
$timestampNextProfile = $timestampNextProfile+3600;
} elseif ($DSTNext==1) {
$timestampNextProfile = $timestampNextProfile-3600;
}
}
}
// see if we have minimum duration or increment
if ($this->rateValues['increment']) {
// increase the billed duration to the next increment
$this->duration = $this->rateValues['increment'] * ceil($this->duration / $this->rateValues['increment']);
}
$durationToRate=$this->duration-$durationRatedAlready;
$month = Date("m", $timestampNextProfile);
$day = Date("d", $timestampNextProfile);
$year = Date("Y", $timestampNextProfile);
$nextProfileTimestamp = mktime($this->nextProfile, 0, 0, $month, $day, $year);
$npdt=Date("Y-m-d H:i", $nextProfileTimestamp);
$timeTillNextProfile = $nextProfileTimestamp - $this->timestampBilling;
if ($durationToRate > $timeTillNextProfile) {
$diff = $durationToRate - $timeTillNextProfile;
$this->durationRated = $timeTillNextProfile;
} else {
$this->durationRated = $durationToRate;
}
$rate = array(
"customer" => $this->CustomerProfile,
"application" => $this->application,
"profile" => $this->profileNameLog,
"day" => $this->PeriodOfProfile,
"destinationId" => $this->DestinationId,
"duration" => $this->durationRated,
"rate" => $this->rateName,
"values" => $this->rateValues,
"interval" => $this->timeInterval,
"nextHourOfDay" => $this->nextProfile
);
return $rate;
}
private function lookupRateMessage($dayofyear, $dayofweek, $hourofday)
{
/*
// Required information from CDR structure
$this->BillingPartyId # calling subscriber
$this->domain # multiple callers may belong to same domain
$this->gateway # multiple callers may belong to the same gateway
$this->cNumber # E164 destination prefixed with 00 (e.g. 0041 CH)
$this->DestinationId # longest matched DestinationId
$this->region # region the destination belongs to
// pertinent to the curent rating SPAN (a span = same profile like evening hours)
$hourofday # which hour of teh day started for peak/ofpeak rates
$dayofweek # which day of the week for matching profiles
$dayofyear # which day of the year for matching holidays
$durationRatedAlready= the full duration for which a profile is defined (e.g. 0800-1800)
// the call is called recursively until the $durationRatedAlready = $CDR->duration
// when a call spans multiple profiles. If we span multiple profiles we must call
// the function again to lookup the corect rates
Rating logic
------------
1. using the profile_name found, lookup the rate_name based
on $hourofday in billing_profiles
- the day may be split in maximum 4 periods
- each day 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'
2. lookup in billing_rates the record having same name found above
and billing_rates.destination = $this->DestinationId
- return an array with all the values to
$this->calculateAudio() function that called us
*/
// get work-day or weekend profile
if ($this->RatingTables->holidays[$dayofyear]) {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
} else {
if ($dayofweek >=1 && $dayofweek <=5) {
$this->profileName = $this->allProfiles['profile_workday'];
$this->profileNameAlt = $this->allProfiles['profile_workday_alt'];
$this->PeriodOfProfile = "weekday";
} else {
$this->profileName = $this->allProfiles['profile_weekend'];
$this->profileNameAlt = $this->allProfiles['profile_weekend_alt'];
$this->PeriodOfProfile = "weekend";
}
}
// get rate for the time of the day
$timestampNextProfile = $this->timestampBilling + $durationRatedAlready;
$profileValues = $this->RatingTables->profiles[$this->profileName];
if (is_array($profileValues)) {
$this->profileNameLog = $this->profileName;
if ($hourofday < $profileValues['hour1']) {
$this->rateName = $profileValues['rate_name1'];
$this->timeInterval = "0-".$profileValues['hour1'];
$foundProfile = $profileValues['hour1'];
} elseif ($hourofday < $profileValues['hour2']) {
$this->rateName = $profileValues['rate_name2'];
$this->timeInterval = $profileValues['hour1']."-".$profileValues['hour2'];
$foundProfile = $profileValues['hour2'];
} elseif ($hourofday < $profileValues['hour3']) {
$this->rateName = $profileValues['rate_name3'];
$this->timeInterval = $profileValues['hour2']."-".$profileValues['hour3'];
$foundProfile = $profileValues['hour3'];
} elseif ($hourofday < $profileValues['hour4']) {
$this->rateName = $profileValues['rate_name4'];
$this->timeInterval = $profileValues['hour3']."-".$profileValues['hour4'];
$foundProfile = $profileValues['hour4'];
}
if ($this->rateName) {
if ($this->region) {
$this->rateValues=$this->lookupRateValuesMessage($this->rateName, $this->region);
if (!$this->rateValues) {
// try the destination as last resort
$this->rateValues=$this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues=$this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
}
}
$profileValuesAlt = $this->RatingTables->profiles[$this->profileNameAlt];
if (!$this->rateValues && is_array($profileValuesAlt)) {
$this->profileNameLog = $this->profileNameAlt;
if ($hourofday < $profileValuesAlt['hour1']) {
$this->rateName = $profileValuesAlt['rate_name1'];
$this->timeInterval = "0-".$profileValuesAlt['hour1'];
$foundProfile = $profileValuesAlt['hour1'];
} elseif ($hourofday < $profileValuesAlt['hour2']) {
$this->rateName = $profileValuesAlt['rate_name2'];
$this->timeInterval = $profileValuesAlt['hour1']."-".$profileValuesAlt['hour2'];
$foundProfile = $profileValuesAlt['hour2'];
} elseif ($hourofday < $profileValuesAlt['hour3']) {
$this->rateName = $profileValuesAlt['rate_name3'];
$this->timeInterval = $profileValuesAlt['hour2']."-".$profileValuesAlt['hour3'];
$foundProfile = $profileValuesAlt['hour3'];
} elseif ($hourofday < $profileValuesAlt['hour4']) {
$this->rateName = $profileValuesAlt['rate_name4'];
$this->timeInterval = $profileValuesAlt['hour3']."-".$profileValuesAlt['hour4'];
$foundProfile = $profileValuesAlt['hour4'];
}
if ($this->rateName) {
if ($this->region) {
$this->rateValues = $this->lookupRateValuesMessage($this->rateName, $this->region);
// try destination as last resort
if (!$this->rateValues) {
$this->rateValues = $this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
} else {
$this->rateValues = $this->lookupRateValuesMessage($this->rateName, $this->DestinationId);
}
}
}
if (!$this->rateValues) {
$this->rateNotFound=true;
$log=sprintf(
"Error: Cannot find rates for callid=%s, billing party=%s, customer %s, gateway=%s, destination=%s, profile=%s, app=sms",
$this->callId,
$this->BillingPartyId,
$this->CustomerProfile,
$this->gateway,
$this->DestinationId,
$this->profileName
);
syslog(LOG_NOTICE, $log);
return false;
}
$rate = array(
"customer" => $this->CustomerProfile,
"application" => $this->application,
"profile" => $this->profileNameLog,
"day" => $this->PeriodOfProfile,
"destinationId" => $this->DestinationId,
"rate" => $this->rateName,
"values" => $this->rateValues,
);
return $rate;
}
public function MaxSessionTime($dictionary)
{
// Used for prepaid application to return maximum session time based on a prepaid balance
$this->rateValuesCache = array();
$this->MaxSessionTimeSpans = 0;
$durationRate = 0;
/////////////////////////////////////////////////////
// required fields passed from the CDR structure
//
$this->timestamp = time();
$this->callId = $dictionary['callId'];
$this->DestinationId = $dictionary['DestinationId'];
$this->BillingPartyId = $dictionary['BillingPartyId'];
$this->domain = $dictionary['domain'];
$this->duration = $dictionary['duration'];
$this->aNumber = $dictionary['aNumber'];
$this->cNumber = $dictionary['cNumber'];
$this->gateway = $dictionary['gateway'];
$this->RatingTables = $dictionary['RatingTables'];
$this->application = $dictionary['application'];
$this->ResellerId = $dictionary['ResellerId'];
$Balance = $dictionary['Balance'];
if (!$this->application) $this->application='audio';
if (!$this->DestinationId) {
$log = sprintf("Error: no DestinationId supplied in MaxSessionTime()");
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->lookupDestinationDetails()) {
return false;
}
if (!$this->lookupProfiles()) {
return false;
}
$this->startTimeBilling = getLocalTime($this->billingTimezone, $this->timestamp);
list($dateText,$timeText) = explode(" ", trim($this->startTimeBilling));
$Bdate = explode("-", $dateText);
$Btime = explode(":", $timeText);
$this->timestampBilling = mktime($Btime[0], $Btime[1], $Btime[2], $Bdate[1], $Bdate[2], $Bdate[0]);
$this->startTimeBilling = Date("Y-m-d H:i:s", $this->timestampBilling);
$i=0;
$durationRatedTotal=0;
while ($Balance > 0) {
$span++;
$this->MaxSessionTimeSpans++;
if ($i == "0") {
$dayofweek = date("w", $this->timestampBilling);
$hourofday = date("G", $this->timestampBilling);
$dayofyear = date("Y-m-d", $this->timestampBilling);
} else {
$dayofweek = date("w", $this->timestampBilling+$durationRatedTotal);
$hourofday = $foundRate['nextHourOfDay'];
$dayofyear = date("Y-m-d", $this->timestampBilling+$durationRatedTotal);
}
$foundRate = $this->lookupRateAudio($dayofyear, $dayofweek, $hourofday, $durationRatedTotal);
if ($this->rateNotFound) {
// break here to avoid loops
break;
}
$thisRate=$foundRate;
if ($j > 0) {
$payConnect=0;
$durationForRating = $thisRate['duration'];
} else {
$payConnect=1;
if ($this->min_duration && $this->duration < $this->min_duration) {
$durationForRating=$this->min_duration;
} else {
$durationForRating=$thisRate['duration'];
}
}
$j++;
$connectCost = $thisRate['values']['connectCost'];
$durationRate = $thisRate['values']['durationRate'];
if ($span=="1" && !$dictionary['skipConnectCost']) {
$this->connectCost=number_format($connectCost/$this->priceDenominator, $this->priceDecimalDigits);
$connectCostSpan=$connectCost;
$setupBalanceRequired=$connectCost/$this->priceDenominator;
if ($connectCost && $Balance <= $setupBalanceRequired) {
syslog(LOG_NOTICE, "Balance too small: $Balance <= $setupBalanceRequired");
return false;
}
$Balance = $Balance-$setupBalanceRequired;
} else {
$connectCostSpan=0;
$setupBalanceRequired=0;
}
$connectCostPrint = number_format($connectCostSpan/$this->priceDenominator, $this->priceDecimalDigits);
$durationRatePrint = number_format($durationRate/$this->priceDenominator, $this->priceDecimalDigits);
$spanPrice = $this->price+$setupBalanceRequired*$payConnect+
$durationRate*$durationForRating/$this->durationPeriodRated/$this->priceDenominator;
if ($Balance > $spanPrice) {
$Balance = $Balance-$spanPrice;
$durationRatedTotal = $durationRatedTotal+ $foundRate['duration'];
} else {
$durationAllowedinThisSpan = $Balance /
$durationRate * $this->durationPeriodRated * $this->priceDenominator;
$rateOfThisSpan=$durationRate/$this->priceDenominator;
$durationRatedTotal=$durationRatedTotal + $durationAllowedinThisSpan;
$Balance=$Balance-$spanPrice;
return $durationRatedTotal;
}
if ($durationRatedTotal >= $this->duration) {
return sprintf("%f", $durationRatedTotal);
}
$i++;
if ($i>10) {
return sprintf("%f", $durationRatedTotal);
break;
}
}
return false;
}
private function lookupRateValuesAudio($rateName, $DestinationId)
{
if (is_array($this->rateValuesCache[$rateName][$DestinationId][$this->application])) {
return $this->rateValuesCache[$rateName][$DestinationId][$this->application];
}
if ($this->settings['split_rating_table']) {
if ($rateName) {
$table="billing_rates_".$rateName;
} else {
$table="billing_rates_default";
}
$query = sprintf(
"select * from %s where destination = '%s' and application = '%s'",
addslashes($table),
addslashes($DestinationId),
addslashes($this->application)
);
} else {
$table = "billing_rates";
$query = sprintf(
"select * from %s where name = '%s' and destination = '%s' and application = '%s'",
addslashes($table),
addslashes($rateName),
addslashes($DestinationId),
addslashes($this->application)
);
}
// mysql backend
if (!$this->db->query($query)) {
if ($this->db->Errno != 1146) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
// try the main table
$query = sprintf(
"select * from billing_rates where name = '%s' and destination = '%s' and application = '%s'",
addslashes($rateName),
addslashes($DestinationId),
addslashes($this->application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
}
if ($this->db->num_rows()) {
$this->db->next_record();
$values = array(
"connectCost" => $this->db->Record['connectCost'],
"durationRate" => $this->db->Record['durationRate'],
"connectCostIn" => $this->db->Record['connectCostIn'],
"durationRateIn" => $this->db->Record['durationRateIn']
);
// cache values
$this->rateValuesCache[$rateName][$DestinationId][$this->application] = $values;
return $values;
} else {
return false;
}
}
private function lookupRateValuesMessage($rateName, $DestinationId)
{
if (is_array($this->rateValuesCache[$rateName][$DestinationId]['sms'])) {
return $this->rateValuesCache[$rateName][$DestinationId]['sms'];
}
if ($this->settings['split_rating_table']) {
if ($rateName) {
$table = "billing_rates_".$rateName;
} else {
$table = "billing_rates_default";
}
$query = sprintf(
"select * from %s where (destination = '%s' or destination = '') and application = 'sms' order by destination desc limit 1",
addslashes($table),
addslashes($DestinationId)
);
} else {
$table = "billing_rates";
$query = sprintf(
"select * from %s where name = '%s' and (destination = '%s' or destination = '') and application = 'sms' order by destination desc limit 1",
addslashes($table),
addslashes($rateName),
addslashes($DestinationId)
);
}
// mysql backend
if (!$this->db->query($query)) {
if ($this->db->Errno != 1146) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
// try the main table
// lookup rate from MySQL
$query = sprintf(
"select * from billing_rates where name = '%s' and (destination = '%s' or destination = '') and application = 'sms' order by destination desc limit 1",
addslashes($rateName),
addslashes($DestinationId)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
return false;
}
}
if ($this->db->num_rows()) {
$this->db->next_record();
$values = array(
"connectCost" => $this->db->Record['connectCost']
);
// cache values
$this->rateValuesCache[$rateName][$DestinationId]['sms']=$values;
return $values;
} else {
return false;
}
}
}
class RatingTables
{
var $database_backend = 'mysql';
var $csv_export=array(
"destinations" => "destinations.csv",
"billing_customers" => "customers.csv",
"billing_profiles" => "profiles.csv",
"billing_rates" => "rates.csv",
"billing_rates_history" => "ratesHistory.csv",
"billing_discounts" => "discounts.csv",
"billing_enum_tlds" => "enumtld.csv",
"prepaid" => "prepaid.csv",
"quota_usage" => "quotausage.csv"
);
var $csv_import = array(
"destinations" => "destinations.csv",
"billing_customers" => "customers.csv",
"billing_profiles" => "profiles.csv",
"billing_rates" => "rates.csv",
"billing_rates_history" => "ratesHistory.csv",
"billing_discounts" => "discounts.csv"
);
var $previously_imported_files = 0;
var $maxrowsperpage = 15;
var $insertDomainOption = array();
var $delimiter = ",";
var $filesToImport = array();
var $importFilesPatterns = array(
'ratesHistory',
'rates',
'profiles',
'destinations',
'discounts',
'customers'
);
var $mustReload = false;
var $web_elements = array(
'table',
'export',
'web_task',
'subweb_task',
'confirmDelete',
'confirmCopy',
'next',
'id',
'search_text',
'ReloadRatingTables',
'account',
'balance',
'fromRate',
'toRate',
'sessionId'
);
var $requireReload = array('destinations');
var $whereResellerFilter = " (1=1) ";
var $cvs_import_dir = "/var/spool/cdrtool";
var $tables = array(
"destinations" => array(
"name" => "Destinations",
"skip_math" => true,
"keys" => array(
"id"
),
"exceptions" => array(),
"order" => "dest_id ASC",
"domainFilterColumn" => "domain",
"fields" => array(
"gateway" => array(
"size" => 15,
"checkType" => 'ip',
"name" => "Trusted peer"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"domain" => array(
"size" => 15,
"name" => "Domain",
"checkType" => 'domain',
"class" => "span2"
),
"subscriber" => array(
"size" => 15,
"checkType" => 'sip_account',
"name" => "Subscriber",
"class" => "span2"
),
"dest_id" => array(
"size" => 12,
"name" => "Destination",
),
"region" => array(
"size" => 10,
"name" => "Region"
),
"dest_name" => array(
"size" => 20,
"name" => "Description",
"class" => "span2"
),
"increment" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Incr"
),
"min_duration" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Min Dur"
),
"max_duration" => array(
"size" => 5,
"checkType" => 'numeric',
"name" => "Max Dur"
),
"max_price" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Max Price"
)
)
),
"billing_customers" => array(
"name" => "Customers",
"skip_math" => true,
"keys" => array("id"),
"domainFilterColumn" => "domain",
"fields" => array(
"gateway" => array(
"size" => 15,
"checkType" => 'ip',
"name" => "Trusted Peer"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"domain" => array(
"size" => 15,
"checkType" => 'domain',
"name" => "Domain",
"class" => "span2"
),
"subscriber" => array(
"size" => 25,
"checkType" => 'sip_account',
"name" => "Subscriber",
"class" => "span2"
),
"profile_name1" => array(
"size" => 10,
"name" => "Profile WD"
),
"profile_name1_alt" => array(
"size" => 8,
"name" => "Fallback"
),
"profile_name2" => array(
"size" => 10,
"name" => "Profile WE"
),
"profile_name2_alt" => array(
"size" => 8,
"name" => "Fallback"
),
"timezone" => array(
"size" => 16,
"name" => "Timezone",
"class" => "span2"
),
"increment" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Incr"
),
"min_duration" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "Min Dur"
)
)
),
"billing_discounts" => array(
"name" => "Discounts",
"keys" => array("id"),
"domainFilterColumn" => "domain",
"fields" => array(
"gateway" => array(
"size" => 15,
"checkType" => 'ip',
"name" => "Trusted Peer"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"domain" => array(
"size" => 15,
"checkType" => 'domain',
"name" => "Domain",
"class" => "span2"
),
"subscriber" => array(
"size" => 25,
"checkType" => 'sip_account',
"name" => "Subscriber",
"class" => "span2"
),
"application" => array(
"size" => 6,
"name" => "App"
),
"destination" => array(
"size" => 10,
"name" => "Destination"
),
"region" => array(
"size" => 8,
"name" => "Region"
),
"connect" => array(
"size" => 5,
"name" => "Connect"
),
"duration" => array(
"size" => 5,
"name" => "Duration"
)
)
),
"billing_profiles" => array(
"name" => "Profiles",
"skip_math" => true,
"keys" => array("id"),
"exceptions" => array(),
"size" => 6,
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"name" => array(
"size" => 12,
"name" => "Profile",
"class" => "span2"
),
"rate_name1" => array(
"size" => 12,
"name" => "Rate 1"
),
"hour1" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "00-H1"
),
"rate_name2" => array(
"size" => 12,
"name" => "Rate 2"
),
"hour2" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "H1-H2"
),
"rate_name3" => array(
"size" => 12,
"name" => "Rate 3"
),
"hour3" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "H2-H3"
),
"rate_name4" => array(
"size" => 12,
"name" => "Rate 4"
),
"hour4" => array(
"size" => 3,
"checkType" => 'numeric',
"name" => "H3-24"
),
)
),
"billing_rates" => array(
"name" => "Rates",
"keys" => array("id"),
"size" => 10,
"exceptions" => array('maxPrice'),
"order" => "durationRate desc",
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"name" => array(
"size" => 12,
"name" => "Rate",
"class" => "span2"
),
"destination" => array(
"size" => 12,
"name" => "Destination"
),
"application" => array(
"size" => 6,
"name" => "App"
),
"connectCost" => array(
"size" => 8,
"checkType" => 'numeric',
"name"=>"Connect"
),
"durationRate" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Duration"
),
"connectCostIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Conn In"
),
"durationRateIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Duration In"
)
)
),
"billing_rates_history" => array(
"name" => "Rates history",
"keys" => array("id"),
"size" => 10,
"order" => "destination ASC, name ASC",
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"name" => array(
"size" => 10,
"name" => "Rate",
"class" => "span2"
),
"destination" => array(
"size" => 12,
"name" => "Destination"
),
"application" => array(
"size" => 6,
"name" => "App"
),
"connectCost" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Conn"
),
"durationRate" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Price"
),
"connectCostIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Conn In"
),
"durationRateIn" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Price In"
),
"startDate" => array(
"size" => 11,
"name" => "Start Date",
"class" => "span2"
),
"endDate" => array(
"size" => 11,
"name" => "End Date",
"class" => "span2"
)
)
),
"billing_enum_tlds" => array(
"name" => "ENUM discounts",
"skip_math" => true,
"keys" => array("id"),
"exceptions" => array(),
"size" => 6,
"fields" => array(
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"enum_tld" => array(
"size" => 35,
"mustExist" => true,
"checkType" => 'domain',
"name" => "ENUM TLD",
"class" => "span2"
),
"e164_regexp" => array(
"size" => 35,
"mustExist" => true,
"name" => "E164 Regexp",
"class" => "span2"
),
"discount" => array(
"size" => 10,
"mustExist" => true,
"checkType" => 'numeric',
"name" => "Discount"
)
)
),
"prepaid" => array(
"name" => "Prepaid accounts",
"keys" => array("id"),
"size" => 15,
"exceptions" => array('change_date','active_sessions','domain'),
"order" => "change_date DESC",
"fields" => array(
"account" => array(
"size" => 35,
"name" => "Subscriber",
"checkType" => 'sip_account',
"mustExist" => true,
"class" => "span2"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"balance" => array(
"size" => 10,
"name" => "Balance"
),
"change_date" => array(
"size" => 19,
"name" => "Last Change",
"readonly" => 1
),
"session_counter" => array(
"size" => 3,
"name" => "Active Sessions",
"readonly" => 1
),
"max_sessions" => array(
"size" => 3,
"name" => "Max Sessions"
)
)
),
"prepaid_cards" => array(
"name" => "Prepaid cards",
"keys" => array("id"),
"size" => 15,
"exceptions" => array('service'),
"fields" => array(
"batch" => array(
"size" => 40,
"name" => "Batch name",
"readonly" => 1,
"class" => "span3"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller"
),
"date_batch" => array(
"size" => 11,
"name" => "Batch Date",
"class" => "span2"
),
"number" => array(
"size" => 20,
"checkType" => 'numeric',
"mustExist" => true,
"name" => "Card Number",
"class" => "span2"
),
"id" => array(
"size" => 20,
"checkType" => 'numeric',
"mustExist" => true,
"name" => "Card Id",
),
"value" => array(
"size" => 8,
"checkType" => 'numeric',
"mustExist" => true,
"name" => "Card Value"
),
"blocked" => array(
"size" => 1,
"name" => "Lock"
),
"date_active" => array(
"size" => 18,
"name" => "Activation Date",
"class" => "span2"
)
)
),
"prepaid_history" => array(
"name" => "Prepaid history",
"order" => "id DESC",
"skip_math" => true,
"keys" => array("id"),
"size" => 15,
"exceptions" => array('session','destination'),
"fields" => array(
"username" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"domain" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller",
"readonly" => 1
),
"action" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"duration" => array(
"size" => 5
),
"destination" => array(
"size" => 15
),
"session" => array(
"size" => 30,
"readonly" => 1
),
"description" => array(
"size" => 30,
"class" => "span3"
),
"value" => array(
"size" => 10
),
"balance" => array(
"size" => 10
),
"date" => array(
"size" => 18,
"class" => "span2"
)
)
),
"quota_usage" => array(
"name" => "Quota usage",
"keys" => array("id"),
"size" => 15,
"readonly" => 1,
"exceptions" => array(
"change_date",
"traffic",
"duration",
"calls"
),
"domainFilterColumn" => "domain",
"fields" => array(
"datasource" => array(
"size" => 15,
"readonly" => 1
),
"reseller_id" => array(
"size" => 8,
"checkType" => 'numeric',
"name" => "Reseller",
"readonly" => true
),
"account" => array(
"size" => 30,
"readonly" => 1,
"name" => "Subscriber",
"class" => "span2"
),
"domain" => array(
"size" => 15,
"readonly" => 1,
"class" => "span2"
),
"blocked" => array(
"size" => 2,
"readonly" => 1
),
"notified" => array(
"size" => 20,
"readonly" => 1
),
"quota" => array(
"size" => 5,
"readonly" => 1
),
"cost" => array(
"size" => 10,
"readonly" => 1,
"name" => "This Month"
),
"cost_today" => array(
"size" => 10,
"readonly" => 1,
"name" => "Today"
),
"duration" => array(
"size" => 10,
"readonly" => 1
),
"calls" => array(
"size" => 10,
"readonly" => 1
),
"traffic" => array(
"size" => 20,
"readonly" => 1
)
)
)
);
public function RatingTables($readonly = false)
{
global $CDRTool;
global $RatingEngine;
$this->settings = $RatingEngine;
$this->CDRTool = $CDRTool;
$this->table = $_REQUEST['table'];
if (!$this->table || !in_array($this->table, array_keys($this->tables))) {
$this->table="destinations";
}
$this->readonly=$readonly;
if ($this->settings['csv_delimiter']) {
$this->delimiter=$this->settings['csv_delimiter'];
}
if (!strlen($this->CDRTool['filter']['customer'])) {
$this->whereResellerFilter = sprintf("reseller_id = %d", '99999999');
} else {
if ($this->CDRTool['filter']['customer'] && $this->tables[$this->table]['fields']['reseller_id']) {
$this->whereResellerFilter = sprintf("reseller_id = %d", addslashes($this->CDRTool['filter']['customer']));
$this->tables[$this->table]['fields']['reseller_id']['readonly']=true;
}
}
if ($this->settings['split_rating_table']) {
$this->tables['billing_rates']['fields']['name']['readonly'] = 1;
}
if (strlen($this->settings['socketIP'])) {
if ($this->settings['socketIP'] == '0.0.0.0' || $this->settings['socketIP'] == '0') {
$this->settings['socketIPforClients'] = '127.0.0.1';
} else {
$this->settings['socketIPforClients'] = $this->settings['socketIP'];
}
}
if ($this->settings['database_backend']) {
$this->database_backend = $this->settings['database_backend'];
}
$this->db = new DB_cdrtool;
$this->db1 = new DB_cdrtool;
$this->db->Halt_On_Error="no";
$this->db1->Halt_On_Error="no";
}
public function ImportCSVFiles($dir = false)
{
$results = 0;
if (!$dir) $dir = "/var/spool/cdrtool";
$this->scanFilesForImport($dir);
if ($this->previously_imported_files) {
printf("Skipping %d previously imported files\n", $this->previously_imported_files);
}
$results=0;
foreach (array_keys($this->filesToImport) as $file) {
$importFunction = "Import".ucfirst($this->filesToImport[$file]['type']);
printf("Reading file %s\n", $this->filesToImport[$file]['path']);
$results = $this->$importFunction($this->filesToImport[$file]['path'],$this->filesToImport[$file]['reseller']);
$this->logImport(
$dir,
$this->filesToImport[$file]['path'],
$this->filesToImport[$file]['watermark'],
$results,
$this->filesToImport[$file]['reseller']
);
}
return $results;
}
private function ImportRates($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing rates from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer = trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$name = trim($p[2]);
$destination = trim($p[3]);
$application = trim($p[4]);
$connectCost = trim($p[5]);
$durationRate = trim($p[6]);
$connectCostIn = trim($p[7]);
$durationRateIn = trim($p[8]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($destination) && !strstr($destination, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if (strlen($connectCost) && !is_numeric($connectCost)) {
$skipped++;
continue;
}
if (strlen($durationRate) && !is_numeric($durationRate)) {
$skipped++;
continue;
}
if (!$application) $application='audio';
if ($ops=="1") {
$query = sprintf(
"insert into billing_rates
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
if (!$this->createRatingTable($name)) {
$query = sprintf(
"insert into %s
(
id,
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
LAST_INSERT_ID(),
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($_table),
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
}
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from billing_rates
where
reseller_id = '%s'
and name = '%s'
and destination = '%s'
and application = '%s'",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application)
);
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
$query = sprintf(
"delete from %s
where reseller_id = '%s'
and name = '%s'
and destination = '%s'
and application = '%s'",
addslashes($_table),
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
$deleted++;
}
// mysql backend
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_rates set
connectCost = '%s',
durationRate = '%s',
connectCostIn = '%s',
durationRateIn = '%s'
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and application = '%s'
",
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
$query = sprintf(
"update %s set
connectCost = '%s',
durationRate = '%s',
connectCostIn = '%s',
durationRateIn = '%s'
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and application = '%s'
",
addslashes($_table),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($application)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
$updated++;
}
} else {
$query = sprintf(
"insert into billing_rates
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
if ($this->settings['split_rating_table']) {
if ($name) {
$_table = 'billing_rates_'.$name;
} else {
$_table = 'billing_rates_default';
}
if (!$this->createRatingTable($name)) {
$query = sprintf(
"insert into %s
(
id,
reseller_id,
name,
destination,
application
connectCost,
durationRate,
connectCostIn,
durationRateIn
) values (
LAST_INSERT_ID(),
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($_table),
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
}
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted+$updated+$deleted;
return $results;
}
private function ImportRatesHistory($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing rates history from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$name = trim($p[2]);
$destination = trim($p[3]);
$application = trim($p[4]);
$connectCost = trim($p[5]);
$durationRate = trim($p[6]);
$connectCostIn = trim($p[7]);
$durationRateIn = trim($p[8]);
$startDate = trim($p[9]);
$endDate = trim($p[10]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($destination) && !strstr($destination, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if (strlen($connectCost) && !is_numeric($connectCost)) {
$skipped++;
continue;
}
if (strlen($durationRate) && !is_numeric($durationRate)) {
$skipped++;
continue;
}
if (preg_match("/^\d{4}\-{\d{2}\-\d{2}$/", $startDate)) {
$skipped++;
continue;
}
if (preg_match("/^\d{4}\-{\d{2}\-\d{2}$/", $endDate)) {
$skipped++;
continue;
}
if ($ops=="1") {
$query = sprintf(
"insert into billing_rates_history
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn,
startDate,
endDate
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops=="3") {
$query = sprintf(
"delete from billing_rates_history
where reseller_id = '%s'
and name = '%s'
and destination = '%s'
and startDate = '%s'
and endDate = '%s'",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops=="2") {
$query = sprintf(
"select * from billing_rates_history
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and startDate = '%s'
and endDate = '%s'
",
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_rates_history set
application = '%s',
connectCost = '%s',
durationRate = '%s',
connectCostIn = '%s',
connectCostIn = '%s'
where name = '%s'
and destination = '%s'
and reseller_id = '%s'
and startDate = '%s'
and endDate = '%s'
",
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($name),
addslashes($destination),
addslashes($reseller_id),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_rates_history
(
reseller_id,
name,
destination,
application,
connectCost,
durationRate,
connectCostIn,
durationRateIn,
startDate,
endDate
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($name),
addslashes($destination),
addslashes($application),
addslashes($connectCost),
addslashes($durationRate),
addslashes($connectCostIn),
addslashes($durationRateIn),
addslashes($startDate),
addslashes($endDate)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$j++;
if ($j=="10000") {
flush();
$j=0;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
private function ImportCustomers($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload = true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing customers from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$gateway = trim($p[2]);
$domain = trim($p[3]);
$subscriber = trim($p[4]);
$profile_name1 = trim($p[5]);
$profile_name1_alt = trim($p[6]);
$profile_name2 = trim($p[7]);
$profile_name2_alt = trim($p[8]);
$timezone = trim($p[9]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (strlen($reseller_id) && !is_integer($reseller_id)) {
$skipped++;
continue;
}
if ($ops=="1") {
$query = sprintf(
"insert into billing_customers
(
reseller_id,
gateway,
domain,
subscriber,
profile_name1,
profile_name2,
timezone,
profile_name1_alt,
profile_name2_alt
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($profile_name1),
addslashes($profile_name2),
addslashes($timezone),
addslashes($profile_name1_alt),
addslashes($profile_name2_alt)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from billing_customers
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from billing_customers
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_customers set
profile_name1 = '%s',
profile_name2 = '%s',
profile_name1_alt = '%s',
profile_name2_alt = '%s',
timezone = '%s'
where gateway = '%s'
and domain = '%s'
and reseller_id = '%s'
and subscriber = '%s'\n",
addslashes($profile_name1),
addslashes($profile_name2),
addslashes($profile_name1_alt),
addslashes($profile_name2_alt),
addslashes($timezone),
addslashes($gateway),
addslashes($domain),
addslashes($reseller_id),
addslashes($subscriber)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_customers
(
reseller_id,
gateway,
domain,
subscriber,
profile_name1,
profile_name2,
timezone,
profile_name1_alt,
profile_name2_alt
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($profile_name1),
addslashes($profile_name2),
addslashes($timezone),
addslashes($profile_name1_alt),
addslashes($profile_name2_alt)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$inserted++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results=$inserted+$updated+$deleted;
return $results;
}
private function ImportDestinations($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing destinations from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$gateway = trim($p[2]);
$domain = trim($p[3]);
$subscriber = trim($p[4]);
$dest_id = trim($p[5]);
$region = trim($p[6]);
$dest_name = trim($p[7]);
$increment = intval($p[8]);
$min_duration = intval($p[9]);
$max_duration = intval($p[10]);
$max_price = trim($p[11]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($dest_id) && !strstr($dest_id, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if ($ops=="1") {
$query = sprintf(
"insert into destinations
(
reseller_id,
gateway,
domain,
subscriber,
dest_id,
region,
dest_name,
increment,
min_duration,
max_duration,
max_price
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id),
addslashes($region),
addslashes($dest_name),
addslashes($increment),
addslashes($min_duration),
addslashes($max_duration),
addslashes($max_price)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from destinations
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and dest_id = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from destinations
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and dest_id = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update destinations set
region = '%s',
dest_name = '%s',
increment = '%s',
min_duration = '%s',
max_duration = '%s',
max_price = '%s'
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and dest_id = '%s'
",
addslashes($region),
addslashes($dest_name),
addslashes($increment),
addslashes($min_duration),
addslashes($max_duration),
addslashes($max_price),
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into destinations
(
reseller_id,
gateway,
domain,
subscriber,
dest_id,
region,
dest_name,
increment,
min_duration,
max_duration,
max_price
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($dest_id),
addslashes($region),
addslashes($dest_name),
addslashes($increment),
addslashes($min_duration),
addslashes($max_duration),
addslashes($max_price)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
private function ImportDiscounts($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
printf("Importing discounts from %s for reseller %s:\n", $file, $reseller);
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$gateway = trim($p[2]);
$domain = trim($p[3]);
$subscriber = trim($p[4]);
$application = trim($p[5]);
$destination = trim($p[6]);
$region = trim($p[7]);
$connect = intval($p[8]);
$duration = intval($p[9]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!is_numeric($destination) && !strstr($destination, '@')) {
// skip invalid destinations
$skipped++;
continue;
}
if ($ops == "1") {
$query = sprintf(
"insert into billing_discounts
(
reseller_id,
gateway,
domain,
subscriber,
application,
destination,
region,
connect,
duration
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region),
addslashes($connect),
addslashes($duration)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query=sprintf(
"delete from billing_discounts
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and application = '%s'
and destination = '%s'
and region = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from billing_discounts
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and application = '%s'
and destination = '%s'
and region = '%s'
",
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_discounts set
connect = '%s',
duration = '%s',
where gateway = '%s'
and reseller_id = '%s'
and domain = '%s'
and subscriber = '%s'
and application = '%s'
and destination = '%s'
and region = '%s'
",
addslashes($connect),
addslashes($duration),
addslashes($gateway),
addslashes($reseller_id),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_discounts
(
reseller_id,
gateway,
domain,
subscriber,
application,
destination,
region,
connect,
duration
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($gateway),
addslashes($domain),
addslashes($subscriber),
addslashes($application),
addslashes($destination),
addslashes($region),
addslashes($connect),
addslashes($duration)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
} else {
$skipped++;
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($skipped) print "Skipped $skipped records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
private function ImportProfiles($file, $reseller = 0)
{
if (!$file || !is_readable($file) || !$fp = fopen($file, "r")) return false;
$this->mustReload=true;
$i=0;
$inserted = 0;
$updated = 0;
$deleted = 0;
print "Importing Profiles:\n";
while ($buffer = fgets($fp, 1024)) {
$buffer=trim($buffer);
$p = explode($this->delimiter, $buffer);
$ops = trim($p[0]);
$profile = trim($p[2]);
$rate1 = trim($p[3]);
$hour1 = trim($p[4]);
$rate2 = trim($p[5]);
$hour2 = trim($p[6]);
$rate3 = trim($p[7]);
$hour3 = trim($p[8]);
$rate4 = trim($p[9]);
$hour4 = trim($p[10]);
if ($reseller) {
$reseller_id = intval($reseller);
} else {
$reseller_id = intval($p[1]);
}
if (!$hour1) $hour1=0;
if (!$hour2) $hour2=0;
if (!$hour3) $hour3=0;
if (!$hour4) $hour4=0;
if ($ops=="1") {
$query = sprintf(
"insert into billing_profiles
(
reseller_id,
name,
rate_name1,
hour1,
rate_name2,
hour2,
rate_name3,
hour3,
rate_name4,
hour4
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($profile),
addslashes($rate1),
addslashes($hour1),
addslashes($rate2),
addslashes($hour2),
addslashes($rate3),
addslashes($hour3),
addslashes($rate4),
addslashes($hour4)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
} elseif ($ops == "3") {
$query = sprintf(
"delete from billing_profiles
where name = '%s'
and reseller_id= '%s'
",
addslashes($profile),
addslashes($reseller_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$deleted++;
}
} elseif ($ops == "2") {
$query = sprintf(
"select * from billing_profiles
where name = '%s'
and reseller_id= '%s'
",
addslashes($profile),
addslashes($reseller_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->num_rows()) {
$query = sprintf(
"update billing_profiles set
rate_name1 = '%s',
rate_name2 = '%s',
rate_name3 = '%s',
rate_name4 = '%s',
hour1 = '%s',
hour2 = '%s',
hour3 = '%s',
hour4 = '%s'
where name = '%s'
and reseller_id= '%s'
\n",
addslashes($rate1),
addslashes($rate2),
addslashes($rate3),
addslashes($rate4),
addslashes($hour1),
addslashes($hour2),
addslashes($hour3),
addslashes($hour4),
addslashes($profile),
addslashes($reseller_id)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows()) {
$updated++;
}
} else {
$query = sprintf(
"insert into billing_profiles
(
reseller_id,
name,
rate_name1,
hour1,
rate_name2,
hour2,
rate_name3,
hour3,
rate_name4,
hour4
) values (
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s',
'%s'
)",
addslashes($reseller_id),
addslashes($profile),
addslashes($rate1),
addslashes($hour1),
addslashes($rate2),
addslashes($hour2),
addslashes($rate3),
addslashes($hour3),
addslashes($rate4),
addslashes($hour4)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if ($this->db->affected_rows() >0) {
$inserted++;
} else {
$failed++;
}
}
}
$this->showImportProgress($file);
$i++;
}
if ($i) print "Read $i records\n";
if ($inserted) print "Inserted $inserted records\n";
if ($updated) print "Updated $updated records\n";
if ($deleted) print "Delete $deleted records\n";
$results = $inserted + $updated + $deleted;
return $results;
}
public function LoadRatingTables()
{
$log = sprintf(
"Memory usage: %0.2fMB, memory limit: %sB",
memory_get_usage() / 1024 / 1024,
ini_get('memory_limit')
);
syslog(LOG_NOTICE, $log);
$loaded['profiles'] = $this->LoadProfilesTable();
$loaded['ratesHistory'] = $this->LoadRatesHistoryTable();
$loaded['holidays'] = $this->LoadHolidaysTable();
$loaded['enumTlds'] = $this->LoadENUMtldsTable();
foreach (array_keys($loaded) as $_load) {
syslog(LOG_NOTICE, "Loaded $loaded[$_load] $_load into memory");
}
$log = sprintf(
"Memory usage: %0.2fMB, memory limit: %sB",
memory_get_usage() / 1024 / 1024,
ini_get('memory_limit')
);
syslog(LOG_NOTICE, $log);
return $loaded;
}
private function LoadENUMtldsTable()
{
$query = "select * from billing_enum_tlds";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
$rows=$this->db->num_rows();
while ($this->db->next_record()) {
if ($this->db->Record['enum_tld']) {
$i++;
$_app=$this->db->Record['application'];
if (!$_app) $_app='audio';
$_ENUMtlds[$this->db->Record['enum_tld']] = array(
"discount" => $this->db->Record['discount'],
"e164_regexp" => $this->db->Record['e164_regexp']
);
}
}
$this->ENUMtlds = $_ENUMtlds;
$this->ENUMtldsCount = $i;
return $i;
}
private function LoadRatesHistoryTable()
{
$query = "select *,
UNIX_TIMESTAMP(startDate) as startDateTimestamp,
UNIX_TIMESTAMP(endDate) as endDateTimestamp
from billing_rates_history
order by name ASC,destination ASC,startDate DESC";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
$rows=$this->db->num_rows();
while ($this->db->next_record()) {
if ($this->db->Record['name'] && $this->db->Record['destination']) {
$i++;
$_app = $this->db->Record['application'];
if (!$_app) $_app='audio';
$_rates[$this->db->Record['name']][$this->db->Record['destination']][$_app][$this->db->Record['id']]=
array(
"connectCost" => $this->db->Record['connectCost'],
"durationRate" => $this->db->Record['durationRate'],
"connectCostIn" => $this->db->Record['connectCostIn'],
"durationRateIn" => $this->db->Record['durationRateIn'],
"increment" => $this->db->Record['increment'],
"min_duration" => $this->db->Record['min_duration'],
"startDate" => $this->db->Record['startDateTimestamp'],
"endDate" => $this->db->Record['endDateTimestamp']
);
}
}
$this->ratesHistory = $_rates;
$this->ratesHistoryCount = $i;
return $i;
}
private function LoadProfilesTable()
{
$query = "select * from billing_profiles order by name";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
while ($this->db->next_record()) {
$i++;
if ($this->db->Record['name'] && $this->db->Record['hour1'] > 0) {
$_profiles[$this->db->Record['name']]=
array(
"rate_name1" => $this->db->Record['rate_name1'],
"hour1" => $this->db->Record['hour1'],
"rate_name2" => $this->db->Record['rate_name2'],
"hour2" => $this->db->Record['hour2'],
"rate_name3" => $this->db->Record['rate_name3'],
"hour3" => $this->db->Record['hour3'],
"rate_name4" => $this->db->Record['rate_name4'],
"hour4" => $this->db->Record['hour4'],
);
}
}
$this->profiles=$_profiles;
return $i;
}
private function LoadHolidaysTable()
{
$query="select * from billing_holidays order by day";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$i=0;
while ($this->db->next_record()) {
if ($this->db->Record['day']) {
$i++;
$_holidays[$this->db->Record['day']]++;
}
}
$this->holidays=$_holidays;
return $i;
}
public function checkRatingEngineConnection()
{
if ($this->settings['socketIPforClients'] && $this->settings['socketPort']
&& $fp = fsockopen($this->settings['socketIPforClients'], $this->settings['socketPort'], $errno, $errstr, 2)
) {
fclose($fp);
return true;
}
return false;
}
function showCustomers($filter)
{
return true;
foreach (array_keys($this->customers) as $key) {
if (strlen($filter)) {
if (preg_match("/$filter/", $key)) {
$customers = $customers.$key."\n";
}
} else {
$customers = $customers.$key."\n";
}
}
return $customers;
}
public function showProfiles()
{
foreach (array_keys($this->profiles) as $key) {
$profiles=$profiles.$key."\n";
}
return $profiles;
}
public function showENUMtlds()
{
foreach (array_keys($this->ENUMtlds) as $key) {
$ENUMtlds=$ENUMtlds.$key."\n";
}
return $ENUMtlds;
}
private function scanFilesForImport($dir)
{
$import_dirs[$this->cvs_import_dir] = array(
'path' => $this->cvs_import_dir,
'reseller' => 0
);
if ($handle = opendir($this->cvs_import_dir)) {
while (false !== ($filename = readdir($handle))) {
$reseller=0;
if ($filename == "." || $filename == "..") continue;
$fullPath = $this->cvs_import_dir."/".$filename;
if (is_dir($fullPath) && is_numeric($filename)) {
$reseller = $filename;
$import_dirs[$fullPath]=array(
'path' => $fullPath,
'reseller'=> $reseller
);
}
}
}
foreach (array_keys($import_dirs) as $_dir) {
if ($handle = opendir($_dir)) {
while (false !== ($filename = readdir($handle))) {
if ($filename != "." && $filename != "..") {
foreach ($this->importFilesPatterns as $_pattern) {
if (strstr($filename, $_pattern) && preg_match("/\.csv$/", $filename)) {
$fullPath = $_dir."/".$filename;
if ($content = file_get_contents($fullPath)) {
$watermark = $filename."-".md5($content);
if ($this->hasFileBeenImported($filename, $watermark)) {
$this->previously_imported_files++;
break;
}
$this->filesToImport[$filename] = array(
'name' => $filename,
'watermark' => $watermark,
'type' => $_pattern,
'path' => $fullPath,
'reseller' => $import_dirs[$_dir]['reseller']
);
}
break;
}
}
}
}
}
}
}
private function hasFileBeenImported($filename, $watermark)
{
$query = sprintf(
"select * from log where url = '%s'\n",
addslashes($watermark)
);
if ($this->db->query($query)) {
if ($this->db->num_rows()) {
$this->db->next_record();
/*
$log=sprintf ("File %s has already been imported at %s.\n",$filename,$this->db->f('date'));
syslog(LOG_NOTICE, $log);
print $log;
*/
return true;
} else {
return false;
}
} else {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
private function logImport($dir, $filename, $watermark, $results = 0, $reseller = 0)
{
$query = sprintf(
"insert into log (
date,
login,
ip,
url,
results,
description,
datasource,
reseller_id
) values (
NOW(),
'ImportScript',
'localhost',
'%s',
'%s',
'Imported %s',
'%s',
%d
)",
addslashes($watermark),
addslashes($results),
addslashes($filename),
addslashes($dir),
addslashes($reseller)
);
$log = sprintf(
"Imported file %s, %d records have been affected\n",
$filename,
$results
);
syslog(LOG_NOTICE, $log);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
}
function showImportProgress($filename = 'unspecified', $increment = 5000)
{
$this->importIndex++;
if ($this->importIndex == $increment) {
printf("Loaded %d records from %s\n", $this->importIndex, $filename);
flush();
$this->importIndex=0;
}
}
function createRatingTable($name)
{
if ($name) {
$table='billing_rates_'.$name;
} else {
$table='billing_rates_default';
}
$query = sprintf(
"create table %s select * from billing_rates where name = '%s'\n",
addslashes($table),
addslashes($name)
);
if ($this->db->query($query)) {
$query = sprintf(
"alter table %s add index rate_idx (name)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
$query = sprintf(
"alter table %s add index destination_idx (destination)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
printf("Created table %s\n", $table);
return true;
} else {
return false;
}
}
public function splitRatingTable()
{
$query = "select count(*) as c from billing_rates";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$this->db->next_record();
$rows=$this->db->f('c');
$query="select distinct(name) from billing_rates order by name ASC";
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
while ($this->db->next_record()) {
$rate_names[]=$this->db->f('name');
}
foreach ($rate_names as $name) {
if (!$name) $name='default';
$table="billing_rates_".$name;
$query = sprintf("drop table if exists %s", addslashes($table));
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$query = sprintf(
"create table %s select * from billing_rates where name = '%s'\n",
addslashes($table),
addslashes($name)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
} else {
$query = sprintf(
"alter table %s add index rate_idx (name)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$query = sprintf(
"alter table %s add index destination_idx (destination)",
addslashes($table)
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
return false;
}
$query = sprintf("select count(*) as c from %s", addslashes($table));
$this->db->query($query);
$this->db->next_record();
$records=$this->db->f('c');
$created_records=$created_records+$records;
$progress=100*$created_records/$rows;
printf(
"Created table %s with %s records (%.1f %s)\n",
$table,
$records,
$progress,
'%'
);
}
}
return true;
}
public function updateTable()
{
global $auth;
$loginname=$auth->auth["uname"];
foreach ($this->web_elements as $_el) {
${$_el}= $_REQUEST[$_el];
}
if (!$table) return false;
if ($this->readonly) {
return true;
}
// Init table structure
if (!is_array($this->tables[$table]['exceptions'])) $this->tables[$table]['exceptions']=array();
if (!is_array($this->tables[$table]['keys'])) $this->tables[$table]['keys']=array();
if (!is_array($this->tables[$table]['fields'])) $this->tables[$table]['fields']=array();
$metadata = $this->db->metadata($table = "$table");
$cc = count($metadata);
// end init table structure
if ($web_task =="update") {
$affected_rows=0;
if ($subweb_task == "Update") {
if ($this->checkValues($table, $_REQUEST)) {
$update_set='';
$k=0;
while ($k < $cc) {
$k++;
$Fname=$metadata[$k]['name'];
if (!$Fname) continue;
$value=$_REQUEST[$Fname];
if ($this->tables[$table]['fields'][$Fname]['readonly']) {
continue;
}
if (in_array($Fname, $this->tables[$table]['exceptions'])) {
continue;
}
if (in_array($Fname, $this->tables[$table]['keys'])) {
continue;
}
if ($kkk > 0) {
$comma = ",";
} else {
$comma = "";
}
if (!$this->tables[$table]['skip_math'] && preg_match("/^([\+\-\*\/])(.*)$/", $value, $sign)) {
$update_set .= $comma.addslashes($Fname)."= ROUND(".addslashes($Fname). " ".$sign[1]. "'".$sign[2]."')";
} else {
$update_set .= $comma.addslashes($Fname)."='".addslashes($value)."'";
}
$kkk++;
}
$k=0;
while ($k < $cc) {
if ($metadata[$k]['name'] == 'change_date') {
$update_set .= sprintf("%s %s = NOW() ", $comma, addslashes($metadata[$k]['name']));
break;
}
$k++;
}
$log_entity=" id = $id ";
$where = sprintf(" id = '%s' and %s", addslashes($id), $this->whereResellerFilter);
if ($table == "billing_rates") {
if ($this->settings['split_rating_table']) {
$rate_table_affected = array();
$query_r = "select distinct (name) from billing_rates where". $where;
if ($this->db->query($query_r)) {
while ($this->db->next_record()) {
$rate_tables_affected[]='billing_rates_'.$this->db->f('name');
}
} else {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
} elseif ($table=="prepaid") {
register_shutdown_function("unLockTables", $this->db);
if ($this->db->query("lock table prepaid write")) {
$query_q = sprintf(
"select * from prepaid where account = '%s'",
addslashes($account)
);
if ($this->db->query($query_q) && $this->db->num_rows()) {
$this->db->next_record();
$old_balance=$this->db->f('balance');
}
$this->db->query("unlock tables");
}
}
$query = sprintf(
"update %s set %s where %s ",
addslashes($table),
$update_set,
$where
);
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
if ($table=="prepaid") {
list($username, $domain) = explode("@", $account);
$value=$balance-$old_balance;
if (floatval($balance) != floatval($old_balance)) {
$query = sprintf(
"insert into prepaid_history
(username,domain,action,description,value,balance,date,reseller_id)
values
('%s','%s','Set balance','Manual update','%s','%s',NOW(),%d)",
addslashes($username),
addslashes($domain),
addslashes($value),
addslashes($balance),
$this->CDRTool['filter']['reseller']
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
} elseif ($table=='billing_rates') {
if ($this->settings['split_rating_table']) {
foreach ($rate_tables_affected as $extra_rate_table) {
$query_u = sprintf(
"update %s set %s where %s ",
addslashes($extra_rate_table),
$update_set,
$where
);
if (!$this->db->query($query_u)) {
$log = sprintf(
"Database error for query %s: %s (%s)",
$query_u,
$this->db->Error,
$this->db->Errno
);
print $log;
syslog(LOG_NOTICE, $log);
}
}
}
}
if (in_array($table, $this->requireReload)) {
if (!$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'")) {
printf(
"Database error: %s (%s)",
$this->db->Error,
$this->db->Errno
);
}
}
}
} else {
printf(
"Database error for query '%s': %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
}
} else {
print "
Correct the values and try again.";
}
} elseif ($subweb_task == "Update selection") {
$k=0;
$kkk=0;
$update_set='';
while ($k < $cc) {
$k++;
$Fname=$metadata[$k]['name'];
$value=$_REQUEST[$Fname];
if (!strlen($value)) continue;
if ($this->tables[$table]['fields'][$Fname]['readonly']) {
continue;
}
if (in_array($Fname, $this->tables[$table]['exceptions'])) {
continue;
}
if (in_array($Fname, $this->tables[$table]['keys'])) {
continue;
}
if ($kkk > 0) {
$comma = ",";
} else {
$comma="";
}
if ($value == "NULL") {
$value="";
}
if (preg_match("/^([\+\-\*\/])(.*)$/", $value, $sign)) {
$update_set .= $comma.$Fname." = ROUND(".$Fname. " ".$sign[1]. "'".$sign[2]."')";
} else {
$update_set .= $comma.$Fname." = '".$value."'";
}
$kkk++;
}
$where = $this->whereResellerFilter;
if ($kkk) {
// reconstruct where clause to apply all changes to selection
// build where clause
// Search build for each field
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like=$likes[1];
$likewhat=$likes[2];
$quotes="";
} else {
$like="like";
$likewhat=$value;
$quotes="'";
}
if (strlen($value)) {
$where .= " and $Fname $like $quotes".$likewhat."$quotes";
$t++;
}
}
$j++;
}
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
$rate_table_affected = array();
$query_r = "select distinct (name) from billing_rates where". $where;
if ($this->db->query($query_r)) {
while ($this->db->next_record()) {
$rate_tables_affected[] = 'billing_rates_'.$this->db->f('name');
}
} else {
printf(
"Database error: %s (%s)",
$this->db->Error,
$this->db->Errno
);
}
}
}
$query = sprintf(
"update %s set %s where %s ",
addslashes($table),
$update_set,
$where
);
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
foreach ($rate_tables_affected as $extra_rate_table) {
$query_u = sprintf(
"update %s set %s where %s ",
addslashes($extra_rate_table),
$update_set,
$where
);
if (!$this->db->query($query_u)) {
printf(
"Database error for %s: %s (%s)",
$query_u,
$this->db->Error,
$this->db->Errno
);
}
}
}
}
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
} else {
printf("Database error: %s", $this->db->Error);
}
}
} elseif ($subweb_task == "Delete selection") {
if ($confirmDelete) {
// reconstruct where clause to apply all changes to selection
// build where clause
// Search build for each field
$where = $this->whereResellerFilter;
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like = $likes[1];
$likewhat = $likes[2];
$quotes = "";
} else {
$like = "like";
$likewhat = $value;
$quotes = "'";
}
if (strlen($value)) {
$where .= " and $Fname $like $quotes".$likewhat."$quotes";
$t++;
}
}
$j++;
}
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
$rate_table_affected=array();
$query_r = "select distinct (name) from billing_rates where". $where;
if ($this->db->query($query_r)) {
while ($this->db->next_record()) {
$rate_tables_affected[] = 'billing_rates_'.$this->db->f('name');
}
} else {
printf(
"Database error: %s (%s)",
$this->db->Error,
$this->db->Errno
);
}
}
}
$query = sprintf(
"delete from %s where %s",
addslashes($table),
$where
);
if ($this->db->query($query)) {
$affected_rows = $this->db->affected_rows();
if ($affected_rows) {
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
foreach ($rate_tables_affected as $extra_rate_table) {
$query_u = sprintf(
"delete from %s where %s ",
addslashes($extra_rate_table),
$where
);
if (!$this->db->query($query_u)) {
printf(
"Database error for %s: %s (%s)",
$query_u,
$this->db->Error,
$this->db->Errno
);
}
}
}
}
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
} else {
printf(
"Database error: %s",
$this->db->Error
);
}
unset($confirmDelete);
} else {
print "
";
print "Please confirm the deletion by pressing the Delete button again. ";
print "";
print "";
}
} elseif ($subweb_task == "Copy rate" && strlen($fromRate) && strlen($toRate)) {
$toRate=preg_replace("/%/", "", $toRate);
if ($confirmCopy) {
if ($toRate == 'history') {
$values = sprintf(
"(reseller_id,name,destination,application,connectCost,durationRate,connectCostIn,durationRateIn,startDate,endDate)
select
billing_rates.reseller_id,
'%s',
billing_rates.destination,
billing_rates.application,
billing_rates.connectCost,
billing_rates.durationRate,
billing_rates.connectCostIn,
billing_rates.durationRateIn,
NOW(),
NOW()
from billing_rates ",
addslashes($fromRate)
);
} else {
$values = sprintf(
"(reseller_id,name,destination,application,connectCost,durationRate,connectCostIn,durationRateIn)
select
billing_rates.reseller_id,
'%s',
billing_rates.destination,
billing_rates.application,
billing_rates.connectCost,
billing_rates.durationRate,
billing_rates.connectCostIn,
billing_rates.durationRateIn
from billing_rates ",
addslashes($toRate)
);
}
$where = $this->whereResellerFilter;
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like=$likes[1];
$likewhat=$likes[2];
$quotes="";
} else {
$like="like";
$likewhat=$value;
$quotes="'";
}
if (strlen($value)) {
$where .= sprintf(
" and %s %s %s%s%s ",
addslashes($Fname),
$like,
$quotes,
addslashes($likewhat),
$quotes
);
$t++;
}
}
$j++;
}
if ($toRate == 'history') {
$query="insert into billing_rates_history $values where $where";
} else {
$query="insert into billing_rates $values where $where";
}
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
print "$affected_rows rates copied. ";
if ($table == 'billing_rates') {
if ($this->settings['split_rating_table']) {
$query = sprintf(
"create table billing_rates_%s select * from billing_rates where %s ",
addslashes($toRate),
$where
);
if (!$this->db->query($query)) {
printf(
"Database error for %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
}
}
}
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
if ($toRate == 'history') {
// Switch to history
$table = 'billing_rates_history';
// Init table structure
$this->tables[$table]['exceptions']= $this->tables[$table]['exceptions'];
$this->tables[$table]['keys'] = $this->tables[$table]['keys'];
$this->tables[$table]['fields'] = $this->tables[$table]['fields'];
$metadata = $this->db->metadata($table = "$table");
$cc = count($metadata);
// end init table structure
}
unset($confirmCopy);
} else {
printf("Database error: %s", $this->db->Error);
}
$log_entity="rate=$toRate";
} else {
print "
";
print "Please confirm the copy of rate $fromRate to $toRate. ";
print "";
}
} elseif ($subweb_task == "Insert") {
//print "
Insert
";
if ($this->checkValues($table, $_REQUEST)) {
$query=sprintf("insert into %s ( ", addslashes($table));
$k=1;
$kkk=0;
while ($k < $cc) {
$Fname=$metadata[$k]['name'];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
if ($kkk > 0) {
$comma = ",";
} else {
$comma="";
}
$query .= $comma.addslashes($Fname);
$kkk++;
}
$k++;
}
$query .= ") values ( ";
$k=1;
$kkk=0;
while ($k < $cc) {
$Fname=$metadata[$k]['name'];
$value=$_REQUEST[$Fname];
if (!in_array($Fname, $this->tables[$table]['exceptions'])) {
if ($kkk > 0) {
$comma = ",";
} else {
$comma="";
}
if ($Fname == 'reseller_id' && $this->CDRTool['filter']['reseller']) {
$query .= $comma."'".addslashes($this->CDRTool['filter']['reseller'])."'";
} else {
$query .= $comma."'".addslashes($value)."'";
}
$kkk++;
}
$k++;
}
$query .= ") ";
$k=1;
while ($k < $cc) {
$Fname=$metadata[$k]['name'];
$value=$_REQUEST[$Fname];
if (in_array($Fname, $this->tables[$table]['keys'])) {
if ($value == "") {
$Fname_print_insert = substr($Fname, 4);
print "$Fname_print_insert = ???? ";
$empty_insert = 1;
}
}
$k++;
}
if (!$empty_insert) {
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows) {
$this->db->query("select LAST_INSERT_ID() as lid");
$this->db->next_record();
$log_entity = sprintf("id=%s", $this->db->f('lid'));
if (in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
}
} else {
printf(
"Database error for query %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
}
} else {
print "
Error: The insert statement contains an empty key!
";
}
} else {
print "
Correct the values and try again.";
}
} elseif ($subweb_task == "Delete") {
if ($confirmDelete) {
$query = sprintf(
"delete from %s where id = '%s' and %s ",
addslashes($table),
addslashes($id),
addslashes($this->whereResellerFilter)
);
if ($this->db->query($query)) {
$affected_rows=$this->db->affected_rows();
if ($affected_rows && in_array($table, $this->requireReload)) {
$this->db->query("update settings set var_value= '1' where var_name = 'reloadRating'");
}
$log_entity = sprintf("id=%s", $id);
} else {
printf("Database error: %s", $this->db->Error);
}
unset($confirmDelete);
} else {
$idForDeletion=$id;
print "
";
print "Please confirm the deletion by pressing the Delete button again. ";
print "";
print "";
}
} elseif ($subweb_task == "Delete session" && $sessionId && $table=='prepaid') {
$query = sprintf(
"select active_sessions from %s where id = %d and %s",
addslashes($table),
addslashes($id),
$this->whereResellerFilter
);
if (!$this->db->query($query)) {
$log = sprintf(
"Database error for %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
print $log;
}
if (!$this->db->num_rows()) return;
$this->db->next_record();
if (strlen($this->db->f('active_sessions'))) {
// remove session
$active_sessions=array();
$old_active_sessions = json_decode($this->db->f('active_sessions'), true);
if (!count($old_active_sessions)) return;
foreach (array_keys($old_active_sessions) as $_key) {
if ($_key==$sessionId) continue;
$active_sessions[$_key]=$old_active_sessions[$_key];
}
} else {
$active_sessions=array();
}
$query = sprintf(
"update %s
set active_sessions = '%s',
session_counter = %d
where id = %d",
addslashes($table),
addslashes(json_encode($active_sessions)),
count($active_sessions),
addslashes($id)
);
if ($this->db->query($query)) {
return 1;
} else {
$log = sprintf(
"Database error for %s: %s (%s)",
$query,
$this->db->Error,
$this->db->Errno
);
syslog(LOG_NOTICE, $log);
print $log;
return 0;
}
}
if ($affected_rows && $table!="prepaid") {
$log_query = sprintf(
"insert into log
(date,login,ip,datasource,results,description,reseller_id)
values (NOW(),'%s','%s','Rating','%d','%s in table %s %s',%d)",
addslashes($loginname),
addslashes($_SERVER['REMOTE_ADDR']),
addslashes($affected_rows),
addslashes($subweb_task),
addslashes($table),
addslashes($log_entity),
addslashes($this->CDRTool['filter']['reseller'])
);
$this->db->query($log_query);
}
}
}
public function showTable()
{
$PHP_SELF=$_SERVER['PHP_SELF'];
foreach ($this->web_elements as $_el) {
${$_el}= $_REQUEST[$_el];
}
if ($this->table == 'prepaid_cards') {
print "
Prepaid card generator";
}
// Init table structure
if (!is_array($this->tables[$this->table]['exceptions'])) $this->tables[$this->table]['exceptions']=array();
if (!is_array($this->tables[$this->table]['keys'])) $this->tables[$this->table]['keys']=array();
if (!is_array($this->tables[$this->table]['fields'])) $this->tables[$this->table]['fields']=array();
if ($this->table=='prepaid' && strlen($_REQUEST['search_session_counter'])) {
$this->readonly=true;
}
if ($this->readonly) {
$this->tables[$this->table]['readonly']=1;
}
$metadata = $this->db->metadata($this->table);
$cc = count($metadata);
// end init table structure
//
// delimiter for exporting records
if ($this->settings['csv_delimiter']) {
$delimiter=$this->settings['csv_delimiter'];
} else {
$delimiter=",";
}
$query = sprintf(
"select count(*) as c from %s where %s",
addslashes($this->table),
$this->whereResellerFilter
);
$t=0;
$j=0;
while ($j < $cc) {
$Fname=$metadata[$j]['name'];
$size=$metadata[$j]['len'];
$class=$metadata[$j]['class'];
if (!in_array($Fname, $this->tables[$this->table]['exceptions'])) {
$f_name="search_".$Fname;
$value=$_REQUEST[$f_name];
if (preg_match("/^([<|>]+)(.*)$/", $value, $likes)) {
$like=$likes[1];
$likewhat=$likes[2];
$quotes="";
} else {
$like="like";
$likewhat=$value;
$quotes="'";
}
if (strlen($value)) {
$where .= sprintf(
" and %s %s %s%s%s ",
addslashes($Fname),
$like,
$quotes,
addslashes($likewhat),
$quotes
);
$t++;
}
}
$j++;
}
$query .= $where;
$this->db->query($query);
$this->db->next_record();
$rows=$this->db->Record['c'];
if (!$export) {
print "
";
if ($rows == 0) {
print "No records found. ";
} else {
print "$selectie $rows records found. ";
}
if ($this->settings['socketIPforClients'] && $this->settings['socketPort']) {
$engineAddress = $this->settings['socketIPforClients'].":".$this->settings['socketPort'];
if ($this->checkRatingEngineConnection()) {
print " | Rating engine running at $engineAddress";
} else {
print " | Cannot connect to rating engine $engineAddress";
}
}
print " | Rating documentation";
print "
";
if ($this->csv_import[$this->table]) {
print "
";
} else {
print "
";
}
print "
";
} else {
$this->maxrowsperpage = 10000000;
}
if (!$next) {
$i=0;
$next=0;
} else {
$i=intval($next);
}
$j=0;
$z=0;
if ($rows > $this->maxrowsperpage) {
$maxrows = $this->maxrowsperpage + $next;
if ($maxrows > $rows) {
$maxrows=$rows;
$prev_rows=$maxrows;
}
} else {
$maxrows=$rows;
}
if (!$order && $this->tables[$this->table]['order']) {
$order = sprintf(
" order by %s ",
addslashes($this->tables[$this->table]['order'])
);
}
$query = sprintf(
"select * from %s where (1=1) %s and %s %s limit %d, %d",
addslashes($this->table),
$where,
$this->whereResellerFilter,
$order,
intval($i),
intval($this->maxrowsperpage)
);
$this->db->query($query);
$num_fields=$this->db->num_fields();
$k=0;
if (!$export) {
if ($this->table=='prepaid') {
print "
";
} else {
print "
";
}
}
while ($k < $cc) {
$th = $metadata[$k]['name'];
if (!in_array($th, $this->tables[$this->table]['exceptions'])) {
if ($this->tables[$this->table]['fields'][$th]['name']) {
$th=$this->tables[$this->table]['fields'][$th]['name'];
} else {
$th=ucfirst($th);
}
if (!$export) {
print "