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);
19 my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap';
20 my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor';
21 my $extra_columns = $arap eq 'ar' ? 'ar.direct_debit, ' : '';
25 SELECT ${arap}.id, ${arap}.invnumber, ${arap}.${vc}_id as vc_id, ${arap}.amount AS invoice_amount, ${arap}.invoice,
26 vc.name AS vcname, vc.language_id, ${arap}.duedate as duedate, ${extra_columns}
28 COALESCE(vc.iban, '') <> '' AND COALESCE(vc.bic, '') <> '' AS vc_bank_info_ok,
30 ${arap}.amount - ${arap}.paid - COALESCE(open_transfers.amount, 0) AS open_amount
33 LEFT JOIN ${vc} vc ON (${arap}.${vc}_id = vc.id)
34 LEFT JOIN (SELECT sei.ap_id, SUM(sei.amount) AS amount
35 FROM sepa_export_items sei
36 LEFT JOIN sepa_export se ON (sei.sepa_export_id = se.id)
40 AS open_transfers ON (${arap}.id = open_transfers.ap_id)
42 WHERE ${arap}.amount > (COALESCE(open_transfers.amount, 0) + ${arap}.paid)
44 ORDER BY lower(vc.name) ASC, lower(${arap}.invnumber) ASC
47 my $results = selectall_hashref_query($form, $dbh, $query);
49 $main::lxdebug->leave_sub();
55 $main::lxdebug->enter_sub();
60 Common::check_params(\%params, qw(employee bank_transfers vc));
62 my $myconfig = \%main::myconfig;
63 my $form = $main::form;
64 my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap';
65 my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor';
68 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
70 my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|);
72 qq|INSERT INTO sepa_export (id, employee_id, vc)
75 WHERE login = ?), ?)|;
76 do_query($form, $dbh, $query, $export_id, $params{employee}, $vc);
78 my $q_item_id = qq|SELECT nextval('id')|;
79 my $h_item_id = prepare_query($form, $dbh, $q_item_id);
82 qq|INSERT INTO sepa_export_items (id, sepa_export_id, ${arap}_id, chart_id,
83 amount, requested_execution_date, reference, end_to_end_id,
84 our_iban, our_bic, vc_iban, vc_bic)
88 my $h_insert = prepare_query($form, $dbh, $q_insert);
91 qq|SELECT arap.invnumber,
94 LEFT JOIN chart c ON (at.chart_id = c.id)
95 WHERE (at.trans_id = ?)
96 AND (c.link LIKE '%${ARAP}_paid%'))
99 FROM sepa_export_items sei
100 WHERE (sei.ap_id = ?))
104 my $h_reference = prepare_query($form, $dbh, $q_reference);
108 foreach my $transfer (@{ $params{bank_transfers} }) {
109 if (!$transfer->{reference}) {
110 do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{"${arap}_id"})) x 3);
112 my ($invnumber, $num_payments) = $h_reference->fetchrow_array();
115 $transfer->{reference} = "${invnumber}-${num_payments}";
118 $h_item_id->execute();
119 my ($item_id) = $h_item_id->fetchrow_array();
121 my $end_to_end_id = strftime "LXO%Y%m%d%H%M%S", localtime;
122 my $item_id_len = length "$item_id";
123 my $num_zeroes = 35 - $item_id_len - length $end_to_end_id;
124 $end_to_end_id .= '0' x $num_zeroes if (0 < $num_zeroes);
125 $end_to_end_id .= $item_id;
126 $end_to_end_id = substr $end_to_end_id, 0, 35;
128 my @values = ($item_id, $export_id,
129 conv_i($transfer->{"${arap}_id"}), conv_i($transfer->{chart_id}),
130 $transfer->{amount}, conv_date($transfer->{requested_execution_date}),
131 $transfer->{reference}, $end_to_end_id,
132 map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vc));
134 do_statement($form, $h_insert, $q_insert, @values);
138 $h_item_id->finish();
140 $dbh->commit() unless ($params{dbh});
142 $main::lxdebug->leave_sub();
147 sub retrieve_export {
148 $main::lxdebug->enter_sub();
153 Common::check_params(\%params, qw(id vc));
155 my $myconfig = \%main::myconfig;
156 my $form = $main::form;
157 my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor';
158 my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap';
160 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
162 my ($joins, $columns);
164 if ($params{details}) {
165 $columns = ', arap.invoice';
166 $joins = "LEFT JOIN ${arap} arap ON (se.${arap}_id = arap.id)";
171 CASE WHEN COALESCE(e.name, '') <> '' THEN e.name ELSE e.login END AS employee
173 LEFT JOIN employee e ON (se.employee_id = e.id)
176 my $export = selectfirst_hashref_query($form, $dbh, $query, conv_i($params{id}));
179 my ($columns, $joins);
181 if ($params{details}) {
182 $columns = qq|, arap.invnumber, arap.invoice, arap.transdate AS reference_date, vc.name AS vc_name, c.accno AS chart_accno, c.description AS chart_description|;
183 $joins = qq|LEFT JOIN ${arap} arap ON (sei.${arap}_id = arap.id)
184 LEFT JOIN ${vc} vc ON (arap.${vc}_id = vc.id)
185 LEFT JOIN chart c ON (sei.chart_id = c.id)|;
188 $query = qq|SELECT sei.*
190 FROM sepa_export_items sei
192 WHERE sei.sepa_export_id = ?|;
194 $export->{items} = selectall_hashref_query($form, $dbh, $query, conv_i($params{id}));
197 $export->{items} = [];
200 $main::lxdebug->leave_sub();
206 $main::lxdebug->enter_sub();
211 Common::check_params(\%params, qw(id));
213 my $myconfig = \%main::myconfig;
214 my $form = $main::form;
216 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
218 my @ids = ref $params{id} eq 'ARRAY' ? @{ $params{id} } : ($params{id});
219 my $placeholders = join ', ', ('?') x scalar @ids;
220 my $query = qq|UPDATE sepa_export SET closed = TRUE WHERE id IN ($placeholders)|;
222 do_query($form, $dbh, $query, map { conv_i($_) } @ids);
224 $dbh->commit() unless ($params{dbh});
226 $main::lxdebug->leave_sub();
230 $main::lxdebug->enter_sub();
235 my $myconfig = \%main::myconfig;
236 my $form = $main::form;
237 my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor';
238 my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap';
240 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
243 'id' => [ 'se.id', ],
244 'export_date' => [ 'se.itime', ],
245 'employee' => [ 'e.name', 'se.id', ],
246 'executed' => [ 'se.executed', 'se.id', ],
247 'closed' => [ 'se.closed', 'se.id', ],
250 my %sort_spec = create_sort_spec('defs' => \%sort_columns, 'default' => 'id', 'column' => $params{sortorder}, 'dir' => $params{sortdir});
252 my (@where, @values, @where_sub, @values_sub, %joins_sub);
254 my $filter = $params{filter} || { };
256 foreach (qw(executed closed)) {
257 push @where, $filter->{$_} ? "se.$_" : "NOT se.$_" if (exists $filter->{$_});
260 my %operators = ('from' => '>=',
263 foreach my $dir (qw(from to)) {
264 next unless ($filter->{"export_date_${dir}"});
265 push @where, "se.itime $operators{$dir} ?::date";
266 push @values, $filter->{"export_date_${dir}"};
269 if ($filter->{invnumber}) {
270 push @where_sub, "arap.invnumber ILIKE ?";
271 push @values_sub, '%' . $filter->{invnumber} . '%';
272 $joins_sub{$arap} = 1;
276 push @where_sub, "vc.name ILIKE ?";
277 push @values_sub, '%' . $filter->{vc} . '%';
278 $joins_sub{$arap} = 1;
282 foreach my $type (qw(requested_execution execution)) {
283 foreach my $dir (qw(from to)) {
284 next unless ($filter->{"${type}_date_${dir}"});
285 push @where_sub, "(items.${type}_date IS NOT NULL) AND (items.${type}_date $operators{$dir} ?)";
286 push @values_sub, $filter->{"${type}_date_${_}"};
292 $joins_sub .= " LEFT JOIN ${arap} arap ON (items.${arap}_id = arap.id)" if ($joins_sub{$arap});
293 $joins_sub .= " LEFT JOIN ${vc} vc ON (arap.${vc}_id = vc.id)" if ($joins_sub{vc});
295 my $where_sub = join(' AND ', map { "(${_})" } @where_sub);
297 my $query_sub = qq|se.id IN (SELECT items.sepa_export_id
298 FROM sepa_export_items items
302 push @where, $query_sub;
303 push @values, @values_sub;
306 push @where, 'se.vc = ?';
309 my $where = @where ? ' WHERE ' . join(' AND ', map { "(${_})" } @where) : '';
312 qq|SELECT se.id, se.employee_id, se.executed, se.closed, itime::date AS export_date,
317 CASE WHEN COALESCE(emp.name, '') <> '' THEN emp.name ELSE emp.login END AS name
319 ) AS e ON (se.employee_id = e.id)
321 ORDER BY $sort_spec{sql}|;
323 my $results = selectall_hashref_query($form, $dbh, $query, @values);
325 $main::lxdebug->leave_sub();
331 $main::lxdebug->enter_sub();
336 Common::check_params(\%params, qw(items));
338 my $myconfig = \%main::myconfig;
339 my $form = $main::form;
340 my $vc = $params{vc} eq 'customer' ? 'customer' : 'vendor';
341 my $arap = $params{vc} eq 'customer' ? 'ar' : 'ap';
342 my $mult = $params{vc} eq 'customer' ? -1 : 1;
345 my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
347 my @items = ref $params{items} eq 'ARRAY' ? @{ $params{items} } : ($params{items});
350 'get_item' => [ qq|SELECT sei.*
351 FROM sepa_export_items sei
354 'get_arap' => [ qq|SELECT at.chart_id
356 LEFT JOIN chart c ON (at.chart_id = c.id)
358 AND ((c.link LIKE '%:${ARAP}') OR (c.link LIKE '${ARAP}:%') OR (c.link = '${ARAP}'))
361 'add_acc_trans' => [ qq|INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, gldate, source, memo)
362 VALUES (?, ?, ?, ?, current_date, ?, '')| ],
364 'update_arap' => [ qq|UPDATE ${arap}
368 'finish_item' => [ qq|UPDATE sepa_export_items
369 SET execution_date = ?, executed = TRUE
372 'has_unexecuted' => [ qq|SELECT sei1.id
373 FROM sepa_export_items sei1
374 WHERE (sei1.sepa_export_id = (SELECT sei2.sepa_export_id
375 FROM sepa_export_items sei2
377 AND NOT COALESCE(sei1.executed, FALSE)
380 'do_close' => [ qq|UPDATE sepa_export
381 SET executed = TRUE, closed = TRUE
385 map { unshift @{ $_ }, prepare_query($form, $dbh, $_->[0]) } values %handles;
387 foreach my $item (@items) {
388 my $item_id = conv_i($item->{id});
390 # Retrieve the item data belonging to the ID.
391 do_statement($form, @{ $handles{get_item} }, $item_id);
392 my $orig_item = $handles{get_item}->[0]->fetchrow_hashref();
394 next if (!$orig_item);
396 # Retrieve the invoice's AR/AP chart ID.
397 do_statement($form, @{ $handles{get_arap} }, $orig_item->{"${arap}_id"});
398 my ($arap_chart_id) = $handles{get_arap}->[0]->fetchrow_array();
400 # Record the payment in acc_trans offsetting AR/AP.
401 do_statement($form, @{ $handles{add_acc_trans} }, $orig_item->{"${arap}_id"}, $arap_chart_id, -1 * $mult * $orig_item->{amount}, $item->{execution_date}, '');
402 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});
404 # Update the invoice to reflect the new paid amount.
405 do_statement($form, @{ $handles{update_arap} }, $orig_item->{amount}, $orig_item->{"${arap}_id"});
407 # Update datepaid of invoice. set_datepaid (which has some extra logic)
408 # finds the date from acc_trans, where the payment has already been
409 # recorded above, so we don't need to explicitly pass
410 # $item->{execution_date}
411 IO->set_datepaid(table => "$arap", id => $orig_item->{"${arap}_id"}, dbh => $dbh);
413 # Update the item to reflect that it has been posted.
414 do_statement($form, @{ $handles{finish_item} }, $item->{execution_date}, $item_id);
416 # Check whether or not we can close the export itself if there are no unexecuted items left.
417 do_statement($form, @{ $handles{has_unexecuted} }, $item_id);
418 my ($has_unexecuted) = $handles{has_unexecuted}->[0]->fetchrow_array();
420 if (!$has_unexecuted) {
421 do_statement($form, @{ $handles{do_close} }, $orig_item->{sepa_export_id});
425 map { $_->[0]->finish() } values %handles;
427 $dbh->commit() unless ($params{dbh});
429 $main::lxdebug->leave_sub();