public static FieldLookupValue GetLookupFieldValue(string lookupName, string lookupListName, ClientContext clientContext)
{
//Ref: Karine Bosch - https://karinebosch.wordpress.com/2015/05/11/setting-the-value-of-a-lookup-field-using-csom/
var lookupList = clientContext.Web.Lists.GetByTitle(lookupListName);
CamlQuery query = new CamlQuery();
string lookupFieldName = ConfigurationManager.AppSettings["LookupFieldName"].ToString();
string lookupFieldType = ConfigurationManager.AppSettings["LookupFieldType"].ToString();
query.ViewXml = string.Format(@"<View><Query><Where><Eq><FieldRef Name='{0}'/><Value Type='{1}'>{2}</Value></Eq>" +
"</Where></Query></View>", lookupFieldName, lookupFieldType, lookupName);
ListItemCollection listItems = lookupList.GetItems(query);
clientContext.Load(listItems, items => items.Include
(listItem => listItem["ID"],
listItem => listItem[lookupFieldName]));
clientContext.ExecuteQuery();
if (listItems != null)
{
ListItem item = listItems[0];
FieldLookupValue lookupValue = new FieldLookupValue();
lookupValue.LookupId = int.Parse(item["ID"].ToString());
return lookupValue;
}
return null;
}
internal static void AddOrder
(this ClientContext ctx, string title, string customerId, string ProductId, string price) {
var orders = ctx.Web.Lists.GetByTitle("Order");
ListItem li = orders.AddItem(new ListItemCreationInformation());
li["Title"] = title;
li["Customer"] = new FieldLookupValue() { LookupId = customerId.ToInt32()};
li["Product_2"] = new TaxonomyFieldValue() { TermGuid = ProductId, Label="", WssId=-1};
li["Price"] = price;
li.Update();
ctx.ExecuteQuery();
}
public static string LookupCollectionValueToString(FieldLookupValue[] value)
{
if (value.Length == 0) return string.Empty;
return value.Select(lv => LookupValueToString(lv))
.Aggregate((all, current) => all + LookupCollectionItemSeparator + current);
}
public ActionResult Add(string title = null, string description = null, string user = null, string lookup = null, bool add = false)
{
var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
ViewBag.SPHostUrl = spContext.SPHostUrl;
if (add)
{
using (var clientContext = spContext.CreateUserClientContextForSPAppWeb())
{
var list = clientContext.Web.Lists.GetByTitle("SampleList");
clientContext.ExecuteQuery();
List<FieldUserValue> users = new List<FieldUserValue>();
foreach(var u in user.Split(';')){
var spUser = clientContext.Web.EnsureUser(string.Format("i:0#.f|membership|{0}", u));
clientContext.Load(spUser);
clientContext.ExecuteQuery();
if (spUser != null)
users.Add(new FieldUserValue
{
LookupId = spUser.Id
});
}
ListItemCreationInformation listItem = new ListItemCreationInformation();
ListItem item = list.AddItem(listItem);
item["Title"] = title;
item["Description1"] = description;
item["Multiuser"] = users;
item["Lookup"] = new FieldLookupValue
{
LookupId = int.Parse(lookup)
};
item.Update();
clientContext.ExecuteQuery();
}
return RedirectToAction("Index", "ListItem", new { SPHostUrl = spContext.SPHostUrl });
}
return View();
}
public static int GetLookupFieldValue(this FieldLookupValue lookupField)
{
return(lookupField != null ? lookupField.LookupId : -1);
}
public static void AddListItems(ClientContext clientContext, string listTitle, XmlDocument sampleData)
{
XmlNode items = sampleData.SelectSingleNode("//List[@name='" + listTitle + "']");
List list = clientContext.Web.Lists.GetByTitle(listTitle);
//remove list items
var d_items = list.GetItems(new CamlQuery());
clientContext.Load(d_items);
clientContext.ExecuteQuery();
var count = d_items.Count;
if (count > 0)
{
while (count-- > 0)
{
d_items[0].DeleteObject();
}
clientContext.ExecuteQuery();
}
foreach (XmlNode item in items)
{
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem newItem = list.AddItem(itemCreateInfo);
foreach (XmlNode column in item.ChildNodes)
{
if (column.Attributes["name"].Value == "Id") continue;
if (column.Attributes["Type"] != null && column.Attributes["Type"].Value == "Lookup" && !string.IsNullOrEmpty(column.InnerText))
{
FieldLookupValue fieldLookupValue = new FieldLookupValue();
fieldLookupValue.LookupId = int.Parse(sampleData.SelectSingleNode(
string.Format("//List[@name='{0}']/item[{1}]/column[@name='Id']",
column.Attributes["Source"].Value,
column.InnerText)).InnerText);
newItem[column.Attributes["name"].Value] = fieldLookupValue;
}
else
{
newItem[column.Attributes["name"].Value] = column.InnerText;
}
}
newItem.Update();
clientContext.ExecuteQuery();
item.SelectSingleNode("./column[@name='Id']").InnerText = newItem.Id.ToString();
}
}
private System.Data.DataTable GetDataTableFromListItemCollection()
{
string strWhere = string.Empty;
string filePath = string.Empty;
/* fields
* Priority
* Initiative
* Title -- changed to Projects
* Description
* Status
* Due Date Due_X0020_Date
* Revised Due Date
* Executive Sponsor
* Business Owner
* IT Owner
* Phase
* IT Comments
* PRB Comments
*/
System.Data.DataTable dtGetReqForm = new System.Data.DataTable();
// add columns to data table
dtGetReqForm.Columns.Add("Priority", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Initiative", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Project", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Description", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Status", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Due Date", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Revised Due Date", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Executive Sponsor", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Business Owner", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("IT Owner", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Phase", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("IT Comments", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("PRB Comments", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("PRB Priority", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Financial Benefit", System.Type.GetType("System.String"));
dtGetReqForm.Columns.Add("Difficulty", System.Type.GetType("System.String"));
//get data from SharePoint list
using (var clientContext = new ClientContext(siteUrl))
{
try
{
SecureString passWord = new SecureString();
foreach (char c in password.ToCharArray())
{
passWord.AppendChar(c);
}
clientContext.Credentials = new SharePointOnlineCredentials(username, passWord);
//Console.WriteLine("Connecting \"" + siteUrl + "\"");
Web Oweb = clientContext.Web;
clientContext.Load(Oweb);
clientContext.ExecuteQuery();
List spList = clientContext.Web.Lists.GetByTitle(spListName);
clientContext.Load(spList);
clientContext.Load(spList.Views);
clientContext.ExecuteQuery();
//Console.WriteLine("Getting List: " + spListName);
if (spList != null && spList.ItemCount > 0)
{
View view = spList.Views.GetByTitle(viewName);
clientContext.Load(view);
clientContext.ExecuteQuery();
ViewFieldCollection viewFields = view.ViewFields;
clientContext.Load(viewFields);
clientContext.ExecuteQuery();
CamlQuery query = new CamlQuery();
query.ViewXml = "<View><Query>" + view.ViewQuery + "</Query></View>";
ListItemCollection listItems = spList.GetItems(query);
clientContext.Load(listItems,
items => items.Include(
item => item["Title"],
item => item["Priority"],
item => item["Description"],
item => item["Initiative"],
item => item["Status"],
item => item["Due_x0020_Date"],
item => item["Revised_x0020_Due_x0020_Date"],
item => item["Executive_x0020_Sponsor"],
item => item["Business_x0020_Owner"],
item => item["IT_x0020_Owner"],
item => item["Phase"],
item => item["IT_x0020_Comments"],
item => item["PRB_x0020_Comments"],
item => item["Difficulty"],
item => item["Financial_x0020_Benefit"],
item => item["PRB_x0020_Priority"]));
try
{
clientContext.ExecuteQuery();
}
catch (Exception e)
{
Console.WriteLine("error loading list columns: " + e.InnerException + ". " + e.StackTrace);
}
if (listItems != null && listItems.Count > 0)
{
foreach (var item in listItems)
{
// load look up columns and people explicitly at item level
clientContext.Load(item,
it => it["Initiative"],
it => it["Status"],
it => it["Phase"],
it => it["IT_x0020_Owner"]);
clientContext.ExecuteQuery();
FieldLookupValue initiative = item["Initiative"] as FieldLookupValue;
FieldLookupValue status = item["Status"] as FieldLookupValue;
FieldLookupValue phase = item["Phase"] as FieldLookupValue;
FieldUserValue itOwner = (FieldUserValue)item["IT_x0020_Owner"];
DataRow dr = dtGetReqForm.NewRow();
dr["Project"] = item["Title"];
dr["Priority"] = item["Priority"];
dr["Description"] = item["Description"];
if (initiative != null)
{
dr["Initiative"] = initiative.LookupValue;
}
else
{
dr["Initiative"] = "";
}
if (status != null)
{
dr["Status"] = status.LookupValue;
}
else
{
dr["Status"] = "";
}
if (item["Due_x0020_Date"] != null)
{
DateTime dt = Convert.ToDateTime(item["Due_x0020_Date"].ToString());
dr["Due Date"] = dt.ToString("MM/dd/yyyy");
}
else
{
dr["Due Date"] = "";
}
if (item["Revised_x0020_Due_x0020_Date"] != null)
{
DateTime dtr = Convert.ToDateTime(item["Revised_x0020_Due_x0020_Date"].ToString());
dr["Revised Due Date"] = dtr.ToString("MM/dd/yyyy");
}
else
{
dr["Revised Due Date"] = "";
}
dr["Executive Sponsor"] = item["Executive_x0020_Sponsor"];
dr["Business Owner"] = item["Business_x0020_Owner"];
if (itOwner != null)
{
dr["IT Owner"] = itOwner.LookupValue;
}
else
{
dr["IT Owner"] = "";
}
if (phase != null)
{
dr["Phase"] = phase.LookupValue;
}
else
{
dr["Phase"] = "";
}
if (item["Financial_x0020_Benefit"] != null)
{
dr["Financial Benefit"] = item["Financial_x0020_Benefit"].ToString();
}
else
{
dr["Financial Benefit"] = "";
}
if (item["Difficulty"] != null)
{
dr["Difficulty"] = item["Difficulty"].ToString();
}
else
{
dr["Difficulty"] = "";
}
dr["IT Comments"] = item["IT_x0020_Comments"];
dr["PRB Comments"] = item["PRB_x0020_Comments"];
dr["PRB Priority"] = item["PRB_x0020_Priority"];
dtGetReqForm.Rows.Add(dr);
}
/*
* // add blank row before footer
* DataRow spacer = dtGetReqForm.NewRow();
* spacer["Description"] = " ";
* spacer["IT Comments"] = "";
* dtGetReqForm.Rows.Add(spacer);
*
* //add footers here
* DataRow footer = dtGetReqForm.NewRow();
* footer["Description"] = "Status Legend";
* footer["IT Comments"] = "Phase Legend";
* dtGetReqForm.Rows.Add(footer);
*
* DataRow footer2 = dtGetReqForm.NewRow();
* footer2["Description"] = "Green – On Target for time, budget, and scope";
* footer2["IT Comments"] = "Concept";
* dtGetReqForm.Rows.Add(footer2);
*
* DataRow footer3 = dtGetReqForm.NewRow();
* footer3["Description"] = "Yellow – At risk for time, budget, or scope (specify)";
* footer3["IT Comments"] = "Planning";
* dtGetReqForm.Rows.Add(footer3);
*
* DataRow footer4 = dtGetReqForm.NewRow();
* footer4["Description"] = "Red – Needs to be refactored – no way to recover without changing the time, budget, or scope";
* footer4["IT Comments"] = "Development / Implementation";
* dtGetReqForm.Rows.Add(footer4);
*
* DataRow footer5 = dtGetReqForm.NewRow();
* footer5["Description"] = "Status Legend";
* footer5["IT Comments"] = "UAT";
* dtGetReqForm.Rows.Add(footer5);
*
* DataRow footer6 = dtGetReqForm.NewRow();
* footer6["IT Comments"] = "Deployed";
* dtGetReqForm.Rows.Add(footer6);
*
* DataRow footer7 = dtGetReqForm.NewRow();
* footer7["IT Comments"] = "Complete (burned in)";
* dtGetReqForm.Rows.Add(footer7);
*
* DataRow footer8 = dtGetReqForm.NewRow();
* footer8["IT Comments"] = "Hold";
* dtGetReqForm.Rows.Add(footer8);
*/
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (clientContext != null)
{
clientContext.Dispose();
}
}
}
return(dtGetReqForm);
}
public static void AddNewListItem(ITLRecord record, List spList, ClientContext clientContext, string LookupList, string lookupFieldName, string lookupFieldType)
{
try
{
Dictionary <string, object> itemFieldValues = new Dictionary <string, object>();
PropertyInfo[] properties = typeof(ITLRecord).GetProperties();
foreach (PropertyInfo property in properties)
{
object propValue = property.GetValue(record, null);
if (!String.IsNullOrEmpty(propValue.ToString()))
{
Field matchingField = spList.Fields.GetByInternalNameOrTitle(property.Name);
clientContext.Load(matchingField);
clientContext.ExecuteQuery();
switch (matchingField.FieldTypeKind)
{
case FieldType.User:
FieldUserValue userFieldValue = GetUserFieldValue(propValue.ToString(), clientContext);
if (userFieldValue != null)
{
itemFieldValues.Add(matchingField.InternalName, userFieldValue);
}
else
{
throw new Exception("User field value could not be added: " + propValue.ToString());
}
break;
case FieldType.Lookup:
FieldLookupValue lookupFieldValue = GetLookupFieldValue(propValue.ToString(), LookupList, lookupFieldName, lookupFieldType, clientContext);
if (lookupFieldValue != null)
{
itemFieldValues.Add(matchingField.InternalName, lookupFieldValue);
}
else
{
throw new Exception("Lookup field value could not be added: " + propValue.ToString());
}
break;
case FieldType.Invalid:
switch (matchingField.TypeAsString)
{
default:
//Code for publishing site columns
continue;
}
default:
itemFieldValues.Add(matchingField.InternalName, propValue);
break;
}
}
}
//Add new item to list
ListItemCreationInformation creationInfo = new ListItemCreationInformation();
sp.ListItem oListItem = spList.AddItem(creationInfo);
foreach (KeyValuePair <string, object> itemFieldValue in itemFieldValues)
{
//Set each field value
oListItem[itemFieldValue.Key] = itemFieldValue.Value;
}
oListItem.Update();
clientContext.ExecuteQuery();
}
catch (Exception ex)
{
throw ex;
}
}
public void CrearEstructura(string nombreCarpeta, string cliente, string factura, string facturatitulo)
{
ClientContext clientContext = this.context;
Web web = clientContext.Web;
clientContext.Load(web);
clientContext.ExecuteQuery();
RoleDefinition roleDefinitionViewOnly = null;
//Obtenemos los distintos grupos;
Group ColaboradoresExpo = web.SiteGroups.GetByName(Grupos.ColaboradoresExportaciones);
Group LectoresEmb = web.SiteGroups.GetByName(Grupos.LectoresEmbarque);
Group LectoresPed = web.SiteGroups.GetByName(Grupos.LectoresPedidos);
Group PropExp = web.SiteGroups.GetByName(Grupos.PropietariosExportaciones);
List raiz = web.Lists.GetByTitle(Listas.CarpetaRaiz);
clientContext.Load(raiz, rt => rt.RootFolder.ServerRelativeUrl);
clientContext.ExecuteQuery();
if (raiz != null)
{
/***********CREA CARPETA DE PEDIDO***********/
ListItemCreationInformation list_info = new ListItemCreationInformation();
list_info.FolderUrl = raiz.RootFolder.ServerRelativeUrl;
list_info.UnderlyingObjectType = FileSystemObjectType.Folder;
list_info.LeafName = nombreCarpeta;
ListItem pedido = raiz.AddItem(list_info);
//Setea el CT a la carpeta pedido
ContentTypeCollection ctc = web.ContentTypes;
clientContext.Load(ctc);
clientContext.ExecuteQuery();
ContentType contentType = Enumerable.FirstOrDefault(ctc, ct => ct.Name == "CT.Raiz");
pedido["ContentTypeId"] = contentType.Id;
pedido[Campos.Nombre] = nombreCarpeta;
pedido[Campos.Pedido] = Int64.Parse(nombreCarpeta);
pedido[Campos.Estado] = "";
pedido.Update();
//Campo Cliente
if (!string.IsNullOrEmpty(cliente))
{
ListItem itemCliente = this.ObtenerClienteParaLookup(cliente);
clientContext.Load(itemCliente);
clientContext.ExecuteQuery();
if (itemCliente != null)
{
FieldLookupValue lv = new FieldLookupValue();
lv.LookupId = itemCliente.Id;
pedido[Campos.Cliente] = lv;
}
}
//Campo factura
if (!string.IsNullOrEmpty(factura))
{
FieldUrlValue campoFactura = new FieldUrlValue();
campoFactura.Description = string.IsNullOrEmpty(facturatitulo) ? "FACTURA" : facturatitulo;
campoFactura.Url = factura;
pedido[Campos.Factura] = campoFactura;
}
pedido.Update();
clientContext.ExecuteQuery();
//Asigna los permisos a la carpeta
AsignarPermisos(web, pedido, ColaboradoresExpo, RoleType.Contributor);
AsignarPermisos(web, pedido, PropExp, RoleType.Contributor);
AsignarPermisos(web, pedido, LectoresPed, RoleType.Reader);
AsignarPermisos(web, pedido, LectoresEmb, RoleType.Reader);
//pedido.UpdateOverwriteVersion();
/*********************************************SUBCARPETAS***************************************************************/
List <string> carpetas = ObtenerNombresSubcarpetas(web.Url);
try
{
roleDefinitionViewOnly = web.RoleDefinitions.Cast <RoleDefinition>().FirstOrDefault(r => r.Name.ToUpper() == "VISTA SÓLO");
}
catch
{
}
foreach (string carpeta in carpetas)
{
//Crea la subcarpeta
ListItemCreationInformation subfolder_info = new ListItemCreationInformation();
subfolder_info.FolderUrl = raiz.RootFolder.ServerRelativeUrl + "/" + nombreCarpeta;
subfolder_info.UnderlyingObjectType = FileSystemObjectType.Folder;
subfolder_info.LeafName = carpeta;
ListItem sfolder = raiz.AddItem(subfolder_info);
//Setea el CT a la subcarpeta
ContentTypeCollection ct_sub = web.ContentTypes;
clientContext.Load(ct_sub);
clientContext.ExecuteQuery();
ContentType contentType_sub = Enumerable.FirstOrDefault(ct_sub, ct => ct.Name == "CT.Sub");
sfolder["ContentTypeId"] = contentType_sub.Id;
sfolder.Update(); //Para obtener la url
//Setea campos
sfolder[Campos.Nombre] = carpeta;
sfolder[Campos.Pedido] = Int64.Parse(nombreCarpeta);
sfolder.Update();
/*
* if (!string.IsNullOrEmpty(factura))
* {
* FieldUrlValue campoFac = new FieldUrlValue();
* campoFac.Description = string.IsNullOrEmpty(facturatitulo) ? "FACTURA" : facturatitulo;
* campoFac.Url = factura;
* sfolder[Campos.Factura] = campoFac;
* }
*
* if (!string.IsNullOrEmpty(cliente))
* {
* ListItem itemclie = this.ObtenerClienteParaLookup(cliente);
* clientContext.Load(itemclie);
* clientContext.ExecuteQuery();
*
* FieldLookupValue lookup = new FieldLookupValue();
* lookup.LookupId = itemclie.Id;
* sfolder[Campos.Cliente] = lookup;
* sfolder.Update();
* clientContext.ExecuteQuery();
*
*
* }*/
clientContext.ExecuteQuery();
switch (carpeta)
{
case SubCarpetas.PermisoEmbarque:
this.AsignarPermisos(web, sfolder, ColaboradoresExpo, RoleType.Contributor);
this.AsignarPermisos(web, sfolder, PropExp, RoleType.Contributor);
if (roleDefinitionViewOnly != null)
{
this.AsignarPermisosPorRoleDefinition(web, sfolder, LectoresEmb, roleDefinitionViewOnly);
}
else
{
//this.AsignarPermisos(web, sfolder, LectoresEmb, RoleType.None);
this.AsignarPermisos(web, sfolder, LectoresPed, RoleType.Reader);
}
break;
case SubCarpetas.Extras:
this.AsignarPermisos(web, sfolder, ColaboradoresExpo, RoleType.Contributor);
this.AsignarPermisos(web, sfolder, PropExp, RoleType.Contributor);
break;
default:
this.AsignarPermisos(web, sfolder, ColaboradoresExpo, RoleType.Contributor);
this.AsignarPermisos(web, sfolder, PropExp, RoleType.Contributor);
this.AsignarPermisos(web, sfolder, LectoresPed, RoleType.Reader);
break;
}
}
}
}
public void SetLookupId(string key, int lookupId)
{
Item[key] = new FieldLookupValue {
LookupId = lookupId
};
}
public static string LookupValueToString(FieldLookupValue value)
{
return value.LookupId + LookupValueSeparator + value.LookupValue;
}
static void Main(string[] args)
{
try
{
//Get site URL and credentials values from config
Uri siteUri = new Uri(ConfigurationManager.AppSettings["SourceSite"].ToString());
//Connect to SharePoint Online
using (ClientContext clientContext = new ClientContext(siteUri.ToString()))
{
SecureString passWord = new SecureString();
foreach (char c in ConfigurationManager.AppSettings["DestinationPassword"].ToCharArray())
{
passWord.AppendChar(c);
}
clientContext.Credentials = new SharePointOnlineCredentials("*****@*****.**", passWord);
if (clientContext != null)
{
//Source list
List sourceList = clientContext.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["SourceList"]);
//Destination library
List destinationLibrary = clientContext.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["DestinationLibrary"]);
// try to get all the list items
// could get in sections if it exceeds List View Threshold
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><Query><OrderBy><FieldRef Name='Title' /></OrderBy></Query></View>";
ListItemCollection listItems = sourceList.GetItems(camlQuery);
FieldCollection listFields = sourceList.Fields;
clientContext.Load(sourceList);
clientContext.Load(listFields);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
// Download attachments for each list item and then upload to new list item
foreach (ListItem item in listItems)
{
string attachmentURL = siteUri + "/Lists/" + ConfigurationManager.AppSettings["SourceList"].ToString() + "/Attachments/" + item["ID"];
Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(attachmentURL);
clientContext.Load(folder);
try
{
clientContext.ExecuteQuery();
}
catch (ServerException ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine("No Attachment for ID " + item["ID"].ToString());
}
FileCollection attachments = folder.Files;
clientContext.Load(attachments);
clientContext.ExecuteQuery();
// write each file to local disk
foreach (SP.File file in folder.Files)
{
if (clientContext.HasPendingRequest)
{
clientContext.ExecuteQuery();
}
var fileRef = file.ServerRelativeUrl;
var fileInfo = SP.File.OpenBinaryDirect(clientContext, fileRef);
using (var memory = new MemoryStream())
{
byte[] buffer = new byte[1024 * 64];
int nread = 0;
while ((nread = fileInfo.Stream.Read(buffer, 0, buffer.Length)) > 0)
{
memory.Write(buffer, 0, nread);
}
memory.Seek(0, SeekOrigin.Begin);
// at this point you have the contents of your file in memory
// save to computer
Microsoft.SharePoint.Client.File.SaveBinaryDirect(clientContext, string.Format("/{0}/{1}", ConfigurationManager.AppSettings["AttachmentLibrary"], System.IO.Path.GetFileName(file.Name)), memory, true);
}
// this call avoids potential problems if any requests are still pending
if (clientContext.HasPendingRequest)
{
clientContext.ExecuteQuery();
}
SP.File newFile = clientContext.Web.GetFileByServerRelativeUrl(string.Format("/{0}/{1}", ConfigurationManager.AppSettings["AttachmentLibrary"], System.IO.Path.GetFileName(file.Name)));
clientContext.Load(newFile);
clientContext.ExecuteQuery();
//check out to make sure not to create multiple versions
newFile.CheckOut();
FieldLookupValue applicationName = item["Source"] as FieldLookupValue;
// app name may be null
if (applicationName == null)
{
applicationName = new FieldLookupValue();
}
applicationName.LookupId = Convert.ToInt32(item["ID"]);
ListItem newItem = newFile.ListItemAllFields;
newItem["From_x0020_Source"] = applicationName;
newItem.Update();
// use OverwriteCheckIn type to make sure not to create multiple versions
newFile.CheckIn(string.Empty, CheckinType.OverwriteCheckIn);
// Clear requests if any if pending
if (clientContext.HasPendingRequest)
{
clientContext.ExecuteQuery();
}
}
Console.WriteLine("All list items and attachments copied over. Press any key to close");
Console.ReadKey();
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Failed: " + ex.Message);
Console.WriteLine("Stack Trace: " + ex.StackTrace);
Console.ReadKey();
}
}
public static List <JObject> GetDocuments(ClientContext cc, FileCollection files, string foldername)
{
List <JObject> SharePointDocs = new List <JObject>();
for (int f = 0; f < files.Count; f++)
{
Microsoft.SharePoint.Client.File file = files[f];
ListItem item = file.ListItemAllFields;
var json = new JObject();
json.Add(new JProperty("filename", file.Name));
if (foldername != null)
{
json.Add(new JProperty("folder", foldername));
}
json.Add(new JProperty("uri", file.LinkingUri));
foreach (KeyValuePair <string, Object> field in item.FieldValues)
{
if (field.Value != null)
{
Regex rg = new Regex(@"Microsoft\.SharePoint\.Client\..*");
var match = rg.Match(field.Value.ToString());
//Check Taxfields
if (match.Success && field.Value.ToString().Equals("Microsoft.SharePoint.Client.FieldUserValue"))
{
FieldUserValue fieldUserValue = field.Value as FieldUserValue;
var jsonUser = new JObject();
jsonUser.Add(new JProperty("Email", fieldUserValue.Email));
jsonUser.Add(new JProperty("LookupId", fieldUserValue.LookupId));
jsonUser.Add(new JProperty("LookupValue", fieldUserValue.LookupValue));
json.Add(new JProperty(field.Key, jsonUser));
}
else if (match.Success && field.Value.ToString().Equals("Microsoft.SharePoint.Client.FieldLookupValue"))
{
FieldLookupValue fieldLookupValue = field.Value as FieldLookupValue;
var jsonfieldLookup = new JObject();
jsonfieldLookup.Add(new JProperty("LookupID", fieldLookupValue.LookupId));
jsonfieldLookup.Add(new JProperty("LookupValue", fieldLookupValue.LookupValue));
json.Add(new JProperty(field.Key, jsonfieldLookup));
}
else if (match.Success && field.Value.ToString().Equals("Microsoft.SharePoint.Client.Taxonomy.TaxonomyFieldValue"))
{
TaxonomyFieldValue taxonomyFieldValue = field.Value as TaxonomyFieldValue;
var jsonTaxField = new JObject();
jsonTaxField.Add(new JProperty("WssId", taxonomyFieldValue.WssId));
jsonTaxField.Add(new JProperty("TermGuid", taxonomyFieldValue.TermGuid));
jsonTaxField.Add(new JProperty("Label", taxonomyFieldValue.Label));
json.Add(new JProperty(field.Key, jsonTaxField));
}
else
{
json.Add(new JProperty(field.Key, field.Value.ToString()));
}
}
}
SharePointDocs.Add(json);
}
return(SharePointDocs);
}
// Copy Method - w\Attahcments
private void CopyingProcessV2()
{
try
{
if (IsSPOnline == true)
{
SPOnlineContext = ClaimClientContext.GetAuthenticatedContext(cboSiteURL.SelectedItem.ToString());
SP.List sourceList = SPOnlineContext.Web.Lists.GetByTitle(cboSourceSiteList.SelectedItem.ToString());
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"";
ListItemCollection sourceCollListItem = sourceList.GetItems(camlQuery);
SPOnlineContext.Load(sourceCollListItem);
SPOnlineContext.ExecuteQuery();
int count = 0;
SP.List destinationList = SPOnlineContext.Web.Lists.GetByTitle(cboDestSiteList.SelectedItem.ToString());
foreach (ListItem sourceListItem in sourceCollListItem)
{
string srcList = cboSourceSiteList.SelectedItem.ToString();
string destList = cboDestSiteList.SelectedItem.ToString();
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem destinationListItem = destinationList.AddItem(itemCreateInfo);
switch (cboDestSiteList.SelectedItem.ToString())
{
case "AllGrantSubmits-2017x":
destinationListItem["Title"] = sourceListItem["Title"];
destinationListItem["Contact_x0020_Name"] = sourceListItem["Contact_x0020_Name"];
destinationListItem["Email"] = sourceListItem["Email"];
destinationListItem["Phone"] = sourceListItem["Phone"];
FieldLookupValue district = ((FieldLookupValue)sourceListItem["District"]);
destinationListItem["District"] = district.LookupId;
//destinationListItem["Comments"] = sourceListItem["Comments"];
destinationListItem.Update();
SPOnlineContext.Load(destinationListItem);
SPOnlineContext.ExecuteQuery();
//ctx.ExecuteQuery();
UpdateAttachments(SPOnlineContext, SPOnlineContext, sourceListItem.Id, destinationListItem.Id, srcList, destList);
count++;
break;
} // END Switch
} // END FOR
MessageBox.Show("Transfer Completed! " + count + " item(s) copied");
}
}
catch (Exception ex)
{
ex.Message.ToString();
MessageBox.Show("Error Encountered " + ex.Message);
}
}
public override TokenParser ProvisionObjects(Web web, ProvisioningTemplate template, TokenParser parser, ProvisioningTemplateApplyingInformation applyingInformation)
{
using (var scope = new PnPMonitoredScope(this.Name))
{
if (template.Lists.Any())
{
var rootWeb = (web.Context as ClientContext).Site.RootWeb;
web.EnsureProperties(w => w.ServerRelativeUrl);
web.Context.Load(web.Lists, lc => lc.IncludeWithDefaultProperties(l => l.RootFolder.ServerRelativeUrl));
web.Context.ExecuteQueryRetry();
var existingLists = web.Lists.AsEnumerable <List>().Select(existingList => existingList.RootFolder.ServerRelativeUrl).ToList();
var serverRelativeUrl = web.ServerRelativeUrl;
#region DataRows
foreach (var listInstance in template.Lists)
{
if (listInstance.DataRows != null && listInstance.DataRows.Any())
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Processing_data_rows_for__0_, listInstance.Title);
// Retrieve the target list
var list = web.Lists.GetByTitle(parser.ParseString(listInstance.Title));
web.Context.Load(list);
// Retrieve the fields' types from the list
Microsoft.SharePoint.Client.FieldCollection fields = list.Fields;
web.Context.Load(fields, fs => fs.Include(f => f.InternalName, f => f.FieldTypeKind, f => f.TypeAsString, f => f.ReadOnlyField, f => f.Title));
web.Context.ExecuteQueryRetry();
var keyColumnType = "Text";
var parsedKeyColumn = parser.ParseString(listInstance.DataRows.KeyColumn);
if (!string.IsNullOrEmpty(parsedKeyColumn))
{
var keyColumn = fields.FirstOrDefault(f => f.InternalName.Equals(parsedKeyColumn, StringComparison.InvariantCultureIgnoreCase));
if (keyColumn != null)
{
switch (keyColumn.FieldTypeKind)
{
case FieldType.User:
case FieldType.Lookup:
keyColumnType = "Lookup";
break;
case FieldType.URL:
keyColumnType = "Url";
break;
case FieldType.DateTime:
keyColumnType = "DateTime";
break;
case FieldType.Number:
case FieldType.Counter:
keyColumnType = "Number";
break;
}
}
}
foreach (var dataRow in listInstance.DataRows)
{
try
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_list_item__0_, listInstance.DataRows.IndexOf(dataRow) + 1);
bool processItem = true;
ListItem listitem = null;
var updateValues = new List <FieldUpdateValue>();
if (!string.IsNullOrEmpty(listInstance.DataRows.KeyColumn))
{
// Get value from key column
var dataRowValues = dataRow.Values.Where(v => v.Key == listInstance.DataRows.KeyColumn);
// if it is empty, skip the check
if (dataRowValues.Any())
{
var query = $@"<View><Query><Where><Eq><FieldRef Name=""{parsedKeyColumn}""/><Value Type=""{keyColumnType}"">{parser.ParseString(dataRowValues.FirstOrDefault().Value)}</Value></Eq></Where></Query><RowLimit>1</RowLimit></View>";
var camlQuery = new CamlQuery()
{
ViewXml = query
};
var existingItems = list.GetItems(camlQuery);
list.Context.Load(existingItems);
list.Context.ExecuteQueryRetry();
if (existingItems.Count > 0)
{
if (listInstance.DataRows.UpdateBehavior == UpdateBehavior.Skip)
{
processItem = false;
}
else
{
listitem = existingItems[0];
processItem = true;
}
}
}
}
if (processItem)
{
if (listitem == null)
{
var listitemCI = new ListItemCreationInformation();
listitem = list.AddItem(listitemCI);
}
foreach (var dataValue in dataRow.Values)
{
Field dataField = fields.FirstOrDefault(
f => f.InternalName == parser.ParseString(dataValue.Key));
if (dataField != null && dataField.ReadOnlyField)
{
// skip read only fields
continue;
}
if (dataField != null)
{
if (dataValue.Value == null)
{
if (dataField.FieldTypeKind == FieldType.Invalid)
{
updateValues.Add(new FieldUpdateValue(dataValue.Key, null, dataField.TypeAsString));
}
else
{
updateValues.Add(new FieldUpdateValue(dataValue.Key, null));
}
}
else
{
String fieldValue = parser.ParseString(dataValue.Value);
switch (dataField.FieldTypeKind)
{
case FieldType.Geolocation:
// FieldGeolocationValue - Expected format: Altitude,Latitude,Longitude,Measure
var geolocationArray = fieldValue.Split(',');
if (geolocationArray.Length == 4)
{
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse(geolocationArray[0]),
Latitude = Double.Parse(geolocationArray[1]),
Longitude = Double.Parse(geolocationArray[2]),
Measure = Double.Parse(geolocationArray[3]),
};
updateValues.Add(new FieldUpdateValue(dataValue.Key, geolocationValue));
}
else
{
updateValues.Add(new FieldUpdateValue(dataValue.Key, fieldValue));
}
break;
case FieldType.Lookup:
// FieldLookupValue - Expected format: LookupID or LookupID,LookupID,LookupID...
if (fieldValue.Contains(","))
{
var lookupValues = new List <FieldLookupValue>();
fieldValue.Split(',').All(value =>
{
lookupValues.Add(new FieldLookupValue
{
LookupId = int.Parse(value),
});
return(true);
});
updateValues.Add(new FieldUpdateValue(dataValue.Key, lookupValues.ToArray()));
}
else
{
var lookupValue = new FieldLookupValue
{
LookupId = int.Parse(fieldValue),
};
updateValues.Add(new FieldUpdateValue(dataValue.Key, lookupValue));
}
break;
case FieldType.URL:
// FieldUrlValue - Expected format: URL,Description
var urlArray = fieldValue.Split(',');
var linkValue = new FieldUrlValue();
if (urlArray.Length == 2)
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[1];
}
else
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[0];
}
updateValues.Add(new FieldUpdateValue(dataValue.Key, linkValue));
break;
case FieldType.User:
// FieldUserValue - Expected format: loginName or loginName,loginName,loginName...
if (fieldValue.Contains(","))
{
var userValues = new List <FieldUserValue>();
fieldValue.Split(',').All(value =>
{
var user = web.EnsureUser(value);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
userValues.Add(new FieldUserValue
{
LookupId = user.Id,
});;
}
return(true);
});
updateValues.Add(new FieldUpdateValue(dataValue.Key, userValues.ToArray()));
}
else
{
var user = web.EnsureUser(fieldValue);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
var userValue = new FieldUserValue
{
LookupId = user.Id,
};
updateValues.Add(new FieldUpdateValue(dataValue.Key, userValue));
}
else
{
updateValues.Add(new FieldUpdateValue(dataValue.Key, fieldValue));
}
}
break;
case FieldType.DateTime:
var dateTime = DateTime.MinValue;
if (DateTime.TryParse(fieldValue, out dateTime))
{
updateValues.Add(new FieldUpdateValue(dataValue.Key, dateTime));
}
break;
case FieldType.Invalid:
switch (dataField.TypeAsString)
{
case "TaxonomyFieldType":
// Single value field - Expected format: term label|term GUID
case "TaxonomyFieldTypeMulti":
// Multi value field - Expected format: term label|term GUID;term label|term GUID;term label|term GUID;...
{
if (fieldValue != null)
{
var termStrings = new List <string>();
var termsArray = fieldValue.Split(new char[] { ';' });
foreach (var term in termsArray)
{
termStrings.Add($"-1;#{term}");
}
updateValues.Add(new FieldUpdateValue(dataValue.Key, termStrings, dataField.TypeAsString));
}
break;
}
}
break;
default:
updateValues.Add(new FieldUpdateValue(dataValue.Key, fieldValue));
break;
}
}
}
}
foreach (var itemValue in updateValues.Where(u => u.FieldTypeString != "TaxonomyFieldTypeMulti" && u.FieldTypeString != "TaxonomyFieldType"))
{
if (string.IsNullOrEmpty(itemValue.FieldTypeString))
{
listitem[itemValue.Key] = itemValue.Value;
}
}
listitem.Update();
web.Context.Load(listitem);
web.Context.ExecuteQueryRetry();
var itemId = listitem.Id;
foreach (var itemValue in updateValues.Where(u => u.FieldTypeString == "TaxonomyFieldTypeMulti" || u.FieldTypeString == "TaxonomyFieldType"))
{
switch (itemValue.FieldTypeString)
{
case "TaxonomyFieldTypeMulti":
{
var field = fields.FirstOrDefault(f => f.InternalName == itemValue.Key as string || f.Title == itemValue.Key as string);
var taxField = web.Context.CastTo <TaxonomyField>(field);
if (itemValue.Value != null)
{
var valueCollection = new TaxonomyFieldValueCollection(web.Context, string.Join(";#", itemValue.Value as List <string>), taxField);
taxField.SetFieldValueByValueCollection(listitem, valueCollection);
}
else
{
var valueCollection = new TaxonomyFieldValueCollection(web.Context, null, taxField);
taxField.SetFieldValueByValueCollection(listitem, valueCollection);
}
listitem.Update();
web.Context.Load(listitem);
web.Context.ExecuteQueryRetry();
break;
}
case "TaxonomyFieldType":
{
var field = fields.FirstOrDefault(f => f.InternalName == itemValue.Key as string || f.Title == itemValue.Key as string);
var taxField = web.Context.CastTo <TaxonomyField>(field);
taxField.EnsureProperty(f => f.TextField);
var taxValue = new TaxonomyFieldValue();
if (itemValue.Value != null)
{
var termString = (itemValue.Value as List <string>).First();
taxValue.Label = termString.Split(new string[] { ";#" }, StringSplitOptions.None)[1].Split(new char[] { '|' })[0];
taxValue.TermGuid = termString.Split(new string[] { ";#" }, StringSplitOptions.None)[1].Split(new char[] { '|' })[1];
taxValue.WssId = -1;
taxField.SetFieldValueByValue(listitem, taxValue);
}
else
{
taxValue.Label = string.Empty;
taxValue.TermGuid = "11111111-1111-1111-1111-111111111111";
taxValue.WssId = -1;
Field hiddenField = list.Fields.GetById(taxField.TextField);
listitem.Context.Load(hiddenField, tf => tf.InternalName);
listitem.Context.ExecuteQueryRetry();
taxField.SetFieldValueByValue(listitem, taxValue); // this order of updates is important.
listitem[hiddenField.InternalName] = string.Empty; // this order of updates is important.
}
listitem.Update();
web.Context.Load(listitem);
web.Context.ExecuteQueryRetry();
break;
}
}
}
if (dataRow.Security != null && (dataRow.Security.ClearSubscopes == true || dataRow.Security.CopyRoleAssignments == true || dataRow.Security.RoleAssignments.Count > 0))
{
listitem.SetSecurity(parser, dataRow.Security);
}
}
}
catch (Exception ex)
{
if (ex.GetType().Equals(typeof(ServerException)) &&
(ex as ServerException).ServerErrorTypeName.Equals("Microsoft.SharePoint.SPDuplicateValuesFoundException", StringComparison.InvariantCultureIgnoreCase) &&
applyingInformation.IgnoreDuplicateDataRowErrors)
{
scope.LogWarning(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_listitem_duplicate);
continue;
}
else
{
scope.LogError(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_listitem_failed___0_____1_, ex.Message, ex.StackTrace);
throw;
}
}
}
}
}
#endregion
}
}
return(parser);
}
// Copy Method
private void CopyingProcess()
{
try
{
if (IsSPOnline == true)
{
SPOnlineContext = ClaimClientContext.GetAuthenticatedContext(cboSiteURL.SelectedItem.ToString());
SP.List sourceList = SPOnlineContext.Web.Lists.GetByTitle(cboSourceSiteList.SelectedItem.ToString());
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"";
ListItemCollection sourceCollListItem = sourceList.GetItems(camlQuery);
SPOnlineContext.Load(sourceCollListItem);
SPOnlineContext.ExecuteQuery();
int count = 0;
SP.List destinationList = SPOnlineContext.Web.Lists.GetByTitle(cboDestSiteList.SelectedItem.ToString());
foreach (ListItem sourceListItem in sourceCollListItem)
{
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem destinationListItem = destinationList.AddItem(itemCreateInfo);
switch (cboDestSiteList.SelectedItem.ToString())
{
case "AllGrantSubmits-Archive2017":
destinationListItem["Title"] = sourceListItem["Title"];
destinationListItem["Contact_x0020_Name"] = sourceListItem["Contact_x0020_Name"];
destinationListItem["Email"] = sourceListItem["Email"];
destinationListItem["Phone"] = sourceListItem["Phone"];
FieldLookupValue district = ((FieldLookupValue)sourceListItem["District"]);
destinationListItem["District"] = district.LookupId;
destinationListItem["Comments"] = sourceListItem["Comments"];
destinationListItem.Update();
SPOnlineContext.Load(destinationListItem);
SPOnlineContext.ExecuteQuery();
count++;
break;
} // END Switch
} // END FOR
MessageBox.Show("Transfer Completed! " + count + " item(s) copied");
}
else
{
// SP ON PREM Selections
SPOnPremContext = new ClientContext(cboSiteURL.SelectedItem.ToString());
SP.List sourceList = SPOnPremContext.Web.Lists.GetByTitle(cboSourceSiteList.SelectedItem.ToString());
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"";
ListItemCollection sourceCollListItem = sourceList.GetItems(camlQuery);
SPOnPremContext.Load(sourceCollListItem);
SPOnPremContext.ExecuteQuery();
int count = 0;
SP.List destinationList = SPOnPremContext.Web.Lists.GetByTitle(cboDestSiteList.SelectedItem.ToString());
foreach (ListItem sourceListItem in sourceCollListItem)
{
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem destinationListItem = destinationList.AddItem(itemCreateInfo);
switch (cboDestSiteList.SelectedItem.ToString())
{
case "CADD Office Leave Calendar":
FieldUserValue rf_user = ((FieldUserValue)sourceListItem["Request_x0020_For"]);
destinationListItem["Request_x0020_For"] = rf_user.LookupId;
destinationListItem["Title"] = ((FieldUserValue)sourceListItem["Request_x0020_For"]).LookupValue;
//destinationListItem["From"] = sourceListItem["From"];
//destinationListItem["To"] = sourceListItem["To"];
// Copies Custom [From] and [To] Form field data to built-in Form fields [Start Time] [End Time]
destinationListItem["EventDate"] = sourceListItem["From"];
destinationListItem["EndDate"] = sourceListItem["To"];
destinationListItem["Requested_x0020_Hours"] = sourceListItem["Requested_x0020_Hours"];
destinationListItem.Update();
SPOnPremContext.Load(destinationListItem);
SPOnPremContext.ExecuteQuery();
count++;
break;
} // END Switch
} //END FOR
MessageBox.Show("Transfer Completed! " + count + " item(s) copied");
}
}
catch (Exception ex)
{
ex.Message.ToString();
MessageBox.Show("Error Encountered " + ex.Message);
}
}
static void Main(string[] args)
{
if (!EventLog.SourceExists(evtLogSrc))
{
EventLog.CreateEventSource(evtLogSrc, "Application");
}
try
{
var runStartTime = DateTime.Now;
EventLog.WriteEntry(evtLogSrc, $"Run started {runStartTime}.", EventLogEntryType.Information);
string groupPrefix = "";
int cycleOffset = -1;
string accountsLstNm = "Accounts";
string ratesLstNm = "Rates";
string fixedConsumptionsLstNm = "Fixed Consumptions";
string consumptionsLstNm = "Consumptions";
string chargesLstNm = "Charges";
string billingPeriodStr = "1m";
bool isCycleOpen = false;
bool? incremental = null;
var authScheme = AuthScheme.ntlm;
string adfsServer = null;
string relyingParty = null;
string userName = null;
string domain = null;
string pwd = null;
bool deleteEndedFixedConsumptions = false;
string lastRunFileName = "billease_last_run.log";
DateTime cycleCalibrationDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1, 0, 0, 0, DateTimeKind.Local);
Dictionary <string, List <KeyValuePair <string, string> > > listColumnsToCopy = new Dictionary <string, List <KeyValuePair <string, string> > >();
listColumnsToCopy.Add("Account", new List <KeyValuePair <string, string> >());
listColumnsToCopy.Add("Rate", new List <KeyValuePair <string, string> >());
listColumnsToCopy.Add("Consumption", new List <KeyValuePair <string, string> >());
var options = new OptionSet()
{
{ "p|prefix_of_group=", v => groupPrefix = v }
, { "o|offset_of_cycle=", v => cycleOffset = int.Parse(v) }
, { "b|billing_period=", v => billingPeriodStr = v }
, { "d|cycle_calibration_date=", v => cycleCalibrationDate = DateTime.ParseExact(v, "yyyy-MM-dd", CultureInfo.InvariantCulture) }
, { "a|accounts_list_name=", v => accountsLstNm = v }
, { "r|rates_list_name=", v => ratesLstNm = v }
, { "f|fixed_consumptions_list_name=", v => fixedConsumptionsLstNm = v }
, { "c|consumptions_list_name=", v => consumptionsLstNm = v }
, { "h|charges_list_name=", v => chargesLstNm = v }
, { "A|account_columns_to_copy=", v => {
var src = v;
var dst = v;
if (v.Contains(":"))
{
src = v.Substring(0, v.IndexOf(":"));
dst = v.Substring(v.IndexOf(":") + 1);
}
listColumnsToCopy["Account"].Add(new KeyValuePair <string, string>(src, dst));
} }
, { "R|rate_columns_to_copy=", v => {
var src = v;
var dst = v;
if (v.Contains(":"))
{
src = v.Substring(0, v.IndexOf(":"));
dst = v.Substring(v.IndexOf(":") + 1);
}
listColumnsToCopy["Rate"].Add(new KeyValuePair <string, string>(src, dst));
} }
, { "C|consumption_columns_to_copy=", v => {
var src = v;
var dst = v;
if (v.Contains(":"))
{
src = v.Substring(0, v.IndexOf(":"));
dst = v.Substring(v.IndexOf(":") + 1);
}
listColumnsToCopy["Consumption"].Add(new KeyValuePair <string, string>(src, dst));
} }
, { "O|is_cycle_open=", v => isCycleOpen = Convert.ToBoolean(v) }
, { "i|incremental=", v => incremental = Convert.ToBoolean(v) }
, { "l|last_run_log_file_name=", v => lastRunFileName = v }
, { "s|auth_scheme=", v => authScheme = (AuthScheme)Enum.Parse(typeof(AuthScheme), v) }
, { "S|adfs_server=", v => adfsServer = v }
, { "P|relying_party=", v => relyingParty = v }
, { "u|username="******"D|domain=", v => domain = v }
, { "w|password="******"x|delete_ended_fixed_consumptions=", v => deleteEndedFixedConsumptions = Convert.ToBoolean(v) }
};
List <String> extraArgs = options.Parse(args);
if (incremental == null)
{
incremental = isCycleOpen;
}
ServicePointManager.ServerCertificateValidationCallback = MyCertHandler;
ClientContext cc = null;
switch (authScheme)
{
case AuthScheme.ntlm:
{
if (userName != null && pwd != null && domain != null)
{
OfficeDevPnP.Core.AuthenticationManager am = new OfficeDevPnP.Core.AuthenticationManager();
cc = am.GetNetworkCredentialAuthenticatedContext(extraArgs[0], userName, pwd, domain);
}
else
{
cc = new ClientContext(extraArgs[0]);
cc.Credentials = System.Net.CredentialCache.DefaultCredentials;
}
break;
}
case AuthScheme.adfs:
{
OfficeDevPnP.Core.AuthenticationManager am = new OfficeDevPnP.Core.AuthenticationManager();
cc = am.GetADFSUserNameMixedAuthenticatedContext(extraArgs[0], userName, pwd, domain, adfsServer, relyingParty);
break;
}
}
Match billingPeriodMatch = Regex.Match(billingPeriodStr, @"(\d)([mdy])");
int billingPeriod = 0;
string billingPeriodUOM = null;
if (billingPeriodMatch.Success)
{
billingPeriod = Int32.Parse(billingPeriodMatch.Groups[1].Value);
billingPeriodUOM = billingPeriodMatch.Groups[2].Value;
}
var billingCycleStart = DateTime.Now;
TimeSpan s = billingCycleStart - cycleCalibrationDate;
switch (billingPeriodUOM)
{
case "d":
billingCycleStart = cycleCalibrationDate.AddDays(s.TotalDays - (s.TotalDays % billingPeriod) + cycleOffset * billingPeriod);
break;
case "m":
int monthDiff = (billingCycleStart.Year - cycleCalibrationDate.Year) * 12 + billingCycleStart.Month - cycleCalibrationDate.Month;
billingCycleStart = cycleCalibrationDate.AddMonths(monthDiff - (monthDiff % billingPeriod) + cycleOffset * billingPeriod);
break;
case "y":
int yearDiff = (int)s.TotalDays / 365;
billingCycleStart = cycleCalibrationDate.AddYears(yearDiff - (yearDiff % billingPeriod) + cycleOffset * billingPeriod);
break;
}
DateTime nextBillingcycleStart = DateTime.Now;
switch (billingPeriodUOM)
{
case "d":
nextBillingcycleStart = billingCycleStart.AddDays(billingPeriod);
break;
case "m":
nextBillingcycleStart = billingCycleStart.AddMonths(billingPeriod);
break;
case "y":
nextBillingcycleStart = billingCycleStart.AddYears(billingPeriod);
break;
}
TimeRange billingRange = new TimeRange(billingCycleStart, nextBillingcycleStart);
// get last run timestamp
var lastRunTs = new DateTime(1970, 01, 01);
try
{
string[] lines = System.IO.File.ReadAllLines(lastRunFileName);
DateTime lastRunCycle = DateTime.Parse(lines[0]);
if (lastRunCycle.Date == billingCycleStart.Date)
{
lastRunTs = DateTime.Parse(lines[1]);
}
}
catch
{
}
var query = new CamlQuery();
// deleted ended fixed consumptions if cycle is closed
try
{
if (deleteEndedFixedConsumptions && !isCycleOpen)
{
query.ViewXml = $@"<View><Query>
<Where>
<Lt>
<FieldRef Name='Service_x0020_End' />
<Value Type=”DateTime”>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Lt>
</Where>
</Query></View>";
var fixedConsumptionLst = cc.Web.Lists.GetByTitle(fixedConsumptionsLstNm);
var fixedConsumptionDeletionLIC = fixedConsumptionLst.GetItems(query);
cc.Load(fixedConsumptionDeletionLIC);
cc.ExecuteQuery();
while (fixedConsumptionDeletionLIC.Count > 0)
{
ListItem fixedConsumptionLI = null;
try
{
fixedConsumptionLI = fixedConsumptionDeletionLIC[0];
fixedConsumptionLI.DeleteObject();
cc.ExecuteQuery();
}
catch (Exception ex)
{
EventLog.WriteEntry(evtLogSrc,
$@"Error delete ended fixed consumption with id {fixedConsumptionLI["ID"]}.
{ex}"
, EventLogEntryType.Error);
}
}
}
}
catch (Exception ex)
{
EventLog.WriteEntry(evtLogSrc, $@"Error delete ended fixed consumptions.
{ex}", EventLogEntryType.Error);
}
// delete consumptions associated with
// deleted or ended fixed consumptions
query.ViewXml = $@"
<View><Query>
<Where>
<And>
<Eq>
<FieldRef Name='Cycle' />
<Value Type='DateTime'>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Eq>
<Or>
<Or>
<Geq>
<FieldRef Name='Fixed_x0020_Consumption_x0020_Re0' />
<Value Type=”DateTime”>{nextBillingcycleStart.ToString("yyyy-MM-dd")}</Value>
</Geq>
<Lt>
<FieldRef Name='Fixed_x0020_Consumption_x0020_Re1' />
<Value Type=”DateTime”>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Lt>
</Or>
<And>
<IsNull>
<FieldRef Name='Fixed_x0020_Consumption_x0020_Re' />
</IsNull>
<Geq>
<FieldRef Name='Fixed_x0020_Consumption_x0020_Re' LookupId='TRUE' />
<Value Type=”Lookup”>0</Value>
</Geq>
</And>
</Or>
</And>
</Where>
</Query></View>";
var consumptionLst = cc.Web.Lists.GetByTitle(consumptionsLstNm);
var consumptionFC = consumptionLst.Fields;
var consumptionDeletionLIC = consumptionLst.GetItems(query);
cc.Load(consumptionFC);
cc.Load(consumptionDeletionLIC);
cc.ExecuteQuery();
while (consumptionDeletionLIC.Count > 0)
{
var consumptionLI = consumptionDeletionLIC[0];
consumptionLI.DeleteObject();
cc.ExecuteQuery();
}
// delete charges associated with deleted consumptions
query = new CamlQuery();
query.ViewXml = $@"<View><Query>
<Where>
<And>
<Eq>
<FieldRef Name='Cycle' />
<Value Type='DateTime'>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Eq>
<And>
<IsNull>
<FieldRef Name='Consumption_x0020_Ref' />
</IsNull>
<Geq>
<FieldRef Name='Consumption_x0020_Ref' LookupId='TRUE' />
<Value Type=”Lookup”>0</Value>
</Geq>
</And>
</And>
</Where>
</Query></View>";
var chgLst = cc.Web.Lists.GetByTitle(chargesLstNm);
var chargesDeletionLIC = chgLst.GetItems(query);
cc.Load(chargesDeletionLIC);
cc.ExecuteQuery();
while (chargesDeletionLIC.Count > 0)
{
var chargeLI = chargesDeletionLIC[0];
chargeLI.DeleteObject();
cc.ExecuteQuery();
}
// populate or update consumptions from fixed consumptions
query = new CamlQuery();
query.ViewXml = $@"
<View><Query>
<Where>
<And>
<Or>
<IsNull>
<FieldRef Name='Service_x0020_Start' />
</IsNull>
<Lt>
<FieldRef Name='Service_x0020_Start' />
<Value Type='DateTime'>{nextBillingcycleStart.ToString("yyyy-MM-dd")}</Value>
</Lt>
</Or>
<Or>
<IsNull>
<FieldRef Name='Service_x0020_End' />
</IsNull>
<Gt>
<FieldRef Name='Service_x0020_End' />
<Value Type='DateTime'>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Gt>
</Or>
</And>
</Where>
</Query></View>";
var fixedConsumptionsLst = cc.Web.Lists.GetByTitle(fixedConsumptionsLstNm);
var fixedConsumptionLIC = fixedConsumptionsLst.GetItems(query);
FieldCollection fixedConsumptionFC = fixedConsumptionsLst.Fields;
cc.Load(fixedConsumptionFC);
cc.Load(fixedConsumptionLIC);
try
{
cc.ExecuteQuery();
foreach (var fixedConsumptionLI in fixedConsumptionLIC)
{
try
{
// check if consumption exists for the current billing cycle
var consumptionItemQuery = new CamlQuery();
consumptionItemQuery.ViewXml = $@"
<View><Query>
<Where>
<And>
<Eq>
<FieldRef Name='Fixed_x0020_Consumption_x0020_Re' LookupId='TRUE' />
<Value Type='Lookup'>{fixedConsumptionLI["ID"]}</Value>
</Eq>
<Eq>
<FieldRef Name='Cycle' />
<Value Type='DateTime'>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Eq>
</And>
</Where>
</Query></View>";
var _consumptionLIC = consumptionLst.GetItems(consumptionItemQuery);
cc.Load(_consumptionLIC);
cc.ExecuteQuery();
ListItem consumptionItem;
if (_consumptionLIC.Count > 0)
{
if (((DateTime)fixedConsumptionLI["Modified"]) < ((DateTime)_consumptionLIC[0]["Modified"]))
{
continue;
}
consumptionItem = _consumptionLIC[0];
}
else
{
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
consumptionItem = consumptionLst.AddItem(itemCreateInfo);
}
foreach (Field field in fixedConsumptionFC)
{
if (field.FromBaseType && field.InternalName != "Title")
{
continue;
}
if (consumptionFC.FirstOrDefault(f =>
(!f.FromBaseType || f.InternalName == "Title") && f.InternalName == field.InternalName
) == null)
{
continue;
}
consumptionItem[field.InternalName] = fixedConsumptionLI[field.InternalName];
}
// calculate proration
try
{
if (fixedConsumptionLI["Prorated"] != null &&
fixedConsumptionLI["Prorated"].ToString().Contains("Yes")
)
{
DateTime serviceStart = DateTime.MinValue;
DateTime serviceEnd = DateTime.MaxValue;
if (fixedConsumptionLI["Service_x0020_Start"] != null)
{
serviceStart = ((DateTime)fixedConsumptionLI["Service_x0020_Start"]).ToLocalTime();
}
if (fixedConsumptionLI["Service_x0020_End"] != null)
{
serviceEnd = ((DateTime)fixedConsumptionLI["Service_x0020_End"]).ToLocalTime();
}
TimeRange serviceRange = new TimeRange(serviceStart, serviceEnd);
ITimeRange overlap = serviceRange.GetIntersection(billingRange);
double portion = overlap.Duration.TotalDays / billingRange.Duration.TotalDays;
if (fixedConsumptionLI["Quantity"] != null)
{
var proratedQty = ((double)fixedConsumptionLI["Quantity"]) * portion;
if (fixedConsumptionLI["Prorated"].ToString() != "Yes")
{
proratedQty = Convert.ToInt32(proratedQty);
}
consumptionItem["Quantity"] = proratedQty;
}
if (fixedConsumptionLI["Amount"] != null)
{
consumptionItem["Amount"] = ((double)fixedConsumptionLI["Amount"]) * portion;
}
}
}
catch
{
EventLog.WriteEntry(evtLogSrc, $@"Error calculating proration for fixed consumption with ID={fixedConsumptionLI["ID"]}", EventLogEntryType.Error);
}
consumptionItem["Cycle"] = billingCycleStart;
FieldLookupValue lookup = new FieldLookupValue();
lookup.LookupId = (int)fixedConsumptionLI["ID"];
consumptionItem["Fixed_x0020_Consumption_x0020_Re"] = lookup;
consumptionItem.Update();
cc.ExecuteQuery();
}
catch (Exception ex)
{
EventLog.WriteEntry(evtLogSrc, $@"Error creating consumption from fixed consumption with ID {fixedConsumptionLI["ID"]}.
{ex}", EventLogEntryType.Error);
}
}
}
catch (Exception ex)
{
EventLog.WriteEntry(evtLogSrc, $@"Error creating consumption from fixed consumption.
{ex}", EventLogEntryType.Error);
}
// set incremental to false if there are updated accounts or rates since last run,
foreach (var lstNm in new string[] { ratesLstNm, accountsLstNm })
{
if (incremental != false)
{
query = new CamlQuery();
query.ViewXml = $@"
<View>
<Query>
<Where>
<Gt>
<FieldRef Name='Modified' />
<Value IncludeTimeValue='true' Type='DateTime'>{lastRunTs.ToString("yyyy-MM-ddTHH:mm:ssZ")}</Value>
</Gt>
</Where>
</Query>
<RowLimit>1</RowLimit>
</View>";
var lst = cc.Web.Lists.GetByTitle(lstNm);
var lic = lst.GetItems(query);
cc.Load(lic);
cc.ExecuteQuery();
if (lic.Count > 0)
{
incremental = false;
}
}
}
// populate or update charge from consumption
query = new CamlQuery();
string viewXml = null;
if (incremental == false)
{
viewXml = $@"
<View><Query>
<Where>
<Eq>
<FieldRef Name='Cycle' />
<Value Type='DateTime'>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Eq>
</Where>
</Query></View>";
}
else
{
viewXml = $@"
<View><Query>
<Where>
<And>
<Eq>
<FieldRef Name='Cycle' />
<Value Type='DateTime'>{billingCycleStart.ToString("yyyy-MM-dd")}</Value>
</Eq>
<Gt>
<FieldRef Name='Modified' />
<Value IncludeTimeValue='true' Type='DateTime'>{lastRunTs.ToString("yyyy-MM-ddTHH:mm:ssZ")}</Value>
</Gt>
</And>
</Where>
</Query></View>";
}
query.ViewXml = viewXml;
var consumptionLIC = consumptionLst.GetItems(query);
cc.Load(consumptionLIC, items => items.IncludeWithDefaultProperties(
item => item["HasUniqueRoleAssignments"]
));
cc.Load(cc.Web.RoleDefinitions);
var gc = cc.Web.SiteGroups;
cc.Load(gc);
var readRD = cc.Web.RoleDefinitions.GetByName("Read");
cc.ExecuteQuery();
RoleDefinition restReadRD;
try
{
restReadRD = cc.Web.RoleDefinitions.GetByName("Restricted Read");
cc.ExecuteQuery();
}
catch
{
restReadRD = null;
}
foreach (var consumptionLI in consumptionLIC)
{
ListItem chgItem;
// check if charges exists
var chgItemQuery = new CamlQuery();
chgItemQuery.ViewXml = $@"
<View><Query>
<Where>
<Eq>
<FieldRef Name='Consumption_x0020_Ref' LookupId='TRUE' />
<Value Type='Lookup'>{consumptionLI["ID"]}</Value>
</Eq>
</Where>
</Query></View>";
var chgLIC = chgLst.GetItems(chgItemQuery);
cc.Load(chgLIC);
cc.ExecuteQuery();
if (chgLIC.Count > 0)
{
chgItem = chgLIC[0];
if (isCycleOpen && incremental != false && ((DateTime)consumptionLI["Modified"]) < ((DateTime)chgItem["Modified"]))
{
continue;
}
chgItem.ResetRoleInheritance();
cc.ExecuteQuery();
}
else
{
// create new charges item
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
chgItem = chgLst.AddItem(itemCreateInfo);
}
// get org item
var orgItemQuery = new CamlQuery();
orgItemQuery.ViewXml = $@"
<View><Query>
<Where>
<Eq>
<FieldRef Name='ID' />
<Value Type='Counter'>{((FieldLookupValue)consumptionLI["Account"]).LookupId}</Value>
</Eq>
</Where>
</Query></View>";
var orgLst = cc.Web.Lists.GetByTitle(accountsLstNm);
var orgLIC = orgLst.GetItems(orgItemQuery);
cc.Load(orgLIC);
// get rate item
var rateItemQuery = new CamlQuery();
rateItemQuery.ViewXml = $@"<View><Query>
<Where>
<Eq>
<FieldRef Name='ID' />
<Value Type='Counter'>{((FieldLookupValue)consumptionLI["Rate"]).LookupId}</Value>
</Eq>
</Where>
</Query></View>";
var rateLst = cc.Web.Lists.GetByTitle(ratesLstNm);
var rateLIC = rateLst.GetItems(rateItemQuery);
cc.Load(rateLIC);
cc.ExecuteQuery();
var orgItem = orgLIC.First();
var rateItem = rateLIC.First();
chgItem["Account"] = orgItem["Title"];
chgItem["Title"] = consumptionLI["Title"];
chgItem["Cycle"] = consumptionLI["Cycle"];
chgItem["Unit_x0020_Price"] = rateItem["Unit_x0020_Price"];
chgItem["Denominator"] = rateItem["Denominator"];
chgItem["UOM"] = rateItem["UOM"];
chgItem["Quantity"] = consumptionLI["Quantity"];
FieldLookupValue lookup = new FieldLookupValue();
lookup.LookupId = (int)consumptionLI["ID"];
chgItem["Consumption_x0020_Ref"] = lookup;
// the order of list enumeration is important
foreach (var lstNm in new string[] { "Account", "Rate", "Consumption" })
{
KeyValuePair <string, List <KeyValuePair <string, string> > > listColumnToCopy = new KeyValuePair <string, List <KeyValuePair <string, string> > >(lstNm, listColumnsToCopy[lstNm]);
if (listColumnToCopy.Value.Count <= 0)
{
continue;
}
ListItem item = null;
switch (listColumnToCopy.Key)
{
case "Account":
item = orgItem;
break;
case "Rate":
item = rateItem;
break;
case "Consumption":
item = consumptionLI;
break;
}
foreach (var columnNVP in listColumnToCopy.Value)
{
try
{
if (item[columnNVP.Key] != null)
{
chgItem[columnNVP.Value] = item[columnNVP.Key];
}
}
catch
{
EventLog.WriteEntry(evtLogSrc, $@"Cannot copy column {columnNVP.Key} in list {listColumnToCopy.Key} for consumption ID={consumptionLI["ID"]}", EventLogEntryType.Error);
}
}
}
if (consumptionLI["Amount"] != null)
{
chgItem["Amount"] = consumptionLI["Amount"];
}
else if (consumptionLI["Quantity"] != null &&
rateItem["Denominator"] != null &&
rateItem["Unit_x0020_Price"] != null &&
rateItem["Denominator"] != null &&
(double)rateItem["Denominator"] > 0)
{
var normalizedQty = (double)consumptionLI["Quantity"] / (double)rateItem["Denominator"];
try
{
if (rateItem["Round_x0020_Up"] != null && rateItem["Round_x0020_Up"] as Nullable <bool> == true)
{
normalizedQty = Math.Ceiling((double)consumptionLI["Quantity"] / (double)rateItem["Denominator"]);
}
}
catch
{
EventLog.WriteEntry(evtLogSrc, $"Error calculate round up for rate ID={rateItem["ID"]}, consumption ID={consumptionLI["ID"]}", EventLogEntryType.Error);
}
chgItem["Amount"] = (double)rateItem["Unit_x0020_Price"] * normalizedQty;
}
if (chgItem.FieldValues.ContainsKey("Amount"))
{
chgItem.Update();
cc.ExecuteQuery();
}
else
{
EventLog.WriteEntry(evtLogSrc, "Cannot calculate amount for consumption item " + consumptionLI["ID"], EventLogEntryType.Error);
}
if (!isCycleOpen)
{
if (!consumptionLI.HasUniqueRoleAssignments)
{
consumptionLI.BreakRoleInheritance(true, false);
cc.ExecuteQuery();
}
cc.Load(consumptionLI.RoleAssignments, items => items.Include(
ra => ra.RoleDefinitionBindings.Include(
rdb => rdb.Name
)
));
cc.ExecuteQuery();
foreach (var ra in consumptionLI.RoleAssignments)
{
bool addRead = false;
foreach (var rdbo in ra.RoleDefinitionBindings)
{
if (rdbo.Name == "Contribute")
{
ra.RoleDefinitionBindings.Remove(rdbo);
addRead = true;
}
}
if (addRead)
{
ra.RoleDefinitionBindings.Add(readRD);
}
ra.Update();
}
cc.ExecuteQuery();
}
}
var chargesLst = cc.Web.Lists.GetByTitle(chargesLstNm);
var chargesLIC = chargesLst.GetItems(query);
cc.Load(chargesLIC, items => items.Include(
item => item["Account"]
, item => item["HasUniqueRoleAssignments"]
));
cc.ExecuteQuery();
foreach (var chargeLI in chargesLIC)
{
if (chargeLI.HasUniqueRoleAssignments)
{
chargeLI.ResetRoleInheritance();
cc.ExecuteQuery();
}
chargeLI.BreakRoleInheritance(true, false);
foreach (var g in gc)
{
if (g.LoginName == (groupPrefix + chargeLI["Account"].ToString()))
{
var rdb = new RoleDefinitionBindingCollection(cc);
rdb.Add(restReadRD != null ? restReadRD : readRD);
chargeLI.RoleAssignments.Add(g, rdb);
break;
}
}
cc.ExecuteQuery();
}
var runEndTime = DateTime.Now;
EventLog.WriteEntry(evtLogSrc, $"Run ended {runEndTime}, lasting {(runEndTime - runStartTime).TotalMinutes.ToString("0.00")} minutes.", EventLogEntryType.Information);
System.IO.File.Delete(lastRunFileName);
System.IO.File.WriteAllLines(lastRunFileName, new string[] { billingCycleStart.ToShortDateString(), runStartTime.ToString() });
}
catch (Exception ex)
{
EventLog.WriteEntry(evtLogSrc, $"{ex}", EventLogEntryType.Error);
throw;
}
}
private static List <ItensListaModel> TratamentoRetornoItens(ListItemCollection listItemcollection, out ErroModel registroErro)
{
List <ItensListaModel> itens = new List <ItensListaModel>();
registroErro = null;
try
{
foreach (var item in listItemcollection)
{
// Obtem o ID do documento e verifica se o mesmo é um documento válido
ItensListaModel process = new ItensListaModel();
//verifica o tipo do campo obtido em cada item, verifica a propriedade e adiciona o valor correto.
foreach (var field in item.FieldValues)
{
if (field.Value is ClientValueObject)
{
try
{
string lookupValue = "";
int lookupId = 0;
FieldLookupValue lookupField = field.Value as FieldLookupValue;
if (lookupField != null)
{
lookupValue = lookupField.LookupValue;
lookupId = lookupField.LookupId;
}
else
{
FieldUserValue lookupUser = field.Value as FieldUserValue;
lookupValue = lookupUser.LookupValue;
lookupId = lookupUser.LookupId;
}
PropertyInfo propId = process.GetType().GetProperty("ID_" + field.Key);
if (propId != null && propId.CanWrite)
{
propId.SetValue(process, lookupId, null);
}
PropertyInfo propDesc = process.GetType().GetProperty("Nome_" + field.Key);
if (propDesc != null && propDesc.CanWrite)
{
propDesc.SetValue(process, lookupValue, null);
}
}
catch (Exception ex)
{
registroErro.Mensagem = $"Ocorreu um erro durante o tratamento das propriedades para campos do tipo ClientValueObject. Mensagem: {ex.Message}";
registroErro.Detalhes = ex.StackTrace;
registroErro.AppOrObjeto = ex.Source;
continue;
}
}
else
{
try
{
PropertyInfo prop = process.GetType().GetProperty(field.Key);
if (prop != null && prop.CanWrite)
{
var val = field.Value;
if (prop.PropertyType.Name == "Boolean" || prop.PropertyType.Name == "Int32" || prop.PropertyType.Name == "Single")
{
val = Convert.ToInt32(val);
}
if (prop.PropertyType.Name == "DateTime")
{
val = Convert.ToDateTime(val);
}
if (prop.PropertyType.Name == "String" && val != null)
{
val = val.ToString();
}
prop.SetValue(process, val, null);
}
}
catch (Exception ex)
{
registroErro.Mensagem = $"Ocorreu um erro durante o tratamento dos campos genéricos. Mensagem: {ex.Message}";
registroErro.Detalhes = ex.StackTrace;
registroErro.AppOrObjeto = ex.Source;
continue;
}
}
}
itens.Add(process);
}
}
catch (Exception ex)
{
registroErro.Mensagem = $"Ocorreu um erro durante o o tratamento de retorno dos itens. Mensagem: {ex.Message}";
registroErro.Detalhes = ex.StackTrace;
registroErro.AppOrObjeto = ex.Source;
}
return(itens);
}
public override object GetFieldValueTyped(string value)
{
object valueTyped = null;
FieldLookup fieldLookup = this.Field as FieldLookup;
if (null != fieldLookup)
{
if (fieldLookup.AllowMultipleValues)
{
List<FieldLookupValue> itemValues = new List<FieldLookupValue>();
string[] parts = value.Split(new string[] { SEPARATOR }, StringSplitOptions.RemoveEmptyEntries);
foreach (string str in parts)
{
int id;
if (int.TryParse(str, out id) && (0 < id))
{
itemValues.Add(new FieldLookupValue()
{
LookupId = id
});
}
}
if (0 < itemValues.Count)
{
valueTyped = itemValues.ToArray();
}
}
else
{
int id;
if (int.TryParse(value, out id) && (0 < id))
{
valueTyped = new FieldLookupValue()
{
LookupId = id
};
}
}
}
return valueTyped;
}
public override void ProvisionObjects(Web web, ProvisioningTemplate template, ProvisioningTemplateApplyingInformation applyingInformation)
{
Log.Info(Constants.LOGGING_SOURCE_FRAMEWORK_PROVISIONING, CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows);
if (template.Lists.Any())
{
var rootWeb = (web.Context as ClientContext).Site.RootWeb;
if (!web.IsPropertyAvailable("ServerRelativeUrl"))
{
web.Context.Load(web, w => w.ServerRelativeUrl);
web.Context.ExecuteQueryRetry();
}
web.Context.Load(web.Lists, lc => lc.IncludeWithDefaultProperties(l => l.RootFolder.ServerRelativeUrl));
web.Context.ExecuteQueryRetry();
var existingLists = web.Lists.AsEnumerable <List>().Select(existingList => existingList.RootFolder.ServerRelativeUrl).ToList();
var serverRelativeUrl = web.ServerRelativeUrl;
#region DataRows
foreach (var listInstance in template.Lists)
{
if (listInstance.DataRows != null && listInstance.DataRows.Any())
{
// Retrieve the target list
var list = web.Lists.GetByTitle(listInstance.Title);
web.Context.Load(list);
// Retrieve the fields' types from the list
FieldCollection fields = list.Fields;
web.Context.Load(fields, fs => fs.Include(f => f.InternalName, f => f.FieldTypeKind));
web.Context.ExecuteQueryRetry();
foreach (var dataRow in listInstance.DataRows)
{
var listitemCI = new ListItemCreationInformation();
var listitem = list.AddItem(listitemCI);
foreach (var dataValue in dataRow.Values)
{
Field dataField = fields.FirstOrDefault(
f => f.InternalName == dataValue.Key.ToParsedString());
if (dataField != null)
{
String fieldValue = dataValue.Value.ToParsedString();
switch (dataField.FieldTypeKind)
{
case FieldType.Geolocation:
// FieldGeolocationValue - Expected format: Altitude,Latitude,Longitude,Measure
var geolocationArray = fieldValue.Split(',');
if (geolocationArray.Length == 4)
{
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse(geolocationArray[0]),
Latitude = Double.Parse(geolocationArray[1]),
Longitude = Double.Parse(geolocationArray[2]),
Measure = Double.Parse(geolocationArray[3]),
};
listitem[dataValue.Key.ToParsedString()] = geolocationValue;
}
else
{
listitem[dataValue.Key.ToParsedString()] = fieldValue;
}
break;
case FieldType.Lookup:
// FieldLookupValue - Expected format: LookupID
var lookupValue = new FieldLookupValue
{
LookupId = Int32.Parse(fieldValue),
};
listitem[dataValue.Key.ToParsedString()] = lookupValue;
break;
case FieldType.URL:
// FieldUrlValue - Expected format: URL,Description
var urlArray = fieldValue.Split(',');
var linkValue = new FieldUrlValue();
if (urlArray.Length == 2)
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[1];
}
else
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[0];
}
listitem[dataValue.Key.ToParsedString()] = linkValue;
break;
case FieldType.User:
// FieldUserValue - Expected format: loginName
var user = web.EnsureUser(fieldValue);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
var userValue = new FieldUserValue
{
LookupId = user.Id,
};
listitem[dataValue.Key.ToParsedString()] = userValue;
}
else
{
listitem[dataValue.Key.ToParsedString()] = fieldValue;
}
break;
default:
listitem[dataValue.Key.ToParsedString()] = fieldValue;
break;
}
}
listitem.Update();
}
web.Context.ExecuteQueryRetry(); // TODO: Run in batches?
}
}
}
#endregion
}
}
public override TokenParser ProvisionObjects(Web web, ProvisioningTemplate template, TokenParser parser, ProvisioningTemplateApplyingInformation applyingInformation)
{
using (var scope = new PnPMonitoredScope(this.Name))
{
if (template.Lists.Any())
{
var rootWeb = (web.Context as ClientContext).Site.RootWeb;
web.EnsureProperties(w => w.ServerRelativeUrl);
web.Context.Load(web.Lists, lc => lc.IncludeWithDefaultProperties(l => l.RootFolder.ServerRelativeUrl));
web.Context.ExecuteQueryRetry();
var existingLists = web.Lists.AsEnumerable <List>().Select(existingList => existingList.RootFolder.ServerRelativeUrl).ToList();
var serverRelativeUrl = web.ServerRelativeUrl;
#region DataRows
foreach (var listInstance in template.Lists)
{
if (listInstance.DataRows != null && listInstance.DataRows.Any())
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Processing_data_rows_for__0_, listInstance.Title);
// Retrieve the target list
var list = web.Lists.GetByTitle(listInstance.Title);
web.Context.Load(list);
// Retrieve the fields' types from the list
Microsoft.SharePoint.Client.FieldCollection fields = list.Fields;
web.Context.Load(fields, fs => fs.Include(f => f.InternalName, f => f.FieldTypeKind));
web.Context.ExecuteQueryRetry();
foreach (var dataRow in listInstance.DataRows)
{
try
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_list_item__0_, listInstance.DataRows.IndexOf(dataRow) + 1);
var listitemCI = new ListItemCreationInformation();
var listitem = list.AddItem(listitemCI);
foreach (var dataValue in dataRow.Values)
{
Field dataField = fields.FirstOrDefault(
f => f.InternalName == parser.ParseString(dataValue.Key));
if (dataField != null)
{
String fieldValue = parser.ParseString(dataValue.Value);
switch (dataField.FieldTypeKind)
{
case FieldType.Geolocation:
// FieldGeolocationValue - Expected format: Altitude,Latitude,Longitude,Measure
var geolocationArray = fieldValue.Split(',');
if (geolocationArray.Length == 4)
{
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse(geolocationArray[0]),
Latitude = Double.Parse(geolocationArray[1]),
Longitude = Double.Parse(geolocationArray[2]),
Measure = Double.Parse(geolocationArray[3]),
};
listitem[parser.ParseString(dataValue.Key)] = geolocationValue;
}
else
{
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
}
break;
case FieldType.Lookup:
// FieldLookupValue - Expected format: LookupID
var lookupValue = new FieldLookupValue
{
LookupId = Int32.Parse(fieldValue),
};
listitem[parser.ParseString(dataValue.Key)] = lookupValue;
break;
case FieldType.URL:
// FieldUrlValue - Expected format: URL,Description
var urlArray = fieldValue.Split(',');
var linkValue = new FieldUrlValue();
if (urlArray.Length == 2)
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[1];
}
else
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[0];
}
listitem[parser.ParseString(dataValue.Key)] = linkValue;
break;
case FieldType.User:
// FieldUserValue - Expected format: loginName
var user = web.EnsureUser(fieldValue);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
var userValue = new FieldUserValue
{
LookupId = user.Id,
};
listitem[parser.ParseString(dataValue.Key)] = userValue;
}
else
{
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
}
break;
default:
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
break;
}
}
listitem.Update();
}
web.Context.ExecuteQueryRetry(); // TODO: Run in batches?
if (dataRow.Security != null && dataRow.Security.RoleAssignments.Count != 0)
{
listitem.SetSecurity(parser, dataRow.Security);
}
}
catch (Exception ex)
{
scope.LogError(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_listitem_failed___0_____1_, ex.Message, ex.StackTrace);
throw;
}
}
}
}
#endregion
}
}
return(parser);
}
public ActionResult TotalPedidos()
{
//Cargas la conexión al sharepoint. Como puede ser con distintos sharepoints, se usa
// el current dentro del provider
var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
var clientContext = spContext.CreateUserClientContextForSPHost();
using (clientContext)
{
if (clientContext != null)
{
//Del cliente context recuperas "web" que es
// lo que hay en la web a la que has conectado
Web web = clientContext.Web;
clientContext.Load(web);
clientContext.ExecuteQuery();
//Una vez cargada de la web obtienes las listas
ListCollection lists = web.Lists;
clientContext.Load <ListCollection>(lists);
clientContext.ExecuteQuery();
var pedidos = lists.GetByTitle("Pedidos");
var productos = lists.GetByTitle("Productos");
clientContext.Load(pedidos);
clientContext.Load(productos);
clientContext.ExecuteQuery();
//Se crea la caml query, aunque en este caso está vacía, pero podría
//levar las instrucciones (en xml) para filtrar por lo que fuese
CamlQuery pedidosQuery = new CamlQuery();
//Hay que pasar siempre una query, aunque esté vacía
ListItemCollection pedidosItems = pedidos.GetItems(pedidosQuery);
clientContext.Load(pedidosItems);
clientContext.ExecuteQuery();
var total = 0.0;
var clientes = new Dictionary <string, double>();
foreach (var item in pedidosItems)
{
//Indicas el valor del campo de búsqueda(lookup)
FieldLookupValue lookUp = item["Producto"] as FieldLookupValue;
//Recuperas el ID del campo lookup
int luID = lookUp.LookupId;
var uds = item["Unidades"];
//De la tabla productos, con el ID del campo de búsqueda, recuperas
//el resto de los datos
var prod = productos.GetItemById(luID);
clientContext.Load(prod);
clientContext.ExecuteQuery();
var precio = prod["Precio"];
var venta = (double)precio * (double)uds;
total += venta;
if (clientes.ContainsKey((item["Title"].ToString())))
{
clientes[item["Title"].ToString()] =
clientes[item["Title"].ToString()] +
venta;
}
else
{
clientes.Add(item["Title"].ToString(), venta);
}
}
var mc = total / clientes.Keys.Count;
var model = new Totales()
{
Numero = pedidosItems.Count,
MediaCliente = mc,
Total = total
};
return(View(model));
}
}
return(null);
}
void UpdatePowerPointProperties(ClientContext clientContext, Guid listId, int listItemId)
{
List list = clientContext.Web.Lists.GetById(listId);
ListItem item = list.GetItemById(listItemId);
clientContext.Load(item);
clientContext.ExecuteQuery();
if (item["File_x0020_Type"].ToString() != "pptx")
{
return;
}
File file = item.File;
ClientResult <SystemIO.Stream> data = file.OpenBinaryStream();
// Load the Stream data for the file
Site site = clientContext.Site;
clientContext.Load(site);
clientContext.Load(file);
clientContext.ExecuteQuery();
List <FieldTitles> fieldTypes = new List <FieldTitles>();
fieldTypes.Add(new FieldTitles("Author", FieldTitleType.UserLookup));
fieldTypes.Add(new FieldTitles("BusinessConsultant", FieldTitleType.Lookup));
fieldTypes.Add(new FieldTitles("BusinessConsultantFirstName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("BusinessConsultantLastName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("BusinessConsultantEmail", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("BusinessConsultantMobile", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("FunctionalConsultant", FieldTitleType.Lookup));
fieldTypes.Add(new FieldTitles("FunctionalConsultantFirstName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("FunctionalConsultantLastName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("FunctionalConsultantEmail", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("FunctionalConsultantMobile", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ProjectManager", FieldTitleType.Lookup));
fieldTypes.Add(new FieldTitles("ProjectManagerFirstName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ProjectManagerLastName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ProjectManagerEmail", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ProjectManagerMobile", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ClientLeadTitle", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ClientLeadFirstName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ClientLeadLastName", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ClientLeadEmail", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ClientLeadMobile", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("ProjectTitle", FieldTitleType.String));
fieldTypes.Add(new FieldTitles("Client", FieldTitleType.String));
PowerPointTokenReplacements = new List <PowerPointParameter>();
foreach (FieldTitles fieldTitle in fieldTypes)
{
string value = "";
if (item[fieldTitle.title] == null)
{
continue;
}
switch (fieldTitle.type)
{
case FieldTitleType.UserLookup:
FieldUserValue fuv = (FieldUserValue)item[fieldTitle.title];
value = fuv.LookupValue;
break;
case FieldTitleType.String:
value = item[fieldTitle.title].ToString();
break;
case FieldTitleType.Lookup:
FieldLookupValue lv = (FieldLookupValue)item[fieldTitle.title];
value = lv.LookupValue;
break;
default:
value = item[fieldTitle.title].ToString();
break;
}
if (string.IsNullOrEmpty(value))
{
continue;
}
PowerPointTokenReplacements.Add(new PowerPointParameter()
{
Name = "[#" + fieldTitle.title + "#]", Text = value
});
}
if (!PowerPointTokenReplacements.Any())
{
return;
}
using (SystemIO.MemoryStream memoryStream = new SystemIO.MemoryStream())
{
data.Value.CopyTo(memoryStream);
memoryStream.Seek(0, SystemIO.SeekOrigin.Begin);
using (PresentationDocument doc = PresentationDocument.Open(memoryStream, true))
{
// Get the presentation part from the presentation document.
var presentationPart = doc.PresentationPart;
// Get the presentation from the presentation part.
var presentation = presentationPart.Presentation;
var slideList = new List <SlidePart>();
SlideDictionary = new Dictionary <SlidePart, SlideId>();
//get available slide list
foreach (SlideId slideID in presentation.SlideIdList)
{
var slide = (SlidePart)presentationPart.GetPartById(slideID.RelationshipId);
slideList.Add(slide);
SlideDictionary.Add(slide, slideID);//add to dictionary to be used when needed
}
//loop all slides and replace images and texts
foreach (var slide in slideList)
{
//ReplaceImages(presentationDocument, slide); //replace images by name
var paragraphs = slide.Slide.Descendants <Paragraph>().ToList(); //get all paragraphs in the slide
foreach (var paragraph in paragraphs)
{
ReplaceText(paragraph); //replace text by placeholder name
}
}
var slideCount = presentation.SlideIdList.ToList().Count; //count slides
//DeleteSlide(presentation, slideList[slideCount - 1]); //delete last slide
presentation.Save(); //save document changes we've made
}
if (saveFile)
{
// Seek to beginning before writing to the SharePoint server.
memoryStream.Seek(0, SystemIO.SeekOrigin.Begin);
FileCreationInformation fci = new FileCreationInformation();
fci.ContentStream = memoryStream;
fci.Overwrite = true;
fci.Url = "https://rapidcircle1com.sharepoint.com" + (string)item["FileRef"];
File uploadFile = list.RootFolder.Files.Add(fci);
clientContext.ExecuteQuery();
}
}
}
/// <summary>
/// Sets the tran properties.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="web">The web.</param>
/// <param name="listItem">The list item.</param>
/// <param name="tranItem">The tran item.</param>
/// <param name="properties">The properties.</param>
/// <param name="getSubItems">if set to <c>true</c> [get sub items].</param>
/// <returns>
/// Transaction item
/// </returns>
private ITrans SetTranProperties(ClientContext context, Web web, ListItem listItem, ITrans tranItem, PropertyInfo[] properties, bool getSubItems = true)
{
foreach (PropertyInfo property in properties)
{
bool isListColumn = property.GetCustomAttribute <IsListColumnAttribute>() == null || property.GetCustomAttribute <IsListColumnAttribute>().IsListColumn;
bool isTask = property.GetCustomAttribute <IsTaskAttribute>() != null && property.GetCustomAttribute <IsTaskAttribute>().IsTaskField;
bool isTran = property.GetCustomAttribute <IsTranAttribute>() != null && property.GetCustomAttribute <IsTranAttribute>().IsTranField;
string listCoumnName = property.GetCustomAttribute <FieldColumnNameAttribute>() != null && !string.IsNullOrEmpty(property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation) ? property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation : property.Name;
bool isFile = property.GetCustomAttribute <IsFileAttribute>() != null && property.GetCustomAttribute <IsFileAttribute>().IsFile;
if (isListColumn)
{
if (property.GetCustomAttribute <FieldColumnNameAttribute>() != null && property.GetCustomAttribute <FieldColumnNameAttribute>().IsLookup)
{
FieldLookupValue lookupField = listItem[listCoumnName] as FieldLookupValue;
property.SetValue(tranItem, lookupField.LookupId);
}
else if (property.GetCustomAttribute <IsPersonAttribute>() != null && property.GetCustomAttribute <IsPersonAttribute>().IsPerson)
{
FieldUserValue[] users = null;
if (listItem[listCoumnName] != null)
{
if (property.GetCustomAttribute <IsPersonAttribute>().IsMultiple)
{
users = listItem[listCoumnName] as FieldUserValue[];
}
else
{
users = new FieldUserValue[1];
users[0] = listItem[listCoumnName] as FieldUserValue;
}
}
if (users != null && property.GetCustomAttribute <IsPersonAttribute>().ReturnID)
{
string personEmails = GetUserIDsFromPersonField(context, web, users);
property.SetValue(tranItem, personEmails);
}
else if (users != null && property.GetCustomAttribute <IsPersonAttribute>().ReturnName)
{
string personEmails = GetPersonValueFromPersonField(context, web, users, Person.Name);
property.SetValue(tranItem, personEmails);
}
else if (users != null && property.GetCustomAttribute <IsPersonAttribute>().ReturnAlias)
{
string personEmails = GetPersonValueFromPersonField(context, web, users, Person.Alias);
property.SetValue(tranItem, personEmails);
}
}
else if (isFile)
{
if (Convert.ToString(listItem["Attachments"]) == "True")
{
context.Load(listItem.AttachmentFiles);
context.ExecuteQuery();
AttachmentCollection attachments = listItem.AttachmentFiles;
List <FileDetails> objAttachmentFiles = GetAttachments(attachments);
property.SetValue(tranItem, objAttachmentFiles);
}
}
else
{
object value = listItem[listCoumnName];
Type t = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
object safeValue = (value == null) ? null : Convert.ChangeType(value, t);
if (t.Name.ToUpper().Trim() == Constants.DateTimeString.ToUpper().Trim() && safeValue != null)
{
var timeInfo = TimeZoneInfo.FindSystemTimeZoneById(Constants.TimeZoneName);
safeValue = TimeZoneInfo.ConvertTimeFromUtc((DateTime)safeValue, timeInfo);
}
property.SetValue(tranItem, safeValue);
}
}
else if (isTran)
{
if (getSubItems)
{
string listName = property.GetCustomAttribute <IsTranAttribute>().TranListName;
Type tSubTran = property.GetCustomAttribute <IsTranAttribute>().TranType;
if (!string.IsNullOrEmpty(listName))
{
List <ITrans> subTrans = this.GetTransactionListData(context, web, tSubTran, listName, Convert.ToInt32(listItem["ID"]));
property.SetValue(tranItem, subTrans);
}
}
}
////else if (isTask)
////{
//// string listName = property.GetCustomAttribute<IsTaskAttribute>().TaskListName;
//// Type tSubTask = property.GetCustomAttribute<IsTaskAttribute>().TaskType;
//// if (!string.IsNullOrEmpty(listName))
//// {
//// List<ITask> subTasks = this.GetTaskListData(context, web, tSubTask, listName, Convert.ToInt32(listItem["ID"]));
//// property.SetValue(taskItem, subTasks);
//// }
////}
}
return(tranItem);
}
public static void AddDocumentLibItems(ClientContext clientContext, string listTitle, XmlDocument sampleData, string BaseFolderPath)
{
XmlNode items = sampleData.SelectSingleNode("//List[@name='" + listTitle + "']");
List list = clientContext.Web.Lists.GetByTitle(listTitle);
//remove list items
var d_items = list.GetItems(new CamlQuery());
clientContext.Load(d_items);
clientContext.ExecuteQuery();
var count = d_items.Count;
if (count > 0)
{
while (count-- > 0)
{
d_items[0].DeleteObject();
}
clientContext.ExecuteQuery();
}
foreach (XmlNode item in items)
{
var filePath = BaseFolderPath + item.Attributes["path"].Value;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
try
{
byte[] bytes = new byte[fs.Length];
fs.Read(bytes, 0, bytes.Length);
var newfile = new FileCreationInformation();
newfile.Url = System.DateTime.Now.Ticks.ToString() + ".jpg";
newfile.Overwrite = true;
newfile.Content = bytes;
Microsoft.SharePoint.Client.File uploadedFile = list.RootFolder.Files.Add(newfile);
foreach (XmlNode column in item.ChildNodes)
{
if (column.Attributes["name"].Value == "Id")
{
continue;
}
if (column.Attributes["Type"] != null && column.Attributes["Type"].Value == "Lookup" && !string.IsNullOrEmpty(column.InnerText))
{
FieldLookupValue fieldLookupValue = new FieldLookupValue();
fieldLookupValue.LookupId = int.Parse(sampleData.SelectSingleNode(
string.Format("//List[@name='{0}']/item[{1}]/column[@name='Id']",
column.Attributes["Source"].Value,
column.InnerText)).InnerText);
uploadedFile.ListItemAllFields[column.Attributes["name"].Value] = fieldLookupValue;
}
else
{
uploadedFile.ListItemAllFields[column.Attributes["name"].Value] = column.InnerText;
}
}
uploadedFile.ListItemAllFields.Update();
clientContext.Load(uploadedFile);
clientContext.ExecuteQuery();
}
catch (Exception el)
{
el.ToString();
}
}
}
}
static void Main(string[] args)
{
UserRole userRole = new UserRole();
Providers providers = new Providers();
TypesOfInvestment typesOfInvestment = new TypesOfInvestment();
using (ClientContext cc = new ClientContext("http://192.168.88.69:3333"))
{
cc.Credentials = new NetworkCredential("auchan_admin", "P@ssWord*1!", "Qpd");
Web web = cc.Web;
using (NpgsqlConnection connection = new NpgsqlConnection(Constants.connectionString))
using (NpgsqlCommand cmd = new NpgsqlCommand(Constants.DeleteCmd, connection))
{
connection.Open();
//Удаление из таблиц данных командой DeleteCmd
cmd.ExecuteNonQuery();
//Получение таблицы Поставщики
List list = web.Lists.GetByTitle("Поставщики");
CamlQuery caml = new CamlQuery();
//Получение полей таблицы Поставщики
ListItemCollection items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
//Заполнение таблицы поставщиков
providers.FillProvider(items);
//Получение таблицы Типы инвестиций
list = web.Lists.GetByTitle("Типы инвестиций");
//Получение полей таблицы Поставщики
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
//Заполнение таблицы Типы инвестиций
typesOfInvestment.FillTypeOfInvestment(items);
//Получение таблицы Группы категорий
list = web.Lists.GetByTitle("Группы категорий");
//Получение данных таблицы Группы категорий
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
cmd.CommandText = Constants.InsertGroupsOfCategories;
Console.WriteLine("Заполнение данных Группы категорий...");
//Заполнение таблицы Группы категорий
foreach (ListItem item in items)
{
cmd.Parameters.Add("@Title", NpgsqlDbType.Text).Value = item["Title"].ToString();
cmd.Parameters.Add("@Code", NpgsqlDbType.Text).Value = item["Code"].ToString();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
//Получение таблицы Категории оборудования
list = web.Lists.GetByTitle("Категории оборудования");
//Получение столбцов таблицы Категории оборудования
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
cmd.CommandText = Constants.InsertCategoriesOfEquipment;
Console.WriteLine("Заполнение данных Категории оборудования...");
foreach (ListItem item in items)
{
FieldLookupValue field = item.FieldValues["CategoryGroupsLookup"] as FieldLookupValue;
cc.Load(item);
cc.ExecuteQuery();
cmd.Parameters.Add("@Title", NpgsqlDbType.Text).Value = item["Title"].ToString();
cmd.Parameters.Add("@Code", NpgsqlDbType.Text).Value = item["Code"].ToString();
cmd.Parameters.Add("@DepreciationPeriod", NpgsqlDbType.Integer).Value = Convert.ToDouble(item["Amortization"]);
cmd.Parameters.Add("@NameCategory", NpgsqlDbType.Text).Value = field.LookupValue;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
//заполнение Ролей
cmd.CommandText = Constants.InsertRole;
cmd.ExecuteNonQuery();
//заполнение пользователей
Console.WriteLine("Заполнение данных Пользователи...");
list = web.Lists.GetByTitle("Пользователи");
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
List userInformationList = web.SiteUserInfoList;
string login = string.Empty;
string jobPosition = string.Empty;
string department = string.Empty;
Array arrayRoles;
foreach (var item in items)
{
try
{
cmd.CommandText = Constants.InsertProfiles;
FieldUserValue userValue = (FieldUserValue)item["DAIUser"];
ListItem itm = userInformationList.GetItemById(userValue.LookupId);
cc.Load(itm);
cc.ExecuteQuery();
login = itm["UserName"].ToString();
if (login == null)
{
login = itm["Name"].ToString();
}
jobPosition = item["DAIJobTitle"].ToString();
if (jobPosition == null)
{
jobPosition = "empty";
}
department = item["DAIDepartment"].ToString();
if (department == null)
{
department = "empty";
}
cmd.Parameters.Add("@Login", NpgsqlDbType.Text);
cmd.Parameters.Add("@Password", NpgsqlDbType.Text);
cmd.Parameters.Add("@FIO", NpgsqlDbType.Text);
cmd.Parameters.Add("@DeletionMark", NpgsqlDbType.Boolean);
cmd.Parameters.Add("@jobPosition", NpgsqlDbType.Text);
cmd.Parameters.Add("@Department", NpgsqlDbType.Text);
cmd.Parameters["@Login"].Value = login;
cmd.Parameters["@Password"].Value = "AQAAAAEAACcQAAAAEMxLaU+KB/2Daq1cjWcUEVM+mk01XyoaAO46VoDocwFobNVaCEjCzOxATCa478fQUg==";
cmd.Parameters["@FIO"].Value = userValue.LookupValue;
cmd.Parameters["@DeletionMark"].Value = Convert.ToBoolean(itm["Attachments"]);
cmd.Parameters["@jobPosition"].Value = jobPosition;
cmd.Parameters["@Department"].Value = department;
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
//Заполнение UserRoles
arrayRoles = (Array)item["DAIRoles"];
userRole.FillUserRole(login, arrayRoles);
cmd.Parameters.Clear();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
cmd.Parameters.Clear();
}
}
//получение таблицы Центры результата
list = web.Lists.GetByTitle("Центры результата");
//получение полей таблицы
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
Console.WriteLine("Заполнение данных Центры результата...");
cmd.CommandText = Constants.InsertResultCentres;
//Заполнение таблицы Центры результата
foreach (var item in items)
{
try
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@Title", NpgsqlDbType.Text).Value = item["Title"];
cmd.Parameters.Add("@Code", NpgsqlDbType.Text).Value = item["Code"];
cmd.Prepare();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
//Получение таблицы Формат ситов
list = web.Lists.GetByTitle("Формат ситов");
//Получение полей таблицы Формат ситов
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
string DirOfFormatLogin = string.Empty;
string DirOfKYFormatLogin = string.Empty;
string KYFormatLogin = string.Empty;
Console.WriteLine("Заполнение данных Формат ситов...");
cmd.CommandText = Constants.InsertFormats;
FieldUserValue userValue1;
FieldUserValue userValue2;
FieldUserValue userValue3;
foreach (var item in items)
{
try
{
cmd.Parameters.Clear();
userValue1 = (FieldUserValue)item["SiteDirOfFormat"];
userValue2 = (FieldUserValue)item["SiteDirOfKYFormat"];
userValue3 = (FieldUserValue)item["SiteKYFormat"];
ListItem DirOfFormat = userInformationList.GetItemById(userValue1.LookupId);
ListItem DirOfKYFormat = userInformationList.GetItemById(userValue2.LookupId);
ListItem KYFormat = userInformationList.GetItemById(userValue3.LookupId);
cc.Load(DirOfFormat);
cc.Load(DirOfKYFormat);
cc.Load(KYFormat);
cc.ExecuteQuery();
AddUser user = new AddUser();
user.FillUsers(DirOfFormat, "Директор формата", "Администратор");
user.FillUsers(DirOfKYFormat, "Директор КУ формата", "Администратор");
user.FillUsers(KYFormat, "КУ формата", "Администратор");
DirOfFormatLogin = DirOfFormat["UserName"].ToString();
DirOfKYFormatLogin = DirOfKYFormat["UserName"].ToString();
KYFormatLogin = KYFormat["UserName"].ToString();
if (DirOfFormatLogin == null)
{
DirOfFormatLogin = DirOfFormat["Name"].ToString();
}
if (DirOfKYFormatLogin == null)
{
DirOfKYFormatLogin = DirOfKYFormat["Name"].ToString();
}
if (KYFormatLogin == null)
{
KYFormatLogin = KYFormat["Name"].ToString();
}
cmd.Parameters.Add("@Title", NpgsqlDbType.Text).Value = item["Title"].ToString();
cmd.Parameters.Add("@E1", NpgsqlDbType.Integer).Value = Convert.ToDouble(item["E1Limit"]);
cmd.Parameters.Add("@E2", NpgsqlDbType.Integer).Value = Convert.ToDouble(item["E2Limit"]);
cmd.Parameters.Add("@E3", NpgsqlDbType.Integer).Value = Convert.ToDouble(item["E3Limit"]);
cmd.Parameters.Add("@TypeOfFormat", NpgsqlDbType.Text).Value = item["KindOfFormat"].ToString();
cmd.Parameters.Add("@DirOfFormatLogin", NpgsqlDbType.Text).Value = DirOfFormatLogin;
cmd.Parameters.Add("@DirOfKYFormatLogin", NpgsqlDbType.Text).Value = DirOfKYFormatLogin;
cmd.Parameters.Add("@KYFormatLogin", NpgsqlDbType.Text).Value = KYFormatLogin;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
list = web.Lists.GetByTitle("Ситы");
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
cmd.CommandText = Constants.InsertSites;
Console.WriteLine("Заполнение данных Ситы...");
string SiteKYLogin;
string SiteTechExpLogin;
string SiteDirectorLogin;
string SiteCreaterOfBudgetLogin;
string SiteKYRegionalLogin;
string SiteOperatingDirLogin;
FieldUserValue userValue4;
FieldUserValue userValue5;
FieldUserValue userValue6;
FieldLookupValue FormatLookup;
ListItem SiteDirector;
ListItem SiteKY;
ListItem SiteTechExp;
ListItem SiteKYRegional;
ListItem SiteOperatingDir;
ListItem SiteCreaterOfBudget;
foreach (var item in items)
{
try
{
cmd.Parameters.Clear();
Array KYRegional = (Array)item["SiteKYRegional"];
cmd.Parameters.Clear();
userValue1 = (FieldUserValue)item["SiteDirector"];
userValue2 = (FieldUserValue)item["SiteKY"];
userValue3 = (FieldUserValue)item["SiteTechExp"];
userValue4 = (FieldUserValue)KYRegional.GetValue(0);
userValue5 = (FieldUserValue)item["SiteOperatingDir"];
userValue6 = (FieldUserValue)item["SiteCreaterOfBudget"];
FormatLookup = (FieldLookupValue)item["FormatLookup"];
SiteDirector = userInformationList.GetItemById(userValue1.LookupId);
SiteKY = userInformationList.GetItemById(userValue2.LookupId);
SiteTechExp = userInformationList.GetItemById(userValue3.LookupId);
SiteKYRegional = userInformationList.GetItemById(userValue4.LookupId);
SiteOperatingDir = userInformationList.GetItemById(userValue5.LookupId);
SiteCreaterOfBudget = userInformationList.GetItemById(userValue6.LookupId);
cc.Load(SiteDirector);
cc.Load(SiteKY);
cc.Load(SiteTechExp);
cc.Load(SiteKYRegional);
cc.Load(SiteOperatingDir);
cc.Load(SiteCreaterOfBudget);
cc.ExecuteQuery();
AddUser user = new AddUser();
user.FillUsers(SiteDirector, "Директор Сита", "Администратор");
user.FillUsers(SiteKY, "КУ Сита", "Администратор");
user.FillUsers(SiteTechExp, "Технический эксперт", "Администратор");
user.FillUsers(SiteKYRegional, "КУ региональный", "Администратор");
user.FillUsers(SiteOperatingDir, "Операционный директор", "Администратор");
user.FillUsers(SiteCreaterOfBudget, "Creater of Budget", "Администратор");
SiteKYLogin = SiteKY["UserName"].ToString();
SiteTechExpLogin = SiteTechExp["UserName"].ToString();
SiteDirectorLogin = SiteDirector["UserName"].ToString();
SiteCreaterOfBudgetLogin = SiteCreaterOfBudget["UserName"].ToString();
SiteKYRegionalLogin = SiteKYRegional["UserName"].ToString();
SiteOperatingDirLogin = SiteOperatingDir["UserName"].ToString();
if (SiteKYLogin == null)
{
SiteKYLogin = SiteKY["Name"].ToString();
}
if (SiteTechExpLogin == null)
{
SiteTechExpLogin = SiteTechExp["Name"].ToString();
}
if (SiteDirectorLogin == null)
{
SiteDirectorLogin = SiteDirector["Name"].ToString();
}
if (SiteCreaterOfBudgetLogin == null)
{
SiteCreaterOfBudgetLogin = SiteCreaterOfBudget["Name"].ToString();
}
if (SiteKYRegionalLogin == null)
{
SiteKYRegionalLogin = SiteKYRegional["Name"].ToString();
}
if (SiteOperatingDirLogin == null)
{
SiteOperatingDirLogin = SiteOperatingDir["Name"].ToString();
}
if (FormatLookup.LookupValue == null || item["Title"].ToString() == null || SiteKYLogin == null || SiteTechExpLogin == null || SiteDirectorLogin == null || SiteCreaterOfBudgetLogin == null || SiteKYRegionalLogin == null || SiteOperatingDirLogin == null)
{
Console.WriteLine("Значение - null");
}
cmd.Parameters.Add("@Title", NpgsqlDbType.Text).Value = item["Title"].ToString();
cmd.Parameters.Add("@FormatName", NpgsqlDbType.Text).Value = FormatLookup.LookupValue.ToString();
cmd.Parameters.Add("@KYSiteLogin", NpgsqlDbType.Text).Value = SiteKYLogin;
cmd.Parameters.Add("@TechnicalExpertLogin", NpgsqlDbType.Text).Value = SiteTechExpLogin;
cmd.Parameters.Add("@DirectorOfSiteLogin", NpgsqlDbType.Text).Value = SiteDirectorLogin;
cmd.Parameters.Add("@CreaterOfBudgetLogin", NpgsqlDbType.Text).Value = SiteCreaterOfBudgetLogin;
cmd.Parameters.Add("@KYRegionLogin", NpgsqlDbType.Text).Value = SiteKYRegionalLogin;
cmd.Parameters.Add("@OperationDirectorLogin", NpgsqlDbType.Text).Value = SiteOperatingDirLogin;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
cmd.Parameters.Clear();
}
}
list = web.Lists.GetByTitle("Бюджетные планы");
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
Console.WriteLine("Заполнение данных Бюджетные планы...");
cmd.CommandText = Constants.InsertBudgetPlan;
foreach (var item in items)
{
try
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@Year", NpgsqlDbType.Integer).Value = Convert.ToInt32(item["BudgetYear"]);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
list = web.Lists.GetByTitle("Строки бюджетного плана");
items = list.GetItems(caml);
cc.Load(items);
cc.ExecuteQuery();
double Price = 1;
double Amount;
int Count = 1;
string Month;
int MonthId = 0;
FieldLookupValue BudgetLookup;
FieldLookupValue CategoryOfEquipment;
FieldLookupValue ResultCenter;
FieldLookupValue Site;
FieldLookupValue TypeOfInvestment;
Console.WriteLine("Заполнение данных Строки бюджетного плана...");
cmd.CommandText = Constants.InsertBudgetLines;
foreach (var item in items)
{
try
{
Month = item["PlannedInvestmentDate"].ToString();
Price = Convert.ToInt32(item["Price"]);
Count = Convert.ToInt32(item["Quantity"]);
Amount = Price * Count;
switch (Month)
{
case "Январь":
MonthId = 1;
break;
case "Февраль":
MonthId = 2;
break;
case "Март":
MonthId = 3;
break;
case "Апрель":
MonthId = 4;
break;
case "Май":
MonthId = 5;
break;
case "Июнь":
MonthId = 6;
break;
case "Июль":
MonthId = 7;
break;
case "Август":
MonthId = 8;
break;
case "Сентябрь":
MonthId = 9;
break;
case "Октябрь":
MonthId = 10;
break;
case "Ноябрь":
MonthId = 11;
break;
case "Декабрь":
MonthId = 12;
break;
default: break;
}
BudgetLookup = (FieldLookupValue)item["BudgetLookup"];
CategoryOfEquipment = (FieldLookupValue)item["CategoryOfEquipmentLookup"];
ResultCenter = (FieldLookupValue)item["ResultCenterLookup"];
Site = (FieldLookupValue)item["SiteLookup"];
TypeOfInvestment = (FieldLookupValue)item["TypeOfInvestmentLookup"];
cmd.Parameters.Clear();
cmd.Parameters.Add("@SubjectOfInvestment", NpgsqlDbType.Text).Value = item["InvestmentSubject"].ToString();
cmd.Parameters.Add("@DateOfDelivery", NpgsqlDbType.Integer).Value = MonthId;
cmd.Parameters.Add("@Count", NpgsqlDbType.Integer).Value = Count;
cmd.Parameters.Add("@Price", NpgsqlDbType.Double).Value = Price;
cmd.Parameters.Add("@Amount", NpgsqlDbType.Double).Value = Amount;
cmd.Parameters.Add("@BudgetPlanYear", NpgsqlDbType.Integer).Value = Convert.ToInt32(BudgetLookup.LookupValue);
cmd.Parameters.Add("@CategoryOfEquipmentTitle", NpgsqlDbType.Text).Value = CategoryOfEquipment.LookupValue;
cmd.Parameters.Add("@SiteTitle", NpgsqlDbType.Text).Value = Site.LookupValue;
cmd.Parameters.Add("@TypeOfInvestmentTitle", NpgsqlDbType.Text).Value = TypeOfInvestment.LookupValue;
if (ResultCenter is null)
{
cmd.Parameters.Add("@ResultCenterTitle", NpgsqlDbType.Text).Value = "empty";
}
else
{
cmd.Parameters.Add("@ResultCenterTitle", NpgsqlDbType.Text).Value = ResultCenter.LookupValue;
}
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
//Заполнение MenuItemRoles
MenuItemRoles menuItemRoles = new MenuItemRoles();
menuItemRoles.FillMenuItemRoles();
//Заполнение RolesObjectTypes
RolesObjectTypes rolesObjectTypes = new RolesObjectTypes();
rolesObjectTypes.FillRolesObjectTypes();
//Заполнение ObjectPermission
cmd.CommandText = Constants.InsertObjectPermissions;
cmd.ExecuteNonQuery();
connection.Close();
Console.WriteLine("Заполнение окончено");
Console.ReadLine();
}
}
}
private IMaster AssignPropertyValues(dynamic objMaster, ClientContext context, Web web, ListItemCollection items, Type itemType)
{
List <IMasterItem> masterItems = new List <IMasterItem>();
foreach (ListItem item in items)
{
dynamic listItem = Activator.CreateInstance(itemType);
PropertyInfo[] itemProperties = listItem.GetType().GetProperties();
foreach (PropertyInfo property in itemProperties)
{
if (property.GetCustomAttribute <FieldColumnNameAttribute>() != null && !string.IsNullOrEmpty(property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation))
{
if (property.GetCustomAttribute <IsPersonAttribute>() != null && property.GetCustomAttribute <IsPersonAttribute>().IsPerson&& !property.GetCustomAttribute <IsPersonAttribute>().ReturnName)
{
if (item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation] is FieldUserValue[])
{
property.SetValue(listItem, BELDataAccessLayer.GetEmailsFromPersonField(context, web, (FieldUserValue[])item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation]));
}
else
{
property.SetValue(listItem, BELDataAccessLayer.GetEmailsFromPersonField(context, web, (FieldUserValue)item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation]));
}
}
else if (property.GetCustomAttribute <IsPersonAttribute>() != null && property.GetCustomAttribute <IsPersonAttribute>().IsPerson&& property.GetCustomAttribute <IsPersonAttribute>().ReturnName)
{
if (item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation] is FieldUserValue[])
{
property.SetValue(listItem, BELDataAccessLayer.GetNameFromPersonField(context, web, (FieldUserValue[])item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation]));
}
else
{
property.SetValue(listItem, BELDataAccessLayer.GetNameFromPersonField(context, web, (FieldUserValue)item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation]));
}
}
else if (property.GetCustomAttribute <FieldColumnNameAttribute>().IsLookup)
{
if (item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation] is FieldLookupValue[])
{
FieldLookupValue[] lookupFields = item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation] as FieldLookupValue[];
List <string> reletedto = new List <string>();
foreach (FieldLookupValue lookupField in lookupFields)
{
reletedto.Add(lookupField.LookupValue);
}
property.SetValue(listItem, reletedto);
}
else
{
FieldLookupValue lookupField = item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation] as FieldLookupValue;
if (lookupField != null)
{
property.SetValue(listItem, lookupField.LookupValue);
}
}
}
else
{
property.SetValue(listItem, item[property.GetCustomAttribute <FieldColumnNameAttribute>().FieldsInformation]);
}
}
}
masterItems.Add(listItem);
}
PropertyInfo prop = objMaster.GetType().GetProperty("Items");
prop.SetValue(objMaster, masterItems, null);
return(objMaster);
}
/// <summary>
/// Updates the list with matched people from the Microsoft Face API
/// </summary>
/// <param name="Photos"></param>
public void updateTaggedPhotosWithMatchedPeople(List<Photo> Photos)
{
using (ClientContext context = Login(SharePointURL))
{
try
{
foreach (Photo photo in Photos)
{
SP.List list = context.Web.GetList(PhotosToTagURL);
ListItem item = list.GetItemById(photo.ID);
item[PhotoNumberOfFacesColumn] = photo.NumberOfMatchedFaces;
item[PhotoNumberOfUnMachedFacesColumn] = photo.NumberOfUnmatchedFaces;
FieldLookupValue[] matchedPeople = new FieldLookupValue[photo.PeopleInPhoto.Count];
for (int i=0; i< photo.PeopleInPhoto.Count; i++)
{
FieldLookupValue value = new FieldLookupValue();
value.LookupId = photo.PeopleInPhoto[i].ID;
matchedPeople[i] = value;
}
item[PhotoMatchedPeopleColumn] = matchedPeople;
item.Update();
context.ExecuteQuery();
}
}
catch (Exception e)
{
throw;
}
}
}
void ProvisionSample7(Web web)
{
//Delete list if it already exists
ListCollection lists = web.Lists;
web.Context.Load(web.CurrentUser, i => i.Id);
IEnumerable<List> results = web.Context.LoadQuery<List>(lists.Where(list => list.Title == "CSR-Read-only-SP-Controls"));
web.Context.ExecuteQuery();
List existingList = results.FirstOrDefault();
if (existingList != null)
{
existingList.DeleteObject();
web.Context.ExecuteQuery();
}
//Create list
ListCreationInformation creationInfo = new ListCreationInformation();
creationInfo.Title = "CSR-Read-only-SP-Controls";
creationInfo.TemplateType = (int)ListTemplateType.Tasks;
List newlist = web.Lists.Add(creationInfo);
newlist.Update();
web.Context.Load(newlist);
web.Context.ExecuteQuery();
//Add items
Microsoft.SharePoint.Client.ListItem item1 = newlist.AddItem(new ListItemCreationInformation());
item1["Title"] = "Task 1";
item1["StartDate"] = "2014-1-1";
item1["DueDate"] = "2014-2-1";
item1["AssignedTo"] = new FieldLookupValue { LookupId = web.CurrentUser.Id };
item1.Update();
//Create sample view
ViewCreationInformation sampleViewCreateInfo = new ViewCreationInformation();
sampleViewCreateInfo.Title = "CSR Sample View";
sampleViewCreateInfo.ViewFields = new string[] { "DocIcon", "LinkTitle", "DueDate", "AssignedTo" };
sampleViewCreateInfo.SetAsDefaultView = true;
Microsoft.SharePoint.Client.View sampleView = newlist.Views.Add(sampleViewCreateInfo);
sampleView.Update();
web.Context.Load(newlist,
l => l.DefaultEditFormUrl);
web.Context.ExecuteQuery();
//Register JS files via JSLink properties
RegisterJStoWebPart(web, newlist.DefaultEditFormUrl, "~sitecollection/Style Library/JSLink-Samples/ReadOnlySPControls.js");
}
public override void ProvisionObjects(Web web, ProvisioningTemplate template, ProvisioningTemplateApplyingInformation applyingInformation)
{
Log.Info(Constants.LOGGING_SOURCE_FRAMEWORK_PROVISIONING, CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows);
if (template.Lists.Any())
{
var rootWeb = (web.Context as ClientContext).Site.RootWeb;
if (!web.IsPropertyAvailable("ServerRelativeUrl"))
{
web.Context.Load(web, w => w.ServerRelativeUrl);
web.Context.ExecuteQueryRetry();
}
web.Context.Load(web.Lists, lc => lc.IncludeWithDefaultProperties(l => l.RootFolder.ServerRelativeUrl));
web.Context.ExecuteQueryRetry();
var existingLists = web.Lists.AsEnumerable<List>().Select(existingList => existingList.RootFolder.ServerRelativeUrl).ToList();
var serverRelativeUrl = web.ServerRelativeUrl;
#region DataRows
foreach (var listInstance in template.Lists)
{
if (listInstance.DataRows != null && listInstance.DataRows.Any())
{
// Retrieve the target list
var list = web.Lists.GetByTitle(listInstance.Title);
web.Context.Load(list);
// Retrieve the fields' types from the list
FieldCollection fields = list.Fields;
web.Context.Load(fields, fs => fs.Include(f => f.InternalName, f => f.FieldTypeKind));
web.Context.ExecuteQueryRetry();
foreach (var dataRow in listInstance.DataRows)
{
var listitemCI = new ListItemCreationInformation();
var listitem = list.AddItem(listitemCI);
foreach (var dataValue in dataRow.Values)
{
Field dataField = fields.FirstOrDefault(
f => f.InternalName == dataValue.Key.ToParsedString());
if (dataField != null)
{
String fieldValue = dataValue.Value.ToParsedString();
switch (dataField.FieldTypeKind)
{
case FieldType.Geolocation:
// FieldGeolocationValue - Expected format: Altitude,Latitude,Longitude,Measure
var geolocationArray = fieldValue.Split(',');
if (geolocationArray.Length == 4)
{
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse(geolocationArray[0]),
Latitude = Double.Parse(geolocationArray[1]),
Longitude = Double.Parse(geolocationArray[2]),
Measure = Double.Parse(geolocationArray[3]),
};
listitem[dataValue.Key.ToParsedString()] = geolocationValue;
}
else
{
listitem[dataValue.Key.ToParsedString()] = fieldValue;
}
break;
case FieldType.Lookup:
// FieldLookupValue - Expected format: LookupID
var lookupValue = new FieldLookupValue
{
LookupId = Int32.Parse(fieldValue),
};
listitem[dataValue.Key.ToParsedString()] = lookupValue;
break;
case FieldType.URL:
// FieldUrlValue - Expected format: URL,Description
var urlArray = fieldValue.Split(',');
var linkValue = new FieldUrlValue();
if (urlArray.Length == 2)
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[1];
}
else
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[0];
}
listitem[dataValue.Key.ToParsedString()] = linkValue;
break;
case FieldType.User:
// FieldUserValue - Expected format: loginName
var user = web.EnsureUser(fieldValue);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
var userValue = new FieldUserValue
{
LookupId = user.Id,
};
listitem[dataValue.Key.ToParsedString()] = userValue;
}
else
{
listitem[dataValue.Key.ToParsedString()] = fieldValue;
}
break;
default:
listitem[dataValue.Key.ToParsedString()] = fieldValue;
break;
}
}
listitem.Update();
}
web.Context.ExecuteQueryRetry(); // TODO: Run in batches?
}
}
}
#endregion
}
}
public static void AddNewListItem(CsvRecord record, List spList, ClientContext clientContext)
{
Dictionary <string, object> itemFieldValues = new Dictionary <string, object>();
//Use reflection to iterate through the record's properties
PropertyInfo[] properties = typeof(CsvRecord).GetProperties();
foreach (PropertyInfo property in properties)
{
object propValue = property.GetValue(record, null);
if (!String.IsNullOrEmpty(propValue.ToString()))
{
Field matchingField = spList.Fields.GetByInternalNameOrTitle(property.Name);
clientContext.Load(matchingField);
clientContext.ExecuteQuery();
switch (matchingField.FieldTypeKind)
{
case FieldType.User:
FieldUserValue userFieldValue = GetUserFieldValue(propValue.ToString(), clientContext);
if (userFieldValue != null)
{
itemFieldValues.Add(matchingField.InternalName, userFieldValue);
}
else
{
throw new Exception("User field value could not be added: " + propValue.ToString());
}
break;
case FieldType.Lookup:
FieldLookupValue lookupFieldValue = GetLookupFieldValue(propValue.ToString(),
ConfigurationManager.AppSettings["LookupListName"].ToString(),
clientContext);
if (lookupFieldValue != null)
{
itemFieldValues.Add(matchingField.InternalName, lookupFieldValue);
}
else
{
throw new Exception("Lookup field value could not be added: " + propValue.ToString());
}
break;
case FieldType.Invalid:
switch (matchingField.TypeAsString)
{
default:
//Code for publishing site columns not implemented
continue;
}
default:
itemFieldValues.Add(matchingField.InternalName, propValue);
break;
}
}
}
//Add new item to list
ListItemCreationInformation creationInfo = new ListItemCreationInformation();
ListItem oListItem = spList.AddItem(creationInfo);
foreach (KeyValuePair <string, object> itemFieldValue in itemFieldValues)
{
//Set each field value
oListItem[itemFieldValue.Key] = itemFieldValue.Value;
}
//Persist changes
oListItem.Update();
clientContext.ExecuteQuery();
}
public void UpdateLookups(Func<Guid, ListItemsProvider> fnGetLookupDependentProvider, PnPMonitoredScope scope)
{
if (null != m_lookups)
{
bool valueUpdated = false;
foreach (KeyValuePair<string, LookupDataRef> pair in m_lookups)
{
LookupDataRef lookupData = pair.Value;
if (0 < lookupData.ItemLookupValues.Count)
{
Guid sourceListId = Guid.Empty;
try
{
sourceListId = new Guid(lookupData.Field.LookupList);
}
catch (Exception ex)
{
scope.LogError(ex, "Failed to get source list for lookup field. Field Name: {0}, Source List: {1}.",
lookupData.Field.InternalName, lookupData.Field.LookupList);
}
if (!Guid.Empty.Equals(sourceListId))
{
ListItemsProvider sourceProvider = fnGetLookupDependentProvider(sourceListId);
if ((null != sourceProvider) && (null != sourceProvider.m_mappingIDs))
{
foreach (KeyValuePair<ListItem, object> lookupPair in lookupData.ItemLookupValues)
{
ListItem item = lookupPair.Key;
object newItemValue = null;
object oldItemValue = lookupPair.Value;
FieldLookupValue oldLookupValue = oldItemValue as FieldLookupValue;
if (null != oldLookupValue)
{
int lookupId = oldLookupValue.LookupId;
int newId;
if (sourceProvider.m_mappingIDs.TryGetValue(lookupId, out newId) && (0 < newId))
{
newItemValue = new FieldLookupValue()
{
LookupId = newId
};
}
}
else
{
List<FieldLookupValue> newLookupValues = new List<FieldLookupValue>();
FieldLookupValue[] oldLookupValues = oldItemValue as FieldLookupValue[];
if ((null != oldLookupValues) && (0 < oldLookupValues.Length))
{
foreach (FieldLookupValue val in oldLookupValues)
{
int newId;
if (sourceProvider.m_mappingIDs.TryGetValue(val.LookupId, out newId) && (0 < newId))
{
newLookupValues.Add(new FieldLookupValue()
{
LookupId = newId
});
}
}
}
if (0 < newLookupValues.Count)
{
newItemValue = newLookupValues.ToArray();
}
}
if (null != newItemValue)
{
item[lookupData.Field.InternalName] = newItemValue;
item.Update();
valueUpdated = true;
}
}
}
}
}
}
if (valueUpdated)
{
try
{
this.Context.ExecuteQueryRetry();
}
catch (Exception ex)
{
string lookupFieldNames = string.Join(", ", m_lookups.Select(pair => pair.Value.Field.InternalName).ToArray());
scope.LogError(ex, "Failed to set lookup values. List: '{0}', Lookup Fields: {1}.", this.List.Title, lookupFieldNames);
}
}
}
}
public override TokenParser ProvisionObjects(Web web, ProvisioningTemplate template, TokenParser parser, ProvisioningTemplateApplyingInformation applyingInformation)
{
using (var scope = new PnPMonitoredScope(this.Name))
{
if (template.Lists.Any())
{
var rootWeb = (web.Context as ClientContext).Site.RootWeb;
web.EnsureProperties(w => w.ServerRelativeUrl);
web.Context.Load(web.Lists, lc => lc.IncludeWithDefaultProperties(l => l.RootFolder.ServerRelativeUrl));
web.Context.ExecuteQueryRetry();
var existingLists = web.Lists.AsEnumerable <List>().Select(existingList => existingList.RootFolder.ServerRelativeUrl).ToList();
var serverRelativeUrl = web.ServerRelativeUrl;
#region DataRows
foreach (var listInstance in template.Lists)
{
if (listInstance.DataRows != null && listInstance.DataRows.Any())
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Processing_data_rows_for__0_, listInstance.Title);
// Retrieve the target list
var list = web.Lists.GetByTitle(parser.ParseString(listInstance.Title));
web.Context.Load(list);
// Retrieve the fields' types from the list
Microsoft.SharePoint.Client.FieldCollection fields = list.Fields;
web.Context.Load(fields, fs => fs.Include(f => f.InternalName, f => f.FieldTypeKind, f => f.TypeAsString));
web.Context.ExecuteQueryRetry();
var keyColumnType = "Text";
var parsedKeyColumn = parser.ParseString(listInstance.DataRows.KeyColumn);
if (!string.IsNullOrEmpty(parsedKeyColumn))
{
var keyColumn = fields.FirstOrDefault(f => f.InternalName.Equals(parsedKeyColumn, StringComparison.InvariantCultureIgnoreCase));
if (keyColumn != null)
{
switch (keyColumn.FieldTypeKind)
{
case FieldType.User:
case FieldType.Lookup:
keyColumnType = "Lookup";
break;
case FieldType.URL:
keyColumnType = "Url";
break;
case FieldType.DateTime:
keyColumnType = "DateTime";
break;
case FieldType.Number:
case FieldType.Counter:
keyColumnType = "Number";
break;
}
}
}
foreach (var dataRow in listInstance.DataRows)
{
try
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_list_item__0_, listInstance.DataRows.IndexOf(dataRow) + 1);
bool create = true;
ListItem listitem = null;
if (!string.IsNullOrEmpty(listInstance.DataRows.KeyColumn))
{
// if it is empty, skip the check
if (!string.IsNullOrEmpty(dataRow.Key))
{
var query = $@"<View><Query><Where><Eq><FieldRef Name=""{parsedKeyColumn}""/><Value Type=""{keyColumnType}"">{parser.ParseString(dataRow.Key)}</Value></Eq></Where></Query><RowLimit>1</RowLimit></View>";
var camlQuery = new CamlQuery()
{
ViewXml = query
};
var existingItems = list.GetItems(camlQuery);
list.Context.Load(existingItems);
list.Context.ExecuteQueryRetry();
if (existingItems.Count > 0)
{
if (listInstance.DataRows.UpdateBehavior == UpdateBehavior.Skip)
{
create = false;
}
else
{
listitem = existingItems[0];
create = true;
}
}
}
}
if (create)
{
if (listitem == null)
{
var listitemCI = new ListItemCreationInformation();
listitem = list.AddItem(listitemCI);
}
foreach (var dataValue in dataRow.Values)
{
Field dataField = fields.FirstOrDefault(
f => f.InternalName == parser.ParseString(dataValue.Key));
if (dataField != null)
{
String fieldValue = parser.ParseString(dataValue.Value);
switch (dataField.FieldTypeKind)
{
case FieldType.Geolocation:
// FieldGeolocationValue - Expected format: Altitude,Latitude,Longitude,Measure
var geolocationArray = fieldValue.Split(',');
if (geolocationArray.Length == 4)
{
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse(geolocationArray[0]),
Latitude = Double.Parse(geolocationArray[1]),
Longitude = Double.Parse(geolocationArray[2]),
Measure = Double.Parse(geolocationArray[3]),
};
listitem[parser.ParseString(dataValue.Key)] = geolocationValue;
}
else
{
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
}
break;
case FieldType.Lookup:
// FieldLookupValue - Expected format: LookupID or LookupID,LookupID,LookupID...
if (fieldValue.Contains(","))
{
var lookupValues = new List <FieldLookupValue>();
fieldValue.Split(',').All(value =>
{
lookupValues.Add(new FieldLookupValue
{
LookupId = int.Parse(value),
});
return(true);
});
listitem[parser.ParseString(dataValue.Key)] = lookupValues.ToArray();
}
else
{
var lookupValue = new FieldLookupValue
{
LookupId = int.Parse(fieldValue),
};
listitem[parser.ParseString(dataValue.Key)] = lookupValue;
}
break;
case FieldType.URL:
// FieldUrlValue - Expected format: URL,Description
var urlArray = fieldValue.Split(',');
var linkValue = new FieldUrlValue();
if (urlArray.Length == 2)
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[1];
}
else
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[0];
}
listitem[parser.ParseString(dataValue.Key)] = linkValue;
break;
case FieldType.User:
// FieldUserValue - Expected format: loginName or loginName,loginName,loginName...
if (fieldValue.Contains(","))
{
var userValues = new List <FieldUserValue>();
fieldValue.Split(',').All(value =>
{
var user = web.EnsureUser(value);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
userValues.Add(new FieldUserValue
{
LookupId = user.Id,
});;
}
return(true);
});
listitem[parser.ParseString(dataValue.Key)] = userValues.ToArray();
}
else
{
var user = web.EnsureUser(fieldValue);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
var userValue = new FieldUserValue
{
LookupId = user.Id,
};
listitem[parser.ParseString(dataValue.Key)] = userValue;
}
else
{
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
}
}
break;
case FieldType.DateTime:
var dateTime = DateTime.MinValue;
if (DateTime.TryParse(fieldValue, out dateTime))
{
listitem[parser.ParseString(dataValue.Key)] = dateTime;
}
break;
default:
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
break;
}
}
}
listitem.Update();
web.Context.ExecuteQueryRetry(); // TODO: Run in batches?
if (dataRow.Security != null && (dataRow.Security.ClearSubscopes == true || dataRow.Security.CopyRoleAssignments == true || dataRow.Security.RoleAssignments.Count > 0))
{
listitem.SetSecurity(parser, dataRow.Security);
}
}
}
catch (Exception ex)
{
if (ex.GetType().Equals(typeof(ServerException)) &&
(ex as ServerException).ServerErrorTypeName.Equals("Microsoft.SharePoint.SPDuplicateValuesFoundException", StringComparison.InvariantCultureIgnoreCase) &&
applyingInformation.IgnoreDuplicateDataRowErrors)
{
scope.LogWarning(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_listitem_duplicate);
continue;
}
else
{
scope.LogError(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_listitem_failed___0_____1_, ex.Message, ex.StackTrace);
throw;
}
}
}
}
}
#endregion
}
}
return(parser);
}
public ActionResult ListaPedidos()
{
List <Pedidos> model = new List <Pedidos>();
var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
using (var clientContext = spContext.CreateUserClientContextForSPHost())
{
if (clientContext != null)
{
Web web = clientContext.Web;
clientContext.Load(web);
clientContext.ExecuteQuery();
ListCollection lists = web.Lists;
clientContext.Load <ListCollection>(lists);
clientContext.ExecuteQuery();
var pedidos = lists.GetByTitle("Pedidos");
clientContext.Load(pedidos);
var productos = lists.GetByTitle("Productos");
clientContext.Load(pedidos);
clientContext.Load(productos);
clientContext.ExecuteQuery();
CamlQuery pedidosQuery = new CamlQuery();
ListItemCollection pedidosItems = pedidos.GetItems(pedidosQuery);
clientContext.Load(pedidosItems);
clientContext.ExecuteQuery();
foreach (var pedidosItem in pedidosItems)
{
FieldLookupValue lookup = pedidosItem["Producto"] as FieldLookupValue;
int lId = lookup.LookupId;
int uds;
int.TryParse(pedidosItem["Unidades"].ToString(), out uds);
var pi = productos.GetItemById(lId);
clientContext.Load(pi);
clientContext.ExecuteQuery();
var precio = pi["Precio"];
var venta = (double)precio * (double)uds;
model.Add(new Pedidos()
{
Cliente = pedidosItem["Title"].ToString(),
Pedido = pi["Title"].ToString(),
Unidades = uds,
Total = venta
});
}
}
return(View(model));
}
}
public static void AddDocumentLibItems(ClientContext clientContext, string listTitle, XmlDocument sampleData, string BaseFolderPath)
{
XmlNode items = sampleData.SelectSingleNode("//List[@name='" + listTitle + "']");
List list = clientContext.Web.Lists.GetByTitle(listTitle);
//remove list items
var d_items = list.GetItems(new CamlQuery());
clientContext.Load(d_items);
clientContext.ExecuteQuery();
var count = d_items.Count;
if (count > 0)
{
while (count-- > 0)
{
d_items[0].DeleteObject();
}
clientContext.ExecuteQuery();
}
foreach (XmlNode item in items)
{
var filePath = BaseFolderPath + item.Attributes["path"].Value;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
try
{
byte[] bytes = new byte[fs.Length];
fs.Read(bytes, 0, bytes.Length);
var newfile = new FileCreationInformation();
newfile.Url = System.DateTime.Now.Ticks.ToString() + ".jpg";
newfile.Overwrite = true;
newfile.Content = bytes;
Microsoft.SharePoint.Client.File uploadedFile = list.RootFolder.Files.Add(newfile);
foreach (XmlNode column in item.ChildNodes)
{
if (column.Attributes["name"].Value == "Id") continue;
if (column.Attributes["Type"] != null && column.Attributes["Type"].Value == "Lookup" && !string.IsNullOrEmpty(column.InnerText))
{
FieldLookupValue fieldLookupValue = new FieldLookupValue();
fieldLookupValue.LookupId = int.Parse(sampleData.SelectSingleNode(
string.Format("//List[@name='{0}']/item[{1}]/column[@name='Id']",
column.Attributes["Source"].Value,
column.InnerText)).InnerText);
uploadedFile.ListItemAllFields[column.Attributes["name"].Value] = fieldLookupValue;
}
else
{
uploadedFile.ListItemAllFields[column.Attributes["name"].Value] = column.InnerText;
}
}
uploadedFile.ListItemAllFields.Update();
clientContext.Load(uploadedFile);
clientContext.ExecuteQuery();
}
catch (Exception el)
{
el.ToString();
}
}
}
}
public ActionResult TotalPedidos()
{
object model = null;
var spContext = SharePointContextProvider.Current.GetSharePointContext(HttpContext);
using (var clientContext = spContext.CreateUserClientContextForSPHost())
{
if (clientContext != null)
{
Web web = clientContext.Web;
clientContext.Load(web);
clientContext.ExecuteQuery();
ListCollection lists = web.Lists;
clientContext.Load <ListCollection>(lists);
clientContext.ExecuteQuery();
var pedidos = lists.GetByTitle("Pedidos");
clientContext.Load(pedidos);
var productos = lists.GetByTitle("Productos");
clientContext.Load(pedidos);
clientContext.Load(productos);
clientContext.ExecuteQuery();
CamlQuery pedidosQuery = new CamlQuery();
ListItemCollection pedidosItems = pedidos.GetItems(pedidosQuery);
clientContext.Load(pedidosItems);
clientContext.ExecuteQuery();
var total = 0.0;
var clientes = new Dictionary <string, double>();
foreach (var pedidosItem in pedidosItems)
{
FieldLookupValue lookup = pedidosItem["Producto"] as FieldLookupValue;
int lId = lookup.LookupId;
var uds = pedidosItem["Unidades"];
var pi = productos.GetItemById(lId);
clientContext.Load(pi);
clientContext.ExecuteQuery();
var precio = pi["Precio"];
var venta = (double)precio * (double)uds;
total += venta;
if (clientes.ContainsKey(pedidosItem["Title"].ToString()))
{
clientes[pedidosItem["Title"].ToString()] = clientes[pedidosItem["Title"].ToString()] +
venta;
}
else
{
clientes.Add(pedidosItem["Title"].ToString(), venta);
}
}
var mc = total / clientes.Keys.Count;
model = new Totales()
{
Numero = pedidosItems.Count, MediaCliente = mc, Total = total
};
}
}
return(View(model));
}
///// <summary>
///// Handles the ItemAdded event by modifying the Description
///// field of the item.
///// </summary>
///// <param name="properties"></param>
private void UpdateDefaultValuesAdding(SPRemoteEventProperties properties, SPRemoteEventResult result)
{
using (ClientContext clientContext = TokenHelper.CreateRemoteEventReceiverClientContext(properties))
{
if (clientContext != null)
{
var itemProperties = properties.ItemEventProperties;
var _userLoginName = properties.ItemEventProperties.UserLoginName;
var _afterProperites = itemProperties.AfterProperties;
string phase = "";
List list = clientContext.Web.Lists.GetById(properties.ItemEventProperties.ListId);
//ListItem item = list.GetItemById(properties.ItemEventProperties.ListItemId);
FieldCollection fields = list.Fields;
//clientContext.Load(item);
clientContext.Load(fields);
clientContext.ExecuteQuery();
string[] folderNameSplit = itemProperties.BeforeUrl.ToString().Split('/');
string folderName = folderNameSplit[folderNameSplit.Length - 2];
string code = folderName.Split('_').First();
string lookupFieldName = "Phase";
FieldLookup lookupField = clientContext.CastTo<FieldLookup>(fields.GetByInternalNameOrTitle(lookupFieldName));
clientContext.Load(lookupField);
clientContext.ExecuteQuery();
Guid parentWeb = lookupField.LookupWebId;
string parentListGUID = lookupField.LookupList;
Web lookupListWeb = clientContext.Site.OpenWebById(parentWeb);
List parentList = lookupListWeb.Lists.GetById(new Guid(parentListGUID));
CamlQuery cq = new CamlQuery();
cq.ViewXml = "<ViewFields><FieldRef Name='Title' /><FieldRef Name='ID' /></ViewFields>";
ListItemCollection litems = parentList.GetItems(cq);
clientContext.Load(parentList);
clientContext.Load(litems);
clientContext.ExecuteQuery();
FieldLookupValue flv = new FieldLookupValue();
foreach (ListItem li in litems)
{
string phaseItemCode = li["Title"].ToString().TrimEnd(')').Split('(').Last();
if (phaseItemCode == code)
{
if (itemProperties.AfterProperties.ContainsKey("Phase") && !String.IsNullOrEmpty(itemProperties.AfterProperties["Phase"].ToString()))
{
string[] stringSeparators = new string[] { ";#" };
if (itemProperties.AfterProperties["Phase"].ToString().Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries)[0] == li["ID"].ToString()) break;
}
phase = li.Id + ";#" + li["Title"];
break;
}
}
result.ChangedItemProperties.Add("Phase", phase);
result.Status = SPRemoteEventServiceStatus.Continue;
}
}
}
public string ProcessingField(ListItem Item, Field oField, string PropertyName)
{
string FieldValue = "";
var FieldValueObject = Item[oField.InternalName];
if (FieldValueObject == null)
{
Folder ValuedFolder = null;
this.ParentFolders.ForEach(ParentFolder =>
{
var ParentFolderValue = ParentFolder.ListItemAllFields[oField.InternalName];
if (!String.IsNullOrEmpty(ParentFolderValue.StringValueOrEmpty()))
{
if (ValuedFolder == null)
{
ValuedFolder = ParentFolder;
}
}
});
if (ValuedFolder != null)
{
FieldValue = ProcessingField(ValuedFolder.ListItemAllFields, oField, PropertyName);
}
}
else
{
switch (oField.FieldTypeKind)
{
case FieldType.Lookup:
FieldLookupValue[] LookupValues = new FieldLookupValue[] { };
if (FieldValueObject.GetType() != LookupValues.GetType())
{
LookupValues = new FieldLookupValue[] { FieldValueObject as FieldLookupValue };
}
foreach (FieldLookupValue LookupValue in LookupValues)
{
if (LookupValue != null)
{
if (PropertyName == "LookupId")
{
FieldValue += LookupValue.LookupId + "; ";
continue;
}
FieldValue += LookupValue.LookupValue + "; ";
}
}
if (!String.IsNullOrEmpty(FieldValue))
{
FieldValue = FieldValue.Substring(0, FieldValue.Length - 2);
}
break;
case FieldType.User:
FieldUserValue[] UserValues = new FieldUserValue[] { };
if (FieldValueObject.GetType() != UserValues.GetType())
{
UserValues = new FieldUserValue[] { FieldValueObject as FieldUserValue };
}
foreach (FieldUserValue UserValue in UserValues)
{
if (UserValue != null)
{
if (PropertyName == "LookupId")
{
FieldValue += UserValue.LookupId + "; ";
continue;
}
FieldValue += UserValue.LookupValue + "; ";
}
}
if (!String.IsNullOrEmpty(FieldValue))
{
FieldValue = FieldValue.Substring(0, FieldValue.Length - 2);
}
break;
case FieldType.Boolean:
bool BoolValue = Convert.ToBoolean(FieldValueObject);
if (BoolValue)
{
FieldValue = "Yes";
if (this.Item.ParentList.ParentWeb.Language == 1049)
{
FieldValue = "Да";
}
}
else
{
FieldValue = "No";
if (this.Item.ParentList.ParentWeb.Language == 1049)
{
FieldValue = "Нет";
}
}
break;
case FieldType.URL:
FieldUrlValue UrlValue = FieldValueObject as FieldUrlValue;
FieldValue = UrlValue.Url;
if (PropertyName == "Description")
{
FieldValue = UrlValue.Description;
}
break;
case FieldType.DateTime:
var DateTimeValue = FieldValueObject;
FieldValue = DateTimeValue.DateTimeValueOrEmpty(PropertyName); //format
break;
case FieldType.Note:
string ServerUrl = this.Item.ParentList.ParentWeb.Url.Replace(this.Item.ParentList.ParentWeb.ServerRelativeUrl, "");
FieldValue = FieldValueObject.StringValueOrEmpty();
FieldValue = FieldValue.Replace("src=\"/", "src=\"" + ServerUrl + "/");
FieldValue = FieldValue.Replace("href=\"/", "href=\"" + ServerUrl + "/");
break;
default:
FieldValue = FieldValueObject.StringValueOrEmpty();
break;
}
}
return(FieldValue);
}
public override TokenParser ProvisionObjects(Web web, ProvisioningTemplate template, TokenParser parser, ProvisioningTemplateApplyingInformation applyingInformation)
{
using (var scope = new PnPMonitoredScope(this.Name))
{
if (template.Lists.Any())
{
var rootWeb = (web.Context as ClientContext).Site.RootWeb;
web.EnsureProperties(w => w.ServerRelativeUrl);
web.Context.Load(web.Lists, lc => lc.IncludeWithDefaultProperties(l => l.RootFolder.ServerRelativeUrl));
web.Context.ExecuteQueryRetry();
var existingLists = web.Lists.AsEnumerable<List>().Select(existingList => existingList.RootFolder.ServerRelativeUrl).ToList();
var serverRelativeUrl = web.ServerRelativeUrl;
#region DataRows
foreach (var listInstance in template.Lists)
{
if (listInstance.DataRows != null && listInstance.DataRows.Any())
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Processing_data_rows_for__0_, listInstance.Title);
// Retrieve the target list
var list = web.Lists.GetByTitle(listInstance.Title);
web.Context.Load(list);
// Retrieve the fields' types from the list
FieldCollection fields = list.Fields;
web.Context.Load(fields, fs => fs.Include(f => f.InternalName, f => f.FieldTypeKind));
web.Context.ExecuteQueryRetry();
foreach (var dataRow in listInstance.DataRows)
{
try
{
scope.LogDebug(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_list_item__0_, listInstance.DataRows.IndexOf(dataRow) + 1);
var listitemCI = new ListItemCreationInformation();
var listitem = list.AddItem(listitemCI);
foreach (var dataValue in dataRow.Values)
{
Field dataField = fields.FirstOrDefault(
f => f.InternalName == parser.ParseString(dataValue.Key));
if (dataField != null)
{
String fieldValue = parser.ParseString(dataValue.Value);
switch (dataField.FieldTypeKind)
{
case FieldType.Geolocation:
// FieldGeolocationValue - Expected format: Altitude,Latitude,Longitude,Measure
var geolocationArray = fieldValue.Split(',');
if (geolocationArray.Length == 4)
{
var geolocationValue = new FieldGeolocationValue
{
Altitude = Double.Parse(geolocationArray[0]),
Latitude = Double.Parse(geolocationArray[1]),
Longitude = Double.Parse(geolocationArray[2]),
Measure = Double.Parse(geolocationArray[3]),
};
listitem[parser.ParseString(dataValue.Key)] = geolocationValue;
}
else
{
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
}
break;
case FieldType.Lookup:
// FieldLookupValue - Expected format: LookupID
var lookupValue = new FieldLookupValue
{
LookupId = Int32.Parse(fieldValue),
};
listitem[parser.ParseString(dataValue.Key)] = lookupValue;
break;
case FieldType.URL:
// FieldUrlValue - Expected format: URL,Description
var urlArray = fieldValue.Split(',');
var linkValue = new FieldUrlValue();
if (urlArray.Length == 2)
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[1];
}
else
{
linkValue.Url = urlArray[0];
linkValue.Description = urlArray[0];
}
listitem[parser.ParseString(dataValue.Key)] = linkValue;
break;
case FieldType.User:
// FieldUserValue - Expected format: loginName
var user = web.EnsureUser(fieldValue);
web.Context.Load(user);
web.Context.ExecuteQueryRetry();
if (user != null)
{
var userValue = new FieldUserValue
{
LookupId = user.Id,
};
listitem[parser.ParseString(dataValue.Key)] = userValue;
}
else
{
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
}
break;
default:
listitem[parser.ParseString(dataValue.Key)] = fieldValue;
break;
}
}
listitem.Update();
}
web.Context.ExecuteQueryRetry(); // TODO: Run in batches?
if (dataRow.Security != null)
{
listitem.SetSecurity(parser, dataRow.Security);
}
}
catch (Exception ex)
{
scope.LogError(CoreResources.Provisioning_ObjectHandlers_ListInstancesDataRows_Creating_listitem_failed___0_____1_, ex.Message, ex.StackTrace);
throw;
}
}
}
}
#endregion
}
}
return parser;
}
public async Task <string> createJobManagerAsync(string listIdName, tb_JobManager oJobManager, string LoginUserName)
{
string sMsg = string.Empty;
string DateFormat = string.Empty;
try
{
DateFormat = "MM/dd/yyyy HH:mm"; // "dd/MM/yyyy HH:mm"; // "yyyy/MM/dd"; // "yyyyMMdHHmmss";
List list = web.Lists.GetById(new Guid(listIdName));
// adds the new item to the list with ListItemCreationInformation
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem newListItem = list.AddItem(itemCreateInfo);
newListItem["GUID0"] = oJobManager.GUID;
newListItem["Nome"] = oJobManager.Nome;
newListItem["Descrizione"] = oJobManager.Descrizione;
newListItem["Data_x0020_Inizio_x0020_Job"] = oJobManager.DataInizioJob.ToString(DateFormat);
newListItem["Data_x0020_Fine_x0020_Job"] = oJobManager.DataFineJob.ToString(DateFormat);
newListItem["Tipo_x0020_Job"] = oJobManager.TipoJob;
newListItem["Ripetizione"] = oJobManager.Ripetizione;
newListItem["Stato_x0020_Job"] = oJobManager.StatoJob;
FieldLookupValue spvReferente1 = new FieldLookupValue();
spvReferente1.LookupId = oJobManager.Referente1;
newListItem["Referente1"] = spvReferente1;
newListItem.Update();
FieldLookupValue spvReferente2 = new FieldLookupValue();
spvReferente2.LookupId = oJobManager.Referente2;
newListItem["Referente2"] = spvReferente2;
newListItem.Update();
FieldLookupValue spvReferente3 = new FieldLookupValue();
spvReferente3.LookupId = oJobManager.Referente3;
newListItem["Referente3"] = spvReferente3;
newListItem.Update();
FieldLookupValue spvReferente4 = new FieldLookupValue();
spvReferente4.LookupId = oJobManager.Referente4;
newListItem["Referente4"] = spvReferente4;
newListItem.Update();
FieldLookupValue spvReferente5 = new FieldLookupValue();
spvReferente5.LookupId = oJobManager.Referente5;
newListItem["Referente5"] = spvReferente5;
newListItem.Update();
// VEDERE SE FUNZIONA
newListItem["Author"] = oJobManager.Autore;
newListItem["Editor"] = oJobManager.AutoreUltimaModifica;
//if (oJobManager.DataCreazione != null)
//{
// newListItem["Created_x0020_Date"] = oJobManager.DataCreazione.ToString(DateFormat);
//}
//if (oJobManager.DataUltimaModifica != null)
//{
// newListItem["Last_x0020_Modified"] = oJobManager.DataUltimaModifica.ToString(DateFormat);
//}
newListItem.Update();
// executes the creation of the new list item on SharePoint
await ctx.ExecuteQueryAsync();
}
catch (Exception ex)
{
sMsg = string.Format("Source: {0}{3} Message: {1}{3} StackTrace: {2}{3}", ex.Source, ex.Message, ex.StackTrace, System.Environment.NewLine);
SeriLogging.LogFatal(LoginUserName, sMsg);
}
return(sMsg);
}
