First we need to create an HTML unordered lists. That is where the previous post comes in, in that post I showed how you can use CTE to compare the current row to the next row or even the previous row. If we change the sql just a bit to use a union rather than a left outer join it will simplify how we can use the sql to generate our html list.
This is the SQL I used in Oracle (please note the orgcode is only populated after the POS006A.sqr is run):
With Cte As (Select Rownum Rownumber, Position_Nbr, Length(Orgcode)/ 3 As Level1 From Ps_Position_Data Where Orgcode Like (Select Orgcode||'%' From Ps_Position_Data X Where X.Position_Nbr = '?' And X.Orgcode > ' ') Order By Orgcode)
Select c1.rownumber,case c1.rownumber when 1 then '<ul class="org"><li><span>' else '<li><span>' end As linebegin ,C1.Position_Nbr, CASE WHEN C2.Level1 < C1.Level1 THEN '</span></li>'|| RPAD ('</ul>', LENGTH('</ul>')*(C1.Level1 - C2.Level1), '</ul>') ELSE Case C2.Level1 - C1.Level1 When 0 Then '</span></li>' Else '</span><ul>' End END As Htmlend From Cte C1 , Cte C2 Where C1.Rownumber = C2.Rownumber - 1
Union
Select Lastrow.Rownumber,'<li><span>' As linebegin ,Lastrow.Position_Nbr, '</span></li>'||Rpad ('</ul>', Length('</ul>')*(Lastrow.Level1 - Firstrow.Level1), '</ul>') Endline From Cte Lastrow, Cte Firstrow Where Lastrow.Rownumber = (Select Count(*) From Cte) And Firstrow.Rownumber = 1 ;
Use this SQL and replace the '?' mark with a position number that has multiple reports, for the purpose of this exercise, don't use the CEO, pick someone with a fair amount of direct reports, in my case I picked 02010087, because he had 103 direct and indirect reports. Also use someone who has multiple levels below them. Run the SQL and copy the results (Minus the rownumber column)
<ul class="org"><li><span> 02010087 </span><ul>
<li><span> 02007923 </span><ul>
<li><span> 02008014 </span></li>
<li><span> 02008027 </span></li>
<li><span> 02008041 </span></li>
<li><span> 02008165 </span></li>
<li><span> 02009161 </span></li>
<li><span> 02010837 </span></li>
<li><span> 02011087 </span></li>
<li><span> 02019820 </span></li>
<li><span> 02020540 </span></li>
<li><span> 02020652 </span></li>
<li><span> 02020752 </span></li>
<li><span> 02020849 </span></li></ul>...:
Take your results and test them out using w3schools
If you copy your sql results (without the rownumber) between the <body> tag and press the submit code button your results should look like this:
Now if we add the JavaScript we can even test the jQuery Tree right here!
Add this JavaScript after the <HTML> Tag and press the submit code button:
<script src="https://github.com/pioz/jquery-tree/raw/master/jquery.min.js" type="text/javascript">
</script>
<script src="https://github.com/pioz/jquery-tree/raw/master/jquery.cookie.js" type="text/javascript">
</script>
<script src="https://github.com/pioz/jquery-tree/raw/master/jquery.tree.js" type="text/javascript">
</script>
<script type="text/javascript">
$(document).ready(function( ) {
$('ul.org').tree({default_expanded_paths_string : 'all'});
});
</script>
Now if you change your HTML to make the Position number to a link to some content regarding the position number and show the title and maybe even the incumbent, then this could be turned into a pagelet that would work really well within a workcenter.
Thank you! Enrico Pilotto for a very cool project!
No comments:
Post a Comment