/*****************************************************************************
*
* Filename         - $Workfile:   user_authentication.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 user_authentication  package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   user_authentication.sql  $  $Revision:   1.3  $
*
* Revision History
*
*
****************************************************************************/

PROMPT create or replace package user_authentication

create or replace package user_authentication
as
     function login_count (
        p_user_id   in   tagtrak_users.user_id%type
     ) return number;

    function max_logins (
        p_user_id   in   tagtrak_users.user_id%type
    ) return number;

    function max_logins (
        p_agents_code   in   agents.agent_code%type
    ) return number;

    procedure update_max_logins (
				p_agent_code IN agents.agent_code%type,
				p_user_role_name IN user_role.user_role_name%type,
        p_max_logins IN INTEGER,
				p_retval       out  INTEGER
    ) ;

    function encrypt_password (
        p_unencrypted_password tagtrak_users.tagtrak_password%TYPE
    ) return tagtrak_users.tagtrak_password%TYPE;

    function validate_password (
        p_userid  in   tagtrak_users.user_id%type, 
        p_password in tagtrak_users.TAGTRAK_PASSWORD%type
    ) return number;

    procedure valid_password (
        p_userid  in   tagtrak_users.user_id%type, 
        p_password in tagtrak_users.TAGTRAK_PASSWORD%type,
				p_retval       out  number
    ) ;

    procedure validate_access (
        p_user_id       in  tagtrak_users.user_id%type, 
        p_flight_id     in  flight.flight_id%type, 
        p_flight_no    in  flight.flight_no%type, 
        p_flight_date  in  flight.flight_date%type,
				p_retval       out  number
    ) ;

    function validate_location (
        p_location_code     in location.location_code%type, 
        p_siteid            in site_info.site_id%type
    ) return number;

end user_authentication;
/
PROMPT create or replace package body user_authentication

create or replace package body user_authentication as

    /* A function to get the number of users logged in. 
    xx   Will not include users logged in with equipment identified by the serial number.
    */
    function login_count (
        p_user_id   in   tagtrak_users.user_id%type
    ) return number
    is
        found number;
        result boolean;
    begin

        BEGIN
            select count(user_id)
            into   found
            from   kit
            where  user_id = p_user_id and
                   logged_in = 'Y' ;
    
            exception
                when NO_DATA_FOUND then
                found := 0;
        END;

        return found;
    end login_count;

    /*
    **  The number concurrent logins allowed for a given user.
    **/
    function max_logins (
        p_user_id   in   tagtrak_users.user_id%type
    ) return number
    is
      found number;
      p_agent_code   agents.agent_code%type;
    BEGIN

      BEGIN
          SELECT ur.max_logins,tu.agent_code
          INTO   found,p_agent_code
          FROM   user_role ur, tagtrak_users tu
          WHERE  tu.user_id           = p_user_id
            AND    ur.user_role_name    = TRIM (tu.group_name)
            AND    ur.agent_code        = tu.agent_code;


      EXCEPTION
          WHEN NO_DATA_FOUND THEN
            found := null;
      END;

      if (found is null) then
          found := max_logins(p_agent_code);
      end if;

      return found;

    end max_logins;


    /*  
    **  The number concurrent logins allowed for a given agent.
    **/
    function max_logins (
          p_agents_code   in   agents.agent_code%type
    ) return number
    is
          found number;
    BEGIN


      BEGIN
       SELECT max_logins
       INTO  found
       FROM   agents
       WHERE  agent_code = p_agents_code;


      EXCEPTION
          WHEN NO_DATA_FOUND THEN
            found := null;
      END;

      /* If max_logins is null then 
           get the value of MAX_CONCURRENT_LOGINS db_config item.
      */
      if (found is null) then
          begin 
              select db_value 
              into found
              from db_config 
              where db_item = 'MAX_CONCURRENT_LOGIN';
              EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                    found := 999;
          END;
      end if;

      return found;
    end max_logins;


    /*
    **  Pass-through to encrypt_password () stored procedure.
    */
    function encrypt_password (
        p_unencrypted_password in tagtrak_users.tagtrak_password%TYPE
    ) return tagtrak_users.tagtrak_password%TYPE
    IS
        p_encrypted_password tagtrak_users.tagtrak_password%TYPE ;
    BEGIN
        --live.encrypt_password (p_unencrypted_password,p_encrypted_password);

        select
                rtrim(translate(rtrim(p_unencrypted_password),
                'abcdefghijklmnopqrstuvwxyz0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
                '6tkJa0wGVp8fyShAeT7DimOrNcZd9RXuH1nYQ3obUC2KLsgI5z4vWEjFlxqBPM'))
        into
                p_encrypted_password
        from
                dual;

        return p_encrypted_password;
    end encrypt_password;


    /*  proc create fo ease of use by MWS */
    procedure valid_password (
        p_userid  in   tagtrak_users.user_id%type, 
        p_password in tagtrak_users.TAGTRAK_PASSWORD%type,
				p_retval       out  number
    ) 
		as 
		BEGIN
				p_retval := validate_password ( p_userid  , p_password );
		END valid_password ;

    /*  
    **  Pass-through to validate_password () stored procedure.
    **  0 (ok), -1032 (fail)
    */
    function validate_password (
        p_userid  in   tagtrak_users.user_id%type, 
        p_password in tagtrak_users.TAGTRAK_PASSWORD%type
    ) return number
    is
        p_retval number;
        encrypted_password tagtrak_users.tagtrak_password%TYPE;
        tagtrak_password   tagtrak_users.tagtrak_password%TYPE;

    BEGIN
--        live.validate_password ( p_userid, p_password, p_retval );
/*
 * VALIDATE_PASSWORD will accept 2 parameters, user_id and password and
 * will then try to validate this password with the user_id.  If it
 * cannot validate it, then it will return BAD_PASSWORD.  If the user_id
 * is unknown, it will then return UNKNOWN_USER.  If it succeeds, it will
 * then return SUCCESS.
 */
       /* Initialise the retval to 0 (SUCCESS) */
        p_retval := 0;
        /*
         * Encrypt the p_password parameter
         */

        encrypted_password := encrypt_password (p_password );

        /* Now get the password from the tagtrak_users table */
        begin
                select
                        tagtrak_password
                into
                        tagtrak_password
                from
                        tagtrak_users
                where
                        user_id          = p_userid and
                        tagtrak_password = encrypted_password;

                return p_retval;

                exception
                        when NO_DATA_FOUND then
                                /* VALIDATION_FAILED */
                                p_retval := -1032;
                                return p_retval;

                        when TOO_MANY_ROWS then
                                /* VALIDATION_FAILED */
                                p_retval := -1032;
                                return p_retval;
        end;


    end validate_password;

/*
 * validate_access will accept the user_id and either the flight_id or the
 * flight_no and flight_date.  It will then check to see if this user has
 * access to the flight, and will return :
 *      SUCCESS - Assuming the user has access to the flight
 *      FAILURE - User does not have access to the flight
 */
/* 
** Pass-through to validate_access () stored procedure.
** 0 (ok), -131/102/-1031 (fail)
*/ 
    procedure validate_access (
        p_user_id       in  tagtrak_users.user_id%type, 
        p_flight_id     in  flight.flight_id%type, 
        p_flight_no    in  flight.flight_no%type, 
        p_flight_date  in  flight.flight_date%type,
        p_retval       out  number
    ) 
    is
        flt_id         flight.flight_id%TYPE;
        ag_code        flight_access.agent_code%TYPE;

        found_access   boolean := FALSE;

        op_fltno char(8);
        op_fltdate char(8);
        op_fltid number;
        depcode site_info.airport_code%type;
        psiteid site_info.site_id%type;

        cursor flight_access_csr is
                select
                        flight_id
                from
                        flight_access
                where
                        flight_id  = flt_id and
                        agent_code = ag_code;
    BEGIN
        --live.validate_access ( p_userid, p_flightid, p_flight_no, p_flight_date, p_retval);

        depcode := multi_site.get_user_loc(p_user_id);
        psiteid := multi_site.get_site_id(depcode);

        if (p_flight_id = 0) then
                /* Get the flight_id from the flight_no and flight_date */
                flight_id2 (p_flight_no, p_flight_date, psiteid, flt_id);

                /* Check for an error */
                if (flt_id < 0) then
                        p_retval := -139;
                        return ;
                end if;
        else
                /* Assign the parameter flight_id to the variable flt_id */
                flt_id := p_flight_id;
        end if;

        /*
         * Get the operating flight (if any) associated with this
         * flight, and use it. This is how we grant access to code-share
         * flights according to the operating flight's access - Actually
         * any work done on a code-share flight will be applied directly
         * to the operating flight, so this behaviour is consistent.
         */
        codeshare.get_operating_flight(null,null, psiteid, flt_id,
                op_fltno, op_fltdate, op_fltid);

        if ( op_fltid > 0 ) then
                flt_id := op_fltid;
        end if;


        /* Get the agent_code for the user_id passed in */
        begin
                select
                        agent_code
                into
                        ag_code
                from
                        tagtrak_users
                where
                        user_id = p_user_id;

                exception
                        when NO_DATA_FOUND then
                                /* UNKNOWN USER */
                                p_retval := -102;
                                return ;
        end;

        /*
         * We now have the agent_code for the user, check this in the flight_access
         * table.
         */

        /* Initialise Found_access to FALSE */
        found_access := FALSE;
        begin
                for csr_rec_loop in flight_access_csr loop
                        found_access := TRUE;
                end loop;
        end;

        if (found_access) then
                /* SUCCESS */
                p_retval := 0;
        else
                /* ACCESS DENIED */
                p_retval := -1031;
        end if;

        return ;
    end validate_access;

/*
* - Count the number of records from the location table where the
*    location_code is equal to the p_location_code parameter.
* - IF the count is equal to 0 (ie. no records) then
*   Assign -1 to the return_value
* - ELSE
*   Assign 0 to the return value
*/
/*
**  Pass-through to validate_location () stored procedure
**  0 (ok) -1 (fail)
*/  
    function validate_location (
        p_location_code     in location.location_code%type, 
        p_siteid            in site_info.site_id%type 
    ) return number
    is
        ret_status number;
        l_location  location.location_code%type; 
        l_siteid    site_info.site_id%type;
    BEGIN
        /* get the location */
        select
                count(*)
        into
                ret_status
        from
                location
        where
                location_code =  p_location_code
        and  site_id  = p_siteid;

        if ( ret_status = 0) then
                ret_status := -1;
        else
                ret_status := 0;
        end if;

        return ret_status;
    end validate_location;



FUNCTION update_default_max_logins (p_max_logins IN number
      ) RETURN NUMBER IS
BEGIN

        UPDATE db_config
        SET     db_value = p_max_logins
        WHERE  db_item = 'MAX_CONCURRENT_LOGIN';

        UPDATE agents
        SET     max_logins = p_max_logins;

        -- if we are going to update the agent default
        --  remove max_logins for ALL their users so they will default to the new value  */
        UPDATE user_role
        SET     max_logins = null;


        IF SQL%NOTFOUND THEN
                RETURN 1;
        ELSE
        RETURN 0;
        END IF;

EXCEPTION
        WHEN OTHERS THEN
                RETURN 1;
END update_default_max_logins;


FUNCTION update_agent_max_logins (p_agent_code IN agents.agent_code%type,
                                       p_max_logins IN number
      ) RETURN NUMBER IS
BEGIN
        UPDATE agents
				SET     max_logins = p_max_logins
				WHERE  agent_code = p_agent_code;

 	      -- if we are going to update the agent default 
				--  remove max_logins for ALL their users so they will default to the new value  */
        UPDATE user_role
				SET     max_logins = null
				WHERE  agent_code = p_agent_code;

        IF SQL%NOTFOUND THEN
                RETURN 1;
        ELSE
        RETURN 0;
        END IF;

EXCEPTION
        WHEN OTHERS THEN
                RETURN 1;
        RETURN 0;
END update_agent_max_logins;

FUNCTION update_user_role_max_logins (p_agent_code IN agents.agent_code%type,
																			p_user_role_name IN user_role.user_role_name%type,
                                      p_max_logins IN number
      ) RETURN NUMBER IS

BEGIN
        UPDATE user_role
				SET     max_logins = p_max_logins
				WHERE  user_role_name =  p_user_role_name and
							 agent_code = p_agent_code;


        IF SQL%NOTFOUND THEN
                RETURN 1;
        ELSE
        RETURN 0;
        END IF;

EXCEPTION
        WHEN OTHERS THEN
                RETURN 1;
END update_user_role_max_logins;


procedure update_max_logins (p_agent_code     IN  agents.agent_code%type,
			     p_user_role_name IN  user_role.user_role_name%type,
			     p_max_logins     IN  INTEGER,
			     p_retval         out INTEGER
) IS

    l_agent_code agents.agent_code%type := trim(p_agent_code);
    l_user_role_name user_role.user_role_name%type := trim(p_user_role_name);

BEGIN

    p_retval := 1;

    -- if a role is given update that specific role
    --
    if (l_user_role_name != '-') then
	p_retval := update_user_role_max_logins
			(l_agent_code,l_user_role_name,p_max_logins);
    else
	-- if NO user role is given
	--
	if (l_agent_code = '-' ) then
	    p_retval := update_default_max_logins(p_max_logins);
	else 
	    p_retval := update_agent_max_logins (l_agent_code,p_max_logins);
	end if;
    end if;

END update_max_logins;


end user_authentication;
/

