/// <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);
if (sportID == Constants.MLB_SPORT_ID) ////////////////////////////////////////
{
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,
HomeTeamName = ss.HomeTeamName,
VisitorTeamName = ss.VisitorTeamName,
GameDate = dg.GameDateJPN == null?0:dg.GameDateJPN.Value,
Time = ss.Time
}).FirstOrDefault();
}
return query;
}
/// <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>
/// サイト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勝ち点３
{
// ホーム勝ちのの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>
/// </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[] GetMlbTDK(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;
//TODO Mlbに合わせて実装
switch (pageNo)
{
case "9-5-1":
case "9-5-2":
case "9-5-6":
if (!string.IsNullOrEmpty(subUrls[3]) && int.TryParse(subUrls[3], out teamCD))
{
string teamName = GetTeamNameById(Constants.MLB_SPORT_ID, teamCD);
title = page.Title.Replace("(チーム名)", teamName);
description = page.Description.Replace("(チーム名)", teamName);
keyWords = page.Keywords.Replace("(チーム名)", teamName);
}
break;
case "9-7":
if (!string.IsNullOrEmpty(subUrls[3]) && int.TryParse(subUrls[3], out gameID))
{
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 g).FirstOrDefault();
var gameDate = (from g in mlb.SeasonSchedule
join d in mlb.DayGroup on g.DayGroupId equals d.DayGroupId
where g.GameID == gameID
select d).FirstOrDefault();
if (gameNames != null && gameDate != null)
{
DateTime gd = DateTime.ParseExact(string.Format("{0}", gameDate.GameDateJPN), "yyyyMMdd", null);
string gameName = string.Format("{0} {1} vs {2}", gd.ToString("MM/dd"), gameNames.HomeTeamName, gameNames.VisitorTeamName);
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> getMlb(int? sportID,
List<TopicMaster> topicList, List<MemberModel> memberList, List<Member> followMemberList, List<ContributedReadingSum> contributedReadingSum)
{
// Mlb Info
MlbEntities mlb = new MlbEntities();
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 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 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 = (int)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 = (int)1, t3 = 1, t4 = (long)3 } into mlb_schedule
from mlbSchedule in mlb_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,
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).Distinct(new PostedInfoViewModelComparer()).ToList();
return query;
}
/// <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("/", ""));
MlbEntities mlb = new MlbEntities();
var query = (from ss in mlb.SeasonSchedule
join dg in mlb.DayGroup on ss.DayGroupId equals dg.DayGroupId
join ht in mlb.TeamInfo on ss.HomeTeamID equals ht.TeamID
join vt in mlb.TeamInfo on ss.VisitorTeamID equals vt.TeamID
where (dg.GameDateJPN >= startDate && dg.GameDateJPN <= endDate)
select ss).Count();
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 GetTeamInfoGTSByGameIDTeamIDVisitor(int gameID, int teamID)
{
MlbEntities mlb = new MlbEntities();
var query = (from rgi in mlb.RealGameInfo
where rgi.GameID == gameID && rgi.VisitorTeamID == teamID
select new ScoreGameInfo
{
GameID = rgi.GameID,
TeamID = teamID,
Inning = null,
TB = null,
R = rgi.VisitorScore
}).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>
/// 試合状況を取得
/// </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.MLB_SPORT_ID, gameId, memberId);
if (oddinfo.ExpectTargetID == 0) return 1;
var checkBet = oddinfo.BetSelectedID != null; // BET有無
var mlb = new MlbEntities();
var gameInfos = (from ss in mlb.SeasonSchedule //年間試合スケジュール_試合情報
join dg in mlb.DayGroup on ss.DayGroupId equals dg.DayGroupId
where ss.GameID == gameId
select new MlbGameInfos
{
GameDateJPN = dg.GameDateJPN,
Time = ss.Time
}).First();
if (gameInfos == null) return 0;
var gameDate = Utils.ConvertStrToDatetime(gameInfos.GameDateJPN.ToString());
var gameStartDateAndTime = DateTime.MaxValue; // 未定
int hours = 0;
int minute = 0;
string time = gameInfos.Time.Trim();
//When time.ToString().Length <2 then error
//When time is 0930-> Int32.TryParse to 900 then error because hours:90 and minute:0
if (time.Length > 1)
hours = Convert.ToInt32(time.Substring(0, 2));
//When time.ToString().Length <4 then error
if (time.Length > 2)
minute = Convert.ToInt32(time.Substring(2, time.Length - 2));
//End edit
gameStartDateAndTime = new DateTime(gameDate.Year, gameDate.Month, gameDate.Day, hours, minute, 0);
//予想可能日付閾値(今日日付基準の月末日取得)
var expectableEnd = DateTime.Now.EndOfTheMonthWithTime();
if (DateTime.MaxValue.Equals(gameStartDateAndTime) || DateTime.Now < gameStartDateAndTime)
{
//Case 1:Can not bet
if (gameStartDateAndTime > expectableEnd)
{
return 1; // 1:ベット不可
}
var 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 rgi in mlb.RealGameInfo where rgi.GameID == gameId select rgi;
if (realGameInfos != null)
{
if (realGameInfos.FirstOrDefault() != null)
{
//空白:試合中または試合前 2=正常終了 7=サスペンデッド 9=中止（試合中）
string gameSetSituationCd = GetNpbGameSetSituationCode((short)realGameInfos.FirstOrDefault().GameStateID);
//"0=試合前、1=試合中、2=試合後
int gameStatus = GetStatusMatch(gameId.ToString());
//int inning = realGameInfos.FirstOrDefault().Inning.HasValue ? realGameInfos.FirstOrDefault().Inning.Value : default(short);
if ( (!string.IsNullOrEmpty(gameSetSituationCd)) && ((gameSetSituationCd == "8") ||(gameSetSituationCd == "9")))
{
return 10;
}
if (gameSetSituationCd == "2")
{
return !checkBet ? 8 : 9; // 8:試合終了、ベットなし 9:試合終了、ベットあり
}
if (string.IsNullOrEmpty(gameSetSituationCd) && gameStatus > 0 /*inning >= 1*/)
{
return !checkBet ? 6 : 7; // 6:試合中、ベットなし 7:試合中、ベットあり
}
return 10; // 10:試合中止
}
return !checkBet ? 6 : 7; // 6:試合中、ベットなし 7:試合中、ベットあり
}
return 0;
}
/// <summary>
/// 試合後かどうかを判断する
/// "0=試合前、1=試合中、2=試合後
/// </summary>
/// <param name="dateCheck"></param>
/// <param name="gameId"></param>
/// <returns></returns>
public static int GetStatusMatch(string gameId)
{
int result = 0;
if (!string.IsNullOrEmpty(gameId))
{
MlbEntities mlb = new MlbEntities();
var realGameInfos = (from rgi in mlb.RealGameInfo //全試合速報
join rgih in mlb.RealGameInfoHeader
on rgi.RealGameInfoHeaderId equals rgih.RealGameInfoHeaderId
join ss in mlb.SeasonSchedule on rgi.GameID equals ss.GameID
join dg in mlb.DayGroup on ss.DayGroupId equals dg.DayGroupId
where rgi.GameID.ToString().Equals(gameId)
select new
{
GameDate = rgih.GameDateJPN,
date = rgih.GameDate,
GameTime = ss.Time
}).FirstOrDefault(); //Order by ha?
if (realGameInfos != null)
{
string dateStr = realGameInfos.GameDate.ToString();
string timeStr = (realGameInfos.GameTime == null ? "0000" : realGameInfos.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 gameInfoRGI = (from rgi in mlb.RealGameInfo
where rgi.GameID.ToString() == gameId
select rgi).FirstOrDefault();
//MLB GameState 4=試合終了、10=中止、11=サスペンデッド
int gameStatus = Convert.ToInt32(gameInfoRGI.GameStateID);
if (gameInfoRGI != null)
{
//試合速報が存在する
if (gameStatus == null || gameStatus == 0) //試合中
{
// if (gameInfoRGI.Inning.Value >= 0)
// {
result = 1; //―＞試合中
// }
}
else if (gameStatus == 4) //正常終了
{
result = 2; //―＞試合後
}
else if (gameStatus == 10) //試合中中止
{
result = 2; //―＞試合後
}
else if (gameStatus == 11) //試合開始遅延
{
result = 0; //―＞試合前
}
}
else
{
//試合速報が存在しない
result = 0; //―＞試合前
}
}
}
}
return result;
}
/// <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 month of gamedate by current year
/// </summary>
/// <returns>list month</returns>
public List<string> GetMonthOfGameDate(int teamId)
{
MlbEntities Mlb = new MlbEntities();
var query = (from seasonSchedule in mlb.SeasonSchedule
join dayGroup in mlb.DayGroup on seasonSchedule.DayGroupId equals dayGroup.DayGroupId
where (dayGroup.GameDateJPN / 10000) == DateTime.Now.Year && (seasonSchedule.HomeTeamID == teamId || seasonSchedule.VisitorTeamID == teamId)
select dayGroup.GameDateJPN.ToString().Substring(4, 2)).Distinct().ToList();
return query;
}
