/php/lib/data_manager/mdb2.class.php

Large files files are truncated, but you can click here to view the full file

<?php

namespace application\weblcms;

use application\weblcms\tool\survey\SurveyInvitation;
use common\libraries\ArrayResultSet;
use common\libraries\SubselectCondition;
use common\libraries\ConditionTranslator;
use common\libraries\NotCondition;
use common\libraries\PlatformSetting;
use common\libraries\Session;
use common\libraries\OrCondition;
use common\libraries\InCondition;
use common\libraries\Utilities;
use common\libraries\ObjectTableOrder;
use common\libraries\AndCondition;
use common\libraries\InequalityCondition;
use common\libraries\EqualityCondition;
use common\libraries\Request;
use common\libraries\Mdb2Database;
use common\libraries\Translation;
use group\Group;
use group\GroupDataManager;
use group\GroupRelUser;
use user\User;
use user\UserDataManager;
use repository\ContentObject;
use repository\content_object\introduction\Introduction;
use repository\ContentObjectPublicationAttributes;
use repository\RepositoryDataManager;
use repository\ComplexContentObjectItem;
use repository\DatabaseRepositoryDataManager;
use common\libraries\Mdb2ResultSet;
use tracking\TrackingDataManager;
use rights\RightsLocation;
use application\weblcms\course\CourseUserRelation;
use application\weblcms\course\CourseGroupRelation;
use application\weblcms\course_type\CourseType;
use application\weblcms\course\Course;
use application\weblcms\course_type\CourseTypeRelCourseSetting;
use application\weblcms\course_type\CourseTypeRelCourseSettingValue;
use application\weblcms\course\CourseRelCourseSetting;
use application\weblcms\course\CourseRelCourseSettingValue;
use common\libraries\DataClass;
use common\libraries\DataClassCache;
use Doctrine\DBAL\Query\QueryBuilder;
use application\weblcms\ContentObjectPublication;
use application\weblcms\tool\assignment\AssignmentTool;
use common\libraries\DoctrineResultSet;

class Mdb2WeblcmsDataManager extends Mdb2Database implements WeblcmsDataManagerInterface
{
    private $course_group_cache;

    function initialize()
    {
        parent :: initialize();
        $this->set_prefix('weblcms_');
    }

    function retrieve_content_object_publication($publication_id)
    {
        $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_ID, $publication_id);
        return $this->retrieve_object(ContentObjectPublication :: get_table_name(), $condition, array(),
                ContentObjectPublication :: CLASS_NAME);
    }

    function retrieve_content_object_publication_feedback($publication_id)
    {
        $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_PARENT_ID, $publication_id);
        return $this->retrieve_objects(ContentObjectPublication :: get_table_name(), $condition, null, null, array(),
                ContentObjectPublication :: CLASS_NAME)->as_array();
    }

    public function content_object_is_published($object_id)
    {
        $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $object_id);
        return $this->count_objects(ContentObjectPublication :: get_table_name(), $condition) >= 1;
    }

    public function any_content_object_is_published($object_ids)
    {
        $condition = new InCondition(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $object_ids);
        return $this->count_objects(ContentObjectPublication :: get_table_name(), $condition) >= 1;
    }

    function get_content_object_publication_attributes($user, $object_id, $type = null, $offset = null, $count = null,
            $order_properties = null)
    {
        if (isset($type))
        {
            if ($type == 'user')
            {
                $rdm = RepositoryDataManager :: get_instance();
                $co_alias = $rdm->get_alias(ContentObject :: get_table_name());
                $pub_alias = $this->get_alias(ContentObjectPublication :: get_table_name());

                $query = 'SELECT ' . $pub_alias . '.*, ' . $co_alias . '.' . $this->escape_column_name(
                        ContentObject :: PROPERTY_TITLE) . ' FROM ' . $this->escape_table_name(
                        ContentObjectPublication :: get_table_name()) . ' AS ' . $pub_alias . ' JOIN ' . $rdm->escape_table_name(
                        ContentObject :: get_table_name()) . ' AS ' . $co_alias . ' ON ' . $this->escape_column_name(
                        ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $pub_alias) . '=' . $this->escape_column_name(
                        ContentObject :: PROPERTY_ID, $co_alias);

                $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_PUBLISHER_ID,
                        Session :: get_user_id());
                $translator = ConditionTranslator :: factory($this);
                $query .= $translator->render_query($condition);

                $order = array();
                foreach ($order_properties as $order_property)
                {
                    if ($order_property->get_property() == 'application')
                    {

                    }
                    elseif ($order_property->get_property() == 'location')
                    {

                    }
                    elseif ($order_property->get_property() == 'title')
                    {
                        $order[] = $this->escape_column_name('title') . ' ' . ($order_property->get_direction() == SORT_DESC ? 'DESC' : 'ASC');
                    }
                    else
                    {
                        $order[] = $this->escape_column_name($order_property->get_property()) . ' ' . ($order_property->get_direction() == SORT_DESC ? 'DESC' : 'ASC');
                    }
                }

                if (count($order) > 0)
                    $query .= ' ORDER BY ' . implode(', ', $order);
            }
        }
        else
        {
            $query = 'SELECT * FROM ' . $this->escape_table_name(ContentObjectPublication :: get_table_name());
            $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $object_id);
            $translator = ConditionTranslator :: factory($this);
            $query .= $translator->render_query($condition);
        }

        $this->set_limit($count, $offset);
        $res = $this->query($query);

        $publication_attr = array();
        while ($record = $res->fetchRow(MDB2_FETCHMODE_ASSOC))
        {
            $info = new ContentObjectPublicationAttributes();
            $info->set_id($record[ContentObjectPublication :: PROPERTY_ID]);
            $info->set_publisher_user_id($record[ContentObjectPublication :: PROPERTY_PUBLISHER_ID]);
            $info->set_publication_date($record[ContentObjectPublication :: PROPERTY_PUBLICATION_DATE]);
            $info->set_application('weblcms');
            // TODO: i8n location string
            $info->set_location(
                    $record[ContentObjectPublication :: PROPERTY_COURSE_ID] . ' &gt; ' . $record[ContentObjectPublication :: PROPERTY_TOOL]);
            // TODO: set correct URL
            $info->set_url(
                    'index.php?application=weblcms&amp;go=' . WeblcmsManager :: ACTION_VIEW_COURSE . '&course=' . $record[ContentObjectPublication :: PROPERTY_COURSE_ID] . '&amp;tool=' . $record[ContentObjectPublication :: PROPERTY_TOOL] . '&amp;tool_action=' . Tool :: ACTION_VIEW . '&amp;' . Tool :: PARAM_PUBLICATION_ID . '=' . $info->get_id());
            $info->set_publication_object_id($record[ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID]);

            $publication_attr[] = $info;
        }

        $res->free();

        return $publication_attr;
    }

    function get_content_object_publication_attribute($publication_id)
    {
        $query = 'SELECT * FROM ' . $this->escape_table_name('content_object_publication') . ' WHERE ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_ID) . '=' . $this->quote($publication_id);
        $this->get_connection()->setLimit(0, 1);
        $res = $this->query($query);
        $publication_attr = array();
        $record = $res->fetchRow(MDB2_FETCHMODE_ASSOC);

        $res->free();

        $publication_attr = new ContentObjectPublicationAttributes();
        $publication_attr->set_id($record[ContentObjectPublication :: PROPERTY_ID]);
        $publication_attr->set_publisher_user_id($record[ContentObjectPublication :: PROPERTY_PUBLISHER_ID]);
        $publication_attr->set_publication_date($record[ContentObjectPublication :: PROPERTY_PUBLICATION_DATE]);
        $publication_attr->set_application('weblcms');
        // TODO: i8n location string
        $publication_attr->set_location(
                $record[ContentObjectPublication :: PROPERTY_COURSE_ID] . ' &gt; ' . $record[ContentObjectPublication :: PROPERTY_TOOL]);
        // TODO: set correct URL
        $publication_attr->set_url(
                'index_weblcms.php?tool=' . $record[ContentObjectPublication :: PROPERTY_TOOL] . '&amp;cidReq=' . $record[ContentObjectPublication :: PROPERTY_COURSE_ID]);
        $publication_attr->set_publication_object_id($record[ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID]);

        return $publication_attr;
    }

    function count_publication_attributes($user = null, $object_id = null, $condition = null)
    {
        if (! $object_id)
        {
            $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_PUBLISHER_ID, $user->get_id());
        }
        else
        {
            $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $object_id);
        }
        return $this->count_objects(ContentObjectPublication :: get_table_name(), $condition);
    }

    function retrieve_content_object_publications($condition = null, $order_by = array(), $offset = 0, $max_objects = -1)
    {

        $publication_table_alias = $this->get_alias(ContentObjectPublication :: get_table_name());

        $repo_data_manager = RepositoryDataManager :: get_instance();
        $content_object_table_alias = $repo_data_manager->get_alias(ContentObject :: get_table_name());

        $query = 'SELECT ' . $publication_table_alias . '.*, ' . $content_object_table_alias . '.' . ContentObject :: PROPERTY_TITLE . ',' . $content_object_table_alias . '.' . ContentObject :: PROPERTY_DESCRIPTION . ',' . $content_object_table_alias . '.' . ContentObject :: PROPERTY_TYPE . ', ' . $content_object_table_alias . '.' . ContentObject :: PROPERTY_OWNER_ID;
        $query .= ' FROM ' . $this->escape_table_name(ContentObjectPublication :: get_table_name()) . ' AS ' . $publication_table_alias;
        $query .= ' JOIN ' . $repo_data_manager->escape_table_name(ContentObject :: get_table_name()) . ' AS ' . $content_object_table_alias . ' ON ' . $repo_data_manager->escape_column_name(
                ContentObject :: PROPERTY_ID, $content_object_table_alias) . '=';

        $query .= $this->escape_column_name(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID,
                $publication_table_alias);

        return $this->retrieve_object_set($query, ContentObjectPublication :: get_table_name(), $condition, $offset,
                $max_objects, $order_by, ContentObjectPublication :: CLASS_NAME);

    }

    function count_content_object_publications($condition)
    {
        $publication_table_alias = $this->get_alias(ContentObjectPublication :: get_table_name());

        $repo_data_manager = RepositoryDataManager :: get_instance();
        $content_object_table_alias = $repo_data_manager->get_alias(ContentObject :: get_table_name());

        $query = 'SELECT COUNT(*)';
        $query .= ' FROM ' . $this->escape_table_name(ContentObjectPublication :: get_table_name()) . ' AS ' . $publication_table_alias;
        $query .= ' JOIN ' . $repo_data_manager->escape_table_name(ContentObject :: get_table_name()) . ' AS ' . $content_object_table_alias . ' ON ' . $repo_data_manager->escape_column_name(
                ContentObject :: PROPERTY_ID, $content_object_table_alias) . '=';

        $query .= $this->escape_column_name(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID,
                $publication_table_alias);

        return $this->count_result_set($query, ContentObjectPublication :: get_table_name(), $condition);
    }

    function subscribe_user_to_allowed_courses($user_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CommonRequest :: PROPERTY_USER_ID, $user_id);
        $conditions[] = new InequalityCondition(CommonRequest :: PROPERTY_DECISION_DATE,
                InequalityCondition :: LESS_THAN_OR_EQUAL, time());
        $conditions[] = new EqualityCondition(CommonRequest :: PROPERTY_DECISION, CommonRequest :: ALLOWED_DECISION);
        $condition = new AndCondition($conditions);

        $course_subscribe_requests = $this->retrieve_requests($condition);

        while ($course_request = $course_subscribe_requests->next_result())
        {
            $course = $this->retrieve_course($course_request->get_course_id());
            $user = UserDataManager :: get_instance()->retrieve_user($user_id);
            if (! $this->is_subscribed($course, $user))
            {
                $this->subscribe_user_to_course($course, '5', '0', $user_id);
            }
            $course_request->delete();
        }
    }

    function count_requests($condition = null)
    {
        return $this->count_objects(CourseRequest :: get_table_name(), $condition);
    }

    function count_course_create_requests($condition = null)
    {
        return $this->count_objects(CourseCreateRequest :: get_table_name(), $condition);
    }

    function count_course_categories($condition = null)
    {
        return $this->count_objects(CourseCategory :: get_table_name(), $condition);
    }

    function count_user_courses($condition = null)
    {
        $course_alias = $this->get_alias(Course :: get_table_name());
        $course_user_relation_alias = $this->get_alias(CourseUserRelation :: get_table_name());
        $course_group_relation_alias = $this->get_alias(CourseGroupRelation :: get_table_name());

        $query = 'SELECT COUNT(*) FROM ' . $this->escape_table_name(Course :: get_table_name()) . ' AS ' . $course_alias;
        $query .= ' LEFT JOIN ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' AS ' . $course_user_relation_alias . ' ON ' . $this->escape_column_name(
                Course :: PROPERTY_ID, $course_alias) . ' = ' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_COURSE_ID, $course_user_relation_alias);
        $query .= ' LEFT JOIN ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' AS ' . $course_group_relation_alias . ' ON ' . $this->escape_column_name(
                Course :: PROPERTY_ID, $course_alias) . ' = ' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_COURSE_ID, $course_group_relation_alias);

        return $this->count_result_set($query, Course :: get_table_name(), $condition);
    }

    function retrieve_user_courses($condition = null, $offset = 0, $max_objects = -1, $order_by = null)
    {
        $course_alias = $this->get_alias(Course :: get_table_name());
        $course_user_relation_alias = $this->get_alias(CourseUserRelation :: get_table_name());
        $course_group_relation_alias = $this->get_alias(CourseGroupRelation :: get_table_name());

        $query = 'SELECT DISTINCT ' . $course_alias . '.* FROM ' . $this->escape_table_name(Course :: get_table_name()) . ' AS ' . $course_alias;
        $query .= ' LEFT JOIN ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' AS ' . $course_user_relation_alias . ' ON ' . $this->escape_column_name(
                Course :: PROPERTY_ID, $course_alias) . ' = ' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_COURSE_ID, $course_user_relation_alias);
        $query .= ' LEFT JOIN ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' AS ' . $course_group_relation_alias . ' ON ' . $this->escape_column_name(
                Course :: PROPERTY_ID, $course_alias) . ' = ' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_COURSE_ID, $course_group_relation_alias);

        if (is_null($order_by))
        {
            $order_by[] = new ObjectTableOrder(Course :: PROPERTY_TITLE);
        }

        return $this->retrieve_object_set($query, Course :: get_table_name(), $condition, $offset, $max_objects,
                $order_by, Course :: class_name());
    }

    function count_course_user_categories($condition = null)
    {
        return $this->count_objects(CourseUserCategory :: get_table_name(), $condition);
    }

    function count_course_type_user_categories($condition = null)
    {
        return $this->count_objects(CourseTypeUserCategory :: get_table_name(), $condition);
    }

    function retrieve_course_list_of_user_as_course_admin($user_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $user_id);
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_STATUS, 1);
        $condition = new AndCondition($conditions);

        return $this->retrieve_course_user_relations($condition);
    }

    function count_distinct_course_user_relations()
    {
        return $this->count_distinct(CourseUserRelation :: get_table_name(), CourseUserRelation :: PROPERTY_USER_ID);
    }

    function count_course_user_relations($condition = null)
    {
        return $this->count_objects(CourseUserRelation :: get_table_name(), $condition);
    }

    function count_course_group_relations($condition = null)
    {
        return $this->count_objects(CourseGroupRelation :: get_table_name(), $condition);
    }

    function retrieve_course_group_group_relation($id)
    {
        $condition = new EqualityCondition(CourseGroupGroupRelation :: PROPERTY_ID, $id);
        return $this->retrieve_object(CourseGroupGroupRelation :: get_table_name(), $condition, array(),
                CourseGroupGroupRelation :: CLASS_NAME);
    }

    function retrieve_course_group_group_relation_custom($condition)
    {
        return $this->retrieve_object(CourseGroupGroupRelation :: get_table_name(), $condition, array(),
                CourseGroupGroupRelation :: CLASS_NAME);
    }

    function retrieve_course_group_group_relation_by_name($name)
    {
        $condition = new EqualityCondition(CourseGroupGroupRelation :: PROPERTY_NAME, $name);
        return $this->retrieve_object(CourseGroupGroupRelation :: get_table_name(), $condition, array(),
                CourseGroupGroupRelation :: CLASS_NAME);
    }
    
    function retrieve_course_group_group_relations($condition = null, $offset = null, $count = null, $order_property = null)
    {
        return $this->retrieve_objects(CourseGroupGroupRelation::get_table_name(), $condition, $offset, $count,
            $order_property, CourseGroupGroupRelation::CLASS_NAME);
    }
    
    function update_course_group_group_relation($course_group_group_relation)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroupGroupRelation :: PROPERTY_ID,
                $course_group_group_relation->get_id());
        $condition = new AndCondition($conditions);

        return $this->update($course_group_group_relation, $condition);
    }
    
    function delete_course_group_group_relation($course_group_group_relation)
    {
        $condition = new EqualityCondition(CourseGroup::PROPERTY_GROUP_ID, $course_group_group_relation->get_id());
        $course_groups = $this->retrieve_course_groups($condition);
        $success = true;
        while($course_group = $course_groups->next_result())
        {
            if(!$this->delete_course_group($course_group))
            {
                $success = false;
            }
        }
        if(!$success)
        {
            return false;
        }
        
        $condition = new EqualityCondition(CourseGroupGroupRelation :: PROPERTY_ID, $course_group_group_relation->get_id());
        $succes = $this->delete(CourseGroupGroupRelation :: get_table_name(), $condition);

        return $succes;
    }

    function retrieve_course_groups_by_group_id($condition = null, $offset = null, $count = null, $order_property = null)
    {
        return $this->retrieve_objects(CourseGroup :: get_table_name(), $condition, $offset, $count, $order_property,
                CourseGroup :: CLASS_NAME);
    }

    function retrieve_course_groups_by_course_group_group_relation_id($course_id, $course_group_group_relation_id)
    {

        $conditions[] = new EqualityCondition(CourseGroup :: PROPERTY_GROUP_ID, $course_group_group_relation_id);
        $conditions[] = new EqualityCondition(CourseGroup :: PROPERTY_COURSE_CODE, $course_id);
        $condition = new AndCondition($conditions);
        return $this->retrieve_objects(CourseGroup :: get_table_name(), $condition, null, null, null,
                CourseGroup :: CLASS_NAME);
    }

    function update_content_object_publication_id($publication_attr)
    {
        $where = $this->escape_column_name(ContentObjectPublication :: PROPERTY_ID) . '=' . $publication_attr->get_id();
        $props = array();
        $props[$this->escape_column_name(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID)] = $publication_attr->get_publication_object_id();
        $this->get_connection()->loadModule('Extended');
        if ($this->get_connection()->extended->autoExecute($this->get_table_name('content_object_publication'), $props,
                MDB2_AUTOQUERY_UPDATE, $where))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    function delete_content_object_publication($publication)
    {
        if (is_numeric($publication))
        {
            $publication = $this->retrieve_content_object_publication($publication);
        }

        $publication_id = $publication->get_id();

        $query = 'DELETE FROM ' . $this->escape_table_name('content_object_publication_user') . ' WHERE publication_id = ' . $this->quote(
                $publication_id);
        $res = $this->exec($query);

        $query = 'DELETE FROM ' . $this->escape_table_name('content_object_publication_course_group') . ' WHERE publication_id = ' . $this->quote(
                $publication_id);
        $res = $this->exec($query);

        $query = 'UPDATE ' . $this->escape_table_name('content_object_publication') . ' SET ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX) . '=' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX) . '-1 WHERE ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX) . '>' . $this->quote(
                $publication->get_display_order_index()) . ' AND ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_COURSE_ID) . '=' . $publication->get_course_id() . ' AND ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_TOOL) . '=\'' . $publication->get_tool() . '\' AND ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_CATEGORY_ID) . '=' . $publication->get_category_id();

        $res = $this->exec($query);

        $query = 'DELETE FROM ' . $this->escape_table_name('content_object_publication') . ' WHERE ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_ID) . '=' . $this->quote($publication_id);
        $this->get_connection()->setLimit(0, 1);
        $res = $this->exec($query);

        return true;
    }

    function delete_content_object_publications($object_id)
    {
        $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $object_id);
        $publications = $this->retrieve_content_object_publications($condition);

        while ($publication = $publications->next_result())
        {
            $publication->delete();
        }
        return true;
    }

    function retrieve_content_object_publication_category($id)
    {
        $condition = new EqualityCondition(ContentObjectPublicationCategory :: PROPERTY_ID, $id);
        return $this->retrieve_object(ContentObjectPublicationCategory :: get_table_name(), $condition, array(),
                ContentObjectPublicationCategory :: CLASS_NAME);
    }

    function move_content_object_publication($publication, $places)
    {
        if ($places < 0)
        {
            return $this->move_content_object_publication_up($publication, - $places);
        }
        else
        {
            return $this->move_content_object_publication_down($publication, $places);
        }
    }

    function retrieve_course_module_access($condition = null, $order_by = array())
    {
        return $this->retrieve_object(CourseModuleLastAccess :: get_table_name(), $condition, $order_by,
                CourseModuleLastAccess :: CLASS_NAME);
    }

    function retrieve_course_module_accesses($condition = null, $offset = null, $max_objects = null, $order_by = null)
    {
        return $this->retrieve_objects(CourseModuleLastAccess :: get_table_name(), $condition, $offset, $max_objects,
                $order_by, CourseModuleLastAccess :: CLASS_NAME);
    }

    function log_course_module_access($course_code, $user_id, $module_name = null, $category_id = 0)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_COURSE_CODE, $course_code);
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_USER_ID, $user_id);
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_MODULE_NAME, $module_name);
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_CATEGORY_ID, $category_id);
        $condition = new AndCondition($conditions);

        $course_module_last_access = $this->retrieve_course_module_access($condition);

        if (! $course_module_last_access)
        {
            $course_module_last_access = new CourseModuleLastAccess();
            $course_module_last_access->set_course_code($course_code);
            $course_module_last_access->set_user_id($user_id);
            $course_module_last_access->set_module_name($module_name);
            $course_module_last_access->set_category_id($category_id);
            $course_module_last_access->set_access_date(time());
            return $course_module_last_access->create();
        }
        else
        {
            $course_module_last_access->set_access_date(time());
            return $course_module_last_access->update();
        }
    }

    /**
     * Creates a course module last acces in the database
     *
     * @param $coursemodule_last_accces CourseModuleLastAccess
     */
    function update_course_module_last_access($coursemodule_last_accces)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_COURSE_CODE,
                $coursemodule_last_accces->get_course_code());
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_MODULE_NAME,
                $coursemodule_last_accces->get_module_name());
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_USER_ID,
                $coursemodule_last_accces->get_user_id());
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_CATEGORY_ID,
                $coursemodule_last_accces->get_category_id());
        $condition = new AndCondition($conditions);

        $this->update($coursemodule_last_accces, $condition);
    }

    /**
     * Returns the last visit date per course and module
     *
     * @param $course_code <type>
     * @param $module_name <type>
     * @return <type>
     */
    function get_last_visit_date_per_course($course_code, $module_name = null, $user_id = null)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_COURSE_CODE, $course_code);
        if (! is_null($module_name))
        {
            $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_MODULE_NAME, $module_name);
        }

        if (! is_null($user_id))
        {
            $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_USER_ID, $user_id);
        }

        $condition = new AndCondition($conditions);

        $order_by = new ObjectTableOrder(CourseModuleLastAccess :: PROPERTY_ACCESS_DATE, SORT_DESC);

        $course_module_access = $this->retrieve_course_module_access($condition, $order_by);

        if (! $course_module_access)
        {
            return 0;
        }
        else
        {
            return $course_module_access->get_access_date();
        }
    }

    function get_last_visit_date($course_code, $user_id, $module_name = null, $category_id = 0)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_COURSE_CODE, $course_code);
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_USER_ID, $user_id);
        $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_CATEGORY_ID, $category_id);
        if (! is_null($module_name))
        {
            $conditions[] = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_MODULE_NAME, $module_name);
        }
        $condition = new AndCondition($conditions);

        $order_by = new ObjectTableOrder(CourseModuleLastAccess :: PROPERTY_ACCESS_DATE, SORT_DESC);

        $course_module_access = $this->retrieve_course_module_access($condition, $order_by);

        if (! $course_module_access)
        {
            return 0;
        }
        else
        {
            return $course_module_access->get_access_date();
        }
    }

    function get_course_modules($course_code, $order_by_name = false)
    {
        $query = 'SELECT * FROM ' . $this->escape_table_name('course_module') . ' WHERE course_id = ' . $this->quote(
                $course_code);

        if ($order_by_name)
        {
            $query .= ' ORDER BY ' . CourseModule :: PROPERTY_NAME;
        }
        else
        {
            $query .= ' ORDER BY ' . CourseModule :: PROPERTY_SORT;
        }

        $res = $this->query($query);
        $modules = array();
        $module = null;
        while ($module = $res->fetchRow(MDB2_FETCHMODE_OBJECT))
        {
            $modules[$module->name] = $module;
        }

        $res->free();

        return $modules;
    }

    // DEPRECATED
    function get_all_course_modules()
    {
        return $this->retrieve_distinct(CourseModule :: get_table_name(), CourseModule :: PROPERTY_NAME);
    }

    function retrieve_course_module($id)
    {
        $condition = new EqualityCondition(CourseModule :: PROPERTY_ID, $id);
        return $this->retrieve_object(CourseModule :: get_table_name(), $condition, array(), CourseModule :: CLASS_NAME);
    }

    /**
     * Retrieves a single course tool by id
     *
     * @param $tool_id type
     * @return type
     */
    function retrieve_course_tool($tool_id)
    {
        $condition = new EqualityCondition(CourseTool :: PROPERTY_ID, $tool_id);
        return $this->retrieve_objects(CourseTool :: get_table_name(), $condition, $offset, $max_objects, $order_by);
    }

    /**
     * Returns the course tools
     *
     * @param $condition Condition
     * @param $offset int
     * @param $max_objects int
     * @param $order_by int
     *
     * @return ResultSet <CourseTool>
     */

    function retrieve_course_tools($condition = null, $offset = null, $max_objects = null, $order_by = null)
    {
        return $this->retrieve_objects(CourseTool :: get_table_name(), $condition, $offset, $max_objects, $order_by);
    }

    function retrieve_course_module_by_name($course_id, $course_module)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_COURSE_CODE, $course_id);
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_NAME, $course_module);
        $condition = new AndCondition($conditions);
        return $this->retrieve_object(CourseModule :: get_table_name(), $condition, array(), CourseModule :: CLASS_NAME);
    }

    // CTODO - move this to course datamanager
    function retrieve_course_user_relation($course_code, $user_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_code);
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $user_id);
        $condition = new AndCondition($conditions);

        return $this->retrieve_object(CourseUserRelation :: get_table_name(), $condition, array(),
                'application\weblcms\course\CourseUserRelation');
    }

    // CTODO - move this to course datamanager
    function retrieve_course_user_relations($condition = null, $offset = null, $count = null, $order_property = null)
    {
        return $this->retrieve_objects(CourseUserRelation :: get_table_name(), $condition, $offset, $count,
                $order_property, 'application\weblcms\course\CourseUserRelation');
    }

    // CTODO - move this to course datamanager
    function retrieve_course_group_relations($condition = null, $offset = null, $count = null, $order_property = null)
    {
        return $this->retrieve_objects(CourseGroupRelation :: get_table_name(), $condition, $offset, $count,
                $order_property, 'application\weblcms\course\CourseGroupRelation');
    }

    function retrieve_course_type_user_category_at_sort($user_id, $course_type_id, $sort, $direction)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_USER_ID, $user_id);
        $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_COURSE_TYPE_ID, $course_type_id);

        if ($direction == 'up')
        {
            $conditions[] = new InequalityCondition(CourseTypeUserCategory :: PROPERTY_SORT,
                    InequalityCondition :: LESS_THAN, $sort);
            $order_direction = SORT_DESC;
        }
        elseif ($direction == 'down')
        {
            $conditions[] = new InequalityCondition(CourseTypeUserCategory :: PROPERTY_SORT,
                    InequalityCondition :: GREATER_THAN, $sort);
            $order_direction = SORT_ASC;
        }

        $condition = new AndCondition($conditions);

        return $this->retrieve_object(CourseTypeUserCategory :: get_table_name(), $condition,
                array(new ObjectTableOrder(CourseTypeUserCategory :: PROPERTY_SORT, $order_direction)),
                CourseTypeUserCategory :: CLASS_NAME);
    }

    function create_course_modules($course_modules, $course_id)
    {
        $condition = new EqualityCondition(CourseSection :: PROPERTY_COURSE_ID, $course_id);
        $sections_set = $this->retrieve_course_sections($condition);
        $sections = array();
        while ($section = $sections_set->next_result())
        {
            $sections[$section->get_type()][] = $section;
        }

        foreach ($course_modules as $module)
        {
            $section_id = $sections[CourseSection :: TYPE_TOOL][0]->get_id();
            $module->set_section($section_id);
            if (! $module->create())
                return false;
        }

        $admin_tools = WeblcmsDataManager :: get_tools('course_admin');
        foreach ($admin_tools as $index => $tool_name)
        {
            $section_id = $sections[CourseSection :: TYPE_ADMIN][0]->get_id();
            $module = new CourseModule();
            $module->set_course_code($course_code);
            $module->set_name($tool_name);
            $module->set_visible(1);
            $module->set_section($section_id);
            $module->set_sort($index);
            if (! $module->create())
                return false;
        }

        return true;
    }

    private $is_subscribed_cache;

    function is_subscribed($course, $user)
    {
        $course_id = $course;
        if ($course instanceof Course)
        {
            $course_id = $course->get_id();
        }

        if(!$user instanceof \user\User)
        {
            $user = \user\DataManager :: retrieve_user($user);
        }

        if (is_null($this->is_subscribed_cache[$course_id][$user->get_id()]))
        {

            $conditions = array();
            $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $user->get_id());
            $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_id);
            $condition = new AndCondition($conditions);

            $has_user_relations = $this->count_objects(CourseUserRelation :: get_table_name(), $condition) > 0;

            $groups = $user->get_groups(true);
            if ($groups)
            {
                $conditions = array();
                $conditions[] = new InCondition(CourseGroupRelation :: PROPERTY_GROUP_ID, $user->get_groups(true));
                $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_id);
                $condition = new AndCondition($conditions);
                $has_group_relations = $this->count_objects(CourseGroupRelation :: get_table_name(), $condition) > 0;
            }
            else
                $has_group_relations = false;
            $this->is_subscribed_cache[$course_id][$user->get_id()] = $has_user_relations || $has_group_relations;
        }
        return $this->is_subscribed_cache[$course_id][$user->get_id()];
    }

    function is_group_subscribed($course_id, $group_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroupRelation :: PROPERTY_GROUP_ID, $group_id);
        $conditions[] = new EqualityCondition(CourseGroupRelation :: PROPERTY_COURSE_ID, $course_id);
        $condition = new AndCondition($conditions);
        return $this->count_objects(CourseGroupRelation :: get_table_name(), $condition) > 0;
    }

    function is_course_category($category)
    {
        $condition = new EqualityCondition(CourseCategory :: PROPERTY_ID, $category);
        return $this->count_objects(CourseCategory :: get_table_name(), $condition) > 0;
    }

    function is_course($course_code)
    {
        $condition = new EqualityCondition(Course :: PROPERTY_ID, $course_code);
        return $this->count_objects(Course :: get_table_name(), $condition) > 0;
    }

    function is_course_admin($course, $user_id)
    {
        $course_id = $course;
        if ($course instanceof Course)
        {
            $course_id = $course->get_id();
        }

        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_id);
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $user_id);
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_STATUS, 1);
        $condition = new AndCondition($conditions);
        return $this->count_objects(CourseUserRelation :: get_table_name(), $condition) > 0;
    }

    function retrieve_next_course_user_relation_sort_value(CourseUserRelation $course_user_relation)
    {
        $course = $this->retrieve_course($course_user_relation->get_course());
        $subcondition = new EqualityCondition(Course :: PROPERTY_COURSE_TYPE_ID, $course->get_course_type_id());
        $conditions[] = new SubselectCondition(CourseUserRelation :: PROPERTY_COURSE_ID, Course :: PROPERTY_ID,
                Course :: get_table_name(), $subcondition);
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $course_user_relation->get_user());
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_CATEGORY,
                $course_user_relation->get_category());
        $condition = new AndCondition($conditions);

        return $this->retrieve_max_sort_value(CourseUserRelation :: get_table_name(),
                CourseUserRelation :: PROPERTY_SORT, $condition) + 1;
    }

    function subscribe_user_to_course($course, $status, $tutor_id, $user_id)
    {
        $course_id = $course;
        if ($course instanceof Course)
        {
            $course_id = $course->get_id();
        }

        $course_user_relation = new CourseUserRelation();
        $course_user_relation->set_course_id($course_id);
        $course_user_relation->set_user_id($user_id);
        $course_user_relation->set_status($status);

        if ($course_user_relation->create())
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    function subscribe_group_to_course(Course $course, $group_id, $status)
    {
        $this->get_connection()->loadModule('Extended');

        $course_group_relation = new CourseGroupRelation();
        $course_group_relation->set_course_id($course->get_id());
        $course_group_relation->set_group_id($group_id);
        $course_group_relation->set_status($status);

        if ($course_group_relation->create())
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    function create_course_user_relation($courseuserrelation)
    {
        $props = array();
        foreach ($courseuserrelation->get_default_properties() as $key => $value)
        {
            $props[$this->escape_column_name($key)] = $value;
        }

        $this->get_connection()->loadModule('Extended');
        if ($this->get_connection()->extended->autoExecute(
                $this->get_table_name(CourseUserRelation :: get_table_name()), $props, MDB2_AUTOQUERY_INSERT))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    function unsubscribe_user_from_course($course, $user_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course->get_id());
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $user_id);
        $condition = new AndCondition($conditions);

        return $this->delete_objects(CourseUserRelation :: get_table_name(), $condition);
    }

    function unsubscribe_group_from_course(Course $course, $group_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroupRelation :: PROPERTY_COURSE_ID, $course->get_id());
        $conditions[] = new EqualityCondition(CourseGroupRelation :: PROPERTY_GROUP_ID, $group_id);
        $condition = new AndCondition($conditions);

        return $this->delete_objects(CourseGroupRelation :: get_table_name(), $condition);
    }

    function delete_course_type_user_category($course_type_user_category)
    {
        $condition = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_ID, $course_type_user_category->get_id());

        if ($this->delete_objects(CourseTypeUserCategory :: get_table_name(), $condition))
        {
            $condition = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_COURSE_USER_CATEGORY_ID,
                    $course_type_user_category->get_course_user_category_id());
            $count = $this->count_course_type_user_categories($condition);
            if ($count == 0)
            {
                $condition = new EqualityCondition(CourseUserCategory :: PROPERTY_ID,
                        $course_type_user_category->get_course_user_category_id());
                $course_user_category = $this->retrieve_course_user_category($condition);
                if (! $course_user_category->delete())
                {
                    return false;
                }
            }

            $conditions = array();
            $conditions[] = new EqualityCondition(
                    CourseTypeUserCategoryRelCourse :: PROPERTY_COURSE_TYPE_USER_CATEGORY_ID,
                    $course_type_user_category->get_id());
            $condition = new AndCondition($conditions);
            $course_type_user_category_rel_courses = $this->retrieve_course_type_user_category_rel_courses($condition);

            while ($course_type_user_category_rel_course = $course_type_user_category_rel_courses->next_result())
            {
                if (! $course_type_user_category_rel_course->delete())
                {
                    return false;
                }
            }

            $conditions = array();
            $conditions[] = new InEqualityCondition(CourseTypeUserCategory :: PROPERTY_SORT,
                    InEqualityCondition :: GREATER_THAN, $course_type_user_category->get_sort());
            $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_COURSE_TYPE_ID,
                    $course_type_user_category->get_course_type_id());
            $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_USER_ID,
                    $course_type_user_category->get_user_id());
            $condition = new AndCondition($conditions);

            $properties = array();
            $properties[CourseTypeUserCategory :: PROPERTY_SORT] = $this->escape_column_name(
                    CourseTypeUserCategory :: PROPERTY_SORT) . '-1';

            return $this->update_objects(CourseTypeUserCategory :: get_table_name(), $properties, $condition);
        }
        else
        {
            return false;
        }
    }

    function delete_course_user($courseuser)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $courseuser->get_course());
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $courseuser->get_user());
        $condition = new AndCondition($conditions);

        return $this->delete_objects(CourseUserRelation :: get_table_name(), $condition);
    }

    function delete_course_category($course_category)
    {
        $condition = new EqualityCondition(CourseCategory :: PROPERTY_ID, $course_category->get_id());
        $success = $this->delete_objects(CourseCategory :: get_table_name(), $condition);

        if ($success)
        {
            $condition = new EqualityCondition(CourseCategory :: PROPERTY_PARENT, $course_category->get_id());
            $properties = array(CourseCategory :: PROPERTY_PARENT => $course_category->get_parent());
            $success = $this->update_objects(CourseCategory :: get_table_name(), $properties, $condition);

            if ($success)
            {
                $condition = new EqualityCondition(Course :: PROPERTY_CATEGORY_ID, $course_category->get_id());
                $properties = array(Course :: PROPERTY_CATEGORY_ID => $course_category->get_parent());
                return $this->update_objects(Course :: get_table_name(), $properties, $condition);
            }
            else
            {
                return false;
            }
        }
        else
        {
            return false;
        }
    }

    function update_courses($properties, $condition)
    {
        return $this->update_objects(Course :: get_table_name(), $properties, $condition);
    }

    function update_course_module($course_module)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_COURSE_CODE, $course_module->get_course_code());
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_NAME, $course_module->get_name());
        $condition = new AndCondition($conditions);
        return $this->update($course_module, $condition);
    }

    /**
     * Updates the visibility of the course modules
     *
     * @param $condition Condition define the to be updated modules and course
     * @param $visibility bool visibility
     */
    function update_course_module_visibility($condition, $visibility)
    {
        // $and_condition = new AndConditon
        $properties = array(CourseModule :: PROPERTY_VISIBLE => $visibility);
        $this->update_objects(CourseModule :: get_table_name(), $properties, $condition);
    }

    function update_course_type_user_category($course_type_user_category)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_COURSE_TYPE_ID,
                $course_type_user_category->get_course_type_id());
        $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_COURSE_USER_CATEGORY_ID,
                $course_type_user_category->get_course_user_category_id());
        $condition = new AndCondition($conditions);
        return $this->update($course_type_user_category, $condition);
    }

    function update_course_user_relation($course_user_relation)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID,
                $course_user_relation->get_course_id());
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID,
                $course_user_relation->get_user_id());
        $condition = new AndCondition($conditions);

        return $this->update($course_user_relation, $condition);
    }

    function delete_courses_by_course_type_id($course_type_id)
    {
        $condition = new EqualityCondition(Course :: PROPERTY_COURSE_TYPE_ID, $course_type_id);
        $resultset = $this->retrieve_courses($condition);
        while ($result = $resultset->next_result())
        {
            if (! $this->delete_course($result->get_id()))
                return false;
        }
        return true;
    }

    function delete_course($course_code)…