Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-create.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,12 @@ +-- +-- packages/project-manager/sql/postgresql/project-manager-create.sql +-- +-- @author jade@bread.com +-- @creation-date 2003-05-15 +-- @cvs-id $Id: project-manager-create.sql,v 1.1.2.1 2004/10/26 03:08:48 khuang Exp $ +-- +-- + +@@ project-manager-table-create.sql +@@ project-manager-functions-create.sql +@@ project-manager-notifications-create.sql Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-custom-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-custom-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-custom-create.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,45 @@ +-- +-- packages/project-manager/sql/postgresql/project-manager-customize.sql +-- +-- @author jader@bread.com +-- @creation-date 2003-12-05 +-- + +-- this file is used to add custom columns to the projects table. +-- you can then customize the columns shown + +-- if you do set this up, you need to set the parameter in the admin +-- UI, so that the add-edit page will know that there is custom code, +-- You'll need to create an add-edit-custom page, filling in the skeleton there + +-- you should use the content_type__create_attribute procedure to add +-- in columns so that the views are correctly recreated. + +-- PROJECTS + +-- example, using customer +-- this is actually done in the table-create script + +-- this adds in the customer column. This is an example of how +-- the custom columns are added in. I put this here as a reminder +-- that other columns can be added in as well. These custom items +-- are in the custom-create.sql script + +declare + attribute_id integer; +begin + attribute_id := content_type.create_attribute ( + content_type => 'pm_project', + attribute_name => 'customer_id', + datatype => 'integer', + pretty_name => 'Customer', + pretty_plural => 'Customers', + sort_order => null, + default_value => null, + column_spec => 'integer constraint pm_project_customer_fk references organizations' + ); +end; +/ + +show errors + Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-custom-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-custom-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-custom-drop.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,12 @@ +-- packages/project-manager/sql/project-manager-drop.sql +-- drop script +-- +-- @author jade@bread.com +-- @creation-date 2003-12-05 +-- @cvs-id $Id: project-manager-custom-drop.sql,v 1.1.2.1 2004/10/26 03:08:48 khuang Exp $ +-- + +-- drop any custom tables here. + +select content_type__drop_attribute ('pm_project', 'customer_id', 't'); + Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-drop.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,212 @@ +-- packages/project-manager/sql/project-manager-drop.sql +-- drop script +-- +-- @author jade@bread.com +-- @creation-date 2003-05-15 +-- @cvs-id $Id: project-manager-drop.sql,v 1.1.2.1 2004/10/26 03:08:48 khuang Exp $ +-- + +-------- +-- TASKS +-------- + +\i project-manager-custom-drop.sql +\i project-manager-notifications-drop.sql + +drop table pm_task_logger_proj_map; + +create function inline_0 () +returns integer as ' +declare + v_item RECORD; + +begin + for v_item in select + item_id + from + cr_items + where + content_type = ''pm_task'' + LOOP + PERFORM pm_task__delete_task_item(v_item.item_id); + end loop; + + return 0; +end; +' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); + +-- unregister content_types from folder +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; + v_item_id cr_items.item_id%TYPE; + v_item_cursor RECORD; +begin + + -- delete all contents of projects folder + FOR v_item_cursor IN + select + item_id + from + cr_items + where + content_type = ''pm_task'' + LOOP + PERFORM pm_project__delete_task_item(v_item_cursor.item_id); + END LOOP; + + -- this table must not hold reference to ''pm_tasks'' type + delete from cr_folder_type_map where content_type = ''pm_tasks''; + + return 0; +end; +' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); + +-- unregister content_types from folder +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; + v_item_id cr_items.item_id%TYPE; + v_item_cursor RECORD; +begin + + -- delete all contents of projects folder + FOR v_item_cursor IN + select + item_id + from + cr_items + where + content_type = ''pm_project'' + LOOP + PERFORM pm_project__delete_project_item(v_item_cursor.item_id); + END LOOP; + + return 0; +end; +' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); + +-- unregister content_types from folder +create function inline_0 () +returns integer as ' +declare + v_folder_id cr_folders.folder_id%TYPE; + v_item_id cr_items.item_id%TYPE; + v_item_cursor RECORD; +begin + + FOR v_item_cursor IN + select folder_id from cr_folders where description=''Project Repository'' + LOOP + PERFORM content_folder__unregister_content_type ( + v_item_cursor.folder_id, -- folder_id + ''pm_project'', -- content_type + ''t'' -- include_subtypes + ); + PERFORM content_folder__delete(v_item_cursor.folder_id); + END LOOP; + + -- this table must not hold reference to ''pm_project'' type + delete from cr_folder_type_map where content_type = ''pm_project''; + + return 0; +end; +' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); + + +-- task dependency types +drop table pm_task_dependency_types cascade; +drop table pm_task_dependency cascade; +drop sequence pm_task_dependency_seq; +drop sequence pm_tasks_number_seq; + +select content_type__drop_attribute ('pm_task', 'end_date', 't'); +select content_type__drop_attribute ('pm_task', 'percent_complete', 't'); +select content_type__drop_attribute ('pm_task', 'estimated_hours_work', 't'); +select content_type__drop_attribute ('pm_task', 'estimated_hours_work_min', 't'); +select content_type__drop_attribute ('pm_task', 'estimated_hours_work_max', 't'); +select content_type__drop_attribute ('pm_task', 'actual_hours_worked', 't'); +select content_type__drop_attribute ('pm_task', 'earliest_start', 't'); +select content_type__drop_attribute ('pm_task', 'earliest_finish', 't'); +select content_type__drop_attribute ('pm_task', 'latest_start', 't'); +select content_type__drop_attribute ('pm_task', 'latest_finish', 't'); + +------------- +-- WORKGROUPS +------------- + +drop sequence pm_workgroup_seq; +drop table pm_workgroup_parties; +drop table pm_workgroup; + +------------ +-- PROCESSES +------------ + +drop sequence pm_process_seq; +drop sequence pm_process_task_seq; +drop sequence pm_process_task_dependency_seq; + +drop table pm_process_task_assignment; +drop table pm_process_task_dependency; +drop table pm_process_task; +drop table pm_process; + +--------- +-- OTHERS +--------- +drop table pm_default_roles; +drop table pm_project_assignment; +drop table pm_task_assignment; +drop table pm_roles; +drop sequence pm_role_seq; + + +select drop_package('pm_task'); + + +----------- +-- PROJECTS +----------- + +--drop permissions +delete from acs_permissions where object_id in (select project_id from pm_projects); + + +-- drop package, which drops all functions created with define_function_args +select drop_package('pm_project'); + +--drop table +drop table pm_projects cascade; + +drop sequence pm_project_status_seq; +drop table pm_project_status cascade; + + + +drop sequence pm_task_status_seq; +drop table pm_task_status cascade; + +drop table pm_tasks cascade; +drop table pm_tasks_revisions cascade; + +select content_type__drop_type('pm_task', 't', 'f'); + +select content_type__drop_type('pm_project', 't', 'f'); + +-- note that the Project Repository folder is not deleted + Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-functions-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-functions-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-functions-create.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,610 @@ +-- +-- packages/project-manager/sql/postgresql/project-manager-functions-create.sql +-- +-- @author jade@bread.com, ncarroll@ee.usyd.edu.au +-- @creation-date 2003-05-15 +-- @cvs-id $Id: project-manager-functions-create.sql,v 1.1.2.1 2004/10/26 03:08:48 khuang Exp $ +-- +-- + +-- When we created the acs object type above, we specified a +-- 'name_method'. This is the name of a function that will return the +-- name of the object. This is a convention ensuring that all objects +-- can be identified. Now we have to build that function. In this case, +-- we'll return a field called title as the name. + + +create or replace package pm_project as + + function name ( p_pm_project_id in pm_projects.project_id%TYPE + ) return varchar2; + + function new_root_folder (p_package_id in apm_packages.package_id%TYPE + ) return integer; + + function get_root_folder (p_package_id in apm_packages.package_id%TYPE , + p_create_if_not_present_p in char + ) return integer; + + function new_project_item ( + p_project_name in varchar2 , + p_project_code in pm_projects.project_code%TYPE, + p_parent_id in integer , + p_goal in pm_projects.goal%TYPE, + p_description in varchar2 , + p_mime_type in varchar2 , + p_planned_start_date in pm_projects.planned_start_date%TYPE , + p_planned_end_date in pm_projects.planned_end_date%TYPE , + p_actual_start_date in pm_projects.actual_start_date%TYPE , + p_actual_end_date in pm_projects.actual_end_date%TYPE, + p_logger_project in pm_projects.logger_project%TYPE , + p_ongoing_p in pm_projects.ongoing_p%TYPE , + p_status_id in pm_projects.status_id%TYPE , + p_customer_id in pm_projects.customer_id%TYPE , + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer; + + procedure delete_project_item ( p_project_id in pm_projects.project_id%TYPE); + + function new_project_revision ( + p_item_id in integer , + p_project_name in varchar2 , + p_project_code in pm_projects.project_code%TYPE, + p_parent_id in integer , + p_goal in pm_projects.goal%TYPE, + p_description in varchar2 , + p_planned_start_date in pm_projects.planned_start_date%TYPE , + p_planned_end_date in pm_projects.planned_end_date%TYPE , + p_actual_start_date in pm_projects.actual_start_date%TYPE , + p_actual_end_date in pm_projects.actual_end_date%TYPE, + p_logger_project in pm_projects.logger_project%TYPE , + p_ongoing_p in pm_projects.ongoing_p%TYPE , + p_status_id in pm_projects.status_id%TYPE , + p_customer_id in pm_projects.customer_id%TYPE , + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer; + + function new_unique_name (p_package_id in integer + ) return varchar; + +end pm_project; +/ + +show errors + +create or replace package body pm_project as + function name ( p_pm_project_id in pm_projects.project_id%TYPE + ) return varchar2 + is + v_pm_project_name varchar2(500); + begin + select name || '_' || p_pm_project_id + into v_pm_project_name + from pm_projectsx + where item_id = p_pm_project_id; + + return v_pm_project_name; + end name; + + function new_root_folder (p_package_id in apm_packages.package_id%TYPE + ) return integer + is + v_folder_id cr_folders.folder_id%TYPE; + v_folder_name cr_items.name%TYPE; + begin + v_folder_name := new_unique_name (p_package_id); + + v_folder_id := content_folder.new ( + name => v_folder_name, + label => 'Projects', + description => 'Project Repository', + parent_id => null, + context_id => p_package_id, + folder_id => null, + creation_date => sysdate, + creation_user => null, + creation_ip => null + ); + + -- Register the standard content types + content_folder.register_content_type ( + folder_id => v_folder_id, + content_type => 'pm_project', + include_subtypes => 'f' + ); + + -- there is no facility in the API for adding in the package_id, + -- so we have to do it ourselves + + update cr_folders + set package_id = p_package_id + where folder_id = v_folder_id; + + -- TODO: Handle Permissions here for this folder. + + return v_folder_id; + end new_root_folder; + + function get_root_folder (p_package_id in apm_packages.package_id%TYPE , + p_create_if_not_present_p char + ) return integer + is + v_folder_id cr_folders.folder_id%TYPE; + v_count integer; + begin + select count(*) into v_count + from cr_folders + where package_id = p_package_id; + + -- raise notice 'count is % for package_id %', v_count, p_package_id; + + if v_count > 1 then + raise_application_error(-20001, 'More than one project repository for this application instance'); + elsif v_count = 1 then + select folder_id into v_folder_id + from cr_folders + where package_id = p_package_id; + else + if p_create_if_not_present_p = 't' then + -- Must be a new instance. Create a new root folder. + -- raise notice 'creating a new root repository folder'; + v_folder_id := new_root_folder(p_package_id); + else + -- raise notice 'setting to null'; + v_folder_id := null; + end if; + end if; + + return v_folder_id; + end get_root_folder; + + function new_project_item ( + p_project_name in varchar2 , + p_project_code in pm_projects.project_code%TYPE, + p_parent_id in integer , + p_goal in pm_projects.goal%TYPE, + p_description in varchar2 , + p_mime_type in varchar2 , + p_planned_start_date in pm_projects.planned_start_date%TYPE , + p_planned_end_date in pm_projects.planned_end_date%TYPE , + p_actual_start_date in pm_projects.actual_start_date%TYPE , + p_actual_end_date in pm_projects.actual_end_date%TYPE, + p_logger_project in pm_projects.logger_project%TYPE , + p_ongoing_p in pm_projects.ongoing_p%TYPE , + p_status_id in pm_projects.status_id%TYPE , + p_customer_id in pm_projects.customer_id%TYPE , + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer + is + v_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_id cr_items.item_id%TYPE; + v_parent_id cr_items.parent_id%TYPE; + begin + + select acs_object_id_seq.nextval into v_id from dual; + + v_parent_id := get_root_folder (p_package_id, 't'); + + -- raise notice 'v_parent_id (%) p_parent_id (%)', v_parent_id, p_parent_id; + + if p_parent_id is not null + then + v_parent_id := p_parent_id; + end if; + + -- raise notice 'v_parent_id (%) p_parent_id (%)', v_parent_id, p_parent_id; + + v_item_id := content_item.new ( + name => v_id, + parent_id => v_parent_id, + item_id => v_id , + locale => null, + creation_date => p_creation_date, + creation_user => p_creation_user, + context_id => p_parent_id, + creation_ip => p_creation_ip, + item_subtype => 'content_item', + content_type => 'pm_project', + title => p_project_name, + description => p_description, + mime_type => p_mime_type, + nls_language => null, + data => null + ); + + v_revision_id := content_revision.new ( + title => p_project_name, + description => p_description, + publish_date => sysdate, + mime_type => p_mime_type, + nls_language => NULL, + data => NULL, + item_id => v_item_id, + revision_id => NULL, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip + ); + + content_item.set_live_revision (v_revision_id); + + insert into pm_projects ( + project_id, project_code, + goal, planned_start_date, + planned_end_date, actual_start_date, actual_end_date, + logger_project, ongoing_p, estimated_finish_date, + earliest_finish_date, latest_finish_date, + actual_hours_completed, + estimated_hours_total, status_id, customer_id) + values ( + v_revision_id, p_project_code, + p_goal, p_planned_start_date, + p_planned_end_date, p_actual_start_date, + p_actual_end_date, p_logger_project, p_ongoing_p, + p_planned_end_date, + p_planned_end_date, p_planned_end_date, '0', + '0', p_status_id, p_customer_id + ); + + acs_permission.grant_permission( + v_revision_id, + p_creation_user, + 'admin' + ); + + return v_revision_id; + end new_project_item; + + procedure delete_project_item ( p_project_id in pm_projects.project_id%TYPE) + is + v_child_item_id cr_items.item_id%TYPE; + begin + -- raise NOTICE 'Deleting pm_project...'; + + for v_child in (select item_id + from cr_items + where parent_id = p_project_id and + content_type = 'pm_project') + loop + delete_project_item(v_child_item_id); + end loop; + + delete from pm_projects where project_id in (select revision_id from pm_projectsx where item_id = p_project_id); + + content_item.del(p_project_id); + end delete_project_item; + + + function new_project_revision ( + p_item_id in integer , + p_project_name in varchar2 , + p_project_code in pm_projects.project_code%TYPE, + p_parent_id in integer , + p_goal in pm_projects.goal%TYPE, + p_description in varchar2 , + p_planned_start_date in pm_projects.planned_start_date%TYPE , + p_planned_end_date in pm_projects.planned_end_date%TYPE , + p_actual_start_date in pm_projects.actual_start_date%TYPE , + p_actual_end_date in pm_projects.actual_end_date%TYPE, + p_logger_project in pm_projects.logger_project%TYPE , + p_ongoing_p in pm_projects.ongoing_p%TYPE , + p_status_id in pm_projects.status_id%TYPE , + p_customer_id in pm_projects.customer_id%TYPE , + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer + is + v_revision_id cr_revisions.revision_id%TYPE; + begin + + -- the item_id is the project_id + v_revision_id := content_revision.new ( + title => p_project_name, + description => p_description, + publish_date => sysdate, + mime_type => 'text/plain', + nls_language => NULL, + data => NULL, + item_id => p_item_id, + revision_id => NULL, + creation_date => p_creation_date, + creation_user => p_creation_user, + creation_ip => p_creation_ip + ); + + content_item.set_live_revision (v_revision_id); + + insert into pm_projects ( + project_id, project_code, + goal, planned_start_date, + planned_end_date, actual_start_date, actual_end_date, + logger_project, + ongoing_p, status_id, customer_id) + values ( + v_revision_id, p_project_code, + p_goal, p_planned_start_date, + p_planned_end_date, p_actual_start_date, + p_actual_end_date, + p_logger_project, p_ongoing_p, p_status_id, p_customer_id); + + acs_permission.grant_permission( + v_revision_id, + p_creation_user, + 'admin' + ); + + return v_revision_id; + + end new_project_revision; + + function new_unique_name (p_package_id in integer + ) return varchar + is + v_name cr_items.name%TYPE; + v_package_key apm_packages.package_key%TYPE; + v_id integer; + begin + select package_key into v_package_key from apm_packages + where package_id = p_package_id; + + select acs_object_id_seq.nextval into v_id from dual; + + -- Set the name + select v_package_key || '_' || + to_char(sysdate, 'YYYYMMDD') || '_' || + v_id into v_name + from dual; + + return v_name; + + end new_unique_name; +end pm_project; +/ +show errors + +---------------------------------- +-- Tasks +---------------------------------- + +-- When we created the acs object type above, we specified a +-- 'name_method'. This is the name of a function that will return the +-- name of the object. This is a convention ensuring that all objects +-- can be identified. Now we have to build that function. In this case, +-- we'll return a field called title as the name. +create or replace package pm_task +as + function name (p_pm_task_id in integer + ) return varchar2; + + function new_task_item ( + p_project_id in integer , + p_title in varchar2, + p_description in varchar2, + p_mime_type in varchar2, + p_end_date in date, + p_percent_complete in numeric, + p_estimated_hours_work in numeric, + p_estimated_hours_work_min in numeric, + p_estimated_hours_work_max in numeric, + p_status_id in integer, + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer; + + function new_task_revision ( + p_task_id in integer, + p_project_id in integer , + p_title in varchar2, + p_description in varchar2, + p_mime_type in varchar2, + p_end_date in date, + p_percent_complete in numeric, + p_estimated_hours_work in numeric, + p_estimated_hours_work_min in numeric, + p_estimated_hours_work_max in numeric, + p_actual_hours_worked in numeric, + p_status_id in integer, + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer; + + procedure delete_task_item (p_task_id in integer); + +end pm_task; +/ +show errors + + +create or replace package body pm_task +as + function name (p_pm_task_id in integer + ) return varchar2 + is + v_pm_task_name cr_items.name%TYPE; + begin + select i.name || '_' || p_pm_task_id into v_pm_task_name + from cr_items i + where i.item_id = p_pm_task_id; + + return v_pm_task_name; + end name; + + function new_task_item ( + p_project_id in integer , + p_title in varchar2, + p_description in varchar2, + p_mime_type in varchar2, + p_end_date in date, + p_percent_complete in numeric, + p_estimated_hours_work in numeric, + p_estimated_hours_work_min in numeric, + p_estimated_hours_work_max in numeric, + p_status_id in integer, + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer + is + v_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_id cr_items.item_id%TYPE; + v_task_number integer; + begin + select acs_object_id_seq.nextval into v_id from dual; + + -- We want to put the task under the project item + -- create the task_number + + v_item_id := content_item.new ( + name => v_id, + parent_id => p_project_id, + item_id => v_id, + locale => null, + creation_date => sysdate, + creation_user => p_creation_user, + context_id => p_package_id, + creation_ip => p_creation_ip, + item_subtype => 'content_item', + content_type => 'pm_task', + title => p_title, + description => p_description, + mime_type => p_mime_type, + nls_language => null, + data => null + ); + + v_revision_id := content_revision.new ( + title => p_title, + description => p_description, + publish_date => sysdate, + mime_type => p_mime_type, + nls_language => NULL, + data => NULL, + item_id => v_item_id, + revision_id => NULL, + creation_date => sysdate, + creation_user => p_creation_user, + creation_ip => p_creation_ip + ); + + content_item.set_live_revision (v_revision_id); + + insert into pm_tasks ( + task_id, task_number, status) + values ( + v_item_id, v_task_number, p_status_id); + + insert into pm_tasks_revisions ( + task_revision_id, end_date, percent_complete, estimated_hours_work, estimated_hours_work_min, estimated_hours_work_max, actual_hours_worked) + values ( + v_revision_id, p_end_date, p_percent_complete, p_estimated_hours_work, p_estimated_hours_work_min, p_estimated_hours_work_max, '0'); + + acs_permission.grant_permission( + v_revision_id, + p_creation_user, + 'admin' + ); + + return v_revision_id; + + end new_task_item; + + function new_task_revision ( + p_task_id in integer, + p_project_id in integer , + p_title in varchar2, + p_description in varchar2, + p_mime_type in varchar2, + p_end_date in date, + p_percent_complete in numeric, + p_estimated_hours_work in numeric, + p_estimated_hours_work_min in numeric, + p_estimated_hours_work_max in numeric, + p_actual_hours_worked in numeric, + p_status_id in integer, + p_creation_date in date default sysdate, + p_creation_user in integer, + p_creation_ip in varchar2, + p_package_id in integer + ) return integer + is + v_revision_id cr_revisions.revision_id%TYPE; + v_id cr_items.item_id%TYPE; + begin + select acs_object_id_seq.nextval into v_id from dual; + + -- We want to put the task under the project item + update cr_items + set parent_id = p_project_id + where item_id = p_task_id; + + v_revision_id := content_revision.new ( + title => p_title, + description => p_description, + publish_date => sysdate, + mime_type => p_mime_type, + nls_language => NULL, + data => NULL, + item_id => p_task_id, + revision_id => NULL, + creation_date => sysdate, + creation_user => p_creation_user, + creation_ip => p_creation_ip + ); + + content_item.set_live_revision (v_revision_id); + + insert into pm_tasks_revisions ( + task_revision_id, end_date, percent_complete, estimated_hours_work, estimated_hours_work_min, estimated_hours_work_max, actual_hours_worked) + values ( + v_revision_id, p_end_date, p_percent_complete, p_estimated_hours_work, p_estimated_hours_work_min, p_estimated_hours_work_max, p_actual_hours_worked); + + update pm_tasks + set status = p_status_id + where task_id = p_task_id; + + acs_permission.grant_permission( + v_revision_id, + p_creation_user, + 'admin' + ); + + return v_revision_id; + end new_task_revision; + + procedure delete_task_item (p_task_id in integer) + is + begin + delete from pm_tasks_revisions + where task_revision_id in (select revision_id from pm_tasks_revisionsx where item_id = p_task_id); + + delete from pm_tasks + where task_id = p_task_id; + + content_item.del(p_task_id); + end delete_task_item ; +end pm_task; +/ + +show errors + + + + Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-notifications-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-notifications-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-notifications-create.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,66 @@ +-- Following directions at +-- http://openacs.org/doc/openacs-HEAD/tutorial-notifications.html + +-- using pm_task_notif_type instead of lars_blogger_notif_type +-- using project-manager instead of lars-blogger + +declare + impl_id integer; + v_foo integer; +begin + + -- the notification type impl + impl_id := acs_sc_impl.new ( + impl_contract_name => 'NotificationType', + impl_name => 'pm_task_notif_type', + impl_owner_name => 'project-manager' + ); + + v_foo := acs_sc_impl_alias.new ( + impl_contract_name => 'NotificationType', + impl_name => 'pm_task_notif_type', + impl_operation_name => 'GetURL', + impl_alias => 'pm::task::get_url', + impl_pl => 'TCL' + ); + + v_foo := acs_sc_impl_alias.new ( + impl_contract_name => 'NotificationType', + impl_name => 'pm_task_notif_type', + impl_operation_name => 'ProcessReply', + impl_alias => 'pm::task::process_reply', + impl_pl => 'TCL' + ); + + acs_sc_binding.new ( + contract_name => 'NotificationType', + impl_name => 'pm_task_notif_type' + ); + + v_foo:= notification_type.new ( + type_id => NULL, + sc_impl_id => impl_id, + short_name => 'pm_task_notif', + pretty_name => 'Task Notification', + description => 'Notifications of task changes', + creation_date => sysdate , + creation_user => null, + creation_ip => null, + context_id => NULL + ); + + -- enable the various intervals and delivery methods + insert into notification_types_intervals + (type_id, interval_id) + select v_foo, interval_id + from notification_intervals where name in ('instant','hourly','daily'); + + insert into notification_types_del_methods + (type_id, delivery_method_id) + select v_foo, delivery_method_id + from notification_delivery_methods where short_name in ('email'); + +end; +/ + +show errors Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-notifications-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-notifications-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-notifications-drop.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,92 @@ +-- +-- Project Manager +-- +-- @author jader@bread.com +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with significant concepts and code +-- lifted from Gilbert's UBB forums. Thanks Orchard Labs. +-- Jade in turn lifted this from gwong and ben. +-- + +create function inline_0 () +returns integer as ' +declare + row record; +begin + for row in select nt.type_id + from notification_types nt + where nt.short_name in (''pm_task_notif'') + loop + perform notification_type__delete(row.type_id); + end loop; + + return null; +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0 (); + +-- +-- Service contract drop stuff was missing - Roberto Mello +-- + +create function inline_0() returns integer as ' +declare + impl_id integer; + v_foo integer; +begin + + -- the notification type impl + impl_id := acs_sc_impl__get_id ( + ''NotificationType'', -- impl_contract_name + ''pm_task_notif_type'' -- impl_name + ); + + PERFORM acs_sc_binding__delete ( + ''NotificationType'', + ''pm_task_notif_type'' + ); + + v_foo := acs_sc_impl_alias__delete ( + ''NotificationType'', -- impl_contract_name + ''pm_task_notif_type'', -- impl_name + ''GetURL'' -- impl_operation_name + ); + + v_foo := acs_sc_impl_alias__delete ( + ''NotificationType'', -- impl_contract_name + ''pm_task_notif_type'', -- impl_name + ''ProcessReply'' -- impl_operation_name + ); + + select into v_foo type_id + from notification_types + where sc_impl_id = impl_id + and short_name = ''pm_task_notif''; + + perform notification_type__delete (v_foo); + + delete from notification_types_intervals + where type_id = v_foo + and interval_id in ( + select interval_id + from notification_intervals + where name in (''instant'',''hourly'',''daily'') + ); + + delete from notification_types_del_methods + where type_id = v_foo + and delivery_method_id in ( + select delivery_method_id + from notification_delivery_methods + where short_name in (''email'') + ); + + return (0); +end; +' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); Index: openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-table-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/project-manager/sql/oracle/Attic/project-manager-table-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/project-manager/sql/oracle/project-manager-table-create.sql 26 Oct 2004 03:08:48 -0000 1.1.2.1 @@ -0,0 +1,638 @@ +-- packages/project-manager/sql/postgresql/project-manager-table-create.sql +-- +-- @author jader@bread.com +-- @author ncarroll@ee.usyd.edu.au was involved in creating the initial CR version +-- @author everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742 +-- @creation-date 2003-05-15 +-- + +-- PROJECTS + +create sequence pm_project_status_seq start with 3; + +create table pm_project_status ( + status_id integer + constraint pm_project_status_pk + primary key, + description varchar(100), + -- closed or open + status_type char(1) default 'c' + constraint pm_projects_status_type_ck + check (status_type in ('c','o')) +); + +insert into pm_project_status (status_id, description, status_type) values +(1, 'Open', 'o'); +insert into pm_project_status (status_id, description, status_type) values +(2, 'Closed', 'c'); + + +-- project revisions, items are kept in cr_items + +create table pm_projects ( + project_id integer + constraint pm_proj_rev_fk + references cr_revisions on delete cascade + constraint pm_proj_rev_pk + primary key, + -- a user-specified project code + project_code varchar(255), + goal varchar(4000), + planned_start_date date, + planned_end_date date, + actual_start_date date, + actual_end_date date, + status_id integer + constraint pm_projects_status_id_nn + not null + constraint pm_projects_status_id_fk + references pm_project_status, + -- if ongoing_p is true, then actual_end_date must be null + ongoing_p char(1) default 'f' + constraint pm_projects_ongoing_p_ck + check (ongoing_p in ('t','f')), + estimated_finish_date date, + earliest_finish_date date, + latest_finish_date date, + -- denormalized, taken from logger + actual_hours_completed numeric, + estimated_hours_total numeric, + -- The logger package keeps its own projects table + logger_project integer + constraint pm_projects_logger_pj_nn + not null + constraint pm_projects_logger_pj_fk + references logger_projects +); + +-- create type +begin + content_type.create_type ( + content_type => 'pm_project', + supertype => 'content_revision', + pretty_name => 'Project', + pretty_plural => 'Projects', + table_name => 'pm_projects', + id_column => 'project_id', + name_method => 'pm_project.name' + ); +end; +/ +show errors + +-- other fields are added in too. See the -custom script. + + +-- ROLES + +create sequence pm_role_seq start with 4; + +create table pm_roles ( + role_id integer + constraint pm_role_id_pk + primary key, + one_line varchar(100) + constraint pm_role_one_line_uq + unique, + description varchar(2000), + sort_order integer, + is_observer_p char(1) default 'f' + constraint pm_role_is_observer_ck + check (is_observer_p in ('t','f')) +); + + +comment on table pm_roles is ' + Roles represent the way in which a party participates in a project + or task. For example, they could be a manager, or client, or + participant.. The sort order determines what order it is displayed + in. The is_observer_p specifies whether they are directly + responsible for the task, or are just observers on it. +'; + +insert into pm_roles (role_id, one_line, description, sort_order) values ('1','Lead','Team members who are responsible for the completion of the project','10'); +insert into pm_roles (role_id, one_line, description, sort_order) values ('2','Player','A person on the team responsible for completion of the project','20'); +insert into pm_roles (role_id, one_line, description, sort_order, is_observer_p) values ('3','Watcher','A person interested in developments, possibly helping out on it.','30','t'); + + +create table pm_default_roles ( + role_id integer + constraint pm_default_role_fk + references pm_roles + on delete cascade, + party_id integer + constraint pm_default_role_party_fk + references parties(party_id) + on delete cascade, + constraint pm_default_roles_uq + unique (role_id, party_id) +); + +comment on table pm_default_roles is ' + Specifies what role a person is a part of by default +'; + +-- PROJECT ASSIGNMENT + +create table pm_project_assignment ( + project_id integer + constraint pm_proj_role_map_project_fk + references cr_items + on delete cascade, + role_id integer + constraint pm_project_role_map_role_fk + references pm_roles, + party_id integer + constraint pm_project_role_map_user_id_fk + references parties(party_id) + on delete cascade, + constraint pm_project_assignment_uq + unique (project_id, role_id, party_id) +); + + +comment on table pm_project_assignment is ' + Maps who is a part of what project, and in what capacity +'; + + +-- TASKS + +-- we create two tables to store task information +-- the information that we keep revisions on is in the +-- pm_task_revisions table, the rest is in pm_task + +create sequence pm_task_status_seq start with 3; + +create table pm_task_status ( + status_id integer + constraint pm_task_status_pk + primary key, + description varchar(100), + -- closed or open + status_type char(1) default 'c' + constraint pm_task_status_type_ck + check (status_type in ('c','o')) +); + +insert into pm_task_status (status_id, description, status_type) values +(1, 'Open', 'o'); +insert into pm_task_status (status_id, description, status_type) values +(2, 'Closed', 'c'); + + +create sequence pm_tasks_number_seq; + +create table pm_tasks ( + task_id integer + constraint pm_tasks_task_id_fk + references cr_items + on delete cascade + constraint pm_task_task_id_pk + primary key, + task_number integer, + status integer + constraint pm_tasks_task_status_fk + references pm_task_status, + deleted_p char(1) default 'f' + constraint pm_tasks_deleted_p_ck + check (deleted_p in ('t','f')) +); + +CREATE OR REPLACE view +pm_tasks_active as + SELECT task_id, task_number, status FROM pm_tasks where deleted_p = 'f'; + + +create table pm_tasks_revisions ( + task_revision_id integer + constraint pm_task_revs_id_fk + references cr_revisions + on delete cascade + constraint pm_task_revs_id_pk + primary key, + -- dates are optional, because it may be computed in reference + -- to all other items, or simply not have a deadline + end_date date, + -- keep track of completion status + percent_complete numeric + constraint pm_task_per_complete_gt_ck + check(percent_complete >= 0) + constraint pm_task_per_complete_lt_ck + check(percent_complete <= 100), + estimated_hours_work numeric, + -- PERT charts require minimum and maximum estimates + -- these are optionally used + estimated_hours_work_min numeric, + estimated_hours_work_max numeric, + -- this should be computed by checking with logger? The actual + -- data should be in logger, logged by who did it, when etc.. + -- or we can create a separate table to keep track of task hours + -- and make sure its data model is similar to logger? + actual_hours_worked numeric, + -- network diagram stuff, computed + earliest_start date, + earliest_finish date, + latest_start date, + latest_finish date +); + +-- create the content type +begin + content_type.create_type ( + content_type => 'pm_task', + supertype => 'content_revision', + pretty_name => 'Task', + pretty_plural => 'Tasks', + table_name => 'pm_tasks_revisions', + id_column => 'task_revision_id', + name_method => 'pm_task__name' + ); +end; +/ +show errors + +-- add in attributes + +declare + attribute_id integer; +begin + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'end_date', + datatype => 'date', + pretty_name => 'End date', + pretty_plural => 'End dates', + sort_order => null, + default_value => null, + column_spec => 'date' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'percent_complete', + datatype => 'number', + pretty_name => 'Percent complete', + pretty_plural => 'Percents complete', + sort_order => null, + default_value => null, + column_spec => 'numeric' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'estimated_hours_work', + datatype => 'number', + pretty_name => 'Estimated hours work', + pretty_plural => 'Estimated hours work', + sort_order => null, + default_value => null, + column_spec => 'numeric' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'estimated_hours_work_min', + datatype => 'number', + pretty_name => 'Estimated minimum hours', + pretty_plural => 'Estimated minimum hours', + sort_order => null, + default_value => null, + column_spec => 'numeric' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'estimated_hours_work_max', + datatype => 'number', + pretty_name => 'Estimated maximum hours', + pretty_plural => 'Estimated maximum hours', + sort_order => null, + default_value => null, + column_spec => 'numeric' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'actual_hours_worked', + datatype => 'number', + pretty_name => 'Actual hours worked', + pretty_plural => 'Actual hours worked', + sort_order => null, + default_value => null, + column_spec => 'numeric' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'earliest_start', + datatype => 'date', + pretty_name => 'Earliest start date', + pretty_plural => 'Earliest start dates', + sort_order => null, + default_value => null, + column_spec => 'date' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'earliest_finish', + datatype => 'date', + pretty_name => 'Earliest finish date', + pretty_plural => 'Earliest finish dates', + sort_order => null, + default_value => null, + column_spec => 'date' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'latest_start', + datatype => 'date', + pretty_name => 'Latest start date', + pretty_plural => 'Latest start dates', + sort_order => null, + default_value => null, + column_spec => 'date' + ); + + attribute_id := content_type.create_attribute ( + content_type => 'pm_task', + attribute_name => 'latest_finish', + datatype => 'date', + pretty_name => 'Latest finish date', + pretty_plural => 'Latest finish dates', + sort_order => null, + default_value => null, + column_spec => 'date' + ); +end; +/ + +show errors + +create table pm_task_logger_proj_map ( + task_item_id integer + constraint pm_task_log_proj_map_t_nn + not null + constraint pm_task_log_proj_map_t_fk + references pm_tasks + on delete cascade, + logger_entry integer + constraint pm_task_log_proj_map_l_nn + not null + constraint pm_task_log_proj_map_l_fk + references logger_entries + on delete cascade, + constraint pm_task_logger_proj_map_uq + unique (task_item_id, logger_entry) +); + + +-- DEPENDENCIES + +-- dependency types +-- such as: +-- cannot start until Task X finishes +-- cannot start until Task X begins +-- cannot finish until Task X finishes +-- cannot finish until Task X begins + +create table pm_task_dependency_types ( + short_name varchar(100) + constraint pm_task_const_sn_pk + primary key, + description varchar(1000) +); + +insert into pm_task_dependency_types (short_name, description) values ('start_before_start','Starts before this starts'); +insert into pm_task_dependency_types (short_name, description) values ('start_before_finish','Starts before this finishes'); +insert into pm_task_dependency_types (short_name, description) values ('finish_before_start','Finishes before this starts'); +insert into pm_task_dependency_types (short_name, description) values ('finish_before_finish','Finishes before this finishes'); + + +create sequence pm_task_dependency_seq; + +create table pm_task_dependency ( + dependency_id integer + constraint pm_task_const_id_pk + primary key, + task_id integer + constraint pm_task_const_task_id_nn + not null + constraint pm_task_const_task_id_fk + references pm_tasks + on delete cascade, + parent_task_id integer + constraint pm_tasks_const_parent_id_nn + not null + constraint pm_tasks_const_parent_id_fk + references pm_tasks + on delete cascade, + dependency_type varchar(100) + constraint pm_tasks_const_type_nn + not null + constraint pm_tasks_const_type_fk + references pm_task_dependency_types, + constraint pm_task_dependency_uq unique (task_id, parent_task_id) +); + + +-- WORKGROUPS + +create sequence pm_workgroup_seq; + +create table pm_workgroup ( + workgroup_id integer + constraint pm_workgroup_id_pk + primary key, + one_line varchar(100) + constraint pm_workgroup_one_line_uq + unique, + description varchar(2000), + sort_order integer +); + +create table pm_workgroup_parties ( + workgroup_id integer + constraint pm_workgroup_parties_wg_id_fk + references pm_workgroup(workgroup_id) + on delete cascade, + party_id integer + constraint pm_workgroup_party_fk + references parties(party_id) + on delete cascade, + role_id integer + constraint pm_workgroup_role_id + references pm_roles, + constraint pm_workgroup_parties_uq + unique (workgroup_id, party_id, role_id) +); + + +-- TASK ASSIGNMENTS + +create table pm_task_assignment ( + task_id integer + constraint pm_task_assignment_task_fk + references pm_tasks(task_id) + on delete cascade, + role_id integer + constraint pm_task_assignment_role_fk + references pm_roles, + party_id integer + constraint pm_task_assignment_party_fk + references parties(party_id) + on delete cascade, + constraint pm_task_assignment_uq + unique (task_id, role_id, party_id) +); + + +comment on table pm_task_assignment is ' + Maps who is a part of what task, and in what capacity +'; + +create table pm_task_xref ( + task_id_1 integer + constraint pm_task_xref_task1_nn + not null + constraint pm_task_xref_task1_fk + references pm_tasks(task_id) + on delete cascade, + task_id_2 integer + constraint pm_task_xref_task2_nn + not null + constraint pm_task_xref_task2_fk + references pm_tasks(task_id) + on delete cascade, + constraint pm_task_xref_lt check (task_id_1 < task_id_2) +); + +comment on table pm_task_xref is ' + Maps related tasks. +'; + + +-- PROCESSES + +create sequence pm_process_seq; + +create table pm_process ( + process_id integer + constraint pm_process_id_pk + primary key, + one_line varchar(200) + constraint pm_process_one_line_nn + not null, + description varchar(1000), + party_id integer + constraint pm_process_party_fk + references parties + constraint pm_process_party_nn + not null, + creation_date date +); + +comment on table pm_process is ' + Processes are a set of templates for tasks, so that people can + create sets of tasks quickly. Their structure needs to match that of + tasks. The process holds the meta information, and is also an identifier + that is used by the user to select which process they''d like to copy or + use +'; + +create sequence pm_process_task_seq; + +create table pm_process_task ( + process_task_id integer + constraint pm_process_task_id_pk + primary key, + process_id integer + constraint pm_process_process_id_fk + references + pm_process + constraint pm_process_process_id_nn + not null, + one_line varchar(200) + constraint pm_process_task_one_line_nn + not null, + description varchar(4000), + -- dates are optional, because it may be computed in reference + -- to all other items, or simply not have a deadline + -- percent complete is always 0 + estimated_hours_work numeric, + -- PERT charts require minimum and maximum estimates + -- these are optionally used + estimated_hours_work_min numeric, + estimated_hours_work_max numeric, + ordering integer +); + +comment on table pm_process_task is ' + A template for the tasks that will be created by the process +'; + +create sequence pm_process_task_dependency_seq; + +create table pm_process_task_dependency ( + dependency_id integer + constraint pm_proc_task_dependcy_pk + primary key, + process_task_id integer + constraint pm_proc_task_proc_task_fk + references pm_process_task + on delete cascade, + parent_task_id integer + constraint pm_proc_task_parent_id_fk + references pm_process_task + on delete cascade, + dependency_type varchar(100) + constraint pm_process_task_dep_type + references pm_task_dependency_types, + constraint pm_proc_task_depend_uq + unique (process_task_id, parent_task_id) +); + +comment on table pm_process_task_dependency is ' + Keeps track of dependencies. Used to create the dependencies in the + new tasks. +'; + +create table pm_process_task_assignment ( + process_task_id integer + constraint pm_proc_task_assign_task_fk + references pm_process_task(process_task_id) + on delete cascade, + role_id integer + constraint pm_proc_task_assign_role_fk + references pm_roles, + party_id integer + constraint pm_proc_task_assign_party_fk + references parties(party_id) + on delete cascade, + constraint pm_proc_task_assgn_uq + unique (process_task_id, role_id, party_id) +); + + +comment on table pm_process_task_assignment is ' + Maps who is assigned to process tasks. These will be the default people + assigned to the new tasks +'; + +create table pm_users_viewed ( + viewing_user integer constraint + pm_usrs_viewed_viewing_user_fk + references parties, + viewed_user integer constraint + pm_usrs_viewed_viewed_user_fk + references parties +); + +comment on table pm_users_viewed is ' + Used to keep track of what users to see on the task calendar and other + views. +'; + +@@project-manager-custom-create.sql