SL/Controller/BankImport.pm

   1 package SL::Controller::BankImport;
   2
   3 use strict;
   4
   5 use parent qw(SL::Controller::Base);
   6
   7 use List::MoreUtils qw(apply);
   8 use List::Util qw(max min);
   9
  10 use SL::DB::BankAccount;
  11 use SL::DB::BankTransaction;
  12 use SL::DB::Default;
  13 use SL::Helper::Flash;
  14 use SL::MT940;
  15 use SL::SessionFile::Random;
  16
  17 use Rose::Object::MakeMethods::Generic
  18 (
  19   scalar                  => [ qw(file_name transactions statistics charset) ],
  20   'scalar --get_set_init' => [ qw(bank_accounts) ],
  21 );
  22
  23 __PACKAGE__->run_before('check_auth');
  24
  25 sub action_upload_mt940 {
  26   my ($self, %params) = @_;
  27
  28   $self->setup_upload_mt940_action_bar;
  29   $self->render('bank_import/upload_mt940', title => $::locale->text('MT940 import'));
  30 }
  31
  32 sub action_import_mt940_preview {
  33   my ($self, %params) = @_;
  34
  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');
  38   }
  39
  40   die "missing file for action import_mt940_preview" unless $::form->{file};
  41
  42   my $file = SL::SessionFile::Random->new(mode => '>');
  43   $file->fh->print($::form->{file});
  44   $file->fh->close;
  45
  46   $self->charset($::form->{charset});
  47   $self->file_name($file->file_name);
  48   $self->parse_and_analyze_transactions;
  49
  50   $self->setup_upload_mt940_preview_action_bar;
  51   $self->render('bank_import/import_mt940', title => $::locale->text('MT940 import preview'), preview => 1);
  52 }
  53
  54 sub action_import_mt940 {
  55   my ($self, %params) = @_;
  56
  57   die "missing file for action import_mt940" unless $::form->{file_name};
  58
  59   $self->file_name($::form->{file_name});
  60   $self->charset($::form->{charset});
  61   $self->parse_and_analyze_transactions;
  62   $self->import_transactions;
  63
  64   $self->render('bank_import/import_mt940', title => $::locale->text('MT940 import result'));
  65 }
  66
  67 sub parse_and_analyze_transactions {
  68   my ($self, %params) = @_;
  69
  70   my $errors     = 0;
  71   my $duplicates = 0;
  72   my ($min_date, $max_date);
  73
  74   my $currency_id = SL::DB::Default->get->currency_id;
  75
  76   $self->transactions([ sort { $a->{transdate} cmp $b->{transdate} } SL::MT940->parse($self->file_name, charset => $self->charset) ]);
  77
  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}) };
  81
  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});
  84       $errors++;
  85       next;
  86     }
  87
  88     $transaction->{local_bank_account_id} = $transaction->{bank_account}->id;
  89     $transaction->{currency_id}           = $currency_id;
  90
  91     $min_date = min($min_date // $transaction->{transdate}, $transaction->{transdate});
  92     $max_date = max($max_date // $transaction->{transdate}, $transaction->{transdate});
  93   }
  94
  95   my %existing_bank_transactions;
  96
  97   if ((scalar(@{ $self->transactions }) - $errors) > 0) {
  98     my @entries =
  99       @{ SL::DB::Manager::BankTransaction->get_all(
 100           where => [
 101             transdate => { ge => $min_date },
 102             transdate => { lt => $max_date->clone->add(days => 1) },
 103           ],
 104           inject_results => 1) };
 105
 106     %existing_bank_transactions = map { (make_transaction_idx($_) => 1) } @entries;
 107   }
 108
 109   foreach my $transaction (@{ $self->transactions }) {
 110     next if $transaction->{error};
 111
 112     if ($existing_bank_transactions{make_transaction_idx($transaction)}) {
 113       $transaction->{duplicate} = 1;
 114       $duplicates++;
 115       next;
 116     }
 117   }
 118
 119   $self->statistics({
 120     total      => scalar(@{ $self->transactions }),
 121     errors     => $errors,
 122     duplicates => $duplicates,
 123     to_import  => scalar(@{ $self->transactions }) - $errors - $duplicates,
 124   });
 125 }
 126
 127 sub import_transactions {
 128   my ($self, %params) = @_;
 129
 130   my $imported = 0;
 131
 132   SL::DB::client->with_transaction(sub {
 133     # make Emacs happy
 134     1;
 135
 136     foreach my $transaction (@{ $self->transactions }) {
 137       next if $transaction->{error} || $transaction->{duplicate};
 138
 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)
 141       )->save;
 142
 143       $imported++;
 144     }
 145
 146     1;
 147   });
 148
 149   $self->statistics->{imported} = $imported;
 150 }
 151
 152 sub check_auth {
 153   $::auth->assert('bank_transaction');
 154 }
 155
 156 sub make_bank_account_idx {
 157   return join '/', map { my $q = $_; $q =~ s{ +}{}g; $q } @_;
 158 }
 159
 160 sub normalize_text {
 161   my ($text) = @_;
 162
 163   $text = lc($text // '');
 164   $text =~ s{ }{}g;
 165
 166   return $text;
 167 }
 168
 169 sub make_transaction_idx {
 170   my ($transaction) = @_;
 171
 172   if (ref($transaction) eq 'SL::DB::BankTransaction') {
 173     $transaction = { map { ($_ => $transaction->$_) } qw(local_bank_account_id transdate valutadate amount purpose) };
 174   }
 175
 176   return normalize_text(join '/',
 177                         map { $_ // '' }
 178                         ($transaction->{local_bank_account_id},
 179                          $transaction->{transdate}->ymd,
 180                          $transaction->{valutadate}->ymd,
 181                          (apply { s{0+$}{} } $transaction->{amount} * 1),
 182                          $transaction->{purpose}));
 183 }
 184
 185 sub init_bank_accounts {
 186   my ($self) = @_;
 187
 188   my %bank_accounts;
 189
 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;
 193     }
 194     if ($bank_account->iban) {
 195       $bank_accounts{make_bank_account_idx('IBAN', $bank_account->iban)} = $bank_account;
 196     }
 197   }
 198
 199   return \%bank_accounts;
 200 }
 201
 202 sub setup_upload_mt940_action_bar {
 203   my ($self) = @_;
 204
 205   for my $bar ($::request->layout->get('actionbar')) {
 206     $bar->add(
 207       action => [
 208         $::locale->text('Preview'),
 209         submit    => [ '#form', { action => 'BankImport/import_mt940_preview' } ],
 210         accesskey => 'enter',
 211       ],
 212     );
 213   }
 214 }
 215
 216 sub setup_upload_mt940_preview_action_bar {
 217   my ($self) = @_;
 218
 219   for my $bar ($::request->layout->get('actionbar')) {
 220     $bar->add(
 221       action => [
 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.'),
 226       ],
 227     );
 228   }
 229 }
 230
 231 1;