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