3 use Test::Deep qw(cmp_bag);
7 use_ok 'Support::TestSetup';
8 use SL::DATEV qw(:CONSTANTS);
9 use SL::Dev::ALL qw(:ALL);
10 use List::Util qw(sum);
11 use SL::DB::Buchungsgruppe;
15 Support::TestSetup::login();
19 my $dbh = SL::DB->client->dbh;
21 my $buchungsgruppe7 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 7%') || die "No accounting group for 7\%";
22 my $bank = SL::DB::Manager::Chart->find_by(description => 'Bank') || die 'Can\'t find chart "Bank"';
23 my $date = DateTime->new(year => 2017, month => 1, day => 1);
24 my $payment_date = DateTime->new(year => 2017, month => 1, day => 5);
25 my $gldate = DateTime->new(year => 2017, month => 2, day => 9); # simulate bookings for Jan being made in Feb
26 my $department = create_department(description => 'Kostenstelle DATEV-Schnittstelle 2018');
27 my $project = create_project(projectnumber => 2017, description => 'Crowd-Funding September 2017');
28 my $customer = new_customer(customernumber => '10001', name => 'Testcustomer')->save;
29 my $vendor = new_vendor(vendornumber => '70001', name => 'Testvendor')->save;
31 my $part1 = new_part(partnumber => '19', description => 'Part 19%')->save;
34 description => 'Part 7%',
35 buchungsgruppen_id => $buchungsgruppe7->id,
38 my $invoice = create_sales_invoice(
39 invnumber => "Þ sales ¥& invöice",
40 customer => $customer,
43 intnotes => 'booked in February',
46 invoiceitems => [ create_invoice_item(part => $part1, qty => 3, sellprice => 70),
47 create_invoice_item(part => $part2, qty => 10, sellprice => 50),
49 department_id => $department->id,
50 globalproject_id => $project->id,
52 $invoice->pay_invoice(chart_id => $bank->id,
53 amount => $invoice->open_amount,
54 transdate => $payment_date->to_kivitendo,
58 my $datev1 = SL::DATEV->new(
59 dbh => $invoice->db->dbh,
60 trans_id => $invoice->id,
63 $datev1->generate_datev_data;
65 cmp_bag $datev1->generate_datev_lines, [
67 'belegfeld1' => Encode::decode('utf-8', "Þ sales ¥& invöice"),
68 'buchungstext' => 'Testcustomer',
69 'datum' => '01.01.2017',
70 'gegenkonto' => '8400',
72 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
73 'kost2' => 'Crowd-Funding September 2017',
76 'soll_haben_kennzeichen' => 'S',
79 'belegfeld1' => Encode::decode('utf-8', "Þ sales ¥& invöice"),
80 'buchungstext' => 'Testcustomer',
81 'datum' => '01.01.2017',
82 'gegenkonto' => '8300',
84 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
85 'kost2' => 'Crowd-Funding September 2017',
88 'soll_haben_kennzeichen' => 'S',
91 'belegfeld1' => Encode::decode('utf-8', "Þ sales ¥& invöice"),
92 'buchungstext' => 'Testcustomer',
93 'datum' => '05.01.2017',
94 'gegenkonto' => '1400',
96 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
97 'kost2' => 'Crowd-Funding September 2017',
100 'soll_haben_kennzeichen' => 'S',
102 ], "trans_id datev check ok";
105 $datev1->generate_datev_data;
106 cmp_bag $datev1->generate_datev_lines, [
108 'belegfeld1' => Encode::decode('utf-8', "Þ sales ¥& invöice"),
109 'buchungstext' => 'Testcustomer',
110 'datum' => '01.01.2017',
111 'gegenkonto' => '8400',
112 'konto' => $customer->customernumber,
113 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
114 'kost2' => 'Crowd-Funding September 2017',
117 'soll_haben_kennzeichen' => 'S',
120 'belegfeld1' => Encode::decode('utf-8', "Þ sales ¥& invöice"),
121 'buchungstext' => 'Testcustomer',
122 'datum' => '01.01.2017',
123 'gegenkonto' => '8300',
124 'konto' => $customer->customernumber,
125 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
126 'kost2' => 'Crowd-Funding September 2017',
129 'soll_haben_kennzeichen' => 'S',
132 'belegfeld1' => Encode::decode('utf-8', "Þ sales ¥& invöice"),
133 'buchungstext' => 'Testcustomer',
134 'datum' => '05.01.2017',
135 'gegenkonto' => $customer->customernumber,
137 'kost1' => 'Kostenstelle DATEV-Schnittstelle 2018',
138 'kost2' => 'Crowd-Funding September 2017',
141 'soll_haben_kennzeichen' => 'S',
143 ], "trans_id datev check use_pk ok";
146 my $startdate = DateTime->new(year => 2017, month => 1, day => 1);
147 my $enddate = DateTime->new(year => 2017, month => 12, day => 31);
149 # check conversion to csv
150 $datev1->from($startdate);
151 $datev1->to($enddate);
152 $datev1->use_pk(0); # reset use_pk for csv_buchungsexport
154 # splice away the header, because sort won't do
155 # we need sort, because pay_invoice is not acc_trans_id order safe
156 my @data_csv = splice @{ $datev1->csv_buchungsexport() }, 2, 5;
157 @data_csv = sort { $a->[0] <=> $b->[0] } @data_csv;
159 my $cp1252_belegfeld1 = SL::Iconv::convert("UTF-8", "CP1252", 'Þ sales ¥& i');
160 my $cp1252_buchungstext = SL::Iconv::convert("UTF-8", "CP1252", 'Þ sales ¥& invöice');
162 cmp_bag($data_csv[1], [ 535, 'S', 'EUR', undef, undef, undef, '1400', '8300', undef, '0101', $cp1252_belegfeld1,
163 undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
164 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
165 undef, 'Crowd-Fu', 'Kostenst', undef, undef, undef, undef, undef, undef, undef, undef,
166 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
167 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
168 undef, undef, undef, undef, undef, 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 ]
174 cmp_bag($data_csv[0], [ '249,9', 'S', 'EUR', undef, undef, undef, '1400', '8400', undef, '0101', $cp1252_belegfeld1,
175 undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
176 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
177 undef, 'Crowd-Fu', 'Kostenst', undef, undef, undef, undef, undef, undef, undef, undef,
178 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
179 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
180 undef, undef, undef, undef, undef, 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 ]
185 cmp_bag($data_csv[2], [ '784,9', 'S', 'EUR', undef, undef, undef, '1200', '1400', undef, '0501', $cp1252_belegfeld1,
186 undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
187 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
188 undef, 'Crowd-Fu', 'Kostenst', undef, undef, undef, undef, undef, undef, undef, undef,
189 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
190 undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
191 undef, undef, undef, undef, undef, 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 ]
196 my $march_9 = DateTime->new(year => 2017, month => 3, day => 9);
197 my $invoice2 = create_sales_invoice(
198 invnumber => "2 sales invoice",
199 customer => $customer,
202 intnotes => 'booked in March',
205 invoiceitems => [ create_invoice_item(part => $part1, qty => 6, sellprice => 70),
206 create_invoice_item(part => $part2, qty => 20, sellprice => 50),
210 my $credit_note = create_credit_note(
211 invnumber => 'Gutschrift 34',
212 customer => $customer,
215 intnotes => 'booked in February',
218 invoiceitems => [ create_invoice_item(part => $part1, qty => 3, sellprice => 70),
219 create_invoice_item(part => $part2, qty => 10, sellprice => 50),
223 my $datev = SL::DATEV->new(
228 $datev->generate_datev_data(from_to => $datev->fromto);
229 my $datev_lines = $datev->generate_datev_lines;
230 my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
231 cmp_ok($::form->round_amount($umsatzsumme,2), '==', 3924.5, "Sum of all bookings ok");
233 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
234 $datev_lines = $datev->generate_datev_lines;
236 note('testing purchase invoice');
237 my $purchase_invoice = new_purchase_invoice();
238 $datev1 = SL::DATEV->new(
239 dbh => $purchase_invoice->db->dbh,
240 trans_id => $purchase_invoice->id,
243 $datev1->generate_datev_data;
244 cmp_bag $datev1->generate_datev_lines, [
246 'belegfeld1' => 'ap1',
247 'buchungstext' => 'Testvendor',
248 'datum' => '01.01.2017',
249 'gegenkonto' => '1600',
253 'soll_haben_kennzeichen' => 'H',
258 'belegfeld1' => 'ap1',
259 'buchungstext' => 'Testvendor',
260 'datum' => '01.01.2017',
261 'gegenkonto' => '1600',
265 'soll_haben_kennzeichen' => 'H',
269 ], "trans_id datev check purchase_invoice ok";
271 $datev1->generate_datev_data;
272 cmp_bag $datev1->generate_datev_lines, [
274 'belegfeld1' => 'ap1',
275 'buchungstext' => 'Testvendor',
276 'datum' => '01.01.2017',
277 'gegenkonto' => $vendor->vendornumber,
281 'soll_haben_kennzeichen' => 'H',
286 'belegfeld1' => 'ap1',
287 'buchungstext' => 'Testvendor',
288 'datum' => '01.01.2017',
289 'gegenkonto' => $vendor->vendornumber,
293 'soll_haben_kennzeichen' => 'H',
297 ], "trans_id datev check purchase_invoice use_pk ok";
299 note('testing gldatefrom');
300 $datev = SL::DATEV->new(
303 to => DateTime->new(year => 2017, month => 01, day => 31),
306 $::form = Support::TestSetup->create_new_form;
307 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
309 $datev->generate_datev_data(from_to => $datev->fromto);
310 $datev_lines = $datev->generate_datev_lines;
311 $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
312 cmp_ok($umsatzsumme, '==', 1569.8, "Sum of bookings made after March 1st (only invoice2) ok");
314 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 5, day => 1)->to_kivitendo;
315 $datev->generate_datev_data(from_to => $datev->fromto);
316 cmp_bag $datev->generate_datev_lines, [], "no bookings for January made after May 1st: ok";
321 sub new_purchase_invoice {
322 # manually create a Kreditorenbuchung from scratch, ap + acc_trans bookings, as no helper exists yet, like $invoice->post.
323 # arap-Booking must come last in the acc_trans order
324 # this function was essentially copied from t/db_helper/payment.t, refactor once $purchase_invoice->post exists
325 my $currency_id = $::instance_conf->get_currency_id;
326 my $employee = SL::DB::Manager::Employee->current || die "No employee";
327 my $taxzone = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || die "No taxzone";
329 my $purchase_invoice = SL::DB::PurchaseInvoice->new(
331 currency_id => $currency_id,
332 employee_id => $employee->id,
341 taxzone_id => $taxzone->id,
344 vendor_id => $vendor->id,
347 my $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3400');
348 my $expense_chart_booking= SL::DB::AccTransaction->new(
350 chart_id => $expense_chart->id,
351 chart_link => $expense_chart->link,
355 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
358 trans_id => $purchase_invoice->id,
360 $expense_chart_booking->save;
362 my $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1576');
363 my $tax_chart_booking= SL::DB::AccTransaction->new(
365 chart_id => $tax_chart->id,
366 chart_link => $tax_chart->link,
370 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
373 trans_id => $purchase_invoice->id,
375 $tax_chart_booking->save;
376 $expense_chart = SL::DB::Manager::Chart->find_by(accno => '3300');
377 $expense_chart_booking= SL::DB::AccTransaction->new(
379 chart_id => $expense_chart->id,
380 chart_link => $expense_chart->link,
384 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
387 trans_id => $purchase_invoice->id,
389 $expense_chart_booking->save;
391 $tax_chart = SL::DB::Manager::Chart->find_by(accno => '1571');
392 $tax_chart_booking= SL::DB::AccTransaction->new(
393 trans_id => $purchase_invoice->id,
394 chart_id => $tax_chart->id,
395 chart_link => $tax_chart->link,
402 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
404 $tax_chart_booking->save;
405 my $arap_chart = SL::DB::Manager::Chart->find_by(accno => '1600');
406 my $arap_booking= SL::DB::AccTransaction->new(
407 trans_id => $purchase_invoice->id,
408 chart_id => $arap_chart->id,
409 chart_link => $arap_chart->link,
416 tax_id => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
420 return $purchase_invoice;
424 SL::DB::Manager::AccTransaction->delete_all(all => 1);
425 SL::DB::Manager::InvoiceItem->delete_all( all => 1);
426 SL::DB::Manager::Invoice->delete_all( all => 1);
427 SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
428 SL::DB::Manager::Customer->delete_all( all => 1);
429 SL::DB::Manager::Part->delete_all( all => 1);
430 SL::DB::Manager::Project->delete_all( all => 1);
431 SL::DB::Manager::Department->delete_all( all => 1);
432 SL::DATEV->clean_temporary_directories;