I'm working on an ADM page to see at a snapshot various items.

I'm looking to have total payments and total invoices per year, and thus generate an account balance for the year.

There seems to be errors in both my total payments and my total invoices per year.

It is a little confusing to say the least.

Here is my Total Invoices statement:

ROUND(SUM((o70vm_invoices_items.value)*(o70vm_invoices_items.amount)),2) AS 'INV-TOTAL'

Here is my Total Payments statement:

SUM(o70vm_invoices_payments.payment_amount) AS 'Paid',

Some of the payments and invoices are correct (say 20%). Majority are incorrect.

Here is my complete MySQL query:

$query = "SELECT       
o70vm_invoices_contacts.name AS 'Parent_Name',
o70vm_invoices_contacts.id AS 'Parent_ID',
o70vm_invoices_contacts.company AS 'Children',

o70vm_invoices_invoices.id AS 'Invoice_ID',
o70vm_invoices_invoices.user_id AS 'Parent_ID_on_Invoice',
o70vm_invoices_invoices.invoice_num AS 'Invoice_Num',
o70vm_invoices_invoices.invoice_date AS 'Invoice_Date',
o70vm_invoices_invoices.invoice_duedate AS 'Invoice_Date_Due',

o70vm_invoices_items.id AS 'Item_ID',
o70vm_invoices_items.invoice_id AS 'Invoice_ID_on_Items',
o70vm_invoices_items.value AS 'Fee',
o70vm_invoices_items.amount AS 'Qty',

SUM(o70vm_invoices_payments.payment_amount) AS 'Paid',
o70vm_invoices_payments.payment_status AS 'Payment_Status',
o70vm_invoices_payments.payment_datetime AS 'Payment_Date',

ROUND(SUM((o70vm_invoices_items.value)*(o70vm_invoices_items.amount)),2) AS 'INV-TOTAL'

FROM o70vm_invoices_invoices, o70vm_invoices_contacts, o70vm_invoices_items, o70vm_invoices_payments
o70vm_invoices_contacts.id = o70vm_invoices_invoices.user_id
o70vm_invoices_items.invoice_id = o70vm_invoices_invoices.id
o70vm_invoices_payments.invoice_id = o70vm_invoices_invoices.id
o70vm_invoices_payments.payment_datetime >= STR_TO_DATE('2014-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')
o70vm_invoices_payments.payment_datetime <= NOW()

GROUP BY o70vm_invoices_contacts.id";

Any help, very much appreciated.


PS: Love this component! It is making life much easier.
Responses (3)
  • Accepted Answer

    Saturday, May 17 2014, 11:34 AM - #Permalink
    first of, I don't think it's a good idea to make the total for invoice and the total for payments in the same query. the SUM functions group results, and if you're grouping invoices and payments in the same query.. bad idea. make 2 querys, one for payments and the other for invoices.

    also, in the invoices query, you're not considering discounts. may this be the issue?

    finally, if you are using Invoice Manager 1.3.0, the're a new field on INVOICES table, which can be very useful to you. it's called "computed_total" and it already contains the invoice total...
    The reply is currently minimized Show
  • Accepted Answer

    Saturday, May 17 2014, 11:47 PM - #Permalink

    I'm not using 1.3.0 and I'm hesitant as I've made so many modifications. Really can't go back to the start with my formatting and coding.

    I'm not using discounts.

    I created just one query with the payments and the payment total is coming out wrong. This is pretty strange. It is really simple to create a total amount paid by a customer.

    This is my Payment total
    SUM(o70vm_invoices_payments.payment_amount) AS 'Paid'

    This is my Group By

    Any idea where I've gone wrong?
    The reply is currently minimized Show
  • Accepted Answer

    Sunday, May 18 2014, 12:04 AM - #Permalink
    Doing some more coding...I now have the total in payments working. Please disregard the previous comment.

    Thanks again.
    The reply is currently minimized Show
Your Reply