c# - 比较 DateTime 结构以查找空闲槽

Question

我想搜索列表中所有用户的事件，并检索每个用户在早上 7 点到晚上 7 点之间有 30 分钟或更长时间空闲的所有时间。

但是有一个问题，如果方法被标记为“重复”，即重复位设置为 1，则该事件在开始后的 52 周内重复发生(因此时间不可用)。这些事件的检索在存储过程中进行处理。

到目前为止，我的代码如下。我打算以正确的方式编写此程序吗？我不太确定如何继续让函数返回我想要的。有人能帮我解决这个问题吗？

List<string> usernames = //List of usernames.
DateTime start = //DateTime for start of period you would like to schedule meeting
DateTime end = //DateTime for end of period
//int mins = //duration of meeting (must be 30mins or greater)

foreach (string username in usernames) {
   //retrieve events for this user
    var db = Database.Open("mPlan");
    List<DateTime> startTimes;
    List<DateTime  endTimes;
    // This stored procedure returns all events of a user in a given time period, 
    // including recurring events.
    var record = db.Query("EXEC dbo.GetEvents @0, @1, @2", username, start, end);
    foreach(var record in result) {
          startTimes.Add(record.event_start);
          endTimes.Add(record.event_end);
    }
    // so now I have a list of all start times and end times of events
    // for one user and could save all this data in a list
  }

表结构:

DECLARE @Users TABLE
(    
    UserID   INT IDENTITY(1,1),
    Username VARCHAR(32)
);

DECLARE @Groups TABLE
(
    GroupID   INT IDENTITY(1,1),
    GroupName VARCHAR(32)
);

DECLARE @Membership TABLE
(
    UserID  INT,
    GroupID INT
);

DECLARE @event TABLE
(
    event_id    INT IDENTITY(1,1),
    event_start DATETIME,
    event_end   DATETIME,
    group_id    INT,
    recurring   BIT
);

我想要的功能示例:

用户将数据库中的多个用户添加到列表中。用户选择他希望与所有这些用户会面的时间段。我的算法计算所有用户免费的所有时间段(即适合所有用户开会的时间 >30 分钟)。

附加信息:

示例案例:

• 用户 A 尝试组织与用户 B 的 session 。所有时间段都是自由。我希望算法返回 DateTime 开始和DateTime 结束所有可能的开始时间和结束时间的组合>30 分钟且 == 持续时间(参数)的时间。

• 典型案例:用户 A 计划了除下午 6 点以外的所有时间的事件 -晚上 7 点。他尝试组织与用户 B 的 session ，持续时间为1小时。用户 B 没有组织任何事件 - DateTime 6PM 和DateTime 7pm 返回表示开始和结束时间 session 。

• 重复案例:用户 A 在周一下午 5 点到 6 点有一个重复事件。他试图在六周内的星期一组织一次 2 小时的 session 。全部返回相差 2 小时的 DateTime start 和 DateTime end 的组合。下午5点到7点的时间是未返回，因为此事件重复发生每周一次，持续 52 周。

下面是检索设定时间段(开始、结束)内所有用户事件的存储过程:

ALTER PROCEDURE dbo.GetEvents 
  @UserName VARCHAR(50), 
  @StartDate DATETIME, 
  @EndDate DATETIME 
AS 

BEGIN 
-- DEFINE A CTE TO GET ALL GROUPS ASSOCIATED WITH THE CURRENT USER 
;WITH Groups AS  
(   SELECT  GroupID  
    FROM    Membership  m 
            INNER JOIN Users u 
                ON m.UserID = u.UserID 
    WHERE   Username = @UserName 
    GROUP BY GroupID 
), 
-- DEFINE A CTE TO GET ALL EVENTS FOR THE GROUPS DEFINED ABOVE 
AllEvents AS 
(   SELECT  e.* 
    FROM    event e 
            INNER JOIN Groups m  
                ON m.GroupID = e.group_id 
    UNION ALL 
    SELECT  e.event_id, e.title, e.description, 
      DATEADD(WEEK, w.weeks, e.event_start), 
      DATEADD(WEEK, w.weeks, e.event_end), 
      e.group_id, e.recurring 
    FROM    event e 
            INNER JOIN Groups m  
                ON m.GroupID = e.group_id 
            CROSS JOIN  
            (   SELECT  ROW_NUMBER() OVER (ORDER BY Object_ID) AS weeks 
                FROM    SYS.OBJECTS 
            ) AS w 
    WHERE  e.recurring = 1 
)    
-- GET ALL EVENTS WHERE THE EVENTS FALL IN THE PERIOD DEFINED 
SELECT  * 
FROM    AllEvents 
WHERE   Event_Start >= @StartDate 
AND     Event_End <= @EndDate 

END 

Answer 1

想象一下一些表:

USE tempdb;
GO

CREATE TABLE dbo.Users
(    
    UserID   INT IDENTITY(1,1),
    Username VARCHAR(32)
);

CREATE TABLE dbo.Groups
(
    GroupID   INT IDENTITY(1,1),
    GroupName VARCHAR(32)
);

CREATE TABLE dbo.Membership
(
    UserID  INT,
    GroupID INT
);

CREATE TABLE dbo.[event]
(
    event_id    INT IDENTITY(1,1),
    event_start DATETIME,
    event_end   DATETIME,
    group_id    INT,
    recurring   BIT
);

想象一下，提供一些示例数据并不难:

INSERT dbo.Users(Username) 
    SELECT 'User A' UNION ALL SELECT 'User B';

INSERT dbo.Groups(GroupName) 
    SELECT 'Group 1' UNION ALL SELECT 'Group 2';

INSERT dbo.Membership(UserID, GroupID)
    SELECT 1,1 UNION ALL SELECT 2,2;


INSERT dbo.[event](event_start, event_end, group_id, recurring)
-- user A, almost all day meeting on a specific date
SELECT '20120313 07:00', '20120313 18:00', 1, 0 

-- user A, recurring meeting every Monday
UNION ALL SELECT '20120312 17:00', '20120312 18:00', 1, 1 

-- user A, recurring meeting every Tuesday (future)
UNION ALL SELECT '20120327 14:00', '20120327 15:00', 1, 1; 
GO

现在我们可以构建这个存储过程:

CREATE PROCEDURE dbo.GetPossibleMeetingTimes
    @AskingUserID INT,
    @TargetUserID INT,
    @Duration     INT,           -- in minutes!
    @StartDate    SMALLDATETIME, -- assumes date, no time!
    @EndDate      SMALLDATETIME  -- again - date, no time!
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH dRange(d) AS
    (
        -- get the actual dates in the requested range
        -- limited to number of rows in sys.objects

        SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)+1) 
            DATEADD(DAY, n-1, @StartDate)
         FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.objects) AS x
    ), possible(ds, de) AS
    (
        -- get all the timeslots of @Duration minutes 
        -- between 7:00 AM and 7:00 PM for each day in 
        -- the range - these are all *potential* slots

        SELECT DATEADD(MINUTE, 30*rn, DATEADD(HOUR, 7, dRange.d)),
            DATEADD(MINUTE, 30*rn + @Duration, DATEADD(HOUR, 7, dRange.d))
        FROM (SELECT TOP (720/30) rn = ROW_NUMBER() OVER
        (ORDER BY [object_id])-1 FROM sys.objects) AS x
        CROSS JOIN dRange
    )
    SELECT p.ds, p.de FROM possible AS p 
    WHERE p.de <= DATEADD(HOUR, 19, DATEADD(DAY, DATEDIFF(DAY, 0, p.de), 0)) 
    AND NOT EXISTS 
    (
        SELECT 1 FROM 
        (
            -- filter down to users with events on the days in the range

            SELECT group_id, event_start, event_end
                FROM dbo.[event] 
                WHERE event_start >= @StartDate 
                AND event_start < DATEADD(DAY, 1, @EndDate)
            UNION ALL 

            -- also include users with recurring events on same weekday(s)
            -- normalized to the matching day in the range

            SELECT group_id, 
              event_start = DATEADD(DAY, DATEDIFF(DAY, event_start, p.ds), event_start),
              event_end   = DATEADD(DAY, DATEDIFF(DAY, event_end,   p.ds), event_end)
            FROM dbo.[event]
            WHERE recurring = 1 
            AND event_start <= DATEADD(DAY, 1, @EndDate) -- ignore future events
                    AND event_start >= DATEADD(WEEK, -52, @EndDate) -- 52 weeks out 
            AND DATEDIFF(DAY, event_start, p.ds) % 7 = 0 -- same weekday
        ) AS sub
        WHERE sub.group_id IN 
        (
            -- this checks that events are within previously scheduled times

            SELECT GroupID FROM dbo.Membership
              WHERE UserID IN (@AskingUserID, @TargetUserID)
              AND (p.de > sub.event_start AND p.ds < sub.event_end)
        )
    )
    ORDER BY p.ds, p.de;
END
GO

调用示例:

-- Case 1: User A tries to meet with User B on a day where 
-- both schedules are clear.

EXEC dbo.GetPossibleMeetingTimes
    @AskingUserID = 1,
    @TargetUserID = 2,
    @Duration     = 30,
    @StartDate    = '20120314', -- no events for either user
    @EndDate      = '20120314';

结果:

-- Case 2: User A tries to meet with User B for an hour, on 
-- a day where user A has meetings from 7 AM to 6 PM.

EXEC dbo.GetPossibleMeetingTimes
    @AskingUserID = 1,
    @TargetUserID = 2,
    @Duration     = 60,
    @StartDate    = '20120313', -- user A has an almost all-day event
    @EndDate      = '20120313';

结果:

-- Case 3: User A tries to meet with User B for two hours, on 
-- a weekday where User A has a recurring meeting from 5-6 PM

EXEC dbo.GetPossibleMeetingTimes
    @AskingUserID = 1,
    @TargetUserID = 2,
    @Duration     = 120,        
    @StartDate    = '20120319', -- user A has a recurring meeting
    @EndDate      = '20120319';

结果:

现在请注意，我考虑了您未考虑或未提及的几个因素(例如在未来开始的重复事件)。另一方面，我也没有处理其他一些因素(例如夏令时，如果它可能会影响这一点)并且没有测试所有可能的场景(例如已经存在的同一天的多个事件)。

我确实测试过，如果你在一个范围内传递(例如 2012-03-12 -> 2012-03-14)，你基本上只会得到上述结果的并集，并且可用的时间段大致相同(这些时间段各不相同)根据类(class)持续时间)。重要的部分是停电时隙得到尊重。我没有测试重复事件在未来开始的情况的逻辑，并且提供的日期范围包括事件第一次实例之前和之后的那个工作日。

如果有任何案例对您不起作用，那么这就是为什么您向我们展示所有案例很重要的原因使用示例数据，而不是单词问题，并解释查询的预期结果鉴于该数据。

编辑 - 要处理超过 2 个用户，您只需要进行一些更改。如果你添加一个split函数如下:

CREATE FUNCTION dbo.SplitInts( @List VARCHAR(MAX) )
RETURNS TABLE
AS
   RETURN 
   ( SELECT Item = CONVERT(INT, Item) FROM (
      SELECT Item = x.i.value('(./text())[1]', 'INT') FROM (
       SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, ',', '</i><i>') 
         + '</i>').query('.')) AS a CROSS APPLY [XML].nodes('i') AS x(i)) AS y
       WHERE Item IS NOT NULL
   );

现在对存储过程进行非常小的更改(我省略了未更改的位):

ALTER PROCEDURE dbo.GetPossibleMeetingTimes
    @UserIDList   VARCHAR(MAX),  -- removed other two parameters
    @Duration     INT,           
    @StartDate    SMALLDATETIME, 
    @EndDate      SMALLDATETIME  
AS
...
        WHERE sub.group_id IN -- changed the code within this subquery
        (
            SELECT GroupID FROM dbo.Membership AS m
              INNER JOIN dbo.SplitInts(@UserIDList) AS i
              ON m.UserID = i.Item
              WHERE (p.de > sub.event_start AND p.ds < sub.event_end)
        )
...

那么你的电话只是稍微改变为:

EXEC dbo.GetPossibleMeetingTimes
     @UserIDList = '1,2,3,4,5',
     @Duration   = 30,
     @StartDate  = '20120314',
     @EndDate    = '20120314';

只需确保请求者包含在以逗号分隔的列表中即可。

PS 此附录未经测试。