Forum OpenACS Development: Re: dotWRK: Project Manager
-- Project repository datamodel.
create table projects (
project_id integer
constraint projects_id_fk
references cr_revisions on delete cascade
constraint projects_id_pk
primary key,
owner_id integer
constraint projects_owner_id_fk
references users(user_id),
project_name varchar(250),
author varchar,
category varchar,
company varchar,
status_date DATE,
manager varchar(250),
subject varchar,
hours_per_day integer, -- 0 to 24
days_per_month integer, -- 0 to 32
week_start_day integer, -- 0 to 6
year_start_month integer, -- 0 to 12
hours_per_week integer, -- 0 to 168
planned_start DATE,
planned_finish DATE,
planned_duration integer,
planned_work integer,
planned_cost integer,
actual_start DATE,
actual_finish DATE,
actual_duration integer,
actual_work integer,
actual_cost integer,
baseline_start DATE,
baseline_finish DATE,
baseline_duration integer,
baseline_work integer,
baseline_cost integer,
remaining_duration integer,
remaining_work integer,
remaining_cost integer,
acwp integer,
bcwp integer,
bcws integer,
start_variance integer,
finish_variance integer,
cost_variance integer,
early_start DATE,
early_finish DATE,
late_start DATE,
late_finish DATE,
total_slack integer,
free_slack integer
);
-- Procedure new project
create function project__new (varchar, integer, integer, varchar, timestamptz,
integer, integer, varchar, varchar, varchar,
varchar, varchar, varchar, varchar, varchar,
varchar, boolean)
returns integer as '
declare
p_project_name alias for $1;
p_parent_id alias for $2;
p_item_id alias for $3;
p_locale alias for $4;
p_creation_date alias for $5;
p_creation_user alias for $6;
p_context_id alias for $7;
p_creation_ip alias for $8;
p_item_subtype alias for $9;
p_content_type alias for $10;
p_title alias for $11;
p_description alias for $12;
p_mime_type alias for $13;
p_nls_language alias for $14;
p_text alias for $15;
p_storage_type alias for $16;
p_is_live alias for $17;
v_item_id integer;
v_revision_id integer;
begin
v_item_id := content_item__new (
p_project_name, -- name
p_parent_id, -- parent_id default null ->null
p_item_id, -- item_id default null ->null
p_locale, -- locale default null ->null
p_creation_date, -- creation_date default now ->now
p_creation_user, -- creation_user default null
p_context_id, -- context_id default null
p_creation_ip, -- creation_ip default null
''content_item'', -- item_subtype default ''content_item''
''project'', -- content_type default ''content_revision''
p_title, -- title default null ->null
p_description, -- description default null ->null
p_mime_type, -- mime_type default ''text/plain'' ->null
p_nls_language, -- nls_language default null ->null
p_text, -- text default null ->null
p_storage_type -- storage_type ->text
);
v_revision_id := content_revision__new (
p_title, -- title
p_description, -- description
p_creation_date, -- publish_date
p_mime_type, -- mime_type
p_nls_language, -- nls_language ->null
NULL, -- data
v_item_id, -- item_id
NULL, -- revision_id
p_creation_date, -- creation_date
p_creation_user, -- creation_user
p_creation_ip -- creation_ip
);
insert into projects (
project_id, owner_id, project_name
) values (
v_revision_id, p_creation_user, p_project_name
);
if p_is_live = ''t'' then
PERFORM content_item__set_live_revision (v_revision_id);
end if;
-- Change admin to manager?
PERFORM acs_permission__grant_permission (
v_revision_id,
p_owner_id,
''admin''
);
return v_item_id;
end;
' language 'plpgsql';
Should have posted it up earlier, but have been busy with other projects. The datamodel isn't complete, which is why I haven't released anything. But I guess some feedback would be useful before I make any further progress.
Cheers,
Nick.
- You define a status_date but not a status. Is there a reason ?
- You define manager as varchar. Shouldn't the manager of a project be part of the system and therefore reference the user.
- Who is the owner of a project vs. the author, vs. the manager
- Categories should use and reference the category package
- Company should reference a seperate company table, which includes more information about this company (e.g. contact information). Good question whether we would like to replace company with sponsor or external_contact, so a company can have multiple sponsors (e.g. if you have multiple projects with one company, but different departments and project leaders on the other end).
- The hours_per_day aso. asf. are unique to a company. At least I haven't found one where one project starts on a monday and another one on saturday, on a regular basis. But maybe I do not understand what these fields are used for.
- My gut feeling is to have a seperate timing table which has the "start, finish, duration, work (what is this), cost as well as type (planned, actual, baseline [what do we need this for]) und project_id"
- How do you define remaining work. Can't we calculate this from somewhere else ?
- I'm sure we can calculate the variances, as well as early and late start/finish.
In any case, thanks for starting this work, looking forward to see more.
