public static StringBuilder FindChanges()
{
StringBuilder sb = new StringBuilder();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPSiteDataQuery query1 = new SPSiteDataQuery();
//query1.ViewFields ="<OrderBy><FieldRef Name=\"Modified\" Ascending=\"False\" /></OrderBy><Where><Gt><FieldRef Name='Modified' /><Value Type='DateTime'><Today OffsetDays='-7' /></Value></Gt></Where>";
query1.RowLimit = 100;
//query1.Webs = "<Webs Scope=\"Recursive\" />";
query1.Lists = "<Lists BaseType=\"0\" />";
using (SPSite site = new SPSite(SPContext.Current.Web.Url))
{
DataTable dt = site.RootWeb.GetSiteData(query1);
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
sb.Append("dt[" + column.ColumnName + "]: " + row[column]);
}
}
}
});
return(sb);
}
public static DataTable GetSPListItemsBySiteSelf(Guid siteId, string webUrl, string listTemplate,
string querystr, string viewFieldsstr, uint rowLimit)
{
DataTable dataTable = null;
try
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite(siteId))
{
using (SPWeb web = site.OpenWeb(webUrl))
{
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate='" + listTemplate + "' />";
query.Webs = "<Webs Scope='Recursive' />";
query.Query = querystr;
query.ViewFields = viewFieldsstr;
if (rowLimit != 0)
{
query.RowLimit = rowLimit;
}
dataTable = web.GetSiteData(query);
}
}
});
}
catch (Exception ex)
{
throw ex;
}
return(dataTable);
}
public void QueryMyWorkData_WhenCalled_ReturnsDataTable()
{
// Arrange
var spSiteDataQuery = new SPSiteDataQuery();
var userToken = new ShimSPUserToken().Instance;
var dataTable = new DataTable();
dataTable.Columns.Add(ListIdColumn);
var row = dataTable.NewRow();
row[ListIdColumn] = guid.ToString();
dataTable.Rows.Add(row);
spWeb.GetSiteDataSPSiteDataQuery = _ => dataTable;
// Act
var actual = (DataTable)privateObj.Invoke(
QueryMyWorkDataMethodName,
BindingFlags.Static | BindingFlags.NonPublic,
new object[] { spSiteDataQuery, string.Empty, guid, userToken });
// Assert
actual.ShouldSatisfyAllConditions(
() => actual.Rows.Count.ShouldBe(1),
() => actual.Rows[0][ListIdColumn].ShouldBe(guid.ToString()));
}
public void getSiteItems_HasListIds_GetSiteDataExecuted()
{
// Arrange
var resultExpected = new DataTable();
SPSiteDataQuery queryUsed = null;
_sharepointShims.WebShim.GetSiteDataSPSiteDataQuery = query =>
{
queryUsed = query;
return(resultExpected);
};
// Act
var result = CoreFunctions.getSiteItems(
_sharepointShims.WebShim,
_sharepointShims.ViewShim,
_spQuery,
_filterFieldName,
_useWbs,
_listTitlePattern,
_groupByFieldNames);
// Assert
Assert.AreEqual(resultExpected, result);
Assert.IsNotNull(queryUsed);
Assert.AreEqual($"<Lists MaxListLimit='0'><List ID='{Guid.Empty}'/></Lists>", queryUsed.Lists);
Assert.AreEqual(_spQuery, queryUsed.Query);
Assert.AreEqual(SPQueryThrottleOption.Override, queryUsed.QueryThrottleMode);
}
/// <summary>
/// Binds this query to an SPGridView control.
/// </summary>
/// <param name="web"></param>
/// <param name="gridView"></param>
/// <param name="query"></param>
/// <param name="initColumns"></param>
private void BindToGridView(SPWeb web, SPGridView gridView, SPSiteDataQuery query, bool initColumns)
{
// Execute the query.
DataTable table = web.GetSiteData(query);
// Initialize the gridview columns, if requested
if (initColumns)
{
gridView.AutoGenerateColumns = false;
gridView.Columns.Clear();
foreach (var col in
table.Columns.Cast <DataColumn>()
.Where(IsBindableColumn)
.Select(column => new BoundField
{
DataField = column.ColumnName,
HeaderText = string.IsNullOrEmpty(column.Caption)
? column.ColumnName
: column.Caption
}))
{
gridView.Columns.Add(col);
}
}
// Bind the data to the view.
gridView.DataSource = table;
gridView.DataBind();
}
private static SPSiteDataQuery SpSiteDataQuery(string query, List <string> selectedFields)
{
Guard.ArgumentIsNotNull(selectedFields, nameof(selectedFields));
var dataQuery = new SPSiteDataQuery
{
Webs = @"<Webs Scope='Recursive'>",
Query = query,
QueryThrottleMode = SPQueryThrottleOption.Override
};
var viewFieldsBuilder = new StringBuilder(dataQuery.ViewFields);
foreach (var selectedField in selectedFields)
{
viewFieldsBuilder.Append($@"<FieldRef Name='{selectedField}' Nullable='TRUE'/>");
}
foreach (var field in new[] { CompletedField, WorkingOnField }.Where(field => !selectedFields.Exists(f => f.Equals(field))))
{
viewFieldsBuilder.Append($"<FieldRef Name='{field}' Nullable='TRUE'/>");
}
dataQuery.ViewFields = viewFieldsBuilder.ToString();
return(dataQuery);
}
public void ConstructDataQuery(SPSiteDataQuery q)
{
if (_listIds.Count == 0)
{
q.Lists = string.Format("<Lists BaseType='{0}'/>", (int)_listType);
}
else
{
string lists = "<Lists>";
foreach (Guid listId in _listIds)
{
lists += string.Format("<List ID='{0}' />", listId);
}
lists += "</Lists>";
q.Lists = lists;
}
q.Query = _query;
if (_webs != Scope.Current)
{
q.Webs = string.Format("<Webs Scope='{0}' />", _webs);
}
if (_viewFields.Count > 0)
{
string viewFields = "";
foreach (string viewField in _viewFields)
{
viewFields += string.Format("<FieldRef Name='{0}'/>", viewField);
}
q.ViewFields = viewFields;
}
}
/// <summary>
/// Build a CAML SPSiteDataQuery object, based on the filter expressions that have been added to it.
/// </summary>
/// <returns></returns>
public SPSiteDataQuery BuildSiteDataQuery()
{
var query = new SPSiteDataQuery {
Query = Build()
};
return(query);
}
public SPSiteDataQueryInstance(ObjectInstance prototype)
: base(prototype)
{
this.PopulateFields();
this.PopulateFunctions();
m_siteDataQuery = new SPSiteDataQuery();
}
public virtual DataTable Get(string queryString) {
var query = new SPSiteDataQuery() {
Lists = ListTemplate,
Webs = WebScope,
ViewFields = ViewFields,
Query = queryString,
QueryThrottleMode = SPQueryThrottleOption.Override
};
return Get(query);
}
public void GetDataFromSP_WhenCalled_ReturnsDataTables()
{
// Arrange
var selectedListIds = new List <string>();
var spSiteDataQuery = new SPSiteDataQuery();
var row = default(DataRow);
var dataTable = new DataTable();
dataTable.Columns.Add(ListIdColumn);
dataTable.Columns.Add(IDColumn);
dataTable.Columns.Add(WorkingOnColumn);
row = dataTable.NewRow();
row[ListIdColumn] = DummyString;
row[IDColumn] = DummyString;
row[WorkingOnColumn] = DummyString;
dataTable.Rows.Add(row);
var workingTable = new DataTable();
workingTable.Columns.Add(ListIdColumn);
workingTable.Columns.Add(ItemIdColumn);
row = workingTable.NewRow();
row[ListIdColumn] = DummyString;
row[ItemIdColumn] = DummyString;
workingTable.Rows.Add(row);
ShimMyWork.GetListIdsFromDbStringSPWebListOfGuid = (_, _1, _2) => new List <string>()
{
$"{DummyString}{DummyString}"
};
spWeb.GetSiteDataSPSiteDataQuery = _ => dataTable;
ShimMyWork.GetWorkingOnSPWeb = _ => workingTable;
GetMyWorkParams.SelectedLists = new List <string>()
{
DummyString
};
GetMyWorkParams.ArchivedWebs = new List <Guid>()
{
guid
};
// Act
var actual = (List <DataTable>)privateObj.Invoke(
GetDataFromSPMethodName,
BindingFlags.Static | BindingFlags.NonPublic,
new object[] { selectedListIds, spSiteDataQuery, spWeb.Instance, spSite.Instance });
// Assert
actual.ShouldSatisfyAllConditions(
() => actual.Count.ShouldBe(1),
() => actual[0].Rows.Count.ShouldBe(1),
() => actual[0].Rows[0][WorkingOnColumn].ToString().ToLower().ShouldBe("true"));
}
public EstimatesService()
{
query = new SPSiteDataQuery();
query.Lists = "<Lists BaseType='1' />";
query.ViewFields = "<FieldRef Name='SOWStatus' />" +
"<FieldRef Name='EstimateValue' />";
query.Query = "<OrderBy>" +
"<FieldRef Name='EstimateValue' />" +
"</OrderBy>";
query.Webs = "<Webs Scope='SiteCollection' />";
}
/// <summary>
/// Gets the data from SP.
/// </summary>
/// <param name="selectedListIds">The selected list ids.</param>
/// <param name="dataQuery">The data query.</param>
/// <param name="spWeb">The sp web.</param>
/// <param name="spSite">The sp site.</param>
/// <param name="archivedWebs">The archived webs.</param>
/// <param name="selectedLists">The selected lists.</param>
/// <returns></returns>
private static IEnumerable <DataTable> GetDataFromSp(
IList <string> selectedListIds,
SPSiteDataQuery dataQuery,
SPWeb spWeb,
SPSite spSite)
{
Guard.ArgumentIsNotNull(spWeb, nameof(spWeb));
Guard.ArgumentIsNotNull(selectedListIds, nameof(selectedListIds));
Guard.ArgumentIsNotNull(dataQuery, nameof(dataQuery));
Guard.ArgumentIsNotNull(spSite, nameof(spSite));
var locker = new object();
var eventWaitHandles = new List <EventWaitHandle>();
var dataTables = new List <DataTable>();
var spExceptionOccured = false;
foreach (var selectedList in GetMyWorkParams.SelectedLists.Distinct().OrderBy(l => l))
{
var theSelectedList = selectedList;
var listIdsBuilder = new StringBuilder();
foreach (
var listId in GetListIdsFromDb(selectedList, spWeb, GetMyWorkParams.ArchivedWebs)
.Where(listId => !selectedListIds.Contains(listId)))
{
selectedListIds.Add(listId);
listIdsBuilder.Append($@"<List ID='{listId}'/>");
}
var listIds = listIdsBuilder.ToString();
if (string.IsNullOrWhiteSpace(listIds))
{
continue;
}
spExceptionOccured = SpExceptionOccured(dataQuery, spWeb, spSite, listIds, eventWaitHandles, selectedList, locker, dataTables, theSelectedList);
}
WaitHandle.WaitAll(eventWaitHandles.ToArray());
foreach (var eventWaitHandle in eventWaitHandles)
{
eventWaitHandle?.Dispose();
}
if (spExceptionOccured)
{
throw new APIException(2016, CannotRunSpDataQuery);
}
ProcessDictWorkingOn(spWeb, dataTables);
return(dataTables);
}
private string BuildSiteMap(string siteUrl, bool recursive)
{
string sitemap = string.Empty;
try
{
if (recursive)
{
//using (SPWeb web = SPContext.Current.Site.RootWeb)
//{
StringBuilder mapWriter = new StringBuilder(string.Empty);
SPSiteDataQuery sdquery = new SPSiteDataQuery();
sdquery.Query = "<Where><Neq><FieldRef Name=\"ContentType\" /><Value Type=\"Text\"></Value></Neq></Where>";
sdquery.ViewFields = "<FieldRef Name=\"Title\"/>"; //<FieldRef Name=\"EncodedAbsUrl\"/><FieldRef Name=\"FileRef\"/><FieldRef Name=\"ContentType\"/><FieldRef Name=\"Modified\"/>";
sdquery.Lists = "<Lists ServerTemplate=\"20002\"/>";
//sdquery.Lists = "<Lists BaseType=\"1\" />";
//sdquery.Webs = "<Webs Scope=\"SiteCollection\" />";
//sdquery.QueryThrottleMode = SPQueryThrottleOption.Override;
DataTable pages = SPContext.Current.Site.RootWeb.GetSiteData(sdquery); // web.GetSiteData(sdquery);
pages.TableName = "SiteMap";
StringWriter sw = new StringWriter(mapWriter);
pages.WriteXml(sw);
sw.Flush();
sitemap = mapWriter.ToString();
//}
}
//using (SPSite site = new SPSite(siteUrl))
//{
// using (SPWeb web = site.OpenWeb())
// {
// textWriter.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
// textWriter.Append("<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">");
// LoadTreeViewForSubWebs(web); // kick it off with the root web
// textWriter.Append("</urlset>");
// MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(textWriter.ToString()));
// web.Files.Add("sitemap.xml", stream, true);
// stream.Close();
// }
//}
}//try
catch (Exception ex)
{
ex.ToString();
//errorHandler(ex, "BuildSitemap");
}
return(sitemap);
}
public virtual DataTable Get(string queryString, uint startRow, uint maxRows) {
var query = new SPSiteDataQuery() {
Lists = ListTemplate,
Webs = WebScope,
ViewFields = ViewFields,
Query = queryString,
RowLimit = startRow + maxRows,
QueryThrottleMode = SPQueryThrottleOption.Override
};
return Get(query, startRow, maxRows);
}
public virtual SPSiteDataQuery BuildCaml(SiteQuery query)
{
SPSiteDataQuery resultQuery = new SPSiteDataQuery();
resultQuery.Webs = BuildCaml(query.WebScope);
resultQuery.Lists = BuildCaml(query.ListsReference);
resultQuery.Query = BuildCaml(query.Query);
resultQuery.ViewFields = BuildCaml(query.ViewFields.ToArray());
return(resultQuery);
}
/// <summary>
/// Provide the view fields as a string[].
/// Example: query.SetViewFields(new string[] {"ID", "Title"});
/// </summary>
/// <param name="viewFields"></param>
/// <returns></returns>
public static void SetViewFields(this SPSiteDataQuery query, string[] viewFields)
{
StringBuilder sb = new StringBuilder();
foreach (string s in viewFields)
{
sb.AppendFormat(VIEW_FIELD_FORMAT, s);
}
query.ViewFields = sb.ToString();
}
private int getCount(string list, string query, SPWeb curWeb)
{
string siteurl = curWeb.ServerRelativeUrl.Substring(1);
SPSiteDataQuery dq = new SPSiteDataQuery();
int count = 0;
try
{
string lists = "";
string squery = "";
if (siteurl == "")
{
squery = "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE webs.siteid='" + web.Site.ID + "' AND (dbo.AllLists.tp_Title like '" + list.Replace("'", "''") + "')";
}
else
{
squery = "SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE (dbo.Webs.FullUrl LIKE '" + siteurl + "/%' OR dbo.Webs.FullUrl = '" + siteurl + "') AND (dbo.AllLists.tp_Title like '" + list.Replace("'", "''") + "')";
}
SqlCommand cmd = new SqlCommand(squery, cn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
lists += "<List ID='" + dr.GetGuid(0).ToString() + "'/>";
}
dr.Close();
//dq.Lists = "<Lists ServerTemplate='10701'/>";
dq.Lists = "<Lists>" + lists + "</Lists>";
dq.Query = "<Where>" + query + "</Where>";
dq.Webs = "<Webs Scope='Recursive'/>";
dq.QueryThrottleMode = SPQueryThrottleOption.Override;
DataTable dt = new DataTable();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
dt = curWeb.GetSiteData(dq);
});
count = dt.Rows.Count;
}
catch (Exception ex)
{
sb.Append("ERROR: " + ex.Message + ex.StackTrace);
}
return(count);
}
static void Main(string[] args) {
var array = "隐患下达任务;隐患整改任务;隐患复查任务;隐患验收任务;隐患签字任务;隐患签字任务;".Trim(';').Split(';').AsEnumerable();
var siteUrl = "http://58.132.202.99/sites/yanglijun";
using (SPSite site = new SPSite(siteUrl)) {
using (SPWeb web = site.OpenWeb()) {
var queryString = new CAMLQueryBuilder()
.AddCurrentUser(SPBuiltInFieldName.AssignedTo)
.OrCurrentUserGroups(SPBuiltInFieldName.AssignedTo)
.AddNotEqual(SPBuiltInFieldName.TaskStatus, "Completed")
.AddIsNotNull(SPBuiltInFieldName.RelatedItems).Build();
var query = new SPSiteDataQuery() {
Webs = "<Webs Scope='SiteCollection' />",
Lists = "<Lists ServerTemplate='171' BaseType='0' />",
ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ContentType' />",
Query = queryString,
QueryThrottleMode = SPQueryThrottleOption.Override,
RowLimit = 100
};
var data = web.GetSiteData(query);
Console.WriteLine(data.Rows.Count);
Console.ReadKey();
//foreach (SPGroup group in web.SiteGroups) {
// var roleDefinitions = web.RoleDefinitions;
// var roleAssignments = web.RoleAssignments;
// var assignment = new SPRoleAssignment(group);
// var bindings = assignment.RoleDefinitionBindings;
// bindings.Add(roleDefinitions["参与讨论"]);
// roleAssignments.Add(assignment);
// Console.WriteLine(group.Name);
//}
// SPList list = web.Lists["Workflow Tasks"];
// SPView view = list.Views["所有任务"];
// view.Query = @"<Where>
// <Or>
// <Membership Type=""CurrentUserGroups"" >
// <FieldRef Name=""AssignedTo"" />
// </Membership>
// <In>
// <FieldRef Name=""AssignedTo"" LookupId=""TRUE"" />
// <Values>
// <Value Type=""Integer"" >
// <UserID />
// </Value>
// </Values>
// </In>
// </Or>
// </Where>";
// view.Update();
}
}
}
private void AppendLookupQueryIfApplicable(SPSiteDataQuery dq)
{
if (!IsLookupQuery())
{
return;
}
var queryXml = new XmlDocument();
queryXml.LoadXml(dq.Query);
LookupFilterHelper.AppendLookupQueryToExistingQuery(ref queryXml, ChartLookupField, ChartLookupFieldList);
dq.Query = queryXml.OuterXml;
}
protected void loadFromList()
{
using (SPSite oSPsite = new SPSite(SPContext.Current.Web.Url))
{
using (SPWeb oSPWeb = oSPsite.OpenWeb())
{
// Fetch using SPSiteDataQuery
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"104\" />";//104=List template ID for "Anouncements"
query.ViewFields = "<FieldRef Name=\"Title\" />";//Add other fields which you want to get.
query.Query = "";//you can add your caml query if you want to filter the list items returned
query.Webs = "<Webs Scope=\"SiteCollection\" />";//Scope is set to site collection to get data from all anouncement lists in that site collection.
DataTable dataTable = oSPWeb.GetSiteData(query);
}
}
}
protected void loadFromList()
{
using (SPSite oSPsite = new SPSite(SPContext.Current.Web.Url))
{
using (SPWeb oSPWeb = oSPsite.OpenWeb())
{
// Fetch using SPSiteDataQuery
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"104\" />"; //104=List template ID for "Anouncements"
query.ViewFields = "<FieldRef Name=\"Title\" />"; //Add other fields which you want to get.
query.Query = ""; //you can add your caml query if you want to filter the list items returned
query.Webs = "<Webs Scope=\"SiteCollection\" />"; //Scope is set to site collection to get data from all anouncement lists in that site collection.
DataTable dataTable = oSPWeb.GetSiteData(query);
}
}
}
/// <summary>
/// Creates and initializes an SPSiteDataQuery object for a given scope.
/// </summary>
public SPSiteDataQuery CreateSiteDataQuery(CAML.QueryScope scope)
{
var query = new SPSiteDataQuery
{
Query = QueryXml,
ViewFields = ViewFieldsXml,
Lists = ListsXml
};
// Only use the Webs clause if the scope is different than the default.
if (scope != CAML.QueryScope.WebOnly)
{
query.Webs = CAML.Webs(scope);
}
return(query);
}
/// <summary>
/// Synchronous before event that occurs before an existing item is completely deleted.
/// </summary>
/// <param name="properties">
/// A Microsoft.SharePoint.SPItemEventProperties object that represents properties of the event handler.
/// </param>
public override void ItemDeleting(SPItemEventProperties properties)
{
//props.projectname = properties.ListItem.ID.ToString() + ";#" + properties.ListItem.Title;
//props.web = properties.ListItem.Web;
propStruct props = new propStruct();
SPWeb web = properties.ListItem.Web;
{
SortedList <string, DataTable> lstListDataToDelete = new SortedList <string, DataTable>();
foreach (SPList list in web.Lists)
{
string fieldname = "";
foreach (SPField field in list.Fields)
{
if (field.SchemaXml.Contains("Type=\"Lookup") && field.SchemaXml.ToLower().Contains("{" + properties.ListId.ToString().ToLower() + "}"))
{
fieldname = field.InternalName;
SPSiteDataQuery query = new SPSiteDataQuery();
query.QueryThrottleMode = SPQueryThrottleOption.Override; //Used to set/reset throttling while retrieving records using CAML query.
query.Lists = "<Lists><List ID=\"" + list.ID.ToString() + "\"/></Lists>";
//query.RowLimit = (uint)0;
query.Query = "<Where><Eq><FieldRef Name=\"" + field.InternalName + "\" LookupId=\"True\"/><Value Type=\"Lookup\">" + properties.ListItemId + "</Value></Eq></Where>";
query.ViewFields = "<FieldRef Name=\"Title\"/>";
DataTable dt = web.GetSiteData(query);
if (dt != null && dt.Rows.Count > 0)
{
lstListDataToDelete.Add(list.Title, dt);
}
}
}
if (fieldname != "")
{
}
}
props.lstListDataToDelete = lstListDataToDelete;
props.weburl = web.Url;
}
Thread thrDownload = new Thread(new ParameterizedThreadStart(processDelete));
thrDownload.IsBackground = true;
thrDownload.Start(props);
}
public EstimatesService()
{
query = new SPSiteDataQuery();
query.Lists = "<Lists BaseType='1' />";
query.ViewFields = "<FieldRef Name='SOWStatus' />" +
"<FieldRef Name='EstimateValue' />" +
"<FieldRef Name='VendorName' />";
query.Query = "<Where><And><Eq><FieldRef Name='SOWStatus' /><Value Type='Choice'>Approved</Value></Eq>" +
"<Eq><FieldRef Name='ContentType' /><Value Type='Text'>" + Constants.estimateContentTypeName + "</Value></Eq></And></Where>" +
"<OrderBy>" +
"<FieldRef Name='EstimateValue' />" +
"</OrderBy>";
query.Webs = "<Webs Scope='SiteCollection' />";
}
private static SPSiteDataQuery GetAllApprovedEstimatesFromSiteCollection()
{
var query = new SPSiteDataQuery();
query.Lists = "<Lists BaseType='1' />";
query.ViewFields = "<FieldRef Name='Title' Nullable='TRUE' />" +
"<FieldRef Name='FileRef' Nullable='TRUE' />";
query.Query = "<Where>" +
"<Eq>" +
"<FieldRef Name='SOWStatus' />" +
"<Value Type='Choice'>Approved</Value>" +
"</Eq>" +
"</Where>";
query.Webs = "<Webs Scope='SiteCollection' />";
return(query);
}
protected void btnQuery_Click(object sender, EventArgs e)
{
//Retrieve Items From Multiple Lists
using (SPSite site = new SPSite(SPContext.Current.Web.Url)) ;
using (SPWeb web = site.OpenWeb()) ;
SPSiteDataQuery dataQuery = new SPSiteDataQuery();
dataQuery.Webs = @"<Webs Scope='Recursive'/>";
dataQuery.Lists = @"<Lists ServerTempate='171'/>";
dataQuery.ViewFields = @"<FieldRef Name='Product' /><FieldRef Name='Status' />";
DataTable dt = web.GetSiteData(dataQuery);
DataView dv = new DataView(dt);
grdTasks.DataSource = dv;
grdTasks.DataBind();
}
public DataTable GetItems(string caml, int rowLimit)
{
// Fetch using SPSiteDataQuery
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = string.Format("<Lists ServerTemplate=\"{0}\" />", this.TemplateId);
query.ViewFields = GetViewFields();
query.Webs = "<Webs Scope=\"SiteCollection\" />";
query.Query = caml;
if (rowLimit != 0)
{
query.RowLimit = (uint)rowLimit;
}
var data = Source.Site.RootWeb.GetSiteData(query);
return(data);
}
/// <summary>
/// Queries my work data.
/// </summary>
/// <param name="dataQuery">The data query.</param>
/// <param name="siteUrl">The site URL.</param>
/// <param name="webId">The web id.</param>
/// <param name="userToken">The user token.</param>
/// <returns></returns>
private static DataTable QueryMyWorkData(
SPSiteDataQuery dataQuery,
string siteUrl,
Guid webId,
SPUserToken userToken)
{
Guard.ArgumentIsNotNull(dataQuery, nameof(dataQuery));
DataTable dataTable = null;
using (var spSite = new SPSite(siteUrl, userToken))
{
using (var spWeb = spSite.OpenWeb(webId))
{
SPSecurity.RunWithElevatedPrivileges(() => dataTable = spWeb.GetSiteData(dataQuery));
}
}
return(dataTable);
}
public SPSiteDataQueryExecutor(string lists, string viewFields, string query, string webs, int rowLimit)
{
_dataQuery = new SPSiteDataQuery();
if (string.IsNullOrEmpty(lists) == false)
{
_dataQuery.Lists = lists;
}
if (string.IsNullOrEmpty(viewFields) == false)
{
_dataQuery.ViewFields = viewFields;
}
if (string.IsNullOrEmpty(query) == false)
{
_dataQuery.Query = query;
}
_dataQuery.RowLimit = (uint)rowLimit;
}
private static void ProcessListFromDataTable(SPWeb web, ref string errors, Action <DataTable> processListAction, SPList list, string dquery, string dqFields)
{
var spSiteDataQuery = new SPSiteDataQuery
{
Lists = string.Format("<Lists MaxListLimit='0'><List ID='{0}'/></Lists>", list.ID),
Query = dquery,
ViewFields = dqFields,
QueryThrottleMode = SPQueryThrottleOption.Override
};
try
{
var dataTable = web.GetSiteData(spSiteDataQuery);
processListAction?.Invoke(dataTable);
}
catch (Exception ex)
{
Trace.TraceError("Exception Suppressed {0}", ex);
errors += string.Format("Get Site Data Error: {0}<br>", ex.Message);
}
}
protected void GetData()
{
DataTable dtResults = null;
using (SPWeb Web = SPContext.Current.Site.RootWeb)
{
SPSiteDataQuery Query = new SPSiteDataQuery();
Query.Lists = "<Lists ServerTemplate=\"108\" />";
//"<Lists BaseType=\"1\" />";
//Query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"ID\" /><FieldRef Name=\"Location\" /><FieldRef Name=\"EventDate\" /><FieldRef Name=\"Description\" /><FieldRef Name=\"ContentTypeId\" />";
//content type & property
//Query.Query = "<Where><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>" + ToolsContentType + "</Value></Eq></Where>";
// Query.Webs = "<Webs Scope=\"Recursive\" />";
Query.Webs = "<Webs Scope=\"Recursive\" />";
dtResults = Web.GetSiteData(Query);
//dtResults.Columns.Add("LinkField");
////fixes the links and only uses 150 chars of the PublishingPageContentField
//foreach (DataRow Row in dtResults.Rows)
//{
// SPSite Site = SPContext.Current.Site;
// Guid WebGuid = new Guid(Row["WebId"].ToString());
// using (SPWeb LinkWeb = Site.OpenWeb(WebGuid))
// {
// Guid ListGuid = new Guid(Row["ListId"].ToString());
// SPList List = LinkWeb.Lists[ListGuid];
// Row["LinkField"] = LinkWeb.Url + "/" + List.RootFolder.Url + "/DispForm.aspx?ID=" + Row["ID"].ToString() + "&ContentTypeID=" + Row["ContentTypeId"].ToString() + "&IsDlg=1";
// }
//}
dtResults.TableName = "dtResults";
dtResults.WriteXml("C:\\temp\\events.xml");
}
//DataView dvResults = new DataView(dtResults);
//return FilterSearchData(dvResults);
}
protected DataTable Get(SPSiteDataQuery query) {
return Web.GetSiteData(query);
}
public virtual SPGENRepositoryDataItemCollection FindDataItems(SPWeb web, SPSiteDataQuery query, string[] fieldNames)
{
DataTable dataTable = null;
try
{
dataTable = web.GetSiteData(query);
return new SPGENRepositoryDataItemCollection(dataTable, fieldNames);
}
catch
{
if (dataTable != null)
dataTable.Dispose();
throw;
}
}
private static void DiscussionWebPartTest(string url)
{
try
{
string siteUrl = url;
SPUser CurrentUser = null;
using (SPSite site = new SPSite(url))
{
CurrentUser = site.RootWeb.CurrentUser;
}
//GetData();
//SPSiteDataQuery query = new SPSiteDataQuery();
////query.ViewFields = "<FieldRef Name=\"LinkDiscussionTitle\"><FieldRef Name=\"ID\">";
//query.Lists = "<Lists ServerTemplate=\"108\">";
//query.Webs = "<Webs Scope=\"SiteCollection\" />";
//SPWeb web = SPContext.Current.Site.RootWeb;
//DataTable table = web.GetSiteData(query);
//table.TableName = "Discussions";
//table.WriteXml("c:\\log.txt");
DateTime recentDate = DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy")).AddDays(-30);
List<string> communitiesWebUrl = new List<string>();
List<string> userCommunities = null;
string html = string.Empty;
string webRelUrl = String.Empty;
//html += "Current Web url: " + SPContext.Current.Web.Url + "<br/>";
//html += "Initial current user: "******"<br/>";
SPSecurity.RunWithElevatedPrivileges(delegate()
{
//Rks: This was changed over from using SPContext.Current.Site.Id to using the url because
// The Site object was opening against the default zone and the permissions there were not working
// properly. using the siteurl zone means the objects are being opened in code against the same
// zone that they are seeing through the browser.
using (SPSite site = new SPSite(siteUrl))
{
// Fetch using SPSiteDataQuery
using (SPWeb web = site.OpenWeb())
{
//Distinct webs from SiteDataQuery for responses during the last 7 days.
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions.
//RKS - 2013-09-04 - this was commented out because the standard discussion list does not include the AverageRating field. If this viewfield is not present, the discussion is not returned.
//query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */
query.Webs = "<Webs Scope=\"SiteCollection\" />";
query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(recentDate) + "</Value></Geq></Where>";
DataTable dataTable = web.GetSiteData(query);
foreach (DataRow row in dataTable.Rows)
{
webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl;
if (!communitiesWebUrl.Contains(webRelUrl))
{
communitiesWebUrl.Add(webRelUrl);
}
}
//Distinct webs from SiteDataQuery for responses to my posts. I got this CAML from Chris' code.
// Fetch using SPSiteDataQuery
query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions.
//RKS - 2013-09-04 - this was commented out because the standard discussion list does not include the AverageRating field. If this viewfield is not present, the discussion is not returned.
//query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */
query.Webs = "<Webs Scope=\"SiteCollection\" />";
query.Query = string.Format(@"<Where>
<And>
<Eq>
<FieldRef Name='ContentType' />
<Value Type='Computed'>Message</Value>
</Eq>
<Eq>
<FieldRef Name='Author' LookupId='True' />
<Value Type='Integer'>{0}</Value>
</Eq>
</And>
</Where>", CurrentUser.ID);
dataTable = web.GetSiteData(query);
foreach (DataRow row in dataTable.Rows)
{
webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl;
if (!communitiesWebUrl.Contains(webRelUrl))
{
communitiesWebUrl.Add(webRelUrl);
}
}
}
//html += "using Site url: " + site.Url + "<br/>";
bool discussionExist = false;
foreach (var communityWebUrl in communitiesWebUrl)
{
//html += "> " + communityWebUrl + "<br/>";
using (SPWeb communityWeb = site.OpenWeb(communityWebUrl))
{
//html += ">--- Url..: " + communityWeb.Url + "<br/>";
//html += ">--- Title: " + communityWeb.Title + "<br/>";
// html += ">--- CurrentUserWeb: " + communityWeb.CurrentUser + "<br/>";
if (communityWeb.DoesUserHavePermissions(CurrentUser.LoginName, SPBasePermissions.AddListItems))
{
//html += ">--- Has Permissions to: " + communityWeb.Title + "<br/>";
//html += "<p>" + communityWeb.Title + "</p>";
List<SPList> discussionBoards = GetDiscussionBoards(communityWeb);
foreach (SPList discussionBoard in discussionBoards)
{
discussionExist = true;
html += "<tr><td><div><b>" + discussionBoard.Title + "</b></div><hr/>";
string queryText = GetQueryForCurrentUserReplies(discussionBoard, CurrentUser, recentDate);
SPView view = discussionBoard.DefaultView;
view.Paged = false;
if (queryText != null)
{
html += "<div>Discussions replied by me: </div>";
SPQuery query = new SPQuery(view);
//'query.Query = queryText;
query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */
query.Query = queryText;
//RKS: I removed this line of code because Replies are not Dicussions so the queryText was more appropriate
//query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(recentDate) + "</Value></Geq></Where>";
query.RowLimit = 3;
// query.ViewFields = "<OrderBy><FieldRef Name='Modified' Ascending='False' /></OrderBy><Fieldref Name='Subject'/><Fieldref Name='Body'/><Fieldref Name='AverageRating'/>";
query.ViewFields = "<FieldRef Name='LinkDiscussionTitle'/><FieldRef Name='DiscussionLastUpdated'/><FieldRef Name='ItemChildCount'/><FieldRef Name='AverageRating'/>";
query.ViewFieldsOnly = true;
string discussionHtml = discussionBoard.RenderAsHtml(query);
html += discussionHtml;
}
string recentForumsQuery = GetQueryForRecentPosts(discussionBoard, recentDate);
if (recentForumsQuery != null)
{
html += "<div>Recent Discussions: </div>";
SPQuery query = new SPQuery(view);
query.Query = recentForumsQuery;
query.ViewFields = "<FieldRef Name='LinkDiscussionTitle'/><FieldRef Name='DiscussionLastUpdated'/>";//<FieldRef Name='ItemChildCount'/><FieldRef Name='AverageRating'/>";
query.ViewFieldsOnly = true;
query.RowLimit = 3;
string discussionHtml = discussionBoard.RenderAsHtml(query);
html += discussionHtml;
}
html += "</td></tr>";
}
}
//else
//{
// html += ">--- No Permissions to: " + communityWeb.Title + "<br/>";
//}
}
}
//removed pagination and context menu
// Commented out for console app
//discussionBoardsDiv.InnerHtml = "<table id='tblDiscussions'><tbody>" + html.Replace(@"""ms-bottompaging""", @"""ms-bottompaging"" style='display:none;'").Replace("OnItem(this)", "") + "</tbody></table>";
if (discussionExist == false)
ShowNoRecords();
}
});
}
catch (Exception ex)
{
ConsoleOut(ex.ToString());
// Commented out for console app
//Controls.Add(new LiteralControl(ex.Message));
}
}
private void DataBindMyTasks()
{
var dtBind = EnsureDataTableRow();
var web = SPContext.Current.Site.RootWeb;
var query = new SPSiteDataQuery();
var swhere = @"<Where>
<And>
<And>
<Eq>
<FieldRef Name=""AssignedTo"" LookupId=""TRUE""/>
<Value Type=""Integer"">{0}</Value>
</Eq>
<Neq>
<FieldRef Name=""Status"" />
<Value Type=""Text"">{1}</Value>
</Neq>
</And>
<Neq>
<FieldRef Name='ID' />
<Value Type='Counter'>{2}</Value>
</Neq>
</And>
</Where>";
var scompleted = SPUtility.GetLocalizedString("$Resources:core,Tasks_Completed;", "core", web.Language);
string currTaskId = Request.QueryString["TaskId"].IsNullOrWhitespace() ? "0" : Request.QueryString["TaskId"];
swhere = String.Format(swhere, web.CurrentUser.ID, scompleted, currTaskId);
//var sorderby = "<OrderBy><FieldRef ID=\"" + SPBuiltInFieldId.WorkflowName.ToString("B") + "\"/><FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\" Ascending=\"FALSE\"/></OrderBy>";
var sorderby = "<OrderBy><FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\" Ascending=\"FALSE\"/></OrderBy>";
query.Query = swhere + sorderby;
query.Lists = "<Lists ServerTemplate=\"107\"/>";
query.ViewFields = "<FieldRef ID=\"" + SPBuiltInFieldId.Title.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.ID.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskDueDate.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.UniqueId.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Completed.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.PercentComplete.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskStatus.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.WorkflowLink.ToString("B") + "\" Nullable=\"TRUE\" Type=\"URL\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FileRef.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.ID.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FSObjType.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.WorkflowName.ToString("B") + "\"/>";
//query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_By.ToString("B") + "\"/>";
query.Webs = "<Webs Scope=\"Recursive\" />";
var createdDateFieldId = SPBuiltInFieldId.Created_x0020_Date.ToString("B");
var uIdField = SPBuiltInFieldId.UniqueId.ToString("B");
var listField = SPBuiltInFieldId.FileRef.ToString("B");
var t = web.GetSiteData(query);
if (t != null && t.Rows.Count > 0)
{
t.DefaultView.Sort = createdDateFieldId + " Desc";
}
var sep = new string[] { ";#" };
t.Columns.Add("WorkFlowUrl");
var mxw = new Hashtable();
var workflowcenter = SPContext.Current.Site.OpenWeb("workflowcenter");
var modules = workflowcenter.Lists["Modules"];
foreach (SPListItem item in modules.Items)
{
var workflownames = (item["WorkflowNames"].AsString() + "").Replace("<p>", "").Replace("</p>", "");
if (!string.IsNullOrEmpty(workflownames.Trim()))
{
foreach (var workflowname in workflownames.Split(';'))
{
if (!string.IsNullOrEmpty(workflowname.Trim()) && !mxw.Contains(workflowname.Trim()))
{
mxw.Add(workflowname.Trim(), item["Title"] + "");
}
}
}
}
DataRow dr = null;
foreach (DataRow row in t.Rows)
{
dr = dtBind.Rows.Add();
var createdDate = "" + row[createdDateFieldId];
var tempArr = createdDate.Split(sep, StringSplitOptions.None);
if (tempArr.Length > 1)
row[createdDateFieldId] = Convert.ToDateTime(tempArr[1]).ToString("yyyy-MM-dd");
row[uIdField] = row[uIdField].ToString().Split(sep, StringSplitOptions.None)[1];
//35;#WorkFlowCenter/Lists/Tasks/35_.000
var workflowUrl = row[listField].ToString().Split(sep, StringSplitOptions.None)[1];
var index = workflowUrl.LastIndexOf(@"/");
workflowUrl = SPContext.Current.Site.RootWeb.Url + "/" + workflowUrl.Remove(index) + "/DispForm.aspx?ID=" + row[SPBuiltInFieldId.ID.ToString("B")];
row["WorkFlowUrl"] = workflowUrl;
dr["TaskId"] = row[SPBuiltInFieldId.ID.ToString("B")];
dr["TaskTitle"] = row[SPBuiltInFieldId.Title.ToString("B")];
if (IsNeedDelete(row[SPBuiltInFieldId.Title.ToString("B")].AsString()))
{
dr["MenuTemplateIdField"] = "MenuList";
}
else
{
dr["MenuTemplateIdField"] = "MenuTemplate1";
}
dr["StartTime"] = row[createdDateFieldId];
// dr["CreatedBy"] = row[SPBuiltInFieldId.Created_x0020_By.ToString("B")];
dr["WorkflowName"] = row[SPBuiltInFieldId.WorkflowName.ToString("B")];
dr["WorkflowUrl"] = workflowUrl + "&Source=" + this.Page.Request.RawUrl;
if (mxw.Contains(row[SPBuiltInFieldId.WorkflowName.ToString("B")]))
{
dr["ModuleTitle"] = mxw[row[SPBuiltInFieldId.WorkflowName.ToString("B")]];
}
else
{
dr["ModuleTitle"] = row[SPBuiltInFieldId.WorkflowName.ToString("B")];
}
dr["Status"] = row[SPBuiltInFieldId.TaskStatus.ToString("B")];
}
//this.gvList.DataSource = dtBind;
this.gvList.DataSource = dtBind.AsEnumerable().OrderBy(d => d["ModuleTitle"]).AsDataView();
this.gvList.DataBind();
}
private static string EnumerateDistinctWebsWithDiscussions(SPSite site)
{
string html2 = String.Empty;
using (SPWeb web = site.RootWeb)
{
//Distinct webs from SiteDataQuery for responses during the last 7 days.
// Fetch using SPSiteDataQuery
string today = DateTime.Now.ToString("MM/dd/yyyy");
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions.
query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; //Title is LastName column
query.Webs = "<Webs Scope=\"SiteCollection\" />";
query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Parse(today).AddDays(-30)) + "</Value></Geq></Where>";
DataTable dataTable = web.GetSiteData(query);
//48
List<string> communitiesWebUrl = new List<string>();
string webRelUrl = String.Empty;
foreach (DataRow row in dataTable.Rows)
{
webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl;
if (!communitiesWebUrl.Contains(webRelUrl))
{
communitiesWebUrl.Add(webRelUrl);
html2 += webRelUrl + "\n";
}
}
SPUser currentUser = web.CurrentUser;
//Distinct webs from SiteDataQuery for responses during the last 7 days.
// Fetch using SPSiteDataQuery
SPSiteDataQuery query2 = new SPSiteDataQuery();
query2.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions.
query2.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; // Title is LastName column
query2.Webs = "<Webs Scope=\"SiteCollection\" />";
query2.Query = string.Format(@"<Where>
<And>
<Eq>
<FieldRef Name='ContentType' />
<Value Type='Computed'>Message</Value>
</Eq>
<Eq>
<FieldRef Name='Author' LookupId='True' />
<Value Type='Integer'>{0}</Value>
</Eq>
</And>
</Where>", currentUser.ID);
DataTable dataTable2 = web.GetSiteData(query2);
//48
List<string> communitiesWebUrl2 = new List<string>();
string webRelUrl2 = String.Empty;
foreach (DataRow row in dataTable.Rows)
{
webRelUrl2 = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl;
if (!communitiesWebUrl2.Contains(webRelUrl))
{
communitiesWebUrl2.Add(webRelUrl);
html2 += webRelUrl + "\n";
}
}
}
return html2;
}
DataTable GetRootTasks()
{
SPWeb web = SPContext.Current.Site.RootWeb;
SPSiteDataQuery query = new SPSiteDataQuery();
string swhere = @"<Where><And>
<Eq>
<FieldRef Name=""AssignedTo"" LookupId=""TRUE""/>
<Value Type=""Integer"">{0}</Value>
</Eq>
<Neq>
<FieldRef Name=""Status"" />
<Value Type=""Text"">{1}</Value>
</Neq>
</And></Where>";
string scompleted = SPUtility.GetLocalizedString("$Resources:core,Tasks_Completed;", "core", web.Language);
swhere = String.Format(swhere, web.CurrentUser.ID, scompleted);
string sorderby = "<OrderBy><FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\"/></OrderBy>";
query.Query = swhere + sorderby;
query.Lists = "<Lists ServerTemplate=\"107\"/>";
query.ViewFields = "<FieldRef ID=\"" + SPBuiltInFieldId.Title.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskDueDate.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.UniqueId.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Completed.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.PercentComplete.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.TaskStatus.ToString("B") + "\" Nullable=\"TRUE\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.WorkflowLink.ToString("B") + "\" Nullable=\"TRUE\" Type=\"URL\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FileRef.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.ID.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.Created_x0020_Date.ToString("B") + "\"/>";
query.ViewFields = query.ViewFields + "<FieldRef ID=\"" + SPBuiltInFieldId.FSObjType.ToString("B") + "\"/>";
query.Webs = "<Webs Scope=\"Recursive\" />";
string createdDateFieldId = SPBuiltInFieldId.Created_x0020_Date.ToString("B");
string uIdField = SPBuiltInFieldId.UniqueId.ToString("B");
string listField = SPBuiltInFieldId.FileRef.ToString("B");
query.RowLimit = 5;
DataTable t = web.GetSiteData(query);
if (t != null && t.Rows.Count > 0)
{
t.DefaultView.Sort = createdDateFieldId + " Desc";
}
string[] sep = new string[] { ";#" };
t.Columns.Add("WorkFlowUrl");
foreach (DataRow row in t.Rows)
{
string createdDate = "" + row[createdDateFieldId];
string[] tempArr = createdDate.Split(sep, StringSplitOptions.None);
if (tempArr.Length > 1)
row[createdDateFieldId] = Convert.ToDateTime(tempArr[1]).ToString("yyyy-MM-dd");
row[uIdField] = row[uIdField].ToString().Split(sep, StringSplitOptions.None)[1];
//35;#WorkFlowCenter/Lists/Tasks/35_.000
string workflowUrl = row[listField].ToString().Split(sep, StringSplitOptions.None)[1];
int index = workflowUrl.LastIndexOf(@"/");
workflowUrl = SPContext.Current.Site.RootWeb.Url + "/DispForm.aspx?ID=" + row[SPBuiltInFieldId.ID.ToString("B")];
row["WorkFlowUrl"] = workflowUrl + "&Source=" + this.Page.Request.RawUrl;
}
return t;
}
private void GetAllActionsOwed(string userID)
{
List<ActionOwed> actionsOwed = new List<ActionOwed>();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
string[] actionsOwedSites = sites.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (string site in actionsOwedSites)
{
using (SPSite emisSite = new SPSite(site))
{
using (SPWeb emisRootWeb = emisSite.OpenWeb())
{
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"107\" />";
query.ViewFields = "<FieldRef Name=\"Title\" />" +
"<FieldRef Name=\"ID\" />" +
"<FieldRef Name=\"AssignedTo\" Nullable=\"TRUE\" Type=\"User\" />" +
"<FieldRef Name=\"Status\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"DueDate\" Nullable=\"TRUE\" Type=\"DateTime\" />" +
"<FieldRef Name=\"IsDelegated\" Nullable=\"TRUE\" Type=\"Boolean\" />" +
"<FieldRef Name=\"OriginalAssignee\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"ActionOwed\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"TaskSource\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"FileRef\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"Related Content\" Nullable=\"TRUE\" Type=\"Text\" />";
query.Query =
"<Where>" +
"<And>" +
"<Eq>" +
"<FieldRef Name='AssignedTo' />" +
" <Value Type='User'>" + userID + "</Value>" +
"</Eq>" +
"<Neq>" +
"<FieldRef Name='Status' />" +
"<Value Type='Choice'>Completed</Value>" +
"</Neq>" +
"</And>" +
"</Where>" +
"<OrderBy>" +
"<FieldRef Name=\"DueDate\" />" +
"</OrderBy>";
query.Webs = "<Webs Scope=\"SiteCollection\" />";
Microsoft.Office.Server.Diagnostics.PortalLog.LogString("ActionsOwed WebPart: About to get data from all subsites for {0}", site);
Microsoft.Office.Server.Diagnostics.PortalLog.LogString("ActionsOwed WebPart: SPSiteDataQuery = {0}", query.Query);
DataTable dt = emisRootWeb.GetSiteData(query);
Microsoft.Office.Server.Diagnostics.PortalLog.LogString("ActionsOwed WebPart: Got data from all subsites for {0}", site);
foreach (DataRow row in dt.Rows)
{
ActionOwed action = new ActionOwed()
{
TaskID = row["ID"].ToString(),
Title = row["Title"].ToString(),
DueDate = DateTime.Parse(row["DueDate"].ToString()),
Status = row["Status"].ToString(),
OriginalAssignee = row["OriginalAssignee"].ToString(),
Action = row["ActionOwed"].ToString(),
Source = row["TaskSource"].ToString(),
FileRef = row["FileRef"].ToString(),
TaskSite = site
};
if (string.IsNullOrEmpty(row["IsDelegated"].ToString()))
{
action.IsDelegated = false;
}
else if (row["IsDelegated"].ToString() == "1")
{
action.IsDelegated = true;
}
else if (bool.Parse(row["IsDelegated"].ToString()))
{
action.IsDelegated = true;
}
string taskRelativeLink = row["FileRef"].ToString().Substring(row["FileRef"].ToString().IndexOf("#") + 1, (row["FileRef"].ToString().Length - row["FileRef"].ToString().IndexOf("#") - 1));
action.TaskLink = taskRelativeLink;
action.AssignedTo = row["AssignedTo"].ToString().Substring(row["AssignedTo"].ToString().IndexOf("#") + 1, (row["AssignedTo"].ToString().Length - row["AssignedTo"].ToString().IndexOf("#") - 1));
actionsOwed.Add(action);
}
}
}
}
});
grdActionsOwed.DataSource = actionsOwed;
grdActionsOwed.DataBind();
}
//This console application searches for all entries in all Tasks folders
//that are assigned to the current user. Try creating tasks in several sites
//within the site collection, assign them to your own account then run this
//project. All tasks should be returned in a single operation
static void Main(string[] args)
{
//Start by formulating the query in CAML
SPSiteDataQuery query = new SPSiteDataQuery();
//Get IDs for the SharePoint built-in fields we want to use
string assignedToID = SPBuiltInFieldId.AssignedTo.ToString("B");
string taskDueDateID = SPBuiltInFieldId.TaskDueDate.ToString("B");
string titleID = SPBuiltInFieldId.Title.ToString("B");
string taskStatusID = SPBuiltInFieldId.TaskStatus.ToString("B");
string percentCompleteID = SPBuiltInFieldId.PercentComplete.ToString("B");
//This is the selection creterion
string whereClause = "<Where><Eq><FieldRef ID='" + assignedToID + "' />"
+ "<Value Type='Integer'><UserID/></Value>"
+ "</Eq></Where>";
//This is the sort order
string orderByClause = "<OrderBy><FieldRef ID='" + taskDueDateID + "' /></OrderBy>";
//Set the query CAML
query.Query = whereClause + orderByClause;
//We will query all the Tasks lists
query.Lists = "<Lists ServerTemplate='107' />";
//Define the view fields in the result set
string viewFields = "<FieldRef ID='" + titleID + "' />"
+ "<FieldRef ID='" + taskDueDateID + "' Nullable='TRUE' />"
+ "<FieldRef ID='" + taskStatusID + "' Nullable='TRUE' />"
+ "<FieldRef ID='" + percentCompleteID + "' Nullable='TRUE' />";
query.ViewFields = viewFields;
//Query all the SPWebs in this SPSite
query.Webs = "<Webs Scope='SiteCollection'>";
//Get the SPSite and SPWeb, ensuring correct disposal
//Replace the URL with your own site collection
using (SPSite site = new SPSite("http://intranet.contoso.com/"))
{
using (SPWeb web = site.OpenWeb())
{
//Run the query
DataTable resultsTable = web.GetSiteData(query);
//Print a heading line
Console.WriteLine("{0, -10} {1, -30} {2, -20} {3}", "Date Due", "Task", "Status", "% Complete");
//Loop through the results and print them
foreach (DataRow currentRow in resultsTable.Rows)
{
//Extract various values
string dueDate = (string)currentRow[taskDueDateID];
string task = (string)currentRow[titleID];
string status = (string)currentRow[taskStatusID];
string percentComplete = (string)currentRow[percentCompleteID];
//Format the due date
DateTime dueDateTime;
bool hasDate = DateTime.TryParse(dueDate, out dueDateTime);
if (hasDate)
{
dueDate = dueDateTime.ToShortDateString();
}
else
{
dueDate = String.Empty;
}
//Format the percent complete string
decimal pctDec;
bool hasValue = decimal.TryParse(percentComplete, out pctDec);
if (hasValue)
{
percentComplete = pctDec.ToString("P0");
}
else
{
percentComplete = "0%";
}
//Print a line for this row
Console.WriteLine("{0, -10} {1, -30} {2, -20} {3, 10}", dueDate, task, status, percentComplete);
}
}
}
//Wait for the user to press a key before closing
Console.ReadKey();
}
public static DataTable GetNewsRecordItems(string query, uint newsNumber, string[] listNames)
{
DataTable allItems = null;
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (var site = new SPSite(SPContext.Current.Web.Site.ID))
{
using (var web = site.OpenWeb(SPContext.Current.Web.ID))
{
try
{
var siteDataQuery = new SPSiteDataQuery();
var sb = new StringBuilder();
// Lists
sb.Append("<Lists>");
foreach (var listName in listNames)
{
SPList list = Utilities.GetListFromUrl(web, listName);
if (list != null)
{
sb.AppendFormat("<List ID='{0}' />", list.ID.ToString("D"));
}
}
sb.Append("</Lists>");
siteDataQuery.Lists = sb.ToString();
//siteDataQuery.Lists = "<Lists ServerTemplate='101'/>";
siteDataQuery.Webs = "<Webs Scope='SiteCollection' />";
siteDataQuery.Query = query;
siteDataQuery.ViewFields = ""
+ "<ListProperty Name='ListId' Nullable='TRUE' />"
+ "<FieldRef Name='ArticleStartDates' Nullable='TRUE' />"
+ "<FieldRef Name='Title' Nullable='TRUE' />"
+ "<FieldRef Name='ID' Nullable='TRUE' />"
+ "<FieldRef Name='Thumbnail' Nullable='TRUE' />"
+ "<FieldRef Name='CategoryName' Nullable='TRUE' />"
+ "<FieldRef Name='ShortContent' Nullable='TRUE' /><FieldRef Name='Status' Nullable='TRUE' />"
+ "<FieldRef Name='FocusNews' Nullable='TRUE' /><FieldRef Name='ViewsCount' Nullable='TRUE' />"
+ "<FieldRef Name='PublishingPageImage' Nullable='TRUE' /><FieldRef Name='PublishingPageContent' Nullable='TRUE' />"
+ "<FieldRef Name='LatestNewsOnHomePage' Nullable='TRUE' /><FieldRef Name='_ModerationStatus' Nullable='TRUE' />";
siteDataQuery.RowLimit = newsNumber;
allItems = web.GetSiteData(siteDataQuery);
}
catch (Exception ex)
{
allItems = null;
}
}
}
});
return allItems;
}
private static bool SpExceptionOccured(
SPSiteDataQuery dataQuery,
SPWeb spWeb,
SPSite spSite,
string listIds,
IList <EventWaitHandle> eventWaitHandles,
string selectedList,
object locker,
IList <DataTable> dataTables,
string theSelectedList)
{
Guard.ArgumentIsNotNull(dataQuery, nameof(dataQuery));
Guard.ArgumentIsNotNull(spWeb, nameof(spWeb));
Guard.ArgumentIsNotNull(spSite, nameof(spSite));
Guard.ArgumentIsNotNull(eventWaitHandles, nameof(eventWaitHandles));
Guard.ArgumentIsNotNull(dataTables, nameof(dataTables));
var spExceptionOccured = false;
dataQuery.Lists = $"<Lists MaxListLimit='0'>{listIds}</Lists>";
var siteUrl = (string)spSite.Url.Clone();
var webId = new Guid(spWeb.ID.ToString());
var spUserToken = spWeb.CurrentUser.UserToken;
var spSiteDataQuery = new SPSiteDataQuery
{
Webs = dataQuery.Webs,
Query = dataQuery.Query,
QueryThrottleMode =
dataQuery.QueryThrottleMode,
ViewFields = dataQuery.ViewFields,
Lists = dataQuery.Lists
};
var eventWaitHandle = new EventWaitHandle(false, EventResetMode.ManualReset);
eventWaitHandles.Add(eventWaitHandle);
var list = selectedList;
var thread = new Thread(
() =>
{
try
{
var dataTable = QueryMyWorkData(spSiteDataQuery, siteUrl, webId, spUserToken);
dataTable.TableName = list;
lock (locker)
{
dataTables.Add(dataTable);
}
}
catch (SPException exception)
{
Trace.WriteLine(exception);
spExceptionOccured = true;
}
eventWaitHandle.Set();
})
{
Name = theSelectedList, IsBackground = true
};
thread.Start();
return(spExceptionOccured);
}
// Specific files according to user criteria in all sites
public List<string> _getSpecificDocsInSiteCollection()
{
List<string> FileRefs = new List<string>();
SPSiteDataQuery DocFilesQuery;
using (SPSite site = new SPSite(SiteUrl))
{
//using (SPWeb web = site.OpenWeb("/"))
using (SPWeb web = site.OpenWeb())
{
user = null;
if (UserLoginName != "")
user = web.EnsureUser(UserLoginName);// only when specific user
DocFilesQuery = new SPSiteDataQuery();
DocFilesQuery.Lists = "<Lists BaseType='1' /><Lists Hidden = \"TRUE\" />";// generic list // better!
//query.Lists = "<Lists ServerTemplate='101' /><Lists Hidden = \"TRUE\" />";// doc lib
DocFilesQuery.Query = CriteriaQuery();
// Select only needed columns: file reference
DocFilesQuery.ViewFields = "<FieldRef Name='FileRef' />";
// Search in all webs of the site collection
DocFilesQuery.Webs = "<Webs Scope='SiteCollection' />";
// constructring file url from GetSiteDataQuery
DataRowCollection filerows = web.GetSiteData(DocFilesQuery).Rows;
foreach (DataRow row in filerows)
{
// add relativeUrl into FileRef collection
FileRefs.Add(getRelativeFileRef(row["FileRef"].ToString()));
}
// specific attachments
SPListCollection listcoll = web.Lists;
foreach (SPList list in listcoll)
{
SPFolderCollection foldercoll = list.RootFolder.SubFolders;
foreach (SPFolder folder in foldercoll)
{
if (folder.Name == "Attachments")
{
SPFolderCollection attachmentFolders = folder.SubFolders;
foreach (SPFolder itemFolder in attachmentFolders)
{
SPFileCollection files = itemFolder.Files;
foreach (SPFile file in files)
{
if (CriteriaApply(file))
FileRefs.Add(file.ServerRelativeUrl);
}
}
break;
}
}
}
}
}
return (FileRefs);
}
public override void populateGroups(string query, SortedList arrGTemp, SPWeb curWeb)
{
SPSiteDataQuery dq = new SPSiteDataQuery();
dq.ViewFields = "<FieldRef Name='Title' Nullable='TRUE'/>";
dq.Webs = "<Webs Scope='Recursive'/>";
dq.Lists = "<Lists ServerTemplate='10701'/>";
dq.Query = "<Where><Eq><FieldRef Name=\"ProjectManagers\" /><Value Type=\"User\"><UserID/></Value></Eq></Where>";
DataTable dtData = curWeb.GetSiteData(dq);
DataTable dtItems = new DataTable();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
using (cn = new SqlConnection(EPMLiveCore.CoreFunctions.getConnectionString(curWeb.Site.WebApplication.Id)))
{
cn.Open();
using (SqlCommand cmd = new SqlCommand("select ts_item_uid,columnname,columnvalue from vwTSItemMeta where period_id=@period_id and site_uid=@siteid", cn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@period_id", period);
cmd.Parameters.AddWithValue("@siteid", curWeb.Site.ID);
dsTimesheetMeta = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dsTimesheetMeta);
}
using (SqlCommand cmd = new SqlCommand("select web_uid, list_uid,item_id,username,resourcename,project,title,list_uid from vwTSTasks where web_uid=@webuid and period_id=@period_id and totalhours > 0", cn))
{
cmd.Parameters.AddWithValue("@webuid", curWeb.ID.ToString());
cmd.Parameters.AddWithValue("@period_id", period);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dtFilteredRecords = new DataTable();
foreach (DataRow drProjects in dtData.Rows)
{
DataView dv = new DataView(ds.Tables[0]);
string filterquery = string.Format("(project='{0}' or (title='{0}' and list_uid='{1}'))", drProjects["Title"].ToString(), drProjects["ListId"].ToString());
dv.RowFilter = filterquery;
if (dv.Count > 0)
{
dtFilteredRecords.Merge(dv.ToTable(), false);
}
}
dtItems.Merge(dtFilteredRecords, false);
}
using (SqlCommand cmd = new SqlCommand("select title,project,ts_uid,web_uid,list_uid,item_id,ts_item_uid,approval_status,resourcename,username from vwTSTasks where period_id=@period_id and site_uid=@siteid order by project", cn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@period_id", period);
cmd.Parameters.AddWithValue("@siteid", curWeb.Site.ID);
dsTimesheetTasks = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dsTimesheetTasks);
}
}
}
catch { }
});
Guid webGuid = new Guid();
Guid listGuid = new Guid();
SPWeb iWeb = null;
SPList iList = null;
foreach (DataRow dr in dtItems.Rows)
{
try
{
Guid wGuid = new Guid(dr[0].ToString());
Guid lGuid = new Guid(dr[1].ToString());
if (webGuid != wGuid)
{
if (iWeb != null)
{
iWeb.Close();
iWeb = site.OpenWeb(wGuid);
}
else
{
iWeb = site.OpenWeb(wGuid);
}
webGuid = iWeb.ID;
}
if (listGuid != lGuid)
{
iList = iWeb.Lists[lGuid];
listGuid = iList.ID;
}
SPListItem li = iList.GetItemById(int.Parse(dr[2].ToString()));
addTSItem(li, arrGTemp, dr[3].ToString(), dr[4].ToString());
}
catch { }
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
int errorIndex = 0;
try
{
var siteContext = SPContext.Current.Site;
//var impersonationUser = siteContext.RootWeb.AllUsers[0];
var site = siteContext;
var query = new SPSiteDataQuery
{
Query = "",
ViewFields =
"<FieldRef Name=\"LinkFilename\"/>" +
"<FieldRef Name=\"Created\"/>" +
"<FieldRef Name=\"Modified\"/>" +
"<FieldRef Name=\"Editor\"/>" +
"<FieldRef Name=\"FileLeafRef\"/>",
Lists =
"<Lists ServerTemplate=\"101\"/>",
RowLimit = 500,
Webs =
"<Webs Scope=\"SiteCollection\"/>"
};
//var productiondate = new DateTime(2012, 09, 12);
//string proddatestring =
// SPUtility.CreateISO8601DateTimeFromSystemDateTime(
// productiondate.ToUniversalTime());
//var date = Convert.ToDateTime(proddatestring);
//date.AddMonths(-1).ToUniversalTime();
string convertedTime = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddMonths(-1).ToUniversalTime());
query.Query =
@"<Where>
<And>
<Geq>
<FieldRef Name='Modified' />
<Value IncludeTimeValue='TRUE' Type='DateTime'>" +
convertedTime +
@"</Value>
</Geq>
<Eq>
<FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value>
</Eq>
</And>
</Where>";
var resultTable = new DataTable();
resultTable.Columns.Add("DocLink");
resultTable.Columns.Add("Docname");
resultTable.Columns.Add("Sitename");
resultTable.Columns.Add("WebURL");
errorIndex = 1;
foreach (DataRow row in site.RootWeb.GetSiteData(query).Rows)
{
try
{
string filename = row["LinkFilename"].ToString();
string extension = Path.GetExtension(filename);
if (extension != null)
switch (extension.ToLower())
{
case ".jpg":
case ".xsl":
case ".css":
case ".png":
case ".xaml":
case ".gif":
case ".js":
//case ".dotm":
{
continue;
}
default:
{
var newRow = resultTable.NewRow();
using (SPWeb web = site.OpenWeb(new Guid(row["WebId"].ToString())))
{
var item =
web.Lists[new Guid(row["listId"].ToString())].GetItemById(
Convert.ToInt32(row["ID"]));
var url = (string) item[SPBuiltInFieldId.EncodedAbsUrl];
newRow["DocLink"] = Uri.EscapeUriString(url);
newRow["Docname"] = filename;
newRow["Sitename"] = web.Name;
newRow["WebURL"] = web.Url;
if (string.IsNullOrEmpty(newRow["Sitename"].ToString()))
newRow["Sitename"] = "Root Site";
resultTable.Rows.Add(newRow);
break;
}
}
}
ViewState["Result"] = resultTable;
}
catch (Exception ex)
{
Label1.Text += ex.StackTrace;
}
}
}
catch (Exception ex)
{
Label1.Text = "<br />" + ex.Message;
// Label1.Text += ex.StackTrace;
Label1.Text += "<br />" + "Error message is" + errorIndex;
}
}
}
protected DataTable Get(SPSiteDataQuery query, int startRow, int maxRows) {
var data = Web.GetSiteData(query);
if (data.Rows.Count > 0) {
return data.Select().Skip(startRow).Take(maxRows).CopyToDataTable();
} else {
return data;
}
}
//public string GetLatestMessageNews(DateTime beforetime, out string nextBeforeTime, bool publicmessage)
public static string GetLatestMessageNews(DateTime beforetime, bool publicmessage)
{
using (new SPMonitoredScope("Weixin.GetLatestMessageNews", 5000))
{
//Guid siteid = SPContext.Current.Site.ID;
//Guid webid = SPContext.Current.Web.ID;
Guid siteid = new Guid("fbf5935b-6717-4036-8bfc-856c1ef075a7");
Guid webid = new Guid("3acebb12-b9c8-4a00-94f7-4d779743e805");
SPUserToken t = null;
SPSecurity.RunWithElevatedPrivileges(delegate() { using (SPSite s = new SPSite(siteid)) { using (SPWeb w = s.OpenWeb(webid)) { t = w.GetUserToken("i:0#.f|fbamember|233173287"); } } });
StringBuilder ret = new StringBuilder();
//using (SPSite site = new SPSite(siteid, SPFBAUser.usertoken))
using (SPSite site = new SPSite(siteid, t))
{
using (SPWeb web = site.OpenWeb(webid))
{
SPSiteDataQuery query = new SPSiteDataQuery();
//query.Lists = string.Concat("<Lists><List ID = '", web.GetList(publicmessage ? PublicMessageListUrl : PrivateMessageListUrl).ID.ToString(), "' /><List ID = '", web.GetList(ImageLibUrl).ID.ToString(), "' /></Lists>");
//query.Webs = "<Webs Scope=\"SiteCollection\"/>";
query.Lists = string.Concat("<Lists><List ID = '", web.GetList(publicmessage ? "/sites/public/lists/list/" : "/sites/public/lists/Private%20Message/").ID.ToString(), "' /><List ID = '", web.GetList("/sites/public/Images1/").ID.ToString(), "' /></Lists>");
query.ViewFields = @"<FieldRef Name=""Author"" Nullable=""TRUE"" /><FieldRef Name=""ItemChildCount"" Nullable=""TRUE"" /><FieldRef Name=""Modified""/><FieldRef Name=""Title"" Nullable=""TRUE"" />";
query.ViewFields += @"<FieldRef Name=""WeChat61"" Nullable=""TRUE""/>";
query.RowLimit = 5;
query.Query = "<OrderBy><FieldRef Name=\"Modified\" Ascending=\"FALSE\" /></OrderBy><Where>"
+ "<And>"
+ "<Or>"
// + "<And><IsNotNull><FieldRef Name=\"WeChatPicUrl\" Nullable=\"TRUE\"/></IsNotNull>"
+ "<IsNotNull><FieldRef Name=\"WeChat61\" Nullable=\"TRUE\"/></IsNotNull>"
// + (publicmessage? string.Concat("<Eq>< FieldRef Name = \"FileDirRef\" ></FieldRef ><Value Type = \"Text\" >",ImageLibUrl,"</Value></Eq>"): string.Concat("<Contains>< FieldRef Name = \"FileDirRef\" ></FieldRef ><Value Type = \"Text\" >", SPFBAUserName, "</Value><Contains>"))
// + (publicmessage ? string.Concat("<Eq><FieldRef Name = \"FileDirRef\" /><Value Type = \"Text\" >", "/sites/public/Images1/", "</Value></Eq>") : string.Concat("<Contains>< FieldRef Name = \"FileDirRef\" /><Value Type = \"Text\" >", "233173287", "</Value></Contains>"))
// + "</And>"
+ "<BeginsWith><FieldRef Name = \"ContentTypeId\" /><Value Type = \"ContentTypeId\" >0x012002</Value></BeginsWith>"
+ "</Or>"
+ "<Lt><FieldRef Name='Created'/><Value IncludeTimeValue ='TRUE' Type ='DateTime'>"
+ SPUtility.CreateISO8601DateTimeFromSystemDateTime(beforetime)
+ "</Value></Lt>"
+ "</And></Where>";
DataTable results = web.GetSiteData(query);
if (results != null && results.Rows.Count > 0)
{
//var responseMessageNews = CreateResponseMessage<ResponseMessageNews>();
//Article a = new Article();
////string prevAuthor = string.Empty;
//int i = 0;
//StringBuilder ret = new StringBuilder();
//foreach (DataRow r in results.Rows)
//{
// string author = r["Author"] == null ? string.Empty : r["Author"].ToString().Trim();
// //SPFieldUserValue au = author.Equals(string.Empty) ? null :
// // (item.Fields.GetFieldByInternalName("Author").GetFieldValue(item["Author"].ToString()) as SPFieldUserValue);
// //string u = au == null ? string.Empty
// // : (au.User == null ? string.Empty : au.User.Name);
// if (string.IsNullOrEmpty((r["WeChatPicUrl"] ?? string.Empty).ToString()))
// {
// ret.AppendLine(string.Concat(author, ":", r["Title"]));
// ret.AppendLine(string.Concat(" 修改时间", r["Modified"].ToString()));
// }
//}
//responseMessageNews.Articles.Add(a);
string lastTime = string.Empty;
foreach (DataRow item in results.Rows)
{
string author = item["Author"] == null ? string.Empty : item["Author"].ToString().Trim();
//SPFieldUserValue au = author.Equals(string.Empty) ? null :
// (item.Fields.GetFieldByInternalName("Author").GetFieldValue(item["Author"].ToString()) as SPFieldUserValue);
//string u = au == null ? string.Empty
// : (au.User == null ? string.Empty : au.User.Name);
if (item["WeChat61"] == null)
{
ret.AppendLine(string.Concat("主题:", item["Title"]));
ret.AppendLine(string.Concat("作者:", author));
ret.AppendLine(string.Concat("修改时间", item["Modified"].ToString()));
ret.AppendLine(string.Concat("回复数:", item["ItemChildCount"]));
}
else
{
ret.AppendLine(string.Concat("作者:", author));
ret.AppendLine(string.Concat("修改时间", item["Modified"].ToString()));
ret.AppendLine(string.Concat("PicUrl", item["WeChat61"].ToString()));
}
ret.AppendLine();
lastTime = item["Modified"].ToString();
}
//nextBeforeTime = lastTime;
return(ret.ToString());
}
else
{
//nextBeforeTime = string.Empty;
return("已无更早留言");
}
//SPList ImageLib = web.GetList(ImageLibUrl);
//SPQuery qry = new SPQuery();
//qry.RowLimit = 1;
//qry.Query = "<OrderBy Override=\"TRUE\"><FieldRef Name=\"Modified\" Ascending=\"FALSE\" /></OrderBy><Where>"
// + "<Lt><FieldRef Name='Created'/><Value IncludeTimeValue='TRUE' Type='DateTime'>"
// + SPUtility.CreateISO8601DateTimeFromSystemDateTime(beforetime)
// + "</Value></Lt></Where>";
//qry.ViewFields = @"<FieldRef Name=""Author"" /><FieldRef Name=""Modified"" /><FieldRef Name=""WeChatPicUrl"" />";
}
}
}
}
private static void ProcessRollupList(
SPWeb web,
string rollupList,
ref string errors,
Action <DataTable> processListAction,
string siteUrl,
string dQuery,
string dqFields)
{
SqlConnection sqlConnection = null;
try
{
SPSecurity.RunWithElevatedPrivileges(
delegate
{
using (var spSite = new SPSite(web.Site.ID))
{
var dbConnection = spSite.ContentDatabase.DatabaseConnectionString;
sqlConnection = new SqlConnection(dbConnection);
sqlConnection.Open();
}
});
var projectedRollup = rollupList.Replace("\r\n", "\n").Split('\n').Distinct().ToArray();
foreach (var rollup in projectedRollup)
{
try
{
var lists = string.Empty;
var query = string.IsNullOrWhiteSpace(siteUrl)
? string.Format(
"SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE webs.siteid='{0}' AND (dbo.AllLists.tp_Title like '{1}')",
web.Site.ID,
rollup.Replace("'", "''"))
: string.Format(
"SELECT dbo.AllLists.tp_ID FROM dbo.Webs INNER JOIN dbo.AllLists ON dbo.Webs.Id = dbo.AllLists.tp_WebId WHERE (dbo.Webs.FullUrl LIKE '{0}/%' OR dbo.Webs.FullUrl = '{0}') AND (dbo.AllLists.tp_Title like '{1}')",
siteUrl,
rollup.Replace("'", "''"));
using (var sqlCommand = new SqlCommand(query, sqlConnection))
{
using (var dataReader = sqlCommand.ExecuteReader())
{
while (dataReader.Read())
{
lists += "<List ID='" + dataReader.GetGuid(0) + "'/>";
}
}
}
if (!string.IsNullOrWhiteSpace(lists))
{
var dataQuery = new SPSiteDataQuery
{
Lists = string.Format("<Lists MaxListLimit='0'>{0}</Lists>", lists),
Query = dQuery,
Webs = "<Webs Scope='Recursive'/>",
ViewFields = dqFields,
QueryThrottleMode = SPQueryThrottleOption.Override
};
try
{
//CC-77556: I don't know how to handle the below scenario regarding Dispose.
//The first assignment seems useless as the variable is replaced right away,
//and creating a variable and disposing it right away also doesn't make sense,
//so I'm keeping the current behavior intact
var dataTable = new DataTable();
SPSecurity.RunWithElevatedPrivileges(delegate { dataTable = web.GetSiteData(dataQuery); });
processListAction?.Invoke(dataTable);
}
catch (Exception ex)
{
errors += "Get Rollup Site Data Error: " + ex.Message + "<br>";
}
}
}
catch (Exception ex)
{
Trace.TraceError("Exception Suppressed {0}", ex);
errors += "Rollup List Error: " + ex.Message + "<br>";
}
}
}
finally
{
sqlConnection.Close();
}
}
private static string FixFor_GettingDistinctWebs(SPSite site)
{
string webRelUrl = String.Empty;
DateTime recentDate = DateTime.Parse(DateTime.Now.ToString("MM/dd/yyyy")).AddDays(-30);
List<string> communitiesWebUrl = new List<string>();
// Fetch using SPSiteDataQuery
using (SPWeb web = site.OpenWeb())
{
SPUser CurrentUser = web.CurrentUser;
//Distinct webs from SiteDataQuery for responses during the last 7 days.
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions.
//query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */
query.Webs = "<Webs Scope=\"SiteCollection\" />";
query.Query = "<Where><Geq><FieldRef Name = 'Modified' /><Value Type ='DateTime'>" + Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(recentDate) + "</Value></Geq></Where>";
DataTable dataTable = web.GetSiteData(query);
foreach (DataRow row in dataTable.Rows)
{
webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl;
if (!communitiesWebUrl.Contains(webRelUrl))
{
communitiesWebUrl.Add(webRelUrl);
}
}
//Distinct webs from SiteDataQuery for responses to my posts. I got this CAML from Chris' code.
// Fetch using SPSiteDataQuery
query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"108\" />"; // Discussions.
query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"LinkDiscussionTitle\"/><FieldRef Name=\"DiscussionLastUpdated\"/><FieldRef Name=\"ItemChildCount\"/><FieldRef Name=\"AverageRating\"/>"; /* Title is LastName column */
query.Webs = "<Webs Scope=\"SiteCollection\" />";
query.Query = string.Format(@"<Where>
<And>
<Eq>
<FieldRef Name='ContentType' />
<Value Type='Computed'>Message</Value>
</Eq>
<Eq>
<FieldRef Name='Author' LookupId='True' />
<Value Type='Integer'>{0}</Value>
</Eq>
</And>
</Where>", CurrentUser.ID);
dataTable = web.GetSiteData(query);
foreach (DataRow row in dataTable.Rows)
{
webRelUrl = site.AllWebs[new Guid(row["WebId"].ToString())].ServerRelativeUrl;
if (!communitiesWebUrl.Contains(webRelUrl))
{
communitiesWebUrl.Add(webRelUrl);
}
}
}
return communitiesWebUrl.ToArray().ToString();
}
static void Main(string[] args)
{
using (var Site = new SPSite("http://epm2007demo/pwa01"))
{
string ConnectionString = Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID, Utilities.DatabaseType.PublishedDatabase);
using (var sqlConnection = new SqlConnection(ConnectionString.Replace("15", "60")))
{
sqlConnection.Open();
}
}
return;
using (var site = new SPSite("http://epm2007demo/pwa01"))
{
var query = new SPSiteDataQuery();
}
return;
using (SPSite Site = new SPSite("http://epm2007demo/pwa02"))
{
string constr = Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID,
Utilities.DatabaseType.
PublishedDatabase);
using (SqlConnection connection = new SqlConnection(constr))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("ProjectUID", "6b08e331-a75e-42f7-98ab-7eede9e4c9e0"));
cmd.Parameters.Add(new SqlParameter("ModifiedBy", "Administrator"));
cmd.CommandText = "TaskAuditTrail";
cmd.ExecuteNonQuery();
}
}
return;
SqlConnection connection_Push =
new SqlConnection("Server=epm2007demo;Database=ITXBaseLineLogs;Trusted_Connection=True;");
connection_Push.Open();
SqlConnection connection_Pull = null;
//SqlConnection connection_Push = null;
SqlDataAdapter dataAdapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand();
DataSet resultSet = new DataSet();
string Qry = string.Empty;
try
{
using (SPSite Site = new SPSite("http://epm2007demo/pwa02"))
{
Guid Project_UID =
new Guid(Site.AllWebs["Project1"].AllProperties[Utilities.ProjWSSUIDProperty].ToString());
connection_Pull =
new SqlConnection(Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID,
Utilities.
DatabaseType.
PublishedDatabase));
connection_Pull.Open();
connection_Push =
new SqlConnection(Utilities.GetProjectServerSQLDatabaseConnectionString(Site.ID,
Utilities.
DatabaseType.
ReportingDatabase));
connection_Push.Open();
command.Connection = connection_Pull;
command.CommandText = "Select ";
for (int i = 0; i < 11; i++)
{
command.CommandText += "TB_BASE_DUR_" + i + ",TB_BASE_DUR_IS_ESTIMATE_" + i +
",TB_BASE_START_" + i + ",TB_BASE_FINISH_" + i + ",TB_BASE_WORK_" + i +
",TB_BASE_COST_" + i + ",TB_BASE_BUDGET_WORK_" + i +
",TB_BASE_BUDGET_COST_" + i + ",";
}
command.CommandText = command.CommandText.Substring(0, command.CommandText.Length - 1);
command.CommandText += " from ProjectSummaries where PROJ_UID='" + Project_UID.ToString() + "'";
dataAdapter.SelectCommand = command;
dataAdapter.Fill(resultSet);
if (resultSet.Tables.Count > 0 && resultSet.Tables[0].Rows.Count > 0)
{
Qry = "Select * from ITXBaseLineDetail where ";
}
else
{
// Log says that empty in Publish DB
}
}
}
catch (Exception)
{
}
finally
{
if (connection_Pull != null && connection_Pull.State == ConnectionState.Open)
connection_Pull.Close();
if (connection_Push != null && connection_Push.State == ConnectionState.Open)
connection_Push.Close();
}
}
private void SPSiteDataQuery_Click(object sender, EventArgs e)
{
using (SPSite _site = new SPSite("http://home/"))
{
using (SPWeb _web = _site.OpenWeb())
{
SPSiteDataQuery query = new SPSiteDataQuery();
// Search in doclibs only
////////////////////////////////////////
/////// ------------------------- //////
/////// BaseType Value //////
/////// ------------------------- //////
/////// Generic List 0 //////
/////// Document Library 1 //////
/////// Discussion Board 3 //////
/////// Survey 4 //////
/////// Issue 5 //////
////////////////////////////////////////
query.Lists = "<Lists BaseType='1' />";
// Only .doc files
query.Query =
@"<Where>
<Eq>
<FieldRef Name='DocIcon' />
<Value Type='Computed'>docx</Value>
</Eq>
</Where>";
// Select only needed columns: file reference
query.ViewFields = "<FieldRef Name='FileRef' />";
// Search in all webs of the site collection
query.Webs = "<Webs Scope='SiteCollection' />";
// Perform the query
DataTable table = _web.GetSiteData(query);
// Generate an absolute url for each document
foreach (DataRow row in table.Rows)
{
string relativeUrl =
row["FileRef"].ToString().Substring(
row["FileRef"].ToString().IndexOf("#") + 1);
string fullUrl = _site.MakeFullUrl(relativeUrl);
// Write urls to window
MessageBox.Show(fullUrl);
}
}
}
}
/// <summary>
/// Gets data using SPSiteDataQuery.
/// </summary>
/// <returns></returns>
protected DataView GetData()
{
DataTable dtResults = null;
DataView dView = null;
using (SPWeb toolsWeb = SPContext.Current.Site.OpenWeb("/tools-catalog"))
{
SPSiteDataQuery Query = new SPSiteDataQuery();
Query.Lists = "<Lists ServerTemplate=\"100\" />";
//"<Lists BaseType=\"1\" />";
Query.ViewFields = "<FieldRef Name=\"Title\" /><FieldRef Name=\"ID\" /><FieldRef Name=\"_Comments\" /><FieldRef Name=\"ContentTypeId\" /><FieldRef Name='MPD_x0020_Classes' /><FieldRef Name='IEPD_x0020_Lifecycle_x0020_Phase' /><FieldRef Name='Artifacts_x0020_Produced' /><FieldRef Name='EMail' /><FieldRef Name='EncodedAbsUrl' /><FieldRef Name='URL' /><FieldRef Name='FileRef' /><FieldRef Name='Latest_x0020_Verison' /><FieldRef Name='Created' /><FieldRef Name='AverageRating' nullable='true'/>";
//content type & property
Query.Query = "<Where><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>" + ToolsContentType + "</Value></Eq></Where>";
Query.Webs = "<Webs Scope=\"Recursive\" />";
dtResults = toolsWeb.GetSiteData(Query);
dtResults = GetLikedTools(dtResults);
LogText("dtResults1:" + dtResults.Rows.Count.ToString());
dtResults = AddDisplayLinks(dtResults);
LogText("dtResults2:" + dtResults.Rows.Count.ToString());
dtResults = GetUserTools(dtResults);
LogText("dtResults3:" + dtResults.Rows.Count.ToString());
dView = new DataView(dtResults);
dView.Sort = "Created desc";
}
return dView;
}
void chkRecentTasksOnly_CheckedChanged(object sender, EventArgs e)
{
chkMyDelegatedTasks.Checked = false;
InitializeWebAppProperties();
if (chkRecentTasksOnly.Checked)
{
string[] actionsOwedSites = sites.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
List<ActionOwed> actionsOwed = new List<ActionOwed>();
DateTime yesterday = DateTime.Today.AddDays(-1);
foreach (string site in actionsOwedSites)
{
using (SPSite emisSite = new SPSite(site))
{
using (SPWeb emisRootWeb = emisSite.OpenWeb())
{
SPSiteDataQuery query = new SPSiteDataQuery();
query.Lists = "<Lists ServerTemplate=\"107\" />";
query.ViewFields = "<FieldRef Name=\"Title\" />" +
"<FieldRef Name=\"ID\" />" +
"<FieldRef Name=\"AssignedTo\" Nullable=\"TRUE\" Type=\"User\" />" +
"<FieldRef Name=\"Status\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"DueDate\" Nullable=\"TRUE\" Type=\"DateTime\" />" +
"<FieldRef Name=\"IsDelegated\" Nullable=\"TRUE\" Type=\"Boolean\" />" +
"<FieldRef Name=\"OriginalAssignee\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"ActionOwed\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"TaskSource\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"FileRef\" Nullable=\"TRUE\" Type=\"Text\" />" +
"<FieldRef Name=\"Related Content\" Nullable=\"TRUE\" Type=\"Text\" />";
query.Query =
"<Where>" +
"<And>" +
"<Geq>" +
"<FieldRef Name='Created' />" +
"<Value IncludeTimeValue='FALSE' Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(yesterday) + "</Value>" +
"</Geq>" +
"<And>" +
"<Eq>" +
"<FieldRef Name='AssignedTo' />" +
" <Value Type='User'><UserID/></Value>" +
"</Eq>" +
"<Neq>" +
"<FieldRef Name='Status' />" +
"<Value Type='Choice'>Completed</Value>" +
"</Neq>" +
"</And>" +
"</And>" +
"</Where>" +
"<OrderBy>" +
"<FieldRef Name=\"DueDate\" />" +
"</OrderBy>";
query.Webs = "<Webs Scope=\"SiteCollection\" />";
DataTable dt = emisRootWeb.GetSiteData(query);
foreach (DataRow row in dt.Rows)
{
ActionOwed action = new ActionOwed()
{
TaskID = row["ID"].ToString(),
Title = row["Title"].ToString(),
DueDate = DateTime.Parse(row["DueDate"].ToString()),
Status = row["Status"].ToString(),
OriginalAssignee = row["OriginalAssignee"].ToString(),
Action = row["ActionOwed"].ToString(),
Source = row["TaskSource"].ToString(),
FileRef = row["FileRef"].ToString(),
TaskSite = site
};
if (string.IsNullOrEmpty(row["IsDelegated"].ToString()))
{
action.IsDelegated = false;
}
else if (row["IsDelegated"].ToString() == "1")
{
action.IsDelegated = true;
}
else if (bool.Parse(row["IsDelegated"].ToString()))
{
action.IsDelegated = true;
}
string taskRelativeLink = row["FileRef"].ToString().Substring(row["FileRef"].ToString().IndexOf("#") + 1, (row["FileRef"].ToString().Length - row["FileRef"].ToString().IndexOf("#") - 1));
action.TaskLink = taskRelativeLink;
action.AssignedTo = row["AssignedTo"].ToString().Substring(row["AssignedTo"].ToString().IndexOf("#") + 1, (row["AssignedTo"].ToString().Length - row["AssignedTo"].ToString().IndexOf("#") - 1));
actionsOwed.Add(action);
}
}
}
}
grdActionsOwed.DataSource = actionsOwed;
grdActionsOwed.DataBind();
}
else
{
GetAllActionsOwed(SPContext.Current.Web.CurrentUser.Name);
}
}
/// <summary>
/// Gets data using SPSiteDataQuery.
/// </summary>
/// <returns></returns>
protected DataView GetDataOld()
{
DataTable dtResults = null;
DataView dvResults = null;
using (SPWeb aboutNiemWeb = SPContext.Current.Site.OpenWeb("/aboutniem"))
{
SPSiteDataQuery Query = new SPSiteDataQuery();
Query.Lists = "<Lists ServerTemplate=\"100\" />";
//"<Lists BaseType=\"1\" />";
Query.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' /><FieldRef Name='StartDate' /><FieldRef Name='Category_x0020_Domains' /><FieldRef Name='Category_x0020_Committee' /><FieldRef Name='Comments' /><FieldRef Name='EndDate' /><FieldRef Name='ContentTypeId' /><FieldRef Name='Category_x0020_Subject_x0020_Are' />";
//content type & property
Query.Query = "<Where><Eq><FieldRef Name='ContentType' /><Value Type='Computed'>" + EventsContentType + "</Value></Eq></Where>";
Query.Webs = "<Webs Scope=\"Recursive\" />";
dtResults = aboutNiemWeb.GetSiteData(Query);
dtResults.Columns.Add("LinkField");
dtResults.Columns.Add("EventDate");
//fixes the links and only uses 150 chars of the PublishingPageContentField
foreach (DataRow Row in dtResults.Rows)
{
string[] CatDomains = Row["Category_x0020_Domains"].ToString().Split('#');
string[] CatAudiences = Row["Category_x0020_Subject_x0020_Are"].ToString().Split('#');
string[] CatCommittees = Row["Category_x0020_Committee"].ToString().Split('#');
Row["Category_x0020_Domains"] = CatDomains[1];
Row["Category_x0020_Subject_x0020_Are"] = CatAudiences[1];
Row["Category_x0020_Committee"] = CatCommittees[1];
DateTime dtStartDate = DateTime.Parse(Row["StartDate"].ToString());
DateTime dtEndDate = DateTime.Parse(Row["EndDate"].ToString());
string DateFormatted = dtStartDate.ToString("MMMM d, yyyy") + " until " + dtEndDate.ToString("MMMM d, yyyy");
//String.Format("{0:M d, yyyy}", Row["StartDate"].ToString()) + " until " + String.Format("{0:M d, yyyy}", Row["EndDate"].ToString());
Row["EventDate"] = DateFormatted;
Row["Comments"] = Row["Comments"].ToString().Substring(0, 150);
SPSite Site = SPContext.Current.Site;
Guid WebGuid = new Guid(Row["WebId"].ToString());
using (SPWeb LinkWeb = Site.OpenWeb(WebGuid))
{
Guid ListGuid = new Guid(Row["ListId"].ToString());
SPList List = LinkWeb.Lists[ListGuid];
Row["LinkField"] = LinkWeb.Url + "/" + List.RootFolder.Url + "/DispForm.aspx?ID=" + Row["ID"].ToString() + "&ContentTypeID=" + Row["ContentTypeId"].ToString() + "&IsDlg=1";
}
}
dvResults = new DataView(dtResults);
/*
////gets profile data
Microsoft.SharePoint.SPServiceContext serviceContext = Microsoft.SharePoint.SPServiceContext.Current;
UserProfileManager upm = new Microsoft.Office.Server.UserProfiles.UserProfileManager(serviceContext);
UserProfile CurrentUser = upm.GetUserProfile(true);
ProfileValueCollectionBase pvbCommunities = CurrentUser.GetProfileValueCollection("EstablishedCommunities");
ProfileValueCollectionBase pvbAudiences = CurrentUser.GetProfileValueCollection("YourAudience");
//grabs established communities and your audiences for filtering
StringBuilder sbQuery = new StringBuilder();
foreach (var value in pvbAudiences)
{
if (sbQuery.Length == 0)
{
sbQuery.Append("Category_x0020_Subject_x0020_Are='" + value.ToString() + "'");
}
else
{
sbQuery.Append(" OR Category_x0020_Subject_x0020_Are='" + value.ToString() + "'");
}
}
foreach (var value in pvbCommunities)
{
if (sbQuery.Length == 0)
{
sbQuery.Append("Category_x0020_Domains='" + value.ToString() + "'");
}
else
{
sbQuery.Append(" OR Category_x0020_Domains='" + value.ToString() + "'");
}
}
dvResults.RowFilter = sbQuery.ToString();
LogText("Returns fine");
*
**/
}
return dvResults;
}
public DataTable Get(string queryString, int startRow, int maxRows) {
var query = new SPSiteDataQuery() {
Lists = view.ListScope,
Webs = view.WebScope,
ViewFields = string.Join("", view.ViewFieldIds.Select(fieldId => string.Format("<FieldRef ID='{0}' />", fieldId))),
Query = queryString,
RowLimit = (uint)(startRow + maxRows)
};
return Get(query, startRow, maxRows);
}
protected DataTable Get(SPSiteDataQuery query, uint startRow, uint maxRows) {
var data = Web.GetSiteData(query);
if (data.Rows.Count > 0) {
return data.Select().
Skip(int.Parse(startRow.ToString())).Take(int.Parse(maxRows.ToString()))
.CopyToDataTable();
} else {
return data;
}
}
