public override Output OnQuery()
{
_output = new Output();
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
var team = footballDB.Query <Team>(FindTeamQuery).FirstOrDefault();
if (team == null)
{
_output.Result = 1;
return;
}
// Finished Fixtures
if (_input.SearchFixtureStatusType == SearchFixtureStatusType.Finished)
{
_output.FixtureDetails = footballDB.Query <DB_FootballFixtureDetail>(FinishedFixturesQuery, new { CurDate = DateTime.UtcNow });
}
// Scheduled Fixtures
else if (_input.SearchFixtureStatusType == SearchFixtureStatusType.Scheduled)
{
_output.FixtureDetails = footballDB.Query <DB_FootballFixtureDetail>(ScheduledixturesQuery, new { CurDate = DateTime.UtcNow });
}
_output.Result = 0;
},
this.OnError);
return(_output);
}
public override P_GET_MATCH_H2H.Output OnQuery()
{
_output = new Output();
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
var fixture = footballDB.Query <Fixture>($"SELECT * FROM fixture WHERE id = {_input.FixtureId}").FirstOrDefault();
if (fixture == null ||
fixture.home_team_id != _input.HomeTeamId ||
fixture.away_team_id != _input.AwayTeamId)
{
_output.Result = 1;
return;
}
var SearchStartTime = new DateTime(fixture.match_time.Year - 10, 1, 1);
var SearchEndTime = fixture.match_time.AddMinutes(-1);
_output.H2HFixtures = footballDB.Query <DB_FootballFixtureDetail>(FixturesQueryString,
new { _input.HomeTeamId, _input.AwayTeamId, SearchStartTime, SearchEndTime });
_output.Result = 0;
},
this.OnError);
return(_output);
}
public override P_SELECT_LINKED_BILLING.Output OnQuery()
{
_output.Result = -1;
DapperFacade.DoWithDBContext(
null,
(Contexts.PoseGlobalDB pose_globalDB) =>
{
_output.UserRole = pose_globalDB.Query <UserRole>(
"SELECT * FROM user_role WHERE user_no = @userNo",
new { userNo = _input }).FirstOrDefault();
if (_output.UserRole == null)
{
_output.Result = 1;
return;
}
_output.InAppBilling = pose_globalDB.Query <InAppBilling>(
"SELECT * FROM in_app_billing WHERE trans_no = @transNo",
new { transNo = _output.UserRole.linked_trans_no }).FirstOrDefault();
if (_output.InAppBilling == null)
{
_output.Result = 2;
return;
}
_output.Result = 0;
},
this.OnError);
return(_output);
}
public override P_GET_LEAGUE_OVERVIEW.Output OnQuery()
{
_output = new Output();
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
_output.LeagueDetail = footballDB.Query <DB_FootballLeagueDetail>(LeagueQuery).FirstOrDefault();
if (_output.LeagueDetail == null)
{
_output.Result = 1;
return;
}
_output.StandingsDetails = footballDB.Query <DB_FootballStandingsDetail>(StandingsQuery, new { LeagueId = _output.LeagueDetail.Id });
//if (_output.StandingsDetails.Count() == 0)
//{
// var participatingTeams = footballDB.Query<int>("SELECT home_team_id as team_id FROM fixture WHERE league_id = @LeagueId " +
// "UNION SELECT away_team_id as team_id FROM fixture WHERE league_id = @LeagueId LIMIT 64;", new { LeagueId = _output.LeagueDetail.Id }).ToArray();
// if (participatingTeams.Length > 0)
// _output.ParticipatingTeamDetails = footballDB.Query<DB_FootballTeamDetail>(TeamsQuery, new { TeamIds = participatingTeams });
//}
_output.Result = 0;
},
this.OnError);
return(_output);
}
public override P_GET_MATCH_OVERVIEW.Output OnQuery()
{
_output = new Output();
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
var fixture = footballDB.Query <Fixture>($"SELECT * FROM fixture WHERE id={_input.FixtureId}").FirstOrDefault();
if (fixture == null)
{
_output.Result = 1;
return;
}
_output.HomeRecntFixtures = footballDB.Query <DB_FootballFixtureDetail>(FixturesQueryString, new { TeamId = fixture.home_team_id, MatchTime = fixture.match_time });
_output.AwayRecentFixtures = footballDB.Query <DB_FootballFixtureDetail>(FixturesQueryString, new { TeamId = fixture.away_team_id, MatchTime = fixture.match_time });
_output.League_HomeRecentFixtures = footballDB.Query <DB_FootballFixtureDetail>(LeagueFixturesQueryString, new { LeagueId = fixture.league_id, TeamId = fixture.home_team_id, MatchTime = fixture.match_time });
_output.League_AwayRecentFixtures = footballDB.Query <DB_FootballFixtureDetail>(LeagueFixturesQueryString, new { LeagueId = fixture.league_id, TeamId = fixture.away_team_id, MatchTime = fixture.match_time });
_output.StandingsDetails = footballDB.Query <DB_FootballStandingsDetail>(StandingsQueryString, new { LeagueId = fixture.league_id, HomeTeamId = fixture.home_team_id, AwayTeamId = fixture.away_team_id });
_output.Result = 0;
},
this.OnError);
return(_output);
}
public override IEnumerable <DB_FootballFixtureDetail> OnQuery()
{
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
_output = footballDB.Query <DB_FootballFixtureDetail>(queryString, new { Ids = _input.Indexes });
},
this.OnError);
return(_output);
}
public override IEnumerable <Tables.Odds> OnQuery()
{
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
_output = footballDB.Query <Tables.Odds>($"SELECT * FROM odds WHERE fixture_id={_input.FixtureId} ");
},
this.OnError);
return(_output);
}
public override int OnQuery()
{
_output = -1;
DapperFacade.DoWithDBContext(
null,
(Contexts.PoseGlobalDB pose_globalDB) =>
{
var affects = pose_globalDB.ExecuteSP("P_INSERT_USER_BASE", this.Parmeters);
_output = this.Parmeters.Get <int>("o_result");
},
this.OnError);
return(_output);
}
public override IEnumerable <T> OnQuery()
{
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
if (!string.IsNullOrEmpty(_input.Query))
{
_output = footballDB.Query <T>($"{_input.Query} {_input.Where} {_input.GroupBy} {_input.OrderBy}");
}
},
this.OnError);
return(_output);
}
public override int OnQuery()
{
DapperFacade.DoWithDBContext(
null,
(Contexts.PoseGlobalDB globalDB) =>
{
if (!string.IsNullOrEmpty(_input))
{
_output = globalDB.Execute(_input);
}
},
this.OnError);
return(_output);
}
public override Output OnQuery()
{
_output.Result = -1;
DapperFacade.DoWithDBContext(
null,
(Contexts.PoseGlobalDB pose_globalDB) =>
{
var affects = pose_globalDB.ExecuteSP("P_UPDATE_IN_APP_BILLING", this.Parmeters);
_output.Result = this.Parmeters.Get <int>("o_result");
},
this.OnError);
return(_output);
}
public override Output OnQuery()
{
DapperFacade.DoWithDBContext(
null,
(Contexts.PoseGlobalDB pose_globalDB) =>
{
_output = pose_globalDB.Query <Output>(UserSelectQuery, new { platform_id = _input }).FirstOrDefault();
if (_output != null)
{
pose_globalDB.Execute("UPDATE user_base SET last_login_date = @last_login_date WHERE platform_id = @platform_id",
new { last_login_date = DateTime.UtcNow, platform_id = _input });
}
},
this.OnError);
return(_output);
}
public override IEnumerable <Fixture> OnQuery()
{
var sb = new StringBuilder();
sb.Append($"SELECT f.* ");
sb.Append("FROM fixture as f ");
sb.Append($"INNER JOIN league_coverage as lc on f.{nameof(Fixture.league_id)} = lc.{nameof(LeagueCoverage.league_id)} ");
sb.Append($"WHERE {_input.WHERE};");
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
_output = footballDB.Query <Fixture>(sb.ToString());
},
this.OnError);
return(_output);
}
public override bool OnQuery()
{
_output = false;
DapperFacade.DoWithDBContext(
null,
(Contexts.PoseGlobalDB pose_globalDB) =>
{
pose_globalDB.Execute(
"UPDATE user_role " +
"SET role_type = @RoleType, linked_trans_no = @LinkedTransNo, " +
"expire_time = @RoleExpireTime, upt_date = @CurrentTime " +
"WHERE user_no = @UserNo"
, _input);
_output = true;
},
this.OnError);
return(_output);
}
public override P_GET_TEAM_OVERVIEW.Output OnQuery()
{
_output = new Output();
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
_output.DB_FixtureDetails = new List <DB_FootballFixtureDetail>();
var leagueDetails = footballDB.Query <DB_FootballLeagueDetail>(ParticipatingLeaguesQueryString).ToList();
if (leagueDetails == null || leagueDetails.Count() == 0)
{
return;
}
// 중복 리그 제거
var leagues = new List <DB_FootballLeagueDetail>();
var leagueByGroups = leagueDetails.GroupBy(elem => $"{elem.CountryName}:{elem.LeagueType}:{elem.Name}");
foreach (var leagueByGroup in leagueByGroups)
{
if (leagueByGroup.Count() == 1)
{
leagues.Add(leagueByGroup.First());
}
else
{
leagues.Add(leagueByGroup.OrderByDescending(elem => elem.SeasonStartDate).First());
}
}
foreach (var league in leagues)
{
var fixtureDetails = footballDB.Query <DB_FootballFixtureDetail>(FixturesQueryString, new { LeagueId = league.Id });
_output.DB_FixtureDetails.AddRange(fixtureDetails);
}
},
this.OnError);
return(_output);
}
public override IEnumerable <DB_FootballVIPFixtureDetail> OnQuery()
{
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
// Finished Fixtures
if (_input.IsSelectHistory)
{
_output = footballDB.Query <DB_FootballVIPFixtureDetail>(_finishedFixturesQuery, new { StartTime = DateTime.UtcNow.AddDays(-7), EndTime = DateTime.UtcNow });
}
// Scheduled Fixtures
else
{
_output = footballDB.Query <DB_FootballVIPFixtureDetail>(_scheduledixturesQuery, new { StartTime = DateTime.UtcNow.Date, EndTime = DateTime.UtcNow.AddHours(48) });
}
},
this.OnError);
return(_output);
}
public override int OnQuery()
{
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
int ret = footballDB.Execute("DELETE FROM prediction WHERE fixture_id = @FixtureId AND main_label = @MainLabel AND sub_label = @SubLabel; ",
new { _input.FixtureId, _input.MainLabel, _input.SubLabel });
if (ret == 1)
{
_output = 0;
}
else
{
_output = 1;
}
},
this.OnError);
return(_output);
}
public override IEnumerable <Output> OnQuery()
{
var sb = new StringBuilder();
sb.Append($"SELECT l.{nameof(League.name)} as {nameof(Output.LeagueName)}, ");
sb.Append($"l.{nameof(League.logo)} as {nameof(Output.LeagueLogo)}, l.{nameof(League.type)} as {nameof(Output.LeagueType)}, ");
sb.Append($"c.{nameof(Country.name)} as {nameof(Output.CountryName)}, c.{nameof(Country.logo)} as {nameof(Output.CountryLogo)} ");
sb.Append("FROM league_coverage as lc ");
sb.Append($"INNER JOIN league as l on lc.{nameof(LeagueCoverage.league_id)} = l.{nameof(League.id)} ");
sb.Append($"INNER JOIN country as c on l.{nameof(League.country_name)} = c.{nameof(Country.name)} ");
sb.Append($"WHERE lc.{nameof(LeagueCoverage.predictions)} = 1 ");
sb.Append($"GROUP BY l.{nameof(League.country_name)}, l.{nameof(League.name)}, l.{nameof(League.type)}, l.{nameof(League.logo)}, c.{nameof(Country.logo)};");
DapperFacade.DoWithDBContext(
null,
(Contexts.FootballDB footballDB) =>
{
_output = footballDB.Query <Output>(sb.ToString());
},
this.OnError);
return(_output);
}
public override Output OnQuery()
{
_output.Result = -1;
DapperFacade.DoWithDBContext(
null,
(Contexts.PoseGlobalDB pose_globalDB) =>
{
var transNo = pose_globalDB.ExecuteScalar <long>(
"INSERT INTO in_app_billing(user_no, store_type, product_id, purchase_state, upt_date, ipt_date) VALUES " +
"(@UserNo, @StoreType, @ProductID, @PurchaseState, @CurrentTime, @CurrentTime); " +
"SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);", _input);
if (transNo > 0)
{
_output.Result = 0;
_output.TransNo = transNo;
}
},
this.OnError);
return(_output);
}
