/*****************************************************************************
*
* Filename         - $Workfile:   log_flight_event.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live schema stored procedure log_flight_event
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   log_flight_event.sql  $  $Revision:   1.1  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/log_flight_event.svl  $
* 
*    Rev 1.1   20 Sep 1999 14:41:34   tagtrak
*  
* 
*    Rev 1.0   28 Sep 1998 13:19:38   tagtrak
* OR: 12265
* CR: 12265
* REV:
* Venna enhancement - Log incoming flight data.
* Andrew Walker
*
****************************************************************************/

PROMPT create or replace procedure log_flight_event

 

create or replace procedure log_flight_event (
	p_flight_no        in    char,
        p_flight_date      in    char,
        p_flight_direction in    char,
        p_flight_id        in    number,
        p_event_key        in    char,
	p_event            in    varchar2,
	p_location         in    char,
	p_user_id          in    number,
	p_start_time      out    char,
        p_end_time        out    char,
        p_error_code      out    number
) as

timestamp date;
l_start_time date;
depair    pax.departure_airport_code%type := null;

l_agent_code     tagtrak_users.agent_code%TYPE; -- Agent associated with current user
l_event_duration flight_event_types.event_duration%TYPE; -- Duration of event 
l_user_id        flight_events.user_id%TYPE; -- User who created open interval log
l_flight_id      flight_events.flight_id%TYPE; -- Flight ID of Outbound flight

begin

   depair    := get_offset.get_user_location(p_user_id); 
   timestamp := sysdate + get_offset.airport_code(depair);

   -- Check if we need to remove the flight ID
   l_flight_id := p_flight_id;
   IF (p_flight_direction = 'I') THEN
      l_flight_id := NULL;
   END IF;

   IF ASCII(p_event_key) = 0 OR p_event_key IS NULL THEN
      -- Use the standard flight events mechanism
      insert into flight_events (
         flight_no,
         flight_date,
         flight_direction,
         flight_id,
         event_description,
         location_code,
         user_id,
         log_time)
       values (
         p_flight_no,
         p_flight_date,
         p_flight_direction,
         l_flight_id,
         p_event,
         p_location,
         p_user_id,
         timestamp
      );

      p_start_time := to_char(timestamp, 'HH24:MI');

   ELSE

      BEGIN
      -- Obtain the agent associated with the user
      SELECT agent_code INTO l_agent_code FROM tagtrak_users
         WHERE user_id = p_user_id;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            -- Return an error as the user doesn't exist in TAGTRAK_USERS table
            p_error_code := -102;
      END;

      IF p_error_code IS NULL THEN
         -- Agent code was retrieved so we can use this to get the event duration
         BEGIN
         -- Obtain the event duration
         SELECT event_duration INTO l_event_duration FROM flight_event_types 
            WHERE event_key = p_event_key AND
                  agent_code = l_agent_code AND
                  flight_direction IN (p_flight_direction,'B') AND
                  ROWNUM = 1;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               -- Return an error as the event doesn't exist in FLIGHT_EVENT_TYPES
               p_error_code := -1413;
         END;
      END IF; -- Agent code was successfully retrieved

      IF p_error_code IS NULL THEN
         -- Okay to continue processing

         IF l_event_duration = 'S' THEN
            -- This is a snapshot event so create an event using the Standard
            -- method
            insert into flight_events (
               flight_no,
               flight_date,
               flight_direction,
               flight_id,
               event_description,
               event_duration,
               event_key,
               location_code,
               user_id,
               log_time)
            values (
               p_flight_no,
               p_flight_date,
               p_flight_direction,
               l_flight_id,
               p_event,
               l_event_duration,
               p_event_key,
               p_location,
               p_user_id,
               timestamp
            );

            p_start_time := to_char(timestamp, 'HH24:MI');


         ELSE
            -- This is an Enhanced Interval event

            -- Look for an existing open event. Match the flight, event_key and agent
            BEGIN
               SELECT o.user_id, o.log_time INTO l_user_id, l_start_time FROM open_flight_events_v o, tagtrak_users t
                  WHERE o.user_id = t.user_id AND
                        o.flight_no = p_flight_no AND
                        o.flight_date = p_flight_date AND
                        o.flight_direction = p_flight_direction AND
                        o.event_key = p_event_key AND
                        t.agent_code = l_agent_code;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  -- A matching row was not found. No need to do anything here
                  NULL;
            END;

            IF l_user_id IS NOT NULL THEN
               -- There is an open event so this log will close it.
               -- Match against the user id to ensure the log for the same agent is updated
               UPDATE flight_events SET end_log_time = timestamp,
                  end_user_id = p_user_id,
                  end_location_code = p_location
                  WHERE flight_no = p_flight_no AND
                        event_key = p_event_key AND
                        end_log_time IS NULL AND
                        event_duration = 'I' AND
                        flight_direction = p_flight_direction AND
                        user_id = l_user_id;
          
               p_start_time := to_char(l_start_time, 'HH24:MI');
               p_end_time := to_char(timestamp, 'HH24:MI');

            ELSE
               -- Create a new flight event

               insert into flight_events (
                  flight_no,
                  flight_date,
                  flight_direction,
                  flight_id,
                  event_description,
                  event_duration,
                  event_key,
                  location_code,
                  user_id,
                  log_time)
               values (
                  p_flight_no,
                  p_flight_date,
                  p_flight_direction,
                  l_flight_id,
                  p_event,
                  l_event_duration,
                  p_event_key,
                  p_location,
                  p_user_id,
                  timestamp
               );
            
               p_start_time := to_char(timestamp, 'HH24:MI');
   
            END IF; -- Event is/is not open
         END IF; -- Snapshot or Interval event
      END IF; -- Successfully obtained event data
   END IF; -- Use Standard or Enhanced logging

end log_flight_event;
/
