create or replace package bmm_messages is

  -- Author  : DAVID.ROLFE
  -- Created : 09/05/2006 13:40:25
  -- Purpose : Handle BMM messages

  /*
  * Define public table TYPE definitions
  */
	TYPE message_id_col_type      IS TABLE OF messages.message_id%TYPE         INDEX BY BINARY_INTEGER;
	TYPE timestamp_col_type       IS TABLE OF message_parts.timestamp%TYPE     INDEX BY BINARY_INTEGER;
	TYPE message_text1_col_type   IS TABLE OF message_parts.message_text1%TYPE INDEX BY BINARY_INTEGER;
	TYPE message_text2_col_type   IS TABLE OF message_parts.message_text2%TYPE INDEX BY BINARY_INTEGER;
	TYPE message_part_no_col_type IS TABLE OF message_parts.part_no%TYPE       INDEX BY BINARY_INTEGER;
	TYPE parts_col_type         	IS TABLE OF messages.parts%TYPE              INDEX BY BINARY_INTEGER;

  MAX_BMM_ROWS number := 1000;

PROCEDURE store_bmm_mail_mws
(
   p_message_id  IN messages.message_id%TYPE,
   p_route_id    IN bmm_routes.route_id%TYPE
);


PROCEDURE search_bmm_messages (
       p_flight_direction     in  varchar2, -- not NULL  I O (or  IO)
       p_flight_no            in  varchar2,
       p_flight_date          in  varchar2,
       p_surname              in  varchar2,
       p_tag                  in  varchar2,
       p_uld_no               in  varchar2,
       num_previous_days      in  number ,  -- date range for query
   	 p_message_id           out message_id_col_type,
       p_timestamp            out timestamp_col_type,
       p_message_text1a       out message_text1_col_type,
       p_message_text1b       out message_text1_col_type,
       p_message_text2        out message_text2_col_type,
       p_msg_part_no_col_type out message_part_no_col_type,
       p_message_parts        out parts_col_type  -- total number of parts to this message
  );

FUNCTION search_bmm_messages_2 (
       p_flight_direction     IN  VARCHAR2, -- not NULL  I O (or  IO)
       p_flight_no            IN  VARCHAR2,
       p_flight_date          IN  VARCHAR2,
       p_surname              IN  VARCHAR2,
       p_tag                  IN  VARCHAR2,
       p_uld_no               IN  VARCHAR2,
       p_num_previous_days    IN  NUMBER) RETURN SYS_REFCURSOR;
       
end bmm_messages;
/

create or replace package body bmm_messages is

  -- Private type declarations
   l_message_id message_id_col_type; -- array of message_id found by the search
   current_row number;    -- the row in the message_id array of the details got

/******************************************************************************/
/*    search for BMM  messages                                                */
/*    that satisy your criteria and retunr the count of messages found        */
/******************************************************************************/
  procedure search_bmm_messages (
       p_flight_direction     in  varchar2, -- not NULL  I O (or  IO)
       p_flight_no            in  varchar2,
       p_flight_date          in  varchar2,
       p_surname              in  varchar2,
       p_tag                  in  varchar2,
       p_uld_no               in  varchar2,
       num_previous_days      in  number ,  -- date range for query
   	   p_message_id           out message_id_col_type,
       p_timestamp            out timestamp_col_type,
       p_message_text1a       out message_text1_col_type,
       p_message_text1b       out message_text1_col_type,
       p_message_text2        out message_text2_col_type,
       p_msg_part_no_col_type out message_part_no_col_type,
       p_message_parts        out parts_col_type  -- total number of parts to this message
   )is

    type msg_ref IS REF CURSOR ;

    msg_csr       msg_ref;
    slow_query    number := 0 ;

    l_flight_no   varchar2(10);
    l_flight_date varchar2(5);
    l_surname     varchar2(29);
    l_tag         varchar2(12);
    l_uld_no      varchar2(12);

begin

    -- set up the local variables such that if a criteria is not enterde
    -- it will have no affect on the selection
    if  nvl(p_flight_no,'-') = '-' then
        l_flight_no := '%';
    else
        l_flight_no :=  Trim(p_flight_no) || '%';
    end if;

    if  nvl(p_flight_date,'-') = '-' then
        l_flight_date := '%';
    else
        l_flight_date :=  p_flight_date;
    end if;


    if  nvl(p_surname,'-') = '-' then
       l_surname := '%';
    else
       l_surname :=  '%' || p_surname ||'%';
       slow_query := slow_query + 1;
    end if;

    if  nvl(p_tag,'-') = '-' then
       l_tag := '%';
    else
       l_tag :=  '%' || p_tag ||'%';
       slow_query := slow_query + 1;
    end if;

    if  nvl(p_uld_no,'-') = '-' then
       l_uld_no := '%';
    else
       l_uld_no :=  '%' || p_uld_no ||'%';
       slow_query := slow_query + 1;
    end if;
  
    -- only accept I/O flight types
    if INSTR('IO',p_flight_direction) = 0 then
       return;
    end if;

    -- only use the instr slower cursor format
    --  if a search of the message text feild is required
    if slow_query = 0 then
      open msg_csr for
         select distinct m.message_id, mp.timestamp, substr(mp.message_text1,1,2000), substr(mp.message_text1,2001), mp.message_text2, mp.part_no, m.parts from messages m, message_parts mp
            where m.message_id = mp.message_id
                AND INSTR(p_flight_direction,M.MESSAGE_DIRECTION ) > 0
                AND MP.TIMESTAMP >= (TRUNC(SYSDATE + 1) - num_previous_days)
                AND M.FLIGHT_NUMBER like l_flight_no
                AND M.FLIGHT_DATE like l_flight_date;
    else
      open msg_csr for
         select distinct m.message_id, mp.timestamp,  substr(mp.message_text1,1,2000), substr(mp.message_text1,2001), mp.message_text2, mp.part_no, m.parts from messages m, message_parts mp
            where m.message_id = mp.message_id
                AND INSTR(p_flight_direction,M.MESSAGE_DIRECTION ) > 0
                AND MP.TIMESTAMP >= (TRUNC(SYSDATE + 1) - num_previous_days)
                AND M.FLIGHT_NUMBER like l_flight_no
                AND M.FLIGHT_DATE like l_flight_date
                and  (mp.message_text1 like l_surname  or  mp.message_text2 like l_surname)
                and  (mp.message_text1 like l_tag      or  mp.message_text2 like l_tag)
                and  (mp.message_text1 like l_uld_no   or  mp.message_text2 like l_uld_no);
    end if;

    FETCH msg_csr BULK COLLECT INTO
       p_message_id,
       p_timestamp,
       p_message_text1a,
       p_message_text1b,
       p_message_text2,
       p_msg_part_no_col_type,
       p_message_parts
       LIMIT MAX_BMM_ROWS;

     close msg_csr;

     dbms_output.put_line (' num_previous_days : '||num_previous_days);
     dbms_output.put_line (' l_flight_no       : '||l_flight_no);
     dbms_output.put_line (' l_flight_date     : '||l_flight_date);
     dbms_output.put_line (' l_surname         : '||l_surname);
     dbms_output.put_line (' l_tag             : '||l_tag );
     dbms_output.put_line (' l_uld_no          : '||l_uld_no);
     
     dbms_output.put_line (' bmm Total msgs found  = '|| p_message_id.count );
     
  
end search_bmm_messages;

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

FUNCTION search_bmm_messages_2 (
       p_flight_direction     IN  VARCHAR2, -- not NULL  I O (or  IO)
       p_flight_no            IN  VARCHAR2,
       p_flight_date          IN  VARCHAR2,
       p_surname              IN  VARCHAR2,
       p_tag                  IN  VARCHAR2,
       p_uld_no               IN  VARCHAR2,
       p_num_previous_days    IN  NUMBER) RETURN SYS_REFCURSOR IS

  l_cursor SYS_REFCURSOR;

  l_slow_query    BOOLEAN := FALSE;

  l_flight_no   VARCHAR2(10);
  l_flight_date VARCHAR2(5);
  l_surname     VARCHAR2(29);
  l_tag         VARCHAR2(12);
  l_uld_no      VARCHAR2(12);

begin

  -- set up the local variables such that if a criteria is not entered
  -- it will have no affect on the selection
  IF nvl(p_flight_no,'-') = '-' THEN
    l_flight_no := '%';
  ELSE
    l_flight_no :=  Trim(p_flight_no) || '%';
  END IF;

  l_flight_date := nvl(p_flight_date, '%');

  IF p_surname IS NOT NULL THEN
    l_surname :=  '%' || p_surname ||'%';
    l_slow_query := TRUE;
  ELSE
    l_surname :=  '%';
  END IF;

  IF p_tag IS NOT NULL THEN
    l_tag :=  '%' || p_tag ||'%';
    l_slow_query := TRUE;
  ELSE
    l_tag :=  '%';
  END IF;

  IF p_uld_no IS NOT NULL THEN
    l_uld_no :=  '%' || p_uld_no ||'%';
    l_slow_query := TRUE;
  ELSE
    l_uld_no := '%';
  END IF;

  -- only use the instr slower cursor format
  -- if a search of the message text feild is required
  IF l_slow_query THEN

    OPEN l_cursor FOR
    SELECT DISTINCT m.message_id, mp.timestamp,
    substr(mp.message_text1, 1, 2000) message_text1a,
    substr(mp.message_text1, 2001) message_text1b,
    mp.message_text2, mp.part_no, m.parts
    FROM messages m, message_parts mp
    WHERE m.message_id = mp.message_id
    AND instr(p_flight_direction, m.message_direction ) > 0
    AND mp.timestamp >= (trunc(sysdate + 1) - p_num_previous_days)
    AND m.flight_number LIKE l_flight_no
    AND m.flight_date LIKE l_flight_date
    AND (mp.message_text1 LIKE l_surname OR mp.message_text2 LIKE l_surname)
    AND (mp.message_text1 LIKE l_tag OR mp.message_text2 LIKE l_tag)
    AND (mp.message_text1 LIKE l_uld_no OR mp.message_text2 LIKE l_uld_no);

  ELSE

    OPEN l_cursor FOR
    SELECT DISTINCT m.message_id, mp.timestamp,
    substr(mp.message_text1, 1, 2000) message_text1a,
    substr(mp.message_text1, 2001) message_text1b,
    mp.message_text2, mp.part_no, m.parts
    FROM messages m, message_parts mp
    WHERE m.message_id = mp.message_id
    AND instr(p_flight_direction, m.message_direction ) > 0
    AND mp.timestamp >= (trunc(sysdate + 1) - p_num_previous_days)
    AND m.flight_number LIKE l_flight_no
    AND m.flight_date LIKE l_flight_date;

  END IF;

  dbms_output.put_line (' num_previous_days : '||p_num_previous_days);
  dbms_output.put_line (' l_flight_no       : '||l_flight_no);
  dbms_output.put_line (' l_flight_date     : '||l_flight_date);
  dbms_output.put_line (' l_surname         : '||l_surname);
  dbms_output.put_line (' l_tag             : '||l_tag );
  dbms_output.put_line (' l_uld_no          : '||l_uld_no);

  RETURN l_cursor;

END search_bmm_messages_2;

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

PROCEDURE store_bmm_mail_mws
(
   p_message_id  IN messages.message_id%TYPE,
   p_route_id    IN bmm_routes.route_id%TYPE
)
IS
rc INTEGER;
BEGIN
/*wrapper procedure around function to be used by MWS*/
rc := store_bmm_mail(p_message_id,p_route_id);
END store_bmm_mail_mws;

end bmm_messages;
/
