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