public bool IsExisted(int zingId)
{
using (var db = new SongContext(_connectionStrings))
{
return(db.Zings.Any(s => s.ZingId == zingId));
}
}
public int SongCounts(SearchOptions option)
{
using (var db = new SongContext(_connectionStrings))
{
var count = 0;
switch (option)
{
case SearchOptions.All:
count = db.Songs.Count(s => s.Status != Status.Delete);
break;
case SearchOptions.Complete:
count = db.Songs.Count(s => s.Status == Status.Active);
break;
case SearchOptions.Incomplete:
count = db.Songs.Count(s => s.Status == Status.Incomplete);
break;
case SearchOptions.New:
count = db.Songs.Count(s => s.Status == Status.New);
break;
}
db.Dispose();
return(count);
}
}
public void UpdateSong(int songId, string title, string singer, int genderId, int vocalTrack, string alpha,
string listeningUrl, string watchVideo, string composer, int statusId, string cutline)
{
using (var db = new SongContext(_connectionStrings))
{
var so = db.Songs.SingleOrDefault(sn => sn.Id == songId);
if (so == null)
{
return;
}
so.Artist = singer;
so.Title = title;
so.Alpha = alpha;
so.ListeningUrl = listeningUrl;
so.WatchUrl = watchVideo;
so.Gender = (Gender)genderId;
so.ModifiedDateTime = DateTime.Now;
so.VocalTrack = vocalTrack;
so.Composer = composer;
so.Status = (Status)statusId;
so.Cutline = cutline;
db.SaveChanges();
}
}
public List <Zing> GetZings()
{
using (var db = new SongContext(_connectionStrings))
{
return(db.Zings.Where(z => z.Status != Status.Delete).OrderBy(s => s.Title).ToList());
}
}
public List <Song> GetFavsByUser(int userId)
{
using (var db = new SongContext(_connectionStrings))
{
var user = db.Users.SingleOrDefault(x => x.Id == userId);
var favList = new List <Song>();
if (user == null || !user.FavoriteSongs.Any())
{
return(favList);
}
foreach (var f in user.FavoriteSongs)
{
var song = db.Songs.SingleOrDefault(x => x.Id == f.SongId);
if (song != null)
{
favList.Add(song);
}
else
{
DeleteFavs(f.Id);
}
}
return(favList.OrderBy(f => f.ProperTitle).ToList());
}
}
public void UpdateSong(Song s)
{
using (var db = new SongContext(_connectionStrings))
{
var so = db.Songs.SingleOrDefault(sn => sn.Id == s.Id);
if (so != null)
{
so.Artist = s.Artist;
so.Title = s.Title;
so.MusicType = s.MusicType;
so.Production = s.Production;
so.Mode = s.Mode;
so.Gender = s.Gender;
so.Lyric = s.Lyric;
so.Cutline = s.Cutline;
so.VideoQuality = s.VideoQuality;
so.Language = s.Language;
so.Status = s.Status;
so.AlbumName = s.AlbumName;
so.ModifiedDateTime = DateTime.Now;
so.Featured = s.Featured;
so.VocalTrack = s.VocalTrack;
so.Composer = s.Composer;
db.SaveChanges();
}
}
}
public FavoriteSong GetFavSong(int favId)
{
using (var db = new SongContext(_connectionStrings))
{
return(db.FavoriteSongs.SingleOrDefault(f => f.Id == favId));
}
}
public bool AddQueue(KaraokeQueue queue)
{
using (var db = new SongContext(_connectionStrings))
{
var que = db.KaraokeQueues.SingleOrDefault(q => q.Id == queue.Id);
if (que != null)
{
que.DatePlayed = queue.DatePlayed;
que.IsCompleted = que.IsCompleted;
}
else
{
db.KaraokeQueues.Add(queue);
}
try
{
db.SaveChanges();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
}
public Zing GetZing(int id)
{
using (var db = new SongContext(_connectionStrings))
{
return(db.Zings.SingleOrDefault(s => s.Id == id && s.Status != Status.Delete));
}
}
public List <Song> GetSongsByAlpha(string alpha)
{
using (var db = new SongContext(_connectionStrings))
{
var results = db.Songs.Where(s => s.Status != Status.Delete && s.Alpha.Equals(alpha)).OrderBy(s => s.Title);
return(results.ToList());
}
}
public List <Zing> GetZingsByAlpha(string alpha, Language language)
{
using (var db = new SongContext(_connectionStrings))
{
return(db.Zings.Where(s => s.Status != Status.Delete && s.Language == language && s.Alpha.Equals(alpha))
.OrderBy(s => s.Title).ToList());
}
}
public List <User> GetLastFiveActiveUsers(string machineName)
{
using (var db = new SongContext(_connectionStrings))
{
var users = db.Users.Where(u => u.LastMachineName == machineName).OrderByDescending(u => u.ModifiedDateTime);
return(users.Count() > 5 ? users.Take(5).ToList() : users.ToList());
}
}
public void AddFavs(FavoriteSong fav)
{
using (var db = new SongContext(_connectionStrings))
{
db.FavoriteSongs.Add(fav);
db.SaveChanges();
}
}
public bool CheckQueue(string machineName, string requester, int songId)
{
using (var db = new SongContext(_connectionStrings))
{
return(db.KaraokeQueues.Any(q =>
q.MachineName == machineName && q.IsCompleted == false && q.RequestBy == requester && q.SongId == songId));
}
}
private bool IsUserNameExisted(string username)
{
using (var db = new SongContext(_connectionStrings))
{
int found = db.Users.Count(u => u.UserName == username);
return(found > 0);
}
}
public List <Song> GetSongsByTitleAndArtist(string title, string artist)
{
using (var db = new SongContext(_connectionStrings))
{
var results = db.Songs.Where(s => s.Title.Equals(title) && s.Artist.Equals(artist)).OrderBy(s => s.Title);
return(results.OrderBy(s => s.Title).ToList());
}
}
public List <Zing> GetZings(DateTime lastModifiedDate)
{
using (var db = new SongContext(_connectionStrings))
{
return
(db.Zings.Where(s => s.ModifiedDateTime >= lastModifiedDate).OrderBy(s => s.Title).ToList());
}
}
public FavoriteSong GetFavSong(int songid, int userId)
{
using (var db = new SongContext(_connectionStrings))
{
return(db.FavoriteSongs
.SingleOrDefault(f => f.UserId == userId && f.SongId == songid));
}
}
public void ClearQueue(string machineName)
{
using (var db = new SongContext(_connectionStrings))
{
var queues = from q in db.KaraokeQueues where q.MachineName == machineName select q;
db.KaraokeQueues.RemoveRange(queues);
db.SaveChanges();
}
}
public Song GetSong(int songid)
{
using (var db = new SongContext(_connectionStrings))
{
var so = db.Songs.SingleOrDefault(s => s.Id == songid && s.Status != Status.Delete);
db.Dispose();
return(so);
}
}
public List <Song> GetTop20PlayedSongs()
{
using (var db = new SongContext(_connectionStrings))
{
var results = (from so in db.Songs orderby so.PlayedCount descending select so).Take(20);
var returnedList = results.ToList();
db.Dispose();
return(returnedList);
}
}
public List <Song> GetSongs(DateTime lastModifiedDate)
{
using (var db = new SongContext(_connectionStrings))
{
var returnList =
db.Songs.Where(s => s.ModifiedDateTime >= lastModifiedDate).OrderBy(s => s.Title).ToList();
;
return(returnList);
}
}
public List <KaraokeQueue> GetQueueByRequester(string machineName, string requester)
{
using (var db = new SongContext(_connectionStrings))
{
var queues = db.KaraokeQueues
.Where(q => q.MachineName == machineName && q.IsCompleted == false && q.RequestBy == requester)
.OrderBy(q => q.Priority).ThenBy(q => q.CreateDateTime);
return(queues.ToList());
}
}
//public List<RankedSong> SearchSongs(string keywords, SearchOptions option)
//{
// return SearchSongs(keywords, option, DateTime.Now);
//}
//public List<RankedSong> SearchSongs(string keywords, SearchOptions option,DateTime cutoffDate)
//{
// keywords = keywords.Trim().Replace(" ", "+");
// using (var db = new SongContext(_connectionStrings))
// {
// var songs = new List<RankedSong>();
// switch (option)
// {
// case SearchOptions.Singer:
// var results1 = db.udf_SongsSearchArtist(keywords, cutoffDate);
// if (results1 != null)
// {
// foreach (var re in results1)
// {
// var s = new RankedSong();
// s.Title = re.Title;
// s.Id = re.SongId;
// s.Artist = re.Artist;
// s.Cutline = re.Cutline;
// s.Rank = re.RANK.Value;
// s.AlbumName = re.AlbumName;
// s.songIcon = SongImageType(re.SongId);
// songs.Add(s);
// }
// }
// var zingResults3 = db.udf_ZingSingerSearch(keywords);
// if (zingResults3 != null)
// {
// foreach (var re in zingResults3)
// {
// var s = new RankedSong();
// s.Title = re.Title;
// s.Id = re.Id;
// s.Artist = re.singer;
// s.Cutline = re.Cutline;
// s.Rank = re.RANK.Value;
// s.songIcon = SongImageType(re.Id);
// songs.Add(s);
// }
// }
// break;
// case SearchOptions.Lyric:
// var results2 = db.udf_SongsSearchLyrics(keywords, cutoffDate);
// if (results2 != null)
// {
// foreach (var re in results2)
// {
// var s = new RankedSong();
// s.Title = re.Title;
// s.Id = re.SongId;
// s.Artist = re.Artist;
// s.Cutline = re.Cutline;
// s.Rank = re.RANK.Value;
// s.AlbumName = re.AlbumName;
// s.songIcon = SongImageType(re.SongId);
// songs.Add(s);
// }
// }
// var zingResults2 = db.udf_ZingLyricSearch(keywords);
// if (zingResults2 != null)
// {
// foreach (var re in zingResults2)
// {
// var s = new RankedSong();
// s.Title = re.Title;
// s.Id = re.Id;
// s.Artist = re.singer;
// s.Cutline = re.Cutline;
// s.Rank = re.RANK.Value;
// s.songIcon = SongImageType(re.Id);
// songs.Add(s);
// }
// }
// break;
// default:
// var results = db.udf_SongsSearch(keywords, cutoffDate);
// if (results != null)
// {
// foreach (var re in results)
// {
// var s = new RankedSong();
// s.Title = re.Title;
// s.Id = re.SongId;
// s.Artist = re.Artist;
// s.Cutline = re.Cutline;
// s.Rank = re.RANK.Value;
// s.AlbumName = re.AlbumName;
// s.songIcon = SongImageType(re.SongId);
// songs.Add(s);
// }
// }
// var zingResults = db.udf_ZingSearch(keywords);
// if (zingResults != null)
// {
// foreach (var re in zingResults)
// {
// var s = new RankedSong();
// s.Title = re.Title;
// s.Id = re.Id;
// s.Artist = re.singer;
// s.Cutline = re.Cutline;
// s.songIcon = SongImageType(re.Id);
// s.Rank = re.RANK.Value;
// songs.Add(s);
// }
// }
// break;
// }
// return songs.OrderByDescending(s => s.Rank).ToList();
// }
//}
public List <Song> GetFeatureSongs()
{
using (var db = new SongContext(_connectionStrings))
{
var results = from so in db.Songs
where so.Status != Status.Delete && so.Featured
orderby so.Id descending
select so;
return(results.ToList());
}
}
public void PlayedCounter(int songid)
{
using (var db = new SongContext(_connectionStrings))
{
Song s = db.Songs.SingleOrDefault(so => so.Id == songid);
if (s != null)
{
s.PlayedCount += 1;
db.SaveChanges();
}
}
}
public void DeleteQueue(int queueId)
{
using (var db = new SongContext(_connectionStrings))
{
var queue = db.KaraokeQueues.SingleOrDefault(q => q.Id == queueId);
if (queue != null)
{
db.KaraokeQueues.Remove(queue);
db.SaveChanges();
}
}
}
public void DeleteFavs(int favId)
{
using (var db = new SongContext(_connectionStrings))
{
var fav = db.FavoriteSongs.SingleOrDefault(f => f.Id == favId);
if (fav != null)
{
db.FavoriteSongs.Remove(fav);
db.SaveChanges();
}
}
}
public int AddSong(Song s)
{
s.ModifiedDateTime = DateTime.Now;
s.CreateDateTime = DateTime.Now;
using (var db = new SongContext(_connectionStrings))
{
db.Songs.Add(s);
db.SaveChanges();
}
return(s.Id);
}
public void Delete(int id)
{
using (var db = new SongContext(_connectionStrings))
{
var so = db.Zings.SingleOrDefault(s => s.Id == id && s.Status != Status.Delete);
if (so != null)
{
so.Status = Status.Delete;
so.ModifiedDateTime = DateTime.Now;
db.SaveChanges();
}
}
}
public List <Song> GetSongsInQueue(string machineName)
{
using (var db = new SongContext(_connectionStrings))
{
var queues = from so in db.Songs
join que in db.KaraokeQueues on so.Id equals que.SongId
where que.MachineName == machineName
select so;
return(queues.ToList());
}
}