Hi, so with my limited knowledge I didn't realise the /* */ meant it commented out the original section of code I referenced at the start of this threat. I assume this code has been replaced by the two lines above it, which seems a very neat query by Tom Teeuwen.
I spent more time on this last night and have, what I believe is a good mysql query, I just cant seem to get it working in the getstatsfromasternic.php file.
If I ssh to the PBX,
mysql -u root
use asteriskcdrdb;
I can start running mysql queries to get a list of external only calls, where the cnum = the extension number of the agent (which is 4000 in my test case).
select * from cdr WHERE cnum=4000 AND lastapp='Dial' AND dcontext like "%restrictedroute%";
might work because all external calls seem to go out a context name beginning with restrictedroute , or
select * from cdr WHERE cnum=4000 AND lastapp='Dial' AND outbound_cnum like "0%";
because no internal extensions start with a 0
We could use both together in one query, but either way, I cant seem to get this tweak into the getstatsfromasternic.php file to pull external calls onto the wallboard.
If anyone has enough knowledge to know where I put AND outbound_cnum like "0%" into the below query it would be a great help. I have tried it inbetween these two bits AND lastapp = 'Dial' GROUP BY cnum`` but we get 0 on the wallboard.
// query contributed by Tom Teeuwen
$condagentoutbound = preg_replace("/AND agent IN/","AND asterisk.users.name in",$condagent);
$query = "SELECT `asterisk`.`users`.`name` AS `agent`, `cnum`.`count` AS `count`, `cnum`.`talktime` AS `talktime` FROM ( SELECT `cnum`, COUNT(*) AS `count`, SUM(`sum`) AS `talktime` FROM ( SELECT `cnum`, `uniqueid`, SUM(`billsec`) AS `sum` FROM `asteriskcdrdb`.`cdr` WHERE calldate >= $mydate AND lastapp = 'Dial' GROUP BY `cnum`, `uniqueid`) AS `billsec_sum` GROUP BY `cnum`) AS `cnum` INNER JOIN `asterisk`.`users` ON `cnum`.`cnum` = `asterisk`.`users`.`extension` WHERE 1=1 $condagentoutbound";
Thanks
David
