Search code examples
c#sqlsql-servercalendardaypilot

DayPilot SQL - Copying appointments that dont have a Date

currently I have a master rota which is storing appointments along with the TIME and DAY but not DATE. The SQL Server database looks like the below for the master rota appointments It is created via the DAYPILOT calendar control

enter image description here

enter image description here As you can see there is time stored but not a DATE but it is storing the Day. E.G Day 0 is Sunday, Day 1 is Monday etc....

I would like to add a button to the UI which will copy across the appointments from the master rota on to the LIVE rota taking into account the DATES

E.G an appoinment from the master rota on Day 1 (Monday) should be inserted into the LIVE rota table but adding in the DATE of the current week from the Daypilot calendar control E.G the Assignment start in the LIVE ROTA table would be 2016-01-16 09:30. It would need to check for the current date of the week in the daypilot calendar and then insert into the LIVE Table.

Is this possible? How can I check for the current DATE and ensure that the DATE matches the correct day using the day field from the DB? E.G Day 0, Day 1 etc..?

Current SQL for master rota creation

public void CreateAssignment(DateTime start, DateTime end, int location, int     week, string 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.TimeOfDay);
            AddParameterWithValue(cmd, "end", end.TimeOfDay);
            AddParameterWithValue(cmd, "location", location);
            AddParameterWithValue(cmd, "week", week);
            AddParameterWithValue(cmd, "person", person);
            AddParameterWithValue(cmd, "note", note);
            if (day == DayOfWeek.Saturday)
            {
                AddParameterWithValue(cmd, "day", day - 6);
            }
            else
            AddParameterWithValue(cmd, "day", day + 1);

            cmd.ExecuteScalar();

        }
    }
Solution

  • Here is a SQL only solution for obtaining the start and end date/times for the specified weekday in the current week:

    SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) + day 
       - DATEPART(dw, GETDATE()) + [AssignmentStart] AS [StartInCurrentWeek],
       DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) + day
       - DATEPART(dw, GETDATE()) + [AssignmentEnd] AS [EndInCurrentWeek]
FROM [master_rota];

    Demo here: http://rextester.com/URJP8148

    (Not sure if this is exactly what you want but it's a start, please let me know if it doesn't meet the requirements...)