/// <summary> /// Join 3 table ExhibitionGameStats, ExhibitionGameStatsHeaders,TeamIconMSTs /// </summary> /// <param name="gameType">Game type </param> /// <returns>List of ranking information</returns> private NpbOrderViewModel GetExhibitionGameStats(int gameType) { NpbEntities npb = new NpbEntities(); var firstHeader = from header in npb.ExhibitionGameStatsHeader select header; if (firstHeader == null || !firstHeader.Any()) return null; var query = from egs in npb.ExhibitionGameStats join egsHeader in npb.ExhibitionGameStatsHeader on egs.ExhibitionGameStatsHeaderId equals egsHeader.ExhibitionGameStatsHeaderId join ti in npb.TeamIconNpb on egs.TeamCD equals ti.TeamCD select new NpbOfficialStatsViewModel { TeamID = egs.TeamCD, GameAssortment = egsHeader.GameAssortment, TeamIcon = ti.TeamIcon, Ranking = egs.Ranking, ShortNameTeam = egs.ShortNameTeam, Game = egs.Game, Win = egs.Win, Lose = egs.Lose, Draw = egs.Draw, WinningPercentage = egs.WinningPercentage, CreatedDate = egs.CreatedDate, GameBehind = egs.GameBehind } into exhibitionGameStats where (exhibitionGameStats.GameAssortment == gameType) orderby exhibitionGameStats.Ranking select exhibitionGameStats; NpbOrderViewModel orderViewModel = new NpbOrderViewModel(); orderViewModel.GameAssortment = 0; orderViewModel.Matchday = firstHeader.First().Matchday; orderViewModel.officialStatsViewModels = query; return orderViewModel; }
/// <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> /// </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> /// /// </summary> /// <param name="url">url</param> /// <returns>string array</returns> public static string[] GetNpbTDK(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]); } } //var url = Context.Request.Path; 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; } else { return new string[4]; } int teamCD, playerCD, gameID; switch (pageNo) { case "8-1": case "8-2": case "8-3": case "8-4": case "8-5": case "8-7": title = page.Title; description = page.Description; keyWords = page.Keywords; break; case "8-5-1": case "8-5-2": case "8-5-3": case "8-5-4": case "8-5-5": case "8-5-6": case "8-5-7": if (!string.IsNullOrEmpty(subUrls[3]) && int.TryParse(subUrls[3], out teamCD)) { string teamName = GetTeamNameById(Constants.NPB_SPORT_ID, teamCD); title = page.Title.Replace("(チーム名)", teamName); description = page.Description.Replace("(チーム名)", teamName); keyWords = page.Keywords.Replace("(チーム名)", teamName); } break; case "8-5-8": case "8-5-9": if (!string.IsNullOrEmpty(subUrls[3]) && !string.IsNullOrEmpty(subUrls[4]) && int.TryParse(subUrls[3], out teamCD) && int.TryParse(subUrls[4], out playerCD)) { NpbEntities npb = new NpbEntities(); var teamName = (from t in npb.TeamInfoMST where t.TeamCD == teamCD select t).FirstOrDefault(); var playerName = (from t in npb.PlayerInfoMST where t.PlayerCD == playerCD select t).FirstOrDefault(); title = page.Title.Replace("(チーム名略称)", (teamName != null) ? teamName.ShortNameTeam : string.Empty).Replace("(選手名)", (playerName != null) ? playerName.Player : string.Empty); description = page.Description.Replace("(チーム名)", (teamName != null) ? teamName.Team : string.Empty).Replace("(選手名)", (playerName != null) ? playerName.Player : string.Empty); keyWords = page.Keywords.Replace("(チーム名)", (teamName != null) ? teamName.Team : string.Empty).Replace("(選手名)", (playerName != null) ? playerName.Player : string.Empty); } break; case "8-6": case "8-6-1": case "8-6-2": if (!string.IsNullOrEmpty(subUrls[3]) && int.TryParse(subUrls[3], out gameID)) { 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); string gameName = string.Format("{0} {1} vs {2}", gd.ToString("MM/dd"), gameNames.HomeTeamNameS, gameNames.VisitorTeamNameS); title = page.Title.Replace("(YY/MM 対戦カード名)", gameName); description = page.Description.Replace("(YY/MM 対戦カード名)", gameName); keyWords = page.Keywords.Replace("(チーム名1)", gameNames.HomeTeamName).Replace("(チーム名2)", gameNames.VisitorTeamName); } } break; case "7-1": long newsItemId; if (!string.IsNullOrEmpty(subUrls[3]) && long.TryParse(subUrls[3], out newsItemId)) { ComEntities news = new ComEntities(); var newsDetail = (from n in news.BriefNews where n.NewsItemID == newsItemId && n.Status == Constants.NEWS_VALID_STATUS && n.CarryLimitDate >= DateTime.Now select n).FirstOrDefault(); if (newsDetail != null) { title = string.Format("{0} | スポログ", ShortenString(newsDetail.Headline, 25)); description = ShortenString(newsDetail.newstext, 100); keyWords = subUrls[1]; } } 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; }
private static IEnumerable<PostedInfoViewModel> getNpb(int? sportID, List<TopicMaster> topicList, List<MemberModel> memberList, List<Member> followMemberList, List<ContributedReadingSum> contributedReadingSum) { // Npb info NpbEntities npb = new NpbEntities(); var npbTeamList = new List<TeamInfoMST>(npb.TeamInfoMST); var npbPlayerList = new List<PlayerInfoMST>(npb.PlayerInfoMST); var npbGameSSList = new List<GameInfoSS>(npb.GameInfoSS); 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 = (int)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 = (int)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 = (int)1, g3 = 1, g4 = (long)1 } into npb_game from npbGame in npb_game.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, 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), 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> /// Get month of gamedate by current year /// </summary> /// <returns>list month</returns> public List<string> GetMonthOfGameDate(int teamId) { NpbEntities npb = new NpbEntities(); var query = (from gameInfoSS in npb.GameInfoSS where (gameInfoSS.GameDate / 10000) == DateTime.Now.Year && (gameInfoSS.HomeTeamID == teamId || gameInfoSS.VisitorTeamID == teamId) select gameInfoSS.GameDate.ToString().Substring(4, 2)).Distinct().ToList(); return query; }
/// <summary> /// Join 3 table OfficialStatsHeaders, OfficialStatsHeaders,TeamIconMSTs /// </summary> /// <param name="gameType">Game type (1-セ・リーグ/2-パ・リーグ/26-交流戦)</param> /// <returns>List of ranking information</returns> private NpbOrderViewModel GetOfficialStats(int gameType) { NpbEntities npb = new NpbEntities(); var firstHeader = from header in npb.OfficialStatsHeaderNpb where(header.GameAssortment == gameType) select header; if (firstHeader == null || !firstHeader.Any()) return null; var query = from os in npb.OfficialStatsNpb join osHeader in npb.OfficialStatsHeaderNpb on os.OfficialStatsHeaderNpbId equals osHeader.OfficialStatsHeaderNpbId join ti in npb.TeamIconNpb on os.TeamCD equals ti.TeamCD select new NpbOfficialStatsViewModel { TeamID = os.TeamCD, GameAssortment = osHeader.GameAssortment, TeamIcon = ti.TeamIcon, Ranking = os.Ranking, ShortNameTeam = os.ShortNameTeam, Game = os.Game, Win = os.Win, Lose = os.Lose, Draw = os.Draw, WinningPercentage = os.WinningPercentage, GameBehind = os.GameBehind, CreatedDate = os.CreatedDate, RestGame = os.RestGame } into officialStats where (officialStats.GameAssortment == gameType) orderby officialStats.Ranking select officialStats; NpbOrderViewModel orderViewModel = new NpbOrderViewModel(); orderViewModel.GameAssortment = gameType; orderViewModel.Matchday = firstHeader.First().Matchday; orderViewModel.officialStatsViewModels = query; return orderViewModel; }
/// <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; }
/// <summary> /// Get game result in right content by gameID /// </summary> /// <param name="gameID">GameID.</param> /// <returns>Result of game : score.</returns> public static GameInfoViewModel GetGameResultInRightContent(int gameID) { NpbEntities npb = new NpbEntities(); var query = (from rgi in npb.RealGameInfoRootRGI join gi in npb.GameInfoRGI on rgi.RealGameInfoRootRGIId equals gi.RealGameInfoRootRGIId join tm in npb.TeamInfoMST on gi.HomeTeam equals tm.TeamCD join tm1 in npb.TeamInfoMST on gi.VisitorTeam equals tm1.TeamCD join si1 in npb.ScoreRGI on rgi.RealGameInfoRootRGIId equals si1.RealGameInfoRootRGIId into tmp1 from si11 in tmp1.DefaultIfEmpty() join si2 in npb.ScoreRGI on rgi.RealGameInfoRootRGIId equals si2.RealGameInfoRootRGIId into tmp2 from si22 in tmp2.DefaultIfEmpty() where gi.GameID == gameID && (si11 == null || si11.HomeVisitor == Constants.HOMETEAM) && (si22 == null || si22.HomeVisitor == Constants.VISITORTEAM) select new GameInfoViewModel { GameID = gi.GameID, HomeTeamID = gi.HomeTeam, VisitorTeamID = gi.VisitorTeam.Value, HomeTeamName = tm.ShortNameTeam, VisitorTeamName = tm1.ShortNameTeam, Round = gi.Round.Value, GameDate = rgi.Matchday.Value, HomeTeamScore = (si11.TotalScore.Value == null ? 0 : si11.TotalScore.Value), VisitorTeamScore = (si22.TotalScore.Value == null ? 0 : si22.TotalScore.Value), GameSituationID = gi.GameSetSituationCD, Inning = gi.Inning.Value, BottomTop = gi.BottomTop }).FirstOrDefault(); return query; }
/// <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) { NpbEntities npb = new NpbEntities(); var query = (from gi in npb.GameInfoGTS join ti in npb.TeamInfoGTS on gi.GameInfoGTSId equals ti.GameInfoGTSId where gi.GameID == gameID && ti.ID == teamID select new ScoreGameInfo { GameID = gi.GameID, TeamID = ti.ID, Inning = gi.Inning == null ? 0 : gi.Inning, TB = gi.TB == null ? 0 : gi.TB, R = ti.R }).FirstOrDefault(); ScoreGameInfo scoreInfo = new ScoreGameInfo(); if (query == null) { scoreInfo.GameID = gameID; scoreInfo.TeamID = teamID; scoreInfo.Inning = 0; scoreInfo.TB = 0; scoreInfo.R = 0; return scoreInfo; } else { return query; } }
/// <summary> /// Get TeamID For GameText By GameID /// </summary> /// <param name="gameID">GameID.</param> /// <param name="BottomTop">BottomTop : 1:表 = 先攻 = ビジター /// 2:裏 = 後攻 = ホーム</param> /// <returns>TeamName</returns> public static string GetTeamIDForGameText(int gameID, int BottomTop) { if (gameID != null) { NpbEntities npb = new NpbEntities(); var query = (from gi in npb.GameInfoGTE join ti in npb.TeamInfoGTE on gi.GameInfoGTEId equals ti.GameInfoGTEId where gi.GameID == gameID && ti.HV != BottomTop select ti.NameS).FirstOrDefault(); return query; } else { return string.Empty; } }
/// <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.NPB_SPORT_ID, gameId, memberId); if (oddinfo.ExpectTargetID == 0) return 1; var isBet = oddinfo.BetSelectedID != null; // BET有無 var npb = new NpbEntities(); // 年間試合スケジュール_試合情報 を取得 var gameInfoSS = npb.GameInfoSS.FirstOrDefault(x => x.ID == gameId); if (gameInfoSS == null) return 0; // 全試合情報を取得 string gameSetSituationCd = null; // 試合終了状況コード var gameInfoRGI = npb.GameInfoRGI.FirstOrDefault(x => x.GameID == gameId); if (gameInfoRGI != null) { gameSetSituationCd = gameInfoRGI.GameSetSituationCD; } if (!string.IsNullOrEmpty(gameSetSituationCd) && (gameSetSituationCd == "0")) // 中止(試合前) { return 10; } var gameDate = Utils.ConvertStrToDatetime(gameInfoSS.GameDate.ToString()); var hours = Convert.ToInt32(gameInfoSS.Time.Substring(0, 2)); var minute = Convert.ToInt32(gameInfoSS.Time.Substring(2, 2)); var 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; } //予想可能限界日付時間(Todo:5分前の定義が、マジックナンバー) var limitTime = gameStartDateAndTime.AddMinutes(-5); if (DateTime.Now < limitTime) //締切時間前 { return !isBet ? 2 : 3; } return !isBet ? 4 : 5; // 締切時間以降 } // 試合開始時間以降 if (gameInfoRGI == null) { return !isBet ? 4 : 5; } if (!string.IsNullOrEmpty(gameSetSituationCd) && (gameSetSituationCd == "9")) // 中止(試合中) { return 10; } if (gameSetSituationCd == "2" || gameSetSituationCd == "4" || gameSetSituationCd == "5" || gameSetSituationCd == "8" || gameSetSituationCd == "A" || gameSetSituationCd == "B" || gameSetSituationCd == "C" || gameSetSituationCd == "D") { return !isBet ? 8 : 9; } if (!string.IsNullOrEmpty(gameSetSituationCd)) { return 10; } int inning = gameInfoRGI.Inning ?? default(short); // 現イニング if (inning == 0) // 試合前 { return !isBet ? 4 : 5; } if (inning >= 1) // 試合中 { return !isBet ? 6 : 7; } return 0; }
/// <summary> /// Check status match npb team info schedule /// </summary> /// <param name="gameId"></param> /// <param name="type"></param> /// <returns> /// 0 = Match not yet started /// 1 = Match ongoing /// 2 = Match finish /// 3 = Match delay /// </returns> public static int GetStatusMatch(string gameId, int type = 0) { int result = 0; //ゲームIDがNullでない場合 if (!string.IsNullOrEmpty(gameId)) { var npb = new NpbEntities(); var query = (from a in npb.RealGameInfoRootRGI join b in npb.GameInfoRGI on a.RealGameInfoRootRGIId equals b.RealGameInfoRootRGIId where b.GameID.ToString().Equals(gameId) select (int?)a.Matchday).FirstOrDefault(); DateTime matchDate; if (query.HasValue) { matchDate = Utils.ConvertStrToDatetime(query.Value.ToString()); if (DateTime.Now < matchDate) { result = 0; } else if (DateTime.Now >= matchDate) { var gameInfoRGI = npb.GameInfoRGI.FirstOrDefault(p => p.GameID.ToString().Equals(gameId)); if (gameInfoRGI != null) { if (string.IsNullOrEmpty(gameInfoRGI.GameSetSituationCD)) { if (gameInfoRGI.Inning.Value > 0) { result = 1; } else { result = 0; } } else if (Convert.ToInt32(gameInfoRGI.GameSetSituationCD) == 2) { result = 2; } else { //check type =1 use in 8-5-2 team schedule if (type != 0) { result = 3; } } } else { result = 0; } } } } return result; }
/// <summary> /// Get status used for point by gameID. /// </summary> /// <param name="gameID">GameID.</param> /// <returns>Game Status.</returns> public static int GetStatusForPointByGameID(int gameID) { int result = 0; if (gameID == null) return result; NpbEntities npb = new NpbEntities(); var query = (from a in npb.RealGameInfoRootRGI join b in npb.GameInfoRGI on a.RealGameInfoRootRGIId equals b.RealGameInfoRootRGIId where b.GameID == gameID select (int?)a.Matchday).FirstOrDefault(); DateTime matchDate; if (query.HasValue) { matchDate = Utils.ConvertStrToDatetime(query.Value.ToString()); if (DateTime.Now < matchDate) { result = 0; } else if (DateTime.Now >= matchDate) { var queryTime = npb.GameInfoSS.Where(x => x.ID == gameID).Select(x => new { x.GameDate, x.Time }).FirstOrDefault(); var gameInfoRGI = (from a in npb.GameInfoRGI where a.GameID == gameID select a).FirstOrDefault(); if (gameInfoRGI != null) { if (string.IsNullOrEmpty(gameInfoRGI.GameSetSituationCD)) { if (gameInfoRGI.Inning.Value > 0) { result = 1; } else { //Test 5 minutes before game start. if (queryTime != null) { var remainType = Utils.CalculateTimeRemain(queryTime.GameDate, queryTime.Time); result = (remainType == 2 || remainType == 3) ? 1 : 0; } else { result = 0; } } } else if (Convert.ToInt32(gameInfoRGI.GameSetSituationCD) == 2) { result = 2; } } else { result = 0; } } } else { var queryTime = npb.GameInfoSS.Where(x => x.ID == gameID).Select(x => new { x.GameDate, x.Time }).FirstOrDefault(); if (queryTime != null) { var gDate = Utils.ConvertStrToDatetime(queryTime.GameDate.ToString()); if (gDate.Date == DateTime.Now.Date) { var remainType = Utils.CalculateTimeRemain(queryTime.GameDate, queryTime.Time); result = (remainType == 2 || remainType == 3) ? 1 : 0; } else if (gDate.Date < DateTime.Now.Date) { result = 2; } } } return result; }
/// <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); }
public NpbGameInfoService(NpbEntities dbContext) { this.dbContext = dbContext; }
/// <summary> /// Get month of gamedate by current year /// </summary> /// <returns>list month</returns> public static int GetMonthOfGameDate(int month) { //Get first date of month var firstDateOfMonth = new DateTime(DateTime.Now.Year, month, 1); //Get first monday date of month var firstMondayOfMonth = firstDateOfMonth.AddDays((DayOfWeek.Monday < firstDateOfMonth.DayOfWeek ? 7 : 0) + DayOfWeek.Monday - firstDateOfMonth.DayOfWeek); //Get last monday of month var lastMondayOfMonth = Utils.GetLastMondayOfMonth(month, DateTime.Now.Year); //Get last sunday of month var lastSundayOfMonth = lastMondayOfMonth.AddDays(6); //format value int startDate = int.Parse(firstMondayOfMonth.ToShortDateString().Replace("/", "")); int endDate = int.Parse(lastSundayOfMonth.ToShortDateString().Replace("/", "")); NpbEntities npb = new NpbEntities(); var query = (from a in npb.GameInfoSS join b in npb.TeamInfoMST on a.HomeTeamID equals b.TeamCD join c in npb.TeamInfoMST on a.VisitorTeamID equals c.TeamCD where (a.GameDate >= startDate && a.GameDate <= endDate) && (b.TeamCD != 397 || c.TeamCD != 397) select a).Count(); return query; }