Geekly articles weekly

Asterisk FreePBX-Telefonverzeichnis aus SQL-Tabellen (webbasiertes Verzeichnis, Hochladen in XML-Datei für Grandstream-Telefone)

Die folgenden Punkte können für das Studium nützlich sein.

1) Stellen Sie eine Verbindung zum SQL Server her, um Abfragen auszuführen:

mysql -u freepbxuser -p   Enter     .      Enter,    MySQL: mysql>_

2) Grundlegende SQL-Befehle

    : SHOW DATABASES;    : USE dbtest;     : SHOW tables;    : SELECT * FROM page;   : DESCRIBE testtable;

3) Verbindung zu SQL von einem externen Netzwerk:

 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-Dateiformat:

 <?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>

Schritt 1. Erstellen Sie ein PHP-Skript, um die Asterisk-SQL-Tabelle zu lesen und eine XML-Datei zu generieren

Wir erstellen zwei Dateien config.php und phonebook.php und geben die erforderlichen Parameter an.

Config.php Datei

 <?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 Datei

 <?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); ?>

Schritt 2. Richten Sie Ihr Grandstream-Telefon so ein, dass es das Telefonbuch aus einer XML-Datei liest

Achtung, es ist wichtig, dass Sie beim Einrichten von "Phonebook Management" auf dem Grandstream GXP16xx-Telefon im Feld "Phonebook XML Server Path" nur den Pfad ohne den Dateinamen angeben.

  1. Wählen Sie im Feld "Telefonbuch-XML-Download aktivieren" die Option "Aktiviert, HTTP verwenden" aus.
  2. Geben Sie im Feld "Phonebook XML Server Path" den Pfad zur XML-Datei an, z. B.: "192.168.0.220/phonebook"

Die restlichen Parameter werden auf Anfrage angezeigt.

Schritt 3. Erstellen Sie ein Web-Telefonbuchverzeichnis



Erstellen Sie die Datei pb.php.

Höhepunkte.

  1. Laden Sie zuerst aus der XML-Datei und bilden Sie Kontaktgruppen
  2. Verarbeiten Sie die Auswahl der Gruppe und zeigen Sie die Kontakte der ausgewählten Gruppe an.

Config.php Datei
 <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>

Source: https://habr.com/ru/post/de465825/

More articles:


All Articles