From: Christian Weiske <cweiske@cweiske.de>
Date: Tue, 7 Aug 2012 13:07:05 +0000 (+0200)
Subject: call logging to database
X-Git-Tag: v1.0.0~44
X-Git-Url: https://git.cweiske.de/auerswald-callnotifier.git/commitdiff_plain/67eb8026d25dd261358ef5de1c8a5a6111a8e282

call logging to database
---

diff --git a/docs/call-log.sql b/docs/call-log.sql
new file mode 100644
index 0000000..0b14b07
--- /dev/null
+++ b/docs/call-log.sql
@@ -0,0 +1,14 @@
+CREATE TABLE `finished` (
+  `call_id` int(11) NOT NULL AUTO_INCREMENT,
+  `call_start` datetime NOT NULL,
+  `call_end` datetime NOT NULL,
+  `call_type` varchar(1) NOT NULL,
+  `call_from` varchar(32) DEFAULT NULL,
+  `call_from_name` varchar(32) DEFAULT NULL,
+  `call_from_location` varchar(32) DEFAULT NULL,
+  `call_to` varchar(32) DEFAULT NULL,
+  `call_to_name` varchar(32) DEFAULT NULL,
+  `call_to_location` varchar(32) DEFAULT NULL,
+  `call_length` int(11) NOT NULL,
+  PRIMARY KEY (`call_id`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='finished calls';
diff --git a/scripts/create-calldb-dump.sql.sh b/scripts/create-calldb-dump.sql.sh
new file mode 100755
index 0000000..567a091
--- /dev/null
+++ b/scripts/create-calldb-dump.sql.sh
@@ -0,0 +1,8 @@
+#!/bin/sh
+cd "`dirname "$0"`"
+
+mysqldump -ucallnotifier -pcallnotifier callnotifier\
+ -d --skip-add-drop-table --skip-comments\
+ | grep -v '/*!'\
+ | grep -v '^$'\
+ > ../docs/call-log.sql
\ No newline at end of file
diff --git a/src/callnotifier/CLI.php b/src/callnotifier/CLI.php
index 6165f15..7109e73 100644
--- a/src/callnotifier/CLI.php
+++ b/src/callnotifier/CLI.php
@@ -64,6 +64,14 @@ class CLI
             new Logger_CallFile('all.log'),
             array('finishedCall')
         );
+        $log->addLogger(
+            new Logger_CallDb(
+                'mysql:host=localhost;dbname=callnotifier',
+                'callnotifier',
+                'callnotifier'
+            ),
+            array('finishedCall')
+        );
 
         $handler = new MessageHandler($this->config, $log, $callMonitor);
 
diff --git a/src/callnotifier/Logger/CallBase.php b/src/callnotifier/Logger/CallBase.php
new file mode 100644
index 0000000..8bd143c
--- /dev/null
+++ b/src/callnotifier/Logger/CallBase.php
@@ -0,0 +1,19 @@
+<?php
+namespace callnotifier;
+
+abstract class Logger_CallBase implements Logger
+{
+    protected function addUnsetVars($call)
+    {
+        static $expectedVars = array(
+            'toName', 'fromName', 'toLocation', 'fromLocation'
+        );
+        foreach ($expectedVars as $varName) {
+            if (!isset($call->$varName)) {
+                $call->$varName = null;
+            }
+        }
+    }
+}
+
+?>
diff --git a/src/callnotifier/Logger/CallDb.php b/src/callnotifier/Logger/CallDb.php
new file mode 100644
index 0000000..110e963
--- /dev/null
+++ b/src/callnotifier/Logger/CallDb.php
@@ -0,0 +1,83 @@
+<?php
+namespace callnotifier;
+
+class Logger_CallDb extends Logger_CallBase
+{
+    /**
+     * 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(
+            $dsn, $username, $password,
+            array(
+                \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
+                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC
+            )
+        );
+        $this->stmt = $this->db->prepare(
+            'INSERT INTO finished ('
+            . '  call_start'
+            . ', call_end'
+            . ', call_type'
+            . ', call_from'
+            . ', call_from_name'
+            . ', call_from_location'
+            . ', call_to'
+            . ', call_to_name'
+            . ', call_to_location'
+            . ', call_length'
+            . ') VALUES ('
+            . '  :call_start'
+            . ', :call_end'
+            . ', :call_type'
+            . ', :call_from'
+            . ', :call_from_name'
+            . ', :call_from_location'
+            . ', :call_to'
+            . ', :call_to_name'
+            . ', :call_to_location'
+            . ', :call_length'
+            . ')'
+        );
+    }
+
+    public function log($type, $arData)
+    {
+        if ($type != 'finishedCall') {
+            return;
+        }
+
+        $call = $arData['call'];
+        $this->addUnsetVars($call);
+
+        $ret = $this->stmt->execute(
+            array(
+                'call_start'         => date('Y-m-d H:i:s', $call->start),
+                'call_end'           => date('Y-m-d H:i:s', $call->end),
+                'call_type'          => $call->type,
+                'call_from'          => $call->from,
+                'call_from_name'     => $call->fromName,
+                'call_from_location' => $call->fromLocation,
+                'call_to'            => $call->to,
+                'call_to_name'       => $call->toName,
+                'call_to_location'   => $call->toLocation,
+                'call_length'        => $call->end - $call->start
+            )
+        );
+        if ($ret === false) {
+            throw new \Exception(
+                'Error logging call to database: '
+                . implode(' / ', $this->stmt->errorInfo())
+            );
+        }
+    }
+
+}
+
+?>
diff --git a/src/callnotifier/Logger/CallFile.php b/src/callnotifier/Logger/CallFile.php
index 85bf287..ba45137 100644
--- a/src/callnotifier/Logger/CallFile.php
+++ b/src/callnotifier/Logger/CallFile.php
@@ -1,7 +1,7 @@
 <?php
 namespace callnotifier;
 
-class Logger_CallFile implements Logger
+class Logger_CallFile extends Logger_CallBase
 {
     protected $file;
     protected $fileHdl;
@@ -101,18 +101,6 @@ class Logger_CallFile implements Logger
         return str_pad($number, 12, ' ', STR_PAD_RIGHT);
     }
 
-    protected function addUnsetVars($call)
-    {
-        static $expectedVars = array(
-            'toName', 'fromName', 'toLocation', 'fromLocation'
-        );
-        foreach ($expectedVars as $varName) {
-            if (!isset($call->$varName)) {
-                $call->$varName = null;
-            }
-        }
-    }
-
 }
 
 ?>