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