public void InsertIntoDb(List <List <List <Premise> > > preparedPremises, List <List <List <Premise> > > invalidPremises)
{
var invalidPremisesIds = new List <List <List <Premise> > >();
// Загружаем данные в БД
PreInitDb();
foreach (var address in preparedPremises)
{
var validPremisesLists = AddressHelper.GetValidPremisesLists(address);
if (validPremisesLists != null && validPremisesLists.Count > 0)
{
InsertIntoDbTable("_prevalid", validPremisesLists.First());
Logger.Write(string.Format("Insert valid address `{0}`", validPremisesLists.First().First().RawAddress),
MsgType.InformationMsg);
}
else
{
invalidPremisesIds.Add(address);
}
}
foreach (var invalidPremise in invalidPremises)
{
var bestVariants =
invalidPremise.Where(premiseList => premiseList.All(premise => premise.IdPremisesValid != null)).ToList();
if (bestVariants.Any())
{
InsertIntoDbTable("_invalid", bestVariants[0]);
}
else
{
if (invalidPremises[0].Count <= 0)
{
continue;
}
InsertIntoDbTable("_invalid", invalidPremise[0]);
}
Logger.Write(string.Format("Insert invalid address `{0}`. Description: {1}", invalidPremise[0][0].RawAddress, invalidPremise[0][0].Description), MsgType.ErrorMsg);
}
foreach (var invalidPremise in invalidPremisesIds)
{
if (invalidPremisesIds[0].Count <= 0)
{
continue;
}
InsertIntoDbTable("_invalid", invalidPremise[0]);
Logger.Write(string.Format("Insert invalid address `{0}`. Description: {1}", invalidPremise[0][0].RawAddress, invalidPremise[0][0].Description), MsgType.ErrorMsg);
}
PostInsertIntoDb();
var command = DbConnection.CreateCommand();
command.CommandText = "SELECT COUNT(*) as cnt FROM _invalid";
var invalidCount = command.ExecuteScalar();
command = DbConnection.CreateCommand();
command.CommandText = "SELECT COUNT(*) as cnt FROM _valid";
var validCount = command.ExecuteScalar();
Logger.Write(string.Format("Inserted valid addresses count: {0}", validCount), MsgType.InformationMsg);
Logger.Write(string.Format("Inserted invalid addresses count: {0}", invalidCount), MsgType.InformationMsg);
}
private List <int?> FindPremiseInSphinx(string street, string house, string premiseNumber)
{
var trigramms = AddressHelper.TrigrammingStreet(AddressHelper.NormalizeStreet(street));
var matchCount = (int)Math.Max(Math.Floor((double)(trigramms.Split(' ').Count() - 4) / 2), 2);
var query = string.Format(@"SELECT *, WEIGHT() AS weight
FROM test1
WHERE MATCH('@street_name """ + AddressHelper.TrigrammingStreet(AddressHelper.NormalizeStreet(street)) +
@"""/" + matchCount + @" @house ""___" + house + @"___"" @premises_num ""___" + premiseNumber.Replace(',', '_') + @"___""')");
var command = new MySqlCommand(query, SphinxConnection);
var ids = new List <int?>();
using (var reader = command.ExecuteReader())
{
string lastStreetName = null;
while (reader.Read())
{
var id = reader.GetInt32("id");
var streetName = reader.GetString("street_name");
if (lastStreetName == null)
{
lastStreetName = streetName;
ids.Add(id);
}
else
if (lastStreetName == streetName)
{
ids.Add(id);
}
}
}
return(ids);
}
private static List <List <Premise> > ParseRawAddress(Premise premiseAddress)
{
const string regExpression = @"^(?:ул\.?[ ]+|пер\.?[ ]+|пр-кт\.?[ ]+|бул\.?[ ]+|б-р\.?[ ]+|проезд\.?[ ]+)?(.*?)(?:[ ]+ул\.?|[ ]+пер\.?|[ ]+пр-кт\.?|[ ]+бул\.?|[ ]+б-р\.?|[ ]+проезд\.?)?(?:[ ]*,|,?[ ]*дом\.?|,?[ ]*д\.?)[ ]*([0-9]+[ """"\-]*[а-яА-Я]?[ """"\-]*(?:[ ]*[\/\\][ ]*[0-9]+[ """"\-]*[а-яА-Я]?[ """"\-]*)?).*?(?:,?[ ]*квартира\.?|,?[ ]*кв\.?[ ]*комн\.?|,?[ ]*кв\.?[ ]*ком\.?|,?[ ]*кв\.?[ ]*пом\.?|,?[ ]*кв\.?[ ]*к\.?|,?[ ]*кв\.?)[ ]*([0-9]+[ ]*[а-дА-Д]?(?:[ ]*(?:[ ]*-[ ]*|[ ]*,[ ]*|,?[ ]*квартира\.?|,?[ ]*кв\.?[ ]*комн\.?|,?[ ]*кв\.?[ ]*ком\.?|,?[ ]*кв\.?[ ]*пом\.?|,?[ ]*кв\.?[ ]*к\.?|,?[ ]*кв\.?)[ ]*(?:[0-9]+[ ]*[а-дА-Д]?))*)(.*)$";
var matches = Regex.Match(premiseAddress.RawAddress.Trim().ToLower(), regExpression);
if (matches.Groups.Count < 4)
{
var copyPremise = PartialCopyPremise(premiseAddress);
copyPremise.Description = "Invalid parse address";
var emptyAddress = new List <List <Premise> >
{
new List <Premise> {
copyPremise
}
};
return(new List <List <Premise> >(emptyAddress));
}
var sleshedRooms = false;
var street = matches.Groups[1].Value;
var house = AddressHelper.NormalizeHouse(matches.Groups[2].Value);
var premises = AddressHelper.NormalizePremises(matches.Groups[3].Value);
string subPremises = null;
if (matches.Groups.Count > 4)
{
subPremises = AddressHelper.NormalizeSubPremises(matches.Groups[4].Value);
subPremises = subPremises.Trim(',');
if (subPremises.StartsWith("/"))
{
sleshedRooms = true;
subPremises = subPremises.Trim('/').Replace('/', ',');
}
}
var addresses = new List <List <Premise> >();
if (string.IsNullOrEmpty(subPremises))
{
var newAddress = PartialCopyPremise(premiseAddress);
newAddress.SubPremises = new List <SubPremise>();
newAddress.PremiseNumber = premises;
newAddress.House = house;
newAddress.Street = street;
addresses.Add(new List <Premise> {
newAddress
});
var premisesArray = premises.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (premisesArray.Length <= 1)
{
return(addresses);
}
var newAddressesList = new List <Premise>();
foreach (var premise in premisesArray)
{
newAddress = PartialCopyPremise(premiseAddress);
newAddress.SubPremises = new List <SubPremise>();
newAddress.PremiseNumber = premise;
newAddress.House = house;
newAddress.Street = street;
newAddressesList.Add(newAddress);
}
addresses.Add(newAddressesList);
}
else
{
var newAddress = PartialCopyPremise(premiseAddress);
newAddress.Street = street;
newAddress.House = house;
newAddress.PremiseNumber = premises;
newAddress.SubPremises = new List <SubPremise>();
var subPremisesArray = subPremises.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var subPremise in subPremisesArray)
{
newAddress.SubPremises.Add(new SubPremise {
SubPremiseNumber = subPremise
});
}
addresses.Add(new List <Premise> {
newAddress
});
var subPremisesList = subPremises.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (sleshedRooms && !Regex.IsMatch(subPremises, @"[А-Я](,[А-Я])*"))
{
for (var i = 1; i <= subPremisesList.Length; i++)
{
newAddress = PartialCopyPremise(premiseAddress);
newAddress.Street = street;
newAddress.House = house;
newAddress.PremiseNumber = premises + "," +
subPremisesList.Take(i).Aggregate((v, acc) => v + "," + acc);
newAddress.SubPremises = subPremisesList.Length > 1
? subPremisesList.Skip(i)
.Select(subPremise => new SubPremise {
SubPremiseNumber = subPremise
})
.ToList()
: new List <SubPremise>();
addresses.Add(new List <Premise> {
newAddress
});
}
if (subPremisesList.Length > 0)
{
var premisesArray = (premises + "," + subPremisesList.Aggregate((v, acc) => v + "," + acc)).
Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
var newAddressesList = new List <Premise>();
foreach (var premise in premisesArray)
{
newAddress = PartialCopyPremise(premiseAddress);
newAddress.Street = street;
newAddress.House = house;
newAddress.PremiseNumber = premise;
newAddress.SubPremises = new List <SubPremise>();
newAddressesList.Add(newAddress);
}
addresses.Add(newAddressesList);
}
}
}
return(addresses);
}
