-- Room Reservation Package
--
-- @author Deds Castillo (deds@infiniteinfo.com)
-- @creation-date 2002-08-28
-- @cvs-id $Id: reservations-package-create.sql,v 1.2 2004/03/17 00:48:49 carolinem Exp $
--
-- Total rewrite though some
-- concepts were taken from the old
-- ACS3.x Room Reservation package

-- let's create helper functions
select define_function_args('rr_reservations__new','reservation_id,object_type;rr_reservation,name,room_id,reserving_user,attendees,note,contact_person,contact_email,contact_phone,timespan_id,activity_id,creation_date,creation_user,creation_ip,context_id');

create or replace function rr_reservations__new (integer,varchar,varchar,integer,integer,integer,varchar,varchar,varchar,varchar,integer,integer,timestamptz,integer,varchar,integer)
returns integer as '
declare
    p_reservation_id              alias for $1;
    p_object_type                 alias for $2;
    p_name                        alias for $3;
    p_room_id                     alias for $4;
    p_reserving_user              alias for $5;
    p_attendees                   alias for $6;
    p_note                        alias for $7;
    p_contact_person              alias for $8;
    p_contact_email               alias for $9;
    p_contact_phone               alias for $10;
    p_timespan_id                 alias for $11;
    p_activity_id                 alias for $12;
    p_creation_date               alias for $13;
    p_creation_user               alias for $14;
    p_creation_ip                 alias for $15;
    p_context_id                  alias for $16;
    v_reservation_id              integer;
    v_status                      rr_reservations.status%TYPE;
    v_approval_needed_p           rr_rooms.approval_needed_p%TYPE;
begin
    v_reservation_id := acs_event__new(
	p_reservation_id,	                  -- event_id
	p_name,		                          -- name
	null,	                                  -- description
        ''f'',		                          -- html_p
        null,                                     -- status_summary
	p_timespan_id,	                          -- timespan_id
	p_activity_id,	                          -- activity_id
	null,	                                  -- recurrence_id
	p_object_type,	                          -- object_type
	p_creation_date,	                  -- creation_date
	p_creation_user,            	          -- creation_user
	p_creation_ip,	                          -- creation_ip
	coalesce(p_context_id, p_reservation_id)  -- context_id
	);
        
    insert into rr_reservations
    (reservation_id, room_id, reserving_user, attendees, note, contact_person, contact_email, contact_phone)
    values
    (v_reservation_id, p_room_id, p_reserving_user, p_attendees, p_note, p_contact_person, p_contact_email, p_contact_phone);

-- DEDS: Is this an open room?
--       If yes, then we can auto approve

    select approval_needed_p 
    into v_approval_needed_p
    from rr_rooms
    where room_id = p_room_id;

    if (v_approval_needed_p = ''f'') then
	perform rr_reservations__approve (p_reservation_id, ''Open Policy Room'',p_creation_user, p_creation_ip);
    end if;

    perform acs_object__update_last_modified(p_reservation_id, p_creation_user, p_creation_ip);

    return v_reservation_id;
end;
' language 'plpgsql';

select define_function_args('rr_reservations__edit','reservation_id,room_id,attendees,note,contact_person,contact_email,contact_phone,status,reason,action_user,action_date,action_ip');

create or replace function rr_reservations__edit (integer,integer,integer,varchar,varchar,varchar,varchar,varchar,varchar,integer,timestamp,varchar)
returns integer as '
declare
    p_reservation_id              alias for $1;
    p_room_id                     alias for $2;
    p_attendees                   alias for $3;
    p_note                        alias for $4;
    p_contact_person              alias for $5;
    p_contact_email               alias for $6;
    p_contact_phone               alias for $7;
    p_status                      alias for $8;
    p_reason                      alias for $9;
    p_action_user                 alias for $10;
    p_action_date                 alias for $11;
    p_action_ip			  alias for $12;
begin
    update rr_reservations
    set room_id = p_room_id,
	attendees = p_attendees,
        note = p_note,
	contact_person = p_contact_person,
	contact_email = p_contact_email,
	contact_phone = p_contact_phone,
        status = p_status,
        reason = p_reason,
        action_user = p_action_user,
        action_date = p_action_date
    where reservation_id = p_reservation_id;

    perform acs_object__update_last_modified(p_reservation_id,p_action_user,p_action_ip);

    return 0;
end;
' language 'plpgsql';


select define_function_args('rr_reservations__edit','reservation_id,room_id,attendees,note,contact_person,contact_email,contact_phone,edit_user,edit_ip');

create or replace function rr_reservations__edit (integer,integer,integer,varchar,varchar,varchar,varchar,integer,varchar)
returns integer as '
declare
    p_reservation_id              alias for $1;
    p_room_id                     alias for $2;
    p_attendees                   alias for $3;
    p_note                        alias for $4;
    p_contact_person              alias for $5;
    p_contact_email               alias for $6;
    p_contact_phone               alias for $7;
    p_edit_user			  alias for $8;
    p_edit_ip			  alias for $9;
begin
    update rr_reservations
    set room_id = p_room_id,
	attendees = p_attendees,
        note = p_note,
	contact_person = p_contact_person,
	contact_email = p_contact_email,
	contact_phone = p_contact_phone
    where reservation_id = p_reservation_id;

    perform acs_object__update_last_modified(p_reservation_id, p_edit_user, p_edit_ip);

    return 0;
end;
' language 'plpgsql';


select define_function_args('rr_reservations__get_status','reservation_id');

create or replace function rr_reservations__get_status (integer)
returns varchar as '
declare
        p_reservation_id        alias for $1;
	v_status	        rr_reservations.status%TYPE;
begin
	select status into v_status
   	from rr_reservations
	where reservation_id = p_reservation_id;

	return v_status;
end;
' language 'plpgsql';

-- helper function for approve/reject
select define_function_args('rr_reservations__set_status','reservation_id,reason,status,action_user,action_ip');

create or replace function rr_reservations__set_status (integer,varchar,varchar,integer,varchar)
returns integer as '
declare
    p_reservation_id              alias for $1;
    p_reason                      alias for $2;
    p_status                      alias for $3;
    p_action_user                 alias for $4;
    p_action_ip			  alias for $5;
    v_room_id                     rr_reservations.room_id%TYPE;
    v_attendees                   rr_reservations.attendees%TYPE;
    v_note                        rr_reservations.note%TYPE;
    v_contact_person              rr_reservations.contact_person%TYPE;
    v_contact_email               rr_reservations.contact_email%TYPE;
    v_contact_phone               rr_reservations.contact_phone%TYPE;
    v_action_date                 rr_reservations.action_date%TYPE;
begin
    v_action_date = now();

-- DEDS: let us get the current values of this
--       reservation so that we can pass them

    select room_id, attendees, note, contact_person, contact_email, contact_phone
    into v_room_id, v_attendees, v_note, v_contact_person, v_contact_email, v_contact_phone
    from rr_reservations
    where reservation_id = p_reservation_id;

    perform rr_reservations__edit(p_reservation_id, v_room_id, v_attendees, v_note, v_contact_person, v_contact_email, v_contact_phone, p_status, p_reason, p_action_user, v_action_date, p_action_ip);

    return 0;
end;
' language 'plpgsql';

select define_function_args('rr_reservations__approve','reservation_id,reason,action_user,action_ip');

create or replace function rr_reservations__approve (integer,varchar,integer,varchar)
returns integer as '
declare
    p_reservation_id              alias for $1;
    p_reason                      alias for $2;
    p_action_user                 alias for $3;
    p_action_ip			  alias for $4;
begin
    perform rr_reservations__set_status(p_reservation_id, p_reason, ''approved'', p_action_user, p_action_ip);

    return 0;
end;
' language 'plpgsql';

select define_function_args('rr_reservations__reject','reservation_id,reason,action_user,action_ip');

create or replace function rr_reservations__reject (integer,varchar,integer,varchar)
returns integer as '
declare
    p_reservation_id              alias for $1;
    p_reason                      alias for $2;
    p_action_user                 alias for $3;
    p_action_ip			  alias for $4;
begin
    perform rr_reservations__set_status(p_reservation_id, p_reason, ''rejected'', p_action_user, p_action_ip);

    return 0;
end;
' language 'plpgsql';


select define_function_args('rr_reservations__cancel','reservation_id,reason,action_user,action_ip');

create or replace function rr_reservations__cancel (integer,varchar,integer,varchar)
returns integer as '
declare
    p_reservation_id              alias for $1;
    p_reason                      alias for $2;
    p_action_user		  alias for $3;
    p_action_ip			  alias for $4;
begin
    perform rr_reservations__set_status(p_reservation_id, p_reason, ''canceled'', p_action_user, p_action_ip);

    return 0;
end;
' language 'plpgsql';

-- delete a reservation
select define_function_args('rr_reservations__delete','reservation_id');

create or replace function rr_reservations__delete(integer)
returns integer as '
declare
    p_reservation_id                      alias for $1;
    v_activity_id                         acs_activities.activity_id%TYPE;
    v_time_interval_id                    time_intervals.interval_id%TYPE;
    v_count                               integer;
begin
-- get the activity this reservation maps to
    select e.activity_id, t.interval_id 
           into v_activity_id, v_time_interval_id
    from acs_events e, 
         timespans t
    where e.timespan_id = t.timespan_id and
          e.event_id = p_reservation_id;

-- delete the reservation
    perform acs_object__delete(p_reservation_id);

-- delete the time interval
    delete from time_intervals where interval_id = v_time_interval_id;

-- are there any other reservations mapped to this activity
    select count(event_id) into v_count
    from acs_events
    where event_id = v_activity_id;

-- if none then delete this activity
    if (v_count = 0) then
	perform acs_activity__delete (v_activity_id);
    end if;

    return 0;
end;
' language 'plpgsql';

-- helper for time conflicts
select define_function_args('rr_reservations__conflict_p','reservation_id,room_id,start_date,end_date');

create or replace function rr_reservations__conflict_p (integer,integer,timestamp,timestamp)
returns integer as '
declare
        p_reservation_id        alias for $1;
        p_room_id               alias for $2;
        p_start_date            alias for $3;
        p_end_date              alias for $4;
	v_conflict_count	integer;
	v_conflict_result	char;
begin
	select count(event_id) into v_conflict_count
   	from acs_events e join timespans s
               on (e.timespan_id = s.timespan_id)
             join time_intervals t
               on (s.interval_id = t.interval_id)
             join rr_reservations r
               on (e.event_id = r.reservation_id)
	where r.room_id = p_room_id and
              not (r.reservation_id = p_reservation_id) and
              r.status in (''approved'', ''rejected'') and
	      ((p_start_date >= start_date and p_start_date < end_date) or
	       (start_date >= p_start_date and start_date < p_end_date));

	if (v_conflict_count > 0) then
           v_conflict_result := 1;
	else
           v_conflict_result := 0;
	end if;

	return v_conflict_result;
end;
' language 'plpgsql';