Hi dharmendr sir,
i have a code that adjust google gantt chart x axis to 24 hours .
like it make x axis 1AM, 2 , 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 PM ,1, 2 , 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 AM
this code make gantt chart of different department when i enter flight number date and type and make graph fatching data from database.
my problem is now it make the x axis of 24 hours of every department .
but does not plotting or making bars according to database start and end time. it plot wrong bars.
i means if starttime is 11 am and end time is 11 30 it make wrong time bar like 2 am to 2 30.
all events bars displying wrong time.
please help me sir
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style>
.container { width: 80%; margin: 0 auto; }
.chart-title { font-weight: bold; color: black; font-size: 50px; text-align: left; }
.chart { min-height: 300px; width: 100%; margin-bottom: 80px; }
text { font-size: 24px !important; /*font-style: bold !important;*/ }
.chart-container { min-height: 400px; width: 100%; /* Set the chart container to 100% width */ margin-bottom: 80px; }
/* Constrain the width of the Gantt chart within the container */
.chart-container .chart { width: 100%; }
</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 type="text/javascript">
google.charts.load('current', { 'packages': ['gantt'] });
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');
var baseDate = new Date('2024-01-01'); // Use a common date for all time values
// Add rows for min and max time
dataTable.addRows([
['Min Time', '', '', new Date(2024, 0, 1, 0, 0), new Date(2024, 0, 1, 0, 0), 0, 0, ''],
['Max Time', '', '', new Date(2024, 0, 2, 0, 0), new Date(2024, 0, 2, 0, 0), 0, 0, '']
]);
// Find the minimum and maximum time values in the data
var minTime = new Date(baseDate);
var maxTime = new Date(baseDate);
for (var i = 0; i < data.length; i++) {
var startTimeParts = data[i][2].split(':');
var endTimeParts = data[i][3].split(':');
var startTime = new Date(baseDate);
startTime.setHours(parseInt(startTimeParts[0], 10));
startTime.setMinutes(parseInt(startTimeParts[1], 10));
var endTime = new Date(baseDate);
endTime.setHours(parseInt(endTimeParts[0], 10));
endTime.setMinutes(parseInt(endTimeParts[1], 10));
minTime = minTime > startTime ? startTime : minTime;
maxTime = maxTime < endTime ? endTime : maxTime;
}
// Set the minimum and maximum values for the horizontal axis
var options = {
height: 300,
width: '100%',
gantt: {
trackHeight: 30
},
hAxis: {
title: 'Time of Day',
format: 'HH:mm',
ticks: generateTicks(minTime, maxTime, 60 * 60 * 1000), // Generate ticks for every hour
},
vAxis: {
title: 'Resource'
}
};
// Add your existing rows
for (var i = 0; i < data.length; i++) {
try {
var taskId = "Task_" + data[i][0].toString();
var taskName = data[i][1].toString();
var resource = data[i][1].toString();
// Parse time values and append them to the base date
var startTimeParts = data[i][2].split(':');
var endTimeParts = data[i][3].split(':');
var startDate = new Date(baseDate);
startDate.setHours(parseInt(startTimeParts[0], 10));
startDate.setMinutes(parseInt(startTimeParts[1], 10));
var endDate = new Date(baseDate);
endDate.setHours(parseInt(endTimeParts[0], 10));
endDate.setMinutes(parseInt(endTimeParts[1], 10));
var duration = (endDate - startDate) / (1000 * 60 * 60 * 24); // Calculate duration in days
var percentageComplete = parseInt("100");
var dependencies = "";
dataTable.addRows([[taskId, taskName, resource, startDate, endDate, duration, percentageComplete, dependencies]]);
} catch (error) {
console.error("Error processing data:", error);
console.error("Problematic data:", data[i]);
}
}
var chart = new google.visualization.Gantt(document.getElementById(chartDivId));
chart.draw(dataTable, options);
}
function generateTicks(minTime, maxTime, interval) {
var ticks = [];
var current = new Date(minTime);
while (current <= maxTime) {
ticks.push(new Date(current)); // Copy the date object to avoid reference issues
current.setTime(current.getTime() + interval);
}
return ticks;
}
$(document).ready(function () {
$("#btnView").click(function () {
var flightNo = $("#flightNo").val();
var flightDate = $("#flightDate").val();
var flightType = $("#flightType").val();
$.ajax({
type: "POST",
url: "WebForm2.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];
// Create a title element for the department name with styling
var titleElement = $("<div class='chart-title'>").text(departmentName);
departmentChartsContainer.append(titleElement);
var chartDivId = "chart_div_" + departmentName;
var chartDiv = $("<div style='width: 100%;'>").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;
});
});
function generateTicks(minTime, maxTime, interval, baseDate) {
var ticks = [];
var current = new Date(minTime);
while (current <= maxTime) {
ticks.push(new Date(current)); // Copy the date object to avoid reference issues
current.setTime(current.getTime() + interval);
}
return ticks;
}
$(document).ready(function () {
$("#btnView").click(function () {
var flightNo = $("#flightNo").val();
var flightDate = $("#flightDate").val();
var flightType = $("#flightType").val();
$.ajax({
type: "POST",
url: "WebForm2.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];
// Create a title element for the department name with styling
var titleElement = $("<div class='chart-title'>").text(departmentName);
departmentChartsContainer.append(titleElement);
var chartDivId = "chart_div_" + departmentName;
var chartDiv = $("<div style='width: 100%;'>").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>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<div id="registration-form">
<div class='fieldset'>
<h1>Flight Operations Report</h1>
<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" font-color="black" TextMode="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>
<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="margin-top: 100px;"></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;
namespace WebApplication1
{
public partial class WebForm2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[WebMethod]
public static Dictionary<string, List<object>> GetChartData(string flightNo, string flightDate, string flightType)
{
string t = "Time";
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 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);
string flightDateStr = flightDate;
DateTime selectedDate = DateTime.Parse(flightDateStr);
//string formattedDate = selectedDate.ToString("dd-MMM-yyyy");
cmd.Parameters.AddWithValue("@flightDate", selectedDate);
cmd.Parameters.AddWithValue("@flightType", flightType);
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
Dictionary<string, List<object>> departmentChartData = new Dictionary<string, List<object>>();
foreach (DataRow row in dt.Rows)
{
string departmentName = row["DeptName"].ToString();
if (!departmentChartData.ContainsKey(departmentName))
{
departmentChartData[departmentName] = new List<object>();
}
departmentChartData[departmentName].Add(new object[] {
row["ActionID"],
row["eventname"],
Convert.ToDateTime(row["starttime"].ToString().Replace('.', ':')).ToString(),
Convert.ToDateTime(row["endtime"].ToString().Replace('.', ':')).ToString()
});
}
return departmentChartData;
}
}
}
}
}
}
}
