public ClubDTO GetFromDB(int objectId)
{
using (UaFootball_DBDataContext db = DBManager.GetDB())
{
vwClub c = db.vwClubs.Single(cc => cc.Club_ID == objectId);
ClubDTO dtoObj = ConvertDBObjectToDTO(c);
Multimedia mLogo = (from tag in db.MultimediaTags
where tag.Club_ID == objectId && tag.Multimedia.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.ClubLogo
select tag.Multimedia).FirstOrDefault();
if (mLogo != null)
{
dtoObj.Logo = MultimediaDTO.FromDBObject(mLogo);
dtoObj.Logo.IsUploaded = true;
}
return(dtoObj);
}
}
public static string GetMultimediaWebPath(Page sender, MultimediaDTO dto)
{
return(GetWebPath(sender, Constants.Paths.MutlimediaWebRoot, dto.FilePath + "//thumb//", dto.FileName.Trim()));
}
public PlayerDTO GetPlayer(int playerId)
{
using (var db = DBManager.GetDB())
{
DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith <Multimedia>(m => m.MultimediaTags);
db.LoadOptions = dlo;
var dbPlayer = (from player in db.Players
where player.Player_Id == playerId
select new
{
p = player,
Country_Name = player.Country.Country_Name
}).Single();
PlayerDTO p = ConvertDBObjectToDTO(dbPlayer.p);
p.Country_Name = dbPlayer.Country_Name;
p.Matches = new List <MatchDTO>();
//TODO - optimize db query
var dbMatches = from match in db.vwMatches
join matchLineup in db.MatchLineups on match.Match_ID equals matchLineup.Match_Id
orderby match.Date
where matchLineup.Player_Id == playerId
select new
{
M = match,
ML = matchLineup
//ME = from matchEvent in db.MatchEvents where matchEvent.Match_Id == match.Match_ID && (matchEvent.Player1_Id == playerId || matchEvent.Player2_Id == playerId) select matchEvent
//match.MatchEvents.Where(me => me.Player1_Id == playerId || me.Player2_Id == playerId),
};
var dbMatchEvents = from matchEvent in db.MatchEvents
where matchEvent.Player1_Id == playerId || matchEvent.Player2_Id == playerId
select new { ev = matchEvent, p1 = matchEvent.Player, p2 = matchEvent.Player1 };
foreach (var dbMatch in dbMatches)
{
MatchDTO match = new MatchDTOHelper().ConvertDBObjectToDTO(dbMatch.M);
match.Lineup = new List <MatchLineupDTO>();
MatchLineupDTO ml = new MatchLineupDTO().ConvertDBObjectToDTO(dbMatch.ML);
match.Lineup.Add(ml);
match.Events = new List <MatchEventDTO>();
foreach (var me in dbMatchEvents.Where(me => me.ev.Match_Id == match.Match_Id))
{
MatchEventDTO meDTO = new MatchEventDTO().ConvertDBObjectToDTO(me.ev);
if (db.MultimediaTags.Any(mt => mt.MatchEvent_ID == me.ev.MatchEvent_Id && mt.Multimedia.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.MatchVideo))
{
meDTO.HasVideo = true;
}
if (meDTO.Player2_Id == playerId)
{
meDTO.AppliesToSecondPlayer = true;
}
if (me.p1 != null)
{
meDTO.Player1 = new PlayerDTOHelper().ConvertDBObjectToDTO(me.p1);
}
if (me.p2 != null)
{
meDTO.Player2 = new PlayerDTOHelper().ConvertDBObjectToDTO(me.p2);
}
match.Events.Add(meDTO);
}
p.Matches.Add(match);
}
List <Multimedia> multimedia = (from tag in db.MultimediaTags
join m in db.Multimedias on tag.Multimedia_ID equals m.Multimedia_ID
where tag.Player_ID == playerId
select m).ToList();
List <Multimedia> multimediaVideo = (from tag in db.MultimediaTags
join matchEvent in db.MatchEvents on tag.MatchEvent_ID equals matchEvent.MatchEvent_Id
join m in db.Multimedias on tag.Multimedia_ID equals m.Multimedia_ID
where (matchEvent.Player1_Id == playerId || matchEvent.Player2_Id == playerId) && m.MultimediaType_CD == Constants.DB.MutlimediaTypes.Video
select m).ToList();
multimedia.AddRange(multimediaVideo);
p.Multimedia = multimedia.Select(m => MultimediaDTO.FromDBObject(m)).ToList();
foreach (Multimedia m in multimedia.Where(mm => mm.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.MatchPhoto || mm.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.MatchVideo))
{
int matchId = m.MultimediaTags.SingleOrDefault(mt => mt.Match_ID != null).Match_ID.Value;
if (m.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.MatchPhoto)
{
p.Matches.SingleOrDefault(match => match.Match_Id == matchId).PhotoCount++;
}
else
{
p.Matches.SingleOrDefault(match => match.Match_Id == matchId).VideoCount++;
}
}
foreach (MatchDTO match in p.Matches)
{
MatchLineupDTO lineup = match.Lineup[0];
lineup.didntPlay = false;
lineup.cameAsSubstitute = false;
lineup.wasSubstituted = false;
int startMinute = -1;
if (lineup.IsSubstitute)
{
MatchEventDTO playerInEvent = match.Events.FirstOrDefault(me => (me.Event_Cd == Constants.DB.EventTypeCodes.Substitution) && (me.Player2_Id == playerId));
if (playerInEvent != null)
{
startMinute = playerInEvent.Minute == 46 ? 45 : playerInEvent.Minute;
lineup.cameAsSubstitute = true;
}
else
{
lineup.didntPlay = true;
}
}
else
{
startMinute = 0;
}
int finishMinute = 90;
if (match.Flags.HasValue && (match.Flags & Constants.DB.MatchFlags.Duration120Minutes) > 0)
{
finishMinute = 120;
}
if (match.Flags.HasValue && (match.Flags & Constants.DB.MatchFlags.GoldenGoalRule) > 0)
{
finishMinute = 120;
MatchEvent lastGoalEvent = db.MatchEvents.Where(me => me.Match_Id == match.Match_Id && (me.Event_Cd == Constants.DB.EventTypeCodes.Goal || me.Event_Cd == Constants.DB.EventTypeCodes.Penalty)).OrderByDescending(me => me.Minute).First();
finishMinute = lastGoalEvent.Minute;
}
MatchEventDTO playerOutEvent = match.Events.FirstOrDefault(me => (me.Event_Cd == Constants.DB.EventTypeCodes.Substitution) && (me.Player1_Id == playerId));
if (playerOutEvent != null)
{
finishMinute = playerOutEvent.Minute == 46 ? 45 : playerOutEvent.Minute;
lineup.wasSubstituted = true;
}
MatchEventDTO redCardEvent = match.Events.FirstOrDefault(me => (me.Event_Cd == Constants.DB.EventTypeCodes.RedCard || me.Event_Cd == Constants.DB.EventTypeCodes.SecondYellowCard));
if (redCardEvent != null)
{
finishMinute = redCardEvent.Minute;
}
lineup.minutesPlayed = finishMinute - startMinute;
if (lineup.minutesPlayed == 0)
{
lineup.minutesPlayed++;
}
}
List <MatchDTO> orderedMatches = p.Matches.OrderBy(m => m.Date).ToList();
int matchNo = 0;
for (var i = 0; i < orderedMatches.Count; i++)
{
if ((orderedMatches[i].Lineup[0].Flags & Constants.DB.LineupFlags.Debut) > 0)
{
matchNo = 1;
orderedMatches[i].Lineup[0].MatchNo = 1;
}
else
{
if (matchNo > 0 && !orderedMatches[i].Lineup[0].didntPlay && orderedMatches[i].CompetitionLevelCode == Constants.DB.CompetitionLevelCd_NationalTeam)
{
matchNo++;
orderedMatches[i].Lineup[0].MatchNo = matchNo;
//p.Matches.Find(m => m.Match_Id == orderedMatches[i].Match_Id).Lineup[0].MatchNo = 3;
}
}
}
return(p);
}
}
public MatchDTO GetFromDB(int objectId)
{
using (UaFootball_DBDataContext db = DBManager.GetDB())
{
var dbData = (from match in db.Matches
where match.Match_Id == objectId
select new { m = match, r = match.Referee, s = match.Stadium, cn = match.Stadium.City.City_Name, compName = match.Competition.Competition_Name, compStageName = match.CompetitionStage.CompetitionStage_Name }).Single();
MatchDTO ret = ConvertDBObjectToDTO(dbData.m);
if (dbData.r != null)
{
ret.Referee = new RefereeDTOHelper().ConvertDBObjectToDTO(dbData.r);
}
ret.Stadium = new StadiumDTOHelper().ConvertDBObjectToDTO(dbData.s);
ret.Stadium.City_Name = dbData.cn;
ret.CompetitionName = dbData.compName;
ret.CompetitionStageName = dbData.compStageName;
if (ret.HomeNationalTeam_Id.HasValue)
{
ret.HomeTeamName = db.NationalTeams.Single(nt => nt.NationalTeam_Id == ret.HomeNationalTeam_Id).Country.Country_Name;
ret.AwayTeamName = db.NationalTeams.Single(nt => nt.NationalTeam_Id == ret.AwayNationalTeam_Id).Country.Country_Name;
MultimediaTag homeTeamLogoTag = db.MultimediaTags.Where(t => t.NationalTeam_ID == ret.HomeNationalTeam_Id && t.Multimedia.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.NationalTeamLogo).OrderByDescending(m => m.Multimedia_ID).FirstOrDefault();
if (homeTeamLogoTag != null)
{
ret.HomeTeamLogo = MultimediaDTO.FromDBObject(homeTeamLogoTag.Multimedia);
}
MultimediaTag awayTeamLogoTag = db.MultimediaTags.Where(t => t.NationalTeam_ID == ret.AwayNationalTeam_Id && t.Multimedia.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.NationalTeamLogo).OrderByDescending(m => m.Multimedia_ID).FirstOrDefault();
if (awayTeamLogoTag != null)
{
ret.AwayTeamLogo = MultimediaDTO.FromDBObject(awayTeamLogoTag.Multimedia);
}
}
else
{
ret.HomeTeamName = db.Clubs.Single(c => c.Club_ID == ret.HomeClub_Id).Club_Name;
ret.AwayTeamName = db.Clubs.Single(c => c.Club_ID == ret.AwayClub_Id).Club_Name;
ret.HomeTeamLogo = db.MultimediaTags.Where(t => t.Club_ID == ret.HomeClub_Id && t.Multimedia.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.ClubLogo).OrderByDescending(m => m.Multimedia_ID).Select(m => MultimediaDTO.FromDBObject(m.Multimedia)).FirstOrDefault();
ret.AwayTeamLogo = db.MultimediaTags.Where(t => t.Club_ID == ret.AwayClub_Id && t.Multimedia.MultimediaSubType_CD == Constants.DB.MutlimediaSubTypes.ClubLogo).OrderByDescending(m => m.Multimedia_ID).Select(m => MultimediaDTO.FromDBObject(m.Multimedia)).FirstOrDefault();
}
IQueryable <MatchLineupDTO> lineup = from matchLineup in db.MatchLineups
where matchLineup.Match_Id == objectId //&& matchLineup.Player_Id>0
orderby matchLineup.IsSubstitute, matchLineup.MatchLineup_Id
select new MatchLineupDTO
{
IsHomeTeamPlayer = matchLineup.IsHomeTeamPlayer,
IsSubstitute = matchLineup.IsSubstitute,
Match_Id = matchLineup.Match_Id,
Player_Id = matchLineup.Player_Id,
MatchLineup_Id = matchLineup.MatchLineup_Id,
Player_FirstName = matchLineup.Player.First_Name,
Player_LastName = matchLineup.Player.Last_Name,
Player_DisplayName = matchLineup.Player.Display_Name,
Player_FirstName_Int = matchLineup.Player.First_Name_Int,
Player_LastName_Int = matchLineup.Player.Last_Name_Int,
Player_CountryId = matchLineup.Coach_Id.HasValue ? 0 : matchLineup.Player.Country_Id,
ShirtNum = matchLineup.ShirtNumber,
CoachId = matchLineup.Coach_Id,
Coach_FirstName = matchLineup.Coach.FirstName,
Coach_LastName = matchLineup.Coach.LastName,
Flags = matchLineup.Flags ?? 0
};
ret.Lineup.AddRange(lineup.AsEnumerable());
IQueryable <MatchEventDTO> events = from matchEvent in db.MatchEvents
where matchEvent.Match_Id == objectId
select new MatchEventDTO
{
Event_Cd = matchEvent.Event_Cd,
EventFlags = matchEvent.EventFlags,
Match_Id = objectId,
MatchEvent_Id = matchEvent.MatchEvent_Id,
Minute = matchEvent.Minute,
Player1 = new PlayerDTO
{
First_Name = matchEvent.Player.First_Name,
Last_Name = matchEvent.Player.Last_Name,
Display_Name = matchEvent.Player.Display_Name,
Country_Id = matchEvent.Player.Player_Id > 0 ? matchEvent.Player.Country_Id : 0
},
Player1_Id = matchEvent.Player1_Id,
//Player1_FName = matchEvent.Player.First_Name,
//Player1_SName = matchEvent.Player.Last_Name,
//Player1_DName = matchEvent.Player.Display_Name,
//Player2_FName = matchEvent.Player1.First_Name,
//Player2_SName = matchEvent.Player1.Last_Name,
//Player2_DName = matchEvent.Player1.Display_Name,
Player2_Id = matchEvent.Player2_Id,
Player2 = new PlayerDTO
{
First_Name = matchEvent.Player1.First_Name,
Last_Name = matchEvent.Player1.Last_Name,
Display_Name = matchEvent.Player1.Display_Name,
Country_Id = matchEvent.Player1.Player_Id > 0 ? matchEvent.Player1.Country_Id : 0
}
};
ret.Events.AddRange(events.AsEnumerable());
IQueryable <MultimediaDTO> multimedia = (from m in db.Multimedias
join mt in db.MultimediaTags on m.Multimedia_ID equals mt.Multimedia_ID
where mt.Match_ID == objectId
select m).Distinct().Select(m => new MultimediaDTO
{
Multimedia_ID = m.Multimedia_ID, MultimediaType_CD = m.MultimediaType_CD
});
ret.Multimedia = new List <MultimediaDTO>();
ret.Multimedia.AddRange(multimedia);
IQueryable <MatchNoteDTO> notes = from note in db.MatchNotes
where note.Match_Id == objectId
select new MatchNoteDTO {
Code = note.Code.Trim(), Text = note.Text, MatchNote_Id = note.MatchNote_Id
};
ret.Notes.AddRange(notes);
for (int i = 0; i < ret.Notes.Count; i++)
{
MatchNoteDTO note = ret.Notes[i];
note.CodeDescription = Constants.MatchNoteSetups.Single(s => s.Code == note.Code).Description;
note.RowIndex = i;
}
return(ret);
}
}
