Example #1
0
        public static ExelSheet[] LoadSheets(Workbook workbook, int count = 0, Action <int> progressReport = null, bool deleteEmptyRows = true)
        {
            var result = new List <ExelSheet>();

            //var totalRowsCount = 0;
            //var loaded = 0;
            //totalRowsCount = workbook.Worksheets.Cast<Worksheet>().Select(i => i.Cells.Rows.Count).Sum();

            Helpers.PercentageProgress prg = new Helpers.PercentageProgress();
            prg.Change += (s, e) =>
            {
                if (progressReport != null)
                {
                    progressReport((int)e.Value);
                }
            };

            foreach (var sheetItem in
                     workbook
                     .Worksheets
                     .Cast <Worksheet>()
                     .Where(s => s.Cells.Rows.Count > 1)
                     .Select(s => new
            {
                Sheet = s,
                ProgressInfo = prg.GetChild()
            })
                     .ToArray()
                     )
            {
                ExelSheet sht = new ExelSheet()
                {
                    Name = sheetItem.Sheet.Name
                };

                sht.Rows.AddRange(
                    LoadRows(sheetItem.Sheet, count, new Action <int>((i) => { sheetItem.ProgressInfo.Value = i; }), deleteEmptyRows)
                    );
                if (sht.Rows.Count > 0)
                {
                    result.Add(sht);
                }
            }

            return(result.ToArray());
        }
Example #2
0
        /// <summary>
        /// Поиск в соответствии со входными параметрами (Адрес и город) улиц и прочей информации
        /// </summary>
        /// <param name="incomingData">Входные параметры (Адрес и город)</param>
        /// <returns>Соответствующие выходные параметры (Найденные и созданные улицы и прочее)</returns>
        public IDictionary<AddressParserIncomingParameter, AddressParserResult> Parse(IEnumerable<AddressParserIncomingParameter> incomingData, bool doNotAddAnyDataToDictionary = false, Action<decimal> reportProgress = null, Action<string> verboseLog = null)
        {
            if (incomingData == null)
                throw new ArgumentNullException(nameof(incomingData));

            var log = new Action<string>((str) => { if (verboseLog != null) verboseLog($"{GetType().Name}.{nameof(Parse)}() {str}"); });

            log($"Incoming data length: {incomingData.Count()}");

            var pp = new Helpers.PercentageProgress();
            pp.Change += (s, e) => { if (reportProgress != null) reportProgress(e.Value); };

            var res = incomingData
                .GroupBy(i => i.City)
                .Select(g => new
                {
                    City = g.Key,
                    Items = g.ToArray(),
                    Progress = pp.GetChild()
                })
                .ToArray()
                //.AsParallel()
                .Select(g => new
                {
                    g.City,
                    Streets = GetStreets(g.Items.Select(i => i.Address), g.City, doNotAddAnyDataToDictionary, (progress) => g.Progress.Value = progress, str => log(str)),
                    g.Items,
                })
                .SelectMany(g => g.Items.Join(g.Streets, r => r.Address, i => i.Key, (r, i) => new
                {
                    IncomingParameters = r,
                    Data = new
                    {
                        Address = i.Key,
                        Street = i.Value
                    }
                }))
                .ToDictionary(item => item.IncomingParameters, item => new AddressParserResult()
                {
                    Address = item.Data.Address,
                    Street = item.Data.Street
                });

            return res;
        }
Example #3
0
        public static BackgroundWorker Start(string fileName, long currentOperatorId, IEnumerable<SheetRulePair> ruleToSheets)
        {
            BackgroundWorker result = new BackgroundWorker();
            result.DoWork += (s, e) =>
                {
                    StringBuilder errors = new StringBuilder();

                    bool showLogAnytime = false;

                    BackgroundWorker current = s as BackgroundWorker;
                    PercentageProgress fullProgress = new PercentageProgress();

                    var readRulesProgress = fullProgress.GetChild();
                    var getLinksProgress = fullProgress.GetChild();
                    var writeExcelFileProgress = fullProgress.GetChild();

                    fullProgress.Change += (s2, args) => { current.ReportProgress((int)args.Value); };

                    ObservableCollection<OutputRow> rowsToExport = new ObservableCollection<OutputRow>();

                    bool wasException = false;
                    var logSession = Helpers.Old.Log.SessionStart("ReExport.ReExport()", true);
                    try
                    {
                        #region Read Rules

                        Helpers.Old.Log.Add(logSession, string.Format("total sheets count: '{0}'", ruleToSheets.Count()));
                        int rulesToSheets = ruleToSheets.Count();

                        int ind = 0;
                        foreach (var item in ruleToSheets)
                        {
                            var mappingRule = item.Rule;
                            var ds = item.Sheet;
                            if (mappingRule != null && ds != null)
                            {
                                if (ds.MainHeader == null)
                                {
                                    Helpers.Old.Log.Add(logSession, string.Format("should update main header row..."));

                                    ds.UpdateMainHeaderRow(mappingRule.MainHeaderSearchTags
                                        .Select(h => h.Tag)
                                        .Union(SettingsProvider.CurrentSettings.HeaderSearchTags.Split(new char[] { ',' }))
                                        .Select(i => i.Trim())
                                        .Where(i => !string.IsNullOrEmpty(i))
                                        .Distinct()
                                        .ToArray());

                                    ds.UpdateHeaders(mappingRule.SheetHeadersSearchTags
                                        .Select(h => h.Tag.Trim())
                                        .Where(i => !string.IsNullOrEmpty(i))
                                        .Distinct()
                                        .ToArray());
                                }

                                var oc = new ObservableCollection<OutputRow>(mappingRule.Convert(ds, new string[] { "Code" }));
                                Helpers.Old.Log.Add(logSession, string.Format("row count on sheet '{0}' : '{1}'", ds.Name, oc.Count));
                                rowsToExport = new ObservableCollection<OutputRow>(rowsToExport.Union(oc));
                                Helpers.Old.Log.Add(logSession, string.Format("subtotal row count on sheets: '{0}'", rowsToExport.Count));
                            }

                            ind++;
                            readRulesProgress.Value = ((decimal)ind / (decimal)rulesToSheets) * 100m;
                        }
                        Helpers.Old.Log.Add(logSession, string.Format("total row count to export: '{0}'", rowsToExport.Count));

                        #endregion
                        #region Get Links
                        Helpers.Old.Log.Add(logSession, string.Format("Try to get links..."));

                        var idsToGet =
                            rowsToExport
                            .Select(i => new ReExportData(i.Code.Trim(), i.OriginalIndex, i.OriginalSheet))
                            .Cast<ReExportData>()
                            .ToList();

                        string outerMap;
                        string outerPdf;

                        try
                        {
                            HttpDataClient.Default.GetResourcesList(currentOperatorId, idsToGet, out outerMap, out outerPdf);
                        }
                        catch(Exception ex)
                        {
                            throw new Exception(string.Format("Ошибка при получении ссылок для кодов (кол-во: {0})", idsToGet.Count), ex);
                        }
                        //Log.Add(logSession, string.Format("Get CODES from server..."));
                        //foreach (var i in idsToGet)
                        //    Log.Add(logSession, string.Format("[Code:'{0}',Location:'{1}',Map:'{2}',Photo:'{3}']", i.Code, i.LinkLocation, i.LinkMap, i.LinkPhoto));
                        //Log.Add(logSession, string.Format("Get CODES done"));

                        //HttpDataAccess.GetResourcesList(currentOperatorId, idsToGet, out outerMap, out outerPdf);
                        Helpers.Old.Log.Add(logSession, string.Format("Links getted"));
                        getLinksProgress.Value = 100;
                        #endregion
                        #region Write Excel File

                        Workbook wb = new Workbook(fileName);
                        foreach (var r in ruleToSheets.Where(r => r.Sheet != null))
                        {
                            var sheet = wb.Worksheets.Cast<Worksheet>().FirstOrDefault(sht => sht.Name.ToLower().Trim() == r.Sheet.Name.ToLower().Trim());
                            if (sheet != null)
                            {
                                int CodeColumnIndex = -1;
                                string CodeColumnName = string.Empty;

                                var convData = r.Rule.ConvertionData.FirstOrDefault(cd => cd.PropertyId == "Code" );
                                if (convData != null)
                                {
                                    var block = convData.Blocks.Blocks.FirstOrDefault();
                                    if (block != null)
                                    {
                                        var func = block.UsedFunctions.FirstOrDefault();
                                        if (func != null)
                                        {
                                            CodeColumnName = func.Function.ColumnName;
                                            if (string.IsNullOrWhiteSpace(func.Function.ColumnName) || func.Function.SelectedParameter == Core.Converter.Functions.FunctionParameters.CellNumber)
                                                CodeColumnIndex = func.Function.ColumnNumber;
                                        }
                                    }
                                }

                                if (!string.IsNullOrWhiteSpace(CodeColumnName) || (CodeColumnIndex != -1))
                                {
                                    if (CodeColumnIndex == -1)
                                        CodeColumnIndex = r.Sheet.MainHeader.Cells.FirstOrDefault(c => string.Compare(c.Value, CodeColumnName, true) == 0)?.OriginalIndex ?? -1;

                                    #region Add cells to rows
                                    if (CodeColumnIndex >= 0)
                                    {
                                        int lastIndex = sheet.Cells.Rows.Cast<Row>().Select(c =>
                                            {
                                                if (c.LastCell != null)
                                                    for (int i = c.LastCell.Column; i >= 0; i--)
                                                    {
                                                        var cell = c.GetCellOrNull(i);
                                                        if (!string.IsNullOrWhiteSpace(
                                                                    cell == null || cell.Value == null
                                                                    ? null
                                                                    : cell.Value.ToString().Trim()
                                                                )
                                                            )
                                                            return cell.IsMerged ? cell.GetMergedRange().FirstColumn + cell.GetMergedRange().ColumnCount - 1 : i;
                                                    }
                                                return 0;
                                            }
                                        )
                                        .Union(new int[] { 0 })
                                        .Max();

                                        var h0 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 0];
                                        var h1 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 1];
                                        var h2 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 2];
                                        var h3 = sheet.Cells[r.Sheet.MainHeader.Index, lastIndex + 3];

                                        sheet.Cells.Columns[h1.Column].IsHidden = false;
                                        sheet.Cells.Columns[h2.Column].IsHidden = false;
                                        sheet.Cells.Columns[h3.Column].IsHidden = false;

                                        h1.Value = "Фото";
                                        h2.Value = "Схема";
                                        h3.Value = "Карта";

                                        Style h0Style = h0.GetStyle();

                                        h1.SetStyle(h0Style);
                                        h2.SetStyle(h0Style);
                                        h3.SetStyle(h0Style);

                                        foreach (var item in idsToGet.Where(i => i.OriginalSheet == sheet.Name))
                                        {
                                            if (!string.IsNullOrWhiteSpace(item.LinkPhoto))
                                            {
                                                var c1 = sheet.Cells[item.OriginalIndex, lastIndex + 1];
                                                c1.Value = "фото";
                                                sheet.Hyperlinks.Add(c1.Name, 1, 1, item.LinkPhoto);
                                            }
                                            if (!string.IsNullOrWhiteSpace(item.LinkLocation))
                                            {
                                                var c2 = sheet.Cells[item.OriginalIndex, lastIndex + 2];
                                                c2.Value = "схема";
                                                sheet.Hyperlinks.Add(c2.Name, 1, 1, item.LinkLocation);
                                            }
                                            if (!string.IsNullOrWhiteSpace(item.LinkMap))
                                            {
                                                var c3 = sheet.Cells[item.OriginalIndex, lastIndex + 3];
                                                c3.Value = "карта";
                                                sheet.Hyperlinks.Add(c3.Name, 1, 1, item.LinkMap);
                                            }
                                        }

                                        if (sheet.Cells.LastCell != null)
                                        {
                                            int lastRowIndex = sheet.Cells.LastCell.Row;

                                            int rowAdd = 3;

                                            if (outerPdf != null)
                                            {
                                                var outer0 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 0];
                                                var outer1 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 1];

                                                outer0.Value = "Скачать оффлайн PDF-презентацию";
                                                outer1.Value = outerPdf;
                                                sheet.Hyperlinks.Add(outer0.Name, 1, 1, outerPdf);

                                                rowAdd++;
                                            }

                                            if (outerMap != null)
                                            {
                                                var outer0 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 0];
                                                var outer1 = sheet.Cells[lastRowIndex + rowAdd, CodeColumnIndex + 1];

                                                outer0.Value = "Карта";
                                                outer1.Value = outerMap;
                                                sheet.Hyperlinks.Add(outer0.Name, 1, 1, outerMap);

                                                rowAdd++;
                                            }

                                        }

                                    }
                                    else
                                    {
                                        errors.AppendFormat("в Excel-файле на вкладке '{0}' невозможно найти колонку с именем '{1}'", sheet.Name, CodeColumnName);
                                        errors.AppendLine();
                                        //throw new Exception(string.Format("в Excel-файле на вкладке '{0}' невозможно найти колонку с кодом '{1}'", sheet.Name, CodeColumnName));
                                    }
                                    #endregion
                                }
                                else
                                {
                                    errors.AppendFormat("Отсутствует правило для определения кода для вкладки '{0}'", sheet.Name);
                                    errors.AppendLine();
                                    //throw new Exception(string.Format("Отсутствует правило для определения кода для вкладки '{0}'", sheet.Name));
                                }
                            }
                        }
                        wb.Save(fileName);
                        e.Result = errors.ToString();

                        #endregion
                    }
                    catch (Exception ex)
                    {
                        wasException = true;
                        Helpers.Old.Log.Add(logSession, ex);
                        throw ex;
                    }
                    finally
                    {
                        current.ReportProgress(100);
                        Helpers.Old.Log.SessionEnd(logSession, wasException || showLogAnytime);
                    }
                };
            result.WorkerReportsProgress = true;
            result.WorkerSupportsCancellation = true;

            return result;
        }
Example #4
0
        private void UpdateStep(int step, bool action, bool clear = false, byte insideThreadCount = 3)
        {
            if (clear)
                UrlsToAddList.Clear();

            #region step 1
            if (step == 1 && action)
            {
                object lockAdd = new Object();
                UrlsToAddList.Clear();

#if DEBUG
                insideThreadCount = 1;
#else
                insideThreadCount = ThreadCount;
#endif

                ParseRuleConnectionType type = NewParseRule.Connection;

                int minWidth = NewParseRule.MinImageWidth;
                int minHeight = NewParseRule.MinImageHeight;
                bool collectIMGTags = NewParseRule.CollectIMGTags;
                bool collectLINKTags = NewParseRule.CollectLINKTags;
                bool collectMETATags = NewParseRule.CollectMETATags;
                byte threadCount = this.ThreadCount;

                BackgroundWorker bw = new BackgroundWorker();
                bw.DoWork += (s, e) =>
                    {
                        Helpers.PercentageProgress progress = new Helpers.PercentageProgress();
                        progress.Change += (sP, eP) =>
                            {
                                bw.ReportProgress((int)eP.Value);
                            };

                        List<UrlResultWrapper> urlResultWrapper = new List<UrlResultWrapper>();
                        var urls = e.Argument as StringUrlWithResultWrapper[];

                        if (urls != null)
                            urls
                                .Where(item => item != null && !string.IsNullOrWhiteSpace(item.Value) && Helper.IsWellFormedUriString(item.Value, UriKind.Absolute))
                                .Select(sw => 
                                    new 
                                        { 
                                            item = sw,
                                            prgItem = progress.GetChild() 
                                        })
                                .ToArray()
                                .AsParallel()
                                .WithDegreeOfParallelism(insideThreadCount)
                                .ForAll(
                                    (sw) =>
                                    {
                                        var item = new UrlResultWrapper() { Value = sw.item.Value };

                                        System.Drawing.Size minSize = new System.Drawing.Size() { Width = minWidth, Height = minHeight };

                                        var result = Helper.GetAllImagesFromUrl(item.Value, minSize, collectIMGTags, collectLINKTags, collectMETATags, threadCount, sw.prgItem, true, type);

                                        foreach (ParseImageResult res in result)
                                            item.ParseResult.Add(res);

                                        if (item.ParseResult.Count > 0)
                                            lock (lockAdd)
                                            {
                                                urlResultWrapper.Add(item);
                                            }
                                    });

                        e.Result = urlResultWrapper;
                    };
                bw.RunWorkerCompleted += (s, e) =>
                    {
                        if (e.Error != null)
                            throw e.Error;

                        try
                        {
                            List<UrlResultWrapper> urlResultWrapper = e.Result as List<UrlResultWrapper>;
                            foreach (var item in urlResultWrapper)
                            {
                                if (item.ParseResult != null)
                                    foreach (var ps in item.ParseResult)
                                        ps.IsSelected = (item.ParseResult.IndexOf(ps) == 0);
                                
                                UrlsToAddList.Add(item);
                            }
                        }
                        finally
                        {
                            bw.Dispose();
                            IsBusy = false;
                        }
                    };
                bw.WorkerReportsProgress = true;
                bw.ProgressChanged += (s, e) =>
                    {
                        LoadedPercent = e.ProgressPercentage;
                    };
                IsBusy = true;
                bw.RunWorkerAsync(Urls.ToArray());
                while (bw.IsBusy)
                    Helper.DoEvents();
                bw = null;
            }
#endregion
#region step 2
            else if (step == 2 && action)
            {
                HtmlNodeWithUrl[] nodes =
                    UrlsToAddList
                    .Where(n => !string.IsNullOrWhiteSpace(n.Value))
                    .Select(i => 
                        {
                            ParseImageResult res = i.ParseResult.Where(i2 => i2.IsSelected).FirstOrDefault();
                            return
                                new HtmlNodeWithUrl()
                                {
                                    Node = res == null ? null : res.Node,
                                    Url = res == null ? new Uri(i.Value, UriKind.RelativeOrAbsolute) : res.Url
                                };
                        }
                    )
                    .Where(i3 => i3 != null && i3.Node != null)
                    .ToArray();

                ParseRule newRule = Helper.GetRule(nodes, NewParseRule.Label, NewParseRule.MinImageSize, NewParseRule.CollectIMGTags, NewParseRule.CollectLINKTags, NewParseRule.CollectMETATags);
                newRule.CopyObject(NewParseRule, new string[] { "Connection" });

                ShowRuleModeCommand.Execute(null);
            }
#endregion

            if (step >= UrlsToAddTabControl.Items.Count)
                for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--)
                    (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == UrlsToAddTabControl.Items.Count - 1) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed;
            else if (step < 0)
                for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--)
                    (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == 0) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed;
            else
            { 
                for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--)
                    (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == step) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed;
            }

            UrlsToAddTabControl.SelectedIndex = UrlsToAddTabControl.Items.IndexOf(UrlsToAddTabControl.Items.Cast<TabItem>().FirstOrDefault(ti => ti.Visibility == System.Windows.Visibility.Visible));
        }
Example #5
0
        public void Initialize()
        {
            InitializeError = string.Empty;
            if (Export2CsvCommand == null)
                Export2CsvCommand = new RelayCommand(() => Export2Csv());
            if (Export2DbCommand == null)
                Export2DbCommand = new RelayCommand(Export2Db);
            if (UpdateErrorsCommand == null)
                UpdateErrorsCommand = new RelayCommand(UpdateErrors);
            if (UpdateSelectedErrorCommand == null)
                UpdateSelectedErrorCommand = new RelayCommand(UpdateSelectedError);
            if (UpdateSelectedWarningCommand == null)
                UpdateSelectedWarningCommand = new RelayCommand(UpdateSelectedWarning);

            loadWorker = new BackgroundWorker() { WorkerReportsProgress = true, WorkerSupportsCancellation = true };
            loadWorker.DoWork += (s, prm) =>
            {
                var Disp = prm.Argument as System.Windows.Threading.Dispatcher;

                ObservableCollection<OutputRow> rowsToExport = new ObservableCollection<OutputRow>();
                Guid logSession = Helpers.Old.Log.SessionStart("ExportViewModel.Initialize()");
                try
                {
                    Log.Add(string.Format("total sheets count: '{0}'", App.Locator.Import.Document.DocumentSheets.Count));
                    var addErr = new List<Error>();
                    var addGErr = new List<GlobalError>();

                    var progress = new PercentageProgress();

                    foreach (var item in App.Locator.Import
                        .ExportRules
                        .Where(r => r.Rule != App.Locator.Import.NullRule)
                        .Select(r => new { Rule = r, Progress = progress.GetChild() })
                        .ToArray()
                        )
                    {
                        if (((BackgroundWorker)s).CancellationPending || s != loadWorker)
                            break;

                        var mappingRule = item.Rule.Rule;
                        var ds = item.Rule.Sheet;

                        if (mappingRule == null || ds == null)
                        {
                            if (!string.IsNullOrWhiteSpace(item.Rule.Status))
                                addGErr.Add(new GlobalError() { Description = item.Rule.Status });
                            item.Progress.Value = 100;
                            continue;
                        }
                        else
                        {
                            if (ds.MainHeader == null)
                            {
                                Log.Add(string.Format("should update main header row..."));

                                ds.UpdateMainHeaderRow(mappingRule.MainHeaderSearchTags
                                        .Select(h => h.Tag)
                                        .Union(SettingsProvider.CurrentSettings.HeaderSearchTags.Split(new char[] { ',' }))
                                        .Select(i => i.Trim())
                                        .Where(i => !string.IsNullOrEmpty(i))
                                        .Distinct()
                                        .ToArray());

                                ds.UpdateHeaders(mappingRule.SheetHeadersSearchTags
                                        .Select(h => h.Tag.Trim())
                                        .Where(i => !string.IsNullOrEmpty(i))
                                        .Distinct()
                                        .ToArray());
                            }

                            var oc = new ObservableCollection<OutputRow>(mappingRule.Convert(ds,
                                progressAction: (i) =>
                                {
                                    item.Progress.Value = i;
                                    ((BackgroundWorker)s).ReportProgress((int)progress.Value);
                                },
                                isCanceled: () => { return ((BackgroundWorker)s).CancellationPending; },
                                additionalErrorAction: (e, r) =>
                                {
                                    addErr.Add(new Error() { Description = e.GetExceptionText(includeStackTrace: false, clearText: true).Trim(), RowNumber = r });
                                }));
                            Log.Add(string.Format("row count on sheet '{0}' : '{1}'", ds.Name, oc.Count));
                            rowsToExport = new ObservableCollection<OutputRow>(rowsToExport.Union(oc));
                            Log.Add(string.Format("subtotal row count on sheets: '{0}'", rowsToExport.Count));
                        }
                    }

                    ExelConvertionRule.RemoveRepeatingId(rowsToExport.ToList());

                    var UrlsPhoto = new UrlCollection();
                    var UrlsSchema = new UrlCollection();
                    var UrlsAll = new UrlCollection();

                    var photos = rowsToExport.Select(r => r.Photo_img).Where(r => Helper.IsWellFormedUriString(r, UriKind.Absolute)).Distinct();
                    var schemas = rowsToExport.Select(r => r.Location_img).Where(r => Helper.IsWellFormedUriString(r, UriKind.Absolute)).Distinct();
                    var all = photos.Union(schemas).Distinct();

                    foreach (var p in photos)
                        UrlsPhoto.Add(new StringUrlWithResultWrapper(p));
                    foreach (var p in schemas)
                        UrlsSchema.Add(new StringUrlWithResultWrapper(p));
                    foreach (var p in all)
                        UrlsAll.Add(new StringUrlWithResultWrapper(p));

                    if (!((BackgroundWorker)s).CancellationPending && s == loadWorker)
                        Disp.Invoke(System.Windows.Threading.DispatcherPriority.Normal, new Action(() =>
                        {
                            UrlCollection.Clear();
                            if (UrlsPhoto.Count > 0)
                                UrlCollection.Add(new UrlCollectionAdditional() { Name = DBParsers.Labels.ElementAt(0), Collection = UrlsPhoto });
                            if (UrlsSchema.Count > 0)
                                UrlCollection.Add(new UrlCollectionAdditional() { Name = DBParsers.Labels.ElementAt(1), Collection = UrlsSchema });
                            if (UrlsPhoto.Count > 0 && UrlsSchema.Count > 0 && UrlsAll.Count > 0)
                                UrlCollection.Add(new UrlCollectionAdditional() { Name = "Все", Collection = UrlsAll });
                            UrlCollectionSelectedIndex = UrlCollection.Count - 1;
                            RowsToExport = rowsToExport;
                            UpdateErrors(addErr, addGErr);
                        }));
                    else
                        prm.Cancel = true;
                }
                catch (Exception ex)
                {
                    Helpers.Old.Log.Add(logSession, ex.GetExceptionText());
                    throw ex;
                }
                finally
                {
                    Log.Add(string.Format("total row count to export: '{0}'", rowsToExport.Count));
                    Helpers.Old.Log.SessionEnd(logSession);
                }
            };

            loadWorker.ProgressChanged += (s, e) =>
            {
                if (s == loadWorker)
                    LoadProgress = e.ProgressPercentage;
            };

            loadWorker.RunWorkerCompleted += (s, e) =>
            {
                if (s == loadWorker)
                try
                {
                    if (e.Cancelled)
                        throw new Exception("Загрузка отменена пользователем");
                    if (e.Error != null)
                        throw e.Error;
                }
                catch (Exception ex)
                {
                    InitializeError = ex.GetExceptionText();
                }
                finally
                {
                    if (s == loadWorker)
                    {
                        IsLoading = false;
                        loadWorker = null;
                    }
                }
                ((BackgroundWorker)s).Dispose();
            };

            LoadProgress = 0;
            IsLoading = true;
            CancelCommand.RaiseCanExecuteChanged();
            loadWorker.RunWorkerAsync(System.Windows.Threading.Dispatcher.CurrentDispatcher);
        }
        public static ExelRow[] LoadRows(Worksheet sheet, int count = 0, Action<int> progressReport = null, bool deleteEmptyRows = true)
        {
            var result = new List<ExelRow>();
            var totalRowsCount = 0;
            var loaded = 0;

            var pp = new Helpers.PercentageProgress();
            if (progressReport != null)
                pp.Change += (s, e) => { progressReport((int)e.Value); };

            var pp0 = pp.GetChild(weight: 9);
            var pp1 = pp.GetChild(weight: 1);

            try
            {
                totalRowsCount = Math.Max(sheet.Cells.MaxRow + 1, sheet.Cells.Rows.Count);
            }
            catch
            {
                totalRowsCount = sheet.Cells.Rows.Count;
            }

            if (count <= 0)
               count = totalRowsCount;

            #region Select max column index for sheet
            int maxColumnIndex = sheet.Cells.Rows.Cast<Row>().Take(count).Select(c =>
            {
                if (c.LastCell != null)
                    for (int i = c.LastCell.Column; i >= 0; i--)
                    {
                        var cell = c.GetCellOrNull(i);
                        if (!string.IsNullOrWhiteSpace(
                                    cell == null || cell.Value == null
                                    ? null
                                    : cell.Value.ToString().Trim()
                                )
                            )
                            return cell.IsMerged ? cell.GetMergedRange().FirstColumn + cell.GetMergedRange().ColumnCount - 1 : i;
                    }
                return 0;
            }
                )
                .Union(new int[] { 0 })
                .Max();
            #endregion
            #region Read rows

            if (sheet.Cells.Count > 0)
            {
                var lockObj = new Object();
                var lockCell = new Object();
                sheet.Cells.MultiThreadReading = true;

                var items =
                        sheet.Cells.Rows.Cast<Row>()
                            .Where(r => r.Index < count)
                            .AsParallel()
                            .Select(row =>
                            {
                                Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("ru-RU");
                                Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;

                                var r = new ExelRow() { Index = row.Index };
                                var currMaxColumnsIndex = row.LastCell == null ? 0 : row.LastCell.Column;

                                int lastFilledColumnIndex = Math.Min(currMaxColumnsIndex, maxColumnIndex);

                                #region Read data from cells

                                var cells = Enumerable.Range(0, lastFilledColumnIndex + 1)
                                    .Select(cellIndex => new { OriginalCell = row.GetCellOrNull(cellIndex), Index = cellIndex })
                                    .Select(c => new { OriginalCell = c.OriginalCell, ResultCell = new ExelCell() { Value = string.Empty, FormatedValue = string.Empty, CellStyle = new Style(), OriginalIndex = c.Index } })
                                    .Select(i =>
                                        {
                                            if (i.OriginalCell != null)
                                            {
                                                lock(lockCell)
                                                    i.ResultCell.FormatedValue = i.OriginalCell.StringValue;

                                                var link = GetHyperlinkForCell(i.OriginalCell, sheet);
                                                if (link != null)
                                                    i.ResultCell.HyperLink = link.Address;
                                                else
                                                {
                                                    var formula = string.Empty;
                                                    lock(lockCell)
                                                        formula = i.OriginalCell.Formula;

                                                    if (!string.IsNullOrWhiteSpace(formula))
                                                        i.ResultCell.HyperLink = formula.Split(new char[] { '\"' }).FirstOrDefault(str => str.Contains("http"));
                                                }

                                                if (i.OriginalCell.IsMerged)
                                                {
                                                    i.ResultCell.IsMerged = true;

                                                    var content = string.Empty;
                                                    var values = (IEnumerable)i.OriginalCell.GetMergedRange().Value;
                                                    if (values != null)
                                                        foreach (var value in values)
                                                            content += value;

                                                    i.ResultCell.Value = content;
                                                }
                                                else if (i.OriginalCell.Value != null)
                                                {
                                                    i.ResultCell.Value = i.OriginalCell.Value.ToString();
                                                }
                                                else
                                                {
                                                    i.ResultCell.Value = string.Empty;
                                                }

                                                try
                                                {
                                                    var comment = sheet.Comments[i.OriginalCell.Row, i.ResultCell.OriginalIndex];
                                                    if (comment != null)
                                                        i.ResultCell.Comment = comment.Note;
                                                }
                                                catch { }

                                                var style = i.OriginalCell.GetStyle();
                                                i.ResultCell.CellStyle = style;
                                                i.ResultCell.Color = (style != null) ? (DefColors.Any(clr => ColorsEqual(clr, style.BackgroundColor)) ? style.ForegroundColor : style.BackgroundColor) : System.Drawing.Color.White;
                                                i.ResultCell.Color = System.Drawing.Color.FromArgb((i.ResultCell.Color.R > byte.MinValue || i.ResultCell.Color.G > byte.MinValue || i.ResultCell.Color.B > byte.MinValue) && i.ResultCell.Color.A == byte.MinValue ? byte.MaxValue : i.ResultCell.Color.A, i.ResultCell.Color.R, i.ResultCell.Color.G, i.ResultCell.Color.B);
                                            }
                                            return new { ResultCell = i.ResultCell, Index = i.ResultCell.OriginalIndex };
                                        })
                                    .OrderBy(i => i.Index)
                                    .Select(i => i.ResultCell)
                                    .ToArray();
                                r.Cells.AddRange(cells);

                                #endregion

                                var lastStyle = (maxColumnIndex > 0) ? r.Cells[lastFilledColumnIndex].CellStyle : new Style();

                                for (int i = r.Cells.Count; i <= maxColumnIndex; i++)
                                    r.Cells.Add(new ExelCell() { Value = string.Empty, CellStyle = lastStyle });

                                lock (lockObj)
                                {
                                    loaded++;
                                    pp0.Value = (decimal)loaded * 100m / (decimal)count;
                                }

                                return r;
                            })
                            .OrderBy(r => r.Index)
                            .Where(r => !deleteEmptyRows || !r.IsEmpty) // delete empty rows
                            .ToArray();
                result.AddRange(items);
            }

            #endregion

            if (result.Count > 0)
            {
                #region Delete bottom

                //#### Try to delete bottom info ####
                //get last empty index

                var lastEmptyRow = result.LastOrDefault(row => row.IsEmpty);
                int lastEmptyRowsInDataIndex = lastEmptyRow == null ? 0 : result.IndexOf(lastEmptyRow);

                //int lastEmptyRowsInDataIndex =
                //    result
                //    .Where(row => row.IsEmpty)
                //    .Select(row => result.IndexOf(row))
                //    .OrderByDescending(i => i)
                //    .FirstOrDefault();

                //get last non empty index before last empty to check similarity
                var lastNotEmptyRow = result.Take(lastEmptyRowsInDataIndex == 0 ? result.Count : lastEmptyRowsInDataIndex).LastOrDefault(row => !row.IsEmpty);
                int lastNotEmptyRowsInDataIndexBeforeEmpty = lastNotEmptyRow == null ? 0 : result.IndexOf(lastNotEmptyRow);

                //int lastNotEmptyRowsInDataIndexBeforeEmpty =
                //    result
                //    .Where(row => !row.IsEmpty)
                //    .Select(row => result.IndexOf(row))
                //    .Where(i => i < lastEmptyRowsInDataIndex)
                //    .OrderByDescending(i => i)
                //    .FirstOrDefault();

                if (lastEmptyRowsInDataIndex > 4 && result.Count - lastEmptyRowsInDataIndex < 15)
                {
                    List<ExelRow> similarityIndexes = new List<ExelRow>();
                    for (int i = lastNotEmptyRowsInDataIndexBeforeEmpty; i >= 0 && i > lastNotEmptyRowsInDataIndexBeforeEmpty - 10; i--)
                        similarityIndexes.Add(result[i]);

                    for (int z = result.Count - 1; z >= lastEmptyRowsInDataIndex; z--)
                    {
                        if (result[z].NotEmptyCells.Count() <= 4 && !similarityIndexes.Select(s => s.Similarity(result[z])).Any(d => d > 0.6))
                            result.RemoveAt(z);
                        else
                            break;
                    }
                }

                pp1.Value = 25;

                var isRowsSimilar = new Func<ExelRow, ExelRow, bool>((r1, r2) =>
                {
                    var cnt = Math.Min(r1.Cells.Count, r2.Cells.Count);
                    for (int i = 0; i < cnt; i++)
                        if (string.Compare(r1.Cells[i].Value.Trim(), r2.Cells[i].Value.Trim(), true) != 0)
                            return false;
                    return true;
                });

                var defStart = 4;
                var minCountForDeleteSimilarity = Properties.Settings.Default.MaxRowsInGroupCountToDeleteSimilarityRows;
                for (int i=result.Count-1; i>= defStart; i--)
                {
                    var n = 1;
                    if ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n]))
                    {
                        var rowIndexes = new List<int>(new int[] { i - n });
                        do
                        {
                            n++;
                            rowIndexes.Add(i - n);
                        } while ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n]));

                        if (n + 1 >= minCountForDeleteSimilarity)
                        {
                            rowIndexes
                                .OrderByDescending(ind => ind)
                                .ToList()
                                .ForEach(ind => result.RemoveAt(ind));
                            i -= n;
                            continue;
                            //result.RemoveAt(i);
                        }
                    }
                }

                pp1.Value = 50;

                #endregion

                //Delete all empty rows from data
                if (deleteEmptyRows)
                    for (int z = result.Count - 1; z >= 0; z--)
                    {
                        var r1 = result[z];
                        if (r1.IsEmpty)
                            result.RemoveAt(z);
                    }

                pp1.Value = 100;
            }
            return result.ToArray();
        }
Example #7
0
        public void DataCalculator_Preview()
        {
            SqlLogEnabled = false;

            var rnd = new Random();
            rnd.Next();

            var alph = "йцукенгшщзфывапролдячсмить";

            var maxCnt = 100;
            var cityNames = Enumerable.Range(0, 3).Select(i => $"TestCity{i}").ToArray();
            var areaNames = Enumerable.Range(0, 10).Select(i => $"TestArea{i}").ToArray();
            var streetNames = Enumerable.Range(0, 100).Select(i => Enumerable.Range(0, 6).Select(n => alph[rnd.Next(alph.Length)].ToString()).Concat(c => c)).ToArray();
            var hostNames = new string[] { "testhost0.ru", "testhost1.ru" };

            try
            {
                hostNames.ToList().ForEach((h) =>
                    {
                        Rep.HostRemove(Rep.HostGet(h), saveAfterRemove: false);
                        var host = Rep.HostNew(h);
                        Rep.HostAdd(host, saveAfterInsert: false);
                    });
                Rep.SaveChanges();

                var acc = Rep.GetAccount(defAccountName, eagerLoad: new string[] { "Settings.Columns.ColumnType" });

                cityNames.ToList().ForEach((s) =>
                {
                    Rep.CityRemove(Rep.CityGet(s), saveAfterRemove: false);
                });
                Rep.SaveChanges();

                cityNames.ToList().ForEach((s) =>
                {
                    var city = Rep.CityNew(s);
                    areaNames.ToList().ForEach((a) =>
                    {
                        var area = Rep.AreaNew(a, city: city);
                        streetNames.ToList().ForEach((ss) =>
                        {
                            if (rnd.Next(5) != 0)
                                Rep.StreetNew(RoyaltyDataCalculator.Parser.Address.FromString(ss, area.Name, acc.Dictionary.Excludes.Select(e => e.Exclude)).Street, area);
                        });
                    });
                });

                Rep.SaveChanges();

                var addrCol = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Address);
                var areaCol = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Area);
                var cityCol = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.City);
                var cityHost = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Host);
                var cityPhone = acc.Settings.GetColumnFor(RoyaltyRepository.Models.ColumnTypes.Phone);

                var csvLines = new List<string>();

                var colValues = acc.Settings.Columns
                    .Where(c => c.ColumnType.ImportTableValidation)
                    .Select(c => new { Name = c.ColumnName.ToLower(), Type = c.ColumnType })
                    .ToArray();

                var columns = string.Empty;
                foreach (var colName in colValues)
                    columns += (string.IsNullOrWhiteSpace(columns) ? string.Empty : ";") + colName.Name;
                csvLines.Add(columns);

                var lastAddedPhone = string.Empty;
                var genNewData = new Func<RoyaltyRepository.Models.ColumnTypes, string>(
                    (ct) => 
                    {
                        var res = string.Empty;
                        switch(ct)
                        {
                            case RoyaltyRepository.Models.ColumnTypes.Phone:
                                if (!string.IsNullOrWhiteSpace(lastAddedPhone) && rnd.Next(0, 5) == 0)
                                    res = lastAddedPhone;
                                else
                                    for (int n = 0; n < 5; n++)
                                        res += rnd.Next(10, 99).ToString();
                                break;
                            case RoyaltyRepository.Models.ColumnTypes.Address:
                                res = streetNames.Union(new string[] { "defaultPreviewStreet2" }).ToArray()[rnd.Next(0, streetNames.Length + 1)] 
                                    + ((rnd.Next(0, 5) != 0) ? ", " + rnd.Next(1,99) : string.Empty);
                                break;
                            case RoyaltyRepository.Models.ColumnTypes.Area:
                                res = areaNames.Union(new string[] { string.Empty, "defaultPreviewArea2" }).ToArray()[rnd.Next(0, areaNames.Length + 2)];
                                break;
                            case RoyaltyRepository.Models.ColumnTypes.City:
                                res = cityNames.Union(new string[] { "defaultPreviewCity1" }).ToArray()[rnd.Next(0, cityNames.Length + 1)];
                                break;
                            case RoyaltyRepository.Models.ColumnTypes.Host:
                                res = (rnd.Next(3) == 0 ? string.Empty : @"http://") 
                                    + hostNames.Union(new string[] { "testhost2.ru" }).ToArray()[rnd.Next(0, hostNames.Length + 1)]
                                    + "/" + Guid.NewGuid().ToString("N");
                                break;
                        }
                        return res;
                    }
                    );

                for (int i = 0; i < maxCnt; i++ )
                {
                    columns = string.Empty;
                    foreach (var colName in colValues)
                        columns += (string.IsNullOrWhiteSpace(columns) ? string.Empty : ";") + genNewData(colName.Type.Type);
                    csvLines.Add(columns);
                }

                Rep.SaveChanges();

                using (var dc = new DataCalculator(acc, Rep))
                {
                    var prg0 = new Helpers.PercentageProgress();
                    var prg1 = prg0.GetChild();
                    var prg2 = prg0.GetChild();
                    var prg3 = prg0.GetChild();
                    prg0.Change += (s, e) => Helpers.Log.Add($"Progress: {e.Value.ToString("N2")}");
                    var log = new Action<string>((s) => Helpers.Log.Add(s));

                    var l = Helpers.CSV.CSVFile.Load(csvLines.AsEnumerable(),
                        tableValidator: dc.TableValidator,
                        rowFilter: dc.RowFilter);

                    dc.Prepare(l.Table, i => prg1.Value = i, log);
                    var previewRes = dc.Preview(l.Table, i => prg2.Value = i, log);
                    Assert.AreEqual(l.Table.Rows.Count, previewRes.Count());

                    var readyToExport = dc.Import(previewRes.Values, null, i => prg3.Value = i, log);
                    Rep.SaveChanges();

                    Helpers.Log.Add($"Data imported and ready to export");

                    Rep.AccountDataRecordRemove(acc.Data);
                }
            }
            finally
            {
                hostNames.ToList().ForEach((s) =>
                {
                    Rep.HostRemove(Rep.HostGet(s), saveAfterRemove: false);
                });
                cityNames.ToList().ForEach((s) =>
                {
                    Rep.CityRemove(Rep.CityGet(s), saveAfterRemove: false);
                });
                Rep.SaveChanges();
                SqlLogEnabled = true;
            }
        }
Example #8
0
        /// <summary>
        /// Получение списка улиц в соответствии с адресами
        /// </summary>
        /// <param name="incomingAddresses">Адреса, для которых нужно определить улицы</param>
        /// <param name="city">Город</param>
        /// <param name="doNotAddAnyDataToDictionary">Флаг для отмены добавления данных в словарь</param>
        /// <param name="reportProgress">Action для отслеживания процесса выполнения</param>
        /// <returns>Список соответствия входящему адресу найденной улицы</returns>
        internal IDictionary<Address, RoyaltyRepository.Models.Street> GetStreets(
            IEnumerable<Address> incomingAddresses,
            RoyaltyRepository.Models.City city,
            bool doNotAddAnyDataToDictionary = false,
            Action<decimal> reportProgress = null,
            Action<string> verboseLog = null)
        {
            var pp = new Helpers.PercentageProgress();
            var ppLoad = pp.GetChild(weight: 0.5m);
            var ppSubitem = pp.GetChild(weight: 9m);
            var ppEnd = pp.GetChild(weight: 0.5m);
            pp.Change += (s, e) => { if (reportProgress != null) reportProgress(e.Value); };
            verboseLog = verboseLog ?? new Action<string>(s => { });

            using (var logSession = Helpers.Log.Session($"{GetType().Name}.{nameof(GetStreets)}()"))
                try
                {
                    logSession.Output = new Action<IEnumerable<string>>( (strs) => strs.ToList().ForEach(s => verboseLog(s)) );

                    var findStreet = incomingAddresses
                        .Distinct()
                        .OrderByDescending(a => a.Area)
                        .ThenByDescending(a => a.Street)
                        .ThenByDescending(a => a.House.ToString())
                        .LeftOuterJoin(Account.Dictionary.Records, //.Where(r => r.Street != null && r.Street.Area != null),
                            s => new { StreetName = s.Street, AreaName = s.Area },
                            d => new { StreetName = d.Street.Name, AreaName = d.Street.Area.Name },
                            (s, d) => new
                            {
                                IncomingAddress = s,
                                Street = (d != null ? d.ChangeStreetTo ?? d.Street : null),
                                ConditionsScore = GetConditionsScore(s.House.Number, d == null ? null : d.Conditions, doNotAddAnyDataToDictionary),
                            })
                        .ToArray();

                    var count = findStreet.Length;
                    int current = 0;
                    ppLoad.Value = 90;

                    var res = findStreet
                        .Select(i =>
                        {
                            var subRes = new
                            {
                                i.IncomingAddress,
                                Street = i.Street
                                    ?? GetStreetByDictionary(i.IncomingAddress, city, doNotAddAnyDataToDictionary, (s) => logSession.Add(s))
                                    ?? GetNewStreet(i.IncomingAddress, city, doNotAddAnyDataToDictionary, (s) => logSession.Add(s))
                            };
                            current++;
                            ppSubitem.Value = (decimal)current / (decimal)count * 100m;
                            return subRes;
                        }
                        )
                        .GroupBy(i => i.IncomingAddress)
                        .Select(g => new { IncomingAddress = g.Key, Items = g })
                        //Исключаем случаи, когда в словаре более одной записи на входящий адрес (что-то пошло не так, и мы генерируем ошибку)
                        .Select(g => 
                        {
                            if (g.Items.Count() > 1)
                            {
                                var ex = new Exception($"For incoming address '{g.IncomingAddress.ToString()}' found more then 1 street. See data for details");
                                int index = 0;
                                foreach (var i in g.Items.Select(n => n.Street))
                                    ex.Data.Add(index++, i.ToString());

                                throw ex;
                            }

                            return new
                            {
                                g.IncomingAddress,
                                g.Items.FirstOrDefault().Street
                            };
                        })
                        .ToArray();

                    ppLoad.Value = 100;

                    logSession.Add($"Array constructed with {res.Length} elements for city '{city}'. Try to create dictionary.");
                    return res.ToDictionary(i => i.IncomingAddress, i => i.Street);
                }
                catch(Exception ex)
                {
                    logSession.Add(ex);
                    logSession.Enabled = true;
                    throw;
                }
                finally
                {
                    ppEnd.Value = 100;
                }
        }
Example #9
0
        /// <summary>
        /// Получить соответствия данным из загружаемой таблицы
        /// </summary>
        /// <param name="dataTable">Загружаемая таблица</param>
        /// <param name="progressAction">Действие для отображения прогресса</param>
        /// <param name="logAction">Действие для отображения лога</param>
        /// <returns>Словарь соответсвия найденных данных для каждой строки таблицы</returns>
        public IDictionary<DataRow, DataPreviewRow> Preview(DataTable dataTable, Action<decimal> progressAction = null, Action<string> logAction = null)
        {
            progressAction = progressAction ?? new Action<decimal>((i) => { });
            logAction = logAction ?? new Action<string>((i) => { });

            using (var logSession = Log.Session($"{this.GetType().Name}.{nameof(Preview)}()", VerboseLog))
                try
                {
                    logSession.Output = (strs) => strs.ToList().ForEach(s => logAction(s));

                    if (dataTable == null)
                        throw new ArgumentNullException(nameof(dataTable));

                    var pp = new Helpers.PercentageProgress();
                    var ppPrepare = pp.GetChild(weight: 0.1m);
                    var ppHostes = pp.GetChild(weight: 0.1m);
                    var ppPhones = pp.GetChild(weight: 0.1m);
                    var ppMarks = pp.GetChild(weight: 0.1m);
                    var ppCities = pp.GetChild(weight: 0.1m);
                    var ppStreets = pp.GetChild(weight: 0.7m);
                    var ppAddresses = pp.GetChild(weight: 0.15m);
                    var ppRows = pp.GetChild(weight: 0.3m);
                    pp.Change += (s, e) => progressAction(e.Value);

                    #region Get column names by column types
                    logSession.Add("Get column names by column types");

                    var columnNames = dataTable.Columns.OfType<DataColumn>().Select(c => c.ColumnName.ToUpper()).ToArray();

                    var columnByTypes = typeof(ColumnTypes)
                        .GetEnumValues()
                        .Cast<ColumnTypes>()
                        .Select(ct => new
                        {
                            Type = ct,
                            Column = Account.Settings.GetColumnFor(ct),
                        })
                        .Where(c => c.Column != null)
                        .Select(i => new
                        {
                            i.Type,
                            i.Column,
                            ExistsInDataTable = columnNames.Contains(i.Column.ColumnName.ToUpper()),
                        }).ToArray();

                    var badColumns = columnByTypes.Where(c => c.Column == null).Concat(i => RoyaltyRepository.Extensions.Extensions.GetEnumNameFromType(i.Type), ", ");
                    if (!string.IsNullOrWhiteSpace(badColumns))
                        throw new Exception(string.Format(Resources.DataCalculator_Preview_ColumnInSettingsNotSetted, badColumns));

                    var columnDict = columnByTypes.ToDictionary(i => i.Type, i => new { i.Column.ColumnName, i.ExistsInDataTable });

                    #endregion
                    #region Prepare data
                    logSession.Add("Parse incoming data");

                    var excludes = Account.Dictionary.Excludes
                        .Select(e => e.Exclude)
                        .ToArray();

                    var subRes0 = dataTable.Rows
                        .Cast<DataRow>()
                        .AsParallel()
                        .Select(dr => new
                        {
                            Row = dr,
                            IncomingAddressValue = columnDict[ColumnTypes.Address].ExistsInDataTable ? dr[columnDict[ColumnTypes.Address].ColumnName] : null,
                            IncomingHostValue = columnDict[ColumnTypes.Host].ExistsInDataTable ? dr[columnDict[ColumnTypes.Host].ColumnName] : null,
                            IncomingPhoneValue = columnDict[ColumnTypes.Phone].ExistsInDataTable ? dr[columnDict[ColumnTypes.Phone].ColumnName] : null,
                            IncomingCityValue = columnDict[ColumnTypes.City].ExistsInDataTable ? dr[columnDict[ColumnTypes.City].ColumnName] : null,
                            IncomingAreaValue = columnDict[ColumnTypes.Area].ExistsInDataTable ? dr[columnDict[ColumnTypes.Area].ColumnName] : null,
                            IncomingMarkValue = columnDict[ColumnTypes.Mark].ExistsInDataTable ? dr[columnDict[ColumnTypes.Mark].ColumnName] : null,
                        })
                        .Select(dr => new
                        {
                            dr.Row,
                            IncomingAddress = (dr.IncomingAddressValue == DBNull.Value || dr.IncomingAddressValue == null) ? string.Empty : dr.IncomingAddressValue.ToString(),
                            IncomingHost = (dr.IncomingHostValue == DBNull.Value || dr.IncomingHostValue == null) ? string.Empty : dr.IncomingHostValue.ToString(),
                            IncomingPhone = (dr.IncomingPhoneValue == DBNull.Value || dr.IncomingPhoneValue == null) ? string.Empty : dr.IncomingPhoneValue.ToString(),
                            IncomingCity = (dr.IncomingCityValue == DBNull.Value || dr.IncomingCityValue == null) ? string.Empty : dr.IncomingCityValue.ToString(),
                            IncomingArea = (dr.IncomingAreaValue == DBNull.Value || dr.IncomingAreaValue == null) ? string.Empty : dr.IncomingAreaValue.ToString(),
                            IncomingMark = (dr.IncomingMarkValue == DBNull.Value || dr.IncomingMarkValue == null) ? string.Empty : dr.IncomingMarkValue.ToString(),
                        })
                        .Select(dr => new
                        {
                            dr.Row,
                            IncomingAddress = Parser.Address.FromString(dr.IncomingAddress, dr.IncomingArea, excludes),
                            IncomingHost = Parser.Host.FromString(dr.IncomingHost),
                            IncomingPhone = Parser.Phone.FromString(dr.IncomingPhone),
                            dr.IncomingCity,
                            dr.IncomingMark,
                        })
                        .ToArray();

                    ppPrepare.Value = 100;

                    #endregion
                    #region Join hostes or create new
                    logSession.Add("Join hostes or create new");

                    var hosts = subRes0
                        .AsParallel()
                        .Select(i => i.IncomingHost.Hostname)
                        .Distinct()
                        .LeftOuterJoin(Repository.HostGet(), h => h.ToUpper(), h => h.Name.ToUpper(), (h, host) => new { HostName = h, Host = host })
                        .ToArray()
                        .Select(i => i.Host ?? Repository.HostNew(i.HostName))
                        .ToArray();

                    ppHostes.Value = 50;

                    var subRes1 = subRes0
                        .Join(hosts, i => i.IncomingHost.Hostname, h => h.Name, (i, h) => new
                        {
                            i.Row,
                            i.IncomingAddress,
                            i.IncomingCity,
                            i.IncomingPhone,
                            i.IncomingHost,
                            i.IncomingMark,
                            Host = h,
                        });

                    ppHostes.Value = 100;
                    #endregion
                    #region Join phones or create new
                    logSession.Add("Join phones or create new");

                    var phones = subRes0
                        .AsParallel()
                        .Select(i => i.IncomingPhone.PhoneNumber)
                        .Distinct()
                        .LeftOuterJoin(Repository.PhoneGet(), p => p, p => p.PhoneNumber, (p, phone) => new { PhoneNumber = p, Phone = phone })
                        .ToArray()
                        .Select(i => i.Phone ?? Repository.PhoneNew(i.PhoneNumber, Account))
                        .ToArray();

                    ppPhones.Value = 50;

                    var subRes2 = subRes1
                        .Join(phones, i => i.IncomingPhone.PhoneNumber, p => p.PhoneNumber, (i, p) => new
                        {
                            i.Row,
                            i.IncomingPhone,
                            i.IncomingAddress,
                            i.IncomingCity,
                            i.IncomingHost,
                            i.IncomingMark,
                            i.Host,
                            Phone = p,
                        });

                    ppPhones.Value = 100;
                    #endregion
                    #region Join marks
                    logSession.Add("Join marks");

                    var defMark = Repository.MarkGet(MarkTypes.Unknown);

#pragma warning disable 618
                    var subRes3 = subRes2
                        .LeftOuterJoin(Repository.MarkGet(), i => i.IncomingMark.ToUpper(), m => m.SystemName.ToUpper(), (i, m) => new
                        {
                            i.Row,
                            i.IncomingPhone,
                            i.IncomingAddress,
                            i.IncomingCity,
                            i.IncomingHost,
                            i.IncomingMark,
                            i.Host,
                            i.Phone,
                            Mark = m ?? defMark,
                        });
#pragma warning restore 618
                    ppMarks.Value = 100;

                    #endregion
                    #region Join cities or create new
                    logSession.Add("Join cities or create new");

                    var cities = subRes0
                        .AsParallel()
                        .Select(i => i.IncomingCity)
                        .Distinct()
                        .LeftOuterJoin(Repository.CityGet(), c => c.ToUpper(), c => c.Name.ToUpper(), (c, city) => new { CityName = c, City = city })
                        .ToArray()
                        .Select(i => i.City ?? Repository.CityNew(i.CityName))
                        .ToArray();

                    ppCities.Value = 50;

                    var subRes4 = subRes3
                        .Join(cities, i => i.IncomingCity.ToUpper(), c => c.Name.ToUpper(), (i, c) => new
                        {
                            i.Row,
                            i.IncomingPhone,
                            i.IncomingAddress,
                            i.IncomingCity,
                            i.IncomingHost,
                            i.IncomingMark,
                            i.Host,
                            i.Phone,
                            i.Mark,
                            City = c
                        });

                    ppCities.Value = 100;
                    #endregion
                    #region Join streets

                    logSession.Add("Join streets");

                    var aP = new Parser.AddressParser(Account, Repository);

                    var aPR = aP.Parse(subRes4
                        .GroupBy(i => new { i.City, i.IncomingAddress.Street, House = i.IncomingAddress.House.ToString(), i.IncomingAddress.Area })
                        .Select(g => new
                        {
                            City = g.Key.City,
                            Address = new Parser.Address(g.Key.Street, g.Key.House, g.Key.Area)
                        })
                        .Select(g => new Parser.AddressParserIncomingParameter()
                        {
                            City = g.City,
                            Address = g.Address
                        })
                        .ToArray(),
                        !UseDictionary,
                        (progress) => ppStreets.Value = progress,
                        (str) => logSession.Add(str));

                    var subRes5 = subRes4
                        .LeftOuterJoin(aPR,
                            r => new { r.IncomingAddress.Street, House = r.IncomingAddress.House.ToString(), r.IncomingAddress.Area, r.City.Name },
                            i => new { i.Key.Address.Street, House = i.Key.Address.House.ToString(), i.Key.Address.Area, i.Key.City.Name },
                            (r, i) => new
                            {
                                r.Row,
                                LoadedRow = new
                                {
                                    i.Value.Address, //change old address to new address
                                    i.Value.Street,
                                    r.Mark,
                                    r.Phone,
                                    r.City,
                                    r.Host,
                                },
                            });

                    #endregion
                    #region Update addresses

                    logSession.Add("Update house numbers");

                    var expectedData = Account.Data
                        .Where(d => !string.IsNullOrWhiteSpace(d.HouseNumber))
                        .GroupBy(d => new { d.Phone, d.Street })
                        .Select(d => new { d.Key.Phone, d.Key.Street, Houses = d.Select(i => i.HouseNumber), Cnt = d.Count() })
                        .Where(i => i.Cnt == 1)
                        .Select(i => new { i.Phone, i.Street, HouseNumber = i.Houses.FirstOrDefault() });

                    var currentData = subRes5
                        .Where(d => !string.IsNullOrWhiteSpace(d.LoadedRow.Address.House.ToString()))
                        .GroupBy(d => new { d.LoadedRow.Phone, d.LoadedRow.Street })
                        .Select(d => new { d.Key.Phone, d.Key.Street, Houses = d.Select(i => i.LoadedRow.Address.House.Number), Cnt = d.Count() })
                        .Where(i => i.Cnt == 1)
                        .Select(i => new { i.Phone, i.Street, HouseNumber = i.Houses.FirstOrDefault() });

                    ppAddresses.Value = 50;

                    var subRes6 = subRes5
                        .LeftOuterJoin(currentData, r => new { r.LoadedRow.Phone.PhoneNumber, r.LoadedRow.Street }, d => new { d?.Phone?.PhoneNumber, d?.Street }, (r, d) => new { Data = r, Grouped = d })
                        .LeftOuterJoin(expectedData, r => new { r.Data.LoadedRow.Phone.PhoneNumber, r.Data.LoadedRow.Street }, d => new { d?.Phone?.PhoneNumber, d?.Street },
                            (r, d) => new
                            {
                                r.Data.Row,
                                LoadedRow = new
                                {
                                    //Address = new Parser.Address(r.Data.LoadedRow.Address.Street, (r.Data.LoadedRow.Address.House.Number?.ToString() ?? r.Grouped?.HouseNumber?.ToString() ?? d?.HouseNumber ?? string.Empty), r.Data.LoadedRow.Address.Area),
                                    HouseNumber = (r.Data.LoadedRow.Address.House.Number?.ToString() ?? r.Grouped?.HouseNumber?.ToString() ?? d?.HouseNumber ?? string.Empty),
                                    r.Data.LoadedRow.Street,
                                    r.Data.LoadedRow.Mark,
                                    r.Data.LoadedRow.Phone,
                                    r.Data.LoadedRow.City,
                                    r.Data.LoadedRow.Host,
                                }
                            });

                    ppAddresses.Value = 100;

                    #endregion
                    #region Load rows

                    logSession.Add("Load rows");

                    var ppRowsPrepare = ppRows.GetChild();
                    var ppRowsLoad = ppRows.GetChild();

                    var rowCount0 = (decimal)subRes6.Count() - 1;
                    var currentIndex0 = 0m;

                    var res = subRes6.Select(r => new
                    {
                        r.Row,
                        DataRecord = Repository.AccountDataRecordNew(Account, r.LoadedRow),
                        Index = (ppRowsPrepare.Value = (currentIndex0++) / rowCount0 * 100m)
                    })
                    .Select(r => new
                    {
                        r.Row,
                        LoadedRow = new DataPreviewRow()
                        {
                            DataRecord = r.DataRecord,
                            DataRecordAdditional = Repository.AccountDataRecordAdditionalNew(r.DataRecord),
                        },
                    }
                    )
                    .ToArray();

                    var rowCount1 = (decimal)dataTable.Rows.Count - 1;
                    var currentIndex1 = 0m;
                    var columns = Account.AdditionalColumns.Where(c => !string.IsNullOrWhiteSpace(c.ColumnName));

                    res.ToList()
                        .ForEach(r =>
                        {
                            foreach (var c in columns)
                            {
                                var pi = r.LoadedRow.DataRecordAdditional.GetType().GetProperty(c.ColumnSystemName);
                                pi.SetValue(r.LoadedRow.DataRecordAdditional, r.Row[c.ColumnName]);
                            }
                            ppRowsLoad.Value = currentIndex1++ / rowCount1 * 100m;
                        });
                    #endregion
                    return res.ToDictionary(i => i.Row, i => i.LoadedRow);
                }
                catch (Exception ex)
                {
                    logSession.Add(ex);
                    logSession.Enabled = true;
                    throw;
                }
        }
Example #10
0
        /// <summary>
        /// Обновление меток в данных
        /// </summary>
        /// <param name="phonesToUpdate">Телефоны, которые нужно проверить для обновления меток</param>
        /// <param name="progressAction">Действие для отображения прогресса</param>
        /// <param name="logAction">Действие для отображения лога</param>
        private void UpdateMarks(IEnumerable<Phone> phonesToUpdate, Action<decimal> progressAction = null, Action<string> logAction = null)
        {
            progressAction = progressAction ?? new Action<decimal>((i) => { });
            logAction = logAction ?? new Action<string>((i) => { });

            using (var logSession = Log.Session($"{this.GetType().Name}.{nameof(UpdateMarks)}()", VerboseLog))
                try
                {
                    logSession.Output = (strs) => strs.ToList().ForEach(s => logAction(s));

                    if (phonesToUpdate == null)
                        throw new ArgumentNullException(nameof(phonesToUpdate));

                    var progress = new Helpers.PercentageProgress();
                    var quickMarksProgress = progress.GetChild();
                    var similarityPhonesProgress = progress.GetChild();
                    progress.Change += (s, e) => progressAction(e.Value);

                    var phones = phonesToUpdate.Select(p => new { Phone = p, Progress = similarityPhonesProgress.GetChild() }).ToList();

                    logSession.Add($"update marks for ({phones.Count}) phones start...");

                    var now = DateTime.UtcNow;
                    var startFindDate = now - (Account.Settings.TimeForTrust ?? new TimeSpan());
                    var numberSeries = Account.SeriesOfNumbers.Where(s => s.DigitCount > 0).ToList();

                    #region Get default marks

                    var phoneMarksDictionary = phonesToUpdate
                        .Join(Account.Data, p => p, d => d.Phone, (p, d) => d)
                        .GroupBy(i => i.Phone)
                        .Select(g => new { Phone = g.Key, CountWithoutConstraint = g.Count(), CountWithConstraint = g.Count(d => d.Created <= startFindDate) })
                        .Select(i =>
                        {
                            var markType = MarkTypes.Default;
                            if (i.CountWithoutConstraint >= 3)
                            {
                                markType = MarkTypes.NotTrusted;
                            }
                            else
                            {
                                if (i.CountWithConstraint > 0)
                                {
                                    if (i.CountWithoutConstraint == 1)
                                    {
                                        markType = MarkTypes.Trusted;
                                    }
                                    else
                                    {
                                        if (i.CountWithoutConstraint == 2)
                                            markType = MarkTypes.HalfTrusted;
                                    }
                                }
                                else
                                {
                                    if (i.CountWithoutConstraint == 2)
                                        markType = MarkTypes.Suspicious;
                                }
                            }
                            return new { i.Phone, markType };
                        })
                        .ToDictionary(i => i.Phone, i => i.markType);

                    quickMarksProgress.Value = 100;

                    #endregion

#if DEBUG
                    phones.ForEach(phoneItem =>
#else
                    Parallel.ForEach(phones, phoneItem =>
#endif
                    {
                        var phone = phoneItem.Phone;
                        var currentMarkType = phoneMarksDictionary[phone];
                        logSession.Add($"mark selected for phone '{phone}' is '{currentMarkType}'");

                        #region Serial numbers

                        if (currentMarkType == MarkTypes.Default)
                        {
                            var prgPrepare = phoneItem.Progress.GetChild();
                            var validNumberSeries = numberSeries
                                .Where(ns => phone.PhoneNumber.Length >= ns.DigitCount)
                                .Select(ns => new { NumberSeries = ns, Progress = phoneItem.Progress.GetChild() })
                                .ToList();
                            prgPrepare.Value = 100;
                            validNumberSeries.ForEach(ns =>
                            {
                                logSession.Add($"similarity action start for phone '{phone}'...");
                                var startFindSimilarityDate = now - ns.NumberSeries.Delay;

                                string phoneSimilarity = phone.PhoneNumber.Substring(0, phone.PhoneNumber.Length - (int)ns.NumberSeries.DigitCount);
                                var similaryDataQuery = Account.Data
                                    .Where(r => r.Phone.PhoneNumber.StartsWith(phoneSimilarity));
                                if (ns.NumberSeries.Delay.Ticks > 0)
                                    similaryDataQuery = similaryDataQuery.Where(r => r.Created >= startFindSimilarityDate);

                                var similarytyPhones = similaryDataQuery.Select(sd => sd.Phone).Distinct().Except(new Phone[] { phone }).ToList();
                                if (similarytyPhones.Count > 0)
                                {
                                    currentMarkType = MarkTypes.Suspicious;
                                    lock (phoneMarksDictionary)
                                    {
                                        phoneMarksDictionary[phone] = currentMarkType;
                                        similarytyPhones.ForEach(sp =>
                                        {
                                            if (phoneMarksDictionary.ContainsKey(sp))
                                                phoneMarksDictionary[sp] = currentMarkType;
                                            else
                                                phoneMarksDictionary.Add(sp, currentMarkType);
                                        });
                                    }
                                }

                                ns.Progress.Value = 100;
                            });
                        }
                        else
                        {
                            phoneItem.Progress.Value = 100;
                        }
                        #endregion

                        logSession.Add($"mark detection done for phone '{phone}'. Finally mark is '{currentMarkType}'.");
                    });

                    var allMarks = Repository.MarkGet().ToArray();

                    var phoneMarksToUpdate = Account.PhoneMarks
                        .Join(phoneMarksDictionary, pm => pm.Phone, md => md.Key, (pm, md) => new { Item = pm, NewMarkType = md.Value })
                        .Join(allMarks, i => i.NewMarkType, m => m.Type, (i, m) => new { i.Item, NewMark = m })
                        .ToList();

                    phoneMarksToUpdate.ForEach(i => i.Item.Mark = i.NewMark);
                }
                catch (Exception ex)
                {
                    logSession.Add(ex);
                    logSession.Enabled = true;
                    throw;
                }
        }
Example #11
0
        /// <summary>
        /// Вставить записи в основную таблицу данных аккаунта
        /// </summary>
        /// <param name="previewRows">Rows to insert</param>
        /// <param name="importFile">Import queue file</param>
        /// <param name="progressAction">Действие для отображения прогресса</param>
        /// <param name="logAction">Действие для отображения лога</param>
        /// <returns>Возвращает обработанные данные, которые можно экспортировать</returns>
        public IEnumerable<AccountDataRecord> Import(IEnumerable<DataPreviewRow> previewRows, ImportQueueRecordFileInfo importFile, Action<decimal> progressAction = null, Action<string> logAction = null)
        {
            progressAction = progressAction ?? new Action<decimal>((i) => { });
            logAction = logAction ?? new Action<string>((i) => { });

            using (var logSession = Log.Session($"{this.GetType().Name}.{nameof(Import)}()", VerboseLog))
                try
                {
                    logSession.Output = (strs) => strs.ToList().ForEach(s => logAction(s));

                    if (previewRows == null)
                        throw new ArgumentNullException(nameof(previewRows));

                    var progress = new Helpers.PercentageProgress();
                    var prgPreparation = progress.GetChild(weight: 0.3m);
                    var prgUpdateMarks = progress.GetChild(weight: 0.6m);
                    var prgPrepareExport = progress.GetChild(weight: 0.1m);
                    progress.Change += (s, e) => progressAction(e.Value);

                    logSession.Add($"grouping {previewRows.Count()} rows...");

                    var dataToImport = previewRows
                        .GroupBy(i => new { i.DataRecord.Phone, i.DataRecord.Street, i.DataRecord.HouseNumber })
                        .Select(i => i.LastOrDefault())
                        .LeftOuterJoin(Account.Data, g => new { g.DataRecord.Phone, g.DataRecord.Street, g.DataRecord.HouseNumber }, d => new { d.Phone, d.Street, d.HouseNumber }, 
                        (g, d) => new
                        {
                            Existed = d,
                            Insert = g
                        })
                        .ToList();

                    if (importFile != null)
                        dataToImport.ForEach(r => Repository.ImportQueueRecordFileAccountDataRecordNew(importFile, r.Existed ?? r.Insert.DataRecord));

                    prgPreparation.Value = 50;
                    logSession.Add($"grouping rows done. Insert it in account data.");

                    //Repository.AccountDataRecordAdd(dataToImport.Where(i => i.Existed == null).Select(i => i.Insert.DataRecord), Account, false);

                    dataToImport.ForEach(i =>
                    {
                        if (i.Existed == null)
                        {
                            Repository.AccountDataRecordAdd(i.Insert.DataRecord, saveAfterInsert: false);
                        }
                        else
                        {
                            i.Insert.DataRecord.CopyObject(i.Existed, new string[] { nameof(i.Insert.DataRecord.DataAdditional), nameof(i.Insert.DataRecord.LoadedByQueueFiles), nameof(i.Insert.DataRecord.AccountDataRecordUID), nameof(i.Insert.DataRecord.Created), nameof(i.Insert.DataRecord.Exported) });
                            i.Existed.CopyObject(i.Insert.DataRecord, new string[] { nameof(i.Insert.DataRecord.DataAdditional), nameof(i.Insert.DataRecord.LoadedByQueueFiles), nameof(i.Insert.DataRecord.AccountDataRecordUID) });

                            i.Insert.DataRecordAdditional.CopyObject(i.Existed.DataAdditional, new string[] { nameof(i.Insert.DataRecordAdditional.AccountDataRecordUID) });
                        }
                    });

                    prgPreparation.Value = 100;

                    UpdateMarks(dataToImport.Select(i => i.Insert.DataRecord.Phone).Distinct(), (i) => prgUpdateMarks.Value = i, s => logSession.Add($"update marks: {s}"));

                    var exportStart = DateTime.UtcNow - (Account.Settings.IgnoreExportTime ?? new TimeSpan());

                    logSession.Add($"prepare results to return records older then '{exportStart}'");
                    var rowsToExport = dataToImport
                        .Where(r => (r.Insert.DataRecord.Exported ?? DateTime.MinValue) <= exportStart)
                        .Select(r => r.Insert.DataRecord);

                    prgPrepareExport.Value = 100;

                    return rowsToExport;
                }
                catch (Exception ex)
                {
                    logSession.Add(ex);
                    logSession.Enabled = true;
                    throw;
                }
        }
Example #12
0
        /// <summary>
        /// Подготовка таблицы аккаунта для импорта
        /// </summary>
        /// <param name="dataTable">Загружаемая таблица</param>
        /// <param name="progressAction">Действие для отображения прогресса</param>
        /// <param name="logAction">Действие для отображения лога</param>
        public void Prepare(DataTable dataTable, Action<decimal> progressAction = null, Action<string> logAction = null)
        {
            progressAction = progressAction ?? new Action<decimal>((i) => { });
            logAction = logAction ?? new Action<string>((i) => { });

            using (var logSession = Log.Session($"{GetType().Name}.{nameof(Prepare)}()", VerboseLog))
                try
                {
                    logSession.Output = (strs) => strs.ToList().ForEach(s => logAction(s));

                    if (dataTable == null)
                        throw new ArgumentNullException(nameof(dataTable));

                    var progress = new Helpers.PercentageProgress();
                    progress.Change += (s, e) => progressAction(e.Value);

                    logSession.Add($"Get ignore columns");
                    var columnNamesToIgnore = Account.Settings.Columns.Select(c => c.ColumnName.ToUpper());
                    progress.Value = 10;

                    logSession.Add($"Get columns to add from data table");
                    var dataColumnNamesToAdd = dataTable.Columns.OfType<DataColumn>().Select(dc => dc.ColumnName).Where(dc => !columnNamesToIgnore.Contains(dc.ToUpper()));
                    progress.Value = 20;

                    logSession.Add($"Join additional columns with columns to add");
                    var namesToAdd = dataColumnNamesToAdd
                        .LeftOuterJoin(Account.AdditionalColumns, n => n.ToUpper(), c => c.ColumnName.ToUpper(), (n, c) => new { ColumnName = n, AdditionalColumn = c })
                        .Where(c => c.AdditionalColumn == null)
                        .Select(c => c.ColumnName).ToArray()
                        .ToArray();
                    progress.Value = 30;

                    logSession.Add($"Get existed additional columns");
                    var existingColumnSystemNames = Account.AdditionalColumns.Select(ac => ac.ColumnSystemName).ToArray();
                    progress.Value = 40;

                    logSession.Add($"Check free additional columns");
                    var freeColumnNames = Enumerable.Range(0, (int)AccountDataRecordAdditional.ColumnCount)
                        .Select(i => new { Index = i, ColumnName = $"Column{i.ToString("00")}" })
                        .LeftOuterJoin(existingColumnSystemNames, n => n.ColumnName.ToUpper(), n => n?.ToUpper(), (n0, n1) => new { n0.Index, n0.ColumnName, Exists = n1 != null })
                        .Where(i => !i.Exists)
                        .OrderBy(i => i.Index)
                        .Select(i => i.ColumnName)
                        .ToArray();
                    progress.Value = 50;

                    logSession.Add($"Free columns count: {freeColumnNames.Length} and need to add folowing ({namesToAdd.Length}) columns: {namesToAdd.Select(c => $"'{c}'").Concat(i => i, ", ")}");
                    for (int i = 0; i < Math.Min(namesToAdd.Length, freeColumnNames.Length); i++)
                    {
                        var adrac = Repository.AccountDataRecordAdditionalColumnNew(Account, namesToAdd[i], freeColumnNames[i]);
                        logSession.Add($"Additional column added: '{adrac}'");
                    }
                    progress.Value = 100;
                }
                catch (Exception ex)
                {
                    logSession.Add(ex);
                    logSession.Enabled = true;
                    throw;
                }
        }
Example #13
0
 /// <summary>
 /// Remove child
 /// </summary>
 /// <param name="child">Child to remove</param>
 public void RemoveChild(PercentageProgress child)
 {
     lock (childLocks)
         if (childs.Contains(child))
         {
             child.Change -= child_Change;
             childs.Remove(child);
         }
     RaiseChange();
 }
Example #14
0
 /// <summary>
 /// Get new child with default value
 /// </summary>
 /// <param name="value">Percentage value for new child</param>
 /// <param name="weight">Percentage item weight for parent item</param>
 /// <returns>Child with default value for this item</returns>
 public PercentageProgress GetChild(decimal value = defaultValue, decimal weight = defaultWeight)
 {
     PercentageProgress result = new PercentageProgress() { Value = value, Weight = weight };
     result.Change += child_Change;
     lock (childLocks)
     {
         childs.Add(result);
     }
     RaiseChange();
     return result;
 }
Example #15
0
        public void DataCalculator_Prepare()
        {
            SqlLogEnabled = false;
            try
            {
                var acc = Rep.GetAccount(defAccountName, eagerLoad: new string[] { "Settings.Columns.ColumnType" });
                var csvLines = new List<string>();

                var colValues = acc.Settings.Columns
                    .Where(c => c.ColumnType.ImportTableValidation)
                    .Select(c => new { Name = c.ColumnName.ToLower(), Type = c.ColumnType })
                    .ToArray();

                var columns = string.Empty;
                foreach (var colName in colValues)
                    columns += (string.IsNullOrWhiteSpace(columns) ? string.Empty : ";") + colName.Name;

                columns += ";test_column";

                csvLines.Add(columns);

                using (var dc = new DataCalculator(acc, Rep))
                {
                    var prg0 = new Helpers.PercentageProgress();
                    prg0.Change += (s, e) => Helpers.Log.Add($"Progress: {e.Value.ToString("N2")}");
                    var log = new Action<string>((s) => Helpers.Log.Add(s));

                    var l = Helpers.CSV.CSVFile.Load(csvLines.AsEnumerable(),
                        tableValidator: dc.TableValidator,
                        rowFilter: dc.RowFilter);

                    dc.Prepare(l.Table, i => prg0.Value = i, log);
                    Assert.AreEqual(true, acc.AdditionalColumns.Any(ad => ad.ColumnName == "test_column"));
                    Rep.SaveChanges();

                    Rep.AccountDataRecordAdditionalColumnRemove(acc.AdditionalColumns);
                }
            }
            finally
            {
                SqlLogEnabled = true;
            }
        }
Example #16
0
        public static ExelRow[] LoadRows(Worksheet sheet, int count = 0, Action <int> progressReport = null, bool deleteEmptyRows = true)
        {
            var result         = new List <ExelRow>();
            var totalRowsCount = 0;
            var loaded         = 0;

            var pp = new Helpers.PercentageProgress();

            if (progressReport != null)
            {
                pp.Change += (s, e) => { progressReport((int)e.Value); }
            }
            ;

            var pp0 = pp.GetChild(weight: 9);
            var pp1 = pp.GetChild(weight: 1);

            try
            {
                totalRowsCount = Math.Max(sheet.Cells.MaxRow + 1, sheet.Cells.Rows.Count);
            }
            catch
            {
                totalRowsCount = sheet.Cells.Rows.Count;
            }


            if (count <= 0)
            {
                count = totalRowsCount;
            }

            #region Select max column index for sheet
            int maxColumnIndex = sheet.Cells.Rows.Cast <Row>().Take(count).Select(c =>
            {
                if (c.LastCell != null)
                {
                    for (int i = c.LastCell.Column; i >= 0; i--)
                    {
                        var cell = c.GetCellOrNull(i);
                        if (!string.IsNullOrWhiteSpace(
                                cell == null || cell.Value == null
                                    ? null
                                    : cell.Value.ToString().Trim()
                                )
                            )
                        {
                            return(cell.IsMerged ? cell.GetMergedRange().FirstColumn + cell.GetMergedRange().ColumnCount - 1 : i);
                        }
                    }
                }
                return(0);
            }
                                                                                  )
                                 .Union(new int[] { 0 })
                                 .Max();
            #endregion
            #region Read rows

            if (sheet.Cells.Count > 0)
            {
                var lockObj  = new Object();
                var lockCell = new Object();
                sheet.Cells.MultiThreadReading = true;

                var items =
                    sheet.Cells.Rows.Cast <Row>()
                    .Where(r => r.Index < count)
                    .AsParallel()
                    .Select(row =>
                {
                    Thread.CurrentThread.CurrentCulture   = CultureInfo.GetCultureInfo("ru-RU");
                    Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;

                    var r = new ExelRow()
                    {
                        Index = row.Index
                    };
                    var currMaxColumnsIndex = row.LastCell == null ? 0 : row.LastCell.Column;

                    int lastFilledColumnIndex = Math.Min(currMaxColumnsIndex, maxColumnIndex);

                    #region Read data from cells

                    var cells = Enumerable.Range(0, lastFilledColumnIndex + 1)
                                .Select(cellIndex => new { OriginalCell = row.GetCellOrNull(cellIndex), Index = cellIndex })
                                .Select(c => new { OriginalCell = c.OriginalCell, ResultCell = new ExelCell()
                                                   {
                                                       Value = string.Empty, FormatedValue = string.Empty, CellStyle = new Style(), OriginalIndex = c.Index
                                                   } })
                                .Select(i =>
                    {
                        if (i.OriginalCell != null)
                        {
                            lock (lockCell)
                                i.ResultCell.FormatedValue = i.OriginalCell.StringValue;

                            var link = GetHyperlinkForCell(i.OriginalCell, sheet);
                            if (link != null)
                            {
                                i.ResultCell.HyperLink = link.Address;
                            }
                            else
                            {
                                var formula = string.Empty;
                                lock (lockCell)
                                    formula = i.OriginalCell.Formula;

                                if (!string.IsNullOrWhiteSpace(formula))
                                {
                                    i.ResultCell.HyperLink = formula.Split(new char[] { '\"' }).FirstOrDefault(str => str.Contains("http"));
                                }
                            }

                            if (i.OriginalCell.IsMerged)
                            {
                                i.ResultCell.IsMerged = true;

                                var content = string.Empty;
                                var values  = (IEnumerable)i.OriginalCell.GetMergedRange().Value;
                                if (values != null)
                                {
                                    foreach (var value in values)
                                    {
                                        content += value;
                                    }
                                }

                                i.ResultCell.Value = content;
                            }
                            else if (i.OriginalCell.Value != null)
                            {
                                i.ResultCell.Value = i.OriginalCell.Value.ToString();
                            }
                            else
                            {
                                i.ResultCell.Value = string.Empty;
                            }

                            try
                            {
                                var comment = sheet.Comments[i.OriginalCell.Row, i.ResultCell.OriginalIndex];
                                if (comment != null)
                                {
                                    i.ResultCell.Comment = comment.Note;
                                }
                            }
                            catch { }

                            var style = i.OriginalCell.GetStyle();
                            i.ResultCell.CellStyle = style;
                            i.ResultCell.Color     = (style != null) ? (DefColors.Any(clr => ColorsEqual(clr, style.BackgroundColor)) ? style.ForegroundColor : style.BackgroundColor) : System.Drawing.Color.White;
                            i.ResultCell.Color     = System.Drawing.Color.FromArgb((i.ResultCell.Color.R > byte.MinValue || i.ResultCell.Color.G > byte.MinValue || i.ResultCell.Color.B > byte.MinValue) && i.ResultCell.Color.A == byte.MinValue ? byte.MaxValue : i.ResultCell.Color.A, i.ResultCell.Color.R, i.ResultCell.Color.G, i.ResultCell.Color.B);
                        }
                        return(new { ResultCell = i.ResultCell, Index = i.ResultCell.OriginalIndex });
                    })
                                .OrderBy(i => i.Index)
                                .Select(i => i.ResultCell)
                                .ToArray();
                    r.Cells.AddRange(cells);

                    #endregion

                    var lastStyle = (maxColumnIndex > 0) ? r.Cells[lastFilledColumnIndex].CellStyle : new Style();

                    for (int i = r.Cells.Count; i <= maxColumnIndex; i++)
                    {
                        r.Cells.Add(new ExelCell()
                        {
                            Value = string.Empty, CellStyle = lastStyle
                        });
                    }

                    lock (lockObj)
                    {
                        loaded++;
                        pp0.Value = (decimal)loaded * 100m / (decimal)count;
                    }

                    return(r);
                })
                    .OrderBy(r => r.Index)
                    .Where(r => !deleteEmptyRows || !r.IsEmpty)         // delete empty rows
                    .ToArray();
                result.AddRange(items);
            }

            #endregion

            if (result.Count > 0)
            {
                #region Delete bottom

                //#### Try to delete bottom info ####
                //get last empty index

                var lastEmptyRow             = result.LastOrDefault(row => row.IsEmpty);
                int lastEmptyRowsInDataIndex = lastEmptyRow == null ? 0 : result.IndexOf(lastEmptyRow);

                //int lastEmptyRowsInDataIndex =
                //    result
                //    .Where(row => row.IsEmpty)
                //    .Select(row => result.IndexOf(row))
                //    .OrderByDescending(i => i)
                //    .FirstOrDefault();

                //get last non empty index before last empty to check similarity
                var lastNotEmptyRow = result.Take(lastEmptyRowsInDataIndex == 0 ? result.Count : lastEmptyRowsInDataIndex).LastOrDefault(row => !row.IsEmpty);
                int lastNotEmptyRowsInDataIndexBeforeEmpty = lastNotEmptyRow == null ? 0 : result.IndexOf(lastNotEmptyRow);

                //int lastNotEmptyRowsInDataIndexBeforeEmpty =
                //    result
                //    .Where(row => !row.IsEmpty)
                //    .Select(row => result.IndexOf(row))
                //    .Where(i => i < lastEmptyRowsInDataIndex)
                //    .OrderByDescending(i => i)
                //    .FirstOrDefault();

                if (lastEmptyRowsInDataIndex > 4 && result.Count - lastEmptyRowsInDataIndex < 15)
                {
                    List <ExelRow> similarityIndexes = new List <ExelRow>();
                    for (int i = lastNotEmptyRowsInDataIndexBeforeEmpty; i >= 0 && i > lastNotEmptyRowsInDataIndexBeforeEmpty - 10; i--)
                    {
                        similarityIndexes.Add(result[i]);
                    }

                    for (int z = result.Count - 1; z >= lastEmptyRowsInDataIndex; z--)
                    {
                        if (result[z].NotEmptyCells.Count() <= 4 && !similarityIndexes.Select(s => s.Similarity(result[z])).Any(d => d > 0.6))
                        {
                            result.RemoveAt(z);
                        }
                        else
                        {
                            break;
                        }
                    }
                }

                pp1.Value = 25;

                var isRowsSimilar = new Func <ExelRow, ExelRow, bool>((r1, r2) =>
                {
                    var cnt = Math.Min(r1.Cells.Count, r2.Cells.Count);
                    for (int i = 0; i < cnt; i++)
                    {
                        if (string.Compare(r1.Cells[i].Value.Trim(), r2.Cells[i].Value.Trim(), true) != 0)
                        {
                            return(false);
                        }
                    }
                    return(true);
                });

                var defStart = 4;
                var minCountForDeleteSimilarity = Properties.Settings.Default.MaxRowsInGroupCountToDeleteSimilarityRows;
                for (int i = result.Count - 1; i >= defStart; i--)
                {
                    var n = 1;
                    if ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n]))
                    {
                        var rowIndexes = new List <int>(new int[] { i - n });
                        do
                        {
                            n++;
                            rowIndexes.Add(i - n);
                        } while ((i - n >= defStart) && result[i].Similarity(result[i - n]) >= 0.8 && isRowsSimilar(result[i], result[i - n]));

                        if (n + 1 >= minCountForDeleteSimilarity)
                        {
                            rowIndexes
                            .OrderByDescending(ind => ind)
                            .ToList()
                            .ForEach(ind => result.RemoveAt(ind));
                            i -= n;
                            continue;
                            //result.RemoveAt(i);
                        }
                    }
                }

                pp1.Value = 50;

                #endregion

                //Delete all empty rows from data
                if (deleteEmptyRows)
                {
                    for (int z = result.Count - 1; z >= 0; z--)
                    {
                        var r1 = result[z];
                        if (r1.IsEmpty)
                        {
                            result.RemoveAt(z);
                        }
                    }
                }

                pp1.Value = 100;
            }
            return(result.ToArray());
        }
Example #17
0
        private void Load()
        {
            if (loadWorker != null)
                try
                {
                    loadWorker.CancelAsync();
                }
                catch { }

            LoadingProgress = 0;
            IsDocumentLoaded = false;
            Error = string.Empty;

            if (string.IsNullOrWhiteSpace(Path))
                return;

            IsBusy = true;
            loadWorker = new BackgroundWorker() { WorkerReportsProgress = true, WorkerSupportsCancellation = true };

            var init = new AsyncLoadInit() { DeleteEmptyRows = this.DeleteEmptyRows, WorkBookFilePath = this.Path, PreloadCount = Settings.SettingsProvider.CurrentSettings.PreloadedRowsCount };

            var applyRes = new Action<Workbook, ExelSheet[]>((w, s) =>
            {
                WorkBook = w;
                DocumentSheets.Clear();
                var oldSheetName = (SelectedSheet == null ? string.Empty : SelectedSheet.Name) ?? string.Empty;
                foreach (var s2 in s)
                    DocumentSheets.Add(s2);
                SelectedSheet = DocumentSheets.FirstOrDefault(ss => ss.Name == oldSheetName) ?? DocumentSheets.FirstOrDefault();
            });

            loadWorker.DoWork += (s, e) =>
            {
                Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("ru-RU");
                Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;

                var bw = (BackgroundWorker)s;
                var prms = (AsyncLoadInit)e.Argument;
                var res = new AsyncLoadResult() { StartSettings = prms };

                var pp = new Helpers.PercentageProgress();
                pp.Change += (sP, eP) => { bw.ReportProgress((int)eP.Value); };

                var pp0 = pp.GetChild();
                pp0.Weight = 1;
                var pp1 = pp.GetChild();
                pp1.Weight = 3;
                var pp2 = pp.GetChild();
                pp2.Weight = 6;
                //var pp3 = pp.GetChild();
                //pp3.Weight = 3;

                bw.ReportProgress((int)pp.Value, "Открытие документа...");

                res.WorkBook = new Workbook(prms.WorkBookFilePath);
                pp0.Value = 100;

                bw.ReportProgress((int)pp.Value, "Чтение первых записей на страницах...");

                var subRes = AsyncDocumentLoader.LoadSheets(res.WorkBook, prms.PreloadCount, (i) => { pp1.Value = i; }, prms.DeleteEmptyRows).ToArray();
                bw.ReportProgress((int)pp.Value, new AsyncLoadResult() { WorkBook = res.WorkBook, WorkSheets = subRes });

                bw.ReportProgress((int)pp.Value, "Чтение всех записей на страницах...");

                res.WorkSheets = AsyncDocumentLoader.LoadSheets(res.WorkBook, 0, (i) => { pp2.Value = i; }, prms.DeleteEmptyRows).ToArray();

                //foreach (var ws in res.WorkSheets.Select(w => new { WorkSheet = w, Progress = pp3.GetChild() }).ToArray())
                //{
                //    ws.WorkSheet.UpdateHeaders();

                //}

                bw.ReportProgress((int)pp.Value, "Применение результата...");

                e.Result = res;
            };
            loadWorker.ProgressChanged += (s, e) =>
            {
                if (loadWorker != s)
                    return;

                LoadingProgress = e.ProgressPercentage;
                var res = e.UserState as AsyncLoadResult;
                if (res != null)
                    applyRes(res.WorkBook, res.WorkSheets);
                else
                {
                    var status = e.UserState as string;
                    if (status != null)
                        Status = status;
                }
            };
            loadWorker.RunWorkerCompleted += (s, e) =>
            {
                if (loadWorker == s)
                {
                    if (e.Cancelled)
                        Error = "Задание отменено пользователем";

                    if (e.Error != null)
                    {
                        Error = e.Error.GetExceptionText();
                    } else
                    {
                        var res = e.Result as AsyncLoadResult;
                        if (res != null)
                        {
                            if (res.WorkSheets.Count() == 0)
                                Error = "В документе не найдено ни одного листа";
                            Status = "Применение результатов (подсчёт заголовков и прочее)...";
                            applyRes(res.WorkBook, res.WorkSheets);
                        }
                        else
                            Error = "Внутренняя ошибка приложения";
                    }
                    Status = "Загрузка документа завершена" + (string.IsNullOrWhiteSpace(Error) ? string.Empty : " с ошибками");

                    LoadingProgress = 100;
                    IsDocumentLoaded = true;
                    IsBusy = false;
                    loadWorker = null;
                }
                ((BackgroundWorker)s).Dispose();
            };
            loadWorker.RunWorkerAsync(init);
        }
Example #18
0
        private void Load()
        {
            if (loadWorker != null)
            {
                try
                {
                    loadWorker.CancelAsync();
                }
                catch { }
            }

            LoadingProgress  = 0;
            IsDocumentLoaded = false;
            Error            = string.Empty;

            if (string.IsNullOrWhiteSpace(Path))
            {
                return;
            }

            IsBusy     = true;
            loadWorker = new BackgroundWorker()
            {
                WorkerReportsProgress = true, WorkerSupportsCancellation = true
            };

            var init = new AsyncLoadInit()
            {
                DeleteEmptyRows = this.DeleteEmptyRows, WorkBookFilePath = this.Path, PreloadCount = Settings.SettingsProvider.CurrentSettings.PreloadedRowsCount
            };

            var applyRes = new Action <Workbook, ExelSheet[]>((w, s) =>
            {
                WorkBook = w;
                DocumentSheets.Clear();
                var oldSheetName = (SelectedSheet == null ? string.Empty : SelectedSheet.Name) ?? string.Empty;
                foreach (var s2 in s)
                {
                    DocumentSheets.Add(s2);
                }
                SelectedSheet = DocumentSheets.FirstOrDefault(ss => ss.Name == oldSheetName) ?? DocumentSheets.FirstOrDefault();
            });

            loadWorker.DoWork += (s, e) =>
            {
                Thread.CurrentThread.CurrentCulture   = CultureInfo.GetCultureInfo("ru-RU");
                Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;

                var bw   = (BackgroundWorker)s;
                var prms = (AsyncLoadInit)e.Argument;
                var res  = new AsyncLoadResult()
                {
                    StartSettings = prms
                };

                var pp = new Helpers.PercentageProgress();
                pp.Change += (sP, eP) => { bw.ReportProgress((int)eP.Value); };

                var pp0 = pp.GetChild();
                pp0.Weight = 1;
                var pp1 = pp.GetChild();
                pp1.Weight = 3;
                var pp2 = pp.GetChild();
                pp2.Weight = 6;
                //var pp3 = pp.GetChild();
                //pp3.Weight = 3;

                bw.ReportProgress((int)pp.Value, "Открытие документа...");

                res.WorkBook = new Workbook(prms.WorkBookFilePath);
                pp0.Value    = 100;

                bw.ReportProgress((int)pp.Value, "Чтение первых записей на страницах...");

                var subRes = AsyncDocumentLoader.LoadSheets(res.WorkBook, prms.PreloadCount, (i) => { pp1.Value = i; }, prms.DeleteEmptyRows).ToArray();
                bw.ReportProgress((int)pp.Value, new AsyncLoadResult()
                {
                    WorkBook = res.WorkBook, WorkSheets = subRes
                });

                bw.ReportProgress((int)pp.Value, "Чтение всех записей на страницах...");

                res.WorkSheets = AsyncDocumentLoader.LoadSheets(res.WorkBook, 0, (i) => { pp2.Value = i; }, prms.DeleteEmptyRows).ToArray();

                //foreach (var ws in res.WorkSheets.Select(w => new { WorkSheet = w, Progress = pp3.GetChild() }).ToArray())
                //{
                //    ws.WorkSheet.UpdateHeaders();

                //}

                bw.ReportProgress((int)pp.Value, "Применение результата...");

                e.Result = res;
            };
            loadWorker.ProgressChanged += (s, e) =>
            {
                if (loadWorker != s)
                {
                    return;
                }

                LoadingProgress = e.ProgressPercentage;
                var res = e.UserState as AsyncLoadResult;
                if (res != null)
                {
                    applyRes(res.WorkBook, res.WorkSheets);
                }
                else
                {
                    var status = e.UserState as string;
                    if (status != null)
                    {
                        Status = status;
                    }
                }
            };
            loadWorker.RunWorkerCompleted += (s, e) =>
            {
                if (loadWorker == s)
                {
                    if (e.Cancelled)
                    {
                        Error = "Задание отменено пользователем";
                    }

                    if (e.Error != null)
                    {
                        Error = e.Error.GetExceptionText();
                    }
                    else
                    {
                        var res = e.Result as AsyncLoadResult;
                        if (res != null)
                        {
                            if (res.WorkSheets.Count() == 0)
                            {
                                Error = "В документе не найдено ни одного листа";
                            }
                            Status = "Применение результатов (подсчёт заголовков и прочее)...";
                            applyRes(res.WorkBook, res.WorkSheets);
                        }
                        else
                        {
                            Error = "Внутренняя ошибка приложения";
                        }
                    }
                    Status = "Загрузка документа завершена" + (string.IsNullOrWhiteSpace(Error) ? string.Empty : " с ошибками");

                    LoadingProgress  = 100;
                    IsDocumentLoaded = true;
                    IsBusy           = false;
                    loadWorker       = null;
                }
                ((BackgroundWorker)s).Dispose();
            };
            loadWorker.RunWorkerAsync(init);
        }
        public static ExelSheet[] LoadSheets(Workbook workbook, int count = 0, Action<int> progressReport = null, bool deleteEmptyRows = true)
        {
            var result = new List<ExelSheet>();
            //var totalRowsCount = 0;
            //var loaded = 0;
            //totalRowsCount = workbook.Worksheets.Cast<Worksheet>().Select(i => i.Cells.Rows.Count).Sum();

            Helpers.PercentageProgress prg = new Helpers.PercentageProgress();
            prg.Change += (s, e) =>
            {
                if (progressReport != null)
                    progressReport((int)e.Value);
            };

            foreach (var sheetItem in
                            workbook
                            .Worksheets
                            .Cast<Worksheet>()
                            .Where(s => s.Cells.Rows.Count > 1)
                            .Select(s => new
                            {
                                Sheet = s,
                                ProgressInfo = prg.GetChild()
                            })
                            .ToArray()
                            )
            {
                ExelSheet sht = new ExelSheet() { Name = sheetItem.Sheet.Name };

                sht.Rows.AddRange(
                    LoadRows(sheetItem.Sheet, count, new Action<int>((i) => { sheetItem.ProgressInfo.Value = i; }), deleteEmptyRows)
                    );
                if (sht.Rows.Count > 0)
                    result.Add(sht);
            }

            return result.ToArray();
        }
        private void UpdateStep(int step, bool action, bool clear = false, byte insideThreadCount = 3)
        {
            if (clear)
                UrlsToAddList.Clear();

            #region step 1
            if (step == 1 && action)
            {
                object lockAdd = new Object();
                UrlsToAddList.Clear();

            #if DEBUG
                insideThreadCount = 1;
            #else
                insideThreadCount = ThreadCount;
            #endif

                ParseRuleConnectionType type = NewParseRule.Connection;

                int minWidth = NewParseRule.MinImageWidth;
                int minHeight = NewParseRule.MinImageHeight;
                bool collectIMGTags = NewParseRule.CollectIMGTags;
                bool collectLINKTags = NewParseRule.CollectLINKTags;
                bool collectMETATags = NewParseRule.CollectMETATags;
                byte threadCount = this.ThreadCount;

                BackgroundWorker bw = new BackgroundWorker();
                bw.DoWork += (s, e) =>
                    {
                        Helpers.PercentageProgress progress = new Helpers.PercentageProgress();
                        progress.Change += (sP, eP) =>
                            {
                                bw.ReportProgress((int)eP.Value);
                            };

                        List<UrlResultWrapper> urlResultWrapper = new List<UrlResultWrapper>();
                        var urls = e.Argument as StringUrlWithResultWrapper[];

                        if (urls != null)
                            urls
                                .Where(item => item != null && !string.IsNullOrWhiteSpace(item.Value) && Helper.IsWellFormedUriString(item.Value, UriKind.Absolute))
                                .Select(sw =>
                                    new
                                        {
                                            item = sw,
                                            prgItem = progress.GetChild()
                                        })
                                .ToArray()
                                .AsParallel()
                                .WithDegreeOfParallelism(insideThreadCount)
                                .ForAll(
                                    (sw) =>
                                    {
                                        var item = new UrlResultWrapper() { Value = sw.item.Value };

                                        System.Drawing.Size minSize = new System.Drawing.Size() { Width = minWidth, Height = minHeight };

                                        var result = Helper.GetAllImagesFromUrl(item.Value, minSize, collectIMGTags, collectLINKTags, collectMETATags, threadCount, sw.prgItem, true, type);

                                        foreach (ParseImageResult res in result)
                                            item.ParseResult.Add(res);

                                        if (item.ParseResult.Count > 0)
                                            lock (lockAdd)
                                            {
                                                urlResultWrapper.Add(item);
                                            }
                                    });

                        e.Result = urlResultWrapper;
                    };
                bw.RunWorkerCompleted += (s, e) =>
                    {
                        if (e.Error != null)
                            throw e.Error;

                        try
                        {
                            List<UrlResultWrapper> urlResultWrapper = e.Result as List<UrlResultWrapper>;
                            foreach (var item in urlResultWrapper)
                            {
                                if (item.ParseResult != null)
                                    foreach (var ps in item.ParseResult)
                                        ps.IsSelected = (item.ParseResult.IndexOf(ps) == 0);

                                UrlsToAddList.Add(item);
                            }
                        }
                        finally
                        {
                            bw.Dispose();
                            IsBusy = false;
                        }
                    };
                bw.WorkerReportsProgress = true;
                bw.ProgressChanged += (s, e) =>
                    {
                        LoadedPercent = e.ProgressPercentage;
                    };
                IsBusy = true;
                bw.RunWorkerAsync(Urls.ToArray());
                while (bw.IsBusy)
                    Helper.DoEvents();
                bw = null;
            }
            #endregion
            #region step 2
            else if (step == 2 && action)
            {
                HtmlNodeWithUrl[] nodes =
                    UrlsToAddList
                    .Where(n => !string.IsNullOrWhiteSpace(n.Value))
                    .Select(i =>
                        {
                            ParseImageResult res = i.ParseResult.Where(i2 => i2.IsSelected).FirstOrDefault();
                            return
                                new HtmlNodeWithUrl()
                                {
                                    Node = res == null ? null : res.Node,
                                    Url = res == null ? new Uri(i.Value, UriKind.RelativeOrAbsolute) : res.Url
                                };
                        }
                    )
                    .Where(i3 => i3 != null && i3.Node != null)
                    .ToArray();

                ParseRule newRule = Helper.GetRule(nodes, NewParseRule.Label, NewParseRule.MinImageSize, NewParseRule.CollectIMGTags, NewParseRule.CollectLINKTags, NewParseRule.CollectMETATags);
                newRule.CopyObject(NewParseRule, new string[] { "Connection" });

                ShowRuleModeCommand.Execute(null);
            }
            #endregion

            if (step >= UrlsToAddTabControl.Items.Count)
                for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--)
                    (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == UrlsToAddTabControl.Items.Count - 1) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed;
            else if (step < 0)
                for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--)
                    (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == 0) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed;
            else
            {
                for (int i = UrlsToAddTabControl.Items.Count - 1; i >= 0; i--)
                    (UrlsToAddTabControl.Items[i] as TabItem).Visibility = (i == step) ? System.Windows.Visibility.Visible : System.Windows.Visibility.Collapsed;
            }

            UrlsToAddTabControl.SelectedIndex = UrlsToAddTabControl.Items.IndexOf(UrlsToAddTabControl.Items.Cast<TabItem>().FirstOrDefault(ti => ti.Visibility == System.Windows.Visibility.Visible));
        }