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