From 548f4467739c9fea1addee0e0717a89e7be75429 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Mon, 4 Jun 2007 13:46:08 +0000 Subject: [PATCH] =?utf8?q?Merge=20der=20=C3=84nderungen=20zwischen=20https?= =?utf8?q?://ls-bs-si1.bs.linet-services.de/svn/prog/vendor/lxoffice-erp/2?= =?utf8?q?.4.2=20und=20https://ls-bs-si1.bs.linet-services.de/svn/prog/ven?= =?utf8?q?dor/lxoffice-erp/unstable-rev-2530?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- SL/Form.pm | 2 +- SL/IS.pm | 273 ++++++++++++++++++++++++++-------------------- bin/mozilla/is.pl | 7 +- 3 files changed, 159 insertions(+), 123 deletions(-) diff --git a/SL/Form.pm b/SL/Form.pm index 67f378244..852c3f953 100644 --- a/SL/Form.pm +++ b/SL/Form.pm @@ -2146,7 +2146,7 @@ sub create_links { # get amounts from individual entries $query = qq|SELECT - c.accno, c.description, + c.accno, c.description, c.link, a.source, a.amount, a.memo, a.transdate, a.cleared, a.project_id, a.taxkey, p.projectnumber, t.rate, t.id diff --git a/SL/IS.pm b/SL/IS.pm index 7b6752889..86f4d97fc 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -34,10 +34,10 @@ package IS; -use Data::Dumper; use SL::AM; use SL::Common; use SL::DBUtils; +use SL::MoreCommon; sub invoice_details { $main::lxdebug->enter_sub(); @@ -481,12 +481,12 @@ sub customer_details { sub post_invoice { $main::lxdebug->enter_sub(); - my ($self, $myconfig, $form) = @_; + my ($self, $myconfig, $form, $provided_dbh, $payments_only) = @_; # connect to database, turn off autocommit - my $dbh = $form->dbconnect_noauto($myconfig); + my $dbh = $provided_dbh ? $provided_dbh : $form->dbconnect_noauto($myconfig); - my ($query, $sth, $null, $project_id, $deliverydate, @values); + my ($query, $sth, $null, $project_id, @values); my $exchangerate = 0; if (!$form->{employee_id}) { @@ -497,28 +497,32 @@ sub post_invoice { my $all_units = AM->retrieve_units($myconfig, $form); - if ($form->{id}) { - - &reverse_invoice($dbh, $form); + if (!$payments_only) { + if ($form->{id}) { + &reverse_invoice($dbh, $form); - } else { - $query = qq|SELECT nextval('glid')|; - ($form->{"id"}) = selectrow_query($form, $dbh, $query); + } else { + $query = qq|SELECT nextval('glid')|; + ($form->{"id"}) = selectrow_query($form, $dbh, $query); - $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; - do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}); + $query = qq|INSERT INTO ar (id, invnumber) VALUES (?, ?)|; + do_query($form, $dbh, $query, $form->{"id"}, $form->{"id"}); - if (!$form->{invnumber}) { - $form->{invnumber} = - $form->update_defaults($myconfig, $form->{type} eq "credit_note" ? - "cnnumber" : "invnumber", $dbh); + if (!$form->{invnumber}) { + $form->{invnumber} = + $form->update_defaults($myconfig, $form->{type} eq "credit_note" ? + "cnnumber" : "invnumber", $dbh); + } } } my ($netamount, $invoicediff) = (0, 0); my ($amount, $linetotal, $lastincomeaccno); - if ($form->{currency} eq $form->{defaultcurrency}) { + my ($currencies) = selectfirst_array_query($form, $dbh, qq|SELECT curr FROM defaults|); + my $defaultcurrency = (split m/:/, $currencies)[0]; + + if ($form->{currency} eq $defaultcurrency) { $form->{exchangerate} = 1; } else { $exchangerate = @@ -638,6 +642,8 @@ sub post_invoice { $form->round_amount($form->{"sellprice_$i"} * $form->{exchangerate}, $decimalplaces); + next if $payments_only; + if ($form->{"inventory_accno_$i"} || $form->{"assembly_$i"}) { # adjust parts onhand quantity @@ -767,7 +773,7 @@ sub post_invoice { $form->{amount}{ $form->{id} }{ $form->{AR} } *= -1; # update exchangerate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { + if (($form->{currency} ne $defaultcurrency) && !$exchangerate) { $form->update_exchangerate($dbh, $form->{currency}, $form->{invdate}, $form->{exchangerate}, 0); } @@ -777,11 +783,10 @@ sub post_invoice { foreach my $trans_id (keys %{ $form->{amount} }) { foreach my $accno (keys %{ $form->{amount}{$trans_id} }) { next unless ($form->{expense_inventory} =~ /$accno/); - if ( - ($form->{amount}{$trans_id}{$accno} = - $form->round_amount($form->{amount}{$trans_id}{$accno}, 2) - ) != 0 - ) { + + $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2); + + if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, @@ -793,11 +798,9 @@ sub post_invoice { } foreach my $accno (keys %{ $form->{amount}{$trans_id} }) { - if ( - ($form->{amount}{$trans_id}{$accno} = - $form->round_amount($form->{amount}{$trans_id}{$accno}, 2) - ) != 0 - ) { + $form->{amount}{$trans_id}{$accno} = $form->round_amount($form->{amount}{$trans_id}{$accno}, 2); + + if (!$payments_only && ($form->{amount}{$trans_id}{$accno} != 0)) { $query = qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, taxkey, project_id) VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, @@ -830,7 +833,7 @@ sub post_invoice { $exchangerate = 0; - if ($form->{currency} eq $form->{defaultcurrency}) { + if ($form->{currency} eq $defaultcurrency) { $form->{"exchangerate_$i"} = 1; } else { $exchangerate = @@ -884,7 +887,7 @@ sub post_invoice { $diff = 0; # update exchange rate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { + if (($form->{currency} ne $defaultcurrency) && !$exchangerate) { $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0); @@ -892,6 +895,19 @@ sub post_invoice { } } + if ($payments_only) { + $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id})); + + if (!$provided_dbh) { + $dbh->commit(); + $dbh->disconnect(); + } + + $main::lxdebug->leave_sub(); + return; + } + # record exchange rate differences and gains/losses foreach my $accno (keys %{ $form->{fx} }) { foreach my $transdate (keys %{ $form->{fx}{$accno} }) { @@ -913,11 +929,6 @@ sub post_invoice { $amount = $netamount + $tax; - # set values which could be empty to 0 - my $datepaid = conv_date($form->{paid}); - my $duedate = conv_date($form->{duedate}); - $deliverydate = conv_date($form->{deliverydate}); - # fill in subject if there is none $form->{subject} = qq|$form->{label} $form->{invnumber}| unless $form->{subject}; @@ -1023,125 +1034,145 @@ Message: $form->{message}\r| if $form->{message}; Common::webdav_folder($form) if ($main::webdav); - my $rc = $dbh->commit; - $dbh->disconnect; + my $rc = 1; + if (!$provided_dbh) { + $dbh->commit(); + $dbh->disconnect(); + } $main::lxdebug->leave_sub(); return $rc; } +sub _delete_payments { + $main::lxdebug->enter_sub(); + + my ($self, $form, $dbh) = @_; + + my (@delete_oids, $delete_next, $sth, $ref); + + # Delete old payment entries from acc_trans. + my $query = + qq|SELECT at.oid, at.*, c.link + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (trans_id = ?) + ORDER BY at.oid|; + + $sth = prepare_execute_query($form, $dbh, $query, conv_i($form->{id})); + + while ($ref = $sth->fetchrow_hashref()) { + if ($delete_next) { + push @delete_oids, $ref->{oid}; + undef $delete_next; + next; + } + + $ref->{LINKS} = { map { $_, 1 } split m/:/, $ref->{link} }; + + if ($ref->{fx_transaction} || ($ref->{LINKS}->{AR} && ($ref->{amount} >= 0))) { + push @delete_oids, $ref->{oid}; + $delete_next = $ref->{LINKS}->{AR}; + } + } + + $sth->finish(); + + if (@delete_oids) { + $query = qq|DELETE FROM acc_trans WHERE oid IN (| . join(", ", @delete_oids) . qq|)|; + do_query($form, $dbh, $query); + } + + $main::lxdebug->leave_sub(); +} + sub post_payment { - $main::lxdebug->enter_sub() and my ($self, $myconfig, $form, $locale) = @_; + $main::lxdebug->enter_sub(); + + my ($self, $myconfig, $form, $locale) = @_; # connect to database, turn off autocommit my $dbh = $form->dbconnect_noauto($myconfig); - $form->{datepaid} = $form->{invdate}; + my (%payments, $old_form, $row, $item, $query, %keep_vars); - # total payments, don't move we need it here - for my $i ( 1 .. $form->{paidaccounts} ) { - $form->{"paid_$i"} = $form->parse_amount($myconfig, $form->{"paid_$i"}); - $form->{"paid_$i"} *= -1 if $form->{type} eq "credit_note"; - $form->{"paid"} += $form->{"paid_$i"}; - $form->{"datepaid"} = $form->{"datepaid_$i"} if $form->{"datepaid_$i"}; - } + my @prior; + push @prior, selectall_hashref_query($form, $dbh, qq|SELECT id, paid, datepaid FROM ar WHERE id = ?|, $form->{id}); + push @prior, selectall_hashref_query($form, $dbh, qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY oid|, $form->{id}); - $form->{exchangerate} = $form->get_exchangerate($dbh, $form->{currency}, $form->{invdate}, "buy"); + $old_form = save_form(); - # record payments and offsetting AR - for my $i (1 .. $form->{paidaccounts}) { - if ($form->{"paid_$i"}) { + # Delete all entries in acc_trans from prior payments. + $self->_delete_payments($form, $dbh); - my ($accno) = split /--/, $form->{"AR_paid_$i"}; - $form->{"datepaid_$i"} = $form->{invdate} unless ($form->{"datepaid_$i"}); - $form->{datepaid} = $form->{"datepaid_$i"}; + my @after_dp; + push @after_dp, selectall_hashref_query($form, $dbh, qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY oid|, $form->{id}); - $exchangerate = 0; - if (($form->{currency} eq $form->{defaultcurrency}) || ($form->{defaultcurrency} eq "")) { - $form->{"exchangerate_$i"} = 1; - } else { - $exchangerate = $form->check_exchangerate($myconfig, $form->{currency}, $form->{"datepaid_$i"}, 'buy'); - $form->{"exchangerate_$i"} = ($exchangerate) ? $exchangerate : $form->parse_amount($myconfig, $form->{"exchangerate_$i"}); - } + # Save the new payments the user made before cleaning up $form. + map { $payments{$_} = $form->{$_} } grep m/^datepaid_\d+$|^memo_\d+$|^source_\d+$|^exchangerate_\d+$|^paid_\d+$|^AR_paid_\d+$|^paidaccounts$/, keys %{ $form }; - # record AR - $amount = $form->round_amount($form->{"paid_$i"} * $form->{"exchangerate"}, 2); + # Clean up $form so that old content won't tamper the results. + %keep_vars = map { $_, 1 } qw(login password id); + map { delete $form->{$_} unless $keep_vars{$_} } keys %{ $form }; - $query = - qq|DELETE FROM acc_trans - WHERE (trans_id = ?) - AND (chart_id = (SELECT id FROM chart WHERE accno = ?)) - AND (amount = ?) AND (transdate = ?)|; - do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, conv_date($form->{"datepaid_$i"})); - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?))|; - do_query($form, $dbh, $query, $form->{id}, $form->{AR}, $amount, $form->{"datepaid_$i"}, conv_i($form->{"globalproject_id"}), $accno); + # Retrieve the invoice from the database. + $self->retrieve_invoice($myconfig, $form); - # record payment - $form->{"paid_$i"} *= -1; + # Set up the content of $form in the way that IR::post_invoice() expects. + $form->{exchangerate} = $form->format_amount($myconfig, $form->{exchangerate}); - $query = - qq|DELETE FROM acc_trans - WHERE (trans_id = ?) - AND (chart_id = (SELECT id FROM chart WHERE accno = ?)) - AND (amount = ?) AND (transdate = ?) AND (source = ?) AND (memo = ?)|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"}); + for $row (1 .. scalar @{ $form->{invoice_details} }) { + $item = $form->{invoice_details}->[$row - 1]; - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, source, memo, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?))|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{"paid_$i"}, $form->{"datepaid_$i"}, $form->{"source_$i"}, $form->{"memo_$i"}, - conv_i($form->{"globalproject_id"}), $accno); + map { $item->{$_} = $form->format_amount($myconfig, $item->{$_}) } qw(qty sellprice discount); - # gain/loss - $amount = $form->{"paid_$i"} * $form->{exchangerate} - $form->{"paid_$i"} * $form->{"exchangerate_$i"}; - $form->{fx}{ $form->{($amount > 0 ? 'fxgain_accno' : 'fxloss_accno')} }{ $form->{"datepaid_$i"} } += $amount; + map { $form->{"${_}_${row}"} = $item->{$_} } keys %{ $item }; + } - $diff = 0; + $form->{rowcount} = scalar @{ $form->{invoice_details} }; - # update exchange rate - if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { - $form->update_exchangerate($dbh, $form->{currency}, $form->{"datepaid_$i"}, $form->{"exchangerate_$i"}, 0); - } + delete @{$form}{qw(invoice_details paidaccounts storno paid)}; - } - } + # Restore the payment options from the user input. + map { $form->{$_} = $payments{$_} } keys %payments; - # record exchange rate differences and gains/losses - foreach my $accno (keys %{ $form->{fx} }) { - foreach my $transdate (keys %{ $form->{fx}{$accno} }) { + # Get the AP accno (which is normally done by Form::create_links()). + $query = + qq|SELECT c.accno + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (trans_id = ?) + AND ((c.link = 'AR') OR (c.link LIKE '%:AR') OR (c.link LIKE 'AR:%')) + ORDER BY at.oid + LIMIT 1|; - if ($form->{fx}{$accno}{$transdate} = $form->round_amount($form->{fx}{$accno}{$transdate}, 2)) { # '=' is no typo, it's an assignment - $query = - qq|DELETE FROM acc_trans - WHERE (trans_id = ?) - AND (chart_id = (SELECT c.id FROM chart c WHERE c.accno = ?)) - AND (amount = ?) AND (transdate = ?) AND (cleared = ?) AND (fx_transaction = ?)|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1); - $query = - qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, cleared, fx_transaction, project_id, taxkey) - VALUES (?, (SELECT id FROM chart WHERE accno = ?), ?, ?, ?, ?, ?, - (SELECT taxkey_id FROM chart WHERE accno = ?))|; - do_query($form, $dbh, $query, $form->{id}, $accno, $form->{fx}{$accno}{$transdate}, $transdate, 0, 1, conv_i($form->{"globalproject_id"}), $accno); - } + ($form->{AR}) = selectfirst_array_query($form, $dbh, $query, conv_i($form->{id})); - } + # Post the new payments. + $self->post_invoice($myconfig, $form, $dbh, 1); + + restore_form($old_form); + + my @after; + push @after, selectall_hashref_query($form, $dbh, qq|SELECT id, paid, datepaid FROM ar WHERE id = ?|, $form->{id}); + push @after, selectall_hashref_query($form, $dbh, qq|SELECT * FROM acc_trans WHERE trans_id = ? ORDER BY oid|, $form->{id}); + + foreach my $rows (@prior, @after_dp, @after) { + map { delete @{$_}{qw(itime mtime)} } @{ $rows }; } - # save AR record - delete $form->{datepaid} unless $form->{paid}; + map { $main::lxdebug->dump_sql_result(0, 'davor ', $_) } @prior; + map { $main::lxdebug->dump_sql_result(0, 'nachDP', $_) } @after_dp; + map { $main::lxdebug->dump_sql_result(0, 'danach', $_) } @after; - my $query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|; - do_query($form, $dbh, $query, $form->{"paid"}, conv_date($form->{"datepaid"}), conv_i($form->{"id"})); + my $rc = 1; +# my $rc = $dbh->commit(); + $dbh->disconnect(); - my $rc = $dbh->commit; - $dbh->disconnect; + $main::lxdebug->leave_sub(); - $main::lxdebug->leave_sub() and return $rc; + return $rc; } sub process_assembly { diff --git a/bin/mozilla/is.pl b/bin/mozilla/is.pl index b8eb07479..1a61ab2d4 100644 --- a/bin/mozilla/is.pl +++ b/bin/mozilla/is.pl @@ -79,7 +79,7 @@ sub add { sub edit { $lxdebug->enter_sub(); - + $form->{"Watchdog::paidaccounts"} = 1; # show history button $form->{javascript} = qq||; #/show hhistory button @@ -199,6 +199,11 @@ sub invoice_links { if ($key eq "AR_paid") { for $i (1 .. scalar @{ $form->{acc_trans}{$key} }) { + $lxdebug->message(0, "link " . $form->{acc_trans}->{$key}->[$i - 1]->{link}); + $lxdebug->dump(0, "...", $form->{acc_trans}->{$key}->[$i - 1]); + my $previous_links = { map { $_, 1 } split m/:/, $form->{acc_trans}->{$key}->[$i - 1]->{link} }; + next if (!$previous_links->{AR}); + $form->{"AR_paid_$i"} = "$form->{acc_trans}{$key}->[$i-1]->{accno}--$form->{acc_trans}{$key}->[$i-1]->{description}"; -- 2.20.1