/// <summary>
/// Delete web dashboard page and helper file
/// </summary>
/// <param name="clientContext">SharePoint Client Context</param>
/// <param name="files">List of files to be deleted</param>
internal static void DeletePages(ClientContext clientContext, List <string> files)
{
List list = clientContext.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["folder"]);
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = Constants.QueryGetSpecificFiles;
ListItemCollection listCollection = list.GetItems(camlQuery);
clientContext.Load(listCollection, items => items.Include(
item => item.DisplayName,
item => item.File.Name,
item => item.Id));
clientContext.ExecuteQuery();
if (null != listCollection)
{
List <ListItem> allItems = listCollection.ToList <ListItem>();
foreach (var file in files)
{
foreach (var item in allItems)
{
if (item.File.Name.ToUpperInvariant().Equals(file.ToUpperInvariant()))
{
item.DeleteObject();
list.Update();
Console.WriteLine(Constants.DeleteFileMessage + file);
}
}
}
clientContext.ExecuteQuery();
}
}
private void DisplayAlerts()
{
var alerts = (from s in allProjectItems.ToList()
select new { Title = s.FieldValues["Title"], Alert = s.FieldValues["Alert"].ToString() }).ToList();
dgAlerts.ItemsSource = alerts;
}
public ActionResult CreateListitem(Boolean?isSuccess)
{
if (isSuccess.HasValue && isSuccess.Value)
{
ViewBag.Message = "Yes item created";
}
List <ListCollection> listcol = new List <ListCollection>();
using (ClientContext ctx = ContextHelper.GetContext())
{
List list = ctx.Web.GetListByTitle("TimsAddedList");
list.Update();
ctx.ExecuteQuery();
ListItemCollection item = list.GetItems(CamlQuery.CreateAllItemsQuery());
ctx.Load(item);
ctx.ExecuteQuery();
ViewBag.list = item.ToList();
return(View());
}
}
/// <summary>
/// Retrieve all listitems in a library
/// </summary>
/// <returns></returns>
public List <ListItem> GetAllDocumentsInaLibrary(string libName)
{
List <ListItem> items = new List <ListItem>();
ClientContext ctx = this.Context;
//ctx.Credentials = Your Credentials
ctx.Load(ctx.Web, a => a.Lists);
ctx.ExecuteQuery();
List list = ctx.Web.Lists.GetByTitle(libName);
ListItemCollectionPosition position = null;
// Page Size: 100
int rowLimit = 100;
var camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View Scope='RecursiveAll'>
<Query>
<OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy>
</Query>
<ViewFields>
<FieldRef Name='Title'/><FieldRef Name='Modified' /><FieldRef Name='Editor' /><FieldRef Name='FileLeafRef' /><FieldRef Name='FileRef' /><FieldRef Name='" + this.hashColumn + "' /></ViewFields><RowLimit Paged='TRUE'>" + rowLimit + "</RowLimit></View>";
do
{
ListItemCollection listItems = null;
camlQuery.ListItemCollectionPosition = position;
listItems = list.GetItems(camlQuery);
ctx.Load(listItems);
ctx.ExecuteQuery();
position = listItems.ListItemCollectionPosition;
items.AddRange(listItems.ToList());
}while (position != null);
return(items);
}
static void DeleteDisabledUsers()
{
SecureString passWord = new SecureString();
foreach (char c in ((System.Collections.Specialized.NameValueCollection)ConfigurationManager.GetSection("secureAppSettings"))["UserPwd"].ToCharArray())
{
passWord.AppendChar(c);
}
context.Credentials = new SharePointOnlineCredentials(ConfigurationManager.AppSettings["UserAccount"], passWord);
ADList = context.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["SharePointList"]);
CamlQuery query = new CamlQuery();
query.ViewXml = "<View><Query><Where><Lt><FieldRef Name='_DCDateModified'/><Value Type='DateTime'><Today /></Value></Lt></Where></Query></View>";
ListItemCollection items = ADList.GetItems(query);
context.Load(items);
context.ExecuteQuery();
foreach (ListItem item in items.ToList())
{
item.DeleteObject();
}
context.ExecuteQuery();
}
/// <summary>
/// Deletes all items of list.
/// </summary>
/// <param name="listName">Name of the list.</param>
public void DeleteAllItemsOfList(string listName)
{
try
{
List list = this.web.Lists.GetByTitle(listName);
if (list != null)
{
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View></View>";
ListItemCollection items = list.GetItems(camlQuery);
this.context.Load(items);
this.context.ExecuteQuery();
if (items != null && items.Count > 0)
{
foreach (ListItem item in items.ToList())
{
item.DeleteObject();
}
this.context.ExecuteQuery();
}
}
}
catch (Exception ex)
{
Logger.Error("Error while delete items in listname " + listName + ": Message = " + ex.Message + ": StackTrace = " + ex.StackTrace);
}
}
/// <summary>
/// Retrieve all listitems in a list
/// </summary>
/// <param name="libName">The list</param>
/// <param name="Context">The ClientContext</param>
/// <returns></returns>
public static List <ListItem> GetAllListItem(string listName, ClientContext Context, int Rows)
{
/*
* List<ListItem> items = new List<ListItem>();
* Context.Load(Context.Web, a => a.Lists);
* Context.ExecuteQuery();
*/
List list = Context.Web.Lists.GetByTitle(listName);
ListItemCollectionPosition position = null;
var camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View Scope='RecursiveAll'>
<Query>
<OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy>
</Query>
<ViewFields>
<FieldRef Name='Title'/><FieldRef Name='Modified' /><FieldRef Name='Editor' /><FieldRef Name='FileLeafRef' /><FieldRef Name='FileRef' /></ViewFields><RowLimit Paged='TRUE'>" + Rows + "</RowLimit></View>";
do
{
ListItemCollection listItems = null;
camlQuery.ListItemCollectionPosition = position;
listItems = list.GetItems(camlQuery);
Context.Load(listItems);
Context.ExecuteQuery();
position = listItems.ListItemCollectionPosition;
items.AddRange(listItems.ToList());
}while (position != null);
return(items);
}
public void UninstallIntegration(string integrationKey, Guid integrationId)
{
using (ClientContext clientContext = GetClientContext())
{
List list = clientContext.Web.Lists.GetByTitle(INT_LIST);
clientContext.Load(list);
ListItemCollection listItemCollection = list.GetItems(new CamlQuery());
clientContext.Load(listItemCollection);
clientContext.ExecuteQuery();
string intKey = _cryptographyService.Encrypt(integrationKey);
string intId = _cryptographyService.Encrypt(integrationId.ToString());
foreach (ListItem listItem in listItemCollection.ToList()
.Where(
i =>
((string)i["IntKey"]).Equals(intKey) &&
((string)i["IntID"]).Equals(intId)))
{
listItem.DeleteObject();
clientContext.ExecuteQuery();
break;
}
}
}
public IList <NameSourcePair> GetFilenamesWithSource()
{
this.EnsureInitialized();
using (ClientContext clientContext = this.CreateContext())
{
List oList = clientContext.Web.Lists.GetByTitle(this.m_settings.DocumentLibrary);
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><RowLimit>5000</RowLimit></View>";
ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem,
items => items.Include(
item => item.FieldValuesAsText));
clientContext.ExecuteQuery();
List <NameSourcePair> nameSourcePairList = new List <NameSourcePair>();
nameSourcePairList.AddRange((IEnumerable <NameSourcePair>)collListItem.ToList <ListItem>().Select <ListItem, NameSourcePair>((Func <ListItem, NameSourcePair>)(i => new NameSourcePair()
{
Name = i.FieldValuesAsText["FileLeafRef"],
Source = i.FieldValuesAsText["_Source"] == null ? string.Empty : i.FieldValuesAsText["_Source"]
})).ToList <NameSourcePair>());
return(nameSourcePairList);
}
}
internal static List <Order> ParseOrders(this ListItemCollection lc)
{
var ret = new List <Order>();
foreach (var il in lc.ToList())
{
ret.Add(il.ParseOrder());
}
return(ret);
}
public List <MyTestListItem> GetMyItems()
{
List list = Context.Web.Lists.GetByTitle(ListTitle);
CamlQuery camlQuery = CamlQuery.CreateAllItemsQuery();
ListItemCollection coll = list.GetItems(camlQuery);
Context.Load(coll);
Context.ExecuteQuery();
return(coll.ToList().Select(item => new MyTestListItem()
{
Id = item.Id,
Title = item["Title"]?.ToString(),
Modified = item.LastModifiedDateTime()
}).ToList());
}
/// <summary>
/// Get all items of a list
/// </summary>
/// <param name="spList">guid of sp List</param>
public List <ListItem> GetItems(List spList)
{
CamlQuery cmQuery = new CamlQuery();
CamlQuery query = CamlQuery.CreateAllItemsQuery(100);
ListItemCollection items = spList.GetItems(query);
Context.Load(items);
Context.ExecuteQuery();
lock (locky)
{
ListItemCollection collListItem = spList.GetItems(cmQuery);
Context.Load(collListItem);
Context.ExecuteQuery();
return(collListItem.ToList());
}
}
public List <ListItem> GetListData(Guid listId, ref ListItemCollectionPosition position)
{
ClientContext clientContext = _connect.GetClientContextSp;
List oList = clientContext.Web.Lists.GetById(listId);
List <ListItem> items = new List <ListItem>();
CamlQuery camlQuery = new CamlQuery
{
ViewXml =
"<View><Query><OrderBy><FieldRef Name=\"Created\" Ascending=\"false\" /></OrderBy></Query><RowLimit>100</RowLimit></View>"
};
//do
//{
ListItemCollection listItems = null;
if (position != null)
{
camlQuery.ListItemCollectionPosition = position;
}
listItems = oList.GetItems(camlQuery);
clientContext.Load(listItems);
clientContext.ExecuteQuery();
position = listItems.ListItemCollectionPosition;
items.AddRange(listItems.ToList());
//}
//while (position != null);
return(items);
//foreach (ListItem oListItem in collListItem)
//{
// Console.WriteLine("ID: {0} \nTitle: {1} \nBody: {2}", oListItem.Id, oListItem["Title"], oListItem["Body"]);
//}
}
public static void ClearSubscriptionStore()
{
Context.Load(Context.Web, w => w.Title, w => w.Description);
List subscriptionStore = Context.Web.Lists.GetByTitle("Push Notification Subscription Store");
Context.Load(subscriptionStore);
ListItemCollection listItems = subscriptionStore.GetItems(new CamlQuery());
Context.Load(listItems);
Context.ExecuteQueryAsync
(
(object sender1, ClientRequestSucceededEventArgs args1) =>
{
foreach (ListItem listItem in listItems.ToList())
{
listItem.DeleteObject();
}
Context.ExecuteQueryAsync(
(object sender2, ClientRequestSucceededEventArgs args2) =>
{
// Close channel if open and set registration status for current app instance.
CloseChannel();
SetRegistrationStatus(false);
ShowMessage("Subscriber store cleared.", "Success");
},
(object sender2, ClientRequestFailedEventArgs args2) =>
{
ShowMessage(args2.Exception.Message, "Error Deleting Subscribers");
});
},
(object sender1, ClientRequestFailedEventArgs args1) =>
{
ShowMessage(args1.Exception.Message, "Error Loading Subscribers List");
});
}
public void CreateXml(ListItemCollection InputList, DateTime date, string docType, List <ModXXFamXX> ModXXs, ClientContext clientContext)
{
try
{
DateTime inputDate = date;
XmlDocument xmlDoc = new XmlDocument();
string itemType = string.Empty; // to check item is document set(folder) or document(file)
string contentType = string.Empty; //to get content type
string confidentiality = string.Empty;
XmlNode nodeLevel4 = StaticNodes(xmlDoc, docType);
XmlNode nodeLevel5Dynamic = null;
XmlNode nodeLevel6Dynamic = null;
//Families for selected ModXXs
var selectedFamXX = (from A in ModXXs
select A.FamilAlias).ToList().Distinct().ToList();
//Selected ModXXs
var selectedModXX = (from A in ModXXs
select A.ModXXName).ToList();
var listItems = InputList.ToList();
if (docType == "WAR")
{
listItems = InputList.OrderBy(c => c["TATNS"]).ThenByDescending(n => n.FileSystemObjectType).ThenBy(n => n["FileLeafRef"]).ToList();
}
else
{
listItems = InputList.OrderBy(c => c["TATNS"]).ThenByDescending(n => n.FileSystemObjectType).ThenBy(n => n["FileLeafRef"]).ToList();
}
for (int i = 0; i < listItems.Count; i++)
{
itemType = listItems[i].FileSystemObjectType.ToString();
contentType = listItems[i].ContentType.Name;
confidentiality = Convert.ToString(listItems[i]["Confidentiality"]);
if (confidentiality == "Public" || String.IsNullOrEmpty(confidentiality))
{
if (docType == "WAR")
{
if (contentType.Contains("My ContentType"))
{
if (itemType == "Folder")
{
string strTATNS = string.Empty;
strTATNS = Convert.ToString(listItems[i]["TATNS"]);
string tatNumber = strTATNS.Split(new char[0])[0];
// Get list of existing LEVEL5 nodes.
string level5 = String.Format("/LEVEL3/LEVEL4/LEVEL5[@KEY='{0}']", tatNumber);
XmlNodeList node5List = xmlDoc.SelectNodes(level5);
if (node5List.Count > 0)
{
nodeLevel5Dynamic = node5List[0];
}
else
{
nodeLevel5Dynamic = DocumentSetNode(listItems[i], xmlDoc, tatNumber, strTATNS);
}
nodeLevel4.AppendChild(nodeLevel5Dynamic);
}
else
{
// check needs to be added for files with name starting with WAR
if (listItems[i]["FileLeafRef"].ToString().StartsWith("WAR"))
{
XmlNode nodeLevel6DynamicAw = DocumentNode(listItems[i], xmlDoc, inputDate);
nodeLevel5Dynamic.AppendChild(nodeLevel6DynamicAw);
}
}
}
}
else
{
if (contentType.Contains("MyBulletin"))
{
string strTATNS = string.Empty;
string strDescription = string.Empty;
string name = string.Empty;
strTATNS = Convert.ToString(listItems[i]["TATNS"]);
string tatNumber = strTATNS.Split(new char[0])[0];
name = Convert.ToString(listItems[i]["FileLeafRef"]);
strDescription = Convert.ToString(listItems[i]["ProductDescription"]);
if (itemType == "Folder")
{
// Get list of existing LEVEL5 nodes.
string level5 = String.Format("/LEVEL3/LEVEL4/LEVEL5[@KEY='{0}']", tatNumber);
XmlNodeList node5List = xmlDoc.SelectNodes(level5);
if (node5List.Count > 0)
{
nodeLevel5Dynamic = node5List[0];
}
else
{
nodeLevel5Dynamic = DocumentSetNode(listItems[i], xmlDoc, tatNumber, strTATNS);
}
nodeLevel4.AppendChild(nodeLevel5Dynamic);
// Get list of existing LEVEL6 nodes.
string level6 = String.Format("/LEVEL3/LEVEL4/LEVEL5/LEVEL6[@KEY='{0}']", name);
XmlNodeList node6List = xmlDoc.SelectNodes(level6);
if (node6List.Count > 0)
{
nodeLevel6Dynamic = node6List[0];
}
else
{
nodeLevel6Dynamic = WriteSB.DocumentSetNodeSBLevel6(listItems[i], xmlDoc, name, strDescription);
}
nodeLevel5Dynamic.AppendChild(nodeLevel6Dynamic);
}
else
{
// Find level6 node with same title as level 7 then add. Find level6 in all items with same title as listItems[i]
string title = Convert.ToString(listItems[i]["Title"]);
var docSetWithSameTitle = listItems.Where(s => Convert.ToString(s["Title"]) == title).Where(s => s.FileSystemObjectType == FileSystemObjectType.Folder).ToList();
//Check if node 6 has same name as docset above.
if (docSetWithSameTitle.Count > 0)
{
string nameofDocSet = Convert.ToString(docSetWithSameTitle[0]["FileLeafRef"]);
string level6 = String.Format("/LEVEL3/LEVEL4/LEVEL5/LEVEL6[@KEY='{0}']", nameofDocSet);
XmlNodeList node6List = xmlDoc.SelectNodes(level6);
if (node6List.Count > 0)
{
nodeLevel6Dynamic = node6List[0];
}
XmlNode nodeLevel7Dynamic = WriteSB.DocumentNodeSB(listItems[i], xmlDoc, inputDate);
nodeLevel6Dynamic.AppendChild(nodeLevel7Dynamic);
}
else
{
logger.LogMissingTitles("Following file is missing Title. Downloaded but not included in XML: " + listItems[i].DisplayName, selectedFamXX, selectedModXX);
//logger.WriteEventLog("Following file is missing Title. Downloaded but not included in XML: " + listItems[i].DisplayName);
}
// Download file
download.FileRef(listItems[i], clientContext, selectedFamXX, selectedModXX);
}
}
}
}
}
string fileDirectory = ConfigurationManager.AppSettings["XmlFileLocation"];
if (!Directory.Exists(fileDirectory))
{
Directory.CreateDirectory(fileDirectory);
}
if (docType == "WAR")
{
string strAbbreviation = string.Join("", selectedFamXX.ToArray());
string strModXX = string.Join("", selectedModXX.ToArray()) + "_AW_INDEX";
string xmlFileLocation = fileDirectory + strAbbreviation + strModXX + ".xml";
xmlDoc.Save(xmlFileLocation);
string formattedString = formatXml.ReplaceSpecialChar(xmlFileLocation);
XmlDocument formattedXml = new XmlDocument();
formattedXml.LoadXml(formattedString);
formattedXml.Save(xmlFileLocation);
}
else
{
string strSBAbbreviation = string.Join("", selectedFamXX.ToArray());
string strModXX = string.Join("", selectedModXX.ToArray()) + "_SB_INDEX";
string xmlFileLocation = fileDirectory + strSBAbbreviation + strModXX + ".xml";
xmlDoc.Save(xmlFileLocation);
string formattedString = formatXml.ReplaceSpecialChar(xmlFileLocation);
XmlDocument formattedXml = new XmlDocument();
formattedXml.LoadXml(formattedString);
formattedXml.Save(xmlFileLocation);
}
}
catch (Exception exec)
{
logger.WriteEventLog("Please contact admin : " + exec.Message);
throw exec;
}
}
static void Main(string[] args)
{
StreamWriter log;
if (!System.IO.File.Exists("D:/logfile.txt"))
{
log = new StreamWriter("D:/logfile.txt");
}
else
{
log = System.IO.File.AppendText("D:/logfile.txt");
}
try
{
string path = @"\\sr3prd01.carpetright.co.uk\connect";
string SAPfileName = "ConnectSAP.csv";
string PAYROLLfileName = @"managers.csv";
string SAPOutputfilepath = @"D:\StoreOutput.txt";
string SAPInputfilepath = @"D:\ConnectSAP.csv";
string PAYROLLOutputfilepath = @"D:\PayrollOutput.txt";
string PAYROLLInputfilepath = @"D:\managers.csv";
string filePath = Path.Combine(path, Path.GetFileName(SAPfileName));
if (System.IO.File.Exists(SAPOutputfilepath))
{
System.IO.File.Delete(SAPOutputfilepath);
Console.WriteLine("File deleted " + SAPOutputfilepath);
}
if (System.IO.File.Exists(SAPInputfilepath))
{
System.IO.File.Delete(SAPInputfilepath);
Console.WriteLine("File deleted " + SAPInputfilepath);
}
if (System.IO.File.Exists(PAYROLLOutputfilepath))
{
System.IO.File.Delete(PAYROLLOutputfilepath);
Console.WriteLine("File deleted " + PAYROLLOutputfilepath);
}
if (System.IO.File.Exists(PAYROLLInputfilepath))
{
System.IO.File.Delete(PAYROLLInputfilepath);
Console.WriteLine("File deleted " + PAYROLLInputfilepath);
}
if (System.IO.File.Exists(filePath))
{
System.IO.File.Copy(filePath, SAPInputfilepath, true);
Console.WriteLine("File copied from " + filePath);
}
filePath = Path.Combine(path, Path.GetFileName(PAYROLLfileName));
if (System.IO.File.Exists(filePath))
{
System.IO.File.Copy(filePath, PAYROLLInputfilepath, true);
Console.WriteLine("File copied from " + filePath);
}
using (StreamWriter sw = System.IO.File.CreateText(SAPOutputfilepath))
{
// Open the file to read from.
using (StreamReader sr = System.IO.File.OpenText(SAPInputfilepath))
{
string s = "";
while ((s = sr.ReadLine()) != null)
{
//Console.WriteLine(s);
if (s.Contains("OPEN"))
{
sw.WriteLine(s);
}
}
}
}
using (StreamWriter sw = System.IO.File.CreateText(PAYROLLOutputfilepath))
{
// Open the file to read from.
using (StreamReader sr = System.IO.File.OpenText(PAYROLLInputfilepath))
{
string s = "";
while ((s = sr.ReadLine()) != null)
{
//Console.WriteLine(s);
sw.WriteLine(s);
}
}
}
if (System.IO.File.Exists(SAPOutputfilepath))
{
string webUrl = args[0];
Console.WriteLine("Site Url is : " + args[0]);
ClientContext clientContext = new ClientContext(webUrl);
List StoreContactList = clientContext.Web.Lists.GetByTitle("Store Contact Information");
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><RowLimit>700</RowLimit></View>";
ListItemCollection collListItems = StoreContactList.GetItems(camlQuery);
clientContext.Load(collListItems);
clientContext.ExecuteQuery();
if (collListItems.Count > 0)
{
foreach (ListItem item in collListItems.ToList())
{
item.DeleteObject();
clientContext.ExecuteQuery();
}
}
string[] parts = null;
string[] PayRollparts = null;
foreach (string line in System.IO.File.ReadAllLines(SAPOutputfilepath))
{
parts = line.Split(',');
IEnumerable <string> Payrollines = System.IO.File.ReadLines(PAYROLLOutputfilepath).Where(x => x.StartsWith(parts[0]));
var PayrollData = Payrollines.FirstOrDefault();
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem oListItem = StoreContactList.AddItem(itemCreateInfo);
oListItem["Site"] = parts[0];
oListItem["Name"] = parts[1];
oListItem["Address"] = parts[5];
oListItem["Country"] = parts[9];
oListItem["Postcode"] = parts[6];
oListItem["Town"] = parts[7];
oListItem["County"] = parts[8];
oListItem["DC_x0020_Name"] = parts[2];
oListItem["Division_x0020_Code_x0020_Name"] = parts[3];
oListItem["Reg_x0020_Code"] = parts[4];
oListItem["Phone"] = parts[13];
oListItem["Fax"] = parts[14];
oListItem["Near_x0020_To"] = parts[10];
oListItem["Host_x0020_Store"] = parts[11];
if (PayrollData != null)
{
PayRollparts = PayrollData.Split(',');
oListItem["First_x0020_Name"] = PayRollparts[4];
oListItem["Surname"] = PayRollparts[5];
oListItem["Job_x0020_Title"] = PayRollparts[6];
oListItem["Division_x0020_Code"] = PayRollparts[2];
}
oListItem.Update();
clientContext.ExecuteQuery();
parts = null;
PayRollparts = null;
}
}
}
catch (Exception e)
{
log.WriteLine("{0} Exception caught.", e.Message);
}
}
///// <summary>
///// Creates the sp group.
///// </summary>
///// <param name="role">The role.</param>
//private void CreateSPGroup(string role)
//{
// try
// {
// Group addgroup = null;
// try
// {
// addgroup = this.context.Web.SiteGroups.GetByName(role);
// this.context.Load(addgroup);
// this.context.ExecuteQuery();
// }
// catch
// {
// Logger.Error(role + " Group not found.Creating Group now.");
// addgroup = null;
// }
// if (addgroup == null)
// {
// //this.web.BreakRoleInheritance(true, false);
// User owner = this.web.EnsureUser("*****@*****.**");
// // User member = this.web.EnsureUser("*****@*****.**");
// GroupCreationInformation groupCreationInfo = new GroupCreationInformation();
// groupCreationInfo.Title = role;
// groupCreationInfo.Description = "Group Name : " + role;
// Group group = this.web.SiteGroups.Add(groupCreationInfo);
// group.Owner = owner;
// // group.Users.AddUser(member);
// group.Update();
// this.context.ExecuteQuery();
// // Get the Role Definition (Permission Level)
// var customFullControlRoleDefinition = this.web.RoleDefinitions.GetByName("Contribute");
// this.context.Load(customFullControlRoleDefinition);
// this.context.ExecuteQuery();
// // Add it to the Role Definition Binding Collection
// RoleDefinitionBindingCollection collRDB = new RoleDefinitionBindingCollection(this.context);
// collRDB.Add(this.web.RoleDefinitions.GetByName("Contribute"));
// // Bind the Newly Created Permission Level to the new User Group
// this.web.RoleAssignments.Add(group, collRDB);
// this.context.Load(group);
// this.context.ExecuteQuery();
// }
// }
// catch (Exception ex)
// {
// Logger.Error("Error while adding Group name =" + role + " Message =" + ex.Message + " StackTrace = " + ex.StackTrace);
// }
//}
///// <summary>
///// Deletes the sp group.
///// </summary>
///// <param name="role">The role.</param>
//private void DeleteSPGroup(string role)
//{
// Group deletegroup = null;
// try
// {
// deletegroup = this.context.Web.SiteGroups.GetByName(role);
// this.context.Load(deletegroup);
// this.context.ExecuteQuery();
// }
// catch
// {
// Logger.Error(role + " Group could not found in sharepoint Group.");
// deletegroup = null;
// }
// try
// {
// if (deletegroup != null)
// {
// this.web.RoleAssignments.GetByPrincipal(deletegroup).DeleteObject();
// this.web.Update();
// this.context.ExecuteQuery();
// GroupCollection groupColl = this.web.SiteGroups;
// groupColl.Remove(deletegroup);
// this.context.ExecuteQuery();
// }
// }
// catch (Exception ex)
// {
// Logger.Error("Error while deleting Group name =" + role + " Message =" + ex.Message + " StackTrace = " + ex.StackTrace);
// }
//}
/// <summary>
/// Deletes the role.
/// </summary>
/// <param name="roleID">The role identifier.</param>
/// <returns></returns>
public ActionStatus DeleteRole(int roleID)
{
ActionStatus status = new ActionStatus();
try
{
string roleName = string.Empty;
if (roleID > 0)
{
List roleMaster = this.web.Lists.GetByTitle(Masters.ROLEMASTER);
ListItem item = roleMaster.GetItemById(roleID);
this.context.Load(item);
this.context.ExecuteQuery();
if (item != null)
{
roleName = Convert.ToString(item["Role"]);
List emplist = this.web.Lists.GetByTitle(Masters.APPROVERMASTER);
CamlQuery camlEmpQuery = new CamlQuery();
camlEmpQuery.ViewXml = @"<View>
<Query>
<Where>
<Eq>
<FieldRef Name='Role' />
<Value Type='Choice'>" + roleName + @"</Value>
</Eq>
</Where>
</Query>
</View>";
ListItemCollection empitems = emplist.GetItems(camlEmpQuery);
this.context.Load(empitems);
this.context.ExecuteQuery();
if (empitems != null && empitems.Count > 0)
{
status.IsSucceed = false;
status.Messages.Add("You can't delete this role because " + empitems.Count + " employee(s) are already assigned to this role.To delete this role please remove/reassign the other role to employee(s).");
}
else if (empitems == null || empitems.Count == 0)
{
List screenMapMaster = this.web.Lists.GetByTitle(ICCPListNames.ROLESCREENMAPPING);
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = @"<View>
<Query>
<Where>
<Eq>
<FieldRef Name='RoleID' />
<Value Type='Lookup'>" + roleName + @"</Value>
</Eq>
</Where>
</Query>
</View>";
ListItemCollection items = screenMapMaster.GetItems(camlQuery);
this.context.Load(items);
this.context.ExecuteQuery();
if (items != null && items.Count > 0)
{
foreach (ListItem deleteitem in items.ToList())
{
deleteitem.DeleteObject();
}
this.context.ExecuteQuery();
}
if (items != null && items.Count == 0)
{
item.DeleteObject();
this.context.ExecuteQuery();
Field rolefield = emplist.Fields.GetByTitle("Role");
FieldChoice fieldChoice = this.context.CastTo <FieldChoice>(rolefield);
this.context.Load(fieldChoice);
this.context.ExecuteQuery();
List <string> options = new List <string>(fieldChoice.Choices);
options.Remove(roleName);
fieldChoice.Choices = options.ToArray();
fieldChoice.Update();
this.context.ExecuteQuery();
// this.DeleteSPGroup(Convert.ToString(roleName));
status.IsSucceed = true;
status.Messages.Add("Role Deleted Successfully.");
}
}
}
}
}
catch (Exception ex)
{
status.IsSucceed = true;
status.Messages.Add("Sorry! Error while delete Role.");
Logger.Error("Error while delete role having roleId =" + roleID + " Message =" + ex.Message + " StackTrace = " + ex.StackTrace);
}
return(status);
}
public static List <ClientDocumentListItem> ToClientDocumentList(this ListItemCollection files)
{
return(files.ToList().ConvertAll(ListItemToClientDocItem));
}
/// <summary>
/// Action for this SharePoint folder
/// </summary>
public override void Process()
{
RunningManager.Logger.Debug($"FolderRunner Process() - {ActiveReceivers.Count} active receivers");
Context.Load(Element,
f => f.Name,
f => f.ServerRelativeUrl,
f => f.ListItemAllFields["FileRef"],
f => f.ListItemAllFields.ParentList.ItemCount);
Context.ExecuteQuery();
RunningManager.Logger.Debug($"Folder | Name: {Element.Name} / URL: {Element.ServerRelativeUrl}");
// OnFolderRunningStart
RunningManager.Logger.Debug("FolderRunner OnFolderRunningStart()");
ActiveReceivers.ForEach(r => r.OnFolderRunningStart(Element));
// If at least one receiver run list items of deeper
if (Manager.Receivers.Any(r => r.IsReceiverCalledOrDeeper(RunningLevel.ListItem)))
{
List <ListItem> items = new List <ListItem>();
if (Element.ListItemAllFields.ParentList.ItemCount > 5000)
{
// Manage large lists
int count = 0;
int inter = 1000;
int countList = Element.ListItemAllFields.ParentList.ItemCount;
while (count < countList)
{
CamlQuery itemsQuery = new CamlQuery()
{
FolderServerRelativeUrl = Element.ListItemAllFields["FileRef"].ToString(),
ViewXml = $"<View><Query><Where><And><Gt><FieldRef Name='ID'/><Value Type='Counter'>{count}</Value></Gt><Leq><FieldRef Name='ID'/><Value Type='Counter'>{count + inter}</Value></Leq></And></Where><OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy></Query></View><RowLimit>{inter}</RowLimit>"
};
ListItemCollection itemsResult = Element.ListItemAllFields.ParentList.GetItems(itemsQuery);
Context.Load(itemsResult);
Context.ExecuteQuery();
items.AddRange(itemsResult);
count += inter;
}
}
else
{
CamlQuery itemsQuery = new CamlQuery()
{
FolderServerRelativeUrl = Element.ListItemAllFields["FileRef"].ToString(),
ViewXml = "<View><Query></Query></View>"
};
ListItemCollection itemsResult = Element.ListItemAllFields.ParentList.GetItems(itemsQuery);
Context.Load(itemsResult);
Context.ExecuteQuery();
items = itemsResult.ToList();
}
List <ListItemRunner> itemRunners = new List <ListItemRunner>();
foreach (ListItem item in items)
{
itemRunners.Add(new ListItemRunner(Manager, Context, item));
}
itemRunners.ForEach(r => r.Process());
}
// OnFolderRunningEnd
RunningManager.Logger.Debug("FolderRunner OnFolderRunningEnd()");
ActiveReceivers.ForEach(r => r.OnFolderRunningEnd(Element));
List <ListItem> subFolders = new List <ListItem>();
if (Element.ListItemAllFields.ParentList.ItemCount > 5000)
{
// Manage large lists
int count = 0;
int inter = 1000;
int countList = Element.ListItemAllFields.ParentList.ItemCount;
while (count < countList)
{
CamlQuery subFoldersQuery = new CamlQuery()
{
FolderServerRelativeUrl = Element.ListItemAllFields["FileRef"].ToString(),
ViewXml = $"<View><Query><Where><And><And><Gt><FieldRef Name='ID'/><Value Type='Counter'>{count}</Value></Gt><Leq><FieldRef Name='ID'/><Value Type='Counter'>{count + inter}</Value></Leq></And><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></And></Where><OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy></Query></View><RowLimit>{inter}</RowLimit>"
};
ListItemCollection subFoldersResult = Element.ListItemAllFields.ParentList.GetItems(subFoldersQuery);
Context.Load(subFoldersResult);
Context.ExecuteQuery();
subFolders.AddRange(subFoldersResult);
count += inter;
}
}
else
{
CamlQuery subFoldersQuery = new CamlQuery()
{
FolderServerRelativeUrl = Element.ListItemAllFields["FileRef"].ToString(),
ViewXml = "<View><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where></Query></View>"
};
// Crawl sub folders
ListItemCollection subFoldersResult = Element.ListItemAllFields.ParentList.GetItems(subFoldersQuery);
Context.Load(subFoldersResult,
coll => coll.Include(
f => f.Folder));
Context.ExecuteQuery();
subFolders = subFoldersResult.ToList();
}
List <FolderRunner> folderRunners = new List <FolderRunner>();
foreach (ListItem folder in subFolders)
{
folderRunners.Add(new FolderRunner(Manager, Context, folder.Folder));
}
folderRunners.ForEach(r => r.Process());
// OnFolderRunningEndAfterSubFolders
RunningManager.Logger.Debug("FolderRunner OnFolderRunningEndAfterSubFolders()");
ActiveReceivers.ForEach(r => r.OnFolderRunningEndAfterSubFolders(Element));
}
public static void GetFailedDocuments()
{
using (ClientContext ctx = new ClientContext(ConfigurationManager.AppSettings["WebUrl"].ToString())) //sharepointurl
{
// SecureString securePassword = GetSecureString(Utility.)
ctx.ExecutingWebRequest += new EventHandler <WebRequestEventArgs>(ctx_MixedAuthRequest);
//Set the Windows credentials.
ctx.AuthenticationMode = ClientAuthenticationMode.Default;
try
{
//Connect to sharepoint
string username = ConfigurationManager.AppSettings["UserName"].ToString();
string password = ConfigurationManager.AppSettings["Password"].ToString();
string domain = ConfigurationManager.AppSettings["Domain"].ToString();
ctx.Credentials = new NetworkCredential(username, password, domain);
Console.WriteLine("Successfully connected to Sharepoint");
//Build Query.
Web web = ctx.Web;
List list = web.Lists.GetByTitle(ConfigurationManager.AppSettings["Title"].ToString());
var query = new CamlQuery();
query.ViewXml = string.Format(@"<View>
<Query>
<Where>
<Eq>
<FieldRef Name='IsEnadocUploaded' />
<Value Type='Boolean'>0</Value>
</Eq>
</Where>
</Query>
</View>");
ListItemCollection listItems = list.GetItems(query);
//Load List
ctx.Load(listItems);
ctx.ExecuteQuery();
var s = listItems.ToList().Select(i => i["MainDocumentId"]).Distinct();
//Loop No of IDs
foreach (var itm in s)
{
if (itm != null)
{
Console.WriteLine(itm);
int ID = int.Parse(itm.ToString());
try
{
var client = new RestClient(ConfigurationManager.AppSettings["ApiUrl"].ToString());//api
var request = new RestRequest(ConfigurationManager.AppSettings["ApiResource"].ToString(), Method.POST);
string mainDocumentID = ID.ToString();
request.AddHeader("Id", mainDocumentID);
var result = client.Execute(request);
if (result.StatusCode == HttpStatusCode.OK)
{
bool text = true;
Print("GetFailedDocuments ", " Document Uploaded. Document ID : " + mainDocumentID, text);
Console.WriteLine("Document Uploaded. Document ID: " + mainDocumentID);
continue;
}
else
{
bool text = true;
Print("GetFailedDocuments ", " Document Failed from StatusCode. Document ID : " + mainDocumentID, text);
Console.WriteLine("Document Failed.Document ID: " + mainDocumentID);
}
}
catch (Exception e)
{
bool text = false;
string er = string.Format(" Document Failed. Documet Id : {0} Error : {1}", ID, e);
Print("GetFailedDocuments", er, text);
Console.WriteLine("Error : " + e);
continue;
}
}
else
{
bool text = true;
Print("GetFailedDocuments", " Document Failed. Document ID : Null ", text);
Console.WriteLine("Document Id is Null.");
}
Console.ReadLine();
}
bool text1 = true;
string logmsg = String.Format("Successfully pushed {0} Documents. ", listItems.Count());
Print("GetFailedDocuments", logmsg, text1);
Console.WriteLine("Successfully pushed {0} Documents. ", listItems.Count());
//key - auto - value - no
var run = ConfigurationManager.AppSettings["auto"];
Console.ReadLine();
}
catch (Exception ex)
{
bool text = false;
Print("GetFailedDocuments", "Error : " + ex, text);
Console.WriteLine(ex);
}
}
return;
}
/// <summary>
/// Action for this SharePoint list
/// </summary>
public override void Process()
{
RunningManager.Logger.Debug($"ListRunner Process() - {ActiveReceivers.Count} active receivers");
Context.Load(Element,
l => l.Title,
l => l.RootFolder.ServerRelativeUrl);
Context.ExecuteQuery();
RunningManager.Logger.Debug($"List | Title: {Element.Title} / URL: {Element.RootFolder.ServerRelativeUrl}");
// OnListRunningStart
RunningManager.Logger.Debug("ListRunner OnListRunningStart()");
ActiveReceivers.ForEach(r => r.OnListRunningStart(Element));
// If at least one receiver run views
if (Manager.Receivers.Any(r => r.IsReceiverCalledOrDeeper(RunningLevel.View)))
{
// Crawl views
Context.Load(Element.Views);
Context.ExecuteQuery();
List <ViewRunner> viewRunners = new List <ViewRunner>();
foreach (View view in Element.Views)
{
viewRunners.Add(new ViewRunner(Manager, Context, view));
}
viewRunners.ForEach(r => r.Process());
}
// If at least one receiver run folders or deeper
if (Manager.Receivers.Any(r => r.IsReceiverCalledOrDeeper(RunningLevel.Folder)))
{
List <ListItem> folders = new List <ListItem>();
if (Element.ItemCount > 5000)
{
// Manage large lists
int count = 0;
int inter = 1000;
int countList = Element.ItemCount;
while (count < countList)
{
CamlQuery foldersQuery = new CamlQuery()
{
ViewXml = $"<View><Query><Where><And><And><Gt><FieldRef Name='ID'/><Value Type='Counter'>{count}</Value></Gt><Leq><FieldRef Name='ID'/><Value Type='Counter'>{count + inter}</Value></Leq></And><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></And></Where><OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy></Query></View><RowLimit>{inter}</RowLimit>"
};
ListItemCollection foldersResult = Element.GetItems(foldersQuery);
Context.Load(foldersResult);
Context.ExecuteQuery();
folders.AddRange(foldersResult);
count += inter;
}
}
else
{
CamlQuery foldersQuery = new CamlQuery()
{
ViewXml = "<View><Query><Where><Eq><FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value></Eq></Where></Query></View>"
};
ListItemCollection foldersResult = Element.GetItems(foldersQuery);
Context.Load(foldersResult,
coll => coll.Include(
f => f.Folder));
Context.ExecuteQuery();
folders = foldersResult.ToList();
}
List <FolderRunner> folderRunners = new List <FolderRunner>();
foreach (ListItem folder in folders)
{
folderRunners.Add(new FolderRunner(Manager, Context, folder.Folder));
}
folderRunners.ForEach(r => r.Process());
}
else if (Manager.Receivers.Any(r => r.IsReceiverCalledOrDeeper(RunningLevel.ListItem)))
{
List <ListItem> items = new List <ListItem>();
if (Element.ItemCount > 5000)
{
// Manage large lists
int count = 0;
int inter = 1000;
int countList = Element.ItemCount;
while (count < countList)
{
CamlQuery itemsQuery = new CamlQuery()
{
ViewXml = $"<View Scope='RecursiveAll'><Query><Where><And><Gt><FieldRef Name='ID'/><Value Type='Counter'>{count}</Value></Gt><Leq><FieldRef Name='ID'/><Value Type='Counter'>{count + inter}</Value></Leq></And></Where><OrderBy Override='TRUE'><FieldRef Name='ID'/></OrderBy></Query></View><RowLimit>{inter}</RowLimit>"
};
ListItemCollection itemsResult = Element.GetItems(itemsQuery);
Context.Load(itemsResult);
Context.ExecuteQuery();
items.AddRange(itemsResult);
count += inter;
}
}
else
{
CamlQuery itemsQuery = new CamlQuery()
{
ViewXml = "<View Scope='RecursiveAll'><Query></Query></View>"
};
ListItemCollection itemsResult = Element.GetItems(itemsQuery);
Context.Load(itemsResult);
Context.ExecuteQuery();
items = itemsResult.ToList();
}
List <ListItemRunner> itemRunners = new List <ListItemRunner>();
foreach (ListItem item in items)
{
itemRunners.Add(new ListItemRunner(Manager, Context, item));
}
itemRunners.ForEach(r => r.Process());
}
// OnListRunningEnd
RunningManager.Logger.Debug("ListRunner OnListRunningEnd()");
ActiveReceivers.ForEach(r => r.OnListRunningEnd(Element));
}
static void Main(string[] args)
{
string pathArchivoCompleto = "C:\\Users\\k697344\\Documents\\Comex PPG\\Documentacion\\Documentos Control Documental_V1_004.xlsx";
string nombrePestana = "CM FINAL";
string usuarioSharePoint = "S004221";
string passwordSharePoint = "Julio2019";
string urlShareFolder = "\\\\10.104.175.150\\Campania\\Reporte";
string urlCompletoFolder = "";
string urlSharePointOrigen = "https://one.web.ppg.com/la/mexico/ppgmexico/CalidadTotal/Control_Documental/BckP_SJDR";
string urlSharePointDestino = "https://one.web.ppg.com/la/mexico/ppgmexico/CalidadTotal/Control_Documental/DocsPublic";
string urlCompletoOrigen = "";
string urlCompletoDestino = "";
string siteUrl = "https://one.web.ppg.com/la/mexico/ppgmexico/CalidadTotal/Control_Documental/";
string bibliotecaDocumentoSP = "Manual de Calidad";
string catalogoArea = "Area";
string catalogoDepartamento = "Department";
string catalogoDocType = "DocType";
string catalogoSBU = "SBU";
string catalogoCliente = "Clientes";
ExcelQueryFactory book = new ExcelQueryFactory();
List <Archivo> ListArchivo = new List <Archivo>();
List <Archivo> ListArchivoEncontrado = new List <Archivo>();
//string nombreArchivo = "\\\\10.104.175.150\\Campania\\Reporte\\Guid.NewGuid().ToString()" + ".xls";
string pathArchivoExcel = "\\\\10.104.175.150\\Campania\\Reporte\\Archivos_Cargados_" + Guid.NewGuid().ToString() + ".xls";
DataSet dsArchivoExcel = new DataSet();
DataTable dtArchivoExcel = new DataTable();
DataSet dsCatalogos = new DataSet();
DataTable dtArea = new DataTable();
DataTable dtDepartamento = new DataTable();
DataTable dtDocType = new DataTable();
dtArchivoExcel.Columns.Add("Area");
dtArchivoExcel.Columns.Add("Departamento");
dtArchivoExcel.Columns.Add("TipoDocumento");
dtArchivoExcel.Columns.Add("DepartamentoCodigo");
dtArchivoExcel.Columns.Add("Codigo");
dtArchivoExcel.Columns.Add("NombreDocumento");
dtArchivoExcel.Columns.Add("DescripcionDocumento");
dtArchivoExcel.Columns.Add("NumeroRevision");
dtArchivoExcel.Columns.Add("FCambioFijo");
dtArchivoExcel.Columns.Add("FCambioFrecuente");
dtArchivoExcel.Columns.Add("SBU");
dtArchivoExcel.Columns.Add("Cliente");
try
{
////LECTURA DEL ARCHIVO EXCEL
//book = new ExcelQueryFactory(pathArchivoCompleto);
//ListArchivo = book.Worksheet(nombrePestana).AsEnumerable()
// .Select(n => new Archivo
// {
// Area = n["Area"].Cast<string>(),
// Departamento = n["Department"].Cast<string>(),
// TipoDocumento = n["Document"].Cast<string>(),
// DepartamentoCodigo = n["Department Code"].Cast<string>(),
// Codigo = n["Archivo"].Cast<string>(),
// NombreDocumento = n["Archivo"].Cast<string>(),
// DescripcionDocumento = n["Name Document"].Cast<string>(),
// NumeroRevision = n["Revision"].Cast<string>(),
// FCambioFijo = n["Date Revision"].Cast<string>(),
// FCambioFrecuente = n["Date Revision"].Cast<string>(),
// SBU = n["SBU"].Cast<string>(),
// Cliente = n["Cliente"].Cast<string>()
// }).ToList();
////CARGAR CLIENTE
////ListArchivo = ListArchivo.Where(n => !string.IsNullOrEmpty(n.DescripcionDocumento)).ToList();
//ListArchivo = ListArchivo.Where(n => !string.IsNullOrEmpty(n.Area) && n.Area.ToUpper() == "SATELITES").ToList();
#region CARGAR ORIGEN DESTINO
/*
* //CARGAR ARCHIVOS A SHARE POINT
* foreach (Archivo a in ListArchivo.ToList())
* //.Where(n => !string.IsNullOrEmpty(n.Codigo) && n.Codigo.Trim() == "IT-1509").ToList())
* {
* if (!string.IsNullOrEmpty(a.Codigo))
* {
* //SE BUSCA EN FORMATO EXCEL
* ///////////////////////////
* urlCompletoOrigen = Path.Combine(HttpUtility.HtmlEncode(urlSharePointOrigen), a.Codigo.Trim()); //+ ".xls");
* urlCompletoFolder = Path.Combine(HttpUtility.HtmlEncode(urlShareFolder), a.Codigo.Trim()); //+ ".xls");
* urlCompletoDestino = Path.Combine(HttpUtility.HtmlEncode(urlSharePointDestino), a.Codigo.Trim()); //+ ".xls");
*
* try
* {
* using (WebClient client = new WebClient())
* {
* client.Credentials = new NetworkCredential(usuarioSharePoint, passwordSharePoint);
* client.DownloadFile(urlCompletoOrigen, urlCompletoFolder);
* client.UploadFile(urlCompletoDestino, "PUT", urlCompletoFolder);
*
* a.Codigo = a.Codigo.Trim(); //+ ".xls";
*
* ListArchivoEncontrado.Add(a);
*
* continue;
* }
*
* //a.Codigo = a.Codigo.Trim(); //+ ".xls";
*
* //ListArchivoEncontrado.Add(a);
* }
* catch (Exception ex)
* {
* continue;
* }
*
* ////INTENTO SIN ESPACIOS EN BLANCO
* //urlCompletoOrigen = Path.Combine(HttpUtility.HtmlEncode(urlSharePointOrigen), a.Codigo.Trim().Replace(" ", "") + ".xls");
* //urlCompletoFolder = Path.Combine(HttpUtility.HtmlEncode(urlShareFolder), a.Codigo.Trim().Replace(" ", "") + ".xls");
* //urlCompletoDestino = Path.Combine(HttpUtility.HtmlEncode(urlSharePointDestino), a.Codigo.Trim().Replace(" ", "") + ".xls");
*
* //try
* //{
* // using (WebClient client = new WebClient())
* // {
* // client.Credentials = new NetworkCredential(usuarioSharePoint, passwordSharePoint);
* // client.DownloadFile(urlCompletoOrigen, urlCompletoFolder);
* // client.UploadFile(urlCompletoDestino, "PUT", urlCompletoFolder);
*
* // a.Codigo = a.Codigo.Trim().Replace(" ", "") + ".xls";
*
* // ListArchivoEncontrado.Add(a);
*
* // continue;
* // }
* //}
* //catch (Exception ex)
* //{
*
* //}
*
* ////SE BUSCA EN FORMATO WORD
* ////////////////////////////
* //urlCompletoOrigen = Path.Combine(HttpUtility.HtmlEncode(urlSharePointOrigen), a.Codigo.Trim() + ".doc");
* //urlCompletoFolder = Path.Combine(HttpUtility.HtmlEncode(urlShareFolder), a.Codigo.Trim() + ".doc");
* //urlCompletoDestino = Path.Combine(HttpUtility.HtmlEncode(urlSharePointDestino), a.Codigo.Trim() + ".doc");
*
* //try
* //{
* // using (WebClient client = new WebClient())
* // {
* // client.Credentials = new NetworkCredential(usuarioSharePoint, passwordSharePoint);
* // client.DownloadFile(urlCompletoOrigen, urlCompletoFolder);
* // client.UploadFile(urlCompletoDestino, "PUT", urlCompletoFolder);
*
* // a.Codigo = a.Codigo.Trim().Replace(" ", "") + ".doc";
*
* // ListArchivoEncontrado.Add(a);
*
* // continue;
* // }
* //}
* //catch (Exception ex)
* //{
*
* //}
*
* ////INTENTO SIN ESPACIOS EN BLANCO
* //urlCompletoOrigen = Path.Combine(HttpUtility.HtmlEncode(urlSharePointOrigen), a.Codigo.Trim().Replace(" ", "") + ".doc");
* //urlCompletoFolder = Path.Combine(HttpUtility.HtmlEncode(urlShareFolder), a.Codigo.Trim().Replace(" ", "") + ".doc");
* //urlCompletoDestino = Path.Combine(HttpUtility.HtmlEncode(urlSharePointDestino), a.Codigo.Trim().Replace(" ", "") + ".doc");
*
* //try
* //{
* // using (WebClient client = new WebClient())
* // {
* // client.Credentials = new NetworkCredential(usuarioSharePoint, passwordSharePoint);
* // client.DownloadFile(urlCompletoOrigen, urlCompletoFolder);
* // client.UploadFile(urlCompletoDestino, "PUT", urlCompletoFolder);
*
* // a.Codigo = a.Codigo.Trim().Replace(" ", "") + ".doc";
*
* // ListArchivoEncontrado.Add(a);
*
* // continue;
* // }
* //}
* //catch (Exception ex)
* //{
*
* //}
*
* ////SE BUSCA EN FORMATO PDF
* ///////////////////////////
* //urlCompletoOrigen = Path.Combine(HttpUtility.HtmlEncode(urlSharePointOrigen), a.Codigo.Trim() + ".pdf");
* //urlCompletoFolder = Path.Combine(HttpUtility.HtmlEncode(urlShareFolder), a.Codigo.Trim() + ".pdf");
* //urlCompletoDestino = Path.Combine(HttpUtility.HtmlEncode(urlSharePointDestino), a.Codigo.Trim() + ".pdf");
*
* //try
* //{
* // using (WebClient client = new WebClient())
* // {
* // client.Credentials = new NetworkCredential(usuarioSharePoint, passwordSharePoint);
* // client.DownloadFile(urlCompletoOrigen, urlCompletoFolder);
* // client.UploadFile(urlCompletoDestino, "PUT", urlCompletoFolder);
*
* // a.Codigo = a.Codigo.Trim() + ".pdf";
*
* // ListArchivoEncontrado.Add(a);
*
* // continue;
* // }
* //}
* //catch (Exception ex)
* //{
*
* //}
*
* ////INTENTO SIN ESPACIOS EN BLANCO
* //urlCompletoOrigen = Path.Combine(HttpUtility.HtmlEncode(urlSharePointOrigen), a.Codigo.Trim().Replace(" ", "") + ".pdf");
* //urlCompletoFolder = Path.Combine(HttpUtility.HtmlEncode(urlShareFolder), a.Codigo.Trim().Replace(" ", "") + ".pdf");
* //urlCompletoDestino = Path.Combine(HttpUtility.HtmlEncode(urlSharePointDestino), a.Codigo.Trim().Replace(" ", "") + ".pdf");
*
* //try
* //{
* // using (WebClient client = new WebClient())
* // {
* // client.Credentials = new NetworkCredential(usuarioSharePoint, passwordSharePoint);
* // client.DownloadFile(urlCompletoOrigen, urlCompletoFolder);
* // client.UploadFile(urlCompletoDestino, "PUT", urlCompletoFolder);
*
* // a.Codigo = a.Codigo.Trim().Replace(" ", "") + ".pdf";
*
* // ListArchivoEncontrado.Add(a);
*
* // continue;
* // }
* //}
* //catch (Exception ex)
* //{
*
* //}
*
* }
* }
*
*/
#endregion
ClientContext clientContext = new ClientContext(siteUrl);
SP.Web myWeb = clientContext.Web;
List myListArchivos = myWeb.Lists.GetByTitle(bibliotecaDocumentoSP);
ListItemCollection listItems = myListArchivos.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(listItems);
clientContext.ExecuteQuery();
#region CREAR ARCHIVO EXCEL
/////////////////////
//////CREAR ARCHIVO EXCEL
//listItems.ToList().ForEach(item =>
//{
// dtArchivoExcel.Rows.Add(item["Area"] == null ? "" : ((FieldLookupValue)item["Area"]).LookupValue,
// item["Department"] == null ? "" : ((FieldLookupValue)item["Department"]).LookupValue,
// item["DocType"] == null ? "" : ((FieldLookupValue)item["DocType"]).LookupValue,
// item["DepartmentCode"] == null ? "" : item["DepartmentCode"].ToString(),
// item["FileLeafRef"] == null ? "" : item["FileLeafRef"].ToString(),
// item["Title"] == null ? "" : item["Title"].ToString(),
// item["Cliente"] == null ? "" : ((FieldLookupValue)item["Cliente"]).LookupValue,
// item["Revision"] == null ? "" : item["Revision"].ToString(),
// item["Update"] == null ? "" : item["Update"].ToString(),
// item["Created"] == null ? "" : item["Created"].ToString(),
// item["SBU"] == null ? "" : ((FieldLookupValue)item["SBU"]).LookupValue,
// item["Modified"] == null ? "" : item["Modified"].ToString());
//});
//dsArchivoExcel.Tables.Add(dtArchivoExcel);
//ExcelLibrary.DataSetHelper.CreateWorkbook(pathArchivoExcel, dsArchivoExcel);
#endregion
//CATALOGO AREA
List myListCatalogoArea = myWeb.Lists.GetByTitle(catalogoArea);
ListItemCollection listCatalogoArea = myListCatalogoArea.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(listCatalogoArea);
clientContext.ExecuteQuery();
//CATALOGO DEPARTAMENTE
List myListCatalogoDepartamento = myWeb.Lists.GetByTitle(catalogoDepartamento);
ListItemCollection listCatalogoDepartamento = myListCatalogoDepartamento.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(listCatalogoDepartamento);
clientContext.ExecuteQuery();
//CATALOGO DOC TYPE
List myListCatalogoDocType = myWeb.Lists.GetByTitle(catalogoDocType);
ListItemCollection listCatalogoDocType = myListCatalogoDocType.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(listCatalogoDocType);
clientContext.ExecuteQuery();
//CATALOGO SBU
List myListCatalogoSBU = myWeb.Lists.GetByTitle(catalogoSBU);
ListItemCollection listCatalogoSBU = myListCatalogoSBU.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(listCatalogoSBU);
clientContext.ExecuteQuery();
//CATALOGO CLIENTE
List myListCatalogoCliente = myWeb.Lists.GetByTitle(catalogoCliente);
ListItemCollection listCatalogoCliente = myListCatalogoCliente.GetItems(CamlQuery.CreateAllItemsQuery());
clientContext.Load(listCatalogoCliente);
clientContext.ExecuteQuery();
#region ACTUALIZAR CODIGO ARCHIVO
long maxId = listItems.Max(n => Convert.ToInt64(((ListItem)n).FieldValues["ID"]));
long minId = listItems.Min(n => Convert.ToInt64(((ListItem)n).FieldValues["ID"]));
var uno = listItems.Where(n => ((ListItem)n).FieldValues["DepartmentCode"] != null).ToList();
string codigoDocumento = string.Empty;
listItems.ToList().ForEach(n =>
{
var area = ((ListItem)n).FieldValues["Area"];
var departamento = ((ListItem)n).FieldValues["Department"];
var tipoDocumento = ((ListItem)n).FieldValues["DocType"];
var id = ((ListItem)n).FieldValues["ID"];
long idc = 0;
idc = Convert.ToInt64(id) - 6525;
n["IDC"] = idc;
//n.Update();
if (area != null && departamento != null && tipoDocumento != null)
{
//clientContext.ExecuteQuery();
if (Convert.ToInt64(id) >= 1)
{
codigoDocumento = ((FieldLookupValue)area).LookupValue.ToUpper().Substring(0, 3) + "-" +
((FieldLookupValue)departamento).LookupValue.ToUpper().Substring(0, 3) + "-" +
((FieldLookupValue)tipoDocumento).LookupValue.ToUpper().Substring(0, 3) + "-" +
idc.ToString("0000");
n["DepartmentCode"] = codigoDocumento;
//n.Update();
}
}
n.Update();
if (Convert.ToInt64(id) % 100 == 0)
{
//n.Update();
clientContext.ExecuteQuery();
}
if (listItems.Count == Convert.ToInt64(id))
{
clientContext.ExecuteQuery();
}
});
clientContext.ExecuteQuery();
#endregion
#region ACTUALIZAR AREA
//ListItem AreaTot = listCatalogoArea.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains("SATELITES")).ToList().FirstOrDefault();
//var res = listItems.Where(n => n.FieldValues["Area"] != null && ((FieldLookupValue)n.FieldValues["Area"]).LookupValue.ToUpper().Trim().Contains(AreaTot["Title"].ToString().ToUpper()));
//foreach (ListItem item in res)
//{
// ListItem Area = listCatalogoArea.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains("LOCALIDADES PPG")).ToList().FirstOrDefault();
// if (Area != null)
// {
// item["Area"] = Area;
// item.Update();
// }
// else
// {
// //Area = listCatalogoArea.ToList().FirstOrDefault();
// //item["Area"] = Area;
// continue;
// }
//}
//clientContext.ExecuteQuery();
#endregion
#region ACTUALIZAR DEPARTAMENTO
//string departamento = "Powder";
//string departamentoNuevo = "Pintura en Polvo";
//ListItem DepartamentoTot = listCatalogoDepartamento.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(departamento.ToUpper())).ToList().FirstOrDefault();
//var resDep = listItems.Where(n => n.FieldValues["Department"] != null && ((FieldLookupValue)n.FieldValues["Department"]).LookupValue.ToUpper().Trim().Contains(DepartamentoTot["Title"].ToString().ToUpper()));
//foreach (ListItem item in resDep)
//{
// ListItem Departamento = listCatalogoDepartamento.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(departamentoNuevo.ToUpper())).ToList().FirstOrDefault();
// if (Departamento != null)
// {
// item["Department"] = Departamento;
// item.Update();
// }
// else
// {
// //Area = listCatalogoArea.ToList().FirstOrDefault();
// //item["Area"] = Area;
// continue;
// }
//}
//clientContext.ExecuteQuery();
#endregion
//CARGAR METADATOS A SHARE POINT
foreach (Archivo am in ListArchivo.ToList())//.Where(n => !string.IsNullOrEmpty(n.SBU) || !string.IsNullOrEmpty(n.DescripcionDocumento)))
//foreach (Archivo am in ListArchivoEncontrado.ToList())
{
try
{
ListItem item = listItems.ToArray().Where(n => ((ListItem)n).FieldValues["FileLeafRef"] != null && ((ListItem)n).FieldValues["FileLeafRef"].ToString() == am.Codigo).ToList().FirstOrDefault();
if (item != null)
{
try
{
//item.Update();
item.File.CheckOut();
clientContext.ExecuteQuery();
}
catch (Exception ex)
{
}
////item.File.UndoCheckOut();
//item["Title"] = am.Codigo;
//item["Loop"] = "Si";
////item["SBU"] = string.IsNullOrEmpty(am.SBU) ? "": am.SBU;
////item["DepartmentCode"] = am.DepartamentoCodigo;
//////item["Cliente"] = string.IsNullOrEmpty(am.DescripcionDocumento) ? "": am.DescripcionDocumento;
//item["Revision"] = am.NumeroRevision;
////item["Area"] = "";
////item["Department"] = "";
////item["DocType"] = "";
#region Catalogo Area
if (!string.IsNullOrEmpty(am.Area))
{
//ListItem Area = listCatalogoArea.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(am.Area.ToUpper().Trim())).ToList().FirstOrDefault();
ListItem Area = listCatalogoArea.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(am.Area.ToUpper().Trim())).ToList().FirstOrDefault();
if (Area != null)
{
item["Area"] = Area;
}
else
{
//Area = listCatalogoArea.ToList().FirstOrDefault();
//item["Area"] = Area;
continue;
}
}
else
{
//ListItem Area = listCatalogoArea.ToList().FirstOrDefault();
//item["Area"] = Area;
continue;
}
#endregion
//#region Catalogo Departamento
//if (!string.IsNullOrEmpty(am.Departamento))
//{
// ListItem Departamento = listCatalogoDepartamento.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(am.Departamento.ToUpper().Trim())).ToList().FirstOrDefault();
// if (Departamento != null)
// {
// item["Department"] = Departamento;
// }
// else
// {
// //Departamento = listCatalogoDepartamento.ToList().FirstOrDefault();
// //item["Department"] = Departamento;
// continue;
// }
//}
//else
//{
// //ListItem Departamento = listCatalogoDepartamento.ToList().FirstOrDefault();
// //item["Department"] = Departamento;
// continue;
//}
//#endregion
//#region Catalogo TipoDocumento
//if (!string.IsNullOrEmpty(am.TipoDocumento))
//{
// ListItem DocType = listCatalogoDocType.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(am.TipoDocumento.ToUpper().Trim())).ToList().FirstOrDefault();
// if (DocType != null)
// {
// item["DocType"] = DocType;
// }
// else
// {
// //DocType = listCatalogoDocType.ToList().FirstOrDefault();
// //item["DocType"] = DocType;
// //continue;
// item["DocType"] = null;
// }
//}
//else
//{
// //ListItem DocType = listCatalogoDocType.ToList().FirstOrDefault();
// //item["DocType"] = DocType;
// item["DocType"] = null;
//}
//#endregion
//#region SBU
//if (!string.IsNullOrEmpty(am.SBU))
//{
// ListItem SBU = listCatalogoSBU.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(am.SBU.ToUpper().Trim())).ToList().FirstOrDefault();
// if (SBU != null)
// {
// item["SBU"] = SBU;
// }
// else
// {
// item["SBU"] = null;
// }
//}
//else
//{
// item["SBU"] = null;
//}
//#endregion
//#region Catalogo Cliente
//if (!string.IsNullOrEmpty(am.Cliente))
//{
// ListItem Clientes = listCatalogoCliente.ToArray().Where(n => ((ListItem)n).FieldValues["Title"] != null && ((ListItem)n).FieldValues["Title"].ToString().ToUpper().Trim().Contains(am.Cliente.ToUpper().Trim())).ToList().FirstOrDefault();
// if (Clientes != null)
// {
// item["Cliente"] = Clientes;
// }
// else
// {
// item["Cliente"] = null;
// }
//}
//else
//{
// item["Cliente"] = null;
//}
//#endregion
//ACTUALIZAR INFORMACION DE METADATOS
//item.File.UndoCheckOut();
item.Update();
//clientContext.ExecuteQuery();
//REALIZAR CHECKOUT PARA TOMAR LOS ARCHIVOS
//item.File.CheckOut();
//clientContext.ExecuteQuery();
//REALIZAR CHECKIN DE LOS ARCHIVOS
item.File.CheckIn("", CheckinType.OverwriteCheckIn);
clientContext.ExecuteQuery();
dtArchivoExcel.Rows.Add(string.IsNullOrEmpty(am.Area) ? " ": am.Area,
string.IsNullOrEmpty(am.Departamento) ? " ": am.Departamento,
string.IsNullOrEmpty(am.TipoDocumento) ? " ": am.TipoDocumento,
string.IsNullOrEmpty(am.DepartamentoCodigo) ? " " : am.DepartamentoCodigo,
string.IsNullOrEmpty(am.Codigo) ? " ": am.Codigo,
string.IsNullOrEmpty(am.NombreDocumento) ? " ": am.NombreDocumento,
string.IsNullOrEmpty(am.DescripcionDocumento) ? " " : am.DescripcionDocumento,
string.IsNullOrEmpty(am.NumeroRevision) ? " ": am.NumeroRevision,
string.IsNullOrEmpty(am.FCambioFijo) ? " ": am.FCambioFijo,
string.IsNullOrEmpty(am.FCambioFrecuente) ? " ": am.FCambioFrecuente,
string.IsNullOrEmpty(am.SBU) ? " " : am.SBU,
string.IsNullOrEmpty(am.Cliente) ? " " : am.Cliente);
//if(dtArchivoExcel.Rows.Count % 200 == 0)
//{
//clientContext.ExecuteQuery();
//}
}
}
catch (Exception ex)
{
}
}
//clientContext.ExecuteQuery();
//CREAR ARCHIVO EXCEL
dsArchivoExcel.Tables.Add(dtArchivoExcel);
ExcelLibrary.DataSetHelper.CreateWorkbook(pathArchivoExcel, dsArchivoExcel);
////EXPORTAR CATALOGOS
////AREA
//dtArea.Columns.Add("Title");
//dtArea.Columns.Add("Code");
//foreach(ListItem item in listCatalogoArea)
//{
// dtArea.Rows.Add(item["Title"], item["Code"]);
//}
////DEPARTAMENTO
//dtDepartamento.Columns.Add("Title");
//dtDepartamento.Columns.Add("Area");
//dtDepartamento.Columns.Add("Code");
//foreach (ListItem item in listCatalogoDepartamento)
//{
// dtDepartamento.Rows.Add(item["Title"], ((FieldLookupValue)item["Area"]).LookupValue, item["b8ph"]);
//}
////DOC TYPE
//dtDocType.Columns.Add("Title");
//foreach (ListItem item in listCatalogoDocType)
//{
// dtDocType.Rows.Add(item["Title"]);
//}
//dsCatalogos.Tables.Add(dtArea);
//dsCatalogos.Tables.Add(dtDepartamento);
//dsCatalogos.Tables.Add(dtDocType);
//ExcelLibrary.DataSetHelper.CreateWorkbook(pathArchivoExcel, dsCatalogos);
}
catch (Exception ex)
{
Console.WriteLine("Mensaje: " + ex.Message + ", Source: " + ex.Source + ", StackTrace: " + ex.StackTrace);
}
}
public UpdatePipelineList(string clientContextWeb, string backupListTarget, string excelIndexTarget, string sharepointIndexTarget)
{
System.IO.Directory.CreateDirectory(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @"\Export\");
string folder = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @"\Export\";
string filter = "*.xlsx";
string[] files = Directory.GetFiles(folder, filter);
string pipelinefile = "Pipeline.xlsx";
string dhcfile = "DHCUpdate.xlsx";
Regex regexPipeline = FindFilesPatternToRegex.Convert("*pipeline*.xlsx");
Regex regexDHC = FindFilesPatternToRegex.Convert("*dhc*.xlsx");
foreach (string file in files)
{
//Console.WriteLine("Inside File check: {0}", file);
if (regexPipeline.IsMatch(file.ToLower()))
{
pipelinefile = file;
}
else if (regexDHC.IsMatch(file.ToLower()))
{
dhcfile = file;
}
}
Console.WriteLine("------Update Pipeline ----------------");
//Console.WriteLine("Folder : {0}", folder);
Console.WriteLine("Pipelinefile: {0}", pipelinefile);
Console.WriteLine("DHCfile : {0}", dhcfile);
Console.WriteLine("--------------------------------------");
log.Debug(string.Format("------ Update Pipeline Files ------"));
log.Debug(string.Format("Pipelinefile: {0}", pipelinefile));
log.Debug(string.Format("DHCfile : {0}", dhcfile));
log.Debug(string.Format("---------------------------------------"));
FileStream stream, streamDHC;
try
{
//update for reading files
stream = System.IO.File.Open(pipelinefile, FileMode.Open, FileAccess.Read);
//update for reading files
streamDHC = System.IO.File.Open(dhcfile, FileMode.Open, FileAccess.Read);
}
catch (Exception ex)
{
Console.WriteLine("Please close the excel file and press enter");
Console.ReadLine();
//update for reading files
stream = System.IO.File.Open(pipelinefile, FileMode.Open, FileAccess.Read);
//update for reading files
streamDHC = System.IO.File.Open(dhcfile, FileMode.Open, FileAccess.Read);
}
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet ds = reader.AsDataSet();
IExcelDataReader readerDHC = ExcelReaderFactory.CreateOpenXmlReader(streamDHC);
readerDHC.IsFirstRowAsColumnNames = true;
DataSet dsDHC = readerDHC.AsDataSet();
DataRowSharepointMappingCollection mapping = MyRetriever.GetTheCollection();
DataRowSharepointMappingCollection mappingDHC = MyRetriever.GetTheCollection("DataRowDHCMappingsSection");
DataTable dt = ds.Tables[0];
DataColumn dcParent = dt.Columns["Opportunity Name"];
using (var clientContext = new ClientContext(clientContextWeb))
{
Web web = clientContext.Web;
//------------------------------------
// GetItems for PipeLine list
//------------------------------------
List oldList = web.Lists.GetByTitle(backupListTarget);
CamlQuery query = CamlQuery.CreateAllItemsQuery(2000);
ListItemCollection oldItems = oldList.GetItems(query);
clientContext.Load(oldItems);
var listFields = oldList.Fields;
clientContext.Load(listFields, fields => fields.Include(field => field.Title, field => field.InternalName, field => field.ReadOnlyField, field => field.StaticName));
clientContext.ExecuteQuery();
//--------------------------------------------------
// GetItems from LOB (LineOfBusiness list here.....
//--------------------------------------------------
List LOBList = web.Lists.GetByTitle("LOB-MPP-Map");
CamlQuery LOBquery = CamlQuery.CreateAllItemsQuery(1000);
ListItemCollection LOBitems = LOBList.GetItems(LOBquery);
clientContext.Load(LOBitems);
var LOBFields = LOBList.Fields;
clientContext.Load(LOBFields, fields => fields.Include(field => field.Title, field => field.InternalName));
clientContext.ExecuteQuery();
//UpdateLOBFields( clientContext, oldList, oldItems, LOBitems);
// Console.WriteLine("Finished return from LOB update");
//oldList.Update();
//Console.WriteLine("Finished return from oldList update");
//clientContext.ExecuteQuery();
//-------------------------
//Stop here for now.
//-------------------------
// Console.ReadLine();
// System.Environment.Exit(0);
//log.Debug(string.Format("Opening List: {0}", backupListTarget));
//log.Debug("Internal fields");
//log.Debug("-------------------------");
// foreach (Field f in listFields)
// {
// log.Debug(string.Format("Title: {0}, Internal Name: {1}", f.Title, f.InternalName));
// log.Debug(string.Format("Static Name: {0}", f.StaticName));
// log.Debug("-----------------");
// //log.Debug(f.InternalName);
// }
foreach (DataRow dr in ds.Tables[0].Rows)
{
//log.Debug("------- Inside For -----------------");
//log.Debug(dr["HPE Opportunity Id"].ToString());
var my_itemlist = oldItems.ToList();
// ---------------BEGIN MY COMMENT SECTION --------------------------
//Console.WriteLine("Sales Opportunity Id: {0}", dr["Sales Opportunity Id"].ToString());
//Console.WriteLine(" My_itemlist count: {0}", my_itemlist.Count);
////---------MAT ----DEBUG TEST--------------------
////-- List out item list for verification ---
//// ---------------------------------------------
//if (my_itemlist.Count() == 0 )
//{
// Console.WriteLine("My List count in 0");
//}
//else
//{
// log.Debug("-- Item List ------");
// foreach (ListItem targetListItem in my_itemlist)
// {
// log.Debug(string.Format("Title: {0}, HPEOppID: {1}", targetListItem["Title"], targetListItem["HPOppID"].ToString()));
// Console.WriteLine(targetListItem["Title"]);
// }
//}
//Console.WriteLine(" -------- MAT list completed here ---------------");
// ---------------END MY COMMENT SECTION --------------------------
var page = from ListItem itemlist in oldItems.ToList()
// var page = from ListItem itemlist in my_itemlist
//where itemlist["HPOppID"].ToString() == dr["Sales Opportunity Id"].ToString()
where itemlist["HPOppID"].ToString() == dr[excelIndexTarget.ToString()].ToString()
select itemlist;
//Console.WriteLine("Page Count is: {0}", page.Count());
//this is an update
if (page.Count() == 1)
{
Console.ForegroundColor = ConsoleColor.Blue;
//Console.WriteLine(string.Format("UPDATE RECORD: Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr["Sales Opportunity Id"].ToString()));
//log.Debug(string.Format("UPDATE: Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr["Sales Opportunity Id"].ToString()));
Console.WriteLine(string.Format("UPDATE RECORD: Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));
log.Debug(string.Format("UPDATE: Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));
ListItem item = page.FirstOrDefault();
//iterate the mapping between sharepoint list items and excel spreadsheet items
foreach (DataRowSharepointMapping map in mapping)
{
UpdateField(item, map.SharePointColumn, map.DataRowColumn, dr, sharepointIndexTarget);
}
CompareSalesStage(item, dsDHC, mappingDHC, excelIndexTarget, sharepointIndexTarget);
//Console.WriteLine("- Before Item update.");
// just update the item
item.Update();
//Console.WriteLine("- Before List update.");
//update the list
oldList.Update();
countupdate++;
}
// This is a new record
//else if (page.Count() == 0 && !string.IsNullOrEmpty(dr["Sales Opportunity Id"].ToString())) ----MAT
else if (page.Count() == 0 && !string.IsNullOrEmpty(dr[excelIndexTarget.ToString()].ToString()))
{
Console.ForegroundColor = ConsoleColor.Yellow;
//Console.WriteLine(string.Format("------- Inside ELSE NEW RECORD-----------------"));
//Console.WriteLine(string.Format("NEW RECORD: Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr["Sales Opportunity Id"].ToString()));
Console.WriteLine(string.Format("NEW RECORD: Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr[excelIndexTarget.ToString()].ToString()));
//log.Debug("------- Inside ELSE NEW RECORD-----------------");
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem oListItem = oldList.AddItem(itemCreateInfo);
// -- iterate the mapping between sharepoint list items and excel spreadsheet items
foreach (DataRowSharepointMapping map in mapping)
{
UpdateField(oListItem, map.SharePointColumn, map.DataRowColumn, dr, sharepointIndexTarget);
}
CompareSalesStage(oListItem, dsDHC, mappingDHC, excelIndexTarget, sharepointIndexTarget);
// -- just update the item
//Console.WriteLine("- Before Item update.");
oListItem.Update();
// -- update the list
//Console.WriteLine("- Before List update.");
oldList.Update();
countnew++;
}
else
{
//Console.ForegroundColor = ConsoleColor.Red;
//Console.WriteLine("ERROR");
}
// Not sure about this one. (MAT)
clientContext.ExecuteQuery();
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(string.Format("We updated: {0} records and we added {1} records", countupdate.ToString(), countnew.ToString()));
log.Debug(string.Format("------------------------------------------------------------"));
log.Debug(string.Format("We updated: {0} records and we added {1} records", countupdate.ToString(), countnew.ToString()));
Console.WriteLine("Completed first set of updates. \n");
Console.WriteLine("Starting LOB checks........ \n");
log.Debug(string.Format("------------------------------------------------------------"));
log.Debug(string.Format("Starting LOB Checks........"));
UpdateLOBFields(clientContext, oldList, oldItems, LOBitems, sharepointIndexTarget);
//Console.WriteLine("Finished return from LOB update...");
//oldList.Update();
clientContext.ExecuteQuery();
Console.WriteLine("Finished Line Of Business updates... \n");
}
}
public UpdatePipelineList(string clientContextWeb, string backupListTarget)
{
System.IO.Directory.CreateDirectory(Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @"\Export\");
string folder = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location) + @"\Export\";
string filter = "*.xlsx";
string[] files = Directory.GetFiles(folder, filter);
string pipelinefile = "Pipeline.xlsx";
string dhcfile = "DHCUpdate.xlsx";
Regex regexPipeline = FindFilesPatternToRegex.Convert("*pipeline*.xlsx");
Regex regexDHC = FindFilesPatternToRegex.Convert("*dhc*.xlsx");
foreach (string file in files)
{
if (regexPipeline.IsMatch(file.ToLower()))
{
pipelinefile = file;
}
else if (regexDHC.IsMatch(file.ToLower()))
{
dhcfile = file;
}
}
FileStream stream, streamDHC;
try
{
//update for reading files
stream = System.IO.File.Open(pipelinefile, FileMode.Open, FileAccess.Read);
//update for reading files
streamDHC = System.IO.File.Open(dhcfile, FileMode.Open, FileAccess.Read);
}
catch (Exception ex)
{
Console.WriteLine("Please close the excel file and press enter");
Console.ReadLine();
//update for reading files
stream = System.IO.File.Open(pipelinefile, FileMode.Open, FileAccess.Read);
//update for reading files
streamDHC = System.IO.File.Open(dhcfile, FileMode.Open, FileAccess.Read);
}
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet ds = reader.AsDataSet();
IExcelDataReader readerDHC = ExcelReaderFactory.CreateOpenXmlReader(streamDHC);
readerDHC.IsFirstRowAsColumnNames = true;
DataSet dsDHC = readerDHC.AsDataSet();
DataRowSharepointMappingCollection mapping = MyRetriever.GetTheCollection();
DataRowSharepointMappingCollection mappingDHC = MyRetriever.GetTheCollection("DataRowDHCMappingsSection");
DataTable dt = ds.Tables[0];
DataColumn dcParent = dt.Columns["Opportunity Name"];
using (var clientContext = new ClientContext(clientContextWeb))
{
Web web = clientContext.Web;
List oldList = web.Lists.GetByTitle(backupListTarget);
CamlQuery query = CamlQuery.CreateAllItemsQuery(2000);
ListItemCollection oldItems = oldList.GetItems(query);
clientContext.Load(oldItems);
var listFields = oldList.Fields;
clientContext.Load(listFields, fields => fields.Include(field => field.Title, field => field.InternalName, field => field.ReadOnlyField));
clientContext.ExecuteQuery();
/* foreach (Field f in listFields)
* {
* log.Debug(f.InternalName);
* }*/
foreach (DataRow dr in ds.Tables[0].Rows)
{
var page = from ListItem itemlist in oldItems.ToList()
where itemlist["HPOppID"].ToString() == dr["HPE Opportunity Id"].ToString()
select itemlist;
//this is an update
if (page.Count() == 1)
{
Console.ForegroundColor = ConsoleColor.Blue;
Console.WriteLine(string.Format("Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr["HPE Opportunity Id"].ToString()));
ListItem item = page.FirstOrDefault();
//iterate the mapping between sharepoint list items and excel spreadsheet items
foreach (DataRowSharepointMapping map in mapping)
{
UpdateField(item, map.SharePointColumn, map.DataRowColumn, dr);
}
CompareSalesStage(item, dsDHC, mappingDHC);
// just update the item
item.Update();
//update the list
oldList.Update();
countupdate++;
}
// This is a new record
else if (page.Count() == 0 && !string.IsNullOrEmpty(dr["HPE Opportunity Id"].ToString()))
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine(string.Format("Name:{0} ID:{1}", dr["Opportunity Name"].ToString(), dr["HPE Opportunity Id"].ToString()));
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem oListItem = oldList.AddItem(itemCreateInfo);
//iterate the mapping between sharepoint list items and excel spreadsheet items
foreach (DataRowSharepointMapping map in mapping)
{
UpdateField(oListItem, map.SharePointColumn, map.DataRowColumn, dr);
}
CompareSalesStage(oListItem, dsDHC, mappingDHC);
// just update the item
oListItem.Update();
//update the list
oldList.Update();
countnew++;
}
else
{
//Console.ForegroundColor = ConsoleColor.Red;
//Console.WriteLine("ERROR");
}
clientContext.ExecuteQuery();
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(string.Format("We updated: {0} records and we added {1} records", countupdate.ToString(), countnew.ToString()));
}
}
