public static Results Read(BinaryData binaryData, ref int offset)
{
var results = new SearchQuery.Results();
results.Count = (int)binaryData.ReadU32(offset);
offset += 4;
results.Pages = (int)binaryData.ReadU32(offset);
offset += 4;
int count = binaryData.ReadU8(offset);
offset += 1;
for (int i = 0; i < count; i++)
{
results.Patterns.Add(Pattern.Read(binaryData, ref offset));
}
return(results);
}
public Results GetResults()
{
var results = new Results();
var connection = new MySqlConnection(Configuration.DatabaseConnector);
connection.Open();
var command = connection.CreateCommand();
List <(string, object)> @params = new List <(string, object)>();
string sql = "SELECT designs.*, GROUP_CONCAT(DISTINCT tags.Tag) as Tags FROM designs";
if (Tags.Count > 0)
{
sql += " LEFT JOIN tags AS tagSearch ON (tagSearch.DesignID = designs.ID AND tagSearch.Tag IN (";
for (int i = 0; i < Tags.Count; i++)
{
sql += (i > 0 ? "," : "") + "?Tag" + i;
@params.Add(("?tag" + i, Tags[i]));
}
sql += "))";
}
sql += " LEFT JOIN tags AS tags ON (tags.DesignID = designs.ID)";
if ((Phrase != null && Phrase != "") ||
(Type > 0x00) ||
(Creator != null && Creator != ""))
{
sql += " WHERE";
sql += " designs.type " + (ProDesigns ? " > 0" : " = 0");
if (Phrase != null && Phrase != "")
{
sql += " AND designs.name LIKE ?phrase";
@params.Add(("?phrase", "%" + this.Phrase + "%"));
}
if (Type != 0xFF)
{
sql += " AND designs.type = ?type";
@params.Add(("?type", this.Type));
}
if (Creator != null && Creator != "")
{
sql += " AND designs.creator LIKE ?creator";
@params.Add(("?creator", this.Creator));
}
if (Code != null && Code != "")
{
sql += " AND designs.code = ?code";
@params.Add(("?code", this.Code));
}
}
sql += " GROUP BY designs.ID";
if (Tags.Count > 0)
{
sql += " HAVING COUNT(DISTINCT tagSearch.Tag) = " + Tags.Count;
}
sql += " LIMIT " + (Page * PerPage) + ", " + PerPage;
System.Console.WriteLine(sql);
command.CommandText = sql;
foreach (var item in @params)
{
command.Parameters.AddWithValue(item.Item1, item.Item2);
}
var reader = command.ExecuteReader();
while (reader.Read())
{
results.Patterns.Add(Pattern.Read(reader));
}
reader.Close();
reader.Dispose();
command.Dispose();
sql = "SELECT COUNT(t.ID) as c FROM (SELECT designs.ID FROM designs";
if (Tags.Count > 0)
{
sql += " LEFT JOIN tags AS tagSearch ON (tagSearch.DesignID = designs.ID AND tagSearch.Tag IN (";
for (int i = 0; i < Tags.Count; i++)
{
sql += (i > 0 ? "," : "") + "?Tag" + i;
}
sql += "))";
}
if ((Phrase != null && Phrase != "") ||
(Type > 0x00) ||
(Creator != null && Creator != ""))
{
sql += " WHERE";
sql += " designs.type " + (ProDesigns ? " > 0" : " = 0");
if (Phrase != null && Phrase != "")
{
sql += " designs.name LIKE ?phrase";
}
if (Type != 0xFF)
{
sql += " AND designs.type = ?type";
}
if (Creator != null && Creator != "")
{
sql += " AND designs.creator LIKE ?creator";
}
if (Code != null && Code != "")
{
sql += " AND designs.code = ?code";
}
}
if (Tags.Count > 0)
{
sql += " GROUP BY designs.ID";
sql += " HAVING COUNT(tagSearch.DesignID) = " + Tags.Count;
}
sql += ") AS t";
System.Console.WriteLine(sql);
command = connection.CreateCommand();
command.CommandText = sql;
foreach (var item in @params)
{
command.Parameters.AddWithValue(item.Item1, item.Item2);
}
var count = (int)(long)command.ExecuteScalar();
command.Dispose();
connection.Close();
connection.Dispose();
results.Count = count;
results.Pages = ((count - 1) / PerPage) + 1;
return(results);
}
