|
Calls |
Seconds |
Minutes |
Hours | ";
if ($this->rating) {
print "Price | ";
}
print "
Success |
Failure |
$group_byPrint |
Description |
Action |
";
} else {
print "id,Calls,Seconds,Minutes,Hours,Price,Success(%),Success(calls),Failure(%),Failure(calls),$group_byPrint,Description\n";
}
}
function showTableHeaderSubscriber() {
$this->showTableHeader();
}
function showTableHeader() {
print "
|
| Date and time |
Username |
Source |
In |
Destination |
Out |
Dur |
Price |
Context |
Channel in |
Channel out |
Account |
App (Duration) |
";
}
function showExportHeader() {
print "id,StartTime,Username,Source,Destination,DestinationId,DestinationName,Duration,Price,Context,ChannelIn,ChannelOut,Account,Application,ApplicationDuration\n";
}
function showSubscriber() {
$this->show();
}
function show() {
global $perm;
foreach ($this->FormElements as $_el) {
${$_el} = $_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);
}
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);
}
$begin_datetime_url=urlencode($begin_datetime_timestamp);
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);
}
$end_datetime_url=urlencode($end_datetime_timestamp);
} 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;
// build an url to be able to log and refine the query
$this->url="?cdr_source=$this->cdr_source&cdr_table=$cdr_table";
$this->url=$this->url."&order_by=$order_by&order_type=$order_type";
$this->url=$this->url."&begin_datetime=$begin_datetime_url";
$this->url=$this->url."&end_datetime=$end_datetime_url";
if ($this->CDRTool['filter']['aNumber']) {
$this->url = $this->url."&aNumbers=".urlencode($this->CDRTool['filter']['aNumber']);
$aNumbers = explode(" ",$this->CDRTool['filter']['aNumber']);
} else if ($aNumbers) {
$this->url = $this->url."&aNumbers=".urlencode($aNumbers);
$aNumbers = explode(" ",$aNumbers);
}
if ($this->CDRTool['filter']['after_date']) {
$where .= sprintf(" and calldate >= '%s' ",$this->CDRTool['filter']['after_date']);
}
$where = " calldate > '$begin_datetime' and calldate <= '$end_datetime' ";
$a_number=trim($a_number);
if ($this->CDRTool['filter']['aNumber']) {
$where .= "
and $this->aNumberField in (" ;
$rr=0;
foreach ($aNumbers as $_aNumber) {
$_aNumber=trim($_aNumber);
if (strlen($_aNumber)) {
if ($rr) $where .= ", ";
$where .= " '$_aNumber'";
$rr++;
}
}
$where .= ") ";
} else if ($aNumbers) {
$where .= "
and $this->aNumberField in (" ;
$rr=0;
foreach ($aNumbers as $_aNumber) {
$_aNumber=trim($_aNumber);
if (strlen($_aNumber)) {
if ($rr) $where .= ", ";
$where .= " '$_aNumber'";
$rr++;
}
}
$where .= ") ";
} else if ($a_number_comp == "empty") {
$where .= " and $this->aNumberField = ''";
$this->url=$this->url."&a_number_comp=$a_number_comp";
} else if (strlen($a_number)) {
$a_number=urldecode($a_number);
if (!$a_number_comp) $a_number_comp="equal";
$a_number_encoded=urlencode($a_number);
$a_number_encoded=urlencode($a_number);
$this->url="$this->url"."&a_number=$a_number_encoded";
if ($a_number_comp=="begin") {
$where .= " and ($this->aNumberField like '".addslashes($a_number)."%'";
$s=1;
} elseif ($a_number_comp=="contain") {
$where .= " and ($this->aNumberField like '%".addslashes($a_number)."%'";
$s=1;
} elseif ($a_number_comp=="equal") {
$where .= " and ($this->aNumberField = '".addslashes($a_number)."'";
$s=1;
}
if ($s) {
$where .= ")";
}
$this->url=$this->url."&a_number_comp=$a_number_comp";
}
if ($channel_in) {
$channel_in_encoded=urlencode($channel_in);
$where = "$where"." and $this->channelInField = '".addslashes($channel_in)."'";
$this->url="$this->url"."&channel_in=$channel_in_encoded";
}
if ($applicationType) {
$where = "$where"." and $this->applicationTypeField like '".addslashes($applicationType)."%'";
$applicationType_encoded=urlencode($applicationType);
$this->url="$this->url"."&applicationType=$applicationType_encoded";
}
if ($UserName_comp != "empty") {
$UserName=trim($UserName);
if ($UserName) {
$UserName=urldecode($UserName);
if (!$UserName_comp) {
$UserName_comp="begin";
}
$UserName_encoded=urlencode($UserName);
if ($UserName_comp=="begin") {
$where .= " and $this->usernameField like '".addslashes($UserName)."%'";
} elseif ($UserName_comp=="contain") {
$where .= " and $this->usernameField like '%".addslashes($UserName)."%'";
} elseif ($UserName_comp=="equal") {
$where .= " and $this->usernameField = '".addslashes($UserName)."'";
}
$this->url="$this->url"."&UserName=$UserName_encoded&UserName_comp=$UserName_comp";
}
} else {
$where .= " and $this->usernameField = ''";
$this->url="$this->url"."&UserName_comp=$UserName_comp";
}
if ($context) {
$context_encoded=urlencode($context);
$where = "$where"." and $this->domainField = '".addslashes($context)."'";
$this->url="$this->url"."&context=$context_encoded";
}
if ($channel_out) {
$channel_out_encoded=urlencode($channel_out);
$where = "$where"." and $this->channelOutField = '".addslashes($channel_out)."'";
$this->url="$this->url"."&channel_out=$channel_out_encoded";
}
if (strlen($c_number)) {
# Trim content of dest_form - allow only digits
if ($c_number_comp=="begin") {
$where = "$where"." and $this->cNumberField like '".addslashes($c_number)."%'";
} elseif ($c_number_comp=="equal") {
$where = "$where"." and $this->cNumberField = '".addslashes($c_number)."'";
} elseif ($c_number_comp=="contain") {
$where .= " and $this->cNumberField like '%".addslashes($c_number)."%'";
} else {
$where = "$where"." and $this->cNumberField like '%".addslashes($c_number)."%'";
}
$c_number_encoded=urlencode($c_number);
$this->url=$this->url."&c_number=$c_number_encoded&c_number_comp=$c_number_comp";
}
if ($DestinationId) {
if ($DestinationId=="empty") {
$DestinationIdSQL="";
} else {
$DestinationIdSQL=$DestinationId;
}
$where = "$where"." and $this->DestinationIdField = '".addslashes($DestinationIdSQL)."'";
$DestinationId_encoded=urlencode($DestinationId);
$this->url="$this->url"."&DestinationId=$DestinationId_encoded";
}
if ($duration) {
if (preg_match("/\d+/",$duration) ) {
$where .= " and ($this->durationField > 0 and $this->durationField $duration) ";
} elseif ($duration == "zero") {
$where = "$where"." and $this->durationField = 0";
} elseif ($duration == "nonzero") {
$where = "$where"." and $this->durationField > 0";
}
$this->url="$this->url"."&duration=$duration";
}
$this->url="$this->url"."&maxrowsperpage=$this->maxrowsperpage";
$url_calls = $this->scriptFile.$this->url."&action=search";
if ($group_by) {
$this->url="$this->url"."&group_by=$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 ($group_by) {
$this->group_byOrig=$group_by;
if ($group_by=="hour") {
$group_by="HOUR($this->startTimeField)";
} else if (preg_match("/^DAY/",$group_by)) {
$group_by="$group_by($this->startTimeField)";
}
if (!$perm->have_perm("statistics")) {
print "You do not have the right for statistics.";
return 0 ;
}
$this->group_by=$group_by;
$query= "
select sum($this->durationField) as duration, SEC_TO_TIME(sum($this->durationField)) as duration_print,
count($group_by) as calls, $group_by
from $cdr_table
where $where
group by $group_by
";
} else {
$query = "select count(*) as records from $cdr_table where ".$where;
}
dprint("$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 {
$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=$this->next;
}
$j=0;
$z=0;
if ($rows>0) {
if ($UnNormalizedCalls=$this->getUnNormalized($where,$cdr_table)) {
dprint("Normalize calls");
$this->NormalizeCDRS($where,$cdr_table);
if (!$this->export && $this->status['normalized'] ) {
printf (" Found %d CDRs for normalization. ",$this->status['normalized']);
}
}
$this->$rows=$rows;
if ($rows > $this->maxrowsperpage) {
$maxrows=$this->maxrowsperpage+$this->next;
if ($maxrows > $rows) {
$maxrows=$rows;
$prev_rows=$maxrows;
}
} else {
$maxrows=$rows;
}
if ($group_by) {
if ($order_by=="group_by") {
$order_by1=$group_by;
} else {
if ($order_by == $this->priceField ||
$order_by == "zeroP" ||
$order_by == "nonzeroP" ||
$order_by == $this->durationField ) {
$order_by1 = $order_by;
} else {
$order_by1 = "calls";
}
}
$query= "
select
sum($this->durationField) as $this->durationField,
SEC_TO_TIME(sum($this->durationField)) as hours,
count($group_by) as calls, ";
if ($this->priceField) {
$query.=" sum($this->priceField) as price, ";
}
$query.="
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.="
$group_by as mygroup
from $cdr_table
where $where
group by $group_by
order by $order_by1 $order_type
limit $i, $this->maxrowsperpage
";
dprint($query);
$this->CDRdb->query($query);
$this->showTableHeaderStatistics();
while ($i<$maxrows) {
$found=$i+1;
$this->CDRdb->next_record();
$seconds =$this->CDRdb->Record[$this->durationField];
$seconds_print =number_format($this->CDRdb->Record[$this->durationField],0);
$minutes =number_format($this->CDRdb->Record[$this->durationField]/60,0,"","");
$minutes_print =number_format($this->CDRdb->Record[$this->durationField]/60,0);
$hours =$this->CDRdb->Record['hours'];
$calls =$this->CDRdb->Record['calls'];
$mygroup =$this->CDRdb->Record['mygroup'];
if ($this->rating && $this->priceField) {
$price =$this->CDRdb->Record['price'];
}
$zero =$this->CDRdb->Record['zero'];
$nonzero =$this->CDRdb->Record['nonzero'];
$success =number_format($nonzero/$calls*100,2,".","");
$failure =number_format($zero/$calls*100,2,".","");
$rr=floor($found/2);
$mod=$found-$rr*2;
if ($mod ==0) {
$inout_color="lightgrey";
} else {
$inout_color="white";
}
$mygroup_enc=urlencode($mygroup);
$traceValue="";
if ($group_by==$this->DestinationIdField) {
if ($this->CDRTool['filter']['aNumber']) {
$description=$this->destinations[$this->CDRTool['filter']['aNumber']][$mygroup];
} else if ($this->CDRTool['filter']['domain']) {
$description=$this->destinations[$this->CDRTool['filter']['domain']][$mygroup];
} else {
$description=$this->destinations["default"][$mygroup];
}
} else if ($group_by==$this->aNumberField) {
$traceField="a_number";
} else if ($group_by==$this->usernameField) {
$traceField="username";
} else if ($group_by==$this->cNumberField) {
$traceField="c_number";
}
if ($mygroup) {
$traceValue=$mygroup;
} else {
$traceValue="empty";
}
if (!$traceField) {
$traceField = $group_by;
}
if (!$traceValue) {
$traceValue = $mygroup;
}
$mygroup_print = $mygroup;
$traceValue_enc=urlencode($traceValue);
if (!$this->export) {
$pricePrint=number_format($price,4);
print "
| $found |
$calls |
$seconds_print |
$minutes_print |
$hours |
$pricePrint |
$success% |
($nonzero calls) |
$failure% |
($zero calls) |
$mygroup_print |
$description |
Display calls |
";
} else {
print "$found,";
print "$calls,";
print "$seconds,";
print "$minutes,";
print "$hours,";
print "$price,";
print "$success,";
print "$nonzero,";
print "$failure,";
print "$zero,";
print "$mygroup,";
print "$description";
print "\n";
}
$i++;
}
if (!$this->export) {
print "
|
";
}
} else {
if (!$order_by) {
$order_by=calldate;
}
$query = "select *,unix_timestamp(calldate) as timestamp
from $cdr_table
where $where
order by $order_by $order_type
limit $i,$this->maxrowsperpage";
dprint("$query");
$this->CDRdb->query($query);
if (!$this->export) {
$this->showTableHeader();
} else {
$this->showExportHeader();
}
while ($i<$maxrows) {
global $found;
$found=$i+1;
$this->CDRdb->next_record();
$Structure=$this->_readCDRFieldsFromDB();
$CDR = new $this->CDR_class($this, $Structure);
if (!$this->export) {
$CDR->show();
} else {
$CDR->export();
}
$i++;
}
if (!$this->export) {
print "
|