I need to check an SPList if it contains Activities that have overlapping DateTime values.
An Activity is a type which contains:
- Date on which the activity was done (
ActivityDate) - Start time of the activity (
ActivityStartTime) - End time of the activity (
ActivityEndTime)
I have already applied the accepted answer from this SO post but I need to do this in CAML so it's a little more efficient.
Here is my method which does the whole thing:
public static bool HandleItemAdded(SPListItem item, SPList list)
{
bool conflictExists = false;
SPListItem currentItem = item;
//Get data of new item...
DateTime currentItemDate = DateTime.Parse(currentItem[Variables.activityDateGuid].ToString());
DateTime tempStart = DateTime.Parse(currentItem[Variables.activityStartTimeGuid].ToString());
DateTime tempEnd = DateTime.Parse(currentItem[Variables.activityEndTimeGuid].ToString());
DateTime currentItemStartTime = new DateTime(currentItemDate.Year, currentItemDate.Month, currentItemDate.Day, tempStart.Hour, tempStart.Minute, tempStart.Second);
DateTime currentItemendTime = new DateTime(currentItemDate.Year, currentItemDate.Month, currentItemDate.Day, tempEnd.Hour, tempEnd.Minute, tempEnd.Second);
//Get all items who have conflicts...
SPQuery query = new SPQuery()
{
Query = "<Where>" +
"<And>" +
"<And>" +
"<And>" +
"<Eq>" +
"<FieldRef Name=\"ActivityDate\" />" +
"<Value IncludeTimeValue=\"FALSE\" Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemDate) + "</Value>" +
"</Eq>" +
"<Gt>" +
"<FieldRef Name=\"ActivityEndTime\" />" +
"<Value Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemStartTime) + "</Value>" +
"</Gt>" +
"</And>" +
"<Neq>" +
"<FieldRef Name=\"ID\" />" +
"<Value Type=\"Number\">" + currentItem.ID + "</Value>" +
"</Neq>" +
"</And>" +
"<Lt>" +
"<FieldRef Name=\"ActivityStartTime\" />" +
"<Value Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemendTime) + "</Value>" +
"</Lt>" +
"</And>" +
"</Where>"
};
SPListItemCollection items = list.GetItems(query); //Always empty here...
// Do something with items...
}
My problem is, I get no returns even if there should be. The ID and Date filters are working as they return the correct values if I take out the other two. But vice versa I also get nothing back.
Does anybody know what I'm doing wrong here?
EDIT:
Weird. When I substitute the <Gt> and <Lt> with <Geq> and <Leq>, respectively, it works but I also get conflicts when I create two activities like this:
- A1: 10.02.2015 09:00 - 10:00
- A2: 10.02.2015 10:00 - 11:00
This should not cause a conflict.
