--
-- acs-workflow/sql/sample-expenses-create.sql
--
-- Creates an expenses workflow to play with.
--
-- @author Lars Pind (lars@pinds.com)
--
-- @creation-date 2000-05-18
--
-- @cvs-id $Id: sample-expenses-create.sql,v 1.3 2001/04/13 00:29:01 danw Exp $
--
/* This table will hold one row for each case using this workflow. */
create table wf_expenses_cases (
  case_id 		integer primary key
			constraint wf_expenses_cases_case_fk
			references wf_cases on delete cascade
);

create function inline_0 () returns integer as '
declare
    v_workflow_key varchar;
begin
    v_workflow_key := workflow__create_workflow(
	''expenses_wf'', 
	''Expense Authorization'', 
	''Expense authorizations'', 
	''Workflow for authorizing employee\\\'s expenses on the company\\\'s behalf'', 
	''wf_expenses_cases'',
        ''case_id''
    );

    return null;
end;' language 'plpgsql'; 

select inline_0 ();
drop function inline_0 ();

insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('start', 'expenses_wf', 'Initial state', 1);
insert into wf_places(place_key, workflow_key, place_name, sort_order)
    values ('assignments_done', 'expenses_wf', 'Tasks have been assigned', 2);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('supervisor_to_approve', 'expenses_wf', 'Supervisor is to approve', 3);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('other_to_approve', 'expenses_wf', 'Other is to approve', 4);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('supervisor_approved', 'expenses_wf', 'Supervisor has approved', 5);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('other_approved', 'expenses_wf', 'Other has approved', 6);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('ready_to_buy', 'expenses_wf', 'Both have approved', 8);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('end', 'expenses_wf', 'End state', 9);


insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('assign', 'Assign users to approval', 'expenses_wf', 1, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('and_split', 'Both supervisor and Other approval', 'expenses_wf', 2, 'automatic');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('supervisor_approval', 'Supervisor approval', 'expenses_wf', 3, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type)
    values ('other_approval', 'Other approval', 'expenses_wf', 4, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('and_join', 'Supervisor and other approval both done', 'expenses_wf', 5, 'automatic');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('buy', 'Buy stuff', 'expenses_wf', 6, 'user');

-- assign --
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'assign', 'start', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'assign', 'assignments_done', 'out');


-- and-split --
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_split', 'assignments_done', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_split', 'supervisor_to_approve', 'out');
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_split', 'other_to_approve', 'out');


-- supervisor_approval
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'supervisor_approval', 'supervisor_to_approve', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'supervisor_approval', 'supervisor_approved', 'out');


-- other-approval
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'other_approval', 'other_to_approve', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'other_approval', 'other_approved', 'out');


-- and-join and or-split
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_join', 'supervisor_approved', 'in');
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('expenses_wf', 'and_join', 'other_approved', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction, guard_callback, guard_description) 
    values ('expenses_wf', 'and_join', 'ready_to_buy', 'out', 'wf_expenses.guard_both_approved_p', 'Both supervisor and CEO approved');
insert into wf_arcs(workflow_key, transition_key, place_key, direction, guard_callback, guard_description)
    values ('expenses_wf', 'and_join', 'end', 'out', '#', 'Either supervisor or CEO did not approve');

-- buy
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction)
    values ('expenses_wf', 'buy', 'ready_to_buy', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction)
    values ('expenses_wf', 'buy', 'end', 'out');


create function inline_1 () returns integer as '
declare
    v_attribute_id acs_attributes.attribute_id%TYPE;
begin
    v_attribute_id := workflow__create_attribute(
	''expenses_wf'',
	''supervisor_ok'',
	''boolean'',
	''Supervisor Approval'',
        null,
        null,
        null,
	''f'',
        1,
        1,
        null,
        ''generic'',
        ''none''
    );

    insert into wf_transition_attribute_map
        (workflow_key, transition_key, attribute_id, sort_order) 
    values
        (''expenses_wf'', ''supervisor_approval'', v_attribute_id, 1);

    v_attribute_id := workflow__create_attribute(
	''expenses_wf'',
	''other_ok'',
	''boolean'',
	''Other Approval'',
        null,
        null,
        null,
	''f'',
        1,
        1,
        null,
        ''generic'',
        ''none''
    );

    insert into wf_transition_attribute_map
        (workflow_key, transition_key, attribute_id, sort_order) 
    values 
        (''expenses_wf'', ''other_approval'', v_attribute_id, 1);

    return null;

end;' language 'plpgsql';

select inline_1 ();

drop function inline_1 ();

insert into wf_transition_assignment_map 
    (workflow_key, transition_key, assign_transition_key)
values
    ('expenses_wf', 'assign', 'supervisor_approval');

insert into wf_transition_assignment_map 
    (workflow_key, transition_key, assign_transition_key)
values
    ('expenses_wf', 'assign', 'other_approval');



/* Context stuff */

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, estimated_minutes
) values (
    'default', 'expenses_wf', 'assign', 5
);

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, hold_timeout_callback, hold_timeout_custom_arg, estimated_minutes
) values (
    'default', 'expenses_wf', 'supervisor_approval', 'wf_callback.time_sysdate_plus_x', 1/24, 15
);

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, estimated_minutes
) values (
    'default', 'expenses_wf', 'other_approval', 15
);

insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, estimated_minutes
) values (
    'default', 'expenses_wf', 'buy', 30
);


insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'supervisor_approval', 1, 'Claim Info', 'sample/expenses-claim-info'
);

insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'supervisor_approval', 2, 'Logic and Aids', 'sample/expenses-approval-aids'
);

insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'other_approval', 1, 'Claim Info', 'sample/expenses-claim-info'
);

insert into wf_context_task_panels (
    context_key, workflow_key, transition_key, sort_key, header, template_url
) values (
    'default', 'expenses_wf', 'other_approval', 2, 'Logic and Aids', 'sample/expenses-approval-aids'
);




/* Callbacks for the workflow */

-- create or replace package wf_expenses
--is
--
--    function guard_both_approved_p (
--	case_id in integer,
--	workflow_key in varchar2,
--	transition_key in varchar2,
--	place_key in varchar2,
--	direction_in varchar2,
--	custom_arg in varchar2
--    )
--    return char;
--
--end wf_expenses;
--/
--show errors;


--create or replace package body wf_expenses
create function guard_both_approved_p (integer,varchar,varchar,varchar,varchar,varchar) 
returns boolean as '
declare
	guard_both_approved_p__case_id                 alias for $1;
	guard_both_approved_p__workflow_key            alias for $2;
	guard_both_approved_p__transition_key          alias for $3;
	guard_both_approved_p__place_key               alias for $4;
	guard_both_approved_p__direction_in            alias for $5;
	guard_both_approved_p__custom_arg              alias for $6;
	v_other_ok_p                                   boolean;
	v_supervisor_ok_p                              boolean;
begin
	v_other_ok_p := workflow_case__get_attribute_value(
	    guard_both_approved_p__case_id,
	    ''other_ok''
	);
	if v_other_ok_p = ''f'' then
	    return ''f'';
	end if;
	v_supervisor_ok_p := workflow_case__get_attribute_value(
	    guard_both_approved_p__case_id,
	    ''supervisor_ok''
	);

	return v_supervisor_ok_p;

end;' language 'plpgsql';