public List <DBKeyword> list(int offset, int limit)
{
if (!this.OpenConnection())
{
return(new List <DBKeyword>());
}
//выбирае из бд инфу с определенным смещением, чтоб не нагружать оперативку
string query = "SELECT `keyword`, `id` FROM `keywords` LIMIT @limit OFFSET @offset;";
List <DBKeyword> list_kw = new List <DBKeyword>();
//Create Command
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@limit", limit);
cmd.Parameters.AddWithValue("@offset", offset);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
DBKeyword dbkw = new DBKeyword();
dbkw.keyword = "" + dataReader["keyword"];
dbkw.keyword_id = Int32.Parse("" + dataReader["id"]);
list_kw.Add(dbkw);
}
dataReader.Close();
this.CloseConnection();
return(list_kw);
}
private async void takeKW(DBKeyword kw, Boolean useSlicer = true)
{
ChromeDriver driver = this.initDriver(kw);
if (!driver.isSelectorExist(By.CssSelector(".report-download-button")))
{
driver.Close();
return;
}
IWebElement element = driver.FindElement(By.CssSelector(".report-download-button"));
string fileName = element.GetAttribute("download");
Console.WriteLine("Скачиваем:" + fileName);
element.SendKeys(Keys.Enter);
while (!File.Exists(fileName))
{
Thread.Sleep(YandexUtils.rndSleep());
}
driver.Close();
try
{
File.ReadLines(fileName)
.Skip(1)
.ToList()
.ForEach(s =>
{
string[] buf = s.Split(';');
string keyword = (new Regex("['\"]")).Replace(buf[0], "");
if (int.Parse(buf[1]) <= 7 && int.Parse(buf[4]) <= 100)
{
if (!this.isKeywordExist(keyword))
{
this.Insert(keyword);
}
if (useSlicer)
{
keyword.sliceAndTakeVariants();
}
}
});
}
catch (Exception e)
{
Console.WriteLine("Мы тут упали:" + e.Message);
}
File.Delete(fileName);
}
public List <DBKeyword> wordsForReport(long offset, int limit)
{
if (!this.OpenConnection())
{
return(new List <DBKeyword>());
}
//выбирае из бд инфу с определенным смещением, чтоб не нагружать оперативку
string query = "select t1.*from keywords as t1 where t1.id not in (select t2.Keywords_id from forecastinfo t2) LIMIT @limit OFFSET @offset";
List <DBKeyword> list_kw = new List <DBKeyword>();
try
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@limit", limit);
cmd.Parameters.AddWithValue("@offset", offset);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
if (!dataReader.HasRows)
{
dataReader.Close();
this.CloseConnection();
return(null);
}
//Read the data and store them in the list
while (dataReader.Read())
{
DBKeyword dbkw = new DBKeyword();
dbkw.keyword = "" + dataReader["keyword"];
dbkw.keyword_id = Int64.Parse("" + dataReader["id"]);
list_kw.Add(dbkw);
}
dataReader.Close();
}
catch { }
this.CloseConnection();
return(list_kw);
}
private async void takeKW(DBKeyword kw)
{
ChromeDriver driver = this.initDriver(kw);
if (!driver.isSelectorExist(By.CssSelector(".report-download-button")))
{
driver.Close();
return;
}
IWebElement element = driver.FindElement(By.CssSelector(".report-download-button"));
string fileName = element.GetAttribute("download");
Console.WriteLine("Скачиваем:" + fileName);
element.SendKeys(Keys.Enter);
while (!File.Exists(fileName))
{
System.Threading.Thread.Sleep(2000);
}
driver.Close();
File.ReadAllLines(fileName)
.Skip(1)
.ToList()
.ForEach(s =>
{
string[] buf = s.Split(';');
string keyword = (new Regex("['\"]")).Replace(buf[0], "");
if (int.Parse(buf[1]) <= 7 && int.Parse(buf[4]) <= 100)
{
if (this.isKeywordExist(keyword) == -1)
{
this.Insert(keyword);
}
}
});
File.Delete(fileName);
}
public List <DBKeyword> loadFile(long offset, int limit)
{
if (!File.Exists(this.loadFromPath))
{
throw new FileNotFoundException(this.loadFromPath);
}
List <DBKeyword> list = new List <DBKeyword>();
File.ReadLines(this.loadFromPath)
.Skip((int)offset)
.Take(limit)
.ToList()
.ForEach(s =>
{
DBKeyword dbw = new DBKeyword();
dbw.keyword = s;
list.Add(dbw);
});
return(list);
}
private ChromeDriver initDriver(DBKeyword kw)
{
var options = new ChromeOptions();
//options.AddArgument("no-sandbox");
options.AddUserProfilePreference("download.default_directory", Directory.GetCurrentDirectory());
options.AddArguments("--disable-extensions");
// options.AddArgument("no-sandbox");
options.AddArgument("--incognito");
//options.AddArgument("--headless");
options.AddArgument("--disable-gpu"); //--d
ChromeDriver driver = new ChromeDriver(options); //открываем сам браузер
driver.LocationContext.PhysicalLocation = new OpenQA.Selenium.Html5.Location(55.751244, 37.618423, 152);
driver.Manage().Timeouts().ImplicitWait = TimeSpan.FromSeconds(10); //время ожидания компонента страницы после загрузки страницы
driver.Manage().Cookies.DeleteAllCookies();
driver.Navigate().GoToUrl("http://www.bukvarix.com/keywords/?q=" + kw.keyword + "&r=report");
return(driver);
}
public void search(DBKeyword keyword)
{
driver.Navigate().GoToUrl(this.url + "/search/?text=" + keyword.keyword);
int adTopCount = 0,
adBottomCount = 0,
searchCount = 0;
Boolean flagTB = true, isAd = true;
foreach (IWebElement i in driver.FindElements(By.CssSelector(".serp-item")))
{
if (i.GetAttribute("class").IndexOf("serp-adv-item") == -1)
{
flagTB = false;
isAd = false;
++searchCount;
}
if (i.GetAttribute("class").IndexOf("serp-adv-item") != -1 && flagTB)
{
++adTopCount;
isAd = true;
}
else
if (i.GetAttribute("class").IndexOf("serp-adv-item") != -1 && !flagTB)
{
++adBottomCount;
isAd = true;
}
String localUrl = "";
String description = "";
try
{
localUrl = i.FindElement(By.CssSelector(".link_outer_yes b")).Text;
description = i.FindElement(By.CssSelector(".organic__title-wrapper")).Text;
}
catch (Exception e) { }
if (localUrl.Trim() == "" && description.Trim() == "")
{
continue;
}
Console.WriteLine("l_url=>{0}, d=>{1}", localUrl, description);
long urlId = isUrlExist(localUrl);
if (urlId == -1)
{
urlId = Insert_Site(localUrl);
}
Keyword kw = new Keyword();
//kw.url = url;
kw.keyword_id = keyword.keyword_id;
kw.description = description;
kw.is_ad = isAd;
kw.region_id = this.regionId;
kw.site_id = urlId;
if (isAd)
{
kw.position = (byte)(flagTB ? adTopCount : 4 + adBottomCount);
}
else
{
kw.position = (byte)searchCount;
}
kw.search_engine = (byte)Keyword.SearchEngine.YANDEX;
kw.created_at = DateTime.Now;
kw.updated_at = DateTime.Now;
try
{
if (!isExist_AdSearchPosition(kw))
{
Insert_AdSearchPosition(kw);
}
}
catch (Exception e)
{
Task.Run(() => (new SelfRestarter(TimeSpan.FromSeconds(25))).execute());
}
}
}