public void DeleteItem(ProduktModell item) { string sql = $"DELETE FROM PRODUKTE where produktID = '{item.produktID}'"; if (connection.State == ConnectionState.Open) { try { using (SQLiteCommand cmd = new SQLiteCommand(sql, connection)) { cmd.ExecuteNonQuery(); } } catch (Exception ex) { log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + "Fehler beim Ausführen des Delete-SQls", ex); log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + sql); return; } } else { return; } }
public void UpdateItem(ProduktModell item) { string sql = $"UPDATE PRODUKTE set articleURL = '{item.articlePicture}', articleName = " + $"'{item.articleName}', hardwareRatURL = '{item.hardwareRatURL}', compareSiteURL = '{item.compareURL}'," + $" hardwareRatPrice = '{item.hardwareRatPrice.ToString().Replace(",", ".")}', compareSitePrice = '{item.comparePrice.ToString().Replace(",", ".")}', state = '{item.State}', " + $" differencePrice = '{item.priceDifference.ToString().Replace(",", ".")}', hardwareRatID = '{item.hardwareRatID}', hasGeizhalsURL = '{(item.hasGeizhalsURL ? "1" : "0")}', IsNew = '{(item.IsNew ? "1" : "0")}' " + $" where produktID = '{item.produktID}'"; if (connection.State == ConnectionState.Open) { try { using (SQLiteCommand cmd = new SQLiteCommand(sql, connection)) { cmd.ExecuteNonQuery(); } } catch (Exception ex) { log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + "Fehler beim Ausführen des Update-SQls", ex); log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + sql); return; } } else { return; } }
public void InsertItem(ProduktModell item) { string sql = $"INSERT INTO PRODUKTE (hardwareRatURL, compareSiteURL, hardwareRatPrice, compareSitePrice, state, differencePrice, compareSiteType, articleName," + $" articleURL, hardwareRatID, addedAt, hasGeizhalsURL, IsNew, GTIN) VALUES ('{item.hardwareRatURL}', '{item.compareURL}', {item.hardwareRatPrice.ToString().Replace(",", ".")}, {item.comparePrice.ToString().Replace(",", ".")}, " + $"'{item.State}', {item.priceDifference.ToString().Replace(",", ".")}," + $" '{item.compareSiteType}', '{item.articleName}', '{item.articlePicture}', '{item.hardwareRatID}', " + $"'{item.AddedAt.ToString("dd.MM.yyyy")}', '{(item.hasGeizhalsURL ? "1": "0")}', '{(item.IsNew ? "1" : "0")}', '{item.gTIN}')"; if (connection.State == ConnectionState.Open) { try { using (SQLiteCommand cmd = new SQLiteCommand(sql, connection)) { cmd.ExecuteNonQuery(); } } catch (Exception ex) { log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + "Fehler beim Ausführen des Insert-SQls", ex); log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + sql); return; } } else { return; } }
public List <ProduktModell> getGridData(string sql) { List <ProduktModell> retVal = new List <ProduktModell>(); if (string.IsNullOrEmpty(sql)) { return(null); } if (connection.State == ConnectionState.Open) { try { using (SQLiteCommand cmd = new SQLiteCommand(sql, connection)) { using (SQLiteDataReader reader = cmd.ExecuteReader()) { if (!reader.HasRows) { return(null); } while (reader.Read()) { ProduktModell dataRow = new ProduktModell() { hardwareRatURL = reader[0].ToString(), compareURL = reader[1].ToString(), hardwareRatPrice = double.Parse(reader[2].ToString()), comparePrice = double.Parse(reader[3].ToString()), State = reader[4].ToString(), priceDifference = double.Parse(reader[5].ToString()), compareSiteType = reader[6].ToString(), produktID = int.Parse(reader[7].ToString()), articleName = reader[8].ToString(), articlePicture = reader[9].ToString(), hardwareRatID = string.IsNullOrEmpty(reader[10].ToString()) ? 0 : int.Parse(reader[10].ToString()), AddedAt = reader[11].ToString() != null?DateTime.Parse(reader[11].ToString()) : DateTime.MinValue, hasGeizhalsURL = reader[12].ToString() == "1" ? true : false, IsNew = reader[13].ToString() == "1" ? true : false, gTIN = reader[14].ToString(), }; retVal.Add(dataRow); } } } return(retVal); } catch (Exception ex) { log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + "Fehler beim Ausführen des Read-SQls", ex); log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": " + sql); return(null); } } else { return(null); } }
public AddValueViewModel(OperationMode mode, ProduktModell selectedItem) { operationMode = mode; if (operationMode == OperationMode.UPDATE) { urlHWRat = selectedItem.hardwareRatURL; urlCompareSite = selectedItem.compareURL; hardwareRatID = selectedItem.hardwareRatID; orginalItem = selectedItem; } string connectionString = Properties.Settings.Default.DatebaseLocation.Replace("{PROJECT}", AppDomain.CurrentDomain.BaseDirectory); sqlHelper = new SQLiteHelper(connectionString); }
public ProduktModell SearchGeizhalsData(string gTIN, string productName) { ProduktModell retVal = new ProduktModell(); try { HtmlWeb webPage = new HtmlWeb(); HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument(); //Name parsen, damit er akzeptiert wird string searchProduct = string.IsNullOrEmpty(gTIN) ? "" : gTIN.Replace(" ", "+").Replace(",", "%2C").Replace("/EU", ""); document = webPage.Load($"https://geizhals.eu/?fs={searchProduct}&hloc=at&in="); if (webPage.ResponseUri.ToString().Contains("?fs=") || webPage.ResponseUri.ToString().Contains("geizhals")) { try { //GeizhalsURL öffnen try { string foundURL = document.DocumentNode.SelectSingleNode("//a[@class='listview__name-link']").Attributes["href"].Value; retVal.comparePrice = double.Parse(document.DocumentNode.SelectSingleNode("//a[@class='listview__price-link ']//span[@class='price']").InnerText.Replace("€ ", "").Replace("€ ", "")); retVal.compareURL = foundURL.Contains("geizhals.eu") ? "https:" + foundURL : "https://geizhals.eu/" + foundURL; //document = webPage.Load(retVal.compareURL); //retVal.comparePrice = double.Parse(document.DocumentNode.SelectSingleNode("//span[@class='variant__header__pricehistory__pricerange']//strong//span[@class='gh_price']").InnerText.Replace("€ ", "").Replace("€ ", "")); retVal.hasGeizhalsURL = true; } catch (Exception ex) { log.writeLog(LogType.ERROR, $"{MethodBase.GetCurrentMethod().Name}: Fehler beim Laden mit GTIN {gTIN}", ex); //Suche per Name probieren searchProduct = productName.Replace(" ", "+").Replace(",", "%2C").Replace("/EU", ""); document = webPage.Load($"https://geizhals.eu/?fs={searchProduct}&hloc=at&in=&sort=p"); string foundURL = document.DocumentNode.SelectSingleNode("//a[@class='listview__name-link']").Attributes["href"].Value; retVal.comparePrice = double.Parse(document.DocumentNode.SelectSingleNode("//a[@class='listview__price-link ']//span[@class='price']").InnerText.Replace("€ ", "").Replace("€ ", "")); retVal.compareURL = foundURL.Contains("geizhals.eu") ? "https:" + foundURL : "https://geizhals.eu/" + foundURL; //document = webPage.Load(retVal.compareURL); //retVal.comparePrice = double.Parse(document.DocumentNode.SelectSingleNode("//span[@class='variant__header__pricehistory__pricerange']//strong//span[@class='gh_price']").InnerText.Replace("€ ", "").Replace("€ ", "")); retVal.hasGeizhalsURL = true; } } catch (Exception ex) { log.writeLog(LogType.ERROR, $"{MethodBase.GetCurrentMethod().Name}: Kein Artikel gefunden zu {gTIN} und {productName}", ex); retVal.compareURL = "Artikel bei Geizhals nicht gefunden"; retVal.hasGeizhalsURL = false; retVal.comparePrice = 0; } } else { retVal.compareURL = webPage.ResponseUri.ToString(); retVal.comparePrice = double.Parse(document.DocumentNode.SelectSingleNode("//span[@class='variant__header__pricehistory__pricerange']//strong//span[@class='gh_price']").InnerText.Replace("€ ", "").Replace("€ ", "")); retVal.hasGeizhalsURL = true; } } catch (Exception) { retVal.comparePrice = 0; } return(retVal); }
public async void LoadXMLintoSQLite(XmlDocument xmlDoc, bool loadGeizhals) { try { List <ProduktModell> listXML = new List <ProduktModell>(); Stopwatch xmlTime = new Stopwatch(); xmlTime.Start(); //XML auswerten und antragen XmlNodeList artikelDaten = xmlDoc.SelectNodes(".//item"); int maxCount = artikelDaten.Count - 1; for (int i = 0; i < artikelDaten.Count; i++) { ProduktModell model = new ProduktModell() { hardwareRatURL = artikelDaten[i].ChildNodes[5].InnerText, hardwareRatPrice = double.Parse(artikelDaten[i].ChildNodes[9].InnerText), articlePicture = artikelDaten[i].ChildNodes[6].InnerText, articleName = artikelDaten[i].ChildNodes[1].InnerText, hardwareRatID = int.Parse(artikelDaten[i].ChildNodes[0].InnerText), gTIN = artikelDaten[i].ChildNodes[11].InnerText, AddedAt = DateTime.Now, }; listXML.Add(model); await MainDispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => { statusValue = $"Lade XML Artikel {i} von {maxCount}"; })); } int articleAdded = 0; int articleMax = 0; //In Datenbank packen if (listXML.Count > 0) { articleMax = listXML.Count; HtmlAgilityPack.HtmlDocument document = null; HtmlWeb webPage = null; if (loadGeizhals) { webPage = new HtmlWeb(); document = new HtmlAgilityPack.HtmlDocument(); } foreach (ProduktModell row in listXML) { //Geizhalsbezug aufrufen if (loadGeizhals) { try { ProduktModell retVal = SearchGeizhalsData(row.gTIN, row.articleName); row.comparePrice = retVal.comparePrice; row.compareURL = retVal.compareURL; row.hasGeizhalsURL = retVal.hasGeizhalsURL; double difference = Math.Round(row.hardwareRatPrice - row.comparePrice, 2); row.priceDifference = difference; if (difference <= 0) { row.State = "günstiger"; } else if (difference > 0 && difference < 3) { row.State = "1-2€ darüber"; } else if (difference > 2) { row.State = "3€ oder mehr darüber"; } //Falls kein Preis bei Geizhals vorhanden if (row.comparePrice == 0) { row.State = "günstiger"; } //1.2 Sekunden warten IP Ban zu umgehen System.Threading.Thread.Sleep(1800); } catch (Exception) { } } if (!produktItems.Any(y => y.hardwareRatID == row.hardwareRatID)) { row.IsNew = true; row.AddedAt = DateTime.Now; sQLiteHelper.InsertItem(row); articleAdded++; await MainDispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => { statusValue = $"Übernehme Artikel {articleAdded} von {articleMax} ({Math.Round(((double)articleAdded / articleMax * 100), 0)}%)"; })); } else { //Artikel nur updaten row.IsNew = false; sQLiteHelper.UpdateItemXML(row, loadGeizhals); await MainDispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => { statusValue = $"Update Artikel HardwareRat ID {row.hardwareRatID}, da er bereits vorhanden ist!"; })); } } } xmlTime.Stop(); log.writeLog(LogType.ERROR, $"XML Abruf hat {xmlTime.Elapsed.ToString("hh")}h {xmlTime.Elapsed.ToString("mm")}m {xmlTime.Elapsed.ToString("ss")}s {xmlTime.Elapsed.ToString("ff")}ms gedauert."); await MainDispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => { statusValue = $"{articleAdded} / {maxCount} Artikel übernommen ({Math.Round(((double)articleAdded / articleMax * 100), 0)}%)"; })); LoadGridItems(false, false); } catch (Exception) { } }
public ProduktModell getHTMLData(ProduktModell item, bool onlyUpdateEmpty) { string retValPrice = "0"; string retValName = "Nicht gefunden!"; string retValPicture = "https://hardwarerat.de/media/image/85/fa/30/logo-klein.png"; try { HtmlWeb webPage = new HtmlWeb(); HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument(); document = webPage.Load(item.hardwareRatURL); //Abfangen wenn es den Artikel nicht mehr gibt / deaktivert try { string errorString = document.DocumentNode.SelectSingleNode("//div[@class='content--wrapper']/div[@class='detail-error content listing--content']/h1[@class='detail-error--headline']").InnerText; item.articleName = "Artikel nicht mehr verfügbar!"; item.articlePicture = "https://hardwarerat.de/media/image/85/fa/30/logo-klein.png"; item.hardwareRatPrice = 0; } catch (Exception) { } retValPrice = document.DocumentNode.SelectSingleNode("//span[@class='price--content content--default']/meta").Attributes["content"].Value.Replace(".", ","); retValName = document.DocumentNode.SelectSingleNode("//h1[@class='product--title']").InnerText.Replace("\n", ""); retValPicture = document.DocumentNode.SelectSingleNode("//span[@class='image--media']/img").Attributes["src"].Value; item.hardwareRatPrice = Math.Round(double.Parse(retValPrice), 2); item.articleName = retValName; item.articlePicture = retValPicture; if (onlyUpdateEmpty && item.hasGeizhalsURL == false) { //Geizhals try { if (!item.hasGeizhalsURL) { //URL laden ProduktModell retVal = SearchGeizhalsData(item.gTIN, item.articleName); item.compareURL = retVal.compareURL; item.comparePrice = retVal.comparePrice; item.hasGeizhalsURL = retVal.hasGeizhalsURL; } document = new HtmlAgilityPack.HtmlDocument(); document = webPage.Load(item.compareURL); retValPrice = document.DocumentNode.SelectSingleNode("//span[@class='variant__header__pricehistory__pricerange']//strong//span[@class='gh_price']").InnerText.Replace("€ ", "").Replace("€ ", ""); item.comparePrice = Math.Round(double.Parse(retValPrice), 2); item.hasGeizhalsURL = true; } catch (Exception) { item.comparePrice = 0; item.hasGeizhalsURL = false; } } if (!onlyUpdateEmpty) { try { document = new HtmlAgilityPack.HtmlDocument(); document = webPage.Load(item.compareURL); retValPrice = document.DocumentNode.SelectSingleNode("//span[@class='variant__header__pricehistory__pricerange']//strong//span[@class='gh_price']").InnerText.Replace("€ ", "").Replace("€ ", ""); item.comparePrice = Math.Round(double.Parse(retValPrice), 2); item.hasGeizhalsURL = true; } catch (Exception) { item.comparePrice = 0; item.hasGeizhalsURL = false; } } } catch (Exception ex) { log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": Fehler beim Laden der HTML Seiten ", ex); log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + $": {item.hardwareRatURL}"); log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + $": {item.compareURL}"); return(item); } return(item); }
public async void LoadGridItems(bool loadSiteData, bool onlyUpdateEmpty) { try { List <ProduktModell> tmpProduktItems = new List <ProduktModell>(); bool waitTask = false; //Sqls laden string sSQL = "SELECT hardwareRatURL, compareSiteURL, hardwareRatPrice, compareSitePrice, state, differencePrice, compareSiteType, produktID, articleName, articleURL, hardwareRatID, addedAt, hasGeizhalsURL, IsNew, GTIN FROM PRODUKTE"; List <ProduktModell> retValProducts = sQLiteHelper.getGridData(sSQL); if (retValProducts != null) { int countFor = 1; if (loadSiteData) { DialogResult dialogResult = System.Windows.Forms.MessageBox.Show("Möchten sie mit Zeitversatz arbeiten, um Geizhals Ban zu umgehen?", "Hinweis!", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { waitTask = true; } } foreach (ProduktModell row in retValProducts) { await MainDispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => { statusValue = $"Lade Artikel {countFor} von {retValProducts.Count} ({Math.Round(((double)countFor / retValProducts.Count * 100), 0)}%)"; })); if (loadSiteData) { //Daten abrufen ProduktModell retVal = getHTMLData(row, onlyUpdateEmpty); row.comparePrice = retVal.comparePrice; row.hardwareRatPrice = retVal.hardwareRatPrice; row.articleName = retVal.articleName; if (waitTask && !row.hasGeizhalsURL && onlyUpdateEmpty) { System.Threading.Thread.Sleep(2500); } else if (waitTask && !onlyUpdateEmpty) { System.Threading.Thread.Sleep(2500); } } //Status abrufen double difference = Math.Round(row.hardwareRatPrice - row.comparePrice, 2); row.priceDifference = difference; if (difference <= 0) { row.State = "günstiger"; } else if (difference > 0 && difference < 3) { row.State = "1-2€ darüber"; } else if (difference > 2) { row.State = "3€ oder mehr darüber"; } //Falls kein Preis bei Geizhals vorhanden if (row.comparePrice == 0) { row.State = "günstiger"; } sQLiteHelper.UpdateItem(row); tmpProduktItems.Add(row); countFor++; } rowsLoaded = $"{retValProducts.Count} Produkte geladen [{retValProducts.Count(y => y.State == "günstiger")} günstiger; {retValProducts.Count(y => y.State == "1-2€ darüber")} 1-2€ darüber; {retValProducts.Count(y => y.State == "3€ oder mehr darüber")} 3€ oder mehr]!"; await MainDispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => { statusValue = "Übersicht erfolgreich aktualisiert!"; produktItems = new ObservableCollection <ProduktModell>(tmpProduktItems); CollectionViewSource productCView = new CollectionViewSource() { Source = tmpProduktItems }; productGrid = productCView.View; })); } else { produktItems = new ObservableCollection <ProduktModell>(); await MainDispatcher.BeginInvoke(DispatcherPriority.Normal, new Action(() => { statusValue = "Keine Daten vorhanden!"; productGrid = null; })); return; } return; } catch (Exception ex) { log.writeLog(LogType.ERROR, MethodBase.GetCurrentMethod().Name + ": Fehler beim Laden der Sensoren", ex); return; } }
public void buttonFinishedCommand(object action) { int notFilledFields = 0; if (string.IsNullOrEmpty(urlHWRat)) { notFilledFields++; } if (string.IsNullOrEmpty(urlCompareSite)) { notFilledFields++; } if (notFilledFields > 0) { MessageBox.Show("Beide Felder müssen gefüllt sein!"); return; } if (operationMode == OperationMode.CREATE) { ProduktModell newsatz = new ProduktModell() { hardwareRatURL = urlHWRat, compareURL = urlCompareSite, hasGeizhalsURL = string.IsNullOrEmpty(urlCompareSite) ? false : true, hardwareRatPrice = 0, comparePrice = 0, priceDifference = 0, State = "unbekannt", compareSiteType = "Geizhals", AddedAt = DateTime.Now, }; //Werte ersetzen, falls Daten laden genutzt wurde if (!string.IsNullOrEmpty(hwProductPicture)) { newsatz.articlePicture = hwProductPicture; } if (!string.IsNullOrEmpty(ghzProductPrice)) { newsatz.comparePrice = double.Parse(ghzProductPrice); } if (!string.IsNullOrEmpty(hwProductPrice)) { newsatz.hardwareRatPrice = double.Parse(hwProductPrice); } if (!string.IsNullOrEmpty(currentState)) { newsatz.State = currentState; } if (!string.IsNullOrEmpty(productDifference)) { newsatz.priceDifference = double.Parse(productDifference); } if (!string.IsNullOrEmpty(hwProductName)) { newsatz.articleName = hwProductName; } if (hardwareRatID > -1) { newsatz.hardwareRatID = hardwareRatID; } sqlHelper.InsertItem(newsatz); } else if (operationMode == OperationMode.UPDATE) { orginalItem.hardwareRatURL = urlHWRat; orginalItem.compareURL = urlCompareSite; orginalItem.hardwareRatID = hardwareRatID; orginalItem.hasGeizhalsURL = string.IsNullOrEmpty(urlCompareSite) ? false : true; sqlHelper.UpdateItem(orginalItem); } View.CloseWindow(true); }
public AddValueView(OperationMode mode, ProduktModell selectedItem) { InitializeComponent(); this.DataContext = new AddValueViewModel(mode, selectedItem); (DataContext as AddValueViewModel).View = this as ICloseWindow; }