--
-- $Id: photo-album-lite-plsql.sql,v 1.1 2001/04/20 20:51:11 donb Exp $
--

set define off;

-- 1. photos

create or replace package pl_photo
as
  function new (
       photo_id	       in pl_photos.photo_id%TYPE default null,
       folder_id       in pl_photos.folder_id%TYPE default null,
       exposure_date   in pl_photos.exposure_date%TYPE default null,
       caption	       in pl_photos.caption%TYPE default null,
       client_filename in pl_photos.client_filename%TYPE default null,
       file_extension  in pl_photos.file_extension%TYPE default null,
       object_type     in acs_object_types.object_type%TYPE default 'pl_photo',
       creation_date   in acs_objects.creation_date%TYPE 
                       default sysdate, 
       creation_user   in acs_objects.creation_user%TYPE 
                       default null, 
       creation_ip     in acs_objects.creation_ip%TYPE default null, 
       context_id      in acs_objects.context_id%TYPE default null 
  ) return pl_photos.photo_id%TYPE;

  function next_photo_id (
       folder_id       in pl_folders.folder_id%TYPE,
       sort_n	       in pl_photos.sort_n%TYPE,
       party_id	       in parties.party_id%TYPE
                        default acs.magic_object_id('the_public'),
       privilege       in acs_privileges.privilege%TYPE default 'read'
  ) return pl_photos.photo_id%TYPE;

  function prev_photo_id (
       folder_id       in pl_folders.folder_id%TYPE,
       sort_n	       in pl_photos.sort_n%TYPE,
       party_id	       in parties.party_id%TYPE
                        default acs.magic_object_id('the_public'),
       privilege       in acs_privileges.privilege%TYPE default 'read'
  ) return pl_photos.photo_id%TYPE;

  function exp_date (
       photo_id        in pl_photos.photo_id%TYPE
  ) return date;

  function folder_title (
       photo_id	       in pl_photos.photo_id%TYPE
  ) return pl_folders.title%TYPE;

  function name (
       photo_id	       in pl_photos.photo_id%TYPE
  ) return pl_photos.client_filename%TYPE;

  function default_name (
       photo_id	       in pl_photos.photo_id%TYPE
  ) return pl_photos.client_filename%TYPE;

  procedure apply_default_name (
       photo_id	       in pl_photos.photo_id%TYPE
  );
       
  procedure delete (
       photo_id	       in pl_photos.photo_id%TYPE
  );
end pl_photo;
/
show errors

create or replace package body pl_photo
as
  -- Note: you must call pl_reshuffle within the same transaction.
  function new (
       photo_id	       in pl_photos.photo_id%TYPE default null,
       folder_id       in pl_photos.folder_id%TYPE default null,
       exposure_date   in pl_photos.exposure_date%TYPE default null,
       caption	       in pl_photos.caption%TYPE default null,
       client_filename in pl_photos.client_filename%TYPE default null,
       file_extension  in pl_photos.file_extension%TYPE default null,
       object_type     in acs_object_types.object_type%TYPE default 'pl_photo',
       creation_date   in acs_objects.creation_date%TYPE 
                       default sysdate, 
       creation_user   in acs_objects.creation_user%TYPE 
                       default null, 
       creation_ip     in acs_objects.creation_ip%TYPE default null, 
       context_id      in acs_objects.context_id%TYPE default null 
  ) return pl_photos.photo_id%TYPE
  is
    v_photo_id integer;
  begin
    v_photo_id := acs_object.new ( 
      object_id     => photo_id, 
      object_type   => object_type, 
      creation_date => creation_date, 
      creation_user => creation_user, 
      creation_ip   => creation_ip, 
      context_id    => context_id 
    );

    insert into pl_photos (
      photo_id,
      folder_id,
      exposure_date,
      sort_n,
      caption,
      client_filename,
      file_extension )
    values (
      v_photo_id,
      folder_id,
      exposure_date,
      v_photo_id,
      caption,
      client_filename,
      file_extension );

    acs_permission.grant_permission (
       object_id => v_photo_id,
       grantee_id => creation_user,
       privilege => 'admin'
    );

    return v_photo_id;
  end new;

  function next_photo_id (
       folder_id       in pl_folders.folder_id%TYPE,
       sort_n	       in pl_photos.sort_n%TYPE,
       party_id	       in parties.party_id%TYPE
                       default acs.magic_object_id('the_public'),
       privilege       in acs_privileges.privilege%TYPE default 'read'
  ) return pl_photos.photo_id%TYPE
  is
      v_photo_id pl_photos.photo_id%TYPE;
  begin
      select p.photo_id into v_photo_id
      from pl_v_photos p
      where p.folder_id = pl_photo.next_photo_id.folder_id
        and p.sort_n in (select min(sort_n)
                         from pl_v_photos p2
                         where p2.folder_id = pl_photo.next_photo_id.folder_id
                           and p2.sort_n > pl_photo.next_photo_id.sort_n
                           and acs_permission.permission_p(p2.photo_id,
                                pl_photo.next_photo_id.party_id,
                                pl_photo.next_photo_id.privilege) = 't');
      return v_photo_id;
  end next_photo_id;

  function prev_photo_id (
       folder_id       in pl_folders.folder_id%TYPE,
       sort_n	       in pl_photos.sort_n%TYPE,
       party_id	       in parties.party_id%TYPE
                       default acs.magic_object_id('the_public'),
       privilege       in acs_privileges.privilege%TYPE default 'read'
  ) return pl_photos.photo_id%TYPE
  is
      v_photo_id pl_photos.photo_id%TYPE;
  begin
      select p.photo_id into v_photo_id
      from pl_v_photos p
      where p.folder_id = pl_photo.prev_photo_id.folder_id
        and p.sort_n in (select max(sort_n)
                         from pl_v_photos p2
                         where p2.folder_id = pl_photo.prev_photo_id.folder_id
                           and p2.sort_n < pl_photo.prev_photo_id.sort_n
                           and acs_permission.permission_p(p2.photo_id,
                                pl_photo.prev_photo_id.party_id,
                                pl_photo.prev_photo_id.privilege) = 't');
      return v_photo_id;
  end prev_photo_id;

  function exp_date (
       photo_id        in pl_photos.photo_id%TYPE
  ) return date
  is
       v_exp_date date;
  begin
       select exposure_date into v_exp_date
       from pl_photos p
       where p.photo_id = pl_photo.exp_date.photo_id;

       return v_exp_date;
  end exp_date;

  function folder_title (
       photo_id	       in pl_photos.photo_id%TYPE
  ) return pl_folders.title%TYPE
  is
       v_title pl_folders.title%TYPE;
  begin
       select f.title into v_title
       from pl_folders f, pl_photos p
       where p.photo_id = pl_photo.folder_title.photo_id
         and f.folder_id = p.folder_id;

       return v_title;
  end folder_title;

  function name (
       photo_id	       in pl_photos.photo_id%TYPE
  ) return pl_photos.client_filename%TYPE
  is
	v_client_filename pl_photos.client_filename%TYPE;
  begin
	select client_filename into v_client_filename
	from pl_photos
	where photo_id = pl_photo.name.photo_id;
	return 'Photo: ' || v_client_filename;
  end;

  function default_name (
       photo_id	       in pl_photos.photo_id%TYPE
  ) return pl_photos.client_filename%TYPE
  is
	v_client_filename pl_photos.client_filename%TYPE;
  begin
        select decode(f.default_prefix,NULL,p.client_filename,f.default_prefix || '-' || to_char(p.sort_n,'TM') || p.file_extension) into v_client_filename
        from pl_photos p,
             pl_folders f
        where p.photo_id = pl_photo.default_name.photo_id
          and p.folder_id = f.folder_id;

	return v_client_filename;
  end;

  procedure apply_default_name (
       photo_id	       in pl_photos.photo_id%TYPE
  )
  is
    v_client_filename pl_photos.client_filename%TYPE;
  begin
    select pl_photo.default_name(pl_photo.apply_default_name.photo_id)
    into v_client_filename from dual;
    update pl_photos
    set client_filename = v_client_filename
    where photo_id = pl_photo.apply_default_name.photo_id;
  end apply_default_name;

  procedure delete (
       photo_id	       in pl_photos.photo_id%TYPE
  )
  is
  begin
    delete from pl_photos
    where photo_id = pl_photo.delete.photo_id;
  end delete;

end pl_photo;
/
show errors;

-- 2. folders

create or replace package pl_folder
as
  function new (
       folder_id       in pl_folders.folder_id%TYPE default null,
       title           in pl_folders.title%TYPE default null,
       descr	       in pl_folders.descr%TYPE default null,
       default_exp_date in pl_folders.default_exp_date%TYPE default null,
       default_prefix  in pl_folders.default_prefix%TYPE default null,
       object_type     in acs_object_types.object_type%TYPE default 'pl_folder',
       creation_date   in acs_objects.creation_date%TYPE
                       default sysdate, 
       creation_user   in acs_objects.creation_user%TYPE 
                       default null, 
       creation_ip     in acs_objects.creation_ip%TYPE default null, 
       context_id      in acs_objects.context_id%TYPE default null 
  ) return pl_folders.folder_id%TYPE;

  function photo_count (
       folder_id       in pl_folders.folder_id%TYPE
  ) return integer;

  function name (
       folder_id       in pl_folders.folder_id%TYPE
  ) return pl_folders.title%TYPE;

  procedure delete (
       folder_id       in pl_folders.folder_id%TYPE
  );

end pl_folder;
/
show errors

create or replace package body pl_folder
as
  function new (
       folder_id       in pl_folders.folder_id%TYPE default null,
       title           in pl_folders.title%TYPE default null,
       descr	       in pl_folders.descr%TYPE default null,
       default_exp_date in pl_folders.default_exp_date%TYPE default null,
       default_prefix  in pl_folders.default_prefix%TYPE default null,
       object_type     in acs_object_types.object_type%TYPE default 'pl_folder',
       creation_date   in acs_objects.creation_date%TYPE
                       default sysdate, 
       creation_user   in acs_objects.creation_user%TYPE 
                       default null, 
       creation_ip     in acs_objects.creation_ip%TYPE default null, 
       context_id      in acs_objects.context_id%TYPE default null 
  ) return pl_folders.folder_id%TYPE
  is
    v_folder_id integer;
  begin
    v_folder_id := acs_object.new ( 
      object_id     => folder_id, 
      object_type   => object_type, 
      creation_date => creation_date, 
      creation_user => creation_user, 
      creation_ip   => creation_ip, 
      context_id    => context_id 
    );

    insert into pl_folders (
      folder_id,
      title,
      descr,
      default_exp_date,
      default_prefix
    ) values (
      v_folder_id,
      title,
      descr,
      default_exp_date,
      default_prefix );

    acs_permission.grant_permission (
       object_id => v_folder_id,
       grantee_id => creation_user,
       privilege => 'admin'
    );

    return v_folder_id;

  end new;

  function photo_count (
       folder_id       in pl_folders.folder_id%TYPE
  ) return integer
  is
    v_photo_count integer;
  begin
    select count(photo_id) into v_photo_count
    from pl_v_photos
    where folder_id = pl_folder.photo_count.folder_id;

    return v_photo_count;
  end photo_count;

  function name (
       folder_id       in pl_folders.folder_id%TYPE
  ) return pl_folders.title%TYPE
  is
	v_title		pl_folders.title%TYPE;
  begin
  	select title into v_title
	from pl_folders
	where folder_id = pl_folder.name.folder_id;
	return 'Folder: ' || v_title;
  end;

  procedure delete (
       folder_id       in pl_folders.folder_id%TYPE
  )
  is
  begin
    delete from pl_folders where folder_id = pl_folder.delete.folder_id;
  end delete;

end pl_folder;
/
show errors;

create or replace function pl_photo_date_format (
  date_to_format in date
)
return varchar
is
begin
  if date_to_format is null then
     return null;
  end if;

  return to_char(date_to_format,'fmDD') || '&nbsp;' || to_char(date_to_format,'fmMon') || '&nbsp;' || to_char(date_to_format,'YYYY');
end pl_photo_date_format;
/
show errors;

create or replace function pl_relative_date (
  the_date date
)
return varchar
is
begin
  if trunc(the_date) = trunc(sysdate) then
    return to_char(the_date,'fmHH:fmMI:SSam');
  else 
    return pl_photo_date_format(the_date);
  end if;
end pl_relative_date;
/
show errors;

create or replace procedure pl_reshuffle
is
 cursor cur is
  select distinct folder_id as id
  from pl_reshuffle_queue;
begin
 for folder in cur loop
   for photo in (select p.*,
                        rownum as row_num
                 from (select photo_id as id
                       from pl_v_photos
                       where folder_id = folder.id
                       order by sort_n) p) loop
    update pl_photos set sort_n = photo.row_num where photo_id = photo.id;
   end loop;
   delete from pl_reshuffle_queue
   where folder_id = folder.id;
 end loop;
end pl_reshuffle;
/
show errors;

set define on;