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