--
-- acs-workflow/sql/sample-article-create.sql
--
-- Creates a sample article-authoring workflow to play with
--
-- @author Kevin Scaldeferri (kevin@theory.caltech.edu)
--
-- @creation-date 2000-05-18
--
-- @cvs-id $Id: sample-article-create.sql,v 1.7 2001/10/09 19:19:43 vinodk Exp $
--

/* This table will hold one row for each case using this workflow. */
create table wf_article_cases (
  case_id 		integer primary key
			constraint wf_article_cases_case_fk
			references wf_cases on delete cascade
);


create function inline_0 () returns integer as '
declare
    v_workflow_key varchar(40);
begin
    v_workflow_key := workflow__create_workflow(
                                ''article_wf'', 
                                ''Article Publication'', 
                                ''Article Publications'', 
                                ''Workflow for managing the publication of an article'',
                                ''wf_article_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', 'article_wf', 'Initial state', 1);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('to_be_written', 'article_wf', 'Needs to be written', 2);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('to_be_reviewed', 'article_wf', 'Needs review', 3);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('to_go_to_press', 'article_wf', 'Ready to go to press', 4);
insert into wf_places(place_key, workflow_key, place_name, sort_order) 
    values ('end', 'article_wf', 'End state', 5);


insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('specification', 'Description and assignment by editor', 'article_wf', 1, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('writing', 'Writing by author', 'article_wf', 2, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type)
    values ('review', 'Approval by reviewer', 'article_wf', 3, 'user');
insert into wf_transitions(transition_key, transition_name, workflow_key, sort_order, trigger_type) 
    values ('press', 'Publication of article', 'article_wf', 4, 'automatic');


-- specification --
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('article_wf', 'specification', 'start', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('article_wf', 'specification', 'to_be_written', 'out');


-- writing
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('article_wf', 'writing', 'to_be_written', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('article_wf', 'writing', 'to_be_reviewed', 'out');


-- review (or-split)
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('article_wf', 'review', 'to_be_reviewed', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction, guard_callback, guard_custom_arg, guard_description) 
    values ('article_wf', 'review', 'to_go_to_press', 'out', 'wf_callback__guard_attribute_true', 'reviewer_ok', 'Reviewer approved article');
insert into wf_arcs(workflow_key, transition_key, place_key, direction, guard_callback, guard_description)
    values ('article_wf', 'review', 'to_be_written', 'out', '#',  'Reviewer disapproved article');


-- press
-- in
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('article_wf', 'press', 'to_go_to_press', 'in');
-- out
insert into wf_arcs(workflow_key, transition_key, place_key, direction) 
    values ('article_wf', 'press', '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(
	''article_wf'',
	''reviewer_ok'',
	''boolean'',
	''Reviewer 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
        (''article_wf'', ''review'', v_attribute_id, 1);

    return null;

end;' language 'plpgsql';

select inline_1 ();
drop function inline_1 ();

-- assignment as part of workflow
insert into wf_transition_assignment_map 
    (workflow_key, transition_key, assign_transition_key)
values
    ('article_wf', 'specification', 'writing');

insert into wf_transition_assignment_map 
    (workflow_key, transition_key, assign_transition_key)
values
    ('article_wf', 'specification', 'review');


/* Context stuff */


insert into wf_context_transition_info (
    context_key, workflow_key, transition_key, hold_timeout_callback, hold_timeout_custom_arg
) values (
    'default', 'article_wf', 'specification', 'wf_callback__time_sysdate_plus_x', 1/24
);



-- create or replace package wf_article_callback
-- is
--     procedure notification(
--         task_id 	in integer, 
--             custom_arg 	in varchar2, 
--             party_to 	in integer, 
--             party_from 	in out integer, 
--             subject 	in out varchar2, 
--             body 	in out varchar2
--     );
-- 
-- end wf_article_callback;
-- /
-- show errors


-- create or replace package body wf_article_callback

-- FIXME: last three variables are in/out variables.

create function wf_article_callback__notification(integer,varchar,integer,integer,varchar,varchar)
returns integer as '
declare
  notification__task_id               alias for $1; 	
  notification__custom_arg            alias for $2;
  notification__party_to              alias for $3;
  notification__party_from            alias for $4;
  notification__subject               alias for $5;
  notification__body                  alias for $6;
  v_deadline_pretty                   varchar(400);
  v_object_name                       text;
  v_transition_name                   wf_transitions.transition_name%TYPE;
  v_name                              varchar(1000);
  v_subject                           text default '''';
  v_body                              text default '''';
begin
        select to_char(ta.deadline,''Mon fmDDfm, YYYY HH24:MI:SS''),
               acs_object__name(c.object_id),
               tr.transition_name
        into   v_deadline_pretty,
               v_object_name, 
	       v_transition_name
	from   wf_tasks ta, wf_transitions tr, wf_cases c
	where  ta.task_id = notification__task_id
        and    c.case_id = ta.case_id
        and    tr.workflow_key = c.workflow_key
        and    tr.transition_key = ta.transition_key;

	v_subject := ''Assignment: '' || v_transition_name || '' '' || 
                     v_object_name;

	v_body := ''Dear '' || acs_object__name(notification__party_to) || ''
'' || ''
Today, you have been assigned to a task.
'' || ''
Task    : '' || v_transition_name || ''
Object  : '' || v_object_name || ''
'';

        if v_deadline_pretty != '''' then
           v_body := v_body || ''Deadline: '' || v_deadline_pretty || ''
'';
        end if;

        -- NOTICE, NOTICE, NOTICE
        --
        -- Since postgresql does not support out parameters, this 
        -- function call has been moved from workflow_case.notify_assignee
        -- into the callback function.

        -- If you implement a new notification callback, make sure 
        -- that this function call is included at the end of the 
        -- callback routine just as we have done for this example code.
        --
        -- DanW (dcwickstrom@earthlink.net)

        v_request_id := acs_mail_nt__post_request (       
            notification__party_from,     -- party_from
            notification__party_to,       -- party_to
            ''f'',                        -- expand_group
            v_subject,                    -- subject
            v_body,                       -- message
            0                             -- max_retries
        );

        return null;
end;' language 'plpgsql';




update wf_context_transition_info
set notification_callback = 'wf_article_callback__notification'
where workflow_key = 'article_wf'
and   context_key = 'default'
and   transition_key = 'specification';