How to know which trunk is used in real time reports?

[DarknessBBB]

Hello there!
I would like to add a column in the real time reports that show which trunk is used for that call, (the same info that the agents view on their screen near the logout button). Is this info featchable in any db table?

[mflorell]

vicidial_auto_calls

[DarknessBBB]

Thank you very much matt, hopefully I'll post the patch in the tracker asap

[DarknessBBB]

I've written down a little bit of code, this is the result:



I'm wondering how to show the channels used by the agents that make manual calls (callbacks,etc). I can't find a way to select the field "channel" from vicidial_auto_calls modifying this query:

Code: Select all

select
vicidial_live_agents.extension,
vicidial_live_agents.user,
conf_exten,
vicidial_live_agents.status,
vicidial_live_agents.server_ip,
UNIX_TIMESTAMP(last_call_time),
UNIX_TIMESTAMP(last_call_finish),
call_server_ip,
vicidial_live_agents.campaign_id,
vicidial_users.user_group,
vicidial_users.full_name,
vicidial_live_agents.comments,
vicidial_live_agents.calls_today,
vicidial_live_agents.callerid,
vicidial_live_agents.lead_id,
UNIX_TIMESTAMP(last_state_change),
on_hook_agent,
ring_callerid,
agent_log_id,
vicidial_auto_calls.channel
from vicidial_live_agents,vicidial_users,vicidial_auto_calls
where vicidial_live_agents.user=vicidial_users.user and
vicidial_auto_calls.lead_id = vicidial_live_agents.lead_id and
vicidial_live_agents.campaign_id IN('201','301','501','801','911','') // "just my campaigns"
order by vicidial_live_agents.status,last_call_time

[DarknessBBB]

SOLVED! Here is the patch

Code: Select all

--- AST_timeonVDADall.php   2011-11-30 11:32:33.000000000 +0100
+++ AST_timeonVDADall.php   2012-06-12 15:41:42.000000000 +0200
@@ -2107,8 +2107,10 @@
 $HTcalls =         " CALLS |";
 $HDpause =   '';
 $HTpause =   '';
-$HDigcall =         "------+------------------";
-$HTigcall =         " HOLD | IN-GROUP ";
+$HDigcall =         "------+------------------+";
+$HTigcall =         " HOLD | IN-GROUP         |";
+$HDchannel =      "-----------+";
+$HTchannel =       "  CHANNEL  |";
 
 if (!ereg("N",$agent_pause_codes_active))
    {
@@ -2153,13 +2155,13 @@
 
 if ($realtime_block_user_info > 0)
    {
-   $Aline  = "$HDbegin$HDusergroup$HDsessionid$HDbarge$HDstatus$HDpause$HDcustphone$HDserver_ip$HDcall_server_ip$HDtime$HDcampaign$HDcalls$HDigcall\n";
-   $Bline  = "$HTbegin$HTusergroup$HTsessionid$HTbarge$HTstatus$HTpause$HTcustphone$HTserver_ip$HTcall_server_ip$HTtime$HTcampaign$HTcalls$HTigcall\n";
+   $Aline  = "$HDbegin$HDusergroup$HDsessionid$HDbarge$HDstatus$HDpause$HDcustphone$HDserver_ip$HDcall_server_ip$HDtime$HDcampaign$HDcalls$HDigcall$HDchannel\n";
+   $Bline  = "$HTbegin$HTusergroup$HTsessionid$HTbarge$HTstatus$HTpause$HTcustphone$HTserver_ip$HTcall_server_ip$HTtime$HTcampaign$HTcalls$HTigcall$HTchannel\n";
    }
 else
    {
-   $Aline  = "$HDbegin$HDstation$HDphone$HDuser$HDusergroup$HDsessionid$HDbarge$HDstatus$HDpause$HDcustphone$HDserver_ip$HDcall_server_ip$HDtime$HDcampaign$HDcalls$HDigcall\n";
-   $Bline  = "$HTbegin$HTstation$HTphone$HTuser$HTusergroup$HTsessionid$HTbarge$HTstatus$HTpause$HTcustphone$HTserver_ip$HTcall_server_ip$HTtime$HTcampaign$HTcalls$HTigcall\n";
+   $Aline  = "$HDbegin$HDstation$HDphone$HDuser$HDusergroup$HDsessionid$HDbarge$HDstatus$HDpause$HDcustphone$HDserver_ip$HDcall_server_ip$HDtime$HDcampaign$HDcalls$HDigcall$HDchannel\n";
+   $Bline  = "$HTbegin$HTstation$HTphone$HTuser$HTusergroup$HTsessionid$HTbarge$HTstatus$HTpause$HTcustphone$HTserver_ip$HTcall_server_ip$HTtime$HTcampaign$HTcalls$HTigcall$HTchannel\n";
    }
 $Aecho .= "$Aline";
 $Aecho .= "$Bline";
@@ -2190,7 +2192,7 @@
 else {$usergroupSQL = " and user_group='" . mysql_real_escape_string($usergroup) . "'";}
 
 $ring_agents=0;
-$stmt="select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agents.server_ip,UNIX_TIMESTAMP(last_call_time),UNIX_TIMESTAMP(last_call_finish),call_server_ip,vicidial_live_agents.campaign_id,vicidial_users.user_group,vicidial_users.full_name,vicidial_live_agents.comments,vicidial_live_agents.calls_today,vicidial_live_agents.callerid,lead_id,UNIX_TIMESTAMP(last_state_change),on_hook_agent,ring_callerid,agent_log_id from vicidial_live_agents,vicidial_users where vicidial_live_agents.user=vicidial_users.user $UgroupSQL $usergroupSQL order by $orderSQL;";
+$stmt="select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agents.server_ip,UNIX_TIMESTAMP(last_call_time),UNIX_TIMESTAMP(last_call_finish),call_server_ip,vicidial_live_agents.campaign_id,vicidial_users.user_group,vicidial_users.full_name,vicidial_live_agents.comments,vicidial_live_agents.calls_today,vicidial_live_agents.callerid,lead_id,UNIX_TIMESTAMP(last_state_change),on_hook_agent,ring_callerid,agent_log_id, channel from vicidial_live_agents,vicidial_users where vicidial_live_agents.user=vicidial_users.user $UgroupSQL $usergroupSQL order by $orderSQL;";
 $rslt=mysql_query($stmt, $link);
 if ($DB) {echo "$stmt\n";}
 $talking_to_print = mysql_num_rows($rslt);
@@ -2220,6 +2222,17 @@
       $Aon_hook_agent[$i] =   $row[16];
       $Aring_callerid[$i] =   $row[17];
       $Aagent_log_id[$i] =   $row[18];
+      $Acall_channel[$i] =    $row[19];
+      
+      if ($Alead_id[$i] <> '0')
+      {
+         $sql_channel = "SELECT `channel` FROM `vicidial_auto_calls` WHERE `lead_id` = '" . $Alead_id[$i] . "';";
+         if ($DB) {echo "$sql_channel\n";}
+         $rslt_channel=mysql_query($sql_channel, $link);
+         $row_channel=mysql_fetch_row($rslt_channel);
+         $Acall_channel[$i] =   $row_channel[0];
+      }
+      
       $Aring_note[$i] =      ' ';
 
       if ($Aon_hook_agent[$i] == 'Y')
@@ -2409,6 +2422,7 @@
       $campaign_id =   sprintf("%-10s", $Acampaign_id[$i]);
       $comments=      $Acomments[$i];
       $calls_today =   sprintf("%-5s", $Acalls_today[$i]);
+      $call_channel  = substr($Acall_channel[$i],0,strpos($Acall_channel[$i],"-"));
 
       if (!ereg("N",$agent_pause_codes_active))
          {$pausecode='       ';}
@@ -2610,16 +2624,20 @@
 
          $INGRP = " $G$vac_stage$EG | $G$vac_campaign$EG ";
          }
+      else
+         {
+         $INGRP = "                        ";
+         }
 
       $agentcount++;
 
       if ($realtime_block_user_info > 0)
          {
-         $Aecho .= "|$UGD $G$sessionid$EG$L$R$Aring_note[$i]| $G$status$EG $CM $pausecode|$CP$SVD$G$call_time_MS$EG | $G$campaign_id$EG | $G$calls_today$EG |$INGRP\n";
+         $Aecho .= "|$UGD $G$sessionid$EG$L$R$Aring_note[$i]| $G$status$EG $CM $pausecode|$CP$SVD$G$call_time_MS$EG | $G$campaign_id$EG | $G$calls_today$EG |$INGRP | $G$call_channel$EG\n";
          }
       if ($realtime_block_user_info < 1)
          {
-         $Aecho .= "| $G$extension$EG$Aring_note[$i]|$phoneD<a href=\"./user_status.php?user=$Luser\" target=\"_blank\">$G$user$EG</a> <a href=\"javascript:ingroup_info('$Luser','$j');\">+</a> |$UGD $G$sessionid$EG$L$R | $G$status$EG $CM $pausecode|$CP$SVD$G$call_time_MS$EG | $G$campaign_id$EG | $G$calls_today$EG |$INGRP\n";
+         $Aecho .= "| $G$extension$EG$Aring_note[$i]|$phoneD<a href=\"./user_status.php?user=$Luser\" target=\"_blank\">$G$user$EG</a> <a href=\"javascript:ingroup_info('$Luser','$j');\">+</a> |$UGD $G$sessionid$EG$L$R | $G$status$EG $CM $pausecode|$CP$SVD$G$call_time_MS$EG | $G$campaign_id$EG | $G$calls_today$EG |$INGRP | $G$call_channel$EG\n";
          }
       $j++;
       }



Can, please, anyone test it?

[DomeDan]

Testing:

Code: Select all

patching file AST_timeonVDADall.php
Hunk #1 FAILED at 2107.
Hunk #2 FAILED at 2153.
Hunk #3 FAILED at 2190.
Hunk #4 FAILED at 2220.
Hunk #5 FAILED at 2409.
Hunk #6 FAILED at 2610.
6 out of 6 hunks FAILED -- saving rejects to file AST_timeonVDADall.php.rej


tried to patch:
$version = '2.4-67';
$build = '120223-1934';

make a patch using "diff -u" instead and write down the build of your AST_timeonVDADall.php

[DarknessBBB]

I've edited the post with the patch.
$version = '2.4-66';
$build = '111103-1220';

[DomeDan]

$version = '2.4-66';
$build = '111103-1220';
Failed.
but now i know why,
its because tab is used in the code, and that is converted to space, you need to upload it as a file to keep the tabs
use the issue tracker

I recommend you download the latest trunk and work with that instead too

[DarknessBBB]

My fault, here is the file

https://docs.google.com/open?id=0BzL_tQ ... zRuT3pabFk

Right now I cannot use the latest trunk (it's a production server), I hope to do the upgrade this weekend.

[DomeDan]

I could even patch $build = '120223-1934';

patching file AST_timeonVDADall.php
Hunk #1 succeeded at 2254 (offset 147 lines).
Hunk #2 succeeded at 2302 (offset 147 lines).
Hunk #3 FAILED at 2192.
Hunk #4 succeeded at 2372 (offset 150 lines).
Hunk #5 succeeded at 2572 (offset 150 lines).
Hunk #6 succeeded at 2774 (offset 150 lines).
1 out of 6 hunks FAILED -- saving rejects to file AST_timeonVDADall.php.rej

if i change this in the patch by hand:

Code: Select all

-$stmt="select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agents.server_ip,UNIX_TIMESTAMP(last_call_time),UNIX_TIMESTAMP(last_call_finish),call_server_ip,vicidial_live_agents.campaign_id,vicidial_users.user_group,vicidial_users.full_name,vicidial_live_agents.comments,vicidial_live_agents.calls_today,vicidial_live_agents.callerid,lead_id,UNIX_TIMESTAMP(last_state_change),on_hook_agent,ring_callerid,agent_log_id from vicidial_live_agents,vicidial_users where vicidial_live_agents.user=vicidial_users.user $UgroupSQL $usergroupSQL order by $orderSQL;";

-$stmt="select extension,vicidial_live_agents.user,conf_exten,vicidial_live_agents.status,vicidial_live_agents.server_ip,UNIX_TIMESTAMP(last_call_time),UNIX_TIMESTAMP(last_call_finish),call_server_ip,vicidial_live_agents.campaign_id,vicidial_users.user_group,vicidial_users.full_name,vicidial_live_agents.comments,vicidial_live_agents.calls_today,vicidial_live_agents.callerid,lead_id,UNIX_TIMESTAMP(last_state_change),on_hook_agent,ring_callerid,agent_log_id from vicidial_live_agents,vicidial_users where vicidial_live_agents.user=vicidial_users.user $UgroupSQL $usergroupSQL $user_group_filter_SQL order by $orderSQL;";


patching file AST_timeonVDADall.php
Hunk #1 succeeded at 2254 (offset 147 lines).
Hunk #2 succeeded at 2302 (offset 147 lines).
Hunk #3 succeeded at 2342 with fuzz 1 (offset 150 lines).
Hunk #4 succeeded at 2372 (offset 150 lines).
Hunk #5 succeeded at 2572 (offset 150 lines).
Hunk #6 succeeded at 2774 (offset 150 lines).

doesn't look so good but yeah sure it works, both manual and auto

Code: Select all

VICIDIAL: Agents Time On Calls Campaign: |ALL-ACTIVE|            2012-06-13 14:35:42
+----------------+------------------------+-----------+----------+---------+------------+-------+------+------------------+-----------+
| STATION        | USER  SHOW ID     INFO | SESSIONID | STATUS   | MM:SS   | CAMPAIGN   | CALLS | HOLD | IN-GROUP         |  CHANNEL  |
+----------------+------------------------+-----------+----------+---------+------------+-------+------+------------------+-----------+
| SIP/151a       | asdf asd             + | 8600052   | READY    |    0:49 | as_dfgh    | 166   |                         |
| SIP/152a       | asdfghjk asdfgh      + | 8600051   | INCALL A |    2:19 | as_dfgh    | 136   |                         | SIP/asdfghjklzxc
| SIP/201e       | asdf g               + | 8600053   | PAUSED   |    5:44 | as_df      | 0     |                         |
+----------------+------------------------+-----------+----------+---------+------------+-------+------+------------------+-----------+

[sofcall]

hi.
Need this patch.can you show us how to patch it(to use it?)(for a a newbie)
Thank you.