public int GetStorageSQLID(TradeHandler.TradeItem MyTradeItem)
{
MySqlConnection MyConnection = new MySqlConnection("Server=" + MainClass.SqlServer + ";Port=" + MainClass.SqlPort.ToString() + ";Database=" + MainClass.SqlDatabase + ";Uid=" + MainClass.SqlUsername + ";Pwd=" + MainClass.SqlPassword + ";");
try
{
MyConnection.Open();
}
catch (Exception myException)
{
Console.WriteLine(myException.Message); Environment.Exit(0);
}
TradeHandler.TradeItem MyTempTradeItem = new TradeHandler.TradeItem();
MyTempTradeItem = MyTradeItem;
// Add Item it the Global Items List and to the Inventory List
// First check if the Item exists in the global Database, if not Add it.
int id = -1;
int count = -1;
//string sql = "SELECT id FROM knownitems WHERE imageid = ?imageid and name = ?name order by imageid, name";
string sql = "SELECT id FROM knownitems WHERE lower(name) = ?name order by name";
MySqlCommand cmd = new MySqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("?imageid", MyTempTradeItem.imageid);
cmd.Parameters.AddWithValue("?name", MyTempTradeItem.name.ToLower());
TheLogger.Debug(sql + "\n");
MySqlDataReader reader = cmd.ExecuteReader();
TheLogger.Debug("### cmd.ExecuteReader() in GetStorageSQLID\n");
try
{
while (reader.Read())
{
id = reader.GetInt16(0);
count++;
}
}
catch (MySqlException oMySQLException)
{
myErrorHandler.errorWriter(oMySQLException);
}
catch (Exception oException)
{
myErrorHandler.errorWriter(oException);
}
finally
{
reader.Close();
TheLogger.Debug("### reader.Close() in GetStorageSQLID\n");
}
if (id != -1 && count == 0)
{
MyConnection.Close();
return id;//we found a perfect match
}
count = -1;
sql = "SELECT id FROM knownitems WHERE imageid = ?imageid and lower(name) = ?name order by imageid, name";
cmd = new MySqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("?imageid", -1);
cmd.Parameters.AddWithValue("?name", MyTempTradeItem.name.ToLower());
TheLogger.Debug(sql + "\n");
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
id = reader.GetInt16(0);
count++;
}
}
catch (MySqlException oMySQLException)
{
myErrorHandler.errorWriter(oMySQLException);
}
catch (Exception oException)
{
myErrorHandler.errorWriter(oException);
}
finally
{
reader.Close();
}
if (id != -1 && count == 0)
{
//update the imageid here from -1 to the right one
// imageid = -1 update it
Inventory.inventory_item MyInventoryItem = new Inventory.inventory_item();
MyInventoryItem.imageid = MyTradeItem.imageid;
MyInventoryItem.name = MyTradeItem.name;
MyInventoryItem.description = "";
MyInventoryItem.weight = MyTradeItem.weight;
MyInventoryItem.is_resource = false;
MyInventoryItem.is_reagent = false;
MyInventoryItem.is_stackable = false;
MyInventoryItem.use_with_inventory = false;
updateknownitems(MyInventoryItem, id);
MyConnection.Close();
return id;//we found a perfect match
}
if (id == -1)
{
if(MyTradeItem.imageid == 0) TheLogger.ErrorLog("Possibly invalid Item: " + MyTradeItem.name + " added by " + Settings.botid + " in GetStorageSQLID()");
sql = "INSERT INTO knownitems (name,description,imageid,weight,is_resource,is_reagent,is_stackable,use_with_inventory) VALUES (?name,?description,?imageid,?weight,?is_resource,?is_reagent,?is_stackable,?use_with_inventory)";
cmd.Parameters.Clear();
cmd = new MySqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("?name", MyTradeItem.name);
cmd.Parameters.AddWithValue("?description", " ");
cmd.Parameters.AddWithValue("?imageid", MyTradeItem.imageid);
cmd.Parameters.AddWithValue("?weight", MyTradeItem.weight);
cmd.Parameters.AddWithValue("?is_resource", -1);
cmd.Parameters.AddWithValue("?is_reagent", -1);
cmd.Parameters.AddWithValue("?is_stackable", -1);
cmd.Parameters.AddWithValue("?use_with_inventory", -1);
try
{
cmd.ExecuteNonQuery();
id = GetKnownItemsSQLID(MyTradeItem);
}
catch (MySqlException oMySQLException)
{
TheLogger.Log("ExecSql Error in " + oMySQLException.TargetSite + " due to : " + oMySQLException.Message + "\n");
TheLogger.Log("ExecSql by SQL : " + sql
+ "\n");
myErrorHandler.errorWriter(oMySQLException);
}
catch (Exception oException)
{
TheLogger.Log("ExecSql Error in " + oException.TargetSite + " due to : " + oException.Message + "\n");
TheLogger.Log("ExecSql by SQL : " + sql + "\n");
myErrorHandler.errorWriter(oException);
}
finally
{
//reader.Close();
}
reader.Close();
MyConnection.Close();
return id;
}
reader.Close();
MyConnection.Close();
return id;
}
public void UpdateText(string nameText)
{
int idx;
nameText = nameText.Replace((char)10, ' ');
lock (TheStorage.SyncRoot)
{
for (idx = 0; idx < TheStorage.Count; idx++)
{
StorageItem MyStorageItem = (StorageItem)TheStorage[idx];
if (MyStorageItem.name == "")
{
MyStorageItem.name = nameText;
TradeHandler.TradeItem MyTradeItem = new TradeHandler.TradeItem();
MyTradeItem.name = MyStorageItem.name;
MyTradeItem.imageid = MyStorageItem.imageid;
MyTradeItem.weight = -1;
TradeHandler.TradeItem MyTempTradeItem = new TradeHandler.TradeItem();
MyTempTradeItem = MyTradeItem;
MyStorageItem.knownItemsID = TheMySqlManager.GetStorageSQLID(MyTempTradeItem);
//get the reserved amount
MyStorageItem.reservedQuantity = TheMySqlManager.ReservedAmount(MyStorageItem.knownItemsID);
TheStorage[idx] = MyStorageItem;
//what we wanna do here is only insert on the last one, then insert them all...
if (idx == TheStorage.Count - 1)
{
//insert all of the rows
TheMySqlManager.InsertStorageItems(TheStorage);
TradeHandler.openingStorage = false;
}
//TheMySqlManager.InsertStorageItem(MyStorageItem);
break;
}
}
}
}
