Esempio n. 1
0
/**
* @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();
 }
Esempio n. 5
0
/**
* @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');
    }
}
Esempio n. 6
0
 /**
  * @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;
     }
 }