5 use POSIX qw(strftime);
9 sub retrieve_open_invoices {
10 $main::lxdebug->enter_sub();
15 my $myconfig = \%main::myconfig;
16 my $form = $main::form;
18 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
22 SELECT ap.id, ap.invnumber, ap.vendor_id, ap.amount AS invoice_amount, ap.invoice,
25 COALESCE(v.iban, '') <> '' AND COALESCE(v.bic, '') <> '' AS vendor_bank_info_ok,
27 ap.amount - ap.paid - COALESCE(open_transfers.amount, 0) AS open_amount
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)
36 AS open_transfers ON (ap.id = open_transfers.ap_id)
38 WHERE ap.amount > (COALESCE(open_transfers.amount, 0) + ap.paid)
40 ORDER BY lower(v.name) ASC, lower(ap.invnumber) ASC
43 my $results = selectall_hashref_query($form, $dbh, $query);
45 $main::lxdebug->leave_sub();
51 $main::lxdebug->enter_sub();
56 Common::check_params(\%params, qw(employee bank_transfers));
58 my $myconfig = \%main::myconfig;
59 my $form = $main::form;
61 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
63 my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|);
65 qq|INSERT INTO sepa_export (id, employee_id)
69 do_query($form, $dbh, $query, $export_id, $params{employee});
71 my $q_item_id = qq|SELECT nextval('id')|;
72 my $h_item_id = prepare_query($form, $dbh, $q_item_id);
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)
81 my $h_insert = prepare_query($form, $dbh, $q_insert);
84 qq|SELECT ap.invnumber,
87 LEFT JOIN chart c ON (at.chart_id = c.id)
88 WHERE (at.trans_id = ?)
89 AND (c.link LIKE '%AP_paid%'))
92 FROM sepa_export_items sei
93 WHERE (sei.ap_id = ?))
97 my $h_reference = prepare_query($form, $dbh, $q_reference);
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);
105 my ($invnumber, $num_payments) = $h_reference->fetchrow_array();
108 $transfer->{reference} = "${invnumber}-${num_payments}";
111 $h_item_id->execute();
112 my ($item_id) = $h_item_id->fetchrow_array();
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;
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));
127 do_statement($form, $h_insert, $q_insert, @values);
131 $h_item_id->finish();
133 $dbh->commit() unless ($params{dbh});
135 $main::lxdebug->leave_sub();
140 sub retrieve_export {
141 $main::lxdebug->enter_sub();
146 Common::check_params(\%params, qw(id));
148 my $myconfig = \%main::myconfig;
149 my $form = $main::form;
151 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
153 my ($joins, $columns);
155 if ($params{details}) {
156 $columns = ', ap.invoice';
157 $joins = 'LEFT JOIN ap ON (se.ap_id = ap.id)';
162 CASE WHEN COALESCE(e.name, '') <> '' THEN e.name ELSE e.login END AS employee
164 LEFT JOIN employee e ON (se.employee_id = e.id)
167 my $export = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id}));
170 my ($columns, $joins);
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)|;
179 $query = qq|SELECT sei.*
181 FROM sepa_export_items sei
183 WHERE sei.sepa_export_id = ?|;
184 $export->{items} = selectall_hashref_query($form, $dbh, $query, conv_i($params{id}));
187 $export->{items} = [];
190 $main::lxdebug->leave_sub();
196 $main::lxdebug->enter_sub();
201 Common::check_params(\%params, qw(id));
203 my $myconfig = \%main::myconfig;
204 my $form = $main::form;
206 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
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)|;
212 do_query($form, $dbh, $query, map { conv_i($_) } @ids);
214 $dbh->commit() unless ($params{dbh});
216 $main::lxdebug->leave_sub();
220 $main::lxdebug->enter_sub();
225 my $myconfig = \%main::myconfig;
226 my $form = $main::form;
228 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
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', ],
238 my %sort_spec = create_sort_spec('defs' => \%sort_columns, 'default' => 'id', 'column' => $params{sortorder}, 'dir' => $params{sortdir});
240 my (@where, @values, @where_sub, @values_sub, %joins_sub);
242 my $filter = $params{filter} || { };
244 foreach (qw(executed closed)) {
245 push @where, $filter->{$_} ? "se.$_" : "NOT se.$_" if (exists $filter->{$_});
248 my %operators = ('from' => '>=',
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}"};
257 if ($filter->{invnumber}) {
258 push @where_sub, "ap.invnumber ILIKE ?";
259 push @values_sub, '%' . $filter->{invnumber} . '%';
263 if ($filter->{vendor}) {
264 push @where_sub, "v.name ILIKE ?";
265 push @values_sub, '%' . $filter->{vendor} . '%';
267 $joins_sub{vendor} = 1;
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_${_}"};
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});
283 my $where_sub = join(' AND ', map { "(${_})" } @where_sub);
285 my $query_sub = qq|se.id IN (SELECT items.sepa_export_id
286 FROM sepa_export_items items
290 push @where, $query_sub;
291 push @values, @values_sub;
294 my $where = ' WHERE ' . join(' AND ', map { "(${_})" } @where) if (@where);
297 qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date,
302 CASE WHEN COALESCE(emp.name, '') <> '' THEN emp.name ELSE emp.login END AS name
304 ) AS e ON (se.employee_id = e.id)
306 ORDER BY $sort_spec{sql}|;
308 my $results = selectall_hashref_query($form, $dbh, $query, @values);
310 $main::lxdebug->leave_sub();
316 $main::lxdebug->enter_sub();
321 Common::check_params(\%params, qw(items));
323 my $myconfig = \%main::myconfig;
324 my $form = $main::form;
326 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
328 my @items = ref $params{items} eq 'ARRAY' ? @{ $params{items} } : ($params{items});
331 'get_item' => [ qq|SELECT sei.*
332 FROM sepa_export_items sei
335 'get_ap' => [ qq|SELECT at.chart_id
337 LEFT JOIN chart c ON (at.chart_id = c.id)
339 AND ((c.link LIKE '%:AP') OR (c.link LIKE 'AP:%') OR (c.link = 'AP'))
342 'add_acc_trans' => [ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo)
343 VALUES (?, ?, ?, ?, current_date, ?, '')| ],
345 'update_ap' => [ qq|UPDATE ap
349 'finish_item' => [ qq|UPDATE sepa_export_items
350 SET execution_date = ?, executed = TRUE
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
358 AND NOT COALESCE(sei1.executed, FALSE)
361 'do_close' => [ qq|UPDATE sepa_export
362 SET executed = TRUE, closed = TRUE
366 map { unshift @{ $_ }, prepare_query($form, $dbh, $_->[0]) } values %handles;
368 foreach my $item (@items) {
369 my $item_id = conv_i($item->{id});
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();
375 next if (!$orig_item);
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();
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});
385 # Update the invoice to reflect the new paid amount.
386 do_statement($form, @{ $handles{update_ap} }, $orig_item->{amount}, $orig_item->{ap_id});
388 # Update the item to reflect that it has been posted.
389 do_statement($form, @{ $handles{finish_item} }, $item->{execution_date}, $item_id);
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();
395 if (!$has_unexecuted) {
396 do_statement($form, @{ $handles{do_close} }, $orig_item->{sepa_export_id});
400 map { $_->[0]->finish() } values %handles;
402 $dbh->commit() unless ($params{dbh});
404 $main::lxdebug->leave_sub();