3 use Test::Deep qw(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_bag \@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 cmp_bag $datev1->generate_datev_lines, [
113 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
114 'buchungstext' => 'Testcustomer',
115 'datum' => '01.01.2017',
116 'gegenkonto' => '8400',
117 'konto' => $customer->customernumber,
118 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
119 'kost2' => 'Crowd-Funding September 2017',
122 'soll_haben_kennzeichen' => 'S',
125 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
126 'buchungstext' => 'Testcustomer',
127 'datum' => '01.01.2017',
128 'gegenkonto' => '8300',
129 'konto' => $customer->customernumber,
130 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
131 'kost2' => 'Crowd-Funding September 2017',
134 'soll_haben_kennzeichen' => 'S',
137 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
138 'buchungstext' => 'Testcustomer',
139 'datum' => '05.01.2017',
140 'gegenkonto' => $customer->customernumber,
142 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
143 'kost2' => 'Crowd-Funding September 2017',
146 'soll_haben_kennzeichen' => 'S',
148 ], "trans_id datev check use_pk ok";
151 my $startdate = DateTime->new(year => 2017, month => 1, day => 1);
152 my $enddate = DateTime->new(year => 2017, month => 12, day => 31);
154 # check conversion to csv
155 $datev1->from($startdate);
156 $datev1->to($enddate);
157 # reset use_pk for csv_buchungsexport
159 $datev1->generate_datev_data;
162 my ($datev_ref, $w_ref) = SL::DATEV::CSV->new(datev_lines => $datev1->generate_datev_lines,
165 locked => $datev1->locked,
167 # warnings should be undef -> no array elements at all
168 is(scalar @{ $w_ref }, 0);
170 # splice away the header, because sort won't do
171 # we need sort, because pay_invoice is not acc_trans_id order safe
172 my @data_csv = splice @{ $datev_ref }, 2, 5;
173 @data_csv = sort { $a->[0] cmp $b->[0] } @data_csv;
175 cmp_bag($data_csv[1], [ 535, 'S', 'EUR', '', '', '', '1400', '8300', '', '0101', "\x{de} sales \x{a5}& i",
176 '', '', '', '', '', '', '', '', '', '', '',
177 '', '', '', '', '', '', '', '', '', '', '', '', '',
178 '', 'Crowd-Fu', 'Kostenst', '', '', '', '', '', '', '', '',
179 '', '', '', '', '', '', '', '', '', '', '', '', '',
180 '', '', '', '', '', '', '', '', '', '', '', '', '',
181 '', '', '', '', '', '', '', '', '', '', '', '', '',
182 '', '', '', '', '', '', '', '', '', '', '', '', '',
183 '', '', '', '', '', '', '', '', '', '', '', '', '',
187 cmp_bag($data_csv[0], [ '249,9', 'S', 'EUR', '', '', '', '1400', '8400', '', '0101', "\x{de} sales \x{a5}& i",
188 '', '', '', '', '', '', '', '', '', '', '',
189 '', '', '', '', '', '', '', '', '', '', '', '', '',
190 '', 'Crowd-Fu', 'Kostenst', '', '', '', '', '', '', '', '',
191 '', '', '', '', '', '', '', '', '', '', '', '', '',
192 '', '', '', '', '', '', '', '', '', '', '', '', '',
193 '', '', '', '', '', '', '', '', '', '', '', '', '',
194 '', '', '', '', '', '', '', '', '', '', '', '', '',
195 '', '', '', '', '', '', '', '', '', '', '', '', '',
198 cmp_bag($data_csv[2], [ '784,9', 'S', 'EUR', '', '', '', '1200', '1400', '', '0501', "\x{de} sales \x{a5}& i",
199 '', '', '', '', '', '', '', '', '', '', '',
200 '', '', '', '', '', '', '', '', '', '', '', '', '',
201 '', 'Crowd-Fu', 'Kostenst', '', '', '', '', '', '', '', '',
202 '', '', '', '', '', '', '', '', '', '', '', '', '',
203 '', '', '', '', '', '', '', '', '', '', '', '', '',
204 '', '', '', '', '', '', '', '', '', '', '', '', '',
205 '', '', '', '', '', '', '', '', '', '', '', '', '',
206 '', '', '', '', '', '', '', '', '', '', '', '', '',
209 my $march_9 = DateTime->new(year => 2017, month => 3, day => 9);
210 my $invoice2 = create_sales_invoice(
211 invnumber => "2 sales invoice",
212 customer => $customer,
215 intnotes => 'booked in March',
218 invoiceitems => [ create_invoice_item(part => $part1, qty => 6, sellprice => 70),
219 create_invoice_item(part => $part2, qty => 20, sellprice => 50),
223 my $credit_note = create_credit_note(
224 invnumber => 'Gutschrift 34',
225 customer => $customer,
228 intnotes => 'booked in February',
231 invoiceitems => [ create_invoice_item(part => $part1, qty => 3, sellprice => 70),
232 create_invoice_item(part => $part2, qty => 10, sellprice => 50),
236 my $datev = SL::DATEV->new(
241 $datev->generate_datev_data(from_to => $datev->fromto);
242 my $datev_lines = $datev->generate_datev_lines;
243 my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
244 cmp_ok($::form->round_amount($umsatzsumme,2), '==', 3924.5, "Sum of all bookings ok");
246 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
247 $datev_lines = $datev->generate_datev_lines;
249 note('testing purchase invoice');
250 my $purchase_invoice = new_purchase_invoice();
251 $datev1 = SL::DATEV->new(
252 dbh => $purchase_invoice->db->dbh,
253 trans_id => $purchase_invoice->id,
256 $datev1->generate_datev_data;
257 cmp_bag $datev1->generate_datev_lines, [
259 'belegfeld1' => 'ap1',
260 'buchungstext' => 'Testvendor',
261 'datum' => '01.01.2017',
262 'gegenkonto' => '1600',
266 'soll_haben_kennzeichen' => 'H',
271 'belegfeld1' => 'ap1',
272 'buchungstext' => 'Testvendor',
273 'datum' => '01.01.2017',
274 'gegenkonto' => '1600',
278 'soll_haben_kennzeichen' => 'H',
282 ], "trans_id datev check purchase_invoice ok";
284 $datev1->generate_datev_data;
285 cmp_bag $datev1->generate_datev_lines, [
287 'belegfeld1' => 'ap1',
288 'buchungstext' => 'Testvendor',
289 'datum' => '01.01.2017',
290 'gegenkonto' => $vendor->vendornumber,
294 'soll_haben_kennzeichen' => 'H',
299 'belegfeld1' => 'ap1',
300 'buchungstext' => 'Testvendor',
301 'datum' => '01.01.2017',
302 'gegenkonto' => $vendor->vendornumber,
306 'soll_haben_kennzeichen' => 'H',
310 ], "trans_id datev check purchase_invoice use_pk ok";
312 note('testing gldatefrom');
313 $datev = SL::DATEV->new(
316 to => DateTime->new(year => 2017, month => 01, day => 31),
319 $::form = Support::TestSetup->create_new_form;
320 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
322 $datev->generate_datev_data(from_to => $datev->fromto);
323 $datev_lines = $datev->generate_datev_lines;
324 $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
325 cmp_ok($umsatzsumme, '==', 1569.8, "Sum of bookings made after March 1st (only invoice2) ok");
327 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 5, day => 1)->to_kivitendo;
328 $datev->generate_datev_data(from_to => $datev->fromto);
329 cmp_bag $datev->generate_datev_lines, [], "no bookings for January made after May 1st: ok";
334 sub new_purchase_invoice {
335 # manually create a Kreditorenbuchung from scratch, ap + acc_trans bookings, as no helper exists yet, like $invoice->post.
336 # arap-Booking must come last in the acc_trans order
337 # this function was essentially copied from t/db_helper/payment.t, refactor once $purchase_invoice->post exists
338 my $currency_id = $::instance_conf->get_currency_id;
339 my $employee = SL::DB::Manager::Employee->current || die "No employee";
340 my $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || die "No taxzone";
342 my $purchase_invoice = SL::DB::PurchaseInvoice->new(
344 currency_id => $currency_id,
345 employee_id => $employee->id,
354 taxzone_id => $taxzone->id,
357 vendor_id => $vendor->id,
360 my $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3400');
361 my $expense_chart_booking= SL::DB::AccTransaction->new(
363 chart_id => $expense_chart->id,
364 chart_link => $expense_chart->link,
368 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
371 trans_id => $purchase_invoice->id,
373 $expense_chart_booking->save;
375 my $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1576');
376 my $tax_chart_booking= SL::DB::AccTransaction->new(
378 chart_id => $tax_chart->id,
379 chart_link => $tax_chart->link,
383 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
386 trans_id => $purchase_invoice->id,
388 $tax_chart_booking->save;
389 $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3300');
390 $expense_chart_booking= SL::DB::AccTransaction->new(
392 chart_id => $expense_chart->id,
393 chart_link => $expense_chart->link,
397 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
400 trans_id => $purchase_invoice->id,
402 $expense_chart_booking->save;
404 $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1571');
405 $tax_chart_booking= SL::DB::AccTransaction->new(
406 trans_id => $purchase_invoice->id,
407 chart_id => $tax_chart->id,
408 chart_link => $tax_chart->link,
415 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
417 $tax_chart_booking->save;
418 my $arap_chart = SL::DB::Manager::Chart->find_by(accno => '1600');
419 my $arap_booking= SL::DB::AccTransaction->new(
420 trans_id => $purchase_invoice->id,
421 chart_id => $arap_chart->id,
422 chart_link => $arap_chart->link,
429 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
433 return $purchase_invoice;
437 SL::DB::Manager::AccTransaction->delete_all(all => 1);
438 SL::DB::Manager::InvoiceItem->delete_all( all => 1);
439 SL::DB::Manager::Invoice->delete_all( all => 1);
440 SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
441 SL::DB::Manager::Customer->delete_all( all => 1);
442 SL::DB::Manager::Part->delete_all( all => 1);
443 SL::DB::Manager::Project->delete_all( all => 1);
444 SL::DB::Manager::Department->delete_all( all => 1);
445 SL::DATEV->clean_temporary_directories;