-- 
-- Upgrade script
-- 
-- @author  (alexk@bread.com)
-- @creation-date 2005-10-24
-- @arch-tag: cf494b6b-fd68-4cc8-9516-7f7f091c4f65
-- @cvs-id $Id: upgrade-3.1d11-3.1d12.sql,v 1.2 2006/05/10 20:35:06 maltes Exp $
--

select content_type__create_attribute (
    'pm_project', -- content_type
    'dform', -- attribute_name
    'string',     -- datatype
    'dform', -- pretty_name
    'dform', -- pretty_plural
    null, -- sort_order
    null, -- default value
    'varchar(100)' -- column_spec
);

select content_type__create_attribute (
    'pm_task', -- content_type
    'dform', -- attribute_name
    'string',     -- datatype
    'dform', -- pretty_name
    'dform', -- pretty_plural
    null, -- sort_order
    null, -- default value
    'varchar(100)' -- column_spec
);

select acs_rel_type__create_type (
      'application_link',
      '#Application_Link#',
      '#Application_Links#',
      'relationship',
      'apm_package',
      'package_id',
      'application_link_rel',
      'acs_object',
      'user',
      1,
      1,
      'acs_object',
      'user',
      1,
      1
);

select acs_rel_type__create_type (
      'application_data_link',
      '#Application_Data_Link#',
      '#Application_Data_Links#',
      'relationship',
      'apm_package_rel',
      'package_id',
      'application_data_link_rel',
      'acs_object',
      'user',
      1,
      1,
      'acs_object',
      'user',
      1,
      1
);

create function inline_0 ()
returns integer as '
DECLARE
        lp RECORD;
BEGIN
        FOR lp IN
           SELECT distinct(item_id), logger_project
           FROM pm_projectsx where logger_project IS NOT NULL 
        LOOP
        raise NOTICE ''item_id (%) | logger_project (%)'', lp.item_id, lp.logger_project;
        perform acs_rel__new (
                         null,
                         ''application_data_link'',
                         lp.item_id,
                         lp.logger_project,
                         lp.item_id,
                         null,
                         null
        );

        perform acs_rel__new (
                         null,
                         ''application_data_link'',
                         lp.logger_project,
                         lp.item_id,
                         lp.item_id,
                         null,
                         null
        );

        END LOOP;
        return 0;
END;
' language 'plpgsql';

select inline_0 ();
drop function inline_0 ();

-- Create logger task links
create function inline_0 ()
returns integer as '
declare
    ct RECORD;
begin
  for ct in select task_item_id, logger_entry
	from pm_task_logger_proj_map
  loop
        perform acs_rel__new (
                         null,
                         ''application_data_link'',
                         ct.task_item_id,
                         ct.logger_entry,
                         ct.task_item_id,
                         null,
                         null
        );

        perform acs_rel__new (
                         null,
                         ''application_data_link'',
                         ct.logger_entry,
                         ct.task_item_id,
                         ct.task_item_id,
                         null,
                         null
        );
  end loop;

  return null;
end;' language 'plpgsql';

select inline_0();
drop function inline_0();

drop table pm_task_logger_proj_map;

alter table pm_projects drop column logger_project cascade;

alter table pm_projects alter column dform set default 'implicit';
update pm_projects set dform = 'implicit';

alter table pm_tasks_revisions alter column dform set default 'implicit';
update pm_tasks_revisions set dform = 'implicit';

drop view pm_tasks_revisionsx;

create or replace view pm_tasks_revisionsx as
 SELECT acs_objects.object_id, acs_objects.object_type, acs_objects.context_id, acs_objects.security_inherit_p, acs_objects.creation_user, acs_objects.creation_date, acs_objects.creation_ip, acs_objects.last_modified, acs_objects.modifying_user, acs_objects.modifying_ip, acs_objects.tree_sortkey, acs_objects.max_child_sortkey, cr.revision_id, cr.title, cr.item_id, cr.description, cr.publish_date, cr.mime_type, cr.nls_language, i.name, i.parent_id, pm_tasks_revisions.task_revision_id, pm_tasks_revisions.end_date, pm_tasks_revisions.percent_complete, pm_tasks_revisions.estimated_hours_work, pm_tasks_revisions.estimated_hours_work_min, pm_tasks_revisions.estimated_hours_work_max, pm_tasks_revisions.actual_hours_worked, pm_tasks_revisions.earliest_start, pm_tasks_revisions.earliest_finish, pm_tasks_revisions.latest_start, pm_tasks_revisions.latest_finish, pm_tasks_revisions.priority, pm_tasks_revisions.dform
  FROM acs_objects, cr_revisions cr, cr_items i, cr_text, pm_tasks_revisions
  WHERE acs_objects.object_id = cr.revision_id AND cr.item_id = i.item_id AND acs_objects.object_id = pm_tasks_revisions.task_revision_id;


select define_function_args('pm_project__new_project_item', 'project_name, project_code, parent_id, goal, description, mime_type, planned_start_date, planned_end_date, actual_start_date, actual_end_date, ongoing_p, status_id, customer_id, dform, creation_date, creation_user, creation_ip, package_id');

create or replace function pm_project__new_project_item (
        varchar,        -- project_name
        varchar,        -- project_code
        integer,        -- parent_id
        varchar,        -- goal
        varchar,        -- description
        varchar,        -- mime_type
        timestamptz,    -- planned_start_date
        timestamptz,    -- planned_end_date
        timestamptz,    -- actual_start_date
        timestamptz,    -- actual_end_date
        char(1),        -- ongoing_p
        integer,        -- status_id
        integer,        -- customer_id (organization_id)
        varchar,        -- dform
        timestamptz,    -- creation_date
        integer,        -- creation_user
        varchar,        -- creation_ip
        integer         -- package_id
) returns integer 
as '
declare
        p_project_name                          alias for $1;
        p_project_code                          alias for $2;
        p_parent_id                             alias for $3;
        p_goal                                  alias for $4;
        p_description                           alias for $5;
        p_mime_type                             alias for $6;
        p_planned_start_date                    alias for $7;
        p_planned_end_date                      alias for $8;
        p_actual_start_date                     alias for $9;
        p_actual_end_date                       alias for $10;
        p_ongoing_p                             alias for $11;
        p_status_id                             alias for $12;
        p_customer_id                           alias for $13;
        p_dform                                 alias for $14;
        p_creation_date                         alias for $15;
        p_creation_user                         alias for $16;
        p_creation_ip                           alias for $17;
        p_package_id                            alias for $18;

        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 := pm_project__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 (
                v_id::varchar,          -- name
                v_parent_id,            -- parent_id
                v_id,                   -- item_id
                null,                   -- locale
                now(),                  -- creation_date
                p_creation_user,        -- creation_user
                p_parent_id,            -- context_id
                p_creation_ip,          -- creation_ip
                ''pm_project'',         -- item_subtype
                ''pm_project'',         -- content_type
                p_project_name,         -- title
                p_description,          -- description
                p_mime_type,            -- mime_type
                null,                   -- nls_language
                null                    -- data
        );

        v_revision_id := content_revision__new (
                p_project_name,         -- title
                p_description,          -- description
                now(),                  -- publish_date
                p_mime_type,            -- mime_type
                NULL,                   -- nls_language
                NULL,                   -- data
                v_item_id,              -- item_id
                NULL,                   -- revision_id
                now(),                  -- creation_date
                p_creation_user,        -- creation_user
                p_creation_ip           -- creation_ip
        );

        PERFORM 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, 
                ongoing_p, estimated_finish_date, 
                earliest_finish_date, latest_finish_date,
                actual_hours_completed, 
                estimated_hours_total, status_id, customer_id, dform)
        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_ongoing_p, 
                p_planned_end_date,
                p_planned_end_date, p_planned_end_date, ''0'',
                ''0'', p_status_id, p_customer_id, p_dform
                );

        PERFORM acs_permission__grant_permission(
                v_revision_id,
                p_creation_user,
                ''admin''
        );

        return v_revision_id;
end;' language 'plpgsql';

select define_function_args('pm_project__new_project_revision', 'item_id, project_name, project_code, parent_id, goal, description, planned_start_date, planned_end_date, actual_start_date, actual_end_date, ongoing_p, status_id, organization_id, dform, creation_date, creation_user, creation_ip, package_id');

create or replace function pm_project__new_project_revision (
        integer,        -- item_id
        varchar,        -- project_name
        varchar,        -- project_code
        integer,        -- parent_id
        varchar,        -- goal
        varchar,        -- description
        timestamptz,    -- planned_start_date
        timestamptz,    -- planned_end_date
        timestamptz,    -- actual_start_date
        timestamptz,    -- actual_end_date
        char(1),        -- ongoing_p
        integer,        -- status_id
        integer,        -- organization_id (customer)
        varchar,        -- dform
        timestamptz,    -- creation_date
        integer,        -- creation_user
        varchar,        -- creation_ip
        integer         -- package_id
) returns integer 
as '
declare
        p_item_id                               alias for $1;
        p_project_name                          alias for $2;
        p_project_code                          alias for $3;
        p_parent_id                             alias for $4;
        p_goal                                  alias for $5;
        p_description                           alias for $6;
        p_planned_start_date                    alias for $7;
        p_planned_end_date                      alias for $8;
        p_actual_start_date                     alias for $9;
        p_actual_end_date                       alias for $10;
        p_ongoing_p                             alias for $11;
        p_status_id                             alias for $12;
        p_customer_id                           alias for $13;
        p_dform                                 alias for $14;
        p_creation_date                         alias for $15;
        p_creation_user                         alias for $16;
        p_creation_ip                           alias for $17;
        p_package_id                            alias for $18;

        v_revision_id           cr_revisions.revision_id%TYPE;
begin

        -- the item_id is the project_id

        v_revision_id := content_revision__new (
                p_project_name,         -- title
                p_description,          -- description
                now(),                  -- publish_date
                ''text/plain'',         -- mime_type
                NULL,                   -- nls_language
                NULL,                   -- data
                p_item_id,              -- item_id
                NULL,                   -- revision_id
                now(),                  -- creation_date
                p_creation_user,        -- creation_user
                p_creation_ip           -- creation_ip
        );

        PERFORM 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, 
                ongoing_p, status_id, customer_id, dform)
        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_ongoing_p, p_status_id, p_customer_id, p_dform);

        PERFORM acs_permission__grant_permission(
                v_revision_id,
                p_creation_user,
                ''admin''
        );

        return v_revision_id;
end;' language 'plpgsql';

select define_function_args('pm_task__new_task_revision', 'task_id, project_id, title, description, mime_type, end_date, percent_complete, estimated_hours_work, estimated_hours_work_min, estimated_hours_work_max, actual_hours_worked, status_id, dform, creation_date, creation_user, creation_ip, package_id, priority');

create or replace function pm_task__new_task_revision (
        integer,        -- task_id (the item_id)
        integer,        -- project_id
        varchar,        -- title
        varchar,        -- description
        varchar,        -- mime_type
        timestamptz,    -- end_date
        numeric,        -- percent_complete
        numeric,        -- estimated_hours_work
        numeric,        -- estimated_hours_work_min
        numeric,        -- estimated_hours_work_max
        numeric,        -- actual_hours_worked
        integer,        -- status_id
        varchar,        -- dform
        timestamptz,    -- creation_date
        integer,        -- creation_user
        varchar,        -- creation_ip
        integer,        -- package_id
	integer		-- priority
) returns integer 
as '
declare
        p_task_id                               alias for $1;
        p_project_id                            alias for $2;
        p_title                                 alias for $3;
        p_description                           alias for $4;
        p_mime_type                             alias for $5;
        p_end_date                              alias for $6;
        p_percent_complete                      alias for $7;
        p_estimated_hours_work                  alias for $8;
        p_estimated_hours_work_min              alias for $9;
        p_estimated_hours_work_max              alias for $10;
        p_actual_hours_worked                   alias for $11;
        p_status_id                             alias for $12;
        p_dform                                 alias for $13;
        p_creation_date                         alias for $14;
        p_creation_user                         alias for $15;
        p_creation_ip                           alias for $16;
        p_package_id                            alias for $17;
        p_priority                              alias for $18;
        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 (
                p_title,                -- title
                p_description,          -- description
                now(),                  -- publish_date
                p_mime_type,            -- mime_type
                NULL,                   -- nls_language
                NULL,                   -- data
                p_task_id,              -- item_id
                NULL,                   -- revision_id
                now(),                  -- creation_date
                p_creation_user,        -- creation_user
                p_creation_ip           -- creation_ip
        );

        PERFORM 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, priority, dform)
        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, p_priority, p_dform);

        update pm_tasks set status = p_status_id where task_id = p_task_id;

        PERFORM acs_permission__grant_permission(
                v_revision_id,
                p_creation_user,
                ''admin''
        );

        return v_revision_id;
end;' language 'plpgsql';

select define_function_args('pm_task__new_task_item', 'project_id, title, description, html_p, end_date, percent_complete, estimated_hours_work, estimated_hours_work_min, estimated_hours_work_max, status_id, process_instance_id, dform, creation_date, creation_user, creation_ip, package_id, priority, task_id');

create or replace function pm_task__new_task_item (
        integer,        -- project_id
        varchar,        -- title
        varchar,        -- description
        varchar,        -- html_p
        timestamptz,    -- end_date
        numeric,        -- percent_complete
        numeric,        -- estimated_hours_work
        numeric,        -- estimated_hours_work_min
        numeric,        -- estimated_hours_work_max,
        integer,        -- status_id
        integer,        -- process_instance_id
        varchar,        -- dform
        timestamptz,    -- creation_date
        integer,        -- creation_user
        varchar,        -- creation_ip
        integer,        -- package_id
	integer,        -- priority
        integer         -- task_id
) returns integer 
as '
declare
        p_project_id                            alias for $1;
        p_title                                 alias for $2;
        p_description                           alias for $3;
        p_mime_type                             alias for $4;
        p_end_date                              alias for $5;
        p_percent_complete                      alias for $6;
        p_estimated_hours_work                  alias for $7;
        p_estimated_hours_work_min              alias for $8;
        p_estimated_hours_work_max              alias for $9;
        p_status_id                             alias for $10;
        p_process_instance_id                   alias for $11;
        p_dform                                 alias for $12;
        p_creation_date                         alias for $13;
        p_creation_user                         alias for $14;
        p_creation_ip                           alias for $15;
        p_package_id                            alias for $16;
        p_priority                              alias for $17;
        p_task_id                               alias for $18;

        v_item_id               cr_items.item_id%TYPE;
        v_id                    cr_items.item_id%TYPE;
        v_revision_id           cr_revisions.revision_id%TYPE;
        v_task_number           integer;
begin
        -- We want to put the task under the project item

        -- create the task_number
        if p_task_id is null then
           select acs_object_id_seq.nextval into v_id from dual;
        else
           v_id := p_task_id;
        end if;

        v_item_id := content_item__new (
                v_id::varchar,          -- name
                p_project_id,           -- parent_id
                v_id,                   -- item_id
                null,                   -- locale
                now(),                  -- creation_date
                p_creation_user,        -- creation_user
                p_package_id,           -- context_id
                p_creation_ip,          -- creation_ip
                ''pm_task'',            -- item_subtype
                ''pm_task'',            -- content_type
                p_title,                -- title
                p_description,          -- description
                p_mime_type,            -- mime_type
                null,                   -- nls_language
                null,                   -- data
                p_package_id            -- package_id
        );

        v_revision_id := content_revision__new (
                p_title,                -- title
                p_description,          -- description
                now(),                  -- publish_date
                p_mime_type,            -- mime_type
                NULL,                   -- nls_language
                NULL,                   -- data
                v_item_id,              -- item_id
                NULL,                   -- revision_id
                now(),                  -- creation_date
                p_creation_user,        -- creation_user
                p_creation_ip,          -- creation_ip
                p_package_id            -- package_id
        );

        PERFORM content_item__set_live_revision (v_revision_id);

        insert into pm_tasks (
                task_id, task_number, status, process_instance)
        values (
                v_item_id, v_task_number, p_status_id, p_process_instance_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, priority, dform)
        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'', p_priority, p_dform);

        update acs_objects set context_id = p_project_id where object_id = v_item_id;

        PERFORM acs_permission__grant_permission(
                v_revision_id,
                p_creation_user,
                ''admin''
        );

        return v_revision_id;
end;' language 'plpgsql';

select content_type__refresh_view('pm_project');

-- Make sure the upgrade to acs_data_links got through

insert into acs_data_links
(select - rel_id as rel_id, object_id_one, object_id_two
 from acs_rels
 where rel_type = 'application_data_link');
