private IEnumerable <long> GetMatchesToParse(string region)
{
var result = new List <long>();
try
{
var stm = $"CALL lol.LoLApiParser_GetMatchesToParse(\"{region}\");";
using (var cmd = new MySqlCommand(stm, Adapter.Connection))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(reader.GetInt64("id"));
}
}
}
}
catch (Exception e)
{
LoggersAdapter.Info($"Ошибка при получении списка матчей: {e}");
}
return(result.ToArray());
}
public bool ProcessClassicMatchResultsResponse(MatchDetailsResponse response, MatchTimeLineResponse timelineResponse, DbAdapterBase Adapter)
{
if (response == null)
{
LoggersAdapter.Warn($"Пришел пустой {nameof(response)}");
throw new ArgumentNullException(nameof(response));
}
if (timelineResponse == null)
{
LoggersAdapter.Warn($"Пришел пустой {nameof(timelineResponse)}");
throw new ArgumentNullException(nameof(timelineResponse));
}
var teamDlStats = response.teams[0];
var teamUrStats = response.teams[1];
var matchResult = new MatchResultDbClass
{
Duration = TimeSpan.FromSeconds(response.gameDuration),
GameType = response.gameType,
Id = response.gameId,
};
FillCommonResultData(teamDlStats, teamUrStats, matchResult);
FillMatchLogData(response, timelineResponse, matchResult);
if (SaveMatchInfo(matchResult, Adapter))
{
return(true);
}
LoggersAdapter.Info($"Не удалось сохранить информацию о матче в БД. Матч {response.gameId}");
return(false);
}
private void ProcessFeaturedGamesResponse(SpectatorGamesResponse response)
{
if (response == null)
{
LoggersAdapter.Warn($"Пришел пустой {nameof(response)}");
throw new ArgumentNullException(nameof(response));
}
using (var transaction = Adapter.Connection.BeginTransaction())
{
try
{
foreach (var gameMatch in response.gameList) // .Where(x => x.participants.All(part => !part.bot)) TODO: нужно или нет
{
const string q = "INSERT INTO lol.MatchDescription (id, dt_insert, dt_start, platform_id, game_mode, map_id) " +
"VALUES (@game_id, UTC_TIMESTAMP(), @game_start_time, @platform_id, @game_mode, @map_id) " +
"ON DUPLICATE KEY UPDATE id = id;";
var cmd = new MySqlCommand(q)
{
Connection = Adapter.Connection,
Transaction = transaction,
};
cmd.Parameters.AddWithValue("@game_id", gameMatch.gameId);
cmd.Parameters.AddWithValue("@game_start_time", Utils.FromMilisecEpochTime(gameMatch.gameStartTime));
cmd.Parameters.AddWithValue("@platform_id", gameMatch.platformId);
cmd.Parameters.AddWithValue("@game_mode", gameMatch.gameMode);
cmd.Parameters.AddWithValue("@map_id", gameMatch.mapId);
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (MySqlException ex)
{
try
{
transaction.Rollback();
}
catch (Exception e)
{
LoggersAdapter.Info($"Ошибка при попытке отката изменений в БД: {e}");
}
}
catch (Exception e)
{
LoggersAdapter.Info($"Ошибка при попытке добавить новые матчи: {e}");
}
}
}
private SpectatorGamesResponse FeaturedGamesParseRoutine()
{
var response = GetFeaturedMatches(_spectatorHandler);
if (response != null)
{
LoggersAdapter.Debug($"Получен ответ для региона {_parsingRegion}");
ProcessFeaturedGamesResponse(response);
}
else
{
LoggersAdapter.Debug($"Получен пустой ответ от api-сервера для региона {_parsingRegion}");
}
return(response);
}
private void MatchResultsParseRoutine(long matchId)
{
var response = GetMatchByMatchId(_matchDetailsHandler, matchId);
if (response != null)
{
LoggersAdapter.Debug($"Получен ответ по резам матча для региона {_parsingRegion}");
//ProcessMatchResultsResponse(response);
}
else
{
LoggersAdapter.Debug($"Получен пустой ответ по матчу {matchId} от api-сервера для региона {_parsingRegion}");
}
var timeLineResponse = GetMatchTimeLineByMatchId(_matchDetailsHandler, matchId);
if (timeLineResponse != null)
{
LoggersAdapter.Debug($"Получен ответ по таймлайну матча для региона {_parsingRegion}");
//ProcessMatchTimelineResponse(timeLineResponse);
}
else
{
LoggersAdapter.Debug($"Получен пустой ответ по по таймлайну матча {matchId} от api-сервера для региона {_parsingRegion}");
}
if (response != null && timeLineResponse != null)
{
switch (response.gameMode)
{
case "CLASSIC":
new ClassicMatchResultsProcessor().ProcessClassicMatchResultsResponse(response, timeLineResponse, Adapter);
break;
case "ARAM":
throw new NotImplementedException();
//new AramMatchResultsProcessor();
break;
}
}
else
{
LoggersAdapter.Info(
$"Одна из полученных структур результатов матча пуста - сохранения матча {matchId} не произойдет");
MatchResultDbClass.UpdateMatchStatus(matchId, Adapter, null, 3);
}
}
private void UpdateMatchStatus(long matchId, DbAdapterBase Adapter, MySqlTransaction transaction, int status = 3)
{
var q = $"UPDATE MatchDescription md SET md.parse_status = {status} WHERE md.id = {matchId};";
using (var mlCmd = new MySqlCommand(q, Adapter.Connection))
{
if (transaction != null)
{
mlCmd.Transaction = transaction;
}
try
{
mlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
LoggersAdapter.Info($"Ошибка при обновлении статуса матча {matchId}: {e}");
}
}
}
public void Start()
{
if (_baseRoutine == null || _baseRoutine?.Status == TaskStatus.Canceled || _baseRoutine?.Status == TaskStatus.Faulted || !MatchParserIsWorking ||
_matchResultsRoutine == null || _matchResultsRoutine?.Status == TaskStatus.Canceled || _matchResultsRoutine?.Status == TaskStatus.Faulted || !MatchResultsParserIsWorking)
{
_baseRoutineCancellationTokenSource = new CancellationTokenSource();
_baseRoutine = Task.Run(() =>
{
while (!_baseRoutineCancellationTokenSource.IsCancellationRequested)
{
var timeout = TimeSpan.FromSeconds(10);
try
{
LoggersAdapter.Info($"Старт работы LOL парсера для региона {_parsingRegion}.....");
Thread.Sleep(2000);
var response = FeaturedGamesParseRoutine();
if (response != null)
{
timeout = TimeSpan.FromSeconds(response.clientRefreshInterval);
}
}
catch (Exception e)
{
LoggersAdapter.Info($"Исключение в базовой рабочей задаче (повтор через {timeout.TotalSeconds:F1} секунд): {e}");
}
Thread.Sleep(timeout);
}
}, _baseRoutineCancellationTokenSource.Token);
_matchResultsRoutineCancellationTokenSource = new CancellationTokenSource();
_matchResultsRoutine = Task.Run(() =>
{
Thread.Sleep(10000);
while (!_matchResultsRoutineCancellationTokenSource.IsCancellationRequested)
{
var timeout = TimeSpan.FromSeconds(30);
LoggersAdapter.Info($"Тик MatchResultsRoutine для региона {_parsingRegion}.....");
var matchesToParse = GetMatchesToParse(ApiRegionString);
foreach (var match in matchesToParse)
{
try
{
Thread.Sleep(2000);
MatchResultsParseRoutine(match);
LoggersAdapter.Debug($"Обработан матч {match}");
Thread.Sleep(2000);
}
catch (Exception e)
{
LoggersAdapter.Info(
$"Исключение в >MatchResults< рабочей задаче (повтор через {timeout.TotalSeconds:F1} секунд): {e}");
}
}
Thread.Sleep(timeout);
}
}, _matchResultsRoutineCancellationTokenSource.Token);
}
}
private bool SaveMatchInfo(MatchResultDbClass matchResult, DbAdapterBase Adapter)
{
#region MatchDescription Query
var mdQuery = $"UPDATE MatchDescription md SET " +
$"md.game_type = \"{matchResult.GameType}\", " +
$"md.dt_parsed = UTC_TIMESTAMP(), " +
$"md.winner = {matchResult.Winner}, " +
$"md.duration = {(int)matchResult.Duration.TotalSeconds}," +
$"md.first_blood = {matchResult.FirstBlood}, " +
$"md.first_tower = {matchResult.FirstTower}, " +
$"md.fisrst_inhibitor = {matchResult.FirstInhibitor}," +
$"md.first_baron = {matchResult.FirstBaron}," +
$"md.first_dragon = {matchResult.FirstDragon}, " +
$"md.first_rift_hera_id = {matchResult.FirstRiftHeraId}" +
$" WHERE md.id = {matchResult.Id}";
#endregion
var partisipantsSb = new StringBuilder();
foreach (var pStat in matchResult.ParticipantStats)
{
partisipantsSb.Append($"({matchResult.Id}, {pStat.ParticipantId}, {pStat.ChampionId}, {pStat.TeamId}, " +
$"{Convert.ToInt32(pStat.Bot)}, {pStat.Spell1}, {pStat.Spell2}, \"{pStat.Role}\", \"{pStat.Lane}\", " +
$"{pStat.Kills}, {pStat.Deaths}, {pStat.Assists}, " +
$"{pStat.TotalMinionsKilled}, {pStat.TotalDamageDealt}, {pStat.MagicDamageDealt}, {pStat.PhysicalDamageDealt}, " +
$"{pStat.TotalDamageDealtToChampions}, {pStat.MagicDamageDealtToChampions}, {pStat.PhysicalDamageDealtToChampions}, " +
$"{pStat.GoldEarned}, {pStat.GoldSpent}, {pStat.ChampLevel}, {pStat.WardsPlaced}, {pStat.WardsKilled}),");
}
partisipantsSb.Length--; // убираем последнюю запятую
#region ParticipantHeroesMatchStats Query
var pStatQuery = $@"INSERT INTO ParticipantHeroesMatchStats (id_match, participant_id, champion_id, team_id, bot, spell1_id, spell2_id, role, lane,
kills, deaths, assists,
total_minions_killed, total_damage_dealt, magic_damage_dealt, physical_damage_dealt,
total_damage_dealt_to_champions, magic_damage_dealt_to_champions, physical_damage_dealt_to_champions,
gold_earned, gold_spent, champ_level, wards_placed, wards_killed)
VALUES {partisipantsSb}
ON DUPLICATE KEY UPDATE
champion_id = VALUES(champion_id),
team_id = VALUES(team_id),
spell1_id = VALUES(spell1_id),
spell2_id = VALUES(spell2_id),
role = VALUES(role),
lane = VALUES(lane),
kills = VALUES(kills),
deaths = VALUES(deaths),
assists = VALUES(assists),
total_minions_killed = VALUES(total_minions_killed),
total_damage_dealt = VALUES(total_damage_dealt),
magic_damage_dealt = VALUES(magic_damage_dealt),
physical_damage_dealt = VALUES(physical_damage_dealt),
total_damage_dealt_to_champions = VALUES(total_damage_dealt_to_champions),
magic_damage_dealt_to_champions = VALUES(magic_damage_dealt_to_champions),
physical_damage_dealt_to_champions = VALUES(physical_damage_dealt_to_champions),
gold_earned = VALUES(gold_earned),
gold_spent = VALUES(gold_spent),
champ_level = VALUES(champ_level),
wards_placed = VALUES(wards_placed),
wards_killed = VALUES(wards_killed)";
#endregion
var mlSb = new StringBuilder();
foreach (var entry in matchResult.MatchLog)
{
mlSb.Append($"({matchResult.Id}, {(int)entry.timestamp.TotalSeconds}, " +
$"{entry.gold[0]},{entry.gold[1]},{entry.gold[2]},{entry.gold[3]},{entry.gold[4]},{entry.gold[5]},{entry.gold[6]},{entry.gold[7]},{entry.gold[8]},{entry.gold[9]}, " +
$"{entry.kills[0]},{entry.kills[1]},{entry.kills[2]},{entry.kills[3]},{entry.kills[4]},{entry.kills[5]},{entry.kills[6]},{entry.kills[7]},{entry.kills[8]},{entry.kills[9]}, " +
$"{entry.assists[0]},{entry.assists[1]},{entry.assists[2]},{entry.assists[3]},{entry.assists[4]},{entry.assists[5]},{entry.assists[6]},{entry.assists[7]},{entry.assists[8]},{entry.assists[9]}, " +
$"{entry.deaths[0]},{entry.deaths[1]},{entry.deaths[2]},{entry.deaths[3]},{entry.deaths[4]},{entry.deaths[5]},{entry.deaths[6]},{entry.deaths[7]},{entry.deaths[8]},{entry.deaths[9]}, " +
$"{entry.DWNLEFT_TurretsStatus},{entry.DWNLEFT_InhibitorsStatus}, " +
$"{entry.UPRIGHT_TurretsStatus},{entry.UPRIGHT_InhibitorsStatus}),");
}
mlSb.Length--;
#region MatchLog Query
var mlQuery = $@"INSERT INTO MatchLog (id_match, time,
gold1, gold2, gold3, gold4, gold5, gold6, gold7, gold8, gold9, gold10,
kills1, kills2, kills3, kills4, kills5, kills6, kills7, kills8, kills9, kills10,
assists1, assists2, assists3, assists4, assists5, assists6, assists7, assists8, assists9, assists10,
deaths1, deaths2, deaths3, deaths4, deaths5, deaths6, deaths7, deaths8, deaths9, deaths10,
dl_turret_status, dl_inhibitor_status,
ur_turret_status, ur_inhibitor_status)
VALUES {mlSb}
ON DUPLICATE KEY UPDATE
gold1 = VALUES(gold1),
gold2 = VALUES(gold2),
gold3 = VALUES(gold3),
gold4 = VALUES(gold4),
gold5 = VALUES(gold5),
gold6 = VALUES(gold6),
gold7 = VALUES(gold7),
gold8 = VALUES(gold8),
gold9 = VALUES(gold9),
gold10 = VALUES(gold10),
kills1 = VALUES(kills1),
kills2 = VALUES(kills2),
kills3 = VALUES(kills3),
kills4 = VALUES(kills4),
kills5 = VALUES(kills5),
kills6 = VALUES(kills6),
kills7 = VALUES(kills7),
kills8 = VALUES(kills8),
kills9 = VALUES(kills9),
kills10 = VALUES(kills10),
deaths1 = VALUES(deaths1),
deaths2 = VALUES(deaths2),
deaths3 = VALUES(deaths3),
deaths4 = VALUES(deaths4),
deaths5 = VALUES(deaths5),
deaths6 = VALUES(deaths6),
deaths7 = VALUES(deaths7),
deaths8 = VALUES(deaths8),
deaths9 = VALUES(deaths9),
deaths10 = VALUES(deaths10),
assists1 = VALUES(assists1),
assists2 = VALUES(assists2),
assists3 = VALUES(assists3),
assists4 = VALUES(assists4),
assists5 = VALUES(assists5),
assists6 = VALUES(assists6),
assists7 = VALUES(assists7),
assists8 = VALUES(assists8),
assists9 = VALUES(assists9),
assists10 = VALUES(assists10),
dl_turret_status = VALUES(dl_turret_status),
dl_inhibitor_status = VALUES(dl_inhibitor_status),
ur_turret_status = VALUES(ur_turret_status),
ur_inhibitor_status = VALUES(ur_inhibitor_status)";
#endregion
//var finalSQL = $"START TRANSACTION; {mdqpart}; {pstatsQPart}; {mlPart}; COMMIT;";
using (var transaction = Adapter.Connection.BeginTransaction())
{
try
{
using (var mdCmd = new MySqlCommand(mdQuery, Adapter.Connection, transaction))
using (var psCmd = new MySqlCommand(pStatQuery, Adapter.Connection, transaction))
using (var mlCmd = new MySqlCommand(mlQuery, Adapter.Connection, transaction))
{
mdCmd.ExecuteNonQuery();
psCmd.ExecuteNonQuery();
mlCmd.ExecuteNonQuery();
MatchResultDbClass.UpdateMatchStatus(matchResult.Id, Adapter, transaction, 1);
transaction.Commit();
}
}
catch (Exception qException)
{
LoggersAdapter.Info($"Ошибка при проведении транзакции сохранения результатов: {qException}");
try
{
transaction.Rollback();
}
catch (Exception rollbackException)
{
LoggersAdapter.Info($"Ошибка при откате транзакции: {rollbackException}");
}
return(false);
}
}
return(true);
}
