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 -N -r1.16.2.2 -r1.16.2.3 --- openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql 19 Jan 2005 19:17:17 -0000 1.16.2.2 +++ openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql 8 Jun 2005 22:07:45 -0000 1.16.2.3 @@ -16,6 +16,7 @@ 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); @@ -66,7 +67,12 @@ constraint evaluations_tasks_rgp_ck check(late_submit_p in ('t','f')), -- estimated time to complete the assigment - estimated_time decimal + estimated_time decimal, + points numeric, + perfect_score numeric, + relative_weight numeric, + forums_related_p char(1) constraint evaluation_tasks_frp_ck + check(forums_related_p in ('t','f')) ); create index evalutaion_tasks_gid_index on evaluation_tasks(grade_item_id); @@ -119,9 +125,10 @@ not null constraint evaluation_sans_pid_fk references parties(party_id), - task_item_id integer + task_item_id integer constraint evaluation_sans_tid_fk - references cr_items(item_id) + references cr_items(item_id), + comment text ); create index evaluation_answers_tid_index on evaluation_answers(party_id,task_item_id); @@ -323,7 +330,7 @@ create function evaluation__new_grade (integer, integer, varchar, varchar, numeric, varchar, timestamptz, integer, varchar, varchar, varchar, timestamptz, varchar, varchar) returns integer as ' declare - p_item_id alias for $1; + p_item_id alias for $1; p_revision_id alias for $2; p_grade_name alias for $3; p_grade_plural_name alias for $4; @@ -601,7 +608,7 @@ --------------------------------------- -create function evaluation__new_answer (integer, integer, integer, integer, varchar, timestamptz, integer, varchar, varchar, timestamptz, varchar, varchar) +create function evaluation__new_answer (integer, integer, integer, integer, varchar, timestamptz, integer, varchar, varchar, timestamptz, varchar, varchar,text) returns integer as ' declare p_item_id alias for $1; @@ -616,6 +623,7 @@ p_publish_date alias for $10; p_nls_language alias for $11; -- default null p_mime_type alias for $12; -- default null + p_comment alias for $13; v_revision_id integer; @@ -640,12 +648,12 @@ (answer_id, answer_item_id, task_item_id, - party_id) + party_id,comment) values (v_revision_id, p_item_id, p_task_item_id, - p_party_id); + p_party_id,p_comment); return v_revision_id; end; @@ -1502,4 +1510,135 @@ select inline_0(); drop function inline_0(); +create function evaluation__clone (integer,integer) +returns integer as ' +declare + p_new_package_id alias for $1; --default null, + p_old_package_id alias for $2; --default null + v_grade_id evaluation_grades.grade_id%TYPE; + v_item_id acs_objects.object_id%TYPE; + v_revision_id acs_objects.object_id%TYPE; + v_task_item_id acs_objects.object_id%TYPE; + v_task_revision_id acs_objects.object_id%TYPE; + previous integer default 0; + one_grade record; + entry record; + +begin + -- get all the grades belonging to the old package, + -- and create new grades for the new package + delete from evaluation_grades where grade_id in (select eg.grade_id from acs_objects o, evaluation_grades eg,cr_items ci,cr_revisions cr where o.object_id = ci.item_id and cr.revision_id=eg.grade_id and ci.item_id=cr.item_id and cr.revision_id=ci.live_revision and o.context_id = p_new_package_id); + + for one_grade in select * + from acs_objects o, evaluation_grades eg,cr_items ci,cr_revisions cr + where o.object_id = ci.item_id + and cr.revision_id=eg.grade_id + and ci.item_id=cr.item_id + and cr.revision_id=ci.live_revision + and o.context_id = p_old_package_id + + loop + + v_item_id := evaluation__new_item (null, + one_grade.name, + null, + one_grade.creation_user, + p_new_package_id, + one_grade.creation_date, + one_grade.creation_ip, + one_grade.title, + one_grade.description, + one_grade.mime_type, + null, + null, + ''text'', + ''content_item'', + ''evaluation_grades'' + ); + + v_revision_id := nextval(''t_acs_object_id_seq''); + v_grade_id := evaluation__new_grade ( v_item_id, + v_revision_id, + one_grade.grade_name, + one_grade.grade_plural_name, + one_grade.weight, + ''evaluation_grades'', + one_grade.creation_date, + one_grade.creation_user, + one_grade.creation_ip, + one_grade.title, + one_grade.description, + one_grade.publish_date, + null, + ''text/plain'' + ); + + for entry in select *, (ci.live_revision = cr.revision_id) as live_p from evaluation_tasks et,cr_revisions cr,cr_items ci, acs_objects o where grade_item_id = one_grade.grade_item_id and cr.revision_id=task_id and cr.item_id=ci.item_id and object_id=ci.item_id order by task_item_id + + loop + if previous != entry.task_item_id then + + v_task_item_id := evaluation__new_item ( + null, + entry.name, + null, + entry.creation_user, + p_new_package_id, + now(), + entry.creation_ip, + entry.task_name, + entry.description, + entry.mime_type, + null, + null, + entry.storage_type, --storage_type + ''content_item'', -- item_subtype + ''evaluation_tasks'' -- content_type + ); + + end if; + + v_task_revision_id := nextval(''t_acs_object_id_seq''); + perform evaluation__new_task ( v_task_item_id, + v_task_revision_id, + entry.task_name, + entry.number_of_members, + v_item_id, + entry.description, + entry.weight, + entry.due_date, + entry.late_submit_p, + entry.online_p, + entry.requires_grade_p, + entry.estimated_time, + entry.object_type, + entry.creation_date, + entry.creation_user, + entry.creation_ip, + entry.title, + entry.publish_date, + entry.nls_language, + entry.mime_type + ); + + update evaluation_tasks set points=entry.points, relative_weight=entry.relative_weight, perfect_score= entry.perfect_score,forums_related_p=entry.forums_related_p where task_id=v_task_revision_id; + + if entry.live_p then + perform content_item__set_live_revision ( + v_task_revision_id + ); + end if; + + previous := entry.task_item_id; + + end loop; + perform content_item__set_live_revision ( + v_revision_id + ); + + end loop; + return 0; + end; +' language 'plpgsql'; + \i evaluation-calendar-create.sql