create or replace package   nav_pck is 

  function navigation_bar_query return varchar2; 

end nav_pck;
/

create or replace package body nav_pck is

/*******************************************************************************
 * Package app_pck
 *
 * Revision history:
 *
 * Date         Name       Description
 * 24-01-2018     Dick Dral  Initial creation
 */
  CRLF              constant char(1) := chr(10);
  g_query           varchar2(32767);
  g_seqno           number := 1;
  g_application_id  number := 1000;
  
/*******************************************************************************
 * Procedure apex_url
 * Description : Builds an APEX url from the different components
 *               
 *               
 * Parameters  : p_app_id       application ID (can also be alias)
 *               p_app_page_id  page ID (can also be alias)
 *               p_session      session ID
 *               p_request      request
 *               p_debug        debug flag
 *               p_clearcache   clearcache parameter
 *               p_var_names    variable names (comma separated)
 *               p_var_values   variable values (comma separated)
 *               result value   APEX url
 */
  function apex_url
      ( p_app_id            in  varchar2  default null
      , p_app_page_id       in  varchar2  default null
      , p_session           in  varchar2  default null
      , p_request           in  varchar2  default null
      , p_debug             in  varchar2  default null
      , p_clearcache        in  varchar2  default null
      , p_var_names         in  varchar2  default null
      , p_var_values        in  varchar2  default null
      ) return varchar2 is
    l_link      varchar2(1000) := 'f?p=[APP_ID]:[APP_PAGE_ID]:[SESSION]:[REQUEST]:[DEBUG]:[CLEARCACHE]:[VAR_NAMES]:[VAR_VALUES]';
  begin
    l_link := replace(l_link,'[APP_ID]'      , coalesce(p_app_id     ,v('APP_ID')      ) );
    l_link := replace(l_link,'[APP_PAGE_ID]' , coalesce(p_app_page_id,v('APP_PAGE_ID') ) );
    l_link := replace(l_link,'[SESSION]'     , coalesce(p_session    ,v('SESSION')     ) );
    l_link := replace(l_link,'[REQUEST]'     , p_request    );
    l_link := replace(l_link,'[DEBUG]'       , p_debug      );
    l_link := replace(l_link,'[CLEARCACHE]'  , p_clearcache );
    l_link := replace(l_link,'[VAR_NAMES]'   , p_var_names  );
    l_link := replace(l_link,'[VAR_VALUES]'  , p_var_values );
    return(l_link);
  end;
  
/*******************************************************************************
 * Procedure add_navigation_bar_entry
 * Description : Builds a query for a navbar entry and adds this to the overall
 *               query. 
 *               
 * Parameters  : p_label                  text displayed on the entry
 *               p_level                  level of the entry (default=1)
 *               p_target                 link as target for the entry (default='#')
 *               p_badge                  text for a badge
 *               p_icon_css_classes       font awesome/apex class for icon
 *               p_list_item_css_classes  css class for the list item entry
 */
  procedure add_navigation_bar_entry
      ( p_label                   in  varchar2
      , p_level                   in  number    default 1
      , p_target                  in  varchar2  default '#'
      , p_badge                   in  varchar2  default null
      , p_icon_css_classes        in  varchar2  default null
      , p_list_item_css_classes   in  varchar2  default null
      ) is
    l_query       varchar2(4000) := q'!select '[level]'   as  lvl
     , '[label]'   as label
     , '[target]'  as  target
     , null      as  attr1
     , '[icon_css_classes]'  as  icon_css_class
     , null      as  attr3
     , null      as  attr4
     , '[badge]'   as  badge
     , '[list_item_css_classes]'  as  list_item_css_classes
     , [seqno]   as  seqno
from   dual
    !';
  begin
    -- add union if not first item
    if g_query is not null then 
      g_query := g_query || CRLF || 'union' || CRLF;
    end if;
    
    -- build query
    l_query := replace(l_query,'[level]'                 , p_level                );
    l_query := replace(l_query,'[label]'                 , p_label                );
    l_query := replace(l_query,'[target]'                , p_target               );
    l_query := replace(l_query,'[icon_css_classes]'      , p_icon_css_classes     );
    l_query := replace(l_query,'[badge]'                 , p_badge                );
    l_query := replace(l_query,'[list_item_css_classes]' , p_list_item_css_classes);
    l_query := replace(l_query,'[seqno]'                 , g_seqno                );
    g_query := g_query || l_query;

    g_seqno := g_seqno + 1;
    
  end;
  
  function in_development_environment return boolean is
    l_app_id      number;
  begin
    l_app_id := to_number(v('APP_ID'));
    return( l_app_id < 2000 );
  end;

/*******************************************************************************
 * Function finish_query
 * Description : Finishes the query. The composed query is surrounded by a query
 *               with an order by on seqno to get the entries in the right order.
 */
  procedure finish_query is
  begin
    g_query := 'select * from ('||g_query||') order by seqno desc';
  end;
  
/*******************************************************************************
 * Function navigation_bar_query
 * Description : Builds the query for the Navigation Bar List.
 *               The query yields:
 *               - logout link
 *               - display of username
 *               - link to Applications menu
 *               - development environment indicator
 *               The order of the elements results in a right to left order of
 *               entries, so the first entry is the right most one.
 *               
 * Parameters  : return value     query for the list
 */
  function navigation_bar_query return varchar2 is
    l_target      varchar2(1000) := null;
  begin

    -----------------------------------------------------    
    -- logout entry
    l_target := 'apex_authentication.logout'
                ||'?p_app_id='||v('APP_ID')
                ||'&p_session_id='||v('APP_SESSION');
    add_navigation_bar_entry
         ( p_label                   =>  'Logout'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-sign-out'
         );

    -----------------------------------------------------    
    -- user entry
    add_navigation_bar_entry
         ( p_label                   =>  initcap(v('APP_USER'))
         , p_icon_css_classes        =>  'fa-user'
         );

    -----------------------------------------------------    
    -- applications entry
    l_target := apex_url
                    ( p_app_id       =>  g_application_id
                    , p_app_page_id  =>  'APP_MENU'
                    );
    add_navigation_bar_entry
         ( p_label                   =>  'Applications'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-desktop'
         );

    -----------------------------------------------------    
    -- contact entry
    if v('APP_ID') in (1100,1110) then
      l_target := apex_url
                    ( p_app_page_id  =>  'CONTACT'
                    );
      add_navigation_bar_entry
         ( p_label                   =>  'Contact'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-phone'
         );
    end if;
    
    -----------------------------------------------------    
    -- DEVELOPMENT entry
    if in_development_environment then
      add_navigation_bar_entry
           ( p_label                   =>  'DEVELOPMENT'
           , p_icon_css_classes        =>  'fa-exclamation-circle'
           , p_list_item_css_classes   =>  'yellow'
           );
    end if;

    finish_query;

    return(g_query);
  end;

end nav_pck;
/