Team management Part-I (View users)

Team Management

1. Team Management part 1 (View users) [Current]
2. Team Management Part II (Add Users) 

In various applications user is the main character to play a role and for software developer, user management is a big issue to handle. Many thing like user satisfaction, user info secrecy, child parent management, easy to handle, scalability etc. are one of the parts of user management process.  Today in this post I choose to write about the parent child relation within a user management or rather we should say the Team management. This is only the part 1, registration checking delete and many others are coming after this.

What is a Team?

So what is a Team? A group of users which has an admin or manager who is managing a set of people or users. And with all those users a team is forming. In the terms of database all the users are virtual, there is no existing of a physical user. So how to manage those virtual users from our database. Lets see.

Basic Database design:

Database design is one of the most important part of a team management process. If your table structure is not properly maintained then many more problems will arise as the no. of team members will increase. As it follows a tree structure you can take a single table or more than one table to put your users with their all details and an important one, their level.  Level is the one thing from which you can actually track your users and do a descend tracking of users.

Database Design:

UserId (Primary Key)	Int (Identity(1,1))
Email	NVarChar(255)
Name	NVarChar(255)
Password	NVarChar(255)
ParentId	Int
IsDelete	Bit

Here I didn't take any position to place the member under an admin. All the users are under one roof. And they are distinguished by UserId & Email and described as a child of a Parent with ParentId.

Design of the database is quite simple and straight forward. I hope you will understand it clearly.


Query to fetch:

Now we have to write the SQL query to fetch the data along with the parent details. The most easiest and simplest way is to use Self Join. To know Self join more precicely you can follow either w3resource.com or w3schools.com. Both have explained it very well.  Now lets come to our query.

select a.UserId, a.Name, a.Email, b.UserId, b.Name, b.Email from tblUser a inner join tblUser b

on a.ParentId = b.UserID

This query will return the all users with their and their parents details.

select a.UserId, a.Name, a.Email, b.UserId, b.Name, b.Email from tblUser a inner join tblUser b

on a.ParentId = b.UserID and a.UserId = '<user_id>'

With the help of this query you can get a single user's details with his/her parents details.

Now you have to find out the possible children and possible parents of a selected node.  For this follow the bellow SQL query.

Query to find all possible parents

DECLARE @UserId INT;

SET @UserId = 4;

WITH tblParent AS

(

    SELECT *

        FROM tblUser WHERE UserId = @UserId

    UNION ALL

    SELECT tblUser.*

        FROM tblUser  JOIN tblParent  ON tblUser.UserId = tblParent.ParentId

)

SELECT * FROM  tblParent

    WHERE UserId <> @UserId

OPTION(MAXRECURSION 32767)

Query to find all possible children

DECLARE @userId INT;

SET @userId = 1;

WITH tblChild AS

(

    SELECT *

        FROM tblUser WHERE ParentId = @userId

    UNION ALL

    SELECT tblUser.* FROM tblUser  JOIN tblChild  ON tblUser.ParentId = tblChild.UserId

)

SELECT *

    FROM tblChild

OPTION(MAXRECURSION 32767)

View Team:

You have done your database, you have shown the details of user and their parents information in a tabular format. Now its time for some graphics. We will produce a genealogy or family view of the whole team, or you can set the admin from a particular user (can be set by sql query).

There are whole lots of process to generate a genealogy view. Here I choose two of the easiest way to generate.

1. Using Google Organizational Chart
2. Using ul, li and CSS3

Google Organizational Chart:

You can get the full details of Google Organizational chart from here. But here I will show you how to connect this with your SQL database. For this you don't need to do much more thing. You just have to create the string which will contain the user data along with their parents' id or name.

Just have a look over the line

data.addRows([['Mike',''], ['Jim', 'Mike'], ['Alice', 'Mike'],['Bob','Jim'],['Carol', 'Jim']]);

It contains the whole of the operation, it is holding the data. You have to create this data string to generate your parent child view. I am taking a DataTable to store the data. You will use the upper sql query to do so.

string s = "";

DataTable table = new DataTable();

table.Columns.Add("name", typeof(string));

table.Columns.Add("parent", typeof(string));

table.Rows.Add("Mike", "");

table.Rows.Add("Jim", "Mike");

table.Rows.Add("Alice", "Mike");

table.Rows.Add("Carol", "Jim");

for (int i = 0; i < table.Rows.Count; i++)

{

     s = s + "['"+table.Rows[i][0].ToString()+"','"+table.Rows[i][1].ToString()+"'],";

}

s = s.TrimEnd(',');

Now in the s string you have all the data stored. Only passing the data stream to JS is left. To do follow the next step.

String csname1 = "PopupScript";

Type cstype = this.GetType();

ClientScriptManager cs = Page.ClientScript;

if (!cs.IsStartupScriptRegistered(cstype, csname1))

{

     StringBuilder cstext1 = new StringBuilder();

     cstext1.Append("<script>");

     cstext1.Append("google.setOnLoadCallback(drawChart);");

     cstext1.Append("function drawChart() {");

     cstext1.Append("var data = new google.visualization.DataTable();");

     cstext1.Append("data.addColumn('string', 'Name'); data.addColumn('string', 'Manager');");

     cstext1.Append("data.addRows(["+s+"]);");

     cstext1.Append("var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));");

     cstext1.Append("chart.draw(data, { allowHtml: true });");

     cstext1.Append("}");

     cstext1.Append("</script>");

     cs.RegisterStartupScript(cstype, csname1, cstext1.ToString());

 }

Here I am using ClientScriptManager to run the created JS code from back end(C# code). Run the code and see the result. 

Using ul, li and CSS3:

We have seen how to do this with he help of Google Organizational Chart. Now its time for a pure HTML, CSS work to do. For that We need to generate the HTML. Follow the bellow code to generate the HTML quickly.

DataView view = new DataView(table);

DataTable distinctValues = view.ToTable(true, "parentid");

tree += "<div class='tree'><ul><li style='background-color: white;'>Relegare(Admin)[rel]</li></ul></div>";

 for (int i = 1; i < distinctValues.Rows.Count;i++ )

 {

     SqlDataAdapter da2 = new SqlDataAdapter("select userid,fname+' '+lname from tbl_userlogin where parentid='" + distinctValues.Rows[i]["parentid"] + "'",con);

     DataTable dt2 = new DataTable();

     da2.Fill(dt2);

     string temp = "";

     for(int j =0;j<dt2.Rows.Count;j++)

     {

         if (j == 0)

         {

              temp += "<ul>";

         }

         temp += "<li>" + dt2.Rows[j][1].ToString() + " [" + dt2.Rows[j][0].ToString() + "]</li>";

         if (j == dt2.Rows.Count-1)

         {

              temp += "</ul>";

              if (i == 1)

              {

                   tree = tree.Replace("[rel]", temp);

              }

              else

              {

                   tree = tree.Replace("["+distinctValues.Rows[i][0].ToString() + "]", temp);

              }

         }

     }

}

tree = tree.Replace("[]","");

tree = Regex.Replace(tree, @"\[(.*?)\]", "");

For CSS follow the jsfiddle online demo.

Run your own project to check the team management, though I didn't mention the add or edit/delete part of the team management. In next article I will discuss about the manage roles and permissions of users from their admin end. Enjoy coding.

Download the Download the full source code of Google Organizational chart here.

CodeProject