public List <FrequencyReport> ReportFrequencyBySongsCount(int channelid, DateTime startdate, DateTime enddate, string programid) { List <FrequencyReport> lstfrequencyreport = new List <FrequencyReport>(); var q = from f in db.Frequencies join c in db.Channels on f.ChannelID equals c.ChannelID join p in db.Programs on f.ProgramID equals p.ProgramID where f.ChannelID == channelid && f.Date >= startdate && f.Date <= enddate group f by new { f.Date, f.ChannelID, c.ChannelName, p.ProgramID, p.Name, f.CurrencyType } into g select new { g.Key.Date, g.Key.ChannelID, g.Key.ChannelName, g.Key.ProgramID, g.Key.Name, g.Key.CurrencyType, price = g.Sum(p => p.Price), count = g.Count() }; if (programid != null) { q = q.Where(s => s.ProgramID == programid); } foreach (var s in q) { FrequencyReport freqreport = new FrequencyReport(); freqreport.FrequencyDate = s.Date.Date; freqreport.TotalAmount = Convert.ToInt32(s.price); freqreport.CurrencyType = s.CurrencyType; freqreport.SongCount = s.count; freqreport.programname = s.Name; freqreport.ChannelName = s.ChannelName; lstfrequencyreport.Add(freqreport); } return(lstfrequencyreport); }
//public List<FrequencyReport> ReportFrequencyBySongDetail(int channelid, DateTime startdate, DateTime enddate) //{ // List<FrequencyReport> lstfrequencyReport = new List<FrequencyReport>(); // var q = from f in db.Frequencies // join c in db.Channels // on f.ChannelID equals c.ChannelID // join pro in db.Programs // on f.ProgramID equals pro.ProgramID // join s in db.Songs // on f.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 f.ChannelID == channelid && f.Date >= startdate && f.Date <= enddate // group f by new { f.ChannelID, c.ChannelName, f.CurrencyType, f.SongID, pro.Name, ba.BandName, st.StudioName, p.ProducerName, s.Length, s.SongTitle } into g // select new { g.Key.ChannelID, g.Key.ChannelName, g.Key.CurrencyType, g.Key.Name, 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) // { // FrequencyReport freqreport = new FrequencyReport(); // freqreport.FrequencyDate = System.DateTime.Now.Date; // freqreport.TotalAmount = Convert.ToInt32(s.price); // freqreport.CurrencyType = s.CurrencyType; // freqreport.SongCount = s.count; // freqreport.ChannelID = s.ChannelID; // freqreport.ChannelName = s.ChannelName; // freqreport.SongID = s.SongID; // freqreport.Studio = s.StudioName; // freqreport.bandname = s.BandName; // freqreport.programname = s.Name; // freqreport.Producer = s.ProducerName; // freqreport.Duration = s.Length; // freqreport.SongTitle = s.SongTitle; // foreach (var l in songrep.GetArtistNameBySongID(freqreport.SongID)) // { // if (freqreport.Artist == null) // { // freqreport.Artist = l.ArtistMyanmarName; // } // else // { // freqreport.Artist += "-" + l.ArtistMyanmarName; // } // } // foreach (var l in songrep.GetComposerNameBySongID(freqreport.SongID)) // { // if (freqreport.Composer == null) // { // freqreport.Composer = l.ComposerName; // } // else // { // freqreport.Composer += "-" + l.ComposerName; // } // } // lstfrequencyReport.Add(freqreport); // } // return lstfrequencyReport; //} #region mod public List <FrequencyReport> ReportFrequencyBySongDetail(string channelid, DateTime startdate, DateTime enddate, string programid, string currencytype, string recivetype) { List <FrequencyReport> lstfrequencyReport = new List <FrequencyReport>(); var q = from f in db.Frequencies join c in db.Channels on f.ChannelID equals c.ChannelID join pro in db.Programs on f.ProgramID equals pro.ProgramID join s in db.Songs on f.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 f.Date >= startdate && f.Date <= enddate orderby f.Date group f by new { f.ChannelID, c.ChannelName, f.CurrencyType, f.SongID, pro.ProgramID, pro.Name, ba.BandName, st.StudioName, p.ProducerName, s.Length, s.SongTitle, f.Ispaid } into g select new { g.Key.ChannelID, g.Key.ChannelName, g.Key.ProgramID, g.Key.CurrencyType, g.Key.Name, 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, g.Key.Ispaid }; if (channelid != "") { q = q.Where(s => s.ChannelID == Convert.ToInt32(channelid)); } if (programid != null) { q = q.Where(s => s.ProgramID == programid); } if (currencytype != null) { q = q.Where(s => s.CurrencyType == currencytype); } if (recivetype != null) { if (recivetype == "Paid") { q = q.Where(s => s.Ispaid == true); } else { q = q.Where(s => s.Ispaid == false); } } foreach (var s in q) { FrequencyReport freqreport = new FrequencyReport(); freqreport.FrequencyDate = System.DateTime.Now.Date; freqreport.TotalAmount = Convert.ToInt32(s.price); freqreport.CurrencyType = s.CurrencyType; freqreport.SongCount = s.count; freqreport.ChannelID = s.ChannelID; freqreport.ChannelName = s.ChannelName; freqreport.SongID = s.SongID; freqreport.Studio = s.StudioName; freqreport.bandname = s.BandName; freqreport.programname = s.Name; freqreport.Producer = s.ProducerName; freqreport.Duration = s.Length; freqreport.SongTitle = s.SongTitle; foreach (var l in songrep.GetArtistNameBySongID(freqreport.SongID)) { if (freqreport.Artist == null) { freqreport.Artist = l.ArtistMyanmarName; } else { freqreport.Artist += "-" + l.ArtistMyanmarName; } } foreach (var l in songrep.GetComposerNameBySongID(freqreport.SongID)) { if (freqreport.Composer == null) { freqreport.Composer = l.ComposerName; } else { freqreport.Composer += "-" + l.ComposerName; } } lstfrequencyReport.Add(freqreport); } return(lstfrequencyReport); }