public int AddMatch(Game game)
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var query = (from res in db.GAMEs where res.Game_Id.Equals(game.ID) && res.League_Id.Equals(game.LeagueID) select res);
if (query.Count() == 0) //AddGame
{
GAME toinsert = new GAME();
// toinsert.Game_Id = game.ID;
toinsert.Team1 = game.TeamOne;
toinsert.Team2 = game.TeamTwo;
toinsert.sDate = game.sDate;
toinsert.Venue = game.Venue;
toinsert.GameType = game.Type;
toinsert.League_Id = game.LeagueID;
db.GAMEs.InsertOnSubmit(toinsert);
db.SubmitChanges();
}
};
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var queryID = (from tm in db.GAMEs
select tm).ToList();
GAME res = queryID.Last();
int ID = res.Game_Id;
return(ID);
};
}
public string Dl_PlayersBySportID(string ID)
{
int _id = Convert.ToInt32(ID);
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
List <TEAMPLAYER> toDelete = (from dl in db.TEAMPLAYERs where dl.Sport_Id == _id select dl).ToList();
if (toDelete == null)
{
return("Failed: Ticket Not Found");
}
else
{
db.TEAMPLAYERs.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
public string registerPlayer(Player player)
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
int isAvail = (from eh in db.TEAMPLAYERs where eh.Name.Equals(player.Name) &&
eh.Sport_Id.Equals(player.SportID) select eh).Count();
if (isAvail == 0)
{
TEAMPLAYER _player = new TEAMPLAYER();
_player.Name = player.Name;
_player.Position = player.Position;
_player.PerformanceRate = player.PerformanceRate;
_player.Description = player.Desc;
_player.Sport_Id = player.SportID;
db.TEAMPLAYERs.InsertOnSubmit(_player);
db.SubmitChanges();
return("Registered " + _player.Name + " successfully");
}
else
{
return("Error: Account already taken");
}
}
catch (Exception e)
{
return(e.GetBaseException().ToString());
}
};
}
public string getTeamImageByTeamName(string strID)
{
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
int ID = Convert.ToInt32(from sp in db.SPORTs where sp.Name.Equals(strID) select sp.Sport_Id);
var innerJoinQuery =
(from file in db.TEAMIMAGEs
where file.Sport_Id.Equals(ID)
select file).FirstOrDefault();
string fileImage = innerJoinQuery.Location;
string output = "";
if (fileImage == null)
{
// assets/img/default.jpg
output = "assets/img/default.jpg";
return(output);
}
else
{
return(fileImage);
}
};
}
catch (Exception)
{
return("assets/img/default.jpg");
}
}
public string deleteLeagueImageByID(string ID)
{
int _id = Convert.ToInt32(ID);
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
List <LEAGUE_IMAGE> toDelete = (from dl in db.LEAGUE_IMAGEs where dl.League_Id == _id select dl).ToList();
if (toDelete == null)
{
return("Failed: Ticket Not Found");
}
else
{
db.LEAGUE_IMAGEs.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
public string UpdateLeague(League _league)
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
var query = (from res in db.LEAGUEs where res.League_Id.Equals(_league.ID) select res);
if (query.Count() != 0)
{
LEAGUE toinsert = query.Single();
toinsert.Name = _league.Name;
toinsert.WinningPrice = _league.Price;
toinsert.Description = _league.Desc;
toinsert.sDate = _league.sDate;
toinsert.eDate = _league.eDate;
db.SubmitChanges();
return("success");
}
else
{
return("failed: No Such team exist");
}
}
catch (Exception)
{
return("failed");
}
};
}
public List <League> GetleagueByUserID(string ID)
{
int _id = Convert.ToInt32(ID);
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
FileUpload fileService = new FileUpload();
var query = (from lg in db.LEAGUEs
where lg.UserId.Equals(_id)
select
new League
{
ID = lg.League_Id,
Name = lg.Name,
AdminName = lg.USER.Name,
foreignID = Convert.ToInt32(lg.UserId),
Price = Convert.ToDecimal(lg.WinningPrice),
Desc = lg.Description,
sDate = Convert.ToDateTime(lg.sDate),
eDate = Convert.ToDateTime(lg.eDate),
NumTeams = Convert.ToInt32(lg.NumTeams),
Category = lg.Category,
ImagePath = fileService.getLeagueImagePath(Convert.ToString(lg.League_Id)),
}).ToList();
return(query);
}
catch (Exception)
{
return(null);
}
};
}
//Get teams in the league with highest average
public List <rep_Teams> gt_LeagueBestTeam(string leagueID)
{
// List<rep_Teams> res = new List<rep_Teams>();
int id = Convert.ToInt32(leagueID);
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
var query = (from sl in db.SPORT_LEAGUEs where sl.LEAGUE.League_Id.Equals(id) select new
rep_Teams
{
Name = sl.SPORT.Name,
numPlayers = Convert.ToInt32(sl.SPORT.NumPlayers),
Average = Convert.ToDecimal(sl.SPORT.Average),
Manager = sl.SPORT.USER.Name,
}).OrderByDescending(t => t.Average).ToList();
if (query != null)
{
return(query);
}
else
{
return(null);
}
}catch (Exception)
{
return(null);
}
}
}
public Log GetLastInput()
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
var query = (from lg in db.LOGs
select new Log
{
ID = lg.Log_Id,
League_ID = Convert.ToInt32(lg.League_Id),
TeamName = lg.TeamName,
Position = Convert.ToInt32(lg.Position),
MatchPlayed = Convert.ToInt32(lg.MatchPlayed),
Wins = Convert.ToInt32(lg.Wins),
Loose = Convert.ToInt32(lg.Loose),
Draws = Convert.ToInt32(lg.Draws),
Points = Convert.ToInt32(lg.Points),
}).ToList();
return(query.Last());
}
catch (Exception)
{
return(null);
}
};
}
public string CreateGameTicket(Game game)
{
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var query = (from res in db.GAME_TICKETs where res.Game_Id.Equals(game.ID) && res.Ticket_Id.Equals(game.ticket_id) select res);
if (query.Count() == 0) //add stats
{
GAME_TICKET toinsert = new GAME_TICKET();
toinsert.Game_Id = game.ID;
toinsert.Price = game.ticket_Price;
toinsert.Date = game.ticket_pDate;
toinsert.Quantity = game.NumTicket;
db.GAME_TICKETs.InsertOnSubmit(toinsert);
db.SubmitChanges();
return("success");
}
else
{
return("failed: Stats already exist");
}
};
}
catch (Exception)
{
return("failed");
}
}
public List <TeamModel> GetAllGamesByUserID(string ID)
{
int _id = Convert.ToInt32(ID);
List <TeamModel> data = new List <TeamModel>();
FileUpload fl = new FileUpload();
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var query = ( //from res in db.SPORTs where res.Type.Equals("Soccer")
//join sl in db.SPORT_LEAGUEs on res.Sport_Id equals sl.Sport_Id
//test league
from lg in db.LEAGUEs
where lg.UserId.Equals(_id)
join gm in db.GAMEs on lg.League_Id equals gm.League_Id
select new TeamModel
{
G_ID = Convert.ToInt32(gm.Game_Id),
TeamOne = gm.Team1,
TeamTwo = gm.Team2,
Date = Convert.ToString(gm.sDate),
Venue = gm.Venue,
LeagueID = Convert.ToInt32(gm.League_Id),
LeaguName = lg.Name,
ImagePath = fl.getGameImagePathById(Convert.ToString(gm.Game_Id)),
}).ToList();
foreach (TeamModel lg in query)
{
data.Add(lg);
}
return(data);
};
}
//Delete Best Player
public string dl_BestPlayer(string ID)
{
int id = Convert.ToInt32(ID);
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
List <BEST_PLAYER> query = (from bp in db.BEST_PLAYERs where bp.league_ID.Equals(id) select bp).ToList();
if (query != null)
{
db.BEST_PLAYERs.DeleteAllOnSubmit(query);
db.SubmitChanges();
return("success");
}
else
{
return("failed : No data found");
}
}
catch (Exception)
{
return("fail: Catched");
}
};
}
//LeagueFixture
public List <TeamModel> GetAllGamesByLeagueID(string ID)
{
FileUpload fl = new FileUpload();
List <TeamModel> items = new List <TeamModel>();
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
int _id = Convert.ToInt32(ID);
var query = (from gm in db.GAMEs where gm.League_Id.Equals(_id) select
new TeamModel
{
G_ID = gm.Game_Id,
TeamOne = gm.Team1,
TeamTwo = gm.Team2,
Date = Convert.ToString(gm.sDate),
Venue = gm.Venue,
Type = gm.GameType,
LeagueID = Convert.ToInt32(gm.League_Id),
ImagePath = fl.getGameImagePathById(Convert.ToString(gm.Game_Id)),
}).ToList();
foreach (TeamModel teams in query)
{
items.Add(teams);
}
return(items);
}catch
{
return(null);
}
};
}
public TeamModel GetGameByID(string id)
{
int _id = Convert.ToInt32(id);
FileUpload fileservice = new FileUpload();
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var query = from gm in db.GAMEs where gm.Game_Id.Equals(_id) select gm;
if (query.Count() != 0)
{
TeamModel data = new TeamModel();
GAME gm = query.First();
data.G_ID = gm.Game_Id;
data.TeamOne = gm.Team1;
data.TeamTwo = gm.Team2;
data.Date = Convert.ToString(gm.sDate);
data.Venue = gm.Venue;
data.LeaguName = gm.LEAGUE.Name;
data.LeagueID = Convert.ToInt32(gm.League_Id);
data.ImagePath = fileservice.getGameImagePathById(Convert.ToString(gm.Game_Id));
return(data);
}
else
{
return(null);
}
};
}
public string EditTeam(Team _team)
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
var query = (from tm in db.SPORTs where tm.Sport_Id.Equals(_team.ID) select tm);
if (query.Count() != 0)
{
SPORT toinsert = query.Single();
// toinsert.Type = _team.Type;
toinsert.Name = _team.Name;
toinsert.NumPlayers = _team.NumPlayers;
toinsert.Description = _team.Desc;
//db.SPORTs.InsertOnSubmit(toinsert);
db.SubmitChanges();
return("success");
}
else
{
return("failed: No Such team exist");
}
}
catch (Exception)
{
return("failed");
}
};
}
public List <Log> GetLogByLeagueID(string ID)
{
int _id = Convert.ToInt32(ID);
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
var query = (from log in db.LOGs where log.League_Id.Equals(_id)
select new Log
{
ID = log.Log_Id,
TeamName = log.TeamName,
League_ID = log.LEAGUE.League_Id,
Position = Convert.ToInt32(log.Position),
MatchPlayed = Convert.ToInt32(log.MatchPlayed),
Wins = Convert.ToInt32(log.Wins),
Loose = Convert.ToInt32(log.Loose),
Draws = Convert.ToInt32(log.Draws),
Points = Convert.ToInt32(log.Points),
}).OrderByDescending(t => t.Points).ToList();
return(query);
}catch (Exception)
{
return(null);
}
};
}
public List <League> getAllLeaguesByCategory(string category)
{
List <Team> items = new List <Team>();
FileUpload fileService = new FileUpload();
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
return(db.LEAGUEs.Where(res => res.Category.Equals(category)).Select(res => new League
{
ID = res.League_Id,
// Type = res.Type,
Name = res.Name,
// NumPlayers = Convert.ToInt32(res.NumPlayers),
// Desc = res.Description,
// foreignID = Convert.ToInt32(res.UserId),
// F_League = res.UserId,
// ImagePath = fileService.getImagePathById(Convert.ToString(res.Sport_Id)),
// ImageLocation = fileService.getImageLocationByEventId(Convert.ToString(ev.E_ID)),
}).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public string UpdateLog(Log log)
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
var query = (from tm in db.LOGs where tm.Log_Id.Equals(log.ID) select tm);
if (query.Count() != 0)
{
LOG toinsert = query.Single();
toinsert.TeamName = log.TeamName;
toinsert.Position = log.Position;
toinsert.MatchPlayed = log.MatchPlayed;
toinsert.Wins = log.Wins;
toinsert.Loose = log.Loose;
toinsert.Draws = log.Draws;
toinsert.Points = log.Points;
// toinsert.League_Id = log.League_ID;
db.SubmitChanges();
return("success");
}
else
{
return("failed: No Such team exist");
}
}
catch (Exception)
{
return("failed");
}
};
}
//Getter=======>>>>
public List <League> GetAllLeagues()
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
FileUpload fileService = new FileUpload();
List <League> leagues = new List <League>();
var query = (from lg in db.LEAGUEs select
new League
{
ID = lg.League_Id,
Name = lg.Name,
Price = Convert.ToDecimal(lg.WinningPrice),
Desc = lg.Description,
sDate = Convert.ToDateTime(lg.sDate),
eDate = Convert.ToDateTime(lg.eDate),
NumTeams = Convert.ToInt32(lg.NumTeams),
Category = lg.Category,
ImagePath = fileService.getLeagueImagePath(Convert.ToString(lg.League_Id)),
}).ToList();
foreach (League league in query)
{
leagues.Add(league);
}
return(leagues);
}catch (Exception)
{
return(null);
}
};
}
public string CreateLog(Log log)
{
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var query = (from res in db.LOGs where res.Log_Id.Equals(log.ID) && res.League_Id.Equals(log.League_ID) select res);
if (query.Count() == 0) //AddTeam
{
LOG toinsert = new LOG();
toinsert.TeamName = log.TeamName;
toinsert.Position = log.Position;
toinsert.MatchPlayed = log.MatchPlayed;
toinsert.Wins = log.Wins;
toinsert.Draws = log.Draws;
toinsert.Points = log.Points;
toinsert.League_Id = log.League_ID;
db.LOGs.InsertOnSubmit(toinsert);
db.SubmitChanges();
return("success");
}
else
{
return("failed: log already exist");
}
};
}
catch (Exception)
{
return("failed: catched");
}
}
public string DeletSportInBT(string sportID)
{
int S_ID = Convert.ToInt32(sportID);
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
List <SPORT_LEAGUE> toDelete = (from dl in db.SPORT_LEAGUEs where dl.Sport_Id == S_ID select dl).ToList();
if (toDelete == null)
{
return("Failed: Ticket Not Found");
}
else
{
db.SPORT_LEAGUEs.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
//Get All Teams by Sport Category
public List <MyTeamModel> getAllTeamByCategory(string category)
{
List <MyTeamModel> items = new List <MyTeamModel>();
FileUpload fileService = new FileUpload();
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
return(db.SPORTs.Where(res => res.Type.Equals(category)).Select(res => new MyTeamModel
{
ID = res.Sport_Id,
Type = res.Type,
Name = res.Name,
NumPlayers = Convert.ToInt32(res.NumPlayers),
Desc = res.Description,
foreignID = Convert.ToInt32(res.UserId),
// F_League = res.UserId,
ManagerName = res.USER.Name,
ImagePath = fileService.getImagePathById(Convert.ToString(res.Sport_Id)),
// ImageLocation = fileService.getImageLocationByEventId(Convert.ToString(ev.E_ID)),
}).ToList());
}
}
catch (Exception)
{
return(null);
}
}
public string saveUserImage(ImageFile image)
{ //trim string location
String imgLocation = image.path;
string output = imgLocation.Substring(imgLocation.IndexOf('U')); //trim string path from Event
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var query = from img in db.USERIMAGEs where img.ImageId.Equals(image.ImageID) select image;
if (query.Count() == 0)
{
USERIMAGE fileToSave = new USERIMAGE();
fileToSave.Name = image.Name;
fileToSave.Location = output;
fileToSave.UserId = Convert.ToInt32(image.foreignID);
db.USERIMAGEs.InsertOnSubmit(fileToSave);
db.SubmitChanges();
}
else
if (query.Count() == 1)
{
return("File Exist");
}
}
return("Success File Uploaded");
}
catch (Exception)
{
return("Failed Upload Failed");
}
}
public TeamModel getTeamsDetails(string ID)
{
int _id = Convert.ToInt32(ID);
FileUpload fileservice = new FileUpload();
PlayerService player = new PlayerService();
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
return((from tm in db.SPORTs
where tm.Sport_Id.Equals(_id)
select new TeamModel
{
s_ID = tm.Sport_Id,
LeaguName = tm.USER.Name, //Manager Name
TeamName = tm.Name,
players = player.getAllTeamPlayers(Convert.ToString(tm.Sport_Id)),
NumPlayers = Convert.ToInt32(tm.NumPlayers),
Desc = tm.Description,
Type = tm.Type,
ImagePath = fileservice.getImagePathById(Convert.ToString(tm.Sport_Id)),
}).First());
}catch (Exception)
{
return(null);
}
};
}
public string getLeagueImagePath(string strID)
{
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var innerJoinQuery =
(from file in db.LEAGUE_IMAGEs
where file.League_Id.Equals(Convert.ToInt32(strID))
select file).FirstOrDefault();
string fileImage = innerJoinQuery.Location;
string output = "";
if (fileImage == null)
{
// assets/img/default.jpg
output = "assets/img/default.jpg";
return(output);
}
else
{
return(fileImage);
}
};
}
catch (Exception)
{
return("assets/img/default.jpg");
}
}
public string DeleteSportByID(string ID)
{
int _id = Convert.ToInt32(ID);
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
List <SPORT> toDelete = (from dl in db.SPORTs where dl.Sport_Id == _id select dl).ToList();
if (toDelete == null)
{
return("Failed:");
}
else
{
db.SPORTs.DeleteAllOnSubmit(toDelete);
db.SubmitChanges();
return("success");
}
};
}
catch (Exception)
{
return("Failed");
}
}
public List <Player> getAllTeamPlayers(string teamID)
{
int _id = Convert.ToInt32(teamID);
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
List <Player> players = new List <Player>();
var query = (from pl in db.TEAMPLAYERs
where pl.Sport_Id.Equals(_id)
select new Player
{
ID = pl.PlayerId,
SportID = Convert.ToInt32(pl.Sport_Id),
Name = pl.Name,
Position = pl.Position,
PerformanceRate = Convert.ToDecimal(pl.PerformanceRate),
Desc = pl.Description,
}).ToList();
foreach (Player items in query)
{
players.Add(items);
}
return(players);
} catch (Exception)
{
return(null);
}
};
}
public List <MyTeamModel> getAllTeamByUserID(string ID)
{
int _id = Convert.ToInt32(ID);
List <MyTeamModel> items = new List <MyTeamModel>();
FileUpload fileService = new FileUpload();
try
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var query = (from lg in db.LEAGUEs
where lg.UserId.Equals(_id)
join sl in db.SPORT_LEAGUEs on lg.League_Id equals sl.League_Id
select new MyTeamModel
{
ID = Convert.ToInt32(sl.Sport_Id),
Name = sl.TeamName,
}).ToList();
return(query);
}
}
catch (Exception)
{
return(null);
}
}
public int registerUser(BASE_USER user)
{
try
{
string hashedPass = HashPassword.HashPass(user.Pass);
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
int isAvail = (from eh in db.USERs where eh.Email.Equals(user.Email) select eh).Count();
if (isAvail == 0)
{
USER _user = new USER();
_user.Name = user.Name;
_user.Level = user.Level;
_user.Surname = user.Surname;
_user.Email = user.Email;
_user.Password = hashedPass;
_user.UserImage = user.imgLocation;
db.USERs.InsertOnSubmit(_user);
db.SubmitChanges();
}
};
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
var queryID = (from eh in db.USERs where eh.Email.Equals(user.Email) select eh);
USER res = queryID.Single();
int ID = res.UserId;
return(ID);
};
}
catch (Exception)
{
return(0);
}
}
public string UpdateUser(BASE_USER user)
{
using (SPORT_LINK_DBDataContext db = new SPORT_LINK_DBDataContext())
{
try
{
string hashedPass = HashPassword.HashPass(user.Pass);
var query = (from res in db.USERs where res.UserId.Equals(user.ID) select res);
if (query.Count() != 0)
{
USER toinsert = query.Single();
toinsert.Name = user.Name;
toinsert.Surname = user.Surname;
toinsert.Email = user.Email;
toinsert.Password = hashedPass;
db.SubmitChanges();
return("success");
}
else
{
return("failed: No Such User exist");
}
}
catch (Exception)
{
return("failed");
}
};
}
