Index: openacs-4/packages/dotlrn/www/communities-chunk-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/communities-chunk-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/communities-chunk-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,262 @@ + + + + postgresql7.1 + + + + select count(*) + from dotlrn_communities_not_closed + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 1 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_active_communities dotlrn_communities, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_member_rels_approved.community_id = dotlrn_communities.community_id + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 1 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_communities dotlrn_communities, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_member_rels_approved.community_id = dotlrn_communities.community_id + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 0 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_active_comms_not_closed dotlrn_communities + where not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_communities.community_id) + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 0 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_communities_not_closed dotlrn_communities + where not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_communities.community_id) + and dotlrn_communities.parent_community_id is null + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + + + select count(*) + from dotlrn_communities_not_closed dotlrn_communities + where dotlrn_communities.community_type = :community_type + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 1 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_active_communities dotlrn_communities, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_member_rels_approved.community_id = dotlrn_communities.community_id + and dotlrn_communities.community_type = :community_type + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 1 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_communities dotlrn_communities, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_member_rels_approved.community_id = dotlrn_communities.community_id + and dotlrn_communities.community_type = :community_type + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 0 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_active_comms_not_closed dotlrn_communities + where not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_communities.community_id) + and dotlrn_communities.community_type = :community_type + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + 0 as member_p, + CASE + WHEN acs_permission__permission_p(:user_id, + dotlrn_communities.community_id, + 'admin') = 'f' + THEN 0 + ELSE 1 + END as admin_p + (select dotlrn_community_types.community_type + from dotlrn_community_types + where dotlrn_community_types.tree_sortkey = tree_ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type + from dotlrn_communities_not_closed dotlrn_communities + where not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_communities.community_id) + and dotlrn_communities.community_type = :community_type + order by root_community_type, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name + + + + Index: openacs-4/packages/dotlrn/www/community-member-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/Attic/community-member-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/community-member-oracle.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + select acs_object_types.pretty_name, + acs_object_types.pretty_plural, + acs_objects.creation_date, + acs_object.name(acs_objects.object_id) object_name + from acs_objects, + acs_object_types + where acs_objects.creation_user = :user_id + and acs_objects.object_type in ('acs_message') + and acs_objects.object_type = acs_object_types.object_type + order by object_name, + creation_date + + + + Index: openacs-4/packages/dotlrn/www/community-member-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/Attic/community-member-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/community-member-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,22 @@ + + + + postgresql7.1 + + + + select acs_object_types.pretty_name, + acs_object_types.pretty_plural, + acs_objects.creation_date, + acs_object__name(acs_objects.object_id) as object_name + from acs_objects, + acs_object_types + where acs_objects.creation_user = :user_id + and acs_objects.object_type in ('acs_message') + and acs_objects.object_type = acs_object_types.object_type + order by object_name, + creation_date + + + + Index: openacs-4/packages/dotlrn/www/community-types-chunk-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/community-types-chunk-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/community-types-chunk-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + select dotlrn_community_types.community_type, + dotlrn_community_types.pretty_name, + dotlrn_community_types.description, + (select site_node__url(site_nodes.node_id) + from site_nodes + where site_nodes.object_id = dotlrn_community_types.package_id) as url + from dotlrn_community_types + where dotlrn_community_types.supertype = :community_type + + + + Index: openacs-4/packages/dotlrn/www/dotlrn-main-portlet-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/dotlrn-main-portlet-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/dotlrn-main-portlet-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,31 @@ + + + + postgresql7.1 + + + + select dotlrn_communities.*, + dotlrn_community__url(dotlrn_communities.community_id) as url, + (CASE + WHEN + dotlrn_communities.community_type = 'dotlrn_community' + THEN 'dotlrn_community' + WHEN dotlrn_communities.community_type = 'dotlrn_club' + THEN 'dotlrn_club' + ELSE 'dotlrn_class_instance' + END) as simple_community_type, + tree_level(dotlrn_communities.tree_sortkey) as tree_level, + coalesce((select tree_level(dotlrn_community_types.tree_sortkey) + from dotlrn_community_types + where dotlrn_community_types.community_type = dotlrn_communities.community_type), 0) as community_type_level, + acs_permission__permission_p(dotlrn_communities.community_id, :user_id, 'admin') as admin_p + from dotlrn_communities, + dotlrn_member_rels_approved + where dotlrn_communities.community_id = dotlrn_member_rels_approved.community_id + and dotlrn_member_rels_approved.user_id = :user_id + order by dotlrn_communities.tree_sortkey + + + + Index: openacs-4/packages/dotlrn/www/manage-memberships-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/Attic/manage-memberships-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/manage-memberships-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,183 @@ + + + + postgresql7.1 + + + + select count(*) + from dotlrn_class_instances_full, + dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id + + + + + + select dotlrn_class_instances_full.*, + dotlrn_member_rels_full.role, + dotlrn_member_rels_full.rel_type, + dotlrn_member_rels_full.member_state + from dotlrn_class_instances_full, + dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_class_instances_full.*, + dotlrn_member_rels_full.role, + dotlrn_member_rels_full.rel_type, + dotlrn_member_rels_full.member_state + from dotlrn_class_instances_full, + dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_class_instances_full.department_key = :member_department_key + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_class_instances_full.*, + dotlrn_member_rels_full.role, + dotlrn_member_rels_full.rel_type, + dotlrn_member_rels_full.member_state + from dotlrn_class_instances_full, + dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_class_instances_full.term_id = :member_term_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_class_instances_full.*, + dotlrn_member_rels_full.role, + dotlrn_member_rels_full.rel_type, + dotlrn_member_rels_full.member_state + from dotlrn_class_instances_full, + dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_class_instances_full.department_key = :member_department_key + and dotlrn_class_instances_full.term_id = :member_term_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_clubs_full.*, + dotlrn_member_rels_full.role, + dotlrn_member_rels_full.rel_type, + dotlrn_member_rels_full.member_state + from dotlrn_clubs_full, + dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_clubs_full.club_id + order by dotlrn_clubs_full.pretty_name, + dotlrn_clubs_full.community_key + + + + + + select count(*) + from dotlrn_class_instances_full + where dotlrn_class_instances_full.join_policy <> 'closed' + and not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id) + + + + + + select dotlrn_class_instances_full.* + from dotlrn_class_instances_full + where dotlrn_class_instances_full.join_policy <> 'closed' + and not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id) + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_class_instances_full.* + from dotlrn_class_instances_full + where dotlrn_class_instances_full.department_key = :non_member_department_key + and dotlrn_class_instances_full.join_policy <> 'closed' + and not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id) + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_class_instances_full.* + from dotlrn_class_instances_full + where dotlrn_class_instances_full.term_id = :non_member_term_id + and dotlrn_class_instances_full.join_policy <> 'closed' + and not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id) + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_class_instances_full.* + from dotlrn_class_instances_full + where dotlrn_class_instances_full.department_key = :non_member_department_key + and dotlrn_class_instances_full.term_id = :non_member_term_id + and dotlrn_class_instances_full.join_policy <> 'closed' + and not exists (select 1 + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id + and dotlrn_member_rels_full.community_id = dotlrn_class_instances_full.class_instance_id) + order by dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_clubs_full.* + from dotlrn_clubs_full, + (select f.club_id + from dotlrn_clubs_full f + where f.join_policy <> 'closed' + and f.club_id not in (select dotlrn_member_rels_full.community_id as club_id + from dotlrn_member_rels_full + where dotlrn_member_rels_full.user_id = :user_id)) non_member_clubs + where dotlrn_clubs_full.club_id = non_member_clubs.club_id + order by dotlrn_clubs_full.pretty_name, + dotlrn_clubs_full.community_key + + + + Index: openacs-4/packages/dotlrn/www/member-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/member-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/member-add-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + + select user_id, + first_names, + last_name, + email + from dotlrn_users + where lower(last_name) like lower('%' || :search_text || '%') + or lower(email) like lower('%' || :search_text || '%') + and user_id not in (select user_id + from dotlrn_member_rels_full + where community_id = :community_id) + + + + Index: openacs-4/packages/dotlrn/www/members-chunk-table-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/members-chunk-table-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/members-chunk-table-oracle.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,29 @@ + + + + oracle8.1.6 + + + select dotlrn_member_rels_approved.rel_id, + dotlrn_member_rels_approved.rel_type, + dotlrn_member_rels_approved.role, + dotlrn_member_rels_approved.user_id, + registered_users.first_names, + registered_users.last_name, + registered_users.email + from registered_users, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.community_id = :community_id + and dotlrn_member_rels_approved.user_id = registered_users.user_id + order by + decode(role,'instructor',1,'admin',2,'teaching_assistant',3,'course_assistant',4,'course_admin',5,'student',6,'member',7) + asc, last_name + -- note, last_name should be sorted by $order, but b/c this + -- query gets called by ad_table, ad_table would have to know + -- to uplevel order.. not going to happen. So no reversals on + -- last name for now, sorry. + + + + + Index: openacs-4/packages/dotlrn/www/members-chunk-table-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/members-chunk-table-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/members-chunk-table-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,42 @@ + + + + postgresql7.1 + + + select dotlrn_member_rels_approved.rel_id, + dotlrn_member_rels_approved.rel_type, + dotlrn_member_rels_approved.role, + dotlrn_member_rels_approved.user_id, + registered_users.first_names, + registered_users.last_name, + registered_users.email + from registered_users, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.community_id = :community_id + and dotlrn_member_rels_approved.user_id = registered_users.user_id + order by ( + CASE + WHEN role = 'instructor' + THEN 1 + WHEN role = 'admin' + THEN 2 + WHEN role = 'teaching_assistant' + THEN 3 + WHEN role = 'course_assistant' + THEN 4 + WHEN role = 'student' + THEN 6 + WHEN role = 'member' + THEN 7 + END + ) asc, + last_name + -- note, last_name should be sorted by $order, but b/c this + -- query gets called by ad_table, ad_table would have to know + -- to uplevel order.. not going to happen. So no reversals on + -- last name for now, sorry. + + + + Index: openacs-4/packages/dotlrn/www/members-chunk-table.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/members-chunk-table.tcl,v diff -u -r1.14 -r1.15 --- openacs-4/packages/dotlrn/www/members-chunk-table.tcl 5 Jul 2002 08:13:46 -0000 1.14 +++ openacs-4/packages/dotlrn/www/members-chunk-table.tcl 15 Jul 2002 20:12:46 -0000 1.15 @@ -131,27 +131,12 @@ } } -set sql " - select dotlrn_member_rels_approved.rel_id, - dotlrn_member_rels_approved.rel_type, - dotlrn_member_rels_approved.role, - dotlrn_member_rels_approved.user_id, - registered_users.first_names, - registered_users.last_name, - registered_users.email - from registered_users, - dotlrn_member_rels_approved - where dotlrn_member_rels_approved.community_id = :community_id - and dotlrn_member_rels_approved.user_id = registered_users.user_id - [ad_order_by_from_sort_spec $orderby $table_def] -" - set table [ad_table \ -Tmissing_text {
No members
} \ -Textra_vars {referer my_user_id community_id rel_type} \ -Torderby $orderby \ select_current_members \ - $sql \ + "" \ $table_def ] Index: openacs-4/packages/dotlrn/www/my-communities-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/my-communities-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/my-communities-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,33 @@ + + + + postgresql7.1 + + + + select dotlrn_communities.community_id, + dotlrn_communities.community_type, + dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_communities.package_id, + dotlrn_community__url(dotlrn_communities.community_id) as url, + dotlrn_member_rels_approved.rel_type, + dotlrn_member_rels_approved.role, + '' as role_pretty_name, + CASE + WHEN + dotlrn_community__admin_p(dotlrn_communities.community_id, + dotlrn_member_rels_approved.user_id) = 'f' + THEN 0 + ELSE 1 + END as admin_p + from dotlrn_communities, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_communities.community_id = dotlrn_member_rels_approved.community_id + and dotlrn_communities.community_type != 'dotlrn_community' + order by dotlrn_communities.pretty_name + + + + Index: openacs-4/packages/dotlrn/www/admin/club-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/admin/club-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/admin/club-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,16 @@ + + + + postgresql7.1 + + + + select dotlrn_communities.pretty_name, + dotlrn_communities.description, + dotlrn_community__url(:club_id) as url + from dotlrn_communities + where dotlrn_communities.community_id = :club_id + + + + Index: openacs-4/packages/dotlrn/www/admin/user-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/admin/user-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/admin/user-edit-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + select dotlrn_users.id, + dotlrn_users.first_names, + dotlrn_users.last_name, + dotlrn_users.email, + dotlrn_users.type, + acs_permission__permission_p(:dotlrn_package_id, :user_id, 'read_private_data') as read_private_data_p + from dotlrn_users + where dotlrn_users.user_id = :user_id + + + + Index: openacs-4/packages/dotlrn/www/admin/user-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/admin/user-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/admin/user-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,81 @@ + + + + postgresql7.1 + + + + select dotlrn_users.*, + acs_permission__permission_p(:dotlrn_package_id, :user_id, 'read_private_data') as read_private_data_p + from dotlrn_users + where dotlrn_users.user_id = :user_id + + + + + + select cr_items.live_revision as revision_id, + coalesce(cr_revisions.title, 'view this portrait') portrait_title + from acs_rels, + cr_items, + cr_revisions + where acs_rels.object_id_two = cr_items.item_id + and cr_items.live_revision = cr_revisions.revision_id + and acs_rels.object_id_one = :user_id + and acs_rels.rel_type = 'user_portrait_rel' + + + + + + select dotlrn_class_instances_full.*, + dotlrn_member_rels_approved.rel_type, + dotlrn_member_rels_approved.role, + '' as role_pretty_name, + acs_permission__permission_p(dotlrn_class_instances_full.class_instance_id, :user_id, 'admin') as admin_p + from dotlrn_class_instances_full, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_member_rels_approved.community_id = dotlrn_class_instances_full.class_instance_id + order by dotlrn_class_instances_full.department_name, + dotlrn_class_instances_full.department_key, + dotlrn_class_instances_full.pretty_name, + dotlrn_class_instances_full.community_key + + + + + + select dotlrn_clubs_full.*, + dotlrn_member_rels_approved.rel_type, + dotlrn_member_rels_approved.role, + '' as role_pretty_name, + acs_permission__permission_p(dotlrn_clubs_full.club_id, :user_id, 'admin') as admin_p + from dotlrn_clubs_full, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_member_rels_approved.community_id = dotlrn_clubs_full.club_id + order by dotlrn_clubs_full.pretty_name, + dotlrn_clubs_full.community_key + + + + + + select dotlrn_communities.*, + dotlrn_community__url(dotlrn_communities.community_id) as url, + dotlrn_member_rels_approved.rel_type, + dotlrn_member_rels_approved.role, + '' as role_pretty_name, + acs_permission__permission_p(dotlrn_communities.community_id, :user_id, 'admin') as admin_p + from dotlrn_communities, + dotlrn_member_rels_approved + where dotlrn_member_rels_approved.user_id = :user_id + and dotlrn_member_rels_approved.community_id = dotlrn_communities.community_id + and dotlrn_communities.community_type = 'dotlrn_community' + order by dotlrn_communities.pretty_name, + dotlrn_communities.community_key + + + + Index: openacs-4/packages/dotlrn/www/admin/users-chunk-large-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/admin/users-chunk-large-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/admin/users-chunk-large-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,86 @@ + + + + postgresql7.1 + + + + select dotlrn_users.user_id, + dotlrn_users.first_names, + dotlrn_users.last_name, + dotlrn_users.email, + acs_permission__permission_p(:dotlrn_package_id, dotlrn_users.user_id, 'read_private_data') as read_private_data_p, + acs_permission__permission_p(:root_object_id, dotlrn_users.user_id, 'admin') as site_wide_admin_p + from dotlrn_users + where dotlrn_users.type = :type + and ( + lower(dotlrn_users.last_name) like lower('%' || :search_text || '%') + or lower(dotlrn_users.first_names) like lower('%' || :search_text || '%') + or lower(dotlrn_users.email) like lower('%' || :search_text || '%') + ) + order by dotlrn_users.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.object_id_one = acs__magic_object_id('registered_users') + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'approved' + and not exists (select 1 + from acs_rels a, + dotlrn_user_types + where a.object_id_one = dotlrn_user_types.group_id + and a.object_id_two = acs_rels.object_id_two) + and ( + lower(persons.last_name) like lower('%' || :search_text || '%') + or lower(persons.first_names) like lower('%' || :search_text || '%') + or lower(parties.email) like lower('%' || :search_text || '%') + ) + order by persons.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.object_id_one = acs__magic_object_id('registered_users') + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'banned' + and ( + lower(persons.last_name) like lower('%' || :search_text || '%') + or lower(persons.first_names) like lower('%' || :search_text || '%') + or lower(parties.email) like lower('%' || :search_text || '%') + ) + order by persons.last_name + + + + Index: openacs-4/packages/dotlrn/www/admin/users-chunk-medium-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/admin/users-chunk-medium-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/admin/users-chunk-medium-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,140 @@ + + + + postgresql7.1 + + + + select dotlrn_users.user_id, + dotlrn_users.first_names, + dotlrn_users.last_name, + dotlrn_users.email, + acs_permission__permission_p(:dotlrn_package_id, dotlrn_users.user_id, 'read_private_data') as read_private_data_p, + acs_permission__permission_p(:root_object_id, dotlrn_users.user_id, 'admin') as site_wide_admin_p + from dotlrn_users + where dotlrn_users.type = :type + and upper(substr(dotlrn_users.last_name, 1, 1)) = :section + order by dotlrn_users.last_name + + + + + + select dotlrn_users.user_id, + dotlrn_users.first_names, + dotlrn_users.last_name, + dotlrn_users.email, + acs_permission__permission_p(:dotlrn_package_id, dotlrn_users.user_id, 'read_private_data') as read_private_data_p, + acs_permission__permission_p(:root_object_id, dotlrn_users.user_id, 'admin') as site_wide_admin_p + from dotlrn_users + where dotlrn_users.type = :type + and upper(substr(dotlrn_users.last_name, 1, 1)) not in ('[join $dimension_list "\', \'"]') + order by dotlrn_users.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'approved' + and not exists (select 1 + from acs_rels a, + dotlrn_user_types + where a.object_id_one = dotlrn_user_types.group_id + and a.object_id_two = acs_rels.object_id_two) + and upper(substr(persons.last_name, 1, 1)) = :section + order by persons.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'approved' + and not exists (select 1 + from acs_rels a, + dotlrn_user_types + where a.object_id_one = dotlrn_user_types.group_id + and a.object_id_two = acs_rels.object_id_two) + and upper(substr(person.last_name, 1, 1)) not in ('[join $dimension_list "\', \'"]') + order by persons.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.object_id_one = acs__magic_object_id('registered_users') + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'banned' + and upper(substr(persons.last_name, 1, 1)) = :section + order by persons.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.object_id_one = acs__magic_object_id('registered_users') + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'banned' + and upper(substr(person.last_name, 1, 1)) not in ('[join $dimension_list "\', \'"]') + order by persons.last_name + + + + Index: openacs-4/packages/dotlrn/www/admin/users-chunk-small-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/admin/users-chunk-small-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/admin/users-chunk-small-postgresql.xql 15 Jul 2002 20:12:46 -0000 1.1 @@ -0,0 +1,71 @@ + + + + postgresql7.1 + + + + select dotlrn_users.user_id, + dotlrn_users.first_names, + dotlrn_users.last_name, + dotlrn_users.email, + acs_permission__permission_p(:dotlrn_package_id, dotlrn_users.user_id, 'read_private_data') as read_private_data_p, + acs_permission__permission_p(:root_object_id, dotlrn_users.user_id, 'admin') as site_wide_admin_p + from dotlrn_users + where dotlrn_users.type = :type + order by dotlrn_users.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.object_id_one = acs__magic_object_id('registered_users') + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'approved' + and not exists (select 1 + from acs_rels a, + dotlrn_user_types + where a.object_id_one = dotlrn_user_types.group_id + and a.object_id_two = acs_rels.object_id_two) + order by persons.last_name + + + + + + select users.user_id, + persons.first_names, + persons.last_name, + parties.email, + 'f' as read_private_data_p, + acs_permission__permission_p(:root_object_id, users.user_id, 'admin') as site_wide_admin_p + from parties, + users, + persons, + acs_rels, + membership_rels + where parties.party_id = users.user_id + and users.user_id = persons.person_id + and persons.person_id = acs_rels.object_id_two + and acs_rels.object_id_one = acs__magic_object_id('registered_users') + and acs_rels.rel_id = membership_rels.rel_id + and membership_rels.member_state = 'banned' + order by persons.last_name + + + +