Comparaison des structures DateTime pour find des locations libres

Je voudrais searchr à travers les events de tous les users dans une list et récupérer toutes les fois où chaque user est libre de 30 minutes ou plus entre 7 AM-7PM.

Cependant, si une méthode est marquée comme "récurrente", c'est-à-dire que le bit récurrent est à 1, alors cet événement se répète pendant une période de 52 semaines après son début (donc l'heure n'est pas disponible). La récupération de ces events est prise en charge dans une procédure stockée.

Mon code jusqu'ici est ci-dessous. Est-ce que je vais écrire cette procédure de la bonne façon? Je ne suis pas vraiment sûr de savoir comment procéder pour que la fonction revienne comme je le voudrais. Est-ce que quelqu'un pourrait m'aider avec ça?

List<ssortingng> 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 (ssortingng username in usernames) { //resortingeve 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 }

Structure de la table:

 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 );

Exemple de fonctionnalité que j'aimerais:

L'user ajoute plusieurs users de la database à une list. L'user sélectionne une période sur laquelle il souhaite rencontrer tous ces users. Mon algorithm calcule toutes les périodes de time qui sont gratuites pour tous les users (c.-à-d. Une heure qui conviendrait à une réunion entre tous les users et qui est> 30 minutes).

Information additionnelle :

Exemples de cas:

L'user A tente d'organiser une réunion avec l'user B. Tous les intervalles de time sont gratuits. Je voudrais que l'algorithm renvoie un début DateTime et DateTime fin de toutes les combinaisons possibles d'heures de début et de fin qui sont> 30mins et == durée (un paramètre).
Cas typique: L'user A a prévu des events pour tous les time sauf 18h – 19h. Il tente d'organiser une réunion avec l'user B pour une durée d'une heure. L'user B n'a aucun événement organisé – le DateTime 6PM et le DateTime 19pm sont renvoyés pour indiquer l'heure de début et de fin des réunions.
Cas récurrent: l'user A a un événement récurrent à 17h-18h un lundi. Il essaie d'organiser une réunion de deux heures un lundi dans six semaines. Toutes les combinaisons de DateTime start et DateTime end où il y a une différence de 2 heures sont renvoyées. L'heure de 17h à 19h n'est pas returnnée, puisque cet événement est récurrent et se produit chaque semaine pendant 52 semaines.

Voici la procédure stockée qui récupère tous les events d'un user pour une période définie (début, fin):

 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

Alors imaginez quelques tables:

 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 );

Et imaginez que certains exemples de données n'étaient pas si difficiles à fournir:

 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

Maintenant, nous pouvons build cette procédure stockée:

 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

Exemples d'appels:

 -- Case 1: User A sortinges 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';

Résultats:

aucun événement pour l'un ou l'autre utilisateur

 -- Case 2: User A sortinges 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';

Résultats:

l'utilisateur A est occupé presque toute la journée

 -- Case 3: User A sortinges 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';

Résultats:

l'utilisateur A a une réunion récurrente

Maintenant, notez que j'ai pris en charge plusieurs facteurs que vous n'avez pas pris en count ou que vous n'avez pas mentionnés (comme un événement récurrent qui commence dans le futur). D'un autre côté, je n'ai pas non plus pris en count d'autres facteurs (par exemple l'heure d'été, si cela peut avoir un impact) et n'ai pas testé tous les scénarios possibles (par exemple plusieurs events le même jour).

J'ai testé que si vous passez dans une plage (par exemple, 2012-03-12 -> 2012-03-14) vous obtiendrez essentiellement une union des résultats ci-dessus avec à peu près les mêmes créneaux horaires disponibles (ceux-ci varient en fonction de la durée bien sûr). La partie importante est que les intervalles de time d'occultation sont honorés. Je n'ai pas testé la logique pour le cas où un événement récurrent commence dans le futur et la plage de dates fournie inclut ce jour de la semaine avant et après la première instance de l'événement.

Si un cas ne fonctionne pas pour vous, c'est exactement pourquoi il est important que vous nous monsortingez tous vos cas en utilisant des exemples de données, pas des problèmes de mots et aussi expliquer les résultats souhaités de la requête donnée à ces données.

EDIT – pour gérer plus de 2 users, vous n'avez besoin que de quelques modifications. Si vous ajoutez une fonction de partage comme suit:

 CREATE FUNCTION dbo.SplitInts( @List VARCHAR(MAX) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(INT, Item) FROM ( SELECT Item = xivalue('(./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 );

Maintenant, des changements mineurs à la procédure stockée (j'ai laissé de côté les bits inchangés):

 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) ) ...

Alors, votre appel change légèrement pour:

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

Assurez-vous simplement que le requestur est inclus dans la list séparée par des virgules.

PS cet addendum n'a pas été testé.