t/datev/invoices.t

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