Index: openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql,v diff -u -N -r1.1.2.3 -r1.1.2.4 --- openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql 14 Sep 2007 20:38:53 -0000 1.1.2.3 +++ openacs-4/packages/assessment/sql/oracle/upgrade/upgrade-0.22d3-0.22d4.sql 14 Sep 2007 23:58:31 -0000 1.1.2.4 @@ -297,9 +297,9 @@ / show errors; -alter table as_session_items drop constraint as_session_items_item_fk; -alter table as_session_items drop constraint as_session_items_section_fk; -alter table as_session_items drop constraint as_session_items_session_fk; -alter table as_session_items add constraint "as_session_items_item_fk" FOREIGN KEY (as_item_id) REFERENCES as_items(as_item_id) on delete cascade; -alter table as_session_items add constraint "as_session_items_section_fk" FOREIGN KEY (section_id) REFERENCES as_sections(section_id) on delete cascade; -alter table as_session_items add constraint "as_session_items_session_fk" FOREIGN KEY (session_id) REFERENCES as_sessions(session_id) on delete cascade; +alter table as_session_items drop constraint as_sess_items_item_fk; +alter table as_session_items drop constraint as_sess_items_section_fk; +alter table as_session_items drop constraint as_sess_items_session_fk; +alter table as_session_items add constraint "as_sess_items_item_fk" FOREIGN KEY (as_item_id) REFERENCES as_items(as_item_id) on delete cascade; +alter table as_session_items add constraint "as_sess_items_section_fk" FOREIGN KEY (section_id) REFERENCES as_sections(section_id) on delete cascade; +alter table as_session_items add constraint "as_sess_items_session_fk" FOREIGN KEY (session_id) REFERENCES as_sessions(session_id) on delete cascade; Index: openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql,v diff -u -N -r1.11.2.3 -r1.11.2.4 --- openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql 12 Apr 2007 17:53:23 -0000 1.11.2.3 +++ openacs-4/packages/assessment/sql/postgresql/assessment-collected-data-create.sql 14 Sep 2007 23:58:32 -0000 1.11.2.4 @@ -199,13 +199,16 @@ create table as_session_items ( session_id integer constraint as_session_items_session_fk - references as_sessions, + references as_sessions + on delete cascade, section_id integer constraint as_session_items_section_fk - references as_sections, + references as_sections + on delete cascade, as_item_id integer constraint as_session_items_item_fk - references as_items, + references as_items + on delete cascade, sort_order integer, constraint as_session_items_pk primary key (session_id, section_id, as_item_id) Index: openacs-4/packages/assessment/www/asm-admin/sessions.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/www/asm-admin/sessions.tcl,v diff -u -N -r1.2.2.6 -r1.2.2.7 --- openacs-4/packages/assessment/www/asm-admin/sessions.tcl 14 Sep 2007 23:14:47 -0000 1.2.2.6 +++ openacs-4/packages/assessment/www/asm-admin/sessions.tcl 14 Sep 2007 23:58:32 -0000 1.2.2.7 @@ -138,50 +138,4 @@ } } -db_multirow sessions get_sessions [subst { - select a.*, - to_char(cs.completed_datetime, 'YYYY-MM-DD HH24:MI:SS') as completed_datetime, - to_char(coalesce(cs.last_mod_datetime, ns.last_mod_datetime), 'YYYY-MM-DD HH24:MI:SS') as last_mod_datetime, - coalesce(cs.subject_id, ns.subject_id) as subject_id, - coalesce(cs.session_id, ns.session_id) as session_id, - cs.percent_score - - from (select a.assessment_id, cr.title, cr.item_id, cr.revision_id, - u.user_id, u.first_names, u.last_name - - from as_assessments a, cr_revisions cr, cr_items ci, acs_users_all u - where a.assessment_id = cr.revision_id - and cr.revision_id = ci.latest_revision - and ci.parent_id = :folder_id - and u.user_id <> 0 - and exists ( - select 1 from acs_object_party_privilege_map - where object_id = :context_object_id - and party_id = u.user_id - and privilege = 'read')) a - left join (select as_sessions.*, cr.item_id - from as_sessions, cr_revisions cr - where session_id in (select max(session_id) - from as_sessions, acs_objects o - where not completed_datetime is null - and o.object_id = session_id - and o.package_id = :package_id - group by subject_id, assessment_id ) - and revision_id=assessment_id) cs - on (a.user_id = cs.subject_id and a.item_id = cs.item_id) - - left join (select * - from as_sessions - where session_id in (select max(session_id) - from as_sessions, acs_objects o - where completed_datetime is null - and o.object_id = session_id - and o.package_id = :package_id - group by subject_id, assessment_id)) ns - on (a.user_id = ns.subject_id and a.assessment_id = ns.assessment_id) - - where 1=1 - [list::filter_where_clauses -and -name "sessions"] - - order by lower(a.title), lower(a.last_name), lower(a.first_names) -}] +db_multirow sessions get_sessions "" \ No newline at end of file Index: openacs-4/packages/assessment/www/asm-admin/sessions.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/assessment/www/asm-admin/sessions.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/assessment/www/asm-admin/sessions.xql 14 Sep 2007 23:58:32 -0000 1.1.2.1 @@ -0,0 +1,57 @@ + + + + + + + + + + select a.*, + to_char(cs.completed_datetime, 'YYYY-MM-DD HH24:MI:SS') as completed_datetime, + to_char(coalesce(cs.last_mod_datetime, ns.last_mod_datetime), 'YYYY-MM-DD HH24:MI:SS') as last_mod_datetime, + coalesce(cs.subject_id, ns.subject_id) as subject_id, + coalesce(cs.session_id, ns.session_id) as session_id, + cs.percent_score + + from (select a.assessment_id, cr.title, cr.item_id, cr.revision_id, + u.user_id, u.first_names, u.last_name + + from as_assessments a, cr_revisions cr, cr_items ci, acs_users_all u + where a.assessment_id = cr.revision_id + and cr.revision_id = ci.latest_revision + and ci.parent_id = :folder_id + and u.user_id <> 0 + and exists ( + select 1 from acs_object_party_privilege_map + where object_id = :context_object_id + and party_id = u.user_id + and privilege = 'read')) a + left join (select as_sessions.*, cr.item_id + from as_sessions, cr_revisions cr + where session_id in (select max(session_id) + from as_sessions, acs_objects o + where not completed_datetime is null + and o.object_id = session_id + and o.package_id = :package_id + group by subject_id, assessment_id ) + and revision_id=assessment_id) cs + on (a.user_id = cs.subject_id and a.item_id = cs.item_id) + + left join (select * + from as_sessions + where session_id in (select max(session_id) + from as_sessions, acs_objects o + where completed_datetime is null + and o.object_id = session_id + and o.package_id = :package_id + group by subject_id, assessment_id)) ns + on (a.user_id = ns.subject_id and a.assessment_id = ns.assessment_id) + + where 1=1 + [list::filter_where_clauses -and -name "sessions"] + + order by lower(a.title), lower(a.last_name), lower(a.first_names) + + + \ No newline at end of file