OpenACS

alexk
committed
MAIN:alexk:20050929172850
on 29 Sep 05
Fixing contacts master link and adding contacts dependency
openacs-4/.../postgresql/tasks-create.sql (+380 -5)
1 1 -----------------------------------------------------
2 2 --
3 3 -- Create the data model for the timecard application
4 4 -- Author: Matthew Geddert geddert@yahoo.com
5 5 -- Creation Date: 2004-02-16
6 6 --
7 7 -----------------------------------------------------
8 8
9   create table tasks_pm_process_task (
  9 create table t_processes (
  10         process_id                      integer
  11                                         constraint t_process_id_pk
  12                                         primary key
  13                                         constraint t_process_id_fk
  14                                         references acs_objects,
  15         title                           varchar(1000),
  16         description                     text,
  17         mime_type                       varchar(200) default 'text/plain',
  18         workflow_id                     integer
  19                                         constraint t_process_workflow_fk
  20                                         references workflows
  21 );
  22
  23 create table t_process_instances (
  24         process_instance_id             integer
  25                                         constraint t_process_instances_id_pk
  26                                         primary key
  27                                         constraint t_process_instances_id_fk
  28                                         references acs_objects,
  29         process_id                      integer
  30                                         constraint t_process_instances_process_fk
  31                                         references t_processes,
  32         case_id                         integer,
  33         party_id                        integer
  34                                         constraint t_process_instances_party_fk
  35                                         references parties,
  36         object_id                       integer
  37                                         constraint t_process_instances_tasks_object_fk
  38                                         references acs_objects
  39 );
  40
  41
  42 create sequence t_task_status_seq start 3;
  43
  44 create table t_task_status (
  45         status_id               integer
  46                                 constraint t_task_status_pk
  47                                 primary key,
  48         short_name              varchar(100),
  49         title                   varchar(100),
  50         -- closed or open
  51         status_type             char(1) default 'c'
  52                                 constraint t_task_status_type_ck
  53                                 check (status_type in ('c', 'o'))
  54 );
  55
  56 insert into t_task_status (status_id, short_name, title, status_type) values
  57 (1, 'open', '#acs-kernel.common_Open#', 'o');
  58 insert into t_task_status (status_id, short_name, title, status_type) values
  59 (2, 'closed', '#acs-kernel.common_Closed#', 'c');
  60
  61
  62 create table t_process_tasks (
  63         task_id                 integer
  64                                 constraint t_process_tasks_task_pk
  65                                 primary key
  66                                 constraint t_process_tasks_task_fk
  67                                 references acs_objects,
  68         process_id              integer
  69                                 constraint t_process_tasks_process_fk
  70                                 references t_processes,
  71         -- action creating this task
  72         open_action_id          integer
  73                                 constraint t_process_tasks_open_action_fk
  74                                 references workflow_actions,
  75         -- action when closing task
  76         closing_action_id       integer
  77                                 constraint t_process_tasks_close_action_fk
  78                                 references workflow_actions,
  79         party_id                integer
  80                                 constraint t_process_tasks_party_fk
  81                                 references parties,
  82         object_id               integer
  83                                 constraint t_process_tasks_object_fk
  84                                 references acs_objects,
  85         title                   varchar(1000),
  86         description             text,
  87         mime_type               varchar(200) default 'text/plain',
  88         comment                 text,
  89         status_id               integer
  90                                 constraint t_process_tasks_status_fk
  91                                 references t_task_status,
  92         priority                integer,
  93         -- start date relative to current date
  94         start                   numeric,
  95         -- due date relative to current date
  96         due                     numeric
  97 );
  98
  99
  100 create table t_tasks (
  101         task_id                 integer
  102                                 constraint t_tasks_task_pk
  103                                 primary key
  104                                 constraint t_tasks_task_fk
  105                                 references acs_objects,
  106         process_instance_id     integer
  107                                 constraint t_tasks_instance_fk
  108                                 references t_process_instances,
10 109         process_task_id         integer
11                                   constraint tasks_pm_process_task_id_fk references pm_process_task(process_task_id)
12                                   constraint tasks_pm_process_task_id_pk primary key,
13           due_interval            interval,
  110                                 constraint t_tasks_process_task_fk
  111                                 references t_process_tasks,
  112         party_id                integer
  113                                 constraint t_tasks_party_fk
  114                                 references parties,
  115         object_id               integer
  116                                 constraint t_tasks_object_fk
  117                                 references acs_objects,
  118         title                   varchar(1000),
  119         description             text,
  120         mime_type               varchar(200) default 'text/plain',
  121         comment                 text,
  122         status_id               integer
  123                                 constraint t_tasks_status_fk
  124                                 references t_task_status,
  125         priority                integer,
  126         start_date              timestamptz,
14 127         due_date                timestamptz,
15           priority                integer default 0
  128         completed_date          timestamptz
16 129 );
17 130
  131
  132
  133
  134
  135
  136 CREATE FUNCTION inline_0()
  137 RETURNS integer
  138 AS 'declare
  139     begin
  140        PERFORM
  141             acs_object_type__create_type(
  142                 ''tasks_task'', -- object_type
  143                 ''Task'',       -- pretty_name
  144                 ''Tasks'',      -- pretty_plural
  145                 ''acs_object'', -- supertype
  146                 ''t_tasks'',    -- table_name
  147                 ''task_id'',    -- id_column
  148                 ''tasks_task'', -- package_name
  149                 ''f'',          -- abstract_p
  150                 null,           -- type_extension_table
  151                 null            -- name_method
  152             );
  153                 
  154        PERFORM
  155             acs_object_type__create_type(
  156                 ''tasks_process'',      -- object_type
  157                 ''Task Process'',       -- pretty_name
  158                 ''Task Processes'',     -- pretty_plural
  159                 ''acs_object'',         -- supertype
  160                 ''t_processes'',        -- table_name
  161                 ''process_id'',         -- id_column
  162                 ''tasks_process'',      -- package_name
  163                 ''f'',                  -- abstract_p
  164                 null,                   -- type_extension_table
  165                 null                    -- name_method
  166             );
  167                 
  168        PERFORM
  169             acs_object_type__create_type(
  170                 ''tasks_process_instance'',     -- object_type
  171                 ''Task Process Instance'',      -- pretty_name
  172                 ''Task Process Instances'',     -- pretty_plural
  173                 ''acs_object'',                 -- supertype
  174                 ''t_process_instances'',        -- table_name
  175                 ''process_instance_id'',        -- id_column
  176                 ''tasks_process_instance'',     -- package_name
  177                 ''f'',                          -- abstract_p
  178                 null,                           -- type_extension_table
  179                 null                            -- name_method
  180             );
  181                 
  182        PERFORM
  183             acs_object_type__create_type(
  184                 ''tasks_process_task'', -- object_type
  185                 ''Process Task'',       -- pretty_name
  186                 ''Process Tasks'',      -- pretty_plural
  187                 ''acs_object'',         -- supertype
  188                 ''t_process_tasks'',    -- table_name
  189                 ''task_id'',            -- id_column
  190                 ''tasks_process_task'', -- package_name
  191                 ''f'',                  -- abstract_p
  192                 null,                   -- type_extension_table
  193                 null                    -- name_method
  194             );
  195                 
  196             return 0;
  197
  198     end;'
  199 LANGUAGE 'plpgsql';
  200
  201 SELECT inline_0();
  202
  203 DROP function inline_0();
  204
  205
  206
18 207 create or replace function tasks__relative_date (
19 208         timestamptz             -- date_comparative
20 209 ) returns varchar
21 210 as '
22 211 declare
23 212         p_date                  alias for $1;
24 213         v_date                  varchar;
25 214 begin
26 215         v_date := CASE WHEN to_char(p_date,''YYYY'') = to_char(now(),''YYYY'') THEN
27 216                        CASE WHEN to_char(p_date,''YYYY-MM-DD'') = to_char(now(),''YYYY-MM-DD'') THEN ''Today''
28 217                             WHEN to_char(p_date,''YYYY-MM-DD'') = to_char((now() - ''1 day''::interval),''YYYY-MM-DD'') THEN ''Yesterday''
29 218                             WHEN to_char(p_date,''YYYY-MM-DD'') = to_char((now() - ''2 day''::interval),''YYYY-MM-DD'') THEN ''Two Days Ago''
30 219                             WHEN to_char(p_date,''YYYY-MM-DD'') = to_char((now() + ''1 day''::interval),''YYYY-MM-DD'') THEN ''Tomorrow''
31 220                             WHEN to_char(p_date,''YYYY-MM-DD'') = to_char((now() + ''2 day''::interval),''YYYY-MM-DD'') THEN CASE WHEN to_char(p_date,''FMDay'') not in ( ''Sunday'', ''Saturday'', ''Monday'', ''Tuesday'') THEN to_char(p_date,''Day'') ELSE to_char(p_date,''Mon DD (Dy)'') END
32 221                             WHEN to_char(p_date,''YYYY-MM-DD'') = to_char((now() + ''3 day''::interval),''YYYY-MM-DD'') THEN CASE WHEN to_char(p_date,''FMDay'') not in ( ''Sunday'', ''Saturday'', ''Monday'', ''Tuesday'') THEN to_char(p_date,''Day'') ELSE to_char(p_date,''Mon DD (Dy)'') END
33 222                             WHEN to_char(p_date,''YYYY-MM-DD'') = to_char((now() + ''4 day''::interval),''YYYY-MM-DD'') THEN CASE WHEN to_char(p_date,''FMDay'') not in ( ''Sunday'', ''Saturday'', ''Monday'', ''Tuesday'') THEN to_char(p_date,''Day'') ELSE to_char(p_date,''Mon DD (Dy)'') END
34 223                             ELSE to_char(p_date,''Mon DD (Dy)'') END
35 224                        ELSE to_char(p_date,''Mon DD, YYYY'') END;
36 225
37 226
 
95 284               FOR revision IN
96 285                   select ptr.percent_complete, ao.creation_user
97 286                     from cr_revisions cr, pm_tasks_revisions ptr, acs_objects ao
98 287                    where cr.item_id = p_task_id
99 288                      and cr.revision_id = ao.object_id
100 289                      and cr.revision_id = ptr.task_revision_id
101 290                    order by ao.creation_date desc
102 291               LOOP                
103 292                     IF revision.percent_complete = ''100'' AND v_previous_p THEN
104 293                           v_user := revision.creation_user;
105 294                     ELSE
106 295                           v_previous_p := ''f'';
107 296                           EXIT;
108 297                     END IF;
109 298               END LOOP;
110 299
111 300         END IF;
112 301
113 302         return v_user;
114 303 end;' language 'plpgsql';
  304
  305
  306 -----------------------------
  307
  308 select define_function_args('tasks_task__new','task_id,process_instance_id,process_task_id,party_id,object_id,title,description,mime_type,comment,status_id,priority,start_date,due_date,package_id,creation_user,creation_ip,context_id');
  309
  310 create or replace function tasks_task__new (integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,timestamptz,timestamptz,integer,integer,varchar,integer)
  311 returns integer as '
  312 declare
  313     p_task_id                 alias for $1;
  314     p_process_instance_id     alias for $2;
  315     p_process_task_id         alias for $3;
  316     p_party_id                alias for $4;
  317     p_object_id               alias for $5;
  318     p_title                   alias for $6;
  319     p_description             alias for $7;
  320     p_mime_type               alias for $8;
  321     p_comment                 alias for $9;
  322     p_status_id               alias for $10;
  323     p_priority                alias for $11;
  324     p_start_date              alias for $12;
  325     p_due_date                alias for $13;
  326     p_package_id              alias for $14;
  327     p_creation_user           alias for $15;
  328     p_creation_ip             alias for $16;
  329     p_context_id              alias for $17;
  330     v_task_id                 integer;
  331     v_start_date              timestamptz;
  332 begin
  333     v_task_id:= acs_object__new(
  334         p_task_id,
  335         ''tasks_task'',
  336         now(),
  337         p_creation_user,
  338         p_creation_ip,
  339         coalesce(p_context_id, p_package_id),
  340         ''t'',
  341         p_title,
  342         p_package_id
  343     );
  344
  345     if p_start_date is null then
  346       v_start_date := now();
  347     else
  348       v_start_date := p_start_date;
  349     end if;
  350
  351     insert into t_tasks
  352     (task_id, process_instance_id, process_task_id, party_id, object_id,
  353      title, description, mime_type, comment, status_id, priority,
  354      start_date, due_date)
  355     values
  356     (v_task_id, p_process_instance_id, p_process_task_id, p_party_id,
  357      p_object_id, p_title, p_description, p_mime_type, p_comment,
  358      p_status_id, p_priority, v_start_date, p_due_date);
  359
  360     return v_task_id;
  361 end;
  362 ' language 'plpgsql';
  363
  364
  365 select define_function_args('tasks_process_task__new','task_id,process_id,open_action_id,closing_action_id,party_id,object_id,title,description,mime_type,comment,status_id,priority,start,due,package_id,creation_user,creation_ip,context_id');
  366
  367 create or replace function tasks_process_task__new (integer,integer,integer,integer,integer,integer,varchar,text,varchar,text,integer,integer,numeric,numeric,integer,integer,varchar,integer)
  368 returns integer as '
  369 declare
  370     p_task_id                 alias for $1;
  371     p_process_id              alias for $2;
  372     p_open_action_id          alias for $3;
  373     p_closing_action_id       alias for $4;
  374     p_party_id                alias for $5;
  375     p_object_id               alias for $6;
  376     p_title                   alias for $7;
  377     p_description             alias for $8;
  378     p_mime_type               alias for $9;
  379     p_comment                 alias for $10;
  380     p_status_id               alias for $11;
  381     p_priority                alias for $12;
  382     p_start                   alias for $13;
  383     p_due                     alias for $14;
  384     p_package_id              alias for $15;
  385     p_creation_user           alias for $16;
  386     p_creation_ip             alias for $17;
  387     p_context_id              alias for $18;
  388     v_task_id                 integer;
  389 begin
  390     v_task_id:= acs_object__new(
  391         p_task_id,
  392         ''tasks_process_task'',
  393         now(),
  394         p_creation_user,
  395         p_creation_ip,
  396         coalesce(p_context_id, p_package_id),
  397         ''t'',
  398         p_title,
  399         p_package_id
  400     );
  401
  402     insert into t_process_tasks
  403     (task_id, process_id, open_action_id, closing_action_id, party_id,
  404      object_id, title, description, mime_type, comment, status_id,
  405      priority, start, due)
  406     values
  407     (v_task_id, p_process_id, p_open_action_id, p_closing_action_id,
  408      p_party_id, p_object_id, p_title, p_description, p_mime_type,
  409      p_comment, p_status_id, p_priority, p_start, p_due);
  410
  411     return v_task_id;
  412 end;
  413 ' language 'plpgsql';
  414
  415
  416 select define_function_args('tasks_process__new','process_id,title,description,mime_type,workflow_id,package_id,creation_user,creation_ip,context_id');
  417
  418 create or replace function tasks_process__new (integer,varchar,text,varchar,integer,integer,integer,varchar,integer)
  419 returns integer as '
  420 declare
  421     p_process_id              alias for $1;
  422     p_title                   alias for $2;
  423     p_description             alias for $3;
  424     p_mime_type               alias for $4;
  425     p_workflow_id             alias for $5;
  426     p_package_id              alias for $6;
  427     p_creation_user           alias for $7;
  428     p_creation_ip             alias for $8;
  429     p_context_id              alias for $9;
  430     v_process_id              integer;
  431 begin
  432     v_process_id:= acs_object__new(
  433         p_process_id,
  434         ''tasks_process'',
  435         now(),
  436         p_creation_user,
  437         p_creation_ip,
  438         coalesce(p_context_id, p_package_id),
  439         ''t'',
  440         p_title,
  441         p_package_id
  442     );
  443
  444     insert into t_processes
  445     (process_id, title, description, mime_type, workflow_id)
  446     values
  447     (v_process_id, p_title, p_description, p_mime_type, p_workflow_id);
  448
  449     return v_process_id;
  450 end;
  451 ' language 'plpgsql';
  452
  453
  454 select define_function_args('tasks_process_instance__new','process_instance_id,process_id,case_id,party_id,object_id,package_id,creation_user,creation_ip,context_id');
  455
  456 create or replace function tasks_process_instance__new (integer,integer,integer,integer,integer,integer,integer,varchar,integer)
  457 returns integer as '
  458 declare
  459     p_process_instance_id     alias for $1;
  460     p_process_id              alias for $2;
  461     p_case_id                 alias for $3;
  462     p_party_id                alias for $4;
  463     p_object_id               alias for $5;
  464     p_package_id              alias for $6;
  465     p_creation_user           alias for $7;
  466     p_creation_ip             alias for $8;
  467     p_context_id              alias for $9;
  468     v_process_instance_id     integer;
  469 begin
  470     v_process_instance_id:= acs_object__new(
  471         p_process_instance_id,
  472         ''tasks_process_instance'',
  473         now(),
  474         p_creation_user,
  475         p_creation_ip,
  476         coalesce(p_context_id, p_package_id),
  477         ''t'',
  478         ''process instance of process '' || p_process_id || '' for object '' || p_object_id,
  479         p_package_id
  480     );
  481
  482     insert into t_process_instances
  483     (process_instance_id, process_id, case_id, party_id, object_id)
  484     values
  485     (v_process_instance_id, p_process_id, p_case_id, p_party_id, p_object_id);
  486
  487     return v_process_instance_id;
  488 end;
  489 ' language 'plpgsql';