public List <AnnualReport> ReportAnnualBySongsCount(int channelid, DateTime startdate, DateTime enddate)
{
List <AnnualReport> lstannualreport = new List <AnnualReport>();
var q = from a in db.Annuals
join c in db.Channels
on a.ChannelID equals c.ChannelID
where a.ChannelID == channelid && a.StartDate >= startdate && a.EndDate <= enddate
group a by new { a.StartDate, a.EndDate, a.ChannelID, c.ChannelName, a.CurrencyType } into g
select new { g.Key.StartDate, g.Key.EndDate, g.Key.ChannelID, g.Key.ChannelName, g.Key.CurrencyType, price = g.Sum(p => p.Price), count = g.Count() };
foreach (var s in q)
{
AnnualReport annualreport = new AnnualReport();
annualreport.StartDate = s.StartDate.Value.Date;
annualreport.EndDate = s.EndDate.Value.Date;
annualreport.TotalAmount = Convert.ToInt32(s.price);
annualreport.CurrencyType = s.CurrencyType;
annualreport.SongCount = s.count;
annualreport.ChannelName = s.ChannelName;
lstannualreport.Add(annualreport);
}
return(lstannualreport);
}
public List <AnnualReport> ReportAnnualBySongDetail(int channelid, DateTime startdate, DateTime enddate)
{
List <AnnualReport> lstannualreport = new List <AnnualReport>();
var q = from a in db.Annuals
join c in db.Channels
on a.ChannelID equals c.ChannelID
join s in db.Songs
on a.SongID equals s.SongID
join p in db.Producers
on s.ProducerID equals p.ProducerID
join st in db.Studios
on s.StudioID equals st.StudioID
join ba in db.Bands
on s.BandID equals ba.BandID
where a.ChannelID == channelid && a.StartDate >= startdate && a.EndDate <= enddate
group a by new { a.ChannelID, c.ChannelName, a.CurrencyType, a.SongID, ba.BandName, st.StudioName, p.ProducerName, s.Length, s.SongTitle } into g
select new { g.Key.ChannelID, g.Key.CurrencyType, g.Key.ChannelName, price = g.Sum(p => p.Price), count = g.Count(), g.Key.SongID, g.Key.BandName, g.Key.StudioName, g.Key.ProducerName, g.Key.Length, g.Key.SongTitle };
foreach (var s in q)
{
AnnualReport annualreport = new AnnualReport();
//annualreport.StartDate = s.StartDate.Value.Date;
//annualreport.EndDate = s.EndDate.Value.Date;
annualreport.TotalAmount = Convert.ToInt32(s.price);
annualreport.CurrencyType = s.CurrencyType;
annualreport.SongCount = s.count;
annualreport.ChannelID = s.ChannelID;
annualreport.ChannelName = s.ChannelName;
annualreport.SongID = s.SongID;
annualreport.Studio = s.StudioName;
annualreport.bandname = s.BandName;
annualreport.Producer = s.ProducerName;
annualreport.Duration = s.Length;
annualreport.SongTitle = s.SongTitle;
foreach (var l in songrep.GetArtistNameBySongID(annualreport.SongID))
{
if (annualreport.Artist == null)
{
annualreport.Artist = l.ArtistMyanmarName;
}
else
{
annualreport.Artist += "-" + l.ArtistMyanmarName;
}
}
foreach (var l in songrep.GetComposerNameBySongID(annualreport.SongID))
{
if (annualreport.Composer == null)
{
annualreport.Composer = l.ComposerName;
}
else
{
annualreport.Composer += "-" + l.ComposerName;
}
}
lstannualreport.Add(annualreport);
}
return(lstannualreport);
}