From 153fb4f6a68fafd7598ed3739795815d12fc0472 Mon Sep 17 00:00:00 2001 From: Christian Weiske Date: Sun, 5 Aug 2012 21:33:13 +0200 Subject: [PATCH] opengeodb and ldap detaillers work! --- docs/opengeodb-create-my_orte.sql | 22 +++++++ src/callnotifier/CLI.php | 19 +++++- .../CallMonitor/Detailler/LDAP.php | 43 ++++++++++++- .../CallMonitor/Detailler/OpenGeoDb.php | 64 +++++++++---------- 4 files changed, 110 insertions(+), 38 deletions(-) create mode 100644 docs/opengeodb-create-my_orte.sql diff --git a/docs/opengeodb-create-my_orte.sql b/docs/opengeodb-create-my_orte.sql new file mode 100644 index 0000000..e2568d4 --- /dev/null +++ b/docs/opengeodb-create-my_orte.sql @@ -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); diff --git a/src/callnotifier/CLI.php b/src/callnotifier/CLI.php index 8958f78..16c95b0 100644 --- a/src/callnotifier/CLI.php +++ b/src/callnotifier/CLI.php @@ -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); diff --git a/src/callnotifier/CallMonitor/Detailler/LDAP.php b/src/callnotifier/CallMonitor/Detailler/LDAP.php index 833fd46..ffcc9e1 100644 --- a/src/callnotifier/CallMonitor/Detailler/LDAP.php +++ b/src/callnotifier/CallMonitor/Detailler/LDAP.php @@ -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; } } diff --git a/src/callnotifier/CallMonitor/Detailler/OpenGeoDb.php b/src/callnotifier/CallMonitor/Detailler/OpenGeoDb.php index a37ede0..2b48004 100644 --- a/src/callnotifier/CallMonitor/Detailler/OpenGeoDb.php +++ b/src/callnotifier/CallMonitor/Detailler/OpenGeoDb.php @@ -1,6 +1,17 @@ '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']; } } -- 2.30.2