How to show indentation?

[ReportingAnalyst]

Hi,

I built indentation that would show the the hierarchy of the people in the org structure in the SQL Server database. So I have got rows with values like this:

Indentation Employee
1 4655
2 5623
2 8956
2 5629
2 5627
3 5986
3 8549

The query that got that the above result is:

Now how can i use this indentation to show the employees indented respectively in my web browser?

Thanks.

 

[Sheco]

If you know the max depth you could use an HTML table.

Otherwise you could do something like this:[tt]
intIndent = adoRS("Indentation") & ""
if intIndent = "" then
intIndent = 0
end if

for x = 1 to intIndent
Response.Write "     "
next
[/tt]

 

[ReportingAnalyst]

If I do a SQL Statement:

Select Max(Indentation) from View_OrgStructure_Indent

I get a value of 5, but I do not want to hardcode that value into the ASP page.

 

[Tarwn]

You could use CSS to handle the indentation. It's not perhaps the prettiest solution, but 5 classes wouldn't be hard:

indent1{ padding-left: 0px }
indent2{ padding-left: 3px }
indent3{ padding-left: 6px }
indent4{ padding-left: 9px }
indent5{ padding-left: 12px }

And then output your cell (if your using a table) to be indented as

Response.Write "<tr><td class=""indent" & RS("indented") & """>" & RS("Employee") & "</td></tr>"

Another option would be to output inline CSS:

Response.Write "<tr><td style=""padding-left: " & (RS("indented") - 1) * 3 & "px"">" & RS("Employee") & "</td></tr>"

If your employees were nested inside one another (ie, the indent 2's inside indent 1's and the indent 4's inside 3's that were in turn inside 2's that were inside 1's) then all you would need is a single indent class so that the children would indent further than their parents, and their children would indent a bit further, etc. So a single class with left-padding would work in that situation.

-T

 

[ReportingAnalyst]

If your employees were nested inside one another (ie, the indent 2's inside indent 1's and the indent 4's inside 3's that were in turn inside 2's that were inside 1's) then all you would need is a single indent class so that the children would indent further than their parents, and their children would indent a bit further, etc. So a single class with left-padding would work in that situation.

Yes, 5 are under 4 which are under 3 which are under 2 and which are under 1.

How can I use CSS for this to handle the different indentation?

Thanks.

 

[ReportingAnalyst]

I tried your example using styled UL tags:

My HTML output looks like this:

12   Manuel Abad Ibanez(40801) 
0   Bill Adkins(40104) 
12   Ashish Agarwal(41736) 
6   Gustavo Agusti(41034) 
9   Sanjay Ahuja(40431)

My ASP code that rendered the above output is:

<ul id="menu">
		<%
		For iRows = 0 to UBound(aEmp, 2)
		indent = (aEmp(C_Indentation, iRows) - 1) * 3
		%>
			<li>
				<%=indent%> &nbsp;<input type="radio" checked="checked" name="empid" id="empid<%=aEmp(C_EmpID, iRows)%>" value="<%=aEmp(C_EmpID, iRows)%>">
				<a href="#" class="a_style"><%=aEmp(C_EmpName, iRows)%></a><font size=1>(<%=aEmp(C_EmpID, iRows)%>)</font>
				<ul id="UI_<%=aEmp(C_SubID, iRows)%>">

				</ul>
			</li>
		<%
		Next
		%>
		</ul>

My question here is how to makes nested <UL> tags based on the indent numbers?

Any ideas?

Thanks.

 

[Tarwn]

Just doing it based on the indent numbers is going to be a little difficult. I think you should be able to use the previous number to determine how many lists were opened before a particular item so you can open or close more lists.

So in each loop you would compare the indent number of the previous value to the current one. If the current one is greater than you would subtract the two and output nested UL's. If the current one is less than the previous one then you would output end UL's. You would also need to end each LI that had been opened right before the UL in the code that was ending LI's. After outputting your value you would need to determine if you can safely close your current LI or need to leave it open and start a UL.



Something like:

Dim ulCtr

Response.Write "<ul>"
For iRows = 0 to UBound(aEmp, 2)
   'If this is not the first record
   If iRows > 0 Then
      'output any necessary close ul tags
      If aEmp(C_Indentation, iRows-1) > aEmp(C_Indentation, iRows) Then
         'close as many open indents as necessary
         For ulCtr = aEmp(C_Indentation, iRows) to aEmp(C_Indentation, iRows - 1)
            Response.Write "</ul></li>"
         Next
      End If
   End If
   'start the list item for this value
   Response.Write "<li>" & aEmp(C_EmpID, iRows)

   'if next one exists
   If iRows + 1 <= UBound(aEmp, 2) Then
      'if the next one has a greater indent, start a UL and leave the LI open
      If aEmp(C_Indentation, iRows) < aEmp(C_Indentation, iRows + 1) Then
         For ulCtr = aEmp(C_Indentation, iRows) To aEmp(C_Indentation, iRows + 1)
            Response.Write "<ul>"
         Next
      'else go ahead and close the current LI
      Else
         Response.Write "</li>"
      End If      
   End If
Next
Response.Write "</ul>"

I wrote this from scratch, but it shouldillustrate a little better what i meant in my explanation above. the other thing you would want to add is some code after the loop to output </li></ul> pairs based on the indentation of the last item in the array (before the final ul). This will close any additional lists that were left open by the loop.

I'm not sure this will necessarally give you what you were looking for. The CSS indent method might be a lot easier to achieve a similar outcome with less server-side processing.

-T

 

[ogri]

Hi

Seems like a perfect situation to use a recursive function. Have each call of the function get a level of employees under a supervisor. The output those, so you just have to format a single indent at a time.

For example (psuedo code)

Function WriteSubordinates(parmEmployeeRef)

[tab]Select EmployeeRef, EmployeeName from Staff where Supervisor = parmEmployeeRef

[tab]If not EOF
[tab][tab]Write "<OL>"
[tab][tab]While not EOF
[tab][tab][tab]Write "<LI>" & EmployeeName
[tab][tab][tab]Call WriteSuborinates(EmployeeRef)
[tab][tab][tab]NextRecord
[tab][tab]Wend
[tab][tab]Write "</OL>"
[tab]End if

End Function

All the best

Keith

 

[Tarwn]

Actually this is the perfect time to not use a recursive function. It is much more efficient to get all the data back at one time then to recursively make new SELECT calls to the database.
ADO is not terribly efficient. Dump that lack of efficiency in multiple connections in a recursive fashion and you will add a great deal of processing time to your page as well as unnecessary load to both the server and the database.

 

[ogri]

Multiple connections should not be required, although you will have multiple recordsets open within that conection. Each recordset should be small, and properly keyed should perform well enough.

Advantage of doing it recursively is that there is no need to know or calulate the levels of indentation in advance (or have them stored in the database). Purely works from the data. If (the original query being a company structure) an extra couple of layers of management were inserted between levels 1 and 2 for one of the level 1 managers then it would be simple to update the data and no changes would be required to the code.

For example

Table of:-
EmployeeRef[tab]Integer
EmployeeName[tab]String
Supervisor[tab]Integer

Giving
1[tab]Jo[tab]Null
2[tab]Fred[tab]1
3[tab]Bill[tab]1
4[tab]Jane[tab]2
5[tab]Kate[tab]2
6[tab]Fry[tab]3
7[tab]John[tab]5
8[tab]Anne[tab]7

With this if you wanted to insert a level of Greg (9) between Fred (2) and Kate (5) you could do so, and the only update required to the rest of the data would be to amend Kate (5) to have 9 as their supervisor.

While there might well be a performance hit for doing it recursively, the gain in ease of maintenance and flexibility should more than compensate.

There is the possibility that you could use a recursive stored procedure in SQL Server, and fiddle round passing a value through for a level of indentation between each call.

All the best

Keith

 

[Tarwn]

Your correct, I did indeed mispeak when I said multiple connections. However, I cannot agree on using recursion for this problem in either the database or the application. While I am a big fan of recursion, there are quite a few ways to implement database tables for nested data without requiring recursion to query for all of the data from the server. On the ASP side, ADO objects are still terribly innefficient. There is a good amount of overhead inherent in creating and manipulating a recordset object without even counting the extra overhead of multiple queries.

Switching to a nested set model instead of the more common adjency list model would allow for a great deal more flexibility. With the creation of 3 stored procedures (add employee, remove employee, move employee) it would be actually be more manageable then the adjency list you suggest. The data is easily queryable in one query without recursion, actually becomes easier to output in a single loop, and the 3 stored procs would be much simpler then those required to do the same thing in an adjency list model.

 

[ogri]

Hi

Can see how it would remove the use of recursion (and fair enough if you want to avoid any overhead like that), although I am a bit dubious that it would be easier to query / update the resulting tables (although deleting would certainly be easier).

Personally I think the recursive method is easier to read and a simpler model.

All the best

Keith

 

[ESquared]

A simple order by in the query and recursion becomes completely unnecessary.

I say use the power of an enterprise database engine's optimized sorting algorithm, instead of writing slow ASP code to do anything recursively.

 

[ogri]

Hi

My point is that the sorting only works if you have already got the levels of indentation / order already precalculated.

Easy enough on static data, but the more often it changes the less useful this would be.

All the best

Keith

 

[ESquared]

Fair enough. But I would do some serious testing:

- Multiple hits to the database to get each next item
- One hit to the database to get a client-side cursor recordset, and step through and around the recordset in the ASP to build the result.
- One extra step in the database code that feeds everything which calculates the indent level and orders correctly.

I expect there could be a wide variation in total performance. There are also issues of where the load is and where you want the load to be, web server or database server.