I am writing an MVC 3 project and I'm using entity framework. I've written a method that generates bulk database entries, and this is for Invoices. The system is for a college, so the invoices are generated from a class called Enrollments (the monthly fee
for a student is in Enrollments). This method works correctly, but now I am looking to generate commission slips for the tutors of the college. The amount of the tutors monthly commission would be the amount their specific students pay X the Commission percentage
they receive. I am unsure how to write the code for this. These are the relevant model classes:
public class Enrollment
{
[Key]
[Display(Name = "Enrollment ID Number")]
public long EnrollmentIDNumber { get; set; }
[Display(Name = "Client ID Number")]
public long ClientNumberID { get; set; }
[Display(Name = "Tutor ID Number")]
public long TutorNoID { get; set; }
[Display(Name = "Course Name")]
public string CourseName { get; set; }
[Display(Name = "Lesson Time")]
public string LessonTime { get; set; }
[Display(Name = "Lesson Day")]
public string LessonDay { get; set; }
[Display(Name = "Lesson Location")]
public string LessonLocation { get; set; }
[Display(Name = "Lesson Type")]
public string LessonType { get; set; }
[Display(Name = "Lesson Level")]
public string LessonLevel { get; set; }
[Display(Name = "Monthly Fee")]
public long MonthlyFee { get; set; }
public virtual Client Client { get; set; }
public virtual Tutor Tutor { get; set; }
}
public class TutorCommission
{
[Key]
[Display(Name = "Commission ID")]
public long CommissionID { get; set; }
[Display(Name = "Commission Month")]
public string CommissionMonth {get; set;}
[Display(Name = "Commission Amount")]
public long CommissionAmount { get; set; }
[Display(Name = "Commission Status")]
public string CommissionStatus { get; set; }
[Display(Name = "Tutor ID Number")]
public long TutorNoID { get; set; }
public virtual Tutor Tutor { get; set; }
public virtual ICollection<CommissionPayments> CommissionPayments { get; set; }
}
public class TutorCommissionPercentage
{
[Key]
public int TutorCommissionID { get; set; }
public long TutorNoID { get; set; }
[Range(0, 100, ErrorMessage="Percentage must be between 0 and 100")]
[Display(Name="Commission Percentage")]
public decimal CommissionPercentage { get; set; }
public virtual Tutor Tutor { get; set; }
}
The code for the generate Invoices is the following:
public ActionResult CreateBulkInvoices()
{
var month = DateTime.Now.ToString("MMMM");
var enrolments = db.Enrollments.ToList();
var newInvoices = from enrolment in enrolments
select new Invoices()
{
InvoiceAmount = enrolment.MonthlyFee,
InvoiceMonth = month, // string constant
InvoiceStatus = "Unpaid",
ClientNumberID = enrolment.ClientNumberID
};
foreach (var newInvoice in newInvoices)
{
db.Invoice.Add(newInvoice);
db.SaveChanges();
}
return RedirectToAction("Index");
}
So to generate the tutor commission database entries, students enrolled with the tutors along with their monthly fees need to be used from the Enrollment class, added together and multiplied by the commission percentage of the tutor. I would like this method
to generate the commission slips for each tutor in the database. The purpose of this is for a manager to click on a link in order to generate these entries for the current month.
Thanks for your reply. Sorry for being difficult to follow! Yes, TutorCommission and TutorCommissionPercentage are linked through tutor. Each client is enrolled with a specific tutor at a specific time, day etc. So what the statement needs to do is find
out how many enrollments are linked to each tutor (through TutorNoID in the Enrollment class) and accumulate the monthly fees accordingly. And then multiply this total amount by the TutorCommissionPercentage. So, shouldn't the CommissionAmount = enrolment.MonthlyFee
have some sort of Sum() function to add all the amounts together? This is what I'm not sure how to do.
I've rewritten your statement below, with all the Invoice information changed to Commission:
public ActionResult CreateBulkCommissions()
{
var month = DateTime.Now.ToString("MMMM");
var enrolments = db.Enrollments.ToList();
var newCommissions = from enrolment in enrolments
select new TutorCommission()
{
CommissionAmount = enrolment.MonthlyFee,
CommissionMonth = month, // string constant
CommissionStatus = "Unpaid",
TutorNoID = enrolment.TutorNoID
};
foreach (var newCommission in newCommissions)
{
var TutorCommision = db.TutorCommission.Where(w => w.TutorNoID = newCommission.TutorNoID).select(s => new TutorCommission
{
CommissionAmount = s.Tutor.TutorCommissionPercentage * newCommission.CommissionAmount}).singleordefault;
db.TutorCommission.Add(newCommission);
db.SaveChanges();
}
return RedirectToAction("Index");
}
But it's giving me an error on this statement:
w => w.TutorNoID = newCommission.TutorNoID
that it cannot convert lambda expression to delegate type 'System.Func<DFPProductions_Default.Models.TutorCommission,int,bool>' because some of the return types in the block are not implicitly convertible to the delegate return type and that it cannot implicitly
convert type 'long' to 'bool'
Note I added TutorNoId to your select new TutorCommissions collection, but forget that, try thsi
in this line i am grouping the comisions by the tutor object (I hope i am doing this free hand) then getting the sum of commissionamout for each tutor, times the times the tutors commissionpercentage. key repersents the tutor we grouped by. give it a go
and let me know how you went
You were correct the first time in fact, I want it to be the total commission for all of the tutors enrollments, so it is more compex. I'm just not sure where to put your previous code? I have it like this:
public ActionResult CreateBulkCommissions()
{
var month = DateTime.Now.ToString("MMMM");
var enrolments = db.Enrollments.ToList();
var newCommissions = from enrolment in enrolments
select new TutorCommission()
{
CommissionAmount = enrolment.MonthlyFee,
CommissionMonth = month, // string constant
CommissionStatus = "Unpaid",
TutorNoID = enrolment.TutorNoID
};
foreach (var newCommission in newCommissions)
{
return newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission
{
CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage.CommissionPercentage
}).tolist;
db.TutorCommission.Add(newCommission);
db.SaveChanges();
}
return RedirectToAction("Index");
}
But as I mentioned earlier, the s.Key.TutorCommissionPercentage.Commission Percentage is giving me an error
public ActionResult CreateBulkCommissions()
{
var month = DateTime.Now.ToString("MMMM");
var enrolments = db.Enrollments.ToList();
var newCommissions = from enrolment in enrolments
select new TutorCommission()
{
CommissionAmount = enrolment.MonthlyFee,
CommissionMonth = month, // string constant
CommissionStatus = "Unpaid",
Tutor = new Tutor { TutorNoID = enrolment.Tutor.TutorNoID, TutorCommissionPercentage = new TutorCommissionPercentage { CommissionPercentage = enrolment.Tutor.TutorCommissionPercentage.CommissionPercentage } }
};
foreach (var newCommission in newCommissions)
{
List<TutorCommission> TutorComs = newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission
{
CommissionAmount = s.Sum(u => u.CommissionAmount) * g.Key.TutorCommissionPercentage.CommissionPercentage,
TutorNoID = s.Key.TutorNoID
} ).ToList;
db.TutorCommission.Add(newCommission);
db.SaveChanges();
}
return RedirectToAction("Index");
}
And I have two errors. One here: { CommissionPercentage = enrolment.Tutor.TutorCommissionPercentage.CommissionPercentage } }, saying that 'System.Collections.Generic.ICollection' does not contain a definition for 'CommissionPercentage' and no extension method
'CommissionPercentage' accepting a first argument of type 'System.Collections.Generic.ICollection' could be found.
And g.Key.TutorCommissionPercentage.CommissionPercentage, that 'g' does not exist in the current context.
amecily1
Member
128 Points
83 Posts
Bulk Database Entries - Commission
Oct 08, 2011 05:27 PM|LINK
Hi,
I am writing an MVC 3 project and I'm using entity framework. I've written a method that generates bulk database entries, and this is for Invoices. The system is for a college, so the invoices are generated from a class called Enrollments (the monthly fee for a student is in Enrollments). This method works correctly, but now I am looking to generate commission slips for the tutors of the college. The amount of the tutors monthly commission would be the amount their specific students pay X the Commission percentage they receive. I am unsure how to write the code for this. These are the relevant model classes:
public class Enrollment { [Key] [Display(Name = "Enrollment ID Number")] public long EnrollmentIDNumber { get; set; } [Display(Name = "Client ID Number")] public long ClientNumberID { get; set; } [Display(Name = "Tutor ID Number")] public long TutorNoID { get; set; } [Display(Name = "Course Name")] public string CourseName { get; set; } [Display(Name = "Lesson Time")] public string LessonTime { get; set; } [Display(Name = "Lesson Day")] public string LessonDay { get; set; } [Display(Name = "Lesson Location")] public string LessonLocation { get; set; } [Display(Name = "Lesson Type")] public string LessonType { get; set; } [Display(Name = "Lesson Level")] public string LessonLevel { get; set; } [Display(Name = "Monthly Fee")] public long MonthlyFee { get; set; } public virtual Client Client { get; set; } public virtual Tutor Tutor { get; set; } }public class TutorCommission { [Key] [Display(Name = "Commission ID")] public long CommissionID { get; set; } [Display(Name = "Commission Month")] public string CommissionMonth {get; set;} [Display(Name = "Commission Amount")] public long CommissionAmount { get; set; } [Display(Name = "Commission Status")] public string CommissionStatus { get; set; } [Display(Name = "Tutor ID Number")] public long TutorNoID { get; set; } public virtual Tutor Tutor { get; set; } public virtual ICollection<CommissionPayments> CommissionPayments { get; set; } }public class TutorCommissionPercentage { [Key] public int TutorCommissionID { get; set; } public long TutorNoID { get; set; } [Range(0, 100, ErrorMessage="Percentage must be between 0 and 100")] [Display(Name="Commission Percentage")] public decimal CommissionPercentage { get; set; } public virtual Tutor Tutor { get; set; } }The code for the generate Invoices is the following:
public ActionResult CreateBulkInvoices() { var month = DateTime.Now.ToString("MMMM"); var enrolments = db.Enrollments.ToList(); var newInvoices = from enrolment in enrolments select new Invoices() { InvoiceAmount = enrolment.MonthlyFee, InvoiceMonth = month, // string constant InvoiceStatus = "Unpaid", ClientNumberID = enrolment.ClientNumberID }; foreach (var newInvoice in newInvoices) { db.Invoice.Add(newInvoice); db.SaveChanges(); } return RedirectToAction("Index"); }So to generate the tutor commission database entries, students enrolled with the tutors along with their monthly fees need to be used from the Enrollment class, added together and multiplied by the commission percentage of the tutor. I would like this method to generate the commission slips for each tutor in the database. The purpose of this is for a manager to click on a link in order to generate these entries for the current month.
Thanks Amy
ThatsIT
Participant
1026 Points
388 Posts
Re: Bulk Database Entries - Commission
Oct 08, 2011 05:53 PM|LINK
A bit hard to folow but i will have a try, this is asuming tutorcommiosn and TutorCommissionPercentage are linked thought tutor
by the way, shouldent TutorCommisonPercentage simply be a property of Tutor, I write in vb, sio my c# syntax may be a bit off
var newInvoices = from enrolment in enrolments select new Invoices() { InvoiceAmount = enrolment.MonthlyFee, InvoiceMonth = month, // string constant InvoiceStatus = "Unpaid", ClientNumberID = enrolment.ClientNumberID, TutorNoID = enrolment.TutorNoID }; foreach (var newInvoice in newInvoices) { var TutorCommision = db.TutorCommisions.where(w => w.TutorNoID .equals(newInvoice.TutorNoID )) .select(s => new TutorCommission { CommissionAmount = s.Totor.TutorCommissionPercentage * newInvoice.InvoiceAmount }).singleordefault db.Invoice.Add(newInvoice); db.SaveChanges(); }amecily1
Member
128 Points
83 Posts
Re: Bulk Database Entries - Commission
Oct 08, 2011 06:33 PM|LINK
Thanks for your reply. Sorry for being difficult to follow! Yes, TutorCommission and TutorCommissionPercentage are linked through tutor. Each client is enrolled with a specific tutor at a specific time, day etc. So what the statement needs to do is find out how many enrollments are linked to each tutor (through TutorNoID in the Enrollment class) and accumulate the monthly fees accordingly. And then multiply this total amount by the TutorCommissionPercentage. So, shouldn't the CommissionAmount = enrolment.MonthlyFee have some sort of Sum() function to add all the amounts together? This is what I'm not sure how to do.
I've rewritten your statement below, with all the Invoice information changed to Commission:
public ActionResult CreateBulkCommissions() { var month = DateTime.Now.ToString("MMMM"); var enrolments = db.Enrollments.ToList(); var newCommissions = from enrolment in enrolments select new TutorCommission() { CommissionAmount = enrolment.MonthlyFee, CommissionMonth = month, // string constant CommissionStatus = "Unpaid", TutorNoID = enrolment.TutorNoID }; foreach (var newCommission in newCommissions) { var TutorCommision = db.TutorCommission.Where(w => w.TutorNoID = newCommission.TutorNoID).select(s => new TutorCommission { CommissionAmount = s.Tutor.TutorCommissionPercentage * newCommission.CommissionAmount}).singleordefault; db.TutorCommission.Add(newCommission); db.SaveChanges(); } return RedirectToAction("Index"); }But it's giving me an error on this statement:
that it cannot convert lambda expression to delegate type 'System.Func<DFPProductions_Default.Models.TutorCommission,int,bool>' because some of the return types in the block are not implicitly convertible to the delegate return type and that it cannot implicitly convert type 'long' to 'bool'
ThatsIT
Participant
1026 Points
388 Posts
Re: Bulk Database Entries - Commission
Oct 09, 2011 03:41 AM|LINK
Note I added TutorNoId to your select new TutorCommissions collection, but forget that, try thsi
in this line i am grouping the comisions by the tutor object (I hope i am doing this free hand) then getting the sum of commissionamout for each tutor, times the times the tutors commissionpercentage. key repersents the tutor we grouped by. give it a go and let me know how you went
return newCommissions.groupby(g => g.Tutor).select(s => new TutorCommission {
{
CommissionAmount = s.sum(u => u.CommissionAmount) * s.key.TutorCommissionPercentage.CommissionPercentage
} ).tolist
amecily1
Member
128 Points
83 Posts
Re: Bulk Database Entries - Commission
Oct 09, 2011 07:53 AM|LINK
Thanks. I'm trying this out - am I putting this within the loop? I'm having a problem with this line: s.Key.TutorCommissionPercentage.
Intellisense won't bring up CommissionPercentage, or any of the fields within TutorCommissionPercentage.
ThatsIT
Participant
1026 Points
388 Posts
Re: Bulk Database Entries - Commission
Oct 09, 2011 09:04 AM|LINK
The code I gave you will give you the total commission for each tutor for all his enrolments,
I am now seeing that you want the tutor commission for each enrolment individually, am I correct?
If so things are much easier,
var newCommissions = from enrolment in enrolments
select new TutorCommission()
{
CommissionAmount = enrolment.MonthlyFee * enrolment.Tutor.TutorCommissionPercentage.CommissionPercentage,
CommissionMonth = month, // string constant
CommissionStatus = "Unpaid",
};
Try that
amecily1
Member
128 Points
83 Posts
Re: Bulk Database Entries - Commission
Oct 09, 2011 09:11 AM|LINK
You were correct the first time in fact, I want it to be the total commission for all of the tutors enrollments, so it is more compex. I'm just not sure where to put your previous code? I have it like this:
public ActionResult CreateBulkCommissions() { var month = DateTime.Now.ToString("MMMM"); var enrolments = db.Enrollments.ToList(); var newCommissions = from enrolment in enrolments select new TutorCommission() { CommissionAmount = enrolment.MonthlyFee, CommissionMonth = month, // string constant CommissionStatus = "Unpaid", TutorNoID = enrolment.TutorNoID }; foreach (var newCommission in newCommissions) { return newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission { CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage.CommissionPercentage }).tolist; db.TutorCommission.Add(newCommission); db.SaveChanges(); } return RedirectToAction("Index"); }But as I mentioned earlier, the s.Key.TutorCommissionPercentage.Commission Percentage is giving me an error
ThatsIT
Participant
1026 Points
388 Posts
Re: Bulk Database Entries - Commission
Oct 09, 2011 09:19 AM|LINK
ok
you need this bottom line added, i dont have the Turo object so im guessing a bit here.
var newCommissions = from enrolment in enrolments
select new TutorCommission()
{
CommissionAmount = enrolment.MonthlyFee,
CommissionMonth = month, // string constant
CommissionStatus = "Unpaid",
Tutor = new Turor { TutorNoId = enrolmen.TuTor.TutorNoId, TutorCommissionPercentage = new TutorCommissionPercentage {CommissionPercentage = enrolment.Tutor.TutorCommissionPercentage.CommissionPercentage}
};
then tis will give you a list of tutors commisions, use the list "TutorComs" in your loop
List<TutorCommission> TutorComs = newCommissions.groupby(g => w.Tutor).select(s => new TutorCommission {
{
CommissionAmount = s.sum(u => u.CommissionAmount) * g.key.TutorCommissionPercentage.CommissionPercentage,
TutorNoID = s.key.TutorNoID
} ).tolist
amecily1
Member
128 Points
83 Posts
Re: Bulk Database Entries - Commission
Oct 09, 2011 09:38 AM|LINK
Okay cool, I've added all that:
public ActionResult CreateBulkCommissions() { var month = DateTime.Now.ToString("MMMM"); var enrolments = db.Enrollments.ToList(); var newCommissions = from enrolment in enrolments select new TutorCommission() { CommissionAmount = enrolment.MonthlyFee, CommissionMonth = month, // string constant CommissionStatus = "Unpaid", Tutor = new Tutor { TutorNoID = enrolment.Tutor.TutorNoID, TutorCommissionPercentage = new TutorCommissionPercentage { CommissionPercentage = enrolment.Tutor.TutorCommissionPercentage.CommissionPercentage } } }; foreach (var newCommission in newCommissions) { List<TutorCommission> TutorComs = newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission { CommissionAmount = s.Sum(u => u.CommissionAmount) * g.Key.TutorCommissionPercentage.CommissionPercentage, TutorNoID = s.Key.TutorNoID } ).ToList; db.TutorCommission.Add(newCommission); db.SaveChanges(); } return RedirectToAction("Index"); }And I have two errors. One here: { CommissionPercentage = enrolment.Tutor.TutorCommissionPercentage.CommissionPercentage } }, saying that 'System.Collections.Generic.ICollection' does not contain a definition for 'CommissionPercentage' and no extension method 'CommissionPercentage' accepting a first argument of type 'System.Collections.Generic.ICollection' could be found.
And g.Key.TutorCommissionPercentage.CommissionPercentage, that 'g' does not exist in the current context.
ThatsIT
Participant
1026 Points
388 Posts
Re: Bulk Database Entries - Commission
Oct 09, 2011 09:44 AM|LINK
g.Key.TutorCommissionPercentage
should be
s.Key.TutorCommissionPercentage
change that, and ill get back on the othr error