Index: openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/acs-workflow/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql 26 Feb 2003 02:25:35 -0000 1.1.2.1 @@ -0,0 +1,243 @@ +-- DRB: None of this code has changed. We need to redefine these items because +-- the party_approved_member_map is now a table rather than view + +drop view wf_user_tasks; +create view wf_user_tasks as +select distinct ta.task_id, + ta.case_id, + ta.workflow_key, + ta.transition_key, + tr.transition_name, + ta.enabled_date, + ta.started_date, + u.user_id, + ta.state, + ta.holding_user, + ta.hold_timeout, + ta.deadline, + ta.estimated_minutes +from wf_tasks ta, + wf_task_assignments tasgn, + wf_cases c, + wf_transition_info tr, + party_approved_member_map m, + users u +where ta.state in ( 'enabled','started') +and c.case_id = ta.case_id +and c.state = 'active' +and tr.transition_key = ta.transition_key +and tr.workflow_key = ta.workflow_key +and tr.trigger_type = 'user' +and tr.context_key = c.context_key +and tasgn.task_id = ta.task_id +and m.party_id = tasgn.party_id +and u.user_id = m.member_id; + +-- procedure add_task_assignment +create or replace function workflow_case__add_task_assignment (integer,integer,boolean) +returns integer as ' +declare + add_task_assignment__task_id alias for $1; + add_task_assignment__party_id alias for $2; + add_task_assignment__permanent_p alias for $3; + v_count integer; + v_workflow_key wf_workflows.workflow_key%TYPE; + v_context_key wf_contexts.context_key%TYPE; + v_case_id wf_cases.case_id%TYPE; + v_role_key wf_roles.role_key%TYPE; + v_transition_key wf_transitions.transition_key%TYPE; + v_notification_callback wf_context_transition_info.notification_callback%TYPE; + v_notification_custom_arg wf_context_transition_info.notification_custom_arg%TYPE; + callback_rec record; + v_assigned_user record; +begin + -- get some needed information + + select ta.case_id, ta.workflow_key, ta.transition_key, tr.role_key, c.context_key + into v_case_id, v_workflow_key, v_transition_key, v_role_key, v_context_key + from wf_tasks ta, wf_transitions tr, wf_cases c + where ta.task_id = add_task_assignment__task_id + and tr.workflow_key = ta.workflow_key + and tr.transition_key = ta.transition_key + and c.case_id = ta.case_id; + + -- make the same assignment as a manual assignment + + if add_task_assignment__permanent_p = ''t'' then + /* We do this up-front, because + * even though the user already had a task assignment, + * he might not have a case assignment. + */ + perform workflow_case__add_manual_assignment ( + v_case_id, + v_role_key, + add_task_assignment__party_id + ); + end if; + + -- check that we do not hit the unique constraint + + select count(*) into v_count + from wf_task_assignments + where task_id = add_task_assignment__task_id + and party_id = add_task_assignment__party_id; + + if v_count > 0 then + return null; + end if; + + -- get callback information + + select notification_callback, + notification_custom_arg into callback_rec + from wf_context_transition_info + where context_key = v_context_key + and workflow_key = v_workflow_key + and transition_key = v_transition_key; + + + if FOUND then + v_notification_callback := callback_rec.notification_callback; + v_notification_custom_arg := callback_rec.notification_custom_arg; + else + v_notification_callback := null; + v_notification_custom_arg := null; + end if; + + -- notify any new assignees + + for v_assigned_user in + select distinct u.user_id + from users u + where u.user_id not in ( + select distinct u2.user_id + from wf_task_assignments tasgn2, + party_approved_member_map m2, + users u2 + where tasgn2.task_id = add_task_assignment__task_id + and m2.party_id = tasgn2.party_id + and u2.user_id = m2.member_id) + and exists ( + select 1 + from party_approved_member_map m + where m.member_id = u.user_id + and m.party_id = add_task_assignment__party_id + ) + LOOP + PERFORM workflow_case__notify_assignee ( + add_task_assignment__task_id, + v_assigned_user.user_id, + v_notification_callback, + v_notification_custom_arg + ); + end loop; + + -- do the insert + + insert into wf_task_assignments ( + task_id, + party_id + ) values ( + add_task_assignment__task_id, + add_task_assignment__party_id + ); + + return 0; +end;' language 'plpgsql'; + +-- function begin_task_action +create or replace function workflow_case__begin_task_action (integer,varchar,varchar,integer,varchar) +returns integer as ' +declare + begin_task_action__task_id alias for $1; + begin_task_action__action alias for $2; + begin_task_action__action_ip alias for $3; + begin_task_action__user_id alias for $4; + begin_task_action__msg alias for $5; -- default null + v_state varchar; + v_journal_id integer; + v_case_id integer; + v_transition_name varchar; + v_num_rows integer; +begin + select state into v_state + from wf_tasks + where task_id = begin_task_action__task_id; + + if begin_task_action__action = ''start'' then + if v_state != ''enabled'' then + raise EXCEPTION ''-20000: Task is in state "%", but it must be in state "enabled" to be started.'', v_state; + end if; + + select case when count(*) = 0 then 0 else 1 end into v_num_rows + from wf_user_tasks + where task_id = begin_task_action__task_id + and user_id = begin_task_action__user_id; + + if v_num_rows = 0 then + raise EXCEPTION ''-20000: You are not assigned to this task.''; + end if; + else if begin_task_action__action = ''finish'' or begin_task_action__action = ''cancel'' then + + if v_state = ''started'' then + /* Is this user the holding user? */ + select case when count(*) = 0 then 0 else 1 end into v_num_rows + from wf_tasks + where task_id = begin_task_action__task_id + and holding_user = begin_task_action__user_id; + if v_num_rows = 0 then + raise EXCEPTION ''-20000: You are not the user currently working on this task.''; + end if; + else if v_state = ''enabled'' then + if begin_task_action__action = ''cancel'' then + raise EXCEPTION ''-20000: You can only cancel a task in state "started", but this task is in state "%"'', v_state; + end if; + + /* Is this user assigned to this task? */ + select case when count(*) = 0 then 0 else 1 end into v_num_rows + from wf_user_tasks + where task_id = begin_task_action__task_id + and user_id = begin_task_action__user_id; + if v_num_rows = 0 then + raise EXCEPTION ''-20000: You are not assigned to this task.''; + end if; + + /* This task is finished without an explicit start. + * Store the user as the holding_user */ + update wf_tasks + set holding_user = begin_task_action__user_id + where task_id = begin_task_action__task_id; + else + raise EXCEPTION ''-20000: Task is in state "%", but it must be in state "enabled" or "started" to be finished'', v_state; + end if; end if; + + else if begin_task_action__action = ''comment'' then + -- We currently allow anyone to comment on a task + -- (need this line because PL/SQL does not like empty if blocks) + v_num_rows := 0; + end if; end if; end if; + + select t.case_id, tr.transition_name into v_case_id, v_transition_name + from wf_tasks t, + wf_transitions tr + where t.task_id = begin_task_action__task_id + and tr.workflow_key = t.workflow_key + and tr.transition_key = t.transition_key; + + /* Insert a journal entry */ + + v_journal_id := journal_entry__new ( + null, + v_case_id, + ''task '' || begin_task_action__task_id || '' '' || begin_task_action__action, + v_transition_name || '' '' || begin_task_action__action, + now(), + begin_task_action__user_id, + begin_task_action__action_ip, + begin_task_action__msg + ); + + return v_journal_id; + +end;' language 'plpgsql'; +