public static void CompareSalesStage(ListItem dr, DataSet dsDHC, DataRowSharepointMappingCollection mappingDHC)
{
//query sharepoint for the record we are updateing by hpoppid
var result = (from myRow in dsDHC.Tables[0].AsEnumerable()
where myRow["HPE Opportunity Id"].ToString() == dr["HPOppID"].ToString()
select myRow).FirstOrDefault();
foreach (DataRowSharepointMapping map in mappingDHC)
{
if (result != null && result[map.DataRowColumn] != null)
{
dr[map.SharePointColumn] = result[map.DataRowColumn];
}
}
Console.ForegroundColor = ConsoleColor.Gray;
Console.WriteLine(string.Format("Updating DHC Name:{0} ID:{1}", dr["Title"].ToString(), dr["HPOppID"].ToString()));;
}
public static void CompareSalesStage(ListItem dr, DataSet dsDHC, DataRowSharepointMappingCollection mappingDHC, string excelIndexColumn, string sharepointIndexColumn)
{
//query sharepoint for the record we are updateing by hpoppid
//Console.WriteLine("Inside CompareSales");
//Console.WriteLine("{0}", dr["HPOppID"].ToString());
var result = (from myRow in dsDHC.Tables[0].AsEnumerable()
//where myRow["HPE Opportunity Id"].ToString() == dr["HPOppID"].ToString()
//where myRow["Sales Opportunity Id"].ToString() == dr["HPOppID"].ToString()
where myRow[excelIndexColumn.ToString()].ToString() == dr["HPOppID"].ToString()
select myRow).FirstOrDefault();
foreach (DataRowSharepointMapping map in mappingDHC)
{
//Console.WriteLine("Mapping: {0}", map.DataRowColumn);
//if (map.SharePointColumn != "OppID")
if (map.SharePointColumn != sharepointIndexColumn.ToString())
{
if (result != null && result[map.DataRowColumn] != null)
{
//Console.WriteLine("Inside assignment Mapping: {0}", result[map.DataRowColumn]);
dr[map.SharePointColumn] = result[map.DataRowColumn];
}
}
else
{
if (result != null && result[map.DataRowColumn] != null)
{
//Console.WriteLine("Inside (RE)assignment Mapping: {0}", result[map.DataRowColumn]);
dr["HPOppID"] = result[map.DataRowColumn];
}
}
}
Console.ForegroundColor = ConsoleColor.Gray;
//Console.WriteLine(string.Format("Updating DHC Name:{0} ID:{1}", dr["Title"].ToString(), dr["HPOppID"].ToString())); ;
Console.WriteLine(string.Format("UPDATE DHC RECORD: {0} ID:{1}", dr["Title"].ToString(), dr["HPOppID"].ToString()));;
}
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()));
}
}