using DayPilot.Web.Ui; using DayPilot.Web.Ui.Enums; using DayPilot.Web.Ui.Events.Scheduler; using DayPilot.Utils; using DayPilot.Web.Ui.Events; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; using System.Configuration; public partial class Telemarketing_HRM_GantChart : System.Web.UI.Page { private DataTable tasks; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { // DayPilotScheduler1.TimeHeaders.Clear(); //DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Month)); //DayPilotScheduler1.TimeHeaders.Add(new TimeHeader(GroupByEnum.Week)); //loadResources(); // UpdateScheduler(); BindGantchart(); // DayPilotScheduler1.SetScrollX(new DataManager().GetStart() ?? DateTime.Today); } } private void BindGantchart() { SqlDataAdapter da1 = new SqlDataAdapter("select min(recieved_date)as start from project_information where status is null and batchtype='Live'", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); DataTable dt1 = new DataTable(); da1.Fill(dt1); SqlDataAdapter da2 = new SqlDataAdapter("select max(deadline)as end1 from project_information where status is null and batchtype='Live'", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); DataTable dt2 = new DataTable(); da2.Fill(dt2); var start = (Convert.ToDateTime(dt1.Rows[0]["start"]).AddDays(-5)); var end = (Convert.ToDateTime(dt2.Rows[0]["end1"]).AddDays(10)); // SqlDataAdapter da = new SqlDataAdapter("select * from project_information where status is null and batchtype='Live'", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); // SqlDataAdapter da = new SqlDataAdapter("select pid, clientid,batchcode,batchname,batchtype,numberfiles,recieved_date,getdate() as deadline,deliverydate ,projecttype,mode,estimate,currency_type,status from project_information where status is null and batchtype='Live' and deliverydategetdate()", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); DataTable dt = new DataTable(); SqlDataAdapter sda1 = new SqlDataAdapter("select distinct clientid from project_information P where status is null and batchtype='Live'and P.deliverydategetdate()",ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); DataSet ds1 = new DataSet(); sda1.Fill(ds1); if (ds1.Tables[0].Rows.Count > 0) { dt.Columns.Add("pid"); dt.Columns.Add("batchname"); dt.Columns.Add("recieved_date", typeof(DateTime)); dt.Columns.Add("deadline", typeof(DateTime)); dt.Columns.Add("deliverydate", typeof(DateTime)); foreach (DataRow dr in ds1.Tables[0].Rows) { #region ADD_MainProject_To_Table SqlDataAdapter sda_main = new SqlDataAdapter("select projectname as batchname,client_id as pid,'" + start.ToShortDateString() + "' as recieved_date,'"+end.ToShortDateString()+"' as deadline,'"+end.ToShortDateString()+"' as deliverydate from client_details where client_id=" + (int)dr["clientid"] + "", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); DataSet ds2 = new DataSet(); sda_main.Fill(ds2); // dt.Rows.Add(ds2.Tables[0].Rows[0]); dt.ImportRow(ds2.Tables[0].Rows[0]); #endregion #region ADD_Batches_To_Table SqlDataAdapter sda_batch = new SqlDataAdapter(@"select P.pid,(select isnull(subproject,' ') from client_details where client_id=P.clientid) + batchname as batchname,recieved_date,dateadd(dd,[dbo].[GetAverageBatchEndDate](P.pid),getdate()) as deadline, P.deliverydate from project_information P where status is null and batchtype='Live' and clientid="+(int)dr["clientid"]+" and P.deliverydategetdate()", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); DataSet ds3 = new DataSet(); sda_batch.Fill(ds3); for (int i = 0; i < ds3.Tables[0].Rows.Count; i++) { dt.ImportRow(ds3.Tables[0].Rows[i]); } #endregion } } SqlDataAdapter da = new SqlDataAdapter(@"select P.pid, P.clientid,batchcode,(select isnull(projectname,' \ ')+' '+isnull(subproject,' ') as Mainproject from client_details where client_id=P.clientid) +' \ '+ batchname as batchname,batchtype,numberfiles,recieved_date,dateadd(dd,[dbo].[GetAverageBatchEndDate](P.pid),getdate()) as deadline, P.deliverydate ,projecttype,mode,estimate,currency_type,status from project_information P where status is null and batchtype='Live' and P.deliverydategetdate()", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); //DataRow newRow = dt.Rows.Add(); //newRow.SetField("pid", "848"); //newRow.SetField("batchname", "Gaurav Bari"); //newRow.SetField("recieved_date", start); //newRow.SetField("deliverydate", end); //newRow.SetField("deadline", end); //DataRow newRow1 = dt.Rows.Add(); //newRow1.SetField("pid", "858"); //newRow1.SetField("batchname", "Vaibhavi Bari"); //newRow1.SetField("recieved_date", "2015-10-22 00:00:00.000"); //newRow1.SetField("deliverydate", "2016-01-15 17:50:41.540"); //newRow1.SetField("deadline", "2016-01-14 17:50:41.540"); //da.Fill(dt); DayPilotScheduler1.StartDate = start; DayPilotScheduler1.Days = (int)Math.Ceiling((end - start).TotalDays); DayPilotScheduler1.DataResourceField = "pid"; DayPilotScheduler1.DataTextField = "batchname"; DayPilotScheduler1.DataSource = dt; DayPilotScheduler1.DataBind(); // DayPilotScheduler1.Update(); } private void LoadResources() { DataTable locations = new DataManager().GetTasks(); DayPilotScheduler1.Resources.Clear(); DayPilotScheduler1.Resources.Add("New Task", "NEW"); foreach (DataRow dr in locations.Rows) { DayPilotScheduler1.Resources.Add((string)dr["TaskName"], Convert.ToString(dr["TaskId"])); } } private void loadResources() { DayPilotScheduler1.Resources.Clear(); foreach (DataRow dr in loadRootResources().Rows) { int id = (int)dr["client_id"]; string name = (string)dr["projectname"]; Resource r = new Resource(name, id.ToString()); // r.Expanded = true; // DayPilotScheduler1.Resources.Add(r); DayPilotScheduler1.Resources.Add(r); addChildren(r); } } private void addChildren(Resource parent) { foreach (DataRow dr in loadChildResources(parent.Value).Rows) { int id = (int)dr["pid"]; string name = (string)dr["batchname"]; Resource r = new Resource(name, id.ToString()); DayPilotScheduler1.Resources.Add(r); } } private DataTable loadRootResources() { //SELECT * FROM [resource] WHERE [parent_id] is null // SqlDataAdapter da = new SqlDataAdapter("select client_id,projectname from client_details where client_id in (select clientid from project_information where status is null and batchtype='Live' )", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); SqlDataAdapter da = new SqlDataAdapter("select client_id,projectname from client_details where subproject is null and client_id in (select clientid from project_information where status is null and batchtype='Live') union select client_id,projectname from client_details where subproject is null and projectname in( select projectname from client_details where subproject is not null and client_id in(select clientid from project_information where status is null and batchtype='Live'))", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); DataTable dt = new DataTable(); da.Fill(dt); return dt; } private DataTable loadChildResources(string parentId) { // SqlDataAdapter da = new SqlDataAdapter("select * from project_information where status is null and batchtype='Live' and clientid= @parent or clientid in (select client_id from client_details where projectname=(select projectname from client_details where client_id=@parent and subproject is null ) and subproject is not null)", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); SqlDataAdapter da = new SqlDataAdapter(@"select pid,isnull((select subproject+' \ ' from client_details where client_id=P.clientid),' ')+batchname as batchname from project_information P where status is null and batchtype='Live' and clientid= @parent or clientid in (select client_id from client_details where projectname=(select projectname from client_details where client_id=@parent and subproject is null ) and subproject is not null)", ConfigurationManager.ConnectionStrings["E_Con"].ConnectionString); da.SelectCommand.Parameters.AddWithValue("parent", parentId); DataTable dt = new DataTable(); da.Fill(dt); return dt; } private void UpdateScheduler() { tasks = new DataManager().GetTasks(); var start = (new DataManager().GetStart() ?? DateTime.Today).Date; start = new DateTime(start.Year, start.Month, 1).AddMonths(-1); var end = new DataManager().GetEnd() ?? DateTime.Today.AddDays(1); end = new DateTime(end.Year, end.Month, 1).AddMonths(2); DayPilotScheduler1.StartDate = start; DayPilotScheduler1.Days = (int)Math.Ceiling((end - start).TotalDays); DayPilotScheduler1.DataSource = new DataManager().GetTasks(); DayPilotScheduler1.DataBind(); // DayPilotScheduler1.Update(); } //protected void DayPilotScheduler1_BeforeCellRender(object sender, BeforeCellRenderEventArgs e) //{ // if (e.ResourceId == "NEW") // { // if (e.IsBusiness) // { // e.BackgroundColor = "#ffffff"; // } // else // { // e.BackgroundColor = "#ffffe7"; // } // } // else // { // if (e.IsBusiness) // { // e.BackgroundColor = "#f8f8f8"; // } // else // { // e.BackgroundColor = "#f8f8e7"; // } // } //} protected void DayPilotScheduler1_BeforeEventRender(object sender, BeforeEventRenderEventArgs e) { if (e.End < DateTime.Now) { e.BackgroundColor = "#66FF0000"; e.DurationBarColor = "#66FF0000"; e.DurationBarColor = "thistle"; e.ToolTip = "Deadline Date: " + e.DataItem["deliverydate"].ToString(); } else { if (Convert.ToDateTime(e.DataItem["deliverydate"]) < DateTime.Now) { e.BackgroundColor = "#66FF0000"; e.DurationBarColor = "#66FF0000"; e.ToolTip = "Average Delivery Date: " + e.DataItem["deadline"].ToString(); } else { e.BackgroundColor = "#66FF0000"; e.DurationBarColor = "#66FF0000"; e.ToolTip = "Delivery Date: " + e.DataItem["deliverydate"].ToString(); } } } protected void DayPilotScheduler1_DataBinding(object sender, EventArgs e) { } }