Display data in hierarchal/Treeview style using jQuery


In this post, I am going to explain you how easily you can display the data in hierarchal/Treeview style using HTML, ASP.NET WebApi and jQuery. This is the functionality that we commonly need in most of the application to present the data to end user. The user must have the basic knowledge of WebApi and jQuery as most of the work is done using both the technologies.

In my example, I will present the SOW and SOW Fin data where SOW is parent and SOW data is child. I hope you will like the post. Leave your inputs in comment area down below. Let’s dig in.

CDN for jQuery Libraries
    <script src="https://code.jquery.com/jquery-1.10.2.js"></script>
    <script src="https://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
StyleSheet
<style>
    .thSow{
        background-color: lightSlateGrey;
        color: #fff;
        font-weight: bold;
        line-height:20px;
        font-family:Calibri;
        font-size:14px;
        width:80px;
        border:1px solid lightGray;
        text-align:center;
    }
    .thFin{
        background-color: peru;
        color: #fff;
        font-weight: bold;
        line-height:18px;
        font-family:Calibri;
        font-size:15px;
        width:80px;
        border:1px solid lightGray;
        text-align:center;
    }
   
    .tdCol{
        width:60px;
        font-family:Calibri;
        font-size:13px;
        line-height:15px;
        width:80px;
        border:1px solid lightGray;
        padding-left:10px;
    }
</style>
HTML Code
<div id="serachResults" style="overflow-x:scroll"></div>
<input type="text" class="form-control text-box single-line" id="filterVal"  style="width:200px" />
<input type="submit" value="Search" class="btn btn-success submit" style="margin-right:25px" onclick="SearchData(this); return true;" />
JavaScript/jQuery Code
$(document).ready(function () {
       SearchData();
});
function SearchData() {
            $.ajax({
                type: "GET",
                url: "@Url.Action("SearchData", "Home")",
                data: { "filterColumn": $('#filterVal').val()},
                contentType: "application/json; charset=utf-8",
                success: function (result) {
                    //debugger;
                    var htm = "";
                    var startTable = ""
, endTable = "
";
                    var startThSow="
"
, startTh = "
"
, endTH = "
", startTR = " " , endTR = "", startTD = "
"
, endTD = "";

                    //Add table Header
                    htm = startTable;
                    for (var i = 0; i < result.length; i++)
                    {
                        var onNumbers=[], offNumbers=[];
                        htm += startTR + startThSow + "" + endTH + startThSow + "Project Name" + endTH + startThSow + "Currency" + endTH + startThSow + "Location" + endTH + startThSow + "Start Date" + endTH + startThSow + "End Date" + endTH + endTR;
                        htm += startTR + startTD + "+i+" onclick=HideShow(this);>" + endTD +
                            startTD + result[i].ProjectName + endTD +
                                       startTD + result[i].Currency + endTD +
                                       startTD + result[i].Location + endTD +
                                        startTD + result[i].SOWStartDate + endTD +
                                        startTD + result[i].SOWEndDate + endTD;
                        htm += endTR + startTR + "+i+">" + startTR;
                        for (var j = 0; j < result[i].SOWFin.length; j++) {
                            htm += startTh + result[i].SOWFin[j].ColumnHeader + endTH;
                        }
                        htm += endTR + startTR + startTD + "Offshore" + endTD;//Offshore
                        for (var k = 1; k < result[i].SOWFin.length; k++) {
                            htm += startTD + (selCurrRate > 0 ? (result[i].Currency == "USD" ? ((result[i].SOWFin[k].OffshoreNumber) / selCurrRate) : (result[i].SOWFin[k].OffshoreNumber) * selCurrRate) : result[i].SOWFin[k].OffshoreNumber).toFixed(3) + endTD;
                            offNumbers.push(result[i].SOWFin[k].OffshoreNumber);
                        }
                        htm += endTR + startTR + startTD + "Onshore" + endTD;//Onshore
                        for (var k = 1; k < result[i].SOWFin.length; k++) {
                            htm += startTD + (selCurrRate > 0 ? (result[i].Currency == "USD" ? ((result[i].SOWFin[k].OnshoreNumber) / selCurrRate) : (result[i].SOWFin[k].OnshoreNumber) * selCurrRate) : result[i].SOWFin[k].OnshoreNumber).toFixed(3) + endTD;
                            onNumbers.push(result[i].SOWFin[k].OnshoreNumber);
                        }
                        htm += endTR + startTR + startTD + "Total" + endTD;//Total
                        for (var l = 0; l < onNumbers.length; l++)
                        {
                            htm += startTD + (selCurrRate > 0 ? (result[i].Currency == "USD" ? ((onNumbers[l] + offNumbers[0]) / selCurrRate):((onNumbers[l] + offNumbers[0]) * selCurrRate)) : (onNumbers[l] + offNumbers[0])).toFixed(3) + endTD;
                        }
                        htm += endTable+endTR;
                    }
                    htm += endTR + endTable;
                    $('#serachResults').html(htm);
                },
                error: function (data) {
                    alert(data)
                }
            });
        }
Data Contract
public class SOWAdhoc
    {
        public string ProjectName { get; set; }
        public string Currency { get; set; }
        public string Location { get; set; }
        public string SOWStartDate { get; set; }
        public string SOWEndDate { get; set; }
        public List<SOWFinancial> SOWFin { get; set; }
    }
    public class SOWFinancial
    {
        public int MonthId { get; set; }
        public int YearId { get; set; }
        public double OnshoreNumber { get; set; }
        public double OffshoreNumber { get; set; }
        public string ColumnName { get; set; }
        public string ColumnHeader { get; set; }
    }
WebAPI
[HttpGet]
        [OutputCache(Duration = 0)]
        public ActionResult GetAdhocResult(string filterColumn, string filterData, string sowType)
        {
            List<string> objList = new List<string>();
            string sSQL = "";
            sSQL = "select * from table";
           

            openCon();
            OleDbCommand oc = new OleDbCommand(sSQL, con);
            OleDbDataAdapter oda = new OleDbDataAdapter(oc);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            ListSOWAdhoc> lstSOW = new ListSOWAdhoc>();

            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dRow in ds.Tables[0].Rows)
                {
                        lstSOW.Add(new Models.SOWAdhoc()
                        {
                            ProjectName = dRow["ProjectName"].ToString(),
                            Currency = dRow["Currency"].ToString(),
                            Location = dRow["location"].ToString(),
                            SOWStartDate = Convert.ToDateTime(dRow["StartSOW"]).ToShortDateString(),
                            SOWEndDate = Convert.ToDateTime(dRow["EndSOW"]).ToShortDateString(),
                            SOWFin = GetColumnName(dRow["Id"].ToString())
                        });
                   
                }
            }
           
            return Json(lstSOW, JsonRequestBehavior.AllowGet);
        }
private List<SOWFinancial> GetColumnName(string Id)
        {
            string sSQL = "";
            openCon();
            OleDbCommand oc = new OleDbCommand(sSQL, con);
            OleDbDataAdapter oda = new OleDbDataAdapter(oc);
            DataSet ds = new DataSet();
            oda.Fill(ds);
            List<SOWFinancial> lstFin = new List<SOWFinancial>();
            if (ds.Tables[0].Rows.Count > 0)
            {
                int loop = 0;
                lstFin.Add(new SOWFinancial() { ColumnName = "LocationName", ColumnHeader = "Location Name" });
                for (DateTime dt = Convert.ToDateTime(ds.Tables[0].Rows[0]["sSOW"]); dt <= Convert.ToDateTime(ds.Tables[0].Rows[0]["eSOW"]); dt = dt.AddMonths(1))
                {
                    sSQL = "select * from finData where id=" + Id;
                    openCon();
                    OleDbCommand oChild = new OleDbCommand(sSQL, con);
                    OleDbDataAdapter odaChild = new OleDbDataAdapter(oChild);
                    DataSet dsChild = new DataSet();
                    odaChild.Fill(dsChild);
                    lstFin.Add(new SOWFinancial()
                    {
                        ColumnName = "Column" + loop.ToString(),
                        ColumnHeader = sMonthName[dt.Month - 1] + dt.Year.ToString(),
                        OffshoreNumber = (dsChild.Tables[0].Rows.Count > 0 ? Convert.ToDouble(dsChild.Tables[0].Rows[0]["OffshoreAmount"]) : 0),
                        OnshoreNumber = (dsChild.Tables[0].Rows.Count > 0 ? Convert.ToDouble(dsChild.Tables[0].Rows[0]["OnshoreAmount"]) : 0)
                    });
                    loop += 1;
                }

            }
            return lstFin;
        }
Sample Data
[{“ProjectName”:”test1”,”Currency”:”USD”,”Location”:”ONSHORE”,”SOWStartDate”:”4/1/2019”,”SOWEndDate”:”4/30/2019”,”SOWFin”:[{“MonthId”:”4”,”YearId”:”2019” ,”OnshoreNumber”:”0.000” ,”OffshoreNumber”:”0.000” ,”ColumnName”:”Column1” ,”ColumnHeader”:” Apr2019”}, {“MonthId”:”5”,”YearId”:”2019” ,”OnshoreNumber”:”0.000” ,”OffshoreNumber”:”0.000” ,”ColumnName”:”Column1” ,” ColumnHeader”:”May2019”}]}]
Final Output




Comments

Post a Comment

Popular posts from this blog

SSIS Merge Join - Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS

Image
SSIS Merge Join Issue ( Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS )   I was working on one of my client requirement where I need to split the flat file data based on the conditions and then add join that data with reference data (again a flat file). I used the MergeJoin task to achieve this. Unfortunately I ended up with this error. I tried most of the options available on the internet but none of them worked for me because my mistake was silly I set a property of “Sort” task in one not the other. That’s why MergeJoin was throwing “ Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS ” error.     Lets first discuss about the solution of my problem Root Cause – The main problem was related to the Sort task. I selected the “Comparison Flags” option in one Sort task but not in other task because of that M...
Read more

jsGrid

Image
I was working one a Asp.Net MVC application where I was using the Web Grid to display the data based on the selected search criteria. It was working great but client asked me to use the jsGrid instead of Web Grid as it has many ready to use features like (Edit, Delete, Search, etc..).  Initially, jsGrid gave me very hard time to display the data in it but after few hit and trial I manage to display the data. After spending couple of hours on jsGrid, I started liking it. In this article, I am going to explain how to use the jsGrid to display the data and some other tactics in very easy steps. I am building the application in ASP.NET MVC. Step 1. Content delivery network (CDN) of jsGrid and jQuery libraries are as below https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.min.css https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid.css https://cdnjs.cloudflare.com/ajax/libs/jsgrid/1.5.3/jsgrid-theme.min.css https://code.jquery.com/jquery.min.js ...
Read more

Add Item to SharePoint List with attachment using client object model

Add Item to SharePoint List with attachment using client object model If your requirement is to add the item to a SharePoint list with attachment using the client object model, this post will help you in this. You can achieve this by following the below steps.   Step 1 – Define some constant variables those will hold the values of SharePoint web service, List Id, etc. private const string siteUrl = "http://appsuat.portal.intranet.aegonuk.com/sites/genapps/CashFlowsCashPositions" ;         private const string spWebServiceUrl = "http://appsuat.portal.intranet.aegonuk.com/sites/genapps/CashFlowsCashPositions/_vti_bin/Lists.asmx" ;         private const string updateSOAPAction = "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems" ;         private const string addAttachSOAPAction = "http://schemas.microsoft.com/sharepoint/soap/AddAtta...
Read more