Hi Dan,
Instead of using "resources" as you didon the default.aspx.I need to replace it with Technicians which is dynamically populated from a SQL view. I am able to bring that in but what happens is the events are not linked. I am working from your Large.aspx so that my users can see two different views, one based on site location and the other based on technicians.
Thanks in advance to your quick reply.
Kathy
Below is the code behind that I used from the Large.aspx.cs:
<code>
public partial class Scheduler_Large : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// prevent invalid ViewState errors in Firefox
if (Request.Browser.Browser == "Firefox") Response.Cache.SetNoStore();
DayPilotScheduler2.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
//DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler1.StartDate.Year, DayPilotScheduler1.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler1.StartDate.AddMonths(1).Year, DayPilotScheduler1.StartDate.AddMonths(1).Month);
DayPilotScheduler2.Days = 360;
DayPilotScheduler2.Separators.Add(DateTime.Now, Color.Red);
if (!IsPostBack)
{
loadResources();
// scroll to this month
DateTime firstOfMonth = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DayPilotScheduler2.SetScrollX(firstOfMonth);
DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
}
}
private void loadResources()
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["_____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT DISTINCT [id], [name] FROM [TechPlanner].[dbo].[PSIVSEmployee] Where [JobTitle] IN ('CEH901', 'CEH902', 'CEH903', 'CEH904', 'CEH905', 'CEH906', 'CHH903', 'CHH904', 'CHH905', 'CMH901', 'CMH903', 'CS1007', 'CS404', 'CS405', 'IV735', 'IV913', 'IV914', 'IV922', 'IV923', 'IV925', 'IV926', 'IV927', 'IVH10', 'IVH20', 'IVH30', 'IVH40', 'IVH50')";
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow r in dt.Rows)
{
string name = (string)r["name"];
decimal id = Convert.ToDecimal(r["id"]);
string ID = Convert.ToString(id);
DayPilotScheduler2.Resources.Add(name, ID);
}
conn.Close();
}
private DataTable dbGetEvents(DateTime start, int days)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT T.id, T.[name], AT.[EventID], AT.eventstart, AT.eventend, E.name FROM PSIVSEmployee T LEFT JOIN AssignedTechs AT ON AT.employeeid = T.id LEFT JOIN EVENT E ON AT.[EventID] = E.[ID] Where T.[JobTitle] IN ('CEH901', 'CEH902', 'CEH903', 'CEH904', 'CEH905', 'CEH906', 'CHH903', 'CHH904', 'CHH905', 'CMH901', 'CMH903', 'CS1007', 'CS404', 'CS405', 'IV735', 'IV913', 'IV914', 'IV922', 'IV923', 'IV925', 'IV926', 'IV927', 'IVH10', 'IVH20', 'IVH30', 'IVH40', 'IVH50') AND AT.[EventID] IS NOT NULL AND NOT ((AT.eventend <= @start) OR (AT.eventstart >= @end))";
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", start.AddDays(days));
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
private void dbUpdateEvent(string id, DateTime start, DateTime end, string resource)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE [event] SET [eventstart] = @start, [eventend] = @end, [resource_id] = @resource WHERE [id] = @id";
cmd.Parameters.AddWithValue("id", id);
cmd.Parameters.AddWithValue("start", start);
cmd.Parameters.AddWithValue("end", end);
cmd.Parameters.AddWithValue("resource", resource);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void DayPilotScheduler2_EventMove(object sender, DayPilot.Web.Ui.Events.EventMoveEventArgs e)
{
string id = e.Value;
DateTime start = e.NewStart;
DateTime end = e.NewEnd;
string resource = e.NewResource;
dbUpdateEvent(id, start, end, resource);
DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
DayPilotScheduler2.Update();
}
protected void DayPilotScheduler2_EventResize(object sender, DayPilot.Web.Ui.Events.EventResizeEventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["___"].ConnectionString);
cmd.Connection = sqlConn;
string id = e.Value;
StringBuilder sb = new StringBuilder();
try
{
sqlConn.Open();
//Update Event Record
sb.Append("Update event ");
sb.Append("Set ");
sb.Append("eventstart = '" + e.NewStart + "', ");
sb.Append("eventend = '" + e.NewEnd + "' ");
sb.Append("Where id = '" + e.Value + "'");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
sb.Length = 0; //reset StringBuilder
}
catch
{
throw;
}
finally
{
if (sqlConn != null)
{
sqlConn.Close();
}
}
DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
DayPilotScheduler2.Update();
}
protected void DayPilotScheduler2_TimeRangeSelected(object sender, DayPilot.Web.Ui.Events.TimeRangeSelectedEventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["_____"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO [event]([eventstart], [eventend]) VALUES (@start, @end)";
cmd.Parameters.AddWithValue("@start", e.Start);
cmd.Parameters.AddWithValue("@end", e.End);
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
protected void DayPilotScheduler2_BeforeEventRender(object sender, DayPilot.Web.Ui.Events.BeforeEventRenderEventArgs e)
{
if (e.Value == "11")
{
e.ContextMenuClientName = "cmSpecial";
e.EventMoveEnabled = false;
e.EventResizeEnabled = false;
e.EventClickEnabled = false;
e.DurationBarColor = "red";
//e.BackgroundColor = "lightyellow";
}
e.BackgroundImage = "../Media/gradient_dps.jpg";
e.BackgroundRepeat = "repeat-x";
}
protected void DayPilotScheduler2_EventMenuClick(object sender, DayPilot.Web.Ui.Events.EventMenuClickEventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["_____"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT [id], [name] FROM [resource]";
switch (e.Command)
{
case "Delete":
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
DataColumn[] keys = new DataColumn[1];
keys[0] = dt.Columns["id"];
dt.PrimaryKey = keys;
DataRow r = dt.Rows.Find(e.Value);
if (r != null)
{
dt.Rows.Remove(r);
dt.AcceptChanges();
}
DayPilotScheduler2.Update();
conn.Close();
break;
}
DayPilotScheduler2.Update();
}
protected void DayPilotScheduler2_BeforeResHeaderRender(object sender, DayPilot.Web.Ui.Events.BeforeHeaderRenderEventArgs e)
{
if (e.IsCorner)
{
if (DayPilotScheduler2.IsExport)
{
e.InnerHTML = DayPilotScheduler2.StartDate.Year.ToString();
}
else
{
e.InnerHTML = String.Format("<div style='padding:5px; font-weight: bold; font-size:22px; text-align:center'>{0}</div>", DayPilotScheduler2.StartDate.Year);
}
}
}
protected void DayPilotBubble1_RenderContent(object sender, RenderEventArgs e)
{
if (e is RenderResourceBubbleEventArgs)
{
RenderResourceBubbleEventArgs re = e as RenderResourceBubbleEventArgs;
e.InnerHTML = "<b>Resource header details</b><br />Value: " + re.ResourceId;
}
else if (e is RenderCellBubbleEventArgs)
{
RenderCellBubbleEventArgs re = e as RenderCellBubbleEventArgs;
e.InnerHTML = "<b>Cell details</b><br />Resource:" + re.ResourceId + "<br />From:" + re.Start + "<br />To: " + re.End;
}
}
protected void DayPilotScheduler2_Command(object sender, DayPilot.Web.Ui.Events.CommandEventArgs e)
{
switch (e.Command)
{
case "next":
DayPilotScheduler2.StartDate = DayPilotScheduler2.StartDate.AddMonths(2);
DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler2.StartDate.Year, DayPilotScheduler2.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler2.StartDate.AddMonths(1).Year, DayPilotScheduler2.StartDate.AddMonths(1).Month);
break;
case "previous":
DayPilotScheduler2.StartDate = DayPilotScheduler2.StartDate.AddMonths(-2);
DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler2.StartDate.Year, DayPilotScheduler2.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler2.StartDate.AddMonths(1).Year, DayPilotScheduler2.StartDate.AddMonths(1).Month);
break;
case "this":
DayPilotScheduler2.StartDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);
DayPilotScheduler2.Days = DateTime.DaysInMonth(DayPilotScheduler2.StartDate.Year, DayPilotScheduler2.StartDate.Month) + DateTime.DaysInMonth(DayPilotScheduler2.StartDate.AddMonths(1).Year, DayPilotScheduler2.StartDate.AddMonths(1).Month);
break;
case "refresh":
case "filter":
loadResources();
string filter = (string)e.Data;
DayPilotScheduler2.DataSource = dbGetEvents(DayPilotScheduler2.StartDate, DayPilotScheduler2.Days);
DayPilotScheduler2.DataBind();
DayPilotScheduler2.Update(CallBackUpdateType.Full);
return; // note that the binding done inside the case section here
default:
throw new Exception("Unknown command.");
}
DataBind();
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}
protected void DayPilotScheduler2_EventClick(object sender, DayPilot.Web.Ui.Events.EventClickEventArgs e)
{
DataBind();
DayPilotScheduler2.Update(String.Format("Event {0} clicked.", e.Value));
}
protected void DayPilotScheduler2_TimeRangeMenuClick(object sender, DayPilot.Web.Ui.Events.TimeRangeMenuClickEventArgs e)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TechPlanner"].ConnectionString);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO [event] ([id], [eventstart], [eventend], [resource_id], [FullName]) VALUES(@start, @end, @id, @column, @technician)";
if (e.Command == "Insert")
{
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
DataRow r = dt.NewRow();
r["start"] = e.Start;
r["end"] = e.End;
r["id"] = Guid.NewGuid().ToString();
r["name"] = "New event";
r["column"] = e.ResourceId;
r["technician"] = e.Data;
dt.Rows.Add(r);
dt.AcceptChanges();
DayPilotScheduler2.Update();
conn.Close();
}
}
protected void DayPilotScheduler2_BeforeCellRender(object sender, DayPilot.Web.Ui.Events.BeforeCellRenderEventArgs e)
{
/*
if (e.Start == new DateTime(2009, 6, 1))
{
e.BackgroundColor = "red";
}*/
}
protected void DayPilotScheduler2_ResourceHeaderMenuClick(object sender, DayPilot.Web.Ui.Events.Scheduler.ResourceHeaderMenuClickEventArgs e)
{
switch (e.Command)
{
case "Insert":
e.Resource.Children.Add(" ", Guid.NewGuid().ToString());
e.Resource.Expanded = true;
break;
case "DeleteChildren":
e.Resource.Children.Clear();
break;
case "Delete":
DayPilotScheduler2.Resources.RemoveFromTree(e.Resource);
break;
}
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}
protected void DayPilotScheduler2_ResourceHeaderClick(object sender, DayPilot.Web.Ui.Events.Scheduler.ResourceHeaderClickEventArgs e)
{
DayPilotScheduler2.Resources.RemoveFromTree(e.Resource);
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}
protected void ButtonExport_Click(object sender, EventArgs e)
{
DataBind();
Response.Clear();
Response.ContentType = "image/png";
Response.AddHeader("content-disposition", "attachment;filename=print.png");
MemoryStream img = DayPilotScheduler2.Export(ImageFormat.Png);
img.WriteTo(Response.OutputStream);
Response.End();
}
protected void DayPilotScheduler1_Refresh(object sender, DayPilot.Web.Ui.Events.RefreshEventArgs e)
{
DayPilotScheduler2.StartDate = e.StartDate;
DayPilotScheduler2.Update(CallBackUpdateType.Full);
}
protected void DayPilotBubble1_RenderEventBubble(object sender, RenderEventBubbleEventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("<b>{0}</b><br />", e.Text);
sb.AppendFormat("Start: {0}<br />", e.Start);
sb.AppendFormat("End: {0}<br />", e.End);
sb.AppendFormat("<br />");
sb.AppendFormat("<b>Double-click the event to enter inline edit mode.</b>");
e.InnerHTML = sb.ToString();
}
}
</code>