use SL::DB::Tax;
use SL::DB::Draft;
use SL::DB::BankAccount;
+use SL::DBUtils qw(like);
use SL::Presenter;
use List::Util qw(max);
push @where, (transdate => { lt => $todate }) if ($todate);
my $bank_account = SL::DB::Manager::BankAccount->find_by( id => $::form->{filter}{bank_account} );
# bank_transactions no younger than starting date,
+ # including starting date (same search behaviour as fromdate)
# but OPEN invoices to be matched may be from before
if ( $bank_account->reconciliation_starting_date ) {
- push @where, (transdate => { gt => $bank_account->reconciliation_starting_date });
+ push @where, (transdate => { ge => $bank_account->reconciliation_starting_date });
};
my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => [ amount => {ne => \'invoice_amount'},
my $html;
$html .= SL::Presenter->input_tag('invoice_ids.' . $::form->{bt_id} . '[]', $::form->{prop_id} , type => 'hidden');
- $html .= SL::Presenter->escape( $invoice->invnumber );
+ # better in template code - but how to ajax this
+ $html .= SL::Presenter->escape(t8('Invno.') . ': ' . $invoice->invnumber . ' ');
+ $html .= SL::Presenter->escape(t8('Amount') . ': ' . $::form->format_amount(\%::myconfig, $invoice->open_amount, 2) . ' ');
$html .= SL::Presenter->select_tag('invoice_skontos.' . $::form->{bt_id} . '[]', \@select_options,
value_key => 'payment_type',
title_key => 'display' ) if @select_options;
foreach my $draft ( @{ $drafts } ) {
my $draft_as_object = YAML::Load($draft->form);
+ next unless $draft_as_object->{vendor_id}; # we cannot filter for vendor name, if this is a gl draft
my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
$draft->{vendor} = $vendor->name;
$draft->{vendor_id} = $vendor->id;
my @where_purchase = (amount => { ne => \'paid' });
if ($::form->{invnumber}) {
- push @where_sale, (invnumber => { ilike => '%' . $::form->{invnumber} . '%'});
- push @where_purchase, (invnumber => { ilike => '%' . $::form->{invnumber} . '%'});
+ push @where_sale, (invnumber => { ilike => like($::form->{invnumber})});
+ push @where_purchase, (invnumber => { ilike => like($::form->{invnumber})});
}
if ($::form->{amount}) {
}
if ($::form->{vcnumber}) {
- push @where_sale, ('customer.customernumber' => { ilike => '%' . $::form->{vcnumber} . '%'});
- push @where_purchase, ('vendor.vendornumber' => { ilike => '%' . $::form->{vcnumber} . '%'});
+ push @where_sale, ('customer.customernumber' => { ilike => like($::form->{vcnumber})});
+ push @where_purchase, ('vendor.vendornumber' => { ilike => like($::form->{vcnumber})});
}
if ($::form->{vcname}) {
- push @where_sale, ('customer.name' => { ilike => '%' . $::form->{vcname} . '%'});
- push @where_purchase, ('vendor.name' => { ilike => '%' . $::form->{vcname} . '%'});
+ push @where_sale, ('customer.name' => { ilike => like($::form->{vcname})});
+ push @where_purchase, ('vendor.name' => { ilike => like($::form->{vcname})});
}
if ($::form->{transdatefrom}) {
my $all_open_ar_invoices = SL::DB::Manager::Invoice->get_all(where => \@where_sale, with_objects => 'customer');
my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
- my @all_open_invoices;
- # filter out subcent differences from ap invoices
+ my @all_open_invoices = @{ $all_open_ar_invoices };
+ # add ap invoices, filtering out subcent open amounts
push @all_open_invoices, grep { abs($_->amount - $_->paid) >= 0.01 } @{ $all_open_ap_invoices };
@all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
my $invoice_hash = delete $::form->{invoice_ids}; # each key (the bt line with a bt_id) contains an array of invoice_ids
my $skonto_hash = delete $::form->{invoice_skontos} || {}; # array containing the payment type, could be empty
+ # a bank_transaction may be assigned to several invoices, i.e. a customer
+ # might pay several open invoices with one transaction
+
while ( my ($bt_id, $invoice_ids) = each(%$invoice_hash) ) {
my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
my $sign = $bank_transaction->amount < 0 ? -1 : 1;
return 1; } @invoices if $bank_transaction->amount < 0;
foreach my $invoice (@invoices) {
+
+ # Check if bank_transaction already has a link to the invoice, may only be linked once per invoice
+ # This might be caused by the user reloading a page and resending the form
+ die t8("Bank transaction with id #1 has already been linked to #2.", $bank_transaction->id, $invoice->displayable_name)
+ if _existing_record_link($bank_transaction, $invoice);
+
my $payment_type;
- if (@{ $skonto_hash->{"$bt_id"} }) {
+ if ( defined $skonto_hash->{"$bt_id"} ) {
$payment_type = shift(@{ $skonto_hash->{"$bt_id"} });
} else {
$payment_type = 'without_skonto';
$bank_transaction->remote_bank_code));
last;
}
- #pay invoice or go to the next bank transaction if the amount is not sufficiently high
- if ($invoice->amount <= $amount_of_transaction) {
+ # pay invoice or go to the next bank transaction if the amount is not sufficiently high
+ if ($invoice->open_amount <= $amount_of_transaction) {
+ # first calculate new bank transaction amount ...
+ if ($invoice->is_sales) {
+ $amount_of_transaction -= $sign * $invoice->open_amount;
+ $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $invoice->open_amount);
+ } else {
+ $amount_of_transaction += $sign * $invoice->open_amount;
+ $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $invoice->open_amount);
+ }
+ # ... and then pay the invoice
$invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
trans_id => $invoice->id,
- amount => $invoice->amount,
+ amount => $invoice->open_amount,
payment_type => $payment_type,
transdate => $bank_transaction->transdate->to_kivitendo);
- if ($invoice->is_sales) {
- $amount_of_transaction -= $sign * $invoice->amount;
- $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $invoice->amount);
- } else {
- $amount_of_transaction += $sign * $invoice->amount if (!$invoice->is_sales);
- $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $invoice->amount);
- }
} else {
$invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id,
trans_id => $invoice->id,
amount => $amount_of_transaction,
payment_type => $payment_type,
transdate => $bank_transaction->transdate->to_kivitendo);
- $bank_transaction->invoice_amount($bank_transaction->amount) if $invoice->is_sales;
- $bank_transaction->invoice_amount($bank_transaction->amount) if !$invoice->is_sales;
+ $bank_transaction->invoice_amount($bank_transaction->amount);
$amount_of_transaction = 0;
}
- #Record a link from the bank transaction to the invoice
+ # Record a record link from the bank transaction to the invoice
my @props = (
from_table => 'bank_transactions',
from_id => $bt_id,
to_id => $invoice->id,
);
- my $existing = SL::DB::Manager::RecordLink->get_all(where => \@props, limit => 1)->[0];
-
- SL::DB::RecordLink->new(@props)->save if !$existing;
+ SL::DB::RecordLink->new(@props)->save;
}
$bank_transaction->save;
}
my ($self) = @_;
foreach my $bt_id (@{ $::form->{proposal_ids} }) {
- #mark bt as booked
my $bt = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
+
+ my $arap = SL::DB::Manager::Invoice->find_by(id => $::form->{"proposed_invoice_$bt_id"});
+ $arap = SL::DB::Manager::PurchaseInvoice->find_by(id => $::form->{"proposed_invoice_$bt_id"}) if not defined $arap;
+
+ # check for existing record_link for that $bt and $arap
+ # do this before any changes to $bt are made
+ die t8("Bank transaction with id #1 has already been linked to #2.", $bt->id, $arap->displayable_name)
+ if _existing_record_link($bt, $arap);
+
+ #mark bt as booked
$bt->invoice_amount($bt->amount);
$bt->save;
#pay invoice
- my $arap = SL::DB::Manager::Invoice->find_by(id => $::form->{"proposed_invoice_$bt_id"});
- $arap = SL::DB::Manager::PurchaseInvoice->find_by(id => $::form->{"proposed_invoice_$bt_id"}) if not defined $arap;
$arap->pay_invoice(chart_id => $bt->local_bank_account->chart_id,
trans_id => $arap->id,
amount => $arap->amount,
to_id => $arap->id,
);
- my $existing = SL::DB::Manager::RecordLink->get_all(where => \@props, limit => 1)->[0];
-
- SL::DB::RecordLink->new(@props)->save if !$existing;
+ SL::DB::RecordLink->new(@props)->save;
}
flash('ok', t8('#1 proposal(s) saved.', scalar @{ $::form->{proposal_ids} }));
);
}
+sub _existing_record_link {
+ my ($bt, $invoice) = @_;
+
+ # check whether a record link from banktransaction $bt already exists to
+ # invoice $invoice, returns 1 if that is the case
+
+ die unless $bt->isa("SL::DB::BankTransaction") && ( $invoice->isa("SL::DB::Invoice") || $invoice->isa("SL::DB::PurchaseInvoice") );
+
+ my $linked_record_to_table = $invoice->is_sales ? 'Invoice' : 'PurchaseInvoice';
+ my $linked_records = $bt->linked_records( direction => 'to', to => $linked_record_to_table, query => [ id => $invoice->id ] );
+
+ return @$linked_records ? 1 : 0;
+};
+
+
sub init_models {
my ($self) = @_;