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

<?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)
    {
        // Delete publication categories
        $condition = new EqualityCondition(ContentObjectPublicationCategory :: PROPERTY_COURSE, $course_code);
        if (! $this->delete_objects(ContentObjectPublicationCategory :: get_table_name(), $condition))
        {
            return false;
        }

        // Delete publications
        $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_COURSE_ID, $course_code);
        if (! $this->delete_objects(ContentObjectPublication :: get_table_name(), $condition))
        {
            return false;
        }

        // Delete course sections
        $condition = new EqualityCondition(CourseSection :: PROPERTY_COURSE_ID, $course_code);
        if (! $this->delete_objects(CourseSection :: get_table_name(), $condition))
        {
            return false;
        }

        // Delete modules
        $condition = new EqualityCondition(CourseModule :: PROPERTY_COURSE_CODE, $course_code);
        if (! $this->delete_objects(CourseModule :: get_table_name(), $condition))
        {
            return false;
        }

        // Delete module last access
        $condition = new EqualityCondition(CourseModuleLastAccess :: PROPERTY_COURSE_CODE, $course_code);
        if (! $this->delete_objects(CourseModuleLastAccess :: get_table_name(), $condition))
        {
            return false;
        }

        // Delete subscriptions of classes in the course
        $condition = new EqualityCondition(CourseGroupRelation :: PROPERTY_COURSE_ID, $course_code);
        if (! $this->delete_objects(CourseGroupRelation :: get_table_name(), $condition))
        {
            return false;
        }

        // Delete subscriptions of users in the course
        $condition = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_code);
        if (! $this->delete_objects(CourseUserRelation :: get_table_name(), $condition))
        {
            return false;
        }

        $condition = new EqualityCondition(CourseRequest :: PROPERTY_COURSE_ID, $course_code);
        if (! $this->delete_objects(CourseRequest :: get_table_name(), $condition))
        {
            return false;
        }

        // Delete course
        $condition = new EqualityCondition(Course :: PROPERTY_ID, $course_code);
        if (! $this->delete_objects(Course :: get_table_name(), $condition))
        {
            return false;
        }

        return true;
    }

    function delete_course_module($course_code, $course_name)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_COURSE_CODE, $course_code);
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_NAME, $course_name);
        $condition = new AndCondition($conditions);
        return $this->delete_objects(CourseModule :: get_table_name(), $condition);
    }

    function retrieve_course_category($category)
    {
        $condition = new EqualityCondition(CourseCategory :: PROPERTY_ID, $category);
        return $this->retrieve_object(CourseCategory :: get_table_name(), $condition, array(), CourseCategory :: CLASS_NAME);
    }

    function retrieve_course_category_by_code($category_code)
    {
        $condition = new EqualityCondition(CourseCategory :: PROPERTY_CODE, $category_code);
        return $this->retrieve_object(CourseCategory :: get_table_name(), $condition, array(), CourseCategory :: CLASS_NAME);
    }
    
    function retrieve_course_categories($condition = null, $offset = null, $max_objects = null, $order_by = null)
    {
        $order_by[] = new ObjectTableOrder(CourseCategory :: PROPERTY_NAME);
        $order_dir[] = SORT_ASC;

        return $this->retrieve_objects(CourseCategory :: get_table_name(), $condition, $offset, $max_objects, $order_by,
                CourseCategory :: CLASS_NAME);
    }

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

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

    function set_module_visible($course_code, $module, $visible)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_COURSE_CODE, $course_code);
        $conditions[] = new EqualityCondition(CourseModule :: PROPERTY_NAME, $module);
        $condition = new AndCondition($conditions);

        $properties = array(CourseModule :: PROPERTY_VISIBLE => $visible);
        return $this->update_objects(CourseModule :: get_table_name(), $properties, $condition);
    }

    function set_module_id_visible($module_id, $visible)
    {
        $condition = new EqualityCondition(CourseModule :: PROPERTY_VISIBLE, $visible);
        $sort = $this->retrieve_max_sort_value(CourseModule :: get_table_name(), CourseModule :: PROPERTY_SORT,
                $condition);
        $condition = new EqualityCondition(CourseModule :: PROPERTY_ID, $module_id);
        $properties = array(CourseModule :: PROPERTY_VISIBLE => $visible, CourseModule :: PROPERTY_SORT => $sort + 1);
        return $this->update_objects(CourseModule :: get_table_name(), $properties, $condition);
    }

    /**
     * Moves learning object publication up
     *
     * @param $publication ContentObjectPublication The publication to move
     * @param $places int The number of places to move the publication up
     */
    private function move_content_object_publication_up($publication, $places)
    {
        $oldIndex = $publication->get_display_order_index();

        $conditions = array();
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_COURSE_ID,
                $publication->get_course_id());
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_TOOL, $publication->get_tool());
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_CATEGORY_ID,
                $publication->get_category_id());
        $conditions[] = new InequalityCondition(ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX,
                InequalityCondition :: LESS_THAN, $oldIndex);
        $condition = new AndCondition($conditions);

        $properties[ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX] = $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX) . '+1';

        if (! $this->update_objects(ContentObjectPublication :: get_table_name(), $properties, $condition, null,
                $places, new ObjectTableOrder(ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX, SORT_DESC)))
        {
            return false;
        }

        $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_ID, $publication->get_id());
        $properties[ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX] = $oldIndex - $places;
        return $this->update_objects(ContentObjectPublication :: get_table_name(), $properties, $condition, null, 1);
    }

    /**
     * Moves learning object publication down
     *
     * @param $publication ContentObjectPublication The publication to move
     * @param $places int The number of places to move the publication down
     */
    private function move_content_object_publication_down($publication, $places)
    {
        $oldIndex = $publication->get_display_order_index();

        $conditions = array();
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_COURSE_ID,
                $publication->get_course_id());
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_TOOL, $publication->get_tool());
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_CATEGORY_ID,
                $publication->get_category_id());
        $conditions[] = new InequalityCondition(ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX,
                InequalityCondition :: GREATER_THAN, $oldIndex);
        $condition = new AndCondition($conditions);

        $properties[ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX] = $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX) . '-1';

        if (! $this->update_objects(ContentObjectPublication :: get_table_name(), $properties, $condition, null,
                $places, new ObjectTableOrder(ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX, SORT_ASC)))
        {
            return false;
        }

        $condition = new EqualityCondition(ContentObjectPublication :: PROPERTY_ID, $publication->get_id());
        $properties[ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX] = $oldIndex + $places;
        return $this->update_objects(ContentObjectPublication :: get_table_name(), $properties, $condition, null, 1);
    }

    function fix_publication_display_order($course, $tool, $category_id = 0, $start_display_order = 1)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_COURSE_ID, $course);
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_TOOL, $tool);
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_CATEGORY_ID, $category_id);
        $conditions[] = new InequalityCondition(ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX,
                InequalityCondition :: GREATER_THAN, $start_display_order);
        $condition = new AndCondition($conditions);

        $properties[ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX] = $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX) . '-1';

        return $this->update_objects(ContentObjectPublication :: get_table_name(), $properties, $condition, null, null,
                new ObjectTableOrder(ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX, SORT_ASC));
    }

    function get_next_content_object_publication_display_order_index($course, $tool, $category)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_COURSE_ID, $course);
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_TOOL, $tool);
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_CATEGORY_ID, $category);
        $condition = new AndCondition($conditions);

        return $this->retrieve_next_sort_value(ContentObjectPublication :: get_table_name(),
                ContentObjectPublication :: PROPERTY_DISPLAY_ORDER_INDEX, $condition);
    }

    private function get_publication_category_tree($parent, $categories)
    {
        $subtree = array();
        foreach ($categories[$parent] as $child)
        {
            $id = $child->get_id();
            $ar = array();
            $ar['obj'] = $child;
            $ar['sub'] = $this->get_publication_category_tree($id, $categories);
            $subtree[$id] = $ar;
        }
        return $subtree;
    }

    function retrieve_publication_target_user_ids($publication_id)
    {
        $course_id = $this->retrieve_content_object_publication($publication_id)->get_course_id();
        $target_entities = WeblcmsRights :: get_instance()->get_target_entities(WeblcmsRights :: VIEW_RIGHT,
                WeblcmsManager :: APPLICATION_NAME, $publication_id, WeblcmsRights :: TYPE_PUBLICATION, $course_id,
                WeblcmsRights :: TREE_TYPE_COURSE);

        return $target_entities[CourseUserEntity :: ENTITY_TYPE];
    }

    function retrieve_publication_target_course_group_ids($publication_id)
    {
        $course_id = $this->retrieve_content_object_publication($publication_id)->get_course_id();
        $target_entities = WeblcmsRights :: get_instance()->get_target_entities(WeblcmsRights :: VIEW_RIGHT,
                WeblcmsManager :: APPLICATION_NAME, $publication_id, WeblcmsRights :: TYPE_PUBLICATION, $course_id,
                WeblcmsRights :: TREE_TYPE_COURSE);

        return $target_entities[CourseGroupEntity :: ENTITY_TYPE];
    }

    function retrieve_publication_target_platform_group_ids($publication_id)
    {
        $course_id = $this->retrieve_content_object_publication($publication_id)->get_course_id();
        $target_entities = WeblcmsRights :: get_instance()->get_target_entities(WeblcmsRights :: VIEW_RIGHT,
                WeblcmsManager :: APPLICATION_NAME, $publication_id, WeblcmsRights :: TYPE_PUBLICATION, $course_id,
                WeblcmsRights :: TREE_TYPE_COURSE);

        return $target_entities[CoursePlatformGroupEntity :: ENTITY_TYPE];
    }

    function retrieve_publication_target_users($publication_id, $course_id, $offset = null, $count = null, $order_by = null,
            $condition = null)
    {
        if (is_null($course_id))
        {
            $course_id = $this->retrieve_content_object_publication($publication_id)->get_course_id();
        }

        $target_entities = WeblcmsRights :: get_instance()->get_target_entities(WeblcmsRights :: VIEW_RIGHT,
                WeblcmsManager :: APPLICATION_NAME, $publication_id, WeblcmsRights :: TYPE_PUBLICATION, $course_id,
                WeblcmsRights :: TREE_TYPE_COURSE);

        $users_ids = array();
        $user_data_manager = UserDataManager :: get_instance();
        foreach ($target_entities as $entity_type => $entity_ids)
        {
            switch ($entity_type)
            {
                case CourseUserEntity :: ENTITY_TYPE :
                    foreach ($entity_ids as $user_id)
                    {
                        $users_ids[$user_id] = $user_id;
                    }
                    break;

                case CourseGroupEntity :: ENTITY_TYPE :
                    $course_groups = $this->retrieve_course_groups_and_subgroups(
                            $target_entities[CourseGroupEntity :: ENTITY_TYPE]);
                    while ($course_group = $course_groups->next_result())
                    {
                        $course_group_users = $this->retrieve_course_group_user_ids($course_group);
                        foreach ($course_group_users as $id)
                        {
                            $users_ids[$id] = $id;
                        }
                    }
                    break;

                case CoursePlatformGroupEntity :: ENTITY_TYPE :
                    $group_manager = GroupDataManager :: get_instance();
                    $group_condition = new InCondition(Group :: PROPERTY_ID, $entity_ids);
                    $groups_resultset = $group_manager->retrieve_groups($group_condition);
                    while ($group = $groups_resultset->next_result())
                    {
                        $user_ids_from_group = $group->get_users(true, true);
                        foreach ($user_ids_from_group as $user)
                        {
                            $users_ids[$user] = $user;
                        }
                    }
                    break;

                case 0 :
                    return $this->retrieve_all_course_users($course_id, $condition, $offset, $count, $order_by);
            }
        }

        if (count($users_ids) == 0)
        {
            $users_ids[] = - 1;
        }
        $conditions = array();
        $conditions[] = new InCondition(User :: PROPERTY_ID, $users_ids);
        if ($condition)
        {
            $conditions[] = $condition;
        }
        $user_condition = new AndCondition($conditions);

        return $user_data_manager->retrieve_users($user_condition, $offset, $count, $order_by);
    }

    function retrieve_publication_target_course_groups($publication_id, $course_id, $offset = null, $count = null,
            $order_by = null, $condition = null)
    {
        if (is_null($course_id))
        {
            $course_id = $this->retrieve_content_object_publication($publication_id)->get_course_id();
        }

        $target_entities = WeblcmsRights :: get_instance()->get_target_entities(WeblcmsRights :: VIEW_RIGHT,
                WeblcmsManager :: APPLICATION_NAME, $publication_id, WeblcmsRights :: TYPE_PUBLICATION, $course_id,
                WeblcmsRights :: TREE_TYPE_COURSE);

        if ($target_entities[0])
        {
            $conditions = array();
            $conditions[] = new EqualityCondition(CourseGroup :: PROPERTY_COURSE_CODE, $course_id);
            $conditions[] = new InequalityCondition(CourseGroup :: PROPERTY_PARENT_ID,
                    InequalityCondition :: GREATER_THAN, 0);
            if ($condition)
            {
                $conditions[] = $condition;
            }
            $group_condition = new AndCondition($conditions);
        }
        else
        {
            return $this->retrieve_course_groups_and_subgroups($target_entities[CourseGroupEntity :: ENTITY_TYPE],
                    $condition, $offset, $count, $order_by);
        }

        return $this->retrieve_course_groups($group_condition, $offset, $count, $order_by);
    }

    function retrieve_publication_target_platform_groups($publication_id, $course_id, $offset = null, $count = null,
            $order_by = null, $condition = null)
    {
        if (is_null($course_id))
        {
            $course_id = $this->retrieve_content_object_publication($publication_id)->get_course_id();
        }

        $target_entities = WeblcmsRights :: get_instance()->get_target_entities(WeblcmsRights :: VIEW_RIGHT,
                WeblcmsManager :: APPLICATION_NAME, $publication_id, WeblcmsRights :: TYPE_PUBLICATION, $course_id,
                WeblcmsRights :: TREE_TYPE_COURSE);

        $gdm = GroupDataManager :: get_instance();

        if ($target_entities[0])
        {
            $cgr_condition = new EqualityCondition(CourseGroupRelation :: PROPERTY_COURSE_ID, $course_id);
            $cgr_resultset = $this->retrieve_course_group_relations($cgr_condition);
            $group_ids = array();
            while ($course_group_rel = $cgr_resultset->next_result())
            {
                $group_ids[] = $course_group_rel->get_group_id();
            }
            return $gdm->retrieve_groups_and_subgroups($group_ids, $offset, $count, $order_by, $condition);
        }
        return $gdm->retrieve_groups_and_subgroups($target_entities[CoursePlatformGroupEntity :: ENTITY_TYPE], $offset,
                $count, $order_by, $condition);
    }

    // Inherited
    function delete_course_group($course_group)
    {
        // Delete subscription of users in this course_group
        $condition = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_COURSE_GROUP, $course_group->get_id());
        $succes = $this->delete(CourseGroupUserRelation :: get_table_name(), $condition);

        if (! $succes)
        {
            return false;
        }

        $condition = new EqualityCondition(CourseGroup :: PROPERTY_ID, $course_group->get_id());
        $succes = $this->delete(CourseGroup :: get_table_name(), $condition);

        return $succes;
    }

    // Inherited
    function create_course_group_user_relation($course_group_user_relation)
    {
        return $this->create($course_group_user_relation);
    }

    // Inherited
    function retrieve_course_group($id)
    {
        if (is_null($this->course_group_cache[$id]))
        {
            $condition = new EqualityCondition(CourseGroup :: PROPERTY_ID, $id);
            $this->course_group_cache[$id] = $this->retrieve_object(CourseGroup :: get_table_name(), $condition,
                    array(), CourseGroup :: CLASS_NAME);
        }
        return $this->course_group_cache[$id];
    }

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

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

    function retrieve_requests($condition = null, $offset = null, $max_objects = null, $order_by = null)
    {
        $order_by[] = new ObjectTableOrder(CourseRequest :: PROPERTY_SUBJECT);
        return $this->retrieve_objects(CourseRequest :: get_table_name(), $condition, $offset, $max_objects, $order_by,
                CourseRequest :: CLASS_NAME);
    }

    function retrieve_course_create_requests($condition = null, $offset = null, $max_objects = null, $order_by = null)
    {
        $order_by[] = new ObjectTableOrder(CourseCreateRequest :: PROPERTY_SUBJECT);
        return $this->retrieve_objects(CourseCreateRequest :: get_table_name(), $condition, $offset, $max_objects,
                $order_by, CourseCreateRequest :: CLASS_NAME);
    }

    function retrieve_course_group_by_name($name)
    {
        $condition = new EqualityCondition(CourseGroup :: PROPERTY_NAME, $name);
        return $this->retrieve_object(CourseGroup :: get_table_name(), $condition, array(), CourseGroup :: CLASS_NAME);
    }

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

    // Inherited
    function retrieve_course_groups($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 count_course_groups($condition)
    {
        return $this->count_objects(CourseGroup :: get_table_name(), $condition);
    }

    // Inherited
    function retrieve_course_group_user_ids($course_group)
    {
        $condition = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_COURSE_GROUP, $course_group->get_id());
        $relations = $this->retrieve_objects(CourseGroupUserRelation :: get_table_name(), $condition, null, null,
                array(), CourseGroupUserRelation :: CLASS_NAME);
        $user_ids = array();

        while ($relation = $relations->next_result())
        {
            $user_ids[] = $relation->get_user();
        }

        return $user_ids;
    }

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

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

    // Inherited
    function retrieve_course_groups_from_user($user_id, $course_id = null)
    {
        $group_alias = $this->get_alias(CourseGroup :: get_table_name());
        $group_relation_alias = $this->get_alias(CourseGroupUserRelation :: get_table_name());

        $query = 'SELECT ' . $group_alias . '.* FROM ' . $this->escape_table_name(CourseGroup :: get_table_name()) . ' AS ' . $group_alias;
        $query .= ' JOIN ' . $this->escape_table_name(CourseGroupUserRelation :: get_table_name()) . ' AS ' . $group_relation_alias . ' ON ' . $this->escape_column_name(
                CourseGroup :: PROPERTY_ID, $group_alias) . ' = ' . $this->escape_column_name(
                CourseGroupUserRelation :: PROPERTY_COURSE_GROUP, $group_relation_alias);

        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_USER, $user_id,
                CourseGroupUserRelation :: get_table_name());
        if (! is_null($course_id))
        {
            $conditions[] = new EqualityCondition(CourseGroup :: PROPERTY_COURSE_CODE, $course_id);
        }

        $condition = new AndCondition($conditions);

        return $this->retrieve_object_set($query, CourseGroup :: get_table_name(), $condition, null, null, array(),
                CourseGroup :: CLASS_NAME);
    }

    // Inherited
    function retrieve_course_group_users($course_group, $condition = null, $offset = null, $count = null, $order_property = null)
    {
        $user_ids = $this->retrieve_course_group_user_ids($course_group);

        if (count($user_ids) > 0)
        {
            $user_condition = new InCondition(User :: PROPERTY_ID, $user_ids);
            if (is_null($condition))
            {
                $condition = $user_condition;
            }
            else
            {
                $condition = new AndCondition($condition, $user_condition);
            }
        }
        else
        {
            // TODO: We need a better fix for this !
            $condition = new EqualityCondition(User :: PROPERTY_ID, '-1000');
        }

        return \user\DataManager :: retrieves(
            User :: class_name(),
            new \common\libraries\DataClassRetrievesParameters($condition, $count, $offset, $order_property)
        );
    }

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

    // Inherited
    function count_course_group_users($course_group, $conditions = null)
    {
        $user_ids = $this->retrieve_course_group_user_ids($course_group);
        if (count($user_ids) > 0)
        {
            $condition = new InCondition(User :: PROPERTY_ID, $user_ids);
            if (is_null($conditions))
            {
                $conditions = $condition;
            }
            else
            {
                $conditions = new AndCondition($condition, $conditions);
            }

            $udm = UserDataManager :: get_instance();
            return $udm->count_users($conditions);
        }
        else
        {
            return 0;
        }
    }

    // Inherited
    function retrieve_possible_course_group_users($course_group, $condition = null, $offset = null, $count = null,
            $order_property = null)
    {
        $course_condition = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID,
                $course_group->get_course_code());
        $course_users = $this->retrieve_course_user_relations($course_condition);
        $group_user_ids = $this->retrieve_course_group_user_ids($course_group);

        $course_user_ids = array();

        while ($course_user = $course_users->next_result())
        {
            $course_user_ids[] = $course_user->get_user();
        }

        $conditions = array();
        $conditions[] = $condition;
        $conditions[] = new InCondition(User :: PROPERTY_ID, $course_user_ids);
        $conditions[] = new NotCondition(new InCondition(User :: PROPERTY_ID, $group_user_ids));
        $condition = new AndCondition($conditions);

        $udm = UserDataManager :: get_instance();
        return $udm->retrieve_users($condition, $offset, $count, $order_property);
    }

    // Inherited
    function count_possible_course_group_users($course_group, $conditions = null)
    {
        if (! is_array($conditions))
        {
            $conditions = array();
        }
        $udm = UserDataManager :: get_instance();
        $query = 'SELECT user_id FROM ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' WHERE ' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_COURSE_ID) . '=' . $this->quote($course_group->get_course_code());
        $res = $this->query($query);
        while ($record = $res->fetchRow(MDB2_FETCHMODE_ASSOC))
        {
            $course_user_ids[] = $record[User :: PROPERTY_ID];
        }

        $res->free();

        $conditions[] = new InCondition(User :: PROPERTY_ID, $course_user_ids);
        $user_ids = $this->retrieve_course_group_user_ids($course_group);
        if (count($user_ids) > 0)
        {
            $user_condition = new NotCondition(new InCondition(User :: PROPERTY_ID, $user_ids));
            $conditions[] = $user_condition;
        }
        $condition = new AndCondition($conditions);
        return $udm->count_users($condition);
    }

    // Inherited
    function subscribe_users_to_course_groups($users, $course_group)
    {
        if (! is_array($users))
        {
            $users = array($users);
        }

        foreach ($users as $user)
        {
            $course_group_user_relation = new CourseGroupUserRelation();
            $course_group_user_relation->set_course_group($course_group->get_id());
            $course_group_user_relation->set_user($user->get_id());
            if (! $course_group_user_relation->create())
            {
                return false;
            }
        }

        return true;
    }

    function more_subscriptions_allowed_for_user_in_group($course_group_id, $user_id)
    {
        if ($course_group_id == null)
            return true;

        $group_group = $this->retrieve_course_group_group_relation($course_group_id);

        $condition = new EqualityCondition(CourseGroup :: PROPERTY_GROUP_ID, $group_group->get_id());
        $all_groups = $this->retrieve_course_groups($condition);

        $num_groups = 0;
        $max_groups = $group_group->get_max_number_of_course_group_per_member();
        while ($group_course_group = $all_groups->next_result())
        {
            $conditions = array();
            $conditions[] = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_COURSE_GROUP, $group_course_group->get_id());
            $conditions[] = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_USER, $user_id);
            $condition = new AndCondition($conditions);

            $users = $this->retrieve_course_group_user_relations($condition);
            if ($users->next_result() != null)
            {
                $num_groups++;
            }
        }
        return $num_groups < $max_groups;
    }

    // Inherited
    function unsubscribe_users_from_course_groups($users, $course_group)
    {
        if (! is_array($users))
        {
            $users = array($users);
        }

        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_COURSE_GROUP, $course_group->get_id());
        $conditions[] = new InCondition(CourseGroupUserRelation :: PROPERTY_USER, $users);
        $condition = new AndCondition($conditions);

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

    // Inherited
    function is_course_group_member($course_group, $user)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_COURSE_GROUP, $course_group->get_id());
        $conditions[] = new EqualityCondition(CourseGroupUserRelation :: PROPERTY_USER, $user->get_id());
        $condition = new AndCondition($conditions);

        return $this->count_objects(CourseGroupUserRelation :: get_table_name(), $condition) > 0;
    }

    private static function from_db_date($date)
    {
        return DatabaseRepositoryDataManager :: from_db_date($date);
    }

    function delete_category($category)
    {
        $condition = new EqualityCondition(CourseCategory :: PROPERTY_ID, $category->get_id());
        $succes = $this->delete(CourseCategory :: get_table_name(), $condition);

        $conditions = array();
        $conditions[] = new InequalityCondition(CourseCategory :: PROPERTY_DISPLAY_ORDER,
                InequalityCondition :: GREATER_THAN, $category->get_display_order());
        $conditions[] = new EqualityCondition(CourseCategory :: PROPERTY_PARENT, $category->get_parent());
        $condition = new AndCondition($conditions);

        $properties = array(
                CourseCategory :: PROPERTY_DISPLAY_ORDER => $this->escape_column_name(
                        CourseCategory :: PROPERTY_DISPLAY_ORDER) . ' - 1');

        return $this->update_objects(CourseCategory :: get_table_name(), $properties, $condition);
    }

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

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

    function delete_content_object_publication_category($content_object_publication_category)
    {
        $condition = new EqualityCondition(ContentObjectPublicationCategory :: PROPERTY_ID,
                $content_object_publication_category->get_id());
        $succes = $this->delete(ContentObjectPublicationCategory :: get_table_name(), $condition);

        $conditions = array();
        $conditions[] = new InequalityCondition(ContentObjectPublicationCategory :: PROPERTY_DISPLAY_ORDER,
                InequalityCondition :: GREATER_THAN, $content_object_publication_category->get_display_order());
        $conditions[] = new EqualityCondition(ContentObjectPublicationCategory :: PROPERTY_PARENT,
                $content_object_publication_category->get_parent());
        $conditions[] = new EqualityCondition(ContentObjectPublicationCategory :: PROPERTY_COURSE,
                $content_object_publication_category->get_course());
        $conditions[] = new EqualityCondition(ContentObjectPublicationCategory :: PROPERTY_TOOL,
                $content_object_publication_category->get_tool());
        $condition = new AndCondition($conditions);

        $properties = array(
                ContentObjectPublicationCategory :: PROPERTY_DISPLAY_ORDER => $this->escape_column_name(
                        ContentObjectPublicationCategory :: PROPERTY_DISPLAY_ORDER) . '-1');
        $this->update_objects(ContentObjectPublicationCategory :: get_table_name(), $properties, $condition);

        $this->delete_content_object_publication_children($content_object_publication_category->get_id());

        return $succes;
    }

    function delete_content_object_publication_children($parent_id)
    {
        $condition = new EqualityCondition(ContentObjectPublicationCategory :: PROPERTY_PARENT, $parent_id);
        $categories = $this->retrieve_content_object_publication_categories($condition);

        while ($category = $categories->next_result())
        {
            $category->delete();
            $this->delete_content_object_publication_children($category->get_id());
        }
    }

    function count_content_object_publication_categories($conditions = null)
    {
        return $this->count_objects(ContentObjectPublicationCategory :: get_table_name(), $conditions);
    }

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

    function get_maximum_score($assessment)
    {
        $condition = new EqualityCondition(ComplexContentObjectItem :: PROPERTY_PARENT, $assessment->get_id(),
                ComplexContentObjectItem :: get_table_name());
        $clo_questions = RepositoryDataManager :: get_instance()->retrieve_complex_content_object_items($condition);

        while ($clo_question = $clo_questions->next_result())
        {
            $maxscore += $clo_question->get_weight();
        }
        return $maxscore;
    }

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

    function delete_course_section($course_section)
    {
        $condition = new EqualityCondition(CourseSection :: PROPERTY_ID, $course_section->get_id());
        if (! $this->delete(CourseSection :: get_table_name(), $condition))
        {
            return false;
        }
        else
        {
            $conditions = array();
            $conditions[] = new InequalityCondition(CourseSection :: PROPERTY_DISPLAY_ORDER,
                    InequalityCondition :: GREATER_THAN, $course_section->get_display_order());
            $conditions[] = new EqualityCondition(CourseSection :: PROPERTY_COURSE_ID, $course_section->get_course_id());
            $condition = new AndCondition($conditions);

            $properties = array(
                    CourseSection :: PROPERTY_DISPLAY_ORDER => $this->escape_column_name(
                            CourseSection :: PROPERTY_DISPLAY_ORDER) . ' - 1');
            if (! $this->update_objects(CourseSection :: get_table_name(), $properties, $condition))
            {
                return false;
            }
            else
            {
                $condition = new EqualityCondition(CourseToolRelCourseSection :: PROPERTY_SECTION_ID,
                        $course_section->get_id());
                return $this->delete_objects(CourseToolRelCourseSection :: get_table_name(), $condition);
            }
        }
    }

    /*
     * Deprecated function change_module_course_section($module_id, $course_section_id) { $condition = new
     * EqualityCondition(CourseModule :: PROPERTY_ID, $module_id); $properties = array(CourseModule :: PROPERTY_SECTION
     * => $course_section_id); return $this->update_objects(CourseModule :: get_table_name(), $properties, $condition);
     * }
     */

    function count_course_sections($conditions = null)
    {
        return $this->count_objects(CourseSection :: get_table_name(), $conditions);
    }

    function retrieve_course_sections($condition = null, $offset = null, $count = null, $order_property = null)
    {
        $order_property = array(new ObjectTableOrder(CourseSection :: PROPERTY_DISPLAY_ORDER));
        return $this->retrieve_objects(CourseSection :: get_table_name(), $condition, $offset, $count, $order_property,
                CourseSection :: CLASS_NAME);
    }

    function times_taken($user_id, $assessment_id)
    {
        /*
         * $query = 'SELECT COUNT('.$this->escape_column_name(UserAssessment :: PROPERTY_ID).') FROM
         * '.$this->escape_table_name(UserAssessment :: get_table_name()).' WHERE
         * '.$this->escape_column_name(UserAssessment :: PROPERTY_ASSESSMENT_ID).'='.$assessment_id.' AND
         * '.$this->escape_column_name(UserAssessment :: PROPERTY_USER_ID).'='.$user_id; $sth =
         * $this->get_connection()->prepare($query); $res = $sth->execute(); $row =
         * $res->fetchRow(MDB2_FETCHMODE_ORDERED); return $row[0];
         */
        return 0;
    }

    // Inherited.
    function is_visual_code_available($visual_code, $id = null) // course
    {
        $condition = new EqualityCondition(Course :: PROPERTY_VISUAL_CODE, $visual_code);
        if ($id)
        {
            $conditions = array();
            $conditions[] = new EqualityCondition(Course :: PROPERTY_VISUAL_CODE, $visual_code);
            $conditions = new EqualityCondition(Course :: PROPERTY_ID, $id);
            $condition = new AndCondition($conditions);
        }
        return ! ($this->count_courses($condition) == 1);
    }

    function retrieve_course_by_visual_code($visual_code)
    {
        $condition = new EqualityCondition(Course :: PROPERTY_VISUAL_CODE, $visual_code);
        return $this->retrieve_object(Course :: get_table_name(), $condition, array(), Course :: class_name());
    }

    // nested trees functions for course_groups

    function count_course_group_children($node)
    {
        return $this->count_children($node, $this->get_course_group_nested_condition($node));
    }

    function get_course_group_children($node, $recursive = false)
    {
        return $this->get_children($node, $recursive, $this->get_course_group_nested_condition($node));
    }

    function count_course_group_parents($node, $include_object = false)
    {
        return $this->count_parents($node, $include_object, $this->get_course_group_nested_condition($node));
    }

    function get_course_group_parents($node, $recursive = false, $include_object = false)
    {
        return $this->get_parents($node, $recursive, $include_object, $this->get_course_group_nested_condition($node));
    }

    function count_course_group_siblings($node, $include_object = false)
    {
        return $this->count_siblings($node, $include_object, $this->get_course_group_nested_condition($node));
    }

    function get_course_group_siblings($node, $include_object = false)
    {
        return $this->get_siblings($node, $include_object, $this->get_course_group_nested_condition($node));
    }

    function move_course_group($node, $new_parent_id = 0, $new_previous_id = 0)
    {
        return $this->move($node, $new_parent_id, $new_previous_id, $this->get_course_group_nested_condition($node));
    }

    function add_course_group_nested_values($node, $previous_visited, $number_of_elements = 1)
    {
        return $this->add_nested_values($node, $previous_visited, $number_of_elements,
                $this->get_course_group_nested_condition($node));
    }

    function delete_course_group_nested_values($node)
    {
        return $this->delete_nested_values($node, $this->get_course_group_nested_condition($node));
    }

    /**
     * Gets the conditions for the course group nested tree functions
     *
     * @param $course_group CourseGroup
     */
    private function get_course_group_nested_condition($course_group)
    {
        return new EqualityCondition(CourseGroup :: PROPERTY_COURSE_CODE, $course_group->get_course_code());
    }

    function retrieve_course_group_root($course_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroup :: PROPERTY_COURSE_CODE, $course_id);
        $conditions[] = new EqualityCondition(CourseGroup :: PROPERTY_PARENT_ID, 0);
        $condition = new AndCondition($conditions);
        return $this->retrieve_course_groups($condition)->next_result();
    }

    function retrieve_new_publication_icon_ids($course_id, $user_id, $is_teacher = false, $tool = null, $category_id = null)
    {
        $publication_alias = $this->get_alias(ContentObjectPublication :: get_table_name());
        $course_module_last_access_alias = $this->get_alias(CourseModuleLastAccess :: get_table_name());

        $conditions = array();
        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_COURSE_ID, $course_id);

        // $access_conditions[] = new
        // InequalityCondition(ContentObjectPublication::
        // PROPERTY_MODIFIED_DATE, InequalityCondition::GREATER_THAN_OR_EQUAL,
        // CourseModuleLastAccess::PROPERTY_ACCESS_DATE);
        // $access_conditions[] = new EqualityCondition(CourseModuleLastAccess
        // :: PROPERTY_ACCESS_DATE, NULL, $course_module_last_access_alias,
        // true);
        // $conditions[] = new OrCondition($access_conditions);

        if ($is_teacher)
        { // as the teacher has view right on all publications, there is no need
          // to retrieve the individual publications ids
            $query = 'SELECT DISTINCT ' . $publication_alias . '.' . ContentObjectPublication :: PROPERTY_TOOL;
        }
        else
        { // for a student the publication ids are needed, to check the view
          // right
            $query = 'SELECT ' . $publication_alias . '.' . ContentObjectPublication :: PROPERTY_ID . ', ' . $publication_alias . '.' . ContentObjectPublication :: PROPERTY_TOOL;
            $query .= ', ' . $publication_alias . '.' . ContentObjectPublication :: PROPERTY_CATEGORY_ID;
        }

        $query .= ' FROM ' . $this->escape_table_name(ContentObjectPublication :: get_table_name()) . ' AS ' . $publication_alias;
        $query .= ' LEFT JOIN ' . $this->escape_table_name(CourseModuleLastAccess :: get_table_name()) . ' AS ' . $course_module_last_access_alias . ' ON ';
        $query .= $this->escape_column_name(CourseModuleLastAccess :: PROPERTY_COURSE_CODE,
                $course_module_last_access_alias);
        $query .= '=' . $this->escape_column_name(ContentObjectPublication :: PROPERTY_COURSE_ID, $publication_alias);
        $query .= ' AND ' . $this->escape_column_name(CourseModuleLastAccess :: PROPERTY_MODULE_NAME,
                $course_module_last_access_alias);
        $query .= '=' . $this->escape_column_name(ContentObjectPublication :: PROPERTY_TOOL, $publication_alias);
        $query .= ' AND ' . $this->escape_column_name(CourseModuleLastAccess :: PROPERTY_USER_ID,
                $course_module_last_access_alias);
        $query .= '=' . $user_id;
        $query .= ' AND ' . $this->escape_column_name(CourseModuleLastAccess :: PROPERTY_CATEGORY_ID,
                $course_module_last_access_alias);
        $query .= '=' . $this->escape_column_name(ContentObjectPublication :: PROPERTY_CATEGORY_ID, $publication_alias);

        if (! is_null($tool))
        {
            $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_TOOL, $tool);
        }
        if (! is_null($category_id))
        {
            $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_CATEGORY_ID, $category_id);
        }

        $conditions[] = new EqualityCondition(ContentObjectPublication :: PROPERTY_HIDDEN, 0);
        $conditions_publication_period = array();
        $conditions_publication_period[] = new InequalityCondition(ContentObjectPublication :: PROPERTY_FROM_DATE,
                InequalityCondition :: LESS_THAN_OR_EQUAL, time());
        $conditions_publication_period[] = new InequalityCondition(ContentObjectPublication :: PROPERTY_TO_DATE,
                InequalityCondition :: GREATER_THAN_OR_EQUAL, time());

        $condition_publication_period = new AndCondition($conditions_publication_period);
        $condition_publication_forever = new EqualityCondition(ContentObjectPublication :: PROPERTY_FROM_DATE, 0);
        $conditions[] = new OrCondition($condition_publication_forever, $condition_publication_period);
        $conditions[] = new NotCondition(new EqualityCondition(ContentObjectPublication :: PROPERTY_TOOL, 'home'));

        $condition = new AndCondition($conditions);

        $translator = ConditionTranslator :: factory($this,
                $this->get_alias(ContentObjectPublication :: get_table_name()));
        $query .= $translator->render_query($condition);
        $query .= ' AND (' . $publication_alias . '.modified >= ' . $course_module_last_access_alias . '.access_date OR ' . $course_module_last_access_alias . '.access_date IS NULL )';

        $result = $this->retrieve_object_set($query, ContentObjectPublication :: get_table_name());
        
        // We need to do this due to issues with the dataclass cache for joins
        DataClassCache :: truncate(ContentObjectPublication :: class_name());
         
        return $result;

    }

    function get_user_with_most_publications_in_course($course_id, $only_teachers = true)
    {
        $content_object_publication_table_name = $this->get_table_name(ContentObjectPublication :: get_table_name());
        $course_rel_user_table_name = $this->get_table_name(CourseUserRelation :: get_table_name());
        $content_object_publication_table_alias = $this->get_alias(ContentObjectPublication :: get_table_name());
        $course_rel_user_table_alias = $this->get_alias(CourseUserRelation :: get_table_name());

        $query = 'SELECT ' . $this->escape_column_name(CourseUserRelation :: PROPERTY_USER_ID,
                $course_rel_user_table_alias);
        $query .= ', COUNT(' . $this->escape_column_name(ContentObjectPublication :: PROPERTY_ID,
                $content_object_publication_table_alias) . ') as count';
        $query .= ' FROM ' . $course_rel_user_table_name . ' AS ' . $course_rel_user_table_alias;
        $query .= ' LEFT JOIN ' . $content_object_publication_table_name . ' AS ' . $content_object_publication_table_alias . ' ON ';
        $query .= $this->escape_column_name(CourseUserRelation :: PROPERTY_COURSE_ID, $course_rel_user_table_alias) . ' = ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_COURSE_ID, $content_object_publication_table_alias);
        $query .= ' AND ' . $this->escape_column_name(CourseUserRelation :: PROPERTY_USER_ID,
                $course_rel_user_table_alias) . ' = ' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_PUBLISHER_ID, $content_object_publication_table_alias);

        if ($only_teachers)
        {
            $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_STATUS,
                    CourseUserRelation :: STATUS_TEACHER, CourseUserRelation :: get_table_name());
        }

        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_id,
                CourseUserRelation :: get_table_name());

        $condition = new AndCondition($conditions);

        $translator = ConditionTranslator :: factory($this);
        $query .= $translator->render_query($condition);

        $query .= ' GROUP BY ' . $this->escape_column_name(CourseUserRelation :: PROPERTY_USER_ID,
                $course_rel_user_table_alias);
        $query .= ' ORDER BY count DESC LIMIT 0,1';

        $result = $this->query($query);
        $res = $this->query($query);
        $record = $res->fetchRow(MDB2_FETCHMODE_ASSOC);
        $res->free();

        if ($record['count'] >= 0)
        {
            return $record[CourseUserRelation :: PROPERTY_USER_ID];
        }
    }

    // Additional Rights System

    function retrieve_course_group_right_location($right_id, $course_group_id, $location_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseGroupRightLocation :: PROPERTY_RIGHT_ID, $right_id);
        $conditions[] = new EqualityCondition(CourseGroupRightLocation :: PROPERTY_COURSE_GROUP_ID, $course_group_id);
        $conditions[] = new EqualityCondition(CourseGroupRightLocation :: PROPERTY_LOCATION_ID, $location_id);
        $condition = new AndCondition($conditions);

        return $this->retrieve_object(CourseGroupRightLocation :: get_table_name(), $condition, array(),
                CourseGroupRightLocation :: CLASS_NAME);
    }

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

    function delete_course_type_user_category_rel_course(
            CourseTypeUserCategoryRelCourse $course_type_user_category_rel_course)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseTypeUserCategoryRelCourse :: PROPERTY_COURSE_ID,
                $course_type_user_category_rel_course->get_course_id());
        $conditions[] = new EqualityCondition(CourseTypeUserCategoryRelCourse :: PROPERTY_COURSE_TYPE_USER_CATEGORY_ID,
                $course_type_user_category_rel_course->get_course_type_user_category_id());
        $condition = new AndCondition($conditions);

        return $this->delete(CourseTypeUserCategoryRelCourse :: get_table_name(), $condition);
    }

    function update_course_type_user_category_rel_course(
            CourseTypeUserCategoryRelCourse $course_type_user_category_rel_course)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseTypeUserCategoryRelCourse :: PROPERTY_COURSE_ID,
                $course_type_user_category_rel_course->get_course_id());
        $conditions[] = new EqualityCondition(CourseTypeUserCategoryRelCourse :: PROPERTY_COURSE_TYPE_USER_CATEGORY_ID,
                $course_type_user_category_rel_course->get_course_type_user_category_id());
        $condition = new AndCondition($conditions);

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

    function count_course_type_user_category_rel_courses($conditions = null)
    {
        return $this->count_objects(CourseTypeUserCategoryRelCourse :: get_table_name(), $conditions);
    }

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

    /**
     * Cleans the sort value of a current course_type_user_category starting by the given sort value
     *
     * @param $start_sort_value int
     * @param $course_type_user_category_id int
     */
    function clean_course_type_user_category_rel_course_sort($start_sort_value, $course_type_user_category_id)
    {
        $conditions = array();
        $conditions[] = new InEqualityCondition(CourseTypeUserCategoryRelCourse :: PROPERTY_SORT,
                InEqualityCondition :: GREATER_THAN, $start_sort_value);
        $conditions[] = new EqualityCondition(CourseTypeUserCategoryRelCourse :: PROPERTY_COURSE_TYPE_USER_CATEGORY_ID,
                $course_type_user_category_id);
        $condition = new AndCondition($conditions);

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

        return $this->update_objects(CourseTypeUserCategoryRelCourse :: get_table_name(), $properties, $condition);
    }

    function retrieve_course_user_categories_from_course_type($course_type_id, $user_id)
    {
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_COURSE_TYPE_ID, $course_type_id,
                CourseTypeUserCategory :: get_table_name());
        $conditions[] = new EqualityCondition(CourseTypeUserCategory :: PROPERTY_USER_ID, $user_id,
                CourseTypeUserCategory :: get_table_name());
        $condition = new AndCondition($conditions);

        $course_user_category_table_name = $this->get_table_name(CourseUserCategory :: get_table_name());
        $course_type_user_category_table_name = $this->get_table_name(CourseTypeUserCategory :: get_table_name());
        $course_user_category_alias = $this->get_alias(CourseUserCategory :: get_table_name());
        $course_type_user_category_alias = $this->get_alias(CourseTypeUserCategory :: get_table_name());

        $course_user_category_id = $this->escape_column_name(CourseUserCategory :: PROPERTY_ID,
                $course_user_category_alias);
        $course_type_user_category_id = $this->escape_column_name(
                CourseTypeUserCategory :: PROPERTY_COURSE_USER_CATEGORY_ID, $course_type_user_category_alias);

        $query = 'SELECT ' . $course_type_user_category_alias . '.*, ' . $course_user_category_alias . '.title FROM ' . $course_type_user_category_table_name . ' AS ' . $course_type_user_category_alias;
        $query .= ' JOIN ' . $course_user_category_table_name . ' AS ' . $course_user_category_alias . ' ON ' . $course_user_category_id . '=' . $course_type_user_category_id;

        return $this->retrieve_object_set($query, CourseTypeUserCategory :: get_table_name(), $condition, null, null,
                new ObjectTableOrder(CourseTypeUserCategory :: PROPERTY_SORT, SORT_ASC),
                CourseTypeUserCategory :: CLASS_NAME);
    }

    function retrieve_all_courses_with_course_categories($user_id, $group_ids)
    {
        $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());
        $course_type_user_category_rel_course_table = $this->get_table_name(
                CourseTypeUserCategoryRelCourse :: get_table_name());
        $course_type_user_category_rel_course_alias = $this->get_alias(
                CourseTypeUserCategoryRelCourse :: get_table_name());

        $sort_column = $this->escape('sort');
        $name_column = $this->escape('course_name');

        $course_type_user_category_rel_course_user_column = $this->escape_column_name(
                CourseTypeUserCategoryRelCourse :: PROPERTY_USER_ID, $course_type_user_category_rel_course_alias);
        $course_type_user_category_rel_course_course_column = $this->escape_column_name(
                CourseTypeUserCategoryRelCourse :: PROPERTY_COURSE_ID, $course_type_user_category_rel_course_alias);
        $course_id_column = $this->escape_column_name(Course :: PROPERTY_ID, $course_alias);

        $condition_translator = ConditionTranslator :: factory($this);

        $common_query_part = ' LEFT JOIN ' . $course_type_user_category_rel_course_table . ' AS ' . $course_type_user_category_rel_course_alias . ' ON ';
        $common_query_part .= $course_type_user_category_rel_course_user_column . ' = ' . $user_id . ' AND ';
        $common_query_part .= $course_type_user_category_rel_course_course_column . ' = ' . $course_id_column;

        $query = 'SELECT ' . $course_alias . '.*, ' . $course_type_user_category_rel_course_alias . '.course_type_user_category_id, ' . $course_type_user_category_rel_course_alias . '.sort ' . $sort_column . ', ' . $course_alias . '.title ' . $name_column;
        $query .= ' FROM ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' AS ' . $course_user_relation_alias;
        $query .= ' JOIN ' . $this->escape_table_name(Course :: get_table_name()) . ' AS ' . $course_alias . ' ON ' . $this->escape_column_name(
                Course :: PROPERTY_ID, $course_alias) . ' = ' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_COURSE_ID, $course_user_relation_alias);
        $query .= $common_query_part;

        $user_condition = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, $user_id,
                $course_user_relation_alias, true);
        $query .= $condition_translator->render_query($user_condition);

        if (count($group_ids) > 0)
        {
            $query .= ' UNION ';
            $query .= 'SELECT ' . $course_alias . '.*, ' . $course_type_user_category_rel_course_alias . '.course_type_user_category_id, ' . $course_type_user_category_rel_course_alias . '.sort ' . $sort_column . ', ' . $course_alias . '.title ' . $name_column;
            $query .= ' FROM ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' AS ' . $course_group_relation_alias;
            $query .= ' JOIN ' . $this->escape_table_name(Course :: get_table_name()) . ' AS ' . $course_alias . ' ON ' . $this->escape_column_name(
                    Course :: PROPERTY_ID, $course_alias) . ' = ' . $this->escape_column_name(
                    CourseGroupRelation :: PROPERTY_COURSE_ID, $course_group_relation_alias);
            $query .= $common_query_part;

            $group_condition = new InCondition(CourseGroupRelation :: PROPERTY_GROUP_ID, $group_ids,
                    $course_group_relation_alias, true);
            $query .= $condition_translator->render_query($group_condition);
        }

        $query .= ' ORDER BY ' . $sort_column . ' ASC, ' . $name_column . ' ASC';

        return $this->retrieve_object_set($query, Course :: get_table_name(), null, null, null, null,
                '\application\weblcms\course\Course');
    }

    /**
     * Retrieves all users subscribed to a course, including the users in all subscribed (sub-)groups.
     *
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_property order
     */
    function retrieve_users_directly_subscribed_to_course($condition = null, $offset = null, $count = null, $order_property = null)
    {
        // datamanagers
        $user_dm = UserDataManager :: get_instance();

        // - SELECT
        $query .= 'SELECT ';

        // -- id
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_ID);
        $query .= ', ';

        // -- official code
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_OFFICIAL_CODE);
        $query .= ', ';

        // -- lastname
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_LASTNAME);
        $query .= ', ';

        // -- firstname
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_FIRSTNAME);
        $query .= ', ';

        // -- username
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_USERNAME);
        $query .= ', ';

        // -- email
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_EMAIL);
        $query .= ', ';

        // -- record_subscription_status
        $query .= $this->get_alias(CourseUserRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_STATUS);
        // $query .= ' AS "record_subscription_status"';

        // - FROM course_user_relation
        $query .= ' FROM ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseUserRelation :: get_table_name());

        // - JOIN user
        $query .= ' JOIN ' . $user_dm->escape_table_name(User :: get_table_name()) . ' AS ' . $user_dm->get_alias(
                User :: get_table_name());
        $query .= ' ON ' . $this->get_alias(CourseUserRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_USER_ID);
        $query .= ' = ' . $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_ID);

        return $this->retrieve_object_set($query, User :: get_table_name(), $condition, $offset, $count,
                $order_property, User :: CLASS_NAME);
    }

    /**
     * Counts all users directly subscribed to a course, users that are subscribed through groups are not counted.
     *
     * @param $condition Condition
     */
    function count_users_directly_subscribed_to_course($condition = null)
    {
        // datamanagers
        $user_dm = UserDataManager :: get_instance();

        // - SELECT
        $query .= 'SELECT COUNT(1)';

        // - FROM course_user_relation
        $query .= ' FROM ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseUserRelation :: get_table_name());

        // - JOIN user
        $query .= ' JOIN ' . $user_dm->escape_table_name(User :: get_table_name()) . ' AS ' . $user_dm->get_alias(
                User :: get_table_name());
        $query .= ' ON ' . $this->get_alias(CourseUserRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_USER_ID);
        $query .= ' = ' . $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_ID);

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

    /**
     * Retrieves all groups directly subscribed to a course.
     *
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_property order
     */
    function retrieve_groups_directly_subscribed_to_course($condition = null, $offset = null, $count = null, $order_property = null)
    {
        // datamanagers
        $group_dm = GroupDataManager :: get_instance();

        // - SELECT
        $query .= 'SELECT ';

        // -- id
        $query .= $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_ID);
        $query .= ', ';

        // -- name
        $query .= $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_NAME);
        $query .= ', ';

        // -- description
        $query .= $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_DESCRIPTION);
        $query .= ', ';

        // -- code
        $query .= $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_CODE);
        $query .= ', ';

        // -- record_subscription_status
        $query .= $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_STATUS);

        // - FROM course_group_relation
        $query .= ' FROM ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseGroupRelation :: get_table_name());

        // - JOIN user
        $query .= ' JOIN ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                Group :: get_table_name());
        $query .= ' ON ' . $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_GROUP_ID);
        $query .= ' = ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_ID);

        return $this->retrieve_object_set($query, Group :: get_table_name(), $condition, $offset, $count,
                $order_property, Group :: CLASS_NAME);
    }

    /**
     * Counts all groups directly subscribed to a course.
     *
     * @param $condition Condition
     */
    function count_groups_directly_subscribed_to_course($condition = null)
    {
        // datamanagers
        $group_dm = GroupDataManager :: get_instance();

        // - SELECT
        $query .= 'SELECT COUNT(1)';

        // - FROM course_user_relation
        $query .= ' FROM ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseGroupRelation :: get_table_name());

        // - JOIN user
        $query .= ' JOIN ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                Group :: get_table_name());
        $query .= ' ON ' . $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_GROUP_ID);
        $query .= ' = ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_ID);

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

    /**
     * Retrieves all users not directly subscribed to a course, users that are subscribed through groups are still shown
     * in this list. The course_id is not given in the condition statement because it is used in an advanced JOIN that
     * replaces a NOT EXITS. Including the course_id in the condition statement could result in invalid queries.
     *
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_property order
     */
    function retrieve_users_not_subscribed_to_course($course_id, $condition = null, $offset = null, $count = null,
            $order_property = null)
    {
        // datamanagers
        $user_dm = UserDataManager :: get_instance();
        $user_table = $user_dm->get_table_name(User :: get_table_name());
        $user_table_alias = $user_dm->get_alias(User :: get_table_name());
        $course_user_relation_table = $this->escape_table_name(CourseUserRelation :: get_table_name());
        $course_user_relation_table_alias = $user_dm->get_alias(CourseUserRelation :: get_table_name());

        // - SELECT
        // $query .= 'SELECT ';

        $properties = array(User :: PROPERTY_ID, User :: PROPERTY_OFFICIAL_CODE, User :: PROPERTY_AUTH_SOURCE,
                User :: PROPERTY_LASTNAME, User :: PROPERTY_FIRSTNAME, User :: PROPERTY_USERNAME, User :: PROPERTY_EMAIL,
                User :: PROPERTY_STATUS);

        $esccaped_properties = array();

        foreach ($properties as $property)
        {
            $escaped_properties[] = $user_dm->escape_column_name($property, $user_table_alias);
        }

        $query_builder = $user_dm->get_connection()->createQueryBuilder();
        $query_builder->select(implode(',', $escaped_properties));

        // // -- id
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_ID);
        // $query .= ', ';
        //
        // // -- official code
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_OFFICIAL_CODE);
        // $query .= ', ';
        //
        // // -- auth source
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_AUTH_SOURCE);
        // $query .= ', ';
        //
        // // -- lastname
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_LASTNAME);
        // $query .= ', ';
        //
        // // -- firstname
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_FIRSTNAME);
        // $query .= ', ';
        //
        // // -- username
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_USERNAME);
        // $query .= ', ';
        //
        // // -- email
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_EMAIL);
        // $query .= ', ';
        //
        // // -- status
        // $query .= $user_dm->get_alias(User :: get_table_name()) . '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_STATUS);
        //
        // // - FROM course_user_relation
        // $query .= ' FROM ' . $user_dm->escape_table_name(User ::
        // get_table_name()) . ' AS ' .
        // $user_dm->get_alias(User :: get_table_name());

        $query_builder->from($user_table, $user_table_alias);

        // - JOIN user
        // $query .= ' LEFT JOIN ' . $this->escape_table_name(CourseUserRelation
        // :: get_table_name()) . ' AS ' .
        // $user_dm->get_alias(CourseUserRelation :: get_table_name());
        // $query .= ' ON ' . $user_dm->get_alias(User :: get_table_name()) .
        // '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_ID);
        // $query .= ' = ' . $user_dm->get_alias(CourseUserRelation ::
        // get_table_name()) . '.' .
        // $this->escape_column_name(CourseUserRelation :: PROPERTY_USER_ID);
        // $query .= ' AND ' . $user_dm->get_alias(CourseUserRelation ::
        // get_table_name()) . '.' .
        // $this->escape_column_name(CourseUserRelation :: PROPERTY_COURSE_ID);
        // $query .= ' = ' . $course_id;

        $join_condition = $user_dm->escape_column_name(User :: PROPERTY_ID, $user_table_alias);
        $join_condition .= ' = ' . $this->escape_column_name(CourseUserRelation :: PROPERTY_USER_ID,
                $course_user_relation_table_alias);
        $join_condition .= ' AND ' . $this->escape_column_name(CourseUserRelation :: PROPERTY_COURSE_ID,
                $course_user_relation_table_alias);
        $join_condition .= ' = ' . $course_id;

        $query_builder->leftJoin($user_table_alias, $course_user_relation_table, $course_user_relation_table_alias,
                $join_condition);

        // additional where condition to mimic NOT EXISTS
        $not_exist_mimic = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, null,
                CourseUserRelation :: get_table_name());

        // reconstruct conditions
        $conditions = array();
        $conditions[] = $not_exist_mimic;
        if (isset($condition))
        {
            $conditions[] = $condition;
        }
        $condition = new AndCondition($conditions);

        // retrieve
        return $user_dm->retrieve_object_set($query_builder, User :: get_table_name(), $condition, $offset, $count,
                $order_property, User :: CLASS_NAME);
    }

    /**
     * Counts all users not directly subscribed to a course, users that are subscribed through groups are still counted.
     * The course_id is not given in the condition statement because it is used in an advanced JOIN that replaces a NOT
     * EXITS. Including the course_id in the condition statement could result in invalid queries.
     *
     * @param $condition Condition
     */
    function count_users_not_subscribed_to_course($course_id, $condition = null)
    {
        $query_builder = new QueryBuilder();

        // datamanagers
        $user_dm = UserDataManager :: get_instance();

        // Table definitions
        $user_table = $user_dm->escape_table_name(User :: get_table_name());
        $user_table_alias = $user_dm->get_alias(User :: get_table_name());
        $course_user_relation_table = $this->escape_table_name(CourseUserRelation :: get_table_name());
        $course_user_relation_table_alias = $user_dm->get_alias(CourseUserRelation :: get_table_name());

        // - SELECT
        // $query .= 'SELECT COUNT(1)';
        $query_builder->select('COUNT(1)');

        // - FROM course_user_relation
        // $query .= ' FROM ' . $user_dm->escape_table_name(User ::
        // get_table_name()) . ' AS ' .
        // $user_dm->get_alias(User :: get_table_name());
        $query_builder->from($user_table, $user_table_alias);

        // - JOIN user
        // $query .= ' LEFT JOIN ' . $this->escape_table_name(CourseUserRelation
        // :: get_table_name()) . ' AS ' .
        // $user_dm->get_alias(CourseUserRelation :: get_table_name());
        // $query .= ' ON ' . $user_dm->get_alias(User :: get_table_name()) .
        // '.' .
        // $user_dm->escape_column_name(User :: PROPERTY_ID);
        // $query .= ' = ' . $user_dm->get_alias(CourseUserRelation ::
        // get_table_name()) . '.' .
        // $this->escape_column_name(CourseUserRelation :: PROPERTY_USER_ID);
        // $query .= ' AND ' . $user_dm->get_alias(CourseUserRelation ::
        // get_table_name()) . '.' .
        // $this->escape_column_name(CourseUserRelation :: PROPERTY_COURSE_ID);
        // $query .= ' = ' . $course_id;

        $join_condition = $user_dm->escape_column_name(User :: PROPERTY_ID, $user_table_alias);
        $join_condition .= ' = ' . $this->escape_column_name(CourseUserRelation :: PROPERTY_USER_ID,
                $course_user_relation_table_alias);
        $join_condition .= ' AND ' . $this->escape_column_name(CourseUserRelation :: PROPERTY_COURSE_ID,
                $course_user_relation_table_alias);
        $join_condition .= ' = ' . $course_id;

        $query_builder->leftJoin($user_table_alias, $course_user_relation_table, $course_user_relation_table_alias,
                $join_condition);

        // additional where condition to mimic NOT EXISTS
        $not_exist_mimic = new EqualityCondition(CourseUserRelation :: PROPERTY_USER_ID, null,
                CourseUserRelation :: get_table_name());

        // reconstruct conditions
        $conditions = array();
        $conditions[] = $not_exist_mimic;
        if (isset($condition))
        {
            $conditions[] = $condition;
        }
        $condition = new AndCondition($conditions);

        // retrieve
        return $user_dm->count_result_set($query_builder, User :: get_table_name(), $condition);
    }

    /**
     * Retrieves all the platform groups subscribed in the given courses
     *
     * @param $course_ids <type>
     * @return Mdb2ResultSet
     */
    function retrieve_all_subscribed_platform_groups($course_ids)
    {
        $condition = new InCondition(CourseGroupRelation :: PROPERTY_COURSE_ID, $course_ids);
        $group_dm = GroupDataManager :: get_instance();

        $group_rel_course_alias = $this->get_alias(CourseGroupRelation :: get_table_name());
        $group_alias = $group_dm->get_alias(Group :: get_table_name());

        $query = 'SELECT ' . $group_dm->escape_column_name(Group :: PROPERTY_LEFT_VALUE, $table_alias) . ', ' . $group_dm->escape_column_name(
                Group :: PROPERTY_RIGHT_VALUE, $table_alias);
        $query .= ' FROM ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' as ' . $group_rel_course_alias;
        $query .= ' JOIN ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' as ' . $group_alias . ' on ' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_GROUP_ID, $group_rel_course_alias) . ' = ' . $group_dm->escape_column_name(
                Group :: PROPERTY_ID, $group_alias);

        $direct_subscribed_groups = $this->retrieve_object_set($query, CourseGroupRelation :: get_table_name(),
                $condition, null, null, null, Group :: CLASS_NAME);

        while ($direct_subscribed_group = $direct_subscribed_groups->next_result())
        {
            $and_conditions = array();
            $and_conditions[] = new InequalityCondition(Group :: PROPERTY_LEFT_VALUE,
                    InequalityCondition :: GREATER_THAN_OR_EQUAL, $direct_subscribed_group->get_left_value(),
                    $group_alias, true);

            $and_conditions[] = new InequalityCondition(Group :: PROPERTY_RIGHT_VALUE,
                    InequalityCondition :: LESS_THAN_OR_EQUAL, $direct_subscribed_group->get_right_value(), $group_alias,
                    true);

            $sub_conditions[] = new AndCondition($and_conditions);
        }
        $condition = new OrCondition($sub_conditions);

        $query = 'SELECT *';
        $query .= ' FROM ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' as ' . $group_alias;
        return $this->retrieve_object_set($query, Group :: get_table_name(), $condition, null, null, null,
                Group :: CLASS_NAME);
    }

    /**
     * Retrieves all users subscribed to a course, including the users in all subscribed (sub-)groups. The course_id is
     * not given in the condition statement because it is used to select the subgroups BEFORE running the actual select.
     * Including the course_id in the condition statement could result in invalid queries.
     *
     * @param $course_id int The course for which we want to retrieve the users
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_property order
     */
    function retrieve_all_course_users($course_id, $condition = null, $offset = null, $count = null, $order_property = null)
    {
        // datamanagers
        $user_dm = UserDataManager :: get_instance();
        $group_dm = GroupDataManager :: get_instance();

        // ////////////////////////////////////////////////////////////////////
        // GET RELATED GROUPS
        // --------------------------------------------------------------------

        // SELECT
        $tree_values_query = 'SELECT ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_LEFT_VALUE);
        $tree_values_query .= ', ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_RIGHT_VALUE);
        $tree_values_query .= ', ' . $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_STATUS);

        // FROM course_group_relation
        $tree_values_query .= ' FROM ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseGroupRelation :: get_table_name());

        // JOIN group
        $tree_values_query .= ' JOIN ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                Group :: get_table_name());
        $tree_values_query .= ' ON ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_ID);
        $tree_values_query .= ' = ' . $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_GROUP_ID);

        // WHERE
        $tree_values_condition = new EqualityCondition(CourseGroupRelation :: PROPERTY_COURSE_ID, $course_id,
                $this->get_alias(CourseGroupRelation :: get_table_name()), true);

        // RETRIEVE
        $direct_groups_with_tree_values = $group_dm->retrieve_object_set($tree_values_query, Group :: get_table_name(),
                $tree_values_condition, null, null, null, Group :: CLASS_NAME);

        // ////////////////////////////////////////////////////////////////////
        // SELECT RESULT + STATUS
        // 1 = individual, 2 = group --> sum(status)>=3? both user and group
        // --------------------------------------------------------------------
        $query = 'SELECT ';

        // -- id
        $query .= 'result.' . $user_dm->escape_column_name(User :: PROPERTY_ID);
        $query .= ', ';

        // -- official code
        $query .= 'result.' . $user_dm->escape_column_name(User :: PROPERTY_OFFICIAL_CODE);
        $query .= ', ';

        // -- lastname
        $query .= 'result.' . $user_dm->escape_column_name(User :: PROPERTY_LASTNAME);
        $query .= ', ';

        // -- firstname
        $query .= 'result.' . $user_dm->escape_column_name(User :: PROPERTY_FIRSTNAME);
        $query .= ', ';

        // -- username
        $query .= 'result.' . $user_dm->escape_column_name(User :: PROPERTY_USERNAME);
        $query .= ', ';

        // -- email
        $query .= 'result.' . $user_dm->escape_column_name(User :: PROPERTY_EMAIL);
        $query .= ', ';

        // -- status
        $query .= 'result.' . $user_dm->escape_column_name(User :: PROPERTY_STATUS);

        // -->-- subscription status
        $query .= ', MIN(result.record_subscription_status) AS "subscription_status"';

        // -->-- subscription type
        $query .= ', SUM(result.record_subscription_type) AS "subscription_type" FROM (';

        // ////////////////////////////////////////////////////////////////////
        // CONSTRUCT RESULT
        // 1. individual users
        // --------------------------------------------------------------------

        // - SELECT
        $query .= ' SELECT ';

        // -- id
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_ID);
        $query .= ', ';

        // -- official code
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_OFFICIAL_CODE);
        $query .= ', ';

        // -- lastname
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_LASTNAME);
        $query .= ', ';

        // -- firstname
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_FIRSTNAME);
        $query .= ', ';

        // -- username
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_USERNAME);
        $query .= ', ';

        // -- email
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_EMAIL);
        $query .= ', ';

        // -- status
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_STATUS);
        $query .= ', ';

        // - record_subscription_status
        $query .= $this->get_alias(CourseUserRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_STATUS);
        $query .= ' AS "record_subscription_status"';
        $query .= ', ';

        // -- record_subscription_type
        $query .= '1 AS "record_subscription_type"';

        // - FROM course_user_relation
        $query .= ' FROM ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseUserRelation :: get_table_name());

        // - JOIN user
        $query .= ' JOIN ' . $user_dm->escape_table_name(User :: get_table_name()) . ' AS ' . $user_dm->get_alias(
                User :: get_table_name());
        $query .= ' ON ' . $this->get_alias(CourseUserRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_USER_ID);
        $query .= ' = ' . $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_ID);

        // WHERE
        $translator = ConditionTranslator :: factory($this);
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_id,
                CourseUserRelation :: get_table_name());
        if (isset($condition))
        {
            $conditions[] = $condition;
        }
        $direct_user_condition = new AndCondition($conditions);
        $query .= $translator->render_query($direct_user_condition);

        // ////////////////////////////////////////////////////////////////////
        // CONSTRUCT RESULT
        // 2a. union with groups
        // --------------------------------------------------------------------
        if ($direct_groups_with_tree_values->size() > 0)
        {
            $query .= ' UNION';

            // - SELECT
            $query .= ' SELECT ';

            // -- id
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_ID);
            $query .= ', ';

            // -- official code
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_OFFICIAL_CODE);
            $query .= ', ';

            // -- lastname
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_LASTNAME);
            $query .= ', ';

            // -- firstname
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_FIRSTNAME);
            $query .= ', ';

            // -- username
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_USERNAME);
            $query .= ', ';

            // -- email
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_EMAIL);
            $query .= ', ';

            // -- status
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_STATUS);
            $query .= ', ';

            // -- record_subscription_status
            $query .= ' CASE ';

            $direct_group_conditions = array();
            while ($group = $direct_groups_with_tree_values->next_result())
            {
                $and_conditions = array();
                $and_conditions[] = new InequalityCondition(Group :: PROPERTY_LEFT_VALUE,
                        InequalityCondition :: GREATER_THAN_OR_EQUAL, $group->get_left_value());
                $and_conditions[] = new InequalityCondition(Group :: PROPERTY_RIGHT_VALUE,
                        InequalityCondition :: LESS_THAN_OR_EQUAL, $group->get_right_value());
                $direct_group_conditions[] = new AndCondition($and_conditions);

                $query .= ' WHEN ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                        Group :: PROPERTY_LEFT_VALUE);
                $query .= ' >= ' . $group->get_left_value();
                $query .= ' AND ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                        Group :: PROPERTY_RIGHT_VALUE);
                $query .= ' <= ' . $group->get_right_value();
                $query .= ' THEN ' . $group->get_optional_property(CourseGroupRelation :: PROPERTY_STATUS);
            }
            $query .= ' END ';
            $query .= ' AS "record_subscription_status"';

            $query .= ', ';

            // -- subscription_type
            $query .= '2 AS "record_subscription_type"';

            // - FROM group
            $query .= ' FROM ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                    Group :: get_table_name());

            // - JOIN group_rel_user
            $query .= ' JOIN ' . $group_dm->escape_table_name(GroupRelUser :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                    GroupRelUser :: get_table_name());
            $query .= ' ON ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                    Group :: PROPERTY_ID);
            $query .= ' = ' . $group_dm->get_alias(GroupRelUser :: get_table_name()) . '.' . $this->escape_column_name(
                    GroupRelUser :: PROPERTY_GROUP_ID);

            // - JOIN user
            $query .= ' JOIN ' . $user_dm->escape_table_name(User :: get_table_name()) . ' AS ' . $user_dm->get_alias(
                    User :: get_table_name());
            $query .= ' ON ' . $group_dm->get_alias(GroupRelUser :: get_table_name()) . '.' . $this->escape_column_name(
                    GroupRelUser :: PROPERTY_USER_ID);
            $query .= ' = ' . $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_ID);

            // 2b. construct WHERE
            $or_condition = new OrCondition($direct_group_conditions);
            $user_conditions = array();
            $user_conditions[] = $or_condition;
            if (isset($condition))
            {
                $user_conditions[] = $condition;
            }
            $group_user_condition = new AndCondition($user_conditions);
            $translator = ConditionTranslator :: factory($this);
            $query .= $translator->render_query($group_user_condition);
        }

        // ////////////////////////////////////////////////////////////////////
        // 3. end subquery
        // --------------------------------------------------------------------
        $query .= ') AS result ';

        // ////////////////////////////////////////////////////////////////////
        // COMPLETE QUERY
        // --------------------------------------------------------------------

        // GROUP BY
        $query .= ' GROUP BY result.' . $user_dm->escape_column_name(User :: PROPERTY_USERNAME);

        // ORDER BY
        $orders = array();
        if (is_null($order_property))
        {
            $order_property = array();
        }
        elseif (! is_array($order_property))
        {
            $order_property = array($order_property);
        }
        foreach ($order_property as $order)
        {
            if ($order)
            {
                // subscription_status and subscription_type are calculated
                // fields, mysql doesn't allow table bames in front of them...
                switch ($order->get_property())
                {
                    case 'subscription_status' :
                    case 'subscription_type' :
                        $orders[] = $this->escape_column_name($order->get_property()) . ' ' . ($order->get_direction() == SORT_DESC ? 'DESC' : 'ASC');
                        break;
                    default :
                        $orders[] = $this->escape_column_name($order->get_property(), 'result') . ' ' . ($order->get_direction() == SORT_DESC ? 'DESC' : 'ASC');
                        break;
                }
            }
        }
        if (count($orders))
        {
            $query .= ' ORDER BY ' . implode(', ', $orders);
        }

        // LIMIT
        if (isset($count))
        {
            $query .= ' LIMIT ';
            $query .= isset($offset) ? $offset : 0;
            $query .= ',';
            $query .= $count;
        }
        DataClassCache :: truncate(User :: class_name());

        return new Mdb2ResultSet($this, $this->query($query), User :: CLASS_NAME);
    }

    /**
     * Counts all users subscribed to a course, including the users in all subscribed (sub-)groups. The course_id is not
     * given in the condition statement because it is used to select the subgroups BEFORE running the actual select.
     * Including the course_id in the condition statement could result in invalid queries.
     *
     * @param $course_id int The course for which we want to retrieve the users
     * @param $condition Condition
     */
    function count_all_course_users($course_id, $condition = null)
    {
        // datamanagers
        $user_dm = UserDataManager :: get_instance();
        $group_dm = GroupDataManager :: get_instance();

        // ////////////////////////////////////////////////////////////////////
        // GET RELATED GROUPS
        // --------------------------------------------------------------------

        // SELECT
        $tree_values_query = 'SELECT ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_LEFT_VALUE);
        $tree_values_query .= ', ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_RIGHT_VALUE);

        // FROM course_group_relation
        $tree_values_query .= ' FROM ' . $this->escape_table_name(CourseGroupRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseGroupRelation :: get_table_name());

        // JOIN group
        $tree_values_query .= ' JOIN ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                Group :: get_table_name());
        $tree_values_query .= ' ON ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                Group :: PROPERTY_ID);
        $tree_values_query .= ' = ' . $this->get_alias(CourseGroupRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_GROUP_ID);

        // WHERE
        $tree_values_condition = new EqualityCondition(CourseGroupRelation :: PROPERTY_COURSE_ID, $course_id,
                $this->get_alias(CourseGroupRelation :: get_table_name()), true);

        // RETRIEVE
        $direct_groups_with_tree_values = $group_dm->retrieve_object_set($tree_values_query, Group :: get_table_name(),
                $tree_values_condition, null, null, null, Group :: CLASS_NAME);

        // ////////////////////////////////////////////////////////////////////
        // SELECT RESULT + STATUS
        // 1 = individual, 2 = group --> sum(status)>=3? both user and group
        // --------------------------------------------------------------------
        $query = 'SELECT COUNT(DISTINCT result.username) AS "count" FROM (';

        // ////////////////////////////////////////////////////////////////////
        // CONSTRUCT RESULT
        // 1. individual users
        // --------------------------------------------------------------------

        // - SELECT
        $query .= 'SELECT ';

        // -- username
        $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_USERNAME);

        // - FROM course_user_relation
        $query .= ' FROM ' . $this->escape_table_name(CourseUserRelation :: get_table_name()) . ' AS ' . $this->get_alias(
                CourseUserRelation :: get_table_name());

        // - JOIN user
        $query .= ' JOIN ' . $user_dm->escape_table_name(User :: get_table_name()) . ' AS ' . $user_dm->get_alias(
                User :: get_table_name());
        $query .= ' ON ' . $this->get_alias(CourseUserRelation :: get_table_name()) . '.' . $this->escape_column_name(
                CourseUserRelation :: PROPERTY_USER_ID);
        $query .= ' = ' . $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                User :: PROPERTY_ID);

        // WHERE
        $translator = ConditionTranslator :: factory($this);
        $conditions = array();
        $conditions[] = new EqualityCondition(CourseUserRelation :: PROPERTY_COURSE_ID, $course_id,
                CourseUserRelation :: get_table_name());
        if (isset($condition))
        {
            $conditions[] = $condition;
        }
        $direct_user_condition = new AndCondition($conditions);
        $query .= $translator->render_query($direct_user_condition);

        // ////////////////////////////////////////////////////////////////////
        // CONSTRUCT RESULT
        // 2a. union with groups
        // --------------------------------------------------------------------
        if ($direct_groups_with_tree_values->size() > 0)
        {
            $query .= ' UNION';

            // - SELECT
            $query .= ' SELECT ';

            // -- username
            $query .= $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_USERNAME);

            // - FROM group
            $query .= ' FROM ' . $group_dm->escape_table_name(Group :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                    Group :: get_table_name());

            // - JOIN group_rel_user
            $query .= ' JOIN ' . $group_dm->escape_table_name(GroupRelUser :: get_table_name()) . ' AS ' . $group_dm->get_alias(
                    GroupRelUser :: get_table_name());
            $query .= ' ON ' . $group_dm->get_alias(Group :: get_table_name()) . '.' . $group_dm->escape_column_name(
                    Group :: PROPERTY_ID);
            $query .= ' = ' . $group_dm->get_alias(GroupRelUser :: get_table_name()) . '.' . $this->escape_column_name(
                    GroupRelUser :: PROPERTY_GROUP_ID);

            // - JOIN user
            $query .= ' JOIN ' . $user_dm->escape_table_name(User :: get_table_name()) . ' AS ' . $user_dm->get_alias(
                    User :: get_table_name());
            $query .= ' ON ' . $group_dm->get_alias(GroupRelUser :: get_table_name()) . '.' . $this->escape_column_name(
                    GroupRelUser :: PROPERTY_USER_ID);
            $query .= ' = ' . $user_dm->get_alias(User :: get_table_name()) . '.' . $user_dm->escape_column_name(
                    User :: PROPERTY_ID);

            // 2b. construct WHERE
            $direct_group_conditions = array();
            while ($group = $direct_groups_with_tree_values->next_result())
            {
                $and_conditions = array();
                $and_conditions[] = new InequalityCondition(Group :: PROPERTY_LEFT_VALUE,
                        InequalityCondition :: GREATER_THAN_OR_EQUAL, $group->get_left_value());
                $and_conditions[] = new InequalityCondition(Group :: PROPERTY_RIGHT_VALUE,
                        InequalityCondition :: LESS_THAN_OR_EQUAL, $group->get_right_value());
                $direct_group_conditions[] = new AndCondition($and_conditions);
            }
            $or_condition = new OrCondition($direct_group_conditions);
            
            if (isset($condition))
            {
                $group_condition = new AndCondition(array($or_condition, $condition));
            }
            else
            {
                $group_condition = $or_condition;
            }

            $translator = ConditionTranslator :: factory($this);
            $query .= $translator->render_query($group_condition);
        }

        // ////////////////////////////////////////////////////////////////////
        // 3. end subquery
        // --------------------------------------------------------------------
        $query .= ') AS result ';

        $record = $this->retrieve_row($query, User :: get_table_name());
        $record_values = array_values($record);
        return $record_values[0];
    }

    function retrieve_platform_groups_from_user($user_id, $course_id, $offset = null, $count = null, $order_property = null)
    {
        $group_manager = GroupDataManager :: get_instance();
        $group_alias = $group_manager->get_alias(Group :: get_table_name());
        $group_rel_user_alias = $group_manager->get_alias(GroupRelUser :: get_table_name());
        $course_group_relation_alias = $this->get_alias(CourseGroupRelation :: get_table_name());
        $query = 'SELECT ' . $group_alias . '.' . Group :: PROPERTY_ID . ', ' . Group :: PROPERTY_NAME . ' FROM ' . $group_manager->escape_table_name(
                Group :: get_table_name()) . ' AS ' . $group_alias . ' JOIN ' . $group_manager->escape_table_name(
                GroupRelUser :: get_table_name()) . ' AS ' . $group_rel_user_alias . ' ON ' . $this->escape_column_name(
                Group :: PROPERTY_ID, $group_alias) . ' = ' . $this->escape_column_name(
                GroupRelUser :: PROPERTY_GROUP_ID, $group_rel_user_alias) . ' JOIN ' . $this->escape_table_name(
                CourseGroupRelation :: get_table_name()) . ' AS ' . $course_group_relation_alias . ' ON ' . $this->escape_column_name(
                Group :: PROPERTY_ID, $group_alias) . ' = ' . $this->escape_column_name(
                CourseGroupRelation :: PROPERTY_GROUP_ID, $course_group_relation_alias) . ' WHERE ' . $this->escape_column_name(
                GroupRelUser :: PROPERTY_USER_ID, $group_rel_user_alias) . ' = ' . $user_id;
        return $this->retrieve_object_set($query, Group :: get_table_name(), null, $offset, $count, $order_property,
                Group :: CLASS_NAME);
    }

    function retrieve_submissions_by_submitter_type($publication_id, $submitter_type, $class_name = null)
    {
        $tracking_data_manager = TrackingDataManager :: get_instance();
        $query = 'SELECT ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_ID . ', min(' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_DATE_SUBMITTED . ') AS first_date, ' . 'max(' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_DATE_SUBMITTED . ') AS last_date, ' . 'count(*) AS count, ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_TYPE . ' FROM ' . $tracking_data_manager->escape_table_name(
                WeblcmsAssignmentSubmissionsTracker :: get_table_name()) . ' WHERE ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_PUBLICATION_ID . ' = ' . $publication_id . ' AND ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_TYPE . ' = ' . $submitter_type . ' GROUP BY ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_ID;
        return $this->retrieve_object_set($query, WeblcmsAssignmentSubmissionsTracker :: get_table_name(), null, null,
                null, null, $class_name);
    }

    function count_submitter_feedbacks($publication_id, $submitter_type, $class_name = null)
    {
        $tracking_data_manager = TrackingDataManager :: get_instance();
        $submission_alias = $this->get_alias(WeblcmsAssignmentSubmissionsTracker :: get_table_name());
        $feedback_alias = $this->get_alias(WeblcmsSubmissionFeedbackTracker :: get_table_name());
        $query = 'SELECT ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_ID . ', count(*) AS count ' . ' FROM ' . $tracking_data_manager->escape_table_name(
                WeblcmsAssignmentSubmissionsTracker :: get_table_name()) . ' AS ' . $submission_alias . ' JOIN ' . $tracking_data_manager->escape_table_name(
                WeblcmsSubmissionFeedbackTracker :: get_table_name()) . ' AS ' . $feedback_alias . ' ON ' . $this->escape_column_name(
                WeblcmsAssignmentSubmissionsTracker :: PROPERTY_ID, $submission_alias) . " = " . $this->escape_column_name(
                WeblcmsSubmissionFeedbackTracker :: PROPERTY_SUBMISSION_ID, $feedback_alias) . ' WHERE ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_PUBLICATION_ID . ' = ' . $publication_id . ' AND ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_TYPE . ' = ' . $submitter_type . ' GROUP BY ' . WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_ID;
        return $this->retrieve_object_set($query, WeblcmsAssignmentSubmissionsTracker :: get_table_name(), null, null,
                null, null, $class_name);
    }

    function retrieve_introduction($condition)
    {
        $rdm = RepositoryDataManager :: get_instance();

        $publication_table_alias = $this->get_alias(ContentObjectPublication :: get_table_name());
        $content_object_table_alias = $rdm->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 . ' FROM ' . $this->escape_table_name(
                ContentObjectPublication :: get_table_name()) . ' AS ' . $publication_table_alias;
        $query .= ' JOIN ' . $rdm->escape_table_name(ContentObject :: get_table_name()) . ' AS ' . $content_object_table_alias . ' ON ' . $rdm->escape_column_name(
                ContentObject :: PROPERTY_ID, $content_object_table_alias) . '=' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $publication_alias);

        $conditions[] = new EqualityCondition(ContentObject :: PROPERTY_TYPE, Introduction :: get_type_name(),
                $content_object_table_alias, true);
        $conditions[] = $condition;

        $condition = new AndCondition($conditions);

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

    /**
     * Retrieves your publications AND the publications you have the edit right for Otherwise there is no way to view
     * 'invisible' publications if you're not the owner, but you do have the edit right
     *
     * @param $condition Condition
     * @param $order_by Array
     * @param $offset <type>
     * @param $max_objects <type>
     * @return Mdb2ResultSet
     */
    function retrieve_my_publications($parent_location, $entities, $condition = null, $order_by = array(), $offset = 0,
            $max_objects = -1, $user_id = null)
    {
        $rdm = RepositoryDataManager :: get_instance();

        $publication_table_alias = $this->get_alias(ContentObjectPublication :: get_table_name());
        $content_object_table_alias = $rdm->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 . ' FROM ' . $this->escape_table_name(
                ContentObjectPublication :: get_table_name()) . ' AS ' . $publication_table_alias;
        $query .= ' JOIN ' . $rdm->escape_table_name(ContentObject :: get_table_name()) . ' AS ' . $content_object_table_alias . ' ON ' . $rdm->escape_column_name(
                ContentObject :: PROPERTY_ID, $content_object_table_alias) . '=' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $publication_alias);

        $granted_location_ids = WeblcmsRights :: get_instance()->get_identifiers_with_right_granted(
                WeblcmsRights :: EDIT_RIGHT, WeblcmsManager :: APPLICATION_NAME, $parent_location,
                WeblcmsRights :: TYPE_PUBLICATION, $user_id, $entities);

        if (! empty($granted_location_ids))
        {
            $conditions[] = $condition;
            $conditions[] = new InCondition(ContentObjectPublication :: PROPERTY_ID, $granted_location_ids);

            $condition = new OrCondition($conditions);
        }

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

    function retrieve_all_publications($condition = null, $order_by = array(), $offset = null, $max_objects = null)
    {
        $rdm = RepositoryDataManager :: get_instance();

        $publication_table_alias = $this->get_alias(ContentObjectPublication :: get_table_name());
        $content_object_table_alias = $rdm->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 . ' FROM ' . $this->escape_table_name(
                ContentObjectPublication :: get_table_name()) . ' AS ' . $publication_table_alias;
        $query .= ' JOIN ' . $rdm->escape_table_name(ContentObject :: get_table_name()) . ' AS ' . $content_object_table_alias . ' ON ' . $rdm->escape_column_name(
                ContentObject :: PROPERTY_ID, $content_object_table_alias) . '=' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $publication_alias);

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

    function count_my_publications($parent_location, $entities, $condition = null, $user_id = null)
    {
        $rdm = RepositoryDataManager :: get_instance();

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

        $query = 'SELECT count(1) FROM ' . $this->escape_table_name(ContentObjectPublication :: get_table_name()) . ' AS ' . $publication_table_alias;
        $query .= ' JOIN ' . $rdm->escape_table_name(ContentObject :: get_table_name()) . ' AS ' . $content_object_table_alias . ' ON ' . $rdm->escape_column_name(
                ContentObject :: PROPERTY_ID, $content_object_table_alias) . '=' . $this->escape_column_name(
                ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID, $publication_alias);

        $granted_location_ids = WeblcmsRights :: get_instance()->get_identifiers_with_right_granted(
                WeblcmsRights :: EDIT_RIGHT, WeblcmsManager :: APPLICATION_NAME, $parent_location,
                WeblcmsRights :: TYPE_PUBLICATION, $user_id, $entities);

        if (! empty($granted_location_ids))
        {
            $conditions[] = $condition;
            $conditions[] = new InCondition(ContentObjectPublication :: PROPERTY_ID, $granted_location_ids);

            $condition = new OrCondition($conditions);
        }

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

    /**
     *
     * @param $parent_location <Location>
     * @param $entities <Array>
     * @param $order_by <type>
     * @param $offset <type>
     * @param $max_objects <type>
     * @return <type>
     */
    function retrieve_content_object_publications_with_view_right_granted_in_category_location($parent_location,
            $entities, $condition, $order_by = array(), $offset = 0, $max_objects = -1, $user_id = null)
    {

        $granted_location_ids = WeblcmsRights :: get_instance()->get_identifiers_with_right_granted(1,
                WeblcmsManager :: APPLICATION_NAME, $parent_location, WeblcmsRights :: TYPE_PUBLICATION, $user_id,
                $entities);
        if (empty($granted_location_ids))
        {
            return 0;
        }

        $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);

        $conditions = array();
        $conditions[] = new InCondition(ContentObjectPublication :: PROPERTY_ID, $granted_location_ids);

        if ($condition)
        {
            $conditions[] = $condition;
        }

        $condition = new AndCondition($conditions);

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

    function count_content_object_publications_with_view_right_granted_in_category_location($parent_location, $entities,
            $condition, $user_id = null)
    {
        $granted_location_ids = WeblcmsRights :: get_instance()->get_identifiers_with_right_granted(1,
                WeblcmsManager :: APPLICATION_NAME, $parent_location, WeblcmsRights :: TYPE_PUBLICATION, $user_id,
                $entities);
        if (empty($granted_location_ids))
        {
            return 0;
        }

        $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(1)';
        $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);

        $conditions = array();
        $conditions[] = new InCondition(ContentObjectPublication :: PROPERTY_ID, $granted_location_ids);

        if ($condition)
        {
            $conditions[] = $condition;
        }

        $condition = new AndCondition($conditions);

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

    /**
     * Checks if the publication is published for the given user or a group he is member of. It does NOT check if the
     * user is subscribed to the course.
     *
     * @param $user_id int
     * @param $publication_id int
     * @return boolean
     */
    function is_publication_target_user($user_id, $publication_id, $course_id = null)
    {
        if (is_null($course_id))
        {
            $course_id = $this->retrieve_content_object_publication($publication_id)->get_course_id();
        }

        $target_entities = WeblcmsRights :: get_instance()->get_target_entities(WeblcmsRights :: VIEW_RIGHT,
                WeblcmsManager :: APPLICATION_NAME, $publication_id, WeblcmsRights :: TYPE_PUBLICATION, $course_id,
                WeblcmsRights :: TREE_TYPE_COURSE);

        $user_data_manager = UserDataManager :: get_instance();

        if (count($target_entities) == 0)
        {
            return true;
        }

        foreach ($target_entities as $entity_type => $entity_ids)
        {
            switch ($entity_type)
            {
                case CourseUserEntity :: ENTITY_TYPE :
                    foreach ($entity_ids as $uid)
                    {
                        if ($user_id == $uid)
                        {
                            return true;
                        }
                    }
                    break;

                case CourseGroupEntity :: ENTITY_TYPE :
                    foreach ($entity_ids as $course_group_id)
                    {
                        $course_group = $this->retrieve_course_group($course_group_id);
                        $course_group_users = $this->retrieve_course_group_user_ids($course_group);
                        foreach ($course_group_users as $id)
                        {
                            if ($user_id == $id)
                            {
                                return true;
                            }
                        }
                    }
                    break;

                case CoursePlatformGroupEntity :: ENTITY_TYPE :
                    $group_manager = GroupDataManager :: get_instance();
                    $groups_resultset = $group_manager->retrieve_groups_and_subgroups($entity_ids);
                    while ($group = $groups_resultset->next_result())
                    {
                        $condition = new EqualityCondition(GroupRelUser :: PROPERTY_GROUP_ID, $group->get_id());
                        $group_user_rels = $group_manager->retrieve_group_rel_users($condition);
                        while ($group_user_rel = $group_user_rels->next_result())
                        {
                            if ($user_id == $group_user_rel->get_user_id())
                            {
                                return true;
                            }
                        }
                    }
                    break;

                case 0 :
                    return true;
            }
        }
    }

    function retrieve_course_groups_and_subgroups($group_ids, $condition = null, $offset = null, $count = null, $order_by = null)
    {
        if (count($group_ids) == 0)
        {
            $group_ids[] = - 1;
        }
        $dg_condition = new InCondition(CourseGroup :: PROPERTY_ID, $group_ids);
        $direct_groups = $this->retrieve_course_groups($dg_condition);

        $direct_group_conditions = array();
        while ($group = $direct_groups->next_result())
        {
            $and_conditions = array();
            $and_conditions[] = new InequalityCondition(CourseGroup :: PROPERTY_LEFT_VALUE,
                    InequalityCondition :: GREATER_THAN_OR_EQUAL, $group->get_left_value());
            $and_conditions[] = new InequalityCondition(CourseGroup :: PROPERTY_RIGHT_VALUE,
                    InequalityCondition :: LESS_THAN_OR_EQUAL, $group->get_right_value());
            $and_conditions[] = new EqualityCondition(CourseGroup :: PROPERTY_COURSE_CODE, $group->get_course_code());
            $direct_group_conditions[] = new AndCondition($and_conditions);
        }

        if (count($direct_group_conditions) > 0)
        {
            $group_conditions = array();
            if ($condition)
            {
                $group_conditions[] = $condition;
            }
            $group_conditions[] = new OrCondition($direct_group_conditions);
            $group_conditions[] = new InequalityCondition(CourseGroup :: PROPERTY_PARENT_ID,
                    InequalityCondition :: GREATER_THAN, 0);
            $group_condition = new AndCondition($group_conditions);
        }
        else
        {
            $group_condition = new EqualityCondition(CourseGroup :: PROPERTY_ID, - 1);
        }

        return $this->retrieve_course_groups($group_condition, $offset, $count, $order_by);
    }

    /**
     * **************************************************************************************************************
     * Course Settings Functions *
     * **************************************************************************************************************
     */

    /**
     * Retrieves a course setting with a given id
     *
     * @param $id int
     *
     * @return CourseSetting
     */
    function retrieve_course_setting($id)
    {
        $condition = new EqualityCondition(CourseSetting :: PROPERTY_ID, $id);
        return $this->retrieve_object(CourseSetting :: get_table_name(), $condition);
    }

    /**
     * Retrieves the course_settings joined with the course_tool (name property)
     *
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_by int
     *
     * @return ResultSet
     */
    function retrieve_course_settings_with_tools($condition = null, $offset = null, $count = null, $order_by = null)
    {
        $settings_table = $this->get_table_name(CourseSetting :: get_table_name());
        $settings_alias = $this->get_alias(CourseSetting :: get_table_name());
        $tools_table = $this->get_table_name(CourseTool :: get_table_name());
        $tools_alias = $this->get_alias(CourseTool :: get_table_name());

        $settings_tool_id_column = $this->escape_column_name(CourseSetting :: PROPERTY_TOOL_ID, $settings_alias);
        $tool_id_column = $this->escape_column_name(CourseTool :: PROPERTY_ID, $tools_alias);
        $tool_name_column = $this->escape_column_name(CourseTool :: PROPERTY_NAME, $tools_alias);
        $tool_name_alias = CourseSetting :: PROPERTY_COURSE_TOOL_NAME;

        $query = 'SELECT ' . $settings_alias . '.*, ' . $tool_name_column . ' AS ' . $tool_name_alias;
        $query .= ' FROM ' . $settings_table . ' AS ' . $settings_alias;
        $query .= ' LEFT JOIN ' . $tools_table . ' AS ' . $tools_alias;
        $query .= ' ON ' . $settings_tool_id_column . ' = ' . $tool_id_column;

        $result_set = $this->retrieve_object_set($query, CourseSetting :: get_table_name(), $condition, $offset, $count,
                $order_by);

        DataClassCache :: truncate(CourseSetting :: class_name());

        return $result_set;
    }

    /**
     * Retrieves the course settings joined with the course type setting values
     *
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_by int
     *
     * @return ResultSet
     */
    function retrieve_course_settings_with_course_type_values($condition, $offset, $count, $order_by)
    {
        $query = $this->get_course_settings_with_values_query(CourseTypeRelCourseSetting :: get_table_name(),
                CourseTypeRelCourseSettingValue :: get_table_name(),
                CourseTypeRelCourseSetting :: PROPERTY_COURSE_SETTING_ID,
                CourseTypeRelCourseSettingValue :: PROPERTY_COURSE_TYPE_REL_COURSE_SETTING_ID);

        return $this->retrieve_object_set($query, CourseSetting :: get_table_name(), $condition, $offset, $count,
                $order_by);
    }

    /**
     * Retrieves the course settings joined with the course setting values
     *
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_by int
     *
     * @return ResultSet
     */
    function retrieve_course_settings_with_course_values($condition, $offset, $count, $order_by)
    {
        $query = $this->get_course_settings_with_values_query(CourseRelCourseSetting :: get_table_name(),
                CourseRelCourseSettingValue :: get_table_name(), CourseRelCourseSetting :: PROPERTY_COURSE_SETTING_ID,
                CourseRelCourseSettingValue :: PROPERTY_COURSE_REL_COURSE_SETTING_ID);

        return $this->retrieve_object_set($query, CourseSetting :: get_table_name(), $condition, $offset, $count,
                $order_by);
    }

    /**
     * Retrieves the course settings joined with the course setting default values
     *
     * @param $condition Condition
     * @param $offset int
     * @param $count int
     * @param $order_by int
     *
     * @return ResultSet
     */
    function retrieve_course_settings_with_default_values($condition, $offset, $count, $order_by)
    {
        $settings_table = $this->get_table_name(CourseSetting :: get_table_name());
        $settings_alias = $this->get_alias(CourseSetting :: get_table_name());
        $settings_default_values_table = $this->get_table_name(CourseSettingDefaultValue :: get_table_name());
        $settings_default_values_alias = $this->get_alias(CourseSettingDefaultValue :: get_table_name());

        $value_column = $this->escape_column_name('value', $settings_default_values_alias);

        $course_setting_id_column = $this->escape_column_name(DataClass :: PROPERTY_ID, $settings_alias);
        $course_setting_foreign_column = $this->escape_column_name(
                CourseSettingDefaultValue :: PROPERTY_COURSE_SETTING_ID, $settings_default_values_alias);

        $query = 'SELECT ' . $settings_alias . '.*, ' . $value_column;
        $query .= ' FROM ' . $settings_table . ' AS ' . $settings_alias;
        $query .= ' JOIN ' . $settings_default_values_table . ' AS ' . $settings_default_values_alias;
        $query .= ' ON ' . $course_setting_id_column . '=' . $course_setting_foreign_column;

        return $this->retrieve_object_set($query, CourseSetting :: get_table_name(), $condition, $offset, $count,
                $order_by);
    }

    /**
     * **************************************************************************************************************
     * Course Settings Helper Functions *
     * **************************************************************************************************************
     */

    /**
     * Builds the query to retrieve course settings with a course setting relation table. Returns course settings with
     * their compliant values.
     *
     * @param $course_setting_relation_table String - The table name for the course setting relation table
     * @param $course_setting_relation_value_table String - The table name for the course setting relation value table
     * @param $course_setting_foreign_property String - The foreign property for the course setting id
     * @param $course_setting_relation_foreign_property String - The foreign property for the course setting relation id
     * @return String
     */
    private function get_course_settings_with_values_query($course_setting_relation_table,
            $course_setting_relation_value_table, $course_setting_foreign_property,
            $course_setting_relation_foreign_property)
    {
        $settings_table = $this->get_table_name(CourseSetting :: get_table_name());
        $settings_alias = $this->get_alias(CourseSetting :: get_table_name());

        $course_setting_relation_table_name = $this->get_table_name($course_setting_relation_table);
        $course_setting_relation_table_alias = $this->get_alias($course_setting_relation_table);
        $course_setting_relation_value_table_name = $this->get_table_name($course_setting_relation_value_table);
        $course_setting_relation_value_table_alias = $this->get_alias($course_setting_relation_value_table);

        $course_setting_id_column = $this->escape_column_name(DataClass :: PROPERTY_ID, $settings_alias);
        $course_setting_relation_id_column = $this->escape_column_name(DataClass :: PROPERTY_ID,
                $course_setting_relation_table_alias);

        $value_column = $this->escape_column_name('value', $course_setting_relation_value_table_alias);

        $course_setting_foreign_column = $this->escape_column_name($course_setting_foreign_property,
                $course_setting_relation_table_alias);

        $course_setting_relation_foreign_column = $this->escape_column_name($course_setting_relation_foreign_property,
                $course_setting_relation_value_table_alias);

        $query = 'SELECT ' . $settings_alias . '.*, ' . $value_column;
        $query .= ' FROM ' . $settings_table . ' AS ' . $settings_alias;
        $query .= ' JOIN ' . $course_setting_relation_table_name . ' AS ' . $course_setting_relation_table_alias;
        $query .= ' ON ' . $course_setting_id_column . ' = ' . $course_setting_foreign_column;
        $query .= ' JOIN ' . $course_setting_relation_value_table_name . ' AS ';
        $query .= $course_setting_relation_value_table_alias;
        $query .= ' ON ' . $course_setting_relation_id_column . ' = ' . $course_setting_relation_foreign_column;

        return $query;
    }

    /**
     * **************************************************************************************************************
     * CourseToolRelCourseSection Dataclass Functionality *
     * **************************************************************************************************************
     */

    /**
     * Retrieves a course tool_rel course section with the given id
     *
     * @param $id int
     *
     * @return CourseToolRelCourseSection
     */
    function retrieve_course_tool_rel_course_section($id)
    {
        $condition = new EqualityCondition(CourseToolRelCourseSection :: PROPERTY_ID, $id);
        return $this->retrieve_object(CourseToolRelCourseSection :: get_table_name(), $condition);
    }

    /**
     * Retrieves a list of course tool_rel course section
     *
     * @param $condition \common\libraries\Condition
     * @param $offset int
     * @param $max_objects int
     * @param $order_by \common\libraries\ObjectTableOrder
     *
     * @return \common\libraries\ResultSet <CourseToolRelCourseSection>
     */
    function retrieve_course_tool_rel_course_sections($condition = null, $offset = null, $max_objects = null, $order_by = null)
    {
        return $this->retrieve_objects(CourseToolRelCourseSection :: get_table_name(), $condition, $offset,
                $max_objects, $order_by);
    }

    /**
     * Counts the course tool_rel course section
     *
     * @param $condition \common\libraries\Condition
     *
     * @return int
     */
    function count_course_tool_rel_course_sections($condition = null)
    {
        return $this->count_objects(CourseToolRelCourseSection :: get_table_name(), $condition);
    }

    /**
     * Retrieves the basic data for a raw export of the results in the assessment tool. This method uses dynamic
     * variables and dynamic query construction. For each new table added, care must be taken. Example: Add new table
     * 'x' for class 'X'. The following constants must also be declared. $x_ref = 'x'; $x_table =
     * {DataManager}::get_instance()->escape_table_name(X::get_table_name()); //DataManager must be the appropriate data
     * manager. $x_alias = {DataManager}::get_instance()->get_alias(X::get_table_name()); //DataManager must be the
     * appropriate data manager. Failure to declare them will break the code using dynamic variables. New columns to be
     * added to $select. New tables to be added to $table_aliases using $x_ref. New joins to be added to
     * $join_declarations. Subselects are not catered for.
     *
     * @param $condition type conditions to be applied to the query.
     * @param $offset type the required offset.
     * @param $max_objects type the required number ob rows.
     * @param $order_by type the order in which the rows are to be returned.
     * @return type A result set of WeblcmsQuestionAttemptsTracker objects.
     */
    function retrieve_assessment_raw_export_data($condition = null, $offset = null, $max_objects = null, $order_by = null)
    {
        $rdm = RepositoryDataManager :: get_instance();
        $tdm = TrackingDataManager :: get_instance();
        $udm = UserDataManager :: get_instance();

        // Dynamic variable constants (requires the following variables for each table 'x': x_ref = 'x', x_table,
        // x_alias).
        $ref_table = '_table';
        $ref_alias = '_alias';

        // tracking_weblcms_question_attempts_tracker (\application\weblcms\WeblcmsQuestionAttemptsTracker)
        $twqat_ref = 'twqat';
        $twqat_table = $tdm->escape_table_name(WeblcmsQuestionAttemptsTracker :: get_table_name());
        $twqat_alias = $tdm->get_alias(WeblcmsQuestionAttemptsTracker :: get_table_name());
        $twqat_answer = $tdm->escape_column_name(WeblcmsQuestionAttemptsTracker :: PROPERTY_ANSWER, $twqat_alias);
        $twqat_assessment_attempt_id = $tdm->escape_column_name(
                WeblcmsQuestionAttemptsTracker :: PROPERTY_ASSESSMENT_ATTEMPT_ID, $twqat_alias);
        $twqat_question_cid = $tdm->escape_column_name(WeblcmsQuestionAttemptsTracker :: PROPERTY_QUESTION_CID,
                $twqat_alias);
        $twqat_score = $tdm->escape_column_name(WeblcmsQuestionAttemptsTracker :: PROPERTY_SCORE, $twqat_alias);

        // tracking_weblcms_assessment_attempts_tracker (\application\weblcms\WeblcmsAssessmentAttemptsTracker)
        $twaat_ref = 'twaat';
        $twaat_table = $tdm->escape_table_name(WeblcmsAssessmentAttemptsTracker :: get_table_name());
        $twaat_alias = $tdm->get_alias(WeblcmsAssessmentAttemptsTracker :: get_table_name());
        $twaat_assessment_id = $tdm->escape_column_name(WeblcmsAssessmentAttemptsTracker :: PROPERTY_ASSESSMENT_ID,
                $twaat_alias);
        $twaat_date = $tdm->escape_column_name(WeblcmsAssessmentAttemptsTracker :: PROPERTY_DATE, $twaat_alias);
        $twaat_id = $tdm->escape_column_name(WeblcmsAssessmentAttemptsTracker :: PROPERTY_ID, $twaat_alias);
        $twaat_start_time = $tdm->escape_column_name(WeblcmsAssessmentAttemptsTracker :: PROPERTY_START_TIME,
                $twaat_alias);
        $twaat_total_score = $tdm->escape_column_name(WeblcmsAssessmentAttemptsTracker :: PROPERTY_TOTAL_SCORE,
                $twaat_alias);
        $twaat_total_time = $tdm->escape_column_name(WeblcmsAssessmentAttemptsTracker :: PROPERTY_TOTAL_TIME,
                $twaat_alias);
        $twaat_user_id = $tdm->escape_column_name(WeblcmsAssessmentAttemptsTracker :: PROPERTY_USER_ID, $twaat_alias);

        // user_user (User)
        $uu_ref = 'uu';
        $uu_table = $udm->escape_table_name(User :: get_table_name());
        $uu_alias = $udm->get_alias(User :: get_table_name());
        $uu_firstname = $udm->escape_column_name(User :: PROPERTY_FIRSTNAME, $uu_alias);
        $uu_id = $udm->escape_column_name(User :: PROPERTY_ID, $uu_alias);
        $uu_lastname = $udm->escape_column_name(User :: PROPERTY_LASTNAME, $uu_alias);
        $uu_official_code = $udm->escape_column_name(User :: PROPERTY_OFFICIAL_CODE, $uu_alias);

        // weblcms_content_object_publication (\application\weblcms\WeblcmsContentObjectPublication)
        $wcop_ref = 'wcop';
        $wcop_table = $this->escape_table_name(ContentObjectPublication :: get_table_name());
        $wcop_alias = $this->get_alias(ContentObjectPublication :: get_table_name());
        $wcop_content_object_id = $this->escape_column_name(ContentObjectPublication :: PROPERTY_CONTENT_OBJECT_ID,
                $wcop_alias);
        $wcop_id = $this->escape_column_name(ContentObjectPublication :: PROPERTY_ID, $wcop_alias);

        // repository_content_object (\repository\ContentObject)
        $rco_ref = 'rco';
        $rco_table = $rdm->escape_table_name(ContentObject :: get_table_name());
        $rco_alias = $rdm->get_alias(ContentObject :: get_table_name());
        $rco_id = $rdm->escape_column_name(ContentObject :: PROPERTY_ID, $rco_alias);
        $rco_title = $rdm->escape_column_name(ContentObject :: PROPERTY_TITLE, $rco_alias);

        // repository_complex_content_object_item (\repository\ComplexContentObjectItem)
        $rccoi_ref = 'rccoi';
        $rccoi_table = $rdm->escape_table_name(ComplexContentObjectItem :: get_table_name());
        $rccoi_alias = $rdm->get_alias(ComplexContentObjectItem :: get_table_name());
        $rccoi_display_order = $rdm->escape_column_name(ComplexContentObjectItem :: PROPERTY_DISPLAY_ORDER,
                $rccoi_alias);
        $rccoi_id = $rdm->escape_column_name(ComplexContentObjectItem :: PROPERTY_ID, $rccoi_alias);

        $selects = array();
        $selects[] = $uu_official_code;
        $selects[] = $uu_firstname;
        $selects[] = $uu_lastname;
        $selects[] = $twaat_assessment_id;
        $selects[] = $rco_title;
        $selects[] = $twaat_date;
        $selects[] = $twaat_start_time;
        $selects[] = $twaat_total_time;
        $selects[] = $twaat_total_score;
        $selects[] = $twqat_question_cid;
        $selects[] = $rccoi_display_order;
        $selects[] = $twqat_answer;
        $selects[] = $twqat_score;
        $select = implode(', ', $selects);

        $table_aliases = array();
        $table_aliases[] = $twqat_ref;
        $table_aliases[] = $twaat_ref;
        $table_aliases[] = $uu_ref;
        $table_aliases[] = $wcop_ref;
        $table_aliases[] = $rco_ref;
        $table_aliases[] = $rccoi_ref;

        // Uses dynamic variables.
        $tables = array();
        foreach ($table_aliases as $table_alias)
        {
            $tables[${$table_alias . $ref_table}] = ${$table_alias . $ref_table} . ' AS ' . ${$table_alias . $ref_alias};
        }

        // Join syntax: $join_left_table JOIN $join_right_table ON $join_left_value = $join_right_value.
        // $join_left_table may be null.
        $join_left_table = 'left_table';
        $join_left_value = 'left_value';
        $join_right_table = 'right_table';
        $join_right_value = 'right_value';
        $join_declarations = array();
        $join_declarations[] = array($join_left_table => $tables[$twqat_table],
                $join_right_table => $tables[$twaat_table], $join_left_value => $twqat_assessment_attempt_id,
                $join_right_value => $twaat_id);
        $join_declarations[] = array($join_left_table => null, $join_right_table => $tables[$uu_table],
                $join_left_value => $twaat_user_id, $join_right_value => $uu_id);
        $join_declarations[] = array($join_left_table => null, $join_right_table => $tables[$wcop_table],
                $join_left_value => $twaat_assessment_id, $join_right_value => $wcop_id);
        $join_declarations[] = array($join_left_table => null, $join_right_table => $tables[$rco_table],
                $join_left_value => $wcop_content_object_id, $join_right_value => $rco_id);
        $join_declarations[] = array($join_left_table => null, $join_right_table => $tables[$rccoi_table],
                $join_left_value => $twqat_question_cid, $join_right_value => $rccoi_id);

        $joins = array();
        foreach ($join_declarations as $join_declaration)
        {
            $joins_entry_parts = array();
            if ($join_declaration[$join_left_table])
            {
                $joins_entry_parts[] = $join_declaration[$join_left_table];
            }
            $joins_entry_parts[] = 'JOIN';
            $joins_entry_parts[] = $join_declaration[$join_right_table];
            $joins_entry_parts[] = 'ON';
            $joins_entry_parts[] = $join_declaration[$join_left_value];
            $joins_entry_parts[] = '=';
            $joins_entry_parts[] = $join_declaration[$join_right_value];
            $joins[] = implode(' ', $joins_entry_parts);
        }
        $join = implode(' ', $joins);

        $query_parts = array();
        $query_parts[] = 'SELECT';
        $query_parts[] = $select;
        $query_parts[] = 'FROM';
        $query_parts[] = $join;
        $query = implode(' ', $query_parts);

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

    function retrieve_assignment_publication_targets($publication_id, $submitter_type, $condition, $offset = null, $count = null,
        $order_by = null)
    {
        $gdm = GroupDataManager::get_instance();
        $tdm = TrackingDataManager :: get_instance();
        $udm = UserDataManager :: get_instance();

        // Dynamic variable constants (requires the following variables for each table 'x': x_ref = 'x', x_table,
        // x_alias).
        $ref_table = '_table';
        $ref_alias = '_alias';

        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_COURSE_GROUP)
        {
            //weblcms_course_grou (\application\weblcms\CourseGroup)
            $wcg_ref = 'wcg';
            $wcg_table = $this->escape_table_name(CourseGroup::get_table_name());
            $wcg_alias = $this->get_alias(CourseGroup::get_table_name());
            $wcg_id = $this->escape_column_name(CourseGroup::PROPERTY_ID, $wcg_alias);
            $wcg_name = $this->escape_column_name(CourseGroup::PROPERTY_NAME, $wcg_alias);
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_PLATFORM_GROUP)
        {
            //group_group (\group\Group)
            $gg_ref = 'gg';
            $gg_table = $gdm->escape_table_name(Group::get_table_name());
            $gg_alias = $gdm->get_alias(Group::get_table_name());
            $gg_id = $gdm->escape_column_name(Group::PROPERTY_ID, $gg_alias);
            $gg_name = $gdm->escape_column_name(Group::PROPERTY_NAME, $gg_alias);
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_USER)
        {
            // user_user (\user\User)
            $uu_ref = 'uu';
            $uu_table = $udm->escape_table_name(User :: get_table_name());
            $uu_alias = $udm->get_alias(User :: get_table_name());
            $uu_firstname = $udm->escape_column_name(User :: PROPERTY_FIRSTNAME, $uu_alias);
            $uu_id = $udm->escape_column_name(User :: PROPERTY_ID, $uu_alias);
            $uu_lastname = $udm->escape_column_name(User :: PROPERTY_LASTNAME, $uu_alias);
        }

        // tracking_weblcms_assignment_submissions_tracker (\application\weblcms\WeblcmsAssignmentSubmissionsTracker)
        $twast_ref = 'twast';
        $twast_table = $tdm->escape_table_name(WeblcmsAssignmentSubmissionsTracker :: get_table_name());
        $twast_alias = $tdm->get_alias(WeblcmsAssignmentSubmissionsTracker :: get_table_name());
        $twast_date_submitted = $tdm->escape_column_name(WeblcmsAssignmentSubmissionsTracker :: PROPERTY_DATE_SUBMITTED,
            $twast_alias);
        $twast_publication_id = $tdm->escape_column_name(WeblcmsAssignmentSubmissionsTracker :: PROPERTY_PUBLICATION_ID,
            $twast_alias);
        $twast_submitter_id = $tdm->escape_column_name(WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_ID,
            $twast_alias);
        $twast_submitter_type = $tdm->escape_column_name(WeblcmsAssignmentSubmissionsTracker :: PROPERTY_SUBMITTER_TYPE,
            $twast_alias);

        // aggregate_columns
        $max_twast_date_submitted = 'MAX(' . $twast_date_submitted . ')';
        $min_twast_date_submitted = 'MIN(' . $twast_date_submitted . ')';
        $count_twast_date_submitted = 'COUNT(' . $twast_date_submitted . ')';

        $selects = array();
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_COURSE_GROUP)
        {
            $selects[] = $wcg_id . ' AS ' . WeblcmsAssignmentSubmissionsTracker::PROPERTY_SUBMITTER_ID;
            $selects[] = $wcg_name;
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_PLATFORM_GROUP)
        {
            $selects[] = $gg_id . ' AS ' . WeblcmsAssignmentSubmissionsTracker::PROPERTY_SUBMITTER_ID;
            $selects[] = $gg_name;
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_USER)
        {
            $selects[] = $uu_id . ' AS ' . WeblcmsAssignmentSubmissionsTracker::PROPERTY_SUBMITTER_ID;
            $selects[] = $uu_firstname;
            $selects[] = $uu_lastname;
        }
        $selects[] = $min_twast_date_submitted . ' AS ' . AssignmentTool :: PROPERTY_FIRST_SUBMISSION;
        $selects[] = $max_twast_date_submitted . ' AS ' . AssignmentTool :: PROPERTY_LAST_SUBMISSION;
        $selects[] = $count_twast_date_submitted . ' AS ' . AssignmentTool :: PROPERTY_NUMBER_OF_SUBMISSIONS;
        $select = implode(', ', $selects);

        $table_aliases = array();
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_COURSE_GROUP)
        {
            $table_aliases[] = $wcg_ref;
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_PLATFORM_GROUP)
        {
            $table_aliases[] = $gg_ref;
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_USER)
        {
            $table_aliases[] = $uu_ref;
        }
        $table_aliases[] = $twast_ref;

        // Uses dynamic variables.
        $tables = array();
        foreach ($table_aliases as $table_alias)
        {
            $tables[${$table_alias . $ref_table}] = ${$table_alias . $ref_table} . ' AS ' . ${$table_alias . $ref_alias};
        }

        // Join syntax: $join_left_table JOIN $join_right_table ON $join_left_value = $join_right_value [AND $conditions].
        // Condition syntax: $join_condition_column $join_condition_operator $join_condition_value.
        // $join_left_table may be null.
        $join_condition_column = 'column';
        $join_condition_operator = 'operator';
        $join_condition_value = 'value';
        $join_conditions = 'conditions';
        $join_left_table = 'left_table';
        $join_left_value = 'left_value';
        $join_right_table = 'right_table';
        $join_right_value = 'right_value';
        $join_type = 'type';
        $join_type_inner = 'JOIN';
        $join_type_left = 'LEFT JOIN';
        $join_type_outer = 'OUTER JOIN';
        $join_type_right = 'RIGHT JOIN';
        $join_declarations = array();
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_COURSE_GROUP)
        {
            $conditions = array();
            $conditions[] = array($join_condition_column => $twast_publication_id, $join_condition_operator => '=',
                $join_condition_value => $publication_id);
            $conditions[] = array($join_condition_column => $twast_submitter_type, $join_condition_operator => '=',
                $join_condition_value => $submitter_type);
            $join_declarations[] = array($join_left_table => $tables[$wcg_table], $join_type => $join_type_left,
                $join_right_table => $tables[$twast_table], $join_left_value => $wcg_id,
                $join_right_value => $twast_submitter_id, $join_conditions => $conditions);
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_PLATFORM_GROUP)
        {
            $conditions = array();
            $conditions[] = array($join_condition_column => $twast_publication_id, $join_condition_operator => '=',
                $join_condition_value => $publication_id);
            $conditions[] = array($join_condition_column => $twast_submitter_type, $join_condition_operator => '=',
                $join_condition_value => $submitter_type);
            $join_declarations[] = array($join_left_table => $tables[$gg_table], $join_type => $join_type_left,
                $join_right_table => $tables[$twast_table], $join_left_value => $gg_id,
                $join_right_value => $twast_submitter_id, $join_conditions => $conditions);
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_USER)
        {
            $conditions = array();
            $conditions[] = array($join_condition_column => $twast_publication_id, $join_condition_operator => '=',
                $join_condition_value => $publication_id);
            $conditions[] = array($join_condition_column => $twast_submitter_type, $join_condition_operator => '=',
                $join_condition_value => $submitter_type);
            $join_declarations[] = array($join_left_table => $tables[$uu_table], $join_type => $join_type_left,
                $join_right_table => $tables[$twast_table], $join_left_value => $uu_id,
                $join_right_value => $twast_submitter_id, $join_conditions => $conditions);
        }

        $joins = array();
        foreach ($join_declarations as $join_declaration)
        {
            $joins_entry_parts = array();
            if ($join_declaration[$join_left_table])
            {
                $joins_entry_parts[] = $join_declaration[$join_left_table];
            }
            $joins_entry_parts[] = $join_declaration[$join_type];
            $joins_entry_parts[] = $join_declaration[$join_right_table];
            $joins_entry_parts[] = 'ON';
            $joins_entry_parts[] = $join_declaration[$join_left_value];
            $joins_entry_parts[] = '=';
            $joins_entry_parts[] = $join_declaration[$join_right_value];
            foreach($join_declaration[$join_conditions] as $join_condition)
            {
                $joins_entry_parts[] = 'AND';
                $joins_entry_parts[] = $join_condition[$join_condition_column];
                $joins_entry_parts[] = $join_condition[$join_condition_operator];
                $joins_entry_parts[] = $join_condition[$join_condition_value];
            }
            $joins[] = implode(' ', $joins_entry_parts);
        }
        $join = implode(' ', $joins);

        $group_bys = array();
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_COURSE_GROUP)
        {
            $group_bys[] = $wcg_id;
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_PLATFORM_GROUP)
        {
            $group_bys[] = $gg_id;
        }
        if($submitter_type == WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_USER)
        {
            $group_bys[] = $uu_id;
        }
        $group_by = implode(', ', $group_bys);

        $query_parts = array();
        $query_parts[] = 'SELECT';
        $query_parts[] = $select;
        $query_parts[] = 'FROM';
        $query_parts[] = $join;

        if (isset($condition))
        {
            $translator = ConditionTranslator :: factory($this);
            $query_parts[] = $translator->render_query($condition);
        }

        $query_parts[] = 'GROUP BY';
        $query_parts[] = $group_by;

        $orders = array();
        if (is_null($order_by))
        {
            $order_by = array();
        }
        elseif (! is_array($order_by))
        {
            $order_by = array($order_by);
        }
        foreach ($order_by as $order)
        {
            $order_parts = array();
            if ($order)
            {
                if ($order->alias_is_set())
                {
                    switch ($order->get_alias())
                    {
                        case '' :
                            $order_parts[] = $this->escape_column_name($order->get_property());
                            break;
                        default :
                            $order_parts[] = $this->escape_column_name($order->get_property(), $order->get_alias());
                            break;
                    }
                }
                else
                {
                    $order_parts[] = $this->escape_column_name($order->get_property(),
                            $this->get_alias(User :: get_table_name()));
                }
                $order_parts[] = $order->get_direction() == SORT_DESC ? 'DESC' : 'ASC';
                $orders[] = implode(' ', $order_parts);
            }
        }
        $order = implode(', ', $orders);
        if (count($orders))
        {
            $query_parts[] = 'ORDER BY';
            $query_parts[] = $order;
        }
        if ($count < 0)
        {
            $count = null;
        }

        $this->set_limit(intval($count), intval($offset));
        $query = implode(' ', $query_parts);

        return new Mdb2ResultSet($this, $this->query($query), WeblcmsAssignmentSubmissionsTracker :: class_name());
    }
    
    function retrieve_assignment_publication_target_users($publication_id, $course_id, $offset = null, $count = null,
        $order_by = null, $condition = null)
    {
        $udm = UserDataManager :: get_instance();
        $users = $this->retrieve_publication_target_users($publication_id, $course_id, null, null, null, $condition)->as_array();
        $user_ids = array();
        foreach ($users as $user)
        {
            $user_ids[$user->get_id()] = $user->get_id();
        }
        if(count($user_ids) < 1)
        {
            $user_ids[] = -1;
        }
        $conditions = array();
        ! is_null($condition) ? $conditions[] = $condition : $condition;
        $conditions[] = new InCondition(User :: PROPERTY_ID, $user_ids, $udm->get_alias(User :: get_table_name()),
            true);
        $condition = new AndCondition($conditions);
        
        return $this->retrieve_assignment_publication_targets($publication_id, WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_USER,
            $condition, $offset, $count, $order_by);
    }
    
    function retrieve_assignment_publication_target_course_groups($publication_id, $course_id, $offset = null, $count = null,
        $order_by = null, $condition = null)
    {
        $course_groups = $this->retrieve_publication_target_course_groups($publication_id, $course_id, null, null, null, $condition)->as_array();
        $course_group_ids = array();
        foreach ($course_groups as $course_group)
        {
            $course_group_ids[$course_group->get_id()] = $course_group->get_id();
        }
        if(count($course_group_ids) < 1)
        {
            $course_group_ids[] = -1;
        }
        $conditions = array();
        ! is_null($condition) ? $conditions[] = $condition : $condition;
        $conditions[] = new InCondition(CourseGroup :: PROPERTY_ID, $course_group_ids, $this->get_alias(CourseGroup :: get_table_name()),
            true);
        $condition = new AndCondition($conditions);
        
        return $this->retrieve_assignment_publication_targets($publication_id, WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_COURSE_GROUP,
            $condition, $offset, $count, $order_by);
    }
    
    function retrieve_assignment_publication_target_platform_groups($publication_id, $course_id, $offset = null, $count = null,
        $order_by = null, $condition = null)
    {
        $gdm = GroupDataManager :: get_instance();
        $platform_groups = $this->retrieve_publication_target_platform_groups($publication_id, $course_id, null, null, null, $condition)->as_array();
        $platform_group_ids = array();
        foreach ($platform_groups as $platform_group)
        {
            $platform_group_ids[$platform_group->get_id()] = $platform_group->get_id();
        }
        if(count($platform_group_ids) < 1)
        {
            $platform_group_ids[] = -1;
        }
        $conditions = array();
        ! is_null($condition) ? $conditions[] = $condition : $condition;
        $conditions[] = new InCondition(Group :: PROPERTY_ID, $platform_group_ids, $gdm->get_alias(Group :: get_table_name()),
            true);
        $condition = new AndCondition($conditions);
        
        return $this->retrieve_assignment_publication_targets($publication_id, WeblcmsAssignmentSubmissionsTracker::SUBMITTER_TYPE_PLATFORM_GROUP,
            $condition, $offset, $count, $order_by);
    }
}
?>