yii get sum from a table related to a related table

Question

I have these existing tables to work with, can't change them. I have given everything but cannot get a workaround. It is complex even for me to explain the problem so please bear with me.

I am using Oracle 10g with Yii 1.1.x, WIndows, Wamp.

Models have been created. I need to get the sum of PRESALES for given SALESEXECs between a date range.

SALESEXEC
------------------------------------------
id             | 1
name           | salesguy1

relation
'SALES_TOTAL' => array(self::STAT, 'CURSALES', 'id', 'select'=>'SUM(AMOUNT)'),

The above works, I can get my total current sales against each SALESEXEC I also need a PRESALES_TOTAL, totally stuck there

CURSALES
------------------------------------------
jobnumber      | AJOB2014   | AJOB2014
customernumber | cus1       | cus2
amount         | 1000       | 1000
saledate       | 01-08-2014 | 01-09-2014
salesexec_id   | 1          | 1

SALES EXEC is in a 1-many relation with CURSALES.

PRESALES
------------------------------------------
jobnumber      | AJOB2014  | AJOB2014  
customernumber | cus1      | cus2
amount         | 500       | 700
salesexec_id   | 1         | 1

This table stores all previous sales. It is related to CURSALES by the customernumber and jobnumber.

I need the sum of this PRESALES amount as a property of SALESEXEC.

As in the example, if I pass the salesdate range as 01-08-2014 and 15-08-2014, it should include PRESALES 1st row in the SUM but not the 2nd row because it has cus2 which maps to 2nd row of CURSALES but the saledate for it is outside my given date range.

I tried this in SALESEXEC model 'PRESALES_TOTAL' => array(self::STAT, 'PRESALES', 'id', 'select'=>'SUM(AMOUNT)'), but it returns all previous sales without matching the date range and the jobnumber and customernumber

I tried 'through' clause in the relation but it is not valid for STAT

In effect, I will pass a list of SALESEXEC id and a date range, it will need to go to CURSALES and pick up data matching the date range, then need to go to PRESAL and get the total of previous year's sales of those customernumber and jobnumber). I need to display list of SALESEXEC with their total sales and total previous sales in a CGridView.

Thanks a lot!