1 EXPLAIN SELECT ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
2 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
3 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
4 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
5 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
6 + ( SELECT COALESCE(SUM(refund
8 ( SELECT SUM(amount) FROM cust_credit_refund
9 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
13 ( SELECT SUM(amount) FROM cust_pay_refund
14 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
17 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
18 - ( SELECT COALESCE(SUM(amount
20 ( SELECT SUM(amount) FROM cust_credit_refund
21 WHERE cust_credit.crednum = cust_credit_refund.crednum )
25 ( SELECT SUM(amount) FROM cust_credit_bill
26 WHERE cust_credit.crednum = cust_credit_bill.crednum )
29 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
30 - ( SELECT COALESCE(SUM(paid
32 ( SELECT SUM(amount) FROM cust_bill_pay
33 WHERE cust_pay.paynum = cust_bill_pay.paynum )
37 ( SELECT SUM(amount) FROM cust_pay_refund
38 WHERE cust_pay.paynum = cust_pay_refund.paynum )
41 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
42 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
43 + ( SELECT COALESCE(SUM(refund
45 ( SELECT SUM(amount) FROM cust_credit_refund
46 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
50 ( SELECT SUM(amount) FROM cust_pay_refund
51 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
54 ), 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
55 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
56 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
57 + ( SELECT COALESCE(SUM(refund
59 ( SELECT SUM(amount) FROM cust_credit_refund
60 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
64 ( SELECT SUM(amount) FROM cust_pay_refund
65 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
68 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
69 - ( SELECT COALESCE(SUM(amount
71 ( SELECT SUM(amount) FROM cust_credit_refund
72 WHERE cust_credit.crednum = cust_credit_refund.crednum )
76 ( SELECT SUM(amount) FROM cust_credit_bill
77 WHERE cust_credit.crednum = cust_credit_bill.crednum )
80 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
81 - ( SELECT COALESCE(SUM(paid
83 ( SELECT SUM(amount) FROM cust_bill_pay
84 WHERE cust_pay.paynum = cust_bill_pay.paynum )
88 ( SELECT SUM(amount) FROM cust_pay_refund
89 WHERE cust_pay.paynum = cust_pay_refund.paynum )
92 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
93 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
94 - ( SELECT COALESCE(SUM(amount
96 ( SELECT SUM(amount) FROM cust_credit_refund
97 WHERE cust_credit.crednum = cust_credit_refund.crednum )
101 ( SELECT SUM(amount) FROM cust_credit_bill
102 WHERE cust_credit.crednum = cust_credit_bill.crednum )
105 ), 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
106 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
107 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
108 + ( SELECT COALESCE(SUM(refund
110 ( SELECT SUM(amount) FROM cust_credit_refund
111 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
115 ( SELECT SUM(amount) FROM cust_pay_refund
116 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
119 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
120 - ( SELECT COALESCE(SUM(amount
122 ( SELECT SUM(amount) FROM cust_credit_refund
123 WHERE cust_credit.crednum = cust_credit_refund.crednum )
127 ( SELECT SUM(amount) FROM cust_credit_bill
128 WHERE cust_credit.crednum = cust_credit_bill.crednum )
131 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
132 - ( SELECT COALESCE(SUM(paid
134 ( SELECT SUM(amount) FROM cust_bill_pay
135 WHERE cust_pay.paynum = cust_bill_pay.paynum )
139 ( SELECT SUM(amount) FROM cust_pay_refund
140 WHERE cust_pay.paynum = cust_pay_refund.paynum )
143 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
144 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
145 - ( SELECT COALESCE(SUM(paid
147 ( SELECT SUM(amount) FROM cust_bill_pay
148 WHERE cust_pay.paynum = cust_bill_pay.paynum )
152 ( SELECT SUM(amount) FROM cust_pay_refund
153 WHERE cust_pay.paynum = cust_pay_refund.paynum )
156 ), 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
157 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
158 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
159 + ( SELECT COALESCE(SUM(refund
161 ( SELECT SUM(amount) FROM cust_credit_refund
162 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
166 ( SELECT SUM(amount) FROM cust_pay_refund
167 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
170 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
171 - ( SELECT COALESCE(SUM(amount
173 ( SELECT SUM(amount) FROM cust_credit_refund
174 WHERE cust_credit.crednum = cust_credit_refund.crednum )
178 ( SELECT SUM(amount) FROM cust_credit_bill
179 WHERE cust_credit.crednum = cust_credit_bill.crednum )
182 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
183 - ( SELECT COALESCE(SUM(paid
185 ( SELECT SUM(amount) FROM cust_bill_pay
186 WHERE cust_pay.paynum = cust_bill_pay.paynum )
190 ( SELECT SUM(amount) FROM cust_pay_refund
191 WHERE cust_pay.paynum = cust_pay_refund.paynum )
194 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
195 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
196 AS balance_0_30, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
197 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
198 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
199 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
200 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
201 + ( SELECT COALESCE(SUM(refund
203 ( SELECT SUM(amount) FROM cust_credit_refund
204 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
208 ( SELECT SUM(amount) FROM cust_pay_refund
209 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
212 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
213 - ( SELECT COALESCE(SUM(amount
215 ( SELECT SUM(amount) FROM cust_credit_refund
216 WHERE cust_credit.crednum = cust_credit_refund.crednum )
220 ( SELECT SUM(amount) FROM cust_credit_bill
221 WHERE cust_credit.crednum = cust_credit_bill.crednum )
224 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
225 - ( SELECT COALESCE(SUM(paid
227 ( SELECT SUM(amount) FROM cust_bill_pay
228 WHERE cust_pay.paynum = cust_bill_pay.paynum )
232 ( SELECT SUM(amount) FROM cust_pay_refund
233 WHERE cust_pay.paynum = cust_pay_refund.paynum )
236 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
237 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
238 + ( SELECT COALESCE(SUM(refund
240 ( SELECT SUM(amount) FROM cust_credit_refund
241 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
245 ( SELECT SUM(amount) FROM cust_pay_refund
246 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
249 ), 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
250 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
251 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
252 + ( SELECT COALESCE(SUM(refund
254 ( SELECT SUM(amount) FROM cust_credit_refund
255 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
259 ( SELECT SUM(amount) FROM cust_pay_refund
260 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
263 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
264 - ( SELECT COALESCE(SUM(amount
266 ( SELECT SUM(amount) FROM cust_credit_refund
267 WHERE cust_credit.crednum = cust_credit_refund.crednum )
271 ( SELECT SUM(amount) FROM cust_credit_bill
272 WHERE cust_credit.crednum = cust_credit_bill.crednum )
275 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
276 - ( SELECT COALESCE(SUM(paid
278 ( SELECT SUM(amount) FROM cust_bill_pay
279 WHERE cust_pay.paynum = cust_bill_pay.paynum )
283 ( SELECT SUM(amount) FROM cust_pay_refund
284 WHERE cust_pay.paynum = cust_pay_refund.paynum )
287 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
288 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
289 - ( SELECT COALESCE(SUM(amount
291 ( SELECT SUM(amount) FROM cust_credit_refund
292 WHERE cust_credit.crednum = cust_credit_refund.crednum )
296 ( SELECT SUM(amount) FROM cust_credit_bill
297 WHERE cust_credit.crednum = cust_credit_bill.crednum )
300 ), 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
301 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
302 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
303 + ( SELECT COALESCE(SUM(refund
305 ( SELECT SUM(amount) FROM cust_credit_refund
306 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
310 ( SELECT SUM(amount) FROM cust_pay_refund
311 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
314 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
315 - ( SELECT COALESCE(SUM(amount
317 ( SELECT SUM(amount) FROM cust_credit_refund
318 WHERE cust_credit.crednum = cust_credit_refund.crednum )
322 ( SELECT SUM(amount) FROM cust_credit_bill
323 WHERE cust_credit.crednum = cust_credit_bill.crednum )
326 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
327 - ( SELECT COALESCE(SUM(paid
329 ( SELECT SUM(amount) FROM cust_bill_pay
330 WHERE cust_pay.paynum = cust_bill_pay.paynum )
334 ( SELECT SUM(amount) FROM cust_pay_refund
335 WHERE cust_pay.paynum = cust_pay_refund.paynum )
338 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
339 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
340 - ( SELECT COALESCE(SUM(paid
342 ( SELECT SUM(amount) FROM cust_bill_pay
343 WHERE cust_pay.paynum = cust_bill_pay.paynum )
347 ( SELECT SUM(amount) FROM cust_pay_refund
348 WHERE cust_pay.paynum = cust_pay_refund.paynum )
351 ), 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
352 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
353 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
354 + ( SELECT COALESCE(SUM(refund
356 ( SELECT SUM(amount) FROM cust_credit_refund
357 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
361 ( SELECT SUM(amount) FROM cust_pay_refund
362 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
365 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
366 - ( SELECT COALESCE(SUM(amount
368 ( SELECT SUM(amount) FROM cust_credit_refund
369 WHERE cust_credit.crednum = cust_credit_refund.crednum )
373 ( SELECT SUM(amount) FROM cust_credit_bill
374 WHERE cust_credit.crednum = cust_credit_bill.crednum )
377 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
378 - ( SELECT COALESCE(SUM(paid
380 ( SELECT SUM(amount) FROM cust_bill_pay
381 WHERE cust_pay.paynum = cust_bill_pay.paynum )
385 ( SELECT SUM(amount) FROM cust_pay_refund
386 WHERE cust_pay.paynum = cust_pay_refund.paynum )
389 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
390 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
391 AS balance_30_60, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
392 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
393 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
394 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
395 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
396 + ( SELECT COALESCE(SUM(refund
398 ( SELECT SUM(amount) FROM cust_credit_refund
399 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
403 ( SELECT SUM(amount) FROM cust_pay_refund
404 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
407 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
408 - ( SELECT COALESCE(SUM(amount
410 ( SELECT SUM(amount) FROM cust_credit_refund
411 WHERE cust_credit.crednum = cust_credit_refund.crednum )
415 ( SELECT SUM(amount) FROM cust_credit_bill
416 WHERE cust_credit.crednum = cust_credit_bill.crednum )
419 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
420 - ( SELECT COALESCE(SUM(paid
422 ( SELECT SUM(amount) FROM cust_bill_pay
423 WHERE cust_pay.paynum = cust_bill_pay.paynum )
427 ( SELECT SUM(amount) FROM cust_pay_refund
428 WHERE cust_pay.paynum = cust_pay_refund.paynum )
431 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
432 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
433 + ( SELECT COALESCE(SUM(refund
435 ( SELECT SUM(amount) FROM cust_credit_refund
436 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
440 ( SELECT SUM(amount) FROM cust_pay_refund
441 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
444 ), 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
445 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
446 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
447 + ( SELECT COALESCE(SUM(refund
449 ( SELECT SUM(amount) FROM cust_credit_refund
450 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
454 ( SELECT SUM(amount) FROM cust_pay_refund
455 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
458 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
459 - ( SELECT COALESCE(SUM(amount
461 ( SELECT SUM(amount) FROM cust_credit_refund
462 WHERE cust_credit.crednum = cust_credit_refund.crednum )
466 ( SELECT SUM(amount) FROM cust_credit_bill
467 WHERE cust_credit.crednum = cust_credit_bill.crednum )
470 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
471 - ( SELECT COALESCE(SUM(paid
473 ( SELECT SUM(amount) FROM cust_bill_pay
474 WHERE cust_pay.paynum = cust_bill_pay.paynum )
478 ( SELECT SUM(amount) FROM cust_pay_refund
479 WHERE cust_pay.paynum = cust_pay_refund.paynum )
482 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
483 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
484 - ( SELECT COALESCE(SUM(amount
486 ( SELECT SUM(amount) FROM cust_credit_refund
487 WHERE cust_credit.crednum = cust_credit_refund.crednum )
491 ( SELECT SUM(amount) FROM cust_credit_bill
492 WHERE cust_credit.crednum = cust_credit_bill.crednum )
495 ), 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
496 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
497 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
498 + ( SELECT COALESCE(SUM(refund
500 ( SELECT SUM(amount) FROM cust_credit_refund
501 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
505 ( SELECT SUM(amount) FROM cust_pay_refund
506 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
509 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
510 - ( SELECT COALESCE(SUM(amount
512 ( SELECT SUM(amount) FROM cust_credit_refund
513 WHERE cust_credit.crednum = cust_credit_refund.crednum )
517 ( SELECT SUM(amount) FROM cust_credit_bill
518 WHERE cust_credit.crednum = cust_credit_bill.crednum )
521 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
522 - ( SELECT COALESCE(SUM(paid
524 ( SELECT SUM(amount) FROM cust_bill_pay
525 WHERE cust_pay.paynum = cust_bill_pay.paynum )
529 ( SELECT SUM(amount) FROM cust_pay_refund
530 WHERE cust_pay.paynum = cust_pay_refund.paynum )
533 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
534 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
535 - ( SELECT COALESCE(SUM(paid
537 ( SELECT SUM(amount) FROM cust_bill_pay
538 WHERE cust_pay.paynum = cust_bill_pay.paynum )
542 ( SELECT SUM(amount) FROM cust_pay_refund
543 WHERE cust_pay.paynum = cust_pay_refund.paynum )
546 ), 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
547 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
548 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
549 + ( SELECT COALESCE(SUM(refund
551 ( SELECT SUM(amount) FROM cust_credit_refund
552 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
556 ( SELECT SUM(amount) FROM cust_pay_refund
557 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
560 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
561 - ( SELECT COALESCE(SUM(amount
563 ( SELECT SUM(amount) FROM cust_credit_refund
564 WHERE cust_credit.crednum = cust_credit_refund.crednum )
568 ( SELECT SUM(amount) FROM cust_credit_bill
569 WHERE cust_credit.crednum = cust_credit_bill.crednum )
572 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
573 - ( SELECT COALESCE(SUM(paid
575 ( SELECT SUM(amount) FROM cust_bill_pay
576 WHERE cust_pay.paynum = cust_bill_pay.paynum )
580 ( SELECT SUM(amount) FROM cust_pay_refund
581 WHERE cust_pay.paynum = cust_pay_refund.paynum )
584 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
585 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
586 AS balance_60_90, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
587 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
588 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
589 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
590 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
591 + ( SELECT COALESCE(SUM(refund
593 ( SELECT SUM(amount) FROM cust_credit_refund
594 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
598 ( SELECT SUM(amount) FROM cust_pay_refund
599 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
602 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
603 - ( SELECT COALESCE(SUM(amount
605 ( SELECT SUM(amount) FROM cust_credit_refund
606 WHERE cust_credit.crednum = cust_credit_refund.crednum )
610 ( SELECT SUM(amount) FROM cust_credit_bill
611 WHERE cust_credit.crednum = cust_credit_bill.crednum )
614 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
615 - ( SELECT COALESCE(SUM(paid
617 ( SELECT SUM(amount) FROM cust_bill_pay
618 WHERE cust_pay.paynum = cust_bill_pay.paynum )
622 ( SELECT SUM(amount) FROM cust_pay_refund
623 WHERE cust_pay.paynum = cust_pay_refund.paynum )
626 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
627 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
628 + ( SELECT COALESCE(SUM(refund
630 ( SELECT SUM(amount) FROM cust_credit_refund
631 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
635 ( SELECT SUM(amount) FROM cust_pay_refund
636 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
639 ), 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
640 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
641 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
642 + ( SELECT COALESCE(SUM(refund
644 ( SELECT SUM(amount) FROM cust_credit_refund
645 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
649 ( SELECT SUM(amount) FROM cust_pay_refund
650 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
653 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
654 - ( SELECT COALESCE(SUM(amount
656 ( SELECT SUM(amount) FROM cust_credit_refund
657 WHERE cust_credit.crednum = cust_credit_refund.crednum )
661 ( SELECT SUM(amount) FROM cust_credit_bill
662 WHERE cust_credit.crednum = cust_credit_bill.crednum )
665 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
666 - ( SELECT COALESCE(SUM(paid
668 ( SELECT SUM(amount) FROM cust_bill_pay
669 WHERE cust_pay.paynum = cust_bill_pay.paynum )
673 ( SELECT SUM(amount) FROM cust_pay_refund
674 WHERE cust_pay.paynum = cust_pay_refund.paynum )
677 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
678 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
679 - ( SELECT COALESCE(SUM(amount
681 ( SELECT SUM(amount) FROM cust_credit_refund
682 WHERE cust_credit.crednum = cust_credit_refund.crednum )
686 ( SELECT SUM(amount) FROM cust_credit_bill
687 WHERE cust_credit.crednum = cust_credit_bill.crednum )
690 ), 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
691 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
692 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
693 + ( SELECT COALESCE(SUM(refund
695 ( SELECT SUM(amount) FROM cust_credit_refund
696 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
700 ( SELECT SUM(amount) FROM cust_pay_refund
701 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
704 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
705 - ( SELECT COALESCE(SUM(amount
707 ( SELECT SUM(amount) FROM cust_credit_refund
708 WHERE cust_credit.crednum = cust_credit_refund.crednum )
712 ( SELECT SUM(amount) FROM cust_credit_bill
713 WHERE cust_credit.crednum = cust_credit_bill.crednum )
716 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
717 - ( SELECT COALESCE(SUM(paid
719 ( SELECT SUM(amount) FROM cust_bill_pay
720 WHERE cust_pay.paynum = cust_bill_pay.paynum )
724 ( SELECT SUM(amount) FROM cust_pay_refund
725 WHERE cust_pay.paynum = cust_pay_refund.paynum )
728 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
729 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
730 - ( SELECT COALESCE(SUM(paid
732 ( SELECT SUM(amount) FROM cust_bill_pay
733 WHERE cust_pay.paynum = cust_bill_pay.paynum )
737 ( SELECT SUM(amount) FROM cust_pay_refund
738 WHERE cust_pay.paynum = cust_pay_refund.paynum )
741 ), 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
742 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
743 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
744 + ( SELECT COALESCE(SUM(refund
746 ( SELECT SUM(amount) FROM cust_credit_refund
747 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
751 ( SELECT SUM(amount) FROM cust_pay_refund
752 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
755 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
756 - ( SELECT COALESCE(SUM(amount
758 ( SELECT SUM(amount) FROM cust_credit_refund
759 WHERE cust_credit.crednum = cust_credit_refund.crednum )
763 ( SELECT SUM(amount) FROM cust_credit_bill
764 WHERE cust_credit.crednum = cust_credit_bill.crednum )
767 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
768 - ( SELECT COALESCE(SUM(paid
770 ( SELECT SUM(amount) FROM cust_bill_pay
771 WHERE cust_pay.paynum = cust_bill_pay.paynum )
775 ( SELECT SUM(amount) FROM cust_pay_refund
776 WHERE cust_pay.paynum = cust_pay_refund.paynum )
779 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
780 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
781 AS balance_90_0, ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
782 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
783 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
784 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
785 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
786 + ( SELECT COALESCE(SUM(refund
788 ( SELECT SUM(amount) FROM cust_credit_refund
789 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
793 ( SELECT SUM(amount) FROM cust_pay_refund
794 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
797 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
798 - ( SELECT COALESCE(SUM(amount
800 ( SELECT SUM(amount) FROM cust_credit_refund
801 WHERE cust_credit.crednum = cust_credit_refund.crednum )
805 ( SELECT SUM(amount) FROM cust_credit_bill
806 WHERE cust_credit.crednum = cust_credit_bill.crednum )
809 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
810 - ( SELECT COALESCE(SUM(paid
812 ( SELECT SUM(amount) FROM cust_bill_pay
813 WHERE cust_pay.paynum = cust_bill_pay.paynum )
817 ( SELECT SUM(amount) FROM cust_pay_refund
818 WHERE cust_pay.paynum = cust_pay_refund.paynum )
821 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
822 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
823 + ( SELECT COALESCE(SUM(refund
825 ( SELECT SUM(amount) FROM cust_credit_refund
826 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
830 ( SELECT SUM(amount) FROM cust_pay_refund
831 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
834 ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
835 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
836 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
837 + ( SELECT COALESCE(SUM(refund
839 ( SELECT SUM(amount) FROM cust_credit_refund
840 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
844 ( SELECT SUM(amount) FROM cust_pay_refund
845 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
848 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
849 - ( SELECT COALESCE(SUM(amount
851 ( SELECT SUM(amount) FROM cust_credit_refund
852 WHERE cust_credit.crednum = cust_credit_refund.crednum )
856 ( SELECT SUM(amount) FROM cust_credit_bill
857 WHERE cust_credit.crednum = cust_credit_bill.crednum )
860 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
861 - ( SELECT COALESCE(SUM(paid
863 ( SELECT SUM(amount) FROM cust_bill_pay
864 WHERE cust_pay.paynum = cust_bill_pay.paynum )
868 ( SELECT SUM(amount) FROM cust_pay_refund
869 WHERE cust_pay.paynum = cust_pay_refund.paynum )
872 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
873 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
874 - ( SELECT COALESCE(SUM(amount
876 ( SELECT SUM(amount) FROM cust_credit_refund
877 WHERE cust_credit.crednum = cust_credit_refund.crednum )
881 ( SELECT SUM(amount) FROM cust_credit_bill
882 WHERE cust_credit.crednum = cust_credit_bill.crednum )
885 ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
886 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
887 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
888 + ( SELECT COALESCE(SUM(refund
890 ( SELECT SUM(amount) FROM cust_credit_refund
891 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
895 ( SELECT SUM(amount) FROM cust_pay_refund
896 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
899 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
900 - ( SELECT COALESCE(SUM(amount
902 ( SELECT SUM(amount) FROM cust_credit_refund
903 WHERE cust_credit.crednum = cust_credit_refund.crednum )
907 ( SELECT SUM(amount) FROM cust_credit_bill
908 WHERE cust_credit.crednum = cust_credit_bill.crednum )
911 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
912 - ( SELECT COALESCE(SUM(paid
914 ( SELECT SUM(amount) FROM cust_bill_pay
915 WHERE cust_pay.paynum = cust_bill_pay.paynum )
919 ( SELECT SUM(amount) FROM cust_pay_refund
920 WHERE cust_pay.paynum = cust_pay_refund.paynum )
923 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
924 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
925 - ( SELECT COALESCE(SUM(paid
927 ( SELECT SUM(amount) FROM cust_bill_pay
928 WHERE cust_pay.paynum = cust_bill_pay.paynum )
932 ( SELECT SUM(amount) FROM cust_pay_refund
933 WHERE cust_pay.paynum = cust_pay_refund.paynum )
936 ), 0) FROM cust_pay LEFT JOIN cust_main USING ( custnum ) WHERE ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
937 WHERE cust_bill.invnum = cust_bill_pay.invnum ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
938 WHERE cust_bill.invnum = cust_credit_bill.invnum )), 0) FROM cust_bill WHERE cust_main.custnum = cust_bill.custnum )
939 + ( SELECT COALESCE(SUM(refund
941 ( SELECT SUM(amount) FROM cust_credit_refund
942 WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
946 ( SELECT SUM(amount) FROM cust_pay_refund
947 WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
950 ), 0) FROM cust_refund WHERE cust_main.custnum = cust_refund.custnum )
951 - ( SELECT COALESCE(SUM(amount
953 ( SELECT SUM(amount) FROM cust_credit_refund
954 WHERE cust_credit.crednum = cust_credit_refund.crednum )
958 ( SELECT SUM(amount) FROM cust_credit_bill
959 WHERE cust_credit.crednum = cust_credit_bill.crednum )
962 ), 0) FROM cust_credit WHERE cust_main.custnum = cust_credit.custnum )
963 - ( SELECT COALESCE(SUM(paid
965 ( SELECT SUM(amount) FROM cust_bill_pay
966 WHERE cust_pay.paynum = cust_bill_pay.paynum )
970 ( SELECT SUM(amount) FROM cust_pay_refund
971 WHERE cust_pay.paynum = cust_pay_refund.paynum )
974 ), 0) FROM cust_pay WHERE cust_main.custnum = cust_pay.custnum )
975 > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )