Index: openacs-4/packages/tasks/sql/postgresql/tasks-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/tasks/sql/postgresql/tasks-create.sql,v diff -u -N -r1.6 -r1.7 --- openacs-4/packages/tasks/sql/postgresql/tasks-create.sql 26 Oct 2005 18:54:29 -0000 1.6 +++ openacs-4/packages/tasks/sql/postgresql/tasks-create.sql 1 Mar 2006 13:49:15 -0000 1.7 @@ -18,6 +18,8 @@ workflow_id integer constraint t_process_workflow_fk references workflows + -- the user assigned to this process + assignee_id integer ); create table t_process_instances ( @@ -30,9 +32,6 @@ constraint t_process_instances_process_fk references t_processes, case_id integer, - party_id integer - constraint t_process_instances_party_fk - references parties, object_id integer constraint t_process_instances_tasks_object_fk references acs_objects @@ -76,9 +75,6 @@ closing_action_id integer constraint t_process_tasks_close_action_fk references workflow_actions, - party_id integer - constraint t_process_tasks_party_fk - references parties, object_id integer constraint t_process_tasks_object_fk references acs_objects, @@ -110,16 +106,7 @@ process_task_id integer constraint t_tasks_process_task_fk references t_process_tasks, - -- The party_id is the party whom this task is associated with (e.g. the contact) - party_id integer - constraint t_tasks_party_fk - references parties, - -- The object_id of the Object that triggered this task. If you created an offer - -- and want to have a reminder to phone, the party_id would be the recipient of the offer - -- the object_id would be the offer_id and the creation_user (assignee who is doing the job) - -- would be yourself. - -- As we are very bad in design we realized too late that it might make sense to change the - -- assignee at a later stage, we now modify the creation_user for this. + -- the object_id this tasks is applied to (such as a person, organization, page, etc.) object_id integer constraint t_tasks_object_fk references acs_objects, @@ -135,6 +122,7 @@ start_date timestamptz, due_date timestamptz, completed_date timestamptz, + -- the user assigned to this task assignee_id integer ); @@ -233,111 +221,33 @@ ELSE to_char(p_date,''Mon DD (Dy)'') END ELSE to_char(p_date,''Mon DD, YYYY'') END; - return v_date; end;' language 'plpgsql'; -create or replace function tasks__completion_date ( - integer -) returns timestamptz -as ' -declare - p_task_id alias for $1; - v_complete_p boolean; - v_date varchar; - v_previous_p boolean; - revision record; -begin - v_complete_p := ''1'' from pm_tasks where task_id = p_task_id and status = ''2''; - v_date := NULL; - - IF v_complete_p THEN - v_previous_p := ''t''; - FOR revision IN - select ptr.percent_complete, ao.creation_date - from cr_revisions cr, pm_tasks_revisions ptr, acs_objects ao - where cr.item_id = p_task_id - and cr.revision_id = ao.object_id - and cr.revision_id = ptr.task_revision_id - order by ao.creation_date desc - LOOP - IF revision.percent_complete = ''100'' AND v_previous_p THEN - v_date := revision.creation_date; - ELSE - v_previous_p := ''f''; - EXIT; - END IF; - END LOOP; - - END IF; - - return v_date; -end;' language 'plpgsql'; - - -create or replace function tasks__completion_user ( - integer -) returns integer -as ' -declare - p_task_id alias for $1; - v_complete_p boolean; - v_user varchar; - v_previous_p boolean; - revision record; -begin - v_complete_p := ''1'' from pm_tasks where task_id = p_task_id and status = ''2''; - v_user := NULL; - - IF v_complete_p THEN - v_previous_p := ''t''; - FOR revision IN - select ptr.percent_complete, ao.creation_user - from cr_revisions cr, pm_tasks_revisions ptr, acs_objects ao - where cr.item_id = p_task_id - and cr.revision_id = ao.object_id - and cr.revision_id = ptr.task_revision_id - order by ao.creation_date desc - LOOP - IF revision.percent_complete = ''100'' AND v_previous_p THEN - v_user := revision.creation_user; - ELSE - v_previous_p := ''f''; - EXIT; - END IF; - END LOOP; - - END IF; - - return v_user; -end;' language 'plpgsql'; - - ----------------------------- -select define_function_args('tasks_task__new','task_id,process_instance_id,process_task_id,party_id,object_id,title,description,mime_type,comment,status_id,priority,start_date,due_date,package_id,creation_user,creation_ip,context_id,assignee_id'); +select define_function_args('tasks_task__new','task_id,process_instance_id,process_task_id,object_id,title,description,mime_type,comment,status_id,priority,start_date,due_date,package_id,creation_user,creation_ip,context_id,assignee_id'); -create or replace function tasks_task__new (integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,timestamptz,timestamptz,integer,integer,varchar,integer,integer) +create or replace function tasks_task__new (integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,timestamptz,timestamptz,integer,integer,varchar,integer,integer) returns integer as ' declare p_task_id alias for $1; p_process_instance_id alias for $2; p_process_task_id alias for $3; - p_party_id alias for $4; - p_object_id alias for $5; - p_title alias for $6; - p_description alias for $7; - p_mime_type alias for $8; - p_comment alias for $9; - p_status_id alias for $10; - p_priority alias for $11; - p_start_date alias for $12; - p_due_date alias for $13; - p_package_id alias for $14; - p_creation_user alias for $15; - p_creation_ip alias for $16; - p_context_id alias for $17; - p_assignee_id alias for $18; + p_object_id alias for $4; + p_title alias for $5; + p_description alias for $6; + p_mime_type alias for $7; + p_comment alias for $8; + p_status_id alias for $9; + p_priority alias for $10; + p_start_date alias for $11; + p_due_date alias for $12; + p_package_id alias for $13; + p_creation_user alias for $14; + p_creation_ip alias for $15; + p_context_id alias for $16; + p_assignee_id alias for $17; v_task_id integer; v_start_date timestamptz; begin @@ -360,11 +270,11 @@ end if; insert into t_tasks - (task_id, process_instance_id, process_task_id, party_id, object_id, + (task_id, process_instance_id, process_task_id, object_id, title, description, mime_type, comment, status_id, priority, start_date, due_date, assignee_id) values - (v_task_id, p_process_instance_id, p_process_task_id, p_party_id, + (v_task_id, p_process_instance_id, p_process_task_id, p_object_id, p_title, p_description, p_mime_type, p_comment, p_status_id, p_priority, v_start_date, p_due_date, p_assignee_id); @@ -373,30 +283,29 @@ ' language 'plpgsql'; -select define_function_args('tasks_process_task__new','task_id,process_id,open_action_id,closing_action_id,party_id,object_id,title,description,mime_type,comment,status_id,priority,start,due,package_id,creation_user,creation_ip,context_id,assignee_id'); +select define_function_args('tasks_process_task__new','task_id,process_id,open_action_id,closing_action_id,object_id,title,description,mime_type,comment,status_id,priority,start,due,package_id,creation_user,creation_ip,context_id,assignee_id'); -create or replace function tasks_process_task__new (integer,integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,numeric,numeric,integer,integer,varchar,integer,integer) +create or replace function tasks_process_task__new (integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,numeric,numeric,integer,integer,varchar,integer,integer) returns integer as ' declare p_task_id alias for $1; p_process_id alias for $2; p_open_action_id alias for $3; p_closing_action_id alias for $4; - p_party_id alias for $5; - p_object_id alias for $6; - p_title alias for $7; - p_description alias for $8; - p_mime_type alias for $9; - p_comment alias for $10; - p_status_id alias for $11; - p_priority alias for $12; - p_start alias for $13; - p_due alias for $14; - p_package_id alias for $15; - p_creation_user alias for $16; - p_creation_ip alias for $17; - p_context_id alias for $18; - p_assignee_id alias for $19; + p_object_id alias for $5; + p_title alias for $6; + p_description alias for $7; + p_mime_type alias for $8; + p_comment alias for $9; + p_status_id alias for $10; + p_priority alias for $11; + p_start alias for $12; + p_due alias for $13; + p_package_id alias for $14; + p_creation_user alias for $15; + p_creation_ip alias for $16; + p_context_id alias for $17; + p_assignee_id alias for $18; v_task_id integer; begin v_task_id:= acs_object__new( @@ -412,33 +321,34 @@ ); insert into t_process_tasks - (task_id, process_id, open_action_id, closing_action_id, party_id, + (task_id, process_id, open_action_id, closing_action_id, object_id, title, description, mime_type, comment, status_id, priority, start, due, assignee_id) values (v_task_id, p_process_id, p_open_action_id, p_closing_action_id, - p_party_id, p_object_id, p_title, p_description, p_mime_type, + p_object_id, p_title, p_description, p_mime_type, p_comment, p_status_id, p_priority, p_start, p_due, p_assignee_id); return v_task_id; end; ' language 'plpgsql'; -select define_function_args('tasks_process__new','process_id,title,description,mime_type,workflow_id,package_id,creation_user,creation_ip,context_id'); +select define_function_args('tasks_process__new','process_id,title,description,mime_type,workflow_id,assignee_id,package_id,creation_user,creation_ip,context_id'); -create or replace function tasks_process__new (integer,varchar,text,varchar,integer,integer,integer,varchar,integer) +create or replace function tasks_process__new (integer,varchar,text,varchar,integer,integer,integer,integer,varchar,integer) returns integer as ' declare p_process_id alias for $1; p_title alias for $2; p_description alias for $3; p_mime_type alias for $4; p_workflow_id alias for $5; - p_package_id alias for $6; - p_creation_user alias for $7; - p_creation_ip alias for $8; - p_context_id alias for $9; + p_assignee_id alias for $6; + p_package_id alias for $7; + p_creation_user alias for $8; + p_creation_ip alias for $9; + p_context_id alias for $10; v_process_id integer; begin v_process_id:= acs_object__new( @@ -454,29 +364,28 @@ ); insert into t_processes - (process_id, title, description, mime_type, workflow_id) + (process_id, title, description, mime_type, workflow_id, assignee_id) values - (v_process_id, p_title, p_description, p_mime_type, p_workflow_id); + (v_process_id, p_title, p_description, p_mime_type, p_workflow_id, p_assignee_id); return v_process_id; end; ' language 'plpgsql'; -select define_function_args('tasks_process_instance__new','process_instance_id,process_id,case_id,party_id,object_id,package_id,creation_user,creation_ip,context_id'); +select define_function_args('tasks_process_instance__new','process_instance_id,process_id,case_id,object_id,package_id,creation_user,creation_ip,context_id'); -create or replace function tasks_process_instance__new (integer,integer,integer,integer,integer,integer,integer,varchar,integer) +create or replace function tasks_process_instance__new (integer,integer,integer,integer,integer,integer,varchar,integer) returns integer as ' declare p_process_instance_id alias for $1; p_process_id alias for $2; p_case_id alias for $3; - p_party_id alias for $4; - p_object_id alias for $5; - p_package_id alias for $6; - p_creation_user alias for $7; - p_creation_ip alias for $8; - p_context_id alias for $9; + p_object_id alias for $4; + p_package_id alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + p_context_id alias for $8; v_process_instance_id integer; begin v_process_instance_id:= acs_object__new( @@ -492,9 +401,9 @@ ); insert into t_process_instances - (process_instance_id, process_id, case_id, party_id, object_id) + (process_instance_id, process_id, case_id, object_id) values - (v_process_instance_id, p_process_id, p_case_id, p_party_id, p_object_id); + (v_process_instance_id, p_process_id, p_case_id, p_object_id); return v_process_instance_id; end;