/*****************************************************************************
*
* Filename         - $Workfile:   $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Package
*
* Language         - Oracle PL/SQL script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   $  $Revision:   $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   $
* 
*****************************************************************************/

PROMPT create or replace package EQ_TRACKING 

create or replace package EQ_TRACKING as

        type t_timestamp IS TABLE OF eq_log.eql_timestamp%TYPE index by binary_integer;
        type t_firstname IS TABLE OF tagtrak_users.firstname%TYPE index by binary_integer;
        type t_surname IS TABLE OF tagtrak_users.surname%TYPE index by binary_integer;
        type t_kit_type IS TABLE OF kit_type.type%TYPE index by binary_integer;
        type t_serial_no IS TABLE OF kit.serial_number%TYPE index by binary_integer;
        type t_status IS TABLE OF kit_status.kis_status%TYPE index by binary_integer;
        type t_comment IS TABLE OF eq_log.eql_comment%TYPE index by binary_integer;

procedure ADD_EQ_LOG
(
        p_users_site IN kit.airport_code%TYPE,
        p_user_id IN tagtrak_users.user_id%TYPE,
        p_kit_type IN kit_type.type%TYPE,
        p_serial_no IN kit.serial_number%TYPE,
        p_new_status IN kit_status.kis_status%TYPE,
        p_comment IN eq_log.eql_comment%TYPE,
        p_override IN NUMBER,
        p_error_number OUT NUMBER,
        p_error_text OUT VARCHAR2
);

procedure GET_EQ_LOG
(
        p_users_site IN kit.airport_code%TYPE,
        p_timestamp OUT t_timestamp,
        p_firstname OUT t_firstname,
        p_surname OUT t_surname,
        p_kit_type OUT t_kit_type,
        p_serial_no OUT t_serial_no,
        p_status OUT t_status,
        p_comment OUT t_comment
);

/******************************************************************************
 *
 * FUNCTION get_eq_log_2
 *
 * Description:
 *   Returns all the eq_log records for a particular airport as a ref cursor
 *   ordered by timestamp desc.
 *
 * Parameters:
 *   p_users_site - the airport code the data is required for
 *
 * Returns:
 *   A sys_refcursor containing: timestamp, firstname, surname,
 *   kit_type, serial_number, kit_status and log_comment
 *
 * Errors raised:
 *   none
 *
 ******************************************************************************/
FUNCTION get_eq_log_2 (p_users_site IN kit.airport_code%TYPE)
RETURN SYS_REFCURSOR;

procedure GET_EQ_STATUS
(
        p_users_site IN site_info.airport_code%TYPE,
        p_scan_total OUT NUMBER,
        p_scan_offsite OUT NUMBER,
        p_scan_loggedin OUT NUMBER,
        p_scan_available OUT NUMBER,
        p_mws_total OUT NUMBER,
        p_mws_offsite OUT NUMBER,
        p_mws_loggedin OUT NUMBER,
        p_mws_available OUT NUMBER
);

-- Commented out as it won't compile without table EQ_LOG_SITE
-- procedure TEST_GET_EQ_LOG
-- (
--         p_users_site IN kit.airport_code%TYPE
-- );

end EQ_TRACKING;
/

PROMPT create or replace package body EQ_TRACKING 

create or replace package body EQ_TRACKING as

-- Return codes

-- Success (>= 0)
        SUCCESS constant number                 := 0 ;
        USER_LOGIN_OVERRIDDEN constant number   := +1 ;

-- Error codes (-ive), see also db_com.h and ERROR table.
        UNKNOWN_USER constant number            := -102;   -- see user_login_pkg.sql
        VALIDATION_FAILED constant number       := -1032;
        UNKNOWN_KIT constant number             := -1034;
        USER_LOGGED_IN constant number          := -1055;  -- new for equipment tracking
        BAD_KIT_STATUS constant number          := -1056;  -- new for equipment tracking
        BAD_SITE constant number                := -1057;  -- new for equipment tracking
        BAD_KIT_TYPE constant number            := -1058;  -- new for equipment tracking
        BAD_SERIAL_NUMBER constant number       := -1059;  -- new for equipment tracking
        ADD_LOG_FAILED constant number          := -1060;  -- new for equipment tracking
        STATUS_UPDATE_FAILED constant number    := -1061;  -- new for equipment tracking
        USER_LOGOUT_FAILED constant number      := -1062;  -- new for equipment tracking

/**************************************************************************************************
*
* Procedure : ADD_EQ_LOG
*
* Purpose : Procedure to allow the MWS to add an equipment log to the EQ_LOG table.
*
* Parameters : 
*
*       p_users_site IN site_info.airport_code%TYPE         - the site at which the user is operating
*       p_user_id IN tagtrak_users.user_id%TYPE             - the id of the user adding the log
*       p_kit_type IN kit_type.type%TYPE                    - the type of the kit being logged
*       p_serial_no IN kit.serial_number%TYPE               - the kit's identifier
*       p_new_status IN kit_status.kis_status%TYPE          - the status that the log is being used to set
*       p_comment IN eq_log.eql_comment%TYPE                - the setting user's comment
*       p_override IN BOOLEAN                               - allows status change if someone is using the kit
*       p_error_number OUT NUMBER                           - returned error code
*       p_error_text OUT VARCHAR2                           - matching text (may contain data, eg. 'Fred Smith is logged in')
* 
* Requirements :
*
* This procedure shall insert a new row into the Equipment Log table. This shall occur for all cases with an appropriate RESULT value.
* 
* If the kit exists in the KIT table (identified using KIT_TYPE and SERIAL_NUMBER) then the row shall be linked to the equipment log.
*
* This procedure shall perform the following checks and, if successful, update the kit status as requested:
* 1.    That the requested kit status exists in the KIT_STATUS table.
* 2.    That the kit's airport code (KIT.AIRPORT_CODE) is the same as the user's airport code (IN parameter).
* 3.    That no user is logged in to the equipment.
*       If a user is logged into the equipment this procedure shall check p_override. If p_override is FALSE then an error message shall be returned and the kit status shall not be updated. If p_override is TRUE then the kit status shall be updated.
* 
* To summarise, the following error/success cases shall be supported (the "text" strings indicate the value of the OUT parameter, p_error_text):
* 1.    SUCCESS.
* 2.    SUCCESS - error overridden.
* 3.    Requesting user validation error (a).
* 4.    KIT not at requesting user's airport (status not updated) (a).
* 5.    STATUS not found (status not updated) (a).
* 6.    User <Firstname|| ||Surname> is logged in to this equipment.
*       This error occurs if an Equipment Log action is attempted when a user is logged into the equipment (b).
* 
* Notes:
* a.    This error cannot be overridden by the MWS by setting the p_override IN parameter to TRUE.
* b.    The error can be overridden by the MWS by setting the p_override IN parameter to TRUE. This has the effect of creating two log entries for one log action, a fail case initially and then an override case.
* 
* p_error_number values shall follow the following convention:
* -     Error cases - negative
* -     Success cases - 0 or positive
* 
* This procedure shall COMMIT / ROLLBACK on behalf of the MWS.
* 
*****************************************************************************************************************************/

procedure ADD_EQ_LOG
(
        p_users_site IN kit.airport_code%TYPE,
        p_user_id IN tagtrak_users.user_id%TYPE,
        p_kit_type IN kit_type.type%TYPE,
        p_serial_no IN kit.serial_number%TYPE,
        p_new_status IN kit_status.kis_status%TYPE,
        p_comment IN eq_log.eql_comment%TYPE,
        p_override IN NUMBER,
        p_error_number OUT NUMBER,
        p_error_text OUT VARCHAR2
) AS

        v_count number ;
        v_error_text varchar2(60);
        v_firstname tagtrak_users.firstname%TYPE;
        v_kis_id kit_status.kis_id%TYPE;
        v_kit_id kit.id%TYPE;
        v_logged_in kit.logged_in%TYPE;
        v_retcode number ;
        v_retval number ;
        v_site_id site_info.site_id%TYPE;
        v_surname tagtrak_users.surname%TYPE;

begin

        v_retval := SUCCESS ;               -- Assume success
        v_error_text := '' ;          -- Assume success

-- Check the site
        if (v_retval >= 0 ) then

                begin
        
                        select site_id into v_site_id from site_info where airport_code = p_users_site ;

                exception
                        when OTHERS then
                                v_retval := BAD_SITE ;
                                v_error_text := 'Bad site - '||nvl(p_users_site,'<undefined site>') ;
                end;
        end if;

-- Check the kit type
        if (v_retval >= 0 ) then

                select count(*) into v_count from kit_type where type = p_kit_type ;

                if (v_count = 0 ) then
                        v_retval := BAD_KIT_TYPE ;
                        v_error_text := 'Bad kit type - '||nvl(p_kit_type,'<undefined kit type>') ;
                end if;

        end if;

-- Check the serial number
        if (p_serial_no is null ) then

                v_retval := BAD_SERIAL_NUMBER ;
                v_error_text := 'Bad serial number - '||nvl(p_serial_no,'<undefined serial number>') ;

        end if;

-- Identify the kit (note: Existence in the KIT table is not mandatory, e.g. spare scanners that have
-- not been used yet may have logs made against them).

        if ( v_retval >= 0 ) then

                begin
                        select id, logged_in, firstname, surname
                        into   v_kit_id, v_logged_in, v_firstname, v_surname
                        from   kit, tagtrak_users
                        where  kit.user_id = tagtrak_users.user_id (+)
                          and  p_users_site = airport_code and p_kit_type = type and p_serial_no = serial_number;
        
                exception
                        when OTHERS then
                                v_kit_id := NULL;
                                v_logged_in := NULL;
                                v_firstname := NULL;
                                v_surname := NULL;
                end;

        end if ;

-- Handle user that is already logged in

        if (v_retval >= 0 ) then

                if (v_logged_in = 'Y' and nvl(p_override,0) <> 0) then

                        v_retval := USER_LOGIN_OVERRIDDEN ;
                        v_error_text :=
                                'Overrode user '||rtrim(nvl(v_firstname,'<undefined firstname>'))||' '||
                                rtrim(nvl(v_surname,'<undefined surname>'))||' log in' ;

                        -- update of kit to set logged_in to N done with the other updates.

                elsif (v_logged_in = 'Y' and nvl(p_override,0) = 0) then

                        v_retval := USER_LOGGED_IN ;
                        v_error_text := 
                                'User '||rtrim(nvl(v_firstname,'<undefined firstname>'))||' '||
                                rtrim(nvl(v_surname,'<undefined surname>'))||' is logged in' ;

                end if ;

        end if ;

-- Check the status
        if (v_retval >= 0 ) then

                begin
                        select kis_id into v_kis_id from kit_status where kis_status = p_new_status
                        and p_new_status <> 'LOGGED IN' ;    -- only log in processes can set LOGGED IN

                exception
                        when OTHERS then
                                v_retval := BAD_KIT_STATUS ;
                                v_error_text := 'Bad kit status - '||nvl(p_new_status,'<undefined status>') ;
                end;
        end if;

-- Add the log
        if (v_retval >= 0 ) then

                begin
        
                        insert into eq_log
                        (
                                eql_id, eql_site_id, eql_kit_id, eql_kit_type, eql_serial_number,
                                eql_user_id, eql_kis_id, eql_comment, eql_timestamp
        
                        ) values
                        (
                                eql_id_s.nextval, v_site_id, v_kit_id, p_kit_type, p_serial_no,
                                p_user_id, v_kis_id, p_comment, sysdate
                        );
        
                exception
                        when OTHERS then
                                v_retval := ADD_LOG_FAILED ;
                                v_error_text := 'Add log failed';
        
                                -- dbms_output.put_line(to_char(SQLCODE)||' - '||SQLERRM) ;
                end ;

        end if;

-- Log any users out if the kit exists
        if (v_retval >= 0 and v_kit_id is not null) then

		-- Need to see live.user_login_pkg from arch and temp schemas when compiling
		-- eq_tracking in them. Nb. add_eq_log never gets called in those schemas
		-- because the MWS disables the update equipment log button. If it does then
		-- it should still work.

                live.user_login_pkg.release_kit (p_kit_type, p_serial_no, v_retcode) ;

                                -- UNKNOWN_USER can be ignored, all we care about is that the kit status has
                                -- been updated ok. retcode = 1 tells us that the actual update has failed.

                                if (v_retcode = 1 or (v_retcode < 0 and v_retcode <> UNKNOWN_USER) ) then
                                
                                   v_retval := USER_LOGOUT_FAILED ;
                                   v_error_text := nvl(p_new_status,'User logout failed') ;
                                
                                end if;

        end if;

-- Update the kit status if the kit exists
        if (v_retval >= 0 and v_kit_id is not null) then

                                begin

                        update kit set
                                kit_kis_id = v_kis_id
                                                where id = v_kit_id ;

                exception
                        when OTHERS then
                                v_retval := STATUS_UPDATE_FAILED ;
                                v_error_text := nvl(p_new_status,'Kit status update failed') ;

                end;

        end if;

-- Transaction handling
        if (v_retval >= 0 ) then

                commit ;

        else

                rollback ;

        end if ;

        p_error_number := v_retval ;
        p_error_text := v_error_text ;

--      dbms_output.put_line (to_char(p_error_number));

end ADD_EQ_LOG;
/**************************************************************************************************
*
* Procedure : GET_EQ_LOG
*
* Purpose : Procedure to allow the MWS to get equipment logs for a site from the database.
*
* Parameters : 
*
*       p_users_site IN site_info.airport_code%TYPE         - the site at which the user is operating
*       p_timestamp OUT t_timestamp,                        - index by table of timestamps
*       p_firstname OUT t_firstname,                        - index by table of ...
*       p_surname OUT t_surname,                            - index by table of ...
*       p_kit_type OUT t_kit_type,                          - index by table of ...
*       p_serial_no OUT t_serial_no,                        - index by table of ...
*       p_status OUT t_status,                              - index by table of ...
*       p_comment OUT t_comment                             - index by table of ...
* 
* Requirements :
*
*       Void.
*
**************************************************************************************************/

procedure GET_EQ_LOG
(
        p_users_site IN kit.airport_code%TYPE,
        p_timestamp OUT t_timestamp,
        p_firstname OUT t_firstname,
        p_surname OUT t_surname,
        p_kit_type OUT t_kit_type,
        p_serial_no OUT t_serial_no,
        p_status OUT t_status,
        p_comment OUT t_comment
) AS

        i   number := 0 ;     -- loop counter also used for array addressing

        cursor c_eq_log(site varchar2) is
        select
               timestamp, firstname, surname,
               kit_type, serial_number, kit_status, log_comment
        from
               eq_log_v
        where
               airport_code  = site 
        order by
               timestamp desc ;   -- Provide a known default order for the MWS

begin

        for eql_rec in c_eq_log(p_users_site) loop

                i := i + 1 ;

                p_timestamp(i) := eql_rec.timestamp ;
                p_firstname(i) := eql_rec.firstname ;
                p_surname(i) := eql_rec.surname ;
                p_kit_type(i) := eql_rec.kit_type ;
                p_serial_no(i) := eql_rec.serial_number ;
                p_status(i) := eql_rec.kit_status ;
                p_comment(i) := eql_rec.log_comment ;

        end loop ;

end GET_EQ_LOG;

---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

FUNCTION get_eq_log_2 (p_users_site IN kit.airport_code%TYPE)
RETURN SYS_REFCURSOR IS

  l_cursor SYS_REFCURSOR;

BEGIN

  OPEN l_cursor FOR 
  SELECT timestamp, firstname, surname, kit_type, 
         serial_number, kit_status, log_comment, rownum view_key
  FROM eq_log_v
  WHERE airport_code = p_users_site
  ORDER BY timestamp DESC ;

  RETURN l_cursor;

END get_eq_log_2;


/**************************************************************************************************
*
* Procedure : GET_EQ_STATUS
*
* Purpose : Procedure to allow the MWS to get equipment status counts for a site from the database.
*
* Parameters : 
*
*       p_users_site IN site_info.airport_code%TYPE         - the site at which the user is operating
*       p_scan_total OUT NUMBER,                            - various totals counts .....
*       p_scan_offsite OUT NUMBER,
*       p_scan_loggedin OUT NUMBER,
*       p_scan_available OUT NUMBER,
*       p_mws_total OUT NUMBER,
*       p_mws_offsite OUT NUMBER,
*       p_mws_loggedin OUT NUMBER,
*       p_mws_available OUT NUMBER
* 
* Requirements :
*
*       Void.
*
**************************************************************************************************/

procedure GET_EQ_STATUS
(
        p_users_site IN site_info.airport_code%TYPE,
        p_scan_total OUT NUMBER,
        p_scan_offsite OUT NUMBER,
        p_scan_loggedin OUT NUMBER,
        p_scan_available OUT NUMBER,
        p_mws_total OUT NUMBER,
        p_mws_offsite OUT NUMBER,
        p_mws_loggedin OUT NUMBER,
        p_mws_available OUT NUMBER
) AS

begin

        -- This should really be a grid on the MWS to make this just a SELECT.

        p_scan_total := 0 ;
        p_scan_offsite := 0 ;
        p_scan_loggedin := 0 ;
        p_scan_available := 0 ;
        p_mws_total := 0 ;
        p_mws_offsite := 0 ;
        p_mws_loggedin := 0 ;
        p_mws_available := 0 ;

        for c_rec in
        (

                select  type, kis_status, count(*) count
                from
                (
                        --
                        -- This select gets kit statuses from kit
                        --
                        select  airport_code, type, serial_number, kis_status
                        from    kit, kit_status
                        where   kit_kis_id = kis_id
                        UNION ALL
                        --
                        -- This part gets kit statuses from EQ_LOG for those kit that don't exist in kit
                        -- (e.g. spare kit that's never been logged in to). It would have been a lot
                        -- simpler just to add kit into KIT when logs were made and they didn't exist
                        -- in kit yet but Ray expected this to be quicker (because I did too). To do this
                        -- I need to find the latest log (nested select c) and then get its data.
                        --
                        select  b.airport_code, a.eql_kit_type type, a.eql_serial_number serial_number, kis_status
                        from    eq_log a, site_info b,
                                (
                                        select   eql_site_id, eql_kit_type, eql_serial_number, max(eql_timestamp) max_ts
                                        from     eq_log
                                        where    eql_kit_id is null
                                        group by eql_site_id, eql_kit_type, eql_serial_number
                                ) c, kit_status d
                        where   a.eql_site_id = b.site_id and a.eql_site_id = c.eql_site_id
                          and   a.eql_kit_type = c.eql_kit_type
                          and   a.eql_serial_number = c.eql_serial_number
                          and   a.eql_timestamp = c.max_ts
                          and   a.eql_kis_id = d.kis_id
                )
                where   airport_code = p_users_site
                group by type, kis_status
        ) loop

                dbms_output.put_line ('Type : '||c_rec.type);
                dbms_output.put_line ('Status : '||c_rec.kis_status);
                dbms_output.put_line ('Count : '||c_rec.count);

                if ( c_rec.type = 'SCANNER' and c_rec.kis_status = 'OFF SITE' ) then
                        p_scan_offsite := c_rec.count ;
                        p_scan_total := p_scan_total + c_rec.count ;
                end if;

                if ( c_rec.type = 'SCANNER' and c_rec.kis_status = 'LOGGED IN' ) then
                        p_scan_loggedin := c_rec.count ;
                        p_scan_total := p_scan_total + c_rec.count ;
                end if;

                if ( c_rec.type = 'SCANNER' and c_rec.kis_status = 'AVAILABLE' ) then
                        p_scan_available := c_rec.count ;
                        p_scan_total := p_scan_total + c_rec.count ;
                end if;

                /*The following check ensures that scanners with status 'LOCKED' 
                **also add to the total count
                */
                if ( c_rec.type = 'SCANNER' and (c_rec.kis_status != 'OFF SITE' 
                                            and  c_rec.kis_status != 'LOGGED IN'
                                            and  c_rec.kis_status != 'AVAILABLE'
                                            and  c_rec.kis_status != 'RETIRED')) then
                        p_scan_total := p_scan_total + c_rec.count ;
                end if;                                            

                if ( c_rec.type = 'MWS_FIXED' and c_rec.kis_status = 'OFF SITE' ) then
                        p_mws_offsite := c_rec.count ;
                        p_mws_total := p_mws_total + c_rec.count ;
                end if;

                if ( c_rec.type = 'MWS_FIXED' and c_rec.kis_status = 'LOGGED IN' ) then
                        p_mws_loggedin := c_rec.count ;
                        p_mws_total := p_mws_total + c_rec.count ;
                end if;

                if ( c_rec.type = 'MWS_FIXED' and c_rec.kis_status = 'AVAILABLE' ) then
                        p_mws_available := c_rec.count ;
                        p_mws_total := p_mws_total + c_rec.count ;
                end if;
                
                /*The following check ensures that MWSes with status 'LOCKED' 
                **also add to the total count
                */
                if ( c_rec.type = 'MWS_FIXED' and (c_rec.kis_status != 'OFF SITE' 
                                              and  c_rec.kis_status != 'LOGGED IN'
                                              and  c_rec.kis_status != 'AVAILABLE'
                                              and  c_rec.kis_status != 'RETIRED')) then
                        p_mws_total := p_mws_total + c_rec.count ;
                end if;

        end loop ;

end GET_EQ_STATUS;

/**************************************************************************************************
*
* Procedure : TEST_GET_LOG
*
* Purpose : Procedure to allow test procedure GET_EQ_LOG.
*           put in here to allow it to share index by table definitions.
*           Note: requires table EQ_LOG_SITE for the results set so commented out.
*
* Parameters : 
*
*       p_users_site IN site_info.airport_code%TYPE         - the site at which the user is operating
* 
* Requirements :
*
*       Void.
*
**************************************************************************************************/

-- procedure TEST_GET_EQ_LOG
-- (
--         p_users_site IN kit.airport_code%TYPE
-- ) AS
-- 
--         timestamps t_timestamp;
--         firstnames t_firstname;
--         surnames t_surname;
--         kit_types t_kit_type;
--         serial_nos t_serial_no;
--         stati t_status;
--         comments t_comment;
-- 
-- begin
-- 
--         get_eq_log (p_users_site, timestamps, firstnames, surnames, kit_types, serial_nos, stati, comments) ;
-- 
--         -- Assumes contiguous (which it is)
--         for i in timestamps.first..timestamps.last loop
-- 
--                 insert into eq_log_site
--                 (
--                         timestamp, firstname, surname, kit_type, serial_number, status, acomment
--                 ) values
--                 (
--                         timestamps(i), firstnames(i), surnames(i), kit_types(i), serial_nos(i), stati(i), comments(i)
--                 ) ;
-- 
--         end loop;
-- 
-- end TEST_GET_EQ_LOG ;

end EQ_TRACKING;
/

