class.ilBookingEntry.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */

class ilBookingEntry
{
    protected $db;

    protected $user;

    private $id = 0;
    private $obj_id = 0;
    
    private $deadline = 0;
    private $num_bookings = 1;
    private $target_obj_ids = array();
    private $booking_group = 0;
    
    
    public function __construct($a_booking_id = 0)
    {
        global $DIC;

        $this->db = $DIC->database();
        $this->user = $DIC->user();
        $this->setId($a_booking_id);
        if ($this->getId()) {
            $this->read();
        }
    }
    
    public static function resetGroup($a_group_id)
    {
        global $DIC;

        $ilDB = $DIC->database();
        
        $query = 'UPDATE booking_entry SET booking_group = ' . $ilDB->quote(0, 'integer') . ' ' .
                'WHERE booking_group = ' . $ilDB->quote($a_group_id, 'integer');
        $ilDB->manipulate($query);
        return true;
    }
    
    public static function lookupBookingsOfUser($a_app_ids, $a_usr_id, ilDateTime $start = null)
    {
        global $DIC;

        $ilDB = $DIC->database();
        
        $query = 'SELECT entry_id FROM booking_user ' .
                'WHERE ' . $ilDB->in('entry_id', $a_app_ids, false, 'integer') . ' ' .
                'AND user_id = ' . $ilDB->quote($a_usr_id, 'integer');
        
        $res = $ilDB->query($query);
        
        $booked_entries = array();
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $booked_entries[] = $row->entry_id;
        }
        return $booked_entries;
    }
    
    protected function setId($a_id)
    {
        $this->id = (int) $a_id;
    }
    
    public function getId()
    {
        return $this->id;
    }
    
    public function setBookingGroup($a_id)
    {
        $this->booking_group = $a_id;
    }
    
    public function getBookingGroup()
    {
        return $this->booking_group;
    }
    
    public function setObjId($a_id)
    {
        $this->obj_id = (int) $a_id;
    }
    
    public function getObjId()
    {
        return $this->obj_id;
    }
    
    public function setDeadlineHours($a_hours)
    {
        $this->deadline = (int) $a_hours;
    }
    
    public function getDeadlineHours()
    {
        return $this->deadline;
    }
    
    public function setNumberOfBookings($a_num)
    {
        $this->num_bookings = (int) $a_num;
    }
    
    public function getNumberOfBookings()
    {
        return $this->num_bookings;
    }

    public function setTargetObjIds($a_obj_id)
    {
        $this->target_obj_ids = $a_obj_id;
    }

    public function getTargetObjIds()
    {
        return $this->target_obj_ids;
    }
    
    public function isTargetObjectVisible($a_ref_id)
    {
        // no course/group filter
        if (!$this->getTargetObjIds()) {
            return true;
        }
        
        $obj_id = ilObject::_lookupObjId($a_ref_id);
        return in_array($obj_id, $this->getTargetObjIds());
    }
    
    public function save()
    {
        $ilDB = $this->db;
        
        $this->setId($ilDB->nextId('booking_entry'));
        $query = 'INSERT INTO booking_entry (booking_id,obj_id,deadline,num_bookings,booking_group) ' .
            "VALUES ( " .
            $ilDB->quote($this->getId(), 'integer') . ', ' .
            $ilDB->quote($this->getObjId(), 'integer') . ', ' .
            $ilDB->quote($this->getDeadlineHours(), 'integer') . ', ' .
            $ilDB->quote($this->getNumberOfBookings(), 'integer') . ',' .
            $ilDB->quote($this->getBookingGroup(), 'integer') . ' ' .
            ") ";
        $ilDB->manipulate($query);
        
        foreach ((array) $this->target_obj_ids as $obj_id) {
            $query = 'INSERT INTO booking_obj_assignment (booking_id, target_obj_id) ' .
                    'VALUES( ' .
                    $ilDB->quote($this->getId(), 'integer') . ', ' .
                    $ilDB->quote($obj_id, 'integer') . ' ' .
                    ')';
            $ilDB->manipulate($query);
        }
        return true;
    }
    
    public function update()
    {
        $ilDB = $this->db;
        
        if (!$this->getId()) {
            return false;
        }
        
        $query = "UPDATE booking_entry SET " .
            " obj_id = " . $ilDB->quote($this->getObjId(), 'integer') . ", " .
            " deadline = " . $ilDB->quote($this->getDeadlineHours(), 'integer') . ", " .
            " num_bookings = " . $ilDB->quote($this->getNumberOfBookings(), 'integer') . ', ' .
            'booking_group = ' . $ilDB->quote($this->getBookingGroup(), 'integer') . ' ' .
            'WHERE booking_id = ' . $ilDB->quote($this->getId(), 'integer');
        $ilDB->manipulate($query);

        // obj assignments
        $query = 'DELETE FROM booking_obj_assignment ' .
                'WHERE booking_id = ' . $ilDB->quote($this->getId(), 'integer');
        $ilDB->manipulate($query);
        
        foreach ((array) $this->target_obj_ids as $obj_id) {
            $query = 'INSERT INTO booking_obj_assignment (booking_id, target_obj_id) ' .
                    'VALUES( ' .
                    $ilDB->quote($this->getId(), 'integer') . ', ' .
                    $ilDB->quote($obj_id, 'integer') . ' ' .
                    ')';
            $ilDB->manipulate($query);
        }
        return true;
    }
    
    public function delete()
    {
        $ilDB = $this->db;
        
        $query = "DELETE FROM booking_entry " .
            "WHERE booking_id = " . $ilDB->quote($this->getId(), 'integer');
        $ilDB->manipulate($query);
        
        $query = 'DELETE FROM booking_obj_assignment ' .
                'WHERE booking_id = ' . $ilDB->quote($this->getId(), 'integer');
        $ilDB->manipulate($query);

        return true;
    }

    protected function read()
    {
        $ilDB = $this->db;
        
        if (!$this->getId()) {
            return false;
        }
        
        $query = "SELECT * FROM booking_entry " .
            "WHERE booking_id = " . $ilDB->quote($this->getId(), 'integer');
        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) {
            $this->setObjId($row['obj_id']);
            $this->setDeadlineHours($row['deadline']);
            $this->setNumberOfBookings($row['num_bookings']);
            $this->setBookingGroup($row['booking_group']);
        }
        
        $query = 'SELECT * FROM booking_obj_assignment ' .
                'WHERE booking_id = ' . $ilDB->quote($this->getId(), 'integer');
        $res = $ilDB->query($query);
        
        $this->target_obj_ids = array();
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $this->target_obj_ids[] = $row->target_obj_id;
        }
        
        return true;
    }

    public function isOwner($a_user_id = null)
    {
        $ilUser = $this->user;

        if (!$a_user_id) {
            $a_user_id = $ilUser->getId();
        }

        if ($this->getObjId() == $a_user_id) {
            return true;
        }
        return false;
    }

    public static function removeObsoleteEntries()
    {
        global $DIC;

        $ilDB = $DIC->database();

        $set = $ilDB->query('SELECT DISTINCT(context_id) FROM cal_entries e' .
            ' JOIN cal_cat_assignments a ON (e.cal_id = a.cal_id)' .
            ' JOIN cal_categories c ON (a.cat_id = c.cat_id) WHERE c.type = ' . $ilDB->quote(ilCalendarCategory::TYPE_CH, 'integer'));
        
        $used = array();
        while ($row = $ilDB->fetchAssoc($set)) {
            $used[] = $row['context_id'];
        }

        $ilDB->query($q = 'DELETE FROM booking_entry WHERE ' . $ilDB->in('booking_id', $used, true, 'integer'));
        $ilDB->query($q = 'DELETE FROM booking_obj_assignment WHERE ' . $ilDB->in('booking_id', $used, true, 'integer'));
    }

    public static function getInstanceByCalendarEntryId($a_id)
    {
        include_once 'Services/Calendar/classes/class.ilCalendarEntry.php';
        $cal_entry = new ilCalendarEntry($a_id);
        $booking_id = $cal_entry->getContextId();
        if ($booking_id) {
            return new self($booking_id);
        }
    }

    public static function isBookable(array $a_obj_ids, $a_target_obj_id = null)
    {
        global $DIC;

        $ilDB = $DIC->database();
        
        if ($a_target_obj_id) {
            $query = 'SELECT DISTINCT(obj_id) FROM booking_entry be ' .
                    'JOIN booking_obj_assignment bo ON be.booking_id = bo.booking_id ' .
                    'WHERE ' . $ilDB->in('obj_id', $a_obj_ids, false, 'integer') . ' ' .
                    'AND bo.target_obj_id = ' . $ilDB->quote($a_target_obj_id, 'integer');
        } else {
            $query = 'SELECT DISTINCT(obj_id) FROM booking_entry be ' .
                    'WHERE ' . $ilDB->in('obj_id', $a_obj_ids, false, 'integer') . ' ';
        }

        $res = $ilDB->query($query);
        $all = array();
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $all[] = $row->obj_id;
        }
        return $all;
    }
    
    public static function lookupBookableUsersForObject($a_obj_id, $a_user_ids)
    {
        global $DIC;

        $ilDB = $DIC->database();
        
        $query = 'SELECT be.obj_id bobj FROM booking_entry be ' .
                'JOIN booking_obj_assignment bo ON be.booking_id = bo.booking_id ' .
                'JOIN cal_entries ce on be.booking_id = ce.context_id ' .
                'JOIN cal_cat_assignments cca on ce.cal_id = cca.cal_id ' .
                'JOIN cal_categories cc on cca.cat_id = cc.cat_id ' .
                'WHERE ' . $ilDB->in('be.obj_id', (array) $a_user_ids, false, 'integer') . ' ' .
                'AND ' . $ilDB->in('bo.target_obj_id', (array) $a_obj_id, false, 'integer') . ' ' .
                'AND cc.obj_id = be.obj_id ' .
                'AND cc.type = ' . $ilDB->quote(ilCalendarCategory::TYPE_CH, 'integer') . ' ';
        
        $res = $ilDB->query($query);
        
        $objs = array();
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            if (!in_array($row->bobj, $objs)) {
                $objs[] = $row->bobj;
            }
        }
        
        // non filtered booking entries
        $query = 'SELECT be.obj_id bobj FROM booking_entry be ' .
                'LEFT JOIN booking_obj_assignment bo ON be.booking_id = bo.booking_id ' .
                'JOIN cal_entries ce on be.booking_id = ce.context_id ' .
                'JOIN cal_cat_assignments cca on ce.cal_id = cca.cal_id ' .
                'JOIN cal_categories cc on cca.cat_id = cc.cat_id ' .
                'WHERE bo.booking_id IS NULL ' .
                'AND ' . $ilDB->in('be.obj_id', (array) $a_user_ids, false, 'integer') . ' ' .
                'AND cc.obj_id = be.obj_id ' .
                'AND cc.type = ' . $ilDB->quote(ilCalendarCategory::TYPE_CH, 'integer') . ' ';
        
        
        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            if (!in_array($row->bobj, $objs)) {
                $objs[] = $row->bobj;
            }
        }
                
        return $objs;
    }
    
    public static function hasObjectBookingEntries($a_obj_id, $a_usr_id)
    {
        global $DIC;

        $ilDB = $DIC->database();
        
        $user_restriction = '';
        if ($a_usr_id) {
            $user_restriction = 'AND obj_id = ' . $ilDB->quote($a_usr_id) . ' ';
        }
        
        
        $query = 'SELECT be.booking_id FROM booking_entry be ' .
                'JOIN booking_obj_assignment bo ON be.booking_id = bo.booking_id ' .
                'WHERE bo.target_obj_id = ' . $ilDB->quote($a_obj_id, 'integer') . ' ' .
                $user_restriction;
        
        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return true;
        }
        return false;
    }
    
    public static function lookupBookingMessage($a_entry_id, $a_usr_id)
    {
        global $DIC;

        $ilDB = $DIC->database();
        
        $query = 'SELECT * from booking_user ' .
                'WHERE entry_id = ' . $ilDB->quote($a_entry_id, 'integer') . ' ' .
                'AND user_id = ' . $ilDB->quote($a_usr_id, 'integer');
        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return $row->booking_message;
        }
        return '';
    }
    
    public static function writeBookingMessage($a_entry_id, $a_usr_id, $a_message)
    {
        global $DIC;

        $ilDB = $DIC->database();
        
        $query = 'UPDATE booking_user SET ' .
                'booking_message = ' . $ilDB->quote($a_message, 'text') . ' ' .
                'WHERE entry_id = ' . $ilDB->quote($a_entry_id, 'integer') . ' ' .
                'AND user_id = ' . $ilDB->quote($a_usr_id, 'integer');
        
        $GLOBALS['ilLog']->write(__METHOD__ . ': ' . $query);
        
        $ilDB->manipulate($query);
        return true;
    }

    public function getCurrentNumberOfBookings($a_entry_id)
    {
        $ilDB = $this->db;

        $set = $ilDB->query('SELECT COUNT(*) AS counter FROM booking_user' .
            ' WHERE entry_id = ' . $ilDB->quote($a_entry_id, 'integer'));
        $row = $ilDB->fetchAssoc($set);
        return (int) $row['counter'];
    }

    public function getCurrentBookings($a_entry_id)
    {
        $ilDB = $this->db;

        $set = $ilDB->query('SELECT user_id FROM booking_user' .
            ' WHERE entry_id = ' . $ilDB->quote($a_entry_id, 'integer'));
        $res = array();
        while ($row = $ilDB->fetchAssoc($set)) {
            $res[] = $row['user_id'];
        }
        return $res;
    }
    
    public static function lookupBookingsForAppointment($a_app_id)
    {
        global $DIC;

        $ilDB = $DIC->database();

        $query = 'SELECT user_id FROM booking_user ' .
                'WHERE entry_id = ' . $ilDB->quote($a_app_id, 'integer');
        $res = $ilDB->query($query);
        
        $users = array();
        while ($row = $ilDB->fetchObject($res)) {
            $users[] = $row->user_id;
        }
        return $users;
    }
    
    public static function lookupBookingsForObject($a_obj_id, $a_usr_id)
    {
        global $DIC;

        $ilDB = $DIC->database();
        

        $query = 'SELECT bu.user_id, starta, enda FROM booking_user bu ' .
                'JOIN cal_entries ca ON entry_id = ca.cal_id ' .
                'JOIN booking_entry be ON context_id = booking_id ' .
                'JOIN booking_obj_assignment bo ON be.booking_id = bo.booking_id ' .
                'WHERE bo.target_obj_id = ' . $ilDB->quote($a_obj_id, 'integer') . ' ' .
                'AND be.obj_id = ' . $ilDB->quote($a_usr_id) . ' ' .
                'ORDER BY starta';
        $res = $ilDB->query($query);
        
        $bookings = array();
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $dt = new ilDateTime($row->starta, IL_CAL_DATETIME, ilTimeZone::UTC);
            $dt_end = new ilDateTime($row->enda, IL_CAL_DATETIME, ilTimeZone::UTC);
            $bookings[$row->user_id][] = array(
                'dt' => $dt->get(IL_CAL_UNIX),
                'dtend' => $dt_end->get(IL_CAL_UNIX),
                'owner' => $a_usr_id);
        }
        return $bookings;
    }

    public static function lookupManagedBookingsForObject($a_obj_id, $a_usr_id)
    {
        $bookings = self::lookupBookingsForObject($a_obj_id, $a_usr_id);
        include_once './Services/Calendar/classes/ConsultationHours/class.ilConsultationHourUtils.php';
        foreach (ilConsultationHourUtils::lookupManagedUsers($a_usr_id) as $managed_user_id) {
            foreach (self::lookupBookingsForObject($a_obj_id, $managed_user_id) as $booked_user => $booking) {
                $fullname = ilObjUser::_lookupFullname($managed_user_id);
                foreach ($booking as $booking_entry) {
                    $booking_entry['explanation'] = '(' . $fullname . ')';
                    $bookings[$booked_user][] = $booking_entry;
                }
            }
        }
        return $bookings;
    }


    public function hasBooked($a_entry_id, $a_user_id = null)
    {
        $ilUser = $this->user;
        $ilDB = $this->db;

        if (!$a_user_id) {
            $a_user_id = $ilUser->getId();
        }

        $query = 'SELECT COUNT(*) AS counter FROM booking_user' .
            ' WHERE entry_id = ' . $ilDB->quote($a_entry_id, 'integer') .
            ' AND user_id = ' . $ilDB->quote($a_user_id, 'integer');
        $set = $ilDB->query($query);
        $row = $ilDB->fetchAssoc($set);
        
        return (bool) $row['counter'];
    }

    public function isBookedOut($a_entry_id, $a_check_current_user = false)
    {
        $ilUser = $this->user;

        if ($this->getNumberOfBookings() == $this->getCurrentNumberOfBookings($a_entry_id)) {
            // check against current user
            if ($a_check_current_user) {
                if ($this->hasBooked($a_entry_id)) {
                    return false;
                }
                if ($ilUser->getId() == $this->getObjId()) {
                    return false;
                }
            }
            return true;
        }

        $deadline = $this->getDeadlineHours();
        if ($deadline) {
            include_once 'Services/Calendar/classes/class.ilCalendarEntry.php';
            $entry = new ilCalendarEntry($a_entry_id);
            if (time() + ($deadline * 60 * 60) > $entry->getStart()->get(IL_CAL_UNIX)) {
                return true;
            }
        }
        return false;
    }
    
    public function isAppointmentBookableForUser($a_app_id, $a_user_id)
    {
        // #12025
        if ($a_user_id == ANONYMOUS_USER_ID) {
            return false;
        }
        
        // Check max bookings
        if ($this->getNumberOfBookings() <= $this->getCurrentNumberOfBookings($a_app_id)) {
            #$GLOBALS['ilLog']->write(__METHOD__.': Number of bookings exceeded');
            return false;
        }

        // Check deadline
        $dead_limit = new ilDateTime(time(), IL_CAL_UNIX);
        $dead_limit->increment(IL_CAL_HOUR, $this->getDeadlineHours());
        
        include_once 'Services/Calendar/classes/class.ilCalendarEntry.php';
        $entry = new ilCalendarEntry($a_app_id);
        if (ilDateTime::_after($dead_limit, $entry->getStart())) {
            #$GLOBALS['ilLog']->write(__METHOD__.': Deadline reached');
            return false;
        }
        
        // Check group restrictions
        if (!$this->getBookingGroup()) {
            #$GLOBALS['ilLog']->write(__METHOD__.': No booking group');
            return true;
        }
        include_once './Services/Calendar/classes/ConsultationHours/class.ilConsultationHourAppointments.php';
        $group_apps = ilConsultationHourAppointments::getAppointmentIdsByGroup(
            $this->getObjId(),
            $this->getBookingGroup()
        );
        
        // Number of bookings in group
        $bookings = self::lookupBookingsOfUser($group_apps, $a_user_id);
        
        include_once './Services/Calendar/classes/ConsultationHours/class.ilConsultationHourGroups.php';
        #$GLOBALS['ilLog']->write(__METHOD__.': '.ilConsultationHourGroups::lookupMaxBookings($this->getBookingGroup()));
        
        if (count($bookings) >= ilConsultationHourGroups::lookupMaxBookings($this->getBookingGroup())) {
            #$GLOBALS['ilLog']->write(__METHOD__.': Personal booking limit reached');
            return false;
        }
        #$GLOBALS['ilLog']->write(__METHOD__.': Is bookable!');
        return true;
    }

    public function book($a_entry_id, $a_user_id = false)
    {
        $ilUser = $this->user;
        $ilDB = $this->db;
        
        if (!$a_user_id) {
            $a_user_id = $ilUser->getId();
        }

        if (!$this->hasBooked($a_entry_id, $a_user_id)) {
            $ilDB->manipulate('INSERT INTO booking_user (entry_id, user_id, tstamp)' .
                ' VALUES (' . $ilDB->quote($a_entry_id, 'integer') . ',' .
                $ilDB->quote($a_user_id, 'integer') . ',' . $ilDB->quote(time(), 'integer') . ')');

            include_once 'Services/Calendar/classes/class.ilCalendarMailNotification.php';
            $mail = new ilCalendarMailNotification();
            $mail->setAppointmentId($a_entry_id);
            $mail->setRecipients(array($a_user_id));
            $mail->setType(ilCalendarMailNotification::TYPE_BOOKING_CONFIRMATION);
            $mail->send();
        }
        return true;
    }

    public function cancelBooking($a_entry_id, $a_user_id = false)
    {
        $ilUser = $this->user;
        $ilDB = $this->db;

        if (!$a_user_id) {
            $a_user_id = $ilUser->getId();
        }

        // @todo do not send mails about past consultation hours
        $entry = new ilCalendarEntry($a_entry_id);
        
        $past = ilDateTime::_before($entry->getStart(), new ilDateTime(time(), IL_CAL_UNIX));
        if ($this->hasBooked($a_entry_id, $a_user_id) && !$past) {
            include_once 'Services/Calendar/classes/class.ilCalendarMailNotification.php';
            $mail = new ilCalendarMailNotification();
            $mail->setAppointmentId($a_entry_id);
            $mail->setRecipients(array($a_user_id));
            $mail->setType(ilCalendarMailNotification::TYPE_BOOKING_CANCELLATION);
            $mail->send();
        }
        $this->deleteBooking($a_entry_id, $a_user_id);
        return true;
    }
    
    public function deleteBooking($a_entry_id, $a_user_id)
    {
        $ilDB = $this->db;
    
        $query = 'DELETE FROM booking_user ' .
            'WHERE entry_id = ' . $ilDB->quote($a_entry_id, 'integer') . ' ' .
            'AND user_id = ' . $ilDB->quote($a_user_id, 'integer');
        $ilDB->manipulate($query);
        return true;
    }
}