3 use Test::Deep qw(cmp_deeply cmp_bag);
8 use_ok 'Support::TestSetup';
9 use SL::DATEV qw(:CONSTANTS);
10 use SL::Dev::ALL qw(:ALL);
11 use List::Util qw(sum);
12 use SL::DB::Buchungsgruppe;
16 Support::TestSetup::login();
20 my $dbh = SL::DB->client->dbh;
22 my $buchungsgruppe7 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 7%') || die "No accounting group for 7\%";
23 my $bank = SL::DB::Manager::Chart->find_by(description => 'Bank') || die 'Can\'t find chart "Bank"';
24 my $date = DateTime->new(year => 2017, month => 1, day => 1);
25 my $payment_date = DateTime->new(year => 2017, month => 1, day => 5);
26 my $gldate = DateTime->new(year => 2017, month => 2, day => 9); # simulate bookings for Jan being made in Feb
27 my $department = create_department(description => 'Kostenstelle DATEV-Schnittstelle 2018');
28 my $project = create_project(projectnumber => 2017, description => 'Crowd-Funding September 2017');
29 my $customer = new_customer(customernumber => '10001', name => 'Testcustomer')->save;
30 my $vendor = new_vendor(vendornumber => '70001', name => 'Testvendor')->save;
32 my $part1 = new_part(partnumber => '19', description => 'Part 19%')->save;
35 description => 'Part 7%',
36 buchungsgruppen_id => $buchungsgruppe7->id,
39 my $invoice = create_sales_invoice(
40 invnumber => "Þ sales ¥& invöice",
41 customer => $customer,
44 intnotes => 'booked in February',
47 invoiceitems => [ create_invoice_item(part => $part1, qty => 3, sellprice => 70),
48 create_invoice_item(part => $part2, qty => 10, sellprice => 50),
50 department_id => $department->id,
51 globalproject_id => $project->id,
53 $invoice->pay_invoice(chart_id => $bank->id,
54 amount => $invoice->open_amount,
55 transdate => $payment_date->to_kivitendo,
59 my $datev1 = SL::DATEV->new(
60 dbh => $invoice->db->dbh,
61 trans_id => $invoice->id,
64 $datev1->generate_datev_data;
66 my @data_datev = sort { $a->{umsatz} <=> $b->{umsatz} } @{ $datev1->generate_datev_lines() };
67 cmp_deeply \@data_datev, [
69 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
70 'buchungstext' => 'Testcustomer',
71 'datum' => '01.01.2017',
72 'gegenkonto' => '8400',
74 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
75 'kost2' => 'Crowd-Funding September 2017',
78 'soll_haben_kennzeichen' => 'S',
81 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
82 'buchungstext' => 'Testcustomer',
83 'datum' => '01.01.2017',
84 'gegenkonto' => '8300',
86 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
87 'kost2' => 'Crowd-Funding September 2017',
90 'soll_haben_kennzeichen' => 'S',
93 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
96 'buchungstext' => 'Testcustomer',
97 'buchungstext' => 'Testcustomer',
98 'datum' => '05.01.2017',
99 'gegenkonto' => '1400',
101 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
102 'kost2' => 'Crowd-Funding September 2017',
105 'soll_haben_kennzeichen' => 'S',
107 ], "trans_id datev check ok";
110 $datev1->generate_datev_data;
111 # TODO for cmp_deeply we need to sort the incoming data structure (see below)
112 cmp_bag $datev1->generate_datev_lines, [
114 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
115 'buchungstext' => 'Testcustomer',
116 'datum' => '01.01.2017',
117 'gegenkonto' => '8400',
118 'konto' => $customer->customernumber,
119 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
120 'kost2' => 'Crowd-Funding September 2017',
123 'soll_haben_kennzeichen' => 'S',
126 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
127 'buchungstext' => 'Testcustomer',
128 'datum' => '01.01.2017',
129 'gegenkonto' => '8300',
130 'konto' => $customer->customernumber,
131 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
132 'kost2' => 'Crowd-Funding September 2017',
135 'soll_haben_kennzeichen' => 'S',
138 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
139 'buchungstext' => 'Testcustomer',
140 'datum' => '05.01.2017',
141 'gegenkonto' => $customer->customernumber,
143 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
144 'kost2' => 'Crowd-Funding September 2017',
147 'soll_haben_kennzeichen' => 'S',
149 ], "trans_id datev check use_pk ok";
152 my $startdate = DateTime->new(year => 2017, month => 1, day => 1);
153 my $enddate = DateTime->new(year => 2017, month => 12, day => 31);
155 # check conversion to csv
156 $datev1->from($startdate);
157 $datev1->to($enddate);
158 # reset use_pk for csv_buchungsexport
160 $datev1->generate_datev_data;
163 my $datev_csv = SL::DATEV::CSV->new(datev_lines => $datev1->generate_datev_lines,
166 locked => $datev1->locked,
171 # we need sort, because pay_invoice is not acc_trans_id order safe
172 my @data_csv = sort { $a->[0] cmp $b->[0] } @{ $datev_csv->lines };
173 # warnings should be undef -> no array elements at all
174 is(scalar @{ $datev_csv->warnings }, 0);
177 cmp_deeply($data_csv[1], [ 535, 'S', 'EUR', '', '', '', '1400', '8300', '', '0101', "\x{de} sales \x{a5}& i",
178 '', '', '', '', '', '', '', '', '', '', '',
179 '', '', '', '', '', '', '', '', '', '', '', '', '',
180 '', 'Kostenst', 'Crowd-Fu', '', '', '', '', '', '', '', '',
181 '', '', '', '', '', '', '', '', '', '', '', '', '',
182 '', '', '', '', '', '', '', '', '', '', '', '', '',
183 '', '', '', '', '', '', '', '', '', '', '', '', '',
184 '', '', '', '', '', '', '', '', '', '', '', '', '',
185 '', '', '', '', '', '', '', '', '', '', '', '', '',
189 cmp_deeply($data_csv[0], [ '249,9', 'S', 'EUR', '', '', '', '1400', '8400', '', '0101', "\x{de} sales \x{a5}& i",
190 '', '', '', '', '', '', '', '', '', '', '',
191 '', '', '', '', '', '', '', '', '', '', '', '', '',
192 '', 'Kostenst', 'Crowd-Fu', '', '', '', '', '', '', '', '',
193 '', '', '', '', '', '', '', '', '', '', '', '', '',
194 '', '', '', '', '', '', '', '', '', '', '', '', '',
195 '', '', '', '', '', '', '', '', '', '', '', '', '',
196 '', '', '', '', '', '', '', '', '', '', '', '', '',
197 '', '', '', '', '', '', '', '', '', '', '', '', '',
200 cmp_deeply($data_csv[2], [ '784,9', 'S', 'EUR', '', '', '', '1200', '1400', '', '0501', "\x{de} sales \x{a5}& i",
201 '', '', '', '', '', '', '', '', '', '', '',
202 '', '', '', '', '', '', '', '', '', '', '', '', '',
203 '', 'Kostenst', 'Crowd-Fu', '', '', '', '', '', '', '', '',
204 '', '', '', '', '', '', '', '', '', '', '', '', '',
205 '', '', '', '', '', '', '', '', '', '', '', '', '',
206 '', '', '', '', '', '', '', '', '', '', '', '', '',
207 '', '', '', '', '', '', '', '', '', '', '', '', '',
208 '', '', '', '', '', '', '', '', '', '', '', '', '',
211 my $march_9 = DateTime->new(year => 2017, month => 3, day => 9);
212 my $invoice2 = create_sales_invoice(
213 invnumber => "2 sales invoice",
214 customer => $customer,
217 intnotes => 'booked in March',
220 invoiceitems => [ create_invoice_item(part => $part1, qty => 6, sellprice => 70),
221 create_invoice_item(part => $part2, qty => 20, sellprice => 50),
225 my $credit_note = create_credit_note(
226 invnumber => 'Gutschrift 34',
227 customer => $customer,
230 intnotes => 'booked in February',
233 invoiceitems => [ create_invoice_item(part => $part1, qty => 3, sellprice => 70),
234 create_invoice_item(part => $part2, qty => 10, sellprice => 50),
238 my $datev = SL::DATEV->new(
243 $datev->generate_datev_data(from_to => $datev->fromto);
244 my $datev_lines = $datev->generate_datev_lines;
245 my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
246 cmp_ok($::form->round_amount($umsatzsumme,2), '==', 3924.5, "Sum of all bookings ok");
248 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
249 $datev_lines = $datev->generate_datev_lines;
251 note('testing purchase invoice');
252 my $purchase_invoice = new_purchase_invoice();
253 $datev1 = SL::DATEV->new(
254 dbh => $purchase_invoice->db->dbh,
255 trans_id => $purchase_invoice->id,
258 $datev1->generate_datev_data;
259 cmp_deeply $datev1->generate_datev_lines, [
261 'belegfeld1' => 'ap1',
262 'buchungstext' => 'Testvendor',
263 'datum' => '01.01.2017',
264 'gegenkonto' => '1600',
268 'soll_haben_kennzeichen' => 'H',
273 'belegfeld1' => 'ap1',
274 'buchungstext' => 'Testvendor',
275 'datum' => '01.01.2017',
276 'gegenkonto' => '1600',
280 'soll_haben_kennzeichen' => 'H',
284 ], "trans_id datev check purchase_invoice ok";
286 $datev1->generate_datev_data;
287 cmp_deeply $datev1->generate_datev_lines, [
289 'belegfeld1' => 'ap1',
290 'buchungstext' => 'Testvendor',
291 'datum' => '01.01.2017',
292 'gegenkonto' => $vendor->vendornumber,
296 'soll_haben_kennzeichen' => 'H',
301 'belegfeld1' => 'ap1',
302 'buchungstext' => 'Testvendor',
303 'datum' => '01.01.2017',
304 'gegenkonto' => $vendor->vendornumber,
308 'soll_haben_kennzeichen' => 'H',
312 ], "trans_id datev check purchase_invoice use_pk ok";
314 note('testing gldatefrom');
315 $datev = SL::DATEV->new(
318 to => DateTime->new(year => 2017, month => 01, day => 31),
321 $::form = Support::TestSetup->create_new_form;
322 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
324 $datev->generate_datev_data(from_to => $datev->fromto);
325 $datev_lines = $datev->generate_datev_lines;
326 $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
327 cmp_ok($umsatzsumme, '==', 1569.8, "Sum of bookings made after March 1st (only invoice2) ok");
329 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 5, day => 1)->to_kivitendo;
330 $datev->generate_datev_data(from_to => $datev->fromto);
331 cmp_deeply $datev->generate_datev_lines, [], "no bookings for January made after May 1st: ok";
336 sub new_purchase_invoice {
337 # manually create a Kreditorenbuchung from scratch, ap + acc_trans bookings, as no helper exists yet, like $invoice->post.
338 # arap-Booking must come last in the acc_trans order
339 # this function was essentially copied from t/db_helper/payment.t, refactor once $purchase_invoice->post exists
340 my $currency_id = $::instance_conf->get_currency_id;
341 my $employee = SL::DB::Manager::Employee->current || die "No employee";
342 my $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || die "No taxzone";
344 my $purchase_invoice = SL::DB::PurchaseInvoice->new(
346 currency_id => $currency_id,
347 employee_id => $employee->id,
356 taxzone_id => $taxzone->id,
359 vendor_id => $vendor->id,
362 my $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3400');
363 my $expense_chart_booking= SL::DB::AccTransaction->new(
365 chart_id => $expense_chart->id,
366 chart_link => $expense_chart->link,
370 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
373 trans_id => $purchase_invoice->id,
375 $expense_chart_booking->save;
377 my $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1576');
378 my $tax_chart_booking= SL::DB::AccTransaction->new(
380 chart_id => $tax_chart->id,
381 chart_link => $tax_chart->link,
385 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
388 trans_id => $purchase_invoice->id,
390 $tax_chart_booking->save;
391 $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3300');
392 $expense_chart_booking= SL::DB::AccTransaction->new(
394 chart_id => $expense_chart->id,
395 chart_link => $expense_chart->link,
399 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
402 trans_id => $purchase_invoice->id,
404 $expense_chart_booking->save;
406 $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1571');
407 $tax_chart_booking= SL::DB::AccTransaction->new(
408 trans_id => $purchase_invoice->id,
409 chart_id => $tax_chart->id,
410 chart_link => $tax_chart->link,
417 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
419 $tax_chart_booking->save;
420 my $arap_chart = SL::DB::Manager::Chart->find_by(accno => '1600');
421 my $arap_booking= SL::DB::AccTransaction->new(
422 trans_id => $purchase_invoice->id,
423 chart_id => $arap_chart->id,
424 chart_link => $arap_chart->link,
431 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
435 return $purchase_invoice;
439 SL::DB::Manager::AccTransaction->delete_all(all => 1);
440 SL::DB::Manager::InvoiceItem->delete_all( all => 1);
441 SL::DB::Manager::Invoice->delete_all( all => 1);
442 SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
443 SL::DB::Manager::Customer->delete_all( all => 1);
444 SL::DB::Manager::Part->delete_all( all => 1);
445 SL::DB::Manager::Project->delete_all( all => 1);
446 SL::DB::Manager::Department->delete_all( all => 1);
447 SL::DATEV->clean_temporary_directories;