handling foreign owned reports of templates to be deleted when they aren't scheduled...

[migration-tools.git] / remove_ou_data / 10_clear_vandelay_reports.sql

diff --git a/remove_ou_data/10_clear_vandelay_reports.sql b/remove_ou_data/10_clear_vandelay_reports.sql
index 2a44d2e..652d0a9 100644 (file)
--- a/remove_ou_data/10_clear_vandelay_reports.sql
+++ b/remove_ou_data/10_clear_vandelay_reports.sql
@@ -27,12 +27,17 @@ BEGIN;
 DELETE FROM vandelay.queue WHERE owner IN
 (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
 
-DELETE FROM reporter.report_folder WHERE owner IN
+DELETE FROM reporter.report WHERE owner IN
 (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
 
-DELETE FROM reporter.report WHERE owner IN
+DELETE FROM reporter.report_folder WHERE owner IN
 (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
 
+DELETE FROM reporter.report WHERE template IN 
+    (SELECT id FROM reporter.template WHERE owner IN
+        (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+    ) AND id NOT IN (SELECT report FROM reporter.schedule WHERE complete_time IS NULL);
+
 DELETE FROM reporter.output_folder WHERE owner IN
 (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));