Date range on Mass Payment not working

The date range selection on the Paypal mass pay page does not work correctly.

On investigation I found that this is because the query generated applies the date range to dateCreated on the AFFILATES table but this should be applied to dateCreate on the TRANSACTION table.

e.g. if I try to perform the mass payment for July the query generated is:

select 3b0_wpam_affiliates.*,
( select coalesce(sum(tr.amount),0)
from 3b0_wpam_transactions tr
where tr.affiliateId = 3b0_wpam_affiliates.affiliateId
and tr.status != ‘failed’ ) balance,
( select coalesce(sum(IF(tr.type = ‘credit’, amount, 0)),0)
from 3b0_wpam_transactions tr
where tr.affiliateId = 3b0_wpam_affiliates.affiliateId and tr.status != ‘failed’ ) earnings
from 3b0_wpam_affiliates
WHERE 3b0_wpam_affiliates.paymentMethod = ‘paypal’
AND 3b0_wpam_affiliates.dateCreated >= ‘2016-07-01’
AND 3b0_wpam_affiliates.dateCreated <= ‘2016-07-31 23:59:59’
having balance >= ‘0.01’
ORDER BY balance DESC

BUT this shows the earnings for all Afflilates created in July rather than all TRANSACTIONS created in July. In my case this is 0 as all of my affiliated were created in June!

The correct SQL would be

select 3b0_wpam_affiliates.*,
( select coalesce(sum(tr.amount),0)
from 3b0_wpam_transactions tr
where tr.affiliateId = 3b0_wpam_affiliates.affiliateId
and tr.status != ‘failed’
AND tr.dateCreated >= ‘2016-07-01’
AND tr.dateCreated <= ‘2016-07-31 23:59:59’)
balance,
( select coalesce(sum(IF(tr.type = ‘credit’, amount, 0)),0)
from 3b0_wpam_transactions tr
where tr.affiliateId = 3b0_wpam_affiliates.affiliateId and tr.status != ‘failed’ ) earnings
from 3b0_wpam_affiliates
WHERE 3b0_wpam_affiliates.paymentMethod = ‘paypal’
having balance >= ‘0.01’
ORDER BY balance DESC

https://www.ads-software.com/plugins/affiliates-manager/