public IList<IPieChartItem> GetTotals()
{
IList<IPieChartItem> retorno = new List<IPieChartItem>();
IDataQuery query = new DataQuery();
IList<IViewUserAges> collection = _repository.Find(query);
int[] usersAgeBetween15_25 = collection.Where(m => m.Age.GetValueOrDefault() >= 15 && m.Age.GetValueOrDefault() <= 25).Select(m => m.Age.GetValueOrDefault()).ToArray<int>();
int[] usersAgeBetween26_40 = collection.Where(m => m.Age.GetValueOrDefault() >= 26 && m.Age.GetValueOrDefault() <= 40).Select(m => m.Age.GetValueOrDefault()).ToArray<int>();
int[] usersAgeBetween41_60 = collection.Where(m => m.Age.GetValueOrDefault() >= 41 && m.Age.GetValueOrDefault() <= 60).Select(m => m.Age.GetValueOrDefault()).ToArray<int>();
IPieChartItem total15_25 = new PieChartItem();
total15_25.Value = usersAgeBetween15_25.Length;
total15_25.Legend = "15 - 25";
IPieChartItem total26_40 = new PieChartItem();
total26_40.Value = usersAgeBetween26_40.Length;
total26_40.Legend = "26 - 40";
IPieChartItem total41_60 = new PieChartItem();
total41_60.Value = usersAgeBetween41_60.Length;
total41_60.Legend = "41 - 60";
retorno.Add(total15_25);
retorno.Add(total26_40);
retorno.Add(total41_60);
return retorno;
}
public IList<IViewBattlResults> GetBattlsWonByProfileId( int profileId , int count = 0 )
{
IDataQuery query = new DataQuery();
query.Where = string.Format(" ViewBattlResults.profileId={0}" , profileId);
query.OrderBy = " battlId DESC ";
IList<IViewBattlResults> retorno = new List<IViewBattlResults>();
retorno = _battlRepository.Find(query);
var groupedResultByBattl = (from n in retorno
group n by n.BattlId into g
orderby g.Key
let batll = g.FirstOrDefault()
select new ViewBattlResults
{
BattlId = g.Key ,
ProfileId = batll.ProfileId ,
FirstSongId = batll.FirstSongId ,
SecondSongId = batll.SecondSongId ,
ArtistNameFirst = batll.ArtistNameFirst ,
ArtistNameSecond = batll.ArtistNameSecond ,
TotalFirst = g.Sum(m => m.TotalFirst).Value ,
TotalSecond = g.Sum(m => m.TotalSecond).Value
}).ToList<IViewBattlResults>();
IList<IViewBattlResults> result = groupedResultByBattl.OrderByDescending(o => o.BattlId).ToList();
return result;
}
public IList<IViewAlbumArtistSongTotalVotes> GetMostVotedThisMonth( int page = 0 , int rowCount = 0 )
{
DateTime firstDayOfTheMonth;
DateTime lastDayOfTheMonth;
DateUtils.GetMonth(DateTime.Now , new CultureInfo("pt-BR") , out firstDayOfTheMonth , out lastDayOfTheMonth);
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" (battlDate BETWEEN '{0}' AND '{1} 23:59:59')",firstDayOfTheMonth.ToString("yyyy-MM-dd"), lastDayOfTheMonth.ToString("yyyy-MM-dd"));
IDataQuery query = new DataQuery();
query.From = "ViewTopSongs";
query.Where = builder.ToString();
if( !String.IsNullOrEmpty(_orderBy) )
{
query.OrderBy = _orderBy;
}
query.Page = page;
query.RowCount = rowCount;
IList<IViewAlbumArtistSongTotalVotes> result = _repository.Find(query);
query.From = "ViewTopSongs";
this.Total = _repository.Count(query);
_collection = result;
return result;
}
public IList<IVote> GetVotesByUserprofile(int profileId)
{
IDataQuery query = new DataQuery();
query.Where = string.Format("profileId={0}", profileId);
IList<IVote> retorno = _voteRepository.Find(query);
return retorno;
}
private IList<IVote> CanGetVotesByWhere(string pWhere)
{
IDataQuery query = new DataQuery();
query.Where = pWhere;
IList<IVote> retorno = _voteRepository.Find(query);
return retorno;
}
public ISong GetRandomSong( int songId )
{
IDataQuery query = new DataQuery();
query.Where = string.Format("songId <> {0}" , songId);
query.OrderBy = " NEWID() ";
ISong song = _songRepository.GetTop(1 , query)[0];
return song;
}
public IList<IArtist> GetTopArtistVoted( int count )
{
IList<IArtist> collection = new List<IArtist>();
IDataQuery query = new DataQuery();
collection = _artistRepository.Find(query);
return collection;
}
public IArtist GetRandomArtist( int idArtist )
{
IDataQuery query = new DataQuery();
query.Where = string.Format("artistId <> {0}" , idArtist);
query.OrderBy = " NEWID() ";
IArtist artist = _artistRepository.GetTop(1 , query)[0];
return artist;
}
public IViewArtistContact ArtistContactGet( int id )
{
IDataQuery query = new DataQuery();
query.Where = string.Format("artistId={0}" , id);
IViewArtistContact retorno = _repository.Find(query)[0];
return retorno;
}
public IList<IVote> CanGetVotesBySong(int songId)
{
IDataQuery query = new DataQuery();
query.Where = string.Format("songId={0}", songId);
IList<IVote> retorno = CanGetVotesByWhere(query.Where);
return retorno;
}
public IList<IViewTopUsers> GetTopUsersSilver( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.Where = " (percentage BETWEEN 2 AND 5) ";
query.Page = page;
query.RowCount = rowCount;
IList<IViewTopUsers> result = GetTopUsers(query);
return result;
}
public IList<IViewTopSongs> GetTopSongsGold( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.Where = " (percentage BETWEEN 4 AND 100)";
query.Page = page;
query.RowCount = rowCount;
IList<IViewTopSongs> result = GetTopSongs(query);
_collection = result;
return result;
}
public IList<IViewTopAlbums> GetMostVotedAlbumThisWeek( int page = 0 , int rowCount = 0 )
{
DateTime firstWeekDay;
DateTime lastWeekDay;
DateUtils.GetWeek(DateTime.Now , new CultureInfo("pt-BR") , out firstWeekDay , out lastWeekDay);
IDataQuery query = new DataQuery();
query.Page = page;
query.RowCount = rowCount;
query.Where = string.Format("(ViewTopAlbums.battlDate BETWEEN '{0}' AND '{1}')" , firstWeekDay.ToString("yyyy-MM-dd") , lastWeekDay.ToString("yyyy-MM-dd"));
IList<IViewTopAlbums> retorno = GetData("viewTopAlbumsGroupedByAlbum" , query);
return retorno;
}
public IUsuario ChangePassword(int usuarioId, string currentPassword, string newPassword, string confirmPassword)
{
IDataQuery query = new DataQuery();
query.Where = string.Format("usuarioId={0}", usuarioId);
IList<IUsuario> result = _repository.Find(query);
IUsuario usuario = result[0];
string passw = PasswordHash.CreateHash(Security.ClearSQLInjection(newPassword));
usuario.Password = passw;
usuario = _repository.Update(usuario);
return usuario;
}
public IList<IViewAlbumArtistSongTotalVotesByArtist> GetTopMostVotedArtist( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.OrderBy = " Total DESC ";
query.Page = page;
query.RowCount = rowCount;
_repository.Find(query);
query.From = "ViewAlbumArtistSongTotalVotesByArtist";
this.Total = _repository.Count(query);
_collection = _repository.Collection;
return _collection;
}
public IList<IViewArtists> GetArtists( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.Page = page;
query.RowCount = rowCount;
IList<IViewArtists> retorno = _repository.Find(query);
query.From = "viewArtists";
this.Total = _repository.Count(query);
_collection = retorno;
return retorno;
}
public IList<IViewPastBattls> GetPastBattls(int page = 0, int rowCount = 0)
{
IDataQuery query = new DataQuery();
query.OrderBy = " battlId DESC ";
query.Page = page;
query.RowCount = rowCount;
_repository.Find(query);
query.From = "viewPastBattls";
this.Total = _repository.Count(query);
_collection = _repository.Collection;
return _collection;
}
public IList<IViewUserBattlResult> GetBattlsWonByProfileId( int profileId , int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.Page = page;
query.RowCount = rowCount;
query.Where = string.Format(" profileIdFirst ={0} AND totalFirst > totalSecond OR profileIdSecond = {0} AND totalFirst < totalSecond " , profileId);
var result = _battlRepository.Find(query);
query.From = "ViewUserBattlWonLostResult";
_total = _battlRepository.Count(query);
_collection = result;
return _collection;
}
public IList<IViewUserTotalVotes> GetTopMostVotesByUser( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.Page = page;
query.RowCount = rowCount;
IList<IViewUserTotalVotes> retorno = new List<IViewUserTotalVotes>();
retorno = _repository.Find(query);
query.From = "ViewUserTotalVotes";
this.Total = _repository.Count(query);
_collection = retorno;
return retorno;
}
public IList<IViewUserFavoritesSongs> GetFavoriteSongsByProfileId( int profileId , int page = 0 , int rowCount = 0 )
{
IList<IViewUserFavoritesSongs> retorno = new List<IViewUserFavoritesSongs>();
IDataQuery query = new DataQuery();
query.Page = page;
query.RowCount = rowCount;
query.Where = string.Format(" (profileId={0}) " , profileId);
var getFavorires = _repository.Find(query).ToList<IViewUserFavoritesSongs>();
IList<IViewUserFavoritesSongs> filteredResult = getFavorires;
query.From = "ViewUserFavoritesSongs";
_total = _repository.Count(query);
_collection = filteredResult;
return filteredResult;
}
public IList<IPieChartItem> GetTotals()
{
IList<IPieChartItem> retorno = new List<IPieChartItem>();
IDataQuery query = new DataQuery();
IViewGenderTotal viewGenderTotal = _repository.Find(query).FirstOrDefault();
IPieChartItem totalFemale = new PieChartItem();
totalFemale.Value = ((decimal)(viewGenderTotal.Total * viewGenderTotal.TotalFemale) / 100);
totalFemale.Legend = "Female";
IPieChartItem totalMale = new PieChartItem();
totalMale.Value = ((decimal)(viewGenderTotal.Total * viewGenderTotal.TotalMale) / 100);
totalMale.Legend = "Male";
retorno.Add(totalMale);
retorno.Add(totalFemale);
return retorno;
}
public IViewBattl CreateBattl()
{
IDataQuery query = new DataQuery();
query.Where = string.Format("ViewBattl.active=1");
IViewBattl _viewBattl = new ViewBattl();
IList<IViewBattl> col = _viewBattlRepository.Find(query);
if (col.Count > 0)
{
_viewBattl = col[0];
}
else
{
IBattl battl = GetBattlNotPlayedThisWeek();
DateTime battlTime = DateTime.Now; //GetLastBattlTime();
battl.BattlDate = battlTime;
battl.StartTime = new DateTime(battlTime.Year, battlTime.Month, battlTime.Day, battlTime.Hour, battlTime.Minute, 0);
battl.EndTime = battl.StartTime.AddSeconds(59);
battl.Active = true;
using (TransactionScope scope = new TransactionScope())
{
battl = _battlRepository.Add(battl);
query = new DataQuery();
query.Where = string.Format("ViewBattl.BattlId={0} and active=1", battl.BattlId);
col = _viewBattlRepository.Find(query);
if (col.Count > 0)
{
_viewBattl = col[0];
}
scope.Complete();
}
}
return _viewBattl;
}
public IList<IViewUserTotalVotes> GetTopMostVotesSongsByUser( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.Page = page;
query.RowCount = rowCount;
IList<IViewUserTotalVotes> retorno = new List<IViewUserTotalVotes>();
retorno = _repository.Find(query);
query.From = "ViewUserTotalVotes";
this.Total = _repository.Count(query);
var groupedResultBySongId = (from n in retorno
group n by new { n.SongId , n.UsuarioId , n.Name , n.SongName , n.Picture } into g
orderby g.Key.Name
let r = g.FirstOrDefault()
select new ViewUserTotalVotes
{
SongId = r.SongId ,
SongName = r.SongName ,
Name = r.Name ,
UsuarioId = r.UsuarioId ,
ProfileId = r.ProfileId ,
Gender = r.Gender ,
BattlId = r.BattlId ,
Picture = r.Picture ,
Total = g.Sum(m => m.Total).Value
}).ToList<IViewUserTotalVotes>();
IList<IViewUserTotalVotes> result = groupedResultBySongId.OrderByDescending(o => o.Total).ToList();
_collection = result;
return result;
}
public IList<IPieChartItem> GetTotals()
{
IList<IPieChartItem> retorno = new List<IPieChartItem>();
IDataQuery query = new DataQuery();
IList<IViewActivityByHour> collection = _repository.Find(query);
IList<IViewActivityByHour> dayList = collection.Where(m => m.Hour <= 17 && m.Hour >= 6).Select(m => m).ToList<IViewActivityByHour>();
IList<IViewActivityByHour> nightList = collection.Where(m => m.Hour >= 18 && m.Hour <= 23 || m.Hour >= 0 && m.Hour <= 5).Select(m => m).ToList<IViewActivityByHour>();
IPieChartItem totalNight = new PieChartItem();
totalNight.Value = nightList.Sum(m => m.TotalByHour).GetValueOrDefault();
totalNight.Legend = "Night";
IPieChartItem totalDay = new PieChartItem();
totalDay.Value = dayList.Sum(m => m.TotalByHour).GetValueOrDefault();
totalDay.Legend = "Day";
retorno.Add(totalNight);
retorno.Add(totalDay);
return retorno;
}
public IVote VoteOnArtist(IVote vote)
{
IVote voteAdded = new Vote();
IDataQuery query = new DataQuery();
query.Where = string.Format("profileId={0} and songId={1} and battlId={2}", vote.ProfileId, vote.SongId, vote.BattlId);
IVote result = _voteRepository.Find(query).FirstOrDefault();
if (result != null)
{
vote.VoteId = result.VoteId;
vote.Votes = result.Votes + 1;
voteAdded = _voteRepository.Update(vote);
}
else
{
voteAdded = _voteRepository.Add(vote);
}
query.Where = string.Format("songId={0} and battlId={1}", vote.SongId, vote.BattlId);
int resultTotal = CanGetVotesByWhere(query.Where).Sum(m => m.Votes);
vote.Votes = resultTotal;
return voteAdded;
}
public IList<IViewAlbumArtistSongTotalVotes> GetTopMostVotedSong( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.OrderBy = " total DESC ";
if( !String.IsNullOrEmpty(_orderBy) )
{
query.OrderBy = _orderBy;
}
query.Page = page;
query.RowCount = rowCount;
IList<IViewAlbumArtistSongTotalVotes> result = _repository.Find(query);
query.From = "ViewAlbumArtistSongTotalVotes";
this.Total = _repository.Count(query);
return result;
}
public IList<IViewAlbumArtistSongTotalVotes> GetTopMostVotedArtist( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.OrderBy = " Total DESC ";
if( !String.IsNullOrEmpty(_orderBy) )
{
query.OrderBy = _orderBy;
}
query.Page = page;
query.RowCount = rowCount;
_repository.Find(query);
this.Total = _repository.Count(query);
_collection = _repository.Collection;
return _collection;
}
public IList<IViewAlbumArtistSongTotalVotes> GetTopMostVotedAlbum( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
query.OrderBy = " total DESC ";
if( !String.IsNullOrEmpty(_orderBy) )
{
query.OrderBy = _orderBy;
}
IList<IViewAlbumArtistSongTotalVotes> retorno = new List<IViewAlbumArtistSongTotalVotes>();
retorno = _repository.Find(query);
var groupedResultByAlbum = (from n in retorno
group n by n.AlbumName into g
orderby g.Key
select new ViewAlbumArtistSongTotalVotes
{
SongId = 0 ,
AlbumName = g.Key ,
ArtistId = g.FirstOrDefault().ArtistId ,
SongName = string.Empty ,
ArtistName = g.FirstOrDefault().ArtistName ,
Total = g.Sum(m => m.Total).Value
}).ToList<IViewAlbumArtistSongTotalVotes>();
IList<IViewAlbumArtistSongTotalVotes> result = groupedResultByAlbum.OrderByDescending(o => o.Total).ToList();
return result;
}
public IList<IViewAlbumArtistSongTotalVotes> GetMostVotesToday( int page = 0 , int rowCount = 0 )
{
IDataQuery query = new DataQuery();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(" (battlDate BETWEEN '{0}' AND '{1} 23:59:59')", DateTime.Now.ToString("yyyy/MM/dd"), DateTime.Now.ToString("yyyy/MM/dd"));
query.From = "ViewTopSongs";
query.Where = builder.ToString();
if (!String.IsNullOrEmpty(_orderBy))
{
query.OrderBy = _orderBy;
}
query.Page = page;
query.RowCount = rowCount;
IList<IViewAlbumArtistSongTotalVotes> result = result = _repository.Find(query);
query.From = "ViewTopSongs";
this.Total = _repository.Count(query);
_collection = result;
return result;
}
public bool DeleteAccount(int usuarioId)
{
bool result = false;
using (TransactionScope scope = new TransactionScope())
{
IDataQuery query = new DataQuery();
query.Where = string.Format("userId={0}", usuarioId);
IList<IProfile> collection = _repositoryProfile.Find(query);
if (collection.Count > 0)
{
VoteRepository voteRepository = new VoteRepository(_dataBase);
query.Where = string.Format("profileId={0}", collection[0].ProfileId);
IList<IVote> votes = voteRepository.Find(query);
foreach (IVote vote in votes)
{
voteRepository.Remove(vote.VoteId);
}
_repositoryProfile.Remove(collection[0].ProfileId);
result = _repository.Remove(usuarioId);
}
}
return result;
}