/// <summary>
/// Search for owner market items
/// </summary>
/// <returns></returns>
public IEnumerable <MarketItemArgument> SearchMarketForOwner(Character target)
{
MySqlConnection connection = ConnectionPool.Request();
MySqlCommand command = new MySqlCommand(_query_09, connection);
command.Parameters.AddWithValue("?CharId", target.ModelId);
MySqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
byte[] buffer = new byte[67];
MarketItemArgument item = new MarketItemArgument();
item.id = reader.GetUInt32(0);
item.sender = reader.GetString(4);
item.price = reader.GetUInt32(7);
item.expires = reader.GetMySqlDateTime(9).GetDateTime();
reader.GetBytes(8, 0, buffer, 0, 67);
Rag2Item.Deserialize(out item.item, buffer, 0);
yield return(item);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
ConnectionPool.Release(connection);
}
}
/// <Sql>
/// SELECT
/// *
/// FROM
/// list_maildata
/// WHERE
/// Sender=?Sender
/// AND
/// IsOutbox=1;
/// </Sql>
public IEnumerable <Mail> GetOutboxMail(Character target)
{
MySqlConnection connection = ConnectionPool.Request();
MySqlCommand command = new MySqlCommand(_query_37, connection);
command.Parameters.AddWithValue("Sender", target.Name);
MySqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
// 0 - MailId
// 1 - Sender
// 2 - Receiptent
// 3 - Date
// 4 - IsRead
// 5 - IsChecked
// 6 - Topic
// 7 - Message
// 8 - Attachment
// 9 - Zeny
Mail mailsubject = new Mail();
mailsubject.MailId = reader.GetUInt32(0);
mailsubject.Sender = reader.GetString(1);
mailsubject.Time = reader.GetDateTime(3);
mailsubject.IsRead = reader.GetByte(4);
mailsubject.Topic = reader.GetString(6);
mailsubject.Message = reader.GetString(7);
mailsubject.Zeny = (int)reader.GetUInt32(9);
if (reader.IsDBNull(8) == false)
{
byte[] buffer = new byte[67];
reader.GetBytes(8, 0, buffer, 0, 67);
Rag2Item.Deserialize(out mailsubject.Attachment, buffer, 0);
}
if (mailsubject.IsRead == 0)
{
mailsubject.Valid = (byte)(30 - Math.Min(30, (int)((DateTime.Now - mailsubject.Time).TotalDays)));
}
yield return(mailsubject);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
ConnectionPool.Release(connection);
}
}
/// <Sql>
/// SELECT
/// *
/// FROM
/// list_maildata
/// WHERE
/// InboxId=?Id
/// LIMIT 1;
/// </Sql>
public MailItem GetMailItemById(MySqlConnection connection, uint id)
{
MySqlCommand command = new MySqlCommand(_query_47, connection);
command.Parameters.AddWithValue("Id", id);
MySqlDataReader reader = null;
try
{
reader = command.ExecuteReader(); // argument CommandBehavior.SingleRow removed (Darkin)
while (reader.Read())
{
MailItem item = new MailItem();
item.Recieptent = reader.GetString(2);
item.Topic = reader.GetString(6);
item.Content = reader.GetString(7);
item.Timestamp = reader.GetDateTime(3);
item.Zeny = reader.GetUInt32(9);
if (reader.IsDBNull(8) == false)
{
byte[] buffer = new byte[67];
reader.GetBytes(8, 0, buffer, 0, 67);
Rag2Item.Deserialize(out item.item, buffer, 0);
}
//Singleton.Item.TryGetItemWithCount(27, 1, out item.item);
return(item);
}
return(null);
}
catch (Exception e)
{
__dbtracelog.WriteError("Database", e.Message);
return(null);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public bool GetItemByAuctionId(MySqlConnection connection, uint id, out AuctionArgument item)
{
MySqlCommand command = new MySqlCommand();
command.CommandText = _query_08;
command.Connection = connection;
command.Parameters.AddWithValue("AuctionId", id);
MySqlDataReader reader = null;
item = null;
try
{
reader = command.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
item = new AuctionArgument();
item.name = reader.GetString(4);
item.zeny = reader.GetUInt32(7);
byte[] buffer = new byte[67];
reader.GetBytes(8, 0, buffer, 0, 67);
Rag2Item.Deserialize(out item.item, buffer, 0);
return(true);
}
}
catch (MySqlException e)
{
__dbtracelog.WriteError("Database", e.Message);
return(false);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
return(false);
}
/// <Sql>
/// SELECT
/// *
/// FROM
/// list_maildata
/// WHERE
/// list_maildata.MailId=1;
/// </Sql>
public Rag2Item GetItemAttachment(uint MailId)
{
MySqlConnection connection = ConnectionPool.Request();
MySqlCommand command = new MySqlCommand(_query_43, connection);
command.Parameters.AddWithValue("Id", MailId);
MySqlDataReader reader = null;
try
{
byte[] buffer = (byte[])command.ExecuteScalar();
if (buffer != null)
{
Rag2Item item;
if (Rag2Item.Deserialize(out item, buffer, 0))
{
return(item);
}
return(null);
}
else
{
return(null);
}
}
catch (Exception e)
{
__dbtracelog.WriteError("Database", e.Message);
return(null);
}
finally
{
if (reader != null)
{
reader.Close();
}
ConnectionPool.Release(connection);
}
}
/// <summary>
/// Search market
/// </summary>
/// <param name="Argument"></param>
/// <returns></returns>
public IEnumerable <MarketItemArgument> SearchMarket(MarketSearchArgument Argument)
{
StringBuilder mysqlcommand = new StringBuilder("SELECT * FROM `auction` WHERE ");
mysqlcommand.AppendFormat("Categorie={0} ", Argument.item_cat);
if (Argument.min_clvl > 0)
{
mysqlcommand.AppendFormat("AND ReqClvl >= {0} ", Argument.min_clvl);
}
if (Argument.max_clvl > 0)
{
mysqlcommand.AppendFormat("AND ReqClvl <= {0} ", Argument.max_clvl);
}
if (Argument.searchType > 0)
{
mysqlcommand.AppendFormat("AND `{0}` LIKE '%{1}%' ", (Argument.searchType == 2) ? "CharName" : "ItemName", Argument.searchstring);
}
if (Argument.item_class > 0)
{
mysqlcommand.AppendFormat("AND Grade = {0} ", Argument.max_clvl);
}
switch (Argument.SortBy)
{
case 0: mysqlcommand.AppendFormat("ORDER BY ItemName ASC ", Argument.max_clvl); break;
case 1: mysqlcommand.AppendFormat("ORDER BY ItemName DESC ", Argument.max_clvl); break;
case 2: mysqlcommand.AppendFormat("ORDER BY CharName ASC ", Argument.max_clvl); break;
case 3: mysqlcommand.AppendFormat("ORDER BY CharName DESC ", Argument.max_clvl); break;
case 4: mysqlcommand.AppendFormat("ORDER BY Price ASC ", Argument.max_clvl); break;
case 5: mysqlcommand.AppendFormat("ORDER BY Price DESC ", Argument.max_clvl); break;
case 6: mysqlcommand.AppendFormat("ORDER BY ReqClvl ASC ", Argument.max_clvl); break;
case 7: mysqlcommand.AppendFormat("ORDER BY ReqClvl DESC ", Argument.max_clvl); break;
default: mysqlcommand.AppendFormat("ORDER BY CharName ASC ", Argument.max_clvl); break;
}
mysqlcommand.AppendFormat("LIMIT {0}, 36", Argument.index * 36);
MySqlConnection connection = ConnectionPool.Request();
MySqlCommand command = new MySqlCommand(mysqlcommand.ToString(), connection);
MySqlDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
byte[] buffer = new byte[67];
MarketItemArgument item = new MarketItemArgument();
item.id = reader.GetUInt32(0);
item.sender = reader.GetString(4);
item.price = reader.GetUInt32(7);
item.expires = reader.GetMySqlDateTime(9).GetDateTime();
reader.GetBytes(8, 0, buffer, 0, 67);
Rag2Item.Deserialize(out item.item, buffer, 0);
yield return(item);
}
}
finally
{
if (reader != null)
{
reader.Close();
}
ConnectionPool.Release(connection);
}
}
/// <Sql>
/// SELECT
/// *
/// FROM
/// list_maildata
/// WHERE
/// Receiptent=?Receiptent
/// AND
/// IsInbox=1;
/// </Sql>
public IEnumerable <Mail> GetInboxMail(MySqlConnection connection, Character target)
{
MySqlCommand command = new MySqlCommand(_query_35, connection);
MySqlCommand command2 = new MySqlCommand(_query_36, connection);
command.Parameters.AddWithValue("Receiptent", target.Name);
command2.Parameters.AddWithValue("Receiptent", target.Name);
MySqlDataReader reader = null;
List <Mail> list = new List <Mail>();
try
{
command2.ExecuteNonQuery();
reader = command.ExecuteReader();
while (reader.Read())
{
Mail mailsubject = new Mail();
mailsubject.MailId = reader.GetUInt32(0);
mailsubject.MailId = reader.GetUInt32(0);
mailsubject.Sender = reader.GetString(2);
mailsubject.Time = reader.GetDateTime(3);
mailsubject.IsRead = reader.GetByte(4);
mailsubject.Topic = reader.GetString(6);
mailsubject.Message = reader.GetString(7);
mailsubject.Zeny = (int)reader.GetUInt32(9);
if (reader.IsDBNull(8) == false)
{
byte[] buffer = new byte[67];
reader.GetBytes(8, 0, buffer, 0, 67);
Rag2Item.Deserialize(out mailsubject.Attachment, buffer, 0);
}
if (mailsubject.IsRead == 0)
{
mailsubject.Valid = (byte)(30 - Math.Min(30, (int)((DateTime.Now - mailsubject.Time).TotalDays)));
}
else
{
DateTime dateread = reader.GetDateTime(10);
mailsubject.Valid = (byte)(7 - Math.Min(7, (int)((DateTime.Now - dateread).TotalDays)));
}
list.Add(mailsubject);
}
return(list);
}
catch (Exception e)
{
__dbtracelog.WriteError("Database", e.Message);
return(list);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}