Bulk Database Entries - Commission

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

Re: Bulk Database Entries - Commission

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

Re: Bulk Database Entries - Commission

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'

Re: Bulk Database Entries - Commission

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

Re: Bulk Database Entries - Commission

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.

Re: Bulk Database Entries - Commission

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

Re: Bulk Database Entries - Commission

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

Re: Bulk Database Entries - Commission

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

Re: Bulk Database Entries - Commission

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.

Re: Bulk Database Entries - Commission

g.Key.TutorCommissionPercentage

should be

s.Key.TutorCommissionPercentage

change that, and ill get back on the othr error

Re: Bulk Database Entries - Commission

var newCommissions =db.Enrollments.select(s => 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 } }
                                 };

replace the code below with the code above,

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

Re: Bulk Database Entries - Commission

The second error was because we were working with a list and not the entity direct

now when we ae making a commission obect, we are adding the tutor object, and then adding the TutorCommissionPercentage object to the Tutor.

i have a question, why is the TutorCommissionPercentage a child object, should CommissionPercentage simply be a property of Tutor?
Are there more then one tutor comissions?

I think you will still get an error.

to fix, you can either make CommissionPercentage a property of Tutor and not a collection of child objects

or you can change this line enrolment.Tutor.TutorCommissionPercentage.CommissionPercentage
to
enrolment.Tutor.TutorCommissionPercentage.firstordefault.CommissionPercentage

Re: Bulk Database Entries - Commission

Okay I've made TutorCommissionPercentage a property of Tutor. The reason I had it alone was because I wanted to keep it confidential but I can still do that by making it a property.

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

            foreach (var newCommission in newCommissions)
            {
                List<TutorCommission> TutorComs = newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission
                    {
                        CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage,
                        TutorNoID = s.Key.TutorNoID

                    }).ToList;

                db.TutorCommission.Add(newCommission);
                db.SaveChanges();
            }

            return RedirectToAction("Index");
        }

How would I change this line accordingly: Tutor = new Tutor { TutorNoID = enrolment.Tutor.TutorNoID, TutorCommissionPercentage = new TutorCommissionPercentage { CommissionPercentage = enrolment.Tutor.TutorCommissionPercentage } } now that I've made TutorCommissionPercentage a property of Tutor?

And in this line: CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage, it says cannot implicitly convert type long to decimal. How would I explicitly convert it?

Re: Bulk Database Entries - Commission

Tutor = new Tutor { TutorNoID = enrolment.Tutor.TutorNoID, TutorCommissionPercentage = enrolment.Tutor.CommissionPercentage }

You mignt need to check this, as i normaly program in VB,

CommissionAmount = (decimal)s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage

CommissionAmount = (decimal)(s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage)

CommissionAmount = Convert.ToDecimal(s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage)

But thats poses another question, why would the answer be a Long, it should be a decimal, double or float, you may be losing your decimal places

Re: Bulk Database Entries - Commission

Okay, I've changed the proerty CommissionAmount to a double, but it's now telling me that

Operator '*' cannot be applied to operands of type 'double' and 'decimal'. I get the same error when I try to use a float.

Re: Bulk Database Entries - Commission

This is why i like VB
it must be something like

CommissionAmount = (decimal)s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage

CommissionAmount = s.Sum(u => u.CommissionAmount) * (decimal)s.Key.TutorCommissionPercentage

Re: Bulk Database Entries - Commission

Hmm, I can't get it work. Surely multiplying a double and a decimal should be allowed? That's something that needs to be done all the time!

Re: Bulk Database Entries - Commission

try

CommissionAmount = Decimal.Mutiply(s.Sum(u => u.CommissionAmount) , s.Key.TutorCommissionPercentage)

Re: Bulk Database Entries - Commission

I am jumping the gun herre, as i assume you are going to get the "can not convert to stored procedure" error

var newCommissions = db.Enrollments.asEnumerable.select(s => new TutorCommission()
                                 {
                                     CommissionAmount = enrolment.MonthlyFee,
                                     CommissionMonth = month, // string constant
                                     CommissionStatus = "Unpaid",
                                     Tutor = new Tutor { TutorNoID = enrolment.Tutor.TutorNoID, enrolment.Tutor.CommissionPercentage }
                                  };

Just a tip. thought in this case it does not matter.

Linq does not bother the database until a value is used,

this lne pulls the whole table into a list

var enrolments = db.Enrollments.ToList();

We may not need all the table, so it is better to do as i have done above "var newCommissions = db.Enrollments"

you can write a lot of linq code and have it compiled into one call to the database, very efficient.

Also AsEnumerable also calls the database, but it is sometimes necessary. thing to remember is to use it after any where clause if you can

the following line only pulls nine records from the database

var newCommissions = db.Enrollments.where(a => a.id < 10).AsEnumerable.select(s =>.....

This line pulls all records from the database

var newCommissions = db.Enrollments.AsEnumerable.where(a => a.id < 10).select(s =>.....

The reason we sometimes need asenumerable is because we need to use some code that cannot be converted into sql

Re: Bulk Database Entries - Commission

Okay, I've change commission percentage to a double because I'm getting irritated with that error and I want to test if the method is working. Once I did that, the error went away, but now there is error here:

}).ToList;

Cannot convert method group 'ToList' to non-delegate type 'System.Collections.Generic.List<DFPProductions_Default.Models.TutorCommission>'.

Did you intend to invoke the method?

My code looks like this now, just as a recap:

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 = enrolment.Tutor.TutorCommissionPercentage }
                                 };

            foreach (var newCommission in newCommissions)
            {
                List<TutorCommission> TutorComs = newCommissions.GroupBy(g => g.Tutor).Select(s => new TutorCommission
                    {
                        CommissionAmount = s.Sum(u => u.CommissionAmount) * s.Key.TutorCommissionPercentage,
                        TutorNoID = s.Key.TutorNoID

                    }).ToList;

                db.TutorCommission.Add(newCommission);
                db.SaveChanges();
            }

            return RedirectToAction("Index");
        }