Index: openacs-4/contrib/obsolete-packages/ticket-tracker/ticket-tracker.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/ticket-tracker.info,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/ticket-tracker.info 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/ticket-tracker.info 4 Jun 2001 16:53:27 -0000 1.2 @@ -4,6 +4,7 @@ Ticket Tracker Ticket Trackers + f f @@ -17,7 +18,7 @@ 2001-02-15 ArsDigita Corporation - + @@ -30,36 +31,75 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -72,23 +112,44 @@ + + + + + + + + + + + + + + + + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/options-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/options-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/options-create.sql 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/options-create.sql 4 Jun 2001 16:53:27 -0000 1.2 @@ -34,7 +34,7 @@ -- generating a sequence for the primary key -- since we're not using acs_object -create sequence ttracker_option_id_seq; +create sequence ttracker_option_id_sequence; comment on table ttracker_options is ' A table to store priority/severity options. Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-drop.sql,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-drop.sql 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-drop.sql 4 Jun 2001 16:53:27 -0000 1.2 @@ -55,9 +55,10 @@ drop table ttracker_categories; drop table ttracker_options; -drop sequence ttracker_option_id_seq; +drop sequence ttracker_option_id_sequence; drop package ttracker_util; drop package ttracker_callback; drop package ttracker_category; +drop package ttracker_option; drop package ttracker_ticket; Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-packages.sql,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-packages.sql 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/oracle/ticket-tracker-packages.sql 4 Jun 2001 16:53:27 -0000 1.2 @@ -369,6 +369,7 @@ object_type => new.object_type, creation_date => new.creation_date, creation_user => new.creation_user, + creation_ip => new.creation_ip, context_id => new.context_id ); insert into ttracker_categories (category_id, package_id, name, default_assignee, description) @@ -428,7 +429,7 @@ end if; if option_id is null then - select ttracker_option_id_seq.nextval into v_option_id + select ttracker_option_id_sequence.nextval into v_option_id from dual; else v_option_id := add_option.option_id; Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/category-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/category-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/category-create.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,125 @@ +-- +-- packages/ticket-tracker/sql/category-create.sql +-- +-- +-- @author Tony Tseng (tony@arsidigta.com) +-- @author Phong Nguyen (phong@arsdigita.com) +-- +-- @creation-date 2000-11-16 +-- +-- @cvs-id $Id: category-create.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +---------------------------------- +-- KNOWLEDGE LEVEL OBJECTS +---------------------------------- + +create function inline_0 () +returns integer as ' +declare + attr_id acs_attributes.attribute_id%TYPE; +begin + perform acs_object_type__create_type ( + ''ttracker_category'', -- object_type + ''Ticket Tracker Category'', -- pretty_name + ''Ticket Tracker Categories'', -- pretty_plural + ''acs_object'', -- supertype + ''ttracker_categories'', -- table_name + ''category_id'', -- id_column + ''ttracker_category'', -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + ''ttracker_category__name'' -- name_method + ); + + perform acs_attribute__create_attribute ( + ''ttracker_category'', -- object_type + ''name'', -- attribute_name + ''string'', -- datatype + ''Name'', -- pretty_name + ''Names'', -- pretty_plural + null, -- table_name + null, -- column_name + null, -- default_value + 1, -- min_n_values + 1, -- max_n_values + null, -- sort_order + ''type_specific'', -- storage + ''f'' -- static_p + ); + + perform acs_attribute__create_attribute ( + ''ttracker_category'', -- object_type + ''description'', -- attribute_name + ''string'', -- datatype + ''Description'', -- pretty_name + ''Descriptions'', -- pretty_plural + null, -- table_name + null, -- column_name + null, -- default_value + 1, -- min_n_values + 1, -- max_n_values + null, -- sort_order + ''type_specific'', -- storage + ''f'' -- static_p + ); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + +---------------------------------- +-- OPERATIONAL LEVEL +---------------------------------- + +create table ttracker_categories ( + category_id integer + constraint ttracker_categories_pk + primary key + constraint ttracker_categories_fk + references acs_objects(object_id) on delete cascade, + package_id integer + constraint ttkr_cat_package_id_fk + references apm_packages (package_id) on delete cascade + constraint ttkr_cat_package_id_nn + not null, + name varchar(100) + constraint ttracker_categories_name_nn + not null, + description text, + default_assignee integer + constraint ttracker_categories_assgn_fk + references parties(party_id) +); + +-- to prevent ttracker_categories from getting locked +-- when persons table is being updated +create index ttkr_cat_assignee_idx on ttracker_categories (default_assignee); + +-- to speed up duplication check +create index ttkr_cat_upper_name_idx on ttracker_categories (upper(name)); + +-- people often query ttracker_categories to +-- find out the categories within a package +create unique index ttkr_cat_pkg_name_cat_idx on ttracker_categories (package_id, name, category_id); + +comment on table ttracker_categories is ' + This table holds information about ticket tracker categories +'; +comment on column ttracker_categories.package_id is ' + The package instance that contains this category. + Right now it is set as "on delete cascade" + so that we can delete the instance of the package right after we mount it +'; +comment on column ttracker_categories.name is ' + The name of the category +'; +comment on column ttracker_categories.description is ' + The detailed description of the category +'; +comment on column ttracker_categories.default_assignee is ' + The default assignee of this category. +'; Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/options-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/options-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/options-create.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,65 @@ +-- +-- packages/ticket-tracker/sql/options-create.sql +-- +-- +-- @author Phong Nguyen (phong@arsdigita.com) +-- @author Tony Tseng (tony@arsidigta.com) +-- +-- @creation-date 2000-12-15 +-- +-- @cvs-id $Id: options-create.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +create table ttracker_options ( + option_id integer + constraint ttracker_options_pk + primary key, + name varchar(100) + constraint ttkr_options_name_nn + not null, + value integer + constraint ttkr_options_value_nn + not null, + package_id integer + constraint ttkr_options_package_id_fk + references apm_packages(package_id) on delete cascade, + function varchar(10) + constraint ttkr_options_function_nn + not null + constraint ttkr_options_function_ck + check (function in ('severity', 'priority')), + constraint ttkr_opt_pkg_fun_val_un unique(package_id, function, value), + constraint ttkr_opt_pkg_fun_nam_un unique(package_id, function, name) +); + +-- generating a sequence for the primary key +-- since we're not using acs_object +create sequence ttracker_option_id_seq; +create view ttracker_option_id_sequence as + select nextval('ttracker_option_id_seq') as nextval; + +comment on table ttracker_options is ' + A table to store priority/severity options. + Basically just pairs of name/value. +'; + +comment on column ttracker_options.option_id is ' + The integer primary key. +'; + +comment on column ttracker_options.package_id is ' + The package instance that uses this option. +'; + +comment on column ttracker_options.name is ' + The user readable name of the priority/severity option. +'; + +comment on column ttracker_options.value is ' + The actual integer value of the priority/severity +'; + +comment on column ttracker_options.function is ' + The function of this entry. Either priority or severity +'; + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-create.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,171 @@ +-- +-- packages/ticket-tracker/sql/ticket-create.sql +-- +-- +-- @author Phong Nguyen (phong@arsdigita.com) +-- @author Tony Tseng (tony@arsidigta.com) +-- +-- @creation-date 2000-11-15 +-- +-- @cvs-id $Id: ticket-create.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +---------------------------------- +-- KNOWLEDGE LEVEL OBJECTS +---------------------------------- +--declare +-- attr_id acs_attributes.attribute_id%TYPE; +--begin + + select acs_object_type__create_type ( + 'ttracker_ticket', + 'Ticket Tracker Ticket', + 'Ticket Tracker Tickets', + 'acs_message', + 'ttracker_tickets', + 'ticket_id', + 'ttracker_ticket', + 'f', + null, + 'ttracker_ticket.name' + ); + + select acs_attribute__create_attribute ( + 'ttracker_ticket', + 'category_id', + 'integer', + 'Category ID', + 'Category IDs', + null, + null, + null, + 1, + 1, + null, + 'type_specific', + 'f' + ); + + select acs_attribute__create_attribute ( + 'ttracker_ticket', + 'subject', + 'string', + 'Subject', + 'Subjects', + null, + null, + null, + 1, + 1, + null, + 'type_specific', + 'f' + ); + + select acs_attribute__create_attribute ( + 'ttracker_ticket', + 'severity', + 'integer', + 'Severity', + 'Severities', + null, + null, + null, + 1, + 1, + null, + 'type_specific', + 'f' + ); + + select acs_attribute__create_attribute ( + 'ttracker_ticket', + 'priority', + 'integer', + 'Priority', + 'Priorities', + null, + null, + null, + 1, + 1, + null, + 'type_specific', + 'f' + ); +--end; +--/ +--show errors + +---------------------------------- +-- OPERATIONAL LEVEL +---------------------------------- + +create table ttracker_tickets ( + ticket_id integer + constraint ttkr_tkt_pk + primary key + constraint ttkr_tkt_fk + references acs_messages (message_id) on delete cascade, + category_id integer + constraint ttkr_tkt_category_id_fk + references ttracker_categories (category_id) on delete cascade, + creation_user integer + constraint ttkr_tkt_creation_user_fk + references persons(person_id) + constraint ttkr_tkt_creation_user_nn + not null, + creation_date timestamp default current_timestamp + constraint ttkr_tkt_date_nn + not null, + subject varchar(1000) + constraint ttkr_tkt_subject_nn + not null, + severity integer + constraint ttkr_tkt_severity_nn + not null, + priority integer + constraint ttkr_tkt_priority_nn + not null +); + +-- to prevent ttracker_tickets from getting locked when ttracker_categories table is being updated +create index tt_category_id_idx on ttracker_tickets (category_id); + +-- to prevent ttracker_tickets from getting locked when persons table is being updated +create index tt_creation_user_idx on ttracker_tickets (creation_user); + +comment on table ttracker_tickets is ' + The contents of a ticket is stored as an acs-message. This table + extends the the acs_messages table to hold additional knowledge + level attributes for a ticket type. +'; + +comment on column ttracker_tickets.creation_user is ' + The id of the user who submitted the ticket. It is stored + redundantly to avoid joining acs-objects. +'; + +comment on column ttracker_tickets.creation_date is ' + The creation date of the ticket. It is stored redundantly to + avoid joining against cr_items and cr_revisions. +'; + +comment on column ttracker_tickets.category_id is ' + The id of the category to associate this ticket to. +'; + +comment on column ttracker_tickets.subject is ' + The subject of the ticket. It is stored redundantly to avoid + joining against cr_items and cr_revisions. +'; + +comment on column ttracker_tickets.severity is ' + The severity level of the ticket. +'; + +comment on column ttracker_tickets.priority is ' + The priority level of the ticket. +'; + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-create.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,21 @@ +-- +-- packages/ticket-tracker/sql/ticket-tracker-create.sql +-- +-- +-- @author Phong Nguyen (phong@arsdigita.com) +-- @author Tony Tseng (tony@arsidigta.com) +-- +-- @creation-date 2000-11-15 +-- +-- @cvs-id $Id: ticket-tracker-create.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +\i options-create.sql +\i category-create.sql +\i ticket-create.sql +\i ttracker-workflow-create.sql +\i ticket-tracker-packages.sql + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-drop.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,84 @@ +-- +-- packages/ticket-tracker/sql/ttracker-drop.sql +-- +-- +-- @author Phong Nguyen (phong@arsdigita.com) +-- @author Tony Tseng (tony@arsidigta.com) +-- +-- @creation-date 2000-11-15 +-- +-- @cvs-id $Id: ticket-tracker-drop.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +-- drop the workflow process +\i ttracker-workflow-drop.sql + +create function inline_0 () +returns integer as ' +declare + comment_rec record; + ticket_rec record; + category_rec record; +begin + -- iterate through all comments on tickets + for comment_rec in select gc.comment_id + from general_comments gc, + ttracker_tickets tt + where gc.object_id = tt.ticket_id loop + perform acs_message__delete(comment_rec.comment_id); + end loop; + + -- iterate through all tickets + for ticket_rec in select ticket_id from ttracker_tickets loop + perform ttracker_ticket__delete(ticket_rec.ticket_id); + end loop; + + -- iterate through all categories + for category_rec in select category_id from ttracker_categories loop + perform ttracker_category__delete(category_rec.category_id); + end loop; + + perform acs_object_type__drop_type (''ttracker_ticket'', ''t''); + perform acs_object_type__drop_type (''ttracker_category'', ''t''); + + return 0; +end;' language 'plpgsql'; + +select inline_0(); + +drop function inline_0(); + +drop table ttracker_tickets; +drop table ttracker_categories; +drop table ttracker_options; + +drop sequence ttracker_option_id_seq; +drop view ttracker_option_id_sequence; + +-- drop packages +drop function ttracker_util__notification_sender (integer); +drop function ttracker_util__workflow_url (boolean); +drop function ttracker_util__install (integer); + +drop function ttracker_callback__assign_to_submitter (integer,varchar); +drop function ttracker_callback__assign_to_assignee (integer,varchar); +drop function ttracker_callback__clarify_fire (integer,varchar,varchar); +drop function ttracker_callback__resolve_fire (integer,varchar,varchar); +drop function ttracker_callback__notification (integer,varchar,integer,integer,varchar,varchar); +drop function ttracker_callback__notify_admin (integer,varchar); + +drop function ttracker_category__new (integer,integer,varchar,timestamp,integer,varchar,integer,varchar,integer,varchar); +drop function ttracker_category__delete (integer); +drop function ttracker_category__name (integer); + +drop function ttracker_option__add_option (integer,integer,varchar,varchar,integer); +drop function ttracker_option__update_option (integer,varchar,integer); +drop function ttracker_option__option_name (integer,varchar,integer); +drop function ttracker_option__option_value (integer,varchar,varchar); + +drop function ttracker_ticket__new (integer,varchar,integer,varchar,varchar,varchar,integer,integer,integer,timestamp,integer,varchar); +drop function ttracker_ticket__delete (integer); +drop function ttracker_ticket__name (integer); +drop function ttracker_ticket__id_valid_p (integer,integer); +drop function ttracker_ticket__update_mime_subj_desc (integer,varchar,varchar,varchar); + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-packages.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ticket-tracker-packages.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,719 @@ +-- +-- packages/ticket-tracker/sql/ticket-tracker-packages.sql +-- +-- +-- @author Phong Nguyen (phong@arsdigita.com) +-- @author Tony Tseng (tony@arsidigta.com) +-- +-- @creation-date 2000-11-30 +-- +-- @cvs-id $Id: ticket-tracker-packages.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +---------------------------------- +-- PACKAGE BODIES +---------------------------------- + +-- create or replace package body ttracker_ticket +-- as + +create function ttracker_ticket__new (integer,varchar,integer,varchar,varchar,varchar,integer,integer,integer,timestamp,integer,varchar) +returns integer as ' +declare + p_ticket_id alias for $1; -- default null + p_object_type alias for $2; -- default ''ttracker_ticket'' + p_context_id alias for $3; -- default null + p_subject alias for $4; + p_description alias for $5; + p_mime_type alias for $6; -- default ''text/plain'' + p_category_id alias for $7; + p_severity alias for $8; + p_priority alias for $9; + p_creation_date alias for $10; -- default now() + p_creation_user alias for $11; + p_creation_ip alias for $12; -- default null + v_ticket_id ttracker_tickets.ticket_id%TYPE; + v_case_id wf_cases.case_id%TYPE; +begin + -- create a new acs-message to hold the contents of the ticket + + select acs_message__new ( + p_ticket_id, -- message_id + p_subject, -- title + p_description, -- text + p_mime_type, -- mime_type + p_context_id, -- context_id + p_object_type, -- object_type + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ) into v_ticket_id; + + -- insert additional ticket info + insert into ttracker_tickets + (ticket_id, + creation_user, + creation_date, + category_id, + subject, + severity, + priority) + values + (v_ticket_id, + p_creation_user, + p_creation_date, + p_category_id, + p_subject, + p_severity, + p_priority); + + -- create a new case + select workflow_case__new ( + null, -- case_id + ''ttracker_wf'', -- workflow_key + null, -- context_key + v_ticket_id, -- object_id + now(), -- creation_date + p_creation_user, -- creation_user + p_creation_ip -- creation_ip + ) into v_case_id; + + -- start the case + perform workflow_case__start_case ( + v_case_id, -- case_id + p_creation_user, -- creation_user + p_creation_ip, -- creation_ip + null -- msg + ); + + return v_ticket_id; +end;' language 'plpgsql'; + +create function ttracker_ticket__delete (integer) +returns integer as ' +declare + p_ticket_id alias for $1; + case_rec record; +begin + for case_rec in select case_id from wf_cases where object_id = p_ticket_id loop + perform workflow_case__delete(case_rec.case_id); + end loop; + + perform acs_message__delete(p_ticket_id); + return p_ticket_id; +end;' language 'plpgsql'; + +create function ttracker_ticket__name (integer) +returns varchar as ' +declare + p_ticker_id alias for $1; + v_result ttracker_tickets.subject%TYPE; +begin + select subject into v_result + from ttracker_tickets + where ticket_id = p_ticket_id; + + return v_result; +end;' language 'plpgsql'; + +create function ttracker_ticket__id_valid_p (integer,integer) +returns boolean as ' +declare + p_ticket_id alias for $1; + p_package_id alias for $2; + v_result char(1); +begin + select case when count(tt.ticket_id) = 0 then ''f'' else ''t'' end into v_result + from ttracker_tickets tt + where exists (select tc.category_id + from ttracker_categories tc + where tc.category_id = tt.category_id + and tc.package_id = p_package_id) + and tt.ticket_id = p_ticket_id; + + return v_result; +end;' language 'plpgsql'; + +create function ttracker_ticket__update_mime_subj_desc (integer,varchar,varchar,varchar) +returns integer as ' +declare + p_ticket_id alias for $1; + p_mime_type alias for $2; + p_subject alias for $3; + p_description alias for $4; + v_cr_revision_id cr_revisions.revision_id%TYPE; +begin + select content_revision__new ( + p_subject, -- title + null, -- description + now(), -- publish_date + p_mime_type, -- mime_type + p_description, -- text + p_ticket_id -- item_id + ) into v_cr_revision_id; + + perform content_item__set_live_revision(v_cr_revision_id); + + return v_cr_revision_id; +end;' language 'plpgsql'; + +-- end ttracker_ticket; +-- / +-- show errors + + +-- create or replace package body ttracker_category +-- as +-- +create function ttracker_category__new (integer,integer,varchar,timestamp,integer,varchar,integer,varchar,integer,varchar) +returns integer as ' +declare + p_category_id alias for $1; -- default null + p_package_id alias for $2; + p_object_type alias for $3; -- default ''ttracker_category'' + p_creation_date alias for $4; -- default now() + p_creation_user alias for $5; -- default null + p_creation_ip alias for $6; -- default null + p_context_id alias for $7; -- default null + p_name alias for $8; + p_default_assignee alias for $9; -- default null + p_description alias for $10; -- default null + v_category_id acs_objects.object_id%TYPE; +begin + select acs_object__new ( + p_category_id, -- object_id + p_object_type, -- object_type + p_creation_date, -- creation_date + p_creation_user, -- creation_user + p_creation_ip, -- creation_ip + p_context_id -- context_id + ) into v_category_id; + + insert into ttracker_categories + (category_id, package_id, name, default_assignee, description) + values + (v_category_id, p_package_id, p_name, p_default_assignee, p_description); + + return v_category_id; +end;' language 'plpgsql'; + +create function ttracker_category__delete (integer) +returns integer as ' +declare + p_category_id alias for $1; + v_ticket_number integer; +begin + -- check if any ticket exists for this category before deleting + select count(ticket_id) into v_ticket_number + from ttracker_tickets + where category_id = p_category_id; + + if v_ticket_number > 0 then + raise exception ''Invalid severity.''; + end if; + + perform acs_object__delete(p_category_id); + + return p_category_id; +end;' language 'plpgsql'; + +create function ttracker_category__name (integer) +returns varchar as ' +declare + p_category_id alias for $1; + v_result ttracker_categories.name%TYPE; +begin + select name into v_result + from ttracker_categories + where category_id = p_category_id; + + return v_result; +end;' language 'plpgsql'; + +-- end ttracker_category; +-- / +-- show errors + +-- create or replace package body ttracker_option +-- as + +create function ttracker_option__add_option (integer,integer,varchar,varchar,integer) +returns integer as ' +declare + p_option_id alias for $1; -- default null + p_package_id alias for $2; + p_function alias for $3; + p_name alias for $4; + p_value alias for $5; + v_option_id ttracker_options.option_id%TYPE; +begin + if p_package_id is null or p_function is null or + p_name is null or p_value is null then + raise exception ''Arguments, with the exception of option_id, cannot be null''; + end if; + + if p_option_id is null then + select ttracker_option_id_sequence.nextval into v_option_id; + else + v_option_id := p_option_id; + end if; + + insert into ttracker_options + (option_id, package_id, function, name, value) + values + (v_option_id, p_package_id, p_function, p_name, p_value); + + return v_option_id; +end;' language 'plpgsql'; + +create function ttracker_option__update_option (integer,varchar,integer) +returns integer as ' +declare + p_option_id alias for $1; + p_name alias for $2; + p_value alias for $3; +begin + if p_option_id is null or p_name is null or p_value is null then + raise exception ''Arguments cannot be null''; + end if; + + update ttracker_options + set name = p_name, + value = p_value + where option_id = p_option_id; + + return p_option_id; +end;' language 'plpgsql'; + +create function ttracker_option__option_name (integer,varchar,integer) +returns varchar as ' +declare + p_package_id alias for $1; + p_function alias for $2; + p_value alias for $3; + v_name ttracker_options.name%TYPE; +begin + select name into v_name + from ttracker_options + where package_id = p_package_id + and function = p_function + and value = p_value; + + if not found then + raise exception ''Invalid option.''; + return ''''; + end if; + + return v_name; +end;' language 'plpgsql'; + + -- given the package_id, function, and name + -- return the integer value +create function ttracker_option__option_value (integer,varchar,varchar) +returns integer as ' +declare + p_package_id alias for $1; + p_function alias for $2; + p_name alias for $3; + v_value ttracker_options.value%TYPE; +begin + select value into v_value + from ttracker_options + where package_id = p_package_id + and function = p_function + and upper(name) = upper(p_name); + + if not found then + raise exception ''Invalid option.''; + return ''''; + end if; + + return v_value; +end;' language 'plpgsql'; + +-- end ttracker_option; +-- / +-- show errors + +-- create or replace package body ttracker_util +-- as + +create function ttracker_util__notification_sender (integer) +returns integer as ' +declare + p_package_id alias for $1; + v_result parties.party_id%TYPE; + v_attr_value apm_parameter_values.attr_value%TYPE; +begin + select apv.attr_value into v_attr_value + from apm_parameter_values apv, apm_parameters ap + where apv.package_id = p_package_id + and ap.package_key = ''ticket-tracker'' + and ap.parameter_name = ''NotificationSender''; + + if not found then + v_result := -1; + else + v_result := to_number(v_attr_value); + end if; + + return v_result; + + -- in case somebody tempered with the parameter + -- exception + -- when others then + -- return -1; +end;' language 'plpgsql'; + +create function ttracker_util__workflow_url (boolean) +returns varchar as ' +declare + p_absolute_p alias for $1; -- default ''t'' + v_system_url apm_parameter_values.attr_value%TYPE; + v_node_id site_nodes.node_id%TYPE; +begin + -- try to get the id of the site node that contains acs workflow + select sn.node_id into v_node_id + from site_nodes sn, apm_packages ap + where ap.package_key = ''acs-workflow'' + and sn.object_id = ap.package_id; + + if not found then + raise exception ''ACS Workflow must be mounted''; + end if; + + -- if only relative url is needed + if absolute_p <> ''t'' then + return site_node__url(v_node_id); + end if; + + -- since there''s only 1 instance of the kernel + -- it''s ok to use select.. into.. + select apv.attr_value into v_system_url + from apm_parameter_values apv, apm_parameters ap + where ap.package_key = ''acs-kernel'' + and ap.parameter_name = ''SystemURL'' + and ap.parameter_id = apv.parameter_id; + + return v_system_url || site_node__url(v_node_id); + +end;' language 'plpgsql'; + +create function ttracker_util__install (integer) +returns integer as ' +declare + p_package_id alias for $1; + v_category_id ttracker_categories.category_id%TYPE; + v_ttracker_url varchar(4000); + v_cat_cnt integer; +begin + -- check if there''s any existing category + -- if yes, quit + select count(category_id) into v_cat_cnt + from ttracker_categories + where package_id = p_package_id; + + if v_cat_cnt > 0 then + return 0; + end if; + + -- create the default category + select ttracker_category__new ( + null, -- category_id + p_package_id, -- package_id + ''ttracker_category'', -- object_type + now(), -- creation_date + null, -- creation_user + null, -- creation_ip + null, -- context_id + ''General'', -- name + null, -- default_assignee + ''Default category'' -- description + ) into v_category_id; + + -- insert the default severity/priority info + perform ttracker_option__add_option ( + null, -- option_id + p_package_id, -- package_id + ''severity'', -- function + ''critical'', -- name + 0 -- value + ); + + perform ttracker_option__add_option ( + null, -- option_id + p_package_id, -- package_id + ''severity'', -- function + ''serious'', -- name + 1 -- value + ); + + perform ttracker_option__add_option ( + null, -- opton_id + p_package_id, -- package_id + ''severity'', -- function + ''medium'', -- name + 2 -- value + ); + + perform ttracker_option__add_option ( + null, -- opton_id + p_package_id, -- package_id + ''severity'', -- function + ''low'', -- name + 3 -- value + ); + + perform ttracker_option__add_option ( + null, -- opton_id + p_package_id, -- package_id + ''severity'', -- function + ''wishlist'', -- name + 4 -- value + ); + + perform ttracker_option__add_option ( + null, -- opton_id + p_package_id, -- package_id + ''priority'', -- function + ''high'', -- name + 0 -- value + ); + + perform ttracker_option__add_option ( + null, -- opton_id + p_package_id, -- package_id + ''priority'', -- function + ''medium'', -- name + 1 -- value + ); + + perform ttracker_option__add_option ( + null, -- opton_id + p_package_id, -- package_id + ''priority'', -- function + ''low'', -- name + 2 -- value + ); + + return 1; +end;' language 'plpgsql'; + +-- end ttracker_util; +-- / +-- show errors + +-- create or replace package body ttracker_callback +-- as + +create function ttracker_callback__assign_to_submitter (integer,varchar) +returns integer as ' +declare + p_task_id alias for $1; + p_custom_arg alias for $2; + v_creation_user ttracker_tickets.creation_user%TYPE; + v_case_id wf_tasks.case_id%TYPE; + v_transition_key wf_tasks.transition_key%TYPE; +begin + -- find the creation user of the ticket (submitter) + select tt.creation_user, wt.case_id, wt.transition_key + into v_creation_user, v_case_id, v_transition_key + from ttracker_tickets tt, wf_tasks wt, wf_cases wc + where wt.task_id = p_task_id + and wt.case_id = wc.case_id + and wc.object_id = tt.ticket_id; + + -- assign the task to the ticket submitter + perform workflow_case__add_task_assignment( + p_task_id, -- task_id + v_creation_user -- party_id + ); + + perform workflow_case__add_manual_assignment ( + v_case_id, -- case_id + v_transition_key, -- transition_key + v_creation_user -- party_id + ); + + return 0; +end;' language 'plpgsql'; + +create function ttracker_callback__assign_to_assignee (integer,varchar) +returns integer as ' +declare + p_task_id alias for $1; + p_custom_arg alias for $2; + v_default_assignee ttracker_categories.default_assignee%TYPE; + v_case_id wf_tasks.case_id%TYPE; + v_transition_key wf_tasks.transition_key%TYPE; +begin + select tc.default_assignee, wt.case_id, wt.transition_key + into v_default_assignee, v_case_id, v_transition_key + from wf_tasks wt, wf_cases wc, ttracker_tickets tt, ttracker_categories tc + where wt.task_id = p_task_id + and wt.case_id = wc.case_id + and wc.object_id = tt.ticket_id + and tt.category_id = tc.category_id; + + if v_default_assignee is null then + return 0; + end if; + + -- assign the task + perform workflow_case__add_task_assignment( + p_task_id, -- task_id + v_default_assignee -- party_id + ); + + perform workflow_case__add_manual_assignment ( + v_case_id, -- case_id + v_transition_key, -- transition_key + v_default_assignee, -- party_id + ); + + return 1; +end;' language 'plpgsql'; + +create function ttracker_callback__clarify_fire (integer,varchar,varchar) +returns integer as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; + p_custom_arg alias for $3; + v_journal_id journal_entries.journal_id%TYPE; +begin + select journal_entry__new ( + null, -- journal_id + case_id, -- object_id + ''modify'', -- action + ''Attribute Change'', -- action_pretty + now(), -- creation_date + null, -- creation_user + null, -- creation_ip + ''Automatic action by the workflow process'' -- msg + ) into v_journal_id; + + perform workflow_case__set_attribute_value ( + v_journal_id, -- journal_id + ''need_clarification'', -- attribute_name + ''f'' -- value + ); + + return 0; +end;' language 'plpgsql'; + +create function ttracker_callback__resolve_fire (integer,varchar,varchar) +returns integer as ' +declare + p_case_id alias for $1; + p_transition_key alias for $2; + p_custom_arg alias for $3; + v_journal_id journal_entries.journal_id%TYPE; +begin + select journal_entry__new ( + null, -- journal_id + case_id, -- object_id + ''modify'', -- action + ''Attribute Change'', -- action_pretty + now(), -- creation_date + null, -- creation_user + null, -- creation_ip + ''Automatic action by the workflow process'' -- msg + ) into v_journal_id; + + perform workflow_case__set_attribute_value ( + v_journal_id, -- journal_id + ''verified'', -- attribute_name + ''t'' -- value + ); + + return 0; +end;' language 'plpgsql'; + +create function ttracker_callback__notification (integer,varchar,integer,integer,varchar,varchar) +returns integer as ' +declare + p_task_id alias for $1; + p_custom_arg alias for $2; + p_party_to alias for $3; + p_party_from alias for $4; -- in out + p_subject alias for $5; -- in out + p_body alias for $6; -- in out + v_url varchar(1000); + v_ticket_id ttracker_tickets.ticket_id%TYPE; + v_subject ttracker_tickets.subject%TYPE; + v_package_id ttracker_categories.package_id%TYPE; +begin + select tt.ticket_id, tt.subject, tc.package_id + into v_ticket_id, v_subject, v_package_id + from ttracker_tickets tt, ttracker_categories tc, + wf_tasks wt, wf_cases wc + where wt.task_id = p_task_id + and wt.case_id = wc.case_id + and wc.object_id = tt.ticket_id + and tt.category_id = tc.category_id; + + if not found then + raise exception ''Invalid task id''; + end if; + + select ttracker_util__notification_sender(v_package_id) into p_party_from; + + v_subject := ''Ticket #'' || v_ticket_id || '' -- '' || p_custom_arg; + p_body := ''Subject: '' || v_subject || '' +'' || ''To do: '' || p_custom_arg || '' +'' || ''Manage via: '' || ttracker_util__workflow_url(''t'') || ''task?task_id='' || p_task_id; + +--FIXME: need to figure out the in-out parameters + return 0; +end;' language 'plpgsql'; + +create function ttracker_callback__notify_admin (integer,varchar) +returns integer as ' +declare + p_task_id alias for $1; + p_custom_arg alias for $2; + v_ticket_id ttracker_tickets.ticket_id%TYPE; + v_subject ttracker_tickets.subject%TYPE; + v_package_id ttracker_categories.package_id%TYPE; + v_request_id integer; + admin_rec record; +begin + -- uses 2 separate queries to find out who has ''admin'' privilege + -- on this package because we don''t want to join any table with + -- acs_object_party_privilege_map unless we absolutely have to + -- (in this case, users table) the first query is only used to + -- gather ticket info and package id + + select tt.ticket_id, tt.subject, tc.package_id + into v_ticket_id, v_subject, v_package_id + from ttracker_tickets tt, ttracker_categories tc, + wf_cases wc, wf_tasks wt + where wt.task_id = p_task_id + and wc.case_id = wt.case_id + and wc.object_id = tt.ticket_id + and tt.category_id = tc.category_id; + + for admin_rec in select u.user_id + from users u, acs_object_party_privilege_map m + where m.object_id = v_package_id + and m.party_id = u.user_id + and m.privilege = ''admin'' loop + select nt.post_request ( + ttracker_util__notification_sender(v_package_id), -- party_from + admin_rec.user_id, -- party_to + ''f'', -- expand_group + ''Ticket #'' || v_ticket_id || '' -- '' || ''Assign Ticket'', -- subject + ''Subject: '' || v_subject || '' +'' || ''To do: '' || ''Assign Ticket'' || '' +'' || ''Manage via: '' || ttracker_util__workflow_url( ''t'') || ''task?task_id='' || p_task_id, -- message + 3 -- max_retries + ) into v_request_id; + end loop; + + return 0; +end;' language 'plpgsql'; + +-- end ttracker_callback; +-- / +-- show errors Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ttracker-workflow-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ttracker-workflow-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ttracker-workflow-create.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,443 @@ +-- +-- packages/ticket-tracker/sql/ttracker-workflow-create.sql +-- +-- +-- @author Phong Nguyen (phong@arsdigita.com) +-- @author Tony Tseng (tony@arsidigta.com) +-- +-- @creation-date 2000-11-15 +-- +-- @cvs-id $Id: ttracker-workflow-create.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +-- This table will hold one row for each case using this workflow. +create table wf_ttracker_cases ( + case_id integer + constraint wf_ttkr_cases_pk + primary key + constraint wf_ticket_cases_case_fk + references wf_cases +); + + +--declare +-- v_workflow_key wf_workflows.workflow_key%TYPE; +--begin + select workflow__create_workflow( + 'ttracker_wf', -- workflow_key + 'Ticket Tracker Process', -- pretty_name + 'Ticket Tracker Process', -- pretty_plural + 'Workflow for processing a ticket in the ticket-tracker', -- description + 'wf_ttracker_cases', -- table_name + 'case_id' -- id_column + ); + + -- Creating places + select workflow__add_place ( + 'ttracker_wf', -- workflow_key + 'start', -- place_key + 'Needs to be resolved', -- place_name + 1 -- sort_order + ); + + select workflow__add_place ( + 'ttracker_wf', -- workflow_key + 'to_be_clarified', -- place_key + 'Needs to be clarified', -- place_name + 2 -- sort_order + ); + + select workflow__add_place ( + 'ttracker_wf', -- workflow_key + 'to_be_verified', -- place_key + 'Needs to be verified', -- place_name + 3 -- sort_order + ); + + select workflow__add_place ( + 'ttracker_wf', -- workflow_key + 'end', -- place_key + 'Closed', -- place_name + 4 -- sort_order + ); + + -- Creating transitions + select workflow__add_transition ( + 'ttracker_wf', -- workflow_key + 'resolve', -- transition_key + 'Resolve ticket', -- transition_name + 1, -- sort_order + 'user' -- trigger_type + ); + + select workflow__add_transition ( + 'ttracker_wf', -- workflow_key + 'clarify', -- transition_key + 'Clarify ticket description', -- transition_name + 2, -- sort_order + 'user' -- trigger_type + ); + + select workflow__add_transition ( + 'ttracker_wf', -- workflow_key + 'verify', -- transition_key + 'Verify result', -- transition_name + 3, -- sort_order + 'user' -- trigger_type + ); + +--end; +--/ +--show errors + +--begin + -- creating arcs + -- resolve in + -- in + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'resolve', -- transition_key + 'start', -- place_key + 'in', -- direction + null, -- guard_callback + null, -- guard_custom_arg + null -- guard_description + ); + + -- out + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'resolve', -- transition_key + 'to_be_clarified', -- place_key + 'out', -- direction + 'wf_callback.guard_attribute_true', -- guard_callback + 'need_clarification', -- guard_custom_arg + 'Description needs clarification' -- guard_description + ); + + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'resolve', -- transition_key + 'to_be_verified', -- place_key + 'out', -- direction + '#', -- guard_callback + null, -- guard_custom_arg + 'Description clear; issue resolved' -- guard_description + ); + + -- verify + -- in + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'verify', -- transition_key + 'to_be_verified', -- place_key + 'in', -- direction + null, -- guard_callback + null, -- guard_custom_arg + null -- guard_description + ); + + -- out + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'verify', -- transition_key + 'end', -- place_key + 'out', -- direction + 'wf_callback.guard_attribute_true', -- guard_callback + 'verified', -- guard_custom_arg + 'Result okay' -- guard_description + ); + + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'verify', -- transition_key + 'start', -- place_key + 'out', -- direction + '#', -- guard_callback + null, -- guard_custom_arg + 'Result not okay' -- guard_description + ); + + -- clarify + -- in + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'clarify', -- transition_key + 'to_be_clarified', -- place_key + 'in', -- direction + null, -- guard_callback + null, -- guard_custom_arg + null -- guard_description + ); + + -- out + select workflow__add_arc ( + 'ttracker_wf', -- workflow_key + 'clarify', -- transition_key + 'start', -- place_key + 'out', -- direction + null, -- guard_callback + null, -- guard_custom_arg + null -- guard_description + ); + +--end; +--/ +--show errors + +create function inline_0 () +returns integer as ' +declare + v_attribute_id acs_attributes.attribute_id%TYPE; +begin + select workflow__create_attribute( + ''ttracker_wf'', -- workflow_key + ''need_clarification'', -- attribute_name + ''boolean'', -- datatype + ''Description needs clarification'', -- pretty_name + null, -- pretty_plural + null, -- table_name + null, -- column_name + ''f'', -- default_value + 1, -- min_n_values + 1, -- max_n_values + null, -- sort_order + ''generic'', -- storage + ''none'' -- wf_datatype + ) into v_attribute_id; + + insert into wf_transition_attribute_map + (workflow_key, transition_key, attribute_id, sort_order) + values + (''ttracker_wf'', ''resolve'', v_attribute_id, 1); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + + +create function inline_1 () +returns integer as ' +declare + v_attribute_id acs_attributes.attribute_id%TYPE; +begin + select workflow__create_attribute( + ''ttracker_wf'', -- workflow_key + ''verified'', -- attribute_name + ''boolean'', -- datatype + ''Result is okay'', -- pretty_name + null, -- pretty_plural + null, -- table_name + null, -- column_name + ''t'', -- default_value + 1, -- min_n_values + 1, -- max_n_values + null, -- sort_order + ''generic'', -- storage + ''none'' -- wf_datatype + ) into v_attribute_id; + + insert into wf_transition_attribute_map + (workflow_key, transition_key, attribute_id, sort_order) + values + (''ttracker_wf'', ''verify'', v_attribute_id, 1); + + return 0; +end;' language 'plpgsql'; + +select inline_1 (); + +drop function inline_1 (); + +--declare +-- v_attribute_id acs_attributes.attribute_id%TYPE; +--begin +-- v_attribute_id := workflow.create_attribute( +-- workflow_key => 'ttracker_wf', +-- attribute_name => 'need_clarification', +-- datatype => 'boolean', +-- pretty_name => 'Description needs clarification', +-- default_value => 'f' +-- ); +-- +-- insert into wf_transition_attribute_map +-- (workflow_key, transition_key, attribute_id, sort_order) +-- values +-- ('ttracker_wf', 'resolve', v_attribute_id, 1); +-- +-- v_attribute_id := workflow.create_attribute( +-- workflow_key => 'ttracker_wf', +-- attribute_name => 'verified', +-- datatype => 'boolean', +-- pretty_name => 'Result is okay', +-- default_value => 't' +-- ); +-- +-- insert into wf_transition_attribute_map +-- (workflow_key, transition_key, attribute_id, sort_order) +-- values +-- ('ttracker_wf', 'verify', v_attribute_id, 1); +--end; +--/ +--show errors + +insert into wf_context_transition_info ( + context_key, + workflow_key, + transition_key, + assignment_callback, + fire_callback, + notification_callback, + notification_custom_arg, + unassigned_callback, + access_privilege +) values ( + 'default', + 'ttracker_wf', + 'resolve', + 'ttracker_callback__assign_to_assignee', + 'ttracker_callback__resolve_fire', + 'ttracker_callback__notification', + 'Resolve Ticket', + 'ttracker_callback__notify_admin', + 'admin' +); + +insert into wf_context_transition_info ( + context_key, + workflow_key, + transition_key, + assignment_callback, + fire_callback, + notification_callback, + notification_custom_arg, + access_privilege +) values ( + 'default', + 'ttracker_wf', + 'clarify', + 'ttracker_callback__assign_to_submitter', + 'ttracker_callback__clarify_fire', + 'ttracker_callback__notification', + 'Clarify Description', + 'admin' +); + +insert into wf_context_transition_info ( + context_key, + workflow_key, + transition_key, + assignment_callback, + notification_callback, + notification_custom_arg, + access_privilege +) values ( + 'default', + 'ttracker_wf', + 'verify', + 'ttracker_callback__assign_to_submitter', + 'ttracker_callback__notification', + 'Verify Result', + 'admin' +); + + +insert into wf_context_task_panels ( + context_key, + workflow_key, + transition_key, + sort_key, + header, + template_url +) values ( + 'default', + 'ttracker_wf', + 'resolve', + 1, + 'Ticket Info', + '/packages/ticket-tracker/wf-templates/ticket-info' +); + +insert into wf_context_task_panels ( + context_key, + workflow_key, + transition_key, + sort_key, + header, + template_url +) values ( + 'default', + 'ttracker_wf', + 'resolve', + 2, + 'What to do', + '/packages/ticket-tracker/wf-templates/resolve' +); + +insert into wf_context_task_panels ( + context_key, + workflow_key, + transition_key, + sort_key, + header, + template_url +) values ( + 'default', + 'ttracker_wf', + 'clarify', + 1, + 'Ticket Info', + '/packages/ticket-tracker/wf-templates/ticket-info' +); + +insert into wf_context_task_panels ( + context_key, + workflow_key, + transition_key, + sort_key, + header, + template_url +) values ( + 'default', + 'ttracker_wf', + 'clarify', + 2, + 'What to do', + '/packages/ticket-tracker/wf-templates/clarify' +); + +insert into wf_context_task_panels ( + context_key, + workflow_key, + transition_key, + sort_key, + header, + template_url +) values ( + 'default', + 'ttracker_wf', + 'verify', + 1, + 'Ticket Info', + '/packages/ticket-tracker/wf-templates/ticket-info' +); + +insert into wf_context_task_panels ( + context_key, + workflow_key, + transition_key, + sort_key, + header, + template_url +) values ( + 'default', + 'ttracker_wf', + 'verify', + 2, + 'What to do', + '/packages/ticket-tracker/wf-templates/verify' +); + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ttracker-workflow-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ttracker-workflow-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/sql/postgresql/ttracker-workflow-drop.sql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,25 @@ +-- +-- packages/ticket-tracker/sql/ttracker-workflow-drop.sql +-- +-- +-- @author Phong Nguyen (phong@arsdigita.com) +-- @author Tony Tseng (tony@arsidigta.com) +-- +-- @creation-date 2000-11-15 +-- +-- @cvs-id $Id: ttracker-workflow-drop.sql,v 1.1 2001/06/04 16:53:27 vinodk Exp $ +-- + +-- begin + select workflow__delete_cases('ttracker_wf'); +-- end; +-- / +-- show errors + +drop table wf_ttracker_cases; + +-- begin + select workflow__drop_workflow('ttracker_wf'); +-- end; +-- / +-- show errors Index: openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,33 @@ + + + + oracle8.1.6 + + + + + select decode(ttracker_ticket.id_valid_p(:value, :package_id), 't', 1, 0) as id_valid_p from dual + + + + + + + + select ttracker_util.workflow_url(:absolute_p) from dual + + + + + + + + begin + ttracker_util.install(:package_id); + end; + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,35 @@ + + + + postgresql7.1 + + + + +select case when ttracker_ticket__id_valid_p(:value, :package_id) = 't' + then 1 else 0 end as id_valid_p + + + + + + + + + +select ttracker_util__workflow_url(:absolute_p) + + + + + + + + +select ttracker_util__install(:package_id) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/tcl/ttracker-procs.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,46 @@ + + + + + + + select case when count(option_id) = 0 then 0 else 1 end as id_valid_p + from ttracker_options + where option_id = :value + and package_id = :package_id + + + + + + + + + select case when count(wt.task_id) = 0 then 0 else 1 end as id_valid_p + from wf_tasks wt + where exists (select wc.case_id + from wf_cases wc, + ttracker_tickets tt, + ttracker_categories tc + where wt.case_id = wc.case_id + and wc.object_id = tt.ticket_id + and tt.category_id = tc.category_id + and tc.package_id = :package_id) + and wt.task_id = :value + + + + + + + + + select case when count(1) = 0 then 0 else 1 end + from ttracker_categories + where package_id = :package_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/wf-templates/ticket-info-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/wf-templates/ticket-info-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/wf-templates/ticket-info-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + + select tt.ticket_id, + ttracker_option.option_name(tc.package_id, 'severity', tt.severity) as pretty_severity, + ttracker_option.option_name(tc.package_id, 'priority', tt.priority) as pretty_priority, + tt.subject, + tc.name as category, + cr.content as description, + cr.mime_type + from ttracker_tickets tt, + ttracker_categories tc, + cr_revisions cr + where tt.ticket_id = :ticket_id + and tt.category_id = tc.category_id + and cr.revision_id = content_item.get_live_revision(:ticket_id) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/wf-templates/ticket-info-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/wf-templates/ticket-info-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/wf-templates/ticket-info-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + +select tt.ticket_id, + ttracker_option__option_name(tc.package_id, 'severity', tt.severity) + as pretty_severity, + ttracker_option__option_name(tc.package_id, 'priority', tt.priority) + as pretty_priority, + tt.subject, + tc.name as category, + cr.content as description, + cr.mime_type + from ttracker_tickets tt, ttracker_categories tc, cr_revisions cr + where tt.ticket_id = :ticket_id and + tt.category_id = tc.category_id and + cr.revision_id = content_item__get_live_revision(:ticket_id) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/category-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/category-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/category-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + + select name, + description, + nvl(acs_object.name(default_assignee), 'N/A') as assignee_name + from ttracker_categories + where category_id = :category_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/category-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/category-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/category-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + + select name, + description, + coalesce(acs_object__name(default_assignee), 'N/A') as assignee_name + from ttracker_categories + where category_id = :category_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/index-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,74 @@ + + + + oracle8.1.6 + + + + select apm_package.name(:package_id) from dual + + + + + + + + + select tt.ticket_id, + tt.subject, + ttracker_option.option_name(:package_id, 'severity', tt.severity) as pretty_severity, + ttracker_option.option_name(:package_id, 'priority', tt.priority) as pretty_priority, + tt.creation_date as created, + ca.case_id, + tc.name as category, + ta.task_id, + ta.transition_key as to_do + from ttracker_tickets tt, + wf_cases ca, + ttracker_categories tc, + wf_task_assignments w, + wf_tasks ta, party_approved_member_map map + where ca.object_id = tt.ticket_id + and ca.state = 'active' + and tt.category_id = tc.category_id + and tc.package_id = :package_id + and w.party_id = map.party_id + and map.member_id = :user_id + and ta.case_id = ca.case_id + and ta.task_id = w.task_id + and (ta.state='enabled' or (ta.state='started' and ta.holding_user=:user_id)) + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + + + + + + select tt.ticket_id, + ttracker_option.option_name(:package_id, 'severity', tt.severity) as pretty_severity, + ttracker_option.option_name(:package_id, 'priority', tt.priority) as pretty_priority, + tt.subject, + to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created, + tc.name as category, + ta.task_id + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases ca, + wf_tasks ta + where tc.package_id = :package_id + and tt.category_id = tc.category_id + and ca.object_id = tt.ticket_id + and ca.state = 'active' + and ta.case_id = ca.case_id + and not exists (select tasn.task_id + from wf_task_assignments tasn + where tasn.task_id = ta.task_id) + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/index-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/index-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,81 @@ + + + + postgresql7.1 + + + + +select apm_package__name(:package_id) + + + + + + + + + +select tt.ticket_id, + tt.subject, + ttracker_option__option_name(:package_id, 'severity', tt.severity) + as pretty_severity, + ttracker_option__option_name(:package_id, 'priority', tt.priority) + as pretty_priority, + tt.creation_date as created, + ca.case_id, + tc.name as category, + ta.task_id, + ta.transition_key as to_do + from ttracker_tickets tt, + wf_cases ca, + ttracker_categories tc, + wf_task_assignments w, + wf_tasks ta, party_approved_member_map map + where ca.object_id = tt.ticket_id and + ca.state = 'active' and + tt.category_id = tc.category_id and + tc.package_id = :package_id and + w.party_id = map.party_id and + map.member_id = :user_id and + ta.case_id = ca.case_id and + ta.task_id = w.task_id and + (ta.state='enabled' or + (ta.state='started' and ta.holding_user=:user_id)) + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + + + + + +select tt.ticket_id, + ttracker_option__option_name(:package_id, 'severity', tt.severity) + as pretty_severity, + ttracker_option__option_name(:package_id, 'priority', tt.priority) + as pretty_priority, + tt.subject, + to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created, + tc.name as category, + ta.task_id + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases ca, + wf_tasks ta + where tc.package_id = :package_id and + tt.category_id = tc.category_id and + ca.object_id = tt.ticket_id and + ca.state = 'active' and + ta.case_id = ca.case_id and + not exists (select tasn.task_id + from wf_task_assignments tasn + where tasn.task_id = ta.task_id) + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/index.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select category_id, name + from ttracker_categories + where package_id = :package_id + order by upper(name) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/selection-bar.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/selection-bar.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/selection-bar.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + + select name, + function, + value + from ttracker_options + where package_id = :package_id + order by function,value + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/state-change.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/state-change.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/state-change.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select case_id, state + from wf_cases + where object_id = :ticket_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add-2-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,35 @@ + + + + oracle8.1.6 + + + + + declare + v_ticket_id acs_objects.object_id%TYPE; + begin + v_ticket_id := ttracker_ticket.new ( + context_id => :package_id, + subject => :subject, + description => :description, + mime_type => :mime_type, + category_id => :category_id, + severity => :severity, + priority => :priority, + creation_user => :user_id, + creation_ip => :creation_ip + ); + + acs_permission.grant_permission( + object_id => v_ticket_id, + grantee_id => :user_id, + privilege => 'write' + ); + end; + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add-2-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,39 @@ + + + + postgresql7.1 + + + + +declare + v_ticket_id acs_objects.object_id%TYPE; +begin + select ttracker_ticket__new ( + null, -- ticket_id + 'ttracker_ticket', -- object_type + :package_id, -- context_id + :subject, -- subject + :description, -- description + :mime_type, -- mime_type + :category_id, -- category_id + :severity, -- severity + :priority, -- priority + now(), -- creation_date + :user_id, -- creation_user + :creation_ip -- creation_ip + ) into v_ticket_id; + + perform acs_permission__grant_permission( + v_ticket_id, -- object_id + :user_id, -- grantee_id + 'write' -- privilege + ); + +end; + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-add.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + + select category_id, name + from ttracker_categories + where package_id = :package_id + order by upper(name) + + + + + + + + + select name, + value, + function + from ttracker_options + where package_id = :package_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + + begin + ttracker_ticket.update_mime_subj_desc ( + ticket_id => :ticket_id, + mime_type => :mime_type, + subject => :subject, + description => :description + ); + end; + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + +select ttracker_ticket__update_mime_subj_desc ( + :ticket_id, -- ticket_id + :mime_type, -- mime_type + :subject, -- subject + :description -- description + ); + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-2.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,18 @@ + + + + + + + update ttracker_tickets + set category_id = :category_id, + subject = :subject, + severity = :severity, + priority = :priority + where ticket_id = :ticket_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + + select tt.ticket_id as id, + tt.subject, + tt.priority, + tt.severity, + tt.category_id, + cr.mime_type, + cr.content as description + from ttracker_tickets tt, + cr_revisions cr + where tt.ticket_id = :ticket_id + and cr.revision_id = content_item.get_live_revision(:ticket_id) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,25 @@ + + + + postgresql7.1 + + + + + select tt.ticket_id as id, + tt.subject, + tt.priority, + tt.severity, + tt.category_id, + cr.mime_type, + cr.content as description + from ttracker_tickets tt, + cr_revisions cr + where tt.ticket_id = :ticket_id + and cr.revision_id = content_item__get_live_revision(:ticket_id) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-edit.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,28 @@ + + + + + + + select category_id, name + from ttracker_categories + where package_id = :package_id + + + + + + + + + select name, + value, + function + from ttracker_options + where package_id = :package_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-view-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-view-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-view-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,60 @@ + + + + oracle8.1.6 + + + + + select decode(acs_permission.permission_p(:ticket_id, :user_id, 'write'), 't', 1, 0) as write_p, + decode(acs_permission.permission_p(:ticket_id, :user_id, 'admin'), 't', 1, 0) as admin_p + from dual + + + + + + + + + select tt.subject, + cr.content as description, + cr.mime_type, + ttracker_option.option_name(:package_id, 'severity', tt.severity) as pretty_severity, + ttracker_option.option_name(:package_id, 'priority', tt.priority) as pretty_priority, + to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created, + tc.name as category, + tc.category_id, + acs_object.name(tt.creation_user) as submitted_by, + wc.state, + wc.case_id + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases wc, + cr_revisions cr + where tt.ticket_id = :ticket_id + and tc.category_id = tt.category_id + and wc.object_id = :ticket_id + and cr.revision_id = content_item.get_live_revision(tt.ticket_id) + + + + + + + + + select acs_object.name(wta.party_id) as name + from wf_task_assignments wta + where wta.task_id = (select max(wt.task_id) + from wf_tasks wt, + wf_cases wc + where wc.object_id = :ticket_id + and wt.case_id = wc.case_id + and wt.transition_key = 'resolve') + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-view-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-view-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/ticket-view-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,65 @@ + + + + postgresql7.1 + + + + +select case when acs_permission__permission_p(:ticket_id, :user_id, 'write') = 't' + then 1 else 0 end as write_p, + case when acs_permission__permission_p(:ticket_id, :user_id, 'admin') = 't' + then 1 else 0 end as admin_p + + + + + + + + + +select tt.subject, + cr.content as description, + cr.mime_type, + ttracker_option__option_name(:package_id, 'severity', tt.severity) + as pretty_severity, + ttracker_option__option_name(:package_id, 'priority', tt.priority) + as pretty_priority, + to_char(tt.creation_date, 'MM/DD/YYYY HH24:MI') as created, + tc.name as category, + tc.category_id, + acs_object__name(tt.creation_user) as submitted_by, + wc.state, + wc.case_id + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases wc, + cr_revisions cr + where tt.ticket_id = :ticket_id and + tc.category_id = tt.category_id and + wc.object_id = :ticket_id and + cr.revision_id = content_item__get_live_revision(tt.ticket_id) + + + + + + + + + +select acs_object__name(wta.party_id) as name + from wf_task_assignments wta + where wta.task_id = (select max(wt.task_id) + from wf_tasks wt, + wf_cases wc + where wc.object_id = :ticket_id + and wt.case_id = wc.case_id + and wt.transition_key = 'resolve') + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/tickets.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/tickets.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/tickets.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,30 @@ + + + + + + + select name, + value + from ttracker_options + where package_id = :package_id + and function = 'priority' + + + + + + + + + select name, + value + from ttracker_options + where package_id = :package_id + and function = 'priority' + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,68 @@ + + + + oracle8.1.6 + + + + +select wta.party_id, + acs_object.name(wta.party_id) as party_name, + count(ticket_id) as total, + sum(decode(lower(wc.state),'active',1,0)) as active, + sum(decode(lower(wc.state),'suspended',1,0)) as suspended, + sum(decode(lower(wc.state),'canceled',1,0)) as canceled, + sum(decode(lower(wc.state),'finished',1,0)) as finished, + max(tt.creation_date) as latest, + min(tt.creation_date) as oldest + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases wc, + wf_task_assignments wta + where tc.package_id = :package_id and + tt.category_id = tc.category_id and + tt.ticket_id = wc.object_id and + wta.task_id = (select max(wt.task_id) + from wf_tasks wt + where wt.case_id = wc.case_id and + wt.transition_key = 'resolve') + [ad_dimensional_sql $dimensional] + group by wta.party_id + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + + + + + +tt.creation_date + 365 > sysdate + + + + + + + + + +tt.creation_date + 90 > sysdate + + + + + + + + + +tt.creation_date + 30 > sysdate + + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,68 @@ + + + + postgresql7.1 + + + + +select wta.party_id, + acs_object__name(wta.party_id) as party_name, + count(ticket_id) as total, + sum(case when lower(wc.state)='active' then 1 else 0 end) as active, + sum(case when lower(wc.state)='suspended' then 1 else 0 end) as suspended, + sum(case when lower(wc.state)='canceled' then 1 else 0 end) as canceled, + sum(case when lower(wc.state)='finished' then 1 else 0 end) as finished, + max(tt.creation_date) as latest, + min(tt.creation_date) as oldest + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases wc, + wf_task_assignments wta + where tc.package_id = :package_id and + tt.category_id = tc.category_id and + tt.ticket_id = wc.object_id and + wta.task_id = (select max(wt.task_id) + from wf_tasks wt + where wt.case_id = wc.case_id and + wt.transition_key = 'resolve') + [ad_dimensional_sql $dimensional] + group by wta.party_id + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + + + + + +tt.creation_date + '365 days'::interval > now() + + + + + + + + + +tt.creation_date + '90 days'::interval > now() + + + + + + + + + +tt.creation_date + '30 days'::interval > now() + + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-assign-summary.tcl 4 Jun 2001 16:53:27 -0000 1.2 @@ -23,9 +23,9 @@ # dimensional slider definition set dimensional { {posted "In the last" any { - {year "year" {where "tt.creation_date + 365 > sysdate"}} - {3month "3 months" {where "tt.creation_date + 90 > sysdate"}} - {month "month" {where "tt.creation_date + 30 > sysdate"}} + {year "year" {where "[db_map posted_last_year]"}} + {3month "3 months" {where "[db_map posted_last_3mos]"}} + {month "month" {where "[db_map posted_last_month]"}} {any "all" {}} }} } Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,63 @@ + + + + oracle8.1.6 + + + + + select tt.creation_user as user_id, + acs_object.name(tt.creation_user) as user_name, + count(ticket_id) as total, + sum(decode(lower(wfc.state),'active',1,0)) as active, + sum(decode(lower(wfc.state),'suspended',1,0)) as suspended, + sum(decode(lower(wfc.state),'canceled',1,0)) as canceled, + sum(decode(lower(wfc.state),'finished',1,0)) as finished, + max(tt.creation_date) as latest, + min(tt.creation_date) as earliest + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases wfc + where tc.package_id = :package_id and + tt.category_id = tc.category_id and + tt.ticket_id = wfc.object_id + [ad_dimensional_sql $dimensional] + group by tt.creation_user + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + + + + + +tt.creation_date + 365 > sysdate + + + + + + + + + +tt.creation_date + 90 > sysdate + + + + + + + + + +tt.creation_date + 30 > sysdate + + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,62 @@ + + + + oracle8.1.6 + + + + +select tt.creation_user as user_id, + acs_object__name(tt.creation_user) as user_name, + count(ticket_id) as total, + sum(case when lower(wfc.state)='active' then 1 else 0 end) as active, + sum(case when lower(wfc.state)='suspended' then 1 else 0 end) as suspended, + sum(case when lower(wfc.state)='canceled' then 1 else 0 end) as canceled, + sum(case when lower(wfc.state)='finished' then 1 else 0 end) as finished, + max(tt.creation_date) as latest, + min(tt.creation_date) as earliest + from ttracker_tickets tt, + ttracker_categories tc, + wf_cases wfc + where tc.package_id = :package_id and + tt.category_id = tc.category_id and + tt.ticket_id = wfc.object_id + [ad_dimensional_sql $dimensional] + group by tt.creation_user + [ad_order_by_from_sort_spec $orderby $table_def] + + + + + + + + + +tt.creation_date + '365 days'::interval > now() + + + + + + + + + +tt.creation_date + '90 days'::interval > now() + + + + + + + + + +tt.creation_date + '30 days'::interval > now() + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/user-summary.tcl 4 Jun 2001 16:53:27 -0000 1.2 @@ -23,9 +23,9 @@ # dimensional slider definition set dimensional { {posted "In the last" any { - {year "year" {where "tt.creation_date + 365 > sysdate"}} - {3month "3 months" {where "tt.creation_date + 90 > sysdate"}} - {month "month" {where "tt.creation_date + 30 > sysdate"}} + {year "year" {where "[db_map posted_last_year]"}} + {3month "3 months" {where "[db_map posted_last_3mos]"}} + {month "month" {where "[db_map posted_last_month]"}} {any "all" {}} }} } Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + + begin + :1 := ttracker_category.new ( + context_id => :package_id, + creation_user => :user_id, + creation_ip => :creation_ip, + package_id => :package_id, + name => :name, + default_assignee => :default_assignee, + description => :description + ); + end; + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,26 @@ + + + + postgresql7.1 + + + + +select ttracker_category__new ( + null, -- category_id + :package_id, -- package_id + 'ttracker_category', -- object_type + now(), -- creation_date + :user_id, -- creation_user + :creation_ip, -- creation_ip + :package_id, -- context_id + :name, -- name + :default_assignee, -- default_assignee + :description -- description +) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-2.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select count(1) as duplicate + from ttracker_categories + where upper(name) = upper(:name) and + package_id = :package_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select 0 as party_id, 'Nobody' as party_name from dual + UNION ALL + select p.party_id, acs_object.name(p.party_id) as party_name + from parties p + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + + select 0 as party_id, 'Nobody' as party_name + UNION ALL + select p.party_id, acs_object__name(p.party_id) as party_name + from parties p + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-add.tcl 4 Jun 2001 16:53:27 -0000 1.2 @@ -41,14 +41,3 @@ } ad_return_template "category-ae" - - - - - - - - - - - Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-delete-2.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,46 @@ + + + + + + + select count(category_id) as num_categories + from ttracker_categories + where package_id = :package_id + + + + + + + + + update ttracker_tickets + set category_id = :transfer_id + where category_id = :category_id + + + + + + + + + delete from ttracker_categories + where category_id = :category_id + + + + + + + + + select count(category_id) + from ttracker_categories + where package_id = :package_id + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-delete.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,38 @@ + + + + + + + select count(category_id) + from ttracker_categories + where package_id = :package_id + + + + + + + + + select count(ticket_id) + from ttracker_tickets + where category_id = :category_id + + + + + + + + + select name, category_id + from ttracker_categories + where package_id = :package_id + and category_id <> :category_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-2.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,29 @@ + + + + + + + select category_id + from ttracker_categories + where upper(name) = upper(:name) and + package_id = :package_id and + category_id != :category_id + + + + + + + + update ttracker_categories + set name = :name, + description = :description, + default_assignee = :default_assignee + where category_id = :category_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,18 @@ + + + + oracle8.1.6 + + + + + select 0 as party_id, 'Nobody' as party_name from dual + UNION + select p.party_id, acs_object.name(p.party_id) as party_name + from parties p + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,18 @@ + + + + postgresql7.1 + + + + + select 0 as party_id, 'Nobody' as party_name + UNION + select p.party_id, acs_object__name(p.party_id) as party_name + from parties p + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit.tcl,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit.tcl 20 Apr 2001 20:51:25 -0000 1.1 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit.tcl 4 Jun 2001 16:53:27 -0000 1.2 @@ -48,7 +48,3 @@ } ad_return_template "category-ae" - - - - Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/category-edit.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select name, description, default_assignee + from ttracker_categories + where category_id = :category_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/index.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,31 @@ + + + + + + + select name, category_id + from ttracker_categories + where package_id = :package_id + order by name + + + + + + + + + select name, + value, + function, + option_id + from ttracker_options + where package_id = :package_id + order by function,value + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-add-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-add-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-add-2-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,37 @@ + + + + oracle8.1.6 + + + + + select count(*) as duplicate + from dual + where exists (select option_id + from ttracker_options + where package_id = :package_id + and function = :function + and (value = :value or upper(name) = upper(:name))) + + + + + + + + + begin + ttracker_option.add_option ( + package_id => :package_id, + function => :function, + name => :name, + value => :value + ); + end; + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-add-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-add-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-add-2-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,35 @@ + + + + postgresql7.1 + + + + +select count(*) as duplicate + where exists (select option_id + from ttracker_options + where package_id = :package_id and + function = :function and (value = :value or + upper(name) = upper(:name))) + + + + + + + + +select ttracker_option__add_option ( + null, -- option_id + :package_id, -- package_id + :function, -- function + :name, -- name + :value -- value +) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + + select count(*) as in_use + from dual + where exists (select tt.ticket_id + from ttracker_options ts, + ttracker_tickets tt, + ttracker_categories tc + where ts.option_id = :option_id + and tt.category_id = tc.category_id + and tc.package_id = ts.package_id + and ((ts.function = 'severity' and ts.value = tt.severity) or + (ts.function = 'priority' and ts.value = tt.priority))) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,25 @@ + + + + postgresql7.1 + + + + + select count(*) as in_use + + where exists (select tt.ticket_id + from ttracker_options ts, + ttracker_tickets tt, + ttracker_categories tc + where ts.option_id = :option_id + and tt.category_id = tc.category_id + and tc.package_id = ts.package_id + and ((ts.function = 'severity' and ts.value = tt.severity) or + (ts.function = 'priority' and ts.value = tt.priority))) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-delete.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + + delete from ttracker_options + where option_id = :option_id + and not exists (select tt.ticket_id + from ttracker_options ts, + ttracker_tickets tt, + ttracker_categories tc + where ts.option_id = :option_id + and tt.category_id = tc.category_id + and tc.package_id = ts.package_id + and ((ts.function = 'severity' and ts.value = tt.severity) or + (ts.function = 'priority' and ts.value = tt.priority))) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,22 @@ + + + + oracle8.1.6 + + + + + select count(*) as duplicate + from dual + where exists (select option_id + from ttracker_options + where package_id = :package_id + and function = :function + and option_id <> :option_id + and (value = :value or upper(name) = upper(:name))) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,22 @@ + + + + postgresql7.1 + + + + + select count(*) as duplicate + + where exists (select option_id + from ttracker_options + where package_id = :package_id + and function = :function + and option_id <> :option_id + and (value = :value or upper(name) = upper(:name))) + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit-2.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + update ttracker_options + set name = :name, + value = :value + where option_id = :option_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/option-edit.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,17 @@ + + + + + + + select name, + value, + function + from ttracker_options + where option_id = :option_id + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/sender-designate-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/sender-designate-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/sender-designate-oracle.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,28 @@ + + + + oracle8.1.6 + + + + + select ttracker_util.notification_sender(:package_id) as party_id, + acs_object.name(ttracker_util.notification_sender(:package_id)) as name + from dual + + + + + + + + + select party_id, + acs_object.name(party_id) as name + from parties + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/sender-designate-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/sender-designate-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/sender-designate-postgresql.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,26 @@ + + + + postgresql7.1 + + + + +select ttracker_util__notification_sender(:package_id) as party_id, + acs_object__name(ttracker_util__notification_sender(:package_id)) as name + + + + + + + + + +select party_id, acs_object__name(party_id) as name from parties + + + + + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/install/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/install/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker/www/admin/install/index.xql 4 Jun 2001 16:53:27 -0000 1.1 @@ -0,0 +1,45 @@ + + + + + + + select count(*) as gc_mounted_p + from site_nodes sn, + apm_packages ap + where ap.package_key = 'general-comments' + and sn.object_id = ap.package_id + + + + + + + + + select count(*) as workflow_mounted_p + from site_nodes sn, + apm_packages ap + where ap.package_key = 'acs-workflow' + and sn.object_id = ap.package_id + + + + + + + + + select apv.attr_value as dubious_url + from apm_parameter_values apv, + apm_parameters ap + where ap.package_key = 'acs-kernel' + and ap.parameter_name = 'SystemURL' + and ap.parameter_id = apv.parameter_id + and ap.default_value = apv.attr_value + + + + + +