use correct pk and remove handling for copying studycourse, adds migration to...

Closes #2441

Merge request studip/studip!1638

1 files changed, 48 insertions, 0 deletions

diff --git a/db/migrations/5.3.19_cleanup_admission_seminar_user.php b/db/migrations/5.3.19_cleanup_admission_seminar_user.php
new file mode 100644
index 0000000..94b5cc8
--- /dev/null
+++ b/db/migrations/5.3.19_cleanup_admission_seminar_user.php
@@ -0,0 +1,48 @@
+<?php
+
+/**
+ * @see https://gitlab.studip.de/studip/studip/-/issues/2441
+ */
+final class CleanupAdmissionSeminarUser extends Migration
+{
+    public function description()
+    {
+        return 'Removes entries from table admission_seminar_user that are '
+             . 'already in table seminar_user';
+    }
+
+    protected function up()
+    {
+        // Fetch affected course ids
+        $query = "SELECT DISTINCT `seminar_id`
+                  FROM `admission_seminar_user`
+                  JOIN `seminar_user` USING (`seminar_id`, `user_id`)";
+        $course_ids = DBManager::get()->fetchFirst($query);
+
+        // Remove all entries that are already in seminar_user
+        $query = "DELETE `admission_seminar_user`
+                  FROM `admission_seminar_user`
+                  JOIN `seminar_user` USING (`seminar_id`, `user_id`)";
+        DBManager::get()->exec($query);
+
+        // Adjust positions in admission_seminar_user for all affected courses
+        foreach ($course_ids as $course_id) {
+            $query = "SELECT `user_id`
+                      FROM `admission_seminar_user`
+                      WHERE `seminar_id` = ? AND status = 'awaiting'
+                      ORDER BY `position`";
+            $user_ids = DBManager::get()->fetchFirst($query, [$course_id]);
+
+            foreach ($user_ids as $index => $user_id) {
+                $query = "UPDATE `admission_seminar_user`
+                          SET `position` = ?
+                          WHERE `seminar_id` = ? AND `user_id` = ?";
+                DBManager::get()->execute($query, [
+                    $index + 1,
+                    $course_id,
+                    $user_id
+                ]);
+            }
+        }
+    }
+}