Keep Google Timeline (Gantt) chart with Labels from Database using jQuery in ASP.Net

Question

muhammad...

SOLVED CLOSED ACTIVE ANSWERED
User: muhammad12
Posted: on Jun 12, 2024 04:01 AM
Forum: jQuery and jQuery Plugins
Answer: 1
Views: 755

Hi dharmendr sir,

I have a code of time line when i click on submit button then timeline is shown which is perfect.

i want when i click on submit button

Total number of passenger and total number of wheel chair is shown this is hard code label.

I want to show total number of wheel chair from database by using query according to provided input.

SELECT Action.starttime 
 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 
 INNER JOIN Users ON Users.UserID = Action.UserID 
 INNER JOIN Station ON Users.StationID = Station.StationID
 where flightno = 'PA-272' and flightdate = '11-Jun-2024' and eventtype='departure' eventgroup = 'Text' and eventname = 'Total Wheel Chair' ,

This is query that i want to show.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Mergegraph.aspx.cs" Inherits="GANTTCHART.Mergegraph" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
     <title></title>
    <!-- Include Google Font link -->
    <link href="https://fonts.googleapis.com/css2?family=Sofia+Sans+Condensed:wght@400;700&display=swap" rel="stylesheet"/>
    <style>
  
         /* Style to center the input form */
       #registration-form-container {
            display: flex;
            justify-content: center;
            align-items: flex-start; /* Align form to the top */
            overflow: hidden;
            margin-top: 0px; /* Adjust top margin as needed */
        }

       #registration-form {
            display: inline-block; /* Ensure it only takes the necessary width */
            padding: 20px;
            border: 1px solid #ccc;
            background-color: #f2f2f2;
            border-radius: 8px;
            overflow:hidden;
            font-family: 'Sofia Sans Condensed'
                , sans-serif; /* Use Sofia Sans Condensed font */
        }
        
       /* Style to make the "Clockify report!" title more prominent */
       legend {
           font-weight: bold;
           font-size: 20px;
           margin-bottom: 20px;
           font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
       }

       /* Style to place input elements in one row */
       .input-group {
           display: inline-block;
           margin-right: 10px; /* Adjust as needed */
       }

       /* Style to adjust the width of input elements */
       .input {
           width: 150px; /* Adjust as needed */
           padding: 5px;
           border: 1px solid #ccc;
           border-radius: 4px;
           box-sizing: border-box;
           font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
       }

       /* Style for labels */
       .label {
           display: block;
           margin-bottom: 5px;
           color: black;
           font-size: 14px;
           font-weight: bold;
           font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
       }

       /* Style for buttons */
       .btn {
           background-color: #4CAF50;
           color: white;
           padding: 10px 20px;
           border: none;
           font-weight: bold;
           border-radius: 4px;
           cursor: pointer;
           font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
       }

       /* Hover effect for buttons */
       .btn:hover {
           background-color: #45a049;
       }

       /* Style to make sure the timeline adjusts according to device width */
       #departmentChartsContainer {
           white-space: nowrap;
           margin-top: 20px;
           font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
            overflow-x: hidden; /* Hide horizontal scrollbar */
   
            overflow-y: hidden; 
            width: 100%; /* Fix the width of the container */
    display: block;
       }

       .sentence {
           display: flex;
           align-items: center;
           margin-top: 10px;
       }

       .sentence .label {
           margin-right: 5px;
       }
       
       #departmentChartsContainer {
    white-space: nowrap;
    margin: 20px auto; /* Center the container horizontally */
    font-family: 'Sofia Sans Condensed', sans-serif;
    overflow-x: hidden;
    overflow-y: hidden;
    width: 80%; /* Set the width to 80% */
    display: block;
}
       
          /* GridView Styling */
       #GridView1 {
           width: 80%; /* Adjust the width of GridView */
           margin: 20px auto; /* Center the GridView */
           border-collapse: collapse; /* Collapse borders */
           /*margin-top:2000px;*/
       }
    
       
       #GridView1 th, #GridView1 td {
           border: 1px solid #ccc; /* Border for cells */
           padding: 8px; /* Padding for cells */
           text-align: left; /* Left align text */
           font-family: 'Sofia Sans Condensed', sans-serif; /* Use Sofia Sans Condensed font */
       }

       #GridView1 th {
           background-color: #4CAF50; /* Header background color */
           color: white; /* Header text color */
           font-weight: bold; /* Header text weight */
       }

       #GridView1 tr:nth-child(even) {
           background-color: #f2f2f2; /* Even row background color */
       }

       #GridView1 tr:hover {
           background-color: #ddd; /* Hover effect for rows */
       }
        #GridView1 tr:hover {
           background-color: #ddd; /* Hover effect for rows */
       }

        .view-detail {
          position: absolute;
          top: 40px;
          right:450px;
          text-decoration: none;
          color: #4CAF50;
          font-weight: bold;
          font-family: 'Sofia Sans Condensed', sans-serif;          
       }

       .view-detail:hover {
           color: #45a049;
       }
        /* Modal styles */
        .modal {
            display: none;
            position: fixed;
            z-index: 1;
            left: 0;
            top: 0;
            width: 100%;
            height: 100%;
            overflow: auto;
            background-color: rgb(0,0,0);
            background-color: rgba(0,0,0,0.4);
            padding-top: 60px;
        }

        .modal-content {
            background-color: #fefefe;
            margin: 5% auto;
            padding: 20px;
            border: 1px solid #888;
            width: 80%;
        }

        .close {
            color: #aaa;
            float: right;
            font-size: 28px;
            font-weight: bold;
        }

        .close:hover,
        .close:focus {
            color: black;
            text-decoration: none;
            cursor: pointer;
        }
    </style>
    <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>
     google.charts.load("current", { packages: ["timeline"] });

     function parseTimeString(timeStr) {
         var parts = timeStr.split(':');
         var hours = parseInt(parts[0], 10);
         var minutes = parseInt(parts[1], 10);
         return { hours: hours, minutes: minutes };
     }
     
     function drawChart(data, chartDivId) {
         var container = document.getElementById(chartDivId);
         var chart = new google.visualization.Timeline(container);

         var dataTable = new google.visualization.DataTable();
         dataTable.addColumn({ type: 'string', id: 'Department' });
         dataTable.addColumn({ type: 'string', id: 'Event' });
         dataTable.addColumn({ type: 'date', id: 'Start' });
         dataTable.addColumn({ type: 'date', id: 'End' });

         for (var departmentName in data) {
             if (data.hasOwnProperty(departmentName)) {
                 data[departmentName].forEach(function (event) {
                     var startTime = parseTimeString(event[2]);
                     var endTime = parseTimeString(event[3]);
                     var start = new Date(2000, 0, 1, startTime.hours, startTime.minutes);
                     var end = new Date(2000, 0, 1, endTime.hours, endTime.minutes);
                     if (endTime.hours < startTime.hours || (endTime.hours === startTime.hours && endTime.minutes < startTime.minutes)) {
                         end.setDate(end.getDate() + 1); // Add a day if end time is earlier than start time
                     }
                     dataTable.addRows([[departmentName, event[1], start, end]]);
                 });
             }
         }

         var options = {
             timeline: {
                 groupByRowLabel: false,
                 colorByRowLabel: true,
                 rowLabelStyle: { fontName: 'Sofia Sans Condensed', fontSize: 10, color: '#000000', bold: true },
                 barLabelStyle: { fontSize: 14, bold: true, fontName: 'Sofia Sans Condensed', color: '#000000' },
                 avoidOverlappingGridLines: true,
             },
             alternatingRowStyle: false,
             backgroundColor: '#ffd',
             height: 2000
         };

         chart.draw(dataTable, options);
        
     }

     $(document).ready(function () {
         
         $("#btnView").click(function () {
             var flightNo = $("#flightNo").val();
             var flightDate = $("#flightDate").val();
             var flightType = $("#flightType").val();
             drawChart({}, "departmentChartsContainer"); // Draw an empty chart initially
             $.ajax({
                 type: "POST",
                 url: "Mergegraph.aspx/GetChartData",
                 data: JSON.stringify({ flightNo: flightNo, flightDate: flightDate, flightType: flightType }),
                 contentType: "application/json; charset=utf-8",
                 dataType: "json",
                 success: function (response) {
                     drawChart(response.d, "departmentChartsContainer");
                    
                     $('body').css('overflow', 'hidden'); // Hide scroll bars
                     // Show the sentence
                     $(".sentence").show();
                 },
                 complete: function () {
                     $('body').css('overflow', 'auto'); // Revert back to original state
                 },
                 error: function (response) {
                     alert(response.responseText);
                 }
             });
      
             return false;
         });

         // Modal handling
         $(".view-detail").click(function () {
             $("#myModal").css("display", "block");
         });

         $(".close").click(function () {
             $("#myModal").css("display", "none");
              
         });

         $(window).click(function (event) {
             if (event.target.id == "myModal") {
                 $("#myModal").css("display", "none");
                  
             }
         });
     });
 </script>
</head>
<body>
    <form id="form1" runat="server">
        <div id="registration-form-container">
            <div id="registration-form">
               
                <legend>Clockify report!</legend>
               
                 <asp:LinkButton ID="lnkSearch" runat="server" class="view-detail" OnClick="lnkSearch_Click">View Detail</asp:LinkButton>
                <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 Date " CssClass="label"></asp:Label>
                        <asp:TextBox ID="flightDate" runat="server" CssClass="input" type="Date" ></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 class="row">&nbsp;</div>
                <div class="row sentence" style="display: none;" runat="server" id="dvLabels">
                 
        <asp:Label ID="Label4" runat="server" Text="Total Number of PAX: " CssClass="label" Style="color: red;"></asp:Label>
        <asp:Label ID="lblpax" runat="server"  CssClass="label" Style="font-weight: bold;"></asp:Label>
        <asp:Label ID="Label5" runat="server" Text=" Total Number of Wheel Chairs:" CssClass="label" Style="color: red;"></asp:Label>
        <asp:Label ID="lblwheel" runat="server" Text="12:00" CssClass="label" Style="font-weight: bold;"></asp:Label>
    </div>  
            </div>
        </div>
        <!-- Container for department Gantt charts -->
        <div id="departmentChartsContainer" ></div>
       <div id="myModal" class="modal" style="display:none;" runat="server">
            <div class="modal-content">
                <span class="close">&times;</span>
                <!-- GridView content will be placed here -->
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" />
            </div>
        </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;

namespace GANTTCHART
{
    public partial class Mergegraph : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //BindGridView();
        }
        [WebMethod]
        public static Dictionary<string, List<object[]>> GetChartData(string flightNo, string flightDate, string flightType)
        {

            string conString = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            string query = "SELECT Department.DeptName, 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 CONVERT(date, flights.flightdate) = @flightDate AND events.eventtype = @flightType  and events.eventgroup = 'Time' ";

            using (SqlConnection con = new SqlConnection(conString))
            {
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@flightNo", flightNo);
                    //string flightDateStr = flightDate;
                    DateTime selectedDate = DateTime.Parse(flightDate);
                    cmd.Parameters.AddWithValue("@flightDate", selectedDate);
                    cmd.Parameters.AddWithValue("@flightType", flightType);

                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    Dictionary<string, List<object[]>> departmentChartData = new Dictionary<string, List<object[]>>();

                    while (reader.Read())
                    {
                        string departmentName = reader["DeptName"].ToString();
                        if (!departmentChartData.ContainsKey(departmentName))
                        {
                            departmentChartData[departmentName] = new List<object[]>();
                        }

                        departmentChartData[departmentName].Add(new object[]
                        {
                     reader["eventname"],
                     reader["eventname"] ,
                      reader["starttime"].ToString(),
                       reader["endtime"].ToString()
                        });
                    }

                    return departmentChartData;
                }
            }
        }
        protected void lnkSearch_Click(object sender, EventArgs e)
        {
            // Call BindGridView function to bind GridView with filtered data
            BindGridView();
            ClientScript.RegisterStartupScript(this.GetType(), "Popup", "<script>openModal();</script>");
        }
        String cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
        protected void BindGridView()
        {
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SELECT events.eventname AS 'Task', Action.starttime AS 'Value', Users.Name AS 'Written By', Users.PA# AS 'PA-num', Department.DeptName AS 'Department', Station.StationName AS 'Station' 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 INNER JOIN Users ON Users.UserID = Action.UserID INNER JOIN Station ON Users.StationID = Station.StationID WHERE flights.flightno = '"+flightNo.Text+"' AND CONVERT(date, flights.flightdate) = '"+flightDate.Text+"' AND events.eventgroup = 'Text' AND events.eventtype = '"+flightType.Text+ "' ORDER BY Department ASC ", con);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.HasRows == true)
                {
                    GridView1.DataSource = dr;
                    GridView1.DataBind();
                    con.Close();
                }
                }

            myModal.Style["display"] = "block";
            dvLabels.Attributes.Remove("style");          
        }
    }
}

Keep Google Timeline (Gantt) chart with Labels from Database using jQuery in ASP.Net

Company

Explore

Follow us

Contact Us