Add Up Amounts from Database MVC 3

I have an Invoice table and a ClientPayments table in my database. I have code that confirms a payment has occurred and registers it against a selected invoice (the user selects an invoice in the view). I am trying to get the status of the Invoice to change to "Confirmed" once the total payment amount equals the invoice amount. There can be many payments against once invoice and if the payment amount is less than the invoice amount, the status changes to "Partly Paid". This is my code from the controller below:

public ActionResult Confirm(int id, long InvoiceAmount, string PaymentType, float? InvoiceCustomAmount)
    {
        var invoice = db.Invoice.Find(id);

        //now validate that if the logged in user is authorized to select and confirm this invoice or not.
        ClientPayments clientPayment = db.ClientPayments.FirstOrDefault(cp => cp.InvoiceNumberID == id);

            clientPayment = new ClientPayments();

            clientPayment.InvoiceNumberID = id;

            var TotalPayments = (clientPayment.PaymentAmount + InvoiceAmount);

        if (InvoiceAmount == 115)
        {
            clientPayment.PaymentAmount = (long)InvoiceCustomAmount;
        }
        else
        {
            clientPayment.PaymentAmount = InvoiceAmount;
        }

            clientPayment.PaymentType = PaymentType;
            clientPayment.PaymentDate = DateTime.Now;
            db.ClientPayments.Add(clientPaym开发者_开发百科ent);

        if (TotalPayments != invoice.InvoiceAmount)
        {
            invoice.InvoiceStatus = "Partly Paid";
        }
        else
        {
            invoice.InvoiceStatus = "Confirmed";
        }

        // You don´t need this, since "invoice" was retrieved earlier in the method the database context
        // knows that changes have been made to this object when you call "SaveChanges".
        // db.Entry(invoices).State = EntityState.Modified;

        db.SaveChanges();

        return View();
    }

The problem I am having is that TotalPayments doesn't give my total payment amount - it doesn't add up the entries that have already be added to the database.

Thanks

Looks like your loading the clientPayment here

   ClientPayments clientPayment = db.ClientPayments.FirstOrDefault(cp => cp.InvoiceNumberID == id);

Now you've set the reference to a new ClientPayments, no longer loaded

   clientPayment = new ClientPayments();

Here your setting the InvoiceNumberID, but nothing else

   clientPayment.InvoiceNumberID = id;

Now your setting TotalPayments to the InvoiceAmount (clientPayment.PaymentAmount is 0 or null)

   var TotalPayments = (clientPayment.PaymentAmount + InvoiceAmount);

You then set the clientPayment.PaymentAmount later, but after you've already set the TotalPayment.

Am I missing something?

EDIT:

I think your close. I don't think you want FirstOrDefault when you get your payments from the database. I think you want a sum.

So, if your just getting the sum of payments, use something like this:

var totalPaid = ClientPayments.Where(cp => cp.InvoiceNumberID == id).Sum(cp => cp.PaymentAmount);

Now, you have your total from the database.

Also, what is this? InvoiceAmount == 115? 115 is what's referred to as a Magic Number. It doesn't mean anything to anyone reading your code. Consider using a Constant or an Enumeration. For example,

     const int totalInvoiceAmount = 115;

Then your code would read,

    if (InvoiceAmount == totalAmountOwed){...}

Then I think you can go ahead and create your new ClientPayment as you've done. Then set the values accordingly.

It looks like you need to get the amount the user is paying and add that to the total we got earlier. You can then check this amount against the balance owed to determine if the invoice stauts should be 'Confirmed' or 'Paid in Full'