private async Task <int> GetCount(FilterGame filter, SqlConnection connection) { string genres = "", platforms = ""; if (filter.Genre != null) { for (int i = 0; i < filter.Genre.Count(); i++) { genres += filter.Genre[i] + ","; } } if (filter.Platform != null) { for (int i = 0; i < filter.Platform.Count(); i++) { platforms += filter.Platform[i] + ","; } } var sql = string.Format("SELECT dbo.GetFilterMaxCountGames('{0}','{1}','{2}')", filter.Name, genres, platforms); int count = await connection.QueryFirstOrDefaultAsync <int>(sql); return(count); }
public HomeViewModel(IFrameNavigationService navigationService, IRepository gameDb) { _navigationService = navigationService; _gameDb = gameDb; currentPage = 1; filter = new FilterGame(); maxShowGames = 50; _navigationService.ShowWaitPage(); GetGenres(); GetPlatforms(); GetGames(); }
public async Task <(int, IEnumerable <IGame>)> GetBaseFilter(FilterGame filter) { using (var connection = new SqlConnection(_connectionString)) { if (filter.Count == 0 && filter.Skip == 0) { return(0, new List <Game>()); } if (filter.Count > 1000) { return(0, new List <Game>()); } /* * var sql = @"select gb.game_id as GameId, gb.game_name as Name, gb.name_second as NameSecond, gb.name_other as NameOther, gnr.genre_name as Genre, gb.year, gb.developer, down.Downloads, rat.Rating, * pl.platform_id as PlatformId, pl.platform_name as PlatformName, pl.alias, lnk.link_id as LinkId, lnk.url, lnk.type_url as TypeUrl * FROM gb_games gb * JOIN gb_genres gnr ON gnr.genre_id = gb.genre_id * JOIN gb_platforms pl ON gb.platform_id = pl.platform_id * left join (select lnks.* from gb_links lnks where lnks.type_url = 2) lnk on lnk.game_id = gb.game_id * OUTER APPLY(SELECT COUNT(*) as Downloads FROM lg_downloads down WHERE down.game_id = gb.game_id) down * OUTER APPLY(SELECT cast(AVG(cast(rat.rating as numeric(18,8))) as numeric(18,2)) as rating FROM lg_ratings rat WHERE rat.game_id = gb.game_id) rat "; */ string genres = "", platforms = ""; if (filter.Genre != null) { for (int i = 0; i < filter.Genre.Count(); i++) { genres += filter.Genre[i] + ","; } } if (filter.Platform != null) { for (int i = 0; i < filter.Platform.Count(); i++) { platforms += filter.Platform[i] + ","; } } var sql = string.Format("SELECT * FROM dbo.GetFilterGames({0}, {1}, '{2}', '{3}', '{4}', {5}, {6}, {7}, {8})", filter.Count, filter.Skip, filter.Name, genres, platforms, filter.OrderByName, filter.OrderByPlatform, filter.OrderByRating, filter.OrderByDownload); try { connection.Open(); var games = await connection.QueryAsync <Game, Platform, Genre, GameLink, Game> (sql, (game, platform, genre, gamelink) => { game.Genre = genre; game.Platform = platform; gamelink.Url = "https://zerpico.ru/retrolauncher/" + gamelink.Url; game.GameLinks = new List <GameLink>() { gamelink }; return(game); }, splitOn : "PlatformId, GenreId, LinkId"); var count = await GetCount(filter, connection); return(count, games); } catch (Exception e) { throw new Exception("Ошибка", e); } } }
public async Task <(int, IEnumerable <Game>)> GetBaseFilter(FilterGame filter) { List <Game> games = new List <Game>(); try { //словарь фильтров Dictionary <string, string> filters = new Dictionary <string, string>(); if (!string.IsNullOrEmpty(filter.Name)) { filters["Name"] = filter.Name; } if (!string.IsNullOrEmpty(filter.Genre)) { filters["Genre"] = filter.Name; } if (filter.Platform != 0) { filters["Platform"] = filter.Platform.ToString(); } if (filter.Count != 0) { filters["Count"] = filter.Count.ToString(); } if (filter.Skip != 0) { filters["Skip"] = filter.Skip.ToString(); } using (HttpClient client = new HttpClient() { Timeout = TimeSpan.FromSeconds(5) }) { //состовляем строку параметров string parameters = string.Empty; int i = 0; foreach (var dic in filters) { switch (dic.Key) { case nameof(FilterGame.Name): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Name) + $"={dic.Value}"; break; case nameof(FilterGame.Genre): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Genre) + $"={dic.Value}"; break; case nameof(FilterGame.Platform): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Platform) + $"={dic.Value}"; break; case nameof(FilterGame.Count): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Count) + $"={dic.Value}"; break; case nameof(FilterGame.Skip): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Skip) + $"={dic.Value}"; break; } i++; } var response = await client.GetAsync(@"https://www.zerpico.ru/api/games/getfilter?" + parameters); if (response.IsSuccessStatusCode) { var content = await response.Content.ReadAsStringAsync(); var result = JsonConvert.DeserializeObject <(int, IEnumerable <Game>)>(content); return(result); } } } catch (Exception e) { throw new Exception("Не удалось получить данные:.\n" + e.ToString()); } return(0, null); }
public async Task <(int, IEnumerable <Game>)> GetFilter([FromQuery] FilterGame filter) { return(await _gbLibrary.GetBaseFilter(filter)); }
public async Task <(int, IEnumerable <IGame>)> GetBaseFilter(FilterGame filter) { // List<IGame> games = new List<Model.Game>(); try { //словарь фильтров Dictionary <string, object> filters = new Dictionary <string, object>(); if (!string.IsNullOrEmpty(filter.Name)) { filters["Name"] = filter.Name; } if (filter.Genre != null && filter.Genre.Count() > 0) { filters["Genre"] = filter.Genre; } if (filter.Platform != null && filter.Platform.Count() > 0) { filters["Platform"] = filter.Platform; } filters["Count"] = filter.Count.ToString(); filters["Skip"] = filter.Skip.ToString(); //составляем строку параметров string parameters = string.Empty; int i = 0; foreach (var dic in filters) { switch (dic.Key) { case nameof(FilterGame.Name): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Name) + $"={dic.Value}"; break; case nameof(FilterGame.Genre): int[] genres = (dic.Value as int[]); for (int j = 0; j < genres.Count(); j++) { if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Genre) + $"={genres[j]}"; i++; } break; case nameof(FilterGame.Platform): int[] platforms = (dic.Value as int[]); for (int j = 0; j < platforms.Count(); j++) { if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Platform) + $"={platforms[j]}"; i++; } break; case nameof(FilterGame.Count): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Count) + $"={dic.Value}"; break; case nameof(FilterGame.Skip): if (i > 0) { parameters = parameters + "&"; } parameters += nameof(FilterGame.Skip) + $"={dic.Value}"; break; } i++; } var response = await _client.GetAsync(url + @"/games/getfilter?" + parameters); if (response.IsSuccessStatusCode) { var content = await response.Content.ReadAsStringAsync(); var result = JsonConvert.DeserializeObject <(int, IEnumerable <Model.Game>)>(content); return(result); } } catch (Exception e) { throw new Exception("Не удалось получить данные:.\n" + e.ToString()); } return(0, null); }
public async Task <(int, IEnumerable <Game>)> GetBaseFilter(FilterGame filter) { using (var connection = new SqlConnection(_connectionString)) { if (filter.Count == 0 && filter.Skip == 0) { return(0, new List <Game>()); } if (filter.Count > 1000) { return(0, new List <Game>()); } var sql = @"select gb.game_id as GameId, gb.game_name as Name, gb.name_second as NameSecond, gb.name_other as NameOther, gnr.genre_name as Genre, gb.year, gb.developer, down.Downloads, rat.Rating, pl.platform_id as PlatformId, pl.platform_name as PlatformName, pl.alias, lnk.link_id as LinkId, lnk.url, lnk.type_url as TypeUrl FROM gb_games gb JOIN gb_genres gnr ON gnr.genre_id = gb.genre_id JOIN gb_platforms pl ON gb.platform_id = pl.platform_id left join (select lnks.* from gb_links lnks where lnks.type_url = 2) lnk on lnk.game_id = gb.game_id OUTER APPLY(SELECT COUNT(*) as Downloads FROM lg_downloads down WHERE down.game_id = gb.game_id) down OUTER APPLY(SELECT cast(AVG(cast(rat.rating as numeric(18,8))) as numeric(18,2)) as rating FROM lg_ratings rat WHERE rat.game_id = gb.game_id) rat "; //var filterSql = string.Format("WHERE (gb.game_name like '%{0}%' or gb.name_second like '%{0}%' or gb.name_other like '%{0}%' ) or (genre_name = '{1}') or (gb.platform_id = {2})", filter.Name, filter.Genre, filter.Platform.PlatformId); string filterSql = string.Empty; //словарь фильтров Dictionary <string, object> filters = new Dictionary <string, object>(); if (!string.IsNullOrEmpty(filter.Name)) { filters["Name"] = filter.Name; } if (!string.IsNullOrEmpty(filter.Genre)) { filters["Genre"] = filter.Name; } if (filter.Platform != 0) { filters["Platform"] = filter.Platform; } int i = 0; foreach (var dic in filters) { switch (dic.Key) { case "Name": if (i > 0) { filterSql = " or " + filterSql; } filterSql += string.Format("(LOWER(gb.game_name) like '%{0}%' or LOWER(gb.name_second) like '%{0}%' or LOWER(gb.name_other) like '%{0}%' )", dic.Value); break; case "Genre": if (i > 0) { filterSql = " or " + filterSql; } filterSql += string.Format("(LOWER(gnr.genre_name) = '{0}')", dic.Value); break; case "Platform": if (i > 0) { filterSql = " or " + filterSql; } filterSql += string.Format("(gb.platform_id = {0})", dic.Value); break; } i++; } if (!string.IsNullOrEmpty(filterSql)) { sql = sql + "\nWHERE " + filterSql; } try { connection.Open(); var games = await connection.QueryAsync <Game, Platform, GameLink, Game> (sql + ("\n ORDER BY GameId \n OFFSET " + filter.Skip + " ROWS \n FETCH NEXT " + filter.Count + " ROWS ONLY").ToString(), (game, platform, gamelink) => { game.Platform = platform; gamelink.Url = "https://zerpico.ru/retrolauncher/" + gamelink.Url; game.GameLinks = new List <GameLink>() { gamelink }; return(game); }, splitOn : "PlatformId, LinkId"); var count = await GetCount(sql, connection); return(count, games); } catch (Exception e) { throw new Exception("Ошибка", e); } } }