fly2279,
In order to get the aging for the customers, you need to use the data from
the invoices model. You need to select the invoices, Cake will join in the
customer data because of the belongsTo relationship, then you group on
customer, summing up the various fields that you want.
If I where you I would do a join of a sub-select of the total payments,
into an invoices select grouped by customer.
- Select Invoices
- join Customer (automatically done by cake)
- join sub on TotalPayments.invoice_id = Invoice.id
- select transactions as TotalPayments
- group by invoice
- group by customer
Does this make sense?
Happy Coding.
Geoff
On Friday, October 26, 2012 3:05:09 PM UTC-7, fly2279 wrote:
>
> 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.
