-- /packages/ticket-tracker-lite/sql/views-and-plsql.sql
--
-- @author dvr@arsdigita.com
-- @created 2001-02-18
--
---------------------------------------------------

-- All areas

create or replace view ttl_v_areas as
select o.*, a.*
from acs_objects o, ttl_areas a
where o.object_id = a.area_id;

-- All active areas

create or replace view ttl_va_areas as
select * from ttl_v_areas where active_p = 't';

-- All tickets

create or replace view ttl_v_tickets as 
select o.*, t.* 
from acs_objects o, ttl_tickets t
where t.ticket_id = o.object_id;

-- All active tickets

create or replace view ttl_va_tickets as 
select * from ttl_v_tickets where active_p = 't';

-- All active comments

create or replace view ttl_va_comments
as select * from ttl_comments where active_p = 't';

-- The time of the last comment for each ticket

create or replace view ttl_v_comments_latest as 
select * from ttl_va_comments c
where c.comment_id = (select max(c2.comment_id) 
                      from ttl_comments c2
                      where c.ticket_id = c2.ticket_id);

-- All users who assignable to tickets

create or replace view ttl_v_users as
select 
    object_id as package_id,
    grantee_id as user_id,
    privilege
from acs_permissions
where privilege = 'write';

create or replace function ttl_relative_date (
    --
    -- if the date is today, show just the time. Otherwise 
    -- return the date.
    --
    v_date IN date
) return varchar
as
  v_sysdate            date;
begin                 

  v_sysdate := sysdate;

  if trunc(v_date) = trunc(sysdate) then
      return to_char(v_date, 'fmhh:fmmi am');
  else 
      return to_char(v_date, 'month fmdd, yyyy');
  end if;

end ttl_relative_date;
/
show errors

create or replace function ttl_top_privilege_for_object (
    --
    -- Return the top privilege the user has for this object
    -- At the moment this is either 'admin' or 'write'
    --
    v_object_id in integer,
    v_user_id   in integer
) return acs_privileges.privilege%TYPE
as
    v_privilege         acs_privileges.privilege%TYPE;
    v_admin_p           integer;
begin

    select count(*) into v_admin_p
    from dual
    where acs_permission.permission_p(v_object_id, v_user_id, 'admin') = 't';

    if v_admin_p = 1 then
        v_privilege := 'admin';
    else 
        select privilege into v_privilege
        from acs_permissions
        where object_id = v_object_id
        and grantee_id = v_user_id
        and privilege = 'write';
    end if;

    return v_privilege;

end;
/
show errors;

create or replace function ttl__create_file (
	p_package_id		   in apm_packages.package_id%TYPE, 
	p_user_id			   in users.user_id%TYPE, 
	p_name				   in cr_items.name%TYPE, 
	p_parent_id			   in cr_items.parent_id%TYPE, 
	p_mime_type			   in cr_mime_types.mime_type%TYPE,
	p_file_extension	   in cr_mime_types.file_extension%TYPE
) return cr_revisions.revision_id%TYPE
as
	v_item_id			cr_items.item_id%TYPE;
	v_revision_id		cr_revisions.revision_id%TYPE;
	v_mime_type			cr_mime_types.mime_type%TYPE;
begin

	begin
		-- detect mime type if mime is not yet in cr_mime_type and add it
		select mime_type into v_mime_type from cr_mime_types
		  where  mime_type = p_mime_type;
 
		exception
		  when NO_DATA_FOUND then
			insert into cr_mime_types 
			(mime_type, file_extension) 
			values 
			(p_mime_type, p_file_extension);
	end;

	-- if the file already exists then nuke it. this become essentially write
	select max(item_id) into v_item_id from cr_items where name = p_name;

	if v_item_id is not null then
	   content_item.del(v_item_id);
	end if;

	-- vinodk: this needs to be rewritten to use CR API
	-- oracle - use lob instead of file

	-- create a new cr_item
	v_item_id := acs_object.new (
		null, 
		'content_item', 
		sysdate, 
		p_user_id, 
		null, 
		p_package_id
	);

	insert into cr_items 
	(item_id, parent_id, name, content_type, storage_type) 
	values 
	(v_item_id, p_parent_id, p_name, 'content_revision', 'lob');

	-- create a new cr_revision
	v_revision_id := acs_object.new (
		null, 
		'content_revision', 
		sysdate, 
		p_user_id, 
		null, 
		null
	);

	insert into cr_revisions 
	(revision_id, item_id, mime_type) 
	values 
	(v_revision_id, v_item_id, p_mime_type);

	return v_revision_id;
end;
/
show errors;