Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- $servername = "localhost";
- $username = "USERNAME";
- $password = "PASSWORD";
- $conn = new mysqli($servername, $username, $password);
- if ($conn->connect_error)
- {
- die("Connection failed: " . $conn->connect_error);
- }
- ?>
- <!DOCTYPE html>
- <html>
- <head>
- <title>IVR Statistics</title>
- <link rel="stylesheet" href="FREEPBXFQDN/styles/pbxstyles.css" />
- <style>
- td { padding: 2px 10px; }
- #callsDiv { position: fixed; height: 50%; width: 99%; overflow-y: scroll; overflow-x: hidden; }
- .callgood { background: #d1fbc6; }
- .callerrel { background: #f8fb7e; }
- .callivrrel { background: #f09e9e; }
- select { font-size: 1em; }
- </style>
- <script>
- function filterIVR()
- {
- proccalls = 0;
- var callerleft = 0;
- var ivrleft = 0;
- var table = document.getElementById("callsTbl");
- tr = table.getElementsByTagName("tr");
- var whichIVR = document.getElementById("ivr-filter").value;
- for (var i = 1; i < tr.length; i++)
- {
- tr[i].style.display = "none";
- td = tr[i].getElementsByTagName("td");
- for (var j = 0; j < td.length; j++)
- {
- cell = tr[i].getElementsByTagName("td")[j];
- if (cell)
- {
- if (whichIVR == "All" || cell.innerHTML.indexOf(whichIVR) > -1)
- {
- proccalls++;
- if (tr[i].innerHTML.indexOf("Released by caller") > -1)
- {
- callerleft++;
- tr[i].className = "callerrel";
- }
- else if (tr[i].innerHTML.indexOf("Released by IVR") > -1)
- {
- ivrleft++;
- tr[i].className = "callivrrel";
- }
- tr[i].style.display = "";
- break;
- }
- }
- }
- document.getElementById("currivr").innerText = whichIVR;
- document.getElementById("proccalls").innerText = proccalls.toString();
- document.getElementById("callerrel").innerText = callerleft.toString();
- document.getElementById("ivrrel").innerText = ivrleft.toString();
- }
- }
- </script>
- </head>
- <body onload="filterIVR()">
- <h1 style="text-align: center">IVR Statistics</h1>
- <span style="font-size: 1.1em">Filter by IVR:</span>
- <span style="width: 60px"> </span>
- <select id="ivr-filter" onchange="filterIVR()">
- <option value="All" default>-----All IVRs-----</option>
- <?php
- $sql = "SELECT description FROM asterisk.ivr_details WHERE description NOT LIKE '%IVR%'";
- $result = $conn->query($sql);
- if ($result->num_rows > 0)
- {
- while ($row = $result->fetch_assoc())
- {
- echo "<option value='" . $row["description"] . "'>" . $row["description"] . "</option>";
- }
- }
- else
- {
- echo "No IVRs to display.";
- }
- ?>
- </select>
- <p> </p>
- <hr>
- <h3>
- Current IVR: <span id="currivr" style="text-decoration: underline"> </span><br />
- Processed calls: <span id="proccalls"> </span><br />
- Released by caller: <span id="callerrel"> </span><br />
- Released by IVR: <span id="ivrrel"> </span>
- </h3>
- <div id="callsDiv">
- <table border="1" id="callsTbl">
- <tr class="header">
- <th>Menu:</th>
- <th>Timestamp:</th>
- <th>Caller:</th>
- <th>Called Number:</th>
- <th>Call Length:</th>
- <th>Call Result:</th>
- </tr>
- <?php
- function secToHMS($seconds)
- {
- $hours = floor($seconds / 3600);
- $minutes = floor(($seconds / 60) % 60);
- $seconds = $seconds % 60;
- $hours = str_pad($hours, 2, "0", STR_PAD_LEFT);
- $minutes = str_pad($minutes, 2, "0", STR_PAD_LEFT);
- $seconds = str_pad($seconds, 2, "0", STR_PAD_LEFT);
- return "$hours:$minutes:$seconds";
- }
- $sq1 = "
- SELECT
- asterisk.ivr_details.description AS ivrname,
- ivrid,
- tstamp,
- caller,
- callednum,
- calllength,
- CASE
- WHEN strcmp(sq2.selected, 's') = 0
- THEN 'Released by caller'
- WHEN strcmp(sq2.selected, 't') = 0
- THEN 'Released by IVR'
- WHEN char_length(sq2.selected) = 4
- THEN concat(ie.selection, ' -> ', sq2.selected)
- ELSE
- concat(ie.selection, ' -> ', substr(ie.dest, 17, 4))
- END AS endresult
- FROM (
- SELECT DISTINCT
- calldate AS tstamp,
- substr(asterisk.incoming.destination, 5, 1) AS ivrid,
- asterisk.incoming.extension AS ivrext,
- src AS caller,
- did AS callednum,
- dst AS selected,
- billsec AS calllength
- FROM asteriskcdrdb.cdr
- JOIN
- asterisk.incoming ON asterisk.incoming.extension = did
- )sq2
- JOIN
- asterisk.ivr_entries ie ON (substr(ie.dest, 17, 4) = sq2.selected OR
- char_length(sq2.selected) = 1) AND
- ie.ivr_id = sq2.ivrid
- JOIN
- asterisk.ivr_details ON asterisk.ivr_details.id = sq2.ivrid
- WHERE
- sq2.callednum IN (ivrext)
- GROUP BY tstamp
- ORDER BY tstamp DESC
- ";
- $result = $conn->query($sq1);
- if ($result->num_rows > 0)
- {
- while ($row = $result->fetch_assoc())
- {
- if ($row["endresult"] == "Released by caller")
- {
- $callerleft++;
- }
- else if ($row["endresult"] == "Released by IVR")
- {
- $ivrterm++;
- }
- echo "<tr class='callgood'><td><a href='FREEPBXFQDN/admin/config.php?display=ivr&action=edit&id=" . $row["ivrid"] . "' target='new'>" . $row["ivrname"] . "</a></td>";
- echo "<td>" . $row["tstamp"] . "</td>";
- echo "<td>" . $row["caller"] . "</td>";
- echo "<td><a href='FREEPBXFQDN/admin/config.php?display=did&view=form&extdisplay=" . $row["callednum"] . "%2F' target='new'>" . $row["callednum"] . "</a></td>";
- echo "<td>" . secToHMS($row["calllength"]) . "</td>";
- echo "<td>" . $row["endresult"] . "</td>";
- }
- }
- else
- {
- echo "0 results from sq1";
- }
- ?>
- </table>
- </div>
- </body>
- </html>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement