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;
}
}