Revision 40c2c37c
Von Moritz Bunkus vor etwa 15 Jahren hinzugefügt
| SL/AP.pm | ||
|---|---|---|
| 35 | 35 |
package AP; |
| 36 | 36 |
|
| 37 | 37 |
use SL::DBUtils; |
| 38 |
use SL::IO; |
|
| 38 | 39 |
use SL::MoreCommon; |
| 39 | 40 |
|
| 40 | 41 |
use strict; |
| ... | ... | |
| 155 | 156 |
# amount for total AP |
| 156 | 157 |
$form->{payables} = $form->{invtotal};
|
| 157 | 158 |
|
| 158 |
$form->{datepaid} = $form->{transdate} unless ($form->{datepaid});
|
|
| 159 |
my $datepaid = ($form->{invpaid} != 0) ? $form->{datepaid} : undef;
|
|
| 160 |
|
|
| 161 | 159 |
# update exchangerate |
| 162 | 160 |
if (($form->{currency} ne $form->{defaultcurrency}) && !$exchangerate) {
|
| 163 | 161 |
$form->update_exchangerate($dbh, $form->{currency}, $form->{transdate}, 0,
|
| ... | ... | |
| 195 | 193 |
|
| 196 | 194 |
$query = qq|UPDATE ap SET |
| 197 | 195 |
invnumber = ?, transdate = ?, ordnumber = ?, vendor_id = ?, taxincluded = ?, |
| 198 |
amount = ?, duedate = ?, paid = ?, datepaid = ?, netamount = ?,
|
|
| 196 |
amount = ?, duedate = ?, paid = ?, netamount = ?, |
|
| 199 | 197 |
curr = ?, notes = ?, department_id = ?, storno = ?, storno_id = ? |
| 200 | 198 |
WHERE id = ?|; |
| 201 | 199 |
@values = ($form->{invnumber}, conv_date($form->{transdate}),
|
| 202 | 200 |
$form->{ordnumber}, conv_i($form->{vendor_id}),
|
| 203 | 201 |
$form->{taxincluded} ? 't' : 'f', $form->{invtotal},
|
| 204 | 202 |
conv_date($form->{duedate}), $form->{invpaid},
|
| 205 |
conv_date($datepaid), $form->{netamount},
|
|
| 203 |
$form->{netamount},
|
|
| 206 | 204 |
$form->{currency}, $form->{notes},
|
| 207 | 205 |
conv_i($form->{department_id}), $form->{storno},
|
| 208 | 206 |
$form->{storno_id}, $form->{id});
|
| ... | ... | |
| 354 | 352 |
do_query($form, $dbh, $query, $form->{invpaid}, $form->{invpaid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
|
| 355 | 353 |
} |
| 356 | 354 |
|
| 355 |
IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh);
|
|
| 356 |
|
|
| 357 | 357 |
my $rc = 1; |
| 358 | 358 |
if (!$provided_dbh) {
|
| 359 | 359 |
$dbh->commit(); |
| ... | ... | |
| 786 | 786 |
do_query($form, $dbh, $query, (values %$row)); |
| 787 | 787 |
} |
| 788 | 788 |
|
| 789 |
map { IO->set_datepaid(table => 'ap', id => $_, dbh => $dbh) } ($id, $new_id);
|
|
| 790 |
|
|
| 789 | 791 |
$dbh->commit; |
| 790 | 792 |
|
| 791 | 793 |
$main::lxdebug->leave_sub(); |
| SL/AR.pm | ||
|---|---|---|
| 36 | 36 |
|
| 37 | 37 |
use Data::Dumper; |
| 38 | 38 |
use SL::DBUtils; |
| 39 |
use SL::IO; |
|
| 39 | 40 |
use SL::MoreCommon; |
| 40 | 41 |
|
| 41 | 42 |
use strict; |
| ... | ... | |
| 141 | 142 |
($null, $form->{department_id}) = split(/--/, $form->{department});
|
| 142 | 143 |
$form->{department_id} *= 1;
|
| 143 | 144 |
|
| 144 |
# record last payment date in ar table |
|
| 145 |
$form->{datepaid} ||= $form->{transdate} ;
|
|
| 146 |
my $datepaid = ($form->{paid} != 0) ? $form->{datepaid} : undef;
|
|
| 147 |
|
|
| 148 | 145 |
# amount for AR account |
| 149 | 146 |
$form->{receivables} = $form->round_amount($form->{amount}, 2) * -1;
|
| 150 | 147 |
|
| ... | ... | |
| 156 | 153 |
$query = |
| 157 | 154 |
qq|UPDATE ar set |
| 158 | 155 |
invnumber = ?, ordnumber = ?, transdate = ?, customer_id = ?, |
| 159 |
taxincluded = ?, amount = ?, duedate = ?, paid = ?, datepaid = ?,
|
|
| 156 |
taxincluded = ?, amount = ?, duedate = ?, paid = ?, |
|
| 160 | 157 |
netamount = ?, curr = ?, notes = ?, department_id = ?, |
| 161 | 158 |
employee_id = ?, storno = ?, storno_id = ? |
| 162 | 159 |
WHERE id = ?|; |
| 163 | 160 |
my @values = ($form->{invnumber}, $form->{ordnumber}, conv_date($form->{transdate}), conv_i($form->{customer_id}), $form->{taxincluded} ? 't' : 'f', $form->{amount},
|
| 164 |
conv_date($form->{duedate}), $form->{paid}, conv_date($datepaid), $form->{netamount}, $form->{currency}, $form->{notes}, conv_i($form->{department_id}),
|
|
| 161 |
conv_date($form->{duedate}), $form->{paid}, $form->{netamount}, $form->{currency}, $form->{notes}, conv_i($form->{department_id}),
|
|
| 165 | 162 |
conv_i($form->{employee_id}), $form->{storno} ? 't' : 'f', $form->{storno_id}, conv_i($form->{id}));
|
| 166 | 163 |
do_query($form, $dbh, $query, @values); |
| 167 | 164 |
|
| ... | ... | |
| 266 | 263 |
} |
| 267 | 264 |
} |
| 268 | 265 |
|
| 266 |
IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
|
|
| 267 |
|
|
| 269 | 268 |
my $rc = 1; |
| 270 | 269 |
if (!$provided_dbh) {
|
| 271 | 270 |
$rc = $dbh->commit(); |
| ... | ... | |
| 673 | 672 |
do_query($form, $dbh, $query, (values %$row)); |
| 674 | 673 |
} |
| 675 | 674 |
|
| 675 |
map { IO->set_datepaid(table => 'ap', id => $_, dbh => $dbh) } ($id, $new_id);
|
|
| 676 |
|
|
| 676 | 677 |
$dbh->commit; |
| 677 | 678 |
|
| 678 | 679 |
$main::lxdebug->leave_sub(); |
| SL/IO.pm | ||
|---|---|---|
| 1 | 1 |
package IO; |
| 2 | 2 |
|
| 3 |
use List::Util qw(first); |
|
| 4 |
use List::MoreUtils qw(any); |
|
| 5 |
|
|
| 3 | 6 |
use SL::DBUtils; |
| 4 | 7 |
|
| 5 | 8 |
use strict; |
| ... | ... | |
| 26 | 29 |
} |
| 27 | 30 |
|
| 28 | 31 |
|
| 32 |
sub set_datepaid {
|
|
| 33 |
$main::lxdebug->enter_sub(); |
|
| 34 |
|
|
| 35 |
my $self = shift; |
|
| 36 |
my %params = @_; |
|
| 37 |
|
|
| 38 |
Common::check_params(\%params, qw(id table)); |
|
| 39 |
|
|
| 40 |
my $myconfig = \%main::myconfig; |
|
| 41 |
my $form = $main::form; |
|
| 42 |
|
|
| 43 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
|
| 44 |
my $id = conv_i($params{id});
|
|
| 45 |
my $table = (any { $_ eq $params{table} } qw(ar ap gl)) ? $params{table} : 'ar';
|
|
| 46 |
|
|
| 47 |
my ($curr_datepaid, $curr_paid) = selectfirst_array_query($form, $dbh, qq|SELECT datepaid, paid FROM $table WHERE id = ?|, $id); |
|
| 48 |
|
|
| 49 |
$query = <<SQL; |
|
| 50 |
SELECT MAX(at.transdate) |
|
| 51 |
FROM acc_trans at |
|
| 52 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
|
| 53 |
WHERE (at.trans_id = ?) |
|
| 54 |
AND (c.link LIKE '%paid%') |
|
| 55 |
SQL |
|
| 56 |
|
|
| 57 |
my ($max_acc_trans_date) = selectfirst_array_query($form, $dbh, $query, $id); |
|
| 58 |
|
|
| 59 |
if ($max_acc_trans_date && ($max_acc_trans_date ne $curr_datepaid)) {
|
|
| 60 |
# 1. Fall: Es gab mindestens eine Zahlung, und das Datum der Zahlung entspricht nicht |
|
| 61 |
# dem vermerkten Zahlungsdatum. |
|
| 62 |
do_query($form, $dbh, qq|UPDATE $table SET datepaid = ? WHERE id = ?|, $max_acc_trans_date, $id); |
|
| 63 |
|
|
| 64 |
} elsif (!$max_acc_trans_date && ($curr_paid * 1)) {
|
|
| 65 |
# 2. Fall: Es gab keine Zahlung, aber paid ist nicht 0. Das ist z.B. der Fall, wenn |
|
| 66 |
# die Funktion "als bezahlt buchen" verwendet oder wenn ein Beleg storniert wird. |
|
| 67 |
# In diesem Fall das letzte Modifikationsdatum als Bezahldatum nehmen, oder aber das |
|
| 68 |
# Erstelldatum, wenn keine Modifikation erfolgt ist (bei Stornos z.B.). |
|
| 69 |
do_query($form, $dbh, qq|UPDATE $table SET datepaid = COALESCE(mtime::date, itime::date) WHERE id = ?|, $id); |
|
| 70 |
} |
|
| 71 |
|
|
| 72 |
$dbh->commit() unless $params{dbh};
|
|
| 73 |
|
|
| 74 |
$main::lxdebug->leave_sub(); |
|
| 75 |
} |
|
| 76 |
|
|
| 77 |
|
|
| 29 | 78 |
1; |
| SL/IR.pm | ||
|---|---|---|
| 41 | 41 |
use SL::DBUtils; |
| 42 | 42 |
use SL::DO; |
| 43 | 43 |
use SL::GenericTranslations; |
| 44 |
use SL::IO; |
|
| 44 | 45 |
use SL::MoreCommon; |
| 45 | 46 |
use List::Util qw(min); |
| 46 | 47 |
|
| ... | ... | |
| 507 | 508 |
} |
| 508 | 509 |
} |
| 509 | 510 |
|
| 511 |
IO->set_datepaid(table => 'ap', id => $form->{id}, dbh => $dbh);
|
|
| 512 |
|
|
| 510 | 513 |
if ($payments_only) {
|
| 511 |
$query = qq|UPDATE ap SET paid = ?, datepaid = ? WHERE id = ?|;
|
|
| 512 |
do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
|
|
| 514 |
$query = qq|UPDATE ap SET paid = ? WHERE id = ?|; |
|
| 515 |
do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
|
|
| 513 | 516 |
|
| 514 | 517 |
if (!$provided_dbh) {
|
| 515 | 518 |
$dbh->commit(); |
| ... | ... | |
| 533 | 536 |
$query = qq|UPDATE ap SET |
| 534 | 537 |
invnumber = ?, ordnumber = ?, quonumber = ?, transdate = ?, |
| 535 | 538 |
orddate = ?, quodate = ?, vendor_id = ?, amount = ?, |
| 536 |
netamount = ?, paid = ?, duedate = ?, datepaid = ?,
|
|
| 539 |
netamount = ?, paid = ?, duedate = ?, |
|
| 537 | 540 |
invoice = ?, taxzone_id = ?, notes = ?, taxincluded = ?, |
| 538 | 541 |
intnotes = ?, curr = ?, storno_id = ?, storno = ?, |
| 539 | 542 |
cp_id = ?, employee_id = ?, department_id = ?, |
| ... | ... | |
| 542 | 545 |
@values = ( |
| 543 | 546 |
$form->{invnumber}, $form->{ordnumber}, $form->{quonumber}, conv_date($form->{invdate}),
|
| 544 | 547 |
conv_date($form->{orddate}), conv_date($form->{quodate}), conv_i($form->{vendor_id}), $amount,
|
| 545 |
$netamount, $form->{paid}, conv_date($form->{duedate}), $form->{paid} ? conv_date($form->{datepaid}) : undef,
|
|
| 548 |
$netamount, $form->{paid}, conv_date($form->{duedate}),
|
|
| 546 | 549 |
'1', $taxzone_id, $form->{notes}, $form->{taxincluded} ? 't' : 'f',
|
| 547 | 550 |
$form->{intnotes}, $form->{currency}, conv_i($form->{storno_id}), $form->{storno} ? 't' : 'f',
|
| 548 | 551 |
conv_i($form->{cp_id}), conv_i($form->{employee_id}), conv_i($form->{department_id}),
|
| SL/IS.pm | ||
|---|---|---|
| 45 | 45 |
use SL::GenericTranslations; |
| 46 | 46 |
use SL::MoreCommon; |
| 47 | 47 |
use SL::IC; |
| 48 |
use SL::IO; |
|
| 48 | 49 |
use Data::Dumper; |
| 49 | 50 |
|
| 50 | 51 |
use strict; |
| ... | ... | |
| 948 | 949 |
$form->{marge_total} *= -1;
|
| 949 | 950 |
} |
| 950 | 951 |
|
| 952 |
IO->set_datepaid(table => 'ar', id => $form->{id}, dbh => $dbh);
|
|
| 953 |
|
|
| 951 | 954 |
if ($payments_only) {
|
| 952 |
$query = qq|UPDATE ar SET paid = ?, datepaid = ? WHERE id = ?|;
|
|
| 953 |
do_query($form, $dbh, $query, $form->{paid}, $form->{paid} ? conv_date($form->{datepaid}) : undef, conv_i($form->{id}));
|
|
| 955 |
$query = qq|UPDATE ar SET paid = ? WHERE id = ?|; |
|
| 956 |
do_query($form, $dbh, $query, $form->{paid}, conv_i($form->{id}));
|
|
| 954 | 957 |
|
| 955 | 958 |
if (!$provided_dbh) {
|
| 956 | 959 |
$dbh->commit(); |
| ... | ... | |
| 988 | 991 |
$query = qq|UPDATE ar set |
| 989 | 992 |
invnumber = ?, ordnumber = ?, quonumber = ?, cusordnumber = ?, |
| 990 | 993 |
transdate = ?, orddate = ?, quodate = ?, customer_id = ?, |
| 991 |
amount = ?, netamount = ?, paid = ?, datepaid = ?,
|
|
| 994 |
amount = ?, netamount = ?, paid = ?, |
|
| 992 | 995 |
duedate = ?, deliverydate = ?, invoice = ?, shippingpoint = ?, |
| 993 | 996 |
shipvia = ?, terms = ?, notes = ?, intnotes = ?, |
| 994 | 997 |
curr = ?, department_id = ?, payment_id = ?, taxincluded = ?, |
| bin/mozilla/common.pl | ||
|---|---|---|
| 586 | 586 |
|
| 587 | 587 |
if($form->{mark_as_paid}) {
|
| 588 | 588 |
my $dbh ||= $form->get_standard_dbh($myconfig); |
| 589 |
my $query = qq|UPDATE $db_name SET paid = amount WHERE id = ?|; |
|
| 589 |
my $query = qq|UPDATE $db_name SET paid = amount, datepaid = current_date WHERE id = ?|;
|
|
| 590 | 590 |
do_query($form, $dbh, $query, $form->{id});
|
| 591 | 591 |
$dbh->commit(); |
| 592 | 592 |
$form->redirect($locale->text("Marked as paid"));
|
| sql/Pg-upgrade2/fix_datepaid.sql | ||
|---|---|---|
| 1 |
-- @tag: fix_datepaid |
|
| 2 |
-- @description: Felder datepaid in ar und ap richtig setzen |
|
| 3 |
-- @depends: release_2_6_0 |
|
| 4 |
|
|
| 5 |
UPDATE ap |
|
| 6 |
SET datepaid = COALESCE((SELECT MAX(at.transdate) |
|
| 7 |
FROM acc_trans at |
|
| 8 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
|
| 9 |
WHERE (at.trans_id = ap.id) |
|
| 10 |
AND (c.link LIKE '%paid%')), |
|
| 11 |
COALESCE(ap.mtime::date, ap.itime::date)) |
|
| 12 |
WHERE paid <> 0; |
|
| 13 |
|
|
| 14 |
UPDATE ar |
|
| 15 |
SET datepaid = COALESCE((SELECT MAX(at.transdate) |
|
| 16 |
FROM acc_trans at |
|
| 17 |
LEFT JOIN chart c ON (at.chart_id = c.id) |
|
| 18 |
WHERE (at.trans_id = ar.id) |
|
| 19 |
AND (c.link LIKE '%paid%')), |
|
| 20 |
COALESCE(ar.mtime::date, ar.itime::date)) |
|
| 21 |
WHERE paid <> 0; |
|
Auch abrufbar als: Unified diff
Beim Buchen von Rechnungen/Zahlungen das Feld "datepaid" richtig setzen.
Zusätzlich noch ein Datenbankupgradescript, das die Felder in
bestehenden Einträgen berichtigt.