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