t/db_helper/price_tax_calculator.t

   1 use Test::More;
   2
   3 use strict;
   4
   5 use lib 't';
   6 use utf8;
   7
   8 use Carp;
   9 use Data::Dumper;
  10 use List::MoreUtils qw(uniq);
  11 use Support::TestSetup;
  12 use Test::Exception;
  13 use SL::Dev::ALL qw(:ALL);
  14
  15 use SL::DB::Buchungsgruppe;
  16 use SL::DB::Currency;
  17 use SL::DB::Customer;
  18 use SL::DB::DeliveryOrder;
  19 use SL::DB::Employee;
  20 use SL::DB::Invoice;
  21 use SL::DB::Order;
  22 use SL::DB::Part;
  23 use SL::DB::Unit;
  24 use SL::DB::TaxZone;
  25
  26 my ($customer, @parts, $buchungsgruppe, $buchungsgruppe7, $unit, $employee, $tax, $tax7, $taxzone);
  27
  28 sub clear_up {
  29   SL::DB::Manager::Order->delete_all(all => 1);
  30   SL::DB::Manager::DeliveryOrder->delete_all(all => 1);
  31   SL::DB::Manager::InvoiceItem->delete_all(all => 1);
  32   SL::DB::Manager::Invoice->delete_all(all => 1);
  33   SL::DB::Manager::Part->delete_all(all => 1);
  34   SL::DB::Manager::Customer->delete_all(all => 1);
  35 };
  36
  37 sub reset_state {
  38   my %params = @_;
  39
  40   $params{$_} ||= {} for qw(buchungsgruppe unit customer part tax);
  41
  42   clear_up();
  43
  44   $buchungsgruppe  = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 19%', %{ $params{buchungsgruppe} }) || croak "No accounting group";
  45   $buchungsgruppe7 = SL::DB::Manager::Buchungsgruppe->find_by(description => 'Standard 7%')                                || croak "No accounting group for 7\%";
  46   $unit            = SL::DB::Manager::Unit->find_by(name => 'kg', %{ $params{unit} })                                      || croak "No unit";
  47   $employee        = SL::DB::Manager::Employee->current                                                                    || croak "No employee";
  48   $tax             = SL::DB::Manager::Tax->find_by(taxkey => 3, rate => 0.19, %{ $params{tax} })                           || croak "No tax";
  49   $tax7            = SL::DB::Manager::Tax->find_by(taxkey => 2, rate => 0.07)                                              || croak "No tax for 7\%";
  50   $taxzone         = SL::DB::Manager::TaxZone->find_by( description => 'Inland')                                           || croak "No taxzone";
  51
  52   $customer     = new_customer(
  53     name        => 'Test Customer',
  54     taxzone_id  => $taxzone->id,
  55     %{ $params{customer} }
  56   )->save;
  57
  58   @parts = ();
  59   push @parts, new_part(
  60     partnumber         => 'T4254',
  61     description        => 'Fourty-two fifty-four',
  62     lastcost           => 1.93,
  63     sellprice          => 2.34,
  64     buchungsgruppen_id => $buchungsgruppe->id,
  65     unit               => $unit->name,
  66     %{ $params{part1} }
  67   )->save;
  68
  69   push @parts, new_part(
  70     partnumber         => 'T0815',
  71     description        => 'Zero EIGHT fifteeN @ 7%',
  72     lastcost           => 5.473,
  73     sellprice          => 9.714,
  74     buchungsgruppen_id => $buchungsgruppe7->id,
  75     unit               => $unit->name,
  76     %{ $params{part2} }
  77   )->save;
  78
  79   push @parts, new_part(
  80     partnumber         => 'T888',
  81     description        => 'Triple 8',
  82     lastcost           => 0,
  83     sellprice          => 0.6,
  84     buchungsgruppen_id => $buchungsgruppe->id,
  85     unit               => $unit->name,
  86     %{ $params{part3} }
  87   )->save;
  88
  89 }
  90
  91 sub new_invoice {
  92   my %params  = @_;
  93
  94   return create_sales_invoice(
  95     taxzone_id  => $taxzone->id,
  96     %params,
  97   );
  98 }
  99
 100 sub new_item {
 101   my (%params) = @_;
 102
 103   my $part = delete($params{part}) || $parts[0];
 104
 105   return create_invoice_item(
 106     part => $part,
 107     %params,
 108   );
 109 }
 110
 111 sub test_default_invoice_one_item_19_tax_not_included() {
 112   reset_state();
 113
 114   my $item = new_item(qty => 2.5);
 115   my $invoice = new_invoice(
 116     taxincluded  => 0,
 117     invoiceitems => [ $item ],
 118   );
 119
 120   my $taxkey = $item->part->get_taxkey(date => DateTime->today_local, is_sales => 1, taxzone => $invoice->taxzone_id);
 121
 122   # sellprice 2.34 * qty 2.5 = 5.85
 123   # 19%(5.85) = 1.1115; rounded = 1.11
 124   # total rounded = 6.96
 125
 126   # lastcost 1.93 * qty 2.5 = 4.825; rounded 4.83
 127   # line marge_total = 1.02
 128   # line marge_percent = 17.4358974358974
 129
 130   my $title = 'default invoice, one item, 19% tax not included';
 131   my %data  = $invoice->calculate_prices_and_taxes;
 132
 133   is($item->marge_total,        1.02,             "${title}: item marge_total");
 134   is($item->marge_percent,      17.4358974358974, "${title}: item marge_percent");
 135   is($item->marge_price_factor, 1,                "${title}: item marge_price_factor");
 136
 137   is($invoice->netamount,       5.85,             "${title}: netamount");
 138   is($invoice->amount,          6.96,             "${title}: amount");
 139   is($invoice->marge_total,     1.02,             "${title}: marge_total");
 140   is($invoice->marge_percent,   17.4358974358974, "${title}: marge_percent");
 141
 142   is_deeply(\%data, {
 143     allocated                                    => {},
 144     amounts                                      => {
 145       $buchungsgruppe->income_accno_id($taxzone) => {
 146         amount                                   => 5.85,
 147         tax_id                                   => $tax->id,
 148         taxkey                                   => 3,
 149       },
 150     },
 151     amounts_cogs                                 => {},
 152     assembly_items                               => [
 153       [],
 154     ],
 155     exchangerate                                 => 1,
 156     taxes                                        => {
 157       $tax->chart_id                             => 1.11,
 158     },
 159     taxes_by_tax_id                              => {
 160       $tax->id                                   => 1.1115,
 161     },
 162     items                                        => [
 163       { linetotal                                => 5.85,
 164         linetotal_cost                           => 4.83,
 165         sellprice                                => 2.34,
 166         tax_amount                               => 1.1115,
 167         taxkey_id                                => $taxkey->id,
 168       },
 169     ],
 170     rounding                                    =>  0,
 171   }, "${title}: calculated data");
 172 }
 173
 174 sub test_default_invoice_two_items_19_7_tax_not_included() {
 175   reset_state();
 176
 177   my $item1   = new_item(qty => 2.5);
 178   my $item2   = new_item(qty => 1.2, part => $parts[1]);
 179   my $invoice = new_invoice(
 180     taxincluded  => 0,
 181     invoiceitems => [ $item1, $item2 ],
 182   );
 183
 184   my $taxkey1 = $item1->part->get_taxkey(date => DateTime->today_local, is_sales => 1, taxzone => $invoice->taxzone_id);
 185   my $taxkey2 = $item2->part->get_taxkey(date => DateTime->today_local, is_sales => 1, taxzone => $invoice->taxzone_id);
 186
 187   # item 1:
 188   # sellprice 2.34 * qty 2.5 = 5.85
 189   # 19%(5.85) = 1.1115; rounded = 1.11
 190   # total rounded = 6.96
 191
 192   # lastcost 1.93 * qty 2.5 = 4.825; rounded 4.83
 193   # line marge_total = 1.02
 194   # line marge_percent = 17.4358974358974
 195
 196   # item 2:
 197   # sellprice 9.714 * qty 1.2 = 11.6568 rounded 11.66
 198   # 7%(11.6568) = 0.815976; rounded = 0.82
 199   # 7%(11.66)   = 0.8162
 200   # total rounded = 12.48
 201
 202   # lastcost 5.473 * qty 1.2 = 6.5676; rounded 6.57
 203   # line marge_total = 5.09
 204   # line marge_percent = 43.6535162950257
 205
 206   my $title = 'default invoice, two item, 19/7% tax not included';
 207   my %data  = $invoice->calculate_prices_and_taxes;
 208
 209   is($item1->marge_total,        1.02,             "${title}: item1 marge_total");
 210   is($item1->marge_percent,      17.4358974358974, "${title}: item1 marge_percent");
 211   is($item1->marge_price_factor, 1,                "${title}: item1 marge_price_factor");
 212
 213   is($item2->marge_total,        5.09,             "${title}: item2 marge_total");
 214   is($item2->marge_percent,      43.6535162950257, "${title}: item2 marge_percent");
 215   is($item2->marge_price_factor, 1,                "${title}: item2 marge_price_factor");
 216
 217   is($invoice->netamount,        5.85 + 11.66,     "${title}: netamount");
 218   is($invoice->amount,           6.96 + 12.48,     "${title}: amount");
 219   is($invoice->marge_total,      1.02 + 5.09,      "${title}: marge_total");
 220   is($invoice->marge_percent,    34.8943460879497, "${title}: marge_percent");
 221
 222   is_deeply(\%data, {
 223     allocated                                     => {},
 224     amounts                                       => {
 225       $buchungsgruppe->income_accno_id($taxzone)  => {
 226         amount                                    => 5.85,
 227         tax_id                                    => $tax->id,
 228         taxkey                                    => 3,
 229       },
 230       $buchungsgruppe7->income_accno_id($taxzone) => {
 231         amount                                    => 11.66,
 232         tax_id                                    => $tax7->id,
 233         taxkey                                    => 2,
 234       },
 235     },
 236     amounts_cogs                                  => {},
 237     assembly_items                                => [
 238       [], [],
 239     ],
 240     exchangerate                                  => 1,
 241     taxes                                         => {
 242       $tax->chart_id                              => 1.11,
 243       $tax7->chart_id                             => 0.82,
 244     },
 245     taxes_by_tax_id                               => {
 246       $tax->id                                    => 1.1115,
 247       $tax7->id                                   => 0.8162,
 248     },
 249     items                                        => [
 250       { linetotal                                => 5.85,
 251         linetotal_cost                           => 4.83,
 252         sellprice                                => 2.34,
 253         tax_amount                               => 1.1115,
 254         taxkey_id                                => $taxkey1->id,
 255       },
 256       { linetotal                                => 11.66,
 257         linetotal_cost                           => 6.57,
 258         sellprice                                => 9.714,
 259         tax_amount                               => 0.8162,
 260         taxkey_id                                => $taxkey2->id,
 261       },
 262     ],
 263     rounding                                    =>  0,
 264   }, "${title}: calculated data");
 265 }
 266
 267 sub test_default_invoice_three_items_sellprice_rounding_discount() {
 268   reset_state();
 269
 270   my $item1   = new_item(qty => 1, sellprice => 5.55, discount => .05);
 271   my $item2   = new_item(qty => 1, sellprice => 5.50, discount => .05);
 272   my $item3   = new_item(qty => 1, sellprice => 5.00, discount => .05);
 273   my $invoice = new_invoice(
 274     taxincluded  => 0,
 275     invoiceitems => [ $item1, $item2, $item3 ],
 276   );
 277
 278   my %taxkeys = map { ($_->id => $_->get_taxkey(date => DateTime->today_local, is_sales => 1, taxzone => $invoice->taxzone_id)) } uniq map { $_->part } ($item1, $item2, $item3);
 279
 280   # item 1:
 281   # discount = sellprice 5.55 * discount (0.05) = 0.2775; rounded 0.28
 282   # linetotal = sellprice 5.55 * (1 - discount 0.05) * qty 1 = 5.2725; rounded 5.27
 283   # 19%(5.27) = 1.0013; rounded = 1.00
 284   # total rounded = 6.27
 285
 286   # lastcost 1.93 * qty 1 = 1.93; rounded 1.93
 287   # line marge_total = 5.27 - 1.93 = 3.34
 288   # line marge_percent = 63.3776091081594
 289
 290   # item 2:
 291   # discount = sellprice 5.50 * discount 0.05 = 0.275; rounded 0.28
 292   # linetotal = sellprice 5.50 * (1 - discount 0.05) * qty 1 = 5.225; rounded 5.23
 293   # 19%(5.23) = .99370; rounded = 0.99
 294   # total rounded = 6.22
 295
 296   # lastcost 1.93 * qty 1 = 1.93; rounded 1.93
 297   # line marge_total = 5.23 - 1.93 = 3.30
 298   # line marge_percent = 3.30/5.23 = 0.630975143403442
 299
 300   # item 3:
 301   # discount = sellprice 5.00 * discount 0.05 = 0.05 = 0.25; rounded 0.25
 302   # linetotal = sellprice 5.00 (1 - discount 0.05) * qty 1 = 4.75; rounded 4.75
 303   # 19%(4.75) = 0.9025; rounded = 0.90
 304   # total rounded = 5.65
 305
 306   # lastcost 1.93 * qty 1 = 1.93; rounded 1.93
 307   # line marge_total = 4.75 - 1.93 = 2.82
 308   # line marge_percent = 2.82/4.75 = 59.3684210526316
 309
 310   my $title = 'default invoice, three items, sellprice, rounding, discount';
 311   my %data  = $invoice->calculate_prices_and_taxes;
 312
 313   is($item1->marge_total,        3.34,               "${title}: item1 marge_total");
 314   is($item1->marge_percent,      63.3776091081594,   "${title}: item1 marge_percent");
 315   is($item1->marge_price_factor, 1,                  "${title}: item1 marge_price_factor");
 316
 317   is($item2->marge_total,        3.30,               "${title}: item2 marge_total");
 318   is($item2->marge_percent,      63.0975143403442,   "${title}: item2 marge_percent");
 319   is($item2->marge_price_factor, 1,                  "${title}: item2 marge_price_factor");
 320
 321   is($item3->marge_total,        2.82,               "${title}: item3 marge_total");
 322   is($item3->marge_percent,      59.3684210526316,   "${title}: item3 marge_percent");
 323   is($item3->marge_price_factor, 1,                  "${title}: item3 marge_price_factor");
 324
 325   is($invoice->netamount,        5.27 + 5.23 + 4.75, "${title}: netamount");
 326
 327   # 6.27 + 6.22 + 5.65 = 18.14
 328   # 1.19*(5.27 + 5.23 + 4.75) = 18.1475; rounded 18.15
 329   #is($invoice->amount,           6.27 + 6.22 + 5.65, "${title}: amount");
 330   is($invoice->amount,           18.15,              "${title}: amount");
 331
 332   is($invoice->marge_total,      3.34 + 3.30 + 2.82, "${title}: marge_total");
 333   is($invoice->marge_percent,    62.0327868852459,   "${title}: marge_percent");
 334
 335   is_deeply(\%data, {
 336     allocated                                    => {},
 337     amounts                                      => {
 338       $buchungsgruppe->income_accno_id($taxzone) => {
 339         amount                                   => 15.25,
 340         tax_id                                   => $tax->id,
 341         taxkey                                   => 3,
 342       },
 343     },
 344     amounts_cogs                                 => {},
 345     assembly_items                               => [
 346       [], [], [],
 347     ],
 348     exchangerate                                 => 1,
 349     taxes                                        => {
 350       $tax->chart_id                             => 2.9,
 351     },
 352     taxes_by_tax_id                              => {
 353       $tax->id                                   => 2.89750,
 354     },
 355     items                                        => [
 356       { linetotal                                => 5.27,
 357         linetotal_cost                           => 1.93,
 358         sellprice                                => 5.27,
 359         tax_amount                               => 1.0013,
 360         taxkey_id                                => $taxkeys{$item1->parts_id}->id,
 361       },
 362       { linetotal                                => 5.23,
 363         linetotal_cost                           => 1.93,
 364         sellprice                                => 5.23,
 365         tax_amount                               => 0.9937,
 366         taxkey_id                                => $taxkeys{$item2->parts_id}->id,
 367       },
 368       { linetotal                                => 4.75,
 369         linetotal_cost                           => 1.93,
 370         sellprice                                => 4.75,
 371         tax_amount                               => 0.9025,
 372         taxkey_id                                => $taxkeys{$item3->parts_id}->id,
 373       }
 374     ],
 375     rounding                                    =>  0,
 376   }, "${title}: calculated data");
 377 }
 378
 379 sub test_default_invoice_one_item_19_tax_not_included_rounding_discount() {
 380   reset_state();
 381
 382   my $item   = new_item(qty => 6, part => $parts[2], discount => 0.03);
 383   my $invoice = new_invoice(
 384     taxincluded  => 0,
 385     invoiceitems => [ $item ],
 386   );
 387
 388   my %taxkeys = map { ($_->id => $_->get_taxkey(date => DateTime->today_local, is_sales => 1, taxzone => $invoice->taxzone_id)) } uniq map { $_->part } ($item);
 389
 390   # 6 parts for 0.60 with 3% discount
 391   #
 392   # linetotal = sellprice 0.60 * qty 6 * discount (1 - 0.03) = 3.492 rounded 3.49
 393   # total = 3.49 + 0.66 = 4.15
 394   #
 395
 396   my $title = 'default invoice, one item, sellprice, rounding, discount';
 397   my %data  = $invoice->calculate_prices_and_taxes;
 398
 399   is($invoice->netamount,         3.49,              "${title}: netamount");
 400
 401   is($invoice->amount,            4.15,              "${title}: amount");
 402
 403   is($invoice->marge_total,       3.49,              "${title}: marge_total");
 404   is($invoice->marge_percent,      100,              "${title}: marge_percent");
 405
 406   is_deeply(\%data, {
 407     allocated                                    => {},
 408     amounts                                      => {
 409       $buchungsgruppe->income_accno_id($taxzone) => {
 410         amount                                   => 3.49,
 411         tax_id                                   => $tax->id,
 412         taxkey                                   => 3,
 413       },
 414     },
 415     amounts_cogs                                 => {},
 416     assembly_items                               => [
 417       [],
 418     ],
 419     exchangerate                                 => 1,
 420     taxes                                        => {
 421       $tax->chart_id                             => 0.66,
 422     },
 423     taxes_by_tax_id                              => {
 424       $tax->id                                   => 0.66310,
 425     },
 426     items                                        => [
 427       { linetotal                                => 3.49,
 428         linetotal_cost                           => 0,
 429         sellprice                                => 0.58,
 430         tax_amount                               => 0.6631,
 431         taxkey_id                                => $taxkeys{$item->parts_id}->id,
 432       },
 433     ],
 434     rounding                                     =>  0,
 435   }, "${title}: calculated data");
 436 }
 437
 438 sub test_default_invoice_one_item_19_tax_not_included_rounding_discount_huge_qty() {
 439   reset_state();
 440
 441   my $item   = new_item(qty => 100000, part => $parts[2], discount => 0.03, sellprice => 0.10);
 442   my $invoice = new_invoice(
 443     taxincluded  => 0,
 444     invoiceitems => [ $item ],
 445   );
 446
 447   my %taxkeys = map { ($_->id => $_->get_taxkey(date => DateTime->today_local, is_sales => 1, taxzone => $invoice->taxzone_id)) } uniq map { $_->part } ($item);
 448
 449   my $title = 'default invoice, one item, 19% tax not included, rounding, discount, huge qty';
 450   my %data  = $invoice->calculate_prices_and_taxes;
 451
 452   is($invoice->netamount,         9700,              "${title}: netamount");
 453
 454   is($invoice->amount,           11543,              "${title}: amount");
 455
 456   is($invoice->marge_total,       9700,              "${title}: marge_total");
 457   is($invoice->marge_percent,      100,              "${title}: marge_percent");
 458
 459   is_deeply(\%data, {
 460     allocated                                    => {},
 461     amounts                                      => {
 462       $buchungsgruppe->income_accno_id($taxzone) => {
 463         amount                                   => 9700,
 464         tax_id                                   => $tax->id,
 465         taxkey                                   => 3,
 466       },
 467     },
 468     amounts_cogs                                 => {},
 469     assembly_items                               => [
 470       [],
 471     ],
 472     exchangerate                                 => 1,
 473     taxes                                        => {
 474       $tax->chart_id                             => 1843,
 475     },
 476     taxes_by_tax_id                              => {
 477       $tax->id                                   => 1843,
 478     },
 479     items                                        => [
 480       { linetotal                                => 9700,
 481         linetotal_cost                           => 0,
 482         sellprice                                => 0.1,
 483         tax_amount                               => 1843,
 484         taxkey_id                                => $taxkeys{$item->parts_id}->id,
 485       },
 486     ],
 487     rounding                                    =>  0,
 488   }, "${title}: calculated data");
 489 }
 490
 491 sub test_default_invoice_one_item_19_tax_not_included_rounding_discount_big_qty_low_sellprice() {
 492   reset_state();
 493
 494   my $item   = new_item(qty => 10001, sellprice => 0.007, discount => 0.035);
 495   my $invoice = new_invoice(
 496     taxincluded  => 0,
 497     invoiceitems => [ $item ],
 498   );
 499
 500   my %taxkeys = map { ($_->id => $_->get_taxkey(date => DateTime->today_local, is_sales => 1, taxzone => $invoice->taxzone_id)) } uniq map { $_->part } ($item);
 501
 502   # item 1:
 503   # discount = sellprice 0.007 * discount (0.035) = 0.000245; rounded 0.00
 504   # sellprice = sellprice 0.007 - discount 0.00 = 0.007
 505   # linetotal = sellprice 0.007 * qty 10001 * (1 - 0.035) = 67.556755; rounded 67.56
 506   # 19%(67.56) = 12.8364; rounded = 12.84
 507   # total rounded = 80.40
 508
 509   # lastcost 1.93 * qty 10001 = 19301.93; rounded 19301.93
 510   # line marge_total = 67.56-19301.93 = -19234.37
 511   # line marge_percent = 100*-19234.37/67.56 = -28470.0562462996
 512
 513   my $title = 'default invoice one item 19 tax not included rounding discount big qty low sellprice';
 514   my %data  = $invoice->calculate_prices_and_taxes;
 515
 516   is($invoice->netamount,                 67.56,    "${title}: netamount");
 517
 518   is($invoice->amount,                    80.40,    "${title}: amount");
 519
 520   is($invoice->marge_total,           -19234.37,    "${title}: marge_total");
 521   is($invoice->marge_percent, -28470.0562462996,    "${title}: marge_percent");
 522
 523   is_deeply(\%data, {
 524     allocated                                    => {},
 525     amounts                                      => {
 526       $buchungsgruppe->income_accno_id($taxzone) => {
 527         amount                                   => 67.56,
 528         tax_id                                   => $tax->id,
 529         taxkey                                   => 3,
 530       },
 531     },
 532     amounts_cogs                                 => {},
 533     assembly_items                               => [
 534       [],
 535     ],
 536     exchangerate                                 => 1,
 537     taxes                                        => {
 538       $tax->chart_id                             => 12.84,
 539     },
 540     taxes_by_tax_id                              => {
 541       $tax->id                                   => 12.8364,
 542     },
 543     items                                        => [
 544       { linetotal                                => 67.56,
 545         linetotal_cost                           => 19301.93,
 546         sellprice                                => 0.007,
 547         tax_amount                               => 12.8364,
 548         taxkey_id                                => $taxkeys{$item->parts_id}->id,
 549       },
 550     ],
 551     rounding                                    =>  0,
 552   }, "${title}: calculated data");
 553 }
 554
 555
 556 Support::TestSetup::login();
 557
 558 test_default_invoice_one_item_19_tax_not_included();
 559 test_default_invoice_two_items_19_7_tax_not_included();
 560 test_default_invoice_three_items_sellprice_rounding_discount();
 561 test_default_invoice_one_item_19_tax_not_included_rounding_discount();
 562 test_default_invoice_one_item_19_tax_not_included_rounding_discount_huge_qty();
 563 test_default_invoice_one_item_19_tax_not_included_rounding_discount_big_qty_low_sellprice();
 564
 565 clear_up();
 566 done_testing();
 567
 568 # vim: ft=perl
 569 # set emacs to perl mode
 570 # Local Variables:
 571 # mode: perl
 572 # End: