public ActionResult mostGamesWon()
{
//
// var model = repository.GetThingByParameter(parameter1);
// var partialViewModel = new PartialViewModel(model);
List<Player> players = new List<Player>();
Player p1 = new Player();
p1.ScreenName = "p1";
p1.GamesPlayed = 10;
p1.GamesWon = 7;
Player p2 = new Player();
p2.ScreenName = "p2";
p2.GamesPlayed = 12;
p2.GamesWon = 7;
Player p3 = new Player();
p3.ScreenName = "p3";
p3.GamesPlayed = 0;
p3.GamesWon = 7;
Player p4 = new Player();
p4.ScreenName = "p4";
p4.GamesPlayed = 13;
p4.GamesWon = 7;
players.Add(p1);
players.Add(p2);
players.Add(p3);
players.Add(p4);
return PartialView("_mostGamesWon", players);
}
//returns int PlayerId
//required, all of: FirstName, LastName, ScreenName, BDLoginName, Email
public static Player CreateNewPlayer(string FirstName, string LastName, string ScreenName, string BDLoginName, string Email, string RFID)
{
var query = @"INSERT INTO [Players] (FirstName, LastName, ScreenName, BDLoginName, Email, RFID)
OUTPUT Inserted.PlayerId
VALUES (@FirstName, @LastName, @ScreenName, @BDLoginName, @Email, @RFID);";
using (var connection = new SqlConnection(_bdppmasterdb)) {
using (var command = new SqlCommand(query, connection)) {
command.Parameters.AddWithValue("@FirstName", FirstName);
command.Parameters.AddWithValue("@LastName", LastName);
command.Parameters.AddWithValue("@ScreenName", ScreenName);
command.Parameters.AddWithValue("@BDLoginName", BDLoginName);
command.Parameters.AddWithValue("@Email", Email);
command.Parameters.AddWithValue("@RFID", RFID);
connection.Open();
using (var reader = command.ExecuteReader()) {
if (!reader.HasRows) { return null; }
reader.Read(); //only need the first item
var player = new Player()
{
PlayerId = reader.GetInt32(reader.GetOrdinal("PlayerId")),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
ScreenName = reader["screenName"].ToString(),
BDLoginName = reader["BDLoginName"].ToString(),
Email = reader["Email"].ToString(),
RFID = reader["RFID"].ToString()
};
//if (!reader["ProfileImage"].Equals(DBNull.Value)) {
// //process image here
//}
return player;
}
}
}
}
public ActionResult timePlayed()
{
//
// var model = repository.GetThingByParameter(parameter1);
// var partialViewModel = new PartialViewModel(model);
List<Player> players = new List<Player>();
Player p1 = new Player();
p1.ScreenName = "JeffB";
p1.GamesPlayed = 10;
p1.GamesWon = 4;
p1.TimePlayed = new TimeSpan(5, 4, 0);
Player p2 = new Player();
p2.ScreenName = "DanB";
p2.GamesPlayed = 12;
p2.GamesWon = 2;
p2.TimePlayed = new TimeSpan(0, 3, 3);
Player p3 = new Player();
p3.ScreenName = "TalB";
p3.GamesPlayed = 50;
p3.TimePlayed = new TimeSpan(34, 45, 0);
Player p4 = new Player();
p4.ScreenName = "DalipJ";
p4.GamesPlayed = 13;
p4.GamesWon = 7;
p4.TimePlayed = new TimeSpan(4, 4, 1);
players.Add(p1);
players.Add(p2);
players.Add(p3);
players.Add(p4);
return PartialView("_timePlayed", players);
}
public ActionResult percentGamesWon()
{
//
// var model = repository.GetThingByParameter(parameter1);
// var partialViewModel = new PartialViewModel(model);
List<Player> players = new List<Player>();
Player p1 = new Player();
p1.ScreenName = "JeffB";
p1.GamesPlayed = 10;
p1.GamesWon = 4;
Player p2 = new Player();
p2.ScreenName = "DanB";
p2.GamesPlayed = 12;
p2.GamesWon = 2;
Player p3 = new Player();
p3.ScreenName = "TalB";
p3.GamesPlayed = 50;
p3.GamesWon = 45;
Player p4 = new Player();
p4.ScreenName = "DalipJ";
p4.GamesPlayed = 13;
p4.GamesWon = 7;
players.Add(p1);
players.Add(p2);
players.Add(p3);
players.Add(p4);
return PartialView("_percentGamesWon", players);
}
public static Team GetTeamPlayersInfo(int TeamId)
{
var team = new Team();
var query = @"SELECT t.TeamId, p.*
FROM [Teams] t
INNER JOIN [Players] p
ON p.PlayerId IN (t.Player1_Id, t.Player2_Id)
WHERE t.TeamId = @TeamId;";
using (var connection = new SqlConnection(_bdppmasterdb))
{
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@TeamId", TeamId);
connection.Open();
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows) { return null; }
while (reader.Read()) {
team.TeamId = reader.GetInt32(reader.GetOrdinal("TeamId")); //TeamId is constant, overwrite is not a concern
var player = new Player()
{
PlayerId = reader.GetInt32(reader.GetOrdinal("PlayerId")),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
ScreenName = reader["screenName"].ToString(),
BDLoginName = reader["BDLoginName"].ToString(),
Email = reader["Email"].ToString(),
RFID = reader["RFID"].ToString(),
ImageNameWithExt = reader["ImageNameWithExt"].ToString()
};
team.Players.Add(player);
}
return team;
}
}
}
}
//required, one of: FirstName, LastName, ScreenName, BDLoginName, Email, RFID
public static Player GetPlayerInfo(string FirstName, string LastName, string ScreenName, string BDLoginName, string Email, string RFID)
{
var queryParams = new StringBuilder();
#region Append Conditions
if (FirstName != null) { queryParams.Append("FirstName = @FirstName"); }
if (LastName != null) {
if (queryParams.Length > 0) {
queryParams.Append(" AND ");
}
queryParams.Append("LastName = @LastName");
}
if (ScreenName != null)
{
if (queryParams.Length > 0)
{
queryParams.Append(" AND ");
}
queryParams.Append("ScreenName = @ScreenName");
}
if (BDLoginName != null)
{
if (queryParams.Length > 0)
{
queryParams.Append(" AND ");
}
queryParams.Append("BDLoginName = @BDLoginName");
}
if (Email != null)
{
if (queryParams.Length > 0)
{
queryParams.Append(" AND ");
}
queryParams.Append("Email = @Email");
}
if (RFID != null)
{
if (queryParams.Length > 0)
{
queryParams.Append(" AND ");
}
queryParams.Append("RFID = @RFID");
}
#endregion
var query = String.Format("SELECT * FROM [Players] WHERE {0};", queryParams.ToString());
using (var connection = new SqlConnection(_bdppmasterdb))
{
using (var command = new SqlCommand(query.ToString(), connection))
{
connection.Open();
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows) { return null; }
reader.Read(); //only need the first item
var player = new Player()
{
PlayerId = reader.GetInt32(reader.GetOrdinal("PlayerId")),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
ScreenName = reader["screenName"].ToString(),
BDLoginName = reader["BDLoginName"].ToString(),
Email = reader["Email"].ToString(),
RFID = reader["RFID"].ToString(),
ImageNameWithExt = reader["ImageNameWithExt"].ToString()
};
return player;
}
}
}
}
