public SongController()
{
_db = new MusicPortalDbContext();
_mngr = new SongManager(_db);
_helper = new HttpResponseHelper<SongVM>(this);
_heartpler = new HttpResponseHelper<HeartedSongVM>(this);
}
public IQueryable<PlaylistVM> getPlaylistsFull(string user_id)
{
SongManager sm = new SongManager(_db);
var playlists = from pl in _db.Playlists.Where(p => p.OwnerId.Equals(user_id))
join pls in _db.PlaylistSongs.Include(pp => pp.Song) on pl.Id equals pls.PlaylistId into pls
from plsg in pls.DefaultIfEmpty()
group plsg by pl into plays
select new PlaylistVM
{
Id = plays.Key.Id,
Title = plays.Key.Title,
Songs = (from song in plays.Select(p => p.Song)
join sa in _db.AuthorSongs on song.Id equals sa.SongId
join authors in _db.Authors on sa.AuthorId equals authors.Id into auths
join hs in _db.HeartedSongs on song.Id equals hs.SongId into hs
from hearted in hs.DefaultIfEmpty()
where hearted.UserId.Equals(user_id) || hearted == null
select new HeartedSongVM
{
SongId = song.Id,
Name = song.Name,
Link = song.Link,
IsHearted = hearted == null ? false : hearted.IsHearted,
Authors = auths.Select(a => new AuthorVM { Id = a.Id, Name = a.Name })
}) //sm.MakeHeartedSong(plays.Select(p => p.Song).AsQueryable(), user_id)
};
return playlists;
}
public List<HeartedSongVM> Get(string q, string s)
{
SongManager sm = new SongManager(_db);
string userId = Microsoft.AspNet.Identity.IdentityExtensions.GetUserId(RequestContext.Principal.Identity);
//"SELECT * FROM[aspnet - MusicPortal].[dbo].[Songs] ORDER BY[aspnet - MusicPortal].[dbo].[Levenshtein]([Name], @q, 30) / CAST(LEN([Name]) AS decimal)"
//SELECT * FROM [dbo].[Songs] ORDER BY [dbo].[Levenshtein]([Name], @q, 30) / CAST(LEN([Name]) AS decimal)
var query = "SELECT[Id] FROM[dbo].[Songs] ORDER BY[dbo].[Levenshtein]([Name], @q, 30) / CAST(LEN([Name]) AS decimal) OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY";
var b = _db.Database.SqlQuery<long>(s, new SqlParameter("@q", q)).ToList();
var c = _db.Songs.Where(so => b.Contains(so.Id));
return sm.MakeHeartedSong(c, userId).ToList().OrderBy(so => b.FindIndex(bo => bo == so.SongId)).ToList();
}
public PlaylistVM GetPlaylistById(long playlistId, string user_id, bool check_if_owner = false)
{
var playlist = _db.Playlists.Where(pl => pl.Id == playlistId).FirstOrDefault();
if (playlist == null)
throw new Exception("Playlist with this ID doesn't exist");
if (user_id != null && check_if_owner && !playlist.OwnerId.Equals(user_id))
throw new Exception("This user isn't owner of this playlist");
SongManager sm = new SongManager(_db);
var songs_query = _db.PlaylistSongs.Where(pls => pls.PlaylistId == playlistId).Join(_db.Songs, p => p.SongId, s => s.Id, (p, s) => s);
var songs = sm.MakeHeartedSong(songs_query, user_id);
return new PlaylistVM
{
Id = playlist.Id,
Title = playlist.Title,
Songs = songs.ToList()
};
}