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); }