Vicidial

How to – Create a simple CDR report for ViciDial

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.

  1. Click on User Groups and “Show User Groups”
  2. Click on the User Group you want to have access to this report
  3. 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

carpenox

Recent Posts

How to – Fix “WARNING[2204]: acl.c:890 resolve_first: Unable to lookup ‘SERVER_EXTERNAL_IP'”

How to - Fix "WARNING[2204]: acl.c:890 resolve_first: Unable to lookup 'SERVER_EXTERNAL_IP'" This error is the…

3 days ago

How to – Upgrade any Leap ViciBox install to Leap 15.6

How to - Upgrade any Leap ViciBox install to Leap 15.6 A lot of people…

1 week ago

How to – Debug Webphones for ViciDial

How to - Debug Webphones for ViciDial I get asked several times a day in…

2 weeks ago

How to – Set Up Load Balanced Phones and Calling for ViciDial

How to - Set Up Load Balanced Phones and Calling for ViciDial Step 1: Ensure…

2 weeks ago

How to – Renew your certbot SSL cert when it fails

How to - Renew your certbot SSL cert when it fails enewing a Let's Encrypt…

2 weeks ago

Introducing the New and Improved Full White Label Theme for ViciDial 2025 Edition by CyburDial

Introducing the New and Improved Full White Label Theme for ViciDial 2025 Edition by CyburDial…

3 weeks ago

This website uses cookies.