Revision cc06de5e
Von Niclas Zimmermann vor mehr als 11 Jahren hinzugefügt
| SL/RP.pm | ||
|---|---|---|
| 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 |
|; |
Auch abrufbar als: Unified diff
chart_link auslesen
Die Spalte chart_link aus der acc_trans wird jetzt auch in der
ausgelesen (im Datev-Export und bei Berichten).