How to – Create a simple CDR report for ViciDial

This article will go over how to create a simple CDR report for ViciDial using MySQL and PHP. I will provide the code I’ve used to accomplish this report which can be altered to fit your needs. First off, I have to thank Nik from www.amdy.io for helping me with the MySQL query for it. Check out his site and his amazing AMD(Answering Machine Detection) service they offer for ViciDial and other dialer systems. Ok so lets get started.

Step 1 – Create the directory on your web server

First move into your web directory by the following commands for both RedHat(CentOS, Alma or Rocky) and OpenSuSE(Leap) distros.

RedHat:

cd /var/www/html
mkdir reports
cd reports

OpenSuSE:

cd /srv/www/htdocs
mkdir reports
cd reports

Step 2 – Copy the code below and create the PHP file to serve it up

Now just copy and paste the code below into a .php file to use with ViciDial after typing the following command: nano cdr-report.php

<?php

$servername = "localhost";
$username = "cron";
$password = "1234";
$dbname = "asterisk";

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

$sql = "SELECT vc.`lead_id`, `campaign_id`, va.`user`, `phone_number`, `first_name`, `last_name`, `outbound_cid`, vd.`call_date`, `dial_time`, va.`status` FROM vicidial_carrier_log vc,  vicidial_agent_log va, vicidial_list vl, vicidial_dial_log vd WHERE vc.`lead_id`= vl.`lead_id` AND va.uniqueid = vc.uniqueid AND vl.lead_id = vd.lead_id and va.`status` NOT LIKE 'NEW'  and vc.caller_code = vd.caller_code;";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "<b><font color=red>Lead ID:</b></font> " . $row["lead_id"]. " -|- <b><font color=red>Campaign:</font></b> " . $row["campaign_id"]. " -|- <font color=red><b>Agent:</b></font> " . $row["user"]. " -|- <b><font color=red>Customer Number:</b></font> " . $row["phone_number"]. " -|- <b><font color=red>First Name:</b></font> " . $row["first_name"]. " -|- <b><font color=red>Last Name:</b></font> " . $row["last_name"]. " -|- <b><font color=red>Outbound Caller ID:</b></font> " . $row["outbound_cid"]. " -|- <b><font color=red>Dial Time:</b></font> " . $row["dial_time"]. " -|- <b><font color=red>Dispo:</b></font> " . $row["status"]. " -|- <b><font color=red>Call Date:</b></font> " . $row["call_date"]. "<br>";
  }
} else {
  echo "0 results";
}
$conn->close();
?>

After you save and exit, you should be able to access this page by going to https://yourserver.com/reports/cdr-report.php and you should see something like the image below:

Step 3 – Add this report into ViciDial through custom reports

Login to your admin panel and go to the reports page
Click on Admin Utilities as shown below

Click on “Custom Reports Admin Page”

Fill out the first line with “CDR Report”
Fill out the third line with “../reports/cdr-report.php

Step 4 – Update User Group Permissions

Now you need to make sure the User Groups that you want to have access to this new report have the proper permissions.

Click on User Groups and “Show User Groups”
Click on the User Group you want to have access to this report
Scroll down to the “Allowed Cutom Reports” area and click on “All Reports” or specify the new report we just created only.

Step 5 – Check the Reports page for your newly created Custom Report

That’s it, You’ve now created a custom report to retrieve your CDR details. This article can be followed to create other custom reports as well, just modify the code as needed or create links to outside web pages or links to access through your reports page within ViciDial. I hope you will find this useful. Feel free to join our Skype Live Support if you need assistance.

-Chris aka carpenox

How to – Create a simple CDR report for ViciDial