Sharepoint CAML查询避免日历重叠预订

Question

我正在使用日历列表进行一些房间预订，一切都很好，但是只有在同一天和时间内的房间重叠或双重预订方面遇到了问题。

我正在尝试通过将用户在表单中输入的数据传递给 CAML 查询来确定此条目是否存在。

如果该条目已经存在，则取消预订；如果不存在，则继续。

例如：如果从上午10:00到上午11:00已经预订。

重叠的情况可能是：

如果用户在表单中输入 从上午10:00到上午10:30

从上午9:00到上午11:00...... 我假设日期相同，只有时间对我有意义。

如何获取 CAML 查询，如果日期相同，只有时间不同，并检查用户输入的起始时间和结束时间是否在已预订项目的范围内。

使用以下查询内容，但不能验证所有情况：

<Where><And><Geq><FieldRef Name='EventDate' /><Value IncludeTimeValue='TRUE'  Type='DateTime'>" + strSPEventDateFormat + "</Value></Geq><And><Leq><FieldRef Name='EndDate' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + strSPEndDateFormat + "</Value></Leq><Eq><FieldRef Name='Room' /><Value Type='Lookup'>" + strCheckRoomAvail + "</Value></Eq></And></And></Where>

请帮我解决这个问题。

提前感谢您的帮助。

- jam

Answer 1

编写CAML以获取所有与“开始时间”冲突的房间。开始时间不能在用户选择的开始时间和结束时间之间 AND 编写CAML以获取所有与“结束时间”冲突的房间。结束时间不能在用户选择的开始时间和结束时间之间

如果用户选择的房间不在上述CAML返回的结果中，则可以继续预订该房间。

 q.Query = "<Where><And><Geq><FieldRef Name=""StartTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(startTime) + _
        "</Value></Geq><Leq><FieldRef Name=""StartTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(endTime) + _
        "</Value></Leq></And></Where><OrderBy><FieldRef Name=""ID"" Ascending=""True"" /></OrderBy>"

q1.Query = "<Where><And><Geq><FieldRef Name=""EndTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(startTime) + _
        "</Value></Geq><Leq><FieldRef Name=""EndTime"" /><Value IncludeTimeValue=""TRUE"" Type=""DateTime"">" + _
        Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(endTime) + _
        "</Value></Leq></And></Where><OrderBy><FieldRef Name=""ID"" Ascending=""True"" /></OrderBy>"

- Ken

Answer 2

这个帖子非常旧了，但我刚好遇到了同样的问题。所以这里是我的解决方案，希望能帮到一些人。

注意：对于时间相关的Search-CAML，您需要使用IncludeTimeValue='TRUE'参数。

/**
 * {Boolean} formModeEdit To determine if this form is an New or Edit form
 */
function checkDoubleBooking(formModeEdit, listId, currentItemId, startDate, endDate) {
  var results = [];
  var liHtml = "";
  var checkOverlappingQueryArray = [];

  //Construct caml query
  checkOverlappingQueryArray.push("<And>");
  checkOverlappingQueryArray.push("<Leq>");
  checkOverlappingQueryArray.push("<FieldRef Name='EventDate' /><Value Type='DateTime' IncludeTimeValue='TRUE'>" + moment(endDate).format() + "</Value>");
  checkOverlappingQueryArray.push("</Leq>");
  checkOverlappingQueryArray.push("<Geq>");
  checkOverlappingQueryArray.push("<FieldRef Name='EndDate' /><Value Type='DateTime' IncludeTimeValue='TRUE'>" + moment(startDate).format() + "</Value>");
  checkOverlappingQueryArray.push("</Geq>");
  checkOverlappingQueryArray.push("</And>");
  if (formModeEdit) {
    checkOverlappingQueryArray.unshift("<And>");
    checkOverlappingQueryArray.push("<Neq>");
    checkOverlappingQueryArray.push("<FieldRef Name='ID' /><Value Type='Integer'>" + currentItemId + "</Value>");
    checkOverlappingQueryArray.push("</Neq>");
    checkOverlappingQueryArray.push("</And>");
  }
  checkOverlappingQueryArray.unshift("<Where>");
  checkOverlappingQueryArray.push("</Where>");
  checkOverlappingQueryArray.unshift("<Query>");
  checkOverlappingQueryArray.push("</Query>");

  jQuery().SPServices({
      operation: "GetListItems",
      async: false,
      listName: listId,
      CAMLViewFields: "<ViewFields>" +
        "<FieldRef Name='ID' />" +
        "<FieldRef Name='Title' />" +
        "<FieldRef Name='EventDate' />" +
        "<FieldRef Name='EndDate' />" +
        "<FieldRef Name='PeoplePickerField1' />" +
        "</ViewFields>",
      CAMLQuery: checkOverlappingQuery,
      CAMLQueryOptions: "<QueryOptions>" +
        "<ExpandUserField>True</ExpandUserField>" + //Expand People Picker values
        "</QueryOptions>",
      CAMLRowLimit: 10, // Override the default view rowlimit
      completefunc: function(xData, Status) {
        $(xData.responseXML).SPFilterNode("z:row").each(function() {
            countOverlappingEvents++;
            results.push({
              title: $(this).attr("ows_Title"),
              eventDate: $(this).attr("ows_EventDate"),
              endDate: $(this).attr("ows_EndDate"),
              peoplePickerField1: userToJsonObject($(this).attr("ows_PeoplePickerField1"))
            });
          }
        }
      });
    return results;
  }

  function userToJsonObject(userObj) {
    if (userObj.length === 0) {
      return null;
    } else {
      var thisUser = userObj.split(";#");
      var thisUserExpanded = thisUser[1].split(",#")
      if (thisUserExpanded.length == 1) {
        return {
          userId: thisUser[0],
          userName: thisUser[1]
        }
      } else {
        return {
          userId: thisUser[0],
          userName: thisUserExpanded[0].replace(/(,,)/g, ","),
          loginName: thisUserExpanded[1].replace(/(,,)/g, ","),
          email: thisUserExpanded[2].replace(/(,,)/g, ","),
          sipAddress: thisUserExpanded[3].replace(/(,,)/g, ","),
          title: thisUserExpanded[4].replace(/(,,)/g, ",")
        }
      }
    }
  }

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.22.2/moment-with-locales.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery.SPServices/2014.02/jquery.SPServices-2014.02.min.js"></script>

- Jan