/** * @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)); }