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 page_execute($argv)
{
/*
Input Options
*/
$id_service_customer = $argv[2];
if (empty($argv[2])) {
die("id_service_customer must be set\n");
}
$output_file = $argv[3];
if (empty($argv[3])) {
die("An output file must be specific for CVS output");
}
/*
Fetch customer details
*/
$id_customer = sql_get_singlevalue("SELECT customerid as value FROM services_customers WHERE id='{$id_service_customer}' LIMIT 1");
if (!$id_customer) {
// no id_customer, return fail
die("invalid id_service_customer, no matching customer entry found\n");
}
log_write("notification", "repair", "Reviewing call rate charges for customer id {$id_customer} with id_service_customer id {$id_service_customer}");
/*
Write File Header
*/
$handle_file_csv = fopen($output_file, 'w');
if (!$handle_file_csv) {
die("Unable to open file {$output_file} for writing");
}
fwrite($handle_file_csv, '"Date";"Source";"Destination";"Num Seconds";"Original Price";"New Price";"Refund Amount";"Original Billgroup";"New Billgroup";' . "\n");
log_write("notification", "repair", "Writing call details to file {$output_file}");
/*
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 = $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.
*/
$service_usage_cdr = new service_usage_cdr();
$service_usage_cdr->id_service_customer = $id_service_customer;
$service_usage_cdr->load_data_service();
$service_usage_cdr->load_data_ddi();
/*
Fetch bill groups
*/
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, billgroup_name FROM `cdr_rate_billgroups`";
$sql_obj->execute();
$sql_obj->fetch_array();
$billgroups = array();
foreach ($sql_obj->data as $data) {
$billgroups[$data["id"]] = $data["billgroup_name"];
}
/*
We want to run through and match any calls that are charged as nationals but actually locals.
Fetch all call rates which are from 64X to 64X and tagged as national calls
*/
$obj_calls_sql = new sql_query();
$obj_calls_sql->string = "SELECT date, price, usage1 as src, usage2 as dst, usage3 as billsec, billgroup FROM service_usage_records WHERE id_service_customer='{$id_service_customer}' ORDER BY date";
$obj_calls_sql->execute();
if (!$obj_calls_sql->num_rows()) {
die("No rows returned, is this a valid service to be checking?\n");
}
$obj_calls_sql->fetch_array();
$refund_total = 0;
foreach ($obj_calls_sql->data as $data_cdr) {
$charges = $obj_cdr_rate_table->calculate_charges($data_cdr["billsec"], $data_cdr["src"], $data_cdr["dst"], $service_usage_cdr->data_local[$data_cdr["src"]], $service_usage_cdr->data_ddi);
if ($data_cdr["billgroup"] != $charges["billgroup"]) {
// changed billgroup
$charges["price"] = round($charges["price"], 2);
// handle refund
$refund = $data_cdr["price"] - $charges["price"];
$refund_total = $refund_total + $refund;
// write output
log_write("debug", "repair", "Call on " . $data_cdr["date"] . " from " . $data_cdr["src"] . " to " . $data_cdr["dst"] . " for " . $data_cdr["billsec"] . " had cost of " . $data_cdr["price"] . " now has cost of " . $charges["price"] . ", old billgroup of " . $data_cdr["billgroup"] . " new billgroup of " . $charges["billgroup"] . "\n");
fwrite($handle_file_csv, '"' . $data_cdr["date"] . '";"' . $data_cdr["src"] . '";"' . $data_cdr["dst"] . '";"' . $data_cdr["billsec"] . '";"' . $data_cdr["price"] . '";"' . $charges["price"] . '";"' . $refund . '";"' . $billgroups[$data_cdr["billgroup"]] . '";"' . $billgroups[$charges["billgroup"]] . '";' . "\n");
}
}
// total refund
fwrite($handle_file_csv, '"";"";"";"";"";"";"' . $refund_total . '";"";"";' . "\n");
// close file
fclose($handle_file_csv);
}
