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