If no widget is registered to the attribute, + -- the procedure does nothing. + -- @author Karl Goldstein, Stanislav Freidin + -- @param content_type The name of the content type + -- @param attribute_name The name of the attribute for which to + -- unregister the widget + -- @see {cm_form_widget.register_attribute_widget} + --*/ + content_type in acs_attributes.object_type%TYPE, + attribute_name in acs_attributes.attribute_name%TYPE +); + +procedure set_attribute_param_value ( + --/** Sets custom values for the param tag of a form widget that is + -- registered to a content type attribute. Unless this procedure is + -- called, the default form widget param values are used.
+ -- If the parameter already has a value associated with it, the old + -- value is overwritten. + -- @author Karl Goldstein, Stanislav Freidin + -- @param content_type The name of the content type + -- @param attribute_name The name of the attribute + -- @param param The name of the form widget parameter. + -- Can be an ATS 'element create' flag or an + -- HTML form widget tag + -- @param param_type The type of value the param tag expects. + -- Can be 'onevalue','onelist', or 'multilist', + -- defaults to 'onevalue' + -- @param param_source How the param value is to be acquired, either + -- 'literal', 'eval', or 'query', defaults to + -- 'literal' + -- @param value The value(s) or means or obtaining the value(s) + -- for the param tag + -- @see /ats/form-procs.tcl/element_create, + -- {cm_form_widget.register_attribute_widget} + --*/ + content_type in acs_attributes.object_type%TYPE, + attribute_name in acs_attributes.attribute_name%TYPE, + param in cm_form_widget_params.param%TYPE, + value in cm_attribute_widget_params.value%TYPE, + param_type in cm_attribute_widget_params.param_type%TYPE + default 'onevalue', + param_source in cm_attribute_widget_params.param_source%TYPE + default 'literal' +); + +end cm_form_widget; +/ +show errors + + +create or replace package body cm_form_widget +is + + procedure register_attribute_widget ( + content_type in acs_attributes.object_type%TYPE, + attribute_name in acs_attributes.attribute_name%TYPE, + widget in cm_form_widgets.widget%TYPE, + is_required in cm_attribute_widgets.is_required%TYPE default 'f' + ) + is + v_attr_id acs_attributes.attribute_id%TYPE; + v_prev_widget integer; + begin + + -- Look for the attribute + begin + select attribute_id into v_attr_id from acs_attributes + where attribute_name=register_attribute_widget.attribute_name + and object_type=register_attribute_widget.content_type; + + exception when no_data_found then + raise_application_error(-20000, 'Attribute ' || content_type || + ':' || attribute_name || + ' does not exist in cm_form_widget.register_attribute_widget' + ); + end; + + -- Determine if a previous value exists + select count(1) into v_prev_widget from dual + where exists (select 1 from cm_attribute_widgets + where attribute_id = v_attr_id); + + if v_prev_widget > 0 then + -- Old widget exists: erase parameters, update widget + delete + from cm_attribute_widget_params + where + attribute_id = v_attr_id + and + param_id in (select param_id from cm_form_widgets + where widget = register_attribute_widget.widget); + + update cm_attribute_widgets set + widget = register_attribute_widget.widget, + is_required = register_attribute_widget.is_required + where attribute_id = v_attr_id; + + else + -- No previous widget registered + -- Insert a new row + insert into cm_attribute_widgets + (attribute_id, widget, is_required) + values + (v_attr_id, widget, is_required); + end if; + + end register_attribute_widget; + + procedure set_attribute_order ( + content_type in acs_attributes.object_type%TYPE, + attribute_name in acs_attributes.attribute_name%TYPE, + sort_order in acs_attributes.sort_order%TYPE + ) is + + begin + + update + acs_attributes + set + sort_order = set_attribute_order.sort_order + where + object_type = set_attribute_order.content_type + and + attribute_name = set_attribute_order.attribute_name; + + end set_attribute_order; + + procedure unregister_attribute_widget ( + content_type in acs_attributes.object_type%TYPE, + attribute_name in acs_attributes.attribute_name%TYPE + ) + is + v_attr_id acs_attributes.attribute_id%TYPE; + v_widget cm_form_widgets.widget%TYPE; + begin + + -- Look for the attribute + begin + select attribute_id into v_attr_id from acs_attributes + where attribute_name = unregister_attribute_widget.attribute_name + and object_type = unregister_attribute_widget.content_type; + + exception when no_data_found then + raise_application_error(-20000, 'Attribute ' || content_type || + ':' || attribute_name || + ' does not exist in cm_form_widget.unregister_attribute_widget' + ); + end; + + -- Look for the widget; if no widget is registered, just return + begin + select widget into v_widget from cm_attribute_widgets + where attribute_id = v_attr_id; + exception when no_data_found then + return; + end; + + -- Delete the param values and the widget assignment + delete from cm_attribute_widget_params + where attribute_id = v_attr_id + and param_id in (select param_id from cm_form_widgets + where widget = v_widget); + + delete from cm_attribute_widgets + where attribute_id = v_attr_id; + + end unregister_attribute_widget; + + procedure set_attribute_param_value ( + content_type in acs_attributes.object_type%TYPE, + attribute_name in acs_attributes.attribute_name%TYPE, + param in cm_form_widget_params.param%TYPE, + value in cm_attribute_widget_params.value%TYPE, + param_type in cm_attribute_widget_params.param_type%TYPE + default 'onevalue', + param_source in cm_attribute_widget_params.param_source%TYPE + default 'literal' + ) + is + v_attr_id acs_attributes.attribute_id%TYPE; + v_widget cm_form_widgets.widget%TYPE; + v_param_id cm_form_widget_params.param_id%TYPE; + v_prev_value integer; + begin + + -- Get the attribute id and the widget + begin + select + a.attribute_id, aw.widget into v_attr_id, v_widget + from + acs_attributes a, cm_attribute_widgets aw + where + a.attribute_name = set_attribute_param_value.attribute_name + and + a.object_type=set_attribute_param_value.content_type + and + aw.attribute_id = a.attribute_id; + exception when no_data_found then + raise_application_error(-20000, + 'No widget is registered for attribute ' || + content_type || '.' || attribute_name || + ' in cm_form_widget.set_attribute_param_value'); + end; + + -- Get the param id + begin + select param_id into v_param_id from cm_form_widget_params + where widget = v_widget + and param = set_attribute_param_value.param; + exception when no_data_found then + raise_application_error(-20000, + 'No parameter named ' || param || + ' exists for the widget ' || v_widget || + ' in cm_form_widget.set_attribute_param_value'); + end; + + -- Check if an old value exists + -- Determine if a previous value exists + select count(1) into v_prev_value from dual + where exists (select 1 from cm_attribute_widget_params + where attribute_id = v_attr_id + and param_id = v_param_id); + + if v_prev_value > 0 then + -- Update the value + update cm_attribute_widget_params set + param_type = set_attribute_param_value.param_type, + param_source = set_attribute_param_value.param_source, + value = set_attribute_param_value.value + where + attribute_id = v_attr_id + and + param_id = v_param_id; + else + -- Insert a new value + insert into cm_attribute_widget_params + (attribute_id, param_id, param_type, param_source, value) + values + (v_attr_id, v_param_id, param_type, param_source, value); + end if; + end set_attribute_param_value; + +end cm_form_widget; +/ +show errors + + +@@cms-widgets Index: openacs-4/packages/cms/sql/postgresql/cms-permissions.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-permissions.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/cms/sql/postgresql/cms-permissions.sql 19 May 2001 01:20:10 -0000 1.1 @@ -0,0 +1,859 @@ +-- This file will eventually replace content-perms.sql +-- Implements the CMS permission + +declare + v_perms varchar2(1) := 'f'; +begin + + begin + select 't' into v_perms from dual + where exists (select 1 from acs_privileges + where privilege = 'cm_root'); + exception when no_data_found then + v_perms := 'f'; + end; + + if v_perms <> 't' then + + -- Dummy root privilege + acs_privilege.create_privilege('cm_root', 'Root', 'Root'); + -- He can do everything + acs_privilege.create_privilege('cm_admin', 'Administrator', 'Administrators'); + acs_privilege.create_privilege('cm_relate', 'Relate Items', 'Relate Items'); + acs_privilege.create_privilege('cm_write', 'Write', 'Write'); + acs_privilege.create_privilege('cm_new', 'Create New Item', 'Create New Item'); + acs_privilege.create_privilege('cm_examine', 'Admin-level Read', 'Admin-level Read'); + acs_privilege.create_privilege('cm_read', 'User-level Read', 'User-level Read'); + acs_privilege.create_privilege('cm_item_workflow', 'Modify Workflow', 'Modify Workflow'); + acs_privilege.create_privilege('cm_perm_admin', 'Modify Any Permissions', 'Modify Any Permissions'); + acs_privilege.create_privilege('cm_perm', 'Donate Permissions', 'Donate Permissions'); + + acs_privilege.add_child('cm_root', 'cm_admin'); -- Do anything + acs_privilege.add_child('cm_admin', 'cm_relate'); -- Related/Child items + acs_privilege.add_child('cm_relate', 'cm_write'); -- Modify the item + acs_privilege.add_child('cm_write', 'cm_new'); -- Create subitems + acs_privilege.add_child('cm_new', 'cm_examine'); -- View in admin mode + acs_privilege.add_child('cm_examine', 'cm_read'); -- View in user mode + acs_privilege.add_child('cm_admin', 'cm_item_workflow'); -- Change item workflow + + acs_privilege.add_child('cm_admin', 'cm_perm_admin'); -- Modify any permissions + acs_privilege.add_child('cm_perm_admin', 'cm_perm'); -- Modify any permissions on an item + + -- Proper inheritance + acs_privilege.add_child('admin', 'cm_root'); + + end if; + +end; +/ +show errors + +create or replace package cms_permission +is + procedure update_permissions ( + --/** Make the child item inherit all of the permissions of the parent + -- item. Typically, this function is called whenever permissions on + -- an item are changed for the first time. + -- @author Stanislav Freidin + -- @param item_id The item_id + -- @param is_recursive If 'f', update child items as well, otherwise + -- update only the item itself (note: this is the opposite of + -- is_recursive in grant_permission and revoke_permission) + -- @see {cms_permission.grant_permission}, {cms_permission.copy_permissions} + --*/ + item_id in cr_items.item_id%TYPE, + is_recursive in varchar2 default 't' + ); + + function has_grant_authority ( + --/** Determine if the user may grant a certain permission to another + -- user. The permission may only be granted if the user has + -- the permission himself and posesses the cm_perm access, or if the + -- user posesses the cm_perm_admin access. + -- @author Stanislav Freidin + -- @param item_id The item whose permissions are to be changed + -- @param holder_id The person who is attempting to grant the permissions + -- @param privilege The privilege to be granted + -- @return 't' if the donation is possible, 'f' otherwise + -- @see {cms_permission.grant_permission}, + -- {cms_permission.is_has_revoke_authority}, + -- {acs_permission.grant_permission} + --*/ + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2; + + procedure grant_permission ( + --/** Grant the specified privilege to another user. If the donation is + -- not possible, the procedure does nothing. + -- @author Stanislav Freidin + -- @param item_id The item whose permissions are to be changed + -- @param holder_id The person who is attempting to grant the permissions + -- @param privilege The privilege to be granted + -- @param recepient_id The person who will gain the privilege + -- @param is_recursive If 't', applies the donation recursively to + -- all child items of the item (equivalent to UNIX's chmod -r). + -- If 'f', only affects the item itself. + -- @see {cms_permission.has_grant_authority}, + -- {cms_permission.revoke_permission}, + -- {acs_permission.grant_permission} + --*/ + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + recepient_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f' + ); + + function has_revoke_authority ( + --/** Determine if the user may take a certain permission away from another + -- user. The permission may only be revoked if the user has + -- the permission himself and posesses the cm_perm access, while the + -- other user does not, or if the user posesses the cm_perm_admin access. + -- @author Stanislav Freidin + -- @param item_id The item whose permissions are to be changed + -- @param holder_id The person who is attempting to revoke the permissions + -- @param privilege The privilege to be revoked + -- @param revokee_id The user from whom the privilege is to be taken away + -- @return 't' if it is possible to revoke the privilege, 'f' otherwise + -- @see {cms_permission.has_grant_authority}, + -- {cms_permission.revoke_permission}, + -- {acs_permission.revoke_permission} + --*/ + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE + ) return varchar2; + + procedure revoke_permission ( + --/** Take the specified privilege away from another user. If the operation is + -- not possible, the procedure does nothing. + -- @author Stanislav Freidin + -- @param item_id The item whose permissions are to be changed + -- @param holder_id The person who is attempting to revoke the permissions + -- @param privilege The privilege to be revoked + -- @param recepient_id The person who will lose the privilege + -- @param is_recursive If 't', applies the operation recursively to + -- all child items of the item (equivalent to UNIX's chmod -r). + -- If 'f', only affects the iten itself. + -- @see {cms_permission.grant_permission}, + -- {cms_permission.has_revoke_authority}, + -- {acs_permission.revoke_permission} + --*/ + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f' + ); + + function permission_p ( + --/** Determine if the user has the specified permission on the specified + -- object. Does NOT check objects recursively: that is, if the user has + -- the permission on the parent object, he does not automatically gain + -- the permission on all the child objects.
+ -- In addition, checks if the Publishing workflow has been assigned to + -- the item. If it has, then the user must be assigned to the current + -- workflow task in order to utilize his cm_relate, cm_write or cm_new + -- permission. + -- @author Stanislav Freidin + -- @param item_id The object whose permissions are to be checked + -- @param holder_id The person whose permissions are to be examined + -- @param privilege The privilege to be checked + -- @return 't' if the user has the specified permission on the item, + -- 'f' otherwise + -- @see {cms_permission.grant_permission}, {cms_permission.revoke_permission}, + -- {acs_permission.permission_p} + --*/ + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2; + + function cm_admin_exists + --/** Determine if there exists a user who has administrative + -- privileges on the entire content repository. + -- @author Stanislav Freidin + -- @return 't' if an administrator exists, 'f' otherwise + -- @see {cms_permission.grant_permission} + --*/ + return varchar2; + +end cms_permission; +/ +show errors + + +create or replace package body cms_permission +is + + procedure update_permissions ( + item_id in cr_items.item_id%TYPE, + is_recursive in varchar2 default 'f' + ) + is + v_grantee_id parties.party_id%TYPE; + v_privilege acs_privileges.privilege%TYPE; + v_inherit_p varchar2(1); + v_context_id acs_objects.context_id%TYPE; + + cursor c_child_cur is + select item_id from cr_items + where parent_id = update_permissions.item_id; + begin + + -- If there is no inheritance, nothing to do + select security_inherit_p, context_id + into v_inherit_p, v_context_id + from acs_objects + where object_id = update_permissions.item_id; + + if v_inherit_p = 'f' or v_context_id is null then + return; + end if; + + -- Remove inheritance on the item + update acs_objects set security_inherit_p = 'f' + where object_id = update_permissions.item_id; + + -- If not recursive, turn off inheritance for children of + -- this item + if is_recursive = 'f' then + update + acs_objects + set + security_inherit_p = 'f' + where + object_id in ( + select item_id from cr_items + where parent_id = update_permissions.item_id + ) + and + security_inherit_p = 't'; + end if; + + -- Get permissions assigned to the parent(s), copy them into child + declare + cursor c_perm_cur is + select + p.grantee_id, p.privilege + from + acs_permissions p, + (select object_id from acs_objects + connect by prior context_id = object_id + and security_inherit_p = 't' + start with object_id = v_context_id) o + where + p.object_id = o.object_id; + begin + open c_perm_cur; + loop + fetch c_perm_cur into v_grantee_id, v_privilege; + exit when c_perm_cur%NOTFOUND; + if acs_permission.permission_p ( + item_id, v_grantee_id, v_privilege + ) = 'f' + then + acs_permission.grant_permission ( + item_id, v_grantee_id, v_privilege + ); + end if; + end loop; + close c_perm_cur; + end; + + end update_permissions; + + function has_grant_authority ( + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2 + is + begin + -- Can donate permission only if you already have it and you have cm_perm, + -- OR you have cm_perm_admin + if acs_permission.permission_p (item_id, holder_id, 'cm_perm_admin')= 't' + or ( + acs_permission.permission_p (item_id, holder_id, 'cm_perm') = 't' and + acs_permission.permission_p (item_id, holder_id, privilege) = 't' + ) + then + return 't'; + else + return 'f'; + end if; + end has_grant_authority; + + function has_revoke_authority ( + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE + ) return varchar2 + is + cursor c_perm_cur is + select + 't' + from + acs_privilege_hierarchy + where + acs_permission.permission_p( + has_revoke_authority.item_id, + has_revoke_authority.holder_id, + child_privilege + ) = 't' + and + acs_permission.permission_p( + has_revoke_authority.item_id, + has_revoke_authority.revokee_id, + privilege + ) = 'f' + connect by + prior privilege = child_privilege + start with + child_privilege = 'cm_perm'; + + v_ret varchar2(1); + begin + open c_perm_cur; + fetch c_perm_cur into v_ret; + if c_perm_cur%NOTFOUND then + v_ret := 'f'; + end if; + return v_ret; + end has_revoke_authority; + + procedure grant_permission ( + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + recepient_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f' + ) + is + cursor c_item_cur is + select + item_id + from + (select item_id from cr_items + connect by parent_id = prior item_id + start with item_id = grant_permission.item_id) i + where + has_grant_authority ( + i.item_id, grant_permission.holder_id, grant_permission.privilege + ) = 't' + and + acs_permission.permission_p ( + i.item_id, grant_permission.recepient_id, grant_permission.privilege + ) = 'f'; + + v_item_id cr_items.item_id%TYPE; + + type item_array_type is table of cr_items.item_id%TYPE + index by binary_integer; + v_items item_array_type; + v_idx integer; + v_count integer; + + cursor c_perm_cur is + select descendant from acs_privilege_descendant_map + where privilege = grant_permission.privilege + and descendant <> grant_permission.privilege; + + type perm_array_type is table of acs_privileges.privilege%TYPE + index by binary_integer; + + v_perms perm_array_type; + v_perm acs_privileges.privilege%TYPE; + v_perm_idx integer; + v_perm_count integer; + begin + + update_permissions(item_id, is_recursive); + + -- Select all child items + open c_item_cur; + v_count := 0; + loop + fetch c_item_cur into v_item_id; + exit when c_item_cur%NOTFOUND; + v_count := v_count + 1; + v_items(v_count) := v_item_id; + exit when is_recursive = 'f'; + end loop; + close c_item_cur; + + if v_count < 1 then + return; + end if; + + -- Grant parent permission + for v_idx in 1..v_count loop + acs_permission.grant_permission ( + v_items(v_idx), recepient_id, privilege + ); + end loop; + + -- Select the child permissions + v_perm_count := 0; + open c_perm_cur; + loop + fetch c_perm_cur into v_perm; + exit when c_perm_cur%NOTFOUND; + v_perm_count := v_perm_count + 1; + v_perms(v_perm_count) := v_perm; + end loop; + close c_perm_cur; + + -- Revoke child permissions + for v_idx in 1..v_count loop + for v_perm_idx in 1..v_perm_count loop + acs_permission.revoke_permission ( + v_items(v_idx), recepient_id, v_perms(v_perm_idx) + ); + end loop; + end loop; + + end grant_permission; + + + procedure revoke_permission ( + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f' + ) + is + + cursor c_item_cur is + select item_id from cr_items + connect by parent_id = prior item_id + start with item_id = revoke_permission.item_id + where + has_revoke_authority ( + item_id, + cms_permission.revoke_permission.holder_id, + cms_permission.revoke_permission.privilege, + cms_permission.revoke_permission.revokee_id + ) = 't' + and + acs_permission.permission_p ( + item_id, + cms_permission.revoke_permission.revokee_id, + cms_permission.revoke_permission.privilege + ) = 't'; + + cursor c_perm_cur is + select + child_privilege + from + acs_privilege_hierarchy + where + privilege = revoke_permission.privilege + and + child_privilege <> revoke_permission.privilege; + + type item_array_type is table of cr_items.item_id%TYPE + index by binary_integer; + v_items item_array_type; + v_item_id cr_items.item_id%TYPE; + v_idx integer; + v_count integer; + + type perm_array_type is table of acs_privileges.privilege%TYPE + index by binary_integer; + + v_perms perm_array_type; + v_perm acs_privileges.privilege%TYPE; + v_perm_idx integer; + v_perm_count integer; + begin + + update_permissions(item_id, is_recursive); + + -- Select the child permissions + v_perm_count := 0; + open c_perm_cur; + loop + fetch c_perm_cur into v_perm; + exit when c_perm_cur%NOTFOUND; + v_perm_count := v_perm_count + 1; + v_perms(v_perm_count) := v_perm; + end loop; + close c_perm_cur; + + -- Select child items + v_count := 0; + open c_item_cur; + loop + fetch c_item_cur into v_item_id; + exit when c_item_cur%NOTFOUND; + v_count := v_count + 1; + v_items(v_count) := v_item_id; + exit when is_recursive = 'f'; + end loop; + close c_item_cur; + + if v_count < 1 then + return; + end if; + + -- Grant child permissions + for v_idx in 1..v_count loop + for v_perm_idx in 1..v_perm_count loop + acs_permission.grant_permission ( + v_items(v_idx), revokee_id, v_perms(v_perm_idx) + ); + end loop; + end loop; + + -- Revoke the parent permission + for v_idx in 1..v_count loop + acs_permission.revoke_permission ( + v_items(v_idx), + revoke_permission.revokee_id, + revoke_permission.privilege + ); + end loop; + + end revoke_permission; + + function permission_p ( + item_id in cr_items.item_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2 + is + v_workflow_count integer; + v_task_count integer; + begin + + -- Check permission the old-fashioned way first + if acs_permission.permission_p ( + item_id, holder_id, privilege + ) = 'f' + then + return 'f'; + end if; + + -- Special case for workflow + + if privilege = 'cm_relate' or + privilege = 'cm_write' or + privilege = 'cm_new' + then + + -- Check if the publishing workflow exists, and if it + -- is the only workflow that exists + select + count(case_id) into v_workflow_count + from + wf_cases + where + object_id = permission_p.item_id; + + -- If there are multiple workflows / no workflows, do nothing + -- special + if v_workflow_count <> 1 then + return 't'; + end if; + + -- Even if there is a workflow, the user can touch the item if he + -- has cm_item_workflow + if acs_permission.permission_p ( + item_id, holder_id, 'cm_item_workflow' + ) = 't' + then + return 't'; + end if; + + -- Check if the user holds the current task + if v_workflow_count = 0 then + return 'f'; + end if; + + select + count(task_id) into v_task_count + from + wf_user_tasks t, wf_cases c + where + t.case_id = c.case_id + and + c.workflow_key = 'publishing_wf' + and + c.state = 'active' + and + c.object_id = permission_p.item_id + and + ( t.state = 'enabled' + or + ( t.state = 'started' and t.holding_user = permission_p.holder_id )) + and + t.user_id = permission_p.holder_id; + + -- is the user assigned a current task on this item + if v_task_count = 0 then + return 'f'; + end if; + + end if; + + return 't'; + + end permission_p; + + -- Determine if the CMS admin exists + function cm_admin_exists + return varchar2 + is + v_exists varchar2(1); + begin + + select 't' into v_exists from dual + where exists ( + select 1 from acs_permissions + where privilege in ('cm_admin', 'cm_root') + ); + + return v_exists; + + exception when no_data_found then + return 'f'; + end cm_admin_exists; + +end cms_permission; +/ +show errors + +-- A trigger to automatically grant item creators the cm_write and cm_perm +-- permissions + +create or replace trigger cr_items_permission_tr +after insert on cr_items for each row +declare + v_user_id parties.party_id%TYPE; +begin + + select creation_user into v_user_id from acs_objects + where object_id = :new.item_id; + + if v_user_id is not null then + + if acs_permission.permission_p ( + :new.item_id, v_user_id, 'cm_write' + ) = 'f' + then + acs_permission.grant_permission ( + :new.item_id, v_user_id, 'cm_write' + ); + end if; + + if acs_permission.permission_p ( + :new.item_id, v_user_id, 'cm_perm' + ) = 'f' + then + acs_permission.grant_permission ( + :new.item_id, v_user_id, 'cm_perm' + ); + end if; + end if; + +exception when no_data_found then null; + +end cr_items_permission_tr; +/ +show errors + + +-- A simple wrapper for acs-content-repository procs + +create or replace package content_permission +is + + procedure inherit_permissions ( + parent_object_id in acs_objects.object_id%TYPE, + child_object_id in acs_objects.object_id%TYPE, + child_creator_id in parties.party_id%TYPE default null + ); + + function has_grant_authority ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2; + + procedure grant_permission_h ( + object_id in acs_objects.object_id%TYPE, + grantee_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ); + + procedure grant_permission ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + recepient_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f', + object_type in acs_objects.object_type%TYPE default 'content_item' + ); + + function has_revoke_authority ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE + ) return varchar2; + + procedure revoke_permission_h ( + object_id in acs_objects.object_id%TYPE, + revokee_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ); + + procedure revoke_permission ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f', + object_type in acs_objects.object_type%TYPE default 'content_item' + ); + + function permission_p ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2; + + function cm_admin_exists + return varchar2; + +end content_permission; +/ +show errors + + +create or replace package body content_permission +is + + procedure inherit_permissions ( + parent_object_id in acs_objects.object_id%TYPE, + child_object_id in acs_objects.object_id%TYPE, + child_creator_id in parties.party_id%TYPE default null + ) + is + begin + cms_permission.update_permissions(child_object_id); + end inherit_permissions; + + function has_grant_authority ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2 + is + begin + return cms_permission.has_grant_authority ( + object_id, holder_id, privilege + ); + end has_grant_authority; + + procedure grant_permission_h ( + object_id in acs_objects.object_id%TYPE, + grantee_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) + is + begin + return; + end; + + procedure grant_permission ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + recepient_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f', + object_type in acs_objects.object_type%TYPE default 'content_item' + ) + is + begin + cms_permission.grant_permission ( + object_id, holder_id, privilege, recepient_id, is_recursive + ); + end grant_permission; + + function has_revoke_authority ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE + ) return varchar2 + is + begin + return cms_permission.has_revoke_authority ( + object_id, holder_id, privilege, revokee_id + ); + end has_revoke_authority; + + procedure revoke_permission_h ( + object_id in acs_objects.object_id%TYPE, + revokee_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) + is + begin + return; + end revoke_permission_h; + + procedure revoke_permission ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE, + revokee_id in parties.party_id%TYPE, + is_recursive in varchar2 default 'f', + object_type in acs_objects.object_type%TYPE default 'content_item' + ) + is + begin + cms_permission.revoke_permission ( + object_id, holder_id, privilege, revokee_id, is_recursive + ); + end revoke_permission; + + function permission_p ( + object_id in acs_objects.object_id%TYPE, + holder_id in parties.party_id%TYPE, + privilege in acs_privileges.privilege%TYPE + ) return varchar2 + is + begin + return cms_permission.permission_p ( + object_id, holder_id, privilege + ); + end permission_p; + + function cm_admin_exists + return varchar2 + is + begin + return cms_permission.cm_admin_exists; + end cm_admin_exists; + +end content_permission; +/ +show errors + + + + + + + + + + Index: openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/cms/sql/postgresql/cms-publishing-wf.sql 19 May 2001 01:20:10 -0000 1.1 @@ -0,0 +1,243 @@ +create table cr_workflows ( + case_id integer + constraint cr_workflows_pk + primary key + constraint cr_workflows_case_id_fk + references wf_cases +); + +declare + v_workflow_key varchar(100); + +begin + + v_workflow_key := workflow.create_workflow ( + workflow_key => 'publishing', + pretty_name => 'Simple Publishing Workflow', + pretty_plural => 'Simple Publishing Workflows', + description => 'A simple linear workflow for authoring, + editing and scheduling content items.', + table_name => 'cr_workflows'); + +end; +/ +show errors + +create or replace package publishing_wf as + + -- simply check the 'next_place' attribute and return true if + -- it matches the submitted place_key + + function is_next ( + case_id in number, + workflow_key in varchar, + transition_key in varchar, + place_key in varchar, + direction in varchar, + custom_arg in varchar + ) return char; + +end publishing_wf; +/ +show errors + +create or replace package body publishing_wf as + + function is_next ( + case_id in number, + workflow_key in varchar, + transition_key in varchar, + place_key in varchar, + direction in varchar, + custom_arg in varchar + ) return char is + + v_next_place varchar(100); + v_result char(1) := 'f'; + + begin + + v_next_place := workflow_case.get_attribute_value(case_id, 'next_place'); + + if v_next_place = place_key then + v_result := 't'; + end if; + + return v_result; + + end is_next; + +end publishing_wf; +/ +show errors + +insert into wf_places ( + place_key, workflow_key, place_name, sort_order +) values ( + 'start', 'publishing_wf', 'Created', 1 +); + +insert into wf_places ( + place_key, workflow_key, place_name, sort_order +) values ( + 'authored', 'publishing_wf', 'Authored', 2 +); + +insert into wf_places ( + place_key, workflow_key, place_name, sort_order +) values ( + 'edited', 'publishing_wf', 'Edited', 3 +); + +insert into wf_places ( + place_key, workflow_key, place_name, sort_order +) values ( + 'end', 'publishing_wf', 'Approved', 4 +); + +/* + * The next step is to define the valid transitions from one place in the + * workflow to another. Transitions are where actions occur, either on the + * part of users or machines. + */ + +insert into wf_transitions ( + transition_key, transition_name, workflow_key, sort_order, trigger_type +) values ( + 'authoring', 'Authoring', 'publishing_wf', 1, 'user' +); + +insert into wf_transitions ( + transition_key, transition_name, workflow_key, sort_order, trigger_type +) values ( + 'editing', 'Editing', 'publishing_wf', 2, 'user' +); + +insert into wf_transitions ( + transition_key, transition_name, workflow_key, sort_order, trigger_type +) values ( + 'approval', 'Approval', 'publishing_wf', 3, 'user' +); + +/* + * The next step is connect transitions to places. This is analogous + * to adding arrows or arcs to the workflow diagram, pointing from places + * to transitions and from transitions to other places. + */ + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction +) values ( + 'publishing_wf', 'authoring', 'start', 'in' +); + +-- The authoring transition can either be to 'authored' or back to 'start' +-- if the author checks in the item without completing it + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'authoring', 'authored', 'out', 'publishing_wf.is_next' +); + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'authoring', 'start', 'out', '#' +); + + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction +) values ( + 'publishing_wf', 'editing', 'authored', 'in' +); + +-- The editing transition can either be to 'edited' or back to 'start' +-- if the item is rejected + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'editing', 'authored', 'out', '#' +); + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'editing', 'edited', 'out', 'publishing_wf.is_next' +); + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'editing', 'start', 'out', 'publishing_wf.is_next' +); + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction +) values ( + 'publishing_wf', 'approval', 'edited', 'in' +); + +-- The approval transition can be to the end, back to authored +-- for further editor review, or back to start for further author work. + + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'approval', 'edited', 'out', '#' +); + + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'approval', 'end', 'out', 'publishing_wf.is_next' +); + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'approval', 'authored', 'out', 'publishing_wf.is_next' +); + +insert into wf_arcs ( + workflow_key, transition_key, place_key, direction, guard_callback +) values ( + 'publishing_wf', 'approval', 'start', 'out', 'publishing_wf.is_next' +); + + +declare + v_attribute_id acs_attributes.attribute_id%TYPE; +begin + v_attribute_id := workflow.create_attribute( + workflow_key => 'publishing_wf', + attribute_name => 'next_place', + datatype => 'string', + wf_datatype => 'none', + pretty_name => 'Next Place', + default_value => 'start' + ); + + insert into wf_transition_attribute_map + (workflow_key, transition_key, attribute_id, sort_order) + values + ('publishing_wf', 'authoring', v_attribute_id, 1); + + insert into wf_transition_attribute_map + (workflow_key, transition_key, attribute_id, sort_order) + values + ('publishing_wf', 'editing', v_attribute_id, 1); + + insert into wf_transition_attribute_map + (workflow_key, transition_key, attribute_id, sort_order) + values + ('publishing_wf', 'approval', v_attribute_id, 1); + +end; +/ +show errors; + Index: openacs-4/packages/cms/sql/postgresql/cms-update.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-update.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/cms/sql/postgresql/cms-update.sql 19 May 2001 01:20:10 -0000 1.1 @@ -0,0 +1,60 @@ + +-- Modify permissions to include the cm_relate permission +declare + v_exists integer; +begin + select count(*) into v_exists from acs_privileges + where privilege = 'cm_admin'; + + if v_exists > 0 then + select count(*) into v_exists from acs_privileges + where privilege = 'cm_relate'; + + if v_exists < 1 then + acs_privilege.create_privilege('cm_relate', 'Relate Items', 'Relate Items'); + acs_privilege.add_child('cm_admin', 'cm_relate'); + update acs_privilege_hierarchy + set privilege = 'cm_relate' + where privilege = 'cm_admin' + and child_privilege = 'cm_write'; + end if; + end if; +end; +/ +show errors + +-- This parent_id column was not included in the cr_keywords table +-- for RC 0. Ensure this column is there. + +begin + + if not column_exists('cr_keywords', 'parent_id') then + + dbms_output.put_line('Adding PARENT_ID column to CR_KEYWORDS' || + ' and updating the parent id from the context id'); + + execute immediate 'alter table cr_keywords add + parent_id integer + constraint cr_keywords_hier + references cr_keywords'; + + execute immediate 'update cr_keywords set parent_id = ( + select context_id from acs_objects + where object_id = keyword_id)'; + + end if; + +end; +/ +show errors + +-- Drop the broken trigger, if any +begin + execute immediate 'drop trigger cr_item_permission_tr'; +exception when others then null; +end; +/ +show errors + +exec content_type.register_mime_type ('content_template', 'text/html'); +exec content_type.register_mime_type ('content_template', 'text/plain'); Index: openacs-4/packages/cms/sql/postgresql/cms-widgets.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-widgets.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/cms/sql/postgresql/cms-widgets.sql 19 May 2001 01:20:10 -0000 1.1 @@ -0,0 +1,295 @@ +/* cms-widgets.sql - metadata for form widgets */ + + + +/* insert form widgets and params */ +begin + + -- insert the standard form widgets + insert into cm_form_widgets (widget) values ('text'); + insert into cm_form_widgets (widget) values ('textarea'); + insert into cm_form_widgets (widget) values ('radio'); + insert into cm_form_widgets (widget) values ('checkbox'); + insert into cm_form_widgets (widget) values ('select'); + insert into cm_form_widgets (widget) values ('multiselect'); + insert into cm_form_widgets (widget) values ('date'); + + + -- insert the standard form widget params and ATS form element params + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (10, 'text', 'size', 'f', 't', '30'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (20, 'textarea', 'rows', 'f', 't', '6'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (30, 'textarea', 'cols', 'f', 't', '60'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (31, 'textarea', 'wrap', 'f', 't', 'physical'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (40, 'radio', 'options', 't', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (50, 'checkbox', 'options', 't', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (60, 'select', 'options', 't', 'f', '{ -- {} }'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (61, 'select', 'values', 'f', 'f', '{}'); + + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (70, 'select', 'size', 'f', 't', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (80, 'multiselect', 'options', 't', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (90, 'multiselect', 'size', 'f', 't', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (100, 'date', 'format', 'f', 'f', 'DD/MONTH/YYYY'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (110, 'date', 'year_interval', 'f', 'f', '2000 2005 1'); + +end; +/ +show errors + +begin + + /* search widget and params */ + dbms_output.put_line('Inserting search widget metadata...'); + + insert into cm_form_widgets (widget) values ('search'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (120, 'search', 'search_query', 't', 'f', null); + +end; +/ +show errors + + + + + + +begin + + /* new widget params 11-31-00 */ + dbms_output.put_line('Inserting new widget metadata...'); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (11, 'text', 'maxlength', 'f', 't', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (12, 'text', 'validate', 'f', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (32, 'textarea', 'validate', 'f', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (41, 'radio', 'values', 'f', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (51, 'checkbox', 'values', 'f', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (91, 'multiselect', 'values', 'f', 'f', null); + + insert into cm_form_widget_params + (param_id, widget, param, is_required, is_html, default_value) + values + (121, 'search', 'result_datatype', 'f', 'f', 'search'); + +end; +/ +show errors + + + + + + + + + + + + +/* Register attribute widgets for content_revision and image */ + +begin + -- register form widgetes for content revision attributes + + cm_form_widget.register_attribute_widget( + content_type => 'content_revision', + attribute_name => 'title', + widget => 'text', + is_required => 't' + ); + + cm_form_widget.register_attribute_widget( + content_type => 'content_revision', + attribute_name => 'description', + widget => 'textarea' + ); + + cm_form_widget.set_attribute_param_value( + content_type => 'content_revision', + attribute_name => 'description', + param => 'cols', + param_type => 'onevalue', + param_source => 'literal', + value => 40 + ); + + cm_form_widget.register_attribute_widget( + content_type => 'content_revision', + attribute_name => 'mime_type', + widget => 'select', + is_required => 't' + ); + + cm_form_widget.set_attribute_param_value( + content_type => 'content_revision', + attribute_name => 'mime_type', + param => 'options', + param_type => 'multilist', + param_source => 'query', + value => 'select + label, map.mime_type as value + from + cr_mime_types types, + cr_content_mime_type_map map + where + types.mime_type = map.mime_type + and + content_type = :content_type + order by + label' + ); + + cm_form_widget.set_attribute_param_value( + content_type => 'content_revision', + attribute_name => 'mime_type', + param => 'values', + param_type => 'onevalue', + param_source => 'query', + value => 'select + mime_type + from + cr_revisions + where + revision_id = content_item.get_latest_revision(:item_id)' + ); + + -- register for widgets for image attributes + + cm_form_widget.register_attribute_widget( + content_type => 'image', + attribute_name => 'width', + widget => 'text' + ); + + cm_form_widget.register_attribute_widget( + content_type => 'image', + attribute_name => 'height', + widget => 'text' + ); + + cm_form_widget.set_attribute_param_value( + content_type => 'image', + attribute_name => 'width', + param => 'size', + param_type => 'onevalue', + param_source => 'literal', + value => 5 + ); + + cm_form_widget.set_attribute_param_value( + content_type => 'image', + attribute_name => 'height', + param => 'size', + param_type => 'onevalue', + param_source => 'literal', + value => 5 + ); + +end; +/ +show errors + + + +begin + + /* new widget params 11-31-00 */ + dbms_output.put_line('Inserting new widget attributes...'); + + cm_form_widget.set_attribute_param_value( + content_type => 'content_revision', + attribute_name => 'title', + param => 'maxlength', + param_type => 'onevalue', + param_source => 'literal', + value => 1000 + ); + + cm_form_widget.set_attribute_param_value ( + content_type => 'content_revision', + attribute_name => 'description', + param => 'validate', + param_type => 'onevalue', + param_source => 'literal', + value => 'description_4k_max { cm_widget::validate_description $value } { Description length cannot exceed 4000 bytes. }' + ); + +end; +/ +show errors Index: openacs-4/packages/cms/sql/postgresql/cms-workflow.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/cms/sql/postgresql/cms-workflow.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/cms/sql/postgresql/cms-workflow.sql 19 May 2001 01:20:10 -0000 1.1 @@ -0,0 +1,1464 @@ +--/** This package is used to manipulate the publishing workflow for CMS +-- @author Michael Pih +--*/ + + + + + +create or replace package content_workflow +as + + function is_overdue ( + --/** Determines if the workflow task is overdue + -- @author Michael Pih + -- @param task_id The task id + -- @return 't' if the deadline > sysdate, 'f' otherwise + --*/ + v_task_id in wf_tasks.task_id%TYPE + ) return char; + + + function is_overdue ( + --/** Determines if the workflow transition (task) is overdue + -- @author Michael Pih + -- @param case_id The case id + -- @param transition_key The transition key + -- @return 't' if the deadline > sysdate, 'f' otherwise + --*/ + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char; + + + function get_holding_user_name( + --/** Gets the name of the user who is currently holding this task + -- @author Michael Pih + -- @param task_id The task id + -- @return name of the user who holds the task, otherwise NULL + --*/ + v_task_id in wf_tasks.task_id%TYPE + ) return varchar2; + + + function get_first_place + --/** Gets the first place in the workflow (determined by sort order) + -- @author Michael Pih + -- @return the first place in the workflow + --*/ + return wf_places.place_key%TYPE; + + + function get_this_place( + --/** Gets the current place in the workflow given the current transition + -- @author Michael Pih + -- @param transition_key The transition + -- @return the current place in the workflow + --*/ + transition_key in wf_transitions.transition_key%TYPE + ) return wf_places.place_key%TYPE; + + + function get_next_place( + --/** Gets the next place given a transition key (determined by sort order). + -- Throws an error if there is no next place. + -- @author Michael Pih + -- @param transition_key The transition + -- @return the next place in the workflow + --*/ + transition_key in wf_transitions.transition_key%TYPE + ) return wf_places.place_key%TYPE; + + + function get_previous_place( + --/** Gets the previous place given a transition key + -- (determined by sort order). + -- Throws an error if there is no previous place. + -- @author Michael Pih + -- @param transition_key The transition + -- @return the previous place in the workflow + --*/ + transition_key in wf_transitions.transition_key%TYPE + ) return wf_places.place_key%TYPE; + + + procedure checkout ( + --/** Checks out a task + -- @author Michael Pih + -- @param task_id The task_id + -- @param hold_timeout How long the user expects to hold this task + -- @param user_id The user checking out the task + -- @param ip_address The user's IP address (for auditing) + -- @param msg Comments concerning checkout + --*/ + task_id in wf_tasks.task_id%TYPE, + hold_timeout in wf_tasks.hold_timeout%TYPE default null, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + msg in varchar + ); + + + procedure checkin ( + --/** Checks in a task that the user is holding. Throws an error + -- if the task is not checked out already or if the task is checked + -- out by another user. + -- @author Michael Pih + -- @param task_id The task_id + -- @param user_id The user checking in the task + -- @param ip_address The IP address of the user + -- @param msg Comment associated with checking the task in + --*/ + task_id in wf_tasks.task_id%TYPE, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + msg in varchar + ); + + + procedure approve( + --/** Finish a task + -- @author Michael Pih + -- @param task_id The task_id + -- @param user_id The user finishing the task + -- @param ip_address The user's IP address (for auditing) + -- @param msg Comments concerning finishing the task + --*/ + task_id in wf_tasks.task_id%TYPE, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + msg in varchar + ); + + + procedure reject( + --/** Finish a task + -- @author Michael Pih + -- @param task_id The task_id + -- @param user_id The user finishing the task + -- @param ip_address The user's IP address (for auditing) + -- @param transition_key The transition the user wants to fall back to + -- @param msg Comments concerning finishing the task + --*/ + task_id in wf_tasks.task_id%TYPE, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + transition_key in wf_transitions.transition_key%TYPE, + msg in varchar + ); + + + procedure notify_of_checkout( + --/** Helper procedure. + -- Fires notifications after stealing the lock on a task + -- @author Michael Pih + -- @param task_id The task_id + -- @param holding_user_old The user finishing the task + -- @param holding_user_new The user's IP address (for auditing) + -- @param msg Comments concerning stealing the task + --*/ + task_id in wf_tasks.task_id%TYPE, + holding_user_old in wf_tasks.holding_user%TYPE, + holding_user_new in wf_tasks.holding_user%TYPE, + msg in varchar + ); + + + function can_reject( + --/** Returns 't' if a task is rejectable by the user. + -- Otherwise returns 'f' + -- @author Michael Pih + -- @param task_id The task_id + -- @param user_id The user_id + -- @return 't' if the task can be rejected, 'f' otherwise + --*/ + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char; + + + + function can_approve( + --/** Returns 't' if a task is approvable by the user. + -- Otherwise returns 'f' + -- @author Michael Pih + -- @param task_id The task_id + -- @param user_id The user_id + -- @return 't' if the task can be approved, 'f' otherwise + --*/ + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char; + + + function can_start( + --/** Returns 't' if a task can be checked out by the user. + -- Otherwise returns 'f' + -- @author Michael Pih + -- @param task_id The task_id + -- @param user_id The user_id + -- @return 't' if the task can be started, 'f' otherwise + --*/ + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char; + + + + function approve_string( + --/** If the task is approvable, returns 'Approve' or if it is the first + -- task, then 'Finish'. Otherwise returns null. + -- @author Michael Pih + -- @param task_id The task_id + -- @param user_id The user_id + -- @return 'Approve','Finish' or null + --*/ + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return varchar2; + + + + function count_finished_tasks( + --/** Gets the number of finished tasks for a given case. + -- @author Michael Pih + -- @param case_id The case_id + -- @return the number of finished tasks for a given case + --*/ + case_id in wf_cases.case_id%TYPE + ) return integer; + + + function count_unfinished_tasks( + --/** Gets the number of unfinished tasks for a given case + -- @author Michael Pih + -- @param case_id The case_id + -- @return the number of unfinished tasks for a given case + --*/ + case_id in wf_cases.case_id%TYPE + ) return integer; + + + function is_active ( + --/** Determines whether a case transition is active + -- @author Michael Pih + -- @param case_id The case_id + -- @param transition_key The transition + -- @return 't' id that case transition is active, 'f' otherwise + --*/ + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char; + + + function is_finished ( + --/** Determines whether a case transition is finished + -- @author Michael Pih + -- @param case_id The case_id + -- @param transition_key The transition + -- @return 't' id that case transition is finished, 'f' otherwise + --*/ + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char; + + + function is_checked_out ( + --/** Determines whether a case transition is checked out + -- @author Michael Pih + -- @param case_id The case_id + -- @param transition_key The transition + -- @return 't' id that case transition is checked out, 'f' otherwise + --*/ + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char; + + + function is_checked_out ( + --/** Determines whether a case transition is checked out by a certain user + -- @author Michael Pih + -- @param case_id The case_id + -- @param transition_key The transition + -- @param user_id The user + -- @return 't' id that case transition is checked out by the specified + -- user, 'f' otherwise + --*/ + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char; + + + function get_status( + --/** Gets the status of the task. + -- @author Michael Pih + -- @param case_id The case_id + -- @param transition_key The transition + -- @return HTML-formatted status of the task, null otherwise + --*/ + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return varchar2; + + + function can_touch ( + --/** Returns 't' if a user has permission to touch an item + -- @author Michael Pih + -- @param item_id The item + -- @param user_id The user + -- @return 't' if a user has permission to touch an item, 'f' otherwise + -- An item is touchable if: + -- 1) the user has admin privileges on the + -- 2) a workflow exists, current task assigned to user, and + -- the task is not checked out + --*/ + item_id in cr_items.item_id%TYPE, + user_id in users.user_id%TYPE + ) return char; + + + + function unfinished_workflow_exists ( + --/** Returns 't' if an unfinished (not cancelled) workflow exists + -- otherwise returns 'f' + -- @author Michael Pih + -- @param item_id The item + -- @return 't' if a workflow case exists and is not in the 'finished' or + -- 'canceled' state + --*/ + item_id in cr_items.item_id%TYPE + ) return char; + + +end content_workflow; +/ +show errors + + + + + +create or replace package body content_workflow +as + + function is_overdue( + v_task_id in wf_tasks.task_id%TYPE + ) return char + is + v_overdue_p char; + begin + select + 't' + into + v_overdue_p + from + wf_tasks t, wf_case_deadlines dead + where + t.task_id = v_task_id + and + t.case_id = dead.case_id + and + t.transition_key = dead.transition_key + and + t.workflow_key = dead.workflow_key + and + dead.deadline is not null + and + dead.deadline < trunc(sysdate); + + return v_overdue_p; + exception + when NO_DATA_FOUND then + return 'f'; + end is_overdue; + + + + function is_overdue( + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char + is + v_overdue_p char; + begin + select + 't' into v_overdue_p + from + wf_case_deadlines dead + where + case_id = is_overdue.case_id + and + transition_key = is_overdue.transition_key + and + deadline is not null + and + deadline < trunc(sysdate) + and + content_workflow.is_finished(is_overdue.case_id, + is_overdue.transition_key) = 'f'; + + return v_overdue_p; + exception + when NO_DATA_FOUND then + return 'f'; + end is_overdue; + + + + function get_holding_user_name( + v_task_id in wf_tasks.task_id%TYPE + ) return varchar2 + is + v_name varchar2(100); + begin + + select + first_names || ' ' || last_name + into + v_name + from + persons p, wf_tasks t + where + t.holding_user = p.person_id + and + t.task_id = v_task_id; + + return v_name; + exception + when NO_DATA_FOUND then + return null; + end get_holding_user_name; + + + + function get_first_place + return wf_places.place_key%TYPE + is + v_first_place wf_places.place_key%TYPE; + begin + + select + place_key into v_first_place + from + wf_places w + where + workflow_key = 'publishing_wf' + and + sort_order = (select + min(sort_order) + from + wf_places + where + workflow_key = w.workflow_key); + + return v_first_place; + + exception when no_data_found then + return null; + end get_first_place; + + + + function get_this_place( + transition_key in wf_transitions.transition_key%TYPE + ) return wf_places.place_key%TYPE + is + v_this_place wf_places.place_key%TYPE; + begin + + select + place_key into v_this_place + from + wf_arcs + where + transition_key = get_this_place.transition_key + and + workflow_key = 'publishing_wf' + and + direction = 'in'; + + return v_this_place; + + exception + when no_data_found then + raise_application_error(-20000, 'Bad transition key ' || + get_this_place.transition_key + ); + end get_this_place; + + + + function get_next_place( + transition_key in wf_transitions.transition_key%TYPE + ) return wf_places.place_key%TYPE + is + v_next_place wf_places.place_key%TYPE; + + cursor c_places_cur is + select + there.place_key + from + wf_places here, wf_places there + where + here.workflow_key = 'publishing_wf' + and + here.workflow_key = there.workflow_key + and + here.place_key = content_workflow.get_this_place( + get_next_place.transition_key ) + and + there.sort_order > here.sort_order + order by + there.sort_order; + begin + + open c_places_cur; + fetch c_places_cur into v_next_place; + if c_places_cur%NOTFOUND then + close c_places_cur; + raise_application_error (-20000, + 'content_workflow.get_next_place - No next place - Dead End' + ); + end if; + close c_places_cur; + + return v_next_place; + exception + when NO_DATA_FOUND then + return null; + end get_next_place; + + + + function get_previous_place( + transition_key in wf_transitions.transition_key%TYPE + ) return wf_places.place_key%TYPE + is + v_previous_place wf_places.place_key%TYPE; + + cursor c_places_cur is + select + there.place_key + from + wf_places here, wf_places there + where + here.workflow_key = 'publishing_wf' + and + here.workflow_key = there.workflow_key + and + here.place_key = content_workflow.get_this_place( + get_previous_place.transition_key ) + and + there.sort_order < here.sort_order + order by + there.sort_order desc; + begin + + open c_places_cur; + fetch c_places_cur into v_previous_place; + if c_places_cur%NOTFOUND then + close c_places_cur; + raise_application_error (-20000, + 'content_workflow.get_previous_place - No previous place - Dead End' + ); + end if; + close c_places_cur; + + return v_previous_place; + exception + when NO_DATA_FOUND then + return null; + end get_previous_place; + + + + procedure checkout ( + task_id in wf_tasks.task_id%TYPE, + hold_timeout in wf_tasks.hold_timeout%TYPE, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + msg in varchar + ) + is + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_journal_id number; + v_transition_key wf_transitions.transition_key%TYPE; + v_this_place wf_places.place_key%TYPE; + begin + + -- find out who is holding the task right now + select + state, holding_user, transition_key + into + v_task_state, v_holding_user, v_transition_key + from + wf_tasks + where + task_id = checkout.task_id; + + -- someone else has already holds this task + -- we need to check in the task as the other person before + -- this user can check it out + if v_task_state = 'started' and v_holding_user is not null + and v_holding_user ^= checkout.user_id then + + -- need to manually update the state otherwise a new task is created + update wf_tasks + set state = 'enabled', + holding_user = null, + hold_timeout = null + where task_id = checkout.task_id; + + v_task_state := 'enabled'; + end if; + + -- actually check out the item + -- (start the task but don't change 'next_place') + if v_task_state = 'enabled' then + + v_journal_id := workflow_case.begin_task_action( + task_id => checkout.task_id, + action => 'start', + action_ip => checkout.ip_address, + user_id => checkout.user_id, + msg => checkout.msg + ); + + v_this_place := content_workflow.get_this_place( v_transition_key ); + + workflow_case.set_attribute_value( + journal_id => v_journal_id, + attribute_name => 'next_place', + value => v_transition_key + ); + + workflow_case.end_task_action( + task_id => checkout.task_id, + action => 'start', + journal_id => v_journal_id + ); + + -- change the holding user and hold timeout + update wf_tasks + set hold_timeout = checkout.hold_timeout, + holding_user = checkout.user_id + where task_id = checkout.task_id; + + if v_holding_user is not null and + v_holding_user ^= checkout.user_id then + + -- send a notification + content_workflow.notify_of_checkout( + task_id => checkout.task_id, + holding_user_old => v_holding_user, + holding_user_new => checkout.user_id, + msg => checkout.msg + ); + end if; + + else + raise_application_error(-20000, + 'Cannot check out this task because it''s in an invalid state ' + || v_task_state + ); + end if; + + end checkout; + + + + procedure checkin ( + task_id in wf_tasks.task_id%TYPE, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + msg in varchar + ) is + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_journal_id number; + v_this_place wf_places.place_key%TYPE; + v_transition_key wf_transitions.transition_key%TYPE; + begin + + -- find out who is holding the task right now + select + state, holding_user, transition_key + into + v_task_state, v_holding_user, v_transition_key + from + wf_tasks + where + task_id = checkin.task_id; + + if v_task_state = 'started' and v_holding_user = checkin.user_id then + + + v_journal_id := workflow_case.begin_task_action( + task_id => checkin.task_id, + action => 'finish', + user_id => checkin.user_id, + action_ip => checkin.ip_address, + msg => checkin.msg + ); + + v_this_place := content_workflow.get_this_place( v_transition_key ); + + workflow_case.set_attribute_value( + journal_id => v_journal_id, + attribute_name => 'next_place', + value => v_this_place + ); + + workflow_case.end_task_action( + journal_id => v_journal_id, + action => 'finish', + task_id => checkin.task_id + ); + + + elsif v_task_state ^= 'started' then + raise_application_error( -20000, + 'Cannot chack in this task because it''s in an invalid state ' + || v_task_state + ); + else + raise_application_error( -20000, + 'Cannot check in this task because user_id ' || user_id || + ' is not the holding user' + ); + end if; + + end checkin; + + + + procedure approve( + task_id in wf_tasks.task_id%TYPE, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + msg in varchar + ) is + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_journal_id number; + v_transition_key wf_transitions.transition_key%TYPE; + v_next_place wf_places.place_key%TYPE; + begin + + -- find out who is holding the task right now + select + state, holding_user, transition_key + into + v_task_state, v_holding_user, v_transition_key + from + wf_tasks + where + task_id = approve.task_id; + + if v_task_state = 'started' and v_holding_user ^= approve.user_id then + + raise_application_error( -20000, + 'content_workflow.approve - Could not approve task because this task + is checked out by someone else ' || v_holding_user + ); + + elsif v_task_state ^= 'started' and v_task_state ^= 'enabled' then + raise_application_error( -20000, + 'content_workflow.approve - Could not approve task because this task + is in an invalid state ' || v_task_state + ); + + -- user is allowed to finish the task + else + + -- we need to checkout the task first + if v_task_state = 'enabled' then + content_workflow.checkout( + task_id => approve.task_id, + hold_timeout => null, + user_id => approve.user_id, + ip_address => approve.ip_address, + msg => approve.msg + ); + end if; + + v_journal_id := workflow_case.begin_task_action( + task_id => approve.task_id, + action => 'finish', + action_ip => approve.ip_address, + user_id => approve.user_id, + msg => approve.msg + ); + + v_next_place := content_workflow.get_next_place( + transition_key => v_transition_key + ); + + workflow_case.set_attribute_value( + journal_id => v_journal_id, + attribute_name => 'next_place', + value => v_next_place + ); + + workflow_case.end_task_action( + task_id => approve.task_id, + action => 'finish', + journal_id => v_journal_id + ); + + end if; + end approve; + + + procedure reject( + task_id in wf_tasks.task_id%TYPE, + user_id in acs_objects.creation_user%TYPE, + ip_address in acs_objects.creation_ip%TYPE, + transition_key in wf_transitions.transition_key%TYPE, + msg in varchar + ) is + v_task_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_transition_key wf_transitions.transition_key%TYPE; + v_journal_id number; + v_sanity_check integer; + v_previous_place wf_places.place_key%TYPE; + begin + + -- find out who is holding the task right now + select + state, holding_user, transition_key + into + v_task_state, v_holding_user, v_transition_key + from + wf_tasks + where + task_id = reject.task_id; + + -- do a quick sanity check + -- make sure the desired transition is accessible from this transition + select + count(1) into v_sanity_check + from + wf_arcs out, wf_arcs dest + where + out.workflow_key = 'publishing_wf' + and + out.workflow_key = dest.workflow_key + and + out.direction = 'out' + and + dest.direction = 'in' + and + out.transition_key = v_transition_key + and + dest.transition_key = reject.transition_key + and + reject.transition_key ^= v_transition_key + and + -- make sure the arcs are connected + out.place_key = dest.place_key; + + + if v_sanity_check = 0 then + raise_application_error( -20000, + 'content_workflow.reject - Sanity check failed - invalid transition: ' + || reject.transition_key + ); + end if; + + + if v_task_state = 'started' and v_holding_user ^= reject.user_id then + raise_application_error( -20000, + 'content_workflow.reject - Could not reject task because this task + is checked out by someone else ' || v_holding_user + ); + elsif v_task_state ^= 'started' and v_task_state ^= 'enabled' then + raise_application_error( -20000, + 'content_workflow.approve - Could not reject task because this task + is in an invalid state ' || v_task_state + ); + else + + -- we need to start this task first + if v_task_state = 'enabled' then + content_workflow.checkout( + task_id => reject.task_id, + hold_timeout => null, + user_id => reject.user_id, + ip_address => reject.ip_address, + msg => reject.msg + ); + + end if; + + + -- ok to reject this task + v_journal_id := workflow_case.begin_task_action( + task_id => reject.task_id, + action => 'finish', + action_ip => reject.ip_address, + user_id => reject.user_id, + msg => reject.msg + ); + + v_previous_place := content_workflow.get_this_place( + transition_key => reject.transition_key + ); + + workflow_case.set_attribute_value( + journal_id => v_journal_id, + attribute_name => 'next_place', + value => v_previous_place + ); + + workflow_case.end_task_action( + task_id => reject.task_id, + action => 'finish', + journal_id => v_journal_id + ); + + end if; + end reject; + + + + + + procedure notify_of_checkout ( + task_id in wf_tasks.task_id%TYPE, + holding_user_old in wf_tasks.holding_user%TYPE, + holding_user_new in wf_tasks.holding_user%TYPE, + msg in varchar + ) is + v_hold_user_old varchar(100); + v_hold_user_new varchar(100); + v_transition_name wf_transitions.transition_name%TYPE; + v_request_id nt_requests.request_id%TYPE; + v_item_name varchar(100); + begin + + -- get the robbed users name + select + first_names || ' ' || last_name into v_hold_user_old + from + persons + where + person_id = notify_of_checkout.holding_user_old; + + -- get the lock stealers name + select + first_names || ' ' || last_name into v_hold_user_new + from + persons + where + person_id = notify_of_checkout.holding_user_new; + + -- get the item name and transition name + select + transition_name, content_item.get_title( c.object_id ) + into + v_transition_name, v_item_name + from + wf_transitions trans, wf_tasks t, wf_cases c + where + trans.transition_key = t.transition_key + and + t.case_id = c.case_id + and + t.task_id = notify_of_checkout.task_id; + + -- send out the request + v_request_id := nt.post_request ( + party_from => notify_of_checkout.holding_user_new, + party_to => notify_of_checkout.holding_user_old, + expand_group => 'f', + subject => v_hold_user_new || ' stole the lock for ' || + v_transition_name || ' of ' || v_item_name, + message => 'Dear ' || v_hold_user_old || ',\n' || + notify_of_checkout.msg + ); + + end notify_of_checkout; + + + function can_reject( + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char + is + v_transition_key wf_transitions.transition_key%TYPE; + v_can_reject char(1); + begin + + select + 't' into v_can_reject + from + wf_tasks + where + task_id = can_reject.task_id + and + workflow_key = 'publishing_wf' + and + (state = 'enabled' + or (state = 'started' + and holding_user = can_reject.user_id)) + and + content_workflow.get_this_place(transition_key) ^= + content_workflow.get_first_place; + + return v_can_reject; + exception + when NO_DATA_FOUND then + return 'f'; + end can_reject; + + + + function can_approve( + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char + is + v_can_approve char(1); + begin + + select + 't' into v_can_approve + from + wf_tasks + where + (state = 'enabled' + or (state = 'started' + and holding_user = can_approve.user_id)) + and + task_id = can_approve.task_id + and + workflow_key = 'publishing_wf'; + + return v_can_approve; + exception + when NO_DATA_FOUND then + return 'f'; + + end can_approve; + + + function can_start( + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char + is + v_can_start char(1); + begin + + select + 't' into v_can_start + from + wf_tasks + where + (state = 'enabled' + or (state = 'started' + and holding_user ^= can_start.user_id)) + and + task_id = can_start.task_id + and + workflow_key = 'publishing_wf'; + + return v_can_start; + exception + when NO_DATA_FOUND then + return 'f'; + + end can_start; + + + + function approve_string( + task_id in wf_tasks.task_id%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return varchar2 + is + v_transition_key wf_transitions.transition_key%TYPE; + v_approve_string varchar(10); + begin + + if content_workflow.can_approve( + approve_string.task_id, approve_string.user_id ) = 't' then + + select + transition_key into v_transition_key + from + wf_tasks + where + task_id = approve_string.task_id; + + if content_workflow.get_this_place( v_transition_key ) = + content_workflow.get_first_place then + v_approve_string := 'Finish'; + else + v_approve_string := 'Approve'; + end if; + + else + v_approve_string := null; + end if; + + return v_approve_string; + exception + when NO_DATA_FOUND then + return null; + + end approve_string; + + + function count_finished_tasks( + case_id in wf_cases.case_id%TYPE + ) return integer + is + v_already_finished_tasks integer; + begin + + select + count(before.place_key) into v_already_finished_tasks + from + ( + select + p.sort_order + from + wf_tasks t, wf_places p + where + t.workflow_key = 'publishing_wf' + and + t.workflow_key = p.workflow_key + and + p.place_key = content_workflow.get_this_place( t.transition_key ) + and + -- active task + t.state in ('enabled', 'started') + and + t.case_id = count_finished_tasks.case_id + ) here, + wf_places before + where + before.workflow_key = 'publishing_wf' + and + -- earlier transitions (tasks that have already been completed) + before.sort_order < here.sort_order; + + return v_already_finished_tasks; + exception + when NO_DATA_FOUND then + return 0; + end count_finished_tasks; + + + + function count_unfinished_tasks( + case_id in wf_cases.case_id%TYPE + ) return integer + is + v_unfinished_tasks integer; + v_already_finished_tasks integer; + v_all_tasks integer; + begin + + select + count(transition_key) into v_all_tasks + from + wf_transitions + where + workflow_key = 'publishing_wf'; + + v_already_finished_tasks := content_workflow.count_finished_tasks( + case_id => count_unfinished_tasks.case_id + ); + + v_unfinished_tasks := v_all_tasks - v_already_finished_tasks; + + + return v_unfinished_tasks; + exception + when NO_DATA_FOUND then + return 0; + end count_unfinished_tasks; + + + function is_active ( + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char + is + v_unfinished_count integer; + begin + + select + count(task_id) into v_unfinished_count + from + wf_tasks + where + transition_key = is_active.transition_key + and + case_id = is_active.case_id + and + state in ('started','enabled'); + + if v_unfinished_count > 0 then + return 't'; + else + return 'f'; + end if; + end is_active; + + + function is_finished ( + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char + is + + cursor c_already_finished_tasks is + select + trans.transition_key + from + wf_transitions trans, wf_places here, wf_places there, wf_tasks t + where + trans.workflow_key = 'publishing_wf' + and + here.workflow_key = there.workflow_key + and + here.workflow_key = trans.workflow_key + and + -- the task belongs to this case + t.case_id = is_finished.case_id + and + -- the task is active + t.state in ('enabled','started') + and + -- here is the place the case is currently at + here.place_key = content_workflow.get_this_place(t.transition_key) + and + -- there is the place we're checking if it's finished + there.place_key = content_workflow.get_this_place( + trans.transition_key) + and + -- there needs to be done before here + -- (sort order determines task order) + there.sort_order < here.sort_order; + + begin + + for v_finished_task in c_already_finished_tasks loop + + -- check if this task has already been finished + if is_finished.transition_key = v_finished_task.transition_key then + return 't'; + end if; + end loop; + + return 'f'; + end is_finished; + + + function is_checked_out ( + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return char + is + v_checkout_count integer; + begin + select + count(*) into v_checkout_count + from + wf_tasks t + where + workflow_key = 'publishing_wf' + and + case_id = is_checked_out.case_id + and + transition_key = is_checked_out.transition_key + and + state = 'started'; + + if v_checkout_count > 0 then + return 't'; + else + return 'f'; + end if; + + end is_checked_out; + + + function is_checked_out ( + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE, + user_id in wf_tasks.holding_user%TYPE + ) return char + is + v_checkout_count integer; + begin + select + count(task_id) into v_checkout_count + from + wf_tasks t + where + workflow_key = 'publishing_wf' + and + case_id = is_checked_out.case_id + and + transition_key = is_checked_out.transition_key + and + state = 'started' + and + holding_user = is_checked_out.user_id; + + if v_checkout_count > 0 then + return 't'; + else + return 'f'; + end if; + + end is_checked_out; + + + function get_status( + case_id in wf_cases.case_id%TYPE, + transition_key in wf_transitions.transition_key%TYPE + ) return varchar2 + is + v_status varchar(1000); + v_state wf_tasks.state%TYPE; + v_holding_user wf_tasks.holding_user%TYPE; + v_hold_timeout wf_tasks.hold_timeout%TYPE; + v_enabled_date wf_tasks.enabled_date%TYPE; + v_started_date wf_tasks.started_date%TYPE; + begin + + select + state, holding_user, hold_timeout, enabled_date, started_date + into + v_state, v_holding_user, v_hold_timeout, v_enabled_date, v_started_date + from + wf_tasks + where + transition_key = get_status.transition_key + and + case_id = get_status.case_id + and + state in ('enabled','started'); + + v_status := '
| Activated on ' || + to_char(v_enabled_date,'Mon. DD, YYYY HH24:MI:SS') || + ' |
| Checked Out by ' || person.name(v_holding_user) || + ' on ' || to_char(v_started_date,'Mon. DD, YYYY HH24:MI:SS') || + ' until ' || + to_char(v_hold_timeout,'Mon. DD, YYYY') || ' |