-- creates the calendar object
--
-- @author Gary Jin (gjin@arsdigita.com)
-- @creation-date Nov 17, 2000
-- @cvs-id $Id: calendar-create.sql,v 1.1.1.1 2001/04/23 23:09:38 donb Exp $
--

------------------------------------------------------------------
-- calendar system permissions 
------------------------------------------------------------------
 
  -- creating the basic set of permissions for cal_item
  --
  -- 1  create: create an new item 
  -- 2. read: can view the cal_item
  -- 3. write: edit an existing cal_item
  -- 4. delete: can delete the cal_item
  -- 5. invite: can allow other parties to view or edit the cal_item
begin
        acs_privilege.create_privilege('cal_item_create', 'Add an new item'); 
        acs_privilege.create_privilege('cal_item_read',   'view an cal_item');
        acs_privilege.create_privilege('cal_item_write',  'Edit an exsiting cal_item');
        acs_privilege.create_privilege('cal_item_delete', 'Delete cal_item' );
        acs_privilege.create_privilege('cal_item_invite', 'Allow others to view cal_item'); 

          -- bind the calendar permissions to the golbal names

        acs_privilege.add_child('create', 'cal_item_create'); 
        acs_privilege.add_child('read', 'cal_item_read'); 
        acs_privilege.add_child('write', 'cal_item_write'); 
        acs_privilege.add_child('delete', 'cal_item_delete'); 

end;
/
show errors

  -- creating the addition set of permissions for calendar. 
  -- these are going to be used as status markers for the calendar
  -- 
  -- 1. calendar_on: calendar has been selected
  -- 2. calendar_show: user wants to view events from the calendar
  -- 3. calendar_hide: user does not want to view events from the calendar

begin
        acs_privilege.create_privilege('calendar_on', 'Implies that a calendar is selected'); 
        acs_privilege.create_privilege('calendar_show', 'Show a calendar');

          -- bind the calendar permissions to the golbal names

        acs_privilege.add_child('read', 'calendar_on'); 
        acs_privilege.add_child('read', 'calendar_show');         
end;
/
show errors

  -- creating the basic set of permissions for calendar. 
  --
  -- 1. calendar_create: make a new calendar 
  -- 2. calendar_read: can view all items on an exsiting calendar
  -- 3. calendar_write: can edit all items on an exsiting calendar 
  -- 4. calendar_delete: delete an existing calendar 


begin
        acs_privilege.create_privilege('calendar_create', 'Create a new calendar'); 
        acs_privilege.create_privilege('calendar_read', 'View items on an exsiting calendar');
        acs_privilege.create_privilege('calendar_write', 'Edit items of an exsiting calendar');
        acs_privilege.create_privilege('calendar_delete','Delete an calendar' );


          -- bind the calendar permissions to the golbal names

        acs_privilege.add_child('create', 'calendar_create'); 
        acs_privilege.add_child('read', 'calendar_read'); 
        acs_privilege.add_child('write', 'calendar_write'); 
        acs_privilege.add_child('delete', 'calendar_delete'); 

          -- bind the cal_item permissions to the calendar permissions

          -- When a calendar has the permission of public, 
          -- it implies that all the default permission to magic group 'public'
          -- have the permissions of "calendar_read"
        
          -- When a calendar has the permission of private
          -- it implies that the only group that will have the permission
          -- "calendar_read" would the group that the calendar belong to.

        acs_privilege.add_child('calendar_create', 'cal_item_create'); 
        acs_privilege.add_child('calendar_read', 'cal_item_read'); 
        acs_privilege.add_child('calendar_write', 'cal_item_write'); 
        acs_privilege.add_child('calendar_delete', 'cal_item_delete'); 
        
end;
/
show errors

  -- Assign the four basic permissions to more specific roles and conditions
  --
  -- calendar_admin is assigned by the owner when the calendar
  -- is created. A calendar_admin can grant any combinations 
  -- or read, write, delete and invite to any member of the party
  -- on a cal_item basis or on a calendar basis(all items). 
begin
        acs_privilege.create_privilege('calendar_admin', 'calendar adminstrator');
        acs_privilege.add_child('admin', 'calendar_admin');
        acs_privilege.add_child('calendar_admin', 'calendar_read');
        acs_privilege.add_child('calendar_admin', 'calendar_write');
        acs_privilege.add_child('calendar_admin', 'calendar_delete');
        acs_privilege.add_child('calendar_admin', 'calendar_create');
        acs_privilege.add_child('calendar_admin', 'cal_item_invite');

end;
/
show errors




---------------------------------------------------------- 
--  calendar_ojbect 
----------------------------------------------------------- 
 
begin
          -- create the calendar object

        acs_object_type.create_type (
                supertype       =>      'acs_object',
                object_type     =>      'calendar',
                pretty_name     =>      'Calendar',
                pretty_plural   =>      'Calendars',
                table_name      =>      'calendars',
                id_column       =>      'calendar_id'
        );
end;
/
show errors
 
declare 
        attr_id acs_attributes.attribute_id%TYPE; 
begin
        attr_id := acs_attribute.create_attribute ( 
                object_type     =>      'calendar', 
                attribute_name  =>      'owner_id', 
                pretty_name     =>      'Owner', 
                pretty_plural   =>      'Owners', 
                datatype        =>      'integer' 
        ); 
   
        attr_id := acs_attribute.create_attribute ( 
                object_type     =>      'calendar', 
                attribute_name  =>      'private_p', 
                pretty_name     =>      'Private Calendar', 
                pretty_plural   =>      'Private Calendars', 
                datatype        =>      'string' 
        ); 

        attr_id := acs_attribute.create_attribute ( 
                object_type     =>      'calendar', 
                attribute_name  =>      'calendar_name', 
                pretty_name     =>      'Calendar Name', 
                pretty_plural   =>      'Calendar Names', 
                datatype        =>      'string' 
        ); 
end;
/
show errors


  -- Calendar is a collection of events. Each calendar must
  -- belong to somebody (a party).
create table calendars (
          -- primary key
        calendar_id             integer         
                                constraint calendars_calendar_id_fk 
                                references acs_objects
                                constraint calendars_calendar_id_pk 
                                primary key,
          -- the name of the calendar
        calendar_name           varchar2(200),
          -- the name of the calendar in a system should be unique
          -- to avoid confusion
        constraint calendar_calendar_name_un unique (calendar_name),
          -- the individual or party that owns the calendar        
        owner_id                integer
                                constraint calendars_calendar_owner_id_fk 
                                references parties
                                on delete cascade,
          -- keep track of package instances
        package_id              integer
                                constraint calendars_package_id_fk
                                references apm_packages(package_id)
                                on delete cascade,
          -- whether or not the calendar is a private personal calendar or a 
          -- public calendar. 
        private_p               varchar2(1)
                                default 'f'
                                constraint calendars_prviate_p_ck 
                                check (private_p in ( 
                                        't',
                                        'f'
                                        )
                                )       
);

comment on table calendars is '
        Table calendars maps the many to many relationship betweens
        calendar and its owners. 
';

comment on column calendars.calendar_id is '
        Primary Key
';

comment on column calendars.calendar_name is '
        the name of the calendar. This would be unique to avoid confusion
';

comment on column calendars.owner_id is '
        the individual or party that owns the calendar
';

comment on column calendars.package_id is '
        keep track of package instances
';


-------------------------------------------------------------
-- Load cal_item_object
-------------------------------------------------------------
@@cal-item-create

-------------------------------------------------------------
-- create package calendar
-------------------------------------------------------------
 
create or replace package calendar
as
        function new (
                calendar_id             in acs_objects.object_id%TYPE           default null,
                calendar_name           in calendars.calendar_name%TYPE         default null,
                object_type             in acs_objects.object_type%TYPE         default 'calendar',
                owner_id                in calendars.owner_id%TYPE              ,
                private_p               in calendars.private_p%TYPE             default 'f',
                package_id              in calendars.package_id%TYPE            default null,           
                context_id              in acs_objects.context_id%TYPE          default null,
                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

        ) return calendars.calendar_id%TYPE;
 
        procedure delete (
                calendar_id             in calendars.calendar_id%TYPE
        );

          -- figures out the name of the calendar       
        function name (
                calendar_id             in calendars.calendar_id%TYPE
        ) return calendars.calendar_name%TYPE;

          -- returns 't' if calendar is private and 'f' if its not
        function private_p (
                calendar_id             in calendars.calendar_id%TYPE
        ) return char;


          -- returns 't' if calendar is viewable by the given party
          -- this implies that the party has calendar_read permission
          -- on this calendar
        function readable_p (
                calendar_id             in calendars.calendar_id%TYPE,
                party_id                in parties.party_id%TYPE
        ) return char;

          -- returns 't' if party wants to be able to select 
          -- this calendar, and return 'f' otherwise. 
        function show_p (
                calendar_id             in calendars.calendar_id%TYPE,
                party_id                in parties.party_id%TYPE
        ) return char;
                

          ----------------------------------------------------------------
          -- Helper functions for calendar generations:
          --
          -- These functions are used for assist in calendar 
          -- generation. Putting them in the PL/SQL level ensures that
          -- the date date will be the same, and allowing adoptation 
          -- to a different language much easier and faster.
          --             
          -- current month name
        function month_name (
                current_date    date
        ) return char;
          
          -- next month
        function next_month (
                current_date    date
        ) return date;
          
          -- prev month
        function prev_month (
                current_date    date
        ) return date;

          -- number of days in the month
        function num_day_in_month (
                current_date    date
        ) return integer;

          -- first day to be displayed in a month. 
        function first_displayed_date (
                current_date    date
        ) return date;

          -- last day to be displayed in a month. 
        function last_displayed_date (
                current_date    date
        ) return date;          
          
end calendar;
/
show errors;
 
 
create or replace package body calendar
as 

        function new (
                calendar_id             in acs_objects.object_id%TYPE           default null,
                calendar_name           in calendars.calendar_name%TYPE         default null,
                object_type             in acs_objects.object_type%TYPE         default 'calendar',
                owner_id                in calendars.owner_id%TYPE              , 
                private_p               in calendars.private_p%TYPE             default 'f',
                package_id              in calendars.package_id%TYPE            default null,
                context_id              in acs_objects.context_id%TYPE          default null,
                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

        ) 
        return calendars.calendar_id%TYPE
   
        is
                v_calendar_id           calendars.calendar_id%TYPE;

        begin
                v_calendar_id := acs_object.new (
                        object_id       =>      calendar_id,
                        object_type     =>      object_type,
                        creation_date   =>      creation_date,
                        creation_user   =>      creation_user,
                        creation_ip     =>      creation_ip,
                        context_id      =>      context_id
                );
        
                insert into     calendars
                                (calendar_id, calendar_name, owner_id, package_id, private_p)
                values          (v_calendar_id, calendar_name, owner_id, package_id, private_p);


                  -- each calendar has three default conditions
                  -- 1. all items are public
                  -- 2. all items are private
                  -- 3. no default conditions
                  -- 
                  -- calendar being public implies granting permission
                  -- calendar_read to the group 'the_public' and 'registered users'
                  --         
                  -- calendar being private implies granting permission 
                  -- calendar_read to the owner party/group of the party
                  --
                  -- by default, we grant "calendar_admin" to
                  -- the owner of the calendar
                acs_permission.grant_permission (
                        object_id       =>      v_calendar_id,
                        grantee_id      =>      owner_id,
                        privilege       =>      'calendar_admin'
                );
                
 
                return v_calendar_id;
        end new;
 


          -- body for procedure delete
        procedure delete (
                calendar_id             in calendars.calendar_id%TYPE
        )
        is
  
        begin
                  -- First erase all the item relate to this calendar.
                delete from     calendars 
                where           calendar_id = calendar.delete.calendar_id;
 
                  -- Delete all privileges associate with this calendar
                delete from     acs_permissions 
                where           object_id = calendar.delete.calendar_id;

                  -- Delete all privilges of the cal_items that's associated 
                  -- with this calendar
                delete from     acs_permissions
                where           object_id in (
                                        select  cal_item_id
                                        from    cal_items
                                        where   on_which_calendar = calendar.delete.calendar_id                                                                                                                                                         
                                );
                        
 
                acs_object.delete(calendar_id);
        end delete;
 


          -- figures out the name of the calendar       
        function name (
                calendar_id             in calendars.calendar_id%TYPE
        ) 
        return calendars.calendar_name%TYPE

        is
                v_calendar_name         calendars.calendar_name%TYPE;
        begin
                select  calendar_name
                into    v_calendar_name
                from    calendars
                where   calendar_id = calendar.name.calendar_id;

                return v_calendar_name;
        end name;



          -- returns 't' if calendar is private and 'f' if its not
        function private_p (
                calendar_id             in calendars.calendar_id%TYPE
        ) 
        return char

        is
                v_private_p             char(1) := 't';
        begin
                select  private_p 
                into    v_private_p
                from    calendars
                where   calendar_id = calendar.private_p.calendar_id;

                return v_private_p;
        end private_p;



          -- returns 't' if calendar is viewable by the given party
          -- this implies that the party has calendar_read permission
          -- on this calendar
        function readable_p (
                calendar_id             in calendars.calendar_id%TYPE,
                party_id                in parties.party_id%TYPE
        ) 
        return char

        is      
                v_readable_p            char(1) := 't';
        begin
                select  decode(count(*), 1, 't', 'f') 
                into    v_readable_p
                from    acs_object_party_privilege_map 
                where   party_id = calendar.readable_p.party_id
                and     object_id = calendar.readable_p.calendar_id 
                and     privilege = 'calendar_read';

                return  v_readable_p;

        end readable_p;

          -- returns 't' if party wants to be able to select (calendar_show granted)
          -- this calendar, and .return 'f' otherwise. 
          --
          -- this seems to be a problem with the problem that when
          -- revoking the permissions using acs_permissions.revoke
          -- data is not removed from table acs_object_party_privilege_map.
        function show_p (
                calendar_id             in calendars.calendar_id%TYPE,
                party_id                in parties.party_id%TYPE
        ) 
        return char

        is
                v_show_p                char(1) := 't';
        begin
                select  decode(count(*), 1, 't', 'f') 
                into    v_show_p
                from    acs_permissions
                where   grantee_id = calendar.show_p.party_id
                and     object_id = calendar.show_p.calendar_id 
                and     privilege = 'calendar_show';

                return  v_show_p;

        end show_p;


          -- Helper functions for calendar generations:
          --
          -- These functions are used for assist in calendar 
          -- generation. Putting them in the PL/SQL level ensures that
          -- the date date will be the same, and allowing adoptation 
          -- to a different language much easier and faster.
          --             
          -- current month name
        function month_name (
                current_date            date
        ) return char
          
        is
                name    char;
        begin
                select  to_char(to_date(calendar.month_name.current_date), 'fmMonth') 
                        into name
                from    dual;
                        
                return name;
        end month_name;

        
          -- next month
        function next_month (
                current_date            date
        ) return date

        is
                v_date                  date;
        begin
                select  trunc(add_months(to_date(sysdate), -1))
                        into v_date
                from    dual;

                return v_date;          
        end next_month;
          

          -- prev month
        function prev_month (
                current_date            date
        ) return date
        
        is
                v_date                  date;
        begin
                select  trunc(add_months(to_date(sysdate), -1))
                        into v_date
                from    dual;

                return v_date;
        end prev_month;

          -- number of days in the month
        function num_day_in_month (
                current_date    date
        ) return integer

        is
                v_num   integer;
        begin
                select  to_char(last_day(to_date(sysdate)), 'DD')
                        into v_num
                from    dual;

                return v_num;
        end num_day_in_month;

          -- first day to be displayed in a month. 
        function first_displayed_date (
                current_date    date
        ) return date

        is
                v_date          date;
        begin
                select  next_day(trunc(to_date(sysdate), 'Month') - 7, 'SUNDAY')
                        into v_date
                from    dual;

                return  v_date;
        end first_displayed_date;

          -- last day to be displayed in a month. 
        function last_displayed_date (
                current_date    date
        ) return date

        is
                v_date          date;
        begin
                select  next_day(last_day(to_date(sysdate)), 'SATURDAY')
                        into v_date
                from    dual;

                return v_date;
        end last_displayed_date;
         
end calendar;
/
show errors
 


-----------------------------------------------------------------
-- load related sql files
-----------------------------------------------------------------
-- 
@@cal-table-create










