Index: openacs-4/packages/dotlrn/www/admin/archived-communities.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/admin/archived-communities.tcl,v
diff -u -r1.8 -r1.9
--- openacs-4/packages/dotlrn/www/admin/archived-communities.tcl	7 Aug 2017 23:48:09 -0000	1.8
+++ openacs-4/packages/dotlrn/www/admin/archived-communities.tcl	22 May 2018 12:00:25 -0000	1.9
@@ -31,17 +31,65 @@
 
 set groups_pretty_plural "[parameter::get -localize -parameter class_instances_pretty_plural] / [parameter::get -localize -parameter clubs_pretty_plural]"
 
-set title "[_ dotlrn.archived_groups]"
+set title [_ dotlrn.archived_groups]
 set context_bar [list $title]
 
-db_multirow -extend { unarchive_url } archived_comms select_archived_comms {} {
+# AEG: This query has gotten pretty nasty.  Basically we need to
+# provide the site-wide admin more information about communities than
+# just the name.  Time information is key, so we try to associate
+# classes with a term.  If the community is not a class, we look to
+# see if the parent is a class.  We don't do the full recursion for
+# sub-subgroups etc.  We could push that into some expensive plsql if
+# the current version turns out to be too hackish.  Or maybe creation
+# date is all we need, and we can throw the rest out?
+db_multirow -extend {
+    unarchive_url
+    url
+    parent_url
+} archived_comms select_archived_comms {
+            select child.community_id,
+                   child.pretty_name, 
+                   child.description,
+                   parent.community_id as parent_community_id,
+                   parent.pretty_name as parent_pretty_name,
+                   parent.community_id as parent_community_id,
+                   class.term_name,
+                   class.term_year,
+                   parent_class.term_name as parent_term_name,
+                   parent_class.term_year as parent_term_year,
+                   to_char(o.creation_date, 'Mon YYYY') as creation_date,
+                   to_char(o.last_modified, 'Mon YYYY') as last_modified
+            from dotlrn_communities_all child left outer join
+                 dotlrn_communities_all parent using (community_id),
+                 dotlrn_communities_all child1 left outer join
+                 (select i.class_instance_id,
+                         t.term_name,
+                         t.term_year
+                  from dotlrn_class_instances i,
+                       dotlrn_terms t
+                  where t.term_id = i.term_id) class on child1.community_id = class.class_instance_id,
+                 dotlrn_communities_all child2 left outer join
+                 (select i.class_instance_id,
+                         t.term_name,
+                         t.term_year
+                  from dotlrn_class_instances i,
+                       dotlrn_terms t
+                  where t.term_id = i.term_id) parent_class on child2.community_id = parent_class.class_instance_id,
+                  acs_objects o
+            where child.archived_p = 't' 
+              and child1.archived_p = 't' and child2.archived_p = 't'
+              and child1.community_id = child.community_id
+              and child2.community_id = child.community_id
+              and o.object_id = child.community_id
+            order by child.pretty_name    
+} {
+    set url [dotlrn_community::get_community_url $community_id]
+    set parent_url [expr {$parent_community_id ne "" ? \
+                              [dotlrn_community::get_community_url $parent_community_id] : ""}]
     set description [ns_quotehtml $description]
     set unarchive_url "unarchive?community_id=$community_id"
 }
 
-
-ad_return_template
-
 # Local variables:
 #    mode: tcl
 #    tcl-indent-level: 4