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); } }