/**
  * @description This adds the collumn for status.
  *
  * @param ilDataCollectionRecord $record
  * @param ilDataCollectionField  $field
  *
  * @return string
  */
 protected function getStatus(ilDataCollectionRecord $record, ilDataCollectionField $field)
 {
     $record_field = ilDataCollectionCache::getRecordFieldCache($record, $field);
     $return = "";
     if ($status = $record_field->getStatus()) {
         $return = "<img src='" . ilLearningProgressBaseGUI::_getImagePathForStatus($status->status) . "'>";
     }
     return $return;
 }
 /**
  * get Values
  * 
  */
 public function getValues()
 {
     //Get Record-Values
     $record_obj = ilDataCollectionCache::getRecordCache($this->record_id);
     //Get Table Field Definitions
     $allFields = $this->table->getFields();
     $values = array();
     foreach ($allFields as $field) {
         $value = $record_obj->getRecordFieldFormInput($field->getId(), ilDataCollectionCache::getRecordFieldCache($record_obj, $field));
         $values['field_' . $field->getId()] = $value;
         if ($field->getDatatypeId() == ilDataCollectionDatatype::INPUTFORMAT_MOB) {
             $img = ilObjMediaObject::_lookupItemPath($value);
             if ($value) {
                 $this->form->getItemByPostVar('field_' . $field->getId())->setImage($img);
             }
         }
     }
     $this->form->setValuesByArray($values);
     return true;
 }
 private function loadRecordFields()
 {
     if ($this->recordfields == NULL) {
         $this->loadTable();
         $recordfields = array();
         foreach ($this->table->getRecordFields() as $field) {
             if ($recordfields[$field->getId()] == NULL) {
                 $recordfields[$field->getId()] = ilDataCollectionCache::getRecordFieldCache($this, $field);
             }
         }
         $this->recordfields = $recordfields;
     }
 }
 /**
  * Return only the needed subset of record objects for the table, according to sorting, paging and filters
  *
  * @param string $sort      Title of a field where the ilTable2GUI is sorted
  * @param string $direction 'desc' or 'asc'
  * @param int    $limit     Limit of records
  * @param int    $offset    Offset from records
  * @param array  $filter    Containing the filter values
  *
  * @return array Array with two keys: 'record' => Contains the record objects, 'total' => Number of total records (without slicing)
  */
 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()) {
         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},";
         } elseif ($id != 'comments') {
             $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";
     }
     if ($id != 'comments' && $sort_field->getDatatypeId() != ilDataCollectionDatatype::INPUTFORMAT_FORMULA) {
         $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();
     $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);
     }
     // 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));
 }