Search code examples
c#sql-servercalendardaypilot

Daypilot - Load appointments from SQL Server

I am creating a master rota using Daypilot.

I am currently storing appointments in the database but I would like to retrieve all appointments for a certain day and display them on a calendar, irrelevant of DATES.

E.G all appointments created on Monday should always be shown on a Monday, irrelevant of what the DATE is.

Current select

public DataTable GetAssignmentsForLocation(DayPilotCalendar calendar)
{
    DataTable dt = new DataTable();

    var da = CreateDataAdapter("select * from [master_rota] where [LocationId] = @location and Week = @Week");

    AddParameterWithValue(da.SelectCommand, "location", (int)calendar.ClientState["location"]);
    AddParameterWithValue(da.SelectCommand, "week", (int)calendar.ClientState["week"]);

    da.Fill(dt);

    return dt;
}

And

 protected void DayPilotCalendar1_Command(object sender, CommandEventArgs e)
{
    switch (e.Command)
    {
        case "navigate":
            var start = (DateTime)e.Data["start"];
            DayPilotCalendar1.StartDate = start;
            DayPilotCalendar1.DataSource = new DataManager_MasterRota().GetAssignmentsForLocation(DayPilotCalendar1);
            DayPilotCalendar1.DataBind();
            DayPilotCalendar1.Update();
            break;
        case "refresh":
            DayPilotCalendar1.DataSource = new DataManager_MasterRota().GetAssignmentsForLocation(DayPilotCalendar1);
            DayPilotCalendar1.DataBind();
            DayPilotCalendar1.Update();
            break;
        case "day":
            DayPilotCalendar1.ViewType = ViewTypeEnum.Day;
            DayPilotCalendar1.StartDate = (DateTime)e.Data["date"];

            DayPilotCalendar1.DataSource = new DataManager_MasterRota().GetAssignmentsForLocation(DayPilotCalendar1);
            DayPilotCalendar1.DataBind();
            DayPilotCalendar1.Update();
            break;
        case "week":
            DayPilotCalendar1.ViewType = ViewTypeEnum.Week;
            DayPilotCalendar1.DataSource = new DataManager_MasterRota().GetAssignmentsForLocation(DayPilotCalendar1);
            DayPilotCalendar1.DataBind();
            DayPilotCalendar1.Update();
            break;

    }
}

Creation

public void CreateAssignment(DateTime start, DateTime end, int location, int week, int person, string note, DayOfWeek day)
{
    using (DbConnection con = CreateConnection())
    {
        con.Open();

        // string id = "";
        var cmd = CreateCommand("insert into [master_rota] ([AssignmentStart], [AssignmentEnd], [LocationId], [PersonId], [AssignmentNote], week, day) values (@start, @end, @location, @person, @note, @Week, @day)", con);
        AddParameterWithValue(cmd, "start", start);
        AddParameterWithValue(cmd, "end", end);
        AddParameterWithValue(cmd, "location", location);
        AddParameterWithValue(cmd, "week", week);
        AddParameterWithValue(cmd, "person", person);
        AddParameterWithValue(cmd, "note", note);
        AddParameterWithValue(cmd, "day", day);

        cmd.ExecuteScalar();
    }
}

Database entries: the database is recording the day for each entry, so in theory it should be possible to load all values for a given day value

E.G All entries for "Day 1", should all show on Monday, irrelevant of DATE

enter image description here

The calendar looks like the below. As you can see there are no dates shown, as it should always load the appointment if the DAY matches

enter image description here

Could someone please assist me in building the select statement so that the appointments load onto the calendar correctly? E.G Day 1 appointments to Monday, Day 2 to Tuesday etc....

Solution

  • If the only data you are displaying is this weekly data (and the only view is the week view) then you can use the following approach:

    When loading appointments, use the "Day" field to calculate a custom start and end dates and override the original values. It would be hard to do that in SQL but you can modify GetAssignmentsForLocation() to post-process the data set easily:

    public DataTable GetAssignmentsForLocation(DayPilotCalendar calendar)
{
    DataTable dt = new DataTable();

    var da = CreateDataAdapter("select * from [master_rota] where [LocationId] = @location and Week = @Week");

    AddParameterWithValue(da.SelectCommand, "@location", (int)calendar.ClientState["location"]);
    AddParameterWithValue(da.SelectCommand, "@week", (int)calendar.ClientState["week"]);

    da.Fill(dt);

    foreach (DataRow dr in dt.Rows) {
       int dayOfWeek = (int) dr["Day"];
       DateTime start = (DateTime) dr["AssignmentStart"];
       DateTime end = (DateTime) dr["AssignmentEnd"];

       TimeSpan duration = end - start;
       TimeSpan startTime = start.TimeOfDay;

       dr["AssignmentStart"] = calendar.VisibleStart.AddDays(dayOfWeek).AddTime(startTime);
       dr["AssignmentEnd"] = calendar.VisibleStart.AddDays(dayOfWeek).AddTime(startTime).AddTime(duration);

    }

    return dt;
}

    Note that this only works for Sunday as the first day of week (i.e. calendar.VisibleStart is always Sunday).