/**
* @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();
}
}
}
}
}
/**
* @param ObjectManager $manager
*/
public function load(ObjectManager $manager)
{
// Saving user fields
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('legal_accept');
$userField->setDisplayText('Legal');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setExtraFieldType(1);
$userField->setFieldType('user');
$userField->setVariable('already_logged_in');
$userField->setDisplayText('Already logged in');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('update_type');
$userField->setDisplayText('Update script type');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(10);
$userField->setVariable('tags');
$userField->setDisplayText('tags');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('rssfeeds');
$userField->setDisplayText('RSS');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('legal_accept');
$userField->setDisplayText('Legal');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('dashboard');
$userField->setDisplayText('Dashboard');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('timezone');
$userField->setDisplayText('Timezone');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$notifyInvitation = new ExtraField();
$notifyInvitation->setFieldType('user');
$notifyInvitation->setExtraFieldType(4);
$notifyInvitation->setVariable('mail_notify_invitation');
$notifyInvitation->setDisplayText('MailNotifyInvitation');
$notifyInvitation->setVisible(1);
$notifyInvitation->setChangeable(1);
$notifyInvitation->setDefaultValue(1);
$manager->persist($notifyInvitation);
$notifyMessage = new ExtraField();
$notifyMessage->setFieldType('user');
$notifyMessage->setExtraFieldType(4);
$notifyMessage->setVariable('mail_notify_message');
$notifyMessage->setDisplayText('MailNotifyMessage');
$notifyMessage->setVisible(1);
$notifyMessage->setChangeable(1);
$notifyMessage->setDefaultValue(1);
$manager->persist($notifyMessage);
$notifyGroup = new ExtraField();
$notifyGroup->setFieldType('user');
$notifyGroup->setExtraFieldType(4);
$notifyGroup->setVariable('mail_notify_group_message');
$notifyGroup->setDisplayText('MailNotifyGroupMessage');
$notifyGroup->setVisible(1);
$notifyGroup->setChangeable(1);
$notifyGroup->setDefaultValue(1);
$manager->persist($notifyGroup);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('user_chat_status');
$userField->setDisplayText('User chat status');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
$userField = new ExtraField();
$userField->setFieldType('user');
$userField->setExtraFieldType(1);
$userField->setVariable('google_calendar_url');
$userField->setDisplayText('Google Calendar URL');
$userField->setVisible(0);
$userField->setChangeable(0);
$manager->persist($userField);
// First
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyInvitation);
$userFieldOption->setValue(1);
$userFieldOption->setDisplayText('AtOnce');
$userFieldOption->setOptionOrder(1);
$manager->persist($userFieldOption);
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyInvitation);
$userFieldOption->setValue(8);
$userFieldOption->setDisplayText('Daily');
$userFieldOption->setOptionOrder(2);
$manager->persist($userFieldOption);
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyInvitation);
$userFieldOption->setValue(0);
$userFieldOption->setDisplayText('No');
$userFieldOption->setOptionOrder(3);
$manager->persist($userFieldOption);
// Second
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyGroup);
$userFieldOption->setValue(1);
$userFieldOption->setDisplayText('AtOnce');
$userFieldOption->setOptionOrder(1);
$manager->persist($userFieldOption);
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyGroup);
$userFieldOption->setValue(8);
$userFieldOption->setDisplayText('Daily');
$userFieldOption->setOptionOrder(2);
$manager->persist($userFieldOption);
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyGroup);
$userFieldOption->setValue(0);
$userFieldOption->setDisplayText('No');
$userFieldOption->setOptionOrder(3);
$manager->persist($userFieldOption);
// Third
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyMessage);
$userFieldOption->setValue(1);
$userFieldOption->setDisplayText('AtOnce');
$userFieldOption->setOptionOrder(1);
$manager->persist($userFieldOption);
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyMessage);
$userFieldOption->setValue(8);
$userFieldOption->setDisplayText('Daily');
$userFieldOption->setOptionOrder(2);
$manager->persist($userFieldOption);
$userFieldOption = new ExtraFieldOptions();
$userFieldOption->setField($notifyMessage);
$userFieldOption->setValue(0);
$userFieldOption->setDisplayText('No');
$userFieldOption->setOptionOrder(3);
$manager->persist($userFieldOption);
$manager->flush();
}
/**
* Proxy method to access the code from real attribute.
*
* @return string
*/
public function getCode()
{
return $this->attribute->getVariable();
}
/**
* Get the tags for a item
* @param ExtraField $extraField The extrafield
* @param int $itemId The item ID
* @return \Doctrine\ORM\QueryBuilder
*/
public function getTags(ExtraField $extraField, $itemId)
{
$queryBuilder = $this->createQueryBuilder('ft');
$queryBuilder->select('t')->innerJoin('ChamiloCoreBundle:Tag', 't', Join::WITH, 'ft.tagId = t.id')->where($queryBuilder->expr()->andX($queryBuilder->expr()->eq('ft.itemId', intval($itemId)), $queryBuilder->expr()->eq('ft.fieldId', $extraField->getId())));
return $queryBuilder->getQuery()->getResult();
}
/**
* @param EntityManager $em
*
* @throws \Doctrine\DBAL\DBALException
*/
function fixIds(EntityManager $em)
{
$debug = true;
$connection = $em->getConnection();
if ($debug) {
error_log('fixIds');
}
// Create temporary indexes to increase speed of the following operations
// Adding and removing indexes will usually take much less time than
// the execution without indexes of the queries in this function, particularly
// for large tables
$sql = "ALTER TABLE c_document ADD INDEX tmpidx_doc(c_id, id)";
$connection->executeQuery($sql);
$sql = "ALTER TABLE c_student_publication ADD INDEX tmpidx_stud (c_id, id)";
$connection->executeQuery($sql);
$sql = "ALTER TABLE c_quiz ADD INDEX tmpidx_quiz (c_id, id)";
$connection->executeQuery($sql);
$sql = "ALTER TABLE c_item_property ADD INDEX tmpidx_ip (to_group_id)";
$connection->executeQuery($sql);
$sql = "SELECT * FROM c_lp_item";
$result = $connection->fetchAll($sql);
foreach ($result as $item) {
$courseId = $item['c_id'];
$iid = isset($item['iid']) ? intval($item['iid']) : 0;
$ref = isset($item['ref']) ? intval($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 forum_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 thread_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
if ($debug) {
error_log('update iids');
}
$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
if ($debug) {
error_log('update c_item_property');
}
$sql = "SELECT * FROM course";
$courseList = $connection->fetchAll($sql);
if ($debug) {
error_log('Getting course list');
}
$totalCourse = count($courseList);
$counter = 0;
foreach ($courseList as $courseData) {
$courseId = $courseData['id'];
if ($debug) {
error_log('Updating course: ' . $courseData['code']);
}
$sql = "SELECT * FROM c_item_property WHERE c_id = {$courseId}";
$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}";
error_log($sql);
$connection->executeQuery($sql);
}
if ($debug) {
// Print a status in the log once in a while
error_log("Process item #{$counter}/{$totalCourse}");
}
$counter++;
}
}
if ($debug) {
error_log('update gradebook_link');
}
// 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 thread_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);
}
}
}
if ($debug) {
error_log('update groups');
}
$sql = "SELECT * FROM groups";
$result = $connection->executeQuery($sql);
$groups = $result->fetchAll();
$oldGroups = array();
if (!empty($groups)) {
foreach ($groups as $group) {
if (empty($group['name'])) {
continue;
}
/*$group['description'] = Database::escape_string($group['description']);
$group['name'] = Database::escape_string($group['name']);
$sql = "INSERT INTO usergroup (name, group_type, description, picture, url, visibility, updated_at, created_at)
VALUES ('{$group['name']}', '1', '{$group['description']}', '{$group['picture_uri']}', '{$group['url']}', '{$group['visibility']}', '{$group['updated_on']}', '{$group['created_on']}')";
*/
$params = ['name' => $group['name'], 'description' => $group['description'], 'group_type' => 1, 'picture' => $group['picture_uri'], 'url' => $group['url'], 'visibility' => $group['visibility'], 'updated_at' => $group['updated_on'], 'created_at' => $group['created_on']];
$connection->insert('usergroup', $params);
//$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 group_id = {$data['group_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);
}
}
}
}
if ($debug) {
error_log('update extra fields');
}
// 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} ";
if ($debug) {
error_log($sql);
}
$result = $connection->query($sql);
$fields = $result->fetchAll();
foreach ($fields as $field) {
if ($debug) {
error_log("Loading field: " . $field['field_variable']);
}
$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();
$handlerId = null;
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 ($debug) {
error_log("Fetch all values for field");
}
}
if (!empty($values)) {
if ($debug) {
error_log("Saving field value in new table");
}
$k = 0;
foreach ($values as $value) {
if (isset($value[$handlerId])) {
/*
$extraFieldValue = new ExtraFieldValues();
$extraFieldValue
->setValue($value['field_value'])
->setField($extraField)
->setItemId($value[$handlerId]);
$em->persist($extraFieldValue);
$em->flush();
*/
// Insert without the use of the entity as it reduces
// speed to 2 records per second (much too slow)
$params = ['field_id' => $extraField->getId(), 'value' => $value['field_value'], 'item_id' => $value[$handlerId]];
$connection->insert('extra_field_values', $params);
if ($debug && $k % 10000 == 0) {
error_log("Saving field {$k}");
}
$k++;
}
}
}
}
}
if ($debug) {
error_log('Remove index');
}
// Drop temporary indexes added to increase speed of this function's queries
$sql = "ALTER TABLE c_document DROP INDEX tmpidx_doc";
$connection->executeQuery($sql);
$sql = "ALTER TABLE c_student_publication DROP INDEX tmpidx_stud";
$connection->executeQuery($sql);
$sql = "ALTER TABLE c_quiz DROP INDEX tmpidx_quiz";
$connection->executeQuery($sql);
$sql = "ALTER TABLE c_item_property DROP INDEX tmpidx_ip";
$connection->executeQuery($sql);
if ($debug) {
error_log('Finish fixId function');
}
}
/**
* @param array $params
* @param bool $show_query
*
* @return bool
*/
public function save($params, $show_query = false)
{
$fieldInfo = self::get_handler_field_info_by_field_variable($params['variable']);
$params = self::clean_parameters($params);
$params['extra_field_type'] = $this->extraFieldType;
if ($fieldInfo) {
return $fieldInfo['id'];
} else {
$extraField = new EntityExtraField();
$extraField->setExtraFieldType($params['extra_field_type'])->setFieldType($params['type'])->setVariable($params['variable'])->setVisible($params['visible'])->setDefaultValue($params['default_value'])->setChangeable($params['changeable'])->setDisplayText($params['display_text'])->setFilter($params['filter']);
//$id = parent::save($params, $show_query);
Database::getManager()->persist($extraField);
Database::getManager()->flush();
$id = $extraField->getId();
if ($id) {
/*$session_field_option = new ExtraFieldOption($this->type);
$params['field_id'] = $id;
$session_field_option->save($params);*/
}
return $id;
}
}