Revision 5df2b57a
Von Moritz Bunkus vor mehr als 16 Jahren hinzugefügt
| SL/DO.pm | ||
|---|---|---|
| 39 | 39 |
use SL::AM; |
| 40 | 40 |
use SL::Common; |
| 41 | 41 |
use SL::DBUtils; |
| 42 |
use SL::RecordLinks; |
|
| 42 | 43 |
|
| 43 | 44 |
sub transactions {
|
| 44 | 45 |
$main::lxdebug->enter_sub(); |
| ... | ... | |
| 342 | 343 |
# save printed, emailed, queued |
| 343 | 344 |
$form->save_status($dbh); |
| 344 | 345 |
|
| 345 |
$self->mark_order_if_delivered('do_id' => $form->{id},
|
|
| 346 |
'type' => $form->{type} eq 'sales_delivery_order' ? 'sales' : 'purchase',
|
|
| 347 |
'dbh' => $dbh,); |
|
| 346 |
# Link this delivery order to the quotations it was created from. |
|
| 347 |
my @oe_ids = grep { $_ } map { $_ * 1 } split m/\s+/, $form->{oe_ids};
|
|
| 348 |
delete $form->{oe_ids};
|
|
| 349 |
if (scalar @oe_ids) {
|
|
| 350 |
my @links = map { { 'from_table' => 'oe', 'from_id' => $_, 'to_table' => 'delivery_orders', 'to_id' => $form->{id} } } @oe_ids;
|
|
| 351 |
RecordLinks->create_links('dbh' => $dbh, 'links' => \@links);
|
|
| 352 |
} |
|
| 353 |
|
|
| 354 |
$self->mark_orders_if_delivered('do_id' => $form->{id},
|
|
| 355 |
'type' => $form->{type} eq 'sales_delivery_order' ? 'sales' : 'purchase',
|
|
| 356 |
'dbh' => $dbh,); |
|
| 348 | 357 |
|
| 349 | 358 |
my $rc = $dbh->commit(); |
| 350 | 359 |
|
| ... | ... | |
| 357 | 366 |
return $rc; |
| 358 | 367 |
} |
| 359 | 368 |
|
| 360 |
sub mark_order_if_delivered {
|
|
| 369 |
sub mark_orders_if_delivered {
|
|
| 361 | 370 |
$main::lxdebug->enter_sub(); |
| 362 | 371 |
|
| 363 | 372 |
my $self = shift; |
| ... | ... | |
| 365 | 374 |
|
| 366 | 375 |
Common::check_params(\%params, qw(do_id type)); |
| 367 | 376 |
|
| 368 |
my $myconfig = \%main::myconfig; |
|
| 369 |
my $form = $main::form; |
|
| 370 |
|
|
| 371 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
| 377 |
my $myconfig = \%main::myconfig; |
|
| 378 |
my $form = $main::form; |
|
| 372 | 379 |
|
| 373 |
my $query = qq|SELECT ordnumber FROM delivery_orders WHERE id = ?|; |
|
| 374 |
my ($ordnumber) = selectfirst_array_query($form, $dbh, $query, conv_i($params{do_id}));
|
|
| 380 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
| 375 | 381 |
|
| 376 |
return $main::lxdebug->leave_sub() if (!$ordnumber); |
|
| 382 |
my @links = RecordLinks->get_links('dbh' => $dbh,
|
|
| 383 |
'from_table' => 'oe', |
|
| 384 |
'to_table' => 'delivery_orders', |
|
| 385 |
'to_id' => $params{do_id});
|
|
| 377 | 386 |
|
| 378 |
my $vc = $params{type} eq 'purchase' ? 'vendor' : 'customer';
|
|
| 379 |
$query = qq|SELECT id |
|
| 380 |
FROM oe |
|
| 381 |
WHERE NOT COALESCE(quotation, FALSE) |
|
| 382 |
AND (COALESCE(${vc}_id, 0) > 0)
|
|
| 383 |
AND (ordnumber = ?) |
|
| 384 |
ORDER BY id |
|
| 385 |
LIMIT 1|; |
|
| 387 |
my ($oe_id) = $links[0]->{from_id} if (scalar @links);
|
|
| 386 | 388 |
|
| 387 |
my ($oe_id) = selectfirst_array_query($form, $dbh, $query, $ordnumber);
|
|
| 389 |
$main::lxdebug->message(0, "oe_id $oe_id");
|
|
| 388 | 390 |
|
| 389 | 391 |
return $main::lxdebug->leave_sub() if (!$oe_id); |
| 390 | 392 |
|
| 391 | 393 |
my $all_units = AM->retrieve_all_units(); |
| 392 | 394 |
|
| 393 |
my %shipped = $self->get_shipped_qty('type' => $params{type},
|
|
| 394 |
'ordnumber' => $ordnumber,); |
|
| 395 |
my %ordered = (); |
|
| 396 |
|
|
| 397 | 395 |
$query = qq|SELECT oi.parts_id, oi.qty, oi.unit, p.unit AS partunit |
| 398 | 396 |
FROM orderitems oi |
| 399 | 397 |
LEFT JOIN parts p ON (oi.parts_id = p.id) |
| 400 | 398 |
WHERE (oi.trans_id = ?)|; |
| 401 | 399 |
my $sth = prepare_execute_query($form, $dbh, $query, $oe_id); |
| 402 | 400 |
|
| 401 |
my %shipped = $self->get_shipped_qty('type' => $params{type},
|
|
| 402 |
'oe_id' => $oe_id,); |
|
| 403 |
my %ordered = (); |
|
| 404 |
|
|
| 405 |
do_statement($form, $sth, $query, $oe_id); |
|
| 406 |
|
|
| 403 | 407 |
while (my $ref = $sth->fetchrow_hashref()) {
|
| 404 | 408 |
$ref->{baseqty} = $ref->{qty} * $all_units->{$ref->{unit}}->{factor} / $all_units->{$ref->{partunit}}->{factor};
|
| 405 | 409 |
|
| ... | ... | |
| 424 | 428 |
|
| 425 | 429 |
if ($delivered) {
|
| 426 | 430 |
$query = qq|UPDATE oe |
| 427 |
SET delivered = TRUE, closed = TRUE
|
|
| 431 |
SET delivered = TRUE |
|
| 428 | 432 |
WHERE id = ?|; |
| 429 | 433 |
do_query($form, $dbh, $query, $oe_id); |
| 430 |
|
|
| 431 | 434 |
$dbh->commit() if (!$params{dbh});
|
| 432 | 435 |
} |
| 433 | 436 |
|
| ... | ... | |
| 1001 | 1004 |
my $self = shift; |
| 1002 | 1005 |
my %params = @_; |
| 1003 | 1006 |
|
| 1004 |
Common::check_params(\%params, qw(type ordnumber));
|
|
| 1007 |
Common::check_params(\%params, qw(type oe_id));
|
|
| 1005 | 1008 |
|
| 1006 | 1009 |
my $myconfig = \%main::myconfig; |
| 1007 | 1010 |
my $form = $main::form; |
| 1008 | 1011 |
|
| 1009 | 1012 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
| 1010 | 1013 |
|
| 1011 |
my $notsales = $params{type} eq 'sales' ? '' : 'NOT';
|
|
| 1014 |
my @links = RecordLinks->get_links('dbh' => $dbh,
|
|
| 1015 |
'from_table' => 'oe', |
|
| 1016 |
'from_id' => $params{oe_id},
|
|
| 1017 |
'to_table' => 'delivery_orders'); |
|
| 1018 |
my @values = map { $_->{to_id} } @links;
|
|
| 1019 |
|
|
| 1020 |
if (!scalar @values) {
|
|
| 1021 |
$main::lxdebug->leave_sub(); |
|
| 1022 |
return (); |
|
| 1023 |
} |
|
| 1012 | 1024 |
|
| 1013 |
my $query =
|
|
| 1025 |
my $query = |
|
| 1014 | 1026 |
qq|SELECT doi.parts_id, doi.qty, doi.unit, p.unit AS partunit |
| 1015 | 1027 |
FROM delivery_order_items doi |
| 1016 | 1028 |
LEFT JOIN delivery_orders o ON (doi.delivery_order_id = o.id) |
| 1017 | 1029 |
LEFT JOIN parts p ON (doi.parts_id = p.id) |
| 1018 |
WHERE ($notsales o.is_sales) |
|
| 1019 |
AND (o.ordnumber = ?)|; |
|
| 1030 |
WHERE o.id IN (| . join(', ', ('?') x scalar @values) . qq|)|;
|
|
| 1020 | 1031 |
|
| 1021 | 1032 |
my %ship = (); |
| 1022 |
my $entries = selectall_hashref_query($form, $dbh, $query, $params{ordnumber});
|
|
| 1033 |
my $entries = selectall_hashref_query($form, $dbh, $query, @values);
|
|
| 1023 | 1034 |
my $all_units = AM->retrieve_all_units(); |
| 1024 | 1035 |
|
| 1025 | 1036 |
foreach my $entry (@{ $entries }) {
|
| SL/RecordLinks.pm | ||
|---|---|---|
| 1 |
package RecordLinks; |
|
| 2 |
|
|
| 3 |
use SL::Common; |
|
| 4 |
use SL::DBUtils; |
|
| 5 |
|
|
| 6 |
sub create_links {
|
|
| 7 |
$main::lxdebug->enter_sub(); |
|
| 8 |
|
|
| 9 |
my $self = shift; |
|
| 10 |
my %params = @_; |
|
| 11 |
|
|
| 12 |
Common::check_params(\%params, qw(links)); |
|
| 13 |
|
|
| 14 |
if (!scalar @{ $params{links} }) {
|
|
| 15 |
$main::lxdebug->leave_sub(); |
|
| 16 |
return; |
|
| 17 |
} |
|
| 18 |
|
|
| 19 |
my $myconfig = \%main::myconfig; |
|
| 20 |
my $form = $main::form; |
|
| 21 |
|
|
| 22 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
| 23 |
|
|
| 24 |
my $query = qq|INSERT INTO record_links (from_table, from_id, to_table, to_id) VALUES (?, ?, ?, ?)|; |
|
| 25 |
my $sth = prepare_query($form, $dbh, $query); |
|
| 26 |
|
|
| 27 |
foreach my $link (@{ $params{links} }) {
|
|
| 28 |
next if ('HASH' ne ref $link);
|
|
| 29 |
next if (!$link->{from_table} || !$link->{from_id} || !$link->{to_table} || !$link->{to_id});
|
|
| 30 |
|
|
| 31 |
do_statement($form, $sth, $query, $link->{from_table}, conv_i($link->{from_id}), $link->{to_table}, conv_i($link->{to_id}));
|
|
| 32 |
} |
|
| 33 |
|
|
| 34 |
$dbh->commit() unless ($params{dbh});
|
|
| 35 |
|
|
| 36 |
$main::lxdebug->leave_sub(); |
|
| 37 |
} |
|
| 38 |
|
|
| 39 |
sub get_links {
|
|
| 40 |
$main::lxdebug->enter_sub(); |
|
| 41 |
|
|
| 42 |
my $self = shift; |
|
| 43 |
my %params = @_; |
|
| 44 |
|
|
| 45 |
Common::check_params(\%params, [ qw(from_table from_id to_table to_id) ]); |
|
| 46 |
|
|
| 47 |
my $myconfig = \%main::myconfig; |
|
| 48 |
my $form = $main::form; |
|
| 49 |
|
|
| 50 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
| 51 |
|
|
| 52 |
my @conditions = (); |
|
| 53 |
my @values = (); |
|
| 54 |
|
|
| 55 |
foreach my $col (qw(from_table from_id to_table to_id)) {
|
|
| 56 |
next unless ($params{$col});
|
|
| 57 |
|
|
| 58 |
if ('ARRAY' eq ref $params{$col}) {
|
|
| 59 |
push @conditions, "$col IN (" . join(', ', ('?') x scalar(@{ $params{$col} })) . ")";
|
|
| 60 |
push @values, $col =~ m/table/ ? @{ $params{$col} } : map { conv_i($_) } @{ $params{$col} };
|
|
| 61 |
|
|
| 62 |
} else {
|
|
| 63 |
push @conditions, "$col = ?"; |
|
| 64 |
push @values, $col =~ m/table/ ? $params{$col} : conv_i($params{$col});
|
|
| 65 |
} |
|
| 66 |
} |
|
| 67 |
|
|
| 68 |
my $query = qq|SELECT from_table, from_id, to_table, to_id |
|
| 69 |
FROM record_links|; |
|
| 70 |
|
|
| 71 |
if (scalar @conditions) {
|
|
| 72 |
$query .= qq| WHERE | . join(' AND ', map { "($_)" } @conditions);
|
|
| 73 |
} |
|
| 74 |
|
|
| 75 |
my $links = selectall_hashref_query($form, $dbh, $query, @values); |
|
| 76 |
|
|
| 77 |
$main::lxdebug->leave_sub(); |
|
| 78 |
|
|
| 79 |
return wantarray ? @{ $links } : $links;
|
|
| 80 |
} |
|
| 81 |
|
|
| 82 |
1; |
|
| bin/mozilla/io.pl | ||
|---|---|---|
| 1883 | 1883 |
|
| 1884 | 1884 |
AM->retrieve_all_units(); |
| 1885 | 1885 |
|
| 1886 |
my %ship = DO->get_shipped_qty('type' => ($form->{type} eq 'purchase_order') ? 'purchase' : 'sales',
|
|
| 1887 |
'ordnumber' => $form->{ordnumber},);
|
|
| 1886 |
my %ship = DO->get_shipped_qty('type' => ($form->{type} eq 'purchase_order') ? 'purchase' : 'sales',
|
|
| 1887 |
'oe_id' => $form->{id},);
|
|
| 1888 | 1888 |
|
| 1889 | 1889 |
foreach my $i (1..$form->{rowcount}) {
|
| 1890 | 1890 |
next unless ($form->{"id_${i}"});
|
| bin/mozilla/oe.pl | ||
|---|---|---|
| 1814 | 1814 |
delete($form->{ordnumber});
|
| 1815 | 1815 |
} |
| 1816 | 1816 |
|
| 1817 |
$form->{cp_id} *= 1;
|
|
| 1817 |
$form->{cp_id} *= 1;
|
|
| 1818 |
$form->{oe_ids} = $form->{id};
|
|
| 1818 | 1819 |
|
| 1819 | 1820 |
$form->{title} = $locale->text('Add Sales Order');
|
| 1820 | 1821 |
$form->{vc} = "customer";
|
| ... | ... | |
| 1883 | 1884 |
require "bin/mozilla/do.pl"; |
| 1884 | 1885 |
|
| 1885 | 1886 |
$form->{cp_id} *= 1;
|
| 1887 |
$form->{oe_ids} = $form->{id};
|
|
| 1886 | 1888 |
$form->{transdate} = $form->current_date(\%myconfig);
|
| 1887 | 1889 |
delete $form->{duedate};
|
| 1888 | 1890 |
|
| sql/Pg-upgrade2/record_links.sql | ||
|---|---|---|
| 1 |
-- @tag: record_links |
|
| 2 |
-- @description: Verkn?pfungen zwischen Auftr?gen, Lieferscheinen und Rechnungen |
|
| 3 |
-- @depends: delivery_orders |
|
| 4 |
CREATE TABLE record_links ( |
|
| 5 |
from_table varchar(50) NOT NULL, |
|
| 6 |
from_id integer NOT NULL, |
|
| 7 |
to_table varchar(50) NOT NULL, |
|
| 8 |
to_id integer NOT NULL, |
|
| 9 |
|
|
| 10 |
itime timestamp DEFAULT now() |
|
| 11 |
); |
|
| 12 |
|
|
| 13 |
CREATE INDEX idx_record_links_from_table ON record_links (from_table); |
|
| 14 |
CREATE INDEX idx_record_links_from_id ON record_links (from_id); |
|
| 15 |
CREATE INDEX idx_record_links_to_table ON record_links (to_table); |
|
| 16 |
CREATE INDEX idx_record_links_to_id ON record_links (to_id); |
|
| templates/webpages/do/form_header_de.html | ||
|---|---|---|
| 71 | 71 |
<input type="hidden" name="max_dunning_level" value="[% HTML.escape(max_dunning_level) %]"> |
| 72 | 72 |
<input type="hidden" name="media" value="[% HTML.escape(media) %]"> |
| 73 | 73 |
<input type="hidden" name="message" value="[% HTML.escape(message) %]"> |
| 74 |
<input type="hidden" name="oe_ids" value="[% HTML.escape(oe_ids) %]"> |
|
| 74 | 75 |
<input type="hidden" name="printed" value="[% HTML.escape(printed) %]"> |
| 75 | 76 |
<input type="hidden" name="proforma" value="[% HTML.escape(proforma) %]"> |
| 76 | 77 |
<input type="hidden" name="queued" value="[% HTML.escape(queued) %]"> |
| templates/webpages/do/form_header_master.html | ||
|---|---|---|
| 71 | 71 |
<input type="hidden" name="max_dunning_level" value="[% HTML.escape(max_dunning_level) %]"> |
| 72 | 72 |
<input type="hidden" name="media" value="[% HTML.escape(media) %]"> |
| 73 | 73 |
<input type="hidden" name="message" value="[% HTML.escape(message) %]"> |
| 74 |
<input type="hidden" name="oe_ids" value="[% HTML.escape(oe_ids) %]"> |
|
| 74 | 75 |
<input type="hidden" name="printed" value="[% HTML.escape(printed) %]"> |
| 75 | 76 |
<input type="hidden" name="proforma" value="[% HTML.escape(proforma) %]"> |
| 76 | 77 |
<input type="hidden" name="queued" value="[% HTML.escape(queued) %]"> |
Auch abrufbar als: Unified diff
Verknüpfungen zwischen Angeboten, Aufträgen, Lieferscheinen, Rechnungen in einer eigenen Tabelle speichern.