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

-- All areas

create 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 view ttl_va_areas as
select * from ttl_v_areas where active_p = 't';

-- All tickets

create 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 view ttl_va_tickets as 
select * from ttl_v_tickets where active_p = 't';

-- All active comments

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

-- The time of the last comment for each ticket

create 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 view ttl_v_users as
select 
    object_id as package_id,
    grantee_id as user_id,
    privilege
from acs_permissions_all
where privilege = 'write' or 
      privilege = 'admin';

    --
    -- if the date is today, show just the time. Otherwise 
    -- return the date.
    --
create function ttl_relative_date (timestamp)
returns varchar as '
declare
  relative_date     alias for $1;
begin
  if date_trunc(''day'', relative_date) = date_trunc(''day'', current_timestamp) then
      return to_char(relative_date, ''fmhh:fmmi am'');
  else 
      return to_char(relative_date, ''month fmdd, yyyy'');
  end if;
end;' language 'plpgsql';

    --
    -- Return the top privilege the user has for this object
    -- At the moment this is either 'admin' or 'write'
    --
create function ttl_top_privilege_for_object (integer, integer)
returns varchar as '
declare
  p_object_id          alias for $1;
  p_user_id            alias for $2;
  v_privilege        acs_privileges.privilege%TYPE default '''';
  v_admin_p          integer;
begin
    select count(*) into v_admin_p
    from dual
    where acs_permission__permission_p(p_object_id, p_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 = p_object_id
        and grantee_id = p_user_id
        and privilege = ''write'';
    end if;

    return v_privilege;
end;' language 'plpgsql';