//public void Open()
//{
// try
// {
// m_dbConnection.Open();
// }
// catch (Exception e)
// {
// Console.Write(e.Message);
// }
//}
/// <summary>
/// ItemInSQL - updating ItemInSQL
/// parameters - NameOfParameter - string value
/// </summary>
/// <param name="itemInSql"></param>
/// <param name="parameters"></param>
public void Update(ItemInSQL itemInSql, Dictionary<string, string> parameters)
{
int canBuy = 1;
int MaxToBuy = 2;
int Bought = 3;
int MinPriceBuy = 4;
int MaxPriceBuy = 5;
allowedParameters.Add("ItemName");
allowedParameters.Add("CanBuy");
allowedParameters.Add("MaxToBuy");
allowedParameters.Add("Bought");
allowedParameters.Add("MinPriceBuy");
allowedParameters.Add("MaxPriceBuy");
allowedParameters.Add("MinPriceSell");
allowedParameters.Add("MaxPriceSell");
bool first = true;
bool last = false;
String updateString = "UPDATE Items SET ";
using (SQLiteConnection connection = GetSqLiteConnection(ConnectionString))
{
foreach (var param in parameters)
{
//checking if the updating field exists. Not neat way to do it.
if (allowedParameters.Contains(param.Key))
{
if (!first)
{
updateString += ", ";
}
updateString += param.Key + " = " + param.Value;
}
first = false;
}
updateString += " WHERE ItemID = '" + itemInSql.ClassInstance + "' AND Hash = '" + itemInSql.Hash + "'";
SQLiteCommand command = new SQLiteCommand(updateString, connection);
command.ExecuteNonQuery();
Console.WriteLine("ItemInSQL " + itemInSql.ItemName + " has updated succefully!");
}
}
/// <summary>
/// We don't check if we could sell this item.
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private static int EvaluatePrice(ItemInSQL item)
{
int minPrice = item.MinPriceSell;
int maxPrice = item.MaxPriceSell;
int price = 0;
string itemInfo = GetItemInfo(item.ClassInstance, "en", CsgotmConfig.SteamWeb);
if (!itemInfo.Contains("error"))
{
dynamic itemInfoJson = JsonConvert.DeserializeObject(itemInfo);
//TODO offers[0] could not exist - if there's no offers for this item.
var currentLowestPrice = 0;
for (int i = 0; i < 5; i++)
{
var debug1 = Int32.Parse(itemInfoJson.offers[i].count.Value);
var debug2 = Int32.Parse(itemInfoJson.offers[i].my_count.Value);
if (debug1 > debug2)
{
// if (itemInfoJson.offers[i].count > itemInfoJson.offers[i].my_count)
//{
currentLowestPrice = itemInfoJson.offers[i].price;
break;
}
}
var numberOfMySellingItems = itemInfoJson.offers[0].my_count;
Console.WriteLine("Current price: " + (double)currentLowestPrice / 100 + " .руб");
//if (numberOfMySellingItems == 0)
//{
if (currentLowestPrice > maxPrice)
{
price = maxPrice;
}
else if (currentLowestPrice <= minPrice)
{
price = minPrice;
}
else if (currentLowestPrice > minPrice)
{
price = currentLowestPrice - 1;
}
//}
//else
//{
// price = currentLowestPrice;
//}
}
return price;
}
public static Boolean SetAutoBuy(ItemInSQL itemInSql, string apiKey, SteamTrade.SteamWeb steamWeb)
{
if (itemInSql.CanBuy == 1)
{
String deserializedResponse;
string lang = "en"; //needed for some requests
//TODO I'm inserting a new AutoBuyPrice, but can't change.
//calculating new price for ItemInSQL
int currentMaxAutoBuyPrice = GetMaxAutoBuyPriceForItem(itemInSql, lang, apiKey, steamWeb); //100
int newPrice;
if (currentMaxAutoBuyPrice < itemInSql.MinPriceBuy)
{
newPrice = itemInSql.MinPriceBuy;
}
else if (currentMaxAutoBuyPrice < itemInSql.MaxPriceBuy)
{
newPrice = currentMaxAutoBuyPrice + 1;
}
else
{
newPrice = itemInSql.MaxPriceBuy;
}
//trying to update price for ItemInSQL
string updateLink = string.Format("https://csgo.tm/api/UpdateOrder/{0}/{1}/{2}/?key={3}",
itemInSql.GetItemClass(), itemInSql.GetItemInstance(), newPrice, apiKey);
string updateJsonResponse = steamWeb.Fetch(updateLink, "GET", null, false, null);
deserializedResponse = JsonConvert.DeserializeObject(updateJsonResponse).ToString();
//if couldn't find the order in base - create a new one.
if (deserializedResponse.ToString().Contains("Данная заявка на покупку не найдена"))
{
string insertLink = string.Format("https://csgo.tm/api/InsertOrder/{0}/{1}/{2}/{3}/?key={4}",
itemInSql.GetItemClass(), itemInSql.GetItemInstance(), newPrice, itemInSql.Hash, apiKey);
string insertJsonResponse = steamWeb.Fetch(insertLink, "GET", null, false, null);
deserializedResponse = JsonConvert.DeserializeObject(insertJsonResponse).ToString();
if (deserializedResponse.Contains("true"))
{
Console.WriteLine("A new autobuy order for ItemInSQL " + itemInSql.ItemName +
" was created successfully!" +
" Price: " + (double) newPrice/100 + " руб.");
return true;
}
else if (deserializedResponse.Contains("Неверно задана цена покупки"))
{
Console.WriteLine("Wrong price for " +itemInSql.ItemName + "! Check the prices for buying in database!");
return false;
}
Console.WriteLine("For item: " + itemInSql.ItemName);
Console.WriteLine(deserializedResponse);
}
else if (deserializedResponse.ToString().Contains("недостаточно средств на счету"))
{
Console.WriteLine("Autobuy false. For " + itemInSql.ItemName +
" - not enough funds in wallet! Пополните кошелек!");
return false;
}
else if (deserializedResponse.ToString().Contains("true"))
{
Console.WriteLine("Autobuy price for " + itemInSql.ItemName + " was updated successfully! New price: " +
(double) newPrice/100 + " руб.");
return true;
}
//TODO
Console.WriteLine("We should never reach here." +
" A new error ");
}
return false;
}
public static void SellItem(ItemInSQL item)
{
if (item.CanSell == 1)
{
int price = EvaluatePrice(item);
var sellItemLink = string.Format("https://csgo.tm/api/SetPrice/new_{0}/{1}/?key={2}",
item.ClassInstance, price, ApiKey);
var sellItemResponse = CsgotmConfig.SteamWeb.Fetch(sellItemLink, "GET", null, false, null);
if (sellItemResponse.Contains("\"result\":1"))
{
Console.WriteLine(item.ItemName + " is selling for price " + (double) price/100 + " .руб!");
}
else
{
Console.WriteLine("Error while setting item to sell on website: " + sellItemResponse);
}
}
}
public static int GetMaxAutoBuyPriceForItem(ItemInSQL itemInSql, string lang, string apikey, SteamTrade.SteamWeb steamWeb)
{
//https://csgo.tm/api/ItemInfo/520025252_0/en/?key= //in json properties get autoBuyOffers
string requestLink = string.Format("https://csgo.tm/api/ItemInfo/{0}/{1}/?key={2}", itemInSql.ClassInstance, lang, apikey);
string jsonItemInfo = steamWeb.Fetch(requestLink, "GET", null, false, null);
dynamic deserializedResponse = JsonConvert.DeserializeObject(jsonItemInfo);
int price = deserializedResponse.buy_offers[0].o_price;
return price;
}
//public bool CheckUnique(String class_instance)
//{
//}
public void Add(ItemInSQL itemInSql)
{
using (SQLiteConnection connection = GetSqLiteConnection(ConnectionString))
{
if (Select(itemInSql) == null)
{
var insertString = "INSERT INTO Items " +
"('Context','ItemID','ItemName','ItemURL','ItemURLSteam','Hash','CanBuy'" +
",'CanSell','MaxToBuy','Bought','MinPriceBuy','MaxPriceBuy', 'MinPriceSell', 'MaxPriceSell')" +
"VALUES (@context,@itemID,@itemName,@itemURL,@itemURLSteam,@hash,@canBuy," +
"@canSell,@maxToBuy,@bought,@minPriceBuy,@maxPriceBuy,@minPriceSell,@maxPriceSell);";
var command = new SQLiteCommand(insertString, connection);
command.Parameters.AddWithValue("@context", itemInSql.Context);
//number for game - csgo = 730, tf2 = 440, dota2 = 570, steam = 753
command.Parameters.AddWithValue("@itemID", itemInSql.ClassInstance);
command.Parameters.AddWithValue("@itemName", itemInSql.ItemName);
command.Parameters.AddWithValue("@itemURL", itemInSql.ItemUrl);
command.Parameters.AddWithValue("@itemURLSteam", itemInSql.ItemUrlSteam);
command.Parameters.AddWithValue("@hash", itemInSql.Hash);
command.Parameters.AddWithValue("@canBuy", itemInSql.CanBuy);
command.Parameters.AddWithValue("@canSell", itemInSql.CanSell);
command.Parameters.AddWithValue("@maxToBuy", itemInSql.MaxToBuy);
command.Parameters.AddWithValue("@bought", itemInSql.Bought);
command.Parameters.AddWithValue("@minPriceBuy", itemInSql.MinPriceBuy);
command.Parameters.AddWithValue("@maxPriceBuy", itemInSql.MaxPriceBuy);
command.Parameters.AddWithValue("@minPriceSell", itemInSql.MinPriceSell);
command.Parameters.AddWithValue("@maxPriceSell", itemInSql.MaxPriceSell);
command.ExecuteNonQuery();
Console.WriteLine("ItemInSQL " + itemInSql.ItemName + " was added successfully!");
}
else
{
Console.WriteLine("The ItemInSQL " + itemInSql.ItemName + " already in base");
}
}
}
//TODO this method mostly reapeats "Select" method. Rewrite so it'll not just repeat same code twice.
public List<ItemInSQL> SelectAll()
{
using (SQLiteConnection connection = GetSqLiteConnection(ConnectionString))
{
List<ItemInSQL> items = new List<ItemInSQL>();
var sqlSelect = "SELECT * FROM Items";
var command = new SQLiteCommand(sqlSelect, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
var itemInSql = new ItemInSQL(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2),
reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6),
reader.GetInt32(7), reader.GetInt32(8), reader.GetInt32(9), reader.GetInt32(10),
reader.GetInt32(11), reader.GetInt32(12), reader.GetInt32(13), reader.GetInt32(14));
items.Add(itemInSql);
}
return items;
}
}
private ItemInSQL SelectWithClassInstance(string class_instance)
{
using (SQLiteConnection connection = GetSqLiteConnection(ConnectionString))
{
var sqlSelect = "SELECT * FROM Items WHERE ItemID = '" + class_instance +
"'";
var command = new SQLiteCommand(sqlSelect, connection);
var reader = command.ExecuteReader();
var items = new List<ItemInSQL>();
while (reader.Read())
{
var itemInSql = new ItemInSQL(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2),
reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6),
reader.GetInt32(7), reader.GetInt32(8), reader.GetInt32(9), reader.GetInt32(10),
reader.GetInt32(11), reader.GetInt32(12), reader.GetInt32(13), reader.GetInt32(14));
items.Add(itemInSql);
}
if (items.Count == 0)
{
Console.WriteLine("No items was found for class_instance " + class_instance);
return null;
}
if (items.Count == 1)
{
return items[0];
}
//TODO: if I change console to log - change here.
//if code reaches here, we have 2+ items, returning the firest one and notificationg about that.
Console.WriteLine("For class-instance " + class_instance + " we have more than one row in db.");
Console.WriteLine("Row numbers of items: ");
foreach (var collisionItem in items)
{
Console.WriteLine(collisionItem._n);
}
Console.WriteLine("Returning first ItemInSQL with number " + items[0]._n);
return items[0];
}
}
//selecting items via class_instance
public ItemInSQL Select(ItemInSQL item)
{
return SelectWithClassInstance(item.ClassInstance);
}
public void Delete(ItemInSQL itemInSql)
{
using (SQLiteConnection connection = GetSqLiteConnection(ConnectionString))
{
var deleteString = "DELETE FROM items WHERE ItemID = '" +
itemInSql.ClassInstance + "' AND Hash = '" + itemInSql.Hash + "'";
if (Select(itemInSql) != null)
{
var command = new SQLiteCommand(deleteString, connection);
command.ExecuteNonQuery();
Console.WriteLine("ItemInSQL " + itemInSql.ItemName + " was deleted successfully!");
}
else
{
Console.WriteLine("ItemInSQL " + itemInSql.ItemName + " was not found in base!");
}
}
}