set define off
create or replace package designer_queries
as
   procedure retrieve_network ( p_startmodule varchar2,p_level varchar2);
   procedure retrieve_network_no_roles ( p_startmodule varchar2,p_level varchar2);
   procedure roles_versus_modules ;
   procedure Header ;
   procedure Footer ;

END;
/

create or replace package body designer_queries
as
  procedure retrieve_network (p_startmodule varchar2, p_level varchar2)
  as
   l_level number;
   cursor child_exists(b_irid number)
   is
      select 1
        from CI_MODULE_NETWORKS mm
       where parent_module_reference = b_irid;
   v_dummy number;
BEGIN
   l_level := p_level;
   IF l_level = '1' THEN
      dbms_output.put_line('     <h3><a name="Module network Including Roles">Module network Including Roles</a></h3> ');

      dbms_output.put_line('<A href="#" onClick="expandTree(''tree1''); return false;">Expand All</A>&nbsp;&nbsp;&nbsp;');
      dbms_output.put_line('<A href="#" onClick="collapseTree(''tree1''); return false;">Collapse All</A>&nbsp;&nbsp;&nbsp;');

      dbms_output.put_line('<ul class="mktree" id="tree1">');
      dbms_output.put_line('   <li class="liOpen">'||p_startmodule||'/ ');
      dbms_output.put_line('   <ul> ');
   END IF;


   FOR rec_start in (select irid from ci_modules where short_name=p_startmodule) LOOP
      FOR rec_level in (select mod_child.short_name child,
                        nvl(lag(mod_child.short_name,1) over (order by mm.called_sequence),'XYZ') prev_child,
                        nvl(lead(mod_child.short_name,1) over (order by mm.called_sequence),'XYZ') next_child,
                        mod_child.top_title top_title_child,
                        mod_child.irid irid_child,
                        mod_child.general_module_type,
                        r.name rolename
                      from CI_MODULE_NETWORKS mm, ci_modules mod_child
                         , CI_ROLE_MODULE_ACCESSES i, CI_ROLES r, ci_modules mod_parent                         
                          where mod_parent.short_name = p_startmodule 
                          and  mm.parent_module_reference = mod_parent.irid 
                          and  mm.child_module_reference = mod_child.irid 
                          and mod_child.irid = i.general_module_reference
                          and  r.irid = i.role_reference
                          order by mm.called_sequence) loop
        IF  rec_level.child <>  rec_level.prev_child THEN
           dbms_output.put_line(lpad(' ',l_level*3,' ')||'<li>'||rec_level.child||' - '||rec_level.top_title_child||'</li>');
           IF l_level < 3 or rec_level.general_module_type = 'MENU' THEN
              open child_exists(rec_level.irid_child);
              fetch child_exists into v_dummy;
              if  child_exists%found then
                dbms_output.put_line('<ul>');
                 retrieve_network(rec_level.child,l_level + 1);
                dbms_output.put_line('</ul>');
              end if;
              close child_exists;
           END IF;
           dbms_output.put_line('<ul>');
              dbms_output.put_line('<li>ROLES</li>');
             dbms_output.put_line('<ul>');
        END IF;

        dbms_output.put_line('<li>'||rec_level.rolename||'</li>');

        IF  rec_level.child <>  rec_level.next_child THEN
           dbms_output.put_line('</ul>');
        END IF;

        IF  rec_level.child <>  rec_level.next_child THEN
            dbms_output.put_line('</ul>');
        END IF;

      END LOOP;

   END LOOP;
   IF l_level = '1' THEN
      dbms_output.put_line('</ul>');
      dbms_output.put_line('</li>');
      dbms_output.put_line('</ul>');
   END IF;
END retrieve_network;

  procedure retrieve_network_no_roles (p_startmodule varchar2, p_level varchar2)
  as
   l_level number;
   cursor child_exists(b_irid number)
   is
      select 1
        from CI_MODULE_NETWORKS mm
       where parent_module_reference = b_irid;
   v_dummy number;
BEGIN
   l_level := p_level;
   IF l_level = '1' THEN
      dbms_output.put_line('     <h3><a name="Module network Without Roles">Module Network without roles</a></h3> ');

      dbms_output.put_line('<A href="#" onClick="expandTree(''tree1''); return false;">Expand All</A>&nbsp;&nbsp;&nbsp;');
      dbms_output.put_line('<A href="#" onClick="collapseTree(''tree1''); return false;">Collapse All</A>&nbsp;&nbsp;&nbsp;');

      dbms_output.put_line('<ul class="mktree" id="tree1">');
      dbms_output.put_line('   <li class="liOpen">'||p_startmodule||'/ ');
      dbms_output.put_line('   <ul> ');
   END IF;


   FOR rec_start in (select irid from ci_modules where short_name=p_startmodule) LOOP
      FOR rec_level in (select mod_child.short_name child,
                        nvl(lag(mod_child.short_name,1) over (order by mm.called_sequence),'XYZ') prev_child,
                        nvl(lead(mod_child.short_name,1) over (order by mm.called_sequence),'XYZ') next_child,
                        mod_child.top_title top_title_child,
                        mod_child.irid irid_child,
                        mod_child.general_module_type
                      from CI_MODULE_NETWORKS mm, ci_modules mod_child, ci_modules mod_parent                         
                          where mod_parent.short_name = p_startmodule 
                          and  mm.parent_module_reference = mod_parent.irid 
                          and  mm.child_module_reference = mod_child.irid 
                          order by mm.called_sequence) loop
        IF  rec_level.child <>  rec_level.prev_child THEN
           dbms_output.put_line(lpad(' ',l_level*3,' ')||'<li>'||rec_level.child||' - '||rec_level.top_title_child||'</li>');
           IF l_level < 4 or rec_level.general_module_type = 'MENU' THEN
              open child_exists(rec_level.irid_child);
              fetch child_exists into v_dummy;
              if  child_exists%found then
                dbms_output.put_line('<ul>');
                 retrieve_network_no_roles(rec_level.child,l_level + 1);
                dbms_output.put_line('</ul>');
              end if;
              close child_exists;
           END IF;
        END IF;

      END LOOP;

   END LOOP;
   IF l_level = '1' THEN
      dbms_output.put_line('</ul>');
      dbms_output.put_line('</li>');
      dbms_output.put_line('</ul>');
   END IF;
END retrieve_network_no_roles;


procedure roles_versus_modules 
as
  l_initialized boolean := false;
BEGIN
   dbms_output.put_line('   <h3><a name="RolesModules">Modules Access By Role</a></h3> ');

   dbms_output.put_line('<A href="#" onClick="expandTree(''tree2''); return false;">Expand All</A>&nbsp;&nbsp;&nbsp;');
   dbms_output.put_line('<A href="#" onClick="collapseTree(''tree2''); return false;">Collapse All</A>&nbsp;&nbsp;&nbsp;');

   dbms_output.put_line('<ul class="mktree" id="tree2">');
   dbms_output.put_line('   <li class="liOpen">Roles ');
   dbms_output.put_line('   <ul> ');

   FOR rec_start in (select case rn
                     when 1
                     then role
                     end role
                , module||' - '||top_title module
                from ( select mde.name module
                            , mde.top_title
                            , rle.name role
                            , row_number() over (partition by rle.name
                                                       order by mde.name
                                                      ) rn
                            from ci_role_module_accesses rms
                            , ci_roles rle
                            , ci_general_modules mde
                            where rle.id = rms.role_reference
                            and rms.general_module_reference = mde.id
                            order  by rle.name , mde.name )) loop
        IF rec_start.role is not null THEN
           if l_initialized then
              dbms_output.put_line('</ul>');
           else
              l_initialized := true;
           end if;
           dbms_output.put_line('<li>'||rec_start.role||'</li>');
           dbms_output.put_line('<ul>');
        END IF;

        dbms_output.put_line('<li>'||rec_start.module||'</li>');


   END LOOP;

      dbms_output.put_line('</ul>');
      dbms_output.put_line('</li>');
      dbms_output.put_line('</ul>');
END roles_versus_modules;

PROCEDURE HEADER 
is
BEGIN
      dbms_output.put_line('<HTML>');
      dbms_output.put_line('<HEAD>');
      dbms_output.put_line('   <TITLE>List of Modules</TITLE>');
      dbms_output.put_line('   <SCRIPT LANGUAGE="JavaScript" SRC="mktree.js"></SCRIPT>');
      dbms_output.put_line('   <LINK REL="stylesheet" HREF="mktree.css">');
      dbms_output.put_line(' <link rel="stylesheet" type="text/css"  href="style_sdp1.css" /> ');

      dbms_output.put_line('</HEAD>');
      dbms_output.put_line('<BODY BGCOLOR=#FFFFFF LINK="#00615F" VLINK="#00615F" ALINK="#00615F">');

      dbms_output.put_line('<div id="navwrap">');
      dbms_output.put_line('<div id="topnav">');
      dbms_output.put_line('<font size=3><b>Designer Queries</b></font>');
      dbms_output.put_line('<ul>');
      dbms_output.put_line('<li><a href="./designerqueries.html">Designer</a></li>');
      dbms_output.put_line('<li><a href="./wf_team.html">About us</a></li>');
      dbms_output.put_line('<li><a href="./wf_admin.html">Admin</a></li>');
      dbms_output.put_line('<li><a href="./wf_faq.html">Faq</a></li>');
      dbms_output.put_line('<li><a href="./wf_docs.html">Docs</a></li>');
      dbms_output.put_line('<li><a href="./sdp.html">Home</a></li>');
      dbms_output.put_line('</ul> ');

      dbms_output.put_line('</div>');

      dbms_output.put_line('<TABLE WIDTH="100%" BORDER="0"><TR><TD WIDTH="100%" ALIGN="LEFT" VALIGN="TOP">');

      dbms_output.put_line('<BR>');
      dbms_output.put_line('        <UL>');
      dbms_output.put_line('            <li type=a><a href="designerqueries.html">Modulenetwork without Roles</a></li>');
      dbms_output.put_line('            <li type=a><a href="modulenetworkwithroles.html">Modulenetwork including Roles</a></li>');
      dbms_output.put_line('            <li type=a><a href="moduleaccessbyrole.html">Module Access by Role</a></li>');
      dbms_output.put_line('        </UL>');

END HEADER;

PROCEDURE FOOTER
IS
BEGIN
      dbms_output.put_line('<BR><BR>');
      dbms_output.put_line('<HR>');
      dbms_output.put_line('</TD><TD VALIGN="TOP">');
      dbms_output.put_line('</TD></TR></TABLE>');
      dbms_output.put_line('</BODY>');
      dbms_output.put_line('</HTML>');
END FOOTER;
END;
/  

create public synonym designer_queries for designer_queries;
grant execute on designer_queries to public;	

--@p:\sql\generate_designer_module_network.sql