opengeodb and ldap detaillers work!
authorChristian Weiske <cweiske@cweiske.de>
Sun, 5 Aug 2012 19:33:13 +0000 (21:33 +0200)
committerChristian Weiske <cweiske@cweiske.de>
Sun, 5 Aug 2012 19:33:13 +0000 (21:33 +0200)
docs/opengeodb-create-my_orte.sql [new file with mode: 0644]
src/callnotifier/CLI.php
src/callnotifier/CallMonitor/Detailler/LDAP.php
src/callnotifier/CallMonitor/Detailler/OpenGeoDb.php

diff --git a/docs/opengeodb-create-my_orte.sql b/docs/opengeodb-create-my_orte.sql
new file mode 100644 (file)
index 0000000..e2568d4
--- /dev/null
@@ -0,0 +1,22 @@
+CREATE TABLE IF NOT EXISTS `my_orte` (
+  `loc_id` int(11) NOT NULL,
+  `name` varchar(128) NOT NULL,
+  `vorwahl` varchar(8) NOT NULL,
+  `bundesland` varchar(32) NOT NULL,
+  `level` tinyint(4) NOT NULL,
+  `einwohner` int(11) NOT NULL,
+  UNIQUE KEY `loc_id` (`loc_id`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
+
+INSERT INTO my_orte (loc_id, name, vorwahl, bundesland, level, einwohner)
+SELECT t_vorwahl.loc_id, t_name.text_val as name, t_vorwahl.text_val as vorwahl, t_name_bundesland.text_val as bundesland, t_hier.level as level, t_einwohner.int_val as einwohner
+FROM geodb_textdata as t_vorwahl
+ JOIN geodb_textdata as t_name ON t_vorwahl.loc_id = t_name.loc_id
+ JOIN geodb_hierarchies AS t_hier ON t_vorwahl.loc_id = t_hier.loc_id
+ JOIN geodb_textdata as t_name_bundesland ON t_hier.id_lvl3 = t_name_bundesland.loc_id
+ LEFT JOIN geodb_intdata as t_einwohner ON t_vorwahl.loc_id = t_einwohner.loc_id
+WHERE t_vorwahl.text_type="500400000"
+ AND t_name.text_type = "500100000"
+ AND t_name_bundesland.text_type = "500100000"
+ AND (t_einwohner.int_type = "600700000" OR t_einwohner.loc_id IS NULL);
index 8958f78..16c95b0 100644 (file)
@@ -36,8 +36,23 @@ class CLI
         );
 
         $callMonitor = new CallMonitor($this->config, $log);
-        $callMonitor->addDetailler(new CallMonitor_Detailler_LDAP());
-        $callMonitor->addDetailler(new CallMonitor_Detailler_OpenGeoDb());
+        $callMonitor->addDetailler(
+            new CallMonitor_Detailler_LDAP(
+                array(
+                    'host' => 'ldap.home.cweiske.de',
+                    'basedn' => 'ou=adressbuch,dc=cweiske,dc=de',
+                    'binddn' => 'cn=readonly,ou=users,dc=cweiske,dc=de',
+                    'bindpw' => 'readonly'
+                )
+            )
+        );
+        $callMonitor->addDetailler(
+            new CallMonitor_Detailler_OpenGeoDb(
+                'mysql:host=dojo;dbname=opengeodb',
+                'opengeodb-read',
+                'opengeodb'
+            )
+        );
 
         $handler = new MessageHandler($this->config, $log, $callMonitor);
 
index 833fd46..ffcc9e1 100644 (file)
@@ -3,8 +3,14 @@ namespace callnotifier;
 
 class CallMonitor_Detailler_LDAP implements CallMonitor_Detailler
 {
-    public function __construct()
+    public function __construct($ldapConfig)
     {
+        $this->ldap = \Net_LDAP2::connect($ldapConfig);
+        if (\PEAR::isError($this->ldap)) {
+            throw new \Exception(
+                'Could not connect to LDAP-server: ' . $this->ldap->getMessage()
+            );
+        }
     }
 
     public function loadCallDetails(CallMonitor_Call $call)
@@ -18,7 +24,40 @@ class CallMonitor_Detailler_LDAP implements CallMonitor_Detailler
 
     protected function loadName($number)
     {
-        return 'foo';
+        $filter = \Net_LDAP2_Filter::combine(
+            'or',
+            array(
+                \Net_LDAP2_Filter::create('companyPhone', 'equals', $number),
+                \Net_LDAP2_Filter::create('homePhone', 'equals', $number),
+                \Net_LDAP2_Filter::create('mobile', 'equals', $number),
+                \Net_LDAP2_Filter::create('otherPhone', 'equals', $number),
+                \Net_LDAP2_Filter::create('telephoneNumber', 'equals', $number),
+            )
+        );
+        $options = array(
+            'scope' => 'sub',
+            'attributes' => array('displayName', 'givenName', 'sn', 'cn')
+        );
+
+        $search = $this->ldap->search(null, $filter, $options);
+        if (\PEAR::isError($search)) {
+            throw new \Exception(
+                'Error searching LDAP: ' . $search->getMessage()
+            );
+        }
+        if ($search->count() == 0) {
+            return null;
+        }
+
+        $arEntry = $search->shiftEntry()->getValues();
+        if (isset($arEntry['displayName'])) {
+            return $arEntry['displayName'];
+        } else if (isset($arEntry['sn']) && $arEntry['givenName']) {
+            return $arEntry['givenName'] . ' ' . $arEntry['sn'];
+        } else if (isset($arEntry['cn'])) {
+            return $arEntry['cn'];
+        }
+        return null;
     }
 
 }
index a37ede0..2b48004 100644 (file)
@@ -1,6 +1,17 @@
 <?php
 namespace callnotifier;
 
+/**
+ * Fetch location name from OpenGeoDb.
+ * In case of mobile phone numbers, the provider is named.
+ *
+ * It uses a custom table "my_orte" that can be created with
+ * docs/opengeodb-create-my_orte.sql
+ *
+ * Sets "toLocation" or "fromLocation", depending on call type
+ *
+ * @link http://opengeodb.org/
+ */
 class CallMonitor_Detailler_OpenGeoDb implements CallMonitor_Detailler
 {
     protected $db;
@@ -25,12 +36,18 @@ class CallMonitor_Detailler_OpenGeoDb implements CallMonitor_Detailler
         '0179' => 'O2',
     );
 
-    public function __construct()
+    /**
+     * Create new detailler object
+     *
+     * @param string $dsn      PDO connection string, for example
+     *                         'mysql:host=dojo;dbname=opengeodb'
+     * @param string $username Database username
+     * @param string $password Database password
+     */
+    public function __construct($dsn, $username, $password)
     {
         $this->db = new \PDO(
-            'mysql:host=dojo;dbname=opengeodb',
-            'opengeodb-read',
-            'opengeodb',
+            $dsn, $username, $password,
             array(
                 \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                 \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC
@@ -57,44 +74,23 @@ class CallMonitor_Detailler_OpenGeoDb implements CallMonitor_Detailler
             }
             return null;
         }
-        //area codes in germany can be 3 to 6 numbers
-        //FIXME: what about international numbers?
-        for ($n = 3; $n <= 6; $n++) {
-            $areacode = substr($number, 0, $n);
-            $name = $this->getNameForAreaCode($areacode);
-            if ($name !== null) {
-                return $name;
-            }
-        }
-
-        return null;
-    }
-
-    protected function getNameForAreaCode($areacode)
-    {
-        $stm = $this->db->query(
-            'SELECT loc_id FROM geodb_textdata'
-            . ' WHERE text_type = "500400000"'//area code
-            . ' AND text_val = ' . $this->db->quote($areacode)
-        );
-        $res = $stm->fetch();
-        if ($res === false) {
-            //area code does not exist
-            return null;
-        }
 
-        $locId = $res['loc_id'];
+        //FIXME: what about international numbers?
+        //area codes in germany can be 3 to 6 numbers
         $stm = $this->db->query(
-            'SELECT text_val FROM geodb_textdata'
-            . ' WHERE text_type = "500100000"'//name
-            . ' AND loc_id = ' . $this->db->quote($locId)
+            'SELECT name FROM my_orte'
+            . ' WHERE vorwahl = ' . $this->db->quote(substr($number, 0, 3))
+            . ' OR vorwahl = ' . $this->db->quote(substr($number, 0, 4))
+            . ' OR vorwahl = ' . $this->db->quote(substr($number, 0, 5))
+            . ' OR vorwahl = ' . $this->db->quote(substr($number, 0, 6))
+            . ' ORDER BY einwohner DESC'
         );
         $res = $stm->fetch();
         if ($res === false) {
             return null;
         }
 
-        return $res['text_val'];
+        return $res['name'];
     }
 
 }