Revision 0228e31c
Von Bernd Bleßmann vor 12 Monaten hinzugefügt
| SL/DO.pm | ||
|---|---|---|
| 72 | 72 |
|
| 73 | 73 |
my $vc = $form->{vc} eq "customer" ? "customer" : "vendor";
|
| 74 | 74 |
|
| 75 |
my ($extra_selects, $extra_joins) = ('', '');
|
|
| 76 |
|
|
| 77 | 75 |
$form->{l_order_confirmation_number} = 'Y' if $form->{order_confirmation_number};
|
| 78 |
if ($form->{l_order_confirmation_number}) {
|
|
| 79 |
$extra_selects = qq|, oe.ordnumber AS order_confirmation_number|; |
|
| 80 |
$extra_joins = qq|LEFT JOIN record_links rl ON (rl.to_id = dord.id) |
|
| 81 |
LEFT JOIN oe ON (oe.id = rl.from_id)|; |
|
| 82 |
} |
|
| 83 | 76 |
|
| 84 | 77 |
my $query = |
| 85 | 78 |
qq|SELECT dord.id, dord.donumber, dord.ordnumber, dord.cusordnumber, |
| ... | ... | |
| 94 | 87 |
dord.record_type, |
| 95 | 88 |
e.name AS employee, |
| 96 | 89 |
sm.name AS salesman |
| 97 |
$extra_selects |
|
| 98 | 90 |
FROM delivery_orders dord |
| 99 | 91 |
LEFT JOIN $vc ct ON (dord.${vc}_id = ct.id)
|
| 100 | 92 |
LEFT JOIN contacts cp ON (dord.cp_id = cp.cp_id) |
| ... | ... | |
| 102 | 94 |
LEFT JOIN employee sm ON (dord.salesman_id = sm.id) |
| 103 | 95 |
LEFT JOIN project pr ON (dord.globalproject_id = pr.id) |
| 104 | 96 |
LEFT JOIN department dep ON (dord.department_id = dep.id) |
| 105 |
$extra_joins |
|
| 106 | 97 |
|; |
| 107 | 98 |
|
| 108 |
if ($form->{l_order_confirmation_number}) {
|
|
| 109 |
push @where, qq|rl.to_table = 'delivery_orders' AND rl.from_table = 'oe' AND oe.record_type::text LIKE 'purchase_order_confirmation'|; |
|
| 110 |
} |
|
| 111 |
|
|
| 112 | 99 |
if ($form->{type} && is_valid_type($form->{type})) {
|
| 113 | 100 |
push @where, 'dord.record_type = ?'; |
| 114 | 101 |
push @values, $form->{type};
|
| ... | ... | |
| 164 | 151 |
} |
| 165 | 152 |
|
| 166 | 153 |
if ($form->{order_confirmation_number}) {
|
| 167 |
push @where, qq|oe.ordnumber ILIKE ?|; |
|
| 154 |
push @where, qq|(EXISTS (SELECT id FROM oe |
|
| 155 |
WHERE ordnumber ILIKE ? |
|
| 156 |
AND record_type = 'purchase_order_confirmation' |
|
| 157 |
AND id IN |
|
| 158 |
(SELECT from_id FROM record_links |
|
| 159 |
WHERE to_id = dord.id |
|
| 160 |
AND to_table = 'delivery_orders' |
|
| 161 |
AND from_table = 'oe') |
|
| 162 |
))|; |
|
| 168 | 163 |
push @values, like($form->{order_confirmation_number});
|
| 169 | 164 |
} |
| 170 | 165 |
|
| ... | ... | |
| 313 | 308 |
"department" => "lower(dep.description)", |
| 314 | 309 |
"insertdate" => "dord.itime", |
| 315 | 310 |
"vendor_confirmation_number" => "dord.vendor_confirmation_number", |
| 316 |
"order_confirmation_number" => "order_confirmation_number", |
|
| 317 | 311 |
); |
| 318 | 312 |
|
| 319 | 313 |
my $sortdir = !defined $form->{sortdir} ? 'ASC' : $form->{sortdir} ? 'ASC' : 'DESC';
|
| ... | ... | |
| 348 | 342 |
$items_sth = prepare_query($form, $dbh, $items_query); |
| 349 | 343 |
} |
| 350 | 344 |
|
| 345 |
my ($order_confirmation_query, $order_confirmation_sth); |
|
| 346 |
if ($form->{l_order_confirmation_number}) {
|
|
| 347 |
$order_confirmation_query = |
|
| 348 |
qq|SELECT id, ordnumber FROM oe |
|
| 349 |
WHERE id IN |
|
| 350 |
(SELECT from_id FROM record_links |
|
| 351 |
WHERE from_table = 'oe' |
|
| 352 |
AND to_table = 'delivery_orders' |
|
| 353 |
AND to_id = ?) |
|
| 354 |
AND record_type = 'purchase_order_confirmation' ORDER BY ordnumber|; |
|
| 355 |
|
|
| 356 |
$order_confirmation_sth = prepare_query($form, $dbh, $order_confirmation_query); |
|
| 357 |
} |
|
| 358 |
|
|
| 351 | 359 |
foreach my $dord (@{ $form->{DO} }) {
|
| 352 | 360 |
if ($form->{l_items}) {
|
| 353 | 361 |
do_statement($form, $items_sth, $items_query, $dord->{id});
|
| ... | ... | |
| 355 | 363 |
$dord->{item_ids} = undef if !@{$dord->{item_ids}};
|
| 356 | 364 |
} |
| 357 | 365 |
|
| 366 |
if ($form->{l_order_confirmation_number}) {
|
|
| 367 |
do_statement($form, $order_confirmation_sth, $order_confirmation_query, $dord->{id});
|
|
| 368 |
my @r = @{$order_confirmation_sth->fetchall_arrayref()};
|
|
| 369 |
push @{$dord->{order_confirmation_numbers}}, { id => $_->[0], number => $_->[1] } for @r;
|
|
| 370 |
} |
|
| 371 |
|
|
| 358 | 372 |
next unless ($dord->{ordnumber});
|
| 359 | 373 |
do_statement($form, $sth, $query, $dord->{ordnumber});
|
| 360 | 374 |
($dord->{oe_id}) = $sth->fetchrow_array();
|
| 361 | 375 |
} |
| 362 | 376 |
|
| 363 | 377 |
$sth->finish(); |
| 364 |
$items_sth->finish() if $form->{l_items};
|
|
| 378 |
$items_sth->finish() if $form->{l_items};
|
|
| 379 |
$order_confirmation_sth->finish() if $form->{l_order_confirmation_number};
|
|
| 365 | 380 |
} |
| 366 | 381 |
|
| 367 | 382 |
$main::lxdebug->leave_sub(); |
Auch abrufbar als: Unified diff
Lieferscheinbericht: Fix: Auftragsbestätigungsnummer anzeigen und filtern können