Thanks Geoff for the idea. I got it working by adding an extra SUM() and
retrieving invoices instead of customers. Now my virtual field in the
Invoice model looks like:
'SUM(IF(Invoice.due_date > \''.date('Y-m-d', strtotime('31 days ago')).'\',
(SELECT IF(SUM(amount) IS NOT NULL, Invoice.total_due - SUM(amount),
Invoice.total_due) FROM transactions WHERE transactions.invoice_id =
Invoice.id), 0))'
It's definitely not very clean but it works.
On Saturday, October 27, 2012 9:57:19 AM UTC-5, Geoff Douglas wrote:
>
> 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.
