9 * @author Christian Weiske <cweiske@cweiske.de>
10 * @copyright 2014 Christian Weiske
11 * @license http://www.gnu.org/licenses/agpl.html GNU AGPL v3
12 * @link http://cweiske.de/bdrem.htm
17 * Fetch data from an SQL database
21 * @author Christian Weiske <cweiske@cweiske.de>
22 * @copyright 2014 Christian Weiske
23 * @license http://www.gnu.org/licenses/agpl.html GNU AGPL v3
24 * @link http://cweiske.de/bdrem.htm
29 * PDO data source description
47 * Database table with event data
55 * - date - array of columns with dates and their event title,
56 * e.g. 'c_birthday' => 'Birthday'
57 * - name - array of name columns
58 * - nameFormat - sprintf-compatible name formatting instruction,
66 * Set SQL server configuration
68 * @param array $config SQL server configuration with keys:
69 * dsn, user, password, table and fields
71 public function __construct($config)
73 $this->dsn = $config['dsn'];
74 $this->user = $config['user'];
75 $this->password = $config['password'];
76 $this->table = $config['table'];
77 $this->fields = $config['fields'];
81 * Return all events for the given date range
83 * @param string $strDate Date the events shall be found for,
85 * @param integer $nDaysPrevious Include number of days before $strDate
86 * @param integer $nDaysNext Include number of days after $strDate
88 * @return Event[] Array of matching event objects
90 public function getEvents($strDate, $nDaysPrevious, $nDaysNext)
93 $this->dsn, $this->user, $this->password,
94 array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION)
96 $arDays = $this->getDates($strDate, $nDaysPrevious, $nDaysNext);
99 foreach ($this->fields['date'] as $field => $typeName) {
100 if (substr($this->dsn, 0, 5) == 'dblib') {
101 //MS SQL Server does of course cook its own sht
102 $sqlMonth = 'DATEPART(month, ' . $field . ')';
103 $sqlDay = 'DATEPART(day, ' . $field . ')';
105 $sqlMonth = 'EXTRACT(MONTH FROM ' . $field . ')';
106 $sqlDay = 'EXTRACT(DAY FROM ' . $field . ')';
110 foreach ($arDays as $month => $days) {
112 . $sqlMonth . ' = ' . $dbh->quote($month, \PDO::PARAM_INT)
113 . ' AND ' . $sqlDay . ' >= '
114 . $dbh->quote(min($days), \PDO::PARAM_INT)
115 . ' AND ' . $sqlDay . ' <= '
116 . $dbh->quote(max($days), \PDO::PARAM_INT)
119 $sql = 'SELECT ' . $field . ' AS e_date'
120 . ', ' . implode(', ', (array) $this->fields['name'])
121 . ' FROM ' . $this->table
123 . implode(' OR ', $parts);
125 $res = $dbh->query($sql);
126 while ($row = $res->fetchObject()) {
127 $arNameData = array();
128 foreach ((array) $this->fields['name'] as $fieldName) {
129 $arNameData[] = $row->$fieldName;
131 $name = call_user_func_array(
134 array($this->fields['nameFormat']),
140 str_replace('0000', '????', $row->e_date)
142 if ($event->isWithin($strDate, $nDaysPrevious, $nDaysNext)) {
143 $arEvents[] = $event;
151 * Create an array of dates that are included in the given range.
153 * @param string $strDate Date the events shall be found for,
155 * @param integer $nDaysPrevious Include number of days before $strDate
156 * @param integer $nDaysNext Include number of days after $strDate
158 * @return array Key is the month, value an array of days
160 protected function getDates($strDate, $nDaysPrevious, $nDaysNext)
162 $ts = strtotime($strDate) - 86400 * $nDaysPrevious;
163 $numDays = $nDaysPrevious + $nDaysNext;
167 $arDays[(int) date('n', $ts)][] = (int) date('j', $ts);
169 } while (--$numDays >= 0);