257 |
257 |
(
|
258 |
258 |
SELECT trans_id
|
259 |
259 |
FROM acc_trans a
|
260 |
|
JOIN chart c ON (a.chart_id = c.id)
|
261 |
|
WHERE (link LIKE '%AR_paid%')
|
|
260 |
WHERE (a.chart_link LIKE '%AR_paid%')
|
262 |
261 |
$subwhere
|
263 |
262 |
)
|
264 |
263 |
$project
|
... | ... | |
277 |
276 |
(
|
278 |
277 |
SELECT trans_id
|
279 |
278 |
FROM acc_trans a
|
280 |
|
JOIN chart c ON (a.chart_id = c.id)
|
281 |
|
WHERE (link LIKE '%AP_paid%')
|
|
279 |
WHERE (a.chart_link LIKE '%AP_paid%')
|
282 |
280 |
$subwhere
|
283 |
281 |
)
|
284 |
282 |
$project
|
... | ... | |
294 |
292 |
$glwhere
|
295 |
293 |
$dpt_where
|
296 |
294 |
$category
|
297 |
|
AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
|
|
295 |
AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP'))
|
298 |
296 |
$project
|
299 |
297 |
GROUP BY c.accno, c.description, c.category |;
|
300 |
298 |
|
... | ... | |
317 |
315 |
(
|
318 |
316 |
SELECT trans_id
|
319 |
317 |
FROM acc_trans a
|
320 |
|
JOIN chart c ON (a.chart_id = c.id)
|
321 |
|
WHERE (link LIKE '%AR_paid%')
|
|
318 |
WHERE (a.chart_link LIKE '%AR_paid%')
|
322 |
319 |
$subwhere
|
323 |
320 |
)
|
324 |
321 |
$project
|
... | ... | |
338 |
335 |
(
|
339 |
336 |
SELECT trans_id
|
340 |
337 |
FROM acc_trans a
|
341 |
|
JOIN chart c ON (a.chart_id = c.id)
|
342 |
|
WHERE link LIKE '%AP_paid%'
|
|
338 |
WHERE a.chart_link LIKE '%AP_paid%'
|
343 |
339 |
$subwhere
|
344 |
340 |
)
|
345 |
341 |
$project
|
... | ... | |
512 |
508 |
/* ar amount is not zero, so we can divide by amount */
|
513 |
509 |
(SELECT SUM(acc.amount) * -1
|
514 |
510 |
FROM acc_trans acc
|
515 |
|
INNER JOIN chart c ON (acc.chart_id = c.id AND c.link LIKE '%AR_paid%')
|
516 |
|
WHERE 1=1 $inwhere AND acc.trans_id = ac.trans_id)
|
|
511 |
WHERE 1=1 $inwhere
|
|
512 |
AND acc.trans_id = ac.trans_id
|
|
513 |
AND acc.chart_link LIKE '%AR_paid%')
|
517 |
514 |
/ (SELECT amount FROM ar WHERE id = ac.trans_id)
|
518 |
515 |
ELSE 0
|
519 |
516 |
/* ar amount is zero, or we are checking with a non-ar-transaction, so we return 0 in both cases as multiplicator of ac.amount */
|
... | ... | |
539 |
536 |
JOIN chart c ON (c.id = ac.chart_id)
|
540 |
537 |
JOIN ar a ON (a.id = ac.trans_id)
|
541 |
538 |
WHERE $where $dpt_where
|
542 |
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
|
|
539 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
|
543 |
540 |
$project
|
544 |
541 |
GROUP BY c.$category
|
545 |
542 |
*/
|
... | ... | |
550 |
547 |
JOIN chart c ON (c.id = ac.chart_id)
|
551 |
548 |
JOIN ap a ON (a.id = ac.trans_id)
|
552 |
549 |
WHERE $where $dpt_where
|
553 |
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
|
|
550 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
|
554 |
551 |
$project
|
555 |
552 |
GROUP BY c.$category
|
556 |
553 |
|
... | ... | |
561 |
558 |
JOIN chart c ON (c.id = ac.chart_id)
|
562 |
559 |
JOIN gl a ON (a.id = ac.trans_id)
|
563 |
560 |
WHERE $where $dpt_where $glwhere
|
564 |
|
AND NOT ((c.link = 'AR') OR (c.link = 'AP'))
|
|
561 |
AND NOT ((ac.chart_link = 'AR') OR (ac.chart_link = 'AP'))
|
565 |
562 |
$project
|
566 |
563 |
GROUP BY c.$category
|
567 |
564 |
|;
|
... | ... | |
576 |
573 |
JOIN parts p ON (ac.parts_id = p.id)
|
577 |
574 |
JOIN chart c on (p.income_accno_id = c.id)
|
578 |
575 |
WHERE (c.category = 'I') $prwhere $dpt_where
|
579 |
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AR_paid%') $subwhere)
|
|
576 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AR_paid%') $subwhere)
|
580 |
577 |
$project
|
581 |
578 |
GROUP BY c.$category
|
582 |
579 |
|
... | ... | |
588 |
585 |
JOIN parts p ON (ac.parts_id = p.id)
|
589 |
586 |
JOIN chart c on (p.expense_accno_id = c.id)
|
590 |
587 |
WHERE (c.category = 'E') $prwhere $dpt_where
|
591 |
|
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a JOIN chart c ON (a.chart_id = c.id) WHERE (link LIKE '%AP_paid%') $subwhere)
|
|
588 |
AND ac.trans_id IN ( SELECT trans_id FROM acc_trans a WHERE (a.chart_link LIKE '%AP_paid%') $subwhere)
|
592 |
589 |
$project
|
593 |
590 |
GROUP BY c.$category
|
594 |
591 |
|;
|
... | ... | |
1222 |
1219 |
street, zipcode, city, country, contact, email,
|
1223 |
1220 |
phone as customerphone, fax as customerfax, ${ct}number,
|
1224 |
1221 |
"invnumber", "transdate",
|
1225 |
|
(amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans LEFT JOIN chart ON (acc_trans.chart_id=chart.id) WHERE link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount",
|
|
1222 |
(amount - COALESCE((SELECT sum(amount)*$ml FROM acc_trans WHERE chart_link ilike '%paid%' AND acc_trans.trans_id=${arap}.id AND acc_trans.transdate <= (date $todate)),0)) as "open", "amount",
|
1226 |
1223 |
"duedate", invoice, ${arap}.id, date_part('days', now() - duedate) as overduedays,
|
1227 |
1224 |
(SELECT $buysell
|
1228 |
1225 |
FROM exchangerate
|
... | ... | |
1347 |
1344 |
(
|
1348 |
1345 |
SELECT trans_id
|
1349 |
1346 |
FROM acc_trans a
|
1350 |
|
JOIN chart c ON (a.chart_id = c.id)
|
1351 |
|
WHERE (link LIKE '%${ARAP}_paid%')
|
|
1347 |
WHERE (a.chart_link LIKE '%${ARAP}_paid%')
|
1352 |
1348 |
AND (transdate <= $todate)
|
1353 |
1349 |
)
|
1354 |
1350 |
|;
|
chart_link auslesen
Die Spalte chart_link aus der acc_trans wird jetzt auch in der
ausgelesen (im Datev-Export und bei Berichten).