Concurrent calls script

Asterisk Asterisk General
1

Hi everyone. I need a little help with this idea of making graphs about how many concurrent calls are made on each SIP channel during an hour. To do this, i store the asterisk cdr in a mysql database with the always useful import.php found at http://voip-info.linuxsys.com/wiki/view/Asterisk+CDR+csv+mysql+import.html.
Then, i run this self-made script:


require ("./phplot/phplot.php");

$dbhost = "XXX";
$dbuser = "XXX";
$dbpass = "XXX";

$dbconnect = mysql_connect($dbhost, $dbuser, $dbpass)
        or die("Connessione non riuscita: " . mysql_error());

mysql_select_db("CDR_AST") or die('Could not select database.');

$data = array();
$carriers = array("A","B","C","D");
	
$X = "'2007-12-13 07:00:00'";
$OFFSET = 60;
$i=0;
$end = 60;

$totalcalls = 0;

for ($i=0; $i<$end; $i++) {
	$row = array();
	$row[] = '';
	$row[] = $i;
	foreach ($carriers as $key=>$value) {
		$sql = "SELECT * FROM cdr WHERE calldate > ($X - INTERVAL $OFFSET MINUTE + INTERVAL $i MINUTE) 
		AND calldate <= ($X + INTERVAL $i MINUTE) 
		AND (calldate + INTERVAL billsec SECOND) > ($X + INTERVAL $i MINUTE)
		AND dstchannel LIKE '%$value%'
		;";
		
		$calls4carrier4minute = mysql_num_rows(mysql_query($sql));
		$row[] = $calls4carrier4minute;
		$totalcalls += $calls4carrier4minute;
		}
	$row[] = $totalcalls;
	$data[$i] = $row;
	$totalcalls = 0;
	}

$plot = new PHPlot(800, 480);
$plot->SetImageBorderType('raised');

$plot->SetPlotType('lines');
$plot->SetDataType('data-data');
$plot->SetDataColors(array('red', 'green', 'blue','yellow','magenta'));
$plot->SetLineStyles(array('solid','solid','solid','solid','dashed'));
$plot->SetDataValues($data);

# Main plot title:
$plot->SetTitle("Chiamate per carrier del $X");

# Make a legend for the 2 functions:
$carriers[] = "Tot. chiamate";
$plot->SetLegend($carriers);

$plot->SetXTitle("Minuti");
$plot->SetYTickIncrement(5);
$plot->SetPrecisionX(0);
$plot->SetDrawXGrid(True);

$plot->SetYTitle("Chiamate contemp.");
$plot->SetYTickIncrement(10);
$plot->SetPrecisionY(0);
$plot->SetDrawYGrid(True);

$plot->DrawGraph();

mysql_close();

This script (that clearly requires the phplot library) interrogates the db about how many concurrent calls are in action during minute 1, minute 2, minute 3 ecc ecc starting from 07:00:00 of the 12 December. The problem is: rhis script is really heavy, with a lot of queries… Any idea about?!?

PS I take no responsability for any crash made on your computer from thi script!!!