SL/SEPA.pm

   1 package SL::SEPA;
   2
   3 use strict;
   4
   5 use POSIX qw(strftime);
   6
   7 use SL::DBUtils;
   8
   9 sub retrieve_open_invoices {
  10   $main::lxdebug->enter_sub();
  11
  12   my $self     = shift;
  13   my %params   = @_;
  14
  15   my $myconfig = \%main::myconfig;
  16   my $form     = $main::form;
  17
  18   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
  19
  20   my $query =
  21     qq|
  22        SELECT ap.id, ap.invnumber, ap.vendor_id, ap.amount AS invoice_amount, ap.invoice,
  23          v.name AS vendorname, ap.duedate as duedate,
  24
  25          COALESCE(v.iban, '') <> '' AND COALESCE(v.bic, '') <> '' AS vendor_bank_info_ok,
  26
  27          ap.amount - ap.paid - COALESCE(open_transfers.amount, 0) AS open_amount
  28
  29        FROM ap
  30        LEFT JOIN vendor v ON (ap.vendor_id = v.id)
  31        LEFT JOIN (SELECT sei.ap_id, SUM(sei.amount) AS amount
  32                   FROM sepa_export_items sei
  33                   LEFT JOIN sepa_export se ON (sei.sepa_export_id = se.id)
  34                   WHERE NOT se.closed
  35                   GROUP BY sei.ap_id)
  36          AS open_transfers ON (ap.id = open_transfers.ap_id)
  37
  38        WHERE ap.amount > (COALESCE(open_transfers.amount, 0) + ap.paid)
  39
  40        ORDER BY lower(v.name) ASC, lower(ap.invnumber) ASC
  41 |;
  42
  43   my $results = selectall_hashref_query($form, $dbh, $query);
  44
  45   $main::lxdebug->leave_sub();
  46
  47   return $results;
  48 }
  49
  50 sub create_export {
  51   $main::lxdebug->enter_sub();
  52
  53   my $self     = shift;
  54   my %params   = @_;
  55
  56   Common::check_params(\%params, qw(employee bank_transfers));
  57
  58   my $myconfig = \%main::myconfig;
  59   my $form     = $main::form;
  60
  61   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
  62
  63   my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|);
  64   my $query       =
  65     qq|INSERT INTO sepa_export (id, employee_id)
  66        VALUES (?, (SELECT id
  67                    FROM employee
  68                    WHERE login = ?))|;
  69   do_query($form, $dbh, $query, $export_id, $params{employee});
  70
  71   my $q_item_id = qq|SELECT nextval('id')|;
  72   my $h_item_id = prepare_query($form, $dbh, $q_item_id);
  73
  74   my $q_insert =
  75     qq|INSERT INTO sepa_export_items (id,          sepa_export_id,           ap_id,       chart_id,
  76                                       amount,      requested_execution_date, reference,   end_to_end_id,
  77                                       our_iban,    our_bic,                  vendor_iban, vendor_bic)
  78        VALUES                        (?,           ?,                        ?,           ?,
  79                                       ?,           ?,                        ?,           ?,
  80                                       ?,           ?,                        ?,           ?)|;
  81   my $h_insert = prepare_query($form, $dbh, $q_insert);
  82
  83   my $q_reference =
  84     qq|SELECT ap.invnumber,
  85          (SELECT COUNT(at.*)
  86           FROM acc_trans at
  87           LEFT JOIN chart c ON (at.chart_id = c.id)
  88           WHERE (at.trans_id = ?)
  89             AND (c.link LIKE '%AP_paid%'))
  90          +
  91          (SELECT COUNT(sei.*)
  92           FROM sepa_export_items sei
  93           WHERE (sei.ap_id = ?))
  94          AS num_payments
  95        FROM ap
  96        WHERE id = ?|;
  97   my $h_reference = prepare_query($form, $dbh, $q_reference);
  98
  99   my @now         = localtime;
 100
 101   foreach my $transfer (@{ $params{bank_transfers} }) {
 102     if (!$transfer->{reference}) {
 103       do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{ap_id})) x 3);
 104
 105       my ($invnumber, $num_payments) = $h_reference->fetchrow_array();
 106       $num_payments++;
 107
 108       $transfer->{reference} = "${invnumber}-${num_payments}";
 109     }
 110
 111     $h_item_id->execute();
 112     my ($item_id)      = $h_item_id->fetchrow_array();
 113
 114     my $end_to_end_id  = strftime "LXO%Y%m%d%H%M%S", localtime;
 115     my $item_id_len    = length "$item_id";
 116     my $num_zeroes     = 35 - $item_id_len - length $end_to_end_id;
 117     $end_to_end_id    .= '0' x $num_zeroes if (0 < $num_zeroes);
 118     $end_to_end_id    .= $item_id;
 119     $end_to_end_id     = substr $end_to_end_id, 0, 35;
 120
 121     my @values = ($item_id,                   $export_id,
 122                   conv_i($transfer->{ap_id}), conv_i($transfer->{chart_id}),
 123                   $transfer->{amount},        conv_date($transfer->{requested_execution_date}),
 124                   $transfer->{reference},     $end_to_end_id,
 125                   map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vendor));
 126
 127     do_statement($form, $h_insert, $q_insert, @values);
 128   }
 129
 130   $h_insert->finish();
 131   $h_item_id->finish();
 132
 133   $dbh->commit() unless ($params{dbh});
 134
 135   $main::lxdebug->leave_sub();
 136
 137   return $export_id;
 138 }
 139
 140 sub retrieve_export {
 141   $main::lxdebug->enter_sub();
 142
 143   my $self     = shift;
 144   my %params   = @_;
 145
 146   Common::check_params(\%params, qw(id));
 147
 148   my $myconfig = \%main::myconfig;
 149   my $form     = $main::form;
 150
 151   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 152
 153   my ($joins, $columns);
 154
 155   if ($params{details}) {
 156     $columns = ', ap.invoice';
 157     $joins   = 'LEFT JOIN ap ON (se.ap_id = ap.id)';
 158   }
 159
 160   my $query =
 161     qq|SELECT se.*,
 162          CASE WHEN COALESCE(e.name, '') <> '' THEN e.name ELSE e.login END AS employee
 163        FROM sepa_export se
 164        LEFT JOIN employee e ON (se.employee_id = e.id)
 165        WHERE se.id = ?|;
 166
 167   my $export = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id}));
 168
 169   if ($export->{id}) {
 170     my ($columns, $joins);
 171
 172     if ($params{details}) {
 173       $columns = qq|, ap.invnumber, ap.invoice, v.name AS vendor_name, c.accno AS chart_accno, c.description AS chart_description|;
 174       $joins   = qq|LEFT JOIN ap       ON (sei.ap_id    = ap.id)
 175                     LEFT JOIN vendor v ON (ap.vendor_id = v.id)
 176                     LEFT JOIN chart c  ON (sei.chart_id = c.id)|;
 177     }
 178
 179     $query = qq|SELECT sei.*
 180                   $columns
 181                 FROM sepa_export_items sei
 182                 $joins
 183                 WHERE sei.sepa_export_id = ?|;
 184     $export->{items} = selectall_hashref_query($form, $dbh, $query, conv_i($params{id}));
 185
 186   } else {
 187     $export->{items} = [];
 188   }
 189
 190   $main::lxdebug->leave_sub();
 191
 192   return $export;
 193 }
 194
 195 sub close_export {
 196   $main::lxdebug->enter_sub();
 197
 198   my $self     = shift;
 199   my %params   = @_;
 200
 201   Common::check_params(\%params, qw(id));
 202
 203   my $myconfig = \%main::myconfig;
 204   my $form     = $main::form;
 205
 206   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 207
 208   my @ids          = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
 209   my $placeholders = join ', ', ('?') x scalar @ids;
 210   my $query        = qq|UPDATE sepa_export SET closed = TRUE WHERE id IN ($placeholders)|;
 211
 212   do_query($form, $dbh, $query, map { conv_i($_) } @ids);
 213
 214   $dbh->commit() unless ($params{dbh});
 215
 216   $main::lxdebug->leave_sub();
 217 }
 218
 219 sub list_exports {
 220   $main::lxdebug->enter_sub();
 221
 222   my $self     = shift;
 223   my %params   = @_;
 224
 225   my $myconfig = \%main::myconfig;
 226   my $form     = $main::form;
 227
 228   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 229
 230   my %sort_columns = (
 231     'id'          => [ 'se.id',                ],
 232     'export_date' => [ 'se.itime',             ],
 233     'employee'    => [ 'e.name',      'se.id', ],
 234     'executed'    => [ 'se.executed', 'se.id', ],
 235     'closed'      => [ 'se.closed',   'se.id', ],
 236     );
 237
 238   my %sort_spec = create_sort_spec('defs' => \%sort_columns, 'default' => 'id', 'column' => $params{sortorder}, 'dir' => $params{sortdir});
 239
 240   my (@where, @values, @where_sub, @values_sub, %joins_sub);
 241
 242   my $filter = $params{filter} || { };
 243
 244   foreach (qw(executed closed)) {
 245     push @where, $filter->{$_} ? "se.$_" : "NOT se.$_" if (exists $filter->{$_});
 246   }
 247
 248   my %operators = ('from' => '>=',
 249                    'to'   => '<=');
 250
 251   foreach my $dir (qw(from to)) {
 252     next unless ($filter->{"export_date_${dir}"});
 253     push @where,  "se.itime $operators{$dir} ?::date";
 254     push @values, $filter->{"export_date_${dir}"};
 255   }
 256
 257   if ($filter->{invnumber}) {
 258     push @where_sub,  "ap.invnumber ILIKE ?";
 259     push @values_sub, '%' . $filter->{invnumber} . '%';
 260     $joins_sub{ap} = 1;
 261   }
 262
 263   if ($filter->{vendor}) {
 264     push @where_sub,  "v.name ILIKE ?";
 265     push @values_sub, '%' . $filter->{vendor} . '%';
 266     $joins_sub{ap}     = 1;
 267     $joins_sub{vendor} = 1;
 268   }
 269
 270   foreach my $type (qw(requested_execution execution)) {
 271     foreach my $dir (qw(from to)) {
 272       next unless ($filter->{"${type}_date_${dir}"});
 273       push @where_sub,  "(items.${type}_date IS NOT NULL) AND (items.${type}_date $operators{$dir} ?)";
 274       push @values_sub, $filter->{"${type}_date_${_}"};
 275     }
 276   }
 277
 278   if (@where_sub) {
 279     my $joins_sub  = '';
 280     $joins_sub    .= ' LEFT JOIN ap       ON (items.ap_id  = ap.id)' if ($joins_sub{ap});
 281     $joins_sub    .= ' LEFT JOIN vendor v ON (ap.vendor_id = v.id)'  if ($joins_sub{vendor});
 282
 283     my $where_sub  = join(' AND ', map { "(${_})" } @where_sub);
 284
 285     my $query_sub  = qq|se.id IN (SELECT items.sepa_export_id
 286                                   FROM sepa_export_items items
 287                                   $joins_sub
 288                                   WHERE $where_sub)|;
 289
 290     push @where,  $query_sub;
 291     push @values, @values_sub;
 292   }
 293
 294   my $where = ' WHERE ' . join(' AND ', map { "(${_})" } @where) if (@where);
 295
 296   my $query =
 297     qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date,
 298          e.name AS employee
 299        FROM sepa_export se
 300        LEFT JOIN (
 301          SELECT emp.id,
 302            CASE WHEN COALESCE(emp.name, '') <> '' THEN emp.name ELSE emp.login END AS name
 303          FROM employee emp
 304        ) AS e ON (se.employee_id = e.id)
 305        $where
 306        ORDER BY $sort_spec{sql}|;
 307
 308   my $results = selectall_hashref_query($form, $dbh, $query, @values);
 309
 310   $main::lxdebug->leave_sub();
 311
 312   return $results;
 313 }
 314
 315 sub post_payment {
 316   $main::lxdebug->enter_sub();
 317
 318   my $self     = shift;
 319   my %params   = @_;
 320
 321   Common::check_params(\%params, qw(items));
 322
 323   my $myconfig = \%main::myconfig;
 324   my $form     = $main::form;
 325
 326   my $dbh      = $params{dbh} || $form->get_standard_dbh($myconfig);
 327
 328   my @items    = ref $params{items} eq 'ARRAY' ? @{ $params{items} } : ($params{items});
 329
 330   my %handles  = (
 331     'get_item'       => [ qq|SELECT sei.*
 332                              FROM sepa_export_items sei
 333                              WHERE sei.id = ?| ],
 334
 335     'get_ap'         => [ qq|SELECT at.chart_id
 336                              FROM acc_trans at
 337                              LEFT JOIN chart c ON (at.chart_id = c.id)
 338                              WHERE (trans_id = ?)
 339                                AND ((c.link LIKE '%:AP') OR (c.link LIKE 'AP:%') OR (c.link = 'AP'))
 340                              LIMIT 1| ],
 341
 342     'add_acc_trans'  => [ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate,       source, memo)
 343                              VALUES                (?,        ?,        ?,      ?,         current_date, ?,      '')| ],
 344
 345     'update_ap'      => [ qq|UPDATE ap
 346                              SET paid = paid + ?
 347                              WHERE id = ?| ],
 348
 349     'finish_item'    => [ qq|UPDATE sepa_export_items
 350                              SET execution_date = ?, executed = TRUE
 351                              WHERE id = ?| ],
 352
 353     'has_unexecuted' => [ qq|SELECT sei1.id
 354                              FROM sepa_export_items sei1
 355                              WHERE (sei1.sepa_export_id = (SELECT sei2.sepa_export_id
 356                                                            FROM sepa_export_items sei2
 357                                                            WHERE sei2.id = ?))
 358                                AND NOT COALESCE(sei1.executed, FALSE)
 359                              LIMIT 1| ],
 360
 361     'do_close'       => [ qq|UPDATE sepa_export
 362                              SET executed = TRUE, closed = TRUE
 363                              WHERE (id = ?)| ],
 364     );
 365
 366   map { unshift @{ $_ }, prepare_query($form, $dbh, $_->[0]) } values %handles;
 367
 368   foreach my $item (@items) {
 369     my $item_id = conv_i($item->{id});
 370
 371     # Retrieve the item data belonging to the ID.
 372     do_statement($form, @{ $handles{get_item} }, $item_id);
 373     my $orig_item = $handles{get_item}->[0]->fetchrow_hashref();
 374
 375     next if (!$orig_item);
 376
 377     # Retrieve the invoice's AP chart ID.
 378     do_statement($form, @{ $handles{get_ap} }, $orig_item->{ap_id});
 379     my ($ap_chart_id) = $handles{get_ap}->[0]->fetchrow_array();
 380
 381     # Record the payment in acc_trans offsetting AP.
 382     do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{ap_id}, $ap_chart_id,           -1 * $orig_item->{amount}, $item->{execution_date}, '');
 383     do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{ap_id}, $orig_item->{chart_id},      $orig_item->{amount}, $item->{execution_date}, $orig_item->{reference});
 384
 385     # Update the invoice to reflect the new paid amount.
 386     do_statement($form, @{ $handles{update_ap} }, $orig_item->{amount}, $orig_item->{ap_id});
 387
 388     # Update the item to reflect that it has been posted.
 389     do_statement($form, @{ $handles{finish_item} }, $item->{execution_date}, $item_id);
 390
 391     # Check whether or not we can close the export itself if there are no unexecuted items left.
 392     do_statement($form, @{ $handles{has_unexecuted} }, $item_id);
 393     my ($has_unexecuted) = $handles{has_unexecuted}->[0]->fetchrow_array();
 394
 395     if (!$has_unexecuted) {
 396       do_statement($form, @{ $handles{do_close} }, $orig_item->{sepa_export_id});
 397     }
 398   }
 399
 400   map { $_->[0]->finish() } values %handles;
 401
 402   $dbh->commit() unless ($params{dbh});
 403
 404   $main::lxdebug->leave_sub();
 405 }
 406
 407 1;