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_ref, $w_ref) = SL::DATEV::CSV->new(datev_lines => $datev1->generate_datev_lines,
166 locked => $datev1->locked,
168 # warnings should be undef -> no array elements at all
169 is(scalar @{ $w_ref }, 0);
171 # splice away the header, because sort won't do
172 # we need sort, because pay_invoice is not acc_trans_id order safe
173 my @data_csv = splice @{ $datev_ref }, 2, 5;
174 @data_csv = sort { $a->[0] cmp $b->[0] } @data_csv;
176 cmp_deeply($data_csv[1], [ 535, 'S', 'EUR', '', '', '', '1400', '8300', '', '0101', "\x{de} sales \x{a5}& i",
177 '', '', '', '', '', '', '', '', '', '', '',
178 '', '', '', '', '', '', '', '', '', '', '', '', '',
179 '', 'Kostenst', 'Crowd-Fu', '', '', '', '', '', '', '', '',
180 '', '', '', '', '', '', '', '', '', '', '', '', '',
181 '', '', '', '', '', '', '', '', '', '', '', '', '',
182 '', '', '', '', '', '', '', '', '', '', '', '', '',
183 '', '', '', '', '', '', '', '', '', '', '', '', '',
184 '', '', '', '', '', '', '', '', '', '', '', '', '',
188 cmp_deeply($data_csv[0], [ '249,9', 'S', 'EUR', '', '', '', '1400', '8400', '', '0101', "\x{de} sales \x{a5}& i",
189 '', '', '', '', '', '', '', '', '', '', '',
190 '', '', '', '', '', '', '', '', '', '', '', '', '',
191 '', 'Kostenst', 'Crowd-Fu', '', '', '', '', '', '', '', '',
192 '', '', '', '', '', '', '', '', '', '', '', '', '',
193 '', '', '', '', '', '', '', '', '', '', '', '', '',
194 '', '', '', '', '', '', '', '', '', '', '', '', '',
195 '', '', '', '', '', '', '', '', '', '', '', '', '',
196 '', '', '', '', '', '', '', '', '', '', '', '', '',
199 cmp_deeply($data_csv[2], [ '784,9', 'S', 'EUR', '', '', '', '1200', '1400', '', '0501', "\x{de} sales \x{a5}& i",
200 '', '', '', '', '', '', '', '', '', '', '',
201 '', '', '', '', '', '', '', '', '', '', '', '', '',
202 '', 'Kostenst', 'Crowd-Fu', '', '', '', '', '', '', '', '',
203 '', '', '', '', '', '', '', '', '', '', '', '', '',
204 '', '', '', '', '', '', '', '', '', '', '', '', '',
205 '', '', '', '', '', '', '', '', '', '', '', '', '',
206 '', '', '', '', '', '', '', '', '', '', '', '', '',
207 '', '', '', '', '', '', '', '', '', '', '', '', '',
210 my $march_9 = DateTime->new(year => 2017, month => 3, day => 9);
211 my $invoice2 = create_sales_invoice(
212 invnumber => "2 sales invoice",
213 customer => $customer,
216 intnotes => 'booked in March',
219 invoiceitems => [ create_invoice_item(part => $part1, qty => 6, sellprice => 70),
220 create_invoice_item(part => $part2, qty => 20, sellprice => 50),
224 my $credit_note = create_credit_note(
225 invnumber => 'Gutschrift 34',
226 customer => $customer,
229 intnotes => 'booked in February',
232 invoiceitems => [ create_invoice_item(part => $part1, qty => 3, sellprice => 70),
233 create_invoice_item(part => $part2, qty => 10, sellprice => 50),
237 my $datev = SL::DATEV->new(
242 $datev->generate_datev_data(from_to => $datev->fromto);
243 my $datev_lines = $datev->generate_datev_lines;
244 my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
245 cmp_ok($::form->round_amount($umsatzsumme,2), '==', 3924.5, "Sum of all bookings ok");
247 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
248 $datev_lines = $datev->generate_datev_lines;
250 note('testing purchase invoice');
251 my $purchase_invoice = new_purchase_invoice();
252 $datev1 = SL::DATEV->new(
253 dbh => $purchase_invoice->db->dbh,
254 trans_id => $purchase_invoice->id,
257 $datev1->generate_datev_data;
258 cmp_deeply $datev1->generate_datev_lines, [
260 'belegfeld1' => 'ap1',
261 'buchungstext' => 'Testvendor',
262 'datum' => '01.01.2017',
263 'gegenkonto' => '1600',
267 'soll_haben_kennzeichen' => 'H',
272 'belegfeld1' => 'ap1',
273 'buchungstext' => 'Testvendor',
274 'datum' => '01.01.2017',
275 'gegenkonto' => '1600',
279 'soll_haben_kennzeichen' => 'H',
283 ], "trans_id datev check purchase_invoice ok";
285 $datev1->generate_datev_data;
286 cmp_deeply $datev1->generate_datev_lines, [
288 'belegfeld1' => 'ap1',
289 'buchungstext' => 'Testvendor',
290 'datum' => '01.01.2017',
291 'gegenkonto' => $vendor->vendornumber,
295 'soll_haben_kennzeichen' => 'H',
300 'belegfeld1' => 'ap1',
301 'buchungstext' => 'Testvendor',
302 'datum' => '01.01.2017',
303 'gegenkonto' => $vendor->vendornumber,
307 'soll_haben_kennzeichen' => 'H',
311 ], "trans_id datev check purchase_invoice use_pk ok";
313 note('testing gldatefrom');
314 $datev = SL::DATEV->new(
317 to => DateTime->new(year => 2017, month => 01, day => 31),
320 $::form = Support::TestSetup->create_new_form;
321 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
323 $datev->generate_datev_data(from_to => $datev->fromto);
324 $datev_lines = $datev->generate_datev_lines;
325 $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
326 cmp_ok($umsatzsumme, '==', 1569.8, "Sum of bookings made after March 1st (only invoice2) ok");
328 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 5, day => 1)->to_kivitendo;
329 $datev->generate_datev_data(from_to => $datev->fromto);
330 cmp_deeply $datev->generate_datev_lines, [], "no bookings for January made after May 1st: ok";
335 sub new_purchase_invoice {
336 # manually create a Kreditorenbuchung from scratch, ap + acc_trans bookings, as no helper exists yet, like $invoice->post.
337 # arap-Booking must come last in the acc_trans order
338 # this function was essentially copied from t/db_helper/payment.t, refactor once $purchase_invoice->post exists
339 my $currency_id = $::instance_conf->get_currency_id;
340 my $employee = SL::DB::Manager::Employee->current || die "No employee";
341 my $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || die "No taxzone";
343 my $purchase_invoice = SL::DB::PurchaseInvoice->new(
345 currency_id => $currency_id,
346 employee_id => $employee->id,
355 taxzone_id => $taxzone->id,
358 vendor_id => $vendor->id,
361 my $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3400');
362 my $expense_chart_booking= SL::DB::AccTransaction->new(
364 chart_id => $expense_chart->id,
365 chart_link => $expense_chart->link,
369 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
372 trans_id => $purchase_invoice->id,
374 $expense_chart_booking->save;
376 my $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1576');
377 my $tax_chart_booking= SL::DB::AccTransaction->new(
379 chart_id => $tax_chart->id,
380 chart_link => $tax_chart->link,
384 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
387 trans_id => $purchase_invoice->id,
389 $tax_chart_booking->save;
390 $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3300');
391 $expense_chart_booking= SL::DB::AccTransaction->new(
393 chart_id => $expense_chart->id,
394 chart_link => $expense_chart->link,
398 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
401 trans_id => $purchase_invoice->id,
403 $expense_chart_booking->save;
405 $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1571');
406 $tax_chart_booking= SL::DB::AccTransaction->new(
407 trans_id => $purchase_invoice->id,
408 chart_id => $tax_chart->id,
409 chart_link => $tax_chart->link,
416 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
418 $tax_chart_booking->save;
419 my $arap_chart = SL::DB::Manager::Chart->find_by(accno => '1600');
420 my $arap_booking= SL::DB::AccTransaction->new(
421 trans_id => $purchase_invoice->id,
422 chart_id => $arap_chart->id,
423 chart_link => $arap_chart->link,
430 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
434 return $purchase_invoice;
438 SL::DB::Manager::AccTransaction->delete_all(all => 1);
439 SL::DB::Manager::InvoiceItem->delete_all( all => 1);
440 SL::DB::Manager::Invoice->delete_all( all => 1);
441 SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
442 SL::DB::Manager::Customer->delete_all( all => 1);
443 SL::DB::Manager::Part->delete_all( all => 1);
444 SL::DB::Manager::Project->delete_all( all => 1);
445 SL::DB::Manager::Department->delete_all( all => 1);
446 SL::DATEV->clean_temporary_directories;