Geekly articles weekly

Répertoire téléphonique Asterisk FreePBX à partir de tables SQL (répertoire Web, téléchargement vers un fichier XML pour les téléphones Grandstream)

Les points suivants peuvent ĂȘtre utiles pour l'Ă©tude.

1) Connectez-vous au serveur SQL pour exĂ©cuter des requĂȘtes:

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

2) Commandes SQL de base

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

3) Connexion à sql depuis un réseau externe:

 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) Format de fichier XML:

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

Étape 1. CrĂ©ez un script php pour lire la table sql Asterisk et gĂ©nĂ©rer un fichier xml

Nous créons deux fichiers config.php et phonebook.php, en spécifiant les paramÚtres nécessaires.

Fichier Config.php

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

Fichier Phonebook.php

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

Étape 2. Configurez votre tĂ©lĂ©phone Grandstream pour lire l'annuaire tĂ©lĂ©phonique Ă  partir d'un fichier xml

Attention, il est important lors de la configuration de "Phonebook Management" sur le téléphone Grandstream GXP16xx, dans le champ "Phonebook XML Server Path", de spécifier uniquement le chemin sans le nom de fichier.

  1. Dans le champ "Activer le téléchargement XML du répertoire", sélectionnez "Activé, utilisez HTTP"
  2. Dans le champ "Chemin d'accÚs au serveur XML du répertoire téléphonique", spécifiez le chemin d'accÚs au fichier xml, par exemple: "192.168.0.220/phonebook"

Les paramÚtres restants sont indiqués sur demande.

Étape 3. CrĂ©ez un rĂ©pertoire d'annuaire Web



Créez le fichier pb.php.

Faits saillants.

  1. Premier chargement depuis le fichier xml et formez des groupes de contacts
  2. Gérez la sélection du groupe et affichez les contacts du groupe sélectionné.

Fichier Config.php
 <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/fr465825/

More articles:


All Articles