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