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",
94 'buchungstext' => 'Testcustomer',
95 'buchungstext' => 'Testcustomer',
96 'datum' => '05.01.2017',
97 'gegenkonto' => '1400',
99 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
100 'kost2' => 'Crowd-Funding September 2017',
103 'soll_haben_kennzeichen' => 'S',
105 ], "trans_id datev check ok";
108 $datev1->generate_datev_data;
109 cmp_bag $datev1->generate_datev_lines, [
111 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
112 'buchungstext' => 'Testcustomer',
113 'datum' => '01.01.2017',
114 'gegenkonto' => '8400',
115 'konto' => $customer->customernumber,
116 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
117 'kost2' => 'Crowd-Funding September 2017',
120 'soll_haben_kennzeichen' => 'S',
123 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
124 'buchungstext' => 'Testcustomer',
125 'datum' => '01.01.2017',
126 'gegenkonto' => '8300',
127 'konto' => $customer->customernumber,
128 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
129 'kost2' => 'Crowd-Funding September 2017',
132 'soll_haben_kennzeichen' => 'S',
135 'belegfeld1' => "\x{de} sales \x{a5}& inv\x{f6}ice",
136 'buchungstext' => 'Testcustomer',
137 'datum' => '05.01.2017',
138 'gegenkonto' => $customer->customernumber,
140 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
141 'kost2' => 'Crowd-Funding September 2017',
144 'soll_haben_kennzeichen' => 'S',
146 ], "trans_id datev check use_pk ok";
149 my $startdate = DateTime->new(year => 2017, month => 1, day => 1);
150 my $enddate = DateTime->new(year => 2017, month => 12, day => 31);
152 # check conversion to csv
153 $datev1->from($startdate);
154 $datev1->to($enddate);
155 $datev1->use_pk(0); # reset use_pk for csv_buchungsexport
157 # splice away the header, because sort won't do
158 # we need sort, because pay_invoice is not acc_trans_id order safe
159 my @data_csv = splice @{ $datev1->csv_buchungsexport() }, 2, 5;
160 @data_csv = sort { $a->[0] cmp $b->[0] } @data_csv;
162 my $cp1252_belegfeld1 = SL::Iconv::convert("UTF-8", "CP1252", 'Þ sales ¥& i');
163 my $cp1252_buchungstext = SL::Iconv::convert("UTF-8", "CP1252", 'Þ sales ¥& invöice');
165 cmp_bag($data_csv[1], [ 535, 'S', 'EUR', undef, undef, undef, '1400', '8300', undef, '0101', $cp1252_belegfeld1,
166 undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
167 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
168 undef, 'Crowd-Fu', 'Kostenst', undef, undef, undef, undef, undef, undef, undef, undef,
169 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
170 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
171 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
172 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
173 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
174 undef, undef, undef, undef, undef ]
177 cmp_bag($data_csv[0], [ '249,9', 'S', 'EUR', undef, undef, undef, '1400', '8400', undef, '0101', $cp1252_belegfeld1,
178 undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
179 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
180 undef, 'Crowd-Fu', 'Kostenst', undef, undef, undef, undef, undef, undef, undef, undef,
181 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
182 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
183 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
184 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
185 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
186 undef, undef, undef, undef, undef ]
188 cmp_bag($data_csv[2], [ '784,9', 'S', 'EUR', undef, undef, undef, '1200', '1400', undef, '0501', $cp1252_belegfeld1,
189 undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
190 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
191 undef, 'Crowd-Fu', 'Kostenst', undef, undef, undef, undef, undef, undef, undef, undef,
192 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
193 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
194 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
195 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
196 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
197 undef, undef, undef, undef, undef ]
199 my $march_9 = DateTime->new(year => 2017, month => 3, day => 9);
200 my $invoice2 = create_sales_invoice(
201 invnumber => "2 sales invoice",
202 customer => $customer,
205 intnotes => 'booked in March',
208 invoiceitems => [ create_invoice_item(part => $part1, qty => 6, sellprice => 70),
209 create_invoice_item(part => $part2, qty => 20, sellprice => 50),
213 my $credit_note = create_credit_note(
214 invnumber => 'Gutschrift 34',
215 customer => $customer,
218 intnotes => 'booked in February',
221 invoiceitems => [ create_invoice_item(part => $part1, qty => 3, sellprice => 70),
222 create_invoice_item(part => $part2, qty => 10, sellprice => 50),
226 my $datev = SL::DATEV->new(
231 $datev->generate_datev_data(from_to => $datev->fromto);
232 my $datev_lines = $datev->generate_datev_lines;
233 my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
234 cmp_ok($::form->round_amount($umsatzsumme,2), '==', 3924.5, "Sum of all bookings ok");
236 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
237 $datev_lines = $datev->generate_datev_lines;
239 note('testing purchase invoice');
240 my $purchase_invoice = new_purchase_invoice();
241 $datev1 = SL::DATEV->new(
242 dbh => $purchase_invoice->db->dbh,
243 trans_id => $purchase_invoice->id,
246 $datev1->generate_datev_data;
247 cmp_bag $datev1->generate_datev_lines, [
249 'belegfeld1' => 'ap1',
250 'buchungstext' => 'Testvendor',
251 'datum' => '01.01.2017',
252 'gegenkonto' => '1600',
256 'soll_haben_kennzeichen' => 'H',
261 'belegfeld1' => 'ap1',
262 'buchungstext' => 'Testvendor',
263 'datum' => '01.01.2017',
264 'gegenkonto' => '1600',
268 'soll_haben_kennzeichen' => 'H',
272 ], "trans_id datev check purchase_invoice ok";
274 $datev1->generate_datev_data;
275 cmp_bag $datev1->generate_datev_lines, [
277 'belegfeld1' => 'ap1',
278 'buchungstext' => 'Testvendor',
279 'datum' => '01.01.2017',
280 'gegenkonto' => $vendor->vendornumber,
284 'soll_haben_kennzeichen' => 'H',
289 'belegfeld1' => 'ap1',
290 'buchungstext' => 'Testvendor',
291 'datum' => '01.01.2017',
292 'gegenkonto' => $vendor->vendornumber,
296 'soll_haben_kennzeichen' => 'H',
300 ], "trans_id datev check purchase_invoice use_pk ok";
302 note('testing gldatefrom');
303 $datev = SL::DATEV->new(
306 to => DateTime->new(year => 2017, month => 01, day => 31),
309 $::form = Support::TestSetup->create_new_form;
310 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
312 $datev->generate_datev_data(from_to => $datev->fromto);
313 $datev_lines = $datev->generate_datev_lines;
314 $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
315 cmp_ok($umsatzsumme, '==', 1569.8, "Sum of bookings made after March 1st (only invoice2) ok");
317 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 5, day => 1)->to_kivitendo;
318 $datev->generate_datev_data(from_to => $datev->fromto);
319 cmp_bag $datev->generate_datev_lines, [], "no bookings for January made after May 1st: ok";
324 sub new_purchase_invoice {
325 # manually create a Kreditorenbuchung from scratch, ap + acc_trans bookings, as no helper exists yet, like $invoice->post.
326 # arap-Booking must come last in the acc_trans order
327 # this function was essentially copied from t/db_helper/payment.t, refactor once $purchase_invoice->post exists
328 my $currency_id = $::instance_conf->get_currency_id;
329 my $employee = SL::DB::Manager::Employee->current || die "No employee";
330 my $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || die "No taxzone";
332 my $purchase_invoice = SL::DB::PurchaseInvoice->new(
334 currency_id => $currency_id,
335 employee_id => $employee->id,
344 taxzone_id => $taxzone->id,
347 vendor_id => $vendor->id,
350 my $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3400');
351 my $expense_chart_booking= SL::DB::AccTransaction->new(
353 chart_id => $expense_chart->id,
354 chart_link => $expense_chart->link,
358 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
361 trans_id => $purchase_invoice->id,
363 $expense_chart_booking->save;
365 my $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1576');
366 my $tax_chart_booking= SL::DB::AccTransaction->new(
368 chart_id => $tax_chart->id,
369 chart_link => $tax_chart->link,
373 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
376 trans_id => $purchase_invoice->id,
378 $tax_chart_booking->save;
379 $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3300');
380 $expense_chart_booking= SL::DB::AccTransaction->new(
382 chart_id => $expense_chart->id,
383 chart_link => $expense_chart->link,
387 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
390 trans_id => $purchase_invoice->id,
392 $expense_chart_booking->save;
394 $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1571');
395 $tax_chart_booking= SL::DB::AccTransaction->new(
396 trans_id => $purchase_invoice->id,
397 chart_id => $tax_chart->id,
398 chart_link => $tax_chart->link,
405 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
407 $tax_chart_booking->save;
408 my $arap_chart = SL::DB::Manager::Chart->find_by(accno => '1600');
409 my $arap_booking= SL::DB::AccTransaction->new(
410 trans_id => $purchase_invoice->id,
411 chart_id => $arap_chart->id,
412 chart_link => $arap_chart->link,
419 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
423 return $purchase_invoice;
427 SL::DB::Manager::AccTransaction->delete_all(all => 1);
428 SL::DB::Manager::InvoiceItem->delete_all( all => 1);
429 SL::DB::Manager::Invoice->delete_all( all => 1);
430 SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
431 SL::DB::Manager::Customer->delete_all( all => 1);
432 SL::DB::Manager::Part->delete_all( all => 1);
433 SL::DB::Manager::Project->delete_all( all => 1);
434 SL::DB::Manager::Department->delete_all( all => 1);
435 SL::DATEV->clean_temporary_directories;