コード例 #1
0
        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()));;
        }
コード例 #2
0
        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()));;
        }
コード例 #3
0
        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");
            }
        }
コード例 #4
0
        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()));
            }
        }