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