From 40c2c37c2db3224e2fa477fd77c34b044a1825d3 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Thu, 24 Sep 2009 16:12:14 +0200 Subject: [PATCH] Beim Buchen von Rechnungen/Zahlungen das Feld "datepaid" richtig setzen. MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Zusätzlich noch ein Datenbankupgradescript, das die Felder in bestehenden Einträgen berichtigt. --- SL/AP.pm | 12 ++++---- SL/AR.pm | 13 +++++---- SL/IO.pm | 49 ++++++++++++++++++++++++++++++++ SL/IR.pm | 11 ++++--- SL/IS.pm | 9 ++++-- bin/mozilla/common.pl | 2 +- sql/Pg-upgrade2/fix_datepaid.sql | 21 ++++++++++++++ 7 files changed, 98 insertions(+), 19 deletions(-) create mode 100644 sql/Pg-upgrade2/fix_datepaid.sql diff --git a/SL/AP.pm b/SL/AP.pm index 7b5a46150..ad8749f56 100644 --- a/SL/AP.pm +++ b/SL/AP.pm @@ -35,6 +35,7 @@ package AP; use SL::DBUtils; +use SL::IO; use SL::MoreCommon; use strict; @@ -155,9 +156,6 @@ sub post_transaction { # amount for total AP $form->{payables} = $form->{invtotal}; - $form->{datepaid} = $form->{transdate} unless ($form->{datepaid}); - my $datepaid = ($form->{invpaid} != 0) ? $form->{datepaid} : undef; - # update exchangerate if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) { $form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0, @@ -195,14 +193,14 @@ sub post_transaction { $query = qq|UPDATE ap SET invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, - amount = ?, duedate = ?, paid = ?, datepaid = ?, netamount = ?, + amount = ?, duedate = ?, paid = ?, netamount = ?, curr = ?, notes = ?, department_id = ?, storno = ?, storno_id = ? WHERE id = ?|; @values = ($form->{invnumber}, conv_date($form->{transdate}), $form->{ordnumber}, conv_i($form->{vendor_id}), $form->{taxincluded} ? 't' : 'f', $form->{invtotal}, conv_date($form->{duedate}), $form->{invpaid}, - conv_date($datepaid), $form->{netamount}, + $form->{netamount}, $form->{currency}, $form->{notes}, conv_i($form->{department_id}), $form->{storno}, $form->{storno_id}, $form->{id}); @@ -354,6 +352,8 @@ sub post_transaction { do_query($form, $dbh, $query, $form->{invpaid}, $form->{invpaid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id})); } + IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh); + my $rc = 1; if (!$provided_dbh) { $dbh->commit(); @@ -786,6 +786,8 @@ sub storno { do_query($form, $dbh, $query, (values %$row)); } + map { IO->set_datepaid(table => 'ap', id => $_, dbh => $dbh) } ($id, $new_id); + $dbh->commit; $main::lxdebug->leave_sub(); diff --git a/SL/AR.pm b/SL/AR.pm index b48741d34..7d037838c 100644 --- a/SL/AR.pm +++ b/SL/AR.pm @@ -36,6 +36,7 @@ package AR; use Data::Dumper; use SL::DBUtils; +use SL::IO; use SL::MoreCommon; use strict; @@ -141,10 +142,6 @@ sub post_transaction { ($null, $form->{department_id}) = split(/--/, $form->{department}); $form->{department_id} *= 1; - # record last payment date in ar table - $form->{datepaid} ||= $form->{transdate} ; - my $datepaid = ($form->{paid} != 0) ? $form->{datepaid} : undef; - # amount for AR account $form->{receivables} = $form->round_amount($form->{amount}, 2) * -1; @@ -156,12 +153,12 @@ sub post_transaction { $query = qq|UPDATE ar set invnumber = ?, ordnumber = ?, transdate = ?, customer_id = ?, - taxincluded = ?, amount = ?, duedate = ?, paid = ?, datepaid = ?, + taxincluded = ?, amount = ?, duedate = ?, paid = ?, netamount = ?, curr = ?, notes = ?, department_id = ?, employee_id = ?, storno = ?, storno_id = ? WHERE id = ?|; my @values = ($form->{invnumber}, $form->{ordnumber}, conv_date($form->{transdate}), conv_i($form->{customer_id}), $form->{taxincluded} ? 't' : 'f', $form->{amount}, - conv_date($form->{duedate}), $form->{paid}, conv_date($datepaid), $form->{netamount}, $form->{currency}, $form->{notes}, conv_i($form->{department_id}), + conv_date($form->{duedate}), $form->{paid}, $form->{netamount}, $form->{currency}, $form->{notes}, conv_i($form->{department_id}), conv_i($form->{employee_id}), $form->{storno} ? 't' : 'f', $form->{storno_id}, conv_i($form->{id})); do_query($form, $dbh, $query, @values); @@ -266,6 +263,8 @@ sub post_transaction { } } + IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh); + my $rc = 1; if (!$provided_dbh) { $rc = $dbh->commit(); @@ -673,6 +672,8 @@ sub storno { do_query($form, $dbh, $query, (values %$row)); } + map { IO->set_datepaid(table => 'ap', id => $_, dbh => $dbh) } ($id, $new_id); + $dbh->commit; $main::lxdebug->leave_sub(); diff --git a/SL/IO.pm b/SL/IO.pm index 814a452dc..83af0d483 100644 --- a/SL/IO.pm +++ b/SL/IO.pm @@ -1,5 +1,8 @@ package IO; +use List::Util qw(first); +use List::MoreUtils qw(any); + use SL::DBUtils; use strict; @@ -26,4 +29,50 @@ sub retrieve_partunits { } +sub set_datepaid { + $main::lxdebug->enter_sub(); + + my $self = shift; + my %params = @_; + + Common::check_params(\%params, qw(id table)); + + my $myconfig = \%main::myconfig; + my $form = $main::form; + + my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig); + my $id = conv_i($params{id}); + my $table = (any { $_ eq $params{table} } qw(ar ap gl)) ? $params{table} : 'ar'; + + my ($curr_datepaid, $curr_paid) = selectfirst_array_query($form, $dbh, qq|SELECT datepaid, paid FROM $table WHERE id = ?|, $id); + + $query = <commit() unless $params{dbh}; + + $main::lxdebug->leave_sub(); +} + + 1; diff --git a/SL/IR.pm b/SL/IR.pm index 454e74737..7e24d4e01 100644 --- a/SL/IR.pm +++ b/SL/IR.pm @@ -41,6 +41,7 @@ use SL::CVar; use SL::DBUtils; use SL::DO; use SL::GenericTranslations; +use SL::IO; use SL::MoreCommon; use List::Util qw(min); @@ -507,9 +508,11 @@ sub post_invoice { } } + IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh); + if ($payments_only) { - $query = qq|UPDATE ap SET paid = ?, datepaid = ? WHERE id = ?|; - do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id})); + $query = qq|UPDATE ap SET paid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id})); if (!$provided_dbh) { $dbh->commit(); @@ -533,7 +536,7 @@ sub post_invoice { $query = qq|UPDATE ap SET invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?, orddate = ?, quodate = ?, vendor_id = ?, amount = ?, - netamount = ?, paid = ?, duedate = ?, datepaid = ?, + netamount = ?, paid = ?, duedate = ?, invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?, intnotes = ?, curr = ?, storno_id = ?, storno = ?, cp_id = ?, employee_id = ?, department_id = ?, @@ -542,7 +545,7 @@ sub post_invoice { @values = ( $form->{invnumber}, $form->{ordnumber}, $form->{quonumber}, conv_date($form->{invdate}), conv_date($form->{orddate}), conv_date($form->{quodate}), conv_i($form->{vendor_id}), $amount, - $netamount, $form->{paid}, conv_date($form->{duedate}), $form->{paid} ? conv_date($form->{datepaid}) : undef, + $netamount, $form->{paid}, conv_date($form->{duedate}), '1', $taxzone_id, $form->{notes}, $form->{taxincluded} ? 't' : 'f', $form->{intnotes}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f', conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}), diff --git a/SL/IS.pm b/SL/IS.pm index e17efc6e6..483bac745 100644 --- a/SL/IS.pm +++ b/SL/IS.pm @@ -45,6 +45,7 @@ use SL::DO; use SL::GenericTranslations; use SL::MoreCommon; use SL::IC; +use SL::IO; use Data::Dumper; use strict; @@ -948,9 +949,11 @@ sub post_invoice { $form->{marge_total} *= -1; } + IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh); + 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})); + $query = qq|UPDATE ar SET paid = ? WHERE id = ?|; + do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id})); if (!$provided_dbh) { $dbh->commit(); @@ -988,7 +991,7 @@ sub post_invoice { $query = qq|UPDATE ar set invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?, transdate = ?, orddate = ?, quodate = ?, customer_id = ?, - amount = ?, netamount = ?, paid = ?, datepaid = ?, + amount = ?, netamount = ?, paid = ?, duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, shipvia = ?, terms = ?, notes = ?, intnotes = ?, curr = ?, department_id = ?, payment_id = ?, taxincluded = ?, diff --git a/bin/mozilla/common.pl b/bin/mozilla/common.pl index 3bc458a8d..2b2016f76 100644 --- a/bin/mozilla/common.pl +++ b/bin/mozilla/common.pl @@ -586,7 +586,7 @@ sub mark_as_paid_common { if($form->{mark_as_paid}) { my $dbh ||= $form->get_standard_dbh($myconfig); - my $query = qq|UPDATE $db_name SET paid = amount WHERE id = ?|; + my $query = qq|UPDATE $db_name SET paid = amount, datepaid = current_date WHERE id = ?|; do_query($form, $dbh, $query, $form->{id}); $dbh->commit(); $form->redirect($locale->text("Marked as paid")); diff --git a/sql/Pg-upgrade2/fix_datepaid.sql b/sql/Pg-upgrade2/fix_datepaid.sql new file mode 100644 index 000000000..d4cc74bb4 --- /dev/null +++ b/sql/Pg-upgrade2/fix_datepaid.sql @@ -0,0 +1,21 @@ +-- @tag: fix_datepaid +-- @description: Felder datepaid in ar und ap richtig setzen +-- @depends: release_2_6_0 + +UPDATE ap + SET datepaid = COALESCE((SELECT MAX(at.transdate) + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (at.trans_id = ap.id) + AND (c.link LIKE '%paid%')), + COALESCE(ap.mtime::date, ap.itime::date)) + WHERE paid <> 0; + +UPDATE ar + SET datepaid = COALESCE((SELECT MAX(at.transdate) + FROM acc_trans at + LEFT JOIN chart c ON (at.chart_id = c.id) + WHERE (at.trans_id = ar.id) + AND (c.link LIKE '%paid%')), + COALESCE(ar.mtime::date, ar.itime::date)) + WHERE paid <> 0; -- 2.20.1