class.ilDataCollectionTable.php

Go to the documentation of this file.

<?php

/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */

include_once './Modules/DataCollection/classes/class.ilDataCollectionStandardField.php';
include_once './Modules/DataCollection/classes/class.ilDataCollectionRecord.php';

class ilDataCollectionTable {

        protected $id = 0;
        protected $objId;
        protected $obj;
        protected $title;
        protected $fields;
        protected $stdFields;
        protected $records;
        protected $is_visible;
        protected $add_perm;
        protected $edit_perm;
        protected $delete_perm;
        protected $edit_by_owner;
        protected $delete_by_owner;
        protected $limited;
        protected $limit_start;
        protected $limit_end;
        protected $export_enabled;
        protected $default_sort_field = 0;
        protected $default_sort_field_order = 'asc';
        protected $description = '';
        protected $public_comments = 0;
        protected $view_own_records_perm = 0;


        public function __construct($a_id = 0) {
                if ($a_id != 0) {
                        $this->id = $a_id;
                        $this->doRead();
                }
        }


        public function doRead() {
                global $ilDB;

                $query = "SELECT * FROM il_dcl_table WHERE id = " . $ilDB->quote($this->getId(), "integer");
                $set = $ilDB->query($query);
                $rec = $ilDB->fetchAssoc($set);

                $this->setObjId($rec["obj_id"]);
                $this->setTitle($rec["title"]);
                $this->setAddPerm($rec["add_perm"]);
                $this->setEditPerm($rec["edit_perm"]);
                $this->setDeletePerm($rec["delete_perm"]);
                $this->setEditByOwner($rec["edit_by_owner"]);
                $this->setExportEnabled($rec["export_enabled"]);
                $this->setLimited($rec["limited"]);
                $this->setLimitStart($rec["limit_start"]);
                $this->setLimitEnd($rec["limit_end"]);
                $this->setIsVisible($rec["is_visible"]);
                $this->setDescription($rec['description']);
                $this->setDefaultSortField($rec['default_sort_field_id']);
                $this->setDefaultSortFieldOrder($rec['default_sort_field_order']);
                $this->setPublicCommentsEnabled($rec['public_comments']);
                $this->setViewOwnRecordsPerm($rec['view_own_records_perm']);
                $this->setDeleteByOwner($rec['delete_by_owner']);
        }


        public function doDelete($delete_main_table = false) {
                global $ilDB;

                foreach ($this->getRecords() as $record) {
                        $record->doDelete();
                }

                foreach ($this->getRecordFields() as $field) {
                        $field->doDelete();
                }

                // SW: Fix #12794 und #11405
                // Problem is that when the DC object gets deleted, $this::getCollectionObject() tries to load the DC but it's not in the DB anymore
                // If $delete_main_table is true, avoid getting the collection object
                $exec_delete = false;
                if ($delete_main_table) {
                        $exec_delete = true;
                }
                if (! $exec_delete && $this->getCollectionObject()->getMainTableId() != $this->getId()) {
                        $exec_delete = true;
                }
                if ($exec_delete) {
                        $query = "DELETE FROM il_dcl_table WHERE id = " . $ilDB->quote($this->getId(), "integer");
                        $ilDB->manipulate($query);

                        // Delete also view definitions
                        $set = $ilDB->query('SELECT * FROM il_dcl_view WHERE table_id = ' . $ilDB->quote($this->getId(), 'integer'));
                        $view_ids = array();
                        while ($row = $ilDB->fetchObject($set)) {
                                $view_ids[] = $row->id;
                        }
                        if (count($view_ids)) {
                                $ilDB->manipulate("DELETE FROM il_dcl_viewdefinition WHERE view_id IN (" . implode(',', $view_ids) . ")");
                        }
                        $ilDB->manipulate("DELETE FROM il_dcl_view WHERE table_id = " . $ilDB->quote($this->getId(), 'integer'));
                }
        }


        public function doCreate($create_views = true) {
                global $ilDB;

                $id = $ilDB->nextId("il_dcl_table");
                $this->setId($id);
                $query = "INSERT INTO il_dcl_table (" . "id" . ", obj_id" . ", title" . ", add_perm" . ", edit_perm" . ", delete_perm" . ", edit_by_owner"
                        . ", limited" . ", limit_start" . ", limit_end" . ", is_visible" . ", export_enabled" . ", default_sort_field_id"
                        . ", default_sort_field_order" . ", description" . ", public_comments" . ", view_own_records_perm" . ", delete_by_owner ) VALUES ("
                        . $ilDB->quote($this->getId(), "integer") . "," . $ilDB->quote($this->getObjId(), "integer") . ","
                        . $ilDB->quote($this->getTitle(), "text") . "," . $ilDB->quote($this->getAddPerm() ? 1 : 0, "integer") . ","
                        . $ilDB->quote($this->getEditPerm() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getDeletePerm() ? 1 : 0, "integer") . ","
                        . $ilDB->quote($this->getEditByOwner() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getLimited() ? 1 : 0, "integer") . ","
                        . $ilDB->quote($this->getLimitStart(), "timestamp") . "," . $ilDB->quote($this->getLimitEnd(), "timestamp") . ","
                        . $ilDB->quote($this->getIsVisible() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getExportEnabled() ? 1 : 0, "integer") . ","
                        . $ilDB->quote($this->getDefaultSortField(), "text") . "," . $ilDB->quote($this->getDefaultSortFieldOrder(), "text") . ","
                        . $ilDB->quote($this->getDescription(), "text") . "," . $ilDB->quote($this->getPublicCommentsEnabled(), "integer") . ","
                        . $ilDB->quote($this->getViewOwnRecordsPerm(), "integer") . "," . $ilDB->quote($this->getDeleteByOwner() ? 1 : 0, 'integer') . ")";

                $ilDB->manipulate($query);

                if ($create_views) {
                        //add view definition
                        $view_id = $ilDB->nextId("il_dcl_view");
                        $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
                                . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::VIEW_VIEW, "integer") . ", "
                                . $ilDB->quote(1, "integer") . ")";
                        $ilDB->manipulate($query);

                        //add edit definition
                        $view_id = $ilDB->nextId("il_dcl_view");
                        $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
                                . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::EDIT_VIEW, "integer") . ", "
                                . $ilDB->quote(1, "integer") . ")";
                        $ilDB->manipulate($query);

                        //add filter definition
                        $view_id = $ilDB->nextId("il_dcl_view");
                        $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
                                . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::FILTER_VIEW, "integer") . ", "
                                . $ilDB->quote(1, "integer") . ")";
                        $ilDB->manipulate($query);

                        //add filter definition
                        $view_id = $ilDB->nextId("il_dcl_view");
                        $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
                                . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::EXPORTABLE_VIEW, "integer") . ", "
                                . $ilDB->quote(1, "integer") . ")";
                        $ilDB->manipulate($query);

                        $this->buildOrderFields();
                }
        }


        /*
         * doUpdate
         */
        public function doUpdate() {
                global $ilDB;

                $ilDB->update("il_dcl_table", array(
                        "obj_id" => array( "integer", $this->getObjId() ),
                        "title" => array( "text", $this->getTitle() ),
                        "add_perm" => array( "integer", (int) $this->getAddPerm() ),
                        "edit_perm" => array( "integer", (int) $this->getEditPerm() ),
                        "delete_perm" => array( "integer", (int) $this->getDeletePerm() ),
                        "edit_by_owner" => array( "integer", (int) $this->getEditByOwner() ),
                        "limited" => array( "integer", $this->getLimited() ),
                        "limit_start" => array( "timestamp", $this->getLimitStart() ),
                        "limit_end" => array( "timestamp", $this->getLimitEnd() ),
                        "is_visible" => array( "integer", $this->getIsVisible() ? 1 : 0 ),
                        "export_enabled" => array( "integer", $this->getExportEnabled() ? 1 : 0 ),
                        "description" => array( "text", $this->getDescription() ),
                        "default_sort_field_id" => array( "text", $this->getDefaultSortField() ),
                        "default_sort_field_order" => array( "text", $this->getDefaultSortFieldOrder() ),
                        "public_comments" => array( "integer", $this->getPublicCommentsEnabled() ? 1 : 0 ),
                        "view_own_records_perm" => array( "integer", $this->getViewOwnRecordsPerm() ),
                        'delete_by_owner' => array('integer', $this->getDeleteByOwner() ? 1 : 0),
                ), array(
                        "id" => array( "integer", $this->getId() )
                ));
        }


        public function setId($a_id) {
                $this->id = $a_id;
        }


        public function getId() {
                return $this->id;
        }


        public function setObjId($a_id) {
                $this->objId = $a_id;
        }


        public function getObjId() {
                return $this->objId;
        }


        public function setTitle($a_title) {
                $this->title = $a_title;
        }


        public function getTitle() {
                return $this->title;
        }


        public function getCollectionObject() {
                $this->loadObj();

                return $this->obj;
        }


        protected function loadObj() {
                if ($this->obj == NULL) {
                        $this->obj = new ilObjDataCollection($this->objId, false);
                }
        }


        public function getRecords() {
                $this->loadRecords();

                return $this->records;
        }


        public function getRecordsByFilter(array $filter = array()) {
                $this->loadRecords();
                // Only pass records trough filter if there is filtering required #performance-improvements
                if (! count($filter)) {
                        return $this->records;
                }
                $filtered = array();
                foreach ($this->getRecords() as $record) {
                        if ($record->passThroughFilter($filter)) {
                                $filtered[] = $record;
                        }
                }

                return $filtered;
        }


        protected function loadRecords() {
                if ($this->records == NULL) {
                        global $ilDB;

                        $records = array();
                        $query = "SELECT id FROM il_dcl_record WHERE table_id = " . $ilDB->quote($this->id, "integer");
                        $set = $ilDB->query($query);

                        while ($rec = $ilDB->fetchAssoc($set)) {
                                $records[$rec['id']] = ilDataCollectionCache::getRecordCache($rec['id']);
                        }

                        $this->records = $records;
                }
        }

        //TODO: replace this method with DataCollection->getTables()
        public function getAll($a_id) {
                global $ilDB;

                // build query
                $query = "SELECT * FROM il_dcl_table WHERE obj_id = " . $ilDB->quote($a_id, "integer");
                $set = $ilDB->query($query);

                $all = array();
                while ($rec = $ilDB->fetchAssoc($set)) {
                        $all[$rec['id']] = $rec;
                }

                return $all;
        }


        public function deleteField($field_id) {
                $field = ilDataCollectionCache::getFieldCache($field_id);
                $records = $this->getRecords();

                foreach ($records as $record) {
                        $record->deleteField($field_id);
                }

                $field->doDelete();
        }


        public function getField($field_id) {
                $fields = $this->getFields();
                $field = NULL;
                foreach ($fields as $field_1) {
                        if ($field_1->getId() == $field_id) {
                                $field = $field_1;
                        }
                }

                return $field;
        }


        public function getFieldIds() {
                return array_keys($this->getFields());
        }


        protected function loadFields() {
                if ($this->fields === NULL) {
                        global $ilDB;

                        $query = "SELECT DISTINCT field.* FROM il_dcl_field AS field 
                                  INNER JOIN il_dcl_view AS view ON view.table_id = field.table_id 
                                  INNER JOIN il_dcl_viewdefinition AS def ON def.view_id = view.id 
                                  WHERE field.table_id =" . $ilDB->quote($this->getId(), "integer") . "
                                  ORDER BY def.field_order DESC";
                        $fields = array();
                        $set = $ilDB->query($query);

                        while ($rec = $ilDB->fetchAssoc($set)) {
                                $field = ilDataCollectionCache::buildFieldFromRecord($rec);
                                $fields[$field->getId()] = $field;
                        }
                        $this->sortByOrder($fields);
                        $this->fields = $fields;
                }
        }


        public function getNewOrder() {
                $fields = $this->getFields();
                $place = 0;
                foreach ($fields as $field) {
                        if (! $field->isStandardField()) {
                                $place = $field->getOrder() + 1;
                        }
                }

                return $place;
        }


        public function getFields() {
                $this->loadFields();
                $this->stdFields = $this->getStandardFields();
                $fields = array_merge($this->fields, $this->stdFields);
                $this->sortByOrder($fields);

                return $fields;
        }


        public function getFieldsForFormula() {
                $unsupported = array(
                        ilDataCollectionDatatype::INPUTFORMAT_ILIAS_REF,
                        ilDataCollectionDatatype::INPUTFORMAT_FORMULA,
                        ilDataCollectionDatatype::INPUTFORMAT_MOB,
                        ilDataCollectionDatatype::INPUTFORMAT_BOOLEAN,
                        ilDataCollectionDatatype::INPUTFORMAT_REFERENCELIST,
                        ilDataCollectionDatatype::INPUTFORMAT_REFERENCE,
                        ilDataCollectionDatatype::INPUTFORMAT_FILE,
                        ilDataCollectionDatatype::INPUTFORMAT_RATING,
                );

                $this->loadFields();
                $return = $this->getStandardFields();
                foreach ($this->fields as $field) {
                        if (! in_array($field->getDatatypeId(), $unsupported)) {
                                $return[] = $field;
                        }
                }

                return $return;
        }


        public function getStandardFields() {
                if ($this->stdFields == NULL) {
                        $this->stdFields = ilDataCollectionStandardField::_getStandardFields($this->id);
                        // Don't return comments as field if this feature is not activated in the settings
                        if (! $this->getPublicCommentsEnabled()) {
                                foreach ($this->stdFields as $k => $field) {
                                        if ($field->getId() == 'comments') {
                                                unset($this->stdFields[$k]);
                                                break;
                                        }
                                }
                        }
                }

                return $this->stdFields;
        }


        public function getRecordFields() {
                $this->loadFields();

                return $this->fields;
        }


        public function getVisibleFields() {
                $fields = $this->getFields();

                $visibleFields = array();

                foreach ($fields as $field) {
                        if ($field->isVisible()) {
                                $visibleFields[] = $field;
                        }
                }

                return $visibleFields;
        }


        public function getEditableFields() {
                $fields = $this->getRecordFields();
                $editableFields = array();

                foreach ($fields as $field) {
                        if (! $field->getLocked()) {
                                $editableFields[] = $field;
                        }
                }

                return $editableFields;
        }


        public function getFilterableFields() {
                $fields = $this->getFields();
                $filterableFields = array();

                foreach ($fields as $field) {
                        if ($field->isFilterable()) {
                                $filterableFields[] = $field;
                        }
                }

                return $filterableFields;
        }


        public function getExportableFields() {
                $fields = $this->getFields();
                $exportableFields = array();
                foreach ($fields as $field) {
                        if ($field->getExportable()) {
                                $exportableFields[] = $field;
                        }
                }

                return $exportableFields;
        }


        public function hasPermissionToFields($ref_id) {
                return ilObjDataCollectionAccess::hasWriteAccess($ref_id);
        }


        public function hasPermissionToAddTable($ref_id) {
                return ilObjDataCollectionAccess::hasWriteAccess($ref_id);
        }


        public function hasPermissionToAddRecord($ref_id) {
                if (ilObjDataCollectionAccess::hasWriteAccess($ref_id)) {
                        return true;
                }
                if (!ilObjDataCollectionAccess::hasAddRecordAccess($ref_id)) {
                        return false;
                }

                return ($this->getAddPerm() AND $this->checkLimit());
        }


        public function hasPermissionToEditRecord($ref_id, ilDataCollectionRecord $record) {
                if (ilObjDataCollectionAccess::hasWriteAccess($ref_id)) {
                        return true;
                }
                if (!ilObjDataCollectionAccess::hasAddRecordAccess($ref_id)) {
                        return false;
                }
                if (!$this->checkLimit()) {
                        return false;
                }
                if ($this->getEditPerm() && !$this->getEditByOwner()) {
                        return true;
                }
                if ($this->getEditByOwner()) {
                        return $this->doesRecordBelongToUser($record);
                }

                return false;
        }


        public function hasPermissionToDeleteRecord($ref_id, ilDataCollectionRecord $record) {
                if (ilObjDataCollectionAccess::hasWriteAccess($ref_id)) {
                        return true;
                }
                if (!ilObjDataCollectionAccess::hasAddRecordAccess($ref_id)) {
                        return false;
                }
                if (!$this->checkLimit()) {
                        return false;
                }
                if ($this->getDeletePerm() && !$this->getDeleteByOwner()) {
                        return true;
                }
                if ($this->getDeleteByOwner()) {
                        return $this->doesRecordBelongToUser($record);
                }

                return false;
        }


        public function hasPermissionToDeleteRecords($ref_id) {
                return ($this->getDeletePerm() AND ilObjDataCollectionAccess::hasWriteAccess($ref_id));
        }


        public function hasPermissionToViewRecord($ref_id, $record) {
                global $ilUser, $rbacreview;
                if (ilObjDataCollectionAccess::hasWriteAccess($ref_id)) {
                        return true;
                }
                if (ilObjDataCollectionAccess::hasReadAccess($ref_id)) {
                        // Check for view only own entries setting
                        if ($this->getViewOwnRecordsPerm() && $ilUser->getId() != $record->getOwner()) {
                                return false;
                        }

                        return true;
                }

                return false;
        }


        protected function doesRecordBelongToUser(ilDataCollectionRecord $record) {
                global $ilUser;

                return ($ilUser->getId() == $record->getOwner());
        }


        protected function checkLimit() {
                if ($this->getLimited()) {
                        $now = new ilDateTime(date("Y-m-d H:i:s"), IL_CAL_DATE);
                        $from = new ilDateTime($this->getLimitStart(), IL_CAL_DATE);
                        $to = new ilDateTime($this->getLimitEnd(), IL_CAL_DATE);
                        return ($from <= $now && $now <= $to);
                }

                return true;
        }


        public function updateFields() {
                foreach ($this->getFields() as $field) {
                        $field->doUpdate();
                }
        }


        public function sortFields(&$fields) {
                $this->sortByOrder($fields);
                //After sorting the array loses it's keys respectivly their keys are set form $field->id to 1,2,3... so we reset the keys.
                $named = array();
                foreach ($fields as $field) {
                        $named[$field->getId()] = $field;
                }

                $fields = $named;
        }


        protected function sortByOrder(&$array) {
                usort($array, array( $this, "compareOrder" ));
        }


        public function buildOrderFields() {
                $fields = $this->getFields();
                $this->sortByOrder($fields);
                $count = 10;
                $offset = 10;
                foreach ($fields as $field) {
                        if (! is_null($field->getOrder())) {
                                $field->setOrder($count);
                                $count = $count + $offset;
                                $field->doUpdate();
                        }
                }
        }


        public function getFieldByTitle($title) {
                $return = NULL;
                foreach ($this->getFields() as $field) {
                        if ($field->getTitle() == $title) {
                                $return = $field;
                                break;
                        }
                }

                return $return;
        }


        public function setAddPerm($add_perm) {
                $this->add_perm = $add_perm;
        }


        public function getAddPerm() {
                return (bool) $this->add_perm;
        }


        public function setDeletePerm($delete_perm) {
                $this->delete_perm = $delete_perm;
                if (!$delete_perm) {
                        $this->setDeleteByOwner(false);
                }
        }


        public function getDeletePerm() {

                return (bool) $this->delete_perm;
        }


        public function setEditByOwner($edit_by_owner) {
                $this->edit_by_owner = $edit_by_owner;
                if ($edit_by_owner) {
                        $this->setEditPerm(true);
                }
        }


        public function getEditByOwner() {
                return (bool) $this->edit_by_owner;
        }


        public function getDeleteByOwner()
        {
                return (bool) $this->delete_by_owner;
        }


        public function setDeleteByOwner($delete_by_owner)
        {
                $this->delete_by_owner = $delete_by_owner;
                if ($delete_by_owner) {
                        $this->setDeletePerm(true);
                }
        }


        public function setEditPerm($edit_perm) {
                $this->edit_perm = $edit_perm;
                if (!$edit_perm) {
                        $this->setEditByOwner(false);
                }
        }


        public function getEditPerm() {
                return (bool) $this->edit_perm;
        }


        public function setLimited($limited) {
                $this->limited = $limited;
        }


        public function getLimited() {
                return $this->limited;
        }


        public function setLimitEnd($limit_end) {
                $this->limit_end = $limit_end;
        }


        public function getLimitEnd() {
                return $this->limit_end;
        }


        public function setLimitStart($limit_start) {
                $this->limit_start = $limit_start;
        }


        public function getLimitStart() {
                return $this->limit_start;
        }


        public function setIsVisible($is_visible) {
                $this->is_visible = $is_visible;
        }


        public function getIsVisible() {
                return $this->is_visible;
        }


        public function setDescription($description) {
                $this->description = $description;
        }


        public function getDescription() {
                return $this->description;
        }


        public function setDefaultSortField($default_sort_field) {
                $default_sort_field = ($default_sort_field) ? $default_sort_field : 0; // Change null or empty strings to zero
                $this->default_sort_field = $default_sort_field;
        }


        public function getDefaultSortField() {
                return $this->default_sort_field;
        }


        public function setDefaultSortFieldOrder($default_sort_field_order) {
                if (! in_array($default_sort_field_order, array( 'asc', 'desc' ))) {
                        $default_sort_field_order = 'asc';
                }
                $this->default_sort_field_order = $default_sort_field_order;
        }


        public function getDefaultSortFieldOrder() {
                return $this->default_sort_field_order;
        }


        public function setPublicCommentsEnabled($public_comments) {
                $this->public_comments = $public_comments;
        }


        public function getPublicCommentsEnabled() {
                return $this->public_comments;
        }


        public function setViewOwnRecordsPerm($view_own_perm) {
                $this->view_own_records_perm = (int)$view_own_perm;
        }


        public function getViewOwnRecordsPerm() {
                return (bool)$this->view_own_records_perm;
        }


        public function hasCustomFields() {
                $this->loadFields();

                return (count($this->fields) > 0) ? true : false;
        }


        function compareOrder($a, $b) {
                if (is_null($a->getOrder() == NULL) && is_null($b->getOrder() == NULL)) {
                        return 0;
                }
                if (is_null($a->getOrder())) {
                        return 1;
                }
                if (is_null($b->getOrder())) {
                        return - 1;
                }

                return $a->getOrder() < $b->getOrder() ? - 1 : 1;
        }


        public function cloneStructure(ilDataCollectionTable $original) {
                $this->setTitle($original->getTitle());
                $this->setDescription($original->getDescription());
                $this->setIsVisible($original->getIsVisible());
                $this->setEditByOwner($original->getEditByOwner());
                $this->setAddPerm($original->getAddPerm());
                $this->setEditPerm($original->getEditPerm());
                $this->setDeleteByOwner($original->getDeleteByOwner());
                $this->setDeletePerm($original->getDeletePerm());
                $this->setLimited($original->getLimited());
                $this->setLimitStart($original->getLimitStart());
                $this->setLimitEnd($original->getLimitEnd());
                $this->setViewOwnRecordsPerm($original->getViewOwnRecordsPerm());
                $this->setExportEnabled($original->getExportEnabled());
                $this->setPublicCommentsEnabled($original->getPublicCommentsEnabled());
                $this->setDefaultSortFieldOrder($original->getDefaultSortFieldOrder());

                $this->doCreate();
                // reset stdFields to get new for the created object

                $default_sort_field = 0;
                // Clone standard-fields
                $org_std_fields = $original->getStandardFields();
                foreach ($this->getStandardFields() as $element_key => $std_field) {
                        $std_field->cloneStructure($org_std_fields[$element_key]);
                        if ($std_field->getId() === $original->getDefaultSortField()) {
                                $default_sort_field = $std_field->getId();
                        }
                }

                // Clone fields
                $new_fields = array();
                foreach ($original->getFields() as $orig_field) {
                        if (! $orig_field->isStandardField()) {
                                $new_field = new ilDataCollectionField();
                                $new_field->setTableId($this->getId());
                                $new_field->cloneStructure($orig_field->getId());
                                $new_fields[$orig_field->getId()] = $new_field;

                                if ($orig_field->getId() == $original->getDefaultSortField()) {
                                        $default_sort_field = $new_field->getId();
                                }
                        }
                }

                $this->setDefaultSortField($default_sort_field);
                $this->doUpdate();

                //TODO: Find better way to copy data (include referenced data)
                // Clone Records with recordfields
                /*foreach($original->getRecords() as $orig_record){
                        $new_record = new ilDataCollectionRecord();
                        $new_record->setTableId($this->getId());
                        $new_record->cloneStructure($orig_record->getId(), $new_fields);
                }*/

                if ($old_view_id = ilDataCollectionRecordViewViewdefinition::getIdByTableId($original->getId())) {
                        $old_view = new ilDataCollectionRecordViewViewdefinition($old_view_id);
                        $old_view->setTableId($original->getId());
                        $viewdef = new ilDataCollectionRecordViewViewdefinition();
                        $viewdef->setTableId($this->id);
                        $viewdef->setXMLContent($old_view->getXMLContent(false));
                        $viewdef->create();
                }
        }


        public function _hasRecords() {
                return (count($this->getRecords()) > 0) ? true : false;
        }


        public function addField($field) {
                $this->fields[$field->getId()] = $field;
        }


        public static function _tableExists($table_id) {
                global $ilDB;
                $query = "SELECT * FROM il_dcl_table WHERE id = " . $table_id;
                $result = $ilDB->query($query);

                return $result->numRows() != 0;
        }


        public static function _getTableIdByTitle($title, $obj_id) {
                global $ilDB;
                $result = $ilDB->query('SELECT id FROM il_dcl_table WHERE title = ' . $ilDB->quote($title, 'text') . ' AND obj_id = '
                        . $ilDB->quote($obj_id, 'integer'));
                $id = 0;
                while ($rec = $ilDB->fetchAssoc($result)) {
                        $id = $rec['id'];
                }

                return $id;
        }


        public function setExportEnabled($export_enabled) {
                $this->export_enabled = $export_enabled;
        }


        public function getExportEnabled() {
                return $this->export_enabled;
        }


        public static function _hasFieldByTitle($title, $obj_id) {
                global $ilDB;
                $result = $ilDB->query('SELECT * FROM il_dcl_field WHERE table_id = ' . $ilDB->quote($obj_id, 'integer') . ' AND title = '
                        . $ilDB->quote($title, 'text'));

                return ($ilDB->numRows($result)) ? true : false;
        }


        public function getPartialRecords($sort, $direction, $limit, $offset, array $filter = array()) {
                global $ilDB, $ilUser, $rbacreview;

                $sort_field = ($sort) ? $this->getFieldByTitle($sort) : $this->getField('id');
                $direction = strtolower($direction);
                $direction = (in_array($direction, array( 'desc', 'asc' ))) ? $direction : 'asc';

                // Sorting by a status from an ILIAS Ref field. This column is added dynamically to the table, there is no field model
                $sort_by_status = false;
                if (substr($sort, 0, 8) == '_status_') {
                        $sort_by_status = true;
                        $sort_field = $this->getFieldByTitle(substr($sort, 8));
                }

                if (is_null($sort_field)) {
                        $sort_field = $this->getField('id');
                }

                $id = $sort_field->getId();
                $stl = $sort_field->getStorageLocation();
                $select_str = '';
                $join_str = '';
                $where_additions = '';
                $has_nref = false;

                if ($sort_field->isStandardField() && $id != 'comments') {
                        if ($id == 'owner' || $id == 'last_edit_by') {
                                $join_str .= "LEFT JOIN usr_data AS sort_usr_data_{$id} ON (sort_usr_data_{$id}.usr_id = record.{$id})";
                                $select_str .= " sort_usr_data_{$id}.login AS field_{$id},";
                        } else {
                                $select_str .= " record.{$id} AS field_{$id},";
                        }
                } else {
                        switch ($sort_field->getDatatypeId()) {
                                case ilDataCollectionDatatype::INPUTFORMAT_RATING:
                                        $rating_joined = true;
                                        // FSX Bugfix 0015735: The average is multiplied with 10000 and added to the amount of votes
                                        $join_str .= "LEFT JOIN (SELECT (ROUND(AVG(rating), 1) * 10000 + COUNT(rating)) as rating, obj_id FROM il_rating GROUP BY obj_id) AS average ON average.obj_id = record.id";
                                        $select_str .= " average.rating AS field_{$id},";
                                        break;
                                case ilDataCollectionDatatype::INPUTFORMAT_ILIAS_REF:
                                        $join_str .=
                                                "LEFT JOIN il_dcl_record_field AS sort_record_field_{$id} ON (sort_record_field_{$id}.record_id = record.id AND sort_record_field_{$id}.field_id = "
                                                . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
                                        $join_str .= "LEFT JOIN il_dcl_stloc{$stl}_value AS sort_stloc_{$id} ON (sort_stloc_{$id}.record_field_id = sort_record_field_{$id}.id) ";
                                        $join_str .= "LEFT JOIN object_reference AS sort_object_reference_{$id} ON (sort_object_reference_{$id}.ref_id = sort_stloc_{$id}.value AND sort_object_reference_{$id}.deleted IS NULL)";
                                        $join_str .= "LEFT JOIN object_data AS sort_object_data_{$id} ON (sort_object_data_{$id}.obj_id = sort_object_reference_{$id}.obj_id)";
                                        if ($sort_by_status) {
                                                global $ilUser;
                                                $join_str .= "LEFT JOIN ut_lp_marks AS ut ON (ut.obj_id = sort_object_data_{$id}.obj_id AND ut.usr_id = "
                                                        . $ilDB->quote($ilUser->getId(), 'integer') . ") ";
                                        }
                                        $select_str .= (! $sort_by_status) ? " sort_object_data_{$id}.title AS field_{$id}," : " ut.status AS field_{$id}";
                                        break;
                                case ilDataCollectionDatatype::INPUTFORMAT_FILE:
                                case ilDataCollectionDatatype::INPUTFORMAT_MOB:
                                        $join_str .=
                                                "LEFT JOIN il_dcl_record_field AS sort_record_field_{$id} ON (sort_record_field_{$id}.record_id = record.id AND sort_record_field_{$id}.field_id = "
                                                . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
                                        $join_str .= "LEFT JOIN il_dcl_stloc{$stl}_value AS sort_stloc_{$id} ON (sort_stloc_{$id}.record_field_id = sort_record_field_{$id}.id) ";
                                        $join_str .= "LEFT JOIN object_data AS sort_object_data_{$id} ON (sort_object_data_{$id}.obj_id = sort_stloc_{$id}.value) ";
                                        $select_str .= " sort_object_data_{$id}.title AS field_{$id},";
                                        break;
                                case ilDataCollectionDatatype::INPUTFORMAT_REFERENCE:
                                        $prop = $sort_field->getPropertyvalues();
                                        $ref_field = ilDataCollectionCache::getFieldCache($sort_field->getFieldRef());
                                        $n_ref = $prop[ilDataCollectionField::PROPERTYID_N_REFERENCE];
                                        if ($n_ref) {
                                                $has_nref = true;
                                        }
                                        $select_str .= ($n_ref) ? " GROUP_CONCAT(stloc_{$id}_joined.value) AS field_{$id}" : "stloc_{$id}_joined.value AS field_{$id},";
                                        $join_str .=
                                                "LEFT JOIN il_dcl_record_field AS record_field_{$id} ON (record_field_{$id}.record_id = record.id AND record_field_{$id}.field_id = "
                                                . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
                                        $join_str .= "LEFT JOIN il_dcl_stloc{$stl}_value AS stloc_{$id} ON (stloc_{$id}.record_field_id = record_field_{$id}.id) ";
                                        $join_str .=
                                                "LEFT JOIN il_dcl_record_field AS record_field_{$id}_joined ON (record_field_{$id}_joined.record_id = stloc_{$id}.value AND record_field_{$id}_joined.field_id = "
                                                . $ilDB->quote($ref_field->getId(), 'integer') . ") ";
                                        $join_str .= "LEFT JOIN il_dcl_stloc{$ref_field->getStorageLocation()}_value AS stloc_{$id}_joined ON (stloc_{$id}_joined.record_field_id = record_field_{$id}_joined.id) ";
                                        break;
                                case ilDataCollectionDatatype::INPUTFORMAT_DATETIME:
                                case ilDataCollectionDatatype::INPUTFORMAT_TEXT:
                                case ilDataCollectionDatatype::INPUTFORMAT_BOOLEAN:
                                case ilDataCollectionDatatype::INPUTFORMAT_NUMBER:
                                        $select_str .= " sort_stloc_{$id}.value AS field_{$id},";
                                        $join_str .=
                                                "LEFT JOIN il_dcl_record_field AS sort_record_field_{$id} ON (sort_record_field_{$id}.record_id = record.id AND sort_record_field_{$id}.field_id = "
                                                . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
                                        $join_str .= "LEFT JOIN il_dcl_stloc{$stl}_value AS sort_stloc_{$id} ON (sort_stloc_{$id}.record_field_id = sort_record_field_{$id}.id) ";
                                        break;
                        }
                }

                if (count($filter)) {
                        foreach ($filter as $key => $filter_value) {
                                $filter_field_id = substr($key, 7);
                                $filter_field = $this->getField($filter_field_id);
                                switch ($filter_field->getDatatypeId()) {
                                        case ilDataCollectionDatatype::INPUTFORMAT_RATING:
                                                if(!$rating_joined) {
                                                        $join_str .= "LEFT JOIN (SELECT (ROUND(AVG(rating), 1) * 10000 + COUNT(rating)) as rating, obj_id FROM il_rating GROUP BY obj_id) AS average ON average.obj_id = record.id";
                                                }
                                                // FSX Bugfix 0015735: The average is multiplied with 10000 and added to the amount of votes
                                                $where_additions .= " AND average.rating >= " . $ilDB->quote($filter_value * 10000, 'integer');
                                                break;
                                        case ilDataCollectionDatatype::INPUTFORMAT_ILIAS_REF:
                                                $join_str .=
                                                        " INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                        . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                $join_str .= " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id) ";
                                                $join_str .= " INNER JOIN object_reference AS filter_object_reference_{$filter_field_id} ON (filter_object_reference_{$filter_field_id}.ref_id = filter_stloc_{$filter_field_id}.value ) ";
                                                $join_str .=
                                                        " INNER JOIN object_data AS filter_object_data_{$filter_field_id} ON (filter_object_data_{$filter_field_id}.obj_id = filter_object_reference_{$filter_field_id}.obj_id AND filter_object_data_{$filter_field_id}.title LIKE "
                                                        . $ilDB->quote("%$filter_value%", 'text') . ") ";
                                                break;
                                        case ilDataCollectionDatatype::INPUTFORMAT_MOB:
                                        case ilDataCollectionDatatype::INPUTFORMAT_FILE:
                                                $join_str .=
                                                        " INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                        . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                $join_str .= " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id) ";
                                                $join_str .=
                                                        " INNER JOIN object_data AS filter_object_data_{$filter_field_id} ON (filter_object_data_{$filter_field_id}.obj_id = filter_stloc_{$filter_field_id}.value AND filter_object_data_{$filter_field_id}.title LIKE "
                                                        . $ilDB->quote("%$filter_value%", 'text') . ") ";
                                                break;
                                        case ilDataCollectionDatatype::INPUTFORMAT_DATETIME:
                                                $date_from = (isset($filter_value['from']) && is_object($filter_value['from'])) ? $filter_value['from'] : NULL;
                                                $date_to = (isset($filter_value['to']) && is_object($filter_value['to'])) ? $filter_value['to'] : NULL;
                                                if ($filter_field->isStandardField()) {
                                                        if ($date_from) {
                                                                $where_additions .= " AND (record.{$filter_field_id} >= " . $ilDB->quote($date_from, 'date') . ")";
                                                        }
                                                        if ($date_to) {
                                                                $where_additions .= " AND (record.{$filter_field_id} <= " . $ilDB->quote($date_to, 'date') . ")";
                                                        }
                                                } else {
                                                        $join_str .=
                                                                " INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                                . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                        $join_str .= " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id ";
                                                        if ($date_from) {
                                                                $join_str .= "AND filter_stloc_{$filter_field_id}.value >= " . $ilDB->quote($date_from, 'date') . " ";
                                                        }
                                                        if ($date_to) {
                                                                $join_str .= "AND filter_stloc_{$filter_field_id}.value <= " . $ilDB->quote($date_to, 'date') . " ";
                                                        }
                                                        $join_str .= ") ";
                                                }
                                                break;
                                        case ilDataCollectionDatatype::INPUTFORMAT_NUMBER:
                                                $from = (isset($filter_value['from'])) ? (int)$filter_value['from'] : NULL;
                                                $to = (isset($filter_value['to'])) ? (int)$filter_value['to'] : NULL;
                                                if ($filter_field->isStandardField()) {
                                                        if (! is_null($from)) {
                                                                $where_additions .= " AND record.{$filter_field_id} >= " . $ilDB->quote($from, 'integer');
                                                        }
                                                        if (! is_null($to)) {
                                                                $where_additions .= " AND record.{$filter_field_id} <= " . $ilDB->quote($to, 'integer');
                                                        }
                                                } else {
                                                        $join_str .=
                                                                " INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                                . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                        $join_str .= " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id";
                                                        if (! is_null($from)) {
                                                                $join_str .= " AND filter_stloc_{$filter_field_id}.value >= " . $ilDB->quote($from, 'integer');
                                                        }
                                                        if (! is_null($to)) {
                                                                $join_str .= " AND filter_stloc_{$filter_field_id}.value <= " . $ilDB->quote($to, 'integer');
                                                        }
                                                        $join_str .= ") ";
                                                }
                                                break;
                                        case ilDataCollectionDatatype::INPUTFORMAT_BOOLEAN:
                                                if ($filter_value == "checked") {
                                                        $join_str .=
                                                                " INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                                . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                        $join_str .= " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id";
                                                        $join_str .= " AND filter_stloc_{$filter_field_id}.value = " . $ilDB->quote(1, 'integer');
                                                } else {
                                                        $join_str .=
                                                                " INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                                . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                        $join_str .= "LEFT JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id";
                                                        $where_additions .= " AND (filter_stloc_{$filter_field_id}.value <> " . $ilDB->quote(1, 'integer')
                                                                . " OR filter_stloc_{$filter_field_id}.value is NULL)";
                                                }
                                                $join_str .= " ) ";
                                                break;
                                        case ilDataCollectionDatatype::INPUTFORMAT_TEXT:
                                                if ($filter_field->isStandardField()) {
                                                        $join_str .=
                                                                " INNER JOIN usr_data AS filter_usr_data_{$filter_field_id} ON (filter_usr_data_{$filter_field_id}.usr_id = record.{$filter_field_id} AND filter_usr_data_{$filter_field_id}.login LIKE "
                                                                . $ilDB->quote("%$filter_value%", 'text') . ") ";
                                                } else {
                                                        $join_str .=
                                                                "  INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                                . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                        $join_str .=
                                                                " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id AND filter_stloc_{$filter_field_id}.value LIKE "
                                                                . $ilDB->quote("%$filter_value%", 'text') . ") ";
                                                }
                                                break;
                                        case ilDataCollectionDatatype::INPUTFORMAT_REFERENCE:
                                                $join_str .=
                                                        " INNER JOIN il_dcl_record_field AS filter_record_field_{$filter_field_id} ON (filter_record_field_{$filter_field_id}.record_id = record.id AND filter_record_field_{$filter_field_id}.field_id = "
                                                        . $ilDB->quote($filter_field_id, 'integer') . ") ";
                                                $prop = $filter_field->getPropertyvalues();
                                                $n_ref = $prop[ilDataCollectionField::PROPERTYID_N_REFERENCE];
                                                if ($n_ref) {
                                                        $join_str .=
                                                                " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id AND filter_stloc_{$filter_field_id}.value LIKE "
                                                                . $ilDB->quote("%$filter_value%", 'text') . ") ";
                                                } else {
                                                        $join_str .=
                                                                " INNER JOIN il_dcl_stloc{$filter_field->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id AND filter_stloc_{$filter_field_id}.value = "
                                                                . $ilDB->quote($filter_value, 'integer') . ") ";
                                                }
                                                break;
                                }
                        }
                }

                // Build the query string
                $sql = "SELECT DISTINCT record.id, record.owner";
                if($select_str) {
                        $sql .= ', ';
                }               $sql .= rtrim($select_str, ',') . " FROM il_dcl_record AS record ";
                $sql .= $join_str;
                $sql .= " WHERE record.table_id = " . $ilDB->quote($this->getId(), 'integer') . $where_additions;
                if ($has_nref) {
                        $sql .= " GROUP BY record.id, record.owner";
                }
                $props = $sort_field->getProperties();
                if($id != 'comments' && $sort_field->getDatatypeId() != ilDataCollectionDatatype::INPUTFORMAT_FORMULA && !$props[ilDataCollectionField::PROPERTYID_URL]) {
                        $sql .= " ORDER BY field_{$id} {$direction}";
                }
                $set = $ilDB->query($sql);
                $total_record_ids = array();
                // Save record-ids in session to enable prev/next links in detail view
                $_SESSION['dcl_record_ids'] = array();
        $_SESSION['dcl_table_id'] = $this->getId();
                $is_allowed_to_view = ilObjDataCollectionAccess::hasWriteAccess(array_pop(ilObject::_getAllReferences($this->getObjId())));
                while ($rec = $ilDB->fetchAssoc($set)) {
                        // Quick check if the current user is allowed to view the record
                        if (!$is_allowed_to_view && ($this->getViewOwnRecordsPerm() && $ilUser->getId() != $rec['owner'])) {
                                continue;
                        }
                        $total_record_ids[] = $rec['id'];
                        $_SESSION['dcl_record_ids'][] = $rec['id'];
                }
                // Sort by formula
                if ($sort_field->getDatatypeId() == ilDataCollectionDatatype::INPUTFORMAT_FORMULA) {
                        $sort_array = array();
                        foreach ($total_record_ids as $id) {
                                $formula_field = ilDataCollectionCache::getRecordFieldCache(new ilDataCollectionRecord($id), $sort_field);
                                $sort_array[$id] = $formula_field->getValue();
                        }
                        switch ($direction) {
                                case 'asc':
                                case 'ASC':
                                        asort($sort_array);
                                        break;
                                case 'desc':
                                case 'DESC':
                                        arsort($sort_array);
                                        break;
                        }
                        $total_record_ids = array_keys($sort_array);
                }
                //Sort by URL-title if existent
                if ($props[ilDataCollectionField::PROPERTYID_URL]) {
                        $sort_array = array();
                        foreach ($total_record_ids as $id) {
                                $url_field = ilDataCollectionCache::getRecordFieldCache(new ilDataCollectionRecord($id), $sort_field);
                                $sort_array[$id] = $url_field->getSortingValue();
                        }
                        switch ($direction) {
                                case 'asc':
                                case 'ASC':
                                        asort($sort_array);
                                        break;
                                case 'desc':
                                case 'DESC':
                                        arsort($sort_array);
                                        break;
                        }
                        $total_record_ids = array_keys($sort_array);
                }
                // Now slice the array to load only the needed records in memory
                $record_ids = array_slice($total_record_ids, $offset, $limit);
                $records = array();
                foreach ($record_ids as $id) {
                        $records[] = ilDataCollectionCache::getRecordCache($id);
                }

                return array( 'records' => $records, 'total' => count($total_record_ids) );
        }
}