public async Task <ArtistViewModel> GetArtistDetailsAsync( string artistSlug, string userId) { ArtistViewModel artistViewModel = new ArtistViewModel(); await using NpgsqlConnection connection = new NpgsqlConnection(_databaseOptions.ConnectionString); await connection.OpenAsync(); string sqlCommand = @"select a.id as artist_id, a.first_name, a.last_name, aslug.name as artist_slug, a.has_image, a.created_at, a.modified_at, a.is_approved from artists as a inner join artist_slugs as aslug on aslug.artist_id = a.id where case when @user_id <> '' then (a.is_approved = true or a.user_id = @user_id) else a.is_approved = true end and aslug.artist_id = (select artist_id from artist_slugs where name = @artist_slug) and aslug.is_primary = true order by a.first_name asc;"; await using NpgsqlCommand command = new NpgsqlCommand(sqlCommand, connection); command.Parameters.AddWithValue("@user_id", userId); command.Parameters.AddWithValue("@artist_slug", artistSlug); await using NpgsqlDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { while (await reader.ReadAsync()) { int artistId = Convert.ToInt32(reader[0]); string firstName = _textInfo.ToTitleCase(Convert.ToString(reader[1])).Trim(); string lastName = _textInfo.ToTitleCase(Convert.ToString(reader[2])).Trim(); string artistPrimarySlug = Convert.ToString(reader[3]); bool artistHasImage = Convert.ToBoolean(reader[4]); DateTime createdAt = Convert.ToDateTime(reader[5]); DateTime?modifiedAt = reader[6] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader[6]); bool isApproved = Convert.ToBoolean(reader[7]); string artistFullName = $"{firstName} {lastName}".Trim(); string artistImageUrl = ImageUrlBuilder .BuildImageUrl(artistHasImage, artistId, ImageSize.Standard); string artistImageAlternateText = ImageUrlBuilder .GetImageAlternateText(artistHasImage, artistFullName); artistViewModel.Id = artistId; artistViewModel.FirstName = firstName; artistViewModel.LastName = lastName; artistViewModel.FullName = $"{firstName} {lastName}"; artistViewModel.PrimarySlug = artistPrimarySlug; artistViewModel.IsApproved = isApproved; artistViewModel.ImageUrl = artistImageUrl; artistViewModel.ImageAlternateText = artistImageAlternateText; artistViewModel.CreatedAt = createdAt; artistViewModel.ModifiedAt = modifiedAt; } } else { throw new ArtistNotFoundException(artistSlug); } return(artistViewModel); }
public async Task <IDictionary <char, List <LibraryArtistViewModel> > > GetAllArtistsAsync() { List <LibraryArtistViewModel> artists = new List <LibraryArtistViewModel>(); await using NpgsqlConnection connection = new NpgsqlConnection(_databaseOptions.ConnectionString); await connection.OpenAsync(); await using NpgsqlCommand command = new NpgsqlCommand("select a.id, a.first_name, a.last_name, \"as\".name as primary_slug, a.has_image, count(l.title) as number_of_lyrics from artists a inner join artist_slugs \"as\" on \"as\".artist_id = a.id left join lyrics l on l.artist_id = a.id where a.is_approved = true and a.is_deleted = false and \"as\".is_primary = true and l.is_approved = true and l.is_deleted = false group by a.id, \"as\".name order by a.first_name asc;", connection); await using NpgsqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { LibraryArtistViewModel artist = new LibraryArtistViewModel(); int artistId = Convert.ToInt32(reader[0]); string firstName = Convert.ToString(reader[1]); string lastName = Convert.ToString(reader[2]); string fullName = _textInfo.ToTitleCase($"{firstName} {lastName}").Trim(); string primarySlug = Convert.ToString(reader[3]); bool hasImage = Convert.ToBoolean(reader[4]); int numberOfLyrics = Convert.ToInt32(reader[5]); string imageUrl = ImageUrlBuilder .BuildImageUrl(hasImage, artistId, ImageSize.Small); string imageAlternateText = ImageUrlBuilder .GetImageAlternateText(hasImage, primarySlug); artist.FirstName = firstName; artist.LastName = lastName; artist.FullName = fullName; artist.PrimarySlug = primarySlug; artist.ImageUrl = imageUrl; artist.ImageAlternateText = imageAlternateText; artist.NumberOfLyrics = numberOfLyrics; artists.Add(artist); } IDictionary <char, List <LibraryArtistViewModel> > dictionary = BuildDictionary(artists); return(dictionary); }
public async Task <IEnumerable <LyricItemViewModel> > GetRecentLyricsAsync() { List <LyricItemViewModel> lyricItemViewModels = new List <LyricItemViewModel>(); await using NpgsqlConnection connection = new NpgsqlConnection(_databaseOptions.ConnectionString); await connection.OpenAsync(); await using NpgsqlCommand command = new NpgsqlCommand("select a.id, l.title as lyric_title, lslugs.name as primary_lyric_slug, a.first_name, a.last_name, artist_slugs.name as artist_slug, a.has_image from lyrics as l inner join artists as a on l.artist_id = a.id inner join artist_slugs on artist_slugs.artist_id = a.id inner join lyric_slugs as lslugs on lslugs.lyric_id = l.id where artist_slugs.is_primary = true and l.is_approved = true and l.is_deleted = false and lslugs.is_primary = true order by l.created_at desc limit 10;", connection); await using NpgsqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { LyricItemViewModel lyricItemViewModel = new LyricItemViewModel(); int artistId = Convert.ToInt32(reader[0]); string lyricTitle = Convert.ToString(reader[1]).Length > 8 ? Convert.ToString(reader[1]).Truncate(8).Trim() + "…" : Convert.ToString(reader[1]); string lyricPrimarySlug = Convert.ToString(reader[2]); string artistFullName = _textInfo.ToTitleCase(Convert.ToString(reader[3]) + " " + Convert.ToString(reader[4])).Trim(); string artistPrimarySlug = Convert.ToString(reader[5]); bool artistHasImage = Convert.ToBoolean(reader[6]); string artistImageUrl = ImageUrlBuilder .BuildImageUrl(artistHasImage, artistPrimarySlug, artistId, ImageSize.Small); string artistImageAlternateText = ImageUrlBuilder .GetImageAlternateText(artistHasImage, artistFullName); lyricItemViewModel.ArtistId = artistId; lyricItemViewModel.Title = lyricTitle; lyricItemViewModel.LyricPrimarySlug = lyricPrimarySlug; lyricItemViewModel.ArtistName = artistFullName; lyricItemViewModel.ArtistPrimarySlug = artistPrimarySlug; lyricItemViewModel.ArtistImageUrl = artistImageUrl; lyricItemViewModel.ArtistImageAlternateText = artistImageAlternateText; lyricItemViewModels.Add(lyricItemViewModel); } return(lyricItemViewModels); }
public async Task <IEnumerable <ArtistItemViewModel> > GetTopTenFemaleArtistsByLyricsCountAsync() { List <ArtistItemViewModel> femaleArtists = new List <ArtistItemViewModel>(); await using NpgsqlConnection connection = new NpgsqlConnection(_databaseOptions.ConnectionString); await connection.OpenAsync(); string sqlCommand = @"select a.id, a.first_name, a.last_name, s.""name"", a.has_image, count(l.id) as number_of_lyrics from artists a left join lyrics l on a.id = l.artist_id inner join artist_slugs s on s.artist_id = a.id where a.sex = 'f' and s.is_primary = true group by a.id, s.""name"" order by number_of_lyrics desc limit 10;"; await using NpgsqlCommand command = new NpgsqlCommand(sqlCommand, connection); await using NpgsqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { int id = Convert.ToInt32(reader[0]); string firstName = _textInfo.ToTitleCase(Convert.ToString(reader[1])).Trim(); string lastName = _textInfo.ToTitleCase(Convert.ToString(reader[2])).Trim(); string primarySlug = Convert.ToString(reader[3]); bool hasImage = Convert.ToBoolean(reader[4]); string artistFullName = $"{firstName} {lastName}".Trim(); string artistImageUrl = ImageUrlBuilder .BuildImageUrl(hasImage, primarySlug, id, ImageSize.Standard); string artistImageAlternateText = ImageUrlBuilder .GetImageAlternateText(hasImage, artistFullName); ArtistItemViewModel artist = new ArtistItemViewModel(); artist.FirstName = firstName; artist.LastName = lastName; artist.PrimarySlug = primarySlug; artist.ImageUrl = artistImageUrl; artist.ImageAlternateText = artistImageAlternateText; femaleArtists.Add(artist); } return(femaleArtists); }
public async Task <IEnumerable <SearchArtistResultViewModel> > SearchArtistsAsync( string artistName) { string artistNameStandardized = artistName.NormalizeStringForUrl(); List <SearchArtistResultViewModel> artists = new List <SearchArtistResultViewModel>(); await using NpgsqlConnection connection = new NpgsqlConnection(_databaseOptions.ConnectionString); await connection.OpenAsync(); await using NpgsqlCommand command = new NpgsqlCommand("select a.id, a.first_name, a.last_name, \"as\".name primary_slug, a.has_image from artists a inner join artist_slugs \"as\" on a.id = \"as\".artist_id where a.id in (select distinct artist_id from artist_slugs where name like @artist_name) and a.is_approved = true and a.is_deleted = false and \"as\".is_primary = true;", connection); command.Parameters.AddWithValue("@artist_name", $"%{artistNameStandardized}%"); await using NpgsqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { SearchArtistResultViewModel artist = new SearchArtistResultViewModel(); int artistId = Convert.ToInt32(reader[0]); string name = _textInfo.ToTitleCase(Convert.ToString(reader[1]) + " " + Convert.ToString(reader[2])); string primarySlug = Convert.ToString(reader[3]); bool hasImage = Convert.ToBoolean(reader[4]); string imageUrl = ImageUrlBuilder .BuildImageUrl(hasImage, artistId, ImageSize.ExtraSmall); string imageAlternateText = ImageUrlBuilder .GetImageAlternateText(hasImage, name); artist.Name = name; artist.PrimarySlug = primarySlug; artist.ImageUrl = imageUrl; artist.ImageAlternateText = imageAlternateText; artists.Add(artist); } return(artists); }