public void AddressParser_GetStreets() { SqlLogEnabled = false; try { Rep.CityRemove(Rep.CityGet("testCity")); var c = Rep.CityNew("testCity"); Rep.CityAdd(c, saveAfterInsert: false); var a0 = Rep.AreaNew("test area0", city: c); var a1 = Rep.AreaNew("test area1", city: c); Rep.AreaAdd(a0, saveAfterInsert: false); Rep.AreaAdd(a1, saveAfterInsert: false); var s00 = Rep.StreetNew("Ивановского", a0); var s01 = Rep.StreetNew("Пузановского", a0); var s10 = Rep.StreetNew("Победы", a1); var s11 = Rep.StreetNew("Успеха и добра", a1); Rep.StreetAdd(s00, saveAfterInsert: false); Rep.StreetAdd(s01, saveAfterInsert: false); Rep.StreetAdd(s10, saveAfterInsert: false); Rep.StreetAdd(s11, saveAfterInsert: false); var a = Rep.GetAccount(defAccountName, eagerLoad: new string[] { "Dictionary", "Dictionary.Records" }); Rep.AccountDictionaryRecordNew(a.Dictionary, s00); Rep.AccountDictionaryRecordNew(a.Dictionary, s01, s00); Rep.AccountDictionaryRecordNew(a.Dictionary, s10); Rep.AccountDictionaryRecordNew(a.Dictionary, s11); Rep.SaveChanges(); var ap = new AddressParser(a, Rep); var res0 = ap.GetStreetByDictionary(Address.FromString(s00.Name), c); Assert.AreNotEqual(null, res0, "res0 must exists!"); Assert.AreEqual(s00.Name, res0.Name, "res0: Values must equals"); var res1 = ap.GetStreetByDictionary(Address.FromString(s01.Name), c); Assert.AreNotEqual(null, res1, "res1 must exists!"); Assert.AreEqual(s00.Name, res1.Name, "res1: Values must equals"); var res2 = ap.GetStreetByDictionary(Address.FromString(s10.Name.Remove(0, 2)), c); Assert.AreNotEqual(null, res2, "res2 must exists!"); Assert.AreEqual(s10.Name, res2.Name, "res2: Values must equals"); var res3 = ap.GetStreetByDictionary(Address.FromString(s11.Name.Remove(2, 1)), c); Assert.AreNotEqual(null, res3, "res3 must exists!"); Assert.AreEqual(s11.Name, res3.Name, "res3: Values must equals"); var d = DateTime.UtcNow; var testName = "Тестовая Улица"; var addrs = new string[] { s00.Name, s01.Name, s10.Name, s11.Name, testName } .Select(cc => Address.FromString(cc)) .ToArray(); var res = ap.GetStreets(addrs, c); var res01 = res[addrs[0]].Name; var res02 = res[addrs[1]].Name; var res03 = res[addrs[2]].Name; var res04 = res[addrs[3]].Name; var res05 = res[addrs[4]].Name; Assert.AreEqual(0, res[addrs[4]].StreetID, "res[4] must be new street"); Assert.AreNotEqual(null, res01, "res01 cant be null"); Assert.AreNotEqual(null, res02, "res02 cant be null"); Assert.AreNotEqual(null, res03, "res03 cant be null"); Assert.AreNotEqual(null, res04, "res04 cant be null"); Assert.AreNotEqual(null, res05, "res05 cant be null"); Assert.AreEqual(s00.Name, res01, "res01: Values must equals"); Assert.AreEqual(s00.Name, res02, "res02: Values must equals"); Assert.AreEqual(s10.Name, res03, "res03: Values must equals"); Assert.AreEqual(s11.Name, res04, "res04: Values must equals"); Assert.AreEqual(testName, res05, "res05: Values must equals"); Console.WriteLine(string.Format("Time elapsed: {0} ms", (DateTime.UtcNow - d).TotalMilliseconds.ToString("N2"))); Rep.CityRemove(c); } finally { SqlLogEnabled = true; } }
public void AddressParser_GetStreetsByDifferentAreas() { SqlLogEnabled = false; try { Rep.CityRemove(Rep.CityGet("testCity")); var a = Rep.GetAccount(defAccountName, eagerLoad: new string[] { "Dictionary", "Dictionary.Records" }); var c = Rep.CityNew("testCity"); Rep.CityAdd(c, saveAfterInsert: false); var a0 = Rep.AreaNew("test area0", city: c); var a1 = Rep.AreaNew("test area1", city: c); var a2 = Rep.AreaNew("test area2", city: c); var s00 = Rep.StreetNew("Ивановского", a0); var s01 = Rep.StreetNew("Ивановского", a1); var s02 = Rep.StreetNew("Ивановского", a2); var adr0 = Rep.AccountDictionaryRecordNew(a.Dictionary, s00); var adr1 = Rep.AccountDictionaryRecordNew(a.Dictionary, s01); var adr2 = Rep.AccountDictionaryRecordNew(a.Dictionary, s02); Rep.AccountDictionaryRecordConditionNew(adr0, 0, 100); Rep.AccountDictionaryRecordConditionNew(adr1, 101, 200); Rep.AccountDictionaryRecordConditionNew(adr2, 201, 300, even: true); Rep.SaveChanges(); var ap = new AddressParser(a, Rep); var addr0 = Address.FromString(s00.Name + " 50"); var addr1 = Address.FromString(s01.Name + " 150"); var addr2 = Address.FromString(s02.Name + " 250"); var res0 = ap.GetStreetByDictionary(addr0, c, ss => Console.WriteLine(ss) ); Assert.AreNotEqual(null, res0, "res0 must exists!"); Assert.AreEqual(s00.Name, res0.Name, "res0: Street names must equals"); Assert.AreEqual(a0.Name, res0.Area.Name, "res0: Area names must equals"); var res1 = ap.GetStreetByDictionary(addr1, c, ss => Console.WriteLine(ss)); Assert.AreNotEqual(null, res1, "res1 must exists!"); Assert.AreEqual(s01.Name, res1.Name, "res0: Street names must equals"); Assert.AreEqual(a1.Name, res1.Area.Name, "res0: Area names must equals"); var res2 = ap.GetStreetByDictionary(addr2, c, ss => Console.WriteLine(ss)); Assert.AreNotEqual(null, res2, "res2 must exists!"); Assert.AreEqual(s02.Name, res2.Name, "res0: Street names must equals"); Assert.AreEqual(a2.Name, res2.Area.Name, "res0: Area names must equals"); var res = ap.GetStreets(new Address[] { addr0, addr1, addr2 }, c); var res01 = res[addr0]?.Area?.Name; var res02 = res[addr1]?.Area?.Name; var res03 = res[addr2]?.Area?.Name; Assert.AreNotEqual(null, res01, "res01 cant be null"); Assert.AreNotEqual(null, res02, "res02 cant be null"); Assert.AreNotEqual(null, res03, "res03 cant be null"); Assert.AreEqual(a0.Name, res01, "res01: Values must equals"); Assert.AreEqual(a1.Name, res02, "res02: Values must equals"); Assert.AreEqual(a2.Name, res03, "res03: Values must equals"); Rep.CityRemove(c); } finally { SqlLogEnabled = true; } }
public void AddressParser_GetStreetsBySettedAndUnsettedAreas() { SqlLogEnabled = false; try { Rep.CityRemove(Rep.CityGet("testCity")); var a = Rep.GetAccount(defAccountName, eagerLoad: new string[] { "Dictionary.Records.Street.Area.City" }); var c = Rep.CityNew("testCity"); Rep.CityAdd(c, saveAfterInsert: false); var a0 = Rep.AreaNew("test area0", city: c); Rep.SaveChanges(); var ap = new AddressParser(a, Rep); var addr0 = new Address("Ивановского","50"); var addr1 = new Address("Ивановского","150", "test area"); var res = ap.GetStreets(new Address[] { addr0, addr1 }, c) .Select(k => k.Value) .ToArray(); var res01 = res[0]?.Area?.Name; var res02 = res[1]?.Area?.Name; Assert.AreNotEqual(null, res01, "res01 cant be null"); Assert.AreNotEqual(null, res02, "res02 cant be null"); Assert.AreEqual("test area", res01, "res01: Values must equals"); Assert.AreEqual("test area", res02, "res02: Values must equals"); var addr2 = new Address("Петровского", "50"); var addr3 = new Address("Петровского", "150", "test area"); var res1 = ap.GetStreets(new Address[] { addr2 }, c) .Select(k => k.Value) .First(); var res2 = ap.GetStreets(new Address[] { addr3 }, c) .Select(k => k.Value) .First(); Assert.AreEqual(res1.Area, res2.Area, "re1 area must change area to res2 area"); Rep.CityRemove(c); } finally { SqlLogEnabled = true; } }
/// <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; } }