File: /home/posscale/subdomains/ccm/includes/php/report_shared.php
<?php
if ( ( array_key_exists( 'action', $_POST ) &&
($_POST['action'] == 'overall_report' ) ) == FALSE )
{
return;
}
ob_end_clean();
ob_start();
//print_r($_POST);
$where = "WHERE user_id = ".$_SESSION['user']['id'][0]." AND (";
for($i=0; $i<count($_POST['pbx']); $i++)
{
if($i!=0)
{
$where .= " OR ";
}
$where .= "pbx_id = ".$_POST['pbx'][$i];
}
$where .= ") AND ( ";
for($i=0; $i<count($_POST['ext']); $i++)
{
if($i!=0)
{
$where .= " OR ";
}
$ext_exp = explode("_", $_POST['ext'][$i], 2);
$where .= "(ext = ".$ext_exp[1]." AND pbx_id=".$ext_exp[0].")";
}
$where .= ") AND time >= ".strtotime($_POST['report_from'])." AND time <= ".strtotime($_POST['report_to']);
if(!$_POST['IncInternal']) // internal calls tick box, inserts statement into string when un-ticked...
{
//print_r("<h1>include internal calls: </h1>Box is NOT Ticked");
//print_r("<h1>include internal calls where script before: </h1>".$where);
$where .= " AND direction <> 'INTERNAL'";
//print_r("<h1>include internal calls where script AFTER: </h1>".$where);
}
$calls = db_query("
SELECT
*
FROM
calls
".$where."
ORDER BY
time");
if($calls['mysql_num_rows']<1)
{
die("<h1>NO RESULTS FOUND</h1>Please adjust results filter and try again");
}
///////////
// Total Call duration
for($i=0; $i<$calls['mysql_num_rows']; $i++)
{
$total_duration += $calls['duration'][$i];
}
///////////
// Average Call duration
$average_duration = $total_duration / $calls['mysql_num_rows'];
///////////
// Total Inbound Calls
$total_in = db_query_1("
SELECT
COUNT(*)
FROM
calls
".$where."
AND
direction = 'IN'");
///////////
// Total Outbound Calls
$total_out = db_query_1("
SELECT
COUNT(*)
FROM
calls
".$where."
AND
direction = 'OUT'");
///////////
// Longest Call
$long_call = db_query_1("
SELECT
duration
FROM
calls
".$where."
ORDER BY
duration DESC
LIMIT
1");
///////////
// Longest Outbound Call
$long_call_out = db_query_1("
SELECT
duration
FROM
calls
".$where."
AND
direction = 'OUT'
ORDER BY
duration DESC
LIMIT
1");
///////////
// Longest Inbound Call
$long_call_in = db_query_1("
SELECT
duration
FROM
calls
".$where."
AND
direction = 'In'
ORDER BY
duration DESC
LIMIT
1");
///////////
// Shortest Call
$short_call = db_query_1("
SELECT
duration
FROM
calls
".$where."
ORDER BY
duration ASC
LIMIT
1");
///////////
// Shortest Outbound Call
$short_call_out = db_query_1("
SELECT
duration
FROM
calls
".$where."
AND
direction = 'OUT'
ORDER BY
duration ASC
LIMIT
1");
///////////
// Shortest Inbound Call
$short_call_in = db_query_1("
SELECT
duration
FROM
calls
".$where."
AND
direction = 'In'
ORDER BY
duration ASC
LIMIT
1");
///////////
// Total Inbound Call duration
for($i=0; $i<$calls['mysql_num_rows']; $i++)
{
if($calls['direction'][$i]=="IN")
{
$total_in_duration += $calls['duration'][$i];
}
}
///////////
// Average Inbound Call duration
$average_in_duration = $total_in_duration / $total_in;
//WTF?
$total_out_duration = 0;
///////////
// Total Outbound Call duration
for($i=0; $i<$calls['mysql_num_rows']; $i++)
{
if($calls['direction'][$i]=="OUT")
{
$total_out_duration += $calls['duration'][$i];
}
}
///////////
// Average Outbound Call duration
$average_out_duration = $total_out_duration / $total_out;
$highest_total_calls = 0;
$highest_total_calls_in = 0;
$highest_total_calls_out = 0;
$highest_total_duration = 0;
$highest_total_duration_in = 0;
$highest_total_duration_out = 0;
for($i=0; $i<$calls['mysql_num_rows']; $i++)
{
$extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls']++;
if($extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls']>$highest_total_calls)
{
$highest_total_calls = $extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls'];
}
if($calls['direction'][$i]=="IN")
{
$extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls_in']++;
if($extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls_in']>$highest_total_calls_in)
{
$highest_total_calls_in = $extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls_in'];
}
}
if($calls['direction'][$i]=="OUT")
{
$extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls_out']++;
if($extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls_out']>$highest_total_calls_out)
{
$highest_total_calls_out = $extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_calls_out'];
}
}
$extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration']+=$calls['duration'][$i];
if($extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration']>$highest_total_duration)
{
$highest_total_duration = $extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration'];
}
if($calls['direction'][$i]=="IN")
{
$extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration_in']+=$calls['duration'][$i];
if($extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration_in']>$highest_total_duration_in)
{
$highest_total_duration_in = $extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration_in'];
}
}
if($calls['direction'][$i]=="OUT")
{
$extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration_out']+=$calls['duration'][$i];
if($extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration_out']>$highest_total_duration_out)
{
$highest_total_duration_out = $extension_results[$calls['pbx_id'][$i]][$calls['ext'][$i]]['total_duration_out'];
}
}
}
//hash array of arrays
$graph_data = array();
//Generate Graph data
foreach($extension_results as $pbx_id=> $ext_data)
{
$pbx_name = db_query_1("SELECT name FROM pbx WHERE id=".$pbx_id);
foreach($ext_data as $ext_no=> $data)
{
$graph_data['ext_nick'][] = db_query_1("SELECT nickname FROM extensions WHERE pbx_id=".$pbx_id." AND ext=".$ext_no);
$graph_data['total_calls'][] = $data['total_calls'];
$graph_data['total_calls_in'][] = $data['total_calls_in'];
$graph_data['total_calls_out'][] = $data['total_calls_out'];
$graph_data['total_duration'][]= $data['total_duration'];
$graph_data['total_duration_in'][] = $data['total_duration_in'];
$graph_data['total_duration_out'][] = $data['total_duration_out'];
}
}
//echo json_encode($graph_data, JSON_PRETTY_PRINT);
function send_csv_data( $call_data, $file_name='file.csv' ){
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$file_name}");
header("Expires: 0");
header("Pragma: public");
$fh = @fopen( 'php://output', 'w' );
echo json_encode( $call_data );
fputcsv( $fh, array( 'Outbound', 'Inbound', 'Both', 'Type' ));
fputcsv( $fh, array( $call_data['total_out'], $call_data['total_in'], $call_data['calls']['mysql_num_rows'], 'Total Calls:' ) );
fputcsv( $fh, array( sec2hms( $call_data['total_out_duration'] ), sec2hms( $call_data['total_in_duration'] ), sec2hms( $call_data['total_duration'] ), 'Total Duration' ) );
fputcsv( $fh, array( sec2hms( $call_data['average_out_duration'] ), sec2hms( $call_data['average_in_duration'] ), sec2hms( $call_data['average_duration'] ), 'Average Duration' ) );
fputcsv( $fh, array( sec2hms( $call_data['long_call_out'] ), sec2hms( $call_data['long_call_in'] ), sec2hms( $call_data['long_call']), 'Longest Call' ) );
fputcsv( $fh, array( sec2hms( $call_data['short_call_out'] ), sec2hms( $call_data['short_call_in'] ), sec2hms( $call_data['short_call'] ), 'Shortest Call ' ) );
//space
fputcsv( $fh, array());
//Graph section.
fputcsv( $fh, array( 'Operator','Total Calls','Total Inbound','Total Outbound','Total Duration','Duration Inbound','Duration Outbound' ) );
$graph_data = $call_data['graph_data'];
//echo json_encode($graph_data, JSON_PRETTY_PRINT);
for( $i=0; $i< count($graph_data['ext_nick']); $i++ )
{
fputcsv( $fh,
array(
$graph_data['ext_nick'][$i],
$graph_data['total_calls'][$i],
$graph_data['total_calls_in'][$i],
$graph_data['total_calls_out'][$i],
sec2hms($graph_data['total_duration'][$i]+0),
sec2hms($graph_data['total_duration_in'][$i]+0),
sec2hms($graph_data['total_duration_out'][$i]+0)
)
);
}
//put space
fputcsv( $fh, array());
fputcsv( $fh, array( '<>', 'Date', 'Time', 'Duration', 'Extension', 'Number' ) );
$calls = $call_data['calls'];
//echo json_encode($calls, JSON_PRETTY_PRINT);
for($i=0; $i<$calls['mysql_num_rows']; $i++)
//for($i=0; $i<89; $i++)
{
$row = array( $calls['direction'][$i],
date("F j Y", $calls['time'][$i]-$calls['duration'][$i]),
date("g:i a", $calls['time'][$i]-$calls['duration'][$i]),
$calls['ext'][$i],
$calls['number'][$i] );
//$row = array( $calls['direction'][$i],
//$calls['time'][$i]-$calls['duration'][$i],
//$calls['time'][$i]-$calls['duration'][$i],
//$calls['ext'][$i],
//$calls['number'][$i] );
//$row = array( 'a', 'b', 'c');
fputcsv( $fh, $row );
}
fclose( $fh );
}
if( array_key_exists( 'csv_hidden', $_POST ) && ( $_POST['csv_hidden'] == 'CSV' ) ) {
$csv_data = array(
'total_duration' => $total_duration,
'total_out_duration' => $total_out_duration,
'total_out' => $total_out,
'total_in_duration' => $total_in_duration,
'total_in' => $total_in,
'long_call' => $long_call,
'long_call_in' => $long_call_in,
'long_call_out' => $long_call_out,
'short_call' => $short_call,
'short_call_in' => $short_call_in,
'short_call_out' => $short_call_out,
'average_duration' => $average_duration,
'average_out_duration' => $average_out_duration,
'average_in_duration' => $average_in_duration,
'calls' => $calls,
'graph_data' => $graph_data
);
send_csv_data( $csv_data, 'file.csv' );
exit();
}
?>