From fe70439784d16f49b4b95befb3df62a62a851174 Mon Sep 17 00:00:00 2001
From: Christian Weiske <cweiske@cweiske.de>
Date: Wed, 12 Feb 2014 20:48:01 +0100
Subject: [PATCH] first sql source implementation

---
 data/bdrem.config.php.dist | 22 ++++++++++
 src/bdrem/Source/Sql.php   | 86 ++++++++++++++++++++++++++++++++++++++
 2 files changed, 108 insertions(+)
 create mode 100644 src/bdrem/Source/Sql.php

diff --git a/data/bdrem.config.php.dist b/data/bdrem.config.php.dist
index 87d01fc..7af4ed0 100644
--- a/data/bdrem.config.php.dist
+++ b/data/bdrem.config.php.dist
@@ -1,5 +1,27 @@
 <?php
+//source: birthday file
 $source = array('Bdf', '/path/to/birthday.bdf');
+
+//Source: SQL database
+$source = array(
+    'Sql',
+    array(
+        'dsn' => 'mysql:dbname=bdrem;host=127.0.0.1',
+        'user' => 'FIXME',
+        'password' => 'FIXME',
+        'table' => 'contacts',
+        'fields' => array(
+            'date' => array(
+                //column name => event title
+                'c_birthday' => 'Birthday'
+            ),
+            //column with name
+            'name' => 'c_name'
+        )
+    )
+);
+
+
 $daysBefore = 3;
 $daysAfter = 14;
 $locale = 'de_DE.UTF-8';
diff --git a/src/bdrem/Source/Sql.php b/src/bdrem/Source/Sql.php
new file mode 100644
index 0000000..ef2d3a0
--- /dev/null
+++ b/src/bdrem/Source/Sql.php
@@ -0,0 +1,86 @@
+<?php
+namespace bdrem;
+
+/**
+ * Fetch data from an SQL database
+ */
+class Source_Sql
+{
+    protected $dsn;
+    protected $user;
+    protected $password;
+    protected $table;
+    protected $fields ;
+
+    public function __construct($config)
+    {
+        $this->dsn      = $config['dsn'];
+        $this->user     = $config['user'];
+        $this->password = $config['password'];
+        $this->table    = $config['table'];
+        $this->fields   = $config['fields'];
+    }
+
+    /**
+     * @param string $strDate Date the events shall be found for, YYYY-MM-DD
+     */
+    public function getEvents($strDate, $nDaysBefore, $nDaysAfter)
+    {
+        $dbh = new \PDO($this->dsn, $this->user, $this->password);
+        $arDays = $this->getDates($strDate, $nDaysBefore, $nDaysAfter);
+        $arEvents = array();
+
+        foreach ($this->fields['date'] as $field => $typeName) {
+            $fieldSql = 'CONCAT('
+                . 'EXTRACT(MONTH FROM ' . $field . '),'
+                . '"-",'
+                . 'EXTRACT(DAY FROM ' . $field . ')'
+                . ') = ';
+
+            $parts = array();
+            foreach ($arDays as $day) {
+                $parts[] = $fieldSql . $dbh->quote($day);
+            }
+            $sql = 'SELECT ' . $field . ' AS e_date'
+                . ', ' . $this->fields['name'] . ' AS e_name'
+                . ' FROM ' . $this->table
+                . ' WHERE '
+                . implode(' OR ', $parts);
+
+            $res = $dbh->query($sql);
+            if ($res === false) {
+                $errorInfo = $dbh->errorInfo();
+                throw new \Exception(
+                    'SQL error #' . $errorInfo[0]
+                    . ': ' . $errorInfo[1]
+                    . ': ' . $errorInfo[2],
+                    (int) $errorInfo[1]
+                );
+            }
+            while ($row = $res->fetchObject()) {
+                $event = new Event(
+                    $row->e_name, $typeName, 
+                    str_replace('0000', '????', $row->e_date)
+                );
+                if ($event->isWithin($strDate, $nDaysBefore, $nDaysAfter)) {
+                    $arEvents[] = $event;
+                }
+            }
+        }
+        return $arEvents;
+    }
+
+    protected function getDates($strDate, $nDaysBefore, $nDaysAfter)
+    {
+        $ts = strtotime($strDate) - 86400 * $nDaysBefore;
+        $numDays = $nDaysBefore + $nDaysAfter;
+
+        $arDays = array();
+        do {
+            $arDays[] = date('n-j', $ts);
+            $ts += 86400;
+        } while (--$numDays >= 0);
+        return $arDays;
+    }
+}
+?>
-- 
2.30.2