Populate Multiple Gantt Charts from Database in ASP.Net using C#

muhammad...

SOLVED CLOSED ACTIVE ANSWERED
User: muhammad12
Posted: on Sep 22, 2023 01:26 AM
Forum: ASP.Net Web Forms
Answer: 1
Views: 2155

Hi dharmendr sir,

I want to draw gantt charts separately for every department.

I want that when user enter flightno, date and type and click on search button then multiple graph should appear below on the basis of department with their department name as a heading.

Depatment1

GANTT CHART OF Department1

Similarly department2

Gantt chart of department2

And so on.

But now my code shown all department event in one graph and plot its data. I want it should be plot data every department seperately

My database table save datalike this

ActionTable

ActionID(pk,int,notnull) UserId(FK, INT) flightID (FK, INT) starttime (varchar(50) endtime(varchar(50)

33 7 8855 10:33 10:50

34 7 8855 11:33 11:50

Its saving start and endtime of every department.

HTML

namespace WebApplication3
{
    public partial class WebForm6 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        [WebMethod]
        public static List<object> GetChartData(string flightNo, string flightDate, string flightType)
        {
            string t = "Time";
            //string d = flightDate;
            //DateTime dat = DateTime.Parse(flightDate);
            //string date = dat.ToString("d-MMM-yyyy");

            //var d = DateTime.Parse(flightDate);
            //string ndate = d.ToString("d-MMM-yyyy");
            string conString = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            string query = "SELECT  ActionID, eventname, starttime, CASE   WHEN ISNULL(endtime, '') = '' THEN      CONVERT(varchar(5), DATEADD(MINUTE, 1, CONVERT(time, starttime)))     ELSE endtime  END AS endtime FROM events INNER JOIN Assign ON Assign.eventid = events.eventid INNER JOIN Department ON Department.DeptID = Assign.DeptID INNER JOIN Action ON Assign.AssignID = Action.AssignID INNER JOIN flights ON flights.flightid = Action.flightid WHERE flights.flightno = @flightNo   AND flights.flightdate = @flightDate    AND events.eventtype = @flightType    AND events.eventgroup = '" + t+"' ";
            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@flightNo", flightNo);
                    cmd.Parameters.AddWithValue("@flightDate", flightDate);
                    cmd.Parameters.AddWithValue("@flightType", flightType);
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);

                            List<object> chartData = new List<object>();
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                chartData.Add(new object[] {
                        dt.Rows[i]["ActionID"],
                        dt.Rows[i]["eventname"],
                        Convert.ToDateTime(dt.Rows[i]["starttime"].ToString().Replace('.',':')).ToString(),
                        Convert.ToDateTime(dt.Rows[i]["endtime"].ToString().Replace('.', ':')).ToString() });
                            }

                            return chartData;
                        }
                    }
                }
            }
        }
    }
}

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="departmentchart.aspx.cs" Inherits="WebApplication3.departmentchart" %>
 
<!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
        google.charts.load('current', { 'packages': ['gantt'] });
        function drawGanttChart(data, chartDivId) {
            var dataTable = new google.visualization.DataTable();
            dataTable.addColumn('string', 'Task ID');
            dataTable.addColumn('string', 'Task Name');
            dataTable.addColumn('string', 'Resource');
            dataTable.addColumn('date', 'Start Date');
            dataTable.addColumn('date', 'End Date');
            data.addColumn('number', 'Duration');
            data.addColumn('number', 'Percent Complete');
            data.addColumn('string', 'Dependencies');
            for (var i = 0; i < response.d.length; i++) {
                var taskId = "Task_" + response.d[i][0].toString();
                var taskName = response.d[i][1].toString();
                var resource = response.d[i][1].toString();
                var startDate = new Date(response.d[i][2].toString());
                var enddate = new Date(response.d[i][3].toString());
                var duration = parseInt("100");
                var percentageComplete = parseInt("100");
                var dependencies = "";
                data.addRows([[taskId, taskName, resource, startDate, enddate, duration, percentageComplete, dependencies]]);
            }
            var options = {
                height: 300,
                gantt: {
                    trackHeight: 30,
                },
            };
            var chart = new google.visualization.Gantt(document.getElementById(chartDivId));
            chart.draw(dataTable, options);
        }
        $(document).ready(function () {
            $("#btnView").click(function () {
                var flightNo = $("#flightNo").val();
                var flightDate = $("#flightDate").val();
                var flightType = $("#flightType").val();
                $.ajax({
                    type: "POST",
                    url: "departmentchart.aspx/GetChartData",
                    data: JSON.stringify({ flightNo: flightNo, flightDate: flightDate, flightType: flightType }),
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        var departmentChartsContainer = $("#departmentChartsContainer");
                        departmentChartsContainer.empty(); // Clear any existing charts
 
                        for (var departmentName in response.d) {
                            var departmentData = response.d[departmentName];
                            var chartDivId = "chart_div_" + departmentName;
                            var chartDiv = $("<div>").attr("id", chartDivId).addClass("chart");
                            departmentChartsContainer.append(chartDiv);
 
                            drawGanttChart(departmentData, chartDivId);
                        }
 
                        if (Object.keys(response.d).length === 0) {
                            // Handle the case when no department data is available
                            departmentChartsContainer.html("<p>No department data available.</p>");
                        }
                    },
                    error: function (response) {
                        alert(response.responseText);
                    }
                });
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div id="registration-form">
            <div class='fieldset'>
                <legend>Clockify report!</legend>
                <div class="row">
                    <div class="input-group">
                        <asp:Label ID="Label1" runat="server" Text="Flight No" CssClass="label"></asp:Label>
                        <asp:TextBox ID="flightNo" runat="server" CssClass="input"></asp:TextBox>
                    </div>
                    <div class="input-group">
                        <asp:Label ID="Label2" runat="server" Text="Flight No" CssClass="label"></asp:Label>
                        <asp:TextBox ID="flightDate" runat="server" CssClass="input"></asp:TextBox>
                    </div>
                    <div class="input-group">
                        <asp:Label ID="Label3" runat="server" Text="Flight Type" CssClass="label"></asp:Label>
                        <asp:DropDownList ID="flightType" runat="server" CssClass="input" Style="color: Black; font-size: 20px;">
                            <asp:ListItem Text="Arrival" />
                            <asp:ListItem Text="Departure" />
                        </asp:DropDownList>
                    </div>
 
                    <!-- Add other input elements here -->
                    <div class="input-group">
                        <asp:Button ID="btnView" runat="server" Text="Submit" class="btn" />
                    </div>
                </div>
            </div>
        </div>
        <!-- Container for department Gantt charts -->
        <div id="departmentChartsContainer"></div>
    </form>
</body>
</html>

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 3

Reply
ANSWER
User: dharmendr
Replied: on Sep 22, 2023 11:40 AM
Report

muhammad12 says:

var dataTable = new google.visualization.DataTable();
          dataTable.addColumn('string', 'Task ID');
          dataTable.addColumn('string', 'Task Name');
          dataTable.addColumn('string', 'Resource');
          dataTable.addColumn('date', 'Start Date');
          dataTable.addColumn('date', 'End Date');
          data.addColumn('number', 'Duration');
          data.addColumn('number', 'Percent Complete');
          data.addColumn('string', 'Dependencies');

The variable name is dataTable and you are adding some columns with data.

muhammad12 says:

data.addRows([[taskId, taskName, resource, startDate, enddate, duration, percentageComplete, dependencies]]);

Adding the rows in data. That should be dataTable.

muhammad12 says:

for (var i = 0; i < response.d.length; i++) {
              var taskId = "Task_" + response.d[i][0].toString();
              var taskName = response.d[i][1].toString();
              var resource = response.d[i][1].toString();
              var startDate = new Date(response.d[i][2].toString());
              var enddate = new Date(response.d[i][3].toString());
              var duration = parseInt("100");
              var percentageComplete = parseInt("100");
              var dependencies = "";
              data.addRows([[taskId, taskName, resource, startDate, enddate, duration, percentageComplete, dependencies]]);
          }

The values you are retrieving was wrong.

Refer the modified code sample.

Note: I have hard coded data inside the WebMethod for binding the Dictionary.

HTML

<div id="registration-form">
    <div class='fieldset'>
        <legend>Clockify report!</legend>
        <div class="row">
            <div class="input-group">
                <asp:Label ID="Label1" runat="server" Text="Flight No" CssClass="label"></asp:Label>
                <asp:TextBox ID="flightNo" runat="server" CssClass="input"></asp:TextBox>
            </div>
            <div class="input-group">
                <asp:Label ID="Label2" runat="server" Text="Flight No" CssClass="label"></asp:Label>
                <asp:TextBox ID="flightDate" runat="server" CssClass="input"></asp:TextBox>
            </div>
            <div class="input-group">
                <asp:Label ID="Label3" runat="server" Text="Flight Type" CssClass="label"></asp:Label>
                <asp:DropDownList ID="flightType" runat="server" CssClass="input" Style="color: Black; font-size: 20px;">
                    <asp:ListItem Text="Arrival" />
                    <asp:ListItem Text="Departure" />

                </asp:DropDownList>

            </div>

            <!-- Add other input elements here -->
            <div class="input-group">
                <asp:Button ID="btnView" runat="server" Text="Submit" class="btn" />
            </div>
        </div>
    </div>
</div>
<!-- Container for department Gantt charts -->
<div id="departmentChartsContainer" style="width: 500px;"></div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    google.charts.load('current', { 'packages': ['gantt'] });
    function drawGanttChart(data, chartDivId) {
        var dataTable = new google.visualization.DataTable();
        dataTable.addColumn('string', 'Task ID');
        dataTable.addColumn('string', 'Task Name');
        dataTable.addColumn('string', 'Resource');
        dataTable.addColumn('date', 'Start Date');
        dataTable.addColumn('date', 'End Date');
        dataTable.addColumn('number', 'Duration');
        dataTable.addColumn('number', 'Percent Complete');
        dataTable.addColumn('string', 'Dependencies');

        for (var i = 0; i < data.length; i++) {
            var taskId = "Task_" + data[i][0].toString();
            var taskName = data[i][1].toString();
            var resource = data[i][1].toString();
            var startDate = new Date(data[i][2].toString());
            var enddate = new Date(data[i][3].toString());
            var duration = parseInt("100");
            var percentageComplete = parseInt("100");
            var dependencies = "";
            dataTable.addRows([[taskId, taskName, resource, startDate, enddate, duration, percentageComplete, dependencies]]);
        }

        var options = {
            height: 300,
            gantt: { trackHeight: 30 }
        };

        var chart = new google.visualization.Gantt(document.getElementById(chartDivId));
        chart.draw(dataTable, options);
    }

    $(document).ready(function () {
        $("#btnView").click(function () {
            var flightNo = $("#flightNo").val();
            var flightDate = $("#flightDate").val();
            var flightType = $("#flightType").val();

            $.ajax({
                type: "POST",
                url: "Default.aspx/GetChartData",
                data: JSON.stringify({ flightNo: flightNo, flightDate: flightDate, flightType: flightType }),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    var departmentChartsContainer = $("#departmentChartsContainer");
                    departmentChartsContainer.empty(); // Clear any existing charts

                    for (var departmentName in response.d) {
                        var departmentData = response.d[departmentName];
                        var chartDivId = "chart_div_" + departmentName;
                        var chartDiv = $("<div style='width: 500px;height: 200px;'>").attr("id", chartDivId).addClass("chart");
                        departmentChartsContainer.append(chartDiv);

                        drawGanttChart(departmentData, chartDivId);
                    }

                    if (Object.keys(response.d).length === 0) {
                        // Handle the case when no department data is available
                        departmentChartsContainer.html("<p>No department data available.</p>");
                    }
                },
                error: function (response) {
                    alert(response.responseText);
                }
            });

            return false;
        });
    });
</script>

Code

[WebMethod]
public static Dictionary<string, List<object>> GetChartData(string flightNo, string flightDate, string flightType)
{
    string t = "Time";
    Dictionary<string, List<object>> departmentChartData = new Dictionary<string, List<object>>();
    List<string> depts = new List<string>();
    depts.Add("IT");
    depts.Add("CS");
    depts.Add("EE");
    foreach (string dept in depts)
    {
        string departmentName = dept;
        if (!departmentChartData.ContainsKey(departmentName))
        {
            departmentChartData[departmentName] = new List<object>();
        }
    }

    departmentChartData["IT"].Add(new object[] { 1, "Event1", Convert.ToDateTime("10:30").ToString(), Convert.ToDateTime("11:15").ToString() });
    departmentChartData["IT"].Add(new object[] { 2, "Event2", Convert.ToDateTime("09:30").ToString(), Convert.ToDateTime("10:00").ToString() });
    departmentChartData["CS"].Add(new object[] { 3, "Event3", Convert.ToDateTime("08:30").ToString(), Convert.ToDateTime("10:45").ToString() });
    departmentChartData["CS"].Add(new object[] { 4, "Event4", Convert.ToDateTime("11:30").ToString(), Convert.ToDateTime("12:45").ToString() });
    departmentChartData["EE"].Add(new object[] { 5, "Event5", Convert.ToDateTime("08:45").ToString(), Convert.ToDateTime("10:30").ToString() });
    departmentChartData["EE"].Add(new object[] { 6, "Event6", Convert.ToDateTime("09:30").ToString(), Convert.ToDateTime("10:30").ToString() });

    return departmentChartData;
}

Screenshot

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Populate Multiple Gantt Charts from Database in ASP.Net using C#

Company

Explore

Follow us

Contact Us