create or replace package acs_event as function new ( -- Creates a new event (20.10.10) -- @author W. Scott Meeks -- @param event_id optional id to use for new event -- @param name optional Name of the new event -- @param description optional Description of the new event -- @param html_p optional Description is html -- @param status_summary optional status information to add to name -- @param timespan_id optional initial time interval set -- @param activity_id optional initial activity -- @param recurrence_id optional id of recurrence information -- @param object_type 'acs_event' -- @param creation_date default sysdate -- @param creation_user acs_object param -- @param creation_ip acs_object param -- @param context_id acs_object param -- @param location location -- @param related_link_url URL -- @param related_link_text anchor for URL -- @param redirect_to_rel_link_p activation flag -- -- @return The id of the new event. -- event_id in acs_events.event_id%TYPE default null, name in acs_events.name%TYPE default null, description in acs_events.description%TYPE default null, html_p in acs_events.html_p%TYPE default null, status_summary in acs_events.status_summary%TYPE default null, timespan_id in acs_events.timespan_id%TYPE default null, activity_id in acs_events.activity_id%TYPE default null, recurrence_id in acs_events.recurrence_id%TYPE default null, object_type in acs_object_types.object_type%TYPE default 'acs_event', 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, package_id in acs_objects.package_id%TYPE default null, location in acs_events.location%TYPE default null, related_link_url in acs_events.related_link_url%TYPE default null, related_link_text in acs_events.related_link_text%TYPE default null, redirect_to_rel_link_p in acs_events.locationredirect_to_rel_link_p%TYPE default null ) return acs_events.event_id%TYPE; procedure del ( -- Deletes an event (20.10.40) -- Also deletes party mappings (via on delete cascade). -- If this is the last instance of a recurring event, the recurrence -- info is deleted as well -- @author W. Scott Meeks -- @param event_id id of event to delete -- event_id in acs_events.event_id%TYPE ); procedure delete_all ( -- Deletes all instances of an event. -- @author W. Scott Meeks -- @param event_id All events with the same recurrence_id as this one will be deleted. -- event_id in acs_events.event_id%TYPE ); procedure delete_all_recurrences ( -- Deletes all instances of an event. -- @author W. Scott Meeks -- @param recurrence_id All events with this recurrence_id will be deleted. -- recurrence_id in recurrences.recurrence_id%TYPE default null ); function get_name ( -- Returns the name or the name of the activity associated with the event if -- name is null. -- @author W. Scott Meeks -- @param event_id id of event to get name for -- event_id in acs_events.event_id%TYPE ) return acs_events.name%TYPE; function get_description ( -- Returns the description or the description of the activity associated -- with the event if description is null. -- @author W. Scott Meeks -- @param event_id id of event to get description for -- event_id in acs_events.event_id%TYPE ) return acs_events.description%TYPE; function get_html_p ( -- Returns html_p or html_p of the activity associated with the event if -- html_p is null. -- @author W. Scott Meeks -- @param event_id id of event to get html_p for -- event_id in acs_events.event_id%TYPE ) return acs_events.html_p%TYPE; function get_status_summary ( -- Returns status_summary or status_summary of the activity associated with the event if -- status_summary is null. -- @author W. Scott Meeks -- @param event_id id of event to get status_summary for -- event_id in acs_events.event_id%TYPE ) return acs_events.status_summary%TYPE; procedure timespan_set ( -- Sets the time span for an event (20.10.15) -- @author W. Scott Meeks -- @param event_id id of event to update -- @param timespan_id new time interval set -- event_id in acs_events.event_id%TYPE, timespan_id in timespans.timespan_id%TYPE ); procedure recurrence_timespan_edit ( event_id in acs_events.event_id%TYPE, start_date in time_intervals.start_date%TYPE, end_date in time_intervals.end_date%TYPE, edit_past_events in char default 't' ); procedure activity_set ( -- Sets the activity for an event (20.10.20) -- @author W. Scott Meeks -- @param event_id id of event to update -- @param activity_id new activity -- event_id in acs_events.event_id%TYPE, activity_id in acs_activities.activity_id%TYPE ); procedure party_map ( -- Adds a party mapping to an event (20.10.30) -- @author W. Scott Meeks -- @param event_id event to add mapping to -- @param party_id party to add mapping for -- event_id in acs_events.event_id%TYPE, party_id in parties.party_id%TYPE ); procedure party_unmap ( -- Deletes a party mapping from an event (20.10.30) -- @author W. Scott Meeks -- @param event_id id of event to delete mapping from -- @param party_id id of party to delete mapping for -- event_id in acs_events.event_id%TYPE, party_id in parties.party_id%TYPE ); function recurs_p ( -- Returns 't' if event recurs, 'f' otherwise (20.50.40) -- @author W. Scott Meeks -- @param event_id id of event to check -- @return 't' or 'f' -- event_id in acs_events.event_id%TYPE ) return char; function instances_exist_p ( -- Returns 't' if events with the given recurrence_id exist, 'f' otherwise -- @author W. Scott Meeks -- @param recurrence_id id of recurrence to check -- @return 't' or 'f' -- recurrence_id in acs_events.recurrence_id%TYPE ) return char; procedure insert_instances ( -- This is the key procedure creating recurring events. This procedure -- uses the interval set and recurrence information referenced by the event -- to insert additional information to represent the recurrences. -- Events will be added up until the earlier of recur_until and -- cutoff_date. The procedure enforces a hard internal -- limit of adding no more than 10,000 recurrences at once to reduce the -- risk of demolishing the DB because of application bugs. The date of the -- last recurrence added is marked as the db_populated_until date. -- -- The application is responsible for calling this function again if -- necessary to populate to a later date. -- -- @author W. Scott Meeks -- @param event_id The id of the event to recur. If the -- event's recurrence_id is null, nothing happens. -- @param cutoff_date optional If provided, determines how far out to -- prepopulate the DB. If not provided, then -- defaults to sysdate plus the value of the -- EventFutureLimit site parameter. event_id in acs_events.event_id%TYPE, cutoff_date in date default null ); procedure shift ( -- Shifts the timespan of an event by the given offsets. -- @author W. Scott Meeks -- @param event_id Event to shift. -- @param start_offset optional If provided, adds this number to the -- start_dates of the timespan of the event. -- No effect on any null start_date. -- @param end_offset optional If provided, adds this number to the -- end_dates of the timespan of the event. -- No effect on any null end_date. -- event_id in acs_events.event_id%TYPE default null, start_offset in number default 0, end_offset in number default 0 ); procedure shift_all ( -- Shifts the timespan of all instances of a recurring event -- by the given offsets. -- @author W. Scott Meeks -- @param event_id All events with the same -- recurrence_id as this one will be shifted. -- @param start_offset optional If provided, adds this number to the -- start_dates of the timespan of the event -- instances. No effect on any null start_date. -- @param end_offset optional If provided, adds this number to the -- end_dates of the timespan of the event -- instances. No effect on any null end_date. -- event_id in acs_events.event_id%TYPE default null, start_offset in number default 0, end_offset in number default 0 ); procedure shift_all ( -- Same as above but invoked using recurrence Id recurrence_id in recurrences.recurrence_id%TYPE default null, start_offset in number default 0, end_offset in number default 0 ); end acs_event; / show errors create or replace package body acs_event as function new ( event_id in acs_events.event_id%TYPE default null, name in acs_events.name%TYPE default null, description in acs_events.description%TYPE default null, html_p in acs_events.html_p%TYPE default null, status_summary in acs_events.status_summary%TYPE default null, timespan_id in acs_events.timespan_id%TYPE default null, activity_id in acs_events.activity_id%TYPE default null, recurrence_id in acs_events.recurrence_id%TYPE default null, object_type in acs_object_types.object_type%TYPE default 'acs_event', 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, package_id in acs_objects.package_id%TYPE default null, location in acs_events.location%TYPE default null related_link_url in acs_events.related_link_url%TYPE default null, related_link_text in acs_events.related_link_text%TYPE default null, redirect_to_rel_link_p in acs_events.redirect_to_rel_link_p%TYPE default NULL ) return acs_events.event_id%TYPE is new_event_id acs_events.event_id%TYPE; begin new_event_id := acs_object.new( object_id => event_id, object_type => object_type, title => name, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => context_id, package_id => package_id ); insert into acs_events (event_id, name, description, html_p, status_summary, activity_id, timespan_id, recurrence_id, location, related_link_url, related_link_text, redirect_to_rel_link_p) values (new_event_id, name, description, html_p, status_summary, activity_id, timespan_id, recurrence_id, location, related_link_url, related_link_text, redirect_to_rel_link_p); return new_event_id; end new; procedure del ( event_id in acs_events.event_id%TYPE ) is recurrence_id acs_events.recurrence_id%TYPE; begin select recurrence_id into recurrence_id from acs_events where event_id = acs_event.del.event_id; -- acs_events and acs_event_party_map deleted via on delete cascade acs_object.del(event_id); -- Check for no more instances and delete recurrence if exists if instances_exist_p(recurrence_id) = 'f' then recurrence.del(recurrence_id); end if; end del; procedure delete_all ( event_id in acs_events.event_id%TYPE ) is recurrence_id acs_events.recurrence_id%TYPE; begin select recurrence_id into recurrence_id from acs_events where event_id = delete_all.event_id; delete_all_recurrences(recurrence_id); end delete_all; procedure delete_all_recurrences ( recurrence_id in recurrences.recurrence_id%TYPE default null ) is cursor event_id_cursor is select event_id from acs_events where recurrence_id = delete_all_recurrences.recurrence_id; event_id event_id_cursor%ROWTYPE; begin if recurrence_id is not null then for event_id in event_id_cursor loop acs_event.del(event_id.event_id); end loop; end if; end delete_all_recurrences; -- Equivalent functionality to get_name and get_description provided by -- acs_event_activity view function get_name ( event_id in acs_events.event_id%TYPE ) return acs_events.name%TYPE is name acs_events.name%TYPE; begin select nvl(e.name, a.name) into name from acs_events e, acs_activities a where event_id = get_name.event_id and e.activity_id = a.activity_id(+); return name; end get_name; function get_description ( event_id in acs_events.event_id%TYPE ) return acs_events.description%TYPE is description acs_events.description%TYPE; begin select nvl(e.description, a.description) into description from acs_events e, acs_activities a where event_id = get_description.event_id and e.activity_id = a.activity_id(+); return description; end get_description; function get_html_p ( event_id in acs_events.event_id%TYPE ) return acs_events.html_p%TYPE is html_p acs_events.html_p%TYPE; begin select nvl(e.html_p, a.html_p) into html_p from acs_events e, acs_activities a where event_id = get_html_p.event_id and e.activity_id = a.activity_id(+); return html_p; end get_html_p; function get_status_summary ( event_id in acs_events.event_id%TYPE ) return acs_events.status_summary%TYPE is status_summary acs_events.status_summary%TYPE; begin select nvl(e.status_summary, a.status_summary) into status_summary from acs_events e, acs_activities a where event_id = get_status_summary.event_id and e.activity_id = a.activity_id(+); return status_summary; end get_status_summary; procedure timespan_set ( event_id in acs_events.event_id%TYPE, timespan_id in timespans.timespan_id%TYPE ) is begin update acs_events set timespan_id = timespan_set.timespan_id where event_id = timespan_set.event_id; end timespan_set; procedure recurrence_timespan_edit ( event_id in acs_events.event_id%TYPE, start_date in time_intervals.start_date%TYPE, end_date in time_intervals.end_date%TYPE, edit_past_events in char default 't' ) is v_timespan timespans%ROWTYPE; v_one_start_date time_intervals.start_date%TYPE; v_one_end_date time_intervals.end_date%TYPE; begin -- get the initial offsets select start_date, end_date into v_one_start_date, v_one_end_date from time_intervals, timespans, acs_events where time_intervals.interval_id = timespans.interval_id and timespans.timespan_id = acs_events.timespan_id and event_id= recurrence_timespan_edit.event_id; for v_timespan in (select * from time_intervals where interval_id in (select interval_id from timespans where timespan_id in (select timespan_id from acs_events where recurrence_id = (select recurrence_id from acs_events where event_id = recurrence_timespan_edit.event_id))) and (edit_past_events = 't' or start_date >= to_date(v_one_start_date,'YYYY-MM-DD HH24:MI:SS') )) LOOP time_interval.edit(v_timespan.interval_id, v_timespan.start_date + (start_date - v_one_start_date), v_timespan.end_date + (end_date - v_one_end_date)); END LOOP; end recurrence_timespan_edit; procedure activity_set ( event_id in acs_events.event_id%TYPE, activity_id in acs_activities.activity_id%TYPE ) as begin update acs_events set activity_id = activity_set.activity_id where event_id = activity_set.event_id; end activity_set; procedure party_map ( event_id in acs_events.event_id%TYPE, party_id in parties.party_id%TYPE ) is begin insert into acs_event_party_map (event_id, party_id) values (event_id, party_id); end party_map; procedure party_unmap ( event_id in acs_events.event_id%TYPE, party_id in parties.party_id%TYPE ) is begin delete from acs_event_party_map where event_id = party_unmap.event_id and party_id = party_unmap.party_id; end party_unmap; function recurs_p ( event_id in acs_events.event_id%TYPE ) return char is result char; begin select decode(recurrence_id, null, 'f', 't') into result from acs_events where event_id = recurs_p.event_id; return result; end recurs_p; function instances_exist_p ( recurrence_id in acs_events.recurrence_id%TYPE ) return char is result char; begin -- Only need to check if any rows exist. select count(*) into result from dual where exists (select recurrence_id from acs_events where recurrence_id = instances_exist_p.recurrence_id); if result = 0 then return 'f'; else return 't'; end if; end instances_exist_p; -- This function is used internally by insert_instances function get_value ( parameter_name in apm_parameters.parameter_name%TYPE ) return apm_parameter_values.attr_value%TYPE is package_id apm_packages.package_id%TYPE; begin select package_id into package_id from apm_packages where package_key = 'acs-events'; return apm.get_value(package_id, parameter_name); end get_value; -- This function is used internally by insert_instances function new_instance ( event_id in acs_events.event_id%TYPE, date_offset in integer ) return acs_events.event_id%TYPE is event acs_events%ROWTYPE; object acs_objects%ROWTYPE; new_event_id acs_events.event_id%TYPE; new_timespan_id acs_events.timespan_id%TYPE; begin select * into event from acs_events where event_id = new_instance.event_id; select * into object from acs_objects where object_id = event_id; new_timespan_id := timespan.copy(event.timespan_id, date_offset); new_event_id := new( name => event.name, description => event.description, html_p => event.html_p, status_summary => event.status_summary, timespan_id => new_timespan_id, activity_id => event.activity_id, recurrence_id => event.recurrence_id, creation_user => object.creation_user, creation_ip => object.creation_ip, context_id => object.context_id, package_id => object.package_id, location => event.location, related_link_url => event.related_link_url, related_link_text => event.related_link_text, redirect_to_rel_link_p => event.redirect_to_rel_link_p ); return new_event_id; end new_instance; procedure insert_instances ( event_id in acs_events.event_id%TYPE, cutoff_date in date default null ) is event acs_events%ROWTYPE; recurrence recurrences%ROWTYPE; new_event_id acs_events.event_id%TYPE; interval_name recurrence_interval_types.interval_name%TYPE; n_intervals recurrence.every_nth_interval%TYPE; days_of_week recurrence.days_of_week%TYPE; last_date_done date; stop_date date; start_date date; event_date date; diff integer; current_date date; v_last_day date; week_date date; instance_count integer; days_length integer; days_index integer; day_num integer; begin select * into event from acs_events where event_id = insert_instances.event_id; select * into recurrence from recurrences where recurrence_id = event.recurrence_id; -- Set cutoff date -- EventFutureLimit is in years. if cutoff_date is null then stop_date := add_months(sysdate, 12 * get_value('EventFutureLimit')); else stop_date := cutoff_date; end if; -- Events only populated until max(cutoff_date, recur_until) -- If recur_until null, then defaults to cutoff_date if recurrence.recur_until < stop_date then stop_date := recurrence.recur_until; end if; -- Figure out the date to start from select trunc(min(start_date)) into event_date from acs_events_dates where event_id = insert_instances.event_id; if recurrence.db_populated_until is null then start_date := event_date; else start_date := recurrence.db_populated_until; end if; current_date := start_date; last_date_done := start_date; n_intervals := recurrence.every_nth_interval; -- Case off of the interval_name to make code easier to read select interval_name into interval_name from recurrences r, recurrence_interval_types t where recurrence_id = recurrence.recurrence_id and r.interval_type = t.interval_type; -- Week has to be handled specially. -- Start with the beginning of the week containing the start date. if interval_name = 'week' then current_date := NEXT_DAY(current_date - 7, 'SUNDAY'); days_of_week := recurrence.days_of_week; days_length := LENGTH(days_of_week); end if; -- Check count to prevent runaway in case of error instance_count := 0; while instance_count < 10000 and (trunc(last_date_done) <= trunc(stop_date)) loop instance_count := instance_count + 1; -- Calculate next date based on interval type if interval_name = 'day' then current_date := current_date + n_intervals; elsif interval_name = 'month_by_date' then current_date := ADD_MONTHS(current_date, n_intervals); elsif interval_name = 'month_by_day' then -- Find last day of month before correct month v_last_day := ADD_MONTHS(LAST_DAY(current_date), n_intervals - 1); -- Find correct week and go to correct day of week current_date := NEXT_DAY(v_last_day + (7 * (to_char(current_date, 'W') - 1)), to_char(current_date, 'DAY')); elsif interval_name = 'last_of_month' then -- Find last day of correct month v_last_day := LAST_DAY(ADD_MONTHS(current_date, n_intervals)); -- Back up one week and find correct day of week current_date := NEXT_DAY(v_last_day - 7, to_char(current_date, 'DAY')); elsif interval_name = 'year' then current_date := ADD_MONTHS(current_date, 12 * n_intervals); -- Deal with custom function elsif interval_name = 'custom' then execute immediate 'current_date := ' || recurrence.custom_func || '(' || current_date || ', ' || n_intervals || ');'; end if; -- Check to make sure we're not going past Trunc because dates aren't integral exit when trunc(current_date) > trunc(stop_date); -- Have to handle week specially if interval_name = 'week' then -- loop over days_of_week extracting each day number -- add day number and insert days_index := 1; week_date := current_date; while days_index <= days_length loop day_num := SUBSTR(days_of_week, days_index, 1); week_date := current_date + day_num; if trunc(week_date) > trunc(start_date) and trunc(week_date) <= trunc(stop_date) then -- This is where we add the event new_event_id := new_instance( event_id, trunc(week_date) - trunc(event_date) ); last_date_done := week_date; elsif trunc(week_date) > trunc(stop_date) then -- Gone too far exit; end if; days_index := days_index + 2; end loop; -- Now move to next week with repeats. current_date := current_date + 7 * n_intervals; else -- All other interval types -- This is where we add the event new_event_id := new_instance( event_id, trunc(current_date) - trunc(event_date) ); last_date_done := current_date; end if; end loop; update recurrences set db_populated_until = last_date_done where recurrence_id = recurrence.recurrence_id; end insert_instances; procedure shift ( event_id in acs_events.event_id%TYPE default null, start_offset in number default 0, end_offset in number default 0 ) is begin update acs_events_dates set start_date = start_date + start_offset, end_date = end_date + end_offset where event_id = shift.event_id; end shift; procedure shift_all ( event_id in acs_events.event_id%TYPE default null, start_offset in number default 0, end_offset in number default 0 ) is begin update acs_events_dates set start_date = start_date + start_offset, end_date = end_date + end_offset where recurrence_id = (select recurrence_id from acs_events where event_id = shift_all.event_id); end shift_all; procedure shift_all ( recurrence_id in recurrences.recurrence_id%TYPE default null, start_offset in number default 0, end_offset in number default 0 ) is begin update acs_events_dates set start_date = start_date + start_offset, end_date = end_date + end_offset where recurrence_id = shift_all.recurrence_id; end shift_all; end acs_event; / show errors