public Propertyb Select(String sourceID) { using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT * FROM propertyb WHERE sourceid=@sourceID"; command.Parameters.Add("sourceid", MySqlDbType.VarChar).Value = sourceID; MySqlDataReader dr = command.ExecuteReader(); Propertyb m = null; if (dr.Read()) { m = new Propertyb(); m.ID = GetValue <int>(dr["id"]); m.SourceID = GetValue <String>(dr["sourceid"]); m.Price = GetValue <int>(dr["price"]); m.Country = GetValue <String>(dr["country"]); m.City = GetValue <String>(dr["city"]); m.PostalCode = GetValue <String>(dr["postal_code"]); m.URLPost = GetValue <String>(dr["urlpost"]); m.InternalPrefix = GetValue <String>(dr["internalprefix"]); m.Image = GetValue <String>(dr["image"]); m.Video = GetValue <String>(dr["video"]); m.ContactName = GetValue <String>(dr["contactname"]); m.ContactEmail = GetValue <String>(dr["contactemail"]); m.ContactMobile = GetValue <String>(dr["contactmobile"]); m.Title = GetValue <String>(dr["title"]); m.Address = GetValue <String>(dr["address"]); m.Lat_Coordinate = GetValue <Decimal>(dr["lat_coordinate"]); m.Long_Coordinate = GetValue <Decimal>(dr["long_coordinate"]); m.Usage = GetValue <String>(dr["usage"]); m.Description = GetValue <String>(dr["description"]); m.InitialPrice = GetValue <int>(dr["initialprice"]); m.LastPrice = GetValue <int>(dr["lastprice"]); m.NumberPriceChanges = GetValue <int>(dr["numberpricechanges"]); m.Expenses = GetValue <int>(dr["expenses"]); m.PriceWithExp = GetValue <int>(dr["pricewithexp"]); m.BasicCharacteristics = GetValue <String>(dr["basiccharacteristics"]); m.TotalSurface = GetValue <int>(dr["totalsurface"]); m.SurfaceEdificable = GetValue <int>(dr["surfaceedificable"]); m.MiniumSellSurface = GetValue <int>(dr["minimumsellsurface"]); m.Access = GetValue <String>(dr["access"]); m.UrbanisticSituation = GetValue <String>(dr["urbanisticsituation"]); m.EquipmentWater = GetValue <String>(dr["equipment_water"]); m.EquipmentElectricity = GetValue <String>(dr["equipment_electricity"]); m.EquipmentSewerSystem = GetValue <String>(dr["equipment_sewer_system"]); m.EquipmentNaturalGus = GetValue <String>(dr["equipment_natural_gas"]); m.EquipmentStreetLighting = GetValue <String>(dr["equipment_street_lighting"]); m.EquipmentPavements = GetValue <String>(dr["equipment_pavements"]); } return(m); } }
public int Insert(Propertyb m) { using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = "INSERT INTO propertyb(sourceid,price,country,city,postal_code,urlpost,internalprefix,image,video,contactname,contactemail,contactmobile,title,address,lat_coordinate,long_coordinate,usage,description,initialprice,lastprice,numberpricechanges,expenses,pricewithexp,basiccharacteristics,totalsurface,surfaceedificable,minimumsellsurface,access,urbanisticsituation,equipment_water,equipment_electricity,equipment_sewer_system,equipment_natural_gas,equipment_street_lighting,equipment_pavements)" + " VALUES(@SourceID,@Price,@Country,@City,@PostalCode,@URLPost,@InternalPrefix,@Image,@Video,@ContactName,@ContactEmail,@ContactMobile,@Title,@Address" + ",@Lat_Coordinate,@Long_Coordinate,@Usage,@Description,@InitialPrice,@LastPrice,@NumberPriceChanges" + ",@Expenses,@PriceWithExp,@BasicCharacteristics,@TotalSurface,@SurfaceEdificable,@MiniumSellSurface,@Access,@UrbanisticSituation" + ",@EquipmentWater,@EquipmentElectricity,@EquipmentSewerSystem,@EquipmentNaturalGus,@EquipmentStreetLighting,@EquipmentPavements)"; command.Parameters.Add("SourceID", MySqlDbType.VarChar).Value = m.SourceID; command.Parameters.Add("Price", MySqlDbType.Int32).Value = m.Price; command.Parameters.Add("Country", MySqlDbType.VarChar).Value = m.Country; command.Parameters.Add("City", MySqlDbType.VarChar).Value = m.City; command.Parameters.Add("PostalCode", MySqlDbType.VarChar).Value = m.PostalCode; command.Parameters.Add("URLPost", MySqlDbType.VarChar).Value = m.URLPost; command.Parameters.Add("InternalPrefix", MySqlDbType.VarChar).Value = m.InternalPrefix; command.Parameters.Add("Image", MySqlDbType.VarChar).Value = m.Image; command.Parameters.Add("Video", MySqlDbType.VarChar).Value = m.Video; command.Parameters.Add("ContactName", MySqlDbType.VarChar).Value = m.ContactName; command.Parameters.Add("ContactEmail", MySqlDbType.VarChar).Value = m.ContactEmail; command.Parameters.Add("ContactMobile", MySqlDbType.VarChar).Value = m.ContactMobile; command.Parameters.Add("Title", MySqlDbType.VarChar).Value = m.Title; command.Parameters.Add("Address", MySqlDbType.VarChar).Value = m.Address; command.Parameters.Add("Lat_Coordinate", MySqlDbType.Decimal).Value = m.Lat_Coordinate; command.Parameters.Add("Long_Coordinate", MySqlDbType.Decimal).Value = m.Long_Coordinate; command.Parameters.Add("Usage", MySqlDbType.VarChar).Value = m.Usage; command.Parameters.Add("Description", MySqlDbType.Text).Value = m.Description; command.Parameters.Add("InitialPrice", MySqlDbType.Int32).Value = m.InitialPrice; command.Parameters.Add("LastPrice", MySqlDbType.String).Value = m.LastPrice; command.Parameters.Add("NumberPriceChanges", MySqlDbType.String).Value = m.NumberPriceChanges; command.Parameters.Add("Expenses", MySqlDbType.String).Value = m.Expenses; command.Parameters.Add("PriceWithExp", MySqlDbType.String).Value = m.PriceWithExp; command.Parameters.Add("BasicCharacteristics", MySqlDbType.String).Value = m.BasicCharacteristics; command.Parameters.Add("TotalSurface", MySqlDbType.String).Value = m.TotalSurface; command.Parameters.Add("SurfaceEdificable", MySqlDbType.String).Value = m.SurfaceEdificable; command.Parameters.Add("MiniumSellSurface", MySqlDbType.String).Value = m.MiniumSellSurface; command.Parameters.Add("Access", MySqlDbType.String).Value = m.Access; command.Parameters.Add("UrbanisticSituation", MySqlDbType.String).Value = m.UrbanisticSituation; command.Parameters.Add("EquipmentWater", MySqlDbType.String).Value = m.EquipmentWater; command.Parameters.Add("EquipmentElectricity", MySqlDbType.String).Value = m.EquipmentElectricity; command.Parameters.Add("EquipmentSewerSystem", MySqlDbType.String).Value = m.EquipmentSewerSystem; command.Parameters.Add("EquipmentNaturalGus", MySqlDbType.String).Value = m.EquipmentNaturalGus; command.Parameters.Add("EquipmentStreetLighting", MySqlDbType.String).Value = m.EquipmentStreetLighting; command.Parameters.Add("EquipmentPavements", MySqlDbType.String).Value = m.EquipmentPavements; return(command.ExecuteNonQuery()); } }
public void ScrapePages(List <String> urlList, String usage, Boolean forceUpdate) { int urlCount = urlList.Count; using (PropertybDao dao = new PropertybDao()) { for (int i = 0; i < urlCount; i++) { String url = urlList[i]; String page = httpClient.RequestGet(HOST_ADDRESS + url); HtmlDocument doc = new HtmlDocument(); doc.LoadHtml(page); HtmlNode documentNode = doc.DocumentNode; Propertyb m = new Propertyb(); { // SourceID var nodes = documentNode.CssSelect("input[name='adId']"); if (nodes.Count() < 1) { throw new NullReferenceException("SourceID not found."); } var node = nodes.First(); m.SourceID = node.GetAttributeValue("value"); } { // Price var nodes = documentNode.CssSelect("div.toggle-price section.price-features__container p strong"); if (nodes.Count() < 1) { throw new NullReferenceException("Price not found."); } var node = nodes.First(); m.Price = Convert.ToInt32(GetNumbers(node.GetDirectInnerText())); } { // Country m.Country = CountryName; } { // City var nodes = documentNode.CssSelect("div#headerMap ul li"); if (nodes.Count() < 1) { throw new NullReferenceException("City not found."); } var node = nodes.Last(); String location = node.GetDirectInnerText(); String[] array = location.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries); m.City = array.Last().Trim(); } { // URLPost m.URLPost = url; } { // InternalPrefix m.InternalPrefix = InternalPrefix; } { // ContactName var nodes = documentNode.CssSelect("aside.side-content section a.about-advertiser-name"); if (nodes.Count() > 0) { var node = nodes.First(); m.ContactName = node.GetDirectInnerText().Trim(); } } // contact email //TODO // contact mobile //TODO { // Title var nodes = documentNode.CssSelect("div.main-info__title span.main-info__title-main"); if (nodes.Count() < 1) { throw new NullReferenceException("Title not found."); } var node = nodes.First(); m.Title = node.GetDirectInnerText().Trim(); } { // Address var nodes = documentNode.CssSelect("div#headerMap ul li"); if (nodes.Count() < 1) { throw new NullReferenceException("Address not found."); } var node = nodes.First(); String address = node.GetDirectInnerText().Trim(); m.Address = address + ", " + m.City; } { // Usage m.Usage = usage; } { //Description var nodes = documentNode.CssSelect("div.commentsContainer div.adCommentsLanguage"); if (nodes.Count() < 1) { throw new NullReferenceException("Description not found."); } var node = nodes.First(); m.Description = node.InnerText.Trim(); } // InitialPrice { var nodes = documentNode.CssSelect("section#simulator-container div.item-form"); if (nodes.Count() < 1) { throw new NullReferenceException("Last Price not found."); } nodes = nodes.First().CssSelect("span"); if (nodes.Count() < 1) { throw new NullReferenceException("Last Price not found."); } var node = nodes.Last(); m.LastPrice = Convert.ToInt32(GetNumbers(node.GetDirectInnerText())); } // NumberPriceChanges { // Expenses var nodes = documentNode.CssSelect("section#simulator-container div.item-form.js-taxes"); if (nodes.Count() < 1) { throw new NullReferenceException("Expenses Price not found."); } nodes = nodes.First().CssSelect("span"); if (nodes.Count() < 1) { throw new NullReferenceException("Expenses Price not found."); } var node = nodes.Last(); m.Expenses = Convert.ToInt32(GetNumbers(node.GetDirectInnerText())); } { // PriceWithExp var nodes = documentNode.CssSelect("section#simulator-container div.item-form.js-price-with-expenses"); if (nodes.Count() < 1) { throw new NullReferenceException("PriceWithExp not found."); } nodes = nodes.First().CssSelect("span"); if (nodes.Count() < 1) { throw new NullReferenceException("PriceWithExp not found."); } var node = nodes.Last(); m.PriceWithExp = Convert.ToInt32(GetNumbers(node.GetDirectInnerText())); } { // BasicCharacteristics var nodes = documentNode.CssSelect("section#details div.details-property-feature-one div.details-property_features"); if (nodes.Count() > 0) { nodes = nodes.First().CssSelect("ul"); if (nodes.Count() > 0) { var node = nodes.First(); m.BasicCharacteristics = node.InnerText.Trim(); } } } { // TotalSurface, MiniumSellSurface, SurfaceEdificable, Access var nodes = documentNode.CssSelect("section#details div.details-property-feature-one div.details-property_features ul"); if (nodes.Count() > 0) { nodes = nodes.First().CssSelect("li"); if (nodes.Count() > 0) { foreach (var node in nodes) { String text = node.GetDirectInnerText(); if (text.Contains("total")) { m.TotalSurface = Convert.ToInt32(GetNumbers(text)); } else if (text.Contains("minima")) { m.MiniumSellSurface = Convert.ToInt32(GetNumbers(text)); } else if (text.Contains("edificable")) { m.SurfaceEdificable = Convert.ToInt32(GetNumbers(text)); } else if (text.Contains("Acceso via")) { m.Access = text.Substring("Acceso via ".Length).Trim(); } } } } } { // UrbanisticSituation var nodes = documentNode.CssSelect("section#details div.details-property-feature-two div.details-property_features ul"); if (nodes.Count() > 0) { var node = nodes.First(); m.UrbanisticSituation = node.InnerText.Trim(); } } { // Equipment var nodes = documentNode.CssSelect("section#details div.details-property-feature-two div.details-property_features ul"); if (nodes.Count() > 1) { var node = nodes.ElementAt(1); m.Equipment = node.InnerText.Trim(); } } // postal code //TODO // lat, long // image, video // Propertyb o = dao.Select(m.SourceID); if (o == null) { dao.Insert(m); HttpClient.Print($"Added\t\t\"{usage}\" ({i + 1}/{urlCount}) : {url}"); } else if (forceUpdate) { if (m.LastPrice != o.LastPrice) { m.InitialPrice = o.LastPrice; m.NumberPriceChanges++; dao.Update(m); HttpClient.Print($"Updated + Price\t\"{usage}\" ({i + 1}/{urlCount}) : {url}"); } else { dao.Update(m); HttpClient.Print($"Updated (Force)\t\"{usage}\" ({i + 1}/{urlCount}) : {url}"); } } else if (m.LastPrice != o.LastPrice) { m.InitialPrice = o.LastPrice; m.NumberPriceChanges++; dao.Update(m); HttpClient.Print($"Updated + Price\t\"{usage}\" ({i + 1}/{urlCount}) : {url}"); } if (forceUpdate && o != null) { String pattern = "var adMultimediasInfo="; int p1 = page.IndexOf(pattern); p1 += pattern.Length; int p2 = page.IndexOf(";", p1); String jsonString = page.Substring(p1, p2 - p1); var json = JObject.Parse(jsonString); JArray array = (JArray)json["fullScreenGalleryPics"]; if (array.Count > 0) { String downloadPath = BaseDownloadDirectory + $"\\{InternalPrefix}{m.SourceID}"; DirectoryInfo downloadDirectoryInfo = new DirectoryInfo(downloadPath); if (!downloadDirectoryInfo.Exists) { downloadDirectoryInfo.Create(); } String image = ""; foreach (JToken j in array) { String src = (String)j["src"]; String filename = Path.GetFileName(src); image += filename + ";"; HttpClient.Print($"\tDownloading : " + src); httpClient.DownloadFile(src, filename); } } } } } }
public int Update(Propertyb m) { using (MySqlCommand command = connection.CreateCommand()) { command.CommandText = "UPDATE propertyb SET " + "price=@Price," + "country=@Country," + "city=@City," + "postal_code=@PostalCode," + "urlpost=@URLPost," + "internalprefix=@InternalPrefix," + "image=@Image," + "video=@Video," + "contactname=@ContactName," + "contactemail=@ContactEmail," + "contactmobile=@ContactMobile," + "title=@Title," + "address=@Address," + "lat_coordinate=@Lat_Coordinate," + "long_coordinate=@Long_Coordinate," + "usage=@Usage," + "description=@Description," + "initialprice=@InitialPrice," + "lastprice=@LastPrice," + "numberpricechanges=@NumberPriceChanges," + "expenses=@Expenses," + "pricewithexp=@PriceWithExp," + "basiccharacteristics=@BasicCharacteristics," + "totalsurface=@TotalSurface," + "surfaceedificable=@SurfaceEdificable," + "minimumsellsurface=@MiniumSellSurface," + "access=@Access," + "urbanisticsituation=@UrbanisticSituation," + "equipment_water=@EquipmentWater," + "equipment_electricity=@EquipmentElectricity," + "equipment_sewer_system=@EquipmentSewerSystem," + "equipment_natural_gas=@EquipmentNaturalGus," + "equipment_street_lighting=@EquipmentStreetLighting," + "equipment_pavements=@EquipmentPavements " + "WHERE sourceid=@SourceID"; command.Parameters.Add("Price", MySqlDbType.Int32).Value = m.Price; command.Parameters.Add("Country", MySqlDbType.VarChar).Value = m.Country; command.Parameters.Add("City", MySqlDbType.VarChar).Value = m.City; command.Parameters.Add("PostalCode", MySqlDbType.VarChar).Value = m.PostalCode; command.Parameters.Add("URLPost", MySqlDbType.VarChar).Value = m.URLPost; command.Parameters.Add("InternalPrefix", MySqlDbType.VarChar).Value = m.InternalPrefix; command.Parameters.Add("Image", MySqlDbType.VarChar).Value = m.Image; command.Parameters.Add("Video", MySqlDbType.VarChar).Value = m.Video; command.Parameters.Add("ContactName", MySqlDbType.VarChar).Value = m.ContactName; command.Parameters.Add("ContactEmail", MySqlDbType.VarChar).Value = m.ContactEmail; command.Parameters.Add("ContactMobile", MySqlDbType.VarChar).Value = m.ContactMobile; command.Parameters.Add("Title", MySqlDbType.VarChar).Value = m.Title; command.Parameters.Add("Address", MySqlDbType.VarChar).Value = m.Address; command.Parameters.Add("Lat_Coordinate", MySqlDbType.Decimal).Value = m.Lat_Coordinate; command.Parameters.Add("Long_Coordinate", MySqlDbType.Decimal).Value = m.Long_Coordinate; command.Parameters.Add("Usage", MySqlDbType.VarChar).Value = m.Usage; command.Parameters.Add("Description", MySqlDbType.Text).Value = m.Description; command.Parameters.Add("InitialPrice", MySqlDbType.Int32).Value = m.InitialPrice; command.Parameters.Add("LastPrice", MySqlDbType.String).Value = m.LastPrice; command.Parameters.Add("NumberPriceChanges", MySqlDbType.String).Value = m.NumberPriceChanges; command.Parameters.Add("Expenses", MySqlDbType.String).Value = m.Expenses; command.Parameters.Add("PriceWithExp", MySqlDbType.String).Value = m.PriceWithExp; command.Parameters.Add("BasicCharacteristics", MySqlDbType.String).Value = m.BasicCharacteristics; command.Parameters.Add("TotalSurface", MySqlDbType.String).Value = m.TotalSurface; command.Parameters.Add("SurfaceEdificable", MySqlDbType.String).Value = m.SurfaceEdificable; command.Parameters.Add("MiniumSellSurface", MySqlDbType.String).Value = m.MiniumSellSurface; command.Parameters.Add("Access", MySqlDbType.String).Value = m.Access; command.Parameters.Add("UrbanisticSituation", MySqlDbType.String).Value = m.UrbanisticSituation; command.Parameters.Add("EquipmentWater", MySqlDbType.String).Value = m.EquipmentWater; command.Parameters.Add("EquipmentElectricity", MySqlDbType.String).Value = m.EquipmentElectricity; command.Parameters.Add("EquipmentSewerSystem", MySqlDbType.String).Value = m.EquipmentSewerSystem; command.Parameters.Add("EquipmentNaturalGus", MySqlDbType.String).Value = m.EquipmentNaturalGus; command.Parameters.Add("EquipmentStreetLighting", MySqlDbType.String).Value = m.EquipmentStreetLighting; command.Parameters.Add("EquipmentPavements", MySqlDbType.String).Value = m.EquipmentPavements; command.Parameters.Add("SourceID", MySqlDbType.VarChar).Value = m.SourceID; return(command.ExecuteNonQuery()); } }