/*****************************************************************************
*
* Filename         - $Workfile:   validate_action.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 weights table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE VALIDATE_ACTION

CREATE OR REPLACE PACKAGE VALIDATE_ACTION AS

  TYPE flight_sta_type is TABLE OF CHAR(3) INDEX BY BINARY_INTEGER;

  PROCEDURE get_flight_statuses
  (
    p_flight_type OUT flight_sta_type,
    retcode       OUT NUMBER
  );

  PROCEDURE check_access
  (
     p_userid       IN     tagtrak_users.user_id%TYPE,
     p_fid          IN     flight.flight_id%TYPE,
     p_function_id  IN     function_id.id%TYPE,
     p_page_name    IN     function_id.page_name%TYPE,
     p_retcode         OUT NUMBER
  );

END;
/

PROMPT CREATE OR REPLACE PACKAGE BODY VALIDATE_ACTION

CREATE OR REPLACE PACKAGE BODY VALIDATE_ACTION AS

PROCEDURE get_flight_statuses
(
    p_flight_type OUT flight_sta_type,
    retcode       OUT NUMBER
) IS

  counter             number(10) := 0;
  scanner_language    char(20);
  db_code_value       char(3);
  flight_status       char(3);

  cursor get_status is
  select FLIGHT_STATUS
    from flight_status_type;

BEGIN
     retcode := 0;

     BEGIN
        -- Fetch the report language for the scanner from db_config
        scanner_language := config.get_value('SCANNER_REPORT_LANG');
        IF( scanner_language IS NULL) THEN
           dbms_output.put_line('No db config');
           raise NO_DATA_FOUND;
        END IF;

     EXCEPTION
        -- If there is no language then assume English
        WHEN NO_DATA_FOUND THEN
           scanner_language := 'ENG';
     END;

     -- Loop round the Flight status type entries
     FOR get_status_rec IN get_status LOOP

         counter := counter + 1;
         flight_status := NULL;

         BEGIN
            -- Do we want to return the statuses in English?
            IF (rtrim(scanner_language) != 'ENG') THEN

               SELECT db_code
                 INTO db_code_value
                 FROM translator
                WHERE type = 'STATUS'
                  AND language ='ENG'
                  AND rtrim(code) = get_status_rec.flight_status;

               SELECT rtrim(code)
                 INTO flight_status
                 FROM translator
                WHERE type = 'STATUS'
                  AND language = rtrim(scanner_language)
                  AND db_code = db_code_value;

               -- Did we get anything back from the database?
               IF (trim(flight_status) IS NOT NULL) THEN
                  -- Yes, use the status value returned
                  p_flight_type(counter) := trim(flight_status);
               ELSE
                  -- Use the original cursor value
                  p_flight_type(counter) := get_status_rec.flight_status;
               END IF;
             ELSE
               -- We want to return the status in English
               p_flight_type(counter) := get_status_rec.flight_status;
             END IF;
         EXCEPTION
             WHEN NO_DATA_FOUND THEN
                -- We couldn't find any translations so return the status in English
                p_flight_type(counter) := get_status_rec.flight_status;
                dbms_output.put_line('no data found');
         END;
     END LOOP;
     IF (counter = 0) THEN
        retcode := -1044;
     END IF;
EXCEPTION
     WHEN OTHERS THEN
        retcode := -1044;
END get_flight_statuses ;


PROCEDURE check_access
(
     p_userid       IN     tagtrak_users.user_id%TYPE,
     p_fid          IN     flight.flight_id%TYPE,
     p_function_id  IN     function_id.id%TYPE,
     p_page_name    IN     function_id.page_name%TYPE,
     p_retcode         OUT NUMBER
) IS

  l_flts          flight.flight_status%TYPE;
  l_usergrp       tagtrak_users.group_name%TYPE;
  l_db_item       db_config.db_value%TYPE;
  l_action_count  NUMBER(10);
  l_priority_grp2 super_groups.group_name%TYPE;
  l_restriction   super_groups.restriction_2%TYPE;

  no_db_config    EXCEPTION;
  access_granted  EXCEPTION;
  access_denied   EXCEPTION;

BEGIN

  -- Check that Flight Closeout processing is enabled
  l_db_item := config.get_value('FLIGHT_CLOSEOUT');
  IF( NVL(TRIM(l_db_item), 'N') <> 'Y' ) THEN

    -- Parameter not defined
    RAISE no_db_config;

  END IF;

  -- Determine whether the specified function is included in Flight Closeout Processing
  IF (p_page_name IS NOT NULL) THEN

    -- MWS call
    SELECT COUNT(*)
    INTO   l_action_count
    FROM   function_id
    WHERE  page_name = RTRIM(p_page_name);

  ELSE

    -- Scanner call
    SELECT id
    INTO   l_action_count
    FROM   function_id
    WHERE  id = p_function_id;

  END IF;

  DBMS_OUTPUT.PUT_LINE('Function Count = ' || ' ' || TO_CHAR(l_action_count));

  -- Did we find this function?
  IF (l_action_count = 0) THEN

    RAISE access_granted;

  END IF;

  -- We've found the function, now get the Flight status
  BEGIN

    SELECT flight_status
    INTO   l_flts
    FROM   flight
    where  flight_id = p_fid;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_flts := NULL;
  END;

  -- Get the Group of this user
  BEGIN

    SELECT RTRIM(group_name)
    INTO   l_usergrp
    FROM   tagtrak_users
    WHERE  user_id = p_userid;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_usergrp := NULL;
  END;

  -- Access Depends on Flight Status
  IF (l_flts IN ('FO','FD')) THEN

    -- Flight Open or Delayed - Access Granted
    RAISE access_granted;

  ELSIF (l_flts IN ('FG','GC')) THEN

    -- Flight Gated or Gate Closed - Check for SUPER_GROUP access
    BEGIN

      SELECT RTRIM(group_name)
      INTO   l_priority_grp2
      FROM   super_groups
      WHERE  RTRIM(group_name) = RTRIM(l_usergrp);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE access_denied;
    END;

    -- User is in Super Group - Access Granted
    RAISE access_granted;

  ELSIF (l_flts = 'FC') THEN

    -- Flight Closed - Check for Super Group Restriction override
    BEGIN

      SELECT RTRIM(group_name),
             restriction_2
      INTO   l_priority_grp2,
             l_restriction
      FROM   super_groups
      WHERE  RTRIM(group_name) = RTRIM(l_usergrp);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RAISE access_denied;
    END;

    -- Check whether access is restricted
    IF (l_restriction = 'N' ) THEN
      RAISE access_granted;
    ELSE
      RAISE access_denied;
    END IF;

  ELSIF (l_flts IN ('PD', 'FF'))  THEN

    -- Flight is Post Departure or Flight Finalised - Access Denied
    RAISE access_denied;

  END IF;

EXCEPTION

  WHEN access_granted THEN

    -- Access Granted
    p_retcode := 0;

  WHEN no_db_config THEN

    -- No parameter defined - Assume Access Granted
    p_retcode := 0;

  WHEN access_denied THEN

    -- Access Denied
    p_retcode := -1031;

END check_access;

END VALIDATE_ACTION;
/
