| 56 |
56 |
my $self = shift;
|
| 57 |
57 |
my %params = @_;
|
| 58 |
58 |
|
| 59 |
|
Common::check_params(\%params, qw(employee bank_transfers));
|
|
59 |
Common::check_params(\%params, qw(employee bank_transfers vc));
|
| 60 |
60 |
|
| 61 |
61 |
my $myconfig = \%main::myconfig;
|
| 62 |
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;
|
| 63 |
66 |
|
| 64 |
67 |
my $dbh = $params{dbh} || $form->get_standard_dbh($myconfig);
|
| 65 |
68 |
|
| 66 |
69 |
my ($export_id) = selectfirst_array_query($form, $dbh, qq|SELECT nextval('sepa_export_id_seq')|);
|
| 67 |
70 |
my $query =
|
| 68 |
|
qq|INSERT INTO sepa_export (id, employee_id)
|
|
71 |
qq|INSERT INTO sepa_export (id, employee_id, vc)
|
| 69 |
72 |
VALUES (?, (SELECT id
|
| 70 |
73 |
FROM employee
|
| 71 |
|
WHERE login = ?))|;
|
| 72 |
|
do_query($form, $dbh, $query, $export_id, $params{employee});
|
|
74 |
WHERE login = ?), ?)|;
|
|
75 |
do_query($form, $dbh, $query, $export_id, $params{employee}, $vc);
|
| 73 |
76 |
|
| 74 |
77 |
my $q_item_id = qq|SELECT nextval('id')|;
|
| 75 |
78 |
my $h_item_id = prepare_query($form, $dbh, $q_item_id);
|
| 76 |
79 |
|
| 77 |
80 |
my $q_insert =
|
| 78 |
|
qq|INSERT INTO sepa_export_items (id, sepa_export_id, ap_id, chart_id,
|
|
81 |
qq|INSERT INTO sepa_export_items (id, sepa_export_id, ${arap}_id, chart_id,
|
| 79 |
82 |
amount, requested_execution_date, reference, end_to_end_id,
|
| 80 |
|
our_iban, our_bic, vendor_iban, vendor_bic)
|
|
83 |
our_iban, our_bic, vc_iban, vc_bic)
|
| 81 |
84 |
VALUES (?, ?, ?, ?,
|
| 82 |
85 |
?, ?, ?, ?,
|
| 83 |
86 |
?, ?, ?, ?)|;
|
| 84 |
87 |
my $h_insert = prepare_query($form, $dbh, $q_insert);
|
| 85 |
88 |
|
| 86 |
89 |
my $q_reference =
|
| 87 |
|
qq|SELECT ap.invnumber,
|
|
90 |
qq|SELECT arap.invnumber,
|
| 88 |
91 |
(SELECT COUNT(at.*)
|
| 89 |
92 |
FROM acc_trans at
|
| 90 |
93 |
LEFT JOIN chart c ON (at.chart_id = c.id)
|
| 91 |
94 |
WHERE (at.trans_id = ?)
|
| 92 |
|
AND (c.link LIKE '%AP_paid%'))
|
|
95 |
AND (c.link LIKE '%${ARAP}_paid%'))
|
| 93 |
96 |
+
|
| 94 |
97 |
(SELECT COUNT(sei.*)
|
| 95 |
98 |
FROM sepa_export_items sei
|
| 96 |
99 |
WHERE (sei.ap_id = ?))
|
| 97 |
100 |
AS num_payments
|
| 98 |
|
FROM ap
|
|
101 |
FROM ${arap} arap
|
| 99 |
102 |
WHERE id = ?|;
|
| 100 |
103 |
my $h_reference = prepare_query($form, $dbh, $q_reference);
|
| 101 |
104 |
|
| ... | ... | |
| 103 |
106 |
|
| 104 |
107 |
foreach my $transfer (@{ $params{bank_transfers} }) {
|
| 105 |
108 |
if (!$transfer->{reference}) {
|
| 106 |
|
do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{ap_id})) x 3);
|
|
109 |
do_statement($form, $h_reference, $q_reference, (conv_i($transfer->{"${arap}_id"})) x 3);
|
| 107 |
110 |
|
| 108 |
111 |
my ($invnumber, $num_payments) = $h_reference->fetchrow_array();
|
| 109 |
112 |
$num_payments++;
|
| ... | ... | |
| 121 |
124 |
$end_to_end_id .= $item_id;
|
| 122 |
125 |
$end_to_end_id = substr $end_to_end_id, 0, 35;
|
| 123 |
126 |
|
| 124 |
|
my @values = ($item_id, $export_id,
|
| 125 |
|
conv_i($transfer->{ap_id}), conv_i($transfer->{chart_id}),
|
| 126 |
|
$transfer->{amount}, conv_date($transfer->{requested_execution_date}),
|
| 127 |
|
$transfer->{reference}, $end_to_end_id,
|
| 128 |
|
map { my $pfx = $_; map { $transfer->{"${pfx}_${_}"} } qw(iban bic) } qw(our vendor));
|
|
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));
|
| 129 |
132 |
|
| 130 |
133 |
do_statement($form, $h_insert, $q_insert, @values);
|
| 131 |
134 |
}
|
Unterscheidung Bankeinzug/Überweisung beim Erstellen der SEPA-Einträge