--
-- 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.12 2003/05/17 12:33:30 jeffd Exp $
--
-- Ported to OpenACS - Vinod Kurup (vkurup@massmed.org)
--

----------------------------------
-- PACKAGE BODIES
----------------------------------

-- Package: ttracker_ticket

create function ttracker_ticket__new (integer,varchar,integer,varchar,varchar,varchar,integer,integer,integer,timestamptz,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
	v_ticket_id := acs_message__new (
		p_ticket_id,	   -- message_id    
		null,			   -- reply_to
		now(),			   -- sent_date
		null,			   -- sender
		null,			   -- rfc822_id
		p_subject,		   -- title         
		null,			   -- description
		p_mime_type,	   -- mime_type     
		p_description,	   -- text          
		null,			   -- data
		0,				   -- parent_id
		p_context_id,	   -- context_id    
		p_creation_user,   -- creation_user 
		p_creation_ip,	   -- creation_ip   
		p_object_type,	   -- object_type   
		''t''			   -- is_live
    );

	-- 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
    v_case_id := 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 
	);

	-- 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
	);                                                        

	-- set the permissions
	--   We are granting ''write'' permissions instead of ''admin'' since
	--   we do not want the user to have privileges of managing
	--   permissions on this ticket. 
	perform acs_permission__grant_permission(
		v_ticket_id,		-- object_id
		p_creation_user,	-- grantee_id
		''write''			-- privilege
	);        

	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_ticket_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';
         
-- Package: ttracker_category

create function ttracker_category__new (integer,integer,varchar,timestamptz,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';
        
-- Package: ttracker_option

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';
    
-- Package: ttracker_util

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''
        and ap.parameter_id = apv.parameter_id;

	if not found then
		v_result := -1;
	else
		v_result := to_number(v_attr_value, ''999999999'');
	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 p_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';

-- Package: ttracker_callback

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
		''f''					-- permanent_p
	);

	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
		''f''					-- permanent_p
	);

	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
		p_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
		p_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;
    v_party_from		integer;  -- FIXME (kludge for inout param)
	v_body				varchar;  -- FIXME (kludge for inout param)
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 v_party_from;

	-- get the package url to use as return_url in the email
	select site_node__url(node_id) into v_url
	from site_nodes where object_id = v_package_id;

	v_subject := ''Ticket #'' || v_ticket_id || '' -- '' || p_custom_arg;
	v_body := ''Subject: '' || v_subject || ''
'' || ''To do: '' || p_custom_arg || ''
'' || ''Manage via: '' || ttracker_util__workflow_url(''t'') || ''task?task_id='' || p_task_id || ''&return_url='' || v_url;

   -- acs-workflow has been changed. actual notification
   -- should be sent from callback now, thus it is called now
   --  vinodk

   perform acs_mail_nt__post_request (       
		   v_party_from,                 -- party_from
		   p_party_to,					 -- party_to
		   ''f'',                        -- expand_group
		   v_subject,                    -- subject
		   v_body,                       -- message
		   0                             -- max_retries
	);

	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;
	v_url				varchar(1000);
	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;

	-- get the package url to use as return_url in the email
	select site_node__url(node_id) into v_url
	from site_nodes where object_id = v_package_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 acs_mail_nt__post_request (
			ttracker_util__notification_sender(v_package_id),	-- party_from
			admin_rec.user_id,									-- party_to
			''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 || ''&return_url='' || v_url                        -- message
		) into v_request_id;
	end loop;

	return 0;
end;' language 'plpgsql';