diff options
| author | cvs2git <cvs2git> | 2008-03-16 19:58:34 +0000 |
|---|---|---|
| committer | cvs2git <cvs2git> | 2008-03-16 19:58:34 +0000 |
| commit | eb061f5119325e666f0dff40d4089e5c1df58e17 (patch) | |
| tree | d55e8fef5aca62eb13bbc8ad20dbdf941c3bd266 /bin/explain-ar-total.sql | |
| parent | 3a17b276638200475d54201fa62566b7440e819a (diff) | |
This commit was manufactured by cvs2svn to create tag 'TRIXBOX_2_6'.TRIXBOX_2_6
Diffstat (limited to 'bin/explain-ar-total.sql')
| -rw-r--r-- | bin/explain-ar-total.sql | 976 |
1 files changed, 0 insertions, 976 deletions
diff --git a/bin/explain-ar-total.sql b/bin/explain-ar-total.sql deleted file mode 100644 index f1544303b..000000000 --- a/bin/explain-ar-total.sql +++ /dev/null @@ -1,976 +0,0 @@ -EXPLAIN SELECT ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date > ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - AS balance_0_30, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_bill._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_refund._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_credit._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM now() ) - 2592000 ) AND cust_pay._date > ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - AS balance_30_60, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_bill._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_refund._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_credit._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM now() ) - 5184000 ) AND cust_pay._date > ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - AS balance_60_90, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM now() ) - 7776000 ) AND ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - AS balance_90_0, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay - WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill - WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum ) - + ( SELECT COALESCE(SUM(refund - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_refund.refundnum = cust_credit_refund.refundnum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_refund.refundnum = cust_pay_refund.refundnum ) - ,0 - ) - ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum ) - - ( SELECT COALESCE(SUM(amount - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_refund - WHERE cust_credit.crednum = cust_credit_refund.crednum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_credit_bill - WHERE cust_credit.crednum = cust_credit_bill.crednum ) - ,0 - ) - ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum ) - - ( SELECT COALESCE(SUM(paid - - COALESCE( - ( SELECT SUM(amount) FROM cust_bill_pay - WHERE cust_pay.paynum = cust_bill_pay.paynum ) - ,0 - ) - - COALESCE( - ( SELECT SUM(amount) FROM cust_pay_refund - WHERE cust_pay.paynum = cust_pay_refund.paynum ) - ,0 - ) - ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum ) - > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) ) - AS balance_0_0 |