<?php
/**
 * The Kronolith_Driver_sql:: class implements the Kronolith_Driver
 * API for a SQL backend.
 *
 * $Horde: kronolith/lib/Driver/sql.php,v 1.31.2.16 2004/04/03 04:12:59 chuck Exp $
 *
 * @author  Luc Saillard <luc.saillard@fr.alcove.com>
 * @author  Chuck Hagenbuch <chuck@horde.org>
 * @version $Revision: 1.31.2.16 $
 * @since   Kronolith 0.3
 * @package kronolith
 */
class Kronolith_Driver_sql extends Kronolith_Driver {

    /**
     * The object handle for the current database connection.
     * @var object DB $_db
     */
    var $_db;

    /**
     * Boolean indicating whether or not we're currently connected to
     * the SQL server.
     * @var boolean $_connected
     */
    var $_connected = false;

    /**
     * Cache events as we fetch them to avoid fetching the same event
     * from the db twice.
     * @var array $_cache
     */
    var $_cache = array();

    function open($calendar)
    {
        $this->_calendar = $calendar;
        $this->_connect();
    }

    function listAlarms($date)
    {
        $allevents = $this->listEvents($date, $date, true);
        $events = array();

        foreach ($allevents as $eventId) {
            $event = &$this->getEventObject($eventId);

            if ($event->getRecurType() == KRONOLITH_RECUR_NONE) {
                $diff = Kronolith::dateDiff($date,
                                            Kronolith::timestampToObject($event->startTimestamp));
                if ($diff->sec < $event->getAlarm() * 60) {
                    $events[] = $eventId;
                }
            } else {
                $next = $this->nextRecurrence($eventId, $date);
                if ($next) {
                    $diff = Kronolith::dateDiff($date, $next);
                    if ($diff->sec < $event->getAlarm() * 60) {
                        $events[] = $eventId;
                    }
                }
            }
        }

        return is_array($events) ? $events : array();
    }

    function listEvents($startDate = null, $endDate = null, $hasAlarm = false)
    {
        if (!isset($endDate)) {
            $endDate = Kronolith::dateObject(array('mday' => 31, 'month' => 12, 'year' => 9999));
        } else {
            list($endDate->mday, $endDate->month, $endDate->year) = explode('/', Date_Calc::nextDay($endDate->mday, $endDate->month, $endDate->year, '%d/%m/%Y'));
        }

        $etime = sprintf('%04d-%02d-%02d 00:00:00', $endDate->year, $endDate->month, $endDate->mday);
        if (isset($startDate)) {
            if ($startDate === 0) {
                $startDate = Kronolith::dateObject(array('mday' => 1, 'month' => 1, 'year' => 0000));
            }
            if ($startDate->month == 0) { $startDate->month = 1; }
            if ($startDate->mday == 0) { $startDate->mday = 1; }
            $stime = sprintf('%04d-%02d-%02d 00:00:00', $startDate->year, $startDate->month, $startDate->mday);
        }

        $q = 'SELECT DISTINCT event_id, event_description, event_location,' .
            ' event_keywords, event_title, event_category,' .
            ' event_recurtype, event_recurenddate, event_recurinterval,' .
            ' event_recurdays, event_start, event_end, event_alarm,' .
            ' event_modified, event_exceptions FROM ' . $this->_params['table'] .
            ' WHERE calendar_id = ' . $this->_db->quote($this->_calendar) . ' AND ((';

        if ($hasAlarm) {
            $q .= 'event_alarm > 0)) AND ((';
        }

        if (isset($stime)) {
            $q .= 'event_end > ' . $this->_db->quote($stime) . ' AND ';
        }
        $q .= 'event_start < ' . $this->_db->quote($etime) . ') OR (';
        if (isset($stime)) {
            $q .= 'event_recurenddate >= ' . $this->_db->quote($stime) . ' AND ';
        }
        $q .= 'event_start <= ' . $this->_db->quote($etime) .
              ' AND event_recurtype != ' . KRONOLITH_RECUR_NONE . '))';

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('SQL event list by %s: table = %s; query = "%s"',
                                  Auth::getAuth(), $this->_params['table'], $q),
                          __FILE__, __LINE__, LOG_DEBUG);

        /* Run the query. */
        $qr = $this->_db->query($q);

        $events = array();
        if (!PEAR::isError($qr)) {
            $row = $qr->fetchRow(DB_FETCHMODE_ASSOC);
            while ($row && !PEAR::isError($row)) {
                // We have all the information we need to create an
                // event object for this event, so go ahead and cache
                // it.
                $this->_cache[$this->_calendar][$row['event_id']] = &new Kronolith_Event_sql($this, $row);

                if ($row['event_recurtype'] == KRONOLITH_RECUR_NONE) {
                    $events[] = $row['event_id'];
                } else {
                    $next = $this->nextRecurrence($row['event_id'], $startDate);
                    if ($next && Kronolith::compareDates($next, $endDate) < 0) {
                        $events[] = $row['event_id'];
                    }
                }

                $row = $qr->fetchRow(DB_FETCHMODE_ASSOC);
            }
        }

        return $events;
    }

    function &getEventObject($eventId = null)
    {
        if (!is_null($eventId)) {
            if (isset($this->_cache[$this->_calendar][$eventId])) {
                return $this->_cache[$this->_calendar][$eventId];
            }

            $event = &$this->_db->getRow('SELECT event_id, event_description, event_location,' .
                                         ' event_keywords, event_title, event_category,' .
                                         ' event_recurtype, event_recurenddate, event_recurinterval,' .
                                         ' event_recurdays, event_start, event_end, event_alarm,' .
                                         ' event_modified, event_exceptions' .
                                         ' FROM ' . $this->_params['table'] .
                                         ' WHERE event_id = ' . (int)$eventId .
                                         ' AND calendar_id = ' . $this->_db->quote($this->_calendar),
                                         DB_FETCHMODE_ASSOC);
            if (PEAR::isError($event)) {
                return $event;
            }

            if ($event) {
                $this->_cache[$this->_calendar][$eventId] = &new Kronolith_Event_sql($this, $event);
                return $this->_cache[$this->_calendar][$eventId];
            } else {
                return false;
            }
        } else {
            return new Kronolith_Event_sql($this);
        }
    }

    function saveEvent($event)
    {
        if (!is_null($event->getID())) {
            $query = 'UPDATE ' . $this->_params['table'] . ' SET ';

            foreach ($event->getProperties() as $key => $val) {
                $query .= " $key = " . $this->_db->quote($val) . ',';
            }
            $query = substr($query, 0, -1);
            $query .= ' WHERE event_id = ' . $this->_db->quote((int)$event->getID());

            /* Log the query at a DEBUG log level. */
            Horde::logMessage(sprintf('SQL event update by %s: table = %s; query = "%s"',
                                      Auth::getAuth(), $this->_params['table'], $query),
                              __FILE__, __LINE__, LOG_DEBUG);

            $res = $this->_db->query($query);
            if (PEAR::isError($res)) {
                return $res;
            }

            return $event->getID();
        } else {
            $id = $this->_db->nextId($this->_params['table']);
            if (PEAR::isError($id)) {
                return $id;
            }

            $query = 'INSERT INTO ' . $this->_params['table'] . ' ';
            $cols_name = '(event_id,';
            $cols_values = 'values (' . $this->_db->quote($id) . ',';

            foreach ($event->getProperties() as $key => $val) {
                $cols_name .= " $key,";
                $cols_values .= $this->_db->quote($val) . ',';
            }

            $cols_name .= ' calendar_id)';
            $cols_values .= $this->_db->quote($this->_calendar) . ')';

            /* Log the query at a DEBUG log level. */
            Horde::logMessage(sprintf('SQL event store by %s: table = %s; query = "%s"',
                                Auth::getAuth(), $this->_params['table'], $query . $cols_name . $cols_values),
                                __FILE__, __LINE__, LOG_DEBUG);

            $res = $this->_db->query($query . $cols_name . $cols_values);
            if (PEAR::isError($res)) {
                return $res;
            }

            return $id;
        }
    }

    function nextRecurrence($eventId, $afterDate)
    {
        $afterDate = Kronolith::dateObject($afterDate);
        $afterDate = Kronolith::correctDate($afterDate);

        $event = &$this->getEventObject($eventId);
        if (PEAR::isError($event)) {
            return $event;
        }

        $afterDate = Kronolith::dateObject($afterDate);

        if (Kronolith::compareDates($event->start, $afterDate) >=0) {
            return $event->start;
        }

        $event->recurEnd->hour = 23;
        $event->recurEnd->min = 59;
        $event->recurEnd->sec = 59;

        switch ($event->getRecurType()) {
        case KRONOLITH_RECUR_DAILY:
            $diff = Date_Calc::dateDiff($event->start->mday, $event->start->month, $event->start->year, $afterDate->mday, $afterDate->month, $afterDate->year);
            $recur = ceil($diff / $event->recurInterval) * $event->recurInterval;
            $next = $event->start;
            list($next->mday, $next->month, $next->year) = explode('/', Date_Calc::daysToDate(Date_Calc::dateToDays($next->mday, $next->month, $next->year) + $recur, '%e/%m/%Y'));
            if (Kronolith::compareDates($next, $event->recurEnd) <= 0 &&
                Kronolith::compareDates($next, $afterDate) >= 0) {
                return $next;
            }
            break;

        case KRONOLITH_RECUR_WEEKLY:
            list($start_week->mday, $start_week->month, $start_week->year) = explode('/', Date_Calc::beginOfWeek($event->start->mday, $event->start->month, $event->start->year, '%e/%m/%Y'));
            $start_week->hour = $event->start->hour;
            $start_week->min = $event->start->min;
            $start_week->sec = $event->start->sec;
            list($after_week->mday, $after_week->month, $after_week->year) = explode('/', Date_Calc::beginOfWeek($afterDate->mday, $afterDate->month, $afterDate->year, '%e/%m/%Y'));
            $after_week_end = $after_week;
            $after_week_end->mday += 7;
            $after_week_end = Kronolith::correctDate($after_week_end);

            $diff = Date_Calc::dateDiff($start_week->mday, $start_week->month, $start_week->year, $after_week->mday, $after_week->month, $after_week->year);
            $recur = $diff + $diff % ($event->recurInterval * 7);
            $next = $start_week;
            list($next->mday, $next->month, $next->year) = explode('/', Date_Calc::daysToDate(Date_Calc::dateToDays($next->mday, $next->month, $next->year) + $recur, '%e/%m/%Y'));
            while (Kronolith::compareDates($next, $afterDate) < 0 && Kronolith::compareDates($next, $after_week_end) < 0) {
                $next->mday++;
                $next = Kronolith::correctDate($next);
            }
            if (Kronolith::compareDates($next, $event->recurEnd) <= 0) {
                if (Kronolith::compareDates($next, $after_week_end) >= 0) {
                    return $this->nextRecurrence($eventId, $after_week_end);
                }
                while (!$event->recurOnDay((int)pow(2, (int)Date_Calc::dayOfWeek($next->mday, $next->month, $next->year))) && Kronolith::compareDates($next, $after_week_end) < 0) {
                    $next->mday++;
                    $next = Kronolith::correctDate($next);
                }
                if (Kronolith::compareDates($next, $event->recurEnd) <= 0) {
                    if (Kronolith::compareDates($next, $after_week_end) >= 0) {
                        return $this->nextRecurrence($eventId, $after_week_end);
                    } else {
                        return Kronolith::dateObject($next);
                    }
                }
            }
            break;

        case KRONOLITH_RECUR_DAY_OF_MONTH:
            $diff = Date_Calc::dateDiff($event->start->mday, $event->start->month, $event->start->year, $afterDate->mday, $afterDate->month, $afterDate->year);
            $start = &Kronolith::dateObject($event->start);
            $start->mday += $diff;
            $diff = $start->month - $event->start->month + (($start->year - $event->start->year) * 12);

            $recur = $diff + $diff % $event->recurInterval;
            $next = $event->start;
            $next->month += $recur;
            $next = Kronolith::correctDate($next);
            if (Kronolith::compareDates($next, $event->recurEnd) <= 0 &&
                Kronolith::compareDates($next, $afterDate) >= 0) {
                return $next;
            }
            break;

        case KRONOLITH_RECUR_WEEK_OF_MONTH:
            // Start with the start date of the event.
            $estart = Kronolith::dateObject($event->start);

            // What day of the week, and week of the month, do we
            // recur on?
            $nth = ceil($event->start->mday / 7);
            $weekday = Kronolith::dayOfWeek($estart->year, $estart->month, $estart->mday);

            // Adjust $estart to be the first candidate.
            $offset = ($afterDate->month - $estart->month) + ($afterDate->year - $estart->year) * 12;
            $offset = floor(($offset + $event->recurInterval - 1) / $event->recurInterval) * $event->recurInterval;

            // Adjust our working date until it's after $afterDate.
            $estart->month += $offset - $event->recurInterval;
            do {
                $estart->month += $event->recurInterval;
                $estart = Kronolith::correctDate($estart);

                $next = Kronolith::dateObject($estart);
                $next = Kronolith::setNthWeekday($next, $weekday, $nth);

                if (Kronolith::compareDates($next, $afterDate) < 0) {
                    // We haven't made it past $afterDate yet, try
                    // again.
                    continue;
                }
                if (Kronolith::compareDates($next, $event->recurEnd) > 0) {
                    // We've gone past the end of recurrence; we can
                    // give up now.
                    return false;
                }

                // We have a candidate to return.
                break;
            } while (true);

            return $next;

        case KRONOLITH_RECUR_YEARLY:
            // Start with the start date of the event.
            $estart = Kronolith::dateObject($event->start);

            // We probably need a seperate case here for February 29th
            // and leap years, but until we're absolutely sure it's a
            // bug, we'll leave it out.
            if ($afterDate->month > $estart->month ||
                ($afterDate->month == $estart->month && $afterDate->mday > $estart->mday)) {
                $afterDate->year++;
                $afterDate->month = $estart->month;
                $afterDate->mday = $estart->mday;
            }

            // Adjust $estart to be the first candidate.
            $offset = $afterDate->year - $estart->year;
            if ($offset > 0) {
                $offset = (($offset + $event->recurInterval - 1) / $event->recurInterval) * $event->recurInterval;
                $estart->year += $offset;
            }

            // We've gone past the end of recurrence; give up.
            if (Kronolith::compareDates($event->recurEnd, $estart) < 0) {
                return false;
            }

            return $estart;
        }

        // We didn't find anything, the recurType was bad, or
        // something else went wrong - return false.
        return false;
    }

    /**
     * Delete a calendar and all its events.
     *
     * @param string $calendar The name of the calendar to delete.
     *
     * @return mixed  True or a PEAR_Error on failure.
     */
    function delete($calendar)
    {
        $this->_connect();

        $query = sprintf('DELETE FROM %s WHERE calendar_id = %s',
                    $this->_params['table'],
                    $this->_db->quote($calendar));

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('SQL Calender Delete by %s: table = %s; query = "%s"',
                                  Auth::getAuth(), $this->_params['table'], $query),
                          __FILE__, __LINE__, LOG_DEBUG);

        return $this->_db->query($query);
    }

    /**
     * Delete an event.
     *
     * @param string $eventId The id of the event to delete.
     *
     * @return mixed  True or a PEAR_Error on failure.
     */
    function deleteEvent($eventId)
    {
        $eventId = (int)$eventId;
        $query = sprintf('DELETE FROM %s WHERE event_id = %s AND calendar_id = %s',
                         $this->_params['table'],
                         $this->_db->quote($eventId),
                         $this->_db->quote($this->_calendar));

        /* Log the query at a DEBUG log level. */
        Horde::logMessage(sprintf('SQL Event Delete by %s: table = %s; query = "%s"',
                                  Auth::getAuth(), $this->_params['table'], $query),
                          __FILE__, __LINE__, LOG_DEBUG);

        return $this->_db->query($query);
    }

    /**
     * Attempts to open a persistent connection to the SQL server.
     *
     * @return boolean True.
     */
    function _connect()
    {
        if (!$this->_connected) {
            require_once 'DB.php';

            if (!is_array($this->_params)) {
                Horde::fatal(PEAR::raiseError(_("No configuration information specified for SQL Calendar.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['phptype'])) {
                Horde::fatal(PEAR::raiseError(_("Required 'phptype' not specified in calendar configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['hostspec'])) {
                Horde::fatal(PEAR::raiseError(_("Required 'hostspec' not specified in calendar configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['username'])) {
                Horde::fatal(PEAR::raiseError(_("Required 'username' not specified in calendar configuration.")), __FILE__, __LINE__);
            }
            if (!isset($this->_params['password'])) {
                Horde::fatal(PEAR::raiseError(_("Required 'password' not specified in calendar configuration.")), __FILE__, __LINE__);
            }

            /* Connect to the SQL server using the supplied parameters. */
            $this->_db = &DB::connect($this->_params, true);
            if (PEAR::isError($this->_db)) {
                Horde::fatal($this->_db, __FILE__, __LINE__);
            }

            /* Enable the "portability" option. */
            $this->_db->setOption('optimize', 'portability');

            $this->_connected = true;

            /* Handle any database specific initialization code to
             * run. */
            switch ($this->_db->dbsyntax) {
            case 'oci8':
                $query = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";

                /* Log the query at a DEBUG log level. */
                Horde::logMessage(sprintf('SQL session setup by %s: table = %s; query = "%s"',
                                          Auth::getAuth(), $this->_params['table'], $query),
                                  __FILE__, __LINE__, LOG_DEBUG);

                $this->_db->query($query);
                break;
            }
        }

        return true;
    }

    function close()
    {
        return true;
    }

    /**
     * Disconnect from the SQL server and clean up the connection.
     *
     * @return boolean true on success, false on failure.
     */
    function _disconnect()
    {
        if ($this->_connected) {
            $this->_connected = false;
            return $this->_db->disconnect();
        }

        return true;
    }

}

class Kronolith_Event_sql extends Kronolith_Event {

    var $_properties = array();

    function fromDriver($SQLEvent)
    {
        $driver = &$this->getDriver();

        $this->start = &new stdClass();
        $this->end = &new stdClass();
        list($this->start->year, $this->start->month, $this->start->mday, $this->start->hour, $this->start->min, $this->start->sec) = sscanf($SQLEvent['event_start'], '%04d-%02d-%02d %02d:%02d:%02d');
        list($this->end->year, $this->end->month, $this->end->mday, $this->end->hour, $this->end->min, $this->end->sec) = sscanf($SQLEvent['event_end'], '%04d-%02d-%02d %02d:%02d:%02d');

        $this->startTimestamp = mktime($this->start->hour, $this->start->min, $this->start->sec, $this->start->month, $this->start->mday, $this->start->year);
        $this->endTimestamp = mktime($this->end->hour, $this->end->min, $this->end->sec, $this->end->month, $this->end->mday, $this->end->year);

        $this->durMin = ($this->endTimestamp - $this->startTimestamp) / 60;

        if (isset($SQLEvent['event_recurenddate'])) {
            $this->recurEnd = &new stdClass();
            list($this->recurEnd->year, $this->recurEnd->month, $this->recurEnd->mday, $this->recurEnd->hour, $this->recurEnd->min, $this->recurEnd->sec) = sscanf($SQLEvent['event_recurenddate'], '%04d-%02d-%02d %02d:%02d:%02d');
            $this->recurEndTimestamp = @mktime($this->recurEnd->hour, $this->recurEnd->min, $this->recurEnd->sec, $this->recurEnd->month, $this->recurEnd->mday, $this->recurEnd->year);
        }

        $this->title = $SQLEvent['event_title'];
        $this->eventID = $SQLEvent['event_id'];
        $this->recurType = (int)$SQLEvent['event_recurtype'];
        $this->recurInterval = (int)$SQLEvent['event_recurinterval'];

        if (isset($SQLEvent['event_category'])) {
            $this->category = $SQLEvent['event_category'];
        }
        if (isset($SQLEvent['event_location'])) {
            $this->location = $SQLEvent['event_location'];
        }
        if (isset($SQLEvent['event_keywords'])) {
            $this->keywords = explode(',', $SQLEvent['event_keywords']);
        }
        if (isset($SQLEvent['event_exceptions'])) {
            $this->exceptions = explode(',', $SQLEvent['event_exceptions']);
        }
        if (isset($SQLEvent['event_description'])) {
            $this->description = $SQLEvent['event_description'];
        }
        if (isset($SQLEvent['event_alarm'])) {
            $this->alarm = (int)$SQLEvent['event_alarm'];
        }
        if (isset($SQLEvent['event_recurdays'])) {
            $this->recurData = (int)$SQLEvent['event_recurdays'];
        }

        $this->initialized = true;
    }

    function toDriver()
    {
        $driver = &$this->getDriver();

        // Basic fields.
        $this->_properties['event_title'] = $this->getTitle();
        $this->_properties['event_description'] = $this->getDescription();
        $this->_properties['event_category'] = $this->getCategory();
        $this->_properties['event_location'] = $this->getLocation();
        $this->_properties['event_keywords'] = implode(',', $this->getKeywords());
        $this->_properties['event_exceptions'] = implode(',', $this->getExceptions());
        $this->_properties['event_modified'] = time();
        $this->_properties['event_modified'] = time();

        // Event start.
        $this->_properties['event_start'] = date('Y-m-d H:i:s', $this->getStartTimestamp());

        // Event end.
        $this->_properties['event_end'] = date('Y-m-d H:i:s', $this->getEndTimestamp());

        // Alarm.
        $this->_properties['event_alarm'] = $this->getAlarm();

        // Recurrence.
        $recur_end = explode(':', @date('Y:n:j', $this->getRecurEndTimestamp()));
        if (empty($recur_end[0]) || $recur_end[0] <= 1970) {
            $recur_end[0] = 9999;
            $recur_end[1] = 12;
            $recur_end[2] = 31;
        }

        $recur = $this->getRecurType();
        $this->_properties['event_recurtype'] = $recur;
        if ($recur != KRONOLITH_RECUR_NONE) {
            $this->_properties['event_recurinterval'] = $this->getRecurInterval();
            $this->_properties['event_recurenddate'] = sprintf('%04d%02d%02d', $recur_end[0],
                                                               $recur_end[1], $recur_end[2]);

            switch ($recur) {
            case KRONOLITH_RECUR_WEEKLY:
                $this->_properties['event_recurdays'] = $this->getRecurOnDays();
                break;
            }
        }
    }

    function getProperties()
    {
        return $this->_properties;
    }

}