Asterisk FreePBX telephone directory from SQL tables (web-based directory, upload to xml file for Grandstream phones)
The following points may come in handy for study.
1) Connect to SQL server to execute queries:
2) Basic sql commands
3) Connection to sql from an external network:
4) XML file format:
Step 1. Creating a php script to read the Asterisk sql table and generate an xml file
We create two files config.php and phonebook.php, specifying the necessary parameters.
Config.php file
Phonebook.php file
Step 2. Set up your Grandstream phone to read the phone book from an xml file
Attention, it is important when setting up "Phonebook Management" on the Grandstream GXP16xx phone, in the "Phonebook XML Server Path" field, specify only the path without the file name.
The remaining parameters are indicated on demand.
Step 3. Create a web directory of the phone book
Create the pb.php file.
Highlights.
1) Connect to SQL server to execute queries:
mysql -u freepbxuser -p Enter . Enter, MySQL: mysql>_
2) Basic sql commands
: SHOW DATABASES; : USE dbtest; : SHOW tables; : SELECT * FROM page; : DESCRIBE testtable;
3) Connection to sql from an external network:
CREATE USER '--'@'localhost' IDENTIFIED BY '!!!!!!'; GRANT ALL PRIVILEGES ON * . * TO '--'@'localhost'; GRANT SELECT ON * . * TO '--'@'localhost'; use mysql SELECT Host,User,Password FROM user; UPDATE user SET Host='%' WHERE User='--' AND Host='localhost'; FLUSH PRIVILEGES;
4) XML file format:
<?xml version="1.0" encoding="UTF-8"?> <AddressBook> <pbgroup> <id>70</id> <name>User Manager Group</name> </pbgroup> <Contact> <id>760</id> <FirstName></FirstName> <LastName></LastName> <Frequent>0</Frequent> <Phone type="Work"> <phonenumber>101</phonenumber> <accountindex>1</accountindex> </Phone> <Group>70</Group> <Primary>0</Primary> </Contact> </AddressBook>
Step 1. Creating a php script to read the Asterisk sql table and generate an xml file
We create two files config.php and phonebook.php, specifying the necessary parameters.
Config.php file
<?php return array( "db" => "mysql:host=localhost;dbname=asterisk", "pb_file_gs" => "/var/www/html/phonebook/phonebook.xml", "username" => "freepbxuser", //Mysql login "password" => " freepbxuser", //Mysql password "options" => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8') );
Phonebook.php file
<?php $config = include("db/config.php"); $db = new PDO($config["db"], $config["username"], $config["password"], $config["options"]); // $sql = "SELECT userman_users.username, userman_users.displayname, contactmanager_group_entries.groupid FROM asterisk.userman_users, asterisk.contactmanager_group_entries, asterisk.contactmanager_groups WHERE contactmanager_group_entries.user = userman_users.id AND contactmanager_group_entries.groupid = contactmanager_groups.id"; // $sqlgrp = "SELECT contactmanager_groups.name,contactmanager_groups.id FROM asterisk.contactmanager_groups"; // $q = $db->prepare($sql); $q->execute(); $rows = $q->fetchAll(); // $q2 = $db->prepare($sqlgrp); $q2->execute(); $rows2 = $q2->fetchAll(); unlink($config["pb_file_gs"]); // $fp = fopen($config["pb_file_gs"],"w+"); fputs($fp, '<?xml version="1.0" encoding="UTF-8"?><AddressBook>'); foreach($rows2 as $row) { // , fputs($fp,'<pbgroup>'); fputs($fp,'<id>'.$row['id'].'</id>'); fputs($fp,'<name>'.$row['name'].'</name>'); fputs($fp,'</pbgroup>'); } foreach($rows as $row) { // , XML if (strlen($row['username']) === 3) { $alln = $row['displayname']; //if ($alln = '') #alln = $row['username']; $ln = strstr($alln, ' ', true); $fn = strstr($alln, ' ', false); $fn = trim($fn); $i++; fputs($fp, '<Contact> <id>'.$i.'</id> <FirstName>'.$ln.'</FirstName> <LastName>'.$fn.'</LastName> <Phone type="Work"> <phonenumber>'.$row['username'].'</phonenumber> <accountindex>1</accountindex> </Phone> <Group>' .$row['groupid']. '</Group> </Contact> '); } } fputs($fp,'</AddressBook>'); fclose($fp); ?>
Step 2. Set up your Grandstream phone to read the phone book from an xml file
Attention, it is important when setting up "Phonebook Management" on the Grandstream GXP16xx phone, in the "Phonebook XML Server Path" field, specify only the path without the file name.
- In the field "Enable Phonebook XML Download" select "Enabled, use HTTP"
- In the "Phonebook XML Server Path" field, specify the path to the xml file, for example: "192.168.0.220/phonebook"
The remaining parameters are indicated on demand.
Step 3. Create a web directory of the phone book
Create the pb.php file.
Highlights.
- First load from the xml file and form contact groups
- Process the selection of the group and display the contacts of the selected group.
Config.php file
<html> <style> <!-- /* HTML */ table.sort{ border-spacing:0em; margin-bottom:1em; margin-top:1em } /* */ table.sort td{ border:1px solid #CCCCCC; padding:0.3em 1em } /* */ table.sort thead td{ cursor:pointer; cursor:hand; font-weight:bold; text-align:center; vertical-align:middle } tbody tr:hover { background: #999999; /* */ color: #FFFFFF; /* */ } /* */ table.sort thead td.curcol{ background-color:#999999; color:#FFFFFF } --> </style> <script type="text/javascript"> initial_sort_id = 0; // , initial_sort_up = 0; // 0 - , 1 - var sort_case_sensitive = false; // function _sort(a, b) { var a = a[0]; var b = b[0]; var _a = (a + '').replace(/,/, '.'); var _b = (b + '').replace(/,/, '.'); if (parseInt(_a) && parseInt(_b)) return sort_numbers(parseInt(_a), parseInt(_b)); else if (!sort_case_sensitive) return sort_insensitive(a, b); else return sort_sensitive(a, b); } function sort_numbers(a, b) { return a - b; } function sort_insensitive(a, b) { var anew = a.toLowerCase(); var bnew = b.toLowerCase(); if (anew < bnew) return -1; if (anew > bnew) return 1; return 0; } function sort_sensitive(a, b) { if (a < b) return -1; if (a > b) return 1; return 0; } function getConcatenedTextContent(node) { var _result = ""; if (node == null) { return _result; } var childrens = node.childNodes; var i = 0; while (i < childrens.length) { var child = childrens.item(i); switch (child.nodeType) { case 1: // ELEMENT_NODE case 5: // ENTITY_REFERENCE_NODE _result += getConcatenedTextContent(child); break; case 3: // TEXT_NODE case 2: // ATTRIBUTE_NODE case 4: // CDATA_SECTION_NODE _result += child.nodeValue; break; case 6: // ENTITY_NODE case 7: // PROCESSING_INSTRUCTION_NODE case 8: // COMMENT_NODE case 9: // DOCUMENT_NODE case 10: // DOCUMENT_TYPE_NODE case 11: // DOCUMENT_FRAGMENT_NODE case 12: // NOTATION_NODE // skip break; } i++; } return _result; } function sort(e) { var el = window.event ? window.event.srcElement : e.currentTarget; while (el.tagName.toLowerCase() != "td") el = el.parentNode; var a = new Array(); var name = el.lastChild.nodeValue; var dad = el.parentNode; var table = dad.parentNode.parentNode; var up = table.up; // no set/getAttribute! var node, arrow, curcol; for (var i = 0; (node = dad.getElementsByTagName("td").item(i)); i++) { if (node.lastChild.nodeValue == name){ curcol = i; if (node.className == "curcol"){ arrow = node.firstChild; table.up = Number(!up); }else{ node.className = "curcol"; arrow = node.insertBefore(document.createElement("span"),node.firstChild); arrow.appendChild(document.createTextNode("")); table.up = 0; } arrow.innerHTML=((table.up==0)?"↓":"↑")+" "; }else{ if (node.className == "curcol"){ node.className = ""; if (node.firstChild) node.removeChild(node.firstChild); } } } var tbody = table.getElementsByTagName("tbody").item(0); for (var i = 0; (node = tbody.getElementsByTagName("tr").item(i)); i++) { a[i] = new Array(); a[i][0] = getConcatenedTextContent(node.getElementsByTagName("td").item(curcol)); a[i][1] = getConcatenedTextContent(node.getElementsByTagName("td").item(1)); a[i][2] = getConcatenedTextContent(node.getElementsByTagName("td").item(0)); a[i][3] = node; } a.sort(_sort); if (table.up) a.reverse(); for (var i = 0; i < a.length; i++) { tbody.appendChild(a[i][3]); } } function init(e) { if (!document.getElementsByTagName) return; if (document.createEvent) function click_elem(elem){ var evt = document.createEvent("MouseEvents"); evt.initMouseEvent("click", false, false, window, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, elem); elem.dispatchEvent(evt); } for (var j = 0; (thead = document.getElementsByTagName("thead").item(j)); j++) { var node; for (var i = 0; (node = thead.getElementsByTagName("td").item(i)); i++) { if (node.addEventListener) node.addEventListener("click", sort, false); else if (node.attachEvent) node.attachEvent("onclick", sort); node.title = " , "; } thead.parentNode.up = 0; if (typeof(initial_sort_id) != "undefined"){ td_for_event = thead.getElementsByTagName("td").item(initial_sort_id); if (td_for_event.dispatchEvent) click_elem(td_for_event); else if (td_for_event.fireEvent) td_for_event.fireEvent("onclick"); if (typeof(initial_sort_up) != "undefined" && initial_sort_up){ if (td_for_event.dispatchEvent) click_elem(td_for_event); else if (td_for_event.fireEvent) td_for_event.fireEvent("onclick"); } } } } var root = window.addEventListener || window.attachEvent ? window : document.addEventListener ? document : null; if (root){ if (root.addEventListener) root.addEventListener("load", init, false); else if (root.attachEvent) root.attachEvent("onload", init); } // function getCookie(name) { var cookie = " " + document.cookie; var search = " " + name + "="; var setStr = null; var offset = 0; var end = 0; if (cookie.length > 0) { offset = cookie.indexOf(search); if (offset != -1) { offset += search.length; end = cookie.indexOf(";", offset) if (end == -1) { end = cookie.length; } setStr = unescape(cookie.substring(offset, end)); } } return(setStr); } // function selectChanged() { var sel = document.getElementById('myselect'); var str = sel.selectedIndex+1 ? (sel.options[sel.selectedIndex].innerHTML) : ' '; document.cookie = "sgrp="+str+";"; //document.getElementById('nameslist').innerHTML = '<?php $hello = $_COOKIE["sgrp"]; echo $hello; ?>'; location.reload(); } </script> <strong> <div style="text-align: center;"> </div> <br> </strong> <?php $xml = simplexml_load_file('http://192.168.0.220/phonebook/phonebook.xml'); $conts_cnt = count($xml->Contact); $grp_cnt = count($xml->pbgroup); // echo '<div id="groupslist" style="width: 20%; float:left"> <strong>:</strong> <br> <br> <select id="myselect" size="20" onchange="javascript:selectChanged();">'; echo "<option></option>"; for($ig = 0; $ig < $grp_cnt; $ig++) { $Groups = $xml->pbgroup[$ig]; if ($Groups->name <> 'User Manager Group') echo "<option>{$Groups->name}</option>"; } echo "</select> </div>"; // echo '<div id="nameslist" style="width: 80%; float:right">'; echo "<strong>{$_COOKIE['sgrp']}</strong>"; echo "<table class=sort align=center width=100%>"; echo "<thead>"; echo "<tr>"; echo "<td></td>"; echo "<td></td>"; echo "<td></td>"; //echo "<td></td>"; echo "</tr>"; echo "</thead>"; echo "<tbody>"; for($i = 0; $i < $conts_cnt; $i++) { $Contacts = $xml->Contact[$i]; $FirstName = $Contacts->FirstName; $LastName = $Contacts->LastName; $phonenumber = $Contacts->Phone->phonenumber; echo "<tr>"; for($ig = 0; $ig < $grp_cnt; $ig++) { $Groups = $xml->pbgroup[$ig]; if (strcasecmp($Contacts->Group, $Groups->id) == 0) { if ($_COOKIE["sgrp"] == trim($Groups->name)) { echo "<td class=col1>{$FirstName}</td>"; echo "<td class=col1>{$LastName}</td>"; echo "<td class=col1>{$phonenumber}</td>"; //echo "<td class=col1>{$Groups->name}</td>"; } else if ("" == $_COOKIE["sgrp"]) { echo "<td class=col1>{$FirstName}</td>"; echo "<td class=col1>{$LastName}</td>"; echo "<td class=col1>{$phonenumber}</td>"; } } } echo "</tr>"; } echo "</tbody>"; echo "</table>"; echo '</div>'; ?> </html>
All Articles