--
-- acs-workflow/sql/workflow-package.sql
--
-- Creates the PL/SQL package that provides the API for defining and dropping
-- workflow cases.
--
-- @author Lars Pind (lars@pinds.com)
--
-- @creation-date 2000-05-18
--
-- @cvs-id $Id: workflow-package.sql,v 1.1.1.1 2001/03/13 22:59:27 ben Exp $
--


create or replace package workflow
as

    /* Call this function after you have created a table */
    function create_workflow (
        workflow_key            in varchar2,
        pretty_name             in varchar2, 
        pretty_plural           in varchar2 default null,
        description             in varchar2 default null,
        table_name              in varchar2,
        id_column               in varchar2 default 'case_id'
    ) return varchar2;

    procedure drop_workflow (
        workflow_key            in varchar2
    );

    procedure delete_cases (
        workflow_key            in varchar2
    );

    function create_attribute ( 
        workflow_key            in varchar2,
        attribute_name          in varchar2,
        datatype                in varchar2,
        pretty_name             in varchar2,
        pretty_plural           in varchar2 default null,
        table_name              in varchar2 default null,
        column_name             in varchar2 default null,
        default_value           in varchar2 default null,
        min_n_values            in integer default 1,
        max_n_values            in integer default 1,
        sort_order              in integer default null,
        storage                 in varchar2 default 'generic',
        wf_datatype             in varchar2 default 'none'
    ) return acs_attributes.attribute_id%TYPE;

    procedure drop_attribute (
        workflow_key            in varchar2,
        attribute_name          in varchar2
    );

    procedure add_place (
        workflow_key            in acs_object_types.object_type%TYPE,
        place_key               in wf_places.place_key%TYPE,
        place_name              in wf_places.place_name%TYPE,
        sort_order              in wf_places.sort_order%TYPE
    );

    procedure delete_place (
        workflow_key            in acs_object_types.object_type%TYPE,
        place_key               in wf_places.place_key%TYPE
    );

    procedure add_transition (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_transitions.transition_key%TYPE,
        transition_name         in wf_transitions.transition_name%TYPE,
        sort_order              in wf_transitions.sort_order%TYPE,
        trigger_type            in wf_transitions.trigger_type%TYPE default 'user'
    );

    procedure delete_transition (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_transitions.transition_key%TYPE
    );

    procedure add_arc (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_arcs.transition_key%TYPE,
        place_key               in wf_arcs.place_key%TYPE,
        direction               in wf_arcs.direction%TYPE,
        guard_callback          in wf_arcs.guard_callback%TYPE default null,
        guard_custom_arg        in wf_arcs.guard_custom_arg%TYPE default null,
        guard_description       in wf_arcs.guard_description%TYPE default null
    );

    procedure delete_arc (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_arcs.transition_key%TYPE,
        place_key               in wf_arcs.place_key%TYPE,
        direction               in wf_arcs.direction%TYPE
    );

    /*
     * A simple workflow is essentially one that we can display nicely using HTML tables.
     * More specifically, it's a workflow containing only sequential routing and 
     * simple iteration, where the choice is always between moving to the next task
     * in the sequence or looping back to some prior task.
     */

    function simple_p (
        workflow_key    in varchar2
    ) return char;

end workflow;
/
show errors

create or replace package body workflow 
as
    
    function create_workflow (
        workflow_key    in varchar2,
        pretty_name     in varchar2, 
        pretty_plural   in varchar2 default null,
        description     in varchar2 default null,
        table_name      in varchar2,
        id_column       in varchar2 default 'case_id'
    ) 
    return varchar2
    is
        v_num_rows      number;
        v_workflow_key  varchar2(100);
    begin
        select count(*) into v_num_rows from user_tables where table_name = upper(create_workflow.table_name);
        if v_num_rows = 0 then
            raise_application_error(-20000, 'The table '''||create_workflow.table_name||''' must be created before calling workflow.create_workflow.');
        end if;

        if substr(create_workflow.workflow_key, length(create_workflow.workflow_key) - 2, 3) != '_wf' then
            v_workflow_key := create_workflow.workflow_key||'_wf';
        else
            v_workflow_key := create_workflow.workflow_key;
        end if;

        acs_object_type.create_type(
            object_type => v_workflow_key, 
            pretty_name => create_workflow.pretty_name, 
            pretty_plural => create_workflow.pretty_plural,
            supertype => 'workflow',
            table_name => create_workflow.table_name,
            id_column => create_workflow.id_column
        );

        insert into wf_workflows 
            (workflow_key, description) 
        values 
            (v_workflow_key, create_workflow.description);

        return v_workflow_key;
    end create_workflow;


    /* Note: The workflow-specific cases table must be dropped before calling this proc */
    procedure drop_workflow (
        workflow_key    in varchar2
    )
    is
        v_table_name varchar2(100);
        v_num_rows number;
        cursor attribute_cur is
            select attribute_id, attribute_name from acs_attributes where object_type = drop_workflow.workflow_key;
    begin
        select table_name into v_table_name 
        from   acs_object_types
        where  object_type = drop_workflow.workflow_key;

        select decode(count(*),0,0,1) into v_num_rows 
        from   user_tables
        where  table_name = upper(v_table_name);

        if v_num_rows > 0 then
            raise_application_error(-20000, 'The table '''||v_table_name||''' must be dropped before calling workflow.drop_workflow.');
        end if;

        select decode(count(*),0,0,1) into v_num_rows 
        from   wf_cases
        where  workflow_key = drop_workflow.workflow_key;

        if v_num_rows > 0 then
            raise_application_error(-20000, 'You must delete all cases of workflow '''||drop_workflow.workflow_key||''' before dropping the workflow definition.');
        end if;

        /* Delete all the auxillary stuff */
        delete from wf_context_task_panels where workflow_key = drop_workflow.workflow_key;
        delete from wf_context_assignments where workflow_key = drop_workflow.workflow_key;
        delete from wf_context_transition_info where workflow_key = drop_workflow.workflow_key; 
        delete from wf_arcs where workflow_key = drop_workflow.workflow_key;
        delete from wf_places where workflow_key = drop_workflow.workflow_key;
        delete from wf_transition_attribute_map where workflow_key = drop_workflow.workflow_key;
        delete from wf_transition_assignment_map where workflow_key = drop_workflow.workflow_key;
        delete from wf_transitions where workflow_key = drop_workflow.workflow_key;

        /* Drop all attributes */
        for attribute_rec in attribute_cur loop
            /* there's no on delete cascade, so we have to manually delete all the values */
            delete from acs_attribute_values where attribute_id = attribute_rec.attribute_id;

            drop_attribute(
                workflow_key => drop_workflow.workflow_key,
                attribute_name => attribute_rec.attribute_name
            );
        end loop;

        /* Delete the workflow */
        delete from wf_workflows where workflow_key = drop_workflow.workflow_key;
        
        acs_object_type.drop_type(
            object_type => drop_workflow.workflow_key
        );
    end drop_workflow;


    procedure delete_cases (
        workflow_key    in varchar2
    )
    is
        cursor workflow_cases is select case_id from wf_cases where workflow_key = delete_cases.workflow_key;
    begin
        for case_rec in workflow_cases loop
            workflow_case.delete(case_rec.case_id);
        end loop;
    end delete_cases;


    function create_attribute (
        workflow_key    in varchar2,
        attribute_name  in varchar2,
        datatype        in varchar2,
        pretty_name     in varchar2,
        pretty_plural   in varchar2 default null,
        table_name      in varchar2 default null,
        column_name     in varchar2 default null,
        default_value   in varchar2 default null,
        min_n_values    in integer default 1,
        max_n_values    in integer default 1,
        sort_order      in integer default null,
        storage         in varchar2 default 'generic',
        wf_datatype     in varchar2 default 'none'
    ) return acs_attributes.attribute_id%TYPE
    is
        v_attribute_id  number;
    begin
        v_attribute_id := acs_attribute.create_attribute(
            object_type => create_attribute.workflow_key,
            attribute_name => create_attribute.attribute_name,
            datatype => create_attribute.datatype,
            pretty_name => create_attribute.pretty_name,
            pretty_plural => create_attribute.pretty_plural,
            table_name => create_attribute.table_name,
            column_name => create_attribute.column_name,
            default_value => create_attribute.default_value,
            min_n_values => create_attribute.min_n_values,
            max_n_values => create_attribute.max_n_values,
            sort_order => create_attribute.sort_order,
            storage => create_attribute.storage
        );
        
        insert into wf_attribute_info
            (attribute_id, wf_datatype)
        values
            (v_attribute_id, create_attribute.wf_datatype);

        return v_attribute_id;
    end create_attribute;


    procedure drop_attribute (
        workflow_key    in varchar2,
        attribute_name  in varchar2
    )
    is
        v_attribute_id  number;
    begin
        select attribute_id into v_attribute_id
        from   acs_attributes
        where  object_type = drop_attribute.workflow_key
        and    attribute_name = drop_attribute.attribute_name;
        
        delete from wf_attribute_info where attribute_id = v_attribute_id;

        acs_attribute.drop_attribute(
            object_type => drop_attribute.workflow_key,
            attribute_name => drop_attribute.attribute_name
        );
    end drop_attribute;


    procedure add_place (
        workflow_key            in acs_object_types.object_type%TYPE,
        place_key               in wf_places.place_key%TYPE,
        place_name              in wf_places.place_name%TYPE,
        sort_order              in wf_places.sort_order%TYPE
    )
    is
    begin
        insert into wf_places (workflow_key, place_key, place_name, sort_order)
        values (add_place.workflow_key, add_place.place_key, add_place.place_name, add_place.sort_order);
    end add_place;

    procedure delete_place (
        workflow_key            in acs_object_types.object_type%TYPE,
        place_key               in wf_places.place_key%TYPE
    )
    is
    begin
        delete from wf_places
        where  workflow_key = delete_place.workflow_key 
        and    place_key = delete_place.place_key;
    end delete_place;

    procedure add_transition (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_transitions.transition_key%TYPE,
        transition_name         in wf_transitions.transition_name%TYPE,
        sort_order              in wf_transitions.sort_order%TYPE,
        trigger_type            in wf_transitions.trigger_type%TYPE default 'user'
    )
    is
    begin
        insert into wf_transitions (workflow_key, transition_key, transition_name, sort_order, trigger_type)
        values (add_transition.workflow_key, add_transition.transition_key, add_transition.transition_name,
        add_transition.sort_order, add_transition.trigger_type);
    end add_transition;

    procedure delete_transition (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_transitions.transition_key%TYPE
    )
    is
    begin
        delete from wf_transitions
        where  workflow_key = delete_transition.workflow_key
        and    transition_key = delete_transition.transition_key;
    end delete_transition;

    procedure add_arc (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_arcs.transition_key%TYPE,
        place_key               in wf_arcs.place_key%TYPE,
        direction               in wf_arcs.direction%TYPE,
        guard_callback          in wf_arcs.guard_callback%TYPE default null,
        guard_custom_arg        in wf_arcs.guard_custom_arg%TYPE default null,
        guard_description       in wf_arcs.guard_description%TYPE default null
    )
    is
    begin
        insert into wf_arcs (workflow_key, transition_key, place_key, direction,
        guard_callback, guard_custom_arg, guard_description)
        values (add_arc.workflow_key, add_arc.transition_key, add_arc.place_key, add_arc.direction,
        add_arc.guard_callback, add_arc.guard_custom_arg, add_arc.guard_description);
    end add_arc;

    procedure delete_arc (
        workflow_key            in acs_object_types.object_type%TYPE,
        transition_key          in wf_arcs.transition_key%TYPE,
        place_key               in wf_arcs.place_key%TYPE,
        direction               in wf_arcs.direction%TYPE
    )
    is
    begin
        delete from wf_arcs
        where  workflow_key = delete_arc.workflow_key
        and    transition_key = delete_arc.transition_key
        and    place_key = delete_arc.place_key
        and    direction = delete_arc.direction;
    end delete_arc;



    function simple_p (
        workflow_key    in varchar2
    )
    return char
    is
        type t_place_table is table of wf_places.place_key%TYPE
            index by binary_integer;
        type t_guard_table is table of wf_arcs.guard_callback%TYPE
            index by binary_integer;
    
        previous_place_list     t_place_table;
        target_place_list       t_place_table;
        guard_list      t_guard_table;
        row_count       integer := 0;
        v_count         integer;
        v_count2        integer;
        v_place_key     wf_places.place_key%TYPE;
        v_end_place     wf_places.place_key%TYPE;
        v_transition_key wf_transitions.transition_key%TYPE;

        cursor transition_targets is
            select place_key,guard_callback,rownum
            from   wf_arcs
            where  workflow_key = simple_p.workflow_key
            and    transition_key = v_transition_key
            and    direction = 'out';

    begin
        /* Let's do some simple checks first */

        /* Places with more than one arc out */
        select count(*) into v_count
        from   wf_places p
        where  p.workflow_key = simple_p.workflow_key
        and    1 < (select count(*) 
                    from   wf_arcs a
                    where  a.workflow_key = p.workflow_key
                    and    a.place_key = p.place_key
                    and    direction = 'in');

        if v_count > 0 then
            return 'f';
        end if;

        /* Transitions with more than one arc in */
        select count(*) into v_count
        from   wf_transitions t
        where  t.workflow_key = simple_p.workflow_key
        and    1 < (select count(*)
                    from   wf_arcs a
                    where  a.workflow_key = t.workflow_key
                    and    a.transition_key = t.transition_key
                    and    direction = 'in');

        if v_count > 0 then
            return 'f';
        end if;

        /* Transitions with more than two arcs out */
        select count(*) into v_count
        from   wf_transitions t
        where  t.workflow_key = simple_p.workflow_key
        and    2 < (select count(*)
                    from   wf_arcs a
                    where  a.workflow_key = t.workflow_key
                    and    a.transition_key = t.transition_key
                    and    direction = 'out');

        if v_count > 0 then
            return 'f';
        end if;

        /* Now we do the more complicated checks.
         * We keep a list of visited places because I couldn't think
         * of a nicer way that wasn't susceptable to infinite loops.
         */


        v_place_key := 'start';
        v_end_place := 'end';

        loop
            exit when v_place_key = v_end_place;

            previous_place_list(row_count) := v_place_key;

            select unique transition_key into v_transition_key
            from   wf_arcs
            where  workflow_key = simple_p.workflow_key
            and    place_key = v_place_key
            and    direction = 'in';

            select count(*) into v_count
            from wf_arcs
            where workflow_key = simple_p.workflow_key
            and   transition_key = v_transition_key
            and   direction = 'out';

            if v_count = 1 then
                select unique place_key into v_place_key
                from wf_arcs
                where workflow_key = simple_p.workflow_key
                and   transition_key = v_transition_key
                and   direction = 'out';

            elsif v_count = 0 then
                /* deadend! */
                return 'f';

            else
                /* better be two based on our earlier test */

                for v_target in transition_targets loop
                    target_place_list(v_target.rownum) := v_target.place_key;
                    guard_list(v_target.rownum) := v_target.guard_callback;
                end loop;
    
                /* Check that the guard functions are the negation of each other 
                 * by looking for the magic entry "#" (exactly once)
                 */
                if ((guard_list(1) != '#' and guard_list(2) != '#') or
                    (guard_list(1) = '#' and guard_list(2) = '#')) then
                    return 'f';
                end if;
    
                /* Check that exactly one of the targets is in the previous list */

                v_count2 := 0;
                for i in 0..row_count loop
                    if target_place_list(1) = previous_place_list(i) then
                        v_count2 := v_count2 + 1;
                        v_place_key := target_place_list(2);
                    end if;
                    if target_place_list(2) = previous_place_list(i) then
                        v_count2 := v_count2 + 1;
                        v_place_key := target_place_list(1);
                    end if;
                end loop;

                if v_count2 != 1 then
                    return 'f';
                end if;

            end if;

            row_count := row_count + 1;

        end loop;

        /* if we got here, it must be okay */
        return 't';

    end simple_p;

end workflow;
/
show errors