Index: openacs-4/packages/dotlrn/tcl/term-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/tcl/Attic/term-procs-oracle.xql,v diff -u -N --- openacs-4/packages/dotlrn/tcl/term-procs-oracle.xql 8 Nov 2010 13:17:44 -0000 1.7 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,59 +0,0 @@ - - - - oracle8.1.6 - - - - insert - into dotlrn_terms - (term_id, term_name, term_year, start_date, end_date) - values - (:term_id, :term_name, :term_year, to_date(:start_date, :date_format), to_date(:end_date, :date_format)) - - - - - - update dotlrn_terms - set term_name = :term_name, - term_year = :term_year, - start_date = to_date(:start_date, :date_format), - end_date = to_date(:end_date, :date_format) - where term_id = :term_id - - - - - - select to_char(dotlrn_terms.start_date, :date_format) as start_date, - to_char(dotlrn_terms.start_date, 'YYYY') as year, - to_char(dotlrn_terms.start_date, 'MM') as month, - to_char(dotlrn_terms.start_date, 'DD') as day - from dotlrn_terms - where dotlrn_terms.term_id = :term_id - - - - - - select to_char(dotlrn_terms.end_date, :date_format) as end_date, - to_char(dotlrn_terms.end_date, 'YYYY') as year, - to_char(dotlrn_terms.end_date, 'MM') as month, - to_char(dotlrn_terms.end_date, 'DD') as day - from dotlrn_terms - where dotlrn_terms.term_id = :term_id - - - - - - select dotlrn_terms.term_name || ' ' || dotlrn_terms.term_year as term, - dotlrn_terms.term_id - from dotlrn_terms - where dotlrn_terms.end_date > sysdate - order by dotlrn_terms.start_date - - - - Index: openacs-4/packages/dotlrn/tcl/term-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/tcl/Attic/term-procs-postgresql.xql,v diff -u -N --- openacs-4/packages/dotlrn/tcl/term-procs-postgresql.xql 8 Nov 2010 13:17:44 -0000 1.3 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,59 +0,0 @@ - - - - postgresql7.1 - - - - insert - into dotlrn_terms - (term_id, term_name, term_year, start_date, end_date) - values - (:term_id, :term_name, :term_year, to_date(:start_date, :date_format), to_date(:end_date, :date_format)) - - - - - - update dotlrn_terms - set term_name = :term_name, - term_year = :term_year, - start_date = to_date(:start_date, :date_format), - end_date = to_date(:end_date, :date_format) - where term_id = :term_id - - - - - - select to_char(dotlrn_terms.start_date, :date_format) as start_date, - to_char(dotlrn_terms.start_date, 'YYYY') as year, - to_char(dotlrn_terms.start_date, 'MM') as month, - to_char(dotlrn_terms.start_date, 'DD') as day - from dotlrn_terms - where dotlrn_terms.term_id = :term_id - - - - - - select to_char(dotlrn_terms.end_date, :date_format) as end_date, - to_char(dotlrn_terms.end_date, 'YYYY') as year, - to_char(dotlrn_terms.end_date, 'MM') as month, - to_char(dotlrn_terms.end_date, 'DD') as day - from dotlrn_terms - where dotlrn_terms.term_id = :term_id - - - - - - select dotlrn_terms.term_name || ' ' || dotlrn_terms.term_year as term, - dotlrn_terms.term_id - from dotlrn_terms - where dotlrn_terms.end_date > current_timestamp - order by dotlrn_terms.start_date - - - - Index: openacs-4/packages/dotlrn/tcl/term-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/tcl/term-procs.xql,v diff -u -N -r1.4 -r1.5 --- openacs-4/packages/dotlrn/tcl/term-procs.xql 1 May 2002 01:19:47 -0000 1.4 +++ openacs-4/packages/dotlrn/tcl/term-procs.xql 22 Nov 2017 13:20:20 -0000 1.5 @@ -27,4 +27,57 @@ + + + insert + into dotlrn_terms + (term_id, term_name, term_year, start_date, end_date) + values + (:term_id, :term_name, :term_year, to_date(:start_date, :date_format), to_date(:end_date, :date_format)) + + + + + + update dotlrn_terms + set term_name = :term_name, + term_year = :term_year, + start_date = to_date(:start_date, :date_format), + end_date = to_date(:end_date, :date_format) + where term_id = :term_id + + + + + + select to_char(dotlrn_terms.start_date, :date_format) as start_date, + to_char(dotlrn_terms.start_date, 'YYYY') as year, + to_char(dotlrn_terms.start_date, 'MM') as month, + to_char(dotlrn_terms.start_date, 'DD') as day + from dotlrn_terms + where dotlrn_terms.term_id = :term_id + + + + + + select to_char(dotlrn_terms.end_date, :date_format) as end_date, + to_char(dotlrn_terms.end_date, 'YYYY') as year, + to_char(dotlrn_terms.end_date, 'MM') as month, + to_char(dotlrn_terms.end_date, 'DD') as day + from dotlrn_terms + where dotlrn_terms.term_id = :term_id + + + + + + select dotlrn_terms.term_name || ' ' || dotlrn_terms.term_year as term, + dotlrn_terms.term_id + from dotlrn_terms + where dotlrn_terms.end_date > current_timestamp + order by dotlrn_terms.start_date + + + Index: openacs-4/packages/dotlrn/tcl/class-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/tcl/Attic/class-procs-oracle.xql,v diff -u -N --- openacs-4/packages/dotlrn/tcl/class-procs-oracle.xql 16 May 2002 13:29:14 -0000 1.9 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,16 +0,0 @@ - - - - oracle8.1.6 - - - - select count(*) - from dual - where exists (select 1 - from dotlrn_terms - where dotlrn_terms.end_date > sysdate) - - - - Index: openacs-4/packages/dotlrn/tcl/class-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/tcl/Attic/class-procs-postgresql.xql,v diff -u -N --- openacs-4/packages/dotlrn/tcl/class-procs-postgresql.xql 16 May 2002 13:29:14 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,16 +0,0 @@ - - - - postgresql7.1 - - - - select count(*) - from dual - where exists (select 1 - from dotlrn_terms - where dotlrn_terms.end_date > now()) - - - - Index: openacs-4/packages/dotlrn/tcl/class-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/tcl/class-procs.xql,v diff -u -N -r1.16 -r1.17 --- openacs-4/packages/dotlrn/tcl/class-procs.xql 13 Jan 2005 13:57:20 -0000 1.16 +++ openacs-4/packages/dotlrn/tcl/class-procs.xql 22 Nov 2017 13:22:52 -0000 1.17 @@ -61,4 +61,14 @@ + + + select count(*) + from dual + where exists (select 1 + from dotlrn_terms + where dotlrn_terms.end_date > current_timestamp) + + + Index: openacs-4/packages/dotlrn/www/manage-memberships-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/Attic/manage-memberships-oracle.xql,v diff -u -N --- openacs-4/packages/dotlrn/www/manage-memberships-oracle.xql 7 Jun 2002 03:41:35 -0000 1.9 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,186 +0,0 @@ - - - - oracle8.1.6 - - - - 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' - MINUS - select dotlrn_member_rels_full.community_id 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/manage-memberships-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/Attic/manage-memberships-postgresql.xql,v diff -u -N --- openacs-4/packages/dotlrn/www/manage-memberships-postgresql.xql 15 Dec 2006 22:45:22 -0000 1.4 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,200 +0,0 @@ - - - - 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.active_start_date, - 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 (active_end_date > (select sysdate from dual) or active_end_date is null) - 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.active_start_date, - 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 active_end_date > (select sysdate from dual) - 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.active_start_date, - 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 - - - - - - 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) - - - - Index: openacs-4/packages/dotlrn/www/manage-memberships.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/manage-memberships.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/dotlrn/www/manage-memberships.xql 22 Nov 2017 13:29:18 -0000 1.1 @@ -0,0 +1,198 @@ + + + + + + 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.active_start_date, + 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 (active_end_date > current_timestamp or active_end_date is null) + 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.active_start_date, + 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 active_end_date > current_timestamp + 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.active_start_date, + 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 + + + + + + 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) + + + +