diff options
| author | ivan <ivan> | 2009-04-24 19:06:15 +0000 |
|---|---|---|
| committer | ivan <ivan> | 2009-04-24 19:06:15 +0000 |
| commit | 713614726f774e7bf0de34a2a95c513f2e1cc558 (patch) | |
| tree | 6c6fbdd68a3eb5bdca8f2579c9dce53ad3a45c72 | |
| parent | 1da30e909e5792bfaf2181c2cd463e33411b2e65 (diff) | |
tune 1.7 query for better performance, hopefully. RT#4412
| -rw-r--r-- | FS/FS/Cron/bill.pm | 45 |
1 files changed, 23 insertions, 22 deletions
diff --git a/FS/FS/Cron/bill.pm b/FS/FS/Cron/bill.pm index f06fc1532..e699d3966 100644 --- a/FS/FS/Cron/bill.pm +++ b/FS/FS/Cron/bill.pm @@ -64,29 +64,30 @@ END # or my $where_bill_event = <<"END"; 0 < ( select count(*) from cust_bill + + LEFT JOIN cust_bill_pay USING ( invnum ) + LEFT JOIN cust_credit_bill USING ( invnum ) + LEFT JOIN part_bill_event ON ( + cust_main.payby = part_bill_event.payby + AND ( disabled IS NULL or disabled = '' ) + AND seconds <= $time - cust_bill._date + ) + LEFT JOIN cust_bill_event ON ( + part_bill_event.eventpart = cust_bill_event.eventpart + AND cust_bill_event.invnum = cust_bill_event.invnum + AND status = 'done' + ) + where cust_main.custnum = cust_bill.custnum - and 0 < charged - - coalesce( - ( select sum(amount) from cust_bill_pay - where cust_bill.invnum = cust_bill_pay.invnum ) - ,0 - ) - - coalesce( - ( select sum(amount) from cust_credit_bill - where cust_bill.invnum = cust_credit_bill.invnum ) - ,0 - ) - and 0 < ( select count(*) from part_bill_event - where payby = cust_main.payby - and ( disabled is null or disabled = '' ) - and seconds <= $time - cust_bill._date - and 0 = ( select count(*) from cust_bill_event - where cust_bill.invnum = cust_bill_event.invnum - and part_bill_event.eventpart = cust_bill_event.eventpart - and status = 'done' - ) - - ) + + GROUP BY cust_bill.invnum, cust_bill.charged + HAVING + 0 < charged + - coalesce( sum(cust_bill_pay.amount) ,0 ) + - coalesce( sum(cust_credit_bill.amount), 0 ) + AND + 0 < COUNT( cust_bill_event.eventnum IS NULL ) + ) END |