private DataTable GetDataSource(SPListItemCollection items)
{
DataTable dt = new DataTable();
switch (this._webpartType)
{
case WebPartType.News:
string newsType = this._newsType;
string subNewsType = this._subNewsType;
if (newsType.IsNotNullOrWhitespace()
&& subNewsType.IsNotNullOrWhitespace())
{
var query = from e in items.GetDataTable().AsEnumerable()
where newsType.Equals(e.Field<string>("NewsType"))
&& subNewsType.Equals(e.Field<string>("NewsSubType"))
select e;
dt = query.AsDataView().ToTable();
}
break;
case WebPartType.Announcement:
dt = items.GetDataTable();
break;
default: break;
}
return dt;
}
void PopulateDropdown()
{
EnsureChildControls();
try
{
ClearError();
if (string.IsNullOrEmpty(strFilterValueField))
{
SPQuery query = BuildQuery(strField, strParentField, strFilterValueField, "");
SPListItemCollection queryResults = ExecuteQuery(strUrl, strList, strField, query);
if (queryResults != null)
{
BindDDL(this, queryResults.GetDataTable(), strField);
}
}
else
{
DropDownList ddlFilterValueField = (DropDownList)FindControlRecursive(this.Page, strFilterValueField);
SPQuery query = BuildQuery(strField, strParentField, strFilterValueField, ddlFilterValueField.SelectedValue);
SPListItemCollection queryResults = ExecuteQuery(strUrl, strList, strField, query);
if (queryResults != null)
{
BindDDL(this, queryResults.GetDataTable(), strField);
}
}
}
catch (Exception ex)
{
ReportError(ex);
}
}
public DataTable GetNewsRecords(string query)
{
DataTable table = new DataTable();
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (var site = new SPSite(SPContext.Current.Web.Site.ID))
{
using (var web = site.OpenWeb(SPContext.Current.Web.ID))
{
try
{
SPQuery spQuery = new SPQuery
{
Query = query
};
SPList list = Utilities.GetDocListFromUrl(web, ListsName.English.ImageCatList);
if (list != null)
{
SPListItemCollection items = list.GetItems(spQuery);
table = items.GetDataTable();
}
}
catch (Exception ex)
{
table = null;
}
}
}
});
return(table);
}
private void getPreviouslySavedObjectives()
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPSite oSite = new SPSite(SPContext.Current.Web.Url);
SPWeb spWeb = oSite.OpenWeb();
SPList spList = spWeb.GetList(SPUrlUtility.CombineUrl(spWeb.ServerRelativeUrl, "lists/" + "Objectives")); //SPList spList = spWeb.GetList("/Lists/Objectives");
SPQuery qry = new SPQuery();
qry.Query =
@" <Where>
<And>
<Eq>
<FieldRef Name='Emp' />
<Value Type='User'>" + strEmpDisplayName + @"</Value>
</Eq>
<Eq>
<FieldRef Name='ObjYear' />
<Value Type='Text'>" + Active_Rate_Goals_Year + @"</Value>
</Eq>
</And>
</Where>";
qry.ViewFieldsOnly = true;
qry.ViewFields = @"<FieldRef Name='ID' /><FieldRef Name='ObjName' /><FieldRef Name='ObjYear' /><FieldRef Name='ObjWeight' /><FieldRef Name='AccPercent' />";
SPListItemCollection listItems = spList.GetItems(qry);
tblObjectives = listItems.GetDataTable();
});
}
//protected void Button2_Click(object sender, EventArgs e)
//{
// try
// {
// SPSecurity.RunWithElevatedPrivileges(delegate()
// {
// using (SPSite site = new SPSite(SPContext.Current.Web.Url))
// {
// using (SPWeb web = site.OpenWeb())
// {
// SPList list = web.Lists.TryGetList("ScheduleInterview");
// if (list != null)
// {
// SPListItem NewItem = list.Items.Add();
// {
// web.AllowUnsafeUpdates = true;
// string clientname = lookup(txtName.SelectedValue);
// NewItem["CandidateName"] = new SPFieldLookupValue(Convert.ToInt32(clientname), txtName.SelectedValue.ToString());
// //NewItem["CandidateName"] = txtName.SelectedItem.ToString();
// int Interviewer = spPeoplePickerUserName.ResolvedEntities.Count;
// for (int i = 0; i < Interviewer; i++)
// {
// PickerEntity peEntity = spPeoplePickerUserName.ResolvedEntities[i] as PickerEntity;
// SPUser user = SPContext.Current.Web.EnsureUser(peEntity.Key);
// NewItem["Interviewer"] = user.Name;
// }
// // Datetime Control
// if (!DateTimeControl1.IsDateEmpty)
// {
// NewItem["InterviewDate"] = Convert.ToDateTime(DateTimeControl1.SelectedDate).ToShortDateString();
// }
// else
// {
// NewItem["InterviewDate"] = DateTime.Now;
// }
// NewItem["Remarks"] = txtRemarks.Text;
// NewItem["Status"] = drpStatus.SelectedValue.ToString();
// NewItem.Update();
// web.AllowUnsafeUpdates = false;
// Page.Response.Redirect(SPContext.Current.Site.Url + MainSite, false);
// }
// }
// }
// }
// });
// }
// catch (Exception ex)
// {
// //Alert.Text = ex.Message.ToString();
// }
//}
//public string lookup(string title)
//{
// string value = null;
// using (SPSite osite = new SPSite(SPContext.Current.Site.Url))
// {
// using (SPWeb oweb = osite.OpenWeb())
// {
// SPList olist = oweb.Lists.TryGetList("Candidates");
// SPQuery oquery = new SPQuery();
// oquery.Query = "<Where><Eq><FieldRef Name='CandidateName' /><Value Type='Text'>" + title + "</Value></Eq></Where><ViewFields><FieldRef Name='ID' /></ViewFields>";
// SPListItemCollection ocollection = olist.GetItems(oquery);
// if (ocollection != null)
// {
// foreach (SPListItem oitem in ocollection)
// {
// value = oitem["ID"].ToString();
// }
// }
// return value; // ddlEmpType.Items.Insert(0, "--Select--");
// }
// }
//}
//public void ProjectType(DropDownList txtName)
//{
// using (SPSite osite = new SPSite(SPContext.Current.Site.Url))
// {
// using (SPWeb oweb = osite.OpenWeb())
// {
// SPList olist = oweb.Lists.TryGetList("Candidates");
// SPQuery oquery = new SPQuery();
// oquery.Query = "<Query><Where><Eq><FieldRef Name='CandidateName' /></Eq></Where></Query>";
// SPListItemCollection ocollection = olist.GetItems(oquery);
// foreach (SPListItem oitem in ocollection)
// {
// txtName.Items.Add(oitem["CandidateName"].ToString());
// }
// //ddlClient.Items.Insert(0, "None");
// }
// }
//}
public void bindview()
{
try
{
SPSecurity.RunWithElevatedPrivileges(delegate
{
using (SPSite site = new SPSite(SPContext.Current.Web.Url))
{
using (SPWeb web = site.OpenWeb())
{
web.AllowUnsafeUpdates = true;
SPList list = web.Lists.TryGetList("ScheduleInterview");
SPQuery oquery = new SPQuery();
// oquery.Query = "<OrderBy><FieldRef Name='ID' Ascending='False' /></OrderBy>";
oquery.Query = " <Where><IsNotNull><FieldRef Name='CandidateName' /></IsNotNull></Where><OrderBy><FieldRef Name='Created' Ascending='False' /></OrderBy>";
SPListItemCollection coll = list.GetItems(oquery);
DataTable dt = coll.GetDataTable();
if (dt.Rows.Count > 0)
{
oGridView.DataSource = dt;
oGridView.DataBind();
}
web.AllowUnsafeUpdates = false;
}
}
});
}
catch (Exception ex)
{
//oException.Exception(ex.Message.ToString(), ex.StackTrace.ToString(), "bindview()");
}
}
/// <summary>
/// 根据创建者获取数据集
/// </summary>
/// <param name="listName"></param>
/// <param name="AuthorName"></param>
/// <returns></returns>
public static DataTable GetDataByAuthorName(string listName, string AuthorName)
{
string siteUrl = SPContext.Current.Site.Url;
DataTable retDataTable = null;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite spSite = new SPSite(siteUrl)) //找到网站集
{
using (SPWeb spWeb = spSite.OpenWeb(SPContext.Current.Web.ID))
{
SPList spList = spWeb.Lists.TryGetList(listName);
if (spList != null)
{
SPQuery qry = new SPQuery();
qry.Query = @" <Where><Eq><FieldRef Name='Author' /><Value Type='User'>" + AuthorName + "</Value></Eq></Where>";
SPListItemCollection listItems = spList.GetItems(qry);
if (listItems.Count > 0)
{
retDataTable = listItems.GetDataTable();
}
}
}
}
});
return(retDataTable);
}
private void ShowHome()
{
mtvViews.SetActiveView(viewHome);
using (SPSite site = new SPSite(SPContext.Current.Site.Url))
{
using (SPWeb web = site.RootWeb)
{
SPList listMeusSistemas = web.Lists.TryGetList(LIST_MEUSSISTEMAS);
if (listMeusSistemas != null)
{
var query = new SPQuery()
{
Query = @"<Where><Eq><FieldRef Name='Author' /><Value Type='User'>" + web.CurrentUser.Name + "</Value></Eq></Where>"
};
SPListItemCollection sistemas = listMeusSistemas.GetItems(query);
rptItens.DataSource = sistemas.GetDataTable();
rptItens.DataBind();
if (rptItens.Items.Count == 0)
{
pnlNoData.Visible = true;
rptItens.Visible = false;
//verTodosMeusSistemas.Visible = false;
}
}
}
}
}
public static DataAccessModel GetListModelBLData(string listName, string url)
{
DataAccessModel outPutData = new DataAccessModel();
try
{
using (SPSite oSite = new SPSite(url))
{
using (SPWeb oWeb = oSite.OpenWeb())
{
List <string> camlQuery = new List <string>();
camlQuery = GetModelBLCAML();
SPQuery query = new SPQuery();
query.Query = camlQuery[0].ToString();
query.ViewFields = camlQuery[1].ToString();
SPList oList = oWeb.Lists[listName];
SPListItemCollection items = oList.GetItems(query);
var dt = items.GetDataTable();
var list = (from table in dt.AsEnumerable()
select new ModelBL
{
Id = table["Id"].ToString(),
Title = table["Title"].ToString(),
BL_CATEGORY = table["BL_CATEGORY"].ToString(),
BRAND = table["BRAND"].ToString(),
BL_PRODUCT_TYPE = table["BL_PRODUCT_TYPE"].ToString(),
BL_PRODUCT_SIZE = table["BL_PRODUCT_SIZE"].ToString(),
REFRIGERANT = table["REFRIGERANT"].ToString(),
INDOOR = table["INDOOR"].ToString(),
OUTDOOR = table["OUTDOOR"].ToString(),
INSTALLATION = table["INSTALLATION"].ToString(),
OWNER = table["OWNER"].ToString(),
DISC = table["DISC"].ToString(),
SPECIFICATION = table["SPECIFICATION"].ToString(),
BULLETIN = table["BULLETIN"].ToString(),
DATABOOK = table["DATABOOK"].ToString(),
VDO = table["VDO"].ToString(),
PRESENTATION = table["PRESENTATION"].ToString(),
IMAGE_LOW = table["IMAGE_LOW"].ToString(),
IMAGE_HD = table["IMAGE_HD"].ToString(),
CATALOGUE = table["CATALOGUE"].ToString()
}).ToList();
outPutData.listModelBLData = list;
outPutData.Status = true;
outPutData.Reason = "Success";
}
}
return(outPutData);
}
catch (Exception ex)
{
outPutData.listModelBLData = new List <ModelBL>();
outPutData.Status = false;
outPutData.Reason = ex.Message.ToString();
return(outPutData);
}
}
private void ShowAll()
{
mtvViews.SetActiveView(viewAll);
using (IntranetData intranet = new IntranetData(SPContext.Current.Web.Url))
{
foreach (Sistema sistema in intranet.SistemasDeAAZ)
{
ddlSistemas.Items.Add(new ListItem(new SPFieldUrlValue(sistema.URL.ToString()).Description, sistema.Id.ToString()));
}
}
using (SPSite site = new SPSite(SPContext.Current.Site.Url))
{
using (SPWeb web = site.RootWeb)
{
SPList listMeusSistemas = web.Lists.TryGetList(LIST_MEUSSISTEMAS);
if (listMeusSistemas != null)
{
var query = new SPQuery()
{
Query = @"<Where><Eq><FieldRef Name='Author' /><Value Type='User'>" + web.CurrentUser.Name + "</Value></Eq></Where>"
};
SPListItemCollection sistemas = listMeusSistemas.GetItems(query);
rptItens2.DataSource = sistemas.GetDataTable();
rptItens2.DataBind();
}
}
}
}
private void btnFind_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(lbSiteName.Text) || string.IsNullOrEmpty(txtListName.Text))
{
MessageBox.Show("Site name or List Name is empty!");
return;
}
Cursor.Current = Cursors.WaitCursor;
listFields.Clear();
listFields = GetAllListColumns(txtSiteName.Text.Trim(), txtListName.Text.Trim());
if (listFields.Count() > 0)
{
bsListFields.DataSource = listFields;
grResult.DataSource = bsListFields;
grResult.Columns["Title"].Width = 170;
grResult.Columns["InternalName"].Width = 350;
}
// Get Items
DataTable dt = items.GetDataTable();
if (dt.Rows.Count > 0)
{
bsItems.DataSource = dt;
grItems.DataSource = bsItems;
}
// --------------
Cursor.Current = Cursors.Default;
}
/// <summary>
/// The get.
/// </summary>
/// <param name="listItems">
/// The list items.
/// </param>
/// <typeparam name="T"> The type of object to return
/// </typeparam>
/// <returns>
/// The <see cref="IList"/>.
/// </returns>
public IList <T> Get <T>(SPListItemCollection listItems) where T : new()
{
var returnList = new List <T>();
if (listItems.Count > 0)
{
// Using GetDataTable is great because it eagerly fetches all
// the data of the SPListItemCollection. Without a GetDataTable
// each step in the SPListItemCollection enumeration will trigger
// a database call. If you are truly careless and forgot to specify
// your SPQuery.ViewFields, each field value access on the item will
// also trigger a database call.
// Lessons:
// 1) always use ISharePointEntityBinder.Get<T>(SPListItemCollection)
// because it eagerly fetches all the data
// and
// 2) always specify SPQuery.ViewFields to avoid per-field-access
// database calls.
var table = listItems.GetDataTable();
var rows = table.AsEnumerable();
foreach (var dataRow in rows)
{
returnList.Add(this.Get <T>(dataRow, listItems.Fields));
}
}
return(returnList);
}
protected void BindRepeater()
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (var adminSite = new SPSite(CurrentWeb.Site.ID))
{
using (var adminWeb = adminSite.OpenWeb(CurrentWeb.ID))
{
try
{
adminWeb.AllowUnsafeUpdates = true;
SPList list = Utilities.GetCustomListByUrl(adminWeb, ListsName.InternalName.DocumentsList);
SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='ID' Ascending='FALSE' /></OrderBy>";
query.RowLimit = 10;
SPListItemCollection items = list.GetItems(query);
if (items != null && items.Count > 0)
{
rptDocument.DataSource = items.GetDataTable();
rptDocument.DataBind();
}
}
catch (Exception ex)
{
Utilities.LogToULS(ex);
}
}
}
});
}
protected void BindDropDownList()
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
using (var adminSite = new SPSite(CurrentWeb.Site.ID))
{
using (var adminWeb = adminSite.OpenWeb(CurrentWeb.ID))
{
try
{
adminWeb.AllowUnsafeUpdates = true;
SPList iconLink = Utilities.GetCustomListByUrl(adminWeb, ListsName.InternalName.WebsiteLink);
SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='Title' Ascending='True' /></OrderBy>";
SPListItemCollection items = iconLink.GetItems(query);
DataTable dt = items.GetDataTable();
if (dt != null && dt.Rows.Count > 0)
{
lbWebURL.DataSource = dt;
lbWebURL.DataTextField = FieldsName.WebsiteLink.InternalName.Title;
lbWebURL.DataValueField = FieldsName.WebsiteLink.InternalName.WebURL;
lbWebURL.DataBind();
}
//lbWebURL.Items.Insert(0, new ListItem("--Liên kết website--", string.Empty));
lbWebURL.Attributes.Add("onclick", string.Format("RedirectURL('{0}')", lbWebURL.ClientID));
}
catch (SPException ex)
{
Utilities.LogToULS(ex);
}
}
}
});
}
public static SPListItemCollection getPreviouslySavedObjectives(string strEmpDisplayName, string Active_Set_Goals_Year)
{
SPListItemCollection listItems = null;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPSite oSite = new SPSite(SPContext.Current.Web.Url);
SPWeb spWeb = oSite.OpenWeb();
SPList spList = spWeb.GetList(SPUrlUtility.CombineUrl(spWeb.ServerRelativeUrl, "lists/" + "Objectives"));
if (spList != null)
{
SPQuery qry = new SPQuery();
qry.Query =
@" <Where>
<And>
<Eq>
<FieldRef Name='Emp' />
<Value Type='User'>" + strEmpDisplayName + @"</Value>
</Eq>
<Eq>
<FieldRef Name='ObjYear' />
<Value Type='Text'>" + Active_Set_Goals_Year + @"</Value>
</Eq>
</And>
</Where>";
listItems = spList.GetItems(qry);
DataTable test = listItems.GetDataTable();
}
});
return(listItems);
}
private DataTable GetAllActions()
{
DataTable dt = null;
SPUser user = SPContext.Current.Web.CurrentUser;
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
using (SPSite spSite = new SPSite(SPContext.Current.Site.ID)) //找到网站集
{
using (SPWeb spWeb = spSite.OpenWeb(SPContext.Current.Web.ID))
{
SPList spList = spWeb.Lists.TryGetList(objWeb.ActionListName);
if (spList == null)
{
lblMsg.Text = objWeb.ActionListName + " 列表不存在！";
}
SPQuery qry = new SPQuery();
qry.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' />";
qry.Query = @"<Where><Or><And><Eq><FieldRef Name='Author' LookupId='True' /><Value Type='Lookup'>" + user.ID + "</Value></Eq><Eq><FieldRef Name='Flag' /><Value Type='Number'>11</Value></Eq></And><Eq><FieldRef Name='Flag' /><Value Type='Number'>0</Value></Eq></Or></Where>";
SPListItemCollection items = spList.GetItems(qry);
dt = items.GetDataTable();
}
}
}
catch
{
}
});
return(dt);
}
private DataTable get_PreviousRequests_by_hid(string hid)
{
DataTable results = new DataTable();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
SPSite site = new SPSite(SPContext.Current.Web.Url);
SPWeb spWeb = site.OpenWeb();
SPList spList = spWeb.Lists.TryGetList("AidRequests");
if (spList != null)
{
SPQuery qry = new SPQuery();
qry.Query =
@" <Where>
<Eq>
<FieldRef Name='EIDCardNumber' />
<Value Type='Text'>" + hid + @"</Value>
</Eq>
</Where>";
SPListItemCollection listItems = spList.GetItems(qry);
results = listItems.GetDataTable();
}
});
return(results);
}
public void getRecords(string siteUrl, Guid listGuid, int itemId)
{
using (SPSite spsite = new SPSite(siteUrl))
{
using (SPWeb spweb = spsite.OpenWeb(siteUrl))
{
SPListCollection colllists = spweb.Lists;
SPList parentlist = colllists.GetList(listGuid, true);
string parentlistTitle = parentlist.Title.ToString(); //父列表标题
string childlistTitle = parentlistTitle + "业绩"; //子列表标题
SPList childlist = colllists.TryGetList(childlistTitle);
if (childlist != null)
{
SPQuery qry = new SPQuery();
qry.Query = "<Where><Eq><FieldRef Name='Dept' LookupId='True' /><Value Type='Lookup'>" + itemId + "</Value></Eq></Where>";
SPListItemCollection listItems = childlist.GetItems(qry);
if (listItems.Count > 0)
{
DataTable dt = listItems.GetDataTable();
}
}
}
}
}
/// <summary>
/// 读取创意列表生成创意DataTable数据源
/// </summary>
/// <param name="lstName"></param>
/// <returns></returns>
private DataTable getDataBySPList(string lstName)
{
DataTable dt = new DataTable("创意表");
using (SPSite site = SPContext.Current.Site)
{
using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
{
SPListCollection lstColl = web.Lists;
SPList lst = lstColl.TryGetList(lstName);
if (lst != null)
{
SPQuery qry = new SPQuery();
qry.Query = @"<Where><Eq><FieldRef Name='Flag0' /><Value Type='Number'>1</Value></Eq></Where>";
SPListItemCollection lstItems = lst.GetItems(qry);
if (lst.ItemCount > 0)
{
dt = lstItems.GetDataTable();
}
else
{
dt = null;
}
}
}
}
return(dt);
}
protected void Add_Item_In_Repeater(string fieldName, bool action)
{
using (SPSite site = new SPSite(SPContext.Current.Site.Url))
{
SPWeb web = site.OpenWeb();
SPList list = web.Lists["News"];
SPQuery query = new SPQuery();
query.Query = string.Format(@"
<Where>
<Eq>
<FieldRef Name ='Visible'/>
<Value Type ='Boolean'>
1
</Value>
</Eq>
</Where >
<OrderBy>
<FieldRef Name='{0}' Ascending='{1}'/>
</OrderBy>", fieldName, action);
SPListItemCollection items = list.GetItems(query);
RptArt.DataSource = items.GetDataTable();
RptArt.DataBind();
}
}
private void BindListView()
{
try
{
using (SPSite site = new SPSite("http://novosite.tce.se.gov.br"))
{
using (SPWeb web = site.RootWeb)
{
SPList list = web.GetList("/noticias/Revistas");
SPListItemCollection itens = list.GetItems();
StringBuilder sb = new StringBuilder();
SPQuery oQuery = itens.SourceQuery;
oQuery.Query = "<OrderBy><FieldRef Name='Modified' Ascending='FALSE' /></OrderBy>";
//oQuery.RowLimit = 4;
itens = list.GetItems(oQuery);
lvCustomers.DataSource = itens.GetDataTable();
lvCustomers.DataBind();
}
}
}
catch (Exception ex)
{
}
}
private void GetCollateDates()
{
string query = @"<ViewFields>
<FieldRef Name='Title' />
<FieldRef Name='StartDate' />
<FieldRef Name='EndDate' />
</ViewFields>
<Where>
<Or>
<Eq>
<FieldRef Name='Title' />
<Value Type='Text'>绩点校对</Value>
</Eq>
<Eq>
<FieldRef Name='Title' />
<Value Type='Text'>金额校对</Value>
</Eq>
</Or>
</Where>
<OrderBy>
<FieldRef Name='StartDate' />
</OrderBy>";
string listName = webObj.CollateDate;
SPListItemCollection collateDates = GetDataFromList(listName, query);
DataTable dt = collateDates.GetDataTable();
for (int i = 0; i < dt.Rows.Count; i++)
{
DateTime[] dates = new DateTime[] { (DateTime)(dt.Rows[i]["StartDate"]), (DateTime)(dt.Rows[i]["EndDate"]) };
ViewState["校对" + i.ToString()] = dates;
}
}
private SPListItemCollection GetCurrentDateActivity(int userId, DateTime time)
{
SPSite site = new SPSite("http://localhost");
SPWeb web = site.OpenWeb(txtWebUrl.Text);
SPList list = web.Lists.TryGetList(webObj.ActivityList);
string dateFld = "Date";
SPListItemCollection items = null;
if (list != null)
{
SPQuery qry = new SPQuery();
qry.Query = @"<Where>
<And>
<Eq>
<FieldRef Name='Author' LookupId='True' />
<Value Type='Integer'>" + userId + @"</Value>
</Eq>
<Eq>
<FieldRef Name='" + dateFld + @"' />
<Value Type='DateTime'>" + time.ToString("yyyy-MM-dd") + @"</Value>
</Eq>
</And>
</Where><OrderBy><FieldRef Name='Created' Ascending='true' /></OrderBy>";
items = list.GetItems(qry);
DataTable dt = items.GetDataTable().Clone();
}
return(items);
}
private void Page_Load(object sender, EventArgs e)
{
try
{
DateTime today = DateTime.Now;
int tuan = GetWeekOrderInYear(today);
DateTime firstDay = GetFirstDayOfWeek(DateTime.Now.Year.ToString(), tuan - 3);
DateTime lastDay = GetLastDayOfWeek(DateTime.Now.Year.ToString(), tuan);
categories.Tuan1 = "Tuần " + (tuan - 3);
categories.Tuan2 = "Tuần " + (tuan - 2);
categories.Tuan3 = "Tuần " + (tuan - 1);
categories.Tuan4 = "Tuần " + (tuan);
//categoryStr = ConvertObjectToJson(categories);
string url = Tandan.Utilities.Utility.GetAbsoluteSiteUrl(this.SiteUrl);
using (SPSite site = new SPSite(url))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists["CongViec"];
SPQuery query = new SPQuery();
query.Query = string.Concat("<Where>",
"<And>",
"<Geq>",
"<FieldRef Name='NgayBatDau'/>",
"<Value IncludeTimeValue='FALSE' Type='DateTime'>" + firstDay.ToString("yyyy-MM-ddThh:mm:ssZ") + "</Value>",
"</Geq>",
"<Leq>",
"<FieldRef Name='NgayBatDau'/>",
"<Value IncludeTimeValue='FALSE' Type='DateTime'>" + lastDay.ToString("yyyy-MM-ddThh:mm:ssZ") + "</Value>",
"</Leq>",
"</And>",
"</Where>",
"<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>");
query.ViewFields = string.Concat("<FieldRef Name='ID'/>",
"<FieldRef Name='NgayBatDau'/>",
"<FieldRef Name='NguoiThucHien'/>",
"<FieldRef Name='DaKetThuc'/>",
"<FieldRef Name='NguoiDaThucHien'/>");
query.ViewFieldsOnly = true;
SPListItemCollection items = list.GetItems(query);
if (items != null && items.Count != 0)
{
DataTable dtGetData = items.GetDataTable();
DangXuLy = GetDataByLinQ(tuan, "Đang xử lý", dtGetData, web.CurrentUser.ToString());
ChuyenXuLy = GetDataByLinQ(tuan, "Đã chuyển xử lý", dtGetData, web.CurrentUser.ToString());
DaXuLy = GetDataByLinQ(tuan, "Đã xử lý", dtGetData, web.CurrentUser.ToString());
DangXuLyStr = ConvertObjectToJson(DangXuLy);
ChuyenXuLyStr = ConvertObjectToJson(ChuyenXuLy);
DaXuLyStr = ConvertObjectToJson(DaXuLy);
}
}
}
}
catch (Exception ex)
{
//TextBox1.Text = ex.ToString();
}
}
private DataTable GetTypesTable(string actionTypeList, string siteUrl)
{
DataTable dtReturn = new DataTable();
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite spSite = new SPSite(siteUrl)) //找到网站集
{
using (SPWeb spWeb = spSite.OpenWeb())
{
SPList spList = spWeb.Lists.TryGetList(actionTypeList);
if (spList != null)
{
SPQuery qry = new SPQuery();
qry.ViewFields = "<FieldRef Name='Title' />";
SPListItemCollection listItems = spList.GetItems(qry);
if (listItems.Count > 0)
{
dtReturn = listItems.GetDataTable();
}
else
{
dtReturn = null;
}
}
else
{
dtReturn = null;
}
}
}
});
return(dtReturn);
}
private void CargarCanal()
{
SPWeb app = SPContext.Current.Web;
app.AllowUnsafeUpdates = true;
SPListItemCollection items = app.Lists["Canales"].Items;
util.CargaDDL(ddlCanal, items.GetDataTable(), "Nombre", "ID");
}
private void CargarEstadoCertificado()
{
SPWeb app = SPContext.Current.Web;
app.AllowUnsafeUpdates = true;
SPListItemCollection items = app.Lists["EstadoCertificado"].Items;
util.CargaDDL(ddlEdoCertificado, items.GetDataTable(), "Nombre", "ID");
}
public override void Execute(Guid contentDbId)
{
// get a reference to the current site collection's content database
var webApplication = Parent as SPWebApplication;
if (webApplication != null)
{
SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];
SPWeb objWeb = contentDb.Sites[0].RootWeb;
DateTime objTiSiteCreationtime = objWeb.Created;
SPList taskList = contentDb.Sites[0].RootWeb.Lists["Tasks"];
var objQuery = new SPQuery();
objQuery.Query = "<Where><Gt><FieldRef Name=\"ID\"/><Value Type=\"Counter\">0</Value></Gt></Where>";
SPListItemCollection objCollection = taskList.GetItems(objQuery);
DataTable dtAllLists = objCollection.GetDataTable();
var objArrayList = new ArrayList();
if (dtAllLists != null)
{
if (dtAllLists.Rows.Count > 0)
{
for (int iCnt = 0; iCnt <= dtAllLists.Rows.Count - 1; iCnt++)
{
objArrayList.Add(Convert.ToString(dtAllLists.Rows[iCnt]["Title"]));
}
}
}
for (int iCnt = 0; iCnt <= objWeb.Lists.Count - 1; iCnt++)
{
if (!objArrayList.Contains(objWeb.Lists[iCnt].Title))
{
if (objWeb.Lists[iCnt].Created.ToShortDateString()
!= objTiSiteCreationtime.ToShortDateString())
{
SPListItem newTask = taskList.Items.Add();
newTask["Title"] = objWeb.Lists[iCnt].Title;
newTask.Update();
//Write a logic to send a mail to admin
}
}
}
}
}
void PopulateChild(CascadingLookupFieldControl field, string filterValue)
{
SPQuery query = BuildQuery(field.strField, field.strParentField, field.strFilterValueField, filterValue);
SPListItemCollection queryResults = ExecuteQuery(field.strUrl, field.strList, field.strField, query);
if (queryResults != null)
{
BindDDL(field, queryResults.GetDataTable(), field.strField);
}
}
/// <summary>
/// Binds the tasks.
/// </summary>
/// <param name="taskList">The task list.</param>
/// <param name="dtCells">The dt cells.</param>
/// <returns>DataTable.</returns>
public static DataTable BindTasks(string taskList, string[] dtCells)
{
DataTable dt = new DataTable();
string url = SPContext.Current.Web.Url;
#region 提升权限运行,将SPList列表数据绑定到DataTable
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite(url))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists[taskList];
SPQuery spQuery = new SPQuery();
spQuery.Query = @"<Where><Eq><FieldRef Name='Status'/>" + "<Value Type='Text'>已完成</Value></Eq></Where>";
SPListItemCollection collListItems = list.GetItems(spQuery);
//for (int i = 0; i < list.ItemCount; i++)
//{
// DataRow dr = dt.NewRow();
// SPListItem item = list.Items[i];
// dr["Title"] = item["Title"].ToString();//名称
// dr["_Level"] = item["_Level"].ToString();//级别
// dr["Priority"] = item["Priority"].ToString();//优先级
// dr["Status"] = item["Status"].ToString();//状态
// dr["PercentComplete"] = item["PercentComplete"].ToString();//完成百分比
// dr["AssignedTo"] = item["AssignedTo"].ToString();//分配对象
// dr["StartDate"] = item["StartDate"].ToString();//开始日期
// dr["DueDate"] = item["DueDate"].ToString();//截止日期
// dt.Rows.Add(dr);
//}
//foreach (SPListItem item in collListItems)
//{
// DataRow dr = dt.NewRow();
// string tempStr = "";
// dr["Title"] = item["Title"].ToString();//名称
// dr["_Level"] = item["_Level"].ToString();//级别
// dr["Priority"] = item["Priority"].ToString();//优先级
// dr["Status"] = item["Status"].ToString();//状态
// dr["PercentComplete"] = ((Double)item["PercentComplete"]*100).ToString()+"%";//完成百分比
// tempStr = item["AssignedTo"].ToString();
// tempStr = tempStr.Remove(tempStr.Length - 2);
// dr["AssignedTo"] =tempStr ;//分配对象
// dr["StartDate"] = item["StartDate"].ToString();//开始日期
// dr["DueDate"] = item["DueDate"].ToString();//截止日期
// dt.Rows.Add(dr);
//}
dt = collListItems.GetDataTable();
}
}
});
return(dt);
#endregion
}
private static void GetUserStateFromPlan(string userName, string planList, string siteUrl, string[] stateIndexs)
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
try
{
using (SPSite spSite = new SPSite(siteUrl)) //找到网站集
{
using (SPWeb spWeb = spSite.OpenWeb())
{
spWeb.AllowUnsafeUpdates = true;
SPList spList = spWeb.Lists.TryGetList(planList);
if (spList != null)
{
SPQuery qry = new SPQuery();
qry.Query = @"<Where><Eq><FieldRef Name='Author' LookupId='True' /><Value Type='Text'>" + userName + "</Value></Eq></Where>";
SPListItemCollection pListItems = spList.GetItems(qry);
//var results = from t in spList.Items.Cast<SPListItem>() select new { t.Fields("Author")};
//var disresults = Enumerable.Distinct(results);
if (pListItems.Count > 0)
{
DataTable dt = pListItems.GetDataTable();
for (int i = 0; i < stateIndexs.Length; i++)
{
string benchmarkList = ConfigurationSettings.AppSettings["BenchmarkList"].ToString();
DataTable dtBenchmark = GetBenchmarkByIndex(stateIndexs[i], siteUrl, benchmarkList);
if (dtBenchmark.Rows.Count > 0)
{
for (int j = 0; j <= dtBenchmark.Rows.Count; j++)
{
string aType = dtBenchmark.Rows[j]["活动类别"].ToString();
string selectStr = "操作类别 ='" + aType + "'";
DataRow[] drs = dt.Select(selectStr);
}
}
}
}
}
else
{
Console.WriteLine("列表“" + planList + "”不存在！");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
});
}
private void CargarAcreedor()
{
SPWeb app = SPContext.Current.Web;
SPList Lista = app.Lists["Acreedores"];
app.AllowUnsafeUpdates = true;
SPQuery oQuery = new SPQuery();
oQuery.Query = "<OrderBy> <FieldRef Name = 'Nombre' Ascending = 'TRUE'/> </OrderBy>";
SPListItemCollection items = Lista.GetItems(oQuery);
util.CargaDDL(ddlAcreedor, items.GetDataTable(), "Nombre", "ID");
}
/// <summary>
/// Get and return table with correct url
/// </summary>
/// <param name="items"></param>
/// <returns></returns>
public static DataTable GetTableWithCorrectUrlHotNews(SPListItemCollection items)
{
var dataTable = items.GetDataTable();
if (items != null && items.Count > 0)
{
string imagepath = string.Empty;
ImageFieldValue imageIcon;
SPFieldUrlValue advLink;
for (int i = 0; i < items.Count; i++)
{
imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue;
if (imageIcon != null)
{
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imageIcon.ImageUrl;
}
else
{
if (imagepath.Length > 2)
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2);
else
{
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath;
}
}
if (items[i].Fields.ContainsField(FieldsName.NewsRecord.English.LinkAdv))
{
advLink = new SPFieldUrlValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.LinkAdv]));
dataTable.Rows[i][FieldsName.NewsRecord.English.LinkAdv] = advLink.Url;
}
}
}
return dataTable;
}
/// <summary>
/// Get and return table with correct url
/// </summary>
/// <param name="categoryListName">categoryListName</param>
/// <param name="items"></param>
/// <returns></returns>
public static DataTable GetTableWithCorrectUrlHotNews(string categoryListName, SPListItemCollection items)
{
var dataTable = items.GetDataTable();
if (!dataTable.Columns.Contains(FieldsName.CategoryId))
{
dataTable.Columns.Add(FieldsName.CategoryId, Type.GetType("System.String"));
}
if (!dataTable.Columns.Contains(Constants.ListCategoryName))
{
dataTable.Columns.Add(Constants.ListCategoryName, Type.GetType("System.String"));
}
if (!dataTable.Columns.Contains(Constants.ListName))
{
dataTable.Columns.Add(Constants.ListName, Type.GetType("System.String"));
}
if (items != null && items.Count > 0)
{
string imagepath = string.Empty;
ImageFieldValue imageIcon;
SPFieldUrlValue advLink;
for (int i = 0; i < items.Count; i++)
{
var listUrl = items.List.RootFolder.ServerRelativeUrl.Split('/');
dataTable.Rows[i][Constants.ListName] = listUrl[listUrl.Length - 1];
SPFieldLookup catFile = (SPFieldLookup)items.List.Fields.GetFieldByInternalName(FieldsName.NewsRecord.English.CategoryName);
listUrl = SPContext.Current.Web.Lists.GetList(new Guid(catFile.LookupList), true).RootFolder.ServerRelativeUrl.Split('/');
dataTable.Rows[i][Constants.ListCategoryName] = listUrl[listUrl.Length - 1];
imagepath = Convert.ToString(items[i][FieldsName.NewsRecord.English.ThumbnailImage]);
imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue;
if (imageIcon != null)
{
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imageIcon.ImageUrl;
}
else
{
if (imagepath.Length > 2)
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2);
else
{
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath;
}
}
if (items[i].Fields.ContainsField(FieldsName.NewsRecord.English.LinkAdv))
{
advLink = new SPFieldUrlValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.LinkAdv]));
dataTable.Rows[i][FieldsName.NewsRecord.English.LinkAdv] = advLink.Url;
}
dataTable.Rows[i][FieldsName.NewsRecord.English.ShortContent] = StripHtml(Convert.ToString(dataTable.Rows[i][FieldsName.NewsRecord.English.ShortContent]));
if (!string.IsNullOrEmpty(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName])))
{
SPFieldLookupValue catLK = new SPFieldLookupValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName]));
dataTable.Rows[i][FieldsName.CategoryId] = catLK.LookupId;
}
}
}
return dataTable;
}
/// <summary>
///
/// </summary>
/// <param name="items"></param>
/// <param name="sapo"></param>
/// <returns></returns>
public static DataTable GetTableWithCorrectUrl(SPListItemCollection items, bool sapo)
{
var dataTable = items.GetDataTable();
if (items != null && items.Count > 0)
{
string imagepath = string.Empty;
ImageFieldValue imageIcon;
for (int i = 0; i < items.Count; i++)
{
imagepath = Convert.ToString(items[i][FieldsName.NewsRecord.English.ThumbnailImage]);
imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue;
dataTable.Rows[i][FieldsName.Title] = GetTextForSapo(Convert.ToString(items[i][FieldsName.Title]), 55);
if (imageIcon != null)
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imageIcon.ImageUrl;
else
{
if (imagepath.Length > 2)
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2);
else
{
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath;
}
}
}
}
return dataTable;
}
/// <summary>
/// Get and return table with correct url
/// </summary>
/// <param name="categoryListName">categoryListName</param>
/// <param name="items"></param>
/// <returns></returns>
public static DataTable GetTableWithCorrectUrl(string categoryListName, SPListItemCollection items)
{
var dataTable = items.GetDataTable();
if (!dataTable.Columns.Contains(FieldsName.CategoryId))
{
dataTable.Columns.Add(FieldsName.CategoryId, Type.GetType("System.String"));
}
if (!dataTable.Columns.Contains(FieldsName.ArticleStartDateTemp))
{
dataTable.Columns.Add(FieldsName.ArticleStartDateTemp, Type.GetType("System.String"));
}
if (items != null && items.Count > 0)
{
string imagepath = string.Empty;
ImageFieldValue imageIcon;
SPFieldUrlValue advLink;
DateTime time = new DateTime();
for (int i = 0; i < items.Count; i++)
{
imagepath = Convert.ToString(items[i][FieldsName.NewsRecord.English.ThumbnailImage]);
imageIcon = items[i][FieldsName.NewsRecord.English.PublishingPageImage] as ImageFieldValue;
if (imageIcon != null)
{
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = GetThumbnailImagePath(imageIcon.ImageUrl);
}
else
{
if (imagepath.Length > 2)
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath.Trim().Substring(0, imagepath.Length - 2);
else
{
dataTable.Rows[i][FieldsName.NewsRecord.English.ThumbnailImage] = imagepath;
}
}
if (items[i].Fields.ContainsField(FieldsName.NewsRecord.English.LinkAdv))
{
advLink = new SPFieldUrlValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.LinkAdv]));
dataTable.Rows[i][FieldsName.NewsRecord.English.LinkAdv] = advLink.Url;
}
if (!string.IsNullOrEmpty(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName])))
{
SPFieldLookupValue catLK = new SPFieldLookupValue(Convert.ToString(items[i][FieldsName.NewsRecord.English.CategoryName]));
dataTable.Rows[i][FieldsName.CategoryId] = catLK.LookupId;
}
time = Convert.ToDateTime(dataTable.Rows[i][FieldsName.ArticleStartDates]);
dataTable.Rows[i][FieldsName.ArticleStartDateTemp] = string.Format("Ngày {0}/{1}/{2}", time.Day, time.Month, time.Year);
}
}
return dataTable;
}
private DataTable GetDataSource(SPListItemCollection items)
{
DataTable reportDT = new DataTable();
SPWeb currWeb = SPContext.Current.Web;
DataTable detailsDT = currWeb.Lists[WorkflowListName.TravelDetails2].Items.GetDataTable();
TRReportItem trReportItem = new TRReportItem();
CommonUtil.logInfo(items.GetDataTable().AsEnumerable().Count().ToString());
CommonUtil.logInfo(detailsDT.AsEnumerable().Count().ToString());
var leftJoin = from parent in items.GetDataTable().AsEnumerable()
join child in detailsDT.AsEnumerable()
on parent[trReportItem.Title].AsString() equals child[trReportItem.Title].AsString() into Joined
from child in Joined.DefaultIfEmpty()
select new TRReportItem
{
Title = parent != null ? parent[trReportItem.Title].AsString() : string.Empty,
ChineseName = parent != null ? parent[trReportItem.ChineseName].AsString() : string.Empty,
Department = parent != null ? parent[trReportItem.Department].AsString() : string.Empty,
CostCenter = child != null ? child[trReportItem.CostCenter].AsString() : string.Empty,
TravelDateFrom = child != null ? child[trReportItem.TravelDateFrom].AsString() : string.Empty,
TravelDateTo = child != null ? child[trReportItem.TravelDateTo].AsString() : string.Empty,
TravelLocationFrom = child != null ? child[trReportItem.TravelLocationFrom].AsString() : string.Empty,
TravelLocationTo = child != null ? child[trReportItem.TravelLocationTo].AsString() : string.Empty
};
if (leftJoin.Any())
{
reportDT = leftJoin.AsDataTable();
}
return reportDT;
}