/// <summary>
///
/// </summary>
/// <param name="charId"></param>
/// <returns></returns>
public static List<ItemsEntry> LoadItems(int charId)
{
List<ItemsEntry> items = new List<ItemsEntry>();
SqlWrapper sqlWrapper = new SqlWrapper();
try
{
string sqlQuery =
"SELECT `Placement`, `Flags`, `MultipleCount`, `Type`, `Instance`, `LowID`, `HighID`, `Quality`, `Nothing` FROM `inventory` WHERE ID = "
+ "'" + charId + "' ORDER BY Placement ASC";
DataTable dataTable = sqlWrapper.ReadDatatable(sqlQuery);
foreach (DataRow itemRow in dataTable.Rows)
{
ItemsEntry itemEntry = new ItemsEntry();
itemEntry.Placement = (Int32)itemRow["Placement"];
itemEntry.Flags = (Int16)itemRow["Flags"];
itemEntry.MultipleCount = (Int16)itemRow["MultipleCount"];
itemEntry.ItemType = (Int32)itemRow["Type"];
itemEntry.Instance = (Int32)itemRow["Instance"];
itemEntry.LowId = (Int32)itemRow["LowID"];
itemEntry.HighId = (Int32)itemRow["HighID"];
itemEntry.Quality = (Int32)itemRow["Quality"];
itemEntry.Nothing = (Int32)itemRow["Nothing"];
items.Add(itemEntry);
}
}
catch (Exception e)
{
sqlWrapper.sqlclose();
Console.WriteLine("Error: CharacterID: " + charId + "Message: " + e.Message);
}
return items;
}
public bool LoadTemplate(string hash)
{
SqlWrapper sqlWrapper = new SqlWrapper();
DataTable dataTable = sqlWrapper.ReadDatatable("SELECT * from vendortemplate WHERE HASH='" + hash + "'");
if (dataTable.Rows.Count > 0)
{
this.TemplateId = (Int32)dataTable.Rows[0]["itemtemplate"];
this.Name = (string)dataTable.Rows[0]["Name"];
AOItem item = ItemHandler.GetItemTemplate(this.TemplateId);
foreach (AOItemAttribute ia in item.Stats)
{
this.Stats.SetStatValueByName(ia.Stat, (uint)ia.Value);
}
sqlWrapper.sqlclose();
this.FillInventory();
return true;
}
return false;
}
public bool LoadTemplate(string hash)
{
SqlWrapper Sql = new SqlWrapper();
DataTable dt = Sql.ReadDT("SELECT * from vendortemplate WHERE HASH='" + hash + "'");
if (dt.Rows.Count > 0)
{
TemplateID = (Int32)dt.Rows[0]["itemtemplate"];
Name = (string)dt.Rows[0]["Name"];
ItemHandler.Item it = new ItemHandler.Item(TemplateID);
foreach (AOItemAttribute ia in it.ItemAttributes)
{
this.Stats.Set(ia.Stat, (uint)ia.Value);
}
Sql.sqlclose();
fillInventory();
return true;
}
return false;
}
public void SetOffline(int charID)
{
SqlWrapper sql = new SqlWrapper();
sql.SqlUpdate("UPDATE characters SET Online = 0 WHERE ID = " + charID + ";");
sql.sqlclose();
}
//static CharStatus()
//{
// SetAllOffline();
//}
public void SetAllOffline()
{
SqlWrapper sql = new SqlWrapper();
sql.SqlUpdate("UPDATE characters SET Online = 0");
sql.sqlclose();
}
/// <summary>
/// Check our tables and create/fill them if they don't exist
/// </summary>
public void CheckDBs()
{
SqlWrapper ms = new SqlWrapper();
List<string> tablelist = new List<string>();
List<string> tabletodo = new List<string>();
bool allok = true;
// ToDo: check if database exists and create it if not (parsing the connection string)
if (this.ismssql)
{
ms.SqlRead("SELECT table_name FROM INFORMATION_SCHEMA.TABLES;");
}
else if (this.isnpgsql)
{
ms.SqlRead("SELECT table_name FROM information_schema.tables;");
}
else if (this.ismysql)
{
ms.SqlRead("show Tables");
}
if (ms.myreader.HasRows)
{
while (ms.myreader.Read())
{
tablelist.Add(ms.myreader.GetString(0));
}
}
else
{
allok = false;
}
ms.sqlclose();
string[] sqlfiles = Directory.GetFiles("SQLTables");
bool isin;
foreach (string s in sqlfiles)
{
isin = false;
foreach (string table in tablelist)
{
if (s.ToLower() == Path.Combine("SQLTables", table + ".sql").ToLower())
{
isin = true;
break;
}
}
if (!isin)
{
tabletodo.Add(s);
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Table " + s + " doesn't exist.");
allok = false;
}
}
if (!allok)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.Write("SQL Tables are not complete. Should they be created? (Y/N) ");
string answer = Console.ReadLine();
string sqlquery;
if (answer.ToLower() == "y")
{
foreach (string todo in tabletodo)
{
long filesize = new FileInfo(todo).Length;
Console.ForegroundColor = ConsoleColor.Green;
Console.Write("Table " + todo.PadRight(67) + "[ 0%]");
if (filesize > 10000)
{
string[] queries = File.ReadAllLines(todo);
int c = 0;
sqlquery = string.Empty;
string lastpercent = "0";
while (c < queries.Length)
{
if (queries[c].IndexOf("INSERT INTO") == -1)
{
sqlquery += queries[c] + "\n";
}
else
{
c--;
break;
}
c++;
}
ms.SqlInsert(sqlquery);
c++;
string buf1 = string.Empty;
while (c < queries.Length)
{
if (queries[c].ToLower().Substring(0, 11) == "insert into")
{
break;
}
c++;
}
if (c < queries.Length)
{
buf1 = queries[c].Substring(0, queries[c].ToLower().IndexOf("values"));
buf1 = buf1 + "VALUES ";
StringBuilder Buffer = new StringBuilder(0, 1 * 1024 * 1024);
while (c < queries.Length)
{
if (Buffer.Length == 0)
{
Buffer.Append(buf1);
}
string part = string.Empty;
while (c < queries.Length)
{
if (queries[c].Trim() != string.Empty)
{
part = queries[c].Substring(queries[c].ToLower().IndexOf("values"));
part = part.Substring(part.IndexOf("(")); // from '(' to end
part = part.Substring(0, part.Length - 1); // Remove ';'
if (Buffer.Length + 1 + part.Length > 1024 * 1000)
{
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
Buffer.Append(buf1);
string lp2 =
Convert.ToInt32(Math.Floor((double)c / queries.Length * 100))
.ToString();
if (lp2 != lastpercent)
{
Console.Write(
"\rTable " + todo.PadRight(67) + "[" + lp2.PadLeft(3) + "%]");
lastpercent = lp2;
}
}
Buffer.Append(part + ", ");
}
c++;
}
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
string lp = Convert.ToInt32(Math.Floor((double)c / queries.Length * 100)).ToString();
if (lp != lastpercent)
{
Console.Write("\rTable " + todo.PadRight(67) + "[" + lp.PadLeft(3) + "%]");
lastpercent = lp;
}
}
}
else
{
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
}
else
{
sqlquery = File.ReadAllText(todo);
ms.SqlInsert(sqlquery);
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
Console.WriteLine();
}
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Database is fine.");
}
public static int CacheAllStatels()
{
SqlWrapper ms = new SqlWrapper();
int count = 0;
DataTable dt = ms.ReadDT("SELECT * FROM statels ORDER BY id ASC");
dt_args = ms.ReadDT("SELECT * FROM statel_function_arguments ORDER BY statel_id, event_id, function_id, attrid ASC");
dt_events = ms.ReadDT("SELECT * FROM statel_events ORDER BY statel_id, eventid ASC");
dt_reqs = ms.ReadDT("SELECT * FROM statel_function_reqs ORDER BY statel_id, event_id, function_id, reqid ASC");
dt_functions = ms.ReadDT("SELECT * FROM statel_functions ORDER BY statel_id, event_id, functionid ASC");
int maxcount = 0;
ms.sqlclose();
List<Statel> temp;
maxcount = dt.Rows.Count;
foreach (DataRow dr in dt.Rows)
{
int pf = (Int32)dr[0];
if (Statelppf.ContainsKey(pf) == false)
{
temp = new List<Statel>();
Statelppf.Add(pf, temp);
temp = new List<Statel>();
StatelppfonEnter.Add(pf, temp);
temp = new List<Statel>();
StatelppfonUse.Add(pf, temp);
}
Statel tempstatel = new Statel();
tempstatel.Coordinates.x = (Single)dr[5];
tempstatel.Coordinates.y = (Single)dr[6];
tempstatel.Coordinates.z = (Single)dr[7];
tempstatel.Type = (Int32)dr[1];
tempstatel.Instance = (UInt32)dr[2];
tempstatel.PlayField = (Int32)dr[0];
tempstatel.Template = (Int32)dr[3];
tempstatel.LoadEvents((Int32)dr[4]);
Statelppf[pf].Add(tempstatel);
foreach (Statel_Event e in tempstatel.Events)
{
if ((e.EventNumber == ItemHandler.eventtype_onenter) || (e.EventNumber == ItemHandler.eventtype_ontargetinvicinity))
{
StatelppfonEnter[pf].Add(tempstatel);
}
if (e.EventNumber == ItemHandler.eventtype_onuse)
{
StatelppfonUse[pf].Add(tempstatel);
}
}
count++;
if ((count % 10) == 0)
{
Console.Write("\rReading statels: " + count.ToString() + "/" + maxcount.ToString() + " \r");
}
}
ms.sqlclose();
Console.Write(" \r");
dt_args.Clear();
dt_events.Clear();
dt_functions.Clear();
dt_reqs.Clear();
dt_args = null;
dt_events = null;
dt_functions = null;
dt_reqs = null;
dt.Clear();
return count;
}
/// <summary>
/// Check our tables and create/fill them if they don't exist
/// </summary>
public void CheckDBs()
{
SqlWrapper ms = new SqlWrapper();
List <string> tablelist = new List <string>();
List <string> tabletodo = new List <string>();
bool allok = true;
// ToDo: check if database exists and create it if not (parsing the connection string)
if (this.ismssql)
{
ms.SqlRead("SELECT table_name FROM INFORMATION_SCHEMA.TABLES;");
}
else if (this.isnpgsql)
{
ms.SqlRead("SELECT table_name FROM information_schema.tables;");
}
else if (this.ismysql)
{
ms.SqlRead("show Tables");
}
if (ms.myreader.HasRows)
{
while (ms.myreader.Read())
{
tablelist.Add(ms.myreader.GetString(0));
}
}
else
{
allok = false;
}
ms.sqlclose();
string[] sqlfiles = Directory.GetFiles("SQLTables");
bool isin;
foreach (string s in sqlfiles)
{
isin = false;
foreach (string table in tablelist)
{
if (s.ToLower() == Path.Combine("SQLTables", table + ".sql").ToLower())
{
isin = true;
break;
}
}
if (!isin)
{
tabletodo.Add(s);
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Table " + s + " doesn't exist.");
allok = false;
}
}
if (!allok)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.Write("SQL Tables are not complete. Should they be created? (Y/N) ");
string answer = Console.ReadLine();
string sqlquery;
if (answer.ToLower() == "y")
{
foreach (string todo in tabletodo)
{
long filesize = new FileInfo(todo).Length;
Console.ForegroundColor = ConsoleColor.Green;
Console.Write("Table " + todo.PadRight(67) + "[ 0%]");
if (filesize > 10000)
{
string[] queries = File.ReadAllLines(todo);
int c = 0;
sqlquery = string.Empty;
string lastpercent = "0";
while (c < queries.Length)
{
if (queries[c].IndexOf("INSERT INTO") == -1)
{
sqlquery += queries[c] + "\n";
}
else
{
c--;
break;
}
c++;
}
ms.SqlInsert(sqlquery);
c++;
string buf1 = string.Empty;
while (c < queries.Length)
{
if (queries[c].ToLower().Substring(0, 11) == "insert into")
{
break;
}
c++;
}
if (c < queries.Length)
{
buf1 = queries[c].Substring(0, queries[c].ToLower().IndexOf("values"));
buf1 = buf1 + "VALUES ";
StringBuilder Buffer = new StringBuilder(0, 1 * 1024 * 1024);
while (c < queries.Length)
{
if (Buffer.Length == 0)
{
Buffer.Append(buf1);
}
string part = string.Empty;
while (c < queries.Length)
{
if (queries[c].Trim() != string.Empty)
{
part = queries[c].Substring(queries[c].ToLower().IndexOf("values"));
part = part.Substring(part.IndexOf("(")); // from '(' to end
part = part.Substring(0, part.Length - 1); // Remove ';'
if (Buffer.Length + 1 + part.Length > 1024 * 1000)
{
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
Buffer.Append(buf1);
string lp2 =
Convert.ToInt32(Math.Floor((double)c / queries.Length * 100))
.ToString();
if (lp2 != lastpercent)
{
Console.Write(
"\rTable " + todo.PadRight(67) + "[" + lp2.PadLeft(3) + "%]");
lastpercent = lp2;
}
}
Buffer.Append(part + ", ");
}
c++;
}
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
string lp = Convert.ToInt32(Math.Floor((double)c / queries.Length * 100)).ToString();
if (lp != lastpercent)
{
Console.Write("\rTable " + todo.PadRight(67) + "[" + lp.PadLeft(3) + "%]");
lastpercent = lp;
}
}
}
else
{
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
}
else
{
sqlquery = File.ReadAllText(todo);
ms.SqlInsert(sqlquery);
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
Console.WriteLine();
}
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Database is fine.");
}
/// <summary>
///
/// </summary>
/// <returns></returns>
private Int32 CreateNewChar()
{
SqlWrapper ms = new SqlWrapper();
Int32 charID = 0;
switch (this.Breed)
{
case 0x1: /* solitus */
this.Abis = new int[6] { 6, 6, 6, 6, 6, 6 };
break;
case 0x2: /* opifex */
this.Abis = new int[6] { 3, 3, 10, 6, 6, 15 };
break;
case 0x3: /* nanomage */
this.Abis = new int[6] { 3, 10, 6, 15, 3, 3 };
break;
case 0x4: /* atrox */
this.Abis = new int[6] { 15, 3, 3, 3, 10, 6 };
break;
default:
Console.WriteLine("unknown breed: ", this.Breed);
break;
}
/*
* Note, all default values are not specified here as defaults are handled
* in the CharacterStats Class for us automatically. Also minimises SQL
* usage for default stats that are never changed from their default value
* ~NV
*/
ms.SqlDelete("DELETE FROM `characters_stats` WHERE ID=" + charID);
String sqlInsert = "INSERT INTO `characters` (`Username`,`Name`,`FirstName`,`LastName`,";
String sqlValues = "VALUES('" + this.AccountName + "','" + this.Name + "','','',";
sqlInsert += "`playfield`,`X`,`Y`,`Z`,`HeadingX`,`HeadingY`,`HeadingZ`,`HeadingW`)";
sqlValues += "0,0,0,0,0,0,0,0)";
sqlInsert += sqlValues;
try
{
ms.SqlInsert(sqlInsert);
}
catch (Exception e)
{
Console.WriteLine(sqlInsert + e.Message);
return 0;
}
try
{
/* select new char id */
string sqlQuery = "SELECT `ID` FROM `characters` WHERE Name = " + "'" + this.Name + "'";
DataTable dt = ms.ReadDatatable(sqlQuery);
foreach (DataRow row in dt.Rows)
{
charID = (Int32)row[0];
}
}
catch (Exception e)
{
ms.sqlclose();
Console.WriteLine(this.Name + e.Message);
return 0;
}
ms.SqlDelete("DELETE FROM `characters_stats` WHERE ID=" + charID);
sqlInsert = "INSERT INTO `characters_stats` (`ID`, `Stat`, `Value`) VALUES ";
// Flags / 0 (Player)
sqlInsert += "(" + charID + ", 0, " + 20 + "),";
// Level / 54
sqlInsert += "(" + charID + ", 54, " + 1 + "),";
// HeadMesh / 64
sqlInsert += "(" + charID + ", 64, " + this.HeadMesh + "),";
// MonsterScale / 360
sqlInsert += "(" + charID + ", 360, " + this.MonsterScale + "),";
// Sex / 59
sqlInsert += "(" + charID + ", 59, " + this.Gender + "),";
// VisualSex / 369
sqlInsert += "(" + charID + ", 369, " + this.Gender + "),";
// Breed / 4
sqlInsert += "(" + charID + ", 4, " + this.Breed + "),";
// VisualBreed / 367
sqlInsert += "(" + charID + ", 367, " + this.Breed + "),";
// Profession / 60
sqlInsert += "(" + charID + ", 60, " + this.Profession + "),";
// VisualProfession / 368
sqlInsert += "(" + charID + ", 368, " + this.Profession + "),";
// Fatness / 47
sqlInsert += "(" + charID + ", 47, " + this.Fatness + "),";
// Strength / 16
sqlInsert += "(" + charID + ", 16, " + this.Abis[0] + "),";
// Psychic / 21
sqlInsert += "(" + charID + ", 21, " + this.Abis[1] + "),";
// Sense / 20
sqlInsert += "(" + charID + ", 20, " + this.Abis[2] + "),";
// Intelligence / 19
sqlInsert += "(" + charID + ", 19, " + this.Abis[3] + "),";
// Stamina / 18
sqlInsert += "(" + charID + ", 18, " + this.Abis[4] + "),";
// Agility / 17
sqlInsert += "(" + charID + ", 17, " + this.Abis[5] + "),";
// Set HP and NP auf 1
sqlInsert += "(" + charID + ",1,1),";
sqlInsert += "(" + charID + ",214,1);";
ms.SqlInsert(sqlInsert);
return charID;
}
