+
+
+
+ date('t', strtotime("$startyear-$startmonth")))) {
+ $startday = $_POST['startday'] = date('t', strtotime("$startyear-$startmonth"));
+} else {
+ $startday = sprintf('%02d',$_POST['startday']);
+}
+$starthour = empty($_POST['starthour']) ? '00' : sprintf('%02d',$_POST['starthour']);
+$startmin = empty($_POST['startmin']) ? '00' : sprintf('%02d',$_POST['startmin']);
+
+$startdate = "'$startyear-$startmonth-$startday $starthour:$startmin:00'";
+$start_timestamp = mktime( $starthour, $startmin, 59, $startmonth, $startday, $startyear );
+
+$endmonth = empty($_POST['endmonth']) ? date('m') : $_POST['endmonth'];
+$endyear = empty($_POST['endyear']) ? date('Y') : $_POST['endyear'];
+
+if (empty($_POST['endday']) || (isset($_POST['endday']) && ($_POST['endday'] > date('t', strtotime("$endyear-$endmonth-01"))))) {
+ $endday = $_POST['endday'] = date('t', strtotime("$endyear-$endmonth"));
+} else {
+ $endday = sprintf('%02d',$_POST['endday']);
+}
+$endhour = empty($_POST['endhour']) ? '23' : sprintf('%02d',$_POST['endhour']);
+$endmin = empty($_POST['endmin']) ? '59' : sprintf('%02d',$_POST['endmin']);
+
+$enddate = "'$endyear-$endmonth-$endday $endhour:$endmin:59'";
+$end_timestamp = mktime( $endhour, $endmin, 59, $endmonth, $endday, $endyear );
+
+#
+# asterisk regexp2sqllike
+#
+if ( !isset($_POST['src']) ) {
+ $src_number = NULL;
+} else {
+ $src_number = cdr_asteriskregexp2sqllike( 'src', '' );
+}
+
+if ( !isset($_POST['dst']) ) {
+ $dst_number = NULL;
+} else {
+ $dst_number = cdr_asteriskregexp2sqllike( 'dst', '' );
+}
+
+$date_range = "calldate BETWEEN $startdate AND $enddate";
+$mod_vars['channel'][] = !isset($_POST['channel']) ? NULL : $_POST['channel'];
+$mod_vars['channel'][] = empty($_POST['channel_mod']) ? NULL : $_POST['channel_mod'];
+$mod_vars['channel'][] = empty($_POST['channel_neg']) ? NULL : $_POST['channel_neg'];
+$mod_vars['src'][] = $src_number;
+$mod_vars['src'][] = empty($_POST['src_mod']) ? NULL : $_POST['src_mod'];
+$mod_vars['src'][] = empty($_POST['src_neg']) ? NULL : $_POST['src_neg'];
+$mod_vars['clid'][] = !isset($_POST['clid']) ? NULL : $_POST['clid'];
+$mod_vars['clid'][] = empty($_POST['clid_mod']) ? NULL : $_POST['clid_mod'];
+$mod_vars['clid'][] = empty($_POST['clid_neg']) ? NULL : $_POST['clid_neg'];
+$mod_vars['did'][] = !isset($_POST['did']) ? NULL : $_POST['did'];
+$mod_vars['did'][] = empty($_POST['did_mod']) ? NULL : $_POST['did_mod'];
+$mod_vars['did'][] = empty($_POST['did_neg']) ? NULL : $_POST['did_neg'];
+$mod_vars['dstchannel'][] = !isset($_POST['dstchannel']) ? NULL : $_POST['dstchannel'];
+$mod_vars['dstchannel'][] = empty($_POST['dstchannel_mod']) ? NULL : $_POST['dstchannel_mod'];
+$mod_vars['dstchannel'][] = empty($_POST['dstchannel_neg']) ? NULL : $_POST['dstchannel_neg'];
+$mod_vars['dst'][] = $dst_number;
+$mod_vars['dst'][] = empty($_POST['dst_mod']) ? NULL : $_POST['dst_mod'];
+$mod_vars['dst'][] = empty($_POST['dst_neg']) ? NULL : $_POST['dst_neg'];
+$mod_vars['userfield'][] = !isset($_POST['userfield']) ? NULL : $_POST['userfield'];
+$mod_vars['userfield'][] = empty($_POST['userfield_mod']) ? NULL : $_POST['userfield_mod'];
+$mod_vars['userfield'][] = empty($_POST['userfield_neg']) ? NULL : $_POST['userfield_neg'];
+$mod_vars['accountcode'][] = !isset($_POST['accountcode']) ? NULL : $_POST['accountcode'];
+$mod_vars['accountcode'][] = empty($_POST['accountcode_mod']) ? NULL : $_POST['accountcode_mod'];
+$mod_vars['accountcode'][] = empty($_POST['accountcode_neg']) ? NULL : $_POST['accountcode_neg'];
+$result_limit = !isset($_POST['limit']) ? $db_result_limit : $_POST['limit'];
+
+foreach ($mod_vars as $key => $val) {
+ if (is_blank($val[0])) {
+ unset($_POST[$key.'_mod']);
+ $$key = NULL;
+ } else {
+ $pre_like = '';
+ if ( $val[2] == 'true' ) {
+ $pre_like = ' NOT ';
+ }
+ switch ($val[1]) {
+ case "contains":
+ $$key = "AND $key $pre_like LIKE '%$val[0]%'";
+ break;
+ case "ends_with":
+ $$key = "AND $key $pre_like LIKE '%$val[0]'";
+ break;
+ case "exact":
+ if ( $val[2] == 'true' ) {
+ $$key = "AND $key != '$val[0]'";
+ } else {
+ $$key = "AND $key = '$val[0]'";
+ }
+ break;
+ case "asterisk-regexp":
+ $ast_dids = preg_split('/\s*,\s*/', $val[0], -1, PREG_SPLIT_NO_EMPTY);
+ $ast_key = '';
+ foreach ($ast_dids as $did) {
+ if (strlen($ast_key) > 0 ) {
+ if ( $pre_like == ' NOT ' ) {
+ $ast_key .= " and ";
+ } else {
+ $ast_key .= " or ";
+ }
+ if ( '_' == substr($did,0,1) ) {
+ $did = substr($did,1);
+ }
+ }
+ $ast_key .= " $key $pre_like RLIKE '^$did\$'";
+ }
+ $$key = "AND ( $ast_key )";
+ break;
+ case "begins_with":
+ default:
+ $$key = "AND $key $pre_like LIKE '$val[0]%'";
+ }
+ }
+}
+
+if ( isset($_POST['disposition_neg']) && $_POST['disposition_neg'] == 'true' ) {
+ $disposition = (empty($_POST['disposition']) || $_POST['disposition'] == 'all') ? NULL : "AND disposition != '$_POST[disposition]'";
+} else {
+ $disposition = (empty($_POST['disposition']) || $_POST['disposition'] == 'all') ? NULL : "AND disposition = '$_POST[disposition]'";
+}
+
+$duration = (!isset($_POST['dur_min']) || is_blank($_POST['dur_max'])) ? NULL : "AND duration BETWEEN '$_POST[dur_min]' AND '$_POST[dur_max]'";
+$order = empty($_POST['order']) ? 'ORDER BY calldate' : "ORDER BY $_POST[order]";
+$sort = empty($_POST['sort']) ? 'DESC' : $_POST['sort'];
+$group = empty($_POST['group']) ? 'day' : $_POST['group'];
+
+// Build the "WHERE" part of the query
+$where = "WHERE $date_range $channel $dstchannel $src $clid $did $dst $userfield $accountcode $disposition $duration";
+
+if ( isset($_POST['need_csv']) && $_POST['need_csv'] == 'true' ) {
+ $query = "(SELECT calldate, clid, did, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, userfield FROM $db_name.$db_table_name $where $order $sort LIMIT $result_limit)";
+ $resultcsv = $dbcdr->getAll($query, DB_FETCHMODE_ASSOC);
+ cdr_export_csv($resultcsv);
+}
+
+if ( isset($_POST['need_html']) && $_POST['need_html'] == 'true' ) {
+ $query = "SELECT `calldate`, `clid`, `did`, `src`, `dst`, `dcontext`, `channel`, `dstchannel`, `lastapp`, `lastdata`, `duration`, `billsec`, `disposition`, `amaflags`, `accountcode`, `uniqueid`, `userfield`, unix_timestamp(calldate) as `call_timestamp`, `recordingfile` FROM $db_name.$db_table_name $where $order $sort LIMIT $result_limit";
+ $results = $dbcdr->getAll($query, DB_FETCHMODE_ASSOC);
+}
+if ( isset($results) ) {
+ $tot_calls_raw = sizeof($results);
+} else {
+ $tot_calls_raw = 0;
+}
+if ( $tot_calls_raw ) {
+ echo "
"._("Call Detail Record - Search Returned")." ".$tot_calls_raw." "._("Calls")."
";
+ echo "
";
+
+ $i = $h_step - 1;
+ $id = -1; // tracker for recording index
+ foreach($results as $row) {
+ ++$id; // Start at table row 1
+ ++$i;
+ if ($i == $h_step) {
+ ?>
+
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+  |
+  |
+
+ \n";
+ cdr_formatCallDate($row['calldate']);
+ cdr_formatRecordingFile($recordingfile, $row['recordingfile'], $id);
+ cdr_formatUniqueID($row['uniqueid']);
+ cdr_formatChannel($row['channel']);
+ cdr_formatSrc($row['src'], $row['clid']);
+ cdr_formatDID($row['did']);
+ cdr_formatApp($row['lastapp'], $row['lastdata']);
+ cdr_formatDst($row['dst'], $row['dcontext']);
+ cdr_formatChannel($row['dstchannel']);
+ cdr_formatDisposition($row['disposition'], $row['amaflags']);
+ cdr_formatDuration($row['duration'], $row['billsec']);
+ cdr_formatUserField($row['userfield']);
+ cdr_formatAccountCode($row['accountcode']);
+ echo " | \n";
+ echo " | \n";
+ echo " \n";
+ }
+ echo "
";
+}
+?>
+
+
+';
+
+//NEW GRAPHS
+$group_by_field = $group;
+// ConcurrentCalls
+$group_by_field_php = array( '', 32, '' );
+
+switch ($group) {
+ case "disposition_by_day":
+ $graph_col_title = 'Disposition by day';
+ $group_by_field_php = array('%Y-%m-%d / ',17,'');
+ $group_by_field = "CONCAT(DATE_FORMAT(calldate, '$group_by_field_php[0]'),disposition)";
+ break;
+ case "disposition_by_hour":
+ $graph_col_title = 'Disposition by hour';
+ $group_by_field_php = array( '%Y-%m-%d %H / ', 20, '' );
+ $group_by_field = "CONCAT(DATE_FORMAT(calldate, '$group_by_field_php[0]'),disposition)";
+ break;
+ case "disposition":
+ $graph_col_title = 'Disposition';
+ break;
+ case "dcontext":
+ $graph_col_title = 'Destination context';
+ break;
+ case "accountcode":
+ $graph_col_title = _("Account Code");
+ break;
+ case "dst":
+ $graph_col_title = _("Destination Number");
+ break;
+ case "src":
+ $graph_col_title = _("Source Number");
+ break;
+ case "userfield":
+ $graph_col_title = _("User Field");
+ break;
+ case "hour":
+ $group_by_field_php = array( '%Y-%m-%d %H', 13, '' );
+ $group_by_field = "DATE_FORMAT(calldate, '$group_by_field_php[0]')";
+ $graph_col_title = _("Hour");
+ break;
+ case "hour_of_day":
+ $group_by_field_php = array('%H',2,'');
+ $group_by_field = "DATE_FORMAT(calldate, '$group_by_field_php[0]')";
+ $graph_col_title = _("Hour of day");
+ break;
+ case "week":
+ $group_by_field_php = array('%V',2,'');
+ $group_by_field = "DATE_FORMAT(calldate, '$group_by_field_php[0]') ";
+ $graph_col_title = _("Week ( Sun-Sat )");
+ break;
+ case "month":
+ $group_by_field_php = array('%Y-%m',7,'');
+ $group_by_field = "DATE_FORMAT(calldate, '$group_by_field_php[0]')";
+ $graph_col_title = _("Month");
+ break;
+ case "day_of_week":
+ $group_by_field_php = array('%w - %A',20,'');
+ $group_by_field = "DATE_FORMAT( calldate, '%W' )";
+ $graph_col_title = _("Day of week");
+ break;
+ case "minutes1":
+ $group_by_field_php = array( '%Y-%m-%d %H:%M', 16, '' );
+ $group_by_field = "DATE_FORMAT(calldate, '%Y-%m-%d %H:%i')";
+ $graph_col_title = _("Minute");
+ break;
+ case "minutes10":
+ $group_by_field_php = array('%Y-%m-%d %H:%M',15,'0');
+ $group_by_field = "CONCAT(SUBSTR(DATE_FORMAT(calldate, '%Y-%m-%d %H:%i'),1,15), '0')";
+ $graph_col_title = _("10 Minutes");
+ break;
+ case "day":
+ default:
+ $group_by_field_php = array('%Y-%m-%d',10,'');
+ $group_by_field = "DATE_FORMAT(calldate, '$group_by_field_php[0]')";
+ $graph_col_title = _("Day");
+}
+
+if ( isset($_POST['need_chart']) && $_POST['need_chart'] == 'true' ) {
+ $query2 = "SELECT $group_by_field AS group_by_field, count(*) AS total_calls, sum(duration) AS total_duration FROM $db_name.$db_table_name $where GROUP BY group_by_field ORDER BY group_by_field ASC LIMIT $result_limit";
+ $result2 = $dbcdr->getAll($query2, DB_FETCHMODE_ASSOC);
+
+ $tot_calls = 0;
+ $tot_duration = 0;
+ $max_calls = 0;
+ $max_duration = 0;
+ $tot_duration_secs = 0;
+ $result_array = array();
+ foreach($result2 as $row) {
+ $tot_duration_secs += $row['total_duration'];
+ $tot_calls += $row['total_calls'];
+ if ( $row['total_calls'] > $max_calls ) {
+ $max_calls = $row['total_calls'];
+ }
+ if ( $row['total_duration'] > $max_duration ) {
+ $max_duration = $row['total_duration'];
+ }
+ array_push($result_array,$row);
+ }
+ $tot_duration = sprintf('%02d', intval($tot_duration_secs/60)).':'.sprintf('%02d', intval($tot_duration_secs%60));
+
+ if ( $tot_calls ) {
+ $html = "
"._("Call Detail Record - Call Graph by")." ".$graph_col_title."
";
+ $html .= "| ". $graph_col_title . " | ";
+ $html .= ""._("Total Calls").": ". $tot_calls ." / "._("Max Calls").": ". $max_calls ." / "._("Total Duration").": ". $tot_duration ." | ";
+ $html .= ""._("Average Call Time")." | ";
+ $html .= "![\"CDR](\"images/scrollup.gif\") | ";
+ $html .= "![\"CDR](\"images/scrolldown.gif\") | ";
+ $html .= "
|---|
";
+ echo $html;
+
+ foreach ($result_array as $row) {
+ $avg_call_time = sprintf('%02d', intval(($row['total_duration']/$row['total_calls'])/60)).':'.sprintf('%02d', intval($row['total_duration']/$row['total_calls']%60));
+ $bar_calls = $row['total_calls']/$max_calls*100;
+ $percent_tot_calls = intval($row['total_calls']/$tot_calls*100);
+ $bar_duration = $row['total_duration']/$max_duration*100;
+ $percent_tot_duration = intval($row['total_duration']/$tot_duration_secs*100);
+ $html_duration = sprintf('%02d', intval($row['total_duration']/60)).':'.sprintf('%02d', intval($row['total_duration']%60));
+ echo " \n";
+ echo " | ".$row['group_by_field']." | ".$row['total_calls']." - $percent_tot_calls% $html_duration - $percent_tot_duration% | $avg_call_time | \n";
+ echo " | \n";
+ echo " | \n";
+ echo "
\n";
+ }
+ echo "
";
+ }
+}
+if ( isset($_POST['need_chart_cc']) && $_POST['need_chart_cc'] == 'true' ) {
+ $date_range = "( (calldate BETWEEN $startdate AND $enddate) or (calldate + interval duration second BETWEEN $startdate AND $enddate) or ( calldate + interval duration second >= $enddate AND calldate <= $startdate ) )";
+ $where = "WHERE $date_range $channel $dstchannel $src $clid $dst $userfield $accountcode $disposition $duration";
+
+ $tot_calls = 0;
+ $max_calls = 0;
+ $result_array_cc = array();
+ $result_array = array();
+ if ( strpos($group_by_field,'DATE_FORMAT') === false ) {
+ /* not date time fields */
+ $query3 = "SELECT $group_by_field AS group_by_field, count(*) AS total_calls, unix_timestamp(calldate) AS ts, duration FROM $db_name.$db_table_name $where GROUP BY group_by_field, unix_timestamp(calldate) ORDER BY group_by_field ASC LIMIT $result_limit";
+ $result3 = $dbcdr->getAll($query3, DB_FETCHMODE_ASSOC);
+ $group_by_str = '';
+ foreach($result3 as $row) {
+ if ( $group_by_str != $row['group_by_field'] ) {
+ $group_by_str = $row['group_by_field'];
+ $result_array = array();
+ }
+ for ( $i=$row['ts']; $i<=$row['ts']+$row['duration']; ++$i ) {
+ if ( isset($result_array[ "$i" ]) ) {
+ $result_array[ "$i" ] += $row['total_calls'];
+ } else {
+ $result_array[ "$i" ] = $row['total_calls'];
+ }
+ if ( $max_calls < $result_array[ "$i" ] ) {
+ $max_calls = $result_array[ "$i" ];
+ }
+ if ( ! isset($result_array_cc[ $row['group_by_field'] ]) || $result_array_cc[ $row['group_by_field'] ][1] < $result_array[ "$i" ] ) {
+ $result_array_cc[$row['group_by_field']][0] = $i;
+ $result_array_cc[$row['group_by_field']][1] = $result_array[ "$i" ];
+ }
+ }
+ $tot_calls += $row['total_calls'];
+ }
+ } else {
+ /* data fields */
+ $query3 = "SELECT unix_timestamp(calldate) AS ts, duration FROM $db_name.$db_table_name $where ORDER BY unix_timestamp(calldate) ASC LIMIT $result_limit";
+ $result3 = $dbcdr->getAll($query3, DB_FETCHMODE_ASSOC);
+ $group_by_str = '';
+ foreach($result3 as $row) {
+ $group_by_str_cur = substr(strftime($group_by_field_php[0],$row['ts']),0,$group_by_field_php[1]) . $group_by_field_php[2];
+ if ( $group_by_str_cur != $group_by_str ) {
+ if ( $group_by_str ) {
+ for ( $i=$start_timestamp; $i<$row['ts']; ++$i ) {
+ if ( ! isset($result_array_cc[ "$group_by_str" ]) || ( isset($result_array["$i"]) && $result_array_cc[ "$group_by_str" ][1] < $result_array["$i"] ) ) {
+ $result_array_cc[ "$group_by_str" ][0] = $i;
+ $result_array_cc[ "$group_by_str" ][1] = isset($result_array["$i"]) ? $result_array["$i"] : 0;
+ }
+ unset( $result_array[$i] );
+ }
+ $start_timestamp = $row['ts'];
+ }
+ $group_by_str = $group_by_str_cur;
+ }
+ for ( $i=$row['ts']; $i<=$row['ts']+$row['duration']; ++$i ) {
+ if ( isset($result_array["$i"]) ) {
+ ++$result_array["$i"];
+ } else {
+ $result_array["$i"]=1;
+ }
+ if ( $max_calls < $result_array["$i"] ) {
+ $max_calls = $result_array["$i"];
+ }
+ }
+ $tot_calls++;
+ }
+ for ( $i=$start_timestamp; $i<=$end_timestamp; ++$i ) {
+ $group_by_str = substr(strftime($group_by_field_php[0],$i),0,$group_by_field_php[1]) . $group_by_field_php[2];
+ if ( ! isset($result_array_cc[ "$group_by_str" ]) || ( isset($result_array["$i"]) && $result_array_cc[ "$group_by_str" ][1] < $result_array["$i"] ) ) {
+ $result_array_cc[ "$group_by_str" ][0] = $i;
+ $result_array_cc[ "$group_by_str" ][1] = isset($result_array["$i"]) ? $result_array["$i"] : 0;
+ }
+ }
+ }
+ if ( $tot_calls ) {
+ $html = "
"._("Call Detail Record - Concurrent Calls by")." ".$graph_col_title."
";
+ $html .= "| ". $graph_col_title . " | ";
+ $html .= ""._("Total Calls").": ". $tot_calls ." / "._("Max Calls").": ". $max_calls ." | ";
+ $html .= ""._("Time")." | ";
+ $html .= "
|---|
";
+ echo $html;
+
+ ksort($result_array_cc);
+
+ foreach ( array_keys($result_array_cc) as $group_by_key ) {
+ $full_time = strftime( '%Y-%m-%d %H:%M:%S', $result_array_cc[ "$group_by_key" ][0] );
+ $group_by_cur = $result_array_cc[ "$group_by_key" ][1];
+ $bar_calls = $group_by_cur/$max_calls*100;
+ echo " \n";
+ echo " | $group_by_key | $group_by_cur | $full_time | \n";
+ echo "
\n";
+ }
+
+ echo "
";
+ }
+}
+
+?>
+
![\"Call](\"assets/cdr/images/cdr_sound.png\")