Index: openacs-4/packages/evaluation/lib/evaluations-chunk-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/lib/Attic/evaluations-chunk-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/lib/evaluations-chunk-oracle.xql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,39 @@ + + + + oracle8.1.6 + + + + + select evaluation.party_id(:user_id,:task_id) + + + + + + select round(ese.grade,2) as grade, + ese.evaluation_id, + ese.description as comments, + ese.show_student_p, + round((ese.grade*:t_weight*:g_weight)/10000,2) as task_grade + from evaluation_student_evalsi ese, cr_items cri + where ese.task_item_id = :task_item_id + and cri.live_revision = ese.evaluation_id + and ese.party_id = evaluation.party_id(:user_id,:task_id) + + + + + + select ea.data as answer_data, + ea.title as answer_title, + ea.answer_id + from evaluation_answersi ea, cr_items cri + where ea.task_item_id = :task_item_id + and cri.live_revision = ea.answer_id + and ea.party_id = evaluation.party_id(:user_id,:task_id) + + + + Index: openacs-4/packages/evaluation/lib/evaluations-chunk-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/lib/Attic/evaluations-chunk-postgresql.xql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/evaluation/lib/evaluations-chunk-postgresql.xql 10 Sep 2004 19:16:28 -0000 1.6 +++ openacs-4/packages/evaluation/lib/evaluations-chunk-postgresql.xql 31 Mar 2005 11:09:27 -0000 1.7 @@ -3,41 +3,6 @@ postgresql7.3 - - - - select et.task_name, - round(et.weight,2) as task_weight, - et.task_id - from evaluation_tasksi et, cr_items cri - where grade_item_id = :grade_item_id - and cri.live_revision = et.task_id - $evaluations_orderby - - - - - - - - select et.task_name, - et.task_item_id, - et.weight as t_weight, - eg.weight as g_weight, - round((et.weight*eg.weight)/100,2) as task_weight, - et.number_of_members, - et.task_id - from evaluation_grades eg, - evaluation_tasksi et, - cr_items cri - where eg.grade_id = :grade_id - and eg.grade_item_id = et.grade_item_id - and cri.live_revision = et.task_id - $evaluations_orderby - - - - Index: openacs-4/packages/evaluation/lib/evaluations-chunk.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/lib/evaluations-chunk.xql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/evaluation/lib/evaluations-chunk.xql 28 Dec 2004 15:40:39 -0000 1.4 +++ openacs-4/packages/evaluation/lib/evaluations-chunk.xql 31 Mar 2005 11:09:27 -0000 1.5 @@ -3,10 +3,40 @@ + select grade_plural_name, weight as grade_weight from evaluation_grades where grade_id = :grade_id + + - select grade_plural_name, weight as grade_weight from evaluation_grades where grade_id = :grade_id - + + + select et.task_name, + round(et.weight,2) as task_weight, + et.task_id + from evaluation_tasksi et, cr_items cri + where grade_item_id = :grade_item_id + and cri.live_revision = et.task_id + $evaluations_orderby + + + select et.task_name, + et.task_item_id, + et.weight as t_weight, + eg.weight as g_weight, + round((et.weight*eg.weight)/100,2) as task_weight, + et.number_of_members, + et.task_id + from evaluation_grades eg, + evaluation_tasksi et, + cr_items cri + where eg.grade_id = :grade_id + and eg.grade_item_id = et.grade_item_id + and cri.live_revision = et.task_id + $evaluations_orderby + + + + Index: openacs-4/packages/evaluation/lib/tasks-chunk-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/lib/Attic/tasks-chunk-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/lib/tasks-chunk-oracle.xql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,82 @@ + + + + oracle8.1.6 + + + + select et.task_name, et.number_of_members, et.task_id, + to_char(et.due_date,'YYYY-MM-DD HH24:MI:SS') as due_date_ansi, + et.online_p, + et.late_submit_p, + et.task_item_id, + et.item_id, + et.requires_grade_p, et.description, et.grade_item_id, + nvl(round(cr.content_length/1024,0),0) as content_length, + et.data as task_data, + crmt.label as pretty_mime_type, + cr.title as task_title, + et.task_id as revision_id + from cr_revisions cr, + evaluation_tasksi et, + cr_items cri, + cr_mime_types crmt + where cr.revision_id = et.revision_id + and et.grade_item_id = :grade_item_id + and cri.live_revision = et.task_id + and et.mime_type = crmt.mime_type + $assignments_orderby + + + + + + select et.task_name, et.number_of_members, et.task_id, + to_char(et.due_date,'YYYY-MM-DD HH24:MI:SS') as due_date_ansi, + et.online_p, + et.late_submit_p, + et.item_id, + et.task_item_id, + et.due_date, + et.requires_grade_p, et.description, et.grade_item_id, + cr.title as task_title, + et.data as task_data, + et.task_id as revision_id, + nvl(round(cr.content_length/1024,0),0) as content_length, + et.late_submit_p, + crmt.label as pretty_mime_type + from cr_revisions cr, + evaluation_tasksi et, + cr_items cri, + cr_mime_types crmt + where cr.revision_id = et.revision_id + and grade_item_id = :grade_item_id + and cri.live_revision = et.task_id + and et.mime_type = crmt.mime_type + $assignments_orderby + + + + + + select 1 from dual where :due_date > sysdate + + + + + + select evaluation.party_id(:user_id,:task_id) from dual + + + + + + select ea.answer_id + from evaluation_answers ea, cr_items cri + where ea.task_item_id = :task_item_id + and cri.live_revision = ea.answer_id + and ea.party_id = evaluation.party_id(:user_id,:task_id) + + + + Index: openacs-4/packages/evaluation/lib/tasks-chunk-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/lib/Attic/tasks-chunk-postgresql.xql,v diff -u -r1.13 -r1.14 --- openacs-4/packages/evaluation/lib/tasks-chunk-postgresql.xql 17 Dec 2004 18:11:00 -0000 1.13 +++ openacs-4/packages/evaluation/lib/tasks-chunk-postgresql.xql 31 Mar 2005 11:09:27 -0000 1.14 @@ -15,18 +15,19 @@ et.requires_grade_p, et.description, et.grade_item_id, coalesce(round(cr.content_length/1024,0),0) as content_length, et.data as task_data, - cr.title as task_title, crmt.label as pretty_mime_type, + cr.title as task_title, et.task_id as revision_id from cr_revisions cr, evaluation_tasksi et, cr_items cri, cr_mime_types crmt - where cr.revision_id = et.revision_id - and et.grade_item_id = :grade_item_id + where cr.revision_id = et.revision_id + and et.grade_item_id = :grade_item_id and cri.live_revision = et.task_id and et.mime_type = crmt.mime_type $assignments_orderby + @@ -50,7 +51,7 @@ from cr_revisions cr, evaluation_tasksi et, cr_items cri, - cr_mime_types crmt + cr_mime_types crmt where cr.revision_id = et.revision_id and grade_item_id = :grade_item_id and cri.live_revision = et.task_id @@ -86,36 +87,14 @@ - - - - select eg.grade_name, eg.grade_plural_name - from evaluation_grades eg, cr_items cri - where eg.grade_item_id = :grade_item_id - and cri.live_revision = eg.grade_id - - - - - - - - select ets.solution_id - from evaluation_tasks_sols ets, cr_items cri - where ets.task_item_id = :task_item_id - and cri.live_revision = ets.solution_id - - - - select ea.answer_id from evaluation_answers ea, cr_items cri where ea.task_item_id = :task_item_id and cri.live_revision = ea.answer_id - and ea.party_id = + and ea.party_id = ( select CASE WHEN et3.number_of_members = 1 THEN :user_id @@ -132,10 +111,9 @@ where et3.task_id = :task_id ) --- evaluation__party_id(:user_id,:task_id) + --evaluation__party_id(:user_id,:task_id) - Index: openacs-4/packages/evaluation/lib/tasks-chunk.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/lib/tasks-chunk.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/lib/tasks-chunk.xql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,24 @@ + + + + + + + select eg.grade_name, eg.grade_plural_name + from evaluation_grades eg, cr_items cri + where eg.grade_item_id = :grade_item_id + and cri.live_revision = eg.grade_id + + + + + + select ets.solution_id + from evaluation_tasks_sols ets, cr_items cri + where ets.task_item_id = :task_item_id + and cri.live_revision = ets.solution_id + + + + + Index: openacs-4/packages/evaluation/sql/oracle/evaluation-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/oracle/evaluation-create.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/evaluation/sql/oracle/evaluation-create.sql 24 Jan 2005 15:13:23 -0000 1.3 +++ openacs-4/packages/evaluation/sql/oracle/evaluation-create.sql 31 Mar 2005 11:09:27 -0000 1.4 @@ -10,13 +10,13 @@ references cr_items(item_id), grade_name varchar(100), grade_plural_name varchar(100), - comments varchar(3000), -- percentage of this grade type in the class - weight number(5,2) constraint evaluation_grades_w_ck + weight number(9,4) constraint evaluation_grades_w_ck check (weight between 0 and 100) ); -create index ev_grades_giid_index on evaluation_grades(grade_item_id); +create index eva_grades_gid_index on evaluation_grades(grade_item_id); + create table evaluation_tasks ( task_id integer constraint evaluation_tasks_pk primary key @@ -34,7 +34,7 @@ grade_item_id integer constraint evaluation_tasks_gid_fk references cr_items(item_id), -- percentage of the grade of the course - weight number(5,2), + weight number(9,4), -- the task will be submitted on line online_p char(1) constraint evaluation_tasks_onp_ck check(online_p in ('t','f')), @@ -45,8 +45,8 @@ check(requires_grade_p in ('t','f')) ); -create index ev_tasks_gid_index on evaluation_tasks(grade_item_id); -create index ev_tasks_tiid_index on evaluation_tasks(task_item_id); +create index eva_tasks_gid_index on evaluation_tasks(grade_item_id); +create index eva_tasks_tiid_index on evaluation_tasks(task_item_id); create table evaluation_tasks_sols ( @@ -59,7 +59,7 @@ ); -- create indexes -create index ev_tasks_sols_tid_index on evaluation_tasks_sols(task_item_id); +create index eva_tasks_sols_tid_index on evaluation_tasks_sols(task_item_id); create table evaluation_answers ( @@ -76,7 +76,7 @@ references cr_items(item_id) ); -create index ev_answers_tid_index on evaluation_answers(party_id,task_item_id); +create index eva_answers_tid_index on evaluation_answers(party_id,task_item_id); create table evaluation_student_evals ( @@ -95,14 +95,14 @@ not null constraint evaluation_stu_evals_pid_fk references parties(party_id), - grade number(5,2), + grade number(9,4), show_student_p char(1) default 't' constraint evaluation_stu_evals_ssp_ck check (show_student_p in ('t','f')) ); -create index ev_student_evals_tid_index on evaluation_student_evals(task_item_id); -create index ev_student_evals_pid_index on evaluation_student_evals(party_id); +create index eva_student_evals_tid_index on evaluation_student_evals(task_item_id); +create index eva_student_evals_pid_index on evaluation_student_evals(party_id); -- table to store the csv sheet grades associated with the evaluations create table evaluation_grades_sheets @@ -115,7 +115,7 @@ ); -- create indexes -create index ev_grades_sheets_tid_index on evaluation_grades_sheets(task_item_id); +create index eva_grades_sheets_tid_index on evaluation_grades_sheets(task_item_id); begin acs_object_type.create_type( @@ -144,7 +144,7 @@ references cr_items(item_id) ); -create index ev_task_groups_tid_index on evaluation_task_groups(task_item_id); +create index eva_task_groups_tid_index on evaluation_task_groups(task_item_id); insert into group_types (group_type) values ('evaluation_task_groups'); @@ -172,263 +172,6 @@ end; / --- Definicion del Paquete -create or replace package evaluation -as - function grade_name - ( - grade_id in evaluation_grades.grade_id%TYPE - )return varchar; - function task_name - ( - task_id in evaluation_tasks.task_id%TYPE - )return varchar; - function party_name - ( - p_party_id in evaluation_tasks.task_id%TYPE, - p_task_id in evaluation_tasks.task_id%TYPE - )return varchar; - function party_id - ( - p_user_id in evaluation_tasks.task_id%TYPE, - p_task_id in evaluation_tasks.task_id%TYPE - )return parties.party_id%TYPE; - function answer_info - ( - p_user_id in evaluation_tasks.task_id%TYPE, - p_task_id in evaluation_tasks.task_id%TYPE, - p_task_item_id in evaluation_tasks.task_id%TYPE - )return integer; - function class_total_grade - ( - p_user_id in acs_objects.context_id%TYPE, - p_package_id acs_objects.context_id%TYPE - )return integer; - function new_evaluation_task_group - ( - p_task_group_id integer, - p_task_group_name varchar, - p_join_policy varchar, - p_creation_date date, - p_creation_user integer, - p_creation_ip varchar, - p_context_id integer, - p_task_item_id integer - )return integer; - function delete_evaluation_task_group - ( - p_task_group_id integer - )return integer; -end evaluation; -/ -show errors - --- Cuerpo del Paquete -create or replace package body evaluation -as - function grade_name - ( - grade_id in evaluation_grades.grade_id%TYPE - )return varchar - is - v_grade_name evaluation_grades.grade_name%TYPE; - begin - select grade_name into v_grade_name - from evaluation_grades where grade_id = grade_name.grade_id; - return v_grade_name; -end grade_name; - -function task_name - ( - task_id in evaluation_tasks.task_id%TYPE - )return varchar - is - v_task_name evaluation_tasks.task_name%TYPE; - begin - select task_name into v_task_name - from evaluation_tasks where task_id = task_name.task_id; - return v_task_name; -end task_name; - - function party_name - ( - p_party_id in evaluation_tasks.task_id%TYPE, - p_task_id in evaluation_tasks.task_id%TYPE - )return varchar - is - v_number_of_members evaluation_tasks.number_of_members%TYPE; - begin - select number_of_members into v_number_of_members - from evaluation_tasks - where task_id = party_name.p_task_id; if v_number_of_members = 1 then - return person.last_name(p_party_id)||', '||person.first_names(p_party_id); - else - return acs_group.name(p_party_id); - end if; - end party_name; - - function party_id - ( - p_user_id in evaluation_tasks.task_id%TYPE, - p_task_id in evaluation_tasks.task_id%TYPE - )return parties.party_id%TYPE - is - v_number_of_members evaluation_tasks.number_of_members%TYPE; - midato evaluation_tasks.task_id%TYPE; - begin - select number_of_members into v_number_of_members - from evaluation_tasks - where task_id = party_id.p_task_id; - if v_number_of_members = 1 then - return party_id.p_user_id; - else - select nvl(etg.group_id,0) into midato from evaluation_task_groups etg, - evaluation_tasks et, - acs_rels map - where map.object_id_one = etg.group_id - and map.object_id_two = party_id.p_user_id - and etg.task_item_id = et.task_item_id - and et.task_id = party_id.p_task_id; - return midato; - end if; - end party_id; - function answer_info - ( - p_user_id in evaluation_tasks.task_id%TYPE, - p_task_id in evaluation_tasks.task_id%TYPE, - p_task_item_id in evaluation_tasks.task_id%TYPE - )return integer - is - answer_id evaluation_student_evals.grade%TYPE; - begin - select ea.answer_id into answer_id - from evaluation_answers ea, cr_items cri - where ea.task_item_id = answer_info.p_task_item_id - and cri.live_revision = ea.answer_id - and ea.party_id = - ( select - CASE - WHEN et3.number_of_members = 1 THEN answer_info.p_user_id - ELSE - (select etg2.group_id from evaluation_task_groups etg2, - evaluation_tasks et2, - acs_rels map - where map.object_id_one = etg2.group_id - and map.object_id_two = answer_info.p_user_id - and etg2.task_item_id = et2.task_item_id - and et2.task_id = answer_info.p_task_id) - END as nom - from evaluation_tasks et3 - where et3.task_id = answer_info.p_task_id - ); - return answer_id; - end answer_info; - - function class_total_grade - ( - p_user_id in acs_objects.context_id%TYPE, - p_package_id acs_objects.context_id%TYPE - )return integer - is - v_grade evaluation_student_evals.grade%TYPE; - begin - v_grade := 0; - FOR row in (select nvl(sum(round((ese.grade*et.weight*eg.weight)/10000,2)),0) as grade - from evaluation_grades eg, evaluation_tasks et, evaluation_student_evals ese, acs_objects ao, cr_items cri1, cr_items cri2, cr_items cri3 - where et.task_item_id = ese.task_item_id and et.grade_item_id = eg.grade_item_id and eg.grade_item_id = ao.object_id and ao.context_id = class_total_grade.p_package_id and ese.party_id = - ( select - CASE - WHEN et3.number_of_members = 1 THEN class_total_grade.p_user_id - ELSE - (select etg2.group_id from evaluation_task_groups etg2, - evaluation_tasks et2, - acs_rels map - where map.object_id_one = etg2.group_id - and map.object_id_two = class_total_grade.p_user_id - and etg2.task_item_id = et2.task_item_id - and et2.task_id = et.task_id) - END as nom - from evaluation_tasks et3 - where et3.task_id = et.task_id - ) - and cri1.live_revision = eg.grade_id - and cri2.live_revision = et.task_id - and cri3.live_revision = ese.evaluation_id) - LOOP - v_grade := v_grade + row.grade; - END LOOP; - return v_grade; -end class_total_grade; -function new_evaluation_task_group - ( - p_task_group_id integer, - p_task_group_name varchar, - p_join_policy varchar, - p_creation_date date, - p_creation_user integer, - p_creation_ip varchar, - p_context_id integer, - p_task_item_id integer - )return integer - is - v_group_id integer; - begin - v_group_id := acs_group.new ( - p_task_group_id, - 'evaluation_task_groups', - p_creation_date, - p_creation_user, - p_creation_ip, - null, - null, - p_task_group_name, - p_join_policy, - p_context_id - ); insert into evaluation_task_groups - (group_id, - task_item_id) - values - (v_group_id, - p_task_item_id); - return v_group_id; -end new_evaluation_task_group; - function delete_evaluation_task_group - ( - p_task_group_id integer - )return integer - is - total integer; - begin - total := 0; - FOR row in (select evaluation_id from evaluation_student_evals where party_id = p_task_group_id) - LOOP - content_revision.del(revision_id => row.evaluation_id); - END LOOP; - - FOR row in (select answer_id from evaluation_answers where party_id = p_task_group_id) - LOOP - content_revision.del(revision_id => row.answer_id); - END LOOP; - - FOR row in (select rel_id from acs_rels where object_id_one = p_task_group_id) - LOOP - acs_rel.del(rel_id => row.rel_id); - END LOOP; - - delete from evaluation_task_groups - where group_id = p_task_group_id; - - delete from groups where group_id = p_task_group_id; - - delete from parties where party_id = p_task_group_id; - - acs_group.del(p_task_group_id); - return total; - end delete_evaluation_task_group; -end evaluation; -/ -show errors - create table evaluation_user_profile_rels ( rel_id integer constraint ev_user_profile_rels_pk @@ -457,6 +200,9 @@ ); end; / +show errors; +@ evaluation-package-create.sql + @ evaluation-calendar-create.sql Index: openacs-4/packages/evaluation/sql/oracle/evaluation-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/oracle/evaluation-drop.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/evaluation/sql/oracle/evaluation-drop.sql 28 Dec 2004 19:50:36 -0000 1.1 +++ openacs-4/packages/evaluation/sql/oracle/evaluation-drop.sql 31 Mar 2005 11:09:27 -0000 1.2 @@ -1,7 +1,7 @@ -- jopez@galileo.edu -- cesarhj@galileo.edu -@evaluation-calendar-drop.sql +@ evaluation-calendar-drop.sql --create function inline_0 () --returns integer as' @@ -46,17 +46,7 @@ begin acs_object_type.drop_type('evaluation_task_group'); -end; -/ -show errors - -begin acs_rel_type.drop_type('evaluation_task_group_rel'); -end; -/ -show errors - -begin acs_object_type.drop_type('evaluation_grades'); acs_object_type.drop_type('evaluation_tasks'); acs_object_type.drop_type('evaluation_tasks_sols'); @@ -66,8 +56,7 @@ acs_object_type.drop_type('evaluation_task_groups'); end; / -show errors - +show errors; drop index ev_tasks_sols_tid_index; drop view evaluation_tasks_solsi; drop view evaluation_tasks_solsx; @@ -113,10 +102,7 @@ -- Originalmente drop function grade__name(integer); -drop package body evaluation; -drop package evaluation; - --------------------------------------- -- TASKS --------------------------------------- @@ -184,14 +170,16 @@ delete from acs_rels where rel_type = 'evaluation_task_group_rel'; end; / -show error +show errors; + drop table evaluation_user_profile_rels; + begin acs_rel_type.drop_type('evaluation_task_group_rel'); acs_object_type.drop_type('evaluation_task_group_rel'); end; / -show error +show errors; --------------------------------------- -- GRADE FUNCIONS @@ -228,4 +216,5 @@ -- Usa Funciones CR --drop function evaluation__delete_all_folders_and_contents (); +@ evaluation-package-drop.sql Index: openacs-4/packages/evaluation/sql/oracle/evaluation-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/oracle/evaluation-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/sql/oracle/evaluation-package-create.sql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,257 @@ +--jopez@galileo.edu +--cesarhj@galileo.edu + +---------------------- +-- Package definition +---------------------- + +create or replace package evaluation +as + function grade_name ( + grade_id in evaluation_grades.grade_id%TYPE + ) return varchar; + function task_name ( + task_id in evaluation_tasks.task_id%TYPE + ) return varchar; + function party_name ( + p_party_id in parties.party_id%TYPE, + p_task_id in evaluation_tasks.task_id%TYPE + ) return varchar; + function party_id ( + p_user_id in users.user_id%TYPE, + p_task_id in evaluation_tasks.task_id%TYPE + ) return integer; + function answer_info ( + p_user_id in users.user_id%TYPE, + p_task_id in evaluation_tasks.task_id%TYPE, + p_task_item_id in evaluation_tasks.task_item_id%TYPE + ) return integer; + function class_total_grade ( + p_user_id in users.user_id%TYPE, + p_package_id acs_objects.context_id%TYPE + ) return integer; + function new_evaluation_task_group ( + p_task_group_id integer, + p_task_group_name varchar, + p_join_policy varchar, + p_creation_date date, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer, + p_task_item_id integer + ) return integer; + function delete_evaluation_task_group ( + p_task_group_id groups.group_id%TYPE + ) return integer; +end evaluation; +/ +show errors; + +------------------ +-- Package Body +------------------ + +create or replace package body evaluation +as + +function grade_name ( + grade_id in evaluation_grades.grade_id%TYPE + ) return varchar + is + v_grade_name evaluation_grades.grade_name%TYPE; + begin + select grade_name into v_grade_name + from evaluation_grades where grade_id = grade_name.grade_id; + return v_grade_name; +end grade_name; + +function task_name + ( + task_id in evaluation_tasks.task_id%TYPE + ) return varchar + is + v_task_name evaluation_tasks.task_name%TYPE; + begin + select task_name into v_task_name + from evaluation_tasks where task_id = task_name.task_id; + return v_task_name; +end task_name; + +function party_name ( + p_party_id in parties.party_id%TYPE, + p_task_id in evaluation_tasks.task_id%TYPE + ) return varchar + is + v_number_of_members evaluation_tasks.number_of_members%TYPE; + begin + select number_of_members into v_number_of_members + from evaluation_tasks + where task_id = party_name.p_task_id; if v_number_of_members = 1 then + return person.last_name(p_party_id)||', '||person.first_names(p_party_id); + else + return acs_group.name(p_party_id); + end if; + end party_name; + +function party_id ( + p_user_id in users.user_id%TYPE, + p_task_id in evaluation_tasks.task_id%TYPE + ) return integer + is + v_number_of_members evaluation_tasks.number_of_members%TYPE; + v_temp parties.party_id%TYPE; + begin + select number_of_members into v_number_of_members + from evaluation_tasks + where task_id = party_id.p_task_id; + if v_number_of_members = 1 then + return party_id.p_user_id; + else + select nvl(etg.group_id,0) into v_temp from evaluation_task_groups etg, + evaluation_tasks et, + acs_rels map + where map.object_id_one = etg.group_id + and map.object_id_two = party_id.p_user_id + and etg.task_item_id = et.task_item_id + and et.task_id = party_id.p_task_id; + return v_temp; + end if; + end party_id; + +function answer_info ( + p_user_id in users.user_id%TYPE, + p_task_id in evaluation_tasks.task_id%TYPE, + p_task_item_id in evaluation_tasks.task_item_id%TYPE + ) return integer + is + v_answer_id evaluation_student_evals.grade%TYPE; + begin + select ea.answer_id into v_answer_id + from evaluation_answers ea, cr_items cri + where ea.task_item_id = answer_info.p_task_item_id + and cri.live_revision = ea.answer_id + and ea.party_id = + ( select + CASE + WHEN et3.number_of_members = 1 THEN answer_info.p_user_id + ELSE + (select etg2.group_id from evaluation_task_groups etg2, + evaluation_tasks et2, + acs_rels map + where map.object_id_one = etg2.group_id + and map.object_id_two = answer_info.p_user_id + and etg2.task_item_id = et2.task_item_id + and et2.task_id = answer_info.p_task_id) + END as nom + from evaluation_tasks et3 + where et3.task_id = answer_info.p_task_id + ); + return v_answer_id; + end answer_info; + +function class_total_grade ( + p_user_id in users.user_id%TYPE, + p_package_id in acs_objects.context_id%TYPE + ) return integer + is + v_grade evaluation_student_evals.grade%TYPE; + begin + v_grade := 0; + FOR row in (select nvl(sum(round((ese.grade*et.weight*eg.weight)/10000,2)),0) as grade + from evaluation_grades eg, evaluation_tasks et, evaluation_student_evals ese, acs_objects ao, cr_items cri1, cr_items cri2, cr_items cri3 + where et.task_item_id = ese.task_item_id and et.grade_item_id = eg.grade_item_id and eg.grade_item_id = ao.object_id and ao.context_id = class_total_grade.p_package_id and ese.party_id = + ( select + CASE + WHEN et3.number_of_members = 1 THEN class_total_grade.p_user_id + ELSE + (select etg2.group_id from evaluation_task_groups etg2, + evaluation_tasks et2, + acs_rels map + where map.object_id_one = etg2.group_id + and map.object_id_two = class_total_grade.p_user_id + and etg2.task_item_id = et2.task_item_id + and et2.task_id = et.task_id) + END as nom + from evaluation_tasks et3 + where et3.task_id = et.task_id + ) + and cri1.live_revision = eg.grade_id + and cri2.live_revision = et.task_id + and cri3.live_revision = ese.evaluation_id) + LOOP + v_grade := v_grade + row.grade; + END LOOP; + return v_grade; +end class_total_grade; + +function new_evaluation_task_group ( + p_task_group_id integer, + p_task_group_name varchar, + p_join_policy varchar, + p_creation_date date, + p_creation_user integer, + p_creation_ip varchar, + p_context_id integer, + p_task_item_id integer + ) return integer + is + v_group_id integer; + begin + v_group_id := acs_group.new ( + p_task_group_id, + 'evaluation_task_groups', + p_creation_date, + p_creation_user, + p_creation_ip, + null, + null, + p_task_group_name, + p_join_policy, + p_context_id + ); + + insert into evaluation_task_groups + (group_id, + task_item_id) + values + (v_group_id, + p_task_item_id); + return v_group_id; +end new_evaluation_task_group; + +function delete_evaluation_task_group ( + p_task_group_id groups.group_id%TYPE + ) return integer + is + total integer; + begin + total := 0; + FOR row in (select evaluation_id from evaluation_student_evals where party_id = p_task_group_id) + LOOP + content_revision.del(revision_id => row.evaluation_id); + END LOOP; + + FOR row in (select answer_id from evaluation_answers where party_id = p_task_group_id) + LOOP + content_revision.del(revision_id => row.answer_id); + END LOOP; + + FOR row in (select rel_id from acs_rels where object_id_one = p_task_group_id) + LOOP + acs_rel.del(rel_id => row.rel_id); + END LOOP; + + delete from evaluation_task_groups + where group_id = p_task_group_id; + + delete from groups where group_id = p_task_group_id; + delete from parties where party_id = p_task_group_id; + + acs_group.del(p_task_group_id); + return total; + end delete_evaluation_task_group; + +end evaluation; +/ +show errors; + Index: openacs-4/packages/evaluation/sql/oracle/evaluation-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/oracle/evaluation-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/sql/oracle/evaluation-package-drop.sql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,5 @@ +-- jopez@inv.it.uc3m.es + +drop package body evaluation; +drop package evaluation; + Index: openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql,v diff -u -r1.21 -r1.22 --- openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql 24 Feb 2005 13:33:17 -0000 1.21 +++ openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql 31 Mar 2005 11:09:27 -0000 1.22 @@ -11,14 +11,14 @@ references cr_items(item_id), grade_name varchar(100), grade_plural_name varchar(100), - comments text, -- percentage of this grade type in the class weight numeric constraint evaluation_grades_w_ck check (weight between 0 and 100) ); create index evalutaion_grades_giid_index on evaluation_grades(grade_item_id); + create table evaluation_tasks ( task_id integer constraint evaluation_tasks_pk @@ -585,5 +585,6 @@ END LOOP; return 0; end;' language 'plpgsql'; - + +\i evaluation-package-create.sql \i evaluation-calendar-create.sql Index: openacs-4/packages/evaluation/sql/postgresql/evaluation-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/postgresql/evaluation-drop.sql,v diff -u -r1.12 -r1.13 --- openacs-4/packages/evaluation/sql/postgresql/evaluation-drop.sql 6 Jan 2005 16:20:10 -0000 1.12 +++ openacs-4/packages/evaluation/sql/postgresql/evaluation-drop.sql 31 Mar 2005 11:09:27 -0000 1.13 @@ -1,5 +1,7 @@ -- jopez@galileo.edu + \i evaluation-calendar-drop.sql + create function inline_0 () returns integer as' declare @@ -92,22 +94,6 @@ drop view evaluation_gradesx; drop table evaluation_grades; ---------------------------------------- --- GRADES ---------------------------------------- - -drop function grade__name(integer); - ---------------------------------------- --- TASKS ---------------------------------------- - -drop function task__name(integer); - -drop function evaluation__new_evaluation_task_group(integer,varchar,varchar,timestamptz,integer,varchar,integer,integer); - -drop function evaluation__delete_evaluation_task_group(integer); - delete from acs_rels where rel_type = 'evaluation_task_group_rel'; drop table evaluation_user_profile_rels; @@ -116,24 +102,4 @@ select acs_object_type__drop_type('evaluation_task_group_rel','f'); ---------------------------------------- --- GRADE FUNCIONS ---------------------------------------- - -drop function evaluation__task_grade (integer, integer); - -drop function evaluation__grade_total_grade (integer, integer); - -drop function evaluation__class_total_grade (integer, integer); - ---------------------------------------- --- OTHER FUNCIONS ---------------------------------------- - -drop function evaluation__party_name (integer,integer); - -drop function evaluation__party_id (integer,integer); - -drop function evaluation__delete_contents (integer); - - +\i evaluation-package-drop.sql \ No newline at end of file Index: openacs-4/packages/evaluation/sql/postgresql/evaluation-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/postgresql/evaluation-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/sql/postgresql/evaluation-package-create.sql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,395 @@ +-- jopez@inv.it.uc3m.es + +--------------------------------------- +-- GRADES +--------------------------------------- +create function grade__name(integer) +returns varchar as ' +declare + p_grade_id alias for $1; + v_grade_name evaluation_grades.grade_name%TYPE; +begin + select grade_name into v_grade_name + from evaluation_grades + where grade_id = p_grade_id; + + return v_grade_name; +end; +' language 'plpgsql'; + +--------------------------------------- +-- TASKS +--------------------------------------- + +create function evaluation__new_task (integer, integer, varchar, integer, integer, varchar, numeric, timestamptz, char, char, char, decimal, varchar, timestamptz, integer, varchar, varchar, timestamptz, varchar, varchar) +returns integer as ' +declare + p_item_id alias for $1; + p_revision_id alias for $2; + p_task_name alias for $3; + p_number_of_members alias for $4; + p_grade_item_id alias for $5; + p_description alias for $6; + p_weight alias for $7; + p_due_date alias for $8; + p_late_submit_p alias for $9; + p_online_p alias for $10; + p_requires_grade_p alias for $11; + p_estimated_time alias for $12; + p_object_type alias for $13; + p_creation_date alias for $14; + p_creation_user alias for $15; + p_creation_ip alias for $16; + p_title alias for $17; -- default null + p_publish_date alias for $18; + p_nls_language alias for $19; -- default null + p_mime_type alias for $20; -- default null + + v_revision_id integer; + +begin + + v_revision_id := content_revision__new( + p_title, -- title + p_description, -- description + p_publish_date, -- publish_date + p_mime_type, -- mime_type + p_nls_language, -- nls_language + null, -- data + p_item_id, -- item_id + p_revision_id, -- revision_id + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip, -- creation_ip + null -- content length + ); + + insert into evaluation_tasks + (task_id, + task_item_id, + task_name, + number_of_members, + due_date, + grade_item_id, + weight, + online_p, + late_submit_p, + requires_grade_p) + values + (v_revision_id, + p_item_id, + p_task_name, + p_number_of_members, + p_due_date, + p_grade_item_id, + p_weight, + p_online_p, + p_late_submit_p, + p_requires_grade_p); + + return v_revision_id; +end; +' language 'plpgsql'; + +create function evaluation__delete_task (integer) +returns integer as ' +declare + p_task_item_id alias for $1; + del_rec record; +begin + + + PERFORM evaluation__delete_student_eval(evaluation_id) from evaluation_student_evals where task_item_id = p_task_item_id; + PERFORM evaluation__delete_answer(answer_id) from evaluation_answers where task_item_id = p_task_item_id; + PERFORM evaluation__delete_task_sol(solution_id) from evaluation_tasks_sols where task_item_id = p_task_item_id; + PERFORM evaluation__delete_grades_sheet(grades_sheet_id) from evaluation_grades_sheets where task_item_id = p_task_item_id; + + delete from evaluation_tasks where task_id = p_task_item_id; + + PERFORM content_revision__delete(task_id) from evaluation_tasks where task_item_id = p_task_item_id; + + return 0; + +end;' language 'plpgsql'; + +create function task__name(integer) +returns varchar as ' +declare + p_task_id alias for $1; + v_task_name evaluation_tasks.task_name%TYPE; +begin + select task_name into v_task_name + from evaluation_tasks + where task_id = p_task_id; + + return v_task_name; +end; +' language 'plpgsql'; + +--------------------------------------- +-- EVALUATION TASK GROUPS +--------------------------------------- +create function evaluation__new_evaluation_task_group(integer,varchar,varchar,timestamptz,integer,varchar,integer,integer) +returns integer as ' +declare + p_task_group_id alias for $1; + p_task_group_name alias for $2; + p_join_policy alias for $3; + p_creation_date alias for $4; + p_creation_user alias for $5; + p_creation_ip alias for $6; + p_context_id alias for $7; + p_task_item_id alias for $8; + + v_group_id integer; + +begin + + v_group_id := acs_group__new ( + p_task_group_id, + ''evaluation_task_groups'', + p_creation_date, + p_creation_user, + p_creation_ip, + null, + null, + p_task_group_name, + p_join_policy, + p_context_id + ); + + insert into evaluation_task_groups + (group_id, + task_item_id) + values + (v_group_id, + p_task_item_id); + + return v_group_id; +end; +' language 'plpgsql'; + + +create function evaluation__delete_evaluation_task_group(integer) +returns integer as ' +declare + p_task_group_id alias for $1; + del_rec record; +begin + + for del_rec in select evaluation_id from evaluation_student_evals where party_id = p_task_group_id + loop + PERFORM content_revision__delete(del_rec.evaluation_id); + end loop; + + for del_rec in select answer_id from evaluation_answers where party_id = p_task_group_id + loop + PERFORM content_revision__delete(del_rec.answer_id); + end loop; + + for del_rec in select rel_id from acs_rels where object_id_one = p_task_group_id + loop + PERFORM acs_rel__delete(del_rec.rel_id); + end loop; + + delete from evaluation_task_groups + where group_id = p_task_group_id; + + delete from groups where group_id = p_task_group_id; + + delete from parties where party_id = p_task_group_id; + + PERFORM acs_group__delete(p_task_group_id); + + return 0; +end; +' language 'plpgsql'; + +create table evaluation_user_profile_rels ( + rel_id integer + constraint evaluation_user_profile_rels_pk + primary key +); + +select acs_rel_type__create_type( + 'evaluation_task_group_rel', + 'Evaluation Task Group Member', + 'Evaluation Task Group Members', + 'membership_rel', + 'evaluation_user_profile_rels', + 'rel_id', + 'evaluations', + 'evaluation_task_groups', + null, + 0, + null, + 'user', + null, + 0, + 1 + ); + +--------------------------------------- +-- GRADE FUNCTIONS +--------------------------------------- + +create function evaluation__task_grade (integer, integer) +returns numeric as ' +declare + + p_user_id alias for $1; + p_task_id alias for $2; + + v_grade evaluation_student_evals.grade%TYPE; + +begin + + select (ese.grade*et.weight*eg.weight)/10000 into v_grade + from evaluation_student_evals ese, evaluation_tasks et, evaluation_grades eg + where party_id = evaluation__party_id(p_user_id, et.task_id) + and et.task_id = p_task_id + and ese.task_item_id = et.task_item_id + and et.grade_item_id = eg.grade_item_id + and content_revision__is_live(eg.grade_id) = true + and content_revision__is_live(et.task_id) = true; + + if v_grade is null then + return 0.00; + else + return v_grade; + end if; +end;' language 'plpgsql'; + +create function evaluation__grade_total_grade (integer, integer) +returns numeric as ' +declare + + p_user_id alias for $1; + p_grade_id alias for $2; + + v_grade evaluation_student_evals.grade%TYPE; + v_grades_cursor RECORD; + +begin + + v_grade := 0; + FOR v_grades_cursor IN + select (ese.grade*et.weight*eg.weight)/10000 as grade + from evaluation_grades eg, evaluation_tasks et, evaluation_student_evalsi ese + where et.task_item_id = ese.task_item_id + and et.grade_item_id = eg.grade_item_id + and eg.grade_id = p_grade_id + and ese.party_id = evaluation__party_id(p_user_id,et.task_id) + and content_revision__is_live(ese.evaluation_id) = true + and content_revision__is_live(et.task_id) = true + LOOP + v_grade := v_grade + v_grades_cursor.grade; + END LOOP; + + return v_grade; +end;' language 'plpgsql'; + +create function evaluation__class_total_grade (integer, integer) +returns numeric as ' +declare + + p_user_id alias for $1; + p_package_id alias for $2; + + v_grade evaluation_student_evals.grade%TYPE; + v_grades_cursor RECORD; + +begin + + v_grade := 0; + FOR v_grades_cursor IN + select (ese.grade*et.weight*eg.weight)/10000 as grade + from evaluation_gradesx eg, evaluation_tasks et, evaluation_student_evalsi ese, acs_objects ao + where et.task_item_id = ese.task_item_id + and et.grade_item_id = eg.grade_item_id + and eg.item_id = ao.object_id + and ao.context_id = p_package_id + and ese.party_id = evaluation__party_id(p_user_id,et.task_id) + and content_revision__is_live(ese.evaluation_id) = true + and content_revision__is_live(eg.grade_id) = true + and content_revision__is_live(et.task_id) = true + LOOP + v_grade := v_grade + v_grades_cursor.grade; + END LOOP; + + return v_grade; +end;' language 'plpgsql'; + +--------------------------------------- +-- OTHER FUNCTIONS +--------------------------------------- + +create function evaluation__party_name (integer,integer) +returns varchar as ' +declare + p_party_id alias for $1; + p_task_id alias for $2; + + v_number_of_members evaluation_tasks.number_of_members%TYPE; +begin + + select number_of_members into v_number_of_members + from evaluation_tasks + where task_id = p_task_id; + + if v_number_of_members = 1 then + return person__last_name(p_party_id)||'', ''||person__first_names(p_party_id); + else + return acs_group__name(p_party_id); + end if; + +end;' language 'plpgsql'; + +create function evaluation__party_id (integer,integer) +returns varchar as ' +declare + p_user_id alias for $1; + p_task_id alias for $2; + + v_number_of_members evaluation_tasks.number_of_members%TYPE; +begin + + select number_of_members into v_number_of_members + from evaluation_tasks + where task_id = p_task_id; + + if v_number_of_members = 1 then + return p_user_id; + else + return coalesce((select etg.group_id from evaluation_task_groups etg, + evaluation_tasks et, + acs_rels map + where map.object_id_one = etg.group_id + and map.object_id_two = p_user_id + and etg.task_item_id = et.task_item_id + and et.task_id = p_task_id),0); + end if; + +end;' language 'plpgsql'; + +create function evaluation__delete_contents (integer) +returns integer as ' +declare + + p_package_id alias for $1; + + v_item_id cr_items.item_id%TYPE; + v_item_cursor RECORD; + +begin + FOR v_item_cursor IN + select etg.group_id + from evaluation_tasksi et, acs_objects ao, evaluation_task_groups etg + where et.item_id = ao.object_id + and etg.task_item_id = et.task_item_id + and ao.context_id = p_package_id + LOOP + PERFORM evaluation__delete_evaluation_task_group(v_item_cursor.group_id); + END LOOP; +return 0; +end;' language 'plpgsql'; Index: openacs-4/packages/evaluation/sql/postgresql/evaluation-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/postgresql/evaluation-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/sql/postgresql/evaluation-package-drop.sql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,39 @@ +-- jopez@inv.it.uc3m.es + +--------------------------------------- +-- GRADES +--------------------------------------- + +drop function grade__name(integer); + +--------------------------------------- +-- TASKS +--------------------------------------- + +drop function task__name(integer); + +drop function evaluation__new_evaluation_task_group(integer,varchar,varchar,timestamptz,integer,varchar,integer,integer); + +drop function evaluation__delete_evaluation_task_group(integer); + +--------------------------------------- +-- GRADE FUNCIONS +--------------------------------------- + +drop function evaluation__task_grade (integer, integer); + +drop function evaluation__grade_total_grade (integer, integer); + +drop function evaluation__class_total_grade (integer, integer); + +--------------------------------------- +-- OTHER FUNCIONS +--------------------------------------- + +drop function evaluation__party_name (integer,integer); + +drop function evaluation__party_id (integer,integer); + +drop function evaluation__delete_contents (integer); + + Index: openacs-4/packages/evaluation/tcl/apm-callback-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/tcl/apm-callback-procs.tcl,v diff -u -r1.19 -r1.20 --- openacs-4/packages/evaluation/tcl/apm-callback-procs.tcl 21 Mar 2005 15:42:00 -0000 1.19 +++ openacs-4/packages/evaluation/tcl/apm-callback-procs.tcl 31 Mar 2005 11:09:27 -0000 1.20 @@ -48,7 +48,7 @@ content::type::new -content_type evaluation_student_evals -supertype content_revision -pretty_name "Student Evaluation" -pretty_plural "Student Evaluations" -table_name evaluation_student_evals -id_column evaluation_id content::type::new -content_type evaluation_grades_sheets -supertype content_revision -pretty_name "Evaluation Grades Sheet" -pretty_plural "Evaluation Grades Sheets" -table_name evaluation_grades_sheets -id_column grades_sheet_id - #Create and register the templates + #Create and register templates set template_id [content::template::new -name evaluation-tasks-default -text "@text;noquote@" -is_live t] content::type::register_template -content_type evaluation_tasks -template_id $template_id -use_context public -is_default t set template_id [content::template::new -name evaluation-tasks-sols-default -text "@text;noquote@" -is_live t] Index: openacs-4/packages/evaluation/tcl/evaluation-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/tcl/evaluation-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/evaluation/tcl/evaluation-procs-oracle.xql 31 Mar 2005 11:09:27 -0000 1.1 @@ -0,0 +1,159 @@ + + + + oracle8.1.6 + + + + begin + select content_item.set_live_revision ( + revision_id => :revision_id + ); + end; + + + + + + begin + select evaluation__new_evaluation_task_group ( + :group_id, + :group_name, + 'closed', + :creation_date, + :creation_user, + :creation_ip, + :context, + :task_item_id + ); + end; + + + + + + begin + select acs_group.name(:group_id) as group_name; + end; + + + + + + select cu.person_id as party_id, cu.last_name||' - '||cu.first_names as party_name, + round(ese.grade,2) as grade, + ese.description as comments + from cc_users cu left outer join evaluation_student_evalsi ese on (ese.party_id = cu.person_id + and ese.task_item_id = :task_item_id + and content_revision.is_live(ese.evaluation_id) = 't') + + + + + + select p.person_id as party_id, p.last_name||' - '||p.first_names as party_name, + ese.grade, + ese.description as comments + from registered_users ru, + dotlrn_member_rels_approved app, + persons p left outer join evaluation_student_evalsi ese on (ese.party_id = p.person_id + and ese.task_item_id = :task_item_id + and content_revision.is_live(ese.evaluation_id) = 't') + where app.community_id = :community_id + and app.user_id = ru.user_id + and app.user_id = p.person_id + and app.role = 'student' + + + + + + select etg.group_id as party_id, + g.group_name as party_name, + grade, + ese.description as comments + from groups g, + evaluation_task_groups etg left outer join evaluation_student_evalsi ese on (ese.party_id = etg.group_id + and ese.task_item_id = :task_item_id + and content_revision.is_live(ese.evaluation_id) = 't') + where etg.task_item_id = :task_item_id + and etg.group_id = g.group_id + + + + + + select description as edit_reason, + grade as current_grade, + evaluation.party_name(party_id,:task_id) as party_name + from evaluation_student_evalsi + where evaluation_id = :evaluation_id + + + + + + select evaluation.party_name(ea.party_id, et.task_id) as party_name, + crr.title as answer_title, + crr.revision_id, + crr.content as cr_file_name, + cri.storage_type, + cri.storage_area_key as cr_path + from evaluation_answersi ea, + cr_revisions crr, + evaluation_tasks et, + cr_items cri, + cr_items cri2 + where ea.task_item_id = et.task_item_id + and ea.answer_item_id = cri.item_id + and crr.revision_id = ea.answer_id + and et.task_id = :task_id + and ea.data is not null + and cri2.live_revision = ea.answer_id + and not exists (select 1 from evaluation_student_evals ese, cr_items cri3 where ese.party_id = ea.party_id and ese.task_item_id = et.task_item_id and cri3.live_revision = ese.evaluation_id) + + + + + + select content_revision.get_content(:revision_id) + + + + + + select sysdate from dual + + + + + + select sysdate from dual + + + + + + select sysdate from dual + + + + + + select sysdate from dual + + + + + + select sysdate from dual + + + + + + select sysdate from dual + + + + Index: openacs-4/packages/evaluation/tcl/evaluation-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/tcl/evaluation-procs-postgresql.xql,v diff -u -r1.20 -r1.21 --- openacs-4/packages/evaluation/tcl/evaluation-procs-postgresql.xql 28 Feb 2005 09:27:06 -0000 1.20 +++ openacs-4/packages/evaluation/tcl/evaluation-procs-postgresql.xql 31 Mar 2005 11:09:28 -0000 1.21 @@ -2,6 +2,7 @@ postgresql7.3 + @@ -12,177 +13,6 @@ - - - - select et.task_name, - et.number_of_members, - et.due_date, - et.weight, - et.online_p, - et.late_submit_p, - et.requires_grade_p, - crr.lob, crr.content, - crr.content_length, - crr.title, - crr.description, - crr.mime_type, - cri.storage_type - from evaluation_tasksi et, - cr_revisions crr, - cr_items cri - where task_id = :from_task_id - and et.task_id = crr.revision_id - and cri.item_id = crr.item_id - - - - - - - - select evaluation__new_item ( - :item_id, --item_id - :item_name, - null, --locale - :creation_user, - :to_package_id, - current_timestamp, - :creation_ip, - :task_name, - :description, - :mime_type, --mime_type - null, --nls_language - null, --text - :storage_type, --storage_type - 'content_item', -- item_subtype - 'evaluation_tasks' -- content_type - ); - - - - - - - - select evaluation__new_task ( - :item_id, - :revision_id, - :task_name, - :number_of_members, - :to_grade_item_id, - :description, - :weight, - :due_date, - :late_submit_p, - :online_p, - :requires_grade_p, - 'evaluation_tasks', - now(), --creation date - :creation_user, - :creation_ip, - :title, - now(), --publish date - null, -- nls_language - :mime_type --mime_type - ) - - - - - - - - update cr_revisions - set content = :content, - content_length = :content_length, - lob = :lob - where revision_id = :revision_id - - - - - - - - update cr_items - set name = :item_name, - storage_type = :storage_type - where item_id = :item_id - - - - - - - - update cr_items - set name = :item_name, - storage_type = :storage_type - where item_id = :item_id - - - - - - - - update cr_items - set name = :item_name, - storage_type = :storage_type - where item_id = :item_id - - - - - - - - select evaluation__new_item ( - :item_id, --item_id - :item_name, - null, --locale - :creation_user, - :package_id, - current_timestamp, - :creation_ip, - :title, - 'student evaluation', - :mime_type, --mime_type - null, --nls_language - null, --text - :storage_type, --storage_type - 'content_item', -- item_subtype - 'evaluation_student_evals' -- content_type - ); - - - - - - - - select evaluation__new_student_eval ( - :item_id, - :revision_id, - :task_item_id, - :party_id, - :grade, - :show_student_p, - :description, - 'evaluation_student_evals', - :creation_date, --creation date - :creation_user, - :creation_ip, - :item_name, --title - :publish_date, --publish date - null, -- nls_language - :mime_type --mime_type - ); - - - - @@ -208,18 +38,6 @@ - - - - select eg.grade_id - from evaluation_tasks est, evaluation_grades eg, cr_items cri - where est.task_id = :task_id - and est.grade_item_id = eg.grade_item_id - and cri.live_revision = eg.grade_id - - - - @@ -252,16 +70,6 @@ - - - - select et.task_name, et.number_of_members, et.task_item_id - from evaluation_tasks et - where et.task_id = :task_id - - - - @@ -279,29 +87,6 @@ - - - - $sql_query - - - - - - - - select eg.grade_name, - et.task_name - from evaluation_grades eg, - evaluation_tasks et, - cr_items cri - where et.task_id = :task_id - and et.grade_item_id = eg.grade_item_id - and cri.live_revision = eg.grade_id - - - - @@ -314,129 +99,6 @@ - - - - select evaluation__new_item ( - :projects_item_id, --item_id - :projects_item_name, - null, - :creation_user, - :package_id, - current_timestamp, - :creation_ip, - :projects_name, - :projects_desc, - 'text/plain', - null, - null, - 'text', - 'content_item', -- item_subtype - 'evaluation_grades' -- content_type - ); - - - - - - - - select evaluation__new_grade ( - :projects_item_id, - :projects_revision_id, - :projects_singular_name, - :projects_name, - 20, - 'evaluation_grades', - now(), --creation date - :creation_user, - :creation_ip, - :projects_revision_name, - :projects_desc, - now(), --publish date - null, --nls_language - 'text/plain' --mime_type - ); - - - - - - - - select content_item__set_live_revision ( - :projects_revision_id - ); - - - - - - - - select evaluation__new_item ( - :tasks_item_id, --item_id - :tasks_item_name, - null, - :creation_user, - :package_id, - current_timestamp, - :creation_ip, - :tasks_name, - :tasks_desc, - 'text/plain', - null, - null, - 'text', - 'content_item', -- item_subtype - 'evaluation_grades' -- content_type - ); - - - - - - - - select evaluation__new_grade ( - :tasks_item_id, - :tasks_revision_id, - :tasks_singular_name, - :tasks_name, - 40, - 'evaluation_grades', - now(), --creation date - :creation_user, - :creation_ip, - :tasks_revision_name, - :tasks_desc, - now(), --publish date - null, --nls_language - 'text/plain' --mime_type - ); - - - - - - - select content_item__set_live_revision ( - :tasks_revision_id - ); - - - - - - - - select evaluation__delete_contents ( - :package_id - ); - - - - @@ -470,69 +132,51 @@ - - - - select lob - from cr_revisions - where revision_id = :revision_id - - - - - + select now() - - - - - - - select now() - - + select now() - + - + select now() - + - + select now() - + - + select now() - + - + select now() - + Index: openacs-4/packages/evaluation/tcl/evaluation-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/tcl/evaluation-procs.tcl,v diff -u -r1.27 -r1.28 --- openacs-4/packages/evaluation/tcl/evaluation-procs.tcl 21 Mar 2005 15:42:00 -0000 1.27 +++ openacs-4/packages/evaluation/tcl/evaluation-procs.tcl 31 Mar 2005 11:09:28 -0000 1.28 @@ -301,6 +301,8 @@ -from_task_id:required -to_grade_item_id:required -to_package_id:required + {-creation_user ""} + {-creation_ip ""} } { Cone a task @@ -311,17 +313,45 @@ db_1row from_task_info { *SQL* } - set creation_user [ad_conn user_id] - set creation_ip [ad_conn peeraddr] + if { [empty_string_p $creation_user] } { + set creation_user [ad_conn user_id] + } + if { [empty_string_p $creation_ip] } { + set creation_ip [ad_conn peeraddr] + } set item_name "${item_id}_${title}" set revision_id [db_nextval acs_object_id_seq] - db_exec_plsql content_item_new { *SQL* } + set item_id [content::item::new -item_id $item_id \ + -parent_id $folder_id \ + -content_type evaluation_tasks \ + -creation_user $creation_user \ + -name $item_name \ + -context_id $to_package_id \ + -creation_ip $creation_ip \ + -mime_type $mime_type \ + -storage_type $storage_type] + + set revision_id [content::revision::new \ + -item_id $item_id \ + -content_type evaluation_tasks \ + -mime_type $mime_type \ + -title $title \ + -description $description \ + -creation_user $creation_user \ + -creation_ip $creation_ip \ + -attributes [list [list weight $weight] \ + [list task_name $task_name] \ + [list task_item_id $item_id] \ + [list online_p $online_p] \ + [list grade_item_id $to_grade_item_id] \ + [list due_date $due_date] \ + [list late_submit_p $late_submit_p] \ + [list requires_grade_p $requires_grade_p] \ + [list number_of_members $number_of_members]]] - db_exec_plsql content_revision_new { *SQL* } - db_dml clone_content { *SQL* } return $revision_id } @@ -337,6 +367,8 @@ -number_of_members:required -requires_grade_p:required -storage_type:required + {-creation_user ""} + {-creation_ip ""} {-estimated_time 0} {-online_p ""} {-due_date ""} @@ -364,15 +396,19 @@ @description Description of the task @storage_type File or text, depending on what are we going to store } { + if { [empty_string_p $creation_user] } { + set creation_user [ad_conn user_id] + } + if { [empty_string_p $creation_ip] } { + set creation_ip [ad_conn peeraddr] + } + set package_id [ad_conn package_id] - set creation_user [ad_conn user_id] - set creation_ip [ad_conn peeraddr] - set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index {f}] - set creation_date [db_string get_date { *SQL* }] + set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index f] if { [empty_string_p $item_name] } { set item_name "${item_id}_${title}" } - #Falta agregarle el title + if { $new_item_p } { set item_id [content::item::new -item_id $item_id \ @@ -381,7 +417,6 @@ -name $item_name \ -context_id $package_id \ -mime_type $mime_type \ - -creation_date $creation_date \ -storage_type $storage_type] } @@ -391,7 +426,6 @@ -mime_type $mime_type \ -title $title \ -description $description \ - -creation_date $creation_date \ -attributes [list [list weight $weight] \ [list task_name $name] \ [list task_item_id $item_id] \ @@ -417,6 +451,8 @@ -task_item_id:required -storage_type:required -title:required + {-creation_user ""} + {-creation_ip ""} {-mime_type "text/plain"} {-publish_date ""} {-creation_date ""} @@ -436,10 +472,15 @@ } { + if { [empty_string_p $creation_user] } { + set creation_user [ad_conn user_id] + } + if { [empty_string_p $creation_ip] } { + set creation_ip [ad_conn peeraddr] + } + set package_id [ad_conn package_id] - set creation_user [ad_conn user_id] - set creation_ip [ad_conn peeraddr] - set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index {f}] + set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index f] set item_name "${item_id}_${title}" set revision_id [db_nextval acs_object_id_seq] @@ -451,14 +492,27 @@ if { [empty_string_p $creation_date] } { set creation_date [db_string get_date { *SQL* }] } + if { $new_item_p } { - set item_id [content::item::new -item_id $item_id -parent_id $folder_id -content_type $content_type -name $item_name -context_id $package_id -mime_type $mime_type -storage_type $storage_type -title $title -creation_date $creation_date] + set item_id [content::item::new -item_id $item_id \ + -parent_id $folder_id \ + -content_type $content_type \ + -name $item_name \ + -context_id $package_id \ + -mime_type $mime_type \ + -storage_type $storage_type \ + -creation_user $creation_user \ + -creation_ip $creation_ip \ + -creation_date $creation_date] } + set revision_id [content::revision::new \ -item_id $item_id \ -content_type $content_type \ -mime_type $mime_type \ -title $title \ + -creation_user $creation_user \ + -creation_ip $creation_ip \ -creation_date $creation_date \ -attributes [list [list task_item_id $task_item_id] \ [list solution_item_id $item_id]] ] @@ -479,6 +533,8 @@ -storage_type:required -title:required -party_id:required + {-creation_user ""} + {-creation_ip ""} {-mime_type "text/plain"} {-publish_date ""} {-creation_date ""} @@ -498,11 +554,15 @@ @param party_id Group or user_id thaw owns the anser } { + if { [empty_string_p $creation_user] } { + set creation_user [ad_conn user_id] + } + if { [empty_string_p $creation_ip] } { + set creation_ip [ad_conn peeraddr] + } + set package_id [ad_conn package_id] - set creation_user [ad_conn user_id] - set creation_ip [ad_conn peeraddr] - set creation_date [db_string get_date { *SQL* }] - set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index {f}] + set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index f] set item_name "${item_id}_${title}" set revision_id [db_nextval acs_object_id_seq] @@ -522,6 +582,8 @@ -name $item_name \ -context_id $package_id \ -mime_type $mime_type \ + -creation_user $creation_user \ + -creation_ip $creation_ip \ -storage_type $storage_type \ -creation_date $creation_date] } @@ -530,13 +592,13 @@ -content_type $content_type \ -mime_type $mime_type \ -title $title\ + -creation_user $creation_user \ + -creation_ip $creation_ip \ -creation_date $creation_date \ -attributes [list [list answer_item_id $item_id] \ [list party_id $party_id] \ [list task_item_id $task_item_id]] ] - - # in order to find the file we have to set the name in cr_items the same that in cr_revisions db_dml update_item_name { *SQL* } return $revision_id @@ -551,6 +613,8 @@ -party_id:required -task_item_id:required -grade:required + {-creation_user ""} + {-creation_ip ""} {-title "evaluation"} {-show_student_p "t"} {-storage_type "text"} @@ -577,10 +641,14 @@ @param mime_type Mime type of the evaluation. } { + if { [empty_string_p $creation_user] } { + set creation_user [ad_conn user_id] + } + if { [empty_string_p $creation_ip] } { + set creation_ip [ad_conn peeraddr] + } + set package_id [ad_conn package_id] - set creation_user [ad_conn user_id] - set creation_date [db_string get_date { *SQL* }] - set creation_ip [ad_conn peeraddr] set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index {f}] set item_name "${item_id}_${title}" @@ -595,13 +663,24 @@ } if { $new_item_p } { - set item_id [content::item::new -item_id $item_id -parent_id $folder_id -content_type $content_type -name $item_name -context_id $package_id -mime_type $mime_type -storage_type $storage_type -creation_date $creation_date] + set item_id [content::item::new -item_id $item_id \ + -parent_id $folder_id \ + -content_type $content_type \ + -name $item_name \ + -context_id $package_id \ + -mime_type $mime_type \ + -storage_type $storage_type \ + -creation_user $creation_user \ + -creation_ip $creation_ip \ + -creation_date $creation_date] } set revision_id [content::revision::new \ -item_id $item_id \ -content_type $content_type \ -mime_type $mime_type \ -title $title\ + -creation_user $creation_user \ + -creation_ip $creation_ip \ -creation_date $creation_date \ -attributes [list [list evaluation_item_id $item_id] \ [list party_id $party_id] \ @@ -610,10 +689,13 @@ [list task_item_id $task_item_id]]] } + ad_proc -public evaluation::new_evaluation_group { -group_id:required -group_name:required -task_item_id:required + {-creation_user ""} + {-creation_ip ""} {-context ""} {-creation_date ""} } { @@ -630,11 +712,15 @@ } { + if { [empty_string_p $creation_user] } { + set creation_user [ad_conn user_id] + } + if { [empty_string_p $creation_ip] } { + set creation_ip [ad_conn peeraddr] + } if { [empty_string_p $context] } { set context [ad_conn package_id] } - set creation_user [ad_conn user_id] - set creation_ip [ad_conn peeraddr] if { [empty_string_p $creation_date] } { set creation_date [db_string get_date { *SQL* }] @@ -667,6 +753,8 @@ -storage_type:required -title:required -mime_type:required + {-creation_user ""} + {-creation_ip ""} {-creation_date ""} {-publish_date ""} } { @@ -686,10 +774,14 @@ } { + if { [empty_string_p $creation_user] } { + set creation_user [ad_conn user_id] + } + if { [empty_string_p $creation_ip] } { + set creation_ip [ad_conn peeraddr] + } set package_id [ad_conn package_id] - set creation_user [ad_conn user_id] - set creation_ip [ad_conn peeraddr] - set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index {f}] + set folder_id [content::item::get_id -item_path "${content_type}_${package_id}" -resolve_index f] set item_name "${item_id}_${title}" set revision_id [db_nextval acs_object_id_seq] @@ -703,14 +795,24 @@ } if { $new_item_p } { - set item_id [content::item::new -item_id $item_id -parent_id $folder_id -content_type $content_type -name $item_name -context_id $package_id -mime_type $mime_type -title $title -storage_type $storage_type] + set item_id [content::item::new -item_id $item_id \ + -parent_id $folder_id \ + -content_type $content_type \ + -name $item_name \ + -context_id $package_id \ + -mime_type $mime_type \ + -creation_user $creation_user \ + -creation_ip $creation_ip \ + -storage_type $storage_type] } set revision_id [content::revision::new \ -item_id $item_id \ -content_type $content_type \ -title $title \ -mime_type $mime_type \ + -creation_user $creation_user \ + -creation_ip $creation_ip \ -attributes [list [list grades_sheet_item_id $item_id] \ [list task_item_id $task_item_id]] ] return $revision_id Index: openacs-4/packages/evaluation/tcl/evaluation-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/tcl/evaluation-procs.xql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/evaluation/tcl/evaluation-procs.xql 15 Jun 2004 23:20:29 -0000 1.1 +++ openacs-4/packages/evaluation/tcl/evaluation-procs.xql 31 Mar 2005 11:09:28 -0000 1.2 @@ -13,5 +13,131 @@ + + + + + select et.task_name, + et.number_of_members, + et.due_date, + et.weight, + et.online_p, + et.late_submit_p, + et.requires_grade_p, + crr.lob, crr.content, + crr.content_length, + crr.title, + crr.description, + crr.mime_type, + cri.storage_type + from evaluation_tasksi et, + cr_revisions crr, + cr_items cri + where task_id = :from_task_id + and et.task_id = crr.revision_id + and cri.item_id = crr.item_id + + + + + + + + update cr_revisions + set content = :content, + content_length = :content_length, + lob = :lob + where revision_id = :revision_id + + + + + + + + update cr_items + set name = :item_name, + storage_type = :storage_type + where item_id = :item_id + + + + + + + + update cr_items + set name = :item_name, + storage_type = :storage_type + where item_id = :item_id + + + + + + + + update cr_items + set name = :item_name, + storage_type = :storage_type + where item_id = :item_id + + + + + + + + select eg.grade_id + from evaluation_tasks est, evaluation_grades eg, cr_items cri + where est.task_id = :task_id + and est.grade_item_id = eg.grade_item_id + and cri.live_revision = eg.grade_id + + + + + + + + select et.task_name, et.number_of_members, et.task_item_id + from evaluation_tasks et + where et.task_id = :task_id + + + + + + + + $sql_query + + + + + + + + select eg.grade_name, + et.task_name + from evaluation_grades eg, + evaluation_tasks et, + cr_items cri + where et.task_id = :task_id + and et.grade_item_id = eg.grade_item_id + and cri.live_revision = eg.grade_id + + + + + + + + select lob + from cr_revisions + where revision_id = :revision_id + + +