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