public List <AVoucherDisplayClass> ReportAnnualListAssociation(string currencytype, string channelid, string paid, string customerid, DateTime startdate, DateTime enddate)
{
List <AVoucherDisplayClass> mylist = new List <AVoucherDisplayClass>();
var q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
where av.CustomerID == customerid && a.StartDate >= startdate && a.EndDate <= enddate && a.Ispaid == true
group av by new { b.ChannelName, a.CurrencyType, av.CustomerID, av.IsPaid, b.ChannelID } into g
orderby g.Key.ChannelName
select new { g.Key.CustomerID, g.Key.ChannelID, g.Key.ChannelName, g.Key.CurrencyType, g.Key.IsPaid, Amount = g.Sum(s => s.Amount), count = g.Count() };
if (paid != null)
{
if (paid == "Paid")
{
q = q.Where(s => s.IsPaid == true);
}
else
{
q = q.Where(s => s.IsPaid == false);
}
}
if (currencytype != null)
{
q = q.Where(s => s.CurrencyType == currencytype);
}
if (channelid != null)
{
q = q.Where(s => s.ChannelID == Convert.ToInt16(channelid));
}
foreach (var l in q)
{
AVoucherDisplayClass display = new AVoucherDisplayClass();
display.ChannelID = l.ChannelID;
display.ChannelName = l.ChannelName;
//display.SongID = l.SongID;
display.CustomerID = l.CustomerID;
//display.AnnualStartDate = Convert.ToDateTime(l.StartDate);
//display.AnnualEndDate = Convert.ToDateTime(l.EndDate);
display.Amount = l.Amount;
display.CurrencyType = l.CurrencyType;
display.SongsCount = l.count;
if (display.CustomerID.StartsWith("As"))
{
display.CustomerName = "Association";
display.CustomerType = "Association";
}
else if (display.CustomerID.StartsWith("A"))
{
ArtistRepository artistrep = new ArtistRepository();
Artist tblartist = artistrep.GetArtistByID(display.CustomerID);
display.CustomerName = tblartist.ArtistMyanmarName;
display.CustomerType = "Artist Name";
}
else if (display.CustomerID.StartsWith("C"))
{
ComposerRepository composerrep = new ComposerRepository();
Composer tblcomposer = composerrep.GetComposerByID(display.CustomerID);
display.CustomerName = tblcomposer.ComposerName;
display.CustomerType = "Composer Name";
}
else if (display.CustomerID.StartsWith("P"))
{
ProducerRepository producerrep = new ProducerRepository();
Producer tblproducer = producerrep.GetProducerByID(display.CustomerID);
display.CustomerName = tblproducer.ProducerName;
display.CustomerType = "Producer Name";
}
else if (display.CustomerID.StartsWith("D"))
{
StudioRepository studiorep = new StudioRepository();
Studio tblStudio = studiorep.GetStudioByID(display.CustomerID);
display.CustomerName = tblStudio.StudioName;
display.CustomerType = "Studio Name";
}
else if (display.CustomerID.StartsWith("B"))
{
BandRepository bandrep = new BandRepository();
Band tblBand = bandrep.GetBandByID(display.CustomerID);
display.CustomerName = tblBand.BandName;
display.CustomerType = "Band Name";
}
else if (display.CustomerID.StartsWith("Othe"))
{
display.CustomerName = "Other";
display.CustomerType = "Other";
}
mylist.Add(display);
}
return(mylist);
}
public List <AVoucherDisplayClass> List(string CurrencyType, string paidtype, string channelid, string customerid, DateTime startdate, DateTime enddate, string custtype)
{
List <AVoucherDisplayClass> mylist = new List <AVoucherDisplayClass>();
bool paid = false;
if (paidtype == "Paid")
{
paid = true;
}
else if (paidtype == "UnPaid")
{
paid = false;
}
//necessory just declare var q for a type
var q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
join arts in db.Artist_Songs
on s.SongID equals arts.SongID
join art in db.Artists
on arts.ArtistID equals art.ArtistID
where a.StartDate >= startdate && a.EndDate <= enddate && av.CustomerID == customerid && art.ArtistID == customerid && a.Ispaid == true
select new { av, a, b, s, ct };
if (custtype == "Producer")
{
q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
join pro in db.Producers
on s.ProducerID equals pro.ProducerID
where a.StartDate >= startdate && a.EndDate <= enddate && av.CustomerID == customerid && pro.ProducerID == customerid && a.Ispaid == true
select new { av, a, b, s, ct };
}
else if (custtype == "Composer")
{
q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
join comps in db.Composer_Songs
on s.SongID equals comps.SongID
join comp in db.Composers
on comps.ComposerID equals comp.ComposerID
where a.StartDate >= startdate && a.EndDate <= enddate && av.CustomerID == customerid && comp.ComposerID == customerid && a.Ispaid == true
select new { av, a, b, s, ct };
}
else if (custtype == "Artist")
{
q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
join arts in db.Artist_Songs
on s.SongID equals arts.SongID
join art in db.Artists
on arts.ArtistID equals art.ArtistID
where a.StartDate >= startdate && a.EndDate <= enddate && av.CustomerID == customerid && art.ArtistID == customerid && a.Ispaid == true
select new { av, a, b, s, ct };
}
else if (custtype == "Band")
{
q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
join ban in db.Bands
on s.BandID equals ban.BandID
where a.StartDate >= startdate && a.EndDate <= enddate && av.CustomerID == customerid && ban.BandID == customerid && a.Ispaid == true
select new { av, a, b, s, ct };
}
else if (custtype == "Studio")
{
q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
join stu in db.Studios
on s.StudioID equals stu.StudioID
where a.StartDate >= startdate && a.EndDate <= enddate && av.CustomerID == customerid && stu.StudioID == customerid && a.Ispaid == true
select new { av, a, b, s, ct };
}
if (paidtype != "All Paid Type")
{
q = q.Where(s => s.av.IsPaid == paid);
}
if (channelid != "")
{
q = q.Where(s => s.b.ChannelID == Convert.ToInt16(channelid));
}
if (CurrencyType != "")
{
q = q.Where(s => s.a.CurrencyType == CurrencyType);
}
foreach (var l in q)
{
AVoucherDisplayClass display = new AVoucherDisplayClass();
display.AVoucherID = l.av.AVoucherID;
display.ChannelID = l.b.ChannelID;
display.ChannelName = l.b.ChannelName;
display.Amount = l.av.Amount;
display.AnnualID = l.av.AnnualID;
display.IsPaid = l.av.IsPaid;
display.SongID = l.s.SongID;
display.SongTitle = l.s.SongTitle;
display.CustomerID = l.av.CustomerID;
display.AnnualStartDate = Convert.ToDateTime(l.a.StartDate);
display.AnnualEndDate = Convert.ToDateTime(l.a.EndDate);
display.CurrencyType = l.a.CurrencyType;
if (display.CustomerID.StartsWith("As"))
{
display.CustomerName = "Association";
}
else if (display.CustomerID.StartsWith("A"))
{
Artist tblartist = artistrep.GetArtistByID(display.CustomerID);
display.CustomerName = tblartist.ArtistMyanmarName;
}
else if (display.CustomerID.StartsWith("C"))
{
Composer tblcomposer = composerrep.GetComposerByID(display.CustomerID);
display.CustomerName = tblcomposer.ComposerName;
}
else if (display.CustomerID.StartsWith("P"))
{
Producer tblproducer = producerrep.GetProducerByID(display.CustomerID);
display.CustomerName = tblproducer.ProducerName;
}
else if (display.CustomerID.StartsWith("D"))
{
Studio tblStudio = studiorep.GetStudioByID(display.CustomerID);
display.CustomerName = tblStudio.StudioName;
}
else if (display.CustomerID.StartsWith("B"))
{
Band tblBand = bandrep.GetBandByID(display.CustomerID);
display.CustomerName = tblBand.BandName;
}
else if (display.CustomerID.StartsWith("Othe"))
{
display.CustomerName = "Other";
}
mylist.Add(display);
}
return(mylist);
}
public List <AVoucherDisplayClass> List()
{
List <AVoucherDisplayClass> mylist = new List <AVoucherDisplayClass>();
var q = from av in db.AVouchers
join a in db.Annuals
on av.AnnualID equals a.AnnualID
join b in db.Channels
on a.ChannelID equals b.ChannelID
join ct in db.ChannelTypes
on b.ChannelTypeID equals ct.ChannelTypeID
join s in db.Songs
on a.SongID equals s.SongID
select new { av, a, b, s, ct };
foreach (var l in q)
{
AVoucherDisplayClass display = new AVoucherDisplayClass();
display.AnnualID = l.a.AnnualID;
display.ChannelID = l.b.ChannelID;
display.ChannelName = l.b.ChannelName;
display.Amount = l.av.Amount;
display.AVoucherID = l.av.AVoucherID;
display.paidtime = Convert.ToDateTime(l.av.PaidTime);
display.IsPaid = l.av.IsPaid;
display.SongID = l.s.SongID;
display.SongTitle = l.s.SongTitle;
display.CustomerID = l.av.CustomerID;
display.AnnualStartDate = Convert.ToDateTime(l.a.StartDate);
display.AnnualEndDate = Convert.ToDateTime(l.a.EndDate);
display.CurrencyType = l.a.CurrencyType;
if (display.CustomerID.StartsWith("As"))
{
display.CustomerName = "Association";
}
else if (display.CustomerID.StartsWith("A"))
{
ArtistRepository artistrep = new ArtistRepository();
Artist tblartist = artistrep.GetArtistByID(display.CustomerID);
display.CustomerName = tblartist.ArtistMyanmarName;
}
else if (display.CustomerID.StartsWith("C"))
{
ComposerRepository composerrep = new ComposerRepository();
Composer tblcomposer = composerrep.GetComposerByID(display.CustomerID);
display.CustomerName = tblcomposer.ComposerName;
}
else if (display.CustomerID.StartsWith("P"))
{
ProducerRepository producerrep = new ProducerRepository();
Producer tblproducer = producerrep.GetProducerByID(display.CustomerID);
display.CustomerName = tblproducer.ProducerName;
}
else if (display.CustomerID.StartsWith("D"))
{
StudioRepository studiorep = new StudioRepository();
Studio tblStudio = studiorep.GetStudioByID(display.CustomerID);
display.CustomerName = tblStudio.StudioName;
}
else if (display.CustomerID.StartsWith("B"))
{
BandRepository bandrep = new BandRepository();
Band tblBand = bandrep.GetBandByID(display.CustomerID);
display.CustomerName = tblBand.BandName;
}
else if (display.CustomerID.StartsWith("Othe"))
{
display.CustomerName = "Other";
}
mylist.Add(display);
}
return(mylist);
}