Revision 8c94b0ea
Von Philip Reetz vor mehr als 16 Jahren hinzugefügt
| SL/CA.pm | ||
|---|---|---|
| 188 | 188 |
($form->{category}) = selectrow_query($form, $dbh, $query, $form->{accno});
|
| 189 | 189 |
|
| 190 | 190 |
if ($form->{fromdate}) {
|
| 191 |
# get beginning balance |
|
| 191 |
# get beginning balances
|
|
| 192 | 192 |
$query = |
| 193 |
qq|SELECT SUM(ac.amount) | . |
|
| 194 |
qq|FROM acc_trans ac | . |
|
| 195 |
qq|JOIN chart c ON (ac.chart_id = c.id) | . |
|
| 196 |
$dpt_join . |
|
| 197 |
qq|WHERE c.accno = ? | . |
|
| 198 |
qq|AND ac.transdate < ? | . |
|
| 199 |
$dpt_where . |
|
| 200 |
$project; |
|
| 201 |
@values = ($form->{accno}, conv_date($form->{fromdate}),
|
|
| 202 |
@department_values, @project_values); |
|
| 203 |
|
|
| 204 |
if ($form->{project_id}) {
|
|
| 205 |
$query .= |
|
| 206 |
qq|UNION | . |
|
| 207 |
|
|
| 208 |
qq|SELECT SUM(ac.qty * ac.sellprice) | . |
|
| 209 |
qq|FROM invoice ac | . |
|
| 210 |
qq|JOIN ar a ON (ac.trans_id = a.id) | . |
|
| 211 |
qq|JOIN parts p ON (ac.parts_id = p.id) | . |
|
| 212 |
qq|JOIN chart c ON (p.income_accno_id = c.id) | . |
|
| 213 |
$dpt_join . |
|
| 214 |
qq|WHERE c.accno = ? | . |
|
| 215 |
qq| AND a.transdate < ? | . |
|
| 216 |
qq| AND c.category = 'I' | . |
|
| 217 |
$dpt_where . |
|
| 218 |
$project . |
|
| 219 |
|
|
| 220 |
qq|UNION | . |
|
| 221 |
|
|
| 222 |
qq|SELECT SUM(ac.qty * ac.sellprice) | . |
|
| 223 |
qq|FROM invoice ac | . |
|
| 224 |
qq|JOIN ap a ON (ac.trans_id = a.id) | . |
|
| 225 |
qq|JOIN parts p ON (ac.parts_id = p.id) | . |
|
| 226 |
qq|JOIN chart c ON (p.expense_accno_id = c.id) | . |
|
| 227 |
$dpt_join . |
|
| 228 |
qq|WHERE c.accno = ? | . |
|
| 229 |
qq| AND a.transdate < ? | . |
|
| 230 |
qq| AND c.category = 'E' | . |
|
| 231 |
$dpt_where . |
|
| 232 |
$project; |
|
| 233 |
|
|
| 234 |
push(@values, |
|
| 235 |
$form->{accno}, conv_date($form->{transdate}),
|
|
| 236 |
@department_values, @project_values, |
|
| 237 |
$form->{accno}, conv_date($form->{transdate}),
|
|
| 238 |
@department_values, @project_values); |
|
| 239 |
} |
|
| 193 |
qq|SELECT SUM(ac.amount) AS amount |
|
| 194 |
FROM acc_trans ac |
|
| 195 |
JOIN chart c ON (ac.chart_id = c.id) |
|
| 196 |
$dpt_join |
|
| 197 |
WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.ob_transaction
|
|
| 198 |
$dpt_where |
|
| 199 |
$project |
|
| 200 |
AND c.accno = ?|; |
|
| 201 |
|
|
| 202 |
($form->{beginning_balance}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
|
| 203 |
|
|
| 204 |
# get last transaction date |
|
| 205 |
my $todate = ($form->{todate}) ? " AND ac.transdate <= '$form->{todate}' " : "";
|
|
| 206 |
$query = qq|SELECT max(ac.transdate) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ?|;
|
|
| 207 |
($form->{last_transaction}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
|
| 208 |
|
|
| 209 |
# get old saldo |
|
| 210 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ?|;
|
|
| 211 |
($form->{saldo_old}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
|
| 212 |
|
|
| 213 |
#get old balance |
|
| 214 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 215 |
($form->{old_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
|
| 240 | 216 |
|
| 241 |
($form->{balance}) = selectrow_query($form, $dbh, $query, @values);
|
|
| 217 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) AND ac.transdate < ? AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 218 |
($form->{old_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{fromdate}, $form->{accno});
|
|
| 219 |
|
|
| 220 |
# get current saldo |
|
| 221 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
|
| 222 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ?|;
|
|
| 223 |
($form->{saldo_new}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
|
| 224 |
|
|
| 225 |
#get current balance |
|
| 226 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
|
| 227 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount < 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 228 |
($form->{current_balance_debit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
|
| 229 |
|
|
| 230 |
my $todate = ($form->{todate} ne "") ? " AND ac.transdate <= '$form->{todate}' " : "";
|
|
| 231 |
$query = qq|SELECT sum(ac.amount) FROM acc_trans ac LEFT JOIN chart c ON (ac.chart_id = c.id)WHERE ((select date_trunc('year', ac.transdate::date)) = (select date_trunc('year', ?::date))) $todate AND c.accno = ? AND ac.amount > 0 AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL)|;
|
|
| 232 |
($form->{current_balance_credit}) = selectrow_query($form, $dbh, $query, $form->{fromdate}, $form->{accno});
|
|
| 242 | 233 |
} |
| 243 | 234 |
} |
| 244 | 235 |
|
| ... | ... | |
| 258 | 249 |
# JOIN ... that also works. |
| 259 | 250 |
|
| 260 | 251 |
# get all transactions |
| 261 |
$query .=
|
|
| 262 |
$union .
|
|
| 263 |
qq|SELECT a.id, a.reference, a.description, ac.transdate, | .
|
|
| 264 |
qq| $false AS invoice, ac.amount, 'gl' as module | .
|
|
| 252 |
$query = |
|
| 253 |
qq|SELECT a.id, a.reference, a.description, ac.transdate, ac.chart_id, | .
|
|
| 254 |
qq| $false AS invoice, ac.amount, 'gl' as module, | .
|
|
| 255 |
qq?(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo ? .
|
|
| 265 | 256 |
qq|FROM acc_trans ac, gl a | . |
| 266 | 257 |
$dpt_join . |
| 267 | 258 |
qq|WHERE | . $where . $dpt_where . $project . |
| 268 | 259 |
qq| AND ac.chart_id = ? | . |
| 269 | 260 |
qq| AND ac.trans_id = a.id | . |
| 261 |
qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) | . |
|
| 270 | 262 |
|
| 271 | 263 |
qq|UNION ALL | . |
| 272 | 264 |
|
| 273 |
qq|SELECT a.id, a.invnumber, c.name, ac.transdate, | . |
|
| 274 |
qq| a.invoice, ac.amount, 'ar' as module | . |
|
| 265 |
qq|SELECT a.id, a.invnumber, c.name, ac.transdate, ac.chart_id, | . |
|
| 266 |
qq| a.invoice, ac.amount, 'ar' as module, | . |
|
| 267 |
qq?(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo ? . |
|
| 275 | 268 |
qq|FROM acc_trans ac, customer c, ar a | . |
| 276 | 269 |
$dpt_join . |
| 277 | 270 |
qq|WHERE | . $where . $dpt_where . $project . |
| 278 | 271 |
qq| AND ac.chart_id = ? | . |
| 279 |
qq| AND NOT a.storno | . |
|
| 280 | 272 |
qq| AND ac.trans_id = a.id | . |
| 281 | 273 |
qq| AND a.customer_id = c.id | . |
| 274 |
qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) | . |
|
| 282 | 275 |
|
| 283 | 276 |
qq|UNION ALL | . |
| 284 | 277 |
|
| 285 |
qq|SELECT a.id, a.invnumber, v.name, ac.transdate, | . |
|
| 286 |
qq| a.invoice, ac.amount, 'ap' as module | . |
|
| 278 |
qq|SELECT a.id, a.invnumber, v.name, ac.transdate, ac.chart_id, | . |
|
| 279 |
qq| a.invoice, ac.amount, 'ap' as module, | . |
|
| 280 |
qq?(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo ? . |
|
| 287 | 281 |
qq|FROM acc_trans ac, vendor v, ap a | . |
| 288 | 282 |
$dpt_join . |
| 289 | 283 |
qq|WHERE | . $where . $dpt_where . $project . |
| 290 | 284 |
qq| AND ac.chart_id = ? | . |
| 291 | 285 |
qq| AND ac.trans_id = a.id | . |
| 292 |
qq| AND NOT a.storno | . |
|
| 293 | 286 |
qq| AND a.vendor_id = v.id |; |
| 287 |
qq| AND (NOT ac.ob_transaction OR ac.ob_transaction IS NULL) | . |
|
| 294 | 288 |
|
| 295 | 289 |
push(@values, |
| 296 | 290 |
@where_values, @department_values, @project_values, $id, |
| ... | ... | |
| 308 | 302 |
qq|UNION ALL | . |
| 309 | 303 |
|
| 310 | 304 |
qq|SELECT a.id, a.invnumber, c.name, a.transdate, | . |
| 311 |
qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module | . |
|
| 305 |
qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ar' as module, | . |
|
| 306 |
qq?(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo ? . |
|
| 312 | 307 |
qq|FROM ar a | . |
| 313 | 308 |
qq|JOIN invoice ac ON (ac.trans_id = a.id) | . |
| 314 | 309 |
qq|JOIN parts p ON (ac.parts_id = p.id) | . |
| ... | ... | |
| 323 | 318 |
qq|UNION ALL | . |
| 324 | 319 |
|
| 325 | 320 |
qq|SELECT a.id, a.invnumber, v.name, a.transdate, | . |
| 326 |
qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module | . |
|
| 321 |
qq| a.invoice, ac.qty * ac.sellprice AS sellprice, 'ap' as module, | . |
|
| 322 |
qq?(SELECT accno||'--'||rate FROM tax LEFT JOIN chart ON (tax.chart_id=chart.id) WHERE tax.id = (SELECT tax_id FROM taxkeys WHERE taxkey_id = ac.taxkey AND taxkeys.startdate <= ac.transdate ORDER BY taxkeys.startdate DESC LIMIT 1)) AS taxinfo ? . |
|
| 327 | 323 |
qq|FROM ap a | . |
| 328 | 324 |
qq|JOIN invoice ac ON (ac.trans_id = a.id) | . |
| 329 | 325 |
qq|JOIN parts p ON (ac.parts_id = p.id) | . |
| ... | ... | |
| 352 | 348 |
$query .= qq|ORDER BY $sort|; |
| 353 | 349 |
$sth = prepare_execute_query($form, $dbh, $query, @values); |
| 354 | 350 |
|
| 351 |
#get detail information for each transaction |
|
| 352 |
$trans_query = |
|
| 353 |
qq|SELECT accno, | . |
|
| 354 |
qq|amount, transdate FROM acc_trans LEFT JOIN chart ON (chart_id=chart.id) WHERE | . |
|
| 355 |
qq|trans_id = ? AND sign(amount) <> sign(?) AND chart_id <> ? AND transdate = ?|; |
|
| 356 |
my $trans_sth = $dbh->prepare($trans_query); |
|
| 357 |
|
|
| 355 | 358 |
$form->{CA} = [];
|
| 356 | 359 |
while (my $ca = $sth->fetchrow_hashref(NAME_lc)) {
|
| 357 | 360 |
# ap |
| ... | ... | |
| 372 | 375 |
$ca->{debit} = 0;
|
| 373 | 376 |
} |
| 374 | 377 |
|
| 375 |
$ca->{index} = join "--", map { $ca->{$_} } qw(id reference description);
|
|
| 378 |
($ca->{ustkonto},$ca->{ustrate}) = split /--/, $ca->{taxinfo};
|
|
| 379 |
|
|
| 380 |
#get detail information for this transaction |
|
| 381 |
$trans_sth->execute($ca->{id}, $ca->{amount}, $ca->{chart_id}, $ca->{transdate}) ||
|
|
| 382 |
$form->dberror($trans_query . " (" . join(", ", $ca->{id}) . ")");
|
|
| 383 |
while (my $trans = $trans_sth->fetchrow_hashref(NAME_lc)) {
|
|
| 384 |
if (($ca->{transdate} eq $trans->{transdate}) && ($ca->{amount} * $trans->{amount} < 0)) {
|
|
| 385 |
if ($trans->{amount} < 0) {
|
|
| 386 |
$trans->{debit} = $trans->{amount} * -1;
|
|
| 387 |
$trans->{credit} = 0;
|
|
| 388 |
} else {
|
|
| 389 |
$trans->{credit} = $trans->{amount};
|
|
| 390 |
$trans->{debit} = 0;
|
|
| 391 |
} |
|
| 392 |
push(@{ $ca->{GEGENKONTO} }, $trans);
|
|
| 393 |
} else {
|
|
| 394 |
next; |
|
| 395 |
} |
|
| 396 |
} |
|
| 397 |
|
|
| 398 |
$ca->{index} = join "--", map { $ca->{$_} } qw(id reference description transdate);
|
|
| 376 | 399 |
|
| 377 | 400 |
push(@{ $form->{CA} }, $ca);
|
| 378 | 401 |
|
Auch abrufbar als: Unified diff
Ueberarbeiteter Kontenbericht