report for calls to AGENTDIRECT ingroup

[brandon.parncutt]

Hello,

I'd like to generate a report for incoming calls to the AGENTDIRECT ingroup for the day. Is there a built-in script/page that does this or will I be digging through the database?

Thanks

Brandon

[ClearCall]

There is an "Inbound Report" that will give you a good summary and the "Export Calls Report" will give you a complete call log. Each of them allow you to specify date range and in group.

[brandon.parncutt]

The Inbound Report doesn't display the actual phone numbers, unless I'm missing something. It would appear the export report only generates reports for outbound calls, even if the AGENTDIRECT ingroup is selected. Here's my situation:

1.) We are running multiple campaigns, of course.
2.) Each of these campaigns are mainly outbound, but we have the occasional inbound call to our DIDs associated with the campaign. These DIDs are directed to an IVR which allows the customer to reach an agent directly by dialing their extension.
3.) The Real-Time report shows these inbound calls as they are happening, but they disappear once the call goes to voicemail or drops.
4.) Frequently, our agents are unaware these calls came in because they fail to check voicemail, the customer doesn't leave one, or they are busy in a call and cannot answer/unaware of it.
5.) I'd just like to generate a list of all of these calls so I can display it either on the Real-Time report page persistently, or even generate another page for this purpose.

I'm proficient with SQL and programming in general so the technical aspect isn't a problem. I was hoping there was a builtin function/script which could accomplish this already. If not, would anyone have a decent database diagram for vicidial? I could pull the relevant data out of the tables with this information if needed (especially if the primary keys of each table were the same values).

Thanks again,

Brandon

[ClearCall]

There are two Export reports. The Export Leads report will show only the most recent record for each lead. The Export Calls report will show each call.

I just ran an Export Calls report with Campaigns set to NONE, Inbound Groups set to AGENTDIRECT, Lists set to ALL, Statuses set to ALL and User Groups set to ALL and I got the report. I do not have an IVR in between though. There is also an Inbound IVR Report but that is also a summary.

If you need to write your own report you can just look at those existing reports to see which tables they are grabbing information from.

[brandon.parncutt]

I found the information I was looking for in the agiout logs for ASTGUICLIENT. The relevant MySQL queries are in there and I was able to adapt this and create my own page with the said information. I'm including the basic code below...it's a good starting point for anyone with the same dilemma:

Thanks

Brandon

Code: Select all

<?php

$url1=$_SERVER['REQUEST_URI'];
header("Refresh: 40; URL=$url1");

echo "<h1>Inbound Agent Calls</h1>";
echo "<h5>*Refreshes every 40s</h5>";
$servername="localhost";
$username="cron";
$password="1234";
$dbname="asterisk";

$conn=new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
   die("Connection failed:  " . $conn->connect_error);
}

$query="select caller_id_number,extension, call_date from vicidial_did_agent_log where call_date > timestampadd(day, -1, now()) and did_route='AGENT';";
$result=$conn->query($query);
if ($result->num_rows > 0) {
    // output data of each row
echo '<table border=1px>';
echo '<th>Caller ID Number</th><th>Extension</th><th>Call Time</th>';

    while($row = $result->fetch_assoc()) {
   echo '<tr>';
        echo "<td>" . $row["caller_id_number"]. "</td><td>" . $row["extension"]. "</td><td>" . $row["call_date"] . "</td></tr>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>


[Keyfin]

Awesome, Brandon, thanks for that. With your example I was able to create an all calls report in a nice table with the phone number, date and time of call, term reason, seconds per call, and agent on the call using this code

Code: Select all

    <?php

    $url1=$_SERVER['REQUEST_URI'];
    header("Refresh: 40; URL=$url1");

    echo "<h1>Calls Past 7 Days</h1>";
    echo "<h5>*Refreshes every 40s</h5>";
    $servername="localhost";
    $username="cron";
    $password="1234";
    $dbname="asterisk";

    $conn=new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
       die("Connection failed:  " . $conn->connect_error);
    }

    $query="select phone_number, call_date, status, term_reason, length_in_sec, user from vicidial_log where call_date > timestampadd(day, -7, now()) ;";
    $result=$conn->query($query);
    if ($result->num_rows > 0) {
        // output data of each row
    echo '<table border=1px>';
    echo '<th>Caller ID Number</th><th>Call Time</th><th>Status</th><th>Term Reason</th><th>Agent</th><th>Call Length</th>';

        while($row = $result->fetch_assoc()) {
       echo '<tr>';
            echo "<td>" . $row["phone_number"]. "</td><td>" . $row["call_date"] . "</td><td>" . $row["status"] . "</td><td>" . $row["term_reason"] . "</td><td>" . $row["user"] . "</td><td>" . $row["length_in_sec"] . "</td></tr>";
        }
    } else {
        echo "0 results";
    }
    $conn->close();
    ?>

for the .php file

[Keyfin]

someone asked me a question about where to put the code, so I'm including here, instruction on how to create a php file and where to put it on your server:

what you'll have to do is create a new text file with the code in there. then rename the text file with a .php extension, something like Call_Report.php. Then you'll need to take the Call_Report.php file and place it on your server, you can use an FTP client like FileZilla for this. you'll want to upload the file to the /srv/www/htdocs directory of your server. then, after the file is uploaded, you can access it by going through a web browser to <your server ip>/agc/Call_Report.php