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