1 package SL::Controller::BankTransaction;
3 # idee- möglichkeit bankdaten zu übernehmen in stammdaten
4 # erst Kontenabgleich, um alle gl-Einträge wegzuhaben
7 use parent qw(SL::Controller::Base);
9 use SL::Controller::Helper::GetModels;
10 use SL::Controller::Helper::ReportGenerator;
11 use SL::ReportGenerator;
13 use SL::DB::BankTransaction;
14 use SL::Helper::Flash;
15 use SL::Locale::String;
18 use SL::DB::PurchaseInvoice;
19 use SL::DB::RecordLink;
22 use SL::DB::AccTransaction;
25 use SL::DB::BankAccount;
27 use Rose::Object::MakeMethods::Generic
29 'scalar --get_set_init' => [ qw(models) ],
32 __PACKAGE__->run_before('check_auth');
42 my $bank_accounts = SL::DB::Manager::BankAccount->get_all();
44 $self->render('bank_transactions/search',
45 label_sub => sub { t8('#1 - Account number #2, bank code #3, #4', $_[0]->name, $_[0]->account_number, $_[0]->bank_code, $_[0]->bank, )},
46 BANK_ACCOUNTS => $bank_accounts);
52 my $transactions = $self->models->get;
54 $self->make_filter_summary;
55 $self->prepare_report;
57 $self->report_generator_list_objects(report => $self->{report}, objects => $transactions);
63 if (!$::form->{filter}{bank_account}) {
64 flash('error', t8('No bank account chosen!'));
69 my $sort_by = $::form->{sort_by} || 'transdate';
70 $sort_by = 'transdate' if $sort_by eq 'proposal';
71 $sort_by .= $::form->{sort_dir} ? ' DESC' : ' ASC';
73 my $fromdate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{fromdate});
74 my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{filter}->{todate});
75 $todate->add( days => 1 ) if $todate;
78 push @where, (transdate => { ge => $fromdate }) if ($fromdate);
79 push @where, (transdate => { lt => $todate }) if ($todate);
81 my $bank_transactions = SL::DB::Manager::BankTransaction->get_all(where => [ amount => {ne => \'invoice_amount'},
82 local_bank_account_id => $::form->{filter}{bank_account},
84 with_objects => [ 'local_bank_account', 'currency' ],
85 sort_by => $sort_by, limit => 10000);
87 my $all_open_ar_invoices = SL::DB::Manager::Invoice->get_all(where => [amount => { gt => \'paid' }], with_objects => 'customer');
88 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => [amount => { gt => \'paid' }], with_objects => 'vendor');
90 my @all_open_invoices;
91 push @all_open_invoices, @{ $all_open_ar_invoices };
92 push @all_open_invoices, @{ $all_open_ap_invoices };
94 foreach my $bt (@{ $bank_transactions }) {
95 next unless $bt->{remote_name}; # bank has no name, usually fees, use create invoice to assign
96 foreach my $open_invoice (@all_open_invoices){
97 $open_invoice->{agreement} = 0;
99 #compare banking arrangements
100 my ($bank_code, $account_number);
101 $bank_code = $open_invoice->customer->bank_code if $open_invoice->is_sales;
102 $account_number = $open_invoice->customer->account_number if $open_invoice->is_sales;
103 $bank_code = $open_invoice->vendor->bank_code if ! $open_invoice->is_sales;
104 $account_number = $open_invoice->vendor->account_number if ! $open_invoice->is_sales;
105 ($bank_code eq $bt->remote_bank_code
106 && $account_number eq $bt->remote_account_number) ? ($open_invoice->{agreement} += 2) : ();
108 my $datediff = $bt->transdate->{utc_rd_days} - $open_invoice->transdate->{utc_rd_days};
109 $open_invoice->{datediff} = $datediff;
112 # (abs($open_invoice->amount) == abs($bt->amount)) ? ($open_invoice->{agreement} += 2) : ();
113 # do we need double abs here?
114 (abs(abs($open_invoice->amount) - abs($bt->amount)) < 0.01) ? ($open_invoice->{agreement} += 4) : ();
116 #search invoice number in purpose
117 my $invnumber = $open_invoice->invnumber;
118 # possible improvement: match has to have more than 1 character?
119 $bt->purpose =~ /\b$invnumber\b/i ? ($open_invoice->{agreement} += 2) : ();
122 if ( $open_invoice->is_sales && $bt->amount < 0 ) {
123 $open_invoice->{agreement} -= 1;
125 if ( ! $open_invoice->is_sales && $bt->amount > 0 ) {
126 $open_invoice->{agreement} -= 1;
129 #search customer/vendor number in purpose
131 $cvnumber = $open_invoice->customer->customernumber if $open_invoice->is_sales;
132 $cvnumber = $open_invoice->vendor->vendornumber if ! $open_invoice->is_sales;
133 $bt->purpose =~ /\b$cvnumber\b/i ? ($open_invoice->{agreement}++) : ();
135 #compare customer/vendor name and account holder
137 $cvname = $open_invoice->customer->name if $open_invoice->is_sales;
138 $cvname = $open_invoice->vendor->name if ! $open_invoice->is_sales;
139 $bt->remote_name =~ /\b$cvname\b/i ? ($open_invoice->{agreement}++) : ();
141 #Compare transdate of bank_transaction with transdate of invoice
142 #Check if words in remote_name appear in cvname
143 $open_invoice->{agreement} += &check_string($bt->remote_name,$cvname);
145 $open_invoice->{agreement} -= 1 if $datediff < -5; # dies hebelt eventuell Vorkasse aus
146 $open_invoice->{agreement} += 1 if $datediff < 30; # dies hebelt eventuell Vorkasse aus
148 # only if we already have a good agreement, let date further change value of agreement.
149 # this is so that if there are several open invoices which are all equal (rent jan, rent feb...) the one with the best date match is chose over the others
150 # another way around this is to just pre-filter by periods instead of matching everything
151 if ( $open_invoice->{agreement} > 5 ) {
152 if ( $datediff == 0 ) {
153 $open_invoice->{agreement} += 3;
154 } elsif ( $datediff > 0 and $datediff <= 14 ) {
155 $open_invoice->{agreement} += 2;
156 } elsif ( $datediff >14 and $datediff < 35) {
157 $open_invoice->{agreement} += 1;
158 } elsif ( $datediff >34 and $datediff < 120) {
159 $open_invoice->{agreement} += 1;
160 } elsif ( $datediff < 0 ) {
161 $open_invoice->{agreement} -= 1;
163 # e.g. datediff > 120
167 #if ($open_invoice->transdate->{utc_rd_days} == $bt->transdate->{utc_rd_days}) {
168 #$open_invoice->{agreement} += 4;
169 #print FH "found matching date for invoice " . $open_invoice->invnumber . " ( " . $bt->transdate->{utc_rd_days} . " . \n";
170 #} elsif (($open_invoice->transdate->{utc_rd_days} + 30) < $bt->transdate->{utc_rd_days}) {
171 #$open_invoice->{agreement} -= 1;
173 #$open_invoice->{agreement} -= 2;
174 #print FH "found nomatch date -2 for invoice " . $open_invoice->invnumber . " ( " . $bt->transdate->{utc_rd_days} . " . \n";
176 #print FH "agreement after date_agreement: " . $open_invoice->{agreement} . "\n";
181 # finished going through all open_invoices
184 # for each open_invoice try to match it to each open_invoice and store agreement in $open_invoice->{agreement} (which gets overwritten each time for each bt)
188 $bt->{proposals} = [];
190 # wird nie ausgeführt, bzw. nur ganz am Ende
191 # oder einmal am Anfang?
192 # es werden maximal 7 vorschläge gemacht?
193 # 7 mal wird geprüft, ob etwas passt
194 while (scalar @{ $bt->{proposals} } < 1 && $agreement-- > 0) {
195 $bt->{proposals} = [ grep { $_->{agreement} > $agreement } @all_open_invoices ];
196 #Kann wahrscheinlich weg:
197 # map { $_->{style} = "green" } @{ $bt->{proposals} } if $agreement >= 5;
198 # map { $_->{style} = "orange" } @{ $bt->{proposals} } if $agreement < 5 and $agreement >= 3;
199 # map { $_->{style} = "red" } @{ $bt->{proposals} } if $agreement < 3;
200 $bt->{agreement} = $agreement; # agreement value at cutoff, will correspond to several results if threshold is 7 and several are already above 7
205 # separate filter for proposals (second tab, agreement >= 5 and exactly one match)
206 # to qualify as a proposal there has to be
208 # * there must be only one exact match
209 # * depending on whether sales or purchase the amount has to have the correct sign (so Gutschriften don't work?)
211 my @proposals = grep { $_->{agreement} >= 5
212 and 1 == scalar @{ $_->{proposals} }
213 and (@{ $_->{proposals} }[0]->is_sales ? abs(@{ $_->{proposals} }[0]->amount - $_->amount) < 0.01 : abs(@{ $_->{proposals} }[0]->amount + $_->amount) < 0.01) } @{ $bank_transactions };
215 #Sort bank transactions by quality of proposal
216 $bank_transactions = [ sort { $a->{agreement} <=> $b->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 1;
217 $bank_transactions = [ sort { $b->{agreement} <=> $a->{agreement} } @{ $bank_transactions } ] if $::form->{sort_by} eq 'proposal' and $::form->{sort_dir} == 0;
220 $self->render('bank_transactions/list',
221 title => t8('List of bank transactions'),
222 BANK_TRANSACTIONS => $bank_transactions,
223 PROPOSALS => \@proposals,
224 bank_account => SL::DB::Manager::BankAccount->find_by(id => $::form->{filter}{bank_account}) );
228 my $bankstring = shift;
229 my $namestring = shift;
230 return 0 unless $bankstring and $namestring;
232 my @bankwords = grep(/^\w+$/, split(/\b/,$bankstring));
235 foreach my $bankword ( @bankwords ) {
236 # only try to match strings with more than 2 characters
237 next unless length($bankword)>2;
238 if ( $namestring =~ /\b$bankword\b/i ) {
245 sub action_assign_invoice {
248 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
250 $self->render('bank_transactions/assign_invoice', { layout => 0 },
251 title => t8('Assign invoice'),);
254 sub action_create_invoice {
256 my %myconfig = %main::myconfig;
258 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
259 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
261 my $drafts = SL::DB::Manager::Draft->get_all(where => [ module => 'ap'] , with_objects => 'employee');
265 foreach my $draft ( @{ $drafts } ) {
266 my $draft_as_object = YAML::Load($draft->form);
267 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
268 $draft->{vendor} = $vendor->name;
269 $draft->{vendor_id} = $vendor->id;
270 push @filtered_drafts, $draft;
274 @filtered_drafts = grep { $_->{vendor_id} == $vendor_of_transaction->id } @filtered_drafts if $vendor_of_transaction;
276 my $all_vendors = SL::DB::Manager::Vendor->get_all();
278 $self->render('bank_transactions/create_invoice', { layout => 0 },
279 title => t8('Create invoice'),
280 DRAFTS => \@filtered_drafts,
281 vendor_id => $vendor_of_transaction ? $vendor_of_transaction->id : undef,
282 vendor_name => $vendor_of_transaction ? $vendor_of_transaction->name : undef,
283 ALL_VENDORS => $all_vendors,
284 limit => $myconfig{vclimit},
285 callback => $self->url_for(action => 'list',
286 'filter.bank_account' => $::form->{filter}->{bank_account},
287 'filter.todate' => $::form->{filter}->{todate},
288 'filter.fromdate' => $::form->{filter}->{fromdate}),
292 sub action_filter_drafts {
295 $self->{transaction} = SL::DB::Manager::BankTransaction->find_by(id => $::form->{bt_id});
296 my $vendor_of_transaction = SL::DB::Manager::Vendor->find_by(account_number => $self->{transaction}->{remote_account_number});
298 my $drafts = SL::DB::Manager::Draft->get_all(with_objects => 'employee');
302 foreach my $draft ( @{ $drafts } ) {
303 my $draft_as_object = YAML::Load($draft->form);
304 my $vendor = SL::DB::Manager::Vendor->find_by(id => $draft_as_object->{vendor_id});
305 $draft->{vendor} = $vendor->name;
306 $draft->{vendor_id} = $vendor->id;
307 push @filtered_drafts, $draft;
310 my $vendor_name = $::form->{vendor};
311 my $vendor_id = $::form->{vendor_id};
314 @filtered_drafts = grep { $_->{vendor_id} == $vendor_id } @filtered_drafts if $vendor_id;
315 @filtered_drafts = grep { $_->{vendor} =~ /$vendor_name/i } @filtered_drafts if $vendor_name;
317 my $output = $self->render(
318 'bank_transactions/filter_drafts',
320 DRAFTS => \@filtered_drafts,
323 my %result = ( count => 0, html => $output );
325 $self->render(\to_json(\%result), { type => 'json', process => 0 });
328 sub action_ajax_add_list {
331 my @where_sale = (amount => { ne => \'paid' });
332 my @where_purchase = (amount => { ne => \'paid' });
334 if ($::form->{invnumber}) {
335 push @where_sale, (invnumber => { ilike => '%' . $::form->{invnumber} . '%'});
336 push @where_purchase, (invnumber => { ilike => '%' . $::form->{invnumber} . '%'});
339 if ($::form->{amount}) {
340 push @where_sale, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
341 push @where_purchase, (amount => $::form->parse_amount(\%::myconfig, $::form->{amount}));
344 if ($::form->{vcnumber}) {
345 push @where_sale, ('customer.customernumber' => { ilike => '%' . $::form->{vcnumber} . '%'});
346 push @where_purchase, ('vendor.vendornumber' => { ilike => '%' . $::form->{vcnumber} . '%'});
349 if ($::form->{vcname}) {
350 push @where_sale, ('customer.name' => { ilike => '%' . $::form->{vcname} . '%'});
351 push @where_purchase, ('vendor.name' => { ilike => '%' . $::form->{vcname} . '%'});
354 if ($::form->{transdatefrom}) {
355 my $fromdate = $::locale->parse_date_to_object(\%::myconfig, $::form->{transdatefrom});
356 push @where_sale, ('transdate' => { ge => $fromdate});
357 push @where_purchase, ('transdate' => { ge => $fromdate});
360 if ($::form->{transdateto}) {
361 my $todate = $::locale->parse_date_to_object(\%::myconfig, $::form->{transdateto});
362 $todate->add(days => 1);
363 push @where_sale, ('transdate' => { lt => $todate});
364 push @where_purchase, ('transdate' => { lt => $todate});
367 my $all_open_ar_invoices = SL::DB::Manager::Invoice->get_all(where => \@where_sale, with_objects => 'customer');
368 my $all_open_ap_invoices = SL::DB::Manager::PurchaseInvoice->get_all(where => \@where_purchase, with_objects => 'vendor');
370 my @all_open_invoices;
371 push @all_open_invoices, @{ $all_open_ar_invoices };
372 push @all_open_invoices, @{ $all_open_ap_invoices };
374 @all_open_invoices = sort { $a->id <=> $b->id } @all_open_invoices;
375 #my $all_open_invoices = SL::DB::Manager::Invoice->get_all(where => \@where);
377 my $output = $self->render(
378 'bank_transactions/add_list',
380 INVOICES => \@all_open_invoices,
383 my %result = ( count => 0, html => $output );
385 $self->render(\to_json(\%result), { type => 'json', process => 0 });
388 sub action_ajax_accept_invoices {
391 my @selected_invoices;
392 foreach my $invoice_id (@{ $::form->{invoice_id} || [] }) {
393 my $invoice_object = SL::DB::Manager::Invoice->find_by(id => $invoice_id);
394 $invoice_object ||= SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id);
396 push @selected_invoices, $invoice_object;
399 $self->render('bank_transactions/invoices', { layout => 0 },
400 INVOICES => \@selected_invoices,
401 bt_id => $::form->{bt_id} );
404 sub action_save_invoices {
407 my $invoice_hash = delete $::form->{invoice_ids};
409 while ( my ($bt_id, $invoice_ids) = each(%$invoice_hash) ) {
410 my $bank_transaction = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
411 my $sign = $bank_transaction->amount < 0 ? -1 : 1;
412 my $amount_of_transaction = $sign * $bank_transaction->amount;
415 foreach my $invoice_id (@{ $invoice_ids }) {
416 push @invoices, (SL::DB::Manager::Invoice->find_by(id => $invoice_id) || SL::DB::Manager::PurchaseInvoice->find_by(id => $invoice_id));
418 @invoices = sort { return 1 if ($a->is_sales and $a->amount > 0);
419 return 1 if (!$a->is_sales and $a->amount < 0);
420 return -1; } @invoices if $bank_transaction->amount > 0;
421 @invoices = sort { return -1 if ($a->is_sales and $a->amount > 0);
422 return -1 if (!$a->is_sales and $a->amount < 0);
423 return 1; } @invoices if $bank_transaction->amount < 0;
425 foreach my $invoice (@invoices) {
426 if ($amount_of_transaction == 0) {
427 flash('warning', $::locale->text('There are invoices which could not be payed by bank transaction #1 (Account number: #2, bank code: #3)!',
428 $bank_transaction->purpose,
429 $bank_transaction->remote_account_number,
430 $bank_transaction->remote_bank_code));
433 #pay invoice or go to the next bank transaction if the amount is not sufficiently high
434 if ($invoice->amount <= $amount_of_transaction) {
435 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id, trans_id => $invoice->id, amount => $invoice->amount, transdate => $bank_transaction->transdate);
436 if ($invoice->is_sales) {
437 $amount_of_transaction -= $sign * $invoice->amount;
438 $bank_transaction->invoice_amount($bank_transaction->invoice_amount + $invoice->amount);
440 $amount_of_transaction += $sign * $invoice->amount if (!$invoice->is_sales);
441 $bank_transaction->invoice_amount($bank_transaction->invoice_amount - $invoice->amount);
444 $invoice->pay_invoice(chart_id => $bank_transaction->local_bank_account->chart_id, trans_id => $invoice->id, amount => $amount_of_transaction, transdate => $bank_transaction->transdate);
445 $bank_transaction->invoice_amount($bank_transaction->amount) if $invoice->is_sales;
446 $bank_transaction->invoice_amount($bank_transaction->amount) if !$invoice->is_sales;
447 $amount_of_transaction = 0;
450 #Record a link from the bank transaction to the invoice
452 from_table => 'bank_transactions',
454 to_table => $invoice->is_sales ? 'ar' : 'ap',
455 to_id => $invoice->id,
458 my $existing = SL::DB::Manager::RecordLink->get_all(where => \@props, limit => 1)->[0];
460 SL::DB::RecordLink->new(@props)->save if !$existing;
462 $bank_transaction->save;
465 $self->action_list();
468 sub action_save_proposals {
471 foreach my $bt_id (@{ $::form->{proposal_ids} }) {
473 my $bt = SL::DB::Manager::BankTransaction->find_by(id => $bt_id);
474 $bt->invoice_amount($bt->amount);
478 my $arap = SL::DB::Manager::Invoice->find_by(id => $::form->{"proposed_invoice_$bt_id"});
479 $arap = SL::DB::Manager::PurchaseInvoice->find_by(id => $::form->{"proposed_invoice_$bt_id"}) if not defined $arap;
480 $arap->pay_invoice(chart_id => $bt->local_bank_account->chart_id,
481 trans_id => $arap->id,
482 amount => $arap->amount,
483 transdate => $bt->transdate);
488 from_table => 'bank_transactions',
490 to_table => $arap->is_sales ? 'ar' : 'ap',
494 my $existing = SL::DB::Manager::RecordLink->get_all(where => \@props, limit => 1)->[0];
496 SL::DB::RecordLink->new(@props)->save if !$existing;
499 flash('ok', t8('#1 proposal(s) saved.', scalar @{ $::form->{proposal_ids} }));
501 $self->action_list();
509 $::auth->assert('bank_transaction');
516 sub make_filter_summary {
519 my $filter = $::form->{filter} || {};
523 [ $filter->{"transdate:date::ge"}, $::locale->text('Transdate') . " " . $::locale->text('From Date') ],
524 [ $filter->{"transdate:date::le"}, $::locale->text('Transdate') . " " . $::locale->text('To Date') ],
525 [ $filter->{"valutadate:date::ge"}, $::locale->text('Valutadate') . " " . $::locale->text('From Date') ],
526 [ $filter->{"valutadate:date::le"}, $::locale->text('Valutadate') . " " . $::locale->text('To Date') ],
527 [ $filter->{"amount:number"}, $::locale->text('Amount') ],
528 [ $filter->{"bank_account_id:integer"}, $::locale->text('Local bank account') ],
532 push @filter_strings, "$_->[1]: $_->[0]" if $_->[0];
535 $self->{filter_summary} = join ', ', @filter_strings;
541 my $callback = $self->models->get_callback;
543 my $report = SL::ReportGenerator->new(\%::myconfig, $::form);
544 $self->{report} = $report;
546 my @columns = qw(transdate valudate remote_name remote_account_number remote_bank_code amount invoice_amount invoices currency purpose local_account_number local_bank_code id);
547 my @sortable = qw(transdate valudate remote_name remote_account_number remote_bank_code amount purpose local_account_number local_bank_code);
550 transdate => { sub => sub { $_[0]->transdate_as_date } },
551 valutadate => { sub => sub { $_[0]->valutadate_as_date } },
553 remote_account_number => { },
554 remote_bank_code => { },
555 amount => { sub => sub { $_[0]->amount_as_number },
557 invoice_amount => { sub => sub { $_[0]->invoice_amount_as_number },
559 invoices => { sub => sub { $_[0]->linked_invoices } },
560 currency => { sub => sub { $_[0]->currency->name } },
562 local_account_number => { sub => sub { $_[0]->local_bank_account->account_number } },
563 local_bank_code => { sub => sub { $_[0]->local_bank_account->bank_code } },
567 map { $column_defs{$_}->{text} ||= $::locale->text( $self->models->get_sort_spec->{$_}->{title} ) } keys %column_defs;
569 $report->set_options(
570 std_column_visibility => 1,
571 controller_class => 'BankTransaction',
572 output_format => 'HTML',
573 top_info_text => $::locale->text('Bank transactions'),
574 title => $::locale->text('Bank transactions'),
575 allow_pdf_export => 1,
576 allow_csv_export => 1,
578 $report->set_columns(%column_defs);
579 $report->set_column_order(@columns);
580 $report->set_export_options(qw(list filter));
581 $report->set_options_from_form;
582 $self->models->disable_pagination if $report->{options}{output_format} =~ /^(pdf|csv)$/i;
583 $self->models->set_report_generator_sort_options(report => $report, sortable_columns => \@sortable);
585 my $bank_accounts = SL::DB::Manager::BankAccount->get_all();
586 my $label_sub = sub { t8('#1 - Account number #2, bank code #3, #4', $_[0]->name, $_[0]->account_number, $_[0]->bank_code, $_[0]->bank )};
588 $report->set_options(
589 raw_top_info_text => $self->render('bank_transactions/report_top', { output => 0 }, BANK_ACCOUNTS => $bank_accounts, label_sub => $label_sub),
590 raw_bottom_info_text => $self->render('bank_transactions/report_bottom', { output => 0 }),
597 SL::Controller::Helper::GetModels->new(
604 transdate => t8('Transdate'),
605 remote_name => t8('Remote name'),
606 amount => t8('Amount'),
607 invoice_amount => t8('Assigned'),
608 invoices => t8('Linked invoices'),
609 valutadate => t8('Valutadate'),
610 remote_account_number => t8('Remote account number'),
611 remote_bank_code => t8('Remote bank code'),
612 currency => t8('Currency'),
613 purpose => t8('Purpose'),
614 local_account_number => t8('Local account number'),
615 local_bank_code => t8('Local bank code'),
617 with_objects => [ 'local_bank_account', 'currency' ],