log_write("error", "sql_query", "Unable to connect to traffic service usage database!");
error_flag_field("SERVICE_TRAFFIC_DB_HOST");
error_flag_field("SERVICE_TRAFFIC_DB_NAME");
error_flag_field("SERVICE_TRAFFIC_DB_USERNAME");
error_flag_field("SERVICE_TRAFFIC_DB_PASSWORD");
} else {
log_write("notification", "sql_query", "Tested successful connection to traffic usage database");
$obj_sql->session_terminate();
}
}
/*
Test CDR Database
*/
if ($data["SERVICE_CDR_DB_TYPE"] == "mysql_cdr_daily") {
$obj_sql = new sql_query();
if (!$obj_sql->session_init("mysql", $data["SERVICE_CDR_DB_HOST"], $data["SERVICE_CDR_DB_NAME"], $data["SERVICE_CDR_DB_USERNAME"], $data["SERVICE_CDR_DB_PASSWORD"])) {
log_write("error", "sql_query", "Unable to connect to CDR service usage database!");
error_flag_field("SERVICE_CDR_DB_HOST");
error_flag_field("SERVICE_CDR_DB_NAME");
error_flag_field("SERVICE_CDR_DB_USERNAME");
error_flag_field("SERVICE_CDR_DB_PASSWORD");
} else {
log_write("notification", "sql_query", "Tested successful connection to CDR usage database");
$obj_sql->session_terminate();
}
}
/*
Process Errors
*/
if (error_check()) {
$_SESSION["error"]["form"]["config_services"] = "failed";
function fetch_usage_calls()
{
log_write("debug", "service_usage_cdr", "Executing fetch_usage_calls()");
/*
Load Call Pricing (including overrides for this customer)
*/
$obj_cdr_rate_table = new cdr_rate_table_rates_override();
$obj_cdr_rate_table->option_type = "customer";
$obj_cdr_rate_table->option_type_id = $this->id_service_customer;
$obj_cdr_rate_table->verify_id_override();
$obj_cdr_rate_table->load_data();
$obj_cdr_rate_table->load_data_rate_all();
$obj_cdr_rate_table->load_data_rate_all_override();
/*
We need a list of the DDIs for this customer.
*/
if (!$this->data_ddi) {
$this->load_data_ddi();
}
/*
Query Call Records
*/
if ($GLOBALS["config"]["SERVICE_CDR_MODE"] == "internal") {
log_write("debug", "service_usage_cdr", "Fetching call records from internal ABS database..");
/*
Whilst for large datasets, Amberdms recommends using an external CDR database, it may be
desirable to use the internal ABS call data database for record billing, such as when importing from
another system via the API.
when using the internal database, the service_usage_records table is used:
date Date of Call
price Price of call
usage1 Source DDI
usage2 Destination DDI
usage3 Billable call seconds
Note that we don't need to loop through the DDIs here, since anyone using the internal database has already matched the DDIs to
the id_service_customer via the API usage upload.
The way the internal database is used will vary depending on the information provided - if a price is already set, this
price will be used for the total call cost.
If no price is set, the costs will be looked up in the rate table.
This behaviour allows charged call rates to be imported from another platform and not having to be re-calculated against
ABS's own rate table.
*/
// calculate end date to be the first date of the next period - failure to do so would mean we would
// miss the last day of the billing period in the query;
$date_start = $this->date_start;
$tmp_date = explode("-", $this->date_end);
$date_end = date("Y-m-d", mktime(0, 0, 0, $tmp_date[1], $tmp_date[2] + 1, $tmp_date[0]));
/*
Fetch Data
Just a simple query here, however if this is a TOLLFREE service, then we need to reverse
the query to charge for inbound calls rather than outbound.
*/
log_write("debug", "service_usage_cdr", "Fetching usage records FOR {$ddi} FROM {$date_start} TO {$date_end}");
$obj_cdr_sql = new sql_query();
if ($this->obj_service->data["typeid_string"] == "phone_tollfree") {
log_write("debug", "service_usage_cdr", "Billing for tollfree service on {$ddi}");
// NOTE! for toll-free services, we reverse src and dst for reverse billing calculations
$obj_cdr_sql->string = "SELECT id, date, price, usage1 as dst, usage2 as src, usage3 as billsec, billgroup FROM service_usage_records WHERE id_service_customer='" . $this->id_service_customer . "' AND date >= '{$date_start}' AND date < '{$date_end}'";
$obj_cdr_sql->execute();
} else {
$obj_cdr_sql->string = "SELECT id, date, price, usage1 as src, usage2 as dst, usage3 as billsec, billgroup FROM service_usage_records WHERE id_service_customer='" . $this->id_service_customer . "' AND date >= '{$date_start}' AND date < '{$date_end}'";
$obj_cdr_sql->execute();
}
/*
Calculate costs of calls
*/
if ($obj_cdr_sql->num_rows()) {
$obj_cdr_sql->fetch_array();
foreach ($obj_cdr_sql->data as $data_cdr) {
// determine price
if ($data_cdr["price"] != "0.00") {
// a price has already been set - make use of that
$charges = array();
$charges["price"] = $data_cdr["price"];
$charges["billgroup"] = $data_cdr["billgroup"];
} else {
$charges = $obj_cdr_rate_table->calculate_charges($data_cdr["billsec"], $data_cdr["src"], $data_cdr["dst"], $this->data_local[$data_cdr["src"]], $this->data_ddi);
// update the charges in the records
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE service_usage_records SET price='" . $charges["price"] . "', billgroup='" . $charges['billgroup'] . "' WHERE id='" . $data_cdr["id"] . "'";
$sql_obj->execute();
}
// add to structure - we use the SRC as the DDI
$this->data[$data_cdr["src"]][$charges["billgroup"]]["charges"] += $charges["price"];
// TODO: this won't catch issues where the DDIs configured don't match the SRC DDI (although it should always!)
}
}
} else {
/*
Connect to External SQL database
*/
// fetch all calls for that DDI from the DB for the selected period
$obj_cdr_db_sql = new sql_query();
if (!$obj_cdr_db_sql->session_init("mysql", $GLOBALS["config"]["SERVICE_CDR_DB_HOST"], $GLOBALS["config"]["SERVICE_CDR_DB_NAME"], $GLOBALS["config"]["SERVICE_CDR_DB_USERNAME"], $GLOBALS["config"]["SERVICE_CDR_DB_PASSWORD"])) {
return 0;
}
/*
We fetch the call records by looping through all the DDIs for this customer, fetching all the records
for those DDIs and then calculating the cost for each call
*/
foreach ($this->data_ddi as $ddi) {
// calculate end date to be the first date of the next period - failure to do so would mean we would
// miss the last day of the billing period in the query;
$date_start = $this->date_start;
$tmp_date = explode("-", $this->date_end);
$date_end = date("Y-m-d", mktime(0, 0, 0, $tmp_date[1], $tmp_date[2] + 1, $tmp_date[0]));
/*
Fetch Data
Just a simple query here, however if this is a TOLLFREE service, then we need to reverse
the query to charge for inbound calls rather than outbound.
*/
log_write("debug", "service_usage_cdr", "Fetching usage records FOR {$ddi} FROM {$date_start} TO {$date_end}");
if ($this->obj_service->data["typeid_string"] == "phone_tollfree") {
log_write("debug", "service_usage_cdr", "Billing for tollfree service on {$ddi}");
// NOTE! for toll-free services, we reverse src and dst for reverse billing calculations
$obj_cdr_db_sql->string = "SELECT calldate, billsec, dst as src, src as dst FROM cdr WHERE disposition='ANSWERED' AND dst='{$ddi}' AND calldate >= '{$date_start}' AND calldate < '{$date_end}'";
$obj_cdr_db_sql->execute();
} else {
$obj_cdr_db_sql->string = "SELECT calldate, billsec, src, dst FROM cdr WHERE disposition='ANSWERED' AND src='{$ddi}' AND calldate >= '{$date_start}' AND calldate < '{$date_end}'";
$obj_cdr_db_sql->execute();
}
/*
Calculate costs of calls
*/
if (!isset($this->data[$ddi][$charges["billgroup"]]["charges"])) {
$this->data[$ddi][$charges["billgroup"]]["charges"] = 0;
}
if ($obj_cdr_db_sql->num_rows()) {
$obj_cdr_db_sql->fetch_array();
foreach ($obj_cdr_db_sql->data as $data_cdr) {
// determine price
$charges = $obj_cdr_rate_table->calculate_charges($data_cdr["billsec"], $data_cdr["src"], $data_cdr["dst"], $this->data_local[$ddi], $this->data_ddi);
// create local usage record for record keeping purposes
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO service_usage_records (id_service_customer, date, price, usage1, usage2, usage3, billgroup) VALUES ('" . $this->id_service_customer . "', '" . $data_cdr["calldate"] . "', '" . $charges["price"] . "', '" . $data_cdr["src"] . "', '" . $data_cdr["dst"] . "', '" . $data_cdr["billsec"] . "', '" . $charges["billgroup"] . "')";
$sql_obj->execute();
// add to structure
$this->data[$ddi][$charges["billgroup"]]["charges"] += $charges["price"];
}
}
}
// end of DDI loop
/*
Disconnect from database
*/
$obj_cdr_db_sql->session_terminate();
}
// end of external data source
}
function fetch_usage_traffic()
{
log_write("debug", "service_usage_traffic", "Executing fetch_usage_traffic()");
/*
Fetch data traffic types
Note that this doesn't query overrides, since the override options will never impact which traffic types that exist,
only how they are billed by include/services/inc_services_invoicegen.php
*/
$traffic_types = sql_get_singlecol("SELECT traffic_types.type_label as col FROM `traffic_caps` LEFT JOIN traffic_types ON traffic_types.id = traffic_caps.id_traffic_type WHERE traffic_caps.id_service='" . $this->obj_service->id . "'");
/*
Fetch raw usage data from DB
*/
if ($GLOBALS["config"]["SERVICE_TRAFFIC_MODE"] == "internal") {
/*
Internal Database
Use the internal database - this stores the usage information for upload/download mapped against the customer's
IP address.
TODO: Currently all traffic is just assigned against type any/*, this should be upgraded to properly support
the different traffic types.
*/
log_write("debug", "service_usage_traffic", "Fetching traffic records from internal database");
// fetch upload/download stats
$sql_obj = new sql_query();
$sql_obj->string = "SELECT SUM(usage1) as usage1, SUM(usage2) as usage2 FROM service_usage_records WHERE id_service_customer='" . $this->id_service_customer . "' AND date>='" . $this->date_start . "' AND date<='" . $this->date_end . "'";
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data["usage1"] = $sql_obj->data[0]["usage1"];
$this->data["usage2"] = $sql_obj->data[0]["usage2"];
unset($sql_obj);
// create a total of both usage columns
$sql_obj = new sql_query();
$sql_obj->string = "SELECT '" . $this->data["usage1"] . "' + '" . $this->data["usage2"] . "' as totalusage";
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data["total"]["*"] = $sql_obj->data[0]["totalusage"];
$this->data["total"]["total"] = $sql_obj->data[0]["totalusage"];
unset($sql_obj);
// we now have the raw usage
log_write("debug", "service_usage_traffic", "Total raw traffic usage for " . $this->date_start . " until " . $this->date_end . " is " . $this->data["total"]["total"] . " bytes");
} else {
/*
Connect to External SQL database
External DBs are common with larger teleco providers since it allows easier storage, splicing and archiving of usage information
for data traffic services.
*/
switch ($GLOBALS["config"]["SERVICE_TRAFFIC_DB_TYPE"]) {
case "mysql_netflow_daily":
/*
MODE: mysql_netflow_daily
In this mode, there are netflow tables for each day which we need to read through and aggregate data from, typically
this is done so that busy ISPs don't end up with massive monthly/yearly tables.
eg:
traffic_20110420
traffic_20110421
traffic_20110422
TODO: Currently all traffic is just assigned against type any/*, this should be upgraded to properly support
the different traffic types.
*/
log_write("debug", "service_usage_traffic", "Processing external database mysql_netflow_daily");
/*
Connect to external database
*/
$obj_traffic_db_sql = new sql_query();
if (!$obj_traffic_db_sql->session_init("mysql", $GLOBALS["config"]["SERVICE_TRAFFIC_DB_HOST"], $GLOBALS["config"]["SERVICE_TRAFFIC_DB_NAME"], $GLOBALS["config"]["SERVICE_TRAFFIC_DB_USERNAME"], $GLOBALS["config"]["SERVICE_TRAFFIC_DB_PASSWORD"])) {
log_write("error", "service_usage_traffic", "Unable to establish a connection to the external traffic DB, unable to run data usage processing.");
return 0;
}
/*
Workout the date range, since we need to query a different table for each day
TODO: this would be nice as a generic function?
*/
$tmp_date = $this->date_start;
$date_range = array();
$date_range[] = $this->date_start;
while ($tmp_date != $this->date_end) {
$tmp_date = explode("-", $tmp_date);
$tmp_date = date("Y-m-d", mktime(0, 0, 0, $tmp_date[1], $tmp_date[2] + 1, $tmp_date[0]));
$date_range[] = $tmp_date;
}
for ($i = 0; $i < count($date_range); $i++) {
// strip "-" charactor
$date_range[$i] = str_replace("-", "", $date_range[$i]);
}
/*
We work out the usage by fetching the totals for each IPv4 address belonging to this
service and aggregating the total.
*/
// make sure we have the array of IPv4 addresses
if (!$this->data_ipv4) {
$this->load_data_ipv4();
}
// blank current total
$this->data["total"]["total"] = 0;
// verify IPv4 address have been configured
if (!is_array($this->data_ipv4)) {
log_write("warning", "service_usage_traffic", "Note: No IPv4 addresses have been configured for this customer");
return 0;
}
// run through each IP
foreach ($this->data_ipv4 as $ipv4) {
/*
Fetch Data
We run through each IP and for each IP, we fetch the total from all the daily tables. Note that
we make the assumption that daily tables might not exist if there's nothing to be processed for that
day, so the code is written accordingly.
Note that we use the SQL database for *ALL* calculations, this is due to the SQL DB being able
to handle 64bit integers, whereas PHP will vary depending on the host platform.
*/
log_write("debug", "service_usage_traffic", "Fetching usage records FOR address {$ipv4} FOR date " . $this->date_start . " to " . $this->date_end . "");
// run through the dates
foreach ($date_range as $date) {
// check that the table exists
$obj_traffic_db_sql->string = "SHOW TABLES LIKE 'traffic_{$date}'";
$obj_traffic_db_sql->execute();
if ($obj_traffic_db_sql->num_rows()) {
// query the current date for the current IP
$obj_traffic_db_sql->string = "SELECT SUM(bytes) as total FROM traffic_{$date} WHERE ip_src='{$ipv4}' OR ip_dst='{$ipv4}'";
$obj_traffic_db_sql->execute();
$obj_traffic_db_sql->fetch_array();
if (!empty($obj_traffic_db_sql->data[0]["total"])) {
// add to running total
$sql_obj = new sql_query();
$sql_obj->string = "SELECT '" . $this->data["total"]["total"] . "' + '" . $obj_traffic_db_sql->data[0]["total"] . "' as totalusage";
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data["total"]["total"] = $sql_obj->data[0]["totalusage"];
}
// end if traffic exists
} else {
log_write("warning", "service_usage_traffic", "SQL database table traffic_{$date} does not exist");
}
}
// end foreach date
log_write("debug", "service_usage_traffic", "Completed usage query for address {$ipv4}.");
}
// end foreach ipv4
log_write("debug", "service_usage_traffic", "Total usage for all addresses in the date range is " . $this->data["total"]["total"] . " bytes");
/*
Disconnect from database
*/
$obj_traffic_db_sql->session_terminate();
unset($obj_traffic_db_sql);
break;
case "mysql_traffic_summary":
/*
MODE: mysql_traffic_summary
In this mode, the database contains a single table "traffic_summary" which includes the following key fields:
* ip_address IPv4 Address
* traffic_datetime Date/Time Field
* traffic_type Type of traffic
* total Total Bytes transfered
Ideally this table should contain one row per IP address, per day, to enable billing to occur.
TODO: update to support traffic types
*/
log_write("debug", "service_usage_traffic", "Processing external database mysql_traffic_summary");
/*
Connect to external database
*/
$obj_traffic_db_sql = new sql_query();
if (!$obj_traffic_db_sql->session_init("mysql", $GLOBALS["config"]["SERVICE_TRAFFIC_DB_HOST"], $GLOBALS["config"]["SERVICE_TRAFFIC_DB_NAME"], $GLOBALS["config"]["SERVICE_TRAFFIC_DB_USERNAME"], $GLOBALS["config"]["SERVICE_TRAFFIC_DB_PASSWORD"])) {
log_write("error", "service_usage_traffic", "Unable to establish a connection to the external traffic DB, unable to run data usage processing.");
return 0;
}
/*
Loop through each IP and fetch usage for that IP.
*/
// make sure we have the array of IPv4 addresses
if (!$this->data_ipv4) {
$this->load_data_ipv4();
}
// blank current overall and per-type totals
$this->data["total"]["total"] = 0;
$this->data["total_byunits"]["total"] = 0;
foreach ($traffic_types as $type) {
$this->data["total"][$type] = 0;
$this->data["total_byunits"][$type] = 0;
}
// verify IPv4 address have been configured
if (!is_array($this->data_ipv4)) {
log_write("warning", "service_usage_traffic", "Note: No IPv4 addresses have been configured for this customer");
return 0;
}
// run through each IP
foreach ($this->data_ipv4 as $ipv4) {
/*
Fetch Data
We run through each IP and for each IP, we fetch the total for the date range.
Note that we use the SQL database for *ALL* calculations, this is due to the SQL DB being able
to handle 64bit integers, whereas PHP will vary depending on the host platform.
*/
log_write("debug", "service_usage_traffic", "Fetching usage records FOR address {$ipv4} FOR date " . $this->date_start . " to " . $this->date_end . "");
// check that the table exists
$obj_traffic_db_sql->string = "SHOW TABLES LIKE 'traffic_summary'";
$obj_traffic_db_sql->execute();
if ($obj_traffic_db_sql->num_rows()) {
// query the current date for the current IP
$obj_traffic_db_sql->string = "SELECT SUM(total) as total, traffic_type FROM traffic_summary WHERE ip_address='{$ipv4}' AND traffic_datetime >= '" . $this->date_start . "' AND traffic_datetime <= '" . $this->date_end . "' GROUP BY traffic_type";
$obj_traffic_db_sql->execute();
if ($obj_traffic_db_sql->num_rows()) {
$obj_traffic_db_sql->fetch_array();
foreach ($obj_traffic_db_sql->data as $data_traffic) {
if (in_array($data_traffic["traffic_type"], $traffic_types)) {
$type = $data_traffic["traffic_type"];
} else {
// unmatched type, assign to any
$type = "*";
}
// add to running total
$sql_obj = new sql_query();
$sql_obj->string = "SELECT '" . $this->data["total"][$type] . "' + '" . $data_traffic["total"] . "' as totalusage";
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data["total"][$type] = $sql_obj->data[0]["totalusage"];
unset($sql_obj);
}
}
// end if traffic exists
} else {
log_write("error", "service_usage_traffic", "SQL database table traffic_summary does not exist");
}
log_write("debug", "service_usage_traffic", "Total usage for address {$ipv4} is " . $this->data["total"] . " bytes");
}
// end foreach ipv4
// produce overall total
$sql_obj = new sql_query();
foreach ($traffic_types as $type) {
$sql_obj->string = "SELECT '" . $this->data["total"]["total"] . "' + '" . $this->data["total"][$type] . "' as totalusage";
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data["total"]["total"] = $sql_obj->data[0]["totalusage"];
}
unset($sql_obj);
log_write("debug", "service_usage_traffic", "Total usage for all addresses in the date range is " . $this->data["total"] . " bytes");
/*
Disconnect from database
*/
$obj_traffic_db_sql->session_terminate();
unset($obj_traffic_db_sql);
break;
default:
/*
Unknown DB type, we should fail.
*/
log_write("error", "debug", "External DB type " . $GLOBALS["config"]["SERVICE_TRAFFIC_DB_TYPE"] . " is not supported.");
return 0;
break;
}
// end of switch between DB types
}
// end of external data source
/*
Generate formatted usage
We have the number of raw units, we now need to generate the number of human readable/formatted units
from this figure.
*/
log_write("debug", "service_usage_traffic", "Generating formatted usage totals");
$this->data["numrawunits"] = sql_get_singlevalue("SELECT numrawunits as value FROM service_units WHERE id='" . $this->obj_service->data["units"] . "' LIMIT 1");
if (!$this->data["numrawunits"]) {
log_debug("service_usage_traffic", "Error: Unable to fetch number of raw units for the units type");
return 0;
}
foreach (array_keys($this->data["total"]) as $type) {
// calculate
$sql_obj = new sql_query();
$sql_obj->string = "SELECT '" . $this->data["total"][$type] . "' / '" . $this->data["numrawunits"] . "' as value";
$sql_obj->execute();
$sql_obj->fetch_array();
// format & return
$this->data["total_byunits"][$type] = round($sql_obj->data[0]["value"], 2);
}
log_write("debug", "service_usage_traffic", "Total traffic usage for period is " . $this->data["total_byunits"]["total"] . "");
/*
Complete
*/
return 1;
}
