/// <summary> /// Get all post that realted to a brief news /// </summary> public static IEnumerable<PostedInfoViewModel> GetRecentPosts(int type = 0, int? sportID = null, int? teamID = null, int? classificationType = null) { ComEntities com = new ComEntities(); NpbEntities npb = new NpbEntities(); JlgEntities jlg = new JlgEntities(); MlbEntities mlb = new MlbEntities(); short currentYear = Convert.ToInt16(DateTime.Now.Year); var topicList = GetTopicList(type, sportID, teamID, classificationType); var memberList = (from quot in com.QuotTopic join cont in com.Contribution on quot.ContributeID equals cont.ContributeId join contOrg in com.ContributionQuotOrg on cont.ContributeId equals contOrg.ContributeId join brief in com.BriefNews on new { c1 = contOrg.QuoteUniqueId1, c2 = contOrg.Status, c3 = contOrg.NewsClassId } equals new { c1 = brief.NewsItemID, c2 = (short)1, c3 = (long)1 } into brief_news from br in brief_news.DefaultIfEmpty() //join mr in com.MonthlyResults on new { g1 = contOrg.QuoteUniqueId2, g2 = contOrg.Status, g3 = contOrg.NewsClassId, g4 = contOrg.QuoteUniqueId1, g5 = currentYear, g6 = sportID.Value } // equals new { g1 = (long)mr.ReleVantMonth, g2 = (short)1, g3 = (long)6, g4 = (long)mr.MemberID, g5 = mr.ReleVantYear, g6 = mr.SportsID } into monthlyResult //from mlr in monthlyResult.DefaultIfEmpty() select new { TopicID = quot.TopicID, ContributeId = quot.ContributeID, ContributeDate = cont.ContributeDate, ModifiedDate = cont.ModifiedDate, Title = cont.Title, ContributedPicture = cont.ContributedPicture, Body = cont.Body, MemberId = cont.MemberId, NewsClassId = contOrg.NewsClassId, QuoteUniqueId1 = contOrg.QuoteUniqueId1, QuoteUniqueId2 = contOrg.QuoteUniqueId2, QuoteUniqueId3 = contOrg.QuoteUniqueId3, Status = contOrg.Status, //ReleVantYear = (mlr != null ? mlr.ReleVantYear : default(Nullable<short>)), //ReleVantMonth = (mlr != null ? mlr.ReleVantMonth : default(Nullable<short>)), //ExpectNumber = (mlr != null ? mlr.ExpectNumber : default(Nullable<int>)), //CorrectPercent = (mlr != null ? mlr.CorrectPercent : default(Nullable<short>)), //CorrectPoint = (mlr != null ? mlr.CorrectPoint : default(Nullable<int>)), HeadLine = (br != null ? br.Headline : null), SentFrom = (br != null ? br.SentFrom : null) } into member_list select member_list).ToList(); //Member var followMemberList = default(List<Member>); switch(type) { case 9: followMemberList = GetFollorMemberList(2); break; case 5: case 6: case 7: followMemberList = GetFollorMemberList(); break; case 10: //sportID save memberId in this case followMemberList = GetFollorMemberList(3, sportID ?? 0); break; default: followMemberList = new List<Member>(com.Member); break; } // total views of post var contributedReadingSum = (from r in com.ContributionReading select r).GroupBy(x => x.ContributeId).Select( l => new { ContributeID = l.Key, ReadingSum = l.Count() }).ToList(); // Npb info var npbTeamList = new List<TeamInfoMST>(npb.TeamInfoMST); var npbPlayerList = new List<PlayerInfoMST>(npb.PlayerInfoMST); var npbGameSSList = new List<GameInfoSS>(npb.GameInfoSS); // Jlg Info var jlgLeagueInfoList = new List<LeagueInfo>(jlg.LeagueInfo); var jlgTeamInfoTEList = new List<TeamInfoTE>(jlg.TeamInfoTE); var jlgPlayerInfoDIList = new List<PlayerInfoDI>(jlg.PlayerInfoDI); var jlgScheduleInfoList = new List<ScheduleInfo>(jlg.ScheduleInfo); // Mlb Info var mlbTeamInfoList = new List<TeamInfo>(mlb.TeamInfo); var mlbScheduleInfoList = (from ss in mlb.SeasonSchedule join dg in mlb.DayGroup on ss.DayGroupId equals dg.DayGroupId select new { GameID = ss.GameID, GameDate = dg.GameDate, HomeTeamFullName = ss.HomeTeamFullName, VisitorTeamFullName = ss.VisitorTeamFullName } into gameSS select gameSS).Distinct().ToList(); var result = default(IEnumerable<PostedInfoViewModel>); if (topicList == null || memberList == null) return result; var monthlyResultSum = (from m in com.MonthlyResults group m by new { m.MemberID, m.ReleVantMonth, m.ReleVantYear } into monthGroup select new { MemberID = monthGroup.Key.MemberID, ReleVantMonth = monthGroup.Key.ReleVantMonth, ReleVantYear = monthGroup.Key.ReleVantYear, ExpectNumber = monthGroup.Sum(x => x.ExpectNumber), CorrectPercent = monthGroup.Sum(x => (x.CorrectPercent * x.ExpectNumber)), CorrectPoint = monthGroup.Sum(x => x.CorrectPoint) }).ToList(); var query = from topic in topicList join mb in memberList on topic.TopicID equals mb.TopicID join m in followMemberList on mb.MemberId equals m.MemberId join c in contributedReadingSum on mb.ContributeId equals c.ContributeID into q_readings from views in q_readings.DefaultIfEmpty() join team in npbTeamList on new { c1 = (mb.NewsClassId == 2 ? mb.QuoteUniqueId3 : mb.QuoteUniqueId2), c2 = mb.Status, c3 = (mb.NewsClassId == 2 || mb.NewsClassId == 3) ? 1 : 2, c4 = mb.QuoteUniqueId1 } equals new { c1 = (long)team.TeamCD, c2 = (short)1, c3 = 1, c4 = (long)1 } into npb_team from npbTeam in npb_team.DefaultIfEmpty() join player in npbPlayerList on new { p1 = mb.QuoteUniqueId3, p2 = mb.Status, p3 = mb.NewsClassId, p4 = mb.QuoteUniqueId1 } equals new { p1 = (long)player.PlayerCD, p2 = (short)1, p3 = (long)3, p4 = (long)1 } into npb_player from npbPlayer in npb_player.DefaultIfEmpty() join game in npbGameSSList on new { g1 = mb.QuoteUniqueId2, g2 = mb.Status, g3 = (mb.NewsClassId == 4 || mb.NewsClassId == 5) ? 1 : 2, g4 = mb.QuoteUniqueId1 } equals new { g1 = (long)game.ID, g2 = (short)1, g3 = 1, g4 = (long)1 } into npb_game from npbGame in npb_game.DefaultIfEmpty() join league in jlgLeagueInfoList on new { l1 = mb.QuoteUniqueId2, l2 = mb.Status, l3 = mb.NewsClassId, l4 = mb.QuoteUniqueId1 } equals new { l1 = (long)league.LeagueID, l2 = (short)1, l3 = (long)2, l4 = (long)2 } into jlg_league from jlgLeague in jlg_league.DefaultIfEmpty() join jlgT in jlgTeamInfoTEList on new { t1 = (mb.NewsClassId == 2 ? mb.QuoteUniqueId3 : mb.QuoteUniqueId2), t2 = mb.Status, t3 = (mb.NewsClassId == 2 || mb.NewsClassId == 3) ? 1 : 2, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)jlgT.TeamID, t2 = (short)1, t3 = 1, t4 = (long)2 } into jlg_team from jlgTeam in jlg_team.DefaultIfEmpty() join jlgP in jlgPlayerInfoDIList on new { t1 = mb.QuoteUniqueId3, t2 = mb.Status, t3 = mb.NewsClassId, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)jlgP.PlayerID, t2 = (short)1, t3 = (long)3, t4 = (long)2 } into jlg_player from jlgPlayer in jlg_player.DefaultIfEmpty() join jlgSc in jlgScheduleInfoList on new { t1 = mb.QuoteUniqueId2, t2 = mb.Status, t3 = (mb.NewsClassId == 4 || mb.NewsClassId == 5) ? 1 : 2, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)jlgSc.GameID, t2 = (short)1, t3 = 1, t4 = (long)2 } into jlg_schedule from jlgSchedule in jlg_schedule.DefaultIfEmpty() join mlbT in mlbTeamInfoList on new { t1 = (mb.NewsClassId == 2 ? mb.QuoteUniqueId3 : mb.QuoteUniqueId2), t2 = mb.Status, t3 = (mb.NewsClassId == 2 || mb.NewsClassId == 3) ? 1 : 2, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)mlbT.TeamID, t2 = (short)1, t3 = 1, t4 = (long)3 } into mlb_team from mlbTeam in mlb_team.DefaultIfEmpty() join mlbSS in mlbScheduleInfoList on new { t1 = mb.QuoteUniqueId2, t2 = mb.Status, t3 = (mb.NewsClassId == 4 || mb.NewsClassId == 5) ? 1 : 2, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)mlbSS.GameID, t2 = (short)1, t3 = 1, t4 = (long)3 } into mlb_schedule from mlbSchedule in mlb_schedule.DefaultIfEmpty() join mr in monthlyResultSum on new { g1 = (mb.QuoteUniqueId2 % 100), g2 = mb.Status, g3 = mb.NewsClassId, g4 = mb.QuoteUniqueId1, g5 = (mb.QuoteUniqueId2 / 100) } equals new { g1 = (long)mr.ReleVantMonth, g2 = (short)1, g3 = (long)6, g4 = (long)mr.MemberID, g5 = (long)mr.ReleVantYear } into monthlyResult from mlr in monthlyResult.DefaultIfEmpty() select new PostedInfoViewModel { SportID = sportID ?? 0, TopicID = (int)topic.TopicID, ContributeId = mb.ContributeId, ContributeDate = mb.ContributeDate, ModifiedDate = mb.ModifiedDate, Title = mb.Title, ContributedPicture = mb.ContributedPicture, Body = mb.Body, MemberId = m.MemberId, Nickname = m.Nickname, ProfileImg = m.ProfileImg, NewsClassId = mb.NewsClassId, QuoteUniqueId1 = mb.QuoteUniqueId1, QuoteUniqueId2 = mb.QuoteUniqueId2, QuoteUniqueId3 = mb.QuoteUniqueId3, Status = mb.Status, ReleVantYear = (mlr != null ? mlr.ReleVantYear : default(Nullable<Int16>)), ReleVantMonth = (mlr != null ? mlr.ReleVantMonth : default(Nullable<Int16>)), ExpectNumber = (mlr != null ? mlr.ExpectNumber : default(Nullable<Int32>)), CorrectPercent = (mlr != null ? (short)mlr.CorrectPercent : default(Nullable<Int16>)), CorrectPoint = (mlr != null ? mlr.CorrectPoint : default(Nullable<Int32>)), HeadLine = mb.HeadLine, SentFrom = mb.SentFrom, NpbShortNameLeague = (npbTeam != null ? npbTeam.ShortNameLeague : null), NpbTeam = (npbTeam != null ? npbTeam.Team : null), NpbPlayer = (npbPlayer != null ? npbPlayer.Player : null), NpbHomeTeamName = (npbGame != null ? npbGame.HomeTeamName : null), NpbGameDate = (npbGame != null ? npbGame.GameDate : default(Nullable<int>)), NpbVisitorTeamName = (npbGame != null ? npbGame.VisitorTeamName : null), JlgLeagueNameS = (jlgLeague != null ? jlgLeague.LeagueNameS : null), JlgTeamName = (jlgTeam != null ? jlgTeam.TeamName : null), JlgPlayerName = (jlgPlayer != null ? jlgPlayer.PlayerName : null), JlgHomeTeamName = (jlgSchedule != null ? jlgSchedule.HomeTeamName : null), JlgGameDate = (jlgSchedule != null ? jlgSchedule.GameDate : default(Nullable<int>)), JlgAwayTeamName = (jlgSchedule != null ? jlgSchedule.AwayTeamName : null), MlbLeagueName = (mlbTeam != null ? mlbTeam.LeagueName : null), MlbTeamName = (mlbTeam != null ? mlbTeam.TeamFullName : null), MlbHomeTeamName = (mlbSchedule != null ? mlbSchedule.HomeTeamFullName : null), MlbGameDate = (mlbSchedule != null ? mlbSchedule.GameDate : default(Nullable<int>)), MlbAwayTeamName = (mlbSchedule != null ? mlbSchedule.VisitorTeamFullName : null), Views = ((views != null) ? views.ReadingSum : 0), PostMonth = mb.ContributeDate.Value.Month, PostYear = mb.ContributeDate.Value.Year } into posted_info select posted_info; if (query != null) result = query.GroupBy(c => c.ContributeId).Select(p => p.OrderBy(t => t.TopicID).FirstOrDefault()); return result.OrderByDescending(p => p.ContributeDate); }
/// <summary> /// </summary> /// <param name="sportID">sport id.</param> /// <param name="gameID">game id.</param> /// <returns>Name of a team.</returns> public static string GetGameNameById(int sportID, int gameID) { string result = "試合"; if (sportID == Constants.NPB_SPORT_ID) { NpbEntities npb = new NpbEntities(); var gameNames = (from g in npb.GameInfoSS where g.ID == gameID select g).FirstOrDefault(); if (gameNames != null) { DateTime gd = DateTime.ParseExact(string.Format("{0}", gameNames.GameDate), "yyyyMMdd", null); result = string.Format("{0} {1} vs {2}", gd.ToString("yyyy/MM/dd"), gameNames.HomeTeamNameS, gameNames.VisitorTeamNameS); } } if (sportID == Constants.JLG_SPORT_ID) { JlgEntities jlg = new JlgEntities(); var gameNames = (from g in jlg.ScheduleInfo where g.GameID == gameID select g).FirstOrDefault(); if (gameNames != null) { DateTime gd = DateTime.ParseExact(string.Format("{0}", gameNames.GameDate), "yyyyMMdd", null); result = string.Format("{0} {1} vs {2}", gd.ToString("yyyy/MM/dd"), gameNames.HomeTeamNameS, gameNames.AwayTeamNameS); } } if (sportID == Constants.MLB_SPORT_ID) { MlbEntities mlb = new MlbEntities(); var gameNames = (from g in mlb.SeasonSchedule join d in mlb.DayGroup on g.DayGroupId equals d.DayGroupId where g.GameID == gameID select new { GameName = g, GameDate = d }).FirstOrDefault(); if (gameNames != null) { DateTime gd = DateTime.ParseExact(string.Format("{0}", gameNames.GameDate.GameDateJPN), "yyyyMMdd", null); result = string.Format("{0} {1} vs {2}", gd.ToString("MM/dd"), gameNames.GameName.HomeTeamName, gameNames.GameName.VisitorTeamName); } } return result; }
/// <summary> /// サイトTopとXXXTopコントローラ向け試合情報の取得 /// </summary> /// <param name="memberId"></param> /// <param name="target_year"></param> /// <param name="target_month"></param> /// <param name="target_date"></param> /// <returns></returns> public static IEnumerable<GameInfoModel> GetGameInfoForTop(Int64 memberId, int target_year, int target_month, int target_date = 0, int sportsId = 0) { #region 情報取得する日付の開始と終了を取得し、投稿記事のリンク表示フラグを設定する DateTime startDate; DateTime endDate; switch (target_date) { // 日付指定がない場合 case 0: // 当月最初の日付を取得 startDate = Convert.ToDateTime(target_year + "/" + target_month + "/01 00:00:00"); // 当月の最後の日付を取得 endDate = Convert.ToDateTime(target_year + "/" + target_month + "/01 23:59:59").AddMonths(1).AddDays(-1); break; // 日付指定の場合 default: // 指定日の前日を取得 startDate = Convert.ToDateTime(target_year + "/" + target_month + "/" + target_date + " 00:00:00"); // 指定日を取得 endDate = Convert.ToDateTime(target_year + "/" + target_month + "/" + target_date + " 23:59:59"); break; } #endregion var oddsService = new OddsService(); #region NPBゲームの取得 List<GameInfoModel> nbpGames = null; if (sportsId == 0 || sportsId == Constants.NPB_SPORT_ID) { //予想情報の取得 var npbExpectTargets = GetPointOfUser(memberId, Constants.NPB_SPORT_ID, startDate, endDate); nbpGames = (from e in npbExpectTargets select new GameInfoModel { TargetYear = target_year, TargetMonth = target_month, StartScheduleDate = e.StartScheduleDate, SportsID = e.SportsID, ExpectTargetID = e.ExpectTargetID, GameID = e.GameID, UrlArticle = "/mypage/article/", WinLose = Convert.ToInt16(e.VorD), //勝敗1:勝ち 2:負け, BetSelectID = e.BetSelectID, SituationStatus = e.SituationStatus, ExpectPoint1 = e.ExpectPoint, StartDt = startDate, OddsInfoModel = oddsService.GetOddsInfoByGameID(e.SportsID, e.GameID, memberId), MyPointInfoModel = ConvertToGamePointInfoModel(e) }).ToList(); if (nbpGames != null) { foreach (var g in nbpGames) { // 試合の勝利チーム var winteam = (from gi in npb.GameInfoRGI join ri in npb.GameResultInfoRGI on gi.RealGameInfoRootRGIId equals ri.RealGameInfoRootRGIId where gi.GameID == g.GameID // 試合 select ri).FirstOrDefault(); if (winteam != null) { g.WinTeamCd = winteam.WinTeamCD; } } } } #endregion #region MLBゲームの取得 List<GameInfoModel> mlbGames = null; if (sportsId == 0 || sportsId == Constants.MLB_SPORT_ID) { var mlbExpectTargets = GetPointOfUser(memberId, Constants.MLB_SPORT_ID, startDate, endDate); mlbGames = (from e in mlbExpectTargets select new GameInfoModel { TargetYear = target_year, TargetMonth = target_month, StartScheduleDate = e.StartScheduleDate, SportsID = e.SportsID, ExpectTargetID = e.ExpectTargetID, GameID = e.GameID, UrlArticle = "/mypage/article/", WinLose = Convert.ToInt16(e.VorD), //勝敗1:勝ち 2:負け, BetSelectID = e.BetSelectID, SituationStatus = e.SituationStatus, ExpectPoint1 = e.ExpectPoint, StartDt = startDate, OddsInfoModel = oddsService.GetOddsInfoByGameID(e.SportsID, e.GameID, memberId), MyPointInfoModel = ConvertToGamePointInfoModel(e) }).ToList(); if (mlbGames != null) { var mlbEntities = new MlbEntities(); foreach (var g in mlbGames) { // チームID,試合日を取得 var ss = (mlbEntities.SeasonSchedule.FirstOrDefault(s => s.GameID == g.GameID)); if (ss != null) { if (ss.HomeTeamID != null) g.HomeTeamID = (int)ss.HomeTeamID; if (ss.VisitorTeamID != null) g.VisitorTeamID = (int)ss.VisitorTeamID; var gg = mlbEntities.DayGroup.FirstOrDefault(x => x.DayGroupId == ss.DayGroupId); if (gg != null) if (gg.GameDateJPN != null) g.GameDateScheduled = (int) gg.GameDateJPN; } // 試合の勝利チームを得る // MlbTeamInfoDailyResultController.csから引用 var query = from realGameInfo in mlb.RealGameInfo join seasonSchedule in mlb.SeasonSchedule on realGameInfo.GameID equals seasonSchedule.GameID join dayGroup in mlb.DayGroup on seasonSchedule.DayGroupId equals dayGroup.DayGroupId where (dayGroup.GameDateJPN.Value == g.GameDateScheduled) && (realGameInfo.GameID == g.GameID && realGameInfo.HomeTeamID == g.HomeTeamID) select new { WinTeamCD = realGameInfo.HomeScore > realGameInfo.VisitorScore ? realGameInfo.HomeTeamID : (realGameInfo.HomeScore < realGameInfo.VisitorScore ? realGameInfo.VisitorTeamID : null), WinTeamName = realGameInfo.HomeScore > realGameInfo.VisitorScore ? realGameInfo.HomeTeamName : (realGameInfo.HomeScore < realGameInfo.VisitorScore ? realGameInfo.VisitorTeamName : null) }; if (query == null) continue; var winteam = query.FirstOrDefault(); if (winteam != null) { if (winteam.WinTeamCD == g.HomeTeamID) { g.WinnerTeam = 1; } else if (winteam.WinTeamCD == g.VisitorTeamID) { g.WinnerTeam = 2; } } } } } #endregion #region JLGゲームの取得 List<GameInfoModel> jlgGames = null; if (sportsId == 0 || sportsId == Constants.JLG_SPORT_ID) { var jlgExpectTargets = GetPointOfUser(memberId, Constants.JLG_SPORT_ID, startDate, endDate); jlgGames = (from e in jlgExpectTargets select new GameInfoModel { TargetYear = target_year, TargetMonth = target_month, StartScheduleDate = e.StartScheduleDate, SportsID = e.SportsID, ExpectTargetID = e.ExpectTargetID, GameID = e.GameID, UrlArticle = "/mypage/article/", WinLose = Convert.ToInt16(e.VorD), //勝敗1:勝ち 2:負け, BetSelectID = e.BetSelectID, SituationStatus = e.SituationStatus, ExpectPoint1 = e.ExpectPoint, StartDt = startDate, OddsInfoModel = oddsService.GetOddsInfoByGameID(e.SportsID, e.GameID, memberId), MyPointInfoModel = ConvertToGamePointInfoModel(e) }).ToList(); if (jlgGames != null) { var jlgEntities = new JlgEntities(); foreach (var g in jlgGames) { // チームIDを取得 var si = jlgEntities.ScheduleInfo.FirstOrDefault(x => x.GameID == g.GameID); if (si != null) { if (si.HomeTeamID != null) g.HomeTeamID = (int) si.HomeTeamID; if (si.AwayTeamID != null) g.VisitorTeamID = (int) si.AwayTeamID; } //実際の試合日時 var lstScoreHome = JlgCommon.GetTeamInfoGTSByGameIDTeamID(g.GameID, g.HomeTeamID); var lstScoreVisitor = JlgCommon.GetTeamInfoGTSByGameIDTeamID(g.GameID, g.VisitorTeamID); // 試合の勝利チームを得る int winTeamCd = lstScoreHome.R > lstScoreVisitor.R ? g.HomeTeamID : lstScoreHome.R < lstScoreVisitor.R ? g.VisitorTeamID : 0; if (winTeamCd == g.HomeTeamID) // Home勝ち点3 { // ホーム勝ちののBetSelectID g.WinnerTeam = (int)BetConst.BetSelectID.Home; } else if (winTeamCd == g.VisitorTeamID) { // アウェー勝ちののBetSelectID g.WinnerTeam = (int)BetConst.BetSelectID.Visitor; } else { // 引き分けのBetSelectID g.WinnerTeam = (int)BetConst.BetSelectID.Draw; } } } } #endregion var allGames = new List<GameInfoModel>(); if (nbpGames != null) allGames.AddRange(nbpGames); if (mlbGames != null) allGames.AddRange(mlbGames); if (jlgGames != null) allGames.AddRange(jlgGames); return allGames; }
/// <summary> /// Title, description, keywords for JLeague /// </summary> /// <param name="url">url</param> /// <returns>string array</returns> public static string[] GetJLeagueTDK(string url, string pageNo = "") { string mainUrl = url.Split('?').FirstOrDefault(); string[] subUrls = mainUrl.Split('/'); List<string> subUrlList = new List<string>(); for (int i = 0; i < subUrls.Count(); i++) { if (!string.IsNullOrEmpty(subUrls[i])) { subUrlList.Add(subUrls[i]); } } PageInfo page = null; ComEntities tkd = new ComEntities(); page = tkd.PageInfo.Find(pageNo); string title = string.Empty; string description = string.Empty; string keyWords = string.Empty; int userId, groupId; if (page != null) { title = page.Title; description = page.Description; keyWords = page.Keywords; } int teamCD, playerCD, gameID; switch (pageNo) { case "10-2-5-1": case "10-2-5-2": case "10-2-5-3": case "10-2-5-4": case "10-2-5-5": case "10-3-5-1": case "10-3-5-2": case "10-3-5-3": case "10-3-5-4": case "10-3-5-5": if (!string.IsNullOrEmpty(subUrlList.ElementAt(3)) && int.TryParse(subUrlList.ElementAt(3), out teamCD)) { string teamName = GetTeamNameById(Constants.JLG_SPORT_ID, teamCD); title = page.Title.Replace("(チーム名)", teamName); description = page.Description.Replace("(チーム名)", teamName); keyWords = page.Keywords.Replace("(チーム名)", teamName); } break; case "10-2-5-6": case "10-3-5-6": int playerIdx = 0; for (int i = 0; i < subUrlList.Count(); i++) { if (subUrlList.ElementAt(i).ToLower().Equals("players")) playerIdx = i; } if (subUrlList.Count() > playerIdx + 1) { if (!string.IsNullOrEmpty(subUrlList.ElementAt(playerIdx + 1)) && int.TryParse(subUrlList.ElementAt(playerIdx + 1), out playerCD)) { JlgEntities jlg = new JlgEntities(); var playerNames = (from p in jlg.PlayerInfoDI where p.PlayerID == playerCD select p).FirstOrDefault(); var teamNames = (from d in jlg.DirectoryDI join p in jlg.PlayerInfoDI on d.DirectoryDIId equals p.DirectoryDIId where p.PlayerID == playerCD select d).FirstOrDefault(); if (playerNames != null && teamNames != null) { // チーム名 string nameReplaced = string.Empty; nameReplaced = teamNames.TeamName; title = page.Title.Replace("(チーム名)", nameReplaced); description = page.Description.Replace("(チーム名)", nameReplaced); keyWords = page.Keywords.Replace("(チーム名)", nameReplaced); // 選手名 nameReplaced = playerNames.PlayerName; title = title.Replace("(選手名)", nameReplaced); description = description.Replace("(選手名)", nameReplaced); keyWords = keyWords.Replace("(選手名)", nameReplaced); } } } break; case "10-5": if (!string.IsNullOrEmpty(subUrls[3]) && int.TryParse(subUrls[3], out gameID)) { JlgEntities jlg = new JlgEntities(); var games = (from g in jlg.ScheduleInfo join gc in jlg.GameCategory on g.GameCategoryId equals gc.GameCategoryId join gs in jlg.GameSchedule on gc.GameScheduleId equals gs.GameScheduleId where (gs.GameKindID == JlgConstants.JLG_GAMEKIND_J1 || gs.GameKindID == JlgConstants.JLG_GAMEKIND_J2 || gs.GameKindID == JlgConstants.JLG_GAMEKIND_NABISCO) && g.GameID == gameID select new { GameKindID = gs.GameKindID, GameInfo = g } into game_info select game_info).FirstOrDefault(); if (games != null) { var occasionReplaced = string.Format("第{0}節", games.GameInfo.OccasionNo); var firstReplaced = string.Empty; switch (games.GameKindID) { case JlgConstants.JLG_GAMEKIND_J1: firstReplaced = "J1" + occasionReplaced; break; case JlgConstants.JLG_GAMEKIND_J2: firstReplaced = "J2" + occasionReplaced; break; case JlgConstants.JLG_GAMEKIND_NABISCO: DateTime gd = DateTime.ParseExact(string.Format("{0}", games.GameInfo.GameDate), "yyyyMMdd", null); firstReplaced = string.Format("ナビスコカップ{0}", gd.ToString("MM/dd")); break; } var secondReplaced = string.Format("{0} vs {1}", games.GameInfo.HomeTeamNameS, games.GameInfo.AwayTeamNameS); title = page.Title.Replace("(J1第○節/J2第○節/ナビスコカップYY/MM)", firstReplaced).Replace("(対戦カード名)", secondReplaced); title = page.Title.Replace("(J1第○節/J2第○節/ナビスコカップMM/DD)", firstReplaced).Replace("(対戦カード名)", secondReplaced); description = page.Description.Replace("(J1第○節/J2第○節/ナビスコカップYY/MM)", firstReplaced).Replace("(対戦カード名)", secondReplaced); description = page.Description.Replace("(J1第○節/J2第○節/ナビスコカップMM/DD)", firstReplaced).Replace("(対戦カード名)", secondReplaced); keyWords = page.Keywords.Replace("(第○節)", occasionReplaced).Replace("(チーム名1)", games.GameInfo.HomeTeamName).Replace("(チーム名2)", games.GameInfo.AwayTeamName); } } break; default: title = page.Title; description = page.Description; keyWords = page.Keywords; break; } string robots = string.Empty; if (page.CanAutomatic != null) { if (page.CanAutomatic == 1) { robots = "none"; } } string[] result = new string[4]; result[0] = title; result[1] = description; result[2] = keyWords; result[3] = robots; return result; }
/// <summary> /// </summary> /// <param name="sportID">sport id.</param> /// <param name="teamID">team id.</param> /// <returns>Name of a team.</returns> public static string GetTeamNameById(int sportID, int teamID) { string result = "[チーム名]"; if (sportID == Constants.NPB_SPORT_ID) { NpbEntities npb = new NpbEntities(); var teamNames = (from t in npb.TeamInfoMST where t.TeamCD == teamID select t).FirstOrDefault(); if (teamNames != null) return teamNames.Team; } if (sportID == Constants.JLG_SPORT_ID) { JlgEntities jlg = new JlgEntities(); var teamNames = (from t in jlg.TeamInfoTE where t.TeamID == teamID select t).FirstOrDefault(); if (teamNames != null) return teamNames.TeamName; } if (sportID == Constants.MLB_SPORT_ID) { MlbEntities mlb = new MlbEntities(); var teamNames = (from t in mlb.TeamInfo where t.TeamID == teamID select t).FirstOrDefault(); if (teamNames != null) return teamNames.TeamName; } return result; }
/// <summary> /// Get score in table GameTextScore By GameID and TeamID. /// </summary> /// <param name="gameID">GameID</param> /// <param name="teamID">TeamID</param> /// <returns>Score if exists or 0 if not exist.</returns> public static ScoreGameInfo GetTeamInfoGTSByGameIDTeamID(int gameID, int teamID) { JlgEntities Jlg = new JlgEntities(); var query = (from grl in Jlg.GameReportLG join til in Jlg.TeamInfoLG on grl.GameReportLGId equals til.GameReportLGId where grl.GameID == gameID && til.ID == teamID select new ScoreGameInfo { GameID = grl.GameID, TeamID = teamID, R = til.Score }).FirstOrDefault(); ScoreGameInfo scoreInfo = new ScoreGameInfo(); if (query == null) { scoreInfo.GameID = gameID; scoreInfo.TeamID = teamID; return scoreInfo; } else { return query; } }
private static IEnumerable<PostedInfoViewModel> getJlg(int? sportID, List<TopicMaster> topicList, List<MemberModel> memberList, List<Member> followMemberList, List<ContributedReadingSum> contributedReadingSum) { // Jlg Info JlgEntities jlg = new JlgEntities(); var jlgLeagueInfoList = new List<LeagueInfo>(jlg.LeagueInfo); var jlgTeamInfoTEList = new List<TeamInfoTE>(jlg.TeamInfoTE); var jlgPlayerInfoDIList = new List<PlayerInfoDI>(jlg.PlayerInfoDI); var jlgScheduleInfoList = new List<ScheduleInfo>(jlg.ScheduleInfo); var query = (from topic in topicList join mb in memberList on topic.TopicID equals mb.TopicID join m in followMemberList on mb.MemberId equals m.MemberId join c in contributedReadingSum on mb.ContributeId equals c.ContributeID into q_readings from views in q_readings.DefaultIfEmpty() join league in jlgLeagueInfoList on new { l1 = mb.QuoteUniqueId2, l2 = mb.Status, l3 = mb.NewsClassId, l4 = mb.QuoteUniqueId1 } equals new { l1 = (long)league.LeagueID, l2 = (int)1, l3 = (long)2, l4 = (long)2 } into jlg_league from jlgLeague in jlg_league.DefaultIfEmpty() join jlgT in jlgTeamInfoTEList on new { t1 = (mb.NewsClassId == 2 ? mb.QuoteUniqueId3 : mb.QuoteUniqueId2), t2 = mb.Status, t3 = (mb.NewsClassId == 2 || mb.NewsClassId == 3) ? 1 : 2, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)jlgT.TeamID, t2 = (int)1, t3 = 1, t4 = (long)2 } into jlg_team from jlgTeam in jlg_team.DefaultIfEmpty() join jlgP in jlgPlayerInfoDIList on new { t1 = mb.QuoteUniqueId3, t2 = mb.Status, t3 = mb.NewsClassId, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)jlgP.PlayerID, t2 = (int)1, t3 = (long)3, t4 = (long)2 } into jlg_player from jlgPlayer in jlg_player.DefaultIfEmpty() join jlgSc in jlgScheduleInfoList on new { t1 = mb.QuoteUniqueId2, t2 = mb.Status, t3 = (mb.NewsClassId == 4 || mb.NewsClassId == 5) ? 1 : 2, t4 = mb.QuoteUniqueId1 } equals new { t1 = (long)jlgSc.GameID, t2 = (int)1, t3 = 1, t4 = (long)2 } into jlg_schedule from jlgSchedule in jlg_schedule.DefaultIfEmpty() select new PostedInfoViewModel { SportID = sportID ?? 0, TopicID = (int)topic.TopicID, ContributeId = mb.ContributeId, ContributeDate = mb.ContributeDate, Title = mb.Title, ContributedPicture = mb.ContributedPicture, Body = mb.Body, MemberId = m.MemberId, Nickname = m.Nickname, ProfileImg = m.ProfileImg, NewsClassId = mb.NewsClassId, QuoteUniqueId1 = mb.QuoteUniqueId1, QuoteUniqueId2 = mb.QuoteUniqueId2, QuoteUniqueId3 = mb.QuoteUniqueId3, Status = (short)mb.Status, ReleVantYear = mb.ReleVantYear, ReleVantMonth = mb.ReleVantMonth, ExpectNumber = mb.ExpectNumber, CorrectPercent = mb.CorrectPercent, CorrectPoint = mb.CorrectPoint, HeadLine = mb.HeadLine, SentFrom = mb.SentFrom, JlgLeagueNameS = (jlgLeague != null ? jlgLeague.LeagueNameS : null), JlgTeamName = (jlgTeam != null ? jlgTeam.TeamName : null), JlgPlayerName = (jlgPlayer != null ? jlgPlayer.PlayerName : null), JlgHomeTeamName = (jlgSchedule != null ? jlgSchedule.HomeTeamName : null), JlgGameDate = (jlgSchedule != null ? jlgSchedule.GameDate : default(Nullable<int>)), JlgAwayTeamName = (jlgSchedule != null ? jlgSchedule.AwayTeamName : null), Views = ((views != null) ? views.ReadingSum : 0), PostMonth = mb.ContributeDate.Value.Month, PostYear = mb.ContributeDate.Value.Year } into posted_info select posted_info).Distinct(new PostedInfoViewModelComparer()).ToList(); return query; }
/// <summary> /// 試合状況を取得 /// </summary> /// <param name="gameId">試合ID</param> /// <param name="strMemberId">会員ID</param> /// <returns> /// 0: 試合情報なし? /// 1: 受付前 /// 2: 5分前以前、ベットなし /// 3: 5分前以前、ベットあり /// 4: 5分前以降、ベットなし /// 5: 5分前以降、ベットあり /// 6: 試合中、ベットなし /// 7: 試合中、ベットあり /// 8: 試合終了、ベットなし /// 9: 試合終了、ベットあり /// 10: 試合中止 /// </returns> public static int GetStatusMatch(int gameId, string strMemberId) { //Todo:判定が煩雑なので、ロジック整理の必要有り var memberId = !string.IsNullOrEmpty(strMemberId) ? Convert.ToInt64(strMemberId) : 0; if (memberId > 0) { // ポイントテーブル存在判定 var systemDatetimeService = new SystemDatetimeService(); var pointInfoService = new PointInfoService(new ComEntities()); var isExist = pointInfoService.IsExistsPoint(memberId, systemDatetimeService.SystemDate); if (!isExist) return 1; } // BET情報を取得 var oddsService = new OddsService(); var oddinfo = oddsService.GetOddsInfoByGameID(Constants.JLG_SPORT_ID, gameId, memberId); if (oddinfo.ExpectTargetID == 0) return 1; var checkBet = oddinfo.BetSelectedID != null; // BET有無 var jlg = new JlgEntities(); var gameInfos = (from si in jlg.ScheduleInfo //年間試合スケジュール_試合情報 where si.GameID == gameId select new JlgGameInfoByCondidtion { ScheduleInfoJlg = si, GameTimetoStr = si.GameTime.ToString() }).First(); int result = 0; if (gameInfos == null) return result; var gameDate = Utils.ConvertStrToDatetime(gameInfos.ScheduleInfoJlg.GameDate.ToString()); var gameStartDateAndTime = DateTime.MaxValue; // "未定"やnullがありうる int time; if (int.TryParse(gameInfos.GameTimetoStr, out time)) { var hours = Convert.ToInt32(time.ToString().Substring(0, 2)); var minute = Convert.ToInt32(time.ToString().Substring(2, 2)); gameStartDateAndTime = new DateTime(gameDate.Year, gameDate.Month, gameDate.Day, hours, minute, 0); } //予想可能日付閾値(今日日付基準の月末日取得) var expectableEnd = DateTime.Now.EndOfTheMonthWithTime(); if (DateTime.Now < gameStartDateAndTime) { //Case 1:Can not bet if (gameStartDateAndTime > expectableEnd) { return 1; // 1:ベット不可 } DateTime limitTime = gameStartDateAndTime.AddMinutes(-5); if (DateTime.Now < limitTime) { return !checkBet ? 2 : 3; // 2:5分前以前、ベットなし 3:5分前以前、ベットあり } return !checkBet ? 4 : 5; // 3:5分前以降、ベットなし 4:5分前以前、ベットあり } // 一試合個人スタッツ_ヘッダー情報 var realGameInfos = from sr in jlg.StatsReportLS where sr.GameID == gameId select sr; if (realGameInfos != null) { if (realGameInfos.FirstOrDefault() != null) { //空白:試合中または試合前 2=正常終了 7=サスペンデッド 9=中止(試合中) string gameSetSituationCd = GetNpbGameStateID((short)realGameInfos.FirstOrDefault().SituationID); if ((!string.IsNullOrEmpty(gameSetSituationCd)) && ((gameSetSituationCd == "8") ||(gameSetSituationCd == "9"))) { return 10; } switch (gameSetSituationCd) { case "0": return !checkBet ? 4 : 5; // 4:試合前、ベットなし 5:試合前、ベットあり case "1": return !checkBet ? 6 : 7; // 8:試合中、ベットなし 7:試合中、ベットあり case "4": return !checkBet ? 8 : 9; // 8:試合終了、ベットなし 9:試合終了、ベットあり } return 10; // 10:試合中止 } return !checkBet ? 4 : 5; // 3:5分前以降、ベットなし 4:5分前以前、ベットあり } return !checkBet ? 4 : 5; // 3:5分前以降、ベットなし 4:5分前以前、ベットあり }
/// <summary> /// 試合後かどうかを判断する /// "3=試合後 /// </summary> /// <param name="dateCheck"></param> /// <param name="gameId"></param> /// <returns></returns> public static int GetStatusMatch(string gameId) { int result = 0; if (!string.IsNullOrEmpty(gameId)) { JlgEntities Jlg = new JlgEntities(); var scheduleInfos = (from sdi in Jlg.ScheduleInfo //全試合速報 where sdi.GameID.ToString().Equals(gameId) select new { GameDate = sdi.GameDate, GameTime = sdi.GameTime }).FirstOrDefault(); //Order by ha? if (scheduleInfos != null) { string dateStr = scheduleInfos.GameDate.ToString(); string timeStr = (scheduleInfos.GameTime == null ? "0000" : scheduleInfos.GameTime.ToString()).PadLeft(4, '0'); DateTime matchDate = DateTime.ParseExact(dateStr + timeStr, "yyyyMMddHHmm", null); if (DateTime.Now < matchDate) { result = 0; //―>試合前 } else if (DateTime.Now >= matchDate) { //開始時刻後 //試合速報を確認する var realGameInfos = (from sr in Jlg.StatsReportLS where sr.GameID.ToString() == gameId select sr ).FirstOrDefault(); if (realGameInfos != null) { //Jlg GameState 4=試合終了、 string gameSetSituationCD = GetNpbGameStateID((short)realGameInfos.SituationID); //試合速報が存在する if (gameSetSituationCD == "1") //試合中 { // if (gameInfoRGI.Inning.Value >= 0) // { result = 1; //―>試合中 // } } else if (gameSetSituationCD == "4") //正常終了 { result = 2; //―>試合後 } else if (gameSetSituationCD == "9") //試合中中止 { result = 9; //―>試合後 } else { result = 0; //―>試合前 } } else { //試合速報が存在しない result = 0; //―>試合前 } } } } return result; }
/// <summary> /// 現在日付以降のシーズンを取得する /// </summary> /// <param name="dateInput"></param> /// <returns></returns> public static int GetSeasonID(int dateInput, int jtype) { JlgEntities jlg = new JlgEntities(); int gameKindId = 0; if (jtype == 1) { gameKindId = JlgConstants.JLG_GAMEKIND_J1; } else if (jtype == 2) { gameKindId = JlgConstants.JLG_GAMEKIND_J2; } else { gameKindId = JlgConstants.JLG_GAMEKIND_NABISCO; } //dateInput = 20150309; var query = (from si in jlg.ScheduleInfo join gcat in jlg.GameCategory on si.GameCategoryId equals gcat.GameCategoryId join gs in jlg.GameSchedule on gcat.GameScheduleId equals gs.GameScheduleId where (si.GameDate >= dateInput) where gs.GameKindID == gameKindId orderby si.GameDate select gcat).FirstOrDefault(); return (query == null) ? 1 : query.SeasonID; }
/// <summary> /// Get gameinfo : name, date, time of game. /// </summary> /// <param name="gameID">GameID.</param> /// <returns>GameInfo need get.</returns> public static GameInfoViewModel GetGameInfoByGameID(int sportID, int gameID) { var query = default(GameInfoViewModel); //Pro Baseball. if (sportID == 1) { NpbEntities npb = new NpbEntities(); query = (from gi in npb.GameInfoSS where gi.ID == gameID select new GameInfoViewModel { GameID = gameID, HomeTeamID = gi.HomeTeamID.Value, HomeTeamName = gi.HomeTeamName, HomeTeamNameS = gi.HomeTeamNameS, VisitorTeamID = gi.VisitorTeamID.Value, VisitorTeamName = gi.VisitorTeamName, VisitorTeamNameS = gi.VisitorTeamNameS, GameDate = gi.GameDate, Time = gi.Time }).FirstOrDefault(); } //Jleague else if (sportID == 2) { JlgEntities Jlg = new JlgEntities(); query = (from si in Jlg.ScheduleInfo join gcat in Jlg.GameCategory on si.GameCategoryId equals gcat.GameCategoryId join gs in Jlg.GameSchedule on gcat.GameScheduleId equals gs.GameScheduleId where si.GameID == gameID select new GameInfoViewModel { GameKindID = gs.GameKindID, GameID = gameID, HomeTeamID = si.HomeTeamID.Value, HomeTeamName = si.HomeTeamName, HomeTeamNameS = si.HomeTeamNameS, VisitorTeamID = si.AwayTeamID.Value, VisitorTeamName = si.AwayTeamName, VisitorTeamNameS = si.AwayTeamNameS, GameDate = si.GameDate.Value, Time = si.GameTime.ToString(), }).FirstOrDefault(); } //MLB. else if (sportID == 3) { MlbEntities mlb = new MlbEntities(); query = (from ss in mlb.SeasonSchedule join dg in mlb.DayGroup on ss.DayGroupId equals dg.DayGroupId where ss.GameID == gameID select new GameInfoViewModel { GameID = gameID, HomeTeamID = ss.HomeTeamID.Value, HomeTeamName = ss.HomeTeamName, HomeTeamNameS = ss.HomeTeamName, VisitorTeamID = ss.VisitorTeamID.Value, VisitorTeamName = ss.VisitorTeamName, VisitorTeamNameS = ss.VisitorTeamName, GameDate = dg.GameDateJPN == null ? 0 : dg.GameDateJPN.Value, Time = ss.Time }).FirstOrDefault(); } return query; }
public UserArticleInfoViewModel AppliedDetailContent(UserArticleInfoViewModel input) { UserArticleInfoViewModel model = input; switch (model.NewsClassId) { case 1: model.NewsInfo = (from brief in memberContext.BriefNews join photo in memberContext.PhotoNews on brief.NewsItemID equals photo.NewsItemID into br_photo from tmp in br_photo.DefaultIfEmpty() where (brief.Status == Constants.NEWS_VALID_STATUS && brief.CarryLimitDate >= DateTime.Now) select new NewsInfoViewModel { NewsItemID = brief.NewsItemID, DeliveryDate = brief.DeliveryDate, Headline = brief.Headline, newstext = brief.newstext, SentFrom = brief.SentFrom, Duid = tmp.Duid, Content = tmp.Content, SubHeadline = brief.SubHeadline, } into news_photo where (news_photo.NewsItemID == model.QuoteUniqueId1 && (news_photo.Duid == Constants.IMAGE_DUID || news_photo.Duid == null)) select news_photo).FirstOrDefault(); if (model.SportID <= 0) { var sportTopic = (from bn in memberContext.BriefNews join nt in memberContext.NewsTopic on bn.NewsItemID equals nt.NewsItemID join tm in memberContext.TopicMaster on nt.TopicID equals tm.TopicID where bn.NewsItemID == model.QuoteUniqueId1 && tm.ClassificationType == 1 select tm).FirstOrDefault(); if (sportTopic != null) model.SportID = sportTopic.SportID; } break; case 2: switch (model.QuoteUniqueId1) { case 1: // NPB model.NpbTeamInfo = (new NpbTeamInfoTeamTopController()).GetTeamInfo(Convert.ToInt32(model.QuoteUniqueId3)); break; case 2: //JLeague int jType = 0; var teamID = Convert.ToInt32(model.QuoteUniqueId3); if (Session["JType"] != null) { jType = Convert.ToInt32(Session["JType"].ToString()); } else { jType = GetJlgTypeByTeamId(teamID); } int gameKindID = 0; switch (jType) { case 1: gameKindID = 2; break; case 2: gameKindID = 6; break; } model.JlgTeamInfo = (new JlgTeamInfoTopController()).GetTeamInfo(teamID, gameKindID); JlgEntities jlg = new JlgEntities(); model.RankInfoRT = (from r in jlg.RankInfoRT where r.TeamID == teamID select r).FirstOrDefault(); break; case 3: //MLB model.MlbTeamInfo = (new MlbTeamInfoTeamTopController()).GetTeamInfo(Convert.ToInt32(model.QuoteUniqueId3)); break; case 4: break; } break; case 3: switch (model.QuoteUniqueId1) { case 1: //Npb break; case 2: //Jleague var teamID = Convert.ToInt32(model.QuoteUniqueId2); var playerID = Convert.ToInt32(model.QuoteUniqueId3); model.PlayerInfoYear = (new JlgTeamInfoPlayerDetailController()).GetPlayerInfo(teamID, playerID); model.PlayerSum = (new JlgTeamInfoPlayerDetailController()).GetPlayerInfo_Sum(teamID, playerID); break; case 3: //Mlb model.MlbTeamInfo = (new MlbTeamInfoTeamTopController()).GetTeamInfo(Convert.ToInt32(model.QuoteUniqueId2)); break; } break; case 6: ViewBag.Year = model.QuoteUniqueId2 / 100; ViewBag.Month = model.QuoteUniqueId2 % 100; //ViewBag.NumType = model.QuoteUniqueId3; //ViewBag.OrtherMemberId = model.QuoteUniqueId1; break; } model.RelatedTopicList = GetRelatedTopicofAPost(model.NewsClassId, model.QuoteUniqueId1, model.QuoteUniqueId2, model.QuoteUniqueId3); return model; }