public IEnumerable <UserBulliten> GetUserArtistBullitens(string email)
{
var validDate = DateTime.Now.AddDays(-1);
var userId = GetUserId(email);
var userArtistBullitens = new List <UserBulliten>();
try
{
using (var context = new sparrow_dbEntities())
{
var user = context.SPRW_USER.FirstOrDefault(i => i.USER_ID == userId);
if (user != null)
{
var bullitens =
user.SPRW_ARTIST.Select(
i => i.ARTIST_BLOG.FirstOrDefault(j => j.LAST_MAINT_TIME >= validDate));
foreach (var bulliten in bullitens)
{
var model = new UserBulliten
{
AristName = bulliten.SPRW_ARTIST.NAME,
Bulliten = bulliten.BLOG
};
userArtistBullitens.Add(model);
}
}
}
return(userArtistBullitens);
}
catch (Exception e)
{
throw;
}
}
public IEnumerable <AlbumModel> SearchAlbums(string name)
{
var albums = new List <AlbumModel>();
try
{
using (var context = new sparrow_dbEntities())
{
var albumList = context.SPRW_ALBUM.Where(i => i.NAME.StartsWith(name)).Take(3);
foreach (var album in albumList)
{
var selectedAlbum = new AlbumModel
{
// Artist = new ArtistModel
// {
// AristName = album.SPRW_ARTIST.NAME,
// ArtistId = album.ARTIST_ID,
// Description = album.SPRW_ARTIST.DESCRP
// },
AlbumName = album.NAME,
AlbumId = album.ARTIST_ID
};
albums.Add(selectedAlbum);
}
}
}
catch (Exception e)
{
}
return(albums);
}
public int CreateFilter(CreateFilterModel model)
{
var userId = GetUserId(model.UserEmail);
var filterId = -1;
using (var context = new sparrow_dbEntities())
{
var user = context.SPRW_USER.FirstOrDefault(i => i.USER_ID == userId);
if (user != null)
{
var filter = new SPRW_USER_FILTER
{
ACT_IND = true,
FILTER = model.Filter,
LAST_MAINT_TIME = DateTime.Now,
LAST_MAINT_USER = model.UserEmail,
NAME = model.Name,
USER_ID = userId
};
context.SPRW_USER_FILTER.Add(filter);
context.SaveChanges();
filterId = filter.FILTER_ID;
}
}
return(filterId);
}
public SPRW_USER GetUser(string userEmail)
{
var user = new SPRW_USER();
try
{
using (var context = new sparrow_dbEntities())
{
var selectedUser = context.SPRW_USER.FirstOrDefault(i => i.EMAIL.ToLower().Equals(userEmail.ToLower()));
if (selectedUser != null)
{
user.ACT_IND = selectedUser.ACT_IND;
user.CC = selectedUser.CC;
user.EMAIL = selectedUser.EMAIL;
user.FIRST_NAME = selectedUser.FIRST_NAME;
user.LAST_MAINT_TIME = selectedUser.LAST_MAINT_TIME;
user.LAST_MAINT_USER_ID = selectedUser.LAST_MAINT_USER_ID;
user.LAST_NAME = selectedUser.LAST_NAME;
user.PASSWORD = selectedUser.PASSWORD;
user.SALT = selectedUser.SALT;
}
}
}
catch (Exception e)
{
throw e;
}
return(user);
}
public void CreateArtistAssociation(string email, int artistId)
{
try
{
using (var context = new sparrow_dbEntities())
{
var userId = GetUserId(email);
var artistMember = new SPRW_ARTIST_MEMBER
{
ROLE_ID = 1,
ACT_IND = true,
ARTIST_ID = artistId,
LAST_MAINT_TIME = DateTime.Now,
LAST_MAINT_USER_ID = userId,
USER_ID = userId
};
context.SPRW_ARTIST_MEMBER.Add(artistMember);
context.SaveChanges();
}
}
catch (Exception e)
{
throw e;
}
}
public IEnumerable <MarketSearchModel> SearchMarkbetByName(string name)
{
var marketList = new List <MarketSearchModel>();
try
{
using (var context = new sparrow_dbEntities())
{
var markets = context.SPRW_MARKET_LOCATIONS.Where(i => i.CITY.Contains(name));
foreach (var market in markets)
{
var marketModel = new MarketSearchModel
{
Longitude = (double)market.LONG_COORDS,
Latitude = (double)market.LAT_COORDS,
Name = market.CITY,
Zip = market.ZIP
};
marketList.Add(marketModel);
}
}
}
catch (Exception e)
{
}
return(marketList);
}
public IEnumerable <ArtistModel> GetArtists(string email)
{
var id = GetUserId(email);
var artistList = new List <ArtistModel>();
try
{
using (var context = new sparrow_dbEntities())
{
var artists = context.SPRW_ARTIST_MEMBER.Where(i => i.USER_ID == id).Select(i => i.SPRW_ARTIST).ToList();
foreach (var artist in artists)
{
var artistItem = new ArtistModel();
artistItem.ArtistId = artist.ARTIST_ID;
artistItem.ArtistName = artist.NAME;
artistList.Add(artistItem);
}
}
}
catch (Exception e)
{
throw e;
}
return(artistList);
}
public IEnumerable <ArtistModel> SearchArtists(string name)
{
var artists = new List <ArtistModel>();
try
{
using (var context = new sparrow_dbEntities())
{
var artistList = context.SPRW_ARTIST.Where(i => i.NAME.Contains(name)).Take(5);
foreach (var artist in artistList)
{
var selectedArtist = new ArtistModel
{
ArtistName = artist.NAME,
ArtistId = artist.ARTIST_ID
};
artists.Add(selectedArtist);
}
}
}
catch (Exception e)
{
}
return(artists);
}
public void UpdateArtist(ModifyArtistModel model)
{
try
{
using (var context = new sparrow_dbEntities())
{
var userId = GetUserId(model.UserEmail);
var artist = context.SPRW_ARTIST.FirstOrDefault(i => i.ARTIST_ID == model.ArtistId);
if (artist != null)
{
artist.ACT_IND = model.Active;
artist.NAME = model.Name;
artist.DESCRP = model.Description;
artist.LAST_MAINT_TIME = DateTime.Now;
artist.LAST_MAINT_USER_ID = model.UserEmail;
}
context.SaveChanges();
}
}
catch (Exception e)
{
throw e;
}
}
public int CreateAlbum(CreateAlbumModel model)
{
try
{
using (var context = new sparrow_dbEntities())
{
var album = new SPRW_ALBUM()
{
ARTIST_ID = model.ArtistId,
RELEASE_DATE = model.ReleaseDate,
NAME = model.AlbumName,
DESCRP = model.Description,
LAST_MAINT_USER_ID = "",
LAST_MAINT_TIME = DateTime.Now
};
context.SPRW_ALBUM.Add(album);
context.SaveChanges();
return(album.ALBUM_ID);
}
}
catch (Exception e)
{
throw e;
}
}
public void AddTrackPopularityLike(PopularityModel model)
{
var success = true;
try
{
var userID = GetUserId(model.UserEmail);
log.Info("method : AddTrackPopularityLike | action : starting db connection | message : liking track " + model.TrackId);
using (var context = new sparrow_dbEntities())
{
var like = new SPRW_TRACK_POPULAR_LIKE()
{
DISLIKE_DATE = DateTime.Now,
TRACK_ID = model.TrackId,
USER_ID = userID
};
context.SPRW_TRACK_POPULAR_LIKE.Add(like);
context.SaveChanges();
}
UpdatePopIndex(model.TrackId);
}
catch (Exception e)
{
log.Error("method : AddTrackPopularityLike | exception : " + e.Message);
}
}
public int CreateArtist(CreateArtistModel model)
{
try
{
using (var context = new sparrow_dbEntities())
{
var userId = GetUserId(model.UserEmail);
var artist = new SPRW_ARTIST()
{
ACT_IND = true,
NAME = model.Name,
DESCRP = model.Description,
LAST_MAINT_TIME = DateTime.Now,
LAST_MAINT_USER_ID = model.UserEmail
};
var sprwMarketLocations = context.SPRW_MARKET_LOCATIONS.FirstOrDefault(i => i.ZIP == model.Zip);
if (sprwMarketLocations != null)
{
artist.SPRW_MARKET_LOCATIONS = sprwMarketLocations;
}
context.SPRW_ARTIST.Add(artist);
context.SaveChanges();
return(artist.ARTIST_ID);
}
}
catch (Exception e)
{
throw e;
}
}
private void UpdatePopIndex(int trackId)
{
using (var context = new sparrow_dbEntities())
{
var track = context.SPRW_TRACK.FirstOrDefault(i => i.TRACK_ID == trackId);
if (track != null)
{
var artist = track.SPRW_ARTIST;
var diffDays = (DateTime.Now - track.LAST_MAINT_TIME).Days;
var popCount = (track.SPRW_TRACK_POPULAR_LIKE.Count * 2) + track.SPRW_TRACK_POPULAR_PLAY_THROUGH.Count +
track.SPRW_TRACK_POPULAR_SELECT.Count;
popCount -= track.SPRW_TRACK_POPULAR_SKIPS.Count;
var popIndex = popCount / Math.Pow(diffDays, 1.8);
track.POP_INDEX = (decimal)popIndex;
var maxPopIndex = artist.SPRW_TRACK.Max(i => i.POP_INDEX);
if (maxPopIndex != null)
{
artist.POP_INDEX = (decimal)maxPopIndex;
}
}
context.SaveChanges();
}
}
public void UpdateEvent(ModifyEventModel model)
{
var userId = GetUserId(model.UserEmail);
try
{
using (var context = new sparrow_dbEntities())
{
var eventModel = context.SPRW_ARTIST_EVENT.FirstOrDefault(i => i.EVENT_ID == model.EventId);
if (eventModel != null)
{
eventModel.ADDRESS = model.Address;
eventModel.ARTIST_ID = model.ArtistId;
eventModel.CITY = model.City;
eventModel.DESCRP = model.Description;
eventModel.EVENT_DATE = model.EventDate;
eventModel.LAST_MAINT_TIME = DateTime.Now;
eventModel.LAST_MAINT_USER_ID = userId;
eventModel.NAME = model.Name;
eventModel.STATE = model.State;
eventModel.URL = model.Url;
}
context.SaveChanges();
}
}
catch (Exception e)
{
throw e;
}
}
public IEnumerable <UserModel> SearchUsers(string email)
{
var userList = new List <UserModel>();
try
{
using (var context = new sparrow_dbEntities())
{
var users = context.SPRW_USER.Where(i => i.EMAIL.Contains(email));
foreach (var user in users)
{
var model = new UserModel()
{
UserEmail = user.EMAIL,
UserId = user.USER_ID
};
userList.Add(model);
}
}
return(userList);
}
catch (Exception e)
{
throw e;
}
}
public void UpdateTrack(ModifyTrackModel model)
{
try
{
log.Info("method : CreateTrack | action : starting db connections");
using (var context = new sparrow_dbEntities())
{
var track = context.SPRW_TRACK.FirstOrDefault(i => i.TRACK_ID == model.TrackId);
if (track != null)
{
track.ARTIST_ID = model.ArtistId;
track.ALBUM_ID = model.AlbumId;
track.ACT_IND = true;
if (model.ReleaseDate != null)
{
track.RELEASE_DATE = (DateTime)model.ReleaseDate;
}
track.NAME = model.TrackName;
track.DESCRP = model.Description;
track.LAST_MAINT_TIME = DateTime.Now;
track.LAST_MAINT_USER_ID = model.UserEmail;
}
context.SaveChanges();
}
}
catch (Exception e)
{
throw e;
}
}
public int CreateEvent(CreateEventModel model)
{
var userId = GetUserId(model.UserEmail);
try
{
using (var context = new sparrow_dbEntities())
{
var eventModel = new SPRW_ARTIST_EVENT()
{
ACT_IND = true,
ADDRESS = model.Address,
ARTIST_ID = model.ArtistId,
CITY = model.City,
DESCRP = model.Description,
EVENT_DATE = model.EventDate,
LAST_MAINT_TIME = DateTime.Now,
LAST_MAINT_USER_ID = userId,
NAME = model.Name,
STATE = model.State,
URL = model.Url
};
context.SPRW_ARTIST_EVENT.Add(eventModel);
context.SaveChanges();
return(eventModel.EVENT_ID);
}
}
catch (Exception e)
{
throw e;
}
}
public void AddTrackPopularitySelect(PopularityModel model)
{
var success = true;
try
{
var userId = GetUserId(model.UserEmail);
using (var context = new sparrow_dbEntities())
{
var select = new SPRW_TRACK_POPULAR_SELECT()
{
DISLIKE_DATE = DateTime.Now,
TRACK_ID = model.TrackId,
USER_ID = userId
};
context.SPRW_TRACK_POPULAR_SELECT.Add(select);
context.SaveChanges();
}
UpdatePopIndex(model.TrackId);
}
catch (Exception e)
{
log.Error("method : AddTrackPopularityPlayThrough | exception : " + e.Message);
}
}
public AlbumModel GetAlbum(int albumId)
{
try
{
var albumModel = new AlbumModel();
using (var context = new sparrow_dbEntities())
{
var album = context.SPRW_ALBUM.FirstOrDefault(i => i.ALBUM_ID == albumId);
if (album != null)
{
albumModel.AlbumId = album.ALBUM_ID;
albumModel.AlbumName = album.NAME;
var tracks = new List <TrackModel>();
foreach (var track in album.SPRW_TRACK)
{
var trackModel = new TrackModel
{
TrackId = track.TRACK_ID,
TrackName = track.NAME
};
tracks.Add(trackModel);
}
albumModel.Tracks = tracks;
}
}
return(albumModel);
}
catch (Exception e)
{
throw e;
}
}
public int CreateUser(CreateUserModel model, int salt)
{
try
{
int id = -1;
using (var context = new sparrow_dbEntities())
{
var user = new SPRW_USER
{
FIRST_NAME = model.FirstName,
LAST_NAME = model.LastName,
EMAIL = model.Email,
SALT = salt,
CC = "",
PASSWORD = model.Password,
LAST_MAINT_TIME = DateTime.Now,
LAST_MAINT_USER_ID = model.Email
};
context.SPRW_USER.Add(user);
context.SaveChanges();
id = user.USER_ID;
}
return(id);
}
catch (Exception e)
{
throw e;
}
}
public List <PlaylistModel> GetPlaylist(int page)
{
var playlist = new List <PlaylistModel>();
try
{
using (var context = new sparrow_dbEntities())
{
var tracks =
context.SPRW_TRACK.OrderByDescending(i => i.SPRW_TRACK_POPULAR_LIKE.Count).ThenBy(i => i.LAST_MAINT_TIME).Take(100);
foreach (var track in tracks)
{
if (playlist.Exists(i => i.ArtistName == track.SPRW_ARTIST.NAME))
{
var playlistItem = new PlaylistTrack
{
AlbumId = track.ALBUM_ID,
AlbumName = track.SPRW_ALBUM != null ? track.SPRW_ALBUM.NAME : null,
TrackId = track.TRACK_ID,
PopIndex =
track.SPRW_TRACK_POPULAR_LIKE.Count(i => i.DISLIKE_DATE > DateTime.Now.AddMonths(-6))
};
var firstOrDefault = playlist.FirstOrDefault(i => i.ArtistName == track.SPRW_ARTIST.NAME);
if (firstOrDefault != null)
{
firstOrDefault.Tracks.Add(playlistItem);
}
}
else
{
playlist.Add(new PlaylistModel
{
ArtistId = track.ARTIST_ID,
ArtistName = track.SPRW_ARTIST.NAME,
Genres = track.SPRW_ARTIST.SPRW_GENRE.Select(i => i.GENRE).ToList(),
Tracks = new List <PlaylistTrack>
{
new PlaylistTrack
{
AlbumId = track.ALBUM_ID,
AlbumName = track.SPRW_ALBUM != null ? track.SPRW_ALBUM.NAME : null,
TrackId = track.TRACK_ID,
PopIndex =
track.SPRW_TRACK_POPULAR_LIKE.Count(i => i.DISLIKE_DATE > DateTime.Now.AddMonths(-6))
}
}
});
}
}
}
return(playlist);
}
catch (Exception e)
{
throw e;
}
}
public int CreateTrack(CreateTrackModel model)
{
var trackId = -1;
DateTime relaseDate;
if (model.ReleaseDate == null)
{
relaseDate = DateTime.Now;
}
else
{
relaseDate = (DateTime)model.ReleaseDate;
}
try
{
log.Info("method : CreateTrack | action : starting db connections");
using (var context = new sparrow_dbEntities())
{
var album = new SPRW_TRACK()
{
ARTIST_ID = model.ArtistId,
ALBUM_ID = model.AlbumId,
ACT_IND = false,
IS_REJECTED = false,
RELEASE_DATE = relaseDate,
NAME = model.TrackName,
DESCRP = model.Description,
LAST_MAINT_TIME = DateTime.Now,
LAST_MAINT_USER_ID = model.UserEmail
};
context.SPRW_TRACK.Add(album);
context.SaveChanges();
trackId = album.TRACK_ID;
var trackQueue = new SPRW_TRACK_QUEUE
{
TRACK_ID = trackId,
DATE_QUEUED = DateTime.Now,
IS_REVIEWING = false,
TRACK_PATH = ""
};
context.SPRW_TRACK_QUEUE.Add(trackQueue);
context.SaveChanges();
}
}
catch (Exception e)
{
throw e;
}
return(trackId);
}
public void RemoveFilter(int filterId)
{
using (var context = new sparrow_dbEntities())
{
var filter = context.SPRW_USER_FILTER.FirstOrDefault(i => i.FILTER_ID == filterId);
if (filter != null)
{
filter.ACT_IND = false;
}
context.SaveChanges();
}
}
public void CreateArtistSetting(int artistId, Dictionary <string, string> artistSettings)
{
using (var context = new sparrow_dbEntities())
{
var settings =
artistSettings.Select(
i => new SPRW_ARTIST_SETTING {
ARTIST_ID = artistId, KEY = i.Key, VALUE = i.Value
});
context.SPRW_ARTIST_SETTING.AddRange(settings);
context.SaveChanges();
}
}
public void CreatePlaylistPage(int playlistId, int pageNum, string playlistItem)
{
using (var context = new sparrow_dbEntities())
{
var page = new SPRW_PLAYLIST_PAGES
{
PLAYLIST_ID = playlistId,
PAGE_NUM = pageNum,
PLAYLIST = playlistItem
};
context.SPRW_PLAYLIST_PAGES.Add(page);
context.SaveChanges();
}
}
public IEnumerable <PlaylistTrack> GetPlaylistByHackerNews()
{
var today = DateTime.Now;
var playlistModel = new List <PlaylistTrack>();
using (var context = new sparrow_dbEntities())
{
var artists = context.SPRW_ARTIST.OrderByDescending(i => i.POP_INDEX);
foreach (var artist in artists)
{
var playlistItem = new PlaylistTrack
{
ArtistId = artist.ARTIST_ID,
ArtistName = artist.NAME
};
var sprwArtistSettings = artist.SPRW_ARTIST_SETTINGS.FirstOrDefault();
if (sprwArtistSettings != null)
{
playlistItem.Setting = sprwArtistSettings.SETTING;
}
playlistItem.Settings = artist.SPRW_ARTIST_SETTING.ToDictionary(i => i.KEY, i => i.VALUE);
var maxPopIndex = artist.SPRW_TRACK.Max(j => j.POP_INDEX);
var popTrack =
artist.SPRW_TRACK.FirstOrDefault(i => i.POP_INDEX == artist.SPRW_TRACK.Max(j => j.POP_INDEX));
if (popTrack != null)
{
playlistItem.AlbumId = popTrack.ALBUM_ID;
playlistItem.AlbumName = popTrack.SPRW_ALBUM.NAME;
playlistItem.Genres = artist.SPRW_GENRE.Select(i => i.GENRE_ID).ToList();
playlistItem.PopIndex = (double)artist.POP_INDEX;
playlistItem.TrackId = popTrack.TRACK_ID;
playlistItem.TrackName = popTrack.NAME;
if (artist.SPRW_MARKET_LOCATIONS != null)
{
playlistItem.Market = new MarketModel
{
Latitude = (double)artist.SPRW_MARKET_LOCATIONS.LAT_COORDS,
Longitude = (double)artist.SPRW_MARKET_LOCATIONS.LONG_COORDS,
Zip = artist.SPRW_MARKET_LOCATIONS.ZIP
};
}
playlistItem.ReleaseDate = popTrack.RELEASE_DATE;
playlistModel.Add(playlistItem);
}
}
}
return(playlistModel);
}
public FollowedArtistModel GetUsersArtists(string email)
{
using (var context = new sparrow_dbEntities())
{
var user = context.SPRW_USER.FirstOrDefault(i => i.EMAIL == email);
var artists = user.SPRW_ARTIST.ToList();
var userArtists = new FollowedArtistModel
{
ArtistIds = new List <int>(),
LikedTrackIds = context.SPRW_TRACK_POPULAR_LIKE.Where(i => i.USER_ID == user.USER_ID).Select(i => i.TRACK_ID).ToList(),
Events = new List <EventModel>(),
Bulliten = new List <BullitenModel>()
};
foreach (var artist in artists)
{
userArtists.ArtistIds.Add(artist.ARTIST_ID);
var bullitens = artist.ARTIST_BLOG.ToList();
foreach (var bulliten in bullitens)
{
var userBulliten = new BullitenModel
{
ArtistId = artist.ARTIST_ID,
AristName = artist.NAME,
Bulliten = bulliten.BLOG
};
userArtists.Bulliten.Add(userBulliten);
}
var evts = artist.SPRW_ARTIST_EVENT.OrderByDescending(i => i.EVENT_DATE).ToList();
foreach (var evt in evts)
{
var userEvent = new EventModel
{
Address = evt.ADDRESS,
ArtistId = artist.ARTIST_ID,
ArtistName = artist.NAME,
Description = evt.DESCRP,
EventDate = evt.EVENT_DATE,
City = evt.CITY,
State = evt.STATE,
Url = evt.URL,
Name = evt.NAME
};
userArtists.Events.Add(userEvent);
}
}
return(userArtists);
}
}
public void UpdateSalt(int salt, string userEmail, string passwordHash)
{
var id = GetUserId(userEmail);
using (var context = new sparrow_dbEntities())
{
var selectedUser = context.SPRW_USER.FirstOrDefault(i => i.USER_ID == id);
if (selectedUser != null)
{
selectedUser.SALT = salt;
selectedUser.PASSWORD = passwordHash;
}
context.SaveChanges();
}
}
public int CreatePlaylist()
{
var id = -1;
using (var context = new sparrow_dbEntities())
{
var playlist = new sprw_playlist {
IS_ACTIVE = true, CREATED = DateTime.Now
};
context.sprw_playlist.Add(playlist);
context.SaveChanges();
id = (int)playlist.PLAYLIST_ID;
}
return(id);
}
public CommonModel GetCommonData()
{
var commonData = new CommonModel();
commonData.Genres = (List <GenreModel>)GetGenreData();
using (var context = new sparrow_dbEntities())
{
commonData.Max = (double)context.SPRW_ARTIST.Max(i => i.POP_INDEX);
commonData.Min = (double)context.SPRW_ARTIST.Min(i => i.POP_INDEX);
commonData.StartDate = context.SPRW_ALBUM.Min(i => i.RELEASE_DATE);
commonData.EndDate = context.SPRW_ALBUM.Max(i => i.RELEASE_DATE);
}
return(commonData);
}
