1 package SL::Controller::BankImport;
5 use parent qw(SL::Controller::Base);
7 use List::MoreUtils qw(apply);
8 use List::Util qw(max min);
10 use SL::DB::BankAccount;
11 use SL::DB::BankTransaction;
13 use SL::Helper::Flash;
15 use SL::SessionFile::Random;
17 use Rose::Object::MakeMethods::Generic
19 scalar => [ qw(file_name transactions statistics charset) ],
20 'scalar --get_set_init' => [ qw(bank_accounts) ],
23 __PACKAGE__->run_before('check_auth');
25 sub action_upload_mt940 {
26 my ($self, %params) = @_;
28 $self->setup_upload_mt940_action_bar;
29 $self->render('bank_import/upload_mt940', title => $::locale->text('MT940 import'));
32 sub action_import_mt940_preview {
33 my ($self, %params) = @_;
35 if (!$::form->{file}) {
36 flash_later('error', $::locale->text('You have to upload an MT940 file to import.'));
37 return $self->redirect_to(action => 'upload_mt940');
40 die "missing file for action import_mt940_preview" unless $::form->{file};
42 my $file = SL::SessionFile::Random->new(mode => '>');
43 $file->fh->print($::form->{file});
46 $self->charset($::form->{charset});
47 $self->file_name($file->file_name);
48 $self->parse_and_analyze_transactions;
50 $self->setup_upload_mt940_preview_action_bar;
51 $self->render('bank_import/import_mt940', title => $::locale->text('MT940 import preview'), preview => 1);
54 sub action_import_mt940 {
55 my ($self, %params) = @_;
57 die "missing file for action import_mt940" unless $::form->{file_name};
59 $self->file_name($::form->{file_name});
60 $self->charset($::form->{charset});
61 $self->parse_and_analyze_transactions;
62 $self->import_transactions;
64 $self->render('bank_import/import_mt940', title => $::locale->text('MT940 import result'));
67 sub parse_and_analyze_transactions {
68 my ($self, %params) = @_;
72 my ($min_date, $max_date);
74 my $currency_id = SL::DB::Default->get->currency_id;
76 $self->transactions([ sort { $a->{transdate} cmp $b->{transdate} } SL::MT940->parse($self->file_name, charset => $self->charset) ]);
78 foreach my $transaction (@{ $self->transactions }) {
79 $transaction->{bank_account} = $self->bank_accounts->{ make_bank_account_idx($transaction->{local_bank_code}, $transaction->{local_account_number}) };
80 $transaction->{bank_account} //= $self->bank_accounts->{ make_bank_account_idx('IBAN', $transaction->{local_account_number}) };
82 if (!$transaction->{bank_account}) {
83 $transaction->{error} = $::locale->text('No bank account configured for bank code/BIC #1, account number/IBAN #2.', $transaction->{local_bank_code}, $transaction->{local_account_number});
88 $transaction->{local_bank_account_id} = $transaction->{bank_account}->id;
89 $transaction->{currency_id} = $currency_id;
91 $min_date = min($min_date // $transaction->{transdate}, $transaction->{transdate});
92 $max_date = max($max_date // $transaction->{transdate}, $transaction->{transdate});
95 my %existing_bank_transactions;
97 if ((scalar(@{ $self->transactions }) - $errors) > 0) {
99 @{ SL::DB::Manager::BankTransaction->get_all(
101 transdate => { ge => $min_date },
102 transdate => { lt => $max_date->clone->add(days => 1) },
104 inject_results => 1) };
106 %existing_bank_transactions = map { (make_transaction_idx($_) => 1) } @entries;
109 foreach my $transaction (@{ $self->transactions }) {
110 next if $transaction->{error};
112 if ($existing_bank_transactions{make_transaction_idx($transaction)}) {
113 $transaction->{duplicate} = 1;
120 total => scalar(@{ $self->transactions }),
122 duplicates => $duplicates,
123 to_import => scalar(@{ $self->transactions }) - $errors - $duplicates,
127 sub import_transactions {
128 my ($self, %params) = @_;
132 SL::DB::client->with_transaction(sub {
136 foreach my $transaction (@{ $self->transactions }) {
137 next if $transaction->{error} || $transaction->{duplicate};
139 SL::DB::BankTransaction->new(
140 map { ($_ => $transaction->{$_}) } qw(amount currency_id local_bank_account_id purpose remote_account_number remote_bank_code remote_name transaction_code transdate valutadate)
149 $self->statistics->{imported} = $imported;
153 $::auth->assert('bank_transaction');
156 sub make_bank_account_idx {
157 return join '/', map { my $q = $_; $q =~ s{ +}{}g; $q } @_;
163 $text = lc($text // '');
169 sub make_transaction_idx {
170 my ($transaction) = @_;
172 if (ref($transaction) eq 'SL::DB::BankTransaction') {
173 $transaction = { map { ($_ => $transaction->$_) } qw(local_bank_account_id transdate valutadate amount purpose) };
176 return normalize_text(join '/',
178 ($transaction->{local_bank_account_id},
179 $transaction->{transdate}->ymd,
180 $transaction->{valutadate}->ymd,
181 (apply { s{0+$}{} } $transaction->{amount} * 1),
182 $transaction->{purpose}));
185 sub init_bank_accounts {
190 foreach my $bank_account (@{ SL::DB::Manager::BankAccount->get_all }) {
191 if ($bank_account->bank_code && $bank_account->account_number) {
192 $bank_accounts{make_bank_account_idx($bank_account->bank_code, $bank_account->account_number)} = $bank_account;
194 if ($bank_account->iban) {
195 $bank_accounts{make_bank_account_idx('IBAN', $bank_account->iban)} = $bank_account;
199 return \%bank_accounts;
202 sub setup_upload_mt940_action_bar {
205 for my $bar ($::request->layout->get('actionbar')) {
208 $::locale->text('Preview'),
209 submit => [ '#form', { action => 'BankImport/import_mt940_preview' } ],
210 accesskey => 'enter',
216 sub setup_upload_mt940_preview_action_bar {
219 for my $bar ($::request->layout->get('actionbar')) {
222 $::locale->text('Import'),
223 submit => [ '#form', { action => 'BankImport/import_mt940' } ],
224 accesskey => 'enter',
225 disabled => $self->statistics->{to_import} ? undef : $::locale->text('No entries can be imported.'),