ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.ilDataCollectionTable.php
Go to the documentation of this file.
1<?php
2
3/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
4
5include_once './Modules/DataCollection/classes/class.ilDataCollectionStandardField.php';
6include_once './Modules/DataCollection/classes/class.ilDataCollectionRecord.php';
7
21
25 protected $id = 0;
29 protected $objId;
33 protected $obj;
37 protected $title;
41 protected $fields;
45 protected $stdFields;
49 protected $records;
53 protected $is_visible;
57 protected $add_perm;
61 protected $edit_perm;
65 protected $delete_perm;
69 protected $edit_by_owner;
77 protected $limited;
81 protected $limit_start;
85 protected $limit_end;
89 protected $export_enabled;
95 protected $default_sort_field = 0;
101 protected $default_sort_field_order = 'asc';
107 protected $description = '';
113 protected $public_comments = 0;
120
121
125 public function __construct($a_id = 0) {
126 if ($a_id != 0) {
127 $this->id = $a_id;
128 $this->doRead();
129 }
130 }
131
132
136 public function doRead() {
137 global $ilDB;
138
139 $query = "SELECT * FROM il_dcl_table WHERE id = " . $ilDB->quote($this->getId(), "integer");
140 $set = $ilDB->query($query);
141 $rec = $ilDB->fetchAssoc($set);
142
143 $this->setObjId($rec["obj_id"]);
144 $this->setTitle($rec["title"]);
145 $this->setAddPerm($rec["add_perm"]);
146 $this->setEditPerm($rec["edit_perm"]);
147 $this->setDeletePerm($rec["delete_perm"]);
148 $this->setEditByOwner($rec["edit_by_owner"]);
149 $this->setExportEnabled($rec["export_enabled"]);
150 $this->setLimited($rec["limited"]);
151 $this->setLimitStart($rec["limit_start"]);
152 $this->setLimitEnd($rec["limit_end"]);
153 $this->setIsVisible($rec["is_visible"]);
154 $this->setDescription($rec['description']);
155 $this->setDefaultSortField($rec['default_sort_field_id']);
156 $this->setDefaultSortFieldOrder($rec['default_sort_field_order']);
157 $this->setPublicCommentsEnabled($rec['public_comments']);
158 $this->setViewOwnRecordsPerm($rec['view_own_records_perm']);
159 $this->setDeleteByOwner($rec['delete_by_owner']);
160 }
161
162
170 public function doDelete($delete_main_table = false) {
171 global $ilDB;
172
174 foreach ($this->getRecords() as $record) {
175 $record->doDelete();
176 }
177
178 foreach ($this->getRecordFields() as $field) {
179 $field->doDelete();
180 }
181
182 // SW: Fix #12794 und #11405
183 // Problem is that when the DC object gets deleted, $this::getCollectionObject() tries to load the DC but it's not in the DB anymore
184 // If $delete_main_table is true, avoid getting the collection object
185 $exec_delete = false;
186 if ($delete_main_table) {
187 $exec_delete = true;
188 }
189 if (! $exec_delete && $this->getCollectionObject()->getMainTableId() != $this->getId()) {
190 $exec_delete = true;
191 }
192 if ($exec_delete) {
193 $query = "DELETE FROM il_dcl_table WHERE id = " . $ilDB->quote($this->getId(), "integer");
194 $ilDB->manipulate($query);
195
196 // Delete also view definitions
197 $set = $ilDB->query('SELECT * FROM il_dcl_view WHERE table_id = ' . $ilDB->quote($this->getId(), 'integer'));
198 $view_ids = array();
199 while ($row = $ilDB->fetchObject($set)) {
200 $view_ids[] = $row->id;
201 }
202 if (count($view_ids)) {
203 $ilDB->manipulate("DELETE FROM il_dcl_viewdefinition WHERE view_id IN (" . implode(',', $view_ids) . ")");
204 }
205 $ilDB->manipulate("DELETE FROM il_dcl_view WHERE table_id = " . $ilDB->quote($this->getId(), 'integer'));
206 }
207 }
208
209
213 public function doCreate($create_views = true) {
214 global $ilDB;
215
216 $id = $ilDB->nextId("il_dcl_table");
217 $this->setId($id);
218 $query = "INSERT INTO il_dcl_table (" . "id" . ", obj_id" . ", title" . ", add_perm" . ", edit_perm" . ", delete_perm" . ", edit_by_owner"
219 . ", limited" . ", limit_start" . ", limit_end" . ", is_visible" . ", export_enabled" . ", default_sort_field_id"
220 . ", default_sort_field_order" . ", description" . ", public_comments" . ", view_own_records_perm" . ", delete_by_owner ) VALUES ("
221 . $ilDB->quote($this->getId(), "integer") . "," . $ilDB->quote($this->getObjId(), "integer") . ","
222 . $ilDB->quote($this->getTitle(), "text") . "," . $ilDB->quote($this->getAddPerm() ? 1 : 0, "integer") . ","
223 . $ilDB->quote($this->getEditPerm() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getDeletePerm() ? 1 : 0, "integer") . ","
224 . $ilDB->quote($this->getEditByOwner() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getLimited() ? 1 : 0, "integer") . ","
225 . $ilDB->quote($this->getLimitStart(), "timestamp") . "," . $ilDB->quote($this->getLimitEnd(), "timestamp") . ","
226 . $ilDB->quote($this->getIsVisible() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getExportEnabled() ? 1 : 0, "integer") . ","
227 . $ilDB->quote($this->getDefaultSortField(), "text") . "," . $ilDB->quote($this->getDefaultSortFieldOrder(), "text") . ","
228 . $ilDB->quote($this->getDescription(), "text") . "," . $ilDB->quote($this->getPublicCommentsEnabled(), "integer") . ","
229 . $ilDB->quote($this->getViewOwnRecordsPerm(), "integer") . "," . $ilDB->quote($this->getDeleteByOwner() ? 1 : 0, 'integer') . ")";
230
231 $ilDB->manipulate($query);
232
233 if ($create_views) {
234 //add view definition
235 $view_id = $ilDB->nextId("il_dcl_view");
236 $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
237 . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::VIEW_VIEW, "integer") . ", "
238 . $ilDB->quote(1, "integer") . ")";
239 $ilDB->manipulate($query);
240
241 //add edit definition
242 $view_id = $ilDB->nextId("il_dcl_view");
243 $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
244 . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::EDIT_VIEW, "integer") . ", "
245 . $ilDB->quote(1, "integer") . ")";
246 $ilDB->manipulate($query);
247
248 //add filter definition
249 $view_id = $ilDB->nextId("il_dcl_view");
250 $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
251 . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::FILTER_VIEW, "integer") . ", "
252 . $ilDB->quote(1, "integer") . ")";
253 $ilDB->manipulate($query);
254
255 //add filter definition
256 $view_id = $ilDB->nextId("il_dcl_view");
257 $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
258 . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::EXPORTABLE_VIEW, "integer") . ", "
259 . $ilDB->quote(1, "integer") . ")";
260 $ilDB->manipulate($query);
261
262 $this->buildOrderFields();
263 }
264 }
265
266
267 /*
268 * doUpdate
269 */
270 public function doUpdate() {
271 global $ilDB;
272
273 $ilDB->update("il_dcl_table", array(
274 "obj_id" => array( "integer", $this->getObjId() ),
275 "title" => array( "text", $this->getTitle() ),
276 "add_perm" => array( "integer", (int) $this->getAddPerm() ),
277 "edit_perm" => array( "integer", (int) $this->getEditPerm() ),
278 "delete_perm" => array( "integer", (int) $this->getDeletePerm() ),
279 "edit_by_owner" => array( "integer", (int) $this->getEditByOwner() ),
280 "limited" => array( "integer", $this->getLimited() ),
281 "limit_start" => array( "timestamp", $this->getLimitStart() ),
282 "limit_end" => array( "timestamp", $this->getLimitEnd() ),
283 "is_visible" => array( "integer", $this->getIsVisible() ? 1 : 0 ),
284 "export_enabled" => array( "integer", $this->getExportEnabled() ? 1 : 0 ),
285 "description" => array( "text", $this->getDescription() ),
286 "default_sort_field_id" => array( "text", $this->getDefaultSortField() ),
287 "default_sort_field_order" => array( "text", $this->getDefaultSortFieldOrder() ),
288 "public_comments" => array( "integer", $this->getPublicCommentsEnabled() ? 1 : 0 ),
289 "view_own_records_perm" => array( "integer", $this->getViewOwnRecordsPerm() ),
290 'delete_by_owner' => array('integer', $this->getDeleteByOwner() ? 1 : 0),
291 ), array(
292 "id" => array( "integer", $this->getId() )
293 ));
294 }
295
296
302 public function setId($a_id) {
303 $this->id = $a_id;
304 }
305
306
312 public function getId() {
313 return $this->id;
314 }
315
316
320 public function setObjId($a_id) {
321 $this->objId = $a_id;
322 }
323
324
328 public function getObjId() {
329 return $this->objId;
330 }
331
332
336 public function setTitle($a_title) {
337 $this->title = $a_title;
338 }
339
340
344 public function getTitle() {
345 return $this->title;
346 }
347
348
352 public function getCollectionObject() {
353 $this->loadObj();
354
355 return $this->obj;
356 }
357
358
359 protected function loadObj() {
360 if ($this->obj == NULL) {
361 $this->obj = new ilObjDataCollection($this->objId, false);
362 }
363 }
364
365
369 public function getRecords() {
370 $this->loadRecords();
371
372 return $this->records;
373 }
374
375
387 public function getRecordsByFilter(array $filter = array()) {
388 $this->loadRecords();
389 // Only pass records trough filter if there is filtering required #performance-improvements
390 if (! count($filter)) {
391 return $this->records;
392 }
393 $filtered = array();
394 foreach ($this->getRecords() as $record) {
395 if ($record->passThroughFilter($filter)) {
396 $filtered[] = $record;
397 }
398 }
399
400 return $filtered;
401 }
402
403
404 protected function loadRecords() {
405 if ($this->records == NULL) {
406 global $ilDB;
407
408 $records = array();
409 $query = "SELECT id FROM il_dcl_record WHERE table_id = " . $ilDB->quote($this->id, "integer");
410 $set = $ilDB->query($query);
411
412 while ($rec = $ilDB->fetchAssoc($set)) {
413 $records[$rec['id']] = ilDataCollectionCache::getRecordCache($rec['id']);
414 }
415
416 $this->records = $records;
417 }
418 }
419
420 //TODO: replace this method with DataCollection->getTables()
426 public function getAll($a_id) {
427 global $ilDB;
428
429 // build query
430 $query = "SELECT * FROM il_dcl_table WHERE obj_id = " . $ilDB->quote($a_id, "integer");
431 $set = $ilDB->query($query);
432
433 $all = array();
434 while ($rec = $ilDB->fetchAssoc($set)) {
435 $all[$rec['id']] = $rec;
436 }
437
438 return $all;
439 }
440
441
445 public function deleteField($field_id) {
446 $field = ilDataCollectionCache::getFieldCache($field_id);
447 $records = $this->getRecords();
448
449 foreach ($records as $record) {
450 $record->deleteField($field_id);
451 }
452
453 $field->doDelete();
454 }
455
456
462 public function getField($field_id) {
463 $fields = $this->getFields();
464 $field = NULL;
465 foreach ($fields as $field_1) {
466 if ($field_1->getId() == $field_id) {
467 $field = $field_1;
468 }
469 }
470
471 return $field;
472 }
473
474
478 public function getFieldIds() {
479 return array_keys($this->getFields());
480 }
481
482
483 protected function loadFields() {
484 if ($this->fields === NULL) {
485 global $ilDB;
486
487 $query = "SELECT DISTINCT field.* FROM il_dcl_field AS field
488 INNER JOIN il_dcl_view AS view ON view.table_id = field.table_id
489 INNER JOIN il_dcl_viewdefinition AS def ON def.view_id = view.id
490 WHERE field.table_id =" . $ilDB->quote($this->getId(), "integer") . "
491 ORDER BY def.field_order DESC";
492 $fields = array();
493 $set = $ilDB->query($query);
494
495 while ($rec = $ilDB->fetchAssoc($set)) {
497 $fields[$field->getId()] = $field;
498 }
499 $this->sortByOrder($fields);
500 $this->fields = $fields;
501 }
502 }
503
504
510 public function getNewOrder() {
511 $fields = $this->getFields();
512 $place = 0;
513 foreach ($fields as $field) {
514 if (! $field->isStandardField()) {
515 $place = $field->getOrder() + 1;
516 }
517 }
518
519 return $place;
520 }
521
522
528 public function getFields() {
529 $this->loadFields();
530 $this->stdFields = $this->getStandardFields();
531 $fields = array_merge($this->fields, $this->stdFields);
532 $this->sortByOrder($fields);
533
534 return $fields;
535 }
536
537
543 public function getFieldsForFormula() {
544 $unsupported = array(
553 );
554
555 $this->loadFields();
556 $return = $this->getStandardFields();
560 foreach ($this->fields as $field) {
561 if (! in_array($field->getDatatypeId(), $unsupported)) {
562 $return[] = $field;
563 }
564 }
565
566 return $return;
567 }
568
569
575 public function getStandardFields() {
576 if ($this->stdFields == NULL) {
577 $this->stdFields = ilDataCollectionStandardField::_getStandardFields($this->id);
578 // Don't return comments as field if this feature is not activated in the settings
579 if (! $this->getPublicCommentsEnabled()) {
581 foreach ($this->stdFields as $k => $field) {
582 if ($field->getId() == 'comments') {
583 unset($this->stdFields[$k]);
584 break;
585 }
586 }
587 }
588 }
589
590 return $this->stdFields;
591 }
592
593
599 public function getRecordFields() {
600 $this->loadFields();
601
602 return $this->fields;
603 }
604
605
611 public function getVisibleFields() {
612 $fields = $this->getFields();
613
614 $visibleFields = array();
615
616 foreach ($fields as $field) {
617 if ($field->isVisible()) {
618 $visibleFields[] = $field;
619 }
620 }
621
622 return $visibleFields;
623 }
624
625
629 public function getEditableFields() {
630 $fields = $this->getRecordFields();
631 $editableFields = array();
632
633 foreach ($fields as $field) {
634 if (! $field->getLocked()) {
635 $editableFields[] = $field;
636 }
637 }
638
639 return $editableFields;
640 }
641
642
649 public function getFilterableFields() {
650 $fields = $this->getFields();
651 $filterableFields = array();
652
653 foreach ($fields as $field) {
654 if ($field->isFilterable()) {
655 $filterableFields[] = $field;
656 }
657 }
658
659 return $filterableFields;
660 }
661
662
668 public function getExportableFields() {
669 $fields = $this->getFields();
670 $exportableFields = array();
671 foreach ($fields as $field) {
672 if ($field->getExportable()) {
673 $exportableFields[] = $field;
674 }
675 }
676
677 return $exportableFields;
678 }
679
680
688 }
689
690
698 }
699
700
709 return true;
710 }
712 return false;
713 }
714
715 return ($this->getAddPerm() AND $this->checkLimit());
716 }
717
718
727 return true;
728 }
730 return false;
731 }
732 if (!$this->checkLimit()) {
733 return false;
734 }
735 if ($this->getEditPerm() && !$this->getEditByOwner()) {
736 return true;
737 }
738 if ($this->getEditByOwner()) {
739 return $this->doesRecordBelongToUser($record);
740 }
741
742 return false;
743 }
744
745
754 return true;
755 }
757 return false;
758 }
759 if (!$this->checkLimit()) {
760 return false;
761 }
762 if ($this->getDeletePerm() && !$this->getDeleteByOwner()) {
763 return true;
764 }
765 if ($this->getDeleteByOwner()) {
766 return $this->doesRecordBelongToUser($record);
767 }
768
769 return false;
770 }
771
772
780 }
781
782
789 public function hasPermissionToViewRecord($ref_id, $record) {
790 global $ilUser, $rbacreview;
793 return true;
794 }
796 // Check for view only own entries setting
797 if ($this->getViewOwnRecordsPerm() && $ilUser->getId() != $record->getOwner()) {
798 return false;
799 }
800
801 return true;
802 }
803
804 return false;
805 }
806
807
813 protected function doesRecordBelongToUser(ilDataCollectionRecord $record) {
814 global $ilUser;
815
816 return ($ilUser->getId() == $record->getOwner());
817 }
818
819
823 protected function checkLimit() {
824 if ($this->getLimited()) {
825 $now = new ilDateTime(date("Y-m-d H:i:s"), IL_CAL_DATE);
826 $from = new ilDateTime($this->getLimitStart(), IL_CAL_DATE);
827 $to = new ilDateTime($this->getLimitEnd(), IL_CAL_DATE);
828 return ($from <= $now && $now <= $to);
829 }
830
831 return true;
832 }
833
834
838 public function updateFields() {
839 foreach ($this->getFields() as $field) {
840 $field->doUpdate();
841 }
842 }
843
844
850 public function sortFields(&$fields) {
851 $this->sortByOrder($fields);
852 //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.
853 $named = array();
854 foreach ($fields as $field) {
855 $named[$field->getId()] = $field;
856 }
857
858 $fields = $named;
859 }
860
861
866 protected function sortByOrder(&$array) {
867 usort($array, array( $this, "compareOrder" ));
868 }
869
870
875 public function buildOrderFields() {
876 $fields = $this->getFields();
877 $this->sortByOrder($fields);
878 $count = 10;
879 $offset = 10;
880 foreach ($fields as $field) {
881 if (! is_null($field->getOrder())) {
882 $field->setOrder($count);
883 $count = $count + $offset;
884 $field->doUpdate();
885 }
886 }
887 }
888
889
897 public function getFieldByTitle($title) {
898 $return = NULL;
899 foreach ($this->getFields() as $field) {
900 if ($field->getTitle() == $title) {
901 $return = $field;
902 break;
903 }
904 }
905
906 return $return;
907 }
908
909
913 public function setAddPerm($add_perm) {
914 $this->add_perm = $add_perm;
915 }
916
917
921 public function getAddPerm() {
922 return (bool) $this->add_perm;
923 }
924
925
929 public function setDeletePerm($delete_perm) {
930 $this->delete_perm = $delete_perm;
931 if (!$delete_perm) {
932 $this->setDeleteByOwner(false);
933 }
934 }
935
936
940 public function getDeletePerm() {
941
942 return (bool) $this->delete_perm;
943 }
944
945
950 $this->edit_by_owner = $edit_by_owner;
951 if ($edit_by_owner) {
952 $this->setEditPerm(true);
953 }
954 }
955
956
960 public function getEditByOwner() {
961 return (bool) $this->edit_by_owner;
962 }
963
964
968 public function getDeleteByOwner()
969 {
970 return (bool) $this->delete_by_owner;
971 }
972
973
978 {
979 $this->delete_by_owner = $delete_by_owner;
980 if ($delete_by_owner) {
981 $this->setDeletePerm(true);
982 }
983 }
984
985
989 public function setEditPerm($edit_perm) {
990 $this->edit_perm = $edit_perm;
991 if (!$edit_perm) {
992 $this->setEditByOwner(false);
993 }
994 }
995
996
1000 public function getEditPerm() {
1001 return (bool) $this->edit_perm;
1002 }
1003
1004
1008 public function setLimited($limited) {
1009 $this->limited = $limited;
1010 }
1011
1012
1016 public function getLimited() {
1017 return $this->limited;
1018 }
1019
1020
1024 public function setLimitEnd($limit_end) {
1025 $this->limit_end = $limit_end;
1026 }
1027
1028
1032 public function getLimitEnd() {
1033 return $this->limit_end;
1034 }
1035
1036
1040 public function setLimitStart($limit_start) {
1041 $this->limit_start = $limit_start;
1042 }
1043
1044
1048 public function getLimitStart() {
1049 return $this->limit_start;
1050 }
1051
1052
1056 public function setIsVisible($is_visible) {
1057 $this->is_visible = $is_visible;
1058 }
1059
1060
1064 public function getIsVisible() {
1065 return $this->is_visible;
1066 }
1067
1068
1072 public function setDescription($description) {
1073 $this->description = $description;
1074 }
1075
1076
1080 public function getDescription() {
1081 return $this->description;
1082 }
1083
1084
1091 $default_sort_field = ($default_sort_field) ? $default_sort_field : 0; // Change null or empty strings to zero
1092 $this->default_sort_field = $default_sort_field;
1093 }
1094
1095
1099 public function getDefaultSortField() {
1101 }
1102
1103
1108 if (! in_array($default_sort_field_order, array( 'asc', 'desc' ))) {
1110 }
1111 $this->default_sort_field_order = $default_sort_field_order;
1112 }
1113
1114
1118 public function getDefaultSortFieldOrder() {
1120 }
1121
1122
1127 $this->public_comments = $public_comments;
1128 }
1129
1130
1134 public function getPublicCommentsEnabled() {
1136 }
1137
1138
1142 public function setViewOwnRecordsPerm($view_own_perm) {
1143 $this->view_own_records_perm = (int)$view_own_perm;
1144 }
1145
1146
1150 public function getViewOwnRecordsPerm() {
1151 return (bool)$this->view_own_records_perm;
1152 }
1153
1154
1160 public function hasCustomFields() {
1161 $this->loadFields();
1162
1163 return (count($this->fields) > 0) ? true : false;
1164 }
1165
1166
1167 function compareOrder($a, $b) {
1168 if (is_null($a->getOrder() == NULL) && is_null($b->getOrder() == NULL)) {
1169 return 0;
1170 }
1171 if (is_null($a->getOrder())) {
1172 return 1;
1173 }
1174 if (is_null($b->getOrder())) {
1175 return - 1;
1176 }
1177
1178 return $a->getOrder() < $b->getOrder() ? - 1 : 1;
1179 }
1180
1181
1185 public function cloneStructure(ilDataCollectionTable $original) {
1186 $this->setTitle($original->getTitle());
1187 $this->setDescription($original->getDescription());
1188 $this->setIsVisible($original->getIsVisible());
1189 $this->setEditByOwner($original->getEditByOwner());
1190 $this->setAddPerm($original->getAddPerm());
1191 $this->setEditPerm($original->getEditPerm());
1192 $this->setDeleteByOwner($original->getDeleteByOwner());
1193 $this->setDeletePerm($original->getDeletePerm());
1194 $this->setLimited($original->getLimited());
1195 $this->setLimitStart($original->getLimitStart());
1196 $this->setLimitEnd($original->getLimitEnd());
1197 $this->setViewOwnRecordsPerm($original->getViewOwnRecordsPerm());
1198 $this->setExportEnabled($original->getExportEnabled());
1201
1202 $this->doCreate();
1203 // reset stdFields to get new for the created object
1204
1206 // Clone standard-fields
1207 $org_std_fields = $original->getStandardFields();
1208 foreach ($this->getStandardFields() as $element_key => $std_field) {
1209 $std_field->cloneStructure($org_std_fields[$element_key]);
1210 if ($std_field->getId() === $original->getDefaultSortField()) {
1211 $default_sort_field = $std_field->getId();
1212 }
1213 }
1214
1215 // Clone fields
1216 $new_fields = array();
1217 foreach ($original->getFields() as $orig_field) {
1218 if (! $orig_field->isStandardField()) {
1219 $new_field = new ilDataCollectionField();
1220 $new_field->setTableId($this->getId());
1221 $new_field->cloneStructure($orig_field->getId());
1222 $new_fields[$orig_field->getId()] = $new_field;
1223
1224 if ($orig_field->getId() == $original->getDefaultSortField()) {
1225 $default_sort_field = $new_field->getId();
1226 }
1227 }
1228 }
1229
1231 $this->doUpdate();
1232
1233 //TODO: Find better way to copy data (include referenced data)
1234 // Clone Records with recordfields
1235 /*foreach($original->getRecords() as $orig_record){
1236 $new_record = new ilDataCollectionRecord();
1237 $new_record->setTableId($this->getId());
1238 $new_record->cloneStructure($orig_record->getId(), $new_fields);
1239 }*/
1240
1241 if ($old_view_id = ilDataCollectionRecordViewViewdefinition::getIdByTableId($original->getId())) {
1242 $old_view = new ilDataCollectionRecordViewViewdefinition($old_view_id);
1243 $old_view->setTableId($original->getId());
1245 $viewdef->setTableId($this->id);
1246 $viewdef->setXMLContent($old_view->getXMLContent(false));
1247 $viewdef->create();
1248 }
1249 }
1250
1251
1257 public function _hasRecords() {
1258 return (count($this->getRecords()) > 0) ? true : false;
1259 }
1260
1261
1265 public function addField($field) {
1266 $this->fields[$field->getId()] = $field;
1267 }
1268
1269
1275 public static function _tableExists($table_id) {
1276 global $ilDB;
1277 $query = "SELECT * FROM il_dcl_table WHERE id = " . $table_id;
1278 $result = $ilDB->query($query);
1279
1280 return $result->numRows() != 0;
1281 }
1282
1283
1290 public static function _getTableIdByTitle($title, $obj_id) {
1291 global $ilDB;
1292 $result = $ilDB->query('SELECT id FROM il_dcl_table WHERE title = ' . $ilDB->quote($title, 'text') . ' AND obj_id = '
1293 . $ilDB->quote($obj_id, 'integer'));
1294 $id = 0;
1295 while ($rec = $ilDB->fetchAssoc($result)) {
1296 $id = $rec['id'];
1297 }
1298
1299 return $id;
1300 }
1301
1302
1307 $this->export_enabled = $export_enabled;
1308 }
1309
1310
1314 public function getExportEnabled() {
1315 return $this->export_enabled;
1316 }
1317
1318
1327 public static function _hasFieldByTitle($title, $obj_id) {
1328 global $ilDB;
1329 $result = $ilDB->query('SELECT * FROM il_dcl_field WHERE table_id = ' . $ilDB->quote($obj_id, 'integer') . ' AND title = '
1330 . $ilDB->quote($title, 'text'));
1331
1332 return ($ilDB->numRows($result)) ? true : false;
1333 }
1334
1335
1347 public function getPartialRecords($sort, $direction, $limit, $offset, array $filter = array()) {
1348 global $ilDB, $ilUser, $rbacreview;
1349
1350 $sort_field = ($sort) ? $this->getFieldByTitle($sort) : $this->getField('id');
1351 $direction = strtolower($direction);
1352 $direction = (in_array($direction, array( 'desc', 'asc' ))) ? $direction : 'asc';
1353
1354 // Sorting by a status from an ILIAS Ref field. This column is added dynamically to the table, there is no field model
1355 $sort_by_status = false;
1356 if (substr($sort, 0, 8) == '_status_') {
1357 $sort_by_status = true;
1358 $sort_field = $this->getFieldByTitle(substr($sort, 8));
1359 }
1360
1361 if (is_null($sort_field)) {
1362 $sort_field = $this->getField('id');
1363 }
1364
1365 $id = $sort_field->getId();
1366 $stl = $sort_field->getStorageLocation();
1367 $select_str = '';
1368 $join_str = '';
1369 $where_additions = '';
1370 $has_nref = false;
1371
1372 if ($sort_field->isStandardField() && $id != 'comments') {
1373 if ($id == 'owner' || $id == 'last_edit_by') {
1374 $join_str .= "LEFT JOIN usr_data AS sort_usr_data_{$id} ON (sort_usr_data_{$id}.usr_id = record.{$id})";
1375 $select_str .= " sort_usr_data_{$id}.login AS field_{$id},";
1376 } else {
1377 $select_str .= " record.{$id} AS field_{$id},";
1378 }
1379 } else {
1380 switch ($sort_field->getDatatypeId()) {
1382 $rating_joined = true;
1383 // FSX Bugfix 0015735: The average is multiplied with 10000 and added to the amount of votes
1384 $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";
1385 $select_str .= " average.rating AS field_{$id},";
1386 break;
1388 $join_str .=
1389 "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 = "
1390 . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
1391 $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) ";
1392 $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)";
1393 $join_str .= "LEFT JOIN object_data AS sort_object_data_{$id} ON (sort_object_data_{$id}.obj_id = sort_object_reference_{$id}.obj_id)";
1394 if ($sort_by_status) {
1395 global $ilUser;
1396 $join_str .= "LEFT JOIN ut_lp_marks AS ut ON (ut.obj_id = sort_object_data_{$id}.obj_id AND ut.usr_id = "
1397 . $ilDB->quote($ilUser->getId(), 'integer') . ") ";
1398 }
1399 $select_str .= (! $sort_by_status) ? " sort_object_data_{$id}.title AS field_{$id}," : " ut.status AS field_{$id}";
1400 break;
1403 $join_str .=
1404 "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 = "
1405 . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
1406 $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) ";
1407 $join_str .= "LEFT JOIN object_data AS sort_object_data_{$id} ON (sort_object_data_{$id}.obj_id = sort_stloc_{$id}.value) ";
1408 $select_str .= " sort_object_data_{$id}.title AS field_{$id},";
1409 break;
1411 $prop = $sort_field->getPropertyvalues();
1412 $ref_field = ilDataCollectionCache::getFieldCache($sort_field->getFieldRef());
1414 if ($n_ref) {
1415 $has_nref = true;
1416 }
1417 $select_str .= ($n_ref) ? " GROUP_CONCAT(stloc_{$id}_joined.value) AS field_{$id}" : "stloc_{$id}_joined.value AS field_{$id},";
1418 $join_str .=
1419 "LEFT JOIN il_dcl_record_field AS record_field_{$id} ON (record_field_{$id}.record_id = record.id AND record_field_{$id}.field_id = "
1420 . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
1421 $join_str .= "LEFT JOIN il_dcl_stloc{$stl}_value AS stloc_{$id} ON (stloc_{$id}.record_field_id = record_field_{$id}.id) ";
1422 $join_str .=
1423 "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 = "
1424 . $ilDB->quote($ref_field->getId(), 'integer') . ") ";
1425 $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) ";
1426 break;
1431 $select_str .= " sort_stloc_{$id}.value AS field_{$id},";
1432 $join_str .=
1433 "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 = "
1434 . $ilDB->quote($sort_field->getId(), 'integer') . ") ";
1435 $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) ";
1436 break;
1437 }
1438 }
1439
1440 if (count($filter)) {
1441 foreach ($filter as $key => $filter_value) {
1442 $filter_field_id = substr($key, 7);
1443 $filter_field = $this->getField($filter_field_id);
1444 switch ($filter_field->getDatatypeId()) {
1446 if(!$rating_joined) {
1447 $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";
1448 }
1449 // FSX Bugfix 0015735: The average is multiplied with 10000 and added to the amount of votes
1450 $where_additions .= " AND average.rating >= " . $ilDB->quote($filter_value * 10000, 'integer');
1451 break;
1453 $join_str .=
1454 " 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 = "
1455 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1456 $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) ";
1457 $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 ) ";
1458 $join_str .=
1459 " 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 "
1460 . $ilDB->quote("%$filter_value%", 'text') . ") ";
1461 break;
1464 $join_str .=
1465 " 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 = "
1466 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1467 $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) ";
1468 $join_str .=
1469 " 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 "
1470 . $ilDB->quote("%$filter_value%", 'text') . ") ";
1471 break;
1473 $date_from = (isset($filter_value['from']) && is_object($filter_value['from'])) ? $filter_value['from'] : NULL;
1474 $date_to = (isset($filter_value['to']) && is_object($filter_value['to'])) ? $filter_value['to'] : NULL;
1475 if ($filter_field->isStandardField()) {
1476 if ($date_from) {
1477 $where_additions .= " AND (record.{$filter_field_id} >= " . $ilDB->quote($date_from, 'date') . ")";
1478 }
1479 if ($date_to) {
1480 $where_additions .= " AND (record.{$filter_field_id} <= " . $ilDB->quote($date_to, 'date') . ")";
1481 }
1482 } else {
1483 $join_str .=
1484 " 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 = "
1485 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1486 $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 ";
1487 if ($date_from) {
1488 $join_str .= "AND filter_stloc_{$filter_field_id}.value >= " . $ilDB->quote($date_from, 'date') . " ";
1489 }
1490 if ($date_to) {
1491 $join_str .= "AND filter_stloc_{$filter_field_id}.value <= " . $ilDB->quote($date_to, 'date') . " ";
1492 }
1493 $join_str .= ") ";
1494 }
1495 break;
1497 $from = (isset($filter_value['from'])) ? (int)$filter_value['from'] : NULL;
1498 $to = (isset($filter_value['to'])) ? (int)$filter_value['to'] : NULL;
1499 if ($filter_field->isStandardField()) {
1500 if (! is_null($from)) {
1501 $where_additions .= " AND record.{$filter_field_id} >= " . $ilDB->quote($from, 'integer');
1502 }
1503 if (! is_null($to)) {
1504 $where_additions .= " AND record.{$filter_field_id} <= " . $ilDB->quote($to, 'integer');
1505 }
1506 } else {
1507 $join_str .=
1508 " 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 = "
1509 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1510 $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";
1511 if (! is_null($from)) {
1512 $join_str .= " AND filter_stloc_{$filter_field_id}.value >= " . $ilDB->quote($from, 'integer');
1513 }
1514 if (! is_null($to)) {
1515 $join_str .= " AND filter_stloc_{$filter_field_id}.value <= " . $ilDB->quote($to, 'integer');
1516 }
1517 $join_str .= ") ";
1518 }
1519 break;
1521 if ($filter_value == "checked") {
1522 $join_str .=
1523 " 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 = "
1524 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1525 $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";
1526 $join_str .= " AND filter_stloc_{$filter_field_id}.value = " . $ilDB->quote(1, 'integer');
1527 } else {
1528 $join_str .=
1529 " 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 = "
1530 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1531 $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";
1532 $where_additions .= " AND (filter_stloc_{$filter_field_id}.value <> " . $ilDB->quote(1, 'integer')
1533 . " OR filter_stloc_{$filter_field_id}.value is NULL)";
1534 }
1535 $join_str .= " ) ";
1536 break;
1538 if ($filter_field->isStandardField()) {
1539 $join_str .=
1540 " 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 "
1541 . $ilDB->quote("%$filter_value%", 'text') . ") ";
1542 } else {
1543 $join_str .=
1544 " 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 = "
1545 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1546 $join_str .=
1547 " 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 "
1548 . $ilDB->quote("%$filter_value%", 'text') . ") ";
1549 }
1550 break;
1552 $join_str .=
1553 " 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 = "
1554 . $ilDB->quote($filter_field_id, 'integer') . ") ";
1555 $prop = $filter_field->getPropertyvalues();
1557 if ($n_ref) {
1558 $join_str .=
1559 " 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 "
1560 . $ilDB->quote("%$filter_value%", 'text') . ") ";
1561 } else {
1562 $join_str .=
1563 " 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 = "
1564 . $ilDB->quote($filter_value, 'integer') . ") ";
1565 }
1566 break;
1567 }
1568 }
1569 }
1570
1571 // Build the query string
1572 $sql = "SELECT DISTINCT record.id, record.owner";
1573 if($select_str) {
1574 $sql .= ', ';
1575 } $sql .= rtrim($select_str, ',') . " FROM il_dcl_record AS record ";
1576 $sql .= $join_str;
1577 $sql .= " WHERE record.table_id = " . $ilDB->quote($this->getId(), 'integer') . $where_additions;
1578 if ($has_nref) {
1579 $sql .= " GROUP BY record.id, record.owner";
1580 }
1581 $props = $sort_field->getProperties();
1582 if($id != 'comments' && $sort_field->getDatatypeId() != ilDataCollectionDatatype::INPUTFORMAT_FORMULA && !$props[ilDataCollectionField::PROPERTYID_URL]) {
1583 $sql .= " ORDER BY field_{$id} {$direction}";
1584 }
1585 $set = $ilDB->query($sql);
1586 $total_record_ids = array();
1587 // Save record-ids in session to enable prev/next links in detail view
1588 $_SESSION['dcl_record_ids'] = array();
1589 $_SESSION['dcl_table_id'] = $this->getId();
1590 $is_allowed_to_view = ilObjDataCollectionAccess::hasWriteAccess(array_pop(ilObject::_getAllReferences($this->getObjId())));
1591 while ($rec = $ilDB->fetchAssoc($set)) {
1592 // Quick check if the current user is allowed to view the record
1593 if (!$is_allowed_to_view && ($this->getViewOwnRecordsPerm() && $ilUser->getId() != $rec['owner'])) {
1594 continue;
1595 }
1596 $total_record_ids[] = $rec['id'];
1597 $_SESSION['dcl_record_ids'][] = $rec['id'];
1598 }
1599 // Sort by formula
1600 if ($sort_field->getDatatypeId() == ilDataCollectionDatatype::INPUTFORMAT_FORMULA) {
1601 $sort_array = array();
1602 foreach ($total_record_ids as $id) {
1603 $formula_field = ilDataCollectionCache::getRecordFieldCache(new ilDataCollectionRecord($id), $sort_field);
1604 $sort_array[$id] = $formula_field->getValue();
1605 }
1606 switch ($direction) {
1607 case 'asc':
1608 case 'ASC':
1609 asort($sort_array);
1610 break;
1611 case 'desc':
1612 case 'DESC':
1613 arsort($sort_array);
1614 break;
1615 }
1616 $total_record_ids = array_keys($sort_array);
1617 }
1618 //Sort by URL-title if existent
1620 $sort_array = array();
1621 foreach ($total_record_ids as $id) {
1623 $sort_array[$id] = $url_field->getSortingValue();
1624 }
1625 switch ($direction) {
1626 case 'asc':
1627 case 'ASC':
1628 asort($sort_array);
1629 break;
1630 case 'desc':
1631 case 'DESC':
1632 arsort($sort_array);
1633 break;
1634 }
1635 $total_record_ids = array_keys($sort_array);
1636 }
1637 // Now slice the array to load only the needed records in memory
1638 $record_ids = array_slice($total_record_ids, $offset, $limit);
1639 $records = array();
1640 foreach ($record_ids as $id) {
1642 }
1643
1644 return array( 'records' => $records, 'total' => count($total_record_ids) );
1645 }
1646}
$result
$_SESSION["AccountId"]
const IL_CAL_DATE
static getRecordFieldCache($record, $field)
static getRecordCache($record_id=0)
Class ilDataCollectionField.
static getIdByTableId($a_table_id)
Get view definition id by table id.
Class ilDataCollectionRecord.
Class ilDataCollectionField.
hasPermissionToDeleteRecord($ref_id, ilDataCollectionRecord $record)
getExportableFields()
Return all the fields that are marked as exportable.
getRecordsByFilter(array $filter=array())
getRecordsByFilter
static _hasFieldByTitle($title, $obj_id)
Checks if a table has a field with the given title.
cloneStructure(ilDataCollectionTable $original)
getFilterableFields()
getFilterableFields Returns all fields of this table who have set their filterable to true,...
getRecordFields()
Returns all fields of this table which are NOT standard fields.
getPartialRecords($sort, $direction, $limit, $offset, array $filter=array())
Return only the needed subset of record objects for the table, according to sorting,...
getFieldByTitle($title)
Get a field by title.
getFields()
Returns all fields of this table including the standard fields.
setDefaultSortField($default_sort_field)
/**
buildOrderFields()
buildOrderFields orders the fields.
static _getTableIdByTitle($title, $obj_id)
hasPermissionToEditRecord($ref_id, ilDataCollectionRecord $record)
getVisibleFields()
Returns all fields of this table who have set their visibility to true, including standard fields.
doesRecordBelongToUser(ilDataCollectionRecord $record)
setDefaultSortFieldOrder($default_sort_field_order)
@classDescription Date and time handling
Class ilObjDataCollection.
static _getAllReferences($a_id)
get all reference ids of object
$ref_id
Definition: sahs_server.php:39
global $ilDB
$errors fields
Definition: imgupload.php:48
global $ilUser
Definition: imgupload.php:15