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                                            'buchungstext' => 'Testcustomer',
  95                                            'buchungstext' => 'Testcustomer',
  96                                            'datum'        => '05.01.2017',
  97                                            'gegenkonto'   => '1400',
  98                                            'konto'        => '1200',
  99                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
 100                                            'kost2'        => 'Crowd-Funding September 2017',
 101                                            'umsatz'       => '784.9',
 102                                            'waehrung'     => 'EUR',
 103                                            'soll_haben_kennzeichen' => 'S',
 104                                          },
 105                                        ], "trans_id datev check ok";
 106
 107 $datev1->use_pk(1);
 108 $datev1->generate_datev_data;
 109 cmp_bag $datev1->generate_datev_lines, [
 110                                          {
 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',
 118                                            'umsatz'       => '249.9',
 119                                            'waehrung'     => 'EUR',
 120                                            'soll_haben_kennzeichen' => 'S',
 121                                          },
 122                                          {
 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',
 130                                            'umsatz'       => 535,
 131                                            'waehrung'     => 'EUR',
 132                                            'soll_haben_kennzeichen' => 'S',
 133                                          },
 134                                          {
 135                                            'belegfeld1'   => "\x{de} sales \x{a5}& inv\x{f6}ice",
 136                                            'buchungstext' => 'Testcustomer',
 137                                            'datum'        => '05.01.2017',
 138                                            'gegenkonto'   => $customer->customernumber,
 139                                            'konto'        => '1200',
 140                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
 141                                            'kost2'        => 'Crowd-Funding September 2017',
 142                                            'umsatz'       => '784.9',
 143                                            'waehrung'     => 'EUR',
 144                                            'soll_haben_kennzeichen' => 'S',
 145                                          },
 146                                        ], "trans_id datev check use_pk ok";
 147
 148
 149 my $startdate = DateTime->new(year => 2017, month =>  1, day =>  1);
 150 my $enddate   = DateTime->new(year => 2017, month => 12, day => 31);
 151
 152 # check conversion to csv
 153 $datev1->from($startdate);
 154 $datev1->to($enddate);
 155 $datev1->use_pk(0); # reset use_pk for csv_buchungsexport
 156
 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;
 161
 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');
 164
 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 ]
 175        );
 176
 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 ]
 187        );
 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 ]
 198         );
 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,
 203   itime        => $march_9,
 204   gldate       => $march_9,
 205   intnotes     => 'booked in March',
 206   taxincluded  => 0,
 207   transdate    => $date,
 208   invoiceitems => [ create_invoice_item(part => $part1, qty =>  6, sellprice => 70),
 209                     create_invoice_item(part => $part2, qty => 20, sellprice => 50),
 210                   ]
 211 );
 212
 213 my $credit_note = create_credit_note(
 214   invnumber    => 'Gutschrift 34',
 215   customer     => $customer,
 216   itime        => $gldate,
 217   gldate       => $gldate,
 218   intnotes     => 'booked in February',
 219   taxincluded  => 0,
 220   transdate    => $date,
 221   invoiceitems => [ create_invoice_item(part => $part1, qty =>  3, sellprice => 70),
 222                     create_invoice_item(part => $part2, qty => 10, sellprice => 50),
 223                   ]
 224 );
 225
 226 my $datev = SL::DATEV->new(
 227   dbh        => $dbh,
 228   from       => $startdate,
 229   to         => $enddate,
 230 );
 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");
 235
 236 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
 237 $datev_lines = $datev->generate_datev_lines;
 238
 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,
 244 );
 245
 246 $datev1->generate_datev_data;
 247 cmp_bag $datev1->generate_datev_lines, [
 248                                         {
 249                                           'belegfeld1'             => 'ap1',
 250                                           'buchungstext'           => 'Testvendor',
 251                                           'datum'                  => '01.01.2017',
 252                                           'gegenkonto'             => '1600',
 253                                           'konto'                  => '3400',
 254                                           'kost1'                  => undef,
 255                                           'kost2'                  => undef,
 256                                           'soll_haben_kennzeichen' => 'H',
 257                                           'umsatz'                 => 119,
 258                                           'waehrung'               => 'EUR'
 259                                         },
 260                                         {
 261                                           'belegfeld1'             => 'ap1',
 262                                           'buchungstext'           => 'Testvendor',
 263                                           'datum'                  => '01.01.2017',
 264                                           'gegenkonto'             => '1600',
 265                                           'konto'                  => '3300',
 266                                           'kost1'                  => undef,
 267                                           'kost2'                  => undef,
 268                                           'soll_haben_kennzeichen' => 'H',
 269                                           'umsatz'                 => 107,
 270                                           'waehrung'               => 'EUR'
 271                                         }
 272                                        ], "trans_id datev check purchase_invoice ok";
 273 $datev1->use_pk(1);
 274 $datev1->generate_datev_data;
 275 cmp_bag $datev1->generate_datev_lines, [
 276                                         {
 277                                           'belegfeld1'             => 'ap1',
 278                                           'buchungstext'           => 'Testvendor',
 279                                           'datum'                  => '01.01.2017',
 280                                           'gegenkonto'             => $vendor->vendornumber,
 281                                           'konto'                  => '3400',
 282                                           'kost1'                  => undef,
 283                                           'kost2'                  => undef,
 284                                           'soll_haben_kennzeichen' => 'H',
 285                                           'umsatz'                 => 119,
 286                                           'waehrung'               => 'EUR'
 287                                         },
 288                                         {
 289                                           'belegfeld1'             => 'ap1',
 290                                           'buchungstext'           => 'Testvendor',
 291                                           'datum'                  => '01.01.2017',
 292                                           'gegenkonto'             => $vendor->vendornumber,
 293                                           'konto'                  => '3300',
 294                                           'kost1'                  => undef,
 295                                           'kost2'                  => undef,
 296                                           'soll_haben_kennzeichen' => 'H',
 297                                           'umsatz'                 => 107,
 298                                           'waehrung'               => 'EUR'
 299                                         }
 300                                        ], "trans_id datev check purchase_invoice use_pk ok";
 301
 302 note('testing gldatefrom');
 303 $datev = SL::DATEV->new(
 304   dbh        => $dbh,
 305   from       => $startdate,
 306   to         => DateTime->new(year => 2017, month => 01, day => 31),
 307 );
 308
 309 $::form               = Support::TestSetup->create_new_form;
 310 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
 311
 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");
 316
 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";
 320
 321 done_testing();
 322 # clear_up();
 323
 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";
 331
 332   my $purchase_invoice = SL::DB::PurchaseInvoice->new(
 333     amount      => '226',
 334     currency_id => $currency_id,
 335     employee_id => $employee->id,
 336     gldate      => $date,
 337     invnumber   => "ap1",
 338     invoice     => 0,
 339     itime       => $date,
 340     mtime       => $date,
 341     netamount   => '200',
 342     paid        => '0',
 343     taxincluded => 0,
 344     taxzone_id  => $taxzone->id,
 345     transdate   => $date,
 346     type        => 'invoice',
 347     vendor_id   => $vendor->id,
 348   )->save;
 349
 350   my $expense_chart  = SL::DB::Manager::Chart->find_by(accno => '3400');
 351   my $expense_chart_booking= SL::DB::AccTransaction->new(
 352     amount     => '-100',
 353     chart_id   => $expense_chart->id,
 354     chart_link => $expense_chart->link,
 355     itime      => $date,
 356     mtime      => $date,
 357     source     => '',
 358     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
 359     taxkey     => 9,
 360     transdate  => $date,
 361     trans_id   => $purchase_invoice->id,
 362   );
 363   $expense_chart_booking->save;
 364
 365   my $tax_chart  = SL::DB::Manager::Chart->find_by(accno => '1576');
 366   my $tax_chart_booking= SL::DB::AccTransaction->new(
 367     amount     => '-19',
 368     chart_id   => $tax_chart->id,
 369     chart_link => $tax_chart->link,
 370     itime      => $date,
 371     mtime      => $date,
 372     source     => '',
 373     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
 374     taxkey     => 0,
 375     transdate  => $date,
 376     trans_id   => $purchase_invoice->id,
 377   );
 378   $tax_chart_booking->save;
 379   $expense_chart  = SL::DB::Manager::Chart->find_by(accno => '3300');
 380   $expense_chart_booking= SL::DB::AccTransaction->new(
 381     amount     => '-100',
 382     chart_id   => $expense_chart->id,
 383     chart_link => $expense_chart->link,
 384     itime      => $date,
 385     mtime      => $date,
 386     source     => '',
 387     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
 388     taxkey     => 8,
 389     transdate  => $date,
 390     trans_id   => $purchase_invoice->id,
 391   );
 392   $expense_chart_booking->save;
 393
 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,
 399     amount     => '-7',
 400     transdate  => $date,
 401     itime      => $date,
 402     mtime      => $date,
 403     source     => '',
 404     taxkey     => 0,
 405     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
 406   );
 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,
 413     amount     => '226',
 414     transdate  => $date,
 415     itime      => $date,
 416     mtime      => $date,
 417     source     => '',
 418     taxkey     => 0,
 419     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
 420   );
 421   $arap_booking->save;
 422
 423   return $purchase_invoice;
 424 }
 425
 426 sub clear_up {
 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;
 436 };
 437
 438 1;