recalculate consultation slot events when responsibilities have changed and provide migration to fix broken entries, fixes #5475

Closes #5475

Merge request studip/studip!4111

1 files changed, 85 insertions, 0 deletions

diff --git a/db/migrations/5.3.30_repair_consultation_events.php b/db/migrations/5.3.30_repair_consultation_events.php
new file mode 100644
index 0000000..6c3e105
--- /dev/null
+++ b/db/migrations/5.3.30_repair_consultation_events.php
@@ -0,0 +1,85 @@
+<?php
+/**
+ * @see https://gitlab.studip.de/studip/studip/-/issues/5475
+ */
+final class RepairConsultationEvents extends Migration
+{
+    protected function up()
+    {
+        $block_ids = $this->getBlockIds();
+
+        if (!$block_ids) {
+            return;
+        }
+
+        $query = "DELETE `consultation_events`, `event_data`
+                  FROM `consultation_events`
+                  LEFT JOIN `event_data` USING (`event_id`)
+                  JOIN `consultation_slots` AS s USING (`slot_id`)
+                  WHERE `block_id` IN (?)
+                    AND NOT EXISTS (
+                      SELECT 1
+                      FROM `consultation_responsibilities`
+                      WHERE `block_id` = s.`block_id`
+                        AND `range_type` = 'user'
+                        AND `range_id` = `consultation_events`.`user_id`
+                    ) AND NOT EXISTS (
+                      SELECT 1
+                      FROM `consultation_responsibilities`
+                      JOIN `statusgruppe_user` ON `range_id` = `statusgruppe_id`
+                      WHERE `block_id` = s.`block_id`
+                        AND `range_type` = 'statusgroup'
+                        AND `statusgruppe_user`.`user_id` = `consultation_events`.`user_id`
+                    ) AND NOT EXISTS (
+                      SELECT 1
+                      FROM `consultation_responsibilities`
+                      JOIN `user_inst` ON `range_id` = `Institut_id`
+                      WHERE `block_id` = s.`block_id`
+                        AND `range_type` = 'institute'
+                        AND `user_inst`.`user_id` = `consultation_events`.`user_id`
+                    )";
+        DBManager::get()->execute($query, [$block_ids]);
+    }
+
+    private function getBlockIds(): array
+    {
+        // Responsibilities: Users
+        $query = "SELECT `block_id`
+                  FROM `consultation_responsibilities` AS r
+                  JOIN `consultation_slots` AS s USING (`block_id`)
+                  JOIN `consultation_events` AS e USING (`slot_id`)
+                  WHERE r.`range_type` = 'user'
+                  GROUP BY `block_id`
+                  HAVING COUNT(DISTINCT r.`range_id`) < COUNT(DISTINCT e.`user_id`)";
+        $block_ids0 = DBManager::get()->fetchFirst($query);
+
+        // Responsibilities: Statusgroups
+        $query = "SELECT `block_id`
+                  FROM `consultation_responsibilities` AS r
+                  JOIN `statusgruppe_user` AS su ON r.`range_id` = su.`statusgruppe_id`
+                  JOIN `consultation_slots` AS s USING (`block_id`)
+                  JOIN `consultation_events` AS e USING (`slot_id`)
+                  WHERE r.`range_type` = 'statusgroup'
+                  GROUP BY `block_id`
+                  HAVING COUNT(DISTINCT su.`user_id`) < COUNT(DISTINCT e.`user_id`)";
+        $block_ids1 = DBManager::get()->fetchFirst($query);
+
+        // Responsibilities: Institutes
+        $query = "SELECT `block_id`
+                  FROM `consultation_responsibilities` AS r
+                  JOIN `user_inst` AS ui ON r.`range_id` = ui.`Institut_id`
+                  JOIN `consultation_slots` AS s USING (`block_id`)
+                  JOIN `consultation_events` AS e USING (`slot_id`)
+                  WHERE r.`range_type` = 'statusgroup'
+                    AND ui.`inst_perms` IN ('tutor', 'dozent')
+                  GROUP BY `block_id`
+                  HAVING COUNT(DISTINCT ui.`user_id`) < COUNT(DISTINCT e.`user_id`)";
+        $block_ids2 = DBManager::get()->fetchFirst($query);
+
+        return array_unique(array_merge(
+            $block_ids0,
+            $block_ids1,
+            $block_ids2,
+        ));
+    }
+}