diff --git a/library/cdr_generic.php b/library/cdr_generic.php index 6e2d32c..977ca29 100644 --- a/library/cdr_generic.php +++ b/library/cdr_generic.php @@ -1,3360 +1,3403 @@ '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) { foreach (array_keys($this->CDRFields) as $field) { $mysqlField = $this->CDRFields[$field]; $CDRStructure[$mysqlField] = $this->CDRdb->f($mysqlField); } return $CDRStructure; } - function initCDRFields() + 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; } } - function initDatabaseConnection() + 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; } - function __construct($cdr_source) + 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 (strlen($this->DATASOURCES[$this->cdr_source]['intAccessCode'])) { $this->intAccessCode = $this->DATASOURCES[$this->cdr_source]['intAccessCode']; } if (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)); - } else if ($this->CDRTool['filter']['domain']) { + } elseif ($this->CDRTool['filter']['domain']) { $fdomain=$this->CDRTool['filter']['domain']; $query .= sprintf(" where domain = '%s' or (subscriber = '' and domain = '' and gateway = '') ", addslashes($fdomain)); - } else if ($this->CDRTool['filter']['gateway']) { + } 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); } - function LoadDisconnectCodes() + public function LoadDisconnectCodes() { } function initForm() { } - function searchForm() + 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(''); 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]; } - - } else if (preg_match("/^([a-z0-9]+:)(.*)$/i", $Number, $m)) { + } elseif (preg_match("/^([a-z0-9]+:)(.*)$/i", $Number, $m)) { #$oct=preg_split("/\./",$m[2]); $oct = explode(",", $m[2]); - if(sizeof($oct) == 4) { + 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"; } else if ($this->destinations_sip[$reseller_id][$domain]) { $destinations_sip = $this->destinations_sip[$reseller_id][$domain]; $fCustomer = "domain=$domain"; } else if ($this->destinations_sip[$reseller_id][$gateway]) { $destinations_sip = $this->destinations_sip[$reseller_id][$gateway]; $fCustomer = "gateway=$gateway"; } else if ($this->destinations_sip[$reseller_id]['default']) { $destinations_sip = $this->destinations_sip[$reseller_id]['default']; $fCustomer = "default"; } else if ($this->destinations_sip[0][$subscriber]) { $destinations_sip = $this->destinations_sip[0][$subscriber]; $fCustomer = "subscriber=$subscriber"; } else if ($this->destinations_sip[0][$domain]) { $destinations_sip = $this->destinations_sip[0][$domain]; $fCustomer = "domain=$domain"; } else if ($this->destinations_sip[0][$gateway]) { $destinations_sip = $this->destinations_sip[0][$gateway]; $fCustomer = "gateway=$gateway"; } else if ($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"; } else if ($this->destinations[$reseller_id][$domain]) { $_destinations = $this->destinations[$reseller_id][$domain]; $maxLength = $this->destinations_length[$reseller_id][$domain]; $fCustomer="domain=$domain"; } else if ($this->destinations[$reseller_id][$gateway]) { $_destinations = $this->destinations[$reseller_id][$gateway]; $maxLength = $this->destinations_length[$reseller_id][$gateway]; $fCustomer="gateway=$gateway"; } else if ($this->destinations[$reseller_id]['default']) { $_destinations = $this->destinations[$reseller_id]['default']; $maxLength = $this->destinations_length[$reseller_id]['default']; $fCustomer="default"; } else if ($this->destinations[0][$subscriber]) { $_destinations = $this->destinations[0][$subscriber]; $maxLength = $this->destinations_length[0][$subscriber]; $fCustomer="subscriber=$subscriber"; } else if ($this->destinations[0][$domain]) { $_destinations = $this->destinations[0][$domain]; $maxLength = $this->destinations_length[0][$domain]; $fCustomer="domain=$domain"; } else if ($this->destinations[0][$gateway]) { $_destinations = $this->destinations[0][$gateway]; $maxLength = $this->destinations_length[0][$gateway]; $fCustomer="gateway=$gateway"; } else if ($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 { +class CDRS_unknown extends CDRS +{ function searchForm() { return; } } -class E164 { +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) { + 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 { - +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; } } } ?>