t/datev/invoices.t

   1 use strict;
   2 use Test::More;
   3 use Test::Deep qw(cmp_bag);
   4
   5 use lib 't';
   6 use utf8;
   7
   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;
  13 use SL::DB::Chart;
  14 use DateTime;
  15
  16 Support::TestSetup::login();
  17
  18 clear_up();
  19
  20 my $dbh = SL::DB->client->dbh;
  21
  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;
  31
  32 my $part1 = new_part(partnumber => '19', description => 'Part 19%')->save;
  33 my $part2 = new_part(
  34   partnumber         => '7',
  35   description        => 'Part 7%',
  36   buchungsgruppen_id => $buchungsgruppe7->id,
  37 )->save;
  38
  39 my $invoice = create_sales_invoice(
  40   invnumber    => "Þ sales ¥& invöice",
  41   customer     => $customer,
  42   itime        => $gldate,
  43   gldate       => $gldate,
  44   intnotes     => 'booked in February',
  45   taxincluded  => 0,
  46   transdate    => $date,
  47   invoiceitems => [ create_invoice_item(part => $part1, qty =>  3, sellprice => 70),
  48                     create_invoice_item(part => $part2, qty => 10, sellprice => 50),
  49                   ],
  50   department_id    => $department->id,
  51   globalproject_id => $project->id,
  52 );
  53 $invoice->pay_invoice(chart_id      => $bank->id,
  54                       amount        => $invoice->open_amount,
  55                       transdate     => $payment_date->to_kivitendo,
  56                       memo          => 'foobar',
  57                       source        => 'barfoo',
  58                      );
  59 my $datev1 = SL::DATEV->new(
  60   dbh        => $invoice->db->dbh,
  61   trans_id   => $invoice->id,
  62 );
  63
  64 $datev1->generate_datev_data;
  65
  66 my @data_datev   = sort { $a->{umsatz} <=> $b->{umsatz} } @{ $datev1->generate_datev_lines() };
  67 cmp_bag \@data_datev, [
  68                                          {
  69                                            'belegfeld1'   => "\x{de} sales \x{a5}& inv\x{f6}ice",
  70                                            'buchungstext' => 'Testcustomer',
  71                                            'datum'        => '01.01.2017',
  72                                            'gegenkonto'   => '8400',
  73                                            'konto'        => '1400',
  74                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
  75                                            'kost2'        => 'Crowd-Funding September 2017',
  76                                            'umsatz'       => '249.9',
  77                                            'waehrung'     => 'EUR',
  78                                            'soll_haben_kennzeichen' => 'S',
  79                                          },
  80                                          {
  81                                            'belegfeld1'   => "\x{de} sales \x{a5}& inv\x{f6}ice",
  82                                            'buchungstext' => 'Testcustomer',
  83                                            'datum'        => '01.01.2017',
  84                                            'gegenkonto'   => '8300',
  85                                            'konto'        => '1400',
  86                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
  87                                            'kost2'        => 'Crowd-Funding September 2017',
  88                                            'umsatz'       => 535,
  89                                            'waehrung'     => 'EUR',
  90                                            'soll_haben_kennzeichen' => 'S',
  91                                          },
  92                                          {
  93                                            'belegfeld1'   => "\x{de} sales \x{a5}& inv\x{f6}ice",
  94
  95
  96 'buchungstext' => 'Testcustomer',
  97                                            'buchungstext' => 'Testcustomer',
  98                                            'datum'        => '05.01.2017',
  99                                            'gegenkonto'   => '1400',
 100                                            'konto'        => '1200',
 101                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
 102                                            'kost2'        => 'Crowd-Funding September 2017',
 103                                            'umsatz'       => '784.9',
 104                                            'waehrung'     => 'EUR',
 105                                            'soll_haben_kennzeichen' => 'S',
 106                                          },
 107                                        ], "trans_id datev check ok";
 108
 109 $datev1->use_pk(1);
 110 $datev1->generate_datev_data;
 111 cmp_bag $datev1->generate_datev_lines, [
 112                                          {
 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',
 120                                            'umsatz'       => '249.9',
 121                                            'waehrung'     => 'EUR',
 122                                            'soll_haben_kennzeichen' => 'S',
 123                                          },
 124                                          {
 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',
 132                                            'umsatz'       => 535,
 133                                            'waehrung'     => 'EUR',
 134                                            'soll_haben_kennzeichen' => 'S',
 135                                          },
 136                                          {
 137                                            'belegfeld1'   => "\x{de} sales \x{a5}& inv\x{f6}ice",
 138                                            'buchungstext' => 'Testcustomer',
 139                                            'datum'        => '05.01.2017',
 140                                            'gegenkonto'   => $customer->customernumber,
 141                                            'konto'        => '1200',
 142                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
 143                                            'kost2'        => 'Crowd-Funding September 2017',
 144                                            'umsatz'       => '784.9',
 145                                            'waehrung'     => 'EUR',
 146                                            'soll_haben_kennzeichen' => 'S',
 147                                          },
 148                                        ], "trans_id datev check use_pk ok";
 149
 150
 151 my $startdate = DateTime->new(year => 2017, month =>  1, day =>  1);
 152 my $enddate   = DateTime->new(year => 2017, month => 12, day => 31);
 153
 154 # check conversion to csv
 155 $datev1->from($startdate);
 156 $datev1->to($enddate);
 157 # reset use_pk for csv_buchungsexport
 158 $datev1->use_pk(0);
 159 $datev1->generate_datev_data;
 160
 161
 162 my ($datev_ref, $w_ref) = SL::DATEV::CSV->new(datev_lines  => $datev1->generate_datev_lines,
 163                                               from         => $startdate,
 164                                               to           => $enddate,
 165                                               locked       => $datev1->locked,
 166                                    );
 167 # warnings should be undef -> no array elements at all
 168 is(scalar @{ $w_ref }, 0);
 169
 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;
 174
 175 my $cp1252_belegfeld1   = SL::Iconv::convert("UTF-8", "CP1252", 'Þ sales ¥& i');
 176 my $cp1252_buchungstext = SL::Iconv::convert("UTF-8", "CP1252", 'Þ sales ¥& invöice');
 177
 178 cmp_bag($data_csv[1], [ 535, 'S', 'EUR', undef, undef, undef, '1400', '8300', undef, '0101', $cp1252_belegfeld1,
 179                      undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
 180                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 181                      undef, 'Crowd-Fu', 'Kostenst', 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, undef, undef, undef, undef, undef, undef, undef, undef,
 187                      undef, undef, undef, undef, undef ]
 188        );
 189
 190 cmp_bag($data_csv[0], [ '249,9', 'S', 'EUR', undef, undef, undef, '1400', '8400', undef, '0101', $cp1252_belegfeld1,
 191                      undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
 192                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 193                      undef, 'Crowd-Fu', 'Kostenst', 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, undef, undef, undef, undef, undef, undef, undef, undef,
 198                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 199                      undef, undef, undef, undef, undef ]
 200        );
 201 cmp_bag($data_csv[2], [ '784,9', 'S', 'EUR', undef, undef, undef, '1200', '1400', undef, '0501', $cp1252_belegfeld1,
 202                      undef, undef, $cp1252_buchungstext, undef, undef, undef, undef, undef, undef, undef, undef,
 203                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 204                      undef, 'Crowd-Fu', 'Kostenst', undef, undef, undef, undef, undef, undef, undef, undef,
 205                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 206                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 207                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 208                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 209                      undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
 210                      undef, undef, undef, undef, undef ]
 211         );
 212 my $march_9 = DateTime->new(year => 2017, month =>  3, day => 9);
 213 my $invoice2 = create_sales_invoice(
 214   invnumber    => "2 sales invoice",
 215   customer     => $customer,
 216   itime        => $march_9,
 217   gldate       => $march_9,
 218   intnotes     => 'booked in March',
 219   taxincluded  => 0,
 220   transdate    => $date,
 221   invoiceitems => [ create_invoice_item(part => $part1, qty =>  6, sellprice => 70),
 222                     create_invoice_item(part => $part2, qty => 20, sellprice => 50),
 223                   ]
 224 );
 225
 226 my $credit_note = create_credit_note(
 227   invnumber    => 'Gutschrift 34',
 228   customer     => $customer,
 229   itime        => $gldate,
 230   gldate       => $gldate,
 231   intnotes     => 'booked in February',
 232   taxincluded  => 0,
 233   transdate    => $date,
 234   invoiceitems => [ create_invoice_item(part => $part1, qty =>  3, sellprice => 70),
 235                     create_invoice_item(part => $part2, qty => 10, sellprice => 50),
 236                   ]
 237 );
 238
 239 my $datev = SL::DATEV->new(
 240   dbh        => $dbh,
 241   from       => $startdate,
 242   to         => $enddate,
 243 );
 244 $datev->generate_datev_data(from_to => $datev->fromto);
 245 my $datev_lines = $datev->generate_datev_lines;
 246 my $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
 247 cmp_ok($::form->round_amount($umsatzsumme,2), '==', 3924.5, "Sum of all bookings ok");
 248
 249 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
 250 $datev_lines = $datev->generate_datev_lines;
 251
 252 note('testing purchase invoice');
 253 my $purchase_invoice = new_purchase_invoice();
 254 $datev1 = SL::DATEV->new(
 255   dbh        => $purchase_invoice->db->dbh,
 256   trans_id   => $purchase_invoice->id,
 257 );
 258
 259 $datev1->generate_datev_data;
 260 cmp_bag $datev1->generate_datev_lines, [
 261                                         {
 262                                           'belegfeld1'             => 'ap1',
 263                                           'buchungstext'           => 'Testvendor',
 264                                           'datum'                  => '01.01.2017',
 265                                           'gegenkonto'             => '1600',
 266                                           'konto'                  => '3400',
 267                                           'kost1'                  => undef,
 268                                           'kost2'                  => undef,
 269                                           'soll_haben_kennzeichen' => 'H',
 270                                           'umsatz'                 => 119,
 271                                           'waehrung'               => 'EUR'
 272                                         },
 273                                         {
 274                                           'belegfeld1'             => 'ap1',
 275                                           'buchungstext'           => 'Testvendor',
 276                                           'datum'                  => '01.01.2017',
 277                                           'gegenkonto'             => '1600',
 278                                           'konto'                  => '3300',
 279                                           'kost1'                  => undef,
 280                                           'kost2'                  => undef,
 281                                           'soll_haben_kennzeichen' => 'H',
 282                                           'umsatz'                 => 107,
 283                                           'waehrung'               => 'EUR'
 284                                         }
 285                                        ], "trans_id datev check purchase_invoice ok";
 286 $datev1->use_pk(1);
 287 $datev1->generate_datev_data;
 288 cmp_bag $datev1->generate_datev_lines, [
 289                                         {
 290                                           'belegfeld1'             => 'ap1',
 291                                           'buchungstext'           => 'Testvendor',
 292                                           'datum'                  => '01.01.2017',
 293                                           'gegenkonto'             => $vendor->vendornumber,
 294                                           'konto'                  => '3400',
 295                                           'kost1'                  => undef,
 296                                           'kost2'                  => undef,
 297                                           'soll_haben_kennzeichen' => 'H',
 298                                           'umsatz'                 => 119,
 299                                           'waehrung'               => 'EUR'
 300                                         },
 301                                         {
 302                                           'belegfeld1'             => 'ap1',
 303                                           'buchungstext'           => 'Testvendor',
 304                                           'datum'                  => '01.01.2017',
 305                                           'gegenkonto'             => $vendor->vendornumber,
 306                                           'konto'                  => '3300',
 307                                           'kost1'                  => undef,
 308                                           'kost2'                  => undef,
 309                                           'soll_haben_kennzeichen' => 'H',
 310                                           'umsatz'                 => 107,
 311                                           'waehrung'               => 'EUR'
 312                                         }
 313                                        ], "trans_id datev check purchase_invoice use_pk ok";
 314
 315 note('testing gldatefrom');
 316 $datev = SL::DATEV->new(
 317   dbh        => $dbh,
 318   from       => $startdate,
 319   to         => DateTime->new(year => 2017, month => 01, day => 31),
 320 );
 321
 322 $::form               = Support::TestSetup->create_new_form;
 323 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
 324
 325 $datev->generate_datev_data(from_to => $datev->fromto);
 326 $datev_lines = $datev->generate_datev_lines;
 327 $umsatzsumme = sum map { $_->{umsatz} } @{ $datev_lines };
 328 cmp_ok($umsatzsumme, '==', 1569.8, "Sum of bookings made after March 1st (only invoice2) ok");
 329
 330 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 5, day => 1)->to_kivitendo;
 331 $datev->generate_datev_data(from_to => $datev->fromto);
 332 cmp_bag $datev->generate_datev_lines, [], "no bookings for January made after May 1st: ok";
 333
 334 done_testing();
 335 # clear_up();
 336
 337 sub new_purchase_invoice {
 338   # manually create a Kreditorenbuchung from scratch, ap + acc_trans bookings, as no helper exists yet, like $invoice->post.
 339   # arap-Booking must come last in the acc_trans order
 340   # this function was essentially copied from t/db_helper/payment.t, refactor once $purchase_invoice->post exists
 341   my $currency_id = $::instance_conf->get_currency_id;
 342   my $employee    = SL::DB::Manager::Employee->current                          || die "No employee";
 343   my $taxzone     = SL::DB::Manager::TaxZone->find_by( description => 'Inland') || die "No taxzone";
 344
 345   my $purchase_invoice = SL::DB::PurchaseInvoice->new(
 346     amount      => '226',
 347     currency_id => $currency_id,
 348     employee_id => $employee->id,
 349     gldate      => $date,
 350     invnumber   => "ap1",
 351     invoice     => 0,
 352     itime       => $date,
 353     mtime       => $date,
 354     netamount   => '200',
 355     paid        => '0',
 356     taxincluded => 0,
 357     taxzone_id  => $taxzone->id,
 358     transdate   => $date,
 359     type        => 'invoice',
 360     vendor_id   => $vendor->id,
 361   )->save;
 362
 363   my $expense_chart  = SL::DB::Manager::Chart->find_by(accno => '3400');
 364   my $expense_chart_booking= SL::DB::AccTransaction->new(
 365     amount     => '-100',
 366     chart_id   => $expense_chart->id,
 367     chart_link => $expense_chart->link,
 368     itime      => $date,
 369     mtime      => $date,
 370     source     => '',
 371     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
 372     taxkey     => 9,
 373     transdate  => $date,
 374     trans_id   => $purchase_invoice->id,
 375   );
 376   $expense_chart_booking->save;
 377
 378   my $tax_chart  = SL::DB::Manager::Chart->find_by(accno => '1576');
 379   my $tax_chart_booking= SL::DB::AccTransaction->new(
 380     amount     => '-19',
 381     chart_id   => $tax_chart->id,
 382     chart_link => $tax_chart->link,
 383     itime      => $date,
 384     mtime      => $date,
 385     source     => '',
 386     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
 387     taxkey     => 0,
 388     transdate  => $date,
 389     trans_id   => $purchase_invoice->id,
 390   );
 391   $tax_chart_booking->save;
 392   $expense_chart  = SL::DB::Manager::Chart->find_by(accno => '3300');
 393   $expense_chart_booking= SL::DB::AccTransaction->new(
 394     amount     => '-100',
 395     chart_id   => $expense_chart->id,
 396     chart_link => $expense_chart->link,
 397     itime      => $date,
 398     mtime      => $date,
 399     source     => '',
 400     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
 401     taxkey     => 8,
 402     transdate  => $date,
 403     trans_id   => $purchase_invoice->id,
 404   );
 405   $expense_chart_booking->save;
 406
 407   $tax_chart  = SL::DB::Manager::Chart->find_by(accno => '1571');
 408   $tax_chart_booking= SL::DB::AccTransaction->new(
 409     trans_id   => $purchase_invoice->id,
 410     chart_id   => $tax_chart->id,
 411     chart_link => $tax_chart->link,
 412     amount     => '-7',
 413     transdate  => $date,
 414     itime      => $date,
 415     mtime      => $date,
 416     source     => '',
 417     taxkey     => 0,
 418     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
 419   );
 420   $tax_chart_booking->save;
 421   my $arap_chart  = SL::DB::Manager::Chart->find_by(accno => '1600');
 422   my $arap_booking= SL::DB::AccTransaction->new(
 423     trans_id   => $purchase_invoice->id,
 424     chart_id   => $arap_chart->id,
 425     chart_link => $arap_chart->link,
 426     amount     => '226',
 427     transdate  => $date,
 428     itime      => $date,
 429     mtime      => $date,
 430     source     => '',
 431     taxkey     => 0,
 432     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
 433   );
 434   $arap_booking->save;
 435
 436   return $purchase_invoice;
 437 }
 438
 439 sub clear_up {
 440   SL::DB::Manager::AccTransaction->delete_all(all => 1);
 441   SL::DB::Manager::InvoiceItem->delete_all(   all => 1);
 442   SL::DB::Manager::Invoice->delete_all(       all => 1);
 443   SL::DB::Manager::PurchaseInvoice->delete_all(all => 1);
 444   SL::DB::Manager::Customer->delete_all(      all => 1);
 445   SL::DB::Manager::Part->delete_all(          all => 1);
 446   SL::DB::Manager::Project->delete_all(       all => 1);
 447   SL::DB::Manager::Department->delete_all(    all => 1);
 448   SL::DATEV->clean_temporary_directories;
 449 };
 450
 451 1;