/*****************************************************************************
*
* Filename              - validate_flight_direction.sql
*
* Project Name          - UltraTrak Database
*
* Source Code Identifier - UAS/FALCP/SD/00848
*
* Description           - Creates procedure VALIDATE_FLIGHT_DIRECTION  which  
*                         attempt to identify a specific flight and return its
*                         flight_id, flight_date and direction. Where multiple
*                         flights meet the flight criteria specified by the
*                         parameters, available data is returned and other fields
*                         left blank. When an arrival flight is identified, the
*                         flight_id is set to 0. 
*
* Language              - Oracle PL/SQL
*
* File History          - For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2004
*
*****************************************************************************/

CREATE OR REPLACE PROCEDURE validate_flight_direction (
        p_flight_number     IN    CHAR,
        p_flight_direction  IN    CHAR,
        p_flight_date       IN    CHAR,
        p_user_id           IN    CHAR,
        p_flight_id        OUT    NUMBER,
        p_actual_direction OUT    CHAR,
        p_actual_date      OUT    CHAR,
        p_error_code       OUT    NUMBER
) AS

   l_count_inbound NUMBER := 0; -- Number of matching inbound flights found

   l_dep_flight_id flight.flight_id%TYPE; -- Flight id of identified departure flight

   -- Date of identified flight
   l_arr_flight_date flight.flight_date%TYPE;
   l_dep_flight_date flight.flight_date%TYPE;

   -- Indicate when more than one flight found matching search criteria
   l_multiple_inbound_found BOOLEAN := FALSE;
   l_multiple_outbound_found BOOLEAN := FALSE;

   -- Indicates whether a flight has been found matching the search criteria
   l_inbound_found  BOOLEAN := FALSE;
   l_outbound_found BOOLEAN := FALSE;

   l_airport_code flight_info_inbound.airport_code%TYPE; -- Airport code associated with user
   l_site_id      flight.site_id%TYPE; -- Site identity associated with the user
   l_agent_code   tagtrak_users.agent_code%TYPE; -- Agent associated with current user
   l_return_code  NUMBER := 0; -- Code returned from procedure check_flight_access

BEGIN

   -- Validate the user id and obtain the agent code
   BEGIN
      SELECT agent_code INTO l_agent_code FROM tagtrak_users
         WHERE user_id = p_user_id;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         -- User is not known
         p_error_code := -102;
         p_flight_id := NULL;
         p_actual_date := NULL;
         p_actual_direction := NULL;
   END;

   If p_error_code IS NULL THEN
      -- continue processing

      -- Obtain the site identity to use in matching outbound flights
      l_site_id := multi_site.get_user_siteid(p_user_id);

      -- Obtain the airport code to use in matching inbound flights
      l_airport_code := multi_site.get_airport_code(l_site_id);

      IF p_flight_direction IS NOT NULL THEN
         IF p_flight_date IS NOT NULL THEN
            -- Identify flight using direction and date

            IF p_flight_direction IN ('I','B') THEN
               -- Check for an inbound flight
               SELECT count(*) INTO l_count_inbound FROM flight_info_inbound
                  WHERE flight_no = p_flight_number AND
                        flight_date = p_flight_date AND
                        airport_code = l_airport_code;
            END IF;
            IF p_flight_direction in ('O','B') THEN
               -- Check for an outbound flight
               BEGIN
                  SELECT flight_id  INTO l_dep_flight_id FROM flight
                     WHERE flight_no = p_flight_number AND
                        flight_date = p_flight_date AND
                        flight_type = 'O' AND
                        site_id = l_site_id;
                  -- Set outbound found flag
                  l_outbound_found := TRUE;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     -- Set flag indicating outbound flight not found
                     l_outbound_found := FALSE;
               END;
            END IF;

            IF l_count_inbound = 1 AND l_outbound_found THEN
               -- Identified an inbound and outbound flight matching criteria
               p_flight_id := NULL;
               p_actual_direction := 'B';
               p_actual_date := p_flight_date;

            ELSIF l_count_inbound = 1 THEN
               -- Identified an inbound flight matching the criteria
               p_flight_id := 0;
               p_actual_direction := 'I';
               p_actual_date := p_flight_date;

            ELSIF l_outbound_found THEN
               -- Identified an outbound flight matching criteria
               p_flight_id := l_dep_flight_id;
               p_actual_direction := 'O';
               p_actual_date := p_flight_date;

            ELSE
               -- Flight matching given criteria was not found so set error code
               p_error_code := -139;
            END IF;

         ELSE -- Flight direction is provided but not flight date
            IF p_flight_direction IN ('I','B') THEN
               -- Check for an inbound flight
               BEGIN
                  SELECT flight_date INTO l_arr_flight_date FROM flight_info_inbound
                     WHERE flight_no = p_flight_number AND
                        airport_code = l_airport_code AND
						NVL(act_arrival_time, NVL(est_arrival_time, schd_arrival_time)) >= (sysdate -1);
                  -- Set flags indicating a single row was found
                  l_inbound_found := TRUE;
                  l_multiple_inbound_found := FALSE;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     l_inbound_found := FALSE;
                     l_multiple_inbound_found := FALSE;
                  WHEN TOO_MANY_ROWS THEN
                     l_inbound_found := TRUE;
                     l_multiple_inbound_found := TRUE;
               END;
            END IF; -- Obtain inbound flight details
            IF p_flight_direction in ('O','B') THEN
               -- Check for an outbound flight
               BEGIN
                  SELECT flight_id, flight_date  INTO l_dep_flight_id, l_dep_flight_date FROM flight
                     WHERE flight_no = p_flight_number AND
                           flight_type = 'O' AND
                           site_id = l_site_id AND
						   departure_time >= trunc(sysdate - 1);
                  -- Set flags indicating a single row was found
                  l_outbound_found := TRUE;
                  l_multiple_outbound_found := FALSE;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     l_outbound_found := FALSE;
                     l_multiple_outbound_found := FALSE;
                  WHEN TOO_MANY_ROWS THEN
                     l_outbound_found := TRUE;
                     l_multiple_outbound_found := TRUE;
               END;
            END IF; -- Obtain outbound flight details

            IF l_inbound_found AND l_outbound_found THEN
               -- Inbound and outbound flights were identified

               If NOT(l_multiple_inbound_found OR l_multiple_outbound_found) THEN
                  -- A single inbound and outbound flight has been found

                  IF l_arr_flight_date = l_dep_flight_date THEN
                     -- Flights operate on same date so we can set the date
                     p_actual_date := l_arr_flight_date;
                     p_flight_id := NULL;
                     p_actual_direction := 'B';

                  ELSE
                     -- Flights operate on different date so just set the direction
                     p_actual_date := NULL;
                     p_flight_id := NULL;
                     p_actual_direction := 'B';
                  END IF;
               ELSE -- Multiple matching flights were found so just set direction
                     p_actual_date := NULL;
                     p_flight_id := NULL;
                     p_actual_direction := 'B';
               END IF; -- multiple flights were/were not found

            ELSIF l_inbound_found THEN
               -- Inbound flight(s) were identified
               IF NOT l_multiple_inbound_found THEN
                  -- A single inbound flight was identified
                  p_actual_date := l_arr_flight_date;
                  p_flight_id := 0;
                  p_actual_direction := 'I';
               ELSE
                  -- Multiple inbound flights were found
                  p_actual_date := NULL;
                  p_flight_id := NULL;
                  p_actual_direction := 'I';
               END IF;

            ELSIF l_outbound_found THEN
               IF NOT l_multiple_outbound_found THEN
                  -- A single outbound flight was identified
                  p_actual_date := l_dep_flight_date;
                  p_flight_id := l_dep_flight_id;
                  p_actual_direction := 'O';
               ELSE
                  -- Multiple outbound flights were identified
                  p_actual_date := NULL;
                  p_flight_id := NULL;
                  p_actual_direction := 'O';
               END IF;

            ELSE
               -- Return unknown flight error code
               p_error_code := -139;
            END IF;
         END IF; -- Flight date was/was not provided

      ELSE -- flight direction was not provided
         IF p_flight_date IS NOT NULL THEN
            -- Select information about flights using date
            SELECT count(*) INTO l_count_inbound FROM flight_info_inbound
               WHERE flight_no = p_flight_number AND
                     flight_date = p_flight_date AND
                     airport_code = l_airport_code;

            BEGIN
               SELECT flight_id INTO l_dep_flight_id FROM flight
                  WHERE flight_no = p_flight_number AND
                        flight_date = p_flight_date AND
                        flight_type = 'O' AND
                        site_id = l_site_id;
               l_outbound_found := TRUE;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  -- Set flag indicating flight was not found
                  l_outbound_found := FALSE;
            END;

            If l_count_inbound = 1 AND l_outbound_found THEN
               -- Inbound and outbound flight identified
               p_actual_date := p_flight_date;
               p_flight_id := NULL;
               p_actual_direction := 'B';
            ELSIF l_count_inbound = 1 THEN
               -- An inbound flight was found
               p_actual_date := p_flight_date;
               p_flight_id := 0;
               p_actual_direction := 'I';
            ELSIF l_outbound_found THEN
               -- An outbound flight was found
               p_actual_date := p_flight_date;
               p_flight_id := l_dep_flight_id;
               p_actual_direction := 'O';
            ELSE
               -- Matching flight was not found so return error code
               p_error_code := -139;
            END IF;

         ELSE -- Neither direction or date have been provided
            -- Look for an inbound flight
            BEGIN
               SELECT flight_date INTO l_arr_flight_date FROM flight_info_inbound
                  WHERE flight_no = p_flight_number AND
                        airport_code = l_airport_code AND
						NVL(act_arrival_time, NVL(est_arrival_time, schd_arrival_time)) >= (sysdate -1);
               -- Set flags indicating a single row was found
               l_inbound_found := TRUE;
               l_multiple_inbound_found := FALSE;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  l_inbound_found := FALSE;
                  l_multiple_inbound_found := FALSE;
               WHEN TOO_MANY_ROWS THEN
                  l_inbound_found := TRUE;
                  l_multiple_inbound_found := TRUE;
            END;

            -- Look for an outbound flight
            BEGIN
               SELECT flight_id, flight_date  INTO l_dep_flight_id, l_dep_flight_date FROM flight
                  WHERE flight_no = p_flight_number AND
                        flight_type = 'O' AND
                        site_id = l_site_id AND
						departure_time >= trunc(sysdate - 1);

               -- Set flags indicating a single row was found
               l_outbound_found := TRUE;
               l_multiple_outbound_found := FALSE;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  l_outbound_found := FALSE;
                  l_multiple_outbound_found := FALSE;
               WHEN TOO_MANY_ROWS THEN
                  l_outbound_found := TRUE;
                  l_multiple_outbound_found := TRUE;
            END;

            If l_inbound_found AND l_outbound_found THEN
               -- Inbound and outbound flights were identified

               IF NOT(l_multiple_inbound_found OR l_multiple_outbound_found) THEN
                  -- A single inbound and outbound flight has been found

                  IF l_arr_flight_date = l_dep_flight_date THEN
                     -- Flights operate on same date so we can set the date
                     p_actual_date := l_arr_flight_date;
                     p_flight_id := NULL;
                     p_actual_direction := 'B';

                  ELSE
                     -- Flights operate on different date so just set the direction
                     p_actual_date := NULL;
                     p_flight_id := NULL;
                     p_actual_direction := 'B';
                  END IF;
               ELSE -- Multiple matching flights were found so just set direction
                     p_actual_date := NULL;
                     p_flight_id := NULL;
                     p_actual_direction := 'B';
               END IF; -- multiple flights were/were not found

            ELSIF l_inbound_found THEN
               -- Inbound flight(s) were identified
               IF NOT l_multiple_inbound_found THEN
                  -- A single inbound flight was identified
                  p_actual_date := l_arr_flight_date;
                  p_flight_id := 0;
                  p_actual_direction := 'I';
               ELSE
                  -- Multiple inbound flights were found
                  p_actual_date := NULL;
                  p_flight_id := NULL;
                  p_actual_direction := 'I';
               END IF;
            ELSIF l_outbound_found THEN
               IF NOT l_multiple_outbound_found THEN
                  -- A single outbound flight was identified
                  p_actual_date := l_dep_flight_date;
                  p_flight_id := l_dep_flight_id;
                  p_actual_direction := 'O';
               ELSE
                  -- Multiple outbound flights were identified
                  p_actual_date := NULL;
                  p_flight_id := NULL;
                  p_actual_direction := 'O';
               END IF;

            ELSE
               -- Return unknown flight error code
               p_error_code := -139;
            END IF;
         END IF; -- Flight date was/was not provided
      END IF; -- Direction was/was not provided
   END IF; -- The user id is valid

   IF p_error_code IS NULL AND NVL(p_flight_id,0) != 0 THEN
      -- We have found a valid departure flight, check that user has
      -- access to this flight

      check_flight_access(p_flight_id,l_agent_code,l_return_code);

      IF l_return_code < 0 THEN
         -- User does not have access to this flight so set
         -- the error code
         p_error_code := -1031;
         p_flight_id := NULL;
         p_actual_date := NULL;
         p_actual_direction := NULL;
      END IF;
   END IF; -- Validate departure flight

END validate_flight_direction;
/


