Forum OpenACS Development: Re: dotWRK: Project Manager

This maybe too late of €0.02, but I would not think that using BT (at least in the current form) is a good idea for the PM. For one, BT does not support nesting of tickets -- with any effective project management tool you do need to be able to nest tasks -- have suntasks of tasks of subtasks of subprojects, etc...

Can't really comment much on using categories as project-holders -- maybe it is possible with the new global categories package -- have not tried/seen it yet. In this case one could (in theory) structure projects as sub-types of categories, then have BT handle individual tasks -- this would still be a limitted way of doing it, though. Projects and sub-projects can be handled this way, but not tasks and subtasks.

When I was thinking some time back about a PM approach (before dotWRK work started -- at some point I tried to port project-tracker to OACS), I was thinking about roughly the following structure:

• Each item in PM is a task. Projects are also tasks. Have a single tasks table to hold all basic task data for all projects (descriptions, dates, reference to projects they belong to, etc.)
• Projects are simply tasks promoted to projects type. Keep them in a separate projects table, with reference to parent_project.
• Projects differ from tasks in ways that you can assigne pools of resources to them. These could probably be defined through acs_rels.
• You can't also flag a project complete unless all tasks are completed under it.
• Tasks under a given project can only be assigned to project's resources pool.
• Project-level tasks also define/own calendars, but those may have to be also resource-specific.
• In a very primitive sense (and that is where I've stopped -- had not had time to move any further), a data model could be as follows:

  -- knowledge-level
  create or replace function inline_0 ()
  returns integer as '
  begin
      PERFORM acs_object_type__create_type (
          ''pm_task'',                            -- object_type
          ''Project Manager Task'',               -- pretty_name
          ''Project Manager Tasks'',              -- pretty_plural
          ''acs_object'',                         -- supertype
          ''pm_tasks'',                           -- table_name
          ''task_id'',                            -- id_column
          null,                                   -- package_name
          ''f'',                                  -- abstract_p
          null,                                   -- type_extension_table
          ''pm_task__name''                       -- name_method
          );
  
      PERFORM acs_object_type__create_type (
          ''pm_project'',                         -- object_type
          ''Project Manager Project'',            -- pretty_name
          ''Project Manager Projects'',           -- pretty_plural
          ''pm_task'',                            -- supertype
          ''pm_project'',                         -- table_name
          ''task_id'',                            -- id_column
          null,                                   -- package_name
          ''f'',                                  -- abstract_p
          null,                                   -- type_extension_table
          ''pm_project__name''                    -- name_method
          );
      return 0;
  end;' language 'plpgsql';
  select inline_0 ();
  drop function inline_0 ();
  
  -- tasks: everything is a task, even a project itself
  create table pm_tasks (
    task_id             integer not null
                        constraint pm_tasks_fk1 refernces acs_objects,
                        constraint pm_tasks_pk primary key,
    task_name           varchar(255),
    task_description    text,
    date_start          date,
    date_end            date,
    project_id          integer not null
                        constraint pm_tasks_fk2 refernces pm_projects,
    is_project_p        boolean default 'false'
  );
  
  -- projects: some tasks can be promoted to be projects
  -- (or sub-projects of existing projects).
  -- they will then get resource pools allocated to them.
  create table pm_projects (
    project_id          integer not null
                        constraint pm_projects_fk1 references pm_tasks
                        constraint pm_projects_pk primary key,
    parent_id           integer not null
                        constraint pm_projects_fk2 references pm_projeects
  );
  
  -- resource pools should be created in Tcl
  
  -- resource assignments
  create table pm_task_rc_map (
    task_id               integer not null
                          constraint pm_task_rc_map_fk1 refernces pm_tasks,
    rc_id                 integer not null
                          constraint pm_task_rc_map_fk2 refernces parties,
                          -- a resource can be assigned to a task only once
                          constraint pm_task_rc_map_un unique(task_id,rc_id)
  );
  
  -- functions
  create or replace function pm_task__name (integer -- task_id)
  returns varchar as '
          declare
                  p_task_id       alias for $1;
                  v_task_name     pm_tasks.task_name%TYPE;
          begin
                  select task_name into v_task_name
                  from pm_tasks
                  where task_id = p_task_id;
                  if not found
                          raise exception ''Task ID % does not exist in database!'', p_task_id;
                  end if;
                  return v_task_name;
          end;' language 'plpgsql';
  
  create or replace function pm_project__name (integer -- task_id)
  returns varchar as '
          declare
                  p_project_id       alias for $1;
                  v_project_name     pm_tasks.task_name%TYPE;
          begin
                  v_project_name := pm_task__name(p_project_id);
                  return v_project_name;
          end;' language 'plpgsql';
  
  create or replace function pm_task__promote2project (integer -- task_id)
  returns integer as '
          decalre
                  p_task_id       alias for $1;
                  v_parent_project_id     pm_tasks.project_id%TYPE;
          begin
                  select project_id into v_parent_project_id
                  from pm_tasks where task_id = p_task_id;
  
                  insert into pm_projects
                  values (p_task_id,v_parent_project_id);
  
                  update pm_task set is_project_p = ''true''
                  where task_id = p_task_id;
  
                  return 0;
  
          end;' language 'plpgsql';
  
  create or replace function pm_task__demote2task (integer -- task_id)
  returns integer as '
          decalre
                  p_project_id       alias for $1;
          begin
  
                  -- resource pools should first be de-allocated
                  -- TBD
  
                  -- removing an entry from projects table
                  delete from pm_projects
                  where project_id = p_task_id;
  
                  -- now changing the flags in tasks table
                  update pm_task set is_project_p = ''false''
                  where task_id = p_task_id;
  
                  return 0;
  
          end;' language 'plpgsql';