Revision 4000272e
Von Jan Büren vor mehr als 13 Jahren hinzugefügt
| SL/GL.pm | ||
|---|---|---|
| 231 | 231 |
push(@apvalues, '%' . $form->{source} . '%');
|
| 232 | 232 |
} |
| 233 | 233 |
|
| 234 |
# default Datumseinschränkung falls nicht oder falsch übergeben (sollte nie passieren) |
|
| 235 |
$form->{datesort} = 'transdate' unless $form->{datesort} =~ /^(transdate|gldate)$/;
|
|
| 236 |
|
|
| 234 | 237 |
if ($form->{datefrom}) {
|
| 235 |
$glwhere .= " AND ac.transdate >= ?";
|
|
| 236 |
$arwhere .= " AND ac.transdate >= ?";
|
|
| 237 |
$apwhere .= " AND ac.transdate >= ?";
|
|
| 238 |
$glwhere .= " AND ac.$form->{datesort} >= ?";
|
|
| 239 |
$arwhere .= " AND ac.$form->{datesort} >= ?";
|
|
| 240 |
$apwhere .= " AND ac.$form->{datesort} >= ?";
|
|
| 238 | 241 |
push(@glvalues, $form->{datefrom});
|
| 239 | 242 |
push(@arvalues, $form->{datefrom});
|
| 240 | 243 |
push(@apvalues, $form->{datefrom});
|
| 241 | 244 |
} |
| 242 | 245 |
|
| 243 | 246 |
if ($form->{dateto}) {
|
| 244 |
$glwhere .= " AND ac.transdate <= ?";
|
|
| 245 |
$arwhere .= " AND ac.transdate <= ?";
|
|
| 246 |
$apwhere .= " AND ac.transdate <= ?";
|
|
| 247 |
$glwhere .= " AND ac.$form->{datesort} <= ?";
|
|
| 248 |
$arwhere .= " AND ac.$form->{datesort} <= ?";
|
|
| 249 |
$apwhere .= " AND ac.$form->{datesort} <= ?";
|
|
| 247 | 250 |
push(@glvalues, $form->{dateto});
|
| 248 | 251 |
push(@arvalues, $form->{dateto});
|
| 249 | 252 |
push(@apvalues, $form->{dateto});
|
| ... | ... | |
| 257 | 260 |
push(@arvalues, '%' . $form->{description} . '%');
|
| 258 | 261 |
push(@apvalues, '%' . $form->{description} . '%');
|
| 259 | 262 |
} |
| 263 |
|
|
| 264 |
if ($form->{employee} =~ /--/) {
|
|
| 265 |
($form->{employee_id},$form->{employee_name}) = split(/--/,$form->{employee});
|
|
| 266 |
$query .= " AND o.employee_id = ?"; |
|
| 267 |
push @values, conv_i($form->{employee_id});
|
|
| 268 |
#if ($form->{employee_id}) {
|
|
| 269 |
$glwhere .= " AND g.employee_id = ? "; |
|
| 270 |
$arwhere .= " AND a.employee_id = ? "; |
|
| 271 |
$apwhere .= " AND a.employee_id = ? "; |
|
| 272 |
push(@glvalues, conv_i($form->{employee_id}));
|
|
| 273 |
push(@arvalues, conv_i($form->{employee_id}));
|
|
| 274 |
push(@apvalues, conv_i($form->{employee_id}));
|
|
| 275 |
} |
|
| 260 | 276 |
|
| 261 | 277 |
if ($form->{notes}) {
|
| 262 | 278 |
$glwhere .= " AND g.notes ILIKE ?"; |
| ... | ... | |
| 312 | 328 |
qq|SELECT SUM(ac.amount) |
| 313 | 329 |
FROM acc_trans ac |
| 314 | 330 |
LEFT JOIN chart c ON (ac.chart_id = c.id) |
| 315 |
WHERE (c.accno = ?) AND (ac.transdate < ?)|;
|
|
| 331 |
WHERE (c.accno = ?) AND (ac.$form->{datesort} < ?)|;
|
|
| 316 | 332 |
($form->{balance}) = selectrow_query($form, $dbh, $query, $form->{accno}, conv_date($form->{datefrom}));
|
| 317 | 333 |
} |
| 318 | 334 |
} |
| ... | ... | |
| 322 | 338 |
my %sort_columns = ( |
| 323 | 339 |
'id' => [ qw(id) ], |
| 324 | 340 |
'transdate' => [ qw(transdate id) ], |
| 341 |
'gldate' => [ qw(gldate id) ], |
|
| 325 | 342 |
'reference' => [ qw(lower_reference id) ], |
| 326 | 343 |
'description' => [ qw(lower_description id) ], |
| 327 | 344 |
'accno' => [ qw(accno transdate id) ], |
| ... | ... | |
| 331 | 348 |
'source' => { 'gl' => 'ac.source', 'arap' => 'ac.source', },
|
| 332 | 349 |
'description' => { 'gl' => 'g.description', 'arap' => 'ct.name', },
|
| 333 | 350 |
); |
| 334 |
|
|
| 351 |
|
|
| 352 |
# sortdir = sort direction (ascending or descending) |
|
| 335 | 353 |
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
|
| 336 |
my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : 'transdate';
|
|
| 354 |
my $sortkey = $sort_columns{$form->{sort}} ? $form->{sort} : $form->{datesort}; # default used to be transdate
|
|
| 337 | 355 |
my $sortorder = join ', ', map { "$_ $sortdir" } @{ $sort_columns{$sortkey} };
|
| 338 | 356 |
|
| 339 | 357 |
my %columns_for_sorting = ( 'gl' => '', 'arap' => '', ); |
| ... | ... | |
| 347 | 365 |
$query = |
| 348 | 366 |
qq|SELECT |
| 349 | 367 |
ac.acc_trans_id, g.id, 'gl' AS type, $false AS invoice, g.reference, ac.taxkey, c.link, |
| 350 |
g.description, ac.transdate, ac.source, ac.trans_id, |
|
| 351 |
ac.amount, c.accno, g.notes, t.chart_id |
|
| 368 |
g.description, ac.transdate, ac.gldate, ac.source, ac.trans_id, |
|
| 369 |
ac.amount, c.accno, g.notes, t.chart_id, |
|
| 370 |
CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee |
|
| 352 | 371 |
$project_columns |
| 353 | 372 |
$columns_for_sorting{gl}
|
| 354 | 373 |
FROM gl g, acc_trans ac $project_join, chart c |
| ... | ... | |
| 360 | 379 |
UNION |
| 361 | 380 |
|
| 362 | 381 |
SELECT ac.acc_trans_id, a.id, 'ar' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, |
| 363 |
ct.name, ac.transdate, ac.source, ac.trans_id, |
|
| 364 |
ac.amount, c.accno, a.notes, t.chart_id |
|
| 382 |
ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id, |
|
| 383 |
ac.amount, c.accno, a.notes, t.chart_id, |
|
| 384 |
CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee |
|
| 365 | 385 |
$project_columns |
| 366 | 386 |
$columns_for_sorting{arap}
|
| 367 | 387 |
FROM ar a, acc_trans ac $project_join, customer ct, chart c |
| ... | ... | |
| 374 | 394 |
UNION |
| 375 | 395 |
|
| 376 | 396 |
SELECT ac.acc_trans_id, a.id, 'ap' AS type, a.invoice, a.invnumber, ac.taxkey, c.link, |
| 377 |
ct.name, ac.transdate, ac.source, ac.trans_id, |
|
| 378 |
ac.amount, c.accno, a.notes, t.chart_id |
|
| 397 |
ct.name, ac.transdate, ac.gldate, ac.source, ac.trans_id, |
|
| 398 |
ac.amount, c.accno, a.notes, t.chart_id, |
|
| 399 |
CASE WHEN (COALESCE(e.name, '') = '') THEN e.login ELSE e.name END AS employee |
|
| 379 | 400 |
$project_columns |
| 380 | 401 |
$columns_for_sorting{arap}
|
| 381 | 402 |
FROM ap a, acc_trans ac $project_join, vendor ct, chart c |
| ... | ... | |
| 386 | 407 |
AND (a.id = ac.trans_id) |
| 387 | 408 |
|
| 388 | 409 |
ORDER BY $sortorder, acc_trans_id $sortdir|; |
| 410 |
# ORDER BY gldate DESC, id DESC, acc_trans_id DESC |
|
| 389 | 411 |
|
| 390 | 412 |
my @values = (@glvalues, @arvalues, @apvalues); |
| 391 | 413 |
|
Auch abrufbar als: Unified diff
Buchungsjournal: Bearbeiter und Buchungsdatum
- Buchungsjournal: Auswahl nach Bearbeiter einschränken
- Sortierung: Auswahl ob Buchungsjournal nach Buchungsdatum oder
Rechnungsdatum sortiert wird (Buchungsdatum ist Default)
- Datumsauswahl von-bis berücksichtigt ebenfalls Auswahl
Buchungsdatum/Rechnungsdatum