/*****************************************************************************
*
* Filename		- handler_tracing.sql
*
* Project Name		- UltraTrak Database
*
* Source Code Identifier - UAS/URBRS/TN/10134
*
* Description		- Prodedures for use by scanner/tcmt to maintain a 
*                         log of users that have operated on a flight
*
* Language		- Oracle PL/SQL
*
* File History		- For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2003
*
*****************************************************************************/


PROMPT CREATE OR REPLACE PACKAGE handler_tracing

CREATE OR REPLACE PACKAGE handler_tracing
AS

/******************************************************************************
 *
 * Private Type Definitions
 *
 ******************************************************************************/

   TYPE handler_type     IS TABLE OF tagtrak_users.loginname%TYPE   INDEX BY BINARY_INTEGER;
   TYPE location_type    IS TABLE OF user_trace.location_code%TYPE  INDEX BY BINARY_INTEGER;
   TYPE login_time_type  IS TABLE OF handler_trace.login_time%TYPE  INDEX BY BINARY_INTEGER;
   TYPE logout_time_type IS TABLE OF handler_trace.logout_time%TYPE INDEX BY BINARY_INTEGER;

/******************************************************************************
 *
 * PROCEDURE CREATE_HANDLER_TRACE
 *
 * Description:
 *
 * This procedure creates an entry in the HANDLER_TRACE table. This should
 * be called whenever carries out an action on a flight
 *
 * Parameters:
 *
 * p_user_id          - user carrying out this operation
 * p_kit_id           - equipment id
 * p_flight_id        - flight id associated with bag (can be 0 providing non
 *                      duplicate tag or uld is supplied)
 * p_bag_tag          - if no flight given, tag can be used to identify flight
 * p_uld_no           - if no flight given, uld can be used to identify flight
 * p_pax_id           - can be used to identify flight
 * p_pax_seq          - can be used to identify flight
 *
 ******************************************************************************/

PROCEDURE create_handler_trace
(
   p_user_id       IN tagtrak_users.user_id%type,
   p_kit_id        IN kit.id%type,
   p_flight_id     IN flight.flight_id%type,
   p_flight_no     IN flight.flight_no%type,
   p_bag_tag       IN bag.bag_tag%type,
   p_uld_no        IN ulds.uld_no%type,
   p_pax_id        IN pax.pax_id%type,
   p_pax_seq       IN pax.pax_sequence_no%type
);

/******************************************************************************
 *
 * PROCEDURE MWS_HANDLER_TRACE
 *
 * Description:
 *
 * This procedure, to be called by the MWS, creates an entry in the 
 * HANDLER_TRACE table by calling create_handler_trace. Before doing this it 
 * first looks up the kit id for the given user_id/serial number. This should
 * be called whenever carries out an action on a flight
 *
 * Parameters:
 *
 * p_user_id          - user carrying out this operation
 * p_serial_number    - equipment id
 * p_flight_id        - flight id associated with bag (can be 0 providing non
 *                      duplicate tag or uld is supplied)
 * p_bag_tag          - if no flight given, tag can be used to identify flight
 * p_uld_no           - if no flight given, uld can be used to identify flight
 * p_pax_id           - can be used to identify flight
 * p_pax_seq          - can be used to identify flight
 *
 ******************************************************************************/

PROCEDURE mws_handler_trace
(
   p_user_id       IN tagtrak_users.user_id%type,
   p_serial_number IN kit.serial_number%type,
   p_flight_id     IN flight.flight_id%type,
   p_flight_no     IN flight.flight_no%type,
   p_bag_tag       IN bag.bag_tag%type,
   p_uld_no        IN ulds.uld_no%type,
   p_pax_id        IN pax.pax_id%type,
   p_pax_seq       IN pax.pax_sequence_no%type
);
/******************************************************************************
 *
 * PROCEDURE LOGOUT_HANDLER_TRACE
 *
 * Description:
 *
 * This procedure updates an entry in the HANDLER_TRACE table to set the logout
 * time. This should be called whenever a user logs out
 *
 * Parameters:
 *
 * p_user_id          - user carrying out this operation
 * p_kit_id           - equipment id
 *
 ******************************************************************************/

PROCEDURE logout_handler_trace
(
   p_user_id       IN tagtrak_users.user_id%type,
   p_kit_id        IN kit.id%type
);

/******************************************************************************
 *
 * PROCEDURE GET_HANDLER_TRACES
 *
 * Description:
 *
 * This get all entries in the HANDLER_TRACE table for the given flight
 *
 * Parameters:
 *
 * p_flight_id        - flight to obtain traces for
 * p_handler          - handler to be displayed
 * p_location         - location handler logged in at
 * p_login_time       - login time of handler
 * p_logout_time      - logout time of handler
 *
 ******************************************************************************/

PROCEDURE get_handler_traces
(
     p_flight_id   IN    flight.flight_id%type,
     p_handler     OUT handler_type,
     p_location    OUT location_type,
     p_login_time  OUT login_time_type,
     p_logout_time OUT logout_time_type
);

END handler_tracing;
/

PROMPT CREATE OR REPLACE PACKAGE BODY handler_tracing

CREATE OR REPLACE PACKAGE BODY handler_tracing
AS 

/******************************************************************************
 *
 * Private Procedures and Functions
 *
 ******************************************************************************/

/******************************************************************************
 *
 * PROCEDURE TRACING_ENABLED
 *
 * Description:
 *
 * This procedure checks whether tracing is enabled by looking in db_config
 * for an db_value of 'Y' for ENABLE_HANDLER_TRACING item.
 *
 * Parameters:
 *
 * None
 *
 ******************************************************************************/

FUNCTION tracing_enabled RETURN BOOLEAN
IS
   l_enabled_flag    db_config.db_value%type := null;
   l_tracing_enabled boolean := FALSE;
BEGIN 

   -- First check that handler tracing is enabled

  BEGIN

      SELECT db_value INTO l_enabled_flag FROM db_config WHERE
         db_item = 'ENABLE_HANDLER_TRACE';

      IF l_enabled_flag = 'Y' THEN

         -- Tracing is enabled
         l_tracing_enabled := TRUE;

      END IF;

  EXCEPTION

      -- db item not found, default to tracing not enabled
      WHEN NO_DATA_FOUND THEN
         null;
  END;

  RETURN l_tracing_enabled;

END tracing_enabled;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_FOR_TAG
 *
 * Description:
 *
 * This procedure attempts to obtain the flight id for a given tag. Note: if the
 * tag is a duplicate the flight cannot be identified
 *
 * Parameters:
 *
 * p_user_id     - Current user - used to determine site id
 * p_bag_tag     - tag to find flight for
 * p_flight_id   - returned flight id
 * p_return_code - error code (0 if flight found)
 *
 ******************************************************************************/

PROCEDURE get_flight_for_tag
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_bag_tag     IN  bag.bag_tag%type,
   p_flight_id   OUT flight.flight_id%type,
   p_return_code OUT integer
)
IS
   l_tag1             CHAR(1)  := NULL;
   l_tag2a            CHAR(3)  := NULL;
   l_tag2b            CHAR(3)  := NULL;
   l_tag3             NUMBER(6):= NULL;
   l_tagno_num        CHAR(12) := NULL;
   l_tag_type         NUMBER   := NULL;
BEGIN

   p_return_code := 0;

   BEGIN
      split_tags (  tagno     => p_bag_tag
                   ,tag1      => l_tag1
                   ,tag2a     => l_tag2a
                   ,tag2b     => l_tag2b
                   ,tag3      => l_tag3
                   ,tagno_num => l_tagno_num
                   ,tag_type  => l_tag_type);

      SELECT f.flight_id
      INTO   p_flight_id
      FROM   bag b,
             pax p,
             flight_outbound_v1 f,
             agents a,
             tagtrak_users tu
      WHERE  b.bag_tag3 = l_tag3
        AND  (b.bag_tag2a = l_tag2a OR b.bag_tag2b = l_tag2b)
        AND  b.pax_id    = p.pax_id
        AND  p.flight_id = f.flight_id
        AND  f.site_id = a.site_id
        AND  a.agent_code = tu.agent_code
        AND  tu.user_id = p_user_id;

   EXCEPTION

      -- Couldn't identify flight (possible reasons include duplicate tag)

      WHEN OTHERS THEN
         p_return_code := -1;

   END;

END get_flight_for_tag;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_FOR_ULD
 *
 * Description:
 *
 * This procedure attempts to obtain the flight id for a given uld
 *
 * Parameters:
 *
 * p_user_id - Current user - used to identify site
 * p_uld_no - uld to find flight for
 * p_flight_id - returned flight id
 * p_return_code - error code (0 if flight found)
 *
 ******************************************************************************/

PROCEDURE get_flight_for_uld
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_uld_no      IN  ulds.uld_no%type, 
   p_flight_id   OUT flight.flight_id%type, 
   p_return_code OUT integer
)
IS
BEGIN

   p_return_code := 0;
 
   BEGIN

      SELECT u.flight_id
      INTO   p_flight_id
      FROM   ulds u,
             flight_outbound_v1 f,
             agents a,
             tagtrak_users tu
      WHERE  trim(u.uld_no) = trim(p_uld_no)
        AND  f.flight_id = u.flight_id
        AND  f.flight_status != 'PD'
        AND  f.site_id = a.site_id
        AND  a.agent_code = tu.agent_code
        AND  tu.user_id = p_user_id;

   EXCEPTION

      -- Couldn't identify flight
      WHEN OTHERS THEN
         p_return_code := -1;
   END;

END get_flight_for_uld;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_FOR_PAX_ID
 *
 * Description:
 *
 * This procedure attempts to obtain the flight id for a given pax id
 *
 * Parameters:
 *
 * p_pax_id      - pax to find flight for
 * p_flight_id   - returned flight id
 * p_return_code - error code (0 if flight found)
 *
 ******************************************************************************/

PROCEDURE get_flight_for_pax_id
(
   p_pax_id      IN  pax.pax_id%type,
   p_flight_id   OUT flight.flight_id%type,
   p_return_code OUT integer
)
IS
BEGIN

   p_return_code := 0;

   BEGIN

      SELECT flight_id INTO p_flight_id FROM pax WHERE
         pax_id = p_pax_id;

   EXCEPTION

      -- Couldn't identify flight
      WHEN OTHERS THEN
         p_return_code := -1;
   END;

END get_flight_for_pax_id;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_FOR_PAX_SEQ
 *
 * Description:
 *
 * This procedure attempts to obtain the flight id for a given passenger 
 * sequence number
 *
 * Parameters:
 *
 * p_bag_tag     - tag to find flight for
 * p_pax_seq     - pax to find flight for
 * p_flight_id   - returned flight id
 * p_return_code - error code (0 if flight found)
 *
 ******************************************************************************/

PROCEDURE get_flight_for_pax_seq
(
   p_bag_tag     IN  bag.bag_tag%type,
   p_pax_seq     IN  pax.pax_sequence_no%type,
   p_flight_id   OUT flight.flight_id%type,
   p_return_code OUT integer
)
IS
BEGIN

   p_return_code := 0;

   BEGIN

      SELECT flight_id INTO p_flight_id FROM bag b, pax p WHERE
         b.bag_tag = p_bag_tag AND
         b.pax_id  = p.pax_id  AND
         p.pax_sequence_no = p_pax_seq;

   EXCEPTION

      -- Couldn't identify flight
      WHEN OTHERS THEN
         p_return_code := -1;
   END;

END get_flight_for_pax_seq;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT_GIVEN_NO
 *
 * Description:
 *
 * This procedure attempts to obtain the flight id for a given flight number.
 * This will only be possible if the given number is unique for the site the
 * user is at
 *
 * Parameters:
 *
 * p_user_id     - user requesting flight
 * p_flight_no   - flight no to find id for
 * p_flight_id   - returned flight id
 * p_return_code - error code (0 if flight found)
 *
 ******************************************************************************/

PROCEDURE get_flight_given_no
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_flight_no   IN  flight.flight_no%type,
   p_flight_id   OUT flight.flight_id%type,
   p_return_code OUT integer
)
IS
BEGIN

   p_return_code := 0;

   BEGIN

      SELECT flight_id INTO p_flight_id FROM 
         flight_outbound_v1 f, tagtrak_users u, agents a 
      WHERE
         f.flight_no  = p_flight_no  AND
         u.user_id    = p_user_id    AND
         u.agent_code = a.agent_code AND 
         f.site_id    = a.site_id;

   EXCEPTION

      -- Couldn't identify flight (e.g. multiple flights with given number)
      WHEN OTHERS THEN
         p_return_code := -1;
   END;

END get_flight_given_no;

/******************************************************************************
 *
 * PROCEDURE GET_FLIGHT
 *
 * Description:
 *
 * This wrapper procedure calls the appropriate procedures in an attempt to 
 * identify a flight given a ULD/tag.
 *
 * Parameters:
 *
 * p_user_id     -  user requesting flight
 * p_flight_no   -  no of flight to get id for
 * p_bag_tag     -  tag to find flight for
 * p_uld_no      -  uld to find flight for (if tag null or zero)
 * p_flight_id   -  returned flight id
 * p_return_code -  error code (0 if flight found)
 *
 ******************************************************************************/

PROCEDURE get_flight
(
   p_user_id     IN  tagtrak_users.user_id%type,
   p_flight_no   IN  flight.flight_no%type,
   p_bag_tag     IN  bag.bag_tag%type,
   p_uld_no      IN  ulds.uld_no%type,
   p_pax_id      IN  pax.pax_id%type,
   p_pax_seq     IN  pax.pax_sequence_no%type,
   p_flight_id   OUT flight.flight_id%type,
   p_return_code OUT integer
)
IS
BEGIN

   IF p_pax_id IS NOT NULL AND p_pax_id != 0 THEN

      get_flight_for_pax_id(p_pax_id, p_flight_id, p_return_code);

   ELSIF p_pax_seq IS NOT NULL AND p_pax_seq != 0 THEN

      get_flight_for_pax_seq(p_bag_tag, p_pax_seq, p_flight_id, p_return_code);
 
   ELSIF ltrim(p_flight_no) IS NOT NULL THEN

      get_flight_given_no(p_user_id, p_flight_no, p_flight_id, p_return_code);

   ELSIF ltrim(p_bag_tag) IS NOT NULL AND ltrim(p_bag_tag) != '0' THEN

      get_flight_for_tag(p_user_id, p_bag_tag, p_flight_id, p_return_code);

   ELSIF ltrim(p_uld_no) IS NOT NULL THEN

      get_flight_for_uld(p_user_id, p_uld_no, p_flight_id, p_return_code);

   ELSE

      -- We have no way of identifing the flight
      p_return_code := -1;

   END IF;

END get_flight;


/******************************************************************************
 *
 * Public Procedures and Functions
 *
 ******************************************************************************/

/******************************************************************************
 *
 * PROCEDURE CREATE_HANDLER_TRACE
 *
 ******************************************************************************/

PROCEDURE create_handler_trace
(
   p_user_id       IN tagtrak_users.user_id%type,
   p_kit_id        IN kit.id%type,
   p_flight_id     IN flight.flight_id%type,
   p_flight_no     IN flight.flight_no%type,
   p_bag_tag       IN bag.bag_tag%type,
   p_uld_no        IN ulds.uld_no%type,
   p_pax_id        IN pax.pax_id%type,
   p_pax_seq       IN pax.pax_sequence_no%type
)
IS
   l_return_code      number := 0;
   l_handler_time     handler_trace.login_time%type;
   l_trace_time       handler_trace.login_time%type;
   l_location_code    user_trace.location_code%type;
   l_existing_trace   boolean := TRUE;
   l_tracing_enabled  db_config.db_value%type := null;
   l_flight_id        flight.flight_id%type;
BEGIN

   -- First check that handler tracing is enabled

   IF tracing_enabled() THEN
 
      -- Get user's login time from user trace table

      BEGIN

         SELECT to_char(max(insert_date), 'HH24:MI'), k.location_code INTO l_trace_time, l_location_code
         FROM user_trace u, tagtrak_users t, kit k WHERE
            u.trace_action = 'LOG-IN'     AND
            u.kit_id       = p_kit_id    AND
            u.kit_id       = k.id        AND
            u.login_name   = t.loginname AND
            t.user_id      = p_user_id
         GROUP BY k.location_code;

      EXCEPTION
      
         WHEN OTHERS THEN
            -- We should not get an error here, as the scanner should not pass
            -- an invalid user, etc, but catch it just in case
            l_return_code := -1;
      END;

      IF l_return_code = 0 THEN

         IF p_flight_id IS NULL OR p_flight_id = 0 THEN

            -- Flight has not been supplied, so attempt to get it
            get_flight(p_user_id, p_flight_no, p_bag_tag, p_uld_no, p_pax_id, 
                       p_pax_seq, l_flight_id, l_return_code);

         ELSE

            l_flight_id := p_flight_id;

         END IF;

         IF l_return_code = 0 THEN


            -- We got the login time from user trace, so now see if there's 
            -- an existing entry in handler trace. 

            BEGIN

               SELECT login_time INTO l_handler_time FROM handler_trace WHERE
                  seq_no = 
                     (SELECT max(seq_no) FROM HANDLER_TRACE WHERE
                        user_id = p_user_id AND
                        kit_id  = p_kit_id  AND
                        flight_id = l_flight_id
                     );

            EXCEPTION

               WHEN NO_DATA_FOUND THEN
    
                  -- No existing handler trace, so we'll have to create one

                  l_existing_trace := FALSE;

            END;

            IF l_existing_trace = TRUE THEN

               -- we have an existing entry, so check it is still valid - i.e.
               -- that this entry still corresponds in with the user trace entry

               IF l_handler_time != l_trace_time THEN

                  -- user trace entry has been updated (i.e. the user has logged
                  -- in again), so we will need to create another handler trace

                  l_existing_trace := FALSE;

                  -- make sure that all existing entries have logout time set

                  UPDATE handler_trace SET logout_time = '--:--' WHERE
                     user_id = p_user_id AND
                     kit_id  = p_kit_id  AND
                     logout_time IS NULL;

               ELSE
                  -- User trace entry still matches, so just update timestamp

                  UPDATE handler_trace SET last_update = sysdate WHERE
                     user_id   = p_user_id    AND
                     kit_id    = p_kit_id     AND
                     flight_id = l_flight_id  AND
                     logout_time IS NULL;

               END IF;
 
            END IF;

            IF l_existing_trace = FALSE THEN

               -- no entry in handler trace, or login time differs, so create entry

               INSERT INTO handler_trace
               (
                   seq_no,
                   user_id,
                   kit_id,
                   flight_id,
                   login_time,
                   logout_time,
                   location_code,
                   last_update
               )
               values
               (
                   handler_trace_seq.nextval,
                   p_user_id,
                   p_kit_id,
                   l_flight_id,
                   l_trace_time,
                   null,
                   l_location_code,
                   sysdate
               );

               COMMIT;

            END IF;

         END IF;

      END IF;   

   END IF;

END create_handler_trace;

/******************************************************************************
 *
 * PROCEDURE MWS_HANDLER_TRACE
 *
 ******************************************************************************/

PROCEDURE mws_handler_trace
(
   p_user_id       IN tagtrak_users.user_id%type,
   p_serial_number IN kit.serial_number%type,
   p_flight_id     IN flight.flight_id%type,
   p_flight_no     IN flight.flight_no%type,
   p_bag_tag       IN bag.bag_tag%type,
   p_uld_no        IN ulds.uld_no%type,
   p_pax_id        IN pax.pax_id%type,
   p_pax_seq       IN pax.pax_sequence_no%type
)
IS

   l_kit_id kit.id%TYPE;

BEGIN

   -- First we need to look up the kit for the given serial_no/user_id

   BEGIN

      SELECT id INTO l_kit_id FROM kit WHERE
         serial_number = p_serial_number AND
         user_id = p_user_id;

   EXCEPTION

      WHEN OTHERS THEN
         l_kit_id := 0;
   END;
dbms_output.put_line('Kit is ' || l_kit_id);
   -- Now we can call the tracing procedure

   create_handler_trace (p_user_id, l_kit_id, p_flight_id, p_flight_no, 
                         p_bag_tag, p_uld_no, p_pax_id, p_pax_seq);


END mws_handler_trace;


/******************************************************************************
 *
 * PROCEDURE LOGOUT_HANDLER_TRACE
 *
 * Description:
 *
 * This procedure updates an entry in the HANDLER_TRACE table to set the logout
 * time. This should be called whenever a user logs out
 *
 * Parameters:
 *
 * p_user_id          - user carrying out this operation
 * p_kit_id           - equipment id
 *
 ******************************************************************************/

PROCEDURE logout_handler_trace
(
   p_user_id       IN tagtrak_users.user_id%type,
   p_kit_id        IN kit.id%type
)
IS
   l_tracing_enabled  db_config.db_value%type := null;
BEGIN

   -- First check that handler tracing is enabled

   IF tracing_enabled() THEN

      -- Set logout time for this user/kit in the 
      -- handler trace table

      UPDATE handler_trace SET
         logout_time = to_char(sysdate, 'HH24:MI'),
         last_update = sysdate
      WHERE
         user_id = p_user_id AND
         kit_id  = p_kit_id AND
         logout_time IS NULL;

      COMMIT;

   END IF;

END logout_handler_trace;

/******************************************************************************
 *
 * PROCEDURE GET_HANDLER_TRACES
 *
 ******************************************************************************/

PROCEDURE get_handler_traces
(
     p_flight_id    IN    flight.flight_id%type,
     p_handler      OUT   handler_type,
     p_location     OUT   location_type,
     p_login_time   OUT   login_time_type,
     p_logout_time  OUT   logout_time_type
)
IS
   CURSOR c_handler_traces IS

      SELECT 
         loginname, 
         location_code, 
         login_time,
         logout_time
      FROM 
         handler_trace h, tagtrak_users t
      WHERE 
         h.flight_id = p_flight_id AND
         t.user_id   = h.user_id; 

   l_count integer := 1;

BEGIN

   -- Copy trace entry details into output parameters

   FOR l_this_rec IN c_handler_traces LOOP

      p_handler(l_count)     := l_this_rec.loginname;
      p_location(l_count)    := l_this_rec.location_code;
      p_login_time(l_count)  := l_this_rec.login_time;
      p_logout_time(l_count) := l_this_rec.logout_time;
      
      l_count := l_count + 1;

   END LOOP;
      
END get_handler_traces;

END handler_tracing;
/
