public async Task <IActionResult> Lyric(
string artistSlug,
string lyricSlug)
{
string userId = User.Identity.IsAuthenticated
? User.GetUserId().ToString()
: string.Empty;
LyricDetailsViewModel viewModel = await _lyricsService
.GetSingleLyricAsync(artistSlug, lyricSlug, userId);
viewModel.PrimarySlug = lyricSlug;
return(View(viewModel));
}
public async Task <LyricDetailsViewModel> GetSingleLyricAsync(
string artistSlug,
string lyricSlug,
string userId)
{
LyricDetailsViewModel viewModel = new LyricDetailsViewModel();
ArtistViewModel artistViewModel = await _artistsService
.GetArtistDetailsAsync(artistSlug, userId);
viewModel.Artist = artistViewModel;
await using NpgsqlConnection connection = new NpgsqlConnection(_databaseOptions.ConnectionString);
await connection.OpenAsync();
await using NpgsqlCommand command = new NpgsqlCommand("select l.id, l.title, l.body, count(likes.lyric_id) as number_of_likes, l.is_verified, l.created_at, l.modified_at, l.is_approved from artists as a inner join lyrics as l on l.artist_id = a.id inner join artist_slugs on artist_slugs.artist_id = a.id inner join lyric_slugs as ls on ls.lyric_id = l.id left join likes on l.id = likes.lyric_id where case when @user_id <> '' then l.user_id = @user_id or a.is_approved = true else l.is_approved = true end and ls.name = @lyric_slug and artist_slugs.name = @artist_slug group by l.id order by number_of_likes;", connection);
command.Parameters.AddWithValue("@user_id", userId);
command.Parameters.AddWithValue("@artist_slug", artistSlug);
command.Parameters.AddWithValue("@lyric_slug", lyricSlug);
await using NpgsqlDataReader reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
int lyricId = Convert.ToInt32(reader[0]);
string lyricTitle = Convert.ToString(reader[1]).Trim();
string lyricBody = Convert.ToString(reader[2]).Trim();
int numberOfLikes = Convert.ToInt32(reader[3]);
bool isVerified = Convert.ToBoolean(reader[4]);
DateTime lyricCreatedAt = Convert.ToDateTime(reader[5]);
DateTime?lyricModifiedAt = reader[6] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader[6]);
bool isApproved = Convert.ToBoolean(reader[7]);
viewModel.Id = lyricId;
viewModel.Title = lyricTitle;
viewModel.Body = lyricBody;
viewModel.NumberOfLikes = numberOfLikes;
viewModel.IsVerified = isVerified;
viewModel.CreatedAt = lyricCreatedAt;
viewModel.ModifiedAt = lyricModifiedAt;
viewModel.IsApproved = isApproved;
}
viewModel.AlreadyLiked = await LyricAlreadyLikedAsync(userId, viewModel.Id);
return(viewModel);
}