/*****************************************************************************
*
* Filename         - $Workfile:   get_offset.sql  $
*
*                   Name Yomi Kogbe  Date 6-Jun-2001 Version 1.0  CR Number 15435
* 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
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited 1998
*
*****************************************************************************/


/**************************************************

   This Function return the time offset for
   airport code. It uses the airport_code to lookup the 
   offset time from timezone table .
************************************/

PROMPT CREATE OR REPLACE PACKAGE  get_offset

CREATE OR REPLACE PACKAGE  get_offset AS

FUNCTION airport_code 
(v_airport_code in timezone.airport_code%type) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(airport_code, wnds, wnps, rnps);

FUNCTION airport_code 
(v_airport_code in site_info.site_id%type) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(airport_code, wnds, wnps, rnps);

FUNCTION find_airport_code
(fid in flight.flight_id%type,
 pid in pax.pax_id%type
)RETURN CHAR;
PRAGMA RESTRICT_REFERENCES(find_airport_code, wnds, wnps, rnps);

FUNCTION get_user_location
(userid  in tagtrak_users.user_id%type) RETURN CHAR;
PRAGMA RESTRICT_REFERENCES(get_user_location, wnds, wnps, rnps);
 
END get_offset;
/

PROMPT CREATE OR REPLACE PACKAGE BODY get_offset
CREATE OR REPLACE PACKAGE BODY get_offset
IS

FUNCTION get_daylight_saving(v_airport_code IN timezone.airport_code%type)
RETURN NUMBER IS

  now              date       := SYSDATE;
  daylight_start   date       ;
  daylight_end     date       ;
  daylight_offset  number(10) ;
  actual_offset    number := 0;

begin

  SELECT  daylight_saving_begins
         ,daylight_saving_ends
         ,daylight_saving_offset
    INTO  daylight_start,daylight_end, daylight_offset
    FROM  timezone
   WHERE airport_code = v_airport_code;

    IF( daylight_start  is NOT NULL AND
        daylight_end    is NOT NULL AND
        daylight_offset is NOT NULL ) THEN


        IF( now between daylight_start AND daylight_end ) THEN


            actual_offset := (1/60) * daylight_offset;
     

        END IF;

    END IF;

    RETURN actual_offset;

EXCEPTION

  WHEN NO_DATA_FOUND THEN
  RETURN actual_offset;

END get_daylight_saving;


FUNCTION airport_code
  (v_airport_code in timezone.airport_code%type) RETURN NUMBER
IS
       ret    number := 0;
       daylight_saving number;
BEGIN
      SELECT offset 
        INTO ret
        FROM timezone
       WHERE airport_code = upper(v_airport_code);

    daylight_saving :=  get_daylight_saving(v_airport_code);

    RETURN (ret/24) + (daylight_saving/24);

EXCEPTION
	 when no_data_found then
       ret := 0;
			 return ret;
   when others then
       ret := 0;
			 return ret;
END airport_code;

FUNCTION airport_code
  (v_airport_code in site_info.site_id%type) RETURN NUMBER
IS
      ret    number := 0;
      daylight_saving number;
BEGIN
    SELECT offset 
      INTO ret
      FROM timezone
     WHERE airport_code = upper (multi_site.get_airport_code (v_airport_code));

    daylight_saving :=  get_daylight_saving(v_airport_code);

    RETURN (ret/24) + (daylight_saving/24);

EXCEPTION
	 when no_data_found then
       ret := 0;
			 return ret;
   when others then
       ret := 0;
			 return ret;
END airport_code;


FUNCTION find_airport_code
 (fid in flight.flight_id%type,
  pid in pax.pax_id%type
 )RETURN CHAR
IS
     depair    pax.departure_airport_code%type := null; 
BEGIN
          IF fid IS NOT NULL THEN
              begin
                  select AIRPORT_CODE
                    into depair
                    from flight f, site_info si
                   where f.flight_id = fid
                     and f.site_id = si.site_id;
             exception
                  when no_data_found then
                       depair := null;
                  when others then
                       depair := null;
             end;
          ELSIF pid IS NOT NULL THEN
             begin
                select departure_airport_code
                  into depair 
                  from pax
                 where pax_id    = pid;
             exception
                  when no_data_found then
                       depair := null;
                  when others then
                       depair := null;
             end;
            
          ELSE
                   depair := null;
                   RETURN depair;
          END IF;  
                 
          RETURN depair;
END find_airport_code; 

FUNCTION get_user_location
(userid  in tagtrak_users.user_id%type) RETURN CHAR
IS
    depair    pax.departure_airport_code%type := null;
BEGIN
    select airport_code
      into depair
      from tagtrak_users t,
           agents a,
           site_info si
     where t.agent_code = a.agent_code
       and a.site_id    = si.site_id
       and t.user_id    = userid; 

       RETURN depair;

EXCEPTION
   when no_data_found then
       depair := null;
       return depair;
   when others then
       depair := null;
       return depair;
END get_user_location;
END get_offset;
/
