/// <summary>
/// This request provides details on a particular players map info
/// </summary>
/// <queryParam name="pid" type="int">The unique player ID</queryParam>
/// <queryParam name="mapid" type="int">The unique map ID</queryParam>
/// <queryParam name="customonly" type="int">Defines whether to only display custom maps</queryParam>
/// <queryParam name="mapname" type="string">The unique map's name</queryParam>
/// <param name="Client">The HttpClient who made the request</param>
/// <param name="Driver">The Stats Database Driver. Connection errors are handled in the calling object</param>
public GetMapInfo(HttpClient Client, StatsDatabase Driver)
{
// Setup Variables
int Pid = 0, MapId = 0, CustomOnly = 0;
string MapName = "";
SelectQueryBuilder Query = new SelectQueryBuilder(Driver);
// Setup QueryString Params
if (Client.Request.QueryString.ContainsKey("pid"))
Int32.TryParse(Client.Request.QueryString["pid"], out Pid);
if (Client.Request.QueryString.ContainsKey("mapid"))
Int32.TryParse(Client.Request.QueryString["mapid"], out MapId);
if (Client.Request.QueryString.ContainsKey("customonly"))
Int32.TryParse(Client.Request.QueryString["customonly"], out CustomOnly);
if (Client.Request.QueryString.ContainsKey("mapname"))
MapName = Client.Request.QueryString["mapname"].Trim();
// Prepare Response
Client.Response.WriteResponseStart();
// Is this a Player Map Request?
if (Pid != 0)
{
// Build our query statement
Query.SelectFromTable("maps");
Query.SelectColumns("maps.*", "mapinfo.name AS mapname");
Query.AddJoin(JoinType.InnerJoin, "mapinfo", "id", Comparison.Equals, "maps", "mapid");
Query.AddWhere("maps.id", Comparison.Equals, Pid);
Query.AddOrderBy("mapid", Sorting.Ascending);
// Execute the reader, and add each map to the output
Client.Response.WriteHeaderLine("mapid", "mapname", "time", "win", "loss", "best", "worst");
foreach (Dictionary<string, object> Map in Driver.QueryReader(Query.BuildCommand()))
Client.Response.WriteDataLine(Map["mapid"], Map["mapname"], Map["time"], Map["win"], Map["loss"], Map["best"], Map["worst"]);
}
else
{
// Build our query statement
Query.SelectFromTable("mapinfo");
Query.SelectColumns("id", "name", "score", "time", "times", "kills", "deaths");
Query.AddOrderBy("id", Sorting.Ascending);
// Select our where statement
if (MapId > 0)
Query.AddWhere("id", Comparison.Equals, MapId);
else if (!String.IsNullOrEmpty(MapName))
Query.AddWhere("name", Comparison.Equals, MapName);
else if (CustomOnly == 1)
Query.AddWhere("id", Comparison.GreaterOrEquals, 700);
// Execute the reader, and add each map to the output
Client.Response.WriteHeaderLine("mapid", "name", "score", "time", "times", "kills", "deaths");
foreach (Dictionary<string, object> Map in Driver.QueryReader(Query.BuildCommand()))
Client.Response.WriteDataLine(Map["id"], Map["name"], Map["score"], Map["time"], Map["times"], Map["kills"], Map["deaths"]);
}
// Send Response
Client.Response.Send();
}
/// <summary>
/// Fills the DataGridView with a list of players
/// </summary>
private void BuildList()
{
// Define initial variables
int Limit = Int32.Parse(LimitSelect.SelectedItem.ToString());
string Like = SearchBox.Text.Replace("'", "").Trim();
List<Dictionary<string, object>> Rows;
WhereClause Where = null;
// Start Record
int Start = (ListPage == 1) ? 0 : (ListPage - 1) * Limit;
// Build Query
SelectQueryBuilder Query = new SelectQueryBuilder(Driver);
Query.SelectColumns("id", "name", "clantag", "rank", "score", "country", "permban");
Query.SelectFromTable("player");
Query.AddOrderBy(SortedCol.Name, ((SortDir == ListSortDirection.Ascending) ? Sorting.Ascending : Sorting.Descending));
Query.Limit(Limit, Start);
// User entered search
if (!String.IsNullOrWhiteSpace(Like))
Where = Query.AddWhere("name", Comparison.Like, "%" + Like + "%");
// Clear out old junk
DataTable.Rows.Clear();
// Add players to data grid
int RowCount = 0;
foreach (Dictionary<string, object> Row in Driver.QueryReader(Query.BuildCommand()))
{
DataTable.Rows.Add(new object[] {
Image.FromStream(Me.GetManifestResourceStream("BF2Statistics.Resources.rank_" + Row["rank"].ToString() + "icon.gif")),
Row["id"].ToString(),
Row["name"].ToString(),
Row["clantag"].ToString(),
Row["score"].ToString(),
Row["country"].ToString(),
Row["permban"].ToString(),
});
RowCount++;
}
// Get Filtered Rows
Query = new SelectQueryBuilder(Driver);
Query.SelectCount();
Query.SelectFromTable("player");
if (Where != null)
Query.AddWhere(Where);
Rows = Driver.ExecuteReader(Query.BuildCommand());
int TotalFilteredRows = Int32.Parse(Rows[0]["count"].ToString());
// Get Total Player Count
Query = new SelectQueryBuilder(Driver);
Query.SelectCount();
Query.SelectFromTable("player");
Rows = Driver.ExecuteReader(Query.BuildCommand());
int TotalRows = Int32.Parse(Rows[0]["count"].ToString());
// Stop Count
int Stop = (ListPage == 1) ? RowCount : ((ListPage - 1) * Limit) + RowCount;
// First / Previous button
if (ListPage == 1)
{
FirstBtn.Enabled = false;
PreviousBtn.Enabled = false;
}
else
{
FirstBtn.Enabled = true;
PreviousBtn.Enabled = true;
}
// Next / Last Button
LastBtn.Enabled = false;
NextBtn.Enabled = false;
// Get total number of pages
if (TotalFilteredRows / (ListPage * Limit) > 0)
{
float total = float.Parse(TotalFilteredRows.ToString()) / float.Parse(Limit.ToString());
TotalPages = Int32.Parse(Math.Floor(total).ToString());
if (TotalFilteredRows % Limit != 0)
TotalPages++;
LastBtn.Enabled = true;
NextBtn.Enabled = true;
}
// Set page number
PageNumber.Maximum = TotalPages;
PageNumber.Value = ListPage;
// Update Row Count Information
RowCountDesc.Text = String.Format(
"Showing {0} to {1} of {2} player{3}",
++Start,
Stop,
TotalFilteredRows,
((TotalFilteredRows > 1) ? "s " : " ")
);
// Add Total row count
if (!String.IsNullOrWhiteSpace(Like))
RowCountDesc.Text += String.Format("(filtered from " + TotalRows + " total player{0})", ((TotalRows > 1) ? "s" : ""));
// Update
DataTable.Update();
}
/// <summary>
/// This request provides claninfo to the bf2server, to set the filter rules
/// for players joining the server.
/// </summary>
/// <queryParam name="type" type="int">The Listype (whitelist, blacklist)</queryParam>
/// <queryParam name ="clantag" type="string">Specified the required clantag</queryParam>
/// <queryParam name="score" type="int">The minimum required score</queryParam>
/// <queryParam name="rank" type="int">The minimum required ranked</queryParam>
/// <queryParam name="time" type="int">The minimum required global time</queryParam>
/// <queryParam name="kdratio" type="float">The minimum required kill/death ratio</queryParam>
/// <queryParam name="country" type="string">The country code (Ex: us, br, no) required, seperated by comma, that is required</queryParam>
/// <queryParam name="banned" type="int">Specified the maximum ban count to be accepted into the list</queryParam>
/// <param name="Client">The HttpClient who made the request</param>
/// <param name="Driver">The Stats Database Driver. Connection errors are handled in the calling object</param>
public GetClanInfo(HttpClient Client, StatsDatabase Database)
{
int Type = 0;
Dictionary<string, string> QueryString = Client.Request.QueryString;
Driver = Database;
// make sure we have a valid player ID
if (!QueryString.ContainsKey("type") || !Int32.TryParse(QueryString["type"], out Type))
{
Client.Response.WriteResponseStart(false);
Client.Response.WriteHeaderLine("asof", "err");
Client.Response.WriteDataLine(DateTime.UtcNow.ToUnixTimestamp(), "Invalid Syntax!");
Client.Response.Send();
return;
}
// Filler Variables
int I = 0;
float F;
string S;
List<DbParameter> Params = new List<DbParameter>();
// Prepare Query
SelectQueryBuilder Query = new SelectQueryBuilder(Driver);
Query.SelectColumns("id", "name");
Query.SelectFromTable("player");
Query.SetWhereOperator(LogicOperator.And);
Query.AddWhere("ip", Comparison.NotEqualTo, "0.0.0.0");
Query.AddOrderBy("id", Sorting.Ascending);
WhereClause Where = null;
switch (Type)
{
// Blacklist
case 0:
int BanLimit = (QueryString.ContainsKey("banned") && Int32.TryParse(QueryString["banned"], out I)) ? I : 100;
Where = new WhereClause("banned", Comparison.GreaterOrEquals, BanLimit);
Where.AddClause(LogicOperator.Or, "permban", Comparison.Equals, 1);
break;
// Whitelist
case 1:
if (QueryString.ContainsKey("clantag"))
{
Where = new WhereClause("clantag", Comparison.Equals, QueryString["clantag"]);
Where.AddClause(LogicOperator.And, "permban", Comparison.Equals, 0);
}
break;
// Greylist
case 2:
// List of possible query's
string[] Queries = new string[] { "score", "rank", "time", "kdratio", "country", "banned" };
foreach (string Param in Queries)
{
if (QueryString.ContainsKey(Param))
{
switch (Param)
{
case "score":
case "time":
case "rank":
if (Int32.TryParse(QueryString[Param], out I))
{
if (Where == null)
Where = new WhereClause(Param, Comparison.GreaterOrEquals, I);
else
Where.AddClause(LogicOperator.And, Param, Comparison.GreaterOrEquals, I);
}
break;
case "kdratio":
if (float.TryParse(QueryString["kdratio"], out F))
{
if (Where == null)
Where = new WhereClause("(kills / deaths)", Comparison.GreaterOrEquals, F);
else
Where.AddClause(LogicOperator.And, "(kills / deaths)", Comparison.GreaterOrEquals, F);
}
break;
case "country":
S = QueryString["country"].Replace(",", "','");
if (Where == null)
Where = new WhereClause(Param, Comparison.In, S.Split(','));
else
Where.AddClause(LogicOperator.And, Param, Comparison.In, S.Split(','));
break;
case "banned":
if (Int32.TryParse(QueryString["banned"], out I))
{
if(Where == null)
Where = new WhereClause("banned", Comparison.LessThan, I);
else
Where.AddClause(LogicOperator.And,"banned", Comparison.LessThan, I);
Where.AddClause(LogicOperator.And, "permban", Comparison.Equals, 0);
}
break;
}
}
}
break;
}
// Pepare 2 output headers
int size = 0;
FormattedOutput Output1 = new FormattedOutput("size", "asof");
FormattedOutput Output2 = new FormattedOutput("pid", "nick");
// Query the database, add each player to Output 2
if(Where != null) Query.AddWhere(Where);
List<Dictionary<string, object>> Players = Driver.ExecuteReader(Query.BuildCommand());
foreach (Dictionary<string, object> P in Players)
{
size++;
Output2.AddRow(P["id"].ToString(), P["name"].ToString());
}
// Send Response
Output1.AddRow(size, DateTime.UtcNow.ToUnixTimestamp());
Client.Response.AddData(Output1);
Client.Response.AddData(Output2);
Client.Response.Send();
}
/// <summary>
/// Loads the players stats from the database, and fills out the forms
/// labels with the current information
/// </summary>
private void LoadPlayer()
{
StatsDatabase Driver;
// Establish DB connection
try
{
Driver = new StatsDatabase();
}
catch (DbConnectException Ex)
{
ExceptionForm.ShowDbConnectError(Ex);
HttpServer.Stop();
Load += (s, e) => Close(); // Close form
return;
}
// Fetch Player from database
SelectQueryBuilder Builder = new SelectQueryBuilder(Driver);
Builder.SelectFromTable("player");
Builder.SelectColumns(
"name", "score", "cmdscore", "skillscore", "teamscore", "joined",
"country", "rank", "wins", "losses", "permban", "clantag", "isbot");
Builder.AddWhere("id", Comparison.Equals, Pid);
List<Dictionary<string, object>> Rows = Driver.ExecuteReader(Builder.BuildCommand());
Player = Rows[0];
// Set window title
this.Text = String.Concat(Player["name"].ToString().Trim(), " (", Pid, ")");
// Set country flag
try
{
string Country = String.IsNullOrEmpty(Player["country"].ToString()) ? "XX" : Player["country"].ToString();
CountryPicture.Image = Image.FromStream(Program.GetResource("BF2Statistics.Resources." + Country.ToUpper() + ".png"));
}
catch { }
// Joined Label
int Joind = Int32.Parse(Player["joined"].ToString());
DateTime D = DateTime.UtcNow.FromUnixTimestamp(Joind);
LabelJoined.Text = String.Concat(D.ToString("yyyy-MM-dd HH:mm"), " GMT");
Tipsy.SetToolTip(LabelJoined, String.Concat(D.ToLocalTime().ToString("yyyy-MM-dd HH:mm"), " Local Time."));
// Fill out the rest of the labels
LabelNick.Text = Player["name"].ToString().Trim();
ClanTagBox.Text = Player["clantag"].ToString();
RankSelect.SelectedIndex = Int32.Parse(Player["rank"].ToString());
PermBanSelect.SelectedIndex = Int32.Parse(Player["permban"].ToString());
LabelGlobalScore.Text = Player["score"].ToString();
LabelWinLoss.Text = String.Concat(Player["wins"], " / ", Player["losses"]);
LabelTeamScore.Text = Player["teamscore"].ToString();
LabelCombatScore.Text = Player["skillscore"].ToString();
LabelCommandScore.Text = Player["cmdscore"].ToString();
// Get Leaderboard Position
Rows = Driver.Query("SELECT COUNT(id) as count FROM player WHERE score > @P0", Int32.Parse(Player["score"].ToString()));
int Position = Int32.Parse(Rows[0]["count"].ToString()) + 1;
LabelPosition.Text = Position.ToString();
SaveBtn.Enabled = false;
// Lock unlocks button if player is Bot
if (Int32.Parse(Player["isbot"].ToString()) > 0)
ResetUnlocksBtn.Enabled = false;
// Close Connection
Driver.Dispose();
}
/// <summary>
/// This method is used to fetch an array of columns we need in our detailed rankings
/// page. It also returns in 2 out variables additional data we need to adjust our query
/// </summary>
/// <param name="action">The Action from our MvcRoute</param>
/// <param name="valueIsDecimal">Indicates to the calling method if the Type of return "value" is a decimal</param>
private void FinishQuery(string action, SelectQueryBuilder builder, out bool valueIsDecimal)
{
// Generate our list of columns
List<string> Cols = new List<string>()
{
"player.id AS pid",
"name",
"rank",
"time",
"country",
"score / (time * 1.0 / 60) AS spm",
"(wins * 1.0 / losses) AS wlr",
"(kills * 1.0 / deaths) AS kdr",
};
// Value Type
valueIsDecimal = false;
// Additional function
switch (action.ToLowerInvariant())
{
case "score":
Cols.Add("score AS value");
break;
case "spm":
Cols.Add("score / (time * 1.0 / 60) AS value");
valueIsDecimal = true;
break;
case "wlr":
Cols.Add("(wins * 1.0 / losses) AS value");
valueIsDecimal = true;
break;
case "kdr":
Cols.Add("(kills * 1.0 / deaths) AS value");
valueIsDecimal = true;
break;
case "knife_kdr":
Cols.Add("(weapons.knifekills * 1.0 / weapons.knifedeaths) AS value");
builder.AddJoin(JoinType.InnerJoin, "weapons", "id", Comparison.Equals, "player", "id");
valueIsDecimal = true;
break;
case "sniper_acc":
Cols.Add("(weapons.hit4 * 1.0 / weapons.fired4) AS value");
valueIsDecimal = true;
builder.AddJoin(JoinType.InnerJoin, "weapons", "id", Comparison.Equals, "player", "id");
break;
case "brs":
Cols.Add("rndscore AS value");
break;
case "fc":
Cols.Add("captures AS value");
break;
case "fw":
Cols.Add("(captureassists + captures + neutralizes + defends) AS value");
break;
case "btw":
Cols.Add("teamscore AS value");
break;
case "hpd":
// Hours Per Day
// Timeframe: ((Last Online timstamp - Joined Timestamp) / 1 day (86400 seconds)) Gets the timespan of days played
// Divide Timeframe by: hours played (seconds played (`time` column) / 1 hr (3600 seconds))
Cols.Add("(time / 3600.0) / ((lastonline - joined) / 86400.0) AS value");
valueIsDecimal = true;
break;
case "command":
Cols.Add("cmdscore AS value");
break;
case "rcmds":
Cols.Add("COALESCE((cmdscore * 1.0 / (cmdtime / 60)), 0.0) AS value");
valueIsDecimal = true;
break;
}
builder.SelectColumns(Cols.ToArray());
//builder.WhereStatement.Add("value", Comparison.GreaterThan, 0);
builder.AddOrderBy("value", Sorting.Descending);
}
/// <summary>
/// Fills the DataGridView with a list of accounts
/// </summary>
private void BuildList()
{
// Define initial variables
int Limit = Int32.Parse(LimitSelect.SelectedItem.ToString());
string Like = SearchBox.Text.Replace("'", "").Trim();
List<Dictionary<string, object>> Rows;
WhereClause Where = null;
// Start Record
int Start = (ListPage == 1) ? 0 : (ListPage - 1) * Limit;
// Build Query
SelectQueryBuilder Query = new SelectQueryBuilder(Driver);
Query.SelectColumns("id", "name", "email", "country", "lastip", "session");
Query.SelectFromTable("accounts");
Query.AddOrderBy(SortedCol.Name, ((SortDir == ListSortDirection.Ascending) ? Sorting.Ascending : Sorting.Descending));
Query.Limit(Limit, Start);
// User entered search
if (!String.IsNullOrWhiteSpace(Like))
Where = Query.AddWhere("name", Comparison.Like, "%" + Like + "%");
// Online Accounts
if (OnlineAccountsCheckBox.Checked)
{
if (Where == null)
Where = Query.AddWhere("session", Comparison.NotEqualTo, 0);
else
Where.AddClause(LogicOperator.And, "session", Comparison.NotEqualTo, 0);
}
// Clear out old junk
DataTable.Rows.Clear();
// Add players to data grid
int RowCount = 0;
foreach (Dictionary<string, object> Row in Driver.QueryReader(Query.BuildCommand()))
{
DataTable.Rows.Add(new string[] {
Row["id"].ToString(),
Row["name"].ToString(),
Row["email"].ToString(),
Row["country"].ToString(),
((Row["session"].ToString() == "1") ? "Yes" : "No"),
Row["lastip"].ToString(),
});
RowCount++;
}
// Get Filtered Rows
Query = new SelectQueryBuilder(Driver);
Query.SelectCount();
Query.SelectFromTable("accounts");
if (Where != null)
Query.AddWhere(Where);
Rows = Driver.ExecuteReader(Query.BuildCommand());
int TotalFilteredRows = Int32.Parse(Rows[0]["count"].ToString());
// Get Total Player Count
Query = new SelectQueryBuilder(Driver);
Query.SelectCount();
Query.SelectFromTable("accounts");
Rows = Driver.ExecuteReader(Query.BuildCommand());
int TotalRows = Int32.Parse(Rows[0]["count"].ToString());
// Stop Count
int Stop = (ListPage == 1) ? RowCount : ((ListPage - 1) * Limit) + RowCount;
// First / Previous button
if (ListPage == 1)
{
FirstBtn.Enabled = false;
PreviousBtn.Enabled = false;
}
else
{
FirstBtn.Enabled = true;
PreviousBtn.Enabled = true;
}
// Next / Last Button
LastBtn.Enabled = false;
NextBtn.Enabled = false;
// Get total number of pages
if (TotalFilteredRows / (ListPage * Limit) > 0)
{
float total = float.Parse(TotalFilteredRows.ToString()) / float.Parse(Limit.ToString());
TotalPages = Int32.Parse(Math.Floor(total).ToString());
if (TotalFilteredRows % Limit != 0)
TotalPages++;
LastBtn.Enabled = true;
NextBtn.Enabled = true;
}
// Set page number
PageNumber.Maximum = TotalPages;
PageNumber.Value = ListPage;
// Update Row Count Information
RowCountDesc.Text = String.Format(
"Showing {0} to {1} of {2} account{3}",
++Start,
Stop,
TotalFilteredRows,
((TotalFilteredRows > 1) ? "s " : " ")
);
// Add Total row count
if (!String.IsNullOrWhiteSpace(Like))
RowCountDesc.Text += String.Format("(filtered from " + TotalRows + " total account{0})", ((TotalRows > 1) ? "s" : ""));
// Update
DataTable.Update();
}
public override void HandleRequest()
{
// Setup Params
if (!Request.QueryString.ContainsKey("nick"))
{
Response.WriteResponseStart(false);
Response.WriteHeaderLine("asof", "err");
Response.WriteDataLine(DateTime.UtcNow.ToUnixTimestamp(), "Invalid Syntax!");
Response.Send();
return;
}
// NOTE: The HttpServer will handle the DbConnectException
using (Database = new StatsDatabase())
{
// Setup local vars
int i = 0;
string Nick = Request.QueryString["nick"];
string Sort = (Request.QueryString.ContainsKey("sort")) ? Request.QueryString["sort"] : "a";
string Where = (Request.QueryString.ContainsKey("where")) ? Request.QueryString["where"] : "a";
// Timestamp Header
Response.WriteResponseStart();
Response.WriteHeaderLine("asof");
Response.WriteDataLine(DateTime.UtcNow.ToUnixTimestamp());
// Build our query builder
SelectQueryBuilder builder = new SelectQueryBuilder(Database);
builder.SelectColumns("id", "name", "score");
builder.SelectFromTable("player");
builder.Limit(20);
// Where statement for our query
switch (Where.ToLowerInvariant())
{
default:
case "a": // Any
builder.AddWhere("name", Comparison.Like, "%" + Nick + "%");
break;
case "b": // Begins With
builder.AddWhere("name", Comparison.Like, "%" + Nick);
break;
case "e": // Ends With
builder.AddWhere("name", Comparison.Like, Nick + "%");
break;
case "x": // Exactly
builder.AddWhere("name", Comparison.Equals, Nick);
break;
}
// Add sorting (a = ascending, r = reverse (descending))
if (Sort.Equals("r", StringComparison.InvariantCultureIgnoreCase))
builder.AddOrderBy("name", Sorting.Descending);
else
builder.AddOrderBy("name", Sorting.Ascending);
// Output status
Response.WriteHeaderLine("n", "pid", "nick", "score");
foreach (Dictionary<string, object> Player in builder.ExecuteQuery())
{
Response.WriteDataLine(++i, Player["id"], Player["name"].ToString().Trim(), Player["score"]);
}
// Send Response
Response.Send();
}
}