Saturday, May 23, 2015

Team management Part-1 (View 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
Int

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 ulli 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 ulli 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.

Thursday, May 21, 2015

Find all the input in a particular cell of a HTML table

Here in this post I will show you how to fetch all the input data of a particular cell of a HTML table using JQuery. What ever the input we are taking in HTML (text, radio, password, checkbox etc.)

Follow the following JSFiddle to get the value of all input tag value in a particular cell.

How it s happening ?
For each tr and every td of that tr, we are searching for the input tag. And where we are getting an input tag we are taking the value of that element. We have taken value as we are moving on text boxes only. If your code is based on checkbox, radio button or  any thing else, then you have to code according to that.



I have taken here tr:eq(1) td:eq(1) for the only 2nd cell of 2nd tr (searching starts with 0). If you want to search through over all table then use  tr td only.

Customize your code and enjoy...

Sunday, April 26, 2015

Prevent Enter Click on Text Box using Java Script

When ever in your html if you click Enter key on a text box after typing your text the very next submit button is being hit automatically. Now not every time it causes problems but few times it creates an serious issue to the application.
So how to prevent this issue? With java script we can handle this very easily. Lets see how to do so?

Into a notepad document write down this line.

<input type='text' onkeydown="return (event.keyCode!=13);"/>

onkeydown="return (event.keyCode!=13);" 

This is the main code to prevent the Enter press within the textbox. 

Note: ASCII key of ENTER is 13.

Now save your document, run it in browser and check.

On line demo:



How to upload Database in Godaddy Server, explain with images

As a request form one of my follower I am writing this post about how to upload your Ms. SQL Server Database. Just follow my steps and in new few minutes your database will be on line in your Godaddy server.

Step 1:
First generate a script of your database with data (Schema & data).

Step 2:
Open your Godaddy server login into it and Go to Web Sites and Domain section. There is a link for Database (marked in pic) section. Click on it and you will be redirect to Database section.


Step 3:
Right now there is no database exists. So you have to create a new one. Click on the Add new database button and go to Add New Database page.

Step 4:
Here you have to enter your database name and  choose Microsoft SQL Server from Type drop down list.
Now its come to the user part. Here I don't have an exiting user so I have to create an user to access the database. So enter the user name and password for the user. You can generate the password for user also.
After all entry click on the OK button and if there is no issue you will be redirect to the previous page where you can see that you have a database with your given name and its user.  Now your database is created and you can add your tables, stored procedures, functions, views  and also insert data.

Note: make a note of the provided IP address and pin no. It will use in case of writing connection string in your web.config file.



Step 5:
To modify your database click on the webadmin link on the right side of your database. It will redirect you to a database management tool.



Step 6:
Click on the Tools section and within that on New Query button.


Step 7:
A new editor will be open for you to write sql query in it. Now copy and paste your local database script into this editor and click on the submit button.


Step 8:
After few minutes(as per your database size) your database will be on line. Now go back to your project. You have to change the connection string of you web.config file to attach this on line database.


Add the following connection string:

<add name="mlm_real_1050ConnectionString" connectionString="Data Source=<IP address>,<PIN No>; Initial Catalog=<Database name>;Network Library=DBMSSOCN;User ID=<User Name>;Password=<Password>;"      providerName="System.Data.SqlClient" />

Now upload your project and enjoy with your website.

Sunday, April 19, 2015

Import Export SQL Server database to Ms. Exel

Here in this post I will show you how to retrieve database table from Ms. Excel step by step with images. First create an excel file where all your data will be saved. 


Now saved this excel file into your drive and open Ms SQL Server management studio. Create a new database or you can do this in your existing database.


On clicking the button import a window will be opened with Data Source option. Choose Ms. Excel from here. 


Now click on the next button to choose Destination. 
Choose SQL Server and your database name from the drop down.


Now only few steps are ahead. Click on the Next button and go to Tables and Views Section. Here select the proper sheet of your excel. And change the name of table as you want.


By clicking on next few Next buttons you will get a success message with no of rows transferred.


Now cross check your database. And also your table which have been created during this process.


Now do it with yours and enjoy.

  

Get currency format using Google in ASP.NET

Many of times I have encountered by a vital question, "How to change 20000.00 to $20,000.00". This is nothing but a simple currency formatter. You can use either jquery or server side coding to show the currency in this format. Here in this post I will show you how to format the currency input from user in ASP.NET.

Format Currency using JQuery:
First start with JQuery. Google has provided a very simple way to format currency. You can find it out from here. Or you can follow this one.

To continue you have to download two js files. One is jquery.formatCurrency-1.4.0.js and second one is jquery.min.js. I have attached these js files with the code I have attached along with this post.

So lets start with sample coding. First create a new project and add a new page, named it whatever you want. Then add a new text box. Firstly we will do it with onBlur function of jquery, so we don't need any more extra button for showing our formatted currency.

Add those downloaded js files into your header section of web form. And the paste the following code into your page.

<script src="jquery.min.js"></script>
<script src="jquery.formatCurrency-1.4.0.js"></script>
<script type="text/javascript">
        $(document).ready(function () {
            $('.text').focusout(function () {
                $('.text').formatCurrency();
                $('.text').formatCurrency('.currencyLabel');
            });
        });       
</script>

Now copy the text box.

<div>
     <p>Currency Formatter</p>
     <asp:TextBox runat="server" ID="txtPrice" CssClass="text"></asp:TextBox>
     Show by Jquery: <span class="currencyLabel"></span>
</div>

Check the CssClass of text box. Its the thing by which formatCurrency() method is calling to format it to text box and also show the output value to a span.

Format Currency using C#:
I hope its clear to you how to format currency by JQuery, now lets see how to do this such using C#. Don't worry C# has an inbuilt function for this. For C# we are taking an extra button to display the output into a label.

<asp:TextBox ID="txtCurrency" runat="server"></asp:TextBox>
<asp:Button ID="btnChange" Text="Format" runat="server" OnClick="btnChange_Click"    />
<asp:Label ID="lblShow" runat="server"></asp:Label>

C# Code:

protected void btnChange_Click(object sender, EventArgs e)
{
    lblShow.Text = (Convert.ToDouble(txtCurrency.Text)).ToString("C2");
}

Make sure this method is only applicable to  data type like decimal and double. So you have to add a checking whether user input is bound to numbers.

Download the full source code here.