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
  63 $datev1->generate_datev_data;
  64
  65 cmp_bag $datev1->generate_datev_lines, [
  66                                          {
  67                                            'belegfeld1'   => Encode::decode('utf-8', "Þ sales ¥& invöice"),
  68                                            'buchungstext' => 'Testcustomer',
  69                                            'datum'        => '01.01.2017',
  70                                            'gegenkonto'   => '8400',
  71                                            'konto'        => '1400',
  72                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
  73                                            'kost2'        => 'Crowd-Funding September 2017',
  74                                            'umsatz'       => '249.9',
  75                                            'waehrung'     => 'EUR',
  76                                            'soll_haben_kennzeichen' => 'S',
  77                                          },
  78                                          {
  79                                            'belegfeld1'   => Encode::decode('utf-8', "Þ sales ¥& invöice"),
  80                                            'buchungstext' => 'Testcustomer',
  81                                            'datum'        => '01.01.2017',
  82                                            'gegenkonto'   => '8300',
  83                                            'konto'        => '1400',
  84                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
  85                                            'kost2'        => 'Crowd-Funding September 2017',
  86                                            'umsatz'       => 535,
  87                                            'waehrung'     => 'EUR',
  88                                            'soll_haben_kennzeichen' => 'S',
  89                                          },
  90                                          {
  91                                            'belegfeld1'   => Encode::decode('utf-8', "Þ sales ¥& invöice"),
  92                                            'buchungstext' => 'Testcustomer',
  93                                            'datum'        => '05.01.2017',
  94                                            'gegenkonto'   => '1400',
  95                                            'konto'        => '1200',
  96                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
  97                                            'kost2'        => 'Crowd-Funding September 2017',
  98                                            'umsatz'       => '784.9',
  99                                            'waehrung'     => 'EUR',
 100                                            'soll_haben_kennzeichen' => 'S',
 101                                          },
 102                                        ], "trans_id datev check ok";
 103
 104 $datev1->use_pk(1);
 105 $datev1->generate_datev_data;
 106 cmp_bag $datev1->generate_datev_lines, [
 107                                          {
 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',
 115                                            'umsatz'       => '249.9',
 116                                            'waehrung'     => 'EUR',
 117                                            'soll_haben_kennzeichen' => 'S',
 118                                          },
 119                                          {
 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',
 127                                            'umsatz'       => 535,
 128                                            'waehrung'     => 'EUR',
 129                                            'soll_haben_kennzeichen' => 'S',
 130                                          },
 131                                          {
 132                                            'belegfeld1'   => Encode::decode('utf-8', "Þ sales ¥& invöice"),
 133                                            'buchungstext' => 'Testcustomer',
 134                                            'datum'        => '05.01.2017',
 135                                            'gegenkonto'   => $customer->customernumber,
 136                                            'konto'        => '1200',
 137                                            'kost1'        => 'Kostenstelle DATEV-Schnittstelle 2018',
 138                                            'kost2'        => 'Crowd-Funding September 2017',
 139                                            'umsatz'       => '784.9',
 140                                            'waehrung'     => 'EUR',
 141                                            'soll_haben_kennzeichen' => 'S',
 142                                          },
 143                                        ], "trans_id datev check use_pk ok";
 144
 145
 146 my $startdate = DateTime->new(year => 2017, month =>  1, day =>  1);
 147 my $enddate   = DateTime->new(year => 2017, month => 12, day => 31);
 148
 149 # check conversion to csv
 150 $datev1->from($startdate);
 151 $datev1->to($enddate);
 152 $datev1->use_pk(0); # reset use_pk for csv_buchungsexport
 153
 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;
 158
 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');
 161
 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 ]
 172        );
 173
 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 ]
 184        );
 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 ]
 195         );
 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,
 200   itime        => $march_9,
 201   gldate       => $march_9,
 202   intnotes     => 'booked in March',
 203   taxincluded  => 0,
 204   transdate    => $date,
 205   invoiceitems => [ create_invoice_item(part => $part1, qty =>  6, sellprice => 70),
 206                     create_invoice_item(part => $part2, qty => 20, sellprice => 50),
 207                   ]
 208 );
 209
 210 my $credit_note = create_credit_note(
 211   invnumber    => 'Gutschrift 34',
 212   customer     => $customer,
 213   itime        => $gldate,
 214   gldate       => $gldate,
 215   intnotes     => 'booked in February',
 216   taxincluded  => 0,
 217   transdate    => $date,
 218   invoiceitems => [ create_invoice_item(part => $part1, qty =>  3, sellprice => 70),
 219                     create_invoice_item(part => $part2, qty => 10, sellprice => 50),
 220                   ]
 221 );
 222
 223 my $datev = SL::DATEV->new(
 224   dbh        => $dbh,
 225   from       => $startdate,
 226   to         => $enddate,
 227 );
 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");
 232
 233 $datev->generate_datev_data(use_pk => 1, from_to => $datev->fromto);
 234 $datev_lines = $datev->generate_datev_lines;
 235
 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,
 241 );
 242
 243 $datev1->generate_datev_data;
 244 cmp_bag $datev1->generate_datev_lines, [
 245                                         {
 246                                           'belegfeld1'             => 'ap1',
 247                                           'buchungstext'           => 'Testvendor',
 248                                           'datum'                  => '01.01.2017',
 249                                           'gegenkonto'             => '1600',
 250                                           'konto'                  => '3400',
 251                                           'kost1'                  => undef,
 252                                           'kost2'                  => undef,
 253                                           'soll_haben_kennzeichen' => 'H',
 254                                           'umsatz'                 => 119,
 255                                           'waehrung'               => 'EUR'
 256                                         },
 257                                         {
 258                                           'belegfeld1'             => 'ap1',
 259                                           'buchungstext'           => 'Testvendor',
 260                                           'datum'                  => '01.01.2017',
 261                                           'gegenkonto'             => '1600',
 262                                           'konto'                  => '3300',
 263                                           'kost1'                  => undef,
 264                                           'kost2'                  => undef,
 265                                           'soll_haben_kennzeichen' => 'H',
 266                                           'umsatz'                 => 107,
 267                                           'waehrung'               => 'EUR'
 268                                         }
 269                                        ], "trans_id datev check purchase_invoice ok";
 270 $datev1->use_pk(1);
 271 $datev1->generate_datev_data;
 272 cmp_bag $datev1->generate_datev_lines, [
 273                                         {
 274                                           'belegfeld1'             => 'ap1',
 275                                           'buchungstext'           => 'Testvendor',
 276                                           'datum'                  => '01.01.2017',
 277                                           'gegenkonto'             => $vendor->vendornumber,
 278                                           'konto'                  => '3400',
 279                                           'kost1'                  => undef,
 280                                           'kost2'                  => undef,
 281                                           'soll_haben_kennzeichen' => 'H',
 282                                           'umsatz'                 => 119,
 283                                           'waehrung'               => 'EUR'
 284                                         },
 285                                         {
 286                                           'belegfeld1'             => 'ap1',
 287                                           'buchungstext'           => 'Testvendor',
 288                                           'datum'                  => '01.01.2017',
 289                                           'gegenkonto'             => $vendor->vendornumber,
 290                                           'konto'                  => '3300',
 291                                           'kost1'                  => undef,
 292                                           'kost2'                  => undef,
 293                                           'soll_haben_kennzeichen' => 'H',
 294                                           'umsatz'                 => 107,
 295                                           'waehrung'               => 'EUR'
 296                                         }
 297                                        ], "trans_id datev check purchase_invoice use_pk ok";
 298
 299 note('testing gldatefrom');
 300 $datev = SL::DATEV->new(
 301   dbh        => $dbh,
 302   from       => $startdate,
 303   to         => DateTime->new(year => 2017, month => 01, day => 31),
 304 );
 305
 306 $::form               = Support::TestSetup->create_new_form;
 307 $::form->{gldatefrom} = DateTime->new(year => 2017, month => 3, day => 1)->to_kivitendo;
 308
 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");
 313
 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";
 317
 318 done_testing();
 319 # clear_up();
 320
 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";
 328
 329   my $purchase_invoice = SL::DB::PurchaseInvoice->new(
 330     amount      => '226',
 331     currency_id => $currency_id,
 332     employee_id => $employee->id,
 333     gldate      => $date,
 334     invnumber   => "ap1",
 335     invoice     => 0,
 336     itime       => $date,
 337     mtime       => $date,
 338     netamount   => '200',
 339     paid        => '0',
 340     taxincluded => 0,
 341     taxzone_id  => $taxzone->id,
 342     transdate   => $date,
 343     type        => 'invoice',
 344     vendor_id   => $vendor->id,
 345   )->save;
 346
 347   my $expense_chart  = SL::DB::Manager::Chart->find_by(accno => '3400');
 348   my $expense_chart_booking= SL::DB::AccTransaction->new(
 349     amount     => '-100',
 350     chart_id   => $expense_chart->id,
 351     chart_link => $expense_chart->link,
 352     itime      => $date,
 353     mtime      => $date,
 354     source     => '',
 355     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
 356     taxkey     => 9,
 357     transdate  => $date,
 358     trans_id   => $purchase_invoice->id,
 359   );
 360   $expense_chart_booking->save;
 361
 362   my $tax_chart  = SL::DB::Manager::Chart->find_by(accno => '1576');
 363   my $tax_chart_booking= SL::DB::AccTransaction->new(
 364     amount     => '-19',
 365     chart_id   => $tax_chart->id,
 366     chart_link => $tax_chart->link,
 367     itime      => $date,
 368     mtime      => $date,
 369     source     => '',
 370     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 9)->id,
 371     taxkey     => 0,
 372     transdate  => $date,
 373     trans_id   => $purchase_invoice->id,
 374   );
 375   $tax_chart_booking->save;
 376   $expense_chart  = SL::DB::Manager::Chart->find_by(accno => '3300');
 377   $expense_chart_booking= SL::DB::AccTransaction->new(
 378     amount     => '-100',
 379     chart_id   => $expense_chart->id,
 380     chart_link => $expense_chart->link,
 381     itime      => $date,
 382     mtime      => $date,
 383     source     => '',
 384     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
 385     taxkey     => 8,
 386     transdate  => $date,
 387     trans_id   => $purchase_invoice->id,
 388   );
 389   $expense_chart_booking->save;
 390
 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,
 396     amount     => '-7',
 397     transdate  => $date,
 398     itime      => $date,
 399     mtime      => $date,
 400     source     => '',
 401     taxkey     => 0,
 402     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 8)->id,
 403   );
 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,
 410     amount     => '226',
 411     transdate  => $date,
 412     itime      => $date,
 413     mtime      => $date,
 414     source     => '',
 415     taxkey     => 0,
 416     tax_id     => SL::DB::Manager::Tax->find_by(taxkey => 0)->id,
 417   );
 418   $arap_booking->save;
 419
 420   return $purchase_invoice;
 421 }
 422
 423 sub clear_up {
 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;
 433 };
 434
 435 1;