private void WriteItem(ItemTO item)
{
var foundItem = GetItem(item.WowId);
if (foundItem == null)
{
using var sqLiteCommand = new SQLiteCommand("INSERT INTO Item(WOW_ID, Name) VALUES(@WOWID,@Name)", connection);
sqLiteCommand.Parameters.AddWithValue("@WOWID", item.WowId);
sqLiteCommand.Parameters.AddWithValue("@Name", item.Name);
Console.WriteLine("Add new Item: " + item.Name);
sqLiteCommand.ExecuteNonQuery();
sqLiteCommand.CommandText = "SELECT last_insert_rowid()";
var lastRowId64 = (long)sqLiteCommand.ExecuteScalar();
item.ID = (int)lastRowId64;
}
else
{
item.ID = foundItem.ID;
}
}
private LootTO ParseLootInfo(string line)
{
if (line.Equals("Index,Name,ID,Time,Mob,Player,Cost,Note"))
{
return(null);
}
var csvParser = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))");
var lootInformation = csvParser.Split(line);
try
{
var itemName = lootInformation[1].Replace("\"", "");
var wowId = Convert.ToInt32(lootInformation[2]);
var dateString = lootInformation[3];
var date = DateTime.ParseExact(dateString, DateFormat, _provider);
var mobName = lootInformation[4].Replace("\"", "");
var foundPlayerName = lootInformation[5].Replace("\"", "");
var foundPlayer = _players.FirstOrDefault(player => string.Equals(player.Name, foundPlayerName));
var item = new ItemTO(itemName, wowId);
return(new LootTO(date, item, foundPlayer, new MobTO(mobName)));
}
catch (Exception)
{
Console.WriteLine("Couldn't parse loot format: '" + line + "'");
}
return(null);
}
private void UpdateItem(ItemTO item)
{
using var sqLiteCommand = new SQLiteCommand("UPDATE Item SET Quality = @Quality WHERE WOW_ID = @WOWID", connection);
sqLiteCommand.Parameters.AddWithValue("@Quality", item.Quality);
sqLiteCommand.Parameters.AddWithValue("@WOWID", item.WowId);
Console.WriteLine("Quality Updated: " + item.Name + " has quality " + item.Quality);
sqLiteCommand.ExecuteNonQuery();
}
//https://www.wowhead.com/item=21323&xml
public static int GetQuality(ItemTO item)
{
using var client = new WebClient();
var contents = client.DownloadString($"https://www.wowhead.com/item={item.WowId}&xml");
var xDocument = XDocument.Parse(contents);
var result = xDocument.Element("wowhead").Descendants();
var itemQuality = result.Elements().FirstOrDefault(item => string.Equals(item.Name.ToString(), "quality"));
return(Convert.ToInt32(itemQuality.Attribute("id")?.Value));
}
private ItemTO GetItem(int WOWID)
{
using var sqLiteCommand = new SQLiteCommand("SELECT * FROM Item WHERE WOW_ID = @WOWID", connection);
sqLiteCommand.Parameters.AddWithValue("@WOWID", WOWID);
using var sqLiteDataReader = sqLiteCommand.ExecuteReader();
while (sqLiteDataReader.Read())
{
var item = new ItemTO();
item.WowId = WOWID;
item.ID = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("Item_ID"));
item.Name = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Name"));
return(item);
}
return(null);
}
private IEnumerable <ItemTO> GetItemsWithoutQuality()
{
using var sqLiteCommand = new SQLiteCommand("SELECT * FROM Item WHERE Quality IS null", connection);
using var sqLiteDataReader = sqLiteCommand.ExecuteReader();
var items = new List <ItemTO>();
while (sqLiteDataReader.Read())
{
var item = new ItemTO();
item.WowId = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("WOW_ID"));
item.ID = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("Item_ID"));
item.Name = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Name"));
items.Add(item);
}
return(items);
}
