//Print values to resultbox, called from FullList
public void ShowFull(FullList Print)
{
ResultBox.Text += Print.PrintF();
}
private void scrapeButton_Click(object sender, EventArgs e)
{
#region Var declarations
string rNumber = "";
string rStreet = "";
string number = "";
string name = "";
string kappa3 = "";
string kappa4 = "";
string Rates = "";
string PAddress = "";
string LV = "";
string IV = "";
string RV = "";
number += NumberBox.Text.ToUpper();
name += AddressBox.Text;
#endregion
#region AgilityPack declaration
HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
HtmlWeb hw = new HtmlWeb();
bool valid;
//Checking link is valid and (in this scope) only from Trademe else raise error
if (urlBox.Text.StartsWith(@"https://www.trademe.co.nz/a/property/residential/sale/"))
{
doc = hw.Load(urlBox.Text);
valid = true;
}
else
{
MessageBox.Show("Please enter a valid Trademe link", "Error", MessageBoxButtons.OK);
valid = false;
}
if (valid == true)
{
if (cityBox.SelectedIndex == 0)
{
#endregion
#region AgilityPack logic
var ProductsHtml = doc.DocumentNode.Descendants("div")
.Where(node => node.GetAttributeValue("class", "")
.Equals("tm-property-listing-body__container")).ToList();
var ProductListItems = ProductsHtml[0].Descendants("section")
.Where(node => node.GetAttributeValue("class", "")
.Contains("l-container")).ToList();
//Find Listing ID
var ListingID = doc.DocumentNode.SelectSingleNode("//div[@class='tm-property-listing__listing-metadata-listingid']").InnerText.TrimStart();
//Find address
try
{
PAddress = doc.DocumentNode.SelectSingleNode("//h1[@class='tm-property-listing-body__location p-h3']").InnerText.TrimStart().ToString();
} catch (Exception ex)
{
}
//Find sellprice
var Sellprice = doc.DocumentNode.SelectSingleNode("//h2/strong[1]").InnerText.TrimStart();
#endregion
#region AgilityPack results
var aSplit = PAddress.Split(' '); //To select specific strings
string aNumber = aSplit[0]; //Get street number
string aSuburb = aSplit[3]; //Get suburb
string aStreet = aSplit[1] + " " + aSplit[2]; //Get street name
rNumber += aNumber;
rStreet += aStreet;
NumberBox.Text = rNumber; //To show user street number
AddressBox.Text = rStreet; //To show street address
number += NumberBox.Text.ToUpper();
name += AddressBox.Text;
int index = number.IndexOf("/"); //helper for below
string index2 = number.Substring(index + 1); //helper for below
string index3 = Regex.Replace(index2, "[^0-9]", ""); //removes alphabet
string index4 = number.Substring(0, 1); //gets first char in string
var digits = new[] { '0' };
foreach (char c in number)
{
kappa3 += NumFromLetter(c);
kappa4 += kappa3.Trim('0');
}
string unitindex = "Unit " + index4 + ","; // unit if address like 1/
string flatindex = "Flat " + index4 + ","; // flat if ""
string unitk4 = "Unit " + kappa4 + ","; // unit if address like 123a
string flatk4 = "Flat " + kappa4 + ","; // flat if ""
string slashindex = kappa4 + "/" + index3;
Regex.IsMatch(number, @"^[a-zA-Z]+$");
string trimcomma = name.Trim(',');
#endregion
#region Selenium declaration
var chromeOptions = new ChromeOptions();
chromeOptions.AddArguments("headless");
#endregion
var chromeDriverService = ChromeDriverService.CreateDefaultService();
chromeDriverService.HideCommandPromptWindow = true;
#region Selenium logic
var browser = new ChromeDriver(chromeDriverService, chromeOptions);
//Rates starts here
browser.Navigate().GoToUrl("https://ccc.govt.nz/services/rates-and-valuations/rates-and-valuation-search");
Thread.Sleep(2000);
browser.SwitchTo().Frame(0);
//Normal search
var search = browser.FindElement(By.CssSelector("#ucAddressSearch_txtStreetNumberName"));
search.Click();
search.SendKeys(number + " " + trimcomma);
var find = browser.FindElement(By.CssSelector("#ucAddressSearch_txtStreetNumberName"));
find.Click();
Thread.Sleep(3000);
if (number.Any(Char.IsLetter))
{
var findresult1 = browser.FindElement(By.XPath("//*[text()[contains(., '" + number + "')]] | //*[text()[contains(., '" + unitk4 + "')]] | //*[text()[contains(., '" + flatk4 + "')]]"));
findresult1.Click();
}
else
{
var findresult2 = browser.FindElement(By.XPath("//*[text()[contains(., '" + number + "')]] | //*[text()[contains(., '" + unitindex + "')]] | //*[text()[contains(., '" + flatindex + "')]]"));
findresult2.Click();
}
Thread.Sleep(3000);
try
{
Rates = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropRatesTotAmd")).Text;
LV = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropLandValNext")).Text;
IV = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropImprValNext")).Text;
RV = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropCapValNext")).Text;
}
catch (NoSuchElementException)
{
DialogResult result = MessageBox.Show("Not found, try alternative search?", "Confirmation", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
scrapeButton.Enabled = false;
altSLabel.Enabled = true;
altSLabel.Visible = true;
}
else
{
scrapeButton.Enabled = false;
altSLabel.Enabled = true;
altSLabel.Visible = true;
}
}
Thread.Sleep(6000);
chromeOptions.AddArguments("headless");
#endregion
#region Selenium logic
//Homes starts here
var browser2 = new ChromeDriver(chromeDriverService, chromeOptions);
browser2.Navigate().GoToUrl("https://homes.co.nz/map/christchurch?lng=172.63949105013296&lat=-43.52800050205544&zoom=14&filter=sold");
Thread.Sleep(10000);
try
{
var search2 = browser2.FindElement(By.CssSelector("#autocomplete-search"));
search.Click();
search.SendKeys(PAddress);
}
catch (StaleElementReferenceException ex)
{
var search2 = browser2.FindElement(By.CssSelector("#autocomplete-search"));
search2.Click();
search2.SendKeys(PAddress);
}
Thread.Sleep(2000);
try
{
var findtext = browser2.FindElement(By.XPath("//div[text()[contains(., '" + number + "')]]"));
Thread.Sleep(1000);
findtext.Click();
}
catch (Exception ex)
{
var findtext = browser2.FindElement(By.XPath("//div[text()[contains(., '" + number + "')]]"));
Thread.Sleep(1000);
findtext.Click();
}
var button = browser2.FindElement(By.XPath("//span[@class='buttonContent']"));
button.Click();
IList <string> all = new List <string>();
IList <string> all1 = new List <string>();
Thread.Sleep(5000);
//Add price and address to list
foreach (var element in browser2.FindElements(By.XPath("//div/div[@class='priceContainer' and 1]/h3[@class='price' and 1]")))
{
all.Add(element.Text);
}
foreach (var element in browser2.FindElements(By.XPath("//div/div[@class='addressPrice' and 1]/h2[@class='address' and 1]")))
{
all1.Add(element.Text);
}
List <string> lstSchool = new List <string>();
Thread.Sleep(3000);
//Zip list
foreach (var nw in all.Zip(all1, Tuple.Create).ToList())
{
lstSchool.Add(nw.Item1 + " " + nw.Item2);
}
//Show list to resultbox
FullList Full = new FullList(ListingID, PAddress, Sellprice, Rates, LV, IV, RV, lstSchool);
ShowFull(Full);
//SQL Insert logic
string connString = FinalGUI3.Properties.Settings.Default.DB3ConnectionString;
SqlConnection sqlConn = new SqlConnection(connString);
string sql_Text = "INSERT INTO dbo.P (ListingID, PAddress, Sellprice, Rates, LV, IV, RV) VALUES (@ListingID, @PAddress, @Sellprice, @Rates, @LV, @IV, @RV);SELECT scope_identity();";
string sql_Text2 = "INSERT INTO dbo.R (SPrice, PropertyID) VALUES (@SPrice, @id)";
SqlCommand cmd = new SqlCommand(sql_Text, sqlConn);
SqlCommand cmd2 = new SqlCommand(sql_Text2, sqlConn);
sqlConn.Open();
cmd.Parameters.AddWithValue("@ListingID", ListingID);
cmd.Parameters.AddWithValue("@PAddress", PAddress);
cmd.Parameters.AddWithValue("@Sellprice", Sellprice);
cmd.Parameters.AddWithValue("@Rates", Rates);
cmd.Parameters.AddWithValue("@LV", LV);
cmd.Parameters.AddWithValue("@IV", IV);
cmd.Parameters.AddWithValue("@RV", RV);
decimal decimalBrandId = (decimal)cmd.ExecuteScalar();
int NewBrandId = (int)decimalBrandId;
foreach (var nw in all.Zip(all1, Tuple.Create).ToList())
{
cmd2.Parameters.Clear();
cmd2.Parameters.AddWithValue("@id", NewBrandId);
cmd2.Parameters.AddWithValue("@SPrice", (nw.Item1 + " " + nw.Item2 + "#"));
cmd2.ExecuteNonQuery();
}
cmd.ExecuteNonQuery();
sqlConn.Close();
//Create dynamic textbox
TextBox at = new TextBox();
at.Multiline = true;
at.ReadOnly = true;
const int x_margin = 0;
const int y_margin = 2;
at.Text = ListingID.ToString() + "\r\n" + PAddress.ToString() + "\r\n" + Sellprice.ToString()
+ "\r\n" + Rates.ToString() + "\r\n" + LV.ToString() + "\r\n" + IV.ToString() + "\r\n" + RV.ToString() + string.Join(Environment.NewLine, lstSchool);
Size size = TextRenderer.MeasureText(at.Text, at.Font);
at.ClientSize =
new Size(size.Width + x_margin, size.Height + y_margin);
flowLayoutPanel1.Controls.Add(at);
tabControl1.SelectedIndex = 1;
browser.Close();
browser.Quit();
}
else
{
MessageBox.Show("Please select Christchurch", "Error", MessageBoxButtons.OK);
}
}
}
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
#region Var declarations
string result2 = "";
string result3 = "";
string number = "";
string name = "";
string kappa3 = "";
string kappa4 = "";
string Rates = "";
string LV = "";
string IV = "";
string RV = "";
number += NumberBox.Text.ToUpper();
name += AddressBox.Text;
#endregion
#region AgilityPack declaration
HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
HtmlWeb hw = new HtmlWeb();
doc = hw.Load(urlBox.Text);
#endregion
#region AgilityPack logic
var ProductsHtml = doc.DocumentNode.Descendants("div")
.Where(node => node.GetAttributeValue("class", "")
.Equals("tm-property-listing-body__container")).ToList();
var ProductListItems = ProductsHtml[0].Descendants("section")
.Where(node => node.GetAttributeValue("class", "")
.Contains("l-container")).ToList();
var ListingID = doc.DocumentNode.SelectSingleNode("//div[@class='tm-property-listing__listing-metadata-listingid']").InnerText.TrimStart();
var PAddress = doc.DocumentNode.SelectSingleNode("//h1[@class='tm-property-listing-body__location p-h3 ng-star-inserted']").InnerText.TrimStart();
var Sellprice = doc.DocumentNode.SelectSingleNode("//h2/strong[1]").InnerText.TrimStart();
#endregion
#region AgilityPack results
var kappa = PAddress.Split(' ');
string a = kappa[0]; //Get street number
string d = kappa[3];
string b = kappa[1] + " " + kappa[2]; //Get street name
result2 += a;
result3 += b;
NumberBox.Text = result2;
AddressBox.Text = result3;
number += NumberBox.Text.ToUpper();
name += AddressBox.Text;
int index = number.IndexOf("/"); //helper for below
string index2 = number.Substring(index + 1); //helper for below
string index3 = Regex.Replace(index2, "[^0-9]", ""); //removes alphabet
string index4 = number.Substring(0, 1); //gets first char in string
var digits = new[] { '0' };
foreach (char c in number)
{
kappa3 += NumFromLetter(c);
kappa4 += kappa3.Trim('0');
}
string unitindex = "Unit " + index4 + ","; // unit if address like 1/
string flatindex = "Flat " + index4 + ","; // flat if ""
string unitk4 = "Unit " + kappa4 + ","; // unit if address like 123a
string flatk4 = "Flat " + kappa4 + ","; // flat if ""
string slashindex = kappa4 + "/" + index3;
Regex.IsMatch(number, @"^[a-zA-Z]+$");
string trimcomma = name.Trim(',');
#endregion
#region Selenium declaration
var chromeOptions = new ChromeOptions();
chromeOptions.AddArguments("headless");
//chromeOptions.AddArguments("--start-maximized");
#endregion
#region Selenium logic
var browser = new ChromeDriver(chromeOptions);
browser.Navigate().GoToUrl("https://ccc.govt.nz/services/rates-and-valuations/rates-and-valuation-search");
Thread.Sleep(2000);
browser.SwitchTo().Frame(0);
//Alternative search
var searchField = browser.FindElement(By.CssSelector("#ucAddressSearch_lbFullSearch"));
searchField.Click();
var searchField2 = browser.FindElement(By.CssSelector("#ucAddressSearch_txtStartStreetNo"));
searchField2.Click();
searchField2.SendKeys(index3);
var searchField3 = browser.FindElement(By.CssSelector("#ucAddressSearch_txtToStreetNo"));
searchField3.Click();
searchField3.SendKeys(index3);
var searchField4 = browser.FindElement(By.CssSelector("#ucAddressSearch_txtStreetName"));
searchField4.Click();
searchField4.SendKeys(trimcomma);
var searchField6 = browser.FindElement(By.CssSelector("#ucAddressSearch_btnSearch"));
searchField6.Click();
Thread.Sleep(3000);
if (number.Any(Char.IsLetter))
{
try
{
var findresult1 = browser.FindElement(By.XPath("//*[text()[contains(., '" + number + "')]] | //*[text()[contains(., '" + unitk4 + "')]] | //*[text()[contains(., '" + flatk4 + "')]]"));
findresult1.Click();
}
catch (NoSuchElementException)
{
}
}
else
{
try
{
var findresult2 = browser.FindElement(By.XPath("//*[text()[contains(., '" + number + "')]] | //*[text()[contains(., '" + unitk4 + "')]] | //*[text()[contains(., '" + flatk4 + "')]] | //*[text()[contains(., '" + slashindex + "')]]"));
findresult2.Click();
}
catch (NoSuchElementException)
{
}
}
Thread.Sleep(5000);
Rates = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropRatesTotAmd")).Text;
LV = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropLandValNext")).Text;
IV = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropImprValNext")).Text;
RV = browser.FindElement(By.CssSelector("#ucPropertyInformation_tcPropertyView_tpProperty_ucProperties_lblPropCapValNext")).Text;
browser.Navigate().GoToUrl("https://homes.co.nz/map/christchurch?lng=172.63949105013296&lat=-43.52800050205544&zoom=14&filter=sold");
Thread.Sleep(4000);
//Normal search
try
{
var search2 = browser.FindElement(By.CssSelector("#autocomplete-search"));
search2.Click();
search2.SendKeys(number + " " + trimcomma);
}
catch (StaleElementReferenceException ex)
{
var search2 = browser.FindElement(By.CssSelector("#autocomplete-search"));
search2.Click();
search2.SendKeys(number + " " + trimcomma);
}
Thread.Sleep(2000);
var findtext = browser.FindElement(By.XPath("//div[text()[contains(., '" + number + "')]]"));
var find2 = findtext.FindElement(By.XPath("//div[text()[contains(., '" + d + "')]]"));
Thread.Sleep(2000);
var button = browser.FindElement(By.XPath("//span[@class='buttonContent']"));
button.Click();
Thread.Sleep(2000);
IList <string> all = new List <string>();
IList <string> all1 = new List <string>();
foreach (var element in browser.FindElements(By.XPath("//div/div[@class='priceContainer' and 1]/h3[@class='price' and 1]")))
{
all.Add(element.Text);
}
foreach (var element in browser.FindElements(By.XPath("//div/div[@class='addressPrice' and 1]/h2[@class='address' and 1]")))
{
all1.Add(element.Text);
}
List <string> lstSchool = new List <string>();
foreach (var nw in all.Zip(all1, Tuple.Create).ToList())
{
lstSchool.Add(nw.Item1 + " " + nw.Item2);
}
FullList Full = new FullList(ListingID, PAddress, Sellprice, Rates, LV, IV, RV, lstSchool);
ShowFull(Full);
tabControl1.SelectedIndex = 1;
browser.Close();
browser.Quit();
string connString = FinalGUI3.Properties.Settings.Default.DB3ConnectionString;
SqlConnection sqlConn = new SqlConnection(connString);
string sql_Text = "INSERT INTO dbo.P (ListingID, PAddress, Sellprice, Rates, LV, IV, RV) VALUES (@ListingID, @PAddress, @Sellprice, @Rates, @LV, @IV, @RV);SELECT scope_identity();";
string sql_Text2 = "INSERT INTO dbo.R (SPrice, PropertyID) VALUES (@SPrice, @id)";
SqlCommand cmd = new SqlCommand(sql_Text, sqlConn);
SqlCommand cmd2 = new SqlCommand(sql_Text2, sqlConn);
sqlConn.Open();
cmd.Parameters.AddWithValue("@ListingID", ListingID);
cmd.Parameters.AddWithValue("@PAddress", PAddress);
cmd.Parameters.AddWithValue("@Sellprice", Sellprice);
cmd.Parameters.AddWithValue("@Rates", Rates);
cmd.Parameters.AddWithValue("@LV", LV);
cmd.Parameters.AddWithValue("@IV", IV);
cmd.Parameters.AddWithValue("@RV", RV);
decimal decimalBrandId = (decimal)cmd.ExecuteScalar();
int NewBrandId = (int)decimalBrandId;
foreach (var nw in all.Zip(all1, Tuple.Create).ToList())
{
cmd2.Parameters.Clear();
cmd2.Parameters.AddWithValue("@id", NewBrandId);
cmd2.Parameters.AddWithValue("@SPrice", (nw.Item1 + " " + nw.Item2 + "#"));
cmd2.ExecuteNonQuery();
}
//cmd.ExecuteNonQuery();
sqlConn.Close();
TextBox at = new TextBox();
at.Multiline = true;
at.ReadOnly = true;
const int x_margin = 0;
const int y_margin = 2;
at.Text = ListingID.ToString() + "\r\n" + PAddress.ToString() + "\r\n" + Sellprice.ToString()
+ "\r\n" + Rates.ToString() + "\r\n" + LV.ToString() + "\r\n" + IV.ToString() + "\r\n" + RV.ToString() + string.Join(Environment.NewLine, lstSchool);
Size size = TextRenderer.MeasureText(at.Text, at.Font);
at.ClientSize =
new Size(size.Width + x_margin, size.Height + y_margin);
flowLayoutPanel1.Controls.Add(at);
cmd.ExecuteNonQuery();
sqlConn.Close();
tabControl1.SelectedIndex = 1;
browser.Close();
browser.Quit();
}
