/**
* @param EntityManager $em
*
* @throws \Doctrine\DBAL\DBALException
*/
function fixIds(EntityManager $em)
{
$connection = $em->getConnection();
$sql = "SELECT * FROM c_lp_item";
$result = $connection->fetchAll($sql);
foreach ($result as $item) {
$courseId = $item['c_id'];
$iid = isset($item['iid']) ? $item['iid'] : 0;
$ref = isset($item['ref']) ? $item['ref'] : 0;
$sql = null;
$newId = '';
switch ($item['item_type']) {
case TOOL_LINK:
$sql = "SELECT * FROM c_link WHERE c_id = {$courseId} AND id = {$ref}";
$data = $connection->fetchAssoc($sql);
if ($data) {
$newId = $data['iid'];
}
break;
case TOOL_STUDENTPUBLICATION:
$sql = "SELECT * FROM c_student_publication WHERE c_id = {$courseId} AND id = {$ref}";
$data = $connection->fetchAssoc($sql);
if ($data) {
$newId = $data['iid'];
}
break;
case TOOL_QUIZ:
$sql = "SELECT * FROM c_quiz WHERE c_id = {$courseId} AND id = {$ref}";
$data = $connection->fetchAssoc($sql);
if ($data) {
$newId = $data['iid'];
}
break;
case TOOL_DOCUMENT:
$sql = "SELECT * FROM c_document WHERE c_id = {$courseId} AND id = {$ref}";
$data = $connection->fetchAssoc($sql);
if ($data) {
$newId = $data['iid'];
}
break;
case TOOL_FORUM:
$sql = "SELECT * FROM c_forum_forum WHERE c_id = {$courseId} AND id = {$ref}";
$data = $connection->fetchAssoc($sql);
if ($data) {
$newId = $data['iid'];
}
break;
case 'thread':
$sql = "SELECT * FROM c_forum_thread WHERE c_id = {$courseId} AND id = {$ref}";
$data = $connection->fetchAssoc($sql);
if ($data) {
$newId = $data['iid'];
}
break;
}
if (!empty($sql) && !empty($newId) && !empty($iid)) {
$sql = "UPDATE c_lp_item SET ref = {$newId} WHERE iid = {$iid}";
$connection->executeQuery($sql);
}
}
// Set NULL if session = 0
$sql = "UPDATE c_item_property SET session_id = NULL WHERE session_id = 0";
$connection->executeQuery($sql);
// Set NULL if group = 0
$sql = "UPDATE c_item_property SET to_group_id = NULL WHERE to_group_id = 0";
$connection->executeQuery($sql);
// Set NULL if insert_user_id = 0
$sql = "UPDATE c_item_property SET insert_user_id = NULL WHERE insert_user_id = 0";
$connection->executeQuery($sql);
// Delete session data of sessions that don't exist.
$sql = "DELETE FROM c_item_property\n WHERE session_id IS NOT NULL AND session_id NOT IN (SELECT id FROM session)";
$connection->executeQuery($sql);
// Delete group data of groups that don't exist.
$sql = "DELETE FROM c_item_property\n WHERE to_group_id IS NOT NULL AND to_group_id NOT IN (SELECT DISTINCT id FROM c_group_info)";
$connection->executeQuery($sql);
// This updates the group_id with c_group_info.iid instead of c_group_info.id
$groupTableToFix = ['c_group_rel_user', 'c_group_rel_tutor', 'c_permission_group', 'c_role_group', 'c_survey_invitation', 'c_attendance_calendar_rel_group'];
foreach ($groupTableToFix as $table) {
$sql = "SELECT * FROM {$table}";
$result = $connection->fetchAll($sql);
foreach ($result as $item) {
$iid = $item['iid'];
$courseId = $item['c_id'];
$groupId = intval($item['group_id']);
// Fix group id
if (!empty($groupId)) {
$sql = "SELECT * FROM c_group_info\n WHERE c_id = {$courseId} AND id = {$groupId}\n LIMIT 1";
$data = $connection->fetchAssoc($sql);
if (!empty($data)) {
$newGroupId = $data['iid'];
$sql = "UPDATE {$table} SET group_id = {$newGroupId}\n WHERE iid = {$iid}";
$connection->executeQuery($sql);
} else {
// The group does not exists clean this record
$sql = "DELETE FROM {$table} WHERE iid = {$iid}";
$connection->executeQuery($sql);
}
}
}
}
// Fix c_item_property
$sql = "SELECT * FROM c_item_property";
$result = $connection->fetchAll($sql);
foreach ($result as $item) {
$courseId = $item['c_id'];
$sessionId = intval($item['session_id']);
$groupId = intval($item['to_group_id']);
$iid = $item['iid'];
$ref = $item['ref'];
// Fix group id
if (!empty($groupId)) {
$sql = "SELECT * FROM c_group_info\n WHERE c_id = {$courseId} AND id = {$groupId}";
$data = $connection->fetchAssoc($sql);
if (!empty($data)) {
$newGroupId = $data['iid'];
$sql = "UPDATE c_item_property SET to_group_id = {$newGroupId}\n WHERE iid = {$iid}";
$connection->executeQuery($sql);
} else {
// The group does not exists clean this record
$sql = "DELETE FROM c_item_property WHERE iid = {$iid}";
$connection->executeQuery($sql);
}
}
$sql = '';
$newId = '';
switch ($item['tool']) {
case TOOL_LINK:
$sql = "SELECT * FROM c_link WHERE c_id = {$courseId} AND id = {$ref} ";
break;
case TOOL_STUDENTPUBLICATION:
$sql = "SELECT * FROM c_student_publication WHERE c_id = {$courseId} AND id = {$ref}";
break;
case TOOL_QUIZ:
$sql = "SELECT * FROM c_quiz WHERE c_id = {$courseId} AND id = {$ref}";
break;
case TOOL_DOCUMENT:
$sql = "SELECT * FROM c_document WHERE c_id = {$courseId} AND id = {$ref}";
break;
case TOOL_FORUM:
$sql = "SELECT * FROM c_forum_forum WHERE c_id = {$courseId} AND id = {$ref}";
break;
case 'thread':
$sql = "SELECT * FROM c_forum_thread WHERE c_id = {$courseId} AND id = {$ref}";
break;
}
if (!empty($sql) && !empty($newId)) {
$data = $connection->fetchAssoc($sql);
if (isset($data['iid'])) {
$newId = $data['iid'];
}
$sql = "UPDATE c_item_property SET ref = {$newId} WHERE iid = {$iid}";
$connection->executeQuery($sql);
}
}
// Fix gradebook_link
$sql = "SELECT * FROM gradebook_link";
$result = $connection->fetchAll($sql);
foreach ($result as $item) {
$courseCode = $item['course_code'];
$courseInfo = api_get_course_info($courseCode);
if (empty($courseInfo)) {
continue;
}
$courseId = $courseInfo['real_id'];
$ref = $item['ref_id'];
$iid = $item['id'];
$sql = '';
switch ($item['type']) {
case LINK_LEARNPATH:
$sql = "SELECT * FROM c_link WHERE c_id = {$courseId} AND id = {$ref} ";
break;
case LINK_STUDENTPUBLICATION:
$sql = "SELECT * FROM c_student_publication WHERE c_id = {$courseId} AND id = {$ref}";
break;
case LINK_EXERCISE:
$sql = "SELECT * FROM c_quiz WHERE c_id = {$courseId} AND id = {$ref}";
break;
case LINK_ATTENDANCE:
//$sql = "SELECT * FROM c_document WHERE c_id = $courseId AND id = $ref";
break;
case LINK_FORUM_THREAD:
$sql = "SELECT * FROM c_forum_thread WHERE c_id = {$courseId} AND id = {$ref}";
break;
}
if (!empty($sql)) {
$data = $connection->fetchAssoc($sql);
if (isset($data) && isset($data['iid'])) {
$newId = $data['iid'];
$sql = "UPDATE gradebook_link SET ref_id = {$newId}\n WHERE id = {$iid}";
$connection->executeQuery($sql);
}
}
}
$sql = "SELECT * FROM groups";
$result = $connection->executeQuery($sql);
$groups = $result->fetchAll();
$oldGroups = array();
if (!empty($groups)) {
foreach ($groups as $group) {
$sql = "INSERT INTO usergroup (name, group_type, description, picture, url, visibility, updated_at, created_at)\n VALUES ('{$group['name']}', '1', '{$group['description']}', '{$group['picture_uri']}', '{$group['url']}', '{$group['visibility']}', '{$group['updated_on']}', '{$group['created_on']}')";
$connection->executeQuery($sql);
$id = $connection->lastInsertId('id');
$oldGroups[$group['id']] = $id;
}
}
if (!empty($oldGroups)) {
foreach ($oldGroups as $oldId => $newId) {
$path = \GroupPortalManager::get_group_picture_path_by_id($oldId, 'system');
if (!empty($path)) {
$newPath = str_replace("groups/{$oldId}/", "groups/{$newId}/", $path['dir']);
$command = "mv {$path['dir']} {$newPath} ";
system($command);
}
}
$sql = "SELECT * FROM group_rel_user";
$result = $connection->executeQuery($sql);
$dataList = $result->fetchAll();
if (!empty($dataList)) {
foreach ($dataList as $data) {
if (isset($oldGroups[$data['group_id']])) {
$data['group_id'] = $oldGroups[$data['group_id']];
$sql = "INSERT INTO usergroup_rel_user (usergroup_id, user_id, relation_type)\n VALUES ('{$data['group_id']}', '{$data['user_id']}', '{$data['relation_type']}')";
$connection->executeQuery($sql);
}
}
}
$sql = "SELECT * FROM group_rel_group";
$result = $connection->executeQuery($sql);
$dataList = $result->fetchAll();
if (!empty($dataList)) {
foreach ($dataList as $data) {
if (isset($oldGroups[$data['group_id']]) && isset($oldGroups[$data['subgroup_id']])) {
$data['group_id'] = $oldGroups[$data['group_id']];
$data['subgroup_id'] = $oldGroups[$data['subgroup_id']];
$sql = "INSERT INTO usergroup_rel_usergroup (group_id, subgroup_id, relation_type)\n VALUES ('{$data['group_id']}', '{$data['subgroup_id']}', '{$data['relation_type']}')";
$connection->executeQuery($sql);
}
}
}
$sql = "SELECT * FROM announcement_rel_group";
$result = $connection->executeQuery($sql);
$dataList = $result->fetchAll();
if (!empty($dataList)) {
foreach ($dataList as $data) {
if (isset($oldGroups[$data['group_id']])) {
//Deleting relation
$sql = "DELETE FROM announcement_rel_group WHERE id = {$data['id']}";
$connection->executeQuery($sql);
//Add new relation
$data['group_id'] = $oldGroups[$data['group_id']];
$sql = "INSERT INTO announcement_rel_group(group_id, announcement_id)\n VALUES ('{$data['group_id']}', '{$data['announcement_id']}')";
$connection->executeQuery($sql);
}
}
}
$sql = "SELECT * FROM group_rel_tag";
$result = $connection->executeQuery($sql);
$dataList = $result->fetchAll();
if (!empty($dataList)) {
foreach ($dataList as $data) {
if (isset($oldGroups[$data['group_id']])) {
$data['group_id'] = $oldGroups[$data['group_id']];
$sql = "INSERT INTO usergroup_rel_tag (tag_id, usergroup_id)\n VALUES ('{$data['tag_id']}', '{$data['group_id']}')";
$connection->executeQuery($sql);
}
}
}
}
// Extra fields
$extraFieldTables = [ExtraField::USER_FIELD_TYPE => Database::get_main_table(TABLE_MAIN_USER_FIELD), ExtraField::COURSE_FIELD_TYPE => Database::get_main_table(TABLE_MAIN_COURSE_FIELD), ExtraField::SESSION_FIELD_TYPE => Database::get_main_table(TABLE_MAIN_SESSION_FIELD)];
foreach ($extraFieldTables as $type => $table) {
//continue;
$sql = "SELECT * FROM {$table} ";
$result = $connection->query($sql);
$fields = $result->fetchAll();
foreach ($fields as $field) {
$originalId = $field['id'];
$extraField = new ExtraField();
$extraField->setExtraFieldType($type)->setVariable($field['field_variable'])->setFieldType($field['field_type'])->setDisplayText($field['field_display_text'])->setDefaultValue($field['field_default_value'])->setFieldOrder($field['field_order'])->setVisible($field['field_visible'])->setChangeable($field['field_changeable'])->setFilter($field['field_filter']);
$em->persist($extraField);
$em->flush();
$values = array();
switch ($type) {
case ExtraField::USER_FIELD_TYPE:
$optionTable = Database::get_main_table(TABLE_MAIN_USER_FIELD_OPTIONS);
$valueTable = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
$handlerId = 'user_id';
break;
case ExtraField::COURSE_FIELD_TYPE:
$optionTable = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_OPTIONS);
$valueTable = Database::get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
$handlerId = 'c_id';
break;
case ExtraField::SESSION_FIELD_TYPE:
$optionTable = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_OPTIONS);
$valueTable = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
$handlerId = 'session_id';
break;
}
if (!empty($optionTable)) {
$sql = "SELECT * FROM {$optionTable} WHERE field_id = {$originalId} ";
$result = $connection->query($sql);
$options = $result->fetchAll();
foreach ($options as $option) {
$extraFieldOption = new ExtraFieldOptions();
$extraFieldOption->setDisplayText($option['option_display_text'])->setField($extraField)->setOptionOrder($option['option_order'])->setValue($option['option_value']);
$em->persist($extraFieldOption);
$em->flush();
}
$sql = "SELECT * FROM {$valueTable} WHERE field_id = {$originalId} ";
$result = $connection->query($sql);
$values = $result->fetchAll();
}
if (!empty($values)) {
foreach ($values as $value) {
$extraFieldValue = new ExtraFieldValues();
$extraFieldValue->setValue($value['field_value'])->setField($extraField)->setItemId($value[$handlerId]);
$em->persist($extraFieldValue);
$em->flush();
}
}
}
}
}
/**
* {@inheritdoc}
*/
public function addExtraFields(ExtraFieldValues $extraFieldValue)
{
//if (!$this->hasExtraField($attribute)) {
$extraFieldValue->setUser($this);
$this->extraFields[] = $extraFieldValue;
//}
return $this;
}
