public spClient.ListItemCollection getSharePointData(string subProjectRiskUrl, string query)
{
spClient.ListItemCollection collectionList = null;
try
{
string[] validUrlApi = subProjectRiskUrl.Split(new string[] { "/Lists/" }, StringSplitOptions.None);
string newriskUrl = subProjectRiskUrl;
if (validUrlApi.Length != 0)
{
newriskUrl = validUrlApi[0] + "/";
}
using (spClient.ClientContext ctx = new spClient.ClientContext(newriskUrl))
{
var passWord = new SecureString();
foreach (var c in "intel@123")
{
passWord.AppendChar(c);
}
ctx.Credentials = new spClient.SharePointOnlineCredentials("*****@*****.**", passWord);
spClient.Web myWeb = ctx.Web;
spClient.List proList = myWeb.Lists.GetByTitle("Risk List");
spClient.CamlQuery myQuery = new spClient.CamlQuery();
myQuery.ViewXml = query;
collectionList = proList.GetItems(myQuery);
ctx.Load(collectionList);
ctx.ExecuteQuery();
}
}
catch (Exception e)
{
collectionList = null;
}
return(collectionList);
}
protected IEnumerable<Attivita> LeggiItemsResoconto()
{
using (var clientContext = TokenHelper.GetClientContextWithContextToken(AppUrl, ContextToken, Request.Url.Authority))
{
SP.List oList = clientContext.Web.Lists.GetByTitle("RegistrazioneAttivita");
SP.CamlQuery camlQuery = new SP.CamlQuery();
camlQuery.ViewXml = "<View><Query><Where><Geq><FieldRef Name='DataInizio'/>" +
"<Value Type='DateTime'><Today /></Value></Geq></Where></Query><RowLimit>100</RowLimit></View>";
SP.ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem);
clientContext.ExecuteQuery();
var proj = from i in collListItem.ToList()
select new Attivita()
{
Titolo = i["Title"] as string,
IdCommessa = Convert.ToInt32(i["Commessa"] ?? 0),
DataInizio = Convert.ToDateTime(i["DataInizio"]).ToLocalTime(),
DataFine = Convert.ToDateTime(i["DataFine"] ?? DateTime.Now).ToLocalTime()
};
return proj;
}
}
public object ExecuteQuery()
{
SP.CamlQuery query = new SP.CamlQuery();
query.ViewXml = CreateCSOMQuery();
return(query);
}
/// <summary>
/// UnLock a file
/// </summary>
/// <param name="name">file name</param>
public static void UnLockItem(string filename)
{
ClientContext clientContext = new ClientContext(Browser.BaseAddress);
List spList = clientContext.Web.Lists.GetByTitle("Documents");
Microsoft.SharePoint.Client.CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();
query.ViewXml = "<View>"
+ "<Query>"
+ "<Where><Eq><FieldRef Name='FileLeafRef'/><Value Type='File'>" + filename + "</Value></Eq></Where>"
+ "</Query>"
+ "</View>";
// execute the query
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
foreach (Microsoft.SharePoint.Client.ListItem listitem in listItems)
{
listitem.File.UndoCheckOut();
try { clientContext.ExecuteQuery(); }
catch
{
Thread.Sleep(4000);
clientContext.ExecuteQuery();
}
}
}
public void DeleteItemByQuery(string ListName, ICamlQuery Query)
{
try
{
SP.Web web = context.Web;
SP.List list = web.Lists.GetByTitle(ListName);
SP.CamlQuery query = (SP.CamlQuery)Query.ExecuteQuery();
SP.ListItemCollection items = list.GetItems(query);
context.Load(items);
context.ExecuteQuery();
if (items != null)
{
foreach (SP.ListItem item in items)
{
item.DeleteObject();
context.Load(item);
}
context.ExecuteQuery();
}
else
{
throw new Exception("Item does not exist");
}
}
catch (Exception ex)
{
throw new Exception("Error updating SharePoint list data: " + ex.Message);
}
}
/// <summary>
/// Lock a file
/// </summary>
/// <param name="name">file name</param>
public static void LockItem(string filename)
{
ClientContext clientContext = new ClientContext(Browser.BaseAddress);
string username = ConfigurationManager.AppSettings["UserName"];
string password = ConfigurationManager.AppSettings["Password"];
clientContext.Credentials = new System.Net.NetworkCredential(username, password);
List spList = clientContext.Web.Lists.GetByTitle("Documents");
Microsoft.SharePoint.Client.CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();
query.ViewXml = "<View>"
+ "<Query>"
+ "<Where><Eq><FieldRef Name='FileLeafRef'/><Value Type='File'>" + filename + "</Value></Eq></Where>"
+ "</Query>"
+ "</View>";
// execute the query
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
foreach (Microsoft.SharePoint.Client.ListItem listitem in listItems)
{
listitem.File.CheckOut();
try { clientContext.ExecuteQuery(); }
catch
{
Thread.Sleep(4000);
clientContext.ExecuteQuery();
}
}
}
public void GetTasks()
{
Tasks.Clear();
CLOM.List taskList = context.Web.Lists.GetByTitle("Tasks");
CLOM.CamlQuery query = new CLOM.CamlQuery();
taskListItems = taskList.GetItems(query);
context.Load(taskListItems);
context.ExecuteQueryAsync(onQuerySucceeded, onQueryFailed);
}
public static List <Employee> GetEnterEstimate(string month, string year, string listname, string siteUrl)
{
List <Employee> _returnEmployee = new List <Employee>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl))
{
if (context != null)
{
MSC.List SalesEstimateEmployee = context.Web.Lists.GetByTitle(listname);
MSC.ListItemCollectionPosition itemPosition = null;
string fromday = year + "-" + month + "-" + "1";
string Today = year + "-" + month + "31";
while (true)
{
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View><Query><Where>
<Eq><FieldRef Name='Date' />
<Value IncludeTimeValue='FLASE' Type='DateTime'>" + fromday + "</Value></Eq>";
camlQuery.ViewXml += @" </Where></Query>
<RowLimit>5000</RowLimit>
<FieldRef Name='ID'/>
<FieldRef Name='EmployeeName'/>
<FieldRef Name='Author'/>
<FieldRef Name='Created'/>
</View>";
MSC.ListItemCollection Items = SalesEstimateEmployee.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
_returnEmployee.Add(new Employee
{
EmployeeName = Convert.ToString((item["EmployeeName"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
EmployeeID = Convert.ToString((item["EmployeeName"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId)
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
}
return(_returnEmployee);
}
public static SP.ListItemCollection GetViewItemsFromList(SP.ClientContext context, SP.List list, SP.View view)
{
SP.ListItemCollection items = null;
SP.CamlQuery query = new SP.CamlQuery();
query.ViewXml = view.ViewQuery;
items = list.GetItems(query);
context.Load(items);
context.ExecuteQuery();
return(items);
}
//public static List<PurchaseOrder> GetAll_PurchaseOrderFromSharePoint(string siteUrl, string listName, string DaysDifference)
//{
// List<PurchaseOrder> _retList = new List<PurchaseOrder>();
// try
// {
// using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl))
// {
// if (context != null)
// {
// MSC.List list = context.Web.Lists.GetByTitle(listName);
// MSC.ListItemCollectionPosition itemPosition = null;
// while (true)
// {
// var dataDateValue = DateTime.Now.AddDays(-Convert.ToInt32(DaysDifference));
// MSC.CamlQuery camlQuery = new MSC.CamlQuery();
// camlQuery.ListItemCollectionPosition = itemPosition;
// camlQuery.ViewXml = @"<View>
// <Query>
// <Where>
// <And>
// <Or>
// <Or>
// <Eq>
// <FieldRef Name='ApprovalStatus'/>
// <Value Type='text'>Submitted</Value>
// </Eq>
// <Eq>
// <FieldRef Name='ApprovalStatus'/>
// <Value Type='text'>Approved By Functional Head</Value>
// </Eq>
// </Or>
// <Or>
// <Eq>
// <FieldRef Name='ApprovalStatus'/>
// <Value Type='text'>Approved By Purchase Head</Value>
// </Eq>
// <Eq>
// <FieldRef Name='ApprovalStatus'/>
// <Value Type='text'>Approved By Plant Head</Value>
// </Eq>
// </Or>
// </Or>
// <Leq><FieldRef Name='Modified'/><Value Type='DateTime'>" + dataDateValue.ToString("o") + "</Value></Leq>";
// camlQuery.ViewXml += @"</And></Where></Query>
// <RowLimit>5000</RowLimit>
// <ViewFields>
// <FieldRef Name='ID'/>
// <FieldRef Name='POReferenceNumber'/>
// <FieldRef Name='Author'/>
// <FieldRef Name='Created'/>
// <FieldRef Name='DepartmentName'/>
// <FieldRef Name='DepartmentID'/>
// <FieldRef Name='LocationName'/>
// <FieldRef Name='LocationID'/>
// <FieldRef Name='LocationType'/>
// <FieldRef Name='DivisionName'/>
// <FieldRef Name='DivisionID'/>
// <FieldRef Name='PONumber'/>
// <FieldRef Name='POCost'/>
// <FieldRef Name='MaterialDetails'/>
// <FieldRef Name='ApprovalStatus'/>
// <FieldRef Name='FHCode'/>
// <FieldRef Name='Modified'/>
// </ViewFields></View>";
// MSC.ListItemCollection Items = list.GetItems(camlQuery);
// context.Load(Items);
// context.ExecuteQuery();
// itemPosition = Items.ListItemCollectionPosition;
// foreach (MSC.ListItem item in Items)
// {
// _retList.Add(new PurchaseOrder
// {
// Id = Convert.ToInt32(item["ID"]),
// POReferenceNumber = Convert.ToString(item["POReferenceNumber"]).Trim(),
// Author = Convert.ToString((item["Author"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
// Created = Convert.ToString(item["Created"]).Trim(),
// DepartmentName = Convert.ToString(item["DepartmentName"]).Trim(),
// DepartmentID = Convert.ToString(item["DepartmentID"]).Trim(),
// LocationName = Convert.ToString(item["LocationName"]).Trim(),
// LocationID = Convert.ToString(item["LocationID"]).Trim(),
// LocationType = Convert.ToString(item["LocationType"]).Trim(),
// ////Author = Convert.ToString((item["Author"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
// //Author = Convert.ToString((item["Author"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
// //FunctionalHead = Convert.ToString((item["FunctionalHead"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
// //HRHead = Convert.ToString((item["HRHead"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
// //HRHeadOnly = Convert.ToString((item["HRHeadOnly"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
// //MDorJMD = item["MDorJMD"] == null ? "" : Convert.ToString((item["MDorJMD"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
// //Recruiter = Convert.ToString((item["Recruiter"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
// DivisionName = Convert.ToString(item["DivisionName"]).Trim(),
// DivisionID = Convert.ToString(item["DivisionID"]).Trim(),
// PONumber = Convert.ToString(item["PONumber"]).Trim(),
// POCost = Convert.ToString(item["POCost"]).Trim(),
// MaterialDetails = Convert.ToString(item["MaterialDetails"]).Trim(),
// ApprovalStatus = Convert.ToString(item["ApprovalStatus"]).Trim(),
// FHCode = Convert.ToString(item["FHCode"]).Trim(),
// Modified = Convert.ToString(item["Modified"]).Trim(),
// });
// }
// if (itemPosition == null)
// {
// break; // TODO: might not be correct. Was : Exit While
// }
// }
// }
// }
// }
// catch (Exception ex)
// {
// CustomSharePointUtility.WriteLog("Error in GetAll_ManpowerRequisitionFromSharePoint()" + " Error:" + ex.Message);
// }
// return _retList;
//}
#endregion
/// New Code to Get Employee
///
public static List <Employee> GetEmployees(string siteUrl, string listName)
{
List <Employee> _returnEmployee = new List <Employee>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl))
{
if (context != null)
{
MSC.List HODList = context.Web.Lists.GetByTitle(listName);
MSC.ListItemCollectionPosition itemPosition = null;
while (true)
{
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View><Query><Where>
<Eq><FieldRef Name='Reminder' /><Value Type='Choice'>Yes</Value></Eq>
</Where></Query>
<RowLimit>5000</RowLimit>
<FieldRef Name='ID'/>
<FieldRef Name='EmployeeName'/>
<FieldRef Name='Author'/>
<FieldRef Name='Created'/>
</View>";
MSC.ListItemCollection Items = HODList.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
_returnEmployee.Add(new Employee
{
EmployeeName = Convert.ToString((item["EmployeeName"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
EmployeeID = Convert.ToString((item["EmployeeName"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId)
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
}
return(_returnEmployee);
}
static Microsoft.SharePoint.Client.ListItemCollection GetSpListItems(Microsoft.SharePoint.Client.ClientContext ctx, string siteUrl, string listName, string camlXml)//string[] listFields)
{
Microsoft.SharePoint.Client.List list = ctx.Web.Lists.GetByTitle(listName);
Microsoft.SharePoint.Client.CamlQuery camlQuery = new Microsoft.SharePoint.Client.CamlQuery()
{
ViewXml = camlXml
};
Microsoft.SharePoint.Client.ListItemCollection lic = list.GetItems(camlQuery);
ctx.Load(lic);
ctx.ExecuteQuery();
return(lic);
}
// load the product categories
private void LoadProductCategories(object state)
{
SP.List categoryList = Globals.ClientContext.Web.Lists.GetByTitle("Product Categories");
SP.CamlQuery query = new SP.CamlQuery();
query.ViewXml = "<View><Query><OrderBy><FieldRef Name='Title' /></OrderBy></Query></View>";
Globals.ProductCategories = categoryList.GetItems(query);
Globals.ClientContext.Load(Globals.ProductCategories);
Globals.ClientContext.ExecuteQuery();
// fire the UI work on another thread
this.Dispatcher.BeginInvoke(new Action(LoadProductCategoriesUIUpdater), DispatcherPriority.Normal);
}
public static List <ErrorModel> GetActiveErrorList(string siteUrl, string listName)
{
List <ErrorModel> errorModels = new List <ErrorModel>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl))
{
if (context != null)
{
MSC.List list = context.Web.Lists.GetByTitle(listName);
MSC.ListItemCollectionPosition itemPosition = null;
while (true)
{
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View><Query><Where><Eq><FieldRef Name='SystemUpdated' /><Value Type='Choice'>No</Value></Eq></Where></Query><ViewFields><FieldRef Name='UserDisplayName' /><FieldRef Name='DisplayNo' /><FieldRef Name='URL' /><FieldRef Name='SystemUpdated' /><FieldRef Name='ID' /></ViewFields><QueryOptions /></View>";
MSC.ListItemCollection Items = list.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
errorModels.Add(new ErrorModel
{
ID = Convert.ToInt32(item["ID"]),
URL = Convert.ToString(item["URL"]).Trim(),
SystemUpdate = Convert.ToString(item["SystemUpdated"]).Trim(),
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
}
return(errorModels);
}
public override int GetRecordCount(string tableName)
{
using (var context = GetClientContext(ConnectionDetails))
{
var site = context.Web;
var list = context.Web.Lists.GetByTitle(tableName);
var camlQuery = new SP.CamlQuery();
// retrieve only one field, to make the query as small and quick as possible.
camlQuery.ViewXml = "<View><ViewFields><FieldRef Name='Title'/></ViewFields></View>";
var listItems = list.GetItems(camlQuery);
context.Load(listItems);
context.ExecuteQuery();
return(listItems.Count);
}
}
private void LoadItems(SP.ClientContext ctx, SP.List list, SP.View view)
{
SP.CamlQuery query = new SP.CamlQuery();
query.ViewXml = "<View>" + view.ViewQuery + "<RowLimit>" + txtResultsNumber.Text + "</RowLimit></View>";
var items = list.GetItems(query);
ctx.Load(items, itms => itms.ListItemCollectionPosition
, itms => itms.Include(
itm => itm.FieldValuesAsText
, itm => itm.ContentType
)
);
ctx.ExecuteQuery();
progressBar1.Value++;
DataTable tbl = new DataTable();
DataColumnCollection columns = tbl.Columns;
DataRowCollection rows = tbl.Rows;
if (items != null && items.Count > 0)
{
columns.Add("N.", typeof(System.String));
columns.Add("ContentType", typeof(System.String));
foreach (var fld in items[0].FieldValuesAsText.FieldValues)
{
columns.Add(fld.Key, typeof(System.String));
}
int i = 1;
foreach (var item in items)
{
DataRow row = tbl.NewRow();
row["N."] = i;
row["ContentType"] = item.ContentType.Name;
foreach (var fld in item.FieldValuesAsText.FieldValues)
{
if (fld.Value != null)
{
row[fld.Key] = item.FieldValuesAsText[fld.Key];
}
}
rows.Add(row);
i++;
}
}
dgResults.AutoGenerateColumns = true;
dgResults.AutoSize = true;
dgResults.DataSource = tbl;
}
private void OnProductChangedWorker(object selectedProduct)
{
SP.ListItem product = (SP.ListItem)selectedProduct;
// get product matching this query
SP.CamlQuery query = new SP.CamlQuery();
query.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Title'/><Value Type='Text'>" + product["Title"] + "</Value></Eq></Where></Query></View>";
SP.List productList = Globals.ClientContext.Web.Lists.GetByTitle("Products");
_products = productList.GetItems(query);
Globals.ClientContext.Load(_products);
Globals.ClientContext.ExecuteQuery();
// fire the UI work on another thread
this.Dispatcher.BeginInvoke(new Action(OnProductChangedUIUpdater), DispatcherPriority.Normal);
}
protected void Page_Load(object sender, EventArgs e)
{
// The following code gets the client context and Title property by using TokenHelper.
// To access other properties, you may need to request permissions on the host web.
if (!Page.IsPostBack)
{
using (var clientContext = TokenHelper.GetClientContextWithContextToken(AppUrl, ContextToken, Request.Url.Authority))
{
//clientContext.Load(clientContext.Web, web => web.Title);
//clientContext.ExecuteQuery();
//Response.Write(clientContext.Web.Title);
//clientContext.ToString();
SP.List oList = clientContext.Web.Lists.GetByTitle("Commesse");
SP.CamlQuery camlQuery = new SP.CamlQuery();
camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Visualizzabile'/>" +
"<Value Type='Boolean'>1</Value></Eq></Where></Query><RowLimit>100</RowLimit></View>";
SP.ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem);
clientContext.ExecuteQuery();
List<Commessa> commesse = new List<Commessa>();
foreach (var item in collListItem)
{
var commessa = new Commessa()
{
Id = item.Id,
Titolo = item["Title"].ToString(),
Colore = Color.FromName(item["Colore"] as string ?? "Red")
};
commesse.Add(commessa);
}
lvCommesse.DataSource = commesse;
lvCommesse.DataBind();
}
// Lo metto solo quando faccio la postback perchè quando clicco sul button lo setto direttamente
// nell'handler dell'evento
var lastItem = LeggiUltimaAttivita();
VisualizzaAttivita(lastItem);
}
}
public void UpdateItemByQuery(string ListName, TEntity Item, ICamlQuery Query)
{
try
{
SP.Web web = context.Web;
SP.List list = web.Lists.GetByTitle(ListName);
SP.CamlQuery query = (SP.CamlQuery)Query.ExecuteQuery();
SP.ListItemCollection items = list.GetItems(query);
context.Load(items);
context.ExecuteQuery();
if (items != null)
{
foreach (SP.ListItem item in items)
{
var properties = typeof(TEntity).GetProperties();
foreach (var property in properties)
{
if (property.Name != "ID" && property.Name != "Created" && property.Name != "Author")
{
item[property.Name] = property.GetValue(Item);
}
}
item.Update();
context.Load(item);
}
context.ExecuteQuery();
}
else
{
throw new Exception("Item does not exist");
}
}
catch (InvalidCastException ex)
{
throw new Exception(ex.Message + ". Data model types are invalid, please verify that models are strongly typed and match the SharePoint list column types.", new InvalidCastException());
}
catch (Exception ex)
{
throw new Exception("Error updating SharePoint list data: " + ex.Message);
}
}
public IEnumerable <TEntity> GetItemsByQuery(string ListName, ICamlQuery Query)
{
try
{
List <TEntity> entityItems = new List <TEntity>();
SP.Web web = context.Web;
SP.List list = web.Lists.GetByTitle(ListName);
SP.CamlQuery query = (SP.CamlQuery)Query.ExecuteQuery();
SP.ListItemCollection items = list.GetItems(query);
context.Load(items);
context.ExecuteQuery();
foreach (SP.ListItem item in items)
{
var properties = typeof(TEntity).GetProperties();
TEntity entry = new TEntity();
foreach (var property in properties)
{
if (item[property.Name] != null)
{
var i = item[property.Name];
property.SetValue(entry, item[property.Name]);
}
}
entityItems.Add(entry);
}
return(entityItems);
}
catch (InvalidCastException ex)
{
throw new Exception(ex.Message + ". Data model types are invalid, please verify that models are strongly typed and match the SharePoint list column types.", new InvalidCastException());
}
catch (Exception ex)
{
throw new Exception("Error updating SharePoint list data: " + ex.Message);
}
}
public TEntity GetItemById(string ListName, int Id)
{
try
{
TEntity entry = new TEntity();
SP.Web web = context.Web;
SP.List list = web.Lists.GetByTitle(ListName);
SP.CamlQuery query = new SP.CamlQuery();
query.ViewXml = @"<View>
<Query>
<Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>" + Id + @"</Value></Eq></Where>
</Query>
</View>";
SP.ListItemCollection items = list.GetItems(query);
context.Load(items);
context.ExecuteQuery();
if (items.Count > 0)
{
SP.ListItem item = items[0];
var properties = typeof(TEntity).GetProperties();
foreach (var property in properties)
{
if (item[property.Name] != null)
{
property.SetValue(entry, item[property.Name]);
}
}
}
return(entry);
}
catch (InvalidCastException ex)
{
throw new Exception(ex.Message + ". Data model types are invalid, please verify that models are strongly typed and match the SharePoint list column types.", new InvalidCastException());
}
catch (Exception ex)
{
throw new Exception("Error updating SharePoint list data: " + ex.Message);
}
}
private IEnumerator <Record> GetRecordsEnumeratorInternal(string tableName, IEnumerable <Field> fields)
{
using (var context = GetClientContext(ConnectionDetails))
{
var site = context.Web;
var list = context.Web.Lists.GetByTitle(tableName);
var spFields = context.LoadQuery(list.Fields);
const string FIELD_REF_FORMAT = "<FieldRef Name='{0}'/>";
var sb = new StringBuilder(200);
foreach (var field in fields)
{
sb.AppendFormat(FIELD_REF_FORMAT, field.Name);
}
var camlQuery = new SP.CamlQuery();
camlQuery.ViewXml = string.Format("<View><ViewFields>{0}</ViewFields></View>", sb.ToString());
var listItems = list.GetItems(camlQuery);
context.Load(listItems);
context.ExecuteQuery();
foreach (var item in listItems)
{
var record = new Record();
record.Fields.AddRange(fields);
foreach (var field in fields)
{
var spField = spFields.SingleOrDefault(f => f.InternalName == field.Name);
if (spField != null)
{
field.Value = item[spField.InternalName];
}
}
yield return(record);
}
}
}
internal static SP.CamlQuery ToSPCamlRequest(this SPCamlQuery query, string pagingInfo = null)
{
var camlQuery = new SP.CamlQuery
{
DatesInUtc = query.DatesInUtc,
ViewXml = query.ViewXml
};
if (!string.IsNullOrEmpty(query.FolderPath))
{
camlQuery.FolderServerRelativeUrl = query.FolderPath;
}
if (!string.IsNullOrEmpty(pagingInfo))
{
camlQuery.ListItemCollectionPosition = new SP.ListItemCollectionPosition {
PagingInfo = pagingInfo
};
}
return(camlQuery);
}
/// <summary>
/// Delete folder on site
/// </summary>
/// <param name="filename">folder name</param>
public static void DeleteFolder(string foldername)
{
ClientContext clientContext = new ClientContext(Browser.BaseAddress);
List spList = clientContext.Web.Lists.GetByTitle("Documents");
Microsoft.SharePoint.Client.CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();
query.ViewXml = "<View>"
+ "<Query>"
+ "<Where><Eq><FieldRef Name='FileLeafRef'/><Value Type='Folder'>" + foldername + "</Value></Eq></Where>"
+ "</Query>"
+ "</View>";
// execute the query
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
foreach (Microsoft.SharePoint.Client.ListItem listitem in listItems)
{
listitem.DeleteObject();
clientContext.ExecuteQuery();
}
}
public MainPage()
{
InitializeComponent();
ClientContext context = new ClientContext(ApplicationContext.Current.Url);
context.Load(context.Web);
List Projects = context.Web.Lists.GetByTitle("Projects");
context.Load(Projects);
CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();
string camlQueryXml = "<View><Query><Where><Gt>" +
"<FieldRef Name='Due_x0020_Date' />" +
"<Value Type='DateTime'>2008-01-1T00:00:00Z</Value>" +
"</Gt></Where></Query><ViewFields>" +
"<FieldRef Name=\"Title\" /><FieldRef Name=\"Description\" />" +
"<FieldRef Name=\"Due_x0020_Date\" />" +
"</ViewFields></View>";
query.ViewXml = camlQueryXml;
_projects = Projects.GetItems(query);
context.Load(_projects);
context.ExecuteQueryAsync(new ClientRequestSucceededEventHandler(OnRequestSucceeded), null);
}
public IEnumerable <TItem> GetAll <TItem>(Func <ListItem, TItem> geneateTItem) where TItem : BaseItem
{
Microsoft.SharePoint.Client.CamlQuery query = new Microsoft.SharePoint.Client.CamlQuery();
query.ViewXml = "<View>"
+ "<Query>"
+ string.Format(CamlQueryConstants.OrderPatern, FieldInternalNames.ID, CamlQueryConstants.SPTrueValue)
+ "</Query>" +
"</View>";
ListItemCollection listItems = _oList.GetItems(query);
_clientContext.Load(listItems);
_clientContext.ExecuteQuery();
IList <TItem> result = new List <TItem>();
foreach (var item in listItems)
{
if (item != null)
{
result.Add(geneateTItem(item));
}
}
return(result.ToArray());
}
private void button_Click(object sender, RoutedEventArgs e)
{
Login_WPF.Login LoginDialog = new Login_WPF.Login();
LoginDialog.ShowDialog();
using (ClientContext cont = new ClientContext("https://inblago.sharepoint.com"))
{
string login = LoginDialog.tb_Email.Text;;
SecureString pass = LoginDialog.tb_password.SecurePassword;
// SecureString pass = new SecureString();
// foreach (char c in "password".ToCharArray()) pass.AppendChar(c);
cont.Credentials = new SharePointOnlineCredentials(login, pass);
Web oWeb = cont.Web;
cont.Load(oWeb);
cont.Load(cont.Web.CurrentUser);
List tasks = oWeb.Lists.GetByTitle("Tasks");
cont.ExecuteQuery();
CamlQuery caml = new Microsoft.SharePoint.Client.CamlQuery();
caml.ViewXml = string.Format(@"<View Scope='RecursiveAll'>
<Query><Where><And>
<Eq><FieldRef Name = 'AssignedTo' LookupId = 'TRUE' /><Value Type = 'Lookup' >{0}</Value></Eq>
<Neq><FieldRef Name = 'PercentComplete' /><Value Type='Number' >1.00</Value></Neq>
</And></Where></Query></View>", cont.Web.CurrentUser.Id);
ListItemCollection items = tasks.GetItems(caml);
items.RetrieveItems().Retrieve();
cont.ExecuteQuery();
// foreach (List lst in oWeb.Lists)
foreach (ListItem item in items)
{
tb_Log.AppendText(item.FieldValues["Title"] + "\n");
}
}
}
private void button1_Click(object sender, EventArgs e)
{
using (ClientContext cont = new ClientContext("https://inblago.sharepoint.com"))
{
SecureString pass = new SecureString();
foreach (char c in "password".ToCharArray()) pass.AppendChar(c);
cont.Credentials = new SharePointOnlineCredentials("login", pass);
Web oWeb = cont.Web;
cont.Load(oWeb);
List tasks = oWeb.Lists.GetByTitle("Tasks");
oWeb.Lists.RetrieveItems().Retrieve();
CamlQuery caml = new Microsoft.SharePoint.Client.CamlQuery();
caml.ViewXml = "<View Scope='RecursiveAll' />";
ListItemCollection items = tasks.GetItems(caml);
items.RetrieveItems().Retrieve();
cont.ExecuteQuery();
// foreach (List lst in oWeb.Lists)
foreach (ListItem item in items)
{
tbLog.AppendText(item.FieldValues["Title"]+"\n");
}
}
}
private SP.ListItem LeggiUltimaAttivita()
{
using (var clientContext = TokenHelper.GetClientContextWithContextToken(AppUrl, ContextToken, Request.Url.Authority))
{
SP.List oList = clientContext.Web.Lists.GetByTitle("RegistrazioneAttivita");
//Aggiorno l'ultima commessa
SP.CamlQuery camlQuery = new SP.CamlQuery();
camlQuery.ViewXml = "<View><Query><OrderBy><FieldRef Name='ID' Ascending='False' />" +
"</OrderBy></Query><RowLimit>1</RowLimit></View>";
SP.ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem);
clientContext.ExecuteQuery();
var lastItem = collListItem.ToList().LastOrDefault();
return lastItem;
}
}
public void readData(string listTitle)
{
try
{
Web web = mClientContext.Web;
SP.CamlQuery myQuery = new SP.CamlQuery();
SP.List projList = mClientContext.Web.Lists.GetByTitle(listTitle);
//myQuery.ViewXml = " < Where > < Eq > < FieldRef Name = 'Estimated_x0020_Start_x0020_Time' /> <Value Type = 'Text'> 9:30 </Value> </ Eq > </ Where ></ View >";
//myQuery.ViewXml = " < Where > < Eq > < FieldRef Name = 'Milestone_x0020_Number' /> <Value Type = 'Text'> 2 </Value> </ Eq > </ Where ></ View >";
SP.ListItemCollection collectItems = projList.GetItems(myQuery);
//SP.ListItemCollection collectItems = projList.GetItems(CamlQuery.CreateAllItemsQuery());
mClientContext.Load(collectItems);
mClientContext.ExecuteQuery();
int count = collectItems.Count;
Dictionary <int, Milestone> milestoneDict = mProg.getMilestoneObjMap();
bool errorCheck = this.mEmailError.getErrorCheck();
int sizeOfMap = mProg.getMilestoneObjMap().Count;
int minInputKey = 0;
if (milestoneDict.Count > 0)
{
minInputKey = milestoneDict.Keys.Min();
Console.WriteLine("largest key in map before entering for loop is: " + minInputKey);
}
int maxNumInTable = 0;
//error code = -1 means default, no error has occurred so far
//error code = 0 means success, continue
//error code = 1 is milestone already exists
//error code = 2 is milestone is too large
//so far that is the system
foreach (SP.ListItem oItem in collectItems)
{
string milestoneStrInTable = (string)oItem["Milestone_x0020_Number"];
int milestoneNumInTable = Convert.ToInt32(milestoneStrInTable);
Console.WriteLine("key in table is: " + milestoneNumInTable);
milestoneDict.TryGetValue(milestoneNumInTable, out Milestone currMilestone); //result holds the milestone that is correlated with the key
if (currMilestone != null)
{
string command = currMilestone.getCommand().Trim();
if (milestoneNumInTable > maxNumInTable)
{
maxNumInTable = milestoneNumInTable;
}
if (milestoneDict.ContainsKey(milestoneNumInTable))
{
//break when error code is not default and not success -- means error occurred
if (this.mEmailError.getErrorCode() != 0 && this.mEmailError.getErrorCode() != -1)
{
break;
}
else
{
if (command == "Add")
{
this.mEmailError.setErrorCheck(true);
}
milestoneCommandHandler(projList, oItem, currMilestone);
//Console.WriteLine("value of command is: " + command);
//if (command == "Add")
//{
// this.mEmailError.setErrorCheck(true);
// mEmailError.sendMilestoneAlreadyExistsErrorEmail();
// this.mEmailError.setErrorCode(1);
//}
//else if (command == "Update")
//{
// oItem["Milestone_x0020_Comment"] = currMilestone.getComment();
// this.mEmailError.setErrorCode(0);
//}
//else if (command == "Remove")
//{
// oItem["Milestone_x0020_Comment"] = "";
// this.mEmailError.setErrorCode(0);
//}
//oItem.Update();
}
//Error duplicate key, send email back
}
//else
//{
// if (command == "Add")
// {
// this.milestonesWithAddCommands.Add(currMilestone.getNumber());
// }
//}
}
//need to check if table doesn't have key b/c user may input milestone to update that does not exist
}
//ADD THIS ERROR BACK IN IN IN ININI!!!!!!!!
if (minInputKey > maxNumInTable + 1 && this.mIsNewList == false && this.mEmailError.getErrorCode() == -1)
{
this.mEmailError.setErrorCheck(true);
this.mEmailError.setErrorCode(2);
Console.WriteLine("ERROR 2 --- TOO LARGE");
Console.WriteLine("what is the max input key? " + minInputKey + " and the max in table is: " + maxNumInTable);
string mailboxAddress = mProg.getMailItem().mMailboxAddress;
mEmailError.sendMilestoneTooLargeErrorEmail(mailboxAddress);
//Error, too large of a milestone inputted
}
if (!this.mEmailError.getErrorCheck())
{
mClientContext.ExecuteQuery();
//addAllData(listTitle);
}
}
catch (CollectionNotInitializedException cnie)
{
if (cnie.Message.Contains("The collection has not been initialized"))
{
Console.WriteLine("WEL WLE EWLL");
}
}
catch (InvalidOperationException ioe)
{
Console.WriteLine(ioe);
}
}
public string createProjectReport(string listTitle)
{
try
{
Web web = mClientContext.Web;
SP.CamlQuery myQuery = new SP.CamlQuery();
SP.List projList = mClientContext.Web.Lists.GetByTitle(listTitle);
//myQuery.ViewXml = " < Where > < Eq > < FieldRef Name = 'Estimated_x0020_Start_x0020_Time' /> <Value Type = 'Text'> 9:30 </Value> </ Eq > </ Where ></ View >";
//myQuery.ViewXml = " < Where > < Eq > < FieldRef Name = 'Milestone_x0020_Number' /> <Value Type = 'Text'> 2 </Value> </ Eq > </ Where ></ View >";
//SP.ListItemCollection collectItems = projList.GetItems(myQuery);
//myQuery.ViewXml = "< OrderBy > < FieldRef Name = 'Title' Ascending = 'False' /> </ OrderBy >";
SP.ListItemCollection collectItems = projList.GetItems(CamlQuery.CreateAllItemsQuery());
mClientContext.Load(collectItems);
mClientContext.ExecuteQuery();
int count = collectItems.Count;
string fullReport = "";
string estimatedStartTime = "";
string estimatedStartDate = "";
string estimatedEndTime = "";
string estimatedEndDate = "";
string actualStartTime = "";
string actualEndTime = "";
string actualStartDate = "";
string actualEndDate = "";
Dictionary <string, string> milestoneNumAndComment = new Dictionary <string, string>();
string milestoneComment = "";
string timeSpent = "";
string resources = "";
string currentStatus = "";
string currentStatusReason = "";
try
{
foreach (SP.ListItem oItem in collectItems)
{
if ((string)oItem["Estimated_x0020_Start_x0020_Time"] != null)
{
estimatedStartTime = (string)oItem["Estimated_x0020_Start_x0020_Time"];
}
if ((string)oItem["Estimated_x0020_Start_x0020_Date"] != null)
{
estimatedStartDate = (string)oItem["Estimated_x0020_Start_x0020_Date"];
}
if ((string)oItem["Estimated_x0020_End_x0020_Time"] != null)
{
estimatedEndTime = (string)oItem["Estimated_x0020_End_x0020_Time"];
}
if ((string)oItem["Estimated_x0020_End_x0020_Date"] != null)
{
estimatedEndDate = (string)oItem["Estimated_x0020_End_x0020_Date"];
}
if ((string)oItem["Actual_x0020_Start_x0020_Time"] != null)
{
actualStartTime = (string)oItem["Actual_x0020_Start_x0020_Time"];
}
if ((string)oItem["Actual_x0020_End_x0020_Time"] != null)
{
actualEndTime = (string)oItem["Actual_x0020_End_x0020_Time"];
}
if ((string)oItem["Actual_x0020_Start_x0020_Date"] != null)
{
actualStartDate = (string)oItem["Actual_x0020_Start_x0020_Date"];
}
if ((string)oItem["Actual_x0020_End_x0020_Date"] != null)
{
actualEndDate = (string)oItem["Actual_x0020_End_x0020_Date"];
}
if ((string)oItem["Milestone_x0020_Number"] != null)
{
milestoneNumAndComment.Add((string)oItem["Milestone_x0020_Number"], (string)oItem["Milestone_x0020_Comment"]);
}
//if ((string)oItem["Milestone_x0020_Comment"] != "")
//{
// milestoneComment = (string)oItem["Milestone_x0020_Comment"];
//}
if ((string)oItem["Time_x0020_Spent"] != null)
{
timeSpent = (string)oItem["Time_x0020_Spent"];
}
if ((string)oItem["Resources"] != null)
{
resources = (string)oItem["Resources"];
}
if ((string)oItem["Current_x0020_Status"] != null)
{
currentStatus = (string)oItem["Current_x0020_Status"];
}
if ((string)oItem["Current_x0020_Status_x0020_Reaso"] != null)
{
currentStatusReason = (string)oItem["Current_x0020_Status_x0020_Reaso"];
}
}
fullReport += "Here is all the data for this project: " + estimatedStartTime + " === " + estimatedEndTime + " === " + estimatedStartDate + " ==== "
+ estimatedEndDate + " === " + timeSpent + " === " + resources +
" === " + currentStatus + " === " + currentStatusReason + " -------- " + "\n" + "And here are the milestones and comments: \n";
foreach (var milestone in milestoneNumAndComment.OrderBy(i => i.Key))
{
fullReport += milestone.Key + " : " + milestone.Value + " \r\n";
}
return(fullReport);
}
catch (ArgumentNullException ane)
{
Console.WriteLine(ane);
}
Console.WriteLine(fullReport);
return(fullReport);
}
catch (CollectionNotInitializedException cnie)
{
if (cnie.Message.Contains("The collection has not been initialized"))
{
Console.WriteLine(cnie);
}
return("");
}
catch (InvalidOperationException ioe)
{
Console.WriteLine(ioe);
return("");
}
}
public static List <ExpenseVoucher> GetAll_TravelVoucherFromSharePoint(string siteUrl, string listName, string DaysDifference)
{
List <ExpenseVoucher> _retList = new List <ExpenseVoucher>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl))
{
if (context != null)
{
MSC.List list = context.Web.Lists.GetByTitle(listName);
MSC.ListItemCollectionPosition itemPosition = null;
while (true)
{
var dataDateValue = DateTime.Now.AddDays(-Convert.ToInt32(DaysDifference));
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
// Temp
//////camlQuery.ViewXml = @"<View>
////// <Query>
////// <Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>6118</Value></Eq></Where>
////// </Query>
////// <RowLimit>4000</RowLimit>
////// <ViewFields>
////// <FieldRef Name='ID'/>
////// <FieldRef Name='ExpVoucherNo'/>
////// <FieldRef Name='CreatorName'/>
////// <FieldRef Name='CreatorDepartment'/>
////// <FieldRef Name='CreatorLocation'/>
////// <FieldRef Name='Destination'/>
////// <FieldRef Name='VisitDate'/>
////// <FieldRef Name='StatusCode'/>
////// <FieldRef Name='StatusName'/>
////// <FieldRef Name='AssignUser'/>
////// <FieldRef Name='FunctionalHead'/>
////// <FieldRef Name='SequenceNo'/>
////// <FieldRef Name='CreationDate'/>
////// <FieldRef Name='AssignDate'/>
////// <FieldRef Name='TotalCost'/>
////// <FieldRef Name='EmployeeName'/>
////// <FieldRef Name='EmployeeNumber'/>
////// <FieldRef Name='Designation'/>
////// <FieldRef Name='ActionTaken'/>
////// <FieldRef Name='CompanyCode'/>
////// <FieldRef Name='DivisionName'/>
////// <FieldRef Name='Modified'/>
////// <FieldRef Name='CurrentApprove'/>
////// <FieldRef Name='AccountApprover'/>
////// <FieldRef Name='AccountUserApproved'/>
////// </ViewFields></View>";
camlQuery.ViewXml = @"<View>
<Query>
<Where>
<And>
<Eq>
<FieldRef Name='StatusCode'/>
<Value Type='Text'>P</Value>
</Eq>
<Leq><FieldRef Name='Modified'/><Value Type='DateTime'>" + dataDateValue.ToString("o") + "</Value></Leq>";
camlQuery.ViewXml += @"</And>
</Where>
</Query>
<RowLimit>4000</RowLimit>
<ViewFields>
<FieldRef Name='ID'/>
<FieldRef Name='ExpVoucherNo'/>
<FieldRef Name='CreatorName'/>
<FieldRef Name='CreatorDepartment'/>
<FieldRef Name='CreatorLocation'/>
<FieldRef Name='Destination'/>
<FieldRef Name='VisitDate'/>
<FieldRef Name='StatusCode'/>
<FieldRef Name='StatusName'/>
<FieldRef Name='AssignUser'/>
<FieldRef Name='FunctionalHead'/>
<FieldRef Name='SequenceNo'/>
<FieldRef Name='CreationDate'/>
<FieldRef Name='AssignDate'/>
<FieldRef Name='TotalCost'/>
<FieldRef Name='EmployeeName'/>
<FieldRef Name='EmployeeNumber'/>
<FieldRef Name='Designation'/>
<FieldRef Name='ActionTaken'/>
<FieldRef Name='CompanyCode'/>
<FieldRef Name='DivisionName'/>
<FieldRef Name='Modified'/>
<FieldRef Name='CurrentApprove'/>
<FieldRef Name='AccountApprover'/>
<FieldRef Name='AccountUserApproved' />
</ViewFields></View>";
MSC.ListItemCollection Items = list.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
_retList.Add(new ExpenseVoucher
{
Id = Convert.ToInt32(item["ID"]),
ExpVoucherNo = Convert.ToString(item["ExpVoucherNo"]).Trim(),
CreatorName = Convert.ToString((item["CreatorName"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
CreatorDepartment = Convert.ToString(item["CreatorDepartment"]).Trim(),
CreatorLocation = Convert.ToString(item["CreatorLocation"]).Trim(),
Destination = Convert.ToString(item["Destination"]).Trim(),
VisitDate = Convert.ToString(item["VisitDate"]).Trim(),
StatusCode = Convert.ToString(item["StatusCode"]).Trim(),
StatusName = Convert.ToString(item["StatusName"]),
AssignUser = item["AssignUser"] == null ? "" : Convert.ToString((item["AssignUser"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
FunctionalHead = item["FunctionalHead"] == null ? "" : Convert.ToString(item["FunctionalHead"]).Trim(),
CreationDate = Convert.ToString(item["CreationDate"]).Trim(),
AssignDate = Convert.ToString(item["AssignDate"]).Trim(),
SequenceNo = Convert.ToString(item["SequenceNo"]).Trim(),
EmployeeName = Convert.ToString(item["EmployeeName"]).Trim(),
EmployeeNumber = Convert.ToString(item["EmployeeNumber"]).Trim(),
Designation = Convert.ToString(item["Designation"]).Trim(),
ActionTaken = Convert.ToString(item["ActionTaken"]).Trim(),
CompanyCode = Convert.ToString(item["CompanyCode"]),
DivisionName = Convert.ToString(item["DivisionName"]),
Modified = Convert.ToString(item["Modified"]),
CurrentApprove = item["CurrentApprove"] == null ? "" : Convert.ToString((item["CurrentApprove"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
AccountApprover = item["AccountApprover"] == null ? "" : Convert.ToString((item["AccountApprover"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
AccountUserApproved = item["AccountUserApproved"] == null ? "" : Convert.ToString(item["AccountUserApproved"])
// AccountUserApproved = Convert.ToString(item["AccountUserApproved"])
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
CustomSharePointUtility.WriteLog("Error in GetAll_EmployeeDetailsFromSharePoint()" + " Error:" + ex.Message);
}
return(_retList);
}
public static List <PurchaseApprovers> PurchaseApproversData(PurchaseOrder purchaseDataFinal, string RootsiteUrl, string siteUrl, string listName, string EmaillistName)
{
List <PurchaseApprovers> _returnList = new List <PurchaseApprovers>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetEmpContext(RootsiteUrl))
{
if (context != null)
{
MSC.List list = context.Web.Lists.GetByTitle(listName);
MSC.ListItemCollectionPosition itemPosition = null;
while (true)
{
//var dataDateValue = DateTime.Now.AddDays(-Convert.ToInt32(DaysDifference));
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View>
<Query>
<Where>
<And>
<Eq>
<FieldRef Name='Division' LookupId='True'/>
<Value Type='Lookup'>" + purchaseDataFinal.DivisionID + "</Value></Eq>";
camlQuery.ViewXml += @"<Eq>
<FieldRef Name ='Location' LookupId='True'/>
<Value Type='Lookup'>" + purchaseDataFinal.LocationID + "</Value></Eq>";
camlQuery.ViewXml += @"</And></Where>
</Query>
<RowLimit>5000</RowLimit>
<ViewFields>
<FieldRef Name='ID'/>
<FieldRef Name='ApproverName'/>
<FieldRef Name='ApproverType'/>
</ViewFields></View>";
MSC.ListItemCollection Items = list.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
_returnList.Add(new PurchaseApprovers
{
ApproverName = item["ApproverName"] == null ? "" : Convert.ToString((item["ApproverName"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
ApproverType = Convert.ToString(item["ApproverType"]).Trim(),
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
CustomSharePointUtility.WriteLog("Error in PurchaseApproversData()" + " Error:" + ex.Message);
}
for (var i = 0; i < _returnList.Count; i++)
{
if (_returnList[i].ApproverType == "MD" || _returnList[i].ApproverType == "JMD")
{
String MDorJMD = _returnList[i].ApproverName;
var success = CustomSharePointUtility.EmailData(purchaseDataFinal, "", "", "", MDorJMD, siteUrl, EmaillistName);
if (success)
{
}
}
else if (_returnList[i].ApproverType == "PURCHASE HEAD")
{
String PurchaseHead = _returnList[i].ApproverName;
var success = CustomSharePointUtility.EmailData(purchaseDataFinal, "", PurchaseHead, "", "", siteUrl, EmaillistName);
if (success)
{
}
}
else if (_returnList[i].ApproverType == "PLANT HEAD")
{
String PlantHead = _returnList[i].ApproverName;
var success = CustomSharePointUtility.EmailData(purchaseDataFinal, "", "", PlantHead, "", siteUrl, EmaillistName);
if (success)
{
}
}
}
return(_returnList);
}
public static List <EmployeeMaster> EmployeeMasterData(PurchaseOrder purchaseDataFinal, string RootsiteUrl, string siteUrl, string listName, string EmaillistName)
{
List <EmployeeMaster> _returnList = new List <EmployeeMaster>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetEmpContext(RootsiteUrl))
{
if (context != null)
{
MSC.List list = context.Web.Lists.GetByTitle(listName);
MSC.ListItemCollectionPosition itemPosition = null;
while (true)
{
//var dataDateValue = DateTime.Now.AddDays(-Convert.ToInt32(DaysDifference));
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View>
<Query>
<Where>
<Eq>
<FieldRef Name='Employee_x0020_Code'/>
<Value Type='text'>" + purchaseDataFinal.FHCode + "</Value></Eq>";
camlQuery.ViewXml += @"</Where>
</Query>
<RowLimit>5000</RowLimit>
<ViewFields>
<FieldRef Name='ID'/>
<FieldRef Name='Employee_x0020_Code'/>
<FieldRef Name='Employee_x0020_Email'/>
</ViewFields></View>";
MSC.ListItemCollection Items = list.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
_returnList.Add(new EmployeeMaster
{
Employee_x0020_Code = Convert.ToString(item["Employee_x0020_Code"]).Trim(),
Employee_x0020_Email = Convert.ToString(item["Employee_x0020_Email"]).Trim(),
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
CustomSharePointUtility.WriteLog("Error in EmployeeMasterData()" + " Error:" + ex.Message);
}
var success = CustomSharePointUtility.EmailData(purchaseDataFinal, _returnList[0].Employee_x0020_Email, "", "", "", siteUrl, EmaillistName);
if (success)
{
}
return(_returnList);
}
public static List <PurchaseOrder> GetAll_PurchaseOrderFromSharePoint(string siteUrl, string listName, string DaysDifference)
{
List <PurchaseOrder> _retList = new List <PurchaseOrder>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl))
{
if (context != null)
{
MSC.List list = context.Web.Lists.GetByTitle(listName);
MSC.ListItemCollectionPosition itemPosition = null;
while (true)
{
var dataDateValue = DateTime.Now.AddDays(-Convert.ToInt32(DaysDifference));
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View>
<Query>
<Where>
<And>
<Or>
<Or>
<Eq>
<FieldRef Name='ApprovalStatus'/>
<Value Type='text'>Submitted</Value>
</Eq>
<Eq>
<FieldRef Name='ApprovalStatus'/>
<Value Type='text'>Approved By Functional Head</Value>
</Eq>
</Or>
<Or>
<Eq>
<FieldRef Name='ApprovalStatus'/>
<Value Type='text'>Approved By Purchase Head</Value>
</Eq>
<Eq>
<FieldRef Name='ApprovalStatus'/>
<Value Type='text'>Approved By Plant Head</Value>
</Eq>
</Or>
</Or>
<Leq><FieldRef Name='Modified'/><Value Type='DateTime'>" + dataDateValue.ToString("o") + "</Value></Leq>";
camlQuery.ViewXml += @"</And></Where></Query>
<RowLimit>5000</RowLimit>
<ViewFields>
<FieldRef Name='ID'/>
<FieldRef Name='POReferenceNumber'/>
<FieldRef Name='Author'/>
<FieldRef Name='Created'/>
<FieldRef Name='DepartmentName'/>
<FieldRef Name='DepartmentID'/>
<FieldRef Name='LocationName'/>
<FieldRef Name='LocationID'/>
<FieldRef Name='LocationType'/>
<FieldRef Name='DivisionName'/>
<FieldRef Name='DivisionID'/>
<FieldRef Name='PONumber'/>
<FieldRef Name='POCost'/>
<FieldRef Name='MaterialDetails'/>
<FieldRef Name='ApprovalStatus'/>
<FieldRef Name='FHCode'/>
<FieldRef Name='Modified'/>
<FieldRef Name='CurrentApprover'/>
<FieldRef Name='NewStatus'/>
<FieldRef Name='NewFlow'/>
</ViewFields></View>";
// camlQuery.ViewXml = "<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Counter'>769</Value></Eq></Where></Query></View>";
MSC.ListItemCollection Items = list.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
_retList.Add(new PurchaseOrder
{
Id = Convert.ToInt32(item["ID"]),
POReferenceNumber = Convert.ToString(item["POReferenceNumber"]).Trim(),
Author = Convert.ToString((item["Author"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
Created = Convert.ToString(item["Created"]).Trim(),
DepartmentName = Convert.ToString(item["DepartmentName"]).Trim(),
DepartmentID = Convert.ToString(item["DepartmentID"]).Trim(),
LocationName = Convert.ToString(item["LocationName"]).Trim(),
LocationID = Convert.ToString(item["LocationID"]).Trim(),
LocationType = Convert.ToString(item["LocationType"]).Trim(),
////Author = Convert.ToString((item["Author"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
//Author = Convert.ToString((item["Author"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
//FunctionalHead = Convert.ToString((item["FunctionalHead"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
//HRHead = Convert.ToString((item["HRHead"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
//HRHeadOnly = Convert.ToString((item["HRHeadOnly"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
//MDorJMD = item["MDorJMD"] == null ? "" : Convert.ToString((item["MDorJMD"] as Microsoft.SharePoint.Client.FieldUserValue).LookupId),
//Recruiter = Convert.ToString((item["Recruiter"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
DivisionName = Convert.ToString(item["DivisionName"]).Trim(),
DivisionID = Convert.ToString(item["DivisionID"]).Trim(),
PONumber = Convert.ToString(item["PONumber"]).Trim(),
POCost = Convert.ToString(item["POCost"]).Trim(),
MaterialDetails = Convert.ToString(item["MaterialDetails"]).Trim(),
ApprovalStatus = Convert.ToString(item["ApprovalStatus"]).Trim(),
FHCode = Convert.ToString(item["FHCode"]).Trim(),
Modified = Convert.ToString(item["Modified"]).Trim(),
CurrentApprover = Convert.ToString(item["CurrentApprover"]).Trim(),
NewStatus = Convert.ToString(item["NewStatus"]).Trim(),
NewFlow = Convert.ToString(item["NewFlow"]).Trim(),
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
CustomSharePointUtility.WriteLog("Error in GetAll_ManpowerRequisitionFromSharePoint()" + " Error:" + ex.Message);
}
return(_retList);
}
public static List <ITAssets> GetAll_AssetsDetailsFromSharePoint(string siteUrl, string listName, string DaysDifference)
{
List <ITAssets> _retList = new List <ITAssets>();
try
{
using (MSC.ClientContext context = CustomSharePointUtility.GetContext(siteUrl))
{
if (context != null)
{
MSC.List list = context.Web.Lists.GetByTitle(listName);
MSC.ListItemCollectionPosition itemPosition = null;
while (true)
{
var dataDateValue = DateTime.Now.AddDays(-Convert.ToInt32(DaysDifference));
MSC.CamlQuery camlQuery = new MSC.CamlQuery();
camlQuery.ListItemCollectionPosition = itemPosition;
camlQuery.ViewXml = @"<View>
<Query>
<Where>
<And>
<Or>
<Or>
<Or>
<Eq>
<FieldRef Name='StatusId'/>
<Value Type='Number'>0</Value>
</Eq>
<Eq>
<FieldRef Name='StatusId'/>
<Value Type='Number'>1</Value>
</Eq>
</Or>
<Or>
<Eq>
<FieldRef Name='StatusId'/>
<Value Type='Number'>4</Value>
</Eq>
<Eq>
<FieldRef Name='StatusId'/>
<Value Type='Number'>5</Value>
</Eq>
</Or>
</Or>
<Or>
<Eq>
<FieldRef Name='StatusId'/>
<Value Type='Number'>6</Value>
</Eq>
<Eq>
<FieldRef Name='StatusId'/>
<Value Type='Number'>7</Value>
</Eq>
</Or>
</Or>
<Leq><FieldRef Name='Modified'/><Value Type='DateTime'>" + dataDateValue.ToString("o") + "</Value></Leq>";
camlQuery.ViewXml += @"</And>
</Where>
</Query>
<RowLimit>5000</RowLimit>
<ViewFields>
<FieldRef Name='ID'/>
<FieldRef Name='RequisitionNo'/>
<FieldRef Name='EmployeeName'/>
<FieldRef Name='Department'/>
<FieldRef Name='Location'/>
<FieldRef Name='Designation'/>
<FieldRef Name='Status'/>
<FieldRef Name='AssignedApprover'/>
<FieldRef Name='Business'/>
<FieldRef Name='StartOn'/>
<FieldRef Name='FunctionalHead'/>
<FieldRef Name='ReportingTo'/>
<FieldRef Name='NewJoineeEmpName'/>
<FieldRef Name='WorkLevel'/>
<FieldRef Name='ConnectivityDevice'/>
<FieldRef Name='ReplacementEmployeeName'/>
<FieldRef Name='Asset'/>
<FieldRef Name='NewEmployeeDesignation'/>
<FieldRef Name='StatusId'/>
<FieldRef Name='Modified'/>
</ViewFields></View>";
MSC.ListItemCollection Items = list.GetItems(camlQuery);
context.Load(Items);
context.ExecuteQuery();
itemPosition = Items.ListItemCollectionPosition;
foreach (MSC.ListItem item in Items)
{
_retList.Add(new ITAssets
{
Id = Convert.ToInt32(item["ID"]),
RequisitionNo = Convert.ToString(item["RequisitionNo"]).Trim(),
EmployeeName = Convert.ToString(item["EmployeeName"]).Trim(),
Department = Convert.ToString(item["Department"]).Trim(),
Location = Convert.ToString(item["Location"]).Trim(),
Designation = Convert.ToString(item["Designation"]).Trim(),
Status = Convert.ToString(item["Status"]).Trim(),
StatusId = Convert.ToString(item["StatusId"]).Trim(),
AssignedApprover = item["AssignedApprover"] == null ? "" : Convert.ToString((item["AssignedApprover"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
//Business = item["AssignedApprover"] == null ? "" : Convert.ToString((item["AssignedApprover"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
Business = Convert.ToString(item["Business"]).Trim(),
StartOn = Convert.ToString(item["StartOn"]).Trim(),
FunctionalHead = item["FunctionalHead"] == null ? "" : Convert.ToString((item["FunctionalHead"] as Microsoft.SharePoint.Client.FieldUserValue[])[0].LookupId),
ReportingTo = item["ReportingTo"] == null ? "" : Convert.ToString((item["ReportingTo"] as Microsoft.SharePoint.Client.FieldUserValue).LookupValue),
NewJoineeEmpName = Convert.ToString(item["NewJoineeEmpName"]).Trim(),
ReplacementEmployeeName = Convert.ToString(item["ReplacementEmployeeName"]).Trim(),
NewEmployeeDesignation = Convert.ToString(item["NewEmployeeDesignation"]).Trim(),
Asset = Convert.ToString(item["Asset"]).Trim(),
Modified = Convert.ToString(item["Modified"]).Trim(),
});
}
if (itemPosition == null)
{
break; // TODO: might not be correct. Was : Exit While
}
}
}
}
}
catch (Exception ex)
{
CustomSharePointUtility.WriteLog("Error in GetAll_AssetsDetailsFromSharePoint()" + " Error:" + ex.Message);
}
return(_retList);
}
