public ActionResult Index(string category = "")
{
var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
using (var clientContext = HttpContext.GetUserClientContextForSPHost()) {
ViewBag.SPHostUrl = spContext.SPHostUrl;
ViewBag.Category = category;
var list = clientContext.Web.GetListByTitle(ListDetails.EventsListName);
var categoryQuery = string.Empty;
if (!string.IsNullOrEmpty(category))
{
categoryQuery = CAML.Where(CAML.Eq(CAML.FieldValue(Event.FIELD_CATEGORY, "Text", category)));
}
var orderByQuery = CAML.OrderBy(new OrderByField("Title"));
var caml = new CamlQuery()
{
ViewXml = CAML.ViewQuery(categoryQuery, orderByQuery, rowLimit: 50)
};
var events = list.GetItems(caml);
clientContext.Load(events);
clientContext.ExecuteQuery();
var eventsList = events.Cast <ListItem>().Select(item => new Event(item)).ToList();
return(View(eventsList));
}
}
public ActionResult Featured(int maxCount = 3)
{
using (var clientContext = HttpContext.GetUserClientContextForSPHost()) {
ViewBag.SPHostUrl = HttpContext.Request.QueryString["SPHostUrl"];
var caml = new CamlQuery()
{
ViewXml = CAML.ViewQuery(
CAML.Where(
CAML.And(
CAML.Geq(
CAML.FieldValue(Event.FIELD_DATE, "Date", CAML.Today())
),
CAML.Eq(
CAML.FieldValue(Event.FIELD_CATEGORY, "Text", "Featured")
)
)
),
CAML.OrderBy(new OrderByField(Event.FIELD_DATE)),
rowLimit: maxCount)
};
var list = clientContext.Web.Lists.GetByTitle(ListDetails.EventsListName);
clientContext.Load(list);
clientContext.ExecuteQuery();
var items = list.GetItems(caml);
clientContext.Load(items);
clientContext.ExecuteQuery();
var result = items.Cast <ListItem>()
.Select(i => new Event(i)).ToList();
return(View(result));
}
}
/// <summary>
/// Query the list to retreive the last ID
/// </summary>
/// <param name="onlineLibrary">The List we will query</param>
/// <param name="lastItemModifiedDate">The date of the last modified list item</param>
/// <returns></returns>
public static int QueryLastItemId(this List onlineLibrary, Nullable <DateTime> lastItemModifiedDate = null)
{
var returnId = 0;
var camlFieldRefs = new string[] { "ID", "Created", "Modified", };
var camlViewClause = CAML.ViewFields(camlFieldRefs.Select(s => CAML.FieldRef(s)).ToArray());
var camlQuery = new CamlQuery()
{
ViewXml = CAML.ViewQuery(
ViewScope.RecursiveAll,
string.Empty,
CAML.OrderBy(new OrderByField("ID", false)),
camlViewClause,
1)
};
ListItemCollectionPosition itemPosition = null;
while (true)
{
camlQuery.ListItemCollectionPosition = itemPosition;
var spListItems = onlineLibrary.GetItems(camlQuery);
onlineLibrary.Context.Load(spListItems, lti => lti.ListItemCollectionPosition);
onlineLibrary.Context.ExecuteQueryRetry();
itemPosition = spListItems.ListItemCollectionPosition;
if (spListItems.Any())
{
if (lastItemModifiedDate.HasValue)
{
foreach (var item in spListItems)
{
var itemModified = item.RetrieveListItemValue("Modified").ToNullableDatetime();
if (itemModified == lastItemModifiedDate)
{
returnId = item.Id;
break;
}
}
}
else
{
returnId = spListItems.OrderByDescending(ob => ob.Id).FirstOrDefault().Id;
}
if (returnId > 0)
{
// Found the item ID that matches the specified date - return it
break;
}
}
if (itemPosition == null)
{
break;
}
}
return(returnId);
}
public void OrderBy_SendManyFieldRefsInOneString_DoesNotThrow()
{
const string orderByString = @"<FieldRef Name=""ID1"" /><FieldRef Name=""ID2"" /><FieldRef Name=""ID3"" />";
var correctResult = $"<OrderBy>{orderByString}</OrderBy>";
var res = CAML.OrderBy(orderByString);
Assert.AreEqual(correctResult, res);
}
/// <summary>
/// Query the list to retreive the last ID
/// </summary>
/// <param name="onlineLibrary">The List we will query</param>
/// <param name="lastItemModifiedDate">The date of the last modified list item</param>
/// <returns></returns>
public static int QueryLastItemId(this List onlineLibrary, Nullable <DateTime> lastItemModifiedDate = null)
{
var returnId = 0;
var camlFieldRefs = new string[] { "ID", "Created", "Modified", };
var camlViewClause = CAML.ViewFields(camlFieldRefs.Select(s => CAML.FieldRef(s)).ToArray());
var camlQuery = new CamlQuery()
{
ViewXml = CAML.ViewQuery(string.Empty, CAML.OrderBy(new OrderByField("Modified", false)), 10)
};
if (!lastItemModifiedDate.HasValue)
{
onlineLibrary.EnsureProperties(olp => olp.LastItemModifiedDate, olp => olp.LastItemUserModifiedDate);
lastItemModifiedDate = onlineLibrary.LastItemModifiedDate;
}
ListItemCollectionPosition ListItemCollectionPosition = null;
while (true)
{
camlQuery.ListItemCollectionPosition = ListItemCollectionPosition;
var spListItems = onlineLibrary.GetItems(camlQuery);
onlineLibrary.Context.Load(spListItems, lti => lti.ListItemCollectionPosition);
onlineLibrary.Context.ExecuteQueryRetry();
ListItemCollectionPosition = spListItems.ListItemCollectionPosition;
if (spListItems.Any())
{
foreach (var item in spListItems)
{
var itemModified = item.RetrieveListItemValue("Modified").ToDateTime();
System.Diagnostics.Trace.TraceInformation("Item {0} Modified {1} IS MATCH:{2}", item.Id, itemModified, (itemModified == lastItemModifiedDate));
}
returnId = spListItems.OrderByDescending(ob => ob.Id).FirstOrDefault().Id;
break;
}
if (ListItemCollectionPosition == null)
{
break;
}
}
return(returnId);
}
public void Tag_ComplicatedQuery_DoesNotThrowXmlException()
{
Assert.DoesNotThrow(() =>
{
var fieldRefId = CAML.FieldRef(BuiltInInternalFieldNames.ID);
var orderByAsCaml = CAML.OrderBy(
CAML.FieldRef(BuiltInInternalFieldNames.ID, CAML.SortType.Ascending)
);
var viewFields = new List <string>
{
BuiltInInternalFieldNames.ID,
BuiltInInternalFieldNames.Title
};
var viewFieldsAsCaml = CAML.ViewFieldsByFieldNames(viewFields.ToArray());
const int maxRowLimit = 1000;
var rowLimitAsCaml = CAML.RowLimit(maxRowLimit);
const int startId = 1;
const int endId = maxRowLimit;
CAML.View(
string.Concat(
CAML.Query(
string.Concat(
CAML.Where(
CAML.And(
CAML.Geq(
fieldRefId,
CAML.Value(startId)
),
CAML.Leq(
fieldRefId,
CAML.Value(endId)
)
)
),
orderByAsCaml
)
),
viewFieldsAsCaml,
rowLimitAsCaml
)
);
});
}
/// <summary>
/// Execute the cmdlet
/// </summary>
public override void ExecuteCmdlet()
{
base.ExecuteCmdlet();
// Initialize logging instance with Powershell logger
ITraceLogger logger = new DefaultUsageLogger(LogVerbose, LogWarning, LogError);
// Construct the model
var SiteComponents = new SiteProvisionerModel()
{
Lists = new List <SPListDefinition>()
};
var ctx = this.ClientContext;
var contextWeb = this.ClientContext.Web;
ctx.Load(contextWeb, ctxw => ctxw.ServerRelativeUrl, wctx => wctx.Url, ctxw => ctxw.Id);
ctx.ExecuteQueryRetry();
var _targetList = Identity.GetList(contextWeb, lctx => lctx.ItemCount, lctx => lctx.EnableFolderCreation, lctx => lctx.RootFolder, lctx => lctx.RootFolder.ServerRelativeUrl, lctx => lctx.RootFolder.Folders);
var webUri = new Uri(contextWeb.Url);
// Will query the list to determine the last item id in the list
var lastItemId = _targetList.QueryLastItemId();
logger.LogInformation("List with item count {0} has a last ID of {1}", _targetList.ItemCount, lastItemId);
logger.LogInformation("List has folder creation = {0}", _targetList.EnableFolderCreation);
// store the OOTB standard fields
var ootbCamlFields = new List <string>()
{
"Title", "ID", "Author", "Editor", "Created", "Modified"
};
// Skip these specific fields
var skiptypes = new FieldType[]
{
FieldType.Invalid,
FieldType.Computed,
FieldType.ContentTypeId,
FieldType.Invalid,
FieldType.WorkflowStatus,
FieldType.WorkflowEventType,
FieldType.Threading,
FieldType.ThreadIndex,
FieldType.Recurrence,
FieldType.PageSeparator,
FieldType.OutcomeChoice,
FieldType.CrossProjectLink,
FieldType.ModStat,
FieldType.Error,
FieldType.MaxItems,
FieldType.Attachments
};
// pull a small portion of the list
// wire up temporary array
var sitelists = new List <SPListDefinition>()
{
ctx.GetListDefinition(_targetList, ExpandObjects, logger, skiptypes)
};
if (sitelists.Any())
{
var idx = 0;
// lets add any list with NO lookups first
var nolookups = sitelists.Where(sl => !sl.ListDependency.Any());
nolookups.ToList().ForEach(nolookup =>
{
logger.LogInformation("adding list {0}", nolookup.ListName);
nolookup.ProvisionOrder = idx++;
SiteComponents.Lists.Add(nolookup);
sitelists.Remove(nolookup);
});
// order with first in stack
var haslookups = sitelists.Where(sl => sl.ListDependency.Any()).OrderBy(ob => ob.ListDependency.Count()).ToList();
while (haslookups.Count() > 0)
{
var listPopped = haslookups.FirstOrDefault();
haslookups.Remove(listPopped);
logger.LogInformation("popping list {0}", listPopped.ListName);
if (listPopped.ListDependency.Any(listField => listPopped.ListName.Equals(listField, StringComparison.InvariantCultureIgnoreCase) ||
listPopped.InternalName.Equals(listField, StringComparison.InvariantCultureIgnoreCase)))
{
// the list has a dependency on itself
logger.LogInformation("Adding list {0} with dependency on itself", listPopped.ListName);
listPopped.ProvisionOrder = idx++;
SiteComponents.Lists.Add(listPopped);
}
else if (listPopped.ListDependency.Any(listField =>
!SiteComponents.Lists.Any(sc => sc.ListName.Equals(listField, StringComparison.InvariantCultureIgnoreCase) ||
sc.InternalName.Equals(listField, StringComparison.InvariantCultureIgnoreCase))))
{
// no list definition exists in the collection with the dependent lookup lists
logger.LogWarning("List {0} depends on {1} which do not exist current collection", listPopped.ListName, string.Join(",", listPopped.ListDependency));
foreach (var dependent in listPopped.ListDependency.Where(dlist => !haslookups.Any(hl => hl.ListName == dlist)))
{
var sitelist = contextWeb.GetListByTitle(dependent, lctx => lctx.Id, lctx => lctx.Title, lctx => lctx.RootFolder.ServerRelativeUrl);
var sitelistDefinition = ctx.GetListDefinition(contextWeb, sitelist, true, logger, skiptypes);
haslookups.Add(sitelistDefinition);
}
haslookups.Add(listPopped); // add back to collection
listPopped = null;
}
else
{
logger.LogInformation("Adding list {0} to collection", listPopped.ListName);
listPopped.ProvisionOrder = idx++;
SiteComponents.Lists.Add(listPopped);
}
}
}
foreach (var listDefinition in SiteComponents.Lists.OrderBy(ob => ob.ProvisionOrder))
{
listDefinition.ListItems = new List <SPListItemDefinition>();
var camlFields = new List <string>();
var listTitle = listDefinition.ListName;
var etlList = this.ClientContext.Web.GetListByTitle(listTitle,
lctx => lctx.Id, lctx => lctx.ItemCount, lctx => lctx.EnableFolderCreation, lctx => lctx.RootFolder.ServerRelativeUrl);
if (ExpandObjects)
{
// list fields
var listFields = listDefinition.FieldDefinitions;
if (listFields != null && listFields.Any())
{
var filteredListFields = listFields.Where(lf => !skiptypes.Any(st => lf.FieldTypeKind == st)).ToList();
var notInCamlFields = listFields.Where(listField => !ootbCamlFields.Any(cf => cf.Equals(listField.InternalName, StringComparison.CurrentCultureIgnoreCase)) && !skiptypes.Any(st => listField.FieldTypeKind == st));
foreach (var listField in notInCamlFields)
{
logger.LogInformation("Processing list {0} field {1}", etlList.Title, listField.InternalName);
camlFields.Add(listField.InternalName);
}
}
}
camlFields.AddRange(ootbCamlFields);
var camlViewFields = CAML.ViewFields(camlFields.Select(s => CAML.FieldRef(s)).ToArray());
ListItemCollectionPosition itemPosition = null;
var camlQueries = etlList.SafeCamlClauseFromThreshold(2000, CamlStatement, 0, lastItemId);
foreach (var camlAndValue in camlQueries)
{
itemPosition = null;
var camlWhereClause = (string.IsNullOrEmpty(camlAndValue) ? string.Empty : CAML.Where(camlAndValue));
var camlQuery = new CamlQuery()
{
ViewXml = CAML.ViewQuery(
ViewScope.RecursiveAll,
camlWhereClause,
CAML.OrderBy(new OrderByField("ID")),
camlViewFields,
500)
};
try
{
while (true)
{
logger.LogWarning("CAML Query {0} at position {1}", camlWhereClause, (itemPosition == null ? string.Empty : itemPosition.PagingInfo));
camlQuery.ListItemCollectionPosition = itemPosition;
var listItems = etlList.GetItems(camlQuery);
etlList.Context.Load(listItems);
etlList.Context.ExecuteQueryRetry();
itemPosition = listItems.ListItemCollectionPosition;
foreach (var rbiItem in listItems)
{
var itemId = rbiItem.Id;
var itemTitle = rbiItem.RetrieveListItemValue("Title");
var author = rbiItem.RetrieveListItemUserValue("Author");
var editor = rbiItem.RetrieveListItemUserValue("Editor");
logger.LogInformation("Title: {0}; Item ID: {1}", itemTitle, itemId);
var newitem = new SPListItemDefinition()
{
Id = itemId,
Title = itemTitle,
Created = rbiItem.RetrieveListItemValue("Created").ToNullableDatetime(),
Modified = rbiItem.RetrieveListItemValue("Modified").ToNullableDatetime()
};
if (author != null)
{
newitem.CreatedBy = new SPPrincipalUserDefinition()
{
Email = author.ToUserEmailValue(),
LoginName = author.ToUserValue(),
Id = author.LookupId
};
}
if (editor != null)
{
newitem.ModifiedBy = new SPPrincipalUserDefinition()
{
Email = editor.ToUserEmailValue(),
LoginName = editor.ToUserValue(),
Id = editor.LookupId
};
}
if (ExpandObjects)
{
var fieldValuesToWrite = rbiItem.FieldValues.Where(rfield => !ootbCamlFields.Any(oc => rfield.Key.Equals(oc, StringComparison.CurrentCultureIgnoreCase)));
foreach (var rbiField in fieldValuesToWrite)
{
newitem.ColumnValues.Add(new SPListItemFieldDefinition()
{
FieldName = rbiField.Key,
FieldValue = rbiField.Value
});
}
}
listDefinition.ListItems.Add(newitem);
}
if (itemPosition == null)
{
break;
}
}
}
catch (Exception ex)
{
logger.LogError(ex, "Failed to retrieve list item collection");
}
}
}
WriteObject(SiteComponents);
}
