diff --git a/library/cdr_generic.php b/library/cdr_generic.php index 7bffb6b..5a0a003 100644 --- a/library/cdr_generic.php +++ b/library/cdr_generic.php @@ -1,3399 +1,3399 @@ 'RadAcctId', 'callId' => 'AcctSessionId', 'username' => 'UserName', 'domain' => 'Realm', 'gateway' => 'NASIPAddress', 'duration' => 'AcctSessionTime', 'startTime' => 'AcctStartTime', 'stopTime' => 'AcctStopTime', 'inputTraffic' => 'AcctInputOctets', 'outputTraffic' => 'AcctOutputOctets', 'aNumber' => 'CallingStationId', 'cNumber' => 'CalledStationId', 'timestamp' => 'timestamp', 'BillingPartyId' => 'UserName', 'sipRPID' => 'SipRPID', 'ResellerId' => 'BillingId', 'price' => 'Price', 'DestinationId' => 'DestinationId' ); function _readCDRNormalizationFieldsFromDB() { foreach (array_keys($this->CDRNormalizationFields) as $field) { $mysqlField = $this->CDRNormalizationFields[$field]; $CDRStructure[$mysqlField] = $this->CDRdb->f($mysqlField); } return $CDRStructure; } - function _readCDRFieldsFromDB($fields) + protected function _readCDRFieldsFromDB() { foreach (array_keys($this->CDRFields) as $field) { $mysqlField = $this->CDRFields[$field]; $CDRStructure[$mysqlField] = $this->CDRdb->f($mysqlField); } return $CDRStructure; } public function initCDRFields() { // init names of CDR fields foreach (array_keys($this->CDRFields) as $field) { $mysqlField = $this->CDRFields[$field]; $_field = $field."Field"; $this->$_field = $mysqlField; } } private function initDatabaseConnection() { // connect to the CDR database(s) if(!$this->DATASOURCES[$this->cdr_source]['db_class']) { $log = sprintf("Error: \$DATASOURCES['%s']['db_class'] is not defined (init)", $this->cdr_source); syslog(LOG_NOTICE, $log); return 0; } $_dbClass = $this->DATASOURCES[$this->cdr_source]['db_class']; if (is_array($_dbClass)) { if ($_dbClass[0]) $this->primary_database = $_dbClass[0]; if ($_dbClass[1]) $this->secondary_database = $_dbClass[1]; } else { $this->primary_database = $_dbClass; } if(!class_exists($this->primary_database)) { $log = sprintf("Error: database class '%s' is not defined", $this->primary_database); syslog(LOG_NOTICE, $log); return 0; } $this->CDRdb = new $this->primary_database; // check db connectivity if (!$this->CDRdb->query('SELECT 1')) { $log = sprintf("Error: failed to connect to the primary CDR database %s\n", $this->primary_database); syslog(LOG_NOTICE, $log); if ($this->secondary_database) { $this->CDRdb = new $this->secondary_database; if (!$this->CDRdb->query('SELECT 1')) { $log = sprintf("Error: failed to connect to the secondary CDR database %s\n", $this->secondary_database); syslog(LOG_NOTICE, $log); return 0; } else { $this->CDRdb1 = new $this->secondary_database; $this->db_class = $this->secondary_database; } } else { return 0; } } else { $this->CDRdb1 = new $this->primary_database; $this->db_class = $this->primary_database; } return 1; } public function __construct($cdr_source) { global $CDRTool; global $DATASOURCES; global $RatingEngine; if (!$cdr_source) { $log = "Error: cdr_source not defined\n"; syslog(LOG_NOTICE, $log); return 0; } if (!$DATASOURCES[$cdr_source]) { $log = sprintf("Error: no such datasource defined (%s)\n", $cdr_source); syslog(LOG_NOTICE, $log); return 0; } $this->initDefaults(); $this->cdrtool = new DB_CDRTool(); $this->cdr_source = $cdr_source; $this->CDRTool = $CDRTool; $this->rating_settings = $RatingEngine; $this->DATASOURCES = $DATASOURCES; $this->cdrtool->Halt_On_Error = "no"; $this->table = $this->DATASOURCES[$this->cdr_source]['table']; $this->initCDRFields(); if ($this->DATASOURCES[$this->cdr_source]['rating']) { $this->ratingEnabled = 1; $this->rating = $this->DATASOURCES[$this->cdr_source]['rating']; if ($this->DATASOURCES[$this->cdr_source]['showRate']) $this->showRate = $this->DATASOURCES[$this->cdr_source]['showRate']; if ($this->DATASOURCES[$this->cdr_source]['rateField']) $this->rateField = $this->DATASOURCES[$this->cdr_source]['rateField']; if ($this->DATASOURCES[$this->cdr_source]['priceField']) $this->priceField = $this->DATASOURCES[$this->cdr_source]['priceField']; } if ($this->DATASOURCES[$this->cdr_source]['UserQuotaClass']) { $this->quotaEnabled = 1; $this->quota_init_flag = $this->cdr_source.':quotaCheckInit'; $this->quota_reset_flag = $this->cdr_source.':reset_quota_for'; if ($this->DATASOURCES[$this->cdr_source]['daily_quota']) { $this->daily_quota=$this->DATASOURCES[$this->cdr_source]['daily_quota']; } } $this->initDatabaseConnection(); if ($this->DATASOURCES[$this->cdr_source]['DestinationIdField']) { $this->DestinationIdField = $this->DATASOURCES[$this->cdr_source]['DestinationIdField']; } if ($this->DATASOURCES[$this->cdr_source]['normalizedField']) { $this->normalizedField = $this->DATASOURCES[$this->cdr_source]['normalizedField']; } if (array_key_exists('intAccessCode', $this->DATASOURCES[$this->cdr_source]) && strlen($this->DATASOURCES[$this->cdr_source]['intAccessCode']) ) { $this->intAccessCode = $this->DATASOURCES[$this->cdr_source]['intAccessCode']; } if (array_key_exists('natAccessCode', $this->DATASOURCES[$this->cdr_source]) && strlen($this->DATASOURCES[$this->cdr_source]['natAccessCode']) ) { $this->natAccessCode = $this->DATASOURCES[$this->cdr_source]['natAccessCode']; } if ($this->DATASOURCES[$this->cdr_source]['db_subscribers']) { if (class_exists($this->DATASOURCES[$this->cdr_source]['db_subscribers'])) { $this->AccountsDB = new $this->DATASOURCES[$this->cdr_source]['db_subscribers']; $this->db_subscribers = $this->DATASOURCES[$this->cdr_source]['db_subscribers']; } else { $log = sprintf( "Error: subscribers database class %s is not defined", $this->DATASOURCES[$this->cdr_source]['db_subscribers'] ); syslog(LOG_NOTICE, $log); return 0; } } else if (class_exists('DB_opensips')) { $this->AccountsDB = new DB_opensips(); $this->db_subscribers = 'DB_opensips'; } else { $log = sprintf( "Error: subscribers database is not defined, please define 'db_subscribers' in datasource '%s'", $this->cdr_source ); syslog(LOG_NOTICE, $log); return 0; } if ($this->DATASOURCES[$this->cdr_source]['BillingIdField']) { $this->BillingIdField = $this->DATASOURCES[$this->cdr_source]['BillingIdField']; } if ($this->DATASOURCES[$this->cdr_source]['E164_class']) { if (class_exists($this->DATASOURCES[$this->cdr_source]['E164_class'])) { $this->E164_class = $this->DATASOURCES[$this->cdr_source]['E164_class']; } else { printf( "Error: E164 class '%s' defined in datasource %s does not exist, using default '%s'", $this->DATASOURCES[$this->cdr_source]['E164_class'], $this->cdr_source, $this->E164_class ); } } if ($this->DATASOURCES[$this->cdr_source]['sipTrace']) { $this->sipTrace = $this->DATASOURCES[$this->cdr_source]['sipTrace']; } if ($this->DATASOURCES[$this->cdr_source]['mediaTrace']) { $this->mediaTrace = $this->DATASOURCES[$this->cdr_source]['mediaTrace']; } if ($this->DATASOURCES[$this->cdr_source]['domain_table']) { $this->domain_table = $this->DATASOURCES[$this->cdr_source]['domain_table']; } if ($this->DATASOURCES[$this->cdr_source]['skipNormalize']) { $this->skipNormalize = $this->DATASOURCES[$this->cdr_source]['skipNormalize']; } if ($this->DATASOURCES[$this->cdr_source]['enableThor']) { $this->enableThor = $this->DATASOURCES[$this->cdr_source]['enableThor']; } if (is_array($this->CDRTool['normalize']['CS_CODES'])) $this->CS_CODES=array_keys($this->CDRTool['normalize']['CS_CODES']); $this->missed_calls = $this->DATASOURCES[$this->cdr_source]['missed_calls']; $this->traceInURL = $this->DATASOURCES[$this->cdr_source]['traceInURL']; $this->traceOutURL = $this->DATASOURCES[$this->cdr_source]['traceOutURL']; $this->protocolTraceURL = $this->DATASOURCES[$this->cdr_source]['protocolTraceURL']; $spath = explode("/",$_SERVER["PHP_SELF"]); $last = count($spath)-1; $this->scriptFile=$spath[$last]; $this->next = $_REQUEST["next"]; $this->export = $_REQUEST["export"]; $this->trace = $_REQUEST["trace"]; if ($this->export) { $this->maxrowsperpage=10000000; } else { if ($_REQUEST["maxrowsperpage"]) { $this->maxrowsperpage = $_REQUEST["maxrowsperpage"]; } else { $this->maxrowsperpage = "25"; } } $this->LoadDisconnectCodes(); $this->LoadDestinations(); $this->LoadENUMtlds(); $this->LoadDomains(); $this->LoadTrustedPeers(); $this->getCDRtables(); if ($this->DATASOURCES[$this->cdr_source]['csv_writer_class']) { $csv_writter_class=$this->DATASOURCES[$this->cdr_source]['csv_writer_class']; if (class_exists($csv_writter_class)) { $this->csv_writter = new $csv_writter_class( $this->cdr_source, $this->DATASOURCES[$this->cdr_source]['csv_directory'], $this->db_subscribers ); } } $this->initOK=1; } function initDefaults() { if (is_readable('/etc/default/cdrtool')) { $defaultContentLines = explode("\n", file_get_contents('/etc/default/cdrtool')); foreach ($defaultContentLines as $_line) { list($defaults_key, $defaults_value) = explode("=", $_line); if (strlen($defaults_value)) $this->defaults[trim($defaults_key)]=trim($defaults_value); } } } function LoadDomains() { } function LoadTrustedPeers() { } function LoadAccounts() { } function LoadDestinations() { $_destinations = array(); $_destinations_sip = array(); $this->destinations_count = 0; $this->destinations_sip_count = 0; $query = sprintf("select `value` from memcache where `key` = 'destinations'"); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } if ($this->cdrtool->num_rows()) { $b = time(); $this->cdrtool->next_record(); $_destinations = json_decode($this->cdrtool->f('value'),true); foreach (array_keys($_destinations) as $_key1) { foreach(array_keys($_destinations[$_key1]) as $_key2) { $this->destinations_count = $this->destinations_count + count($_destinations[$_key1][$_key2]); } } if (!$this->destinations_count) { $log = "Error: cached destinations key contains no data"; syslog(LOG_NOTICE, $log); } $query = sprintf("select `value` from memcache where `key` = 'destinations_sip'"); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } if ($this->cdrtool->num_rows()) { $this->cdrtool->next_record(); $_destinations_sip = json_decode($this->cdrtool->f('value'), true); foreach (array_keys($_destinations_sip) as $_key1) { foreach (array_keys($_destinations_sip[$_key1]) as $_key2) { $this->destinations_sip_count = $this->destinations_count + count($_destinations_sip[$_key1][$_key2]); } } } /* $e=time(); $log=sprintf("Read %d PSTN destinations from cache in %d seconds",$this->destinations_count,$e-$b); syslog(LOG_NOTICE,$log); if ($this->destinations_sip_count) { $e=time(); $log=sprintf("Read %d SIP destinations from cache in %d seconds",$this->destinations_sip_count,$e-$b); syslog(LOG_NOTICE,$log); } */ $this->destinations = $_destinations; $this->destinations_sip = $_destinations_sip; unset($_destinations); unset($_destinations_sip); } else { $this->CacheDestinations(); $this->destinations = $this->_destinations; $this->destinations_sip = $this->_destinations_sip; unset($this->_destinations); unset($this->_destinations_sip); } if (is_array($this->destinations)) { foreach (array_keys($this->destinations) as $_reseller) { foreach ($this->destinations[$_reseller] as $key => $val) { $this->destinations_length[$_reseller][$key] = max(array_map('strlen', array_keys($val))); } } } $c = $this->destinations_count + $this->destinations_sip_count; return $c; } function CacheDestinations() { $this->_destinations = array(); $this->_destinations_sip = array(); $this->destinations_count = 0; $this->destinations_sip_count = 0; $b=time(); $query = "select * from destinations"; if ($this->CDRTool['filter']['aNumber']) { $faNumber=$this->CDRTool['filter']['aNumber']; $query .= sprintf(" where subscriber = '%s' or (subscriber = '' and domain = '' and gateway = '') ", addslashes($faNumber)); } elseif ($this->CDRTool['filter']['domain']) { $fdomain=$this->CDRTool['filter']['domain']; $query .= sprintf(" where domain = '%s' or (subscriber = '' and domain = '' and gateway = '') ", addslashes($fdomain)); } elseif ($this->CDRTool['filter']['gateway']) { $fgateway=$this->CDRTool['filter']['gateway']; $query .= sprintf(" where gateway = '%s' or (subscriber = '' and domain = '' and gateway = '') ",addslashes($fgateway)); } $this->cdrtool->query($query); if (!$this->cdrtool->num_rows()) { $log = "Error: could not find any entries in the destinations table"; syslog(LOG_NOTICE, $log); return 0; } $destinations_cache = "\n"; $destinations_sip_cache = "\n"; $this->destinations_count = 0; $this->destinations_default_count = 0; $this->destinations_gateway_count = 0; $this->destinations_domain_count = 0; $this->destinations_subscriber_count = 0; $j=0; while ($this->cdrtool->next_record()) { $j++; $reseller_id = $this->cdrtool->Record['reseller_id']; $gateway = trim($this->cdrtool->Record['gateway']); $domain = trim($this->cdrtool->Record['domain']); $subscriber = trim($this->cdrtool->Record['subscriber']); $dest_id = trim($this->cdrtool->Record['dest_id']); $region = iso8859_1_to_utf8($this->cdrtool->Record['region']); $name = iso8859_1_to_utf8($this->cdrtool->Record['dest_name']); $name_print = $this->cdrtool->Record['dest_name']." (".$dest_id.")"; if (strstr($dest_id, '@')) { // SIP destination if ($subscriber) { $this->_destinations_sip[$reseller_id][$subscriber][$dest_id] = array( 'name' => $name, 'region' => $region ); $this->destinations_sip_count++; } elseif ($domain) { $this->_destinations_sip[$reseller_id][$domain][$dest_id] = array( 'name' => $name, 'region' => $region ); $this->destinations_sip_count++; } elseif ($gateway) { $this->_destinations_sip[$reseller_id][$gateway][$dest_id] = array( 'name' => $name, 'region' => $region ); $this->destinations_sip_count++; } elseif ($dest_id) { $this->_destinations_sip[$reseller_id]["default"][$dest_id] = array( 'name' => $name, 'region' => $region ); $this->destinations_sip_count++; } } else { // PSTN destination if (!is_numeric($dest_id)) { $log = sprintf( "Error: cannot load non-numeric destination '%s' from row id %d", $dest_id, $this->cdrtool->Record['id'] ); syslog(LOG_NOTICE, $log); continue; } if ($subscriber) { $this->destinations_subscriber_count++; $this->_destinations[$reseller_id][$subscriber][$dest_id]=array( 'name' => $name, 'region' => $region ); $this->destinations_count++; } elseif ($domain) { $this->destinations_domain_count++; $this->_destinations[$reseller_id][$domain][$dest_id]=array( 'name' => $name, 'region' => $region ); $this->destinations_count++; } elseif ($gateway) { $this->destinations_gateway_count++; $this->_destinations[$reseller_id][$gateway][$dest_id]=array( 'name' => $name, 'region'=> $region ); $this->destinations_count++; } elseif ($dest_id) { $this->destinations_default_count++; $this->_destinations[$reseller_id]["default"][$dest_id]=array( 'name' => $name, 'region' => $region ); $this->destinations_count++; } } } $destinations_cache = json_encode($this->_destinations); $destinations_sip_cache = json_encode($this->_destinations_sip); $log = sprintf("PSTN destinations cache size: %0.2f MB", strlen($destinations_cache) / 1024 / 1024); syslog(LOG_NOTICE, $log); if ($destinations_sip_cache) { $log = sprintf("SIP destinations cache size: %0.2f MB", strlen($destinations_sip_cache) / 1024 / 1024); syslog(LOG_NOTICE, $log); } $query = sprintf("select `value` from memcache where `key` = 'destinations'"); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } if ($this->cdrtool->num_rows()) { $query = sprintf( "update memcache set value = '%s' where `key` = 'destinations'", addslashes($destinations_cache) ); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } $log = sprintf( "Cached %d total, %d default, %d gateway, %d domain, %d subscriber destinations", $this->destinations_count, $this->destinations_default_count, $this->destinations_gateway_count, $this->destinations_domain_count, $this->destinations_subscriber_count ); syslog(LOG_NOTICE, $log); } else { $query = sprintf( "insert into memcache (`key`,`value`) values ('destinations','%s')", addslashes($destinations_cache) ); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } $log = sprintf( "Cached %d total, %d default, %d gateway, %d domain, %d subscriber destinations", $this->destinations_count, $this->destinations_default_count, $this->destinations_gateway_count, $this->destinations_domain_count, $this->destinations_subscriber_count ); syslog(LOG_NOTICE, $log); } $query = sprintf("select `value` from memcache where `key` = 'destinations_sip'"); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } if ($this->cdrtool->num_rows()) { $query = sprintf( "update memcache set value = '%s' where `key` = 'destinations_sip'", addslashes($destinations_sip_cache) ); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } $log = sprintf("Cached %d SIP destinations", $this->destinations_sip_count); syslog(LOG_NOTICE, $log); } else { $query = sprintf( "insert into memcache (`key`,`value`) values ('destinations_sip','%s')", addslashes($destinations_sip_cache) ); if (!$this->cdrtool->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno ); print $log; syslog(LOG_NOTICE, $log); return false; } $log = sprintf("Updated cache for %d SIP destinations", $this->destinations_sip_count); syslog(LOG_NOTICE, $log); } return true; } function LoadENUMtlds() { $_ENUMtlds = array(); $query = "select * from billing_enum_tlds"; $this->cdrtool->query($query); while ($this->cdrtool->next_record()) { $_ENUMtlds[trim($this->cdrtool->Record['enum_tld'])] = array( 'discount' => trim($this->cdrtool->Record['discount']), 'e164_regexp' => trim($this->cdrtool->Record['e164_regexp']) ); } $this->ENUMtlds = $_ENUMtlds; $c = count($this->ENUMtlds); return count($this->ENUMtlds); } public function LoadDisconnectCodes() { } function initForm() { } public function searchForm() { } function showTableHeader() { } function showTableHeaderStatistics() { } function showResultsMenu($hide_rows = "", $begin_datetime = '', $end_datetime = '') { global $loginname; if (!$this->export) { print "
"; print "
url_edit\" > Refine searchurl_run\"> Refresh"; $log_query = sprintf( "insert into log (date,login,ip,url,results,rerun,reedit,datasource,reseller_id) values (NOW(),'%s','%s','%s','%s','%s','%s','%s',%d)", addslashes($loginname), addslashes($_SERVER["REMOTE_ADDR"]), addslashes($this->url), addslashes($this->rows), addslashes($this->url_run), addslashes($this->url_edit), addslashes($this->cdr_source), addslashes($this->CDRTool['filter']['reseller']) ); if ($this->cdrtool->query($log_query)) { $this->cdrtool->query("select LAST_INSERT_ID() as lid"); $this->cdrtool->next_record(); $current_log=$this->cdrtool->f('lid'); } if ($this->rows) { print "url_export\" target=_new> Export results to file
"; } else { print "
"; } print "
"; if (!$hide_rows) { print "
"; if ($this->rows == 0) { print "No records found"; } else { print "$this->rows records found"; } if ($begin_datetime && $end_datetime) { printf(" between %s and %s", $begin_datetime, $end_datetime); } print "
"; } } } function showResultsMenuSubscriber($hide_rows = "", $begin_datetime = '', $end_datetime = '') { global $loginname; if (!$this->export) { print "
"; $log_query = sprintf( "insert into log (date,login,ip,url,results,rerun,reedit,datasource,reseller_id) values (NOW(),'%s','%s','%s','%s','%s','%s','%s',%d)", addslashes($loginname), addslashes($_SERVER["REMOTE_ADDR"]), addslashes($this->url), addslashes($this->rows), addslashes($this->url_run), addslashes($this->url_edit), addslashes($this->cdr_source), 0 ); if ($this->cdrtool->query($log_query)) { $this->cdrtool->query("select LAST_INSERT_ID() as lid"); $this->cdrtool->next_record(); $current_log=$this->cdrtool->f('lid'); } if (!$this->CDRTool['filter']['aNumber']) { if ($this->rows) { print " | url_export\" target=_new>Export results to file"; } print " "; } print "
url_edit\">Refine search | url_run\">Refresh | Save a description for this query:
"; if (!$hide_rows) { print "
"; if ($this->rows == 0) { print "No records found"; } else { print "$this->rows records found"; } if ($begin_datetime && $end_datetime) { printf(" between %s and %s",$begin_datetime,$end_datetime); } print "
"; } } } function showDateTimeElements($f) { print " Start Time
"; $f->show_element("begin_date",""); print "
"; print "Time: "; print "
"; $f->show_element("begin_time",""); print "
Stop Time
"; $f->show_element("end_date",""); print "
"; print "Time: "; print "
"; $f->show_element("end_time",""); print "
"; } function showDataSources($f) { global $perm; print " Data Source "; $f->show_element("cdr_source",""); if (count($this->tables) > 0) { print " Table: "; $this->f->show_element("cdr_table",""); } print " "; } function showPagination($next, $maxrows) { $PHP_SELF = $_SERVER["PHP_SELF"]; if (!$this->export) { print " "; } } function show() { } function dump() { } function unNormalize($where = "", $table = "") { if ($this->skipNormalize) { return 0; } if (!$this->normalizedField) { return 0; } // do not allow renormalization for readonly accounts global $perm; if (is_object($perm) && $perm->have_perm('readonly')) return false; if (!$where) $where=" (1=1) "; if (!$table) $table=$this->table; $query=sprintf( "update %s set %s = '0' where %s ", addslashes($table), addslashes($this->normalizedField), $where ); $c=0; if ($this->CDRdb->query($query)) { $c = $this->CDRdb->affected_rows(); $this->reNormalize = true; } return $c; } function buildWhereForUnnormalizedSessions() { $this->whereUnnormalized = sprintf(" %s = '0'",$this->normalizedField); if ($this->stopTimeField) $this->whereUnnormalized .= " and $this->stopTimeField not like '0000-00-00 00:00:00%' "; if ($this->CDRFields['MediaTimeout']) { /* If we use MediaProxy information then eliminate all possible raise conditions 1. Session started and is in progress: AcctStopTime = '0000-00-00 00:00:00' AcctSessionTime = 0 MediaInfo is NULL ConnectInfo_stop is NULL 2. Session closed with a negative response code ([4-6]XX): AcctSessionTime = 0 AcctStopTime != '0000-00-00 00:00:00' MediaInfo is NULL ConnectInfo_stop is NULL 3. Session received a BYE: ConnectInfo_stop is not NULL AcctStopTime != '0000-00-00 00:00:00' 4. Media has timed-out: MediaInfo = 'timeout' ConnectInfo_stop is NULL AcctStopTime != '0000-00-00 00:00:00' 5. MediaProxy update before BYE is received: MediaInfo = '' ConnectInfo_stop is NULL AcctStopTime != '0000-00-00 00:00:00' 6. Mofified 5. for the case where the session received a broken BYE that did not generate a STOP while MediaProxy generated an UPDATE */ $this->whereUnnormalized .= " and (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != '' or (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(AcctStopTime) > 20)) "; } } function getUnNormalized($where = "", $table = "") { if ($this->skipNormalize) { return 0; } if (!$where) $where=" (1=1) "; if (!$table) $table=$this->table; $ReNormalize = $_REQUEST["ReNormalize"]; if ($ReNormalize) $this->unNormalize($where, $table); if (!$this->normalizedField) { return 0; } $this->buildWhereForUnnormalizedSessions(); $query=sprintf( "select count(*) as c from %s where %s and %s", addslashes($table), $where, $this->whereUnnormalized ); if ($this->CDRdb->query($query)) { $this->CDRdb->next_record(); $c = $this->CDRdb->f('c'); } return $c; } function NormalizeCDRS($where = "", $table = "") { $this->missing_destinations=array(); $b=time(); if (!$where) $where=" (1=1) "; if (!$table) $table=$this->table; if ($this->skipNormalize) { return 1; } if (!$this->normalizedField) { return 1; } $lockName = sprintf("%s:%s", $this->cdr_source, $table); if (!$this->getNormalizeLock($lockName)) { //printf("Cannot get obtain lock %s",$lockName); return true; } $this->buildWhereForUnnormalizedSessions(); $this->status['cdr_to_normalize'] = 0; $this->status['normalized'] = 0; $this->status['normalize_failures'] = 0; $query = sprintf( "select count(*) as c from %s where %s and %s", addslashes($table), $where, $this->whereUnnormalized ); if ($this->CDRdb->query($query)) { $this->CDRdb->next_record(); $c=$this->CDRdb->f('c'); } $this->status['cdr_to_normalize']=$c; //$this->status['cdr_to_normalize']=$this->CDRdb->num_rows(); //print "

$query"; if ($this->status['cdr_to_normalize'] > 0) { if ($this->ratingEnabled) { // Load rating tables $this->RatingTables = new RatingTables(); $this->RatingTables->LoadRatingTables(); } } else { return 0; } $this->usageKeysForDeletionFromCache = array(); // For loop to process 1k records each time for ($i = 0; $i <= $this->status['cdr_to_normalize']; $i=$i+1000) { $query = sprintf( "select *, UNIX_TIMESTAMP($this->startTimeField) as timestamp from %s where %s and %s limit 0,1000", addslashes($table), $where, $this->whereUnnormalized ); if (!$this->CDRdb->query($query)) { $log = sprintf( "Database error: %s (%s)\n", $this->CDRdb->Error, $this->CDRdb->Errno ); syslog(LOG_NOTICE, $log); print $log; return false; } while ($this->CDRdb->next_record()) { //$Structure=$this->_readCDRNormalizationFieldsFromDB(); - $Structure=$this->_readCDRFieldsFromDB(''); + $Structure=$this->_readCDRFieldsFromDB(); if ($this->csv_writter) { if (!$this->csv_file_cannot_be_opened) { if (!$this->csv_writter->ready) { if (!$this->csv_writter->open_file($Structure[$this->CDRNormalizationFields['id']])) { $this->csv_file_cannot_be_opened = true; } else { $this->csv_file_ready = true; } } } } $found++; $CDR = new $this->CDR_class($this, $Structure); if ($CDR->normalize("Save", $table)) { $this->status['normalized']++; if ($this->csv_file_ready) { if (!$this->csv_writter->write_cdr($CDR)) { // stop writing future records if we have a failure $this->csv_file_cannot_be_opened = true; } } if ($CDR->broken_rate) { $this->brokenRates[$CDR->DestinationId]++; } } else { $this->status['normalize_failures']++; } if ($this->reNormalize && !$this->usageKeysForDeletionFromCache[$CDR->BillingPartyId]) { $this->usageKeysForDeletionFromCache[$CDR->BillingPartyId]++; } if ($this->status['cdr_to_normalize'] > 1000) { if ($found > $progress*$this->status['cdr_to_normalize']/100) { $progress++; if ($progress%10==0) { print "$progress% "; flush(); } } } } } if ($this->ratingEnabled && is_array($this->brokenRates) && count($this->brokenRates) > 0) { if ($this->rating_settings['reportMissingRates']) { if (count($this->brokenRates)) { foreach (array_keys($this->brokenRates) as $dest) { $missingRatesBodytext=$missingRatesBodytext."\nDestination id $dest (".$this->brokenRates[$dest]." calls)"; } $to = $this->CDRTool['provider']['toEmail']; $from = $this->CDRTool['provider']['fromEmail']; $log = sprintf( "Mailing missing rates for %d destination(s) to %s", count($this->brokenRates), $to ); syslog(LOG_NOTICE, $log); mail($to, "Missing CDRTool rates",$missingRatesBodytext, "From: $from"); } } } if (count($this->missing_destinations)) { $to = $this->CDRTool['provider']['toEmail']; $from = $this->CDRTool['provider']['fromEmail']; $body = ''; foreach ($this->missing_destinations as $_dest) { if (!$seen[$_dest]) { $body .= sprintf("No destination for number %s\n", $_dest); } $seen[$_dest]++; } mail($to, "Missing CDRTool destinations", $body, "From: $from"); } if ($this->status['cdr_to_normalize'] > 0) { $d = time() - $b; $log = sprintf("Normalization done in %d s, memory usage: %0.2f MB", $d, memory_get_usage() / 1024 / 1024); syslog(LOG_NOTICE, $log); } if ($this->csv_file_ready) { $this->csv_writter->close_file(); $this->csv_writter->ready = false; } if (count($this->usageKeysForDeletionFromCache)) { $this->resetQuota(array_keys($this->usageKeysForDeletionFromCache)); } return 1; } function NormalizeNumber($Number, $type = "destination", $subscriber = "", $domain = "", $gateway = "", $CountryCode = "", $ENUMtld = "", $reseller_id = 0) { $this->CSCODE=""; $Number = strtolower(quoted_printable_decode($Number)); if ($pos = strpos($Number, "@")) { // this is a SIP URI $NumberStack['username'] = substr($Number, 0, $pos); if (strlen($NumberStack['username']) < 1) { $NumberStack['username'] = "unknown"; } $NumberStack['domain'] = substr($Number,$pos+1); $NumberStack['delimiter'] = "@"; $pos = strpos($NumberStack['username'], ":"); if ($pos) { $NumberStack['protocol'] = substr($NumberStack['username'], 0, $pos+1); $NumberStack['username'] = substr($NumberStack['username'], $pos+1); } if (preg_match("/^(.*)[=:;]/U", $NumberStack['domain'], $p)){ $NumberStack['domain'] = $p[1]; } } elseif (preg_match("/^([a-z0-9]+:)(.*)$/i", $Number, $m)) { #$oct=preg_split("/\./",$m[2]); $oct = explode(",", $m[2]); if (sizeof($oct) == 4) { // This is a SIP address without username $NumberStack['username'] = ""; $NumberStack['domain'] = $m[2]; } else { // This is a SIP address without domain $NumberStack['username'] = $m[2]; $NumberStack['domain'] = ""; } $NumberStack['protocol'] = $m[1]; $NumberStack['delimiter'] = ""; } else { // This is a simple address like a phone number $NumberStack['protocol'] = ""; $NumberStack['username'] = $Number; $NumberStack['delimiter'] = ""; $NumberStack['domain'] = ""; } if (preg_match("/^(.*)[=:;]/U", $NumberStack['domain'], $p)){ $NumberStack['domain'] = $p[1]; } // Translate the domain if (is_array($this->DATASOURCES[$this->cdr_source]['domainTranslationDestination']) && isset($this->DATASOURCES[$this->cdr_source]['domainTranslationDestination'][$NumberStack['domain']]) ) { $NumberStack['domain'] = $this->DATASOURCES[$this->cdr_source]['domainTranslationDestination'][$NumberStack['domain']]; } if ($type=="destination" && is_numeric($NumberStack['username'])) { // strip custom prefix from destination $usernameLength = strlen($NumberStack['username']); if (is_array($this->CS_CODES)) { foreach ($this->CS_CODES as $strip_prefix) { $prefixLength = strlen($strip_prefix); $restLength = $usernameLength-$prefixLength; if ($restLength > 0 and preg_match("/^$strip_prefix(.*)$/", $NumberStack['username'], $m)) { $NumberStack['username'] = $m[1]; $this->CSCODE = $strip_prefix; break; } } } if (!$CountryCode) $CountryCode = $this->CDRTool['normalize']['defaultCountryCode']; $e164class = $this->E164_class; $E164 = new $e164class( $this->intAccessCode, $this->natAccessCode, $CountryCode, $this->ENUMtlds[$ENUMtld]['e164_regexp'] ); $NumberStack['E164'] = $E164->E164Format($NumberStack['username']); } if ($type=="destination" && $NumberStack['E164']) { // lookup destination id for the E164 number $dst_struct = $this->lookupDestination( $NumberStack['E164'], $subscriber, $domain, $gateway,$reseller_id ); $NumberStack['DestinationId'] = $dst_struct[0]; $NumberStack['destinationName'] = $dst_struct[1]; $NumberStack['NumberPrint'] = "+".$NumberStack['E164']; if (!$ENUMtld) { $NumberStack['Normalized'] = $this->intAccessCode. $NumberStack['E164']. $NumberStack['delimiter']. $NumberStack['domain']; } else { $NumberStack['Normalized'] = $NumberStack['username']. $NumberStack['delimiter']. $NumberStack['domain']; } } else { $dst_struct = $this->lookupDestination( $Number, $subscriber, $domain, $gateway, $reseller_id ); $NumberStack['DestinationId'] = $dst_struct[0]; $NumberStack['destinationName'] = $dst_struct[1]; $NumberStack['NumberPrint'] = $NumberStack['username']. $NumberStack['delimiter']. $NumberStack['domain']; $NumberStack['Normalized'] = $NumberStack['username']. $NumberStack['delimiter']. $NumberStack['domain']; } return $NumberStack; } function lookupDestination($destination, $subscriber = "", $domain = "", $gateway = "", $reseller_id = 0) { if (!$destination) return; if (is_numeric($destination)) { return $this->lookupPSTNDestination($destination, $subscriber, $domain, $gateway, $reseller_id); } else { return $this->lookupSipDestination($destination, $subscriber, $domain, $gateway, $reseller_id); } } function lookupSipDestination($destination = '', $subscriber = '', $domain = '', $gateway = '', $reseller_id = 0) { if ($this->destinations_sip[$reseller_id][$subscriber]) { $destinations_sip = $this->destinations_sip[$reseller_id][$subscriber]; $fCustomer = "subscriber=$subscriber"; } elseif ($this->destinations_sip[$reseller_id][$domain]) { $destinations_sip = $this->destinations_sip[$reseller_id][$domain]; $fCustomer = "domain=$domain"; } elseif ($this->destinations_sip[$reseller_id][$gateway]) { $destinations_sip = $this->destinations_sip[$reseller_id][$gateway]; $fCustomer = "gateway=$gateway"; } elseif ($this->destinations_sip[$reseller_id]['default']) { $destinations_sip = $this->destinations_sip[$reseller_id]['default']; $fCustomer = "default"; } elseif ($this->destinations_sip[0][$subscriber]) { $destinations_sip = $this->destinations_sip[0][$subscriber]; $fCustomer = "subscriber=$subscriber"; } elseif ($this->destinations_sip[0][$domain]) { $destinations_sip = $this->destinations_sip[0][$domain]; $fCustomer = "domain=$domain"; } elseif ($this->destinations_sip[0][$gateway]) { $destinations_sip = $this->destinations_sip[0][$gateway]; $fCustomer = "gateway=$gateway"; } elseif ($this->destinations_sip[0]['default']) { $destinations_sip = $this->destinations_sip[0]['default']; $fCustomer = "default"; } $ret = false; if ($destinations_sip[$destination]) { $ret = array($destination,$destinations_sip[$destination]['name']); } else { list($user,$domain) = explode("@", $destination); if ($domain) { $domain = sprintf("@%s", $domain); if ($destinations_sip[$domain]) { $ret = array($domain, $destinations_sip[$domain]['name']); } } } return $ret; } function lookupPSTNDestination($destination = '', $subscriber = '', $domain = '', $gateway = '', $reseller_id = 0) { if ($this->destinations[$reseller_id][$subscriber]) { $_destinations = $this->destinations[$reseller_id][$subscriber]; $maxLength = $this->destinations_length[$reseller_id][$subscriber]; $fCustomer="subscriber=$subscriber"; } elseif ($this->destinations[$reseller_id][$domain]) { $_destinations = $this->destinations[$reseller_id][$domain]; $maxLength = $this->destinations_length[$reseller_id][$domain]; $fCustomer="domain=$domain"; } elseif ($this->destinations[$reseller_id][$gateway]) { $_destinations = $this->destinations[$reseller_id][$gateway]; $maxLength = $this->destinations_length[$reseller_id][$gateway]; $fCustomer="gateway=$gateway"; } elseif ($this->destinations[$reseller_id]['default']) { $_destinations = $this->destinations[$reseller_id]['default']; $maxLength = $this->destinations_length[$reseller_id]['default']; $fCustomer="default"; } elseif ($this->destinations[0][$subscriber]) { $_destinations = $this->destinations[0][$subscriber]; $maxLength = $this->destinations_length[0][$subscriber]; $fCustomer="subscriber=$subscriber"; } elseif ($this->destinations[0][$domain]) { $_destinations = $this->destinations[0][$domain]; $maxLength = $this->destinations_length[0][$domain]; $fCustomer="domain=$domain"; } elseif ($this->destinations[0][$gateway]) { $_destinations = $this->destinations[0][$gateway]; $maxLength = $this->destinations_length[0][$gateway]; $fCustomer="gateway=$gateway"; } elseif ($this->destinations[0]['default']) { $_destinations = $this->destinations[0]['default']; $maxLength = $this->destinations_length[0]['default']; $fCustomer="default"; } else { $log = sprintf( "Error: cannot find destinations for subscriber='%s', domain ='%s', gateway='%s', reseller='%s'\n", $subscriber, $domain, $gateway, $reseller_id ); syslog(LOG_NOTICE, $log); } if (count($_destinations) > 0) { $length = min(strlen($destination), $maxLength); for ($i = $length; $i > 0; $i--) { $buf = substr($destination, 0, $i); if ($_destinations[$buf]) { return array($buf, $_destinations[$buf]['name']); } } } $log = sprintf( "Error: cannot find destination id for %s of customer = '%s', total destinations = %d\n", $destination, $fCustomer, count($_destinations) ); syslog(LOG_NOTICE, $log); $this->missing_destinations[] = $destination; return false; } function import($file) { } function RadiusRecordRead($fp) { $keepreading=1; while ($keepreading) { $contents = fgets($fp, 8192); if (preg_match("/^$/", $contents)) { $keepreading=0; } else { $record[]=$contents; } } return $record; } function RadiusRecordParse($record) { unset($radiusParsed); if (!is_array($record)) { return 0; } foreach ($record as $line) { $line=trim($line); foreach (array_keys($this->radiusAttributes) as $attribute) { if (preg_match("/$attribute = (.*)$/", $line, $m)) { $value = preg_replace("/\"/", "", trim($m[1])); $radiusParsed[$attribute] = $value; } } } return $radiusParsed; } function getCDRtables() { if (!is_object($this->CDRdb)) return 0; $_tables=$this->CDRdb->table_names(); $t=count($_tables); if ($this->table) $this->tables[]=$this->table; while ($t <> 0) { $_table=$_tables[$t-1]["table_name"]; if ($_table=='radacct') $this->tables[]='radacct'; if (preg_match("/^(\w+)(\d{6})$/", $_table, $m)) { if ($list_t > 24) break; $this->tables[] = $_table; $list_t++; } $t--; } $this->tables=array_unique($this->tables); } function rotateTable($sourceTable, $month, $action) { // create a new table tableYYYYMM and copy data from the main table into it // if no month is supplied, the default is the previous month if (!$month) $month=date('Ym', mktime(0, 0, 0, date("m") - 1, "01", date("Y"))); if (!$sourceTable) $sourceTable = $this->table; if (preg_match("/^(\w+)\d{6}$/", $sourceTable, $m)) { $destinationTable = $m[1].$month; } else { $destinationTable = $sourceTable.$month; } print("rotateTable($sourceTable, $month, $destinationTable)\n"); if ($sourceTable == $destinationTable) { $log = sprintf("Error: cannot copy records to the same table %s.\n", $destinationTable); syslog(LOG_NOTICE, $log); print $log; return 0; } $createTableFile = $this->CDRTool['Path'].$this->createTableFile; if (!$this->createTableFile || !is_readable($createTableFile)) { $log = sprintf("Error: cannot locate mysql creation file\n"); syslog(LOG_NOTICE, $log); print $log; return 0; } $lockFile="/var/lock/CDRTool_".$this->cdr_source."_rotateTable.lock"; $f = fopen($lockFile, "w"); if (flock($f, LOCK_EX + LOCK_NB, $w)) { if ($w) { $log = sprintf("Another CDRTool rotate table is in progress. Aborting.\n"); syslog(LOG_NOTICE, $log); print $log; return 0; } } else { $log = sprintf("Another CDRTool rotate table is in progress. Aborting.\n"); syslog(LOG_NOTICE, $log); print $log; return 0; } $b=time(); if (!preg_match("/^(\d{4})(\d{2})$/", $month, $m)) { print "Error: Month $month must be in YYYYMM format\n"; return 0; } else { if ($m[2] > 12) { print "Error: Month must be in YYYYMM format\n"; return 0; } $lastMonth = $month; $startSQL = $m[1]."-".$m[2]."-01"; $stopSQL =date('Y-m-01', mktime(0, 0, 0, $m[2] + 1, "01", $m[1])); } $query = sprintf( "select count(*) as c from %s where %s >='%s' and %s < '%s'\n", addslashes($sourceTable), addslashes($this->CDRFields['startTime']), addslashes($startSQL), addslashes($this->CDRFields['startTime']), addslashes($stopSQL) ); if ($this->CDRdb->query($query)) { $this->CDRdb->next_record(); $rowsSourceTable = $this->CDRdb->f('c'); $log=sprintf("Source table %s has %d records in month %s\n", $sourceTable, $rowsSourceTable, $month); syslog(LOG_NOTICE, $log); print $log; if (!$rowsSourceTable) return 1; } else { $log = sprintf("Error: %s (%s)\n", $this->table, $this->CDRdb->Error); syslog(LOG_NOTICE, $log); print $log; return 0; } $query = sprintf("select count(*) as c from %s\n", addslashes($destinationTable)); if ($this->CDRdb->query($query)) { $this->CDRdb->next_record(); $rowsDestinationTable = $this->CDRdb->f('c'); $log = sprintf("Destination table %s has %d records\n", $destinationTable, $rowsDestinationTable); syslog(LOG_NOTICE, $log); print $log; if ($rowsDestinationTable != $rowsSourceTable) { $log = sprintf( "Error: source table has %d records and destination table has %d records\n", $rowsSourceTable, $rowsDestinationTable ); syslog(LOG_NOTICE, $log); print $log; } else { $log = sprintf("Tables are in sync\n"); syslog(LOG_NOTICE, $log); print $log; } } else { $log = sprintf("%s (%s)\n", $this->CDRdb->Error, $this->CDRdb->Errno); syslog(LOG_NOTICE, $log); print $log; if ($this->CDRdb->Errno==1146) { $destinationTableTmp = $destinationTable."_tmp"; $query=sprintf("drop table if exists %s", addslashes($destinationTableTmp)); print($query); $this->CDRdb->query($query); if ($query=file_get_contents($createTableFile)) { $query=preg_replace("/CREATE TABLE.*/", "CREATE TABLE $destinationTableTmp (", $query); if (!$this->CDRdb->query($query)) { $log = sprintf("Error creating table %s: %s, %s\n", $destinationTableTmp, $this->CDRdb->Error,$query); syslog(LOG_NOTICE, $log); print $log; return 0; } } else { $log = sprintf("Cannot read file %s\n",$createTableFile); syslog(LOG_NOTICE, $log); print $log; return 0; } // if we reached this point we start to copy records $query = sprintf( "insert into %s select * from %s where %s >='%s' and %s < '%s'", addslashes($destinationTableTmp), addslashes($sourceTable), addslashes($this->CDRFields['startTime']), addslashes($startSQL), addslashes($this->CDRFields['startTime']), addslashes($stopSQL) ); return; if ($this->CDRdb->query($query)) { $e=time(); $d=$e-$b; $rps=0; if ($this->CDRdb->affected_rows() && $d) $rps=$this->CDRdb->affected_rows()/$d; $log = printf("Copied %d CDRs into table %s in %d s @ %.0f rps\n",$this->CDRdb->affected_rows(),$destinationTableTmp,$d,$rps); syslog(LOG_NOTICE,$log); print $log; $query=sprinf("rename table %s to %s", addslashes($destinationTableTmp),addslashes($destinationTableTmp)); if (!$this->CDRdb->query($query)) { printf ("Error renaming table %s to %s: %s\n",$destinationTableTmp,$destinationTable,$this->CDRdb->Error); return 0; } } else { printf ("Error copying records in table %s: %s\n",$destinationTable,$this->CDRdb->Error); return 0; } } } } function purgeTable($sourceTable, $month) { // delete records for a given month with minimal locking of database // this function is useful after archive of CDR data using rotate script $begin = time(); if ($month) { if (!preg_match("/^(\d{4})(\d{2})$/", $month, $m)) { print "Error: Month must be in YYYYMM format\n"; return 0; } else { $beginDate=$m[1]."-".$m[2]."-01"; $endDate=date('Y-m-d', mktime(0, 0, 0, $m[2]+1, '01', $m[1])); } } else if (is_int($this->DATASOURCES[$this->cdr_source]['purgeCDRsAfter'])) { $beginDate="1970-01-01"; $endDate=date('Y-m-d', mktime(0, 0, 0, Date('m'), Date('d')-$this->DATASOURCES[$this->cdr_source]['purgeCDRsAfter'], Date('Y'))); } else { return 0; } if (!$sourceTable) $sourceTable=$this->table; $query = sprintf( "select min(%s) as min,max(%s) as max from %s where %s >= '%s' and %s < '%s' ", addslashes($this->CDRFields['id']), addslashes($this->CDRFields['id']), addslashes($sourceTable), addslashes($this->CDRFields['startTime']), addslashes($beginDate), addslashes($this->CDRFields['startTime']), addslashes($endDate) ); dprint($query); if (!$this->CDRdb->query($query)) { printf("Error: %s", $this->CDRdb->Error); return 0; } $this->CDRdb->next_record(); $min=$this->CDRdb->f('min'); $max=$this->CDRdb->f('max'); if (!$min || !$max) { $log = sprintf("No CDRs found in %s between %s and %s\n", $sourceTable, $beginDate, $endDate); print $log; syslog(LOG_NOTICE, $log); return 0; } $deleted=0; $i=$min; $interval=100; $rows2delete=$max-$min; $found = 0; print "$rows2delete CDRs will be deleted between $min and $max, $interval at a time\n"; while ($i <= $max) { $found=$found+$interval; if ($i + $interval < $max) { $top=$i; } else { $top=$max; } $query = sprintf( "delete low_priority from %s where %s <= '%d' and %s >= '%d'", addslashes($sourceTable), addslashes($this->CDRFields['id']), addslashes($top), addslashes($this->CDRFields['id']), addslashes($min) ); if ($this->CDRdb->query($query)) { $deleted=$deleted+$this->CDRdb->affected_rows(); } else { $log=sprintf("Error: %s (%s)",$this->CDRdb->Error,$this->CDRdb->Errno); syslog(LOG_NOTICE,$log); print $log; return 0; } if ($found > $progress*$rows2delete/100) { $progress++; if ($progress%10==0) { print "$progress% "; flush(); } } print "."; flush(); $i=$i+$interval; } print "\n"; $end = time(); $duration = $end-$begin; $rps=0; if ($deleted && $duration) $rps=$deleted/$duration; $log=sprintf("%s CDRs of month %s deleted from %s in %d s @ %.0f rps\n",$deleted,$month,$sourceTable,$duration,$rps); syslog(LOG_NOTICE,$log); print $log; return 1; } function cacheQuotaUsage($accounts = array()) { if (!$this->quotaEnabled) return true; $saved_keys=0; $failed_keys=0; foreach (array_keys($accounts) as $_key) { $query=sprintf("select id from quota_usage where datasource = '%s' and account = '%s'",addslashes($this->cdr_source),addslashes($_key)); if (!$this->cdrtool->query($query)){ $log=sprintf ("Database error: %s (%s)",$this->cdrtool->Error,$this->cdrtool->Errno); syslog(LOG_NOTICE, $log); print($log); return false; } if ($this->cdrtool->num_rows()) { // sync with quota_usage table $query=sprintf("update quota_usage set calls = calls + %d, duration = duration + %d, cost = cost + '%s', cost_today = cost_today + '%s', traffic = traffic + '%s' where account = '%s' ", addslashes($accounts[$_key]['usage']['calls']), addslashes($accounts[$_key]['usage']['duration']), addslashes($accounts[$_key]['usage']['cost']), addslashes($accounts[$_key]['usage']['cost_today']), addslashes($accounts[$_key]['usage']['traffic']), addslashes($_key) ); if (!$this->cdrtool->query($query)){ $log=sprintf ("Database error: %s (%s)",$this->cdrtool->Error,$this->cdrtool->Errno); syslog(LOG_NOTICE, $log); $failed_keys++; } else { $saved_keys++; } } else { $quota=$this->getQuota($_key); $blocked=$this->getBlockedByQuotaStatus($_key); list($_u,$_d)=explode("@",$_key); $query=sprintf("insert into quota_usage (datasource,account,domain,quota,calls,duration,cost,cost_today,traffic,blocked,reseller_id) values ('%s','%s','%s',%d,%d,'%s','%s','%s','%s','%s',%d) ", addslashes($this->cdr_source), addslashes($_key), addslashes($_d), addslashes($quota), addslashes($accounts[$_key]['usage']['calls']), addslashes($accounts[$_key]['usage']['duration']), addslashes($accounts[$_key]['usage']['cost']), addslashes($accounts[$_key]['usage']['cost_today']), addslashes($accounts[$_key]['usage']['traffic']), intval($blocked), addslashes($this->localDomains[$_d]['reseller']) ); if (!$this->cdrtool->query($query)){ $log=sprintf ("Database error: %s (%s)",$this->cdrtool->Error,$this->cdrtool->Errno); syslog(LOG_NOTICE, $log); $failed_keys++; } else { $saved_keys++; } } } $this->status['cached_keys']['saved_keys'] = $this->status['cached_keys']['saved_keys'] + $saved_keys; $this->status['cached_keys']['failed_keys'] = $this->status['cached_keys']['failed_keys'] + $failed_keys; return 1; } function getNormalizeLock($lockname='') { if (!$locker = new DB_Locker()) { $log=sprintf("Error: cannot init locker database. "); print $log; syslog(LOG_NOTICE, $log); return 0; } if (!$lockname) { $log=sprintf("Error: no lockname provided. "); print $log; syslog(LOG_NOTICE, $log); return 0; } unset($this->lock_connection_id); register_shutdown_function("unLockNormalization",$locker,$lockname); $query=sprintf("SELECT GET_LOCK('%s',0)",addslashes($lockname)); if ($locker->query($query)) { $locker->next_record(); $return = $locker->Record["GET_LOCK('$lockname',0)"]; $query=sprintf("SELECT IS_USED_LOCK('%s')",addslashes($lockname)); if ($locker->query($query)) { $locker->next_record(); $this->lock_connection_id=$locker->Record["IS_USED_LOCK('$lockname')"]; } if ($return == 0) { $log=sprintf("Lock %s already aquired by another process with id %s ",$lockname,$this->lock_connection_id); syslog(LOG_NOTICE, $log); print "$log\n"; return 0; } else { $log=sprintf("Normalize lock id %s aquired for %s ",$this->lock_connection_id,$lockname); syslog(LOG_NOTICE, $log); //print "$log\n"; return 1; } } else { $log = sprintf("Database error: failed to request mysql lock %s (%s)\n", $locker->Error, $locker->Errno); print $log; syslog(LOG_NOTICE, $log); return 0; } } function getQuota($account) { } function getBlockedByQuotaStatus($account) { } function resetQuota($accounts = array()) { if (!$this->quotaEnabled) return true; $_reset_array = array_unique($accounts); foreach ($_reset_array as $_el) { if (strlen($_el)) $_accounts[]=$_el; } $_reset_array = $_accounts; $log=sprintf("Next quota check will rebuild the counters for %s accounts",count($_reset_array)); syslog(LOG_NOTICE,$log ); $query=sprintf("delete from memcache where `key` in ('%s','%s')",addslashes($this->quota_init_flag),addslashes($this->quota_reset_flag)); if (!$this->cdrtool->query($query)) { $log=sprintf ("Database error for query %s: %s (%s)",$query,$this->cdrtool->Error,$this->cdrtool->Errno); print $log; syslog(LOG_NOTICE, $log); return false; } $query=sprintf("insert into memcache (`key`,`value`) values ('%s','%s')",addslashes($this->quota_reset_flag),addslashes(json_encode($_reset_array))); if (!$this->cdrtool->query($query)) { $log=sprintf ("Database error for query %s: %s (%s)",$query,$this->cdrtool->Error,$this->cdrtool->Errno); print $log; syslog(LOG_NOTICE, $log); return false; } $query="delete from quota_usage where account in ("; $t=0; foreach ($_reset_array as $_el) { if ($t) $query.=","; $query.= sprintf("'%s'",addslashes($_el)); $t++; } $query.=")"; if (!$this->cdrtool->query($query)) { $log=sprintf ("Database error: %s (%s)",$this->cdrtool->Error,$this->cdrtool->Errno); syslog(LOG_NOTICE,$log); print $log; return 0; } else { return 1; } } } class CDRS_unknown extends CDRS { function searchForm() { return; } } class E164 { // Class that helps normalization of a telephone number in E164 format // Based on this normalization, CDRTool rating engine decides whether // to consider the session a PSTN destination and rate it according // to the PSTN rating plan function E164($intAccessCode='00', $natAccessCode='0',$CountryCode='',$ENUMtldRegexp="") { $this->regexp_international = "/^".$intAccessCode."([0-9]{5,})\$/"; $this->regexp_national = "/^".$natAccessCode."([0-9]{3,})\$/"; $this->CountryCode = trim($CountryCode); $this->ENUMtldRegexp = trim($ENUMtldRegexp); } function E164Format($Number) { //dprint "E164Format($Number,ENUMtldRegexp=$this->ENUMtldRegexp)"; // This function returns the full E164 format for a PSTN number without leading zero or + // E164 = Country Code + Network Code + Subscriber Number // Example: 31208015100 is an E164 number from Holland (country code 31) // If nothing is returned by this function the session is considered an Internet destination if (preg_match($this->regexp_international,$Number,$m)) { return $m[1]; } else if (preg_match($this->regexp_national,$Number,$m)) { // Add default country code return $this->CountryCode.$m[1]; } else if (strlen($this->ENUMtldRegexp)) { $_regexp="/^".$this->ENUMtldRegexp."\$/"; if (preg_match($_regexp,$Number,$m)) { return $m[1]; } } return false; } } class E164_Europe extends E164 { function E164_Europe ($intAccessCode='00', $natAccessCode='0',$CountryCode='',$ENUMtldRegexp="([1-9][0-9]{7,})") { $this->regexp_international = "/^".$intAccessCode."([1-9][0-9]{5,})\$/"; $this->regexp_national = "/^".$natAccessCode."([1-9][0-9]{3,})\$/"; $this->CountryCode = trim($CountryCode); $this->ENUMtldRegexp = trim($ENUMtldRegexp); } } class E164_US extends E164 { function E164_US($intAccessCode='011', $natAccessCode='[1-9][0-9]{2}',$CountryCode='',$ENUMtldRegexp="([1-9][0-9]{7,})") { $this->regexp_international = "/^".$intAccessCode."([1-9][0-9]{5,})\$/"; $this->regexp_national = "/^".$natAccessCode."([0-9]{3,})\$/"; $this->CountryCode = trim($CountryCode); $this->ENUMtldRegexp = trim($ENUMtldRegexp); } } class CDR { // we need two db descriptors to update a CDR // within same result set var $idField = "RadAcctId"; var $callIdField = "AcctSessionId"; var $usernameField = "UserName"; var $domainField = "Realm"; var $gatewayField = "NASIPAddress"; var $gatewayPortField = "CiscoNASPort"; var $timestampField = "timestamp"; var $portIdField = "NASPortId"; var $portTypeField = "NASPortType"; var $startTimeField = "AcctStartTime"; var $stopTimeField = "AcctStopTime"; var $durationField = "AcctSessionTime"; var $inputTrafficField = "AcctInputOctets"; var $outputTrafficField = "AcctOutputOctets"; var $serviceTypeField = "ServiceType"; var $cNumberField = "CalledStationId"; var $aNumberField = "CallingStationId"; var $disconnectField = "H323DisconnectCause"; var $traceIn = ""; var $traceOut = ""; var $defaultApplicationType = "audio"; var $supportedApplicationTypes = array( 'audio', 'message', 'video', 'chat', 'file-transfer' ); function CDR() { } function NormalizeDisconnect() { $causePrint=$this->CDRS->disconnectCodesDescription[$this->disconnect]." (".$this->disconnect.")"; return $causePrint; } function traceOut () { } function traceIn () { } function show() { } function normalize($save="",$table="") { if (!$table) $table = $this->CDRS->table; if ($this->CDRS->CSCODE && $CarrierInfo = $this->CDRS->CDRTool['normalize']['CS_CODES'][$this->CDRS->CSCODE]) { // We found a carrier so we set the BillingId $this->BillingId = $CarrierInfo[BillingPartyId]; } if ($save) { if (!$this->id) { return 0; } $query =""; $query1 =""; $query2 =""; if ($this->CDRS->normalizedField) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s='1' ",addslashes($this->CDRS->normalizedField)); } if ($this->CDRS->BillingPartyIdField && $this->BillingPartyId) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->BillingPartyIdField),addslashes($this->BillingPartyId)); } if (strlen($this->durationNormalized) && $this->durationNormalized != $this->duration) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s ='%s' ",addslashes($this->CDRS->durationField),addslashes($this->durationNormalized)); $this->duration=$this->durationNormalized; } if ($this->CDRS->DestinationIdField) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->DestinationIdField),addslashes($this->DestinationId)); } if ($this->CDRS->ResellerIdField) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->ResellerIdField),addslashes($this->ResellerId)); } if ($this->usernameNormalized && $this->usernameNormalized!=$this->username) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->usernameField),addslashes($this->usernameNormalized)); } if ($this->aNumberNormalized && $this->aNumberNormalized!=$this->aNumber) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->aNumberField),addslashes($this->aNumberNormalized)); $this->aNumber=$this->aNumberNormalized; } if ($this->CDRS->applicationField && $this->applicationNormalized) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->applicationField), addslashes($this->applicationNormalized)); $this->application=$this->applicationNormalized; } if ($this->CDRS->flowField && $this->flow) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->flowField),addslashes($this->flow)); } if ($this->domainNormalized && $this->domainNormalized != $this->domain) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->domainField),addslashes($this->domainNormalized)); $this->domainNumber=$this->domainNormalized; $this->domain=$this->domainNormalized; } if ($this->cNumberNormalized && $this->cNumberNormalized!=$this->cNumber) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->cNumberField),addslashes($this->cNumberNormalized)); $this->cNumber=$this->cNumberNormalized; } if ($this->CDRS->BillingIdField && $this->BillingId) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->BillingIdField),addslashes($this->BillingId)); } if ($this->CDRS->RemoteAddressField && $this->RemoteAddressNormalized && $this->RemoteAddressNormalized!= $this->RemoteAddress) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->RemoteAddressField),addslashes($this->RemoteAddressNormalized)); } if ($this->CDRS->CanonicalURIField && $this->CanonicalURINormalized && $this->CanonicalURINormalized!= $this->CanonicalURI) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->CanonicalURIField),addslashes($this->CanonicalURINormalized)); } if ($this->stopTimeNormalized) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->stopTimeField),addslashes($this->stopTimeNormalized)); } if ($this->CDRS->ratingEnabled && ($this->duration || $this->application == 'message')) { if ($this->DestinationId) { $Rate = new Rate($this->CDRS->rating_settings, $this->CDRS->cdrtool); if ($this->application == 'message') { $RateDictionary = array( 'callId' => $this->callId, 'timestamp' => $this->timestamp, 'duration' => $this->duration, 'DestinationId' => $this->DestinationId, 'BillingPartyId' => $this->BillingPartyId, 'ResellerId' => $this->ResellerId, 'domain' => $this->domain, 'gateway' => $this->gateway, 'RatingTables' => $this->CDRS->RatingTables, 'aNumber' => $this->aNumber, 'cNumber' => $this->cNumber ); $Rate->calculateMessage($RateDictionary); } else { $RateDictionary = array( 'callId' => $this->callId, 'timestamp' => $this->timestamp, 'duration' => $this->duration, 'DestinationId' => $this->DestinationId, 'inputTraffic' => $this->inputTraffic, 'outputTraffic' => $this->outputTraffic, 'BillingPartyId' => $this->BillingPartyId, 'ResellerId' => $this->ResellerId, 'domain' => $this->domain, 'gateway' => $this->gateway, 'RatingTables' => $this->CDRS->RatingTables, 'aNumber' => $this->aNumber, 'cNumber' => $this->cNumber, 'ENUMtld' => $this->ENUMtld, 'application' => $this->application ); $Rate->calculateAudio($RateDictionary); } $this->pricePrint = $Rate->pricePrint; $this->price = $Rate->price; $this->rateInfo = $Rate->rateInfo; $this->rateDuration = $Rate->duration; if ($Rate->broken_rate) { $this->broken_rate = true; } } else { $this->rateInfo = ''; $this->pricePrint = ''; $this->price = ''; } // strict mysql query fails when price is being set to '' if ($this->pricePrint === null || $this->pricePrint === '') { $this->pricePrint = '0.00'; } if ($this->CDRS->priceField) { if ($updatedFields) $query .= ", "; $updatedFields++; $query .= sprintf( " %s = '%s' ", addslashes($this->CDRS->priceField), addslashes($this->pricePrint) ); if ($this->CDRS->rateField ) { if ($updatedFields) $query .= ", "; $updatedFields++; $query.=sprintf(" %s = '%s' ",addslashes($this->CDRS->rateField),addslashes($this->rateInfo)); } } } $query1 = sprintf( "update %s set %s where %s = '%s'", addslashes($table), $query, addslashes($this->idField), addslashes($this->id)); dprint_sql($query1); if ($updatedFields) { if ($this->CDRS->CDRdb1->query($query1)) { if ($this->CDRS->CDRdb1->affected_rows()) { if ( $this->isBillingPartyLocal() && $table == "radacct".date('Ym')) { // cache usage only if current month $_traffic = ($this->inputTraffic + $this->outputTraffic) / 2; $_usage = array( 'calls' => 1, 'duration' => $this->duration, 'cost' => $this->price, 'cost_today' => $this->price, 'traffic' => $_traffic ); $this->cacheQuotaUsage($_usage); } } else { if (preg_match("/^(\w+)(\d{4})(\d{2})$/", $table, $m)) { $previousTable=$m[1].date('Ym', mktime(0, 0, 0, $m[3]-1, "01", $m[2])); $query2 = sprintf("update %s set %s where %s = '%s'",addslashes($previousTable),$query,addslashes($this->idField),addslashes($this->id)); if ($this->CDRS->CDRdb1->query($query2)) { if ($this->CDRS->CDRdb1->affected_rows()) { if ($this->isBillingPartyLocal() && $previousTable == "radacct".date('Ym')) { // cache usage only if current month $_traffic = ($this->inputTraffic + $this->outputTraffic) / 2; $_usage = array( 'calls' => 1, 'duration' => $this->duration, 'cost' => $this->price, 'cost_today' => $this->price, 'traffic' => $_traffic ); $this->cacheQuotaUsage($_usage); } } } else { $log = sprintf( "Database error: %s (%s)", $this->CDRS->CDRdb1->Error, $this->CDRS->CDRdb1->Errno ); syslog(LOG_NOTICE, $log); print($log); return 0; } } } return 1; } else { $log = sprintf( "Database error for query %s: %s (%s)", $query1, $this->CDRS->CDRdb1->Error, $this->CDRS->CDRdb1->Errno ); syslog(LOG_NOTICE, $log); print($log); return 0; } } } else { if ($this->CDRS->BillingPartyIdField && $CarrierInfo['BillingPartyId']) { $this->domain = $CarrierInfo['BillingDomain']; } if ($this->usernameNormalized && $this->usernameNormalized!=$this->username) { $this->username = $this->usernameNormalized; } if ($this->aNumberNormalized && $this->aNumberNormalized!=$this->aNumber) { $this->aNumber = $this->aNumberNormalized; } if ($this->domainNormalized && $this->domainNormalized != $this->domain) { $this->domainNumber = $this->domainNormalized; } if ($this->cNumberNormalized && $this->cNumberNormalized!=$this->cNumber) { $this->cNumber = $this->cNumberNormalized; } if ($this->CDRS->RemoteAddressField && $this->RemoteAddressNormalized && $this->RemoteAddressNormalized!= $this->RemoteAddress) { $this->RemoteAddress = $this->RemoteAddressNormalized; } } return 1; } function cacheQuotaUsage($usage) { if (!is_array($usage)) return ; $accounts[$this->BillingPartyId]['usage'] = $usage; $this->CDRS->cacheQuotaUsage($accounts); } function isCallerLocal() { return false; } function isCalleeLocal() { return false; } function isBillingPartyLocal() { return false; } function obfuscateCallerId() { global $obfuscateCallerId; if ($obfuscateCallerId) { } } function lookupRateFromNetwork($RateDictionary, $fp) { $this->rateInfo=''; $this->pricePrint=''; $this->price=''; $countEndofLines=0; $cmd="ShowPrice"; foreach (array_keys($RateDictionary) as $key) { $cmd .=" ".$key."=".$RateDictionary[$key]." "; } $this->price = 0; $this->pricePrint = ""; $this->rateInfo = ""; if (fputs($fp,"$cmd\n") !== false) { $i=0; while ($i < 100) { $i++; $line = fgets($fp,1024); if (!$line) { syslog(LOG_NOTICE, "Error: lookupRateFromNetwork(): connection to network socket died"); break; } if (preg_match("/^\n/",$line) || preg_match("/^END/",$line)) { break; } if ($i == 1) { $this->price = trim($line); $this->pricePrint = number_format($this->price, 4); continue; } $this->rateInfo.=$line; } } } function lookupGeoLocation($ip) { if ($_loc=geoip_record_by_name($ip)) { return $_loc['country_name'].'/'.$_loc['city']; } else if ($_loc=geoip_country_name_by_name($ip)) { return $_loc; } else { return ''; } } } function getLocalTime($timezone,$timestamp) { global $CDRTool; if (!$timezone || $timezone == $CDRTool['provider']['timezone']) { return date("Y-m-d H:i:s", $timestamp); } putenv("TZ=$timezone"); $startTimeLocal=date("Y-m-d H:i:s", $timestamp); $timezone=$CDRTool['provider']['timezone']; putenv("TZ=$timezone"); return $startTimeLocal; } function validDay($month,$day,$year) { if (!$month || !$year) { return $day; } while (1) { if (!checkdate($month,$day,$year) && $day) { $day--; next; } else { break; } } return $day; } // include CDRTool modules defined in global.inc if (is_array($CDRToolModules)) { foreach ($CDRToolModules as $module) { $module_filename="cdr_".$module.".php"; include($module_filename); } } function unLockNormalization ($dbid,$lockname) { $query=sprintf("SELECT RELEASE_LOCK('%s')",addslashes($lockname)); $log=sprintf("Unlock %s",$lockname); syslog(LOG_NOTICE, $log); if (!$dbid->query($query)) { $log="Error in unLockNormalization()"; syslog(LOG_NOTICE, $log); } } class SIPonline { function SIPonline ($datasource='',$database='db',$table='location') { global $CDRTool; $expandAll = $_REQUEST['expandAll']; $domain = $_REQUEST['domain']; $this->expandAll = $expandAll; $this->domain = $domain; $this->datasource = $datasource; if (strlen($CDRTool['filter']['domain'])) { $this->allowedDomains=explode(" ",$CDRTool['filter']['domain']); $allowed_domains_sql=""; $j=0; foreach ($this->allowedDomains as $_domain) { if ($j>0) $allowed_domains_sql.=","; $allowed_domains_sql.="'".addslashes($_domain)."'"; $j++; } } $this->locationDB = new $database; $this->locationTable = $table; $this->Registered=array(); $this->countUA=array(); $where = " where (1=1) " ; if ($allowed_domains_sql) { $where.= sprintf("and domain in (%s)",addslashes($allowed_domains_sql)) ; } $query=sprintf("select count(*) as c, domain from %s %s group by domain order by domain ASC", addslashes($this->locationTable), $where ); $this->locationDB->query($query); $this->domains=$this->locationDB->num_rows(); while ($this->locationDB->next_record()) { $this->Registered[$this->locationDB->f('domain')]=$this->locationDB->f('c'); $this->total=$this->total+$this->locationDB->f('c'); } $query=sprintf("select count(*) as c, user_agent from %s %s",addslashes($this->locationTable),$where); if ($this->domain) { $query.=sprintf(" and domain = '%s' ",addslashes($this->domain)); } $query.=" group by user_agent order by c DESC"; $this->locationDB->query($query); while ($this->locationDB->next_record()) { $this->countUA[$this->locationDB->f('user_agent')]=$this->locationDB->f('c'); } } function showHeader() { print ""; print " "; if ($this->domain) { print " "; } else { print " "; } print " "; } function showFooter() { print "
User@Domain SIP UA contact NAT address User Agent Expires Remain Users@ Domain
$this->total users@ $this->domains domains
"; } function showAll() { global $found; $this->showHeader(); foreach (array_keys($this->Registered) as $ld) { $onlines=$this->Registered[$ld]; if ($this->expandAll || ($this->domain && $this->domain==$ld)) { $this->show($ld); } else { $found++; $url = sprintf("%s?datasource=%s&domain=%s", $_SERVER['PHP_SELF'], urlencode($this->datasource), urlencode($ld) ); print " $found $onlines users@ $ld "; if ($this->domain) { print " "; } print " "; } } $this->showfooter(); /* print "

"; $this->showUAdistribution(); */ } function show() { global $found; $query="SELECT *, SEC_TO_TIME(UNIX_TIMESTAMP(expires)-UNIX_TIMESTAMP(NOW())) AS remain FROM location "; if ($this->domain) $query.=sprintf(" where domain = '%s'", addslashes($this->domain)); $query.= " ORDER BY domain ASC, username ASC "; $this->locationDB->query($query); while ($this->locationDB->next_record()) { $found++; $username = $this->locationDB->f('username'); $domain = $this->locationDB->f('domain'); $contact = $this->locationDB->f('contact'); $received = $this->locationDB->f('received'); $user_agent = $this->locationDB->f('user_agent'); $expires = $this->locationDB->f('expires'); $remain = $this->locationDB->f('remain'); $contact_print=substr($contact,4); $c_els=explode(";", $contact); $r_els=explode(";", $received); $transport="UDP"; if ($c_els[1] && preg_match("/transport=(tcp|tls)/i",$c_els[1],$m)) { $transport=strtoupper($m[1]); } $sip_account=$username."@".$domain; print " $found $sip_account $transport $c_els[0] $r_els[0] $user_agent $expires $remain "; $seen[$username]++; $seen[$domain]++; } } function showUAdistribution () { print ""; print " "; print ""; print ""; print ""; print " "; foreach ($this->countUA as $k => $v) { $users=$users+$v; $count++; print " "; print ""; print ""; print ""; print ""; } print " "; print ""; print ""; print ""; print " "; print "
User agentUsers
$count$k$v
$this->domain$users
"; } } class PrepaidHistory { function PrepaidHistory() { $this->db = new DB_cdrtool; } function purge($days=7) { $beforeDate=Date("Y-m-d", time()-$days*3600*24); $query=sprintf("delete from prepaid_history where date < '%s' and action like 'Debit balance%s'",addslashes($beforeDate),'%'); if (!$this->db->query($query)) { $log=sprintf ("Database error for query %s: %s (%s)\n",$query,$this->db->Error,$this->db->Errno); print $log; syslog(LOG_NOTICE,$log); } else { $log=sprintf ("Purged %d records from prepaid history before %s\n",$this->db->affected_rows(),$beforeDate); print $log; syslog(LOG_NOTICE,$log); } } } class CSVWritter { var $csv_directory = '/var/spool/cdrtool/normalize'; var $filename_extension = '.csv'; var $fields = array(); var $ready = false; var $cdr_type = array(); var $lines = 0; function CSVWritter($cdr_source='',$csv_directory='') { if ($cdr_source) { $this->cdr_source = $cdr_source; } else { $this->cdr_source = 'unknown'; } if ($csv_directory) { if (is_dir($csv_directory)) { $this->csv_directory = $csv_directory; } else { $log=sprintf ("CSV writter error: %s is not a directory\n",$csv_directory); syslog(LOG_NOTICE,$log); return false; } } $this->directory=$this->csv_directory."/".date("Ymd"); if (!is_dir($this->directory)) { if (!mkdir($this->directory)) { $log=sprintf ("CSV writter error: cannot create directory %s\n",$this->directory); syslog(LOG_NOTICE,$log); return false; } chmod($this->directory, 0775); } $this->directory_ready = true; } function open_file ($filename_suffix='') { if ($this->ready) return true; if (!$this->directory_ready) return false; if (!$filename_suffix) { $log=sprintf ("CSV writter error: no filename suffix provided\n"); syslog(LOG_NOTICE,$log); return false; } $this->filename_prefix = strtolower($this->cdr_source).'-'.date('YmdHi'); $this->full_path=rtrim($this->directory,'/').'/'.$this->filename_prefix.'-'.$filename_suffix.$this->filename_extension; $this->full_path_tmp=$this->full_path.'.tmp'; if (!$this->fp = fopen($this->full_path_tmp, 'w')) { $log=sprintf ("CSV writter error: cannot open %s for writing\n",$this->full_path_tmp); syslog(LOG_NOTICE,$log); return false; } $this->ready = true; return true; } function close_file () { if (!$this->ready) return false; fclose($this->fp); if (!rename($this->full_path_tmp, $this->full_path)) { $log=sprintf ("CSV writter error: cannot rename %s to %s\n",$this->full_path_tmp,$this->full_path); syslog(LOG_NOTICE,$log); } else { $log=sprintf ("%d normalized CDRs written to %s\n",$this->lines, $this->full_path); syslog(LOG_NOTICE,$log); } } function write_cdr ($CDR) { if (!$this->ready) return false; $line = sprintf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n", $CDR->id, $CDR->callId, $CDR->flow, $CDR->application, $CDR->username, $CDR->CanonicalURI, $CDR->startTime, $CDR->stopTime, $CDR->duration, $CDR->DestinationId, $CDR->BillingPartyId, $CDR->ResellerId, $CDR->price ); if (!fputs($this->fp,$line)) { $this->ready = false; return false; } $this->lines++; return true; } } class MaxRate extends CSVWritter { var $skip_prefixes = array(); var $skip_numbers = array(); var $skip_domains = array(); var $rpid_cache = array(); var $translate_uris = array(); function MaxRate ($cdr_source='', $csv_directory='', $db_subscribers='') { global $MaxRateSettings; // set in global.inc /* $MaxRateSettings= array( 'translate_uris'=> array( '1233@10.0.0.2'=>'+1233', '[1-9][0-9]{4}.*@10.0.0.2'=>'+1233'), 'skip_domains' => array('example.net','10.0.0.1'), 'skip_numbers' => array('1233'), // skip CDRs that has the username part in this array 'skip_prefixes' => array('0031901') // skip CDRs that begin with any of this prefixes ); */ if (is_array($MaxRateSettings['skip_domains'])) { $this->skip_domains=$MaxRateSettings['skip_domains']; } if (is_array($MaxRateSettings['skip_numbers'])) { $this->skip_numbers=$MaxRateSettings['skip_numbers']; } if (is_array($MaxRateSettings['skip_prefixes'])) { $this->skip_prefixes=$MaxRateSettings['skip_prefixes']; } if (is_array($MaxRateSettings['translate_uris'])) { $this->translate_uris=$MaxRateSettings['translate_uris']; } $this->AccountsDB = new $db_subscribers(); $this->CSVWritter($cdr_source, $csv_directory); } function write_cdr($CDR) { if (!$this->ready) return false; # skip if no audio if ($CDR->application != 'audio') return true; # skip if no duration if (!$CDR->duration && ($CDR->disconnect != 200)) return true; # normalize destination if ($CDR->CanonicalURIE164) { $cdr['destination'] = '+'.$CDR->CanonicalURIE164; } else { $cdr['destination'] = $CDR->CanonicalURI; } list($canonical_username, $canonical_domain)=explode("@",$cdr['destination']); # skip domains if ($canonical_domain && in_array($canonical_domain,$this->skip_domains)) return true; # skip numbers if ($canonical_username && in_array($canonical_username,$this->skip_numbers)) return true; # skip prefixes if ($canonical_username && count($this->skip_prefixes)) { foreach ($this->skip_prefixes as $prefix) { if (preg_match("/^$prefix/",$canonical_username)) return true; } } # get RPID if caller is local if ($CDR->flow != 'incoming') { $CallerRPID=$this->getRPIDforAccount($CDR->aNumberPrint); } if ($CallerRPID) { # normalize RPID $cdr['origin'] = '0031'.ltrim($CallerRPID,'0'); } else { # normalize caller id numbers from PSTN gateway to 00format if (preg_match("/^\+?0([1-9][0-9]+)@(.*)$/",$CDR->aNumberPrint,$m)) { $cdr['origin'] = "0031".$m[1]; } else if (preg_match("/^\+?00([1-9][0-9]+)@(.*)$/",$CDR->aNumberPrint,$m)) { $cdr['origin'] = "00".$m[1]; } else if (preg_match("/^([1-9][0-9]+)@(.*)$/",$CDR->aNumberPrint,$m)) { $cdr['origin'] = "0031".$m[1]; } else if (preg_match("/^\+([1-9][0-9]+)@(.*)$/",$CDR->aNumberPrint,$m)) { $cdr['origin'] = "00".$m[1]; } else if (preg_match("/^anonymous@(.*)$/",$CDR->aNumberPrint) && $CDR->SipRPID) { if (preg_match("/^\+?0([1-9][0-9]+)$/",$CDR->SipRPID,$m)) { $cdr['origin'] = "0031".$m[1]; } else if (preg_match("/^\+?00([1-9][0-9]+)$/",$CDR->SipRPID,$m)) { $cdr['origin'] = "00".$m[1]; } else if (preg_match("/^([1-9][0-9]+)@(.*)$/",$CDR->SipRPID,$m)) { $cdr['origin'] = $m[1]; } else if (preg_match("/^\+([1-9][0-9]+)@(.*)$/",$CDR->SipRPID,$m)) { $cdr['origin'] = "00".$m[1]; } else if (preg_match("/^\+?0[0-9]?+@?(.*)?$/",$CDR->SipRPID,$m)) { $cdr['origin'] = "0031123456789"; } else if (preg_match("/^.*[a-zA-Z].*$/",$CDR->SipRPID,$m)) { $cdr['origin'] = "0031123456789"; } else if (preg_match("/^ims.imscore.net.*$/",$CDR->SipRPID,$m)) { $cdr['origin'] = "0031123456789"; } else { $cdr['origin'] = $CDR->SipRPID; } } else { $cdr['origin'] = "0031123456789"; //$cdr['origin'] = $CDR->aNumberPrint; } } # normalize short origins if (preg_match("/^\d{1,3}@.*$/",$cdr['origin'])) { $cdr['origin']='+31000000000'; } # normalize anonymous origins if (preg_match("/^anonymous@.*$/",$cdr['origin'])) { $cdr['origin']='+31000000000'; } #translate destination URIs to desired format if ($CDR->CanonicalURINormalized && count($this->translate_uris)) { foreach ($this->translate_uris as $key => $uri) { if ( preg_match("/^$key/", $CDR->CanonicalURINormalized)) { $cdr['destination']=$uri; break; } } } preg_match("/^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})$/",$CDR->startTime,$m); $cdr['start_date'] = sprintf ("%s/%s/%s %s",$m[3],$m[2],$m[1],$m[4]); $cdr['diversion'] = ''; preg_match("/^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})$/",$CDR->stopTime,$m); $cdr['stop_date'] = sprintf ("%s/%s/%s %s",$m[3],$m[2],$m[1],$m[4]); $cdr['product'] = $this->product; # normalize duration based on billed duration if ($CDR->rateDuration) { $cdr['duration'] = $CDR->rateDuration; } else { $cdr['duration'] = $CDR->duration; } $rate_info = explode("\n", $CDR->rateInfo); for ($i = 0; $i < sizeof($rate_info); ++$i) { //dprint_r($rate_info[$i]); if (strpos($rate_info[$i], "ProfileId:") !== false) { $cdr['profile'] = ltrim(str_replace("ProfileId: ", '', $rate_info[$i])); } } //$cdr['extra']="$CDR->callId"; list($cdr['username'],$cdr['domain'])= explode('@',$CDR->username); $cdr['charge_info'] = sprintf('"%s","%s","%s"',$CDR->price,$cdr['profile'],$CDR->destinationName); if ($CDR->flow == 'on-net') { # RFP 4.2.1 $CalleeRPID=$this->getRPIDforAccount($CDR->CanonicalURI); if ($CalleeRPID) { $cdr['destination'] = '0031'.ltrim($CalleeRPID,'0'); } $cdr['extra'] = $cdr['extra']."$CDR->flow"; } else if ($CDR->flow == 'outgoing') { # RFP 4.2.2 $cdr['extra'] = $cdr['extra']."$CDR->flow"; } else if ($CDR->flow == 'incoming') { # RFP 4.2.3 if ($this->inbound_trunks[$CDR->SourceIP]) { $inbound_trunk = $this->inbound_trunks[$CDR->SourceIP]; } else { $inbound_trunk = 'unknown'; } $cdr['username'] = $canonical_username; $CalleeRPID=$this->getRPIDforAccount($CDR->CanonicalURI); if ($CalleeRPID) { $cdr['destination'] = '0031'.ltrim($CalleeRPID,'0'); } $cdr['extra'] = $cdr['extra']."$CDR->flow"; } else if ($CDR->flow == 'diverted-on-net') { # RFP 4.2.4 $CalleeRPID=$this->getRPIDforAccount($CDR->CanonicalURI); $DiverterRPID=$this->getRPIDforAccount($CDR->username); if ($DiverterRPID) { $diverter_origin = '0031'.ltrim($DiverterRPID,'0'); } else { $diverter_origin = $CDR->username; } if ($CalleeRPID) { $cdr['c_num'] = '0031'.ltrim($CalleeRPID,'0'); } # Set destination to B-Number $cdr['destination'] = $diverter_origin; $cdr['diversion'] = $cdr['c_num']; $cdr['extra'] = $cdr['extra']."incoming-diverted-on-net"; } else if ($CDR->flow == 'diverted-off-net') { # RFP 4.2.5 $DiverterRPID=$this->getRPIDforAccount($CDR->username); if ($DiverterRPID) { $diverter_origin = '0031'.ltrim($DiverterRPID,'0'); } else { $diverter_origin = $CDR->username; } $cdr['c_num'] = $cdr['destination']; # Set destination to B-Number $cdr['destination']=$diverter_origin; $cdr['diversion'] = $cdr['c_num']; $cdr['extra'] = $cdr['extra']."incoming-diverted-off-net"; } else if ($CDR->flow == 'on-net-diverted-on-net') { # RFP 4.2.6 $DiverterRPID=$this->getRPIDforAccount($CDR->username); if ($DiverterRPID) { $diverter_origin = '0031'.ltrim($DiverterRPID,'0'); } else { $diverter_origin = $CDR->username; } $CalleeRPID=$this->getRPIDforAccount($CDR->CanonicalURI); if ($CalleeRPID) { $cdr['c_num'] = '0031'.ltrim($CalleeRPID,'0'); } # Set destination to B-Number $cdr['destination'] = $diverter_origin; $cdr['diversion'] = $cdr['c_num']; $cdr['extra'] = $cdr['extra']."$CDR->flow"; } else if ($CDR->flow == 'on-net-diverted-off-net') { # RFP 4.2.7 $DiverterRPID=$this->getRPIDforAccount($CDR->username); if ($DiverterRPID) { $diverter_origin = '0031'.ltrim($DiverterRPID,'0'); } else { $diverter_origin = $CDR->username; } $cdr['c_num']= $cdr['destination']; # Set destination to B-Number $cdr['destination'] = $diverter_origin; $cdr['diversion'] = $cdr['c_num']; $cdr['extra'] = $cdr['extra']."$CDR->flow"; } $cdr['username'] = preg_replace('/caiw0+|test0+/', "", $cdr['username']); $cdr['origin'] = str_replace('+','00',$cdr['origin']); $cdr['destination'] = str_replace('+','00',$cdr['destination']); $cdr['diversion'] = str_replace('+','00',$cdr['diversion']); $line = sprintf('"%s","%s","%s","%s","%s","%s","%s","%s","%s",%s'."\n", $CDR->callId, $cdr['origin'], $cdr['username'], $cdr['destination'], $cdr['diversion'], $cdr['start_date'], $cdr['stop_date'], $cdr['duration'], $cdr['extra'], $cdr['charge_info'] ); if (!fputs($this->fp,$line)) { $log=sprintf ("CSV writter error: cannot append to file %s\n",$this->full_path_tmp); syslog(LOG_NOTICE,$log); $this->close_file(); $this->ready = false; return false; } $this->lines++; return true; } function getRPIDforAccount($account) { if (!$account) return false; if ($this->rpid_cache[$account]) { return $this->rpid_cache[$account]; } list($username,$domain) = explode('@',$account); $query=sprintf("select * from sip_accounts where username = '%s' and domain = '%s'",addslashes($username),addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log=sprintf ("Database error for query %s: %s (%s)",$query,$this->AccountsDB->Error,$this->AccountsDB->Errno); syslog(LOG_NOTICE,$log); return false; } if ($this->AccountsDB->num_rows()) { $this->AccountsDB->next_record(); $_profile=json_decode(trim($this->AccountsDB->f('profile'))); $this->rpid_cache[$account]=$_profile->rpid; return $_profile->rpid; } else { return false; } } } ?> diff --git a/library/cdr_opensips.php b/library/cdr_opensips.php index ad05678..3c91d64 100644 --- a/library/cdr_opensips.php +++ b/library/cdr_opensips.php @@ -1,5351 +1,5351 @@ 'RadAcctId', 'callId' => 'AcctSessionId', 'duration' => 'AcctSessionTime', 'startTime' => 'AcctStartTime', 'stopTime' => 'AcctStopTime', 'inputTraffic' => 'AcctInputOctets', 'outputTraffic' => 'AcctOutputOctets', 'flow' => 'ServiceType', 'tlscn' => 'AcctAuthentic', 'aNumber' => 'CallingStationId', 'username' => 'UserName', 'domain' => 'Realm', 'cNumber' => 'CalledStationId', 'timestamp' => 'timestamp', 'SipMethod' => 'SipMethod', 'disconnect' => 'SipResponseCode', 'disconnectOrig' => 'AcctTerminateCause', 'SipFromTag' => 'SipFromTag', 'SipToTag' => 'SipToTag', 'RemoteAddress' => 'SipTranslatedRequestURI', 'SipCodec' => 'SipCodecs', 'SipUserAgents' => 'SipUserAgents', 'application' => 'SipApplicationType', 'BillingPartyId' => 'UserName', 'SipRPID' => 'SipRPID', 'SipProxyServer' => 'NASIPAddress', 'MediaProxy' => 'FramedIPAddress', 'gateway' => 'SourceIP', 'SourceIP' => 'SourceIP', 'SourcePort' => 'SourcePort', 'CanonicalURI' => 'CanonicalURI', 'normalized' => 'Normalized', 'rate' => 'Rate', 'price' => 'Price', 'DestinationId' => 'DestinationId', 'ResellerId' => 'BillingId', 'MediaInfo' => 'MediaInfo', 'RTPStatistics' => 'RTPStatistics', 'ENUMtld' => 'ENUMtld', 'UserAgent' => 'UserAgent', 'FromHeader' => 'FromHeader' ); public $CDRNormalizationFields = array( 'id' => 'RadAcctId', 'callId' => 'AcctSessionId', 'username' => 'UserName', 'domain' => 'Realm', 'gateway' => 'SourceIP', 'duration' => 'AcctSessionTime', 'startTime' => 'AcctStartTime', 'stopTime' => 'AcctStopTime', 'inputTraffic' => 'AcctInputOctets', 'outputTraffic' => 'AcctOutputOctets', 'aNumber' => 'CallingStationId', 'cNumber' => 'CalledStationId', 'timestamp' => 'timestamp', 'disconnect' => 'SipResponseCode', 'RemoteAddress' => 'SipTranslatedRequestURI', 'CanonicalURI' => 'CanonicalURI', 'SipRPID' => 'SipRPID', 'SipMethod' => 'SipMethod', 'application' => 'SipApplicationType', 'flow' => 'ServiceType', 'BillingPartyId' => 'UserName', 'ResellerId' => 'BillingId', 'price' => 'Price', 'DestinationId' => 'DestinationId', 'ENUMtld' => 'ENUMtld' ); public $GROUPBY = array( 'UserName' => 'SIP Billing Party', 'CallingStationId' => 'SIP Caller Party', 'SipRPID' => 'SIP Remote Party Id', 'CanonicalURI' => 'SIP Canonical URI', 'DestinationId' => 'SIP Destination Id', 'NASIPAddress' => 'SIP Proxy', 'FramedIPAddress' => 'Media Proxy', 'MediaInfo' => 'Media Information', 'AcctAuthentic' => 'TLS Common Name', 'SourceIP' => 'Source IP', 'Realm' => 'SIP Billing domain', 'UserAgent' => 'User Agent', 'SipCodecs' => 'Codec type', 'SipApplicationType' => 'Application', 'SipResponseCode' => 'SIP status code', 'BillingId' => 'Tech prefix', 'ServiceType' => 'Call Flow', ' ' => '-------------', 'hour' => 'Hour of day', 'DAYOFWEEK' => 'Day of Week', 'DAYOFMONTH' => 'Day of Month', 'DAYOFYEAR' => 'Day of Year', 'BYMONTH' => 'Month', 'BYYEAR' => 'Year' ); public $FormElements = array( "begin_hour","begin_min","begin_month","begin_day","begin_year","begin_datetime","begin_time","end_time", "end_hour","end_min","end_month","end_day","end_year","end_datetime","end_date","begin_date", "call_id","sip_proxy", "media_proxy", "tlscn", "a_number","a_number_comp","UserName","UserName_comp","BillingId", "c_number","c_number_comp","DestinationId","ExcludeDestinations", "NASPortId","Realm","Realms", "SipMethod","SipCodec","SipRPID","UserAgent", "application","sip_status","sip_status_class","gateway", "duration","action","MONTHYEAR", "order_by","order_type","group_by", "cdr_source","trace", "ReNormalize","media_info","cdr_table","maxrowsperpage", "flow" ); public function LoadDisconnectCodes() { $query = "select * from sip_status order by code"; $this->disconnectCodesElements[] = array("label"=>"Any Status","value"=>""); $this->disconnectCodesElements[] = array("label"=>"Undefined (0)","value"=>"0"); $this->disconnectCodesClassElements[] = array("label"=>"Any Status Class","value"=>""); $seen = array(); if ($this->cdrtool->query($query)) { while ($this->cdrtool->next_record()) { $key = $this->cdrtool->f('code'); $value = $this->cdrtool->f('description'); $value_print = $this->cdrtool->f('description')." (".$this->cdrtool->f('code').")"; if (preg_match("/^[^2-6]/", $key)) { continue; } $this->disconnectCodesElements[] = array("label"=>$value_print,"value"=>$key); $this->disconnectCodesDescription[$key] = $value; $class = substr($key, 0, 1); $class_text = substr($key, 0, 1)."XX (".$this->cdrtool->f('code_type').")"; if (!in_array($class, $seen)) { $this->disconnectCodesClassElements[] = array("label"=>$class_text,"value"=>substr($key, 0, 1)); $this->disconnectCodesClassDescription[substr($key, 0, 1)] = $class_text; $seen[] = $class; } $i++; } } } function showTableHeader() { print " "; } function showExportHeader() { global $perm; $fields = array( "id", "StartTime", "StopTime", "BillingParty", "BillingDomain", "CallerParty", "CalledParty", "DestinationId", "DestinationName", "RemoteAddress", "CanonicalURI", "Duration", "Price", "SIPProxy", "Caller KBIn", "Called KBIn", "CallingUserAgent", "CalledUserAgent", "StatusCode", "StatusName", "Codec", "MediaProxy", "TLSCN" ); if ($perm->have_perm("showCallerId")) { array_push($fields, 'PAssertedIdentity'); } printf("%s\n", implode(',', $fields)); } function showTableHeaderSubscriber() { if (!$this->export) { print "
Id Start Time Media/Flow SIP Caller Caller Location Sip Proxy Media Proxy SIP Destination Dur Price KBIn KBOut Status
"; } else { print "id,StartTime,StopTime,SIPBillingParty,SIPBillingDomain,CallerParty,CalledParty,DestinationId,DestinationName,RemoteAddress,CanonicalURI,Duration,Price,SIPProxy,Applications,Caller KBIn,Called KBIn,CallingUserAgent,CalledUserAgent,StatusCode,StatusName,Codec,Application\n"; } } function showTableHeaderStatistics() { $group_byPrint = $this->GROUPBY[$this->group_byOrig]; if (!$this->export) { print "
Id Start Time SIP Caller Caller Location Sip Proxy SIP Destination Duration Price KBIn KBOut
"; } else { print "id,Calls,Seconds,Minutes,Hours,Price,TrafficIn(MB),TrafficOut(MB),Success(%),Success(calls),Failure(%),Failure(calls),$group_byPrint,Description\n"; } } function initForm() { // form els added below must have global vars foreach ($this->FormElements as $_el) { global ${$_el}; ${$_el} = trim($_REQUEST[$_el]); } $action = "search"; if ($this->CDRTool['filter']['gateway']) { $gateway = $this->CDRTool["filter"]["gateway"]; } if ($this->CDRTool['filter']['aNumber']) { $UserName = $this->CDRTool['filter']['aNumber']; } if ($this->CDRTool['filter']['domain']) { $Realm = $this->CDRTool['filter']['domain']; } if (!$maxrowsperpage) { $maxrowsperpage = 15; } $this->f = new form; if (isset($this->CDRTool['dataSourcesAllowed'])) { foreach ($this->CDRTool['dataSourcesAllowed'] as $k => $v) { if ($this->DATASOURCES[$v]['invisible']) continue; $cdr_source_els[]=array("label"=>$this->DATASOURCES[$v]['name'],"value"=>$v); } } if (!$cdr_source) { $cdr_source=$cdr_source_els[0]['value']; } $this->f->add_element( array( "name"=>"cdr_source", "type"=>"select", "options"=>$cdr_source_els, "size"=>"1", "extrahtml"=>"class=span2 onChange=\"document.datasource.submit.disabled = true; location.href = 'callsearch.phtml?cdr_source=' + this.options[this.selectedIndex].value\"", "value"=>"$cdr_source" ) ); $cdr_table_els = array(); foreach ($this->tables as $_table) { if (preg_match("/^.*(\d{6})$/", $_table, $m)) { $cdr_table_els[]=array("label"=>$m[1],"value"=>$_table); } else { $cdr_table_els[]=array("label"=>$_table,"value"=>$_table); } } $this->f->add_element( array( "name"=>"cdr_table", "type"=>"select", "options"=>$cdr_table_els, "size"=>"1", "class"=>"span2", "value"=>$cdr_table, "extrahtml"=>"class=span2" ) ); if ($begin_datetime) { preg_match("/^(\d\d\d\d)-(\d+)-(\d+)\s+(\d\d):(\d\d)/", "$begin_datetime", $parts); $begin_year = date("Y", $begin_datetime); $begin_month = date("m", $begin_datetime); $begin_day = date("d", $begin_datetime); $begin_hour = date("H", $begin_datetime); $begin_min = date("i", $begin_datetime); } else { $begin_day = $_REQUEST["begin_day"]; $begin_month = $_REQUEST["begin_month"]; $begin_year = $_REQUEST["begin_year"]; $begin_hour = $_REQUEST["begin_hour"]; $begin_min = $_REQUEST["begin_min"]; list($begin_hour, $begin_min)=explode(":", $begin_time); list($begin_year, $begin_month, $begin_day)=explode("-", $begin_date); } if ($end_datetime) { preg_match("/^(\d\d\d\d)-(\d+)-(\d+)\s+(\d\d):(\d\d)/", "$end_datetime", $parts); $end_year = date("Y", $end_datetime); $end_month = date("m", $end_datetime); $end_day = date("d", $end_datetime); $end_hour = date("H", $end_datetime); $end_min = date("i", $end_datetime); } else { $end_day = $_REQUEST["end_day"]; $end_month = $_REQUEST["end_month"]; $end_year = $_REQUEST["end_year"]; $end_hour = $_REQUEST["end_hour"]; $end_min = $_REQUEST["end_min"]; list($end_hour, $end_min)=explode(":", $end_time); list($end_year, $end_month, $end_day)=explode("-", $end_date); } // corect last day of the month to be valid day $begin_day = validDay($begin_month, $begin_day, $begin_year); $end_day = validDay($end_month, $end_day, $end_year); $default_year = Date("Y"); $default_month = Date("m"); $default_day = Date("d"); $default_hour = Date("H", time()); if ($default_hour > 1) { $default_hour=$default_hour-1; } $default_hour = preg_replace("/^(\d)$/", "0$1", $default_hour); $default_min = Date("i"); if ($default_min > 10) { $default_min = $default_min-10; $default_min = preg_replace("/^(\d)$/", "0$1", $default_min); } if (!$begin_hour) $begin_hour = $default_hour; if (!$begin_min) $begin_min = $default_min; if (!$begin_day) $begin_day = $default_day; if (!$begin_month) $begin_month = $default_month; if (!$begin_year) $begin_year = $default_year; if (!$end_hour) $end_hour = 23; if (!$end_min) $end_min = 55; if (!$end_day) $end_day = $default_day; if (!$end_month) $end_month = $default_month; if (!$end_year) $end_year = $default_year; $this->f->add_element( array( "name"=>"begin_time", "size"=>"1", "type"=>"text", "extrahtml"=>"id='timepicker1' class=\"input-small\" data-show-meridian='false' data-minute-step='1' data-default-time='$begin_hour:$begin_min'" ) ); $this->f->add_element( array( "name"=>"end_time", "size"=>"1", "type"=>"text", "extrahtml"=>"id='timepicker2' class=\"input-small\" data-show-meridian='false' data-minute-step='1' data-default-time='$end_hour:$end_min'" ) ); $this->f->add_element( array( "name"=>"call_id", "type"=>"text", "size"=>"50", "maxlength"=>"100", "extrahtml"=>"class=span4" ) ); $this->f->add_element( array( "name"=>"UserName", "type"=>"text", "size"=>"25", "maxlength"=>"255", "extrahtml"=>"class=span2" ) ); $this->f->add_element( array( "name"=>"a_number", "type"=>"text", "size"=>"25", "maxlength"=>"255", "extrahtml"=>"class=span2" ) ); $this->f->add_element( array( "name"=>"BillingId", "type"=>"text", "size"=>"25", "maxlength"=>"255", "extra_html"=>"class=span2" ) ); $this->f->add_element( array( "name"=>"c_number", "type"=>"text", "size"=>"25", "maxlength"=>"255", "extrahtml"=>"class=span2" ) ); $this->f->add_element( array( "name" => "sip_status", "type" => "select", "options" => $this->disconnectCodesElements, "size" => "1", "value" => $sip_status, "extrahtml" => "class=span3" ) ); $this->f->add_element( array( "name" => "sip_status_class", "type" => "select", "options" => $this->disconnectCodesClassElements, "size" => "1", "extrahtml" => "class=span3" ) ); if (!$this->CDRTool['filter']['aNumber']) { $durations_els = array( array("label"=>"All calls","value"=>""), array("label"=>"0 seconds","value"=>"zero"), array("label"=>"non 0 seconds","value"=>"nonzero"), array("label"=>"non 0 seconds without price","value"=>"zeroprice"), array("label"=>"less than 5 seconds","value"=>"< 5"), array("label"=>"more than 5 seconds","value"=>"> 5"), array("label"=>"less than 60 seconds","value"=>"< 60"), array("label"=>"more than 2 minutes","value"=>"> 120"), array("label"=>"greater than 1 hour","value"=>"> 3600"), array("label"=>"one hour","value"=>"onehour"), array("label"=>"greater than 5 hours","value"=>"> 18000"), array("label"=>"Un-normalized calls","value"=>"unnormalized"), array("label"=>"Un-normalized calls > 0s","value"=>"unnormalized_duration"), array("label"=>"One way media","value"=>"onewaymedia"), array("label"=>"No media","value"=>"nomedia") ); } else { $durations_els = array( array("label"=>"All calls","value"=>""), array("label"=>"0 seconds call","value"=>"zero"), array("label"=>"Succesfull calls","value"=>"nonzero"), array("label"=>"less than 60 seconds","value"=>"< 60"), array("label"=>"greater than 1 hour","value"=>"> 3600") ); $this->GROUPBY = array( 'UserName' => 'SIP Billing Party', 'CallingStationId' => 'SIP Caller Party', 'DestinationId' => 'SIP Destination Id', 'SipApplicationType' => 'Application', ' ' => '-------------', 'hour' => 'Hour of day', 'DAYOFWEEK' => 'Day of Week', 'DAYOFMONTH' => 'Day of Month', 'DAYOFYEAR' => 'Day of Year', 'BYMONTH' => 'Month', 'BYYEAR' => 'Year' ); } $flow_els = array( array("label"=>"Any Call Flow","value"=>""), array("label"=>"On Net","value"=>"on-net"), array("label"=>"Incoming","value"=>"incoming"), array("label"=>"Outgoing","value"=>"outgoing"), array("label"=>"Transit","value"=>"transit"), array("label"=>"Diverted On Net","value"=>"diverted-on-net"), array("label"=>"Diverted Off Net","value"=>"diverted-off-net"), array("label"=>"On Net Diverted On Net","value"=>"on-net-diverted-on-net"), array("label"=>"On Net Diverted Off Net","value"=>"on-net-diverted-off-net"), array("label"=>"Unknown Flow","value"=>"Sip-Session") ); $this->f->add_element(array( "name"=>"flow", "type"=>"select", "options"=>$flow_els, "value"=>"", "size"=>"1", "extrahtml"=>"class=span3" )); $this->f->add_element(array( "name"=>"duration", "type"=>"select", "options"=>$durations_els, "value"=>"All", "size"=>"1", "extrahtml"=>"class=span3" )); $comp_ops_els = array( array("label"=>"Begins with","value"=>"begin"), array("label"=>"Contains","value"=>"contain"), array("label"=>"Is empty","value"=>"empty"), array("label"=>"Equal","value"=>"equal") ); $this->f->add_element(array( "name"=>"a_number_comp", "type"=>"select", "options"=>$comp_ops_els, "value"=>"begin", "size"=>"1", "extrahtml"=>"class=span2" )); $this->f->add_element(array( "name"=>"c_number_comp", "type"=>"select", "options"=>$comp_ops_els, "value"=>"begin", "size"=>"1", "extrahtml"=>"class=span2" )); $this->f->add_element(array( "name"=>"UserName_comp", "type"=>"select", "options"=>$comp_ops_els, "value"=>"begin", "size"=>"1", "extrahtml"=>"class=span2" )); $this->f->add_element(array( "name"=>"Realm", "type"=>"text", "size"=>"25", "maxlength"=>"25", "extrahtml"=>"class=span2" )); $media_info_els=array( array("label"=>"","value"=>""), array("label"=>"Timeout","value"=>"timeout"), array("label"=>"ICE session","value"=>"ICE session") ); $this->f->add_element(array( "name"=>"media_info", "type"=>"select", "options"=>$media_info_els, "size"=>"1", "value"=>"", "extrahtml"=>"class=span2" )); $this->f->add_element(array("type"=>"submit", "name"=>"submit", "value"=>"Search","extrahtml"=>"class=btn" )); $max_els = array( array("label"=>"5","value"=>"5"), array("label"=>"10","value"=>"10"), array("label"=>"15","value"=>"15"), array("label"=>"25","value"=>"25"), array("label"=>"50","value"=>"50"), array("label"=>"100","value"=>"100"), array("label"=>"500","value"=>"500") ); $this->f->add_element(array( "name"=>"maxrowsperpage", "type"=>"select", "options"=>$max_els, "size"=>"1", "value"=>"25", "extrahtml"=>"class = span2" )); $order_type_els = array( array("label"=>"Descending","value"=>"DESC"), array("label"=>"Ascending","value"=>"ASC") ); $this->f->add_element(array( "name"=>"order_type", "type"=>"select", "options"=>$order_type_els, "size"=>"1", "extrahtml"=>"class=span2" )); $this->f->add_element(array("type"=>"hidden", "name"=>"action", "value"=>$action )); $order_by_els = array(array("label"=>"Id","value"=>"RadAcctId"), array("label"=>"Date","value"=>"AcctStopTime"), array("label"=>"Billing Party","value"=>"UserName"), array("label"=>"Remote Party Id","value"=>"SipRPID"), array("label"=>"Caller Party","value"=>"CallingStationId"), array("label"=>"Destination","value"=>"CalledStationId"), array("label"=>"Duration","value"=>"AcctSessionTime"), array("label"=>"Input traffic","value"=>"AcctInputOctets"), array("label"=>"Output traffic","value"=>"AcctOutputOctets"), array("label"=>"Price","value"=>"Price"), array("label"=>"Failures(%)","value"=>"zeroP"), array("label"=>"Success(%)","value"=>"nonzeroP"), array("label"=>"Group by","value"=>"group_by") ); $group_by_els[] = array("label"=>"", "value"=>""); global $perm; if (!$perm->have_perm("showCallerId")) { $order_by_els = array_filter( $order_by_els, function ($element) { return $element['label'] != "Remote Party Id"; } ); unset($this->GROUPBY['SipRPID']); } foreach ($this->GROUPBY as $k => $v) { $group_by_els[]=array("label"=>$v,"value"=>$k); } $this->f->add_element( array( "name" => "order_by", "type" => "select", "options" => $order_by_els, "value" => $order_by, "size" => "1", "extrahtml" => "class=span3" ) ); $this->f->add_element( array( "name"=> "group_by", "type" => "select", "options" => $group_by_els, "value" => $group_by, "size" => "1", "extrahtml" => "class=span3" ) ); $application_els = array( array( "label" => "Any Application", "value" => "" ), array( "label" => "Audio", "value" => "audio" ), array( "label" => "Video", "value"=>"video" ), array( "label" => "Message", "value" => "message" ), array( "label" => "IM Chat" , "value" => "chat" ), array( "label" => "Audio + Chat" , "value" => "audio=2C chat" ), array( "label" => "File Transfer", "value" => "file-transfer" ) ); $this->f->add_element(array("name"=>"application", "type"=>"select", "options"=>$application_els, "value"=>$application, "size"=>"1", "extrahtml"=>"class=span2" )); $this->f->add_element(array("name"=>"UserAgent", "type"=>"text", "size"=>"25", "maxlength"=>"50", "value"=>$UserAgent, "extrahtml"=>"class=span2" )); $this->f->add_element(array("name"=>"SipCodec", "type"=>"text", "size"=>"10", "maxlength"=>"50", "value"=>$SipCodec, "extrahtml"=>"class=span2" )); $this->f->add_element( array( "name"=>"sip_proxy", "type"=>"text", "size"=>"25", "maxlength"=>"255", "value"=>$sip_proxy, "extrahtml"=>"class=span2" ) ); $this->f->add_element( array( "name"=>"media_proxy", "type"=>"text", "size"=>"25", "maxlength"=>"255", "value"=>$media_proxy, "extrahtml"=>"class=span2" ) ); $this->f->add_element( array( "name"=>"tlscn", "type"=>"text", "size"=>"25", "maxlength"=>"255", "value"=>$tlscn, "extrahtml"=>"class=span2" ) ); $this->f->add_element( array( "name"=>"gateway", "type"=>"text", "size"=>"25", "maxlength"=>"255", "value"=>$gateway, "extrahtml"=>"class=span2" ) ); $this->f->add_element( array( "name"=>"DestinationId", "type"=>"text", "size"=>"10", "extrahtml"=>"class=span3" ) ); $this->f->add_element( array( "name"=>"ExcludeDestinations", "type"=>"text", "size"=>"20", "maxlength"=>"255", "extrahtml"=>"class=span3" ) ); $this->f->load_defaults(); $this->f->add_element( array( "name"=>"begin_date", "size"=>"10", "maxlength"=>"10", "type"=>"text", "value" => "$begin_year-$begin_month-$begin_day", "extrahtml"=>"id='begin_date' data-date-format=\"yyyy-mm-dd\" class=\"span2\"" ) ); $this->f->add_element(array( "name"=>"end_date", "size"=>"1", "type"=>"text", "value"=>"$end_year-$end_month-$end_day", "extrahtml"=>"id='end_date' data-date-format=\"yyyy-mm-dd\" class=\"span2\"" )); } public function searchForm() { global $perm; $this->initForm(); $this->f->start("", "POST", "", "", "datasource"); print "
Calls Seconds Minutes Hours Price TrafficIn(MB) TrafficOut(MB) Success Failure $group_byPrint Description Action
"; $this->showDataSources($this->f); $this->showDateTimeElements($this->f); $ff = array(); // freeze some form els if ($this->CDRTool['filter']['aNumber']) { $ff[]="a_number"; $ff[]="a_number_comp"; $ff[]="UserName"; $ff[]="UserName_comp"; } if ($this->CDRTool['filter']['domain']) { $Realm=$this->CDRTool['filter']['domain']; $ff[]="Realm"; } if ($this->CDRTool['filter']['gateway']) { $gateway=$this->CDRTool['filter']['gateway']; $ff[]="gateway"; } if (count($ff)) { $this->f->freeze($ff); } print " "; print " "; print " "; print " "; print " "; print " "; print " "; print " "; print "

"; $this->f->show_element("submit", ""); $this->f->finish(); print "
"; } function searchFormSubscriber() { global $perm; $this->initForm(); $this->f->start("", "POST", "", "", "datasource"); print " "; $this->showDataSources($this->f); $this->showDateTimeElements($this->f); // freeze some form els if ($this->CDRTool['filter']['aNumber']) { $ff[]="UserName"; } if ($this->CDRTool['filter']['domain']) { $ff[]="Realm"; } if ($this->CDRTool["filter"]["gateway"]) { $ff[]="gateway"; } if (count($ff)) { $this->f->freeze($ff); } print " "; print " "; print " "; print " "; print " "; print "

"; $this->f->show_element("submit", ""); $this->f->finish(); print "
"; } function show() { global $perm; if (!is_object($this->CDRdb)) { $log = sprintf("Error: CDR database is not initalized"); print $log; return false; } foreach ($this->FormElements as $_el) { ${$_el} = trim($_REQUEST[$_el]); } if ($begin_time) { list($begin_hour, $begin_min)=explode(":", $begin_time); } if ($end_time) { list($end_hour, $end_min)=explode(":", $end_time); } if ($begin_date) { list($begin_year, $begin_month, $begin_day)=explode("-", $begin_date); } if ($end_date) { list($end_year, $end_month, $end_day)=explode("-", $end_date); } // overwrite some elements based on user rights if ($this->CDRTool['filter']['gateway']) { $gateway =$this->CDRTool['filter']['gateway']; } if (!$this->export) { if (!$begin_datetime) { $begin_datetime="$begin_year-$begin_month-$begin_day $begin_hour:$begin_min"; $begin_datetime_timestamp = mktime($begin_hour, $begin_min, 0, $begin_month, $begin_day, $begin_year); } else { $begin_datetime_timestamp=$begin_datetime; $begin_datetime = Date("Y-m-d H:i", $begin_datetime); } if (!$end_datetime) { $end_datetime_timestamp = mktime($end_hour, $end_min, 0, $end_month, $end_day, $end_year); $end_datetime="$end_year-$end_month-$end_day $end_hour:$end_min"; } else { $end_datetime_timestamp=$end_datetime; $end_datetime = Date("Y-m-d H:i", $end_datetime); } } else { $begin_datetime = Date("Y-m-d H:i", $begin_datetime); $end_datetime = Date("Y-m-d H:i", $end_datetime); } if (!$order_by || (!$group_by && $order_by == "group_by")) { $order_by=$this->idField; } if (!$cdr_table) { $cdr_table=$this->table; } $this->url = sprintf("?cdr_source=%s&cdr_table=%s", $this->cdr_source, $cdr_table); if ($this->CDRTool['filter']['domain']) { $this->url .= sprintf("&Realms=%s", urlencode($this->CDRTool['filter']['domain'])); $Realms = explode(" ", $this->CDRTool['filter']['domain']); } elseif ($Realms) { $this->url .= sprintf("&Realms=%s", urlencode($Realms)); $Realms = explode(" ", $Realms); } if ($this->CDRTool['filter']['aNumber']) { $this->url .= sprintf("&UserName=%s", urlencode($this->CDRTool['filter']['aNumber'])); } if ($this->CDRTool['filter']['after_date']) { $where .= sprintf(" and %s >= '%s' ", addslashes($this->startTimeField), addslashes($this->CDRTool['filter']['after_date'])); } if ($order_by) { $this->url.=sprintf("&order_by=%s&order_type=%s", addslashes($order_by), addslashes($order_type)); } $this->url.=sprintf("&begin_datetime=%s", urlencode($begin_datetime_timestamp)); $this->url.=sprintf("&end_datetime=%s", urlencode($end_datetime_timestamp)); if (!$call_id && $begin_datetime && $end_datetime) { $where .= sprintf( " (%s >= '%s' and %s < '%s') ", addslashes($this->startTimeField), addslashes($begin_datetime), addslashes($this->startTimeField), addslashes($end_datetime) ); } else { $where .= sprintf(" (%s >= '1970-01-01' ) ", addslashes($this->startTimeField)); } if ($MONTHYEAR) { $where .= sprintf(" and %s like '%s%s' ", addslashes($this->startTimeField), addslashes($MONTHYEAR), '%'); $this->url.= sprintf("&MONTHYEAR=%s", urlencode($MONTHYEAR)); } if ($flow) { $this->url.=sprintf("&flow=%s", urlencode($flow)); $where .= sprintf(" and %s = '%s' ", addslashes($this->flowField), addslashes($flow)); } if ($this->CDRTool['filter']['aNumber']) { // force user to see only CDRS with his a_numbers $where .= sprintf( " and ( %s = '%s' or %s = '%s') ", addslashes($this->usernameField), addslashes($this->CDRTool['filter']['aNumber']), addslashes($this->CanonicalURIField), addslashes($this->CDRTool['filter']['aNumber']) ); $UserName_comp='equal'; $UserName=$this->CDRTool['filter']['aNumber']; } if ($UserName_comp == "empty") { $where .= sprintf(" and %s = ''", addslashes($this->usernameField)); $this->url.=sprintf("&UserName_comp=%s", urlencode($UserName_comp)); } elseif (strlen($UserName) && !$this->CDRTool['filter']['aNumber']) { if (!$UserName_comp) { $UserName_comp='begin'; } if ($UserName_comp=="begin") { $where .= sprintf(" and %s like '%s%s'", addslashes($this->usernameField), addslashes($UserName), '%'); } elseif ($UserName_comp=="contain") { $where .= sprintf(" and %s like '%s%s%s'", addslashes($this->usernameField), '%', addslashes($UserName), '%'); } elseif ($UserName_comp=="equal") { $where .= sprintf(" and %s = '%s'", addslashes($this->usernameField), addslashes($UserName)); } else { $where .= sprintf(" and %s = ''", addslashes($this->usernameField)); } $this->url.= sprintf("&UserName=%s&UserName_comp=%s", urlencode($UserName), $UserName_comp); } $a_number = trim($a_number); if ($a_number_comp == "empty") { $where .= sprintf(" and %s = ''", addslashes($this->aNumberField)); $this->url.=sprintf("&a_number_comp=%s", urlencode($a_number_comp)); } elseif (strlen($a_number)) { $a_number = urldecode($a_number); if (!$a_number_comp) { $a_number_comp = "equal"; } $this->url.=sprintf("&a_number=%s", urlencode($a_number)); if ($a_number_comp=="begin") { $where .= sprintf(" and %s like '%s%s'", addslashes($this->aNumberField), addslashes($a_number), '%'); } elseif ($a_number_comp=="contain") { $where .= sprintf(" and %s like '%s%s%s'", addslashes($this->aNumberField), '%', addslashes($a_number), '%'); } elseif ($a_number_comp=="equal") { $where .= sprintf(" and %s = '%s'", addslashes($this->aNumberField), addslashes($a_number)); } $this->url.=sprintf("&a_number_comp=%s", urlencode($a_number_comp)); } $c_number = trim($c_number); if ($c_number_comp == "empty") { $where .= sprintf(" and %s = ''", addslashes($this->CanonicalURIField)); $this->url.=sprintf("&c_number_comp=%s", urlencode($c_number_comp)); } elseif (strlen($c_number)) { $c_number = urldecode($c_number); if (!$c_number_comp) { $c_number_comp = "begin"; } if (!$c_number_comp || $c_number_comp == "begin") { $where .= sprintf(" and %s like '%s%s'", addslashes($this->CanonicalURIField), addslashes($c_number), '%'); } elseif ($c_number_comp=="contain") { $where .= sprintf(" and %s like '%s%s%s'", addslashes($this->CanonicalURIField), '%', addslashes($c_number), '%'); } elseif ($c_number_comp=="equal") { $where .= sprintf(" and %s = '%s'", addslashes($this->CanonicalURIField), addslashes($c_number)); } $this->url.=sprintf("&c_number=%s&c_number_comp=%s", urlencode($c_number), urlencode($c_number_comp)); } $Realm = trim($Realm); if ($Realms) { $where .= sprintf(" and ("); $count_realms = count($Realms); $j = 1; foreach ($Realms as $realm) { $where .= sprintf(" ( %s like '%%%s' or %s like '%%%s' ) ", $this->domainField, addslashes($realm), $this->CanonicalURIField, addslashes($realm)); if ($j < $count_realms) { $where .= " or "; } $j = $j + 1; } $where .= ") "; } elseif ($Realm) { $Realm = urldecode($Realm); $where .= sprintf(" and %s like '%s' ", $this->domainField, addslashes($Realm)); $this->url.=sprintf("&Realm=%s", urlencode($Realm)); } $BillingId = trim($BillingId); if (preg_match("/^\d+$/", $BillingId) && $this->BillingIdField) { $where .= " and $this->BillingIdField = '".addslashes($BillingId)."'"; $this->url.=sprintf("&BillingId=%s", urlencode($BillingId)); } if ($application) { $where .= " and $this->applicationField like '%".addslashes($application)."%'"; $this->url.=sprintf("&application=%s", urlencode($application)); } if ($DestinationId) { if ($DestinationId=="empty") { $DestinationIdSQL = ""; } else { $DestinationIdSQL = $DestinationId; } $where .= " and $this->DestinationIdField = '".addslashes($DestinationIdSQL)."'"; $this->url.=sprintf("&DestinationId=%s", urlencode($DestinationId)); } if (strlen(trim($ExcludeDestinations))) { $ExcludeDestArray = explode(" ", trim($ExcludeDestinations)); foreach ($ExcludeDestArray as $exclDst) { if (preg_match("/^0+(\d+)$/", $exclDst, $m)) { $exclDest_id = $m[1]; } else { $exclDest_id = $exclDst; } $where .= " and ". $this->CanonicalURIField. " not like '". addslashes(trim($exclDst)). "'"; } $this->url .= sprintf("&ExcludeDestinations=%s", urlencode($ExcludeDestinations)); } $call_id = trim($call_id); if ($call_id) { $call_id = urldecode($call_id); $where .= " and $this->callIdField = '".addslashes($call_id)."'"; $this->url.=sprintf("&call_id=%s", urlencode($call_id)); } if ($sip_proxy) { $sip_proxy = urldecode($sip_proxy); $where .= " and $this->SipProxyServerField = '".addslashes($sip_proxy)."'"; $this->url.=sprintf("&sip_proxy=%s", urlencode($sip_proxy)); } if ($media_proxy) { $media_proxy = urldecode($media_proxy); $where .= " and $this->MediaProxyField = '".addslashes($media_proxy)."'"; $this->url.=sprintf("&media_proxy=%s", urlencode($media_proxy)); } if ($tlscn) { $tlscn = urldecode($tlscn); $where .= " and $this->tlscnField = '".addslashes($tlscn)."'"; $this->url.=sprintf("&tlscn=%s", urlencode($tlscn)); } if ($SipCodec) { $this->url.=sprintf("&SipCodec=%s", urlencode($SipCodec)); if ($SipCodec != "empty") { $where .= " and $this->SipCodecField = '".addslashes($SipCodec)."'"; } else { $where .= " and $this->SipCodecField = ''"; } } if ($SipRPID) { $this->url.=sprintf("&SipRPID=%s", urlencode($SipRPID)); if ($SipRPID != "empty") { $where .= " and $this->SipRPIDField = '".addslashes($SipRPID)."'"; } else { $where .= " and $this->SipRPIDField = ''"; } } if ($UserAgent) { $where .= " and $this->UserAgentField like '%".addslashes($UserAgent)."%'"; $this->url.=sprintf("&UserAgent=%s", urlencode($UserAgent)); } if (strlen($sip_status)) { $where .= " and $this->disconnectField ='".addslashes($sip_status)."'"; $this->url.=sprintf("&sip_status=%s", urlencode($sip_status)); } if ($sip_status_class) { $where .= " and $this->disconnectField like '$sip_status_class%'"; $this->url.=sprintf("&sip_status_class=%s", urlencode($sip_status_class)); } if ($this->CDRTool['filter']["gateway"]) { $gatewayFilter=$this->CDRTool[filter]["gateway"]; $where .= " and $this->gatewayField = '".addslashes($gatewayFilter)."'"; } elseif ($gateway) { $gateway = urldecode($gateway); $where .= " and $this->gatewayField = '".addslashes($gateway)."'"; $this->url.=sprintf("&gateway=%s", urlencode($gateway)); } if ($duration) { if (preg_match("/\d+/", $duration)) { $where .= " and ($this->durationField > 0 and $this->durationField $duration) "; } elseif (preg_match("/onehour/", $duration)) { $where .= " and ($this->durationField < 3610 and $this->durationField > 3530) "; } elseif ($duration == "zero") { $where .= " and $this->durationField = 0"; } elseif ($duration == "zeroprice" && $this->priceField) { $where .= " and $this->durationField > 0 and ($this->priceField = '' or $this->priceField is NULL)"; } elseif ($duration == "nonzero") { $where .= " and $this->durationField > 0"; } elseif ($duration == "onewaymedia") { $where .= " and (($this->inputTrafficField > 0 && $this->outputTrafficField = 0) || ($this->inputTrafficField = 0 && $this->outputTrafficField > 0)) " ; } elseif ($duration == "nomedia") { $where .= " and ($this->inputTrafficField = 0 && $this->outputTrafficField = 0) " ; } $this->url.=sprintf("&duration=%s", urlencode($duration)); } if ($media_info) { $this->url.=sprintf("&media_info=%s", urlencode($media_info)); $where .= sprintf(" and %s = '%s' ", addslashes($this->MediaInfoField), addslashes($media_info)); } $this->url.=sprintf("&maxrowsperpage=%s", addslashes($this->maxrowsperpage)); $url_calls = $this->scriptFile.$this->url."&action=search"; if ($group_by) { $this->url.=sprintf("&group_by=%s", urlencode($group_by)); } $this->url_edit = $this->scriptFile.$this->url."&action=edit"; $this->url_run = $this->scriptFile.$this->url."&action=search"; $this->url_export = $_SERVER["PHP_SELF"].$this->url."&action=search&export=1"; if ($duration == "unnormalized") { $where .= " and $this->normalizedField = '0' "; } if ($duration == "unnormalized_duration") { $where .= " and $this->normalizedField = '0' and $this->durationField > 0 "; } if ($group_by) { $this->group_byOrig=$group_by; if ($group_by=="hour") { $group_by="HOUR(AcctStartTime)"; } elseif (preg_match("/^DAY/", $group_by)) { $group_by="$group_by(AcctStartTime)"; } elseif (preg_match("/BYMONTH/", $group_by)) { $group_by="DATE_FORMAT(AcctStartTime,'%Y-%m')"; } elseif (preg_match("/BYYEAR/", $group_by)) { $group_by="DATE_FORMAT(AcctStartTime,'%Y')"; } elseif ($group_by=="UserAgentType") { $group_by="SUBSTRING_INDEX($this->SipUserAgentsField, ' ', '1')"; } $this->group_by=$group_by; if ($group_by==$this->callIdField) { $having = sprintf(" having count(%s) > 1 ", addslashes($group_by)); } $query= sprintf( " select sum(%s) as duration, SEC_TO_TIME(sum(%s)) as duration_print, count(%s) as calls, %s from %s where %s group by %s %s ", addslashes($this->durationField), addslashes($this->durationField), $group_by, $group_by, addslashes($cdr_table), $where, $group_by, $having ); } else { $query = sprintf("select count(*) as records from %s where ", addslashes($cdr_table)). $where; } dprint_sql($query); if ($this->CDRdb->query($query)) { $this->CDRdb->next_record(); if ($group_by) { $rows = $this->CDRdb->num_rows(); } else { $rows = $this->CDRdb->f('records'); } } else { printf("%s", $this->CDRdb->Error); $rows = 0; } $this->rows=$rows; if ($this->CDRTool['filter']['aNumber']) { $this->showResultsMenuSubscriber('0', $begin_datetime, $end_datetime); } else { $this->showResultsMenu('0', $begin_datetime, $end_datetime); } if (!$this->next) { $i=0; $this->next=0; } else { $i = intval($this->next); } $j=0; $z=0; if ($rows > 0) { if ($call_id && $ReNormalize) { $query = sprintf( " update %s set %s = '0' where %s = '%s' ", addslashes($cdr_table), addslashes($this->normalizedField), addslashes($this->callIdField), addslashes($call_id) ); $this->CDRdb->query($query); } if ($UnNormalizedCalls = $this->getUnNormalized($where, $cdr_table)) { if (!$this->DATASOURCES[$this->cdr_source]['skipNormalizeOnPageLoad']) { if ($UnNormalizedCalls < $this->maxCDRsNormalizeWeb) { $this->NormalizeCDRS($where, $cdr_table); if (!$this->export && $this->status['normalized']) { print "
"; print "  "; printf("%d CDRs normalized. ", $this->status['normalized']); if ($this->status['cached_keys']['saved_keys']) { printf("Quota usage updated for %d accounts. ", $this->status['cached_keys']['saved_keys']); } print "
"; } } } } if ($rows > $this->maxrowsperpage) { $maxrows = $this->maxrowsperpage + $this->next; if ($maxrows > $rows) { $maxrows = $rows; $prev_rows = $maxrows; } } else { $maxrows = $rows; } if ($duration == "unnormalized") { // if display un normalized calls we must substract // the amount of calls normalized above $maxrows=$maxrows-$this->status['normalized']; } if ($group_by) { if ($order_by == "group_by") { $order_by1 = $group_by; } else { if ($order_by == $this->inputTrafficField || $order_by == $this->outputTrafficField || $order_by == $this->durationField || $order_by == $this->priceField || $order_by == "zeroP" || $order_by == "nonzeroP" ) { $order_by1 = $order_by; } else { $order_by1 = "calls"; } } $this->SipMethodField = $this->CDRFields['SipMethod']; $query = " select sum($this->durationField) as $this->durationField, SEC_TO_TIME(sum($this->durationField)) as hours, count($group_by) as calls, $this->SipMethodField, 2*sum($this->inputTrafficField)/1024/1024 as $this->inputTrafficField, 2*sum($this->outputTrafficField)/1024/1024 as $this->outputTrafficField, SUM($this->durationField = '0') as zero, SUM($this->durationField > '0') as nonzero,"; if ($order_by=="zeroP" || $order_by=="nonzeroP") { $query .= " SUM($this->durationField = '0')/count($group_by)*100 as zeroP, SUM($this->durationField > '0')/count($group_by)*100 as nonzeroP,"; } $query .= " sum($this->inputTrafficField)*8*2/1024/sum($this->durationField) as netrate_in, sum($this->outputTrafficField)*8*2/1024/sum($this->durationField) as netrate_out"; if ($this->priceField) { $query .= ", sum($this->priceField) as $this->priceField "; } $_max_rows = intval($this->maxrowsperpage); if (!$_max_rows) { $_max_rows = 10; } /* $query.= " , $group_by as mygroup from $cdr_table where $where group by $group_by $having order by $order_by1 $order_type limit $i,$_max_rows "; */ $query.= sprintf( " , %s as mygroup from %s where %s group by %s %s order by %s %s limit %d, %d ", $group_by, addslashes($cdr_table), $where, $group_by, addslashes($having), addslashes($order_by1), addslashes($order_type), $i, $_max_rows ); dprint_sql($query); $this->CDRdb->query($query); $this->showTableHeaderStatistics(); while ($i<$maxrows) { $found = $i + 1; $this->CDRdb->next_record(); $calls = $this->CDRdb->f('calls'); $seconds = $this->CDRdb->f($this->durationField); $seconds = $this->CDRdb->f($this->durationField); $seconds_print = number_format($this->CDRdb->f($this->durationField), 0); $minutes = number_format($this->CDRdb->f($this->durationField)/60, 0, "", ""); $minutes_print = number_format($this->CDRdb->f($this->durationField)/60, 0); $hours = $this->CDRdb->f('hours'); $AcctInputOctets = number_format($this->CDRdb->f($this->inputTrafficField), 2, ".", ""); $AcctOutputOctets = number_format($this->CDRdb->f($this->outputTrafficField), 2, ".", ""); $NetRateIn = $this->CDRdb->f('netrate_in'); $NetRateOut = $this->CDRdb->f('netrate_out'); $SipMethod = $this->CDRdb->f($this->callTypeField); $AcctTerminateCause = $this->CDRdb->f($this->disconnectField); $mygroup = $this->CDRdb->f('mygroup'); $zero = $this->CDRdb->f('zero'); $nonzero = $this->CDRdb->f('nonzero'); $success = number_format($nonzero/$calls*100, 2, ".", ""); $failure = number_format($zero/$calls*100, 2, ".", ""); $NetworkRateIn = number_format($NetRateIn, 2); $NetworkRateOut = number_format($NetRateOut, 2); $NetworkRate = max($NetworkRateIn, $NetworkRateOut); if ($this->priceField) { $price = $this->CDRdb->f($this->priceField); } $rr = floor($found/2); $mod=$found-$rr*2; if ($mod ==0) { $inout_color="lightgrey"; } else { $inout_color="white"; } $traceValue=""; $mygroup_print = quoted_printable_decode($mygroup); if ($this->group_byOrig==$this->DestinationIdField) { if ($this->CDRTool['filter']['domain'] && $this->destinations[$this->CDRTool['filter']['domain']]) { list($_dst_id, $_dst_name) = $this->getPSTNDestinationId($mygroup, '', $this->CDRTool['filter']['domain']); $description=$_dst_name; } else { $description=$this->destinations[0]["default"][$mygroup]["name"]; //list($_dst_id,$_dst_name)=$this->getPSTNDestinationId($mygroup); //$description=$_dst_name; } if ($mygroup) { $traceValue=$mygroup; } else { $traceValue="empty"; } } elseif ($this->group_byOrig==$this->aNumberField) { // Normalize Called Station Id $N=$this->NormalizeNumber($mygroup); $mygroup_print=$N['username']."@".$N[domain]; $description=""; $traceField="a_number"; $traceValue = urlencode($mygroup); } elseif ($this->group_byOrig==$this->CanonicalURIField) { $traceField="c_number"; $traceValue = urlencode($mygroup); } elseif ($this->group_byOrig==$this->SipProxyServerField) { $traceField="sip_proxy"; $traceValue = urlencode($mygroup); } elseif ($this->group_byOrig==$this->MediaProxyField) { $traceField="media_proxy"; $traceValue = urlencode($mygroup); } elseif ($this->group_byOrig==$this->tlscnField) { $traceField="tlscn"; $traceValue = urlencode($mygroup); } elseif ($this->group_byOrig==$this->SipCodecField) { $traceField="SipCodec"; } elseif (preg_match("/UserAgent/", $this->group_byOrig)) { $traceField="UserAgent"; } elseif (preg_match("/^BY/", $this->group_byOrig)) { $traceField="MONTHYEAR"; } elseif ($this->group_byOrig==$this->callIdField) { $traceField="call_id"; } elseif ($this->group_byOrig=="DAYOFWEEK") { if ($mygroup == "1") { $description="Sunday"; } elseif ($mygroup == "2") { $description="Monday"; } elseif ($mygroup == "3") { $description="Tuesday"; } elseif ($mygroup == "4") { $description="Wednesday"; } elseif ($mygroup == "5") { $description="Thursday"; } elseif ($mygroup == "6") { $description="Friday"; } elseif ($mygroup == "7") { $description="Saturday"; } } elseif ($this->group_byOrig=="DAYOFMONTH") { $description =$this->CDRdb->f('day'); } elseif ($this->group_byOrig=="DAYOFYEAR") { $description =$this->CDRdb->f('day'); } elseif ($this->group_byOrig=="SourceIP") { $traceField="gateway"; } elseif ($this->group_byOrig=="SipResponseCode") { $description =$this->disconnectCodesDescription[$mygroup]; $traceField="sip_status"; } elseif ($this->group_byOrig=="SipApplicationType") { $traceField="application"; } elseif ($this->group_byOrig=="ServiceType") { $traceField="flow"; } else { $description=""; } if (!$traceField) { $traceField = $group_by; } if (!$traceValue) { $traceValue = $mygroup; } if (!$traceValue) { $traceValue=""; $comp_type="empty"; } else { $comp_type="begin"; } $traceValue_enc = urlencode($traceValue); if (!$this->export) { print " $found $calls $seconds_print $minutes_print $hours "; if ($perm->have_perm("showPrice")) { $pricePrint = number_format($price, 4, ".", ""); } else { $pricePrint = 'x.xxx'; } print " $pricePrint $AcctInputOctets $AcctOutputOctets $success% ($nonzero calls) $failure% ($zero calls) $mygroup_print $description "; printf( "Display calls", $url_calls, $traceField, $traceValue_enc, $traceField, $comp_type ); print ""; } else { print "$found,"; print "$calls,"; print "$seconds,"; print "$minutes,"; print "$hours,"; if ($perm->have_perm("showPrice")) { $pricePrint=$price; } else { $pricePrint='x.xxx'; } print "$pricePrint,"; print "$AcctInputOctets,"; print "$AcctOutputOctets,"; print "$success,"; print "$nonzero,"; print "$failure,"; print "$zero,"; print "$mygroup_print,"; print "$description"; print "\n"; } $i++; } if (!$this->export) { print ""; } } else { if (!$this->export) { // printf ("
For more information about each call click on its Id column.
"); } if ($order_by=="zeroP" || $order_by=="nonzeroP") { $order_by="timestamp"; } $_max_rows = intval($this->maxrowsperpage); if (!$_max_rows) { $_max_rows = 10; } $query = sprintf( "select *, UNIX_TIMESTAMP($this->startTimeField) as timestamp from %s where %s order by %s %s limit %d, %d", addslashes($cdr_table), $where, addslashes($order_by), addslashes($order_type), intval($i), $_max_rows ); $this->CDRdb->query($query); if ($this->CDRTool['filter']['aNumber']) { $this->showTableHeaderSubscriber(); } else { if (!$this->export) { $this->showTableHeader(); } else { $this->showExportHeader(); } } while ($i<$maxrows) { global $found; $found = $i + 1; $this->CDRdb->next_record(); - $Structure=$this->_readCDRFieldsFromDB(''); + $Structure = $this->_readCDRFieldsFromDB(); //dprint_r($Structure); $CDR = new $this->CDR_class($this, $Structure); if ($this->CDRTool['filter']['aNumber']) { $CDR->showSubscriber(); } else { if (!$this->export) { $CDR->show(); } else { $CDR->export(); } } $i++; } if (!$this->export) { print ""; } } $this->showPagination($this->next, $maxrows); } } function LoadDomains() { if (!$this->db_subscribers) { $log = printf("Error: Cannot load domains because db_subscribers is not defined in datasource %s", $this->cdr_source); print $log; syslog(LOG_NOTICE, $log); return false; } if (!is_object($this->AccountsDB)) { $log = printf("Error: AccountsDB is not a valid database object"); print $log; syslog(LOG_NOTICE, $log); return false; } if (strlen($this->DATASOURCES[$this->cdr_source]['enableThor'])) { $this->domain_table = "sip_domains"; } else { $this->domain_table = "domain"; } $query = sprintf("select * from %s", $this->domain_table); if ($this->CDRTool['filter']['aNumber']) { $els = explode("@", $this->CDRTool['filter']['aNumber']); $query.= sprintf(" where domain = '%s' ", addslashes($els[1])); } elseif ($this->CDRTool['filter']['domain']) { $fdomain = $this->CDRTool['filter']['domain']; $query.=sprintf(" where domain = '%s' ", addslashes($fdomain)); } if (!$this->AccountsDB->query($query)) { $log=sprintf("Database %s error: %s (%d) %s\n", $this->db_subscribers, $this->AccountsDB->Error, $this->AccountsDB->Errno, $query); print $log; syslog(LOG_NOTICE, $log); return false; } while ($this->AccountsDB->next_record()) { if ($this->AccountsDB->f('domain')) { $this->localDomains[$this->AccountsDB->f('domain')] = array( 'name' => $this->AccountsDB->f('domain'), 'reseller' => intval($this->AccountsDB->f('reseller_id')) ); } } return count($this->localDomains); } function LoadTrustedPeers() { if (!$this->db_subscribers) { $log = printf( "Error: Cannot load trusted peers because db_subscribers is not defined in datasource %s", $this->cdr_source ); print $log; syslog(LOG_NOTICE, $log); return false; } if (!is_object($this->AccountsDB)) { $log = printf("Error: AccountsDB is not a valid database object"); print $log; syslog(LOG_NOTICE, $log); return false; } if (strlen($this->DATASOURCES[$this->cdr_source]['enableThor'])) { $this->trusted_table = "sip_trusted"; } else { $this->trusted_table = "trusted_peers"; } $query=sprintf("select * from %s", addslashes($this->trusted_table)); if (!$this->AccountsDB->query($query)) { $log = sprintf("Database %s error: %s (%d) %s\n", $this->db_subscribers, $this->AccountsDB->Error, $this->AccountsDB->Errno, $query); print $log; syslog(LOG_NOTICE, $log); return false; } while ($this->AccountsDB->next_record()) { if ($this->AccountsDB->f('ip')) { $this->trustedPeers[$this->AccountsDB->f('ip')] = array( 'ip' => $this->AccountsDB->f('ip'), 'reseller' => intval($this->AccountsDB->f('reseller_id')) ); } } return count($this->trustedPeers); } function getQuota($account) { if (!$this->quotaEnabled) { return true; } if (!$account) { return; } if (!is_object($this->AccountsDB)) { $log = printf("Error: AccountsDB is not a valid database object"); print $log; syslog(LOG_NOTICE, $log); return false; } list($username, $domain) = explode("@", $account); if ($this->enableThor) { $query = sprintf("select * from sip_accounts where username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log = sprintf( "Database error for query 1 %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno ); syslog(LOG_NOTICE, $log); return 0; } if ($this->AccountsDB->num_rows()) { $this->AccountsDB->next_record(); $_profile=json_decode(trim($this->AccountsDB->f('profile'))); return $_profile->quota; } else { return 0; } } else { $query=sprintf("select quota from subscriber where username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno ); syslog(LOG_NOTICE, $log); return 0; } if ($this->AccountsDB->num_rows()) { $this->AccountsDB->next_record(); return $this->AccountsDB->f('quota'); } else { return 0; } } } function getBlockedByQuotaStatus($account) { if (!$this->quotaEnabled) { return true; } if (!$account) { return 0; } if (!is_object($this->AccountsDB)) { $log = printf("Error: AccountsDB is not a valid database object"); print $log; syslog(LOG_NOTICE, $log); return false; } list($username, $domain) = explode("@", $account); if ($this->enableThor) { $query = sprintf("select * from sip_accounts where username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log = sprintf( "Database error for query2 %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno ); syslog(LOG_NOTICE, $log); return 0; } if ($this->AccountsDB->num_rows()) { $this->AccountsDB->next_record(); $_profile=json_decode(trim($this->AccountsDB->f('profile'))); if (in_array('quota', $_profile->groups)) { return 1; } else { return 0; } } else { return 0; } } else { $query=sprintf("select CONCAT(username,'@',domain) as account from grp where grp = 'quota' and username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log = sprintf("Database error for query %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno); syslog(LOG_NOTICE, $log); return 0; } if ($this->AccountsDB->num_rows()) { return 1; } else { return 0; } } return 0; } function notifyLastSessions($count='200', $account='') { // send emails with last missed and received sessions to subscribers in group $this->missed_calls_group $lockName = sprintf("%s:notifySessions", $this->cdr_source); if (!$this->getNormalizeLock($lockName)) { return true; } if (strlen($account)) { list($username, $domain) = explode('@', $account); if (!strlen($username) || !strlen($domain)) { return false; } } else { $query=sprintf("select * from memcache where `key` = '%s'", 'notifySessionsLastRun'); $this->cdrtool->query($query); if ($this->cdrtool->num_rows()) { $this->cdrtool->next_record(); $lastRun=$this->cdrtool->f('value'); if (Date('Y-m-d') == $lastRun) { $log=sprintf("Notify sessions script already run for date %s\n", $lastRun); print $log; syslog(LOG_NOTICE, $log); return true; } } } $this->notifySubscribers=array(); require_once('Mail.php'); require_once('Mail/mime.php'); if ($this->enableThor) { $query=sprintf("select * from sip_accounts"); if (strlen($account)) { $query=sprintf("select * from sip_accounts where username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); } if (!$this->AccountsDB->query($query)) { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno ); syslog(LOG_NOTICE, $log); return 0; } if ($this->AccountsDB->num_rows()) { while ($this->AccountsDB->next_record()) { $_profile=json_decode(trim($this->AccountsDB->f('profile'))); if (in_array($this->missed_calls_group, $_profile->groups)) { $this->notifySubscribers[$this->AccountsDB->f('username').'@'.$this->AccountsDB->f('domain')]=array('email'=>$this->AccountsDB->f('email'),'timezone' => $_profile->timezone); } } } else { return 0; } } else { $query = sprintf( "select CONCAT(username,'@',domain) as account,email_address,timezone from grp join subscriber on grp.subscriber_id =subscriber.id where grp = '%s'", addslashes($this->missed_calls_group) ); if (strlen($account)) { $query.= sprintf(" and username = '%s' and domain = '%s' ", $username, $domain); } if (!$this->AccountsDB->query($query)) { $log = sprintf("Database error for query %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno); syslog(LOG_NOTICE, $log); return 0; } if ($this->AccountsDB->num_rows()) { while ($this->AccountsDB->next_record()) { $this->notifySubscribers[$this->AccountsDB->f('account')]=array('email'=>$this->AccountsDB->f('email_address'),'timezone' => $this->AccountsDB->f('timezone')); } } else { return 0; } } if (!count($this->notifySubscribers)) { return 0; } $j = 0; foreach (array_keys($this->notifySubscribers) as $_subscriber) { $j++; $_last_sessions=array(); unset($textBody); unset($htmlBody); $query = sprintf( " SELECT *, UNIX_TIMESTAMP(%s) as timestamp FROM %s where (%s = '%s' or %s = '%s') and %s > DATE_ADD(NOW(), INTERVAL -1 day) order by %s desc limit 200", addslashes($this->startTimeField), addslashes($this->table), addslashes($this->usernameField), addslashes($_subscriber), addslashes($this->CanonicalURIField), addslashes($_subscriber), addslashes($this->startTimeField), addslashes($this->startTimeField) ); if (!$this->CDRdb->query($query)) { $log = sprintf("Database error for query %s: %s (%s)", $query, $this->CDRdb->Error, $this->CDRdb->Errno); syslog(LOG_NOTICE, $log); print $log; return 0; } if (Date('d') == 1) { while ($this->CDRdb->next_record()) { $_last_sessions[] = array( 'duration' => $this->CDRdb->f($this->durationField), 'from' => $this->CDRdb->f($this->aNumberField), 'to' => $this->CDRdb->f($this->cNumberField), 'username' => $this->CDRdb->f($this->usernameField), 'canonical' => $this->CDRdb->f($this->CanonicalURIField), 'date' => getlocaltime($this->notifySubscribers[$_subscriber]['timezone'], $this->CDRdb->f('timestamp')) ); } if (preg_match("/^(\w+)(\d{4})(\d{2})$/", $this->table, $m)) { $previousTable=$m[1].date('Ym', mktime(0, 0, 0, $m[3]-1, "01", $m[2])); $query = sprintf( " SELECT *, UNIX_TIMESTAMP(%s) as timestamp FROM %s where %s = '%s' and %s > DATE_ADD(NOW(), INTERVAL -1 day) order by %s desc limit 200 ", addslashes($this->startTimeField), addslashes($previousTable), addslashes($this->CanonicalURIField), addslashes($_subscriber), addslashes($this->startTimeField), addslashes($this->startTimeField) ); if (!$this->CDRdb->query($query)) { $log = sprintf("Database error for query %s: %s (%s)", $query, $this->CDRdb->Error, $this->CDRdb->Errno); syslog(LOG_NOTICE, $log); print $log; return 0; } while ($this->CDRdb->next_record()) { $_last_sessions[] = array( 'duration' => $this->CDRdb->f($this->durationField), 'from' => $this->CDRdb->f($this->aNumberField), 'to' => $this->CDRdb->f($this->cNumberField), 'username' => $this->CDRdb->f($this->usernameField), 'canonical' => $this->CDRdb->f($this->CanonicalURIField), 'date' => getlocaltime($this->notifySubscribers[$_subscriber]['timezone'], $this->CDRdb->f('timestamp')) ); } } } else { while ($this->CDRdb->next_record()) { $_last_sessions[] = array( 'duration' => $this->CDRdb->f($this->durationField), 'from' => $this->CDRdb->f($this->aNumberField), 'to' => $this->CDRdb->f($this->cNumberField), 'username' => $this->CDRdb->f($this->usernameField), 'canonical' => $this->CDRdb->f($this->CanonicalURIField), 'date' => getlocaltime($this->notifySubscribers[$_subscriber]['timezone'], $this->CDRdb->f('timestamp')) ); } } if (!count($_last_sessions)) { continue; } $sessions=array( 'missed' => array(), 'received' => array(), 'diverted' => array() ); $have_sessions=0; foreach ($_last_sessions as $_s) { if ($_s['duration'] == 0 && $_s['canonical'] == $_subscriber) { $sessions['missed'][]=$_s; $have_sessions++; continue; } if ($_s['duration'] > 0 && $_s['canonical'] == $_subscriber) { $sessions['received'][]=$_s; $have_sessions++; continue; } if ($_s['from'] != $_subscriber && $_s['canonical'] != $_subscriber) { $sessions['diverted'][]=$_s; $have_sessions++; continue; } } if (!$have_sessions) { continue; } if (count($sessions['missed'])) { // missed sessions $textBody .= sprintf( " Missed sessions\n\n Id,Date,From,Duration\n " ); $htmlBody .= sprintf("

Missed Calls

"); $i=0; foreach ($sessions['missed'] as $_session) { $i++; if ($i >= $count) { break; } $htmlBody .= sprintf( " ", $i, $_session['date'], $_session['from'], $_session['from'] ); $txtBody.=sprintf( "%s,%s,%s,%s,%s\n", $i, $_session['date'], $_session['from'], $_session['to'] ); } $htmlBody.="
Date and Time Caller
%s %s sip:%s
"; } if (count($sessions['diverted'])) { // diverted sessions $textBody .= sprintf("Diverted Calls\n\n Id,Date,From,Diverted to\n "); $htmlBody .= sprintf("

Diverted Calls

"); $i=0; foreach ($sessions['diverted'] as $_session) { $i++; if ($i >= $count) break; $htmlBody.=sprintf( " ", $i, $_session['date'], $_session['from'], $_session['from'], $_session['canonical'] ); $txtBody .= sprintf( "%s,%s,%s,%s\n", $i, $_session['date'], $_session['from'], $_session['canonical'] ); } $htmlBody.="
Date and Time Caller Diverted to
%s %s sip:%s %s
"; } if (count($sessions['received'])) { // received sessions $textBody .= sprintf("Received Calls\n\nId,Date,From,Duration\n"); $htmlBody .= sprintf( "

Received Calls

" ); $i=1; foreach ($sessions['received'] as $_session) { if ($i >= $count) { break; } $htmlBody .= sprintf( "", $i, $_session['date'], $_session['from'], $_session['from'], $_session['duration'] ); $txtBody .= sprintf( "%s,%s,%s,%s\n", $i, $_session['date'], $_session['from'], $_session['duration'] ); $i++; } $htmlBody.="
Date and Time Caller Duration
%s%ssip:%s%s
"; } $htmlBody.="

This is an automatically generated message, do not reply."; $txtBody.="\nThis is an automatically generated message, do not reply.\n"; $crlf = "\n"; $hdrs = array( 'From'=> $this->CDRTool['provider']['fromEmail'], 'Subject' => sprintf("Incoming Calls for %s on %s", $_subscriber, date('Y-m-d')) ); $mime = new Mail_mime($crlf); $mime->setTXTBody($textBody); $mime->setHTMLBody($htmlBody); $body = $mime->get(); $hdrs = $mime->headers($hdrs); $mail =& Mail::factory('mail'); $mail->send($this->notifySubscribers[$_subscriber]['email'], $hdrs, $body); $log=sprintf( "Notify %s at %s with last %d sessions\n", $_subscriber, $this->notifySubscribers[$_subscriber]['email'], count($_last_sessions) ); print $log; syslog(LOG_NOTICE, $log); } $query = sprintf("update memcache set `value` = '%s' where `key` = '%s'", Date('Y-m-d'), 'notifySessionsLastRun'); if (!$this->cdrtool->query($query)) { $log = sprintf("Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno); print $log; syslog(LOG_NOTICE, $log); return false; } if (!$this->cdrtool->affected_rows()) { $query=sprintf("insert into memcache (`value`,`key`) values ('%s','%s')", Date('Y-m-d'), 'notifySessionsLastRun'); if (!$this->cdrtool->query($query)) { if ($this->cdrtool->Errno != 1062) { $log=sprintf("Database error for query %s: %s (%s)", $query, $this->cdrtool->Error, $this->cdrtool->Errno); print $log; syslog(LOG_NOTICE, $log); return false; } } } } function getCallerId($account) { if (!$account) { return null; } if ($this->callerid_cache[$account]) { return $this->callerid_cache[$account]; } list($username, $domain) = explode('@', $account); if ($this->enableThor) { $query=sprintf("select * from sip_accounts where username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log=sprintf("Database error for query %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno); syslog(LOG_NOTICE, $log); return null; } if ($this->AccountsDB->num_rows()) { $this->AccountsDB->next_record(); $_profile=json_decode(trim($this->AccountsDB->f('profile'))); $this->callerid_cache[$account]=$_profile->rpid; return $_profile->rpid; } } else { $query=sprintf("select rpid from subscriber where username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log=sprintf("Database error for query %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno); syslog(LOG_NOTICE, $log); return null; } if ($this->AccountsDB->num_rows()) { $this->AccountsDB->next_record(); $rpid = $this->AccountsDB->f('rpid'); $this->callerid_cache[$account]=$rpid; return $rpid; } } return null; } function rate_on_net_enabled($username, $domain) { if ($this->enableThor) { $query=sprintf("select * from sip_accounts where username = '%s' and domain = '%s'", addslashes($username), addslashes($domain)); if (!$this->AccountsDB->query($query)) { $log=sprintf("Database error for query %s: %s (%s)", $query, $this->AccountsDB->Error, $this->AccountsDB->Errno); syslog(LOG_NOTICE, $log); return false; } if ($this->AccountsDB->num_rows()) { while ($this->AccountsDB->next_record()) { $_profile = json_decode(trim($this->AccountsDB->f('profile'))); if (in_array($this->rate_on_net_group, $_profile->groups)) { return true; } } } } return false; } } class CDR_opensips extends CDR { function __construct($parent, $CDRfields) { $this->CDRS = $parent; $this->cdr_source = $this->CDRS->cdr_source; foreach (array_keys($this->CDRS->CDRFields) as $field) { $this->$field = $CDRfields[$this->CDRS->CDRFields[$field]]; } if ($this->CanonicalURI) { $this->CanonicalURI = quoted_printable_decode($this->CanonicalURI); } if ($this->RemoteAddress) { $this->RemoteAddress = quoted_printable_decode($this->RemoteAddress); } if ($this->BillingPartyId) { $this->BillingPartyId = quoted_printable_decode($this->BillingPartyId); } if ($this->aNumber) { $this->aNumber = quoted_printable_decode($this->aNumber); } if ($this->cNumber) { $this->cNumber = quoted_printable_decode($this->cNumber); } if ($this->SipRPID) { $this->SipRPID = quoted_printable_decode($this->SipRPID); } if (!$this->application && $this->SipMethod) { $_method=strtolower($this->SipMethod); if ($_method == 'message') { $this->application = 'message'; $this->stopTimeNormalized=$this->startTime; } else { $this->application = 'audio'; } } if ($this->application == 'message') { $this->stopTimeNormalized=$this->startTime; } $this->application=strtolower($this->application); $this->application_print=quoted_printable_decode($this->application); $this->FromHeaderPrint = quoted_printable_decode($this->FromHeader); if (strstr($this->FromHeaderPrint, ';')) { $_els=explode(";", $this->FromHeaderPrint); $this->FromHeaderPrint = $_els[0]; } $this->FromHeaderPrint = htmlentities($this->FromHeaderPrint); $this->UserAgentPrint = quoted_printable_decode($this->UserAgent); $app_prefix = preg_replace('/[.].*$/', '', $this->application); if (!in_array($app_prefix, $this->supportedApplicationTypes)) { $log=sprintf("Changing application from %s to %s\n", $this->application, $this->defaultApplicationType); syslog(LOG_NOTICE, $log); $this->application = $this->defaultApplicationType; } //$this->applicationNormalized=$this->application; if ($this->aNumber) { $NormalizedNumber = $this->CDRS->NormalizeNumber($this->aNumber, "source"); $this->aNumberPrint = $NormalizedNumber['NumberPrint']; $this->aNumberNormalized = $NormalizedNumber['Normalized']; $this->aNumberUsername = $NormalizedNumber['username']; $this->aNumberDomain = $NormalizedNumber['domain']; } if (!$this->BillingPartyId || $this->BillingPartyId == 'n/a') { $this->BillingPartyId=$this->aNumberPrint; } $this->ResellerId=0; // calculate reseller $_billing_party_els=explode("@", $this->BillingPartyId); if ($this->isBillingPartyLocal()) { $this->ResellerId = $this->CDRS->localDomains[$_billing_party_els[1]]['reseller']; } else { if (!strlen($_billing_party_els[0])) { $this->BillingPartyId=$_billing_party_els[1]; } if (count($_billing_party_els) == 2) { if (!$this->domain) { $this->domain=$_billing_party_els[1]; } if ($this->CDRS->localDomains[$_billing_party_els[1]]['reseller']) { $this->ResellerId = $this->CDRS->localDomains[$_billing_party_els[1]]['reseller']; } elseif ($this->CDRS->trustedPeers[$_billing_party_els[1]]['reseller']) { $this->ResellerId = $this->CDRS->trustedPeers[$_billing_party_els[1]]['reseller']; } } elseif (count($_billing_party_els)==1) { $this->ResellerId=$this->CDRS->trustedPeers[$_billing_party_els[0]]['reseller']; } } if (!strlen($this->ResellerId)) { $this->ResellerId = 0; } $this->BillingPartyId=strtolower($this->BillingPartyId); $this->BillingPartyIdPrint = $this->BillingPartyId; $this->domainNormalized = $this->domain; if (is_array($this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation_SourceIP']) && isset($this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation_SourceIP'][$this->SourceIP]) && strlen($this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation_SourceIP'][$this->SourceIP]) ) { $this->domainNormalized=$this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation_SourceIP'][$this->SourceIP]; } elseif (is_array($this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation']) && isset($this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation'][$this->domain]) && strlen($this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation'][$this->domain]) ) { $this->domainNormalized=$this->CDRS->DATASOURCES[$this->cdr_source]['domainTranslation'][$this->domain]; } $this->domainNormalized=strtolower($this->domainNormalized); $this->RemoteAddressPrint=quoted_printable_decode($this->RemoteAddress); $_timestamp_stop=$this->timestamp+$this->duration; $this->dayofweek = date("w", $this->timestamp); $this->hourofday = date("G", $this->timestamp); $this->dayofyear = date("Y-m-d", $this->timestamp); // Called Station ID or cNumber should not be used for rating purposes because // it is chosen by the subscriber but the Proxy rewrites it into a different // final destination (the Canonical URI) // Canonical URI is the final logical SIP destination after all // lookups like aliases, usrloc , call forwarding, ENUM // mappings or PSTN gateways but before the DNS lookup // Canonical URI must be saved in the SIP Proxy and added as an extra // Radius attribute in the Radius START packet if (!$this->CanonicalURI) { if ($this->RemoteAddress) { $this->CanonicalURI=$this->RemoteAddress; } elseif ($this->cNumber) { $this->CanonicalURI=$this->cNumber; } } if ($this->CanonicalURI) { $this->CanonicalURIPrint = $this->CanonicalURI; $NormalizedNumber = $this->CDRS->NormalizeNumber( $this->CanonicalURI, "destination", $this->BillingPartyId, $this->domain, $this->gateway, '', $this->ENUMtld, $this->ResellerId ); $this->CanonicalURINormalized = $NormalizedNumber['Normalized']; $this->CanonicalURIUsername = $NormalizedNumber['username']; $this->CanonicalURIDomain = $NormalizedNumber['domain']; $this->CanonicalURIPrint = $NormalizedNumber['NumberPrint']; $this->CanonicalURIDelimiter = $NormalizedNumber['delimiter']; $this->CanonicalURIE164 = $NormalizedNumber['E164']; // Destination Id is used for rating purposes $this->DestinationId = $NormalizedNumber['DestinationId']; $this->destinationName = $NormalizedNumber['destinationName']; $this->region = $NormalizedNumber['region']; } if ($this->cNumber) { $NormalizedNumber = $this->CDRS->NormalizeNumber( $this->cNumber, "destination", $this->BillingPartyId, $this->domain, $this->gateway, '', $this->ENUMtld, $this->ResellerId ); $this->cNumberNormalized = $NormalizedNumber['Normalized']; $this->cNumberUsername = $NormalizedNumber['username']; $this->cNumberDomain = $NormalizedNumber['domain']; $this->cNumberPrint = $NormalizedNumber['username'].$NormalizedNumber['delimiter'].$NormalizedNumber['domain']; $this->cNumberDelimiter = $NormalizedNumber['delimiter']; $this->cNumberE164 = $NormalizedNumber['E164']; } if ($this->RemoteAddress) { // Next hop is the real destination after all lookups including DNS $NormalizedNumber = $this->CDRS->NormalizeNumber( $this->RemoteAddress, "destination", $this->BillingPartyId, $this->domain, $this->gateway, '', $this->ENUMtld, $this->ResellerId ); $this->RemoteAddressPrint = $NormalizedNumber['NumberPrint']; $this->RemoteAddressNormalized = $NormalizedNumber['Normalized']; $this->RemoteAddressDestinationId = $NormalizedNumber['DestinationId']; $this->RemoteAddressDestinationName = $NormalizedNumber['destinationName']; $this->RemoteAddressUsername = $NormalizedNumber['username']; $this->RemoteAddressDelimiter = $NormalizedNumber['delimiter']; $this->RemoteAddressE164 = $NormalizedNumber['E164']; $this->remoteGateway = $NormalizedNumber['domain']; $this->remoteUsername = $NormalizedNumber['username']; } $this->isCalleeLocal(); $this->isCallerLocal(); if ($this->CallerIsLocal) { if ($this->aNumberPrint == $this->BillingPartyId) { // call is not diverted if ($this->CalleeIsLocal) { $this->flow = 'on-net'; } else { $this->flow = 'outgoing'; } } else { // call is diverted if ($this->CalleeIsLocal) { $this->flow = 'on-net-diverted-on-net'; } else { $this->flow = 'on-net-diverted-off-net'; } } } else { if ($this->isBillingPartyLocal()) { // call is diverted by local user if ($this->CalleeIsLocal) { $this->flow = 'diverted-on-net'; } else { $this->flow = 'diverted-off-net'; } } elseif ($this->CalleeIsLocal) { $this->flow = 'incoming'; } else { // transit from trusted peer $this->flow = 'transit'; } } if (( $this->flow == 'on-net' || $this->flow == 'diverted-on-net' || $this->flow == 'on-net-diverted-on-net' ) && $this->application == 'audio' && $this->CDRS->rating_settings['rate_on_net_calls'] && $this->CDRS->rate_on_net_enabled($_billing_party_els[0], $_billing_party_els[1]) && !$this->DestinationId && $this->CalleeCallerId ) { $_dest = preg_replace("/^\+(\d+)$/", "00$1", $this->CalleeCallerId); $NormalizedNumber = $this->CDRS->NormalizeNumber( $_dest, "destination", $this->BillingPartyId, $this->domain, $this->gateway, '', $this->ENUMtld, $this->ResellerId ); $this->DestinationId = $NormalizedNumber['DestinationId']; $this->destinationName = $NormalizedNumber['destinationName']; } if ($this->CDRS->rating_settings['rate_on_net_calls'] && $this->CDRS->rating_settings['rate_on_net_diverted_calls'] && ( $this->flow == 'on-net-diverted-off-net' || $this->flow == 'on-net-diverted-on-net' || $this->flow == 'diverted-on-net' || $this->flow == 'diverted-off-net' ) && !$this->normalized && $this->duration != '0' && $this->disconnect == $this->disconnectOrig ) { $query = sprintf( " update %s set AcctStopTime ='%s', Normalized='0', AcctSessionTime='%s', SipResponseCode='200' where AcctSessionId='%s' and SipFromTag='%s' and SipToTag!='%s' and ( ServiceType='on-net' or ServiceType='on-net-diverted-on-net' or ServiceType='diverted-on-net' or ServiceType='incoming') and AcctSessionTime='' ", $this->CDRS->table, $this->stopTime, $this->duration, $this->callId, $this->SipFromTag, $this->SipToTag ); $this->tdb = new DB_radius; dprint_sql($query); $this->tdb->query($query); } if ($this->application == "presence") { $this->destinationPrint = $this->cNumberUsername.$this->cNumberDelimiter.$this->cNumberDomain; $this->DestinationForRating = $this->cNumberNormalized; } else { if (!$this->DestinationId) { if ($this->CanonicalURIDomain) { $this->destinationPrint = $this->CanonicalURIUsername.$this->CanonicalURIDelimiter.$this->CanonicalURIDomain; } else { $this->destinationPrint = $this->cNumberUsername.$this->cNumberDelimiter.$this->cNumberDomain; } if (strstr($this->CanonicalURINormalized, '@')) { $this->DestinationForRating = $this->CanonicalURINormalized; } else { $this->DestinationForRating = $this->RemoteAddressNormalized; } } else { $this->DestinationForRating = $this->CanonicalURINormalized; $this->destinationPrint = $this->CanonicalURIPrint; } } if ($this->inputTraffic) { $this->inputTrafficPrint = number_format($this->inputTraffic/1024, 2); } else { $this->inputTrafficPrint = 0; } if ($this->outputTraffic) { $this->outputTrafficPrint = number_format($this->outputTraffic/1024, 2); } else { $this->outputTrafficPrint = 0; } if (!$CDRfields['skip_fix_prepaid_duration']) { if (!$this->normalized && $this->callId) { // fix the duration of prepaid sessions if the prepaid duration is different than radius calculated duration $query = sprintf( " select duration from prepaid_history where session = '%s' and destination = '%s' order by id desc limit 1 ", addslashes($this->callId), addslashes($this->destinationPrint) // must be synced with maxsession time ); if ($this->CDRS->cdrtool->query($query)) { if ($this->CDRS->cdrtool->num_rows()) { $this->CDRS->cdrtool->next_record(); $this->durationNormalized = $this->CDRS->cdrtool->f('duration'); $this->durationPrint = sec2hms($this->durationNormalized); } else { $this->durationPrint = sec2hms($this->duration); } } else { $log = sprintf( "Database error for query %s: %s (%s)", $query, $this->CDRS->cdrtool->Error, $this->CDRS->cdrtool->Errno ); syslog(LOG_NOTICE, $log); } } else { $this->durationPrint = sec2hms($this->duration); } } else { $this->durationPrint = sec2hms($this->duration); } if ($this->disconnect) { $this->disconnectPrint = $this->NormalizeDisconnect($this->disconnect); } if ($this->disconnectOrig != $this->disconnect && $this->disconnect && $this->CDRS->rating_settings['rate_on_net_diverted_calls']) { $this->disconnectOrigPrint = $this->CDRS->disconnectCodesDescription[$this->disconnectOrig]." (".$this->disconnectOrig.")"; } $this->traceIn(); $this->traceOut(); $this->obfuscateCallerId(); if ($this->CDRS->rating) { global $perm; if (is_object($perm) && $perm->have_perm("showPrice")) { $this->pricePrint=$this->price; } else { $this->pricePrint='x.xxx'; } } } private function buildCDRdetail() { global $perm; global $found; if (!is_object($perm)) return; $this->geo_location = $this->lookupGeoLocation($this->SourceIP); $this->cdr_details = "

SIP Signalling
"; $this->cdr_details .= sprintf( "Click here to show only this call id", $this->CDRS->url_run, urlencode($this->callId) ); if ($this->CDRS->sipTrace) { $trace_query = array( 'cdr_source' => $this->CDRS->sipTrace, 'callid' => quoted_printable_decode($this->callId), 'fromtag' => quoted_printable_decode($this->SipFromTag), 'totag' => quoted_printable_decode($this->SipToTag), 'proxyIP' => $this->SipProxyServer ); $this->traceLink = sprintf( " Click here for the SIP trace  ", http_build_query($trace_query) ); $this->cdr_details .= "
Call id:
$this->callId
"; } $this->cdr_details .= sprintf( "
%s
", $this->traceLink ); $this->cdr_details .= "
From tag:
$this->SipFromTag
To tag:
$this->SipToTag
Start Time:
$this->startTime $providerTimezone
Stop Time:
$this->stopTime
"; $this->cdr_details .= sprintf( "
Country:
%s
", $this->geo_location ); $this->cdr_details .= "
Method:
$this->SipMethod from $this->SourceIP:$this->SourcePort
From:
$this->aNumberPrint
From Header:
$this->FromHeaderPrint
User Agent:
$this->UserAgentPrint
Domain:
$this->domain
To (dialed URI):
$this->cNumberPrint
"; if ($perm->have_perm("showCallerId") && $this->SipRPID) { $this->cdr_details .= sprintf( "
PAI Header:
%s
", htmlentities($this->SipRPIDPrint) ); } if ($this->tlscn) { $this->cdr_details .= sprintf( "
TLS Common Name:
%s
", htmlentities($this->tlscn) ); } if ($this->CanonicalURI) { $this->cdr_details .= sprintf( "
Canonical URI:
%s
", htmlentities($this->CanonicalURI) ); } $this->cdr_details .= sprintf( "
Next Hop URI:
%s
", htmlentities($this->RemoteAddress) ); if ($this->DestinationId) { $this->cdr_details .= "
Destination:
$this->destinationName ($this->DestinationId)
"; } if ($this->ENUMtld && $this->ENUMtld != 'none' && $this->ENUMtld != 'N/A') { $this->cdr_details .= "
ENUM TLD:
$this->ENUMtld
"; } if ($this->CalleeCallerId) { $this->cdr_details .= "
Called ID:
$this->CalleeCallerId
"; } $this->cdr_details .= "
Billing Party:
$this->BillingPartyIdPrint
Reseller:
$this->ResellerId
"; $this->cdr_details .= "
"; if ($this->application != 'message') { $this->cdr_details .= "
Media Streams
"; if ($this->CDRS->mediaTrace) { $media_query = array( 'cdr_source' => $this->CDRS->mediaTrace, 'callid' => quoted_printable_decode($this->callId), 'fromtag' => quoted_printable_decode($this->SipFromTag), 'totag' => quoted_printable_decode($this->SipToTag), 'proxyIP' => $this->SipProxyServer ); $this->mediaTraceLink = sprintf( "Click here for media information  ", http_build_query($media_query) ); $this->cdr_details .= sprintf( "
%s
", $this->mediaTraceLink ); } $sessionId = rtrim(base64_encode(hash('md5', $this->callId, true)), "="); $this->cdr_details .= "
Session ID:
$sessionId
"; $this->SipCodec = quoted_printable_decode($this->SipCodec); if ($this->SipCodec) { $this->cdr_details .= "
Codecs:
$this->SipCodec
"; } $this->cdr_details .= "
Caller RTP:
$this->inputTrafficPrint KB
Called RTP:
$this->outputTrafficPrint KB
"; if ($this->MediaInfo) { $this->cdr_details .= "
Media Info:
$this->MediaInfo
"; } $this->cdr_details .= "
Applications:
$this->application_print
"; } if ($this->SipUserAgents) { $this->SipUserAgents = quoted_printable_decode($this->SipUserAgents); $callerAgents = explode("+", $this->SipUserAgents); $callerUA = htmlentities($callerAgents[0]); $calledUA = htmlentities($callerAgents[1]); $this->cdr_details.= "
Caller SIP UA:
$callerUA
Called SIP UA:
$calledUA
"; } $this->cdr_details.= "
"; if ($perm->have_perm("showPrice") && $this->normalized) { $this->cdr_details.= "
Rating
"; if ($this->price > 0 || $this->rate) { $this->ratePrint=nl2br($this->rate); $this->cdr_details.= "
$this->ratePrint
"; } else { $this->cdr_details.= "
Free call
"; } $this->cdr_details.= "
"; } $this->cdr_details.= "
"; } function traceIn() { $datasource=$this->CDRS->traceInURL[$this->SourceIP]; global $DATASOURCES; if (!$datasource || !$DATASOURCES[$datasource]) { return; } $tplus = $this->timestamp+$this->duration+300; $tmin = $this->timestamp-300; $c_number = $this->remoteUsername; $cdr_table = Date('Ym', time($this->timestamp)); $this->traceIn = "". "In". ""; } function traceOut() { $datasource = $this->CDRS->traceOutURL[$this->remoteGateway]; global $DATASOURCES; if (!$datasource || !$DATASOURCES[$datasource]) { return; } $tplus = $this->timestamp+$this->duration+300; $tmin = $this->timestamp-300; $c_number = preg_replace("/^(0+)/", "", $this->remoteUsername); $cdr_table = Date('Ym', time($this->timestamp)); $this->traceOut= "". "Out". ""; } public function show() { $this->buildCDRdetail(); global $found; global $perm; $rr = floor($found / 2); $mod = $found - $rr * 2; if ($mod == 0) { $inout_color = "#F9F9F9"; } else { $inout_color = "white"; } $this->ratePrint = nl2br($this->rate); if ($this->CDRS->Accounts[$this->BillingPartyId]['timezone']) { $timezone_print = $this->CDRS->Accounts[$this->BillingPartyId]['timezone']; } else { $timezone_print = $this->CDRS->CDRTool['provider']['timezone']; } $found_print = $found; if ($this->normalized) { $found_print .= 'N'; } $providerTimezone = $this->CDRS->CDRTool['provider']['timezone']; print " $found_print $this->startTime $this->application $this->flow $this->aNumberPrint $this->geo_location $this->SipProxyServer $this->MediaProxy $this->destinationPrint "; if ($this->DestinationId) { if ($this->DestinationId != $this->CanonicalURI) { print " ($this->destinationName $this->DestinationId)"; } else { print " ($this->destinationName)"; } } print ""; if (!$this->normalized) { if ($this->duration > 0) { print "$this->duration(s)"; } else { print "in progress"; } } else { print " $this->durationPrint $this->pricePrint $this->inputTrafficPrint $this->outputTrafficPrint "; } $SIPclass=substr($this->disconnect, 0, 1); if ($SIPclass=="6") { $status_color=""; } elseif ($SIPclass == "5") { $status_color=""; } elseif ($SIPclass == "4") { $status_color=""; } elseif ($SIPclass == "3") { $status_color=""; } elseif ($SIPclass == "2") { $status_color=""; } else { $status_color=""; } if ($this->disconnectOrig != $this->disconnect && $this->CDRS->rating_settings['rate_on_net_diverted_calls']) { $disclass = substr($this->disconnectOrig, 0, 1); if ($disclass == "6" || $disclass == "5") { $status1_color=""; } elseif ($disclass == "4") { $status1_color=""; } elseif ($disclass == "3") { $status1_color=""; } elseif ($disclass == "2") { $status1_color=""; } else { $status1_color=""; } } print " $status_color $this->disconnectPrint"; if ($this->disconnectOrig != $this->disconnect && $this->CDRS->rating_settings['rate_on_net_diverted_calls']) { print "$status1_color $this->disconnectOrigPrint"; } print " $this->cdr_details "; } function export() { global $found; global $perm; $disconnectName = $this->CDRS->disconnectCodesDescription[$this->disconnect]; $UserAgents = explode("+", $this->SipUserAgents); $CallingUserAgent = trim($UserAgents[0]); $CalledUserAgent = trim($UserAgents[1]); print "$found"; print ",$this->startTime"; print ",$this->stopTime"; print ",$this->BillingPartyIdPrint"; print ",$this->domain"; print ",$this->aNumberPrint"; print ",$this->destinationPrint"; print ",$this->DestinationId"; print ",$this->destinationName"; print ",$this->RemoteAddressPrint"; print ",$this->CanonicalURIPrint"; print ",$this->duration"; print ",$this->price"; print ",$this->SipProxyServer"; print ",$this->inputTraffic"; print ",$this->outputTraffic"; printf(",%s", preg_replace("/,/", "/", quoted_printable_decode($CallingUserAgent))); printf(",%s", preg_replace("/,/", "/", quoted_printable_decode($CalledUserAgent))); print ",$this->disconnect"; print ",$disconnectName"; printf(",%s", preg_replace("/,/", "/", quoted_printable_decode($this->SipCodec))); print ",$this->application"; print ",$this->MediaProxy"; print ",$this->tlscn"; if ($perm->have_perm("showCallerId")) { print ",$this->SipRPIDPrint"; } print "\n"; } function showSubscriber() { $this->buildCDRdetail(); global $found; $rr=floor($found/2); $mod=$found-$rr*2; if ($mod ==0) { $inout_color="lightgrey"; } else { $inout_color="white"; } if (!$this->CDRS->export) { $timezone_print=$this->CDRS->CDRTool['provider']['timezone']; $found_print=$found; if ($this->normalized) { $found_print.='N'; } print " $found_print $this->startTime $timezone_print $this->aNumberPrint $this->geo_location $this->SipProxyServer $this->destinationPrint $this->destinationName $this->durationPrint "; if ($this->CDRS->rating) { print "$this->pricePrint"; } print " $this->inputTrafficPrint $this->outputTrafficPrint "; print " $this->cdr_details "; } else { $disconnectName = $this->CDRS->disconnectCodesDescription[$this->disconnect]; $UserAgents = explode("+", $this->SipUserAgents); $CallingUserAgent = trim($UserAgents[0]); $CalledUserAgent = trim($UserAgents[1]); print "$found"; print ",$this->startTime"; print ",$this->stopTime"; print ",$this->BillingPartyId"; print ",$this->domain"; print ",$this->aNumberPrint"; print ",$this->cNumberPrint"; print ",$this->DestinationId"; print ",$this->destinationName"; print ",$this->RemoteAddressPrint"; print ",$this->duration"; print ",$this->price"; print ",$this->SipProxyServer"; print ",$this->inputTraffic"; print ",$this->outputTraffic"; print ",$CallingUserAgent"; print ",$CalledUserAgent"; print ",$this->disconnect"; print ",$disconnectName"; print ",$this->SipCodec"; print ",$this->application\n"; } } function isBillingPartyLocal() { $els = explode("@", $this->BillingPartyId); if ($els[1] && isset($this->CDRS->localDomains[$els[1]])) { return true; } return false; } function isCallerLocal() { if (isset($this->CDRS->localDomains[$this->aNumberDomain])) { $this->CallerIsLocal = true; $this->SipRPID = $this->CDRS->getCallerId($this->BillingPartyId); #$this->SipRPIDPrint = quoted_printable_decode($this->SipRPID); } $this->SipRPIDPrint = $this->SipRPID; } function isCalleeLocal() { if (isset($this->CDRS->localDomains[$this->CanonicalURIDomain]) && !preg_match("/^0/", $this->CanonicalURIUsername)) { $this->CalleeIsLocal = true; $this->CalleeCallerId = $this->CDRS->getCallerId($this->CanonicalURI); } } function obfuscateCallerId() { global $obfuscateCallerId; if ($obfuscateCallerId) { //Caller party $caller_els=explode("@", $this->aNumberPrint); if (is_numeric($caller_els[0]) && strlen($caller_els[0]>3)) { $_user = substr($caller_els[0], 0, strlen($caller_els[0])-3).'xxx'; } else { $_user = 'caller'; } if (count($caller_els) == 2) { $this->aNumberPrint = $_user.'@'.$caller_els[1]; } else { $this->aNumberPrint = $_user; } //Billing party $caller_els = explode("@", $this->BillingPartyIdPrint); if (is_numeric($caller_els[0]) && strlen($caller_els[0]>3)) { $_user = substr($caller_els[0], 0, strlen($caller_els[0])-3).'xxx'; } else { $_user = 'party'; } $this->BillingPartyIdPrint = $_user.'@'.$caller_els[1]; // Destination $caller_els = explode("@", $this->destinationPrint); if (is_numeric($caller_els[0]) && strlen($caller_els[0]>3)) { $_user = substr($caller_els[0], 0, strlen($caller_els[0])-3).'xxx'; } else { $_user = 'destination'; } if (count($caller_els) == 2) { $this->destinationPrint = $_user.'@'.$caller_els[1]; } else { $this->destinationPrint = $_user; } $caller_els = explode("@", $this->cNumberPrint); if (is_numeric($caller_els[0]) && strlen($caller_els[0]>3)) { $_user = substr($caller_els[0], 0, strlen($caller_els[0])-3).'xxx'; } else { $_user = 'dialedNumber'; } if (count($caller_els) == 2) { $this->cNumberPrint = $_user.'@'.$caller_els[1]; } else { $this->cNumberPrint = $_user; } $caller_els = explode("@", $this->RemoteAddressPrint); if (is_numeric($caller_els[0]) && strlen($caller_els[0]>3)) { $_user = substr($caller_els[0], 0, strlen($caller_els[0])-3).'xxx'; } else { $_user = 'remoteAddress'; } if (count($caller_els) == 2) { $this->RemoteAddressPrint = $_user.'@'.$caller_els[1]; } else { $this->RemoteAddressPrint = $_user; } // Canonical URI $caller_els = explode("@", $this->CanonicalURIPrint); if (is_numeric($caller_els[0]) && strlen($caller_els[0]>3)) { $_user = substr($caller_els[0], 0, strlen($caller_els[0])-3).'xxx'; } else { $_user = 'canonicalURI'; } if (count($caller_els) == 2) { $this->CanonicalURIPrint = $_user.'@'.$caller_els[1]; } else { $this->CanonicalURIPrint = $_user; } if (is_numeric($this->SipRPIDPrint) && strlen($this->SipRPIDPrint) > 3) { $this->SipRPIDPrint = substr($this->SipRPID, 0, strlen($this->SipRPID)-3).'xxx'; } else { $_user = 'callerId'; } // IP address $this->SourceIP = 'xxx.xxx.xxx.xxx'; } } } class SIP_trace { public $enableThor = false; public $trace_array = array(); public $traced_ip = array(); public $SIPProxies = array(); public $mediaTrace = false; public $thor_nodes = array(); public $hostnames = array(); public function __construct($cdr_source) { global $DATASOURCES, $auth; require_once 'errors.php'; $this->cdr_source = $cdr_source; $this->cdrtool = new DB_CDRTool(); if (!is_array($DATASOURCES[$this->cdr_source])) { $log = sprintf("Error: datasource '%s' is not defined\n", $this->cdr_source); print $log; throw new DataSourceUndefinedError($log); return 0; } if (strlen($DATASOURCES[$this->cdr_source]['enableThor'])) { $this->enableThor = $DATASOURCES[$this->cdr_source]['enableThor']; } if (strlen($DATASOURCES[$this->cdr_source]['mediaTrace'])) { $this->mediaTrace = $DATASOURCES[$this->cdr_source]['mediaTrace']; } if ($this->enableThor) { require '/etc/cdrtool/ngnpro_engines.inc'; require_once 'ngnpro_soap_library.php'; if ($DATASOURCES[$this->cdr_source]['soapEngineId'] && in_array($DATASOURCES[$this->cdr_source]['soapEngineId'], array_keys($soapEngines))) { $this->soapEngineId=$DATASOURCES[$this->cdr_source]['soapEngineId']; $this->SOAPlogin = array( "username" => $soapEngines[$this->soapEngineId]['username'], "password" => $soapEngines[$this->soapEngineId]['password'], "admin" => true ); $this->SOAPurl=$soapEngines[$this->soapEngineId]['url']; $this->SoapAuth = array('auth', $this->SOAPlogin , 'urn:AGProjects:NGNPro', 0, ''); // Instantiate the SOAP client $this->soapclient = new WebService_NGNPro_SipPort($this->SOAPurl); $this->soapclient->setOpt('curl', CURLOPT_TIMEOUT, 5); $this->soapclient->setOpt('curl', CURLOPT_SSL_VERIFYPEER, 0); $this->soapclient->setOpt('curl', CURLOPT_SSL_VERIFYHOST, 0); if (is_array($soapEngines[$this->soapEngineId]['hostnames'])) { $this->hostnames=$soapEngines[$this->soapEngineId]['hostnames']; } } else { printf("

Error: soapEngineID not defined in datasource %s", $this->cdr_source); return false; } } else { $this->table = $DATASOURCES[$this->cdr_source]['table']; $db_class = $DATASOURCES[$this->cdr_source]['db_class']; $this->purgeRecordsAfter = $DATASOURCES[$this->cdr_source]['purgeRecordsAfter']; if (class_exists($db_class)) { $this->db = new $db_class; } else { printf("

Error: database class '%s' is not defined", $db_class); return false; } } if (is_object($auth)) { $this->isAuthorized=1; } if (is_array($DATASOURCES[$this->cdr_source]['SIPProxies'])) { $this->SIPProxies = $DATASOURCES[$this->cdr_source]['SIPProxies']; } } private function isProxy($ip, $sip_proxy = '') { if (!$ip) { return false; } if (!$this->enableThor) { if (!is_array($this->SIPProxies)) { return false; } if (in_array($ip, array_keys($this->SIPProxies))) { return true; } } elseif ($sip_proxy) { if (isset($this->thor_nodes[$ip])) { return true; } else { if (isThorNode($ip, $sip_proxy) || isThorNode($ip, $sip_proxy, 'msteams_gateway')) { $this->thor_nodes[$ip]=1; return true; } else { return false; } } } return false; } private function getTrace($proxyIP, $callid, $fromtag, $totag) { if ($this->enableThor) { // get trace using soap request if (!$proxyIP || !$callid || !$fromtag) { return false; } global $DATASOURCES; if (is_array($DATASOURCES[$this->cdr_source]['proxyTranslation_IP']) && isset($DATASOURCES[$this->cdr_source]['proxyTranslation_IP'][$proxyIP]) && strlen($DATASOURCES[$this->cdr_source]['proxyTranslation_IP'][$proxyIP]) ) { $proxyIP = $DATASOURCES[$this->cdr_source]['proxyTranslation_IP'][$proxyIP]; } if (!is_object($this->soapclient)) { print "Error: soap client is not defined."; return false; } $filter = array( 'nodeIp' => $proxyIP, 'callId' => $callid, 'fromTag' => $fromtag, 'toTag' => $totag ); $this->soapclient->addHeader($this->SoapAuth); $result = $this->soapclient->getSipTrace($filter); if ((new PEAR)->isError($result)) { $error_msg = $result->getMessage(); $error_fault = $result->getFault(); $error_code = $result->getCode(); printf( "

Error from %s


%s (%s)
", $this->SOAPurl, $error_fault->detail->exception->errorstring, $error_fault->detail->exception->errorcode ); return false; } $columns = 0; $traces = json_decode($result); $trace_array = array(); foreach ($traces as $_trace) { if (preg_match("/^(udp|tcp|tls):(.*):(.*)$/", $_trace->to_ip, $m)) { $toip = $m[2]; $transport = $m[1]; $toport = $m[3]; } elseif (preg_match("/^(.*):(.*)$/", $_trace->to_ip, $m)) { $toip = $m[1]; $transport = 'udp'; $toport = $m[2]; } else { $toip = $_trace->to_ip; $transport = $_trace->to_proto; $toport = $_trace->to_port; } if (preg_match("/^(udp|tcp|tls):(.*):(.*)$/", $_trace->from_ip, $m)) { $fromip = $m[2]; $fromport = $m[3]; } elseif (preg_match("/^(.*):(.*)$/", $_trace->from_ip, $m)) { $fromip = $m[1]; $fromport = $m[2]; } else { $fromip = $_trace->from_ip; $fromport = $_trace->from_port; } if (!isset($this->column[$fromip])) { $this->column[$fromip] = $columns + 1; $this->column_port[$fromip] = $fromport; $columns++; } if (!isset($this->column[$toip])) { $this->column[$toip] = $columns+1; $this->column_port[$toip] = $toport; $columns++; } preg_match("/^(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)$/", $_trace->time_stamp, $m); $timestamp = mktime($m[4], $m[5], $m[6], $m[2], $m[3], $m[1]); $idx=$proxyIP.'_'.$_trace->id; $trace_array[$idx] = array ( 'id' => $idx, 'direction' => $_trace->direction, 'fromip' => $fromip, 'toip' => $toip, 'fromport' => $fromport, 'toport' => $toport, 'method' => $_trace->method, 'transport' => $transport, 'date' => $_trace->time_stamp, 'status' => $_trace->status, 'timestamp' => $timestamp, 'msg' => $_trace->message, 'md5' => md5($_trace->message) ); } $this->trace_array=$trace_array; $this->rows = count($this->trace_array); } else { // get trace from SQL if (!is_object($this->db)) { print "

Error: no database connection defined"; return false; } $query = sprintf( " select *, UNIX_TIMESTAMP(time_stamp) as timestamp from %s where callid = '%s' order by id asc ", addslashes($this->table), addslashes($callid) ); if (!$this->db->query($query)) { printf("Database error for query %s: %s (%s)", $query, $this->db->Error, $this->db->Errno); return false; } $this->rows = $this->db->num_rows(); $columns = 0; while ($this->db->next_record()) { if (preg_match("/^(udp|tcp|tls):(.*):(.*)$/", $this->db->f('toip'), $m)) { $toip = $m[2]; $transport = $m[1]; $toport = $m[3]; } elseif (preg_match("/^(.*):(.*)$/", $this->db->f('toip'), $m)) { $toip = $m[1]; $transport = 'udp'; $toport = $m[2]; } else { $toip = $this->db->f('toip'); $toport = '5060'; } if (preg_match("/^(udp|tcp|tls):(.*):(.*)$/", $this->db->f('fromip'), $m)) { $fromip = $m[2]; $fromport = $m[3]; } elseif (preg_match("/^(.*):(.*)$/", $this->db->f('fromip'), $m)) { $fromip = $m[1]; $fromport = $m[2]; } else { $fromip = $this->db->f('fromip'); $transport = 'udp'; $fromport = '5060'; } if (!$this->column[$fromip]) { $this->column[$fromip]=$columns+1; $this->column_port[$fromip]=$fromport; $columns++; } if (!$this->column[$toip]) { $this->column[$toip] = $columns + 1; $this->column_port[$toip]=$toport; $columns++; } $this->trace_array[$this->db->f('id')] = array ( 'id' => $this->db->f('id'), 'direction' => $this->db->f('direction'), 'fromip' => $fromip, 'toip' => $toip, 'method' => $this->db->f('method'), 'fromport' => $fromport, 'toport' => $toport, 'transport' => $transport, 'date' => $this->db->f('time_stamp'), 'status' => $this->db->f('status'), 'timestamp' => $this->db->f('timestamp'), 'msg' => $this->db->f('msg'), 'md5' => md5($this->db->f('msg')) ); } } } private function printLabelProtocolPort($transport, $port) { echo ''; if ($transport == 'tls') { echo " "; } printf('%s: %d', strtoupper($transport), $port); echo ''; } public function show($proxyIP, $callid, $fromtag, $totag) { $action = $_REQUEST['action']; $toggleVisibility = $_REQUEST['toggleVisibility']; if ($action == 'toggleVisibility') { $this->togglePublicVisibility($callid, $fromtag, $toggleVisibility); } if ($_SERVER['HTTPS'] == "on") { $protocolURL = "https://"; } else { $protocolURL = "http://"; } $this->getTrace($proxyIP, $callid, $fromtag, $totag); /* No trace can be found */ if (!count($this->trace_array)) { echo "

SIP trace for session id $callid is not available.

"; return; } echo "

CDRTool SIP trace
Call ID: $callid $authorize

"; $basicURL = $protocolURL.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']; $fullURL = $basicURL; print "URLs for this trace: HTML | TEXT"; if ($this->mediaTrace) { $media_query = array( 'cdr_source' => $this->mediaTrace, 'callid' => $callid, 'fromtag' => $fromtag, 'totag' => $totag, 'proxyIP' => $proxyIP ); $this->mediaTraceLink = sprintf( "

Click here for RTP media information

", http_build_query($media_query) ); } print "

Click on each packet to expand its body content

$this->mediaTraceLink
"; foreach (array_keys($this->trace_array) as $key) { $this->trace_array[$key]['isProxy'] = 0; if ($this->trace_array[$key]['direction'] == 'in') { if (is_array($this->SIPProxies)) { $thisIP=explode(":", $this->trace_array[$key]['fromip']); if ($this->isProxy($thisIP[0], $proxyIP)) { $this->trace_array[$key]['isProxy'] = 1; } } $this->trace_array[$key]['msg_possition'] = $this->column[$this->trace_array[$key]['toip']]; $this->trace_array[$key]['arrow_possition'] = $this->column[$this->trace_array[$key]['fromip']]; $this->trace_array[$key]['arrow_direction'] = $arrow_direction; // handle self-generated BYE if ($this->trace_array[$key]['fromip'] == $this->trace_array[$key]['toip']) { if ($this->trace_array[$key]['method'] == 'BYE') { $bye_ip = $this->trace_array[$key]['fromip']; $bye_lines = preg_split('/\n|\r\n?/', $this->trace_array[$key]['msg']); $bye_line = $bye_lines[0]; $fi=$this->trace_array[$key]['fromip']; $fp=$this->trace_array[$key]['fromport']; $ti=$this->trace_array[$key]['toip']; $tp=$this->trace_array[$key]['toport']; if (preg_match("/^BYE (sip:|sips:)(.*)\@(.*):(\d*)(.*)$/", $bye_line, $m)) { $bye_ip = $m[3]; $bye_port = $m[4]; if ($this->column[$bye_ip]){ $this->trace_array[$key]['fromip'] = $bye_ip; $this->trace_array[$key]['fromport'] = $bye_port; $this->trace_array[$key]['arrow_possition'] = $this->column[$bye_ip]; } else { $found_bye_ip = false; foreach ($bye_lines as $_line) { if (preg_match("/^Route:(.*)$/", $_line, $mr)) { $line = str_replace(array('<', '>'), "", $mr[1]); $routes = explode(",", $line); foreach ($routes as $r) { if (preg_match("/(.*sip:|sips:)(.*):(\d+)(.*)$/", $r, $mm)) { $bye_ip = $mm[2]; $bye_port = $mm[3]; if ($this->column[$bye_ip]){ $this->trace_array[$key]['fromip'] = $bye_ip; $this->trace_array[$key]['fromport'] = $bye_port; $this->trace_array[$key]['arrow_possition'] = $this->column[$bye_ip]; $found_bye_ip = true; break; } } } if ($found_bye_ip){ break; } } } } } else { $arrow_direction = "loop"; } if ($this->column[$this->trace_array[$key]['fromip']] < $this->column[$proxyIP]) { $arrow_direction = "left"; } else { $arrow_direction = "right"; } } else { $arrow_direction = "loop"; } } elseif ($this->column[$this->trace_array[$key]['fromip']] < $this->column[$this->trace_array[$key]['toip']]) { $arrow_direction = "right"; } else { $arrow_direction = "left"; } $this->trace_array[$key]['arrow_direction'] = $arrow_direction; } else { if ($this->trace_array[$key]['fromip'] == $this->trace_array[$key]['toip']) { $arrow_direction = "loop"; } elseif ($this->column[$this->trace_array[$key]['fromip']] < $this->column[$this->trace_array[$key]['toip']]) { $arrow_direction = "right"; } else { $arrow_direction = "left"; } $this->trace_array[$key]['msg_possition'] = $this->column[$this->trace_array[$key]['fromip']]; $this->trace_array[$key]['arrow_possition'] = $this->column[$this->trace_array[$key]['toip']]; $this->trace_array[$key]['arrow_direction'] = $arrow_direction; } } echo " "; $_seen_timeline = array(); foreach (array_keys($this->column) as $_key) { $IPels = explode(":", $_key); if (isset($this->hostnames[$IPels[0]])) { $_hostname = $this->hostnames[$IPels[0]]; } else { $_hostname = $_key; } print ""; } print ""; /* Rows */ $i=0; foreach (array_keys($this->trace_array) as $key) { $i++; $id = $this->trace_array[$key]['id']; $msg = $this->trace_array[$key]['msg']; $fromip = $this->trace_array[$key]['fromip']; $toip = $this->trace_array[$key]['toip']; $date = substr($this->trace_array[$key]['date'], 11); $status = $this->trace_array[$key]['status']; $direction = $this->trace_array[$key]['direction']; $timestamp = $this->trace_array[$key]['timestamp']; $method = $this->trace_array[$key]['method']; $isProxy = $this->trace_array[$key]['isProxy']; $transport = $this->trace_array[$key]['transport']; $msg_possition = $this->trace_array[$key]['msg_possition']; $arrow_possition = $this->trace_array[$key]['arrow_possition']; $arrow_direction = $this->trace_array[$key]['arrow_direction']; $md5 = $this->trace_array[$key]['md5']; if ($i == 1) { $begin_timestamp = $timestamp; } $timeline = $timestamp - $begin_timestamp; $sip_phone_img = getImageForUserAgent($msg); if ($seen_msg[$md5]) { continue; } $SIPclass = substr($status, 0, 1); switch ($SIPclass) { case 6: $status_color = "red"; break; case 5: $status_color = "red"; break; case 4: $status_color = "red"; break; case 3: $status_color = "green"; break; case 2: $status_color = "green"; break; case 1: $status_color = "orange"; break; default: $status_color = "blue"; if ($method == "ACK") { $status_color = 'cyan'; } else if ($method == "CANCEL") { $status_color = 'magenta'; } break; } $_lines = explode("\n", $msg); if (preg_match("/^(.*) SIP/", $_lines[0], $m)) { $_lines[0] = $m[1]; } elseif (preg_match("/^SIP\/2\.0 (.*)/", $_lines[0], $m)) { $_lines[0] = $m[1]; } unset($media); unset($diversions); $media_index=0; $search_ice=0; $search_ip=0; $contact_header=''; foreach ($_lines as $_line) { if (preg_match("/^(Diversion: ).*;(.*)$/", $_line, $m)) { $diversions[]=$m[1].$m[2]; } if (preg_match("/^Cseq:\s*\d+\s*(.*)$/i", $_line, $m)) { $status_for_method=$m[1]; } if (preg_match("/^c=IN \w+ ([\d|\w\.]+)/i", $_line, $m)) { $media['ip']=$m[1]; } if (preg_match("/^m=(\w+) (\d+) /i", $_line, $m)) { $media_index++; $search_ice=1; $search_ip=1; $media['streams'][$media_index] = array( 'type' => $m[1], 'ip' => $media['ip'], 'port' => $m[2], 'ice' => '' ); } if ($search_ip && preg_match("/^c=IN \w+ ([\d|\w\.]+)/i", $_line, $m)) { $media['streams'][$media_index]['ip']=$m[1]; $search_ip=0; } if ($search_ice && preg_match("/^a=ice/i", $_line, $m)) { $media['streams'][$media_index]['ice']="ICE"; $search_ice=0; } } $_els = explode(";", $_lines[0]); $cell_content = "
$_els[0]"; if ($status) { $cell_content .= " for ".$status_for_method.""; } if (is_array($diversions)) { foreach ($diversions as $_diversion) { $cell_content.="
$_diversion"; } } if (is_array($media['streams'])) { foreach (array_keys($media['streams']) as $_key) { $_stream = sprintf( "%s: %s:%s %s", $media['streams'][$_key]['type'], $media['streams'][$_key]['ip'], $media['streams'][$_key]['port'], $media['streams'][$_key]['ice'] ); if ($media['streams'][$_key]['port']) { $cell_content.="
$_stream"; } else { $cell_content.="
$_stream"; } } } $cell_content.="
"; print " "; $packet_length = strlen($msg); print " "; $column_current = 1; while ($column_current <= count($this->column)) { if ($arrow_possition == $column_current) { /* First cell, first port, append extra cell */ if ($column_current < count($this->column) && $column_current < $msg_possition) { print ""; } $arrowColor = $status_color; if ($arrow_direction == 'loop') { print ""; } if ($arrow_possition >= 2 * $msg_possition) { $arrow_span = ($arrow_possition * 2) - 4; echo ""; if ($column_current < count($this->column) && $column_current > $msg_possition) { print ""; } } else { if ($msg_possition == $column_current) { if ($msg_possition < $arrow_possition) { print ""; #} elseif ($column_current != $this->column[$fromip] && $column_current != $this->column[$toip]) { # echo ""; } echo ""; } if ($arrow_possition == $column_current && $column_current == count($this->column)) { echo ""; } $column_current++; if ($arrow_direction == 'loop') { $seen_msg[$md5]++; } } echo ""; if (is_array($this->SIPProxies)) { $IPels = explode(":", $fromip); $justIP = $IPels[0]; foreach (array_keys($this->SIPProxies) as $localProxy) { if ($localProxy == $justIP) { $direction="out"; break; } } } /* Details */ $trace_span = count($this->column) * 2 + 3; print " "; } print "
Packet Time"; if ($proxyIP != $IPels[0] && $this->isProxy($IPels[0], $proxyIP)) { $trace_query = array( 'cdr_source' => $this->cdr_source, 'callid' => $callid, 'fromtag' => $fromtag, 'totag' => $totag, 'proxyIP' => $IPels[0] ); $trace_link = sprintf( "%s:%s", http_build_query($trace_query), $_hostname, $this->column_port[$_key] ); printf("%s", $trace_link); } else { printf("%s", $_hostname); } print "
$i/$this->rows  $date"; if ($timeline && !isset($_seen_timeline[$timeline])) { printf("  +%ds", $timeline); $_seen_timeline[$timeline] = 1; } print "
$packet_length bytes
"; if ($direction == 'in') { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['fromport']); } else { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['toport']); } echo ""; } else { echo ""; } if ($arrow_direction != 'loop') { print "
"; } if ($arrow_direction == "left") { print "
$cell_content
"; } else { print "
$cell_content
"; } echo "
"; if ($direction == 'in') { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['fromport']); } else { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['toport']); } echo ""; if ($direction == 'out') { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['fromport']); } else { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['toport']); } } else { print ""; if ($direction == 'out') { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['fromport']); } else { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['toport']); } } } elseif ($arrow_possition != $column_current && ( $column_current == 1 || ( $arrow_possition < $column_current && $arrow_possition != $msg_possition) )) { print ""; print ""; print " "; } elseif ($arrow_possition == $msg_possition) { echo ""; if ($direction == 'in') { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['fromport']); } else { $this->printLabelProtocolPort($transport, $this->trace_array[$key]['toport']); } echo "
"; if ($direction == "out" or $isProxy) { print "

SIP Proxy

"; } else { if ($sip_phone_img && $sip_phone_img!='unknown.png') { print ""; } else { print ""; } } print "
"; if ($timeline > 0) { printf("

+%s s
(%s)

", $timeline, sec2hms($timeline)); } print "
"; $msg = nl2br(htmlentities($msg)); print "$msg
"; echo "
"; } public function showText($proxyIP, $callid, $fromtag, $totag) { $this->getTrace($proxyIP, $callid, $fromtag, $totag); print "
";
 
         if (!count($this->trace_array)) {
             print "SIP trace for session id $callid is not available.";
             return false;
         }
 
         printf("SIP trace on proxy %s for session %s\n--\n\n", $proxyIP, $callid);
 
         foreach (array_keys($this->trace_array) as $key) {
             $i++;
             printf(
                 "Packet %d at %s from %s to %s (%s)\n",
                 $i,
                 $this->trace_array[$key]['date'],
                 $this->trace_array[$key]['fromip'],
                 $this->trace_array[$key]['toip'],
                 $this->trace_array[$key]['direction']
             );
             printf(
                 "\n%s\n",
                 htmlspecialchars($this->trace_array[$key]['msg'])
             );
             print "---\n";
         }
         print "
"; } public function togglePublicVisibility($callid, $fromtag, $public = '0') { $key="callid-".trim($callid).trim($fromtag); if (!$public) { $query = sprintf("delete from memcache where `key` = '%s'", addslashes($key)); $this->cdrtool->query($query); } else { $query = sprintf("delete from memcache where `key` = '%s'", addslashes($key)); $this->cdrtool->query($query); $query = sprintf("insert into memcache values ('%s','public')", addslashes($key)); $this->cdrtool->query($query); } } public function purgeRecords($days = '') { if ($this->enableThor) { return true; } $b=time(); if ($days) { $this->purgeRecordsAfter = $days; } elseif (!$this->purgeRecordsAfter) { $this->purgeRecordsAfter = 15; } $beforeDate=Date("Y-m-d", time()-$this->purgeRecordsAfter*3600*24); $query = sprintf( "SELECT id as min, time_stamp FROM %s ORDER BY id ASC limit 1", addslashes($this->table) ); if ($this->db->query($query)) { if ($this->db->num_rows()) { $this->db->next_record(); $min=$this->db->f('min'); $begindate=$this->db->f('date'); } else { $log = sprintf("No records found in %s\n", $this->table); print $log; syslog(LOG_NOTICE, $log); return false; } } else { $log = sprintf("Error: %s (%s)\n", $this->db->Error, $query); print $log; syslog(LOG_NOTICE, $log); return false; } $query=sprintf( "select id as max from %s where time_stamp < '%s' order by id DESC limit 1", addslashes($this->table), addslashes($beforeDate) ); if ($this->db->query($query) && $this->db->num_rows()) { $this->db->next_record(); $max=$this->db->f('max'); } else { $log=sprintf( "No records found in %s before %s, records start after %s\n", $this->table, $beforeDate, $begindate ); syslog(LOG_NOTICE, $log); print $log; return false; } $deleted = 0; $i = $min; $interval = 1000; $rows2delete = $max - $min; $found = 0; print "$rows2delete traces to delete between $min and $max\n"; while ($i<=$max) { $found=$found+$interval; if ($i + $interval < $max) { $top=$i; } else { $top=$max; } $query=sprintf( "delete low_priority from %s where id >= '%d' and id <='%d'", addslashes($this->table), addslashes($min), addslashes($top) ); if ($this->db->query($query)) { $deleted = $deleted + $this->db->affected_rows(); } else { $log = sprintf("Error: %s (%s)", $this->db->Error, $this->db->Errno); syslog(LOG_NOTICE, $log); return false; } if ($found > $progress * $rows2delete / 100) { $progress++; if ($progress % 10 == 0) { print "$progress% "; } flush(); } $i = $i + $interval; } print "\n"; $e = time(); $d = $e - $b; $rps = 0; if ($deleted && $d) { $rps=$deleted/$d; } $log = sprintf( "%s records before %s from %s deleted in %d s @ %.0f rps\n", $deleted, $beforeDate, $this->table, $d, $rps ); syslog(LOG_NOTICE, $log); print $log; return true; } } class Media_trace { public $enableThor = false; public $table = 'media_sessions'; function __construct($cdr_source) { global $DATASOURCES; require_once 'errors.php'; $this->cdr_source = $cdr_source; $this->cdrtool = new DB_CDRTool(); if (!is_array($DATASOURCES[$this->cdr_source])) { $log = sprintf("Error: datasource '%s' is not defined", $this->cdr_source); print $log; throw new DataSourceUndefinedError($log); return 0; } if (strlen($DATASOURCES[$this->cdr_source]['enableThor'])) { $this->enableThor = $DATASOURCES[$this->cdr_source]['enableThor']; } if ($this->enableThor) { require '/etc/cdrtool/ngnpro_engines.inc'; require_once 'ngnpro_soap_library.php'; if ($DATASOURCES[$this->cdr_source]['soapEngineId'] && in_array($DATASOURCES[$this->cdr_source]['soapEngineId'], array_keys($soapEngines))) { $this->soapEngineId=$DATASOURCES[$this->cdr_source]['soapEngineId']; $this->SOAPlogin = array( "username" => $soapEngines[$this->soapEngineId]['username'], "password" => $soapEngines[$this->soapEngineId]['password'], "admin" => true ); $this->SOAPurl=$soapEngines[$this->soapEngineId]['url']; $this->SoapAuth = array('auth', $this->SOAPlogin , 'urn:AGProjects:NGNPro', 0, ''); // Instantiate the SOAP client $this->soapclient = new WebService_NGNPro_SipPort($this->SOAPurl); $this->soapclient->setOpt('curl', CURLOPT_TIMEOUT, 5); $this->soapclient->setOpt('curl', CURLOPT_SSL_VERIFYPEER, 0); $this->soapclient->setOpt('curl', CURLOPT_SSL_VERIFYHOST, 0); } else { print "Error: soapEngineID not defined in datasource $this->cdr_source"; return false; } } else { if ($DATASOURCES[$this->cdr_source]['table']) { $this->table = $DATASOURCES[$this->cdr_source]['table']; } $db_class = $DATASOURCES[$this->cdr_source]['db_class']; if (class_exists($db_class)) { $this->db = new $db_class; } else { printf("

Error: database class %s is not defined in datasource %s", $db_class, $this->cdr_source); return false; } } } private function getTrace($proxyIP, $callid, $fromtag, $totag) { if ($this->enableThor) { // get trace using soap request if (!$proxyIP || !$callid || !$fromtag) { echo "

Error: proxyIP or callid or fromtag are not defined

"; return false; } global $DATASOURCES; if (is_array($DATASOURCES[$this->cdr_source]['proxyTranslation_IP']) && isset($DATASOURCES[$this->cdr_source]['proxyTranslation_IP'][$proxyIP]) && strlen($DATASOURCES[$this->cdr_source]['proxyTranslation_IP'][$proxyIP]) ) { $proxyIP = $DATASOURCES[$this->cdr_source]['proxyTranslation_IP'][$proxyIP]; } if (!is_object($this->soapclient)) { print "

Error: soap client is not defined"; return false; } $filter = array( 'nodeIp' => $proxyIP, 'callId' => $callid, 'fromTag' => $fromtag, 'toTag' => $totag ); $this->soapclient->addHeader($this->SoapAuth); $result = $this->soapclient->getMediaTrace($filter); if ((new PEAR)->isError($result)) { $error_msg = $result->getMessage(); $error_fault = $result->getFault(); $error_code = $result->getCode(); if ($error_fault->detail->exception->errorcode != 1060) { printf( "

Error from %s


%s (%s)
", $this->SOAPurl, $error_fault->detail->exception->errorstring, $error_fault->detail->exception->errorcode ); } return false; } $this->info = json_decode($result); } else { if (!is_object($this->db)) { print "

Error: no database connection defined"; return false; } // get trace from SQL $query = sprintf( "select info from %s where call_id = '%s' and from_tag = '%s' and to_tag= '%s'", addslashes($this->table), addslashes($callid), addslashes($fromtag), addslashes($totag) ); if (!$this->db->query($query)) { printf( "

Database error for query %s: %s (%s)", $query, $this->db->Error, $this->db->Errno ); return false; } if ($this->db->num_rows()) { $this->db->next_record(); $this->info = json_decode($this->db->f('info')); } } } public function show($proxyIP, $callid, $fromtag, $totag) { if ($_SERVER['HTTPS'] == "on") { $protocolURL = "https://"; } else { $protocolURL = "http://"; } $this->getTrace($proxyIP, $callid, $fromtag, $totag); if (!is_object($this->info)) { echo "

No information available
"; return false; } if (empty($this->info->streams)) { echo "
No RTP media streams have been established
"; return; } print "
"; $sessionId = rtrim(base64_encode(hash('md5', $callid, true)), "="); print "

CDRTool Media Trace
Call ID: $callid
Media Session ID: $sessionId

"; foreach (array_values($this->info->streams) as $_val) { $_diff=$_val->end_time-$_val->timeout_wait; $seen_stamp[$_val->start_time]++; $seen_stamp[$_val->end_time]++; $seen_stamp[$_diff]++; $media_types[]=$_val->media_type; } print "

Media Information

"; print ""; printf("", $this->info->duration); list($relay_ip, $relay_port)=explode(":", $this->info->streams[0]->caller_local); printf("", $relay_ip); print "
Call duration%s
Media relay%s
"; print "

Media Streams

"; print ""; print ""; foreach (array_values($media_types) as $_type) { printf("", ucfirst($_type)); } print ""; foreach ($this->info->streams[0] as $_val => $_value) { printf("", ucfirst(preg_replace("/_/", " ", $_val))); $j=0; while ($j < count($media_types)) { printf("", $this->info->streams[$j]->$_val); $j++; } printf("\n"); } print "
%s
%s%s
"; print "

Stream Succession

"; $w_legend_bar = 500; $w_text = 30; $stamps = array_keys($seen_stamp); sort($stamps); $w_table = $w_legend_bar + $w_text; print ""; $j = 0; $_index = 0; foreach (array_values($this->info->streams) as $_val) { if ($_val->status == 'unselected ice candidate') { continue; } $_index = $_index+$_val->start_time; $_duration = $_val->end_time-$_val->start_time; $_timeout = $_val->timeout_wait; $duration_print = $_duration; if ($_val->status == 'conntrack timeout') { $w_duration = intval(($_duration-$_timeout)*$w_legend_bar/$this->info->duration); $w_timeout = intval($_timeout*$w_legend_bar/$this->info->duration); $duration_print = $_duration - $_timeout; } elseif ($_val->status == 'no-traffic timeout') { $w_duration = intval($_duration*$w_legend_bar/$this->info->duration); $w_timeout = intval($_timeout*$w_legend_bar/$this->info->duration); } elseif ($_val->status == 'closed') { $w_duration = intval($_duration * $w_legend_bar / $this->info->duration); $w_timeout = 0; } $w_start_time = intval($_index*$w_legend_bar/$this->info->duration); $w_rest = $w_legend_bar-$w_duration-$w_timeout-$w_start_time; $w_duration_p = ($w_legend_bar/$w_duration) * 100; $w_timeout = 0; if ($w_timeout > 0) { $w_timeout_p = ($w_legend_bar/$w_timeout) * 100; } $w_start_p = 0; if ($w_start_time > 0) { $w_start_p = ($w_legend_bar/$w_start_time)* 100; } //printf ("%s, %s, %s, %s
\n",$w_start_p,$w_duration_p,$w_timeout_p,$w_rest); if ($_val->caller_packets != '0' && $_val->callee_packets != '0') { print ""; print ""; } elseif ($_val->status == 'unselected ICE candidate') { print ""; } else { print ""; } } print "
$_val->media_type\n"; //print "\n"; print "
"; print "
\n"; print "
$duration_print
\n"; if ($_val->timeout_wait) { print "
$_timeout
\n"; } else { print "
\n"; } //print "
\n"; //print "
\n"; print "
ICE session
No stream data found
"; print "
Legend"; print "

Session data
Timeout period

"; } } include_once "phone_images.php"; function getImageForUserAgent($msg) { global $userAgentImages; $msg_lines = explode("\n", $msg); foreach ($msg_lines as $line) { $els = explode(":", $line); if (strtolower($els[0]) == 'user-agent' || strtolower($els[0]) == 'server') { foreach ($userAgentImages as $agentRegexp => $image) { if (preg_match("/^(user-agent|server):.*$agentRegexp/i", $line)) { return $image; } } } } return "unknown.png"; } function isThorNode($ip, $sip_proxy, $role="sip_proxy") { if (!$ip || !$sip_proxy) { return false; } $socket = fsockopen($sip_proxy, 9500, $errno, $errstr, 1); if (!$socket) { return false; } $request=sprintf("is_online %s as %s", $ip, $role); if (fputs($socket, "$request\r\n") !== false) { $ret = trim(fgets($socket, 4096)); fclose($socket); } else { fclose($socket); return false; } if ($ret == 'Yes') { return true; } else { return false; } } ?>