I am trying to get an accounts aging report to work correctly. I want to
find all customers that have invoices with a balance within a date range.
Invoice hasMany Transaction - the transactions are payments made on that
invoice, I have a virtual field in the Invoice model that takes the total
invoice minus the sum of the transactions that belong to that invoice. The
'balance_due' virtual field in the Invoice model has this sql 'SELECT
IF(SUM(amount) IS NOT NULL, Invoice.total_due - SUM(amount),
Invoice.total_due) FROM transactions WHERE transactions.invoice_id =
Invoice.id'.
In my Customer model that hasMany Invoice I want to retrieve all the
customers and have a virtual field that has the total amount due based on a
date. What I have for my 'aging' virtual field in the Customer model is:
'SELECT IF(SUM(balance_due) IS NOT NULL, SUM(balance_due), 0) FROM invoices
WHERE invoices.balance_due > 0 AND invoices.due_date > \''.date('Y-m-d',
strtotime('31 days ago')).'\' AND invoices.customer_id = Customer.id'
The customer's virtual field would work correctly if it was using a real
column in the invoices table. How do I change the sql string to replace
balance_due with something that will look like the result in the Invoice
model? Do I need some kind of join or subquery?
--
Like Us on FaceBook https://www.facebook.com/CakePHP
Find us on Twitter http://twitter.com/CakePHP
---
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com.
To unsubscribe from this group, send email to
cake-php+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en.
