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

Closes #5475

Merge request studip/studip!4111

3 files changed, 104 insertions, 2 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,
+        ));
+    }
+}
diff --git a/lib/models/ConsultationBlock.php b/lib/models/ConsultationBlock.php
index fbc5de8..299820d 100644
--- a/lib/models/ConsultationBlock.php
+++ b/lib/models/ConsultationBlock.php
@@ -116,8 +116,8 @@ class ConsultationBlock extends SimpleORMap implements PrivacyObject
             return array_values($persons);
         };
 
-        $config['registered_callbacks']['after_store'][] = function (ConsultationBlock $block) {
-            $block->slots->updateEvents();
+        $config['registered_callbacks']['after_store'][] = function (ConsultationBlock $block): void {
+            $block->updateEvents();
         };
 
         parent::configure($config);
@@ -458,4 +458,14 @@ class ConsultationBlock extends SimpleORMap implements PrivacyObject
             date('H:i', $this->end)
         );
     }
+
+    /**
+     * Convenience method that updates all events for all slots of this block.
+     */
+    public function updateEvents(): void
+    {
+        $this->slots->each(function (ConsultationSlot $slot): void {
+            $slot->updateEvents();
+        });
+    }
 }
diff --git a/lib/models/ConsultationResponsibility.php b/lib/models/ConsultationResponsibility.php
index a446a73..ef67251 100644
--- a/lib/models/ConsultationResponsibility.php
+++ b/lib/models/ConsultationResponsibility.php
@@ -22,6 +22,13 @@ class ConsultationResponsibility extends SimpleORMap
             'foreign_key' => 'block_id',
         ];
 
+        $config['registered_callbacks']['after_store'][] = function (ConsultationResponsibility $responsibility): void {
+            $responsibility->block->updateEvents();
+        };
+        $config['registered_callbacks']['after_delete'][] = function (ConsultationResponsibility $responsibility): void {
+            $responsibility->block->updateEvents();
+        };
+
         parent::configure($config);
     }