Call Detail Records - Caller Number

Hi community!
Hope you can help me to sort out with the following problem : somehow now in Call Detail Records as Caller number i see the Outbound Caller id (which is set to external number in extension properties) instead of 4digit Extension and because of this i have empty Extension Summary statistics.
But im sure that some time ago it was showing me "extension id" instead of Outbound one..
Can you help me please to identify why?
Thanks!
 
Last edited:
I noticed the same thing... sorta. The CDR records show the outbound caller ID of the extension number, rather than the extension number itself. My extension summary has data though and looks fine.. this change just makes searching through the CDR's now a bit cumbersome since I can't see the extension number that made the call anymore without digging down into the CDR records in detail.

I'm running on the master branch, however.
 
So, just had to review the source code.
Finally sorted a little workaround using extension "alias" configured in Extension properties and modified the SQL query in xml_cdr_extension_summary.php
For those who needs here is the SQL


$sql = "SELECT \n";
$sql .= "e.domain_uuid, \n";
$sql .= "d.domain_name, \n";
$sql .= "e.extension, \n";
$sql .= "e.number_alias, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE c.domain_uuid = e.domain_uuid \n";
$sql .= " AND ((\n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL and ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND (\n";
$sql .= " c.answer_stamp IS NOT NULL \n";
$sql .= " and \n";
$sql .= " c.bridge_uuid IS NOT NULL) \n";

if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= "AND direction = 'inbound') \n";
}
$sql .= "AS answered, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR (\n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND ( \n";
$sql .= " c.answer_stamp IS NULL \n";
$sql .= " AND \n";
$sql .= " c.bridge_uuid IS NULL) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'outbound'))";
} else {
$sql .= " AND direction = 'inbound')";
}
$sql .= "AS missed, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND c.hangup_cause = 'NO_ANSWER' \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local') \n";
}
else {
$sql .= "AND direction = 'inbound' \n";
}
$sql .= ") AS no_answer, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND \n";
$sql .= " c.hangup_cause = 'USER_BUSY' \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= " AND direction = 'inbound') \n";
}
$sql .= "AS busy, \n";

$sql .= "SUM(c.billsec) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'outbound') \n";
}
$sql .= " ) / \n";
$sql .= "COUNT(*) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'outbound') \n";
}
$sql .= " ) AS aloc, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= " AND direction = 'inbound') \n";
}
$sql .= "AS inbound_calls, \n";

$sql .= "SUM(c.billsec) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
if ($this->include_internal) {
$sql .= " AND (direction = 'inbound' OR direction = 'local')) \n";
}
else {
$sql .= " AND direction = 'inbound') \n";
}
$sql .= "AS inbound_duration, \n";

$sql .= "COUNT(*) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND \n";
$sql .= " c.direction = 'outbound') \n";
$sql .= "AS outbound_calls, \n";

$sql .= "SUM(c.billsec) \n";
$sql .= "FILTER ( \n";
$sql .= " WHERE (( \n";
$sql .= " c.caller_id_name = e.extension \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.extension) \n";
$sql .= " OR ( \n";
$sql .= " e.number_alias IS NOT NULL \n";
$sql .= " AND ( \n";
$sql .= " c.caller_id_name = e.number_alias \n";
$sql .= " OR \n";
$sql .= " c.destination_number = e.number_alias))) \n";
$sql .= " AND ( \n";
$sql .= " c.direction = 'outbound')) \n";
$sql .= "AS outbound_duration, \n";

$sql .= "e.description \n";

$sql .= "FROM v_extensions AS e, v_domains AS d, \n";
$sql .= "( SELECT \n";
$sql .= " domain_uuid, \n";
$sql .= " caller_id_name, \n";
$sql .= " destination_number, \n";
$sql .= " answer_stamp, \n";
$sql .= " bridge_uuid, \n";
$sql .= " direction, \n";
$sql .= " start_stamp, \n";
$sql .= " hangup_cause, \n";
$sql .= " billsec \n";
$sql .= " FROM v_xml_cdr \n";
$sql .= " WHERE domain_uuid = '".$this->domain_uuid."' \n";
$sql .= $sql_date_range;
$sql .= ") AS c \n";

$sql .= "WHERE \n";
$sql .= "d.domain_uuid = e.domain_uuid \n";
if (!($_GET['showall'] && permission_exists('xml_cdr_all'))) {
$sql .= "AND e.domain_uuid = '".$this->domain_uuid."' \n";
}
$sql .= "GROUP BY e.extension, e.domain_uuid, d.domain_uuid, e.number_alias, e.description \n";
$sql .= "ORDER BY extension ASC \n";

$prep_statement = $this->db->prepare(check_sql($sql));
print_r($prep_statement);
$prep_statement->execute();
$summary = $prep_statement->fetchAll(PDO::FETCH_NAMED);

Next step is to fix xml_cdr_statistics.php page because its also empty..
 
I worked on this and fixed on master and 4.4 branch updated and try again.
Hi,
I am having the same problem with version 4.3.1. Could you advise if this can be solved without fully upgrading to 4.4 branch?
Does it also require changes on database and freeswitch scripts?

My system is on production, and I am kind of scared to upgrade it at the moment. If it only require some changes on php files, can we replace those files from 4.4 branch?

Please advise what changes will solve this issue.

Regards,
Saiful Alam