ILIAS  Release_4_4_x_branch Revision 61816
 All Data Structures Namespaces Files Functions Variables Groups Pages
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 
5 include_once './Modules/DataCollection/classes/class.ilDataCollectionStandardField.php';
6 include_once './Modules/DataCollection/classes/class.ilDataCollectionRecord.php';
7 
19 class ilDataCollectionTable {
20 
21  protected $id; // [int]
22  protected $objId; // [int]
23  protected $obj;
24  protected $title; // [string]
25  private $fields; // [array][ilDataCollectionField]
26  private $stdFields;
27  private $records;
31  private $is_visible;
35  private $add_perm;
39  private $edit_perm;
43  private $delete_perm;
47  private $edit_by_owner;
51  private $limited;
55  private $limit_start;
59  private $limit_end;
63  protected $export_enabled;
67  protected $description;
68 
69 
78  public function __construct($a_id = 0) {
79  if ($a_id != 0) {
80  $this->id = $a_id;
81  $this->doRead();
82  }
83  }
84 
85 
89  public function doRead() {
90  global $ilDB;
91 
92  $query = "SELECT * FROM il_dcl_table WHERE id = " . $ilDB->quote($this->getId(), "integer");
93  $set = $ilDB->query($query);
94  $rec = $ilDB->fetchAssoc($set);
95 
96  $this->setObjId($rec["obj_id"]);
97  $this->setTitle($rec["title"]);
98  $this->setAddPerm($rec["add_perm"]);
99  $this->setEditPerm($rec["edit_perm"]);
100  $this->setDeletePerm($rec["delete_perm"]);
101  $this->setEditByOwner($rec["edit_by_owner"]);
102  $this->setExportEnabled($rec["export_enabled"]);
103  $this->setLimited($rec["limited"]);
104  $this->setLimitStart($rec["limit_start"]);
105  $this->setLimitEnd($rec["limit_end"]);
106  $this->setIsVisible($rec["is_visible"]);
107  $this->setDescription($rec['description']);
108  }
109 
110 
118  public function doDelete($delete_main_table = false) {
119  global $ilDB;
120 
121  foreach ($this->getRecords() as $record) {
122  $record->doDelete();
123  }
124 
125  foreach ($this->getRecordFields() as $field) {
126  $field->doDelete();
127  }
128 
129  // SW: Fix #12794 und #11405
130  // Problem is that when the DC object gets deleted, $this::getCollectionObject() tries to load the DC but it's not in the DB anymore
131  // If $delete_main_table is true, avoid getting the collection object
132  $exec_delete = false;
133  if ($delete_main_table) {
134  $exec_delete = true;
135  }
136  if (! $exec_delete && $this->getCollectionObject()->getMainTableId() != $this->getId()) {
137  $exec_delete = true;
138  }
139  if ($exec_delete) {
140  $query = "DELETE FROM il_dcl_table WHERE id = " . $ilDB->quote($this->getId(), "integer");
141  $ilDB->manipulate($query);
142 
143  // Delete also view definitions
144  $set = $ilDB->query('SELECT * FROM il_dcl_view WHERE table_id = ' . $ilDB->quote($this->getId(), 'integer'));
145  $view_ids = array();
146  while ($row = $ilDB->fetchObject($set)) {
147  $view_ids[] = $row->id;
148  }
149  if (count($view_ids)) {
150  $ilDB->manipulate("DELETE FROM il_dcl_viewdefinition WHERE view_id IN (" . implode(',', $view_ids) . ")");
151  }
152  $ilDB->manipulate("DELETE FROM il_dcl_view WHERE table_id = " . $ilDB->quote($this->getId(), 'integer'));
153  }
154  }
155 
156 
160  public function doCreate() {
161  global $ilDB;
162 
163  $id = $ilDB->nextId("il_dcl_table");
164  $this->setId($id);
165  $query = "INSERT INTO il_dcl_table (" . "id" . ", obj_id" . ", title" . ", add_perm" . ", edit_perm" . ", delete_perm" . ", edit_by_owner"
166  . ", limited" . ", limit_start" . ", limit_end" . ", is_visible" . ", export_enabled" . ", description" . " ) VALUES ("
167  . $ilDB->quote($this->getId(), "integer") . "," . $ilDB->quote($this->getObjId(), "integer") . ","
168  . $ilDB->quote($this->getTitle(), "text") . "," . $ilDB->quote($this->getAddPerm() ? 1 : 0, "integer") . ","
169  . $ilDB->quote($this->getEditPerm() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getDeletePerm() ? 1 : 0, "integer") . ","
170  . $ilDB->quote($this->getEditByOwner() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getLimited() ? 1 : 0, "integer") . ","
171  . $ilDB->quote($this->getLimitStart(), "timestamp") . "," . $ilDB->quote($this->getLimitEnd(), "timestamp") . ","
172  . $ilDB->quote($this->getIsVisible() ? 1 : 0, "integer") . "," . $ilDB->quote($this->getExportEnabled() ? 1 : 0, "integer") . ","
173  . $ilDB->quote($this->getDescription(), "text") . ")";
174  $ilDB->manipulate($query);
175 
176  //add view definition
177  $view_id = $ilDB->nextId("il_dcl_view");
178  $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
179  . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::VIEW_VIEW, "integer") . ", "
180  . $ilDB->quote(1, "integer") . ")";
181  $ilDB->manipulate($query);
182 
183  //add edit definition
184  $view_id = $ilDB->nextId("il_dcl_view");
185  $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
186  . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::EDIT_VIEW, "integer") . ", "
187  . $ilDB->quote(1, "integer") . ")";
188  $ilDB->manipulate($query);
189 
190  //add filter definition
191  $view_id = $ilDB->nextId("il_dcl_view");
192  $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
193  . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::FILTER_VIEW, "integer") . ", "
194  . $ilDB->quote(1, "integer") . ")";
195  $ilDB->manipulate($query);
196 
197  //add filter definition
198  $view_id = $ilDB->nextId("il_dcl_view");
199  $query = "INSERT INTO il_dcl_view (id, table_id, type, formtype) VALUES (" . $ilDB->quote($view_id, "integer") . ", "
200  . $ilDB->quote($this->id, "integer") . ", " . $ilDB->quote(ilDataCollectionField::EXPORTABLE_VIEW, "integer") . ", "
201  . $ilDB->quote(1, "integer") . ")";
202  $ilDB->manipulate($query);
203 
204  $this->buildOrderFields();
205  }
206 
207 
208  /*
209  * doUpdate
210  */
211  public function doUpdate() {
212  global $ilDB;
213 
214  $ilDB->update("il_dcl_table", array(
215  "obj_id" => array( "integer", $this->getObjId() ),
216  "title" => array( "text", $this->getTitle() ),
217  "add_perm" => array( "integer", $this->getAddPerm() ),
218  "edit_perm" => array( "integer", $this->getEditPerm() ),
219  "delete_perm" => array( "integer", $this->getDeletePerm() ),
220  "edit_by_owner" => array( "integer", $this->getEditByOwner() ),
221  "limited" => array( "integer", $this->getLimited() ),
222  "limit_start" => array( "timestamp", $this->getLimitStart() ),
223  "limit_end" => array( "timestamp", $this->getLimitEnd() ),
224  "is_visible" => array( "integer", $this->getIsVisible() ? 1 : 0 ),
225  "export_enabled" => array( "integer", $this->getExportEnabled() ? 1 : 0 ),
226  "description" => array( "text", $this->getDescription() ),
227  ), array(
228  "id" => array( "integer", $this->getId() )
229  ));
230  }
231 
232 
238  public function setId($a_id) {
239  $this->id = $a_id;
240  }
241 
242 
248  public function getId() {
249  return $this->id;
250  }
251 
252 
258  public function setObjId($a_id) {
259  $this->objId = $a_id;
260  }
261 
262 
268  public function getObjId() {
269  return $this->objId;
270  }
271 
272 
278  public function setTitle($a_title) {
279  $this->title = $a_title;
280  }
281 
282 
288  public function getTitle() {
289  return $this->title;
290  }
291 
292 
298  public function getCollectionObject() {
299  $this->loadObj();
300 
301  return $this->obj;
302  }
303 
304 
305  /*
306  * loadObj
307  */
308  private function loadObj() {
309  if ($this->obj == NULL) {
310  $this->obj = new ilObjDataCollection($this->objId, false);
311  }
312  }
313 
314 
318  public function getRecords() {
319  $this->loadRecords();
320 
321  return $this->records;
322  }
323 
324 
336  public function getRecordsByFilter(array $filter = array()) {
337  $this->loadRecords();
338  // Only pass records trough filter if there is filtering required #performance-improvements
339  if (! count($filter)) {
340  return $this->records;
341  }
342  $filtered = array();
343  foreach ($this->getRecords() as $record) {
344  if ($record->passThroughFilter($filter)) {
345  $filtered[] = $record;
346  }
347  }
348 
349  return $filtered;
350  }
351 
352 
353  /*
354  * loadRecords
355  */
356  private function loadRecords() {
357  if ($this->records == NULL) {
358  global $ilDB;
359 
360  $records = array();
361  $query = "SELECT id FROM il_dcl_record WHERE table_id = " . $ilDB->quote($this->id, "integer");
362  $set = $ilDB->query($query);
363 
364  while ($rec = $ilDB->fetchAssoc($set)) {
365  $records[$rec['id']] = ilDataCollectionCache::getRecordCache($rec['id']);
366  }
367 
368  $this->records = $records;
369  }
370  }
371 
372  //TODO: replace this method with DataCollection->getTables()
379  public function getAll($a_id) {
380  global $ilDB;
381 
382  //build query
383  $query = "SELECT *
384  FROM il_dcl_table
385  WHERE obj_id = " . $ilDB->quote($a_id, "integer");
386  $set = $ilDB->query($query);
387 
388  $all = array();
389  while ($rec = $ilDB->fetchAssoc($set)) {
390  $all[$rec['id']] = $rec;
391  }
392 
393  return $all;
394  }
395 
396 
397  /*
398  * deleteField
399  */
400  public function deleteField($field_id) {
401  $field = ilDataCollectionCache::getFieldCache($field_id);
402  $records = $this->getRecords();
403 
404  foreach ($records as $record) {
405  $record->deleteField($field_id);
406  }
407 
408  $field->doDelete();
409  }
410 
411 
412  /*
413  * getField
414  */
415  public function getField($field_id) {
416  $fields = $this->getFields();
417  $field = NULL;
418  foreach ($fields as $field_1) {
419  if ($field_1->getId() == $field_id) {
420  $field = $field_1;
421  }
422  }
423 
424  return $field;
425  }
426 
427 
428  /*
429  * getFieldIds
430  */
431  public function getFieldIds() {
432  return array_keys($this->getFields());
433  }
434 
435 
436  /*
437  * loadFields
438  */
439  private function loadFields() {
440  if ($this->fields == NULL) {
441  global $ilDB;
442 
443  $query = "SELECT DISTINCT field.* FROM il_dcl_field AS field
444  INNER JOIN il_dcl_view AS view ON view.table_id = field.table_id
445  INNER JOIN il_dcl_viewdefinition AS def ON def.view_id = view.id
446  WHERE field.table_id =" . $ilDB->quote($this->getId(), "integer") . "
447  ORDER BY def.field_order DESC";
448  $fields = array();
449  $set = $ilDB->query($query);
450 
451  while ($rec = $ilDB->fetchAssoc($set)) {
452  $field = ilDataCollectionCache::buildFieldFromRecord($rec);
453  $fields[$field->getId()] = $field;
454  }
455  $this->sortByOrder($fields);
456  $this->fields = $fields;
457  }
458  }
459 
460 
466  public function getNewOrder() {
467  $fields = $this->getFields();
468  $place = 0;
469  foreach ($fields as $field) {
470  if ($field->isVisible()) {
471  $place = $field->getOrder() + 1;
472  }
473  }
474 
475  return $place;
476  }
477 
478 
484  public function getFields() {
485  $this->loadFields();
486  if ($this->stdFields == NULL) {
487  $this->stdFields = ilDataCollectionStandardField::_getStandardFields($this->id);
488  }
489  $fields = array_merge($this->fields, $this->stdFields);
490  $this->sortByOrder($fields);
491 
492  return $fields;
493  }
494 
495 
501  public function getStandardFields() {
502  if ($this->stdFields == NULL) {
503  $this->stdFields = ilDataCollectionStandardField::_getStandardFields($this->id);
504  }
505 
506  return $this->stdFields;
507  }
508 
509 
515  public function getRecordFields() {
516  $this->loadFields();
517 
518  return $this->fields;
519  }
520 
521 
527  public function getVisibleFields() {
528  $fields = $this->getFields();
529 
530  $visibleFields = array();
531 
532  foreach ($fields as $field) {
533  if ($field->isVisible()) {
534  $visibleFields[] = $field;
535  }
536  }
537 
538  return $visibleFields;
539  }
540 
541 
542  /*
543  * getEditableFields
544  */
545  public function getEditableFields() {
546  $fields = $this->getRecordFields();
547  $editableFields = array();
548 
549  foreach ($fields as $field) {
550  if (! $field->getLocked()) {
551  $editableFields[] = $field;
552  }
553  }
554 
555  return $editableFields;
556  }
557 
558 
565  public function getFilterableFields() {
566  $fields = $this->getFields();
567  $filterableFields = array();
568 
569  foreach ($fields as $field) {
570  if ($field->isFilterable()) {
571  $filterableFields[] = $field;
572  }
573  }
574 
575  return $filterableFields;
576  }
577 
578 
584  public function getExportableFields() {
585  $fields = $this->getFields();
586  $exportableFields = array();
587  foreach ($fields as $field) {
588  if ($field->getExportable()) {
589  $exportableFields[] = $field;
590  }
591  }
592 
593  return $exportableFields;
594  }
595 
596 
597  /*
598  * hasPermissionToFields
599  */
600  public function hasPermissionToFields($ref_id) {
601  return ilObjDataCollection::_hasWriteAccess($ref_id);
602  }
603 
604 
605  /*
606  * hasPermissionToAddTable
607  */
608  public function hasPermissionToAddTable($ref_id) {
609  return ilObjDataCollection::_hasWriteAccess($ref_id);
610  }
611 
612 
613  public function hasPermissionToAddRecord($ref) {
614  return ($this->getAddPerm() && ilObjDataCollection::_hasReadAccess($ref) && $this->checkLimit())
615  || ilObjDataCollection::_hasWriteAccess($ref);
616  }
617 
618 
625  public function hasPermissionToEditRecord($ref, $record) {
626  return ($this->getEditPerm() && ilObjDataCollection::_hasReadAccess($ref) && $this->checkEditByOwner($record) && $this->checkLimit())
627  || ilObjDataCollection::_hasWriteAccess($ref);
628  }
629 
630 
637  public function hasPermissionToDeleteRecord($ref, $record) {
638  return ($this->getDeletePerm() && ilObjDataCollection::_hasReadAccess($ref) && $this->checkEditByOwner($record) && $this->checkLimit())
639  || ilObjDataCollection::_hasWriteAccess($ref);
640  }
641 
642 
643  /*
644  * checkEditByOwner
645  */
646  private function checkEditByOwner($record) {
647  global $ilUser;
648 
649  if ($this->getEditByOwner() && $ilUser->getId() != $record->getOwner()) {
650  return false;
651  }
652 
653  return true;
654  }
655 
656 
657  /*
658  * checkLimit
659  */
660  private function checkLimit() {
661  if ($this->getLimited()) {
662  $now = new ilDateTime(date("Y-m-d H:i:s"), IL_CAL_DATE);
663  $from = new ilDateTime($this->getLimitStart(), IL_CAL_DATE);
664  $to = new ilDateTime($this->getLimitEnd(), IL_CAL_DATE);
665  return ($from <= $now && $now <= $to);
666  }
667 
668  return true;
669  }
670 
671 
672  /*
673  * updateFields
674  */
675  public function updateFields() {
676  foreach ($this->getFields() as $field) {
677  $field->doUpdate();
678  }
679  }
680 
681 
687  public function sortFields(&$fields) {
688  $this->sortByOrder($fields);
689 
690  //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.
691  $named = array();
692  foreach ($fields as $field) {
693  $named[$field->getId()] = $field;
694  }
695 
696  $fields = $named;
697  }
698 
699 
704  private function sortByOrder(&$array) {
705  usort($array, array( $this, "compareOrder" ));
706  }
707 
708 
713  public function buildOrderFields() {
714  $fields = $this->getFields();
715 
716  $this->sortByOrder($fields);
717 
718  $count = 10;
719  $offset = 10;
720 
721  foreach ($fields as $field) {
722  if (! is_null($field->getOrder())) {
723  $field->setOrder($count);
724  $count = $count + $offset;
725  $field->doUpdate();
726  }
727  }
728  }
729 
730 
736  public function getFieldByTitle($name) {
737  $return = NULL;
738  foreach ($this->getFields() as $field) {
739  if ($field->getTitle() == $name) {
740  $return = $field;
741  break;
742  }
743  }
744 
745  return $return;
746  }
747 
748 
752  public function setAddPerm($add_perm) {
753  $this->add_perm = $add_perm;
754  }
755 
756 
760  public function getAddPerm() {
761  return $this->add_perm;
762  }
763 
764 
768  public function setDeletePerm($delete_perm) {
769  $this->delete_perm = $delete_perm;
770  }
771 
772 
776  public function getDeletePerm() {
777  return $this->delete_perm;
778  }
779 
780 
784  public function setEditByOwner($edit_by_owner) {
785  $this->edit_by_owner = $edit_by_owner;
786  }
787 
788 
792  public function getEditByOwner() {
793  return $this->edit_by_owner;
794  }
795 
796 
800  public function setEditPerm($edit_perm) {
801  $this->edit_perm = $edit_perm;
802  }
803 
804 
808  public function getEditPerm() {
809  return $this->edit_perm;
810  }
811 
812 
816  public function setLimited($limited) {
817  $this->limited = $limited;
818  }
819 
820 
824  public function getLimited() {
825  return $this->limited;
826  }
827 
828 
832  public function setLimitEnd($limit_end) {
833  $this->limit_end = $limit_end;
834  }
835 
836 
840  public function getLimitEnd() {
841  return $this->limit_end;
842  }
843 
844 
848  public function setLimitStart($limit_start) {
849  $this->limit_start = $limit_start;
850  }
851 
852 
856  public function getLimitStart() {
857  return $this->limit_start;
858  }
859 
860 
864  public function setIsVisible($is_visible) {
865  $this->is_visible = $is_visible;
866  }
867 
868 
872  public function getIsVisible() {
873  return $this->is_visible;
874  }
875 
876 
880  public function setDescription($description) {
881  $this->description = $description;
882  }
883 
884 
888  public function getDescription() {
889  return $this->description;
890  }
891 
892 
898  public function hasCustomFields() {
899  $this->loadFields();
900 
901  return (count($this->fields) > 0) ? true : false;
902  }
903 
904 
905  function compareOrder($a, $b) {
906  if (is_null($a->getOrder() == NULL) && is_null($b->getOrder() == NULL)) {
907  return 0;
908  }
909  if (is_null($a->getOrder())) {
910  return 1;
911  }
912  if (is_null($b->getOrder())) {
913  return - 1;
914  }
915 
916  return $a->getOrder() < $b->getOrder() ? - 1 : 1;
917  }
918 
919 
920  /*
921  * cloneStructure
922  */
923  public function cloneStructure($original_id) {
924  $original = ilDataCollectionCache::getTableCache($original_id);
925  $this->setEditByOwner($original->getEditByOwner());
926  $this->setAddPerm($original->getAddPerm());
927  $this->setEditPerm($original->getEditPerm());
928  $this->setDeletePerm($original->getDeletePerm());
929  $this->setLimited($original->getLimited());
930  $this->setLimitStart($original->getLimitStart());
931  $this->setLimitEnd($original->getLimitEnd());
932  $this->setTitle($original->getTitle());
933  $this->doCreate();
934 
935  //clone fields.
936  foreach ($original->getRecordFields() as $field) {
937  $new_field = new ilDataCollectionField();
938  $new_field->setTableId($this->getId());
939  $new_field->cloneStructure($field->getId());
940  }
941 
942  if ($old_view_id = ilDataCollectionRecordViewViewdefinition::getIdByTableId($original_id)) {
943  $old_view = new ilDataCollectionRecordViewViewdefinition($old_view_id);
944  $old_view->setTableId($original_id);
945  $viewdef = new ilDataCollectionRecordViewViewdefinition();
946  $viewdef->setTableId($this->id);
947  $viewdef->setXMLContent($old_view->getXMLContent(false));
948  $viewdef->create();
949  }
950  }
951 
952 
958  public function _hasRecords() {
959  return (count($this->getRecords()) > 0) ? true : false;
960  }
961 
962 
966  public function addField($field) {
967  $this->fields[$field->getId()] = $field;
968  }
969 
970 
976  public static function _tableExists($table_id) {
977  global $ilDB;
978  $query = "SELECT * FROM il_dcl_table WHERE id = " . $table_id;
979  $result = $ilDB->query($query);
980 
981  return $result->numRows() != 0;
982  }
983 
984 
991  public static function _getTableIdByTitle($title, $obj_id) {
992  global $ilDB;
993  $result = $ilDB->query('SELECT id FROM il_dcl_table WHERE title = ' . $ilDB->quote($title, 'text') . ' AND obj_id = '
994  . $ilDB->quote($obj_id, 'integer'));
995  $id = 0;
996  while ($rec = $ilDB->fetchAssoc($result)) {
997  $id = $rec['id'];
998  }
999 
1000  return $id;
1001  }
1002 
1003 
1004  public function buildTableAsArray() {
1005  global $ilDB;
1006  $fields = $this->getVisibleFields();
1007  $table = array();
1008  $query = " SELECT stloc.value AS val rec_field AS FROM il_dcl_stloc1_value stloc
1009  INNER JOIN il_dcl_record_field rec_field ON rec_field.field_id = 2
1010  WHERE stloc.record_field_id = rec_field.id";
1011  }
1012 
1013 
1017  public function setExportEnabled($export_enabled) {
1018  $this->export_enabled = $export_enabled;
1019  }
1020 
1021 
1025  public function getExportEnabled() {
1026  return $this->export_enabled;
1027  }
1028 
1029 
1038  public static function _hasFieldByTitle($title, $obj_id) {
1039  global $ilDB;
1040  $result = $ilDB->query('SELECT * FROM il_dcl_field WHERE table_id = ' . $ilDB->quote($obj_id, 'integer') . ' AND title = '
1041  . $ilDB->quote($title, 'text'));
1042 
1043  return ($ilDB->numRows($result)) ? true : false;
1044  }
1045 
1046 
1058  public function getPartialRecords($sort, $direction, $limit, $offset, array $filter = array()) {
1059  global $ilDB;
1060  $sortField = ($sort) ? $sortField = $this->getFieldByTitle($sort) : $sortField = $this->getField('id');
1061 
1062  $direction = strtolower($direction);
1063  $direction = (in_array($direction, array( 'desc', 'asc' ))) ? $direction : 'asc';
1064 
1065  // Sorting by a status from an ILIAS Ref field. This column is added dynamically to the table, there is no field model
1066  $sortByStatus = false;
1067  if (substr($sort, 0, 8) == '_status_') {
1068  $sortByStatus = true;
1069  $sortField = $this->getFieldByTitle(substr($sort, 8));
1070  }
1071 
1072  if (is_null($sortField)) {
1073  $sortField = $this->getField('id');
1074  }
1075 
1076  $id = $sortField->getId();
1077  $stl = $sortField->getStorageLocation();
1078  $selectStr = '';
1079  $joinStr = '';
1080  $where_additions = '';
1081  $hasNref = false;
1082 
1083  if ($sortField->isStandardField()) {
1084  if ($id == 'owner' || $id == 'last_edit_by') {
1085  $joinStr .= "LEFT JOIN usr_data AS sort_usr_data_{$id} ON (sort_usr_data_{$id}.usr_id = record.{$id})";
1086  $selectStr .= " sort_usr_data_{$id}.login AS field_{$id},";
1087  } else {
1088  $selectStr .= " record.{$id} AS field_{$id},";
1089  }
1090  } else {
1091  switch ($sortField->getDatatypeId()) {
1092  case ilDataCollectionDatatype::INPUTFORMAT_RATING:
1093  $rating_joined = true;
1094  // FSX Bugfix 0015735: The average is multiplied with 10000 and added to the amount of votes
1095  $joinStr .= "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";
1096  $selectStr .= " average.rating AS field_{$id},";
1097  break;
1098  case ilDataCollectionDatatype::INPUTFORMAT_ILIAS_REF:
1099  $joinStr .=
1100  "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 = "
1101  . $ilDB->quote($sortField->getId(), 'integer') . ") ";
1102  $joinStr .= "LEFT JOIN il_dcl_stloc{$stl}_value AS sort_stloc_{$id} ON (sort_stloc_{$id}.record_field_id = sort_record_field_{$id}.id) ";
1103  $joinStr .= "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)";
1104  $joinStr .= "LEFT JOIN object_data AS sort_object_data_{$id} ON (sort_object_data_{$id}.obj_id = sort_object_reference_{$id}.obj_id)";
1105  if ($sortByStatus) {
1106  global $ilUser;
1107  $joinStr .= "LEFT JOIN ut_lp_marks AS ut ON (ut.obj_id = sort_object_data_{$id}.obj_id AND ut.usr_id = "
1108  . $ilDB->quote($ilUser->getId(), 'integer') . ") ";
1109  }
1110  $selectStr .= (! $sortByStatus) ? " sort_object_data_{$id}.title AS field_{$id}," : " ut.status AS field_{$id}";
1111  break;
1112  case ilDataCollectionDatatype::INPUTFORMAT_FILE:
1113  case ilDataCollectionDatatype::INPUTFORMAT_MOB:
1114  $joinStr .=
1115  "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 = "
1116  . $ilDB->quote($sortField->getId(), 'integer') . ") ";
1117  $joinStr .= "LEFT JOIN il_dcl_stloc{$stl}_value AS sort_stloc_{$id} ON (sort_stloc_{$id}.record_field_id = sort_record_field_{$id}.id) ";
1118  $joinStr .= "LEFT JOIN object_data AS sort_object_data_{$id} ON (sort_object_data_{$id}.obj_id = sort_stloc_{$id}.value) ";
1119  $selectStr .= " sort_object_data_{$id}.title AS field_{$id},";
1120  break;
1121  case ilDataCollectionDatatype::INPUTFORMAT_REFERENCE:
1122  $prop = $sortField->getPropertyvalues();
1123  $refField = ilDataCollectionCache::getFieldCache($sortField->getFieldRef());
1124  $nRef = $prop[ilDataCollectionField::PROPERTYID_N_REFERENCE];
1125  if ($nRef) {
1126  $hasNref = true;
1127  }
1128  $selectStr .= ($nRef) ? " GROUP_CONCAT(stloc_{$id}_joined.value) AS field_{$id}" : "stloc_{$id}_joined.value AS field_{$id},";
1129  $joinStr .=
1130  "LEFT JOIN il_dcl_record_field AS record_field_{$id} ON (record_field_{$id}.record_id = record.id AND record_field_{$id}.field_id = "
1131  . $ilDB->quote($sortField->getId(), 'integer') . ") ";
1132  $joinStr .= "LEFT JOIN il_dcl_stloc{$stl}_value AS stloc_{$id} ON (stloc_{$id}.record_field_id = record_field_{$id}.id) ";
1133  $joinStr .=
1134  "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 = "
1135  . $ilDB->quote($refField->getId(), 'integer') . ") ";
1136  $joinStr .= "LEFT JOIN il_dcl_stloc{$refField->getStorageLocation()}_value AS stloc_{$id}_joined ON (stloc_{$id}_joined.record_field_id = record_field_{$id}_joined.id) ";
1137  break;
1138  case ilDataCollectionDatatype::INPUTFORMAT_DATETIME:
1139  case ilDataCollectionDatatype::INPUTFORMAT_TEXT:
1140  case ilDataCollectionDatatype::INPUTFORMAT_BOOLEAN:
1141  case ilDataCollectionDatatype::INPUTFORMAT_NUMBER:
1142  $selectStr .= " sort_stloc_{$id}.value AS field_{$id},";
1143  $joinStr .=
1144  "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 = "
1145  . $ilDB->quote($sortField->getId(), 'integer') . ") ";
1146  $joinStr .= "LEFT JOIN il_dcl_stloc{$stl}_value AS sort_stloc_{$id} ON (sort_stloc_{$id}.record_field_id = sort_record_field_{$id}.id) ";
1147  break;
1148  }
1149  }
1150 
1151  if (count($filter)) {
1152  foreach ($filter as $key => $filter_value) {
1153  $filter_field_id = substr($key, 7);
1154  $filterField = $this->getField($filter_field_id);
1155  switch ($filterField->getDatatypeId()) {
1156  case ilDataCollectionDatatype::INPUTFORMAT_RATING:
1157  if(!$rating_joined) {
1158  $joinStr .= "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";
1159  }
1160  // FSX Bugfix 0015735: The average is multiplied with 10000 and added to the amount of votes
1161  $where_additions .= " AND average.rating >= " . $ilDB->quote($filter_value * 10000, 'integer');
1162 
1163  break;
1164  case ilDataCollectionDatatype::INPUTFORMAT_ILIAS_REF:
1165  $joinStr .=
1166  "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 = "
1167  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1168  $joinStr .= "INNER JOIN il_dcl_stloc{$filterField->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id) ";
1169  $joinStr .= "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 ) ";
1170  $joinStr .=
1171  "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 "
1172  . $ilDB->quote("%$filter_value%", 'text') . ") ";
1173  break;
1174  case ilDataCollectionDatatype::INPUTFORMAT_MOB:
1175  case ilDataCollectionDatatype::INPUTFORMAT_FILE:
1176  $joinStr .=
1177  "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 = "
1178  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1179  $joinStr .= "INNER JOIN il_dcl_stloc{$filterField->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id) ";
1180  $joinStr .=
1181  "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 "
1182  . $ilDB->quote("%$filter_value%", 'text') . ") ";
1183  break;
1184  case ilDataCollectionDatatype::INPUTFORMAT_DATETIME:
1185  $dateFrom = (isset($filter_value['from']) && is_object($filter_value['from'])) ? $filter_value['from'] : NULL;
1186  $dateTo = (isset($filter_value['to']) && is_object($filter_value['to'])) ? $filter_value['to'] : NULL;
1187  if ($filterField->isStandardField()) {
1188  if ($dateFrom) {
1189  $where_additions .= " AND (record.{$filter_field_id} >= " . $ilDB->quote($dateFrom, 'date') . ")";
1190  }
1191  if ($dateTo) {
1192  $where_additions .= " AND (record.{$filter_field_id} <= " . $ilDB->quote($dateTo, 'date') . ")";
1193  }
1194  } else {
1195  $joinStr .=
1196  "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 = "
1197  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1198  $joinStr .= "INNER JOIN il_dcl_stloc{$filterField->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id ";
1199  if ($dateFrom) {
1200  $joinStr .= "AND filter_stloc_{$filter_field_id}.value >= " . $ilDB->quote($dateFrom, 'date') . " ";
1201  }
1202  if ($dateTo) {
1203  $joinStr .= "AND filter_stloc_{$filter_field_id}.value <= " . $ilDB->quote($dateTo, 'date') . " ";
1204  }
1205  $joinStr .= ") ";
1206  }
1207  break;
1208  case ilDataCollectionDatatype::INPUTFORMAT_NUMBER:
1209  $from = (isset($filter_value['from'])) ? (int)$filter_value['from'] : NULL;
1210  $to = (isset($filter_value['to'])) ? (int)$filter_value['to'] : NULL;
1211  if ($filterField->isStandardField()) {
1212  if (! is_null($from)) {
1213  $where_additions .= " AND record.{$filter_field_id} >= " . $ilDB->quote($from, 'integer');
1214  }
1215  if (! is_null($to)) {
1216  $where_additions .= " AND record.{$filter_field_id} <= " . $ilDB->quote($to, 'integer');
1217  }
1218  } else {
1219  $joinStr .=
1220  "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 = "
1221  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1222  $joinStr .= "INNER JOIN il_dcl_stloc{$filterField->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id";
1223  if (! is_null($from)) {
1224  $joinStr .= " AND filter_stloc_{$filter_field_id}.value >= " . $ilDB->quote($from, 'integer');
1225  }
1226  if (! is_null($to)) {
1227  $joinStr .= " AND filter_stloc_{$filter_field_id}.value <= " . $ilDB->quote($to, 'integer');
1228  }
1229  $joinStr .= ") ";
1230  }
1231  break;
1232  case ilDataCollectionDatatype::INPUTFORMAT_BOOLEAN:
1233  if ($filter_value == "checked") {
1234  $joinStr .=
1235  "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 = "
1236  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1237  $joinStr .= "INNER JOIN il_dcl_stloc{$filterField->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id";
1238  $joinStr .= " AND filter_stloc_{$filter_field_id}.value = " . $ilDB->quote(1, 'integer');
1239  } else {
1240  $joinStr .=
1241  "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 = "
1242  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1243  $joinStr .= "LEFT JOIN il_dcl_stloc{$filterField->getStorageLocation()}_value AS filter_stloc_{$filter_field_id} ON (filter_stloc_{$filter_field_id}.record_field_id = filter_record_field_{$filter_field_id}.id";
1244  $where_additions .= " AND (filter_stloc_{$filter_field_id}.value <> " . $ilDB->quote(1, 'integer')
1245  . " OR filter_stloc_{$filter_field_id}.value is NULL)";
1246  }
1247  $joinStr .= " ) ";
1248  break;
1249  case ilDataCollectionDatatype::INPUTFORMAT_TEXT:
1250  if ($filterField->isStandardField()) {
1251  $joinStr .=
1252  "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 "
1253  . $ilDB->quote("%$filter_value%", 'text') . ") ";
1254  } else {
1255  $joinStr .=
1256  " 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 = "
1257  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1258  $joinStr .=
1259  " INNER JOIN il_dcl_stloc{$filterField->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 "
1260  . $ilDB->quote("%$filter_value%", 'text') . ") ";
1261  }
1262  break;
1263  case ilDataCollectionDatatype::INPUTFORMAT_REFERENCE:
1264  $joinStr .=
1265  " 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 = "
1266  . $ilDB->quote($filter_field_id, 'integer') . ") ";
1267  $prop = $filterField->getPropertyvalues();
1268  $nRef = $prop[ilDataCollectionField::PROPERTYID_N_REFERENCE];
1269  if ($nRef) {
1270  $joinStr .=
1271  " INNER JOIN il_dcl_stloc{$filterField->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 "
1272  . $ilDB->quote("%$filter_value%", 'text') . ") ";
1273  } else {
1274  $joinStr .=
1275  " INNER JOIN il_dcl_stloc{$filterField->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 = "
1276  . $ilDB->quote($filter_value, 'integer') . ") ";
1277  }
1278  break;
1279  }
1280  }
1281  }
1282 
1283  // Build the query string
1284  $sql = "SELECT DISTINCT record.id, ";
1285  $sql .= rtrim($selectStr, ',') . " FROM il_dcl_record AS record ";
1286  $sql .= $joinStr;
1287  $sql .= " WHERE record.table_id = " . $ilDB->quote($this->getId(), 'integer') . $where_additions;
1288  if ($hasNref) {
1289  $sql .= " GROUP BY record.id";
1290  }
1291  $sql .= " ORDER BY field_{$id} {$direction}";
1292  $set = $ilDB->query($sql);
1293  $totalRecordIds = array();
1294  while ($rec = $ilDB->fetchAssoc($set)) {
1295  $totalRecordIds[] = $rec['id'];
1296  }
1297  // Now slice the array to load only the needed records in memory
1298  $recordIds = array_slice($totalRecordIds, $offset, $limit);
1299  $records = array();
1300  foreach ($recordIds as $id) {
1301  $records[] = ilDataCollectionCache::getRecordCache($id);
1302  }
1303 
1304  return array( 'records' => $records, 'total' => count($totalRecordIds) );
1305  }
1306 }
1307 
1308 ?>