Fullwallboard tweaking what counts as an outbound call

[davids1]

We have a client with FreePBX/PBXact, FOP2 and Callcenter Stats Pro.
If one extension calls another extension, it adds 1 to their outbound calls number in the queue.
The client would like internal calls, and internal transfers to not count as an outbound call.
My thoughts are to edit the getstatsfromasternic.php file, possibly on line 481, to only include context=macro-dialout-trunk
I dont know the MySQL code yet but it could look something like this - I have my extra bit in bold.
Does this sound like it could work, or has anyone a better idea?
Thanks
Dave

/*
$query = "SELECT agent,count,talktime FROM ( ";
$query.= "SELECT a.name AS agent,count(*) AS count, sum(billsec) AS talktime  ";
$query.= "FROM asterisk.users a ";
$query.= "INNER JOIN ( SELECT  * FROM asteriskcdrdb.cdr WHERE calldate >= $mydate) b ";
$query.= "ON extension=src AND lastapp='Dial' **AND context='macro-dialout-trunk'** GROUP BY a.name ";
$query.=") j WHERE 1=1 $condagent";
*/

[davids1]

Cant see how to edit, but the bold didnt show, it has added ** either side of the extra bit I added
AND context='macro-dialout-trunk'

[davids1]

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